Active Suppliers with Payment terms and Payment Methods in EBS R12
Business team or the vendor team will look for the active vendors which are associated with the payment terms and the payment methods very often. Since payment terms and payment methods are very crucial in payables module and they will get automatically derived from the supplier and supplier site.. we or business should maintain the accurate information in the application. else wrong payment terms can lead to have the wrong due dates on the invoice and will be excluded or included early for the payment and which may cause issues with the suppliers.. hence we need to make sure everything is accurate at the supplier and supplier site level..
Also, a major thing is Payment Method will also get defaulted from the supplier site, hence we need to monitor the supplier data freequently, if we have the wrong payment method at the supplier may defaulted to invoices and during the payment it may be an issue.. hence VMD (vendor Master data management team) will always look for this data very often..
We can use the below script to get the data of active suppliers along with their Payment terms and the payment method information..
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’
)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
,ASS.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”
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 Aps.ORG_ID IN (82)
–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
–hr.name ,
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
,ASS.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
–,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
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’
)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
,ASS.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”
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(+)
and Aps.ORG_ID IN (82)
AND NOT Exists(SELECT 1 FROM apps.iby_external_payees_all payees, –bank 2
apps.iby_pmt_instr_uses_all instrument, –bank 3
apps.iby_account_owners owners–bank4
–apps.iby_ext_bank_accounts ieb,–bank5
–apps.ce_bank_branches_v cbbv–bank6
