Unable to receive against Internal Requisition though the shipment is shown as In-Transit. We should be able to receive In-Transit quantity at destination organization.
The issue can be reproduced at will with the following steps:
- From Inventory Responsibility, Choose Destination Organization
- Open Manage Shipments form: Navigation: Transactions > Receiving > Manage Shipments
- Query for the problematic shipment
- The shipment lines are shown
- Open Receipts form: Navigation: Transactions > Receiving > Receipts
- Enter problematic Shipment Number in Shipment Field and click Find
- There are no lines available for Receipt
Missing MTL_SUPPLY records for the shipment expected to receive against Internal Requisition.
Run the below SQL to see if the problematic shipment is returned:
SELECT rsh.shipment_num "Shipment Number",
rsl.line_num "Shipment Line Number",
rsl.from_organization_id,
rsl.to_organization_id,
rsl.shipment_line_id
FROM rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.source_document_code = 'REQ'
AND rsl.shipment_line_status_code = 'EXPECTED'
AND rsl.quantity_received = 0
AND rsh.shipment_num = <-- did-->
AND NOT EXISTS (SELECT 1
FROM mtl_supply ms
WHERE ms.shipment_line_id = rsl.shipment_line_id);
Now run the below script for Missing MTL_SUPPLY entry. Here Expected Receipt date is taken as Internal requisition's need_by_date:
DECLARE
l_to_org_prim_qty NUMBER := -1;
l_intransit_owning_org_id NUMBER := -1;
l_from_sub mtl_supply.from_subinventory%TYPE;
l_trx_date wsh_trip_stops.actual_departure_date%TYPE;
l_receipt_date mtl_supply.receipt_date%TYPE;
l_expected_delivery_date mtl_supply.expected_delivery_date%TYPE;
l_ship_method_code wsh_trips.ship_method_code%TYPE;
l_ship_from_location_id wsh_delivery_details.ship_from_location_id%TYPE;
l_ship_to_site_use_id wsh_delivery_details.ship_to_site_use_id%TYPE;
l_intransit_time NUMBER;
l_lead_time NUMBER;
l_return_status VARCHAR2(30);
l_insert_ms NUMBER := 1; --1 -> insert into MS ,
--0 -> insert into MS ,
l_picking_line_id wsh_delivery_details.delivery_detail_id%TYPE;
CURSOR c_rsl_with_no_ms IS
SELECT rsl.*
FROM rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.source_document_code = 'REQ'
AND rsl.shipment_line_status_code = 'EXPECTED'
AND rsl.quantity_received = 0
AND rsh.shipment_num = <-- did-->
AND NOT EXISTS (SELECT 1
FROM mtl_supply ms
WHERE ms.shipment_line_id = rsl.shipment_line_id);
rsl_rec c_rsl_with_no_ms%ROWTYPE;
BEGIN
dbms_output.Put_line('mtl_supply creation script started...');
OPEN c_rsl_with_no_ms;
LOOP
FETCH c_rsl_with_no_ms INTO rsl_rec;
EXIT WHEN c_rsl_with_no_ms%NOTFOUND;
l_insert_ms := 1;
--reseting the value so that mtl supply record will get inserted
SELECT mmt.owning_organization_id,
subinventory_code,
picking_line_id
INTO l_intransit_owning_org_id, l_from_sub, l_picking_line_id
FROM mtl_material_transactions mmt,
rcv_shipment_lines rsl
WHERE mmt.transaction_id = rsl.mmt_transaction_id
AND rsl.shipment_line_id = rsl_rec.shipment_line_id;
IF ( rsl_rec.unit_of_measure <> rsl_rec.primary_unit_of_measure ) THEN
l_to_org_prim_qty :=
rsl_rec.quantity_shipped *
po_uom_s.Po_uom_convert(rsl_rec.unit_of_measure, rsl_rec.primary_unit_of_measure, rsl_rec.item_id);
ELSE
l_to_org_prim_qty := rsl_rec.quantity_shipped;
END IF;
SELECT wts.actual_departure_date,
wt.ship_method_code,
wdd.ship_from_location_id,
wdd.ship_to_site_use_id
INTO l_trx_date, l_ship_method_code, l_ship_from_location_id,
l_ship_to_site_use_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trips wt
WHERE wts.stop_id = wdl.pick_up_stop_id
AND wts.trip_id = wt.trip_id
AND wdl.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.released_status = 'C'
AND wdd.delivery_detail_id = l_picking_line_id;
/* Got the follwing code from WSH_SHIP_CONFIRM_ACTIONS.Interface_Detail_To_Inv()
to calculate the expected_arrival_date.
During Iterface Trip Stop, Shipping populates the expected arrival date in MTI and
Inventory passes that value to Receiving during Shipment creation as
rti.expected_receipt_date.
(After shipment creation RTI & MTI data will be purged)
As we are in situation, Shipment creation is done, but mtl_supply data is not there
and not MTI and RTI data, recalculating the expected receipt date using Shipping code */
IF ( l_ship_method_code IS NOT NULL ) THEN
--Passing only Ship_Method_Code to API WSH_MAP_LOCATION_REGION_PKG.Get_Transit_Time
--Setting following variables to NULL so that WSH_MAP_LOCATION_REGION_PKG.Get_Transit_Time
--will not fetch Ship_Method_Code using Carrier, Service Level and Mode of Transport.
wsh_map_location_region_pkg.Get_transit_time (
p_ship_from_loc_id => l_ship_from_location_id,
p_ship_to_site_id => l_ship_to_site_use_id,
p_ship_method_code => l_ship_method_code, p_carrier_id => NULL,
---l_carrier_id,
p_service_code => NULL, ---l_service_code,
p_mode_code => NULL, ----l_mode_code,
p_from => 'OM', x_transit_time => l_intransit_time,
x_return_status => l_return_status);
IF ( l_return_status <> wsh_util_core.g_ret_sts_success ) THEN
dbms_output.Put_line('intransit time calculation error...'
||l_return_status);
dbms_output.Put_line('rsl_id:'
||rsl_rec.shipment_line_id);
l_insert_ms := 0; --don't create mtl_supply
END IF;
ELSE
l_intransit_time := 0;
END IF; --if ( l_ship_method_code is not null ) then
IF l_insert_ms = 1 THEN
--Calculation of receipt date
l_receipt_date := l_trx_date + Nvl(l_intransit_time, 0);
IF Nvl(rsl_rec.item_id, -1) != -1 THEN
BEGIN
SELECT postprocessing_lead_time
INTO l_lead_time
FROM mtl_system_items
WHERE inventory_item_id = rsl_rec.item_id
AND organization_id = rsl_rec.to_organization_id;
EXCEPTION
WHEN OTHERS THEN
l_insert_ms := 0;
dbms_output.Put_line('lead time calculation error...'
||SQLERRM);
dbms_output.Put_line('rsl_id:'
||rsl_rec.shipment_line_id);
END;
ELSE--if nvl(rsl_rec.item_id,-1) != -1 then
l_lead_time := 0;
END IF; --if nvl(rsl_rec.item_id,-1) != -1 then
--Calculation of expected delivery date
l_expected_delivery_date := l_receipt_date + l_lead_time;
END IF; ----if l_insert_ms = 1 then
IF l_insert_ms = 1 THEN
INSERT INTO mtl_supply
(supply_type_code,
supply_source_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
req_header_id,
req_line_id,
shipment_header_id,
shipment_line_id,
item_id,
item_revision,
quantity,
unit_of_measure,
receipt_date,
need_by_date,
destination_type_code,
from_organization_id,
from_subinventory,
to_organization_id,
to_subinventory,
intransit_owning_org_id,
cost_group_id,
to_org_primary_quantity,
to_org_primary_uom)
SELECT 'SHIPMENT',
rsl_rec.shipment_line_id,
rsl_rec.last_updated_by,
rsl_rec.last_update_date,
Nvl(rsl_rec.last_update_login, -1),
rsl_rec.created_by,
rsl_rec.creation_date,
prl.requisition_header_id,
rsl_rec.requisition_line_id,
rsl_rec.shipment_header_id,
rsl_rec.shipment_line_id,
rsl_rec.item_id,
rsl_rec.item_revision,
rsl_rec.quantity_shipped,
rsl_rec.unit_of_measure,
l_receipt_date,
prl.need_by_date,
rsl_rec.destination_type_code,
rsl_rec.from_organization_id,
l_from_sub,
rsl_rec.to_organization_id,
rsl_rec.to_subinventory,
l_intransit_owning_org_id,
rsl_rec.cost_group_id,
l_to_org_prim_qty,
rsl_rec.primary_unit_of_measure
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = rsl_rec.requisition_line_id;
dbms_output.Put_line('rsl_id:'
||rsl_rec.shipment_line_id);
END IF; --if l_insert_ms = 1 then
END LOOP;
CLOSE c_rsl_with_no_ms;
dbms_output.Put_line('Please commit the script if satisfy the transaction...');
NULL;
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line('Exception Occurred'
||SQLERRM);
dbms_output.Put_line('rsl_id:'
||rsl_rec.shipment_line_id);
IF ( c_rsl_with_no_ms%isopen ) THEN
CLOSE c_rsl_with_no_ms;
END IF;
ROLLBACK;
dbms_output.Put_line('Rollbacked the transaction...');
END;
No comments:
Post a Comment