R12总账和子模块关联SQL

最新推荐文章于 2024-02-23 16:53:16 发布
刚木
最新推荐文章于 2024-02-23 16:53:16 发布
阅读量918
点赞数 1
分类专栏： SQL
本文链接：https://blog.csdn.net/q290245786/article/details/106697404
版权
SQL 专栏收录该内容
34 篇文章
订阅专栏
本文深入解析了针对不同业务场景的复杂SQL查询语句，涵盖了库存、采购订单接收、在制品(WIP)、应付账款发票及付款、应收账款发票及收款等模块的数据整合与会计处理流程。通过详细的SQL语句展示，读者可以了解如何从多个源系统中抽取、转换并加载数据到总账系统，实现财务会计的自动化与精确化。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
--库存
SELECT GJH.JE_CATEGORY, GJH.JE_SOURCE, XTE.ENTITY_CODE, XDL.SOURCE_DISTRIBUTION_TYPE,A.*, MTA.*

  FROM GL_JE_HEADERS                GJH,

       GL_JE_LINES                  GJL,

       GL_IMPORT_REFERENCES         T,

       XLA_AE_LINES                 A,

       XLA_AE_HEADERS               H,

       XLA.XLA_TRANSACTION_ENTITIES XTE,

       MTL_MATERIAL_TRANSACTIONS    MMT,

       MTL_TRANSACTION_ACCOUNTS     MTA,

       XLA_DISTRIBUTION_LINKS       XDL

 WHERE /*T.JE_HEADER_ID = 176

   AND T.JE_LINE_NUM = 1

   AND*/ GJH.JE_CATEGORY = 'Inventory'

   AND GJH.JE_SOURCE = 'Cost Management'

   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

   AND T.JE_HEADER_ID = GJL.JE_HEADER_ID

   AND T.JE_LINE_NUM = GJL.JE_LINE_NUM

   AND XTE.ENTITY_ID = H.ENTITY_ID

   AND XTE.APPLICATION_ID = H.APPLICATION_ID

   AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'

   AND A.GL_SL_LINK_ID = T.GL_SL_LINK_ID

   AND A.GL_SL_LINK_TABLE = T.GL_SL_LINK_TABLE

   AND A.AE_HEADER_ID = H.AE_HEADER_ID

   AND MMT.TRANSACTION_ID = NVL(XTE.SOURCE_ID_INT_1,(-99))

   AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID

   AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = MTA.INV_SUB_LEDGER_ID

   AND XDL.SOURCE_DISTRIBUTION_TYPE = 'MTL_TRANSACTION_ACCOUNTS'

   AND XDL.APPLICATION_ID = A.APPLICATION_ID

   AND XDL.AE_HEADER_ID = A.AE_HEADER_ID

   AND XDL.AE_LINE_NUM = A.AE_LINE_NUM;


--PO接收
SELECT GJH.NAME,GJH.LEDGER_ID,GJH.JE_CATEGORY, GJH.JE_SOURCE, XTE.ENTITY_CODE,RT.PO_HEADER_ID,RT.PO_LINE_ID,RRSL.*, RT.*
  FROM GL_JE_HEADERS                GJH,
       GL_JE_LINES                  GJL,
       GL_IMPORT_REFERENCES         T,
       XLA_AE_LINES                 A,
       XLA_AE_HEADERS               H,
       XLA.XLA_TRANSACTION_ENTITIES XTE,
       /*MTL_MATERIAL_TRANSACTIONS    MMT,
       MTL_TRANSACTION_ACCOUNTS     MTA,*/
       RCV_TRANSACTIONS RT,
       XLA_DISTRIBUTION_LINKS       XDL,
       RCV_RECEIVING_SUB_LEDGER     RRSL,
       PO_HEADERS_ALL               PHA,
       PO_LINES_ALL                 PLA,
       PO_LINE_LOCATIONS_ALL        PLLA,
       PO_DISTRIBUTIONS_ALL         PDA
 WHERE /*T.JE_HEADER_ID = 7659
   AND T.JE_LINE_NUM = 1
   AND */GJH.JE_CATEGORY = 'Receiving'
 /*  AND GJH.NAME= 'MAR-2011 Receiving CNY'
   AND GJL.JE_LINE_NUM=1*/
   AND GJH.JE_SOURCE = 'Cost Management'
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_LINE_NUM = GJL.JE_LINE_NUM
   AND XTE.ENTITY_ID = H.ENTITY_ID
   AND XTE.APPLICATION_ID = H.APPLICATION_ID
   --AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
   AND A.GL_SL_LINK_ID = T.GL_SL_LINK_ID
   AND A.GL_SL_LINK_TABLE = T.GL_SL_LINK_TABLE
   AND A.AE_HEADER_ID = H.AE_HEADER_ID
