Oracle EBS AP 供应商取值

SELECT --nvl(substr(po.vendor_name,1,instr(po.vendor_name,‘,1)-1),po.vendor_name) vendor_name,
       po.vendor_name vendor_name,po.vendor_name_alt,--别名
       po.segment1,--供应商编号
       decode(po.employee_id,NULL,‘STANDARD‘,‘EMPLOYEE‘) vendor_type,--创建供应商若关联员工 则是员工创建 若没有则是标准
       flv.meaning TYPE,--类型
       hop.tax_reference,--纳税登记编号
       hop.jgzz_fiscal_code,--纳税人标识
       ps.purchasing_site_flag,--采购
       ps.pay_site_flag,--付款
       ps.rfq_only_site_flag,pvsa.org_id,hr.name org_name,hl.country country,hl.province province,hl.state state,hl.city city,hl.postal_code postal_code,--邮政编码
       hl.address1 address1,hps.party_site_name address_name,hcpp1.phone_number,hcpf1.phone_number fax_number,pvsa.attribute1 pay_type,pvsa.attribute2 id_card,gcc.segment1 accts_pay_code1,--负债账户
       gcc.segment2 accts_pay_code2,gcc.segment3 accts_pay_code3,gcc.segment4 accts_pay_code4,gcc.segment5 accts_pay_code5,gcc.segment6 accts_pay_code6,gcc.segment7 accts_pay_code7,gcc.segment8 accts_pay_code8,gcc1.segment1 prepay_code1,--预付款账户
       gcc1.segment2 prepay_code2,gcc1.segment3 prepay_code3,gcc1.segment4 prepay_code4,gcc1.segment5 prepay_code5,gcc1.segment6 prepay_code6,gcc1.segment7 prepay_code7,gcc1.segment8 prepay_code8,gcc2.segment1 future_dated_payment1,--应付票据
       gcc2.segment2 future_dated_payment2,gcc2.segment3 future_dated_payment3,gcc2.segment4 future_dated_payment4,gcc2.segment5 future_dated_payment5,gcc2.segment6 future_dated_payment6,gcc2.segment7 future_dated_payment7,gcc2.segment8 future_dated_payment8,hp.person_first_name first_name,--名
       hp.person_last_name last_name,--姓
       hp.person_middle_name middle_name,--中间名
       hcpp.phone_number phone_number1,--电话(联系人)
       hcpf.phone_number fax_number1,--传真（联系人）
       hcpe.email_address email_address,--EMAIL
       ieba.bank_account_num bank_account_num,ieba.bank_account_name bank_account_name,ieba.country_code country1,hz.party_name bank_name,hz1.party_name branch_name,ieba.attribute1 deposit_bank_type,ieba.attribute2 bank_province,ieba.attribute3 bank_city,ieba.attribute4 bank_number,ipm.payment_method_name,po.end_date_active,hps.status,po.vendor_id,ps.vendor_site_id,ieba.bank_id,ieba.branch_id,po.attribute1 wm_coa_int,po.attribute2 taxpayer_type,hps.end_date_active org_end_time,ieba.end_date bank_num_end_time,decode(sign(po.last_update_date - ps.LAST_UPDATE_DATE),1,ps.LAST_UPDATE_DATE,po.last_update_date)   last_update_date,ppf.employee_number -- add by ran

  FROM po_vendors                  po,po_vendor_sites_all         pvsa,hz_organization_profiles    hop,ap_supplier_sites_all       ps,hz_party_sites              hps,fnd_lookup_values           flv,hz_locations                hl,hr_operating_units          hr,gl_code_combinations        gcc,gl_code_combinations        gcc1,gl_code_combinations        gcc2,hz_relationships            hr1,hz_parties                  hp,hz_contact_points           hcpp1,hz_contact_points           hcpf1,hz_contact_points           hcpp,hz_contact_points           hcpf,hz_contact_points           hcpe,--iby_account_owners          iao,--deleted by jenrry 20170627
       iby_ext_bank_accounts       ieba,hz_parties                  hz,hz_parties                  hz1,iby.iby_external_payees_all iep,iby.iby_ext_party_pmt_mthds ieppm,iby_payment_methods_vl      ipm,per_people_f                ppf --add by ran
