mtl_supply表的作用

mtl_supply表的作用，是當初培訓時的一個知識點，現在我整理了一下，貼出來
1、當請購單建立、審批後，mtl_supply的變化
a.當建立完請購單,MTL_SUPPLY為空白
b.當審批後,mtl_supply中產生一條資料，supply_type_code=REQ
c.當請購單審批後，請購頭與請購行存入MS.REQ_HEADER_ID, MS.REQ_LINE_ID，此時MS.SUPPLY_TYPE_CODE=REQ

2、當PO建立、審批後，mtl_supply的變化
a.當請購單轉換成po後，在未審批時，MTL_SUPPLY為空白
b.當審批後,mtl_supply中原有的supply_type_code=REQ被更改為supply_type_code=PO
c.一般而言，當請購單自動建立為採購單時，其supply_type_code=REQ被更改為supply_type_code=PO
d.當審批後的po做取消時，MTL_SUPPLY中supply_type_code=PO被更改為supply_type_code=REQ
e.--當調用由請購單自動開採購單的程式時，如果未審批，MS.PO_HEADER_ID    ,MS.PO_LINE_ID,
   --MS.PO_RELEASE_ID, MS.PO_LINE_LOCATION_ID    ,MS.PO_DISTRIBUTION_ID,為空白，如果已審批
   --MS.REQ_HEADER_ID, MS.REQ_LINE_ID被清空，MS.SUPPLY_TYPE_CODE=PO,對於在po單中修改數量或添加新的採購訂單行
   --而未審批，原有已審批的採購訂單行資料不變，但新添加的行未錄入該表
   --當審批後的po做取消時，MTL_SUPPLY中supply_type_code=PO被更改為supply_type_code=REQ,
   --MS.REQ_HEADER_ID, MS.REQ_LINE_ID被填入原有的請購單的頭和行值，同時， MS.PO_HEADER_ID,MS.PO_LINE_ID,
   --MS.PO_RELEASE_ID, MS.PO_LINE_LOCATION_ID,MS.PO_DISTRIBUTION_ID, MS.NEED_BY_DATE,MS.RECEIPT_DATE,
   --MS.EXPECTED_DELIVERY_DATE,  被清空
3、當PO接收後，mtl_supply的變化
  a.當po完全接收後，mtl_supply中原有的supply_type_code=PO被更改為supply_type_code=RECEIVING
  b. --當採購單做完接收後，MTL_SUPPLY中supply_type_code=PO被更改為supply_type_code=RECEIVING,
     --同時 MS.SHIPMENT_HEADER_ID,MS.SHIPMENT_LINE_ID,  MS.RCV_TRANSACTION_ID    ,存入shipment的頭資訊，行資訊，及
     --rcv_transaction的transaction_id
4、當PO某行部分接收後，mtl_supply的變化
  a.當PO某行部分接收後,接受行中原有的supply_type_code=PO被更改為supply_type_code=RECEIVING
5、當PO檢驗後，mtl_supply有什麼變化
  a.當PO檢驗後,接受行中原有的supply_type_code=RECEIVING不變
6、當PO部分入庫後，mtl_supply有什麼變化；全部入庫後呢
  a.當PO部分入庫後,mtl_supply中該行記錄被刪除，而全部入庫則該採購單所有行記錄被刪除
涉及的表

--已審批的請購單
SELECT prh.requisition_header_id, prl.requisition_line_id,prh.segment1
  from PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL
where prh.requisition_header_id = prl.requisition_header_id
   and prh.requisition_header_id = 662
   and prh.authorization_status  = 'APPROVED'
   
--已審批的採購單   
SELECT ph.po_header_id,pl.PO_LINE_ID,ph.segment1,ph.*
  from Po_Lines_all pl,Po_Headers_All ph
where pl.PO_HEADER_ID=ph.po_header_id
/*   and ph.po_header_id = 41526*/
   and ph.authorization_status  = 'APPROVED'
   and NVL(ph.cancel_flag,'N')<>'Y'
   and ph.creation_date>=trunc(sysdate)
--接收

select *
from RCV_SHIPMENT_HEADERS rsh,
      rcv_shipment_lines   rsl
where rsh.shipment_header_id=rsl.shipment_header_id
and rsh.creation_date>=trunc(sysdate)
and rsh.receipt_num='185631'
      
select *
from rcv_transactions rt
where  rt.transaction_id=870339
ｔｒｍ上的解釋如下
MTL_SUPPLY stores incoming supply information for an organization.
This table forms one of the sources in Inventory's Demand-Supply form.
There are four different types of supply captured in this table:

1) Approved Requisitions
2) Approved Purchase orders
3) Shipments from suppliers
4) Intransit shipments from another organization

Types 3 and 4 could be distinguished by the presence of data in the
INTRANSIT_OWNING_ORGANIZATION_ID column, which identifies the
ownership of the items in intransit. If this column is null then it
means that the shipment supply is from a Vendor.
.
This information is used by the available to promise routine to derive
the ATP information as appropiate. Quantities of items in intransit
are also kept track of in the table.
.
Records in MTL_SUPPLY are created every time you approve a requisition
or a PO or create an intransit shipment. One record of REQ type will
be created for one requisition line when the requisition is approved.
One record of PO type will be created per PO distribution when a PO is
approved and one record per shipment line will be created when a
shipment is created.
.
Records in MTL_SUPPLY will be recreated whenever there are
transactions such as return to receiving, return to vendor or
cancellation of purchase orders.
.
Records in MTL_SUPPLY are deleted every time you change the document
status to not approved. For example, a PO would require approval if
you change the line and shipment quantity.
If such a PO is re-approved then the PO supply will be recreated for
the new quantity.
.
The supply type code of a REQ supply record is changed to PO every
time a requisition is autocreated. Similarly the supply type code is
changed from PO to RECEIVING when a PO is fully received. If a PO is
partially received then a supply with the supply type code of
RECEIVING will be created for the quantity received. When the receipt
is delivered the RECEIVING supply is deleted. SHIPMENT supply works in
the same way as PO supply.
.
There is a database trigger on MTL_SUPPLY with the name MTL_SUPPLY_T.
This trigger fires on insert, update or deletion of records in
MTL_SUPPLY. It inserts records into MRP_RELIEF_INTERFACE table

 

本文轉自：http://www.itjaj.com/redirect.php?tid=2109&goto=lastpost