/*   AND MMT.TRANSACTION_ID = XTE.SOURCE_ID_INT_1
   AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
   AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = MTA.INV_SUB_LEDGER_ID
   AND XDL.SOURCE_DISTRIBUTION_TYPE = 'MTL_TRANSACTION_ACCOUNTS'*/
   AND XDL.APPLICATION_ID = A.APPLICATION_ID
   AND XDL.AE_HEADER_ID = A.AE_HEADER_ID
   AND RT.TRANSACTION_ID = XTE.SOURCE_ID_INT_1
   AND XDL.AE_LINE_NUM = A.AE_LINE_NUM
   AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
   AND PHA.PO_HEADER_ID = RT.PO_HEADER_ID
   AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
   AND PLLA.PO_HEADER_ID = PLA.PO_HEADER_ID
   AND PLLA.PO_LINE_ID = PLA.PO_LINE_ID
   AND PDA.LINE_LOCATION_ID = PLLA.LINE_LOCATION_ID
   AND PDA.PO_DISTRIBUTION_ID = TO_NUMBER(RRSL.REFERENCE3)
   AND A.DISPLAYED_LINE_NUMBER > 0;


--WIP



SELECT GJH.NAME,GJH.LEDGER_ID,GJH.JE_CATEGORY, GJH.JE_SOURCE,XDL.SOURCE_DISTRIBUTION_TYPE, XTE.ENTITY_CODE
  FROM GL_JE_HEADERS                GJH,
       GL_JE_LINES                  GJL,
       GL_IMPORT_REFERENCES         T,
       XLA_AE_LINES                 A,
       XLA_AE_HEADERS               H,
       XLA.XLA_TRANSACTION_ENTITIES XTE,
       XLA_DISTRIBUTION_LINKS       XDL,
       WIP_TRANSACTIONS             WT,
       WIP_TRANSACTION_ACCOUNTS     WTA
 WHERE T.JE_HEADER_ID = 7660
   AND T.JE_LINE_NUM = 1
   AND GJH.JE_CATEGORY = 'WIP'
 /*  AND GJH.NAME= 'MAR-2011 Receiving CNY'
   AND GJL.JE_LINE_NUM=1*/
   AND GJH.JE_SOURCE = 'Cost Management'
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_LINE_NUM = GJL.JE_LINE_NUM
   AND XTE.ENTITY_ID = H.ENTITY_ID
   AND XTE.APPLICATION_ID = H.APPLICATION_ID
   --AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
   AND A.GL_SL_LINK_ID = T.GL_SL_LINK_ID
   AND A.GL_SL_LINK_TABLE = T.GL_SL_LINK_TABLE
   AND A.AE_HEADER_ID = H.AE_HEADER_ID
   AND XDL.APPLICATION_ID = A.APPLICATION_ID
   AND XDL.AE_HEADER_ID = A.AE_HEADER_ID
   AND XDL.AE_LINE_NUM = A.AE_LINE_NUM
   AND A.DISPLAYED_LINE_NUMBER > 0
   
   AND WT.TRANSACTION_ID = XTE.SOURCE_ID_INT_1
   AND WTA.TRANSACTION_ID = WT.TRANSACTION_ID
   AND WTA.ORGANIZATION_ID = WT.ORGANIZATION_ID
   AND WTA.WIP_SUB_LEDGER_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1;


--AP发票

