select
party_id,
"Supplier Name", "vendor_site_code",
"Vendor Category", "Supplier Number",
"Concern Dept","Supplier Creation date",
"Supplier Inactive date" , -- "Supplier site Inactive date",
"Status",
NVL( "Payment method on Site","Payment method on Supplier") "Payment method" ,
"Account Number", "Address", "Email"
from ( select sup.party_id,
sup.vendor_name "Supplier Name",
ss.vendor_site_code "vendor_site_code",
sup.vendor_type_lookup_code "Vendor Category",
sup.segment1 "Supplier Number",
sup.attribute1 "Concern Dept",
trunc(sup.creation_date) "Supplier Creation date",
trunc(sup.end_date_active) "Supplier Inactive date",
-- trunc( ss.inactive_date) "Supplier site Inactive date",
(case when trunc(sup.end_date_active) >= sysdate then 'ACTIVE'
when sup.end_date_active is null then 'ACTIVE'
else 'INACTIVE' end) "Status",
(select rr.payment_method_code
from apps.IBY_EXT_PARTY_PMT_MTHDS rr
where rr.primary_flag = 'Y'
and rr.ext_pmt_party_id =
(select bb.ext_payee_id
from apps.iby_external_payees_all bb
where bb.org_id is null
/*This One Addedd */ and bb.party_site_id is null
and bb.payee_party_id =
(select cc.payee_party_id
from apps.iby_external_payees_all cc
where cc.org_id = ss.org_id
and cc.party_site_id = ss.party_site_id
and cc.supplier_site_id = ss.vendor_site_id))) "Payment method on Supplier",
( SELECT ieppm.payment_method_code
FROM apps.ap_supplier_sites_all assa,
apps.ap_suppliers sup1,
apps.iby_external_payees_all iepa,
apps.iby_ext_party_pmt_mthds ieppm
WHERE sup1.vendor_id = assa.vendor_id
--AND assa.pay_site_flag = 'Y'
AND assa.vendor_site_id = iepa.supplier_site_id
AND iepa.ext_payee_id = ieppm.ext_pmt_party_id
and iepa.org_id = assa.org_id
AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)
)
and sup1.vendor_id = sup.vendor_id
AND assa.vendor_site_id = ss.vendor_site_id
and iepa.org_id = ss.org_id
AND ieppm.primary_flag = 'Y'
) "Payment method on Site",
( SELECT
accts.bank_account_num
from apps.iby_pmt_instr_uses_all uses,
apps.iby_external_payees_all payee,
apps.iby_ext_bank_accounts accts,
--apps.fnd_currencies_vl fc,
apps.HZ_PARTIES bank,
apps.HZ_ORGANIZATION_PROFILES bankProfile,
apps.CE_BANK_BRANCHES_V branch
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.payee_party_id =sup.party_id
and uses.instrument_id = accts.ext_bank_account_id
--and fc.currency_code(+) = accts.currency_code
AND sysdate between NVL(accts.start_date, sysdate-1) AND
NVL(accts.end_date, sysdate+1)
AND accts.bank_id = bank.party_id(+)
AND accts.bank_id = bankProfile.party_id(+)
AND accts.branch_id = branch.branch_party_id(+)
and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and
NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)
and SYSDATE between TRUNC(uses.start_date(+)) and
NVL(TRUNC(uses.end_date(+)), SYSDATE + 1)
--and ( trunc(uses.end_date) is null or trunc(uses.end_date) >= sysdate)
--and trunc(uses.end_date) is null
and nvl(payee.org_id,693) = 693
and uses.order_of_preference = ( SELECT
min(
uses.order_of_preference)
from apps.iby_pmt_instr_uses_all uses,
apps.iby_external_payees_all payee,
apps.iby_ext_bank_accounts accts,
apps.fnd_currencies_vl fc,
apps.HZ_PARTIES bank,
apps.HZ_ORGANIZATION_PROFILES bankProfile,
apps.CE_BANK_BRANCHES_V branch
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.payee_party_id = sup.party_id
and uses.instrument_id = accts.ext_bank_account_id
and fc.currency_code(+) = accts.currency_code
AND sysdate between NVL(accts.start_date, sysdate-1) AND
NVL(accts.end_date, sysdate+1)
AND accts.bank_id = bank.party_id(+)
AND accts.bank_id = bankProfile.party_id(+)
AND accts.branch_id = branch.branch_party_id(+)
and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and
NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)
and SYSDATE between TRUNC(uses.start_date(+)) and
NVL(TRUNC(uses.end_date(+)), SYSDATE + 1)
--and ( trunc(uses.end_date) is null or trunc(uses.end_date) >= sysdate)
--and trunc(uses.end_date) is null
and nvl(payee.org_id,693) = 693 )
) "Account Number",
ss.address_line1 || ', ' || ss.address_line2 || ', ' ||ss.address_line3 "Address",
-- sup.num_1099 CNIC,
-- sup.vat_registration_num NTN,
/*
( SELECT max(accts.bank_account_name)
from iby_pmt_instr_uses_all uses,
iby_external_payees_all payee,
iby_ext_bank_accounts accts,
fnd_currencies_vl fc,
HZ_PARTIES bank,
HZ_ORGANIZATION_PROFILES bankProfile,
CE_BANK_BRANCHES_V branch
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.payee_party_id = sup.party_id
and uses.instrument_id = accts.ext_bank_account_id
and fc.currency_code(+) = accts.currency_code
AND sysdate between NVL(accts.start_date, sysdate) AND
NVL(accts.end_date, sysdate)
AND accts.bank_id = bank.party_id(+)
AND accts.bank_id = bankProfile.party_id(+)
AND accts.branch_id = branch.branch_party_id(+)
and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and
NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)
and nvl(payee.org_id,693) = 693
-- and (trunc(uses.end_date) is null or trunc(uses.end_date) not between '01-DEC-2017' and '30-NOV-2021')
) "Account title",
( SELECT max(trunc(accts.creation_date))
from iby_pmt_instr_uses_all uses,
iby_external_payees_all payee,
iby_ext_bank_accounts accts,
fnd_currencies_vl fc,
HZ_PARTIES bank,
HZ_ORGANIZATION_PROFILES bankProfile,
CE_BANK_BRANCHES_V branch
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.payee_party_id = sup.party_id
and uses.instrument_id = accts.ext_bank_account_id
and fc.currency_code(+) = accts.currency_code
AND sysdate between NVL(accts.start_date, sysdate) AND
NVL(accts.end_date, sysdate)
AND accts.bank_id = bank.party_id(+)
AND accts.bank_id = bankProfile.party_id(+)
AND accts.branch_id = branch.branch_party_id(+)
and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and
NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)
and nvl(payee.org_id,693) = 693
-- and (trunc(uses.end_date) is null or trunc(uses.end_date) not between '01-DEC-2017' and '30-NOV-2021')
) "Account Opening date",
null "Account Title",
null "Account Opening date",
(
select hcp1.phone_number
from hz_contact_points hcp1
where hcp1.owner_table_id(+) = ss.party_site_id
and hcp1.CONTACT_POINT_TYPE(+) = 'PHONE'
and hcp1.phone_line_type(+) = 'GEN'
and hcp1.status(+) = 'A'
and hcp1.owner_table_name(+) = 'HZ_PARTY_SITES'
and hcp1.primary_flag(+) = 'Y'
) "MOBILE NUMBER",
*/
(
select hcp2.email_address
from apps.hz_contact_points hcp2
where hcp2.owner_table_id(+) = ss.party_site_id
and hcp2.CONTACT_POINT_TYPE(+) = 'EMAIL'
and hcp2.status(+) = 'A'
and hcp2.owner_table_name(+) = 'HZ_PARTY_SITES'
and hcp2.primary_flag(+) = 'Y'
) "Email"
/* sup.vendor_type_lookup_code "Occupation/Services type",
trunc(sup.last_update_date) "Last Updation date",
(select user_name from fnd_user v where v.user_id = sup.created_by) "Vendor Openned By",
(select count(b.invoice_id)
from ap_checks_all a, ap_invoice_payments_all b
where a.check_id = b.check_id
-- and ( a.void_date is null or trunc(a.void_date) not between '01-DEC-2017' and '30-NOV-2021')
and a.vendor_id = sup.vendor_id
and a.vendor_site_id = ss.vendor_site_id and a.org_id = ss.org_id
) "Total Number of purchases",
(
select sum(b.amount)
from ap_checks_all a, ap_invoice_payments_all b
where a.check_id = b.check_id
-- and ( a.void_date is null or trunc(a.void_date) not between '01-DEC-2017' and '30-NOV-2021')
and a.vendor_id = sup.vendor_id
and a.vendor_site_id = ss.vendor_site_id and a.org_id = ss.org_id
) "Total Payment received",
null "CAAML Status"*/
FROM apps.ap_suppliers sup, apps.ap_supplier_sites_all ss
WHERE sup.vendor_id = ss.vendor_id
--and trunc(sup.creation_date) between '01-DEC-2017' and '30-NOV-2021'
--and sup.vendor_name ='NOOR ALAM'
and ss.org_id = 693
order by sup.segment1 desc
)
