[Home] [Help]
62: ----------------------------------------------------------------------------
63:
64: cursor g_initial_count is
65: select count(1)
66: from ams_list_entries
67: where list_header_id = g_list_header_id
68: and enabled_flag = 'Y';
69:
70: PROCEDURE logger is
293: and enabled_flag = 'Y';
294:
295: cursor c_get_cnt_from_sel is
296: select count(1)
297: from ams_list_entries
298: where list_header_id = g_list_header_id
299: and list_select_action_id = p_list_select_action_id;
300:
301: begin
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
627: ------------------------------------------------------------------------------
628: --gets the number of entries in the list for a particular list select action--
633: e.List_select_action_id,
634: a.distribution_pct,
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
650: e.List_select_action_id,
651: a.distribution_pct,
652: a.rank rank_col,
653: sum(decode(e.marked_as_duplicate_flag,'Y',1,0))
654: FROM ams_list_entries e,
655: ams_list_select_actions a
656: WHERE e.list_header_id = p_list_header_id
657: AND e.list_select_action_id = a.list_select_action_id
658: AND a.arc_action_used_by = 'LIST'
667: a.List_select_action_id,
668: a.distribution_pct,
669: a.rank,
670: sum(decode(e.marked_as_duplicate_flag,'Y',1,0))
671: FROM ams_list_entries e,
672: ams_list_select_actions a,
673: ams_act_lists t
674: WHERE e.list_header_id = p_list_header_id
675: AND e.list_select_action_id = t.act_list_header_id
691: 0)),
692: sum(decode(MARKED_AS_SUPPRESSED_FLAG,'Y',1,0)),
693: sum(decode(MARKED_AS_FATIGUED_FLAG,'Y',1,0)),
694: sum(decode(TCA_LOAD_STATUS,'ERROR',1,0))
695: from ams_list_entries
696: where list_header_id = cur_p_list_header_id ;
697:
698: l_tca_error_recs number;
699: l_TCA_FAILED_RECORDS number;
698: l_tca_error_recs number;
699: l_TCA_FAILED_RECORDS number;
700:
701: cursor c_tca_error is
702: select count(1) from ams_list_entries
703: where list_header_id = p_list_header_id
704: and TCA_LOAD_STATUS = 'ERROR';
705:
706: l_list_type varchar2(100);
954:
955: -----------------------------------------------------------------------------
956: -- Delete_List_Entries
957: -- 1. Delete List entries which may have occured from previous generations
958: --- of the list. Table Name : ams_list_tmp_entries and ams_list_entries
959: -- 2. Delete from ams_list_src_type_usages
960: -- List_src_type_usages store the source type code against each type
961: -- of list
962: -- 10/29/1999 TDONOHOE Created
971:
972: l_delete_action varchar2(80);
973: l_total_recs number;
974: l_null varchar2(30) := null;
975: -- deletes from ams_list_entries in case of standard type of generation
976: BEGIN
977: write_to_act_log('Executing delete_list_entries to delete entries from ams_list_entries(Since list/target group is generated in STANDARD mode).', 'LIST', g_list_header_id,'LOW');
978: x_return_status := FND_API.G_RET_STS_SUCCESS;
979: -------------------------------------------------------------------------------
973: l_total_recs number;
974: l_null varchar2(30) := null;
975: -- deletes from ams_list_entries in case of standard type of generation
976: BEGIN
977: write_to_act_log('Executing delete_list_entries to delete entries from ams_list_entries(Since list/target group is generated in STANDARD mode).', 'LIST', g_list_header_id,'LOW');
978: x_return_status := FND_API.G_RET_STS_SUCCESS;
979: -------------------------------------------------------------------------------
980: --Delete all existing entries for this list which are in the temporary table.--
981: -------------------------------------------------------------------------------
983: ----------------------------------------------
984: --Delete all existing entries for this list.--
985: ----------------------------------------------
986: l_delete_action := 'List Entries delete';
987: DELETE FROM ams_list_entries
988: WHERE list_header_id = p_list_header_id;
989: write_to_act_log(sql%rowcount||' entries deleted from ams_list_entries in local instance.', 'LIST', g_list_header_id,'LOW');
990: /********************************************************************
991: Dynamic procedure will delete the list from the remote instance in
985: ----------------------------------------------
986: l_delete_action := 'List Entries delete';
987: DELETE FROM ams_list_entries
988: WHERE list_header_id = p_list_header_id;
989: write_to_act_log(sql%rowcount||' entries deleted from ams_list_entries in local instance.', 'LIST', g_list_header_id,'LOW');
990: /********************************************************************
991: Dynamic procedure will delete the list from the remote instance in
992: case of remote list
993: *********************************************************************/
1282:
1283: FETCH cur_get_created_by INTO l_created_by;
1284: CLOSE cur_get_created_by;
1285:
1286: x_include_sql := ' insert into ams_list_entries
1287: (list_header_id ,
1288: list_entry_id,
1289: imp_source_line_id,
1290: object_version_number,
1384: FAX
1385: )
1386: select
1387: ' || p_action_used_by_id || ' ,
1388: ams_list_entries_s.nextval, import_source_line_id,
1389: 1 ,' ||
1390: ''''||'NONE' ||'''' || ','||
1391: 0 || ',' ||
1392: ''''||'NONE' ||'''' || ','||
1390: ''''||'NONE' ||'''' || ','||
1391: 0 || ',' ||
1392: ''''||'NONE' ||'''' || ','||
1393: ''''||'IMPH' ||'''' || ','||
1394: 'ams_list_entries_s.currval'|| ','||
1395: 530 || ','||
1396: ''''||'N' ||''''|| ','||
1397: ''''||'N' ||''''|| ','||
1398: ''''||'N' ||''''|| ','||
1486: ' and import_list_header_id =' || p_incl_object_id ||
1487: ' and nvl(party_id, import_source_line_id) in (' ;
1488: write_to_act_log('Insert statement constructed for imported B2B list', 'LIST', g_list_header_id,'LOW');
1489: else
1490: x_include_sql := ' insert into ams_list_entries
1491: (list_header_id ,
1492: list_entry_id,
1493: imp_source_line_id,
1494: source_code ,
1576: FAX
1577: )
1578: select
1579: ' || p_action_used_by_id || ' ,
1580: ams_list_entries_s.nextval, ' ||
1581: ' import_source_line_id , ' ||
1582: ''''||'NONE' ||'''' || ','||
1583: 0 || ',' ||
1584: ''''||'NONE' ||'''' || ','||
1582: ''''||'NONE' ||'''' || ','||
1583: 0 || ',' ||
1584: ''''||'NONE' ||'''' || ','||
1585: ''''||'IMPH' ||'''' || ','||
1586: 'ams_list_entries_s.currval'|| ','||
1587: 530 || ','||
1588: ''''||'N' ||''''|| ','||
1589: ''''||'N' ||''''|| ','||
1590: ''''||'N' ||''''|| ','||
1711: write_to_act_log('Executing process_list since list has been included in list/target group selections.', 'LIST', g_list_header_id,'LOW');
1712: if p_list_action_type = 'INCLUDE' then
1713: --bmuthukr for lpo
1714: --get_count(p_list_select_action_id,p_order_number,'LIST',null);
1715: x_include_sql := 'insert into ams_list_entries
1716: (list_header_id ,
1717: list_entry_id,
1718: object_version_number,
1719: source_code ,
2091: CUSTOM_COLUMN25
2092: )
2093: select ' ||
2094: p_action_used_by_id ||',
2095: ams_list_entries_s.nextval,
2096: 1 ,' ||
2097: ''''||'NONE' ||'''' || ','||
2098: 0 || ',' ||
2099: ''''||'NONE' ||'''' || ','||
2097: ''''||'NONE' ||'''' || ','||
2098: 0 || ',' ||
2099: ''''||'NONE' ||'''' || ','||
2100: ''''||'LIST' ||'''' || ','||
2101: 'ams_list_entries_s.currval'|| ','||
2102: 530 || ','||
2103: ''''||'N' ||''''|| ','||
2104: ''''||'N' ||''''|| ','||
2105: ''''||'N' ||''''|| ','||
2466: CUSTOM_COLUMN22,
2467: CUSTOM_COLUMN23,
2468: CUSTOM_COLUMN24,
2469: CUSTOM_COLUMN25
2470: from ams_list_entries
2471: where list_header_id = ' || p_incl_object_id ||
2472: ' and nvl(enabled_flag,' ||''''||'N'||''''||') = '||
2473: ''''||'Y'||'''' ||
2474: ' and list_entry_source_system_id in (' ;
2516: /*
2517: Commented OUT NOCOPY because of preformance issues
2518: list_entry_source_syetem_type is not selected
2519: x_std_sql := ' select list_entry_source_system_id||
2520: list_entry_source_system_type from ams_list_entries
2521: where list_header_id = ' || p_incl_object_id ||
2522: ' and enabled_flag = ' || ''''||'Y' || '''' ;
2523: */
2524: x_std_sql := ' select list_entry_source_system_id
2521: where list_header_id = ' || p_incl_object_id ||
2522: ' and enabled_flag = ' || ''''||'Y' || '''' ;
2523: */
2524: x_std_sql := ' select list_entry_source_system_id
2525: from ams_list_entries
2526: where list_header_id = ' || p_incl_object_id ||
2527: ' and enabled_flag = ' || ''''||'Y' || '''' ;
2528: write_to_act_log('Execution of procedure process_list completed', 'LIST', g_list_header_id,'LOW');
2529: END process_list ;
2741:
2742: FETCH cur_get_created_by INTO l_created_by;
2743: CLOSE cur_get_created_by;
2744:
2745: l_insert_sql := 'insert into ams_list_entries '||
2746: '( LIST_SELECT_ACTION_FROM_NAME, '||
2747: ' LIST_ENTRY_SOURCE_SYSTEM_ID , '||
2748: ' LIST_ENTRY_SOURCE_SYSTEM_TYPE, '||
2749: ' list_select_action_id , '||
2776: to_char(sysdate )|| ''''||','||
2777: to_char(FND_GLOBAL.login_id )|| ',' ||''''||
2778: to_char(sysdate )|| ''''||','||
2779: to_char(nvl(l_created_by, FND_GLOBAL.login_id) )|| ',' ||
2780: 'ams_list_entries_s.nextval' || ','||
2781: 1 || ','||
2782: ''''||'NONE' ||'''' || ','||
2783: 0 || ',' ||
2784: ''''||'NONE' ||'''' || ','||
2782: ''''||'NONE' ||'''' || ','||
2783: 0 || ',' ||
2784: ''''||'NONE' ||'''' || ','||
2785: ''''||'NONE' ||'''' || ','||
2786: 'ams_list_entries_s.currval'|| ','||
2787: 530 || ','||
2788: ''''||'N' ||''''|| ','||
2789: ''''||'N' ||''''|| ','||
2790: ''''||'N' ||''''|| ','||
3869: l_total_recs number;
3870: l_query_templ_flag varchar2(1) ;
3871:
3872: cursor c_count1 is select count(1)
3873: from ams_list_entries
3874: where list_header_id = g_list_header_id ;
3875: l_count1 number:= 0;
3876:
3877:
3923: BEGIN
3924: write_to_act_log('Executing process_list_actions','LIST',g_list_header_id, 'HIGH');
3925: l_const_sql := ' minus '||
3926: ' select list_entry_source_system_id ' ||
3927: ' from ams_list_entries ' ||
3928: ' where list_header_id = ' || p_action_used_by_id ;
3929:
3930: l_const_sql1 := ' and LIST_SELECT_ACTION_ID = ';
3931:
4590: -- CREATE_LIST_ENTRIES
4591: --
4592: -- PURPOSE
4593:
4594: -- 1. Populate The AMS_LIST_ENTRIES table with a DISTINCT set of
4595: -- Entries from The AMS_LIST_TMP_ENTRIES table.
4596: -- 2. An Entry is considered UNIQUE by values in The list_entry_source_id
4597: -- and List_Entry_Source_Type fields from The AMS_LIST_TMP_ENTRIES table.
4598: -- 3. There may be more than one Entry in The AMS_LIST_TMP_ENTRIES table
4601: -- the Highest Rank is choosen, if Two Equivalent entries have
4602: -- the same rank then one entry is choosen arbitrarily.
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:
4602: -- the same rank then one entry is choosen arbitrarily.
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:
4657: FETCH cur_get_created_by INTO l_created_by;
4658: CLOSE cur_get_created_by;
4659:
4660:
4661: INSERT INTO ams_List_Entries
4662: ( list_entry_id ,
4663: last_update_date ,
4664: last_updated_by ,
4665: creation_date ,
4683: exclude_in_triggered_list_flag ,
4684: enabled_flag,
4685: object_version_number
4686: )
4687: ( select ams_list_entries_s.nextval,
4688: sysdate,
4689: fnd_global.user_id,
4690: sysdate,
4691: nvl(l_created_by, fnd_global.user_id),
4696: s.incl_object_name,
4697: 'NONE',
4698: 0,
4699: h.list_used_by_id,
4700: ams_list_entries_s.currval,
4701: t.list_entry_source_id,
4702: t.list_entry_source_type,
4703: fnd_global.conc_login_id,
4704: 'N',
4717: );
4718:
4719: select count(*)
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,
4764: close c_check_tg;
4765:
4766: if l_tg_check = 'Y' then
4767: write_to_act_log('Generating target group(which has selections based on LIST) in update mode.','LIST',g_list_header_id,'LOW');
4768: update ams_list_entries ale
4769: set (
4770: ale.newly_updated_flag ,
4771: ale.enabled_flag ,
4772: ale.SUFFIX,
5460: ail.CUSTOM_COLUMN22,
5461: ail.CUSTOM_COLUMN23,
5462: ail.CUSTOM_COLUMN24,
5463: ail.CUSTOM_COLUMN25
5464: from ams_list_entries ail,
5465: ams_act_lists als
5466: where als.ACT_LIST_HEADER_ID = ale.list_select_action_id
5467: and als.LIST_HEADER_ID = ail.list_header_id
5468: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
5470: and rownum <=1 )
5471: where arc_list_select_action_from = 'LIST'
5472: and list_header_id = p_list_header_id
5473: and exists (select 'x'
5474: from ams_list_entries ail,
5475: ams_act_lists als
5476: where als.ACT_LIST_HEADER_ID = ale.list_select_action_id
5477: and als.LIST_HEADER_ID = ail.list_header_id
5478: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
5484: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
5485:
5486: ELSE -- For List Generation
5487: write_to_act_log('Generating list(which has selections based on LIST) in update mode.','LIST',g_list_header_id,'LOW');
5488: update ams_list_entries ale
5489: set (
5490: ale.newly_updated_flag ,
5491: ale.enabled_flag ,
5492: ale.SUFFIX,
6120: ail.COL297 ,
6121: ail.COL298 ,
6122: ail.COL299 ,
6123: ail.COL300
6124: from ams_list_entries ail,
6125: ams_list_select_actions als
6126: where als.list_select_action_id = ale.list_select_action_id
6127: and als.incl_object_id = ail.list_header_id
6128: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
6130: and rownum <=1 )
6131: where arc_list_select_action_from = 'LIST'
6132: and list_header_id = p_list_header_id
6133: and exists (select 'x'
6134: from ams_list_entries ail,
6135: ams_list_select_actions als
6136: where als.list_select_action_id = ale.list_select_action_id
6137: and als.incl_object_id = ail.list_header_id
6138: and ail.list_entry_source_system_id = ale.list_entry_source_system_id
6162: close c1;
6163: write_to_act_log('Import type is '||l_b2b_flag,'LIST',g_list_header_id,'LOW');
6164: if l_b2b_flag = 'B2B' then
6165: write_to_act_log('Generating list(which has selections based on imported B2B list) in update mode.','LIST',g_list_header_id,'LOW');
6166: update ams_list_entries ale
6167: set (
6168: ale.newly_updated_flag ,
6169: -- ale.enabled_flag ,
6170: ale.ADDRESS_LINE1,
6277: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
6278: end if;
6279: if l_b2b_flag = 'B2C' then
6280: write_to_act_log('Generating list(which has selections based on imported B2C list) in update mode.','LIST',g_list_header_id,'LOW');
6281: update ams_list_entries ale
6282: set (
6283: ale.newly_updated_flag ,
6284: -- ale.enabled_flag ,
6285: ale.customer_name,
6500: g_list_header_id := p_list_header_id;
6501: end if;
6502:
6503: if nvl(g_remote_list,'N') = 'Y' then
6504: execute immediate 'select count(1) from ams_list_entries@'||g_database_link||' where list_header_id = '||p_list_header_id||' and rownum = 1' into l_remote_cnt;
6505: if l_remote_cnt = 0 then
6506: write_to_act_log('No entries in remote schema for this list/target group. Cannot update.', 'LIST', g_list_header_id,'LOW');
6507: x_return_status := 'S';
6508: return;
6514:
6515: write_to_act_log('Executing procedure get_list_entry_data.', 'LIST', g_list_header_id,'LOW');
6516: OPEN C_MAPPING_TYPES_USED(p_list_header_id);
6517: LOOP
6518: l_update_str := 'UPDATE ams_list_entries SET (';
6519: l_select_str := ' ) = ( SELECT DISTINCT ';
6520: l_header_clause := ' AND list_header_id = '||
6521: to_char(p_list_header_id);
6522: l_type_clause := ' AND list_entry_source_system_type = ';
6650: end if;
6651: l_iterator := 0;
6652: l_list_entry_columns := l_NULL_table;
6653: l_source_columns := l_NULL_table;
6654: l_update_str := 'UPDATE ams_list_entries SET (';
6655: l_select_str := ' ) = ( SELECT ';
6656: l_header_clause := ' AND list_header_id = '||
6657: to_char(p_list_header_id);
6658: l_type_clause := ' AND list_entry_source_system_type = ';
6797: -- START OF COMMENTS
6798: -- NAME : GENERATE_LIST.
6799: -- PURPOSE
6800: -- 1. Public Procedure which when called will generate a set of list
6801: -- entries into the ams_list_entries table.
6802: -- HISTORY
6803: -- 06/01/1999 tdonohoe created
6804: -- 01/24/2001 gjoby Re-Created for Hornet
6805: -- END OF COMMENTS
7365: --So no need to enable all the entries before generation.
7366: -- if l_listheader_rec.generation_type = 'UPDATE' then
7367: /* if l_listheader_rec.generation_type = 'UPD' then
7368: write_to_act_log(' List is generated in UPDATE mode', 'LIST', g_list_header_id,'HIGH');
7369: update ams_list_entries
7370: set newly_updated_flag = 'N',
7371: enabled_flag = 'Y'
7372: where list_header_id = l_listheader_rec.list_header_id;
7373: */
7488: -- if l_listheader_rec.generation_type = 'UPDATE' then
7489: if l_listheader_rec.generation_type = 'UPD' then
7490: l_error_position := '<- set enabled flag for gen type UPDATE ';
7491: if g_remote_list <> 'Y' then
7492: update ams_list_entries
7493: set enabled_flag = 'N'
7494: where newly_updated_flag = 'N'
7495: and list_header_id = l_listheader_rec.list_header_id;
7496: write_to_act_log(sql%rowcount||' entries disabled when generating list in update mode','LIST',g_list_header_id,'HIGH');
7531: */
7532: if g_remote_list_gen = 'N' then
7533: if l_listheader_rec.generation_type = 'STANDARD' then --R12 applicable only for full refresh mode
7534: write_to_act_log('Identifying duplicate records(based on party id) in the list.','LIST',g_list_header_id,'HIGH');
7535: UPDATE ams_list_entries a
7536: SET a.enabled_flag = 'N',
7537: a.marked_as_duplicate_flag = 'Y'
7538: WHERE a.list_header_id = p_list_header_id
7539: and a.enabled_flag = 'Y'
7537: a.marked_as_duplicate_flag = 'Y'
7538: WHERE a.list_header_id = p_list_header_id
7539: and a.enabled_flag = 'Y'
7540: AND a.rowid > (SELECT min(b.rowid)
7541: from ams_list_entries b
7542: where b.list_header_id = p_list_header_id
7543: and b.party_id = a.party_id
7544: and b.enabled_flag = 'Y'
7545: and b.rank = a.rank
7544: and b.enabled_flag = 'Y'
7545: and b.rank = a.rank
7546: );
7547: write_to_act_log('Duplicate records(based on party) within the selections identified and marked.','LIST',g_list_header_id,'LOW');
7548: UPDATE ams_list_entries a
7549: SET a.enabled_flag = 'N',
7550: a.marked_as_duplicate_flag = 'Y'
7551: WHERE a.list_header_id = p_list_header_id
7552: and a.enabled_flag = 'Y'
7551: WHERE a.list_header_id = p_list_header_id
7552: and a.enabled_flag = 'Y'
7553: -- AND a.rowid > (SELECT min(b.rowid)
7554: AND a.rank > (SELECT min(b.rank)
7555: from ams_list_entries b
7556: where b.list_header_id = p_list_header_id
7557: and b.party_id = a.party_id
7558: and b.enabled_flag = 'Y'
7559: );
7583: (p_list_header_id => p_list_header_id,
7584: p_enable_word_replacement_flag => 'Y',
7585: -- l_listheader_rec.enable_word_replacement_flag,
7586: p_send_to_log => l_listheader_rec.enable_log_flag,
7587: p_object_name => 'AMS_LIST_ENTRIES');
7588: write_to_act_log('Deduplication done for this list.', 'LIST', g_list_header_id,'HIGH');
7589: else
7590: /* For Remote list generation */
7591: write_to_act_log('Call Execute_Remote_Dedupe_List for deduplication in remote instance.', 'LIST', g_list_header_id,'HIGH');
7593: (p_list_header_id => p_list_header_id,
7594: p_enable_word_replacement_flag => 'Y',
7595: -- l_listheader_rec.enable_word_replacement_flag,
7596: p_send_to_log => 'Y', -- l_listheader_rec.enable_log_flag,
7597: p_object_name => 'AMS_LIST_ENTRIES');
7598: write_to_act_log('Deduplication done for this list in remote instance.', 'LIST', g_list_header_id,'LOW');
7599: end if;
7600: end if;
7601: end if;
8789: l_list_select_id num_tbl_type;
8790: l_incl_object_type char_tbl_type;
8791:
8792: cursor c_count1 is select count(1)
8793: from ams_list_entries
8794: where list_header_id = g_list_header_id ;
8795: l_count1 number:= 0;
8796:
8797: cursor c_query_temp_type is
8849: --END IF;
8850:
8851: l_const_sql := ' minus '||
8852: ' select list_entry_source_system_id ' ||
8853: ' from ams_list_entries ' ||
8854: ' where list_header_id = ' || p_action_used_by_id ;
8855:
8856: l_const_sql1 := ' and LIST_SELECT_ACTION_ID = ';
8857:
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
9581: where list_header_id = p_list_header_id;
9582:
9583: l_list_field_mapped varchar2(1);
9584:
9973: --In Update mode need to update only the enabled entries from R12.
9974: /*
9975: if l_listheader_rec.generation_type = 'UPD' then
9976: write_to_act_log('Target group is generated in UPDATE mode', 'LIST', g_list_header_id,'HIGH');
9977: update ams_list_entries
9978: set newly_updated_flag = 'N' , enabled_flag = 'Y'
9979: where list_header_id = l_listheader_rec.list_header_id;
9980: */
9981: /********************************************************************
10067: -- added for R12. bmuthukr
10068: if l_listheader_rec.generation_type = 'STANDARD' then
10069: if g_remote_list_gen = 'N' then
10070: write_to_act_log('Identifying duplicate records(based on party id) in the target group.','LIST',g_list_header_id,'HIGH');
10071: UPDATE ams_list_entries a
10072: SET a.enabled_flag = 'N',
10073: a.marked_as_duplicate_flag = 'Y'
10074: WHERE a.list_header_id = p_list_header_id
10075: AND a.enabled_flag = 'Y'
10073: a.marked_as_duplicate_flag = 'Y'
10074: WHERE a.list_header_id = p_list_header_id
10075: AND a.enabled_flag = 'Y'
10076: AND a.rowid > (SELECT min(b.rowid)
10077: from ams_list_entries b
10078: where b.list_header_id = p_list_header_id
10079: and b.party_id = a.party_id
10080: and b.enabled_flag = 'Y'
10081: );
10104: l_no_of_duplicates := AMS_LISTDEDUPE_PVT.DEDUPE_LIST
10105: (p_list_header_id => p_list_header_id,
10106: p_enable_word_replacement_flag => 'Y', -- l_listheader_rec.enable_word_replacement_flag,
10107: p_send_to_log => 'Y', -- l_listheader_rec.enable_log_flag,
10108: p_object_name => 'AMS_LIST_ENTRIES');
10109: write_to_act_log('Deduplication done for target group.', 'LIST', g_list_header_id,'HIGH');
10110: else
10111: /* For Remote Target Group generation */
10112: write_to_act_log('Calling Execute_Remote_Dedupe_List for deduplication in remote instance.', 'LIST', g_list_header_id,'LOW');
10113: Execute_Remote_Dedupe_List
10114: (p_list_header_id => p_list_header_id,
10115: p_enable_word_replacement_flag => 'Y', -- l_listheader_rec.enable_word_replacement_flag,
10116: p_send_to_log => 'Y', -- l_listheader_rec.enable_log_flag,
10117: p_object_name => 'AMS_LIST_ENTRIES');
10118: end if;
10119: END IF; -- for l_list_rule_id
10120: end if;
10121: -- Call to suppresion to be done..
10338: end if;
10339:
10340: END if;
10341: --Code movded from tg api.
10342: /* UPDATE ams_list_entries set
10343: source_code = l_source_code ,
10344: arc_list_used_by_source = p_list_used_by ,
10345: source_code_for_id = p_list_used_by_id
10346: where list_header_id = g_list_header_id ;
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
10476: -- the set of rules associated with a list.
10477: CURSOR c_list_rules (my_list_header_id IN NUMBER)
10504: -- we also exclude any records where the dedupe flag is already set.
10505: CURSOR c_dedupe_keys (my_list_header_id IN NUMBER)
10506: IS
10507: SELECT DISTINCT dedupe_key, COUNT (dedupe_key)
10508: FROM ams_list_entries
10509: WHERE list_header_id = my_list_header_id
10510: GROUP BY dedupe_key;
10511:
10512:
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;
10532:
10544: l_msg_data varchar(2000);
10545: l_rank number := 0;
10546: BEGIN
10547: write_to_act_log('Executing procedure execute_remote_dedupe_list', 'LIST', p_list_header_id,'LOW');
10548: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
10549: l_sql_stmt1 := 'update ams_list_entries set dedupe_key = ';
10550: END IF;
10551:
10552: --performing check to see if this list has been deduped before.
10545: l_rank number := 0;
10546: BEGIN
10547: write_to_act_log('Executing procedure execute_remote_dedupe_list', 'LIST', p_list_header_id,'LOW');
10548: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
10549: l_sql_stmt1 := 'update ams_list_entries set dedupe_key = ';
10550: END IF;
10551:
10552: --performing check to see if this list has been deduped before.
10553: OPEN c_deduped_before (p_list_header_id);
10564: -- if a dedupe has never been perfomed then this field will contains
10565: -- NULLS and there is no
10566: -- need to perform this update
10567: IF (l_last_dedupe_by IS NOT NULL) THEN
10568: /* UPDATE ams_list_entries
10569: SET dedupe_key = NULL
10570: WHERE list_header_id = p_list_header_id; */
10571: write_to_act_log('Executing remote procedure with process type as DEDUPE1', 'LIST', p_list_header_id,'HIGH');
10572: execute immediate
10580: OUT l_return_status,
10581: OUT l_msg_count,
10582: OUT l_msg_data,
10583: p_list_header_id,
10584: 'UPDATE ams_list_entries SET dedupe_key = NULL WHERE list_header_id = '||to_char(p_list_header_id),
10585: l_null,
10586: l_null,
10587: OUT l_total_dup_recs,
10588: 'DEDUPE1';
10631: --we must ensure that the key gets reset to NULL for the list to
10632: -- ensure accurate results.
10633: -- removed khung 07/07/1999
10634: /*
10635: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
10636: UPDATE ams_list_entries
10637: SET dedupe_key = NULL
10638: WHERE list_header_id = p_list_header_id
10639: AND marked_as_duplicate_flag IS NULL;
10632: -- ensure accurate results.
10633: -- removed khung 07/07/1999
10634: /*
10635: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
10636: UPDATE ams_list_entries
10637: SET dedupe_key = NULL
10638: WHERE list_header_id = p_list_header_id
10639: AND marked_as_duplicate_flag IS NULL;
10640: COMMIT;
10887:
10888: write_to_act_log('Database link is '||l_dblink,'LIST',p_list_header_id,'LOW');
10889: Ams_Utility_Pvt.Write_Conc_log('Start Delete list entries from local instance : ');
10890: write_to_act_log('Deleting existing entries from the local instance.','LIST',p_list_header_id,'LOW');
10891: delete from ams_list_entries
10892: where list_header_id = p_list_header_id;
10893: Ams_Utility_Pvt.Write_Conc_log('End Delete list entries from local instance : ');
10894:
10895: l_insert_sql := 'insert into ams_list_entries
10891: delete from ams_list_entries
10892: where list_header_id = p_list_header_id;
10893: Ams_Utility_Pvt.Write_Conc_log('End Delete list entries from local instance : ');
10894:
10895: l_insert_sql := 'insert into ams_list_entries
10896: (list_header_id ,
10897: list_entry_id,
10898: object_version_number,
10899: source_code ,
11282: RANK
11283: )
11284: SELECT
11285: list_header_id,
11286: ams_list_entries_s.nextval,
11287: object_version_number,
11288: source_code ,
11289: source_code_for_id ,
11290: arc_list_used_by_source ,
11668: CUSTOM_COLUMN23,
11669: CUSTOM_COLUMN24,
11670: CUSTOM_COLUMN25,
11671: RANK
11672: from ams_list_entries@'||l_dblink||' '||
11673: 'where list_header_id = ' ||to_char(p_list_header_id);
11674: -- ||' and rownum between '||to_char(l_start_rownum)||' and '||to_char(l_end_rownum);
11675: l_no_of_chunks := 0;
11676: l_no_of_chunks := ceil(length(l_insert_sql)/2000 );
11823:
11824: cursor c_count_list_entries(cur_p_list_header_id number) is
11825: select sum(decode(enabled_flag,'Y',1,0)),
11826: sum(decode(marked_as_fatigued_flag,'Y',1,0))
11827: from ams_list_entries
11828: where list_header_id = cur_p_list_header_id ;
11829:
11830:
11831: begin
11837: p_msg_data => 'UPDATE_FOR_TRAFFIC_COP : Started ',
11838: p_msg_type => 'DEBUG');
11839:
11840: FORALL I in p_list_entry_id.first .. p_list_entry_id.last
11841: UPDATE ams_list_entries
11842: SET ENABLED_FLAG = 'N',
11843: MARKED_AS_FATIGUED_FLAG = 'Y'
11844: WHERE list_entry_id = p_list_entry_id(i)
11845: AND list_header_id = p_list_header_id ;
12008: write_to_act_log('Executing procedure calc_running_total', 'LIST', g_list_header_id,'LOW');
12009: END IF;
12010: l_const_sql := ' minus '||
12011: ' select list_entry_source_system_id ' ||
12012: ' from ams_list_entries ' ||
12013: ' where list_header_id = ' || p_action_used_by_id ;
12014: open c_last_gen;
12015: fetch c_last_gen into l_last_generation_success_flag;
12016: close c_last_gen;
12347: if p_list_action_type = 'INCLUDE' then
12348: x_include_sql := '
12349: select
12350: count(*) into :1
12351: from ams_list_entries
12352: where list_header_id = ' || p_incl_object_id ||
12353: ' and nvl(enabled_flag,' ||''''||'N'||''''||') = '||
12354: ''''||'Y'||'''' ||
12355: ' and list_entry_source_system_id in (' ;
12361: end loop;
12362: end if;
12363:
12364: x_std_sql := ' select list_entry_source_system_id
12365: from ams_list_entries
12366: where list_header_id = ' || p_incl_object_id ||
12367: ' and enabled_flag = ' || ''''||'Y' || '''' ;
12368: --WRITE_TO_ACT_LOG('std nclude ');
12369: WRITE_TO_ACT_LOG(x_std_sql, 'LIST', g_list_header_id,'LOW');
13082: web_rec hz_contact_point_v2pub.web_rec_type;
13083:
13084:
13085: cursor c_list_entries is
13086: select list_entry_id from ams_list_entries where list_header_id = p_list_header_id
13087: -- and party_id is null
13088: and nvl(tca_load_status,'x') not in ('SUCCESS','ERROR')
13089: and enabled_flag = 'Y'; --bmuthukr. R12 Need to upload only the enabled entries.
13090:
13127: open c_tca_columns;
13128: loop
13129: fetch c_tca_columns into list_column_name, tca_column_name;
13130: exit when c_tca_columns%notfound;
13131: l_string := 'begin select '||list_column_name||' into :l_entry_value from ams_list_entries where
13132: list_entry_id = '||to_char(l_list_entry_id)||' ; end;';
13133: -- DBMS_OUTPUT.PUT_LINE('l_string = '||l_string);
13134: execute immediate l_string using out l_entry_value;
13135: -- DBMS_OUTPUT.PUT_LINE('l_entry_value = '||l_entry_value);
13799:
13800: -- DBMS_OUTPUT.PUT_LINE('TCA Upload Process : x_tmp_var ->' || x_tmp_var );
13801: -- DBMS_OUTPUT.PUT_LINE('TCA Upload Process : x_tmp_var ->' || x_tmp_var1 );
13802: END LOOP;
13803: update ams_list_entries set error_flag = 'E',
13804: tca_load_status = 'ERROR',
13805: ENABLED_FLAG = 'N',
13806: --error_text = 'TCA API ERROR :'||substr(x_tmp_var1,1,3000)
13807: error_text = 'TCA API ERROR :'||x_tmp_var
13807: error_text = 'TCA API ERROR :'||x_tmp_var
13808: where list_entry_id = l_list_entry_id;
13809: end if;
13810: if l_party_id is null then
13811: update ams_list_entries set error_flag = 'E',
13812: tca_load_status = 'ERROR',
13813: ENABLED_FLAG = 'N',
13814: --error_text = 'TCA API ERROR :'||nvl(x_tmp_var,substr(x_tmp_var1,1,3000))
13815: error_text = 'TCA API ERROR :'||x_tmp_var
13816: where list_entry_id = l_list_entry_id;
13817: end if;
13818:
13819: if l_party_id is not null then
13820: update ams_list_entries set party_id = l_party_id,
13821: error_flag = 'S',
13822: ENABLED_FLAG = 'Y',
13823: tca_load_status = 'SUCCESS'
13824: where list_entry_id = l_list_entry_id;
13988: where list_header_id = p_list_header_id;
13989:
13990: cursor c2 is
13991: select 1
13992: from ams_list_entries
13993: where list_header_id = p_list_header_id
13994: and enabled_flag = 'Y'
13995: and rownum = 1;
13996:
14122: select sum(decode(enabled_flag,'N',0,1)),
14123: sum(decode(enabled_flag,'Y',0,1)),
14124: sum(1),
14125: sum(decode(manually_entered_flag,'Y',decode(enabled_flag,'Y','1',0),0))
14126: from ams_list_entries
14127: where list_header_id = p_list_header_id;
14128:
14129: begin
14130: open c_list_det;