select
(SELECT hou.NAME
FROM apps.hr_operating_units hou
WHERE 1 = 1
AND hou.organization_id = aps.org_id) ou_name,
(SELECT
paym.payment_method_code
FROM APPS.iby_ext_party_pmt_mthds paym
WHERE IEPA.ext_payee_id = paym.ext_pmt_party_id
and aps.vendor_site_id = iepa.supplier_site_id
–AND ass.supplier_site_id IS NULL
AND paym.primary_flag = ‘Y’ and rownum=1
)SITE_PAYMENT_METHOD,
aps.ORG_ID,
ass.segment1 SUPPLIER_NUMBER,
NULL “LEGACY_SUPPLIER_NUMBER”,
ass.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,
ass.VENDOR_NAME SUPPLIER_NAME,
ass.VENDOR_NAME_ALT SUPPLIER_NAME_ALT,
ass.num_1099 TAXPAYER_ID,
ass.vat_registration_num TAX_REGISTRATION_NUM,
ass.allow_awt_flag SUPPLIER_ALLOW_WITHHOLDING_TAX,
ASS.CREATION_DATE SUPPLIER_CREATION_DATE,
(select user_name from apps.fnd_user where user_id=ASS.CREATED_BY) SUPPLIER_CREATED_BY,
ASS.LAST_UPDATE_DATE SUPPLIER_LAST_UPDATE_DATE,
decode(ass.END_DATE_ACTIVE,null,’ACTIVE’,’IN ACTIVE’) “Supplier active code “,
(select user_name from apps.fnd_user where user_id=ass.LAST_UPDATED_BY) SUPPLIER_LAST_UPDATED_BY,
aps.VENDOR_SITE_ID,
aps.INACTIVE_DATE,
aps.vendor_site_code SUPPLIER_SITE_CODE,
aps.vendor_SITE_CODE_ALT SITE_CODE_ALT,
aps.allow_awt_flag SITE_ALLOW_WITHHOLDING_TAX
,APS.ADDRESS_LINE1
,APS.ADDRESS_LINE2
,APS.ADDRESS_LINE3
,APS.ADDRESS_LINE4
,APS.CITY
,APS.STATE
,APS.ZIP POST_CODE
,APS.CREATION_DATE SUPPLIER_SITE_CREATION_DATE
,APS.CREATED_BY SUPPLIER_SITE_CREATED_BY
,APS.LAST_UPDATE_DATE SUPPLIER_SITE_LAST_UPDATE_DATE
,APS.LAST_UPDATED_BY SUPPLIER_SITE_LAST_UPDATED_BY
,aps.SUPPLIER_NOTIF_METHOD SITE_PO_NOTIF_METHOD
,APS.EMAIL_ADDRESS SITE_PO_EMAIL
,Aps.PAY_GROUP_LOOKUP_CODE
,IEPA.OBJECT_VERSION_NUMBER MAX_OBJECT_VERSION
–,ieppm.payment_method_code
,iepa.DEFAULT_PAYMENT_METHOD_CODE SITE_PAYMENT_METHOD
,t.name SITE_TERMS_NAME
,IEPA.REMIT_ADVICE_DELIVERY_METHOD SITE_REMIT_ADVICE_DEL_METHOD
,iepa.remit_advice_email SITE_REMIT_ADVICE_EMAIL
–,accts.country_code BANK_COUNTRY_CODE
,decode(accts.country_code,’IN’,’IN’,’NULL’)COUNTRY_CODE
,NULL “Allow_International_Payments”
,branch.bank_name “bank_name”
,branch.bank_branch_name BANK_BRANCH_NAME
,branch.branch_number bank_branch_name
,accts.bank_account_name BANK_ACCOUNT_NAME
,accts.bank_account_num BANK_ACCOUNT_NUMBER
,accts.check_digits “check_digits”
,NULL BIC
,accts.iban “IBAN”
,uses.start_date “Account_start_date”
,uses.end_date “Account_end_date”
,accts.currency_code “Account_Currency_Code”
,accts.CREATION_DATE BANK_ACCOUNT_CREATION_DATE
,accts.LAST_UPDATE_DATE BANK_ACCOUNT_LAST_UPDATE_DATE
,accts.secondary_account_reference “SECONDARY_ACCOUNT_REFERENCE”,
aps.attribute1 po_flag
FROM apps.ap_supplier_sites_all aps,
apps.ap_suppliers ass,
apps.ap_terms t,
apps.iby_external_payees_all iepa, –bank 2
apps.iby_pmt_instr_uses_all uses, –bank 3
apps.iby_account_owners owners,–bank4
apps.iby_ext_bank_accounts accts,–bank5
apps.ce_bank_branches_v branch–bank6
WHERE 1=1– Aps.ORG_ID=2704
–and owners.primary_flag = ‘Y’
–and ass.segment1 IN (‘342255′,’345467’)–‘346873′
AND ass.end_date_active IS NULL
AND aps.inactive_date IS NULL
and aps.terms_id=t.term_id
–and owners.account_owner_party_id is not null
–and ieb.country_code=’IN’
AND owners.ext_bank_account_id = accts.ext_bank_account_id(+)
AND owners.ext_bank_account_id(+) = uses.instrument_id
AND iepa.ext_payee_id = uses.ext_pmt_party_id(+)
AND iepa.payee_party_id = owners.account_owner_party_id
–AND owners.PRIMARY_FLAG IN (‘Y’,’N’)
and uses.end_date is null
AND iepa.supplier_site_id = aps.vendor_site_id
AND ass.vendor_id = aps.vendor_id
AND branch.branch_party_id(+) = accts.branch_id
AND uses.instrument_type = ‘BANKACCOUNT’
AND iepa.payment_function in(‘PAYABLES_DISB’)
GROUP BY
aps.ORG_ID,
ass.segment1,
ass.VENDOR_TYPE_LOOKUP_CODE,
ass.VENDOR_NAME,
ass.VENDOR_NAME_ALT,
ass.num_1099,
ass.vat_registration_num ,
ass.allow_awt_flag ,
ASS.CREATION_DATE ,
ASS.CREATED_BY ,
ASS.LAST_UPDATE_DATE ,
decode(ass.END_DATE_ACTIVE,null,’ACTIVE’,’IN ACTIVE’),
ass.LAST_UPDATED_BY,
aps.VENDOR_SITE_ID,
aps.INACTIVE_DATE,
aps.vendor_site_code ,
aps.vendor_SITE_CODE_ALT ,
aps.allow_awt_flag
,APS.ADDRESS_LINE1
,APS.ADDRESS_LINE2
,APS.ADDRESS_LINE3
,APS.ADDRESS_LINE4
,APS.CITY
,APS.STATE
–,payees.DEFAULT_PAYMENT_METHOD_CODE
,iepa.DEFAULT_PAYMENT_METHOD_CODE–,ieppm.payment_method_code
,APS.ZIP
,APS.CREATION_DATE
,APS.CREATED_BY
,APS.LAST_UPDATE_DATE
,APS.LAST_UPDATED_BY
,aps.SUPPLIER_NOTIF_METHOD
,APS.EMAIL_ADDRESS
,ApS.PAY_GROUP_LOOKUP_CODE
,IEPA.OBJECT_VERSION_NUMBER
–,ieppm.payment_method_code SITE_PAYMENT_METHOD
,t.name
,IEPA.REMIT_ADVICE_DELIVERY_METHOD
,iepa.remit_advice_email
,accts.country_code
–,NULL “Allow_International_Payments”
–,bank.party_name
,branch.bank_name
,branch.bank_branch_name
,branch.branch_number
,accts.bank_account_name
,accts.bank_account_num
,accts.check_digits
–,NULL BIC
,accts.iban
,uses.start_date
,uses.end_date
,accts.currency_code
,accts.CREATION_DATE
,accts.LAST_UPDATE_DATE
,accts.secondary_account_reference
,aps.attribute1
–,GST_NUMBER
–,PAN_NUMBER
,ASS.VENDOR_ID
,IEPA.ext_payee_id
,ass.LAST_UPDATED_BY
–,paym.ext_pmt_party_id
, aps.vendor_site_id
, iepa.supplier_site_id
—order by ORG_ID
UNION
select
(SELECT hou.NAME
FROM apps.hr_operating_units hou
WHERE 1 = 1
AND hou.organization_id = aps.org_id
) ou_name,
(SELECT
paym.payment_method_code
FROM
apps.iby_external_payees_all payee,
apps.iby_ext_party_pmt_mthds paym
WHERE
payee.ext_payee_id = paym.ext_pmt_party_id
and aps.vendor_site_id = payee.supplier_site_id
–AND ass.supplier_site_id IS NULL
AND paym.primary_flag = ‘Y’ and rownum=1
)SITE_PAYMENT_METHOD,
aps.ORG_ID,ass.segment1 SUPPLIER_NUMBER,
NULL “LEGACY_SUPPLIER_NUMBER”,
ass.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,
ass.VENDOR_NAME SUPPLIER_NAME,
ass.VENDOR_NAME_ALT SUPPLIER_NAME_ALT,
ass.num_1099 TAXPAYER_ID,
ass.vat_registration_num TAX_REGISTRATION_NUM,
ass.allow_awt_flag SUPPLIER_ALLOW_WITHHOLDING_TAX,
ASS.CREATION_DATE SUPPLIER_CREATION_DATE,
(select user_name from apps.fnd_user where user_id=ASS.CREATED_BY) SUPPLIER_CREATED_BY,
ASS.LAST_UPDATE_DATE SUPPLIER_LAST_UPDATE_DATE,
decode(ass.END_DATE_ACTIVE,null,’ACTIVE’,’IN ACTIVE’) “Supplier active code “,
(select user_name from apps.fnd_user where user_id=ass.LAST_UPDATED_BY) SUPPLIER_LAST_UPDATED_BY,
aps.VENDOR_SITE_ID,
aps.INACTIVE_DATE,
aps.vendor_site_code SUPPLIER_SITE_CODE,
aps.vendor_SITE_CODE_ALT SITE_CODE_ALT,
aps.allow_awt_flag SITE_ALLOW_WITHHOLDING_TAX
,APS.ADDRESS_LINE1
,APS.ADDRESS_LINE2
,APS.ADDRESS_LINE3
,APS.ADDRESS_LINE4
,APS.CITY
,APS.STATE
,APS.ZIP POST_CODE
,APS.CREATION_DATE SUPPLIER_SITE_CREATION_DATE
,APS.CREATED_BY SUPPLIER_SITE_CREATED_BY
,APS.LAST_UPDATE_DATE SUPPLIER_SITE_LAST_UPDATE_DATE
,APS.LAST_UPDATED_BY SUPPLIER_SITE_LAST_UPDATED_BY
,aps.SUPPLIER_NOTIF_METHOD SITE_PO_NOTIF_METHOD
,APS.EMAIL_ADDRESS SITE_PO_EMAIL
,ApS.PAY_GROUP_LOOKUP_CODE
,NULL MAX_OBJECT_VERSION
,NULL SITE_PAYMENT_METHOD
–,NULL SITE_PAYMENT_METHOD1
,t.name SITE_TERMS_NAME
,NULL SITE_REMIT_ADVICE_DEL_METHOD
,NULL SITE_REMIT_ADVICE_EMAIL
–,accts.country_code BANK_COUNTRY_CODE
,NULL COUNTRY_CODE
,NULL “Allow_International_Payments”
,NULL “bank_name”
,NULL BANK_BRANCH_NAME
,NULL bank_branch_name
,NULL BANK_ACCOUNT_NAME
,NULL BANK_ACCOUNT_NUMBER
,NULL “check_digits”
,NULL BIC
,NULL “IBAN”
,NULL “Account_start_date”
,NULL “Account_end_date”
,NULL “Account_Currency_Code”
,NULL BANK_ACCOUNT_CREATION_DATE
,NULL BANK_ACCOUNT_LAST_UPDATE_DATE
,NULL “SECONDARY_ACCOUNT_REFERENCE”,
aps.attribute1 po_flag
from
apps.ap_suppliers ass,
apps.ap_supplier_sites_all aps,
apps.ap_terms t
where ass.vendor_id=aps.vendor_id
–and ass.segment1 IN (‘342255′,’345467’)
and ass.end_date_active IS NULL
AND aps.inactive_date IS NULL
AND aps.pay_site_flag = ‘Y’
and aps.terms_id=t.term_id(+);
Recent Posts
