0 Replies Latest reply: Jul 5, 2017 9:35 AM by swati moku RSS

    Oracle Payables overdue query

    swati moku

      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),

                        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),

                        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),

                        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