ADVANCE TO SUPPLIER AGEING QUERY
SELECT
vendor_name,
supplier_num,
SUM("current1") AS "current",
SUM("1-30 Days") AS "1-30 Days",
SUM("30-60 days") AS "30-60 days",
SUM("60-90 Days") AS "60-90 Days",
SUM("90-180 days") AS "90-180 days",
SUM("180-365 days") AS "180-365 days",
SUM("365-730 days") AS "365-730 days",
SUM("730 above") AS "730 above",
sum("current"+"1-30 Days"+"30-60 days"+"60-90 Days"+"90-180 days"+"180-365 days"+"365-730 days"+"730 above") BALANCE
FROM
(
SELECT
lineS.line_number,
MAX(ps.vendor_name) vendor_name,
MAX( ps.segment1 )supplier_num,
inv.invoice_num,
MAX(lines.line_TYPE_LOOKUP_CODE ) line_TYPE_LOOKUP_CODE,
MAX( CASE
WHEN to_char(dist1.accounting_date, 'YYYY-MM-DD') = to_char(sysdate, 'YYYY-MM-DD') THEN lines.amount
ELSE 0
END) AS "current1",
MAX( CASE
WHEN floor(sysdate -dist1.accounting_date) > 0 AND floor(sysdate - dist1.accounting_date) <= 30 THEN lines.amount
ELSE 0
END) AS "1-30 Days",
MAX( CASE
WHEN floor(sysdate - dist1.accounting_date) > 30 AND floor(sysdate -dist1.accounting_date) <= 60 THEN lines.amount
ELSE 0
END) AS "30-60 days",
MAX( CASE
WHEN floor(sysdate - dist1.accounting_date) > 60 AND floor(sysdate - dist1.accounting_date) <= 90 THEN lines.amount
ELSE 0
END) AS "60-90 Days",
MAX( CASE
WHEN floor(sysdate - dist1.accounting_date) > 90 AND floor(sysdate -dist1.accounting_date) <= 180 THEN lines.amount
ELSE 0
END) AS "90-180 days",
MAX( CASE
WHEN floor(sysdate - dist1.accounting_date) > 180 AND floor(sysdate - dist1.accounting_date) <= 365 THEN lines.amount
ELSE 0
END) AS "180-365 days",
MAX( CASE
WHEN floor(sysdate - dist1.accounting_date) > 365 AND floor(sysdate - dist1.accounting_date) <= 730 THEN lines.amount
ELSE 0
END) AS "365-730 days",
MAX( CASE
WHEN floor(sysdate - dist1.accounting_date) > 730 THEN lines.amount
ELSE 0
END) AS "730 above"
FROM
ap_invoice_lines_all lines,
ap_invoices_all inv,
poz_suppliers_v ps,
AP_INVOICE_PAYMENTS_ALL dist1,
(SELECT MAX(gcc.segment2) segment2, DIST.INVOICE_ID,MAX(dist.invoice_line_number) invoice_line_number FROM gl_code_combinations gcc, ap_invoice_distributions_all dist WHERE
dist.dist_code_combination_id = gcc.code_combination_id GROUP BY dist.invoice_line_number,DIST.INVOICE_ID
) GCC1
WHERE
dist1.invoice_id = inv.invoice_id
AND lines.invoice_id = inv.invoice_id
AND GCC1.invoice_id = inv.invoice_id
AND GCC1.invoice_line_number = lineS.line_number
AND inv.vendor_id = ps.vendor_id
AND ((INV.INVOICE_DATE) <= NVL((:Payment_Date), ((dist1.accounting_date)))
OR (INV.INVOICE_DATE) >= nvl((:Payment_Date), ((dist1.accounting_date))))
AND ps.vendor_name = NVL(:Supplier_name, ps.vendor_name)
AND ps.SEGMENT1 = NVL(:Supplier_number, ps.SEGMENT1)
AND inv.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND gcc1.segment2 = '1161xxxxx1'
GROUP BY inv.invoice_num, lineS.line_number
)
GROUP BY vendor_name, supplier_num
Comments
Post a Comment