1 PACKAGE AMS_List_Query_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvliqs.pls 115.10 2004/04/21 18:50:46 sranka ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_List_Query_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16 -- Default number of records fetch per call
17 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
18 --===================================================================
19 -- Start of Comments
20 -- -------------------------------------------------------
21 -- Record name
22 -- list_query_rec_type
23 -- -------------------------------------------------------
24 -- Parameters:
25 -- list_query_id
26 -- last_update_date
27 -- last_updated_by
28 -- creation_date
29 -- created_by
30 -- last_update_login
31 -- object_version_number
32 -- name
33 -- type
34 -- enabled_flag
35 -- primary_key
36 -- source_object_name
37 -- public_flag
38 -- org_id
39 -- comments
40 -- act_list_query_used_by_id
41 -- arc_act_list_query_used_by
42 -- sql_string
43 --
44 -- Required
45 --
46 -- Defaults
47 --
48 -- Note: This is automatic generated record definition, it includes all columns
49 -- defined in the table, developer must manually add or delete some of the attributes.
50 --
51 -- End of Comments
52
53 --===================================================================
54 TYPE list_query_rec_type IS RECORD
55 (
56 list_query_id NUMBER := FND_API.G_MISS_NUM,
57 last_update_date DATE := FND_API.G_MISS_DATE,
58 last_updated_by NUMBER := FND_API.G_MISS_NUM,
59 creation_date DATE := FND_API.G_MISS_DATE,
60 created_by NUMBER := FND_API.G_MISS_NUM,
61 last_update_login NUMBER := FND_API.G_MISS_NUM,
62 object_version_number NUMBER := FND_API.G_MISS_NUM,
63 name VARCHAR2(240) := FND_API.G_MISS_CHAR,
64 type VARCHAR2(30) := FND_API.G_MISS_CHAR,
65 enabled_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
66 primary_key VARCHAR2(60) := FND_API.G_MISS_CHAR,
67 source_object_name VARCHAR2(60) := FND_API.G_MISS_CHAR,
68 seed_flag varchar2(1) := FND_API.G_MISS_CHAR,
69 public_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
70 org_id NUMBER := FND_API.G_MISS_NUM,
71 comments VARCHAR2(900) := FND_API.G_MISS_CHAR,
72 act_list_query_used_by_id NUMBER := FND_API.G_MISS_NUM,
73 arc_act_list_query_used_by VARCHAR2(30) := FND_API.G_MISS_CHAR,
74 sql_string VARCHAR2(4000) := FND_API.G_MISS_CHAR,
75 parent_list_query_id number := FND_API.G_MISS_NUM,
76 sequence_order number := FND_API.G_MISS_NUM
77 );
78
79 TYPE sql_string_tbl IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
80 g_miss_sql_string_tbl sql_string_tbl;
81
82 TYPE list_query_rec_type_tbl IS RECORD
83 (
84 list_query_id NUMBER := FND_API.G_MISS_NUM,
85 last_update_date DATE := FND_API.G_MISS_DATE,
86 last_updated_by NUMBER := FND_API.G_MISS_NUM,
87 creation_date DATE := FND_API.G_MISS_DATE,
88 created_by NUMBER := FND_API.G_MISS_NUM,
89 last_update_login NUMBER := FND_API.G_MISS_NUM,
90 object_version_number NUMBER := FND_API.G_MISS_NUM,
91 name VARCHAR2(240) := FND_API.G_MISS_CHAR,
92 type VARCHAR2(30) := FND_API.G_MISS_CHAR,
93 enabled_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
94 primary_key VARCHAR2(60) := FND_API.G_MISS_CHAR,
95 source_object_name VARCHAR2(60) := FND_API.G_MISS_CHAR,
96 seed_flag varchar2(1) := FND_API.G_MISS_CHAR,
97 public_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
98 org_id NUMBER := FND_API.G_MISS_NUM,
99 comments VARCHAR2(900) := FND_API.G_MISS_CHAR,
100 act_list_query_used_by_id NUMBER := FND_API.G_MISS_NUM,
101 arc_act_list_query_used_by VARCHAR2(30) := FND_API.G_MISS_CHAR,
102 -- t_sql_string_tbl sql_string_tbl := g_miss_sql_string_tbl,
103 parent_list_query_id number := FND_API.G_MISS_NUM,
104 sequence_order number := FND_API.G_MISS_NUM
105 );
106
107 -- TYPE list_query_tbl_type IS TABLE OF list_query_rec_type INDEX BY BINARY_INTEGER;
108
109 g_miss_list_query_rec list_query_rec_type;
110 --TYPE list_query_tbl_type IS TABLE OF list_query_rec_type INDEX BY BINARY_INTEGER;
111 --g_miss_list_query_tbl list_query_tbl_type;
112
113 TYPE list_query_id_Tbl_Type IS TABLE OF number
114 INDEX BY BINARY_INTEGER;
115
116 -- ==============================================================================
117 -- Start of Comments
118 -- ==============================================================================
119 -- API Name
120 -- Create_List_Query
121 -- Type
122 -- Private
123 -- Pre-Req
124 --
125 -- Parameters
126 --
127 -- IN
128 -- p_api_version_number IN NUMBER Required
129 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
130 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
131 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
132 -- p_list_query_rec IN list_query_rec_type Required
133 --
134 -- OUT
135 -- x_return_status OUT VARCHAR2
136 -- x_msg_count OUT NUMBER
137 -- x_msg_data OUT VARCHAR2
138 -- Version : Current version 1.0
139 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
140 -- and basic operation, developer must manually add parameters and business logic as necessary.
141 --
142 -- End of Comments
143 -- ==============================================================================
144 --
145
146 PROCEDURE Create_List_Query(
147 p_api_version_number IN NUMBER,
148 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
149 p_commit IN VARCHAR2 := FND_API.G_FALSE,
150 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
151 x_return_status OUT NOCOPY VARCHAR2,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2,
154 p_list_query_rec IN list_query_rec_type := g_miss_list_query_rec,
155 x_list_query_id OUT NOCOPY NUMBER
156 );
157
158 PROCEDURE Create_List_Query(
159 p_api_version_number IN NUMBER,
160 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
161 p_commit IN VARCHAR2 := FND_API.G_FALSE,
162 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
163 x_return_status OUT NOCOPY VARCHAR2,
164 x_msg_count OUT NOCOPY NUMBER,
165 x_msg_data OUT NOCOPY VARCHAR2,
166 p_list_query_rec_tbl IN list_query_rec_type_tbl ,--:= g_miss_list_query_tbl ,
167 p_sql_string_tbl in sql_string_tbl ,
168 x_parent_list_query_id OUT NOCOPY NUMBER
169 );
170
171 PROCEDURE Create_List_Query(
172 p_api_version_number IN NUMBER,
173 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
174 p_commit IN VARCHAR2 := FND_API.G_FALSE,
175 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_msg_count OUT NOCOPY NUMBER,
178 x_msg_data OUT NOCOPY VARCHAR2,
179 p_list_query_rec_tbl IN list_query_rec_type_tbl ,--:= g_miss_list_query_tbl ,
180 p_sql_string_tbl in sql_string_tbl ,
181 p_query_param in sql_string_tbl ,
182 x_parent_list_query_id OUT NOCOPY NUMBER
183 );
184
185
186 -- ==============================================================================
187 -- Start of Comments
188 -- ==============================================================================
189 -- API Name
190 -- Update_List_Query
191 -- Type
192 -- Private
193 -- Pre-Req
194 --
195 -- Parameters
196 --
197 -- IN
198 -- p_api_version_number IN NUMBER Required
199 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
200 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
201 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
202 -- p_list_query_rec IN list_query_rec_type Required
203 --
204 -- OUT
205 -- x_return_status OUT VARCHAR2
206 -- x_msg_count OUT NUMBER
207 -- x_msg_data OUT VARCHAR2
208 -- Version : Current version 1.0
209 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
210 -- and basic operation, developer must manually add parameters and business logic as necessary.
211 --
212 -- End of Comments
213 -- ==============================================================================
214 --
215
216 PROCEDURE Update_List_Query(
217 p_api_version_number IN NUMBER,
218 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
219 p_commit IN VARCHAR2 := FND_API.G_FALSE,
220 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2,
224 p_list_query_rec IN list_query_rec_type,
225 x_object_version_number OUT NOCOPY NUMBER
226 );
227
228 PROCEDURE Update_List_Query(
229 p_api_version_number IN NUMBER,
230 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
231 p_commit IN VARCHAR2 := FND_API.G_FALSE,
232 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2,
236 p_list_query_rec_tbl IN list_query_rec_type_tbl,
237 p_sql_string_tbl in sql_string_tbl ,
238 x_object_version_number OUT NOCOPY NUMBER
239 );
240
241 -- ==============================================================================
242 -- Start of Comments
243 -- ==============================================================================
244 -- API Name
245 -- Delete_List_Query
246 -- Type
247 -- Private
248 -- Pre-Req
249 --
250 -- Parameters
251 --
252 -- IN
253 -- p_api_version_number IN NUMBER Required
254 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
255 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
256 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
257 -- p_LIST_QUERY_ID IN NUMBER
258 -- p_object_version_number IN NUMBER Optional Default = NULL
259 --
260 -- OUT
261 -- x_return_status OUT VARCHAR2
262 -- x_msg_count OUT NUMBER
263 -- x_msg_data OUT VARCHAR2
264 -- Version : Current version 1.0
265 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
266 -- and basic operation, developer must manually add parameters and business logic as necessary.
267 --
268 -- End of Comments
269 -- ==============================================================================
270 --
271
272 PROCEDURE Delete_List_Query(
273 p_api_version_number IN NUMBER,
274 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
275 p_commit IN VARCHAR2 := FND_API.G_FALSE,
276 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
277 x_return_status OUT NOCOPY VARCHAR2,
278 x_msg_count OUT NOCOPY NUMBER,
279 x_msg_data OUT NOCOPY VARCHAR2,
280 p_list_query_id IN NUMBER,
281 p_object_version_number IN NUMBER
282 );
283
284 PROCEDURE Delete_parent_List_Query(
285 p_api_version_number IN NUMBER,
286 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
287 p_commit IN VARCHAR2 := FND_API.G_FALSE,
288 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
289 x_return_status OUT NOCOPY VARCHAR2,
290 x_msg_count OUT NOCOPY NUMBER,
291 x_msg_data OUT NOCOPY VARCHAR2,
292 p_parent_list_query_id IN NUMBER,
293 p_object_version_number IN NUMBER
294 );
295
296 -- ==============================================================================
297 -- Start of Comments
298 -- ==============================================================================
299 -- API Name
300 -- Lock_List_Query
301 -- Type
302 -- Private
303 -- Pre-Req
304 --
305 -- Parameters
306 --
307 -- IN
308 -- p_api_version_number IN NUMBER Required
309 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
310 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
311 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
312 -- p_list_query_rec IN list_query_rec_type Required
313 --
317 -- x_msg_data OUT VARCHAR2
314 -- OUT
315 -- x_return_status OUT VARCHAR2
316 -- x_msg_count OUT NUMBER
318 -- Version : Current version 1.0
319 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
320 -- and basic operation, developer must manually add parameters and business logic as necessary.
321 --
322 -- End of Comments
323 -- ==============================================================================
324 --
325
326 PROCEDURE Lock_List_Query(
327 p_api_version_number IN NUMBER,
328 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER,
331 x_msg_data OUT NOCOPY VARCHAR2,
332 p_list_query_id IN NUMBER,
333 p_object_version IN NUMBER
334 );
335
336
337 -- Start of Comments
338 --
339 -- validation procedures
340 --
341 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
342 -- For create: G_CREATE, for update: G_UPDATE
343 -- Note: 1. This is automated generated item level validation procedure.
344 -- The actual validation detail is needed to be added.
345 -- 2. We can also validate table instead of record. There will be an option for user to choose.
346 -- End of Comments
347
348 PROCEDURE Validate_list_query(
349 p_api_version_number IN NUMBER,
350 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
351 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
352 p_list_query_rec IN list_query_rec_type,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2
356 );
357
358 -- Start of Comments
359 --
360 -- validation procedures
361 --
362 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
363 -- For create: G_CREATE, for update: G_UPDATE
364 -- Note: 1. This is automated generated item level validation procedure.
365 -- The actual validation detail is needed to be added.
366 -- 2. Validate the unique keys, lookups here
367 -- End of Comments
368
369 PROCEDURE Check_list_query_Items (
370 P_list_query_rec IN list_query_rec_type,
371 p_validation_mode IN VARCHAR2,
372 x_return_status OUT NOCOPY VARCHAR2
373 );
374
375 -- Start of Comments
376 --
377 -- Record level validation procedures
378 --
379 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
380 -- For create: G_CREATE, for update: G_UPDATE
381 -- Note: 1. This is automated generated item level validation procedure.
382 -- The actual validation detail is needed to be added.
383 -- 2. Developer can manually added inter-field level validation.
384 -- End of Comments
385
386 PROCEDURE Validate_list_query_rec(
387 p_api_version_number IN NUMBER,
388 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
389 x_return_status OUT NOCOPY VARCHAR2,
390 x_msg_count OUT NOCOPY NUMBER,
391 x_msg_data OUT NOCOPY VARCHAR2,
392 p_list_query_rec IN list_query_rec_type
393 );
394
395
396 -- Start of Comments
397 ---------------------------------------------------------------------
398 -- PROCEDURE
399 -- Copy_List_Queries
400 --
401 -- PURPOSE
402 -- Take list header id of the list to copy from
403 -- last update date, last updated by, creation date and
404 -- created by are defaulted
405 -- copy the entries pertaining to a particular list in
406 -- AMS_LIST_QUERIES_ALL into a new set and create new AMS_LIST_SELECT_ACTIONS
407 -- associate the new list header id with copied parent list query id
408 --
409 -- PARAMETERS
410 --
411 --
412 -- End Of Comments
413
414
415
416 PROCEDURE Copy_List_Queries
417 ( p_api_version IN NUMBER,
418 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
419 p_commit IN VARCHAR2 := FND_API.G_FALSE,
420 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
421 p_source_listheader_id IN NUMBER,
422 p_new_listheader_id IN NUMBER,
423 p_new_listheader_name IN VARCHAR2,
424
425 x_return_status OUT NOCOPY VARCHAR2,
426 x_msg_count OUT NOCOPY NUMBER,
427 x_msg_data OUT NOCOPY VARCHAR2
428 );
429
430 END AMS_List_Query_PVT;
[Home] [Help]