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.

APPS.JTF_PERZ_QUERY_PVT dependencies on JTF_PERZ_QUERY

Line 1: PACKAGE BODY Jtf_Perz_Query_Pvt AS

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: --

Line 9: -- Jtf_Perz_Query_Pvt

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: --

Line 25: G_PKG_NAME CONSTANT VARCHAR2(30):='Jtf_Perz_Query_Pvt';

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:

Line 34: -- 1. insert_jtf_perz_query

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

Line 35: -- 2. insert_jtf_perz_query_order_by

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:

Line 36: -- 3. insert_jtf_perz_query_param

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: -- *****************************************************************************

Line 37: -- 4. insert_jtf_perz_query_raw_sql

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: -- *****************************************************************************

Line 38: -- 5. update_jtf_perz_query

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:

Line 44: PROCEDURE update_jtf_perz_query(

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,

Line 56: UPDATE JTF_PERZ_QUERY

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 =

Line 79: END update_jtf_perz_query;

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:

Line 84: PROCEDURE insert_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,

Line 94: CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY

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:

Line 97: CURSOR C2 IS SELECT JTF_PERZ_QUERY_S.NEXTVAL FROM sys.dual;

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

Line 106: INSERT INTO JTF_PERZ_QUERY(

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,

Line 135: END insert_jtf_perz_query;

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

Line 141: PROCEDURE insert_jtf_perz_query_order_by(

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,

Line 149: CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_ORDER_BY

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:

Line 152: CURSOR C2 IS SELECT JTF_PERZ_QUERY_ORDER_BY_s.NEXTVAL FROM sys.dual;

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;

Line 160: INSERT INTO JTF_PERZ_QUERY_ORDER_BY(

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,

Line 184: END insert_jtf_perz_query_order_by;

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

Line 190: PROCEDURE insert_jtf_perz_query_raw_sql(

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,

Line 201: CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_RAW_SQL

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

Line 203: CURSOR C2 IS SELECT JTF_PERZ_QUERY_RAW_SQL_s.NEXTVAL FROM sys.dual;

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

Line 212: INSERT INTO JTF_PERZ_QUERY_RAW_SQL(

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,

Line 242: END insert_jtf_perz_query_raw_sql;

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,

Line 245: PROCEDURE insert_jtf_perz_query_param(

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,

Line 255: CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_PARAM

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

Line 257: CURSOR C2 IS SELECT JTF_PERZ_QUERY_PARAM_s.NEXTVAL FROM sys.dual;

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;

Line 265: INSERT INTO JTF_PERZ_QUERY_PARAM(

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,

Line 293: END insert_jtf_perz_query_param;

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: --

Line 335: FROM JTF_PERZ_QUERY

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;

Line 355: -- FROM JTF_PERZ_QUERY

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: --

Line 371: -- FROM JTF_PERZ_QUERY

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;

Line 386: -- FROM JTF_PERZ_QUERY

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;

Line 426: p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE

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,

Line 427: := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,

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:

Line 428: p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE

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,

Line 429: := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,

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,

Line 430: p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,

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,

Line 438: l_query_param_tbl Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE

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;

Line 517: insert_jtf_perz_query(

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,

Line 539: insert_jtf_perz_query_order_by(

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,

Line 561: insert_jtf_perz_query_param(

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,

Line 581: insert_jtf_perz_query_raw_sql(

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,

Line 660: p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE

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 ,

Line 661: := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,

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:

Line 662: p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE

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,

Line 663: := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,

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,

Line 664: p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE ,

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,

Line 672: l_query_param_tbl Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE

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);

Line 753: DELETE FROM JTF_PERZ_QUERY_PARAM WHERE QUERY_ID = l_query_id;

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:

Line 756: DELETE FROM JTF_PERZ_QUERY_ORDER_BY WHERE QUERY_ID = l_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:

Line 759: DELETE FROM JTF_PERZ_QUERY_RAW_SQL WHERE QUERY_ID = l_query_id;

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(

Line 763: update_jtf_perz_query(

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,

Line 781: insert_jtf_perz_query_order_by(l_rowid,

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,

Line 796: insert_jtf_perz_query_param(

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,

Line 811: insert_jtf_perz_query_raw_sql(

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,

Line 929: DELETE FROM JTF_PERZ_QUERY_PARAM WHERE QUERY_ID = l_query_id;

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:

Line 932: DELETE FROM JTF_PERZ_QUERY_ORDER_BY WHERE QUERY_ID = l_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:

Line 935: DELETE FROM JTF_PERZ_QUERY_RAW_SQL WHERE QUERY_ID = l_query_id;

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:

Line 938: DELETE FROM JTF_PERZ_QUERY WHERE QUERY_ID = l_query_id;

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

Line 989: x_query_out_tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_QUERY_PUB.QUERY_OUT_TBL_TYPE,

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

Line 1036: FROM JTF_PERZ_QUERY

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,

Line 1042: FROM JTF_PERZ_QUERY

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

Line 1049: FROM JTF_PERZ_QUERY

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;

Line 1058: FROM JTF_PERZ_QUERY

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,

Line 1064: FROM JTF_PERZ_QUERY

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 ********

Line 1316: x_query_param_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE,

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,

Line 1317: x_query_order_by_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE,

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,

Line 1318: x_query_raw_sql_rec OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,

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

Line 1329: l_query_out_tbl Jtf_Perz_Query_Pub.QUERY_OUT_TBL_TYPE;

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

Line 1356: FROM JTF_PERZ_QUERY_PARAM

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

Line 1363: FROM JTF_PERZ_QUERY_ORDER_BY

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 ********

Line 1519: FROM JTF_PERZ_QUERY_RAW_SQL

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

Line 1586: p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE

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 ,

Line 1587: := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,

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,

Line 1588: p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE

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,

Line 1589: := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,

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,

Line 1590: p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE ,

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

Line 1814: END Jtf_Perz_Query_Pvt;

1810: END Save_Perz_Query;
1811: --******************************************************************************
1812: --******************************************************************************
1813:
1814: END Jtf_Perz_Query_Pvt;