[Home] [Help]
PACKAGE BODY: APPS.JTF_PERZ_QUERY_PVT
Source
1 PACKAGE BODY Jtf_Perz_Query_Pvt AS
2 /* $Header: jtfzvpqb.pls 120.2 2005/11/02 22:47:16 skothe ship $ */
3 --
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- Jtf_Perz_Query_Pvt
10 --
11 -- PURPOSE
12 -- Private API for saving, retrieving and updating personalized queries.
13 --
14 -- NOTES
15 -- This is a pulicly accessible pacakge. It should be used by all
16 -- sources for saving, retrieving and updating personalized queries
17 -- within the personalization framework.
18 --
19
20 -- HISTORY
21 -- 4/18/2000 SMATTEGU Created
22
23 -- *****************************************************************************
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):='Jtf_Perz_Query_Pvt';
26 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfzvpqb.pls';
27 G_LOGIN_ID NUMBER := Fnd_Global.CONC_LOGIN_ID;
28 G_USER_ID NUMBER := Fnd_Global.USER_ID;
29
30
31 -- *****************************************************************************
32 -- *****************************************************************************
33 -- TABLE HANDLERS
34 -- 1. insert_jtf_perz_query
35 -- 2. insert_jtf_perz_query_order_by
36 -- 3. insert_jtf_perz_query_param
37 -- 4. insert_jtf_perz_query_raw_sql
38 -- 5. update_jtf_perz_query
39
40 -- *****************************************************************************
41 -- *****************************************************************************
42
43
44 PROCEDURE update_jtf_perz_query(
45 p_QUERY_ID NUMBER,
46 p_PROFILE_ID NUMBER,
47 p_APPLICATION_ID NUMBER,
48 p_QUERY_NAME VARCHAR2,
49 p_QUERY_TYPE VARCHAR2,
50 p_QUERY_DESCRIPTION VARCHAR2,
51 p_QUERY_DATA_SOURCE VARCHAR2,
52 p_OBJECT_VERSION_NUMBER IN NUMBER)
53
54 IS
55 BEGIN
56 UPDATE JTF_PERZ_QUERY
57 SET
58 QUERY_TYPE =
59 DECODE( p_QUERY_TYPE, Fnd_Api.G_MISS_CHAR, QUERY_TYPE, p_QUERY_TYPE),
60 QUERY_DESCRIPTION =
61 DECODE( p_QUERY_DESCRIPTION, Fnd_Api.G_MISS_CHAR, QUERY_DESCRIPTION,
62 p_QUERY_DESCRIPTION),
63 QUERY_DATA_SOURCE =
64 DECODE( p_QUERY_DATA_SOURCE, Fnd_Api.G_MISS_CHAR, QUERY_DATA_SOURCE,
65 p_QUERY_DATA_SOURCE),
66 OBJECT_VERSION_NUMBER =
67 DECODE (p_OBJECT_VERSION_NUMBER, Fnd_Api.G_MISS_NUM,
68 OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER+1),
69 LAST_UPDATE_DATE = SYSDATE,
70 LAST_UPDATED_BY = Fnd_Global.USER_ID,
71 LAST_UPDATE_LOGIN = Fnd_Global.CONC_LOGIN_ID
72 WHERE QUERY_ID = p_QUERY_ID
73 AND OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
74
75 IF (SQL%NOTFOUND) THEN
76 RAISE NO_DATA_FOUND;
77 END IF;
78
79 END update_jtf_perz_query;
80
81 -- *****************************************************************************
82 -- insert row into query header
83
84 PROCEDURE insert_jtf_perz_query(
85 x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
86 X_QUERY_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
87 x_PROFILE_ID NUMBER,
88 x_APPLICATION_ID NUMBER,
89 x_QUERY_NAME VARCHAR2,
90 X_QUERY_TYPE VARCHAR2,
91 X_QUERY_DESCRIPTION VARCHAR2,
92 X_QUERY_DATA_SOURCE VARCHAR2
93 ) IS
94 CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY
95 WHERE QUERY_ID = x_QUERY_ID;
96
97 CURSOR C2 IS SELECT JTF_PERZ_QUERY_S.NEXTVAL FROM sys.dual;
98
99 BEGIN
100 IF ((x_QUERY_ID IS NULL) OR
101 (x_QUERY_ID = Fnd_Api.G_MISS_NUM)) THEN
102 OPEN C2;
103 FETCH C2 INTO x_QUERY_ID;
104 CLOSE C2;
105 END IF;
106 INSERT INTO JTF_PERZ_QUERY(
107 QUERY_ID,
108 PROFILE_ID,
109 APPLICATION_ID,
110 QUERY_NAME,
111 QUERY_TYPE,
112 QUERY_DESCRIPTION,
113 QUERY_DATA_SOURCE,
114 OBJECT_VERSION_NUMBER,
115 CREATED_BY,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_LOGIN
119 ) VALUES
120 (
121 x_QUERY_ID,
122 DECODE( x_PROFILE_ID, Fnd_Api.G_MISS_NUM, NULL ,x_PROFILE_ID ),
123 DECODE( x_APPLICATION_ID, Fnd_Api.G_MISS_NUM, NULL ,x_APPLICATION_ID ),
124 DECODE( x_QUERY_NAME, Fnd_Api.G_MISS_CHAR, NULL ,x_QUERY_NAME ),
125 DECODE( x_QUERY_TYPE, Fnd_Api.G_MISS_CHAR, NULL ,x_QUERY_TYPE ),
126 DECODE( x_QUERY_DESCRIPTION, Fnd_Api.G_MISS_CHAR, NULL ,x_QUERY_DESCRIPTION ),
127 DECODE( x_QUERY_DATA_SOURCE, Fnd_Api.G_MISS_CHAR, NULL ,x_QUERY_DATA_SOURCE ),
128 1, G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID);
129 OPEN C;
130 FETCH C INTO x_Rowid;
131 IF (C%NOTFOUND) THEN
132 CLOSE C;
133 RAISE NO_DATA_FOUND;
134 END IF;
135 END insert_jtf_perz_query;
136
137 -- *****************************************************************************
138
139 -- insert row into query header
140
141 PROCEDURE insert_jtf_perz_query_order_by(
142 X_ROWID OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
143 X_QUERY_ORDER_BY_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
144 X_QUERY_ID NUMBER,
145 X_PARAMETER_NAME VARCHAR2,
146 X_ACND_DCND_FLAG VARCHAR2,
147 X_PARAMETER_SEQUENCE NUMBER
148 ) IS
149 CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_ORDER_BY
150 WHERE QUERY_ORDER_BY_ID = x_QUERY_ORDER_BY_ID;
151
152 CURSOR C2 IS SELECT JTF_PERZ_QUERY_ORDER_BY_s.NEXTVAL FROM sys.dual;
153
154 BEGIN
155 IF (x_QUERY_ORDER_BY_ID IS NULL) THEN
156 OPEN C2;
157 FETCH C2 INTO x_QUERY_ORDER_BY_ID;
158 CLOSE C2;
159 END IF;
160 INSERT INTO JTF_PERZ_QUERY_ORDER_BY(
161 QUERY_ORDER_BY_ID,
162 QUERY_ID,
163 PARAMETER_NAME,
164 ACND_DCND_FLAG,
165 PARAMETER_SEQUENCE,
166 CREATED_BY,
167 LAST_UPDATE_DATE,
168 LAST_UPDATED_BY,
169 LAST_UPDATE_LOGIN
170 ) VALUES (
171 x_QUERY_ORDER_BY_ID,
172 x_QUERY_ID,
173 DECODE( x_PARAMETER_NAME, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_NAME ),
174 DECODE( x_ACND_DCND_FLAG, Fnd_Api.G_MISS_CHAR, NULL ,x_ACND_DCND_FLAG ),
175 DECODE( x_PARAMETER_SEQUENCE, Fnd_Api.G_MISS_NUM, NULL ,x_PARAMETER_SEQUENCE ),
176 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID
177 );
178 OPEN C;
179 FETCH C INTO x_Rowid;
180 IF (C%NOTFOUND) THEN
181 CLOSE C;
182 RAISE NO_DATA_FOUND;
183 END IF;
184 END insert_jtf_perz_query_order_by;
185
186 -- *****************************************************************************
187
188 -- insert row into query order by
189
190 PROCEDURE insert_jtf_perz_query_raw_sql(
191 x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
192 X_Query_Raw_Sql_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
193 x_Query_ID NUMBER,
194 x_Select_String VARCHAR2,
195 X_From_String VARCHAR2,
196 X_Where_String VARCHAR2,
197 X_Order_by_String VARCHAR2,
198 X_Group_by_String VARCHAR2,
199 X_Having_String VARCHAR2
200 ) IS
201 CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_RAW_SQL
202 WHERE Query_Raw_Sql_ID = X_Query_Raw_Sql_ID;
203 CURSOR C2 IS SELECT JTF_PERZ_QUERY_RAW_SQL_s.NEXTVAL FROM sys.dual;
204
205 BEGIN
206 IF ((X_Query_Raw_Sql_ID IS NULL) OR
207 (X_Query_Raw_Sql_ID = Fnd_Api.G_MISS_NUM ))THEN
208 OPEN C2;
209 FETCH C2 INTO X_Query_Raw_Sql_ID;
210 CLOSE C2;
211 END IF;
212 INSERT INTO JTF_PERZ_QUERY_RAW_SQL(
213 QUERY_RAW_SQL_ID,
214 QUERY_ID,
215 SELECT_STRING,
216 FROM_STRING,
217 WHERE_STRING,
218 ORDER_BY_STRING,
219 GROUP_BY_STRING,
220 HAVING_STRING,
221 CREATED_BY,
222 LAST_UPDATE_DATE,
223 LAST_UPDATED_BY,
224 LAST_UPDATE_LOGIN
225 ) VALUES (
226 x_QUERY_RAW_SQL_ID,
227 x_QUERY_ID,
228 DECODE( x_SELECT_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_SELECT_STRING ),
229 DECODE( x_FROM_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_FROM_STRING ),
230 DECODE( x_WHERE_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_WHERE_STRING ),
231 DECODE( x_ORDER_BY_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_ORDER_BY_STRING ),
232 DECODE( x_GROUP_BY_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_GROUP_BY_STRING ),
233 DECODE( x_HAVING_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_HAVING_STRING ),
234 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID
235 );
236 OPEN C;
237 FETCH C INTO x_Rowid;
238 IF (C%NOTFOUND) THEN
239 CLOSE C;
240 RAISE NO_DATA_FOUND;
241 END IF;
242 END insert_jtf_perz_query_raw_sql;
243 -- *****************************************************************************
244
245 PROCEDURE insert_jtf_perz_query_param(
246 x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
247 X_query_param_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
248 x_Query_ID NUMBER,
249 x_Parameter_Name VARCHAR2,
250 X_Parameter_Type VARCHAR2,
251 X_Parameter_Value VARCHAR2,
252 X_Parameter_condition VARCHAR2,
253 X_Parameter_sequence VARCHAR2
254 ) IS
255 CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_PARAM
256 WHERE query_param_id = X_query_param_id;
257 CURSOR C2 IS SELECT JTF_PERZ_QUERY_PARAM_s.NEXTVAL FROM sys.dual;
258 BEGIN
259 IF ((X_query_param_id IS NULL) OR
260 (X_query_param_id = Fnd_Api.G_MISS_NUM)) THEN
261 OPEN C2;
262 FETCH C2 INTO X_query_param_id;
263 CLOSE C2;
264 END IF;
265 INSERT INTO JTF_PERZ_QUERY_PARAM(
266 QUERY_PARAM_ID,
267 QUERY_ID,
268 PARAMETER_NAME,
269 PARAMETER_TYPE,
270 PARAMETER_VALUE,
271 PARAMETER_CONDITION,
272 PARAMETER_SEQUENCE,
273 CREATED_BY,
274 LAST_UPDATE_DATE,
275 LAST_UPDATED_BY,
276 LAST_UPDATE_LOGIN
277 ) VALUES (
278 x_QUERY_PARAM_ID,
279 x_QUERY_ID,
280 DECODE( x_PARAMETER_NAME, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_NAME ),
281 DECODE( x_PARAMETER_TYPE, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_TYPE ),
282 DECODE( x_PARAMETER_VALUE, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_VALUE ),
283 DECODE( x_PARAMETER_CONDITION, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_CONDITION ),
284 DECODE( x_PARAMETER_SEQUENCE, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_SEQUENCE ),
285 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID
286 );
287 OPEN C;
288 FETCH C INTO x_Rowid;
289 IF (C%NOTFOUND) THEN
290 CLOSE C;
291 RAISE NO_DATA_FOUND;
292 END IF;
293 END insert_jtf_perz_query_param;
294
295 -- ****************************************************************************
296 --******************************************************************************
297 --
298 -- APIs
299 --
300 -- 1. Create_Perz_Query
301 -- 2. Update_Perz_Query
302 -- 3. Delete_Perz_Query
303 -- 4. Get_Perz_Query_Summary
304 -- 5. Save_Perz_Query
305 -- 6. Get_Perz_Query
306 -- 7. check_query_duplicates
307 --
308 --******************************************************************************
309 --******************************************************************************
310 --
311 -- PROCEDURE check_query_duplicates()
312
313
314 PROCEDURE check_query_duplicates(
315 p_query_name IN VARCHAR2,
316 p_query_type IN VARCHAR2,
317 p_application_id IN NUMBER,
318 p_profile_id IN NUMBER,
319 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
320 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
321 x_object_version_number OUT NOCOPY /* file.sql.39 change */ NUMBER
322 )
323 IS
324 l_temp_id NUMBER := NULL;
325
326 BEGIN
327
328 IF ( ((p_query_name IS NOT NULL) AND (p_query_name <> Fnd_Api.G_MISS_CHAR)) AND
329 ((p_query_type IS NOT NULL) AND (p_query_type <> Fnd_Api.G_MISS_CHAR)) ) THEN
330
331 --IF (p_query_name IS NOT NULL) then
332
333 SELECT query_id, object_version_number
334 INTO x_query_id , x_object_version_number
335 FROM JTF_PERZ_QUERY
336 WHERE query_name = p_query_name AND
337 query_type = p_query_type AND
338 application_id = p_application_id AND
339 profile_id = p_profile_id;
340
341 IF (x_query_id IS NOT NULL) THEN
342 x_return_status := Fnd_Api.G_TRUE;
343 ELSE
344 x_return_status := Fnd_Api.G_FALSE;
345 END IF;
346
347
348 -- ELSIF (((p_query_name IS NOT NULL) OR
349 -- (p_query_name <> Fnd_Api.G_MISS_CHAR)) AND
350 -- ((x_query_id IS NULL) OR
351 -- (x_query_id = Fnd_Api.G_MISS_NUM)))THEN
352 --
353 -- SELECT query_id, object_version_number
354 -- INTO x_query_id , x_object_version_number
355 -- FROM JTF_PERZ_QUERY
356 -- WHERE query_name = p_query_name AND
357 -- application_id = p_application_id AND
358 -- profile_id = p_profile_id;
359 --
360 -- IF (x_query_id IS NOT NULL) THEN
361 -- x_return_status := Fnd_Api.G_TRUE;
362 -- ELSE
363 -- x_return_status := Fnd_Api.G_FALSE;
364 -- END IF;
365 --
366 -- ELSIF (((x_query_id IS NOT NULL) OR (x_query_id <> Fnd_Api.G_MISS_NUM)) AND
367 -- ((p_query_name IS NULL) OR (p_query_name = Fnd_Api.G_MISS_CHAR))) THEN
368 --
369 -- SELECT Query_id, object_version_number
370 -- INTO l_temp_id , x_object_version_number
371 -- FROM JTF_PERZ_QUERY
372 -- WHERE query_id = x_query_id;
373 --
374 -- IF (l_temp_id IS NOT NULL) THEN
375 -- x_return_status := Fnd_Api.G_TRUE;
376 -- x_query_id := l_temp_id;
377 -- ELSE
378 -- x_return_status := Fnd_Api.G_FALSE;
379 -- END IF;
380 --
381 -- ELSIF (((p_query_name IS NOT NULL) OR (p_query_name <> Fnd_Api.G_MISS_CHAR)) AND
382 -- ((x_query_id IS NOT NULL) OR (x_query_id <> Fnd_Api.G_MISS_NUM))) THEN
383 --
384 -- SELECT Query_id, object_version_number
385 -- INTO l_temp_id , x_object_version_number
386 -- FROM JTF_PERZ_QUERY
387 -- WHERE query_id = x_query_id;
388 --
389 -- IF (l_temp_id IS NOT NULL) THEN
390 -- x_return_status := Fnd_Api.G_TRUE;
391 -- x_query_id := l_temp_id;
392 -- ELSE
393 -- x_return_status := Fnd_Api.G_FALSE;
394 -- END IF;
395
396 ELSE
397 x_return_status := Fnd_Api.G_FALSE;
398
399 END IF;
400
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403 x_return_status := Fnd_Api.G_FALSE;
404 WHEN OTHERS THEN
405 x_return_status := Fnd_Api.G_FALSE;
406 END check_query_duplicates;
407
408 -- ****************************************************************************
409 --******************************************************************************
410
411 PROCEDURE Create_Perz_Query
412 ( p_api_version_number IN NUMBER,
413 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
414 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
415
416 p_application_id IN NUMBER,
417 p_profile_id IN NUMBER,
418 p_profile_name IN VARCHAR2,
419
420 p_query_id IN NUMBER,
421 p_query_name IN VARCHAR2,
422 p_query_type IN VARCHAR2,
423 p_query_desc IN VARCHAR2,
424 p_query_data_source IN VARCHAR2,
425
426 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
427 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
428 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
429 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
430 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,
431
432 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
433 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
434 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
435 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
436 ) IS
437
438 l_query_param_tbl Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
439 := p_query_param_tbl;
440
441 l_any_errors BOOLEAN := FALSE;
442 l_any_row_errors BOOLEAN := FALSE;
443 l_rowid ROWID;
444 l_return_status VARCHAR2(240) := Fnd_Api.G_RET_STS_SUCCESS;
445 l_api_name CONSTANT VARCHAR2(30) := 'Create Perz Query';
446 l_curr_row NUMBER := NULL;
447 l_query_name VARCHAR2(60) := p_query_name;
448
449 -- Variables for ids
450 l_query_string VARCHAR2(1) := NULL;
451 l_active_flag VARCHAR2(1) := 'Y';
452 l_profile_id NUMBER := NULL;
453 l_query_id NUMBER;
454 l_query_order_by_id NUMBER;
455 l_query_param_id NUMBER;
456 l_Query_Raw_Sql_ID NUMBER;
457 l_profile_attrib_id NUMBER;
458 l_is_duplicate VARCHAR2(1) := Fnd_Api.G_FALSE;
459 l_object_version_number NUMBER :=NULL;
460
461 BEGIN
462 -- ******* Standard Begins ********
463
464 -- Standard Start of API savepoint
465 SAVEPOINT CREATE_PERZ_QUERY_PVT;
466
467 -- -- Standard call to check for call compatibility.
468 -- IF NOT FND_API.Compatible_API_Call (
469 -- l_api_version_number,
470 -- p_api_version_number,
471 -- l_api_name,
472 -- G_PKG_NAME)
473 -- THEN
474 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
475 -- END IF;
476
477 -- Initialize message list if p_init_msg_list is set to TRUE.
478 IF Fnd_Api.to_Boolean( p_init_msg_list )
479 THEN
480 Fnd_Msg_Pub.initialize;
481 END IF;
482
483 -- Initialize API return status to success
484 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
485
486
487 -- CALL FLOW :
488 -- 1. check if duplicate exists if TRUE, return error
489 -- 2. if FALSE, do
490 -- 3. insert row with query data into query table
491 -- 4. pick query_id and cycle through parameters insert into parameter table
492 -- 5. pick query_id and cycle through order by insert into order by table
493 -- 6. pick query_id and cycle through raw SQL insert into raw SQL table
494
495
496 -- 1. CHECK IF DUPLICATE EXISTS
497 -- the duplicacy is defined having the same query name for the a profile id within an
498 -- application id.
499
500 check_query_duplicates (
501 p_query_name,
502 p_query_type,
503 p_application_id,
504 p_profile_id,
505 l_is_duplicate,
506 l_query_id,
507 l_object_version_number
508 );
509
510 IF (Fnd_Api.To_Boolean(l_is_duplicate)) THEN
511 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
512 RAISE Fnd_Api.G_EXC_ERROR;
513 END IF;
514
515 -- 3. insert row with query data into query table
516
517 insert_jtf_perz_query(
518 l_rowid,
519 l_query_id,
520 p_profile_id,
521 p_application_id,
522 p_query_name,
523 p_query_type,
524 p_query_desc,
525 p_query_data_source
526 );
527
528
529
530 -- 5. insert records into query orderby table
531
532
533 IF (p_query_order_by_tbl.COUNT > 0) THEN
534 FOR l_curr_row IN 1..p_query_order_by_tbl.COUNT LOOP
535
536 l_rowid := NULL;
537 l_query_order_by_id :=p_query_order_by_tbl(l_curr_row).query_order_by_id ;
538
539 insert_jtf_perz_query_order_by(
540 l_rowid,
541 l_query_Order_By_ID,
542 l_query_id,
543 p_query_order_by_tbl(l_curr_row).Parameter_Name,
544 p_query_order_by_tbl(l_curr_row).Acnd_Dcnd_Flag,
545 p_query_order_by_tbl(l_curr_row).Parameter_sequence
546 );
547
548
549 END LOOP;
550 END IF;
551
552
553 -- insert records into query param table
554
555 IF (p_query_param_tbl.COUNT > 0) THEN
556 FOR l_curr_row IN 1..p_query_param_tbl.COUNT LOOP
557
558 l_rowid := NULL;
559 l_query_param_id := p_query_param_tbl(l_curr_row).query_param_id;
560
561 insert_jtf_perz_query_param(
562 l_rowid,
563 l_query_param_id,
564 l_query_id,
565 p_query_param_tbl(l_curr_row).Parameter_Name,
566 p_query_param_tbl(l_curr_row).Parameter_Type,
567 p_query_param_tbl(l_curr_row).Parameter_Value,
568 p_query_param_tbl(l_curr_row).Parameter_condition,
569 p_query_param_tbl(l_curr_row).Parameter_sequence
570 );
571 END LOOP;
572 END IF;
573
574 -- insert records into query raw SQL table
575
576 IF (p_query_raw_sql_rec.Select_String IS NOT NULL) THEN
577
578 l_rowid := NULL;
579 l_Query_Raw_Sql_ID := p_query_raw_sql_rec.Query_Raw_Sql_ID;
580
581 insert_jtf_perz_query_raw_sql(
582 l_rowid,
583 l_Query_Raw_Sql_ID,
584 l_query_id,
585 p_query_raw_sql_rec.Select_String,
586 p_query_raw_sql_rec.From_String,
587 p_query_raw_sql_rec.Where_String,
588 p_query_raw_sql_rec.Order_by_String,
589 p_query_raw_sql_rec.Group_by_String,
590 p_query_raw_sql_rec.Having_String
591 );
592 END IF;
593
594 -- copying ID to output.
595 x_query_id := l_query_id;
596
597 -- ******** Standard Ends ***********
598 --
599 -- End of main API body.
600
601 -- Standard check of p_commit.
602 IF (Fnd_Api.To_Boolean(p_commit)) THEN
603 COMMIT WORK;
604 END IF;
605
606 -- Standard call to get message count and if count is 1, get message info.
607 Fnd_Msg_Pub.Count_And_Get(
608 p_count => x_msg_count,
609 p_data => x_msg_data );
610
611 EXCEPTION
612 WHEN Fnd_Api.G_EXC_ERROR THEN
613 ROLLBACK TO CREATE_PERZ_QUERY_PVT;
614 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
615
616 Fnd_Msg_Pub.Count_And_Get(
617 p_count => x_msg_count,
618 p_data => x_msg_data );
619
620 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
621 ROLLBACK TO CREATE_PERZ_QUERY_PVT;
622 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
623 --x_return_status := SQLCODE||SUBSTR(SQLERRM,1,100);
624
625 Fnd_Msg_Pub.Count_And_Get(
626 p_count => x_msg_count,
627 p_data => x_msg_data );
628
629 WHEN OTHERS THEN
630 ROLLBACK TO CREATE_PERZ_QUERY_PVT;
631 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
632
633 --x_return_status := SQLCODE||SUBSTR(SQLERRM,1,100);
634 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
635 THEN
636 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
637 END IF;
638
639 Fnd_Msg_Pub.Count_And_Get
640 ( p_count => x_msg_count,
641 p_data => x_msg_data );
642
643 END Create_perz_query;
644
645 --******************************************************************************
646 PROCEDURE Update_Perz_Query
647 ( p_api_version_number IN NUMBER,
648 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
649 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
650
651 p_application_id IN NUMBER,
652 p_profile_id IN NUMBER,
653
654 p_query_id IN NUMBER,
655 p_query_name IN VARCHAR2,
656 p_query_type IN VARCHAR2,
657 p_query_desc IN VARCHAR2,
658 p_query_data_source IN VARCHAR2,
659
660 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
661 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
662 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
663 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
664 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE ,
665
666 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
667 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
668 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
669 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
670 )IS
671
672 l_query_param_tbl Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
673 := p_query_param_tbl;
674
675 l_api_version NUMBER := 1.0;
676 l_init_msg_list VARCHAR2(240);
677 l_any_errors BOOLEAN := FALSE;
678 l_any_row_errors BOOLEAN := FALSE;
679 l_found_flag BOOLEAN := FALSE;
680 l_rowid ROWID;
681 l_return_status VARCHAR2(240) := Fnd_Api.G_RET_STS_SUCCESS;
682 l_api_name CONSTANT VARCHAR2(30) := 'Update Perz Query';
683
684 l_count NUMBER := NULL;
685 l_msg_count NUMBER := NULL;
686 l_msg_data VARCHAR2(200) := NULL;
687 l_count_1 NUMBER := NULL;
688 l_curr_row NUMBER := NULL;
689
690 l_duplicate VARCHAR2(240) := Fnd_Api.G_FALSE;
691 l_query_name VARCHAR2(60) := p_query_name;
692
693 -- Variables for ids
694 l_query_string VARCHAR2(1) := NULL;
695 l_active_flag VARCHAR2(1) := 'Y';
696 l_profile_id NUMBER := NULL;
697 l_query_id NUMBER;
698 l_Query_Order_By_ID NUMBER;
699 l_query_param_id NUMBER;
700 l_Query_Raw_Sql_ID NUMBER;
701 l_profile_attrib_id NUMBER;
702 l_is_duplicate VARCHAR2(1) := Fnd_Api.G_FALSE;
703 l_profile_name VARCHAR2(30) := NULL;
704 l_object_version_number NUMBER :=NULL;
705
706 BEGIN
707 -- ******* Standard Begins ********
708 -- Standard Start of API savepoint
709 SAVEPOINT UPDATE_PERZ_QUERY_PVT;
710
711 -- -- Standard call to check for call compatibility.
712 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
713 -- THEN
714 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
715 -- END IF;
716
717 -- Initialize message list if p_init_msg_list is set to TRUE.
718 IF Fnd_Api.to_Boolean( p_init_msg_list )
719 THEN
720 Fnd_Msg_Pub.initialize;
721 END IF;
722
723 -- Initialize API return status to success
724 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
725
726
727 -- CALL FLOW
728 -- 1. Select query ID from databe
729 -- 2. Delete all parameters for that Query ID
730 -- 3. Delete all Order By records for that ID
731 -- 4. Delete all raw SQL records for that query ID
732 -- 5. Update query header information
733 -- 6. Insert all new rocords from input.
734
735
736 -- 1. getting query ID for query Name
737 l_query_id := p_query_id;
738
739 check_query_duplicates (
740 p_query_name,
741 p_query_type,
742 p_application_id,
743 p_profile_id,
744 l_is_duplicate,
745 l_query_id,
746 l_object_version_number);
747
748
749 IF (Fnd_Api.To_Boolean(l_is_duplicate)) THEN
750
751 -- 2. Delete all parmeter table entries for this query_id
752
753 DELETE FROM JTF_PERZ_QUERY_PARAM WHERE QUERY_ID = l_query_id;
754
755 -- 3. Delete all order by table entries for this query_id
756 DELETE FROM JTF_PERZ_QUERY_ORDER_BY WHERE QUERY_ID = l_query_id;
757
758 -- 4. Delete all raw sql table entries for this query_id
759 DELETE FROM JTF_PERZ_QUERY_RAW_SQL WHERE QUERY_ID = l_query_id;
760
761 -- 5. Update query header information
762 -- **** UPDATE CALL HERE
763 update_jtf_perz_query(
764 l_QUERY_ID,
765 p_PROFILE_ID,
766 p_APPLICATION_ID,
767 p_QUERY_NAME,
768 p_QUERY_TYPE,
769 p_QUERY_DESC,
770 p_QUERY_DATA_SOURCE,
771 l_object_version_number);
772
773 -- 6. Insert new data into the three different tables
774
775 IF (p_query_order_by_tbl.COUNT > 0) THEN
776 FOR l_curr_row IN 1..p_query_order_by_tbl.COUNT LOOP
777
778 l_rowid := NULL;
779 l_Query_Order_By_ID := p_query_order_by_tbl(l_curr_row).Query_Order_By_ID;
780
781 insert_jtf_perz_query_order_by(l_rowid,
782 l_Query_Order_By_ID,
783 l_query_id,
784 p_query_order_by_tbl(l_curr_row).Parameter_Name,
785 p_query_order_by_tbl(l_curr_row).Acnd_Dcnd_Flag,
786 p_query_order_by_tbl(l_curr_row).Parameter_sequence );
787 END LOOP;
788 END IF;
789
790 IF (p_query_param_tbl.COUNT > 0) THEN
791 FOR l_curr_row IN 1..p_query_param_tbl.COUNT LOOP
792
793 l_rowid := NULL;
794 l_query_param_id := p_query_param_tbl(l_curr_row).query_param_id;
795
796 insert_jtf_perz_query_param(
797 l_rowid,
798 l_query_param_id,
799 l_query_id,
800 p_query_param_tbl(l_curr_row).Parameter_Name,
801 p_query_param_tbl(l_curr_row).Parameter_Type,
802 p_query_param_tbl(l_curr_row).Parameter_Value,
803 p_query_param_tbl(l_curr_row).Parameter_condition,
804 p_query_param_tbl(l_curr_row).Parameter_sequence );
805 END LOOP;
806 END IF;
807
808 IF (p_query_raw_sql_rec.Select_String IS NOT NULL) THEN
809 l_rowid := NULL;
810 l_Query_Raw_Sql_ID := p_query_raw_sql_rec.Query_Raw_Sql_ID;
811 insert_jtf_perz_query_raw_sql(
812 l_rowid,
813 l_Query_Raw_Sql_ID,
814 l_query_id,
815 p_query_raw_sql_rec.Select_String,
816 p_query_raw_sql_rec.From_String,
817 p_query_raw_sql_rec.Where_String,
818 p_query_raw_sql_rec.Order_by_String,
819 p_query_raw_sql_rec.Group_by_String,
820 p_query_raw_sql_rec.Having_String
821 );
822 END IF;
823 x_query_id := l_query_id;
824
825 ELSE
826 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
827 RAISE Fnd_Api.G_EXC_ERROR;
828 END IF;
829
830 EXCEPTION
831
832 WHEN Fnd_Api.G_EXC_ERROR THEN
833 -- dbms_output.put_line('stop 1 ');
834
835 ROLLBACK TO UPDATE_PERZ_QUERY_PVT;
836 --x_return_status := FND_API.G_RET_STS_ERROR ;
837 x_return_status := SQLCODE||SUBSTR(SQLERRM,1,100);
838
839 Fnd_Msg_Pub.Count_And_Get(
840 p_count => x_msg_count,
841 p_data => x_msg_data );
842
843 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
844 -- dbms_output.put_line('stop 2 ');
845 ROLLBACK TO UPDATE_PERZ_QUERY_PVT;
846 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
847
848 Fnd_Msg_Pub.Count_And_Get(
849 p_count => x_msg_count,
850 p_data => x_msg_data );
851
852 WHEN OTHERS THEN
853 -- dbms_output.put_line('stop 3 ');
854 ROLLBACK TO UPDATE_PERZ_QUERY_PVT;
855 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
856 --x_return_status := SQLCODE||SUBSTR(SQLERRM,1,100);
857
858 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
859 THEN
860 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
861 END IF;
862
863 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
864 p_data => x_msg_data );
865
866
867 END update_perz_query;
868 --******************************************************************************
869 PROCEDURE Delete_Perz_Query
870 ( p_api_version_number IN NUMBER,
871 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
872 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
873
874 p_application_id IN NUMBER,
875 p_profile_id IN NUMBER,
876 p_query_id IN NUMBER,
877
878 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
879 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
880 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
881 )IS
882
883 l_query_id NUMBER;
884 l_api_name CONSTANT VARCHAR2(30) := 'Delete Profile';
885 BEGIN
886 -- Standard Start of API savepoint
887 SAVEPOINT DELETE_PERZ_QUERY_PVT;
888
889 -- -- Standard call to check for call compatibility.
890 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
891 -- THEN
892 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893 -- END IF;
894
895 -- Initialize message list if p_init_msg_list is set to TRUE.
896 IF Fnd_Api.to_Boolean( p_init_msg_list )
897 THEN
898 Fnd_Msg_Pub.initialize;
899 END IF;
900
901 -- Initialize API return status to success
902 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
903
904
905 -- CALL FLOW
906 -- 1. Select query ID from databe
907 -- 2. Delete all parameters for that Query ID
908 -- 3. Delete all Order By records for that ID
909 -- 4. Delete all raw SQL records for that query ID
910 -- 5. Update query header information
911 -- 6. Insert all new rocords from input.
912
913
914 -- 1. getting query ID for query Name
915 l_query_id := p_query_id;
916
917 -- IF (p_query_id = NULL) THEN
918 -- check_query_duplicates ( p_query_name,
919 -- p_application_id,
920 -- p_profile_id,
921 -- l_is_duplicate,
922 -- l_query_id);
923 -- END IF;
924 --
925 -- dbms_output.put_line('id from databe ' || l_query_id);
926
927 -- 2. Delete all parmeter table entries for this query_id
928
929 DELETE FROM JTF_PERZ_QUERY_PARAM WHERE QUERY_ID = l_query_id;
930
931 -- 3. Delete all order by table entries for this query_id
932 DELETE FROM JTF_PERZ_QUERY_ORDER_BY WHERE QUERY_ID = l_query_id;
933
934 -- 4. Delete all raw sql table entries for this query_id
935 DELETE FROM JTF_PERZ_QUERY_RAW_SQL WHERE QUERY_ID = l_query_id;
936
937 -- 5. Delete query header table entries for this query_id
938 DELETE FROM JTF_PERZ_QUERY WHERE QUERY_ID = l_query_id;
939
940 EXCEPTION
941
942 WHEN Fnd_Api.G_EXC_ERROR THEN
943 --dbms_output.put_line('stop 1 ');
944
945 ROLLBACK TO DELETE_PERZ_QUERY_PVT;
946 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
947
948 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
949 p_data => x_msg_data );
950
951 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
952 --dbms_output.put_line('stop 2 ');
953 ROLLBACK TO DELETE_PERZ_QUERY_PVT;
954 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
955
956 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
957 p_data => x_msg_data );
958
959 WHEN OTHERS THEN
960 --dbms_output.put_line('stop 3 ');
961 ROLLBACK TO DELETE_PERZ_QUERY_PVT;
962 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
963
964 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
965 THEN
966 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
967 END IF;
968
969 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
970 p_data => x_msg_data );
971
972
973 END Delete_perz_query;
974
975 --******************************************************************************
976
977 PROCEDURE Get_Perz_Query_Summary
978 ( p_api_version_number IN NUMBER,
979 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
980
981 p_application_id IN NUMBER,
982 p_profile_id IN NUMBER,
983 p_profile_name IN VARCHAR2,
984
985 p_query_id IN NUMBER,
986 p_query_name IN VARCHAR2,
987 p_query_type IN VARCHAR2,
988
989 x_query_out_tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_QUERY_PUB.QUERY_OUT_TBL_TYPE,
990
991 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
992 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
993 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
994 ) IS
995
996 l_api_version NUMBER := 1.0;
997 l_init_msg_list VARCHAR2(240);
998 l_any_errors BOOLEAN := FALSE;
999 l_any_row_errors BOOLEAN := FALSE;
1000 l_found_flag BOOLEAN := FALSE;
1001 l_rowid ROWID;
1002 l_return_status VARCHAR2(240) := Fnd_Api.G_RET_STS_SUCCESS;
1003 l_api_name CONSTANT VARCHAR2(30) := 'Get Perz Query Summary';
1004
1005 l_count NUMBER := 0;
1006 l_msg_count NUMBER := NULL;
1007 l_msg_data VARCHAR2(200) := NULL;
1008 l_count_1 NUMBER := NULL;
1009 l_curr_row NUMBER := NULL;
1010
1011 l_duplicate VARCHAR2(240) := Fnd_Api.G_FALSE;
1012 l_query_name VARCHAR2(100) := p_query_name;
1013
1014 -- Variables for ids
1015 l_query_string VARCHAR2(1) := NULL;
1016 l_active_flag VARCHAR2(1) := 'Y';
1017 l_profile_id NUMBER := p_profile_id;
1018 l_query_id NUMBER;
1019 l_is_duplicate VARCHAR2(1);
1020 l_profile_name VARCHAR2(30) := NULL;
1021
1022 -- Temporary variables
1023 l_query_id_temp NUMBER;
1024 l_profile_id_temp NUMBER;
1025 l_application_id_temp NUMBER;
1026 l_query_name_temp VARCHAR2(100);
1027 l_query_type_temp VARCHAR2(100);
1028 l_query_description_temp VARCHAR2(240);
1029 l_query_data_source_temp VARCHAR2(2000);
1030 --
1031 -- cursors
1032
1033 CURSOR C_Get_Query_Summary (p_profile_id NUMBER, p_application_id NUMBER) IS
1034 SELECT Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
1035 Query_Description, Query_Data_source
1036 FROM JTF_PERZ_QUERY
1037 WHERE Profile_ID = p_profile_id AND Application_ID = p_application_id;
1038
1039 CURSOR C_Get_Query_Summary_qid (p_query_id NUMBER ) IS
1040 SELECT Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
1041 Query_Description, Query_Data_source
1042 FROM JTF_PERZ_QUERY
1043 WHERE query_id = p_query_id;
1044
1045 CURSOR C_Get_Query_Summary_qnmty (p_query_name VARCHAR2, p_query_type VARCHAR2,
1046 p_profile_id NUMBER, p_application_id NUMBER) IS
1047 SELECT Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
1048 Query_Description, Query_Data_source
1049 FROM JTF_PERZ_QUERY
1050 WHERE query_type = p_query_type
1051 AND query_name = p_query_name
1052 AND Profile_ID = p_profile_id
1053 AND Application_ID = p_application_id;
1054
1055 CURSOR C_Get_Query_Summary_qty (p_query_type VARCHAR2, p_profile_id NUMBER, p_application_id NUMBER) IS
1056 SELECT Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
1057 Query_Description, Query_Data_source
1058 FROM JTF_PERZ_QUERY
1059 WHERE query_type = p_query_type AND Profile_ID = p_profile_id AND Application_ID = p_application_id;
1060
1061 CURSOR C_Get_Query_Summary_qnm (p_query_name VARCHAR2, p_profile_id NUMBER, p_application_id NUMBER) IS
1062 SELECT Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
1063 Query_Description, Query_Data_source
1064 FROM JTF_PERZ_QUERY
1065 WHERE query_name = p_query_name AND Profile_ID = p_profile_id AND Application_ID = p_application_id;
1066
1067 BEGIN
1068 -- ******* Standard Begins ********
1069
1070 -- -- Standard call to check for call compatibility.
1071 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
1072 -- THEN
1073 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1074 -- END IF;
1075
1076 -- Initialize message list if p_init_msg_list is set to TRUE.
1077 IF Fnd_Api.to_Boolean( p_init_msg_list )
1078 THEN
1079 Fnd_Msg_Pub.initialize;
1080 END IF;
1081 -- Initialize API return status to success
1082 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1083
1084
1085 IF ((p_query_id IS NOT NULL) AND
1086 (p_query_id <> Fnd_Api.G_MISS_NUM)) THEN
1087
1088 l_count := 1;
1089 OPEN C_Get_Query_Summary_qid(p_query_id);
1090 LOOP
1091 FETCH C_Get_Query_Summary_qid INTO
1092 l_query_id_temp,
1093 l_profile_id_temp,
1094 l_application_id_temp,
1095 l_query_name_temp,
1096 l_query_type_temp,
1097 l_query_description_temp,
1098 l_query_data_source_temp;
1099 EXIT WHEN C_Get_Query_Summary_qid%NOTFOUND;
1100
1101 IF (C_Get_Query_Summary_qid%FOUND = TRUE) THEN
1102 x_query_out_tbl(l_count).query_id := l_query_id_temp;
1103 x_query_out_tbl(l_count).profile_id := l_profile_id_temp;
1104 x_query_out_tbl(l_count).application_id := l_application_id_temp;
1105 x_query_out_tbl(l_count).query_name := l_query_name_temp;
1106 x_query_out_tbl(l_count).query_type := l_query_type_temp;
1107 x_query_out_tbl(l_count).query_description := l_query_description_temp;
1108 x_query_out_tbl(l_count).query_data_source := l_query_data_source_temp;
1109 l_count := l_count + 1;
1110 END IF;
1111 END LOOP;
1112 CLOSE C_Get_Query_Summary_qid;
1113
1114 ELSE
1115 l_profile_id := p_profile_id;
1116 l_profile_name := p_profile_name;
1117 l_return_status := Fnd_Api.G_TRUE;
1118
1119 IF ( l_profile_id IS NULL ) THEN
1120 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(l_profile_name,
1121 l_return_status,
1122 l_profile_id);
1123
1124 -- If profile does not exists, raise an error and exit
1125
1126 IF (l_return_status = Fnd_Api.G_FALSE) THEN
1127 RAISE Fnd_Api.G_EXC_ERROR;
1128 END IF;
1129 END IF;
1130
1131 IF (((l_profile_id IS NOT NULL) AND
1132 (l_profile_id <> Fnd_Api.G_MISS_NUM)) AND
1133 ((p_application_id IS NOT NULL) AND
1134 (p_application_id <> Fnd_Api.G_MISS_NUM))) THEN
1135
1136 IF((p_query_name IS NOT NULL) AND (p_query_name <> Fnd_Api.G_MISS_CHAR)
1137 AND (p_query_type IS NOT NULL) AND (p_query_type <> Fnd_Api.G_MISS_CHAR)) THEN
1138 /* if query name and type was given */
1139 l_count := 1;
1140 OPEN C_Get_Query_Summary_qnmty(p_query_name,p_query_type,
1141 l_profile_id, p_application_id);
1142 LOOP
1143 FETCH C_Get_Query_Summary_qnmty INTO
1144 l_query_id_temp,
1145 l_profile_id_temp,
1146 l_application_id_temp,
1147 l_query_name_temp,
1148 l_query_type_temp,
1149 l_query_description_temp,
1150 l_query_data_source_temp;
1151 EXIT WHEN C_Get_Query_Summary_qnmty%NOTFOUND;
1152
1153 IF (C_Get_Query_Summary_qnmty%FOUND = TRUE) THEN
1154 x_query_out_tbl(l_count).query_id := l_query_id_temp;
1155 x_query_out_tbl(l_count).profile_id := l_profile_id_temp;
1156 x_query_out_tbl(l_count).application_id := l_application_id_temp;
1157 x_query_out_tbl(l_count).query_name := l_query_name_temp;
1158 x_query_out_tbl(l_count).query_type := l_query_type_temp;
1159 x_query_out_tbl(l_count).query_description := l_query_description_temp;
1160 x_query_out_tbl(l_count).query_data_source := l_query_data_source_temp;
1161 l_count := l_count + 1;
1162 END IF;
1163 END LOOP;
1164 CLOSE C_Get_Query_Summary_qnmty;
1165
1166 RETURN;
1167 END IF; -- end for name and type
1168
1169 IF((p_query_name IS NOT NULL) AND
1170 (p_query_name <> Fnd_Api.G_MISS_CHAR)) THEN
1171 /* if query name was given */
1172 l_count := 1;
1173 OPEN C_Get_Query_Summary_qnm(p_query_name,
1174 l_profile_id, p_application_id);
1175 LOOP
1176 FETCH C_Get_Query_Summary_qnm INTO
1177 l_query_id_temp,
1178 l_profile_id_temp,
1179 l_application_id_temp,
1180 l_query_name_temp,
1181 l_query_type_temp,
1182 l_query_description_temp,
1183 l_query_data_source_temp;
1184 EXIT WHEN C_Get_Query_Summary_qnm%NOTFOUND;
1185
1186 IF (C_Get_Query_Summary_qnm%FOUND = TRUE) THEN
1187 x_query_out_tbl(l_count).query_id := l_query_id_temp;
1188 x_query_out_tbl(l_count).profile_id := l_profile_id_temp;
1189 x_query_out_tbl(l_count).application_id := l_application_id_temp;
1190 x_query_out_tbl(l_count).query_name := l_query_name_temp;
1191 x_query_out_tbl(l_count).query_type := l_query_type_temp;
1192 x_query_out_tbl(l_count).query_description := l_query_description_temp;
1193 x_query_out_tbl(l_count).query_data_source := l_query_data_source_temp;
1194 l_count := l_count + 1;
1195 END IF;
1196 END LOOP;
1197 CLOSE C_Get_Query_Summary_qnm;
1198
1199 RETURN;
1200 END IF; -- end for name
1201
1202 IF((p_query_type IS NOT NULL) AND
1203 (p_query_type <> Fnd_Api.G_MISS_CHAR)) THEN
1204 /* if query type was given */
1205 l_count := 1;
1206 OPEN C_Get_Query_Summary_qty(p_query_type,
1207 l_profile_id, p_application_id);
1208 LOOP
1209 FETCH C_Get_Query_Summary_qty INTO
1210 l_query_id_temp,
1211 l_profile_id_temp,
1212 l_application_id_temp,
1213 l_query_name_temp,
1214 l_query_type_temp,
1215 l_query_description_temp,
1216 l_query_data_source_temp;
1217 EXIT WHEN C_Get_Query_Summary_qty%NOTFOUND;
1218
1219 IF (C_Get_Query_Summary_qty%FOUND = TRUE) THEN
1220 x_query_out_tbl(l_count).query_id := l_query_id_temp;
1221 x_query_out_tbl(l_count).profile_id := l_profile_id_temp;
1222 x_query_out_tbl(l_count).application_id := l_application_id_temp;
1223 x_query_out_tbl(l_count).query_name := l_query_name_temp;
1224 x_query_out_tbl(l_count).query_type := l_query_type_temp;
1225 x_query_out_tbl(l_count).query_description := l_query_description_temp;
1226 x_query_out_tbl(l_count).query_data_source := l_query_data_source_temp;
1227 l_count := l_count + 1;
1228 END IF;
1229 END LOOP;
1230 CLOSE C_Get_Query_Summary_qty;
1231
1232 RETURN;
1233 END IF; -- end for type
1234
1235 /* if query name or type was not given */
1236 l_count := 1;
1237 OPEN C_Get_Query_Summary(l_profile_id, p_application_id);
1238 LOOP
1239 FETCH C_Get_Query_Summary INTO
1240 l_query_id_temp,
1241 l_profile_id_temp,
1242 l_application_id_temp,
1243 l_query_name_temp,
1244 l_query_type_temp,
1245 l_query_description_temp,
1246 l_query_data_source_temp;
1247 EXIT WHEN C_Get_Query_Summary%NOTFOUND;
1248
1249 IF (C_Get_Query_Summary%FOUND = TRUE) THEN
1250 x_query_out_tbl(l_count).query_id := l_query_id_temp;
1251 x_query_out_tbl(l_count).profile_id := l_profile_id_temp;
1252 x_query_out_tbl(l_count).application_id := l_application_id_temp;
1253 x_query_out_tbl(l_count).query_name := l_query_name_temp;
1254 x_query_out_tbl(l_count).query_type := l_query_type_temp;
1255 x_query_out_tbl(l_count).query_description := l_query_description_temp;
1256 x_query_out_tbl(l_count).query_data_source := l_query_data_source_temp;
1257 l_count := l_count + 1;
1258 END IF;
1259 END LOOP;
1260 CLOSE C_Get_Query_Summary;
1261
1262 END IF; -- endif for profile/appid check
1263
1264 END IF; -- endif for query id
1265
1266 EXCEPTION
1267
1268 WHEN Fnd_Api.G_EXC_ERROR THEN
1269 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
1270
1271 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
1272 p_data => x_msg_data );
1273
1274 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1275 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1276
1277 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
1278 p_data => x_msg_data );
1279
1280 WHEN OTHERS THEN
1281 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1282
1283 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1284 THEN
1285 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1286 END IF;
1287
1288 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
1289 p_data => x_msg_data );
1290
1291
1292 END get_perz_query_summary;
1293
1294
1295 --******************************************************************************
1296
1297
1298 PROCEDURE Get_Perz_Query
1299 ( p_api_version_number IN NUMBER,
1300 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1301
1302 p_application_id IN NUMBER,
1303 p_profile_id IN NUMBER,
1304 p_profile_name IN VARCHAR2,
1305
1306 p_query_id IN NUMBER,
1307 p_query_name IN VARCHAR2,
1308 p_query_type IN VARCHAR2,
1309
1310 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1311 x_query_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1312 x_query_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1313 x_query_desc OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1314 x_query_data_source OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1315
1316 x_query_param_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE,
1317 x_query_order_by_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE,
1318 x_query_raw_sql_rec OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,
1319
1320 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1321 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1322 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1323 )IS
1324 -- ******* Get_Perz_Query Local Variables - Standards ********
1325 l_api_name VARCHAR2(60) := 'Get_Perz_Query';
1326 l_api_version_number NUMBER := p_api_version_number;
1327
1328 -- ******* Get_Perz_Query Local Variables ********
1329 l_query_out_tbl Jtf_Perz_Query_Pub.QUERY_OUT_TBL_TYPE;
1330
1331 l_count NUMBER := NULL;
1332
1333 --Temporary variables
1334 l_QUERY_PARAM_ID_temp NUMBER;
1335 l_QUERY_ID_temp NUMBER;
1336 l_PARAMETER_NAME_temp VARCHAR2(60);
1337 l_PARAMETER_TYPE_temp VARCHAR2(30);
1338 l_PARAMETER_VALUE_temp VARCHAR2(300);
1339 l_PARAMETER_CONDITION_temp VARCHAR2(10);
1340 l_PARAMETER_SEQUENCE_temp NUMBER;
1341 l_QUERY_RAW_SQL_ID_temp NUMBER;
1342 l_SELECT_STRING_temp VARCHAR2(200);
1343 l_FROM_STRING_temp VARCHAR2(200);
1344 l_WHERE_STRING_temp VARCHAR2(200);
1345 l_ORDER_BY_STRING_temp VARCHAR2(200);
1346 l_GROUP_BY_STRING_temp VARCHAR2(200);
1347 l_HAVING_STRING_temp VARCHAR2(200);
1348 l_QUERY_ORDER_BY_ID_temp NUMBER;
1349 l_ACND_DCND_FLAG_temp VARCHAR2(1);
1350
1351
1352 CURSOR C_Get_Query_param (p_query_id NUMBER) IS
1353 SELECT QUERY_PARAM_ID, QUERY_ID, PARAMETER_NAME,
1354 PARAMETER_TYPE, PARAMETER_VALUE, PARAMETER_CONDITION,
1355 PARAMETER_SEQUENCE
1356 FROM JTF_PERZ_QUERY_PARAM
1357 WHERE QUERY_ID = p_query_id
1358 ORDER BY PARAMETER_SEQUENCE;
1359
1360 CURSOR C_Get_Query_Order_By (p_query_id NUMBER) IS
1361 SELECT QUERY_ORDER_BY_ID, QUERY_ID, PARAMETER_NAME,
1362 ACND_DCND_FLAG, PARAMETER_SEQUENCE
1363 FROM JTF_PERZ_QUERY_ORDER_BY
1364 WHERE QUERY_ID = p_query_id
1365 ORDER BY PARAMETER_SEQUENCE;
1366 BEGIN
1367 -- ******* Standard Begins ********
1368
1369 -- -- Standard call to check for call compatibility.
1370 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
1371 -- THEN
1372 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1373 -- END IF;
1374
1375 -- Initialize message list if p_init_msg_list is set to TRUE.
1376 IF Fnd_Api.to_Boolean( p_init_msg_list )
1377 THEN
1378 Fnd_Msg_Pub.initialize;
1379 END IF;
1380
1381 -- Initialize API return status to success
1382 x_return_status := Fnd_Api.G_RET_STS_SUCCESS ;
1383
1384
1385 -- ******* Get_Perz_Query Execution Plan ********
1386 -- execution steps
1387 --
1388 --1. Call get_perz_query_summary()
1389 --2. If Sucessfdul and QUERY OUT TABLE returns more than one record,
1390 -- raise exception if error
1391 --3. If QUERY OUT TABLE returns one record only, then
1392 --4. a. Get QUERY PARAMETERS
1393 -- b. Get RAW SQL
1394 -- c. Get ORDER BY
1395 -- d. sign the results to out parameters
1396 --5. Commit the whole thing
1397
1398 -- Get_Perz_Query Implementation
1399
1400 --1. Call get_perz_query_summary()
1401
1402 Get_Perz_Query_Summary
1403 ( p_api_version_number ,
1404 p_init_msg_list ,
1405 p_application_id,
1406 p_profile_id ,
1407 p_profile_name ,
1408 p_query_id,
1409 p_query_name ,
1410 p_query_type,
1411 l_query_out_tbl,
1412 x_return_status,
1413 x_msg_count,
1414 x_msg_data
1415 );
1416
1417 l_count := l_query_out_tbl.COUNT;
1418
1419 --dbms_output.put_line('l_count from summary output '||l_count);
1420 IF ( l_count = 1) THEN
1421
1422 --3. If QUERY OUT TABLE returns one record only, then
1423 x_query_id := l_query_out_tbl(1).QUERY_ID;
1424 x_query_name := l_query_out_tbl(1).QUERY_NAME;
1425 x_query_desc := l_query_out_tbl(1).QUERY_DESCRIPTION;
1426 x_query_type := l_query_out_tbl(1).QUERY_TYPE;
1427 x_query_data_source := l_query_out_tbl(1).QUERY_DATA_SOURCE;
1428
1429 --4. a. Get QUERY PARAMETERS
1430
1431 l_QUERY_PARAM_ID_temp := NULL;
1432 l_QUERY_ID_temp := NULL;
1433 l_PARAMETER_NAME_temp := NULL;
1434 l_PARAMETER_TYPE_temp := NULL;
1435 l_PARAMETER_VALUE_temp := NULL;
1436 l_PARAMETER_CONDITION_temp := NULL;
1437 l_PARAMETER_SEQUENCE_temp := NULL;
1438
1439 l_count := 1;
1440 OPEN C_Get_Query_param(x_query_id);
1441 LOOP
1442 FETCH C_Get_Query_param INTO
1443 l_QUERY_PARAM_ID_temp,
1444 l_QUERY_ID_temp,
1445 l_PARAMETER_NAME_temp,
1446 l_PARAMETER_TYPE_temp,
1447 l_PARAMETER_VALUE_temp,
1448 l_PARAMETER_CONDITION_temp,
1449 l_PARAMETER_SEQUENCE_temp;
1450 EXIT WHEN C_Get_Query_param%NOTFOUND;
1451 IF (C_Get_Query_param%FOUND = TRUE) THEN
1452 x_query_param_tbl(l_count).QUERY_PARAM_ID := l_QUERY_PARAM_ID_temp;
1453 x_query_param_tbl(l_count).QUERY_ID := l_QUERY_ID_temp;
1454 x_query_param_tbl(l_count).PARAMETER_NAME := l_PARAMETER_NAME_temp;
1455 x_query_param_tbl(l_count).PARAMETER_TYPE := l_PARAMETER_TYPE_temp;
1456 x_query_param_tbl(l_count).PARAMETER_VALUE := l_PARAMETER_VALUE_temp;
1457 x_query_param_tbl(l_count).PARAMETER_CONDITION := l_PARAMETER_CONDITION_temp;
1458 x_query_param_tbl(l_count).PARAMETER_SEQUENCE := l_PARAMETER_SEQUENCE_temp;
1459 l_count := l_count +1;
1460 END IF;
1461 END LOOP;
1462 CLOSE C_Get_Query_param;
1463
1464 -- c. Get ORDER BY
1465
1466
1467 l_QUERY_ORDER_BY_ID_temp := NULL;
1468 l_QUERY_ID_temp := NULL;
1469 l_PARAMETER_NAME_temp := NULL;
1470 l_ACND_DCND_FLAG_temp := NULL;
1471 l_PARAMETER_SEQUENCE_temp := NULL;
1472 l_count := 1;
1473 OPEN C_Get_Query_Order_By (x_query_id );
1474 LOOP
1475 FETCH C_Get_Query_Order_By INTO
1476 l_QUERY_ORDER_BY_ID_temp,
1477 l_QUERY_ID_temp,
1478 l_PARAMETER_NAME_temp,
1479 l_ACND_DCND_FLAG_temp,
1480 l_PARAMETER_SEQUENCE_temp;
1481 EXIT WHEN C_Get_Query_Order_By%NOTFOUND;
1482 IF (C_Get_Query_Order_By%FOUND) THEN
1483 x_query_order_by_tbl(l_count).QUERY_ORDER_BY_ID := l_QUERY_ORDER_BY_ID_temp;
1484 x_query_order_by_tbl(l_count).QUERY_ID := l_QUERY_ID_temp;
1485 x_query_order_by_tbl(l_count).PARAMETER_NAME := l_PARAMETER_NAME_temp;
1486 x_query_order_by_tbl(l_count).ACND_DCND_FLAG := l_ACND_DCND_FLAG_temp;
1487 x_query_order_by_tbl(l_count).PARAMETER_SEQUENCE := l_PARAMETER_SEQUENCE_temp;
1488 l_count := l_count +1;
1489 END IF;
1490 END LOOP;
1491 CLOSE C_Get_Query_Order_By;
1492
1493 -- b. Get RAW SQL
1494
1495
1496 l_QUERY_RAW_SQL_ID_temp := NULL;
1497 l_QUERY_ID_temp := NULL;
1498 l_SELECT_STRING_temp := NULL;
1499 l_FROM_STRING_temp := NULL;
1500 l_WHERE_STRING_temp := NULL;
1501 l_ORDER_BY_STRING_temp := NULL;
1502 l_GROUP_BY_STRING_temp := NULL;
1503 l_HAVING_STRING_temp := NULL;
1504
1505 BEGIN
1506
1507 SELECT QUERY_RAW_SQL_ID, QUERY_ID, SELECT_STRING,
1508 FROM_STRING, WHERE_STRING, ORDER_BY_STRING,
1509 GROUP_BY_STRING, HAVING_STRING
1510 INTO
1511 x_query_raw_sql_rec.QUERY_RAW_SQL_ID,
1512 x_query_raw_sql_rec.QUERY_ID,
1513 x_query_raw_sql_rec.SELECT_STRING,
1514 x_query_raw_sql_rec.FROM_STRING,
1515 x_query_raw_sql_rec.WHERE_STRING,
1516 x_query_raw_sql_rec.ORDER_BY_STRING,
1517 x_query_raw_sql_rec.GROUP_BY_STRING,
1518 x_query_raw_sql_rec.HAVING_STRING
1519 FROM JTF_PERZ_QUERY_RAW_SQL
1520 WHERE QUERY_ID = p_query_id;
1521
1522 EXCEPTION
1523 WHEN NO_DATA_FOUND THEN
1524 NULL;
1525 END;
1526
1527 ELSE
1528 --2. If Sucessfdul and QUERY OUT TABLE returns more than one record,
1529 -- raise exception if error
1530 RAISE Fnd_Api.G_EXC_ERROR;
1531 END IF;
1532
1533 EXCEPTION
1534
1535 WHEN Fnd_Api.G_EXC_ERROR THEN
1536 -- dbms_output.put_line('stop 1 ');
1537 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
1538
1539 Fnd_Msg_Pub.Count_And_Get
1540 ( p_count => x_msg_count,
1541 p_data => x_msg_data );
1542
1543
1544 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1545 --dbms_output.put_line('stop 2 ');
1546 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1547
1548 Fnd_Msg_Pub.Count_And_Get
1549 ( p_count => x_msg_count,
1550 p_data => x_msg_data );
1551
1552
1553 WHEN OTHERS THEN
1554 --dbms_output.put_line('stop 3 ');
1555 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1556
1557 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1558 THEN
1559 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1560 END IF;
1561
1562
1563 Fnd_Msg_Pub.Count_And_Get
1564 ( p_count => x_msg_count,
1565 p_data => x_msg_data );
1566
1567
1568
1569 END Get_Perz_Query;
1570 --******************************************************************************
1571 PROCEDURE Save_Perz_Query
1572 ( p_api_version_number IN NUMBER,
1573 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1574 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1575 p_application_id IN NUMBER,
1576 p_profile_id IN NUMBER,
1577 p_profile_name IN VARCHAR2,
1578 p_profile_type IN VARCHAR2,
1579 p_Profile_Attrib IN Jtf_Perz_Profile_Pub.PROFILE_ATTRIB_TBL_TYPE
1580 := Jtf_Perz_Profile_Pub.G_MISS_PROFILE_ATTRIB_TBL,
1581 p_query_id IN NUMBER,
1582 p_query_name IN VARCHAR2,
1583 p_query_type IN VARCHAR2,
1584 p_query_desc IN VARCHAR2,
1585 p_query_data_source IN VARCHAR2,
1586 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
1587 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
1588 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
1589 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
1590 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE ,
1591 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1592 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1593 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1594 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1595 )IS
1596 -- ******* Save_Perz_Query Local Variables - Standards ********
1597 l_api_name VARCHAR2(60) := 'Save_Perz_Query';
1598 l_api_version_number NUMBER := p_api_version_number;
1599
1600 -- ******* Save_Perz_Query Local Variables ********
1601
1602 l_return_status VARCHAR2(240) := Fnd_Api.G_RET_STS_SUCCESS;
1603 l_query_id NUMBER;
1604 l_profile_id NUMBER;
1605 l_is_duplicate VARCHAR2(1) := Fnd_Api.G_FALSE;
1606
1607
1608 l_profile_attrib Jtf_Perz_Profile_Pub.PROFILE_ATTRIB_TBL_TYPE
1609 := p_profile_attrib;
1610 l_profile_name VARCHAR2(60) := p_profile_name;
1611
1612 l_commit VARCHAR2(1) := Fnd_Api.G_TRUE;
1613 l_object_version_number NUMBER :=NULL;
1614
1615
1616
1617 BEGIN
1618 -- ******* Standard Begins ********
1619
1620 -- Standard Start of API savepoint
1621 SAVEPOINT SAVE_PERZ_QUERY_PVT;
1622
1623 -- -- Standard call to check for call compatibility.
1624 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
1625 -- THEN
1626 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1627 -- END IF;
1628
1629 -- Initialize message list if p_init_msg_list is set to TRUE.
1630 IF Fnd_Api.to_Boolean( p_init_msg_list )
1631 THEN
1632 Fnd_Msg_Pub.initialize;
1633 END IF;
1634
1635 -- Initialize API return status to success
1636 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1637
1638
1639 -- CALL FLOW :
1640 -- 1. Check for profile, if not existing create profile.
1641 -- 2. check if duplicate query exists if TRUE,
1642 -- then call update()
1643 -- else call insert()
1644
1645 -- 1. check profile
1646
1647 l_profile_id := p_profile_id;
1648
1649 Jtf_Perz_Profile_Pvt.check_profile_duplicates(
1650 l_profile_name,
1651 l_return_status,
1652 l_profile_id
1653 );
1654 -- dbms_output.put_line('profile id is:'||l_profile_id);
1655
1656 -- 1.1 if profile is not available, create profile
1657
1658 IF (l_return_status = Fnd_Api.G_FALSE) THEN
1659
1660 l_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1661 l_commit := Fnd_Api.G_FALSE;
1662 l_profile_id := p_profile_id;
1663
1664 Jtf_Perz_Profile_Pvt.Create_Profile(
1665 p_api_version_number => l_api_version_number,
1666 p_commit => l_commit,
1667 p_profile_id => l_profile_id,
1668 p_profile_name => p_profile_name,
1669 p_profile_type => p_profile_type,
1670 p_profile_attrib_tbl => l_profile_attrib,
1671 x_profile_name => l_profile_name,
1672 x_profile_id => l_profile_id,
1673 x_return_status => l_return_status,
1674 x_msg_count => x_msg_count,
1675 x_msg_data => x_msg_data
1676 );
1677
1678 IF (l_return_status <> Fnd_Api.G_RET_STS_SUCCESS) THEN
1679 RAISE Fnd_Api.G_EXC_ERROR;
1680 END IF;
1681 -- dbms_output.put_line('2 profile id is:'||l_profile_id);
1682 END IF;
1683
1684
1685 -- 2. CHECK IF DUPLICATE QUERY EXISTS
1686 -- the duplicacy is defined having the same query name
1687 -- for the a profile id within an application id.
1688
1689 check_query_duplicates (
1690 p_query_name,
1691 p_query_type,
1692 p_application_id,
1693 l_profile_id,
1694 l_is_duplicate,
1695 l_query_id,
1696 l_object_version_number
1697 );
1698
1699
1700 l_commit := Fnd_Api.G_FALSE;
1701 IF (Fnd_Api.To_Boolean(l_is_duplicate)) THEN
1702 --dbms_output.put_line(' duplicate query exists!'||l_query_id);
1703 -- dbms_output.put_line(' duplicate query exists, profile id is:'||l_profile_id);
1704 -- Call update_perz_query
1705
1706 Update_Perz_Query
1707 ( p_api_version_number,
1708 p_init_msg_list,
1709 l_commit,
1710
1711 p_application_id,
1712 l_profile_id,
1713
1714 l_query_id,
1715 p_query_name ,
1716 p_query_type,
1717 p_query_desc,
1718 p_query_data_source ,
1719
1720 p_query_param_tbl,
1721
1722 p_query_order_by_tbl,
1723 p_query_raw_sql_rec ,
1724 x_query_id ,
1725 x_return_status ,
1726 x_msg_count,
1727 x_msg_data
1728 );
1729
1730 --dbms_output.put_line('return status from update is'||x_return_status);
1731 ELSE
1732
1733 -- Call create_perz_query
1734 Create_Perz_Query
1735 ( p_api_version_number,
1736 p_init_msg_list,
1737 l_commit,
1738
1739 p_application_id,
1740
1741 l_profile_id ,
1742 p_profile_name ,
1743
1744 p_query_id,
1745 p_query_name ,
1746 p_query_type,
1747 p_query_desc,
1748 p_query_data_source ,
1749
1750 p_query_param_tbl,
1751
1752 p_query_order_by_tbl,
1753 p_query_raw_sql_rec ,
1754 x_query_id ,
1755 x_return_status ,
1756 x_msg_count,
1757 x_msg_data
1758 );
1759 --dbms_output.put_line('query id is'||x_query_id);
1760 END IF;
1761 -- ******** Standard Ends ***********
1762 --
1763 -- End of main API body.
1764
1765 -- Standard check of p_commit.
1766 IF (Fnd_Api.To_Boolean(p_commit)) THEN
1767 COMMIT WORK;
1768 END IF;
1769
1770 -- Standard call to get message count and if count is 1, get message info.
1771 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
1772 p_data => x_msg_data );
1773
1774 EXCEPTION
1775
1776 WHEN Fnd_Api.G_EXC_ERROR THEN
1777 -- dbms_output.put_line('stop 1 ');
1778
1779 ROLLBACK TO SAVE_PERZ_QUERY_PVT;
1780 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
1781
1782 Fnd_Msg_Pub.Count_And_Get
1783 ( p_count => x_msg_count,
1784 p_data => x_msg_data );
1785
1786 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1787 -- dbms_output.put_line('stop 2 ');
1788 ROLLBACK TO SAVE_PERZ_QUERY_PVT;
1789 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1790 Fnd_Msg_Pub.Count_And_Get
1791 ( p_count => x_msg_count,
1792 p_data => x_msg_data );
1793
1794
1795 WHEN OTHERS THEN
1796 -- dbms_output.put_line('stop 3 ');
1797 ROLLBACK TO SAVE_PERZ_QUERY_PVT;
1798 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1799
1800 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1801 THEN
1802 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1803 END IF;
1804
1805 Fnd_Msg_Pub.Count_And_Get
1806 ( p_count => x_msg_count,
1807 p_data => x_msg_data );
1808
1809
1810 END Save_Perz_Query;
1811 --******************************************************************************
1812 --******************************************************************************
1813
1814 END Jtf_Perz_Query_Pvt;