[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ROUTE_PEOPLE_UTIL
Source
1 PACKAGE BODY Eng_Change_Route_People_Util AS
2 /* $Header: ENGURTPB.pls 120.1 2006/01/12 19:23:03 mkimizuk noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'Eng_Change_Route_People_Util' ;
5
6 -- For Debug
7 g_debug_file UTL_FILE.FILE_TYPE ;
8 g_debug_flag BOOLEAN := FALSE ; -- For TEST : FALSE ;
9 g_output_dir VARCHAR2(80) ;
10 g_debug_filename VARCHAR2(35) ;
11 g_debug_errmesg VARCHAR2(240);
12
13 -- Global Assignee Rec
14 g_orig_assignee_rec Eng_Change_Route_People_Util.Assignee_Rec_Type ;
15 g_assignee_rec Eng_Change_Route_People_Util.Assignee_Rec_Type ;
16
17
18
19 /********************************************************************
20 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
21 * Write_Debug
22 * Parameters IN :
23 * Parameters OUT:
24 * Purpose : These procedures are for test and debug
25 *********************************************************************/
26 -- Open_Debug_Session
27 PROCEDURE Open_Debug_Session
28 ( p_output_dir IN VARCHAR2 := NULL
29 , p_file_name IN VARCHAR2 := NULL
30 )
31 IS
32 l_found NUMBER := 0;
33 l_utl_file_dir VARCHAR2(2000);
34
35 BEGIN
36
37 IF p_output_dir IS NOT NULL THEN
38 g_output_dir := p_output_dir ;
39
40 END IF ;
41
42 IF p_file_name IS NOT NULL THEN
43 g_debug_filename := p_file_name ;
44 END IF ;
45
46 IF g_output_dir IS NULL
47 THEN
48
49 g_output_dir := FND_PROFILE.VALUE('ECX_UTL_LOG_DIR') ;
50
51 END IF;
52
53 select value
54 INTO l_utl_file_dir
55 FROM v$parameter
56 WHERE name = 'utl_file_dir';
57
58 l_found := INSTR(l_utl_file_dir, g_output_dir);
59
60 IF l_found = 0
61 THEN
62 RETURN;
63 END IF;
64
65 -- Set Default Debug Name
66 IF g_debug_filename IS NULL THEN
67 g_debug_filename := 'Eng_Change_Route_People_Util.log' ;
68 END IF ;
69
70 g_debug_file := utl_file.fopen( g_output_dir
71 , g_debug_filename
72 , 'w');
73 g_debug_flag := TRUE ;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
78 g_debug_flag := FALSE;
79
80 END Open_Debug_Session ;
81
82 -- Close Debug_Session
83 PROCEDURE Close_Debug_Session
84 IS
85 BEGIN
86 IF utl_file.is_open(g_debug_file)
87 THEN
88 utl_file.fclose(g_debug_file);
89 END IF ;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
94 g_debug_flag := FALSE;
95
96 END Close_Debug_Session ;
97
98 -- Test Debug
99 PROCEDURE Write_Debug
100 ( p_debug_message IN VARCHAR2 )
101 IS
102 BEGIN
103
104 IF utl_file.is_open(g_debug_file)
105 THEN
106 utl_file.put_line(g_debug_file, p_debug_message);
107 END IF ;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
112 g_debug_flag := FALSE;
113
114 END Write_Debug;
115
116
117
118 /********************************************************************
119 * API Type : Private APIs
120 * Purpose : Those APIs are private
121 *********************************************************************/
122
123 -- Get assignee rec from cache rec or database rec
124 PROCEDURE GetAssigneeRec
125 ( p_assignee_id IN NUMBER
126 , p_assignee_type_code IN VARCHAR2
127 , p_current_flag IN VARCHAR2 := NULL -- Y or NULL regarded as Current
128 , x_assignee_rec OUT NOCOPY Eng_Change_Route_People_Util.Assignee_Rec_Type
129 )
130 IS
131
132 l_idx BINARY_INTEGER ;
133 l_found BOOLEAN := FALSE ;
134
135 CURSOR assignee_type_cur(p_assignee_type VARCHAR2)
136 IS
137 SELECT AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
138 FROM FND_LOOKUPS AssigneeTypeLookup
139 WHERE AssigneeTypeLookup.LOOKUP_CODE = p_assignee_type
140 AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES' ;
141
142 CURSOR role_assignee_cur(p_role_id NUMBER)
143 IS
144 SELECT MenuTL.USER_MENU_NAME ASSIGNEE_NAME
145 FROM FND_MENUS_TL MenuTL
146 WHERE MenuTL.LANGUAGE = USERENV('LANG')
147 AND MenuTL.MENU_ID = p_role_id ;
148
149
150 CURSOR role_assignee_lkup_cur
151 IS
152 SELECT AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
153 , '' ASSIGNEE_COMPANY
154 FROM FND_LOOKUPS AssigneeTypeLookup
155 WHERE AssigneeTypeLookup.LOOKUP_CODE = 'ROLE'
156 AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES' ;
157
158
159
160 -- Decided to put prefix 'Change'for Change Object role also
161 CURSOR role_object_name(p_role_id NUMBER)
162 IS
163 SELECT Obj.DISPLAY_NAME OBJECT_NAME
164 FROM ( select distinct f.object_id, e.menu_id
165 from fnd_form_functions f, fnd_menu_entries e
166 where e.function_id = f.function_id) EgoRoles,
167 FND_OBJECTS_VL Obj
168 WHERE Obj.OBJECT_ID = EgoRoles.OBJECT_ID
169 -- AND Obj.OBJ_NAME <> 'ENG_CHANGE'
170 AND EgoRoles.MENU_ID = p_role_id ;
171
172
173 CURSOR person_assignee_cur (p_person_id NUMBER)
174 IS
175 SELECT Parties.PARTY_NAME ASSIGNEE_NAME
176 , AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
177 , Company.PARTY_NAME ASSIGNEE_COMPANY
178 FROM FND_LOOKUPS AssigneeTypeLookup
179 , HZ_RELATIONSHIPS Emp_Cmpy
180 , HZ_PARTIES Company
181 , HZ_PARTIES Parties
182 WHERE AssigneeTypeLookup.LOOKUP_CODE = 'PERSON'
183 AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES'
184 AND Emp_Cmpy.SUBJECT_TYPE (+)= 'PERSON'
185 AND Emp_Cmpy.SUBJECT_TABLE_NAME (+)= 'HZ_PARTIES'
186 AND Emp_Cmpy.OBJECT_TYPE (+)= 'ORGANIZATION'
187 AND Emp_Cmpy.RELATIONSHIP_CODE (+)= 'EMPLOYEE_OF'
188 AND Emp_Cmpy.OBJECT_TABLE_NAME (+)= 'HZ_PARTIES'
189 AND Emp_Cmpy.STATUS (+)= 'A'
190 AND Emp_Cmpy.START_DATE (+)<= SYSDATE
191 AND (Emp_Cmpy.END_DATE IS NULL OR Emp_Cmpy.END_DATE >= SYSDATE)
192 AND Company.PARTY_ID (+)= Emp_Cmpy.OBJECT_ID
193 AND Company.STATUS (+)= 'A'
194 AND Emp_Cmpy.SUBJECT_ID (+)= Parties.PARTY_ID
195 AND Parties.PARTY_TYPE = 'PERSON'
196 AND Parties.PARTY_ID = p_person_id ;
197
198 CURSOR group_assignee_cur (p_group_id NUMBER)
199 IS
200 SELECT Grp.PARTY_NAME ASSIGNEE_NAME
201 , AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
202 , '' ASSIGNEE_COMPANY
203 FROM FND_LOOKUPS AssigneeTypeLookup
204 , HZ_PARTIES Grp
205 WHERE AssigneeTypeLookup.LOOKUP_CODE = 'GROUP'
206 AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES'
207 AND Grp.PARTY_TYPE = 'GROUP'
208 AND Grp.PARTY_ID = p_group_id ;
209
210
211 CURSOR chg_policy_assignee_cur (p_chg_policy_assignee_id VARCHAR2)
212 IS
213 SELECT ChgPolicyAssigneeLookup.MEANING ASSIGNEE_NAME
214 , AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
215 , '' ASSIGNEE_COMPANY
216 FROM FND_LOOKUPS ChgPolicyAssigneeLookup
217 , FND_LOOKUPS AssigneeTypeLookup
218 WHERE AssigneeTypeLookup.LOOKUP_CODE = 'CHANGE_POLICY'
219 AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES'
220 AND ChgPolicyAssigneeLookup.LOOKUP_TYPE = 'ENG_ROUTE_CHG_POLICY_ASSIGNEES'
221 AND ChgPolicyAssigneeLookup.LOOKUP_CODE = p_chg_policy_assignee_id ;
222
223 BEGIN
224
225 -- 1. Check Current Cache Record
226 IF p_current_flag IS NULL OR p_current_flag = 'Y' THEN
227
228 IF g_assignee_rec.assignee_id = p_assignee_id
229 AND g_assignee_rec.assignee_type_code = p_assignee_type_code
230 THEN
231 x_assignee_rec := g_assignee_rec ;
232 RETURN ;
233 END IF ;
234
235 ELSE
236
237 IF g_orig_assignee_rec.assignee_id = p_assignee_id
238 AND g_orig_assignee_rec.assignee_type_code = p_assignee_type_code
239 THEN
240 x_assignee_rec := g_orig_assignee_rec;
241 RETURN ;
242 END IF ;
243
244 END IF ;
245
246 -- 2. Check Cache Table
247 l_idx := NVL(G_ASSIGNEE_TBL.COUNT, 0 ) ;
248
249 -- Cache exists in table
250 IF l_idx <> 0 THEN
251
252 FOR i IN 1..G_ASSIGNEE_TBL.COUNT
253 LOOP
254
255 IF G_ASSIGNEE_TBL(i).assignee_id = p_assignee_id
256 AND G_ASSIGNEE_TBL(i).assignee_type_code = p_assignee_type_code
257 THEN
258
259 x_assignee_rec := G_ASSIGNEE_TBL(i) ;
260 l_found := TRUE ;
261
262 END IF ;
263
264 END LOOP;
265
266 END IF ;
267
268 -- 3. Query from Database
269 IF NOT l_found OR l_idx = 0
270 THEN
271
272 l_idx := l_idx + 1 ;
273
274 IF p_assignee_type_code = 'ROLE'
275 THEN
276
277 FOR l_role_rec IN role_assignee_cur (p_assignee_id)
278 LOOP
279 x_assignee_rec.assignee_id := p_assignee_id ;
280 x_assignee_rec.assignee_type_code := p_assignee_type_code ;
281 x_assignee_rec.assignee_name := l_role_rec.assignee_name ;
282 x_assignee_rec.assignee_role_obj_name := NULL ;
283 END LOOP ;
284
285
286 FOR l_role_rec IN role_assignee_lkup_cur
287 LOOP
288 x_assignee_rec.assignee_type := l_role_rec.assignee_type ;
289 x_assignee_rec.assignee_company := l_role_rec.assignee_company ;
290 END LOOP ;
291
292
293 IF x_assignee_rec.assignee_type IS NOT NULL
294 THEN
295 -- May need to use FND_MESSAGE for Object Role Name
296 FOR l_obj_rec IN role_object_name (p_assignee_id)
297 LOOP
298
299 FND_MESSAGE.SET_NAME('ENG', 'ENG_CHANGE_OBJECT_ROLE') ;
300 FND_MESSAGE.SET_TOKEN('OBJECT_NAME', l_obj_rec.object_name);
301 FND_MESSAGE.SET_TOKEN('ROLE', x_assignee_rec.assignee_type);
302 x_assignee_rec.assignee_role_obj_name := FND_MESSAGE.GET ;
303
304 END LOOP ;
305 END IF ;
306
307
308 ELSIF p_assignee_type_code = 'PERSON'
309 THEN
310
311 IF p_assignee_id = -1 THEN
312
313 -- FND_MESSAGE.SET_NAME('ENG','ENG_ROUTE_NO_ASSIGNEE');
314 x_assignee_rec.assignee_id := p_assignee_id ;
315 x_assignee_rec.assignee_type_code := p_assignee_type_code ;
316 x_assignee_rec.assignee_name := NULL ;
317 -- x_assignee_rec.assignee_name := FND_MESSAGE.GET ;
318 x_assignee_rec.assignee_company := NULL ;
319 x_assignee_rec.assignee_role_obj_name := NULL ;
320
321 FOR l_type_rec IN assignee_type_cur (p_assignee_type_code)
322 LOOP
323 x_assignee_rec.assignee_type := l_type_rec.assignee_type ;
324 END LOOP ;
325
326 ELSE
327
328 FOR l_per_rec IN person_assignee_cur (p_assignee_id)
329 LOOP
330 x_assignee_rec.assignee_id := p_assignee_id ;
331 x_assignee_rec.assignee_type_code := p_assignee_type_code ;
332 x_assignee_rec.assignee_type := l_per_rec.assignee_type ;
333 x_assignee_rec.assignee_name := l_per_rec.assignee_name ;
334 x_assignee_rec.assignee_company := l_per_rec.assignee_company ;
335 x_assignee_rec.assignee_role_obj_name := NULL ;
336 END LOOP ;
337
338 END IF ;
339
340 ELSIF p_assignee_type_code = 'GROUP'
341 THEN
342
343 FOR l_grp_rec IN group_assignee_cur (p_assignee_id)
344 LOOP
345 x_assignee_rec.assignee_id := p_assignee_id ;
346 x_assignee_rec.assignee_type_code := p_assignee_type_code ;
347 x_assignee_rec.assignee_type := l_grp_rec.assignee_type ;
348 x_assignee_rec.assignee_name := l_grp_rec.assignee_name ;
349 x_assignee_rec.assignee_company := l_grp_rec.assignee_company ;
350 x_assignee_rec.assignee_role_obj_name := NULL ;
351 END LOOP ;
352
353 ELSIF p_assignee_type_code = 'CHANGE_POLICY'
354 THEN
355
356 FOR l_policy_rec IN chg_policy_assignee_cur (TO_CHAR(p_assignee_id))
357 LOOP
358 x_assignee_rec.assignee_id := p_assignee_id ;
359 x_assignee_rec.assignee_type_code := p_assignee_type_code ;
360 x_assignee_rec.assignee_type := l_policy_rec.assignee_type ;
361 x_assignee_rec.assignee_name := l_policy_rec.assignee_name ;
362 x_assignee_rec.assignee_company := l_policy_rec.assignee_company ;
363 x_assignee_rec.assignee_role_obj_name := NULL ;
364 END LOOP ;
365
366 END IF ;
367
368
369 G_ASSIGNEE_TBL(l_idx).assignee_id := x_assignee_rec.assignee_id ;
370 G_ASSIGNEE_TBL(l_idx).assignee_type_code := x_assignee_rec.assignee_type_code ;
371 G_ASSIGNEE_TBL(l_idx).assignee_type := x_assignee_rec.assignee_type ;
372 G_ASSIGNEE_TBL(l_idx).assignee_name := x_assignee_rec.assignee_name ;
373 G_ASSIGNEE_TBL(l_idx).assignee_company := x_assignee_rec.assignee_company ;
374 G_ASSIGNEE_TBL(l_idx).assignee_role_obj_name := x_assignee_rec.assignee_role_obj_name ;
375
376
377 END IF ;
378
379 -- 4. Set current rec
380 IF p_current_flag IS NULL OR p_current_flag = 'Y' THEN
381 g_assignee_rec := x_assignee_rec ;
382 ELSE
383 g_orig_assignee_rec := x_assignee_rec ;
384 END IF ;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 NULL ;
389
390 END GetAssigneeRec ;
391
392
393 FUNCTION Get_Assignee_Name
394 ( p_assignee_id IN NUMBER
395 , p_assignee_type_code IN VARCHAR2
396 , p_current_flag IN VARCHAR2 := NULL -- Y or NULL regarded as Current
397 )
398 RETURN VARCHAR2
399 IS
400
401 l_assignee_rec Eng_Change_Route_People_Util.Assignee_Rec_Type ;
402
403 BEGIN
404
405 IF p_assignee_id IS NOT NULL AND p_assignee_type_code IS NOT NULL
406 THEN
407 -- Get assignee rec from cache rec or database rec
408 GetAssigneeRec( p_assignee_id => p_assignee_id
409 , p_assignee_type_code => p_assignee_type_code
410 , p_current_flag => p_current_flag
411 , x_assignee_rec => l_assignee_rec ) ;
412
413 RETURN l_assignee_rec.assignee_name ;
414
415 ELSE
416 RETURN NULL ;
417
418 END IF ;
419
420 EXCEPTION
421 WHEN OTHERS THEN
422 RETURN NULL ;
423
424 END Get_Assignee_Name ;
425
426
427 FUNCTION Get_Assignee_Company
428 ( p_assignee_id IN NUMBER
429 , p_assignee_type_code IN VARCHAR2
430 , p_current_flag IN VARCHAR2 := NULL -- Y or NULL regarded as Current
431 )
432 RETURN VARCHAR2
433 IS
434
435 l_assignee_rec Eng_Change_Route_People_Util.Assignee_Rec_Type ;
436
437 BEGIN
438
439 IF p_assignee_id IS NOT NULL AND p_assignee_type_code = 'PERSON'
440 THEN
441 -- Get assignee rec from cache rec or database rec
442 GetAssigneeRec( p_assignee_id => p_assignee_id
443 , p_assignee_type_code => p_assignee_type_code
444 , p_current_flag => p_current_flag
445 , x_assignee_rec => l_assignee_rec ) ;
446
447 RETURN l_assignee_rec.assignee_company ;
448
449 ELSE
450 RETURN NULL ;
451
452 END IF ;
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 RETURN NULL ;
457
458 END Get_Assignee_Company ;
459
460
461 FUNCTION Get_Assignee_Type
462 ( p_assignee_id IN NUMBER
463 , p_assignee_type_code IN VARCHAR2
464 , p_current_flag IN VARCHAR2 := NULL -- Y or NULL regarded as Current
465 )
466 RETURN VARCHAR2
467 IS
468
469 l_assignee_rec Eng_Change_Route_People_Util.Assignee_Rec_Type ;
470
471 BEGIN
472
473 IF p_assignee_id IS NOT NULL AND p_assignee_type_code IS NOT NULL
474 THEN
475
476 -- Get assignee rec from cache rec or database rec
477 GetAssigneeRec( p_assignee_id => p_assignee_id
478 , p_assignee_type_code => p_assignee_type_code
479 , p_current_flag => p_current_flag
480 , x_assignee_rec => l_assignee_rec ) ;
481
482
483 IF l_assignee_rec.assignee_role_obj_name IS NULL
484 THEN
485
486 RETURN l_assignee_rec.assignee_type ;
487 ELSE
488
489 RETURN l_assignee_rec.assignee_role_obj_name ;
490
491 END IF ;
492
493 ELSIF p_assignee_id IS NULL AND p_assignee_type_code = 'PERSON'
494 THEN
495
496 -- Get assignee rec from cache rec or database rec
497 GetAssigneeRec( p_assignee_id => -1
498 , p_assignee_type_code => p_assignee_type_code
499 , p_current_flag => p_current_flag
500 , x_assignee_rec => l_assignee_rec ) ;
501
502 RETURN l_assignee_rec.assignee_type ;
503
504 ELSE
505 RETURN NULL ;
506
507 END IF ;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 RETURN NULL ;
512
513 END Get_Assignee_Type ;
514
515
516
517 /********************************************************************
518 * API Type : Private Copy People API
519 * Purpose : This api will copy instances for Route People
520 *********************************************************************/
521 PROCEDURE COPY_PEOPLE (
522 P_FROM_STEP_ID IN NUMBER ,
523 P_TO_STEP_ID IN NUMBER ,
524 P_USER_ID IN NUMBER := NULL ,
525 P_API_CALLER IN VARCHAR2 := NULL
526 )
527 IS
528
529 -- Added check PARENT_ROUTE_PEOPLE_ID IS NULL
530 -- not to copy transferred assignee by ntf reassignment
531 cursor c is select
532 ROUTE_PEOPLE_ID,
533 STEP_ID,
534 ASSIGNEE_ID,
535 ASSIGNEE_TYPE_CODE,
536 ADHOC_PEOPLE_FLAG,
537 WF_NOTIFICATION_ID,
538 RESPONSE_CODE,
539 RESPONSE_DATE,
540 REQUEST_ID,
541 ORIGINAL_SYSTEM_REFERENCE,
542 PROGRAM_ID,
543 PROGRAM_APPLICATION_ID,
544 PROGRAM_UPDATE_DATE,
545 ORIGINAL_ASSIGNEE_ID,
546 ORIGINAL_ASSIGNEE_TYPE_CODE,
547 RESPONSE_CONDITION_CODE,
548 RESPONSE_DESCRIPTION
549 from ENG_CHANGE_ROUTE_PEOPLE_VL
550 where STEP_ID = P_FROM_STEP_ID
551 and PARENT_ROUTE_PEOPLE_ID IS NULL
552 ;
553
554 -- No need to lock
555 -- for update of ROUTE_PEOPLE_ID nowait;
556
557
558 -- General variables
559 l_fnd_user_id NUMBER ;
560 l_fnd_login_id NUMBER ;
561 l_language VARCHAR2(4) ;
562 l_rowid ROWID;
563
564 l_people_id NUMBER ;
565
566
567 BEGIN
568
569 -- Initialize Vars
570 l_fnd_user_id := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
571 l_fnd_login_id := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
572 l_language := userenv('LANG');
573
574 -- Real code starts here
575 -- FND_PROFILE package is not available for workflow (WF),
576 -- therefore manually set WHO column values
577 IF p_api_caller = 'WF' THEN
578 l_fnd_user_id := p_user_id;
579 l_fnd_login_id := '';
580 END IF;
581
582
583 IF l_fnd_user_id IS NULL THEN
584
585 l_fnd_user_id := -10000 ;
586
587 END IF ;
588
589
590 for recinfo in c loop
591
592 -- Get Next Sequence Value for STEP_ID
593 SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL into l_people_id
594 FROM DUAL;
595
596 INSERT_ROW (
597 X_ROWID => l_rowid,
598 X_ROUTE_PEOPLE_ID => l_people_id ,
599 X_STEP_ID => P_TO_STEP_ID ,
600 X_ASSIGNEE_ID => recinfo.ASSIGNEE_ID,
601 X_ASSIGNEE_TYPE_CODE => recinfo.ASSIGNEE_TYPE_CODE,
602 X_ADHOC_PEOPLE_FLAG => recinfo.ADHOC_PEOPLE_FLAG,
603 X_WF_NOTIFICATION_ID => NULL ,
604 X_RESPONSE_CODE => NULL ,
605 X_RESPONSE_DATE => NULL ,
606 X_REQUEST_ID => recinfo.REQUEST_ID,
607 X_ORIGINAL_SYSTEM_REFERENCE => recinfo.ORIGINAL_SYSTEM_REFERENCE,
608 X_RESPONSE_DESCRIPTION => NULL,
609 X_CREATION_DATE => SYSDATE,
610 X_CREATED_BY => l_fnd_user_id,
611 X_LAST_UPDATE_DATE => SYSDATE,
612 X_LAST_UPDATED_BY => l_fnd_user_id,
613 X_LAST_UPDATE_LOGIN => l_fnd_login_id,
614 X_PROGRAM_ID => recinfo.PROGRAM_ID,
615 X_PROGRAM_APPLICATION_ID => recinfo.PROGRAM_APPLICATION_ID,
616 X_PROGRAM_UPDATE_DATE => recinfo.PROGRAM_UPDATE_DATE,
617 X_ORIGINAL_ASSIGNEE_ID => recinfo.ORIGINAL_ASSIGNEE_ID,
618 X_ORIGINAL_ASSIGNEE_TYPE_CODE => recinfo.ORIGINAL_ASSIGNEE_TYPE_CODE,
619 X_RESPONSE_CONDITION_CODE => recinfo.RESPONSE_CONDITION_CODE,
620 X_PARENT_ROUTE_PEOPLE_ID => NULL
621 ) ;
622
623 --
624 --
625 -- Call Assoc's Copy Row Procedures
626 --
627 Eng_Change_Route_Assocs_Util.COPY_ASSOCIATIONS(
628 P_FROM_PEOPLE_ID => recinfo.ROUTE_PEOPLE_ID,
629 P_TO_PEOPLE_ID => l_people_id ,
630 P_USER_ID => l_fnd_user_id,
631 P_API_CALLER => P_API_CALLER
632 ) ;
633
634 end loop ;
635
636
637 END COPY_PEOPLE ;
638
639
640
641
642 /********************************************************************
643 * API Type : Private Table Hander APIs
644 * Purpose : Those APIs are private
645 * Table Hander for TL Entity Object:
646 * ENG_CHANGE_ROUTE_PEOPLE_VL
647 * PROCEDURE INSERT_ROW;
648 * PROCEDURE LOCK_ROW;
649 * PROCEDURE UPDATE_ROW;
650 * PROCEDURE DELETE_ROW;
651 *********************************************************************/
652 PROCEDURE INSERT_ROW (
653 X_ROWID IN OUT NOCOPY VARCHAR2,
654 X_ROUTE_PEOPLE_ID IN NUMBER,
655 X_STEP_ID IN NUMBER,
656 X_ASSIGNEE_ID IN NUMBER,
657 X_ASSIGNEE_TYPE_CODE IN VARCHAR2,
658 X_ADHOC_PEOPLE_FLAG IN VARCHAR2,
659 X_WF_NOTIFICATION_ID IN NUMBER,
660 X_RESPONSE_CODE IN VARCHAR2,
661 X_RESPONSE_DATE IN DATE,
662 X_REQUEST_ID IN NUMBER,
663 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
664 X_RESPONSE_DESCRIPTION IN VARCHAR2,
665 X_CREATION_DATE IN DATE,
666 X_CREATED_BY IN NUMBER,
667 X_LAST_UPDATE_DATE IN DATE,
668 X_LAST_UPDATED_BY IN NUMBER,
669 X_LAST_UPDATE_LOGIN IN NUMBER,
670 X_PROGRAM_ID IN NUMBER,
671 X_PROGRAM_APPLICATION_ID IN NUMBER,
672 X_PROGRAM_UPDATE_DATE IN DATE,
673 X_ORIGINAL_ASSIGNEE_ID IN NUMBER,
674 X_ORIGINAL_ASSIGNEE_TYPE_CODE IN VARCHAR2,
675 X_RESPONSE_CONDITION_CODE IN VARCHAR2,
676 X_PARENT_ROUTE_PEOPLE_ID IN NUMBER
677 )
678 IS
679
680 cursor C is select ROWID from ENG_CHANGE_ROUTE_PEOPLE
681 where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
682 ;
683
684
685 BEGIN
686
687 insert into ENG_CHANGE_ROUTE_PEOPLE (
688 ROUTE_PEOPLE_ID,
689 STEP_ID,
690 ASSIGNEE_ID,
691 ASSIGNEE_TYPE_CODE,
692 ADHOC_PEOPLE_FLAG,
693 WF_NOTIFICATION_ID,
694 RESPONSE_CODE,
695 RESPONSE_DATE,
696 REQUEST_ID,
697 ORIGINAL_SYSTEM_REFERENCE,
698 CREATION_DATE,
699 CREATED_BY,
700 LAST_UPDATE_DATE,
701 LAST_UPDATED_BY,
702 LAST_UPDATE_LOGIN,
703 PROGRAM_ID,
704 PROGRAM_APPLICATION_ID,
705 PROGRAM_UPDATE_DATE,
706 ORIGINAL_ASSIGNEE_ID,
707 ORIGINAL_ASSIGNEE_TYPE_CODE,
708 RESPONSE_CONDITION_CODE,
709 PARENT_ROUTE_PEOPLE_ID
710 ) values (
711 X_ROUTE_PEOPLE_ID,
712 X_STEP_ID,
713 X_ASSIGNEE_ID,
714 X_ASSIGNEE_TYPE_CODE,
715 X_ADHOC_PEOPLE_FLAG,
716 X_WF_NOTIFICATION_ID,
717 X_RESPONSE_CODE,
718 X_RESPONSE_DATE,
719 X_REQUEST_ID,
720 X_ORIGINAL_SYSTEM_REFERENCE,
721 X_CREATION_DATE,
722 X_CREATED_BY,
723 X_LAST_UPDATE_DATE,
724 X_LAST_UPDATED_BY,
725 X_LAST_UPDATE_LOGIN,
726 X_PROGRAM_ID,
727 X_PROGRAM_APPLICATION_ID,
728 X_PROGRAM_UPDATE_DATE,
729 X_ORIGINAL_ASSIGNEE_ID,
730 X_ORIGINAL_ASSIGNEE_TYPE_CODE,
731 X_RESPONSE_CONDITION_CODE,
732 X_PARENT_ROUTE_PEOPLE_ID
733 );
734
735 insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
736 ROUTE_PEOPLE_ID,
737 CREATION_DATE,
738 CREATED_BY,
739 LAST_UPDATE_DATE,
740 LAST_UPDATED_BY,
741 LAST_UPDATE_LOGIN,
742 RESPONSE_DESCRIPTION,
743 LANGUAGE,
744 SOURCE_LANG
745 ) select
746 X_ROUTE_PEOPLE_ID,
747 X_CREATION_DATE,
748 X_CREATED_BY,
749 X_LAST_UPDATE_DATE,
750 X_LAST_UPDATED_BY,
751 X_LAST_UPDATE_LOGIN,
752 X_RESPONSE_DESCRIPTION,
753 L.LANGUAGE_CODE,
754 userenv('LANG')
755 from FND_LANGUAGES L
756 where L.INSTALLED_FLAG in ('I', 'B')
757 and not exists
758 (select NULL
759 from ENG_CHANGE_ROUTE_PEOPLE_TL T
760 where T.ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
761 and T.LANGUAGE = L.LANGUAGE_CODE);
762
763 open c;
764 fetch c into X_ROWID;
765 if (c%notfound) then
766 close c;
767 raise no_data_found;
768 end if;
769 close c;
770
771 END INSERT_ROW;
772
773 PROCEDURE LOCK_ROW (
774 X_ROUTE_PEOPLE_ID IN NUMBER,
775 X_STEP_ID IN NUMBER,
776 X_ASSIGNEE_ID IN NUMBER,
777 X_ASSIGNEE_TYPE_CODE IN VARCHAR2,
778 X_ADHOC_PEOPLE_FLAG IN VARCHAR2,
779 X_WF_NOTIFICATION_ID IN NUMBER,
780 X_RESPONSE_CODE IN VARCHAR2,
781 X_RESPONSE_DATE IN DATE,
782 X_REQUEST_ID IN NUMBER,
783 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
784 X_RESPONSE_DESCRIPTION IN VARCHAR2,
785 X_PROGRAM_ID IN NUMBER,
786 X_PROGRAM_APPLICATION_ID IN NUMBER,
787 X_PROGRAM_UPDATE_DATE IN DATE,
788 X_ORIGINAL_ASSIGNEE_ID IN NUMBER,
789 X_ORIGINAL_ASSIGNEE_TYPE_CODE IN VARCHAR2,
790 X_RESPONSE_CONDITION_CODE IN VARCHAR2,
791 X_PARENT_ROUTE_PEOPLE_ID IN NUMBER
792 )
793 IS
794
795 cursor c is select
796 STEP_ID,
797 ASSIGNEE_ID,
798 ASSIGNEE_TYPE_CODE,
799 ADHOC_PEOPLE_FLAG,
800 WF_NOTIFICATION_ID,
801 RESPONSE_CODE,
802 RESPONSE_DATE,
803 REQUEST_ID,
804 ORIGINAL_SYSTEM_REFERENCE,
805 PROGRAM_ID,
806 PROGRAM_APPLICATION_ID,
807 PROGRAM_UPDATE_DATE,
808 ORIGINAL_ASSIGNEE_ID,
809 ORIGINAL_ASSIGNEE_TYPE_CODE,
810 RESPONSE_CONDITION_CODE,
811 PARENT_ROUTE_PEOPLE_ID
812 from ENG_CHANGE_ROUTE_PEOPLE
813 where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
814 for update of ROUTE_PEOPLE_ID nowait;
815 recinfo c%rowtype;
816
817 cursor c1 is select
818 RESPONSE_DESCRIPTION,
819 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
820 from ENG_CHANGE_ROUTE_PEOPLE_TL
821 where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
822 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
823 for update of ROUTE_PEOPLE_ID nowait;
824
825
826 BEGIN
827
828
829 open c;
830 fetch c into recinfo;
831 if (c%notfound) then
832 close c;
833 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
834 app_exception.raise_exception;
835 end if;
836 close c;
837 if ( (recinfo.STEP_ID = X_STEP_ID)
838 AND (recinfo.ASSIGNEE_ID = X_ASSIGNEE_ID)
839 AND (recinfo.ASSIGNEE_TYPE_CODE = X_ASSIGNEE_TYPE_CODE)
840 AND (recinfo.ADHOC_PEOPLE_FLAG = X_ADHOC_PEOPLE_FLAG)
841 AND ((recinfo.WF_NOTIFICATION_ID = X_WF_NOTIFICATION_ID)
842 OR ((recinfo.WF_NOTIFICATION_ID is null) AND (X_WF_NOTIFICATION_ID is null)))
843 AND ((recinfo.RESPONSE_CODE = X_RESPONSE_CODE)
844 OR ((recinfo.RESPONSE_CODE is null) AND (X_RESPONSE_CODE is null)))
845 AND ((recinfo.RESPONSE_DATE = X_RESPONSE_DATE)
846 OR ((recinfo.RESPONSE_DATE is null) AND (X_RESPONSE_DATE is null)))
847 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
848 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
849 AND ((recinfo.ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE)
850 OR ((recinfo.ORIGINAL_SYSTEM_REFERENCE is null) AND (X_ORIGINAL_SYSTEM_REFERENCE is null)))
851 AND ((recinfo.ORIGINAL_ASSIGNEE_ID = X_ORIGINAL_ASSIGNEE_ID)
852 OR ((recinfo.ORIGINAL_ASSIGNEE_ID is null) AND (X_ORIGINAL_ASSIGNEE_ID is null)))
853 AND ((recinfo.ORIGINAL_ASSIGNEE_TYPE_CODE = X_ORIGINAL_ASSIGNEE_TYPE_CODE)
854 OR ((recinfo.ORIGINAL_ASSIGNEE_TYPE_CODE is null) AND (X_ORIGINAL_ASSIGNEE_TYPE_CODE is null)))
855 AND ((recinfo.RESPONSE_CONDITION_CODE = X_RESPONSE_CONDITION_CODE)
856 OR ((recinfo.RESPONSE_CONDITION_CODE is null) AND (X_RESPONSE_CONDITION_CODE is null)))
857 AND ((recinfo.PARENT_ROUTE_PEOPLE_ID = X_PARENT_ROUTE_PEOPLE_ID)
858 OR ((recinfo.PARENT_ROUTE_PEOPLE_ID is null) AND (X_PARENT_ROUTE_PEOPLE_ID is null)))
859 -- followings are not generated by tool
860 -- AND ((recinfo.PROGRAM_ID= X_PROGRAM_ID)
861 -- OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
862 -- AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
863 -- OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
864 -- AND ((recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE)
865 -- OR ((recinfo.PROGRAM_UPDATE_DATE is null) AND (X_PROGRAM_UPDATE_DATE is null)))
866 ) then
867 null;
868 else
869 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
870 app_exception.raise_exception;
871 end if;
872
873 for tlinfo in c1 loop
874 if (tlinfo.BASELANG = 'Y') then
875 if ( ((tlinfo.RESPONSE_DESCRIPTION = X_RESPONSE_DESCRIPTION)
876 OR ((tlinfo.RESPONSE_DESCRIPTION is null) AND (X_RESPONSE_DESCRIPTION is null)))
877 ) then
878 null;
879 else
880 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
881 app_exception.raise_exception;
882 end if;
883 end if;
884 end loop;
885 return;
886
887 END LOCK_ROW ;
888
889
890
891 PROCEDURE UPDATE_ROW (
892 X_ROUTE_PEOPLE_ID IN NUMBER,
893 X_STEP_ID IN NUMBER,
894 X_ASSIGNEE_ID IN NUMBER,
895 X_ASSIGNEE_TYPE_CODE IN VARCHAR2,
896 X_ADHOC_PEOPLE_FLAG IN VARCHAR2,
897 X_WF_NOTIFICATION_ID IN NUMBER,
898 X_RESPONSE_CODE IN VARCHAR2,
899 X_RESPONSE_DATE IN DATE,
900 X_REQUEST_ID IN NUMBER,
901 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
902 X_RESPONSE_DESCRIPTION IN VARCHAR2,
903 X_LAST_UPDATE_DATE IN DATE,
904 X_LAST_UPDATED_BY IN NUMBER,
905 X_LAST_UPDATE_LOGIN IN NUMBER,
906 X_PROGRAM_ID IN NUMBER,
907 X_PROGRAM_APPLICATION_ID IN NUMBER,
908 X_PROGRAM_UPDATE_DATE IN DATE,
909 X_ORIGINAL_ASSIGNEE_ID IN NUMBER,
910 X_ORIGINAL_ASSIGNEE_TYPE_CODE IN VARCHAR2,
911 X_RESPONSE_CONDITION_CODE IN VARCHAR2,
912 X_PARENT_ROUTE_PEOPLE_ID IN NUMBER
913 )
914 IS
915
916 BEGIN
917
918 update ENG_CHANGE_ROUTE_PEOPLE set
919 STEP_ID = X_STEP_ID,
920 ASSIGNEE_ID = X_ASSIGNEE_ID,
921 ASSIGNEE_TYPE_CODE = X_ASSIGNEE_TYPE_CODE,
922 ADHOC_PEOPLE_FLAG = X_ADHOC_PEOPLE_FLAG,
923 WF_NOTIFICATION_ID = X_WF_NOTIFICATION_ID,
924 RESPONSE_CODE = X_RESPONSE_CODE,
925 RESPONSE_DATE = X_RESPONSE_DATE,
926 REQUEST_ID = X_REQUEST_ID,
927 ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE,
928 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
929 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
930 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
931 PROGRAM_ID = X_PROGRAM_ID,
932 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
933 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
934 ORIGINAL_ASSIGNEE_ID = X_ORIGINAL_ASSIGNEE_ID,
935 ORIGINAL_ASSIGNEE_TYPE_CODE = X_ORIGINAL_ASSIGNEE_TYPE_CODE,
936 RESPONSE_CONDITION_CODE = X_RESPONSE_CONDITION_CODE,
937 PARENT_ROUTE_PEOPLE_ID = PARENT_ROUTE_PEOPLE_ID
938 where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID;
939
940 if (sql%notfound) then
941 raise no_data_found;
942 end if;
943
944 update ENG_CHANGE_ROUTE_PEOPLE_TL set
945 RESPONSE_DESCRIPTION = X_RESPONSE_DESCRIPTION,
946 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
947 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
948 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
949 SOURCE_LANG = userenv('LANG')
950 where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
951 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
952
953 if (sql%notfound) then
954 raise no_data_found;
955 end if;
956
957 END UPDATE_ROW;
958
959
960
961 PROCEDURE DELETE_ROW (
962 X_ROUTE_PEOPLE_ID IN NUMBER
963 )
964 IS
965
966
967 BEGIN
968
969
970 delete from ENG_CHANGE_ROUTE_PEOPLE_TL
971 where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID;
972
973 if (sql%notfound) then
974 raise no_data_found;
975 end if;
976
977 delete from ENG_CHANGE_ROUTE_PEOPLE
978 where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID;
979
980 if (sql%notfound) then
981 raise no_data_found;
982 end if;
983
984 END DELETE_ROW ;
985
986
987 PROCEDURE ADD_LANGUAGE
988 IS
989
990 BEGIN
991
992 delete from ENG_CHANGE_ROUTE_PEOPLE_TL T
993 where not exists
994 (select NULL
995 from ENG_CHANGE_ROUTE_PEOPLE B
996 where B.ROUTE_PEOPLE_ID = T.ROUTE_PEOPLE_ID
997 );
998
999 update ENG_CHANGE_ROUTE_PEOPLE_TL T set (
1000 RESPONSE_DESCRIPTION
1001 ) = (select
1002 B.RESPONSE_DESCRIPTION
1003 from ENG_CHANGE_ROUTE_PEOPLE_TL B
1004 where B.ROUTE_PEOPLE_ID = T.ROUTE_PEOPLE_ID
1005 and B.LANGUAGE = T.SOURCE_LANG)
1006 where (
1007 T.ROUTE_PEOPLE_ID,
1008 T.LANGUAGE
1009 ) in (select
1010 SUBT.ROUTE_PEOPLE_ID,
1011 SUBT.LANGUAGE
1012 from ENG_CHANGE_ROUTE_PEOPLE_TL SUBB, ENG_CHANGE_ROUTE_PEOPLE_TL SUBT
1013 where SUBB.ROUTE_PEOPLE_ID = SUBT.ROUTE_PEOPLE_ID
1014 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1015 and (SUBB.RESPONSE_DESCRIPTION <> SUBT.RESPONSE_DESCRIPTION
1016 or (SUBB.RESPONSE_DESCRIPTION is null and SUBT.RESPONSE_DESCRIPTION is not null)
1017 or (SUBB.RESPONSE_DESCRIPTION is not null and SUBT.RESPONSE_DESCRIPTION is null)
1018 ));
1019
1020 insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
1021 ROUTE_PEOPLE_ID,
1022 CREATION_DATE,
1023 CREATED_BY,
1024 LAST_UPDATE_DATE,
1025 LAST_UPDATED_BY,
1026 LAST_UPDATE_LOGIN,
1027 RESPONSE_DESCRIPTION,
1028 LANGUAGE,
1029 SOURCE_LANG
1030 ) select
1031 B.ROUTE_PEOPLE_ID,
1032 B.CREATION_DATE,
1033 B.CREATED_BY,
1034 B.LAST_UPDATE_DATE,
1035 B.LAST_UPDATED_BY,
1036 B.LAST_UPDATE_LOGIN,
1037 B.RESPONSE_DESCRIPTION,
1038 L.LANGUAGE_CODE,
1039 B.SOURCE_LANG
1040 from ENG_CHANGE_ROUTE_PEOPLE_TL B, FND_LANGUAGES L
1041 where L.INSTALLED_FLAG in ('I', 'B')
1042 and B.LANGUAGE = userenv('LANG')
1043 and not exists
1044 (select NULL
1045 from ENG_CHANGE_ROUTE_PEOPLE_TL T
1046 where T.ROUTE_PEOPLE_ID = B.ROUTE_PEOPLE_ID
1047 and T.LANGUAGE = L.LANGUAGE_CODE);
1048
1049 END ADD_LANGUAGE;
1050
1051
1052 /********************************************************************
1053 * API Type : Public APIs
1054 * Purpose : Those APIs are public
1055 *********************************************************************/
1056
1057
1058 END Eng_Change_Route_People_Util ;