Query to fetch vendor bank details, check, invoice etc

SELECT
VOUCHER_NUMBER,
TRANSACTION_DATE,
CHECQUE_DATE,
CHECK_NUMBER,
INVOICE_NUMBER,
NAME_OF_BANK,
BANK_ACCOUNT_NUMBER,
AMOUNT_PAID,
AMOUNT_PAID_TO,
OPERATING_UNIT,
ORG_ID,
SUP_BANK,
REMIT_BANK_AC_NUM
FROM
(
SELECT DISTINCT
aia.attribute1 “VOUCHER_NUMBER”,
to_char(aia.invoice_date,’DD-MON-RRRR’) “TRANSACTION_DATE”,
to_char(aca.check_date,’DD-MON-RRRR’) “CHECQUE_DATE”,
aca.check_number “CHECK_NUMBER”,
aia.invoice_num “INVOICE_NUMBER”,
cbb.bank_name “NAME_OF_BANK”,
cba.bank_account_num “BANK_ACCOUNT_NUMBER”,
aca.amount “AMOUNT_PAID”,
asa.vendor_name “AMOUNT_PAID_TO”,
hou.name “OPERATING_UNIT”,
hou.organization_id org_id,
eb.bank_name “SUP_BANK”,
(
SELECT DISTINCT
ie.masked_bank_account_num
FROM po_vendors pv,
ap_invoices_all ap,
iby_ext_bank_accounts ie,
apps.ce_bank_branches_v cb
WHERE ap.vendor_id = pv.vendor_id
AND ap.external_bank_account_id = ie.ext_bank_account_id(+)
AND ie.branch_id = cb.branch_party_id(+)
AND ie.bank_id = cb.bank_party_id(+)
AND ap.invoice_num = aia.invoice_num
AND ap.invoice_id = aia.invoice_id
)”REMIT_BANK_AC_NUM”
FROM
ap_invoice_payments_all aipa,
ap_invoices_all aia,
ap_checks_all aca,
ap_suppliers asa,
ap_supplier_sites_all assa,
hr_operating_units hou,
ce_bank_branches_v cbb,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau,
iby_ext_bank_accounts eba ,
iby_account_owners ao ,
iby_ext_banks_v eb ,
iby_ext_bank_branches_v ebb
WHERE 1 = 1
AND UPPER(aca.vendor_site_code) <> UPPER(‘Petty Cash’)
AND aia.invoice_id = aipa.invoice_id(+)
AND aca.check_id(+) = aipa.check_id
AND aia.org_id = hou.organization_id
AND asa.vendor_id(+) = aia.vendor_id
AND asa.vendor_id = assa.vendor_id
AND cbb.branch_party_id(+) = cba.bank_branch_id
AND cbau.bank_account_id = cba.bank_account_id(+)
AND aca.ce_bank_acct_use_id = cbau.bank_acct_use_id(+)
AND ao.account_owner_party_id(+) = asa.party_id
AND eba.ext_bank_account_id(+) = ao.ext_bank_account_id
AND eba.branch_id = ebb.branch_party_id(+)
AND eba.bank_id = eb.bank_party_id(+)
and nvl(aia.invoice_num,:p_voucher_num) = nvl(:p_voucher_num,aia.invoice_num)
AND NVL(hou.name ,:P_OPERATING_UNIT) = NVL(:P_OPERATING_UNIT,hou.name )
AND aca.check_date = :CHECQUE_DATE_MAIN
GROUP BY
aia.attribute1 ,
aia.invoice_date,
aca.check_date,
aca.check_number,
aia.invoice_num,
cbb.bank_name ,
cba.bank_account_num ,
aca.amount ,
asa.vendor_name ,
hou.name ,
hou.organization_id ,
eb.bank_name ,
aia.invoice_num,
aia.invoice_id,
eba.masked_bank_account_num
)
WHERE
1 = 1
AND OPERATING_UNIT = nvl(:P_OPERATING_UNIT,OPERATING_UNIT)
GROUP BY
OPERATING_UNIT,
ORG_ID,
VOUCHER_NUMBER,
TRANSACTION_DATE,
CHECQUE_DATE,
CHECK_NUMBER,
INVOICE_NUMBER,
NAME_OF_BANK,
BANK_ACCOUNT_NUMBER,
AMOUNT_PAID,
AMOUNT_PAID_TO,
SUP_BANK,
REMIT_BANK_AC_NUM
ORDER BY
OPERATING_UNIT,
CHECQUE_DATE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s