[Home] [Help]
PACKAGE: APPS.AMS_LISTHEADER_PVT
Source
1 PACKAGE AMS_ListHeader_PVT as
2 /* $Header: amsvlshs.pls 120.1 2005/08/24 16:01:57 appldev ship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMS_ListHeader_PVT
7 --
8 -- PURPOSE
9 -- Private API for Oracle Marketing(AMS) List Headers
10 --
11 -- Procedures:
12
13 -- Create_ListHeader
14 -- Update_ListHeader
15 -- Delete_ListHeader
16 -- Lock_ListHeader
17
18 -- Validate_ListHeader
19 -- Validate_List_Record
20 -- Validate_List_Items
21
22
23 -- Complete_ListHeader_rec
24 -- Init_ListHeader_rec
25 -- Copy list
26 -- HISTORY
27 -- 05/12/1999 tdonohoe created
28 -- 07/05/2000 tdonohoe modified, update code to conform to SQL coding standards.
29 -- 07/05/2000 tdonohoe modified, update code to conform to SQL coding standards.
30 -- 12/05/201 vbhandar added
31 -- End of Comments
32
33
34 TYPE list_header_rec_type IS RECORD(
35 list_header_id number,
36 last_update_date date,
37 last_updated_by number,
38 creation_date date,
39 created_by number,
40 last_update_login number,
41 object_version_number number,
42 request_id number,
43 program_id number,
44 program_application_id number,
45 program_update_date date,
46 view_application_id number,
47 list_name varchar2(240),
48 list_used_by_id number,
49 arc_list_used_by varchar2(30),
50 list_type varchar2(30),
51 status_code varchar2(30),
52 status_date date,
53 generation_type varchar2(30),
54 repeat_exclude_type varchar2(30),
55 row_selection_type varchar2(30),
56 owner_user_id number,
57 access_level varchar2(30),
58 enable_log_flag varchar2(1),
59 enable_word_replacement_flag varchar2(1),
60 enable_parallel_dml_flag varchar2(1),
61 dedupe_during_generation_flag varchar2(1),
62 generate_control_group_flag varchar2(1),
63 last_generation_success_flag varchar2(1),
64 forecasted_start_date date,
65 forecasted_end_date date,
66 actual_end_date date,
67 sent_out_date date,
68 dedupe_start_date date,
69 last_dedupe_date date,
70 last_deduped_by_user_id number,
71 workflow_item_key number,
72 no_of_rows_duplicates number,
73 no_of_rows_min_requested number,
74 no_of_rows_max_requested number,
75 no_of_rows_in_list number,
76 no_of_rows_in_ctrl_group number,
77 no_of_rows_active number,
78 no_of_rows_inactive number,
79 no_of_rows_manually_entered number,
80 no_of_rows_do_not_call number,
81 no_of_rows_do_not_mail number,
82 no_of_rows_random number,
83 org_id number,
84 main_gen_start_time date,
85 main_gen_end_time date,
86 main_random_nth_row_selection number,
87 main_random_pct_row_selection number,
88 ctrl_random_nth_row_selection number,
89 ctrl_random_pct_row_selection number,
90 repeat_source_list_header_id varchar2(4000),
91 result_text varchar2(4000),
92 keywords varchar2(4000),
93 description varchar2(4000),
94 list_priority number,
95 assign_person_id number,
96 list_source varchar2(240),
97 list_source_type varchar2(30),
98 list_online_flag varchar2(1),
99 random_list_id number,
100 enabled_flag varchar2(1),
101 assigned_to number,
102 query_id number,
103 owner_person_id number,
104 archived_by number,
105 archived_date date,
106 attribute_category varchar2(30),
107 attribute1 varchar2(150),
108 attribute2 varchar2(150),
109 attribute3 varchar2(150),
110 attribute4 varchar2(150),
111 attribute5 varchar2(150),
112 attribute6 varchar2(150),
113 attribute7 varchar2(150),
114 attribute8 varchar2(150),
115 attribute9 varchar2(150),
116 attribute10 varchar2(150),
117 attribute11 varchar2(150),
118 attribute12 varchar2(150),
119 attribute13 varchar2(150),
120 attribute14 varchar2(150),
121 attribute15 varchar2(150),
122 timezone_id number,
123 user_entered_start_time date,
124 user_status_id number,
125 quantum number,
126 release_control_alg_id number,
127 dialing_method varchar2(10),
128 calling_calendar_id number,
129 release_strategy varchar2(10),
130 custom_setup_id number,
131 country number,
132 callback_priority_flag varchar2(1),
133 call_center_ready_flag varchar2(1),
134 language varchar2(4) ,
135 purge_flag varchar2(1),
136 public_flag varchar2(1),
137 list_category varchar2(120),
138 quota number,
139 quota_reset number,
140 recycling_alg_id number,
141 source_lang varchar2(4),
142 no_of_rows_prev_contacted number,
143 APPLY_TRAFFIC_COP varchar2(1),
144 PURPOSE_CODE varchar2(120),
145 CTRL_CONF_LEVEL number,
146 CTRL_REQ_RESP_RATE number,
147 CTRL_LIMIT_OF_ERROR number,
148 STATUS_CODE_OLD varchar2(30),
149 CTRL_CONC_JOB_ID number,
150 CTRL_STATUS_CODE varchar2(30),
151 CTRL_GEN_MODE varchar2(30),
152 APPLY_SUPPRESSION_FLAG varchar2(1)
153 );
154
155
156 -- Start of Comments
157 ---------------------------------------------------------------------
158 -- PROCEDURE
159 -- Create_Listheader
160 --
161 -- PURPOSE
162 -- Creates a new List Hheader.
163 --
164 -- PARAMETERS
165 -- p_listheader_rec The New Record to be inserted.
166 -- x_listheader_id The Primary of The New Record.
167 -- End Of Comments
168
169 PROCEDURE Create_Listheader
170 ( p_api_version IN NUMBER,
171 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
172 p_commit IN VARCHAR2 := FND_API.G_FALSE,
173 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
174 x_return_status OUT NOCOPY VARCHAR2,
175 x_msg_count OUT NOCOPY NUMBER,
176 x_msg_data OUT NOCOPY VARCHAR2,
177 p_listheader_rec IN list_header_rec_type,
178 x_listheader_id OUT NOCOPY NUMBER
179 );
180
181
182
183 -- Start of Comments
184 ------------------------------------------------------------------------------
185 -- PROCEDURE
186 -- Update_ListHeader
187 --
188 -- PURPOSE
189 -- Updates an existing List Header.
190 --
191 -- PARAMETERS
192 -- p_listheader_rec The Record to be Updated.
193
194 -- NOTES
195 -- 1. Raise exception if the object_version_number doesn't match.
196 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
197 -- that column won't be updated.
198 -- End Of Comments
199
200 PROCEDURE Update_ListHeader
201 ( p_api_version IN NUMBER,
202 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
203 p_commit IN VARCHAR2 := FND_API.G_FALSE,
204 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2,
208 p_listheader_rec IN list_header_rec_type
209 );
210
211
212 --------------------------------------------------------------------
213 -- PROCEDURE
214 -- Delete_ListHeader
215 --
216 -- PURPOSE
217 -- Deletes a List Header.
218 --
219 -- PARAMETERS
220 -- p_listheader_id: the list header primary key.
221 -- p_object_version: the object_version_number
222 --
223 -- NOTES
224 -- 1. Raise exception if the object_version_number doesn't match.
225 --------------------------------------------------------------------
226
227 PROCEDURE Delete_ListHeader
228 ( p_api_version IN NUMBER,
229 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
230 p_commit IN VARCHAR2 := FND_API.G_FALSE,
231 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2,
235 p_listheader_id IN NUMBER
236 );
237
238 -- Start Of Comments
239 -------------------------------------------------------------------
240 -- PROCEDURE
241 -- Lock_ListHeader
242 --
243 -- PURPOSE
244 -- Lock a List Header.
245 --
246 -- PARAMETERS
247 -- p_listheader: the list header primary key
248 -- p_object_version: the object_version_number
249 --
250 -- NOTES
251 -- 1. Raise exception if the object_version_number doesn't match.
252 --------------------------------------------------------------------
253 -- End Of Comments
254
255 PROCEDURE Lock_ListHeader
256 ( p_api_version IN NUMBER,
257 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
258 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
259 x_return_status OUT NOCOPY VARCHAR2,
260 x_msg_count OUT NOCOPY NUMBER,
261 x_msg_data OUT NOCOPY VARCHAR2,
262 p_listheader_id IN NUMBER,
263 p_object_version IN NUMBER
264 );
265
266
267 -- Start of Comments
268 ---------------------------------------------------------------------
269 -- PROCEDURE
270 -- Validate_ListHeader
271 --
272 -- PURPOSE
273 -- Validate a List Header Record.
274 --
275 -- PARAMETERS
276 -- p_listheader_rec: the list header record to be validated
277 --
278 -- NOTES
279 -- 1. p_listheader_rec_rec should be the complete list header record. There
280 -- should not be any FND_API.g_miss_char/num/date in it.
281 ----------------------------------------------------------------------
282 -- End Of Comments
283
284 PROCEDURE Validate_ListHeader
285 ( p_api_version IN NUMBER,
286 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
287 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2,
291 p_listheader_rec IN list_header_rec_type
292 );
293
294
295 -- Start of Comments
296 ---------------------------------------------------------------------
297 -- PROCEDURE
298 -- Validate_List_Items
299 --
300 -- PURPOSE
301 -- Perform the item level checking including unique keys,
302 -- required columns, foreign keys, domain constraints.
303 --
304 -- PARAMETERS
305 -- p_listheader : the record to be validated
306 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
307 ----------------------------------------------------------------------- End Of Comments
308
309 PROCEDURE Validate_List_Items
310 ( p_listheader_rec IN list_header_rec_type,
311 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
312 x_return_status OUT NOCOPY VARCHAR2
313 );
314
315
316
317 -- Start of Comments
318 ---------------------------------------------------------------------
319 -- PROCEDURE
320 -- Validate_List_Record
321 --
322 -- PURPOSE
323 -- Validate the list record level business rules.
324 --
325 -- PARAMETERS
326 -- p_listheader_rec: the record to be validated; may contain attributes
327 -- as FND_API.g_miss_char/num/date
328 -- p_complete_rec: the complete record after all "g_miss" items
329 -- have been replaced by current database values
330 ---------------------------------------------------------------------
331 -- End Of Comments
332
333 PROCEDURE Validate_List_Record
334 ( p_listheader_rec IN list_header_rec_type,
335 p_complete_rec IN list_header_rec_type := NULL,
336 x_return_status OUT NOCOPY VARCHAR2
337 );
338
339
340 ---------------------------------------------------------------------
341 -- PROCEDURE
342 -- Init_ListHeader_rec
343 --
344 -- PURPOSE
345 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
346 ---------------------------------------------------------------------
347 PROCEDURE Init_ListHeader_rec(
348 x_listheader_rec OUT NOCOPY list_header_rec_type
349 );
350
351
352 ---------------------------------------------------------------------
353 -- PROCEDURE
354 -- Complete_ListHeader_rec
355 --
356 -- PURPOSE
357 -- For update_listheader, some attributes may be passed in as
358 -- FND_API.g_miss_char/num/date if the user doesn't want to
359 -- update those attributes. This procedure will replace the
360 -- "g_miss" attributes with current database values.
361 --
362 -- PARAMETERS
363 -- p_listheader_rec: the record which may contain attributes as
364 -- FND_API.g_miss_char/num/date
365 -- x_complete_rec : the complete record after all "g_miss" items
366 -- have been replaced by current database values
367 ---------------------------------------------------------------------
371 );
368 PROCEDURE Complete_ListHeader_rec(
369 p_listheader_rec IN list_header_rec_type,
370 x_complete_rec OUT NOCOPY list_header_rec_type
372
373 PROCEDURE Update_list_header_count(
374 p_list_header_id IN number,
375 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
376 p_commit IN VARCHAR2 := FND_API.G_FALSE,
377 x_return_status OUT NOCOPY VARCHAR2,
378 x_msg_count OUT NOCOPY NUMBER,
379 x_msg_data OUT NOCOPY VARCHAR2
380 );
381
382 ---------------------------------------------------------------------
383 -- PROCEDURE
384 -- Update_Prev_contacted_count
385 --
386 -- PURPOSE
387 -- aDDED TO SUPPORT TRIGGER BACKPORT FUNCTIONALITY
388 -- Gived a schedule id / one off event id or event header id and the last contacted date
389 -- update all list entries of the related target group or invite list ,
390 -- who were not contacted earlier, with the last contacted date
391 -- also populate list header with total number of records with last contacted date populated
392 -- PARAMETERS
393 -- p_used_by: eg CSCH/EVEO/EVEH
394 -- p_used_by : Schedule Id/ or One off Event Id or Event Header Id
395 -- p_last_contacted_date :last contacted date to be populated in to the list entries table
396 ---------------------------------------------------------------------
397
398 PROCEDURE Update_Prev_contacted_count(
399 p_used_by_id IN number,
400 p_used_by IN VARCHAR2,
401 p_last_contacted_date IN DATE,
402 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
403 p_commit IN VARCHAR2 := FND_API.G_FALSE,
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2
407 );
408
409
410 -- Start of Comments
411 ---------------------------------------------------------------------
412 -- PROCEDURE
413 -- Copy_List
414 --
415 -- PURPOSE
416 -- Take list header id of the list to copy from, list name, public
417 -- flag, purge flag, owner user id and description for the new list and generate
418 -- a new list header id.
419 -- last update date, last updated by, creation date and
420 -- created by are defaulted
421 -- copy the entries pertaining to a particular list in
422 -- AMS_LIST_SELECT_ACTIONS, AMS_LIST_QUERIES_ALL, AMS_LIST_ENTRIES into a new set
423 -- and associate them with a new list header.
424 --
425 -- PARAMETERS
426 -- p_listheader_rec The Record to be copied.
427 -- x_listheader_id The Primary of The New Record.
428 -- End Of Comments
429
430
431
432 PROCEDURE Copy_List
433 ( p_api_version IN NUMBER,
434 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
435 p_commit IN VARCHAR2 := FND_API.G_FALSE,
436 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2,
440 p_source_listheader_id IN NUMBER,
441 p_listheader_rec IN list_header_rec_type,
442 p_copy_select_actions IN VARCHAR2 := 'Y',
443 p_copy_list_queries IN VARCHAR2 := 'Y',
444 p_copy_list_entries IN VARCHAR2 := 'Y',
445
446 x_listheader_id OUT NOCOPY NUMBER
447 );
448
449
450
451 END AMS_ListHeader_PVT;