[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_all pll,
po_headers_all ph,
po_lines_all 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_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(from_lpn_id, content_lpn_id),
loaded_lpn_id = Nvl(to_lpn_id, Nvl(from_lpn_id, content_lpn_id))
WHERE task_type_id IN (1, 4, 5, 6)
AND 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(from_lpn_id, content_lpn_id),
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
)
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
)
RETURN VARCHAR2
IS
l_select_generic VARCHAR2 (4000);
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 || '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;
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
);
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 || ', 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 || ', 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
);
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(wda.delivery_id,NULL,wdd.ship_method_code,decode(wnd.ship_method_code,NULL,wdd.ship_method_code,wnd.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(wda.delivery_id, NULL, wdd.carrier_id, Decode(wnd.carrier_id,NULL, wdd.carrier_id, wnd.carrier_id))';
l_select_outbound := l_select_outbound || ', wdd.carrier_id ';
l_select_outbound := l_select_outbound || ',INV_SHIPPING_TRANSACTION_PUB.GET_FREIGHT_CODE(Decode(wda.delivery_id,NULL,wdd.carrier_id,Decode(wnd.carrier_id,NULL,wdd.carrier_id,wnd.carrier_id)))';
l_select_outbound := l_select_outbound || ', INV_SHIPPING_TRANSACTION_PUB.GET_FREIGHT_CODE(wdd.carrier_id)';
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 || ', 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 = (select delivery_detail_id from wsh_delivery_details ';
|| '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
);
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
);
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 || ', 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 || 'MIN(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 || '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_group_by_cc := l_group_by_cc || 'mcce.last_update_date, ';
l_select_cc := l_select_cc || 'mcce.last_updated_by, ';
l_group_by_cc := l_group_by_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_group_by_cc := l_group_by_cc || 'wdt.last_update_date, ';
l_group_by_cc := l_group_by_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 ';
|| 'AND NOT EXISTS (SELECT 1 FROM wms_dispatched_tasks ';
l_query := l_insert_cc || l_select_cc || l_from_cc || l_where_cc || l_group_by_cc ;
l_query := l_select_cc || l_from_cc || l_where_cc || l_group_by_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
);
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
);
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
);
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
);
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;
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 = l_transaction_temp_id_table (i)
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');
DEBUG ('No of records deleted are-555 ' || 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 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'
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
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 transaction_temp_id = l_transaction_temp_id_table (i);
DEBUG ('No of records updated are-333 ' || 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);