[Home] [Help]
PACKAGE BODY: APPS.WMS_TASK_ACTION_PVT
Source
1 PACKAGE BODY WMS_TASK_ACTION_PVT AS
2 /* $Header: WMSTACPB.pls 120.10.12020000.3 2012/11/08 08:41:07 gjyoti ship $ */
3 l_is_unreleased BOOLEAN;
4 l_is_pending BOOLEAN;
5 l_is_queued BOOLEAN;
6 l_is_dispatched BOOLEAN;
7 l_is_active BOOLEAN;
8 l_is_loaded BOOLEAN;
9 l_is_completed BOOLEAN;
10 l_include_inbound BOOLEAN;
11 l_include_outbound BOOLEAN;
12 l_include_crossdock BOOLEAN;
13 l_include_manufacturing BOOLEAN;
14 l_include_warehousing BOOLEAN;
15 l_include_sales_orders BOOLEAN;
16 l_include_internal_orders BOOLEAN;
17 l_include_replenishment BOOLEAN;
18 l_include_mo_transfer BOOLEAN;
19 l_include_mo_issue BOOLEAN;
20 l_include_lpn_putaway BOOLEAN;
21 l_include_staging_move BOOLEAN;
22 l_include_cycle_count BOOLEAN;
23 l_is_pending_plan BOOLEAN;
24 l_is_inprogress_plan BOOLEAN;
25 l_is_completed_plan BOOLEAN;
26 l_is_cancelled_plan BOOLEAN;
27 l_is_aborted_plan BOOLEAN;
28 l_query_independent_tasks BOOLEAN;
29 l_query_planned_tasks BOOLEAN;
30
31 l_organization_id NUMBER;
32 l_subinventory VARCHAR2(240);
33 l_locator_id NUMBER;
34 l_to_subinventory VARCHAR2(240);
35 l_to_locator_id NUMBER;
36 l_inventory_item_id NUMBER;
37 l_category_set_id NUMBER;
38 l_item_category_id NUMBER;
39 l_employee_id NUMBER;
40 l_equipment_type_id NUMBER;
41 l_equipment VARCHAR2(240);
42 l_user_task_type_id NUMBER;
43 l_from_task_quantity NUMBER;
44 l_to_task_quantity NUMBER;
45 l_from_task_priority NUMBER;
46 l_to_task_priority NUMBER;
47 l_from_creation_date DATE;
48 l_to_creation_date DATE;
49 l_from_purchase_order VARCHAR2(240);
50 l_from_po_header_id NUMBER;
51 l_to_purchase_order VARCHAR2(240);
52 l_to_po_header_id NUMBER;
53 l_from_rma VARCHAR2(240);
54 l_from_rma_header_id NUMBER;
55 l_to_rma VARCHAR2(240);
56 l_to_rma_header_id NUMBER;
57 l_from_requisition VARCHAR2(240);
58 l_from_requisition_header_id NUMBER;
59 l_to_requisition VARCHAR2(240);
60 l_to_requisition_header_id NUMBER;
61 l_from_shipment VARCHAR2(240);
62 l_to_shipment VARCHAR2(240);
63 l_from_sales_order_id NUMBER;
64 l_to_sales_order_id NUMBER;
65 l_from_pick_slip NUMBER;
66 l_to_pick_slip NUMBER;
67 l_customer_id NUMBER;
68 l_customer_category VARCHAR2(240);
69 l_delivery_id NUMBER;
70 l_carrier_id NUMBER;
71 l_ship_method_code VARCHAR2(240);
72 l_trip_id NUMBER;
73 l_shipment_priority VARCHAR2(240);
74 l_from_shipment_date DATE;
75 l_to_shipment_date DATE;
76 l_ship_to_state VARCHAR2(240);
77 l_ship_to_country VARCHAR2(240);
78 l_ship_to_postal_code VARCHAR2(240);
79 l_from_lines_in_sales_order NUMBER;
80 l_to_lines_in_sales_order NUMBER;
81 l_manufacturing_type VARCHAR2(240);
82 l_from_job VARCHAR2(240);
83 l_to_job VARCHAR2(240);
84 l_assembly_id NUMBER;
85 l_from_start_date DATE;
86 l_to_start_date DATE;
87 l_from_line VARCHAR2(240);
88 l_to_line VARCHAR2(240);
89 l_department_id NUMBER;
90 l_from_replenishment_mo VARCHAR2(240);
91 l_to_replenishment_mo VARCHAR2(240);
92 l_from_transfer_issue_mo VARCHAR2(240);
93 l_to_transfer_issue_mo VARCHAR2(240);
94 l_cycle_count_name VARCHAR2(240);
95 l_op_plan_activity_id NUMBER;
96 l_op_plan_type_id NUMBER;
97 l_op_plan_id NUMBER;
98 --added for bug:13014493
99 l_time_till_shipment NUMBER;
100 l_time_till_shipment_uom_code NUMBER;
101
102 l_action_description VARCHAR2(1000);
103 l_tasks_total NUMBER;
104 l_action_type VARCHAR2(10);
105 l_status VARCHAR2(60);
106 l_status_code NUMBER;
107 l_priority_type VARCHAR2(10);
108 l_priority NUMBER;
109 l_clear_priority VARCHAR2(100);
110 l_assign_type VARCHAR2(100);
111 l_employee VARCHAR2(100);
112 l_user_task_type VARCHAR2(100);
113 l_effective_start_date DATE;
114 l_effective_end_date DATE;
115 l_person_resource_id NUMBER;
116 l_person_resource_code VARCHAR2(100);
117 l_override_emp_check BOOLEAN;
118
119 l_return_status VARCHAR2(1);
120 l_temp_query BOOLEAN;
121 l_temp_action BOOLEAN;
122 l_wave_header_id NUMBER;
123 PROCEDURE DEBUG
124 (
125 p_message VARCHAR2,
126 p_module VARCHAR2 DEFAULT 'WMS_TASK_ACTION_PVT'
127 )
128 IS
129 l_counter NUMBER := 1;
130 i NUMBER ;
131 BEGIN
132 WHILE l_counter < LENGTH (p_message)
133 LOOP
134 inv_log_util.TRACE (SUBSTR (p_message, l_counter, 80), p_module);
135 l_counter := l_counter + 80;
136 END LOOP;
137 RETURN;
138 END DEBUG;
139
140 PROCEDURE GET_TRANSACTION_TASK_IDS
141 ( p_task_type_id IN NUMBER,
142 p_transaction_temp_id_tbl OUT NOCOPY wms_waveplan_tasks_pvt.transaction_temp_table_type,
143 p_task_type_id_table OUT NOCOPY wms_waveplan_tasks_pvt.task_type_id_table_type,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_return_message OUT NOCOPY VARCHAR2
146 )
147 IS
148 i NUMBER;
149 l_transaction_temp_id wms_waveplan_tasks_temp.transaction_temp_id%type;
150 l_task_type_id wms_waveplan_tasks_temp.task_type_id%type;
151 CURSOR c_transaction_task_ids( p_task_type_id NUMBER)
152 IS
153 SELECT DISTINCT transaction_temp_id
154 FROM wms_waveplan_tasks_temp
155 WHERE task_type_id = p_task_type_id;
156 BEGIN
157 DEBUG( 'Inside GET_TRANSACTION_TASK_IDS procedure.');
158 DEBUG( 'p_task_type_id = ' || p_task_type_id);
159 i:= 1;
160 l_task_type_id := p_task_type_id;
161
162 DEBUG( 'Pupulating transaction id table');
163
164 FOR rec_ids IN c_transaction_task_ids( p_task_type_id )
165 LOOP
166 l_transaction_temp_id := rec_ids.transaction_temp_id;
167 p_transaction_temp_id_tbl(i) := l_transaction_temp_id;
168 p_task_type_id_table(i) := l_task_type_id;
169 i := i+1;
170 END LOOP;
171
172 DEBUG( 'Transaction id table populated');
173
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 DEBUG( 'Exiting GET_TRANSACTION_TASK_IDS');
177
178 EXCEPTION
179 WHEN OTHERS
180 THEN
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 x_return_message:= 'Unexpected error has occured in WMS_TASK_ACTION_PVT.GET_TRANSACTION_TASK_IDS. '
183 || 'Oracle error message is ' || SQLERRM;
184 DEBUG( 'Unexpected error has occured. Oracle error message is '
185 || SQLERRM , 'WMS_TASK_ACTION_PVT.SUBMIT_REQUEST' );
186 END GET_TRANSACTION_TASK_IDS;
187
188 PROCEDURE UPDATE_TASK
189 ( x_return_status OUT NOCOPY VARCHAR2,
190 x_return_message OUT NOCOPY VARCHAR2
191 )
192 IS
193 l_transaction_temp_id_tbl wms_waveplan_tasks_pvt.transaction_temp_table_type;
194 l_task_type_id_table wms_waveplan_tasks_pvt.task_type_id_table_type;
195 l_result wms_waveplan_tasks_pvt.result_table_type;
196 l_message wms_waveplan_tasks_pvt.message_table_type;
197 l_task_id wms_waveplan_tasks_pvt.task_id_table_type;
198 l_return_msg VARCHAR2( 120);
199 l_msg_count NUMBER;
200 l_task_type_id NUMBER;
201 l_return_message VARCHAR2( 4000 );
202
203 CURSOR c_task_type_id
204 IS
205 SELECT distinct to_number(wwtt.task_type_id) task_type_id,
206 mfl.meaning
207 FROM wms_waveplan_tasks_temp wwtt,
208 mfg_lookups mfl
209 WHERE wwtt.task_type_id = mfl.lookup_code
210 AND mfl.lookup_type = 'WMS_TASK_TYPES';
211 BEGIN
212 DEBUG( 'Inside UPDATE_TASK');
213 DEBUG( 'Opening cursor c_task_type_id');
214
215 FOR rec_task_type_id in c_task_type_id
216 LOOP
217 l_task_type_id := rec_task_type_id.task_type_id;
218
219 DEBUG( 'Calling GET_TRANSACTION_TASK_IDS');
220 DEBUG( 'Task type id passed = ' || l_task_type_id );
221 --Calling GET_TRANSACTION_TASK_IDS to get transaction_temp_id and saved_action_type
222
223 GET_TRANSACTION_TASK_IDS
224 (
225 p_task_type_id => l_task_type_id,
226 p_transaction_temp_id_tbl => l_transaction_temp_id_tbl,
227 p_task_type_id_table => l_task_type_id_table,
228 x_return_status => l_return_status,
229 x_return_message => l_return_message
230 );
231
232 DEBUG( 'GET_TRANSACTION_TASK_IDS return status = ' || l_return_status );
233 DEBUG( 'GET_TRANSACTION_TASK_IDS return message = ' || l_return_message );
234
235 IF l_return_status = fnd_api.g_ret_sts_error OR
236 l_return_status = fnd_api.g_ret_sts_unexp_error
237 THEN
238 DEBUG (' Error in GET_TRANSACTION_TASK_IDS ' );
239 x_return_status := FND_API.G_RET_STS_ERROR;
240 x_return_message:= ( 'GET_TRANSACTION_TASK_IDS returned with Error status.'
241 ||' Error message is ' || l_return_message);
242 return;
243 END IF;
244
245 DEBUG( 'Task_Type_Id = ' || l_task_type_id );
246 DEBUG( 'Task Type in process = ' || rec_task_type_id.meaning );
247 DEBUG( 'No of tasks to be updated = ' || l_transaction_temp_id_tbl.count );
248
249 IF ( ( l_transaction_temp_id_tbl.count > 0 ) and ( l_task_type_id_table.count > 0 ) )
250 THEN
251 /* Bug 5485730 - The employee details should be null if the status is being updated
252 to Pending or Unreleased */
253 IF l_status_code IN (1,8) THEN
254 l_employee := NULL;
255 l_employee_id := NULL;
256 l_user_task_type := NULL;
257 l_user_task_type_id := NULL;
258 l_effective_start_date := NULL;
259 l_effective_end_date := NULL;
260 l_person_resource_id := NULL;
261 l_person_resource_code := NULL;
262 END IF;
263 /* End of Bug 5485730 */
264 --call update task
265 DEBUG( 'Calling WMS_WAVEPLAN_TASKS_PVT.UPDATE_TASK');
266 DEBUG( 'Following are the input parameters');
267 DEBUG( 'p_employee => ' || l_employee);
268 DEBUG( 'p_employee_id => ' || l_employee_id);
269 DEBUG( 'p_user_task_type => ' || l_user_task_type);
270 DEBUG( 'p_user_task_type_id => ' || l_user_task_type_id);
271 DEBUG( 'p_effective_start_date => ' || l_effective_start_date);
272 DEBUG( 'p_effective_end_date => ' || l_effective_end_date);
273 DEBUG( 'p_person_resource_id => ' || l_person_resource_id);
274 DEBUG( 'p_person_resource_code => ' || l_person_resource_code);
275 DEBUG( 'p_to_status => ' || l_status);
276 DEBUG( 'p_to_status_id => ' || l_status_code);
277 DEBUG( 'p_update_priority_type => ' || l_priority_type);
278 DEBUG( 'p_update_priority => ' || l_priority);
279 DEBUG( 'p_clear_priority => ' || l_clear_priority);
280
281 IF l_override_emp_check = TRUE
282 THEN
283 DEBUG( 'p_force_employee_change => TRUE');
284 ELSE
285 DEBUG( 'p_force_employee_change => FALSE');
286 END IF;
287
288 wms_waveplan_tasks_pvt.update_task
289 (
290 p_transaction_temp_id => l_transaction_temp_id_tbl,
291 p_task_type_id => l_task_type_id_table,
292 p_employee => l_employee,
293 p_employee_id => l_employee_id,
294 p_user_task_type => l_user_task_type,
295 p_user_task_type_id => l_user_task_type_id,
296 p_effective_start_date => l_effective_start_date,
297 p_effective_end_date => l_effective_end_date,
298 p_person_resource_id => l_person_resource_id,
299 p_person_resource_code => l_person_resource_code,
300 p_force_employee_change => l_override_emp_check,
301 p_to_status => l_status,
302 p_to_status_id => l_status_code,
303 p_update_priority_type => l_priority_type,
304 p_update_priority => l_priority,
305 p_clear_priority => l_clear_priority,
306 x_result => l_result,
307 x_message => l_message,
308 x_task_id => l_task_id,
309 x_return_status => l_return_status,
310 x_return_msg => l_return_msg,
311 x_msg_count => l_msg_count
312 );
313
314 DEBUG( 'WMS_WAVEPLAN_TASKS_PVT.UPDATE_TASK return status = '|| l_return_status );
315 DEBUG( 'WMS_WAVEPLAN_TASKS_PVT.UPDATE_TASK return message = '|| l_return_msg );
316
317 DEBUG( 'No of tasks updated = ' || l_task_id.count );
318
319 IF l_return_status = FND_API.G_RET_STS_ERROR OR
320 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
321 THEN
322 DEBUG (' Error in WMS_WAVEPLAN_TASKS_PVT.UPDATE_TASK ' || l_return_msg );
323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
324 x_return_message:= 'Error in wms_waveplan_tasks_pvt.update_task. '
325 || 'Error message is ' || l_return_msg;
326 return;
327 END IF;
328 END IF;
329 END LOOP;
330
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 DEBUG( 'Exiting UPDATE_TASK');
334
335 EXCEPTION
336 WHEN OTHERS
337 THEN
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339 x_return_message:= 'Unexpected error has occured in WMS_TASK_ACTION_PVT.UPDATE_TASK. '
340 || 'Oracle error message is ' || SQLERRM ;
341 DEBUG( 'Unexpected error has occured. Oracle error message is '
342 || SQLERRM , 'WMS_TASK_ACTION_PVT.SUBMIT_REQUEST' );
343 END UPDATE_TASK;
344
345
346 PROCEDURE CANCEL_TASK
347 ( x_return_status OUT NOCOPY VARCHAR2,
348 x_return_message OUT NOCOPY VARCHAR2
349 )
350 IS
351 l_transaction_temp_id_tbl wms_waveplan_tasks_pvt.transaction_temp_table_type;
352 l_task_type_id_table wms_waveplan_tasks_pvt.task_type_id_table_type;
353 l_result wms_waveplan_tasks_pvt.result_table_type;
354 l_message wms_waveplan_tasks_pvt.message_table_type;
355 l_task_id wms_waveplan_tasks_pvt.task_id_table_type;
356 l_return_msg VARCHAR2( 120);
357 l_msg_count NUMBER;
358 l_task_type_id NUMBER;
359 l_return_message VARCHAR2( 4000 );
360
361 CURSOR c_task_type_id
362 IS
363 SELECT distinct to_number(wwtt.task_type_id) task_type_id,
364 mfl.meaning
365 FROM wms_waveplan_tasks_temp wwtt,
366 mfg_lookups mfl
367 WHERE wwtt.task_type_id = mfl.lookup_code
368 AND mfl.lookup_type = 'WMS_TASK_TYPES';
369 BEGIN
370 DEBUG( 'Inside CANCEL_TASK');
371 DEBUG( 'Opening cursor c_task_type_id');
372
373 FOR rec_task_type_id in c_task_type_id
374 LOOP
375 l_task_type_id := rec_task_type_id.task_type_id;
376
377 DEBUG( 'Calling GET_TRANSACTION_TASK_IDS');
378 DEBUG( 'Task type id passed = ' || l_task_type_id );
379 --Calling GET_TRANSACTION_TASK_IDS to get transaction_temp_id and saved_action_type
380
381 GET_TRANSACTION_TASK_IDS
382 (
383 p_task_type_id => l_task_type_id,
384 p_transaction_temp_id_tbl => l_transaction_temp_id_tbl,
385 p_task_type_id_table => l_task_type_id_table,
386 x_return_status => l_return_status,
387 x_return_message => l_return_message
388 );
389
390 DEBUG( 'GET_TRANSACTION_TASK_IDS return status = ' || l_return_status );
391 DEBUG( 'GET_TRANSACTION_TASK_IDS return message = ' || l_return_message );
392
393 IF l_return_status = fnd_api.g_ret_sts_error OR
394 l_return_status = fnd_api.g_ret_sts_unexp_error
395 THEN
396 DEBUG (' Error in GET_TRANSACTION_TASK_IDS ' );
397 x_return_status := FND_API.G_RET_STS_ERROR;
398 x_return_message:= ( 'GET_TRANSACTION_TASK_IDS returned with Error status.'
399 ||' Error message is ' || l_return_message);
400 return;
401 END IF;
402
403 DEBUG( 'Task_Type_Id = ' || l_task_type_id );
404 DEBUG( 'Task Type in process = ' || rec_task_type_id.meaning );
405 DEBUG( 'No of tasks to be cancelled = ' || l_transaction_temp_id_tbl.count );
406
407 IF ( ( l_transaction_temp_id_tbl.count > 0 ) and ( l_task_type_id_table.count > 0 ) )
408 THEN
409 --call update task
410 DEBUG( 'Calling WMS_WAVEPLAN_TASKS_PVT.CANCEL_TASK');
411 IF (wms_plan_tasks_pvt.g_include_crossdock ) THEN
412 DEBUG( 'global variable wms_plan_tasks_pvt.g_include_crossdock = TRUE ');
413 ELSE
414 DEBUG( 'global variable wms_plan_tasks_pvt.g_include_crossdock = FALSE ');
415 END IF;
416
417 wms_waveplan_tasks_pvt.cancel_task
418 (
419 p_transaction_temp_id => l_transaction_temp_id_tbl,
420 p_task_type_id => l_task_type_id_table,
421 p_is_crossdock => wms_plan_tasks_pvt.g_include_crossdock, --Bug#6075802.
422 x_result => l_result,
423 x_message => l_message,
424 x_task_id => l_task_id,
425 x_return_status => l_return_status,
426 x_return_msg => l_return_msg,
427 x_msg_count => l_msg_count
428 );
429
430 DEBUG( 'WMS_WAVEPLAN_TASKS_PVT.CANCEL_TASK return status = '|| l_return_status );
431 DEBUG( 'WMS_WAVEPLAN_TASKS_PVT.CANCEL_TASK return message = '|| l_return_msg );
432
433 DEBUG( 'No of tasks cancelled = ' || l_task_id.count );
434
435 IF l_return_status = FND_API.G_RET_STS_ERROR OR
436 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
437 THEN
438 DEBUG (' Error in WMS_WAVEPLAN_TASKS_PVT.CANCEL_TASK ' || l_return_msg );
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
440 x_return_message:= 'Error in wms_waveplan_tasks_pvt.cancel_task. '
441 || 'Error message is ' || l_return_msg;
442 return;
443 END IF;
444 END IF;
445 END LOOP;
446
447 x_return_status := FND_API.G_RET_STS_SUCCESS;
448
449 DEBUG( 'Exiting CANCEL_TASK');
450
451 EXCEPTION
452 WHEN OTHERS
453 THEN
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
455 x_return_message:= 'Unexpected error has occured in WMS_TASK_ACTION_PVT.CANCEL_TASK. '
456 || 'Oracle error message is ' || SQLERRM ;
457 DEBUG( 'Unexpected error has occured. Oracle error message is '
458 || SQLERRM , 'WMS_TASK_ACTION_PVT.SUBMIT_REQUEST' );
459 END CANCEL_TASK;
460
461 PROCEDURE SET_QUERY_TASKS_PARAMETERS
462 (
463 p_field_name_table IN wms_task_action_pvt.field_name_table_type,
464 p_field_value_table IN wms_task_action_pvt.field_value_table_type,
465 p_organization_id_table IN wms_task_action_pvt.organization_id_table_type,
466 p_query_type_table IN wms_task_action_pvt.query_type_table_type,
467 x_return_status OUT NOCOPY VARCHAR2,
468 x_return_message OUT NOCOPY VARCHAR2
469 )
470 IS
471 i number;
472 BEGIN
473 DEBUG( 'Inside SET_QUERY_TASKS_PARAMETERS');
474
475 IF p_field_name_table.count <> 0
476 THEN
477 FOR i in p_field_name_table.first .. p_field_name_table.last
478 LOOP
479 IF ( p_field_name_table(i) = 'FIND_TASKS.UNRELEASED'
480 AND p_field_value_table(i) = 'Y' )
481 THEN
482 l_is_unreleased := TRUE;
483 END IF;
484
485 IF ( p_field_name_table(i) = 'FIND_TASKS.PENDING'
486 AND p_field_value_table(i) = 'Y' )
487 THEN
488 l_is_pending := TRUE;
489 END IF;
490
491 IF ( p_field_name_table(i) = 'FIND_TASKS.QUEUED'
492 AND p_field_value_table(i) = 'Y' )
493 THEN
494 l_is_queued := TRUE;
495 END IF;
496
497 IF ( p_field_name_table(i) = 'FIND_TASKS.DISPATCHED'
498 AND p_field_value_table(i) = 'Y' )
499 THEN
500 l_is_dispatched := TRUE;
501 END IF;
502
503 IF ( p_field_name_table(i) = 'FIND_TASKS.ACTIVE'
504 AND p_field_value_table(i) = 'Y' )
505 THEN
506 l_is_active := TRUE;
507 END IF;
508
509 IF ( p_field_name_table(i) = 'FIND_TASKS.LOADED'
510 AND p_field_value_table(i) = 'Y' )
511 THEN
512 l_is_loaded := TRUE;
513 END IF;
514
515 IF ( p_field_name_table(i) = 'FIND_TASKS.COMPLETED'
516 AND p_field_value_table(i) = 'Y' )
517 THEN
518 l_is_completed := TRUE;
519 END IF;
520
521 IF ( p_field_name_table(i) = 'FIND_TASKS.INBOUND'
522 AND p_field_value_table(i) = 'Y' )
523 THEN
524 l_include_inbound := TRUE;
525 END IF;
526
527 IF ( p_field_name_table(i) = 'FIND_TASKS.OUTBOUND'
528 AND p_field_value_table(i) = 'Y' )
529 THEN
530 l_include_outbound := TRUE;
531 END IF;
532
533 IF ( p_field_name_table(i) = 'FIND_TASKS.CROSSDOCK'
534 AND p_field_value_table(i) = 'Y' )
535 THEN
536 l_include_crossdock := TRUE;
537 END IF;
538
539 IF ( p_field_name_table(i) = 'FIND_TASKS.MANUFACTURING'
540 AND p_field_value_table(i) = 'Y' )
541 THEN
542 l_include_manufacturing := TRUE;
543 END IF;
544
545 IF ( p_field_name_table(i) = 'FIND_TASKS.WAREHOUSING'
546 AND p_field_value_table(i) = 'Y' )
547 THEN
548 l_include_warehousing := TRUE;
549 END IF;
550
551 IF ( p_field_name_table(i) = 'FIND_TASKS.ORDER_TYPE'
552 AND p_field_value_table(i) = 'S' )
553 THEN
554 l_include_sales_orders := TRUE;
555 ELSIF ( p_field_name_table(i) = 'FIND_TASKS.ORDER_TYPE'
556 AND p_field_value_table(i) = 'I' )
557 THEN
558 l_include_internal_orders := TRUE;
559 ELSIF ( p_field_name_table(i) = 'FIND_TASKS.ORDER_TYPE'
560 AND p_field_value_table(i) = 'B' )
561 THEN
562 l_include_sales_orders := TRUE;
563 l_include_internal_orders := TRUE;
564 END IF;
565
566 IF ( p_field_name_table(i) = 'FIND_TASKS.REPLENISHMENT_TASKS'
567 AND p_field_value_table(i) = 'Y' )
568 THEN
569 l_include_replenishment := TRUE;
570 END IF;
571
572 IF ( p_field_name_table(i) = 'FIND_TASKS.MO_TRANSFER_TASKS'
573 AND p_field_value_table(i) = 'Y' )
574 THEN
575 l_include_mo_transfer := TRUE;
576 END IF;
577
578 IF ( p_field_name_table(i) = 'FIND_TASKS.MO_ISSUE_TASKS'
579 AND p_field_value_table(i) = 'Y' )
580 THEN
581 l_include_mo_issue := TRUE;
582 END IF;
583
584 IF ( p_field_name_table(i) = 'FIND_TASKS.LPN_PUTAWAY_TASKS'
585 AND p_field_value_table(i) = 'Y' )
586 THEN
587 l_include_lpn_putaway := TRUE;
588 END IF;
589
590 IF ( p_field_name_table(i) = 'FIND_TASKS.STAGING_MOVE'
591 AND p_field_value_table(i) = 'Y' )
592 THEN
593 l_include_staging_move := TRUE;
594 END IF;
595
596 IF ( p_field_name_table(i) = 'FIND_TASKS.CYCLE_COUNT_TASKS'
597 AND p_field_value_table(i) = 'Y' )
598 THEN
599 l_include_cycle_count := TRUE;
600 END IF;
601
602 IF ( p_field_name_table(i) = 'FIND_TASKS.PLAN_PENDING'
603 AND p_field_value_table(i) = 'Y' )
604 THEN
605 l_is_pending_plan := TRUE;
606 END IF;
607
608 IF ( p_field_name_table(i) = 'FIND_TASKS.PLAN_IN_PROGRESS'
609 AND p_field_value_table(i) = 'Y' )
610 THEN
611 l_is_inprogress_plan := TRUE;
612 END IF;
613
614 IF ( p_field_name_table(i) = 'FIND_TASKS.PLAN_COMPLETED'
615 AND p_field_value_table(i) = 'Y' )
616 THEN
617 l_is_completed_plan := TRUE;
618 END IF;
619
620 IF ( p_field_name_table(i) = 'FIND_TASKS.PLAN_CANCELLED'
621 AND p_field_value_table(i) = 'Y' )
622 THEN
623 l_is_cancelled_plan := TRUE;
624 END IF;
625
626 IF ( p_field_name_table(i) = 'FIND_TASKS.PLAN_ABORTED'
627 AND p_field_value_table(i) = 'Y' )
628 THEN
629 l_is_aborted_plan := TRUE;
630 END IF;
631
632 IF ( p_field_name_table(i) = 'FIND_TASKS.PLANNED_TASKS'
633 AND p_field_value_table(i) = 'Y' )
634 THEN
635 l_query_planned_tasks := TRUE;
636 END IF;
637
638 IF ( p_field_name_table(i) = 'FIND_TASKS.INDEPENDENT_TASKS'
639 AND p_field_value_table(i) = 'Y' )
640 THEN
641 l_query_independent_tasks := TRUE;
642 END IF;
643
644 IF p_field_name_table(i) = 'FIND_TASKS.SUBINVENTORY'
645 THEN
646 l_subinventory := p_field_value_table(i);
647 ELSIF p_field_name_table(i) = 'FIND_TASKS.LOCATOR_ID'
648 THEN
649 l_locator_id := p_field_value_table(i);
650 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_SUBINVENTORY'
651 THEN
652 l_to_subinventory := p_field_value_table(i);
653 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_LOCATOR_ID'
654 THEN
655 l_to_locator_id := p_field_value_table(i);
656 ELSIF p_field_name_table(i) = 'FIND_TASKS.INVENTORY_ITEM_ID'
657 THEN
658 l_inventory_item_id := p_field_value_table(i);
659 ELSIF p_field_name_table(i) = 'FIND_TASKS.CATEGORY_SET_ID'
660 THEN
661 l_category_set_id := p_field_value_table(i);
662 ELSIF p_field_name_table(i) = 'FIND_TASKS.ITEM_CATEGORY_ID'
663 THEN
664 l_item_category_id := p_field_value_table(i);
665 ELSIF p_field_name_table(i) = 'FIND_TASKS.EMPLOYEE_ID'
666 THEN
667 l_employee_id := p_field_value_table(i);
668 ELSIF p_field_name_table(i) = 'FIND_TASKS.PERSON_RESOURCE_ID'
669 THEN
670 l_person_resource_id := p_field_value_table(i);
671 ELSIF p_field_name_table(i) = 'FIND_TASKS.EQUIPMENT_TYPE_ID'
672 THEN
673 l_equipment_type_id := p_field_value_table(i);
674 ELSIF p_field_name_table(i) = 'FIND_TASKS.EQUIPMENT'
675 THEN
676 l_equipment := p_field_value_table(i);
677 ELSIF p_field_name_table(i) = 'FIND_TASKS.USER_TASK_TYPE_ID'
678 THEN
679 l_user_task_type_id := p_field_value_table(i);
680 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_TASK_QUANTITY'
681 THEN
682 l_from_task_quantity := p_field_value_table(i);
683 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_TASK_QUANTITY'
684 THEN
685 l_to_task_quantity := p_field_value_table(i);
686 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_TASK_PRIORITY'
687 THEN
688 l_from_task_priority := p_field_value_table(i);
689 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_TASK_PRIORITY'
690 THEN
691 l_to_task_priority := p_field_value_table(i);
692 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_CREATION_DATE'
693 THEN
694 l_from_creation_date := FND_DATE.CHARDT_TO_DATE( p_field_value_table(i), 1);
695 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_CREATION_DATE'
696 THEN
697 l_to_creation_date := FND_DATE.CHARDT_TO_DATE( p_field_value_table(i), 1);
698 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_PURCHASE_ORDER'
699 THEN
700 l_from_purchase_order := p_field_value_table(i);
701 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_PO_HEADER_ID'
702 THEN
703 l_from_po_header_id := p_field_value_table(i);
704 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_PURCHASE_ORDER'
705 THEN
706 l_to_purchase_order := p_field_value_table(i);
707 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_PO_HEADER_ID'
708 THEN
709 l_to_po_header_id := p_field_value_table(i);
710 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_RMA'
711 THEN
712 l_from_rma := p_field_value_table(i);
713 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_RMA_HEADER_ID'
714 THEN
715 l_from_rma_header_id := p_field_value_table(i);
716 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_RMA'
717 THEN
718 l_to_rma := p_field_value_table(i);
719 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_RMA_HEADER_ID'
720 THEN
721 l_to_rma_header_id := p_field_value_table(i);
722 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_REQUISITION'
723 THEN
724 l_from_requisition := p_field_value_table(i);
725 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_REQUISITION_HEADER_ID'
726 THEN
727 l_from_requisition_header_id := p_field_value_table(i);
728 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_REQUISITION'
729 THEN
730 l_to_requisition := p_field_value_table(i);
731 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_REQUISITION_HEADER_ID'
732 THEN
733 l_to_requisition_header_id := p_field_value_table(i);
734 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_SHIPMENT'
735 THEN
736 l_from_shipment := p_field_value_table(i);
737 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_SHIPMENT'
738 THEN
739 l_to_shipment := p_field_value_table(i);
740 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_SALES_ORDER_ID'
741 THEN
742 l_from_sales_order_id := p_field_value_table(i);
743 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_SALES_ORDER_ID'
744 THEN
745 l_to_sales_order_id := p_field_value_table(i);
746 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_PICK_SLIP'
747 THEN
748 l_from_pick_slip := p_field_value_table(i);
749 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_PICK_SLIP'
750 THEN
751 l_to_pick_slip := p_field_value_table(i);
752 ELSIF p_field_name_table(i) = 'FIND_TASKS.CUSTOMER_ID'
753 THEN
754 l_customer_id := p_field_value_table(i);
755 ELSIF p_field_name_table(i) = 'FIND_TASKS.CUSTOMER_CATEGORY'
756 THEN
757 l_customer_category := p_field_value_table(i);
758 ELSIF p_field_name_table(i) = 'FIND_TASKS.DELIVERY_ID'
759 THEN
760 l_delivery_id := p_field_value_table(i);
761 ELSIF p_field_name_table(i) = 'FIND_TASKS.CARRIER_ID'
762 THEN
763 l_carrier_id := p_field_value_table(i);
764 ELSIF p_field_name_table(i) = 'FIND_TASKS.SHIP_METHOD_CODE'
765 THEN
766 l_ship_method_code := p_field_value_table(i);
767 ELSIF p_field_name_table(i) = 'FIND_TASKS.TRIP_ID'
768 THEN
769 l_trip_id := p_field_value_table(i);
770 ELSIF p_field_name_table(i) = 'FIND_TASKS.SHIPMENT_PRIORITY'
771 THEN
772 l_shipment_priority := p_field_value_table(i);
773 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_SHIPMENT_DATE'
774 THEN
775 l_from_shipment_date := FND_DATE.CHARDT_TO_DATE( p_field_value_table(i), 1);
776 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_SHIPMENT_DATE'
777 THEN
778 l_to_shipment_date := FND_DATE.CHARDT_TO_DATE( p_field_value_table(i), 1);
779 ELSIF p_field_name_table(i) = 'FIND_TASKS.SHIP_TO_STATE'
780 THEN
781 l_ship_to_state := p_field_value_table(i);
782 ELSIF p_field_name_table(i) = 'FIND_TASKS.SHIP_TO_COUNTRY'
783 THEN
784 l_ship_to_country := p_field_value_table(i);
785 ELSIF p_field_name_table(i) = 'FIND_TASKS.SHIP_TO_POSTAL_CODE'
786 THEN
787 l_ship_to_postal_code := p_field_value_table(i);
788 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_LINES_IN_SALES_ORDER'
789 THEN
790 l_from_lines_in_sales_order := p_field_value_table(i);
791 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_LINES_IN_SALES_ORDER'
792 THEN
793 l_to_lines_in_sales_order := p_field_value_table(i);
794 ELSIF p_field_name_table(i) = 'FIND_TASKS.MANUFACTURING_TYPE'
795 THEN
796 l_manufacturing_type := p_field_value_table(i);
797 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_JOB'
798 THEN
799 l_from_job := p_field_value_table(i);
800 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_JOB'
801 THEN
802 l_to_job := p_field_value_table(i);
803 ELSIF p_field_name_table(i) = 'FIND_TASKS.ASSEMBLY_ID'
804 THEN
805 l_assembly_id := p_field_value_table(i);
806 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_START_DATE'
807 THEN
808 l_from_start_date := FND_DATE.CHARDT_TO_DATE( p_field_value_table(i), 1);
809 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_START_DATE'
810 THEN
811 l_to_start_date := FND_DATE.CHARDT_TO_DATE( p_field_value_table(i), 1);
812 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_LINE'
813 THEN
814 l_from_line := p_field_value_table(i);
815 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_LINE'
816 THEN
817 l_to_line := p_field_value_table(i);
818 ELSIF p_field_name_table(i) = 'FIND_TASKS.DEPARTMENT_ID'
819 THEN
820 l_department_id := p_field_value_table(i);
821 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_REPLENISHMENT_MO'
822 THEN
823 l_from_replenishment_mo := p_field_value_table(i);
824 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_REPLENISHMENT_MO'
825 THEN
826 l_to_replenishment_mo := p_field_value_table(i);
827 ELSIF p_field_name_table(i) = 'FIND_TASKS.FROM_TRANSFER_ISSUE_MO'
828 THEN
829 l_from_transfer_issue_mo := p_field_value_table(i);
830 ELSIF p_field_name_table(i) = 'FIND_TASKS.TO_TRANSFER_ISSUE_MO'
831 THEN
832 l_to_transfer_issue_mo := p_field_value_table(i);
833 ELSIF p_field_name_table(i) = 'FIND_TASKS.CYCLE_COUNT_NAME'
834 THEN
835 l_cycle_count_name := p_field_value_table(i);
836 ELSIF p_field_name_table(i) = 'FIND_TASKS.OP_PLAN_ACTIVITY_ID'
837 THEN
838 l_op_plan_activity_id := p_field_value_table(i);
839 ELSIF p_field_name_table(i) = 'FIND_TASKS.OP_PLAN_TYPE_ID'
840 THEN
841 l_op_plan_type_id := p_field_value_table(i);
842 ELSIF p_field_name_table(i) = 'FIND_TASKS.OP_PLAN_ID'
843 THEN
844 l_op_plan_id := p_field_value_table(i);
845 ELSIF p_field_name_table(i) = 'FIND_TASKS.WAVE_HEADER_ID'
846 THEN
847 l_wave_header_id := p_field_value_table(i);
848
849 --code cahnges:13014493
850 ELSIF p_field_name_table(i) = 'FIND_TASKS.TIME_TILL_SHIPMENT'
851 THEN
852 l_time_till_shipment := p_field_value_table(i);
853
854 ELSIF p_field_name_table(i) = 'FIND_TASKS.TIME_TILL_SHIPMENT_UOM_CODE'
855 THEN
856 l_time_till_shipment_uom_code := p_field_value_table(i);
857
858 END IF;
859
860 END LOOP;
861 END IF;
862
863 i := 1;
864 l_organization_id := p_organization_id_table( i );
865
866 IF p_query_type_table(i) = 'TEMP_TASK_PLANNING'
867 THEN
868 l_temp_query := TRUE;
869 ELSE
870 l_temp_query := FALSE;
871 END IF;
872
873 x_return_status := FND_API.G_RET_STS_SUCCESS;
874
875 DEBUG( 'Exiting SET_QUERY_TASKS_PARAMETERS');
876
877 EXCEPTION
878 WHEN OTHERS
879 THEN
880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
881 x_return_message:= 'Unexpected error has occured in WMS_TASK_ACTION_PVT.SET_QUERY_TASKS_PARAMETERS. '
882 || 'Oracle error message is ' || SQLERRM;
883 DEBUG( 'Unexpected error has occured. Oracle error message is '
884 || SQLERRM, 'WMS_TASK_ACTION_PVT.SET_QUERY_TASKS_PARAMETERS - other error');
885
886 END SET_QUERY_TASKS_PARAMETERS;
887
888 PROCEDURE SET_ACTION_TASKS_PARAMETERS
889 (
890 p_field_name_table IN wms_task_action_pvt.field_name_table_type,
891 p_field_value_table IN wms_task_action_pvt.field_value_table_type,
892 p_query_type_table IN wms_task_action_pvt.query_type_table_type,
893 x_return_status OUT NOCOPY VARCHAR2,
894 x_return_message OUT NOCOPY VARCHAR2
895 )
896 IS
897 i number;
898 BEGIN
899 DEBUG( 'Inside SET_ACTION_TASKS_PARAMETERS');
900
901 IF p_field_name_table.count <> 0
902 THEN
903 FOR i IN p_field_name_table.first .. p_field_name_table.last
904 LOOP
905 IF p_field_name_table(i) = 'MANAGE_TASKS.ACTION_TYPE'
906 THEN
907 l_action_type := p_field_value_table(i);
908 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.STATUS'
909 THEN
910 l_status := p_field_value_table(i);
911 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.STATUS_CODE'
912 THEN
913 l_status_code := p_field_value_table(i);
914 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.PRIORITY_TYPE'
915 THEN
916 l_priority_type := p_field_value_table(i);
917 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.PRIORITY'
918 THEN
919 l_priority := p_field_value_table(i);
920 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.CLEAR_PRIORITY'
921 THEN
922 l_clear_priority := p_field_value_table(i);
923 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.ASSIGN_TYPE'
924 THEN
925 l_assign_type := p_field_value_table(i);
926 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.EMPLOYEE'
927 THEN
928 l_employee := p_field_value_table(i);
929 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.EMPLOYEE_ID'
930 THEN
931 l_employee_id := p_field_value_table(i);
932 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.USER_TASK_TYPE'
933 THEN
934 l_user_task_type := p_field_value_table(i);
935 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.USER_TASK_TYPE_ID'
936 THEN
937 l_user_task_type_id := p_field_value_table(i);
938 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.EFFECTIVE_START_DATE'
939 THEN
940 -- l_effective_start_date := FND_DATE.CHARDT_TO_DATE( p_field_value_table(i), 1);
941
942 /* For bug 14496291 made a call to FND_DATE.CANONICAL_TO_DATE as suggested by International Calendar support team */
943 l_effective_start_date := FND_DATE.CANONICAL_TO_DATE(p_field_value_table(i));
944
945 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.EFFECTIVE_END_DATE'
946 THEN
947 /* For bug 14496291 made a call to FND_CONC_DATE.STRING_TO_DATE as suggested by ATG team */
948 -- l_effective_end_date := FND_DATE.CHARDT_TO_DATE( p_field_value_table(i), 1);
949
950 l_effective_end_date := FND_DATE.CANONICAL_TO_DATE(p_field_value_table(i));
951
952 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.PERSON_RESOURCE_ID'
953 THEN
954 l_person_resource_id := p_field_value_table(i);
955 ELSIF p_field_name_table(i) = 'MANAGE_TASKS.PERSON_RESOURCE_CODE'
956 THEN
957 l_person_resource_code := p_field_value_table(i);
958 END IF;
959
960 IF ( ( p_field_name_table(i) = 'MANAGE_TASKS.OVERRIDE_EMP_CHECK' )
961 and ( p_field_value_table(i) = 'Y' ) )
962 THEN
963 l_override_emp_check := TRUE;
964 END IF;
965
966 END LOOP;
967 END IF;
968 i := 1;
969 IF p_query_type_table(i) = 'TEMP_TASK_ACTION'
970 THEN
971 l_temp_action := TRUE;
972 ELSE
973 l_temp_action := FALSE;
974 END IF;
975
976 x_return_status := FND_API.G_RET_STS_SUCCESS;
977
978 DEBUG( 'Exiting SET_ACTION_TASKS_PARAMETERS');
979
980 EXCEPTION
981 WHEN OTHERS
982 THEN
983 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
984 x_return_message:= 'Unexpected error has occured in WMS_TASK_ACTION_PVT.SET_ACTION_TASKS_PARAMETERS. '
985 || 'Oracle error message is ' || SQLERRM;
986 DEBUG( 'Unexpected error has occured. Oracle error message is '
987 ||SQLERRM, 'WMS_TASK_ACTION_PVT.SET_ACTION_TASKS_PARAMETERS - other error');
988 END SET_ACTION_TASKS_PARAMETERS;
989
990 PROCEDURE DELETE_TEMP_QUERY
991 ( p_query_name IN VARCHAR2,
992 x_return_status OUT NOCOPY VARCHAR2,
993 x_return_message OUT NOCOPY VARCHAR2
994 )
995 IS
996 BEGIN
997 DEBUG( 'Inside DELETE_TEMP_QUERY');
998
999 BEGIN
1000 delete
1001 from wms_saved_queries
1002 where query_name = p_query_name
1003 and query_type = 'TEMP_TASK_PLANNING';
1004
1005 DEBUG( 'Temporary query records cleaned. Records deleted = ' || sql%rowcount );
1006 EXCEPTION
1007 WHEN OTHERS
1008 THEN
1009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1010 x_return_message
1011 := 'Unexpected error has occured in '
1012 || 'WMS_TASK_ACTION_PVT.DELETE_TEMP_QUERY. '
1013 || 'Oracle error message is ' || SQLERRM;
1014 DEBUG
1015 ( 'Unexpected error has occured in '
1016 || 'WMS_TASK_ACTION_PVT.DELETE_TEMP_QUERY. '
1017 || 'Oracle error message is ' || SQLERRM
1018 , 'WMS_TASK_ACTION_PVT.DELETE_TEMP_QUERY - other error'
1019 );
1020 END;
1021
1022 x_return_status := FND_API.G_RET_STS_SUCCESS;
1023
1024 DEBUG( 'Exiting DELETE_TEMP_QUERY');
1025 EXCEPTION
1026 WHEN OTHERS
1027 THEN
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1029 x_return_message:= 'Unexpected error has occured in WMS_TASK_ACTION_PVT.DELETE_TEMP_QUERY '
1030 || 'Oracle error message is ' || SQLERRM;
1031 DEBUG( 'Unexpected error has occured. Oracle error message is '
1032 ||SQLERRM, 'WMS_TASK_ACTION_PVT.DELETE_TEMP_QUERY - other error');
1033 END DELETE_TEMP_QUERY;
1034
1035 PROCEDURE DELETE_TEMP_ACTION
1036 ( p_action_name IN VARCHAR2,
1037 x_return_status OUT NOCOPY VARCHAR2,
1038 x_return_message OUT NOCOPY VARCHAR2
1039 )
1040 IS
1041 BEGIN
1042 DEBUG( 'Inside DELETE_TEMP_ACTION');
1043
1044 BEGIN
1045 delete
1046 from wms_saved_queries
1047 where query_name = p_action_name
1048 and query_type = 'TEMP_TASK_ACTION';
1049
1050 DEBUG( 'Temporary action records cleaned. Records deleted = ' || sql%rowcount );
1051
1052 EXCEPTION
1053 WHEN OTHERS
1054 THEN
1055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1056 x_return_message
1057 := 'Unexpected error has occured in '
1058 || 'WMS_TASK_ACTION_PVT.DELETE_TEMP_ACTION. '
1059 || 'Oracle error message is ' || SQLERRM;
1060 DEBUG
1061 ( 'Unexpected error has occured in '
1062 || 'WMS_TASK_ACTION_PVT.DELETE_TEMP_ACTION. '
1063 , 'WMS_TASK_ACTION_PVT.DELETE_TEMP_ACTION - other error'
1064 );
1065 END;
1066
1067 x_return_status := FND_API.G_RET_STS_SUCCESS;
1068
1069 DEBUG( 'Exiting DELETE_TEMP_ACTION');
1070 EXCEPTION
1071 WHEN OTHERS
1072 THEN
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1074 x_return_message:= 'Unexpected error has occured in WMS_TASK_ACTION_PVT.DELETE_TEMP_ACTION '
1075 || 'Oracle error message is ' || SQLERRM;
1076 DEBUG( 'Unexpected error has occured. Oracle error message is '
1077 ||SQLERRM, 'WMS_TASK_ACTION_PVT.DELETE_TEMP_ACTION - other error');
1078 END DELETE_TEMP_ACTION;
1079
1080 PROCEDURE TASK_ACTION_CONC_PROG
1081 (
1082 errbuf OUT NOCOPY VARCHAR2,
1083 retcode OUT NOCOPY VARCHAR2,
1084 p_query_name IN VARCHAR2,
1085 p_action IN VARCHAR2,
1086 p_action_name IN VARCHAR2
1087 )
1088 IS
1089 l_request_id NUMBER;
1090 l_return_status VARCHAR2(1);
1091 l_return_message VARCHAR2(4000);
1092 l_msg_data VARCHAR2( 120 );
1093 l_msg_count NUMBER;
1094 l_save_count NUMBER;
1095 l_return_msg VARCHAR2( 120) ;
1096 l_rowcount NUMBER;
1097 ret BOOLEAN;
1098 BEGIN
1099
1100 DEBUG( 'Calling TASK_ACTION');
1101 DEBUG( 'Input parameters passed are');
1102 DEBUG( 'p_query_name => ' || p_query_name);
1103 DEBUG( 'p_action => ' || p_action );
1104 DEBUG( 'p_action_name => ' || p_action_name );
1105
1106 TASK_ACTION
1107 ( p_query_name => p_query_name,
1108 p_action => p_action,
1109 p_action_name => p_action_name ,
1110 p_online => 'N' ,
1111 x_rowcount => l_rowcount ,
1112 x_return_status => l_return_status,
1113 x_return_message => l_return_message
1114 );
1115
1116 DEBUG( 'TASK_ACTION x_rowcount => ' || l_rowcount );
1117 DEBUG( 'TASK_ACTION x_return_status => ' || l_return_status);
1118 DEBUG( 'TASK_ACTION x_return_message => ' || l_return_message);
1119
1120 IF l_temp_query
1121 THEN
1122 DEBUG( 'l_temp_query = TRUE');
1123 ELSE
1124 DEBUG( 'l_temp_query = FALSE');
1125 END IF;
1126
1127 IF l_temp_action
1128 THEN
1129 DEBUG( 'l_temp_action = TRUE');
1130 ELSE
1131 DEBUG( 'l_temP_action = FALSE');
1132 END IF;
1133
1134 IF l_return_status = fnd_api.g_ret_sts_error OR
1135 l_return_status = fnd_api.g_ret_sts_unexp_error
1136 THEN
1137 retcode := '2';
1138 errbuf := 'Error: ' || l_return_message;
1139
1140 IF l_temp_query
1141 THEN
1142 DEBUG( 'Calling DELETE_TEMP_QUERY');
1143
1144 DELETE_TEMP_QUERY
1145 (
1146 p_query_name => p_query_name,
1147 x_return_status => l_return_status,
1148 x_return_message => l_return_message
1149 );
1150
1151 DEBUG( 'DELETE_TEMP_QUERY return status = ' || l_return_status );
1152 DEBUG( 'DELETE_TEMP_QUERY return message = ' || l_return_message );
1153
1154 IF l_return_status = fnd_api.g_ret_sts_error
1155 OR l_return_status = fnd_api.g_ret_sts_unexp_error
1156 THEN
1157 errbuf := errbuf || l_return_message;
1158 ELSE
1159 COMMIT WORK;
1160 END IF;
1161 END IF;
1162
1163 IF l_temp_action
1164 THEN
1165 DEBUG( 'Calling DELETE_TEMP_ACTION');
1166
1167 DELETE_TEMP_ACTION
1168 (
1169 p_action_name => p_action_name,
1170 x_return_status => l_return_status,
1171 x_return_message => l_return_message
1172 );
1173
1174 DEBUG( 'DELETE_TEMP_ACTION return status = ' || l_return_status );
1175 DEBUG( 'DELETE_TEMP_ACTION return message = ' || l_return_message );
1176
1177 IF l_return_status = fnd_api.g_ret_sts_error
1178 OR l_return_status = fnd_api.g_ret_sts_unexp_error
1179 THEN
1180 errbuf := errbuf || l_return_message;
1181 ELSE
1182 COMMIT WORK;
1183 END IF;
1184 END IF;
1185
1186 return;
1187 END IF;
1188
1189 IF l_rowcount <> 0
1190 THEN
1191 DEBUG( 'Calling wms_waveplan_tasks_pvt.save_tasks ');
1192 DEBUG( 'Input Parameters passed');
1193 DEBUG( 'p_commit => TRUE');
1194 DEBUG( 'p_user_id => ' || fnd_global.user_id);
1195 DEBUG( 'p_login_id => ' || fnd_global.login_id);
1196
1197 wms_waveplan_tasks_pvt.save_tasks
1198 (
1199 p_task_action => p_action,
1200 p_commit => TRUE,
1201 p_user_id => fnd_global.user_id,
1202 p_login_id => fnd_global.login_id,
1203 x_save_count => l_save_count,
1204 x_return_status => l_return_status,
1205 x_msg_data => l_msg_data,
1206 x_msg_count => l_msg_count
1207 );
1208
1209 DEBUG( 'WMS_WAVEPLAN_TASKS_PVT.SAVE_TASKS return Status = ' || l_return_status );
1210 DEBUG( 'WMS_WAVEPLAN_TASKS_PVT.SAVE_TASKS l_save_count = ' || l_save_count );
1211 DEBUG( 'WMS_WAVEPLAN_TASKS_PVT.SAVE_TASKS l_msg_data = ' || l_msg_data );
1212 DEBUG( 'WMS_WAVEPLAN_TASKS_PVT.SAVE_TASKS l_msg_count = ' || l_msg_count );
1213
1214
1215 IF l_return_status = fnd_api.g_ret_sts_error
1216 OR l_return_status = fnd_api.g_ret_sts_unexp_error
1217 THEN
1218 retcode := '2';
1219 errbuf := 'Error: ' || l_return_message;
1220
1221 IF l_temp_query
1222 THEN
1223 DEBUG( 'Calling DELETE_TEMP_QUERY');
1224
1225 DELETE_TEMP_QUERY
1226 (
1227 p_query_name => p_query_name,
1228 x_return_status => l_return_status,
1229 x_return_message => l_return_message
1230 );
1231 DEBUG( 'DELETE_TEMP_QUERY return status = ' || l_return_status );
1232 DEBUG( 'DELETE_TEMP_QUERY return message = ' || l_return_message );
1233
1234 IF l_return_status = fnd_api.g_ret_sts_error
1235 OR l_return_status = fnd_api.g_ret_sts_unexp_error
1236 THEN
1237 errbuf := errbuf || l_return_message;
1238 ELSE
1239 COMMIT WORK;
1240 END IF;
1241 END IF;
1242
1243 IF l_temp_action
1244 THEN
1245 DEBUG( 'Calling DELETE_TEMP_ACTION');
1246
1247 DELETE_TEMP_ACTION
1248 (
1249 p_action_name => p_action_name,
1250 x_return_status => l_return_status,
1251 x_return_message => l_return_message
1252 );
1253
1254 DEBUG( 'DELETE_TEMP_ACTION return status = ' || l_return_status );
1255 DEBUG( 'DELETE_TEMP_ACTION return message = ' || l_return_message );
1256
1257 IF l_return_status = fnd_api.g_ret_sts_error
1258 OR l_return_status = fnd_api.g_ret_sts_unexp_error
1259 THEN
1260 errbuf := errbuf || l_return_message;
1261 ELSE
1262 COMMIT WORK;
1263 END IF;
1264 END IF;
1265
1266 return;
1267 END IF;
1268 END IF;
1269
1270 IF l_temp_query
1271 THEN
1272 DEBUG( 'Calling DELETE_TEMP_QUERY');
1273 DELETE_TEMP_QUERY
1274 (
1275 p_query_name => p_query_name,
1276 x_return_status => l_return_status,
1277 x_return_message => l_return_message
1278 );
1279 DEBUG( 'DELETE_TEMP_QUERY return status = ' || l_return_status );
1280 DEBUG( 'DELETE_TEMP_QUERY return message = ' || l_return_message );
1281 IF l_return_status = fnd_api.g_ret_sts_error
1282 OR l_return_status = fnd_api.g_ret_sts_unexp_error
1283 THEN
1284 retcode := '2';
1285 errbuf := errbuf || l_return_message;
1286 ELSE
1287 COMMIT WORK;
1288 END IF;
1289 END IF;
1290
1291 IF l_temp_action
1292 THEN
1293 DEBUG( 'Calling DELETE_TEMP_ACTION');
1294 DELETE_TEMP_ACTION
1295 (
1296 p_action_name => p_action_name,
1297 x_return_status => l_return_status,
1298 x_return_message => l_return_message
1299 );
1300 DEBUG( 'DELETE_TEMP_ACTION return status = ' || l_return_status );
1301 DEBUG( 'DELETE_TEMP_ACTION return message = ' || l_return_message );
1302 IF l_return_status = fnd_api.g_ret_sts_error
1303 OR l_return_status = fnd_api.g_ret_sts_unexp_error
1304 THEN
1305 retcode := '2';
1306 errbuf := errbuf || l_return_message;
1307 ELSE
1308 COMMIT WORK;
1309 END IF;
1310 END IF;
1311
1312 retcode := '0';
1313 ret := fnd_concurrent.set_completion_status('NORMAL', errbuf);
1314 DEBUG( 'Exiting TASK_ACTION_CONC_PROG with status = '||retcode);
1315
1316 EXCEPTION
1317 WHEN OTHERS
1318 THEN
1319 retcode := '2';
1320 errbuf := 'Unexpected error has occured in WMS_TASK_ACTION_PVT.TASK_ACTION_CONC_PROG. '
1321 || 'Oracle error message is ' || SQLERRM;
1322 ret := fnd_concurrent.set_completion_status('ERROR', errbuf);
1323 DEBUG( 'Unexpected error has occured. Oracle error message is '
1324 ||SQLERRM, 'WMS_TASK_ACTION_PVT.TASK_ACTION_CONC_PROG - other error');
1325 END TASK_ACTION_CONC_PROG;
1326
1327 PROCEDURE TASK_ACTION
1328 (
1329 p_query_name IN VARCHAR2,
1330 p_action_name IN VARCHAR2,
1331 p_action IN VARCHAR2,
1332 p_online IN VARCHAR2,
1333 x_rowcount OUT NOCOPY NUMBER,
1334 x_return_status OUT NOCOPY VARCHAR2,
1335 x_return_message OUT NOCOPY VARCHAR2
1336 )
1337 IS
1338 CURSOR c_saved_queries ( p_query_name VARCHAR2 ) IS
1339 select field_name,
1340 --ltrim(rtrim(field_value)) field_value,
1341 field_value,
1342 organization_id,
1343 query_type
1344 from wms_saved_queries
1345 where query_name = p_query_name
1346 and (query_type = 'TASK_PLANNING' or query_type = 'TEMP_TASK_PLANNING')
1347 FOR UPDATE NOWAIT;
1348
1349 CURSOR c_saved_actions ( p_action_name varchar2 ) IS
1350 select field_name,
1351 field_value,
1352 query_type
1353 from wms_saved_queries
1354 where query_name = p_action_name
1355 and (query_type = 'TASK_ACTION' or query_type = 'TEMP_TASK_ACTION')
1356 FOR UPDATE NOWAIT;
1357
1358 CURSOR c_query_type ( p_action_name varchar2 ) IS
1359 select distinct query_type
1360 from wms_saved_queries
1361 where query_name = p_action_name;
1362
1363 rec_saved_queries c_saved_queries%rowtype;
1364 rec_saved_actions c_saved_actions%rowtype;
1365
1366 l_field_name_table wms_task_action_pvt.field_name_table_type;
1367 l_field_value_table wms_task_action_pvt.field_value_table_type;
1368 l_organization_id_table wms_task_action_pvt.organization_id_table_type;
1369 l_query_type_table wms_task_action_pvt.query_type_table_type;
1370
1371 l_return_status VARCHAR2( 1 );
1372 l_msg_data VARCHAR2( 120 );
1373 l_msg_count NUMBER;
1374 l_save_count NUMBER;
1375 l_return_msg VARCHAR2( 120) ;
1376 l_record_count NUMBER;
1377
1378 l_query_name varchar2(100);
1379 l_return_message VARCHAR2(4000);
1380 BEGIN
1381
1382 l_record_count := 0;
1383 l_is_unreleased := FALSE;
1384 l_is_pending := FALSE;
1385 l_is_queued := FALSE;
1386 l_is_dispatched := FALSE;
1387 l_is_active := FALSE;
1388 l_is_loaded := FALSE;
1389 l_is_completed := FALSE;
1390 l_include_inbound := FALSE;
1391 l_include_outbound := FALSE;
1392 l_include_crossdock := FALSE;
1393 l_include_manufacturing := FALSE;
1394 l_include_warehousing := FALSE;
1395 l_include_sales_orders := FALSE;
1396 l_include_internal_orders := FALSE;
1397 l_include_replenishment := FALSE;
1398 l_include_mo_transfer := FALSE;
1399 l_include_mo_issue := FALSE;
1400 l_include_lpn_putaway := FALSE;
1401 l_include_staging_move := FALSE;
1402 l_include_cycle_count := FALSE;
1403 l_is_pending_plan := FALSE;
1404 l_is_inprogress_plan := FALSE;
1405 l_is_completed_plan := FALSE;
1406 l_is_cancelled_plan := FALSE;
1407 l_is_aborted_plan := FALSE;
1408 l_query_independent_tasks := FALSE;
1409 l_query_planned_tasks := FALSE;
1410 l_override_emp_check := FALSE;
1411
1412 DEBUG ( 'Inside TASK_ACTION procedure' );
1413 DEBUG ( 'p_query_name => ' || p_query_name );
1414 DEBUG ( 'p_action_name => ' || p_action_name );
1415 DEBUG ( 'p_action => ' || p_action );
1416
1417 DEBUG( 'Opening c_saved_queries');
1418
1419 OPEN c_saved_queries( p_query_name );
1420
1421 FETCH c_saved_queries
1422 BULK COLLECT INTO
1423 l_field_name_table,
1424 l_field_value_table,
1425 l_organization_id_table,
1426 l_query_type_table;
1427
1428 -- If no records founds for the given query name
1429 -- then close the cursor and return informing invalid query name.
1430
1431 DEBUG( 'c_saved_queries%ROWCOUNT = ' || c_saved_queries%ROWCOUNT );
1432 x_rowcount := c_saved_queries%ROWCOUNT;
1433
1434 IF c_saved_queries%ROWCOUNT = 0
1435 THEN
1436 CLOSE c_saved_queries;
1437 DEBUG ('No data found for query name = ' || p_query_name);
1438 x_rowcount := 0;
1439 x_return_status := fnd_api.g_ret_sts_success;
1440 x_return_message:= 'No data found for query name = ' || p_query_name ;
1441
1442 FOR rec_query_type IN c_query_type( p_action_name )
1443 LOOP
1444 IF rec_query_type.query_type = 'TEMP_TASK_ACTION'
1445 THEN
1446 l_temp_action := TRUE;
1447 ELSE
1448 l_temp_action := FALSE;
1449 END IF;
1450 END LOOP;
1451
1452 RETURN;
1453 END IF;
1454
1455 CLOSE c_saved_queries;
1456
1457 DEBUG ( 'Bulk collect from c_saved_queries successful and closed c_saved_queries cursor' );
1458
1459 DEBUG ( 'Calling SET_QUERY_TASKS_PARAMETERS' );
1460
1461 SET_QUERY_TASKS_PARAMETERS
1462 ( p_field_name_table => l_field_name_table,
1463 p_field_value_table => l_field_value_table ,
1464 p_organization_id_table => l_organization_id_table,
1465 p_query_type_table => l_query_type_table,
1466 x_return_status => l_return_status,
1467 x_return_message => l_return_message
1468 );
1469
1470 DEBUG( 'SET_QUERY_TASKS_PARAMETERS return status = ' || l_return_status );
1471 DEBUG( 'SET_QUERY_TASKS_PARAMETERS return message = ' || l_return_message );
1472
1473 IF l_return_status = fnd_api.g_ret_sts_error OR
1474 l_return_status = fnd_api.g_ret_sts_unexp_error
1475 THEN
1476 DEBUG (' Error in SET_QUERY_TASKS_PARAMETERS ' );
1477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1478 x_return_message:= 'SET_QUERY_TASKS_PARAMETERS returned with Error status'
1479 || 'Error message = ' || l_return_message ;
1480 return;
1481 END IF;
1482
1483 DEBUG ( 'Calling WMS_WAVEPLAN_TASKS_PVT.QUERY_TASKS' );
1484 DEBUG ( 'Input parameters passed');
1485 DEBUG( 'p_add => NULL' );
1486 DEBUG( 'p_organization_id => ' || l_organization_id );
1487 DEBUG( 'p_subinventory_code => ' || l_subinventory );
1488 DEBUG( 'p_locator_id => ' || l_locator_id );
1489 DEBUG( 'p_to_subinventory_code => ' || l_to_subinventory );
1490 DEBUG( 'p_to_locator_id => ' || l_to_locator_id );
1491 DEBUG( 'p_inventory_item_id => ' || l_inventory_item_id );
1492 DEBUG( 'p_category_set_id => ' || l_category_set_id );
1493 DEBUG( 'p_item_category_id => ' || l_item_category_id );
1494 DEBUG( 'p_person_id => ' || l_employee_id );
1495 DEBUG( 'p_person_resource_id => ' || l_person_resource_id );
1496 DEBUG( 'p_equipment_type_id => ' || l_equipment_type_id );
1497 DEBUG( 'p_machine_instance => ' || l_equipment );
1498 DEBUG( 'p_user_task_type_id => ' || l_user_task_type_id );
1499 DEBUG( 'p_from_task_quantity => ' || l_from_task_quantity );
1500 DEBUG( 'p_to_task_quantity => ' || l_to_task_quantity );
1501 DEBUG( 'p_from_task_priority => ' || l_from_task_priority );
1502 DEBUG( 'p_to_task_priority => ' || l_to_task_priority );
1503 DEBUG( 'p_from_creation_date => ' || FND_DATE.DATE_TO_CHARDATE(l_from_creation_date), 1);
1504 DEBUG( 'p_to_creation_date => ' || FND_DATE.DATE_TO_CHARDATE(l_to_creation_date), 1);
1505
1506 IF l_is_unreleased
1507 THEN
1508 DEBUG( 'p_is_unreleased => TRUE' );
1509 ELSE
1510 DEBUG( 'p_is_unreleased => FALSE' );
1511 END IF;
1512
1513 IF l_is_pending
1514 THEN
1515 DEBUG( 'p_is_pending => TRUE');
1516 ELSE
1517 DEBUG( 'p_is_pending => FALSE');
1518 END IF;
1519
1520 IF l_is_queued
1521 THEN
1522 DEBUG( 'p_is_queued => TRUE' );
1523 ELSE
1524 DEBUG( 'p_is_queued => FALSE' );
1525 END IF;
1526
1527 IF l_is_dispatched
1528 THEN
1529 DEBUG( 'p_is_dispatched => TRUE' );
1530 ELSE
1531 DEBUG( 'p_is_dispatched => FALSE' );
1532 END IF;
1533
1534 IF l_is_active
1535 THEN
1536 DEBUG( 'p_is_active => TRUE');
1537 ELSE
1538 DEBUG( 'p_is_active => FALSE');
1539 END IF;
1540
1541 IF l_is_loaded
1542 THEN
1543 DEBUG( 'p_is_loaded => TRUE' );
1544 ELSE
1545 DEBUG( 'p_is_loaded => FALSE' );
1546 END IF;
1547
1548 IF l_is_completed
1549 THEN
1550 DEBUG( 'p_is_completed => TRUE' );
1551 ELSE
1552 DEBUG( 'p_is_completed => FALSE' );
1553 END IF;
1554
1555 IF l_include_inbound
1556 THEN
1557 DEBUG( 'p_include_inbound => TRUE' );
1558 ELSE
1559 DEBUG( 'p_include_inbound => FALSE' );
1560 END IF;
1561
1562 IF l_include_outbound
1563 THEN
1564 DEBUG( 'p_include_outbound => TRUE' );
1565 ELSE
1566 DEBUG( 'p_include_outbound => FALSE' );
1567 END IF;
1568
1569 IF l_include_crossdock
1570 THEN
1571 DEBUG( 'p_include_crossdock => TRUE' );
1572 ELSE
1573 DEBUG( 'p_include_crossdock => FALSE' );
1574 END IF;
1575
1576
1577 IF l_include_manufacturing
1578 THEN
1579 DEBUG( 'p_include_manufacturing => TRUE' );
1580 ELSE
1581 DEBUG( 'p_include_manufacturing => FALSE' );
1582 END IF;
1583
1584 IF l_include_warehousing
1585 THEN
1586 DEBUG( 'p_include_warehousing => TRUE' );
1587 ELSE
1588 DEBUG( 'p_include_warehousing => FALSE' );
1589 END IF;
1590
1591 DEBUG( 'p_to_purchase_order => ' || l_to_purchase_order );
1592 DEBUG( 'p_to_po_header_id => ' || l_to_po_header_id );
1593 DEBUG( 'p_from_rma => ' || l_from_rma );
1594 DEBUG( 'p_from_rma_header_id => ' || l_from_rma_header_id );
1595 DEBUG( 'p_to_rma => ' || l_to_rma );
1596 DEBUG( 'p_to_rma_header_id => ' || l_to_rma_header_id );
1597 DEBUG( 'p_from_requisition => ' || l_from_requisition );
1598 DEBUG( 'p_from_requisition_header_id => ' || l_from_requisition_header_id );
1599 DEBUG( 'p_to_requisition => ' || l_to_requisition );
1600 DEBUG( 'p_to_requisition_header_id => ' || l_to_requisition_header_id );
1601 DEBUG( 'p_from_shipment_number => ' || l_from_shipment );
1602 DEBUG( 'p_to_shipment_number => ' || l_to_shipment );
1603 DEBUG( 'p_from_sales_order_id => ' || l_from_sales_order_id );
1604 DEBUG( 'p_to_sales_order_id => ' || l_to_sales_order_id );
1605 DEBUG( 'p_from_pick_slip_number => ' || l_from_pick_slip );
1606 DEBUG( 'p_to_pick_slip_number => ' || l_to_pick_slip );
1607 DEBUG( 'p_customer_id => ' || l_customer_id );
1608 DEBUG( 'p_customer_category => ' || l_customer_category );
1609 DEBUG( 'p_delivery_id => ' || l_delivery_id );
1610 DEBUG( 'p_carrier_id => ' || l_carrier_id );
1611 DEBUG( 'p_ship_method => ' || l_ship_method_code );
1612 DEBUG( 'p_trip_id => ' || l_trip_id );
1613 DEBUG( 'p_shipment_priority => ' || l_shipment_priority );
1614 DEBUG( 'p_from_shipment_date => ' || l_from_shipment_date );
1615 DEBUG( 'p_to_shipment_date => ' || l_to_shipment_date );
1616 DEBUG( 'p_ship_to_state => ' || l_ship_to_state );
1617 DEBUG( 'p_ship_to_country => ' || l_ship_to_country );
1618 DEBUG( 'p_ship_to_postal_code => ' || l_ship_to_postal_code );
1619 DEBUG( 'p_from_number_of_order_lines => ' || l_from_lines_in_sales_order );
1620 DEBUG( 'p_to_number_of_order_lines => ' || l_to_lines_in_sales_order );
1621 DEBUG( 'p_manufacturing_type => ' || l_manufacturing_type );
1622 DEBUG( 'p_from_job => ' || l_from_job );
1623 DEBUG( 'p_to_job => ' || l_to_job );
1624 DEBUG( 'p_assembly_id => ' || l_assembly_id );
1625 DEBUG( 'p_from_start_date => ' || l_from_start_date );
1626 DEBUG( 'p_to_start_date => ' || l_to_start_date );
1627 DEBUG( 'p_from_line => ' || l_from_line );
1628 DEBUG( 'p_to_line => ' || l_to_line );
1629 DEBUG( 'p_department_id => ' || l_department_id );
1630
1631 IF l_include_sales_orders
1632 THEN
1633 DEBUG( 'p_include_sales_orders => TRUE' );
1634 ELSE
1635 DEBUG( 'p_include_sales_orders => FALSE' );
1636 END IF;
1637
1638 IF l_include_internal_orders
1639 THEN
1640 DEBUG( 'p_include_internal_orders => TRUE' );
1641 ELSE
1642 DEBUG( 'p_include_internal_orders => FALSE' );
1643 END IF;
1644
1645 IF l_include_replenishment
1646 THEN
1647 DEBUG( 'p_include_replenishment => TRUE' );
1648 ELSE
1649 DEBUG( 'p_include_replenishment => FALSE' );
1650 END IF;
1651
1652 DEBUG( 'p_from_replenishment_mo => ' || l_from_replenishment_mo );
1653 DEBUG( 'p_to_replenishment_mo => ' || l_to_replenishment_mo );
1654
1655 IF l_include_mo_transfer
1656 THEN
1657 DEBUG( 'p_include_mo_transfer => TRUE' );
1658 ELSE
1659 DEBUG( 'p_include_mo_transfer => FALSE' );
1660 END IF;
1661
1662 IF l_include_mo_issue
1663 THEN
1664 DEBUG( 'p_include_mo_issue => TRUE' );
1665 ELSE
1666 DEBUG( 'p_include_mo_issue => FALSE' );
1667 END IF;
1668
1669 DEBUG( 'p_from_transfer_issue_mo => ' || l_from_transfer_issue_mo );
1670 DEBUG( 'p_to_transfer_issue_mo => ' || l_to_transfer_issue_mo );
1671
1672 IF l_include_lpn_putaway
1673 THEN
1674 DEBUG( 'p_include_lpn_putaway => TRUE' );
1675 ELSE
1676 DEBUG( 'p_include_lpn_putaway => FALSE' );
1677 END IF;
1678
1679 IF l_include_staging_move
1680 THEN
1681 DEBUG( 'p_include_staging_move => TRUE' );
1682 ELSE
1683 DEBUG( 'p_include_staging_move => FALSE' );
1684 END IF;
1685
1686 IF l_include_cycle_count
1687 THEN
1688 DEBUG( 'p_include_cycle_count => TRUE' );
1689 ELSE
1690 DEBUG( 'p_include_cycle_count => FALSE' );
1691 END IF;
1692
1693 DEBUG( 'p_cycle_count_name => ' || l_cycle_count_name );
1694
1695 IF l_query_independent_tasks
1696 THEN
1697 DEBUG( 'p_query_independent_tasks => TRUE' );
1698 ELSE
1699 DEBUG( 'p_query_independent_tasks => FALSE' );
1700 END IF;
1701
1702 IF l_query_planned_tasks
1703 THEN
1704 DEBUG( 'p_query_planned_tasks => TRUE' );
1705 ELSE
1706 DEBUG( 'p_query_planned_tasks => FALSE' );
1707 END IF;
1708
1709 IF l_is_pending_plan
1710 THEN
1711 DEBUG( 'p_is_pending_plan => TRUE' );
1712 ELSE
1713 DEBUG( 'p_is_pending_plan => FALSE' );
1714 END IF;
1715
1716 IF l_is_inprogress_plan
1717 THEN
1718 DEBUG( 'p_is_inprogress_plan => TRUE' );
1719 ELSE
1720 DEBUG( 'p_is_inprogress_plan => FALSE' );
1721 END IF;
1722
1723 IF l_is_completed_plan
1724 THEN
1725 DEBUG( 'p_is_completed_plan => TRUE' );
1726 ELSE
1727 DEBUG( 'p_is_completed_plan => FALSE' );
1728 END IF;
1729
1730 IF l_is_cancelled_plan
1731 THEN
1732 DEBUG( 'p_is_cancelled_plan => TRUE' );
1733 ELSE
1734 DEBUG( 'p_is_cancelled_plan => FALSE' );
1735 END IF;
1736
1737 IF l_is_aborted_plan
1738 THEN
1739 DEBUG( 'p_is_aborted_plan => TRUE' );
1740 ELSE
1741 DEBUG( 'p_is_aborted_plan => FALSE' );
1742 END IF;
1743
1744 DEBUG( 'p_activity_id => ' || l_op_plan_activity_id );
1745 DEBUG( 'p_plan_type_id => ' || l_op_plan_type_id );
1746 DEBUG( 'p_op_plan_id => ' || l_op_plan_id );
1747 DEBUG( 'p_time_till_shipment => ' || l_time_till_shipment );
1748 DEBUG( 'p_time_till_shipment_uom_code => ' || l_time_till_shipment_uom_code );
1749
1750 wms_waveplan_tasks_pvt.query_tasks
1751 ( p_add => NULL ,
1752 p_organization_id => l_organization_id,
1753 p_subinventory_code => l_subinventory,
1754 p_locator_id => l_locator_id,
1755 p_to_subinventory_code => l_to_subinventory,
1756 p_to_locator_id => l_to_locator_id,
1757 p_inventory_item_id => l_inventory_item_id,
1758 p_category_set_id => l_category_set_id,
1759 p_item_category_id => l_item_category_id,
1760 p_person_id => l_employee_id,
1761 p_person_resource_id => l_person_resource_id,
1762 p_equipment_type_id => l_equipment_type_id,
1763 p_machine_instance => l_equipment,
1764 p_user_task_type_id => l_user_task_type_id,
1765 p_from_task_quantity => l_from_task_quantity,
1766 p_to_task_quantity => l_to_task_quantity,
1767 p_from_task_priority => l_from_task_priority,
1768 p_to_task_priority => l_to_task_priority,
1769 p_from_creation_date => l_from_creation_date,
1770 p_to_creation_date => l_to_creation_date,
1771 p_is_unreleased => l_is_unreleased,
1772 p_is_pending => l_is_pending,
1773 p_is_queued => l_is_queued,
1774 p_is_dispatched => l_is_dispatched,
1775 p_is_active => l_is_active,
1776 p_is_loaded => l_is_loaded,
1777 p_is_completed => l_is_completed,
1778 p_include_inbound => l_include_inbound,
1779 p_include_outbound => l_include_outbound,
1780 p_include_crossdock => l_include_crossdock,
1781 p_include_manufacturing => l_include_manufacturing,
1782 p_include_warehousing => l_include_warehousing,
1783 p_from_purchase_order => l_from_purchase_order,
1784 p_from_po_header_id => l_from_po_header_id,
1785 p_to_purchase_order => l_to_purchase_order,
1786 p_to_po_header_id => l_to_po_header_id,
1787 p_from_rma => l_from_rma,
1788 p_from_rma_header_id => l_from_rma_header_id,
1789 p_to_rma => l_to_rma,
1790 p_to_rma_header_id => l_to_rma_header_id,
1791 p_from_requisition => l_from_requisition,
1792 p_from_requisition_header_id => l_from_requisition_header_id,
1793 p_to_requisition => l_to_requisition,
1794 p_to_requisition_header_id => l_to_requisition_header_id,
1795 p_from_shipment_number => l_from_shipment,
1796 p_to_shipment_number => l_to_shipment,
1797 p_from_sales_order_id => l_from_sales_order_id,
1798 p_to_sales_order_id => l_to_sales_order_id,
1799 p_from_pick_slip_number => l_from_pick_slip,
1800 p_to_pick_slip_number => l_to_pick_slip,
1801 p_customer_id => l_customer_id,
1802 p_customer_category => l_customer_category,
1803 p_delivery_id => l_delivery_id,
1804 p_carrier_id => l_carrier_id,
1805 p_ship_method => l_ship_method_code,
1806 p_trip_id => l_trip_id,
1807 p_shipment_priority => l_shipment_priority,
1808 p_from_shipment_date => l_from_shipment_date,
1809 p_to_shipment_date => l_to_shipment_date,
1810 p_ship_to_state => l_ship_to_state,
1811 p_ship_to_country => l_ship_to_country,
1812 p_ship_to_postal_code => l_ship_to_postal_code,
1813 p_from_number_of_order_lines => l_from_lines_in_sales_order,
1814 p_to_number_of_order_lines => l_to_lines_in_sales_order,
1815 p_manufacturing_type => l_manufacturing_type,
1816 p_from_job => l_from_job,
1817 p_to_job => l_to_job,
1818 p_assembly_id => l_assembly_id,
1819 p_from_start_date => l_from_start_date,
1820 p_to_start_date => l_to_start_date,
1821 p_from_line => l_from_line,
1822 p_to_line => l_to_line,
1823 p_department_id => l_department_id,
1824 p_include_sales_orders => l_include_sales_orders,
1825 p_include_internal_orders => l_include_internal_orders,
1826 p_include_replenishment => l_include_replenishment,
1827 p_from_replenishment_mo => l_from_replenishment_mo,
1828 p_to_replenishment_mo => l_to_replenishment_mo,
1829 p_include_mo_transfer => l_include_mo_transfer,
1830 p_include_mo_issue => l_include_mo_issue,
1831 p_from_transfer_issue_mo => l_from_transfer_issue_mo,
1832 p_to_transfer_issue_mo => l_to_transfer_issue_mo,
1833 p_include_lpn_putaway => l_include_lpn_putaway,
1834 p_include_staging_move => l_include_staging_move,
1835 p_include_cycle_count => l_include_cycle_count,
1836 p_cycle_count_name => l_cycle_count_name,
1837 x_return_status => l_return_status,
1838 x_msg_data => l_msg_data,
1839 x_msg_count => l_msg_count,
1840 x_record_count => l_record_count,
1841 p_query_independent_tasks => l_query_independent_tasks,
1842 p_query_planned_tasks => l_query_planned_tasks,
1843 p_is_pending_plan => l_is_pending_plan,
1844 p_is_inprogress_plan => l_is_inprogress_plan,
1845 p_is_completed_plan => l_is_completed_plan,
1846 p_is_cancelled_plan => l_is_cancelled_plan,
1847 p_is_aborted_plan => l_is_aborted_plan,
1848 p_activity_id => l_op_plan_activity_id,
1849 p_plan_type_id => l_op_plan_type_id,
1850 p_op_plan_id => l_op_plan_id,
1851
1852 --added for Bug:--13014493
1853 p_time_till_shipment => l_time_till_shipment,
1854 p_time_till_shipment_uom_code => l_time_till_shipment_uom_code
1855 );
1856
1857 DEBUG ( 'WMS_WAVEPLAN_TASKS_PVT.QUERY_TASKS x_return_status => ' || l_return_status );
1858 DEBUG ( 'WMS_WAVEPLAN_TASKS_PVT.QUERY_TASKS x_msg_data => ' || l_msg_data);
1859 DEBUG ( 'WMS_WAVEPLAN_TASKS_PVT.QUERY_TASKS x_msg_count => ' || l_msg_count);
1860 DEBUG ( 'WMS_WAVEPLAN_TASKS_PVT.QUERY_TASKS x_record_count => ' || l_record_count);
1861
1862 IF l_return_status = fnd_api.g_ret_sts_error
1863 THEN
1864 DEBUG (' Error in WMS_WAVEPLAN_TASKS_PVT.QUERY_TASKS ' );
1865 x_return_status := FND_API.G_RET_STS_ERROR;
1866 x_return_message:= 'WMS_WAVEPLAN_TASKS_PVT.QUERY_TASKS returned with Error status'
1867 || 'Error message is ' || l_return_message;
1868 return;
1869 END IF;
1870
1871 -- Clearing the tables.
1872 l_field_name_table.delete;
1873 l_field_value_table.delete;
1874 l_query_type_table.delete;
1875
1876 DEBUG ('Cleared pl/sql tables l_query_type_table, l_field_name_table and l_field_value_table.' );
1877
1878 DEBUG ( 'Opening c_saved_actions cursor');
1879
1880 OPEN c_saved_actions( p_action_name );
1881 FETCH c_saved_actions
1882 BULK COLLECT INTO l_field_name_table, l_field_value_table, l_query_type_table;
1883
1884 -- If no records founds for the given query name
1885 -- then close the cursor and return informing invalid query name.
1886
1887 DEBUG( 'c_saved_actions%ROWCOUNT = ' || c_saved_actions%ROWCOUNT );
1888
1889 x_rowcount := c_saved_actions%ROWCOUNT;
1890
1891 IF c_saved_actions%ROWCOUNT = 0
1892 THEN
1893 CLOSE c_saved_actions;
1894
1895 DEBUG ('No data found for action name. ' || p_action_name);
1896 x_rowcount := 0;
1897 x_return_status := fnd_api.g_ret_sts_success;
1898 x_return_message:= 'No data found for action name. ' || p_action_name;
1899
1900 FOR rec_query_type IN c_query_type( p_query_name )
1901 LOOP
1902 IF rec_query_type.query_type = 'TEMP_TASK_ACTION'
1903 THEN
1904 l_temp_action := TRUE;
1905 ELSE
1906 l_temp_action := FALSE;
1907 END IF;
1908 END LOOP;
1909
1910 RETURN;
1911 END IF;
1912
1913 CLOSE c_saved_actions;
1914
1915 DEBUG ( 'Bulk collect successful and closed c_saved_actions cursor');
1916
1917 DEBUG ( 'Calling SET_ACTION_TASKS_PARAMETERS');
1918
1919 SET_ACTION_TASKS_PARAMETERS
1920 ( p_field_name_table => l_field_name_table,
1921 p_field_value_table => l_field_value_table ,
1922 p_query_type_table => l_query_type_table ,
1923 x_return_status => l_return_status,
1924 x_return_message => l_return_message
1925 );
1926
1927
1928 DEBUG ( 'SET_ACTION_TASKS_PARAMETERS return status = ' || l_return_status );
1929 DEBUG ( 'SET_ACTION_TASKS_PARAMETERS return message = ' || l_return_message );
1930
1931 -- If set_action_tasks_parameters returns error then log message and return.
1932 IF l_return_status = fnd_api.g_ret_sts_error OR
1933 l_return_status = fnd_api.g_ret_sts_unexp_error
1934 THEN
1935 DEBUG (' Error in SET_ACTION_TASKS_PARAMETERS ' );
1936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1937 return;
1938 END IF;
1939
1940 DEBUG ( 'No of eligible tasks to be updated are ' || l_field_name_table.count );
1941
1942 IF p_action = l_action_type
1943 THEN
1944 IF p_action = 'U'
1945 THEN
1946 DEBUG( 'Calling UPDATE_TASK');
1947
1948 UPDATE_TASK
1949 ( x_return_status => l_return_status,
1950 x_return_message=> l_return_message
1951 );
1952
1953 IF l_return_status = fnd_api.g_ret_sts_error OR
1954 l_return_status = fnd_api.g_ret_sts_unexp_error
1955 THEN
1956 DEBUG (' Error in UPDATE_TASK ' );
1957 x_return_status := FND_API.G_RET_STS_ERROR;
1958 x_return_message:= 'UPDATE_TASK returned with Error status'
1959 || 'Error message is ' || l_return_message;
1960 return;
1961 END IF;
1962 ELSIF p_action = 'C' THEN
1963 DEBUG( 'Calling CANCEL_TASK');
1964
1965 CANCEL_TASK
1966 ( x_return_status => l_return_status,
1967 x_return_message=> l_return_message
1968 );
1969
1970 IF l_return_status = fnd_api.g_ret_sts_error OR
1971 l_return_status = fnd_api.g_ret_sts_unexp_error
1972 THEN
1973 DEBUG (' Error in CANCEL_TASK ' );
1974 x_return_status := FND_API.G_RET_STS_ERROR;
1975 x_return_message:= 'CANCEL_TASK returned with Error status'
1976 || 'Error message is ' || l_return_message;
1977 return;
1978 END IF;
1979
1980 END IF;
1981 ELSE
1982 DEBUG( 'Could not perform specified Action.' );
1983 DEBUG( 'Action type of input parameter Action Name, "' || l_action_type ||
1984 '", does not match with Action parameter, "'|| p_action || '".');
1985 x_return_status := FND_API.G_RET_STS_ERROR;
1986 x_return_message:= 'Could not perform specified Action.'
1987 || 'Action type of input parameter Action Name,"'
1988 || l_action_type ||
1989 '", does not match with Action parameter,"'
1990 || p_action || '".';
1991 return;
1992 END IF;
1993
1994 IF p_online = 'Y'
1995 THEN
1996 IF l_temp_query
1997 THEN
1998 DEBUG( 'Calling DELETE_TEMP_QUERY');
1999 DELETE_TEMP_QUERY
2000 (
2001 p_query_name => p_query_name,
2002 x_return_status => l_return_status,
2003 x_return_message => l_return_message
2004 );
2005
2006 DEBUG( 'DELETE_TEMP_QUERY return status = ' || l_return_status );
2007 DEBUG( 'DELETE_TEMP_QUERY return message = ' || l_return_message );
2008
2009 IF l_return_status = fnd_api.g_ret_sts_error
2010 OR l_return_status = fnd_api.g_ret_sts_unexp_error
2011 THEN
2012 x_return_status := fnd_api.g_ret_sts_error;
2013 x_return_message := l_return_message;
2014 END IF;
2015 END IF;
2016
2017 IF l_temp_action
2018 THEN
2019 DEBUG( 'Calling DELETE_TEMP_ACTION');
2020 DELETE_TEMP_ACTION
2021 (
2022 p_action_name => p_action_name,
2023 x_return_status => l_return_status,
2024 x_return_message => l_return_message
2025 );
2026
2027 DEBUG( 'DELETE_TEMP_ACTION return status = ' || l_return_status );
2028 DEBUG( 'DELETE_TEMP_ACTION return message = ' || l_return_message );
2029
2030 IF l_return_status = fnd_api.g_ret_sts_error
2031 OR l_return_status = fnd_api.g_ret_sts_unexp_error
2032 THEN
2033 x_return_status := fnd_api.g_ret_sts_error;
2034 x_return_message := l_return_message;
2035 END IF;
2036 END IF;
2037 END IF;
2038
2039 x_return_status := FND_API.G_RET_STS_SUCCESS;
2040
2041 DEBUG( 'Exiting TASK_ACTION');
2042
2043 EXCEPTION
2044 WHEN OTHERS
2045 THEN
2046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2047 x_return_message:= 'Unexpected error has occured in TASK_ACTION. '
2048 || 'Oracle error message is ' || SQLERRM;
2049 DEBUG( 'Unexpected error has occured. Oracle error message is '
2050 || SQLERRM , 'WMS_TASK_ACTION_PVT.TASK_ACTION' );
2051 END TASK_ACTION;
2052
2053 PROCEDURE SUBMIT_REQUEST
2054 (
2055 p_query_name IN VARCHAR2,
2056 p_action IN VARCHAR2,
2057 p_action_name IN VARCHAR2,
2058 x_request_id OUT NOCOPY NUMBER,
2059 x_return_status OUT NOCOPY VARCHAR2,
2060 x_return_message OUT NOCOPY VARCHAR2
2061 )
2062 IS
2063 l_request_id NUMBER;
2064 l_return VARCHAR2(1);
2065 begin
2066
2067 DEBUG( 'Calling program unit FND_REQUEST.Submit_Request');
2068 DEBUG( 'Current Time is ',SYSDATE);
2069 DEBUG( 'Parameters passed to Concurrent program are as follows');
2070 DEBUG( 'argument1 = ' || p_query_name );
2071 DEBUG( 'argument2 = ' || p_action );
2072 DEBUG( 'argument3 = ' || p_action_name );
2073 l_request_id := FND_REQUEST.SUBMIT_REQUEST
2074 (
2075 application => 'WMS',
2076 program => 'WMSCBTAC',
2077 description => '',
2078 start_time => '',
2079 sub_request => FALSE,
2080 argument1 => p_query_name,
2081 argument2 => p_action,
2082 argument3 => p_action_name
2083 );
2084
2085 -- If request submission failed, exit with error.
2086 IF l_request_id <= 0
2087 THEN
2088 DEBUG('Request submission failed', 'WMS_TASK_ACTION_PVT.SUBMIT_REQUEST');
2089 l_return := FND_API.G_RET_STS_ERROR;
2090 RETURN;
2091 ELSE
2092 DEBUG( 'Request '||l_request_id||' submitted successfully');
2093 COMMIT WORK;
2094 END IF;
2095
2096 x_request_id := l_request_id;
2097
2098 x_return_status := FND_API.G_RET_STS_SUCCESS;
2099
2100 DEBUG( 'Exiting SUBMIT_REQUEST' );
2101
2102 EXCEPTION
2103 WHEN OTHERS
2104 THEN
2105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2106 x_return_message:= 'Unexpected error has occured in WMS_TASK_ACTION_PVT.SUBMIT_REQUEST. '
2107 || 'Oracle error message is ' || SQLERRM;
2108
2109 DEBUG( 'Unexpected error has occured. Oracle error message is '
2110 || SQLERRM , 'WMS_TASK_ACTION_PVT.SUBMIT_REQUEST' );
2111 DEBUG( 'Unexpected error has occured. Oracle error message is '
2112 || SQLERRM , 'WMS_TASK_ACTION_PVT.SUBMIT_REQUEST' );
2113 END SUBMIT_REQUEST;
2114
2115 END WMS_TASK_ACTION_PVT;