OracleEBS:追踪PO全过程:
PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1=;
select * from po_headers_all where po_header_id=;
po_lines_all
select *from po_lines_all where po_header_id =;
po_line_locations_all
select *from po_line_locations_all where po_header_id =;
po_distributions_all
select *from po_distributions_all where po_header_id =;
po_releases_all
Select *FROM po_releases_all Where po_header_id =;
2.一旦PO收货,则收货数据将转移至接收及库存的表中
RCV_SHIPMENT_HEADERS
SELECT*
FROMrcv_accounting_events
WHERErcv_transaction_id IN (SELECT transaction_id
FROMrcv_transactions
WHEREpo_header_id = );
RCV_SHIPMENT_LINES
select *from rcv_shipment_lines where po_header_id =;
RCV_TRANSACTIONS
select *from rcv_transactions where po_header_id =;
RCV_ACCOUNTING_EVENTS
Select *FROM rcv_Accounting_Events Where rcv_transaction_id IN
(selecttransaction_id from rcv_transactions
wherepo_header_id =);
RCV_RECEIVING_SUB_LEDGER
select *from rcv_receiving_sub_ledger where rcv_transaction_id in (selecttransaction_id from rcv_transactions where po_header_id=);
RCV_SUB_LEDGER_DETAILS
select *from rcv_sub_ledger_details
wherercv_transaction_id in (select transaction_id from rcv_transactionswhere po_header_id =);
MTL_MATERIAL_TRANSACTIONS
select *from mtl_material_transactions where transaction_source_id=;
MTL_TRANSACTION_ACCOUNTS
select *from mtl_transaction_accounts where transaction_id in ( selecttransaction_id from mtl_material_transactions wheretransaction_source_id = =);
Stage 3:Invoicing details
3.会计分录明细
AP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_idin ( select po_distribution_id from po_distributions_all wherepo_header_id =);
AP_INVOICES_ALL
select *from ap_invoices_all where invoice_id in
(selectinvoice_id from ap_invoice_distributions_all wherepo_distribution_id in
( selectpo_distribution_id from po_distributions_all where po_header_id=));
Stage 4: Many Time there is tie up with Project related PO
PA_EXPENDITURE_ITEMS_ALL
select *from pa_expenditure_items_all peia wherepeia.orig_transaction_reference in
( selectto_char(transaction_id) from mtl_material_transactions
wheretransaction_source_id =
Stage 5 :General Ledger
5.总账分类账目
Prompt 17.GL_BC_PACKETS ..This is for encumbrances
Select *FROM gl_bc_packets Where reference2 IN ('');
GL_INTERFACE
Select*
FROMGL_INTERFACE GLI
Whereuser_je_source_name ='Purchasing'
ANDgl_sl_link_table ='RSL'
ANDreference21='PO'
ANDEXISTS
( Select1
FROMrcv_receiving_sub_ledger RRSL
WhereGLI.reference22 =RRSL.reference2
ANDGLI.reference23 =RRSL.reference3
ANDGLI.reference24 =RRSL.reference4
ANDRRSL.rcv_transaction_id in
(selecttransaction_id from rcv_transactions
wherepo_header_id
GL_IMPORT_REFERENCES
Select*
FROMgl_import_references GLIR
Wherereference_1='PO'
ANDgl_sl_link_table ='RSL'
ANDEXISTS
( Select1
FROMrcv_receiving_sub_ledger RRSL
WhereGLIR.reference_2 =RRSL.reference2
ANDGLIR.reference_3 =RRSL.reference3
ANDGLIR.reference_4 =RRSL.reference4
ANDRRSL.rcv_transaction_id in
(selecttransaction_id from rcv_transactions
wherepo_header_id =))
1、创建一采购订单
SELECT
SELECT*
SELECT
SELECT
|
<上面的Sample中,第一行发运及分配行,都是系统自动创建的。数量等同于订单行的数量。第二行都是我追加的>
Add ByBoyle.lee 2006/07/27
---------------------
在很多取数时,我们经常从Po_distirbutions_all
但是,有时候取出来的数据从EBS上看来时,是采购行->发运行->分配行,在我们的测试数据中,是1:1:1的关系。那么
我们是否可以这样做:
select *from po_distribution_id pd where pd.po_line_id =Po.po_lines_all.po_line_id ?
结果是否如我们所想的是一条呢(无论在什么时候)。
答案是:
让我们来观察一下我们最新的一个测试数据
SELECT |
SOURCE_DISTRIBUTION_ID | 273 | 7285 |
ORG_ID | INVENTORY | 273 |
DESTINATION_TYPE_CODE | 274 | INVENTORY |
DESTINATION_ORGANIZATION_ID | 54630 | 274 |
ACCRUAL_ACCOUNT_ID | 7285 | 54630 |
PO_DISTRIBUTION_ID | 2223 | 7288 |
PO_HEADER_ID | 24503 | 2223 |
PO_LINE_ID | 54645 | 24503 |
LINE_LOCATION_ID | 1001 | 54648 |
SET_OF_BOOKS_ID | 19487 | 1001 |
CODE_COMBINATION_ID | 30 | 19487 |
QUANTITY_ORDERED |
| 30 |
PO_RELEASE_ID | 30 | 643 |
QUANTITY_DELIVERED | 0 | 30 |
QUANTITY_BILLED | 0 | 0 |
QUANTITY_CANCELLED | 2006-7-19 | 0 |
RATE_DATE | 8.021 | 2006-7-19 |
RATE | PLANNED | 8.021 |
DISTRIBUTION_TYPE |
| SCHEDULED |
这时候,问题来了。
一个采购行对应有多个分配行?
原因是什么?
答案应该只有一个,我们先来看看Oracle怎么说的:
IfSOURCE_DISTRIBUTION_ID has a value, the distributionis part of aplanned purchase order release. |
计划订单?
对,答案就是它:这是一个计划订单。
每做一次Realese,PO_distributions_all就会新增一条记录。这是计划订单的特性。
PO_RELEASES_ALL contains information about blanket andplanned purchaseorder releases. You need one row for each release youissue for ablanket or planned purchase order. Each row includes thebuyer, date,release status, and release number. Each release must have atleast onepurchase order shipment (PO_LINE_LOCATIONS_ALL). RELEASE_NUM is the number you assign to therelease. |
对,Release表也会相应的增加记录,针对计划采购订单。
由于此篇文章工作量大,所以,我们专门开一个章节来讲述计划订单。
-----------------
接收:
采购订单创建完成后,审核OK好了就可以做接收了。
*****在审批时,发现他们系统有客户化要求,即同一订单行不能出现两个发运行,所以没办法,把我追加的发运行删除了(此发运行对应分配行自动删除)。
接收可以分步完成。
如上面的数据。我们先接收第一行的3台中的1台。数据如下:
SELECT *FROM rcv_transactions rt WHERE rt.interface_source_code = 'RCV' ANDpo_header_id = 14553 AND po_line_id = 16679 | |
| |
| |
TRANSACTION_ID | 747003 |
TRANSACTION_TYPE | RECEIVE |
QUANTITY | 1 |
UNIT_OF_MEASURE | dozen |
SHIPMENT_HEADER_ID | 672003 |
SHIPMENT_LINE_ID | 687003 |
INTERFACE_SOURCE_CODE | RCV |
SOURCE_DOCUMENT_CODE | PO |
DESTINATION_TYPE_CODE | RECEIVING |
PRIMARY_QUANTITY | 1 |
PRIMARY_UNIT_OF_MEASURE | dozen |
PO_HEADER_ID | 14553 |
PO_LINE_ID | 16679 |
PO_LINE_LOCATION_ID | 16419 |
PO_DISTRIBUTION_ID |
|
再接收第一行的3台中的2台。差异数据如下:
SELECT *FROM rcv_transactions rt WHERE rt.interface_source_code = 'RCV' ANDpo_header_id = 14553 AND po_line_id = 16679
TRANSACTION_ID | 747004 | 747003 |
SHIPMENT_LINE_ID | 687004 | 687003 |
PRIMARY_QUANTITY | 2 | 1 |
------------------------rcv_receiving_sub_ledger
接收事务处理:
接收之后,其实现在还并没有入库。我们要接着做接收事务处理。把采购接收的数据入库。
SELECT *FROM rcv_transactions rt WHERE rt.interface_source_code = 'RCV' ANDpo_header_id = 14553 AND po_line_id = 16679
TRANSACTION_ID | 747004 | 747005 | 747006 | 747003 |
TRANSACTION_TYPE | RECEIVE | DELIVER | DELIVER | RECEIVE |
QUANTITY | 2 | 1 | 2 | 1 |
UNIT_OF_MEASURE | dozen | dozen | dozen | dozen |
SHIPMENT_HEADER_ID | 672003 | 672003 | 672003 | 672003 |
SHIPMENT_LINE_ID | 687004 | 687003 | 687004 | 687003 |
INTERFACE_SOURCE_CODE | RCV | RCV | RCV | RCV |
SOURCE_DOCUMENT_CODE | PO | PO | PO | PO |
DESTINATION_TYPE_CODE | RECEIVING | INVENTORY | INVENTORY | RECEIVING |
PRIMARY_QUANTITY | 2 | 1 | 2 | 1 |
PRIMARY_UNIT_OF_MEASURE | dozen | dozen | dozen | dozen |
PO_HEADER_ID | 14553 | 14553 | 14553 | 14553 |
PO_LINE_ID | 16679 | 16679 | 16679 | 16679 |
PO_LINE_LOCATION_ID | 16419 | 16419 | 16419 | 16419 |
PO_DISTRIBUTION_ID |
| 16819 | 16819 |
|
因为涉及入库操作,所以,在库存事务处理表中会留下相应的记录。即在Mtl_material_transactions表中,会存在相应的两条入库记录。
SELECT *FROM mtl_material_transactions mmt
TRANSACTION_ID | 547128 | 547132 |
TRANSACTION_TYPE_ID | 18 | 18 |
TRANSACTION_ACTION_ID | 27 | 27 |
TRANSACTION_SOURCE_TYPE_ID | 1 | 1 |
TRANSACTION_SOURCE_ID | 14553 | 14553 |
TRANSACTION_QUANTITY | 1 | 2 |
TRANSACTION_UOM | DZ | DZ |
DISTRIBUTION_ACCOUNT_ID | 4321 | 4321 |
TRANSACTION_SET_ID | 547125 | 547130 |
RCV_TRANSACTION_ID | 747005 | 747006 |
SOURCE_CODE | RCV | RCV |
SOURCE_LINE_ID | 747005 | 747006 |
以上,通过总结,主要有如下取数要求及判断逻辑
1、取已审批销售订单头和行的数据
2、取订单与其发运的关系
3、取订单发运行与分配行的关系
3、取接收数据
5、取接收入库数据
或者
--
--
×××事务处理来源类型是
BTW:
取退货的数量:
我做了个简单的测试。创建了一个PO,包含一条记录。100台。
接收了100台
第一次退货至接收
记录如下
SELECT *FROM rcv_transactions rt WHERE rt.interface_source_code IS NULL ANDpo_header_id = 14601 AND po_line_id = 16741
TRANSACTION_ID | 749013 | 749014 | 749015 |
TRANSACTION_TYPE | RETURN TORECEIVING | RETURN TORECEIVING | RETURN TOVENDOR |
SOURCE_DOCUMENT_CODE | PO | PO | PO |
DESTINATION_TYPE_CODE | RECEIVING | INVENTORY | RECEIVING |
QUANTITY | 10 | 21 | 21 |
UNIT_OF_MEASURE | dozen | dozen | dozen |
SHIPMENT_HEADER_ID | 674007 | 674007 | 674007 |
SHIPMENT_LINE_ID | 689003 | 689003 | 689003 |
PO_HEADER_ID | 14601 | 14601 | 14601 |
PO_LINE_ID | 16741 | 16741 | 16741 |
PO_LINE_LOCATION_ID | 16481 | 16481 | 16481 |
PO_DISTRIBUTION_ID | 16861 | 16861 | 16861 |
从上述数据可以看出来
退货至接收时,产生一条记录,退货至供应商时,产生两条数据。
可见退货的实际顺序为:
同时,我们去查看MMT表
SELECT *FROM mtl_material_transactionsWHERE
TRANSACTION_ID | 550160 | 550166 | 550170 |
TRANSACTION_TYPE_ID | 18 | 36 | 36 |
TRANSACTION_ACTION_ID | 27 | 1 | 1 |
TRANSACTION_QUANTITY | 100 | -10 | -21 |
TRANSACTION_UOM | DZ | DZ | DZ |
从上述数据可以看出来
不管是退货至接收还是退货至供应商,在事务处理中,都会产生两条记录。而且,数量符号与接收的数据正好相反。而且产生的记录都是RETURNTO RECEIVING。
从业务的角度来说:
好,我们接下来把“退货至接收”的那条数据再次“退货至供应商”,看看发生了什么样的情况。
TRANSACTION_ID | 749013 | 749014 | 749015 | 749016 |
TRANSACTION_TYPE | RETURN TORECEIVING | RETURN TORECEIVING | RETURN TOVENDOR | RETURN TOVENDOR |
QUANTITY | 10 | 21 | 21 | 10 |
UNIT_OF_MEASURE | dozen | dozen | dozen | dozen |
SHIPMENT_HEADER_ID | 674007 | 674007 | 674007 | 674007 |
SHIPMENT_LINE_ID | 689003 | 689003 | 689003 | 689003 |
SOURCE_DOCUMENT_CODE | PO | PO | PO | PO |
DESTINATION_TYPE_CODE | RECEIVING | INVENTORY | RECEIVING | RECEIVING |
PO_HEADER_ID | 14601 | 14601 | 14601 | 14601 |
PO_LINE_ID | 16741 | 16741 | 16741 | 16741 |
PO_LINE_LOCATION_ID | 16481 | 16481 | 16481 | 16481 |
PO_DISTRIBUTION_ID | 16861 | 16861 | 16861 |
|
从上述数据可以看出来
新产生了一条记录,从Receving退货至Return To Vendor。
而且,这条记录并没有记录PO_DISTRIBUTION_ID,即我们不能从退货至Receving再从Receving中ReturnTo Vendor中去查找他原始的位置。
所以,我们如果要取退货的数量,或者取订单的接受数量(在有退货的时候),需要从如下来取。
--------###
但是,虽然我们再次做了退货,发现MMT的数据并没有改变。所以。我们可以直接从事务处理来取数。
或者
--
--
------------------------------------
--综上所述,最好的取采购订单入库数量(包括入库与退货)
--的方法为:
Way1 | SELECT |
Way2 |
|
这两种方法。一般来说。大家无所谓选哪种方法。只要综合一下执行效率就行。
但是,这里面有一种情况,有时候,MMT里面有数据,而这些数据在RT中并不存在(RT异常)。这时候,我们就要想一下了。
如果我们要计算通过采购订单确认准确的库存量。我们也许只能取Way2了。这个时候,最好与业务人员商量一下了。
from: http://www.cnblogs.com/toowang/archive/2012/01/10/2317691.html