Consigned Inventory Flow in R12

Source

Consigned Inventory Flow in R12

Setup steps

Step 1) Create an Item "CONSIGNED". Apply Purchasing template. Also tick "Use Approved Supplier" in purchasing tab

Step 2) Create a Blanket Purchase Agreement for this item. Take supplier as "Abbott Laboratories, Inc." and site as "CORP HQ". Make sure pay on "Use" is ticked in Supplier setups

Step 3) Create an ASL for this Item and Supplier combination. On Item-attributes form assign above blanket Agreement and  in Inventory tab tick the Consigned from Supplier.

Step 4) Define Consigned\VMI Consumption rules in inventory. Navigate to Setup>Transactions>Consigned\VMI Consumption. Here take From Sub-inventory as "Stores" and To Sub-inventory as "FGI"

Note : The weight value allows you to set the processing order. For example, if two transactions satisfy the transaction criteria, the system processes the transaction with the highest weight.

Test Case

Step 1) Create a Standard PO and Receive the material. Please keep receipt routing as Direct

You can notice Consigned flag on Shipment line is checked automatically. All consigned PO is always closed for invoicing

Step 2) Now go to iSupplier Portal and create an ASN for this PO 6356

ASN1234

Step 3) Now receive against this ASN

Step 4) You can check that no accounting transactions created yet

Step 5) Check owning party information

Step 6) Now when we require material we can transfer material from Stores to FGI. Transfer 10 qty

Step 7) Check Material transactions

Step 8) Now run Consumption Advise

Step 9) Now check the consumption advise from the po summary form. consumption advice create a release against the BPA we have created in setup steps. Remember the BPA number was 6355.

You can see you can not open the release i.e. you can not modify the consumption advise. also this release is closed for receiving as material is already received

Step 10) Now log in iSupplier to see consumption advice

You can click on consumption advise and see it

Inventory Transactions(USEFUL INFORMATION)

Inventory Transactions(USEFUL INFORMATION)

Delivery and Trips

When a Trip record and Trip Stop record will be created.

i) Will it be created at the time of Delivery Creation itself?

If you are coming from Pick Release point of view and decide to autocreate delivery, Yes, a Trip and Two stops will be automatically created.

ii) Inbetween Delivery Creation and Ship Confirmation of the delivery?


If you decide to not to create delivery during pick releasing, you will be either autocreating a delivery for the delivery detail in the shipping screen manually or assigning to an existing open delivery. If you choose to do so, you have to either create trip manually from the shipping screen or assign to existing open trip.


iii) After Ship Confirmation?

No, you have to have a trip and minimum of two stops to ship confirm. But, if you have not created the trip for the delivery in the step2, system will autocreate the trip and stop when you perform ship confirmation step.




iv) Inventory updates will occur after Ship confirmation of the delivery or closing of the trips?


Inventory updates will happen only when Interface Trip Stops program is run successfully for the inventory interface component.

USE  View : WSH_DELIVERY_TRIPS_V

How to check Interface Trip Stop problems

SOURCE

Most of the Interface Trip Stop problems will require a datafix from Development. The following are some of the scenario that can appear for Oracle Order Management Interface issues. The scenario also includes the query which needs to be run where the output will need to be included in the datafix bug.

1) Order line is closed but the delivery detail is not yet OM interfaced:

select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id, wdd.released_status,wdd.creation_date
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.released_status <> 'D'
and wdd.oe_interfaced_flag in ('N','P')
and wdd.source_line_id = ol.line_id
and trunc(wdd.creation_date) > sysdate -365
and ol.open_flag = 'N'
and ol.cancelled_flag = 'N'
and not exists ( select 'x'
from wsh_delivery_details wdd1
where wdd1.source_code = 'OE'
and wdd1.source_line_id = wdd.source_line_id
and wdd1.oe_interfaced_flag = 'Y')
order by 1,2,3;

2) Order line is shipped and one of the WDDs is OM interfaced but other WDDs are not OM interfaced.

select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id,wdd.released_status,wdd.creation_date
from wsh_delivery_details wdd
where wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'N'
and wdd.released_status <> 'D'
and trunc(wdd.creation_date) > sysdate - 365
and exists ( select 'x'
from wsh_delivery_details wdd1
where wdd1.source_code = 'OE'
and wdd.source_line_id = wdd1.source_line_id
and wdd1.oe_interfaced_flag = 'Y')
order by 1,2,3;

