eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AMS_LISTGENERATION_PKG dependencies on AMS_LIST_ENTRIES

Line 66: from ams_list_entries

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

Line 297: from ams_list_entries

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

Line 624: -- updates the count of ams_list_entries and list selections

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--

Line 637: FROM ams_list_entries e,

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

Line 654: FROM ams_list_entries e,

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'

Line 671: FROM ams_list_entries e,

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

Line 695: from ams_list_entries

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;

Line 702: select count(1) from ams_list_entries

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);

Line 958: --- of the list. Table Name : ams_list_tmp_entries and ams_list_entries

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

Line 975: -- deletes from ams_list_entries in case of standard type of generation

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: -------------------------------------------------------------------------------

Line 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');

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: -------------------------------------------------------------------------------

Line 987: DELETE FROM ams_list_entries

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

Line 989: write_to_act_log(sql%rowcount||' entries deleted from ams_list_entries in local instance.', 'LIST', g_list_header_id,'LOW');

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: *********************************************************************/

Line 1286: x_include_sql := ' insert into ams_list_entries

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,

Line 1388: ams_list_entries_s.nextval, import_source_line_id,

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' ||'''' || ','||

Line 1394: 'ams_list_entries_s.currval'|| ','||

1390: ''''||'NONE' ||'''' || ','||
1391: 0 || ',' ||
1392: ''''||'NONE' ||'''' || ','||
1393: ''''||'IMPH' ||'''' || ','||
1394: 'ams_list_entries_s.currval'|| ','||
1395: 530 || ','||
1396: ''''||'N' ||''''|| ','||
1397: ''''||'N' ||''''|| ','||
1398: ''''||'N' ||''''|| ','||

Line 1490: x_include_sql := ' insert into ams_list_entries

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 ,

Line 1580: ams_list_entries_s.nextval, ' ||

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' ||'''' || ','||

Line 1586: 'ams_list_entries_s.currval'|| ','||

1582: ''''||'NONE' ||'''' || ','||
1583: 0 || ',' ||
1584: ''''||'NONE' ||'''' || ','||
1585: ''''||'IMPH' ||'''' || ','||
1586: 'ams_list_entries_s.currval'|| ','||
1587: 530 || ','||
1588: ''''||'N' ||''''|| ','||
1589: ''''||'N' ||''''|| ','||
1590: ''''||'N' ||''''|| ','||

Line 1715: x_include_sql := 'insert into ams_list_entries

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 ,

Line 2095: ams_list_entries_s.nextval,

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' ||'''' || ','||

Line 2101: 'ams_list_entries_s.currval'|| ','||

2097: ''''||'NONE' ||'''' || ','||
2098: 0 || ',' ||
2099: ''''||'NONE' ||'''' || ','||
2100: ''''||'LIST' ||'''' || ','||
2101: 'ams_list_entries_s.currval'|| ','||
2102: 530 || ','||
2103: ''''||'N' ||''''|| ','||
2104: ''''||'N' ||''''|| ','||
2105: ''''||'N' ||''''|| ','||

Line 2470: from ams_list_entries

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 (' ;

Line 2520: list_entry_source_system_type from ams_list_entries

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

Line 2525: 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
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 ;

Line 2745: l_insert_sql := 'insert into ams_list_entries '||

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 , '||

Line 2780: 'ams_list_entries_s.nextval' || ','||

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' ||'''' || ','||

Line 2786: 'ams_list_entries_s.currval'|| ','||

2782: ''''||'NONE' ||'''' || ','||
2783: 0 || ',' ||
2784: ''''||'NONE' ||'''' || ','||
2785: ''''||'NONE' ||'''' || ','||
2786: 'ams_list_entries_s.currval'|| ','||
2787: 530 || ','||
2788: ''''||'N' ||''''|| ','||
2789: ''''||'N' ||''''|| ','||
2790: ''''||'N' ||''''|| ','||

Line 3873: from ams_list_entries

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: cursor c_query_temp_type is

Line 3926: ' from ams_list_entries ' ||

