采購(gòu)到入庫(kù)所經(jīng)歷的表

1、-11i 分銷 采購(gòu)到入庫(kù)所經(jīng)歷的表-購(gòu)到, 經(jīng)歷, 入庫(kù)-采購(gòu)到入庫(kù)所經(jīng)歷的表-0.請(qǐng)購(gòu)單-創(chuàng)建請(qǐng)購(gòu)單方式有-a.從外掛系統(tǒng)導(dǎo)入請(qǐng)購(gòu)的接口表PO_REQUISITIONS_INTERFACE_ALL,并允許請(qǐng)求(名稱:導(dǎo)入申請(qǐng))SELECT * FROM po_requisitions_interface_all WHERE interface_source_code = 'TEST KHJ'-b.在系統(tǒng)中創(chuàng)建請(qǐng)購(gòu)單(路徑：PO/申請(qǐng)/申請(qǐng))-請(qǐng)購(gòu)單頭信息SELECT prh.requisition_header_id, prh.authorization_status -未

2、審批時(shí)為INCOMPLETE,審批完后為 FROM po_requisition_headers_all prh WHERE prh.segment1 = '600000' AND prh.type_lookup_code = 'PURCHASE'-請(qǐng)購(gòu)單行信息SELECT prl.requisition_line_id, prl.* FROM po_requisition_lines_all prl WHERE prl.requisition_header_id IN (SELECT prh.requisition_header_id FROM po_requ

3、isition_headers_all prh WHERE prh.segment1 = '600000' AND prh.type_lookup_code = 'PURCHASE');-請(qǐng)購(gòu)單分配行SELECT * FROM po_req_distributions_all prda WHERE prda.requisition_line_id IN (SELECT prl.requisition_line_id FROM po_requisition_lines_all prl WHERE prl.requisition_header_id IN (SELE

4、CT prh.requisition_header_id FROM po_requisition_headers_all prh WHERE prh.segment1 = '600000' AND prh.type_lookup_code = 'PURCHASE');-1.采購(gòu)訂單的創(chuàng)建（路徑：PO/采購(gòu)訂單/采購(gòu)訂單）-po_headers_all 采購(gòu)訂單頭表SELECT pha.po_header_id, pha.segment1, pha.agent_id, pha.type_lookup_code, -標(biāo)準(zhǔn)采購(gòu)單為STANDARD,一攬子協(xié)議為BLAN

5、KET decode(pha.approved_flag, 'R', pha.approved_flag, nvl(pha.authorization_status, 'INCOMPLETE'), -審批,未審批時(shí)為INCOMPLETE，審批后為APPROVED po_headers_sv3.get_po_status(pha.po_header_id) -剛下完采購(gòu)單，未審批時(shí)，po狀態(tài)為未完成，審批后，狀態(tài)為批準(zhǔn) FROM po_headers_all pha WHERE segment1 = 300446; -采購(gòu)單號(hào)碼-po_lines_all 采購(gòu)訂單

6、行表SELECT pla.po_line_id, pla.line_type_id FROM po_lines_all pla WHERE po_header_id = (SELECT po_header_id FROM po_headers_all WHERE segment1 = 300446);/*取已審批銷售訂單頭和行的數(shù)據(jù):涉及表： Po_headers_all,Po_lines_all邏輯如下：限制頭表的如下屬性，并通過(guò)Po_header_id把頭、行表關(guān)聯(lián)起來(lái)APPROVED_FLAG=Y*/-po_line_locations_all 采購(gòu)訂單行的發(fā)送表(路徑：PO/采購(gòu)訂單/

7、采購(gòu)訂單/發(fā)運(yùn)(T)-po_line_id=po_lines_all.po_line_id-當(dāng)點(diǎn)擊發(fā)運(yùn)按鈕時(shí)，系統(tǒng)會(huì)自動(dòng)創(chuàng)建第一行發(fā)運(yùn)行，可根據(jù)需要手工創(chuàng)建新的發(fā)運(yùn)行-(例如同一采購(gòu)訂單行的物料可能會(huì)發(fā)往不同的地點(diǎn)，此表記錄物料發(fā)送情況)-下面為取訂單與其發(fā)運(yùn)的關(guān)系（可能存在多次發(fā)運(yùn)）SELECT * FROM po_line_locations_all plla WHERE plla.po_line_id = (SELECT pla.po_line_id FROM po_lines_all pla WHERE po_header_id = (SELECT po_header_id FROM

8、po_headers_all WHERE segment1 = 300446);-或者SELECT * FROM po_line_locations_all plla WHERE plla.po_header_id = (SELECT po_header_id FROM po_headers_all WHERE segment1 = 300446);-4、po_distributions_all 采購(gòu)訂單發(fā)送行的分配表(路徑：PO/采購(gòu)訂單/采購(gòu)訂單/發(fā)運(yùn)(T)/分配(T)-line_location_id=po_line_location_all.line_location_id-發(fā)往同一地

9、點(diǎn)的物料也可能放在不同的子庫(kù)存，此表記錄物料分配情況 SELECT * FROM po_distributions_all pda WHERE pda.line_location_id IN (SELECT plla.line_location_id FROM po_line_locations_all plla WHERE plla.po_line_id = (SELECT pla.po_line_id FROM po_lines_all pla WHERE po_header_id = (SELECT po_header_id FROM po_headers_all WHERE segme

10、nt1 = 300446);-或者SELECT * FROM po_distributions_all WHERE po_header_id = (SELECT po_header_id FROM po_headers_all WHERE segment1 = 300446);-或者SELECT * FROM po_distributions_all pda WHERE pda.po_line_id = (SELECT pla.po_line_id FROM po_lines_all pla WHERE po_header_id = (SELECT po_header_id FROM po_h

11、eaders_all WHERE segment1 = 300446);-對(duì)于po_distribution_all 表而言，如果其SOURCE_DISTRIBUTION_ID 有值, 其對(duì)應(yīng)于計(jì)劃采購(gòu)單發(fā)放/*以上各表從上到下是一對(duì)多關(guān)系的 */-po_releases_all 訂單發(fā)放-該表包含一攬子協(xié)議以及計(jì)劃采購(gòu)單的release，對(duì)于每一張發(fā)放的一攬子協(xié)議或者計(jì)劃采購(gòu)單都有相關(guān)行與之對(duì)應(yīng)-其包含采購(gòu)員，日期，釋放狀態(tài)，釋放號(hào)碼，每一個(gè)釋放行都有至少一條的采購(gòu)單的發(fā)運(yùn)信息與之對(duì)應(yīng)(PO_LINE_LOCATIONS_ALL).-每做一次Realese，PO_distributions_

12、all就會(huì)新增一條記錄。這是計(jì)劃訂單的特性。-SELECT * FROM po_releases_all WHERE po_header_id = < po_header_id >-接收（路徑：INV/事務(wù)處理/接收/接收）-1.rcv_shipment_headers 接收發(fā)送頭表-記錄采購(gòu)訂單的接收情況的頭表SELECT * FROM rcv_shipment_headers rsh WHERE rsh.shipment_header_id IN (SELECT shipment_header_id FROM rcv_shipment_lines WHERE po_header

13、_id = 4105);-2.rcv_shipment_lines 接收發(fā)送行表 -記錄采購(gòu)訂單的發(fā)送的行的接收情況SELECT * FROM rcv_shipment_lines WHERE po_header_id = 4105;-3.rcv_transactions 接收事務(wù)處理表-記錄采購(gòu)訂單的發(fā)送行的RECEIVE的信息SELECT rt.transaction_id, rt.transaction_type, rt.destination_type_code, rt.* FROM rcv_transactions rt WHERE erface_source_code

14、= 'RCV' AND rt.source_document_code = 'PO' AND (rt.po_header_id = (SELECT pha.po_header_id FROM po_headers_all pha WHERE segment1 = 300446) OR rt.po_line_id IN (SELECT pla.po_line_id FROM po_lines_all pla WHERE po_header_id = (SELECT po_header_id FROM po_headers_all WHERE segment1 =

15、300446) OR rt.shipment_header_id = (SELECT rsh.shipment_header_id FROM rcv_shipment_headers rsh WHERE shipment_header_id IN (SELECT shipment_header_id FROM rcv_shipment_lines WHERE po_header_id = 4105) OR rt.shipment_line_id IN (SELECT shipment_line_id FROM rcv_shipment_lines WHERE po_header_id = 41

16、05);-4.rcv_receiving_sub_ledger 暫記應(yīng)付表-記錄采購(gòu)訂單接收后，產(chǎn)生的暫記應(yīng)付信息(接收事務(wù)處理產(chǎn)生的分配行)SELECT * FROM rcv_receiving_sub_ledger WHERE rcv_transaction_id IN (SELECT transaction_id FROM rcv_transactions WHERE po_header_id = 4105);-接受（路徑：INV/事務(wù)處理/接收/接收事務(wù)處理）-接收事務(wù)處理：接收之后，其實(shí)現(xiàn)在還并沒(méi)有入庫(kù)。-rcv_transactions 接收事務(wù)處理表-記錄采購(gòu)訂單的發(fā)送行的ACC

17、EPT的信息SELECT rt.transaction_id, rt.transaction_type, rt.destination_type_code, rt.* FROM rcv_transactions rt WHERE erface_source_code = 'RCV' -做接收的條件 AND rt.source_document_code = 'PO' -做接收的條件 AND rt.transaction_type = 'RECEIVE' -做接收的條件 AND rt.destination_type_code = &#

18、39;RECEIVE' -做接收的條件 AND (rt.po_header_id = (SELECT pha.po_header_id FROM po_headers_all pha WHERE segment1 = 300446) OR rt.po_line_id IN (SELECT pla.po_line_id FROM po_lines_all pla WHERE po_header_id = (SELECT po_header_id FROM po_headers_all WHERE segment1 = 300446) OR rt.shipment_header_id =

19、(SELECT rsh.shipment_header_id FROM rcv_shipment_headers rsh WHERE shipment_header_id IN (SELECT shipment_header_id FROM rcv_shipment_lines WHERE po_header_id = 4105) OR rt.shipment_line_id IN (SELECT shipment_line_id FROM rcv_shipment_lines WHERE po_header_id = 4105);- 入庫(kù)-因?yàn)樯婕叭霂?kù)操作，所以，在庫(kù)存事務(wù)處理表中會(huì)留下相應(yīng)

20、的記錄。-即在Mtl_material_transactions表中，會(huì)存在相應(yīng)的兩條入庫(kù)記錄。SELECT mmt.* FROM mtl_material_transactions mmt WHERE mmt.transaction_type_id = 18 -po接收 AND mmt.transaction_action_id = 27 -接收至庫(kù)存 AND mmt.transaction_source_type_id = 1 -采購(gòu)訂單 AND (mmt.transaction_source_id = 4105 -po_header_id OR mmt.rcv_transaction_i

21、d IN (SELECT rt.transaction_id FROM rcv_transactions rt WHERE erface_source_code = 'RCV' AND rt.source_document_code = 'PO' AND (rt.po_header_id = (SELECT pha.po_header_id FROM po_headers_all pha WHERE segment1 = 300446);-此時(shí),rcv_transactions的狀態(tài)變?yōu)镾ELECT rt.transaction_id, rt.tra

22、nsaction_type, rt.destination_type_code, rt.* FROM rcv_transactions rt WHERE erface_source_code = 'RCV' -做入庫(kù)的條件 AND rt.source_document_code = 'PO' -做入庫(kù)的條件 AND rt.transaction_type = 'DELIVER' -做入庫(kù)的條件 AND rt.destination_type_code = 'INVENTORY' -做入庫(kù)的條件 AND (rt.po_h

23、eader_id = (SELECT pha.po_header_id FROM po_headers_all pha WHERE segment1 = 300446) OR rt.po_line_id IN (SELECT pla.po_line_id FROM po_lines_all pla WHERE po_header_id = (SELECT po_header_id FROM po_headers_all WHERE segment1 = 300446) OR rt.shipment_header_id = (SELECT rsh.shipment_header_id FROM

24、rcv_shipment_headers rsh WHERE shipment_header_id IN (SELECT shipment_header_id FROM rcv_shipment_lines WHERE po_header_id = 4105) OR rt.shipment_line_id IN (SELECT shipment_line_id FROM rcv_shipment_lines WHERE po_header_id = 4105);-退貨-說(shuō)明:-退貨至接收時(shí)，產(chǎn)生一條記錄，退貨至供應(yīng)商時(shí)，產(chǎn)生兩條數(shù)據(jù)。 可見(jiàn)退貨的實(shí)際順序?yàn)? 庫(kù)存-> 接收-> 供

25、應(yīng)商-不管是退貨至接收還是退貨至供應(yīng)商，在事務(wù)處理中，都會(huì)產(chǎn)生兩條記錄。-而且，數(shù)量符號(hào)與接收的數(shù)據(jù)正好相反。而且產(chǎn)生的記錄都是RETURN TO RECEIVING。-1.庫(kù)存退貨至接受SELECT rt.destination_type_code, erface_source_code, rt.* FROM rcv_transactions rt WHERE erface_source_code IS NULL AND rt.transaction_type = 'RETURN TO RECEIVING' -退貨至接受 AND rt.source_d

26、ocument_code = 'PO' AND rt.destination_type_code = 'RECEIVING' AND po_header_id = 4105 AND po_line_id = 9938;SELECT mmt.* FROM mtl_material_transactions mmt WHERE mmt.transaction_source_id = 4105 AND mmt.transaction_type_id = 36 AND mmt.transaction_action_id = 1 AND mmt.transaction_source_type_id = 1;-2.庫(kù)存退貨至供應(yīng)商（產(chǎn)生兩條數(shù)據(jù)。順序?yàn)? 庫(kù)