SELECT a.vendor_name,
a.vendor_num,
a.VENDOR_ID,
NVL (SUM (a.pay_amt_dr), 0) NET_DR_CB,
NVL (SUM (a.inv_amt_cr), 0) NET_CR_CB,
(SELECT NVL (SUM (inv.invoice_amount * NVL (inv.exchange_rate, 1)), 0)
FROM ap_invoices_all inv
WHERE inv.vendor_id = a.VENDOR_ID
AND inv.gl_date < TO_DATE (:P_FROM_DATE, 'DD-MON-YYYY'))
NET_CR_OB,
(SELECT NVL (SUM (chk.amount * NVL (chk.exchange_rate, 1)), 0)
FROM ap_checks_all chk
WHERE chk.vendor_id = a.VENDOR_ID
AND chk.check_date < TO_DATE (:P_FROM_DATE, 'DD-MON-YYYY'))
NET_DR_OB,
( (SELECT NVL (SUM (inv.invoice_amount * NVL (inv.exchange_rate, 1)),
0)
FROM ap_invoices_all inv
WHERE inv.vendor_id = a.VENDOR_ID
AND inv.gl_date < TO_DATE (:P_FROM_DATE, 'DD-MON-YYYY'))
- (SELECT NVL (SUM (chk.amount * NVL (chk.exchange_rate, 1)), 0)
FROM ap_checks_all chk
WHERE chk.vendor_id = a.VENDOR_ID
AND chk.check_date < TO_DATE (:P_FROM_DATE, 'DD-MON-YYYY')))
NET_OB_BAL,
( ( (SELECT NVL (
SUM (inv.invoice_amount * NVL (inv.exchange_rate, 1)),
0)
FROM ap_invoices_all inv
WHERE inv.vendor_id = a.VENDOR_ID
AND inv.gl_date < TO_DATE (:P_FROM_DATE, 'DD-MON-YYYY'))
- (SELECT NVL (SUM (chk.amount * NVL (chk.exchange_rate, 1)), 0)
FROM ap_checks_all chk
WHERE chk.vendor_id = a.VENDOR_ID
AND chk.check_date <
TO_DATE (:P_FROM_DATE, 'DD-MON-YYYY')))
+ NVL (SUM (a.inv_amt_cr), 0)
- NVL (SUM (a.pay_amt_dr), 0))
NET_CB_BAL
FROM (SELECT 1 SEQ,
inv.gl_date TRX_DATE,
inv.invoice_num INV_NUM,
NULL PAYMENT_NUM,
sup.vendor_name,
sup.segment1 vendor_num,
INV.invoice_amount * NVL (inv.exchange_rate, 1) INV_AMT_CR,
NULL PAY_AMT_DR,
inv.DESCRIPTION DESCRIPTION,
inv.INVOICE_CURRENCY_CODE Currency,
(SELECT ALC1.DISPLAYED_FIELD INVOICE_TYPE
FROM AP_LOOKUP_CODES ALC1
WHERE ALC1.LOOKUP_TYPE = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE = INV.INVOICE_TYPE_LOOKUP_CODE)
INVOICE_TYPE,
DECODE (payment_status_flag,
'N', 'Un Paid',
'P', 'Partial Paid',
'Y', 'Paid')
payment_status,
inv.attribute12 VEN_INV_NO,
inv.VENDOR_ID
FROM ap_invoices_all inv, ap_suppliers sup
WHERE inv.VENDOR_ID = sup.vendor_id
AND inv.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
--and inv.invoice_id = invl.invoice_id
---------Parameters-----------------------------------
AND (sup.vendor_id = :P_VENDOR_ID OR :P_VENDOR_ID IS NULL)
AND (inv.gl_DATE BETWEEN TO_DATE (:P_FROM_DATE, 'DD-MON-YYYY')
AND TO_DATE (:P_TO_DATE, 'DD-MON-YYYY'))
AND ( DECODE (ap_invoices_pkg.get_approval_status (
inv.invoice_id,
inv.invoice_amount,
inv.payment_status_flag,
inv.INVOICE_TYPE_LOOKUP_CODE),
'APPROVED', 'Y',
'N') = :P_VALIDATED
OR :P_VALIDATED IS NULL)
AND ( ap_invoices_pkg.get_posting_status (inv.invoice_id) =
:P_ACCOUNTED
OR :P_ACCOUNTED IS NULL)
AND ( DECODE (
ap_invoices_pkg.get_wfapproval_status (
inv.invoice_id,
inv.org_id),
'APPROVED', 'Y',
'MANUALLY APPROVED', 'Y',
'N') = :P_APPROVED
OR :P_APPROVED IS NULL)
----------------------------------------------------------------------------
--order by inv.invoice_num
UNION ALL
SELECT DISTINCT
2 seq,
chk.CHECK_DATE TRX_DATE,
inv.invoice_num INV_NUM,
chk.CHECK_NUMBER PAYMENT_NUM,
sup.vendor_name,
sup.segment1 vendor_num,
NULL INV_AMT_CR,
inv_chk.amount * NVL (chk.exchange_rate, 1) PAY_AMT_DR,
NVL (chk.DESCRIPTION, inv.description) DESCRIPTION,
chk.CURRENCY_CODE currency,
(SELECT ALC1.DISPLAYED_FIELD INVOICE_TYPE
FROM AP_LOOKUP_CODES ALC1
WHERE ALC1.LOOKUP_TYPE = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE = INV.INVOICE_TYPE_LOOKUP_CODE)
INVOICE_TYPE,
DECODE (chk.STATUS_LOOKUP_CODE, 'VOIDED', 'Void', 'Paid')
payment_status,
inv.attribute12 VEN_INV_NO,
inv.VENDOR_ID
FROM ap_invoices_all inv,
ap_suppliers sup,
ap_invoice_payments_all inv_chk,
ap_checks_all chk
WHERE inv.VENDOR_ID = sup.vendor_id
AND inv.invoice_id = inv_chk.invoice_id
AND chk.check_id = inv_chk.CHECK_ID
---------Parameters-----------------------------------
AND (sup.vendor_id = :P_VENDOR_ID OR :P_VENDOR_ID IS NULL)
AND (chk.check_date BETWEEN TO_DATE (:P_FROM_DATE,
'DD-MON-YYYY')
AND TO_DATE (:P_TO_DATE,
'DD-MON-YYYY'))
AND ( AP_CHECKS_PKG.GET_POSTING_STATUS (chk.CHECK_ID) =
:P_ACCOUNTED
OR :P_ACCOUNTED IS NULL)) a
GROUP BY a.vendor_name, a.vendor_num, a.VENDOR_ID