Consigned Inventory is used to manage material owned by Third party. So supplier places the inventory in customer’s store or warehouse and allows them to sell or consume it. Customer purchases the stock only after he resells the stock or consumes it.
Query to find out all the consigned items
SELECT pov.segment1 vendor_code
FROM po_approved_supplier_list asl,
WHERE asl.vendor_id = pov.vendor_id
AND asl.vendor_site_id = sites.vendor_site_id
AND msi.inventory_item_id = asl.item_id
AND msi.organization_id = asl.using_organization_id
and flvip.lookup_type = 'EGO_INVENTORY_PLANNING_CODE'
and flvip.lookup_code = msi.inventory_planning_code
AND sites.org_id = hou.organization_id
and asl.vendor_business_type = plc.lookup_code
AND asl.asl_status_id = past.status_id
AND plc.lookup_type = 'ASL_VENDOR_BUSINESS_TYPE'
and past.status ='Approved'
and oodu.organization_id = asl.using_organization_id
and pad.asl_id(+) = asl.asl_id
and ph.po_header_id(+) = pad.document_header_id
and pl.po_line_id(+) = pad.document_line_id
and paa.asl_id = asl.asl_id
and pov.segment1 = :vendor_number
--and msi.segment1 = :item_number
and oodu.organization_code = :organization_number
and paa.consigned_from_supplier_flag = 'Y'
order by msi.segment1, oodu.organization_code
1 . Create an item to test Consign inventory process
For more details about creating an item follow the link
2. Create a supplier who supplies this consign item.
Notice that we are associating price 10 against the item.
4. Assign the item to the supplier through Approved Supplier List(ASL)
Attach the Blanket agreement against the ASL
Check the flag for ‘Consigned from Supplier’. This states that all the inventory received into this org will be owned by the supplier.
- Billing Cycle (Days): Indicates how frequently you would like to group the consumption transactions into a consumption advice. Consumption advice is the document against which supplier can raise an invoice and gets paid for. The default is null. If the value is null, a consumption advice is created every time the Create Consumption Advice concurrent program is run.
Last Billing Cycle Date: This is set to the system date when you initially set up Consigned Inventory from Supplier. It is updated each time a new consumption advice is created as per the billing cycle for this ASL. Billing cycle and last billing date in conjunction control the frequency at which the consumption advice is created. A new consumption advice is created when the following occurs:
System date > Last Billing Date + Billing Cycle Days
- Consume on Aging: In some consigned relationships, the supplier stipulates the maximum period for which he can make the material available at your site on consignment basis and you are required take ownership of any balance material after this period. If you are required to consume consigned material within such a stipulated time period, enable the Consume on Aging check box. Consigned Inventory Aging queries would report your commitments from such agreements. Selecting this option does not automatically transfer the ownership of the overdue consigned stocks; however, it enables you to use the Consigned Inventory Aging inquiries to identify the consigned stocks that are overdue for consumption.
- Aging Period (Days): Indicates the maximum number of days the material may be kept in consigned status by your supplier.
5. Create a Purchase Order (PO) and approve it
We are trying to raise a PO to receive the goods from the supplier. The process is similar to that of normal item but the ownership of the item will be with supplier and not the org.
Also note that the Consigned flag is checked for this shipment line (in po_line_locations table)
6. Create a receipt against this PO.
7. Check how the onhand is shown for this item
Onhand shows that the part is owned by the supplier ‘TEST CONSIGN SUPPLIER’
Navigate to ‘Transaction Summaries’ Window
Click on transaction details to view the details of the transaction
Owning Party is the Supplier. Planning Party is the org.
In the transaction details, you can view the consumption advice tab is enabled for this transaction.
From Owning Party is the org.
No distributions are created for this transaction because the inventory is still owned by supplier.
10. Explicit Consumption
The system automatically generates a transfer to regular transaction whenever consumption takes place. Consumption transactions are performed either explicitly or implicitly. Explicit consumption is performed whenever the user specifies the external owning party (the consignor) whose goods will be transferred to the internal organization. Explicit consumption is performed by using the consigned transactions window and performing a transfer to regular transaction. Implicit consumption refers to any type of consumption in which the system automatically determines that consumption is necessary, and selects the owning parties whose stock will be used up to complete the required transaction. So, for example, when you issue material from consigned stock to a discrete job, an implicit consumption is triggered automatically to transfer the stock from consigned to regular before it is issued to the job.
Perform a consigned transaction on this item
Choose ‘Transaction type’ as ‘Transfer to Regular’
’Transfer to Regular’ transaction changes the material from consigned status to org owned. ‘Transfer to consigned’ transaction, restores the material to consigned status. The reversal accounting entries are generated for the corrected quantity.
Enter item TESTCONSIGN and Owning Party (which is the supplier) TEST CONSIGN SUPPLIER
10. Now check the onhand quantity screen for this item
Owning Party is gone showing the supplier no longer owns the part
11. Again check the material transactions
Navigate to ‘Transaction Summaries’ Window
2 records now exist in material transactions table
- Ownership Transfer. So we (org) bought the item from supplier. Value 10 is retrieved from Blanket agreement created in step (3)
- Receipt into stores, which is the previously created transaction
Check the transaction details for ‘Transfer to Regular’
2 records are created
- Increase in transaction quantity for part where the owning org is the inventory organization (trx id : 519988883)
- Decrease in transaction quantity for part where the owning org is supplier (trx id : 519988884)
12. Create consumption advice
The consumption advice is a purchasing document that provides evidence of consumption to the supplier, and can be used to match invoices. Creating and matching invoices against the consumption advice generates the corresponding inventory accounts payable accrual reversal. This matching process performs in the same way as standard functionality.
Check the transaction to see if the ‘Creation Status’ is marked as Created.
You can check from the backend to see if the ‘Consumption_release_id’ is populated.
Following query can be run
,TRUNC (mmt.creation_date) creation_date
,ABS (mmt.transaction_quantity) outbound_quantity
,SUBSTR (mmt.transaction_reference, 1, 15) REFERENCE
FROM mtl_material_transactions mmt,
WHERE mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.organization_id = hou.organization_id(+)
AND mmt.transaction_type_id IN (74, 75)
AND mmt.transaction_id = mct.transaction_id
AND ( pvs.vendor_site_id = mmt.owning_organization_id
OR pvs.vendor_site_id = mmt.xfr_owning_organization_id
AND pov.vendor_id = pvs.vendor_id
--AND mmt.inventory_item_id = :inventory_item_id
AND msi.segment1 = 'TESTCONSIGN'
13. Check in the PO summary screen whether release is created
In the Find Purchase orders screen you can see that release is created. Blanket release (which actually confirms that the item is purchased from the supplier) is created after the consumption advice is created.
After consumption occurs, it cannot be reversed automatically. For example, if you perform a work in process component issue and later reverse the operation through a component return transaction, the consigned attribute is not restored to the material.
After materials are consumed and the consumption advice is generated, you can use any supported method for paying to supplier.