AP Netting Suppliers Report
SELECT AL1.Company Company,
AL1.Vendor_Name,
AL1.Vendor_Number,
AL1.Pay_Group,
AL1.ABN_Vendor_Number,
AL1.Currency_Code,
AL1.Address_Line1,
AL1.Address_Line2,
AL1.Address_Line3,
AL1.Vendor_City,
AL1.State,
AL1.ZIP,
AL1.Vendor_Country,
AL1.FAX_Area_Code,
AL1.FAX,
AL1.Vendor_Netting_Email1,
AL1.Account_Number,
AL1.Bank_Number,
AL1.Branch_Number,
AL1.Bank_Name,
AL1.City,
AL1.Country,
AL1.Vendor_Site,
AL1.Funct_Curr,
AL1.IBAN_Number
FROM (SELECT PO.segment1 Vendor_Number,
PVSA.PAY_GROUP_LOOKUP_CODE Pay_Group,
SUBSTR(hou.NAME,1,3) Company,
ipi.attribute1 ABN_Vendor_Number,
regexp_replace(replace(translate(PO.VENDOR_NAME,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Vendor_Name,
ieb.CURRENCY_CODE Currency_Code,
regexp_replace(replace(translate(PVSA.ADDRESS_LINE1,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Address_Line1,
regexp_replace(replace(translate(PVSA.ADDRESS_LINE2,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Address_Line2,
regexp_replace(replace(translate(PVSA.ADDRESS_LINE3,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Address_Line3,
regexp_replace(replace(translate(NVL(PVSA.CITY,'UNKNOWN'),
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Vendor_City,
regexp_replace(replace(translate(NVL(NVL(PVSA.STATE,PVSA.PROVINCE),
'UNKNOWN'),'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') State,
regexp_replace(replace(translate(PVSA.ZIP,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') ZIP,
regexp_replace(replace(translate(PVSA.COUNTRY,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Vendor_Country,
regexp_replace(replace(translate(PVSA.FAX_AREA_CODE,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') FAX_Area_Code,
regexp_replace(replace(translate(PVSA.FAX,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') FAX,
regexp_replace(replace(translate(iep.remit_advice_email,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Vendor_Netting_Email1,
regexp_replace(replace(translate(ieb.bank_account_num,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Account_Number,
regexp_replace(replace(translate(bank_prof.bank_or_branch_number,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Bank_Number,
regexp_replace(replace(translate(branch_prof.bank_or_branch_number,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Branch_Number,
regexp_replace(replace(translate(party_bank.party_name,
'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') Bank_Name,
regexp_replace(replace(translate(NVL(party_branch.CITY,
'UNKNOWN'),'~||*||:||\||^','~'),'~',''),'[[:cntrl:]]') City,
DECODE(GREATEST(SUBSTR(branch_prof.bank_or_branch_number,1,1),CHR('64')),
LEAST(SUBSTR(branch_prof.bank_or_branch_number,1,1),CHR('91')),
SUBSTR(branch_prof.bank_or_branch_number,5,2),'US') Country,
( select class_code
from hz_code_assignments hca_brtype
where class_category = 'BANK_BRANCH_TYPE'
and hca_brtype.owner_table_name = 'HZ_PARTIES'
and owner_table_id = ieb.branch_id
and sysdate between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate+1))
and rownum = 1
) Branch_Type,
PVSA.VENDOR_SITE_CODE Vendor_Site,
PVSA.PAYMENT_CURRENCY_CODE Funct_Curr,
ieb.iban IBAN_Number,
ieb.branch_id Branch_Party_Id
FROM poz_suppliers_V PO,
poz_supplier_sites_v PVSA,
hz_parties party_supp,
hz_party_sites site_supp,
iby_external_payees_all iep ,
iby_pmt_instr_uses_all ipi ,
iby_ext_bank_accounts ieb ,
hz_parties party_bank,
hz_parties party_branch,
hz_organization_profiles_v bank_prof ,
hz_organization_profiles_v branch_prof,
hr_operating_units hou
--
WHERE party_supp.party_id = po.party_id
and party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = pvsa.party_site_id
AND pvsa.vendor_id = po.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = pvsa.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
and ieb.branch_id = party_branch.party_id
and party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
AND PVSA.pay_site_flag = 'Y'
AND NVL(ieb.end_date, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND SYSDATE BETWEEN NVL(ipi.start_date,SYSDATE) AND NVL(ipi.end_date,SYSDATE)
AND NVL(PVSA.INACTIVE_DATE, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND NVL(PO.END_DATE_ACTIVE, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND NVL(bank_prof.effective_end_date, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND NVL(branch_prof.effective_end_date, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND pvsa.pay_group_lookup_code = 'NETTING SUPPLIERS'
and pvsa.prc_bu_id = hou.organization_id) AL1
No comments:
Post a Comment