SELECT /*Supplier Information*/
aps.segment1 oracle_supplier_number,
aps.vendor_id,
aps.vendor_name supplier_name,
aps.party_id supplier_party_id,
iepa.remit_advice_fax remit_advice_fax,
iepa.remit_advice_email remit_advice_email/* Supplier Site Information */
,
assa.vendor_site_id,
assa.party_site_id supplier_party_site_id,
assa.vendor_site_code vendor_site_code,
assa.pay_site_flag pay_site_flag,
assa.purchasing_site_flag purchasing_site_flag,
assa.rfq_only_site_flag rfq_only_site_flag/* Bank Information*/
,
ieba.ext_bank_account_id,
hp.party_name Bank_party_name,
ieba.bank_account_num bank_account_num,
ieba.bank_account_name bank_account_name,
ieba.country_code bank_acct_country_code,
ieba.currency_code bank_acct_currency_code/* Bank Address */
,
hp.address1 bank_address_line1,
hp.address2 bank_address_line2,
hp.address3 bank_address_line3,
hp.city bank_address_city,
hp.state bank_address_state,
hp.postal_code bank_address_zip,
hp.country bank_address_country/* Bank Branch Address */
,
hp1.address1 branch_address_line1,
hp1.address2 branch_address_line2,
hp1.address3 branch_address_line3,
hp1.city branch_address_city,
hp1.state branch_address_state,
hp1.postal_code branch_address_zip,
hp1.country branch_address_country
FROM ap_supplier_sites_all assa,
hz_parties hp,
iby_ext_bank_accounts ieba,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
ap_suppliers aps,
hz_parties hp1
WHERE assa.vendor_site_id = iepa.supplier_site_id
AND hp.party_id = ieba.bank_id
AND ipiua.instrument_id = ieba.ext_bank_account_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND assa.vendor_id = aps.vendor_id
AND ieba.branch_id = hp1.party_id
AND ipiua.instrument_type = 'BANKACCOUNT'
AND aps.vendor_name LIKE '%vendor_name %'
AND assa.vendor_site_id = '&vendor_site_id';
SELECT aps.vendor_name "VERDOR NAME",
apss.vendor_site_code "VENDOR SITE CODE",
ieb.bank_name "BANK NAME",
iebb.bank_branch_name "BANK BRANCH NAME",
iebb.branch_number "BRANCH NUMBER",
ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM ap.ap_suppliers aps,
ap.ap_supplier_sites_all apss,
apps.iby_ext_bank_accounts ieba,
apps.iby_account_owners iao,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_branches_v iebb
WHERE aps.vendor_id = apss.vendor_id
and iao.account_owner_party_id = aps.party_id
and ieba.ext_bank_account_id = iao.ext_bank_account_id
and ieb.bank_party_id = iebb.bank_party_id
and ieba.branch_id = iebb.branch_party_id
and ieba.bank_id = ieb.bank_party_id;
aps.segment1 oracle_supplier_number,
aps.vendor_id,
aps.vendor_name supplier_name,
aps.party_id supplier_party_id,
iepa.remit_advice_fax remit_advice_fax,
iepa.remit_advice_email remit_advice_email/* Supplier Site Information */
,
assa.vendor_site_id,
assa.party_site_id supplier_party_site_id,
assa.vendor_site_code vendor_site_code,
assa.pay_site_flag pay_site_flag,
assa.purchasing_site_flag purchasing_site_flag,
assa.rfq_only_site_flag rfq_only_site_flag/* Bank Information*/
,
ieba.ext_bank_account_id,
hp.party_name Bank_party_name,
ieba.bank_account_num bank_account_num,
ieba.bank_account_name bank_account_name,
ieba.country_code bank_acct_country_code,
ieba.currency_code bank_acct_currency_code/* Bank Address */
,
hp.address1 bank_address_line1,
hp.address2 bank_address_line2,
hp.address3 bank_address_line3,
hp.city bank_address_city,
hp.state bank_address_state,
hp.postal_code bank_address_zip,
hp.country bank_address_country/* Bank Branch Address */
,
hp1.address1 branch_address_line1,
hp1.address2 branch_address_line2,
hp1.address3 branch_address_line3,
hp1.city branch_address_city,
hp1.state branch_address_state,
hp1.postal_code branch_address_zip,
hp1.country branch_address_country
FROM ap_supplier_sites_all assa,
hz_parties hp,
iby_ext_bank_accounts ieba,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
ap_suppliers aps,
hz_parties hp1
WHERE assa.vendor_site_id = iepa.supplier_site_id
AND hp.party_id = ieba.bank_id
AND ipiua.instrument_id = ieba.ext_bank_account_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND assa.vendor_id = aps.vendor_id
AND ieba.branch_id = hp1.party_id
AND ipiua.instrument_type = 'BANKACCOUNT'
AND aps.vendor_name LIKE '%vendor_name %'
AND assa.vendor_site_id = '&vendor_site_id';
SELECT aps.vendor_name "VERDOR NAME",
apss.vendor_site_code "VENDOR SITE CODE",
ieb.bank_name "BANK NAME",
iebb.bank_branch_name "BANK BRANCH NAME",
iebb.branch_number "BRANCH NUMBER",
ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM ap.ap_suppliers aps,
ap.ap_supplier_sites_all apss,
apps.iby_ext_bank_accounts ieba,
apps.iby_account_owners iao,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_branches_v iebb
WHERE aps.vendor_id = apss.vendor_id
and iao.account_owner_party_id = aps.party_id
and ieba.ext_bank_account_id = iao.ext_bank_account_id
and ieb.bank_party_id = iebb.bank_party_id
and ieba.branch_id = iebb.branch_party_id
and ieba.bank_id = ieb.bank_party_id;
No comments:
Post a Comment