SELECT GJH.NAME,GJH.LEDGER_ID,GJH.JE_CATEGORY, GJH.JE_SOURCE,XDL.SOURCE_DISTRIBUTION_TYPE, XTE.ENTITY_CODE,
AIDA.*

  FROM GL_JE_HEADERS                GJH,
       GL_JE_LINES                  GJL,
       GL_IMPORT_REFERENCES         T,
       XLA_AE_LINES                 A,
       XLA_AE_HEADERS               H,
       XLA.XLA_TRANSACTION_ENTITIES XTE,
       XLA_DISTRIBUTION_LINKS       XDL,
       AP_INVOICES_ALL              AI,
       AP_INVOICE_DISTRIBUTIONS_ALL AIDA
 WHERE T.JE_HEADER_ID = 10710
   AND T.JE_LINE_NUM = 5
   AND GJH.JE_CATEGORY =  'Purchase Invoices'
 /*  AND GJH.NAME= 'MAR-2011 Receiving CNY'
   AND GJL.JE_LINE_NUM=1*/
   AND GJH.JE_SOURCE =  'Payables'
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_LINE_NUM = GJL.JE_LINE_NUM
   AND XTE.ENTITY_ID = H.ENTITY_ID
   AND XTE.APPLICATION_ID = H.APPLICATION_ID
   --AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
   AND A.GL_SL_LINK_ID = T.GL_SL_LINK_ID
   AND A.GL_SL_LINK_TABLE = T.GL_SL_LINK_TABLE
   AND A.AE_HEADER_ID = H.AE_HEADER_ID
   AND XDL.APPLICATION_ID = A.APPLICATION_ID
   AND XDL.AE_HEADER_ID = A.AE_HEADER_ID
   AND XDL.AE_LINE_NUM = A.AE_LINE_NUM
   AND A.DISPLAYED_LINE_NUMBER > 0
   
   AND XTE.SOURCE_ID_INT_1 = AI.INVOICE_ID
   AND XDL.APPLIED_TO_SOURCE_ID_NUM_1 = AI.INVOICE_ID
   AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
   ;

--AP付款

SELECT GJH.NAME,GJH.LEDGER_ID,GJH.JE_CATEGORY, GJH.JE_SOURCE,XDL.SOURCE_DISTRIBUTION_TYPE, XTE.ENTITY_CODE,
  XTE.SECURITY_ID_INT_1,AC.ORG_ID,XDL.*
  FROM GL_JE_HEADERS                GJH,
       GL_JE_LINES                  GJL,
       GL_IMPORT_REFERENCES         T,
       XLA_AE_LINES                 A,
       XLA_AE_HEADERS               H,
       XLA.XLA_TRANSACTION_ENTITIES XTE,
       XLA_DISTRIBUTION_LINKS       XDL,
       AP_CHECKS_ALL                AC,
       AP_PAYMENT_HIST_DISTS        APHD,
       AP_PAYMENT_HISTORY_ALL       APHA
 WHERE T.JE_HEADER_ID = 10658
   AND T.JE_LINE_NUM = 2
   AND GJH.JE_CATEGORY =   'Payments'
 /*  AND GJH.NAME= 'MAR-2011 Receiving CNY'
   AND GJL.JE_LINE_NUM=1*/
   AND GJH.JE_SOURCE =  'Payables'
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_LINE_NUM = GJL.JE_LINE_NUM
   AND XTE.ENTITY_ID = H.ENTITY_ID
   AND XTE.APPLICATION_ID = H.APPLICATION_ID
   --AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
   AND A.GL_SL_LINK_ID = T.GL_SL_LINK_ID
   AND A.GL_SL_LINK_TABLE = T.GL_SL_LINK_TABLE
   AND A.AE_HEADER_ID = H.AE_HEADER_ID
   AND XDL.APPLICATION_ID = A.APPLICATION_ID
   AND XDL.AE_HEADER_ID = A.AE_HEADER_ID
   AND XDL.AE_LINE_NUM = A.AE_LINE_NUM
   AND A.DISPLAYED_LINE_NUMBER > 0
   
   AND XTE.SOURCE_ID_INT_1 = AC.CHECK_ID
   AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = APHD.PAYMENT_HIST_DIST_ID
   AND APHA.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
   AND APHA.CHECK_ID = AC.CHECK_ID
   
   ;