3922: BEGIN
3923: write_to_act_log('Executing process_list_actions','LIST',g_list_header_id, 'HIGH');
3924: l_const_sql := ' minus '||
3925: ' select list_entry_source_system_id ' ||
3926: ' from ams_list_entries ' ||
3927: ' where list_header_id = ' || p_action_used_by_id ;
3928:
3929: l_const_sql1 := ' and LIST_SELECT_ACTION_ID = ';
3930:

Line 4587: -- 1. Populate The AMS_LIST_ENTRIES table with a DISTINCT set of

4583: -- CREATE_LIST_ENTRIES
4584: --
4585: -- PURPOSE
4586:
4587: -- 1. Populate The AMS_LIST_ENTRIES table with a DISTINCT set of
4588: -- Entries from The AMS_LIST_TMP_ENTRIES table.
4589: -- 2. An Entry is considered UNIQUE by values in The list_entry_source_id
4590: -- and List_Entry_Source_Type fields from The AMS_LIST_TMP_ENTRIES table.
4591: -- 3. There may be more than one Entry in The AMS_LIST_TMP_ENTRIES table

Line 4598: -- Rows with this value are then inserted into the AMS_LIST_ENTRIES table.

4594: -- the Highest Rank is choosen, if Two Equivalent entries have
4595: -- the same rank then one entry is choosen arbitrarily.
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:

Line 4599: -- 5. if less entries exist in the AMS_LIST_ENTRIES_TABLE than specifed

4595: -- the same rank then one entry is choosen arbitrarily.
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:

Line 4654: INSERT INTO ams_List_Entries

