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
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
