R12 Mapping Between Subledger Tables, SLA and GL Tables
来源:互联网 发布:量子技术 知乎 编辑:程序博客网 时间:2024/06/06 00:23
SOURCE PO TABLE
Table relationships between PO tables and rcv_transactions and rcv_receiving_sub_ledger remain the same as in R11i. The following reference fields are used to capture and keep PO information in RCV_RECEIVING_SUB_LEDGER
REFERENCE1
Source (PO or REQ)
REFERENCE2
PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or
po_requisition_headers_all.requisition_header_id)
REFERENCE3
Distribution ID (from po_distributions_all.po_distribution_id or
po_req_distributions_all.distribution_id)
REFERENCE4
Purchase Order or Requisition number (from po_headers_all.segment1 or
po_requistion_headers_all.segment1)
REFERENCE5
(Autocreated Purchase Orders only) Backing requisition number (from
po_requisition_headers_all.segment1)
RCV_TRANSACTION_ID Receiving transaction unique identifier (from rcv_transactions.transactions_id)
The receiving package that populates this table is RCV_CreateAccounting_PVT (RCVVACCB.pls)
Release 11i Mapping Ref Note.461103.1 - Mapping Between PO Tables And General Ledger Tables
XLA_TABLES
SLA-Subledger Accounting, has introduced new SLA tables :
* XLA_AE_HEADERS
* XLA_AE_LINES
* XLA_DISTRIBUTION_LINKS
RCV_RECEIVING_SUB_LEDGER is not directly linked to table GL_JE_LINES, instead it is linked through the subledger tables :
* XLA_AE_HEADERS
* XLA_AE_LINES
* XLA_DISTRIBUTION_LINKS
Accounting entries in RCV_RECEIVING_SUB_LEDGER can be retrieved using SLA tables and GL_IMPORT_REFERENCES table after running Create Accounting.
RCV_RECEIVING_SUB_LEDGER is linked to XLA Tables by RCV_SUB_LEDGER_ID
Run the sql below to get RCV_SUB_LEDGER_ID:
Select RCV_SUB_LEDGER_ID from rcv_receiving_sub_ledger
where rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =&&po_header_id);
XLA_DISTRIBUTION_LINKS stores the link between transactions and subledger journal entry lines.
The tables XLA_DISTRIBUTION_LINKS and RCV_RECEIVING_SUB_LEDGER are linked by
* SOURCE_DISTRIBUTION_ID_NUM_1 = RCV_SUB_LEDGER_ID
* SOURCE_DISTRIBUTION_TYPE = 'RCV_RECEIVING_SUB_LEDGER'
For example:
Select *
FROM XLA_DISTRIBUTION_LINKS XDL
where XDL.SOURCE_DISTRIBUTION_ID_NUM_1 IN (' <>')
And SOURCE_DISTRIBUTION_TYPE = 'RCV_RECEIVING_SUB_LEDGER'
And APPLICATION_ID = 707;
Select * from rcv_receiving_sub_ledger
where rcv_sub_ledger_id in
(select source_dIstribution_id_num_1
FROM XLA_DISTRIBUTION_LINKS
where SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
and APPLICATION_ID=707)
Link the PO Distibution, Receipts, Receiving Subledger and XLA Distributions
select rt.po_header_id, pd.po_distribution_id,
rt.transaction_id, rrsl.rcv_sub_ledger_id,
xdl.ae_header_id,xdl.SOURCE_DISTRIBUTION_ID_NUM_1,
xdl.SOURCE_DISTRIBUTION_TYPE
from rcv_receiving_sub_ledger rrsl, rcv_transactions rt,
po_distributions_all pd, xla_distribution_links xdl
where pd.po_distribution_id = rt.PO_DISTRIBUTION_ID
and rt.transaction_id = rrsl.rcv_transaction_id
and rt.PO_DISTRIBUTION_ID = rrsl.reference3
and rrsl.rcv_sub_ledger_id = xdl.SOURCE_DISTRIBUTION_ID_NUM_1
and xdl.SOURCE_DISTRIBUTION_TYPE = 'RCV_RECEIVING_SUB_LEDGER'
and xdl.APPLICATION_ID=707;
The tables PO_BC_DISTRIBUTIONS and XLA_AE_HEADERS are linked with the column 'event_id'.
The tables XLA_AE_HEADERS and GL_BC_PACKETS are linked with the column 'ae_header_id'.
XLA_DISTRIBUTION_LINKS and GL_BC_PACKETS are linked by 'source_distribution_type' and 'source_distribution_id_num_1' . On Reserving and Approving the PO, Encumbrance entries get generated in table GL_BC_PACKETS with column 'source_distribution_type' as po_distributions_all and column 'source_distribution_id_num_1' as po_distribution_id.
The tables XLA_AE_HEADERS and XLA_AE_LINES are linked with the column 'ae_header_id'.
The tables XLA_DISTRIBUTION_LINKS and MTL_TRANSACTION_ACCOUNTS are linked by
* source_distribution_id_num_1 = inv_sub_ledger_id
* source_distribution_type = MTL_TRANSACTION_ACCOUNTS
GL_TABLES
XLA_AE_LINES table is mapped to GL_IMPORT_REFERENCES table with the 'gl_sl_link_id' and 'gl_sl_link_table' columns. The linking columns between GL_IMPORT_REFERENCES and GL_JE_LINES tables are 'je_header_id' and 'je_line_num. '
DATA COLLECTION SQL SCRIPTS
DATA FROM PO TABLES
======================
1.
SELECT * FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID
2.
SELECT * FROM PO_LINES_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID
3.
SELECT * FROM PO_LINE_LOCATIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID
3a.
SELECT * FROM po_line_locations_archive_all
WHERE PO_HEADER_ID = &&po_header_id
4.
SELECT * FROM PO_DISTRIBUTIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID
5.
SELECT * FROM PO_RELEASES_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID;
RECEIVING AND DELIVERY DATA
===========================
select * from rcv_transactions where po_header_id in ()
select * from mtl_material_transactions where rcv_transaction_id in ()
DATA FROM RECEIVING,INVENTORY TABLES and AP TABLES
==================================================
SELECT * FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID IN
(SELECT SHIPMENT_HEADER_ID FROM RCV_SHIPMENT_LINES
WHERE PO_HEADER_ID =&&PO_HEADER_ID );
SELECT * FROM RCV_SHIPMENT_LINES
WHERE PO_HEADER_ID =&&PO_HEADER_ID;
SELECT * FROM RCV_TRANSACTIONS
WHERE PO_HEADER_ID =&&PO_HEADER_ID;
SELECT * FROM RCV_ACCOUNTING_EVENTS
WHERE PO_HEADER_ID =&&PO_HEADER_ID;
SELECT * FROM RCV_RECEIVING_SUB_LEDGER
WHERE RCV_TRANSACTION_ID IN
(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS
WHERE PO_HEADER_ID =&&PO_HEADER_ID);
SELECT * FROM RCV_SUB_LEDGER_DETAILS
WHERE RCV_TRANSACTION_ID IN
(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS
WHERE PO_HEADER_ID =&&PO_HEADER_ID);
SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_ID = &&PO_HEADER_ID;
SELECT * FROM MTL_TRANSACTION_ACCOUNTS
WHERE TRANSACTION_ID IN
( SELECT TRANSACTION_ID FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_ID = &&PO_HEADER_ID )
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE PO_DISTRIBUTION_ID IN
(SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID );
SELECT * FROM AP_INVOICES_ALL
WHERE INVOICE_ID IN
(SELECT INVOICE_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE PO_DISTRIBUTION_ID IN
( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL
WHERE PO_HEADER_ID =&&PO_HEADER_ID ));
RECEIVING TRANSACTIONS/RRLS and XLA TABLES
===========================================
RCV
-----
Select * FROM RCV_TRANSACTIONS
where TRANSACTION_ID IN
( Select rcv_transaction_id from rcv_receIving_sub_ledger
where rcv_sub_ledger_id in
(select source_dIstribution_id_num_1
from XLA_DISTRIBUTION_LINKS
where SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
and APPLICATION_ID=707))
RRSL
---
Select * from rcv_receIving_sub_ledger
where rcv_sub_ledger_id in
(select source_dIstribution_id_num_1
FROM XLA_DISTRIBUTION_LINKS
where SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
and APPLICATION_ID=707)
select * from xla_ae_headers where ae_header_id in ()
select * from xla_ae_lines where ae_header_id in ()
select * from xla_distribution_links where ae_header_id in ()
select * from xla_distribution_links where source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER' and source_distribution_id_num_1 in ()
XLA and MTL TABLES
---
SELECT * FROM MTL_MATERIAL_TRASNACTIONS
WHERE TRANSACTION_ID=&TXN_ID
SELECT * FROM MTL_TRANSACTION_ACCOUNTS
WHERE TRASNACTION_ID=&TXN_ID
SELECT * FROM XLA_TRASNACTION_ENTITIES
WHERE TRANSACTION_NUMBER=&TXN_ID
(SOURCE_ID_INT_1 also represnets txn_id)
SELECT * FROM XLA_EVENTS
WHERE ENTITY_ID IN (
SELECT ENTITY_ID FROM XLA_TRANSACTION_ENTITIES
WHERE TRANSACTION_NUMBER=&TXN_ID)
SELECT * FROM XLA_DISTRIBUTION_LINKS
WHERE EVENT_ID IN
(SELECT EVENT_ID FROM XLA_EVENTS
WHERE ENTITY_ID IN (
SELECT ENTITY_ID FROM XLA_TRANSACTION_ENTITIES
WHERE TRANSACTION_NUMBER=&TXN_ID))
SELECT * FROM XLA_AE_HEADERS
WHERE EVENT_ID IN
(SELECT EVENT_ID FROM XLA_EVENTS
WHERE ENTITY_ID IN (
SELECT ENTITY_ID FROM XLA_TRANSACTION_ENTITIES
WHERE TRANSACTION_NUMBER=&TXN_ID))
SELECT * FROM XLA_AE_LINES
WHERE AE_HEADER_ID IN
(SELECT AE_HEADER_ID FROM XLA_AE_HEADERS
WHERE EVENT_ID IN
select * from rcv_transactions where po_header_id in ()
select * from mtl_material_transactions where rcv_transaction_id in ()
select * from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in ()
References
NOTE:461103.1 - Mapping Between PO Tables And General Ledger Table
- R12 Mapping Between Subledger Tables, SLA and GL Tables
- R12 Mapping Between Subledger Tables, SLA and GL Tables
- R12 sla tables
- EBS R12 SLA: Analyzing Subledger Accounting
- EBS R12 SLA: Analyzing Subledger Accounting
- The Difference Between Trees and Hash Tables
- R12 SLA: From Product Accounting to Subledger Accounting
- the differences between transparent, pooled, and cluster tables
- R12 - GL / XLA / FAH - How to link GL data to the subledger data or vice versa
- R12 SLA 子分类帐和GL总帐关联查询
- Oracle Ebs R12 SLA与GL关系变化
- Pooled and Cluster Tables
- Create Tables and Constraints
- Pooled and Cluster Tables
- watir and ajax tables
- Tables, Matrices, and Lists
- Create DataRelation ship between two tables
- 【MongoDB】The connection between two tables
- C#屏蔽ComboBox系统右键菜单介绍(通过API拦截消息)
- Swing LookandFeel制作的总结
- 物流解决方案收集
- 关于Spring中dataSource的配置
- 正则表达式入门非常不错的讲解
- R12 Mapping Between Subledger Tables, SLA and GL Tables
- http://gekben.gitcd.com/blog/2012/07/11/collection-of-network-performance-tuning-tools/
- 地理(投影)坐标与屏幕坐标的转换!
- ILayerEffects接口用来设置一个要素图层的透明度,对比度,对比度
- Excel 单元格按照条件改变背景颜色
- 自定义通知&&分享
- Calendar5.js日历控件在IE8下点击右边滚动条即会报错的解决方法
- apr_status_t apr_pollset_poll apache
- 如何高效利用GitHub