http://aporaclepayables.blogspot.com/2017/01/ap-aging-query.html
begin
MO_GLOBAL.SET_POLICY_CONTEXT('S', '82');
end;
--------------------------------------------------------------
SELECT distinct i.invoice_num, i.invoice_date, k.invoice_amount, k.doc_sequence_value, k.vendor_name
,DECODE
(apps.ap_invoices_pkg.get_approval_status
(i.invoice_id,
i.invoice_amount,
i.payment_status_flag,
i.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'APPROVED', 'Validated',
'CANCELLED', 'Cancelled',
'AVAILABLE PREPAYMENT', 'Available Prepayment',
'AVAILABLE', 'Available'
) invoice_status, i.payment_status_flag "Payment Status", --'Y' -- Fully Paid, 'P' -- Partially Paid , 'N'-- Not Paid
k.posting_flag "Invoice Accounting Status" --'Y' -- Accounted, 'P' -- Partial , 'N'-- No Accounted
FROM ap_invoices_all i, Ap_Invoices_v k
where 1=1
and i.invoice_id=k.invoice_id
AND ap_invoices_pkg.get_approval_status (i.invoice_id, i.invoice_amount, i.payment_status_flag, i.invoice_type_lookup_code) in ('APPROVED','AVAILABLE')
and i.invoice_id not in(
select P.INVOICE_ID
from ap_invoice_payments_all p, AP_PAYMENT_SCHEDULES_ALL PS , AP_CHECKS_ALL C
WHERE
P.CHECK_ID=C.CHECK_ID AND
P.INVOICE_ID=PS.INVOICE_ID
AND (PS.PAYMENT_STATUS_FLAG='Y' -- Y means FULL PAID, P PARTIAL, N NOTPAID
or p.reversal_flag = 'N'
)
and c.cleared_date<='31-DEC-2016' -- added for hubco
and p.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and ps.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and c.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and p.org_id=ps.org_id -- ADDED ON 31-JUL-15
and p.org_id=c.org_id -- ADDED ON 31-JUL-15
)
-- and i.PAYMENT_STATUS_FLAG <> 'Y'
-- and k.vendor_name = 'Pakistan State Oil Company Limited'
and i.INVOICE_DATE <= '31-DEC-2016'
-- and i.invoice_id=69912
--------------------------------------------------------------------------------------------------------------------------
SELECT distinct i.invoice_num, i.invoice_date, k.invoice_amount, k.doc_sequence_value, k.vendor_name
,DECODE
(apps.ap_invoices_pkg.get_approval_status
(i.invoice_id,
i.invoice_amount,
i.payment_status_flag,
i.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'APPROVED', 'Validated',
'CANCELLED', 'Cancelled',
'AVAILABLE PREPAYMENT', 'Available Prepayment',
'AVAILABLE', 'Available'
) invoice_status, i.payment_status_flag "Payment Status", --'Y' -- Fully Paid, 'P' -- Partially Paid , 'N'-- Not Paid
k.posting_flag "Invoice Accounting Status" --'Y' -- Accounted, 'P' -- Partial , 'N'-- No Accounted
FROM ap_invoices_all i, Ap_Invoices_v k
where 1=1
and i.invoice_id=k.invoice_id
AND ap_invoices_pkg.get_approval_status (i.invoice_id, i.invoice_amount, i.payment_status_flag, i.invoice_type_lookup_code) in ('APPROVED','AVAILABLE')
and i.INVOICE_ID not in (
SELECT a.invoice_id
FROM apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c,
apps.ap_payment_schedules_all d,
apps.ap_invoice_payments_all ap,
ap_checks_all ac,
Ap_Invoices_v al,
ap_checks_v asd
WHERE a.vendor_id = b.vendor_id
AND a.vendor_site_id = c.vendor_site_id
AND b.vendor_id = c.vendor_id
AND a.invoice_id = d.invoice_id
AND ap.invoice_id = a.invoice_id
AND ac.CHECK_ID = ap.CHECK_ID
and ac.check_id=asd.check_id
and ap.check_id=asd.check_id
and a.invoice_id=al.invoice_id
and d.invoice_id=al.invoice_id
AND (d.PAYMENT_STATUS_FLAG='Y' -- Y means FULL PAID, P PARTIAL, N NOTPAID
or ap.reversal_flag = 'N')
and ac.cleared_date<='31-MAR-2016'
)
-- and ac.STATUS_LOOKUP_CODE <> 'VOIDED'
-- AND a.org_id =?
--and ac.check_number='?'
--and a.invoice_id= 1234
--AND a.pay_group_lookup_code IN ('DISTRIBUTOR')
-- AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')]
/* AND ap_invoices_pkg.get_approval_status (i.invoice_id, i.invoice_amount, i.payment_status_flag, i.invoice_type_lookup_code) in ('APPROVED','AVAILABLE')
and i.invoice_id not in(
select P.INVOICE_ID
from ap_invoice_payments_all p, AP_PAYMENT_SCHEDULES_ALL PS
, AP_CHECKS_ALL C
WHERE
P.CHECK_ID=C.CHECK_ID AND
P.INVOICE_ID=PS.INVOICE_ID
AND (PS.PAYMENT_STATUS_FLAG='Y' -- Y means FULL PAID, P PARTIAL, N NOTPAID
or p.reversal_flag = 'N'
)
and c.cleared_date<='31-MAR-2016' -- added for hubco
and p.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and ps.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and c.org_id='&P_ORG_ID' -- ADDED ON 31-JUL-15
and p.org_id=ps.org_id -- ADDED ON 31-JUL-15
and p.org_id=c.org_id -- ADDED ON 31-JUL-15
) */
-- and i.PAYMENT_STATUS_FLAG <> 'Y'
-- and k.vendor_name = 'Pakistan State Oil Company Limited'
and i.INVOICE_DATE <= '31-MAR-2016'
-- and i.invoice_id=69912
