[Home] [Help]
57: AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
58: ------------------------------------------------------------------
59: --This Variable stores the primary key of the list header. --
60: ------------------------------------------------------------------
61: g_list_header_id ams_list_headers_all.list_header_id%type;
62: ----------------------------------------------------------------------------
63:
64: cursor g_initial_count is
65: select count(1)
119: l_debug_prof_level number := -1;
120:
121: cursor c_get_user_id(l_list_header_id number) is
122: select jtf.user_id
123: from jtf_rs_resource_extns jtf, ams_list_headers_all hd
124: where jtf.resource_id = hd.owner_user_id
125: and hd.list_header_id = l_list_header_id;
126:
127: begin
261:
262:
263: cursor c_gen_type is
264: select generation_type
265: from ams_list_headers_all
266: where list_header_id = g_list_header_id;
267:
268: cursor c_no_of_rows_reqd is
269: select distribution_pct,incl_object_id
271: where list_select_action_id = p_list_select_action_id;
272:
273: cursor c_no_of_rows_in_list is
274: select no_of_rows_active
275: from ams_list_headers_all
276: where list_header_id = l_incl_object_id;
277:
278: cursor c_import_type is
279: select decode(import_type,'B2C','PERSON_LIST','ORGANIZATION_CONTACT_LIST')
418: ctrl_req_resp_rate,
419: ctrl_conf_level,
420: ctrl_random_nth_row_selection,
421: ctrl_random_pct_row_selection
422: FROM ams_list_headers_all
423: WHERE list_header_id = p_list_header_id;
424: c1_rec c1%rowtype;
425:
426: BEGIN
529: FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
530: FND_API.G_FALSE,
531: l_msg_data,
532: l_msg_count);
533: UPDATE ams_list_headers_all
534: SET result_text = result_text||' '|| l_msg_data
535: WHERE list_header_id = g_list_header_id;
536: END LOOP;
537: COMMIT;
602: -- 1. Updates The No_of_Rows_In_List,
603: -- No_of_Rows_In_Ctrl_Group,
604: -- No_of_Rows_Duplicates,
605: -- Last_Generation_Success_Flag,
606: -- Main_Gen_End_Time columns on The Ams_List_Headers_All table.
607: -- gjoby Changed for hornet
608:
609: -- CALLED BY.
610: -- 1. Generate_List.
618:
619: --------------------------------------------------------------
620: --This Variable stores the result of cursor c_list_gen_type.--
621: --------------------------------------------------------------
622: l_generation_type AMS_LIST_HEADERS_ALL.GENERATION_TYPE%TYPE;
623:
624: -- updates the count of ams_list_entries and list selections
625: -- Calls update_list_action_dets for updating selection records
626: -- If the list criteria is not met then the list status is set back to draft
635: a.rank rank_col,
636: sum(decode(e.marked_as_duplicate_flag,'Y',1,0))
637: FROM ams_list_entries e,
638: ams_list_select_actions a
639: ,ams_list_headers_all b
640: WHERE e.list_header_id = p_list_header_id
641: AND e.list_select_action_id = a.list_select_action_id
642: AND a.arc_action_used_by = 'LIST'
643: AND a.action_used_by_id = p_list_header_id
704: and TCA_LOAD_STATUS = 'ERROR';
705:
706: l_list_type varchar2(100);
707: cursor c_list_type is
708: select list_type from ams_list_headers_all where list_header_id = p_list_header_id;
709:
710:
711: l_list_entry_action_count NUMBER;
712: l_list_select_action_id NUMBER;
737: x_msg_data varchar2(2000);
738:
739: /* Added by rrajesh on 08/02/04. Bugfix: 3799191*/
740: CURSOR C_GET_LIST_TYPE(p_list_header_id NUMBER) IS
741: select list_type from ams_list_headers_all
742: where list_header_id = p_list_header_id;
743: l_type varchar2(30);
744: /* End Bugfix: 3799191*/
745:
803: -- Start changes for migration
804: /*
805: SELECT nvl(no_of_rows_min_requested,0)
806: INTO l_min_rows
807: FROM ams_list_headers_all
808: WHERE list_header_id = p_list_header_id;
809:
810: if l_min_rows > l_no_of_rows_active then
811: l_new_status := 'DRAFT';
822: -- end if;
823:
824: -- End changes for migration
825:
826: update ams_list_headers_all
827: set no_of_rows_in_list = nvl(l_no_of_rows_in_list,0),
828: no_of_rows_active = nvl(l_no_of_rows_active,0),
829: no_of_rows_inactive = nvl(l_no_of_rows_inactive,0),
830: no_of_rows_in_ctrl_group = nvl(l_no_of_rows_in_ctrl_group,0),
863: the target group/list status should be failed */
864: --if l_no_of_rows_in_list = l_tca_error_recs then
865: if l_tca_error_recs > 0 then
866: /* End fix: 3799191 */
867: update ams_list_headers_all
868: set status_code = 'FAILED',
869: user_status_id = 311
870: WHERE list_header_id = p_list_header_id;
871: write_to_act_log('TCA fields not mapped for atleast one record. Marking the status as FAILED.','LIST', g_list_header_id,'HIGH');
1051: x_msg_data OUT NOCOPY varchar2,
1052: x_return_status OUT NOCOPY VARCHAR2) IS
1053: BEGIN
1054: write_to_act_log('Executing procedure initialize_list_header', 'LIST', g_list_header_id,'LOW');
1055: UPDATE ams_list_headers_all
1056: SET result_text = NULL,
1057: main_gen_start_time = SYSDATE,
1058: last_update_date = SYSDATE,
1059: main_gen_end_time = NULL
1196: -- 01/24/2001 GJOBY Modified for hornet
1197: ---------------------------------------------------------------------------
1198:
1199: PROCEDURE insert_list_mapping_usage
1200: (p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE,
1201: p_source_type_code AMS_LIST_SRC_TYPES.SOURCE_TYPE_CODE%TYPE) IS
1202:
1203: l_found NUMBER;
1204: BEGIN
1261: l_created_by NUMBER;
1262:
1263: CURSOR cur_get_created_by (x_list_header_id IN NUMBER) IS
1264: SELECT created_by
1265: FROM ams_list_headers_all
1266: WHERE list_header_id= x_list_header_id;
1267:
1268: BEGIN
1269: write_to_act_log('Executing process_imph since imported list has been included in list/target group selections.', 'LIST', g_list_header_id,'LOW');
2661: l_created_by NUMBER;
2662:
2663: CURSOR cur_get_created_by (x_list_header_id IN NUMBER) IS
2664: SELECT created_by
2665: FROM ams_list_headers_all
2666: WHERE list_header_id= x_list_header_id;
2667:
2668: begin
2669: WRITE_TO_ACT_LOG('Execution of procedure form_sql_statement started' || p_master_type, 'LIST', g_list_header_id,'LOW');
3117: SELECT a.list_source_type_id,
3118: a.source_type_code,
3119: a.source_object_name,
3120: a.source_object_pk_field
3121: FROM ams_list_src_types a,ams_list_headers_all b
3122: WHERE a.master_source_type_flag = 'Y'
3123: and a.source_type_code = b.list_source_type
3124: and b.list_header_id = g_list_header_id
3125: AND a.list_source_type in ('ANALYTICS', 'TARGET');
3467:
3468: cursor cur_primary_key_sql is
3469: SELECT lc.SOURCE_OBJECT_NAME, lc.SOURCE_OBJECT_PK_FIELD
3470: FROM ams_list_queries_all lq,
3471: ams_list_headers_all lh,
3472: ams_list_src_types lc
3473: WHERE lq.list_query_id = p_incl_object_id
3474: and lq.ARC_ACT_LIST_QUERY_USED_BY = 'LIST'
3475: and lq.ACT_LIST_QUERY_USED_BY_ID = lh.list_header_id
3639: exit when cur_diwb%notfound ;
3640: --Added by bmuthukr for bug 3944161.
3641: if instr(l_sql_string(l_counter), 'ORDER BY') > 0 or instr(l_sql_string(l_counter), 'GROUP BY') > 0 then
3642: write_to_act_log('Workbook sql has ORDER BY or GROUP BY clause. Aborting list/target group generation.', 'LIST', g_list_header_id,'HIGH');
3643: update ams_list_headers_all
3644: set last_generation_success_flag = 'N',
3645: status_code = 'FAILED',
3646: user_status_id = 311,
3647: status_date = sysdate,
3844: l_string VARCHAR2(32767);
3845:
3846: cursor c1 is
3847: select generation_type
3848: from ams_list_headers_all
3849: where list_header_id = l_list_header_id;
3850: l_generation_type varchar2(60);
3851: l_PARAMETERIZED_FLAG varchar2(1) := 'N';
3852: TYPE table_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
4031:
4032: if nvl(l_tot_params,0) > 100 then
4033: write_to_act_log('Number of parameters used exceeds 100. Aborting list generation process. Please redefine your criteria and restrict it to 100.',
4034: 'LIST',g_list_header_id,'HIGH');
4035: UPDATE ams_list_headers_all
4036: SET last_generation_success_flag = 'N',
4037: status_code = 'FAILED',
4038: user_status_id = 311,
4039: status_date = sysdate,
4054: p_arc_log_used_by => 'LIST',
4055: p_log_used_by_id => g_list_header_id,
4056: p_level => 'HIGH');
4057:
4058: UPDATE ams_list_headers_all
4059: SET last_generation_success_flag = 'N',
4060: status_code = 'FAILED',
4061: user_status_id = 311,
4062: status_date = sysdate,
4095:
4096: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
4097: write_to_act_log('Error when executing process_'||p_action_rec.arc_incl_object_from, 'LIST', g_list_header_id,'HIGH');
4098: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
4099: UPDATE ams_list_headers_all
4100: SET last_generation_success_flag = 'N',
4101: status_code = 'FAILED',
4102: user_status_id = 311,
4103: status_date = sysdate,
4317: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
4318: write_to_act_log('Error while executing remote procedure for generating list', 'LIST', g_list_header_id,'HIGH');
4319: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
4320: --Added for bug 4577528 by bmuthukr.
4321: update ams_list_headers_all
4322: set last_generation_success_flag = 'N',
4323: status_code = 'FAILED',
4324: user_status_id = 311,
4325: status_date = sysdate,
4499: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
4500: write_to_act_log('Error while generating list in remote instance.', 'LIST', g_list_header_id,'HIGH');
4501: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
4502: --Added for bug 4577528 by bmuthukr.
4503: update ams_list_headers_all
4504: set last_generation_success_flag = 'N',
4505: status_code = 'FAILED',
4506: user_status_id = 311,
4507: status_date = sysdate,
4596: -- 4. The set of distinct entries in The AMS_LIST_TMP_ENTRIES table
4597: -- have the SAVE_ROW_FLAG updated to 'Y'.
4598: -- Rows with this value are then inserted into the AMS_LIST_ENTRIES table.
4599: -- 5. if less entries exist in the AMS_LIST_ENTRIES_TABLE than specifed
4600: -- in the AMS_LIST_HEADERS_ALL
4601: -- .NO_OF_ROWS_MIN_REQUESTED column then an error is reported.
4602:
4603:
4604: -- CALLED BY.
4621: l_created_by NUMBER;
4622:
4623: CURSOR cur_get_created_by (x_list_header_id IN NUMBER) IS
4624: SELECT created_by
4625: FROM ams_list_headers_all
4626: WHERE list_header_id= x_list_header_id;
4627:
4628: BEGIN
4629: write_to_act_log('Executing procedure create_list_entries', 'LIST', g_list_header_id,'LOW');
4702: 'Y',
4703: 1
4704: from ams_list_select_actions s,
4705: ams_list_tmp_entries t,
4706: ams_list_headers_all h
4707: where h.list_header_id = p_list_header_id
4708: and t.list_header_id = h.list_header_id
4709: and t.list_select_action_id = s.list_select_action_id
4710: );
4713: into l_entry_count
4714: from ams_list_entries
4715: where list_header_id = p_list_header_id;
4716:
4717: update ams_list_headers_all
4718: set no_of_rows_in_list = l_entry_count,
4719: last_update_date = sysdate
4720: where list_header_id = p_list_header_id;
4721:
6141: cursor c1 is
6142: select imp.import_type
6143: from ams_imp_list_headers_all imp,
6144: ams_list_select_actions ail,
6145: ams_list_headers_all alh
6146: where alh.list_header_id = p_list_header_id
6147: and alh.list_header_id = ail.action_used_by_id
6148: and ail.arc_action_used_by = 'LIST'
6149: and ail.arc_incl_object_from = 'IMPH'
6383: --will be values in the c.sub_source_type_pk_column and source_type_code--
6384: --fields. --
6385: --------------------------------------------------------------------------
6386: CURSOR C_MAPPING_TYPES_USED(p_list_header_id
6387: AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE)
6388: IS SELECT a.list_source_type_id,
6389: a.source_type_code,
6390: a.source_object_name,
6391: a.source_object_pk_field,
6768: --gjoby check if l_no_of_duplicates required
6769: l_no_of_duplicates NUMBER := 0;
6770:
6771: -- Two records are required for init rec and complete rec
6772: -- Table ams_list_headers_all_tl and ams_list_headers_all
6773: l_listheader_rec ams_listheader_pvt.list_header_rec_type;
6774: l_tmp_listheader_rec ams_listheader_pvt.list_header_rec_type;
6775:
6776: -- Two records are required for init rec and complete rec
6779: l_tmp_listaction_rec ams_listaction_pvt.action_rec_type;
6780: l_no_of_rows_in_list number ;
6781: cursor c_get_rows (c_list_header_id in number ) is
6782: select no_of_rows_in_list
6783: from ams_list_headers_all
6784: where list_header_id = c_list_header_id ;
6785: l_error_position varchar2(100);
6786:
6787: cursor c_remote_list is
6785: l_error_position varchar2(100);
6786:
6787: cursor c_remote_list is
6788: select nvl(stypes.remote_flag,'N') ,database_link
6789: from ams_list_src_types stypes, ams_list_headers_all list
6790: where list.list_source_type = stypes.source_type_code
6791: and list_header_id = p_list_header_id;
6792:
6793: l_list_selection varchar2(1);
6795: l_no_of_rows number := 0;
6796:
6797: cursor c_check_gen_mode is
6798: select nvl(no_of_rows_in_list ,0)
6799: from ams_list_headers_all
6800: where list_header_id = p_list_header_id;
6801:
6802: cursor c_list_selection is
6803: select 'Y' from ams_list_select_actions
6805: and arc_action_used_by = 'LIST'
6806: and arc_incl_object_from in ('CELL','DIWB','SQL');
6807:
6808: cursor c_only_list_selection is
6809: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
6810: where act.action_used_by_id = p_list_header_id
6811: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
6812: and act.INCL_OBJECT_ID = head.list_header_id
6813: and head.status_code = 'AVAILABLE'
6842: -- check whether datasource is enabled.
6843: cursor c_check_datasource(c_list_header_id NUMBER) is
6844: SELECT a.enabled_flag
6845: FROM ams_list_src_types a,
6846: ams_list_headers_all b
6847: WHERE a.source_type_code = b.list_source_type
6848: AND b.list_header_id = c_list_header_id;
6849:
6850: l_ds_enabled_flag VARCHAR2(1);
6850: l_ds_enabled_flag VARCHAR2(1);
6851: -- SOLIN, end
6852:
6853: cursor c_master_ds_fields_mapped is
6854: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
6855: where hd.list_header_id = p_list_header_id
6856: and hd.LIST_SOURCE_TYPE = ty.source_type_code
6857: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
6858: and fd.FIELD_COLUMN_NAME is NOT NULL;
6857: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
6858: and fd.FIELD_COLUMN_NAME is NOT NULL;
6859:
6860: cursor c_child_ds_fields_mapped is
6861: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty,
6862: ams_list_src_type_assocs ats
6863: where hd.list_header_id = p_list_header_id
6864: and hd.LIST_SOURCE_TYPE = ty.source_type_code
6865: and ty.list_source_type_id = ats.master_source_type_id
6871: SELECT min(master_child.field_column_name1) ,count(master_child.field_column_name) from
6872: (
6873: SELECT d.field_column_name field_column_name1,d.field_column_name
6874: FROM ams_list_src_types a,
6875: ams_list_headers_all b,
6876: ams_list_src_fields d
6877: WHERE a.source_type_code = b.list_source_type
6878: and b.list_header_id = p_list_header_id
6879: and d.list_source_type_id = a.list_source_type_id
6880: and d.USED_IN_LIST_ENTRIES = 'Y'
6881: union all
6882: SELECT d.field_column_name field_column_name1,d.field_column_name
6883: FROM ams_list_src_types a,
6884: ams_list_headers_all b,
6885: ams_list_src_fields d,
6886: ams_list_src_type_assocs e
6887: WHERE a.source_type_code = b.list_source_type
6888: and b.list_header_id = p_list_header_id
6900:
6901: cursor c_get_dup_mapping(p_col_name in varchar2) is
6902: SELECT d.source_column_name, d.field_column_name , d.de_list_source_type_code stc
6903: FROM ams_list_src_types a,
6904: ams_list_headers_all b,
6905: ams_list_src_fields d
6906: WHERE a.source_type_code = b.list_source_type
6907: and b.list_header_id = g_list_header_id
6908: and d.list_source_type_id = a.list_source_type_id
6910: and d.USED_IN_LIST_ENTRIES = 'Y'
6911: union all
6912: SELECT d.source_column_name, d.field_column_name, d.de_list_source_type_code stc
6913: FROM ams_list_src_types a,
6914: ams_list_headers_all b,
6915: ams_list_src_fields d,
6916: ams_list_src_type_assocs e
6917: WHERE a.source_type_code = b.list_source_type
6918: and b.list_header_id = g_list_header_id
6943: GENERATION_TYPE,
6944: OWNER_USER_ID,
6945: ROW_SELECTION_TYPE,
6946: NO_OF_ROWS_MAX_REQUESTED
6947: FROM AMS_LIST_HEADERS_ALL
6948: WHERE LIST_HEADER_ID = P_LIST_HEADER_ID;
6949:
6950: CURSOR C11(P_LIST_HEADER_ID NUMBER,
6951: P_ACTION VARCHAR2) IS
7042: p_msg_data => 'Aborting the List generation process. The datasource for this list is not enabled. Contact your administrator to enable the datasource, and generate the list again.',
7043: p_arc_log_used_by => 'LIST',
7044: p_log_used_by_id => p_list_header_id,
7045: p_level => 'HIGH');
7046: UPDATE ams_list_headers_all
7047: SET last_generation_success_flag = 'N',
7048: status_code = 'FAILED',
7049: user_status_id = 311,
7050: status_date = sysdate,
7077: p_arc_log_used_by => 'LIST',
7078: p_log_used_by_id => p_list_header_id,
7079: p_level =>'HIGH');
7080:
7081: UPDATE ams_list_headers_all
7082: SET last_generation_success_flag = 'N',
7083: status_code = 'FAILED',
7084: user_status_id = 311,
7085: status_date = sysdate,
7121: exit when c_get_dup_mapping%notfound;
7122: write_to_Act_log('Data Source Name :- '||l_ds_name||' '||' Source Column :- '||l_source_col_name||' '||' List Entries Col :- '||l_field_col_name,'LIST',p_list_header_id,'HIGH');
7123: end loop;
7124: --
7125: UPDATE ams_list_headers_all
7126: SET last_generation_success_flag = 'N',
7127: status_code = 'FAILED',
7128: user_status_id = 311,
7129: status_date = sysdate,
7259: close c_check_gen_mode;
7260:
7261: if l_no_of_rows = 0 then
7262: write_to_act_log('No entries in list entries table. Unable to generate list in update mode. Pls generate in full refresh/append mode.','LIST',g_list_header_id,'HIGH');
7263: UPDATE ams_list_headers_all
7264: SET last_generation_success_flag = 'N',
7265: status_code = 'FAILED',
7266: ctrl_status_code = 'DRAFT',
7267: user_status_id = 311,
7561:
7562: if g_remote_list = 'Y' then
7563: write_to_act_log('Updating the list header info in the remote instance.', 'LIST', p_list_header_id,'HIGH');
7564: write_to_act_log('Deleting the existing ist header record deleted in remote instance.', 'LIST', p_list_header_id,'LOW');
7565: --execute immediate 'begin Delete from ams_list_headers_all'||'@'||g_database_link||' where list_header_id = :1 ; end;' using p_list_header_id;
7566:
7567: /*********** added by savio for remote bug 3764343 **************************/
7568:
7569: /* open c10(p_list_header_id);
7734: -- calling logging program
7735: write_to_act_log('Error in generating list while executing procedure generate_list '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
7736:
7737: --
7738: UPDATE ams_list_headers_all
7739: SET last_generation_success_flag = 'N',
7740: status_code = 'FAILED',
7741: user_status_id = 311,
7742: status_date = sysdate,
7754: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7755: -- calling logging program
7756: write_to_act_log('Error in generating list while executing procedure generate_list '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
7757: --
7758: UPDATE ams_list_headers_all
7759: SET last_generation_success_flag = 'N',
7760: status_code = 'FAILED',
7761: user_status_id = 311,
7762: last_update_date = sysdate,
7776: WHEN AMS_LISTGENERATION_UTIL_PKG.cancelListGen THEN
7777: write_to_act_log('In GENERATE_LIST: User cancel list gen action detected. ' ,'LIST',g_list_header_id,'HIGH');
7778: write_to_act_log('List Generation Stopped successfully.','LIST',g_list_header_id,'HIGH');
7779:
7780: UPDATE ams_list_headers_all
7781: SET last_generation_success_flag = 'N',
7782: status_code = 'DRAFT',
7783: user_status_id = 300,
7784: last_update_date = sysdate,
7807: WHEN OTHERS THEN
7808: -- calling logging program
7809: write_to_act_log('Error in generating list while executing procedure generate_list '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
7810: --
7811: UPDATE ams_list_headers_all
7812: SET last_generation_success_flag = 'N',
7813: status_code = 'FAILED',
7814: user_status_id = 311,
7815: last_update_date = sysdate,
8668: l_update_sql VARCHAR2(32767);
8669: l_list_header_id number ;
8670: cursor c1 is
8671: select generation_type
8672: from ams_list_headers_all
8673: where list_header_id = l_list_header_id;
8674: l_generation_type varchar2(60);
8675: l_PARAMETERIZED_FLAG varchar2(1) := 'N';
8676: TYPE table_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
8719: l_order_number number := 0;
8720:
8721: l_repeat_tg varchar2(1);
8722: CURSOR c_repeat_tg is
8723: select 'Y' from ams_campaign_schedules_b b, ams_list_headers_all h
8724: where h.list_header_id = g_list_header_id
8725: and h.LIST_USED_BY_ID = b.schedule_id
8726: and b.orig_csch_id is not null;
8727:
8793: if nvl(l_tot_params,0) > 100 then
8794: write_to_act_log('Numbers of parameters exceed 100. Aborting list generation process. Please redefine your criteria and restrict it to 100.',
8795: 'LIST',g_list_header_id,'HIGH');
8796:
8797: UPDATE ams_list_headers_all
8798: SET last_generation_success_flag = 'N',
8799: status_code = 'FAILED',
8800: ctrl_status_code = 'DRAFT',
8801: user_status_id = 311,
8818: p_arc_log_used_by => 'LIST',
8819: p_log_used_by_id => g_list_header_id,
8820: p_level => 'HIGH');
8821:
8822: UPDATE ams_list_headers_all
8823: SET last_generation_success_flag = 'N',
8824: status_code = 'FAILED',
8825: user_status_id = 311,
8826: status_date = sysdate,
8890: OUT l_include_sql;
8891: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
8892: write_to_act_log('Error when executing process_'||l_action_dets_rec.list_act_type, 'LIST', g_list_header_id,'HIGH');
8893: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
8894: UPDATE ams_list_headers_all
8895: SET last_generation_success_flag = 'N',
8896: status_code = 'FAILED',
8897: user_status_id = 311,
8898: status_date = sysdate,
9111: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
9112: write_to_act_log('Error while executing remote procedure for generating target group', 'LIST', g_list_header_id,'HIGH');
9113: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
9114: --Added for bug 4577528 by bmuthukr.
9115: update ams_list_headers_all
9116: set last_generation_success_flag = 'N',
9117: status_code = 'FAILED',
9118: user_status_id = 311,
9119: status_date = sysdate,
9305: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
9306: write_to_act_log('Error while generating target group in remote instance.', 'LIST', g_list_header_id,'HIGH');
9307: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
9308: --Added for bug 4577528 by bmuthukr.
9309: update ams_list_headers_all
9310: set last_generation_success_flag = 'N',
9311: status_code = 'FAILED',
9312: user_status_id = 311,
9313: status_date = sysdate,
9406: --gjoby check if l_no_of_duplicates required
9407: l_no_of_duplicates NUMBER := 0;
9408:
9409: -- Two records are required for init rec and complete rec
9410: -- Table ams_list_headers_all_tl and ams_list_headers_all
9411: l_listheader_rec ams_listheader_pvt.list_header_rec_type;
9412: l_tmp_listheader_rec ams_listheader_pvt.list_header_rec_type;
9413:
9414: -- Two records are required for init rec and complete rec
9417: l_tmp_listaction_rec ams_listaction_pvt.action_rec_type;
9418: l_no_of_rows_in_list number ;
9419: cursor c_get_rows (c_list_header_id in number ) is
9420: select no_of_rows_in_list
9421: from ams_list_headers_all
9422: where list_header_id = c_list_header_id ;
9423: l_error_position varchar2(100);
9424:
9425:
9424:
9425:
9426: cursor c_remote_list is
9427: select nvl(stypes.remote_flag,'N') ,database_link
9428: from ams_list_src_types stypes, ams_list_headers_all list
9429: where list.list_source_type = stypes.source_type_code
9430: and list_header_id = p_list_header_id;
9431:
9432: l_list_selection varchar2(1);
9437: and arc_action_used_by = 'LIST'
9438: and arc_incl_object_from in ('CELL','DIWB','SQL');
9439:
9440: cursor c_only_list_selection is
9441: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
9442: where act.action_used_by_id = p_list_header_id
9443: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
9444: and act.INCL_OBJECT_ID = head.list_header_id
9445: and head.status_code = 'AVAILABLE'
9471: l_list_rule_id number := 0;
9472: l_hd_status varchar(60);
9473: l_entry_count number;
9474: cursor c_hd_status is
9475: select STATUS_CODE from ams_list_headers_all
9476: where list_header_id = p_list_header_id;
9477:
9478: cursor c_entry_count is
9479: select count(1) from ams_list_entries
9481:
9482: l_list_field_mapped varchar2(1);
9483:
9484: cursor c_master_ds_fields_mapped is
9485: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
9486: where hd.list_header_id = p_list_header_id
9487: and hd.LIST_SOURCE_TYPE = ty.source_type_code
9488: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
9489: and fd.FIELD_COLUMN_NAME is NOT NULL;
9488: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
9489: and fd.FIELD_COLUMN_NAME is NOT NULL;
9490:
9491: cursor c_child_ds_fields_mapped is
9492: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty,
9493: ams_list_src_type_assocs ats
9494: where hd.list_header_id = p_list_header_id
9495: and hd.LIST_SOURCE_TYPE = ty.source_type_code
9496: and ty.list_source_type_id = ats.master_source_type_id
9502: l_no_of_rows number := 0;
9503:
9504: cursor c_check_gen_mode is
9505: select nvl(no_of_rows_in_list ,0)
9506: from ams_list_headers_all
9507: where list_header_id = g_list_header_id;
9508:
9509: -- SOLIN, bug 4410333
9510: -- check whether datasource is enabled.
9510: -- check whether datasource is enabled.
9511: cursor c_check_datasource(c_list_header_id NUMBER) is
9512: SELECT a.enabled_flag
9513: FROM ams_list_src_types a,
9514: ams_list_headers_all b
9515: WHERE a.source_type_code = b.list_source_type
9516: AND b.list_header_id = c_list_header_id;
9517:
9518: l_ds_enabled_flag VARCHAR2(1);
9518: l_ds_enabled_flag VARCHAR2(1);
9519: -- SOLIN, end
9520:
9521: cursor c_master_ds_tca_mapped is
9522: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
9523: where hd.list_header_id = p_list_header_id
9524: and hd.LIST_SOURCE_TYPE = ty.source_type_code
9525: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
9526: and fd.tca_column_id is NOT NULL;
9525: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
9526: and fd.tca_column_id is NOT NULL;
9527:
9528: cursor c_child_ds_tca_mapped is
9529: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty,
9530: ams_list_src_type_assocs ats
9531: where hd.list_header_id = p_list_header_id
9532: and hd.LIST_SOURCE_TYPE = ty.source_type_code
9533: and ty.list_source_type_id = ats.master_source_type_id
9538: SELECT min(master_child.field_column_name1) ,count(master_child.field_column_name) from
9539: (
9540: SELECT d.field_column_name field_column_name1,d.field_column_name
9541: FROM ams_list_src_types a,
9542: ams_list_headers_all b,
9543: ams_list_src_fields d
9544: WHERE a.source_type_code = b.list_source_type
9545: and b.list_header_id = p_list_header_id
9546: and d.list_source_type_id = a.list_source_type_id
9547: and d.USED_IN_LIST_ENTRIES = 'Y'
9548: union all
9549: SELECT d.field_column_name field_column_name1,d.field_column_name
9550: FROM ams_list_src_types a,
9551: ams_list_headers_all b,
9552: ams_list_src_fields d,
9553: ams_list_src_type_assocs e
9554: WHERE a.source_type_code = b.list_source_type
9555: and b.list_header_id = p_list_header_id
9568:
9569: cursor c_get_dup_mapping(p_col_name in varchar2) is
9570: SELECT d.source_column_name, d.field_column_name , d.de_list_source_type_code stc
9571: FROM ams_list_src_types a,
9572: ams_list_headers_all b,
9573: ams_list_src_fields d
9574: WHERE a.source_type_code = b.list_source_type
9575: and b.list_header_id = g_list_header_id
9576: and d.list_source_type_id = a.list_source_type_id
9578: and d.USED_IN_LIST_ENTRIES = 'Y'
9579: union all
9580: SELECT d.source_column_name, d.field_column_name, d.de_list_source_type_code stc
9581: FROM ams_list_src_types a,
9582: ams_list_headers_all b,
9583: ams_list_src_fields d,
9584: ams_list_src_type_assocs e
9585: WHERE a.source_type_code = b.list_source_type
9586: and b.list_header_id = g_list_header_id
9590: and d.USED_IN_LIST_ENTRIES = 'Y';
9591: --
9592: cursor c_check_supp is
9593: select nvl(apply_suppression_flag,'N')
9594: from ams_list_headers_all
9595: where list_header_id = p_list_header_id;
9596:
9597: l_supp_flag varchar2(1) := 'N';
9598:
9630: p_msg_data => 'Aborting the List generation process. The datasource for this list is not enabled. Contact your administrator to enable the datasource, and generate the list again.',
9631: p_arc_log_used_by => 'LIST',
9632: p_log_used_by_id => p_list_header_id,
9633: p_level => 'HIGH');
9634: UPDATE ams_list_headers_all
9635: SET last_generation_success_flag = 'N',
9636: status_code = 'FAILED',
9637: user_status_id = 311,
9638: status_date = sysdate,
9671: p_arc_log_used_by => 'LIST',
9672: p_log_used_by_id => p_list_header_id,
9673: p_level =>'HIGH');
9674:
9675: UPDATE ams_list_headers_all
9676: SET last_generation_success_flag = 'N',
9677: status_code = 'FAILED',
9678: user_status_id = 311,
9679: status_date = sysdate,
9719: exit when c_get_dup_mapping%notfound;
9720: write_to_Act_log('Data Source Name :- '||l_ds_name||' '||' Source Column :- '||l_source_col_name||' '||' List Entries Col :- '||l_field_col_name,'LIST',p_list_header_id,'HIGH');
9721: end loop;
9722: --
9723: UPDATE ams_list_headers_all
9724: SET last_generation_success_flag = 'N',
9725: status_code = 'FAILED',
9726: user_status_id = 311,
9727: status_date = sysdate,
9853: close c_check_gen_mode;
9854:
9855: if l_no_of_rows = 0 then
9856: write_to_act_log('No entries in list entries table. Unable to generate target group in update mode. Pls generate in full refresh/append mode.','LIST',g_list_header_id,'HIGH');
9857: UPDATE ams_list_headers_all
9858: SET last_generation_success_flag = 'N',
9859: status_code = 'FAILED',
9860: ctrl_status_code = 'DRAFT',
9861: user_status_id = 311,
10165: write_to_act_log('Error in executing remote procedure for deleting target group entries', 'LIST', g_list_header_id,'HIGH');
10166: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
10167: end if;
10168:
10169: UPDATE ams_list_headers_all
10170: SET last_generation_success_flag = 'N',
10171: NO_OF_ROWS_ACTIVE = null,
10172: NO_OF_ROWS_IN_LIST = null,
10173: status_code = 'FAILED',
10204: open c_hd_status;
10205: fetch c_hd_status into l_hd_status;
10206: close c_hd_status;
10207: if l_hd_status = 'AVAILABLE' then
10208: UPDATE ams_list_headers_all
10209: SET status_code = 'GENERATING',
10210: user_status_id = 302
10211: WHERE list_header_id = p_list_header_id;
10212: -- Added for cancel list gen as it prevents parallel update- Raghu
10285: WHEN FND_API.G_EXC_ERROR THEN
10286: -- calling logging program
10287: write_to_act_log('Error while executing procedure generate_target_group for generating target group.', 'LIST', g_list_header_id,'HIGH');
10288:
10289: UPDATE ams_list_headers_all
10290: SET last_generation_success_flag = 'N',
10291: status_code = 'FAILED',
10292: user_status_id = 311,
10293: status_date = sysdate,
10311: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
10312: -- calling logging program
10313: write_to_act_log('Error while executing procedure generate_target_group for generating target group.', 'LIST', g_list_header_id,'HIGH');
10314:
10315: UPDATE ams_list_headers_all
10316: SET last_generation_success_flag = 'N',
10317: status_code = 'FAILED',
10318: user_status_id = 311,
10319: last_update_date = sysdate,
10337: WHEN OTHERS THEN
10338: -- calling logging program
10339: write_to_act_log('Error while executing procedure generate_target_group for generating target group.', 'LIST', g_list_header_id,'HIGH');
10340:
10341: UPDATE ams_list_headers_all
10342: SET last_generation_success_flag = 'N',
10343: status_code = 'FAILED',
10344: user_status_id = 311,
10345: last_update_date = sysdate,
10364: END GENERATE_TARGET_GROUP;
10365:
10366: -- -----------------------------------------------------------------------
10367: PROCEDURE Execute_Remote_Dedupe_List
10368: (p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE
10369: ,p_enable_word_replacement_flag
10370: AMS_LIST_HEADERS_ALL.ENABLE_WORD_REPLACEMENT_FLAG%TYPE
10371: ,p_send_to_log VARCHAR2 := 'N'
10372: ,p_object_name VARCHAR2 := 'AMS_LIST_ENTRIES'
10366: -- -----------------------------------------------------------------------
10367: PROCEDURE Execute_Remote_Dedupe_List
10368: (p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE
10369: ,p_enable_word_replacement_flag
10370: AMS_LIST_HEADERS_ALL.ENABLE_WORD_REPLACEMENT_FLAG%TYPE
10371: ,p_send_to_log VARCHAR2 := 'N'
10372: ,p_object_name VARCHAR2 := 'AMS_LIST_ENTRIES'
10373: )
10374: IS
10394: -- perform a check to see if this list has been deduped already.
10395: CURSOR c_deduped_before (my_list_header_id IN NUMBER)
10396: IS
10397: SELECT last_deduped_by_user_id
10398: FROM ams_list_headers_all
10399: WHERE list_header_id = my_list_header_id;
10400:
10401: -- get a distinct list of merge keys for the list and a
10402: -- count of the occurance of each key
10422:
10423: l_fields VARCHAR2(10000);
10424: l_no_of_masters NUMBER := 0;
10425: l_list_rule_id ams_list_rules_all.list_rule_id%TYPE;
10426: l_last_dedupe_by ams_list_headers_all.last_deduped_by_user_id%TYPE;
10427: l_dedupe_key ams_list_entries.dedupe_key%TYPE;
10428: l_dedupe_key_count NUMBER;
10429: l_rank_count NUMBER;
10430: i BINARY_INTEGER := 1;
10693: END LOOP; --c_list_rules loop
10694:
10695: --recording who performed the deduplication and at what time.
10696: --recording the number of duplicates found.
10697: UPDATE ams_list_headers_all
10698: SET last_deduped_by_user_id = FND_GLOBAL.User_Id
10699: ,last_dedupe_date = SYSDATE
10700: ,no_of_rows_duplicates = l_total_dup_recs
10701: WHERE list_header_id = p_list_header_id;
10701: WHERE list_header_id = p_list_header_id;
10702:
10703: /* savio removing potential multiple update statement */
10704: /*
10705: -- UPDATE ams_list_headers_all
10706: -- SET no_of_rows_duplicates = l_total_dup_recs
10707: -- WHERE list_header_id = p_list_header_id;
10708: */
10709:
10723:
10724: l_list_header_id NUMBER;
10725: cursor c_migrate_list is
10726: select head.list_header_id
10727: from ams_list_headers_all head, ams_list_src_types stypes
10728: where head.status_code = 'AVAILABLE'
10729: and head.MIGRATION_DATE is NULL
10730: and head.list_source_type = stypes.source_type_code
10731: and stypes.remote_flag = 'Y';
10771:
10772:
10773: cursor c_dblink is
10774: select database_link
10775: from ams_list_src_types stypes, ams_list_headers_all list
10776: where list.list_source_type = stypes.source_type_code
10777: and list_header_id = p_list_header_id;
10778:
10779: begin
11588: Ams_Utility_Pvt.Write_Conc_log('End migrate_lists : '||to_char(p_list_header_id));
11589: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
11590: WHERE system_status_type = 'AMS_LIST_STATUS' AND
11591: system_status_code = 'AVAILABLE' and default_flag = 'Y';
11592: UPDATE ams_list_headers_all
11593: set status_code = 'AVAILABLE',
11594: user_status_id = l_user_status_id,
11595: migration_date = sysdate
11596: where list_header_id = p_list_header_id;
11610: RAISE FND_API.G_EXC_ERROR;
11611: END IF;
11612: EXCEPTION
11613: WHEN OTHERS THEN
11614: UPDATE ams_list_headers_all
11615: SET last_generation_success_flag = 'N',
11616: status_code = 'FAILED',
11617: user_status_id = 311,
11618: last_update_date = sysdate,
11748: into l_no_of_rows_active ,
11749: l_fatigued_records ;
11750: close c_count_list_entries;
11751:
11752: UPDATE ams_list_headers_all
11753: SET NO_OF_ROWS_FATIGUED = l_fatigued_records,
11754: NO_OF_ROWS_ACTIVE = l_no_of_rows_active
11755: WHERE list_header_id = p_list_header_id ;
11756: -- Added for cancel list gen as it prevents parallel update- Raghu
11872: l_update_sql VARCHAR2(32767);
11873: l_list_header_id number ;
11874: cursor c1 is
11875: select generation_type
11876: from ams_list_headers_all
11877: where list_header_id = l_list_header_id;
11878: l_generation_type varchar2(60);
11879: l_selection_results t_number;
11880: l_list_select_action t_number;
11891: l_last_generation_success_flag varchar2(1);
11892:
11893: cursor c_last_gen is
11894: select nvl(last_generation_success_flag,'N')
11895: from ams_list_headers_all
11896: where list_header_id = p_action_used_by_id;
11897:
11898: BEGIN
11899: g_list_header_id := p_action_used_by_id;
12653:
12654: cursor cur_primary_key_sql is
12655: SELECT lc.SOURCE_OBJECT_NAME, lc.SOURCE_OBJECT_PK_FIELD
12656: FROM ams_list_queries_all lq,
12657: ams_list_headers_all lh,
12658: ams_list_src_types lc
12659: WHERE lq.list_query_id = p_incl_object_id
12660: and lq.ARC_ACT_LIST_QUERY_USED_BY = 'LIST'
12661: and lq.ACT_LIST_QUERY_USED_BY_ID = lh.list_header_id
12989:
12990: cursor c_tca_columns is
12991: select flds.field_column_name , tca.column_name
12992: from ams_list_src_fields flds, AMS_DS_TCA_ENTITY_COLS tca,
12993: ams_list_headers_all hdr, ams_list_src_types typ
12994: where hdr.list_header_id = p_list_header_id
12995: and hdr.LIST_SOURCE_TYPE = typ.SOURCE_TYPE_CODE
12996: and typ.list_source_type_id = flds.list_source_type_id
12997: and flds.enabled_flag = 'Y'
12998: and used_in_list_entries = 'Y'
12999: and flds.tca_column_id = tca.ENTITY_COLUMN_ID;
13000:
13001: cursor c_data_type is
13002: select source_category from ams_list_src_types types, ams_list_headers_all head
13003: where head.list_header_id = p_list_header_id
13004: and head.list_source_type = types.source_type_code;
13005:
13006: l_prof VARCHAR2(50);
13728: -- ---------------------------TCA CALL ---------------------------------
13729:
13730: END LOOP; -- For c_list_entries
13731: close c_list_entries;
13732: update ams_list_headers_all
13733: set migration_date = sysdate
13734: where list_header_id = p_list_header_id;
13735:
13736: write_to_act_log('Executed procedure tca_upload_process', 'LIST', g_list_header_id,'LOW');
13810: and arc_action_used_by = 'LIST'
13811: and arc_incl_object_from in ('CELL','DIWB','SQL');
13812:
13813: cursor c_only_list_selection is
13814: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
13815: where act.action_used_by_id = p_list_header_id
13816: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
13817: and act.INCL_OBJECT_ID = head.list_header_id
13818: and head.status_code = 'AVAILABLE'
13882: x_is_manual OUT NOCOPY varchar2 ) is
13883:
13884: cursor c1 is
13885: select list_type
13886: from ams_list_headers_all
13887: where list_header_id = p_list_header_id;
13888:
13889: cursor c2 is
13890: select 1
13959: if nvl(x_is_manual,'N') = 'Y' then --Either if it is manual list or if any of the incl are of type emp list.
13960: open c2;
13961: fetch c2 into l_dummy;
13962: if c2%found then -- List already has enabled entries.. So making it as available.
13963: update ams_list_headers_all
13964: set status_code = 'AVAILABLE',
13965: user_status_id = 303,
13966: status_date = sysdate,
13967: last_update_date = sysdate
13966: status_date = sysdate,
13967: last_update_date = sysdate
13968: where list_header_id = p_list_header_id;
13969: else -- No enabled entries..So it will be in DRAFT status
13970: update ams_list_headers_all
13971: set status_code = 'DRAFT',
13972: user_status_id = 300,
13973: status_date = sysdate,
13974: last_update_date = sysdate
14012: cursor c_list_det is
14013: select stypes.database_link,
14014: list.remote_gen_flag,
14015: list.list_type
14016: from ams_list_src_types stypes, ams_list_headers_all list
14017: where list.list_source_type = stypes.source_type_code
14018: and list_header_id = p_list_header_id;
14019:
14020: cursor c_count_list_entries is
14055: OUT x_msg_data,
14056: OUT x_return_status;
14057: end if;
14058:
14059: update ams_list_headers_all
14060: set no_of_rows_in_list = nvl(l_no_of_rows_in_list,0),
14061: no_of_rows_active = nvl(l_no_of_rows_active,0),
14062: no_of_rows_inactive = nvl(l_no_of_rows_inactive,0),
14063: no_of_rows_manually_entered = nvl(l_no_of_rows_manually_entered,0)