[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ROUTE_UTIL
Source
1 PACKAGE BODY Eng_Change_Route_Util AS
2 /* $Header: ENGURTEB.pls 120.1 2006/01/12 19:24:01 mkimizuk noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'Eng_Change_Route_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(30) ;
11 g_debug_errmesg VARCHAR2(240);
12
13
14 /********************************************************************
15 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
16 * Write_Debug
17 * Parameters IN :
18 * Parameters OUT:
19 * Purpose : These procedures are for test and debug
20 *********************************************************************/
21 -- Open_Debug_Session
22 PROCEDURE Open_Debug_Session
23 ( p_output_dir IN VARCHAR2 := NULL
24 , p_file_name IN VARCHAR2 := NULL
25 )
26 IS
27 l_found NUMBER := 0;
28 l_utl_file_dir VARCHAR2(2000);
29
30 BEGIN
31
32 IF p_output_dir IS NOT NULL THEN
33 g_output_dir := p_output_dir ;
34
35 END IF ;
36
37 IF p_file_name IS NOT NULL THEN
38 g_debug_filename := p_file_name ;
39 END IF ;
40
41 IF g_output_dir IS NULL
42 THEN
43
44 g_output_dir := FND_PROFILE.VALUE('ECX_UTL_LOG_DIR') ;
45
46 END IF;
47
48 select value
49 INTO l_utl_file_dir
50 FROM v$parameter
51 WHERE name = 'utl_file_dir';
52
53 l_found := INSTR(l_utl_file_dir, g_output_dir);
54
55 IF l_found = 0
56 THEN
57 RETURN;
58 END IF;
59
60 -- Set Default Debug File Name
61 IF g_debug_filename IS NULL THEN
62 g_debug_filename := 'Eng_Change_Route_Util.log' ;
63 END IF ;
64
65 g_debug_file := utl_file.fopen( g_output_dir
66 , g_debug_filename
67 , 'w');
68 g_debug_flag := TRUE ;
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
73 g_debug_flag := FALSE;
74
75 END Open_Debug_Session ;
76
77 -- Close Debug_Session
78 PROCEDURE Close_Debug_Session
79 IS
80 BEGIN
81 IF utl_file.is_open(g_debug_file)
82 THEN
83 utl_file.fclose(g_debug_file);
84 END IF ;
85
86 EXCEPTION
87 WHEN OTHERS THEN
88 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
89 g_debug_flag := FALSE;
90
91 END Close_Debug_Session ;
92
93 -- Test Debug
94 PROCEDURE Write_Debug
95 ( p_debug_message IN VARCHAR2 )
96 IS
97 BEGIN
98
99 IF utl_file.is_open(g_debug_file)
100 THEN
101 utl_file.put_line(g_debug_file, p_debug_message);
102 END IF ;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
107 g_debug_flag := FALSE;
108
109 END Write_Debug;
110
111
112
113 /********************************************************************
114 * API Type : Refresh Route API
115 * Scope : Oracle (for Oracle Applications development use only)
116 * Purpose : This api will create another instance of Route specified
117 * as param and set original Route as History
118 *********************************************************************/
119 PROCEDURE REFRESH_ROUTE(
120 X_NEW_ROUTE_ID OUT NOCOPY NUMBER,
121 P_ROUTE_ID IN NUMBER,
122 P_USER_ID IN NUMBER := NULL ,
123 P_API_CALLER IN VARCHAR2 := NULL
124 )
125 IS
126
127 BEGIN
128
129
130 -- Get Next Sequence Value for ROUTE_ID
131 SELECT ENG_CHANGE_ROUTES_S.NEXTVAL into X_NEW_ROUTE_ID
132 FROM DUAL;
133
134 -- Call COPY_ROUTE Prodedure
135 COPY_ROUTE (
136 X_TO_ROUTE_ID => X_NEW_ROUTE_ID ,
137 P_FROM_ROUTE_ID => P_ROUTE_ID,
138 P_USER_ID => P_USER_ID ,
139 P_API_CALLER => P_API_CALLER
140 ) ;
141
142 -- Set Original Route to History
143 UPDATE ENG_CHANGE_ROUTES
144 SET TEMPLATE_FLAG = 'H'
145 WHERE ROUTE_ID = P_ROUTE_ID ;
146
147
148 END REFRESH_ROUTE ;
149
150
151 /********************************************************************
152 * API Type : Private Copy Route API
153 * Purpose : This api will create another instance of Route
154 *********************************************************************/
155 PROCEDURE COPY_ROUTE (
156 X_TO_ROUTE_ID IN OUT NOCOPY NUMBER ,
157 P_FROM_ROUTE_ID IN NUMBER ,
158 P_USER_ID IN NUMBER := NULL ,
159 P_API_CALLER IN VARCHAR2 := NULL
160 )
161 IS
162
163 cursor c is select
164 TEMPLATE_FLAG,
165 OWNER_ID,
166 FIXED_FLAG,
167 OBJECT_NAME,
168 OBJECT_ID1,
169 OBJECT_ID2,
170 OBJECT_ID3,
171 OBJECT_ID4,
172 OBJECT_ID5,
173 APPLIED_TEMPLATE_ID,
174 WF_ITEM_TYPE,
175 WF_ITEM_KEY,
176 WF_PROCESS_NAME,
177 STATUS_CODE,
178 ROUTE_START_DATE,
179 ROUTE_END_DATE,
180 CHANGE_REVISION,
181 ATTRIBUTE_CATEGORY,
182 ATTRIBUTE1,
183 ATTRIBUTE2,
184 ATTRIBUTE3,
185 ATTRIBUTE4,
186 ATTRIBUTE5,
187 ATTRIBUTE6,
188 ATTRIBUTE7,
189 ATTRIBUTE8,
190 ATTRIBUTE9,
191 ATTRIBUTE10,
192 ATTRIBUTE11,
193 ATTRIBUTE12,
194 ATTRIBUTE13,
195 ATTRIBUTE14,
196 ATTRIBUTE15,
197 REQUEST_ID,
198 PROGRAM_ID,
199 PROGRAM_APPLICATION_ID,
200 PROGRAM_UPDATE_DATE,
201 ORIGINAL_SYSTEM_REFERENCE,
202 CLASSIFICATION_CODE,
203 ROUTE_TYPE_CODE
204 from ENG_CHANGE_ROUTES
205 where ROUTE_ID = P_FROM_ROUTE_ID ;
206
207 -- No need to lock
208 -- for update of ROUTE_ID nowait;
209
210 recinfo c%rowtype;
211
212 cursor c1 is select
213 ROUTE_NAME,
214 ROUTE_DESCRIPTION,
215 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
216 from ENG_CHANGE_ROUTES_TL
217 where ROUTE_ID = P_FROM_ROUTE_ID
218 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;
219
220 -- No need to lock
221 -- for update of ROUTE_ID nowait;
222
223 tlrecinfo c1%rowtype;
224
225 -- General variables
226 l_fnd_user_id NUMBER ;
227 l_fnd_login_id NUMBER ;
228 l_language VARCHAR2(4) ;
229 l_rowid ROWID;
230
231
232 BEGIN
233
234 -- Initialize Vars
235 l_fnd_user_id := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
236 l_fnd_login_id := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
237 l_language := userenv('LANG');
238
239
240
241 -- Real code starts here
242 -- FND_PROFILE package is not available for workflow (WF),
243 -- therefore manually set WHO column values
244 IF p_api_caller = 'WF' THEN
245 l_fnd_user_id := p_user_id;
246 l_fnd_login_id := '';
247 END IF;
248
249
250 IF l_fnd_user_id IS NULL THEN
251
252 l_fnd_user_id := -10000 ;
253
254 END IF ;
255
256 open c;
257 fetch c into recinfo;
258 if (c%notfound) then
259 close c;
260 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
261 app_exception.raise_exception;
262 end if;
263 close c;
264
265 open c1;
266 fetch c1 into tlrecinfo;
267 if (c1%notfound) then
268 close c1;
269 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
270 app_exception.raise_exception;
271 end if;
272 close c1;
273
274
275 INSERT_ROW (
276 X_ROWID => l_rowid,
277 X_ROUTE_ID => X_TO_ROUTE_ID ,
278 X_ROUTE_NAME => tlrecinfo.ROUTE_NAME,
279 X_ROUTE_DESCRIPTION => tlrecinfo.ROUTE_DESCRIPTION,
280 X_TEMPLATE_FLAG => 'N' , -- recinfo.TEMPLATE_FLAG,
281 X_OWNER_ID => recinfo.OWNER_ID,
282 X_FIXED_FLAG => recinfo.FIXED_FLAG,
283 X_OBJECT_NAME => recinfo.OBJECT_NAME,
284 X_OBJECT_ID1 => recinfo.OBJECT_ID1,
285 X_OBJECT_ID2 => recinfo.OBJECT_ID2,
286 X_OBJECT_ID3 => recinfo.OBJECT_ID3,
287 X_OBJECT_ID4 => recinfo.OBJECT_ID4,
288 X_OBJECT_ID5 => recinfo.OBJECT_ID5,
289 X_APPLIED_TEMPLATE_ID => recinfo.APPLIED_TEMPLATE_ID,
290 X_WF_ITEM_TYPE => recinfo.WF_ITEM_TYPE,
291 X_WF_ITEM_KEY => recinfo.WF_ITEM_KEY,
292 X_WF_PROCESS_NAME => recinfo.WF_PROCESS_NAME,
293 X_STATUS_CODE => 'NOT_STARTED' , -- recinfo.STATUS_CODE,
294 X_ROUTE_START_DATE => NULL , -- recinfo.ROUTE_START_DATE,
295 X_ROUTE_END_DATE => NULL , -- recinfo.ROUTE_END_DATE,
296 X_CHANGE_REVISION => recinfo.CHANGE_REVISION,
297 X_CREATION_DATE => SYSDATE,
298 X_CREATED_BY => l_fnd_user_id,
299 X_LAST_UPDATE_DATE => SYSDATE,
300 X_LAST_UPDATED_BY => l_fnd_user_id,
301 X_LAST_UPDATE_LOGIN => l_fnd_login_id,
302 X_ATTRIBUTE_CATEGORY => recinfo.ATTRIBUTE_CATEGORY,
303 X_ATTRIBUTE1 => recinfo.ATTRIBUTE1,
304 X_ATTRIBUTE2 => recinfo.ATTRIBUTE2,
305 X_ATTRIBUTE3 => recinfo.ATTRIBUTE3,
306 X_ATTRIBUTE4 => recinfo.ATTRIBUTE4,
307 X_ATTRIBUTE5 => recinfo.ATTRIBUTE5,
308 X_ATTRIBUTE6 => recinfo.ATTRIBUTE6,
309 X_ATTRIBUTE7 => recinfo.ATTRIBUTE7,
310 X_ATTRIBUTE8 => recinfo.ATTRIBUTE8,
311 X_ATTRIBUTE9 => recinfo.ATTRIBUTE9,
312 X_ATTRIBUTE10 => recinfo.ATTRIBUTE10,
313 X_ATTRIBUTE11 => recinfo.ATTRIBUTE11,
314 X_ATTRIBUTE12 => recinfo.ATTRIBUTE12,
315 X_ATTRIBUTE13 => recinfo.ATTRIBUTE13,
316 X_ATTRIBUTE14 => recinfo.ATTRIBUTE14,
317 X_ATTRIBUTE15 => recinfo.ATTRIBUTE15,
318 X_REQUEST_ID => recinfo.REQUEST_ID,
319 X_PROGRAM_ID => recinfo.PROGRAM_ID,
320 X_PROGRAM_APPLICATION_ID => recinfo.PROGRAM_APPLICATION_ID,
321 X_PROGRAM_UPDATE_DATE => recinfo.PROGRAM_UPDATE_DATE,
322 X_ORIGINAL_SYSTEM_REFERENCE => recinfo.ORIGINAL_SYSTEM_REFERENCE,
323 X_CLASSIFICATION_CODE => recinfo.CLASSIFICATION_CODE,
324 X_ROUTE_TYPE_CODE => recinfo.ROUTE_TYPE_CODE
325 );
326
327 --
328 --
329 -- Call Step's Copy Row Procedures
330 --
331 Eng_Change_Route_Step_Util.COPY_STEPS (
332 P_FROM_ROUTE_ID => P_FROM_ROUTE_ID,
333 P_TO_ROUTE_ID => X_TO_ROUTE_ID ,
334 P_USER_ID => l_fnd_user_id ,
335 P_API_CALLER => P_API_CALLER
336 ) ;
337
338
339 END COPY_ROUTE ;
340
341
342 /********************************************************************
343 * API Type : Private Table Hander APIs
344 * Purpose : Those APIs are private
345 * Table Hander for TL Entity Object: ENG_CHANGE_ROUTES_VL
346 * PROCEDURE INSERT_ROW;
347 * PROCEDURE LOCK_ROW;
348 * PROCEDURE UPDATE_ROW;
349 * PROCEDURE DELETE_ROW;
350 *********************************************************************/
351 PROCEDURE INSERT_ROW (
352 X_ROWID IN OUT NOCOPY VARCHAR2,
353 X_ROUTE_ID IN NUMBER,
354 X_ROUTE_NAME IN VARCHAR2,
355 X_ROUTE_DESCRIPTION IN VARCHAR2,
356 X_TEMPLATE_FLAG IN VARCHAR2,
357 X_OWNER_ID IN NUMBER,
358 X_FIXED_FLAG IN VARCHAR2,
359 X_OBJECT_NAME IN VARCHAR2,
360 X_OBJECT_ID1 IN NUMBER,
361 X_OBJECT_ID2 IN NUMBER,
362 X_OBJECT_ID3 IN NUMBER,
363 X_OBJECT_ID4 IN NUMBER,
364 X_OBJECT_ID5 IN NUMBER,
365 X_APPLIED_TEMPLATE_ID IN NUMBER,
366 X_WF_ITEM_TYPE IN VARCHAR2,
367 X_WF_ITEM_KEY IN VARCHAR2,
368 X_WF_PROCESS_NAME IN VARCHAR2,
369 X_STATUS_CODE IN VARCHAR2,
370 X_ROUTE_START_DATE IN DATE,
371 X_ROUTE_END_DATE IN DATE,
372 X_CHANGE_REVISION IN VARCHAR2,
373 X_CREATION_DATE IN DATE,
374 X_CREATED_BY IN NUMBER,
375 X_LAST_UPDATE_DATE IN DATE,
376 X_LAST_UPDATED_BY IN NUMBER,
377 X_LAST_UPDATE_LOGIN IN NUMBER,
378 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
379 X_ATTRIBUTE1 IN VARCHAR2,
380 X_ATTRIBUTE2 IN VARCHAR2,
381 X_ATTRIBUTE3 IN VARCHAR2,
382 X_ATTRIBUTE4 IN VARCHAR2,
383 X_ATTRIBUTE5 IN VARCHAR2,
384 X_ATTRIBUTE6 IN VARCHAR2,
385 X_ATTRIBUTE7 IN VARCHAR2,
386 X_ATTRIBUTE8 IN VARCHAR2,
387 X_ATTRIBUTE9 IN VARCHAR2,
388 X_ATTRIBUTE10 IN VARCHAR2,
389 X_ATTRIBUTE11 IN VARCHAR2,
390 X_ATTRIBUTE12 IN VARCHAR2,
391 X_ATTRIBUTE13 IN VARCHAR2,
392 X_ATTRIBUTE14 IN VARCHAR2,
393 X_ATTRIBUTE15 IN VARCHAR2,
394 X_REQUEST_ID IN NUMBER,
395 X_PROGRAM_ID IN NUMBER,
396 X_PROGRAM_APPLICATION_ID IN NUMBER,
397 X_PROGRAM_UPDATE_DATE IN DATE,
398 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
399 X_CLASSIFICATION_CODE IN VARCHAR2,
400 X_ROUTE_TYPE_CODE IN VARCHAR2
401 )
402 IS
403
404 CURSOR C IS
405 SELECT ROWID FROM ENG_CHANGE_ROUTES
406 WHERE ROUTE_ID = X_ROUTE_ID
407 ;
408
409 BEGIN
410
411 insert into ENG_CHANGE_ROUTES (
412 ROUTE_ID,
413 TEMPLATE_FLAG,
414 OWNER_ID,
415 FIXED_FLAG,
416 OBJECT_NAME,
417 OBJECT_ID1,
418 OBJECT_ID2,
419 OBJECT_ID3,
420 OBJECT_ID4,
421 OBJECT_ID5,
422 APPLIED_TEMPLATE_ID,
423 WF_ITEM_TYPE,
424 WF_ITEM_KEY,
425 WF_PROCESS_NAME,
426 STATUS_CODE,
427 ROUTE_START_DATE,
428 ROUTE_END_DATE,
429 CHANGE_REVISION,
430 ATTRIBUTE_CATEGORY,
431 ATTRIBUTE1,
432 ATTRIBUTE2,
433 ATTRIBUTE3,
434 ATTRIBUTE4,
435 ATTRIBUTE5,
436 ATTRIBUTE6,
437 ATTRIBUTE7,
438 ATTRIBUTE8,
439 ATTRIBUTE9,
440 ATTRIBUTE10,
441 ATTRIBUTE11,
442 ATTRIBUTE12,
443 ATTRIBUTE13,
444 ATTRIBUTE14,
445 ATTRIBUTE15,
446 REQUEST_ID,
447 CREATION_DATE,
448 CREATED_BY,
449 LAST_UPDATE_DATE,
450 LAST_UPDATED_BY,
451 LAST_UPDATE_LOGIN,
452 PROGRAM_ID,
453 PROGRAM_APPLICATION_ID,
454 PROGRAM_UPDATE_DATE,
455 ORIGINAL_SYSTEM_REFERENCE,
456 CLASSIFICATION_CODE,
457 ROUTE_TYPE_CODE
458 ) values (
459 X_ROUTE_ID,
460 X_TEMPLATE_FLAG,
461 X_OWNER_ID,
462 X_FIXED_FLAG,
463 X_OBJECT_NAME,
464 X_OBJECT_ID1,
465 X_OBJECT_ID2,
466 X_OBJECT_ID3,
467 X_OBJECT_ID4,
468 X_OBJECT_ID5,
469 X_APPLIED_TEMPLATE_ID,
470 X_WF_ITEM_TYPE,
471 X_WF_ITEM_KEY,
472 X_WF_PROCESS_NAME,
473 X_STATUS_CODE,
474 X_ROUTE_START_DATE,
475 X_ROUTE_END_DATE,
476 X_CHANGE_REVISION,
477 X_ATTRIBUTE_CATEGORY,
478 X_ATTRIBUTE1,
479 X_ATTRIBUTE2,
480 X_ATTRIBUTE3,
481 X_ATTRIBUTE4,
482 X_ATTRIBUTE5,
483 X_ATTRIBUTE6,
484 X_ATTRIBUTE7,
485 X_ATTRIBUTE8,
486 X_ATTRIBUTE9,
487 X_ATTRIBUTE10,
488 X_ATTRIBUTE11,
489 X_ATTRIBUTE12,
490 X_ATTRIBUTE13,
491 X_ATTRIBUTE14,
492 X_ATTRIBUTE15,
493 X_REQUEST_ID,
494 X_CREATION_DATE,
495 X_CREATED_BY,
496 X_LAST_UPDATE_DATE,
497 X_LAST_UPDATED_BY,
498 X_LAST_UPDATE_LOGIN,
499 X_PROGRAM_ID,
500 X_PROGRAM_APPLICATION_ID,
501 X_PROGRAM_UPDATE_DATE,
502 X_ORIGINAL_SYSTEM_REFERENCE,
503 X_CLASSIFICATION_CODE,
504 X_ROUTE_TYPE_CODE
505 );
506
507 insert into ENG_CHANGE_ROUTES_TL (
508 LAST_UPDATE_LOGIN,
509 ROUTE_NAME,
513 CREATED_BY,
510 ROUTE_DESCRIPTION,
511 ROUTE_ID,
512 CREATION_DATE,
514 LAST_UPDATE_DATE,
515 LAST_UPDATED_BY,
516 LANGUAGE,
517 SOURCE_LANG
518 ) select
519 X_LAST_UPDATE_LOGIN,
520 X_ROUTE_NAME,
521 X_ROUTE_DESCRIPTION,
522 X_ROUTE_ID,
523 X_CREATION_DATE,
524 X_CREATED_BY,
525 X_LAST_UPDATE_DATE,
526 X_LAST_UPDATED_BY,
527 L.LANGUAGE_CODE,
528 userenv('LANG')
529 from FND_LANGUAGES L
530 where L.INSTALLED_FLAG in ('I', 'B')
531 and not exists
532 (select NULL
533 from ENG_CHANGE_ROUTES_TL T
534 where T.ROUTE_ID = X_ROUTE_ID
535 and T.LANGUAGE = L.LANGUAGE_CODE);
536
537
538 open c;
539 fetch c into X_ROWID;
540 if (c%notfound) then
541 close c;
542 raise no_data_found;
543 end if;
544 close c;
545
546
547 END INSERT_ROW;
548
549
550 PROCEDURE LOCK_ROW (
551 X_ROUTE_ID IN NUMBER,
552 X_ROUTE_NAME IN VARCHAR2,
553 X_ROUTE_DESCRIPTION IN VARCHAR2,
554 X_TEMPLATE_FLAG IN VARCHAR2,
555 X_OWNER_ID IN NUMBER,
556 X_FIXED_FLAG IN VARCHAR2,
557 X_OBJECT_NAME IN VARCHAR2,
558 X_OBJECT_ID1 IN NUMBER,
559 X_OBJECT_ID2 IN NUMBER,
560 X_OBJECT_ID3 IN NUMBER,
561 X_OBJECT_ID4 IN NUMBER,
562 X_OBJECT_ID5 IN NUMBER,
563 X_APPLIED_TEMPLATE_ID IN NUMBER,
564 X_WF_ITEM_TYPE IN VARCHAR2,
565 X_WF_ITEM_KEY IN VARCHAR2,
566 X_WF_PROCESS_NAME IN VARCHAR2,
567 X_STATUS_CODE IN VARCHAR2,
568 X_ROUTE_START_DATE IN DATE,
569 X_ROUTE_END_DATE IN DATE,
570 X_CHANGE_REVISION IN VARCHAR2,
571 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
572 X_ATTRIBUTE1 IN VARCHAR2,
573 X_ATTRIBUTE2 IN VARCHAR2,
574 X_ATTRIBUTE3 IN VARCHAR2,
575 X_ATTRIBUTE4 IN VARCHAR2,
576 X_ATTRIBUTE5 IN VARCHAR2,
577 X_ATTRIBUTE6 IN VARCHAR2,
578 X_ATTRIBUTE7 IN VARCHAR2,
579 X_ATTRIBUTE8 IN VARCHAR2,
580 X_ATTRIBUTE9 IN VARCHAR2,
581 X_ATTRIBUTE10 IN VARCHAR2,
582 X_ATTRIBUTE11 IN VARCHAR2,
583 X_ATTRIBUTE12 IN VARCHAR2,
584 X_ATTRIBUTE13 IN VARCHAR2,
585 X_ATTRIBUTE14 IN VARCHAR2,
586 X_ATTRIBUTE15 IN VARCHAR2,
587 X_REQUEST_ID IN NUMBER,
588 X_PROGRAM_ID IN NUMBER,
589 X_PROGRAM_APPLICATION_ID IN NUMBER,
590 X_PROGRAM_UPDATE_DATE IN DATE,
591 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
592 X_CLASSIFICATION_CODE IN VARCHAR2,
593 X_ROUTE_TYPE_CODE IN VARCHAR2
594 )
595 IS
596
597 cursor c is select
598 TEMPLATE_FLAG,
599 OWNER_ID,
600 FIXED_FLAG,
601 OBJECT_NAME,
602 OBJECT_ID1,
603 OBJECT_ID2,
604 OBJECT_ID3,
605 OBJECT_ID4,
606 OBJECT_ID5,
607 APPLIED_TEMPLATE_ID,
608 WF_ITEM_TYPE,
609 WF_ITEM_KEY,
610 WF_PROCESS_NAME,
611 STATUS_CODE,
612 ROUTE_START_DATE,
613 ROUTE_END_DATE,
614 CHANGE_REVISION,
615 ATTRIBUTE_CATEGORY,
616 ATTRIBUTE1,
617 ATTRIBUTE2,
618 ATTRIBUTE3,
619 ATTRIBUTE4,
620 ATTRIBUTE5,
621 ATTRIBUTE6,
622 ATTRIBUTE7,
623 ATTRIBUTE8,
624 ATTRIBUTE9,
625 ATTRIBUTE10,
626 ATTRIBUTE11,
627 ATTRIBUTE12,
628 ATTRIBUTE13,
629 ATTRIBUTE14,
630 ATTRIBUTE15,
631 REQUEST_ID,
632 PROGRAM_ID,
633 PROGRAM_APPLICATION_ID,
634 PROGRAM_UPDATE_DATE,
635 ORIGINAL_SYSTEM_REFERENCE,
636 CLASSIFICATION_CODE,
637 ROUTE_TYPE_CODE
638 from ENG_CHANGE_ROUTES
639 where ROUTE_ID = X_ROUTE_ID
640 for update of ROUTE_ID nowait;
641 recinfo c%rowtype;
642
643 cursor c1 is select
644 ROUTE_NAME,
645 ROUTE_DESCRIPTION,
646 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
647 from ENG_CHANGE_ROUTES_TL
648 where ROUTE_ID = X_ROUTE_ID
649 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
650 for update of ROUTE_ID nowait;
651
652 BEGIN
653
654 open c;
655 fetch c into recinfo;
656 if (c%notfound) then
657 close c;
658 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
659 app_exception.raise_exception;
660 end if;
661 close c;
662
663
664 if ( (recinfo.TEMPLATE_FLAG = X_TEMPLATE_FLAG)
665 AND (recinfo.OWNER_ID = X_OWNER_ID)
666 AND ((recinfo.FIXED_FLAG = X_FIXED_FLAG)
670 AND ((recinfo.OBJECT_ID2 = X_OBJECT_ID2)
667 OR ((recinfo.FIXED_FLAG is null) AND (X_FIXED_FLAG is null)))
668 AND (recinfo.OBJECT_NAME = X_OBJECT_NAME)
669 AND (recinfo.OBJECT_ID1 = X_OBJECT_ID1)
671 OR ((recinfo.OBJECT_ID2 is null) AND (X_OBJECT_ID2 is null)))
672 AND ((recinfo.OBJECT_ID3 = X_OBJECT_ID3)
673 OR ((recinfo.OBJECT_ID3 is null) AND (X_OBJECT_ID3 is null)))
674 AND ((recinfo.OBJECT_ID4 = X_OBJECT_ID4)
675 OR ((recinfo.OBJECT_ID4 is null) AND (X_OBJECT_ID4 is null)))
676 AND ((recinfo.OBJECT_ID5 = X_OBJECT_ID5)
677 OR ((recinfo.OBJECT_ID5 is null) AND (X_OBJECT_ID5 is null)))
678 AND ((recinfo.APPLIED_TEMPLATE_ID = X_APPLIED_TEMPLATE_ID)
679 OR ((recinfo.APPLIED_TEMPLATE_ID is null) AND (X_APPLIED_TEMPLATE_ID is null)))
680 AND ((recinfo.WF_ITEM_TYPE = X_WF_ITEM_TYPE)
681 OR ((recinfo.WF_ITEM_TYPE is null) AND (X_WF_ITEM_TYPE is null)))
682 AND ((recinfo.WF_ITEM_KEY = X_WF_ITEM_KEY)
683 OR ((recinfo.WF_ITEM_KEY is null) AND (X_WF_ITEM_KEY is null)))
684 AND ((recinfo.WF_PROCESS_NAME = X_WF_PROCESS_NAME)
685 OR ((recinfo.WF_PROCESS_NAME is null) AND (X_WF_PROCESS_NAME is null)))
686 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
687 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
688 AND ((recinfo.ROUTE_START_DATE = X_ROUTE_START_DATE)
689 OR ((recinfo.ROUTE_START_DATE is null) AND (X_ROUTE_START_DATE is null)))
690 AND ((recinfo.ROUTE_END_DATE = X_ROUTE_END_DATE)
691 OR ((recinfo.ROUTE_END_DATE is null) AND (X_ROUTE_END_DATE is null)))
692 AND ((recinfo.CHANGE_REVISION = X_CHANGE_REVISION)
693 OR ((recinfo.CHANGE_REVISION is null) AND (X_CHANGE_REVISION is null)))
694 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
695 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
696 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
697 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
698 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
699 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
700 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
701 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
702 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
703 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
704 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
705 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
706 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
707 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
708 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
709 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
710 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
711 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
712 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
713 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
714 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
715 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
716 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
717 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
718 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
719 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
720 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
721 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
722 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
723 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
724 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
725 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
726 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
727 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
728 AND ((recinfo.ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE)
729 OR ((recinfo.ORIGINAL_SYSTEM_REFERENCE is null) AND (X_ORIGINAL_SYSTEM_REFERENCE is null)))
730 AND ((recinfo.CLASSIFICATION_CODE = X_CLASSIFICATION_CODE)
731 OR ((recinfo.CLASSIFICATION_CODE is null) AND (X_CLASSIFICATION_CODE is null)))
732 AND ((recinfo.ROUTE_TYPE_CODE = X_ROUTE_TYPE_CODE )
733 OR ((recinfo.ROUTE_TYPE_CODE is null) AND (X_ROUTE_TYPE_CODE is null)))
734 -- followings are not generated by tool
735 -- AND ((recinfo.PROGRAM_ID= X_PROGRAM_ID)
736 -- OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
737 -- AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
738 -- OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
739 -- AND ((recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE)
740 -- OR ((recinfo.PROGRAM_UPDATE_DATE is null) AND (X_PROGRAM_UPDATE_DATE is null)))
741 ) then
742 null;
743 else
744 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
745 app_exception.raise_exception;
746 end if;
747
748 for tlinfo in c1 loop
749 if (tlinfo.BASELANG = 'Y') then
750 if ( (tlinfo.ROUTE_NAME = X_ROUTE_NAME)
751 AND ((tlinfo.ROUTE_DESCRIPTION = X_ROUTE_DESCRIPTION)
752 OR ((tlinfo.ROUTE_DESCRIPTION is null) AND (X_ROUTE_DESCRIPTION is null)))
753 ) then
754 null;
755 else
756 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
757 app_exception.raise_exception;
761
758 end if;
759 end if;
760 end loop;
762 RETURN;
763
764 END LOCK_ROW;
765
766
767 PROCEDURE UPDATE_ROW (
768 X_ROUTE_ID IN NUMBER,
769 X_ROUTE_NAME IN VARCHAR2,
770 X_ROUTE_DESCRIPTION IN VARCHAR2,
771 X_TEMPLATE_FLAG IN VARCHAR2,
772 X_OWNER_ID IN NUMBER,
773 X_FIXED_FLAG IN VARCHAR2,
774 X_OBJECT_NAME IN VARCHAR2,
775 X_OBJECT_ID1 IN NUMBER,
776 X_OBJECT_ID2 IN NUMBER,
777 X_OBJECT_ID3 IN NUMBER,
778 X_OBJECT_ID4 IN NUMBER,
779 X_OBJECT_ID5 IN NUMBER,
780 X_APPLIED_TEMPLATE_ID IN NUMBER,
781 X_WF_ITEM_TYPE IN VARCHAR2,
782 X_WF_ITEM_KEY IN VARCHAR2,
783 X_WF_PROCESS_NAME IN VARCHAR2,
784 X_STATUS_CODE IN VARCHAR2,
785 X_ROUTE_START_DATE IN DATE,
786 X_ROUTE_END_DATE IN DATE,
787 X_CHANGE_REVISION IN VARCHAR2,
788 X_LAST_UPDATE_DATE IN DATE,
789 X_LAST_UPDATED_BY IN NUMBER,
790 X_LAST_UPDATE_LOGIN IN NUMBER,
791 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
792 X_ATTRIBUTE1 IN VARCHAR2,
793 X_ATTRIBUTE2 IN VARCHAR2,
794 X_ATTRIBUTE3 IN VARCHAR2,
795 X_ATTRIBUTE4 IN VARCHAR2,
796 X_ATTRIBUTE5 IN VARCHAR2,
797 X_ATTRIBUTE6 IN VARCHAR2,
798 X_ATTRIBUTE7 IN VARCHAR2,
799 X_ATTRIBUTE8 IN VARCHAR2,
800 X_ATTRIBUTE9 IN VARCHAR2,
801 X_ATTRIBUTE10 IN VARCHAR2,
802 X_ATTRIBUTE11 IN VARCHAR2,
803 X_ATTRIBUTE12 IN VARCHAR2,
804 X_ATTRIBUTE13 IN VARCHAR2,
805 X_ATTRIBUTE14 IN VARCHAR2,
806 X_ATTRIBUTE15 IN VARCHAR2,
807 X_REQUEST_ID IN NUMBER,
808 X_PROGRAM_ID IN NUMBER,
809 X_PROGRAM_APPLICATION_ID IN NUMBER,
810 X_PROGRAM_UPDATE_DATE IN DATE,
811 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
812 X_CLASSIFICATION_CODE IN VARCHAR2,
813 X_ROUTE_TYPE_CODE IN VARCHAR2
814 )
815 IS
816
817 BEGIN
818
819 update ENG_CHANGE_ROUTES set
820 TEMPLATE_FLAG = X_TEMPLATE_FLAG,
821 OWNER_ID = X_OWNER_ID,
822 FIXED_FLAG = X_FIXED_FLAG,
823 OBJECT_NAME = X_OBJECT_NAME,
824 OBJECT_ID1 = X_OBJECT_ID1,
825 OBJECT_ID2 = X_OBJECT_ID2,
826 OBJECT_ID3 = X_OBJECT_ID3,
827 OBJECT_ID4 = X_OBJECT_ID4,
828 OBJECT_ID5 = X_OBJECT_ID5,
829 APPLIED_TEMPLATE_ID = X_APPLIED_TEMPLATE_ID,
830 WF_ITEM_TYPE = X_WF_ITEM_TYPE,
831 WF_ITEM_KEY = X_WF_ITEM_KEY,
832 WF_PROCESS_NAME = X_WF_PROCESS_NAME,
833 STATUS_CODE = X_STATUS_CODE,
834 ROUTE_START_DATE = X_ROUTE_START_DATE,
835 ROUTE_END_DATE = X_ROUTE_END_DATE,
836 CHANGE_REVISION = X_CHANGE_REVISION,
837 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
838 ATTRIBUTE1 = X_ATTRIBUTE1,
839 ATTRIBUTE2 = X_ATTRIBUTE2,
840 ATTRIBUTE3 = X_ATTRIBUTE3,
841 ATTRIBUTE4 = X_ATTRIBUTE4,
842 ATTRIBUTE5 = X_ATTRIBUTE5,
843 ATTRIBUTE6 = X_ATTRIBUTE6,
844 ATTRIBUTE7 = X_ATTRIBUTE7,
845 ATTRIBUTE8 = X_ATTRIBUTE8,
846 ATTRIBUTE9 = X_ATTRIBUTE9,
847 ATTRIBUTE10 = X_ATTRIBUTE10,
848 ATTRIBUTE11 = X_ATTRIBUTE11,
849 ATTRIBUTE12 = X_ATTRIBUTE12,
850 ATTRIBUTE13 = X_ATTRIBUTE13,
851 ATTRIBUTE14 = X_ATTRIBUTE14,
852 ATTRIBUTE15 = X_ATTRIBUTE15,
853 REQUEST_ID = X_REQUEST_ID,
854 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
855 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
856 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
857 PROGRAM_ID = X_PROGRAM_ID,
858 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
859 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
860 ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE,
861 CLASSIFICATION_CODE = X_CLASSIFICATION_CODE,
862 ROUTE_TYPE_CODE = X_ROUTE_TYPE_CODE
863 where ROUTE_ID = X_ROUTE_ID;
864
865 if (sql%notfound) then
866 raise no_data_found;
867 end if;
868
869 update ENG_CHANGE_ROUTES_TL set
870 ROUTE_NAME = X_ROUTE_NAME,
871 ROUTE_DESCRIPTION = X_ROUTE_DESCRIPTION,
872 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
873 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
874 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
875 SOURCE_LANG = userenv('LANG')
876 where ROUTE_ID = X_ROUTE_ID
877 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
878
879 if (sql%notfound) then
880 raise no_data_found;
881 end if;
882
883 END UPDATE_ROW;
884
885
886 PROCEDURE DELETE_ROW (
887 X_ROUTE_ID IN NUMBER
888 )
889 IS
890
891 BEGIN
892 delete from ENG_CHANGE_ROUTES_TL
893 where ROUTE_ID = X_ROUTE_ID;
894
895 if (sql%notfound) then
896 raise no_data_found;
897 end if;
898
899 delete from ENG_CHANGE_ROUTES
900 where ROUTE_ID = X_ROUTE_ID;
901
902 if (sql%notfound) then
903 raise no_data_found;
904 end if;
905
906 END DELETE_ROW;
907
908
909
910 PROCEDURE ADD_LANGUAGE
911 IS
912
913 BEGIN
914
915 delete from ENG_CHANGE_ROUTES_TL T
916 where not exists
917 (select NULL
918 from ENG_CHANGE_ROUTES B
919 where B.ROUTE_ID = T.ROUTE_ID
920 );
921
922 update ENG_CHANGE_ROUTES_TL T set (
923 ROUTE_NAME,
924 ROUTE_DESCRIPTION
925 ) = (select
926 B.ROUTE_NAME,
927 B.ROUTE_DESCRIPTION
928 from ENG_CHANGE_ROUTES_TL B
929 where B.ROUTE_ID = T.ROUTE_ID
930 and B.LANGUAGE = T.SOURCE_LANG)
931 where (
932 T.ROUTE_ID,
933 T.LANGUAGE
934 ) in (select
935 SUBT.ROUTE_ID,
936 SUBT.LANGUAGE
937 from ENG_CHANGE_ROUTES_TL SUBB, ENG_CHANGE_ROUTES_TL SUBT
938 where SUBB.ROUTE_ID = SUBT.ROUTE_ID
939 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
940 and (SUBB.ROUTE_NAME <> SUBT.ROUTE_NAME
941 or SUBB.ROUTE_DESCRIPTION <> SUBT.ROUTE_DESCRIPTION
942 or (SUBB.ROUTE_DESCRIPTION is null and SUBT.ROUTE_DESCRIPTION is not null)
943 or (SUBB.ROUTE_DESCRIPTION is not null and SUBT.ROUTE_DESCRIPTION is null)
944 ));
945
946
947 insert into ENG_CHANGE_ROUTES_TL (
948 LAST_UPDATE_LOGIN,
949 ROUTE_NAME,
950 ROUTE_DESCRIPTION,
951 ROUTE_ID,
952 CREATION_DATE,
953 CREATED_BY,
954 LAST_UPDATE_DATE,
955 LAST_UPDATED_BY,
956 LANGUAGE,
957 SOURCE_LANG
958 ) select
959 B.LAST_UPDATE_LOGIN,
960 B.ROUTE_NAME,
961 B.ROUTE_DESCRIPTION,
962 B.ROUTE_ID,
963 B.CREATION_DATE,
964 B.CREATED_BY,
965 B.LAST_UPDATE_DATE,
966 B.LAST_UPDATED_BY,
967 L.LANGUAGE_CODE,
968 B.SOURCE_LANG
969 from ENG_CHANGE_ROUTES_TL B, FND_LANGUAGES L
970 where L.INSTALLED_FLAG in ('I', 'B')
971 and B.LANGUAGE = userenv('LANG')
972 and not exists
973 (select NULL
974 from ENG_CHANGE_ROUTES_TL T
975 where T.ROUTE_ID = B.ROUTE_ID
976 and T.LANGUAGE = L.LANGUAGE_CODE);
977
978
979 END ADD_LANGUAGE;
980
981
982 PROCEDURE CLOSE_LOB(lob_loc IN OUT NOCOPY CLOB)
983 IS
984 BEGIN
985
986 if (DBMS_LOB.isOpen(lob_loc) = 1) then
987 DBMS_LOB.Close(lob_loc);
988 end if;
989 if (dbms_lob.isTemporary(lob_loc)=1) then
990 DBMS_LOB.freeTemporary(lob_loc);
991 end if;
992
993 END CLOSE_LOB ;
994
995 --
996 -- Don't forget calling CLOSAE_LOB after calling
997 -- this API if out param CLOB is not null
998 --
999 PROCEDURE CREATE_INSTANCE_SET_SQL
1000 (
1001 p_Object_Values IN VARCHAR2,
1002 x_User_Group_Flag IN VARCHAR2,
1003 x_Complete_query OUT NOCOPY CLOB
1004 )
1005 IS
1006
1007 l_set_query VARCHAR2(3200);
1008 object_names VARCHAR2(2000);
1009 l_object_name VARCHAR2(2000);
1010 l_amount BINARY_INTEGER ;
1011 l_offset INTEGER ;
1012 text VARCHAR2(3200) ;
1013 l_loop_flag NUMBER ;
1014
1015
1016 -- Comment out
1017 -- cursor c_set_user_queries(p_object_name VARCHAR2) IS
1018 -- select
1019 -- 'SELECT ' || a.OBJECT_ID || ' OBJECT_ID,'
1020 -- || b.menu_id|| ' ROLE_ID,'''
1021 -- || b.grantee_key||''' GRANTEE_KEY,'
1022 -- || a.PK1_COLUMN_NAME || ' PK1_VALUE,'
1023 -- || NVL(a.PK2_COLUMN_NAME ,-1) || ' PK2_VALUE,'
1024 -- || NVL(a.PK3_COLUMN_NAME ,-1) || ' PK3_VALUE,'
1028 -- || ' WHERE ' || c.predicate query
1025 -- || NVL(a.PK4_COLUMN_NAME ,-1) || ' PK4_VALUE,'
1026 -- || NVL(a.PK5_COLUMN_NAME ,-1) || ' PK5_VALUE '
1027 -- || ' FROM ' || a.database_object_name
1029 -- from
1030 -- fnd_object_instance_sets c,
1031 -- fnd_grants b,
1032 -- fnd_objects a
1033 -- where b.object_id=a.object_id
1034 -- and a.object_id=c.object_id
1035 -- and c.instance_set_id=b.instance_set_id
1036 -- and b.GRANTEE_ORIG_SYSTEM='HZ_PARTY'
1037 -- and b.instance_type='SET'
1038 -- and NVL(b.END_DATE,SYSDATE)>=SYSDATE
1039 -- and a.obj_name =p_object_name;
1040
1044 -- 'SELECT ' || a.OBJECT_ID || ' OBJECT_ID,'
1041 -- Comment out
1042 -- cursor c_set_group_queries(p_object_name VARCHAR2) IS
1043 -- select
1045 -- || b.menu_id|| ' ROLE_ID,'''
1046 -- || b.grantee_key||''' GRANTEE_KEY,'
1047 -- || a.PK1_COLUMN_NAME || ' PK1_VALUE,'
1048 -- || NVL(a.PK2_COLUMN_NAME ,-1) || ' PK2_VALUE,'
1049 -- || NVL(a.PK3_COLUMN_NAME ,-1) || ' PK3_VALUE,'
1050 -- || NVL(a.PK4_COLUMN_NAME ,-1) || ' PK4_VALUE,'
1051 -- || NVL(a.PK5_COLUMN_NAME ,-1) || ' PK5_VALUE '
1052 -- || ' FROM ' || a.database_object_name
1053 -- || ' WHERE ' || c.predicate query
1054 -- from
1055 -- fnd_object_instance_sets c,
1056 -- fnd_grants b,
1057 -- fnd_objects a
1058 -- where b.object_id=a.object_id
1059 -- and a.object_id=c.object_id
1060 -- and c.instance_set_id=b.instance_set_id
1061 -- and b.GRANTEE_ORIG_SYSTEM='HZ_GROUP'
1062 -- and b.instance_type='SET'
1063 -- and NVL(b.END_DATE,SYSDATE)>=SYSDATE
1064 -- and a.obj_name =p_object_name;
1065
1066
1067 BEGIN
1068
1069 -- Init Vars
1070 l_amount := 20 ;
1071 l_offset := 1 ;
1072 l_loop_flag := -1 ;
1073
1074 -- This procedure obosolete
1075 /**********************************************************
1076 object_names:=p_object_values;
1077
1078 if x_User_Group_Flag = 'INSTANCE_SET_USER'
1079 then
1080 while l_loop_flag=-1
1081 loop
1082 IF INSTR(object_names,',') >0
1083 THEN
1084 l_object_name:=SUBSTR(object_names,0,INSTR(p_Object_Values,',')-1);
1085 object_names:=SUBSTR(object_names,INSTR(p_Object_Values,',')+1);
1086 ELSE
1087 l_object_name := object_names;
1088 l_loop_flag := 0;
1089 END IF;
1090
1091 open c_set_user_queries(p_object_name => l_object_name);
1092 LOOP
1093 FETCH c_set_user_queries INTO l_set_query;
1094 exit when c_set_user_queries%NOTFOUND;
1095 if x_Complete_query is null
1096 then
1097 DBMS_LOB.createtemporary(x_Complete_query,true);
1098 DBMS_LOB.Trim ( lob_loc => x_Complete_query,newlen => 0 );
1099 DBMS_LOB.Write ( lob_loc => x_Complete_query
1100 , amount => Length (l_set_query)
1101 , offset => l_offset
1102 , buffer => l_set_query
1103 );
1104 -- insert into abc_table values('here in the null part');
1105 else
1106 l_set_query :=' UNION ALL ' || l_set_query;
1107 DBMS_LOB.WriteAppend ( lob_loc => x_Complete_query
1108 , amount => Length (l_set_query)
1109 , buffer => l_set_query
1110 );
1111 -- insert into abc_table values('here in the not null');
1112 end if;
1113 END loop;
1114 close c_set_user_queries;
1115 END loop;
1116
1117 --
1118 -- Starting x_User_Group_Flag = 'INSTANCE_SET_GROUP'
1119 else
1120 while l_loop_flag=-1
1121 loop
1122 IF INSTR(object_names,',') >0
1123 THEN
1124 l_object_name:=SUBSTR(object_names,0,INSTR(p_Object_Values,',')-1);
1125 object_names:=SUBSTR(object_names,INSTR(p_Object_Values,',')+1);
1126 ELSE
1127 l_object_name := object_names;
1128 l_loop_flag := 0;
1129 END IF;
1130
1131 open c_set_group_queries(p_object_name => l_object_name);
1132 LOOP
1133 FETCH c_set_group_queries INTO l_set_query;
1134 exit when c_set_group_queries%NOTFOUND;
1135 if x_Complete_query is null
1136 then
1137 DBMS_LOB.createtemporary(x_Complete_query,true);
1138 DBMS_LOB.Trim ( lob_loc => x_Complete_query,newlen => 0 );
1139 DBMS_LOB.Write ( lob_loc => x_Complete_query
1140 , amount => Length (l_set_query)
1141 , offset => l_offset
1142 , buffer => l_set_query
1143 );
1144
1145 -- insert into abc_table values('here in the null part');
1146
1147 else
1148 l_set_query :=' UNION ALL ' || l_set_query;
1149
1150 DBMS_LOB.WriteAppend ( lob_loc => x_Complete_query
1151 , amount => Length (l_set_query)
1152 , buffer => l_set_query
1153 );
1154
1155 -- insert into abc_table values('here in the not null');
1156
1157 end if;
1158
1159 END loop;
1160 close c_set_group_queries;
1161 END loop;
1162
1163 end if;
1164 *********************************************************************/
1165
1166 --Append_VARCHAR_to_LOB(x_Complete_query,' ' ,'END');
1167 --DBMS_LOB.read(lob_loc => x_Complete_query,amount => l_amount ,offset => l_offset,buffer => text);
1168
1169 --DBMS_OUTPUT.put_line(' Aman == > ' ||' ' ||text);
1170
1171
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 -- closing and freeing the temp lob
1175 if (DBMS_LOB.isOpen(x_Complete_query) = 1) then
1176 DBMS_LOB.Close(x_Complete_query);
1177 end if;
1178
1179 if (dbms_lob.isTemporary(x_Complete_query)=1) then
1180 DBMS_LOB.freeTemporary(x_Complete_query);
1181 end if;
1182
1183 RAISE ;
1184
1185 END CREATE_INSTANCE_SET_SQL ;
1186
1187 /********************************************************************
1188 * API Type : Public APIs
1189 * Purpose : APIS to create Instance set query
1190 *********************************************************************/
1191
1192
1193
1194
1195 END Eng_Change_Route_Util ;