Comments: Please provide the raw trace file and debug log file of Interface Trip Stop process (with debug settings as per Metalink Note 290432.1) for a similar order(with same order type, item where user ships partial quantity). These outputs are needed to verify if there are any intermediate commits either in the custom code or supported package extensions/hooks.
Apply split_line_trigger.sql to stop future occurences of this issue. Log a bug once a process(like ITS) errors out because of this trigger.

3) Model components are interfaced to OM but the Model line is failing OM interface.

select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and trunc(wdd.creation_date) > sysdate-365
and wdd.source_line_id = wdd.top_model_line_id
and wdd.oe_interfaced_flag in ('N','P')
and wdd.source_line_id = ol.line_id
and ol.open_flag = 'N'
and not exists (select 'x'
from wsh_delivery_details wdd1
where wdd.top_model_line_id = wdd1.top_model_line_id
and wdd1.source_code = 'OE'
and wdd1.released_status <> 'D'
and wdd1.oe_interfaced_flag = 'Y'
and wdd1.released_status = 'C'
and wdd1.source_line_id <> wdd.top_model_line_id)
order by 1;

4) Trip Stop/delivery do not show up in ITS LOV but some of the details are not OM/INV interfaced.

select distinct wdd.source_header_number, wts.stop_id, wts.trip_id
from wsh_delivery_details wdd,wsh_delivery_assignments wda,wsh_new_deliveries wnd, wsh_delivery_legs wdl,wsh_trip_stops wts
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and (wdd.oe_interfaced_flag in ('N','P') or wdd.inv_interfaced_flag in ('N','P'))
and wdd.delivery_detail_id = wda.delivery_detail_id
and trunc(wdd.creation_date) > sysdate -365
and wda.delivery_id = wnd.delivery_id
and wnd.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and nvl(wts.pending_interface_flag,'X') <> 'Y'
order by 1;

5) Inventory Interface/Interface Trip stop log file shows 'Requisition Line not found' message and the details are not INV interfaced.

select wdd.source_header_number,wdd.source_header_id,wdd.source_line_id,wdd.delivery_detail_id
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.source_line_id = ol.line_id
and trunc(wdd.creation_date) > sysdate -365
and wdd.released_status <> 'D'
and nvl(wdd.inv_interfaced_flag,'N') in ('N','P')
and wdd.source_line_id = ol.line_id
and ol.source_document_type_id = 10
and not exists ( select 'x'
from po_requisition_lines_all pl,
po_req_distributions_all pd
where pl.requisition_line_id = ol.source_document_line_id
and pl.requisition_header_id = ol.source_document_id
and pl.requisition_line_id = pd.requisition_line_id)
order by 1,3,4;

6) ITS completes with 'Batch is not yet fully interfaced to OM'.
You need to use one of the following queries depending on the symptoms.

i) Use this query if the shipped quantity on details is 0/null and the released status is 'Shipped'.
select distinct wdd.source_header_number, wdd.delivery_detail_id,wdd.creation_date
from wsh_delivery_details wdd
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and nvl(wdd.shipped_quantity,0) = 0
and wdd.oe_interfaced_flag = 'N'
and trunc(wdd.creation_date) > sysdate -365
order by 1,2;

Comments: Apply trigger ship_qty_zero_trig.sql to stop future occurences of this issue. Log a bug once a process(like 'Ship Confirm'/'Close Stop') errors out because of this trigger.

ii) Otherwise, use this one
select wdd.source_header_number,wdd.source_line_id,ol.shipping_quantity,ol.flow_status_code, wdd.delivery_detail_id,wdd.creation_date
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and wdd.source_line_id = ol.line_id
and wdd.oe_interfaced_flag = 'P'
and trunc(wdd.creation_date) > sysdate -365
order by 1,2,5;

7) Interface trip Stops program errors out with "for this transaction row the serial records are missing".
select wdd.source_header_number , wdd.delivery_detail_id, wdd.shipped_quantity,
wdd.transaction_temp_id, msi.serial_number_control_code
from wsh_delivery_details wdd, mtl_system_items msi
where wdd.released_status = 'C'
and wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'Y'
and wdd.inv_interfaced_flag in ('N','P')
and wdd.inventory_item_id = msi.inventory_item_id
and wdd.organization_id = msi.organization_id
and wdd.transaction_temp_id is null
and exists (
select 'x'
from wsh_serial_numbers wsn where wsn.delivery_detail_id = wdd.delivery_detail_id );