Oracle Payables overdue query - Qlik Community

swatimoku · ‎2017-07-05

Following is the oracle query to get the overdues amount. Please help me out with the flow of the query and important fields to get the overdues amount:

SELECT

SUM(O.NET_AMT) O_AMT,

SUM(DECODE(SIGN((TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY'))),

1,

DECODE(SIGN(16 - ((TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY')))),

1,

NVL(O.NET_AMT, 0),

0),

0)) INVAMT_00TO15,

SUM(DECODE(SIGN(TO_DATE(:AS_OF_DATE) -

TO_DATE(APS.DUE_DT, 'DD-MON-YY')),

1,

DECODE(SIGN(15 - (TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY'))),

-1,

DECODE(SIGN(31 - (TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY'))),

1,

NVL(O.NET_AMT, 0),

0),

0)) INVAMT_16TO30,

SUM(DECODE(SIGN(TO_DATE(:AS_OF_DATE) -

TO_DATE(APS.DUE_DT, 'DD-MON-YY')),

1,

DECODE(SIGN(30 - (TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY'))),

-1,

DECODE(SIGN(61 - (TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY'))),

1,

NVL(O.NET_AMT, 0),

0),

0)) INVAMT_31TO60,

SUM(DECODE(SIGN(TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY')),

1,

DECODE(SIGN(60 - (TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY'))),

-1,

DECODE(SIGN(91 - (TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY'))),

1,

NVL(O.NET_AMT, 0),

0),

0)) INVAMT_61TO90,

SUM(DECODE(SIGN((TO_DATE(:AS_OF_DATE, 'DD-MON-YY') -

TO_DATE(APS.DUE_DT, 'DD-MON-YY')) - 90),

1,

NVL(O.NET_AMT, 0),

0)) INVAMT_91TOABOVE

FROM (

SELECT

'STDINV' TRN_TYP,

(NVL(AEL.ACCOUNTED_DR, 0) - NVL(AEL.ACCOUNTED_CR, 0)) NET_AMT

FROM AP_INVOICES_ALL ,

AP_SUPPLIERS C,

GL_CODE_COMBINATIONS D,

XLA_EVENTS AE,

XLA_AE_HEADERS AEH,

XLA_AE_LINES AEL,

XLA.XLA_TRANSACTION_ENTITIES

WHERE A.VENDOR_ID = C.VENDOR_ID AND

AEL.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID AND

XTE.LEDGER_ID = 2021 AND XTE.APPLICATION_ID = 200 AND

AEL.APPLICATION_ID = AEH.APPLICATION_ID AND

AEL.AE_HEADER_ID = AEH.AE_HEADER_ID AND

XTE.APPLICATION_ID = AEH.APPLICATION_ID AND

XTE.ENTITY_ID = AEH.ENTITY_ID AND

XTE.ENTITY_CODE = 'AP_INVOICES' AND

XTE.SOURCE_ID_INT_1 = A.INVOICE_ID AND

AEH.APPLICATION_ID = AE.APPLICATION_ID AND

AE.EVENT_ID = AEH.EVENT_ID AND

AE.ENTITY_ID = AEH.ENTITY_ID AND

AEH.AE_HEADER_ID = AEL.AE_HEADER_ID AND AEL.LEDGER_ID = 2021 AND

AEL.ACCOUNTING_CLASS_CODE = 'LIABILITY' AND

A.INVOICE_TYPE_LOOKUP_CODE != 'PREPAYMENT' AND

AEL.ACCOUNTING_DATE <= TO_DATE(:AS_OF_DATE) AND

D.SEGMENT2 BETWEEN :FROM_PCSC AND :TO_PCSC AND

AEH.GL_TRANSFER_STATUS_CODE = 'Y'

UNION ALL

SELECT 'PPAPPLY' TRN_TYP,

(NVL(AEL.ACCOUNTED_DR, 0) - NVL(AEL.ACCOUNTED_CR, 0)) NET_AMT

FROM AP_INVOICES_ALL A,

AP_SUPPLIERS C,

GL_CODE_COMBINATIONS D,

XLA_EVENTS AE,

XLA_AE_HEADERS AEH,

XLA_AE_LINES AEL,

XLA.XLA_TRANSACTION_ENTITIES XTE,

(SELECT AIA.INVOICE_NUM PPINVNO,

AIA.INVOICE_ID PPAPPL_INVID,

AIA.DOC_SEQUENCE_VALUE PPDOCNO,

APH.INVOICE_ID PPINV_ID,

APH.ACCOUNTING_EVENT_ID ,

AIA.INVOICE_TYPE_LOOKUP_CODE

FROM AP_PREPAY_HISTORY_ALL APH, AP_INVOICES_ALL AIA

WHERE APH.PREPAY_INVOICE_ID = AIA.INVOICE_ID AND

APH.ACCOUNTING_DATE <= TO_DATE(:AS_OF_DATE)) AI

WHERE 1 = 1 AND AI.PPINV_ID = A.INVOICE_ID AND

AI.PPEVENT_ID = AEH.EVENT_ID AND A.VENDOR_ID = C.VENDOR_ID AND

AEL.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID AND

XTE.LEDGER_ID = 2021 AND XTE.APPLICATION_ID = 200 AND

AEL.APPLICATION_ID = AEH.APPLICATION_ID AND

XTE.APPLICATION_ID = AEH.APPLICATION_ID AND

AEH.APPLICATION_ID = AE.APPLICATION_ID AND

AEL.AE_HEADER_ID = AEH.AE_HEADER_ID AND

XTE.ENTITY_CODE = 'AP_INVOICES' AND

XTE.SOURCE_ID_INT_1 = A.INVOICE_ID AND

XTE.ENTITY_ID = AEH.ENTITY_ID AND AE.EVENT_ID = AEH.EVENT_ID AND

AE.ENTITY_ID = AEH.ENTITY_ID AND

AEH.AE_HEADER_ID = AEL.AE_HEADER_ID AND AEL.LEDGER_ID = 2021 AND

AEL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE', 'ROUNDING') AND

A.INVOICE_TYPE_LOOKUP_CODE != 'PREPAYMENT' AND

AEL.ACCOUNTING_DATE <= TO_DATE(:AS_OF_DATE) AND

D.SEGMENT2 BETWEEN :FROM_PCSC AND :TO_PCSC AND

AEH.GL_TRANSFER_STATUS_CODE = 'Y'

UNION ALL

SELECT 'PAID' TRN_TYP,

(((DECODE(SIGN(ABS(AIPA.AMOUNT) - .01), -- Mantis Ticket no: 38411

-1,

0,

AIPA.AMOUNT /

DECODE(SIGN(ABS(FDIST_AMT) - .01), -1, 1, FDIST_AMT)))) * -- Mantis Ticket no: 38411

DIST_AMT) * NVL(AIA.EXCHANGE_RATE, 1) NET_AMT,

FROM AP_INVOICES_ALL AIA,

--

(SELECT CHECK_ID,

INVOICE_ID,

ACCOUNTING_DATE,

SUM(AMOUNT) AMOUNT

FROM AP_INVOICE_PAYMENTS_ALL

GROUP BY CHECK_ID, INVOICE_ID, ACCOUNTING_DATE) AIPA,

--

( --

SELECT AID.INVOICE_ID INV_ID, SUM(AID.AMOUNT) DIST_AMT

FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,

GL_CODE_COMBINATIONS GC

WHERE 1 = 1 AND

AID.DIST_CODE_COMBINATION_ID = GC.CODE_COMBINATION_ID AND

GC.SEGMENT2 BETWEEN :FROM_PCSC AND :TO_PCSC AND

AID.ACCOUNTING_DATE <= TO_DATE(:AS_OF_DATE)

GROUP BY AID.INVOICE_ID --

) DST,

--

(SELECT AID.INVOICE_ID INV_ID, SUM(AID.AMOUNT) FDIST_AMT

FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,

GL_CODE_COMBINATIONS GC

WHERE 1 = 1 AND

AID.DIST_CODE_COMBINATION_ID = GC.CODE_COMBINATION_ID AND

AID.ACCOUNTING_DATE <= TO_DATE(:AS_OF_DATE)

GROUP BY AID.INVOICE_ID --

) FDST,

--

( --

SELECT

A.CHECK_ID CHK_ID,

A.AMOUNT CHK_AMT,

C.SEGMENT1 VND_CD,

C.VENDOR_NAME,

C.VENDOR_TYPE_LOOKUP_CODE,

A.VENDOR_ID VND_ID,

SUM(NVL(AEL.ACCOUNTED_DR, 0) - NVL(AEL.ACCOUNTED_CR, 0)) NET_AMT_AEL,

XTE.TRANSACTION_NUMBER CHK_NO,

(AEH.DOC_SEQUENCE_VALUE) DOC_NO,

A.STATUS_LOOKUP_CODE CHQ_STATUS

FROM AP_CHECKS_ALL A,

AP_SUPPLIERS C,

GL_CODE_COMBINATIONS D,

XLA_EVENTS AE,

XLA_AE_HEADERS AEH,

XLA_AE_LINES AEL,

XLA.XLA_TRANSACTION_ENTITIES XTE

WHERE A.VENDOR_ID = C.VENDOR_ID AND

AEL.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID AND

XTE.LEDGER_ID = 2021 AND XTE.APPLICATION_ID = 200 AND

AEL.APPLICATION_ID = AEH.APPLICATION_ID AND

XTE.APPLICATION_ID = AEH.APPLICATION_ID AND

AEH.APPLICATION_ID = AE.APPLICATION_ID AND

AEL.AE_HEADER_ID = AEH.AE_HEADER_ID AND

XTE.ENTITY_CODE = 'AP_PAYMENTS' AND

XTE.SOURCE_ID_INT_1 = A.CHECK_ID AND

XTE.ENTITY_ID = AEH.ENTITY_ID AND

AE.ENTITY_ID = AEH.ENTITY_ID AND

AE.EVENT_ID = AEH.EVENT_ID AND

AEH.AE_HEADER_ID = AEL.AE_HEADER_ID AND

AEL.LEDGER_ID = 2021 AND

AEL.ACCOUNTING_CLASS_CODE IN

('PREPAID_EXPENSE', 'LIABILITY', 'ROUNDING') AND

AEL.ACCOUNTING_DATE <= TO_DATE(:AS_OF_DATE) AND

D.SEGMENT2 BETWEEN :FROM_PCSC AND :TO_PCSC AND

AEH.GL_TRANSFER_STATUS_CODE = 'Y'

GROUP BY A.CHECK_ID,

A.AMOUNT,

C.SEGMENT1,

C.VENDOR_NAME,

C.VENDOR_TYPE_LOOKUP_CODE,

A.VENDOR_ID,

XTE.TRANSACTION_NUMBER,

(AEH.DOC_SEQUENCE_VALUE),

A.STATUS_LOOKUP_CODE

) AG

WHERE AIPA.CHECK_ID = AG.CHK_ID AND

AIPA.INVOICE_ID = AIA.INVOICE_ID AND

AIPA.INVOICE_ID = DST.INV_ID AND

AIPA.INVOICE_ID = FDST.INV_ID AND

AIA.INVOICE_ID IN

(SELECT AIL.INVOICE_ID

FROM AP_INVOICE_LINES_ALL AIL,

AP_INVOICE_DISTRIBUTIONS_ALL AID,

GL_CODE_COMBINATIONS GC

WHERE 1 = 1 AND AIL.INVOICE_ID = AID.INVOICE_ID AND

AID.DIST_CODE_COMBINATION_ID = GC.CODE_COMBINATION_ID AND

GC.SEGMENT2 BETWEEN :FROM_PCSC AND :TO_PCSC) AND

AIPA.ACCOUNTING_DATE <= TO_DATE(:AS_OF_DATE)

) O,

AP_INVOICES_ALL I,

gl_code_combinations_kfv gc,

(SELECT INVOICE_ID, MAX(DUE_DATE) DUE_DT

FROM AP_PAYMENT_SCHEDULES_ALL

GROUP BY INVOICE_ID) APS -- ADDED

WHERE I.INVOICE_ID = O.INV_ID AND I.INVOICE_ID = APS.INVOICE_ID -- ADDED

AND gc.code_combination_id =i.accts_pay_code_combination_idF

GROUP BY O.VNDID,

O.VND_CD,

O.VENDOR_NAME,

O.VENDOR_TYPE_LOOKUP_CODE,

O.INV_ID,

O.INV_NO,

I.INVOICE_DATE,

I.TERMS_DATE,

APS.DUE_DT,

O.OTHPCSC_PMT,

O.OTHPCSC_INV,

gc.segment4,---------------mantis ticket : 45714

I.GL_DATE,

I.INVOICE_CURRENCY_CODE,

I.INVOICE_TYPE_LOOKUP_CODE,

I.DOC_CATEGORY_CODE,

I.DOC_SEQUENCE_VALUE

HAVING ABS(SUM(O.NET_AMT)) > 0.009

ORDER BY O.VND_CD, O.INV_NO