2 Replies Latest reply: Sep 4, 2013 11:22 AM by Ahmedov RSS

    Link between xla_distribution_links and ap_invoices_all

    Ahmedov
      SELECT SUP.VENDOR_NAME,
                    API.INVOICE_NUM,
                    SUM (NVL (AEL.ACCOUNTED_DR, 0) - NVL (AEL.ACCOUNTED_CR, 0)) "BALANCE"
      FROM xla.xla_ae_lines AEL,
               xla.xla_ae_headers AEH,
               ---------------------------------------------------------
               (SELECT   XDL.AE_HEADER_ID,
                                XDL.AE_LINE_NUM,
                                XDL.APPLIED_TO_SOURCE_ID_NUM_1,
                                XDL.ALLOC_TO_SOURCE_ID_NUM_1,
                                APSI.INVOICE_NUM "ALLOC_TO_INVOICE_NUM",
                                XDL.EVENT_CLASS_CODE
                  FROM APPS.XLA_DISTRIBUTION_LINKS XDL, AP.AP_INVOICES_ALL APSI
                 WHERE XDL.APPLICATION_ID = 200
                       AND XDL.EVENT_CLASS_CODE IN
                              ('PREPAYMENTS', 'PREPAYMENT APPLICATIONS')
                       AND XDL.ROUNDING_CLASS_CODE = 'PREPAID_EXPENSE'
                       AND XDL.ALLOC_TO_SOURCE_ID_NUM_1 = APSI.INVOICE_ID
                       GROUP BY XDL.AE_HEADER_ID,
                                XDL.AE_LINE_NUM,
                                XDL.APPLIED_TO_SOURCE_ID_NUM_1,
                                XDL.ALLOC_TO_SOURCE_ID_NUM_1,
                                APSI.INVOICE_NUM,
                                XDL.EVENT_CLASS_CODE
                       ) dl, --------------VERY IMPORTANT---------------
               -----------------------------------------------------------
               GL.GL_CODE_COMBINATIONS CC,
               AP.AP_SUPPLIERS SUP,
               AP.AP_INVOICES_ALL API
         WHERE     aeh.balance_type_code = 'A'
               AND aeh.ae_header_id = ael.ae_header_id
               ---hh
               AND aeh.ae_header_id = DL.ae_header_id
               AND ael.ae_line_num = DL.ae_line_num
               --
               AND ael.ledger_id = 2022
               AND ael.code_combination_id = CC.CODE_COMBINATION_ID
               AND DL.APPLIED_TO_SOURCE_ID_NUM_1 = API.INVOICE_ID --------------VERY IMPORTANT-----------------
               AND CC.SEGMENT1 = '131061'
               AND AEL.PARTY_ID = SUP.VENDOR_ID
               AND SUP.VENDOR_NAME = 'COMPANY_NAME'
               AND TRUNC (AEH.ACCOUNTING_DATE) <= TO_DATE ('31-DEC-2012', 'DD-MON-YYYY')
      GROUP BY API.INVOICE_NUM,  SUP.VENDOR_NAME
      HAVING          SUM (NVL (AEL.ACCOUNTED_DR, 0) - NVL (AEL.ACCOUNTED_CR, 0)) <> 0
      
      

       

      Pleaes explain the above query. The result is the prepayment balance grouped by Vendor_name and Invoice_num. What I don't understand is the use of the inline view DL. Why would you need it? I tried to rewrite the above task as following.

      select  inv.vendor_id, INV.INVOICE_NUM, sum(AEL.ACCOUNTED_DR) - sum(AEL.ACCOUNTED_CR) balance
       from xla.xla_ae_lines ael,
              xla.xla_ae_headers  aeh,
              XLA.XLA_DISTRIBUTION_LINKS xld,
              AP.AP_INVOICES_ALL inv,
              ap.ap_invoice_distributions_all dist,
              AP.AP_SUPPLIERS sup
       where ael.ae_header_id = aeh.ae_header_id AND
                  XLD.AE_HEADER_ID = aeh.ae_header_id AND
                  inv.invoice_id = dist.invoice_id AND
                  INV.vendor_id = SUP.VENDOR_ID AND
                  INV.VENDOR_ID = 407012 AND
                  AEL.APPLICATION_ID = 200 AND
                  INV.INVOICE_NUM = '30'
      Group by inv.vendor_id, INV.INVOICE_NUM
      

       

      I linked AP_INVOICES_ALL to XLA_DISTRIBUTION_LINKS with APPLIED_TO_SOURCE_ID_NUM_1. But in the first query I posted, XLA_DISTRIBUTION_LINKS and AP_INVOICES_ALL were joined twice - first in the inline view DL using ALLOC_TO_SOURCE_ID_NUM_1, then in the main query using APPLIED_TO_SOURCE_ID_NUM_1. Oracle ETRM says nothing about the usage of ALLOC_TO_SOURCE_ID_NUM_1. So I am totally confused. Any hints would be appreciated