[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;
4032:
4033: if nvl(l_tot_params,0) > 100 then
4034: write_to_act_log('Number of parameters used exceeds 100. Aborting list generation process. Please redefine your criteria and restrict it to 100.',
4035: 'LIST',g_list_header_id,'HIGH');
4036: UPDATE ams_list_headers_all
4037: SET last_generation_success_flag = 'N',
4038: status_code = 'FAILED',
4039: user_status_id = 311,
4040: status_date = sysdate,
4055: p_arc_log_used_by => 'LIST',
4056: p_log_used_by_id => g_list_header_id,
4057: p_level => 'HIGH');
4058:
4059: UPDATE ams_list_headers_all
4060: SET last_generation_success_flag = 'N',
4061: status_code = 'FAILED',
4062: user_status_id = 311,
4063: status_date = sysdate,
4096:
4097: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
4098: write_to_act_log('Error when executing process_'||p_action_rec.arc_incl_object_from, 'LIST', g_list_header_id,'HIGH');
4099: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
4100: UPDATE ams_list_headers_all
4101: SET last_generation_success_flag = 'N',
4102: status_code = 'FAILED',
4103: user_status_id = 311,
4104: status_date = sysdate,
4319: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
4320: write_to_act_log('Error while executing remote procedure for generating list', 'LIST', g_list_header_id,'HIGH');
4321: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
4322: --Added for bug 4577528 by bmuthukr.
4323: update ams_list_headers_all
4324: set last_generation_success_flag = 'N',
4325: status_code = 'FAILED',
4326: user_status_id = 311,
4327: status_date = sysdate,
4501: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
4502: write_to_act_log('Error while generating list in remote instance.', 'LIST', g_list_header_id,'HIGH');
4503: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
4504: --Added for bug 4577528 by bmuthukr.
4505: update ams_list_headers_all
4506: set last_generation_success_flag = 'N',
4507: status_code = 'FAILED',
4508: user_status_id = 311,
4509: status_date = sysdate,
4603: -- 4. The set of distinct entries in The AMS_LIST_TMP_ENTRIES table
4604: -- have the SAVE_ROW_FLAG updated to 'Y'.
4605: -- Rows with this value are then inserted into the AMS_LIST_ENTRIES table.
4606: -- 5. if less entries exist in the AMS_LIST_ENTRIES_TABLE than specifed
4607: -- in the AMS_LIST_HEADERS_ALL
4608: -- .NO_OF_ROWS_MIN_REQUESTED column then an error is reported.
4609:
4610:
4611: -- CALLED BY.
4628: l_created_by NUMBER;
4629:
4630: CURSOR cur_get_created_by (x_list_header_id IN NUMBER) IS
4631: SELECT created_by
4632: FROM ams_list_headers_all
4633: WHERE list_header_id= x_list_header_id;
4634:
4635: BEGIN
4636: write_to_act_log('Executing procedure create_list_entries', 'LIST', g_list_header_id,'LOW');
4709: 'Y',
4710: 1
4711: from ams_list_select_actions s,
4712: ams_list_tmp_entries t,
4713: ams_list_headers_all h
4714: where h.list_header_id = p_list_header_id
4715: and t.list_header_id = h.list_header_id
4716: and t.list_select_action_id = s.list_select_action_id
4717: );
4720: into l_entry_count
4721: from ams_list_entries
4722: where list_header_id = p_list_header_id;
4723:
4724: update ams_list_headers_all
4725: set no_of_rows_in_list = l_entry_count,
4726: last_update_date = sysdate
4727: where list_header_id = p_list_header_id;
4728:
6148: cursor c1 is
6149: select imp.import_type
6150: from ams_imp_list_headers_all imp,
6151: ams_list_select_actions ail,
6152: ams_list_headers_all alh
6153: where alh.list_header_id = p_list_header_id
6154: and alh.list_header_id = ail.action_used_by_id
6155: and ail.arc_action_used_by = 'LIST'
6156: and ail.arc_incl_object_from = 'IMPH'
6390: --will be values in the c.sub_source_type_pk_column and source_type_code--
6391: --fields. --
6392: --------------------------------------------------------------------------
6393: CURSOR C_MAPPING_TYPES_USED(p_list_header_id
6394: AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE)
6395: IS SELECT a.list_source_type_id,
6396: a.source_type_code,
6397: a.source_object_name,
6398: a.source_object_pk_field,
6825: --gjoby check if l_no_of_duplicates required
6826: l_no_of_duplicates NUMBER := 0;
6827:
6828: -- Two records are required for init rec and complete rec
6829: -- Table ams_list_headers_all_tl and ams_list_headers_all
6830: l_listheader_rec ams_listheader_pvt.list_header_rec_type;
6831: l_tmp_listheader_rec ams_listheader_pvt.list_header_rec_type;
6832:
6833: -- Two records are required for init rec and complete rec
6836: l_tmp_listaction_rec ams_listaction_pvt.action_rec_type;
6837: l_no_of_rows_in_list number ;
6838: cursor c_get_rows (c_list_header_id in number ) is
6839: select no_of_rows_in_list
6840: from ams_list_headers_all
6841: where list_header_id = c_list_header_id ;
6842: l_error_position varchar2(100);
6843:
6844: cursor c_remote_list is
6842: l_error_position varchar2(100);
6843:
6844: cursor c_remote_list is
6845: select nvl(stypes.remote_flag,'N') ,database_link
6846: from ams_list_src_types stypes, ams_list_headers_all list
6847: where list.list_source_type = stypes.source_type_code
6848: and list_header_id = p_list_header_id;
6849:
6850: l_list_selection varchar2(1);
6852: l_no_of_rows number := 0;
6853:
6854: cursor c_check_gen_mode is
6855: select nvl(no_of_rows_in_list ,0)
6856: from ams_list_headers_all
6857: where list_header_id = p_list_header_id;
6858:
6859: cursor c_list_selection is
6860: select 'Y' from ams_list_select_actions
6862: and arc_action_used_by = 'LIST'
6863: and arc_incl_object_from in ('CELL','DIWB','SQL');
6864:
6865: cursor c_only_list_selection is
6866: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
6867: where act.action_used_by_id = p_list_header_id
6868: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
6869: and act.INCL_OBJECT_ID = head.list_header_id
6870: and head.status_code = 'AVAILABLE'
6899: -- check whether datasource is enabled.
6900: cursor c_check_datasource(c_list_header_id NUMBER) is
6901: SELECT a.enabled_flag
6902: FROM ams_list_src_types a,
6903: ams_list_headers_all b
6904: WHERE a.source_type_code = b.list_source_type
6905: AND b.list_header_id = c_list_header_id;
6906:
6907: l_ds_enabled_flag VARCHAR2(1);
6907: l_ds_enabled_flag VARCHAR2(1);
6908: -- SOLIN, end
6909:
6910: cursor c_master_ds_fields_mapped is
6911: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
6912: where hd.list_header_id = p_list_header_id
6913: and hd.LIST_SOURCE_TYPE = ty.source_type_code
6914: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
6915: and fd.FIELD_COLUMN_NAME is NOT NULL;
6914: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
6915: and fd.FIELD_COLUMN_NAME is NOT NULL;
6916:
6917: cursor c_child_ds_fields_mapped is
6918: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty,
6919: ams_list_src_type_assocs ats
6920: where hd.list_header_id = p_list_header_id
6921: and hd.LIST_SOURCE_TYPE = ty.source_type_code
6922: and ty.list_source_type_id = ats.master_source_type_id
6928: SELECT min(master_child.field_column_name1) ,count(master_child.field_column_name) from
6929: (
6930: SELECT d.field_column_name field_column_name1,d.field_column_name
6931: FROM ams_list_src_types a,
6932: ams_list_headers_all b,
6933: ams_list_src_fields d
6934: WHERE a.source_type_code = b.list_source_type
6935: and b.list_header_id = p_list_header_id
6936: and d.list_source_type_id = a.list_source_type_id
6937: and d.USED_IN_LIST_ENTRIES = 'Y'
6938: union all
6939: SELECT d.field_column_name field_column_name1,d.field_column_name
6940: FROM ams_list_src_types a,
6941: ams_list_headers_all b,
6942: ams_list_src_fields d,
6943: ams_list_src_type_assocs e
6944: WHERE a.source_type_code = b.list_source_type
6945: and b.list_header_id = p_list_header_id
6957:
6958: cursor c_get_dup_mapping(p_col_name in varchar2) is
6959: SELECT d.source_column_name, d.field_column_name , d.de_list_source_type_code stc
6960: FROM ams_list_src_types a,
6961: ams_list_headers_all b,
6962: ams_list_src_fields d
6963: WHERE a.source_type_code = b.list_source_type
6964: and b.list_header_id = g_list_header_id
6965: and d.list_source_type_id = a.list_source_type_id
6967: and d.USED_IN_LIST_ENTRIES = 'Y'
6968: union all
6969: SELECT d.source_column_name, d.field_column_name, d.de_list_source_type_code stc
6970: FROM ams_list_src_types a,
6971: ams_list_headers_all b,
6972: ams_list_src_fields d,
6973: ams_list_src_type_assocs e
6974: WHERE a.source_type_code = b.list_source_type
6975: and b.list_header_id = g_list_header_id
7000: GENERATION_TYPE,
7001: OWNER_USER_ID,
7002: ROW_SELECTION_TYPE,
7003: NO_OF_ROWS_MAX_REQUESTED
7004: FROM AMS_LIST_HEADERS_ALL
7005: WHERE LIST_HEADER_ID = P_LIST_HEADER_ID;
7006:
7007: CURSOR C11(P_LIST_HEADER_ID NUMBER,
7008: P_ACTION VARCHAR2) IS
7035:
7036: CURSOR C_WAIT_LIST(P_LIST_HEADER_ID NUMBER) IS
7037: SELECT COUNT(1)
7038: FROM AMS_LIST_SELECT_ACTIONS ACT,
7039: AMS_LIST_HEADERS_ALL HEAD
7040: WHERE ACT.ACTION_USED_BY_ID = P_LIST_HEADER_ID
7041: AND ACT.ARC_INCL_OBJECT_FROM = 'LIST' AND ACT.ARC_ACTION_USED_BY = 'LIST'
7042: AND ACT.INCL_OBJECT_ID = HEAD.LIST_HEADER_ID
7043: AND HEAD.STATUS_CODE IN ('SCHEDULED','GENERATING')
7127: 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.',
7128: p_arc_log_used_by => 'LIST',
7129: p_log_used_by_id => p_list_header_id,
7130: p_level => 'HIGH');
7131: UPDATE ams_list_headers_all
7132: SET last_generation_success_flag = 'N',
7133: status_code = 'FAILED',
7134: user_status_id = 311,
7135: status_date = sysdate,
7162: p_arc_log_used_by => 'LIST',
7163: p_log_used_by_id => p_list_header_id,
7164: p_level =>'HIGH');
7165:
7166: UPDATE ams_list_headers_all
7167: SET last_generation_success_flag = 'N',
7168: status_code = 'FAILED',
7169: user_status_id = 311,
7170: status_date = sysdate,
7206: exit when c_get_dup_mapping%notfound;
7207: 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');
7208: end loop;
7209: --
7210: UPDATE ams_list_headers_all
7211: SET last_generation_success_flag = 'N',
7212: status_code = 'FAILED',
7213: user_status_id = 311,
7214: status_date = sysdate,
7344: close c_check_gen_mode;
7345:
7346: if l_no_of_rows = 0 then
7347: 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');
7348: UPDATE ams_list_headers_all
7349: SET last_generation_success_flag = 'N',
7350: status_code = 'FAILED',
7351: ctrl_status_code = 'DRAFT',
7352: user_status_id = 311,
7649:
7650: if g_remote_list = 'Y' then
7651: write_to_act_log('Updating the list header info in the remote instance.', 'LIST', p_list_header_id,'HIGH');
7652: write_to_act_log('Deleting the existing ist header record deleted in remote instance.', 'LIST', p_list_header_id,'LOW');
7653: --execute immediate 'begin Delete from ams_list_headers_all'||'@'||g_database_link||' where list_header_id = :1 ; end;' using p_list_header_id;
7654:
7655: /*********** added by savio for remote bug 3764343 **************************/
7656:
7657: /* open c10(p_list_header_id);
7822: -- calling logging program
7823: write_to_act_log('Error in generating list while executing procedure generate_list '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
7824:
7825: --
7826: UPDATE ams_list_headers_all
7827: SET last_generation_success_flag = 'N',
7828: status_code = 'FAILED',
7829: user_status_id = 311,
7830: status_date = sysdate,
7842: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7843: -- calling logging program
7844: write_to_act_log('Error in generating list while executing procedure generate_list '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
7845: --
7846: UPDATE ams_list_headers_all
7847: SET last_generation_success_flag = 'N',
7848: status_code = 'FAILED',
7849: user_status_id = 311,
7850: last_update_date = sysdate,
7864: WHEN AMS_LISTGENERATION_UTIL_PKG.cancelListGen THEN
7865: write_to_act_log('In GENERATE_LIST: User cancel list gen action detected. ' ,'LIST',g_list_header_id,'HIGH');
7866: write_to_act_log('List Generation Stopped successfully.','LIST',g_list_header_id,'HIGH');
7867:
7868: UPDATE ams_list_headers_all
7869: SET last_generation_success_flag = 'N',
7870: status_code = 'DRAFT',
7871: user_status_id = 300,
7872: last_update_date = sysdate,
7895: WHEN OTHERS THEN
7896: -- calling logging program
7897: write_to_act_log('Error in generating list while executing procedure generate_list '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
7898: --
7899: UPDATE ams_list_headers_all
7900: SET last_generation_success_flag = 'N',
7901: status_code = 'FAILED',
7902: user_status_id = 311,
7903: last_update_date = sysdate,
8758: l_update_sql VARCHAR2(32767);
8759: l_list_header_id number ;
8760: cursor c1 is
8761: select generation_type
8762: from ams_list_headers_all
8763: where list_header_id = l_list_header_id;
8764: l_generation_type varchar2(60);
8765: l_PARAMETERIZED_FLAG varchar2(1) := 'N';
8766: TYPE table_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
8814: l_order_number number := 0;
8815:
8816: l_repeat_tg varchar2(1);
8817: CURSOR c_repeat_tg is
8818: select 'Y' from ams_campaign_schedules_b b, ams_list_headers_all h
8819: where h.list_header_id = g_list_header_id
8820: and h.LIST_USED_BY_ID = b.schedule_id
8821: and b.orig_csch_id is not null;
8822:
8888: if nvl(l_tot_params,0) > 100 then
8889: write_to_act_log('Numbers of parameters exceed 100. Aborting list generation process. Please redefine your criteria and restrict it to 100.',
8890: 'LIST',g_list_header_id,'HIGH');
8891:
8892: UPDATE ams_list_headers_all
8893: SET last_generation_success_flag = 'N',
8894: status_code = 'FAILED',
8895: ctrl_status_code = 'DRAFT',
8896: user_status_id = 311,
8913: p_arc_log_used_by => 'LIST',
8914: p_log_used_by_id => g_list_header_id,
8915: p_level => 'HIGH');
8916:
8917: UPDATE ams_list_headers_all
8918: SET last_generation_success_flag = 'N',
8919: status_code = 'FAILED',
8920: user_status_id = 311,
8921: status_date = sysdate,
8985: OUT l_include_sql;
8986: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
8987: write_to_act_log('Error when executing process_'||l_action_dets_rec.list_act_type, 'LIST', g_list_header_id,'HIGH');
8988: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
8989: UPDATE ams_list_headers_all
8990: SET last_generation_success_flag = 'N',
8991: status_code = 'FAILED',
8992: user_status_id = 311,
8993: status_date = sysdate,
9208: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
9209: write_to_act_log('Error while executing remote procedure for generating target group', 'LIST', g_list_header_id,'HIGH');
9210: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
9211: --Added for bug 4577528 by bmuthukr.
9212: update ams_list_headers_all
9213: set last_generation_success_flag = 'N',
9214: status_code = 'FAILED',
9215: user_status_id = 311,
9216: status_date = sysdate,
9402: if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
9403: write_to_act_log('Error while generating target group in remote instance.', 'LIST', g_list_header_id,'HIGH');
9404: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
9405: --Added for bug 4577528 by bmuthukr.
9406: update ams_list_headers_all
9407: set last_generation_success_flag = 'N',
9408: status_code = 'FAILED',
9409: user_status_id = 311,
9410: status_date = sysdate,
9507: --gjoby check if l_no_of_duplicates required
9508: l_no_of_duplicates NUMBER := 0;
9509:
9510: -- Two records are required for init rec and complete rec
9511: -- Table ams_list_headers_all_tl and ams_list_headers_all
9512: l_listheader_rec ams_listheader_pvt.list_header_rec_type;
9513: l_tmp_listheader_rec ams_listheader_pvt.list_header_rec_type;
9514:
9515: -- Two records are required for init rec and complete rec
9518: l_tmp_listaction_rec ams_listaction_pvt.action_rec_type;
9519: l_no_of_rows_in_list number ;
9520: cursor c_get_rows (c_list_header_id in number ) is
9521: select no_of_rows_in_list
9522: from ams_list_headers_all
9523: where list_header_id = c_list_header_id ;
9524: l_error_position varchar2(100);
9525:
9526:
9525:
9526:
9527: cursor c_remote_list is
9528: select nvl(stypes.remote_flag,'N') ,database_link
9529: from ams_list_src_types stypes, ams_list_headers_all list
9530: where list.list_source_type = stypes.source_type_code
9531: and list_header_id = p_list_header_id;
9532:
9533: l_list_selection varchar2(1);
9538: and arc_action_used_by = 'LIST'
9539: and arc_incl_object_from in ('CELL','DIWB','SQL');
9540:
9541: cursor c_only_list_selection is
9542: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
9543: where act.action_used_by_id = p_list_header_id
9544: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
9545: and act.INCL_OBJECT_ID = head.list_header_id
9546: and head.status_code = 'AVAILABLE'
9572: l_list_rule_id number := 0;
9573: l_hd_status varchar(60);
9574: l_entry_count number;
9575: cursor c_hd_status is
9576: select STATUS_CODE from ams_list_headers_all
9577: where list_header_id = p_list_header_id;
9578:
9579: cursor c_entry_count is
9580: select count(1) from ams_list_entries
9582:
9583: l_list_field_mapped varchar2(1);
9584:
9585: cursor c_master_ds_fields_mapped is
9586: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
9587: where hd.list_header_id = p_list_header_id
9588: and hd.LIST_SOURCE_TYPE = ty.source_type_code
9589: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
9590: and fd.FIELD_COLUMN_NAME is NOT NULL;
9589: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
9590: and fd.FIELD_COLUMN_NAME is NOT NULL;
9591:
9592: cursor c_child_ds_fields_mapped is
9593: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty,
9594: ams_list_src_type_assocs ats
9595: where hd.list_header_id = p_list_header_id
9596: and hd.LIST_SOURCE_TYPE = ty.source_type_code
9597: and ty.list_source_type_id = ats.master_source_type_id
9603: l_no_of_rows number := 0;
9604:
9605: cursor c_check_gen_mode is
9606: select nvl(no_of_rows_in_list ,0)
9607: from ams_list_headers_all
9608: where list_header_id = g_list_header_id;
9609:
9610: -- SOLIN, bug 4410333
9611: -- check whether datasource is enabled.
9611: -- check whether datasource is enabled.
9612: cursor c_check_datasource(c_list_header_id NUMBER) is
9613: SELECT a.enabled_flag
9614: FROM ams_list_src_types a,
9615: ams_list_headers_all b
9616: WHERE a.source_type_code = b.list_source_type
9617: AND b.list_header_id = c_list_header_id;
9618:
9619: l_ds_enabled_flag VARCHAR2(1);
9619: l_ds_enabled_flag VARCHAR2(1);
9620: -- SOLIN, end
9621:
9622: cursor c_master_ds_tca_mapped is
9623: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
9624: where hd.list_header_id = p_list_header_id
9625: and hd.LIST_SOURCE_TYPE = ty.source_type_code
9626: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
9627: and fd.tca_column_id is NOT NULL;
9626: and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
9627: and fd.tca_column_id is NOT NULL;
9628:
9629: cursor c_child_ds_tca_mapped is
9630: select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty,
9631: ams_list_src_type_assocs ats
9632: where hd.list_header_id = p_list_header_id
9633: and hd.LIST_SOURCE_TYPE = ty.source_type_code
9634: and ty.list_source_type_id = ats.master_source_type_id
9639: SELECT min(master_child.field_column_name1) ,count(master_child.field_column_name) from
9640: (
9641: SELECT d.field_column_name field_column_name1,d.field_column_name
9642: FROM ams_list_src_types a,
9643: ams_list_headers_all b,
9644: ams_list_src_fields d
9645: WHERE a.source_type_code = b.list_source_type
9646: and b.list_header_id = p_list_header_id
9647: and d.list_source_type_id = a.list_source_type_id
9648: and d.USED_IN_LIST_ENTRIES = 'Y'
9649: union all
9650: SELECT d.field_column_name field_column_name1,d.field_column_name
9651: FROM ams_list_src_types a,
9652: ams_list_headers_all b,
9653: ams_list_src_fields d,
9654: ams_list_src_type_assocs e
9655: WHERE a.source_type_code = b.list_source_type
9656: and b.list_header_id = p_list_header_id
9669:
9670: cursor c_get_dup_mapping(p_col_name in varchar2) is
9671: SELECT d.source_column_name, d.field_column_name , d.de_list_source_type_code stc
9672: FROM ams_list_src_types a,
9673: ams_list_headers_all b,
9674: ams_list_src_fields d
9675: WHERE a.source_type_code = b.list_source_type
9676: and b.list_header_id = g_list_header_id
9677: and d.list_source_type_id = a.list_source_type_id
9679: and d.USED_IN_LIST_ENTRIES = 'Y'
9680: union all
9681: SELECT d.source_column_name, d.field_column_name, d.de_list_source_type_code stc
9682: FROM ams_list_src_types a,
9683: ams_list_headers_all b,
9684: ams_list_src_fields d,
9685: ams_list_src_type_assocs e
9686: WHERE a.source_type_code = b.list_source_type
9687: and b.list_header_id = g_list_header_id
9691: and d.USED_IN_LIST_ENTRIES = 'Y';
9692: --
9693: cursor c_check_supp is
9694: select nvl(apply_suppression_flag,'N')
9695: from ams_list_headers_all
9696: where list_header_id = p_list_header_id;
9697:
9698: l_supp_flag varchar2(1) := 'N';
9699:
9731: 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.',
9732: p_arc_log_used_by => 'LIST',
9733: p_log_used_by_id => p_list_header_id,
9734: p_level => 'HIGH');
9735: UPDATE ams_list_headers_all
9736: SET last_generation_success_flag = 'N',
9737: status_code = 'FAILED',
9738: user_status_id = 311,
9739: status_date = sysdate,
9772: p_arc_log_used_by => 'LIST',
9773: p_log_used_by_id => p_list_header_id,
9774: p_level =>'HIGH');
9775:
9776: UPDATE ams_list_headers_all
9777: SET last_generation_success_flag = 'N',
9778: status_code = 'FAILED',
9779: user_status_id = 311,
9780: status_date = sysdate,
9820: exit when c_get_dup_mapping%notfound;
9821: 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');
9822: end loop;
9823: --
9824: UPDATE ams_list_headers_all
9825: SET last_generation_success_flag = 'N',
9826: status_code = 'FAILED',
9827: user_status_id = 311,
9828: status_date = sysdate,
9954: close c_check_gen_mode;
9955:
9956: if l_no_of_rows = 0 then
9957: 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');
9958: UPDATE ams_list_headers_all
9959: SET last_generation_success_flag = 'N',
9960: status_code = 'FAILED',
9961: ctrl_status_code = 'DRAFT',
9962: user_status_id = 311,
10266: write_to_act_log('Error in executing remote procedure for deleting target group entries', 'LIST', g_list_header_id,'HIGH');
10267: write_to_act_log('Error '||x_msg_data , 'LIST', g_list_header_id,'HIGH');
10268: end if;
10269:
10270: UPDATE ams_list_headers_all
10271: SET last_generation_success_flag = 'N',
10272: NO_OF_ROWS_ACTIVE = null,
10273: NO_OF_ROWS_IN_LIST = null,
10274: status_code = 'FAILED',
10305: open c_hd_status;
10306: fetch c_hd_status into l_hd_status;
10307: close c_hd_status;
10308: if l_hd_status = 'AVAILABLE' then
10309: UPDATE ams_list_headers_all
10310: SET status_code = 'GENERATING',
10311: user_status_id = 302
10312: WHERE list_header_id = p_list_header_id;
10313: -- Added for cancel list gen as it prevents parallel update- Raghu
10386: WHEN FND_API.G_EXC_ERROR THEN
10387: -- calling logging program
10388: write_to_act_log('Error while executing procedure generate_target_group for generating target group.', 'LIST', g_list_header_id,'HIGH');
10389:
10390: UPDATE ams_list_headers_all
10391: SET last_generation_success_flag = 'N',
10392: status_code = 'FAILED',
10393: user_status_id = 311,
10394: status_date = sysdate,
10412: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
10413: -- calling logging program
10414: write_to_act_log('Error while executing procedure generate_target_group for generating target group.', 'LIST', g_list_header_id,'HIGH');
10415:
10416: UPDATE ams_list_headers_all
10417: SET last_generation_success_flag = 'N',
10418: status_code = 'FAILED',
10419: user_status_id = 311,
10420: last_update_date = sysdate,
10438: WHEN OTHERS THEN
10439: -- calling logging program
10440: write_to_act_log('Error while executing procedure generate_target_group for generating target group.', 'LIST', g_list_header_id,'HIGH');
10441:
10442: UPDATE ams_list_headers_all
10443: SET last_generation_success_flag = 'N',
10444: status_code = 'FAILED',
10445: user_status_id = 311,
10446: last_update_date = sysdate,
10465: END GENERATE_TARGET_GROUP;
10466:
10467: -- -----------------------------------------------------------------------
10468: PROCEDURE Execute_Remote_Dedupe_List
10469: (p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE
10470: ,p_enable_word_replacement_flag
10471: AMS_LIST_HEADERS_ALL.ENABLE_WORD_REPLACEMENT_FLAG%TYPE
10472: ,p_send_to_log VARCHAR2 := 'N'
10473: ,p_object_name VARCHAR2 := 'AMS_LIST_ENTRIES'
10467: -- -----------------------------------------------------------------------
10468: PROCEDURE Execute_Remote_Dedupe_List
10469: (p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE
10470: ,p_enable_word_replacement_flag
10471: AMS_LIST_HEADERS_ALL.ENABLE_WORD_REPLACEMENT_FLAG%TYPE
10472: ,p_send_to_log VARCHAR2 := 'N'
10473: ,p_object_name VARCHAR2 := 'AMS_LIST_ENTRIES'
10474: )
10475: IS
10495: -- perform a check to see if this list has been deduped already.
10496: CURSOR c_deduped_before (my_list_header_id IN NUMBER)
10497: IS
10498: SELECT last_deduped_by_user_id
10499: FROM ams_list_headers_all
10500: WHERE list_header_id = my_list_header_id;
10501:
10502: -- get a distinct list of merge keys for the list and a
10503: -- count of the occurance of each key
10523:
10524: l_fields VARCHAR2(10000);
10525: l_no_of_masters NUMBER := 0;
10526: l_list_rule_id ams_list_rules_all.list_rule_id%TYPE;
10527: l_last_dedupe_by ams_list_headers_all.last_deduped_by_user_id%TYPE;
10528: l_dedupe_key ams_list_entries.dedupe_key%TYPE;
10529: l_dedupe_key_count NUMBER;
10530: l_rank_count NUMBER;
10531: i BINARY_INTEGER := 1;
10794: END LOOP; --c_list_rules loop
10795:
10796: --recording who performed the deduplication and at what time.
10797: --recording the number of duplicates found.
10798: UPDATE ams_list_headers_all
10799: SET last_deduped_by_user_id = FND_GLOBAL.User_Id
10800: ,last_dedupe_date = SYSDATE
10801: ,no_of_rows_duplicates = l_total_dup_recs
10802: WHERE list_header_id = p_list_header_id;
10802: WHERE list_header_id = p_list_header_id;
10803:
10804: /* savio removing potential multiple update statement */
10805: /*
10806: -- UPDATE ams_list_headers_all
10807: -- SET no_of_rows_duplicates = l_total_dup_recs
10808: -- WHERE list_header_id = p_list_header_id;
10809: */
10810:
10824:
10825: l_list_header_id NUMBER;
10826: cursor c_migrate_list is
10827: select head.list_header_id
10828: from ams_list_headers_all head, ams_list_src_types stypes
10829: where head.status_code = 'AVAILABLE'
10830: and head.MIGRATION_DATE is NULL
10831: and head.list_source_type = stypes.source_type_code
10832: and stypes.remote_flag = 'Y';
10872:
10873:
10874: cursor c_dblink is
10875: select database_link
10876: from ams_list_src_types stypes, ams_list_headers_all list
10877: where list.list_source_type = stypes.source_type_code
10878: and list_header_id = p_list_header_id;
10879:
10880: begin
11689: Ams_Utility_Pvt.Write_Conc_log('End migrate_lists : '||to_char(p_list_header_id));
11690: SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
11691: WHERE system_status_type = 'AMS_LIST_STATUS' AND
11692: system_status_code = 'AVAILABLE' and default_flag = 'Y';
11693: UPDATE ams_list_headers_all
11694: set status_code = 'AVAILABLE',
11695: user_status_id = l_user_status_id,
11696: migration_date = sysdate
11697: where list_header_id = p_list_header_id;
11711: RAISE FND_API.G_EXC_ERROR;
11712: END IF;
11713: EXCEPTION
11714: WHEN OTHERS THEN
11715: UPDATE ams_list_headers_all
11716: SET last_generation_success_flag = 'N',
11717: status_code = 'FAILED',
11718: user_status_id = 311,
11719: last_update_date = sysdate,
11849: into l_no_of_rows_active ,
11850: l_fatigued_records ;
11851: close c_count_list_entries;
11852:
11853: UPDATE ams_list_headers_all
11854: SET NO_OF_ROWS_FATIGUED = l_fatigued_records,
11855: NO_OF_ROWS_ACTIVE = l_no_of_rows_active
11856: WHERE list_header_id = p_list_header_id ;
11857: -- Added for cancel list gen as it prevents parallel update- Raghu
11973: l_update_sql VARCHAR2(32767);
11974: l_list_header_id number ;
11975: cursor c1 is
11976: select generation_type
11977: from ams_list_headers_all
11978: where list_header_id = l_list_header_id;
11979: l_generation_type varchar2(60);
11980: l_selection_results t_number;
11981: l_list_select_action t_number;
11992: l_last_generation_success_flag varchar2(1);
11993:
11994: cursor c_last_gen is
11995: select nvl(last_generation_success_flag,'N')
11996: from ams_list_headers_all
11997: where list_header_id = p_action_used_by_id;
11998:
11999: BEGIN
12000: g_list_header_id := p_action_used_by_id;
12754:
12755: cursor cur_primary_key_sql is
12756: SELECT lc.SOURCE_OBJECT_NAME, lc.SOURCE_OBJECT_PK_FIELD
12757: FROM ams_list_queries_all lq,
12758: ams_list_headers_all lh,
12759: ams_list_src_types lc
12760: WHERE lq.list_query_id = p_incl_object_id
12761: and lq.ARC_ACT_LIST_QUERY_USED_BY = 'LIST'
12762: and lq.ACT_LIST_QUERY_USED_BY_ID = lh.list_header_id
13090:
13091: cursor c_tca_columns is
13092: select flds.field_column_name , tca.column_name
13093: from ams_list_src_fields flds, AMS_DS_TCA_ENTITY_COLS tca,
13094: ams_list_headers_all hdr, ams_list_src_types typ
13095: where hdr.list_header_id = p_list_header_id
13096: and hdr.LIST_SOURCE_TYPE = typ.SOURCE_TYPE_CODE
13097: and typ.list_source_type_id = flds.list_source_type_id
13098: and flds.enabled_flag = 'Y'
13099: and used_in_list_entries = 'Y'
13100: and flds.tca_column_id = tca.ENTITY_COLUMN_ID;
13101:
13102: cursor c_data_type is
13103: select source_category from ams_list_src_types types, ams_list_headers_all head
13104: where head.list_header_id = p_list_header_id
13105: and head.list_source_type = types.source_type_code;
13106:
13107: l_prof VARCHAR2(50);
13829: -- ---------------------------TCA CALL ---------------------------------
13830:
13831: END LOOP; -- For c_list_entries
13832: close c_list_entries;
13833: update ams_list_headers_all
13834: set migration_date = sysdate
13835: where list_header_id = p_list_header_id;
13836:
13837: write_to_act_log('Executed procedure tca_upload_process', 'LIST', g_list_header_id,'LOW');
13911: and arc_action_used_by = 'LIST'
13912: and arc_incl_object_from in ('CELL','DIWB','SQL');
13913:
13914: cursor c_only_list_selection is
13915: select 'Y' from ams_list_select_actions act, ams_list_headers_all head
13916: where act.action_used_by_id = p_list_header_id
13917: and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
13918: and act.INCL_OBJECT_ID = head.list_header_id
13919: and head.status_code = 'AVAILABLE'
13983: x_is_manual OUT NOCOPY varchar2 ) is
13984:
13985: cursor c1 is
13986: select list_type
13987: from ams_list_headers_all
13988: where list_header_id = p_list_header_id;
13989:
13990: cursor c2 is
13991: select 1
14060: 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.
14061: open c2;
14062: fetch c2 into l_dummy;
14063: if c2%found then -- List already has enabled entries.. So making it as available.
14064: update ams_list_headers_all
14065: set status_code = 'AVAILABLE',
14066: user_status_id = 303,
14067: status_date = sysdate,
14068: last_update_date = sysdate
14067: status_date = sysdate,
14068: last_update_date = sysdate
14069: where list_header_id = p_list_header_id;
14070: else -- No enabled entries..So it will be in DRAFT status
14071: update ams_list_headers_all
14072: set status_code = 'DRAFT',
14073: user_status_id = 300,
14074: status_date = sysdate,
14075: last_update_date = sysdate
14113: cursor c_list_det is
14114: select stypes.database_link,
14115: list.remote_gen_flag,
14116: list.list_type
14117: from ams_list_src_types stypes, ams_list_headers_all list
14118: where list.list_source_type = stypes.source_type_code
14119: and list_header_id = p_list_header_id;
14120:
14121: cursor c_count_list_entries is
14156: OUT x_msg_data,
14157: OUT x_return_status;
14158: end if;
14159:
14160: update ams_list_headers_all
14161: set no_of_rows_in_list = nvl(l_no_of_rows_in_list,0),
14162: no_of_rows_active = nvl(l_no_of_rows_active,0),
14163: no_of_rows_inactive = nvl(l_no_of_rows_inactive,0),
14164: no_of_rows_manually_entered = nvl(l_no_of_rows_manually_entered,0)