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