,hz_parties                  hp1,iby_pmt_instr_uses_all   uses--add by jenrry 20170627
      
 WHERE po.party_id = hop.party_id(+)
   AND po.party_id = hps.party_id(+)
   AND hps.party_site_id = ps.party_site_id(+)
   AND ps.vendor_id(+) = po.vendor_id
   AND po.vendor_id = pvsa.vendor_id(+)
   AND ps.inactive_date(+) IS NULL
   AND pvsa.vendor_site_id = ps.vendor_site_id(+)
   AND pvsa.org_id = ps.org_id(+)
   AND hop.effective_end_date(+) IS NULL
   AND flv.lookup_type(+) = ‘VENDOR TYPE‘
   AND po.vendor_type_lookup_code = flv.lookup_code(+)
   AND flv.language(+) = userenv(‘lang‘)
   AND hps.location_id = hl.location_id(+)
   AND hr.organization_id(+) = pvsa.org_id
   AND hcpp1.owner_table_id(+) = hps.party_site_id
   AND hcpf1.owner_table_id(+) = hps.party_site_id
   AND hcpp1.owner_table_name(+) = ‘HZ_PARTY_SITES‘
   AND hcpf1.owner_table_name(+) = ‘HZ_PARTY_SITES‘
   AND hcpp1.contact_point_type(+) = ‘PHONE‘
   AND hcpp1.phone_line_type(+) = ‘GEN‘
   AND hcpf1.contact_point_type(+) = ‘PHONE‘
   AND hcpf1.phone_line_type(+) = ‘FAX‘
   AND hcpp1.primary_flag(+) = ‘Y‘
   AND hcpf1.primary_flag(+) = ‘Y‘
   AND gcc.code_combination_id(+) = ps.accts_pay_code_combination_id
   AND gcc1.code_combination_id(+) = ps.prepay_code_combination_id
   AND gcc2.code_combination_id(+) = ps.future_dated_payment_ccid
   AND hr1.object_id(+) = po.party_id
   AND hp.party_id(+) = hr1.subject_id
   AND hcpp.owner_table_id(+) = hr1.party_id
   AND hcpf.owner_table_id(+) = hr1.party_id
   AND hcpe.owner_table_id(+) = hr1.party_id
   AND hcpp.owner_table_name(+) = ‘HZ_PARTIES‘
   AND hcpf.owner_table_name(+) = ‘HZ_PARTIES‘
   AND hcpe.owner_table_name(+) = ‘HZ_PARTIES‘
   AND hcpp.contact_point_type(+) = ‘PHONE‘
   AND hcpp.phone_line_type(+) = ‘GEN‘
   AND hcpf.contact_point_type(+) = ‘PHONE‘
   AND hcpf.phone_line_type(+) = ‘FAX‘
   AND hcpe.contact_point_type(+) = ‘EMAIL‘
   AND hcpe.phone_line_type(+) IS NULL
   --AND po.party_id = iao.account_owner_party_id(+) --deleted by jenrry 20170627
   --AND iao.ext_bank_account_id = ieba.ext_bank_account_id(+) --deleted by jenrry 20170627
   and uses.instrument_type(+) = ‘BANKACCOUNT‘ --add by jenrry 20170627
   AND iep.ext_payee_id+1 = uses.ext_pmt_party_id(+) --add by jenrry 20170627
   AND iep.payment_function(+) = ‘PAYABLES_DISB‘ --add by jenrry 20170627
   AND uses.instrument_id = ieba.ext_bank_account_id(+) --add by jenrry 20170627
   and iep.ORG_ID(+) = ps.ORG_ID --add by jenrry 20170627
   --AND iao.primary_flag(+) = ‘Y‘ --deleted by jenrry 20170627
   AND ieba.bank_id = hz.party_id(+)
   AND ieba.branch_id = hz1.party_id(+)
   AND iep.party_site_id(+) = ps.party_site_id --这个用来过滤费用员工供应商，貌似做完员工费用报表，系统自动生成一个员工供应商，但界面查询不到
   AND iep.supplier_site_id(+) = pvsa.vendor_site_id
   AND iep.payee_party_id(+) = po.party_id /*--the same as iep.payee_party_id = hzp.party_id*/
   AND iep.ext_payee_id = ieppm.ext_pmt_party_id(+)
   AND hp1.person_identifier = ppf.person_id(+) --add by ran
   AND ieppm.payment_method_code = ipm.payment_method_code(+)
   and hp1.PARTY_ID = po.PARTY_ID
   and ppf.EFFECTIVE_END_DATE(+) > sysdate
;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!