[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
EXECUTE IMMEDIATE 'INSERT INTO my_temp_table VALUES ('||
p_message||','||i||')';
INSERT INTO my_temp_table VALUES (p_message,i);
SELECT we.WIP_ENTITY_NAME , wwtt.transaction_temp_id, wwtt.task_type_id
bulk collect INTO l_source_header, l_temp_id, l_task_type_id
FROM wip_entities we ,
wms_waveplan_tasks_temp wwtt
WHERE we.WIP_ENTITY_ID = wwtt.reference_id
AND wwtt.reference = 'WIP JOB'
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)
WHERE wwtt.transaction_temp_id = l_temp_id(i)
AND wwtt.task_type_id = l_task_type_id(i);
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 changed po_requisition_headers and po_requisition_lines to _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);
x_plans_select_str wms_plan_tasks_pvt.short_sql;
l_insert_str wms_plan_tasks_pvt.short_sql;
SELECT wooi.operation_sequence,wwtt.transaction_temp_id
FROM
wms_op_operation_instances wooi,
(SELECT transaction_temp_id
FROM wms_waveplan_tasks_temp
WHERE status_id = 6
AND plans_tasks = wms_plan_tasks_pvt.g_plan_task_types(2)) wwtt
WHERE wooi.source_task_id = wwtt.transaction_temp_id
UNION ALL
SELECT wooih.operation_sequence,wwtt.transaction_temp_id
FROM WMS_OP_OPERTN_INSTANCES_HIST wooih,
(SELECT transaction_temp_id
FROM wms_waveplan_tasks_temp
WHERE status_id = 6
AND plans_tasks = wms_plan_tasks_pvt.g_plan_task_types(2)) wwtt
where wooih.source_task_id = wwtt.transaction_temp_id;
/*Get the insert stmt - Call the function get_insert_stmt -
This stmt is used to insert records into wwtt.
Fields not revelant for plans, but are relevant for tasks are also included
in this insert stmt..for fetching plans, the corresponding select stmt
will select null values. */
get_col_list(l_insert_str);
l_insert_str := 'INSERT INTO WMS_WAVEPLAN_TASKS_TEMP(' || l_insert_str || ')';
debug('l_insert_str from get_col_list' || l_insert_str, 'query_inbound');
This function returns the select stmt w/o the insert stmt.
Store this string in a local variable, say l_tasks_query_str.
*/
IF l_query_tasks THEN
get_tasks(l_tasks_query_str,p_summary_mode); -- p_summary_mode
This procedure returns the select stmt w/o insert stmt into the
OUT variable Store the string in a local variable.
*/
/* If plan_specific query is made, it is handled in get_plans */
get_plans(l_plans_query_str);
/*First insert tasks records into WWTT - execute the sql.
Once tasks records are inserted now insert plans records -
Now insert the plans records into WWTT */
IF l_tasks_query_str IS NOT NULL THEN
IF l_debug = 1 THEN
debug('l_tasks_query_str is not null ','query_inbound_tasks');
l_tasks_query := l_insert_str || l_tasks_query_str;
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 = wms_plan_tasks_pvt.g_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 = wms_plan_tasks_pvt.g_to_sales_order_id;
/* Now the tasks records are inserted into the table wwtt . Next insert
the plan records if l_plans_query_str is not null */
IF l_plans_query_str IS NOT NULL THEN
l_plans_query := l_insert_str || l_plans_query_str;
/* delete the drop-pending record from the temp table, when both independent and planned
* task records are queried
*/
--IF wms_plan_tasks_pvt.g_query_independent_tasks AND
IF wms_plan_tasks_pvt.g_planned_tasks_rec.is_loaded
THEN
l_planned_task := wms_plan_tasks_pvt.g_plan_task_types(2);
DELETE FROM wms_waveplan_tasks_temp wwtt
WHERE EXISTS (
SELECT 1
FROM wms_op_operation_instances wooi
WHERE wwtt.transaction_temp_id = wooi.source_task_id
AND wwtt.operation_sequence = wooi.operation_sequence
AND wooi.operation_status IN (1,2) -- added for bug 5172443 to delete drop-active record
AND wooi.complete_time IS NULL)
AND wwtt.plans_tasks = l_planned_task
AND wwtt.status_id = 4;
DEBUG('rows deleted '|| SQL%ROWCOUNT);
l_plans_select_str varchar2(3000);
l_wdth_select_str VARCHAR2(3000);
l_inline_query := ' (Select distinct parent_line_id, reference_id, ' ||
' reference, source_header, line_number '||
' from wms_waveplan_tasks_temp where ' ||
' task_type_id in (2,8) ' ||
' and operation_plan_id is not null ' ||
' and parent_line_id is not null) wwtt ';
l_plans_select_str := 'SELECT ''+'',' ||
'''' || wms_plan_tasks_pvt.g_plan_task_types(3) || ''', ' ||
'mmtt.transaction_temp_id, '||
'mmtt.parent_line_id, ' ||
'mmtt.inventory_item_id, ' ||
'msiv.concatenated_segments, ' ||
'msiv.description, ' ||
'msiv.unit_weight, ' ||
'msiv.weight_uom_code, ' ||
'msiv.unit_volume, '||
'msiv.volume_uom_code, ' ||
'mmtt.organization_id, ' ||
'mmtt.revision, ' ||
'mmtt.subinventory_code, ' ||
'mmtt.locator_id, ' ||
'decode(milv.segment19, null, milv.concatenated_segments, null), ' ||
'wopi.status, ' ||
'wopi.status, ' ||
'decode(wopi.status,' ||
'1, ''' || wms_plan_tasks_pvt.g_plan_status_codes(1)
|| ''', 2, '''
|| wms_plan_tasks_pvt.g_plan_status_codes(2)
|| ''', 3, '''
|| wms_plan_tasks_pvt.g_plan_status_codes(3)
|| ''', 4, '''
|| wms_plan_tasks_pvt.g_plan_status_codes(4)
|| ''', 5, '''
|| wms_plan_tasks_pvt.g_plan_status_codes(5)
|| ''', 6, '''
|| wms_plan_tasks_pvt.g_plan_status_codes(6)
|| ''', 7, '''
|| wms_plan_tasks_pvt.g_plan_status_codes(7)
|| '''), ' ||
'mmtt.transaction_type_id, ' ||
'mmtt.transaction_action_id, ' ||
'mmtt.transaction_source_type_id, ';
l_plans_select_str := l_plans_select_str
|| 'mtst.transaction_source_type_name, '; --transaction_source_type
l_plans_select_str := l_plans_select_str ||
'mmtt.transaction_source_id, ' ||
'mmtt.trx_source_line_id, ' ||
'mmtt.transfer_organization, ';
l_plans_select_str := l_plans_select_str || 'mp1.organization_code, ';
l_plans_select_str:=l_plans_select_str ||
'mmtt.transfer_subinventory, ' ||
'mmtt.transfer_to_location, ';
l_plans_select_str := l_plans_select_str ||
'decode(milv1.segment19, null, milv1.concatenated_segments, null), ';
l_plans_select_str := l_plans_select_str ||
'mmtt.transaction_uom, ' ||
'mmtt.transaction_quantity, '||
'mmtt.standard_operation_id, ';
l_plans_select_str := l_plans_select_str || ' NULL, ';
l_plans_select_str := l_plans_select_str ||
'mmtt.move_order_line_id, ' || /*move_order_line_id */
'mmtt.pick_slip_number, ' || /*pick_slip_number*/
'mmtt.cartonization_id, '; /*cartonization_id */
l_plans_select_str := l_plans_select_str
|| 'wlpn2.license_plate_number, '; --cartonization_lpn
l_plans_select_str := l_plans_select_str ||
'mmtt.allocated_lpn_id, '; /*allocated_lpn_id*/
l_plans_select_str := l_plans_select_str
|| 'wlpn1.license_plate_number, '; --allocated_lpn
l_plans_select_str := l_plans_select_str ||
'mmtt.container_item_id, '; --container_item_id
l_plans_select_str := l_plans_select_str
|| 'msiv1.concatenated_segments, '; --container_item
l_plans_select_str := l_plans_select_str ||
'mmtt.lpn_id, '; --from_lpn_id
l_plans_select_str := l_plans_select_str ||
'wlpn5.license_plate_number, '; --from_lpn
l_plans_select_str := l_plans_select_str ||
'mmtt.content_lpn_id, '; --content_lpn_id
l_plans_select_str := l_plans_select_str
|| 'wlpn3.license_plate_number, '; --content_lpn
l_plans_select_str := l_plans_select_str ||'mmtt.transfer_lpn_id, ';
l_plans_select_str := l_plans_select_str || 'wlpn4.license_plate_number, ';
l_plans_select_str := l_plans_select_str ||
'mmtt.last_update_date, ' || /*mmtt_last_update_date*/
'mmtt.last_updated_by, ' || /*mmtt_last_updated_by*/
'mmtt.task_priority, ' || /*priority*/
'mmtt.task_priority, ' || /*priority_original */
'mmtt.wms_task_type, ' || /*task_type_id */
'decode(mmtt.wms_task_type,'
|| '1, '''
|| wms_plan_tasks_pvt.g_task_types(1)
|| ''', 2, '''
|| wms_plan_tasks_pvt.g_task_types(2)
|| ''', 3, '''
|| wms_plan_tasks_pvt.g_task_types(3)
|| ''', 4, '''
|| wms_plan_tasks_pvt.g_task_types(4)
|| ''', 5, '''
|| wms_plan_tasks_pvt.g_task_types(5)
|| ''', 6, '''
|| wms_plan_tasks_pvt.g_task_types(6)
|| ''', 7, '''
|| wms_plan_tasks_pvt.g_task_types(7)
|| ''', 8, '''
|| wms_plan_tasks_pvt.g_task_types(8)
|| '''), ' ||
'mmtt.creation_date, ' || /*creation_time */
'mmtt.operation_plan_id, '; /*operation_plan_id*/
l_plans_select_str := l_plans_select_str ||
'wop.operation_plan_name, '; /*operation_plan*/
l_plans_select_str := l_plans_select_str || 'to_number(null), '; --operation_sequence
l_plans_select_str := l_plans_select_str ||
'wopi.op_plan_instance_id op_plan_instance_id, '|| /*op_plan_instance_id*/
--'to_number(null), '|| /*operation_sequence*/
'to_number(null), ' || /*task_id*/
'to_number(null), ' || /*person_id*/
'to_number(null), '|| /*person_id_original*/
'null, ' || /*person*/
'to_date(null), ' || /*effective_start_date*/
'to_date(null), ' || /*effective_end_date*/
'to_number(null), '; /*person_resource_id*/
l_plans_select_str := l_plans_select_str ||
'null, ';/*person_resource_code*/
l_plans_select_str := l_plans_select_str ||
'to_number(null), '; /*machine_resource_id*/
l_plans_select_str := l_plans_select_str ||
'null, '; /*machine_resource_code*/
l_plans_select_str := l_plans_select_str ||
'null, ' || /*equipment_instance*/
'to_date(null), ' || /*dispatched_time*/
'to_date(null), ' || /*loaded_time*/
'to_date(null), ' || /*drop_off_time*/
'to_date(null), ' || /*wdt_last_update_date*/
'to_number(null), ' || /*wdt_last_updated_by*/
'''N'', ' || /*is_modified */
'mmtt.secondary_uom_code, '||
'mmtt.secondary_transaction_quantity ';
/* If inbound specific query is made, select more fields */
IF wms_plan_tasks_pvt.g_include_inbound THEN
l_plans_select_str := l_plans_select_str
|| ', wwtt.reference_id '
|| ', wwtt.reference ';
l_plans_select_str := l_plans_select_str
|| ', wwtt.source_header '
|| ', wwtt.line_number ';
debug('l_plans_select_str ' ||l_plans_select_str,'get_plans');
This requires that the task records are already inserted into wwtt.
We then fetch all parent records of the task records already
inserted into wwtt.
But now we do not check if the planned_task records are already
inserted into wwtt or not. This validation should be taken care of
in the calling program - In this case, it is get_inbound_tasks
**/
IF wms_plan_tasks_pvt.g_inbound_specific_query OR
wms_plan_tasks_pvt.g_include_inbound THEN
l_plans_where_str := l_plans_where_str
|| ' AND mmtt.transaction_temp_id = wwtt.parent_line_id ';
l_plans_str := l_plans_select_str || l_plans_from_str || l_plans_where_str;
get_wdth_plan_records(l_wdth_select_str, l_wdth_from_str, l_wdth_where_str);
debug('l_wdth_select_str from get_wdth_plan_records: ' ||l_wdth_select_str,'get_plans');
l_wdth_str := l_wdth_select_str || l_wdth_from_str || l_wdth_where_str;
l_tasks_select_str wms_plan_tasks_pvt.short_sql := NULL;
l_completed_tasks_select_str wms_plan_tasks_pvt.long_sql:= NULL; /* Bug 5507934 */
l_completed_records_select_str wms_plan_tasks_pvt.long_sql:= NULL;
l_inbound_select_str wms_plan_tasks_pvt.short_sql:= NULL;
debug('query for loaded and pending tasks. calling get_generic_select ','get_tasks');
l_tasks_select_str := ' SELECT mmtt.wms_task_type, count(*) ';
l_tasks_select_str := wms_waveplan_tasks_pvt.get_generic_select(
p_is_pending => l_is_pending
, p_is_loaded => l_is_loaded
, p_is_completed => FALSE
);
debug('after calling get_generic_select :','get_tasks');
debug('l_tasks_select_str ' || l_tasks_select_str,'get_tasks');
/* If inbound specific query is made, get the inbound specific select, from
and where clauses, append them to the final query for non-completed tasks.
*/
IF wms_plan_tasks_pvt.g_inbound_specific_query OR
wms_plan_tasks_pvt.g_include_inbound OR
wms_plan_tasks_pvt.g_include_crossdock THEN
IF l_debug = 1 THEN
debug('inbound specific query is made. calling get_inbound_specific_query ','get_tasks');
get_inbound_specific_query(x_inbound_select_str => l_inbound_select_str
,x_inbound_from_str => l_inbound_from_str
,x_inbound_where_str => l_inbound_where_str
,p_is_completed_rec => 0);
l_tasks_select_str := l_tasks_select_str || l_inbound_select_str; -- only for detailed select include the inbound specific select
/* If plan specific query is made, get the plan specific select, from and
where clauses, append them to the final query for non-completed tasks.
*/
if wms_plan_tasks_pvt.g_op_plan_id is not null THEN
l_tasks_where_str := l_tasks_where_str ||
' and mmtt.operation_plan_id = :op_plan_id ';
l_tasks_str := l_tasks_select_str || l_tasks_from_str || l_tasks_where_str;
get_completed_records(l_completed_records_select_str,
l_completed_records_from_str,
l_completed_records_where_str);
get_inbound_specific_query(x_inbound_select_str => l_inbound_select_str
,x_inbound_from_str => l_inbound_from_str
,x_inbound_where_str => l_inbound_where_str
,p_is_completed_rec => 1);
l_completed_records_select_str := l_completed_records_select_str
|| l_inbound_select_str;
/* If plan specific query is made, get the plan specific select, from and
* where clauses, append them to the final query for non-completed tasks.
*/
if wms_plan_tasks_pvt.g_op_plan_id is not null THEN
l_completed_records_where_str := l_completed_records_where_str ||
' and wdth.operation_plan_id = :op_plan_id ';
l_completed_records_str := l_completed_records_select_str ||
l_completed_records_from_str ||
l_completed_records_where_str;
l_completed_records_str := ' SELECT wdth.task_type,count(*) ' || l_completed_records_from_str ||
l_completed_records_where_str || ' GROUP BY wdth.task_type ';
l_completed_tasks_select_str := ' SELECT wdth.task_type, count(*) ';
l_completed_tasks_select_str := wms_waveplan_tasks_pvt.get_generic_select(
p_is_pending => FALSE
, p_is_loaded => FALSE
, p_is_completed => l_is_completed
);
get_inbound_specific_query(x_inbound_select_str => l_inbound_select_str
,x_inbound_from_str => l_inbound_from_str
,x_inbound_where_str => l_inbound_where_str
,p_is_completed_rec => 1);
l_completed_tasks_select_str := l_completed_tasks_select_str || l_inbound_select_str;
l_completed_tasks_str := l_completed_tasks_select_str || l_completed_tasks_from_str ||
l_completed_tasks_where_str;
|| ', mmtt_last_update_date '
|| ', mmtt_last_updated_by '
|| ', priority '
|| ', priority_original '
|| ', task_type_id '
|| ', task_type '
|| ', creation_time '
|| ', operation_plan_id ';
|| ', wdt_last_update_date '
|| ', wdt_last_updated_by '
|| ', is_modified '
|| ', secondary_transaction_uom '
|| ', secondary_transaction_quantity ';
procedure get_completed_records(x_wdth_select_str OUT NOCOPY varchar2,
x_wdth_from_str OUT NOCOPY varchar2,
x_wdth_where_str OUT NOCOPY varchar2) is
l_wdth_select_str wms_plan_tasks_pvt.short_sql:= NULL;
/**** First build the Select string ****/
IF l_debug = 1 THEN
debug('in get_completed_records ','get_completed_records');
l_wdth_select_str := 'SELECT '
|| 'decode(wdth.is_parent,''N'',null,''+''), '
|| 'decode(wdth.is_parent,''N'', decode(wdth.operation_plan_id,null,'
|| 'decode(wdth.parent_transaction_id,null,'''
|| wms_plan_tasks_pvt.g_plan_task_types(1) || ''','''
|| wms_plan_tasks_pvt.g_plan_task_types(5)
|| '''),'''|| wms_plan_tasks_pvt.g_plan_task_types(2)
|| '''),decode(wdth.transaction_action_id,28,'''||wms_plan_tasks_pvt.g_plan_task_types(4)
|| ''',''' || wms_plan_tasks_pvt.g_plan_task_types(3) || ''')), ' /*plan_task */
|| 'wdth.transaction_id, ' /* transaction_temp_id */
|| 'wdth.parent_transaction_id, ' /*parent_line_id*/
|| 'wdth.inventory_item_id, ' /*inventory_item_id*/
|| 'msiv.concatenated_segments, '/*item*/
|| 'msiv.description, ' /*item description*/
|| 'msiv.unit_weight, ' /*unit_weight*/
|| 'msiv.weight_uom_code, ' /*weight_uom_code*/
|| 'msiv.unit_volume, ' /*unit_volume*/
|| 'msiv.volume_uom_code, ' /*volume_uom_code*/
|| 'wdth.organization_id, ' /*organization_id*/
|| 'wdth.revision, ' /*revision*/
|| 'wdth.source_subinventory_code, ' /*subinventory*/
|| 'wdth.source_locator_id, ' /*locator_id*/
|| 'decode(milv.segment19, null, milv.concatenated_segments, null), '/*locator*/
|| 'wdth.status, ' /*status_id*/
|| 'wdth.status, ' /*status_id_original*/
|| 'decode(wdth.status,'
|| '6, '''
|| wms_plan_tasks_pvt.g_plan_status_codes(3)
|| ''', 11, '''
|| wms_plan_tasks_pvt.g_plan_status_codes(4)
|| ''', 12, '''
|| wms_plan_tasks_pvt.g_plan_status_codes(5)/*status*/
|| '''), '
|| 'wdth.transaction_type_id, '/*transaction_type_id*/
|| 'wdth.transaction_action_id, '/*transaction_action_id*/
|| 'wdth.transaction_source_type_id, '; --transaction_source_type_id
l_wdth_select_str := l_wdth_select_str
|| 'mtst.transaction_source_type_name, '; --transaction_source_type
l_wdth_select_str := l_wdth_select_str
|| 'to_number(null), ' /*transaction_source_id*/
|| 'to_number(null), ' /*transaction_source_line_id*/
|| 'wdth.transfer_organization_id, ';/* to_organization_id*/
l_wdth_select_str := l_wdth_select_str
|| 'mp1.organization_code, '; /* to_organization_code */
l_wdth_select_str := l_wdth_select_str
|| 'wdth.dest_subinventory_code, ' /*to_subinventory*/
|| 'wdth.dest_locator_id, '; /*to_locator_id*/
l_wdth_select_str := l_wdth_select_str
|| 'decode(milv1.segment19, null, milv1.concatenated_segments, null), ';
l_wdth_select_str := l_wdth_select_str
|| 'wdth.transaction_uom_code, ' /* transaction_uom */
|| 'wdth.transaction_quantity, ' /*transaction_quantity */
|| 'wdth.user_task_type, '; /*user_task_type_id*/
l_wdth_select_str := l_wdth_select_str
|| 'bso.operation_code, '; --user_task_type
l_wdth_select_str := l_wdth_select_str
|| 'to_number(null), ' /*move_order_line_id*/
|| 'to_number(null), ' /*pick_slip_number*/
|| 'to_number(null), '; /*cartonization_id*/
l_wdth_select_str := l_wdth_select_str || 'null, ';
l_wdth_select_str := l_wdth_select_str
|| 'to_number(null), '; /*allocated_lpn_id*/
l_wdth_select_str := l_wdth_select_str || 'null, '; --allocated_lpn
l_wdth_select_str := l_wdth_select_str
|| 'to_number(null), '; /*container_item_id*/
l_wdth_select_str := l_wdth_select_str || 'null, '; /*container item */
l_wdth_select_str := l_wdth_select_str || 'wdth.lpn_id, ' ; /*from_lpn_id*/
l_wdth_select_str := l_wdth_select_str
|| 'wlpn5.license_plate_number, ' ; /*from_lpn*/
l_wdth_select_str := l_wdth_select_str || 'wdth.content_lpn_id, '; /*content_lpn_id*/
l_wdth_select_str := l_wdth_select_str
|| 'wlpn3.license_plate_number, '; --content_lpn
l_wdth_select_str := l_wdth_select_str
|| 'wdth.transfer_lpn_id, '; --to_lpn_id
l_wdth_select_str := l_wdth_select_str
|| 'wlpn4.license_plate_number, '; --to_lpn
l_wdth_select_str := l_wdth_select_str
|| 'to_date(null), ' /*mmt_last_update_date*/
|| 'to_number(null), ' /*mmt_last_updated_by*/
|| 'wdth.priority, '/*priority*/
|| 'wdth.priority, ' /*priority_original*/
|| 'wdth.task_type, ' /*task_type_id*/
|| 'decode(wdth.task_type,'
|| '1, '''
|| wms_plan_tasks_pvt.g_task_types(1)
|| ''', 2, '''
|| wms_plan_tasks_pvt.g_task_types(2)
|| ''', 3, '''
|| wms_plan_tasks_pvt.g_task_types(3)
|| ''', 4, '''
|| wms_plan_tasks_pvt.g_task_types(4)
|| ''', 5, '''
|| wms_plan_tasks_pvt.g_task_types(5)
|| ''', 6, '''
|| wms_plan_tasks_pvt.g_task_types(6)
|| ''', 7, '''
|| wms_plan_tasks_pvt.g_task_types(7)
|| ''', 8, '''
|| wms_plan_tasks_pvt.g_task_types(8)
|| '''), '/*task*/
|| 'to_date(null), ' /*creation_time */
|| 'wdth.operation_plan_id, ';/*operation_plan_id*/
l_wdth_select_str := l_wdth_select_str
|| 'wop.operation_plan_name, ';/*operation_plan*/
l_wdth_select_str := l_wdth_select_str || ' to_number(null), '; --operation_sequence
l_wdth_select_str := l_wdth_select_str
|| 'to_number(wdth.op_plan_instance_id), '/*operation_instance_id*/
--|| 'to_number(null), '/*operation_sequence*/
|| 'wdth.task_id, '/*task_id*/
|| 'wdth.person_id, '/*person_id*/
|| 'wdth.person_id, ';/*person_id_original*/
l_wdth_select_str := l_wdth_select_str || 'pap.full_name, '; --person_id
l_wdth_select_str := l_wdth_select_str
|| 'wdth.effective_start_date, '/*effective_start_date*/
|| 'wdth.effective_end_date, '/*effective_end_date*/
|| 'wdth.person_resource_id, '; /*person_resource_id*/
l_wdth_select_str := l_wdth_select_str
|| 'br1.resource_code, '; --person_resource_code
l_wdth_select_str := l_wdth_select_str
|| 'wdth.machine_resource_id, '; --machine_resource_id
l_wdth_select_str := l_wdth_select_str
|| 'br2.resource_code, '; --machine_resource_code
l_wdth_select_str := l_wdth_select_str
|| 'wdth.equipment_instance, '/*equipment_instance*/
|| 'wdth.dispatched_time, '/*dispatched_time*/
|| 'wdth.loaded_time, '/*loaded_time*/
|| 'wdth.drop_off_time, '/*drop_off_time*/
|| 'to_date(null), '/*wdt_last_update_date*/
|| 'to_number(null), '/*wdt_last_updated_by*/
|| '''N'', '/*is modified*/
|| 'wdth.SECONDARY_TRANSACTION_UOM_CODE, '
|| 'wdth.secondary_transaction_quantity ';
debug('l_wdth_select_str## ' || l_wdth_select_str, 'get_completed_records');
x_wdth_select_str := l_wdth_select_str;
x_inbound_select_str OUT NOCOPY VARCHAR2
,x_inbound_from_str OUT NOCOPY VARCHAR2
,x_inbound_where_str OUT NOCOPY VARCHAR2
,p_is_completed_rec IN NUMBER) IS
l_inbound_select wms_plan_tasks_pvt.short_sql;
l_inbound_select :=', Nvl2(mtrl.reference ,mtrl.reference_id , mtrl.TXN_SOURCE_ID) ' ||
' , Nvl( mtrl.reference , Decode (mtrl.transaction_source_type_id,5 , ' ||'''WIP JOB'' , NULL ) ) ';
/* Original Code l_inbound_select := ', mtrl.reference_id ' || /*reference_id */
/*', mtrl.reference '; -- reference */
l_inbound_select := ', decode(rt.source_document_code, '
|| '''INVENTORY'', rt.shipment_line_id, '
|| '''PO'', rt.po_line_location_id, '
|| '''REQ'', rt.shipment_line_id, '
|| '''RMA'', rt.oe_order_line_id '
|| ' , NULL ' ||' , '|| '( SELECT distinct TXN_SOURCE_ID FROM mtl_txn_request_lines '
|| ' WHERE line_id = '|| l_mover_order_line || ' ))' -- Code for Bug#8467334
|| ', decode(rt.source_document_code, '
|| '''INVENTORY'', ''SHIPMENT_LINE_ID'', '
|| '''PO'', ''PO_LINE_LOCATION_ID'', '
|| '''REQ'', ''SHIPMENT_LINE_ID'', '
|| '''RMA'', ''ORDER_LINE_ID'' '
||', NULL '||' , ' || '( SELECT Distinct (Decode (transaction_source_type_id,5 , '
||'''WIP JOB'' , NULL )) FROM mtl_material_transactions WHERE MOVE_ORDER_LINE_ID =
'|| l_mover_order_line || ' ) )'; -- Code for Bug#8467334
l_inbound_select := l_inbound_select || ', ph.segment1 ' || /* source_header*/
', pl.line_num '; -- line_number
l_inbound_select := l_inbound_select || ', ooh.order_number '; -- source_header
l_inbound_select := l_inbound_select || ', ool.line_number '; -- line_number
l_inbound_select := l_inbound_select || ', prh.segment1 '; -- source_header
l_inbound_select := l_inbound_select || ', prl.line_num '; -- line_number
l_inbound_select := l_inbound_select || ', wnd.name '; --delivery
x_inbound_select_str := l_inbound_select;
|| ', (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 ';
PROCEDURE get_wdth_plan_records(x_wdth_select_str OUT NOCOPY VARCHAR2,
x_wdth_from_str OUT NOCOPY VARCHAR2,
x_wdth_where_str OUT NOCOPY VARCHAR2) IS
l_wdth_str VARCHAR2(6000);
l_wdth_select_str wms_plan_tasks_pvt.short_sql;
l_wdth_select_str :=
'SELECT ' ||
' decode(wdth.is_parent,''N'',decode(wdth.operation_plan_id,null,null,null),''+''), ' || /* expansion_code*/
' ''' || wms_plan_tasks_pvt.g_plan_task_types(3) || ''',' || /*plan_task*/
' wdth.transaction_id, ' || /*transaction_temp_id*/
' wdth.parent_transaction_id,' || /*parent_line_id*/
' wdth.inventory_item_id, ' || /*inventory_item_id*/
' msiv.concatenated_segments, ' || /*item*/
' msiv.description, ' || /*item description*/
' msiv.unit_weight, ' || /*unit_weight*/
' msiv.weight_uom_code, ' || /*weight_uom_code*/
' msiv.unit_volume, ' || /*unit_volume*/
' msiv.volume_uom_code, ' || /*volume_uom_code*/
' wdth.organization_id, ' || /*organization_id*/
' wdth.revision, ' || /*revision*/
' wdth.source_subinventory_code, ' ||/* subinventory*/
' wdth.source_locator_id, ' || /*locator_id*/
' decode(milv.segment19, null, milv.concatenated_segments, null), ' || /*locator*/
' wdth.status, ' || /*status_id*/
' wdth.status, ' || /*status_id_original*/
' decode(wdth.status,'
|| '6, '''
|| g_status_codes(6)
|| ''', 11, '''
|| g_status_codes(11)
|| ''', 12, '''
|| g_status_codes(12)
|| '''), ' ||
' wdth.transaction_type_id, ' || /*transaction_type_id*/
' wdth.transaction_action_id, ' || /*transaction_action_id */
' wdth.transaction_source_type_id, '; /*transaction_source_type_id*/
l_wdth_select_str := l_wdth_select_str
|| ' mtst.transaction_source_type_name, '; --transaction_source_type
l_wdth_select_str := l_wdth_select_str
|| ' to_number(null), ' || /*transaction_source_id*/
' to_number(null), ' || /*transaction_source_line_id*/
' wdth.transfer_organization_id, '; /*to_organization_id*/
l_wdth_select_str := l_wdth_select_str
|| 'mp1.organization_code, '; --to_organization_id
l_wdth_select_str := l_wdth_select_str
|| 'wdth.dest_subinventory_code, ' /*to_subinventory*/
|| 'wdth.dest_locator_id, '; /*to_locator_id*/
l_wdth_select_str := l_wdth_select_str ||
'decode(milv1.segment19, null, milv1.concatenated_segments, null), ';
l_wdth_select_str := l_wdth_select_str ||
'wdth.transaction_uom_code, ' || /*transaction_uom*/
' wdth.transaction_quantity, ' || /*transaction_quantity*/
' wdth.user_task_type, '; /*user_task_type_id*/
l_wdth_select_str := l_wdth_select_str
|| 'bso.operation_code, '; --user_task_type
l_wdth_select_str := l_wdth_select_str ||
' to_number(null), ' || /*move_order_line_id*/
' to_number(null), ' || /*pick_slip_number*/
' to_number(null), ';/*cartonization_id*/
l_wdth_select_str := l_wdth_select_str || 'null, ';
l_wdth_select_str := l_wdth_select_str || ' to_number(null), ' ; /*allocated_lpn_id*/
l_wdth_select_str := l_wdth_select_str || 'null, '; --allocated_lpn
l_wdth_select_str := l_wdth_select_str || ' to_number(null), ';/*container_item_id*/
l_wdth_select_str := l_wdth_select_str || 'null, '; /*container item */
l_wdth_select_str := l_wdth_select_str || ' wdth.lpn_id, ';/*from_lpn_id*/
l_wdth_select_str := l_wdth_select_str
|| 'wlpn5.license_plate_number, ';/*from_lpn*/
l_wdth_select_str := l_wdth_select_str || ' wdth.content_lpn_id, ';/*content_lpn_id*/
l_wdth_select_str := l_wdth_select_str
|| 'wlpn3.license_plate_number, '; --content_lpn
l_wdth_select_str := l_wdth_select_str
|| 'wdth.transfer_lpn_id, '; --to_lpn_id
l_wdth_select_str := l_wdth_select_str
|| 'wlpn4.license_plate_number, '; --to_lpn
l_wdth_select_str := l_wdth_select_str ||
' to_date(null), ' || /*mmt_last_update_date*/
' to_number(null), ' || /*mmt_last_updated_by*/
' wdth.priority, ' || /*priority*/
' wdth.priority, ' || /*priority_original*/
' wdth.task_type, ' || /*task_type_id*/
' decode(wdth.task_type,'
|| '1, '''
|| wms_plan_tasks_pvt.g_task_types(1)
|| ''', 2, '''
|| wms_plan_tasks_pvt.g_task_types(2)
|| ''', 3, '''
|| wms_plan_tasks_pvt.g_task_types(3)
|| ''', 4, '''
|| wms_plan_tasks_pvt.g_task_types(4)
|| ''', 5, '''
|| wms_plan_tasks_pvt.g_task_types(5)
|| ''', 6, '''
|| wms_plan_tasks_pvt.g_task_types(6)
|| ''', 7, '''
|| wms_plan_tasks_pvt.g_task_types(7)
|| ''', 8, '''
|| wms_plan_tasks_pvt.g_task_types(8)
|| '''), ' ||
' to_date(null), ' || /*creation_time */
' wdth.operation_plan_id, '; /*operation_plan_id*/
l_wdth_select_str := l_wdth_select_str
|| 'wop.operation_plan_name, ';/*operation_plan*/
l_wdth_select_str := l_wdth_select_str || ' to_number(null), ';
l_wdth_select_str := l_wdth_select_str ||
' to_number(wdth.op_plan_instance_id), ' || /*operation_instance_id*/
--' to_number(null), '|| /*operation_sequence*/
' wdth.task_id, ' ||/*task_id */
' wdth.person_id, ' || /*person_id*/
' wdth.person_id, '; /*person_id_original*/
l_wdth_select_str := l_wdth_select_str || 'pap.full_name, '; --person_id
l_wdth_select_str := l_wdth_select_str ||
' wdth.effective_start_date, ' || /*effective_start_date*/
' wdth.effective_end_date, ' || /*effective_end_date*/
' wdth.person_resource_id, '; --person_resource_id
l_wdth_select_str := l_wdth_select_str
|| 'br1.resource_code, '; --person_resource_code
l_wdth_select_str := l_wdth_select_str ||
' wdth.machine_resource_id, '; --machine_resource_id
l_wdth_select_str := l_wdth_select_str
|| 'br2.resource_code, '; --machine_resource_code
l_wdth_select_str := l_wdth_select_str ||
' wdth.equipment_instance, ' || /*equipment_instance*/
' wdth.dispatched_time, ' || /*dispatched_time*/
' wdth.loaded_time, ' || /*loaded_time*/
' wdth.drop_off_time, ' || /*drop_off_time*/
' to_date(null), ' || /*wdt_last_update_date*/
' to_number(null), ' || /*wdt_last_updated_by*/
'''N'', '|| --is modified
'wdth.SECONDARY_TRANSACTION_UOM_CODE, '||
'wdth.secondary_transaction_quantity ';
l_wdth_select_str := l_wdth_select_str
|| ', wwtt.reference_id '
|| ', wwtt.reference ';
l_wdth_select_str := l_wdth_select_str
|| ', wwtt.source_header '
|| ', wwtt.line_number ';
debug(' l_wdth_select_str ' || l_wdth_select_str,'get_wdth_plan_records');
x_wdth_select_str := l_wdth_select_str;