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
;