[11i distribution] tables purchased to warehouse receiving

-- Tables that have been purchased for warehouse receiving
-- 0. Purchase Order
-- Purchase orders can be created in the following ways:
-- A. Import the requested interface table po_requisitions_interface_all from the plug-in system and allow the request (Name: Import Application)
Select *
From po_requisitions_interface_all
Where interface_source_code = 'test khj'
-- B. Create a requisition in the system (Path: po/application/Application)
-- Requisition header information
Select PRH. requisition_header_id
, PRH. authorization_status -- incomplete if not approved
From po_requisition_headers_all PRH
Where PRH. segment1 = '20140901'
And PRH. type_lookup_code = 'purchase'
-- Requisition Line Information
Select prolactin. requisition_line_id
, Prolactin .*
From po_requisition_lines_all prolactin
Where prolactin. requisition_header_id in (
Select PRH. requisition_header_id
From po_requisition_headers_all PRH
Where PRH. segment1 = '20140901'
And PRH. type_lookup_code = 'purchase ')
-- Purchase order distribution line
Select *
From po_req_distributions_all prda
Where prda. requisition_line_id in
(Select prolactin. requisition_line_id
From po_requisition_lines_all prolactin
Where prolactin. requisition_header_id in
(Select PRH. requisition_header_id
From po_requisition_headers_all PRH
Where PRH. segment1 = '20140901'
And PRH. type_lookup_code = 'purchase '))

-- 1. Create a purchase order (Path: po/purchase order)
-- Po_headers_all purchase order header table
Select PHA. po_header_id,
PHA. segment1,
PHA. agent_id,
PHA. type_lookup_code, -- the standard purchase order is standard, and the package agreement is blanket.
Decode (PHA. approved_flag, 'R', PHA. approved_flag,
Nvl (PHA. authorization_status, 'complete'), -- Approval, incomplete if not approved, and approved after approval
Po_headers_sv3.get_po_status (PHA. po_header_id) -- The po status is incomplete when the purchase order is not approved. After the Po status is approved, the PO status is approved.
From po_headers_all PHA
Where segment1 = 300446 -- purchase order number

-- Po_lines_all: Purchase Order table
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 );

/*
Obtain the data of the approved sales order header and line:
Involved tables: po_headers_all, po_lines_all
The logic is as follows:
Restrict the following attributes of the header table and associate the headers and rows with po_header_id.
Approved_flag = y
*/
-- Po_line_locations_all send table of the purchase order line (Path: po/purchase order/shipment (t ))
-- Po_line_id = po_lines_all.po_line_id
-- When you click the shipping button, the system will automatically create the first line for sending and running. You can manually create a new sending and running as needed.
-- (For example, materials in the same purchase order line may be sent to different locations. This table records the material delivery status)
-- The following figure shows the relationship between order fetch and shipping (there may be multiple shipping requests)

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 po_headers_all
Where segment1 = 300446 ));
-- Or
Select *
From po_line_locations_all PLLA
Where PLLA. po_header_id = (select po_header_id
From po_headers_all
Where segent1 = 300446 );

-- 4. po_distributions_all: distribution table of the sending row of the purchase order (Path: po/purchasing order/shipping (T)/allocation (t ))
-- Line_location_id = po_line_location_all.line_location_id
-- Materials sent to the same location may also be placed in different sub-inventories. This table records material distribution.
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 segment1 = 300446 )))
-- Or
Select *
From po_distributions_all
Where po_header_id =
(Select po_header_id from po_headers_all where segment1 = 300446)

-- Or
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_headers_all
Where segment1 = 300446 ))
-- For a po_distribution_all table, if its source_distribution_id has a value, it corresponds to the issuance of the planned purchase order.

/* The tables above are one-to-multiple relationships from top to bottom */

-- Po_releases_all order issuance
-- This table contains the package agreement and the release of the planned purchase order. Each issued package agreement or planned purchase order has corresponding rows.
-- It contains the buyer, date, release status, and release number. Each release row has at least one shipping information corresponding to the purchase order (po_line_locations_all ).
-- Each time realese is executed, po_distributions_all adds a new record. This is a feature of planned orders.
--
Select * From po_releases_all where po_header_id = <po_header_id> ;;

-- Receive (Path: inv/transaction processing/receiving)

-- 1. rcv_shipment_headers receiving and sending headers table
-- Header table that records the receipt of purchase orders
Select *
From rcv_shipment_headers RSH
Where RSH. shipment_header_id in
(Select shipment_header_id
From rcv_shipment_lines
Where po_header_id = 4105 );

-- 2. rcv_shipment_lines receiving and sending row table
-- Record the receipt of the row sent by the purchase order
Select * From rcv_shipment_lines where po_header_id = 4105

