[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_num_rows_inserted NUMBER;
l_num_rows_inserted := 0;
INSERT INTO WMS_ELS_EXP_RESOURCE
(els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
document_type,
source_subinventory,
transaction_uom ,
inventory_item_id ,
quantity,
source_header_id,
source_line_id,
group_id,
work_scheduled_date,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select
WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
to_organization_id,
1,--Inbound
1,--Recieve
1,--Reciept
supply_type_code,
from_subinventory,
mum.UOM_CODE,
item_id,
quantity,
decode(supply_type_code,
'PO', po_header_id,
'REQ', req_header_id,
'SHIPMENT',shipment_header_id
),
decode(supply_type_code,
'PO', po_line_id,
'REQ',req_line_id,
'SHIPMENT',shipment_line_id
),
1, --manual and user directed
receipt_date,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from mtl_supply ms,
wms_els_parameters wep,
mtl_units_of_measure_vl mum
where to_organization_id = p_org_id
and wep.organization_id = to_organization_id
and supply_type_code IN( 'PO','REQ','SHIPMENT')
and mum.description = ms.unit_of_measure
and receipt_date < ( SYSDATE + decode ( wep.data_period_unit ,
1 ,
wep.data_period_value /24,
2 , wep.data_period_value,
3, (ADD_MONTHS (SYSDATE,
wep.data_period_value ) - SYSDATE)
)
);
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for inbound(PO,REQ,SHIPMENTS) '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
insert into WMS_ELS_EXP_RESOURCE
(
els_exp_resource_id,
organization_id,
activity_id,
activity_detail_id,
operation_id,
document_type,
source_subinventory,
transaction_uom,
inventory_item_id,
quantity,
source_header_id,
source_line_id,
group_id,
work_scheduled_date,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_Date
)
select wms_els_exp_resource_s.nextval,
ship_from_org_id,
1, -- Inbound
1, -- Recieving
1, -- Reciept
'RMA',
subinventory,
shipping_quantity_uom,
inventory_item_id,
shipping_quantity - shipped_quantity - cancelled_quantity,
header_id,
line_id,
1, -- Manual and user directed
promise_date,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from oe_order_lines_all,
wms_els_parameters wep
where line_category_code like 'RETURN'
and booked_flag ='Y'
and cancelled_flag='N'
and open_flag='Y'
and flow_status_code not IN('CLOSED' , 'CANCELLED')
AND flow_status_code = 'AWAITING_RETURN'
and ship_from_org_id = p_org_id
and wep.organization_id = ship_from_org_id
and promise_date < ( SYSDATE + decode ( wep.data_period_unit ,
1 ,
wep.data_period_value /24,
2 ,wep.data_period_value,
3,(ADD_MONTHS (SYSDATE, wep.data_period_value
) - SYSDATE)
)
);
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for inbound(RMA) '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
INSERT INTO WMS_ELS_EXP_RESOURCE
(els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
destination_subinventory,
destination_locator_id,
source_header_id,
source_line_id,
group_id,
operation_plan_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select
WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
organization_id,
1,--Inbound
2,--Putaway
3,--Drop
subinventory_code,
locator_id,
transaction_header_id,
transaction_temp_id,
1, --manual and user directed
operation_plan_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_material_transactions_temp
where organization_id = p_org_id
and transaction_type_id = 18
and transaction_action_id =27
and transaction_source_type_id =1
AND wms_task_type IN (2,8)
and move_order_line_id IS NULL;
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for inbound putaway PO '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
INSERT INTO WMS_ELS_EXP_RESOURCE
(els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
destination_subinventory,
destination_locator_id,
source_header_id,
source_line_id,
group_id,
operation_plan_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select
WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
organization_id,
1,--Inbound
2,--Putaway
3,--Drop
subinventory_code,
locator_id,
transaction_header_id,
transaction_temp_id,
1, --manual and user directed
operation_plan_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_material_transactions_temp
where organization_id = p_org_id
and transaction_type_id = 15
and transaction_action_id =27
and transaction_source_type_id =12
AND wms_task_type IN (2,8)
and move_order_line_id IS NULL;
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for inbound putaway RMA '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
INSERT INTO WMS_ELS_EXP_RESOURCE
(els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
destination_subinventory,
destination_locator_id,
source_header_id,
source_line_id,
group_id,
operation_plan_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select
WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
organization_id,
1,--Inbound
2,--Putaway
3,--Drop
subinventory_code,
locator_id,
transaction_header_id,
transaction_temp_id,
1, --manual and user directed
operation_plan_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_material_transactions_temp
where organization_id = p_org_id
and transaction_type_id = 12
and transaction_action_id =12
and transaction_source_type_id =13
AND wms_task_type IN (2,8)
and move_order_line_id IS NULL;
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for inbound putaway Intransit Shipment '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
INSERT INTO WMS_ELS_EXP_RESOURCE
(els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
destination_subinventory,
destination_locator_id,
source_header_id,
source_line_id,
group_id,
operation_plan_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select
WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
organization_id,
1,--Inbound
2,--Putaway
3,--Drop
subinventory_code,
locator_id,
transaction_header_id,
transaction_temp_id,
1, --manual and user directed
operation_plan_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_material_transactions_temp
where organization_id = p_org_id
and transaction_type_id = 61
and transaction_action_id =12
and transaction_source_type_id =7
AND wms_task_type IN (2,8)
and move_order_line_id IS NULL;
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for inbound putaway Intransit Shipment '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
INSERT INTO WMS_ELS_EXP_RESOURCE
(els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
destination_subinventory,
destination_locator_id,
source_header_id,
source_line_id,
group_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select
WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
organization_id,
2,-- Manufacturing
2,-- putaway
3,--DROP
subinventory_code,
locator_id,
transaction_header_id,
transaction_temp_id,
1, --manual and user directed
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from mtl_material_transactions_temp
where organization_id = p_org_id
and transaction_type_id = 44
and transaction_action_id =31
and transaction_source_type_id =5
and wms_task_type =2;
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for Manufacturing putaway DROP'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
insert into WMS_ELS_EXP_RESOURCE
( els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
document_type,
source_subinventory,
source_locator_id,
inventory_item_id,
source_header_id,
source_line_id,
group_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select wms_els_exp_resource_s.nextval,
mcce.organization_id,
4,-- Warehousing
5,-- Counting
4,--Count
NULL,-- not inbound so document type is NULL
mcce.Subinventory,
mcce.locator_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
mcce.cycle_count_entry_id,
1, --Individual and System Directed
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_cycle_Count_entries mcce
WHERE
mcce.organization_id = p_org_id
and mcce.entry_status_code in (1,3); -- it is uncounted or for recounting.
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for Cycle Counting'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
insert into WMS_ELS_EXP_RESOURCE
( els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
document_type,
source_subinventory,
source_locator_id,
transaction_uom,
quantity,
inventory_item_id,
source_header_id,
source_line_id,
group_id,
operation_plan_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select wms_els_exp_resource_s.nextval,
mmtt.organization_id,
(CASE when (
( Transaction_Type_Id = 52
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 2
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =53
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 8
and Wms_Task_Type =1
)
)
THEN 3
when (
( Transaction_Type_Id =64
and Transaction_Action_Id =2
AND Transaction_Source_Type_Id = 4
and Wms_Task_Type =4
)
OR
( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =5
)
OR
( Transaction_Type_Id =63
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =6
)
)
THEN 4 -- Warehousing
when (
( Transaction_Type_Id =51 -- Pull Type
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 13
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =35 -- Push Type
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 5
and Wms_Task_Type =1
)
)
THEN 2 -- Manufacturing*/
end
) activity_id,
(CASE when (
( Transaction_Type_Id =52
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 2
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =53
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 8
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =51 -- Pull Type
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 13
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =35 -- Push Type
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 5
and Wms_Task_Type =1
)
)
THEN 3-- Picking
when ( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =4
)
THEN 8 -- Replenishment
when ( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =5
)
THEN 7 -- Move order transfer
when ( Transaction_Type_Id =63
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =6
)
THEN 6 -- Move Order Issue
end
) actvity_detail_id,
(CASE WHEN ( Transaction_Type_Id =63
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =6
)
THEN 5 -- Issue
ELSE 2 --Load
END
)operation_id,
NULL,-- not inbound so document type is NULL
mmtt.subinventory_code,
mmtt.locator_id,
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.transaction_uom
end
),-- so if LPN is populated we donot need item level information
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.transaction_quantity
end
),-- so if LPN is populated we donot need item level information
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.inventory_item_id
end
),-- so if LPN is populated we donot need item level information
mmtt.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
(CASE WHEN mmtt.demand_source_line is NOT NULL THEN to_number(mmtt.demand_source_line)
else mmtt.parent_line_id
end), /*mmtt.demand_source_line, Modified for bug # 5478983(For Bulk Tasks,
demand_source_line will be NULL. hence, parent_line_id will be populated for BULK tasks) */
3, --Individual and system directed
operation_plan_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_material_transactions_temp mmtt
where
mmtt.organization_id = p_org_id
and
(
( mmtt.transaction_Type_Id = 52
and mmtt.Transaction_Action_Id =28
and mmtt.Transaction_Source_Type_Id = 2
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 53
and mmtt.Transaction_Action_Id =28
and mmtt.Transaction_Source_Type_Id = 8
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 64
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =4
)
OR
( mmtt.transaction_Type_Id = 64
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =5
)
OR
( mmtt.transaction_Type_Id = 63
and mmtt.Transaction_Action_Id =1
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =6
)
OR
( mmtt.transaction_Type_Id = 51 -- Pull Type
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 13
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 35 -- Push Type
and mmtt.Transaction_Action_Id =1
and mmtt.Transaction_Source_Type_Id = 5
and mmtt.Wms_Task_Type =1
)
)
and mmtt.wms_task_status IN(1,8)
and mmtt.transaction_temp_id = nvl(mmtt.parent_line_id, mmtt.transaction_temp_id); -- Added for bug #5478983
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for LOAD TASKS Outbound/Relenishment tasks(pending,unreleased)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
insert into WMS_ELS_EXP_RESOURCE
( els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
document_type,
source_subinventory,
source_locator_id,
transaction_uom,
quantity,
inventory_item_id,
source_header_id,
source_line_id,
group_id,
operation_plan_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select wms_els_exp_resource_s.nextval,
mmtt.organization_id,
(CASE when (
( Transaction_Type_Id = 52
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 2
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =53
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 8
and Wms_Task_Type =1
)
)
THEN 3
when (
( Transaction_Type_Id =64
and Transaction_Action_Id =2
AND Transaction_Source_Type_Id = 4
and Wms_Task_Type =4
)
OR
( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =5
)
OR
( Transaction_Type_Id =63
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =6
)
)
THEN 4 -- Warehousing
when (
( Transaction_Type_Id =51 -- Pull type
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 13
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =35 -- Push type
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 5
and Wms_Task_Type =1
)
)
THEN 2 -- Manufacturing*/
end
) activity_id,
(CASE when (
( Transaction_Type_Id =52
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 2
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =53
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 8
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =51 -- Pull Type
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 13
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =35 -- Push type
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 5
and Wms_Task_Type =1
)
)
THEN 3-- Picking
when ( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =4
)
THEN 8 -- Replenishment
when ( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =5
)
THEN 7 -- Move order transfer
when ( Transaction_Type_Id =63
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =6
)
THEN 6 -- Move Order Issue
end
) actvity_detail_id,
(CASE WHEN ( Transaction_Type_Id =63
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =6
)
THEN 5 -- Issue
ELSE 2 --Load
END
)operation_id,
NULL,-- not inbound so document type is NULL
mmtt.subinventory_code,
mmtt.locator_id,
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.transaction_uom
end
),-- so if LPN is populated we donot need item level information
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.transaction_quantity
end
),-- so if LPN is populated we donot need item level information
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.inventory_item_id
end
),-- so if LPN is populated we donot need item level information
mmtt.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
(CASE WHEN mmtt.demand_source_line is NOT NULL THEN to_number(mmtt.demand_source_line)
else mmtt.parent_line_id
end), /*mmtt.demand_source_line, Modified for bug # 5478983(For Bulk Tasks,
demand_source_line will be NULL. hence, parent_line_id will be populated for BULK tasks) */
3, --Individual and system directed
mmtt.operation_plan_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt
where
mmtt.organization_id = p_org_id
and
(
( mmtt.transaction_Type_Id = 52
and mmtt.Transaction_Action_Id =28
and mmtt.Transaction_Source_Type_Id = 2
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 53
and mmtt.Transaction_Action_Id =28
and mmtt.Transaction_Source_Type_Id = 8
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 64
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =4
)
OR
( mmtt.transaction_Type_Id = 64
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =5
)
OR
( mmtt.transaction_Type_Id = 63
and mmtt.Transaction_Action_Id =1
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =6
)
OR
( mmtt.transaction_Type_Id = 51 --Pull Type
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 13
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 35 --Push Type
and mmtt.Transaction_Action_Id =1
and mmtt.Transaction_Source_Type_Id = 5
and mmtt.Wms_Task_Type =1
)
)
and (wdt.status IN (2,3) and wdt.transaction_temp_id = mmtt.transaction_temp_id)
and mmtt.transaction_temp_id = nvl(mmtt.parent_line_id, mmtt.transaction_temp_id); -- Added for bug #5478983
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for LOAD TASKS Outbound/Relenishment tasks(queued,dispatched)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
insert into WMS_ELS_EXP_RESOURCE
( els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
document_type,
destination_subinventory,
destination_locator_id,
transaction_uom,
quantity,
inventory_item_id,
source_header_id,
source_line_id,
group_id,
operation_plan_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select wms_els_exp_resource_s.nextval,
mmtt.organization_id,
(CASE when (
( Transaction_Type_Id = 52
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 2
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =53
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 8
and Wms_Task_Type =1
)
)
THEN 3
when (
( Transaction_Type_Id =64
and Transaction_Action_Id =2
AND Transaction_Source_Type_Id = 4
and Wms_Task_Type =4
)
OR
( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =5
)
)
THEN 4 -- Warehousing
when (
( Transaction_Type_Id =51 -- Pull Type
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 13
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =35 -- Push Type
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 5
and Wms_Task_Type =1
)
)
THEN 2 -- Manufacturing*/
end
) activity_id,
(CASE when (
( Transaction_Type_Id =52
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 2
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =53
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 8
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =51 -- Pull Type
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 13
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =35 -- Push Type
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 5
and Wms_Task_Type =1
)
)
THEN 3-- Picking
when ( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =4
)
THEN 8 -- Replenishment
when ( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =5
)
THEN 7 -- Move order transfer
end
) actvity_detail_id,
3,--Drop(Operation_ID)
NULL,-- not inbound so document type is NULL
mmtt.transfer_subinventory,
mmtt.transfer_to_location,
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.transaction_uom
end
),-- so if LPN is populated we donot need item level information
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.transaction_quantity
end
),-- so if LPN is populated we donot need item level information
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.inventory_item_id
end
),-- so if LPN is populated we donot need item level information
mmtt.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
mmtt.demand_source_line,
3, --Individual and system directed
mmtt.operation_plan_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_material_transactions_temp mmtt
where
mmtt.organization_id = p_org_id
and
(
( mmtt.transaction_Type_Id = 52
and mmtt.Transaction_Action_Id =28
and mmtt.Transaction_Source_Type_Id = 2
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 53
and mmtt.Transaction_Action_Id =28
and mmtt.Transaction_Source_Type_Id = 8
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 64
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =4
)
OR
( mmtt.transaction_Type_Id = 64
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =5
)
OR
( mmtt.transaction_Type_Id = 51 -- Pull Type
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 13
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 35 --Push Type
and mmtt.Transaction_Action_Id =1
and mmtt.Transaction_Source_Type_Id = 5
and mmtt.Wms_Task_Type =1
)
)
and mmtt.wms_task_status IN(1,8)
and mmtt.transaction_temp_id <> nvl(mmtt.parent_line_id, -999); -- Added for bug # 5478983
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for DROP TASKS Outbound/Relenishment tasks(pending,unreleased)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
insert into WMS_ELS_EXP_RESOURCE
( els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
document_type,
destination_subinventory,
destination_locator_id,
transaction_uom,
quantity,
inventory_item_id,
source_header_id,
source_line_id,
group_id,
operation_plan_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select wms_els_exp_resource_s.nextval,
mmtt.organization_id,
(CASE when (
( Transaction_Type_Id = 52
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 2
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =53
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 8
and Wms_Task_Type =1
)
)
THEN 3
when (
( Transaction_Type_Id =64
and Transaction_Action_Id =2
AND Transaction_Source_Type_Id = 4
and Wms_Task_Type =4
)
OR
( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =5
)
)
THEN 4 -- Warehousing
when (
( Transaction_Type_Id =51 -- Pull Type
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 13
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =35 -- Push Type
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 5
and Wms_Task_Type =1
)
)
THEN 2 -- Manufacturing*/
end
) activity_id,
(CASE when (
( Transaction_Type_Id =52
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 2
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =53
and Transaction_Action_Id =28
and Transaction_Source_Type_Id = 8
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =51 -- Pull Type
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 13
and Wms_Task_Type =1
)
OR
( Transaction_Type_Id =35 -- Push type
and Transaction_Action_Id =1
and Transaction_Source_Type_Id = 5
and Wms_Task_Type =1
)
)
THEN 3-- Picking
when ( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =4
)
THEN 8 -- Replenishment
when ( Transaction_Type_Id =64
and Transaction_Action_Id =2
and Transaction_Source_Type_Id = 4
and Wms_Task_Type =5
)
THEN 7 -- Move order transfer
end
) actvity_detail_id,
3,--Drop(Operation_ID)
NULL,-- not inbound so document type is NULL
mmtt.transfer_subinventory,
mmtt.transfer_to_location,
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.transaction_uom
end
),-- so if LPN is populated we donot need item level information
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.transaction_quantity
end
),-- so if LPN is populated we donot need item level information
(CASE when allocated_lpn_id IS NOT NULL THEN NULL
else mmtt.inventory_item_id
end
),-- so if LPN is populated we donot need item level information
mmtt.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
mmtt.demand_source_line,
3, --Individual and system directed
mmtt.operation_plan_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt
where
mmtt.organization_id = p_org_id
and
(
( mmtt.transaction_Type_Id = 52
and mmtt.Transaction_Action_Id =28
and mmtt.Transaction_Source_Type_Id = 2
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 53
and mmtt.Transaction_Action_Id =28
and mmtt.Transaction_Source_Type_Id = 8
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 64
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =4
)
OR
( mmtt.transaction_Type_Id = 64
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 4
and mmtt.Wms_Task_Type =5
)
OR
( mmtt.transaction_Type_Id = 51 -- Pull Type
and mmtt.Transaction_Action_Id =2
and mmtt.Transaction_Source_Type_Id = 13
and mmtt.Wms_Task_Type =1
)
OR
( mmtt.transaction_Type_Id = 35 -- Push Type
and mmtt.Transaction_Action_Id =1
and mmtt.Transaction_Source_Type_Id = 5
and mmtt.Wms_Task_Type =1
)
)
and (wdt.status IN (2,3,4) and wdt.transaction_temp_id = mmtt.transaction_temp_id)
and mmtt.parent_line_id is NULL; -- Added for bug # 5478983
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for DROP TASKS Outbound/Relenishment tasks(queued,dispatched)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
* The SQL will insert the information related to the Bulk drop tasks.
*
*/
IF g_debug=1 THEN
debug('Before populating work for BULK DROP TASKS Outbound/Relenishment tasks(queued,dispatched) ','POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
insert into WMS_ELS_EXP_RESOURCE
( els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
document_type,
destination_subinventory,
destination_locator_id,
transaction_uom,
quantity,
inventory_item_id,
source_header_id,
source_line_id,
group_id,
operation_plan_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select wms_els_exp_resource_s.nextval,
mmtt2.organization_id,
(CASE when (
( mmtt2.Transaction_Type_Id = 52
and mmtt2.Transaction_Action_Id =28
and mmtt2.Transaction_Source_Type_Id = 2
and mmtt2.Wms_Task_Type =1
)
OR
( mmtt2.Transaction_Type_Id =53
and mmtt2.Transaction_Action_Id =28
and mmtt2.Transaction_Source_Type_Id = 8
and mmtt2.Wms_Task_Type =1
)
)
THEN 3
when (
( mmtt2.Transaction_Type_Id =64
and mmtt2.Transaction_Action_Id =2
AND mmtt2.Transaction_Source_Type_Id = 4
and mmtt2.Wms_Task_Type =4
)
OR
( mmtt2.Transaction_Type_Id =64
and mmtt2.Transaction_Action_Id =2
and mmtt2.Transaction_Source_Type_Id = 4
and mmtt2.Wms_Task_Type =5
)
)
THEN 4 -- Warehousing
when (
( mmtt2.Transaction_Type_Id =51 -- Pull Type
and mmtt2.Transaction_Action_Id =2
and mmtt2.Transaction_Source_Type_Id = 13
and mmtt2.Wms_Task_Type =1
)
OR
( mmtt2.Transaction_Type_Id =35 -- Push Type
and mmtt2.Transaction_Action_Id =1
and mmtt2.Transaction_Source_Type_Id = 5
and mmtt2.Wms_Task_Type =1
)
)
THEN 2 -- Manufacturing*/
end
) activity_id,
(CASE when (
( mmtt2.Transaction_Type_Id =52
and mmtt2.Transaction_Action_Id =28
and mmtt2.Transaction_Source_Type_Id = 2
and mmtt2.Wms_Task_Type =1
)
OR
( mmtt2.Transaction_Type_Id =53
and mmtt2.Transaction_Action_Id =28
and mmtt2.Transaction_Source_Type_Id = 8
and mmtt2.Wms_Task_Type =1
)
OR
( mmtt2.Transaction_Type_Id =51 -- Pull Type
and mmtt2.Transaction_Action_Id =2
and mmtt2.Transaction_Source_Type_Id = 13
and mmtt2.Wms_Task_Type =1
)
OR
( mmtt2.Transaction_Type_Id =35 -- Push type
and mmtt2.Transaction_Action_Id =1
and mmtt2.Transaction_Source_Type_Id = 5
and mmtt2.Wms_Task_Type =1
)
)
THEN 3-- Picking
when ( mmtt2.Transaction_Type_Id =64
and mmtt2.Transaction_Action_Id =2
and mmtt2.Transaction_Source_Type_Id = 4
and mmtt2.Wms_Task_Type =4
)
THEN 8 -- Replenishment
when ( mmtt2.Transaction_Type_Id =64
and mmtt2.Transaction_Action_Id =2
and mmtt2.Transaction_Source_Type_Id = 4
and mmtt2.Wms_Task_Type =5
)
THEN 7 -- Move order transfer
end
) actvity_detail_id,
3,--Drop(Operation_ID)
NULL,-- not inbound so document type is NULL
mmtt2.transfer_subinventory,
mmtt2.transfer_to_location,
(CASE when mmtt2.allocated_lpn_id IS NOT NULL THEN NULL
else mmtt2.transaction_uom
end
),-- so if LPN is populated we donot need item level information
(CASE when mmtt2.allocated_lpn_id IS NOT NULL THEN NULL
else mmtt2.transaction_quantity
end
),-- so if LPN is populated we donot need item level information
(CASE when mmtt2.allocated_lpn_id IS NOT NULL THEN NULL
else mmtt2.inventory_item_id
end
),-- so if LPN is populated we donot need item level information
mmtt2.transaction_temp_id, -- mmtt.demand_source_header_id, Modified for bug # 5169490
mmtt2.demand_source_line,
3, --Individual and system directed
mmtt2.operation_plan_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
mtl_material_transactions_temp mmtt1,
mtl_material_transactions_temp mmtt2,
wms_dispatched_tasks wdt
where
mmtt1.organization_id = p_org_id
and mmtt2.organization_id = p_org_id
and
(
( mmtt2.transaction_Type_Id = 52
and mmtt2.Transaction_Action_Id =28
and mmtt2.Transaction_Source_Type_Id = 2
and mmtt2.Wms_Task_Type =1
)
OR
( mmtt2.transaction_Type_Id = 53
and mmtt2.Transaction_Action_Id =28
and mmtt2.Transaction_Source_Type_Id = 8
and mmtt2.Wms_Task_Type =1
)
OR
( mmtt2.transaction_Type_Id = 64
and mmtt2.Transaction_Action_Id =2
and mmtt2.Transaction_Source_Type_Id = 4
and mmtt2.Wms_Task_Type =4
)
OR
( mmtt2.transaction_Type_Id = 64
and mmtt2.Transaction_Action_Id =2
and mmtt2.Transaction_Source_Type_Id = 4
and mmtt2.Wms_Task_Type =5
)
OR
( mmtt2.transaction_Type_Id = 51 -- Pull Type
and mmtt2.Transaction_Action_Id =2
and mmtt2.Transaction_Source_Type_Id = 13
and mmtt2.Wms_Task_Type =1
)
OR
( mmtt2.transaction_Type_Id = 35 -- Push Type
and mmtt2.Transaction_Action_Id =1
and mmtt2.Transaction_Source_Type_Id = 5
and mmtt2.Wms_Task_Type =1
)
)
and (wdt.status IN (2,3,4) and wdt.transaction_temp_id = mmtt1.transaction_temp_id)
and mmtt1.parent_line_id = mmtt2.parent_line_id
and mmtt1.parent_line_id <> mmtt2.transaction_temp_id
and mmtt1.parent_line_id is NOT NULL
and mmtt2.parent_line_id is NOT NULL;
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for BULK DROP TASKS Outbound/Relenishment tasks(queued,dispatched)'|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
INSERT INTO WMS_ELS_EXP_RESOURCE
(els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
destination_subinventory,
destination_locator_id,
transaction_uom,
quantity,
inventory_item_id,
source_header_id,
source_line_id,
group_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
SELECT WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
organization_id,
activity_id,
activity_detail_id,
operation_id,
destination_subinventory,
destination_locator_id,
transaction_uom,
transaction_quantity,
inventory_item_id,
source_header_id,
source_line_id,
group_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
FROM
(
SELECT DISTINCT
mmtt.transfer_organization organization_id,
4 activity_id,--Warehousing
9 activity_detail_id,--Inventory Move
3 operation_id,--Drop
mmtt.transfer_subinventory destination_subinventory,
mmtt.transfer_to_location destination_locator_id,
decode(num_lines,1,mmtt.transaction_uom,NULL) transaction_uom,
decode (num_lines,1,mmtt.transaction_quantity,NULL) transaction_quantity,
decode (num_lines,1,mmtt.inventory_item_id,NULL) inventory_item_id,
decode (num_lines,1,mmtt.transaction_header_id,0,NULL,mmtt.transaction_header_id) source_header_id,
decode (num_lines,1,mmtt.transaction_temp_id,0,NULL,mmtt.lpn_id) source_line_id,
1 group_id --manual and user directed
FROM
(SELECT lpn_id,transfer_to_location,count(*) num_lines
FROM
mtl_material_transactions_temp
WHERE organization_id = p_org_id
AND transaction_type_id = 64
AND transaction_action_id =2
AND transaction_source_type_id =4
AND wms_task_type =2
GROUP BY lpn_id,transfer_to_location ) tab1, mtl_material_transactions_temp mmtt
WHERE mmtt.lpn_id = tab1.lpn_id
AND mmtt.transfer_to_location = tab1.transfer_to_location
);
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for Inventory Move DROP '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
l_num_rows_inserted := 0;
INSERT INTO WMS_ELS_EXP_RESOURCE
(els_exp_resource_id ,
organization_id,
activity_id,
activity_detail_id,
operation_id,
destination_subinventory,
destination_locator_id,
transaction_uom,
quantity,
inventory_item_id,
source_header_id,
source_line_id,
operation_plan_id,
group_id,
last_updated_by,
last_update_Date,
last_update_login,
created_by,
creation_Date
)
select WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
organization_id,
activity_id,
activity_detail_id,
operation_id,
subinventory_code,
locator_id,
transaction_uom,
transaction_quantity,
inventory_item_id,
source_header_id,
source_line_id,
operation_plan_id,
group_id,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE
from
(
select distinct
mmtt.organization_id organization_id,
3 activity_id,--Outbound
4 activity_detail_id,--Staging Move
3 operation_id,--Drop
mmtt.subinventory_code subinventory_code,
mmtt.locator_id locator_id,
decode(num_lines,1,mmtt.transaction_uom,NULL) transaction_uom,
decode (num_lines,1,transaction_quantity,NULL) transaction_quantity,
decode (num_lines,1,inventory_item_id,NULL) inventory_item_id,
decode (num_lines,1,transaction_header_id,NULL) source_header_id,
decode (num_lines,1,transaction_temp_id,NULL) source_line_id,
mmtt.operation_plan_id,
1 group_id --manual and user directed
from
(select content_lpn_id,locator_id,count(*) num_lines from
mtl_material_transactions_temp
where organization_id = p_org_id
and transaction_type_id = 2
and transaction_action_id =2
and transaction_source_type_id =13
AND wms_task_type =7
group by content_lpn_id,locator_id ) tab1, mtl_material_transactions_temp mmtt
where mmtt.content_lpn_id = tab1.content_lpn_id
and mmtt.locator_id = tab1.locator_id
);
l_num_rows_inserted := SQL%ROWCOUNT;
debug('The no of rows inserted for Staging Move DROP '|| l_num_rows_inserted,'POPULATE_EXPECTED_WORK');
SELECT els_data_id,
organization_id,
activity_id,
activity_detail_id,
operation_id,
source_zone_id,
source_subinventory,
destination_zone_id,
destination_subinventory,
labor_txn_source_id,
transaction_uom,
from_quantity,
to_quantity,
item_category_id,
operation_plan_id,
group_id,
task_type_id,
expected_travel_time,
expected_txn_time,
expected_idle_time,
travel_time_threshold,
num_trx_matched
FROM wms_els_individual_tasks_b
WHERE organization_id = l_org_id
AND history_flag IS NULL
AND Analysis_id IN (2,4)
ORDER BY group_id DESC,sequence_number ASC;
l_update_count NUMBER;
l_update_count := 0;
debug('Before inserting the data into Global temporary table','MATCH_RATE_EXP_RESOURCE');
INSERT INTO WMS_ELS_EXP_RESOURCE_GTEMP
(SELECT els_data_id
, source_header_id
, source_line_id
, activity_id
, activity_detail_id
, operation_id
FROM wms_els_exp_resource
WHERE organization_id = p_org_id
AND els_data_id IS NOT NULL);
debug('After inserting the data in Global temporary table','MATCH_RATE_EXP_RESOURCE');
DELETE FROM WMS_ELS_EXP_RESOURCE WHERE organization_id = p_org_id;
UPDATE wms_els_individual_tasks_b weitb
SET num_trx_matched = num_trx_matched - (SELECT count(weerg.els_data_id)
FROM wms_els_exp_resource_gtemp weerg
, wms_els_exp_resource weer
WHERE weerg.source_header_id = weer.source_header_id
AND weerg.source_line_id = weer.source_line_id
AND weerg.activity_id = weer.activity_id
AND weerg.activity_detail_id = weer.activity_detail_id
AND weerg.operation_id = weer.operation_id
AND weerg.els_data_id = weitb.els_data_id
AND weer.organization_id = p_org_id)
WHERE weitb.organization_id = p_org_id
AND weitb.analysis_id IN (2, 4); -- Analysis_id should be Work outstanding (4) or both(2) */
|| ' IN (select inventory_location_id'
|| ' from WMS_ZONE_LOCATORS'
|| ' where zone_id= :source_zone_id AND organization_id = :org_id'
|| ' AND '
||' WMS_LMS_UTILS. ZONE_LABOR_MGMT_ENABLED(:org_id,:source_zone_id)=''Y'''
|| ')'
|| ')) ';
|| ' IN (select inventory_location_id '
|| ' from WMS_ZONE_LOCATORS '
|| ' where zone_id= :destination_zone_id AND organization_id = :org_id'
|| ' AND '
|| ' WMS_LMS_UTILS. ZONE_LABOR_MGMT_ENABLED(:org_id,:destination_zone_id)=''Y'''
|| ')'
|| ')) ';
|| ' IN (select inventory_item_id'
|| ' from MTL_ITEM_CATEGORIES'
|| ' where category_id= :item_category_id AND organization_id =:org_id'
|| ')'
|| ')) ';
l_sql :='UPDATE wms_els_exp_resource SET '
||' els_data_id = :els_data_id'
||' ,source_zone_id = :source_zone'
||' ,destination_zone_id = :destination_zone'
||' ,item_category_id = :item_category'
||' , unattributed_flag = NULL'
||' ,estimated_time_required = (:expected_travel_time + :expected_txn_time + NVL(:expected_idle_time,0))'
||' ,estimated_resource_required =(:expected_travel_time + :expected_txn_time + NVL(:expected_idle_time,0))'
|| ' /(:time_per_day*60*60*:utilization_rate/100)'
||' where els_data_id IS NULL and organization_id = :org_id ';
l_update_count := DBMS_SQL.EXECUTE(c);
debug('SQL executed Number of rows updated '|| l_update_count,'MATCH_RATE_EXP_RESOURCE');
l_total := l_update_count + NVL(l_els_data.num_trx_matched,0);
UPDATE wms_els_individual_tasks_b
SET
num_trx_matched = l_total
WHERE els_data_id = l_els_data.els_data_id;
l_update_count := NULL;
UPDATE wms_els_exp_resource SET unattributed_flag = 1
WHERE els_data_id IS NULL AND organization_id = p_org_id;
l_update_count := SQL%ROWCOUNT;
debug('Number of rows updated as non-standardized '|| l_update_count,'MATCH_RATE_EXP_RESOURCE');