--AR发票
SELECT GJH.NAME,GJH.LEDGER_ID,GJH.JE_CATEGORY, GJH.JE_SOURCE,XDL.SOURCE_DISTRIBUTION_TYPE, XTE.ENTITY_CODE,
  XTE.SECURITY_ID_INT_1,XDL.*
  FROM GL_JE_HEADERS                GJH,
       GL_JE_LINES                  GJL,
       GL_IMPORT_REFERENCES         T,
       XLA_AE_LINES                 A,
       XLA_AE_HEADERS               H,
       XLA.XLA_TRANSACTION_ENTITIES XTE,
       XLA_DISTRIBUTION_LINKS       XDL, 
       RA_CUSTOMER_TRX_ALL          RCTA,
       RA_CUST_TRX_LINE_GL_DIST_ALL DIS
 WHERE T.JE_HEADER_ID = 96785
   AND T.JE_LINE_NUM = 3
   AND GJH.JE_CATEGORY =    'Sales Invoices'
 /*  AND GJH.NAME= 'MAR-2011 Receiving CNY'
   AND GJL.JE_LINE_NUM=1*/
   AND GJH.JE_SOURCE =   'Receivables'
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_LINE_NUM = GJL.JE_LINE_NUM
   AND XTE.ENTITY_ID = H.ENTITY_ID
   AND XTE.APPLICATION_ID = H.APPLICATION_ID
   --AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
   AND A.GL_SL_LINK_ID = T.GL_SL_LINK_ID
   AND A.GL_SL_LINK_TABLE = T.GL_SL_LINK_TABLE
   AND A.AE_HEADER_ID = H.AE_HEADER_ID
   AND XDL.APPLICATION_ID = A.APPLICATION_ID
   AND XDL.AE_HEADER_ID = A.AE_HEADER_ID
   AND XDL.AE_LINE_NUM = A.AE_LINE_NUM
   AND A.DISPLAYED_LINE_NUMBER > 0 
   AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
   AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = DIS.CUST_TRX_LINE_GL_DIST_ID
   AND DIS.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
   ;

--AR收款

SELECT GJH.NAME,
       GJH.LEDGER_ID,
       GJH.JE_CATEGORY,
       GJH.JE_SOURCE,
       XTE.ENTITY_CODE,
       ACRA.RECEIPT_NUMBER,
       XTE.SECURITY_ID_INT_1,
       A.ACCOUNTING_CLASS_CODE,
       A.*
  FROM GL_JE_HEADERS                GJH,
       GL_JE_LINES                  GJL,
       GL_IMPORT_REFERENCES         T,
       XLA_AE_LINES                 A,
       XLA_AE_HEADERS               H,
       XLA.XLA_TRANSACTION_ENTITIES XTE,
       -- RA_CUSTOMER_TRX_ALL          RCTA
       AR_CASH_RECEIPTS_ALL ACRA
 WHERE GJH.LEDGER_ID = 1063
      -- AND GJH.PERIOD_NAME = '10-2018' 
      -- AND ACRA.RECEIPT_NUMBER = '105242936-585' 
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND T.JE_LINE_NUM = GJL.JE_LINE_NUM
   AND XTE.ENTITY_ID = H.ENTITY_ID
   AND XTE.APPLICATION_ID = H.APPLICATION_ID
   AND A.GL_SL_LINK_ID = T.GL_SL_LINK_ID
   AND A.GL_SL_LINK_TABLE = T.GL_SL_LINK_TABLE
   AND A.AE_HEADER_ID = H.AE_HEADER_ID
   AND A.DISPLAYED_LINE_NUMBER > 0
   AND ACRA.CASH_RECEIPT_ID = XTE.SOURCE_ID_INT_1
   AND XTE.ENTITY_CODE = 'RECEIPTS'
   AND XTE.LEDGER_ID = 1063 
;