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,
  ---yash---
  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
--yash-21-jan--
--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'),
--yash---
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
--yash--
,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
--yeswanth---start
,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(+)