-- 3. rcv_transactions receiving transaction processing table
-- Record the receive information of the sending row of the purchase order

Select Rt. transaction_id,
Rt. transaction_type,
Rt. destination_type_code,
Rt .*
From rcv_transactions RT
Where Rt. interface_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)
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 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)
)
-- 4. rcv_mongoing_sub_ledger
-- Records the temporary notes and payables generated after the purchase order is received (the allocated rows generated by the receipt transaction)
Select *
From rcv_receiving_sub_ledger
Where rcv_transaction_id in
(Select transaction_id
From rcv_transactions
Where po_header_id = 4105 );

-- Accept (Path: inv/transaction processing/receiving Transaction Processing)
-- Receive transaction processing: After receiving the transaction, it has not been written into the database yet.
-- Rcv_transactions receiving transaction processing table
-- Record the accept information of the sending line of the purchase order

Select Rt. transaction_id, Rt. transaction_type, Rt. destination_type_code, Rt .*
From rcv_transactions RT
Where Rt. interface_source_code = 'rcv '-- conditions for receiving
And Rt. source_document_code = 'po' -- conditions for receiving
And Rt. transaction_type = 'receive '-- conditions for receiving
And Rt. destination_type_code = 'receive '-- conditions for receiving
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 = (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)
)

-- Warehouse receiving
-- Because warehouse receiving operations are involved, the corresponding records are left in the inventory transaction processing table.
-- In the mtl_material_transactions table, two warehouse receiving records exist.
Select Mt .*
From mtl_material_transactions Mt
Where Mt. transaction_type_id = 18 -- po receiving
And Mt. transaction_action_id = 27 -- receive to inventory
And Mt. transaction_source_type_id = 1 -- purchase order
And (mt. transaction_source_id = 4105 -- po_header_id
Or Mt. rcv_transaction_id in
(Select Rt. transaction_id
From rcv_transactions RT
Where Rt. interface_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 ))))
-- The rcv_transactions status changes

Select Rt. transaction_id, Rt. transaction_type, Rt. destination_type_code, Rt .*
From rcv_transactions RT
Where Rt. interface_source_code = 'rcv '-- conditions for warehouse receiving
And Rt. source_document_code = 'po' -- conditions for warehouse receiving
And Rt. transaction_type = 'delimiter' -- conditions for warehouse receiving
And Rt. destination_type_code = 'ventory '-- conditions for warehouse receiving
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 = (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)
)

-- Return
-- Note:
-- A record is generated when the return is received, and two data records are generated when the return is made to the supplier. The actual order of return is: Inventory ----> receipt ----> supplier
-- Two records are generated during transaction processing, whether returned or returned to the supplier.
-- In addition, The number symbol is the opposite of the received data. And the generated records are all return to processing ing.
-- 1. Inventory return to accept
Select Rt. destination_type_code, Rt. interface_source_code, Rt .*
From rcv_transactions RT
Where Rt. interface_source_code is null
And Rt. transaction_type = 'return to processing in' -- return to accept
And Rt. source_document_code = 'po'
And Rt. destination_type_code = 'grouping ing'
And po_header_id = 4105
And po_line_id = 9938.

Select Mt .*
From mtl_material_transactions Mt
Where Mt. transaction_source_id = 4105
And Mt. transaction_type_id = 36
And Mt. transaction_action_id = 1
And Mt. transaction_source_type_id = 1

-- 2. The inventory is returned to the supplier (two pieces of data are generated. Order: Inventory ----> receipt ----> supplier)
-- A. Inventory return to receive
Select Rt. destination_type_code, Rt. interface_source_code, Rt .*
From rcv_transactions RT
Where Rt. interface_source_code is null
And Rt. transaction_type = 'return to processing in' -- returns the result to the receiver first.
And Rt. source_document_code = 'po'
And Rt. destination_type_code = 'ventory'
And po_header_id = 4105

-- B. Receive the return to the supplier
Select Rt. destination_type_code, Rt. interface_source_code, Rt .*
From rcv_transactions RT
Where Rt. interface_source_code is null
And Rt. transaction_type = 'return to vendor' -- return to supplier
And Rt. source_document_code = 'po'
And Rt. destination_type_code = 'grouping ing'
And po_header_id = 4105

Select Mt .*
From mtl_material_transactions Mt
Where Mt. transaction_source_id = 4105
And Mt. transaction_type_id = 36 -- return to supplier
And Mt. transaction_action_id = 1 -- issue from stock
And Mt. transaction_source_type_id = 1 -- purchase order

 

This article from: http://bbs.itjaj.com/redirect.php? Tid = 2835 & goto = lastpost