How To Join XLA_AE_HEADERS and RCV_TRANSACTIONS(子分类账到事务处理追溯)
来源:互联网 发布:linux dd 格式化 编辑:程序博客网 时间:2024/05/16 06:41
转自:http://www.cnblogs.com/toowang/p/3346831.html
Applies to: Oracle Inventory Management - Version: 12.0.6<max_ver> and later [Release: 12 and later ] Information in this document applies to any platform. ***Checked for relevance on 7-Sep-2011*** Goal In Release 12, Subledger Accounting has been introduced for Procurement. As a part of Subledger Accounting, new SLA tables XLA_AE_HEADERS, XLA_AE_LINES and XLA_DISTRIBUTION_LINKS have been introduced.
How to join the tables XLA_AE_HEADERS with RCV_TRANSACTIONS? Solution There is no direct join between these 2 tables, however you can link via other tables and return data.
In the rcv_receiving_sub_ledger with the rcv_transaction_id you can tie in the rcv_transactions table with the transaction_id, so:
rrsl.rcv_transaction_id = rt.transaction_id
In the rcv_receiving_sub_ledger with the RCV_SUB_LEDGER_ID you can tie in the XLA_DISTRIBUTION_LINKS table with the SOURCE_DISTRIBUTION_ID_NUM_1:
xdl.SOURCE_DISTRIBUTION_ID_NUM_1 = rrsl.RCV_SUB_LEDGER_ID
And then complete the join between the tables with the ae_header_id reference between xla_ae_headers and XLA_DISTRIBUTION_LINKS:
aeh.ae_header_id = xdl.ae_header_id
So with the following completed script by entering an accrued receipt number you can obtain the rcv_transaction transaction_id and the xla_ae_headers table ae_header_id values, thus effectively joining the respective tables. Please note however that the transactions must have been accrued and the Create Accounting process have completed for records to be seen in the Subledger Tables. Here is the script, it requires the entry of a receipt number and the appropriate set of books id ( for a receipt that has been accrued as described above):
SELECT aeh.ae_header_id,
ael.ae_line_num,
ael.accounting_class_code,
ael.accounted_dr,
ael.accounted_cr,
rt.transaction_id,
rt.shipment_header_id
FROM xla_ae_headers aeh,
xla_ae_lines ael,
rcv_transactions rt,
xla_distribution_links xdl,
rcv_receiving_sub_ledger rrsl
WHERE aeh.ae_header_id = xdl.ae_header_id
AND aeh.ae_header_id = ael.ae_header_id
AND ael.ae_header_id = xdl.ae_header_id
AND ael.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
AND xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id
AND rrsl.rcv_transaction_id = rt.transaction_id
AND rrsl.set_of_books_id = &set_of_books_id
AND rt.shipment_header_id IN (SELECT shipment_header_id
FROM rcv_shipment_headers
WHERE receipt_num = '&RECEIPT_NUM')
ORDER BY rt.transaction_id,
ael.ae_header_id,
ael.ae_line_num;
- How To Join XLA_AE_HEADERS and RCV_TRANSACTIONS(子分类账到事务处理追溯)
- how to select data in multiple datatables after using join,group by and order by in Linq
- When and How to Micromanage
- How to choose the right startup to join
- Join and set operations come to dplyr
- How do I invite others to join Gmail?
- How to simulate FULL OUTER JOIN in MySQL
- How to join on two columns using hibernate JPA annotations
- Oracle EBS R12 总帐和子分类账关系详解
- Duwamish' Framewokes and data how to run !!!~~~
- How to install and configure bugzilla
- How to install and use doxygen
- HOW TO INTEGRATE TOMACAT AND APACHE
- Xmanager and VNS Config how-to
- How to Create and Use the DLL
- How to mix C and C++
- How To Use Function 'F4_FILENAME' and 'KD_GET_FILENAME_ON_F4'
- HOW TO MIX C AND C++
- MIPS 五级流水线
- Uva - 10129 - Play on Words
- oracle排序时把null放在最后
- loadrunner11 webtours 自带示例为什么只显示头部
- 日期格式转换
- How To Join XLA_AE_HEADERS and RCV_TRANSACTIONS(子分类账到事务处理追溯)
- Android Loader机制 源码笔记(2)
- ubuntu下修改文件夹权限
- TCP/IP协议栈
- hdu 2203 亲和串
- 萨德韦是否违反
- Linux下根目录解析
- Codeforces551A:GukiZ and Contest
- GRE写作常用词汇及短语