1 PACKAGE Jtf_Perz_Query_Pub AUTHID CURRENT_USER AS
2 /* $Header: jtfzppqs.pls 120.2 2005/11/02 04:48:16 skothe ship $ */
3 -- Start of Comments
4 -- NAME
5 -- Jtf_Perz_Query_Pub
6 --
7 -- PURPOSE
8 -- Public API for saving, retrieving and updating personalized queries.
9 -- NOTES
10 -- This is a pulicly accessible pacakge. It should be used by all
11 -- sources for saving, retrieving and updating personalized queries
12 -- within the personalization framework.
13 -- HISTORY
14 -- 04/18/2000 SMATTEGU Created
15 --
16 ---- End of Comments
17 -- *****************************************************************************
18
19 -- Start of Comments
20 --
21 -- QUERY_PARAMETER_REC_TYPE Rec
22 --
23 -- This record is used to set the field/parameter map associated with a query.
24 -- If one is setting a query parameter record, the columns PARAMETER_NAME,
25 -- PARAMETER_TYPE and PARAMETER_VALUE are required.
26 --
27 -- End of Comments
28
29 TYPE QUERY_PARAMETER_REC_TYPE IS RECORD
30 (
31 QUERY_PARAM_ID NUMBER := Fnd_Api.G_MISS_NUM,
32 QUERY_ID NUMBER := Fnd_Api.G_MISS_NUM,
33 PARAMETER_NAME VARCHAR2(60) := Fnd_Api.G_MISS_CHAR,
34 PARAMETER_TYPE VARCHAR2(30) := Fnd_Api.G_MISS_CHAR,
35 PARAMETER_VALUE VARCHAR2(300) := Fnd_Api.G_MISS_CHAR,
36 PARAMETER_CONDITION VARCHAR2(10) := Fnd_Api.G_MISS_CHAR,
37 PARAMETER_SEQUENCE NUMBER := Fnd_Api.G_MISS_NUM
38 );
39
40 -- Start of Comments
41 -- QUERY_PARAMETER_TBL Table: QUERY_PARAMETER_TBL_TYPE
42 -- End of Comments
43
44 TYPE QUERY_PARAMETER_TBL_TYPE IS TABLE OF QUERY_PARAMETER_REC_TYPE
45 INDEX BY BINARY_INTEGER;
46
47 -- G_MISS definition for table
48 G_MISS_QUERY_PARAMETER_TBL QUERY_PARAMETER_TBL_TYPE;
49
50 -- *****************************************************************************
51 -- Start of Comments
52 -- QUERY_OUT_REC_TYPE
53 -- This record defines the out record for a get performed on the database.
54 -- The table of results returned from a Get_Perz_Query(..) will be a table of this
55 -- record type.
56 --
57 -- End of Comments
58 TYPE QUERY_OUT_REC_TYPE IS RECORD
59 (
60 QUERY_ID NUMBER := NULL,
61 PROFILE_ID NUMBER := NULL,
62 APPLICATION_ID NUMBER := NULL,
63 QUERY_NAME VARCHAR2(100) := NULL,
64 QUERY_TYPE VARCHAR2(100) := NULL,
65 QUERY_DESCRIPTION VARCHAR2(240) := NULL,
66 QUERY_DATA_SOURCE VARCHAR2(2000) := NULL
67 );
68
69 -- Start of Comments
70 -- QUERY_OUT_TBL Table: QUERY_OUT_REC_TYPE
71 -- End of Comments
72
73 TYPE QUERY_OUT_TBL_TYPE IS TABLE OF QUERY_OUT_REC_TYPE
74 INDEX BY BINARY_INTEGER;
75 -- *****************************************************************************
76 -- Start of Comments
77 -- QUERY_ORDER_BY_REC_TYPE
78 -- This record defines the order-by record for a get performed on the database.
79 -- The record stored tha parameter the query will be ordered by, ascending or
80 -- descending and the sequence if there are a number of order by parameters.
81 -- End of Comments
82
83
84 TYPE QUERY_ORDER_BY_REC_TYPE IS RECORD
85 ( QUERY_ORDER_BY_ID NUMBER := NULL,
86 QUERY_ID NUMBER := NULL,
87 PARAMETER_NAME VARCHAR2(60) := NULL,
88 ACND_DCND_FLAG VARCHAR2(1) := NULL,
89 PARAMETER_SEQUENCE NUMBER := NULL
90 );
91
92 -- Start of Comments
93 -- QUERY_ORDER_BY_TBL Table: QUERY_ORDER_BY_REC_TYPE
94 -- End of Comments
95 TYPE QUERY_ORDER_BY_TBL_TYPE IS TABLE OF QUERY_ORDER_BY_REC_TYPE
96 INDEX BY BINARY_INTEGER;
97
98 -- G_MISS definition for table
99
100 G_MISS_QUERY_ORDER_BY_TBL QUERY_ORDER_BY_TBL_TYPE;
101
102 -- *****************************************************************************
103 -- Start of Comments
104 --
105 -- QUERY_RAW_SQL_REC_TYPE
106 --
107 -- This record defines the record for storing a raw SQL query.
108 --
109 -- End of Comments
110
111
112 TYPE QUERY_RAW_SQL_REC_TYPE IS RECORD
113 (
114 QUERY_RAW_SQL_ID NUMBER := NULL,
115 QUERY_ID NUMBER := NULL,
116 SELECT_STRING VARCHAR2(200) := NULL,
117 FROM_STRING VARCHAR2(200) := NULL,
118 WHERE_STRING VARCHAR2(200) := NULL,
119 ORDER_BY_STRING VARCHAR2(200) := NULL,
120 GROUP_BY_STRING VARCHAR2(200) := NULL,
121 HAVING_STRING VARCHAR2(200) := NULL
122 );
123
124 G_MISS_QUERY_RAW_SQL_REC QUERY_RAW_SQL_REC_TYPE;
125 -- *****************************************************************************-- *****************************************************************************-- START API SPECIFICATIONS
126 --
127 -- Start of Comments
128 --
129 -- API name : Save_Perz_Query
130 -- Type : Public
131 -- Function : Create or update if exists, a personalized query and associated
132 -- field map with values.
133 -- Paramaeters :
134 -- IN :
135 -- p_api_version_number IN NUMBER Required
136 -- p_init_msg_list IN VARCHAR2 Optional
137 -- p_commit IN VARCHAR2 Optional
138 -- p_application_id IN NUMBER Required
139 -- p_profile_id IN NUMBER Optional
140 -- p_profile_name IN VARCHAR2 Optional
141 -- p_profile_type IN VARCHAR2 Optional
142 -- p_profile_attrib IN PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE Optional
143 --
144 -- p_query_id IN NUMBER Optional
145 -- p_query_name IN VARCHAR2 Required
146 -- p_query_type IN VARCHAR2 Required
147 -- p_query_desc IN VARCHAR2 Optional
148 -- p_query_data_source IN VARCHAR2,
149 -- p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
150 -- p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
151 -- p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
152 --
153 -- OUT :
154 -- x_query_id OUT NUMBER
155 -- x_return_status OUT VARCHAR2
156 -- x_msg_count OUT NUMBER
157 -- x_msg_data OUT VARCHAR2
158 -- Version :Current version 1.0
159 -- Initial version 1.0
160 --
161 -- Notes:
162 --
163 --
164 -- *****************************************************************************
165 -- USAGE NOTES :
166 -- 1. This API creates or updates query in the personalization framework.
167 -- Of the input parameters p_profile_id (or the name of the profile p_profile_name
168 -- and its attributes p_Profile_Attrib) is a required field. The other required
169 -- fields are p_application_id (the application id of the caller) and p_query_name
170 -- which is This field has to be unique for that profile id and application id, or
171 -- the API will return an error. This field also has to be made of characters
172 -- with no spaces (underscores allowed).
173 -- 2. The p_query_desc is the description (free text) of query being saved.
174 -- 3. p_query_data_cource defines the name of the type of execution mechanism
175 -- to use for that query (like VIEW, PACKAGE, WEB etc.)
176 -- 4.The p_query_param_tbl is the table that holds the parameters and values
177 -- associated with a query. The PARAMETER_NAME field holds the name/tag
178 -- associated with a query. The PARAMETER_TYPE field holds the value for this
179 -- query parameter. The PARAMETER_TYPE is used to store the type of parameter_value
180 -- value being stored (for and type conversion purposes). For example, one
181 -- could have a record like : [CUSTOMER_ID, 20, NUMBER]. this essentially
182 -- says that the query has a parameter called CUSTOMER_ID with value 235.
183
184 -- 5. The p_query_order_by_tbl will hold the order by sequence, parameter
185 -- order by direction (ACND_DCND_FLAG) details for a given query.
186 -- 6. If the query to store is a select statement, then p_query_raw_sql_rec
187 -- will hold all the necessary details of the select statement.
188 -- 7. The main out parameter for this API is x_return_status which returns
189 -- FND_API.G_RETURN_SUCCESS when the API completes successfully
190 -- FND_API.G_RETURN_UNEXPECTED when the API reaches a unxpected state
191 -- FND_API.G_RETURN_ERROR when the API hits an error
192
193 -- *****************************************************************************
194
195 PROCEDURE Save_Perz_Query
196 ( p_api_version_number IN NUMBER,
197 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
198 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
199
200 p_application_id IN NUMBER,
201 p_profile_id IN NUMBER,
202 p_profile_name IN VARCHAR2,
203 p_profile_type IN VARCHAR2,
204 p_Profile_Attrib IN Jtf_Perz_Profile_Pub.PROFILE_ATTRIB_TBL_TYPE
205 := Jtf_Perz_Profile_Pub.G_MISS_PROFILE_ATTRIB_TBL,
206
207 p_query_id IN NUMBER,
208 p_query_name IN VARCHAR2,
209 p_query_type IN VARCHAR2,
210 p_query_desc IN VARCHAR2,
211 p_query_data_source IN VARCHAR2,
212
213 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
214 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
215 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
216 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
217 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
218 := Jtf_Perz_Query_Pub.G_MISS_QUERY_RAW_SQL_REC ,
219
220 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
221 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
222 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
223 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
224
225 );
226
227 -- *****************************************************************************
228 -- Start of Comments
229 --
230 -- API name : Create_Perz_Query
231 -- Type : Public
232 -- Function : Create Query and associated field map with values
233 --
234 -- Paramaeters :
235 -- IN :
236 -- p_api_version_number IN NUMBER Required
237 -- p_init_msg_list IN VARCHAR2 Optional
238 -- p_commit IN VARCHAR2 Optional
239 --
240 -- p_application_id IN NUMBER Required
241 -- p_profile_id IN NUMBER Optional
242 -- p_profile_name IN VARCHAR2 Optional
243 --
244 -- p_query_id IN NUMBER Optional
245 -- p_query_name IN VARCHAR2 Required
246 -- p_query_type IN VARCHAR2 Optional
247 -- p_query_desc IN VARCHAR2 Optional
248 -- p_query_data_source IN VARCHAR2 Optional
249 --
250 -- p_query_param_tbl IN JTF_PERZ_QUERY_PUB.QUERY_PARAMETER_TBL_TYPE
251 -- := JTF_PERZ_QUERY_PUB.G_MISS_QUERY_PARAMETER_TBL,
252 -- p_query_order_by_tbl IN JTF_PERZ_QUERY_PUB.QUERY_ORDER_BY_TBL_TYPE
253 -- := JTF_PERZ_QUERY_PUB.G_MISS_QUERY_ORDER_BY_TBL,
254 -- p_query_raw_sql_rec IN JTF_PERZ_QUERY_PUB.QUERY_RAW_SQL_REC_TYPE
255 --
256
257 -- OUT :
258 -- x_query_id OUT NUMBER
259 -- x_return_status OUT VARCHAR2
260 -- x_msg_count OUT NUMBER
261 -- x_msg_data OUT VARCHAR2
262 --
263 --
264 -- Version : Current version 1.0
265 -- Initial version 1.0
266 --
267 -- Notes:
268
269
270 -- *****************************************************************************
271 --
272 -- USAGE NOTES :
273 --
274 -- 1. This API creates a query in the personalization framework.
275 -- Of the input parameters p_profile_id (or the name of the profile p_profile_name)
276 -- is a required field. The other required fields are p_application_id (the
277 -- application id of the caller) and p_query_name which is This field has to be
278 -- unique for that profile id and application id, or the API will return an error.
279 -- This field also has to be made of characters with no spaces (underscores allowed).
280 --
281 -- 2. The p_query_desc is the description (free text) of query being saved.
282 --
283 -- 3. p_query_data_cource defines the name of the type of execution mechanism
284 -- to use for that query (like VIEW, PACKAGE, WEB etc.)
285 --
286 -- 4.The p_query_param_tbl is the table that holds the parameters and values
287 -- associated with a query. The PARAMETER_NAME field holds the name/tag
288 -- associated with a query. The PARAMETER_TYPE field holds the value for this
289 -- query parameter. The PARAMETER_TYPE is used to store the type of parameter_value
290 -- value being stored (for and type conversion purposes). For example, one
291 -- could have a record like : [CUSTOMER_ID, 20, NUMBER]. this essentially
292 -- says that the query has a parameter called CUSTOMER_ID with value 235.
293 --
294 -- 5. The p_query_order_by_tbl will hold the order by sequence, parameter
295 -- order by direction (ACND_DCND_FLAG) details for a given query.
296 --
297 -- 6. If the query to store is a select statement, then p_query_raw_sql_rec
298 -- will hold all the necessary details of the select statement.
299 --
300 -- 7. The API returns x_query_id the ID of the query that has been stored in the
301 -- framework.
302 --
303 -- 8. The other out parameter for this API is x_return_status which returns
304 -- FND_API.G_RETURN_SUCCESS when the API completes successfully
305 -- FND_API.G_RETURN_UNEXPECTED when the API reaches a unxpected state
306 -- FND_API.G_RETURN_ERROR when the API hits an error
307 --
308 -- *****************************************************************************
309
310 PROCEDURE Create_Perz_Query
311 ( p_api_version_number IN NUMBER,
312 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
313 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
314
315 p_application_id IN NUMBER,
316 p_profile_id IN NUMBER,
317 p_profile_name IN VARCHAR2,
318
319 p_query_id IN NUMBER,
320 p_query_name IN VARCHAR2,
321 p_query_type IN VARCHAR2,
322 p_query_desc IN VARCHAR2,
323 p_query_data_source IN VARCHAR2,
324
325 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
326 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
327 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
328 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
329 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
330 := Jtf_Perz_Query_Pub.G_MISS_QUERY_RAW_SQL_REC ,
331
332 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
333 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
334 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
335 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
336 );
337 -- *****************************************************************************
338
339 -- Start of Comments
340 --
341 -- API name : Get_Perz_Query
342 -- Type : Public
343 -- Function : Get personalized query from query store
344 --
345 -- Paramaeters :
346 -- IN :
347 -- p_api_version_number IN NUMBER Required
348 -- p_init_msg_list IN VARCHAR2 Optional
349 --
350 -- p_application_id IN NUMBER Required
351 -- p_profile_id IN NUMBER Optional
352 -- p_profile_name IN VARCHAR2 Optional
353 --
354 -- p_query_id IN NUMBER Optional
355 -- p_query_name IN VARCHAR2 Optional
356 -- p_query_type IN VARCHAR2 Optional
357
358 -- OUT :
359 --
360 -- x_query_id OUT NUMBER,
361 -- x_query_name OUT VARCHAR2,
362 -- x_query_type OUT VARCHAR2,
363 -- x_query_desc OUT VARCHAR2,
364
365 -- x_query_param_tbl OUT Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE,
366 -- x_query_order_by_tbl OUT Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE,
367 -- x_query_raw_sql_rec OUT Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,
368
369 -- x_return_status OUT VARCHAR2
370 -- x_msg_count OUT NUMBER
371 -- x_msg_data OUT VARCHAR2
372 --
373 -- Version :Current version 1.0
374 -- Initial version 1.0
375 --
376 -- Notes: Sending in IDs will greatly improve performance.
377 --
378 -- *****************************************************************************
379 --
380 -- USAGE NOTES :
381 --
382 -- 1. This API gets/queries a personalized query from the personalization framework.
383 -- Of the input parameters p_profile_id (or the name of the profile p_profile_name)
384 -- the application id (p_application_id) and the name of the query p_query_name are
385 -- required fields. Sending in the queryid (p_query_id) will improve performance.
386 --
387 -- 2. The x_query_param_tbl holds the output set from the query parameters.
388 --
389 -- 3. The x_query_order_by_tbl holds the output set from Order by details for
390 -- the query
391 --
392 -- 4. The x_query_raw_sql_rec will hold the output set from select query details
393 -- for the query
394 --
395 -- 5. query_id, description and query_data_source are also returned.
396 --
397 -- 6. The other out parameters for this API is x_return_status which returns
398 -- FND_API.G_RETURN_SUCCESS when the API completes successfully
399 -- FND_API.G_RETURN_UNEXPECTED when the API reaches a unxpected state
400 -- FND_API.G_RETURN_ERROR when the API hits an error
401 --
402 -- *****************************************************************************
403 PROCEDURE Get_Perz_Query
404 ( p_api_version_number IN NUMBER,
405 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
406
407 p_application_id IN NUMBER,
408 p_profile_id IN NUMBER,
409 p_profile_name IN VARCHAR2,
410
411 p_query_id IN NUMBER,
412 p_query_name IN VARCHAR2,
413 p_query_type IN VARCHAR2,
414
415 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
416 x_query_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
417 x_query_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
418 x_query_desc OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
419 x_query_data_source OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
420
421 x_query_param_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE,
422 x_query_order_by_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE,
423 x_query_raw_sql_rec OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,
424
425 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
426 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
427 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
428 );
429 -- *****************************************************************************
430 -- API name : Get_Perz_Query_Summary
431 -- Type : Public
432 -- Function : Get Only query header(s) from query store
433 -- Paramaeters :
434 -- IN :
435 -- p_api_version_number IN NUMBER Required
436 -- p_init_msg_list IN VARCHAR2 Optional
437 --
438 -- p_application_id IN NUMBER Required
439 -- p_profile_id IN NUMBER Optional
440 -- p_profile_name IN VARCHAR2 Optional
441
442 -- p_query_id IN NUMBER Optional
443 -- p_query_name IN VARCHAR2 Optional
444 -- p_query_type IN VARCHAR2 Optional
445 -- OUT :
446 -- x_query_out_tbl OUT Jtf_Perz_Query_Pub.QUERY_OUT_TBL_TYPE
447 -- x_return_status OUT VARCHAR2
448 -- x_msg_count OUT NUMBER
449 -- x_msg_data OUT VARCHAR2
450
451 -- Version : Current version 1.0
452 -- Initial version 1.0
453
454 -- Notes: Sending in IDs will greatly improve performance.
455 -- *****************************************************************************
456 --
457 -- USAGE NOTES :
458 --
459 -- 1. This API gets/queries query headers/summaries from the personalization framework.
460 -- Of the input parameters p_profile_id (or the name of the profile p_profile_name)
461 -- the application id (p_application_id) and the name of the query p_query_name are
462 -- required fields. Sending in the queryid (p_query_id) will improve performance.
463 --
464 -- 2. The x_query_out_tbl holds the output set from the query headers.
465 --
466 -- 3. The other out parameters for this API is x_return_status which returns
467 -- FND_API.G_RETURN_SUCCESS when the API completes successfully
468 -- FND_API.G_RETURN_UNEXPECTED when the API reaches a unxpected state
469 -- FND_API.G_RETURN_ERROR when the API hits an error
470 --
471 -- *****************************************************************************
472 PROCEDURE Get_Perz_Query_Summary
473 ( p_api_version_number IN NUMBER,
474 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
475
476 p_application_id IN NUMBER,
477 p_profile_id IN NUMBER,
478 p_profile_name IN VARCHAR2,
479
480 p_query_id IN NUMBER,
481 p_query_name IN VARCHAR2,
482 p_query_type IN VARCHAR2,
483
484 x_query_out_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_OUT_TBL_TYPE,
485
486 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
487 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
488 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
489 );
490 -- *****************************************************************************-- Start of Comments
491 --
492 -- API name : Update_Perz_Query
493 -- Type : Public
494 -- Function : Updates the personalized query header and associated field-map
495 -- for a given query and profile.
496 --
497 -- Paramaeters :
498 -- IN :
499 -- p_api_version_number IN NUMBER Required
500 -- p_init_msg_list IN VARCHAR2 Optional
501 -- p_commit IN VARCHAR2 Optional
502 --
503 -- p_application_id IN NUMBER Required
504 -- p_profile_id IN NUMBER Required
505 --
506 -- p_query_id IN NUMBER Optional
507 -- p_query_name IN VARCHAR2 Required
508 -- p_query_type IN VARCHAR2 Optional
509 -- p_query_desc IN VARCHAR2 Optional
510 -- p_query_data_source IN VARCHAR2 Optional
511 --
512 -- p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
513 -- := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
514 -- p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
515 -- := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
516 -- p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
517 --
518 -- OUT :
519 -- x_query_id OUT NUMBER
520 -- x_return_status OUT VARCHAR2
521 -- x_msg_count OUT NUMBER
522 -- x_msg_data OUT VARCHAR2
523 --
524 -- Version :Current version 1.0
525 -- Initial version 1.0
526 --
527 -- Notes:
528 -- *****************************************************************************
529 --
530 -- USAGE NOTES :
531 --
532 -- 1. This API updates query in the personalization framework.
533 -- Of the input parameters p_profile_id (or the name of the profile p_profile_name
534 -- and its attributes p_Profile_Attrib) is a required field. The other required
535 -- fields are p_application_id (the application id of the caller) and p_query_name
536 -- which is This field has to be unique for that profile id and application id, or
537 -- the API will return an error. This field also has to be made of characters
538 -- with no spaces (underscores allowed).
539 --
540 -- 2. The p_query_desc is the description (free text) of query being saved.
541 --
542 -- 3. p_query_data_cource defines the name of the type of execution mechanism
543 -- to use for that query (like VIEW, PACKAGE, WEB etc.)
544 --
545 -- 4.The p_query_param_tbl is the table that holds the parameters and values
546 -- associated with a query. The PARAMETER_NAME field holds the name/tag
547 -- associated with a query. The PARAMETER_TYPE field holds the value for this
548 -- query parameter. The PARAMETER_TYPE is used to store the type of parameter_value
549 -- value being stored (for and type conversion purposes). For example, one
550 -- could have a record like : [CUSTOMER_ID, 20, NUMBER]. this essentially
551 -- says that the query has a parameter called CUSTOMER_ID with value 235.
552 --
553 -- 5. The p_query_order_by_tbl will hold the order by sequence, parameter
554 -- order by direction (ACND_DCND_FLAG) details for a given query.
555 --
556 -- 6. If the query to store is a select statement, then p_query_raw_sql_rec
557 -- will hold all the necessary details of the select statement.
558 --
559 -- 7. The main out parameter for this API is x_return_status which returns
560 -- FND_API.G_RETURN_SUCCESS when the API completes successfully
561 -- FND_API.G_RETURN_UNEXPECTED when the API reaches a unxpected state
562 -- FND_API.G_RETURN_ERROR when the API hits an error
563 --
564 -- *****************************************************************************
565 PROCEDURE Update_Perz_Query
566 ( p_api_version_number IN NUMBER,
567 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
568 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
569
570 p_application_id IN NUMBER,
571 p_profile_id IN NUMBER,
572
573 p_query_id IN NUMBER,
574 p_query_name IN VARCHAR2,
575 p_query_type IN VARCHAR2,
576 p_query_desc IN VARCHAR2,
577 p_query_data_source IN VARCHAR2,
578
579 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
580 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
581 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
582 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
583 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
584 := Jtf_Perz_Query_Pub.G_MISS_QUERY_RAW_SQL_REC ,
585
586 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
587 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
588 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
589 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
590 );
591 -- *****************************************************************************-- Start of Comments
592 --
593 -- API name : Delete_Perz_Query
594 -- Type : Public
595 -- Function : Deletes a personalized query in the personalization framework.
596 --
597 -- Paramaeters :
598 -- IN :
599 -- p_api_version_number IN NUMBER Required
600 -- p_init_msg_list IN VARCHAR2 Optional
601 -- p_commit IN VARCHAR2 Optional
602 --
603 -- p_application_id IN NUMBER Required
604 -- p_profile_id IN NUMBER Required
605 -- p_query_id IN NUMBER Required
606 --
607 -- OUT :
608 -- x_return_status OUT VARCHAR2
609 -- x_msg_count OUT NUMBER
610 -- x_msg_data OUT VARCHAR2
611 --
612 -- Version :Current version 1.0
613 -- Initial version 1.0
614 --
615 -- Notes:
616 -- *****************************************************************************
617 --
618 -- USAGE NOTES :
619 --
620 -- 1. This API deletes a personalized query from the personalization framework.
621 -- Of the input parameters p_profile_id (or the name of the profile p_profile_name)
622 -- the application id (p_application_id) and the query id p_query_id are
623 -- required fields.
624 --
625 -- 2. The out parameter for this API is x_return_status which returns
626 -- FND_API.G_RETURN_SUCCESS when the API completes successfully
627 -- FND_API.G_RETURN_UNEXPECTED when the API reaches a unxpected state
628 -- FND_API.G_RETURN_ERROR when the API hits an error
629 --
630 -- *****************************************************************************
631 PROCEDURE Delete_Perz_Query
632 ( p_api_version_number IN NUMBER,
633 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
634 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
635
636 p_application_id IN NUMBER,
637 p_profile_id IN NUMBER,
638 p_query_id IN NUMBER,
639
640 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
641 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
642 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
643 );
644
645 -- *****************************************************************************
646 -- *****************************************************************************
647 END Jtf_Perz_Query_Pub;
[Home] [Help]