Payable Aging Report Query

SELECT org_name,


vendor_name,

vendor_number,

vendor_site_details,

invoice_number,

invoice_date,

gl_Date,

invoice_type,

due_date,

past_due_days,

amt_due_remaining,

CASE

WHEN past_due_days >= -999 AND past_due_days < 0 THEN

amt_due_remaining

ELSE

0

END CURRENT_BUCKET,

CASE

WHEN past_due_days >= 0 AND past_due_days <= 30 THEN

amt_due_remaining

ELSE

0

END BUCKET_0_30,

CASE

WHEN past_due_days > 30 AND past_due_days <= 60 THEN

amt_due_remaining

ELSE

0

END BUCKET_31_60,

CASE

WHEN past_due_days > 60 AND past_due_days <= 90 THEN

amt_due_remaining

ELSE

0

END BUCKET_61_90,

CASE

WHEN past_due_days > 90 AND past_due_days <= 120 THEN

amt_due_remaining

ELSE

0

END BUCKET_91_120,

CASE

WHEN past_due_days > 120 AND past_due_days <= 999999 THEN

amt_due_remaining

ELSE

0

END GREATER_THAN_120

FROM (SELECT hou.name org_name,

pv.vendor_name vendor_name,

pv.segment1 vendor_number,

pvs.vendor_site_code

' '

pvs.city

' '

state vendor_site_details,

i.invoice_num invoice_number,

i.payment_status_flag,

i.invoice_type_lookup_code invoice_type,

i.invoice_date Invoice_Date,

i.gl_date Gl_Date,

ps.due_date Due_Date,

(CEIL(SYSDATE - ps.due_date)) past_due_days, -- DAYS_DUE,

DECODE(i.invoice_currency_code,

'USD',

DECODE(0,

0,

ROUND(((NVL(ps.amount_remaining, 0) /

(NVL(i.payment_cross_rate, 1))) *

NVL(i.exchange_rate, 1)),

2),

ROUND(((NVL(ps.amount_remaining, 0) /

(NVL(i.payment_cross_rate, 1))) *

NVL(i.exchange_rate, 1)) / 0) * 0),

DECODE(i.exchange_rate,

NULL,

0,

DECODE(0,

0,

ROUND(((NVL(ps.amount_remaining, 0) /

(NVL(ps.payment_cross_rate, 1))) *

NVL(i.exchange_rate, 1)),

2),

ROUND(((NVL(ps.amount_remaining, 0) /

(NVL(i.payment_cross_rate, 1))) *

NVL(i.exchange_rate, 1)) / 0) * 0))) amt_due_remaining

FROM ap_payment_schedules_all ps,

ap_invoices_all i,

ap_suppliers pv,

ap_supplier_sites_all pvs,

ap_lookup_codes alc1,

hr_operating_units hou

WHERE i.invoice_id = ps.invoice_id

AND i.vendor_id = pv.vendor_id

AND i.vendor_site_id = pvs.vendor_site_id

AND i.org_id = hou.organization_id

AND i.cancelled_date IS NULL

AND ps.amount_remaining = 0

AND (NVL(ps.amount_remaining, 0) * NVL(i.exchange_rate, 1)) != 0

AND i.payment_status_flag IN ('N', 'P')

AND alc1.lookup_type(+) = 'INVOICE TYPE'

AND alc1.lookup_code(+) = i.invoice_type_lookup_code

--and i.INVOICE_NUM ='358908411'

AND ap_invoices_pkg.get_approval_status(i.invoice_id,

i.invoice_amount,

ps.payment_status_flag,

invoice_type_lookup_code) in

('APPROVED', 'NEEDS REAPPROVAL'))

-- AND i.org_id = fnd_profile.VALUE ('ORG_ID'))

ORDER BY 2, 6;