供应商信息全SQL

SELECT hou.name,

       pv.vendor_name 供应商,

       pv.party_id,

       pvs.vendor_site_id,

       pvs.terms_id,

       pv.vendor_name_alt 供应商别名,

       hp.known_as 别名,

       cux_rebate_pub.get_lookup_meaning(p_lookup_type => 'VENDOR TYPE',

                                         p_lookup_code => pv.vendor_type_lookup_code) 供应商类型,

       decode(upper(pv.vendor_type_lookup_code),

              'EMPLOYEE',

              papf.national_identifier,

              decode(pv.organization_type_lookup_code,

                     'INDIVIDUAL',

                     pv.individual_1099,

                     'FOREIGN INDIVIDUAL',

                     pv.individual_1099,

                     hp.jgzz_fiscal_code)) 纳税人标识,

       pv.attribute1 是否内部供应商,

       pv.attribute2 快递收件人电话,

       pv.attribute3 快递收件人地址,

       bank.party_name 银行名称,

       branch.party_name 银行分行名称,

       ieba.bank_account_num 银行账号,

       hop.principal_name 法人代表,

       vendor_site.party_site_name 供应商地点,

       vendor_site.vendor_address 地址,

       vendor_site.city 城市,

       vendor_site.postal_code 邮编,

       hpc.person_last_name 联系人姓名,

       /*hcpp.phone_area_code ||'-'||*/

       hcpp.phone_number 电话号码,

       /*hcpf.phone_area_code ||'-'||*/

       cux_inv_item_category_pkg.get_gl_concat_segments(p_ccid => pvs.accts_pay_code_combination_id) 应付票据,

       cux_inv_item_category_pkg.get_gl_concat_segments(p_ccid => pvs.prepay_code_combination_id) 预付款,

       hcpf.phone_number 传真号码,

       zft.classification_type_code 会计分类类型代码,

       hca.class_code 会计分类代码,

       hca.start_date_active 有效日期自,

       ipm.payment_method_name 付款方法,

       att.name 付款条件,

       gcc1.concatenated_segments 负债账户,

       gcc2.concatenated_segments 预付款账户

  FROM po_vendors pv,

       hz_parties hp,

       per_all_people_f papf,

       hz_organization_profiles hop,

       (SELECT hps.party_id,

               hps.party_site_id,

               hps.party_site_name,

               hl.city,

               hl.postal_code,

               hl.address1 || ' ' || hl.address2 || ' ' || hl.address3 || ' ' ||

               hl.address4 vendor_address

          FROM hz_party_sites hps, hz_locations hl

         WHERE hps.status = 'A'

           AND hl.location_id = hps.location_id

           AND NOT EXISTS

         (SELECT 1

                  FROM pos_address_requests par, pos_supplier_mappings psm

                 WHERE psm.party_id = hps.party_id

                   AND psm.mapping_id = par.mapping_id

                   AND party_site_id = hps.party_site_id

                   AND request_status = 'PENDING'

                   AND request_type IN ('UPDATE', 'DELETE'))

        UNION ALL

        SELECT psm.party_id,

               par.party_site_id,

               par.party_site_name,

               par.city,

               par.postal_code,

               par.address_line1 || ' ' || par.address_line2 || ' ' ||

               par.address_line3 || ' ' || par.address_line4 vendor_address

          FROM pos_address_requests par, pos_supplier_mappings psm

         WHERE par.mapping_id = psm.mapping_id

           AND par.request_status = 'PENDING'

           AND par.request_type = 'ADD') vendor_site,

       hz_relationships hr,

       hz_parties hpc,

       hz_contact_points hcpp,

       hz_contact_points hcpf,

       hz_party_sites hps,

       zx_party_tax_profile ptp,

       hz_code_assignments hca,

       zx_fc_types_b zft,

       po_vendor_sites_all pvs,

       hr_operating_units hou,

       iby_external_payees_all payee,

       iby_ext_party_pmt_mthds ppm,

       iby_payment_methods_vl ipm,

       ap_terms_tl att,

       gl_code_combinations_kfv gcc1,

       gl_code_combinations_kfv gcc2,

       iby_account_owners iao, --账号与供应商关系表

       iby_ext_bank_accounts ieba, --账号账号表

       hz_parties bank, --银行表

       hz_parties branch --银行分行表

 WHERE 1 = 1

   AND pv.party_id = hp.party_id

   AND pv.employee_id = papf.person_id(+)

   AND hop.party_id = hp.party_id

   AND hop.effective_end_date IS NULL

   AND vendor_site.party_id(+) = hp.party_id

   AND hp.party_id = hr.subject_id(+)

   AND hr.subject_type(+) = 'ORGANIZATION'

   AND hr.subject_table_name(+) = 'HZ_PARTIES'

   AND hr.object_table_name(+) = 'HZ_PARTIES'

   AND hr.object_type(+) = 'PERSON'

   AND hr.relationship_code(+) = 'CONTACT'

   AND hr.directional_flag(+) = 'B'

   AND hr.relationship_type(+) = 'CONTACT'

   AND hr.object_id = hpc.party_id(+)

   AND hcpp.owner_table_id(+) = hr.party_id

   AND hcpp.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpp.phone_line_type(+) = 'GEN'

   AND hcpp.contact_point_type(+) = 'PHONE'

   AND hcpf.owner_table_id(+) = hr.party_id

   AND hcpf.owner_table_name(+) = 'HZ_PARTIES'

   AND hcpf.phone_line_type(+) = 'FAX'

   AND hcpf.contact_point_type(+) = 'PHONE'

   AND hp.party_id = hps.party_id(+)

   AND ptp.party_id(+) = hps.party_site_id

   AND ptp.party_type_code(+) = 'THIRD_PARTY_SITE'

   AND hca.owner_table_name(+) = 'ZX_PARTY_TAX_PROFILE'

   AND hca.owner_table_id(+) = ptp.party_tax_profile_id

   AND hca.class_category = zft.owner_id_char(+)

   AND pv.vendor_id = pvs.vendor_id

   AND pvs.org_id = hou.organization_id

   AND payee.org_id(+) = pvs.org_id

   AND payee.party_site_id(+) = pvs.party_site_id

   AND payee.supplier_site_id(+) = pvs.vendor_site_id

   AND ppm.ext_pmt_party_id(+) = payee.ext_payee_id

   AND ppm.payment_flow(+) = 'DISBURSEMENTS'

   AND (ppm.inactive_date(+) IS NULL OR

       ppm.inactive_date(+) >= trunc(SYSDATE))

   AND ppm.payment_method_code = ipm.payment_method_code(+)

   AND pvs.terms_id = att.term_id(+)

   AND att.language(+) = 'ZHS'

   AND att.enabled_flag(+) = 'Y'

   AND pvs.accts_pay_code_combination_id = gcc1.code_combination_id(+)

   AND pvs.prepay_code_combination_id = gcc2.code_combination_id(+)

   AND pv.party_id = iao.account_owner_party_id(+)

   AND iao.ext_bank_account_id = ieba.ext_bank_account_id(+)

   AND ieba.bank_id = bank.party_id(+)

   AND ieba.branch_id = branch.party_id(+)

--AND pv.vendor_id = 15

 ORDER BY pv.vendor_name;

/* SELECT COUNT(1) FROM AP_SUPPLIER_SITES_ALL;

*/
巴特西

供应商信息全SQL

最新文章

热门文章
