[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
SELECT wdd1.organization_id,
wdd1.subinventory,
wdd1.locator_id,
wdd1.inventory_item_id,
wdd1.revision,
wdd1.requested_quantity_uom,
wdd2.lpn_id,
wdd1.shipped_quantity
INTO
l_org_id,
l_subinv,
l_locator_id,
l_item_id,
l_rev,
l_uom,
l_lpn_id,
l_qty
FROM wsh_delivery_details wdd1, wsh_delivery_assignments_v wda,
wsh_delivery_details wdd2
WHERE wdd1.DELIVERY_DETAIL_ID = p_task_trx_id
AND wdd1.delivery_detail_id = wda.parent_delivery_detail_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id;
insert INTO wms_device_requests (request_id,
task_id,
task_summary,
business_event_id,
organization_id,
subinventory_code,
locator_id,
inventory_item_id,
revision,
uom,
lpn_id,
transaction_quantity,
last_update_date,
last_updated_by) VALUES
(l_request_id,
p_task_trx_id,
'Y',
p_bus_event,
l_org_id,
l_subinv,
l_locator_id,
l_item_id,
l_rev,
l_uom,
l_lpn_id,
l_qty,
Sysdate,
FND_GLOBAL.USER_ID);
SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
SELECT organization_id,
subinventory_code,
locator_id,
transfer_organization,
transfer_subinventory,
transfer_to_location,
inventory_item_id,
revision,
transaction_uom,
Nvl(lpn_id,allocated_lpn_id) lpn_id,
transaction_quantity
INTO
l_org_id,
l_subinv,
l_locator_id,
l_xfr_org_id,
l_xfr_subinv,
l_xfr_locator_id,
l_item_id,
l_rev,
l_uom,
l_lpn_id,
l_qty
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_task_trx_id;
insert INTO wms_device_requests (request_id,
task_id,
task_summary,
business_event_id,
organization_id,
subinventory_code,
locator_id,
transfer_org_id,
transfer_sub_code,
transfer_loc_id,
inventory_item_id,
revision,
uom,
lpn_id,
transaction_quantity,
last_update_date,
last_updated_by) VALUES
(l_request_id,
p_task_trx_id,
'Y',
p_bus_event,
l_org_id,
l_subinv,
l_locator_id,
l_xfr_org_id,
l_xfr_subinv,
l_xfr_locator_id,
l_item_id,
l_rev,
l_uom,
l_lpn_id,
l_qty,
Sysdate,
FND_GLOBAL.USER_ID);
SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
insert INTO wms_device_requests (request_id,
task_id,
task_summary,
business_event_id,
organization_id,
subinventory_code,
locator_id,
transfer_org_id,
transfer_sub_code,
transfer_loc_id,
inventory_item_id,
revision,
uom,
lpn_id,
transaction_quantity,
last_update_date,
last_updated_by) VALUES
(l_request_id,
Nvl(p_task_trx_id,-9999),
'Y',
p_bus_event,
p_org_id,
l_subinv,
l_locator_id,
p_xfr_org_id,
l_xfr_subinv,
l_xfr_locator_id,
p_item_id,
p_rev,
p_uom,
p_lpn_id,
p_qty,
Sysdate,
FND_GLOBAL.USER_ID);
SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
insert INTO wms_device_requests (request_id,
task_id,
task_summary,
business_event_id,
organization_id,
subinventory_code,
locator_id,
transfer_org_id,
transfer_sub_code,
transfer_loc_id,
inventory_item_id,
revision,
uom,
lpn_id,
device_id,
transaction_quantity,
last_update_date,
last_updated_by) VALUES
(l_request_id,
Nvl(p_task_trx_id,-9999),
'Y',
p_bus_event,
p_org_id,
l_subinv,
l_locator_id,
p_xfr_org_id,
l_xfr_subinv,
l_xfr_locator_id,
p_item_id,
p_rev,
p_uom,
p_lpn_id,
p_device_id,
p_qty,
Sysdate,
FND_GLOBAL.USER_ID);
SELECT
mtlt.lot_number lot_num,
mtlt.transaction_quantity lot_qty,
msnt.fm_serial_number ser_num
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
WHERE
mmtt.transaction_temp_id = wdrrec.task_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mmtt.transaction_temp_id = msnt.transaction_temp_id(+)
AND ((mmtt.transaction_temp_id=msnt.transaction_temp_id
AND mtlt.serial_transaction_temp_id=msnt.transaction_temp_id)
OR 1=1);
INSERT INTO wms_device_requests (request_id,
task_id,
relation_id,
sequence_id,
task_summary,
task_type_id,
business_event_id,
organization_id,
subinventory_code,
locator_id,
transfer_org_id,
transfer_sub_code,
transfer_loc_id,
inventory_item_id,
revision,
uom,
lot_number,
lot_qty,
serial_number,
lpn_id,
transaction_quantity,
device_id,
status_code,
last_update_date,
last_updated_by,
last_update_login) VALUES
(wdrrec.request_id,
wdrrec.task_id,
wdrrec.relation_id,
wdrrec.sequence_id,
'N',
wdrrec.task_type_id,
wdrrec.business_event_id,
wdrrec.organization_id,
wdrrec.subinventory_code,
wdrrec.locator_id,
wdrrec.transfer_org_id,
wdrrec.transfer_sub_code,
wdrrec.transfer_loc_id,
wdrrec.inventory_item_id,
wdrrec.revision,
wdrrec.uom,
l_rec.lot_num,
l_rec.lot_qty,
l_rec.ser_num,
wdrrec.lpn_id,
l_qty,
wdrrec.device_id,
wdrrec.status_code,
wdrrec.last_update_date,
wdrrec.last_updated_by,
wdrrec.last_update_login);
FUNCTION select_Device(wdrrec WMS_DEVICE_REQUESTS%ROWTYPE,
p_autoenable VARCHAR2,
p_parent_request_id NUMBER
) return NUMBER is
dev_id number := 0;
trace('in select device, org, sub, user, autoenable, bus, parent req:');
SELECT device_id, task_id
INTO dev_id, par_task_id
FROM wms_device_requests_hist
WHERE request_id = p_parent_request_id
AND ROWNUM < 2;
trace('SelectDev:find device at destination for putaway OR repl-allocation');
seLECT DEVICE_ID INTO dev_ID FROM
( SELECT wbed.DEVICE_ID FROM
wms_bus_event_devices wbed,
wms_devices_b wd
WHERE
wd.device_id = wbed.device_id
AND WBED.organization_id = WD.organization_id
and wd.ENABLED_FLAG = 'Y'
and wbed.ENABLED_FLAG = 'Y'
AND decode(level_type,DEVICE_LEVEL_SUB,wbed.subinventory_code,level_value) =
decode(level_type,DEVICE_LEVEL_SUB,wdrrec.transfer_sub_code,DEVICE_LEVEL_ORG,
wdrrec.organization_id,DEVICE_LEVEL_LOCATOR,wdrrec.transfer_loc_id,
DEVICE_LEVEL_USER,FND_GLOBAL.USER_ID,level_value)
AND Nvl(wbed.organization_id,-1) = Nvl(wdrrec.organization_id,Nvl(wbed.organization_id,-1))
AND wbed.AUTO_ENABLED_FLAG = decode(p_autoenable,'Y','Y',wbed.AUTO_ENABLED_FLAG)
AND wbed.business_event_id = wdrrec.business_event_id
ORDER BY level_type desc)
where ROWNUM<2;
SELECT force_sign_on_flag
INTO l_force_sign_on_flag
FROM wms_devices_b
WHERE device_id = dev_id;
SELECT device_id
INTO l_dev_id
FROM wms_device_assignment_temp
WHERE device_id = dev_id
AND CREATED_BY = FND_GLOBAL.USER_ID;
trace('SelectDev:No device found at destination FOR putaway OR repl-allocation');
SELECT DEVICE_ID INTO dev_ID FROM
( SELECT wbed.DEVICE_ID FROM
wms_bus_event_devices wbed,
wms_devices_b wd
WHERE
wd.device_id = wbed.device_id
AND WBED.organization_id = WD.organization_id
and wd.ENABLED_FLAG = 'Y'
and wbed.ENABLED_FLAG = 'Y'
AND decode(level_type,DEVICE_LEVEL_SUB,wbed.subinventory_code,level_value) =
decode(level_type,DEVICE_LEVEL_SUB,wdrrec.subinventory_code,DEVICE_LEVEL_ORG,
wdrrec.organization_id,DEVICE_LEVEL_LOCATOR,wdrrec.locator_id,
DEVICE_LEVEL_USER,FND_GLOBAL.USER_ID,level_value)
AND Nvl(wbed.organization_id,-1) = Nvl(wdrrec.organization_id,Nvl(wbed.organization_id,-1))
AND wbed.AUTO_ENABLED_FLAG = decode(p_autoenable,'Y','Y',wbed.AUTO_ENABLED_FLAG)
AND wbed.business_event_id = wdrrec.business_event_id
ORDER BY level_type desc)
where ROWNUM<2;
SELECT force_sign_on_flag
INTO l_force_sign_on_flag
FROM wms_devices_b
WHERE device_id = dev_id;
SELECT device_id
INTO l_dev_id
FROM wms_device_assignment_temp
WHERE device_id = dev_id
AND CREATED_BY = FND_GLOBAL.USER_ID;
trace('SelectDev:No device found at any level for source');
select nvl(notification_flag, 'N')
into l_notification_flag
from wms_devices_b
where device_id = dev_id;
UPDATE wms_device_requests
SET device_id = dev_id,
task_id = nvl(par_task_id, task_id)
WHERE request_id = wdrrec.request_id
AND Nvl(task_type_id,0) = Nvl(wdrrec.task_type_id,Nvl(task_type_id,0))
AND organization_id = wdrrec.organization_id
AND business_event_id = wdrrec.business_event_id
AND Nvl(task_id,0) = Nvl(wdrrec.task_id,Nvl(task_id,0)); -- BUG4616997
select Nvl(lot_serial_capable,'N')
into l_lot_ser_ok
from WMS_DEVICES_B
where device_id = dev_id;
--code to insert lot/ser records into wdr table
wms_insert_lotSer_rec_WDR := 1;
UPDATE wms_dispatched_tasks
SET DEVICE_REQUEST_ID = NULL
WHERE TRANSACTION_TEMP_ID = wdrrec.task_id;
trace('SelectDev: Deviceid='||dev_id||',parent_task='||par_task_id);
SELECT wd.name DEVICE, wdr.request_id REQUESTID, ml1.meaning TASKTYPE,
ml2.meaning BUSINESSEVENT,
wdr.task_id TASKID, wdr.sequence_id SEQUENCEID,
wdr.relation_id RELATIONID,
mp1.organization_code ORG, wdr.subinventory_code SUB,
milk1.concatenated_segments LOC, mp2.organization_code TRANSFERORG,
wdr.transfer_sub_code TRANSFERSUB, milk2.concatenated_segments TRANSFERLOC,
wlpn.license_plate_number LPN, msik.concatenated_segments ITEM,
wdr.revision REVISION, wdr.transaction_quantity QUANTITY,
wdr.uom UOM, wdr.lot_number LOT, wdr.lot_qty LOTQTY,
wdr.serial_number serial,
wdr.status_msg STATUSMSG, wdr.last_update_date timestamp,
wdr.business_event_id bus_event_id
FROM wms_device_requests wdr, mfg_lookups ml1, mfg_lookups ml2,
wms_devices_vl wd, mtl_parameters mp1, mtl_item_locations_kfv milk1,
mtl_parameters mp2, mtl_item_locations_kfv milk2, wms_license_plate_numbers wlpn,
mtl_system_items_kfv msik
WHERE ml1.lookup_type(+)= 'WMS_TASK_TYPES' AND ml1.lookup_code(+) = wdr.task_type_id
AND ml2.lookup_type(+)='WMS_BUS_EVENT_TYPES'
AND ml2.lookup_code(+) = wdr.business_event_id
AND wd.device_id = wdr.device_id AND mp1.organization_id = wdr.organization_id
AND milk1.organization_id(+) = wdr.organization_id
AND milk1.subinventory_code(+) = wdr.subinventory_code
AND milk1.inventory_location_id(+) = wdr.locator_id
AND mp2.organization_id (+) = wdr.transfer_org_id
AND milk2.organization_id(+) = wdr.transfer_org_id
AND milk2.subinventory_code(+) = wdr.transfer_sub_code
AND milk2.inventory_location_id (+) = wdr.transfer_loc_id
AND wlpn.lpn_id(+) = wdr.lpn_id
AND msik.organization_id(+)= wdr.organization_id
AND msik.inventory_item_id(+) = wdr.inventory_item_id
AND wdr.device_id = p_dev_id
AND nvl(wdr.task_summary,'Y') = p_task_sum
ORDER BY wdr.task_id asc, wdr.sequence_id asc,wdr.task_type_id asc;
SELECT wd.name ||l_seperator|| wdr.request_id ||l_seperator|| ml1.meaning ||l_seperator||
ml2.meaning ||l_seperator|| wdr.task_id ||l_seperator|| wdr.sequence_id ||l_seperator
||wdr.relation_id||l_seperator ||
mp1.organization_code ||l_seperator|| wdr.subinventory_code ||l_seperator||
milk1.concatenated_segments ||l_seperator|| mp2.organization_code ||l_seperator||
wdr.transfer_sub_code ||l_seperator|| milk2.concatenated_segments ||l_seperator||
wlpn.license_plate_number ||l_seperator|| msik.concatenated_segments ||l_seperator||
wdr.revision ||l_seperator|| wdr.transaction_quantity ||l_seperator||
wdr.uom ||l_seperator|| wdr.lot_number ||l_seperator||
wdr.lot_qty||l_seperator||
wdr.serial_number||l_seperator||wdr.status_msg||l_seperator||wdr.last_update_date
CSV_LINE
FROM wms_device_requests wdr, mfg_lookups ml1, mfg_lookups ml2,
wms_devices_vl wd, mtl_parameters mp1, mtl_item_locations_kfv milk1,
mtl_parameters mp2, mtl_item_locations_kfv milk2, wms_license_plate_numbers wlpn,
mtl_system_items_kfv msik
WHERE ml1.lookup_type(+) = 'WMS_TASK_TYPES' AND ml1.lookup_code(+) = wdr.task_type_id
AND ml2.lookup_type(+)='WMS_BUS_EVENT_TYPES'
AND ml2.lookup_code(+) = wdr.business_event_id
AND wd.device_id = wdr.device_id AND mp1.organization_id = wdr.organization_id
AND milk1.organization_id(+) = wdr.organization_id
AND milk1.subinventory_code(+) = wdr.subinventory_code
AND milk1.inventory_location_id(+) = wdr.locator_id
AND mp2.organization_id (+) = wdr.transfer_org_id
AND milk2.organization_id(+) = wdr.transfer_org_id
AND milk2.subinventory_code(+) = wdr.transfer_sub_code
AND milk2.inventory_location_id (+) = wdr.transfer_loc_id
AND wlpn.lpn_id(+) = wdr.lpn_id
AND msik.organization_id(+) = wdr.organization_id
AND msik.inventory_item_id(+) = wdr.inventory_item_id
AND wdr.device_id = p_dev_id
AND nvl(wdr.task_summary,'Y') = p_task_sum
ORDER BY wdr.task_id asc, wdr.sequence_id asc, wdr.task_type_id asc;
SELECT rtrim(out_directory,'/'), out_file_prefix, nvl(LOT_SERIAL_CAPABLE, 'N'), name
INTO l_file_dir, l_file_prefix, l_lot_serial_enabled, l_dev_name
FROM wms_devices
WHERE device_id = p_device_id;
SELECT request_id INTO l_seq_id
FROM wms_device_requests
WHERE device_id = p_device_id
AND ROWNUM<2;
SELECT 1 INTO l_detail_available FROM dual
WHERE exists(
SELECT 1
FROM wms_device_requests
WHERE device_id = p_device_id
AND nvl(task_summary,'Y') = 'N');
select To_number(wdd.source_header_number) into l_order_num from
wsh_delivery_details wdd,
mtl_material_transactions_temp mmtt
where mmtt.transaction_temp_id = v_xml.taskid
and mmtt.trx_source_line_id = wdd.source_line_id;
select mtrl.header_id into l_order_num
from mtl_txn_request_lines mtrl,
mtl_material_transactions_temp mmtt
where mmtt.move_order_line_id = mtrl.line_id
and transaction_temp_id = v_xml.taskid;
-- update outfile_name
IF (l_debug = 1) THEN
trace('update outfile_name ' || l_file_name || p_device_id || l_task_sum);
UPDATE wms_device_requests
SET outfile_name = l_file_name
WHERE device_id = p_device_id
AND nvl(task_summary, 'Y') = decode(l_lot_serial_enabled,'N','Y','Y',nvl(task_summary,'Y'),'Y');
CURSOR cur_dev IS SELECT * FROM wms_device_requests where device_id is
not null;
INSERT INTO wms_device_requests_hist (request_id,
task_id,
relation_id,
sequence_id,
task_summary,
task_type_id,
business_event_id,
organization_id,
subinventory_code,
locator_id,
transfer_org_id,
transfer_sub_code,
transfer_loc_id,
inventory_item_id,
revision,
uom,
lot_number,
lot_qty,
serial_number,
lpn_id,
xfer_lpn_id,
transaction_quantity,
device_id,
status_code,
status_msg,
outfile_name,
request_date,
resubmit_date,
requested_by,
responsibility_application_id,
responsibility_id,
concurrent_request_id,
program_application_id,
program_id,
program_update_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login) VALUES
(l_rec.request_id,
l_rec.task_id,
l_rec.relation_id,
l_rec.sequence_id,
l_rec.task_summary,
l_rec.task_type_id,
l_rec.business_event_id,
l_rec.organization_id,
l_rec.subinventory_code,
l_rec.locator_id,
l_rec.transfer_org_id,
l_rec.transfer_sub_code,
l_rec.transfer_loc_id,
l_rec.inventory_item_id,
l_rec.revision,
l_rec.uom,
l_rec.lot_number,
l_rec.lot_qty,
l_rec.serial_number,
l_rec.lpn_id,
l_rec.xfer_lpn_id,
l_rec.transaction_quantity,
l_rec.device_id,
Nvl(l_rec.status_code,'S'),
l_rec.status_msg,
l_rec.outfile_name,
l_rec.last_update_date,
NULL,
l_rec.last_updated_by,
FND_GLOBAL.RESP_APPL_ID,
FND_GLOBAL.RESP_ID,
null,
null,
null,
null,
l_rec.last_update_date,
l_rec.last_updated_by,
l_rec.last_update_date,
l_rec.last_updated_by,
l_rec.last_update_login);
insert into wms_device_requests (
BUSINESS_EVENT_ID
,DEVICE_ID
,DEVICE_STATUS
,INVENTORY_ITEM_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LOCATOR_ID
,LOT_NUMBER
,LOT_QTY
,LPN_ID
,ORGANIZATION_ID
,OUTFILE_NAME
,REASON_ID
,RELATION_ID
,REQUEST_ID
,REVISION
,SEQUENCE_ID
,SERIAL_NUMBER
,STATUS_CODE
,STATUS_MSG
,SUBINVENTORY_CODE
,TASK_ID
,TASK_SUMMARY
,TASK_TYPE_ID
,TRANSACTION_QUANTITY
,TRANSFER_LOC_ID
,TRANSFER_ORG_ID
,TRANSFER_SUB_CODE
,UOM
,XFER_LPN_ID)
select
BUSINESS_EVENT_ID
,DEVICE_ID
,DEVICE_STATUS
,INVENTORY_ITEM_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LOCATOR_ID
,LOT_NUMBER
,LOT_QTY
,LPN_ID
,ORGANIZATION_ID
,OUTFILE_NAME
,REASON_ID
,RELATION_ID
,REQUEST_ID
,REVISION
,SEQUENCE_ID
,SERIAL_NUMBER
,STATUS_CODE
,STATUS_MSG
,SUBINVENTORY_CODE
,TASK_ID
,TASK_SUMMARY
,TASK_TYPE_ID
,TRANSACTION_QUANTITY
,TRANSFER_LOC_ID
,TRANSFER_ORG_ID
,TRANSFER_SUB_CODE
,UOM
,XFER_LPN_ID
from wms_device_requests_hist
WHERE request_id = p_request_id
AND status_code ='P'
AND Nvl(business_event_id,-1) = Nvl(p_bus_event_id,-1);
cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
SELECT 1 INTO l_setup_row_cnt FROM DUAL WHERE exists
(SELECT 1
FROM wms_bus_event_devices
WHERE business_event_id = p_bus_event);
SELECT request_id INTO l_parent_request_id
FROM wms_device_requests_hist
WHERE request_id = p_request_id
AND task_summary = 'Y';
UPDATE wms_dispatched_tasks
SET DEVICE_REQUEST_ID = l_request_id
WHERE TRANSACTION_TEMP_ID = p_task_trx_id;
UPDATE wms_dispatched_tasks
SET DEVICE_REQUEST_ID = l_request_id
WHERE transaction_temp_id = p_task_trx_id;
trace('Select device for each request');
l_seldev := select_Device(l_wdr, l_autoenabled, l_parent_request_id);
-- If No device has been selected for this record, then do not
-- consider this record for further processing
if (l_seldev <> 0) then
select Nvl(lot_serial_capable,'N')
into l_lot_ser_ok
from WMS_DEVICES_B
where device_id = l_seldev;
-- update the request records' RELATION_ID with the parent_request_id
IF(p_bus_event in (wms_be_task_complete, wms_be_task_skip, wms_be_task_cancel)) THEN
select nvl(notification_flag, 'N')
into l_notification_flag
from WMS_DEVICES_B
where device_id = l_cur_dev.device_id;
trace('Event is task complete/skip/cancel, check whether need to update relation_id, notification_flag='||l_notification_flag);
trace('update request and request_hist for device '||l_cur_dev.device_id|| ' and parent_request_id='||l_parent_request_id);
update wms_device_requests
set relation_id = l_parent_request_id
where device_id = l_cur_dev.device_id;
update wms_device_requests_hist
set relation_id = l_parent_request_id
where request_id = l_parent_request_id;
select d.OUTPUT_METHOD_ID, p.WCS_ENABLED
into l_deviotype, l_wcs_enabled
from WMS_DEVICES_B d, mtl_parameters p
where d.device_id = l_cur_dev.device_id
and p.organization_id = d.organization_id;
UPDATE wms_device_requests
SET status_code = l_xml_stat,
status_msg = l_status_msg
WHERE device_id = l_cur_dev.device_id;
UPDATE wms_device_requests
SET status_code = 'S'
WHERE device_id = l_cur_dev.device_id;
UPDATE wms_device_requests
SET status_code = l_req_stat,
status_msg = l_req_stat_msg
WHERE device_id = l_cur_dev.device_id;
UPDATE wms_device_requests
SET status_code = 'S'
WHERE device_id = l_cur_dev.device_id;
-- Finally delete all rows from WMS_DEV_REQUEST so that each invocation of
-- this API in this session starts with an empty table.
IF (l_debug = 1) THEN
trace(' Delete request rows');
delete from wms_device_requests;
trace('Error: G_EXC_ERR : Delete request rows');
delete from wms_device_requests;
trace('Error: G_EXC_UNEXP : Delete request rows');
delete from wms_device_requests;
delete from wms_device_requests;
Inserting into WDR is kept transparent to the OPM team who will call this
through a wrapper API in a group package
The request traffic will get logged in the wms_device_requests_hist table
in addition to being captured in wms_carousel_log
*/
PROCEDURE DEVICE_REQUEST(
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_bus_event IN NUMBER,
p_call_ctx IN VARCHAR2 ,
p_task_trx_id IN NUMBER := NULL,
p_org_id IN NUMBER := NULL,
p_item_id IN NUMBER := NULL,
p_subinv IN VARCHAR2 := NULL,
p_locator_id IN NUMBER := NULL,
p_lpn_id IN NUMBER := NULL,
p_xfr_org_id IN NUMBER := NULL,
p_xfr_subinv IN VARCHAR2 := NULL,
p_xfr_locator_id IN NUMBER := NULL,
p_trx_qty IN NUMBER := NULL,
p_trx_uom IN VARCHAR2 := NULL,
p_rev IN VARCHAR2 := NULL,
x_request_msg OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_request_id IN OUT NOCOPY NUMBER,
p_device_id IN NUMBER) IS
cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
SELECT 1 INTO l_setup_row_cnt FROM DUAL WHERE exists
(SELECT 1
FROM wms_bus_event_devices
WHERE business_event_id = p_bus_event);
SELECT request_id INTO l_parent_request_id
FROM wms_device_requests_hist
WHERE request_id = p_request_id
AND task_summary = 'Y';
UPDATE wms_dispatched_tasks
SET DEVICE_REQUEST_ID = l_request_id
WHERE TRANSACTION_TEMP_ID = p_task_trx_id;
UPDATE wms_dispatched_tasks
SET DEVICE_REQUEST_ID = l_request_id
WHERE transaction_temp_id = p_task_trx_id;
trace('Select device for each request');
--l_seldev := select_Device(l_wdr, l_autoenabled, l_parent_request_id);
trace('Not calling select_Device. Directly using the passed Device Id:'||p_device_id);
-- If No device has been selected for this record, then do not
-- consider this record for further processing
if (l_seldev <> 0) then
select Nvl(lot_serial_capable,'N')
into l_lot_ser_ok
from WMS_DEVICES_B
where device_id = l_seldev;
-- update the request records' RELATION_ID with the parent_request_id
IF(p_bus_event in (wms_be_task_complete, wms_be_task_skip, wms_be_task_cancel)) THEN
select nvl(notification_flag, 'N')
into l_notification_flag
from WMS_DEVICES_B
where device_id = l_cur_dev.device_id;
trace('Event is task complete/skip/cancel, check whether need to update relation_id, notification_flag='||l_notification_flag);
trace('update request and request_hist for device '||l_cur_dev.device_id|| ' and parent_request_id='||l_parent_request_id);
update wms_device_requests
set relation_id = l_parent_request_id
where device_id = l_cur_dev.device_id;
update wms_device_requests_hist
set relation_id = l_parent_request_id
where request_id = l_parent_request_id;
select d.OUTPUT_METHOD_ID, p.WCS_ENABLED
into l_deviotype, l_wcs_enabled
from WMS_DEVICES_B d, mtl_parameters p
where d.device_id = l_cur_dev.device_id
and p.organization_id = d.organization_id;
UPDATE wms_device_requests
SET status_code = l_xml_stat,
status_msg = l_status_msg
WHERE device_id = l_cur_dev.device_id;
UPDATE wms_device_requests
SET status_code = 'S'
WHERE device_id = l_cur_dev.device_id;
UPDATE wms_device_requests
SET status_code = l_req_stat,
status_msg = l_req_stat_msg
WHERE device_id = l_cur_dev.device_id;
UPDATE wms_device_requests
SET status_code = 'S'
WHERE device_id = l_cur_dev.device_id;
-- Finally delete all rows from WMS_DEV_REQUEST so that each invocation of
-- this API in this session starts with an empty table.
IF (l_debug = 1) THEN
trace(' Delete request rows');
delete from wms_device_requests;
trace('Error: G_EXC_ERR : Delete request rows');
delete from wms_device_requests;
trace('Error: G_EXC_UNEXP : Delete request rows');
delete from wms_device_requests;
delete from wms_device_requests;
CURSOR x_cur IS SELECT distinct device_id dev_id
FROM wms_device_requests_hist
WHERE Nvl(device_id, -1) = Nvl( p_device_id, -1)
AND request_id = p_request_id
AND status_code ='P'
AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1))
AND Nvl(task_id,-1) = Nvl(p_task_trx_id,nvl(task_id, -1))
GROUP BY device_id;
DELETE FROM wms_device_requests_hist
WHERE request_id = p_request_id
AND status_code ='P'
AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1))
AND Nvl(task_id,-1) = Nvl(p_task_trx_id,nvl(task_id, -1))
AND business_event_id = p_business_event_id ;
SELECT nvl(WCS_ENABLED,'N') into l_wcs_enabled FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = (SELECT ORGANIZATION_ID
FROM wms_device_requests_hist
WHERE request_id = p_request_id
AND status_code ='P'
AND Nvl(business_event_id,-1) = Nvl(p_business_event_id,-1)
AND ROWNUM < 2);
UPDATE wms_device_requests_hist
SET status_code = l_req_stat,
status_msg = l_stat_msg
WHERE device_id = l_rec.dev_id
AND request_id = p_request_id;
UPDATE wms_device_requests_hist
SET status_code = 'S'
WHERE device_id = l_rec.dev_id
AND request_id = p_request_id;
DELETE FROM wms_device_requests
WHERE request_id = p_request_id
AND Nvl(business_event_id,-1) = Nvl(p_business_event_id,-1)
AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1));
UPDATE wms_device_requests_hist
SET status_code = 'E',resubmit_date = null
WHERE request_id = p_request_id
AND BUSINESS_EVENT_ID =p_business_event_id
AND status_code = 'P';
UPDATE wms_device_requests_hist
SET status_code = 'E',resubmit_date = null
WHERE request_id = p_request_id
AND BUSINESS_EVENT_ID =p_business_event_id
AND status_code = 'P';
UPDATE wms_device_requests_hist
SET status_code = 'E',resubmit_date = null
WHERE request_id = p_request_id
AND BUSINESS_EVENT_ID =p_business_event_id
AND status_code = 'P';
SELECT 1 INTO l_device_cnt FROM DUAL WHERE exists
(SELECT 1
FROM wms_devices_b
WHERE ORGANIZATION_ID= p_org_id);
SELECT 1 INTO l_setup_row_cnt FROM DUAL WHERE exists
(SELECT 1
FROM wms_bus_event_devices
WHERE business_event_id = p_bus_event_id
AND organization_id = p_org_id
and business_event_id < 50);
SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
delete from wms_device_requests_hist
where creation_date < p_purge_date and organization_id = p_orgid ;
delete from wms_lpn_histories
where creation_date < p_purge_date and organization_id = p_orgid ;
delete from wms_dispatched_tasks_history
where creation_date < p_purge_date and organization_id = p_orgid ;
delete from wms_exceptions
where creation_date < p_purge_date and organization_id = p_orgid ;
delete from wms_lpn_process_temp ;
INSERT INTO mtl_purge_header (
purge_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
purge_date,
archive_flag,
purge_name,
organization_id)
VALUES (
mtl_material_transactions_s.NEXTVAL,
Sysdate,
FND_GLOBAL.user_id,
fnd_global.user_id,
Sysdate,
FND_GLOBAL.user_id,
p_purge_date,
NULL,
p_purge_name,
p_orgid );