[Home] [Help]
PACKAGE: APPS.JTF_PERZ_QUERY_PVT
Source
1 PACKAGE JTF_PERZ_QUERY_PVT AS
2 /* $Header: jtfzvpqs.pls 120.2 2005/11/02 22:46:59 skothe ship $ */
3 --
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- JTF_PERZ_QUERY_PVT
10 --
11 -- PURPOSE
12 -- Public API for
13 --
14 -- NOTES
15 -- This is a pulicly accessible pacakge. It should be used by all
16 -- sources for
17 --
18 -- HISTORY
19 -- 04/18/2000 SMATTEGU Created
20 --
21 -- End of Comments
22 --
23 --
24
25 -- ****************************************************************************
26 --******************************************************************************
27 --
28 -- APIS
29 --
30 --1. Create_Perz_Query
31 --2. Get_Perz_Query
32 --3. Get_Perz_Query_Summary
33 --4. Update_Perz_Query
34 --5. Delete_Perz_Query
35 --7. save_perz_query
36 -- ****************************************************************************
37 --******************************************************************************
38
39 -- Start of Comments
40 --
41 -- API name : Create_Perz_Query
42 -- Type : Public
43 -- Function : Create Query and associated field map with values
44 --
45 -- Paramaeters :
46 -- IN :
47 -- p_api_version_number IN NUMBER Required
48 -- p_init_msg_list IN VARCHAR2 Optional
49 -- p_commit IN VARCHAR2 Optional
50 --
51 -- p_application_id IN NUMBER Required
52 -- p_profile_id IN NUMBER Optional
53 -- p_profile_name IN VARCHAR2 Optional
54 --
55 -- p_query_id IN NUMBER Optional
56 -- p_query_name IN VARCHAR2 Required
57 -- p_query_type IN VARCHAR2 Optional
58 -- p_query_desc IN VARCHAR2 Optional
59 -- p_query_data_source IN VARCHAR2 Optional
60 --
61 -- p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
62 -- := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
63 -- p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
64 -- := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
65 -- p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
66 --
67
68 -- OUT :
69 -- x_query_id OUT NUMBER
70 -- x_return_status OUT VARCHAR2
71 -- x_msg_count OUT NUMBER
72 -- x_msg_data OUT VARCHAR2
73 --
74 --
75 -- Version : Current version 1.0
76 -- Initial version 1.0
77 --
78 -- Notes:
79
80 --******************************************************************************
81 PROCEDURE Create_Perz_Query
82 ( p_api_version_number IN NUMBER,
83 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
84 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
85
86 p_application_id IN NUMBER,
87 p_profile_id IN NUMBER,
88 p_profile_name IN VARCHAR2,
89
90 p_query_id IN NUMBER,
91 p_query_name IN VARCHAR2,
92 p_query_type IN VARCHAR2,
93 p_query_desc IN VARCHAR2,
94 p_query_data_source IN VARCHAR2,
95
96 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
97 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
98 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
99 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
100 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,
101
102 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
103 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
104 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
105 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
106 );
107
108
109 -- ****************************************************************************
110 --******************************************************************************
111
112
113 -- Start of Comments
114 --
115 -- API name : Get_Perz_Query
116 -- Type : Public
117 -- Function : Get personalized query from query store
118 --
119 -- Paramaeters :
120 -- IN :
121 -- p_api_version_number IN NUMBER Required
122 -- p_init_msg_list IN VARCHAR2 Optional
123 --
124 -- p_application_id IN NUMBER Required
125 -- p_profile_id IN NUMBER Optional
126 -- p_profile_name IN VARCHAR2 Optional
127 --
128 -- p_query_id IN NUMBER Optional
129 -- p_query_name IN VARCHAR2(100) Optional
130 -- p_query_type IN VARCHAR2 Optional
131
132 -- OUT :
133 --
134 -- x_query_id OUT NUMBER,
135 -- x_query_name OUT VARCHAR2(100),
136 -- x_query_type OUT VARCHAR2,
137 -- x_query_desc OUT VARCHAR2,
138
139 -- x_query_param_tbl OUT Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE,
140 -- x_query_order_by_tbl OUT Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE,
141 -- x_query_raw_sql_rec OUT Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,
142
143 -- x_return_status OUT VARCHAR2
144 -- x_msg_count OUT NUMBER
145 -- x_msg_data OUT VARCHAR2
146 --
147 -- Version :Current version 1.0
148 -- Initial version 1.0
149 --
150 -- Notes: Sending in IDs will greatly improve performance.
151 --
152 --******************************************************************************
153
154
155
156 PROCEDURE Get_Perz_Query
157 ( p_api_version_number IN NUMBER,
158 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
159
160 p_application_id IN NUMBER,
161 p_profile_id IN NUMBER,
162 p_profile_name IN VARCHAR2,
163
164 p_query_id IN NUMBER,
165 p_query_name IN VARCHAR2,
166 p_query_type IN VARCHAR2,
167
168 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
169 x_query_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
170 x_query_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
171 x_query_desc OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
172 x_query_data_source OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
173
174 x_query_param_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE,
175 x_query_order_by_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE,
176 x_query_raw_sql_rec OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,
177
178 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
179 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
180 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
181 );
182
183 -- ****************************************************************************
184 --******************************************************************************
185
186
187 PROCEDURE Get_Perz_Query_Summary
188 ( p_api_version_number IN NUMBER,
189 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
190
191 p_application_id IN NUMBER,
192 p_profile_id IN NUMBER,
193 p_profile_name IN VARCHAR2,
194
195 p_query_id IN NUMBER,
196 p_query_name IN VARCHAR2,
197 p_query_type IN VARCHAR2,
198
199 x_query_out_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_OUT_TBL_TYPE,
200
201 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
202 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
203 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
204 );
205
206 -- ****************************************************************************
207 --******************************************************************************
208
209 -- Start of Comments
210 --
211 -- API name : Update_Perz_Query
212 -- Type : Public
213 -- Function : Updates the personalized query header and associated field-map
214 -- for a given query and profile.
215 --
216 -- Paramaeters :
217 -- IN :
218 -- p_api_version_number IN NUMBER Required
219 -- p_init_msg_list IN VARCHAR2 Optional
220 -- p_commit IN VARCHAR2 Optional
221 --
222 -- p_application_id IN NUMBER Required
223 -- p_profile_id IN NUMBER Required
224 --
225 -- p_query_id IN NUMBER Optional
226 -- p_query_name IN VARCHAR2 Required
227 -- p_query_type IN VARCHAR2 Optional
228 -- p_query_desc IN VARCHAR2 Optional
229 -- p_query_data_source IN VARCHAR2 Optional
230 --
231 -- p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
232 -- := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
233 -- p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
234 -- := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
235 -- p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
236 --
237 -- OUT :
238 -- x_query_id OUT NUMBER
239 -- x_return_status OUT VARCHAR2
240 -- x_msg_count OUT NUMBER
241 -- x_msg_data OUT VARCHAR2
242 --
243 -- Version :Current version 1.0
244 -- Initial version 1.0
245 --
246 -- Notes:
247 --
248 --
249 --******************************************************************************
250
251
252 PROCEDURE Update_Perz_Query
253 ( p_api_version_number IN NUMBER,
254 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
255 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
256
257 p_application_id IN NUMBER,
258 p_profile_id IN NUMBER,
259
260 p_query_id IN NUMBER,
261 p_query_name IN VARCHAR2,
262 p_query_type IN VARCHAR2,
263 p_query_desc IN VARCHAR2,
264 p_query_data_source IN VARCHAR2,
265
266 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
267 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
268 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
269 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
270 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE ,
271
272 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
273 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
274 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
275 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
276 );
277
278 -- ****************************************************************************
279 --******************************************************************************
280
281 -- Start of Comments
282 --
283 -- API name : Delete_Perz_Query
284 -- Type : Public
285 -- Function : Deletes a personalized query in the personalization framework.
286 --
287 -- Paramaeters :
288 -- IN :
289 -- p_api_version_number IN NUMBER Required
290 -- p_init_msg_list IN VARCHAR2 Optional
291 -- p_commit IN VARCHAR2 Optional
292 --
293 -- p_application_id IN NUMBER Required
294 -- p_profile_id IN NUMBER Required
295 -- p_query_id IN NUMBER Required
296 --
297 -- OUT :
298 -- x_return_status OUT VARCHAR2
299 -- x_msg_count OUT NUMBER
300 -- x_msg_data OUT VARCHAR2
301 --
302 -- Version :Current version 1.0
303 -- Initial version 1.0
304 --
305 -- Notes:
306 --
307 --******************************************************************************
308
309
310
311 PROCEDURE Delete_Perz_Query
312 ( p_api_version_number IN NUMBER,
313 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
314 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
315
316 p_application_id IN NUMBER,
317 p_profile_id IN NUMBER,
318 p_query_id IN NUMBER,
319
320 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
321 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
322 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
323 );
324
325 -- ****************************************************************************
326 --******************************************************************************
327
328 PROCEDURE Save_Perz_Query
329 ( p_api_version_number IN NUMBER,
330 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
331 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
332 p_application_id IN NUMBER,
333 p_profile_id IN NUMBER,
334 p_profile_name IN VARCHAR2,
335 p_profile_type IN VARCHAR2,
336 p_Profile_Attrib IN Jtf_Perz_Profile_Pub.PROFILE_ATTRIB_TBL_TYPE
337 := Jtf_Perz_Profile_Pub.G_MISS_PROFILE_ATTRIB_TBL,
338 p_query_id IN NUMBER,
339 p_query_name IN VARCHAR2,
340 p_query_type IN VARCHAR2,
341 p_query_desc IN VARCHAR2,
342 p_query_data_source IN VARCHAR2,
343
344 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
345 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
346 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
347 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
348 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,
349
350 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
351 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
352 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
353 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
354 );
355 -- ****************************************************************************
356 --******************************************************************************
357 END JTF_PERZ_QUERY_PVT;