This discussion is archived
2 Replies Latest reply: Sep 4, 2013 9:22 AM by Ahmedov RSS

Link between xla_distribution_links and ap_invoices_all

Ahmedov Newbie
Currently Being Moderated
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    

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points