1 PACKAGE AMS_ListGeneration_PKG AUTHID CURRENT_USER AS
2 /* $Header: amsvlgns.pls 120.3 2006/01/18 01:04:02 bmuthukr ship $*/
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMS_ListGeneration
7 --
8 -- PURPOSE
9 -- This package performs the generation of all oracle marketing defined lists.
10 --
11 -- Procedures:
12 -- Generate_List
13 --
14 --
15 -- NOTES
16 --
17 --
18 -- HISTORY
19 -- 06/21/1999 tdonohoe created
20 -- 06/22/2000 tdonohoe modified c_listheader_dets to explicitly specify column values from the ams_list_headers_all table.
21 -- End of Comments
22
23
24 --Retrieve all List Header parameters necessary for generation of the list.
25 Cursor C_ListHeader_Dets(p_list_header_id NUMBER)IS
26 SELECT
27 list_header_id
28 ,last_update_date
29 ,last_updated_by
30 ,creation_date
31 ,created_by
32 ,last_update_login
33 ,object_version_number
34 ,request_id
35 ,program_id
36 ,program_application_id
37 ,program_update_date
38 ,view_application_id
39 ,list_name
40 ,list_used_by_id
41 ,arc_list_used_by
42 ,list_type
43 ,status_code
44 ,status_date
45 ,generation_type
46 ,repeat_exclude_type
47 ,row_selection_type
48 ,owner_user_id
49 ,access_level
50 ,enable_log_flag
51 ,enable_word_replacement_flag
52 ,enable_parallel_dml_flag
53 ,dedupe_during_generation_flag
54 ,generate_control_group_flag
55 ,last_generation_success_flag
56 ,forecasted_start_date
57 ,forecasted_end_date
58 ,actual_end_date
59 ,sent_out_date
60 ,dedupe_start_date
61 ,last_dedupe_date
62 ,last_deduped_by_user_id
63 ,workflow_item_key
64 ,no_of_rows_duplicates
65 ,no_of_rows_min_requested
66 ,no_of_rows_max_requested
67 ,no_of_rows_in_list
68 ,no_of_rows_in_ctrl_group
69 ,no_of_rows_active
70 ,no_of_rows_inactive
71 ,no_of_rows_manually_entered
72 ,no_of_rows_do_not_call
73 ,no_of_rows_do_not_mail
74 ,no_of_rows_random
75 ,org_id
76 ,main_gen_start_time
77 ,main_gen_end_time
78 ,main_random_nth_row_selection
79 ,main_random_pct_row_selection
80 ,ctrl_random_nth_row_selection
81 ,ctrl_random_pct_row_selection
82 ,repeat_source_list_header_id
83 ,result_text
84 ,keywords
85 ,description
86 ,list_priority
87 ,assign_person_id
88 ,list_source
89 ,list_source_type
90 ,list_online_flag
91 ,random_list_id
92 ,enabled_flag
93 ,assigned_to
94 ,query_id
95 ,owner_person_id
96 ,archived_by
97 ,archived_date
98 ,attribute_category
99 ,attribute1
100 ,attribute2
101 ,attribute3
102 ,attribute4
103 ,attribute5
104 ,attribute6
105 ,attribute7
106 ,attribute8
107 ,attribute9
108 ,attribute10
109 ,attribute11
110 ,attribute12
111 ,attribute13
112 ,attribute14
113 ,attribute15
114 ,timezone_id
115 ,user_entered_start_time
116 From ams_list_headers_all
117 Where list_header_id = p_list_header_id;
118
119 --Retrieve all List Action details which generate the set of list entries for the list.
120 Cursor C_ListAction_Dets(p_list_header_id ams_list_headers_all.list_header_id%type)
121 IS
122 SELECT
123 list_select_action_id
124 ,last_update_date
125 ,last_updated_by
126 ,creation_date
127 ,created_by
128 ,last_update_login
129 ,object_version_number
130 ,list_header_id
131 ,order_number
132 ,list_action_type
133 ,incl_object_name
134 ,arc_incl_object_from
135 ,incl_object_id
136 ,incl_object_wb_sheet
137 ,incl_object_wb_owner
138 ,incl_object_cell_code
139 ,rank
140 ,no_of_rows_available
141 ,no_of_rows_requested
142 ,no_of_rows_used
143 ,distribution_pct
144 ,result_text
145 ,description
146 FROM ams_list_select_actions
147 WHERE list_header_id = p_list_header_id
148 ORDER BY order_number;
149
150 --Retrieve the number of list entries for each list action, excluding entries which are
151 --marked as duplicates.
152 Cursor C_Action_Entry_Count(p_list_header_id ams_list_headers_all.list_header_id%type)
153 IS Select list_select_action_id,count(*)
154 From ams_list_entries
155 where list_header_id = p_list_header_id
156 and marked_as_duplicate_flag ='N'
157 group by list_select_action_id
158 order by list_select_action_id;
159
160 --Getting the set of defined master list types.
161 Cursor C_Mapping_Types IS Select list_source_type_id,
162 source_type_code,
163 source_object_name,
164 source_object_pk_field
165 From ams_list_src_types
166 Where master_source_type_flag = 'Y'
167 And list_source_type = 'TARGET';
168
169 --Getting the set of sub types associated with a master type.
170 Cursor C_Mapping_SubTypes(p_master_source_type_id ams_list_src_type_assocs.master_source_type_id%type)
171 IS Select source_type_code
172 From ams_list_src_types a,
173 ams_list_src_type_assocs b
174 Where b.master_source_type_id = p_master_source_type_id
175 And b.sub_source_type_id = a.list_source_type_id;
176
177
178 --getting the set of workbooks associated with the specified target segment.
179 Cursor C_Segment_WorkBooks(p_cell_id ams_cells_all_b.cell_id%type)
180 IS Select d.WorkBook_Name
181 From ams_act_discoverer_all d,
182 ams_cells_all_b c
183 Where c.cell_id = p_cell_id
184 And c.cell_id = d.act_discoverer_used_by_id
185 And arc_act_discoverer_used_by = 'CELL';
186
187
188 PROCEDURE Generate_List
189 ( p_api_version IN NUMBER,
190 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
191 p_commit IN VARCHAR2 := FND_API.G_FALSE,
192 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
193 p_list_header_id IN NUMBER ,
194 x_return_status OUT NOCOPY VARCHAR2,
195 x_msg_count OUT NOCOPY NUMBER,
196 x_msg_data OUT NOCOPY VARCHAR2);
197
198 PROCEDURE process_imph
199 (p_action_used_by_id in number,
200 p_incl_object_id in number,
201 p_list_action_type in varchar2,
202 p_list_select_action_id in number,
203 p_order_number in number,
204 p_rank in number,
205 p_include_control_group in varchar2,
206 x_msg_count OUT NOCOPY number,
207 x_msg_data OUT NOCOPY varchar2,
208 x_return_status IN OUT NOCOPY VARCHAR2,
209 x_std_sql OUT NOCOPY varchar2 ,
210 x_include_sql OUT NOCOPY varchar2 );
211 PROCEDURE process_list
212 (p_action_used_by_id in number,
213 p_incl_object_id in number,
214 p_list_action_type in varchar2,
215 p_list_select_action_id in number,
216 p_order_number in number,
217 p_rank in number,
218 p_include_control_group in varchar2,
219 x_msg_count OUT NOCOPY number,
220 x_msg_data OUT NOCOPY varchar2,
221 x_return_status IN OUT NOCOPY VARCHAR2,
222 x_std_sql OUT NOCOPY varchar2 ,
223 x_include_sql OUT NOCOPY varchar2 );
224 PROCEDURE process_diwb
225 (p_action_used_by_id in number,
226 p_incl_object_id in number,
227 p_list_action_type in varchar2,
228 p_list_select_action_id in number,
229 p_order_number in number,
230 p_rank in number,
231 p_include_control_group in varchar2,
232 x_msg_count OUT NOCOPY number,
233 x_msg_data OUT NOCOPY varchar2,
234 x_return_status IN OUT NOCOPY VARCHAR2,
235 x_std_sql OUT NOCOPY varchar2 ,
236 x_include_sql OUT NOCOPY varchar2 );
237
238 PROCEDURE process_cell
239 (p_action_used_by_id in number,
240 p_incl_object_id in number,
241 p_list_action_type in varchar2,
242 p_list_select_action_id in number,
243 p_order_number in number,
244 p_rank in number,
245 p_include_control_group in varchar2,
246 x_msg_count OUT NOCOPY number,
247 x_msg_data OUT NOCOPY varchar2,
248 x_return_status IN OUT NOCOPY VARCHAR2,
249 x_std_sql OUT NOCOPY varchar2 ,
250 x_include_sql OUT NOCOPY varchar2 );
251 PROCEDURE process_sql
252 (p_action_used_by_id in number,
253 p_incl_object_id in number,
254 p_list_action_type in varchar2,
255 p_list_select_action_id in number,
256 p_order_number in number,
257 p_rank in number,
258 p_include_control_group in varchar2,
259 x_msg_count OUT NOCOPY number,
260 x_msg_data OUT NOCOPY varchar2,
261 x_return_status IN OUT NOCOPY VARCHAR2,
262 x_std_sql OUT NOCOPY varchar2 ,
263 x_include_sql OUT NOCOPY varchar2 );
264
265 PROCEDURE process_manual
266 (p_action_used_by_id in number,
267 p_incl_object_id in number,
268 p_list_action_type in varchar2,
269 p_list_select_action_id in number,
270 p_order_number in number,
271 p_rank in number,
272 p_include_control_group in varchar2,
273 x_msg_count OUT NOCOPY number,
274 x_msg_data OUT NOCOPY varchar2,
275 x_return_status IN OUT NOCOPY VARCHAR2,
276 x_std_sql OUT NOCOPY varchar2 ,
277 x_include_sql OUT NOCOPY varchar2 );
278
279 PROCEDURE process_standard
280 (p_action_used_by_id in number,
281 p_incl_object_id in number,
282 p_list_action_type in varchar2,
283 p_list_select_action_id in number,
284 p_order_number in number,
285 p_rank in number,
286 p_include_control_group in varchar2,
287 x_msg_count OUT NOCOPY number,
288 x_msg_data OUT NOCOPY varchar2,
289 x_return_status IN OUT NOCOPY VARCHAR2,
290 x_std_sql OUT NOCOPY varchar2 ,
291 x_include_sql OUT NOCOPY varchar2 );
292
293 TYPE sql_string IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
294 TYPE sql_string_4K IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
295 TYPE child_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
296 TYPE t_number is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
297 TYPE t_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
298
299 g_count NUMBER := 1;
300 /*
301 g_message_table sql_string;
302 g_message_table_null sql_string;
303 */
304 g_message_table sql_string_4K;
305 g_message_table_null sql_string_4K;
306 g_date t_date;
307 g_msg_tbl_opt ams_list_options_pvt.g_msg_tbl_type;
308
309 g_list_owner_user_id number := -1; -- Will store the list owner id for this list from list header table
310 g_user_id number := -1; -- Will store the user id in fnd_user table(will be taken from jtf_resource table).
311 g_log_level varchar2(200) := NULL; -- Initially set to HIGH. Will get value based on "FND: Message Level Threshold".
312
313 g_remote_list_gen VARCHAR2(1) := 'N';
314 g_database_link VARCHAR2(128);
315
316 PROCEDURE create_list
317 ( p_api_version IN NUMBER,
318 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
319 p_commit IN VARCHAR2 := FND_API.G_FALSE,
320 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
321 p_list_name in varchar2,
322 p_list_type in varchar2 := 'STANDARD',
323 p_owner_user_id in number,
324 p_sql_string in OUT NOCOPY varchar2,
325 p_primary_key in varchar2,
326 p_source_object_name in varchar2,
327 p_master_type in varchar2,
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_msg_count OUT NOCOPY NUMBER,
330 x_msg_data OUT NOCOPY VARCHAR2,
331 x_list_header_id OUT NOCOPY NUMBER ) ;
332
333 PROCEDURE insert_list_mapping_usage
334 (p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE,
335 p_source_type_code AMS_LIST_SRC_TYPES.SOURCE_TYPE_CODE%TYPE);
336 PROCEDURE GET_LIST_ENTRY_DATA
337 (p_list_header_id in number,
338 p_additional_where_condition in varchar2 default null,
339 x_return_status OUT NOCOPY varchar2 );
340 procedure Update_List_Dets(p_list_header_id IN NUMBER,
341 x_return_status OUT NOCOPY varchar2);
342 PROCEDURE create_import_list
343 ( p_api_version IN NUMBER,
344 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
345 p_commit IN VARCHAR2 := FND_API.G_FALSE,
349 x_return_status OUT NOCOPY VARCHAR2,
346 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
347 p_owner_user_id in number,
348 p_imp_list_header_id in number,
350 x_msg_count OUT NOCOPY NUMBER,
351 x_msg_data OUT NOCOPY VARCHAR2,
352 x_list_header_id OUT NOCOPY NUMBER ,
353 p_list_name in VARCHAR2 DEFAULT NULL);
354
355 PROCEDURE validate_sql_string
356 (p_sql_string in sql_string
357 ,p_search_string in varchar2
358 ,p_comma_valid in varchar2
359 ,x_found OUT NOCOPY varchar2
360 ,x_position OUT NOCOPY number
361 ,x_counter OUT NOCOPY number
362 );
363 PROCEDURE get_master_types
364 (p_sql_string in sql_string,
365 p_start_length in number,
366 p_start_counter in number,
367 p_end_length in number,
368 p_end_counter in number,
369 x_master_type_id OUT NOCOPY number,
370 x_master_type OUT NOCOPY varchar2,
371 x_found OUT NOCOPY varchar2,
372 x_source_object_name OUT NOCOPY varchar2,
373 x_source_object_pk_field OUT NOCOPY varchar2);
374
375
376 PROCEDURE create_list_from_query
377 ( p_api_version IN NUMBER,
378 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
379 p_commit IN VARCHAR2 := FND_API.G_FALSE,
380 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
381 p_list_name in varchar2,
382 p_list_type in varchar2,
383 p_owner_user_id in number,
384 p_list_header_id in number,
385 p_sql_string_tbl in AMS_List_Query_PVT.sql_string_tbl ,
386 p_primary_key in varchar2,
387 p_source_object_name in varchar2,
388 p_master_type in varchar2,
389 x_return_status OUT NOCOPY VARCHAR2,
390 x_msg_count OUT NOCOPY NUMBER,
391 x_msg_data OUT NOCOPY VARCHAR2
392 ) ;
393 PROCEDURE create_list_from_query
394 ( p_api_version IN NUMBER,
395 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
396 p_commit IN VARCHAR2 := FND_API.G_FALSE,
397 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
398 p_list_name in varchar2,
399 p_list_type in varchar2,
400 p_owner_user_id in number,
401 p_list_header_id in number,
402 p_sql_string_tbl in AMS_List_Query_PVT.sql_string_tbl ,
403 p_primary_key in varchar2,
404 p_source_object_name in varchar2,
405 p_master_type in varchar2,
406 p_query_param in AMS_List_Query_PVT.sql_string_tbl ,
407 x_return_status OUT NOCOPY VARCHAR2,
408 x_msg_count OUT NOCOPY NUMBER,
409 x_msg_data OUT NOCOPY VARCHAR2
410 ) ;
411 PROCEDURE get_child_types (p_sql_string in sql_string,
412 p_start_length in number,
413 p_start_counter in number,
414 p_end_length in number,
415 p_end_counter in number,
416 p_master_type_id in number,
417 x_child_types OUT NOCOPY child_type,
418 x_found OUT NOCOPY varchar2 ) ;
419
420 PROCEDURE GENERATE_TARGET_GROUP
421 ( p_api_version IN NUMBER,
422 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
423 p_commit IN VARCHAR2 := FND_API.G_FALSE,
424 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
425 p_list_header_id IN NUMBER,
426 x_return_status OUT NOCOPY VARCHAR2,
427 x_msg_count OUT NOCOPY NUMBER,
428 x_msg_data OUT NOCOPY VARCHAR2) ;
429
430 PROCEDURE Execute_Remote_Dedupe_List
431 (p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE
432 ,p_enable_word_replacement_flag AMS_LIST_HEADERS_ALL.ENABLE_WORD_REPLACEMENT_FLAG%TYPE
433 ,p_send_to_log VARCHAR2 := 'N'
434 ,p_object_name VARCHAR2 := 'AMS_LIST_ENTRIES'
435 );
436
437 PROCEDURE migrate_lists_from_remote(
438 Errbuf OUT NOCOPY VARCHAR2,
439 Retcode OUT NOCOPY VARCHAR2,
440 p_list_header_id NUMBER
441 );
442
443
444 PROCEDURE migrate_word_replacements(
445 Errbuf OUT NOCOPY VARCHAR2,
446 Retcode OUT NOCOPY VARCHAR2,
447 dblink VARCHAR2
448 );
449
450 PROCEDURE UPDATE_FOR_TRAFFIC_COP( p_list_header_id in number ,
451 p_list_entry_id in t_number );
452
453
454
455 PROCEDURE calc_selection_running_total
456 (p_action_used_by_id in number,
457 p_action_used_by in varchar2 ,-- DEFAULT 'LIST',
458 p_log_flag in varchar2 ,-- DEFAULT 'Y',
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
464 (p_action_used_by_id in number,
461 x_msg_data OUT NOCOPY VARCHAR2);
462
463 PROCEDURE process_run_total_imph
465 p_incl_object_id in number,
466 p_list_action_type in varchar2,
467 p_list_select_action_id in number,
468 p_order_number in number,
469 p_rank in number,
470 p_include_control_group in varchar2,
471 x_msg_count OUT NOCOPY number,
472 x_msg_data OUT NOCOPY varchar2,
473 x_return_status IN OUT NOCOPY VARCHAR2,
474 x_std_sql OUT NOCOPY varchar2 ,
475 x_include_sql OUT NOCOPY varchar2
476 );
477
478 PROCEDURE process_run_total_list
479 (p_action_used_by_id in number,
480 p_incl_object_id in number,
481 p_list_action_type in varchar2,
482 p_list_select_action_id in number,
483 p_order_number in number,
484 p_rank in number,
485 p_include_control_group in varchar2,
486 x_msg_count OUT NOCOPY number,
487 x_msg_data OUT NOCOPY varchar2,
488 x_return_status IN OUT NOCOPY VARCHAR2,
489 x_std_sql OUT NOCOPY varchar2 ,
490 x_include_sql OUT NOCOPY varchar2
491 );
492
493 PROCEDURE process_run_total_sql (p_action_used_by_id in number,
494 p_incl_object_id in number,
495 p_list_action_type in varchar2,
496 p_list_select_action_id in number,
497 p_order_number in number,
498 p_rank in number,
499 p_include_control_group in varchar2,
500 x_msg_count OUT NOCOPY number,
501 x_msg_data OUT NOCOPY varchar2,
502 x_return_status IN OUT NOCOPY VARCHAR2,
503 x_std_sql OUT NOCOPY varchar2 ,
504 x_include_sql OUT NOCOPY varchar2
505 );
506
507 PROCEDURE process_run_total_cell
508 (p_action_used_by_id in number,
509 p_incl_object_id in number,
510 p_list_action_type in varchar2,
511 p_list_select_action_id in number,
512 p_order_number in number,
513 p_rank in number,
514 p_include_control_group in varchar2,
515 x_msg_count OUT NOCOPY number,
516 x_msg_data OUT NOCOPY varchar2,
517 x_return_status IN OUT NOCOPY VARCHAR2,
518 x_std_sql OUT NOCOPY varchar2 ,
519 x_include_sql OUT NOCOPY varchar2
520 );
521
522
523 PROCEDURE process_run_total_diwb (p_action_used_by_id in number,
524 p_incl_object_id in number,
525 p_list_action_type in varchar2,
526 p_list_select_action_id in number,
527 p_order_number in number,
528 p_rank in number,
529 p_include_control_group in varchar2,
530 x_msg_count OUT NOCOPY number,
531 x_msg_data OUT NOCOPY varchar2,
532 x_return_status IN OUT NOCOPY VARCHAR2,
533 x_std_sql OUT NOCOPY varchar2 ,
534 x_include_sql OUT NOCOPY varchar2
535 );
536
537 PROCEDURE tca_upload_process
538 (p_list_header_id in number,
539 p_log_flag in varchar2 ,-- DEFAULT 'Y',
540 x_return_status OUT NOCOPY VARCHAR2,
541 x_msg_count OUT NOCOPY NUMBER,
542 x_msg_data OUT NOCOPY VARCHAR2);
543
544 PROCEDURE calc_running_total (
545 Errbuf OUT NOCOPY VARCHAR2,
546 Retcode OUT NOCOPY VARCHAR2,
547 p_action_used_by_id in number);
548
549 PROCEDURE write_to_act_log(p_msg_data in VARCHAR2,
550 p_arc_log_used_by in VARCHAR2,
551 p_log_used_by_id in number,
552 p_level in varchar2 := 'LOW');
553 PROCEDURE logger;
554
555 procedure find_log_level(p_list_header_id number);
556
557 PROCEDURE remote_list_gen
558 (p_list_header_id in number,
559 x_return_status OUT NOCOPY VARCHAR2,
560 x_msg_count OUT NOCOPY NUMBER,
561 x_msg_data OUT NOCOPY VARCHAR2,
562 x_remote_gen OUT NOCOPY varchar2 );
563
564 PROCEDURE is_manual
565 (p_list_header_id in number,
566 x_return_status OUT NOCOPY VARCHAR2,
567 x_msg_count OUT NOCOPY NUMBER,
568 x_msg_data OUT NOCOPY VARCHAR2,
569 x_is_manual OUT NOCOPY varchar2 );
570
571 procedure upd_list_header_info(p_list_header_id in number,
572 x_msg_count out nocopy number,
573 x_msg_data out nocopy varchar2,
574 x_return_status out nocopy varchar2);
575
576 END AMS_ListGeneration_PKG;
[Home] [Help]