[Home] [Help]
PACKAGE BODY: APPS.WMS_LMS_UTILS
Source
1 PACKAGE BODY WMS_LMS_UTILS AS
2 /* $Header: WMSLUTLB.pls 120.13.12010000.2 2008/08/19 09:54:39 anviswan ship $ */
3
4 g_version_printed BOOLEAN := FALSE;
5 g_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6
7 PROCEDURE DEBUG(p_message IN VARCHAR2,
8 p_module IN VARCHAR2 default 'abc',
9 p_level IN VARCHAR2 DEFAULT 9) IS
10 BEGIN
11
12 IF NOT g_version_printed THEN
13 INV_TRX_UTIL_PUB.TRACE('$Header: WMSLUTLB.pls 120.13.12010000.2 2008/08/19 09:54:39 anviswan ship $',g_pkg_name, 9);
14 g_version_printed := TRUE;
15 END IF;
16
17 INV_TRX_UTIL_PUB.TRACE( P_MESG =>P_MESSAGE
18 ,P_MOD => p_module
19 ,p_level => p_level
20 );
21 END DEBUG;
22
23
24 FUNCTION ORG_LABOR_MGMT_ENABLED ( p_org_id IN NUMBER)
25 RETURN BOOLEAN IS
26
27 l_labor_enabled_flag VARCHAR2(1);
28 l_labor_mgmt_enabled BOOLEAN;
29
30 BEGIN
31
32 l_labor_enabled_flag := NULL;
33
34 IF g_debug=1 THEN
35 debug('The value of p_org_id '|| p_org_id,'ORG_LABOR_MGMT_ENABLED');
36 END IF;
37
38 -- select from mtl_parameters table the values of wms_enabled
39 -- and labor Mangement enabled flags
40
41 SELECT labor_management_enabled_flag
42 INTO l_labor_enabled_flag
43 FROM mtl_parameters
44 WHERE organization_id = p_org_id;
45
46 --Check for the value of this flag and return true if the
47 -- the flag is 'Y' and false otherwise
48
49 IF (l_labor_enabled_flag ='Y' ) THEN
50 l_labor_mgmt_enabled := TRUE;
51
52 IF g_debug=1 THEN
53 debug('Org Labor Management Enabled ','ORG_LABOR_MGMT_ENABLED');
54 END IF;
55
56 ELSE
57 l_labor_mgmt_enabled := FALSE;
58
59
60 IF g_debug=1 THEN
61 debug('Org not Labor Management Enabled ','ORG_LABOR_MGMT_ENABLED');
62 END IF;
63
64 END IF;
65
66
67 RETURN l_labor_mgmt_enabled ;
68
69 EXCEPTION
70 --handle exceptions
71 WHEN OTHERS THEN
72
73 IF g_debug=1 THEN
74 debug('Came to the Exception','ORG_LABOR_MGMT_ENABLED');
75 END IF;
76
77 l_labor_mgmt_enabled := FALSE;
78 RETURN l_labor_mgmt_enabled ;
79
80 END ORG_LABOR_MGMT_ENABLED;
81
82
83 FUNCTION ZONE_LABOR_MGMT_ENABLED ( p_org_id IN NUMBER,
84 p_zone_id IN NUMBER
85 )
86 RETURN VARCHAR2 IS
87 l_labor_enabled_flag VARCHAR2(1);
88 --l_labor_mgmt_enabled NUMBER;
89
90 BEGIN
91 l_labor_enabled_flag := NULL;
92
93 IF g_debug=1 THEN
94 debug('The value of p_org_id '|| p_org_id,'ZONE_LABOR_MGMT_ENABLED');
95 debug('The value of p_zone_id '|| p_zone_id,'ZONE_LABOR_MGMT_ENABLED');
96 END IF;
97
98
99 -- select the labor enabled flag value for wms_zones_b table
100 -- into the local variable
101
102 SELECT labor_enabled
103 INTO l_labor_enabled_flag
104 FROM wms_zones_b
105 WHERE organization_id = p_org_id
106 AND zone_id = p_zone_id;
107
108
109 IF (l_labor_enabled_flag IS NULL ) THEN
110 --l_labor_mgmt_enabled := TRUE;
111 l_labor_enabled_flag :='N';
112 IF g_debug=1 THEN
113 debug('Zone not Labor Management Enabled ','ZONE_LABOR_MGMT_ENABLED');
114 END IF;
115 END IF;
116
117 RETURN l_labor_enabled_flag ;
118
119 EXCEPTION
120 --handle exceptions
121 WHEN OTHERS THEN
122 IF g_debug=1 THEN
123 debug('Came to the Exception','ZONE_LABOR_MGMT_ENABLED');
124 END IF;
125 l_labor_enabled_flag := 'N';
126 RETURN l_labor_enabled_flag ;
127
128 END ZONE_LABOR_MGMT_ENABLED;
129
130
131
132 FUNCTION IS_USER_NON_TRACKED ( p_user_id IN NUMBER,
133 p_org_id IN NUMBER )
134 RETURN BOOLEAN IS
135
136 l_user_id NUMBER;
137 BEGIN
138
139 l_user_id := 0;
140
141 IF g_debug=1 THEN
142 debug('The value of user_id '|| p_user_id,'IS_USER_NON_TRACKED');
143 END IF;
144
145 --select from the WMS_ELS_NON_TRACKED_USERS and see that
146 -- is the user_id has been defined as a phantom user or not.
147
148 SELECT 1
149 INTO l_user_id
150 FROM WMS_ELS_NON_TRACKED_USERS
151 WHERE USER_ID = P_USER_ID
152 AND ORGANIZATION_ID= P_ORG_ID;
153
154 -- A row is returned so return true(the user is a phantom user)
155
156 RETURN TRUE;
157
158 IF g_debug=1 THEN
159 debug('The user is a non tracked user','IS_USER_NON_TRACKED');
160 END IF;
161
162 EXCEPTION
163 --handle exceptions (the user is not a phantom user
164 WHEN NO_DATA_FOUND THEN
165
166 IF g_debug=1 THEN
167 debug('No Data found Exception. So user is a tracked user','IS_USER_NON_TRACKED');
168 END IF;
169
170 RETURN FALSE;
171
172 WHEN OTHERS THEN
173
174 IF g_debug=1 THEN
175 debug('In Exception','IS_USER_NON_TRACKED');
176 END IF;
177
178 RETURN FALSE;
179
180 END IS_USER_NON_TRACKED;
181
182
183
184 PROCEDURE PURGE_LMS_SETUP_HISTORY
185 (errbuf OUT NOCOPY VARCHAR2,
186 retcode OUT NOCOPY NUMBER,
187 p_org_id IN NUMBER,
188 p_purge_date IN VARCHAR2
189 )
190 IS
191
192 CURSOR els_history(l_org_id NUMBER,l_purge_date VARCHAR2) IS SELECT els_data_id
193 FROM wms_els_individual_tasks_b
194 WHERE history_flag = 1
195 AND organization_id = l_org_id
196 AND archive_date <= NVL (TO_DATE(l_purge_date,'YYYY/MM/DD HH24:MI:SS') ,SYSDATE);
197
198 TYPE els_id_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
199
200 l_els_id_tab els_id_tab;
201 --l_limit NUMBER;
202 l_records_deleted NUMBER;
203 l_ret BOOLEAN;
204 l_message VARCHAR2(250);
205
206 BEGIN
207
208 SAVEPOINT purge_lms_history;
209
210 l_records_deleted :=0;
211
212 IF g_debug=1 THEN
213 debug('The value of organization Id '|| p_org_id,'PURGE_LMS_SETUP_HISTORY');
214 END IF;
215
216 -- Only have to proceed when the org_id being passed is not NULL
217
218 IF (p_org_id IS NOT NULL) THEN
219
220 LOOP
221 OPEN els_history(p_org_id,p_purge_date);
222
223 FETCH els_history bulk collect INTO l_els_id_tab;
224 EXIT WHEN els_history%NOTFOUND;
225 END LOOP;
226
227 CLOSE els_history;
228
229 --delete all records in wms_lab_trx_src table which have els_data_id
230 -- costed against them
231
232 FORALL i IN l_els_id_tab.first..l_els_id_tab.last
233
234 DELETE wms_els_trx_src
235 WHERE els_data_id = l_els_id_tab(i);
236
237 l_records_deleted :=SQL%ROWCOUNT;
238
239 IF g_debug=1 THEN
240 debug('Number of records deleted from wms_els_trx_src table '|| l_records_deleted,
241 'PURGE_LMS_SETUP_HISTORY'
242 );
243 END IF;
244
245 l_records_deleted:=0;
246
247 -- delete all records from ems_els_individual_tasks table which have
248 -- been marked as hsitory
249
250 FORALL j IN l_els_id_tab.first..l_els_id_tab.last
251
252 DELETE wms_els_individual_tasks_b
253 WHERE els_data_id = l_els_id_tab(j);
254
255 l_records_deleted :=SQL%ROWCOUNT;
256
257 IF g_debug=1 THEN
258 debug('Number of records deleted from wms_els_individual_tasks_b table '|| l_records_deleted,
259 'PURGE_LMS_SETUP_HISTORY'
260 );
261 END IF;
262
263 l_records_deleted:=0;
264
265 FORALL k IN l_els_id_tab.first..l_els_id_tab.last
266
267 DELETE wms_els_individual_tasks_tl
268 WHERE els_data_id = l_els_id_tab(k);
269
270 l_records_deleted :=SQL%ROWCOUNT;
271
272 IF g_debug=1 THEN
273 debug('Number of records deleted from wms_els_individual_tasks_tl table '|| l_records_deleted,
274 'PURGE_LMS_SETUP_HISTORY'
275 );
276 END IF;
277
278 IF g_debug=1 THEN
279 debug('Purge Successfully completed' ,
280 'PURGE_LMS_SETUP_HISTORY'
281 );
282 END IF;
283
284 retcode := 1;
285 fnd_message.set_name('WMS', 'WMS_LMS_PURGE_HIST_SUCCESS');
286 l_message := fnd_message.get;
287 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',l_message);
288
289 ELSE
290
291 IF g_debug=1 THEN
292 debug('Org Id being passed is null' ,
293 'PURGE_LMS_SETUP_HISTORY'
294 );
295 END IF;
296
297 retcode := 2;
298 fnd_message.set_name('WMS', 'WMS_LMS_REQ_PARAM_NULL');
299 l_message := fnd_message.get;
300 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
301 END IF;
302
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 IF g_debug=1 THEN
307 debug('Exception has occured during purge'||SQLERRM ,
308 'PURGE_LMS_SETUP_HISTORY'
309 );
310 END IF;
311 ROLLBACK TO purge_lms_history;
312 retcode := 2;
313 fnd_message.set_name('WMS', 'WMS_LMS_PURGE_HIST_ERROR');
314 l_message := fnd_message.get;
315 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
316
317 END PURGE_LMS_SETUP_HISTORY;
318
319
320 PROCEDURE BUCKET_ACTUAL_TIMINGS ( x_return_status OUT NOCOPY VARCHAR2
321 , x_msg_count OUT NOCOPY VARCHAR2
322 , x_msg_data OUT NOCOPY VARCHAR2
323 , p_org_id IN NUMBER
324 , p_purge_date IN DATE
325 )IS
326 l_records_inserted NUMBER;
327 BEGIN
328 l_records_inserted :=0;
329 IF g_debug=1 THEN
330 debug('The value of organization Id '|| p_org_id,'BUCKET_ACTUAL_TIMINGS');
331 debug('The value of purge date is '|| p_purge_date,'BUCKET_ACTUAL_TIMINGS');
332 END IF;
333
334 --select the summary row from the table and insert into the table
335
336 INSERT INTO WMS_ELS_TRX_SRC
337 (els_trx_src_id,
338 els_data_id,
339 organization_id,
340 travel_time,
341 transaction_time,
342 idle_time,
343 last_update_date,
344 last_updated_by,
345 created_by,
346 creation_date
347 )
348 SELECT WMS_ELS_TRX_SRC_S.NEXTVAL,
349 els_data_id,
350 organization_id,
351 travel_time,
352 transaction_time,
353 idle_time,
354 SYSDATE,
355 FND_GLOBAL.USER_ID,
356 FND_GLOBAL.USER_ID,
357 SYSDATE
358 FROM
359 (SELECT ELS_DATA_ID els_data_id,
360 organization_id,
361 AVG(TRAVEL_TIME) travel_time,
362 AVG(TRANSACTION_TIME) transaction_time,
363 AVG(IDLE_TIME) Idle_time
364 FROM WMS_ELS_TRX_SRC
365 WHERE transaction_date < p_purge_date
366 AND organization_id = p_org_id
367 AND els_data_id IS NOT NULL
368 GROUP BY ELS_DATA_ID,organization_id
369 );
370
371 l_records_inserted := SQL%ROWCOUNT;
372
373 IF g_debug=1 THEN
374 debug( 'The process of bucketing the transaction records is a success'
375 ,'BUCKET_ACTUAL_TIMINGS');
376 debug( 'The number of bucketed records inserted are '|| l_records_inserted
377 ,'BUCKET_ACTUAL_TIMINGS');
378 END IF;
379
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381
382 EXCEPTION
383 WHEN OTHERS THEN
384 IF g_debug=1 THEN
385 debug( 'The process of bucketing the transaction records is a failure'
386 ,'BUCKET_ACTUAL_TIMINGS');
387 END IF;
388
389 x_return_status := FND_API.G_RET_STS_ERROR;
390
391 END BUCKET_ACTUAL_TIMINGS;
392
393
394
395
396 PROCEDURE PURGE_LMS_TRANSACTIONS
397 (errbuf OUT NOCOPY VARCHAR2,
398 retcode OUT NOCOPY NUMBER,
399 p_org_id IN NUMBER,
400 p_purge_date IN VARCHAR2
401 )
402 IS
403 l_records_deleted NUMBER;
404 l_ret BOOLEAN;
405 l_message VARCHAR2(250);
406 l_return_status VARCHAR2(1);
407 l_msg_count VARCHAR2(10);
408 l_msg_data VARCHAR2(100);
409 BEGIN
410
411 l_records_deleted := 0;
412
413 SAVEPOINT purge_lms_txns;
414
415 IF g_debug=1 THEN
416 debug('The value of organization Id '|| p_org_id,'PURGE_LMS_TRANSACTIONS');
417 debug('The value of purge date is '|| p_purge_date,'PURGE_LMS_TRANSACTIONS');
418 END IF;
419
420
421 IF (p_org_id IS NOT NULL) THEN
422 -- proceed only when the mandatory parameters are passed
423
424 -- First bucket the actual timings to be later used if the option
425 -- for calculating the actual values for the time components when
429 debug('Before calling Bucket_actual_timings ','PURGE_LMS_TRANSACTIONS');
426 -- moving avaerage value is 'ALL'
427
428 IF g_debug=1 THEN
430 END IF;
431
432 WMS_LMS_UTILS.BUCKET_ACTUAL_TIMINGS ( x_return_status => l_return_status
433 , x_msg_count => l_msg_count
434 , x_msg_data => l_msg_data
435 , p_org_id => p_org_id
436 , p_purge_date => TO_DATE(p_purge_date,'YYYY/MM/DD HH24:MI:SS')
437 );
438
439
440 IF(l_return_status = FND_API.g_ret_sts_success )THEN
441 -- If success then only proceed with the purging
442
443 IF g_debug=1 THEN
444 debug( 'Call to Bucket_actual_timings is a success now deleting '
445 ,'PURGE_LMS_TRANSACTIONS'
446 );
447 END IF;
448
449 DELETE wms_els_trx_src
450 WHERE transaction_date <= NVL(TO_DATE(p_purge_date,'YYYY/MM/DD HH24:MI:SS'),SYSDATE)
451 AND organization_id = p_org_id;
452
453 l_records_deleted :=SQL%ROWCOUNT;
454 -- set the appropiate return code and status
455
456 IF g_debug=1 THEN
457 debug( 'Number of rows deleted'|| l_records_deleted
458 ,'PURGE_LMS_TRANSACTIONS'
459 );
460 END IF;
461
462 retcode := 1;
463 fnd_message.set_name('WMS', 'WMS_LMS_PURGE_TXNS_SUCCESS');
464 l_message := fnd_message.get;
465 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',l_message);
466
467 ELSE -- call to bucket actual timings is not a success
468
469 IF g_debug=1 THEN
470 debug( 'Call to Bucket_actual_timings is not a success '
471 ,'PURGE_LMS_TRANSACTIONS'
472 );
473 END IF;
474
475 retcode := 2;
476 fnd_message.set_name('WMS', 'WMS_LMS_PURGE_TXNS_ERROR');
477 l_message := fnd_message.get;
478 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
479
480 ROLLBACK TO purge_lms_txns;
481
482 END IF;
483
484 ELSE
485 -- When the required parameters not passed
486
487 IF g_debug=1 THEN
488 debug( 'Required parameters not passed '
489 ,'PURGE_LMS_TRANSACTIONS'
490 );
491 END IF;
492
493 retcode := 2;
494 fnd_message.set_name('WMS', 'WMS_LMS_REQ_PARAM_NULL');
495 l_message := fnd_message.get;
496 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
497
498 END IF; -- when required parameters passed
499
500
501 EXCEPTION
502 WHEN OTHERS THEN
503 IF g_debug=1 THEN
504 debug( 'Exception has occured'
505 ,'PURGE_LMS_TRANSACTIONS'
506 );
507 END IF;
508 ROLLBACK TO purge_lms_txns;
509 retcode := 2;
510 fnd_message.set_name('WMS', 'WMS_LMS_PURGE_TXNS_ERROR');
511 l_message := fnd_message.get;
512 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
513
514 END PURGE_LMS_TRANSACTIONS;
515
516
517
518 PROCEDURE COPY_ACTUAL_TIMINGS ( errbuf OUT NOCOPY VARCHAR2
519 , retcode OUT NOCOPY NUMBER
520 , p_org_id IN NUMBER
521 ) IS
522 l_records_updated NUMBER;
523 l_error NUMBER;
524 l_ret BOOLEAN;
525 l_message VARCHAR2(250);
526
527 /* The following cursor has been created to fetch the non-history els data records
528 * so that the values will be used to create the history records while executing
529 * the "Standardize Actual to Expected times" request.
530 */
531
532 CURSOR els_individual_tasks_cur(l_org_id NUMBER) IS
533 SELECT ELS_DATA_ID
534 , ORGANIZATION_ID
535 , SEQUENCE_NUMBER
536 , ANALYSIS_ID
537 , ACTIVITY_ID
538 , ACTIVITY_DETAIL_ID
539 , OPERATION_ID
540 , EQUIPMENT_ID
541 , SOURCE_ZONE_ID
542 , SOURCE_SUBINVENTORY
543 , DESTINATION_ZONE_ID
544 , DESTINATION_SUBINVENTORY
545 , LABOR_TXN_SOURCE_ID
546 , TRANSACTION_UOM
547 , FROM_QUANTITY
548 , TO_QUANTITY
549 , ITEM_CATEGORY_ID
550 , OPERATION_PLAN_ID
551 , GROUP_ID
552 , TASK_TYPE_ID
553 , TASK_METHOD_ID
554 , EXPECTED_TRAVEL_TIME
555 , EXPECTED_TXN_TIME
556 , EXPECTED_IDLE_TIME
557 , ACTUAL_TRAVEL_TIME
558 , ACTUAL_TXN_TIME
559 , ACTUAL_IDLE_TIME
560 , TRAVEL_TIME_THRESHOLD
561 , ARCHIVE_DATE
562 , NUM_TRX_MATCHED
563 , ATTRIBUTE_CATEGORY
564 , ATTRIBUTE1
565 , ATTRIBUTE2
566 , ATTRIBUTE3
570 , ATTRIBUTE7
567 , ATTRIBUTE4
568 , ATTRIBUTE5
569 , ATTRIBUTE6
571 , ATTRIBUTE8
572 , ATTRIBUTE9
573 , ATTRIBUTE10
574 , ATTRIBUTE12
575 , ATTRIBUTE13
576 , ATTRIBUTE14
577 , ATTRIBUTE15
578 FROM wms_els_individual_tasks_b
579 WHERE organization_id= l_org_id
580 AND nvl(HISTORY_FLAG, -999) <> 1;
581
582 /* The following cursor has been added to fetch the records from TL table
583 * for each els_data_id that is present in the base table.
584 */
585
586 CURSOR els_individual_tasks_tl_cur(l_els_data_id NUMBER) IS
587 SELECT description
588 , language
589 , source_lang
590 FROM wms_els_individual_tasks_tl
591 WHERE els_data_id = l_els_data_id;
592
593 l_new_els_data_id NUMBER;
594
595 BEGIN
596
597 l_records_updated:=0;
598 l_error := 0;
599
600 IF g_debug=1 THEN
601 debug('The value of organization Id '|| p_org_id,'COPY_ACTUAL_TIMINGS');
602 END IF;
603
604 BEGIN
605 /* Start of fix for bug # 5520074
606 * The following code has been added to maintain the history records while executing
607 * "Standardize Actual to Expected Times" request
608 */
609 IF g_debug=1 THEN
610 debug('Before creating History Records' , 'COPY_ACTUAL_TIMINGS');
611 END IF;
612
613 FOR els_individual_tasks_rec IN els_individual_tasks_cur(p_org_id) LOOP
614
615 SELECT WMS_ELS_INDIVIDUAL_TASKS_S.NEXTVAL
616 INTO l_new_els_data_id
617 FROM dual;
618
619 INSERT INTO wms_els_individual_tasks_b (
620 ELS_DATA_ID
621 , ORGANIZATION_ID
622 , SEQUENCE_NUMBER
623 , ANALYSIS_ID
624 , ACTIVITY_ID
625 , ACTIVITY_DETAIL_ID
626 , OPERATION_ID
627 , EQUIPMENT_ID
628 , SOURCE_ZONE_ID
629 , SOURCE_SUBINVENTORY
630 , DESTINATION_ZONE_ID
631 , DESTINATION_SUBINVENTORY
632 , LABOR_TXN_SOURCE_ID
633 , TRANSACTION_UOM
634 , FROM_QUANTITY
635 , TO_QUANTITY
636 , ITEM_CATEGORY_ID
637 , OPERATION_PLAN_ID
638 , GROUP_ID
639 , TASK_TYPE_ID
640 , TASK_METHOD_ID
641 , EXPECTED_TRAVEL_TIME
642 , EXPECTED_TXN_TIME
643 , EXPECTED_IDLE_TIME
644 , ACTUAL_TRAVEL_TIME
645 , ACTUAL_TXN_TIME
646 , ACTUAL_IDLE_TIME
647 , TRAVEL_TIME_THRESHOLD
648 , HISTORY_FLAG
649 , ARCHIVE_DATE
650 , NUM_TRX_MATCHED
651 , LAST_UPDATED_BY
652 , LAST_UPDATE_LOGIN
653 , CREATED_BY
654 , CREATION_DATE
655 , LAST_UPDATE_DATE
656 , ATTRIBUTE_CATEGORY
657 , ATTRIBUTE1
658 , ATTRIBUTE2
659 , ATTRIBUTE3
660 , ATTRIBUTE4
661 , ATTRIBUTE5
662 , ATTRIBUTE6
663 , ATTRIBUTE7
664 , ATTRIBUTE8
665 , ATTRIBUTE9
666 , ATTRIBUTE10
667 , ATTRIBUTE12
668 , ATTRIBUTE13
669 , ATTRIBUTE14
670 , ATTRIBUTE15)
671 VALUES ( l_new_els_data_id
672 , els_individual_tasks_rec.ORGANIZATION_ID
673 , els_individual_tasks_rec.SEQUENCE_NUMBER
674 , els_individual_tasks_rec.ANALYSIS_ID
675 , els_individual_tasks_rec.ACTIVITY_ID
676 , els_individual_tasks_rec.ACTIVITY_DETAIL_ID
677 , els_individual_tasks_rec.OPERATION_ID
678 , els_individual_tasks_rec.EQUIPMENT_ID
679 , els_individual_tasks_rec.SOURCE_ZONE_ID
680 , els_individual_tasks_rec.SOURCE_SUBINVENTORY
681 , els_individual_tasks_rec.DESTINATION_ZONE_ID
682 , els_individual_tasks_rec.DESTINATION_SUBINVENTORY
683 , els_individual_tasks_rec.LABOR_TXN_SOURCE_ID
684 , els_individual_tasks_rec.TRANSACTION_UOM
685 , els_individual_tasks_rec.FROM_QUANTITY
686 , els_individual_tasks_rec.TO_QUANTITY
687 , els_individual_tasks_rec.ITEM_CATEGORY_ID
688 , els_individual_tasks_rec.OPERATION_PLAN_ID
689 , els_individual_tasks_rec.GROUP_ID
690 , els_individual_tasks_rec.TASK_TYPE_ID
691 , els_individual_tasks_rec.TASK_METHOD_ID
692 , els_individual_tasks_rec.EXPECTED_TRAVEL_TIME
693 , els_individual_tasks_rec.EXPECTED_TXN_TIME
694 , els_individual_tasks_rec.EXPECTED_IDLE_TIME
695 , els_individual_tasks_rec.ACTUAL_TRAVEL_TIME
696 , els_individual_tasks_rec.ACTUAL_TXN_TIME
697 , els_individual_tasks_rec.ACTUAL_IDLE_TIME
698 , els_individual_tasks_rec.TRAVEL_TIME_THRESHOLD
699 , 1 -- The new record created will be a history record. Hence, it takes the values as 1.
703 , FND_GLOBAL.LOGIN_ID
700 , SYSDATE -- ARCHIVE_DATE
701 , els_individual_tasks_rec.NUM_TRX_MATCHED
702 , FND_GLOBAL.USER_ID
704 , FND_GLOBAL.USER_ID
705 , SYSDATE
706 , SYSDATE
707 , els_individual_tasks_rec.ATTRIBUTE_CATEGORY
708 , els_individual_tasks_rec.ATTRIBUTE1
709 , els_individual_tasks_rec.ATTRIBUTE2
710 , els_individual_tasks_rec.ATTRIBUTE3
711 , els_individual_tasks_rec.ATTRIBUTE4
712 , els_individual_tasks_rec.ATTRIBUTE5
713 , els_individual_tasks_rec.ATTRIBUTE6
714 , els_individual_tasks_rec.ATTRIBUTE7
715 , els_individual_tasks_rec.ATTRIBUTE8
716 , els_individual_tasks_rec.ATTRIBUTE9
717 , els_individual_tasks_rec.ATTRIBUTE10
718 , els_individual_tasks_rec.ATTRIBUTE12
719 , els_individual_tasks_rec.ATTRIBUTE13
720 , els_individual_tasks_rec.ATTRIBUTE14
721 , els_individual_tasks_rec.ATTRIBUTE15);
722
723 /* Insert the history record data in TL tables*/
724 FOR els_individual_tasks_tl_rec IN els_individual_tasks_tl_cur(els_individual_tasks_rec.els_data_id) LOOP
725 INSERT INTO wms_els_individual_tasks_tl(
726 ELS_DATA_ID
727 , LANGUAGE
728 , SOURCE_LANG
729 , DESCRIPTION
730 , LAST_UPDATED_BY
731 , LAST_UPDATE_LOGIN
732 , CREATED_BY
733 , CREATION_DATE
734 , LAST_UPDATE_DATE)
735 VALUES( l_new_els_data_id
736 , els_individual_tasks_tl_rec.language
737 , els_individual_tasks_tl_rec.source_lang
738 , els_individual_tasks_tl_rec.description
739 , FND_GLOBAL.USER_ID
740 , FND_GLOBAL.LOGIN_ID
741 , FND_GLOBAL.USER_ID
742 , SYSDATE
743 , SYSDATE);
744 END LOOP;
745 END LOOP;
746
747 IF g_debug=1 THEN
748 debug('After creating History Records' , 'COPY_ACTUAL_TIMINGS');
749 END IF;
750
751 /* End of fix for bug # 5520074 */
752
753 --update the expected value fields with the actual values.
754 UPDATE wms_els_individual_tasks_b
755 SET
756 Expected_Travel_Time = NVL(Actual_Travel_time,Expected_Travel_Time),
757 Expected_Txn_Time = NVL(Actual_Txn_Time,Expected_Txn_Time),
758 Expected_Idle_Time = NVL(Actual_Idle_Time,Expected_Idle_Time),
759 NUM_TRX_MATCHED = NULL, -- Added for bug # 5520074
760 LAST_UPDATED_BY = FND_GLOBAL.USER_ID, -- Added for bug # 5520074
761 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, -- Added for bug # 5520074
762 LAST_UPDATE_DATE = SYSDATE -- Added for bug # 5520074
763 WHERE
764 organization_id= p_org_id
765 AND history_flag IS NULL;
766
767
768 l_records_updated := SQL%ROWCOUNT;
769
770 IF g_debug=1 THEN
771 debug( 'The process of copying actual timings is a success for individula and manual data'
772 ,'COPY_ACTUAL_TIMINGS');
773 debug( 'The number of setup records where expectyed timing has been copied are '|| l_records_updated
774 ,'COPY_ACTUAL_TIMINGS');
775 END IF;
776
777 EXCEPTION
778 WHEN OTHERS THEN
779
780 IF g_debug=1 THEN
781 debug('Error in copying timings for Individual and manual tasks','CALCULATE_ACTUAL_TIMINGS');
782 END IF;
783
784 l_error := l_error + 1 ;
785 END;
786
787 --reinitialize this to 0 for getting the rowcount for next update statement
788
789 l_records_updated := 0;
790
791
792 BEGIN
793 --update the expected value fields with the actual values.
794 UPDATE wms_els_grouped_tasks_b
795 SET
796 Expected_Travel_Time = NVL(Actual_Travel_time,Expected_Travel_Time)
797 WHERE
798 organization_id= p_org_id;
799
800 l_records_updated := SQL%ROWCOUNT;
801
802 IF g_debug=1 THEN
803 debug( 'The process of copying actual timings is a success for grouped data '
804 ,'COPY_ACTUAL_TIMINGS');
805 debug( 'The number of setup records where expected timing has been copied are '|| l_records_updated
806 ,'COPY_ACTUAL_TIMINGS');
807 END IF;
808
809 EXCEPTION
810 WHEN OTHERS THEN
811
812 IF g_debug=1 THEN
813 debug(' Error in copying timings for Grpouped tasks','CALCULATE_ACTUAL_TIMINGS');
814 END IF;
815
816 l_error := l_error+ 1 ;
817 END;
818
819
820 IF l_error = 0 THEN
821 retcode := 1;
822 fnd_message.set_name('WMS', 'WMS_LMS_COPY_TIMINGS_SUCCESS');
823 l_message := fnd_message.get;
824 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',l_message);
825
826 ELSIF l_error =2 THEN
827 retcode := 2;
828 fnd_message.set_name('WMS', 'WMS_LMS_COPY_TIMINGS_ERROR');
829 l_message := fnd_message.get;
830 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
831
832 ELSE
833 retcode := 3;
834 fnd_message.set_name('WMS', 'WMS_LMS_COPY_TIMINGS_WARN');
835 l_message := fnd_message.get;
836 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('WARN',l_message);
837 END IF;
838
839
840
841 EXCEPTION
842 WHEN OTHERS THEN
846 END IF;
843 IF g_debug=1 THEN
844 debug( 'Unexpected Exception occured while updating actuial timings'
845 ,'COPY_ACTUAL_TIMINGS');
847
848 retcode := 2;
849 fnd_message.set_name('WMS', 'WMS_LMS_COPY_TIMINGS_ERROR');
850 l_message := fnd_message.get;
851 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
852
853 END COPY_ACTUAL_TIMINGS;
854
855
856
857 PROCEDURE CALCULATE_ACTUAL_TIMINGS ( errbuf OUT NOCOPY VARCHAR2
858 , retcode OUT NOCOPY NUMBER
859 , p_org_id IN NUMBER
860 ) IS
861
862
863 CURSOR C_ELS_DATA_ID (l_organization_id NUMBER) IS
864 SELECT els_data_id FROM WMS_ELS_INDIVIDUAL_TASKS_B WHERE
865 organization_id=l_organization_id
866 and history_flag IS NULL;
867
868 -- Ignore history rows
869
870 l_date DATE;
871
872 TYPE els_id_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
873
874 l_els_data_id_tab els_id_tab;
875
876 l_include_bucket BOOLEAN;
877
878 l_time_frame_average_id NUMBER;
879
880 l_records_updated NUMBER;
881
882 l_ret BOOLEAN;
883 l_message VARCHAR2(250);
884
885 BEGIN
886
887 l_date:= NULL;
888 l_include_bucket := FALSE;
889 l_time_frame_average_id := NULL;
890
891
892
893 IF g_debug=1 THEN
894 debug('The value of organization Id '|| p_org_id,'CALCULATE_ACTUAL_TIMINGS');
895 END IF;
896
897
898 IF (p_org_id IS NOT NULL) THEN
899
900 BEGIN
901 --Get the global setup variable
902
903 SELECT time_frame_average_id INTO l_time_frame_average_id
904 FROM WMS_ELS_PARAMETERS WHERE organization_id = p_org_id;
905
906 IF g_debug=1 THEN
907 debug('The value of time frame id '|| l_time_frame_average_id,'CALCULATE_ACTUAL_TIMINGS');
908 END IF;
909
910 EXCEPTION
911 WHEN NO_DATA_FOUND THEN
912
913 IF g_debug=1 THEN
914 debug('The value of time frame id is NULL Cant Proceed','CALCULATE_ACTUAL_TIMINGS');
915 END IF;
916
917 retcode := 2;
918 fnd_message.set_name('WMS', 'WMS_LMS_TIME_FRAME_NULL');
919 l_message := fnd_message.get;
920 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
921 RETURN;
922 END;
923
924 -- Set the local variable l_from_date depending on the
925 -- Time_frame_average_id from WMS_ELS_GLOBAL_DATA
926
927 IF l_time_frame_average_id =1 THEN
928 -- If the time frame in global setup data is 30 days
929 l_date:= (trunc(SYSDATE) - 30);
930
931 IF g_debug=1 THEN
932 debug( 'Time frame is 30 days.The value of l_date ' || to_CHAR(l_date,'DD-MON-YYYY HH24:MI:SS')
933 ,'CALCULATE_ACTUAL_TIMINGS');
934 END IF;
935
936 ELSIF l_time_frame_average_id =2 THEN
937 -- If the time frame in global setup data is 6 months
938 l_date:= ADD_MONTHS(trunc(SYSDATE),-6);
939
940 IF g_debug=1 THEN
941 debug( 'Time frame is 6 months.The value of l_date ' || to_CHAR(l_date,'DD-MON-YYYY HH24:MI:SS')
942 ,'CALCULATE_ACTUAL_TIMINGS');
943 END IF;
944
945 END IF;
946
947
948 OPEN C_ELS_DATA_ID (p_org_id);
949 --Open the cursor
950 LOOP
951
952 FETCH C_ELS_DATA_ID
953 BULK COLLECT
954 INTO l_els_data_id_tab;
955
956 EXIT WHEN C_ELS_DATA_ID%NOTFOUND;
957
958 END LOOP;
959
960 l_records_updated := 0;
961 --Exit till the records exist and bulk fetch to a table type
962
963
964 FORALL i IN l_els_data_id_tab.first .. l_els_data_id_tab.last
965 --Bulk update the els data table with actual timings.
966 --Bulk update is used here for performance enhancement resons.
967
968 UPDATE wms_els_individual_tasks_b SET
969 actual_travel_time=(
970 SELECT AVG(travel_time) FROM WMS_ELS_TRX_SRC
971 WHERE els_data_id=l_els_data_id_tab(i)
972 AND NVL(Transaction_Date,SYSDATE) >= NVL(l_date,SYSDATE)
973 AND (
974 (l_time_frame_average_id =3)
975 OR ((l_time_frame_average_id <> 3 ) AND (TRANSACTION_DATE IS NOT NULL))
976 )
977 ),
978 actual_txn_time =(
979 SELECT AVG(Transaction_time) FROM WMS_ELS_TRX_SRC
980 WHERE els_data_id=l_els_data_id_tab(i)
981 AND NVL(Transaction_Date,SYSDATE) >= NVL(l_date,SYSDATE)
982 AND (
983 (l_time_frame_average_id =3)
984 OR ((l_time_frame_average_id <> 3 ) AND (TRANSACTION_DATE IS NOT NULL))
985 )
986 ),
987 actual_idle_time=(
988 SELECT AVG(idle_time) FROM WMS_ELS_TRX_SRC
989 WHERE els_data_id=l_els_data_id_tab(i)
990 AND NVL(Transaction_Date,SYSDATE) >= NVL(l_date,SYSDATE)
991 AND (
992 (l_time_frame_average_id =3)
993 OR ((l_time_frame_average_id <> 3 ) AND (TRANSACTION_DATE IS NOT NULL))
994 )
995 )
996 WHERE els_data_id = l_els_data_id_tab(i);
997
998 l_records_updated := SQL%ROWCOUNT;
999
1000 IF g_debug=1 THEN
1004 CLOSE C_ELS_DATA_ID;
1001 debug('Number of records updated'||l_records_updated ,'CALCULATE_ACTUAL_TIMINGS');
1002 END IF;
1003
1005
1006 IF g_debug=1 THEN
1007 debug('Calculate actual timings is a success','CALCULATE_ACTUAL_TIMINGS');
1008 END IF;
1009
1010 retcode := 1;
1011 fnd_message.set_name('WMS', 'WMS_LMS_CAL_ACTUALS_SUCCESS');
1012 l_message := fnd_message.get;
1013 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',l_message);
1014
1015 ELSE -- if org id is not passed
1016
1017 IF g_debug=1 THEN
1018 debug('No org Id passed so error out','CALCULATE_ACTUAL_TIMINGS');
1019 END IF;
1020
1021 retcode := 2;
1022 fnd_message.set_name('WMS', 'WMS_LMS_REQ_PARAM_NULL');
1023 l_message := fnd_message.get;
1024 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
1025
1026 END IF;
1027
1028 EXCEPTION
1029 --Handle exceptions.
1030
1031 WHEN OTHERS THEN
1032
1033 IF g_debug=1 THEN
1034 debug('Exception has occured','CALCULATE_ACTUAL_TIMINGS');
1035 END IF;
1036
1037 retcode := 2;
1038 fnd_message.set_name('WMS', 'WMS_LMS_CAL_ACTUALS_ERROR');
1039 l_message := fnd_message.get;
1040 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
1041
1042 --close the cursor if it is open
1043
1044 IF C_ELS_DATA_ID%isopen THEN
1045 CLOSE C_ELS_DATA_ID;
1046 END IF;
1047
1048 END CALCULATE_ACTUAL_TIMINGS;
1049
1050
1051 FUNCTION unprocessed_rows_remaining ( p_org_id NUMBER )
1052 RETURN NUMBER IS
1053 is_unprocessed NUMBER;
1054 BEGIN
1055
1056 select 1 into is_unprocessed from dual where exists (select 1 from wms_els_exp_resource
1057 where els_data_id IS NULL and organization_id = p_org_id
1058 );
1059 RETURN 1;
1060
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN
1063 RETURN 2;
1064 END unprocessed_rows_remaining;
1065
1066
1067 FUNCTION unprocessed_rows_remaining ( p_org_id NUMBER,
1068 p_max_id NUMBER )
1069 RETURN NUMBER IS
1070 is_unprocessed NUMBER;
1071 BEGIN
1072
1073 select 1 into is_unprocessed from dual where exists (select 1 from wms_els_trx_src
1074 where els_data_id IS NULL
1075 and organization_id = p_org_id
1076 and els_trx_src_id <= p_max_id
1077 );
1078 RETURN 1;
1079
1080 EXCEPTION
1081 WHEN NO_DATA_FOUND THEN
1082 RETURN 2;
1083 END unprocessed_rows_remaining;
1084
1085
1086 FUNCTION get_parameter_string(p_concurrent_program_id IN NUMBER,
1087 p_org_id IN NUMBER)
1088 RETURN VARCHAR2 IS
1089
1090 l_message_text VARCHAR2(100);
1091 l_argument2 VARCHAR2(240);
1092 l_argument3 VARCHAR2(240);
1093 l_argument4 VARCHAR2(240);
1094 l_argument5 VARCHAR2(240);
1095 l_meaning VARCHAR2(240);
1096
1097 BEGIN
1098 l_message_text := NULL;
1099
1100 IF g_debug=1 THEN
1101 debug('Organization Id is '|| p_org_id,'get_parameter_string');
1102 debug('p_concurrent_program_id is '|| p_concurrent_program_id,'get_parameter_string');
1103 END IF;
1104
1105 --argument 2 = data period unit
1106 --argument 3 = data period value
1107 --argument 4 = number of hrs per day
1108 --argument 5 = utilization rate
1109
1110 select ARGUMENT2,ARGUMENT3,
1111 ARGUMENT4,ARGUMENT5
1112 into l_argument2,l_argument3,
1113 l_argument4,l_argument5
1114 from fnd_concurrent_requests where argument1 = to_char(p_org_id)
1115 and request_id = (select Max(Request_ID)
1116 From Fnd_Concurrent_Requests
1117 Where Concurrent_Program_ID = p_concurrent_program_id
1118 AND PHASE_CODE = 'C' and argument1 = to_char(p_org_id)); -- added this condition for bug 5478746
1119
1120 IF g_debug=1 THEN
1121 debug('Argument2 = '|| l_argument2,'get_parameter_string');
1122 debug('Argument3 = '|| l_argument3,'get_parameter_string');
1123 debug('Argument4 = '|| l_argument4,'get_parameter_string');
1124 debug('Argument5 = '|| l_argument5,'get_parameter_string');
1125 END IF;
1126
1127
1128 -- Format of the message text is as follows
1129 -- Data Period= TOKEN1 TOKEN2 ,No. Of Working Hrs/day=TOKEN3, Utilization Rate=TOKEN4%
1130
1131
1132 select meaning into l_meaning from mfg_lookups where lookup_code = l_argument2
1133 and lookup_type = 'WMS_ELS_FUTURE_PERIOD';
1134
1135 IF g_debug=1 THEN
1136 debug('Data period Unit from lookup = '|| l_meaning,'get_parameter_string');
1137 END IF;
1138
1139
1140 FND_MESSAGE.SET_NAME('WMS', 'WMS_LMS_REPORT_PARAMS');
1141 FND_MESSAGE.SET_TOKEN('TOKEN1',l_argument3);
1142 FND_MESSAGE.SET_TOKEN('TOKEN2',l_meaning);
1143 FND_MESSAGE.SET_TOKEN('TOKEN3',l_argument4);
1144 FND_MESSAGE.SET_TOKEN('TOKEN4',l_argument5);
1145
1146 l_message_text := FND_MESSAGE.GET;
1147
1148 IF g_debug=1 THEN
1149 debug('Final Message text = '|| l_message_text,'get_parameter_string');
1150 END IF;
1151
1152 RETURN l_message_text;
1153
1154 EXCEPTION
1155 -- do normal EXCPETION HANDLING
1156
1157 WHEN OTHERS THEN
1158 IF g_debug=1 THEN
1162 return l_message_text;
1159 debug('In Exception Block','get_parameter_string');
1160 END IF;
1161 l_message_text := NULL;
1163
1164 END get_parameter_string;
1165
1166
1167 FUNCTION get_next_scheduled_time ( p_concurrent_program_id IN NUMBER,
1168 p_application_id IN NUMBER,
1169 p_org_id IN NUMBER
1170 )
1171 return VARCHAR2 IS
1172
1173 l_next_scheduled_date DATE;
1174 l_message VARCHAR2(100);
1175
1176 BEGIN
1177
1178 l_next_scheduled_date:= NULL;
1179
1180 select min(requested_start_date) into l_next_scheduled_date
1181 from fnd_concurrent_requests
1182 where concurrent_program_id = p_concurrent_program_id
1183 and program_application_id = p_application_id
1184 and phase_code = 'P'
1185 and STATUS_CODE IN ( 'I','Q')
1186 and argument1 = to_char(p_org_id);
1187
1188 IF ( l_next_scheduled_date IS NULL) THEN
1189 FND_MESSAGE.SET_NAME('WMS', 'WMS_LMS_NOT_SCHEDULED');
1190
1191 l_message := FND_MESSAGE.GET;
1192
1193 RETURN l_message;
1194
1195 END IF;
1196
1197 IF g_debug=1 THEN
1198 debug('Server time zone code is '||fnd_date.server_timezone_code,'get_parameter_string');
1199 debug('Client time zone code is '||fnd_date.client_timezone_code,'get_parameter_string');
1200 END IF;
1201
1202 l_next_scheduled_date := fnd_date.adjust_datetime( l_next_scheduled_date
1203 ,fnd_date.server_timezone_code
1204 ,fnd_date.client_timezone_code);
1205
1206
1207 RETURN to_char(l_next_scheduled_date ,'DD-MON-YYYY HH24:MI:SS');
1208
1209 END get_next_scheduled_time;
1210
1211
1212
1213 FUNCTION last_run_time ( p_concurrent_program_id IN NUMBER,
1214 p_org_id IN NUMBER
1215 )
1216 RETURN VARCHAR2 IS
1217 l_actual_completion_date DATE;
1218 l_message VARCHAR2(100);
1219
1220 BEGIN
1221 l_actual_completion_date := NULL;
1222
1223 IF g_debug=1 THEN
1224 debug('Organization Id is '|| p_org_id,'get_parameter_string');
1225 debug('p_concurrent_program_id is '|| p_concurrent_program_id,'get_parameter_string');
1226 END IF;
1227
1228
1229 select actual_completion_date
1230 into l_actual_completion_date
1231 from fnd_concurrent_requests where argument1 = to_char(p_org_id)
1232 and request_id = (select Max(Request_ID)
1233 From Fnd_Concurrent_Requests
1234 Where Concurrent_Program_ID = p_concurrent_program_id
1235 AND PHASE_CODE = 'C'
1236 and argument1 = to_char(p_org_id)); -- added this condition for bug 5478746
1237
1238
1239 IF g_debug=1 THEN
1240 debug('Server time zone code is '||fnd_date.server_timezone_code,'get_parameter_string');
1241 debug('Client time zone code is '||fnd_date.client_timezone_code,'get_parameter_string');
1242 END IF;
1243
1244 l_actual_completion_date := fnd_date.adjust_datetime( l_actual_completion_date
1245 ,fnd_date.server_timezone_code
1246 ,fnd_date.client_timezone_code);
1247
1248 RETURN to_char(l_actual_completion_date,'DD-MON-YYYY HH24:MI:SS');
1249
1250 EXCEPTION
1251 WHEN NO_DATA_FOUND THEN
1252
1253 FND_MESSAGE.SET_NAME('WMS', 'WMS_LMS_NOT_RUN');
1254 l_message:= FND_MESSAGE.GET;
1255 RETURN l_message;
1256
1257 END last_run_time;
1258
1259 FUNCTION last_run_time_success ( p_concurrent_program_id IN NUMBER,
1260 p_org_id IN NUMBER
1261 )
1262 RETURN VARCHAR2 IS
1263 l_actual_completion_date DATE;
1264 l_message VARCHAR2(100);
1265
1266 BEGIN
1267 l_actual_completion_date := NULL;
1268
1269 IF g_debug=1 THEN
1270 debug('Organization Id is '|| p_org_id,'get_parameter_string');
1271 debug('p_concurrent_program_id is '|| p_concurrent_program_id,'get_parameter_string');
1272 END IF;
1273
1274
1275 select actual_completion_date
1276 into l_actual_completion_date
1277 from fnd_concurrent_requests where argument1 = to_char(p_org_id)
1278 and request_id = (select Max(Request_ID)
1279 From Fnd_Concurrent_Requests
1280 Where Concurrent_Program_ID = p_concurrent_program_id
1281 AND PHASE_CODE = 'C'
1282 AND STATUS_CODE = 'C'
1283 and argument1 = to_char(p_org_id)); -- added this condition for bug 5478746
1284
1285
1286 IF g_debug=1 THEN
1287 debug('Server time zone code is '||fnd_date.server_timezone_code,'get_parameter_string');
1288 debug('Client time zone code is '||fnd_date.client_timezone_code,'get_parameter_string');
1289 END IF;
1290 l_actual_completion_date := fnd_date.adjust_datetime( l_actual_completion_date
1291 ,fnd_date.server_timezone_code
1292 ,fnd_date.client_timezone_code);
1293
1294 RETURN to_char(l_actual_completion_date,'DD-MON-YYYY HH24:MI:SS');
1295
1296 EXCEPTION
1297 WHEN NO_DATA_FOUND THEN
1298
1299 FND_MESSAGE.SET_NAME('WMS', 'WMS_LMS_NOT_RUN');
1303 END last_run_time_success;
1300 l_message:= FND_MESSAGE.GET;
1301 RETURN l_message;
1302
1304
1305
1306 FUNCTION last_run_status ( p_concurrent_program_id IN NUMBER,
1307 p_org_id IN NUMBER
1308 )
1309 RETURN VARCHAR2 IS
1310 l_last_run_status VARCHAR2(20);
1311
1312 BEGIN
1313 IF g_debug=1 THEN
1314 debug('Organization Id is '|| p_org_id,'get_parameter_string');
1315 debug('p_concurrent_program_id is '|| p_concurrent_program_id,'get_parameter_string');
1316 END IF;
1317
1318
1319 select fl.meaning
1320 into l_last_run_status
1321 from fnd_concurrent_requests fcr, fnd_lookups fl
1322 where argument1 = to_char(p_org_id)
1323 and fl.lookup_code = fcr.status_code
1324 and fl.Lookup_type = 'CP_STATUS_CODE'
1325 and request_id = (select Max(Request_ID)
1326 from Fnd_Concurrent_Requests
1327 where Concurrent_Program_ID = p_concurrent_program_id
1328 AND PHASE_CODE = 'C'
1329 and argument1 = to_char(p_org_id)); -- added this condition for bug 5478746
1330
1331 RETURN l_last_run_status;
1332
1333 EXCEPTION
1334 WHEN NO_DATA_FOUND THEN
1335
1336 FND_MESSAGE.SET_NAME('WMS', 'WMS_LMS_NOT_RUN');
1337 l_last_run_status := FND_MESSAGE.GET;
1338 RETURN l_last_run_status;
1339
1340 END last_run_status;
1341
1342
1343
1344 FUNCTION getWorkOutstanding(l_ActivityId IN NUMBER, l_ActivityDetailId IN NUMBER, l_OrgId IN NUMBER)
1345 RETURN VARCHAR2 IS
1346 CURSOR CUR_WRK_OUTSTANDING(p_ActivityId number, p_activityDetailId number, p_OrgId number) IS
1347 SELECT COUNT(*) || ' ' || NVL(DOCUMENT_TYPE, ML1.MEANING) AS WORK
1348 FROM WMS_ELS_EXP_RESOURCE, MFG_LOOKUPS ML1
1349 WHERE ORGANIZATION_ID = p_OrgId
1350 AND ACTIVITY_ID = p_ActivityId
1351 AND ACTIVITY_DETAIL_ID = p_ActivityDetailId
1352 AND ML1.LOOKUP_TYPE = 'WMS_ELS_TASKS_LOOKUP'
1353 GROUP BY DOCUMENT_TYPE, ML1.MEANING;
1354
1355 strWorkOutstanding varchar2(4000);
1356 BEGIN
1357 strWorkOutstanding := NULL;
1358 FOR l_wrk_outstanding in CUR_WRK_OUTSTANDING(l_ActivityId, l_ActivityDetailId, l_OrgId)
1359 LOOP
1360 strWorkOutstanding := strWorkOutstanding || l_wrk_outstanding.work || ', ';
1361 END LOOP;
1362 IF(strWorkOutstanding is not null) THEN
1363 strWorkOutstanding := substr(strWorkOutstanding,1,length(strWorkOutstanding)-2);
1364 END IF;
1365 RETURN strWorkOutstanding;
1366 EXCEPTION
1367 WHEN OTHERS
1368 THEN
1369 RETURN '';
1370 END getWorkOutstanding;
1371
1372 FUNCTION getWorkOutstandingGraphData(l_ActivityId NUMBER, l_ActivityDetailId NUMBER, l_OrgId NUMBER)
1373 RETURN NUMBER
1374 IS
1375 CURSOR CUR_WRK_OUTSTANDING(p_ActivityId number, p_activityDetailId number, p_OrgId number) IS
1376 SELECT COUNT(*) AS WORK
1377 FROM WMS_ELS_EXP_RESOURCE, MFG_LOOKUPS ML1
1378 WHERE ORGANIZATION_ID = p_OrgId
1379 AND ACTIVITY_ID = p_ActivityId
1380 AND ACTIVITY_DETAIL_ID = p_ActivityDetailId
1381 AND ML1.LOOKUP_TYPE = 'WMS_ELS_TASKS_LOOKUP';
1382 workValue NUMBER := NULL;
1383 BEGIN
1384 FOR l_wrk_value in CUR_WRK_OUTSTANDING(l_ActivityId, l_ActivityDetailId, l_OrgId)
1385 LOOP
1386 workValue := workValue || l_wrk_value.work;
1387 END LOOP;
1388 RETURN workValue;
1389 EXCEPTION
1390 WHEN OTHERS
1391 THEN
1392 RETURN -1;
1393 END getWorkOutstandingGraphData;
1394
1395
1396 FUNCTION getratingfrompoints (p_points NUMBER)
1397 RETURN VARCHAR2
1398 IS
1399 l_points NUMBER;
1400 rating VARCHAR2 (20) := '';
1401 BEGIN
1402 l_points := p_points;
1403 IF(p_points = 0) THEN
1404 l_points := 4;
1405 END IF;
1406 SELECT meaning
1407 INTO rating
1408 FROM mfg_lookups
1409 WHERE lookup_type = 'WMS_LABOR_RATINGS' AND lookup_code = l_points;
1410
1411 RETURN rating;
1412 EXCEPTION
1413 WHEN OTHERS
1414 THEN
1415 RETURN '';
1416 END getratingfrompoints;
1417
1418
1419 PROCEDURE GET_MAX_SEQ_NUMBERS ( x_seq_num_ind_and_sys_directed OUT NOCOPY NUMBER
1420 , x_seq_num_man_and_usr_directed OUT NOCOPY NUMBER
1421 , x_seq_num_man_and_sys_directed OUT NOCOPY NUMBER
1422 , x_seq_num_grouped OUT NOCOPY NUMBER
1423 , p_org_id IN NUMBER
1424 ) IS
1425
1426 BEGIN
1427
1428
1429 BEGIN
1430 select round(MAX(sequence_number),-1) into x_seq_num_ind_and_sys_directed
1431 from WMS_ELS_INDIVIDUAL_TASKS_B
1432 where group_id= 3
1433 AND history_flag IS null
1434 AND organization_id = p_org_id;-- individual group
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437 x_seq_num_ind_and_sys_directed := 0;
1438 END;
1439
1440 BEGIN
1441 select round(MAX(sequence_number),-1) into x_seq_num_man_and_sys_directed
1442 from WMS_ELS_INDIVIDUAL_TASKS_B
1443 where group_id= 2
1447 WHEN OTHERS THEN
1444 AND history_flag IS null
1445 AND organization_id = p_org_id; -- manual group
1446 EXCEPTION
1448 x_seq_num_man_and_sys_directed := 0;
1449 END;
1450
1451 BEGIN
1452 select round(MAX(sequence_number),-1) into x_seq_num_man_and_usr_directed
1453 from WMS_ELS_INDIVIDUAL_TASKS_B
1454 where group_id= 1
1455 AND history_flag IS null
1456 AND organization_id = p_org_id; -- manual group
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459 x_seq_num_man_and_usr_directed := 0;
1460 END;
1461
1462 BEGIN
1463 select round(MAX(sequence_number),-1) into x_seq_num_grouped
1464 from WMS_ELS_GROUPED_TASKS_B
1465 where organization_id = p_org_id; -- grouped tasks
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468 x_seq_num_grouped := 0;
1469 END;
1470
1471 -- If the Table Doesnt contain any rows then the sequence numbers will be null. In this case, set them to 0.
1472 IF x_seq_num_ind_and_sys_directed IS NULL THEN
1473 x_seq_num_ind_and_sys_directed := 0;
1474 END IF;
1475
1476 IF x_seq_num_man_and_sys_directed IS NULL THEN
1477 x_seq_num_man_and_sys_directed := 0;
1478 END IF;
1479
1480 IF x_seq_num_man_and_usr_directed IS NULL THEN
1481 x_seq_num_man_and_usr_directed := 0;
1482 END IF;
1483
1484 IF x_seq_num_grouped IS NULL THEN
1485 x_seq_num_grouped := 0;
1486 END IF;
1487
1488 END GET_MAX_SEQ_NUMBERS;
1489
1490 PROCEDURE STANDARDIZE_LINES(
1491 X_NUM_LINES_INSERTED_TASKS OUT NOCOPY NUMBER
1492 , X_NUM_LINES_INSERTED_GROUP OUT NOCOPY NUMBER
1493 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
1494 , X_MSG_NAME OUT NOCOPY VARCHAR2
1495 , P_COPY_ID IN VARCHAR2
1496 , P_COPY_ANALYSIS IN VARCHAR2
1497 , P_ORG_ID IN NUMBER
1498 ) IS
1499
1500 l_seq_num_ind_and_sys_directed NUMBER;
1501 l_seq_num_man_and_usr_directed NUMBER;
1502 l_seq_num_man_and_sys_directed NUMBER;
1503 l_ind_or_man_tasks_inserted NUMBER;
1504 l_seq_num_grouped NUMBER;
1505 l_comma_start_pos NUMBER;
1506 l_comma_end_pos NUMBER;
1507 l_els_trx_src_id NUMBER;
1508 l_analysis_id NUMBER;
1509 i NUMBER;
1510 l_return_staus VARCHAR2(1);
1511
1512 l_copy_id VARCHAR2(4000);
1513 l_copy_analysis VARCHAR2(4000);
1514
1515 TYPE els_src_id_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1516 TYPE analysis_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1517
1518 l_els_trx_src_id_tab els_src_id_tab;
1519 l_analysis_tab analysis_tab;
1520 l_grp_id NUMBER; --Added for bug 5194353
1521
1522 BEGIN
1523
1524 -- set the status to success initially
1525 x_return_status := fnd_api.g_ret_sts_success;
1526
1527
1528 SAVEPOINT standardize_lines;
1529
1530
1531 IF g_debug=1 THEN
1532 debug('The value of organization Id '|| p_org_id,'STANDARDIZE_LINES');
1533 debug('The p_copy_id '|| p_copy_id,'STANDARDIZE_LINES');
1534 END IF;
1535
1536 -- Check if all required parameters are passed
1537
1538 IF P_COPY_ID IS NULL THEN
1539 -- FND_MESSAGE.SET_NAME('WMS','WMS_NO_LINES_TO_COPY');
1540 -- FND_MSG_PUB.ADD;
1541 X_MSG_NAME := 'WMS_NO_LINES_SELECTED';
1542 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1543 RETURN ;
1544 END IF;
1545
1546 IF P_COPY_ANALYSIS IS NULL THEN
1547 -- FND_MESSAGE.SET_NAME('WMS','WMS_NO_LINES_TO_COPY');
1548 -- FND_MSG_PUB.ADD;
1549 X_MSG_NAME := 'WMS_NO_LINES_SELECTED';
1550 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1551 RETURN ;
1552 END IF;
1553
1554 -- Proceed if all required parameters are passed.
1555 -- get the max of the sequence numbers (as per HLD)in the
1556 -- setup table(WMS_ELS_INDIVIDUAL_TASKS_B)
1557 -- for both the Individual and the manual group.
1558
1559 IF (P_ORG_ID IS NOT NULL) THEN
1560
1561 GET_MAX_SEQ_NUMBERS ( x_seq_num_ind_and_sys_directed => l_seq_num_ind_and_sys_directed
1562 , x_seq_num_man_and_usr_directed => l_seq_num_man_and_usr_directed
1563 , x_seq_num_man_and_sys_directed => l_seq_num_man_and_sys_directed
1564 , x_seq_num_grouped => l_seq_num_grouped
1565 , p_org_id => p_org_id
1566 );
1567
1568 IF g_debug=1 THEN
1569 debug('The value of new seq for individual and system directed tasks '|| l_seq_num_ind_and_sys_directed,'STANDARDIZE_LINES');
1570 debug('The value of new seq for manual and system directed tasks '|| l_seq_num_man_and_sys_directed,'STANDARDIZE_LINES');
1571 debug('The value of new seq for manual and user directed tasks '|| l_seq_num_man_and_usr_directed,'STANDARDIZE_LINES');
1572 debug('The value of new seq for grouped tasks '|| l_seq_num_grouped,'STANDARDIZE_LINES');
1573 END IF;
1574
1575 ELSE
1576
1580
1577 IF g_debug=1 THEN
1578 debug('No Organization Specified','STANDARDIZE_LINES');
1579 END IF;
1581 -- FND_MESSAGE.SET_NAME('WMS','WMS_LMS_NO_ORG');
1582 -- FND_MSG_PUB.ADD;
1583 X_MSG_NAME := 'WMS_LMS_NO_ORG';
1584 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1585 RETURN ;
1586
1587 END IF;
1588
1589 IF g_debug=1 THEN
1590 debug('Before inserting into individual tasks table','STANDARDIZE_LINES');
1591 END IF;
1592
1593
1594
1595 ----------------------------------------------------------------------------
1596 --
1597 -- Convert the Comma Separated Strings of Id's and Analyses into two arrays.
1598 --
1599 ----------------------------------------------------------------------------
1600
1601 l_copy_id := p_copy_id;
1602 l_comma_start_pos := 1;
1603 i := 0;
1604
1605 while(l_copy_id is not null)
1606 loop
1607 BEGIN
1608 l_comma_end_pos := instr(l_copy_id,',');
1609 if(l_comma_end_pos = 0) then
1610 l_els_trx_src_id_tab(i) := to_number(l_copy_id);
1611 -- dbms_output.put_line(l_els_trx_src_id_tab(i));
1612 exit;
1613 else
1614 l_els_trx_src_id_tab(i) := to_number(substr(l_copy_id,l_comma_start_pos,l_comma_end_pos-1));
1615 -- dbms_output.put_line(l_els_trx_src_id_tab(i));
1616 end if;
1617 l_copy_id := substr(l_copy_id,l_comma_end_pos+1);
1618 -- l_comma_start_pos := l_comma_end_pos + 1; Commented for bug 5194353
1619 i := i + 1;
1620 EXCEPTION
1621 when others then
1622 l_copy_id := null;
1623 exit;
1624 END;
1625 end loop;
1626
1627 l_copy_analysis := p_copy_analysis;
1628 l_comma_start_pos := 1;
1629 i := 0;
1630 while(l_copy_analysis is not null)
1631 loop
1632 BEGIN
1633 l_comma_end_pos := instr(l_copy_analysis,',');
1634 if(l_comma_end_pos = 0) then
1635 l_analysis_tab(i) := to_number(l_copy_analysis);
1636 -- dbms_output.put_line(l_analysis_tab(i));
1637 exit;
1638 else
1639 l_analysis_tab(i) := to_number(substr(l_copy_analysis,l_comma_start_pos,l_comma_end_pos-1));
1640 -- dbms_output.put_line(l_analysis_tab(i));
1641 end if;
1642 l_copy_analysis := substr(l_copy_analysis,l_comma_end_pos+1);
1643 -- l_comma_start_pos := l_comma_end_pos + 1; Commented for bug 5194353
1644 i := i + 1;
1645 EXCEPTION
1646 when others then
1647 l_copy_analysis := null;
1648 exit;
1649 END;
1650 end loop;
1651
1652 ----------------------------------------------------------------------------
1653 --
1654 -- Inserting into individual or manual tasks table.
1655 --
1656 ----------------------------------------------------------------------------
1657
1658
1659 l_ind_or_man_tasks_inserted := 0;
1660 for i in l_els_trx_src_id_tab.first .. l_els_trx_src_id_tab.last
1661 loop
1662 l_els_trx_src_id := l_els_trx_src_id_tab(i);
1663 l_analysis_id := l_analysis_tab(i);
1664 BEGIN
1665 -- dbms_output.put_line(l_els_trx_src_id || ' els_trx_src_id value');
1666 if( l_els_trx_src_id <> 0 ) then
1667
1668 INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B
1669 (
1670 els_data_id
1671 , organization_id
1672 , sequence_number
1673 , analysis_id
1674 , activity_id
1675 , activity_detail_id
1676 , operation_id
1677 , equipment_id
1678 , source_zone_id
1679 , source_subinventory
1680 , destination_zone_id
1681 , destination_subinventory
1682 , labor_txn_source_id
1683 , transaction_uom
1684 , from_quantity
1685 , to_quantity
1686 , item_category_id
1687 , operation_plan_id
1688 , group_id
1689 , task_type_id
1690 , task_method_id
1691 , expected_travel_time
1692 , expected_txn_time
1693 , expected_idle_time
1694 , actual_travel_time
1695 , actual_txn_time
1696 , actual_idle_time
1697 , last_updated_by
1698 , last_update_date
1699 , last_update_login
1700 , created_by
1701 , creation_date
1702 )
1703 SELECT
1704 wms_els_individual_tasks_s.nextval
1705 , organization_id
1706 , decode(group_id,1,nvl(l_seq_num_man_and_usr_directed,0)+10,
1707 2,nvl(l_seq_num_man_and_sys_directed,0) + 10,
1708 3,nvl(l_seq_num_ind_and_sys_directed,0) + 10
1709 )
1710 , l_analysis_id
1711 , activity_id
1712 , activity_detail_id
1713 , operation_id
1714 , equipment_id
1715 , source_zone_id
1716 , source_subinventory
1717 , destination_zone_id
1718 , destination_subinventory
1719 , labor_txn_source_id
1720 , transaction_uom
1721 , quantity
1722 , quantity
1723 , item_category_id
1724 , operation_plan_id
1725 , group_id
1726 , task_type_id
1727 , task_method_id
1728 , travel_and_idle_time
1729 , transaction_time
1730 , NULL
1731 , travel_and_idle_time
1732 , transaction_time
1733 , idle_time
1734 , FND_GLOBAL.USER_ID
1735 , sysdate
1736 , FND_GLOBAL.LOGIN_ID
1737 , FND_GLOBAL.USER_ID
1738 , sysdate
1739 FROM WMS_ELS_TRX_SRC
1743
1740 WHERE ELS_DATA_ID IS NULL
1741 AND ELS_TRX_SRC_ID = l_els_trx_src_id
1742 AND UNATTRIBUTED_FLAG = 1 ;
1744
1745 /* Added the following select statement for bug 5194353 */
1746
1747 SELECT nvl(group_id,0) into l_grp_id
1748 FROM WMS_ELS_TRX_SRC
1749 WHERE ELS_TRX_SRC_ID = l_els_trx_src_id;
1750
1751 if(l_grp_id = 1) then
1752 l_seq_num_man_and_usr_directed := nvl(l_seq_num_man_and_usr_directed,0) +10;
1753 elsif (l_grp_id = 2) then
1754 l_seq_num_man_and_sys_directed := nvl(l_seq_num_man_and_sys_directed,0) +10;
1755 elsif (l_grp_id = 3) then
1756 l_seq_num_ind_and_sys_directed := nvl(l_seq_num_ind_and_sys_directed,0) +10;
1757 end if;
1758
1759 l_ind_or_man_tasks_inserted := l_ind_or_man_tasks_inserted + 1;
1760
1761
1762 -- X_NUM_LINES_INSERTED_TASKS := X_NUM_LINES_INSERTED_TASKS + SQL%ROWCOUNT;
1763
1764 end if;
1765
1766 X_NUM_LINES_INSERTED_TASKS := l_ind_or_man_tasks_inserted;
1767 EXCEPTION
1768 when others then
1769 l_ind_or_man_tasks_inserted := l_ind_or_man_tasks_inserted;
1770 ROLLBACK TO standardize_lines;
1771
1772 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1773 X_MSG_NAME := 'WMS_LMS_LINES_UPDATE_ERROR';
1774 X_NUM_LINES_INSERTED_TASKS := 0;
1775 X_NUM_LINES_INSERTED_GROUP := 0;
1776
1777 IF g_debug=1 THEN
1778 debug('Error Occured in Individual Task Insertion ' || SQLERRM ,'STANDARDIZE_LINES');
1779 END IF;
1780
1781 return;
1782 END;
1783
1784 end loop;
1785
1786
1787
1788
1789 --We dont know how many new rows are created in the Base table. Also we dont have the els_Data_id's of all those rows.
1790 --So we have the inner select Query.
1791
1792 INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_TL
1793 (
1794 els_data_id,
1795 language,
1796 source_lang,
1797 description,
1798 last_updated_by,
1799 last_update_login,
1800 created_by,
1801 creation_date,
1802 last_update_date
1803 )
1804 SELECT b.els_data_id
1805 , l.language_code
1806 , userenv('lang') --Have to change this line. Need to give a Source Language.
1807 , null
1808 , b.last_updated_by
1809 , b.last_update_login
1810 , b.created_by
1811 , b.creation_date
1812 , b.last_update_date
1813 FROM WMS_ELS_INDIVIDUAL_TASKS_B b,
1814 FND_LANGUAGES L
1815 WHERE els_data_id NOT IN (
1816 SELECT DISTINCT els_data_id
1817 FROM wms_els_individual_tasks_tl
1818 )
1819 AND L.INSTALLED_FLAG in ('I', 'B');
1820
1821
1822
1823 --X_NUM_LINES_INSERTED_TASKS := SQL%ROWCOUNT;
1824
1825
1826 IF g_debug=1 THEN
1827 debug('Num lines inserted into WMS_ELS_INDIVIDUAL_TASKS_B '||X_NUM_LINES_INSERTED_TASKS ,'STANDARDIZE_LINES');
1828 debug('Before inserting into grouped tasks table','STANDARDIZE_LINES');
1829 END IF;
1830
1831
1832
1833 ----------------------------------------------------------------------------
1834 --
1835 -- Inserting into Grouped tasks table.
1836 --
1837 ----------------------------------------------------------------------------
1838
1839 --l_query1 :=
1840 EXECUTE IMMEDIATE
1841 'INSERT INTO WMS_ELS_GROUPED_TASKS_B' ||
1842 '(' ||
1843 ' Els_Group_Id' ||
1844 ' , Organization_id' ||
1845 ' , Sequence_Number' ||
1846 ' , activity_id' ||
1847 ' , activity_detail_id' ||
1848 ' , operation_id' ||
1849 ' , labor_txn_source_id' ||
1850 ' , source_zone_id' ||
1851 ' , source_subinventory' ||
1852 ' , destination_zone_id' ||
1853 ' , destination_subinventory' ||
1854 ' , task_method_id' ||
1855 ' , task_range_from' ||
1856 ' , task_range_to' ||
1857 ' , expected_travel_time' ||
1858 ' , actual_travel_time' ||
1859 ' , last_updated_by' ||
1860 ' , last_update_date' ||
1861 ' , last_update_login' ||
1862 ' , created_by' ||
1863 ' , creation_date' ||
1864 ')' ||
1865 'SELECT wms_els_grouped_tasks_s.NEXTVAL' ||
1866 ' , organization_id' ||
1867 ' , (nvl('||l_seq_num_grouped||',0) + (10*ROWNUM)) sequence_number' ||
1868 ' , activity_id' ||
1869 ' , activity_detail_id' ||
1870 ' , operation_id' ||
1871 ' , labor_txn_source_id ' ||
1872 ' , source_zone_id' ||
1873 ' , source_subinventory' ||
1874 ' , destination_zone_id' ||
1875 ' , destination_subinventory' ||
1876 ' , task_method_id' ||
1877 ' , task_range_from' ||
1878 ' , task_range_to' ||
1879 ' , exp_travel_time' ||
1880 ' , act_travel_Time' ||
1881 ' , FND_GLOBAL.USER_ID last_updated_by ' ||
1882 ' , sysdate last_update_date' ||
1883 ' , FND_GLOBAL.LOGIN_ID last_update_login' ||
1884 ' , FND_GLOBAL.USER_ID created_by' ||
1885 ' , sysdate creation_date' ||
1886 ' FROM ' ||
1887 '( select organization_id' ||
1888 ' , activity_id' ||
1889 ' , activity_detail_id' ||
1890 ' , operation_id' ||
1891 ' , source_zone_id' ||
1892 ' , source_subinventory' ||
1893 ' , destination_zone_id' ||
1894 ' , destination_subinventory' ||
1895 ' , labor_txn_source_id' ||
1899 ' , sum(travel_and_idle_time) exp_travel_time' ||
1896 ' , task_method_id' ||
1897 ' , count(*) task_range_from' ||
1898 ' , count(*) task_range_to' ||
1900 ' , sum(travel_and_idle_time) act_travel_Time' ||
1901 ' FROM wms_els_trx_src' ||
1902 ' where els_data_id is null' ||
1903 ' and organization_id = '|| p_org_id ||
1904 ' and Els_Trx_Src_Id IN (' || P_COPY_ID || ') '||
1905 ' and unattributed_flag = 1 '||
1906 ' and grouped_Task_identifier IS NOT NULL'||
1907 ' group by grouped_Task_identifier' ||
1908 ' , organization_id' ||
1909 ' , activity_id' ||
1910 ' , activity_detail_id' ||
1911 ' , operation_id' ||
1912 ' , source_zone_id' ||
1913 ' , source_subinventory' ||
1914 ' , destination_zone_id' ||
1915 ' , destination_subinventory' ||
1916 ' , labor_txn_source_id' ||
1917 ' , task_method_id' ||
1918 ' )';
1919
1920
1921
1922
1923
1924 X_NUM_LINES_INSERTED_GROUP := SQL%ROWCOUNT;
1925
1926 IF g_debug=1 THEN
1927 debug('Num lines inserted in groups table'||X_NUM_LINES_INSERTED_GROUP ,'STANDARDIZE_LINES');
1928 END IF;
1929
1930 INSERT INTO WMS_ELS_GROUPED_TASKS_TL
1931 (
1932 els_group_id,
1933 language,
1934 source_lang,
1935 description,
1936 last_updated_by,
1937 last_update_login,
1938 created_by,
1939 creation_date,
1940 last_update_date
1941 )
1942 SELECT b.els_group_id,
1943 l.language_code,
1944 userenv('lang'), --Have to change this line. Need to give a Source Language.
1945 null,
1946 b.last_updated_by,
1947 b.last_update_login,
1948 b.created_by,
1949 b.creation_date,
1950 b.last_update_date
1951 FROM WMS_ELS_GROUPED_TASKS_B b,
1952 FND_LANGUAGES L
1953 WHERE els_group_id NOT IN (
1954 SELECT DISTINCT els_group_id
1955 FROM wms_els_grouped_tasks_tl
1956 )
1957 AND L.INSTALLED_FLAG in ('I', 'B');
1958
1959
1960 --Completed entering all the values. Now have to change the unattributed_lines flag of all are selected rows.
1961 EXECUTE IMMEDIATE
1962 'UPDATE wms_els_trx_src ' ||
1963 'SET unattributed_flag = null ' ||
1964 'WHERE Els_Trx_Src_Id IN (' || P_COPY_ID || ')';
1965
1966 EXCEPTION
1967 WHEN OTHERS THEN
1968
1969 IF g_debug=1 THEN
1970 debug('In Exception No lines inserted'||SQLERRM ,'STANDARDIZE_LINES');
1971 END IF;
1972 ROLLBACK TO standardize_lines;
1973
1974 -- return the appropriate status
1975 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
1976 X_MSG_NAME := 'WMS_LMS_LINES_UPDATE_ERROR';
1977 X_NUM_LINES_INSERTED_TASKS := 0;
1978 X_NUM_LINES_INSERTED_GROUP := 0;
1979
1980 --dbms_output.put_line(sqlerrm);
1981 IF g_debug=1 THEN
1982 debug('Error Occured in Standardize Lines function ' || SQLERRM ,'STANDARDIZE_LINES');
1983 END IF;
1984
1985
1986 END STANDARDIZE_LINES;
1987
1988
1989 PROCEDURE STANDARDIZE_LINES_CP(
1990 ERRBUF OUT NOCOPY VARCHAR2
1991 , RETCODE OUT NOCOPY NUMBER
1992 , P_ORG_ID IN NUMBER
1993 , P_ANALYSIS_TYPE IN NUMBER
1994 , P_ACTIVITY_ID IN NUMBER
1995 , P_ACTIVITY_DETAIL_ID IN NUMBER
1996 , P_OPERATION_ID IN NUMBER
1997 , P_FROM_DATE IN VARCHAR2
1998 , P_TO_DATE IN VARCHAR2
1999 )
2000 IS
2001 l_seq_num_ind_and_sys_directed NUMBER;
2002 l_seq_num_man_and_usr_directed NUMBER;
2003 l_seq_num_man_and_sys_directed NUMBER;
2004 l_seq_num_grouped NUMBER;
2005 l_where_clause VARCHAR2(1000);
2006 l_num_lines_inserted NUMBER;
2007 l_num_sql_failed NUMBER;
2008 l_which_group_failed VARCHAR2(20);
2009 l_not_in_clause VARCHAR2(200);
2010 l_ret BOOLEAN;
2011
2012 l_message VARCHAR2(250);
2013 l_sql VARCHAR2(4000);
2014
2015 BEGIN
2016
2017 SAVEPOINT standardize_lines_cp;
2018
2019 l_where_clause := NULL;
2020
2021 l_num_lines_inserted :=0;
2022
2023 l_num_sql_failed := 0;
2024
2025 l_which_group_failed := NULL;
2026
2027 l_not_in_clause := NULL;
2028
2029
2030
2031 IF ( P_ORG_ID IS NOT NULL AND P_ANALYSIS_TYPE IS NOT NULL ) THEN
2032
2033 GET_MAX_SEQ_NUMBERS ( x_seq_num_ind_and_sys_directed => l_seq_num_ind_and_sys_directed
2034 , x_seq_num_man_and_usr_directed => l_seq_num_man_and_usr_directed
2035 , x_seq_num_man_and_sys_directed => l_seq_num_man_and_sys_directed
2036 , x_seq_num_grouped => l_seq_num_grouped
2037 , p_org_id => p_org_id
2041 debug('The value of new seq for manual and system directed tasks '|| l_seq_num_man_and_sys_directed,'STANDARDIZE_LINES_CP');
2038 );
2039 IF g_debug=1 THEN
2040 debug('The value of new seq for individual and system directed tasks '|| l_seq_num_ind_and_sys_directed,'STANDARDIZE_LINES_CP');
2042 debug('The value of new seq for manual and user directed tasks '|| l_seq_num_man_and_usr_directed,'STANDARDIZE_LINES_CP');
2043 debug('The value of new seq for grouped tasks '|| l_seq_num_grouped,'STANDARDIZE_LINES_CP');
2044 END IF;
2045
2046 IF (P_ACTIVITY_ID IS NOT NULL) THEN
2047 l_where_clause := l_where_clause || ' AND activity_id = '|| p_activity_id ;
2048 END IF;
2049
2050 IF (P_ACTIVITY_DETAIL_ID IS NOT NULL) THEN
2051 l_where_clause := l_where_clause || ' AND activity_detail_id = '|| p_activity_detail_id ;
2052 END IF;
2053
2054 IF (P_OPERATION_ID IS NOT NULL) THEN
2055 l_where_clause := l_where_clause || ' AND operation_id = '|| p_operation_id ;
2056 END IF;
2057
2058 IF (P_FROM_DATE IS NOT NULL ) THEN
2059 l_where_clause := l_where_clause || ' AND transaction_date >= '|| 'TO_DATE(''' || p_from_date || ''',''YYYY/MM/DD HH24:MI:SS'')' ;
2060 END IF;
2061
2062 IF (P_TO_DATE IS NOT NULL ) THEN
2063 l_where_clause := l_where_clause || ' AND transaction_date <= '|| 'TO_DATE(''' || p_to_date || ''',''YYYY/MM/DD HH24:MI:SS'')' ;
2064 END IF;
2065
2066
2067 IF g_debug=1 THEN
2068 debug('The value of the where clause is '|| l_where_clause,'STANDARDIZE_LINES_CP');
2069 END IF;
2070
2071 BEGIN
2072 -- for manual and user directed tasks
2073
2074 EXECUTE IMMEDIATE
2075 ' INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B '
2076 || ' ( '
2077 || ' els_data_id '
2078 || ' , organization_id '
2079 || ' , sequence_number '
2080 || ' , analysis_id '
2081 || ' , activity_id '
2082 || ' , activity_detail_id '
2083 || ' , operation_id '
2084 || ' , equipment_id '
2085 || ' , source_zone_id '
2086 || ' , source_subinventory '
2087 || ' , destination_zone_id '
2088 || ' , destination_subinventory '
2089 || ' , labor_txn_source_id '
2090 || ' , operation_plan_id '
2091 || ' , group_id '
2092 || ' , task_type_id '
2093 || ' , task_method_id '
2094 || ' , expected_travel_time '
2095 || ' , expected_txn_time '
2096 || ' , expected_idle_time '
2097 || ' , actual_travel_time '
2098 || ' , actual_txn_time '
2099 || ' , actual_idle_time '
2100 || ' , last_updated_by '
2101 || ' , last_update_date '
2102 || ' , last_update_login '
2103 || ' , created_by '
2104 || ' , creation_date '
2105 || ' ) '
2106 || ' SELECT '
2107 || ' wms_els_individual_tasks_s.NEXTVAL ' -- els_data_id
2108 || ' , organization_id ' --organization_id
2109 || ' , nvl ( ' || l_seq_num_man_and_usr_directed || ',0)+10*ROWNUM ' --sequence_number
2110 || ' , ' || p_analysis_type --analysis_id
2111 || ' , activity_id ' --activity_id
2112 || ' , activity_detail_id ' --activity_detail_id
2113 || ' , operation_id ' --operation_id
2114 || ' , equipment_id ' --equipment_id
2115 || ' , source_zone_id ' --source_zone_id
2116 || ' , source_subinventory '--source_subinventory
2117 || ' , destination_zone_id ' --destination_zone_id
2118 || ' , destination_subinventory ' --destination_subinventory
2119 || ' , labor_txn_source_id ' --labor_txn_source_id
2120 || ' , operation_plan_id ' --operation_plan_id
2121 || ' , group_id ' --group_id
2122 || ' , task_type_id ' --task_type_id
2123 || ' , task_method_id ' --task_method_id
2124 || ' , travel_and_idle_time ' --expected_travel_time
2125 || ' , transaction_time ' --expected_txn_time
2126 || ' , NULL' --expected_idle_time
2127 || ' , travel_and_idle_time ' --actual_travel_time
2128 || ' , transaction_time ' --actual_txn_time
2129 || ' , idle_time ' --actual_idle_time
2130 || ' , FND_GLOBAL.USER_ID '
2131 || ' , sysdate '
2132 || ' , FND_GLOBAL.LOGIN_ID '
2133 || ' , FND_GLOBAL.USER_ID '
2134 || ' , sysdate '
2135 || ' FROM '
2136 || ' ( '
2137 || ' SELECT '
2138 || ' organization_id ' --organization_id
2139 || ' , activity_id ' --activity_id
2140 || ' , activity_detail_id ' --activity_detail_id
2141 || ' , operation_id ' --operation_id
2142 || ' , equipment_id ' --equipment_id
2143 || ' , source_zone_id ' --source_zone_id
2144 || ' , source_subinventory '--source_subinventory
2145 || ' , destination_zone_id ' --destination_zone_id
2146 || ' , destination_subinventory ' --destination_subinventory
2147 || ' , labor_txn_source_id ' --labor_txn_source_id
2148 || ' , operation_plan_id ' --operation_plan_id
2149 || ' , group_id ' --group_id
2150 || ' , task_type_id ' --task_type_id
2151 || ' , task_method_id ' --task_method_id
2152 || ' , ROUND(AVG(travel_and_idle_time),3) travel_and_idle_time ' --expected_travel_time
2153 || ' , ROUND(AVG(transaction_time),3) transaction_time' --expected_txn_time
2154 || ' , ROUND(AVG(idle_time),3) idle_time '
2155 || ' FROM WMS_ELS_TRX_SRC '
2159 || l_where_clause
2156 || ' WHERE UNATTRIBUTED_FLAG = 1 '
2157 || ' AND organization_id = ' || p_org_id
2158 || ' AND group_id = 1 '
2160 || ' GROUP BY '
2161 || ' organization_id,activity_id,activity_detail_id,operation_id '
2162 || ' ,equipment_id,source_zone_id,source_subinventory,destination_zone_id '
2163 || ' ,destination_subinventory,labor_txn_source_id,operation_plan_id '
2164 || ' ,group_id,task_type_id,task_method_id '
2165 || ' )';
2166
2167
2168 l_num_lines_inserted := SQL%ROWCOUNT;
2169
2170 IF g_debug=1 THEN
2171 debug('Standardization for manual and user directed tasks successfull','STANDARDIZE_LINES_CP');
2172 debug('Number of lines inserted for manual and user directed tasks'|| l_num_lines_inserted,'STANDARDIZE_LINES_CP');
2173 END IF;
2174
2175 EXCEPTION
2176 WHEN OTHERS THEN
2177 IF g_debug=1 THEN
2178 debug('Standardization for manual and user directed tasks failed'||SQLERRM,'STANDARDIZE_LINES');
2179 END IF;
2180 l_num_sql_failed := l_num_sql_failed + 1;
2181 l_which_group_failed := l_which_group_failed || '1,';
2182
2183 END;
2184
2185 l_num_lines_inserted :=0;--reinitialize
2186
2187 -- for manual and system directed tasks
2188 BEGIN
2189
2190 EXECUTE IMMEDIATE
2191 ' INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B '
2192 || ' ( '
2193 || ' els_data_id '
2194 || ' , organization_id '
2195 || ' , sequence_number '
2196 || ' , analysis_id '
2197 || ' , activity_id '
2198 || ' , activity_detail_id '
2199 || ' , operation_id '
2200 || ' , equipment_id '
2201 || ' , source_zone_id '
2202 || ' , source_subinventory '
2203 || ' , destination_zone_id '
2204 || ' , destination_subinventory '
2205 || ' , labor_txn_source_id '
2206 || ' , operation_plan_id '
2207 || ' , group_id '
2208 || ' , task_type_id '
2209 || ' , task_method_id '
2210 || ' , expected_travel_time '
2211 || ' , expected_txn_time '
2212 || ' , expected_idle_time '
2213 || ' , actual_travel_time '
2214 || ' , actual_txn_time '
2215 || ' , actual_idle_time '
2216 || ' , last_updated_by '
2217 || ' , last_update_date '
2218 || ' , last_update_login '
2219 || ' , created_by '
2220 || ' , creation_date '
2221 || ' ) '
2222 || ' SELECT '
2223 || ' wms_els_individual_tasks_s.NEXTVAL ' -- els_data_id
2224 || ' , organization_id ' --organization_id
2225 || ' , nvl ( ' || l_seq_num_man_and_sys_directed || ',0)+10*ROWNUM ' --sequence_number
2226 || ' , ' || p_analysis_type --analysis_id
2227 || ' , activity_id ' --activity_id
2228 || ' , activity_detail_id ' --activity_detail_id
2229 || ' , operation_id ' --operation_id
2230 || ' , equipment_id ' --equipment_id
2231 || ' , source_zone_id ' --source_zone_id
2232 || ' , source_subinventory '--source_subinventory
2233 || ' , destination_zone_id ' --destination_zone_id
2234 || ' , destination_subinventory ' --destination_subinventory
2235 || ' , labor_txn_source_id ' --labor_txn_source_id
2236 || ' , operation_plan_id ' --operation_plan_id
2237 || ' , group_id ' --group_id
2238 || ' , task_type_id ' --task_type_id
2239 || ' , task_method_id ' --task_method_id
2240 || ' , travel_and_idle_time ' --expected_travel_time
2241 || ' , transaction_time ' --expected_txn_time
2242 || ' , NULL' --expected_idle_time
2243 || ' , travel_and_idle_time ' --actual_travel_time
2244 || ' , transaction_time ' --actual_txn_time
2245 || ' , idle_time ' --actual_idle_time
2246 || ' , FND_GLOBAL.USER_ID '
2247 || ' , sysdate '
2248 || ' , FND_GLOBAL.LOGIN_ID '
2249 || ' , FND_GLOBAL.USER_ID '
2250 || ' , sysdate '
2251 || ' FROM '
2252 || ' ( '
2253 || ' SELECT '
2254 || ' organization_id ' --organization_id
2255 || ' , activity_id ' --activity_id
2256 || ' , activity_detail_id ' --activity_detail_id
2257 || ' , operation_id ' --operation_id
2258 || ' , equipment_id ' --equipment_id
2259 || ' , source_zone_id ' --source_zone_id
2260 || ' , source_subinventory '--source_subinventory
2261 || ' , destination_zone_id ' --destination_zone_id
2262 || ' , destination_subinventory ' --destination_subinventory
2263 || ' , labor_txn_source_id ' --labor_txn_source_id
2264 || ' , operation_plan_id ' --operation_plan_id
2265 || ' , group_id ' --group_id
2266 || ' , task_type_id ' --task_type_id
2267 || ' , task_method_id ' --task_method_id
2268 || ' , ROUND(AVG(travel_and_idle_time),3) travel_and_idle_time ' --expected_travel_time
2269 || ' , ROUND(AVG(transaction_time),3) transaction_time' --expected_txn_time
2270 || ' , ROUND(AVG(idle_time),3) idle_time '
2271 || ' FROM WMS_ELS_TRX_SRC '
2272 || ' WHERE UNATTRIBUTED_FLAG = 1 '
2273 || ' AND organization_id = ' || p_org_id
2274 || ' AND group_id = 2 '
2275 || l_where_clause
2276 || ' GROUP BY '
2277 || ' organization_id,activity_id,activity_detail_id,operation_id '
2278 || ' ,equipment_id,source_zone_id,source_subinventory,destination_zone_id '
2279 || ' ,destination_subinventory,labor_txn_source_id,operation_plan_id '
2280 || ' ,group_id,task_type_id,task_method_id '
2281 || ' )';
2285
2282
2283
2284 l_num_lines_inserted := SQL%ROWCOUNT;
2286 IF g_debug=1 THEN
2287 debug('Standardization for manual and system directed tasks successfull','STANDARDIZE_LINES_CP');
2288 debug('Number of lines inserted for manual and system directed tasks'|| l_num_lines_inserted,'STANDARDIZE_LINES_CP');
2289 END IF;
2290
2291 EXCEPTION
2292 WHEN OTHERS THEN
2293 IF g_debug=1 THEN
2294 debug('Standardization for manual and system directed tasks failed'||SQLERRM,'STANDARDIZE_LINES');
2295 END IF;
2296
2297 l_num_sql_failed := l_num_sql_failed + 1;
2298 l_which_group_failed := l_which_group_failed || '2,';
2299
2300 END;
2301
2302 l_num_lines_inserted :=0;--reinitialize
2303
2304 -- for individual and system directed tasks
2305
2306 BEGIN
2307
2308 EXECUTE IMMEDIATE
2309 ' INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B '
2310 || ' ( '
2311 || ' els_data_id '
2312 || ' , organization_id '
2313 || ' , sequence_number '
2314 || ' , analysis_id '
2315 || ' , activity_id '
2316 || ' , activity_detail_id '
2317 || ' , operation_id '
2318 || ' , equipment_id '
2319 || ' , source_zone_id '
2320 || ' , source_subinventory '
2321 || ' , destination_zone_id '
2322 || ' , destination_subinventory '
2323 || ' , labor_txn_source_id '
2324 || ' , operation_plan_id '
2325 || ' , group_id '
2326 || ' , task_type_id '
2327 || ' , task_method_id '
2328 || ' , expected_travel_time '
2329 || ' , expected_txn_time '
2330 || ' , expected_idle_time '
2331 || ' , actual_travel_time '
2332 || ' , actual_txn_time '
2333 || ' , actual_idle_time '
2334 || ' , last_updated_by '
2335 || ' , last_update_date '
2336 || ' , last_update_login '
2337 || ' , created_by '
2338 || ' , creation_date '
2339 || ' ) '
2340 || ' SELECT '
2341 || ' wms_els_individual_tasks_s.NEXTVAL ' -- els_data_id
2342 || ' , organization_id ' --organization_id
2343 || ' , nvl ( ' || l_seq_num_ind_and_sys_directed || ',0)+10*ROWNUM ' --sequence_number
2344 || ' , ' || p_analysis_type --analysis_id
2345 || ' , activity_id ' --activity_id
2346 || ' , activity_detail_id ' --activity_detail_id
2347 || ' , operation_id ' --operation_id
2348 || ' , equipment_id ' --equipment_id
2349 || ' , source_zone_id ' --source_zone_id
2350 || ' , source_subinventory '--source_subinventory
2351 || ' , destination_zone_id ' --destination_zone_id
2352 || ' , destination_subinventory ' --destination_subinventory
2353 || ' , labor_txn_source_id ' --labor_txn_source_id
2354 || ' , operation_plan_id ' --operation_plan_id
2355 || ' , group_id ' --group_id
2356 || ' , task_type_id ' --task_type_id
2357 || ' , task_method_id ' --task_method_id
2358 || ' , travel_and_idle_time ' --expected_travel_time
2359 || ' , transaction_time ' --expected_txn_time
2360 || ' , NULL' --expected_idle_time
2361 || ' , travel_and_idle_time ' --actual_travel_time
2362 || ' , transaction_time ' --actual_txn_time
2363 || ' , idle_time ' --actual_idle_time
2364 || ' , FND_GLOBAL.USER_ID '
2365 || ' , sysdate '
2366 || ' , FND_GLOBAL.LOGIN_ID '
2367 || ' , FND_GLOBAL.USER_ID '
2368 || ' , sysdate '
2369 || ' FROM '
2370 || ' ( '
2371 || ' SELECT '
2372 || ' organization_id ' --organization_id
2373 || ' , activity_id ' --activity_id
2374 || ' , activity_detail_id ' --activity_detail_id
2375 || ' , operation_id ' --operation_id
2376 || ' , equipment_id ' --equipment_id
2377 || ' , source_zone_id ' --source_zone_id
2378 || ' , source_subinventory '--source_subinventory
2379 || ' , destination_zone_id ' --destination_zone_id
2380 || ' , destination_subinventory ' --destination_subinventory
2381 || ' , labor_txn_source_id ' --labor_txn_source_id
2382 || ' , operation_plan_id ' --operation_plan_id
2383 || ' , group_id ' --group_id
2384 || ' , task_type_id ' --task_type_id
2385 || ' , task_method_id ' --task_method_id
2386 || ' , ROUND(AVG(travel_and_idle_time),3) travel_and_idle_time ' --expected_travel_time
2387 || ' , ROUND(AVG(transaction_time),3) transaction_time' --expected_txn_time
2388 || ' , ROUND(AVG(idle_time),3) idle_time '
2389 || ' FROM WMS_ELS_TRX_SRC '
2390 || ' WHERE UNATTRIBUTED_FLAG = 1 '
2391 || ' AND organization_id = ' || p_org_id
2392 || ' AND group_id = 3 '
2393 || l_where_clause
2394 || ' GROUP BY '
2395 || ' organization_id,activity_id,activity_detail_id,operation_id '
2396 || ' ,equipment_id,source_zone_id,source_subinventory,destination_zone_id '
2397 || ' ,destination_subinventory,labor_txn_source_id,operation_plan_id '
2398 || ' ,group_id,task_type_id,task_method_id '
2399 || ' )';
2400
2401
2402 l_num_lines_inserted := SQL%ROWCOUNT;
2403
2404 IF g_debug=1 THEN
2405 debug('Standardization for individual and system directed tasks successfull','STANDARDIZE_LINES_CP');
2406 debug('Number of lines inserted for individual and system directed tasks'|| l_num_lines_inserted,'STANDARDIZE_LINES_CP');
2407 END IF;
2408
2409 EXCEPTION
2410 WHEN OTHERS THEN
2411 IF g_debug=1 THEN
2415 l_num_sql_failed := l_num_sql_failed + 1;
2412 debug('Standardization for individual and system directed tasks failed'||SQLERRM,'STANDARDIZE_LINES_CP');
2413 END IF;
2414
2416 l_which_group_failed := l_which_group_failed || '3,';
2417
2418 END;
2419
2420 --If all the sql's for the three groups have failed then error out and return from here
2421
2422 IF(l_num_sql_failed = 3 )THEN
2423
2424 --- Dont PROCEED;return error from here itself;
2425 ROLLBACK TO standardize_lines_cp;
2426 retcode := 2;
2427 fnd_message.set_name('WMS', 'WMS_LMS_STANDARDIZE_ERROR');
2428 l_message := fnd_message.get;
2429 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
2430 RETURN;
2431
2432 END IF;
2433
2434 -- Not all sql's have failed so one can procedd and at the end return appropiate
2435 -- staus of SUCCESS(if nothing fails) or WARNING( if some sql but not all fail)
2436
2437 BEGIN
2438 -- insert lines into TL table for all lines inserted into Base tables
2439
2440 INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_TL
2441 (
2442 els_data_id,
2443 language,
2444 source_lang,
2445 description,
2446 last_updated_by,
2447 last_update_login,
2448 created_by,
2449 creation_date,
2450 last_update_date
2451 )
2452 SELECT b.els_data_id
2453 , l.language_code
2454 , userenv('lang')
2455 , null
2456 , b.last_updated_by
2457 , b.last_update_login
2458 , b.created_by
2459 , b.creation_date
2460 , b.last_update_date
2461 FROM WMS_ELS_INDIVIDUAL_TASKS_B b,
2462 FND_LANGUAGES L
2463 WHERE els_data_id NOT IN (
2464 SELECT DISTINCT els_data_id
2465 FROM wms_els_individual_tasks_tl
2466 )
2467 AND L.INSTALLED_FLAG in ('I', 'B');
2468
2469 EXCEPTION
2470 WHEN OTHERS THEN
2471 -- If insertion into TL table fails the rollback everything and return from here
2472 -- no use to proceed after such a mishap
2473
2474 IF g_debug=1 THEN
2475 debug('Insertion into TL table failed so rolling back everything'||SQLERRM,'STANDARDIZE_LINES_CP');
2476 END IF;
2477
2478 ROLLBACK TO standardize_lines_cp;
2479
2480 retcode := 2;
2481 fnd_message.set_name('WMS', 'WMS_STANDARDIZE_LINES_ERROR');
2482 l_message := fnd_message.get;
2483 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
2484 RETURN;
2485
2486 END;
2487
2488 -- Now when the insertion to WMS_ELS_INDIVIDUAL_TASKS_B and
2489 -- WMS_ELS_INDIVIDUAL_TASKS_TL table is successfull now we can proceed
2490 -- to inserting lines in the WMS_ELS_GROUPED_TASKS_B and WMS_ELS_GROUPED_TASKS_TL table
2491
2492 l_num_lines_inserted :=0;--reinitialize
2493
2494
2495 IF (l_which_group_failed IS NOT NULL) THEN
2496
2497 SELECT substr(l_which_group_failed,0,length(l_which_group_failed)-1)
2498 INTO l_which_group_failed
2499 FROM dual;
2500
2501 IF g_debug=1 THEN
2502 debug('Value of l_which_group_failed '||l_which_group_failed,'STANDARDIZE_LINES_CP');
2503 END IF;
2504
2505 l_not_in_clause := ' AND group_id NOT IN (' || l_which_group_failed || ') ';
2506
2507 IF g_debug=1 THEN
2508 debug('Value of l_not_in_clause '||l_not_in_clause ,'STANDARDIZE_LINES_CP');
2509 END IF;
2510
2511 END IF;
2512
2513 BEGIN
2514
2515 EXECUTE IMMEDIATE
2516 'INSERT INTO WMS_ELS_GROUPED_TASKS_B' ||
2517 '(' ||
2518 ' Els_Group_Id' ||
2519 ' , Organization_id' ||
2520 ' , Sequence_Number' ||
2521 ' , activity_id' ||
2522 ' , activity_detail_id' ||
2523 ' , operation_id' ||
2524 ' , labor_txn_source_id' ||
2525 ' , source_zone_id' ||
2526 ' , source_subinventory' ||
2527 ' , destination_zone_id' ||
2528 ' , destination_subinventory' ||
2529 ' , task_method_id' ||
2530 ' , task_range_from' ||
2531 ' , task_range_to' ||
2532 ' , expected_travel_time' ||
2533 ' , actual_travel_time' ||
2534 ' , last_updated_by' ||
2535 ' , last_update_date' ||
2536 ' , last_update_login' ||
2537 ' , created_by' ||
2538 ' , creation_date' ||
2539 ')' ||
2540 'SELECT wms_els_grouped_tasks_s.NEXTVAL' ||
2541 ' , organization_id' ||
2542 ' , (nvl('||l_seq_num_grouped||',0) + (10*ROWNUM)) sequence_number' ||
2543 ' , activity_id' ||
2544 ' , activity_detail_id' ||
2545 ' , operation_id' ||
2546 ' , labor_txn_source_id ' ||
2547 ' , source_zone_id' ||
2548 ' , source_subinventory' ||
2549 ' , destination_zone_id' ||
2550 ' , destination_subinventory' ||
2551 ' , task_method_id' ||
2552 ' , task_range_from' ||
2553 ' , task_range_to' ||
2554 ' , exp_travel_time' ||
2555 ' , act_travel_Time' ||
2556 ' , FND_GLOBAL.USER_ID last_updated_by ' ||
2557 ' , sysdate last_update_date' ||
2558 ' , FND_GLOBAL.LOGIN_ID last_update_login' ||
2559 ' , FND_GLOBAL.USER_ID created_by' ||
2560 ' , sysdate creation_date' ||
2561 ' FROM ' ||
2562 '( select organization_id' ||
2563 ' , activity_id' ||
2564 ' , activity_detail_id' ||
2565 ' , operation_id' ||
2566 ' , source_zone_id' ||
2567 ' , source_subinventory' ||
2571 ' , task_method_id' ||
2568 ' , destination_zone_id' ||
2569 ' , destination_subinventory' ||
2570 ' , labor_txn_source_id' ||
2572 ' , count(*) task_range_from' ||
2573 ' , count(*) task_range_to' ||
2574 ' , sum(travel_and_idle_time) exp_travel_time' ||
2575 ' , sum(travel_and_idle_time) act_travel_Time' ||
2576 ' FROM wms_els_trx_src' ||
2577 ' where els_data_id is null' ||
2578 ' and organization_id = '|| p_org_id ||
2579 ' and unattributed_flag = 1 '||
2580 ' and grouped_Task_identifier IS NOT NULL ' ||
2581 l_where_clause || l_not_in_clause ||
2582 ' group by grouped_Task_identifier' ||
2583 ' , organization_id' ||
2584 ' , activity_id' ||
2585 ' , activity_detail_id' ||
2586 ' , operation_id' ||
2587 ' , source_zone_id' ||
2588 ' , source_subinventory' ||
2589 ' , destination_zone_id' ||
2590 ' , destination_subinventory' ||
2591 ' , labor_txn_source_id' ||
2592 ' , task_method_id' ||
2593 ' )';
2594
2595 l_num_lines_inserted := SQL%ROWCOUNT;
2596
2597 IF g_debug=1 THEN
2598 debug('Standardization for grouped and system directed tasks successfull','STANDARDIZE_LINES_CP');
2599 debug('Number of lines inserted for grouped and system directed tasks'|| l_num_lines_inserted,'STANDARDIZE_LINES_CP');
2600 END IF;
2601
2602 EXCEPTION
2603 WHEN OTHERS THEN
2604 IF g_debug=1 THEN
2605 debug('Standardization for grouped and system directed tasks failed'||SQLERRM,'STANDARDIZE_LINES_CP');
2606 END IF;
2607
2608 ROLLBACK TO standardize_lines_cp;
2609
2610 retcode := 2;
2611 fnd_message.set_name('WMS', 'WMS_STANDARDIZE_LINES_ERROR');
2612 l_message := fnd_message.get;
2613 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
2614 RETURN;
2615
2616 END;
2617
2618 -- proceed with TL table only when insertion to WMS_ELS_GROUPED_TASKS_B is a success
2619
2620 BEGIN
2621
2622 INSERT INTO WMS_ELS_GROUPED_TASKS_TL
2623 (
2624 els_group_id,
2625 language,
2626 source_lang,
2627 description,
2628 last_updated_by,
2629 last_update_login,
2630 created_by,
2631 creation_date,
2632 last_update_date
2633 )
2634 SELECT b.els_group_id,
2635 l.language_code,
2636 userenv('lang'), --Have to change this line. Need to give a Source Language.
2637 null,
2638 b.last_updated_by,
2639 b.last_update_login,
2640 b.created_by,
2641 b.creation_date,
2642 b.last_update_date
2643 FROM WMS_ELS_GROUPED_TASKS_B b,
2644 FND_LANGUAGES L
2645 WHERE els_group_id NOT IN (
2646 SELECT DISTINCT els_group_id
2647 FROM wms_els_grouped_tasks_tl
2648 )
2649 AND L.INSTALLED_FLAG in ('I', 'B');
2650
2651 EXCEPTION
2652 WHEN OTHERS THEN
2653 IF g_debug=1 THEN
2654 debug('Insertion to TL table failed with '||SQLERRM,'STANDARDIZE_LINES_CP');
2655 END IF;
2656 -- roll back to savepoint and return
2657
2658 ROLLBACK TO standardize_lines_cp;
2659
2660 retcode := 2;
2661 fnd_message.set_name('WMS', 'WMS_STANDARDIZE_LINES_ERROR');
2662 l_message := fnd_message.get;
2663 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
2664 RETURN;
2665
2666
2667 END;
2668
2669 -- now make the unattributed_flag as null for these lines that have been standardized
2670
2671 EXECUTE IMMEDIATE
2672 'UPDATE wms_els_trx_src ' ||
2673 'SET unattributed_flag = null ' ||
2674 'WHERE organization_id = ' || p_org_id ||l_where_clause || l_not_in_clause ;
2675
2676
2677 --NOW check for the status of l_num_sql_failed to
2678 -- determine the status of completion of the program
2679 -- If no sql have failed(variables have the value 0 then SUCCESS)
2680 -- Else if any one sql has failed ( variable has the value of not =0 then WARNING)
2681
2682 IF ( l_num_sql_failed = 0 )THEN
2683 -- every thing is done so the concurrent program has finished normal
2684 COMMIT;
2685 retcode := 1;
2686 fnd_message.set_name('WMS', 'WMS_STANDARDIZE_LINES_SUCCESS');
2687 l_message := fnd_message.get;
2688 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',l_message);
2689
2690 ELSE
2691
2692 COMMIT;
2693 retcode := 3;
2694 fnd_message.set_name('WMS', 'WMS_STANDARDIZE_LINES_WARN');
2695 l_message := fnd_message.get;
2696 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
2697
2698 END IF;
2699
2700 ELSE -- When required parameters are not passed
2701
2702 IF g_debug=1 THEN
2703 debug( 'Required parameters not passed '
2704 ,'PURGE_LMS_TRANSACTIONS'
2705 );
2706 END IF;
2707
2708 retcode := 2;
2709 fnd_message.set_name('WMS', 'WMS_LMS_REQ_PARAM_NULL');
2710 l_message := fnd_message.get;
2711 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
2712
2713 END IF; -- when required parameters passed
2714
2715
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718
2719 IF g_debug=1 THEN
2720 debug( 'Exception has occured','STANDARDIZE_LINES_CP');
2721 END IF;
2722
2723 -- roll back to savepoint and return
2724 ROLLBACK TO standardize_lines_cp;
2725
2726 retcode := 2;
2727 fnd_message.set_name('WMS', 'WMS_STANDARDIZE_LINES_ERROR');
2728 l_message := fnd_message.get;
2729 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
2730
2731 END STANDARDIZE_LINES_CP;
2732
2733
2734 PROCEDURE INSERT_ELS_TRX
2735 (
2736 P_ACTIVITY_ID NUMBER,
2737 P_ACTIVITY_DETAIL_ID NUMBER,
2738 P_OPERATION_ID NUMBER,
2739 P_ORGANIZATION_ID NUMBER,
2740 P_USER_ID NUMBER,
2741 P_EQUIPMENT_ID NUMBER,
2742 P_SOURCE_SUBINVENTORY VARCHAR2,
2743 P_DESTINATION_SUBINVENTORY VARCHAR2,
2744 P_FROM_LOCATOR_ID NUMBER,
2745 P_TO_LOCATOR_ID NUMBER,
2746 P_LABOR_TXN_SOURCE_ID NUMBER,
2747 P_TRANSACTION_UOM VARCHAR2,
2748 P_QUANTITY NUMBER,
2749 P_INVENTORY_ITEM_ID NUMBER,
2750 P_GROUP_ID NUMBER,
2751 P_TASK_METHOD_ID NUMBER,
2752 P_TASK_TYPE_ID NUMBER,
2753 P_GROUPED_TASK_IDENTIFIER NUMBER,
2754 P_GROUP_SIZE NUMBER,
2755 P_TRANSACTION_TIME NUMBER,
2756 P_TRAVEL_AND_IDLE_TIME NUMBER,
2757 P_CREATED_BY NUMBER,
2758 P_OPERATION_PLAN_ID NUMBER,
2759 X_RETURN_STATUS OUT NOCOPY VARCHAR2
2760 ) AS
2761 PRAGMA AUTONOMOUS_TRANSACTION;
2762
2763 BEGIN
2764
2765 x_return_status := FND_API.G_RET_STS_SUCCESS;
2766
2767 IF(IS_USER_NON_TRACKED (
2768 P_USER_ID=>P_USER_ID,
2769 P_ORG_ID =>P_ORGANIZATION_ID
2770 )
2771 ) THEN
2772 /* Since this is a non-tracked user, do not
2773 enter any record for this transaction */
2774 IF g_debug=1 THEN
2775 debug('Since this is a non-tracked user: ' || P_USER_ID || ', do not enter any record for this transaction');
2776 END IF;
2777
2778 RETURN;
2779 END IF;
2780
2781
2782 INSERT INTO WMS_ELS_TRX_SRC
2783 (
2784 ELS_TRX_SRC_ID,
2785 TRANSACTION_DATE,
2786 ACTIVITY_ID,
2787 ACTIVITY_DETAIL_ID,
2788 OPERATION_ID,
2789 ORGANIZATION_ID,
2790 USER_ID,
2791 EQUIPMENT_ID,
2792 SOURCE_SUBINVENTORY,
2793 DESTINATION_SUBINVENTORY,
2794 FROM_LOCATOR_ID,
2795 TO_LOCATOR_ID,
2796 LABOR_TXN_SOURCE_ID,
2797 TRANSACTION_UOM,
2798 QUANTITY,
2799 INVENTORY_ITEM_ID,
2800 GROUP_ID,
2801 TASK_METHOD_ID,
2802 TASK_TYPE_ID,
2803 GROUPED_TASK_IDENTIFIER,
2804 GROUP_SIZE,
2805 TRANSACTION_TIME,
2806 TRAVEL_AND_IDLE_TIME,
2807 CREATION_DATE,
2808 CREATED_BY,
2809 OPERATION_PLAN_ID,
2810 LAST_UPDATE_DATE,
2811 LAST_UPDATED_BY
2812 )
2813 values
2814 (
2815 WMS_ELS_TRX_SRC_S.NEXTVAL,
2816 SYSDATE,
2817 P_ACTIVITY_ID,
2818 P_ACTIVITY_DETAIL_ID,
2819 P_OPERATION_ID,
2820 P_ORGANIZATION_ID,
2821 P_USER_ID,
2822 P_EQUIPMENT_ID,
2823 P_SOURCE_SUBINVENTORY,
2824 P_DESTINATION_SUBINVENTORY,
2825 P_FROM_LOCATOR_ID,
2826 P_TO_LOCATOR_ID,
2827 P_LABOR_TXN_SOURCE_ID,
2828 P_TRANSACTION_UOM,
2829 P_QUANTITY,
2830 P_INVENTORY_ITEM_ID,
2831 P_GROUP_ID,
2832 P_TASK_METHOD_ID,
2833 P_TASK_TYPE_ID,
2834 P_GROUPED_TASK_IDENTIFIER,
2835 P_GROUP_SIZE,
2836 P_TRANSACTION_TIME,
2837 P_TRAVEL_AND_IDLE_TIME,
2838 SYSDATE,
2839 P_CREATED_BY,
2840 P_OPERATION_PLAN_ID,
2841 SYSDATE,
2842 P_CREATED_BY
2843 );
2844 commit;
2845
2846 IF g_debug=1 THEN
2847 debug('Successfully inserted record in WMS_ELS_TRX_SRC table');
2848 END IF;
2849
2850 EXCEPTION
2851 WHEN OTHERS THEN
2852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2853
2854 IF g_debug=1 THEN
2855 debug('Insertion in WMS_ELS_TRX_SRC failed'||SQLERRM);
2856 END IF;
2857
2858 END INSERT_ELS_TRX;
2859
2860
2861 END WMS_LMS_UTILS;
2862