EBS:从WIP到GL

Saturday, June 5, 2010
Subledger Accounting (SLA) in R12

1) All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL

2) Run “Create Accounting” to populate accounting events (SLA) tables. User can “View Accounting” only after “Create Accounting” is run. Create Accounting process
– Applies accounting rules
– Loads SLA tables, GL tables
– Creates detailed data per accounting rules, stores in SLA “distribution links” table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah
XLA_AE_LINES xal
XLA_EVENTS xae
XLA_TRANSACTION_ENTITIES xte
XLA_DISTRIBUTION_LINKS xdl
GL_IMPORT_REFERENCES gir

Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id
xah.entity_id = xte.entity_id
xae.entity_id = xte.entity_id
xah.event_id = xlae.event_id
xah.ae_header_id = xdl.ae_header_id
xah.ae_line_num = xdl.ae_line_num
xah.event_id = xdl.event_id
xal.gl_sl_link_id = gir.gl_sl_link_id
xal.gl_sl_link_table = gir.gl_sl_link_table
xah.application_id = xal.application_id
xal.application_id = xte.application_id
xte.application_id = xdl.application_id
xae.application_id = xte.application_id
xah.application_id = xae.application_id
xah.application_id = (Different value based on Module)

xte.entity_code =
'TRANSACTIONS' or
'RECEIPTS' or
'ADJUSTMENTS' or
'PURCHASE_ORDER' or
'AP_INVOICES' or
'AP_PAYMENTS' or
'MTL_ACCOUNTING_EVENTS' or
'WIP_ACCOUNTING_EVENTS'

xte.source_id_int_1 =
'INVOICE_ID' or
'CHECK_ID' or
'CUSTOMER_TRX_ID' or
'TRANSACTION_ID'

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types
xdl.source_distribution_type = 'AP_PMT_DIST '
and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id
---------------
xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id
---------------
xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id
and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id
---------------
xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id
---------------
xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id
---------------
-- 从WIP到GL: 必须已经过帐到总帐
xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id
---------------
xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id

XLA_DISTRIBUTION_LINKS stores the link between transactions and subledger journal entry lines. There is a one-to-many relationship between the subledger lines and distribution links. The transaction is stored in the columns UNROUNDED_ENTERED_DR, UNROUNDED_ENTERED_CR, UNROUNDED_ACCOUNTED_CR & UNROUNDED_ACCOUNTED_DR.

If xla_distribution_links table is confusing, use xla_events table with below joins

gl_je_lines (je_header_id, je_line_num) -> gl_import_references (je_header_id, je_line_num)

gl_import_references (gl_sl_link_table, gl_sl_link_id) -> xla_ae_lines (gl_sl_link_table, gl_sl_link_id)

xla_ae_lines (applicaiton_id, ae_header_id) -> xla_ae_headers (application_id, ae_header_id)

xla_ae_headers (application_id, event_id) -> xla_events (application_id, event_id)

xla_events (application_id, entity_id) -> xla.xla_transaction_entities (application_id, entity_id)

xla_distribution_links(ae_header_id,ae_line_num) -> xla_ae_lines(ae_header_id,ae_line_num)

 

-- 从离散任务WIP到总帐GL
SELECT GJB.NAME as "GL批名称",
       GJH.JE_CATEGORY,
       GJH.NAME AS GL_NAME,
       GJH.DESCRIPTION AS "日记账说明",
       TO_CHAR(gjh.doc_sequence_value) AS "单据编号",
       --GJL.GL_SL_LINK_ID,
       GL.NAME AS "帐套CODE",
       GL.SHORT_NAME AS "帐套简称",
       GL.DESCRIPTION AS "帐套说明",
       
       WE.WIP_ENTITY_NAME AS "工单号",
       GJL.* 
  FROM GL.GL_JE_BATCHES GJB ,  -- 总帐批
       GL.GL_JE_HEADERS GJH,  -- 总帐头 
       GL.GL_JE_LINES GJL,  --总帐行 
       GL.GL_LEDGERS GL, -- 帐套表 
       GL.GL_IMPORT_REFERENCES GIR  , -- 导入GL中间表
       XLA.XLA_AE_LINES XAL,
       XLA.XLA_DISTRIBUTION_LINKS XDL,
       WIP.WIP_TRANSACTION_ACCOUNTS WTA,
       WIP.WIP_ENTITIES WE 
 WHERE 1=1
   AND GJB.DEFAULT_PERIOD_NAME LIKE '2024-04'
   AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID 
   AND GL.LEDGER_ID = GJH.LEDGER_ID
  -- AND GJH.LEDGER_ID = 2021
  -- AND GJH.JE_CATEGORY= 'Manufacturing'
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID  
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID 
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM 
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
   AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM 
   AND XDL.SOURCE_DISTRIBUTION_TYPE = 'WIP_TRANSACTION_ACCOUNTS'
   AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = WTA.WIP_SUB_LEDGER_ID
   AND WTA.ORGANIZATION_ID = WE.ORGANIZATION_ID
   AND WTA.WIP_ENTITY_ID = WE.WIP_ENTITY_ID 
  -- and gjh.doc_sequence_value ='14020231200002' 
 -- AND XAL.GL_SL_LINK_ID BETWEEN 1167631130 AND 1167631133
  AND WE.WIP_ENTITY_NAME = '15731090'
 --  AND GJL.GL_SL_LINK_ID = 1003151891
  -- AND GJB.NAME = '应收账款 A 1051426 161225323'
  -- AND GJH.NAME = '320299288 收款 CNY'
ORDER BY GJB.NAME,GJH.PERIOD_NAME ,GJH.LEDGER_ID