EBS: 应收发票过账总账后的追索查询（从AR到GL追索）

-- AR 发票行分配 过账GL后的查询， GL到AR 关联。
-- CREATE TABLE CUX.CUX_CUST_TRX_LINE_GL_DIST_TMP2 AS
select RB.NAME AS BATCH_NAME
       ,rct.trx_number       
       ,BS.NAME AS "来源"
       ,HCA.ACCOUNT_NUMBER AS CUSTOMER_NUMBER
       ,HCA.ACCOUNT_NAME
       ,RCTL.LINE_NUMBER
       ,(SELECT MSI.SEGMENT1 FROM INV.MTL_SYSTEM_ITEMS_B MSI
          WHERE MSI.ORGANIZATION_ID = 301
           AND MSI.INVENTORY_ITEM_ID = RCTL.INVENTORY_ITEM_ID AND ROWNUM=1
        ) AS ITEM_CODE
       ,RCTL.DESCRIPTION AS ITEM_DESCRIPTION
       ,RCTL.UOM_CODE
       ,RCTL.QUANTITY_INVOICED
       ,RCTL.UNIT_STANDARD_PRICE
       ,RCTL.UNIT_SELLING_PRICE
       ,RCTL.SALES_ORDER
       ,RCTL.SALES_ORDER_LINE       
       ,RCTLD.CUSTOMER_TRX_LINE_ID
       ,RCTLD.CUST_TRX_LINE_GL_DIST_ID
       ,RCTLD.GL_POSTED_DATE -- 当此值非空时，表示已经过账GL
       ,RCTLD.posting_control_id -- '-3' (未过账的默认值)
       ,RCTLD.REQUEST_ID
       ,RCTLD.ACCOUNT_CLASS
       ,RCTLD.ACCTD_AMOUNT
       ,RCTLD.EVENT_ID
       ,rctLD.CODE_COMBINATION_ID
       ,RCTLD.latest_rec_flag
       ,fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>50368, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>rctLD.CODE_COMBINATION_ID --        IN  NUMBER   -- code_Combination_id 
                    ) AS "GL帐户"
    ,xla_oa_functions_pkg.get_ccid_description(p_coa_id =>50368,  -- CHART_OF_ACCOUNT_ID
   p_ccid => rctLD.CODE_COMBINATION_ID   --  CODE_COMBINZATION_ID 
   )   AS "GL帐户描述" 
   ,GIR.JE_BATCH_ID
   ,GIR.JE_HEADER_ID
   ,GIR.JE_LINE_NUM
   ,GJL. ENTERED_DR
   ,GJL.ENTERED_CR
   ,GJL.DESCRIPTION AS GL_LINE_DESCRIPTION
   ,GJL.CREATION_DATE AS GL_CREATION_DATE
   ,GJH.JE_CATEGORY
   ,GJH.JE_SOURCE
   ,GJH.PERIOD_NAME
   ,GJH.NAME AS GL_NAME
   ,GJH.DESCRIPTION AS GL_DESCRIPTION
   ,GJH.CURRENCY_CODE
   ,GJH.STATUS
   ,GJH.DOC_SEQUENCE_VALUE
   ,GJB.NAME AS BATCH_NAME
   ,GJB.DESCRIPTION AS BATCH_DESCRIPTION
  -- ,RCTL.*
 FROM RA_BATCHES_ALL RB,
      RA_CUSTOMER_TRX_all  rct,
      RA_CUSTOMER_TRX_LINES_all rctl,
      RA_CUST_TRX_LINE_GL_DIST_ALL RCTLD,
      Hz_Cust_Accounts  HCA, --  客户账户表
      RA_BATCH_SOURCES_ALL BS,  -- 来源 
      GL_CODE_COMBINATIONS GCC,
      XLA.XLA_EVENTS XE,
      XLA.XLA_TRANSACTION_ENTITIES XTE ,
      XLA.XLA_AE_HEADERS XAH,
      XLA.XLA_AE_LINES XAL,
      XLA.XLA_DISTRIBUTION_LINKS XDL,
      GL.GL_IMPORT_REFERENCES GIR,
      GL.GL_JE_LINES GJL,
      GL.GL_JE_HEADERS GJH,
      GL.GL_JE_BATCHES GJB
