[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NAME,
NVL(ORDER_ID_PRIORITY, 999999999),
DECODE(ORDER_ID_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(INVOICE_VALUE_PRIORITY, 999999999),
DECODE(INVOICE_VALUE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(SCHEDULE_DATE_PRIORITY, 999999999),
DECODE(SCHEDULE_DATE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(SHIPMENT_PRI_PRIORITY, 999999999),
DECODE(SHIPMENT_PRI_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(TRIP_STOP_DATE_PRIORITY, 999999999),
DECODE(TRIP_STOP_DATE_SORT, 'A', 'ASC', 'D', 'DESC', '')
FROM WSH_PICK_SEQUENCE_RULES
WHERE PICK_SEQUENCE_RULE_ID = v_psr_id
AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
NVL(END_DATE_ACTIVE, TRUNC(SYSDATE) + 1);
select MISI.SOURCE_SUBINVENTORY, MSISR.pick_uom_code, Nvl(MISI.max_minmax_quantity,0), NVL(MISI.FIXED_LOT_MULTIPLE, -1)
FROM MTL_ITEM_SUB_INVENTORIES MISI,
MTL_SECONDARY_INVENTORIES msi,
MTL_SECONDARY_INVENTORIES MSISR
WHERE MISI.organization_id = p_Org_id
AND MISI.SECONDARY_INVENTORY = MSI.SECONDARY_INVENTORY_NAME
AND MISI.ORGANIZATION_ID = MSI.ORGANIZATION_ID
and MISI.INVENTORY_ITEM_ID = p_Item_id
and MISI.source_type = 3 --(for Subinventory)
AND MISI.source_organization_id = p_Org_id
and MISI.SECONDARY_INVENTORY = p_picking_sub
and MSISR.SECONDARY_INVENTORY_NAME = MISI.SOURCE_SUBINVENTORY
AND MSISR.ORGANIZATION_ID = MISI.ORGANIZATION_ID
order by MSI.picking_order;
PROCEDURE update_wdd_repl_status (p_deliv_detail_id IN NUMBER
, p_repl_status IN VARCHAR2
, p_deliv_qty IN NUMBER DEFAULT NULL
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
l_detail_info_tab.DELETE;
l_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec
);
print_debug('Error returned from Create_Update_Delivery_Detail IN api update_wdd_repl_status');
print_debug('Unexpected errror from Create_Update_Delivery_Detail IN api update_wdd_repl_status');
print_debug('Error update_wdd_repl_status: ' || sqlcode || ',' || sqlerrm);
END update_wdd_repl_status;
SELECT delivery_detail_id
FROM wsh_delivery_details wdd
WHERE wdd.source_code = 'OE'
AND wdd.organization_id = p_org_id
AND wdd.requested_quantity > 0
-- excluding Replenishment Requested status
AND wdd.released_status in ('R', 'B') and wdd.replenishment_status = 'R'
-- there might not be reservation
AND NOT EXISTS
(select 1
from mtl_reservations mr
WHERE MR.DEMAND_SOURCE_LINE_ID = wdd.source_line_id
and MR.DEMAND_SOURCE_HEADER_ID =
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id)
and MR.demand_source_type_id =
decode(wdd.source_document_type_id, 10, 8, 2)
and MR.SUBINVENTORY_CODE IS NOT NULL --locator is not needed,
)-- Exclude detailed RSV
AND NOT EXISTS
(select wrd.demand_line_detail_id
from WMS_REPLENISHMENT_DETAILS wrd
where wrd.demand_line_detail_id = wdd.delivery_detail_id
and wrd.demand_line_id = wdd.source_line_id
and wrd.organization_id = wdd.organization_id
AND wrd.organization_id = p_org_id)
AND wdd.batch_id = p_batch_id;
l_detail_info_tab.DELETE;
l_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec
);
print_debug('Error returned from Create_Update_Delivery_Detail IN api Revert_ALL_WDD_dynamic_repl');
print_debug('Unexpected errror from Create_Update_Delivery_Detail api IN Revert_ALL_WDD_dynamic_repl');
SELECT demand_line_detail_id
FROM wms_repl_demand_gtmp
WHERE inventory_item_id = p_item_id
AND ORGANIZATION_ID = p_org_id
AND repl_level = 1
AND demand_type_id <> 4;
l_detail_info_tab.DELETE;
l_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec
);
print_debug('Error returned from Create_Update_Delivery_Detail IN api revert_consol_item_changes');
print_debug('Unexpected errror from Create_Update_Delivery_Detail api IN revert_consol_item_changes');
DELETE FROM wms_repl_demand_gtmp
WHERE inventory_item_id = p_item_id
AND ORGANIZATION_ID = p_org_id;
G_backorder_deliv_tab.DELETE;
G_backorder_qty_tab.DELETE;
G_dummy_table.DELETE;
G_backorder_deliv_tab.DELETE;
G_backorder_qty_tab.DELETE;
G_dummy_table.DELETE;
SELECT demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
Quantity,
Uom_code,
quantity_in_repl_uom,
REPL_UOM_code,
Expected_ship_date,
Repl_To_Subinventory_code,
repl_status,
RELEASED_STATUS
FROM wms_repl_demand_gtmp
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id
AND repl_level = P_REPL_LEVEL
ORDER BY repl_sequence_id;
SELECT NVL(SUM(mr.primary_reservation_quantity),0)
INTO l_rsvd_demand_qty
FROM mtl_reservations mr ,
(select distinct organization_id, inventory_item_id,
demand_line_id, demand_header_id
from WMS_REPL_DEMAND_GTMP
where inventory_item_id = l_item_id
and organization_id = l_org_id
and demand_type_id <> 4
and repl_level = nvl(p_repl_level,1)) wrdg_v
WHERE mr.organization_id = wrdg_v.organization_id
AND mr.inventory_item_id = wrdg_v.inventory_item_id
AND MR.DEMAND_SOURCE_LINE_ID = wrdg_v.DEMAND_line_ID
AND MR.DEMAND_SOURCE_HEADER_ID = wrdg_v.DEMAND_HEADER_ID;
SELECT Nvl(sum(wrdg.QUANTITY),0) INTO l_gtmp_demand_qty
from WMS_REPL_DEMAND_GTMP WRDG
WHERE Wrdg.INVENTORY_ITEM_ID = l_item_id
and wrdg.ORGANIZATION_ID = L_ORG_ID
and wrdg.demand_type_id <> 4
and wrdg.repl_level = nvl(p_repl_level, 1);
SELECT Nvl(SUM(wdd.requested_quantity),0) INTO l_other_wdd_qty
FROM wsh_delivery_details wdd
WHERE wdd.organization_id = l_org_id
AND wdd.inventory_item_id = l_item_id
AND((wdd.released_status NOT IN ('R','N','B','X','C') AND wdd.replenishment_status IS NULL)
OR (wdd.released_status = 'B' AND wdd.replenishment_status IS NOT NULL))
AND NOT EXISTS
(SELECT 1
FROM wms_repl_demand_gtmp wrdg
WHERE wrdg.organization_id = wdd.organization_id
AND wrdg.inventory_item_id = wdd.inventory_item_id
AND wrdg.demand_line_detail_id = wdd.delivery_detail_id
AND wrdg.demand_header_id = inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id)
AND wrdg.demand_line_id = wdd.source_line_id
)
AND inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) IN
(
SELECT DISTINCT demand_header_id FROM wms_repl_demand_gtmp wrdg1
WHERE WRDG1.organization_id = l_org_id
AND WRDG1.inventory_item_id = l_item_id
);
SELECT SUM((mtrl.quantity - (Nvl(mtrl.quantity_detailed,0) +
Nvl(mtrl.quantity_delivered,0)))) INTO l_open_mo_qty
FROM mtl_txn_request_lines mtrl,
Mtl_txn_request_headers mtrh
WHERE mtrl.header_id = mtrh.header_id
AND mtrl.organization_id = mtrh.organization_id
AND mtrl.organization_id = L_ORG_ID
AND mtrl.inventory_item_id = L_item_id
AND mtrl.organization_id in (select organization_id from mtl_parameters where wms_enabled_flag = 'Y')
AND MTRH.move_order_type = 2
and mtrl.line_status in (3,7) -- only approved and pre-approved
and mtrl.transaction_type_id = 64
and mtrl.transaction_source_type_id = 4
and mtrl.from_SUBINVENTORY_CODE = x_consol_item_repl_tbl(i).repl_to_subinventory_code
GROUP BY mtrl.inventory_item_id;
-- l_dyn_bkord_dd_id_tab.DELETE;
-- l_push_bkord_dd_id_tab.DELETE;
SELECT NVL(SUM(mr.primary_reservation_quantity),0)
INTO l_rsvd_demand_line_qty
FROM mtl_reservations mr
WHERE mr.organization_id =l_org_id
AND mr.inventory_item_id =l_item_id
AND MR.DEMAND_SOURCE_LINE_ID = l_demand_line_id
AND MR.DEMAND_SOURCE_HEADER_ID = l_demand_header_id;
-- Shipping will update current WDD with qty = l_atr
-- and create a new WDD with qty = (l_quantity - l_atr)
-- 2-Split the Qty in the GTMP :UPDATE current demand RECORD with qty = l_atr
-- We do NOT need to insert the new record in GTMP with qty = (l_quantity - l_atr)
-- since we are going to delete downstream from GTMP for ALL unmet demand lines
-- -3-Adjust l_prim_repl_qty := l_prim_repl_qty +l_atr;
l_detail_id_tab.DELETE;
-- update the qty in the existing GTMP record
UPDATE wms_repl_demand_gtmp
SET QUANTITY = l_atr,
QUANTITY_IN_REPL_UOM = l_new_qty_in_repl_uom
WHERE demand_line_detail_id = l_demand_line_detail_id;
-- add to the backorder table to backorder demand AND delete from GTMP
IF p_repl_type = g_push_repl THEN
-- we can call the backorder API for WDD lines that are already backordered
-- Now, Shipping team has made changes and it will honor
-- consolidation of the backordered api feature
-- it does NOT matter whether DD_id was alredy backordered
l_cnt := l_cnt+1; --BUG10131943
-- delete this demand line from the GTMP
-- subtract the qty from the consol record
DELETE FROM wms_repl_demand_gtmp
WHERE demand_line_detail_id = l_demand_line_detail_id;
-- we have not updated the qty l_prim_repl_qty yet
-- so no need to update the consol qty for this item
-- Add here to list of delivery_details to be backordered
cnt := cnt+1;
print_debug( 'PUSH - NUMBER OF lines to be deleted :'||l_push_bkord_dd_id_tab.count);
DELETE FROM wms_repl_demand_gtmp
WHERE demand_line_detail_id = l_push_bkord_dd_id_tab(k)
AND inventory_item_id = l_item_id
AND organization_id = L_ORG_ID
AND Nvl(repl_level,1) = p_repl_level;
print_debug( 'DYNAMIC - NUMBER OF lines to be deleted :'||l_dyn_bkord_dd_id_tab.count);
DELETE FROM wms_repl_demand_gtmp
WHERE demand_line_detail_id = l_dyn_bkord_dd_id_tab(k)
AND inventory_item_id = l_item_id
AND organization_id = L_ORG_ID
AND Nvl(repl_level,1) = p_repl_level;
x_consol_item_repl_tbl.DELETE(i);
l_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec
);
print_debug('Error returned from Create_Update_Delivery_Detail IN api ADJUST_ATR_FOR_ITEM');
print_debug('Unexpected errror from Create_Update_Delivery_Detail api IN ADJUST_ATR_FOR_ITEM');
l_dyn_bkord_dd_id_tab.DELETE;
l_push_bkord_dd_id_tab.DELETE;
l_consol_item_repl_tbl.DELETE;
g_item_uom_conversion_tb.DELETE;
l_consol_item_repl_tbl.DELETE;
print_debug('RECORD has been deleted from consol table, Skipping it');
l_consol_item_repl_tbl.DELETE;
l_consol_item_repl_tbl.DELETE;
g_item_uom_conversion_tb.DELETE;
l_consol_item_repl_tbl.DELETE;
g_item_uom_conversion_tb.DELETE;
l_consol_item_repl_tbl.DELETE;
print_debug('RECORD has been deleted from consol table, Skipping it');
l_consol_item_repl_tbl.DELETE;
SELECT
item_id,header_id,line_id,delivery_detail_id,demand_type_id,requested_quantity,requested_quantity_uom,
quantity_in_repl_uom, expected_ship_date , replenishment_status,released_status,
sort_attribute1 ,sort_attribute2,sort_attribute3,sort_attribute4,sort_attribute5
FROM (
SELECT wdd.inventory_item_id as item_id,
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) AS header_id,
wdd.source_line_id AS line_id,
wdd.delivery_detail_id delivery_detail_id,
decode(wdd.source_document_type_id, 10, 8, 2) as demand_type_id, -- for SO=2 and Internal Order=8
wdd.requested_quantity requested_quantity, -- this is always stored in primary UOM
wdd.requested_quantity_uom requested_quantity_uom,
ROUND((wdd.requested_quantity * get_conversion_rate(wdd.inventory_item_id,
wdd.requested_quantity_uom,
p_repl_UOM)),g_conversion_precision) as quantity_in_repl_uom,
decode(p_Scheduled_Ship_Date_To,
null,
decode(p_Scheduled_Ship_Date_From,
null,
null,
NVL(WMS_REPLENISHMENT_PVT.Get_Expected_Time(decode(wdd.source_document_type_id, 10, 8, 2),
wdd.source_header_id,
wdd.source_line_id,
wdd.delivery_detail_id),
WDD.date_scheduled)),
NVL(WMS_REPLENISHMENT_PVT.Get_Expected_Time(decode(wdd.source_document_type_id, 10, 8, 2),
wdd.source_header_id,
wdd.source_line_id,
wdd.delivery_detail_id),
WDD.date_scheduled)) as expected_ship_date,
wdd.replenishment_status,
wdd.released_status,
-- For order clause by select column values for WSH_PICK_SEQUENCE_RULES
-- get for sort_attribute1
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(1).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute1,
-- get for sort_attribute2
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(2).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute2,
-- get for sort_attribute3
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(3).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute3,
-- get for sort_attribute4
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(4).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute4,
-- get for sort_attribute5
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(5).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
null),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute5
FROM oe_order_lines_all oel,
wsh_delivery_details wdd
WHERE wdd.organization_id = p_organization_id
AND wdd.source_code = 'OE'
AND oel.booked_flag = 'Y'
AND oel.open_flag = 'Y'
AND wdd.requested_quantity > 0
AND oel.line_id = wdd.source_line_id
-- excluding Replenishment requested status
AND wdd.released_status in ('R', 'B')
and nvl(wdd.replenishment_status, 'C') = 'C'
-- there might not be reservation
AND not exists
(select 1
from mtl_reservations mr
WHERE MR.DEMAND_SOURCE_LINE_ID = wdd.source_line_id
and MR.DEMAND_SOURCE_HEADER_ID =
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id)
AND MR.SUBINVENTORY_CODE IS NOT NULL) --locator is not needed -- Exclude detailed RSV
--Exclude those demands that have suub
-- specified, we can not use sub = Forward_pick_sub either
-- becs FP sub info is not availble while marking 'RC' at the pick drop time when rsv = N
AND wdd.subinventory IS NULL
/*10131943- Made the query on MTL_ABC_ASSIGNMENTS a sub-query because an item can have multiple ABC compile/group assignments*/
AND (NOT EXISTS (SELECT 1
FROM mtl_abc_assignments maa
WHERE maa.inventory_item_id = oel.inventory_item_id)
OR EXISTS (SELECT 1
FROM mtl_abc_assignments maa
WHERE maa.inventory_item_id = oel.inventory_item_id
AND Nvl(maa.assignment_group_id, -1) = Nvl(p_ABC_assignment_group_id, Nvl(maa.assignment_group_id, -1))
AND Nvl(maa.abc_class_id, -1) = Nvl(p_abc_class_id, Nvl(maa.abc_class_id, -1))
)
)
AND (nvl(wdd.customer_id, -1) = nvl(p_customer_id,nvl(wdd.customer_id, -1))
OR wdd.customer_id in (SELECT party_id FROM hz_cust_accounts
WHERE customer_class_code = p_customer_class
AND status <> 'I'
AND party_id = nvl(p_customer_id,party_id)))
AND wdd.source_header_type_id = nvl(P_ORDER_TYPE_ID,source_header_type_id)
AND NOT exists
(select wrd.demand_line_detail_id
from WMS_REPLENISHMENT_DETAILS wrd
where wrd.demand_line_detail_id = wdd.delivery_detail_id
and wrd.demand_line_id = wdd.source_line_id
and wrd.organization_id = wdd.organization_id
And wrd.organization_id = p_organization_id)
AND wdd.INVENTORY_ITEM_ID = NVL(P_ITEM_ID, wdd.INVENTORY_ITEM_ID)
AND nvl(wdd.carrier_id, -1) =
NVL(p_Carrier_id, nvl(wdd.carrier_id, -1))
AND nvl(wdd.SHIP_METHOD_CODE, '@@@') =
NVL(p_Ship_Method_code, nvl(wdd.SHIP_METHOD_CODE, '@@@')) -- mandatory field
) X
WHERE x.quantity_in_repl_uom > 0
AND x.expected_ship_date <= (SYSDATE + p_scheduled_ship_date_to )-- MANDATORY FIELD
ORDER BY
x.sort_attribute1,
x.sort_attribute2,
x.sort_attribute3,
x.sort_attribute4,
x.sort_attribute5
FOR UPDATE SKIP LOCKED;
SELECT X.inventory_item_id inventory_item_id,
X.total_demand_qty total_demand_qty,
X.date_required date_required
FROM (SELECT inventory_item_id,
sum(quantity_in_repl_uom) as total_demand_qty,
MIN(expected_ship_date) as date_required,
MIN(repl_sequence_id) AS order_priority -- to avoid conflicting situation
FROM WMS_REPL_DEMAND_GTMP
where organization_id = p_organization_id
group by inventory_item_id
order by decode(p_Sort_Criteria,
1,
sum(quantity_in_repl_uom),
count(1)) DESC, order_priority ASC) X
WHERE ROWNUM <= nvl(p_max_NUM_items_for_repl, 1e25);
g_ordered_psr.DELETE;
l_item_id_tb.DELETE;
l_header_id_tb.DELETE;
l_line_id_tb.DELETE;
l_delivery_detail_id_tb.DELETE;
l_demand_type_id_tb.DELETE;
l_requested_quantity_tb.DELETE;
l_requested_quantity_uom_tb.DELETE;
l_quantity_in_repl_uom_tb.DELETE;
l_expected_ship_date_tb.DELETE;
l_repl_status_tb.DELETE;
l_released_status_tb.DELETE;
l_attr1_tab.DELETE;
l_attr2_tab.DELETE;
l_attr3_tab.DELETE;
l_attr4_tab.DELETE;
l_attr5_tab.DELETE;
insert into WMS_REPL_DEMAND_GTMP
(Repl_Sequence_id,
repl_level,
Inventory_item_id,
Organization_id,
demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
Quantity,
Uom_code,
quantity_in_repl_uom,
REPL_UOM_code,
Expected_ship_date,
Repl_To_Subinventory_code,
filter_item_flag,
repl_status,
repl_type,
RELEASED_STATUS)
values
(WMS_REPL_DEMAND_GTMP_S.NEXTVAL,
p_repl_level,
l_item_id_tb(k),
p_organization_id,
l_header_id_tb(k),
l_line_id_tb(k),
l_delivery_detail_id_tb(k),
l_demand_type_id_tb(k),
l_requested_quantity_tb(k),
l_requested_quantity_uom_tb(k),
l_quantity_in_repl_uom_tb(k),
p_repl_UOM,
l_expected_ship_date_tb(k),
P_Forward_Pick_Sub,
NULL,
l_repl_status_tb(k),
1,
l_released_status_tb(k)); -- for Push replenishment
DELETE FROM WMS_REPL_DEMAND_GTMP
WHERE expected_ship_date < (SYSDATE - p_scheduled_ship_date_from);
DELETE FROM WMS_REPL_DEMAND_GTMP
WHERE (inventory_item_id)
IN (SELECT inventory_item_id
from WMS_REPL_DEMAND_GTMP
where organization_id = p_organization_id
group by inventory_item_id
having sum(quantity_in_repl_uom) < p_Min_repl_qty_threshold);
DELETE FROM WMS_REPL_DEMAND_GTMP
where(inventory_item_id) in
(SELECT inventory_item_id from WMS_REPL_DEMAND_GTMP
where organization_id = p_organization_id group by inventory_item_id
having count(1) < Nvl(p_min_order_lines_threshold,1));
print_debug('Error inserting wms_repl_demand_gtmp table');
l_item_id_tb.DELETE;
l_total_demand_qty_tb.DELETE;
l_date_required_tb.DELETE;
x_consol_item_repl_tbl.DELETE;
INSERT INTO WMS_REPL_DEMAND_GTMP
(Repl_Sequence_id,
repl_level,
Inventory_item_id,
Organization_id,
demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
quantity_in_repl_uom,
REPL_UOM_code,
Quantity,
Uom_code,
Expected_ship_date,
Repl_To_Subinventory_code,
filter_item_flag,
repl_status,
repl_type,
RELEASED_STATUS)
VALUES
(WMS_REPL_DEMAND_GTMP_S.NEXTVAL,
p_repl_level,
l_item_id_tb(k),
p_organization_id, --for push repl, it is same though
-9999,
-9999,
-9999,
-9999,
-9999,
p_repl_UOM,
-9999,
'@@@',
l_date_required_tb(k),
P_Forward_Pick_Sub,
'Y',
NULL,
1, -- For Push replenishment
NULL);
--2 Delete those item records in the GTMP table whose item_ids are not same as item_id
--records that correspond to Filter_item_flag columns = 'Y' records
DELETE FROM wms_repl_demand_gtmp
WHERE filter_item_flag IS NULL
AND inventory_item_id NOT IN (SELECT inventory_item_id FROM
wms_repl_demand_gtmp WHERE
Nvl(filter_item_flag,'N') = 'Y');
DELETE FROM wms_repl_demand_gtmp
WHERE filter_item_flag = 'Y';
print_debug('Error inserting in WRDG temp table with Filter_item_flag = y : '||SQLCODE ||' '||SQLERRM);
x_consol_item_repl_tbl.DELETE;
x_consol_item_repl_tbl.DELETE;
SELECT COUNT(1) INTO l_temp_cnt FROM wms_repl_demand_gtmp;
x_consol_item_repl_tbl.DELETE;
SELECT repl_level
INTO l_repl_level
FROM wms_replenishment_details
WHERE source_type_id = 4
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND source_header_id = p_move_order_header_id
AND source_line_id = p_move_order_line_id
AND ROWNUM = 1;
SELECT (quantity - Nvl(quantity_detailed,0) - Nvl(quantity_delivered,0))
, from_subinventory_code
, from_locator_id
INTO l_create_qty
, l_mo_source_sub
, l_mo_source_loc
FROM mtl_txn_request_lines
WHERE line_id = p_move_order_line_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
select MISI.SOURCE_SUBINVENTORY into l_next_lvl_src_sub
FROM MTL_ITEM_SUB_INVENTORIES MISI
WHERE MISI.organization_id = p_organization_id
and MISI.INVENTORY_ITEM_ID = p_inventory_item_id
and MISI.source_type = 3 --(for Subinventory)
and MISI.SECONDARY_INVENTORY = l_mo_source_sub
AND ROWNUM = 1;
SELECT DISTINCT organization_id
FROM wms_repl_demand_gtmp
WHERE repl_level = p_repl_level;
SELECT inventory_item_id,
sum(quantity_in_repl_uom) as total_demand_qty,
repl_to_subinventory_code,
repl_uom_code
FROM wms_repl_demand_gtmp
WHERE organization_id = v_org_id
AND repl_level = p_repl_level
GROUP BY inventory_item_id, repl_to_subinventory_code,repl_uom_code
ORDER BY inventory_item_id, repl_to_subinventory_code;
l_org_id_tb.DELETE;
DELETE FROM wms_repl_demand_gtmp WHERE repl_level = p_repl_level;
l_item_id_tb.DELETE;
l_total_demand_qty_tb.DELETE;
l_repl_to_sub_code_tb.DELETE;
l_repl_uom_code_tb.DELETE;
l_consol_item_repl_tbl.DELETE;
DELETE FROM wms_repl_demand_gtmp
WHERE repl_level = p_repl_level
AND organization_id = l_org_id_tb(j);
DELETE FROM wms_repl_demand_gtmp
WHERE repl_level = p_repl_level
AND organization_id = l_org_id_tb(j);
SELECT COUNT(1) INTO l_temp_cnt FROM wms_repl_demand_gtmp;
DELETE FROM wms_repl_demand_gtmp WHERE repl_level=Nvl(p_repl_level,1);
DELETE FROM wms_repl_demand_gtmp WHERE repl_level = (Nvl(p_repl_level,1)+1);
Select repl_sequence_id,
demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
quantity,
uom_code,
expected_ship_date,
quantity_in_repl_uom,
repl_uom_code,
Nvl(repl_level,1),
repl_type
FROM WMS_REPL_DEMAND_GTMP
WHERE ORGANIZATION_ID = p_org_id
AND inventory_item_id = p_item_id
AND REPL_TO_SUBINVENTORY_CODE = P_FP_SUB
order by Repl_Sequence_id;
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mo_line_id;
l_trohdr_rec.last_updated_by := fnd_global.user_id;
l_trohdr_rec.last_update_date := sysdate;
l_trohdr_rec.last_update_login := fnd_global.user_id;
l_item_id_tb.DELETE;
l_org_id_tb.DELETE;
l_demand_header_id_tb.DELETE;
l_demand_line_id_tb.DELETE;
l_demand_type_id_tb.DELETE;
l_requested_quantity_tb.DELETE;
l_requested_quantity_uom_tb.DELETE;
l_quantity_in_repl_uom_tb.DELETE;
l_expected_ship_date_tb.DELETE;
l_repl_to_sub_code_tb.DELETE;
l_repl_uom_code_tb.DELETE;
-- Now take care of stamping right UOM and updated qty
IF (l_src_pick_uom IS NOT NULL) THEN
IF l_conversion > 0 THEN
-- convert the final qty into apporopriate qty based on l_src_pick_uom
p_consol_item_repl_tbl(i).final_replenishment_qty :=
ROUND((l_txn_prim_qty * get_conversion_rate(p_consol_item_repl_tbl(i).Item_id,
inv_cache.item_rec.primary_uom_code,
l_src_pick_uom )),
g_conversion_precision);
-- UPDATE THE UOM CODE AS WELL
p_consol_item_repl_tbl(i).repl_uom_code := l_src_pick_uom;
l_trolin_tbl.DELETE;
l_trolin_tbl(l_order_count).last_updated_by := fnd_global.user_id;
l_trolin_tbl(l_order_count).last_update_date := sysdate;
l_trolin_tbl(l_order_count).last_update_login := fnd_global.user_id;
fnd_msg_pub.delete_msg(i);
l_trolin_tbl.DELETE(l_order_count);
fnd_msg_pub.delete_msg(i);
l_trolin_tbl.DELETE(l_order_count);
l_trolin_tbl.DELETE(l_order_count);
l_trolin_tbl.DELETE(l_order_count);
update_wdd_repl_status (p_deliv_detail_id => l_demand_line_detail_id
, p_repl_status => 'R' -- for completed status
, x_return_status => l_return_status
);
l_trolin_tbl.DELETE(l_order_count);
-- STORE DATA here and do BULK INSERT later
l_organization_id_tab(l_index) := p_consol_item_repl_tbl(i).organization_id;
-- entry in the GTMP table will be deleted at the end of the processing of the replenishment calls
END LOOP; -- for each demand line for an item
INSERT INTO wms_replenishment_details
(Replenishment_id,
Organization_id,
source_header_id,
Source_line_id,
Source_line_detail_id,
Source_type_id,
demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
Inventory_item_id,
Primary_UOM,
Primary_Quantity,
demand_sort_order,
repl_level,
repl_type,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)VALUES (
WMS_REPLENISHMENT_DETAILS_S.NEXTVAL,
l_organization_id_tab(k),
l_mo_header_id_tab(k),
l_mo_line_id_tab(k),
NULL,
4, -- For Move Orders
l_demand_header_id_tab(k),
l_demand_line_id_tab(k),
l_demand_line_detail_id_tab(k),
l_demand_type_id_tab(k),
l_item_id_tab(k),
l_demand_uom_code_tab(k),
l_demand_quantity_tab(k),
l_sequence_id_tab(k),
l_repl_level_tab(k),
l_repl_type_tab(k),
Sysdate,
Sysdate,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.user_id);
l_organization_id_tab.DELETE;
l_mo_header_id_tab.DELETE;
l_mo_line_id_tab.DELETE;
l_demand_header_id_tab.DELETE;
l_demand_line_id_tab.DELETE;
l_demand_line_detail_id_tab.DELETE;
l_demand_type_id_tab.DELETE;
l_item_id_tab.DELETE;
l_demand_uom_code_tab.DELETE;
l_demand_quantity_tab.DELETE;
l_sequence_id_tab.DELETE;
l_repl_level_tab.DELETE;
l_repl_type_tab.DELETE;
-- just insert records into the WRD table
IF L_DEBUG = 1 THEN
print_debug('******Create_Reservation = N and Next LEVEL repl');
INSERT INTO wms_replenishment_details
(Replenishment_id,
Organization_id,
source_header_id,
Source_line_id,
Source_line_detail_id,
Source_type_id,
demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
Inventory_item_id,
Primary_UOM,
Primary_Quantity,
demand_sort_order,
repl_level,
repl_type,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT
WMS_REPLENISHMENT_DETAILS_S.NEXTVAL, --Replenishment_id,
p_consol_item_repl_tbl(i).organization_id, --Organization_id,
l_x_trolin_tbl(1).header_id, -- source_header_id,
l_x_trolin_tbl(1).line_id, -- Source_line_id,
NULL, -- Source_line_detail_id,
4, -- Source_type_id,
demand_header_id,
demand_line_id,
demand_line_detail_id, -- stored as -9999 for next level
demand_type_id, -- stored as 4 for next level
p_consol_item_repl_tbl(i).item_id, -- Inventory_item_id,
inv_cache.item_rec.primary_uom_code, -- Primary_UOM,
l_txn_prim_qty, -- Primary_Quantity,
repl_sequence_id , --demand_sort_order,
repl_level,
repl_type,
Sysdate,
Sysdate,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.user_id
FROM WMS_REPL_DEMAND_GTMP
WHERE ORGANIZATION_ID = p_consol_item_repl_tbl(i).ORGANIZATION_ID
AND inventory_item_id = p_consol_item_repl_tbl(i).item_id
AND REPL_TO_SUBINVENTORY_CODE = p_consol_item_repl_tbl(i).repl_to_subinventory_code;
--update the MO line quantity_detailed field or close the MO
BEGIN
SELECT NVL(SUM(primary_quantity), 0)
,NVL(sum(transaction_quantity),0)
,COUNT(*)
INTO l_prim_quantity_detailed
,l_quantity_detailed_conv
,l_num_detail_recs
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_x_trolin_tbl(1).line_id;
-- update the quantity detailed correctly
UPDATE mtl_txn_request_lines mtrl
SET mtrl.quantity_detailed = l_quantity_detailed
where line_id = l_x_trolin_tbl(1).line_id;
print_debug('Updated the detailed qty on the MO line');
UPDATE mtl_txn_request_lines mtrl
SET mtrl.quantity_detailed = l_quantity_detailed
where line_id = l_x_trolin_tbl(1).line_id;
-- Call to check to insert into temp table next level record after
--
IF (( l_prev_item_id IS NULL AND
l_prev_sub_code IS NULL ) OR
l_prev_org_id <> p_consol_item_repl_tbl(i).organization_id OR
l_prev_item_id <> p_consol_item_repl_tbl(i).Item_id OR
l_prev_sub_code <> l_src_sub) THEN
l_source_sub_atr := NULL;
-- store the index of the consol table to be deleted outside the loop
l_del_index := l_del_index +1;
p_consol_item_repl_tbl.DELETE(l_del_consol_item_tb(j));
INSERT INTO WMS_REPL_DEMAND_GTMP
(Repl_Sequence_id,
repl_level,
Inventory_item_id,
Organization_id,
demand_header_id,
demand_line_id,
DEMAND_LINE_DETAIL_ID,
demand_type_id,
quantity_in_repl_uom,
REPL_UOM_code,
Quantity,
Uom_code,
Expected_ship_date,
Repl_To_Subinventory_code,
filter_item_flag,
repl_type)
VALUES
(WMS_REPL_DEMAND_GTMP_S.NEXTVAL,
Nvl(p_repl_level,1) + 1,
l_item_id_tb(k),
l_org_id_tb(k),
l_demand_header_id_tb(k),
l_demand_line_id_tb(k),
-9999,
l_demand_type_id_tb(k),
l_quantity_in_repl_uom_tb(k),
l_repl_uom_code_tb(k),
l_requested_quantity_tb(k),
l_requested_quantity_uom_tb(k),
l_expected_ship_date_tb(k),
l_repl_to_sub_code_tb(k),
NULL,
2);
l_detail_id_delete_tab num_tab;
SELECT Repl_Sequence_id,
demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
Nvl(quantity,0),
uom_code,
expected_ship_date,
Nvl(quantity_in_repl_uom,0),
repl_level,
repl_type
FROM WMS_REPL_DEMAND_GTMP
WHERE ORGANIZATION_ID = P_ORG_ID
AND inventory_item_id = p_item_id
AND repl_to_subinventory_code = p_fp_sub
order by Repl_Sequence_id;
select mtrl.line_id,
mtrl.header_id,
-- get all open move order qty that are not part of WRD
-- once fully transacted, mtrl.QUANTITY = mtrl.QUANTITY_DETAILED + mtrl.QUANTITY_DELIVERED
-- here quantity containes QUANTITY_DETAILED at the current sub as
-- well AS untouched move ORDER qty
(mtrl.QUANTITY - NVL(mtrl.QUANTITY_DELIVERED,0)) AS quantity,
mtrl.uom_code,
Nvl(mtrl.quantity_detailed,0) AS quantity_detailed
from mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
where mtrl.header_id = mtrh.header_id
and mtrl.organization_id = P_ORG_ID
and mtrl.inventory_item_id = p_item_id
and mtrl.to_subinventory_code = P_FP_SUB
and mtrl.line_status in (3, 7) -- only approved and pre-approved
and mtrh.move_order_type = 2 -- for replenishment only
AND not exists
(select WRD.Source_line_id
from WMS_REPLENISHMENT_DETAILS wrd
where WRD.source_header_id = MTRL.HEADER_ID
AND WRD.Source_line_id = MTRL.LINE_ID
And wrd.organization_id = mtrl.organization_id
And wrd.organization_id = p_org_id )
UNION
-- get all Open Move Order qty that are left out due to rounding or other reason
select mtrl.line_id,
mtrl.header_id,
-- qty inside function Round(**) below might NOT be allocated yet based on
-- how the stock up was RUN but we have earmarked that much mo qty through WRD for certain demands
-- so we need to subtract that much qty from existing_mo_qty for availble_mo_qty
(mtrl.QUANTITY- NVL(mtrl.QUANTITY_DELIVERED,0) -
ROUND((WMS_REPLENISHMENT_PVT.get_conversion_rate(p_item_id, x.primary_uom, mtrl.uom_code)* Nvl(X.quantity,0)),5)) AS quantity,
mtrl.uom_code,
Nvl(mtrl.quantity_detailed,0) AS quantity_detailed
FROM
mtl_txn_request_lines mtrl,
(
SELECT WRD.Source_line_id, WRD.source_header_id,
wrd.inventory_item_id, SUM(wrd.Primary_quantity) quantity,
wrd.organization_id,wrd.primary_uom
FROM WMS_REPLENISHMENT_DETAILS wrd, wsh_delivery_details wdd
WHERE wrd.demand_line_detail_id = wdd.delivery_detail_id
AND wrd.demand_line_id = wdd.source_line_id
AND wrd.organization_id = P_ORG_ID
AND wrd.organization_id = wdd.organization_id
GROUP BY wrd.organization_id,
WRD.source_header_id,
WRD.Source_line_id,
wrd.inventory_item_id,
wrd.primary_uom) X
WHERE X.inventory_item_id = mtrl.inventory_item_id
and x.source_header_id = MTRL.HEADER_ID
AND x.Source_line_id = MTRL.LINE_ID
and x.organization_id = mtrl.organization_id
and mtrl.organization_id = P_ORG_ID
and mtrl.inventory_item_id = p_item_id
and mtrl.to_subinventory_code = P_FP_SUB
and mtrl.line_status in (3, 7)
order by quantity DESC;
SELECT demand_line_detail_id, demand_line_id
FROM WMS_REPLENISHMENT_DETAILS WRD
WHERE WRD.organization_id = P_ORG_ID
AND WRD.source_header_id = P_mo_header_id
AND WRD.Source_line_id = P_mo_line_id;
IF (l_detail_id_delete_tab.COUNT()>0) THEN
FOR d in 1 .. l_detail_id_delete_tab.COUNT() LOOP
IF (l_detail_id_delete_tab(d) = l_demand_line_detail_id) THEN
l_detail_removed := 'Y';
update_wdd_repl_status (p_deliv_detail_id => l_demand_line_detail_id
, p_repl_status => 'R' -- for completed status
, x_return_status => l_return_status
);
-- STORE DATA here and do BULK INSERT later
l_organization_id_tab(l_index) := x_consol_item_repl_tbl(i).organization_id;
-- This set can be different than set inserted in the WRD table
-- Store here to BULK DELETE later
l_detail_id_delete_tab(l_del_index) := l_demand_line_detail_id;
print_debug('BULK INSERT ALL CONSUMED DEMANDS IN WRD table' );
INSERT INTO WMS_REPLENISHMENT_DETAILS
(Replenishment_id,
Organization_Id,
source_header_id,
Source_line_id,
Source_line_detail_id,
Source_type_id,
demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
Inventory_item_id,
Primary_UOM,
Primary_Quantity,
demand_sort_order,
repl_level,
repl_type,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)VALUES (
WMS_REPLENISHMENT_DETAILS_S.NEXTVAL,
l_organization_id_tab(k),
l_mo_header_id_tab(k),
l_mo_line_id_tab(k),
NULL,
4, -- For Move Orders
l_demand_header_id_tab(k),
l_demand_line_id_tab(k),
l_demand_line_detail_id_tab(k),
l_demand_type_id_tab(k),
l_item_id_tab(k),
l_demand_uom_code_tab(k),
l_demand_quantity_tab(k),
l_sequence_id_tab(k),
l_repl_level_tab(k),
l_repl_type_tab(k),
Sysdate,
Sysdate,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.user_id
);
l_organization_id_tab.DELETE;
l_mo_header_id_tab.DELETE;
l_mo_line_id_tab.DELETE;
l_demand_header_id_tab.DELETE;
l_demand_line_id_tab.DELETE;
l_demand_line_detail_id_tab.DELETE;
l_demand_type_id_tab.DELETE;
l_item_id_tab.DELETE;
l_demand_uom_code_tab.DELETE;
l_demand_quantity_tab.DELETE;
l_sequence_id_tab.DELETE;
l_repl_level_tab.DELETE;
l_repl_type_tab.DELETE;
FORALL k in 1 .. l_detail_id_delete_tab.COUNT()
DELETE From WMS_REPL_DEMAND_GTMP
WHERE demand_line_detail_id = l_detail_id_delete_tab(k);
l_detail_id_delete_tab.DELETE;
SELECT repl_sequence_id, demand_header_id, demand_line_id,
demand_line_detail_id,
demand_type_id, quantity, uom_code, expected_ship_date,
quantity_in_repl_uom, repl_status
FROM WMS_REPL_DEMAND_GTMP
WHERE ORGANIZATION_ID = P_ORG_ID
AND inventory_item_id = p_item_id
AND repl_to_subinventory_code = p_fp_sub
order by Repl_Sequence_id;
l_rsv_id_tb.DELETE;
update_wdd_repl_status (p_deliv_detail_id => l_demand_line_detail_id
, p_repl_status => 'C' -- for completed status
, x_return_status => l_return_status
);
DELETE FROM wms_repl_demand_gtmp
WHERE Organization_id = x_consol_item_repl_tbl(i).organization_id
AND INVENTORY_ITEM_ID = x_consol_item_repl_tbl(i).item_id
AND demand_type_id = l_demand_type_id
AND DEMAND_LINE_DETAIL_ID = l_demand_line_detail_id
AND demand_header_id = l_demand_header_id
AND demand_line_id = l_demand_line_id;
UPDATE mtl_reservations
SET subinventory_code = x_consol_item_repl_tbl(i).repl_to_subinventory_code
WHERE reservation_id = l_rsv_id_tb(k);
update_wdd_repl_status (p_deliv_detail_id => l_demand_line_detail_id
, p_repl_status => 'C' -- for completed status
, x_return_status => l_return_status
);
DELETE FROM wms_repl_demand_gtmp
WHERE Organization_id = x_consol_item_repl_tbl(i).organization_id
AND INVENTORY_ITEM_ID = x_consol_item_repl_tbl(i).item_id
AND demand_type_id = l_demand_type_id
AND DEMAND_LINE_DETAIL_ID = l_demand_line_detail_id
AND demand_header_id = l_demand_header_id
AND demand_line_id = l_demand_line_id;
update_wdd_repl_status (p_deliv_detail_id => l_demand_line_detail_id
, p_repl_status => 'C' -- for completed status
, x_return_status => l_return_status
);
DELETE FROM wms_repl_demand_gtmp
WHERE Organization_id = x_consol_item_repl_tbl(i).organization_id
AND INVENTORY_ITEM_ID = x_consol_item_repl_tbl(i).item_id
AND demand_type_id = l_demand_type_id
AND DEMAND_LINE_DETAIL_ID = l_demand_line_detail_id
AND demand_header_id = l_demand_header_id
AND demand_line_id = l_demand_line_id;
DELETE FROM wms_repl_demand_gtmp
WHERE Organization_id = x_consol_item_repl_tbl(i).organization_id
AND INVENTORY_ITEM_ID = x_consol_item_repl_tbl(i).item_id
AND demand_type_id = l_demand_type_id
AND DEMAND_LINE_DETAIL_ID = l_demand_line_detail_id
AND demand_header_id = l_demand_header_id
AND demand_line_id = l_demand_line_id;
-- store the index of the consol table to be deleted outside the loop
l_del_index := l_del_index +1;
x_consol_item_repl_tbl.DELETE(l_del_consol_item_tb(j));
SELECT NVL(wts.planned_departure_date,
NVL(wdd.date_scheduled,
NVL(ool.schedule_ship_date, ool.promise_date))) AS expected_ship_date
FROM wsh_delivery_details wdd, oe_order_lines_all ool,
wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
wsh_trip_stops wts, wsh_trips wt
WHERE wdd.delivery_detail_id = p_delivery_line_id
AND ool.line_id = p_source_line_id
AND wdd.source_line_id = ool.line_id
AND wdd.source_header_id = p_source_header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
AND wda.delivery_id = wnd.delivery_id (+)
AND wnd.delivery_id = wdl.delivery_id (+)
AND (wdl.sequence_number IS NULL OR
wdl.sequence_number = (SELECT MIN(sequence_number)
FROM wsh_delivery_legs wdl_first_leg
WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
AND wdl.pick_up_stop_id = wts.stop_id (+)
AND wts.trip_id = wt.trip_id (+);
SELECT MIN(NVL(wts.planned_departure_date, wdd.date_scheduled))
-- MIN(NVL(wts.planned_departure_date,
-- NVL(wdd.date_scheduled,
-- NVL(ool.schedule_ship_date, ool.promise_date)))) AS min_expected_ship_date,
FROM wsh_new_deliveries wnd, wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl, wsh_trip_stops wts
-- oe_order_lines_all ool
WHERE wdd.delivery_detail_id = p_delivery_detail_id
-- AND wdd.source_line_id = ool.line_id (+)
AND wnd.shipment_direction = 'O'
AND wnd.delivery_id = wda.delivery_id (+)
AND wda.delivery_detail_id = wdd.delivery_detail_id (+)
AND wnd.delivery_id = wdl.delivery_id (+)
AND (wdl.sequence_number IS NULL OR
wdl.sequence_number = (SELECT MIN(sequence_number)
FROM wsh_delivery_legs wdl_first_leg
WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
AND wdl.pick_up_stop_id = wts.stop_id (+)
GROUP BY wnd.organization_id, wnd.delivery_id, wts.stop_id;
SELECT SECONDARY_INVENTORY, PICK_UOM_CODE
FROM (select MISI.SECONDARY_INVENTORY,
MSI.PICK_UOM_CODE,
MSIB.PRIMARY_UOM_CODE,
get_conversion_rate(MISI.INVENTORY_ITEM_id,
MSI.PICK_UOM_CODE,
MSIB.PRIMARY_UOM_CODE) AS CONVERSION_RATE
from MTL_ITEM_SUB_INVENTORIES MISI,
MTL_SECONDARY_INVENTORIES MSI,
MTL_SYSTEM_ITEMS_B MSIB
WHERE MISI.organization_id = P_Org_id
and MISI.INVENTORY_ITEM_ID = P_Item_id
AND MISI.SECONDARY_INVENTORY = MSI.SECONDARY_INVENTORY_NAME
AND MISI.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.PICK_UOM_CODE IS NOT NULL
--AND MOD(P_PRIMARY_DEMAND_QTY,(get_conversion_rate(MISI.INVENTORY_ITEM_id,MSI.PICK_UOM_CODE,MSIB.PRIMARY_UOM_CODE)))=0
AND P_PRIMARY_DEMAND_QTY/get_conversion_rate(MISI.INVENTORY_ITEM_id,MSI.PICK_UOM_CODE,MSIB.PRIMARY_UOM_CODE) >= 1 --13419401
AND get_conversion_rate(MISI.INVENTORY_ITEM_id,
MSI.PICK_UOM_CODE,
MSIB.PRIMARY_UOM_CODE) > 0
AND MISI.INVENTORY_ITEM_id = MSIB.INVENTORY_ITEM_id
AND MISI.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
ORDER BY CONVERSION_RATE DESC, MSI.PICKING_ORDER) X
WHERE ROWNUM = 1;
SELECT PICK_UOM_CODE INTO x_repl_uom_code
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = p_org_id
and secondary_inventory_name = x_to_subinventory_code;
SELECT wdd.inventory_item_id as item_id,
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) AS header_id,
wdd.source_line_id AS line_id,
wdd.delivery_detail_id,
decode(wdd.source_document_type_id, 10, 8, 2) as demand_type_id, -- for SO=2 and Internal Order=8
wdd.requested_quantity, -- this is always stored in primary UOM
wdd.requested_quantity_uom,
NVL(WMS_REPLENISHMENT_PVT.Get_Expected_Time(decode(wdd.source_document_type_id, 10, 8, 2),
wdd.source_header_id,
wdd.source_line_id,
wdd.delivery_detail_id),
WDD.date_scheduled) as expected_ship_date,
wdd.subinventory,
wdd.replenishment_status,
wdd.released_status,
-- get for sort_attribute1
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(1).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute1,
-- get for sort_attribute2
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(2).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute2,
-- get for sort_attribute3
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(3).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute3,
-- get for sort_attribute4
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(4).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute4,
-- get for sort_attribute5
To_number(DECODE(p_Release_Sequence_Rule_Id,
null,
null,
DECODE(g_ordered_psr(5).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute5
FROM wsh_delivery_details wdd
WHERE wdd.source_code = 'OE'
AND wdd.organization_id = p_org_id
AND wdd.requested_quantity > 0
-- excluding Replenishment Requested status
AND wdd.released_status in ('R', 'B') and wdd.replenishment_status = 'R'
-- there might not be reservation
AND NOT EXISTS
(select 1
from mtl_reservations mr
WHERE MR.DEMAND_SOURCE_LINE_ID = wdd.source_line_id
and MR.DEMAND_SOURCE_HEADER_ID =
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id)
and MR.demand_source_type_id =
decode(wdd.source_document_type_id, 10, 8, 2)
and MR.SUBINVENTORY_CODE IS NOT NULL --locator is not needed
and (nvl(mr.staged_flag,'N') = 'N' and nvl(mr.detailed_quantity,0) = 0)) --13398141 ; Exclude detailed RSV
(select wrd.demand_line_detail_id
from WMS_REPLENISHMENT_DETAILS wrd
where wrd.demand_line_detail_id = wdd.delivery_detail_id
and wrd.demand_line_id = wdd.source_line_id
and wrd.organization_id = wdd.organization_id
AND wrd.organization_id = p_org_id)
AND wdd.batch_id = P_Batch_id
ORDER BY sort_attribute1,
sort_attribute2,
sort_attribute3,
sort_attribute4,
sort_attribute5
FOR UPDATE SKIP LOCKED;
SELECT inventory_item_id,
sum(quantity_in_repl_uom) as total_demand_qty,
MIN(expected_ship_date) as date_required,
repl_to_subinventory_code,
repl_uom_code
FROM wms_repl_demand_gtmp
WHERE organization_id = p_org_id
GROUP BY inventory_item_id, repl_to_subinventory_code,repl_uom_code
ORDER BY inventory_item_id, repl_to_subinventory_code;
g_ordered_psr.DELETE;
l_item_id_tb.DELETE;
l_header_id_tb.DELETE;
l_line_id_tb.DELETE;
l_delivery_detail_id_tb.DELETE;
l_demand_type_id_tb.DELETE;
l_requested_quantity_tb.DELETE;
l_requested_quantity_uom_tb.DELETE;
l_expected_ship_date_tb.DELETE;
l_repl_status_tb.DELETE;
l_released_status_tb.DELETE;
l_attr1_tb.DELETE;
l_attr2_tb.DELETE;
l_attr3_tb.DELETE;
l_attr4_tb.DELETE;
l_attr5_tb.DELETE;
l_repl_to_sub_code_tb.DELETE;
l_quantity_in_repl_uom_tb.DELETE;
l_repl_uom_code_tb.DELETE;
select pick_from_subinventory INTO l_PR_sub
from wsh_picking_batches where batch_id = p_batch_id;
-- Must delete this records jth element from the bulk uploaded tables later
l_revert_wdd := TRUE;
-- Must delete this records jth element from the bulk uploaded tables later
l_revert_wdd := TRUE;
-- Must delete this records jth element from the bulk uploaded tables later
IF (l_debug = 1) THEN
print_debug('Skipping REPL for the delivery detail:'||l_delivery_detail_id_tb(j) );
-- Note: these delete in pl/sql table do not reindex
-- data. element deleted at J remains NULL. it works sort of
-- key-value pair for id and values
l_item_id_tb.DELETE(j);
l_header_id_tb.DELETE(j);
l_line_id_tb.DELETE(j);
l_delivery_detail_id_tb.DELETE(j);
l_demand_type_id_tb.DELETE(j);
l_requested_quantity_tb.DELETE(j);
l_requested_quantity_uom_tb.DELETE(j);
l_expected_ship_date_tb.DELETE(j);
l_repl_status_tb.DELETE(j);
l_released_status_tb.DELETE(j);
l_repl_to_sub_code_tb.DELETE(j);
l_quantity_in_repl_uom_tb.DELETE(j);
l_repl_uom_code_tb.DELETE(j);
l_item_id_tb.DELETE;
l_header_id_tb.DELETE;
l_line_id_tb.DELETE;
l_delivery_detail_id_tb.DELETE;
l_demand_type_id_tb.DELETE;
l_requested_quantity_tb.DELETE;
l_requested_quantity_uom_tb.DELETE;
l_expected_ship_date_tb.DELETE;
l_repl_status_tb.DELETE;
l_released_status_tb.DELETE;
l_repl_to_sub_code_tb.DELETE;
l_quantity_in_repl_uom_tb.DELETE;
l_repl_uom_code_tb.DELETE;
INSERT INTO WMS_REPL_DEMAND_GTMP
(Repl_Sequence_id,
repl_level,
Inventory_item_id,
Organization_id,
demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
quantity_in_repl_uom,
REPL_UOM_code,
Quantity,
Uom_code,
Expected_ship_date,
Repl_To_Subinventory_code,
filter_item_flag,
repl_status,
repl_type,
RELEASED_STATUS)
VALUES
(WMS_REPL_DEMAND_GTMP_S.NEXTVAL,
p_repl_level,
l_item_id_tb(k),
p_org_id,
l_header_id_tb(k),
l_line_id_tb(k),
l_delivery_detail_id_tb(k),
l_demand_type_id_tb(k),
l_quantity_in_repl_uom_tb(k),
l_repl_uom_code_tb(k),
l_requested_quantity_tb(k),
l_requested_quantity_uom_tb(k),
l_expected_ship_date_tb(k),
l_repl_to_sub_code_tb(k),
NULL,
l_repl_status_tb(k),
2, -- for dynamic replenishment
l_released_status_tb(k));
SELECT COUNT(1) INTO l_temp_cnt FROM wms_repl_demand_gtmp;
l_item_id_tb.DELETE;
l_total_demand_qty_tb.DELETE;
l_date_required_tb.DELETE;
l_repl_to_sub_code_tb.DELETE;
l_repl_uom_code_tb.DELETE;
x_consol_item_repl_tbl.DELETE;
x_consol_item_repl_tbl.DELETE;
select
mtrl.organization_id,
mtrl.INVENTORY_ITEM_ID,
mtrl.FROM_SUBINVENTORY_CODE,
mtrl.TO_SUBINVENTORY_CODE,
mtrl.quantity mol_qty,
Nvl(mtrl.quantity_detailed,0) mol_detailed_qty,
Nvl(mtrl.quantity_delivered,0) mol_delivered_qty,
mtrl.uom_code,
mtrl.header_id,
mtrl.line_id
FROM mtl_txn_request_lines mtrl,
Mtl_txn_request_headers mtrh
WHERE mtrl.header_id = mtrh.header_id
And mtrl.organization_id = mtrh.organization_id
and mtrl.organization_id in (select organization_id from mtl_parameters where wms_enabled_flag = 'Y')
And MTRH.move_order_type = 2
and mtrl.line_status in (3,7) -- only approved and pre-approved
and (mtrl.quantity - (Nvl(mtrl.quantity_detailed,0) + Nvl(mtrl.quantity_delivered,0))) > 0
and mtrh.transaction_type_id = 64
and mtrl.transaction_type_id = 64
and mtrl.transaction_source_type_id = 4
ORDER BY mtrl.organization_id, mtrl.TO_SUBINVENTORY_CODE, mtrl.line_id, mtrl.INVENTORY_ITEM_ID;
SELECT INVENTORY_LOCATION_ID
FROM MTL_ITEM_LOCATIONS
WHERE SUBINVENTORY_CODE = P_SUB_CODE
AND ORGANIZATION_ID = p_organization_id
ORDER BY PICKING_ORDER;
SELECT MSI.PICK_UOM_CODE, MISI.FIXED_LOT_MULTIPLE
from MTL_ITEM_SUB_INVENTORIES MISI,
MTL_SECONDARY_INVENTORIES MSI
where MISI.organization_id = p_org_id
AND MISI.SOURCE_SUBINVENTORY = MSI.SECONDARY_INVENTORY_NAME
AND MISI.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND nvl(MISI.SOURCE_SUBINVENTORY, '@@@') = NVL(p_src_sub , nvl(MISI.SOURCE_SUBINVENTORY,'@@@'))
AND MISI.SECONDARY_INVENTORY = p_dest_sub
AND MISI.INVENTORY_ITEM_ID = p_Item_id
and MISI.source_type = 3 --(for Subinventory)
AND MISI.source_organization_id = p_org_id;
SELECT muom.uom_code
FROM mtl_units_of_measure_tl muom,mtl_units_of_measure_tl muom2
WHERE muom2.uom_code = p_txn_uom_code
AND muom2.language = userenv('LANG')
AND muom.uom_class = muom2.uom_class
AND muom.language = userenv('LANG')
AND muom.base_uom_flag = 'Y';
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mo_line_id;
-- returning the updated available capacity.
IF (l_debug = 1) THEN
print_debug('Capacity of current locator: '||l_available_capacity);
--update the MO line quantity_detailed field or close the MO
BEGIN
SELECT NVL(SUM(primary_quantity), 0)
,NVL(sum(transaction_quantity),0)
,COUNT(*)
INTO l_prim_quantity_detailed
,l_quantity_detailed_conv
,l_num_detail_recs
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_open_repl_mo.line_id;
-- update the quantity detailed correctly
UPDATE mtl_txn_request_lines mtrl
SET mtrl.quantity_detailed = l_quantity_detailed
where line_id = l_open_repl_mo.line_id;
print_debug('Updated the detailed qty on the MO line');
UPDATE mtl_txn_request_lines mtrl
SET mtrl.quantity_detailed = l_quantity_detailed
where line_id = l_open_repl_mo.line_id;
PROCEDURE UPDATE_DELIVERY_DETAIL (
p_delivery_detail_id IN NUMBER,
P_PRIMARY_QUANTITY IN NUMBER,
P_SPLIT_DELIVERY_DETAIL_ID IN NUMBER DEFAULT NULL,
p_split_source_line_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
--UPDATE THE PRIMARY QTY FOR THE DEMAND
UPDATE WMS_REPLENISHMENT_DETAILS
SET PRIMARY_QUANTITY = P_PRIMARY_QUANTITY
WHERE DEMAND_LINE_DETAIL_ID = p_delivery_detail_id
AND primary_quantity >= P_PRIMARY_QUANTITY;
DELETE FROM WMS_REPLENISHMENT_DETAILS
WHERE DEMAND_LINE_DETAIL_ID = P_DELIVERY_DETAIL_ID;
SELECT
source_header_id, Source_line_id,
Source_line_detail_id,Source_type_id, demand_header_id,
demand_line_id,demand_type_id, Primary_UOM, Primary_Quantity,
demand_sort_order, inventory_item_id, organization_id
, Nvl(repl_level,1), repl_type
INTO l_source_header_id,l_Source_line_id,
l_Source_line_detail_id, l_Source_type_id,
l_demand_header_id,l_demand_line_id, l_demand_type_id,l_Primary_UOM, l_orig_pri_qty, l_demand_sort_order,
l_item_id, l_org_id , l_repl_level, l_repl_type
FROM WMS_REPLENISHMENT_DETAILS
WHERE DEMAND_LINE_DETAIL_ID = p_delivery_detail_id;
-- Update old delivery with decreased qty
UPDATE WMS_REPLENISHMENT_DETAILS
SET PRIMARY_QUANTITY = P_PRIMARY_QUANTITY
WHERE DEMAND_LINE_DETAIL_ID = p_delivery_detail_id;
-- Insert a new record in WRD with remaining qty
-- Note: Priority of the Split demand Order remain same as the
-- original one
INSERT INTO WMS_REPLENISHMENT_DETAILS
(Replenishment_id,
Organization_Id,
source_header_id,
Source_line_id,
Source_line_detail_id,
Source_type_id,
demand_header_id,
demand_line_id,
demand_line_detail_id,
demand_type_id,
Inventory_item_id,
Primary_UOM,
Primary_Quantity,
demand_sort_order,
repl_type,
repl_level,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)VALUES (
WMS_REPLENISHMENT_DETAILS_S.NEXTVAL,
l_org_id,
l_source_header_id,
l_source_line_id,
l_Source_line_detail_id,
l_Source_type_id,
l_demand_header_id,
Nvl(p_split_source_line_id,l_demand_line_id),
p_split_delivery_detail_id,
l_demand_type_id,
l_item_id,
l_Primary_UOM,
(l_orig_pri_qty - p_primary_quantity) ,
l_demand_sort_order,
L_repl_type,
l_repl_level,
Sysdate,
Sysdate,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.user_id);
print_debug('Exception in update_delivery_detail: ' || sqlcode || ', ' || sqlerrm);
END update_delivery_detail;