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