eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_PE_DYNAMIC_PERSID_GROUP

Source


1 PACKAGE BODY IGS_PE_DYNAMIC_PERSID_GROUP AS
2 /* $Header: IGSPEDGB.pls 120.1 2006/02/02 06:49:07 skpandey noship $ */
3 
4   -- Global Variables
5 
6 
7   g_api_version 	CONSTANT NUMBER       := 1.0;
8   g_api_name    	CONSTANT VARCHAR2(30) := 'igs_get_dynamic_sql';
9   g_pkg_name		CONSTANT VARCHAR2(30) := 'igs_dynamic_perid_group';
10   g_full_name   	CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| g_api_name;
11   --g_msg_list		VARCHAR2     	      := FND_API.g_false;
12 
13 
14 /*==========================================================================+
15  | Name: IGS_GET_DYNAMIC_SQL().                                             |
16  |       Returns the SQL extracted from Discoverer.                         |
17  |    04-DEC-2003  ssaleem     Dynamic Person id group Bug 3198795          |
18  |                             handled order by clause in dynamic person    |
19  |                             id group                                     |
20  +==========================================================================*/
21 
22   FUNCTION IGS_GET_DYNAMIC_SQL(
23      p_GroupID	IN     		igs_pe_persid_group_all.group_id%TYPE,
24      p_Status	OUT NOCOPY 	VARCHAR2) RETURN VARCHAR2
25   IS
26 
27      --local variables
28 
29     l_SQL 		VARCHAR2(32767);
30     l_SQL_exp 		VARCHAR2(67);
31     l_id		NUMBER(6);
32     lv_parser           VARCHAR2(32767);
33     lv_found            VARCHAR2(1);
34     x_Count		NUMBER;
35     x_Data		VARCHAR2(2000);
36 
37     CURSOR c_group_type IS
38     SELECT group_type
39     FROM IGS_PE_PERSID_GROUP_V
40     WHERE group_id = p_GroupID;
41     lv_group_type IGS_PE_PERSID_GROUP_V.group_type%TYPE;
42 
43     CURSOR c_Rec IS
44     SELECT S.sql_segment
45     FROM IGS_PE_PERSID_GROUP_ALL G, IGS_PE_DYN_SQLSEGS S
46     WHERE G.group_id = p_GroupID
47     AND G.file_name = S.file_name
48     ORDER BY S.seg_sequence_num;
49 
50 
51   BEGIN
52     l_SQL := '';
53     l_SQL_exp := 'SELECT 1 FROM DUAL';
54     OPEN c_group_type;
55     FETCH c_group_type INTO lv_group_type;
56     CLOSE c_group_type;
57 
58     IF lv_group_type = 'STATIC' THEN
59        l_SQL := 'SELECT person_id FROM igs_pe_prsid_grp_mem_all where TRUNC(SYSDATE) BETWEEN NVL(start_date,TRUNC(SYSDATE)) AND NVL(end_date,TRUNC(SYSDATE)) AND group_id = '||p_GroupID ;
60 
61     ELSIF lv_group_type = 'DYNAMIC' THEN
62        FOR c_Disc IN c_REC LOOP
63           l_SQL  := l_SQL || c_Disc.sql_segment;
64        END LOOP;
65 
66        -- l_SQL := UPPER(rtrim(l_SQL)); Bug:3405360, asbala --commented this code
67        lv_parser  := substr(l_SQL,(instr(l_SQL,'SELECT') + 6));
68        lv_parser  := substr(lv_parser,1,(instr(lv_parser,'FROM') - 1));
69        lv_parser  := trim(lv_parser);
70        lv_parser  := lv_parser||',';
71 
72        WHILE lv_parser IS NOT NULL LOOP
73          DECLARE
74              lv_check VARCHAR2(100);
75          BEGIN
76              lv_check  := substr(lv_parser,1,(instr(lv_parser,',')-1));
77              lv_parser := trim(substr(lv_parser,(instr(lv_parser,',')+1)));
78 
79              IF ((UPPER(lv_check) like '%PERSON_ID%') OR
80                  (UPPER(lv_check) like '%PARTY_ID%')) THEN
81                   l_SQL := 'SELECT '||lv_check||' PERSON_ID '||
82                        substr(l_SQL,(instr(l_SQL,'FROM')));
83                   lv_found := 'Y';
84                   lv_parser := NULL;
85               END IF;
86          END;
87        END LOOP;
88 
89        IF INSTR(l_SQL,' ORDER BY ') > 0 THEN
90          l_SQL := SUBSTR(l_SQL,0,(INSTR(l_SQL,' ORDER BY ')));
91        END IF;
92 
93        IF NVL(lv_found,'N') = 'N' THEN
94               RAISE NO_DATA_FOUND;
95        END IF;
96     END IF;
97     p_Status := FND_API.g_ret_sts_success;
98 
99     RETURN l_SQL;
100 
101   EXCEPTION
102     WHEN NO_DATA_FOUND THEN
103       p_Status := FND_API.g_ret_sts_unexp_error ;
104 
105       IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
106         FND_MSG_PUB.add_exc_msg(g_pkg_name, g_api_name);
107       END IF;
108       FND_MSG_PUB.count_and_get(
109         p_encoded => FND_API.g_false,
110         p_count   => x_Count,
111         p_data    => x_Data
112       );
113       RETURN l_SQL_exp;
114     WHEN FND_API.g_exc_error THEN
115       p_Status := FND_API.g_ret_sts_error;
116       FND_MSG_PUB.count_and_get(
117         p_encoded => FND_API.g_false,
118         p_count   => x_Count,
119         p_data    => x_Data
120       );
121       RETURN l_SQL_exp;
122     WHEN FND_API.g_exc_unexpected_error THEN
123       p_Status := FND_API.g_ret_sts_unexp_error ;
124       FND_MSG_PUB.count_and_get(
125         p_encoded => FND_API.g_false,
126         p_count   => x_count,
127         p_data    => x_data
128       );
129       RETURN l_SQL_exp;
130   END IGS_GET_DYNAMIC_SQL;
131 
132 
133 FUNCTION GET_DYNAMIC_SQL(
134      p_GroupID	IN     		igs_pe_persid_group_all.group_id%TYPE,
135      p_Status	OUT NOCOPY 	VARCHAR2,
136      p_group_type OUT NOCOPY    IGS_PE_PERSID_GROUP_V.group_type%TYPE) RETURN VARCHAR2
137   IS
138 
139      --local variables
140 
141     l_SQL 		VARCHAR2(32767);
142     l_SQL_exp 		VARCHAR2(67);
143     l_id		NUMBER(6);
144     lv_parser           VARCHAR2(32767);
145     lv_found            VARCHAR2(1);
146     x_Count		NUMBER;
147     x_Data		VARCHAR2(2000);
148 
149 
150     CURSOR c_group_type IS
151     SELECT group_type
152     FROM IGS_PE_PERSID_GROUP_V
153     WHERE group_id = p_GroupID;
154     lv_group_type IGS_PE_PERSID_GROUP_V.group_type%TYPE;
155 
156     CURSOR c_Rec IS
157     SELECT S.sql_segment
158     FROM IGS_PE_PERSID_GROUP_ALL G, IGS_PE_DYN_SQLSEGS S
159     WHERE G.group_id = p_GroupID
160     AND G.file_name = S.file_name
161     ORDER BY S.seg_sequence_num;
162 
163   BEGIN
164     l_SQL := '';
165     l_SQL_exp := 'SELECT 1 FROM DUAL';
166 
167     OPEN c_group_type;
168     FETCH c_group_type INTO lv_group_type;
169     CLOSE c_group_type;
170 
171     IF lv_group_type = 'STATIC' THEN
172        l_SQL := 'SELECT person_id FROM igs_pe_prsid_grp_mem_all where TRUNC(SYSDATE) BETWEEN NVL(start_date,TRUNC(SYSDATE)) AND NVL(end_date,TRUNC(SYSDATE)) AND group_id = :p_GroupID';
173 
174     ELSIF lv_group_type = 'DYNAMIC' THEN
175        FOR c_Disc IN c_REC LOOP
176           l_SQL  := l_SQL || c_Disc.sql_segment;
177        END LOOP;
178 
179        lv_parser  := substr(l_SQL,(instr(l_SQL,'SELECT') + 6));
180        lv_parser  := substr(lv_parser,1,(instr(lv_parser,'FROM') - 1));
181        lv_parser  := trim(lv_parser);
182        lv_parser  := lv_parser||',';
183 
184        WHILE lv_parser IS NOT NULL LOOP
185          DECLARE
186              lv_check VARCHAR2(100);
187          BEGIN
188              lv_check  := substr(lv_parser,1,(instr(lv_parser,',')-1));
189              lv_parser := trim(substr(lv_parser,(instr(lv_parser,',')+1)));
190 
191              IF ((UPPER(lv_check) like '%PERSON_ID%') OR
192                  (UPPER(lv_check) like '%PARTY_ID%')) THEN
193                   l_SQL := 'SELECT '||lv_check||' PERSON_ID '||
194                        substr(l_SQL,(instr(l_SQL,'FROM')));
195                   lv_found := 'Y';
196                   lv_parser := NULL;
197               END IF;
198          END;
199        END LOOP;
200 
201        IF INSTR(l_SQL,' ORDER BY ') > 0 THEN
202          l_SQL := SUBSTR(l_SQL,0,(INSTR(l_SQL,' ORDER BY ')));
203        END IF;
204 
205        IF NVL(lv_found,'N') = 'N' THEN
206               RAISE NO_DATA_FOUND;
207        END IF;
208     END IF;
209     p_Status := FND_API.g_ret_sts_success;
210     p_group_type := lv_group_type;
211     RETURN l_SQL;
212 
213   EXCEPTION
214     WHEN NO_DATA_FOUND THEN
215       p_Status := FND_API.g_ret_sts_unexp_error ;
216 
217       IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
218         FND_MSG_PUB.add_exc_msg(g_pkg_name, g_api_name);
219       END IF;
220       FND_MSG_PUB.count_and_get(
221         p_encoded => FND_API.g_false,
222         p_count   => x_Count,
223         p_data    => x_Data
224       );
225       RETURN l_SQL_exp;
226     WHEN FND_API.g_exc_error THEN
227       p_Status := FND_API.g_ret_sts_error;
228       FND_MSG_PUB.count_and_get(
229         p_encoded => FND_API.g_false,
230         p_count   => x_Count,
231         p_data    => x_Data
232       );
233       RETURN l_SQL_exp;
234     WHEN FND_API.g_exc_unexpected_error THEN
235       p_Status := FND_API.g_ret_sts_unexp_error ;
236       FND_MSG_PUB.count_and_get(
237         p_encoded => FND_API.g_false,
238         p_count   => x_count,
239         p_data    => x_data
240       );
241       RETURN l_SQL_exp;
242   END GET_DYNAMIC_SQL;
243 
244 
245   FUNCTION DYN_PIG_MEMBER(p_GroupID  IN   NUMBER,
246                        p_PersonID IN   NUMBER)
247   RETURN NUMBER AS
248 
249      --local variables
250 
251     l_SQL               VARCHAR2(32767);
252     l_length            NUMBER;
253     l_id                NUMBER(6);
254     lv_parser           VARCHAR2(32767);
255     lv_found            VARCHAR2(1);
256 
257     TYPE c_person_id_grpCurTyp IS REF CURSOR ;
258     c_person_id_grp c_person_id_grpCurTyp ;
259     TYPE c_person_id_grp_recTyp IS RECORD
260     (  person_id igs_pe_person_base_v.person_id%TYPE);
261      c_person_id_grp_rec c_person_id_grp_recTyp ;
262 
263     CURSOR c_Rec IS
264     SELECT S.sql_segment
265     FROM IGS_PE_PERSID_GROUP_ALL G, IGS_PE_DYN_SQLSEGS S
266     WHERE G.group_id = p_GroupID
267     AND G.file_name = S.file_name
268     ORDER BY S.seg_sequence_num;
269 
270 
271   BEGIN
272     l_SQL               := '';
273     FOR c_Disc IN c_Rec LOOP
274         l_SQL  := l_SQL || c_Disc.sql_segment;
275     END LOOP;
276 
277     l_SQL :=  rtrim(l_SQL);
278     lv_parser  := substr(l_SQL,(instr(l_SQL,'SELECT') + 6));
279     lv_parser  := substr(lv_parser,1,(instr(lv_parser,'FROM') - 1));
280     lv_parser  := trim(lv_parser);
281     lv_parser  := lv_parser||',';
282 
283     WHILE lv_parser IS NOT NULL LOOP
284       DECLARE
285           lv_check VARCHAR2(100);
286       BEGIN
287           lv_check  := substr(lv_parser,1,(instr(lv_parser,',')-1));
288           lv_parser := trim(substr(lv_parser,(instr(lv_parser,',')+1)));
289 
290           IF ((lv_check like '%PERSON_ID%') OR
291               (lv_check like '%PARTY_ID%')) THEN
292                l_SQL := 'SELECT '||lv_check||' PERSON_ID '||
293                     substr(l_SQL,(instr(l_SQL,'FROM')));
294                lv_found := 'Y';
295                lv_parser := NULL;
296            END IF;
297       END;
298     END LOOP;
299     IF NVL(lv_found,'N') = 'N' THEN
300            RAISE NO_DATA_FOUND;
301     END IF;
302 
303 --  skpandey, 02-FEB-2006, Bug#4937960: Changed cursor as a part of LITERAL fix
304     OPEN c_person_id_grp  FOR 'SELECT person_id FROM ('||l_SQL||') WHERE person_id = :p_PersonID' USING p_PersonID;
305 
306     FETCH c_person_id_grp INTO c_person_id_grp_rec;
307     IF  c_person_id_grp%NOTFOUND THEN
308        CLOSE c_person_id_grp;
309        RETURN  NULL;
310     ELSE
311        CLOSE c_person_id_grp;
312        RETURN p_PersonID;
313     END IF;
314 
315 
316   EXCEPTION
317     WHEN OTHERS THEN
318         Fnd_Message.Set_Name('IGS' , 'DYN_PIG_MEMBER');
319         IGS_GE_MSG_STACK.ADD;
320         App_Exception.Raise_Exception ;
321 
322   END DYN_PIG_MEMBER;
323 
324 /*==========================================================================+
325  | Name:  IGS_Post_Save_Document().                                         |
326  |        To enable automatic extraction of SQL, Oracle Discoverer has      |
327  |        enabled a trigger that gets fired whenever a Workbook is          |
328  |        saved.  The "trigger" looks for a PL/SQL function which is        |
329  |        mapped to this function and then does the necessary data          |
330  |        insertion.                                                        |
331  +==========================================================================*/
332 
333   FUNCTION IGS_POST_SAVE_DOCUMENT(p_WorkBookOwner IN VARCHAR2,
334                                   p_WorkBookName  IN VARCHAR2,
335                                   p_WorkSheetName IN VARCHAR2,
336                                   p_Sequence      IN NUMBER,
337                                   p_SQLSegment    IN VARCHAR2)
338   RETURN NUMBER IS
339 
340     -- local variables
341     l_sqlerrm 	       VARCHAR2(600);
342     l_sqlcode          VARCHAR2(100);
343     l_sequence_id      NUMBER(6);
344     l_creator_id       NUMBER(15);
345     l_temp_str	       VARCHAR2(200);
346     l_num              NUMBER;
347 
348     l_insert	       BOOLEAN;
349     l_sqltxt	       VARCHAR2(32767);
350 
351     CURSOR c_dyn_sql IS
352     SELECT 1
353     FROM IGS_PE_DYN_SQLSEGS
354     WHERE file_name = UPPER(p_WorkBookName)||':'||UPPER(p_WorkSheetName);
355 
356     CURSOR c_prev_seg IS
357     SELECT 1
358     FROM IGS_PE_DYN_SQLSEGS
359     WHERE file_name = UPPER(p_WorkBookName)||':'||UPPER(p_WorkSheetName)
360     AND seg_sequence_num=1;
361 
362 
363   BEGIN
364     l_sqltxt	       := '';
365     l_temp_str := substr(p_WorkBookOwner, 2);
366     l_creator_id := to_number(l_temp_str);
367 
368 
369     IF (AMS_DISCOVERER_PVT.EUL_TRIGGER$POST_SAVE_DOCUMENT(p_WorkBookOwner,
370       p_WorkBookName, p_WorkSheetName, p_Sequence, p_SQLSegment) = 0) THEN
371 
372       l_insert := FALSE;
373       --check for an existing set of workbook entries
374       if (p_Sequence < 2) then
375          OPEN c_dyn_sql;
376          FETCH c_dyn_sql INTO l_num;
377          IF(c_dyn_sql%FOUND) THEN
378              DELETE from IGS_PE_DYN_SQLSEGS
379              WHERE file_name = UPPER(p_WorkBookName)||':'
380                                ||UPPER(p_WorkSheetName);
381          END IF;
382          CLOSE c_dyn_sql;
383 
384          l_sqltxt := UPPER(p_SQLSegment);
385          IF INSTR(l_sqltxt, '.PARTY_ID') > 0 OR
386             INSTR(l_sqltxt, '.PERSON_ID') > 0
387          THEN
388             l_insert := TRUE;
389          END IF;
390       else  /* Continuing segments */
391          OPEN c_prev_seg;
392          FETCH c_prev_seg INTO l_num;
393          IF (c_prev_seg%FOUND) THEN
394             l_insert := TRUE;
395          ELSE
396             l_insert := FALSE;
397          END IF;
398          CLOSE c_prev_seg;
399       end if;
400 
401       IF l_insert = TRUE THEN
402          l_sqltxt := replace(p_SQLSegment,fnd_global.local_chr(10),' ');
403          INSERT INTO IGS_PE_DYN_SQLSEGS (
404             SQLSEGS_ID,
405             FILE_NAME,
406             SEG_SEQUENCE_NUM,
407             SQL_SEGMENT,
408             CREATED_BY,
409             CREATION_DATE,
410             LAST_UPDATED_BY,
411             LAST_UPDATE_DATE
412           )
413 	  VALUES (
414   	    IGS_PE_DYN_SQLSEGS_S.nextval,
415             UPPER(p_WorkBookName)||':'||UPPER(p_WorkSheetName),
416   	    p_Sequence,
417   	    l_sqltxt,
418   	    l_creator_id,
419   	    SYSDATE,
420   	    l_creator_id,
421   	    SYSDATE
422           );
423        END IF;
424 
425     ELSE
426       RETURN(1);
427     END IF;
428 
429 
430   RETURN(0);
431 
432 
433   EXCEPTION
434     WHEN OTHERS THEN
435       l_sqlerrm := SQLERRM;
436       l_sqlcode := SQLCODE;
437       RETURN (1) ;
438 
439   END IGS_POST_SAVE_DOCUMENT;
440 
441 /*==========================================================================+
442  | Name: GET_DYNAMIC_SQL_FROM_FILE().                                             |
443  |       Returns the SQL extracted from Discoverer.                         |
444  +==========================================================================*/
445 
446   FUNCTION GET_DYNAMIC_SQL_FROM_FILE(
447      p_FileName	IN     		IGS_PE_DYN_SQLSEGS.file_name%TYPE,
448      p_Status	OUT NOCOPY 	VARCHAR2) RETURN VARCHAR2
449   IS
450 
451      --local variables
452 
453     l_SQL 		VARCHAR2(32767);
454     l_SQL_exp 		VARCHAR2(67);
455     l_id		NUMBER(6);
456     lv_parser           VARCHAR2(32767);
457     lv_found            VARCHAR2(1);
458     x_Count		NUMBER;
459     x_Data		VARCHAR2(2000);
460 
461     CURSOR c_Rec (cp_FileName IGS_PE_DYN_SQLSEGS.file_name%TYPE) IS
462     SELECT S.sql_segment
463     FROM IGS_PE_DYN_SQLSEGS S
464     WHERE S.file_name = cp_FileName
465     ORDER BY S.seg_sequence_num;
466 
467   BEGIN
468        l_SQL 		         := '';
469        l_SQL_exp 		 := 'SELECT 1 FROM DUAL';
470        FOR c_Disc IN c_REC(p_FileName) LOOP
471           l_SQL  := l_SQL || c_Disc.sql_segment;
472        END LOOP;
473 
474        lv_parser  := substr(l_SQL,(instr(l_SQL,'SELECT') + 6));
475        lv_parser  := substr(lv_parser,1,(instr(lv_parser,'FROM') - 1));
476        lv_parser  := trim(lv_parser);
477        lv_parser  := lv_parser||',';
478 
479        WHILE lv_parser IS NOT NULL LOOP
480          DECLARE
481              lv_check VARCHAR2(100);
482          BEGIN
483              lv_check  := substr(lv_parser,1,(instr(lv_parser,',')-1));
484              lv_parser := trim(substr(lv_parser,(instr(lv_parser,',')+1)));
485 
486              IF ((UPPER(lv_check) like '%PERSON_ID%') OR
487                  (UPPER(lv_check) like '%PARTY_ID%')) THEN
488                   l_SQL := 'SELECT '||lv_check||' PERSON_ID '||
489                        substr(l_SQL,(instr(l_SQL,'FROM')));
490                   lv_found := 'Y';
491                   lv_parser := NULL;
492               END IF;
493          END;
494        END LOOP;
495 
496        IF INSTR(l_SQL,' ORDER BY ') > 0 THEN
497          l_SQL := SUBSTR(l_SQL,0,(INSTR(l_SQL,' ORDER BY ')));
498        END IF;
499 
500        IF NVL(lv_found,'N') = 'N' THEN
501               RAISE NO_DATA_FOUND;
502        END IF;
503 
504     p_Status := FND_API.g_ret_sts_success;
505 
506     RETURN l_SQL;
507 
508   EXCEPTION
509     WHEN NO_DATA_FOUND THEN
510       p_Status := FND_API.g_ret_sts_unexp_error ;
511 
512       IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
513         FND_MSG_PUB.add_exc_msg(g_pkg_name, g_api_name);
514       END IF;
515       FND_MSG_PUB.count_and_get(
516         p_encoded => FND_API.g_false,
517         p_count   => x_Count,
518         p_data    => x_Data
519       );
520       RETURN l_SQL_exp;
521     WHEN FND_API.g_exc_error THEN
522       p_Status := FND_API.g_ret_sts_error;
523       FND_MSG_PUB.count_and_get(
524         p_encoded => FND_API.g_false,
525         p_count   => x_Count,
526         p_data    => x_Data
527       );
528       RETURN l_SQL_exp;
529     WHEN FND_API.g_exc_unexpected_error THEN
530       p_Status := FND_API.g_ret_sts_unexp_error ;
531       FND_MSG_PUB.count_and_get(
532         p_encoded => FND_API.g_false,
533         p_count   => x_count,
534         p_data    => x_data
535       );
536       RETURN l_SQL_exp;
537   END GET_DYNAMIC_SQL_FROM_FILE;
538 
539 END IGS_PE_DYNAMIC_PERSID_GROUP;