where RB.BATCH_ID = RCT.BATCH_ID
 AND rct.customer_trx_id =rctLD.customer_trx_id
 and rct.trx_number IN ('1425818','1423636','1424285')
 AND RCTLD.EVENT_ID = XE.EVENT_ID
 AND XE.APPLICATION_ID = 222 -- 222: AR 
 AND XE.ENTITY_ID = XTE.ENTITY_ID
 AND XE.APPLICATION_ID = XTE.APPLICATION_ID 
 AND XE.EVENT_ID = XAH.EVENT_ID
 AND XTE.ENTITY_ID = XAH.ENTITY_ID
 AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
 AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
 AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
 AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RCTLD.CUST_TRX_LINE_GL_DIST_ID
 AND XDL.EVENT_ID = RCTLD.EVENT_ID 
 AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID -- IN (17091942,17091943,17091944)
 AND GIR.GL_SL_LINK_TABLE =XAL.GL_SL_LINK_TABLE -- 'XLAJEL'
 AND GIR.REFERENCE_5 = TO_CHAR(XAH.ENTITY_ID) -- 558386118 -- ENTITY_ID
 AND GIR.REFERENCE_6 = TO_CHAR(XAH.EVENT_ID) -- 559118134 -- EVENT_ID 
 AND GIR.REFERENCE_7 = TO_CHAR(XAL.AE_HEADER_ID) --9204337 -- AE_HEADER_ID 
 AND GIR.REFERENCE_8 = TO_CHAR(XAL.AE_LINE_NUM) -- AE_LINE_NUM   
 AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
 AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
 AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
 AND GJL.LEDGER_ID = GJH.LEDGER_ID 
 AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID 
 AND GJB.CHART_OF_ACCOUNTS_ID = 50368 -- 公司的帐簿ID 
-- and rct.creation_date>sysdate-1/2
-- and rctl.org_id = 281
  --AND RCTLD.latest_rec_flag ='Y'
-- and rctl.line_type in ('LINE') -- 'LINE','TAX'
-- AND RCT.COMPLETE_FLAG = 'N' -- 状态，  Y:完成， N:未完成
-- AND RCT.posting_control_id = -3  -- '-3' (未过账的默认值)
-- and rctl.CUSTOMER_TRX_LINE_ID = 372612624
-- and rctl.interface_line_attribute1 = 1001196187 -- ooh.order_number 
-- and rctl.interface_line_attribute6 = 290473141  -- ool.line_id 
-- and rctl.interface_line_attribute10 = 301 -- ool.ship_from_org_id 
-- and rctl.interface_line_attribute12 = 5  -- ool.line_number 
-- and rctl.tax_exempt_flag ='S' -- ool.tax_exempt_flag 
--AND RCTL.SALES_ORDER IN ('1001197631')
--AND RCTLD.latest_rec_flag ='Y'
-- AND RCTLD.ACCOUNT_CLASS = 'REC'  -- REC:应收帐款 
AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID 
--AND HCA.ACCOUNT_NUMBER = 'S20000'
AND RCT.CUSTOMER_TRX_ID = RCTLD.CUSTOMER_TRX_ID
AND RCTL.CUSTOMER_TRX_LINE_ID(+) = RCTLD.CUSTOMER_TRX_LINE_ID
AND RCTL.CUSTOMER_TRX_ID(+) = RCTLD.CUSTOMER_TRX_ID 
AND RCT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID 
 AND BS.NAME LIKE 'OM%' -- "来源"
 AND RB.NAME LIKE 'OM 导入_60838597'
AND RCTLD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID 
AND GCC.CHART_OF_ACCOUNTS_ID = 50368 
--AND GCC.SEGMENT3 = '600105'
--AND RCTLD.REQUEST_ID = 60827077 
 and rct.creation_date >= sysdate - 6/2 
ORDER BY  rct.trx_number --,RCTL.SALES_ORDER,RCTL.SALES_ORDER_LINE
 -- ,RCTL.LINE_TYPE
 

　　EBS: 应收发票过账总账后的追索查询（从AR到GL追索）