1 PACKAGE AMS_Media_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvmeds.pls 115.13 2002/12/18 09:26:12 cgoyal ship $ */
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- AMS_Media_PVT
7 --
8 -- PURPOSE
9 -- This package is a Private API for managing Media information in
10 -- AMS. It contains specification for pl/sql records and tables
11 --
12 -- AMS_MEDIA_VL:
13 -- Create_Media (see below for specification)
14 -- Update_Media (see below for specification)
15 -- Delete_Media (see below for specification)
16 -- Lock_Media (see below for specification)
17 -- Validate_Media (see below for specification)
18 --
19 -- Check_Media_Items (see below for specification)
20 -- Check_Media_Record (see below for specification)
21 -- Init_Media_Rec
22 -- Complete_Media_Rec
23 --
24 -- AMS_MEDIA_CHANNELS:
25 -- Create_MediaChannel
26 -- Update_MediaChannel
27 -- Delete_MediaChannel
28 -- Lock_MediaChannel
29 -- Validate_MediaChannel
30 --
31 -- Check_MediaChannel_Items
32 -- Check_MediaChannel_Records
33 -- Init_MediaChannel_Rec
34 -- Complete_MediaChannel_Rec
35 --
36 -- NOTES
37 --
38 --
39 -- HISTORY
40 -- 03-Nov-1999 choang Created.
41 -- 10-Dec-1999 ptendulk Modified
42 -----------------------------------------------------------
43
44 -------------------------------------
45 ----- MEDIA -----
46 -------------------------------------
47 -- Record for AMS_MEDIA_VL
48 TYPE Media_Rec_Type IS RECORD (
49 media_id NUMBER,
50 last_update_date DATE,
51 last_updated_by NUMBER,
52 creation_date DATE,
53 created_by NUMBER,
54 last_update_login NUMBER,
55 object_version_number NUMBER,
56 media_type_code VARCHAR2(30),
57 media_type_name VARCHAR2(80),
58 inbound_flag VARCHAR2(1),
59 enabled_flag VARCHAR2(1),
60 attribute_category VARCHAR2(30),
61 attribute1 VARCHAR2(150),
62 attribute2 VARCHAR2(150),
63 attribute3 VARCHAR2(150),
64 attribute4 VARCHAR2(150),
65 attribute5 VARCHAR2(150),
66 attribute6 VARCHAR2(150),
67 attribute7 VARCHAR2(150),
68 attribute8 VARCHAR2(150),
69 attribute9 VARCHAR2(150),
70 attribute10 VARCHAR2(150),
71 attribute11 VARCHAR2(150),
72 attribute12 VARCHAR2(150),
73 attribute13 VARCHAR2(150),
74 attribute14 VARCHAR2(150),
75 attribute15 VARCHAR2(150),
76 media_name VARCHAR2(120),
77 description VARCHAR2(4000)
78 );
79
80 --------------------------------------------------------------------
81 -- PROCEDURE
82 -- Create_Media
83 --
84 -- PURPOSE
85 -- Create media entry.
86 --
87 -- PARAMETERS
88 -- p_media_rec: the record representing AMS_MEDIA_VL view..
89 -- x_media_id: the media_id.
90 --
91 -- NOTES
92 -- 1. object_version_number will be set to 1.
93 -- 2. If media_id is passed in, the uniqueness will be checked.
94 -- Raise exception in case of duplicates.
95 -- 3. If import_list_header_id is not passed in, generate a unique one from
96 -- the sequence.
97 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
98 -- Raise exception for invalid flag.
99 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
100 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
101 --------------------------------------------------------------------
102 PROCEDURE Create_Media (
103 p_api_version IN NUMBER,
104 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
105 p_commit IN VARCHAR2 := FND_API.g_false,
106 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
107
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_msg_count OUT NOCOPY NUMBER,
110 x_msg_data OUT NOCOPY VARCHAR2,
111
112 p_media_rec IN Media_Rec_Type,
113 x_media_id OUT NOCOPY NUMBER
114 );
115
116 --------------------------------------------------------------------
117 -- PROCEDURE
118 -- Update_Media
119 --
120 -- PURPOSE
121 -- Update a media entry.
122 --
123 -- PARAMETERS
124 -- p_media_rec: the record representing AMS_MEDIA_VL (without the ROW_ID column).
125 --
126 -- NOTES
127 -- 1. Raise exception if the object_version_number doesn't match.
128 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
129 -- that column won't be updated.
130 --------------------------------------------------------------------
131 PROCEDURE Update_Media (
132 p_api_version IN NUMBER,
133 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
134 p_commit IN VARCHAR2 := FND_API.g_false,
135 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
136
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_msg_count OUT NOCOPY NUMBER,
139 x_msg_data OUT NOCOPY VARCHAR2,
140
141 p_media_rec IN Media_Rec_Type
142 );
143
144 --------------------------------------------------------------------
145 -- PROCEDURE
146 -- Delete_Media
147 --
148 -- PURPOSE
149 -- Delete a media entry.
150 --
151 -- PARAMETERS
152 -- p_media_id: the media_id
153 -- p_object_version: the object_version_number
154 --
155 -- ISSUES
156 --
157 -- NOTES
158 -- 1. Raise exception if the object_version_number doesn't match.
159 --------------------------------------------------------------------
160 PROCEDURE Delete_Media (
161 p_api_version IN NUMBER,
162 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
163 p_commit IN VARCHAR2 := FND_API.g_false,
164 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
165
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_msg_count OUT NOCOPY NUMBER,
168 x_msg_data OUT NOCOPY VARCHAR2,
169
170 p_media_id IN NUMBER,
171 p_object_version IN NUMBER
172 );
173
174 --------------------------------------------------------------------
175 -- PROCEDURE
176 -- Lock_Media
177 --
178 -- PURPOSE
179 -- Lock a media entry.
180 --
181 -- PARAMETERS
182 -- p_media_id: the media
183 -- p_object_version: the object_version_number
184 --
185 -- ISSUES
186 --
187 -- NOTES
188 -- 1. Raise exception if the object_version_number doesn't match.
189 --------------------------------------------------------------------
190 PROCEDURE Lock_Media (
191 p_api_version IN NUMBER,
192 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
193 p_commit IN VARCHAR2 := FND_API.g_false,
194 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
195
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2,
199
200 p_media_id IN NUMBER,
201 p_object_version IN NUMBER
202 );
203
204 --------------------------------------------------------------------
205 -- PROCEDURE
206 -- Validate_Media
207 --
208 -- PURPOSE
209 -- Validate a media entry.
210 --
211 -- PARAMETERS
212 -- p_media_rec: the record representing AMS_MEDIA_VL (without ROW_ID).
213 --
214 -- NOTES
215 -- 1. p_media_rec should be the complete media record. There
216 -- should not be any FND_API.g_miss_char/num/date in it.
217 -- 2. If FND_API.g_miss_char/num/date is in the record, then raise
218 -- an exception, as those values are not handled.
219 --------------------------------------------------------------------
220 PROCEDURE Validate_Media (
221 p_api_version IN NUMBER,
222 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
223 p_commit IN VARCHAR2 := FND_API.g_false,
224 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
225
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229
230 p_media_rec IN Media_Rec_Type
231 );
232
233 ---------------------------------------------------------------------
234 -- PROCEDURE
235 -- Check_Media_Items
236 --
237 -- PURPOSE
238 -- Perform the item level checking including unique keys,
239 -- required columns, foreign keys, domain constraints.
240 --
241 -- PARAMETERS
242 -- p_media_rec: the record to be validated
243 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
244 ---------------------------------------------------------------------
245 PROCEDURE Check_Media_Items (
246 p_media_rec IN Media_Rec_Type,
247 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
248 x_return_status OUT NOCOPY VARCHAR2
249 );
250
251 ---------------------------------------------------------------------
252 -- PROCEDURE
253 -- Check_Media_Record
254 --
255 -- PURPOSE
256 -- Check the record level business rules.
257 --
258 -- PARAMETERS
259 -- p_media_rec: the record to be validated; may contain attributes
260 -- as FND_API.g_miss_char/num/date
261 -- p_complete_rec: the complete record after all "g_miss" items
262 -- have been replaced by current database values
263 ---------------------------------------------------------------------
264 PROCEDURE Check_Media_Record (
265 p_media_rec IN Media_Rec_Type,
266 p_complete_rec IN Media_Rec_Type := NULL,
267 x_return_status OUT NOCOPY VARCHAR2
268 );
269
270 ---------------------------------------------------------------------
271 -- PROCEDURE
272 -- Init_Media_Rec
273 --
274 -- PURPOSE
275 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
276 ---------------------------------------------------------------------
277 PROCEDURE Init_Media_Rec (
278 x_media_rec OUT NOCOPY Media_Rec_Type
279 );
280
281 ---------------------------------------------------------------------
282 -- PROCEDURE
283 -- Complete_Media_Rec
284 --
285 -- PURPOSE
286 -- For Update_Media, some attributes may be passed in as
287 -- FND_API.g_miss_char/num/date if the user doesn't want to
288 -- update those attributes. This procedure will replace the
289 -- "g_miss" attributes with current database values.
290 --
291 -- PARAMETERS
292 -- p_media_rec: the record which may contain attributes as
293 -- FND_API.g_miss_char/num/date
294 -- x_complete_rec: the complete record after all "g_miss" items
295 -- have been replaced by current database values
296 ---------------------------------------------------------------------
297 PROCEDURE Complete_Media_Rec (
298 p_media_rec IN Media_Rec_Type,
299 x_complete_rec OUT NOCOPY Media_Rec_Type
300 );
301
302
303 -------------------------------------
304 ------- MEDIA CHANNEL ------
305 -------------------------------------
306 -- Record declaration for AMS_MEDIA_CHANNELS
307 TYPE MediaChannel_Rec_Type IS RECORD (
308 media_channel_id NUMBER,
309 last_update_date DATE,
310 last_updated_by NUMBER,
311 creation_date DATE,
312 created_by NUMBER,
313 last_update_login NUMBER,
314 object_version_number NUMBER,
315 media_id NUMBER,
316 channel_id NUMBER,
317 active_from_date DATE,
318 active_to_date DATE
319 );
320
321 --------------------------------------------------------------------
322 -- PROCEDURE
323 -- Create_MediaChannel
324 --
325 -- PURPOSE
326 -- Create media channel entry.
327 --
328 -- PARAMETERS
329 -- p_mediachl_rec: the record representing AMS_MEDIA_CHANNELS view..
330 -- x_mediachl_id: the media_channel_id.
331 --
332 -- NOTES
333 -- 1. object_version_number will be set to 1.
334 -- 2. If ID is passed in, the uniqueness will be checked.
335 -- Raise exception in case of duplicates.
336 -- 3. If import_list_header_id is not passed in, generate a unique one from
337 -- the sequence.
338 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
339 -- Raise exception for invalid flag.
340 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
341 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
342 --------------------------------------------------------------------
343 PROCEDURE Create_MediaChannel (
344 p_api_version IN NUMBER,
345 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
346 p_commit IN VARCHAR2 := FND_API.g_false,
347 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
348
349 x_return_status OUT NOCOPY VARCHAR2,
350 x_msg_count OUT NOCOPY NUMBER,
351 x_msg_data OUT NOCOPY VARCHAR2,
352
353 p_mediachl_rec IN MediaChannel_Rec_Type,
354 x_mediachl_id OUT NOCOPY NUMBER
355 );
356
357 --------------------------------------------------------------------
358 -- PROCEDURE
359 -- Update_MediaChannel
360 --
361 -- PURPOSE
362 -- Update a media channel entry.
363 --
364 -- PARAMETERS
365 -- p_mediachl_rec: the record representing AMS_MEDIA_CHANNELS (without the ROW_ID column).
366 --
367 -- NOTES
368 -- 1. Raise exception if the object_version_number doesn't match.
369 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
370 -- that column won't be updated.
371 --------------------------------------------------------------------
372 PROCEDURE Update_MediaChannel (
373 p_api_version IN NUMBER,
374 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
375 p_commit IN VARCHAR2 := FND_API.g_false,
376 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
377
378 x_return_status OUT NOCOPY VARCHAR2,
379 x_msg_count OUT NOCOPY NUMBER,
380 x_msg_data OUT NOCOPY VARCHAR2,
381
382 p_mediachl_rec IN MediaChannel_Rec_Type
383 );
384
385 --------------------------------------------------------------------
386 -- PROCEDURE
387 -- Delete_MediaChannel
388 --
389 -- PURPOSE
390 -- Delete a media channel entry.
391 --
392 -- PARAMETERS
393 -- p_mediachl_id: the mediachl_id
394 -- p_object_version: the object_version_number
395 --
396 -- ISSUES
397 --
398 -- NOTES
399 -- 1. Raise exception if the object_version_number doesn't match.
400 --------------------------------------------------------------------
401 PROCEDURE Delete_MediaChannel (
402 p_api_version IN NUMBER,
403 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
404 p_commit IN VARCHAR2 := FND_API.g_false,
405 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
406
407 x_return_status OUT NOCOPY VARCHAR2,
408 x_msg_count OUT NOCOPY NUMBER,
409 x_msg_data OUT NOCOPY VARCHAR2,
410
411 p_mediachl_id IN NUMBER,
412 p_object_version IN NUMBER
413 );
414
415 --------------------------------------------------------------------
416 -- PROCEDURE
417 -- Lock_MediaChannel
418 --
419 -- PURPOSE
420 -- Lock a media channel entry.
421 --
422 -- PARAMETERS
423 -- p_mediachl_id: the media
427 --
424 -- p_object_version: the object_version_number
425 --
426 -- ISSUES
428 -- NOTES
429 -- 1. Raise exception if the object_version_number doesn't match.
430 --------------------------------------------------------------------
431 PROCEDURE Lock_MediaChannel (
432 p_api_version IN NUMBER,
433 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
434 p_commit IN VARCHAR2 := FND_API.g_false,
435 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
436
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2,
440
441 p_mediachl_id IN NUMBER,
442 p_object_version IN NUMBER
443 );
444
445 --------------------------------------------------------------------
446 -- PROCEDURE
447 -- Validate_MediaChannel
448 --
449 -- PURPOSE
450 -- Validate a media channel entry.
451 --
452 -- PARAMETERS
453 -- p_mediachl_rec: the record representing AMS_MEDIA_CHANNELS (without ROW_ID).
454 --
455 -- NOTES
456 -- 1. p_mediachl_rec should be the complete media channel record. There
457 -- should not be any FND_API.g_miss_char/num/date in it.
458 -- 2. If FND_API.g_miss_char/num/date is in the record, then raise
459 -- an exception, as those values are not handled.
460 --------------------------------------------------------------------
461 PROCEDURE Validate_MediaChannel (
462 p_api_version IN NUMBER,
463 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
464 p_commit IN VARCHAR2 := FND_API.g_false,
465 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
466
467 x_return_status OUT NOCOPY VARCHAR2,
468 x_msg_count OUT NOCOPY NUMBER,
469 x_msg_data OUT NOCOPY VARCHAR2,
470
471 p_mediachl_rec IN MediaChannel_Rec_Type
472 );
473
474 ---------------------------------------------------------------------
475 -- PROCEDURE
476 -- Check_MediaChannel_Items
477 --
478 -- PURPOSE
479 -- Perform the item level checking including unique keys,
480 -- required columns, foreign keys, domain constraints.
481 --
482 -- PARAMETERS
483 -- p_mediachl_rec: the record to be validated
484 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
485 ---------------------------------------------------------------------
486 PROCEDURE Check_MediaChannel_Items (
487 p_mediachl_rec IN MediaChannel_Rec_Type,
488 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
489 x_return_status OUT NOCOPY VARCHAR2
490 );
491
492 ---------------------------------------------------------------------
493 -- PROCEDURE
494 -- Check_MediaChannel_Record
495 --
496 -- PURPOSE
497 -- Check the record level business rules.
498 --
499 -- PARAMETERS
500 -- p_mediachl_rec: the record to be validated; may contain attributes
501 -- as FND_API.g_miss_char/num/date
502 -- p_complete_rec: the complete record after all "g_miss" items
503 -- have been replaced by current database values
504 ---------------------------------------------------------------------
505 PROCEDURE Check_MediaChannel_Record (
506 p_mediachl_rec IN MediaChannel_Rec_Type,
507 p_complete_rec IN MediaChannel_Rec_Type := NULL,
508 x_return_status OUT NOCOPY VARCHAR2
509 );
510
511 ---------------------------------------------------------------------
512 -- PROCEDURE
513 -- Init_MediaChannel_Rec
514 --
515 -- PURPOSE
516 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
517 ---------------------------------------------------------------------
518 PROCEDURE Init_MediaChannel_Rec (
519 x_mediachl_rec OUT NOCOPY MediaChannel_Rec_Type
520 );
521
522 ---------------------------------------------------------------------
523 -- PROCEDURE
524 -- Complete_MediaChannel_Rec
525 --
526 -- PURPOSE
527 -- For Update_MediaChannel, some attributes may be passed in as
528 -- FND_API.g_miss_char/num/date if the user doesn't want to
529 -- update those attributes. This procedure will replace the
530 -- "g_miss" attributes with current database values.
531 --
532 -- PARAMETERS
533 -- p_mediachl_rec: the record which may contain attributes as
534 -- FND_API.g_miss_char/num/date
535 -- x_complete_rec: the complete record after all "g_miss" items
536 -- have been replaced by current database values
537 ---------------------------------------------------------------------
538 PROCEDURE Complete_MediaChannel_Rec (
539 p_mediachl_rec IN MediaChannel_Rec_Type,
540 x_complete_rec OUT NOCOPY MediaChannel_Rec_Type
541 );
542
543
544 END AMS_Media_PVT;