AR-->SLA-->GL

A query which i recently developed, this might have been there already , but i still want to keep it in my blog and hope it can help someone in need.
SELECT lgd.amount          gl_posted_amount
      ,ctl.extended_amount ar_trx_amount ar_trx_amount
      , ctl.description   inv_item_desc ,        gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8  gl_account
      , gp.period_name
       ,cta.TRX_NUMBER   Trx_number
       ,ctl.INTERFACE_LINE_ATTRIBUTE1 Sales_Order_Number
      ,ll.line_number Sales_order_line_number
      ,CTT.NAME TRANSACTION_TYPE
      ,FLV.MEANING RETURN_REASON_CODE
      ,XDL.UNROUNDED_ACCOUNTED_CR
      ,XDL.UNROUNDED_ACCOUNTED_DR
      ,XDL.UNROUNDED_ENTERED_CR
      ,XDL.UNROUNDED_ENTERED_DR
      ,XAL.GL_SL_LINK_ID
      ,H.JE_CATEGORY JOURNAL_CATEGORY 
      ,H.JE_SOURCE   JOURNAL_SOURCE
      ,l.ACCOUNTED_CR JE_LINES_ACCOUNTED_CR
      ,l.ACCOUNTED_DR JE_LINES_ACCOUNTED_DR
      ,l.DESCRIPTION  JE_LINES_DESCRIPTION
      ,l.ENTERED_CR   JE_LINES_ENTERED_CR
      ,l.ENTERED_DR   JE_LINES_ENTERED_DR
      ,H.NAME       JOURNAL_NAME
      ,B.NAME       JOURNAL_BATCH_NAME
      ,L.JE_LINE_NUM    JOURNAL_LINE_NUMBER

FROM    RA_CUST_TRX_LINE_GL_DIST_ALL LGD
      , RA_CUSTOMER_TRX_LINES_ALL CTL
      , GL_CODE_COMBINATIONS GCC
      , GL_PERIODS GP
      -- Tables for Return Reason Codes
      ,OE_ORDER_HEADERS_ALL H,
       OE_ORDER_LINES_ALL LL
      ,FND_LOOKUP_TYPES_VL FLT
      ,FND_LOOKUP_VALUES_VL FLV
      ,RA_CUSTOMER_TRX_ALL CTA
      ,RA_CUST_TRX_TYPES_ALL CTT
--       XLA Tables
      ,XLA_DISTRIBUTION_LINKS XDL
      ,XLA_AE_LINES XAL
      ,XLA_AE_HEADERS XAH
      ,XLA_EVENTS XLE
      ,XLA_TRANSACTION_ENTITIES XTE
      -- GL Tables
      ,GL_CODE_COMBINATIONS_KFV GLCC
      ,GL_IMPORT_REFERENCES GIR
      ,GL_JE_HEADERS h
      ,GL_JE_LINES l
      ,GL_JE_BATCHES b
     
WHERE   LGD.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID
    AND LGD.CODE_COMBINATION_ID  = GCC.CODE_COMBINATION_ID
    AND LGD.GL_POSTED_DATE BETWEEN DATE1 andDATE2
