AP Supplier query with Bank and Tax details (GST & VAT)

 

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(+);