AP Supplier Bank Accounts Report
SELECT
l.company co,
l.org_id,
l.vendor_name,
l.vendor_number,
l.vendor_site,
l.vndr_site_pmt_method,
l.payment_curr payment_cy_cd,
l.account_currency account_cy_cd,
l.invoice_curr invoice_cy_cd,
l.vendor_name_alt,
l.pay_group,
l.bank_name,
l.bank_number,
l.bank_country,
l.branch_name,
l.branch_number,
l.bank_account_name,
l.bank_account_number,
l.bank_account_type,
l.account_creation_date,
l.account_start_date,
l.account_end_date,
l.bank_acct_start_date,
l.bank_acct_end_date,
l.priority,
l.acct_last_updt_name,
l.acct_last_updt_id,
l.acct_last_updt_date,
l.assg_last_updt_name,
l.assg_last_updt_id,
l.assg_last_updt_date,
l.bank_account_name_alt,
l.bank_name_alt,
l.branch_name_alt,
l.iban,
l.allow_international_payments,
l.instrument_id,
l.iby_payment_method,
l.branch_address_1,
l.branch_address_2,
l.branch_city,
l.branch_state,
l.branch_postal_cd,
l.branch_province,
l.branch_country,
l.branch_country_name,
l.remit_email,
l.active_flag,
l.sup_site_inactive_date,
l.sup_hdr_inactive_date,
--sysdate timestamp
(to_char(sysdate,'mm/dd/yyyy hh:mi:ss AM')) timestamp
FROM
(
SELECT
substr((hrou.name), 1, 3) company,
prs.party_name vendor_name,
--prs.organization_name_phonetic vendor_name_alt,
hop.organization_name vendor_name_alt,
--aps.segment1 vendor_number,
poz.segment1 vendor_number,
--ass.vendor_site_code vendor_site,
pss.vendor_site_code vendor_site,
--ass.pay_group_lookup_code pay_group,
pss.pay_group_lookup_code pay_group,
--ass.invoice_currency_code invoice_curr,
pss.invoice_currency_code invoice_curr,
--ass.payment_currency_code payment_curr,
pss.payment_currency_code payment_curr,
party_bank.party_name bank_name,
--bank_prof.organization_name_phonetic bank_name_alt,
bank_prof.organization_name bank_name_alt,
bank_prof.bank_or_branch_number bank_number,
party_branch.party_name branch_name,
--party_branch.organization_name_phonetic branch_name_alt,
party_branch.organization_name branch_name_alt,
party_branch.bank_or_branch_number branch_number,
party_branch.home_country branch_country_cd,
party_branch.territory_short_name branch_country_name,
ieb.bank_account_name bank_account_name,
ieb.bank_account_name_alt bank_account_name_alt,
ieb.bank_account_num bank_account_number,
ieb.bank_account_type bank_account_type,
ieb.currency_code account_currency,
--ass.org_id org_id,
hrou.organization_id org_id,
ieb.ext_bank_account_id bank_account_id,
ieb.branch_id branch_id,
ieb.bank_id bank_id,
ieb.creation_date account_creation_date,
ieb.iban iban,
ieb.foreign_payment_use_flag allow_international_payments,
ipi.start_date account_start_date,
ipi.end_date account_end_date,
/*NVL2(ipi.end_date, 'Y','N') Inactive_Flag,*/
CASE
WHEN nvl(ipi.end_date, trunc(sysdate)) >= trunc(sysdate) THEN
'Y'
ELSE
'N'
END active_flag,
ieb.start_date bank_acct_start_date,
ieb.end_date bank_acct_end_date,
--usr1.user_name assg_last_updt_id,
psr1.user_id assg_last_updt_id,
--usr1.description assg_last_updt_name,
psr1.username assg_last_updt_name,
--usr2.user_name acct_last_updt_id,
--usr2.description acct_last_updt_name,
psr2.user_id acct_last_updt_id,
psr2.username acct_last_updt_name,
ipi.last_update_date assg_last_updt_date,
ieb.last_update_date acct_last_updt_date,
--aps.vendor_id vendor_id,
poz.vendor_id vendor_id,
--ass.vendor_site_id vendor_site_id,
pss.vendor_site_id vendor_site_id,
ipi.instrument_id instrument_id,
ipi.order_of_preference priority,
ipm.payment_method_code iby_payment_method,
-- ass.payment_method_lookup_code vndr_site_pmt_method,
aia.payment_method_lookup_code vndr_site_pmt_method,
iep.remit_advice_email remit_email,
party_branch.address1 branch_address_1,
party_branch.address2 branch_address_2,
party_branch.address3 branch_address_3,
party_branch.city branch_city,
party_branch.postal_code branch_postal_cd,
party_branch.province branch_province,
party_branch.country branch_country,
party_branch.state branch_state,
--prs.country bank_country, --change the incorrect reference ANNL 08-21-24
party_branch.home_country bank_country,
--aps.end_date_active sup_hdr_inactive_date,
poz.end_date_active sup_hdr_inactive_date,
--ass.inactive_date sup_site_inactive_date
pss.inactive_date sup_site_inactive_date
FROM
hz_parties prs,
hz_organization_profiles hop,
--ap_suppliers aps,
poz_suppliers poz,
hz_party_sites site_supp,
--ap_supplier_sites_all ass,
POZ_SUPPLIER_SITES_ALL_M pss,
ap_invoices_all aia,
iby_external_payees_all iep,
iby_pmt_instr_uses_all ipi,
iby_ext_bank_accounts ieb,
hz_parties party_bank,
(
SELECT
party_branch.address1,
party_branch.address2,
party_branch.address3,
party_branch.city,
party_branch.postal_code,
party_branch.province,
party_branch.country,
party_branch.state,
party_branch.party_name,
party_branch.party_id,
terr.territory_short_name,
--branch_prof.organization_name_phonetic,--need to check
branch_prof.organization_name,
branch_prof.bank_or_branch_number,
branch_prof.home_country
FROM
hz_parties party_branch,
hz_organization_profiles branch_prof,
fnd_territories_tl terr
WHERE
terr.territory_code (+) = branch_prof.home_country
AND terr.language (+) = 'US'
AND party_branch.party_id = branch_prof.party_id (+)
AND branch_prof.effective_end_date (+) IS NULL
) party_branch,
hz_organization_profiles bank_prof,
hr_operating_units hrou,
--fnd_user usr1,
--fnd_user usr2,
per_users psr1,
per_users psr2,
iby_ext_party_pmt_mthds ipm
WHERE
1 = 1
AND prs.party_id = poz.party_id
AND hop.party_id = prs.party_id
AND prs.party_id = site_supp.party_id
--AND site_supp.party_site_id = ass.party_site_id
AND site_supp.party_site_id = pss.party_site_id
--AND ass.vendor_id = aps.vendor_id
AND pss.vendor_id = poz.vendor_id
AND aia.vendor_id = poz.vendor_id
AND iep.payee_party_id = prs.party_id
AND iep.party_site_id = site_supp.party_site_id
--AND iep.supplier_site_id = ass.vendor_site_id
AND iep.supplier_site_id = pss.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_bank.party_id = bank_prof.party_id
--AND hrou.organization_id = ass.org_id
AND hrou.organization_id = pss.prc_bu_id
AND bank_prof.effective_end_date IS NULL
--AND usr1.user_id = ipi.last_updated_by
--AND usr2.user_id = ieb.last_updated_by
AND psr1.last_updated_by = ipi.last_updated_by
AND psr2.last_updated_by = ieb.last_updated_by
AND ipm.ext_pmt_party_id (+) = iep.ext_payee_id
AND ipm.primary_flag (+) = 'Y'
AND ipm.inactive_date (+) IS NULL
AND hrou.NAME = nvl(:p_company,hrou.name)
AND hrou.organization_id = nvl(:p_org_id,hrou.organization_id)
AND prs.party_name = nvl(:p_vendor_name,prs.party_name)
AND poz.segment1 = nvl(:p_vendor_number,poz.segment1)
AND ieb.currency_code = nvl(:p_ACCOUNT_CY_CD,ieb.currency_code)
AND ((:p_Only_Act_Bnk_Acc='Y' and nvl(ipi.end_date, trunc(sysdate)) >= trunc(sysdate))
or :p_Only_Act_Bnk_Acc='N'
)
) l
No comments:
Post a Comment