[Home] [Help]
PACKAGE BODY: APPS.WMS_PLAN_TASKS_PVT
Source
1 PACKAGE BODY wms_plan_tasks_pvt AS
2 /* $Header: WMSPTKPB.pls 120.32.12020000.4 2013/03/22 11:48:58 pyerrams ship $ */
3
4 /*
5 * Private constants to decide the destination
6 * of log message
7 */
8 LOGFILE CONSTANT NUMBER := 1;
9 LOGTABLE CONSTANT NUMBER := 2;
10 LOGSCREEN CONSTANT NUMBER := 3;
11
12 MODULE_NAME VARCHAR2(100) := '$RCSfile: WMSPTKPB.pls,v $($Revision: 120.32.12020000.4 $)';
13
14 /* Procedure to write the log messages */
15 i NUMBER := 0;
16
17 PROCEDURE DEBUG(
18 p_message VARCHAR2,
19 p_module VARCHAR2 DEFAULT 'Plans_tasks'
20 ) IS
21
22 l_counter NUMBER := 1;
23 l_substr VARCHAR2(4000);
24
25
26 l_module VARCHAR2(100) := MODULE_NAME||'.'||p_module;
27 l_message_length NUMBER := LENGTH(p_message);
28 l_message_destination NUMBER := LOGFILE;
29
30 --PRAGMA AUTONOMOUS_TRANSACTION;
31 BEGIN
32
33 IF l_message_destination = LOGFILE THEN
34
35 WHILE l_counter < l_message_length LOOP
36
37 l_substr := SUBSTR(p_message, l_counter, 4000);
38 inv_log_util.trace(SUBSTR(p_message, l_counter, 4000), l_module);
39 l_counter := l_counter + 4000;
40
41 END LOOP;
42
43 ELSIF l_message_destination = LOGSCREEN THEN
44 --dbms_output.put_line(p_message);
45 NULL;
46 ELSE
47
48 i := i+1;
49 EXECUTE IMMEDIATE 'INSERT INTO my_temp_table VALUES ('||
50 p_message||','||i||')';
51
52 END IF;
53 /*
54 INSERT INTO my_temp_table VALUES (p_message,i);
55 i := i+1;
56 COMMIT;*/
57 RETURN;
58 EXCEPTION
59
60 WHEN OTHERS THEN
61 IF l_message_destination = LOGSCREEN THEN
62 --dbms_output.disable;
63 debug('other error while printing debug msgs' || SQLERRM);
64 END IF;
65 inv_log_util.trace(p_message,p_module,9);
66 END DEBUG;
67
68 PROCEDURE set_inbound_source_header_line IS
69 TYPE source_header_type IS TABLE OF wms_waveplan_tasks_temp.source_header%TYPE;
70 TYPE line_number_type IS TABLE OF wms_waveplan_tasks_temp.line_number%TYPE;
71 TYPE temp_id_type IS TABLE OF wms_waveplan_tasks_temp.transaction_temp_id%TYPE;
72 TYPE task_type_id_type IS TABLE OF wms_waveplan_tasks_temp.task_type_id%TYPE;
73
74 l_source_header source_header_type;
75 l_line_number line_number_type;
76 l_temp_id temp_id_type;
77 l_task_type_id task_type_id_type;
78 BEGIN
79
80 -- Code added for Bug#8467334
81 -- code added to popluate wip Job name for the tasks
82
83 SELECT we.WIP_ENTITY_NAME , wwtt.transaction_temp_id, wwtt.task_type_id
84 bulk collect INTO l_source_header, l_temp_id, l_task_type_id
85 FROM wip_entities we ,
86 wms_waveplan_tasks_temp wwtt
87 WHERE we.WIP_ENTITY_ID = wwtt.reference_id
88 AND wwtt.reference = 'WIP JOB'
89 AND wwtt.source_header IS NULL
90 AND wwtt.reference_id IS NOT NULL;
91
92 IF l_temp_id.COUNT > 0 THEN
93 forall i IN l_temp_id.first..l_temp_id.last
94 UPDATE wms_waveplan_tasks_temp wwtt
95 SET source_header = l_source_header(i)
96 WHERE wwtt.transaction_temp_id = l_temp_id(i)
97 AND wwtt.task_type_id = l_task_type_id(i);
98 END IF;
99
100 -- End of code added to popluate wip Job name for the tasks
101 -- End of Code added for Bug#8467334
102
103 SELECT ph.segment1, pl.line_num, wwtt.transaction_temp_id, wwtt.task_type_id
104 bulk collect INTO l_source_header, l_line_number, l_temp_id, l_task_type_id
105 FROM po_line_locations_trx_v pll,--CLM Changes, using CLM views instead of base tables
106 po_headers_trx_v ph,
107 po_lines_trx_v pl,
108 wms_waveplan_tasks_temp wwtt
109 WHERE pll.line_location_id = wwtt.reference_id
110 AND pll.po_line_id = pl.po_line_id
111 AND ph.po_header_id = pl.po_header_id
112 AND wwtt.reference = 'PO_LINE_LOCATION_ID'
113 AND wwtt.source_header IS NULL
114 AND wwtt.reference_id IS NOT NULL;
115
116 IF l_temp_id.COUNT > 0 THEN
117 forall i IN l_temp_id.first..l_temp_id.last
118 UPDATE wms_waveplan_tasks_temp wwtt
119 SET source_header = l_source_header(i),
120 line_number = l_line_number(i)
121 WHERE wwtt.transaction_temp_id = l_temp_id(i)
122 AND wwtt.task_type_id = l_task_type_id(i);
123 END IF;
124
125 SELECT ooh.order_number, ool.line_number, wwtt.transaction_temp_id, wwtt.task_type_id
126 bulk collect INTO l_source_header, l_line_number, l_temp_id, l_task_type_id
127 FROM oe_order_lines_all ool,
128 oe_order_headers_all ooh,
129 wms_waveplan_tasks_temp wwtt
130 WHERE ool.line_id = wwtt.reference_id
131 AND ooh.header_id = ool.header_id
132 AND wwtt.reference = 'ORDER_LINE_ID'
133 AND wwtt.source_header IS NULL
134 AND wwtt.reference_id IS NOT NULL;
135
136 IF l_temp_id.COUNT > 0 THEN
137 forall i IN l_temp_id.first..l_temp_id.last
138 UPDATE wms_waveplan_tasks_temp wwtt
139 SET source_header = l_source_header(i),
140 line_number = l_line_number(i)
141 WHERE wwtt.transaction_temp_id = l_temp_id(i)
142 AND wwtt.task_type_id = l_task_type_id(i);
143 END IF;
144
145 SELECT Decode(rsl.requisition_line_id, NULL, rsh.shipment_num, prh.segment1),
146 Decode(rsl.requisition_line_id, NULL, rsl.line_num, prl.line_num),
147 wwtt.transaction_temp_id, wwtt.task_type_id
148 bulk collect INTO l_source_header, l_line_number, l_temp_id, l_task_type_id
149 -- MOAC changed po_requisition_headers and po_requisition_lines to _ALL tables
150 FROM po_requisition_headers_all prh,
151 po_requisition_lines_all prl,
152 rcv_shipment_lines rsl,
153 rcv_shipment_headers rsh,
154 wms_waveplan_tasks_temp wwtt
155 WHERE rsl.shipment_line_id = wwtt.reference_id
156 AND prh.requisition_header_id(+) = prl.requisition_header_id
157 AND rsl.requisition_line_id = prl.requisition_line_id(+)
158 AND rsl.shipment_header_id = rsh.shipment_header_id
159 AND wwtt.reference = 'SHIPMENT_LINE_ID'
160 AND wwtt.source_header IS NULL
161 AND wwtt.reference_id IS NOT NULL;
162
163 IF l_temp_id.COUNT > 0 THEN
164 forall i IN l_temp_id.first..l_temp_id.last
165 UPDATE wms_waveplan_tasks_temp wwtt
166 SET source_header = l_source_header(i),
167 line_number = l_line_number(i)
168 WHERE wwtt.transaction_temp_id = l_temp_id(i)
169 AND wwtt.task_type_id = l_task_type_id(i);
170 END IF;
171 END set_inbound_source_header_line;
172
173 /* wrapper procedure to fetch the inbound plans and tasks */
174 PROCEDURE query_inbound_plan_tasks(x_return_status OUT NOCOPY VARCHAR2, p_summary_mode NUMBER DEFAULT 0) IS
175
176 l_plans_query wms_plan_tasks_pvt.long_sql;
177 l_tasks_query wms_plan_tasks_pvt.long_sql;
178 l_plans_query_str wms_plan_tasks_pvt.long_sql;
179 l_tasks_query_str wms_plan_tasks_pvt.long_sql;
180
181 l_query_plans BOOLEAN := FALSE;
182 l_query_tasks BOOLEAN := FALSE;
183
184 l_query_handle NUMBER; -- Handle for the dynamic sql
185 l_query_count NUMBER;
186 l_wms_task_type NUMBER;
187 l_task_count NUMBER;
188
189 x_plans_select_str wms_plan_tasks_pvt.short_sql;
190 x_plans_from_str wms_plan_tasks_pvt.short_sql;
191 x_plans_where_str wms_plan_tasks_pvt.short_sql;
192 l_insert_str wms_plan_tasks_pvt.short_sql;
193
194 l_module_name VARCHAR2(30) := 'query_inbound';
195 l_planned_task VARCHAR2(80);
196
197 l_is_range_so BOOLEAN := FALSE;
198 l_from_tonum_mso_seg1 VARCHAR2(40);
199 l_to_tonum_mso_seg1 VARCHAR2(40);
200 l_from_mso_seg2 VARCHAR2(150);
201 l_to_mso_seg2 VARCHAR2(150);
202 l_from_mso_seg1 VARCHAR2(40);
203 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
204
205 CURSOR upd_op_seq IS
206 SELECT wooi.operation_sequence,wwtt.transaction_temp_id
207 FROM
208 wms_op_operation_instances wooi,
209 (SELECT transaction_temp_id
210 FROM wms_waveplan_tasks_temp
211 WHERE status_id = 6
212 AND plans_tasks = wms_plan_tasks_pvt.g_plan_task_types(2)) wwtt
213 WHERE wooi.source_task_id = wwtt.transaction_temp_id
214 UNION ALL
215 SELECT wooih.operation_sequence,wwtt.transaction_temp_id
216 FROM WMS_OP_OPERTN_INSTANCES_HIST wooih,
217 (SELECT transaction_temp_id
218 FROM wms_waveplan_tasks_temp
219 WHERE status_id = 6
220 AND plans_tasks = wms_plan_tasks_pvt.g_plan_task_types(2)) wwtt
221 where wooih.source_task_id = wwtt.transaction_temp_id;
222
223 TYPE wwtt_op_seq_rec IS RECORD
224 ( operation_sequence NUMBER,
225 transaction_temp_id NUMBER
226 );
227 l_wwtt_opseq_rec wwtt_op_seq_rec;
228
229 BEGIN
230 IF l_debug = 1 THEN
231 debug('in query_inbound ', 'query_inbound');
232 IF wms_plan_tasks_pvt.g_include_crossdock THEN
233 debug('Querying Crossdock tasks', 'query_inbound');
234 ELSE
235 debug('NOT Querying Crossdock tasks', 'query_inbound');
236 END IF;
237 END IF;
238
239 x_return_status := fnd_api.g_ret_sts_success;
240
241 /* set the planned_tasks record statuses if planned_tasks are queried*/
242 IF wms_plan_tasks_pvt.g_query_planned_tasks THEN
243 IF l_debug = 1 THEN
244 debug('planned_tasks are queried ','query_inbound');
245 END IF;
246 wms_plan_tasks_pvt.g_planned_tasks_rec.is_loaded := wms_plan_tasks_pvt.g_is_loaded_task;
247 wms_plan_tasks_pvt.g_planned_tasks_rec.is_pending := wms_plan_tasks_pvt.g_is_pending_task;
248 wms_plan_tasks_pvt.g_planned_tasks_rec.is_completed := wms_plan_tasks_pvt.g_is_completed_task;
249 END IF;
250 /* If only planned task records are qeuried and independent tasks are not
251 * queried, then set the values of global variables to related to independent
252 * tasks to false
253 */
254 IF NOT wms_plan_tasks_pvt.g_query_independent_tasks THEN
255 wms_plan_tasks_pvt.g_is_loaded_task := FALSE;
256 wms_plan_tasks_pvt.g_is_pending_task := FALSE;
257 wms_plan_tasks_pvt.g_is_completed_task := FALSE;
258 END IF;
259
260 If wms_plan_tasks_pvt.g_is_pending_plan
261 or wms_plan_tasks_pvt.g_is_inprogress_plan
262 or wms_plan_tasks_pvt.g_is_completed_plan
263 or wms_plan_tasks_pvt.g_is_aborted_plan
264 or wms_plan_tasks_pvt.g_is_cancelled_plan
265 then
266 /* plans are queried */
267 IF l_debug = 1 THEN
268 debug('plans are queried ', 'query_inbound');
269 END IF;
270 l_query_plans := TRUE;
271 if wms_plan_tasks_pvt.g_is_pending_plan then
272 wms_plan_tasks_pvt.g_planned_tasks_rec.is_pending := TRUE;
273 END IF;
274 if wms_plan_tasks_pvt.g_is_inprogress_plan then
275 wms_plan_tasks_pvt.g_planned_tasks_rec.is_pending := TRUE;
276 wms_plan_tasks_pvt.g_planned_tasks_rec.is_loaded := TRUE;
277 wms_plan_tasks_pvt.g_planned_tasks_rec.is_completed := TRUE;
278 END IF;
279
280 IF wms_plan_tasks_pvt.g_is_completed_plan OR
281 wms_plan_tasks_pvt.g_is_cancelled_plan OR
282 wms_plan_tasks_pvt.g_is_aborted_plan THEN
283 wms_plan_tasks_pvt.g_planned_tasks_rec.is_completed := TRUE;
284 END IF;
285
286 wms_plan_tasks_pvt.g_query_planned_tasks := TRUE;
287 Else
288 IF l_debug = 1 THEN
289 debug('plans are not queried ', 'query_inbound');
290 END IF;
291 l_query_plans := FALSE;
292 End if;
293
294
295 IF wms_plan_tasks_pvt.g_query_independent_tasks OR
296 wms_plan_tasks_pvt.g_query_planned_tasks THEN
297 IF l_debug = 1 THEN
298 debug('tasks are queried ', 'query_inbound');
299 END IF;
300 l_query_tasks := TRUE;
301 ELSE
302 IF l_debug = 1 THEN
303 debug('tasks are not queried ', 'query_inbound');
304 END IF;
305 l_query_tasks := FALSE;
306 END IF;
307
308 /*Get the insert stmt - Call the function get_insert_stmt -
309 This stmt is used to insert records into wwtt.
310 Fields not revelant for plans, but are relevant for tasks are also included
311 in this insert stmt..for fetching plans, the corresponding select stmt
312 will select null values. */
313
314 get_col_list(l_insert_str);
315 l_insert_str := 'INSERT INTO WMS_WAVEPLAN_TASKS_TEMP(' || l_insert_str || ')';
316 IF l_debug = 1 THEN
317 debug('l_insert_str from get_col_list' || l_insert_str, 'query_inbound');
318 END IF;
319
320 /* Call the function get_tasks to fetch the tasks records if the user has
321 made task_specific query -
322 This includes independent and planned_tasks records.
323 This function returns the select stmt w/o the insert stmt.
324 Store this string in a local variable, say l_tasks_query_str.
325 */
326 IF l_query_tasks THEN
327 get_tasks(l_tasks_query_str,p_summary_mode); -- p_summary_mode
328 END IF;
329 IF l_debug = 1 THEN
330 debug('Query String : ' || l_tasks_query_str, 'query_inbound');
331 END IF;
332 /* If plans are queried, then we have to query the planned_tasks also.
333 * If pending plans are queried,
334 fetch the pending planned_tasks.
335 if in_progress plans are queried,
336 fetch the pending planned_tasks +
337 fetch the Loaded planned_tasks +
338 fetch the completed planned_tasks.
339 if completed plans are queried,
340 fetch the completed planned_tasks.
341
342 Note: Since plans are relevant only for inbound now, the statuses
343 applicable for planned_tasks are limited. When this is extended for
344 outbound, more statuses should be queried for
345 */
346
347 if l_query_plans then
348 /* Call the procedure get_plans to fetch the plans records query.
349 This procedure returns the select stmt w/o insert stmt into the
350 OUT variable Store the string in a local variable.
351 */
352 /* If plan_specific query is made, it is handled in get_plans */
353
354 get_plans(l_plans_query_str);
355 IF l_debug = 1 THEN
356 debug('l_plans_query_str from get_plans' || l_plans_query_str, 'query_inbound');
357 END IF;
358 END IF;
359
360
361 /*First insert tasks records into WWTT - execute the sql.
362 Once tasks records are inserted now insert plans records -
363 Now insert the plans records into WWTT */
364
365 IF l_tasks_query_str IS NOT NULL THEN
366 IF l_debug = 1 THEN
367 debug('l_tasks_query_str is not null ','query_inbound_tasks');
368 END IF;
369 IF p_summary_mode = 1 THEN
370 l_tasks_query := l_tasks_query_str;
371 ELSE
372 l_tasks_query := l_insert_str || l_tasks_query_str;
373 END IF;
374 IF l_debug = 1 THEN
375 debug('l_tasks_query ' || l_tasks_query,'query_inbound');
376 END IF;
377
378 l_query_handle := DBMS_SQL.open_cursor;
379 DBMS_SQL.parse(l_query_handle, l_tasks_query, DBMS_SQL.native);
380
381 /* set the bind variables now */
382 IF l_debug = 1 THEN
383 debug('setting the bind_variables ','query_inbound');
384 END IF;
385 IF wms_plan_tasks_pvt.g_organization_id IS NOT NULL THEN
386 IF l_debug = 1 THEN
387 debug('wms_plan_tasks_pvt.g_organization_id is not null ','query_inbound');
388 END IF;
389 dbms_sql.bind_variable(l_query_handle, 'org_id', wms_plan_tasks_pvt.g_organization_id);
390 END IF;
391
392 IF wms_plan_tasks_pvt.g_subinventory_code IS NOT NULL THEN
393 IF l_debug = 1 THEN
394 debug('wms_plan_tasks_pvt.g_subinventory_code is not null ','query_inbound');
395 END IF;
396 dbms_sql.bind_variable(l_query_handle, 'sub_code', wms_plan_tasks_pvt.g_subinventory_code);
397 END IF;
398
399 IF wms_plan_tasks_pvt.g_locator_id IS NOT NULL THEN
400 IF l_debug = 1 THEN
401 debug('wms_plan_tasks_pvt.g_locator_id is not null ','query_inbound');
402 END IF;
403 dbms_sql.bind_variable(l_query_handle, 'loc_id', wms_plan_tasks_pvt.g_locator_id);
404 END IF;
405
406 IF wms_plan_tasks_pvt.g_to_subinventory_code IS NOT NULL THEN
407 IF l_debug = 1 THEN
408 debug('wms_plan_tasks_pvt.g_to_subinventory_code is not null ','query_inbound');
409 END IF;
410 dbms_sql.bind_variable(l_query_handle, 'to_sub_code', wms_plan_tasks_pvt.g_to_subinventory_code );
411 END IF;
412
413 IF wms_plan_tasks_pvt.g_to_locator_id IS NOT NULL THEN
414 IF l_debug = 1 THEN
415 debug('wms_plan_tasks_pvt.g_to_locator_id is not null ','query_inbound');
416 END IF;
417 dbms_sql.bind_variable(l_query_handle, 'to_loc_id', wms_plan_tasks_pvt.g_to_locator_id );
418 END IF;
419
420 IF wms_plan_tasks_pvt.g_category_set_id IS NOT NULL THEN
421 IF l_debug = 1 THEN
422 debug('wms_plan_tasks_pvt.g_category_set_id is not null ','query_inbound');
423 END IF;
424 dbms_sql.bind_variable(l_query_handle, 'category_set_id', wms_plan_tasks_pvt.g_category_set_id );
425 END IF;
426
427 IF wms_plan_tasks_pvt.g_item_category_id IS NOT NULL THEN
428 IF l_debug = 1 THEN
429 debug('wms_plan_tasks_pvt.g_item_category_id is not null ','query_inbound');
430 END IF;
431 dbms_sql.bind_variable(l_query_handle, 'item_category_id', wms_plan_tasks_pvt.g_item_category_id );
432 END IF;
433
434 IF wms_plan_tasks_pvt.g_inventory_item_id IS NOT NULL THEN
435 IF l_debug = 1 THEN
436 debug('wms_plan_tasks_pvt.g_inventory_item_id is not null ','query_inbound');
437 END IF;
438 dbms_sql.bind_variable(l_query_handle, 'item_id', wms_plan_tasks_pvt.g_inventory_item_id );
439 END IF;
440
441 IF wms_plan_tasks_pvt.g_person_id IS NOT NULL THEN
442 IF l_debug = 1 THEN
443 debug('wms_plan_tasks_pvt.g_person_id is not null ','query_inbound');
444 END IF;
445 dbms_sql.bind_variable(l_query_handle, 'person_id', wms_plan_tasks_pvt.g_person_id);
446 END IF;
447
448 IF wms_plan_tasks_pvt.g_person_resource_id IS NOT NULL THEN
449 IF l_debug = 1 THEN
450 debug('wms_plan_tasks_pvt.g_person_resource_id is not null ','query_inbound');
451 END IF;
452 dbms_sql.bind_variable(l_query_handle, 'person_resource_id', wms_plan_tasks_pvt.g_person_resource_id);
453 END IF;
454
455 IF wms_plan_tasks_pvt.g_equipment_type_id IS NOT NULL THEN
456 IF l_debug = 1 THEN
457 debug('wms_plan_tasks_pvt.g_equipment_type_id is not null ','query_inbound');
458 END IF;
459 dbms_sql.bind_variable(l_query_handle, 'equipment_type_id', wms_plan_tasks_pvt.g_equipment_type_id);
460 END IF;
461
462 IF wms_plan_tasks_pvt.g_machine_resource_id IS NOT NULL THEN
463 IF l_debug = 1 THEN
464 debug('wms_plan_tasks_pvt.g_machine_resource_id is not null ','query_inbound');
465 END IF;
466 dbms_sql.bind_variable(l_query_handle, 'machine_resource_id', wms_plan_tasks_pvt.g_machine_resource_id);
467 END IF;
468
469 IF wms_plan_tasks_pvt.g_machine_instance IS NOT NULL THEN
470 IF l_debug = 1 THEN
471 debug('wms_plan_tasks_pvt.g_machine_instance is not null ','query_inbound');
472 END IF;
473 dbms_sql.bind_variable(l_query_handle, 'machine_instance', wms_plan_tasks_pvt.g_machine_instance);
474 END IF;
475
476 IF wms_plan_tasks_pvt.g_from_creation_date IS NOT NULL THEN
477 IF l_debug = 1 THEN
478 debug('wms_plan_tasks_pvt.g_from_creation_date is not null ','query_inbound');
479 END IF;
480 dbms_sql.bind_variable(l_query_handle, 'from_creation_date', wms_plan_tasks_pvt.g_from_creation_date);
481 END IF;
482
483 IF wms_plan_tasks_pvt.g_to_creation_date IS NOT NULL THEN
484 IF l_debug = 1 THEN
485 debug('wms_plan_tasks_pvt.g_to_creation_date is not null ','query_inbound');
486 END IF;
487 dbms_sql.bind_variable(l_query_handle, 'to_creation_date', wms_plan_tasks_pvt.g_to_creation_date);
488 END IF;
489
490 IF wms_plan_tasks_pvt.g_from_task_quantity IS NOT NULL THEN
491 IF l_debug = 1 THEN
492 debug('wms_plan_tasks_pvt.g_from_task_quantity is not null ','query_inbound');
493 END IF;
494 dbms_sql.bind_variable(l_query_handle, 'from_task_quantity', wms_plan_tasks_pvt.g_from_task_quantity );
495 END IF;
496
497 IF wms_plan_tasks_pvt.g_to_task_quantity IS NOT NULL THEN
498 IF l_debug = 1 THEN
499 debug('wms_plan_tasks_pvt.g_to_task_quantity is not null ','query_inbound');
500 END IF;
501 dbms_sql.bind_variable(l_query_handle, 'to_task_quantity', wms_plan_tasks_pvt.g_to_task_quantity );
502 END IF;
503
504 IF wms_plan_tasks_pvt.g_from_requisition_header_id IS NOT NULL THEN
505 IF l_debug = 1 THEN
506 debug('wms_plan_tasks_pvt.g_from_requisition_header_id is not null ','query_inbound');
507 END IF;
508 dbms_sql.bind_variable(l_query_handle, 'from_requisition_header_id', wms_plan_tasks_pvt.g_from_requisition_header_id );
509 END IF;
510
511 IF wms_plan_tasks_pvt.g_to_requisition_header_id IS NOT NULL THEN
512 IF l_debug = 1 THEN
513 debug('wms_plan_tasks_pvt.g_to_requisition_header_id is not null ','query_inbound');
514 END IF;
515 dbms_sql.bind_variable(l_query_handle, 'to_requisition_header_id', wms_plan_tasks_pvt.g_to_requisition_header_id );
516 END IF;
517
518 IF wms_plan_tasks_pvt.g_from_shipment_number IS NOT NULL THEN
519 IF l_debug = 1 THEN
520 debug('wms_plan_tasks_pvt.g_from_shipment_number is not null ','query_inbound');
521 END IF;
522 -- Bug #3746810. Modified the bin var g_from_shipment_number to from_shipment_number
523 dbms_sql.bind_variable(l_query_handle, 'from_shipment_number', wms_plan_tasks_pvt.g_from_shipment_number );
524 END IF;
525
526 IF wms_plan_tasks_pvt.g_to_shipment_number IS NOT NULL THEN
527 IF l_debug = 1 THEN
528 debug('wms_plan_tasks_pvt.g_to_shipment_number is not null ','query_inbound');
529 END IF;
530 dbms_sql.bind_variable(l_query_handle, 'to_shipment_number', wms_plan_tasks_pvt.g_to_shipment_number );
531 END IF;
532
533 IF wms_plan_tasks_pvt.g_from_po_header_id IS NOT NULL THEN
534 IF l_debug = 1 THEN
535 debug('wms_plan_tasks_pvt.g_from_po_header_id is not null ' || wms_plan_tasks_pvt.g_from_po_header_id,'query_inbound');
536 END IF;
537 dbms_sql.bind_variable(l_query_handle, 'from_po_header_id', wms_plan_tasks_pvt.g_from_po_header_id );
538 END IF;
539
540 IF wms_plan_tasks_pvt.g_to_po_header_id IS NOT NULL THEN
541 IF l_debug = 1 THEN
542 debug('wms_plan_tasks_pvt.g_to_po_header_id is not null ' || wms_plan_tasks_pvt.g_to_po_header_id,'query_inbound');
543 END IF;
544 dbms_sql.bind_variable(l_query_handle, 'to_po_header_id', wms_plan_tasks_pvt.g_to_po_header_id );
545 END IF;
546
547 IF wms_plan_tasks_pvt.g_from_rma_header_id IS NOT NULL THEN
548 IF l_debug = 1 THEN
549 debug('wms_plan_tasks_pvt.g_from_rma_header_id is not null ','query_inbound');
550 END IF;
551 dbms_sql.bind_variable(l_query_handle, 'from_rma_header_id', wms_plan_tasks_pvt.g_from_rma_header_id );
552 END IF;
553
554 IF wms_plan_tasks_pvt.g_to_rma_header_id IS NOT NULL THEN
555 IF l_debug = 1 THEN
556 debug('wms_plan_tasks_pvt.g_to_rma_header_id is not null ','query_inbound');
557 END IF;
558 dbms_sql.bind_variable(l_query_handle, 'to_rma_header_id', wms_plan_tasks_pvt.g_to_rma_header_id );
559 END IF;
560
561 IF wms_plan_tasks_pvt.g_op_plan_id IS NOT NULL THEN
562 IF l_debug = 1 THEN
563 debug('wms_plan_tasks_pvt.g_op_plan_id IS NOT NULL ','query_inbound');
564 END IF;
565 dbms_sql.bind_variable(l_query_handle, 'op_plan_id', wms_plan_tasks_pvt.g_op_plan_id );
566 END IF;
567
568 IF wms_plan_tasks_pvt.g_include_crossdock THEN
569
570 if (wms_plan_tasks_pvt.g_from_sales_order_id = wms_plan_tasks_pvt.g_to_sales_order_id) then
571 l_is_range_so := FALSE;
572 else/*range so is TRUE if from or to is null or form<> to*/
573 l_is_range_so := TRUE;
574 end if;
575 if wms_plan_tasks_pvt.g_from_sales_order_id is not null then
576 select lpad(segment1,40), segment2,segment1
577 INTO l_from_tonum_mso_seg1,l_from_mso_seg2,l_from_mso_seg1
578 from mtl_sales_orders
579 WHERE sales_order_id = wms_plan_tasks_pvt.g_from_sales_order_id;
580 end if;
581 IF(l_is_range_so) THEN
582 /* Its a range...Query for details of to sales order */
583 if wms_plan_tasks_pvt.g_to_sales_order_id is not null then
584 select lpad(segment1,40), segment2
585 INTO l_to_tonum_mso_seg1,l_to_mso_seg2
586 from mtl_sales_orders
587 WHERE sales_order_id = wms_plan_tasks_pvt.g_to_sales_order_id;
588 end if;/*added the above code to get the values since we are not going to join with mso 3455109*/
589 ELSE
590 l_to_tonum_mso_seg1 := l_from_tonum_mso_seg1;
591 l_to_mso_seg2 := l_from_mso_seg2;
592 END IF;
593
594
595 IF wms_plan_tasks_pvt.g_include_internal_orders AND NOT wms_plan_tasks_pvt.g_include_sales_orders
596 THEN
597 DBMS_SQL.bind_variable (l_query_handle, 'source_type_id', 8);
598 ELSIF NOT wms_plan_tasks_pvt.g_include_internal_orders AND wms_plan_tasks_pvt.g_include_sales_orders
599 THEN
600 DBMS_SQL.bind_variable (l_query_handle, 'source_type_id', 2);
601 END IF;
602
603 /*added if else for 3455109 since we are changing the query also need to change binds if its for completed task*/
604 IF NOT g_is_completed_task THEN -- Non Completed tasks
605
606 IF wms_plan_tasks_pvt.g_from_sales_order_id IS NOT NULL
607 THEN
608 DBMS_SQL.bind_variable (l_query_handle,'from_sales_order_id',wms_plan_tasks_pvt.g_from_sales_order_id);
609 END IF;
610
611 IF wms_plan_tasks_pvt.g_to_sales_order_id IS NOT NULL
612 THEN
613 DBMS_SQL.bind_variable (l_query_handle,'to_sales_order_id',wms_plan_tasks_pvt.g_to_sales_order_id);
614 END IF;
615 ELSE --completed tasks
616 IF(l_is_range_so) then
617 IF wms_plan_tasks_pvt.g_from_sales_order_id IS NOT NULL THEN
618 --3240261 dbms_sql.bind_variable(l_query_handle, 'from_sales_order_id', p_from_sales_order_id);
619 dbms_sql.bind_variable(l_query_handle,'l_from_tonum_mso_seg1',l_from_tonum_mso_seg1);--added for 3455109
620 dbms_sql.bind_variable(l_query_handle,'l_from_mso_seg2',l_from_mso_seg2);--3455109
621 END IF;
622
623 IF wms_plan_tasks_pvt.g_to_sales_order_id IS NOT NULL THEN
624 --3420261 dbms_sql.bind_variable(l_query_handle, 'to_sales_order_id', p_to_sales_order_id);
625 dbms_sql.bind_variable(l_query_handle,'l_to_tonum_mso_seg1',l_to_tonum_mso_seg1);
626 dbms_sql.bind_variable(l_query_handle,'l_to_mso_seg2',l_to_mso_seg2);
627 END IF;
628 ELSE
629 dbms_sql.bind_variable(l_query_handle,'l_from_mso_seg1',l_from_mso_seg1);
630 dbms_sql.bind_variable(l_query_handle,'l_from_mso_seg2',l_from_mso_seg2);
631 END IF;--end of range or not range so
632
633 END IF;--end of copleted or not completed task 3455109
634
635 IF wms_plan_tasks_pvt.g_from_pick_slip_number IS NOT NULL
636 THEN
637 DBMS_SQL.bind_variable (l_query_handle,'from_pick_slip_number',wms_plan_tasks_pvt.g_from_pick_slip_number);
638 END IF;
639
640 IF wms_plan_tasks_pvt.g_to_pick_slip_number IS NOT NULL
641 THEN
642 DBMS_SQL.bind_variable (l_query_handle,'to_pick_slip_number',wms_plan_tasks_pvt.g_to_pick_slip_number);
643 END IF;
644
645 IF wms_plan_tasks_pvt.g_customer_id IS NOT NULL
646 THEN
647 DBMS_SQL.bind_variable (l_query_handle,'customer_id',wms_plan_tasks_pvt.g_customer_id);
648 END IF;
649
650 IF wms_plan_tasks_pvt.g_customer_category IS NOT NULL
651 THEN
652 DBMS_SQL.bind_variable (l_query_handle,'customer_category',wms_plan_tasks_pvt.g_customer_category);
653 END IF;
654
655 IF wms_plan_tasks_pvt.g_trip_id IS NOT NULL
656 THEN
657 DBMS_SQL.bind_variable (l_query_handle, 'trip_id', wms_plan_tasks_pvt.g_trip_id);
658 END IF;
659
660 IF wms_plan_tasks_pvt.g_delivery_id IS NOT NULL
661 THEN
662 DBMS_SQL.bind_variable (l_query_handle,'delivery_id',wms_plan_tasks_pvt.g_delivery_id);
663 END IF;
664
665 IF wms_plan_tasks_pvt.g_carrier_id IS NOT NULL
666 THEN
667 DBMS_SQL.bind_variable (l_query_handle,'carrier_id',wms_plan_tasks_pvt.g_carrier_id);
668 END IF;
669
670 IF wms_plan_tasks_pvt.g_ship_method IS NOT NULL
671 THEN
672 DBMS_SQL.bind_variable (l_query_handle,'ship_method',wms_plan_tasks_pvt.g_ship_method);
673 END IF;
674
675 IF wms_plan_tasks_pvt.g_shipment_priority IS NOT NULL
676 THEN
677 DBMS_SQL.bind_variable (l_query_handle,'shipment_priority',wms_plan_tasks_pvt.g_shipment_priority);
678 END IF;
679
680 IF wms_plan_tasks_pvt.g_from_shipment_date IS NOT NULL
681 THEN
682 DBMS_SQL.bind_variable (l_query_handle,'from_shipment_date',wms_plan_tasks_pvt.g_from_shipment_date);
683 END IF;
684
685 IF wms_plan_tasks_pvt.g_to_shipment_date IS NOT NULL
686 THEN
687 DBMS_SQL.bind_variable (l_query_handle,'to_shipment_date',wms_plan_tasks_pvt.g_to_shipment_date);
688 END IF;
689 /*
690 IF wms_plan_tasks_pvt.g_time_till_shipment IS NOT NULL AND wms_plan_tasks_pvt.g_time_till_shipment_uom_code IS NOT NULL
691 THEN
692 DBMS_SQL.bind_variable (l_query_handle,'p_time_till_shipment',wms_plan_tasks_pvt.g_time_till_shipment);
693 END IF;
694 */
695
696 IF wms_plan_tasks_pvt.g_ship_to_state IS NOT NULL
697 THEN
698 DBMS_SQL.bind_variable (l_query_handle,'ship_to_state',wms_plan_tasks_pvt.g_ship_to_state);
699 END IF;
700
701 IF wms_plan_tasks_pvt.g_ship_to_country IS NOT NULL
702 THEN
703 DBMS_SQL.bind_variable (l_query_handle,'ship_to_country',wms_plan_tasks_pvt.g_ship_to_country);
704 END IF;
705
706 IF wms_plan_tasks_pvt.g_ship_to_postal_code IS NOT NULL
707 THEN
708 DBMS_SQL.bind_variable (l_query_handle,'ship_to_postal_code',wms_plan_tasks_pvt.g_ship_to_postal_code);
709 END IF;
710
711 IF wms_plan_tasks_pvt.g_from_number_of_order_lines IS NOT NULL
712 THEN
713 DBMS_SQL.bind_variable (l_query_handle,'from_number_of_order_lines',wms_plan_tasks_pvt.g_from_number_of_order_lines);
714 END IF;
715
716 IF wms_plan_tasks_pvt.g_to_number_of_order_lines IS NOT NULL
717 THEN
718 DBMS_SQL.bind_variable (l_query_handle,'to_number_of_order_lines',wms_plan_tasks_pvt.g_to_number_of_order_lines);
719 END IF;
720
721 END IF;
722
723 /* end of setting the bind variables */
724 IF l_debug = 1 THEN
725 debug('end setting the bind variables ','query_inbound');
726 END IF;
727 IF p_summary_mode = 1 THEN
728 DBMS_SQL.DEFINE_COLUMN(l_query_handle, 1, l_wms_task_type);
729 DBMS_SQL.DEFINE_COLUMN(l_query_handle, 2, l_task_count);
730 END IF;
731
732 l_query_count := DBMS_SQL.EXECUTE(l_query_handle);
733 IF p_summary_mode = 1 THEN
734 LOOP
735 IF DBMS_SQL.FETCH_ROWS(l_query_handle)>0 THEN
736 DBMS_SQL.COLUMN_VALUE(l_query_handle, 1, l_wms_task_type);
737 DBMS_SQL.COLUMN_VALUE(l_query_handle, 2, l_task_count);
738 IF l_debug = 1 THEN
739 debug(' l_wms_task_type : ' || l_wms_task_type, 'query_inbound');
740 debug(' l_wms_task_count : ' || l_task_count, 'query_inbound');
741 END IF;
742 IF l_wms_task_type > 0 THEN
743 wms_waveplan_tasks_pvt.g_wms_task_summary_tbl(l_wms_task_type).wms_task_type := l_wms_task_type;
744 wms_waveplan_tasks_pvt.g_wms_task_summary_tbl(l_wms_task_type).task_count := wms_waveplan_tasks_pvt.g_wms_task_summary_tbl(l_wms_task_type).task_count + l_task_count;
745 ELSIF l_wms_task_type = -1 THEN
746 /* There are some taks populated with task_type as -1, we will show them as putaway tasks, not sure if this is correct. Doing it this way inorder to be consitent with the results grid*/
747
748 wms_waveplan_tasks_pvt.g_wms_task_summary_tbl(wms_waveplan_tasks_pvt.g_task_type_putaway).task_count := wms_waveplan_tasks_pvt.g_wms_task_summary_tbl(wms_waveplan_tasks_pvt.g_task_type_putaway).task_count + l_task_count;
749 END IF;
750 ELSE
751 EXIT; -- no more rows returned from dynamic SQL
752 END IF;
753 END LOOP;
754 END IF;
755 IF l_debug = 1 THEN
756 debug('l_query_count ' || l_query_count,'query_inbound');
757 END IF;
758 wms_plan_tasks_pvt.g_plans_tasks_record_count :=
759 wms_plan_tasks_pvt.g_plans_tasks_record_count + l_query_count;
760 END IF;
761
762 /* Now the tasks records are inserted into the table wwtt . Next insert
763 the plan records if l_plans_query_str is not null */
764 IF l_plans_query_str IS NOT NULL THEN
765 l_plans_query := l_insert_str || l_plans_query_str;
766 IF l_debug = 1 THEN
767 debug('l_plans_query ' || l_plans_query,'query_inbound');
768 END IF;
769 l_query_handle := DBMS_SQL.open_cursor;
770 DBMS_SQL.parse(l_query_handle, l_plans_query, DBMS_SQL.native);
771
772 /* Set the bind variables now */
773 IF wms_plan_tasks_pvt.g_organization_id IS NOT NULL THEN
774 IF l_debug = 1 THEN
775 debug('wms_plan_tasks_pvt.g_organization_id IS NOT NULL ','query_inbound');
776 END IF;
777 dbms_sql.bind_variable(l_query_handle, 'org_id', wms_plan_tasks_pvt.g_organization_id);
778 END IF;
779 IF wms_plan_tasks_pvt.g_subinventory_code IS NOT NULL THEN
780 IF l_debug = 1 THEN
781 debug('wms_plan_tasks_pvt.g_subinventory_code IS NOT NULL ','query_inbound');
782 END IF;
783 dbms_sql.bind_variable(l_query_handle, 'sub_code', wms_plan_tasks_pvt.g_subinventory_code);
784 END IF;
785 IF wms_plan_tasks_pvt.g_locator_id IS NOT NULL THEN
786 IF l_debug = 1 THEN
787 debug('wms_plan_tasks_pvt.g_locator_id IS NOT NULL ','query_inbound');
788 END IF;
789 dbms_sql.bind_variable(l_query_handle, 'loc_id', wms_plan_tasks_pvt.g_locator_id);
790 END IF;
791 IF wms_plan_tasks_pvt.g_to_subinventory_code IS NOT NULL THEN
792 IF l_debug = 1 THEN
793 debug('wms_plan_tasks_pvt.g_to_subinventory_code IS NOT NULL ','query_inbound');
794 END IF;
795 dbms_sql.bind_variable(l_query_handle, 'to_sub_code', wms_plan_tasks_pvt.g_to_subinventory_code );
796 END IF;
797 IF wms_plan_tasks_pvt.g_to_locator_id IS NOT NULL THEN
798 IF l_debug = 1 THEN
799 debug('wms_plan_tasks_pvt.g_to_locator_id IS NOT NULL ','query_inbound');
800 END IF;
801 dbms_sql.bind_variable(l_query_handle, 'to_loc_id', wms_plan_tasks_pvt.g_to_locator_id );
802 END IF;
803 IF wms_plan_tasks_pvt.g_inventory_item_id IS NOT NULL THEN
804 IF l_debug = 1 THEN
805 debug('wms_plan_tasks_pvt.g_inventory_item_id IS NOT NULL ','query_inbound');
806 END IF;
807 dbms_sql.bind_variable(l_query_handle, 'item_id', wms_plan_tasks_pvt.g_inventory_item_id );
808 END IF;
809 IF wms_plan_tasks_pvt.g_category_set_id IS NOT NULL THEN
810 IF l_debug = 1 THEN
811 debug('wms_plan_tasks_pvt.g_category_set_id IS NOT NULL ','query_inbound');
812 END IF;
813 dbms_sql.bind_variable(l_query_handle, 'category_set_id', wms_plan_tasks_pvt.g_category_set_id );
814 END IF;
815 IF wms_plan_tasks_pvt.g_item_category_id IS NOT NULL THEN
816 IF l_debug = 1 THEN
817 debug('wms_plan_tasks_pvt.g_item_category_id IS NOT NULL ','query_inbound');
818 END IF;
819 dbms_sql.bind_variable(l_query_handle, 'item_category_id', wms_plan_tasks_pvt.g_item_category_id );
820 END IF;
821 IF wms_plan_tasks_pvt.g_user_task_type_id IS NOT NULL THEN
822 IF l_debug = 1 THEN
823 debug('wms_plan_tasks_pvt.g_user_task_type_id IS NOT NULL ','query_inbound');
824 END IF;
825 dbms_sql.bind_variable(l_query_handle, 'user_task_type_id', wms_plan_tasks_pvt.g_user_task_type_id );
826 END IF;
827 IF wms_plan_tasks_pvt.g_from_task_quantity IS NOT NULL THEN
828 IF l_debug = 1 THEN
829 debug('wms_plan_tasks_pvt.g_from_task_quantity IS NOT NULL ','query_inbound');
830 END IF;
831 dbms_sql.bind_variable(l_query_handle, 'from_task_quantity', wms_plan_tasks_pvt.g_from_task_quantity );
832 END IF;
833 IF wms_plan_tasks_pvt.g_to_task_quantity IS NOT NULL THEN
834 IF l_debug = 1 THEN
835 debug('wms_plan_tasks_pvt.g_to_task_quantity IS NOT NULL ','query_inbound');
836 END IF;
837 dbms_sql.bind_variable(l_query_handle, 'to_task_quantity', wms_plan_tasks_pvt.g_to_task_quantity );
838 END IF;
839 IF wms_plan_tasks_pvt.g_from_task_priority IS NOT NULL THEN
840 IF l_debug = 1 THEN
841 debug('wms_plan_tasks_pvt.g_from_task_priority IS NOT NULL ','query_inbound');
842 END IF;
843 dbms_sql.bind_variable(l_query_handle, 'from_task_priority', wms_plan_tasks_pvt.g_from_task_priority );
844 END IF;
845
846 IF wms_plan_tasks_pvt.g_to_task_priority IS NOT NULL THEN
847 IF l_debug = 1 THEN
848 debug('wms_plan_tasks_pvt.g_to_task_priority IS NOT NULL ','query_inbound');
849 END IF;
850 dbms_sql.bind_variable(l_query_handle, 'to_task_priority', wms_plan_tasks_pvt.g_to_task_priority );
851 END IF;
852 IF wms_plan_tasks_pvt.g_from_creation_date IS NOT NULL THEN
853 IF l_debug = 1 THEN
854 debug('wms_plan_tasks_pvt.g_from_creation_date IS NOT NULL ','query_inbound');
855 END IF;
856 dbms_sql.bind_variable(l_query_handle, 'from_creation_date', wms_plan_tasks_pvt.g_from_creation_date );
857 END IF;
858 IF wms_plan_tasks_pvt.g_to_creation_date IS NOT NULL THEN
859 IF l_debug = 1 THEN
860 debug('wms_plan_tasks_pvt.g_to_creation_date IS NOT NULL ','query_inbound');
861 END IF;
862 dbms_sql.bind_variable(l_query_handle, 'to_creation_date', wms_plan_tasks_pvt.g_to_creation_date );
863 END IF;
864 IF wms_plan_tasks_pvt.g_plan_type_id IS NOT NULL THEN
865 IF l_debug = 1 THEN
866 debug('wms_plan_tasks_pvt.g_plan_type_id IS NOT NULL ','query_inbound');
867 END IF;
868 dbms_sql.bind_variable(l_query_handle, 'plan_type_id', wms_plan_tasks_pvt.g_plan_type_id );
869 END IF;
870 IF wms_plan_tasks_pvt.g_op_plan_id IS NOT NULL THEN
871 IF l_debug = 1 THEN
872 debug('wms_plan_tasks_pvt.g_op_plan_id IS NOT NULL ','query_inbound');
873 END IF;
874 dbms_sql.bind_variable(l_query_handle, 'op_plan_id', wms_plan_tasks_pvt.g_op_plan_id );
875 END IF;
876 /* end setting the bind variables */
877 IF l_debug = 1 THEN
878 debug('end setting the bind variables for plans query','query_inbound');
879 END IF;
880 l_query_count := DBMS_SQL.EXECUTE(l_query_handle);
881 IF l_debug = 1 THEN
882 debug('l_query_count after executing the plans query ' || l_query_count ,'query_inbound');
883 END IF;
884 wms_plan_tasks_pvt.g_plans_tasks_record_count :=
885 wms_plan_tasks_pvt.g_plans_tasks_record_count + l_query_count;
886 END IF;
887
888 set_inbound_source_header_line;
889 /* delete the drop-pending record from the temp table, when both independent and planned
890 * task records are queried
891 */
892 --IF wms_plan_tasks_pvt.g_query_independent_tasks AND
893 IF wms_plan_tasks_pvt.g_planned_tasks_rec.is_loaded
894 THEN
895 l_planned_task := wms_plan_tasks_pvt.g_plan_task_types(2);
896 DELETE FROM wms_waveplan_tasks_temp wwtt
897 WHERE EXISTS (
898 SELECT 1
899 FROM wms_op_operation_instances wooi
900 WHERE wwtt.transaction_temp_id = wooi.source_task_id
901 AND wwtt.operation_sequence = wooi.operation_sequence
902 AND wooi.operation_status IN (1,2) -- added for bug 5172443 to delete drop-active record
903 AND wooi.complete_time IS NULL)
904 AND wwtt.plans_tasks = l_planned_task
905 AND wwtt.status_id = 4;
906 IF l_debug = 1 THEN
907 DEBUG('rows deleted '|| SQL%ROWCOUNT);
908 END IF;
909 wms_plan_tasks_pvt.g_plans_tasks_record_count :=
910 wms_plan_tasks_pvt.g_plans_tasks_record_count - SQL%ROWCOUNT;
911 END IF;
912
913 EXCEPTION
914 WHEN fnd_api.g_exc_error THEN
915 x_return_status := fnd_api.g_ret_sts_error ;
916 IF l_debug = 1 THEN
917 DEBUG(SQLERRM, 'plan_tasks.query_inbound_tasks-error');
918 END IF;
919 WHEN fnd_api.g_exc_unexpected_error THEN
920 x_return_status := fnd_api.g_ret_sts_unexp_error ;
921 IF l_debug = 1 THEN
922 DEBUG(SQLERRM, 'plan_tasks.query_inbound_tasks-unexpected error');
923 END IF;
924 WHEN OTHERS THEN
925 x_return_status := fnd_api.g_ret_sts_error ;
926 IF l_debug = 1 THEN
927 DEBUG(SQLERRM, 'plan_tasks.query_inbound_tasks-other error');
928 END IF;
929
930 END query_inbound_plan_tasks;
931
932 PROCEDURE get_plans(x_plans_query_str OUT NOCOPY VARCHAR2) IS
933 --Bug6688574 :Increased the length of l_plans_query_str from 8000 to 16000.
934 l_plans_query_str VARCHAR2(16000);
935
936 l_plans_str VARCHAR2(5000);
937 l_plans_select_str varchar2(3000);
938 l_plans_from_str varchar2(1000);
939 l_plans_where_str varchar2(3000);
940
941 l_wdth_str VARCHAR2(5000);
942 l_wdth_select_str VARCHAR2(3000);
943 l_wdth_from_str VARCHAR2(3000);
944 l_wdth_where_str VARCHAR2(3000);
945
946 l_inline_query VARCHAR2(500);
947 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
948 BEGIN
949 IF l_debug = 1 THEN
950 debug('in get_plans ','get_plans');
951 END IF;
952
953 l_inline_query := ' (Select distinct parent_line_id, reference_id, ' ||
954 ' reference, source_header, line_number '||
955 ' from wms_waveplan_tasks_temp where ' ||
956 ' task_type_id in (2,8) ' ||
957 ' and operation_plan_id is not null ' ||
958 ' and parent_line_id is not null) wwtt ';
959 /**** Get the non-completed plans query first ****/
960
961 IF wms_plan_tasks_pvt.g_is_pending_plan OR
962 wms_plan_tasks_pvt.g_is_inprogress_plan THEN
963
964 IF l_debug = 1 THEN
965 debug('pending or inprogress plans are queried ','get_plans');
966 END IF;
967 l_plans_select_str := 'SELECT ''+'',' ||
968 '''' || wms_plan_tasks_pvt.g_plan_task_types(3) || ''', ' ||
969 'mmtt.transaction_temp_id, '||
970 'mmtt.parent_line_id, ' ||
971 'mmtt.inventory_item_id, ' ||
972 'msiv.concatenated_segments, ' ||
973 'msiv.description, ' ||
974 'msiv.unit_weight, ' ||
975 'msiv.weight_uom_code, ' ||
976 'msiv.unit_volume, '||
977 'msiv.volume_uom_code, ' ||
978 'mmtt.organization_id, ' ||
979 'mmtt.revision, ' ||
980 'mmtt.subinventory_code, ' ||
981 'mmtt.locator_id, ' ||
982 'decode(milv.segment19, null, milv.concatenated_segments, null), ' ||
983 'wopi.status, ' ||
984 'wopi.status, ' ||
985 'decode(wopi.status,' ||
986 '1, ''' || wms_plan_tasks_pvt.g_plan_status_codes(1)
987 || ''', 2, '''
988 || wms_plan_tasks_pvt.g_plan_status_codes(2)
989 || ''', 3, '''
990 || wms_plan_tasks_pvt.g_plan_status_codes(3)
991 || ''', 4, '''
992 || wms_plan_tasks_pvt.g_plan_status_codes(4)
993 || ''', 5, '''
994 || wms_plan_tasks_pvt.g_plan_status_codes(5)
995 || ''', 6, '''
996 || wms_plan_tasks_pvt.g_plan_status_codes(6)
997 || ''', 7, '''
998 || wms_plan_tasks_pvt.g_plan_status_codes(7)
999 || '''), ' ||
1000 'mmtt.transaction_type_id, ' ||
1001 'mmtt.transaction_action_id, ' ||
1002 'mmtt.transaction_source_type_id, ';
1003
1004 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
1005 l_plans_select_str := l_plans_select_str
1006 || 'mtst.transaction_source_type_name, '; --transaction_source_type
1007 END IF;
1008
1009 l_plans_select_str := l_plans_select_str ||
1010 'mmtt.transaction_source_id, ' ||
1011 'mmtt.trx_source_line_id, ' ||
1012 'mmtt.transfer_organization, ';
1013
1014 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
1015 l_plans_select_str := l_plans_select_str || 'mp1.organization_code, ';
1016 END IF;
1017
1018 l_plans_select_str:=l_plans_select_str ||
1019 'mmtt.transfer_subinventory, ' ||
1020 'mmtt.transfer_to_location, ';
1021
1022 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
1023 l_plans_select_str := l_plans_select_str ||
1024 'decode(milv1.segment19, null, milv1.concatenated_segments, null), ';
1025 END IF;
1026
1027 l_plans_select_str := l_plans_select_str ||
1028 'mmtt.transaction_uom, ' ||
1029 'mmtt.transaction_quantity, '||
1030 'mmtt.standard_operation_id, ';
1031 IF wms_plan_tasks_pvt.g_user_task_type_visible = 'T' THEN
1032 l_plans_select_str := l_plans_select_str || ' NULL, ';
1033 END IF;
1034 l_plans_select_str := l_plans_select_str ||
1035 'mmtt.move_order_line_id, ' || /*move_order_line_id */
1036 'mmtt.pick_slip_number, ' || /*pick_slip_number*/
1037 'mmtt.cartonization_id, '; /*cartonization_id */
1038
1039 IF wms_plan_tasks_pvt.g_cartonization_lpn_visible = 'T' THEN
1040 l_plans_select_str := l_plans_select_str
1041 || 'wlpn2.license_plate_number, '; --cartonization_lpn
1042 END IF;
1043
1044 l_plans_select_str := l_plans_select_str ||
1045 'mmtt.allocated_lpn_id, '; /*allocated_lpn_id*/
1046
1047 IF wms_plan_tasks_pvt.g_allocated_lpn_visible = 'T' THEN
1048 l_plans_select_str := l_plans_select_str
1049 || 'wlpn1.license_plate_number, '; --allocated_lpn
1050 END IF;
1051
1052 l_plans_select_str := l_plans_select_str ||
1053 'mmtt.container_item_id, '; --container_item_id
1054
1055 IF wms_plan_tasks_pvt.g_container_item_visible = 'T' THEN
1056 l_plans_select_str := l_plans_select_str
1057 || 'msiv1.concatenated_segments, '; --container_item
1058 END IF;
1059
1060 l_plans_select_str := l_plans_select_str ||
1061 'mmtt.lpn_id, '; --from_lpn_id
1062
1063 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
1064 l_plans_select_str := l_plans_select_str ||
1065 'wlpn5.license_plate_number, '; --from_lpn
1066 END IF;
1067
1068 l_plans_select_str := l_plans_select_str ||
1069 'mmtt.content_lpn_id, '; --content_lpn_id
1070
1071 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
1072 l_plans_select_str := l_plans_select_str
1073 || 'wlpn3.license_plate_number, '; --content_lpn
1074 END IF;
1075
1076 l_plans_select_str := l_plans_select_str ||'mmtt.transfer_lpn_id, ';
1077
1078 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
1079 l_plans_select_str := l_plans_select_str || 'wlpn4.license_plate_number, ';
1080 END IF;
1081 l_plans_select_str := l_plans_select_str ||
1082 'mmtt.last_update_date, ' || /*mmtt_last_update_date*/
1083 'mmtt.last_updated_by, ' || /*mmtt_last_updated_by*/
1084 'mmtt.task_priority, ' || /*priority*/
1085 'mmtt.task_priority, ' || /*priority_original */
1086 'mmtt.wms_task_type, ' || /*task_type_id */
1087 'decode(mmtt.wms_task_type,'
1088 || '1, '''
1089 || wms_plan_tasks_pvt.g_task_types(1)
1090 || ''', 2, '''
1091 || wms_plan_tasks_pvt.g_task_types(2)
1092 || ''', 3, '''
1093 || wms_plan_tasks_pvt.g_task_types(3)
1094 || ''', 4, '''
1095 || wms_plan_tasks_pvt.g_task_types(4)
1096 || ''', 5, '''
1097 || wms_plan_tasks_pvt.g_task_types(5)
1098 || ''', 6, '''
1099 || wms_plan_tasks_pvt.g_task_types(6)
1100 || ''', 7, '''
1101 || wms_plan_tasks_pvt.g_task_types(7)
1102 || ''', 8, '''
1103 || wms_plan_tasks_pvt.g_task_types(8)
1104 || '''), ' ||
1105 'mmtt.creation_date, ' || /*creation_time */
1106 'mmtt.operation_plan_id, '; /*operation_plan_id*/
1107 IF wms_plan_tasks_pvt.g_operation_plan_visible = 'T' THEN
1108 l_plans_select_str := l_plans_select_str ||
1109 'wop.operation_plan_name, '; /*operation_plan*/
1110 END IF;
1111
1112 IF wms_plan_tasks_pvt.g_operation_sequence_visible = 'T' THEN
1113 l_plans_select_str := l_plans_select_str || 'to_number(null), '; --operation_sequence
1114 END IF;
1115
1116 l_plans_select_str := l_plans_select_str ||
1117 'wopi.op_plan_instance_id op_plan_instance_id, '|| /*op_plan_instance_id*/
1118 --'to_number(null), '|| /*operation_sequence*/
1119 'to_number(null), ' || /*task_id*/
1120 'to_number(null), ' || /*person_id*/
1121 'to_number(null), '|| /*person_id_original*/
1122 'null, ' || /*person*/
1123 'to_date(null), ' || /*effective_start_date*/
1124 'to_date(null), ' || /*effective_end_date*/
1125 'to_number(null), '; /*person_resource_id*/
1126 IF wms_plan_tasks_pvt.g_person_resource_visible = 'T' THEN
1127 l_plans_select_str := l_plans_select_str ||
1128 'null, ';/*person_resource_code*/
1129 END IF;
1130 l_plans_select_str := l_plans_select_str ||
1131 'to_number(null), '; /*machine_resource_id*/
1132 IF wms_plan_tasks_pvt.g_machine_resource_visible = 'T' THEN
1133 l_plans_select_str := l_plans_select_str ||
1134 'null, '; /*machine_resource_code*/
1135 END IF;
1136 l_plans_select_str := l_plans_select_str ||
1137 'null, ' || /*equipment_instance*/
1138 'to_date(null), ' || /*dispatched_time*/
1139 'to_date(null), ' || /*loaded_time*/
1140 'to_date(null), ' || /*drop_off_time*/
1141 'to_date(null), ' || /*wdt_last_update_date*/
1142 'to_number(null), ' || /*wdt_last_updated_by*/
1143 '''N'', ' || /*is_modified */
1144 'mmtt.secondary_uom_code, '||
1145 'mmtt.secondary_transaction_quantity ';
1146
1147
1148 /* If inbound specific query is made, select more fields */
1149 IF wms_plan_tasks_pvt.g_include_inbound THEN
1150 l_plans_select_str := l_plans_select_str
1151 || ', wwtt.reference_id '
1152 || ', wwtt.reference ';
1153 END IF;
1154
1155 IF wms_plan_tasks_pvt.g_inbound_specific_query THEN
1156 l_plans_select_str := l_plans_select_str
1157 || ', wwtt.source_header '
1158 || ', wwtt.line_number ';
1159 END IF;
1160
1161 IF l_debug = 1 THEN
1162 debug('l_plans_select_str ' ||l_plans_select_str,'get_plans');
1163 END IF;
1164 /** Now build the 'from' part of the query **/
1165 l_plans_from_str := ' FROM mtl_material_transactions_temp mmtt '
1166 -- || ', mtl_system_items_kfv msiv '
1167 || ', mtl_system_items_vl msiv ' --Bug 11798973 Modified to fetch translated value
1168 || ', mtl_item_locations_kfv milv '
1169 || ', wms_op_plan_instances wopi '
1170 || ', wms_op_plans_vl wop ';
1171
1172 IF wms_plan_tasks_pvt.g_item_category_id IS NOT NULL
1173 OR wms_plan_tasks_pvt.g_category_set_id IS NOT NULL THEN
1174 l_plans_from_str := l_plans_from_str || ', mtl_item_categories mic ';
1175 END IF;
1176
1177 IF wms_plan_tasks_pvt.g_allocated_lpn_visible = 'T' then
1178 l_plans_from_str := l_plans_from_str || ', wms_license_plate_numbers wlpn1 ';
1179 END IF;
1180
1181 IF wms_plan_tasks_pvt.g_cartonization_lpn_visible = 'T' THEN
1182 l_plans_from_str := l_plans_from_str || ', wms_license_plate_numbers wlpn2 ';
1183 END IF;
1184
1185 IF wms_plan_tasks_pvt.g_container_item_visible = 'T' THEN
1186 l_plans_from_str := l_plans_from_str || ', mtl_system_items_kfv msiv1 ';
1187 END IF;
1188
1189 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
1190 l_plans_from_str := l_plans_from_str || ', wms_license_plate_numbers wlpn5 ';
1191 END IF;
1192
1193 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
1194 l_plans_from_str := l_plans_from_str || ', wms_license_plate_numbers wlpn3 ';
1195 END IF;
1196
1197 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
1198 l_plans_from_str := l_plans_from_str || ', wms_license_plate_numbers wlpn4 ';
1199 END IF;
1200
1201 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
1202 l_plans_from_str := l_plans_from_str || ', mtl_parameters mp1 ';
1203 END IF;
1204 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
1205 l_plans_from_str := l_plans_from_str || ', mtl_item_locations_kfv milv1 ';
1206 END IF;
1207
1208 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
1209 l_plans_from_str := l_plans_from_str || ', mtl_txn_source_types mtst ';
1210 END IF;
1211
1212 IF wms_plan_tasks_pvt.g_inbound_specific_query OR
1213 wms_plan_tasks_pvt.g_include_inbound THEN
1214 l_plans_from_str := l_plans_from_str || ', ' || l_inline_query;
1215 END IF;
1216
1217 IF l_debug = 1 THEN
1218 debug('l_plans_from_str ' || l_plans_from_str,'get_plans');
1219 END IF;
1220 /** Build the plans 'where' clause **/
1221 l_plans_where_str := ' WHERE 1=1 ';
1222 l_plans_where_str := l_plans_where_str
1223 || 'AND mmtt.organization_id = msiv.organization_id '
1224 || 'AND mmtt.inventory_item_id = msiv.inventory_item_id '
1225 || 'AND mmtt.organization_id = milv.organization_id(+) '
1226 || 'AND mmtt.locator_id = milv.inventory_location_id(+) '
1227 || 'AND mmtt.operation_plan_id = wop.operation_plan_id '
1228 || 'AND mmtt.transaction_temp_id = wopi.source_task_id '
1229 || 'and mmtt.parent_line_id is null '
1230 || 'and mmtt.operation_plan_id is not null ';
1231
1232 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
1233 l_plans_where_str := l_plans_where_str
1234 || 'AND mmtt.transfer_subinventory = milv1.subinventory_code(+) '
1235 || 'AND mmtt.transfer_to_location = milv1.inventory_location_id(+) ';
1236 end if;
1237 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
1238 l_plans_where_str := l_plans_where_str
1239 || 'AND mmtt.transaction_source_type_id = mtst.transaction_source_type_id ';
1240 end if;
1241 IF wms_plan_tasks_pvt.g_allocated_lpn_visible = 'T' then
1242 l_plans_where_str := l_plans_where_str
1243 || 'AND mmtt.allocated_lpn_id = wlpn1.lpn_id(+) ';
1244 end if;
1245 IF wms_plan_tasks_pvt.g_cartonization_lpn_visible = 'T' then
1246 l_plans_where_str := l_plans_where_str
1247 || 'AND mmtt.cartonization_id = wlpn2.lpn_id(+) ';
1248 end if;
1249 IF wms_plan_tasks_pvt.g_container_item_visible = 'T' then
1250 l_plans_where_str := l_plans_where_str
1251 || 'AND mmtt.container_item_id = msiv1.inventory_item_id(+) '
1252 || 'AND mmtt.organization_id = msiv1.organization_id(+) ';
1253 end if;
1254 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
1255 l_plans_where_str := l_plans_where_str
1256 || 'AND mmtt.lpn_id = wlpn5.lpn_id(+) ';
1257 END IF;
1258 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
1259 l_plans_where_str := l_plans_where_str
1260 || 'AND mmtt.content_lpn_id = wlpn3.lpn_id(+) ';
1261 end if;
1262 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
1263 l_plans_where_str := l_plans_where_str
1264 || 'AND mmtt.transfer_lpn_id = wlpn4.lpn_id(+) ';
1265 end if;
1266 IF wms_plan_tasks_pvt.g_organization_id IS NOT NULL THEN
1267 l_plans_where_str := l_plans_where_str
1268 || 'AND mmtt.organization_id = :org_id ';
1269 END IF;
1270 IF wms_plan_tasks_pvt.g_subinventory_code IS NOT NULL THEN
1271 l_plans_where_str := l_plans_where_str
1272 || 'AND mmtt.subinventory_code = :sub_code ';
1273 END IF;
1274
1275 IF wms_plan_tasks_pvt.g_locator_id IS NOT NULL THEN
1276 l_plans_where_str := l_plans_where_str
1277 || 'AND mmtt.locator_id = :loc_id ';
1278 END IF;
1279
1280 IF wms_plan_tasks_pvt.g_to_subinventory_code IS NOT NULL THEN
1281 l_plans_where_str := l_plans_where_str
1282 || 'AND mmtt.transfer_subinventory = :to_sub_code ';
1283 END IF;
1284
1285 IF wms_plan_tasks_pvt.g_to_locator_id IS NOT NULL THEN
1286 l_plans_where_str := l_plans_where_str
1287 || 'AND mmtt.transfer_to_location = :to_loc_id ';
1288 END IF;
1289
1290 IF wms_plan_tasks_pvt.g_inventory_item_id IS NOT NULL THEN
1291 l_plans_where_str := l_plans_where_str
1292 || 'AND mmtt.inventory_item_id = :item_id ';
1293 END IF;
1294
1295 IF wms_plan_tasks_pvt.g_category_set_id IS NOT NULL THEN
1296 l_plans_where_str := l_plans_where_str
1297 || 'AND mic.category_set_id = :category_set_id ';
1298 END IF;
1299
1300 IF wms_plan_tasks_pvt.g_item_category_id IS NOT NULL THEN
1301 l_plans_where_str := l_plans_where_str
1302 || 'AND mmtt.inventory_item_id = mic.inventory_item_id '
1303 || 'AND mic.organization_id = mmtt.organization_id '
1304 || 'AND mic.category_id = :item_category_id ';
1305 END IF;
1306
1307 IF wms_plan_tasks_pvt.g_user_task_type_id IS NOT NULL THEN
1308 l_plans_where_str := l_plans_where_str
1309 || 'AND mmtt.standard_operation_id = :user_task_type_id ';
1310 END IF;
1311
1312 IF wms_plan_tasks_pvt.g_from_task_quantity IS NOT NULL THEN
1313 l_plans_where_str := l_plans_where_str
1314 || 'AND mmtt.transaction_quantity >= :from_task_quantity ';
1315 END IF;
1316
1317 IF wms_plan_tasks_pvt.g_to_task_quantity IS NOT NULL THEN
1318 l_plans_where_str := l_plans_where_str
1319 || 'AND mmtt.transaction_quantity <= :to_task_quantity ';
1320 END IF;
1321
1322 IF wms_plan_tasks_pvt.g_from_task_priority IS NOT NULL THEN
1323 l_plans_where_str := l_plans_where_str
1324 || 'AND mmtt.task_priority <= :from_task_priority ';
1325 END IF;
1326
1327 IF wms_plan_tasks_pvt.g_to_task_priority IS NOT NULL THEN
1328 l_plans_where_str := l_plans_where_str
1329 || 'AND mmtt.task_priority <= :to_task_priority ';
1330 END IF;
1331
1332 IF wms_plan_tasks_pvt.g_from_creation_date IS NOT NULL THEN
1333 l_plans_where_str := l_plans_where_str
1334 -- || 'AND mmtt.creation_date >= :from_creation_date ';--commented in bug 6854145
1335 || 'AND TRUNC(mmtt.creation_date) >= TRUNC(:from_creation_date) ';--Added TRUNC in bug 6854145
1336 END IF;
1337
1338 IF wms_plan_tasks_pvt.g_to_creation_date IS NOT NULL THEN
1339 l_plans_where_str := l_plans_where_str
1340 --|| 'AND mmtt.creation_date <= :to_creation_date ';--commented in bug 6854145
1341 || 'AND TRUNC(mmtt.creation_date) <= TRUNC(:to_creation_date) ';--Added TRUNC in bug 6854145
1342 END IF;
1343 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
1344 l_plans_where_str := l_plans_where_str
1345 || 'AND mmtt.transfer_organization = mp1.organization_id(+) ';
1346 end if;
1347
1348 /** The following section handles the plan_specific query **/
1349 if wms_plan_tasks_pvt.g_plan_type_id is not null then
1350 l_plans_where_str := l_plans_where_str
1351 || 'AND wop.plan_type_id = :plan_type_id ';
1352 end if;
1353 if wms_plan_tasks_pvt.g_op_plan_id is not null then
1354 l_plans_where_str := l_plans_where_str
1355 || 'AND wop.operation_plan_id = :op_plan_id ';
1356 end if;
1357
1358 l_plans_where_str := l_plans_where_str || ' AND wopi.status in (';
1359 IF wms_plan_tasks_pvt.g_is_pending_plan THEN
1360 l_plans_where_str := l_plans_where_str || '1';
1361 IF wms_plan_tasks_pvt.g_is_inprogress_plan THEN
1362 l_plans_where_str := l_plans_where_str || ',6';
1363 END IF;
1364 ELSIF wms_plan_tasks_pvt.g_is_inprogress_plan THEN
1365 l_plans_where_str := l_plans_where_str || '6';
1366 END IF;
1367
1368 l_plans_where_str := l_plans_where_str || ')';
1369
1370
1371 /** The following section handles the inbound specific query
1372 The challenge here is - the link between the plan mmtt/wdth
1373 record is lost because MMTT.move_order_line_id is made null.
1374 So we have to fetch the records via the child records.
1375 This requires that the task records are already inserted into wwtt.
1376 We then fetch all parent records of the task records already
1377 inserted into wwtt.
1378 But now we do not check if the planned_task records are already
1379 inserted into wwtt or not. This validation should be taken care of
1380 in the calling program - In this case, it is get_inbound_tasks
1381 **/
1382 IF wms_plan_tasks_pvt.g_inbound_specific_query OR
1383 wms_plan_tasks_pvt.g_include_inbound THEN
1384 l_plans_where_str := l_plans_where_str
1385 || ' AND mmtt.transaction_temp_id = wwtt.parent_line_id ';
1386 END IF;
1387 /* Filter based on the status - pending/inprogress. */
1388 l_plans_str := l_plans_select_str || l_plans_from_str || l_plans_where_str;
1389
1390 END IF; --if pending or inprogress
1391
1392
1393 /* For completed/cancelled/aborted plans, call the procedure get_wdth_plan_records */
1394 IF wms_plan_tasks_pvt.g_is_completed_plan OR
1395 wms_plan_tasks_pvt.g_is_cancelled_plan OR
1396 wms_plan_tasks_pvt.g_is_aborted_plan THEN
1397 IF l_debug = 1 THEN
1398 debug('completed/canceled/aborted plans are queried.calling get_wdth_plan_records ','get_plans');
1399 END IF;
1400 get_wdth_plan_records(l_wdth_select_str, l_wdth_from_str, l_wdth_where_str);
1401
1402 IF l_debug = 1 THEN
1403 debug('l_wdth_select_str from get_wdth_plan_records: ' ||l_wdth_select_str,'get_plans');
1404 debug('l_wdth_from_str from get_wdth_plan_records: ' ||l_wdth_from_str,'get_plans');
1405 debug('l_wdth_where_str from get_wdth_plan_records: ' ||l_wdth_where_str,'get_plans');
1406 END IF;
1407
1408 /** The following section handles the plan_specific query **/
1409 if wms_plan_tasks_pvt.g_plan_type_id is not null then
1410 l_wdth_where_str := l_wdth_where_str
1411 || 'AND wop.plan_type_id = :plan_type_id ';
1412 end if;
1413 if wms_plan_tasks_pvt.g_op_plan_id is not null then
1414 l_wdth_where_str := l_wdth_where_str
1415 || 'AND wop.operation_plan_id = :op_plan_id ';
1416 end if;
1417 /* If inbound specific query or inbound query is made, append the inline query to the
1418 from clause
1419 */
1420 IF wms_plan_tasks_pvt.g_inbound_specific_query OR
1421 wms_plan_tasks_pvt.g_include_inbound THEN
1422 l_wdth_from_str := l_wdth_from_str || ', ' || l_inline_query;
1423 END IF;
1424 IF wms_plan_tasks_pvt.g_inbound_specific_query OR
1425 wms_plan_tasks_pvt.g_include_inbound THEN
1426 IF l_debug = 1 THEN
1427 debug('inbound specific query is made ','get_plans');
1428 END IF;
1429 l_wdth_where_str := l_wdth_where_str
1430 || ' AND wdth.transaction_id = wwtt.parent_line_id ';
1431
1432 IF l_debug = 1 THEN
1433 debug('l_wdth_where_str after appending inbound specific query ' || l_wdth_where_str,'get_plans');
1434 END IF;
1435 END IF;
1436 l_wdth_str := l_wdth_select_str || l_wdth_from_str || l_wdth_where_str;
1437 IF l_debug = 1 THEN
1438 debug('l_wdth_str ' || l_wdth_str,'get_plans');
1439 END IF;
1440 END IF;
1441
1442 IF l_plans_str IS NOT NULL THEN
1443 IF l_debug = 1 THEN
1444 debug('l_plans_str is not null' || l_plans_str,'get_plans');
1445 END IF;
1446 l_plans_query_str := l_plans_str;
1447 IF l_debug = 1 THEN
1448 debug('l_plans_query_str ' || l_plans_query_str,'get_plans');
1449 END IF;
1450 IF l_wdth_str IS NOT NULL THEN
1451 IF l_debug = 1 THEN
1452 debug('l_plans_str is not null and l_wdth_str is not null','get_plans');
1453 END IF;
1454 l_plans_query_str := l_plans_str || ' UNION ALL ' || l_wdth_str;
1455 IF l_debug = 1 THEN
1456 debug('l_plans_query_str ' || l_plans_query_str,'get_plans');
1457 END IF;
1458 END IF;
1459 ELSIF l_wdth_str IS NOT NULL THEN
1460 IF l_debug = 1 THEN
1461 debug('l_wdth_str is not null','get_plans');
1462 END IF;
1463 l_plans_query_str := l_wdth_str;
1464 IF l_debug = 1 THEN
1465 debug('l_plans_query_str ' || l_plans_query_str,'get_plans');
1466 END IF;
1467 END IF;
1468
1469 x_plans_query_str := l_plans_query_str;
1470 END get_plans;
1471
1472 PROCEDURE get_tasks(x_tasks_query_str OUT NOCOPY VARCHAR2, p_summary_mode NUMBER DEFAULT 0) IS
1473 l_tasks_query_str wms_plan_tasks_pvt.long_sql:= '';
1474
1475 l_tasks_str wms_plan_tasks_pvt.long_sql := NULL;
1476 l_tasks_select_str wms_plan_tasks_pvt.short_sql := NULL;
1477 l_tasks_from_str wms_plan_tasks_pvt.short_sql:= NULL;
1478 l_tasks_where_str wms_plan_tasks_pvt.short_sql:= NULL;
1479
1480 l_completed_tasks_str wms_plan_tasks_pvt.long_sql:= NULL;
1481 l_completed_tasks_select_str wms_plan_tasks_pvt.long_sql:= NULL; /* Bug 5507934 */
1482 l_completed_tasks_from_str wms_plan_tasks_pvt.long_sql:= NULL;
1483 l_completed_tasks_where_str wms_plan_tasks_pvt.long_sql:= NULL;
1484
1485 l_completed_records_str wms_plan_tasks_pvt.long_sql:= NULL;
1486 l_completed_records_select_str wms_plan_tasks_pvt.long_sql:= NULL;
1487 l_completed_records_from_str wms_plan_tasks_pvt.long_sql:= NULL;
1488 l_completed_records_where_str wms_plan_tasks_pvt.long_sql:= NULL; /* End of Bug 5507934 */
1489
1490 l_inbound_select_str wms_plan_tasks_pvt.short_sql:= NULL;
1491 l_inbound_from_str wms_plan_tasks_pvt.short_sql:= NULL;
1492 l_inbound_where_str wms_plan_tasks_pvt.short_sql:= NULL;
1493
1494 l_outbound_from_str wms_plan_tasks_pvt.short_sql:= NULL;
1495 l_outbound_where_str wms_plan_tasks_pvt.short_sql:= NULL;
1496
1497 l_is_pending boolean;
1498 l_is_loaded boolean;
1499 l_is_completed boolean;
1500 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1501 BEGIN
1502 IF l_debug = 1 THEN
1503 debug('in get_tasks ', 'get_tasks');
1504 END IF;
1505 if wms_plan_tasks_pvt.g_planned_tasks_rec.is_pending = TRUE OR
1506 wms_plan_tasks_pvt.g_is_pending_task = TRUE then
1507 IF l_debug = 1 THEN
1508 debug('query for pending task','get_tasks');
1509 END IF;
1510 l_is_pending := TRUE;
1511 ELSE
1512 IF l_debug = 1 THEN
1513 debug('do not query for pending task','get_tasks');
1514 END IF;
1515 l_is_pending := false;
1516 end if;
1517
1518 if wms_plan_tasks_pvt.g_planned_tasks_rec.is_loaded = TRUE OR
1519 wms_plan_tasks_pvt.g_is_loaded_task = TRUE then
1520 l_is_loaded := TRUE;
1521 IF l_debug = 1 THEN
1522 debug('query for loaded task','get_tasks');
1523 END IF;
1524 ELSE
1525 IF l_debug = 1 THEN
1526 debug('do not query for loaded task ','get_tasks');
1527 END IF;
1528 l_is_loaded := false;
1529 end if;
1530
1531 if wms_plan_tasks_pvt.g_planned_tasks_rec.is_completed = TRUE OR
1532 wms_plan_tasks_pvt.g_is_completed_task = TRUE then
1533 l_is_completed := TRUE;
1534 IF l_debug = 1 THEN
1535 debug('query for completed task ','get_tasks');
1536 END IF;
1537 ELSE
1538 IF l_debug = 1 THEN
1539 debug('do not query for completed task ','get_tasks');
1540 END IF;
1541 l_is_completed := false;
1542 end if;
1543
1544 IF l_is_pending OR l_is_loaded THEN
1545 IF l_debug = 1 THEN
1546 debug('query for loaded and pending tasks. calling get_generic_select ','get_tasks');
1547 END IF;
1548 -- Bug #3754781 +1 line.
1549 g_inbound_cycle := TRUE;
1550
1551 IF p_summary_mode = 1 THEN
1552 l_tasks_select_str := ' SELECT mmtt.wms_task_type, count(*) ';
1553 ELSE
1554 l_tasks_select_str := wms_waveplan_tasks_pvt.get_generic_select(
1555 p_is_pending => l_is_pending
1556 , p_is_loaded => l_is_loaded
1557 , p_is_completed => FALSE
1558 );
1559 END IF; -- p_summary_mode
1560 -- Bug #3754781 +1 line.
1561 g_inbound_cycle := FALSE;
1562 IF l_debug = 1 THEN
1563 debug('after calling get_generic_select :','get_tasks');
1564 debug('l_tasks_select_str ' || l_tasks_select_str,'get_tasks');
1565 debug('query for loaded and pending tasks. calling get_generic_from ','get_tasks');
1566 END IF;
1567 l_tasks_from_str :=
1568 wms_waveplan_tasks_pvt.get_generic_from(
1569 p_is_loaded => l_is_loaded
1570 , p_is_completed => FALSE
1571 , p_item_category_id => wms_plan_tasks_pvt.g_item_category_id
1572 , p_category_set_id => wms_plan_tasks_pvt.g_category_set_id
1573 );
1574
1575 l_tasks_where_str :=
1576 wms_waveplan_tasks_pvt.get_generic_where(
1577 p_add => wms_plan_tasks_pvt.g_is_add
1578 , p_organization_id => wms_plan_tasks_pvt.g_organization_id
1579 , p_subinventory_code => wms_plan_tasks_pvt.g_subinventory_code
1580 , p_locator_id => wms_plan_tasks_pvt.g_locator_id
1581 , p_to_subinventory_code => wms_plan_tasks_pvt.g_to_subinventory_code
1582 , p_to_locator_id => wms_plan_tasks_pvt.g_to_locator_id
1583 , p_inventory_item_id => wms_plan_tasks_pvt.g_inventory_item_id
1584 , p_category_set_id => wms_plan_tasks_pvt.g_category_set_id
1585 , p_item_category_id => wms_plan_tasks_pvt.g_item_category_id
1586 , p_person_id => wms_plan_tasks_pvt.g_person_id
1587 , p_person_resource_id => wms_plan_tasks_pvt.g_person_resource_id
1588 , p_equipment_type_id => wms_plan_tasks_pvt.g_equipment_type_id
1589 , p_machine_resource_id => wms_plan_tasks_pvt.g_machine_resource_id
1590 , p_machine_instance => wms_plan_tasks_pvt.g_machine_instance
1591 , p_from_task_quantity => wms_plan_tasks_pvt.g_from_task_quantity
1592 , p_to_task_quantity => wms_plan_tasks_pvt.g_to_task_quantity
1593 , p_from_creation_date => wms_plan_tasks_pvt.g_from_creation_date
1594 , p_to_creation_date => wms_plan_tasks_pvt.g_to_creation_date
1595 , p_is_pending => l_is_pending
1596 , p_is_loaded => l_is_loaded
1597 , p_is_completed => FALSE
1598 );
1599
1600 /* Now add where clauses specific to planned_tasks or independent_tasks*/
1601 if (wms_plan_tasks_pvt.g_is_pending_task = TRUE
1602 and wms_plan_tasks_pvt.g_planned_tasks_rec.is_pending = TRUE) OR
1603 (wms_plan_tasks_pvt.g_is_loaded_task = TRUE
1604 and wms_plan_tasks_pvt.g_planned_tasks_rec.is_loaded = TRUE)
1605 then
1606 IF l_debug = 1 THEN
1607 debug('both independent and planned_tasks are queried ','get_tasks');
1608 END IF;
1609 null;
1610 elsif (wms_plan_tasks_pvt.g_is_pending_task = TRUE
1611 and not wms_plan_tasks_pvt.g_planned_tasks_rec.is_pending = TRUE) OR
1612 (wms_plan_tasks_pvt.g_is_loaded_task = TRUE
1613 and NOT wms_plan_tasks_pvt.g_planned_tasks_rec.is_loaded = TRUE)
1614 then
1615 IF l_debug = 1 THEN
1616 debug('only independent tasks are queried ','get_tasks');
1617 END IF;
1618 l_tasks_where_str := l_tasks_where_str || ' and mmtt.operation_plan_id is null';
1619
1620 elsif (not wms_plan_tasks_pvt.g_is_pending_task = TRUE
1621 and wms_plan_tasks_pvt.g_planned_tasks_rec.is_pending = TRUE) OR
1622 (NOT wms_plan_tasks_pvt.g_is_loaded_task = TRUE
1623 and wms_plan_tasks_pvt.g_planned_tasks_rec.is_loaded = TRUE)
1624 then
1625 l_tasks_where_str := l_tasks_where_str || ' and mmtt.operation_plan_id is not null';
1626 --l_tasks_where_str := l_tasks_where_str || ' and wooi.complete_time is not null';
1627 IF l_debug = 1 THEN
1628 debug('only planned tasks are queried ','get_tasks');
1629 END IF;
1630 end if;
1631
1632 /* If inbound specific query is made, get the inbound specific select, from
1633 and where clauses, append them to the final query for non-completed tasks.
1634 */
1635 IF wms_plan_tasks_pvt.g_inbound_specific_query OR
1636 wms_plan_tasks_pvt.g_include_inbound OR
1637 wms_plan_tasks_pvt.g_include_crossdock THEN
1638 IF l_debug = 1 THEN
1639 debug('inbound specific query is made. calling get_inbound_specific_query ','get_tasks');
1640 END IF;
1641 get_inbound_specific_query(x_inbound_select_str => l_inbound_select_str
1642 ,x_inbound_from_str => l_inbound_from_str
1643 ,x_inbound_where_str => l_inbound_where_str
1644 ,p_is_completed_rec => 0);
1645
1646 IF wms_plan_tasks_pvt.g_include_crossdock THEN
1647 IF l_debug = 1 THEN
1648 debug('Crossdock query is made. calling get_inbound_specific_query ','get_tasks');
1649 END IF;
1650 /* Bug 5259318 */
1651 /*IF wms_plan_tasks_pvt.g_outbound_specific_query THEN*/
1652 get_outbound_specific_query(x_outbound_from_str => l_outbound_from_str
1653 ,x_outbound_where_str => l_outbound_where_str
1654 );
1655 l_outbound_where_str := l_outbound_where_str || ' AND mtrl.BACKORDER_DELIVERY_DETAIL_ID = wdd.delivery_detail_id ';
1656 /*ELSE
1657 l_outbound_from_str := NULL;
1658 l_outbound_where_str := ' AND mtrl.BACKORDER_DELIVERY_DETAIL_ID IS NOT NULL ';
1659 END IF;*/
1660 /* End of Bug 5259318 */
1661 ELSE
1662 l_inbound_where_str := l_inbound_where_str || ' AND mtrl.BACKORDER_DELIVERY_DETAIL_ID IS NULL '; -- Bug 5472012
1663 END IF;
1664
1665 IF p_summary_mode = 0 THEN
1666 l_tasks_select_str := l_tasks_select_str || l_inbound_select_str; -- only for detailed select include the inbound specific select
1667 END IF;
1668 l_tasks_from_str := l_tasks_from_str || l_inbound_from_str || l_outbound_from_str;
1669 l_tasks_where_str := l_tasks_where_str || l_inbound_where_str || l_outbound_where_str;
1670 END IF;
1671 /* If plan specific query is made, get the plan specific select, from and
1672 where clauses, append them to the final query for non-completed tasks.
1673 */
1674 if wms_plan_tasks_pvt.g_op_plan_id is not null THEN
1675 l_tasks_where_str := l_tasks_where_str ||
1676 ' and mmtt.operation_plan_id = :op_plan_id ';
1677 END IF;
1678
1679 IF p_summary_mode = 1 THEN
1680 l_tasks_where_str := l_tasks_where_str || ' GROUP BY mmtt.wms_task_type ';
1681 END IF;
1682
1683 l_tasks_str := l_tasks_select_str || l_tasks_from_str || l_tasks_where_str;
1684 IF l_debug = 1 THEN
1685 debug('l_tasks_str: ' || l_tasks_str,'get_tasks');
1686 END IF;
1687 END IF; --if l_is_pending OR l_is_loaded
1688
1689 /* Now fetch the completed tasks - We have two different procedures for
1690 * fetching the completed task records - one for planned_tasks and the other
1691 * for independent tasks
1692 */
1693
1694 /* Fetch the planned_task completed records - either if the user has chosen
1695 * the task status 'completed' on the tasks tab + user also checked the
1696 * planned_task checkbox in inbound plans tab (OR) user has chosen to view the
1697 * 'completed' plans and/or 'In Progress' plans
1698 */
1699
1700 /* Bug 3540981 When the user performs manual drop into a receiving sub/loc,
1701 * the task should be shown as an independent task. This record is archived
1702 * only in WDTH, but not in MMT. Hence we need to fetch records from WDTH
1703 * always even when the user queries for independent tasks also. i.e., a
1704 * 'Union All' with string returned from get_completed_records is present
1705 * always. But based on the query combination of Planned and Independent tasks
1706 * the where clause would be modified
1707 */
1708
1709 -- #1 Fetch records from WDTH first
1710 IF l_is_completed THEN
1711 get_completed_records(l_completed_records_select_str,
1712 l_completed_records_from_str,
1713 l_completed_records_where_str);
1714 /* If inbound specific query is made,
1715 * #1 Call the function get_inbound_specific_query_str to fetch the tasks
1716 * specific and inbound specific additional query.
1717 * #2 Append the string returned from this function to l_tasks_query_str.
1718 */
1719
1720 IF wms_plan_tasks_pvt.g_inbound_specific_query = TRUE OR
1721 wms_plan_tasks_pvt.g_include_inbound OR
1722 wms_plan_tasks_pvt.g_include_crossdock THEN
1723 IF l_debug = 1 THEN
1724 debug('inbound specific query is made','get_tasks');
1725 END IF;
1726 get_inbound_specific_query(x_inbound_select_str => l_inbound_select_str
1727 ,x_inbound_from_str => l_inbound_from_str
1728 ,x_inbound_where_str => l_inbound_where_str
1729 ,p_is_completed_rec => 1);
1730 l_inbound_where_str := l_inbound_where_str
1731 || ' AND wdth.source_document_id = rt.transaction_id (+) ';
1732
1733 IF wms_plan_tasks_pvt.g_include_crossdock THEN
1734 IF l_debug = 1 THEN
1735 debug('Crossdock query is made','get_tasks');
1736 END IF;
1737 /* Bug 5259318 */
1738 /*IF wms_plan_tasks_pvt.g_outbound_specific_query THEN*/
1739 get_outbound_specific_query(x_outbound_from_str => l_outbound_from_str
1740 ,x_outbound_where_str => l_outbound_where_str
1741 );
1742 /*ELSE
1743 l_outbound_from_str := NULL;
1744 l_outbound_where_str := ' AND mtrl.BACKORDER_DELIVERY_DETAIL_ID IS NOT NULL ';
1745 END IF;
1746 ELSE
1747 --l_inbound_where_str := l_inbound_where_str || ' AND mtrl.BACKORDER_DELIVERY_DETAIL_ID IS NULL ';
1748 null; /* Bug 5223606 */
1749 END IF; --Crossdock query
1750 /* End of Bug 5259318 */
1751
1752 l_completed_records_select_str := l_completed_records_select_str
1753 || l_inbound_select_str;
1754 l_completed_records_from_str := l_completed_records_from_str
1755 || l_inbound_from_str || l_outbound_from_str;
1756 l_completed_records_where_str := l_completed_records_where_str
1757 || l_inbound_where_str|| l_outbound_where_str;
1758
1759 /* If plan specific query is made, get the plan specific select, from and
1760 * where clauses, append them to the final query for non-completed tasks.
1761 */
1762 if wms_plan_tasks_pvt.g_op_plan_id is not null THEN
1763 l_completed_records_where_str := l_completed_records_where_str ||
1764 ' and wdth.operation_plan_id = :op_plan_id ';
1765 END IF;
1766
1767
1768 end if; --inbound specific query
1769
1770 /* Filter on operation_plan_id only if either planned_task or independent
1771 * tasks are queried, but not both together. In the latter case, we dont
1772 * filter on operation_plan_id
1773 */
1774 IF wms_plan_tasks_pvt.g_query_independent_tasks AND
1775 wms_plan_tasks_pvt.g_planned_tasks_rec.is_completed = FALSE THEN
1776 l_completed_records_where_str := l_completed_records_where_str ||
1777 ' and wdth.operation_plan_id is null ';
1778 ELSIF wms_plan_tasks_pvt.g_planned_tasks_rec.is_completed = TRUE AND
1779 wms_plan_tasks_pvt.g_query_independent_tasks = FALSE THEN
1780 l_completed_records_where_str := l_completed_records_where_str
1781 || 'and wdth.operation_plan_id is not null ';
1782 END IF;
1783
1784 /* Filter based on the task_type */
1785
1786 debug('Filter based on the task_type');
1787
1788 l_completed_records_where_str := l_completed_records_where_str ||
1789 ' and wdth.task_type IN (2,8) ' ||
1790 ' and ('||
1791 ' (wdth.transaction_source_type_id = 1 ' ||
1792 ' and wdth.transaction_action_id = 27) OR '||
1793 ' (wdth.transaction_source_type_id = 12 ' ||
1794 ' and wdth.transaction_action_id = 27) OR '||
1795 ' (wdth.transaction_source_type_id = 7 ' ||
1796 ' and wdth.transaction_action_id = 12) OR '||
1797 ' (wdth.transaction_source_type_id = 13 ' ||
1798 ' and wdth.transaction_action_id = 12) OR '||
1799 ' (wdth.transaction_source_type_id = 4 ' || --Bug #3789492
1800 ' and wdth.transaction_action_id = 2) OR '|| --Bug #3789492
1801 ' (wdth.transaction_source_type_id = 4 ' || --Bug #5231114
1802 ' and wdth.transaction_action_id = 27) '|| --Bug #5231114
1803 ' OR (wdth.transaction_source_type_id = 5 ' || -- Code for Bug#8467334
1804 ' and wdth.transaction_action_id = 31) ' || -- Code for Bug#8467334
1805 ') ';
1806
1807 ---fix for bug 6826562
1808 IF wms_plan_tasks_pvt.g_subinventory_code IS NOT NULL THEN
1809 l_completed_records_where_str := l_completed_records_where_str
1810 || 'AND wdth.source_subinventory_code = :sub_code ';
1811 END IF;
1812
1813 IF wms_plan_tasks_pvt.g_locator_id IS NOT NULL THEN
1814 l_completed_records_where_str := l_completed_records_where_str
1815 || 'AND wdth.source_locator_id = :loc_id ';
1816 END IF;
1817
1818 IF wms_plan_tasks_pvt.g_to_subinventory_code IS NOT NULL THEN
1819 l_completed_records_where_str := l_completed_records_where_str
1820 || 'AND wdth.dest_subinventory_code = :to_sub_code ';
1821 END IF;
1822
1823 IF wms_plan_tasks_pvt.g_to_locator_id IS NOT NULL THEN
1824 l_completed_records_where_str := l_completed_records_where_str -- Modified for bug 14243507
1825 || 'AND wdth.dest_locator_id = :to_loc_id ';
1826 END IF;
1827 ---fix for bug 6826562
1828
1829 IF p_summary_mode = 0 THEN
1830 l_completed_records_str := l_completed_records_select_str ||
1831 l_completed_records_from_str ||
1832 l_completed_records_where_str;
1833 ELSE
1834 l_completed_records_str := ' SELECT wdth.task_type,count(*) ' || l_completed_records_from_str ||
1835 l_completed_records_where_str || ' GROUP BY wdth.task_type ';
1836 END IF;
1837
1838 IF l_debug = 1 THEN
1839 debug('completed_records_str ' || l_completed_records_str);
1840 END IF;
1841 --fetching the completed independent tasks
1842 if wms_plan_tasks_pvt.g_is_completed_task
1843 AND wms_plan_tasks_pvt.g_query_independent_tasks then
1844
1845 /*Bug 3627575:Setting the variable to true for Inbound queries*/
1846 wms_plan_tasks_pvt.g_from_inbound :=TRUE;
1847
1848 IF l_debug = 1 THEN
1849 debug('fetching the completed independent tasks ','get_tasks');
1850 END IF;
1851 g_inbound_cycle := TRUE;
1852 IF p_summary_mode = 1 THEN
1853 l_completed_tasks_select_str := ' SELECT wdth.task_type, count(*) ';
1854 ELSE
1855 l_completed_tasks_select_str := wms_waveplan_tasks_pvt.get_generic_select(
1856 p_is_pending => FALSE
1857 , p_is_loaded => FALSE
1858 , p_is_completed => l_is_completed
1859 );
1860 END IF;
1861
1862 g_inbound_cycle := FALSE;
1863
1864 l_completed_tasks_from_str :=
1865 wms_waveplan_tasks_pvt.get_generic_from(
1866 p_is_loaded => FALSE
1867 , p_is_completed => l_is_completed
1868 , p_item_category_id => wms_plan_tasks_pvt.g_item_category_id
1869 , p_category_set_id => wms_plan_tasks_pvt.g_category_set_id
1870 );
1871
1872 l_completed_tasks_where_str :=
1873 wms_waveplan_tasks_pvt.get_generic_where(
1874 p_add => wms_plan_tasks_pvt.g_is_add
1875 , p_organization_id => wms_plan_tasks_pvt.g_organization_id
1876 , p_subinventory_code => wms_plan_tasks_pvt.g_subinventory_code
1877 , p_locator_id => wms_plan_tasks_pvt.g_locator_id
1878 , p_to_subinventory_code => wms_plan_tasks_pvt.g_to_subinventory_code
1879 , p_to_locator_id => wms_plan_tasks_pvt.g_to_locator_id
1880 , p_inventory_item_id => wms_plan_tasks_pvt.g_inventory_item_id
1881 , p_category_set_id => wms_plan_tasks_pvt.g_category_set_id
1882 , p_item_category_id => wms_plan_tasks_pvt.g_item_category_id
1883 , p_person_id => wms_plan_tasks_pvt.g_person_id
1884 , p_person_resource_id => wms_plan_tasks_pvt.g_person_resource_id
1885 , p_equipment_type_id => wms_plan_tasks_pvt.g_equipment_type_id
1886 , p_machine_resource_id => wms_plan_tasks_pvt.g_machine_resource_id
1887 , p_machine_instance => wms_plan_tasks_pvt.g_machine_instance
1888 , p_from_task_quantity => wms_plan_tasks_pvt.g_from_task_quantity
1889 , p_to_task_quantity => wms_plan_tasks_pvt.g_to_task_quantity
1890 , p_from_creation_date => wms_plan_tasks_pvt.g_from_creation_date
1891 , p_to_creation_date => wms_plan_tasks_pvt.g_to_creation_date
1892 , p_is_pending => FALSE
1893 , p_is_loaded => FALSE
1894 , p_is_completed => l_is_completed
1895 );
1896
1897 l_tasks_where_str := l_tasks_where_str || ' and wdth.operation_plan_id is null';
1898
1899 IF wms_plan_tasks_pvt.g_inbound_specific_query OR
1900 wms_plan_tasks_pvt.g_include_inbound OR
1901 wms_plan_tasks_pvt.g_include_crossdock THEN
1902
1903 IF l_debug = 1 THEN
1904 debug('inbound specific query is made. calling get_inbound_specific_query ','get_tasks');
1905 END IF;
1906 get_inbound_specific_query(x_inbound_select_str => l_inbound_select_str
1907 ,x_inbound_from_str => l_inbound_from_str
1908 ,x_inbound_where_str => l_inbound_where_str
1909 ,p_is_completed_rec => 1);
1910 /*Bug 3627575:Made the join with rcv_transactions an outer join*/
1911 l_inbound_where_str := l_inbound_where_str
1912 || ' AND mmt.rcv_transaction_id = rt.transaction_id(+) ';
1913 /*Bug 4000897:Added the where clause to fetch only non-planned tasks*/
1914 l_inbound_where_str := l_inbound_where_str
1915 || ' AND wdth.is_parent IS NULL ';
1916 /*Bug 3627575:added filter for task type for Inbound tasks*/
1917 l_inbound_where_str := l_inbound_where_str
1918 || ' AND wdth.task_type in (2,8) ';
1919 l_inbound_where_str := l_inbound_where_str||' and ('||
1920 ' (wdth.transaction_source_type_id = 1 ' ||
1921 ' and wdth.transaction_action_id = 27) OR '||
1922 ' (wdth.transaction_source_type_id = 12 ' ||
1923 ' and wdth.transaction_action_id = 27) OR '||
1924 ' (wdth.transaction_source_type_id = 7 ' ||
1925 ' and wdth.transaction_action_id = 12) OR '||
1926 ' (wdth.transaction_source_type_id = 13 ' ||
1927 ' and wdth.transaction_action_id = 12) OR '||
1928 ' (wdth.transaction_source_type_id = 4 ' || --Bug #3789492
1929 ' and wdth.transaction_action_id = 2) OR'|| --Bug #3789492
1930 ' (wdth.transaction_source_type_id = 4 ' || --Bug #5231114
1931 ' and wdth.transaction_action_id = 27) '|| --Bug #5231114
1932 ') ';
1933
1934 ---fix for bug 6826562
1935
1936 IF wms_plan_tasks_pvt.g_subinventory_code IS NOT NULL THEN
1937 l_completed_tasks_where_str := l_completed_tasks_where_str
1938 || 'AND wdth.source_subinventory_code = :sub_code ';
1939 END IF;
1940
1941 IF wms_plan_tasks_pvt.g_locator_id IS NOT NULL THEN
1942 l_completed_tasks_where_str := l_completed_tasks_where_str
1943 || 'AND wdth.source_locator_id = :loc_id ';
1944 END IF;
1945
1946 IF wms_plan_tasks_pvt.g_to_subinventory_code IS NOT NULL THEN
1947 l_completed_tasks_where_str := l_completed_tasks_where_str
1948 || 'AND wdth.dest_subinventory_code = :to_sub_code ';
1949 END IF;
1950
1951 IF wms_plan_tasks_pvt.g_to_locator_id IS NOT NULL THEN
1952 l_completed_tasks_where_str := l_completed_tasks_where_str
1953 || 'AND wdth.dest_locator_id = :to_loc_id ';
1954 END IF;
1955
1956 ---fix for bug 6826562
1957
1958 IF wms_plan_tasks_pvt.g_include_crossdock THEN
1959 IF l_debug = 1 THEN
1960 debug('Crossdock query is made. calling get_inbound_specific_query ','get_tasks');
1961 END IF;
1962 l_completed_tasks_from_str := l_completed_tasks_from_str || ', mtl_txn_request_lines mtrl';
1963 l_completed_tasks_where_str := l_completed_tasks_where_str || ' AND wdth.organization_id = mtrl.organization_id ';
1964 l_completed_tasks_where_str := l_completed_tasks_where_str || ' AND wdth.inventory_item_id = mtrl.inventory_item_id ';
1965 l_completed_tasks_where_str := l_completed_tasks_where_str || ' AND wdth.move_order_line_id = mtrl.line_id ';
1966 /* Bug 5259318 */
1967 /*IF wms_plan_tasks_pvt.g_outbound_specific_query THEN*/
1968 -- for crossdock tasks with outbound criteria specified
1969 l_completed_tasks_where_str := l_completed_tasks_where_str || ' AND mtrl.backorder_delivery_detail_id = wdd.delivery_detail_id ';
1970 get_outbound_specific_query(x_outbound_from_str => l_outbound_from_str
1971 ,x_outbound_where_str => l_outbound_where_str
1972 );
1973 /*ELSE
1974 -- for crossdock tasks without outbound criteria specified
1975 debug('!@#$%^&*(): not calling get_outbound specific query' ,'get_tasks');
1976 l_completed_tasks_where_str := l_completed_tasks_where_str || ' AND mtrl.backorder_delivery_detail_id is not null ';
1977 END IF;*/
1978 /* End of Bug 5259318 */
1979 ELSE
1980 -- for inbound tasks
1981 /*l_completed_tasks_where_str := l_completed_tasks_where_str || ' AND mtrl.backorder_delivery_detail_id is null ';*/
1982 null; /* Bug 5223606 */
1983 END IF;
1984
1985 IF p_summary_mode = 0 THEN
1986 l_completed_tasks_select_str := l_completed_tasks_select_str || l_inbound_select_str;
1987 END IF;
1988 l_completed_tasks_from_str := l_completed_tasks_from_str || l_inbound_from_str || l_outbound_from_str;
1989 l_completed_tasks_where_str := l_completed_tasks_where_str || l_inbound_where_str || l_outbound_where_str;
1990
1991 IF p_summary_mode = 1 THEN
1992 l_completed_tasks_where_str := l_completed_tasks_where_str || ' GROUP BY wdth.task_type ' ;
1993 END IF;
1994
1995 END IF;
1996
1997 l_completed_tasks_str := l_completed_tasks_select_str || l_completed_tasks_from_str ||
1998 l_completed_tasks_where_str;
1999 IF l_debug = 1 THEN
2000 debug('l_completed_tasks_str: ' || l_completed_tasks_str,'get_tasks');
2001 END IF;
2002 end if;
2003
2004 END IF; --if l_is_completed
2005 /* Now we have 1 to 3 strings :
2006 l_tasks_str - string for the non-completed tasks(independent+planned)
2007 l_completed_tasks_str - String for the completed independent_tasks
2008 l_completed_records_str - string for the completed planned_tasks
2009 */
2010 IF l_tasks_str IS NOT NULL THEN
2011 IF l_debug = 1 THEN
2012 debug('l_tasks_str is not null ','get_tasks');
2013 END IF;
2014 l_tasks_query_str := l_tasks_str;
2015 IF l_completed_tasks_str IS NOT NULL THEN
2016 l_tasks_query_str := l_tasks_query_str || ' UNION ALL ' || l_completed_tasks_str;
2017 IF l_debug = 1 THEN
2018 debug('l_tasks_str is not null..l_completed_tasks_str is not null ','get_tasks');
2019 debug('l_tasks_query_str: ' || l_tasks_query_str,'get_tasks');
2020 END IF;
2021 END IF;
2022 IF l_completed_records_str IS NOT NULL THEN
2023 IF l_debug = 1 THEN
2024 debug('l_completed_records_str is not null ','get_tasks');
2025 END IF;
2026 l_tasks_query_str := l_tasks_query_str || ' UNION ALL ' ||
2027 l_completed_records_str;
2028 IF l_debug = 1 THEN
2029 debug('l_tasks_str is not null..l_completed_records_str is not null ','get_tasks');
2030 debug('l_tasks_query_str: ' || l_tasks_query_str,'get_tasks');
2031 END IF;
2032 END IF;
2033
2034 ELSIF l_completed_tasks_str IS NOT NULL THEN
2035 IF l_debug = 1 THEN
2036 debug('l_completed_tasks_str is not null ','get_tasks');
2037 END IF;
2038 l_tasks_query_str := l_completed_tasks_str;
2039 IF l_completed_records_str IS NOT NULL THEN
2040 IF l_debug = 1 THEN
2041 debug('l_completed_records_str is not null ','get_tasks');
2042 END IF;
2043 l_tasks_query_str := l_tasks_query_str || ' UNION ALL ' ||
2044 l_completed_records_str;
2045 IF l_debug = 1 THEN
2046 debug('l_completed_tasks_str is not null and l_completed_records_str is not null','get_tasks');
2047 END IF;
2048 END IF;
2049 ELSIF l_completed_records_str IS NOT NULL THEN
2050 l_tasks_query_str := l_completed_records_str;
2051 END IF;
2052 x_tasks_query_str := l_tasks_query_str;
2053 IF l_debug = 1 THEN
2054 debug('returning l_tasks_query_str: ' || l_tasks_query_str,'get_tasks');
2055 END IF;
2056 END get_tasks;
2057
2058 PROCEDURE get_col_list(x_col_list_str OUT NOCOPY VARCHAR2) IS
2059 l_col_list_str wms_plan_tasks_pvt.short_sql;
2060 BEGIN
2061 l_col_list_str := 'expansion_code'
2062 || ', plans_tasks'
2063 || ', transaction_temp_id '
2064 || ', parent_line_id '
2065 || ', inventory_item_id '
2066 || ', item '
2067 || ', item_description '
2068 || ', unit_weight '
2069 || ', weight_uom_code '
2070 || ', unit_volume '
2071 || ', volume_uom_code '
2072 || ', organization_id '
2073 || ', revision '
2074 || ', subinventory '
2075 || ', locator_id '
2076 || ', locator '
2077 || ', status_id '
2078 || ', status_id_original '
2079 || ', status '
2080 || ', transaction_type_id '
2081 || ', transaction_action_id '
2082 || ', transaction_source_type_id ';
2083
2084 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
2085 l_col_list_str := l_col_list_str || ', transaction_source_type ';
2086 END IF;
2087
2088 l_col_list_str := l_col_list_str
2089 || ', transaction_source_id '
2090 || ', transaction_source_line_id '
2091 || ', to_organization_id ';
2092
2093 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
2094 l_col_list_str := l_col_list_str || ', to_organization_code ';
2095 END IF;
2096
2097 l_col_list_str := l_col_list_str
2098 || ', to_subinventory '
2099 || ', to_locator_id ';
2100
2101 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
2102 l_col_list_str := l_col_list_str || ', to_locator ';
2103 END IF;
2104
2105 l_col_list_str := l_col_list_str
2106 || ', transaction_uom '
2107 || ', transaction_quantity '
2108 || ', user_task_type_id ';
2109 IF wms_plan_tasks_pvt.g_user_task_type_visible = 'T' THEN
2110 l_col_list_str := l_col_list_str || ', user_task_type ';
2111 END IF;
2112 l_col_list_str := l_col_list_str
2113 || ', move_order_line_id '
2114 || ', pick_slip_number '
2115 || ', cartonization_id ';
2116
2117 IF wms_plan_tasks_pvt.g_cartonization_lpn_visible = 'T' THEN
2118 l_col_list_str := l_col_list_str || ', cartonization_lpn ';
2119 END IF;
2120
2121 l_col_list_str := l_col_list_str || ', allocated_lpn_id ';
2122
2123 IF wms_plan_tasks_pvt.g_allocated_lpn_visible = 'T' THEN
2124 l_col_list_str := l_col_list_str || ', allocated_lpn ';
2125 END IF;
2126
2127 l_col_list_str := l_col_list_str || ', container_item_id ';
2128
2129 IF wms_plan_tasks_pvt.g_container_item_visible = 'T' THEN
2130 l_col_list_str := l_col_list_str || ', container_item ';
2131 END IF;
2132
2133 l_col_list_str := l_col_list_str || ', from_lpn_id ';
2134
2135 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
2136 l_col_list_str := l_col_list_str || ', from_lpn ';
2137 END IF;
2138
2139 l_col_list_str := l_col_list_str || ', content_lpn_id ';
2140
2141 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
2142 l_col_list_str := l_col_list_str || ', content_lpn ';
2143 END IF;
2144
2145 l_col_list_str := l_col_list_str || ', to_lpn_id ';
2146
2147 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
2148 l_col_list_str := l_col_list_str || ', to_lpn ';
2149 END IF;
2150
2151 l_col_list_str := l_col_list_str
2152 || ', mmtt_last_update_date '
2153 || ', mmtt_last_updated_by '
2154 || ', priority '
2155 || ', priority_original '
2156 || ', task_type_id '
2157 || ', task_type '
2158 || ', creation_time '
2159 || ', operation_plan_id ';
2160 IF wms_plan_tasks_pvt.g_operation_plan_visible = 'T' THEN
2161 l_col_list_str := l_col_list_str
2162 || ', operation_plan ';
2163 END IF;
2164 IF wms_plan_tasks_pvt.g_operation_sequence_visible = 'T' THEN
2165 l_col_list_str := l_col_list_str || ', operation_sequence ';
2166 END IF;
2167 l_col_list_str := l_col_list_str
2168 || ', op_plan_instance_id '
2169 --|| ', operation_sequence '
2170 || ', task_id '
2171 || ', person_id '
2172 || ', person_id_original '
2173 || ', person '
2174 || ', effective_start_date '
2175 || ', effective_end_date '
2176 || ', person_resource_id ';
2177 IF wms_plan_tasks_pvt.g_person_resource_visible = 'T' THEN
2178 l_col_list_str := l_col_list_str
2179 || ', person_resource_code ';
2180 END IF;
2181 l_col_list_str := l_col_list_str
2182 || ', machine_resource_id ';
2183 IF wms_plan_tasks_pvt.g_machine_resource_visible = 'T' THEN
2184 l_col_list_str := l_col_list_str
2185 || ', machine_resource_code ';
2186 END IF;
2187 l_col_list_str := l_col_list_str
2188 || ', equipment_instance '
2189 || ', dispatched_time '
2190 || ', loaded_time '
2191 || ', drop_off_time '
2192 || ', wdt_last_update_date '
2193 || ', wdt_last_updated_by '
2194 || ', is_modified '
2195 || ', secondary_transaction_uom '
2196 || ', secondary_transaction_quantity ';
2197 IF wms_plan_tasks_pvt.g_include_inbound OR wms_plan_tasks_pvt.g_include_crossdock THEN
2198 l_col_list_str := l_col_list_str
2199 || ', reference_id '
2200 || ', reference ';
2201 END IF;
2202
2203 IF wms_plan_tasks_pvt.g_inbound_specific_query THEN
2204 l_col_list_str := l_col_list_str
2205 || ', source_header '
2206 || ', line_number ';
2207 END IF;
2208
2209 /* Bug 5259318 */
2210 IF wms_plan_tasks_pvt.g_include_crossdock THEN
2211 l_col_list_str := l_col_list_str || ', delivery ';
2212 END IF;
2213 /* End of Bug 5259318 */
2214
2215 x_col_list_str := l_col_list_str;
2216 END get_col_list;
2217
2218 /* This procedure fetched the 'Planned_task' records from WDTH */
2219 procedure get_completed_records(x_wdth_select_str OUT NOCOPY varchar2,
2220 x_wdth_from_str OUT NOCOPY varchar2,
2221 x_wdth_where_str OUT NOCOPY varchar2) is
2222 l_wdth_select_str wms_plan_tasks_pvt.short_sql:= NULL;
2223 l_wdth_from_str wms_plan_tasks_pvt.short_sql:= NULL;
2224 l_wdth_where_str wms_plan_tasks_pvt.short_sql:= NULL;
2225 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2226
2227 begin
2228 /**** First build the Select string ****/
2229 IF l_debug = 1 THEN
2230 debug('in get_completed_records ','get_completed_records');
2231 END IF;
2232 l_wdth_select_str := 'SELECT '
2233 || 'decode(wdth.is_parent,''N'',null,''+''), '
2234 || 'decode(wdth.is_parent,''N'', decode(wdth.operation_plan_id,null,'
2235 || 'decode(wdth.parent_transaction_id,null,'''
2236 || wms_plan_tasks_pvt.g_plan_task_types(1) || ''','''
2237 || wms_plan_tasks_pvt.g_plan_task_types(5)
2238 || '''),'''|| wms_plan_tasks_pvt.g_plan_task_types(2)
2239 || '''),decode(wdth.transaction_action_id,28,'''||wms_plan_tasks_pvt.g_plan_task_types(4)
2240 || ''',''' || wms_plan_tasks_pvt.g_plan_task_types(3) || ''')), ' /*plan_task */
2241 || 'wdth.transaction_id, ' /* transaction_temp_id */
2242 || 'wdth.parent_transaction_id, ' /*parent_line_id*/
2243 || 'wdth.inventory_item_id, ' /*inventory_item_id*/
2244 || 'msiv.concatenated_segments, '/*item*/
2245 || 'msiv.description, ' /*item description*/
2246 || 'msiv.unit_weight, ' /*unit_weight*/
2247 || 'msiv.weight_uom_code, ' /*weight_uom_code*/
2248 || 'msiv.unit_volume, ' /*unit_volume*/
2249 || 'msiv.volume_uom_code, ' /*volume_uom_code*/
2250 || 'wdth.organization_id, ' /*organization_id*/
2251 || 'wdth.revision, ' /*revision*/
2252 || 'wdth.source_subinventory_code, ' /*subinventory*/
2253 || 'wdth.source_locator_id, ' /*locator_id*/
2254 || 'decode(milv.segment19, null, milv.concatenated_segments, null), '/*locator*/
2255 || 'wdth.status, ' /*status_id*/
2256 || 'wdth.status, ' /*status_id_original*/
2257 || 'decode(wdth.status,'
2258 || '6, '''
2259 || wms_plan_tasks_pvt.g_plan_status_codes(3)
2260 || ''', 11, '''
2261 || wms_plan_tasks_pvt.g_plan_status_codes(4)
2262 || ''', 12, '''
2263 || wms_plan_tasks_pvt.g_plan_status_codes(5)/*status*/
2264 || '''), '
2265 || 'wdth.transaction_type_id, '/*transaction_type_id*/
2266 || 'wdth.transaction_action_id, '/*transaction_action_id*/
2267 || 'wdth.transaction_source_type_id, '; --transaction_source_type_id
2268
2269 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
2270 l_wdth_select_str := l_wdth_select_str
2271 || 'mtst.transaction_source_type_name, '; --transaction_source_type
2272 END IF;
2273
2274 l_wdth_select_str := l_wdth_select_str
2275 || 'to_number(null), ' /*transaction_source_id*/
2276 || 'to_number(null), ' /*transaction_source_line_id*/
2277 || 'wdth.transfer_organization_id, ';/* to_organization_id*/
2278
2279 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
2280 l_wdth_select_str := l_wdth_select_str
2281 || 'mp1.organization_code, '; /* to_organization_code */
2282 END IF;
2283 l_wdth_select_str := l_wdth_select_str
2284 || 'wdth.dest_subinventory_code, ' /*to_subinventory*/
2285 || 'wdth.dest_locator_id, '; /*to_locator_id*/
2286
2287 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
2288 l_wdth_select_str := l_wdth_select_str
2289 || 'decode(milv1.segment19, null, milv1.concatenated_segments, null), ';
2290 /* to_locator */
2291 END IF;
2292
2293 l_wdth_select_str := l_wdth_select_str
2294 || 'wdth.transaction_uom_code, ' /* transaction_uom */
2295 || 'wdth.transaction_quantity, ' /*transaction_quantity */
2296 || 'wdth.user_task_type, '; /*user_task_type_id*/
2297
2298 IF wms_plan_tasks_pvt.g_user_task_type_visible = 'T' THEN
2299
2300 l_wdth_select_str := l_wdth_select_str
2301 || 'bso.operation_code, '; --user_task_type
2302 END IF;
2303
2304
2305
2306 l_wdth_select_str := l_wdth_select_str
2307 || 'to_number(null), ' /*move_order_line_id*/
2308 || 'to_number(null), ' /*pick_slip_number*/
2309 || 'to_number(null), '; /*cartonization_id*/
2310 IF g_cartonization_lpn_visible = 'T' THEN
2311 l_wdth_select_str := l_wdth_select_str || 'null, ';
2312 --cartonization_lpn
2313 END IF;
2314 l_wdth_select_str := l_wdth_select_str
2315 || 'to_number(null), '; /*allocated_lpn_id*/
2316
2317
2318 IF wms_plan_tasks_pvt.g_allocated_lpn_visible = 'T' THEN
2319 l_wdth_select_str := l_wdth_select_str || 'null, '; --allocated_lpn
2320 END IF;
2321 l_wdth_select_str := l_wdth_select_str
2322 || 'to_number(null), '; /*container_item_id*/
2323
2324 IF g_container_item_visible = 'T' THEN
2325 l_wdth_select_str := l_wdth_select_str || 'null, '; /*container item */
2326 end if;
2327
2328 l_wdth_select_str := l_wdth_select_str || 'wdth.lpn_id, ' ; /*from_lpn_id*/
2329
2330 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
2331 l_wdth_select_str := l_wdth_select_str
2332 || 'wlpn5.license_plate_number, ' ; /*from_lpn*/
2333 END IF;
2334
2335 l_wdth_select_str := l_wdth_select_str || 'wdth.content_lpn_id, '; /*content_lpn_id*/
2336
2337 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
2338 l_wdth_select_str := l_wdth_select_str
2339 || 'wlpn3.license_plate_number, '; --content_lpn
2340 END IF;
2341
2342 l_wdth_select_str := l_wdth_select_str
2343 || 'wdth.transfer_lpn_id, '; --to_lpn_id
2344
2345 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
2346 l_wdth_select_str := l_wdth_select_str
2347 || 'wlpn4.license_plate_number, '; --to_lpn
2348 END IF;
2349 l_wdth_select_str := l_wdth_select_str
2350 || 'to_date(null), ' /*mmt_last_update_date*/
2351 || 'to_number(null), ' /*mmt_last_updated_by*/
2352 || 'wdth.priority, '/*priority*/
2353 || 'wdth.priority, ' /*priority_original*/
2354 || 'wdth.task_type, ' /*task_type_id*/
2355 || 'decode(wdth.task_type,'
2356 || '1, '''
2357 || wms_plan_tasks_pvt.g_task_types(1)
2358 || ''', 2, '''
2359 || wms_plan_tasks_pvt.g_task_types(2)
2360 || ''', 3, '''
2361 || wms_plan_tasks_pvt.g_task_types(3)
2362 || ''', 4, '''
2363 || wms_plan_tasks_pvt.g_task_types(4)
2364 || ''', 5, '''
2365 || wms_plan_tasks_pvt.g_task_types(5)
2366 || ''', 6, '''
2367 || wms_plan_tasks_pvt.g_task_types(6)
2368 || ''', 7, '''
2369 || wms_plan_tasks_pvt.g_task_types(7)
2370 || ''', 8, '''
2371 || wms_plan_tasks_pvt.g_task_types(8)
2372 || '''), '/*task*/
2373 || 'to_date(null), ' /*creation_time */
2374 || 'wdth.operation_plan_id, ';/*operation_plan_id*/
2375 IF wms_plan_tasks_pvt.g_operation_plan_visible = 'T' THEN
2376 l_wdth_select_str := l_wdth_select_str
2377 || 'wop.operation_plan_name, ';/*operation_plan*/
2378 END IF;
2379 IF wms_plan_tasks_pvt.g_operation_sequence_visible = 'T' THEN
2380 l_wdth_select_str := l_wdth_select_str || ' to_number(null), '; --operation_sequence
2381 END IF;
2382 l_wdth_select_str := l_wdth_select_str
2383 || 'to_number(wdth.op_plan_instance_id), '/*operation_instance_id*/
2384 --|| 'to_number(null), '/*operation_sequence*/
2385 || 'wdth.task_id, '/*task_id*/
2386 || 'wdth.person_id, '/*person_id*/
2387 || 'wdth.person_id, ';/*person_id_original*/
2388
2389 IF wms_plan_tasks_pvt.g_person_visible = 'T' THEN
2390 l_wdth_select_str := l_wdth_select_str || 'pap.full_name, '; --person_id
2391 END IF;
2392
2393 l_wdth_select_str := l_wdth_select_str
2394 || 'wdth.effective_start_date, '/*effective_start_date*/
2395 || 'wdth.effective_end_date, '/*effective_end_date*/
2396 || 'wdth.person_resource_id, '; /*person_resource_id*/
2397
2398 IF wms_plan_tasks_pvt.g_person_resource_visible = 'T' THEN
2399 l_wdth_select_str := l_wdth_select_str
2400 || 'br1.resource_code, '; --person_resource_code
2401 END IF;
2402
2403 l_wdth_select_str := l_wdth_select_str
2404 || 'wdth.machine_resource_id, '; --machine_resource_id
2405
2406 IF wms_plan_tasks_pvt.g_machine_resource_visible = 'T' THEN
2407 l_wdth_select_str := l_wdth_select_str
2408 || 'br2.resource_code, '; --machine_resource_code
2409 END IF;
2410 l_wdth_select_str := l_wdth_select_str
2411 || 'wdth.equipment_instance, '/*equipment_instance*/
2412 || 'wdth.dispatched_time, '/*dispatched_time*/
2413 || 'wdth.loaded_time, '/*loaded_time*/
2414 || 'wdth.drop_off_time, '/*drop_off_time*/
2415 || 'to_date(null), '/*wdt_last_update_date*/
2416 || 'to_number(null), '/*wdt_last_updated_by*/
2417 || '''N'', '/*is modified*/
2418 || 'wdth.SECONDARY_TRANSACTION_UOM_CODE, '
2419 || 'wdth.secondary_transaction_quantity ';
2420
2421
2422 /**** Now build the From string ****/
2423 l_wdth_from_str := ' FROM wms_dispatched_tasks_history wdth'
2424 || ', mtl_txn_request_lines mtrl '
2425 -- || ', mtl_system_items_kfv msiv '
2426 || ', mtl_system_items_vl msiv ' --Bug 11798973 Modified to fetch translated value
2427
2428 || ', mtl_item_locations_kfv milv ';
2429
2430 IF wms_plan_tasks_pvt.g_include_crossdock THEN /* CKR */
2431 l_wdth_from_str := l_wdth_from_str || ', mtl_txn_request_headers mtrh ';
2432 END IF;
2433
2434 IF wms_plan_tasks_pvt.g_item_category_id IS NOT NULL
2435 OR wms_plan_tasks_pvt.g_category_set_id IS NOT NULL THEN
2436 l_wdth_from_str := l_wdth_from_str
2437 || ', mtl_item_categories mic ';
2438 END IF;
2439
2440 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
2441 l_wdth_from_str := l_wdth_from_str
2442 || ', wms_license_plate_numbers wlpn5 ';
2443 END IF;
2444
2445 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
2446 l_wdth_from_str := l_wdth_from_str
2447 || ', wms_license_plate_numbers wlpn3 ';
2448 END IF;
2449
2450 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
2451 l_wdth_from_str := l_wdth_from_str
2452 || ', wms_license_plate_numbers wlpn4 ';
2453 END IF;
2454
2455 IF wms_plan_tasks_pvt.g_user_task_type_visible = 'T' THEN
2456 l_wdth_from_str := l_wdth_from_str
2457 || ', bom_standard_operations bso ';
2458 END IF;
2459
2460 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
2461 l_wdth_from_str := l_wdth_from_str || ', mtl_parameters mp1 ';
2462 END IF;
2463
2464 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
2465 l_wdth_from_str := l_wdth_from_str
2466 || ', mtl_txn_source_types mtst ';
2467 END IF;
2468
2469 l_wdth_from_str := l_wdth_from_str || ', wms_op_plans_vl wop ';
2470
2471 IF wms_plan_tasks_pvt.g_person_resource_visible = 'T' THEN
2472 l_wdth_from_str := l_wdth_from_str || ', bom_resources br1 ';
2473 END IF;
2474
2475
2476 IF wms_plan_tasks_pvt.g_machine_resource_visible = 'T' THEN
2477 l_wdth_from_str := l_wdth_from_str || ', bom_resources br2 ';
2478 END IF;
2479
2480 IF wms_plan_tasks_pvt.g_person_visible = 'T' THEN
2481 l_wdth_from_str := l_wdth_from_str
2482 || ', per_all_people_f pap ';
2483 END IF;
2484 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
2485 l_wdth_from_str := l_wdth_from_str || ', mtl_item_locations_kfv milv1 ';
2486 END IF;
2487
2488
2489 /**** Now get the Where string ****/
2490 l_wdth_where_str := 'WHERE 1=1 ';
2491 l_wdth_where_str := l_wdth_where_str
2492 || 'AND wdth.organization_id = msiv.organization_id '
2493 || 'AND wdth.inventory_item_id = msiv.inventory_item_id '
2494 || 'AND wdth.organization_id = milv.organization_id(+) '
2495 || 'AND wdth.source_locator_id = milv.inventory_location_id(+) '
2496 || 'AND wdth.organization_id = mtrl.organization_id '
2497 || 'AND wdth.inventory_item_id = mtrl.inventory_item_id ';
2498
2499
2500 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
2501 l_wdth_where_str := l_wdth_where_str
2502 || 'AND wdth.transfer_organization_id = mp1.organization_id(+) ';
2503 END IF;
2504
2505 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
2506 l_wdth_where_str := l_wdth_where_str
2507 || 'AND wdth.transaction_source_type_id = mtst.transaction_source_type_id (+) ';
2508 END IF;
2509
2510 IF wms_plan_tasks_pvt.g_person_resource_visible = 'T' THEN
2511 l_wdth_where_str := l_wdth_where_str
2512 || 'AND wdth.person_resource_id = br1.resource_id(+) ';
2513 END IF;
2514
2515 IF wms_plan_tasks_pvt.g_machine_resource_visible = 'T' THEN
2516 l_wdth_where_str := l_wdth_where_str
2517 || 'AND wdth.machine_resource_id = br2.resource_id(+) ';
2518 END IF;
2519 IF wms_plan_tasks_pvt.g_person_visible = 'T' THEN
2520 l_wdth_where_str := l_wdth_where_str
2521 || 'AND wdth.person_id = pap.person_id (+)'
2522 || 'AND wdth.effective_start_date >= pap.effective_start_date (+) '
2523 || 'AND wdth.effective_end_date <= pap.effective_end_date (+) ';
2524 END IF;
2525
2526 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
2527 l_wdth_where_str := l_wdth_where_str
2528 || 'AND wdth.lpn_id = wlpn5.lpn_id(+) ';
2529 END IF;
2530
2531 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
2532 l_wdth_where_str := l_wdth_where_str
2533 || 'AND wdth.content_lpn_id = wlpn3.lpn_id(+) ';
2534 END IF;
2535
2536 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
2537 l_wdth_where_str := l_wdth_where_str
2538 || 'AND wdth.transfer_lpn_id = wlpn4.lpn_id(+) ';
2539 END IF;
2540
2541 IF wms_plan_tasks_pvt.g_user_task_type_visible = 'T' THEN
2542 l_wdth_where_str := l_wdth_where_str
2543 || 'AND wdth.user_task_type = bso.standard_operation_id(+) '
2544 || 'AND wdth.organization_id = bso.organization_id(+) ';
2545 END IF;
2546
2547 l_wdth_where_str := l_wdth_where_str
2548 || 'AND wdth.operation_plan_id = wop.operation_plan_id(+) ';
2549 IF wms_plan_tasks_pvt.g_organization_id IS NOT NULL THEN
2550 l_wdth_where_str := l_wdth_where_str
2551 || 'AND wdth.organization_id = :org_id ';
2552 END IF;
2553 /* 13965757 start */
2554 IF wms_plan_tasks_pvt.g_person_id IS NOT NULL THEN
2555 l_wdth_where_str := l_wdth_where_str
2556 || 'AND wdth.person_id = :person_id ';
2557 END IF;
2558 /* 13965757 end */
2559 IF wms_plan_tasks_pvt.g_category_set_id IS NOT NULL THEN
2560 l_wdth_where_str := l_wdth_where_str
2561 || 'AND mic.category_set_id = :category_set_id ';
2562 END IF;
2563
2564 IF wms_plan_tasks_pvt.g_item_category_id IS NOT NULL THEN
2565 l_wdth_where_str := l_wdth_where_str
2566 || 'AND wdth.inventory_item_id = mic.inventory_item_id (+) '
2567 || 'AND mic.organization_id(+) = wdth.organization_id '
2568 || 'AND mic.category_id = :item_category_id ';
2569 END IF;
2570
2571 IF wms_plan_tasks_pvt.g_inventory_item_id IS NOT NULL THEN
2572 l_wdth_where_str := l_wdth_where_str
2573 || 'AND wdth.inventory_item_id = :item_id ';
2574 END IF;
2575
2576 IF wms_plan_tasks_pvt.g_from_task_quantity IS NOT NULL THEN
2577 l_wdth_where_str := l_wdth_where_str
2578 || 'AND wdth.transaction_quantity >= :from_task_quantity ';
2579 END IF;
2580
2581 IF wms_plan_tasks_pvt.g_to_task_quantity IS NOT NULL THEN
2582 l_wdth_where_str := l_wdth_where_str
2583 || 'AND wdth.transaction_quantity <= :to_task_quantity ';
2584 END IF;
2585
2586 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
2587 l_wdth_where_str := l_wdth_where_str
2588 || 'and wdth.dest_locator_id = milv1.inventory_location_id (+)'
2589 || 'AND wdth.dest_subinventory_code = milv1.subinventory_code (+)';
2590 END IF;
2591 /* Since this procedure is called for planned_tasks records only,
2592 add a where clause to restrict on operation_plan_id and is_parent
2593 */
2594 l_wdth_where_str := l_wdth_where_str
2595 || 'and wdth.is_parent = ''N'''
2596 || 'and wdth.move_order_line_id = mtrl.line_id ';
2597 -- bug5163661
2598 IF wms_plan_tasks_pvt.g_include_crossdock THEN
2599 l_wdth_where_str := l_wdth_where_str
2600 /*|| 'and mtrl.backorder_delivery_detail_id IS NOT NULL '; --= wdd.delivery_detail_id ';*/ /* Bug 5259318 */
2601 || 'and mtrl.backorder_delivery_detail_id = wdd.delivery_detail_id ';
2602 l_wdth_where_str := l_wdth_where_str
2603 || 'and mtrh.header_id = mtrl.header_id ';
2604 /* Bug 5259318 */
2605 ELSE
2606 l_wdth_where_str := l_wdth_where_str
2607 || 'and mtrl.backorder_delivery_detail_id is null '; /*Bug 5223606 */
2608 END IF;
2609
2610 /* Bug 5507934 */
2611 IF wms_plan_tasks_pvt.g_from_sales_order_id IS NOT NULL OR wms_plan_tasks_pvt.g_to_sales_order_id IS NOT NULL
2612 THEN
2613 l_wdth_where_str := l_wdth_where_str || 'AND wdd.organization_id = wdth.organization_id ';
2614 l_wdth_where_str := l_wdth_where_str || 'AND wdd.inventory_item_id = wdth.inventory_item_id ';
2615 END IF;
2616 /* End of Bug 5507934 */
2617 --BUG 6342338
2618 IF wms_plan_tasks_pvt.g_from_creation_date IS NOT NULL THEN
2619 l_wdth_where_str := l_wdth_where_str
2620 || 'AND wdth.creation_date >= :from_creation_date ';
2621 END IF;
2622
2623 IF wms_plan_tasks_pvt.g_to_creation_date IS NOT NULL THEN
2624 l_wdth_where_str := l_wdth_where_str
2625 || 'AND wdth.creation_date <= :to_creation_date ';
2626 END IF;
2627 --End Bug 6342338
2628
2629
2630 IF l_debug = 1 THEN
2631 debug('l_wdth_select_str## ' || l_wdth_select_str, 'get_completed_records');
2632 debug('l_wdth_from_str## ' || l_wdth_from_str, 'get_completed_records');
2633 debug('l_wdth_where_str## ' || l_wdth_where_str, 'get_completed_records');
2634 END IF;
2635
2636 x_wdth_select_str := l_wdth_select_str;
2637 x_wdth_from_str := l_wdth_from_str;
2638 x_wdth_where_str := l_wdth_where_str;
2639
2640 end get_completed_records;
2641
2642 /** Procedure that sets the global variables. This takes in all the fields
2643 on the form as input parameters.
2644 Each of the input parameter is a record representing each of the tabs on
2645 form
2646 **/
2647 PROCEDURE set_globals(
2648 p_organization_id NUMBER DEFAULT NULL
2649 , p_subinventory_code VARCHAR2 DEFAULT NULL
2650 , p_locator_id NUMBER DEFAULT NULL
2651 , p_to_subinventory_code VARCHAR2 DEFAULT NULL
2652 , p_to_locator_id NUMBER DEFAULT NULL
2653 , p_inventory_item_id NUMBER DEFAULT NULL
2654 , p_category_set_id NUMBER DEFAULT NULL
2655 , p_item_category_id NUMBER DEFAULT NULL
2656 , p_person_id NUMBER DEFAULT NULL
2657 , p_person_resource_id NUMBER DEFAULT NULL
2658 , p_equipment_type_id NUMBER DEFAULT NULL
2659 , p_machine_resource_id NUMBER DEFAULT NULL
2660 , p_machine_instance VARCHAR2 DEFAULT NULL
2661 , p_user_task_type_id NUMBER DEFAULT NULL
2662 , p_from_task_quantity NUMBER DEFAULT NULL
2663 , p_to_task_quantity NUMBER DEFAULT NULL
2664 , p_from_task_priority NUMBER DEFAULT NULL
2665 , p_to_task_priority NUMBER DEFAULT NULL
2666 , p_from_creation_date DATE DEFAULT NULL
2667 , p_to_creation_date DATE DEFAULT NULL
2668 , p_is_unreleased_task BOOLEAN DEFAULT FALSE
2669 , p_is_pending_task BOOLEAN DEFAULT FALSE
2670 , p_is_queued_task BOOLEAN DEFAULT FALSE
2671 , p_is_dispatched_task BOOLEAN DEFAULT FALSE
2672 , p_is_active_task BOOLEAN DEFAULT FALSE
2673 , p_is_loaded_task BOOLEAN DEFAULT FALSE
2674 , p_is_completed_task BOOLEAN DEFAULT FALSE
2675 , p_include_inbound BOOLEAN DEFAULT FALSE
2676 , p_include_outbound BOOLEAN DEFAULT FALSE
2677 , p_include_crossdock BOOLEAN DEFAULT FALSE
2678 , p_include_manufacturing BOOLEAN DEFAULT FALSE
2679 , p_include_warehousing BOOLEAN DEFAULT FALSE
2680 , p_from_po_header_id NUMBER DEFAULT NULL
2681 , p_to_po_header_id NUMBER DEFAULT NULL
2682 , p_from_purchase_order VARCHAR2 DEFAULT NULL
2683 , p_to_purchase_order VARCHAR2 DEFAULT NULL
2684 , p_from_rma_header_id NUMBER DEFAULT NULL
2685 , p_to_rma_header_id NUMBER DEFAULT NULL
2686 , p_from_rma VARCHAR2 DEFAULT NULL
2687 , p_to_rma VARCHAR2 DEFAULT NULL
2688 , p_from_requisition_header_id NUMBER DEFAULT NULL
2689 , p_to_requisition_header_id NUMBER DEFAULT NULL
2690 , p_from_requisition VARCHAR2 DEFAULT NULL
2691 , p_to_requisition VARCHAR2 DEFAULT NULL
2692 , p_from_shipment_number VARCHAR2 DEFAULT NULL
2693 , p_to_shipment_number VARCHAR2 DEFAULT NULL
2694 , p_include_sales_orders BOOLEAN DEFAULT TRUE
2695 , p_include_internal_orders BOOLEAN DEFAULT TRUE
2696 , p_from_sales_order_id NUMBER DEFAULT NULL
2697 , p_to_sales_order_id NUMBER DEFAULT NULL
2698 , p_from_pick_slip_number NUMBER DEFAULT NULL
2699 , p_to_pick_slip_number NUMBER DEFAULT NULL
2700 , p_customer_id NUMBER DEFAULT NULL
2701 , p_customer_category VARCHAR2 DEFAULT NULL
2702 , p_delivery_id NUMBER DEFAULT NULL
2703 , p_carrier_id NUMBER DEFAULT NULL
2704 , p_ship_method VARCHAR2 DEFAULT NULL
2705 , p_shipment_priority VARCHAR2 DEFAULT NULL
2706 , p_trip_id NUMBER DEFAULT NULL
2707 , p_from_shipment_date DATE DEFAULT NULL
2708 , p_to_shipment_date DATE DEFAULT NULL
2709 , p_ship_to_state VARCHAR2 DEFAULT NULL
2710 , p_ship_to_country VARCHAR2 DEFAULT NULL
2711 , p_ship_to_postal_code VARCHAR2 DEFAULT NULL
2712 , p_from_number_of_order_lines NUMBER DEFAULT NULL
2713 , p_to_number_of_order_lines NUMBER DEFAULT NULL
2714 , p_manufacturing_type VARCHAR2 DEFAULT NULL
2715 , p_from_job VARCHAR2 DEFAULT NULL
2716 , p_to_job VARCHAR2 DEFAULT NULL
2717 , p_assembly_id NUMBER DEFAULT NULL
2718 , p_from_start_date DATE DEFAULT NULL
2719 , p_to_start_date DATE DEFAULT NULL
2720 , p_from_line VARCHAR2 DEFAULT NULL
2721 , p_to_line VARCHAR2 DEFAULT NULL
2722 , p_department_id NUMBER DEFAULT NULL
2723 , p_include_replenishment BOOLEAN DEFAULT TRUE
2724 , p_from_replenishment_mo VARCHAR2 DEFAULT NULL
2725 , p_to_replenishment_mo VARCHAR2 DEFAULT NULL
2726 , p_include_mo_transfer BOOLEAN DEFAULT TRUE
2727 , p_include_mo_issue BOOLEAN DEFAULT TRUE
2728 , p_from_transfer_issue_mo VARCHAR2 DEFAULT NULL
2729 , p_to_transfer_issue_mo VARCHAR2 DEFAULT NULL
2730 , p_include_lpn_putaway BOOLEAN DEFAULT TRUE
2731 , p_include_staging_move BOOLEAN DEFAULT FALSE
2732 , p_include_cycle_count BOOLEAN DEFAULT TRUE
2733 , p_cycle_count_name VARCHAR2 DEFAULT NULL
2734 , p_query_independent_tasks BOOLEAN DEFAULT TRUE
2735 , p_query_planned_tasks BOOLEAN DEFAULT TRUE
2736 , p_is_pending_plan BOOLEAN DEFAULT FALSE
2737 , p_is_inprogress_plan BOOLEAN DEFAULT FALSE
2738 , p_is_completed_plan BOOLEAN DEFAULT FALSE
2739 , p_is_cancelled_plan BOOLEAN DEFAULT FALSE
2740 , p_is_aborted_plan BOOLEAN DEFAULT FALSE
2741 , p_activity_id NUMBER DEFAULT NULL
2742 , p_plan_type_id NUMBER DEFAULT NULL
2743 , p_op_plan_id NUMBER DEFAULT NULL
2744 , p_is_picknone_task BOOLEAN DEFAULT FALSE ) IS --ER13869750
2745
2746 l_module_name CONSTANT VARCHAR2(20) := 'set_globals';
2747 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2748
2749 BEGIN
2750 IF l_debug = 1 THEN
2751 debug(' in set_globals ','set_globals');
2752 END IF;
2753 wms_plan_tasks_pvt.g_organization_id := p_organization_id;
2754 wms_plan_tasks_pvt.g_subinventory_code := p_subinventory_code;
2755 wms_plan_tasks_pvt.g_locator_id := p_locator_id;
2756 wms_plan_tasks_pvt.g_to_subinventory_code := p_to_subinventory_code;
2757 wms_plan_tasks_pvt.g_to_locator_id := p_to_locator_id;
2758 wms_plan_tasks_pvt.g_inventory_item_id := p_inventory_item_id;
2759 wms_plan_tasks_pvt.g_category_set_id := p_category_set_id;
2760 wms_plan_tasks_pvt.g_item_category_id := p_item_category_id;
2761 wms_plan_tasks_pvt.g_person_id := p_person_id;
2762 wms_plan_tasks_pvt.g_person_resource_id := p_person_resource_id;
2763 wms_plan_tasks_pvt.g_equipment_type_id := p_equipment_type_id;
2764 wms_plan_tasks_pvt.g_machine_resource_id := p_machine_resource_id;
2765 wms_plan_tasks_pvt.g_machine_instance := p_machine_instance;
2766 wms_plan_tasks_pvt.g_user_task_type_id := p_user_task_type_id;
2767 wms_plan_tasks_pvt.g_from_task_quantity := p_from_task_quantity;
2768 wms_plan_tasks_pvt.g_to_task_quantity := p_to_task_quantity;
2769 wms_plan_tasks_pvt.g_from_task_priority := p_from_task_priority;
2770 wms_plan_tasks_pvt.g_to_task_priority := p_to_task_priority;
2771 wms_plan_tasks_pvt.g_from_creation_date := p_from_creation_date;
2772 wms_plan_tasks_pvt.g_to_creation_date := p_to_creation_date;
2773
2774 wms_plan_tasks_pvt.g_is_unreleased_task := p_is_unreleased_task;
2775 wms_plan_tasks_pvt.g_is_pending_task := p_is_pending_task;
2776 wms_plan_tasks_pvt.g_is_queued_task := p_is_queued_task;
2777 wms_plan_tasks_pvt.g_is_dispatched_task := p_is_dispatched_task;
2778 wms_plan_tasks_pvt.g_is_active_task := p_is_active_task;
2779 wms_plan_tasks_pvt.g_is_loaded_task := p_is_loaded_task;
2780 wms_plan_tasks_pvt.g_is_completed_task := p_is_completed_task ;
2781 wms_plan_tasks_pvt.g_is_picknone_task := p_is_picknone_task ; --ER13869750
2782
2783 wms_plan_tasks_pvt.g_include_inbound := p_include_inbound;
2784 wms_plan_tasks_pvt.g_include_outbound := p_include_outbound;
2785 wms_plan_tasks_pvt.g_include_crossdock := p_include_crossdock;
2786 wms_plan_tasks_pvt.g_include_manufacturing := p_include_manufacturing;
2787 wms_plan_tasks_pvt.g_include_warehousing := p_include_warehousing;
2788 wms_plan_tasks_pvt.g_from_po_header_id := p_from_po_header_id;
2789 wms_plan_tasks_pvt.g_to_po_header_id := p_to_po_header_id;
2790 wms_plan_tasks_pvt.g_from_purchase_order := p_from_purchase_order;
2791 wms_plan_tasks_pvt.g_to_purchase_order := p_to_purchase_order;
2792 wms_plan_tasks_pvt.g_from_rma_header_id := p_from_rma_header_id;
2793 wms_plan_tasks_pvt.g_to_rma_header_id := p_to_rma_header_id;
2794 wms_plan_tasks_pvt.g_from_rma := p_from_rma;
2795 wms_plan_tasks_pvt.g_to_rma := p_to_rma;
2796 wms_plan_tasks_pvt.g_from_requisition_header_id := p_from_requisition_header_id;
2797 wms_plan_tasks_pvt.g_to_requisition_header_id:= p_to_requisition_header_id;
2798 wms_plan_tasks_pvt.g_from_requisition := p_from_requisition;
2799 wms_plan_tasks_pvt.g_to_requisition := p_to_requisition;
2800 wms_plan_tasks_pvt.g_from_shipment_number := p_from_shipment_number;
2801 wms_plan_tasks_pvt.g_to_shipment_number := p_to_shipment_number;
2802 wms_plan_tasks_pvt.g_include_sales_orders := p_include_sales_orders;
2803 wms_plan_tasks_pvt.g_include_internal_orders := p_include_internal_orders;
2804 wms_plan_tasks_pvt.g_from_sales_order_id := p_from_sales_order_id;
2805 wms_plan_tasks_pvt.g_to_sales_order_id := p_to_sales_order_id;
2806 wms_plan_tasks_pvt.g_from_pick_slip_number := p_from_pick_slip_number;
2807 wms_plan_tasks_pvt.g_to_pick_slip_number := p_to_pick_slip_number;
2808 wms_plan_tasks_pvt.g_customer_id := p_customer_id;
2809 wms_plan_tasks_pvt.g_customer_category := p_customer_category;
2810 wms_plan_tasks_pvt.g_delivery_id := p_delivery_id;
2811 wms_plan_tasks_pvt.g_carrier_id := p_carrier_id;
2812 wms_plan_tasks_pvt.g_ship_method := p_ship_method;
2813 wms_plan_tasks_pvt.g_shipment_priority := p_shipment_priority;
2814 wms_plan_tasks_pvt.g_trip_id := p_trip_id;
2815 wms_plan_tasks_pvt.g_from_shipment_date := p_from_shipment_date;
2816 wms_plan_tasks_pvt.g_to_shipment_date := p_to_shipment_date;
2817 wms_plan_tasks_pvt.g_ship_to_state := p_ship_to_state;
2818 wms_plan_tasks_pvt.g_ship_to_country := p_ship_to_country;
2819 wms_plan_tasks_pvt.g_ship_to_postal_code := p_ship_to_postal_code;
2820 wms_plan_tasks_pvt.g_from_number_of_order_lines := p_from_number_of_order_lines;
2821 wms_plan_tasks_pvt.g_to_number_of_order_lines := p_to_number_of_order_lines;
2822 wms_plan_tasks_pvt.g_manufacturing_type := p_manufacturing_type;
2823 wms_plan_tasks_pvt.g_from_job := p_from_job;
2824 wms_plan_tasks_pvt.g_to_job := p_to_job;
2825 wms_plan_tasks_pvt.g_assembly_id := p_assembly_id;
2826 wms_plan_tasks_pvt.g_from_start_date := p_from_start_date;
2827 wms_plan_tasks_pvt.g_to_start_date := p_to_start_date;
2828 wms_plan_tasks_pvt.g_from_line := p_from_line;
2829 wms_plan_tasks_pvt.g_to_line := p_to_line;
2830 wms_plan_tasks_pvt.g_department_id := p_department_id;
2831 wms_plan_tasks_pvt.g_include_replenishment := p_include_replenishment;
2832 wms_plan_tasks_pvt.g_from_replenishment_mo := p_from_replenishment_mo;
2833 wms_plan_tasks_pvt.g_to_replenishment_mo := p_to_replenishment_mo;
2834 wms_plan_tasks_pvt.g_include_mo_transfer := p_include_mo_transfer;
2835 wms_plan_tasks_pvt.g_include_mo_issue := p_include_mo_issue;
2836 wms_plan_tasks_pvt.g_from_transfer_issue_mo := p_from_transfer_issue_mo;
2837 wms_plan_tasks_pvt.g_to_transfer_issue_mo := p_to_transfer_issue_mo;
2838 wms_plan_tasks_pvt.g_include_lpn_putaway := p_include_lpn_putaway;
2839 wms_plan_tasks_pvt.g_include_staging_move := p_include_staging_move;
2840 wms_plan_tasks_pvt.g_include_cycle_count := p_include_cycle_count;
2841 wms_plan_tasks_pvt.g_cycle_count_name := p_cycle_count_name;
2842
2843 wms_plan_tasks_pvt.g_query_independent_tasks := p_query_independent_tasks;
2844 wms_plan_tasks_pvt.g_query_planned_tasks := p_query_planned_tasks;
2845
2846 wms_plan_tasks_pvt.g_is_pending_plan := p_is_pending_plan;
2847 wms_plan_tasks_pvt.g_is_inprogress_plan := p_is_inprogress_plan;
2848 wms_plan_tasks_pvt.g_is_completed_plan := p_is_completed_plan;
2849 wms_plan_tasks_pvt.g_is_cancelled_plan := p_is_cancelled_plan;
2850 wms_plan_tasks_pvt.g_is_aborted_plan := p_is_aborted_plan;
2851
2852 wms_plan_tasks_pvt.g_activity_id := p_activity_id;
2853 wms_plan_tasks_pvt.g_plan_type_id := p_plan_type_id;
2854 wms_plan_tasks_pvt.g_op_plan_id := p_op_plan_id;
2855
2856 IF wms_plan_tasks_pvt.g_from_po_header_id IS NOT NULL
2857 OR wms_plan_tasks_pvt.g_to_po_header_id IS NOT NULL
2858 OR wms_plan_tasks_pvt.g_from_rma_header_id IS NOT NULL
2859 OR wms_plan_tasks_pvt.g_to_rma_header_id IS NOT NULL
2860 OR wms_plan_tasks_pvt.g_from_requisition_header_id IS NOT NULL
2861 OR wms_plan_tasks_pvt.g_to_requisition_header_id IS NOT NULL THEN
2862 wms_plan_tasks_pvt.g_inbound_specific_query := TRUE;
2863 END IF;
2864
2865 IF wms_plan_tasks_pvt.g_from_sales_order_id IS NOT NULL OR
2866 wms_plan_tasks_pvt.g_to_sales_order_id IS NOT NULL OR
2867 wms_plan_tasks_pvt.g_customer_id IS NOT NULL OR
2868 wms_plan_tasks_pvt.g_customer_category IS NOT NULL OR
2869 wms_plan_tasks_pvt.g_delivery_id IS NOT NULL OR
2870 wms_plan_tasks_pvt.g_carrier_id IS NOT NULL OR
2871 wms_plan_tasks_pvt.g_ship_method IS NOT NULL OR
2872 wms_plan_tasks_pvt.g_shipment_priority IS NOT NULL OR
2873 wms_plan_tasks_pvt.g_trip_id IS NOT NULL OR
2874 wms_plan_tasks_pvt.g_from_shipment_date IS NOT NULL OR
2875 wms_plan_tasks_pvt.g_to_shipment_date IS NOT NULL OR
2876 wms_plan_tasks_pvt.g_ship_to_state IS NOT NULL OR
2877 wms_plan_tasks_pvt.g_ship_to_country IS NOT NULL OR
2878 wms_plan_tasks_pvt.g_ship_to_postal_code IS NOT NULL THEN
2879 wms_plan_tasks_pvt.g_outbound_specific_query := TRUE;
2880 END IF;
2881
2882
2883
2884 /*
2885 IF wms_waveplan_tasks_pvt.g_task_types.count > 0 THEN
2886 FOR i IN 1..wms_waveplan_tasks_pvt.g_task_types.count LOOP
2887 wms_plan_tasks_pvt.g_task_types(i) := wms_waveplan_tasks_pvt.g_task_types(i);
2888 END LOOP;
2889 END IF;
2890
2891 IF wms_waveplan_tasks_pvt.g_task_types_orig.count > 0 THEN
2892 FOR i IN 1..wms_waveplan_tasks_pvt.g_task_types_orig.count LOOP
2893 wms_plan_tasks_pvt.g_task_types_orig(i) := wms_waveplan_tasks_pvt.g_task_types_orig(i);
2894 END LOOP;
2895 END IF;
2896
2897 IF wms_waveplan_tasks_pvt.g_status_codes.count > 0 THEN
2898 FOR i IN 1..wms_waveplan_tasks_pvt.g_status_codes.count LOOP
2899 wms_plan_tasks_pvt.g_status_codes(i) := wms_waveplan_tasks_pvt.g_status_codes(i);
2900 END LOOP;
2901 END IF;
2902
2903 IF wms_waveplan_tasks_pvt.g_status_codes_orig.count > 0 THEN
2904 FOR i IN 1..wms_waveplan_tasks_pvt.g_status_codes_orig.count LOOP
2905 wms_plan_tasks_pvt.g_status_codes_orig(i) := wms_waveplan_tasks_pvt.g_status_codes_orig(i);
2906 END LOOP;
2907 END IF;
2908
2909 IF wms_waveplan_tasks_pvt.g_plan_task_types.count > 0 THEN
2910 FOR i IN 1..wms_waveplan_tasks_pvt.g_plan_task_types.count LOOP
2911 wms_plan_tasks_pvt.g_plan_task_types(i) := wms_waveplan_tasks_pvt.g_plan_task_types(i);
2912 END LOOP;
2913 END IF;
2914
2915 IF wms_waveplan_tasks_pvt.g_plan_task_types_orig.count > 0 THEN
2916 FOR i IN 1..wms_waveplan_tasks_pvt.g_plan_task_types_orig.count LOOP
2917 wms_plan_tasks_pvt.g_plan_task_types_orig(i) := wms_waveplan_tasks_pvt.g_plan_task_types_orig(i);
2918 END LOOP;
2919 END IF;
2920
2921 IF wms_waveplan_tasks_pvt.g_plan_status_codes.count > 0 THEN
2922 FOR i IN 1..wms_waveplan_tasks_pvt.g_plan_status_codes.count LOOP
2923 wms_plan_tasks_pvt.g_plan_status_codes(i) := wms_waveplan_tasks_pvt.g_plan_status_codes(i);
2924 END LOOP;
2925 END IF;
2926
2927 IF wms_waveplan_tasks_pvt.g_plan_status_codes_orig.count > 0 THEN
2928 FOR i IN 1..wms_waveplan_tasks_pvt.g_plan_status_codes_orig.count LOOP
2929 wms_plan_tasks_pvt.g_plan_status_codes_orig(i) := wms_waveplan_tasks_pvt.g_plan_status_codes_orig(i);
2930 END LOOP;
2931 END IF;
2932 */
2933
2934 wms_plan_tasks_pvt.g_task_types := wms_waveplan_tasks_pvt.g_task_types;
2935 wms_plan_tasks_pvt.g_task_types_orig := wms_waveplan_tasks_pvt.g_task_types_orig;
2936 wms_plan_tasks_pvt.g_status_codes := wms_waveplan_tasks_pvt.g_status_codes;
2937 wms_plan_tasks_pvt.g_status_codes_orig := wms_waveplan_tasks_pvt.g_status_codes_orig;
2938 wms_plan_tasks_pvt.g_plan_task_types := wms_waveplan_tasks_pvt.g_plan_task_types;
2939 wms_plan_tasks_pvt.g_plan_task_types_orig := wms_waveplan_tasks_pvt.g_plan_task_types_orig;
2940 wms_plan_tasks_pvt.g_plan_status_codes := wms_waveplan_tasks_pvt.g_plan_status_codes;
2941 wms_plan_tasks_pvt.g_plan_status_codes_orig := wms_waveplan_tasks_pvt.g_plan_status_codes_orig;
2942
2943 wms_plan_tasks_pvt.g_plans_tasks_record_count := 0;
2944
2945 END set_globals;
2946
2947 PROCEDURE get_inbound_specific_query(
2948 x_inbound_select_str OUT NOCOPY VARCHAR2
2949 ,x_inbound_from_str OUT NOCOPY VARCHAR2
2950 ,x_inbound_where_str OUT NOCOPY VARCHAR2
2951 ,p_is_completed_rec IN NUMBER) IS
2952 l_inbound_select wms_plan_tasks_pvt.short_sql;
2953 l_inbound_from wms_plan_tasks_pvt.short_sql;
2954 l_inbound_where VARCHAR2(5000);
2955 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2956 L_MOVER_ORDER_LINE VARCHAR2(5000); --code for Bug#8467334
2957 --Hardik LSP bug 9274233
2958 l_from_client_code VARCHAR2(30);
2959 l_delimiter VARCHAR2(1);
2960 l_is_po_number VARCHAR2(60);--CLM Changes; PO number can be alphanumeric
2961 --Hardik LSP bug 9274233 End
2962 BEGIN
2963 /**** Inbound specific queries ****/
2964 --
2965 IF p_is_completed_rec = 0 THEN --not a completed record
2966 IF l_debug = 1 THEN
2967 debug(' not a completed record ','get_inbound_specific_query');
2968 END IF;
2969
2970 -- Code for Bug#8467334 .Added to popluate TXN_SOURCE_ID and transaction_source_type_id of loaded tasks in WMS_WAVEPLAN_TASKS_TEMP .
2971 -- We'll use this info -- Populate Job No In ctl Board
2972 l_inbound_select :=', Nvl2(mtrl.reference ,mtrl.reference_id , mtrl.TXN_SOURCE_ID) ' ||
2973 ' , Nvl( mtrl.reference , Decode (mtrl.transaction_source_type_id,5 , ' ||'''WIP JOB'' , NULL ) ) ';
2974 /* Original Code l_inbound_select := ', mtrl.reference_id ' || /*reference_id */
2975 /*', mtrl.reference '; -- reference */
2976
2977 -- End of code for Bug#8467334
2978
2979 ELSE
2980 IF l_debug = 1 THEN
2981 debug(' querying for completed_record ','get_inbound_specific_query');
2982 END IF;
2983 -- Code for Bug#8467334
2984 -- Code added to popluate TXN_SOURCE_ID and transaction_source_type_id of completed tasks in WMS_WAVEPLAN_TASKS_TEMP . We'll use this info
2985 -- Populate Job No In ctl Board
2986 L_MOVER_ORDER_LINE := '' ;
2987 if wms_plan_tasks_pvt.g_planned_tasks_rec.is_completed = TRUE OR
2988 wms_plan_tasks_pvt.g_is_completed_task = TRUE then
2989
2990 l_mover_order_line := ' wdth.MOVE_ORDER_LINE_ID ';
2991
2992 END IF;
2993
2994 -- reference
2995 l_inbound_select := ', decode(rt.source_document_code, '
2996 || '''INVENTORY'', rt.shipment_line_id, '
2997 || '''PO'', rt.po_line_location_id, '
2998 || '''REQ'', rt.shipment_line_id, '
2999 || '''RMA'', rt.oe_order_line_id '
3000 || ' , NULL ' ||' , '|| '( SELECT distinct TXN_SOURCE_ID FROM mtl_txn_request_lines '
3001 || ' WHERE line_id = '|| l_mover_order_line || ' ))' -- Code for Bug#8467334
3002 || ', decode(rt.source_document_code, '
3003 || '''INVENTORY'', ''SHIPMENT_LINE_ID'', '
3004 || '''PO'', ''PO_LINE_LOCATION_ID'', '
3005 || '''REQ'', ''SHIPMENT_LINE_ID'', '
3006 || '''RMA'', ''ORDER_LINE_ID'' '
3007 ||', NULL '||' , ' || '( SELECT Distinct (Decode (transaction_source_type_id,5 , '
3008 ||'''WIP JOB'' , NULL )) FROM mtl_material_transactions WHERE MOVE_ORDER_LINE_ID =
3009 '|| l_mover_order_line || ' ) )'; -- Code for Bug#8467334
3010 -- End of code for Bug#8467334
3011
3012 END IF;
3013
3014 IF wms_plan_tasks_pvt.g_from_po_header_id IS NOT NULL
3015 OR wms_plan_tasks_pvt.g_to_po_header_id IS NOT NULL THEN
3016 l_inbound_select := l_inbound_select || ', ph.segment1 ' || /* source_header*/
3017 ', pl.line_num '; -- line_number
3018 ELSIF wms_plan_tasks_pvt.g_from_rma_header_id IS NOT NULL
3019 OR wms_plan_tasks_pvt.g_to_rma_header_id IS NOT NULL THEN
3020 l_inbound_select := l_inbound_select || ', ooh.order_number '; -- source_header
3021 l_inbound_select := l_inbound_select || ', ool.line_number '; -- line_number
3022 ELSIF wms_plan_tasks_pvt.g_from_requisition_header_id IS NOT NULL
3023 OR wms_plan_tasks_pvt.g_to_requisition_header_id IS NOT NULL THEN
3024 l_inbound_select := l_inbound_select || ', prh.segment1 '; -- source_header
3025 l_inbound_select := l_inbound_select || ', prl.line_num '; -- line_number
3026 END IF;
3027
3028 /* Bug 5259318 */
3029 IF g_delivery_visible = 'T'
3030 THEN
3031 IF wms_plan_tasks_pvt.g_include_crossdock then
3032 l_inbound_select := l_inbound_select || ', wnd.name '; --delivery
3033 END IF;
3034 END IF;
3035 /* End of Bug 5259318 */
3036
3037
3038 /* inbound specific from */
3039 IF wms_plan_tasks_pvt.g_from_po_header_id IS NOT NULL
3040 OR wms_plan_tasks_pvt.g_to_po_header_id IS NOT NULL THEN
3041 IF p_is_completed_rec = 0 THEN
3042 l_inbound_from := l_inbound_from || ', po_line_locations_trx_v pll';
3043 END IF;
3044
3045 l_inbound_from := l_inbound_from
3046 || ', po_headers_trx_v ph'--CLM Changes, using CLM views instead of base tables
3047 ||', po_lines_trx_v pl';
3048 ELSIF wms_plan_tasks_pvt.g_from_rma_header_id IS NOT NULL
3049 OR wms_plan_tasks_pvt.g_to_rma_header_id IS NOT NULL THEN
3050 l_inbound_from := l_inbound_from
3051 || ', oe_order_headers_all ooh'
3052 || ', oe_order_lines_all ool';
3053 ELSIF wms_plan_tasks_pvt.g_from_requisition_header_id IS NOT NULL
3054 OR wms_plan_tasks_pvt.g_to_requisition_header_id IS NOT NULL THEN
3055 l_inbound_from := l_inbound_from
3056 || ', rcv_shipment_headers rsh'
3057 || ', rcv_shipment_lines rsl'
3058 -- MOAC changed po_requisition_headers and po_requisition_lines to _ALL tables
3059 || ', po_requisition_headers_all prh'
3060 || ', po_requisition_lines_all prl';
3061 ELSIF wms_plan_tasks_pvt.g_from_shipment_number IS NOT NULL
3062 OR wms_plan_tasks_pvt.g_to_shipment_number IS NOT NULL THEN
3063 l_inbound_from := l_inbound_from
3064 || ', rcv_shipment_headers rsh'
3065 || ', rcv_shipment_lines rsl';
3066 END IF;
3067
3068 IF p_is_completed_rec = 0 THEN --not completed
3069 l_inbound_from := l_inbound_from
3070 || ', mtl_txn_request_headers mtrh '
3071 || ', mtl_txn_request_lines mtrl ';
3072 ELSE -- completed tasks
3073
3074 l_inbound_from := l_inbound_from || ', rcv_transactions rt ';
3075 END IF;
3076
3077 IF p_is_completed_rec = 0 THEN
3078 l_inbound_where := l_inbound_where
3079 || ' AND mtrh.header_id = mtrl.header_id '
3080 || ' AND mtrh.move_order_type = 6 '
3081 || ' AND mmtt.move_order_line_id = mtrl.line_id ';
3082
3083 IF NOT(
3084 wms_plan_tasks_pvt.g_from_po_header_id IS NOT NULL
3085 OR wms_plan_tasks_pvt.g_to_po_header_id IS NOT NULL
3086 OR wms_plan_tasks_pvt.g_from_rma_header_id IS NOT NULL
3087 OR wms_plan_tasks_pvt.g_to_rma_header_id IS NOT NULL
3088 OR wms_plan_tasks_pvt.g_from_requisition_header_id IS NOT NULL
3089 OR wms_plan_tasks_pvt.g_to_requisition_header_id IS NOT NULL
3090 ) THEN
3091 l_inbound_where := l_inbound_where
3092 || ' AND '
3093 || '( ' -- Code for Bug#8467334
3094 ||' mtrl.reference in (''PO_LINE_LOCATION_ID'', ''ORDER_LINE_ID'', ''SHIPMENT_LINE_ID'') ';
3095
3096 -- Code for Bug#8467334
3097 -- Code added to show Put away of WIP Job for Loaded tasks
3098 l_inbound_where := l_inbound_where
3099 || ' OR (mtrl.reference IS NULL and mtrl.transaction_source_type_id = 5) ) '; -- Added mtrl transaction source type, to take care of loaded LPNs(in inventory) not to be shown in inbound tasks
3100 --End of Code added to show Put away of WIP Job for Loaded tasks
3101 -- End of Code for Bug#8467334
3102
3103 END IF;
3104 END IF;
3105
3106 -- Build the inbound section(FROM and WHERE) of the query
3107
3108 IF wms_plan_tasks_pvt.g_from_po_header_id IS NOT NULL
3109 OR wms_plan_tasks_pvt.g_to_po_header_id IS NOT NULL THEN
3110 l_inbound_where := l_inbound_where
3111 || ' AND pl.po_header_id = ph.po_header_id ';
3112
3113 IF p_is_completed_rec = 0 THEN --not completed
3114 l_inbound_where := l_inbound_where
3115 || ' AND pll.po_line_id = pl.po_line_id '
3116 || ' AND pll.line_location_id = mtrl.reference_id ';
3117 IF wms_plan_tasks_pvt.g_from_po_header_id IS NOT NULL THEN
3118 l_inbound_where := l_inbound_where
3119 || 'AND pll.po_header_id >= :from_po_header_id ';
3120 END IF;
3121
3122 IF wms_plan_tasks_pvt.g_to_po_header_id IS NOT NULL THEN
3123 l_inbound_where := l_inbound_where
3124 || 'AND pll.po_header_id <= :to_po_header_id ';
3125 END IF;
3126
3127 --Hardik LSP bug 9274233
3128
3129 l_from_client_code := wms_deploy.get_po_client_code(wms_plan_tasks_pvt.g_from_po_header_id);
3130 l_delimiter := wms_deploy.get_item_flex_delimiter;
3131 l_is_po_number := wms_deploy.get_po_number(wms_plan_tasks_pvt.g_from_purchase_order);
3132
3133 IF l_is_po_number <> '-1' THEN
3134 IF l_from_client_code IS NOT NULL THEN
3135 l_inbound_where := l_inbound_where
3136 || 'AND ph.segment1 LIKE ' || '''' || '%' || l_delimiter || l_from_client_code || '''';
3137 ELSE
3138 l_inbound_where := l_inbound_where
3139 || 'AND to_char(wms_deploy.get_po_number(ph.segment1)) = ph.segment1';
3140 END IF;
3141 END IF;
3142 --Hardik LSP End bug 9274233
3143
3144 ELSE -- is completed
3145
3146 l_inbound_where := l_inbound_where
3147 || ' AND rt.po_line_id = pl.po_line_id ';
3148
3149 IF wms_plan_tasks_pvt.g_from_po_header_id IS NOT NULL THEN
3150 l_inbound_where := l_inbound_where
3151 || 'AND rt.po_header_id >= :from_po_header_id ';
3152 END IF;
3153
3154 IF wms_plan_tasks_pvt.g_to_po_header_id IS NOT NULL THEN
3155 l_inbound_where := l_inbound_where
3156 || 'AND rt.po_header_id <= :to_po_header_id ';
3157 END IF;
3158 END IF;
3159 ELSIF wms_plan_tasks_pvt.g_from_rma_header_id IS NOT NULL
3160 OR wms_plan_tasks_pvt.g_to_rma_header_id IS NOT NULL THEN
3161 l_inbound_where := l_inbound_where
3162 || ' AND ooh.header_id = ool.header_id ';
3163
3164 IF p_is_completed_rec = 0 THEN -- not completed
3165 l_inbound_where := l_inbound_where
3166 || ' AND mtrl.reference_id = ool.line_id '
3167 || ' AND mtrl.reference = ''ORDER_LINE_ID'' ';
3168
3169 IF wms_plan_tasks_pvt.g_from_rma_header_id IS NOT NULL THEN
3170 l_inbound_where := l_inbound_where
3171 || 'AND ooh.header_id >= :from_rma_header_id ';
3172 END IF;
3173
3174 IF wms_plan_tasks_pvt.g_to_rma_header_id IS NOT NULL THEN
3175 l_inbound_where := l_inbound_where
3176 || 'AND ooh.header_id <= :to_rma_header_id ';
3177 END IF;
3178 ELSE
3179 l_inbound_where := l_inbound_where
3180 || ' AND rt.oe_order_line_id = ool.line_id ';
3181
3182 IF wms_plan_tasks_pvt.g_from_rma_header_id IS NOT NULL THEN
3183 l_inbound_where := l_inbound_where
3184 || 'AND rt.oe_order_header_id >= :from_rma_header_id ';
3185 END IF;
3186
3187 IF wms_plan_tasks_pvt.g_to_rma_header_id IS NOT NULL THEN
3188 l_inbound_where := l_inbound_where
3189 || 'AND rt.oe_order_header_id <= :to_rma_header_id ';
3190 END IF;
3191 END IF;
3192 ELSIF wms_plan_tasks_pvt.g_from_requisition_header_id IS NOT NULL
3193 OR wms_plan_tasks_pvt.g_to_requisition_header_id IS NOT NULL THEN
3194 l_inbound_where := l_inbound_where
3195 || ' AND rsl.requisition_line_id = prl.requisition_line_id '
3196 || ' AND rsh.shipment_header_id = rsl.shipment_header_id '
3197 || ' AND prh.requisition_header_id = prl.requisition_header_id ';
3198
3199 IF p_is_completed_rec = 0 THEN -- not completed
3200 l_inbound_where := l_inbound_where
3201 || ' AND mtrl.reference_id = rsl.shipment_line_id '
3202 || ' AND mtrl.reference = ''SHIPMENT_LINE_ID'' ';
3203
3204 IF wms_plan_tasks_pvt.g_from_requisition_header_id IS NOT NULL THEN
3205 l_inbound_where := l_inbound_where
3206 || 'AND prh.requisition_header_id >= :from_requisition_header_id ';
3207 END IF;
3208
3209 IF wms_plan_tasks_pvt.g_to_requisition_header_id IS NOT NULL THEN
3210 l_inbound_where := l_inbound_where
3211 || 'AND prh.requisition_header_id <= :to_requisition_header_id ';
3212 END IF;
3213 ELSE -- completed
3214 l_inbound_where := l_inbound_where
3215 || ' AND rt.shipment_line_id = rsl.shipment_line_id ';
3216
3217 IF wms_plan_tasks_pvt.g_from_requisition_header_id IS NOT NULL THEN
3218 l_inbound_where := l_inbound_where
3219 || 'AND prh.requisition_header_id >= :from_requisition_header_id ';
3220 END IF;
3221
3222 IF wms_plan_tasks_pvt.g_to_requisition_header_id IS NOT NULL THEN
3223 l_inbound_where := l_inbound_where
3224 || 'AND prh.requisition_header_id <= :to_requisition_header_id ';
3225 END IF;
3226 END IF;
3227 ELSIF wms_plan_tasks_pvt.g_from_shipment_number IS NOT NULL
3228 OR wms_plan_tasks_pvt.g_to_shipment_number IS NOT NULL THEN
3229 l_inbound_where := l_inbound_where
3230 || ' AND rsh.shipment_header_id = rsl.shipment_header_id '
3231 || ' AND rsl.requisition_line_id IS NULL ';
3232
3233 IF p_is_completed_rec = 0 THEN -- not completed
3234 l_inbound_where := l_inbound_where
3235 || ' AND mtrl.reference_id = rsl.shipment_line_id '
3236 || ' AND mtrl.reference = ''SHIPMENT_LINE_ID'' ';
3237 ELSE -- completed
3238 l_inbound_where := l_inbound_where
3239 || ' AND rsh.shipment_header_id = rt.shipment_header_id '
3240 || ' AND rsl.shipment_line_id = rt.shipment_line_id '
3241 -- || ' AND rt.po_line_id IS NULL ' -- bug#14751834
3242 || ' AND rt.oe_order_header_id IS NULL ';
3243 END IF;
3244
3245 IF wms_plan_tasks_pvt.g_from_shipment_number IS NOT NULL THEN
3246 l_inbound_where := l_inbound_where
3247 || ' AND rsh.shipment_num >= :from_shipment_number ';
3248 END IF;
3249
3250 IF wms_plan_tasks_pvt.g_to_shipment_number IS NOT NULL THEN
3251 l_inbound_where := l_inbound_where
3252 || ' AND rsh.shipment_num <= :to_shipment_number ';
3253 END IF;
3254 END IF;
3255
3256 x_inbound_select_str := l_inbound_select;
3257 x_inbound_from_str := l_inbound_from;
3258 x_inbound_where_str := l_inbound_where;
3259
3260 END get_inbound_specific_query;
3261
3262
3263 /* This is used to add-in the outbound query criteria while
3264 querying crossdock tasks */
3265 PROCEDURE get_outbound_specific_query(
3266 x_outbound_from_str OUT NOCOPY VARCHAR2
3267 ,x_outbound_where_str OUT NOCOPY VARCHAR2
3268 ) IS
3269 --l_inbound_select wms_plan_tasks_pvt.short_sql;
3270 l_from_outbound wms_plan_tasks_pvt.short_sql;
3271 l_where_outbound wms_plan_tasks_pvt.short_sql;
3272 l_is_range_so BOOLEAN;
3273 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
3274
3275 BEGIN
3276 /**** Outbound specific queries ****/
3277 IF l_debug = 1 THEN
3278 debug(' in get_outbound_specific_query.. ' );
3279 END IF;
3280 if (wms_plan_tasks_pvt.g_from_sales_order_id = wms_plan_tasks_pvt.g_to_sales_order_id) then
3281 l_is_range_so := FALSE;
3282 else/*range so is TRUE if from or to is null or form<> to*/
3283 l_is_range_so := TRUE;
3284 end if;
3285
3286 -- BUILD THE FROM CLAUSE
3287 l_from_outbound := ', wsh_delivery_details_ob_grp_v wdd ';
3288 if NOT g_is_completed_task then/*3455109 we will no longer use mso for completed tasks*/
3289 IF wms_plan_tasks_pvt.g_from_sales_order_id IS NOT NULL
3290 THEN
3291 l_from_outbound := l_from_outbound || ', mtl_sales_orders mso1 ';
3292 END IF;
3293
3294 IF wms_plan_tasks_pvt.g_to_sales_order_id IS NOT NULL
3295 THEN
3296 l_from_outbound := l_from_outbound || ', mtl_sales_orders mso2 ';
3297 END IF;
3298 END IF;
3299
3300 IF g_customer_visible = 'T' OR wms_plan_tasks_pvt.g_customer_category IS NOT NULL
3301 THEN
3302 l_from_outbound := l_from_outbound || ', hz_parties hp ';
3303 l_from_outbound := l_from_outbound || ', hz_cust_accounts hca ';
3304 END IF;
3305
3306 IF g_carrier_visible = 'T'
3307 THEN
3308 l_from_outbound := l_from_outbound || ', wsh_carriers wc ';
3309 END IF;
3310
3311 IF g_ship_method_visible = 'T'
3312 THEN
3313 l_from_outbound :=
3314 l_from_outbound || ', fnd_lookup_values_vl flv ';
3315 END IF;
3316
3317 IF wms_plan_tasks_pvt.g_trip_id IS NOT NULL
3318 THEN
3319 --Change
3320 /*l_from_outbound := l_from_outbound || ', wsh_trips wt, wsh_trip_stops wts ';
3321 l_from_outbound := l_from_outbound || ', wsh_delivery_legs wdl, wsh_new_deliveries wnd ';
3322 l_from_outbound := l_from_outbound || ', wsh_delivery_assignments wda '; */
3323 l_from_outbound :=
3324 l_from_outbound
3325 || ', wsh_trips_ob_grp_v wt, wsh_trip_stops_ob_grp_v wts ';
3326 l_from_outbound :=
3327 l_from_outbound
3328 || ', wsh_delivery_legs_ob_grp_v wdl, wsh_new_deliveries_ob_grp_v wnd ';
3329 l_from_outbound :=
3330 l_from_outbound || ', wsh_delivery_assignments wda ';
3331 --End of change
3332 ELSIF wms_plan_tasks_pvt.g_delivery_id IS NOT NULL
3333 THEN
3334 l_from_outbound :=
3335 l_from_outbound || ', wsh_delivery_assignments wda ';
3336 END IF;
3337
3338 IF g_delivery_visible = 'T' AND wms_plan_tasks_pvt.g_trip_id IS NULL
3339 THEN
3340 --Change
3341 --l_from_outbound := l_from_outbound || ', wsh_new_deliveries wnd ';
3342 l_from_outbound :=
3343 l_from_outbound || ', wsh_new_deliveries_ob_grp_v wnd ';
3344
3345 -- End of change
3346 IF wms_plan_tasks_pvt.g_delivery_id IS NULL
3347 THEN
3348 l_from_outbound :=
3349 l_from_outbound || ', wsh_delivery_assignments wda ';
3350 END IF;
3351 END IF;
3352
3353 IF wms_plan_tasks_pvt.g_ship_to_state IS NOT NULL
3354 OR wms_plan_tasks_pvt.g_ship_to_country IS NOT NULL
3355 OR wms_plan_tasks_pvt.g_ship_to_postal_code IS NOT NULL
3356 OR g_ship_to_country_visible = 'T'
3357 OR g_ship_to_state_visible = 'T'
3358 OR g_ship_to_postal_code_visible = 'T'
3359 THEN
3360 l_from_outbound := l_from_outbound || ', hz_locations hl ';
3361 END IF;
3362
3363 IF wms_plan_tasks_pvt.g_from_number_of_order_lines IS NOT NULL
3364 OR wms_plan_tasks_pvt.g_to_number_of_order_lines IS NOT NULL
3365 THEN
3366 l_from_outbound :=
3367 l_from_outbound
3368 || ', (SELECT COUNT(line_id) line_sum, header_id FROM oe_order_lines_all ';
3369
3370 IF wms_plan_tasks_pvt.g_customer_id IS NOT NULL
3371 THEN
3372 l_from_outbound :=
3373 l_from_outbound || ' WHERE sold_to_org_id = :customer_id ';
3374 END IF;
3375
3376 l_from_outbound :=
3377 l_from_outbound || ' GROUP BY header_id) oolac ';
3378 END IF;
3379
3380 -- BUILD THE WHERE CLAUSE
3381 IF NOT g_is_completed_task THEN -- Non Completed tasks
3382 -- Build the outbound where section of the query
3383 -- l_where_outbound := 'AND mmtt.transaction_action_id = 28 ';
3384
3385 IF (wms_plan_tasks_pvt.g_include_internal_orders AND NOT wms_plan_tasks_pvt.g_include_sales_orders
3386 )
3387 OR (NOT wms_plan_tasks_pvt.g_include_internal_orders AND wms_plan_tasks_pvt.g_include_sales_orders)
3388 THEN
3389 l_where_outbound :=
3390 l_where_outbound
3391 || 'AND mmtt.transaction_source_type_id = :source_type_id ';
3392 END IF;
3393
3394 IF wms_plan_tasks_pvt.g_from_sales_order_id IS NOT NULL
3395 THEN
3396 l_where_outbound :=
3397 l_where_outbound
3398 || 'AND mso1.sales_order_id = :from_sales_order_id ';
3399 l_where_outbound :=
3400 l_where_outbound
3401 || 'AND to_number(wdd.source_header_number) >= to_number(mso1.segment1) ';
3402 l_where_outbound :=
3403 l_where_outbound
3404 || 'AND wdd.source_header_type_name >= mso1.segment2 ';
3405 END IF;
3406
3407 IF wms_plan_tasks_pvt.g_to_sales_order_id IS NOT NULL
3408 THEN
3409 l_where_outbound :=
3410 l_where_outbound
3411 || 'AND mso2.sales_order_id = :to_sales_order_id ';
3412 l_where_outbound :=
3413 l_where_outbound
3414 || 'AND to_number(wdd.source_header_number) <= to_number(mso2.segment1) ';
3415 l_where_outbound :=
3416 l_where_outbound
3417 || 'AND wdd.source_header_type_name <= mso2.segment2 ';
3418 END IF;
3419
3420 IF wms_plan_tasks_pvt.g_from_pick_slip_number IS NOT NULL
3421 THEN
3422 l_where_outbound :=
3423 l_where_outbound
3424 || 'AND mmtt.pick_slip_number >= :from_pick_slip_number ';
3425 END IF;
3426
3427 IF wms_plan_tasks_pvt.g_to_pick_slip_number IS NOT NULL
3428 THEN
3429 l_where_outbound :=
3430 l_where_outbound
3431 || 'AND mmtt.pick_slip_number <= :to_pick_slip_number ';
3432 END IF;
3433 /*
3434 l_where_outbound := l_where_outbound || 'AND mmtt.move_order_line_id = wdd.move_order_line_id ';
3435 l_where_outbound := l_where_outbound || 'AND wdd.delivery_detail_id = (select delivery_detail_id from wsh_delivery_details_ob_grp_v ';
3436 l_where_outbound := l_where_outbound || ' where mmtt.trx_source_line_id = source_line_id ';
3437 l_where_outbound := l_where_outbound || ' and mmtt.move_order_line_id = move_order_line_id ';
3438 l_where_outbound := l_where_outbound || ' and rownum < 2) ';
3439 */
3440 ELSE -- Completed tasks
3441 -- Build the outbound where section of the query for completed tasks
3442 IF (wms_plan_tasks_pvt.g_include_internal_orders AND NOT wms_plan_tasks_pvt.g_include_sales_orders
3443 )
3444 OR (NOT wms_plan_tasks_pvt.g_include_internal_orders AND wms_plan_tasks_pvt.g_include_sales_orders)
3445 THEN
3446 l_where_outbound :=
3447 l_where_outbound
3448 || 'AND wdth.transaction_source_type_id = :source_type_id ';
3449 END IF;
3450
3451
3452 /*Logic if L_is_range_so then we need to add the greater than or less
3453 than but change it to bind rather than from mso 3455109 else use equals*/
3454 IF(l_is_range_so) then
3455 IF wms_plan_tasks_pvt.g_from_sales_order_id IS NOT NULL
3456 THEN
3457 -- 3455109 l_where_outbound := l_where_outbound || 'AND mso1.sales_order_id = :from_sales_order_id ';
3458 l_where_outbound := l_where_outbound || ' AND lpad(wdd.source_header_number,40) >= :l_from_tonum_mso_seg1 ';
3459 l_where_outbound := l_where_outbound || ' AND wdd.source_header_type_name >= :l_from_mso_seg2 ';
3460 END IF;
3461
3462 IF wms_plan_tasks_pvt.g_to_sales_order_id IS NOT NULL
3463 THEN
3464 l_where_outbound := l_where_outbound || 'AND lpad(wdd.source_header_number,40) <= :l_to_tonum_mso_seg1 ';
3465 l_where_outbound := l_where_outbound || 'AND wdd.source_header_type_name <= :l_to_mso_seg2 ';
3466 END IF;
3467 ELSE
3468 l_where_outbound := l_where_outbound || 'AND (wdd.source_header_number) = :l_from_mso_seg1 ';
3469 l_where_outbound := l_where_outbound || 'AND wdd.source_header_type_name = :l_from_mso_seg2 ';
3470 END IF;
3471 -- commenting since there is no MMT available for crossdock tasks )
3472 /*
3473 IF wms_plan_tasks_pvt.g_from_pick_slip_number IS NOT NULL
3474 THEN
3475 l_where_outbound := l_where_outbound || 'AND mmt.pick_slip_number >= :from_pick_slip_number ';
3476 END IF;
3477
3478 IF wms_plan_tasks_pvt.g_to_pick_slip_number IS NOT NULL
3479 THEN
3480 l_where_outbound := l_where_outbound || 'AND mmt.pick_slip_number <= :to_pick_slip_number ';
3481 END IF;
3482 */
3483 END IF;
3484
3485 IF wms_plan_tasks_pvt.g_customer_id IS NOT NULL
3486 THEN
3487 l_where_outbound :=
3488 l_where_outbound || 'AND wdd.customer_id = :customer_id ';
3489 END IF;
3490
3491 IF g_customer_visible = 'T' OR wms_plan_tasks_pvt.g_customer_category IS NOT NULL
3492 THEN
3493 l_where_outbound :=
3494 l_where_outbound || 'AND hca.party_id = hp.party_id ';
3495
3496 --Bug 6069381: wdd.customer_id is not always same as hp.party_id.
3497 --It is same as hca.cust_account_id which is taken care below.
3498 /*l_where_outbound :=
3499 l_where_outbound || 'AND wdd.customer_id = hp.party_id ';*/
3500
3501 /* Bug 5507934 */
3502 l_where_outbound :=
3503 l_where_outbound || 'AND wdd.customer_id = hca.cust_account_id ';
3504 /* End of Bug 5507934 */
3505
3506 IF wms_plan_tasks_pvt.g_customer_category IS NOT NULL
3507 THEN
3508 l_where_outbound :=
3509 l_where_outbound
3510 || 'AND hca.customer_class_code = :customer_category ';
3511 END IF;
3512 END IF;
3513
3514 IF g_carrier_visible = 'T'
3515 THEN
3516 l_where_outbound :=
3517 l_where_outbound || 'AND wdd.carrier_id = wc.carrier_id(+) ';
3518 END IF;
3519
3520 IF g_ship_method_visible = 'T'
3521 THEN
3522 l_where_outbound :=
3523 l_where_outbound
3524 || 'AND flv.lookup_code(+) = wdd.ship_method_code ';
3525 l_where_outbound :=
3526 l_where_outbound || 'AND flv.lookup_type(+) = ''SHIP_METHOD'' ';
3527 l_where_outbound :=
3528 l_where_outbound
3529 || 'AND nvl(flv.start_date_active(+), sysdate) <= sysdate ';
3530 l_where_outbound :=
3531 l_where_outbound
3532 || 'AND nvl(flv.end_date_active(+), sysdate) >= sysdate ';
3533 l_where_outbound :=
3534 l_where_outbound || 'AND flv.view_application_id(+) = 3 ';
3535 END IF;
3536
3537 IF wms_plan_tasks_pvt.g_carrier_id IS NOT NULL
3538 THEN
3539 l_where_outbound :=
3540 l_where_outbound || 'AND wdd.carrier_id = :carrier_id ';
3541 END IF;
3542
3543 IF wms_plan_tasks_pvt.g_ship_method IS NOT NULL
3544 THEN
3545 l_where_outbound :=
3546 l_where_outbound || 'AND wdd.ship_method_code = :ship_method ';
3547 END IF;
3548
3549 IF wms_plan_tasks_pvt.g_shipment_priority IS NOT NULL
3550 THEN
3551 l_where_outbound :=
3552 l_where_outbound
3553 || 'AND wdd.shipment_priority_code = :shipment_priority ';
3554 END IF;
3555
3556 IF wms_plan_tasks_pvt.g_from_shipment_date IS NOT NULL
3557 THEN
3558 l_where_outbound :=
3559 l_where_outbound
3560 || 'AND wdd.date_scheduled >= :from_shipment_date ';
3561 END IF;
3562
3563 IF wms_plan_tasks_pvt.g_to_shipment_date IS NOT NULL
3564 THEN
3565 l_where_outbound :=
3566 l_where_outbound
3567 || 'AND wdd.date_scheduled <= :to_shipment_date ';
3568 END IF;
3569
3570 IF wms_plan_tasks_pvt.g_trip_id IS NOT NULL OR wms_plan_tasks_pvt.g_delivery_id IS NOT NULL
3571 THEN
3572 IF wms_plan_tasks_pvt.g_trip_id IS NOT NULL
3573 THEN
3574 l_where_outbound :=
3575 l_where_outbound || 'AND wt.trip_id = :trip_id ';
3576 l_where_outbound :=
3577 l_where_outbound || 'AND wt.trip_id = wts.trip_id ';
3578 l_where_outbound :=
3579 l_where_outbound
3580 || 'AND wdl.pick_up_stop_id = wts.stop_id ';
3581 l_where_outbound :=
3582 l_where_outbound
3583 || 'AND wnd.delivery_id = wdl.delivery_id ';
3584 l_where_outbound :=
3585 l_where_outbound
3586 || 'AND wnd.delivery_id = wda.delivery_id ';
3587 END IF;
3588
3589 l_where_outbound :=
3590 l_where_outbound
3591 || 'AND wdd.delivery_detail_id = wda.delivery_detail_id ';
3592
3593 IF wms_plan_tasks_pvt.g_delivery_id IS NOT NULL
3594 THEN
3595 l_where_outbound :=
3596 l_where_outbound || 'AND wda.delivery_id = :delivery_id ';
3597 END IF;
3598 END IF;
3599
3600 IF g_delivery_visible = 'T' AND wms_plan_tasks_pvt.g_trip_id IS NULL
3601 THEN
3602 IF wms_plan_tasks_pvt.g_delivery_id IS NULL
3603 THEN
3604 l_where_outbound :=
3605 l_where_outbound
3606 || 'AND wdd.delivery_detail_id = wda.delivery_detail_id(+) ';
3607 END IF;
3608
3609 l_where_outbound :=
3610 l_where_outbound || 'AND wnd.delivery_id(+) = wda.delivery_id ';
3611 END IF;
3612
3613 IF wms_plan_tasks_pvt.g_ship_to_state IS NOT NULL
3614 OR wms_plan_tasks_pvt.g_ship_to_country IS NOT NULL
3615 OR wms_plan_tasks_pvt.g_ship_to_postal_code IS NOT NULL
3616 OR g_ship_to_country_visible = 'T'
3617 OR g_ship_to_state_visible = 'T'
3618 OR g_ship_to_postal_code_visible = 'T'
3619 THEN
3620 l_where_outbound :=
3621 l_where_outbound
3622 || 'AND wdd.ship_to_location_id = hl.location_id ';
3623
3624 IF wms_plan_tasks_pvt.g_ship_to_state IS NOT NULL
3625 THEN
3626 l_where_outbound :=
3627 l_where_outbound || 'AND hl.state = :ship_to_state ';
3628 END IF;
3629
3630 IF wms_plan_tasks_pvt.g_ship_to_country IS NOT NULL
3631 THEN
3632 l_where_outbound :=
3633 l_where_outbound || 'AND hl.country = :ship_to_country ';
3634 END IF;
3635
3636 IF wms_plan_tasks_pvt.g_ship_to_postal_code IS NOT NULL
3637 THEN
3638 l_where_outbound :=
3639 l_where_outbound
3640 || 'AND hl.postal_code = :ship_to_postal_code ';
3641 END IF;
3642 END IF;
3643
3644 IF wms_plan_tasks_pvt.g_from_number_of_order_lines IS NOT NULL
3645 OR wms_plan_tasks_pvt.g_to_number_of_order_lines IS NOT NULL
3646 THEN
3647 IF wms_plan_tasks_pvt.g_from_number_of_order_lines IS NOT NULL
3648 THEN
3649 l_where_outbound :=
3650 l_where_outbound
3651 || 'AND oolac.line_sum >= :from_number_of_order_lines ';
3652 END IF;
3653
3654 IF wms_plan_tasks_pvt.g_to_number_of_order_lines IS NOT NULL
3655 THEN
3656 l_where_outbound :=
3657 l_where_outbound
3658 || 'AND oolac.line_sum <= :to_number_of_order_lines ';
3659 END IF;
3660
3661 l_where_outbound :=
3662 l_where_outbound
3663 || 'AND oolac.header_id = wdd.source_header_id ';
3664 END IF;
3665 x_outbound_from_str := l_from_outbound;
3666 x_outbound_where_str := l_where_outbound;
3667
3668 END get_outbound_specific_query;
3669
3670 /* This procedure fetches the 'PLAN' records from WDTH. */
3671 PROCEDURE get_wdth_plan_records(x_wdth_select_str OUT NOCOPY VARCHAR2,
3672 x_wdth_from_str OUT NOCOPY VARCHAR2,
3673 x_wdth_where_str OUT NOCOPY VARCHAR2) IS
3674
3675 l_wdth_str VARCHAR2(6000);
3676 l_wdth_select_str wms_plan_tasks_pvt.short_sql;
3677 l_wdth_from_str VARCHAR2(500);
3678 l_wdth_where_str wms_plan_tasks_pvt.short_sql;
3679 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
3680 BEGIN
3681 IF l_debug = 1 THEN
3682 debug(' in get_wdth_plan_records','get_wdth_plan_records');
3683 END IF;
3684 l_wdth_select_str :=
3685 'SELECT ' ||
3686 ' decode(wdth.is_parent,''N'',decode(wdth.operation_plan_id,null,null,null),''+''), ' || /* expansion_code*/
3687 ' ''' || wms_plan_tasks_pvt.g_plan_task_types(3) || ''',' || /*plan_task*/
3688 ' wdth.transaction_id, ' || /*transaction_temp_id*/
3689 ' wdth.parent_transaction_id,' || /*parent_line_id*/
3690 ' wdth.inventory_item_id, ' || /*inventory_item_id*/
3691 ' msiv.concatenated_segments, ' || /*item*/
3692 ' msiv.description, ' || /*item description*/
3693 ' msiv.unit_weight, ' || /*unit_weight*/
3694 ' msiv.weight_uom_code, ' || /*weight_uom_code*/
3695 ' msiv.unit_volume, ' || /*unit_volume*/
3696 ' msiv.volume_uom_code, ' || /*volume_uom_code*/
3697 ' wdth.organization_id, ' || /*organization_id*/
3698 ' wdth.revision, ' || /*revision*/
3699 ' wdth.source_subinventory_code, ' ||/* subinventory*/
3700 ' wdth.source_locator_id, ' || /*locator_id*/
3701 ' decode(milv.segment19, null, milv.concatenated_segments, null), ' || /*locator*/
3702 ' wdth.status, ' || /*status_id*/
3703 ' wdth.status, ' || /*status_id_original*/
3704 ' decode(wdth.status,'
3705 || '6, '''
3706 || g_status_codes(6)
3707 || ''', 11, '''
3708 || g_status_codes(11)
3709 || ''', 12, '''
3710 || g_status_codes(12)
3711 || '''), ' ||
3712 ' wdth.transaction_type_id, ' || /*transaction_type_id*/
3713 ' wdth.transaction_action_id, ' || /*transaction_action_id */
3714 ' wdth.transaction_source_type_id, '; /*transaction_source_type_id*/
3715
3716 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
3717 l_wdth_select_str := l_wdth_select_str
3718 || ' mtst.transaction_source_type_name, '; --transaction_source_type
3719 END IF;
3720
3721 l_wdth_select_str := l_wdth_select_str
3722 || ' to_number(null), ' || /*transaction_source_id*/
3723 ' to_number(null), ' || /*transaction_source_line_id*/
3724 ' wdth.transfer_organization_id, '; /*to_organization_id*/
3725
3726 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
3727 l_wdth_select_str := l_wdth_select_str
3728 || 'mp1.organization_code, '; --to_organization_id
3729 END IF;
3730
3731 l_wdth_select_str := l_wdth_select_str
3732 || 'wdth.dest_subinventory_code, ' /*to_subinventory*/
3733 || 'wdth.dest_locator_id, '; /*to_locator_id*/
3734
3735 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
3736 --to locator
3737 l_wdth_select_str := l_wdth_select_str ||
3738 'decode(milv1.segment19, null, milv1.concatenated_segments, null), ';
3739 END IF;
3740
3741 l_wdth_select_str := l_wdth_select_str ||
3742 'wdth.transaction_uom_code, ' || /*transaction_uom*/
3743 ' wdth.transaction_quantity, ' || /*transaction_quantity*/
3744 ' wdth.user_task_type, '; /*user_task_type_id*/
3745
3746 IF wms_plan_tasks_pvt.g_user_task_type_visible = 'T' THEN
3747 l_wdth_select_str := l_wdth_select_str
3748 || 'bso.operation_code, '; --user_task_type
3749 END IF;
3750
3751 l_wdth_select_str := l_wdth_select_str ||
3752 ' to_number(null), ' || /*move_order_line_id*/
3753 ' to_number(null), ' || /*pick_slip_number*/
3754 ' to_number(null), ';/*cartonization_id*/
3755 IF g_cartonization_lpn_visible = 'T' THEN
3756 l_wdth_select_str := l_wdth_select_str || 'null, ';
3757 --cartonization_lpn
3758 END IF;
3759 l_wdth_select_str := l_wdth_select_str || ' to_number(null), ' ; /*allocated_lpn_id*/
3760 IF wms_plan_tasks_pvt.g_allocated_lpn_visible = 'T' THEN
3761 l_wdth_select_str := l_wdth_select_str || 'null, '; --allocated_lpn
3762 END IF;
3763 l_wdth_select_str := l_wdth_select_str || ' to_number(null), ';/*container_item_id*/
3764 IF g_container_item_visible = 'T' THEN
3765 l_wdth_select_str := l_wdth_select_str || 'null, '; /*container item */
3766 end if;
3767
3768 l_wdth_select_str := l_wdth_select_str || ' wdth.lpn_id, ';/*from_lpn_id*/
3769
3770 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
3771 l_wdth_select_str := l_wdth_select_str
3772 || 'wlpn5.license_plate_number, ';/*from_lpn*/
3773 END IF;
3774
3775 l_wdth_select_str := l_wdth_select_str || ' wdth.content_lpn_id, ';/*content_lpn_id*/
3776
3777 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
3778 l_wdth_select_str := l_wdth_select_str
3779 || 'wlpn3.license_plate_number, '; --content_lpn
3780 END IF;
3781
3782 l_wdth_select_str := l_wdth_select_str
3783 || 'wdth.transfer_lpn_id, '; --to_lpn_id
3784 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
3785 l_wdth_select_str := l_wdth_select_str
3786 || 'wlpn4.license_plate_number, '; --to_lpn
3787 END IF;
3788
3789 l_wdth_select_str := l_wdth_select_str ||
3790 ' to_date(null), ' || /*mmt_last_update_date*/
3791 ' to_number(null), ' || /*mmt_last_updated_by*/
3792 ' wdth.priority, ' || /*priority*/
3793 ' wdth.priority, ' || /*priority_original*/
3794 ' wdth.task_type, ' || /*task_type_id*/
3795 ' decode(wdth.task_type,'
3796 || '1, '''
3797 || wms_plan_tasks_pvt.g_task_types(1)
3798 || ''', 2, '''
3799 || wms_plan_tasks_pvt.g_task_types(2)
3800 || ''', 3, '''
3801 || wms_plan_tasks_pvt.g_task_types(3)
3802 || ''', 4, '''
3803 || wms_plan_tasks_pvt.g_task_types(4)
3804 || ''', 5, '''
3805 || wms_plan_tasks_pvt.g_task_types(5)
3806 || ''', 6, '''
3807 || wms_plan_tasks_pvt.g_task_types(6)
3808 || ''', 7, '''
3809 || wms_plan_tasks_pvt.g_task_types(7)
3810 || ''', 8, '''
3811 || wms_plan_tasks_pvt.g_task_types(8)
3812 || '''), ' ||
3813 ' to_date(null), ' || /*creation_time */
3814 ' wdth.operation_plan_id, '; /*operation_plan_id*/
3815 IF wms_plan_tasks_pvt.g_operation_plan_visible = 'T' THEN
3816 l_wdth_select_str := l_wdth_select_str
3817 || 'wop.operation_plan_name, ';/*operation_plan*/
3818 END IF;
3819 IF wms_plan_tasks_pvt.g_operation_sequence_visible = 'T' THEN
3820 l_wdth_select_str := l_wdth_select_str || ' to_number(null), ';
3821 END IF;
3822 l_wdth_select_str := l_wdth_select_str ||
3823 ' to_number(wdth.op_plan_instance_id), ' || /*operation_instance_id*/
3824 --' to_number(null), '|| /*operation_sequence*/
3825 ' wdth.task_id, ' ||/*task_id */
3826 ' wdth.person_id, ' || /*person_id*/
3827 ' wdth.person_id, '; /*person_id_original*/
3828
3829 IF wms_plan_tasks_pvt.g_person_visible = 'T' THEN
3830 l_wdth_select_str := l_wdth_select_str || 'pap.full_name, '; --person_id
3831 END IF;
3832 l_wdth_select_str := l_wdth_select_str ||
3833 ' wdth.effective_start_date, ' || /*effective_start_date*/
3834 ' wdth.effective_end_date, ' || /*effective_end_date*/
3835 ' wdth.person_resource_id, '; --person_resource_id
3836
3837 IF wms_plan_tasks_pvt.g_person_resource_visible = 'T' THEN
3838 l_wdth_select_str := l_wdth_select_str
3839 || 'br1.resource_code, '; --person_resource_code
3840 END IF;
3841
3842 l_wdth_select_str := l_wdth_select_str ||
3843 ' wdth.machine_resource_id, '; --machine_resource_id
3844
3845 IF wms_plan_tasks_pvt.g_machine_resource_visible = 'T' THEN
3846 l_wdth_select_str := l_wdth_select_str
3847 || 'br2.resource_code, '; --machine_resource_code
3848 END IF;
3849
3850 l_wdth_select_str := l_wdth_select_str ||
3851 ' wdth.equipment_instance, ' || /*equipment_instance*/
3852 ' wdth.dispatched_time, ' || /*dispatched_time*/
3853 ' wdth.loaded_time, ' || /*loaded_time*/
3854 ' wdth.drop_off_time, ' || /*drop_off_time*/
3855 ' to_date(null), ' || /*wdt_last_update_date*/
3856 ' to_number(null), ' || /*wdt_last_updated_by*/
3857 '''N'', '|| --is modified
3858 'wdth.SECONDARY_TRANSACTION_UOM_CODE, '||
3859 'wdth.secondary_transaction_quantity ';
3860
3861 IF wms_plan_tasks_pvt.g_include_inbound THEN
3862 l_wdth_select_str := l_wdth_select_str
3863 || ', wwtt.reference_id '
3864 || ', wwtt.reference ';
3865 END IF;
3866
3867 IF wms_plan_tasks_pvt.g_inbound_specific_query THEN
3868 l_wdth_select_str := l_wdth_select_str
3869 || ', wwtt.source_header '
3870 || ', wwtt.line_number ';
3871 END IF;
3872 /* Build the from clause of the query */
3873 l_wdth_from_str := ' FROM wms_dispatched_tasks_history wdth' ||
3874 -- ', mtl_system_items_kfv msiv ' ||
3875 ', mtl_system_items_vl msiv ' || --Bug 11798973 Modified to fetch translated value
3876 ', mtl_item_locations_kfv milv ';
3877
3878 IF wms_plan_tasks_pvt.g_item_category_id IS NOT NULL
3879 OR wms_plan_tasks_pvt.g_category_set_id IS NOT NULL THEN
3880 l_wdth_from_str := l_wdth_from_str ||
3881 ', mtl_item_categories mic ';
3882 END IF;
3883
3884 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
3885 l_wdth_from_str := l_wdth_from_str
3886 || ', wms_license_plate_numbers wlpn5 ';
3887 END IF;
3888
3889 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
3890 l_wdth_from_str := l_wdth_from_str
3891 || ', wms_license_plate_numbers wlpn3 ';
3892 END IF;
3893
3894 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
3895 l_wdth_from_str := l_wdth_from_str
3896 || ', wms_license_plate_numbers wlpn4 ';
3897 END IF;
3898
3899 IF wms_plan_tasks_pvt.g_user_task_type_visible = 'T' THEN
3900 l_wdth_from_str := l_wdth_from_str
3901 || ', bom_standard_operations bso ';
3902 END IF;
3903
3904 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
3905 l_wdth_from_str := l_wdth_from_str || ', mtl_parameters mp1 ';
3906 END IF;
3907
3908 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
3909 l_wdth_from_str := l_wdth_from_str
3910 || ', mtl_txn_source_types mtst ';
3911 END IF;
3912
3913 l_wdth_from_str := l_wdth_from_str || ', wms_op_plans_vl wop ';
3914
3915 IF wms_plan_tasks_pvt.g_person_resource_visible = 'T' THEN
3916 l_wdth_from_str := l_wdth_from_str || ', bom_resources br1 ';
3917 END IF;
3918
3919 IF wms_plan_tasks_pvt.g_machine_resource_visible = 'T' THEN
3920 l_wdth_from_str := l_wdth_from_str || ', bom_resources br2 ';
3921 END IF;
3922
3923 IF wms_plan_tasks_pvt.g_person_visible = 'T' THEN
3924 l_wdth_from_str := l_wdth_from_str
3925 || ', per_all_people_f pap ';
3926 END IF;
3927 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
3928 l_wdth_from_str := l_wdth_from_str || ', mtl_item_locations_kfv milv1 ';
3929 END IF;
3930
3931 l_wdth_where_str := 'WHERE 1 = 1 ';
3932 l_wdth_where_str := l_wdth_where_str
3933 || 'AND wdth.organization_id = msiv.organization_id '
3934 || 'AND wdth.inventory_item_id = msiv.inventory_item_id '
3935 || 'AND wdth.organization_id = milv.organization_id(+) '
3936 || 'AND wdth.source_locator_id = milv.inventory_location_id(+) ';
3937 IF wms_plan_tasks_pvt.g_to_organization_code_visible = 'T' THEN
3938 l_wdth_where_str := l_wdth_where_str
3939 || 'AND wdth.transfer_organization_id = mp1.organization_id(+) ';
3940 END IF;
3941
3942 IF wms_plan_tasks_pvt.g_txn_source_type_visible = 'T' THEN
3943 l_wdth_where_str := l_wdth_where_str
3944 || 'AND wdth.transaction_source_type_id = mtst.transaction_source_type_id (+) ';
3945 END IF;
3946
3947 IF wms_plan_tasks_pvt.g_person_resource_visible = 'T' THEN
3948 l_wdth_where_str := l_wdth_where_str
3949 || 'AND wdth.person_resource_id = br1.resource_id(+) ';
3950 END IF;
3951
3952 IF wms_plan_tasks_pvt.g_machine_resource_visible = 'T' THEN
3953 l_wdth_where_str := l_wdth_where_str
3954 || 'AND wdth.machine_resource_id = br2.resource_id(+) ';
3955 END IF;
3956
3957 IF wms_plan_tasks_pvt.g_person_visible = 'T' THEN
3958 l_wdth_where_str := l_wdth_where_str
3959 || 'AND wdth.person_id = pap.person_id (+)'
3960 || 'AND wdth.effective_start_date >= pap.effective_start_date (+) '
3961 || 'AND wdth.effective_end_date <= pap.effective_end_date (+) ';
3962 END IF;
3963
3964 IF wms_plan_tasks_pvt.g_from_lpn_visible = 'T' THEN
3965 l_wdth_where_str := l_wdth_where_str
3966 || 'AND wdth.lpn_id = wlpn5.lpn_id(+) ';
3967 END IF;
3968
3969 IF wms_plan_tasks_pvt.g_content_lpn_visible = 'T' THEN
3970 l_wdth_where_str := l_wdth_where_str
3971 || 'AND wdth.content_lpn_id = wlpn3.lpn_id(+) ';
3972 END IF;
3973
3974 IF wms_plan_tasks_pvt.g_to_lpn_visible = 'T' THEN
3975 l_wdth_where_str := l_wdth_where_str
3976 || 'AND wdth.transfer_lpn_id = wlpn4.lpn_id(+) ';
3977 END IF;
3978
3979 IF wms_plan_tasks_pvt.g_user_task_type_visible = 'T' THEN
3980 l_wdth_where_str := l_wdth_where_str
3981 || 'AND wdth.user_task_type = bso.standard_operation_id(+) '
3982 || 'AND wdth.organization_id = bso.organization_id(+) ';
3983 END IF;
3984
3985 l_wdth_where_str := l_wdth_where_str
3986 || 'AND wdth.operation_plan_id = wop.operation_plan_id(+) ';
3987
3988 IF wms_plan_tasks_pvt.g_organization_id IS NOT NULL THEN
3989 l_wdth_where_str := l_wdth_where_str
3990 || 'AND wdth.organization_id = :org_id ';
3991 END IF;
3992
3993 IF wms_plan_tasks_pvt.g_category_set_id IS NOT NULL THEN
3994 l_wdth_where_str := l_wdth_where_str
3995 || 'AND mic.category_set_id = :category_set_id ';
3996 END IF;
3997
3998 IF wms_plan_tasks_pvt.g_item_category_id IS NOT NULL THEN
3999 l_wdth_where_str := l_wdth_where_str
4000 || 'AND wdth.inventory_item_id = mic.inventory_item_id (+) '
4001 || 'AND mic.organization_id = wdth.organization_id '
4002 || 'AND mic.category_id = :item_category_id ';
4003 END IF;
4004
4005 l_wdth_where_str := l_wdth_where_str
4006 || ' AND wdth.is_parent = ''Y'' '
4007 || ' AND wdth.operation_plan_id is not null ';
4008
4009 IF wms_plan_tasks_pvt.g_inventory_item_id IS NOT NULL THEN
4010 l_wdth_where_str := l_wdth_where_str
4011 || 'AND wdth.inventory_item_id = :item_id ';
4012 END IF;
4013
4014 IF wms_plan_tasks_pvt.g_from_task_quantity IS NOT NULL THEN
4015 l_wdth_where_str := l_wdth_where_str
4016 || 'AND wdth.transaction_quantity >= :from_task_quantity ';
4017 END IF;
4018
4019 IF wms_plan_tasks_pvt.g_to_task_quantity IS NOT NULL THEN
4020 l_wdth_where_str := l_wdth_where_str
4021 || 'AND wdth.transaction_quantity <= :to_task_quantity ';
4022 END IF;
4023
4024 IF wms_plan_tasks_pvt.g_from_creation_date IS NOT NULL THEN
4025 l_wdth_where_str := l_wdth_where_str
4026 --|| 'AND wdth.creation_date >= :from_creation_date ';--Commented in bug 6854145
4027 || 'AND TRUNC(wdth.creation_date) >= TRUNC(:from_creation_date) ';--Added TRUNC in bug 6854145
4028 END IF;
4029
4030 IF wms_plan_tasks_pvt.g_to_creation_date IS NOT NULL THEN
4031 l_wdth_where_str := l_wdth_where_str
4032 --|| 'AND wdth.creation_date <= :to_creation_date ';--Commented in bug 6854145
4033 || 'AND TRUNC(wdth.creation_date) <= TRUNC(:to_creation_date) ';--Added TRUNC in bug 6854145
4034 END IF;
4035
4036 IF wms_plan_tasks_pvt.g_to_locator_visible = 'T' THEN
4037 l_wdth_where_str := l_wdth_where_str
4038 || 'and wdth.dest_locator_id = milv1.inventory_location_id (+) '
4039 || 'AND wdth.dest_subinventory_code = milv1.subinventory_code (+) ';
4040 END IF;
4041 /* Filter based on the status - completed/cancelled/aborted. */
4042 l_wdth_where_str := l_wdth_where_str || 'AND wdth.status in (';
4043 IF wms_plan_tasks_pvt.g_is_completed_plan THEN
4044 l_wdth_where_str := l_wdth_where_str || '6';
4045 IF wms_plan_tasks_pvt.g_is_cancelled_plan THEN
4046 l_wdth_where_str := l_wdth_where_str || ',12';
4047 END IF;
4048 IF wms_plan_tasks_pvt.g_is_aborted_plan THEN
4049 l_wdth_where_str := l_wdth_where_str || ',11';
4050 END IF;
4051 ELSIF wms_plan_tasks_pvt.g_is_cancelled_plan THEN
4052 l_wdth_where_str := l_wdth_where_str || '12';
4053 IF wms_plan_tasks_pvt.g_is_aborted_plan THEN
4054 l_wdth_where_str := l_wdth_where_str || ',11';
4055 END IF;
4056 ELSIF wms_plan_tasks_pvt.g_is_aborted_plan THEN
4057 l_wdth_where_str := l_wdth_where_str || '11';
4058 END IF;
4059
4060 l_wdth_where_str := l_wdth_where_str || ')';
4061
4062 IF l_debug = 1 THEN
4063 debug(' l_wdth_select_str ' || l_wdth_select_str,'get_wdth_plan_records');
4064 debug(' l_wdth_from_str ' || l_wdth_from_str,'get_wdth_plan_records');
4065 debug(' l_wdth_where_str ' || l_wdth_where_str,'get_wdth_plan_records');
4066 END IF;
4067
4068 x_wdth_select_str := l_wdth_select_str;
4069 x_wdth_from_str := l_wdth_from_str;
4070 x_wdth_where_str := l_wdth_where_str;
4071
4072 END get_wdth_plan_records;
4073
4074 PROCEDURE clear_globals IS
4075
4076 l_module_name CONSTANT VARCHAR2(20) := 'clear_globals';
4077 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
4078 BEGIN
4079 IF l_debug = 1 THEN
4080 debug(' in clear_globals ',l_module_name);
4081 END IF;
4082 --wms_plan_tasks_pvt.g_add := FALSE;
4083 wms_plan_tasks_pvt.g_organization_id := NULL;
4084 wms_plan_tasks_pvt.g_subinventory_code := NULL;
4085 wms_plan_tasks_pvt.g_locator_id := NULL;
4086 wms_plan_tasks_pvt.g_to_subinventory_code := NULL;
4087 wms_plan_tasks_pvt.g_to_locator_id := NULL;
4088 wms_plan_tasks_pvt.g_inventory_item_id := NULL;
4089 wms_plan_tasks_pvt.g_category_set_id := NULL;
4090 wms_plan_tasks_pvt.g_item_category_id := NULL;
4091 wms_plan_tasks_pvt.g_person_id := NULL;
4092 wms_plan_tasks_pvt.g_person_resource_id := NULL;
4093 wms_plan_tasks_pvt.g_equipment_type_id := NULL;
4094 wms_plan_tasks_pvt.g_machine_resource_id := NULL;
4095 wms_plan_tasks_pvt.g_machine_instance := NULL;
4096 wms_plan_tasks_pvt.g_user_task_type_id := NULL;
4097 wms_plan_tasks_pvt.g_from_task_quantity := NULL;
4098 wms_plan_tasks_pvt.g_to_task_quantity := NULL;
4099 wms_plan_tasks_pvt.g_from_task_priority := NULL;
4100 wms_plan_tasks_pvt.g_to_task_priority := NULL;
4101 wms_plan_tasks_pvt.g_from_creation_date := NULL;
4102 wms_plan_tasks_pvt.g_to_creation_date := NULL;
4103
4104 wms_plan_tasks_pvt.g_is_unreleased_task := FALSE;
4105 wms_plan_tasks_pvt.g_is_pending_task := FALSE;
4106 wms_plan_tasks_pvt.g_is_queued_task := FALSE;
4107 wms_plan_tasks_pvt.g_is_dispatched_task := FALSE;
4108 wms_plan_tasks_pvt.g_is_active_task := FALSE;
4109 wms_plan_tasks_pvt.g_is_loaded_task := FALSE;
4110 wms_plan_tasks_pvt.g_is_completed_task := FALSE ;
4111
4112 wms_plan_tasks_pvt.g_include_inbound := FALSE;
4113 wms_plan_tasks_pvt.g_include_outbound := FALSE;
4114 wms_plan_tasks_pvt.g_include_manufacturing := FALSE;
4115 wms_plan_tasks_pvt.g_include_warehousing := FALSE;
4116 wms_plan_tasks_pvt.g_from_po_header_id := NULL;
4117 wms_plan_tasks_pvt.g_to_po_header_id := NULL;
4118 wms_plan_tasks_pvt.g_from_purchase_order := NULL;
4119 wms_plan_tasks_pvt.g_to_purchase_order := NULL;
4120 wms_plan_tasks_pvt.g_from_rma_header_id := NULL;
4121 wms_plan_tasks_pvt.g_to_rma_header_id := NULL;
4122 wms_plan_tasks_pvt.g_from_rma := NULL;
4123 wms_plan_tasks_pvt.g_to_rma := NULL;
4124 wms_plan_tasks_pvt.g_from_requisition_header_id := NULL;
4125 wms_plan_tasks_pvt.g_to_requisition_header_id:= NULL;
4126 wms_plan_tasks_pvt.g_from_requisition := NULL;
4127 wms_plan_tasks_pvt.g_to_requisition := NULL;
4128 wms_plan_tasks_pvt.g_from_shipment_number := NULL;
4129 wms_plan_tasks_pvt.g_to_shipment_number := NULL;
4130 wms_plan_tasks_pvt.g_include_sales_orders := TRUE;
4131 wms_plan_tasks_pvt.g_include_internal_orders := TRUE;
4132 wms_plan_tasks_pvt.g_from_sales_order_id := NULL;
4133 wms_plan_tasks_pvt.g_to_sales_order_id := NULL;
4134 wms_plan_tasks_pvt.g_from_pick_slip_number := NULL;
4135 wms_plan_tasks_pvt.g_to_pick_slip_number := NULL;
4136 wms_plan_tasks_pvt.g_customer_id := NULL;
4137 wms_plan_tasks_pvt.g_customer_category := NULL;
4138 wms_plan_tasks_pvt.g_delivery_id := NULL;
4139 wms_plan_tasks_pvt.g_carrier_id := NULL;
4140 wms_plan_tasks_pvt.g_ship_method := NULL;
4141 wms_plan_tasks_pvt.g_shipment_priority := NULL;
4142 wms_plan_tasks_pvt.g_trip_id := NULL;
4143 wms_plan_tasks_pvt.g_from_shipment_date := NULL;
4144 wms_plan_tasks_pvt.g_to_shipment_date := NULL;
4145 wms_plan_tasks_pvt.g_ship_to_state := NULL;
4146 wms_plan_tasks_pvt.g_ship_to_country := NULL;
4147 wms_plan_tasks_pvt.g_ship_to_postal_code := NULL;
4148 wms_plan_tasks_pvt.g_from_number_of_order_lines := NULL;
4149 wms_plan_tasks_pvt.g_to_number_of_order_lines := NULL;
4150 wms_plan_tasks_pvt.g_manufacturing_type := NULL;
4151 wms_plan_tasks_pvt.g_from_job := NULL;
4152 wms_plan_tasks_pvt.g_to_job := NULL;
4153 wms_plan_tasks_pvt.g_assembly_id := NULL;
4154 wms_plan_tasks_pvt.g_from_start_date := NULL;
4155 wms_plan_tasks_pvt.g_to_start_date := NULL;
4156 wms_plan_tasks_pvt.g_from_line := NULL;
4157 wms_plan_tasks_pvt.g_to_line := NULL;
4158 wms_plan_tasks_pvt.g_department_id := NULL;
4159 wms_plan_tasks_pvt.g_include_replenishment := TRUE;
4160 wms_plan_tasks_pvt.g_from_replenishment_mo := NULL;
4161 wms_plan_tasks_pvt.g_to_replenishment_mo := NULL;
4162 wms_plan_tasks_pvt.g_include_mo_transfer := TRUE;
4163 wms_plan_tasks_pvt.g_include_mo_issue := TRUE;
4164 wms_plan_tasks_pvt.g_from_transfer_issue_mo := NULL;
4165 wms_plan_tasks_pvt.g_to_transfer_issue_mo := NULL;
4166 wms_plan_tasks_pvt.g_include_lpn_putaway := TRUE;
4167 wms_plan_tasks_pvt.g_include_staging_move := FALSE;
4168 wms_plan_tasks_pvt.g_include_cycle_count := TRUE;
4169 wms_plan_tasks_pvt.g_cycle_count_name := NULL;
4170
4171 wms_plan_tasks_pvt.g_query_independent_tasks := NULL;
4172 wms_plan_tasks_pvt.g_query_planned_tasks := FALSE;
4173
4174 wms_plan_tasks_pvt.g_is_pending_plan := FALSE;
4175 wms_plan_tasks_pvt.g_is_inprogress_plan := FALSE;
4176 wms_plan_tasks_pvt.g_is_completed_plan := FALSE;
4177 wms_plan_tasks_pvt.g_is_cancelled_plan := FALSE;
4178 wms_plan_tasks_pvt.g_is_aborted_plan := FALSE;
4179
4180 wms_plan_tasks_pvt.g_activity_id := NULL;
4181 wms_plan_tasks_pvt.g_plan_type_id := NULL;
4182 wms_plan_tasks_pvt.g_op_plan_id := NULL;
4183
4184 wms_plan_tasks_pvt.g_inbound_specific_query := FALSE;
4185 wms_plan_tasks_pvt.g_outbound_specific_query := FALSE;
4186 wms_plan_tasks_pvt.g_plans_tasks_record_count := 0;
4187 wms_plan_tasks_pvt.g_from_inbound := FALSE;
4188
4189
4190 END clear_globals;
4191
4192 END;