4650: FETCH cur_get_created_by INTO l_created_by;
4651: CLOSE cur_get_created_by;
4652:
4653:
4654: INSERT INTO ams_List_Entries
4655: ( list_entry_id ,
4656: last_update_date ,
4657: last_updated_by ,
4658: creation_date ,

Line 4680: ( select ams_list_entries_s.nextval,

4676: exclude_in_triggered_list_flag ,
4677: enabled_flag,
4678: object_version_number
4679: )
4680: ( select ams_list_entries_s.nextval,
4681: sysdate,
4682: fnd_global.user_id,
4683: sysdate,
4684: nvl(l_created_by, fnd_global.user_id),

Line 4693: ams_list_entries_s.currval,

4689: s.incl_object_name,
4690: 'NONE',
4691: 0,
4692: h.list_used_by_id,
4693: ams_list_entries_s.currval,
4694: t.list_entry_source_id,
4695: t.list_entry_source_type,
4696: fnd_global.conc_login_id,
4697: 'N',

Line 4714: from ams_list_entries

4710: );
4711:
4712: select count(*)
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,

Line 4761: update ams_list_entries ale

4757: close c_check_tg;
4758:
4759: if l_tg_check = 'Y' then
4760: write_to_act_log('Generating target group(which has selections based on LIST) in update mode.','LIST',g_list_header_id,'LOW');
4761: update ams_list_entries ale
4762: set (
4763: ale.newly_updated_flag ,
4764: ale.enabled_flag ,
4765: ale.SUFFIX,

Line 5457: from ams_list_entries ail,

5453: ail.CUSTOM_COLUMN22,
5454: ail.CUSTOM_COLUMN23,
5455: ail.CUSTOM_COLUMN24,
5456: ail.CUSTOM_COLUMN25
5457: from ams_list_entries ail,
5458: ams_act_lists als
5459: where als.ACT_LIST_HEADER_ID = ale.list_select_action_id
5460: and als.LIST_HEADER_ID = ail.list_header_id
5461: and ail.list_entry_source_system_id = ale.list_entry_source_system_id

Line 5467: from ams_list_entries ail,

5463: and rownum <=1 )
5464: where arc_list_select_action_from = 'LIST'
5465: and list_header_id = p_list_header_id
5466: and exists (select 'x'
5467: from ams_list_entries ail,
5468: ams_act_lists als
5469: where als.ACT_LIST_HEADER_ID = ale.list_select_action_id
5470: and als.LIST_HEADER_ID = ail.list_header_id
5471: and ail.list_entry_source_system_id = ale.list_entry_source_system_id

Line 5481: update ams_list_entries ale

5477: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
5478:
5479: ELSE -- For List Generation
5480: write_to_act_log('Generating list(which has selections based on LIST) in update mode.','LIST',g_list_header_id,'LOW');
5481: update ams_list_entries ale
5482: set (
5483: ale.newly_updated_flag ,
5484: ale.enabled_flag ,
5485: ale.SUFFIX,

Line 6117: from ams_list_entries ail,

6113: ail.COL297 ,
6114: ail.COL298 ,
6115: ail.COL299 ,
6116: ail.COL300
6117: from ams_list_entries ail,
6118: ams_list_select_actions als
6119: where als.list_select_action_id = ale.list_select_action_id
6120: and als.incl_object_id = ail.list_header_id
6121: and ail.list_entry_source_system_id = ale.list_entry_source_system_id

Line 6127: from ams_list_entries ail,

6123: and rownum <=1 )
6124: where arc_list_select_action_from = 'LIST'
6125: and list_header_id = p_list_header_id
6126: and exists (select 'x'
6127: from ams_list_entries ail,
6128: ams_list_select_actions als
6129: where als.list_select_action_id = ale.list_select_action_id
6130: and als.incl_object_id = ail.list_header_id
6131: and ail.list_entry_source_system_id = ale.list_entry_source_system_id

Line 6159: update ams_list_entries ale

6155: close c1;
6156: write_to_act_log('Import type is '||l_b2b_flag,'LIST',g_list_header_id,'LOW');
6157: if l_b2b_flag = 'B2B' then
6158: write_to_act_log('Generating list(which has selections based on imported B2B list) in update mode.','LIST',g_list_header_id,'LOW');
6159: update ams_list_entries ale
6160: set (
6161: ale.newly_updated_flag ,
6162: -- ale.enabled_flag ,
6163: ale.ADDRESS_LINE1,

Line 6274: update ams_list_entries ale

6270: write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
6271: end if;
6272: if l_b2b_flag = 'B2C' then
6273: write_to_act_log('Generating list(which has selections based on imported B2C list) in update mode.','LIST',g_list_header_id,'LOW');
6274: update ams_list_entries ale
6275: set (
6276: ale.newly_updated_flag ,
6277: -- ale.enabled_flag ,
6278: ale.customer_name,

Line 6497: 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;

6493: g_list_header_id := p_list_header_id;
6494: end if;
6495:
6496: if nvl(g_remote_list,'N') = 'Y' then
6497: 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;
6498: if l_remote_cnt = 0 then
6499: write_to_act_log('No entries in remote schema for this list/target group. Cannot update.', 'LIST', g_list_header_id,'LOW');
6500: x_return_status := 'S';
6501: return;

Line 6511: l_update_str := 'UPDATE ams_list_entries SET (';

6507:
6508: write_to_act_log('Executing procedure get_list_entry_data.', 'LIST', g_list_header_id,'LOW');
6509: OPEN C_MAPPING_TYPES_USED(p_list_header_id);
6510: LOOP
6511: l_update_str := 'UPDATE ams_list_entries SET (';
6512: l_select_str := ' ) = ( SELECT ';
6513: l_header_clause := ' AND list_header_id = '||
6514: to_char(p_list_header_id);
6515: l_type_clause := ' AND list_entry_source_system_type = ';

Line 6633: l_update_str := 'UPDATE ams_list_entries SET (';

6629: end if;
6630: l_iterator := 0;
6631: l_list_entry_columns := l_NULL_table;
6632: l_source_columns := l_NULL_table;
6633: l_update_str := 'UPDATE ams_list_entries SET (';
6634: l_select_str := ' ) = ( SELECT ';
6635: l_header_clause := ' AND list_header_id = '||
6636: to_char(p_list_header_id);
6637: l_type_clause := ' AND list_entry_source_system_type = ';

Line 6744: -- entries into the ams_list_entries table.

6740: -- START OF COMMENTS
6741: -- NAME : GENERATE_LIST.
6742: -- PURPOSE
6743: -- 1. Public Procedure which when called will generate a set of list
6744: -- entries into the ams_list_entries table.
6745: -- HISTORY
6746: -- 06/01/1999 tdonohoe created
6747: -- 01/24/2001 gjoby Re-Created for Hornet
6748: -- END OF COMMENTS

Line 7284: update ams_list_entries

7280: --So no need to enable all the entries before generation.
7281: -- if l_listheader_rec.generation_type = 'UPDATE' then
7282: /* if l_listheader_rec.generation_type = 'UPD' then
7283: write_to_act_log(' List is generated in UPDATE mode', 'LIST', g_list_header_id,'HIGH');
7284: update ams_list_entries
7285: set newly_updated_flag = 'N',
7286: enabled_flag = 'Y'
7287: where list_header_id = l_listheader_rec.list_header_id;
7288: */

Line 7407: update ams_list_entries

7403: -- if l_listheader_rec.generation_type = 'UPDATE' then
7404: if l_listheader_rec.generation_type = 'UPD' then
7405: l_error_position := '<- set enabled flag for gen type UPDATE ';
7406: if g_remote_list <> 'Y' then
7407: update ams_list_entries
7408: set enabled_flag = 'N'
7409: where newly_updated_flag = 'N'
7410: and list_header_id = l_listheader_rec.list_header_id;
7411: write_to_act_log(sql%rowcount||' entries disabled when generating list in update mode','LIST',g_list_header_id,'HIGH');

Line 7450: UPDATE ams_list_entries a

7446: */
7447: if g_remote_list_gen = 'N' then
7448: if l_listheader_rec.generation_type = 'STANDARD' then --R12 applicable only for full refresh mode
7449: write_to_act_log('Identifying duplicate records(based on party id) in the list.','LIST',g_list_header_id,'HIGH');
7450: UPDATE ams_list_entries a
7451: SET a.enabled_flag = 'N',
7452: a.marked_as_duplicate_flag = 'Y'
7453: WHERE a.list_header_id = p_list_header_id
7454: and a.enabled_flag = 'Y'

Line 7456: from ams_list_entries b

7452: a.marked_as_duplicate_flag = 'Y'
7453: WHERE a.list_header_id = p_list_header_id
7454: and a.enabled_flag = 'Y'
7455: AND a.rowid > (SELECT min(b.rowid)
7456: from ams_list_entries b
7457: where b.list_header_id = p_list_header_id
7458: and b.party_id = a.party_id
7459: and b.enabled_flag = 'Y'
7460: );

Line 7463: /* UPDATE ams_list_entries a

7459: and b.enabled_flag = 'Y'
7460: );
7461: write_to_act_log('No of duplicates identified.'||sql%rowcount,'LIST',g_list_header_id,'HIGH');
7462: write_to_act_log('Duplicate records(based on party) identified and marked.','LIST',g_list_header_id,'LOW');
7463: /* UPDATE ams_list_entries a
7464: SET a.enabled_flag = 'N',
7465: a.marked_as_duplicate_flag = 'Y'
7466: WHERE a.list_header_id = p_list_header_id
7467: and a.enabled_flag = 'Y'

Line 7470: from ams_list_entries b

7466: WHERE a.list_header_id = p_list_header_id
7467: and a.enabled_flag = 'Y'
7468: -- AND a.rowid > (SELECT min(b.rowid)
7469: AND a.rank > (SELECT min(b.rank)
7470: from ams_list_entries b
7471: where b.list_header_id = p_list_header_id
7472: and b.party_id = a.party_id
7473: and b.enabled_flag = 'Y'
7474: );*/

Line 7499: p_object_name => 'AMS_LIST_ENTRIES');

7495: (p_list_header_id => p_list_header_id,
7496: p_enable_word_replacement_flag => 'Y',
7497: -- l_listheader_rec.enable_word_replacement_flag,
7498: p_send_to_log => l_listheader_rec.enable_log_flag,
7499: p_object_name => 'AMS_LIST_ENTRIES');
7500: write_to_act_log('Deduplication done for this list.', 'LIST', g_list_header_id,'HIGH');
7501: else
7502: /* For Remote list generation */
7503: write_to_act_log('Call Execute_Remote_Dedupe_List for deduplication in remote instance.', 'LIST', g_list_header_id,'HIGH');

Line 7509: p_object_name => 'AMS_LIST_ENTRIES');

7505: (p_list_header_id => p_list_header_id,
7506: p_enable_word_replacement_flag => 'Y',
7507: -- l_listheader_rec.enable_word_replacement_flag,
7508: p_send_to_log => 'Y', -- l_listheader_rec.enable_log_flag,
7509: p_object_name => 'AMS_LIST_ENTRIES');
7510: write_to_act_log('Deduplication done for this list in remote instance.', 'LIST', g_list_header_id,'LOW');
7511: end if;
7512: end if;
7513: end if;

Line 8758: ' from ams_list_entries ' ||

8754: --END IF;
8755:
8756: l_const_sql := ' minus '||
8757: ' select list_entry_source_system_id ' ||
8758: ' from ams_list_entries ' ||
8759: ' where list_header_id = ' || p_action_used_by_id ;
8760:
8761: l_const_sql1 := ' and LIST_SELECT_ACTION_ID = ';
8762:

Line 9479: select count(1) from ams_list_entries

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
9480: where list_header_id = p_list_header_id;
9481:
9482: l_list_field_mapped varchar2(1);
9483:

Line 9876: update ams_list_entries

9872: --In Update mode need to update only the enabled entries from R12.
9873: /*
9874: if l_listheader_rec.generation_type = 'UPD' then
9875: write_to_act_log('Target group is generated in UPDATE mode', 'LIST', g_list_header_id,'HIGH');
9876: update ams_list_entries
9877: set newly_updated_flag = 'N' , enabled_flag = 'Y'
9878: where list_header_id = l_listheader_rec.list_header_id;
9879: */
9880: /********************************************************************

Line 9970: UPDATE ams_list_entries a

9966: -- added for R12. bmuthukr
9967: if l_listheader_rec.generation_type = 'STANDARD' then
9968: if g_remote_list_gen = 'N' then
9969: write_to_act_log('Identifying duplicate records(based on party id) in the target group.','LIST',g_list_header_id,'HIGH');
9970: UPDATE ams_list_entries a
9971: SET a.enabled_flag = 'N',
9972: a.marked_as_duplicate_flag = 'Y'
9973: WHERE a.list_header_id = p_list_header_id
9974: AND a.enabled_flag = 'Y'

Line 9976: from ams_list_entries b

9972: a.marked_as_duplicate_flag = 'Y'
9973: WHERE a.list_header_id = p_list_header_id
9974: AND a.enabled_flag = 'Y'
9975: AND a.rowid > (SELECT min(b.rowid)
9976: from ams_list_entries b
9977: where b.list_header_id = p_list_header_id
9978: and b.party_id = a.party_id
9979: and b.enabled_flag = 'Y'
9980: );

Line 10007: p_object_name => 'AMS_LIST_ENTRIES');

10003: l_no_of_duplicates := AMS_LISTDEDUPE_PVT.DEDUPE_LIST
10004: (p_list_header_id => p_list_header_id,
10005: p_enable_word_replacement_flag => 'Y', -- l_listheader_rec.enable_word_replacement_flag,
10006: p_send_to_log => 'Y', -- l_listheader_rec.enable_log_flag,
10007: p_object_name => 'AMS_LIST_ENTRIES');
10008: write_to_act_log('Deduplication done for target group.', 'LIST', g_list_header_id,'HIGH');
10009: else
10010: /* For Remote Target Group generation */
10011: write_to_act_log('Calling Execute_Remote_Dedupe_List for deduplication in remote instance.', 'LIST', g_list_header_id,'LOW');

Line 10016: p_object_name => 'AMS_LIST_ENTRIES');

10012: Execute_Remote_Dedupe_List
10013: (p_list_header_id => p_list_header_id,
10014: p_enable_word_replacement_flag => 'Y', -- l_listheader_rec.enable_word_replacement_flag,
10015: p_send_to_log => 'Y', -- l_listheader_rec.enable_log_flag,
10016: p_object_name => 'AMS_LIST_ENTRIES');
10017: end if;
10018: END IF; -- for l_list_rule_id
10019: end if;
10020: -- Call to suppresion to be done..

Line 10241: /* UPDATE ams_list_entries set

10237: end if;
10238:
10239: END if;
10240: --Code movded from tg api.
10241: /* UPDATE ams_list_entries set
10242: source_code = l_source_code ,
10243: arc_list_used_by_source = p_list_used_by ,
10244: source_code_for_id = p_list_used_by_id
10245: where list_header_id = g_list_header_id ;

Line 10372: ,p_object_name VARCHAR2 := 'AMS_LIST_ENTRIES'

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
10375: -- the set of rules associated with a list.
10376: CURSOR c_list_rules (my_list_header_id IN NUMBER)

Line 10407: FROM ams_list_entries

10403: -- we also exclude any records where the dedupe flag is already set.
10404: CURSOR c_dedupe_keys (my_list_header_id IN NUMBER)
10405: IS
10406: SELECT DISTINCT dedupe_key, COUNT (dedupe_key)
10407: FROM ams_list_entries
10408: WHERE list_header_id = my_list_header_id
10409: GROUP BY dedupe_key;
10410:
10411:

Line 10427: l_dedupe_key ams_list_entries.dedupe_key%TYPE;

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;
10431:

Line 10447: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN

10443: l_msg_data varchar(2000);
10444: l_rank number := 0;
10445: BEGIN
10446: write_to_act_log('Executing procedure execute_remote_dedupe_list', 'LIST', p_list_header_id,'LOW');
10447: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
10448: l_sql_stmt1 := 'update ams_list_entries set dedupe_key = ';
10449: END IF;
10450:
10451: --performing check to see if this list has been deduped before.

Line 10448: l_sql_stmt1 := 'update ams_list_entries set dedupe_key = ';

10444: l_rank number := 0;
10445: BEGIN
10446: write_to_act_log('Executing procedure execute_remote_dedupe_list', 'LIST', p_list_header_id,'LOW');
10447: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
10448: l_sql_stmt1 := 'update ams_list_entries set dedupe_key = ';
10449: END IF;
10450:
10451: --performing check to see if this list has been deduped before.
10452: OPEN c_deduped_before (p_list_header_id);

Line 10467: /* UPDATE ams_list_entries

10463: -- if a dedupe has never been perfomed then this field will contains
10464: -- NULLS and there is no
10465: -- need to perform this update
10466: IF (l_last_dedupe_by IS NOT NULL) THEN
10467: /* UPDATE ams_list_entries
10468: SET dedupe_key = NULL
10469: WHERE list_header_id = p_list_header_id; */
10470: write_to_act_log('Executing remote procedure with process type as DEDUPE1', 'LIST', p_list_header_id,'HIGH');
10471: execute immediate

Line 10483: 'UPDATE ams_list_entries SET dedupe_key = NULL WHERE list_header_id = '||to_char(p_list_header_id),

10479: OUT l_return_status,
10480: OUT l_msg_count,
10481: OUT l_msg_data,
10482: p_list_header_id,
10483: 'UPDATE ams_list_entries SET dedupe_key = NULL WHERE list_header_id = '||to_char(p_list_header_id),
10484: l_null,
10485: l_null,
10486: OUT l_total_dup_recs,
10487: 'DEDUPE1';

Line 10534: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN

10530: --we must ensure that the key gets reset to NULL for the list to
10531: -- ensure accurate results.
10532: -- removed khung 07/07/1999
10533: /*
10534: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
10535: UPDATE ams_list_entries
10536: SET dedupe_key = NULL
10537: WHERE list_header_id = p_list_header_id
10538: AND marked_as_duplicate_flag IS NULL;

Line 10535: UPDATE ams_list_entries

10531: -- ensure accurate results.
10532: -- removed khung 07/07/1999
10533: /*
10534: IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
10535: UPDATE ams_list_entries
10536: SET dedupe_key = NULL
10537: WHERE list_header_id = p_list_header_id
10538: AND marked_as_duplicate_flag IS NULL;
10539: COMMIT;

Line 10790: delete from ams_list_entries

10786:
10787: write_to_act_log('Database link is '||l_dblink,'LIST',p_list_header_id,'LOW');
10788: Ams_Utility_Pvt.Write_Conc_log('Start Delete list entries from local instance : ');
10789: write_to_act_log('Deleting existing entries from the local instance.','LIST',p_list_header_id,'LOW');
10790: delete from ams_list_entries
10791: where list_header_id = p_list_header_id;
10792: Ams_Utility_Pvt.Write_Conc_log('End Delete list entries from local instance : ');
10793:
10794: l_insert_sql := 'insert into ams_list_entries

Line 10794: l_insert_sql := 'insert into ams_list_entries

10790: delete from ams_list_entries
10791: where list_header_id = p_list_header_id;
10792: Ams_Utility_Pvt.Write_Conc_log('End Delete list entries from local instance : ');
10793:
10794: l_insert_sql := 'insert into ams_list_entries
10795: (list_header_id ,
10796: list_entry_id,
10797: object_version_number,
10798: source_code ,

Line 11185: ams_list_entries_s.nextval,

11181: RANK
11182: )
11183: SELECT
11184: list_header_id,
11185: ams_list_entries_s.nextval,
11186: object_version_number,
11187: source_code ,
11188: source_code_for_id ,
11189: arc_list_used_by_source ,

Line 11571: from ams_list_entries@'||l_dblink||' '||

11567: CUSTOM_COLUMN23,
11568: CUSTOM_COLUMN24,
11569: CUSTOM_COLUMN25,
11570: RANK
11571: from ams_list_entries@'||l_dblink||' '||
11572: 'where list_header_id = ' ||to_char(p_list_header_id);
11573: -- ||' and rownum between '||to_char(l_start_rownum)||' and '||to_char(l_end_rownum);
11574: l_no_of_chunks := 0;
11575: l_no_of_chunks := ceil(length(l_insert_sql)/2000 );

Line 11726: from ams_list_entries

11722:
11723: cursor c_count_list_entries(cur_p_list_header_id number) is
11724: select sum(decode(enabled_flag,'Y',1,0)),
11725: sum(decode(marked_as_fatigued_flag,'Y',1,0))
11726: from ams_list_entries
11727: where list_header_id = cur_p_list_header_id ;
11728:
11729:
11730: begin

Line 11740: UPDATE ams_list_entries

11736: p_msg_data => 'UPDATE_FOR_TRAFFIC_COP : Started ',
11737: p_msg_type => 'DEBUG');
11738:
11739: FORALL I in p_list_entry_id.first .. p_list_entry_id.last
11740: UPDATE ams_list_entries
11741: SET ENABLED_FLAG = 'N',
11742: MARKED_AS_FATIGUED_FLAG = 'Y'
11743: WHERE list_entry_id = p_list_entry_id(i)
11744: AND list_header_id = p_list_header_id ;

Line 11911: ' from ams_list_entries ' ||

11907: write_to_act_log('Executing procedure calc_running_total', 'LIST', g_list_header_id,'LOW');
11908: END IF;
11909: l_const_sql := ' minus '||
11910: ' select list_entry_source_system_id ' ||
11911: ' from ams_list_entries ' ||
11912: ' where list_header_id = ' || p_action_used_by_id ;
11913: open c_last_gen;
11914: fetch c_last_gen into l_last_generation_success_flag;
11915: close c_last_gen;

Line 12250: from ams_list_entries

12246: if p_list_action_type = 'INCLUDE' then
12247: x_include_sql := '
12248: select
12249: count(*) into :1
12250: from ams_list_entries
12251: where list_header_id = ' || p_incl_object_id ||
12252: ' and nvl(enabled_flag,' ||''''||'N'||''''||') = '||
12253: ''''||'Y'||'''' ||
12254: ' and list_entry_source_system_id in (' ;

Line 12264: from ams_list_entries

12260: end loop;
12261: end if;
12262:
12263: x_std_sql := ' select list_entry_source_system_id
12264: from ams_list_entries
12265: where list_header_id = ' || p_incl_object_id ||
12266: ' and enabled_flag = ' || ''''||'Y' || '''' ;
12267: --WRITE_TO_ACT_LOG('std nclude ');
12268: WRITE_TO_ACT_LOG(x_std_sql, 'LIST', g_list_header_id,'LOW');

Line 12985: select list_entry_id from ams_list_entries where list_header_id = p_list_header_id

12981: web_rec hz_contact_point_v2pub.web_rec_type;
12982:
12983:
12984: cursor c_list_entries is
12985: select list_entry_id from ams_list_entries where list_header_id = p_list_header_id
12986: -- and party_id is null
12987: and nvl(tca_load_status,'x') not in ('SUCCESS','ERROR')
12988: and enabled_flag = 'Y'; --bmuthukr. R12 Need to upload only the enabled entries.
12989:

Line 13030: l_string := 'begin select '||list_column_name||' into :l_entry_value from ams_list_entries where

13026: open c_tca_columns;
13027: loop
13028: fetch c_tca_columns into list_column_name, tca_column_name;
13029: exit when c_tca_columns%notfound;
13030: l_string := 'begin select '||list_column_name||' into :l_entry_value from ams_list_entries where
13031: list_entry_id = '||to_char(l_list_entry_id)||' ; end;';
13032: -- DBMS_OUTPUT.PUT_LINE('l_string = '||l_string);
13033: execute immediate l_string using out l_entry_value;
13034: -- DBMS_OUTPUT.PUT_LINE('l_entry_value = '||l_entry_value);

Line 13702: update ams_list_entries set error_flag = 'E',

13698:
13699: -- DBMS_OUTPUT.PUT_LINE('TCA Upload Process : x_tmp_var ->' || x_tmp_var );
13700: -- DBMS_OUTPUT.PUT_LINE('TCA Upload Process : x_tmp_var ->' || x_tmp_var1 );
13701: END LOOP;
13702: update ams_list_entries set error_flag = 'E',
13703: tca_load_status = 'ERROR',
13704: ENABLED_FLAG = 'N',
13705: --error_text = 'TCA API ERROR :'||substr(x_tmp_var1,1,3000)
13706: error_text = 'TCA API ERROR :'||x_tmp_var

Line 13710: update ams_list_entries set error_flag = 'E',

13706: error_text = 'TCA API ERROR :'||x_tmp_var
13707: where list_entry_id = l_list_entry_id;
13708: end if;
13709: if l_party_id is null then
13710: update ams_list_entries set error_flag = 'E',
13711: tca_load_status = 'ERROR',
13712: ENABLED_FLAG = 'N',
13713: --error_text = 'TCA API ERROR :'||nvl(x_tmp_var,substr(x_tmp_var1,1,3000))
13714: error_text = 'TCA API ERROR :'||x_tmp_var

Line 13719: update ams_list_entries set party_id = l_party_id,

13715: where list_entry_id = l_list_entry_id;
13716: end if;
13717:
13718: if l_party_id is not null then
13719: update ams_list_entries set party_id = l_party_id,
13720: error_flag = 'S',
13721: ENABLED_FLAG = 'Y',
13722: tca_load_status = 'SUCCESS'
13723: where list_entry_id = l_list_entry_id;

Line 13891: from ams_list_entries

13887: where list_header_id = p_list_header_id;
13888:
13889: cursor c2 is
13890: select 1
13891: from ams_list_entries
13892: where list_header_id = p_list_header_id
13893: and enabled_flag = 'Y'
13894: and rownum = 1;
13895:

Line 14025: from ams_list_entries

14021: select sum(decode(enabled_flag,'N',0,1)),
14022: sum(decode(enabled_flag,'Y',0,1)),
14023: sum(1),
14024: sum(decode(manually_entered_flag,'Y',decode(enabled_flag,'Y','1',0),0))
14025: from ams_list_entries
14026: where list_header_id = p_list_header_id;
14027:
14028: begin
14029: open c_list_det;