[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_task_updated wms_waveplan_tasks_temp.error%TYPE;
g_cannot_update_putaway wms_waveplan_tasks_temp.error%TYPE;
g_cannot_update_staging_move wms_waveplan_tasks_temp.error%TYPE;
/*INSERT INTO my_temp_table VALUES (p_message,i);
SELECT transaction_temp_id, task_id, is_modified, status_id,
status_id_original, person_id, person_id_original, priority,
priority_original
FROM wms_waveplan_tasks_temp
WHERE is_modified = 'Y';
UPDATE wms_waveplan_tasks_temp
SET RESULT = DECODE (RESULT, NULL, 'X', 'S', 'Y', 'E', 'Z')
WHERE transaction_temp_id = p_transaction_temp_id (i)
AND task_type_id = p_task_type_id (i);
UPDATE wms_waveplan_tasks_temp
SET RESULT =
DECODE (RESULT,
'Y', 'S',
'Z', 'E',
'X', NULL,
RESULT
)
WHERE RESULT IN ('X', 'Y', 'Z');
SELECT NVL (jcgc.visible_flag, jgc.visible_flag)
BULK COLLECT INTO visible_columns
FROM jtf_grid_cols_b jgc, jtf_custom_grid_cols jcgc
WHERE jgc.grid_datasource_name = jcgc.grid_datasource_name(+)
AND jgc.grid_col_alias = jcgc.grid_col_alias(+)
AND jgc.grid_datasource_name = 'WMS_WAVEPLAN_TASKS'
AND jcgc.created_by(+) = l_user_id
AND jgc.grid_col_alias IN
('ALLOCATED_LPN',
'ASSEMBLY',
'CARRIER',
'CARTONIZATION_LPN',
'CONTAINER_ITEM',
'CONTENT_LPN',
'CUSTOMER',
'DELIVERY',
'DEPARTMENT',
'DROP_LPN', --bug 4510849
'FROM_LPN',
'LINE',
'LINE_NUMBER',
'LOADED_LPN', --bug 4510849
'MACHINE_RESOURCE_CODE',
'NUM_OF_CHILD_TASKS',
'OPERATION_PLAN',
'OPERATION_SEQUENCE',
'OP_PLAN_INSTANCE_ID',
'PERSON',
'PERSON_RESOURCE_CODE',
'PICKED_LPN', --bug 4510849
'SHIP_METHOD',
'SHIP_TO_COUNTRY',
'SHIP_TO_POSTAL_CODE',
'SHIP_TO_STATE',
'SOURCE_HEADER',
'STATUS',
'TASK_TYPE',
'TO_LOCATOR',
'TO_LPN',
'TO_ORGANIZATION_CODE',
'TRANSACTION_SOURCE_TYPE',
'USER_TASK_TYPE'
)
ORDER BY NVL (jcgc.grid_col_alias, jgc.grid_col_alias);
SELECT REPLACE (meaning, '''', ''''''), meaning
BULK COLLECT INTO g_status_codes, g_status_codes_orig
FROM mfg_lookups
WHERE lookup_type = 'WMS_TASK_STATUS'
ORDER BY lookup_code;
SELECT REPLACE (meaning, '''', ''''''), meaning
BULK COLLECT INTO g_task_types, g_task_types_orig
FROM mfg_lookups
WHERE lookup_type = 'WMS_TASK_TYPES'
ORDER BY lookup_code;
SELECT REPLACE (meaning, '''', '''''')
BULK COLLECT INTO g_plan_task_types
FROM mfg_lookups
WHERE lookup_type = 'WMS_PLAN_TASK_TYPES'
ORDER BY lookup_code;
SELECT REPLACE (meaning, '''', '''''')
BULK COLLECT INTO g_plan_status_codes
FROM mfg_lookups
WHERE lookup_type = 'WMS_OP_PLAN_INSTANCE_STATUS'
ORDER BY lookup_code;
SELECT DECODE (project_reference_enabled, 1, 'Y', 'N')
INTO g_project_enabled_organization
FROM mtl_parameters
WHERE organization_id = p_organization_id;
UPDATE wms_waveplan_tasks_temp wwtt
SET LOCATOR =
inv_project.get_locator (wwtt.locator_id,
wwtt.organization_id
)
WHERE LOCATOR IS NULL;
UPDATE wms_waveplan_tasks_temp wwtt
SET to_locator =
inv_project.get_locator (wwtt.to_locator_id,
wwtt.organization_id
)
WHERE to_locator IS NULL;
SELECT ph.segment1, pl.line_num, wwtt.transaction_temp_id,
wwtt.task_type_id
BULK COLLECT INTO l_source_header, l_line_number, l_temp_id,
l_task_type_id
FROM po_line_locations_trx_v pll,--CLM Changes, using CLM views instead of base tables
po_headers_trx_v ph,
po_lines_trx_v pl,
wms_waveplan_tasks_temp wwtt
WHERE pll.line_location_id = wwtt.reference_id
AND pll.po_line_id = pl.po_line_id
AND ph.po_header_id = pl.po_header_id
AND wwtt.REFERENCE = 'PO_LINE_LOCATION_ID'
AND wwtt.source_header IS NULL
AND wwtt.reference_id IS NOT NULL;
UPDATE wms_waveplan_tasks_temp wwtt
SET source_header = l_source_header (i),
line_number = l_line_number (i)
WHERE wwtt.transaction_temp_id = l_temp_id (i)
AND wwtt.task_type_id = l_task_type_id (i);
SELECT ooh.order_number, ool.line_number, wwtt.transaction_temp_id,
wwtt.task_type_id
BULK COLLECT INTO l_source_header, l_line_number, l_temp_id,
l_task_type_id
FROM oe_order_lines_all ool,
oe_order_headers_all ooh,
wms_waveplan_tasks_temp wwtt
WHERE ool.line_id = wwtt.reference_id
AND ooh.header_id = ool.header_id
AND wwtt.REFERENCE = 'ORDER_LINE_ID'
AND wwtt.source_header IS NULL
AND wwtt.reference_id IS NOT NULL;
UPDATE wms_waveplan_tasks_temp wwtt
SET source_header = l_source_header (i),
line_number = l_line_number (i)
WHERE wwtt.transaction_temp_id = l_temp_id (i)
AND wwtt.task_type_id = l_task_type_id (i);
SELECT DECODE (rsl.requisition_line_id,
NULL, rsh.shipment_num,
prh.segment1
),
DECODE (rsl.requisition_line_id,
NULL, rsl.line_num,
prl.line_num
),
wwtt.transaction_temp_id, wwtt.task_type_id
BULK COLLECT INTO l_source_header,
l_line_number,
l_temp_id, l_task_type_id
-- MOAC po_requisition_headers and
-- po_requisition_lines switched to use _ALL tables
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
wms_waveplan_tasks_temp wwtt
WHERE rsl.shipment_line_id = wwtt.reference_id
AND prh.requisition_header_id(+) = prl.requisition_header_id
AND rsl.requisition_line_id = prl.requisition_line_id(+)
AND rsl.shipment_header_id = rsh.shipment_header_id
AND wwtt.REFERENCE = 'SHIPMENT_LINE_ID'
AND wwtt.source_header IS NULL
AND wwtt.reference_id IS NOT NULL;
UPDATE wms_waveplan_tasks_temp wwtt
SET source_header = l_source_header (i),
line_number = l_line_number (i)
WHERE wwtt.transaction_temp_id = l_temp_id (i)
AND wwtt.task_type_id = l_task_type_id (i);
SELECT COUNT (1), wwtt.transaction_temp_id
BULK COLLECT INTO l_num_of_child_tasks_tbl, l_parent_temp_ids_tbl
FROM wms_waveplan_tasks_temp wwtt,mtl_cycle_count_entries mcce, mtl_cycle_count_headers mcch
WHERE mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.inventory_item_id = wwtt.inventory_item_id
AND mcce.subinventory = wwtt.subinventory
AND mcce.locator_id = wwtt.locator_id
AND NVL(mcce.revision,'-1') = NVL(wwtt.revision, '-1')
AND mcch.cycle_count_header_name = wwtt.source_header
AND mcch.organization_id = wwtt.organization_id
AND mcce.entry_status_code not in (2,4,5)
AND wwtt.status_id in (1,2) --bug 12614348 - Show child task count for Queued tasks too
AND wwtt.task_type_id = 3
--bug 12614348 - Show child task count for Queued tasks too
/*AND not exists (select 1 from wms_dispatched_tasks wdt
where wdt.transaction_temp_id = mcce.cycle_count_entry_id
and wdt.status in (2,3))*/
GROUP BY wwtt.transaction_temp_id;
UPDATE wms_waveplan_tasks_temp wwtt
SET wwtt.num_of_child_tasks = l_num_of_child_tasks_tbl (i)
WHERE wwtt.transaction_temp_id = l_parent_temp_ids_tbl (i)
AND l_num_of_child_tasks_tbl (i) <> 1;
SELECT COUNT (1), wwtt.transaction_temp_id
BULK COLLECT INTO l_num_of_child_tasks_tbl, l_parent_temp_ids_tbl
FROM wms_waveplan_tasks_temp wwtt,
mtl_material_transactions_temp mmtt
WHERE wwtt.transaction_temp_id = mmtt.parent_line_id
AND wwtt.transaction_temp_id <> mmtt.transaction_temp_id
GROUP BY wwtt.transaction_temp_id;
UPDATE wms_waveplan_tasks_temp wwtt
SET wwtt.num_of_child_tasks = l_num_of_child_tasks_tbl (i)
WHERE wwtt.transaction_temp_id = l_parent_temp_ids_tbl (i);
UPDATE wms_waveplan_tasks_temp
-- transfer_lpn_id would be null if it is loaded into the same lpn as that of picked.
SET picked_lpn_id = NVL(content_lpn_id,from_lpn_id), /*12984304 Changed the order in NVL * /
loaded_lpn_id = Nvl(to_lpn_id, Nvl(content_lpn_id,from_lpn_id))/*12984304 Changed the order in NVL * /
WHERE task_type_id IN (1, 4, 5, 6)
AND status_id = 6;
UPDATE wms_waveplan_tasks_temp wwtt
-- transfer_lpn_id would be null if it is loaded into the same lpn as that of picked.
SET wwtt.picked_lpn_id = NVL(wwtt.content_lpn_id,wwtt.from_lpn_id), /*12984304 Changed the order in NVL */
wwtt.loaded_lpn_id = NVL((select transfer_lpn_id from wms_dispatched_tasks_history wdth where wdth.task_id = wwtt.task_id),
NVL(wwtt.to_lpn_id, NVL(wwtt.content_lpn_id, wwtt.from_lpn_id)))
WHERE wwtt.task_type_id IN (1, 4, 5, 6)
AND wwtt.status_id = 6;
UPDATE wms_waveplan_tasks_temp
SET picked_lpn = (SELECT license_plate_number FROM wms_license_plate_numbers WHERE lpn_id = picked_lpn_id)
WHERE picked_lpn_id IS NOT NULL
AND task_type_id IN (1, 4, 5, 6)
AND status_id = 6;
UPDATE wms_waveplan_tasks_temp
SET loaded_lpn = (SELECT license_plate_number FROM wms_license_plate_numbers WHERE lpn_id = loaded_lpn_id)
WHERE loaded_lpn_id IS NOT NULL
AND task_type_id IN (1, 4, 5, 6)
AND status_id = 6;
UPDATE wms_waveplan_tasks_temp wstt
SET drop_lpn = Nvl((select license_plate_number
FROM wms_license_plate_numbers wlpn, mtl_material_transactions mmt
WHERE wlpn.lpn_id = mmt.transfer_lpn_id
AND mmt.transaction_action_id = 50
AND mmt.transaction_set_id = wstt.transaction_set_id
AND mmt.transaction_quantity > 0),
loaded_lpn)
WHERE task_type_id IN (1, 4, 5, 6)
AND status_id = 6;
UPDATE wms_waveplan_tasks_temp
-- transfer_lpn_id would be null if it is loaded into the same lpn as that of picked.
SET picked_lpn_id = NVL(content_lpn_id,from_lpn_id), /*12984304 Changed the order in NVL*/
loaded_lpn_id = to_lpn_id
WHERE task_type_id IN (1, 4, 5, 6)
AND status_id = 4;
UPDATE wms_waveplan_tasks_temp
SET picked_lpn = (SELECT license_plate_number FROM wms_license_plate_numbers WHERE lpn_id = picked_lpn_id)
WHERE picked_lpn_id IS NOT NULL
AND task_type_id IN (1, 4, 5, 6)
AND status_id = 4;
UPDATE wms_waveplan_tasks_temp
SET loaded_lpn = (SELECT license_plate_number FROM wms_license_plate_numbers WHERE lpn_id = loaded_lpn_id)
WHERE loaded_lpn_id IS NOT NULL
AND task_type_id IN (1, 4, 5, 6)
AND status_id = 4;
FUNCTION get_generic_insert (
p_is_unreleased BOOLEAN DEFAULT FALSE,
p_is_pending BOOLEAN DEFAULT FALSE,
p_is_queued BOOLEAN DEFAULT FALSE,
p_is_dispatched BOOLEAN DEFAULT FALSE,
p_is_active BOOLEAN DEFAULT FALSE,
p_is_loaded BOOLEAN DEFAULT FALSE,
p_is_completed BOOLEAN DEFAULT FALSE
,p_is_picknone BOOLEAN DEFAULT FALSE --ER13869750
)
RETURN VARCHAR2
IS
l_insert_generic VARCHAR2 (2000);
l_insert_generic := 'INSERT INTO wms_waveplan_tasks_temp( ';
/* Patchset J - ATF - we have to insert expansion_code, plans_tasks also
* into the temp table
*/
l_insert_generic := l_insert_generic || 'expansion_code';
l_insert_generic := l_insert_generic || ', plans_tasks';
l_insert_generic := l_insert_generic || ', transaction_temp_id ';
l_insert_generic := l_insert_generic || ', parent_line_id ';
l_insert_generic := l_insert_generic || ', inventory_item_id ';
l_insert_generic := l_insert_generic || ', item ';
l_insert_generic := l_insert_generic || ', item_description ';
l_insert_generic := l_insert_generic || ', unit_weight ';
l_insert_generic := l_insert_generic || ', weight_uom_code ';
l_insert_generic := l_insert_generic || ', unit_volume ';
l_insert_generic := l_insert_generic || ', volume_uom_code ';
l_insert_generic := l_insert_generic || ', organization_id ';
l_insert_generic := l_insert_generic || ', revision ';
l_insert_generic := l_insert_generic || ', subinventory ';
l_insert_generic := l_insert_generic || ', locator_id ';
l_insert_generic := l_insert_generic || ', locator ';
l_insert_generic := l_insert_generic || ', status_id ';
l_insert_generic := l_insert_generic || ', status_id_original ';
l_insert_generic := l_insert_generic || ', status ';
l_insert_generic := l_insert_generic || ', transaction_type_id ';
l_insert_generic := l_insert_generic || ', transaction_action_id ';
l_insert_generic := l_insert_generic || ', transaction_source_type_id ';
l_insert_generic := l_insert_generic || ', transaction_source_type ';
l_insert_generic := l_insert_generic || ', transaction_source_id ';
l_insert_generic := l_insert_generic || ', transaction_source_line_id ';
l_insert_generic := l_insert_generic || ', to_organization_id ';
l_insert_generic := l_insert_generic || ', to_organization_code ';
l_insert_generic := l_insert_generic || ', to_subinventory ';
l_insert_generic := l_insert_generic || ', to_locator_id ';
l_insert_generic := l_insert_generic || ', to_locator ';
l_insert_generic := l_insert_generic || ', transaction_uom ';
l_insert_generic := l_insert_generic || ', transaction_quantity ';
l_insert_generic := l_insert_generic || ', user_task_type_id ';
l_insert_generic := l_insert_generic || ', user_task_type ';
l_insert_generic := l_insert_generic || ', move_order_line_id ';
l_insert_generic := l_insert_generic || ', pick_slip_number ';
l_insert_generic := l_insert_generic || ', cartonization_id ';
l_insert_generic := l_insert_generic || ', cartonization_lpn ';
l_insert_generic := l_insert_generic || ', allocated_lpn_id ';
l_insert_generic := l_insert_generic || ', allocated_lpn ';
l_insert_generic := l_insert_generic || ', container_item_id ';
l_insert_generic := l_insert_generic || ', container_item ';
l_insert_generic := l_insert_generic || ', from_lpn_id ';
l_insert_generic := l_insert_generic || ', from_lpn ';
l_insert_generic := l_insert_generic || ', content_lpn_id ';
l_insert_generic := l_insert_generic || ', content_lpn ';
l_insert_generic := l_insert_generic || ', to_lpn_id ';
l_insert_generic := l_insert_generic || ', to_lpn ';
l_insert_generic := l_insert_generic || ', mmtt_last_update_date ';
l_insert_generic := l_insert_generic || ', mmtt_last_updated_by ';
l_insert_generic := l_insert_generic || ', priority ';
l_insert_generic := l_insert_generic || ', priority_original ';
l_insert_generic := l_insert_generic || ', task_type_id ';
l_insert_generic := l_insert_generic || ', task_type ';
l_insert_generic := l_insert_generic || ', creation_time ';
l_insert_generic := l_insert_generic || ', operation_plan_id ';
l_insert_generic := l_insert_generic || ', operation_plan ';
l_insert_generic := l_insert_generic || ', operation_sequence ';
l_insert_generic := l_insert_generic || ', op_plan_instance_id ';
l_insert_generic := l_insert_generic || ', task_id ';
l_insert_generic := l_insert_generic || ', person_id ';
l_insert_generic := l_insert_generic || ', person_id_original ';
l_insert_generic := l_insert_generic || ', person ';
l_insert_generic := l_insert_generic || ', effective_start_date ';
l_insert_generic := l_insert_generic || ', effective_end_date ';
l_insert_generic := l_insert_generic || ', person_resource_id ';
l_insert_generic := l_insert_generic || ', person_resource_code ';
l_insert_generic := l_insert_generic || ', machine_resource_id ';
l_insert_generic :=
l_insert_generic || ', machine_resource_code ';
l_insert_generic := l_insert_generic || ', equipment_instance ';
l_insert_generic := l_insert_generic || ', dispatched_time ';
l_insert_generic := l_insert_generic || ', loaded_time ';
l_insert_generic := l_insert_generic || ', drop_off_time ';
l_insert_generic := l_insert_generic || ', wdt_last_update_date ';
l_insert_generic := l_insert_generic || ', wdt_last_updated_by ';
l_insert_generic := l_insert_generic || ', is_modified ';
l_insert_generic := l_insert_generic || ', secondary_transaction_uom ';
l_insert_generic := l_insert_generic || ', secondary_transaction_quantity ';
l_insert_generic := l_insert_generic || ', transaction_set_id ';
RETURN l_insert_generic;
FUNCTION get_generic_select (
p_is_unreleased BOOLEAN DEFAULT FALSE,
p_is_pending BOOLEAN DEFAULT FALSE,
p_is_queued BOOLEAN DEFAULT FALSE,
p_is_dispatched BOOLEAN DEFAULT FALSE,
p_is_active BOOLEAN DEFAULT FALSE,
p_is_loaded BOOLEAN DEFAULT FALSE,
p_is_completed BOOLEAN DEFAULT FALSE,
p_populate_merged_tasks BOOLEAN DEFAULT FALSE
,p_is_picknone BOOLEAN DEFAULT FALSE --ER13869750
)
RETURN VARCHAR2
IS
l_select_generic VARCHAR2 (4000);
Debug(' Entered into get_generic_select ' || i );
Debug('Entered into generic select');
l_select_generic := 'SELECT ';
l_select_generic := l_select_generic || 'null, '; -- expansion_code
l_select_generic :=
l_select_generic
|| 'decode(mmtt.parent_line_id,'
|| ' null,decode(mmtt.operation_plan_id,null,'''
|| g_plan_task_types (1)
|| ''','
|| ' decode(mmtt.transaction_action_id,28,'''
|| g_plan_task_types(1)
|| ''','
|| ' decode(mmtt.transaction_source_type_id, 5, '''
|| g_plan_task_types(1)
|| ''','
|| ' 13, decode(mmtt.transaction_type_id,51,'''
|| g_plan_task_types(1)
|| ''','''
|| g_plan_task_types(3)
|| ''' )))), '
|| ' mmtt.transaction_temp_id, '''
|| g_plan_task_types (4)
|| ''','''
|| g_plan_task_types (2)
|| '''),';
l_select_generic := l_select_generic || 'mmtt.transaction_temp_id, ';
l_select_generic := l_select_generic || 'mmtt.parent_line_id, ';
l_select_generic := l_select_generic || 'mmtt.inventory_item_id, ';
l_select_generic :=
l_select_generic || 'msiv.concatenated_segments, ';
l_select_generic := l_select_generic || 'msiv.description, ';
l_select_generic := l_select_generic || 'msiv.unit_weight, ';
l_select_generic := l_select_generic || 'msiv.weight_uom_code, ';
l_select_generic := l_select_generic || 'msiv.unit_volume, ';
l_select_generic := l_select_generic || 'msiv.volume_uom_code, ';
l_select_generic := l_select_generic || 'mmtt.organization_id, ';
l_select_generic := l_select_generic || 'mmtt.revision, '; --revision
l_select_generic := l_select_generic || 'mmtt.subinventory_code, ';
l_select_generic := l_select_generic || 'mmtt.locator_id, ';
l_select_generic :=
l_select_generic
|| 'decode(milv.segment19, null, milv.concatenated_segments, null), ';
l_select_generic :=
l_select_generic
|| 'decode(wdt.status, null, nvl(mmtt.wms_task_status, 1), wdt.status), ';
l_select_generic :=
l_select_generic
|| 'decode(wdt.status, null, nvl(mmtt.wms_task_status, 1), wdt.status), ';
l_select_generic :=
l_select_generic
|| 'decode(decode(wdt.status, null, nvl(mmtt.wms_task_status, 1), wdt.status),'
|| '1, '''
|| g_status_codes (1)
|| ''', 2, '''
|| g_status_codes (2)
|| ''', 3, '''
|| g_status_codes (3)
|| ''', 4, '''
|| g_status_codes (4)
|| ''', 5, '''
|| g_status_codes (5)
|| ''', 6, '''
|| g_status_codes (6)
|| ''', 7, '''
|| g_status_codes (7)
|| ''', 8, '''
|| g_status_codes (8)
|| ''', '
|| '9, '''
|| g_status_codes (9)
|| '''), ';
l_select_generic :=
l_select_generic || 'nvl(mmtt.wms_task_status, 1), ';
l_select_generic :=
l_select_generic || 'nvl(mmtt.wms_task_status, 1), ';
l_select_generic :=
l_select_generic
|| 'decode(nvl(mmtt.wms_task_status, 1),'
|| '1, '''
|| g_status_codes (1)
|| ''', 8, '''
|| g_status_codes (8)
|| '''), ';
l_select_generic := l_select_generic || 'mmtt.transaction_type_id, ';
l_select_generic :=
l_select_generic || 'mmtt.transaction_action_id, ';
l_select_generic :=
l_select_generic || 'mmtt.transaction_source_type_id, ';
l_select_generic :=
l_select_generic || 'mtst.transaction_source_type_name, ';
l_select_generic :=
l_select_generic || 'mmtt.transaction_source_id, ';
l_select_generic := l_select_generic || 'mmtt.trx_source_line_id, ';
l_select_generic :=
l_select_generic || 'mmtt.transfer_organization, ';
l_select_generic := l_select_generic || 'mp1.organization_code, ';
l_select_generic :=
l_select_generic || 'mmtt.transfer_subinventory, ';
l_select_generic := l_select_generic || 'mmtt.transfer_to_location, ';
l_select_generic :=
l_select_generic
|| 'decode(milv1.segment19, null, milv1.concatenated_segments, null), ';
l_select_generic := l_select_generic || 'mmtt.transaction_uom, ';
l_select_generic := l_select_generic || 'mmtt.transaction_quantity, ';
l_select_generic :=
l_select_generic || 'mmtt.standard_operation_id, ';
l_select_generic := l_select_generic || 'bso.operation_code, ';
l_select_generic :=
l_select_generic || 'mmtt.move_order_line_id, ';
l_select_generic := l_select_generic || 'mmtt.pick_slip_number, ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'mmtt.pick_slip_number, '; --pick_slip_number
l_select_generic := l_select_generic || 'to_number(null), '; --pick_slip_number
l_select_generic := l_select_generic || 'mmtt.cartonization_id, ';
l_select_generic :=
l_select_generic || 'wlpn2.license_plate_number, ';
l_select_generic := l_select_generic || 'mmtt.allocated_lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn1.license_plate_number, ';
l_select_generic := l_select_generic || 'mmtt.container_item_id, ';
l_select_generic :=
l_select_generic || 'msiv1.concatenated_segments, ';
l_select_generic := l_select_generic || 'mmtt.lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn5.license_plate_number, ';
l_select_generic := l_select_generic || 'mmtt.content_lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn3.license_plate_number, ';
l_select_generic := l_select_generic || 'mmtt.transfer_lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn4.license_plate_number, ';
l_select_generic := l_select_generic || 'mmtt.last_update_date, ';
l_select_generic := l_select_generic || 'mmtt.last_updated_by, ';
l_select_generic := l_select_generic || 'mmtt.task_priority, ';
l_select_generic := l_select_generic || 'mmtt.task_priority, ';
l_select_generic := l_select_generic || 'mmtt.wms_task_type, ';
l_select_generic :=
l_select_generic
|| 'decode(mmtt.wms_task_type,'
|| '1, '''
|| g_task_types (1)
|| ''', 2, '''
|| g_task_types (2)
|| ''', 3, '''
|| g_task_types (3)
|| ''', 4, '''
|| g_task_types (4)
|| ''', 5, '''
|| g_task_types (5)
|| ''', 6, '''
|| g_task_types (6)
|| ''', 7, '''
|| g_task_types (7)
|| ''', 8, '''
|| g_task_types (8)
|| '''), ';
l_select_generic := l_select_generic || 'mmtt.creation_date, ';
l_select_generic := l_select_generic || 'mmtt.operation_plan_id, ';
l_select_generic :=
l_select_generic || 'wop.operation_plan_name, ';
l_select_generic := l_select_generic || 'wooi.operation_sequence, '; --operation_sequence
l_select_generic :=
l_select_generic || 'wooi.op_plan_instance_id, ';
l_select_generic := l_select_generic || 'to_number(null), '; --operation_sequence
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'wdt.task_id, ';
l_select_generic := l_select_generic || 'wdt.person_id, ';
l_select_generic := l_select_generic || 'wdt.person_id, ';
l_select_generic := l_select_generic || 'pap.full_name, ';
l_select_generic :=
l_select_generic || 'wdt.effective_start_date, ';
l_select_generic := l_select_generic || 'wdt.effective_end_date, ';
l_select_generic := l_select_generic || 'wdt.person_resource_id, ';
l_select_generic := l_select_generic || 'br1.resource_code, ';
l_select_generic :=
l_select_generic || 'wdt.machine_resource_id, ';
l_select_generic := l_select_generic || 'br2.resource_code, ';
l_select_generic := l_select_generic || 'wdt.equipment_instance, ';
l_select_generic := l_select_generic || 'wdt.dispatched_time, ';
l_select_generic := l_select_generic || 'wdt.loaded_time, ';
l_select_generic := l_select_generic || 'wdt.drop_off_time, ';
l_select_generic := l_select_generic || 'wdt.last_update_date, ';
l_select_generic := l_select_generic || 'wdt.last_updated_by, ';
l_select_generic :=
l_select_generic
|| 'to_number(null), ' /*task_id*/
|| 'to_number(null), ' /*person_id*/
|| 'to_number(null), '; --person_id_original
l_select_generic := l_select_generic || 'NULL, ';
l_select_generic :=
l_select_generic
|| 'TO_DATE(NULL), ' /*effective_start_date*/
|| 'TO_DATE(NULL), ' /*effective_end_date*/
|| 'TO_NUMBER(NULL), '; --person_resource_id
l_select_generic := l_select_generic || 'NULL, ';
l_select_generic := l_select_generic || 'TO_NUMBER(NULL), ';
l_select_generic := l_select_generic || 'NULL, ';
l_select_generic := l_select_generic || 'NULL, ';
l_select_generic := l_select_generic || 'TO_DATE(NULL), ';
l_select_generic := l_select_generic || 'TO_DATE(NULL), ';
l_select_generic := l_select_generic || 'TO_DATE(NULL), ';
l_select_generic := l_select_generic || 'TO_DATE(NULL), ';
l_select_generic := l_select_generic || 'TO_NUMBER(NULL), ';
l_select_generic := l_select_generic || '''N'', '; --is_modified
l_select_generic := l_select_generic || 'mmtt.secondary_uom_code, ';
l_select_generic :=
l_select_generic || 'mmtt.secondary_transaction_quantity ';
RETURN l_select_generic;
l_select_generic := 'SELECT ';
l_select_generic := l_select_generic || ' /*+ leading ( wdd ) and index ( wdd WSH_DELIVERY_DETAILS_N1 ) push_pred(wdd) push_pred(wt) push_pred(wts) push_pred(wdl) push_pred(wnd) */ null, '; -- expansion_code
l_select_generic :=
l_select_generic
|| 'decode(wdth.is_parent,'
|| '''Y'', decode(wdth.transaction_action_id,28,'''
|| g_plan_task_types (4)
|| ''','
|| 'decode(wdth.transaction_source_type_id,'
|| '5,decode(wdth.transaction_type_id,35,'''
|| g_plan_task_types (4)
|| ''','''
|| g_plan_task_types (3)
|| '''),'
|| '13, decode(wdth.transaction_type_id,51,'''
|| g_plan_task_types (4)
|| ''','''
|| g_plan_task_types (3)
|| '''),'''
|| g_plan_task_types (3)
|| ''')),'
|| 'decode(wdth.parent_transaction_id,null,''' --'N' or null
|| g_plan_task_types(1)
|| ''','
|| 'decode(wdth.transaction_action_id,28,'''
|| g_plan_task_types(5)
|| ''','
|| 'decode(wdth.transaction_source_type_id,'
|| '5,decode(wdth.transaction_type_id,35,'''
|| g_plan_task_types (5)
|| ''','''
|| g_plan_task_types (2)
|| '''),'
|| '13, decode(wdth.transaction_type_id,51,'''
|| g_plan_task_types (5)
|| ''','''
|| g_plan_task_types (2)
|| '''),'''
|| g_plan_task_types (2)
|| ''')))),'; --plan task
l_select_generic := l_select_generic || 'mmt.transaction_id, ';
l_select_generic := l_select_generic || 'wdth.parent_transaction_id,';
l_select_generic := l_select_generic || 'mmt.inventory_item_id, ';
l_select_generic :=
l_select_generic || 'msiv.concatenated_segments, ';
l_select_generic := l_select_generic || 'msiv.description, ';
l_select_generic := l_select_generic || 'msiv.unit_weight, ';
l_select_generic := l_select_generic || 'msiv.weight_uom_code, ';
l_select_generic := l_select_generic || 'msiv.unit_volume, ';
l_select_generic := l_select_generic || 'msiv.volume_uom_code, ';
l_select_generic := l_select_generic || 'mmt.organization_id, ';
l_select_generic := l_select_generic || 'mmt.revision, '; --revision
l_select_generic := l_select_generic || 'mmt.subinventory_code, ';
l_select_generic := l_select_generic || 'mmt.locator_id, ';
l_select_generic :=
l_select_generic
|| 'decode(milv.segment19, null, milv.concatenated_segments, null), ';
l_select_generic := l_select_generic || '6, '; --status_id
l_select_generic := l_select_generic || '6, '; --status_id_original
l_select_generic :=
l_select_generic || '''' || g_status_codes (6)
|| ''', '; --status
l_select_generic := l_select_generic || 'mmt.transaction_type_id, ';
l_select_generic := l_select_generic || 'mmt.transaction_action_id, ';
l_select_generic :=
l_select_generic || 'mmt.transaction_source_type_id, ';
l_select_generic :=
l_select_generic || 'mtst.transaction_source_type_name, ';
l_select_generic := l_select_generic || 'mmt.transaction_source_id, ';
l_select_generic := l_select_generic || 'mmt.trx_source_line_id, ';
l_select_generic :=
l_select_generic || 'mmt.transfer_organization_id, ';
l_select_generic := l_select_generic || 'mp1.organization_code, ';
l_select_generic := l_select_generic || 'mmt.transfer_subinventory, ';
l_select_generic := l_select_generic || 'mmt.transfer_locator_id, ';
l_select_generic :=
l_select_generic
|| 'decode(milv1.segment19, null, milv1.concatenated_segments, null), ';
l_select_generic := l_select_generic || 'mmt.transaction_uom, ';
l_select_generic :=
l_select_generic || 'abs(mmt.transaction_quantity), ';
l_select_generic := l_select_generic || 'wdth.user_task_type, ';
l_select_generic := l_select_generic || 'bso.operation_code, ';
l_select_generic := l_select_generic || 'mmt.move_order_line_id, ';
l_select_generic := l_select_generic || 'mmt.pick_slip_number, ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'null, '; --cartonization_lpn
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'null, ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'null, '; /*container item */
l_select_generic := l_select_generic || 'mmt.lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn5.license_plate_number, ';
l_select_generic := l_select_generic || 'mmt.content_lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn3.license_plate_number, ';
l_select_generic := l_select_generic || 'mmt.transfer_lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn4.license_plate_number, ';
l_select_generic := l_select_generic || 'to_date(null), ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'wdth.priority, '; --priority
l_select_generic := l_select_generic || 'wdth.priority, ';
l_select_generic := l_select_generic || 'wdth.task_type, ';
l_select_generic :=
l_select_generic
|| 'decode(wdth.task_type,'
|| '1, '''
|| g_task_types (1)
|| ''', 2, '''
|| g_task_types (2)
|| ''', 3, '''
|| g_task_types (3)
|| ''', 4, '''
|| g_task_types (4)
|| ''', 5, '''
|| g_task_types (5)
|| ''', 6, '''
|| g_task_types (6)
|| ''', 7, '''
|| g_task_types (7)
|| '''), ';
l_select_generic := l_select_generic || 'to_date(null), ';
l_select_generic := l_select_generic || 'wdth.operation_plan_id, ';
l_select_generic :=
l_select_generic || 'wop.operation_plan_name, ';
l_select_generic := l_select_generic || 'to_number(null), ';--operation_sequence
l_select_generic := l_select_generic || 'to_number(null), ';--operation_sequence
l_select_generic := l_select_generic || 'wdth.op_plan_instance_id, ';
l_select_generic := l_select_generic || 'wdth.task_id, '; --task_id
l_select_generic := l_select_generic || 'wdth.person_id, ';
l_select_generic := l_select_generic || 'wdth.person_id, ';
l_select_generic := l_select_generic || 'pap.full_name, ';
l_select_generic := l_select_generic || 'wdth.effective_start_date, ';
l_select_generic := l_select_generic || 'wdth.effective_end_date, ';
l_select_generic := l_select_generic || 'wdth.person_resource_id, ';
l_select_generic := l_select_generic || 'br1.resource_code, ';
l_select_generic := l_select_generic || 'wdth.machine_resource_id, ';
l_select_generic := l_select_generic || 'br2.resource_code, ';
l_select_generic := l_select_generic || 'wdth.equipment_instance, ';
l_select_generic := l_select_generic || 'wdth.dispatched_time, ';
l_select_generic := l_select_generic || 'wdth.loaded_time, ';
l_select_generic := l_select_generic || 'wdth.drop_off_time, ';
l_select_generic := l_select_generic || 'to_date(null), ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || '''N'', '; --is_modified
l_select_generic := l_select_generic || 'mmt.secondary_uom_code, ';
l_select_generic :=
l_select_generic || 'abs(mmt.secondary_transaction_quantity), ';
l_select_generic := l_select_generic || 'mmt.transaction_set_id ';
l_select_generic := l_select_generic || 'mmt.secondary_uom_code, ';
l_select_generic :=
l_select_generic || 'abs(mmt.secondary_transaction_quantity) ';
RETURN l_select_generic;
l_select_generic := 'SELECT ';
l_select_generic := l_select_generic || 'null, '; -- expansion_code
l_select_generic :=
l_select_generic
|| 'decode(wdth.is_parent,'
|| '''N'', decode(wdth.operation_plan_id,null, decode(wdth.parent_transaction_id,null,'''
|| g_plan_task_types (1)
|| ''','''
|| g_plan_task_types (5)
|| '''),'''
|| g_plan_task_types (2)
|| '''), '
|| 'decode(wdth.transaction_action_id,28,'''
|| g_plan_task_types (4)
|| ''','
|| 'decode(wdth.transaction_source_type_id,'
|| '5,decode(wdth.transaction_type_id,35,'''
|| g_plan_task_types (4)
|| ''','''
|| g_plan_task_types (3)
|| '''),'
|| '13, decode(wdth.transaction_type_id,51,'''
|| g_plan_task_types (4)
|| ''','''
|| g_plan_task_types (3)
|| '''),'''
|| g_plan_task_types (3)
|| '''))),'; --plan_task
l_select_generic := l_select_generic || 'wdth.transaction_id, ';
l_select_generic := l_select_generic || 'wdth.parent_transaction_id,';
l_select_generic := l_select_generic || 'wdth.inventory_item_id, ';
l_select_generic :=
l_select_generic || 'msiv.concatenated_segments, ';
l_select_generic := l_select_generic || 'msiv.description, ';
l_select_generic := l_select_generic || 'msiv.unit_weight, ';
l_select_generic := l_select_generic || 'msiv.weight_uom_code, ';
l_select_generic := l_select_generic || 'msiv.unit_volume, ';
l_select_generic := l_select_generic || 'msiv.volume_uom_code, ';
l_select_generic := l_select_generic || 'wdth.organization_id, ';
l_select_generic := l_select_generic || 'wdth.revision, '; --revision
l_select_generic :=
l_select_generic || 'wdth.source_subinventory_code, ';
l_select_generic := l_select_generic || 'wdth.source_locator_id, ';
l_select_generic :=
l_select_generic
|| 'decode(milv.segment19, null, milv.concatenated_segments, null), ';
l_select_generic := l_select_generic || '6, '; --status_id
l_select_generic := l_select_generic || '6, '; --status_id_original
l_select_generic :=
l_select_generic || '''' || g_status_codes (6)
|| ''', '; --status
l_select_generic := l_select_generic || 'wdth.transaction_type_id, ';
l_select_generic :=
l_select_generic || 'wdth.transaction_action_id, ';
l_select_generic :=
l_select_generic || 'wdth.transaction_source_type_id, ';
l_select_generic :=
l_select_generic || 'mtst.transaction_source_type_name, ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic :=
l_select_generic || 'wdth.transfer_organization_id, ';
l_select_generic := l_select_generic || 'mp1.organization_code, ';
l_select_generic := l_select_generic || 'null, '; --to_subinventory
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'null, ';
l_select_generic := l_select_generic || 'wdth.transaction_uom_code, ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'wdth.user_task_type, ';
l_select_generic := l_select_generic || 'bso.operation_code, ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'null, '; --cartonization_lpn
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'null, ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'null, ';
l_select_generic := l_select_generic || 'wdth.lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn5.license_plate_number, ';
l_select_generic := l_select_generic || 'wdth.content_lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn3.license_plate_number, ';
l_select_generic := l_select_generic || 'wdth.transfer_lpn_id, ';
l_select_generic :=
l_select_generic || 'wlpn4.license_plate_number, ';
l_select_generic := l_select_generic || 'to_date(null), ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'wdth.priority, '; --priority
l_select_generic := l_select_generic || 'wdth.priority, ';
l_select_generic := l_select_generic || 'wdth.task_type, ';
l_select_generic :=
l_select_generic
|| 'decode(wdth.task_type,'
|| '1, '''
|| g_task_types (1)
|| ''', 2, '''
|| g_task_types (2)
|| ''', 3, '''
|| g_task_types (3)
|| ''', 4, '''
|| g_task_types (4)
|| ''', 5, '''
|| g_task_types (5)
|| ''', 6, '''
|| g_task_types (6)
|| ''', 7, '''
|| g_task_types (7)
|| ''', 8, '''
|| g_task_types (8)
|| '''), ';
l_select_generic := l_select_generic || 'to_date(null), ';
l_select_generic := l_select_generic || 'wdth.operation_plan_id, ';
l_select_generic :=
l_select_generic || 'wop.operation_plan_name, ';
l_select_generic := l_select_generic || 'to_number(null), '; --operation_sequence
l_select_generic := l_select_generic || 'wdth.op_plan_instance_id, ';
l_select_generic := l_select_generic || 'wdth.task_id, '; --task_id
l_select_generic := l_select_generic || 'wdth.person_id, ';
l_select_generic := l_select_generic || 'wdth.person_id, ';
l_select_generic := l_select_generic || 'pap.full_name, ';
l_select_generic := l_select_generic || 'wdth.effective_start_date, ';
l_select_generic := l_select_generic || 'wdth.effective_end_date, ';
l_select_generic := l_select_generic || 'wdth.person_resource_id, ';
l_select_generic := l_select_generic || 'br1.resource_code, ';
l_select_generic := l_select_generic || 'wdth.machine_resource_id, ';
l_select_generic := l_select_generic || 'br2.resource_code, ';
l_select_generic := l_select_generic || 'wdth.equipment_instance, ';
l_select_generic := l_select_generic || 'wdth.dispatched_time, ';
l_select_generic := l_select_generic || 'wdth.loaded_time, ';
l_select_generic := l_select_generic || 'wdth.drop_off_time, ';
l_select_generic := l_select_generic || 'to_date(null), ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || '''N'', '; --is_modified
l_select_generic := l_select_generic || 'wdth.secondary_transaction_uom_code, ';
l_select_generic := l_select_generic || 'to_number(null), ';
l_select_generic := l_select_generic || 'mmt.transaction_set_id ';
l_select_generic := l_select_generic || 'wdth.secondary_transaction_uom_code, ';
l_select_generic := l_select_generic || 'to_number(null) ';
RETURN l_select_generic;
debug('Entered into picknone select');
l_select_generic := NULL;
l_select_generic := 'SELECT ';
l_select_generic := l_select_generic || 'null, ';
l_select_generic :=
l_select_generic
|| 'decode(wdth.is_parent,'
|| '''Y'', decode(wdth.transaction_action_id,28,'''
|| g_plan_task_types (4)
|| ''','
|| 'decode(wdth.transaction_source_type_id,'
|| '5,decode(wdth.transaction_type_id,35,'''
|| g_plan_task_types (4)
|| ''','''
|| g_plan_task_types (3)
|| '''),'
|| '13, decode(wdth.transaction_type_id,51,'''
|| g_plan_task_types (4)
|| ''','''
|| g_plan_task_types (3)
|| '''),'''
|| g_plan_task_types (3)
|| ''')),'
|| 'decode(wdth.parent_transaction_id,null,''' --'N' or null
|| g_plan_task_types(1)
|| ''','
|| 'decode(wdth.transaction_action_id,28,'''
|| g_plan_task_types(5)
|| ''','
|| 'decode(wdth.transaction_source_type_id,'
|| '5,decode(wdth.transaction_type_id,35,'''
|| g_plan_task_types (5)
|| ''','''
|| g_plan_task_types (2)
|| '''),'
|| '13, decode(wdth.transaction_type_id,51,'''
|| g_plan_task_types (5)
|| ''','''
|| g_plan_task_types (2)
|| '''),'''
|| g_plan_task_types (2)
|| ''')))),';
l_select_generic := l_select_generic || 'wdth.transaction_temp_id, '; --transaction_temp_id
l_select_generic := l_select_generic || 'wdth.parent_transaction_id,'; --parent_line_id
l_select_generic := l_select_generic || 'wdth.inventory_item_id, '; --inventory_item_id
l_select_generic := l_select_generic || 'msiv.concatenated_segments, '; --item
l_select_generic := l_select_generic || 'msiv.description, '; --item description
l_select_generic := l_select_generic || 'msiv.unit_weight, '; --unit_weight
l_select_generic := l_select_generic || 'msiv.weight_uom_code, '; --weight_uom_code
l_select_generic := l_select_generic || 'msiv.unit_volume, '; --unit_volume
l_select_generic := l_select_generic || 'msiv.volume_uom_code, '; --volume_uom_code
l_select_generic := l_select_generic || 'wdth.organization_id, '; --organization_id
l_select_generic := l_select_generic || 'wdth.revision, '; --revision
l_select_generic := l_select_generic || 'wdth.source_subinventory_code, '; --subinventory
l_select_generic := l_select_generic || 'wdth.source_locator_id, '; --locator_id
l_select_generic := l_select_generic || 'decode(milv.segment19, null, milv.concatenated_segments, null), '; --locator
l_select_generic := l_select_generic || '13, '; --status_id
l_select_generic := l_select_generic || '13, '; --status_id_original
l_select_generic := l_select_generic || '''' || g_status_codes (13) || ''', '; --status
l_select_generic := l_select_generic || 'wdth.transaction_type_id, '; --transaction_type_id
l_select_generic := l_select_generic || 'wdth.transaction_action_id, '; --transaction_action_id
l_select_generic := l_select_generic || 'wdth.transaction_source_type_id, '; --transaction_source_type_id
l_select_generic := l_select_generic || 'mtst.transaction_source_type_name, '; --transaction_source_type
l_select_generic := l_select_generic || 'to_number(null), '; --transaction_source_id
l_select_generic := l_select_generic || 'to_number(null), '; --transaction_source_line_id
l_select_generic := l_select_generic || 'wdth.transfer_organization_id, '; --to_organization_id
l_select_generic := l_select_generic || 'mp1.organization_code, '; --to_organization_id
l_select_generic := l_select_generic || ' NVL(wdth.SUGGESTED_DEST_SUBINVENTORY,wdth.DEST_SUBINVENTORY_CODE) , '; --to_subinventory
l_select_generic := l_select_generic || ' NVL(wdth.SUGGESTED_DEST_LOCATOR_ID,wdth.DEST_LOCATOR_ID) , '; --'to_number(null), '; --to_locator_id
l_select_generic := l_select_generic || 'decode(milv1.segment19, null, milv1.concatenated_segments, null), ';
l_select_generic := l_select_generic || 'wdth.transaction_UOM_CODE, '; --jan28 'wdd.REQUESTED_QUANTITY_UOM, ' --transaction_uom
l_select_generic := l_select_generic || 'wdth.transaction_QUANTITY, '; --jan28 'wdd.REQUESTED_QUANTITY, ' --transaction_quantity
l_select_generic := l_select_generic || 'wdth.user_task_type, '; --user_task_type_id
l_select_generic := l_select_generic || 'bso.operation_code, '; --user_task_type
l_select_generic := l_select_generic || 'mtrl.line_id, '; --move_order_line_id
l_select_generic := l_select_generic || 'mtrl.pick_slip_number, '; --pick_slip_number
l_select_generic := l_select_generic || 'to_number(null), '; --cartonization_id
l_select_generic := l_select_generic || 'null, '; --cartonization_lpn
l_select_generic := l_select_generic || 'to_number(null), '; --allocated_lpn_id
l_select_generic := l_select_generic || 'null, ';
l_select_generic := l_select_generic || 'to_number(null), '; --container_item_id
l_select_generic := l_select_generic || 'null, ';
l_select_generic := l_select_generic || 'wdth.lpn_id, '; --from_lpn_id
l_select_generic := l_select_generic || 'wlpn5.license_plate_number, '; --from_lpn
l_select_generic := l_select_generic || 'wdth.content_lpn_id, '; --content_lpn_id
l_select_generic := l_select_generic || 'wlpn3.license_plate_number, '; --content_lpn
l_select_generic := l_select_generic || 'wdth.transfer_lpn_id, '; --to_lpn_id
l_select_generic := l_select_generic || 'wlpn4.license_plate_number, '; --to_lpn
l_select_generic := l_select_generic || 'wdth.LAST_UPDATE_DATE, '; --mmt_last_update_date
l_select_generic := l_select_generic || 'wdth.LAST_UPDATED_BY, '; --mmt_last_updated_by
l_select_generic := l_select_generic || 'wdth.priority, '; --priority
l_select_generic := l_select_generic || 'wdth.priority, '; --priority_original
l_select_generic := l_select_generic || 'wdth.task_type, '; --task_type_id
l_select_generic :=
l_select_generic
|| 'decode(wdth.task_type,'
|| '1, '''
|| g_task_types (1)
|| ''', 2, '''
|| g_task_types (2)
|| ''', 3, '''
|| g_task_types (3)
|| ''', 4, '''
|| g_task_types (4)
|| ''', 5, '''
|| g_task_types (5)
|| ''', 6, '''
|| g_task_types (6)
|| ''', 7, '''
|| g_task_types (7)
|| ''', 8, '''
|| g_task_types (8)
|| '''), ';
l_select_generic := l_select_generic || 'wdth.CREATION_DATE, '; --creation_time
l_select_generic := l_select_generic || 'wdth.operation_plan_id, '; --operation_plan_id
l_select_generic := l_select_generic || 'wop.operation_plan_name, '; --operation_plan
l_select_generic := l_select_generic || 'to_number(null), '; --operation_sequence
l_select_generic := l_select_generic || 'wdth.op_plan_instance_id, '; --op_plan_instance_id
l_select_generic := l_select_generic || 'wdth.task_id, '; --task_id
l_select_generic := l_select_generic || 'wdth.person_id, '; --person_id
l_select_generic := l_select_generic || 'wdth.person_id, '; --person_id_original
l_select_generic := l_select_generic || 'pap.full_name, '; --person_id
l_select_generic := l_select_generic || 'wdth.effective_start_date, '; --effective_start_date
l_select_generic := l_select_generic || 'wdth.effective_end_date, '; --effective_end_date
l_select_generic := l_select_generic || 'wdth.person_resource_id, '; --person_resource_id
l_select_generic := l_select_generic || 'br1.resource_code, '; --person_resource_code
l_select_generic := l_select_generic || 'wdth.machine_resource_id, '; --machine_resource_id
l_select_generic := l_select_generic || 'br2.resource_code, '; --machine_resource_code
l_select_generic := l_select_generic || 'wdth.equipment_instance, '; --equipment_instance
l_select_generic := l_select_generic || 'wdth.dispatched_time, '; --dispatched_time
l_select_generic := l_select_generic || 'wdth.loaded_time, '; --loaded_time
l_select_generic := l_select_generic || 'wdth.drop_off_time, '; --drop_off_time
l_select_generic := l_select_generic || 'to_date(null), '; --wdt_last_update_date
l_select_generic := l_select_generic || 'to_number(null), '; --wdt_last_updated_by
l_select_generic := l_select_generic || '''N'', '; --is_modified -- bug #5163661
l_select_generic := l_select_generic || 'wdth.secondary_transaction_uom_code, '; --sec_transaction_uom
l_select_generic := l_select_generic || 'wdth.SECONDARY_TRANSACTION_QUANTITY '; --sec_transaction_quantity
l_select_generic := l_select_generic || 'wdth.secondary_transaction_uom_code, '; --sec_transaction_uom
l_select_generic := l_select_generic || 'wdth.SECONDARY_TRANSACTION_QUANTITY '; --sec_transaction_quantity
debug('select for picknone'||l_select_generic);
RETURN l_select_generic;
END get_generic_select;
|| ', (select distinct parent_line_id from wms_waveplan_tasks_temp where task_type_id <> 3) wwtt';
'WHERE NOT exists (SELECT 1 FROM wms_waveplan_tasks_temp ';
|| 'AND NOT EXISTS (SELECT 1 FROM wms_dispatched_tasks ';
'WHERE NOT exists (SELECT 1 FROM wms_waveplan_tasks_temp ';
'WHERE NOT exists (SELECT 1 FROM wms_waveplan_tasks_temp ';
l_insert_query VARCHAR2 (2000);
l_select_generic VARCHAR2 (3000);
DELETE FROM wms_waveplan_tasks_temp
WHERE task_type_id <> 3
AND transaction_temp_id IN (
SELECT parent_line_id
FROM wms_waveplan_tasks_temp
WHERE parent_line_id IS NOT NULL
AND task_type_id <> 3
--Change
--Check for outbound and wip tasks below
AND ( transaction_action_id = 28
OR ( transaction_source_type_id = 5
AND transaction_type_id = 35
)
OR ( transaction_source_type_id = 13
AND transaction_type_id = 51
)
));
SELECT 1
INTO l_query_count
FROM DUAL
WHERE EXISTS (
SELECT parent_line_id
FROM wms_waveplan_tasks_temp
WHERE parent_line_id IS NOT NULL
AND task_type_id <> 3
--Change
--check for outbound and wip tasks below
AND ( transaction_action_id = 28
OR ( transaction_source_type_id = 5
AND transaction_type_id = 35
)
OR ( transaction_source_type_id = 13
AND transaction_type_id = 51
)
));
l_insert_query :=
get_generic_insert (p_is_unreleased => p_is_unreleased,
p_is_pending => p_is_pending,
p_is_queued => p_is_queued,
p_is_dispatched => p_is_dispatched,
p_is_active => p_is_active,
p_is_loaded => p_is_loaded,
p_is_completed => FALSE
);
l_insert_query := l_insert_query || ') ';
l_select_generic :=
get_generic_select (p_is_unreleased => p_is_unreleased,
p_is_pending => p_is_pending,
p_is_queued => p_is_queued,
p_is_dispatched => p_is_dispatched,
p_is_active => p_is_active,
p_is_loaded => p_is_loaded,
p_is_completed => FALSE,
p_populate_merged_tasks => TRUE
);
l_insert_query
|| l_select_generic
|| l_from_generic
|| l_where_generic;
DELETE FROM wms_waveplan_tasks_temp
WHERE parent_line_id IS NOT NULL
AND status_id <> 6
--Change
--check for outbound and wip tasks below
AND ( transaction_action_id = 28
OR ( transaction_source_type_id = 5
AND transaction_type_id = 35
)
OR ( transaction_source_type_id = 13
AND transaction_type_id = 51
)
)
AND parent_line_id <> transaction_temp_id;
SELECT 1
INTO l_query_count
FROM DUAL
WHERE EXISTS (
SELECT parent_line_id
FROM wms_waveplan_tasks_temp
WHERE parent_line_id IS NOT NULL
AND task_type_id <> 3
--Change
--check for outbound and wip tasks below
AND ( transaction_action_id = 28
OR ( transaction_source_type_id = 5
AND transaction_type_id = 35
)
OR ( transaction_source_type_id = 13
AND transaction_type_id = 51
)
)
--End of change
AND status_id = 6);
l_insert_query :=
get_generic_insert (p_is_unreleased => FALSE
--p_is_unreleased,
,
p_is_pending => FALSE --p_is_pending,
,
p_is_queued => FALSE --p_is_queued,
,
p_is_dispatched => FALSE
--p_is_dispatched,
,
p_is_active => FALSE --p_is_active,
,
p_is_loaded => FALSE --p_is_loaded,
,
p_is_completed => TRUE
);
l_insert_query := l_insert_query || ') ';
l_select_generic :=
get_generic_select (p_is_unreleased => FALSE
--p_is_unreleased,
,
p_is_pending => FALSE
--p_is_pending,
,
p_is_queued => FALSE
--p_is_queued,
,
p_is_dispatched => FALSE
--p_is_dispatched,
,
p_is_active => FALSE
--p_is_active,
,
p_is_loaded => FALSE
--p_is_loaded,
,
p_is_completed => TRUE,
p_populate_merged_tasks => TRUE
);
l_insert_query
|| l_select_generic
|| l_from_generic
|| l_where_generic;
l_insert_query VARCHAR2 (3000);
l_select_generic VARCHAR2 (4000);
l_select_outbound VARCHAR2 (4000);
select lpad(segment1,40), segment2,segment1
INTO l_from_tonum_mso_seg1,l_from_mso_seg2,l_from_mso_seg1
from mtl_sales_orders
WHERE sales_order_id = p_from_sales_order_id;
select lpad(segment1,40), segment2
INTO l_to_tonum_mso_seg1,l_to_mso_seg2
from mtl_sales_orders
WHERE sales_order_id = p_to_sales_order_id;
l_insert_query := NULL;
l_select_generic := NULL;
l_select_outbound := NULL;
l_select_generic := ' SELECT mmtt.wms_task_type, count(*) ';
l_select_generic := ' SELECT wdth.task_type, count(*) ';
l_insert_query :=
get_generic_insert (p_is_unreleased => l_is_unreleased,
p_is_pending => l_is_pending,
p_is_queued => l_is_queued,
p_is_dispatched => l_is_dispatched,
p_is_active => l_is_active,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
, p_is_picknone => l_is_picknone --ER13869750
);
l_insert_query := l_insert_query || ', customer_id ';
l_insert_query := l_insert_query || ', customer ';
l_insert_query := l_insert_query || ', ship_to_location_id ';
l_insert_query := l_insert_query || ', ship_to_country ';
l_insert_query := l_insert_query || ', ship_to_state ';
l_insert_query := l_insert_query || ', ship_to_postal_code ';
l_insert_query := l_insert_query || ', delivery ';
l_insert_query := l_insert_query || ', ship_method ';
l_insert_query := l_insert_query || ', carrier_id ';
l_insert_query := l_insert_query || ', carrier ';
l_insert_query := l_insert_query || ', wave_header_id ';
l_insert_query := l_insert_query || ', shipment_date ';
l_insert_query := l_insert_query || ', shipment_priority ';
l_insert_query := l_insert_query || ', source_header ';
l_insert_query := l_insert_query || ', line_number ';
l_insert_query := l_insert_query || ',trip_id';
l_insert_query := l_insert_query || ',trip_name';
l_insert_query := l_insert_query || ', load_seq_number ';
l_insert_query := l_insert_query || ') ';
l_select_generic :=
get_generic_select (p_is_unreleased => l_is_unreleased,
p_is_pending => l_is_pending,
p_is_queued => l_is_queued,
p_is_dispatched => l_is_dispatched,
p_is_active => l_is_active,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
,p_is_picknone => l_is_picknone --ER13869750
);
l_select_outbound := ', wdd.customer_id '; --customer_id
l_select_outbound := l_select_outbound || ', substr( hp.party_name, 1, 50 ) ';
l_select_outbound :=
l_select_outbound || ', wdd.ship_to_location_id ';
l_select_outbound := l_select_outbound || ', hl.country ';
l_select_outbound := l_select_outbound || ', hl.state ';
l_select_outbound := l_select_outbound || ', hl.postal_code ';
l_select_outbound := l_select_outbound || ', wnd.name ';
l_select_outbound :=l_select_outbound || ', INV_SHIPPING_TRANSACTION_PUB.GET_SHIPMETHOD_MEANING(Decode(wt.ship_method_code, NULL,
decode(wda.delivery_id,NULL,wdd.ship_method_code,decode(wnd.ship_method_code,NULL,wdd.ship_method_code,wnd.ship_method_code)), wt.ship_method_code))'; -- 4629955
l_select_outbound :=l_select_outbound || ', INV_SHIPPING_TRANSACTION_PUB.GET_SHIPMETHOD_MEANING(wdd.ship_method_code)';
l_select_outbound := l_select_outbound || ', Decode(wt.carrier_id, NULL, Decode(wda.delivery_id, NULL, wdd.carrier_id,
Decode(wnd.carrier_id,NULL, wdd.carrier_id, wnd.carrier_id)), wt.carrier_id)'; --bug 15874781
l_select_outbound := l_select_outbound || ', wdd.carrier_id ';
l_select_outbound := l_select_outbound || ',INV_SHIPPING_TRANSACTION_PUB.GET_FREIGHT_CODE(Decode(wt.carrier_id, NULL, Decode(wda.delivery_id, NULL, wdd.carrier_id,
Decode(wnd.carrier_id,NULL, wdd.carrier_id, wnd.carrier_id)), wt.carrier_id))'; --bug 15874781
l_select_outbound := l_select_outbound || ', INV_SHIPPING_TRANSACTION_PUB.GET_FREIGHT_CODE(wdd.carrier_id)';
l_select_outbound := l_select_outbound || ', to_number(null) ';
l_select_outbound := l_select_outbound || ', to_number(null) ';
l_select_outbound := l_select_outbound || ', to_number(null) ';
l_select_outbound := l_select_outbound || ', to_number(null) ';
l_select_outbound := l_select_outbound || ', wwl.wave_header_id ';
l_select_outbound := l_select_outbound || ', to_number(null) ';
l_select_outbound := l_select_outbound || ', wdd.date_scheduled ';
l_select_outbound :=
l_select_outbound || ', wdd.shipment_priority_code ';
l_select_outbound :=
l_select_outbound || ', wdd.source_header_number ';
l_select_outbound := l_select_outbound || ', wdd.source_line_number ';
l_select_outbound := l_select_outbound || ', wt.trip_id ';
l_select_outbound := l_select_outbound || ', wt.name ';
l_select_outbound := l_select_outbound || ',to_number(null) ';
l_select_outbound := l_select_outbound || ', to_number(null) ';
l_select_outbound := l_select_outbound || ', wdd.load_seq_number ';
|| ', (SELECT COUNT(line_id) line_sum, header_id FROM oe_order_lines_all ';
l_where_outbound := l_where_outbound || 'AND wdd.delivery_detail_id = (select delivery_detail_id from wsh_delivery_details_ob_grp_v ';
/* l_where_outbound := l_where_outbound || 'AND EXISTS ( SELECT 1 ';
l_where_outbound := l_where_outbound || ' AND wdd.delivery_detail_id IN (SELECT Min(wddi.delivery_detail_id) ';
l_where_outbound := l_where_outbound || ' AND EXISTS (SELECT 1 FROM oe_order_lines_all oel ';
l_where_outbound := l_where_outbound || ' ( SELECT line_id from oe_order_lines_all oel1 WHERE oel.header_id = oel1.header_id ';
|| 'AND EXISTS (SELECT 1 FROM '||
'WMS_DOCK_APPOINTMENTS_B WDAB, '||
'WSH_DELIVERY_LEGS WDL '||
'WHERE '||
'WDAB.TRIP_STOP = WDL.PICK_UP_STOP_ID AND '||
'WDL.DELIVERY_ID = WDA.DELIVERY_ID AND '||
'WDAB.APPOINTMENT_TYPE = 2 AND '|| -- OUTBOUND appt
'(WDAB.START_TIME - sysdate)'||n||' BETWEEN 0 AND :p_time_till_appt) ';
l_insert_query
|| l_select_generic
|| l_select_outbound
|| l_from_generic
|| l_from_outbound
|| l_where_generic
|| l_where_outbound;
l_insert_query VARCHAR2 (4000);
l_select_generic VARCHAR2 (4000);
l_insert_query := NULL;
l_select_generic := NULL;
l_select_generic := ' SELECT mmtt.wms_task_type, count(*) ';
l_select_generic := ' SELECT wdth.task_type, count(*) ';
l_insert_query :=
get_generic_insert (p_is_pending => l_is_pending,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
);
l_insert_query := l_insert_query || ', reference_id ';
l_insert_query := l_insert_query || ', reference ';
l_insert_query := l_insert_query || ', source_header ';
l_insert_query := l_insert_query || ', line_number ';
l_insert_query := l_insert_query || ') ';
l_select_generic :=
get_generic_select (p_is_pending => l_is_pending,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
);
l_select_generic := l_select_generic || ', mtrl.reference_id ';
l_select_generic := l_select_generic || ', mtrl.reference ';
l_select_generic :=
l_select_generic || ', decode(rt.source_document_code, ';
l_select_generic :=
l_select_generic || '''INVENTORY'', rt.shipment_line_id, ';
l_select_generic :=
l_select_generic || '''PO'', rt.po_line_location_id, ';
l_select_generic :=
l_select_generic || '''REQ'', rt.shipment_line_id, ';
l_select_generic :=
l_select_generic || '''RMA'', rt.oe_order_line_id) ';
l_select_generic :=
l_select_generic || ', decode(rt.source_document_code, ';
l_select_generic :=
l_select_generic || '''INVENTORY'', ''SHIPMENT_LINE_ID'', ';
l_select_generic :=
l_select_generic || '''PO'', ''PO_LINE_LOCATION_ID'', ';
l_select_generic :=
l_select_generic || '''REQ'', ''SHIPMENT_LINE_ID'', ';
l_select_generic :=
l_select_generic || '''RMA'', ''ORDER_LINE_ID'') ';
l_select_generic := l_select_generic || ', ph.segment1 ';
l_select_generic := l_select_generic || ', pl.line_num ';
l_select_generic := l_select_generic || ', ooh.order_number ';
l_select_generic := l_select_generic || ', ool.line_number ';
l_select_generic := l_select_generic || ', prh.segment1 ';
l_select_generic := l_select_generic || ', prl.line_num ';
l_insert_query
|| l_select_generic
|| l_from_generic
|| l_from_inbound
|| l_where_generic
|| l_where_inbound;
l_insert_query VARCHAR2 (3000);
l_select_manufacturing VARCHAR2 (3000);
l_select_generic VARCHAR2 (3000);
l_insert_query := NULL;
l_select_generic := NULL;
l_select_manufacturing := NULL;
l_select_generic := ' SELECT mmtt.wms_task_type, count(*) ';
l_select_generic := ' SELECT wdth.task_type, count(*) ';
l_insert_query :=
get_generic_insert (p_is_unreleased => l_is_unreleased,
p_is_pending => l_is_pending,
p_is_queued => l_is_queued,
p_is_dispatched => l_is_dispatched,
p_is_active => l_is_active,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
,p_is_picknone => l_is_picknone --ER13869750
);
l_insert_query := l_insert_query || ', assembly_id ';
l_insert_query := l_insert_query || ', assembly ';
l_insert_query := l_insert_query || ', line_id ';
l_insert_query := l_insert_query || ', line ';
l_insert_query := l_insert_query || ', department_id ';
l_insert_query := l_insert_query || ', department ';
l_insert_query := l_insert_query || ', source_header ';
l_insert_query := l_insert_query || ', wip_entity_type ';
l_insert_query := l_insert_query || ', wip_entity_id ';
/* Navin INV_Convergence_Gme_Wms_TD REPLACED: l_insert_query := l_insert_query || ') '; */
l_insert_query := l_insert_query || ', primary_product ';
l_insert_query := l_insert_query || ') ';
l_insert_query := l_insert_query || ') ';
l_select_generic :=
get_generic_select (p_is_unreleased => l_is_unreleased,
p_is_pending => l_is_pending,
p_is_queued => l_is_queued,
p_is_dispatched => l_is_dispatched,
p_is_active => l_is_active,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
,p_is_picknone => l_is_picknone --ER13869750
);
l_select_manufacturing := ', wdj.primary_item_id ';
l_select_manufacturing :=
l_select_manufacturing
|| ', msiv2.concatenated_segments '; -- assembly
l_select_manufacturing :=
l_select_manufacturing || ', wl.line_id ';
l_select_manufacturing :=
l_select_manufacturing || ', wl.line_code ';
l_select_manufacturing :=
l_select_manufacturing || ', wro.department_id ';
l_select_manufacturing :=
l_select_manufacturing || ', bd.department_code ';
l_select_manufacturing :=
l_select_manufacturing || ', we.wip_entity_name ';
l_select_manufacturing :=
l_select_manufacturing || ', to_number(1) ';
l_select_manufacturing :=
l_select_manufacturing || ', wdj.wip_entity_id ';
l_select_manufacturing := ', wfs.primary_item_id ';
l_select_manufacturing :=
l_select_manufacturing
|| ', msiv2.concatenated_segments '; -- assembly
l_select_manufacturing :=
l_select_manufacturing || ', wl.line_id ';
l_select_manufacturing :=
l_select_manufacturing || ', wl.line_code ';
l_select_manufacturing :=
l_select_manufacturing || ', bos.department_id ';
l_select_manufacturing :=
l_select_manufacturing || ', bd.department_code ';
l_select_manufacturing :=
l_select_manufacturing || ', wfs.schedule_number ';
l_select_manufacturing :=
l_select_manufacturing || ',to_number(2) ';
l_select_manufacturing :=
l_select_manufacturing || ', wfs.wip_entity_id ';
l_select_manufacturing := ', wri.primary_item_id ';
l_select_manufacturing :=
l_select_manufacturing
|| ', msiv2.concatenated_segments '; -- assembly
l_select_manufacturing :=
l_select_manufacturing || ', wl.line_id ';
l_select_manufacturing :=
l_select_manufacturing || ', wl.line_code ';
l_select_manufacturing :=
l_select_manufacturing || ', wro.department_id ';
l_select_manufacturing :=
l_select_manufacturing || ', bd.department_code ';
l_select_manufacturing :=
l_select_manufacturing || ', we.wip_entity_name ';
l_select_manufacturing :=
l_select_manufacturing || ', to_number(3) ';
l_select_manufacturing :=
l_select_manufacturing || ', wrs.wip_entity_id ';
l_select_manufacturing := ', TO_NUMBER (NULL) '; -- Inventory item Identifier
l_select_manufacturing :=
l_select_manufacturing
|| ', NULL '; -- assembly
l_select_manufacturing :=
l_select_manufacturing || ', TO_NUMBER (NULL) ';
l_select_manufacturing :=
l_select_manufacturing || ', NULL ';
l_select_manufacturing :=
l_select_manufacturing || ', TO_NUMBER (NULL) ';
l_select_manufacturing :=
l_select_manufacturing || ', NULL ';
l_select_manufacturing :=
l_select_manufacturing || ', h.batch_no ';
l_select_manufacturing :=
l_select_manufacturing || ', to_number(10) ';
l_select_manufacturing :=
l_select_manufacturing || ', h.batch_id ';
l_select_manufacturing :=
l_select_manufacturing || ', msiv2.concatenated_segments ';
l_select_manufacturing := NULL; -- for summary mode we wont need the manufacturing related data
l_insert_query
|| l_select_generic
|| l_select_manufacturing
|| l_from_generic
|| l_from_manufacturing
|| l_where_generic
|| l_where_manufacturing;
l_select_cc VARCHAR2 (4000);
l_insert_cc VARCHAR2 (4000);
l_insert_cc := NULL;
l_select_cc := NULL;
l_insert_cc := 'INSERT INTO wms_waveplan_tasks_temp( ';
l_insert_cc := l_insert_cc || 'transaction_temp_id ';
l_insert_cc := l_insert_cc || ', inventory_item_id ';
l_insert_cc := l_insert_cc || ', item ';
l_insert_cc := l_insert_cc || ', item_description ';--Added for Bug 8540985
l_insert_cc := l_insert_cc || ', organization_id ';
l_insert_cc := l_insert_cc || ', revision ';
l_insert_cc := l_insert_cc || ', subinventory ';
l_insert_cc := l_insert_cc || ', locator_id ';
l_insert_cc := l_insert_cc || ', locator ';
l_insert_cc := l_insert_cc || ', status_id ';
l_insert_cc := l_insert_cc || ', status_id_original ';
l_insert_cc := l_insert_cc || ', status ';
l_insert_cc := l_insert_cc || ', transaction_action_id ';
l_insert_cc := l_insert_cc || ', transaction_source_type_id ';
l_insert_cc := l_insert_cc || ', transaction_source_type ';
l_insert_cc := l_insert_cc || ', user_task_type_id ';
l_insert_cc := l_insert_cc || ', user_task_type ';
l_insert_cc := l_insert_cc || ', mmtt_last_update_date ';
l_insert_cc := l_insert_cc || ', mmtt_last_updated_by ';
l_insert_cc := l_insert_cc || ', priority ';
l_insert_cc := l_insert_cc || ', priority_original ';
l_insert_cc := l_insert_cc || ', task_type_id ';
l_insert_cc := l_insert_cc || ', task_type ';
l_insert_cc := l_insert_cc || ', creation_time ';
l_insert_cc := l_insert_cc || ', task_id ';
l_insert_cc := l_insert_cc || ', person_id ';
l_insert_cc := l_insert_cc || ', person_id_original ';
l_insert_cc := l_insert_cc || ', person ';
l_insert_cc := l_insert_cc || ', effective_start_date ';
l_insert_cc := l_insert_cc || ', effective_end_date ';
l_insert_cc := l_insert_cc || ', person_resource_id ';
l_insert_cc := l_insert_cc || ', person_resource_code ';
l_insert_cc := l_insert_cc || ', machine_resource_id ';
l_insert_cc := l_insert_cc || ', machine_resource_code ';
l_insert_cc := l_insert_cc || ', equipment_instance ';
l_insert_cc := l_insert_cc || ', dispatched_time ';
l_insert_cc := l_insert_cc || ', loaded_time ';
l_insert_cc := l_insert_cc || ', drop_off_time ';
l_insert_cc := l_insert_cc || ', wdt_last_update_date ';
l_insert_cc := l_insert_cc || ', wdt_last_updated_by ';
l_insert_cc := l_insert_cc || ', is_modified ';
l_insert_cc := l_insert_cc || ', source_header ';
l_insert_cc := l_insert_cc || ') ';
l_select_cc := 'SELECT ';
l_select_cc := l_select_cc || 'mcce.cycle_count_entry_id, ';
l_select_cc := l_select_cc || 'mcce.inventory_item_id, ';
l_select_cc := l_select_cc || 'msiv.concatenated_segments, '; --item
l_select_cc := l_select_cc || 'msiv.description, '; -- Added for Bug 8540985
l_select_cc := l_select_cc || 'mcce.organization_id, ';
l_select_cc := l_select_cc || 'mcce.revision, '; --revision
l_select_cc := l_select_cc || 'mcce.subinventory, '; --subinventory
l_select_cc := l_select_cc || 'mcce.locator_id, '; --locator_id
l_select_cc :=
l_select_cc
|| 'decode(milv.segment19, null, milv.concatenated_segments, null), ';
l_select_cc := l_select_cc || 'decode(wdt.status, null, 1, wdt.status), ';
l_select_cc := l_select_cc || 'decode(wdt.status, null, 1, wdt.status), ';
l_select_cc :=
l_select_cc
|| 'decode(decode(wdt.status, null, 1, wdt.status),'
|| '1, '''
|| g_status_codes (1)
|| ''', 2, '''
|| g_status_codes (2)
|| ''', 3, '''
|| g_status_codes (3)
|| ''', 4, '''
|| g_status_codes (4)
|| ''', 5, '''
|| g_status_codes (5)
|| ''', 6, '''
|| g_status_codes (6)
|| ''', 7, '''
|| g_status_codes (7)
|| ''', 8, '''
|| g_status_codes (8)
|| ''', '
|| '9, '''
|| g_status_codes (9)
|| '''), ';
l_select_cc := l_select_cc || '1, ';
l_select_cc := l_select_cc || '1, ';
l_select_cc :=
l_select_cc || '''' || g_status_codes (1)
|| ''', ';
l_select_cc := l_select_cc || '6, '; --status_id
l_select_cc := l_select_cc || '6, '; --status_id_original
l_select_cc := l_select_cc || '''' || g_status_codes (6)
|| ''', '; --status
l_select_cc := l_select_cc || '4, '; --transaction_action_id
l_select_cc := l_select_cc || '9, '; --transaction_source_type_id
l_select_cc :=
l_select_cc || 'mtst.transaction_source_type_name, ';
l_select_cc := l_select_cc || 'mcce.standard_operation_id, ';
l_select_cc := l_select_cc || 'bso.operation_code, ';
l_select_cc := l_select_cc || 'mcce.last_update_date, ';
l_select_cc := l_select_cc || 'mcce.last_updated_by, ';
l_select_cc := l_select_cc || 'mcce.task_priority, '; --priority
l_select_cc := l_select_cc || 'mcce.task_priority, ';
l_select_cc := l_select_cc || '3, '; --task_type_id
l_select_cc := l_select_cc || '''' || g_task_types (3) || ''', ';
l_select_cc := l_select_cc || 'mcce.creation_date, '; --creation_time
l_select_cc := l_select_cc || 'wdt.task_id, '; --task_id
l_select_cc := l_select_cc || 'wdt.person_id, '; --person_id
l_select_cc := l_select_cc || 'wdt.person_id, ';
l_select_cc := l_select_cc || 'pap.full_name, '; --person_id
l_select_cc := l_select_cc || 'wdt.effective_start_date, ';
l_select_cc := l_select_cc || 'wdt.effective_end_date, ';
l_select_cc := l_select_cc || 'wdt.person_resource_id, ';
l_select_cc := l_select_cc || 'br1.resource_code, ';
l_select_cc := l_select_cc || 'wdt.machine_resource_id, ';
l_select_cc := l_select_cc || 'br2.resource_code, ';
l_select_cc := l_select_cc || 'wdt.equipment_instance, ';
l_select_cc := l_select_cc || 'wdt.dispatched_time, ';
l_select_cc := l_select_cc || 'wdt.loaded_time, '; --loaded_time
l_select_cc := l_select_cc || 'wdt.drop_off_time, ';
l_select_cc := l_select_cc || 'wdt.last_update_date, ';
l_select_cc := l_select_cc || 'wdt.last_updated_by, ';
l_select_cc := l_select_cc || '''N'' '; -- is_modified
l_select_cc := l_select_cc || ', mcch.cycle_count_header_name ';
'WHERE NOT exists (SELECT 1 FROM wms_waveplan_tasks_temp ';
so that when selecting min(mcce.cycle_count_entry_id)all the columns need not be
in gruop by clause of outer query which is causing many issues.*/
l_where_cc := l_where_cc || ' and mcce.cycle_count_entry_id IN
(SELECT MIN(mcce.cycle_count_entry_id) from mtl_cycle_count_entries mcce , mtl_cycle_count_headers mcch
WHERE mcce.organization_id = :org_id
and mcce.cycle_count_header_id = mcch.cycle_count_header_id ' ;
|| 'AND NOT EXISTS (SELECT 1 FROM wms_dispatched_tasks ';
l_query := l_insert_cc || l_select_cc || l_from_cc || l_where_cc ;
l_query := l_select_cc || l_from_cc || l_where_cc ;
l_insert_query VARCHAR2 (4000);
l_select_generic VARCHAR2 (4000);
l_select_repl VARCHAR2 (4000);
l_insert_query := NULL;
l_select_generic := NULL;
l_select_repl := NULL;
l_select_generic := ' SELECT mmtt.wms_task_type, count(*) ';
l_select_generic := ' SELECT wdth.task_type, count(*) ';
l_insert_query :=
get_generic_insert (p_is_unreleased => l_is_unreleased,
p_is_pending => l_is_pending,
p_is_queued => l_is_queued,
p_is_dispatched => l_is_dispatched,
p_is_active => l_is_active,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
, p_is_picknone => l_is_picknone --ER13869750
);
l_insert_query := l_insert_query || ', source_header ';
l_insert_query := l_insert_query || ', line_number '; --line_number
l_insert_query := l_insert_query || ') ';
l_select_generic :=
get_generic_select (p_is_unreleased => l_is_unreleased,
p_is_pending => l_is_pending,
p_is_queued => l_is_queued,
p_is_dispatched => l_is_dispatched,
p_is_active => l_is_active,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
,p_is_picknone => l_is_picknone --ER13869750
);
l_select_repl := ', mtrh.request_number '; --source_header
l_select_repl := l_select_repl || ', mtrl.line_number ';
l_insert_query
|| l_select_generic
|| l_select_repl
|| l_from_generic
|| l_from_repl
|| l_where_generic
|| l_where_repl;
l_insert_query VARCHAR2 (4000);
l_select_generic VARCHAR2 (4000);
l_select_repl VARCHAR2 (4000);
l_insert_query := NULL;
l_select_generic := NULL;
l_insert_query :=
get_generic_insert (p_is_unreleased => l_is_unreleased,
p_is_pending => l_is_pending,
p_is_queued => l_is_queued,
p_is_dispatched => l_is_dispatched,
p_is_active => l_is_active,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
,p_is_picknone => l_is_picknone --ER13869750
);
l_insert_query := l_insert_query || ') ';
l_select_generic :=
get_generic_select (p_is_unreleased => l_is_unreleased,
p_is_pending => l_is_pending,
p_is_queued => l_is_queued,
p_is_dispatched => l_is_dispatched,
p_is_active => l_is_active,
p_is_loaded => l_is_loaded,
p_is_completed => l_is_completed
,p_is_picknone => l_is_picknone --ER13869750
);
l_insert_query
|| l_select_generic
|| l_from_generic
|| l_where_generic;
l_parent_inserted NUMBER := 0;
DELETE FROM wms_waveplan_tasks_temp;
x_non_complete_parent => l_parent_inserted
);
as Employee does not hold good for Pending tasks. Hence delete the unwanted records from WWTT */
IF ( p_is_queued OR p_is_dispatched OR p_is_active OR p_is_loaded OR p_is_completed
AND ( p_is_pending OR p_is_unreleased ) ) AND ( p_person_id IS NOT NULL OR
p_person_resource_id IS NOT NULL OR
p_equipment_type_id IS NOT NULL OR
p_machine_resource_id IS NOT NULL OR
p_machine_instance IS NOT NULL )
THEN
IF (p_is_pending) THEN
l_is_pending := FALSE;
DELETE FROM wms_waveplan_tasks_temp
WHERE status_id = 8;
debug('Done populating merged tasks. Delete unwanted tasks','query_tasks');
DELETE FROM wms_waveplan_tasks_temp
WHERE status_id = 1;
/* Bug - 5446146 If a user task type has been entered, delete the records which are not associated with a user task type */
IF p_user_task_type_id IS NOT NULL THEN
DELETE FROM wms_waveplan_tasks_temp
WHERE user_task_type_id IS NULL;
IF g_num_of_child_tasks_visible = 'T' AND l_parent_inserted > 0
THEN
--calculate number of children associated with parent tasks
set_num_of_child_tasks;
SELECT wms_task_dispatch_device.get_eligible_device
(mmtt.organization_id,
mmtt.subinventory_code,
mmtt.locator_id
)
INTO p_device_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_temp_id;
SELECT force_sign_on_flag
INTO l_force_sign_on
FROM wms_devices_b
WHERE device_id = p_device_id;
PROCEDURE update_task (
p_transaction_temp_id IN wms_waveplan_tasks_pvt.transaction_temp_table_type,
p_task_type_id IN wms_waveplan_tasks_pvt.task_type_id_table_type,
p_employee IN VARCHAR2,
p_employee_id IN NUMBER,
p_user_task_type IN VARCHAR2,
p_user_task_type_id IN NUMBER,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE,
p_person_resource_id IN NUMBER,
p_person_resource_code IN VARCHAR2,
p_force_employee_change IN BOOLEAN,
p_to_status IN VARCHAR2,
p_to_status_id IN NUMBER,
p_update_priority_type IN VARCHAR2,
p_update_priority IN NUMBER,
p_clear_priority IN VARCHAR2,
x_result OUT NOCOPY wms_waveplan_tasks_pvt.result_table_type,
x_message OUT NOCOPY wms_waveplan_tasks_pvt.message_table_type,
x_task_id OUT NOCOPY wms_waveplan_tasks_pvt.task_id_table_type,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
)
IS
l_task_id NUMBER;
IF g_cannot_update_putaway IS NULL
THEN
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_PUTAWAY_TASK');
g_cannot_update_putaway := fnd_message.get;
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_STAGING_MOVE');
g_cannot_update_staging_move := fnd_message.get;
fnd_message.set_name ('WMS', 'WMS_TASK_UPDATED');
g_task_updated := fnd_message.get;
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'X'
WHERE transaction_temp_id = p_transaction_temp_id (i)
AND task_type_id = p_task_type_id (i);
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error =
DECODE (task_type_id,
2, g_cannot_update_putaway,
7, g_cannot_update_staging_move
)
WHERE RESULT = 'X' AND task_type_id IN (2, 7);
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error = g_cannot_unrelease_cc
WHERE RESULT = 'X' AND task_type_id = 3;
SELECT transaction_temp_id, task_type_id, status
BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses
FROM wms_waveplan_tasks_temp
WHERE RESULT = 'X'
AND NOT ( (status_id = 8 AND p_to_status_id IN (1, 2)
) -- Unreleased to pending or queued
OR (status_id = 1 AND p_to_status_id IN (2, 8)
) -- Pending to queued or unreleased
OR (status_id = 2 AND p_to_status_id IN (1, 8)
) -- Queued to pending or unreleased
OR (status_id = 9 AND p_to_status_id IN (1, 8)
) -- R12:Active to Pending or unreleased
OR (status_id = 3 AND p_to_status_id IN (1, 8)
) -- R12:Dispatched to Pending or unreleased
OR (status_id = p_to_status_id)
); -- No Status Change
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_STATUS');
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error = l_messages (i)
WHERE transaction_temp_id = l_transaction_temp_ids (i)
AND task_type_id = l_task_type_ids (i);
SELECT wwtt.transaction_temp_id, wwtt.task_type_id, wwtt.status
BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses
FROM wms_waveplan_tasks_temp wwtt, mtl_material_transactions_temp mmtt, WMS_DISPATCHED_TASKS wdt
WHERE wwtt.transaction_temp_id = wdt.transaction_temp_id AND
wwtt.transaction_temp_id = mmtt.transaction_temp_id AND
wwtt.RESULT = 'X' AND
wwtt.status_id = 3 AND
EXISTS ( SELECT 1 FROM WMS_DISPATCHED_TASKS wdt2
WHERE wdt2.person_id = wwtt.person_id AND
wdt2.status = 9 AND
wdt2.task_method IS NOT NULL AND
wdt2.transaction_temp_id IN( SELECT transaction_temp_id FROM mtl_material_transactions_temp mmtt1
WHERE DECODE(wdt.TASK_METHOD,
'CARTON', mmtt1.cartonization_id,
'PICK_SLIP', mmtt1.pick_slip_number,
'DISCRETE', mmtt1.pick_slip_number,
mmtt1.transaction_source_id) = DECODE(wdt.TASK_METHOD,
'CARTON', mmtt.cartonization_id,
'PICK_SLIP', mmtt.pick_slip_number,
'DISCRETE', mmtt.pick_slip_number,
mmtt.transaction_source_id))
);
fnd_message.set_name ('WMS', 'WMS_GROUP_TASKS_CANNOT_UPDATE');
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error = 'This group of tasks is currently being worked, cannot change status'
WHERE transaction_temp_id = l_transaction_temp_ids (i)
AND task_type_id = l_task_type_ids (i);
SELECT transaction_temp_id, task_type_id, status
BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses
FROM wms_waveplan_tasks_temp wwtt
WHERE RESULT = 'X' AND
status_id = 9 AND
EXISTS ( SELECT 1 FROM MTL_MOBILE_LOGIN_HIST MMLH, WMS_DISPATCHED_TASKS WDT
WHERE WDT.TRANSACTION_TEMP_ID = WWTT.TRANSACTION_TEMP_ID AND
MMLH.USER_ID = WDT.LAST_UPDATED_BY AND
MMLH.LOGOFF_DATE IS NULL AND
MMLH.EVENT_MESSAGE IS NULL
);
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error = 'This task is currently being worked, cannot change status'
WHERE transaction_temp_id = l_transaction_temp_ids (i)
AND task_type_id = l_task_type_ids (i);
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_EMPLOYEE');
UPDATE wms_waveplan_tasks_temp wwtt
SET RESULT = 'E',
error = l_message
WHERE RESULT = 'X'
AND NOT EXISTS (
SELECT 1
FROM bom_std_op_resources bsor,
bom_resource_employees bre
WHERE wwtt.user_task_type_id = bsor.standard_operation_id
AND bsor.resource_id = bre.resource_id
AND bre.person_id = p_employee_id);
SELECT transaction_temp_id
BULK COLLECT INTO l_transaction_temp_ids_temp
FROM wms_waveplan_tasks_temp
WHERE RESULT = 'X';
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_EMPLOYEE');
DEBUG ('l_device_id : ' || l_device_id, ' Update_TAsk :');
' Update_TAsk :'
);
UPDATE wms_waveplan_tasks_temp wwtt
SET RESULT = 'E',
error = l_message
WHERE transaction_temp_id = l_transaction_temp_ids_temp (i)
AND NOT EXISTS (
SELECT 1
FROM wms_device_assignment_temp
WHERE device_id = l_device_id
AND employee_id = p_employee_id);
UPDATE wms_waveplan_tasks_temp wwtt
SET person_resource_id =
(SELECT bre.resource_id
FROM bom_std_op_resources bsor, bom_resource_employees bre
WHERE wwtt.user_task_type_id = bsor.standard_operation_id
AND bsor.resource_id = bre.resource_id
AND bre.person_id = wwtt.person_id
AND ROWNUM < 2)
WHERE RESULT = 'X';
UPDATE wms_waveplan_tasks_temp wwtt
SET RESULT = 'E',
error = l_message
WHERE RESULT = 'X' AND (status_id NOT IN (1, 2, 3, 8, 9) AND p_to_status_id IN (1, 8))
RETURNING transaction_temp_id, task_type_id, status
BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses;
UPDATE wms_waveplan_tasks_temp wwtt
SET RESULT = 'E',
error = l_message
WHERE RESULT = 'X' AND status_id NOT IN (1, 8)
RETURNING transaction_temp_id, task_type_id, status
BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses;
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_USER_TASK_TYPE');
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error = l_messages (i)
WHERE transaction_temp_id = l_transaction_temp_ids (i)
AND task_type_id = l_task_type_ids (i);
IF (p_update_priority_type IS NOT NULL AND p_update_priority IS NOT NULL) OR p_clear_priority = 'Y'
THEN
-- R12: Can update priority if task is dispatched, active IF
-- Dispatched or Active tasks are in the process of getting updated to pending or Unreleased
IF p_to_status_id IS NOT NULL THEN
UPDATE wms_waveplan_tasks_temp wwtt
SET RESULT = 'E',
error = l_message
WHERE RESULT = 'X' AND (status_id NOT IN (1, 2, 3, 8, 9) AND p_to_status_id IN (1, 8))
RETURNING transaction_temp_id, task_type_id, status
BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses;
UPDATE wms_waveplan_tasks_temp wwtt
SET RESULT = 'E',
error = l_message
WHERE RESULT = 'X' AND status_id NOT IN (1, 2, 8)
RETURNING transaction_temp_id, task_type_id, status
BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses;
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_PRIORITY');
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error = l_messages (i)
WHERE transaction_temp_id = l_transaction_temp_ids (i)
AND task_type_id = l_task_type_ids (i);
UPDATE wms_waveplan_tasks_temp
SET task_id = NVL (task_id, wms_dispatched_tasks_s.NEXTVAL),
status = p_to_status,
status_id = p_to_status_id,
priority =
DECODE (p_clear_priority,
'Y', NULL,
DECODE(p_update_priority_type,
'I', NVL(priority,0) + p_update_priority, -- R12: Increment priority
'D', DECODE(SIGN(NVL(priority,0) - p_update_priority),-- R12: Decrement priority
-1, 0,
+1, NVL(priority,0) - p_update_priority,
0, 0),
'S', NVL(p_update_priority, priority), -- R12: Set priority
priority)
),
person = p_employee,
person_id = p_employee_id,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
person_resource_code = p_person_resource_code,
person_resource_id = p_person_resource_id,
RESULT = 'S',
error = g_task_updated,
is_modified = 'Y'
WHERE RESULT = 'X';
DEBUG ('Else Part ______________________: ', ' Update_TAsk :');
UPDATE wms_waveplan_tasks_temp
SET task_id = DECODE (p_to_status_id, 1, NULL, 8, NULL, task_id),
status = NVL (p_to_status, status),
status_id = NVL (p_to_status_id, status_id),
user_task_type = NVL (p_user_task_type, user_task_type), -- R12: Assign User Task Type
user_task_type_id = NVL (p_user_task_type_id, user_task_type_id),-- R12: Assign User Task Type
priority =
DECODE (p_clear_priority,
'Y', NULL,
DECODE(p_update_priority_type,
'I', NVL(priority,0) + p_update_priority, -- R12: Increment priority
'D', DECODE(SIGN(NVL(priority,0) - p_update_priority),-- R12: Decrement priority
-1, 0,
+1, NVL(priority,0) - p_update_priority,
0, 0),
'S', NVL(p_update_priority, priority), -- R12: Set Constant priority value
priority)
),
person = DECODE (p_to_status_id, 1, NULL, 8, NULL, person),
person_id =
DECODE (p_to_status_id,
1, NULL,
8, NULL,
person_id
),
effective_start_date =
DECODE (p_to_status_id,
1, NULL,
8, NULL,
effective_start_date
),
effective_end_date =
DECODE (p_to_status_id,
1, NULL,
8, NULL,
effective_end_date
),
person_resource_code =
DECODE (p_to_status_id,
1, NULL,
8, NULL,
person_resource_code
),
person_resource_id =
DECODE (p_person_resource_id,
1, NULL,
8, NULL,
person_resource_id
),
RESULT = 'S',
error = g_task_updated,
is_modified = 'Y'
WHERE RESULT = 'X';
' Update_TAsk :'
);
UPDATE wms_waveplan_tasks_temp
SET RESULT = RESULT
WHERE transaction_temp_id = p_transaction_temp_id (i)
AND task_type_id = p_task_type_id (i)
RETURNING task_id, RESULT, error
BULK COLLECT INTO x_task_id, x_result, x_message;
DEBUG ('Sql Error: ' || SQLERRM, 'Task Planning.Task Update');
END update_task;
/*IF g_cannot_update_putaway IS NULL
THEN
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_PUTAWAY_TASK');
g_cannot_update_putaway := fnd_message.get;
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_STAGING_MOVE');
g_cannot_update_staging_move := fnd_message.get;
fnd_message.set_name ('WMS', 'WMS_TASK_UPDATED');
g_task_updated := fnd_message.get;
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'X'
WHERE transaction_temp_id = p_transaction_temp_id (i)
AND task_type_id = p_task_type_id (i);
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error =
DECODE (task_type_id,
1, 'Can not cancel pick task',
3, 'Can not cancel cycle count task',
4, 'Can not cancel replenish task',
5, 'Can not cancel move order transfer task',
6, 'Can not cancel move order issue task',
7, 'Can not cancel staging move task'
)
WHERE RESULT = 'X' AND task_type_id <> 2;
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error =
DECODE (task_type_id,
2, 'Can not cancel putaway task'
)
WHERE RESULT = 'X' AND task_type_id = 2;
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error = 'Can not cancel completed tasks'
WHERE RESULT = 'X' AND status_id = 6;
UPDATE wms_waveplan_tasks_temp
SET status_id = 12,
status = g_status_codes(12),
is_modified = 'Y',
RESULT = 'S',
error = 'Task Canceled'
WHERE RESULT = 'X';
UPDATE wms_waveplan_tasks_temp
SET RESULT = RESULT
WHERE transaction_temp_id = p_transaction_temp_id (i)
AND task_type_id = p_task_type_id (i)
RETURNING task_id, RESULT, error
BULK COLLECT INTO x_task_id, x_result, x_message;
DELETE FROM wms_waveplan_tasks_temp
WHERE transaction_temp_id = p_transaction_temp_id (i);
TYPE user_task_type_id IS TABLE OF wms_waveplan_tasks_temp.user_task_type_id%TYPE -- R12: Update User Task Type Id
INDEX BY BINARY_INTEGER;
l_user_task_type_id user_task_type_id; -- R12: Update User Task Type Id
l_update_date DATE;
SELECT transaction_temp_id, task_type_id, mmtt_last_updated_by,
mmtt_last_update_date, wdt_last_updated_by,
wdt_last_update_date, person_id, person_id_original,person_resource_id,/* Bug 5630187 - Added person_resource_id */
effective_start_date, effective_end_date, status_id,
status_id_original, priority, priority_original, user_task_type_id, num_of_child_tasks -- R12: Added User Task Type Id
FROM wms_waveplan_tasks_temp wwtt
WHERE wwtt.is_modified = 'Y';
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE parent_line_id = trx_temp_id
AND transaction_temp_id <> trx_temp_id
FOR UPDATE nowait;
IF p_task_action = 'U' THEN -- Update tasks
FOR rec_wwtt IN cur_wwtt
LOOP
BEGIN
IF rec_wwtt.task_type_id IN (2, 8) AND rec_wwtt.status_id = 12 THEN
if l_debug = 1 then
DEBUG ('Cancelled Plan');
UPDATE wms_waveplan_tasks_temp
SET is_modified = 'N',
RESULT = 'S',
error = g_plan_cancelled
WHERE transaction_temp_id = rec_wwtt.transaction_temp_id;
SELECT mmtt.transaction_temp_id
INTO l_transaction_temp_id_table (i)
FROM mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id =
rec_wwtt.transaction_temp_id
AND mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
AND mmtt.wms_task_type = wdt.task_type(+)
AND DECODE (wdt.status,
NULL, NVL (mmtt.wms_task_status, 1),
wdt.status
) = NVL (rec_wwtt.status_id_original, -1)
AND NVL (mmtt.task_priority, -1) =
NVL (rec_wwtt.priority_original,
-1)
AND NVL (wdt.person_id, -1) =
NVL (rec_wwtt.person_id_original,
-1)
AND mmtt.last_updated_by = rec_wwtt.mmtt_last_updated_by
AND mmtt.last_update_date =
rec_wwtt.mmtt_last_update_date
AND NVL (wdt.last_updated_by, -1) =
NVL (rec_wwtt.wdt_last_updated_by,
-1)
AND ( wdt.last_update_date =
rec_wwtt.wdt_last_update_date
OR ( wdt.last_update_date IS NULL
AND rec_wwtt.wdt_last_update_date IS NULL
)
)
FOR UPDATE NOWAIT;
SELECT mcce.cycle_count_entry_id
INTO l_transaction_temp_id_table (i)
FROM mtl_cycle_count_entries mcce,
wms_dispatched_tasks wdt
WHERE mcce.cycle_count_entry_id =
rec_wwtt.transaction_temp_id
AND mcce.cycle_count_entry_id = wdt.transaction_temp_id(+)
AND 3 = wdt.task_type(+)
AND DECODE (wdt.status, NULL, 1, wdt.status) =
NVL (rec_wwtt.status_id_original,
-1)
AND NVL (mcce.task_priority, -1) =
NVL (rec_wwtt.priority_original,
-1)
AND NVL (wdt.person_id, -1) =
NVL (rec_wwtt.person_id_original,
-1)
AND mcce.last_updated_by = rec_wwtt.mmtt_last_updated_by
AND mcce.last_update_date =
rec_wwtt.mmtt_last_update_date
AND NVL (wdt.last_updated_by, -1) =
NVL (rec_wwtt.wdt_last_updated_by,
-1)
AND ( wdt.last_update_date =
rec_wwtt.wdt_last_update_date
OR ( wdt.last_update_date IS NULL
AND rec_wwtt.wdt_last_update_date IS NULL
)
)
FOR UPDATE NOWAIT;
l_user_task_type_id (i) := rec_wwtt.user_task_type_id; -- R12: Update User Task Type Id
l_user_task_type_id (i) := rec_wwtt.user_task_type_id; -- R12: Update User Task Type Id
UPDATE mtl_material_transactions_temp
SET wms_task_status =
DECODE (l_wms_task_status_table (i),
8, 8,
1, 1,
NULL
),
task_priority = l_task_priority_table (i),
last_update_date = SYSDATE,
last_updated_by = p_user_id,
last_update_login = p_login_id,
standard_operation_id = l_user_task_type_id (i)
WHERE transaction_temp_id = l_transaction_temp_id_table (i) -- R12: Update User Task Type Id
AND l_task_type_id (i) <> 3;
DEBUG ('No of records updated are-777 ' || SQL%ROWCOUNT);
UPDATE mtl_cycle_count_entries
SET task_priority = l_task_priority_table (i),
last_update_date = SYSDATE,
last_updated_by = p_user_id,
last_update_login = p_login_id,
standard_operation_id = l_user_task_type_id (i) --R12: Update User Task Type Id
WHERE cycle_count_entry_id IN (
SELECT mcce.cycle_count_entry_id
FROM wms_waveplan_tasks_temp wwtt, mtl_cycle_count_entries mcce, mtl_cycle_count_headers mcch
WHERE wwtt.transaction_temp_id=l_transaction_temp_id_table (i)
AND wwtt.is_modified = 'Y'
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.inventory_item_id = wwtt.inventory_item_id
AND mcce.subinventory = wwtt.subinventory
AND mcce.locator_id = wwtt.locator_id
AND NVL(mcce.revision,'-1') = NVL(wwtt.revision, '-1')
AND mcch.cycle_count_header_name = wwtt.source_header
AND mcch.organization_id = wwtt.organization_id
AND mcce.entry_status_code not in (2,4,5))
AND l_task_type_id (i) = 3;
DEBUG ('No of records updated are-666 ' || SQL%ROWCOUNT);
DELETE wms_dispatched_tasks wdt
WHERE wdt.status IN (2, 3, 9) -- R12: Delete the Active or Dispatched tasks which were updated to pending/Unreleased
AND wdt.transaction_temp_id IN (
SELECT transaction_temp_id
FROM wms_waveplan_tasks_temp wwtt
WHERE wwtt.status_id IN (1, 8)
AND wwtt.is_modified = 'Y'
-- adding following condition to delete WDT's with no child tasks
AND (
(wdt.task_type = 3 AND NVL(wwtt.num_of_child_tasks,1)<2)
OR
wdt.task_type <> 3
)
);
l_update_date := SYSDATE;
DEBUG ('inserting into WDT ' || x_save_count);
INSERT INTO wms_dispatched_tasks
(task_id, transaction_temp_id, organization_id,
user_task_type, person_id, effective_start_date,
effective_end_date, person_resource_id,
machine_resource_id, status, dispatched_time,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, task_type, priority,
move_order_line_id, operation_plan_id, transfer_lpn_id)
(SELECT wwtt.task_id, wwtt.transaction_temp_id,
wwtt.organization_id, NVL (wwtt.user_task_type_id, 0),
wwtt.person_id,sysdate, sysdate , /*bug#6409956.replaced effective dates by sysdate */
wwtt.person_resource_id, NULL, 2, -- Queued
NULL, l_update_date, p_user_id, l_update_date, p_user_id,
p_login_id, wwtt.task_type_id, wwtt.priority,
wwtt.move_order_line_id, wwtt.operation_plan_id,
wwtt.to_lpn_id
FROM wms_waveplan_tasks_temp wwtt
WHERE wwtt.status_id = 2
AND wwtt.status_id_original IN (1, 8)
AND wwtt.is_modified = 'Y'
-- bug 11705135 : adding following condition to insert WDT record with no child tasks
-- WDT CC records with child tasks will be added later in this procedure
AND (
(wwtt.task_type_id = 3 AND NVL(wwtt.num_of_child_tasks,1)<2)
OR
wwtt.task_type_id <> 3
)
AND NOT EXISTS (
SELECT 1
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id =
wwtt.transaction_temp_id));
DEBUG ('No of records inserted are-444 ' || SQL%ROWCOUNT);
UPDATE wms_dispatched_tasks wdt
SET person_id = l_person_id_table (i),
person_resource_id = l_person_resource_id_table (i),
effective_start_date = l_effective_start_date_table (i),
effective_end_date = l_effective_end_date_table (i),
priority = l_task_priority_table (i),
last_update_date = l_update_date,
last_updated_by = p_user_id,
last_update_login = p_login_id
WHERE wdt.transaction_temp_id IN ( --modified the where condition for bug 14663231
SELECT wwtt.transaction_temp_id
FROM wms_waveplan_tasks_temp wwtt
WHERE wwtt.transaction_temp_id = l_transaction_temp_id_table (i)
--AND wwtt.status_id IN (1, 8) -- Bug 15898611
AND wwtt.status_id IN (1, 2, 8) -- Bug 15898611
AND wwtt.is_modified = 'Y'
AND (
(wdt.task_type = 3 AND NVL(wwtt.num_of_child_tasks,1)<2)
OR
wdt.task_type <> 3
)
);
DEBUG ('No of records updated are-333' || SQL%ROWCOUNT);
DELETE wms_dispatched_tasks wdt
WHERE wdt.status IN (2, 3)
AND wdt.task_type = 3
AND wdt.transaction_temp_id IN (
SELECT mcce.cycle_count_entry_id
FROM wms_waveplan_tasks_temp wwtt, mtl_cycle_count_entries mcce, mtl_cycle_count_headers mcch
WHERE wwtt.status_id IN (1, 8)
AND wwtt.is_modified = 'Y'
AND NVL(wwtt.num_of_child_tasks,1)>1
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.inventory_item_id = wwtt.inventory_item_id
AND mcce.subinventory = wwtt.subinventory
AND mcce.locator_id = wwtt.locator_id
AND NVL(mcce.revision,'-1') = NVL(wwtt.revision, '-1')
AND mcch.cycle_count_header_name = wwtt.source_header
AND mcch.organization_id = wwtt.organization_id
AND mcce.entry_status_code not in (2,4,5));
DEBUG ('No of records deleted are-999 ' || SQL%ROWCOUNT);
l_update_date := SYSDATE;
DEBUG ('inserting into WDT ' || x_save_count);
INSERT INTO wms_dispatched_tasks
(task_id, transaction_temp_id, organization_id,
user_task_type, person_id, effective_start_date,
effective_end_date, person_resource_id,
machine_resource_id, status, dispatched_time,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, task_type, priority,
move_order_line_id, operation_plan_id, transfer_lpn_id)
(SELECT wms_dispatched_tasks_s.NEXTVAL, mcce.cycle_count_entry_id,
wwtt.organization_id, NVL (wwtt.user_task_type_id, 0),
wwtt.person_id, sysdate, sysdate , /*bug#5965353.replaced effective dates by sysdate */
wwtt.person_resource_id, NULL, 2, -- Queued
NULL, l_update_date, p_user_id, l_update_date, p_user_id,
p_login_id, wwtt.task_type_id, wwtt.priority,
wwtt.move_order_line_id, wwtt.operation_plan_id,
wwtt.to_lpn_id
FROM wms_waveplan_tasks_temp wwtt, mtl_cycle_count_entries mcce, mtl_cycle_count_headers mcch
WHERE NVL(wwtt.NUM_OF_CHILD_TASKS,1) > 1
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.inventory_item_id = wwtt.inventory_item_id
AND mcce.subinventory = wwtt.subinventory
AND mcce.locator_id = wwtt.locator_id
AND NVL(mcce.revision,'-1') = NVL(wwtt.revision, '-1')
AND mcch.cycle_count_header_name = wwtt.source_header
AND mcch.organization_id = wwtt.organization_id
AND mcce.entry_status_code not in (2,4, 5)
AND wwtt.status_id = 2
AND wwtt.status_id_original IN (1, 8)
AND wwtt.task_type_id = 3
AND NOT EXISTS (
SELECT 1
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id =
wwtt.transaction_temp_id));
DEBUG ('No of records inserted are-888 ' || SQL%ROWCOUNT);
UPDATE wms_dispatched_tasks
SET person_id = l_person_id_table (i),
effective_start_date = l_effective_start_date_table (i),
effective_end_date = l_effective_end_date_table (i),
priority = l_task_priority_table (i),
last_update_date = l_update_date,
last_updated_by = p_user_id,
last_update_login = p_login_id
WHERE task_type = 3
AND transaction_temp_id IN (
SELECT mcce.cycle_count_entry_id
FROM wms_waveplan_tasks_temp wwtt, mtl_cycle_count_entries mcce, mtl_cycle_count_headers mcch
WHERE wwtt.transaction_temp_id=l_transaction_temp_id_table (i)
AND wwtt.is_modified = 'Y'
AND NVL(wwtt.num_of_child_tasks,1)>1
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.inventory_item_id = wwtt.inventory_item_id
AND mcce.subinventory = wwtt.subinventory
AND mcce.locator_id = wwtt.locator_id
AND NVL(mcce.revision,'-1') = NVL(wwtt.revision, '-1')
AND mcch.cycle_count_header_name = wwtt.source_header
AND mcch.organization_id = wwtt.organization_id
AND mcce.entry_status_code not in (2,4,5));
DEBUG ('No of records updated are-777 ' || SQL%ROWCOUNT);
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'S',
error = g_task_saved,
is_modified = 'N',
person_id_original = l_person_id_table (i),
status_id_original = l_wms_task_status_table (i),
priority_original = l_task_priority_table (i),
mmtt_last_updated_by = p_user_id,
mmtt_last_update_date = l_update_date,
wdt_last_updated_by =
DECODE (l_wms_task_status_table (i),
1, NULL,
8, NULL,
p_user_id
),
wdt_last_update_date =
TO_DATE (DECODE (l_wms_task_status_table (i),
1, NULL,
8, NULL,
TO_CHAR (l_update_date,
'DD-MON-YY HH24:MI:SS'
)
),
'DD-MON-YY HH24:MI:SS'
)
WHERE transaction_temp_id = l_transaction_temp_id_table (i);
DEBUG ('No of records updated are-222 ' || SQL%ROWCOUNT);
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'S',
error = 'Task Cancelled',
is_modified = 'N'
WHERE transaction_temp_id = rec_wwtt.transaction_temp_id;
UPDATE wms_waveplan_tasks_temp
SET RESULT = 'E',
error = l_error_message
WHERE is_modified = 'Y';
DEBUG ('No of records updated are-111 ' || SQL%ROWCOUNT);
SELECT status_id, COUNT (*) task_count
FROM wms_waveplan_tasks_temp
WHERE task_type_id = NVL (p_task_type_id, task_type_id)
GROUP BY status_id;
SELECT task_type_id, COUNT (*) task_count
FROM wms_waveplan_tasks_temp
WHERE task_type_id = NVL (p_task_type_id, task_type_id)
GROUP BY task_type_id;
SELECT transaction_temp_id, task_type_id, user_task_type_id,
unit_volume, volume_uom_code, unit_weight, weight_uom_code,
inventory_item_id, transaction_quantity
BULK COLLECT INTO l_transaction_temp_id, l_task_type_id, l_user_task_type_id,
l_volume, l_volume_uom_code, l_weight, l_weight_uom_code,
l_inventory_item_id, l_quantity
FROM wms_waveplan_tasks_temp
WHERE RESULT IN ('X', 'Y', 'Z');
UPDATE wms_waveplan_tasks_temp
SET time_estimate = l_time (i),
display_weight = l_weight (i),
display_volume = l_volume (i)
WHERE transaction_temp_id = l_transaction_temp_id (i)
AND task_type_id = l_task_type_id (i);
DELETE FROM wms_waveplan_summary_temp;
INSERT INTO wms_waveplan_summary_temp
(task_type_id, task_type, task_type_description,
total_tasks, total_time, time_uom, weight, weight_uom,
volume, volume_uom, organization_id)
SELECT wwtt.user_task_type_id, bso.operation_code,
bso.operation_description, COUNT (*),
ROUND (SUM (wwtt.time_estimate), 1), p_time_uom,
ROUND (SUM (wwtt.display_weight), 1), p_weight_uom,
ROUND (SUM (wwtt.display_volume), 1), p_volume_uom,
wwtt.organization_id
FROM wms_waveplan_tasks_temp wwtt, bom_standard_operations bso
WHERE wwtt.RESULT IN ('X', 'Y', 'Z')
AND wwtt.user_task_type_id = bso.standard_operation_id(+)
AND wwtt.organization_id = bso.organization_id(+)
GROUP BY wwtt.user_task_type_id,
bso.operation_code,
bso.operation_description,
wwtt.organization_id;
UPDATE wms_waveplan_tasks_temp
SET RESULT = l_result (i),
error = l_error (i)
WHERE transaction_temp_id = l_transaction_temp_id (i)
AND task_type_id = l_task_type_id (i);
SELECT transaction_temp_id
INTO l_transaction_temp_id
FROM wms_waveplan_tasks_temp
WHERE parent_line_id = p_transaction_temp_table (i)
AND status = 'Pending';
UPDATE wms_waveplan_tasks_temp
SET is_modified = 'Y',
status_id = 12,
status = g_status_codes(12)
WHERE transaction_temp_id = p_transaction_temp_table (i)
AND x_ret_code (i) = fnd_api.g_ret_sts_success;
DEBUG ('No of records updated are' || SQL%ROWCOUNT);
DELETE FROM wms_waveplan_tasks_temp
WHERE parent_line_id = p_transaction_temp_table (j)
AND status_id = 1
AND x_ret_code (j) = fnd_api.g_ret_sts_success;
DEBUG ('No of records deleted are' || SQL%ROWCOUNT);
DELETE
FROM wms_waveplan_tasks_temp wwtt
WHERE 1=1
AND status_id =13
AND not exists
(SELECT 1
FROM wms_exceptions we, mtl_transaction_reasons mtr
WHERE we.reason_id = mtr.reason_id
AND reason_context_code = 'PN'
AND we.TASK_id = wwtt.transaction_temp_id
) ;
debug('Delete Not Pick None tasks');
l_insert_query VARCHAR2 (3000);
l_select_generic VARCHAR2 (4000);
l_select_outbound VARCHAR2 (4000);
l_insert_query := get_generic_insert (p_is_unreleased => false, p_is_pending => false, p_is_queued => false, p_is_dispatched => false, p_is_active => false, p_is_loaded => false, p_is_completed => false , p_is_picknone => l_is_picknone --ER13869750
);
l_insert_query := l_insert_query || ') ';
debug('Entered into picknone select');
l_select_generic := NULL;
l_select_generic := 'SELECT ';
l_select_generic := l_select_generic || 'null, '; -- expansion_code
l_select_generic := l_select_generic || '''' || g_plan_task_types(4) || ''', ';
l_select_generic := l_select_generic || 'wdth.transaction_temp_id, ';
l_select_generic := l_select_generic || 'wdth.parent_transaction_id,'; --parent_line_id
l_select_generic := l_select_generic || 'wdth.inventory_item_id, '; --inventory_item_id
l_select_generic := l_select_generic || 'msiv.concatenated_segments, '; --item
l_select_generic := l_select_generic || 'msiv.description, '; --item description
l_select_generic := l_select_generic || 'msiv.unit_weight, '; --unit_weight
l_select_generic := l_select_generic || 'msiv.weight_uom_code, '; --weight_uom_code
l_select_generic := l_select_generic || 'msiv.unit_volume, '; --unit_volume
l_select_generic := l_select_generic || 'msiv.volume_uom_code, '; --volume_uom_code
l_select_generic := l_select_generic || 'wdth.organization_id, '; --organization_id
l_select_generic := l_select_generic || 'wdth.revision, '; --revision
l_select_generic := l_select_generic || 'wdth.source_subinventory_code, '; --subinventory
l_select_generic := l_select_generic || 'wdth.source_locator_id, '; --locator_id
l_select_generic := l_select_generic || 'decode(milv.segment19, null, milv.concatenated_segments, null), '; --locator
l_select_generic := l_select_generic || '13, '; --status_id
l_select_generic := l_select_generic || '13, '; --status_id_original
l_select_generic := l_select_generic || '''' || g_status_codes (13) || ''', '; --status
l_select_generic := l_select_generic || 'wdth.transaction_type_id, '; --transaction_type_id
l_select_generic := l_select_generic || 'wdth.transaction_action_id, '; --transaction_action_id
l_select_generic := l_select_generic || 'wdth.transaction_source_type_id, '; --transaction_source_type_id
l_select_generic := l_select_generic || 'mtst.transaction_source_type_name, '; --transaction_source_type
l_select_generic := l_select_generic || 'to_number(null), '; --transaction_source_id
l_select_generic := l_select_generic || 'to_number(null), '; --transaction_source_line_id
l_select_generic := l_select_generic || 'wdth.transfer_organization_id, '; --to_organization_id
l_select_generic := l_select_generic || 'mp1.organization_code, '; --to_organization_id
l_select_generic := l_select_generic || ' NVL(wdth.SUGGESTED_DEST_SUBINVENTORY,wdth.DEST_SUBINVENTORY_CODE) , '; --to_subinventory
l_select_generic := l_select_generic || ' NVL(wdth.SUGGESTED_DEST_LOCATOR_ID,wdth.DEST_LOCATOR_ID) , '; --'to_number(null), '; --to_locator_id
l_select_generic := l_select_generic || ' null , ';
l_select_generic := l_select_generic || 'wdth.transaction_UOM_CODE, '; --jan28 'wdd.REQUESTED_QUANTITY_UOM, ' --transaction_uom
l_select_generic := l_select_generic || 'wdth.transaction_QUANTITY, '; --jan28 'wdd.REQUESTED_QUANTITY, ' --transaction_quantity
l_select_generic := l_select_generic || 'wdth.user_task_type, '; --user_task_type_id
l_select_generic := l_select_generic || 'bso.operation_code, '; --user_task_type
l_select_generic := l_select_generic || 'to_number(null), '; --move_order_line_id
l_select_generic := l_select_generic || 'to_number(null), '; --pick_slip_number
l_select_generic := l_select_generic || 'to_number(null), '; --cartonization_id
l_select_generic := l_select_generic || 'null, '; --cartonization_lpn
l_select_generic := l_select_generic || 'to_number(null), '; --allocated_lpn_id
l_select_generic := l_select_generic || 'null, ';
l_select_generic := l_select_generic || 'to_number(null), '; --container_item_id
l_select_generic := l_select_generic || 'null, ';
l_select_generic := l_select_generic || 'wdth.lpn_id, '; --from_lpn_id
l_select_generic := l_select_generic || 'to_number(null), '; --from_lpn
l_select_generic := l_select_generic || 'to_number(null), '; --content_lpn_id
l_select_generic := l_select_generic || 'to_number(null), '; --content_lpn
l_select_generic := l_select_generic || 'wdth.transfer_lpn_id, '; --to_lpn_id
l_select_generic := l_select_generic || 'to_number(null), '; --to_lpn
l_select_generic := l_select_generic || 'wdth.LAST_UPDATE_DATE, '; --mmt_last_update_date
l_select_generic := l_select_generic || 'wdth.LAST_UPDATED_BY, '; --mmt_last_updated_by
l_select_generic := l_select_generic || 'wdth.priority, '; --priority
l_select_generic := l_select_generic || 'wdth.priority, '; --priority_original
l_select_generic := l_select_generic || 'wdth.task_type, '; --task_type_id
l_select_generic :=
l_select_generic
|| 'decode(wdth.task_type,'
|| '1, '''
|| g_task_types (1)
|| ''', 2, '''
|| g_task_types (2)
|| ''', 3, '''
|| g_task_types (3)
|| ''', 4, '''
|| g_task_types (4)
|| ''', 5, '''
|| g_task_types (5)
|| ''', 6, '''
|| g_task_types (6)
|| ''', 7, '''
|| g_task_types (7)
|| ''', 8, '''
|| g_task_types (8)
|| '''), ';
l_select_generic := l_select_generic || 'wdth.CREATION_DATE, '; --creation_time
l_select_generic := l_select_generic || 'wdth.operation_plan_id, '; --operation_plan_id
l_select_generic := l_select_generic || 'wop.operation_plan_name, '; --operation_plan
l_select_generic := l_select_generic || 'to_number(null), '; --operation_sequence
l_select_generic := l_select_generic || 'wdth.op_plan_instance_id, '; --op_plan_instance_id
l_select_generic := l_select_generic || 'wdth.task_id, '; --task_id
l_select_generic := l_select_generic || 'wdth.person_id, '; --person_id
l_select_generic := l_select_generic || 'wdth.person_id, '; --person_id_original
l_select_generic := l_select_generic || 'pap.full_name, '; --person_id
l_select_generic := l_select_generic || 'wdth.effective_start_date, '; --effective_start_date
l_select_generic := l_select_generic || 'wdth.effective_end_date, '; --effective_end_date
l_select_generic := l_select_generic || 'wdth.person_resource_id, '; --person_resource_id
l_select_generic := l_select_generic || 'br1.resource_code, '; --person_resource_code
l_select_generic := l_select_generic || 'wdth.machine_resource_id, '; --machine_resource_id
l_select_generic := l_select_generic || 'br2.resource_code, '; --machine_resource_code
l_select_generic := l_select_generic || 'wdth.equipment_instance, '; --equipment_instance
l_select_generic := l_select_generic || 'wdth.dispatched_time, '; --dispatched_time
l_select_generic := l_select_generic || 'wdth.loaded_time, '; --loaded_time
l_select_generic := l_select_generic || 'wdth.drop_off_time, '; --drop_off_time
l_select_generic := l_select_generic || 'to_date(null), '; --wdt_last_update_date
l_select_generic := l_select_generic || 'to_number(null), '; --wdt_last_updated_by
l_select_generic := l_select_generic || '''N'' , '; --is_modified -- bug #5163661
l_select_generic := l_select_generic || ' null , ';
l_select_generic := l_select_generic || 'to_number(null) ';
debug('select for bulk picknone'||l_select_generic);
l_query := l_insert_query || l_select_generic || l_from_generic || l_where_generic ;