AND LGD.SET_OF_BOOKS_ID = SOB_ID
    AND LGD.ORG_ID          =---- Org_id----
    AND gp.period_set_name  = 'GL_CALENDAR'
    AND GP.PERIOD_YEAR      = Present Year
    AND LGD.GL_POSTED_DATE BETWEEN GP.START_DATE AND GP.END_DATE
    AND cta.CUST_TRX_TYPE_ID    =   ctt.CUST_TRX_TYPE_ID
    AND CTA.CUSTOMER_TRX_ID     =   LGD.CUSTOMER_TRX_ID
    AND CTL.INTERFACE_LINE_ATTRIBUTE1 =  TO_CHAR(H.ORDER_NUMBER)
    AND CTL.INTERFACE_LINE_ATTRIBUTE6 =  TO_CHAR (LL.LINE_ID)  
    and flt.lookup_type = 'CREDIT_MEMO_REASON'
    AND FLV.END_DATE_ACTIVE IS NULL
    AND FLT.LOOKUP_TYPE = FLV.LOOKUP_TYPE
    AND LL.RETURN_REASON_CODE = FLV.LOOKUP_CODE   
    and xdl.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
    AND LGD.CUST_TRX_LINE_GL_DIST_ID =xdl.SOURCE_DISTRIBUTION_ID_NUM_1  
    AND lgd.CODE_COMBINATION_ID     =   xal.code_combination_id
    AND XAL.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
    AND    XDL.EVENT_ID    =   XLE.EVENT_ID
    AND    XDL.EVENT_ID    =   XAH.EVENT_ID
    AND    XDL.AE_HEADER_ID    =   XAH.AE_HEADER_ID
    AND    XDL.AE_HEADER_ID    =   XAL.AE_HEADER_ID
    AND    XAH.ENTITY_ID       =   XTE.ENTITY_ID(+)
    AND    XAL.GL_SL_LINK_ID   =   GIR.GL_SL_LINK_ID
    AND    XAL.GL_SL_LINK_TABLE=   GIR.GL_SL_LINK_TABLE
    AND    H.JE_BATCH_ID       =   B.JE_BATCH_ID
    AND    L.JE_HEADER_ID    =   H.JE_HEADER_ID   
    AND    L.JE_LINE_NUM     =   GIR.JE_LINE_NUM
    AND    L.JE_HEADER_ID    =   GIR.JE_HEADER_ID

The Key Join i felt is
and xdl.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
    AND LGD.CUST_TRX_LINE_GL_DIST_ID =xdl.SOURCE_DISTRIBUTION_ID_NUM_1 
Couple of  images i copied  from web for better Understanding

Comments

Post a Comment

Popular posts from this blog

Create attachments and attach them to Sales Order Header or Order Lines

Use the API below to create the attachment. This could be a limited text that you want on the order header or line. OE_FND_ATTACHMENTS_PUB.CREATE_SHORT_TEXT_DOCUMENT (1.0, --version P_SHORT_TEXT, --Text to be added as attachment P_CATEGORY_ID, -- Attachment category id P_DESCRIPTION, -- Attachment description NULL, V_SECURITY_ID, --The value is 4 for no security NULL, 'Y', 'O', -- One time usage SYSDATE, NULL, NULL, V_DOCUMENT_ID , -- Document id returned by the API V_RETURN_STATUS_CS, V_MSG_COUNT_CS, V_MSG_DATA_CS); Pass V_DOCUMENT_ID from above API into below second API OE_FND_ATTACHMENTS_PUB.ADD_ATTACHMENT (1.0, P_ENTITY_NAME, --Value of this parameter would be 'OE_ORDER_HEADERS' for header level attachment -- and 'OE_ORDER_LINES' for line level attachment P_PK1_VALUE, --line id or header id (For line level or header level attachment) NULL, NULL, NULL, NULL, 'N', NULL, V_DOCUMENT_ID , --Document id from above ap...
Read more

Link XLA Tables With AR Tables To Get Information From GL

There is   no one-to-one mapping between AR, XLA and GL. The data that actually posts to the GL is the XLA table data, not AR. Depending on the AAD rules you have defined, one row that appears in AR could become 10 in XLA_DISTRIBUTION_LINKS, but when the data is posted into the GL, the accounts of the same type and ccid are merged to a single entry. For example, a transaction could have 34 gl_dist rows, but 201 rows in ar_distribution_links, but when it actually posts, consolidates to 9 rows in gl_import_references. The main link to bind information together is the gl_sl_link_id. It will show in the gl_je_lines, gl_import_refernces and xla_ae_lines table. Also, the XLA_DISTRIBUTION_LINKS table contains the application_id, event_id, ae_header_id, ae_line_num from the XLA Tables and source_distribution_id_num_1 will be the cust_trx_line_gl_dist_id in the case of a transaction. ACTION: COMPLETE TRANSACTION : RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = RA_CUST_TRX_LINE_GL_DIST_ALL.CUS...
Read more