[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_no_of_rows_ini_selected number :=0;
select count(1)
from ams_list_entries
where list_header_id = g_list_header_id
and enabled_flag = 'Y';
INSERT INTO ams_act_logs(
activity_log_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,act_log_used_by_id
,arc_act_log_used_by
,log_transaction_id
,log_message_text
)
VALUES (
ams_act_logs_s.NEXTVAL
,g_date(i)
,FND_GLOBAL.User_Id
,g_date(i)
,FND_GLOBAL.User_Id
,FND_GLOBAL.Conc_Login_Id
,1
,g_list_header_id
,'LIST'
,ams_act_logs_transaction_id_s.NEXTVAL
,g_message_table(i)
) ;*/
select jtf.user_id
from jtf_rs_resource_extns jtf, ams_list_headers_all hd
where jtf.resource_id = hd.owner_user_id
and hd.list_header_id = l_list_header_id;
delete from ams_act_logs
where arc_act_log_used_by = 'LIST'
and act_log_used_by_id = p_list_header_id ;
INSERT INTO ams_act_logs(
activity_log_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,act_log_used_by_id
,arc_act_log_used_by
,log_transaction_id
,log_message_text
)
VALUES (
ams_act_logs_s.NEXTVAL
,sysdate
,FND_GLOBAL.User_Id
,sysdate
,FND_GLOBAL.User_Id
,FND_GLOBAL.Conc_Login_Id
,1
,nvl(p_log_used_by_id,g_list_header_id)
,'LIST'
,ams_act_logs_transaction_id_s.NEXTVAL
,p_msg_data
) ;
PROCEDURE get_count(p_list_select_action_id in number,
p_order_number in number,
p_incl_type in varchar2 default 'OTHERS',
p_sql_string in varchar2 default null) is
l_no_of_rows_in_list number := 0;
l_rep_str varchar2(32767) := 'SELECT COUNT(1) FROM ';
l_selection_cnt number := 0;
select generation_type
from ams_list_headers_all
where list_header_id = g_list_header_id;
select distribution_pct,incl_object_id
from ams_list_select_actions
where list_select_action_id = p_list_select_action_id;
select no_of_rows_active
from ams_list_headers_all
where list_header_id = l_incl_object_id;
select decode(import_type,'B2C','PERSON_LIST','ORGANIZATION_CONTACT_LIST')
from ams_imp_list_headers_all
where import_list_header_id = l_incl_object_id;
select count(1)
from ams_hz_b2b_mapping_v
where import_list_header_id = l_incl_object_id
and enabled_flag = 'Y';
select count(1)
from ams_hz_b2c_mapping_v
where import_list_header_id = l_incl_object_id
and enabled_flag = 'Y';
select count(1)
from ams_list_entries
where list_header_id = g_list_header_id
and list_select_action_id = p_list_select_action_id;
write_to_act_log('All the rows will be taken from this selection','LIST',g_list_header_id,'HIGH');
write_to_act_log('Total # of rows from this selection is '||l_cnt , 'LIST', g_list_header_id,'LOW');
write_to_act_log('No of rows to be taken from this selection is '||l_no_of_rows_reqd,'LIST',g_list_header_id,'LOW');
fetch c_get_cnt_from_sel into l_selection_cnt;
if l_no_of_rows_reqd > l_selection_cnt then
l_no_of_rows_reqd := l_no_of_rows_reqd - l_selection_cnt;
write_to_act_log('Already there are '||l_selection_cnt||' entries. Need to insert '||l_no_of_rows_reqd||' entries.','LIST',g_list_header_id,'LOW');
elsif l_no_of_rows_reqd <= l_selection_cnt then
l_no_of_rows_reqd := 0;
write_to_act_log('Already there are '||l_selection_cnt||' entries. No need to insert.','LIST',g_list_header_id,'LOW');
PROCEDURE UPDATE_REMOTE_LIST_HEADER(P_LIST_HEADER_ID NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
CURSOR C1(p_list_header_id number) IS
SELECT list_header_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
list_used_by_id,
arc_list_used_by,
list_type,
status_code,
status_date,
generation_type,
owner_user_id,
row_selection_type,
no_of_rows_max_requested,
main_random_pct_row_selection,
ctrl_gen_mode,
ctrl_status_code,
ctrl_conc_job_id,
status_code_old,
ctrl_limit_of_error,
ctrl_req_resp_rate,
ctrl_conf_level,
ctrl_random_nth_row_selection,
ctrl_random_pct_row_selection
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id;
write_to_act_log('Passing the details to remote database to update the header info.', 'LIST', p_list_header_id,'HIGH');
ams_remote_listgen_pkg.remote_insert_list_headers'||'@'||g_database_link||'(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28)'||';'||
c1_rec.last_update_date,
c1_rec.last_updated_by,
c1_rec.creation_date,
c1_rec.created_by,
c1_rec.last_update_login,
c1_rec.list_used_by_id,
c1_rec.arc_list_used_by,
c1_rec.list_type,
c1_rec.status_code,
c1_rec.status_date,
c1_rec.generation_type,
c1_rec.owner_user_id,
c1_rec.row_selection_type,
c1_rec.no_of_rows_max_requested,
c1_rec.main_random_pct_row_selection,
c1_rec.ctrl_gen_mode,
c1_rec.ctrl_status_code,
c1_rec.ctrl_conc_job_id,
c1_rec.status_code_old,
c1_rec.ctrl_limit_of_error,
c1_rec.ctrl_req_resp_rate,
c1_rec.ctrl_conf_level,
c1_rec.ctrl_random_nth_row_selection,
c1_rec.ctrl_random_pct_row_selection,
out x_msg_count,
out x_msg_data,
out x_return_status;
write_to_act_log('Header info updated in the remote database.', 'LIST', g_list_header_id,'HIGH');
write_to_act_log('Error in executing remote_insert_list_headers procedure', 'LIST', g_list_header_id,'HIGH');
write_to_act_log('remote_insert_list_headers procedure executed successfully.' ,'LIST',g_list_header_id,'LOW');
write_to_act_log('Error while executing update_remote_list_header '||sqlcode||' '||sqlerrm,'LIST',g_list_header_id,'HIGH');
END UPDATE_REMOTE_LIST_HEADER;
PROCEDURE update_list_result_text is
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE ams_list_headers_all
SET result_text = result_text||' '|| l_msg_data
WHERE list_header_id = g_list_header_id;
end update_list_result_text;
PROCEDURE UPDATE_LIST_ACTION_DETS( p_list_select_action_id in t_number ,
p_no_of_rows_used in t_number ,
p_no_of_rows_available in t_number ,
p_no_of_rows_duplicates in t_number ) IS
BEGIN
write_to_act_log(p_msg_data => 'Updating list selections. No of selections = '||to_char(p_list_select_action_id.count),
p_arc_log_used_by => 'LIST',
p_log_used_by_id => g_list_header_id,
p_level => 'LOW');
FORALL I in p_list_select_action_id.first .. p_list_select_action_id.last
UPDATE ams_list_select_actions
SET no_of_rows_used = p_no_of_rows_used(i),
no_of_rows_available = p_no_of_rows_available(i),
no_of_rows_duplicates = p_no_of_rows_duplicates(i)
WHERE list_select_action_id = p_list_select_action_id(i);
write_to_act_log(p_msg_data => 'Error while executing procedure update_list_action_dets '||sqlcode||sqlerrm,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => g_list_header_id,
p_level =>'HIGH');
END UPDATE_LIST_ACTION_DETS;
procedure Update_List_Dets(p_list_header_id IN NUMBER,
x_return_status OUT NOCOPY varchar2) is
--------------------------------------------------------------
--This Variable stores the result of cursor c_list_gen_type.--
--------------------------------------------------------------
l_generation_type AMS_LIST_HEADERS_ALL.GENERATION_TYPE%TYPE;
SELECT b.no_of_rows_active Count,
sum(decode(e.enabled_flag,'Y',1,0)),
e.List_select_action_id,
a.distribution_pct,
a.rank rank_col,
sum(decode(e.marked_as_duplicate_flag,'Y',1,0))
FROM ams_list_entries e,
ams_list_select_actions a
,ams_list_headers_all b
WHERE e.list_header_id = p_list_header_id
AND e.list_select_action_id = a.list_select_action_id
AND a.arc_action_used_by = 'LIST'
AND a.action_used_by_id = p_list_header_id
AND b.list_header_id = a.incl_object_id
and a.arc_incl_object_from = 'LIST'
GROUP BY e.list_select_action_id,a.distribution_pct,a.rank,b.no_of_rows_active
UNION ALL
SELECT COUNT(e.List_Entry_Id) Count,
sum(decode(e.enabled_flag,'Y',1,0)),
e.List_select_action_id,
a.distribution_pct,
a.rank rank_col,
sum(decode(e.marked_as_duplicate_flag,'Y',1,0))
FROM ams_list_entries e,
ams_list_select_actions a
WHERE e.list_header_id = p_list_header_id
AND e.list_select_action_id = a.list_select_action_id
AND a.arc_action_used_by = 'LIST'
AND a.action_used_by_id = p_list_header_id
and a.arc_incl_object_from <> 'LIST'
GROUP BY e.list_select_action_id,a.distribution_pct,a.rank
ORDER BY rank_col desc ;
SELECT COUNT(e.List_Entry_Id),
sum(decode(e.enabled_flag,'Y',1,0)),
a.List_select_action_id,
a.distribution_pct,
a.rank,
sum(decode(e.marked_as_duplicate_flag,'Y',1,0))
FROM ams_list_entries e,
ams_list_select_actions a,
ams_act_lists t
WHERE e.list_header_id = p_list_header_id
AND e.list_select_action_id = t.act_list_header_id
and t.list_header_id = a.INCL_OBJECT_ID
AND a.arc_action_used_by = 'LIST'
AND a.action_used_by_id = p_list_header_id
GROUP BY a.list_select_action_id,a.distribution_pct,a.rank
ORDER BY a.rank desc ;
select sum(decode(enabled_flag,'N',0,1)),
sum(decode(enabled_flag,'Y',0,1)),
sum(1),
sum(decode(part_of_control_group_flag,'Y',1,0)),
sum(decode(marked_as_random_flag,'Y',1,0)),
sum(decode(marked_as_duplicate_flag,'Y',1,0)),
sum(decode(manually_entered_flag,
'Y',decode(enabled_flag,'Y','1',0),
0)),
sum(decode(MARKED_AS_SUPPRESSED_FLAG,'Y',1,0)),
sum(decode(MARKED_AS_FATIGUED_FLAG,'Y',1,0)),
sum(decode(TCA_LOAD_STATUS,'ERROR',1,0))
from ams_list_entries
where list_header_id = cur_p_list_header_id ;
select count(1) from ams_list_entries
where list_header_id = p_list_header_id
and TCA_LOAD_STATUS = 'ERROR';
select list_type from ams_list_headers_all where list_header_id = p_list_header_id;
l_list_select_action_id NUMBER;
t_list_select_action_id t_number;
select list_type from ams_list_headers_all
where list_header_id = p_list_header_id;
write_to_act_log('Executing update_list_dets to update the list header details','LIST', g_list_header_id,'LOW');
/* Bugfix: 3799191. Modified by rrajesh. Number of TCA error records not getting updated for remote based TG */
Open C_GET_LIST_TYPE(p_list_header_id);
write_to_act_log('Calling remote procedure to update list header details','LIST', g_list_header_id,'LOW');
SELECT nvl(no_of_rows_min_requested,0)
INTO l_min_rows
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id;
update ams_list_headers_all
set no_of_rows_in_list = nvl(l_no_of_rows_in_list,0),
no_of_rows_active = nvl(l_no_of_rows_active,0),
no_of_rows_inactive = nvl(l_no_of_rows_inactive,0),
no_of_rows_in_ctrl_group = nvl(l_no_of_rows_in_ctrl_group,0),
no_of_rows_random = nvl(l_no_of_rows_random,0),
no_of_rows_duplicates = nvl(l_no_of_rows_duplicates,0),
no_of_rows_manually_entered = nvl(l_no_of_rows_manually_entered,0),
no_of_rows_suppressed = nvl(l_no_of_rows_suppressed,0),
NO_OF_ROWS_FATIGUED = nvl(l_no_of_rows_fatigued,0),
TCA_FAILED_RECORDS = nvl(l_TCA_FAILED_RECORDS,0),
last_generation_success_flag = decode(l_new_status_id,303,'Y','N'),
-- MIGRATION_DATE = decode(g_remote_list_gen,'Y',null,MIGRATION_DATE),
status_code = l_new_status,
user_status_id = l_new_status_id,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
no_of_rows_initially_selected = g_no_of_rows_ini_selected,
remote_gen_flag = nvl(g_remote_list_gen,'N')
WHERE list_header_id = p_list_header_id;
write_to_act_log('List header table updated','LIST', g_list_header_id,'LOW');
update ams_list_headers_all
set status_code = 'FAILED',
user_status_id = 311
WHERE list_header_id = p_list_header_id;
write_to_act_log('Updating list select actions for each selection.'||l_list_type, 'LIST', g_list_header_id,'LOW');
l_list_select_action_id,
l_distribution_pct,
l_rank,
l_no_of_rows_dup;
t_list_select_action_id(l_iterator) := l_list_select_action_id;
write_to_act_log('Calling update_list_action_dets to update the list selection '||l_list_select_action_id, 'LIST', g_list_header_id,'LOW');
update_list_action_dets( t_list_select_action_id,
t_no_of_rows_used,
t_no_of_rows_available,
t_no_of_rows_duplicates);
write_to_act_log('Updating selections in TG ', 'LIST', g_list_header_id,'LOW');
l_list_select_action_id,
l_distribution_pct,
l_rank,
l_no_of_rows_dup;
t_list_select_action_id(l_iterator) := l_list_select_action_id;
write_to_act_log('Calling update_list_action_dets for updating the TG selection '||l_list_select_action_id, 'LIST', g_list_header_id,'LOW');
update_list_action_dets( t_list_select_action_id,
t_no_of_rows_used,
t_no_of_rows_available,
t_no_of_rows_duplicates);
write_to_act_log('List summary details updated in header and selection tables','LIST', g_list_header_id,'LOW');
write_to_act_log( 'Error while executing procedure update_list_dets '||sqlcode||' '||sqlerrm, 'LIST', g_list_header_id,'HIGH');
END UPDATE_LIST_DETS ;
PROCEDURE delete_list_entries(p_list_header_id number ,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status OUT NOCOPY varchar2) IS
l_delete_action varchar2(80);
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');
l_delete_action := 'List tmp Entries delete';
l_delete_action := 'List Entries delete';
DELETE FROM ams_list_entries
WHERE list_header_id = p_list_header_id;
write_to_act_log(sql%rowcount||' entries deleted from ams_list_entries in local instance.', 'LIST', g_list_header_id,'LOW');
Dynamic procedure will delete the list from the remote instance in
case of remote list
*********************************************************************/
if g_remote_list = 'Y' then
write_to_act_log('Calling remote procedure with process type as DELETE_LIST_ENTRIES to delete entries in remote instance', 'LIST', g_list_header_id,'LOW');
'DELETE_LIST_ENTRIES';
write_to_act_log('Entries deleted succesfully in remote instance','LIST', g_list_header_id,'LOW');
l_delete_action := 'List Source Type usages';
DELETE FROM ams_list_src_type_usages
WHERE list_header_id = p_list_header_id;
write_to_act_log('Procedure delete_list_entries executed successfully.', 'LIST', g_list_header_id,'LOW');
write_to_act_log('Error while executing delete_list_entries '||sqlcode || ' '||sqlerrm, 'LIST',g_list_header_id,'HIGH');
FND_MESSAGE.Set_Token('TEXT', 'Delete List Entries ' ||
l_delete_action || ' '|| SQLERRM||' '||SQLCODE);
END delete_list_entries;
UPDATE ams_list_headers_all
SET result_text = NULL,
main_gen_start_time = SYSDATE,
last_update_date = SYSDATE,
main_gen_end_time = NULL
WHERE list_header_id = p_list_header_id;
UPDATE ams_list_select_actions
SET no_of_rows_used = 0
WHERE arc_action_used_by = 'LIST'
and action_used_by_id = p_list_header_id;
write_to_act_log('Calling delete_list_entries to delete the existing entries.(The generation type is STANDARD).', 'LIST', g_list_header_id,'LOW');
delete_list_entries (p_list_header_id => p_list_header_rec.list_header_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
write_to_act_log('Error while executing delete_list_entries. Unable to delete entries.', 'LIST', g_list_header_id,'HIGH');
PROCEDURE insert_list_mapping_usage
(p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE,
p_source_type_code AMS_LIST_SRC_TYPES.SOURCE_TYPE_CODE%TYPE) IS
l_found NUMBER;
INSERT INTO ams_list_src_type_usages
(
list_source_type_usage_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,source_type_code
,list_header_id
)
select
AMS_LIST_SRC_TYPE_USAGES_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
1,
p_source_type_code,
p_list_header_id
from dual
where not exists
( select 'x'
from ams_list_src_type_usages
where list_header_id = p_list_header_id
and source_type_code = p_source_type_code ) ;
write_to_act_log('Error while executing procedure insert_list_mapping_usage '||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
END INSERT_LIST_MAPPING_USAGE;
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
l_list_entry_source_type varchar2(30);
is select decode(import_type,'B2C','PERSON_LIST','ORGANIZATION_CONTACT_LIST')
from ams_imp_list_headers_all
where import_list_header_id = p_incl_object_id;
SELECT created_by
FROM ams_list_headers_all
WHERE list_header_id= x_list_header_id;
write_to_act_log('Executing process_imph since imported list has been included in list/target group selections.', 'LIST', g_list_header_id,'LOW');
x_include_sql := ' insert into ams_list_entries
(list_header_id ,
list_entry_id,
imp_source_line_id,
object_version_number,
source_code ,
source_code_for_id ,
arc_list_used_by_source ,
arc_list_select_action_from ,
pin_code ,
view_application_id ,
manually_entered_flag ,
marked_as_random_flag ,
marked_as_duplicate_flag ,
part_of_control_group_flag ,
exclude_in_triggered_list_flag ,
enabled_flag ,
LIST_SELECT_ACTION_FROM_NAME,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
list_entry_source_system_id,
list_entry_source_system_type,
list_select_action_id,
rank,
ADDRESS_LINE1,
ADDRESS_LINE2,
COL127,
COL128,
COL227,
CITY,
COUNTRY,
COL118,
COL142,
COL138,
COL122,
EMAIL_ADDRESS,
COL239,
FIRST_NAME,
COL243,
COL144,
COL145, --Added by bmuthukr for bug 5156979
LAST_NAME,
COL251,
COL252,
COL137,
SUFFIX,
COL259,
COL6,
COL5,
COL7,
PHONE,
ZIPCODE,
COL120,
STATE,
COL125,
COL2,
TITLE,
customer_name,
party_id,
COL276 ,
NOTES ,
VEHICLE_RESPONSE_CODE ,
SALES_AGENT_EMAIL_ADDRESS ,
RESOURCE_ID ,
col147,
location_id ,
contact_point_id ,
orig_system_reference,
col116,
col117,
CUSTOM_COLUMN1,
CUSTOM_COLUMN2,
CUSTOM_COLUMN3,
CUSTOM_COLUMN4,
CUSTOM_COLUMN5,
CUSTOM_COLUMN6,
CUSTOM_COLUMN7,
CUSTOM_COLUMN8,
CUSTOM_COLUMN9,
CUSTOM_COLUMN10,
CUSTOM_COLUMN11,
CUSTOM_COLUMN12,
CUSTOM_COLUMN13,
CUSTOM_COLUMN14,
CUSTOM_COLUMN15,
CUSTOM_COLUMN16,
CUSTOM_COLUMN17,
CUSTOM_COLUMN18,
CUSTOM_COLUMN19,
CUSTOM_COLUMN20,
CUSTOM_COLUMN21,
CUSTOM_COLUMN22,
CUSTOM_COLUMN23,
CUSTOM_COLUMN24,
CUSTOM_COLUMN25,
FAX
)
select
' || p_action_used_by_id || ' ,
ams_list_entries_s.nextval, import_source_line_id,
1 ,' ||
''''||'NONE' ||'''' || ','||
0 || ',' ||
''''||'NONE' ||'''' || ','||
''''||'IMPH' ||'''' || ','||
'ams_list_entries_s.currval'|| ','||
530 || ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'Y' ||''''||',
'||p_action_used_by_id || ',
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
'||nvl(l_created_by,FND_GLOBAL.USER_ID)||',
FND_GLOBAL.USER_ID,
nvl(party_id,import_source_line_id), ' ||
''''|| l_list_entry_source_type||''''|| ' , '||
p_list_select_action_id || ' ,'||
p_rank ||',
ADDRESS1,
ADDRESS2,
BEST_TIME_CONTACT_BEGIN,
BEST_TIME_CONTACT_END,
CEO_NAME,
CITY,
COUNTRY,
COUNTY,
DECISION_MAKER_FLAG,
DEPARTMENT,
DUNS_NUMBER,
EMAIL_ADDRESS,
EMPLOYEES_TOTAL,
PERSON_FIRST_NAME,
FISCAL_YEAREND_MONTH,
JOB_TITLE,
JOB_TITLE_CODE, --Added by bmuthukr for bug 5156979
PERSON_LAST_NAME,
LEGAL_STATUS,
LINE_OF_BUSINESS,
PERSON_MIDDLE_NAME,
PERSON_NAME_SUFFIX,
party_name,
PHONE_AREA_CODE,
PHONE_COUNTRY_CODE,
PHONE_EXTENTION,
PHONE_NUMBER,
POSTAL_CODE,
PROVINCE,
STATE,
TAX_REFERENCE,
TIME_ZONE,
PERSON_NAME_PREFIX,
party_name,
party_id,
YEAR_ESTABLISHED,
NOTES ,
VEHICLE_RESPONSE_CODE ,
SALES_AGENT_EMAIL_ADDRESS ,
RESOURCE_ID ,
ORGANIZATION_ID,
location_id ,
contact_point_id ,
orig_system_reference,
address3,
address4,
CUSTOM_COLUMN1,
CUSTOM_COLUMN2,
CUSTOM_COLUMN3,
CUSTOM_COLUMN4,
CUSTOM_COLUMN5,
CUSTOM_COLUMN6,
CUSTOM_COLUMN7,
CUSTOM_COLUMN8,
CUSTOM_COLUMN9,
CUSTOM_COLUMN10,
CUSTOM_COLUMN11,
CUSTOM_COLUMN12,
CUSTOM_COLUMN13,
CUSTOM_COLUMN14,
CUSTOM_COLUMN15,
CUSTOM_COLUMN16,
CUSTOM_COLUMN17,
CUSTOM_COLUMN18,
CUSTOM_COLUMN19,
CUSTOM_COLUMN20,
CUSTOM_COLUMN21,
CUSTOM_COLUMN22,
CUSTOM_COLUMN23,
CUSTOM_COLUMN24,
CUSTOM_COLUMN25,
FAX_NUMBER
from ams_hz_b2b_mapping_v ail
where enabled_flag = '||''''||'Y'||''''||
' and import_list_header_id =' || p_incl_object_id ||
' and nvl(party_id, import_source_line_id) in (' ;
write_to_act_log('Insert statement constructed for imported B2B list', 'LIST', g_list_header_id,'LOW');
x_include_sql := ' insert into ams_list_entries
(list_header_id ,
list_entry_id,
imp_source_line_id,
source_code ,
source_code_for_id ,
arc_list_used_by_source ,
arc_list_select_action_from ,
pin_code ,
view_application_id ,
manually_entered_flag ,
marked_as_random_flag ,
marked_as_duplicate_flag ,
part_of_control_group_flag ,
exclude_in_triggered_list_flag ,
enabled_flag ,
LIST_SELECT_ACTION_FROM_NAME,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
list_entry_source_system_id,
list_entry_source_system_type,
list_select_action_id,
rank,
ADDRESS_LINE1,
ADDRESS_LINE2,
CITY,
COL127,
COL128,
COL118,
COUNTRY,
FIRST_NAME,
LAST_NAME,
COL137,
EMAIL_ADDRESS,
col70,
COL145,
STATE,
ZIPCODE,
COL120,
TITLE,
COL2,
col5,
col6,
PHONE,
col7,
party_id,
customer_name,
SUFFIX ,
NOTES ,
VEHICLE_RESPONSE_CODE ,
SALES_AGENT_EMAIL_ADDRESS ,
RESOURCE_ID ,
location_id,
contact_point_id ,
orig_system_reference,
col116,
col117,
CUSTOM_COLUMN1,
CUSTOM_COLUMN2,
CUSTOM_COLUMN3,
CUSTOM_COLUMN4,
CUSTOM_COLUMN5,
CUSTOM_COLUMN6,
CUSTOM_COLUMN7,
CUSTOM_COLUMN8,
CUSTOM_COLUMN9,
CUSTOM_COLUMN10,
CUSTOM_COLUMN11,
CUSTOM_COLUMN12,
CUSTOM_COLUMN13,
CUSTOM_COLUMN14,
CUSTOM_COLUMN15,
CUSTOM_COLUMN16,
CUSTOM_COLUMN17,
CUSTOM_COLUMN18,
CUSTOM_COLUMN19,
CUSTOM_COLUMN20,
CUSTOM_COLUMN21,
CUSTOM_COLUMN22,
CUSTOM_COLUMN23,
CUSTOM_COLUMN24,
CUSTOM_COLUMN25,
FAX
)
select
' || p_action_used_by_id || ' ,
ams_list_entries_s.nextval, ' ||
' import_source_line_id , ' ||
''''||'NONE' ||'''' || ','||
0 || ',' ||
''''||'NONE' ||'''' || ','||
''''||'IMPH' ||'''' || ','||
'ams_list_entries_s.currval'|| ','||
530 || ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'Y' ||''''||',
'||p_action_used_by_id || ',
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
'||nvl(l_created_by,FND_GLOBAL.USER_ID)||',
FND_GLOBAL.USER_ID,
1,
nvl(party_id,import_source_line_id), ' ||
''''|| l_list_entry_source_type||''''|| ' , '||
p_list_select_action_id || ' ,'||
p_rank ||',
ADDRESS1,
ADDRESS2,
CITY,
BEST_TIME_CONTACT_BEGIN,
BEST_TIME_CONTACT_END,
COUNTY,
COUNTRY,
PERSON_FIRST_NAME,
PERSON_LAST_NAME,
PERSON_MIDDLE_NAME,
EMAIL_ADDRESS,
GENDER,
HOUSEHOLD_INCOME,
STATE,
POSTAL_CODE,
PROVINCE,
PERSON_NAME_PREFIX,
TIME_ZONE ,
PHONE_COUNTRY_CODE,
PHONE_AREA_CODE ,
PHONE_NUMBER ,
PHONE_EXTENTION ,
party_id,
PERSON_LAST_NAME || '|| ''''|| ' , ' || ''''||
' || PERSON_FIRST_NAME,
PERSON_NAME_SUFFIX ,
NOTES ,
VEHICLE_RESPONSE_CODE ,
SALES_AGENT_EMAIL_ADDRESS ,
RESOURCE_ID ,
location_id ,
contact_point_id ,
orig_system_reference,
address3,
address4 ,
CUSTOM_COLUMN1,
CUSTOM_COLUMN2,
CUSTOM_COLUMN3,
CUSTOM_COLUMN4,
CUSTOM_COLUMN5,
CUSTOM_COLUMN6,
CUSTOM_COLUMN7,
CUSTOM_COLUMN8,
CUSTOM_COLUMN9,
CUSTOM_COLUMN10,
CUSTOM_COLUMN11,
CUSTOM_COLUMN12,
CUSTOM_COLUMN13,
CUSTOM_COLUMN14,
CUSTOM_COLUMN15,
CUSTOM_COLUMN16,
CUSTOM_COLUMN17,
CUSTOM_COLUMN18,
CUSTOM_COLUMN19,
CUSTOM_COLUMN20,
CUSTOM_COLUMN21,
CUSTOM_COLUMN22,
CUSTOM_COLUMN23,
CUSTOM_COLUMN24,
CUSTOM_COLUMN25,
FAX_NUMBER
from ams_hz_b2c_mapping_v
where enabled_flag = '||''''||'Y'||''''||
' and import_list_header_id =' || p_incl_object_id ||
' and nvl(party_id, import_source_line_id) in (' ;
write_to_act_log('Insert statement constructed for imported B2B list', 'LIST', g_list_header_id,'LOW');
x_std_sql := ' select party_idl_list_entry_source_type
' from ams_imp_source_lines
where import_list_header_id = ' || p_incl_object_id ;
x_std_sql := ' select nvl(party_id,import_source_line_id)
from ams_imp_source_lines
where import_list_header_id = ' || p_incl_object_id ||
' and nvl(duplicate_flag,' ||''''||'N'||''''||') = '||
''''||'N'||'''' ;
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
l_no_of_chunks number;
write_to_act_log('Executing process_list since list has been included in list/target group selections.', 'LIST', g_list_header_id,'LOW');
x_include_sql := 'insert into ams_list_entries
(list_header_id ,
list_entry_id,
object_version_number,
source_code ,
source_code_for_id ,
arc_list_used_by_source ,
arc_list_select_action_from ,
pin_code ,
view_application_id ,
manually_entered_flag ,
marked_as_random_flag ,
marked_as_duplicate_flag ,
part_of_control_group_flag ,
exclude_in_triggered_list_flag ,
enabled_flag ,
LIST_SELECT_ACTION_FROM_NAME,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
list_entry_source_system_id,
list_entry_source_system_type,
list_select_action_id,
Rank,
SUFFIX,
FIRST_NAME,
LAST_NAME,
CUSTOMER_NAME,
TITLE,
ADDRESS_LINE1,
ADDRESS_LINE2,
CITY,
STATE,
ZIPCODE,
COUNTRY,
FAX,
PHONE,
EMAIL_ADDRESS,
CUSTOMER_ID ,
LIST_SOURCE ,
PARTY_ID ,
PARENT_PARTY_ID ,
IMP_SOURCE_LINE_ID ,
COL1,
COL2,
COL3,
COL4,
COL5,
COL6,
COL7,
COL8,
COL9,
COL10,
COL11,
COL12,
COL13,
COL14,
COL15,
COL16,
COL17,
COL18,
COL19,
COL20,
COL21,
COL22,
COL23,
COL24,
COL25,
COL26,
COL27,
COL28,
COL29,
COL30,
COL31,
COL32,
COL33,
COL34,
COL35,
COL36,
COL37,
COL38,
COL39,
COL40,
COL41,
COL42,
COL43,
COL44,
COL45,
COL46,
COL47,
COL48,
COL49,
COL50,
COL51,
COL52,
COL53,
COL54,
COL55,
COL56,
COL57,
COL58,
COL59,
COL60,
COL61,
COL62,
COL63,
COL64,
COL65,
COL66,
COL67,
COL68,
COL69,
COL70,
COL71,
COL72,
COL73,
COL74,
COL75,
COL76,
COL77,
COL78,
COL79,
COL80,
COL81,
COL82,
COL83,
COL84,
COL85,
COL86,
COL87,
COL88,
COL89,
COL90,
COL91,
COL92,
COL93,
COL94,
COL95,
COL96,
COL97,
COL98,
COL99,
COL100,
COL101,
COL102,
COL103,
COL104,
COL105,
COL106,
COL107,
COL108,
COL109,
COL110,
COL111,
COL112,
COL113,
COL114,
COL115,
COL116,
COL117,
COL118,
COL119,
COL120,
COL121,
COL122,
COL123,
COL124,
COL125,
COL126,
COL127,
COL128,
COL129,
COL130,
COL131,
COL132,
COL133,
COL134,
COL135,
COL136,
COL137,
COL138,
COL139,
COL140,
COL141,
COL142,
COL143,
COL144,
COL145,
COL146,
COL147,
COL148,
COL149,
COL150,
COL151,
COL152,
COL153,
COL154,
COL155,
COL156,
COL157,
COL158,
COL159,
COL160,
COL161,
COL162,
COL163,
COL164,
COL165,
COL166,
COL167,
COL168,
COL169,
COL170,
COL171,
COL172,
COL173,
COL174,
COL175,
COL176,
COL177,
COL178,
COL179,
COL180,
COL181,
COL182,
COL183,
COL184,
COL185,
COL186,
COL187,
COL188,
COL189,
COL190,
COL191,
COL192,
COL193,
COL194,
COL195,
COL196,
COL197,
COL198,
COL199,
COL200,
COL201,
COL202,
COL203,
COL204,
COL205,
COL206,
COL207,
COL208,
COL209,
COL210,
COL211,
COL212,
COL213,
COL214,
COL215,
COL216,
COL217,
COL218,
COL219,
COL220,
COL221,
COL222,
COL223,
COL224,
COL225,
COL226,
COL227,
COL228,
COL229,
COL230,
COL231,
COL232,
COL233,
COL234,
COL235,
COL236,
COL237,
COL238,
COL239,
COL240,
COL241,
COL242,
COL243,
COL244,
COL245,
COL246,
COL247,
COL248,
COL249,
COL250 ,
COL251 ,
COL252 ,
COL253 ,
COL254 ,
COL256 ,
COL255 ,
COL257 ,
COL258 ,
COL259 ,
COL260 ,
COL261 ,
COL262 ,
COL263 ,
COL264 ,
COL265 ,
COL266 ,
COL267 ,
COL268 ,
COL269 ,
COL270 ,
COL271 ,
COL272 ,
COL273 ,
COL274 ,
COL275 ,
COL276 ,
COL277 ,
COL278 ,
COL279 ,
COL280 ,
COL281 ,
COL282 ,
COL283 ,
COL284 ,
COL285 ,
COL286 ,
COL287 ,
COL288 ,
COL289 ,
COL290 ,
COL291 ,
COL292 ,
COL293 ,
COL294 ,
COL295 ,
COL296 ,
COL297 ,
COL298 ,
COL299 ,
COL300 ,
NOTES ,
VEHICLE_RESPONSE_CODE ,
SALES_AGENT_EMAIL_ADDRESS ,
RESOURCE_ID ,
location_id ,
contact_point_id ,
orig_system_reference,
CUSTOM_COLUMN1,
CUSTOM_COLUMN2,
CUSTOM_COLUMN3,
CUSTOM_COLUMN4,
CUSTOM_COLUMN5,
CUSTOM_COLUMN6,
CUSTOM_COLUMN7,
CUSTOM_COLUMN8,
CUSTOM_COLUMN9,
CUSTOM_COLUMN10,
CUSTOM_COLUMN11,
CUSTOM_COLUMN12,
CUSTOM_COLUMN13,
CUSTOM_COLUMN14,
CUSTOM_COLUMN15,
CUSTOM_COLUMN16,
CUSTOM_COLUMN17,
CUSTOM_COLUMN18,
CUSTOM_COLUMN19,
CUSTOM_COLUMN20,
CUSTOM_COLUMN21,
CUSTOM_COLUMN22,
CUSTOM_COLUMN23,
CUSTOM_COLUMN24,
CUSTOM_COLUMN25
)
select ' ||
p_action_used_by_id ||',
ams_list_entries_s.nextval,
1 ,' ||
''''||'NONE' ||'''' || ','||
0 || ',' ||
''''||'NONE' ||'''' || ','||
''''||'LIST' ||'''' || ','||
'ams_list_entries_s.currval'|| ','||
530 || ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'Y' ||''''||',
list_entry_source_system_id||list_entry_source_system_type,
sysdate,
last_updated_by,
sysdate,
created_by,
last_update_login,
list_entry_source_system_id,
list_entry_source_system_type, '||
p_list_select_action_id ||','||
p_rank ||',
SUFFIX,
FIRST_NAME,
LAST_NAME,
CUSTOMER_NAME,
TITLE,
ADDRESS_LINE1,
ADDRESS_LINE2,
CITY,
STATE,
ZIPCODE,
COUNTRY,
FAX,
PHONE,
EMAIL_ADDRESS,
CUSTOMER_ID ,
LIST_SOURCE ,
PARTY_ID ,
PARENT_PARTY_ID ,
IMP_SOURCE_LINE_ID ,
COL1,
COL2,
COL3,
COL4,
COL5,
COL6,
COL7,
COL8,
COL9,
COL10,
COL11,
COL12,
COL13,
COL14,
COL15,
COL16,
COL17,
COL18,
COL19,
COL20,
COL21,
COL22,
COL23,
COL24,
COL25,
COL26,
COL27,
COL28,
COL29,
COL30,
COL31,
COL32,
COL33,
COL34,
COL35,
COL36,
COL37,
COL38,
COL39,
COL40,
COL41,
COL42,
COL43,
COL44,
COL45,
COL46,
COL47,
COL48,
COL49,
COL50,
COL51,
COL52,
COL53,
COL54,
COL55,
COL56,
COL57,
COL58,
COL59,
COL60,
COL61,
COL62,
COL63,
COL64,
COL65,
COL66,
COL67,
COL68,
COL69,
COL70,
COL71,
COL72,
COL73,
COL74,
COL75,
COL76,
COL77,
COL78,
COL79,
COL80,
COL81,
COL82,
COL83,
COL84,
COL85,
COL86,
COL87,
COL88,
COL89,
COL90,
COL91,
COL92,
COL93,
COL94,
COL95,
COL96,
COL97,
COL98,
COL99,
COL100,
COL101,
COL102,
COL103,
COL104,
COL105,
COL106,
COL107,
COL108,
COL109,
COL110,
COL111,
COL112,
COL113,
COL114,
COL115,
COL116,
COL117,
COL118,
COL119,
COL120,
COL121,
COL122,
COL123,
COL124,
COL125,
COL126,
COL127,
COL128,
COL129,
COL130,
COL131,
COL132,
COL133,
COL134,
COL135,
COL136,
COL137,
COL138,
COL139,
COL140,
COL141,
COL142,
COL143,
COL144,
COL145,
COL146,
COL147,
COL148,
COL149,
COL150,
COL151,
COL152,
COL153,
COL154,
COL155,
COL156,
COL157,
COL158,
COL159,
COL160,
COL161,
COL162,
COL163,
COL164,
COL165,
COL166,
COL167,
COL168,
COL169,
COL170,
COL171,
COL172,
COL173,
COL174,
COL175,
COL176,
COL177,
COL178,
COL179,
COL180,
COL181,
COL182,
COL183,
COL184,
COL185,
COL186,
COL187,
COL188,
COL189,
COL190,
COL191,
COL192,
COL193,
COL194,
COL195,
COL196,
COL197,
COL198,
COL199,
COL200,
COL201,
COL202,
COL203,
COL204,
COL205,
COL206,
COL207,
COL208,
COL209,
COL210,
COL211,
COL212,
COL213,
COL214,
COL215,
COL216,
COL217,
COL218,
COL219,
COL220,
COL221,
COL222,
COL223,
COL224,
COL225,
COL226,
COL227,
COL228,
COL229,
COL230,
COL231,
COL232,
COL233,
COL234,
COL235,
COL236,
COL237,
COL238,
COL239,
COL240,
COL241,
COL242,
COL243,
COL244,
COL245,
COL246,
COL247,
COL248,
COL249,
COL250 ,
COL251 ,
COL252 ,
COL253 ,
COL254 ,
COL256 ,
COL255 ,
COL257 ,
COL258 ,
COL259 ,
COL260 ,
COL261 ,
COL262 ,
COL263 ,
COL264 ,
COL265 ,
COL266 ,
COL267 ,
COL268 ,
COL269 ,
COL270 ,
COL271 ,
COL272 ,
COL273 ,
COL274 ,
COL275 ,
COL276 ,
COL277 ,
COL278 ,
COL279 ,
COL280 ,
COL281 ,
COL282 ,
COL283 ,
COL284 ,
COL285 ,
COL286 ,
COL287 ,
COL288 ,
COL289 ,
COL290 ,
COL291 ,
COL292 ,
COL293 ,
COL294 ,
COL295 ,
COL296 ,
COL297 ,
COL298 ,
COL299 ,
COL300 ,
NOTES ,
VEHICLE_RESPONSE_CODE ,
SALES_AGENT_EMAIL_ADDRESS ,
RESOURCE_ID ,
location_id ,
contact_point_id ,
orig_system_reference,
CUSTOM_COLUMN1,
CUSTOM_COLUMN2,
CUSTOM_COLUMN3,
CUSTOM_COLUMN4,
CUSTOM_COLUMN5,
CUSTOM_COLUMN6,
CUSTOM_COLUMN7,
CUSTOM_COLUMN8,
CUSTOM_COLUMN9,
CUSTOM_COLUMN10,
CUSTOM_COLUMN11,
CUSTOM_COLUMN12,
CUSTOM_COLUMN13,
CUSTOM_COLUMN14,
CUSTOM_COLUMN15,
CUSTOM_COLUMN16,
CUSTOM_COLUMN17,
CUSTOM_COLUMN18,
CUSTOM_COLUMN19,
CUSTOM_COLUMN20,
CUSTOM_COLUMN21,
CUSTOM_COLUMN22,
CUSTOM_COLUMN23,
CUSTOM_COLUMN24,
CUSTOM_COLUMN25
from ams_list_entries
where list_header_id = ' || p_incl_object_id ||
' and nvl(enabled_flag,' ||''''||'N'||''''||') = '||
''''||'Y'||'''' ||
' and list_entry_source_system_id in (' ;
INSERT INTO ams_list_src_type_usages
(
list_source_type_usage_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,source_type_code
,list_header_id
)
select
AMS_LIST_SRC_TYPE_USAGES_S.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
1,
als.source_type_code,
p_action_used_by_id
from ams_list_src_type_usages als
where not exists
( select 'x'
from ams_list_src_type_usages als1
where als1.list_header_id = p_action_used_by_id -- p_incl_object_id
and als.source_type_code = als1.source_type_code )
and als.list_header_id = p_incl_object_id ;
write_to_act_log('Insert statement constructed based on the list included.', 'LIST', g_list_header_id,'LOW');
list_entry_source_syetem_type is not selected
x_std_sql := ' select list_entry_source_system_id||
list_entry_source_system_type from ams_list_entries
where list_header_id = ' || p_incl_object_id ||
' and enabled_flag = ' || ''''||'Y' || '''' ;
x_std_sql := ' select list_entry_source_system_id
from ams_list_entries
where list_header_id = ' || p_incl_object_id ||
' and enabled_flag = ' || ''''||'Y' || '''' ;
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) IS
BEGIN
process_list
(p_action_used_by_id => p_action_used_by_id,
p_incl_object_id => p_incl_object_id,
p_list_action_type => p_list_action_type,
p_list_select_action_id => p_list_select_action_id,
p_order_number => p_order_number,
p_rank => p_rank,
p_include_control_group => p_include_control_group,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status,
x_std_sql => x_std_sql ,
x_include_sql => x_include_sql );
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2 ) is
BEGIN
process_list
(p_action_used_by_id => p_action_used_by_id,
p_incl_object_id => p_incl_object_id,
p_list_action_type => p_list_action_type,
p_list_select_action_id => p_list_select_action_id,
p_order_number => p_order_number,
p_rank => p_rank,
p_include_control_group => p_include_control_group,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status,
x_std_sql => x_std_sql ,
x_include_sql => x_include_sql );
PROCEDURE form_sql_statement(p_select_statement in varchar2,
p_select_add_statement in varchar2,
p_master_type in varchar2,
p_child_types in child_type,
p_from_string in sql_string_4K,
p_action_used_by_id in number,
p_list_select_action_id in number,
p_list_action_type in varchar2,
p_order_number in number,
p_rank in number,
x_final_string OUT NOCOPY varchar2
) is
-- child_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
select source_object_name , source_object_name || '.' || source_object_pk_field , list_source_type_id
from ams_list_src_types
where source_type_code = p_master_type;
select a.source_object_name ,
a.source_object_name || '.' || b.sub_source_type_pk_column
,b.master_source_type_pk_column
from ams_list_src_types a, ams_list_src_type_assocs b
where a.source_type_code = l_child_src_type
and b.sub_source_type_id = a.list_source_type_id
and b.master_source_type_id = l_master_source_type_id ;
l_select_clause varchar2(32767);
l_insert_clause varchar2(32767);
l_insert_sql varchar2(32767);
SELECT created_by
FROM ams_list_headers_all
WHERE list_header_id= x_list_header_id;
SELECT b.field_column_name ,
c.source_object_name,
b.source_column_name,
b.field_data_type
BULK COLLECT INTO :1 ,:2 ,:3 ,:4
FROM ams_list_src_fields b, ams_list_src_types c
WHERE b.list_source_type_id = c.list_source_type_id
AND c.list_source_type = ''TARGET''
and b.DE_LIST_SOURCE_TYPE_CODE IN '|| l_data_source_types ||
' AND b.ROWID >= (SELECT MAX(a.ROWID)
FROM ams_list_src_fields a
WHERE a.field_column_name= b.field_column_name
AND a.DE_LIST_SOURCE_TYPE_CODE IN '
|| l_data_source_types || ') ;
l_insert_clause := l_insert_clause || ' ,' || l_field_col_tbl(i) ;
l_select_clause := l_select_clause || ' ,' ||
'to_char('||l_view_tbl(i) || '.'||l_source_col_tbl(i)||','||''''||'DD-MM-RRRR'||''''||')' ;
l_select_clause := l_select_clause || ' ,' ||
l_view_tbl(i) || '.'||l_source_col_tbl(i) ;
write_to_act_log('Insert clause formed is '||l_insert_clause, 'LIST', g_list_header_id,'LOW');
write_to_act_log('Select clause formed is '||l_insert_clause, 'LIST', g_list_header_id,'LOW');
l_insert_sql := 'insert into ams_list_entries '||
'( LIST_SELECT_ACTION_FROM_NAME, '||
' LIST_ENTRY_SOURCE_SYSTEM_ID , '||
' LIST_ENTRY_SOURCE_SYSTEM_TYPE, '||
' list_select_action_id , '||
' rank , '||
' list_header_id,last_update_date, '||
' last_updated_by,creation_date,created_by,'||
'list_entry_id, '||
'object_version_number, ' ||
'source_code , ' ||
'source_code_for_id , ' ||
'arc_list_used_by_source , ' ||
'arc_list_select_action_from , ' ||
'pin_code , ' ||
'view_application_id , ' ||
'manually_entered_flag , ' ||
'marked_as_random_flag , ' ||
'marked_as_duplicate_flag , ' ||
'part_of_control_group_flag , ' ||
'exclude_in_triggered_list_flag , ' ||
'enabled_flag ' ||
l_insert_clause || ' ) ' ||
'select ' ||
l_master_primary_key ||','||
l_master_primary_key ||','||
''''||p_master_type||''''||','||
p_list_select_action_id || ',' ||
p_rank || ',' ||
to_char(p_action_used_by_id )|| ',' ||''''||
to_char(sysdate )|| ''''||','||
to_char(FND_GLOBAL.login_id )|| ',' ||''''||
to_char(sysdate )|| ''''||','||
to_char(nvl(l_created_by, FND_GLOBAL.login_id) )|| ',' ||
'ams_list_entries_s.nextval' || ','||
1 || ','||
''''||'NONE' ||'''' || ','||
0 || ',' ||
''''||'NONE' ||'''' || ','||
''''||'NONE' ||'''' || ','||
'ams_list_entries_s.currval'|| ','||
530 || ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'N' ||''''|| ','||
''''||'Y' ||''''||
l_select_clause ;
l_final_sql := l_insert_sql || ' ' ||
l_from_clause || ' '||
l_where_clause || ' and ' ||
l_master_primary_key ||
'|| '||''''||p_master_type ||''''|| ' in ( ' ;
l_final_sql := l_insert_sql || ' ' ||
l_from_clause || ' '||
l_where_clause || ' and ' ||
l_master_primary_key|| ' in ( ' ;
PROCEDURE process_insert_sql(p_select_statement in varchar2,
p_select_add_statement in varchar2,
p_master_type in varchar2,
p_child_types in child_type,
p_from_string in sql_string_4K,
p_action_used_by_id in number,
p_list_select_action_id in number,
p_list_action_type in varchar2,
p_order_number in number,
p_rank in number,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
l_final_sql varchar2(32767);
l_insert_sql varchar2(32767);
l_insert_sql1 varchar2(32767);
write_to_act_log('Executing process_insert_sql procedure', 'LIST', g_list_header_id,'LOW');
l_table_name := ' ams_list_delete_tmp_entries ';
l_insert_sql := 'insert into '|| l_table_name || ' '||
'(list_entry_source_key, ' ||
' list_entry_source_id,list_entry_source_type, ' ||
' list_select_action_id ,' ||
' list_header_id,last_update_date, ' ||
' last_updated_by,creation_date,created_by,'||
' rank) '||
p_select_statement || ' '||
p_select_add_statement || ' ,' ||
p_list_select_action_id || ',' ||
to_char(p_action_used_by_id )|| ',' ||''''||
to_char(sysdate )|| ''''||','||
to_char(FND_GLOBAL.login_id )|| ',' ||''''||
to_char(sysdate )|| ''''||','||
to_char(FND_GLOBAL.login_id )|| ',' ||
to_char( nvl(p_rank,9999999));
l_insert_sql := p_select_statement ;
l_insert_sql := l_insert_sql || p_from_string(i);
x_std_sql := l_insert_sql;
form_sql_statement(p_select_statement ,
p_select_add_statement ,
p_master_type ,
p_child_types ,
p_from_string ,
p_action_used_by_id ,
p_list_select_action_id ,
p_list_action_type ,
p_order_number ,
p_rank ,
l_final_sql
) ;
write_to_act_log('Procedure process_insert_sql completed', 'LIST', g_list_header_id,'LOW');
write_to_act_log('Error while executing procedure process_insert_sql '||sqlcode||' '||sqlerrm , 'LIST', g_list_header_id,'HIGH');
END process_insert_sql;
select source_type_code
from ams_list_src_types a,
ams_list_src_type_assocs b
where b.master_source_type_id = p_master_type_id
and b.sub_source_type_id = a.list_source_type_id
and b.enabled_flag = 'Y'
and a.enabled_flag = 'Y'
and exists (select 'x' from ams_list_src_fields
where list_source_type_id = b.sub_source_type_id
and field_column_name is not null) ;
SELECT list_source_type_id,
source_type_code,
source_object_name,
source_object_pk_field
FROM ams_list_src_types
WHERE master_source_type_flag = 'Y'
AND list_source_type in ('ANALYTICS', 'TARGET');
SELECT a.list_source_type_id,
a.source_type_code,
a.source_object_name,
a.source_object_pk_field
FROM ams_list_src_types a,ams_list_headers_all b
WHERE a.master_source_type_flag = 'Y'
and a.source_type_code = b.list_source_type
and b.list_header_id = g_list_header_id
AND a.list_source_type in ('ANALYTICS', 'TARGET');
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
p_sql_string in sql_string,
p_primary_key in varchar2,
p_source_object_name in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
l_sql_string sql_string;
l_select_condition varchar2(2000);
l_select_add_condition varchar2(2000);
write_to_act_log('Calling insert list_mapping usage', 'LIST', g_list_header_id,'LOW');
insert_list_mapping_usage
(p_list_header_id => p_action_used_by_id,
p_source_type_code => l_child_types(i) ) ;
l_select_condition := 'SELECT ' ||l_source_object_name||'.'
||l_source_object_pk_field;
l_select_add_condition := ','||l_source_object_name||'.'
||l_source_object_pk_field||','||''''
||l_master_type||'''' ;
write_to_act_log('Calling process_insert_sql', 'LIST', g_list_header_id,'LOW');
process_insert_sql(p_select_statement => l_select_condition,
p_select_add_statement => l_select_add_condition,
p_master_type => l_master_type,
p_child_types => l_child_types,
p_from_string => l_from_string ,
p_list_select_action_id => p_list_select_action_id ,
p_action_used_by_id => p_action_used_by_id ,
p_list_action_type => p_list_action_type ,
p_order_number => p_order_number,
p_rank => p_rank,
x_std_sql => x_std_sql,
x_include_sql => x_include_sql
);
write_to_act_log('Calling insert_list_mapping_usage', 'LIST', g_list_header_id,'LOW');
insert_list_mapping_usage
(p_list_header_id => p_action_used_by_id,
p_source_type_code => l_master_type ) ;
insert_list_mapping_usage
(p_list_header_id => p_action_used_by_id,
p_source_type_code => l_child_types(i) ) ;
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
------------------------------------------------------------------------------
-- Given the sql id from ams_list_select_actions it will retrieve the
-- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
-- workbook_name.
------------------------------------------------------------------------------
cursor cur_sql is
SELECT query,primary_key, source_object_name
FROM ams_list_queries_all
WHERE (list_query_id = p_incl_object_id )
or (parent_list_query_id = p_incl_object_id )
order by sequence_order;
SELECT sql_string, primary_key, source_object_name
FROM ams_list_queries_all
WHERE (list_query_id = p_incl_object_id )
or (parent_list_query_id = p_incl_object_id )
order by sequence_order;
SELECT lc.SOURCE_OBJECT_NAME, lc.SOURCE_OBJECT_PK_FIELD
FROM ams_list_queries_all lq,
ams_list_headers_all lh,
ams_list_src_types lc
WHERE lq.list_query_id = p_incl_object_id
and lq.ARC_ACT_LIST_QUERY_USED_BY = 'LIST'
and lq.ACT_LIST_QUERY_USED_BY_ID = lh.list_header_id
and lc.source_type_code = lh.list_source_type;
l_select_condition varchar2(2000);
l_select_add_condition varchar2(2000);
p_list_select_action_id => p_list_select_action_id ,
p_order_number => p_order_number ,
p_rank => p_rank ,
p_include_control_group => p_include_control_group,
p_sql_string => l_sql_string ,
p_primary_key => l_primary_key,
p_source_object_name => l_source_object_name,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
x_return_status => x_return_status ,
x_std_sql => x_std_sql,
x_include_sql => x_include_sql
);
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
------------------------------------------------------------------------------
-- Given the sql id from ams_list_select_actions it will retrieve the
-- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
-- workbook_name.
------------------------------------------------------------------------------
--bmuthukr 4351740. this cursor should be used to pick the disc sql
--since sql could be stored in more than one record this reqd.
cursor cur_diwb(l_incl_object_id in number ) is
SELECT sql_string
FROM ams_discoverer_sql
WHERE (workbook_name, worksheet_name )
IN
( SELECT workbook_name, worksheet_name
FROM ams_discoverer_sql
WHERE discoverer_sql_id = l_incl_object_id)
ORDER BY sequence_order;
l_select_condition varchar2(2000);
l_select_add_condition varchar2(2000);
write_to_act_log('Executing process_diwb since workbook has been included in list/target group selections.', 'LIST', g_list_header_id,'LOW');
update ams_list_headers_all
set last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
where list_header_id = g_list_header_id;
p_list_select_action_id => p_list_select_action_id ,
p_order_number => p_order_number ,
p_rank => p_rank ,
p_include_control_group => p_include_control_group,
p_sql_string => l_sql_string,
p_primary_key => null,
p_source_object_name => null,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
x_return_status => x_return_status ,
x_std_sql => x_std_sql,
x_include_sql => x_include_sql);
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
------------------------------------------------------------------------------
-- Given the sql id from ams_list_select_actions it will retrieve the
-- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
-- workbook_name.
------------------------------------------------------------------------------
l_sql_string sql_string;
l_select_condition varchar2(2000);
l_select_add_condition varchar2(2000);
write_to_act_log('Executing process_cell since segment has been included in list/target group selections.', 'LIST', g_list_header_id,'LOW');
p_list_select_action_id => p_list_select_action_id ,
p_order_number => p_order_number ,
p_rank => p_rank ,
p_include_control_group => p_include_control_group,
p_sql_string => l_sql_string ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
x_return_status => x_return_status ,
x_std_sql => x_std_sql,
x_include_sql => x_include_sql,
p_primary_key => null,
p_source_object_name => null);
l_list_select_action_id number;
SELECT list_select_action_id
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_used_by_id
AND arc_action_used_by = p_action_used_by
ORDER by rank; -- Raghu Jul 07
SELECT list_select_action_id
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_used_by_id
AND arc_action_used_by = p_action_used_by
ORDER by order_number;
l_update_sql VARCHAR2(32767);
select generation_type
from ams_list_headers_all
where list_header_id = l_list_header_id;
cursor c_query(l_query_id number) is select
nvl(PARAMETERIZED_FLAG ,'N')
from ams_list_queries_all
where list_query_id = l_query_id ;
select PARAMETER_ORDER, PARAMETER_VALUE,parameter_name
from ams_list_queries_param
where list_query_id = l_query_id
order by PARAMETER_ORDER;
l_remote_update_sql VARCHAR2(32767);
cursor c_count1 is select count(1)
from ams_list_entries
where list_header_id = g_list_header_id ;
select 'Y'
from ams_list_headers_vl a ,
ams_query_template_all b
where a.list_header_id = g_list_header_id
and b.template_type = 'PARAMETERIZED'
and a.query_template_id = b.template_id ;
l_list_select_id num_tbl_type;
SELECT list_select_action_id
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_used_by_id
AND arc_action_used_by = p_action_used_by
AND order_number = l_order_num; -- added for bug fix 4443619
SELECT list_select_action_id,order_number
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_used_by_id
AND arc_action_used_by = p_action_used_by
AND order_number = l_order_num;
select count(1)
from ams_list_queries_param
where list_query_id = p_incl_object_id;
' select list_entry_source_system_id ' ||
' from ams_list_entries ' ||
' where list_header_id = ' || p_action_used_by_id ;
l_const_sql1 := ' and LIST_SELECT_ACTION_ID = ';
write_to_act_log('Fetching info for the selections','LIST',g_list_header_id, 'LOW');
FETCH c_action_dets INTO l_list_select_action_id;
l_tmp_action_rec.list_select_action_id := l_list_select_action_id;
write_to_act_log('Action type of the first selection should always be INCLUDE. Aborting list generation.', 'LIST', g_list_header_id,'HIGH');
write_to_act_log('Calling validate_listaction procedure to validate the list selection attributes','LIST',g_list_header_id, 'LOW');
write_to_act_log('Error while validating list selections', 'LIST', g_list_header_id,'HIGH');
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = g_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = g_list_header_id;
write_to_act_log('p_action_rec.list_select_action_id = '||p_action_rec.list_select_action_id, 'LIST', g_list_header_id,'LOW');
p_action_rec.list_select_action_id,
p_action_rec.order_number,
p_action_rec.rank,
'N',--CHECK p_action_rec.incl_control_group,
OUT x_msg_data,
OUT x_msg_count,
in OUT x_return_status ,
OUT l_std_sql ,
OUT l_include_sql;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = g_list_header_id;
l_list_select_id(l_include_count) := p_action_rec.list_select_action_id;
write_to_act_log('Action on the list/target group selection completed. ', 'LIST', g_list_header_id,'LOW');
write_to_act_log('No of inclusions in the selection '|| l_include_count, 'LIST', g_list_header_id,'LOW');
write_to_act_log('Sorting based on rank for the selection. '|| l_include_count, 'LIST', g_list_header_id,'LOW');
WRITE_TO_ACT_LOG('List selection id is '||l_l_sele_action_id||' for order number '||l_order_num, 'LIST', g_list_header_id,'LOW');
write_to_act_log('% Requested for this selection is '||l_dist_pct_tbl(l_sorted),'LIST',g_list_header_id,'LOW');
l_temp_sql := 'SELECT count(1) '||substr(l_temp_sql,instr(l_temp_sql, ' FROM '));
-- get_count(l_list_select_id(l_sorted),l_sorted,'OTHERS',l_temp_sql||l_std_sql_tbl(l_sorted)||l_const_sql||l_const_sql1||l_l_sele_action_id||')' );
get_count(l_list_select_id(l_sorted),l_sorted,'OTHERS',l_temp_sql||l_std_sql_tbl(l_sorted)||l_const_sql||')' );
get_count(l_list_select_id(l_sorted),l_sorted,'LIST',null);
get_count(l_list_select_id(l_sorted),l_sorted,'IMPH',null);
write_to_act_log('No of rows requested from the selection is '||g_reqd_num_tbl(l_sorted),'LIST',g_list_header_id,'LOW');
a segment , sql or workbook is in the list selection then it will be generated in the remote
instance through a dynamic procedure call */
write_to_act_log('Calling remote procedure to generate list in remote instance', 'LIST', g_list_header_id,'LOW');
write_to_act_log(' % Requested at selection level for this selection ','LIST',g_list_header_id,'LOW');
write_to_act_log(' % Not Requested at selection level for this selection ','LIST',g_list_header_id,'LOW');
update ams_list_headers_all
set last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
where list_header_id = g_list_header_id;
update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
-- l_final_big_sql := ' insert into t11 values (99,:PARTY_TYPE)';
update ams_list_headers_all
set last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
where list_header_id = g_list_header_id;
update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
SELECT created_by
FROM ams_list_headers_all
WHERE list_header_id= x_list_header_id;
delete from ams_list_tmp_entries alte1
where alte1.rowid > (select min(alte2.rowid)
from ams_list_tmp_entries alte2
where alte2.list_header_id = alte1.list_header_id
and alte2.list_select_action_id
= alte1.list_select_action_id
and alte2.list_entry_source_key
= alte1.list_entry_source_key );
delete from ams_list_tmp_entries alte1
where alte1.rank > (select min(alte2.rank)
from ams_list_tmp_entries alte2
where alte2.list_header_id = alte1.list_header_id
and alte2.list_entry_source_key
= alte1.list_entry_source_key );
INSERT INTO ams_List_Entries
( list_entry_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
list_header_id ,
list_select_action_id ,
arc_list_select_action_from ,
list_select_action_from_name ,
source_code ,
source_code_for_id ,
arc_list_used_by_source ,
pin_code ,
list_entry_source_system_id ,
list_entry_source_system_type ,
view_application_id ,
manually_entered_flag ,
marked_as_random_flag ,
marked_as_duplicate_flag ,
part_of_control_group_flag ,
exclude_in_triggered_list_flag ,
enabled_flag,
object_version_number
)
( select ams_list_entries_s.nextval,
sysdate,
fnd_global.user_id,
sysdate,
nvl(l_created_by, fnd_global.user_id),
fnd_global.conc_login_id,
p_list_header_id,
s.list_select_action_id,
s.arc_incl_object_from,
s.incl_object_name,
'NONE',
0,
h.list_used_by_id,
ams_list_entries_s.currval,
t.list_entry_source_id,
t.list_entry_source_type,
fnd_global.conc_login_id,
'N',
'N',
'N',
'N',
'N',
'Y',
1
from ams_list_select_actions s,
ams_list_tmp_entries t,
ams_list_headers_all h
where h.list_header_id = p_list_header_id
and t.list_header_id = h.list_header_id
and t.list_select_action_id = s.list_select_action_id
);
select count(*)
into l_entry_count
from ams_list_entries
where list_header_id = p_list_header_id;
update ams_list_headers_all
set no_of_rows_in_list = l_entry_count,
last_update_date = sysdate
where list_header_id = p_list_header_id;
procedure update_list_entries(p_list_header_id in number) is
l_tg_check varchar2(1) := null;
select 'Y' from ams_act_lists where list_header_id = p_list_header_id;
write_to_act_log('Generating target group(which has selections based on LIST) in update mode.','LIST',g_list_header_id,'LOW');
update ams_list_entries ale
set (
ale.newly_updated_flag ,
ale.enabled_flag ,
ale.SUFFIX,
ale.FIRST_NAME,
ale.LAST_NAME,
ale.CUSTOMER_NAME,
ale.TITLE,
ale.ADDRESS_LINE1,
ale.ADDRESS_LINE2,
ale.CITY,
ale.STATE,
ale.ZIPCODE,
ale.COUNTRY,
ale.FAX,
ale.PHONE,
ale.EMAIL_ADDRESS,
ale.COL1,
ale.COL2,
ale.COL3,
ale.COL4,
ale.COL5,
ale.COL6,
ale.COL7,
ale.COL8,
ale.COL9,
ale.COL10,
ale.COL11,
ale.COL12,
ale.COL13,
ale.COL14,
ale.COL15,
ale.COL16,
ale.COL17,
ale.COL18,
ale.COL19,
ale.COL20,
ale.COL21,
ale.COL22,
ale.COL23,
ale.COL24,
ale.COL25,
ale.COL26,
ale.COL27,
ale.COL28,
ale.COL29,
ale.COL30,
ale.COL31,
ale.COL32,
ale.COL33,
ale.COL34,
ale.COL35,
ale.COL36,
ale.COL37,
ale.COL38,
ale.COL39,
ale.COL40,
ale.COL41,
ale.COL42,
ale.COL43,
ale.COL44,
ale.COL45,
ale.COL46,
ale.COL47,
ale.COL48,
ale.COL49,
ale.COL50,
ale.COL51,
ale.COL52,
ale.COL53,
ale.COL54,
ale.COL55,
ale.COL56,
ale.COL57,
ale.COL58,
ale.COL59,
ale.COL60,
ale.COL61,
ale.COL62,
ale.COL63,
ale.COL64,
ale.COL65,
ale.COL66,
ale.COL67,
ale.COL68,
ale.COL69,
ale.COL70,
ale.COL71,
ale.COL72,
ale.COL73,
ale.COL74,
ale.COL75,
ale.COL76,
ale.COL77,
ale.COL78,
ale.COL79,
ale.COL80,
ale.COL81,
ale.COL82,
ale.COL83,
ale.COL84,
ale.COL85,
ale.COL86,
ale.COL87,
ale.COL88,
ale.COL89,
ale.COL90,
ale.COL91,
ale.COL92,
ale.COL93,
ale.COL94,
ale.COL95,
ale.COL96,
ale.COL97,
ale.COL98,
ale.COL99,
ale.COL100,
ale.COL101,
ale.COL102,
ale.COL103,
ale.COL104,
ale.COL105,
ale.COL106,
ale.COL107,
ale.COL108,
ale.COL109,
ale.COL110,
ale.COL111,
ale.COL112,
ale.COL113,
ale.COL114,
ale.COL115,
ale.COL116,
ale.COL117,
ale.COL118,
ale.COL119,
ale.COL120,
ale.COL121,
ale.COL122,
ale.COL123,
ale.COL124,
ale.COL125,
ale.COL126,
ale.COL127,
ale.COL128,
ale.COL129,
ale.COL130,
ale.COL131,
ale.COL132,
ale.COL133,
ale.COL134,
ale.COL135,
ale.COL136,
ale.COL137,
ale.COL138,
ale.COL139,
ale.COL140,
ale.COL141,
ale.COL142,
ale.COL143,
ale.COL144,
ale.COL145,
ale.COL146,
ale.COL147,
ale.COL148,
ale.COL149,
ale.COL150,
ale.COL151,
ale.COL152,
ale.COL153,
ale.COL154,
ale.COL155,
ale.COL156,
ale.COL157,
ale.COL158,
ale.COL159,
ale.COL160,
ale.COL161,
ale.COL162,
ale.COL163,
ale.COL164,
ale.COL165,
ale.COL166,
ale.COL167,
ale.COL168,
ale.COL169,
ale.COL170,
ale.COL171,
ale.COL172,
ale.COL173,
ale.COL174,
ale.COL175,
ale.COL176,
ale.COL177,
ale.COL178,
ale.COL179,
ale.COL180,
ale.COL181,
ale.COL182,
ale.COL183,
ale.COL184,
ale.COL185,
ale.COL186,
ale.COL187,
ale.COL188,
ale.COL189,
ale.COL190,
ale.COL191,
ale.COL192,
ale.COL193,
ale.COL194,
ale.COL195,
ale.COL196,
ale.COL197,
ale.COL198,
ale.COL199,
ale.COL200,
ale.COL201,
ale.COL202,
ale.COL203,
ale.COL204,
ale.COL205,
ale.COL206,
ale.COL207,
ale.COL208,
ale.COL209,
ale.COL210,
ale.COL211,
ale.COL212,
ale.COL213,
ale.COL214,
ale.COL215,
ale.COL216,
ale.COL217,
ale.COL218,
ale.COL219,
ale.COL220,
ale.COL221,
ale.COL222,
ale.COL223,
ale.COL224,
ale.COL225,
ale.COL226,
ale.COL227,
ale.COL228,
ale.COL229,
ale.COL230,
ale.COL231,
ale.COL232,
ale.COL233,
ale.COL234,
ale.COL235,
ale.COL236,
ale.COL237,
ale.COL238,
ale.COL239,
ale.COL240,
ale.COL241,
ale.COL242,
ale.COL243,
ale.COL244,
ale.COL245,
ale.COL246,
ale.COL247,
ale.COL248,
ale.COL249,
ale.COL250 ,
ale.COL251 ,
ale.COL252 ,
ale.COL253 ,
ale.COL254 ,
ale.COL256 ,
ale.COL255 ,
ale.COL257 ,
ale.COL258 ,
ale.COL259 ,
ale.COL260 ,
ale.COL261 ,
ale.COL262 ,
ale.COL263 ,
ale.COL264 ,
ale.COL265 ,
ale.COL266 ,
ale.COL267 ,
ale.COL268 ,
ale.COL269 ,
ale.COL270 ,
ale.COL271 ,
ale.COL272 ,
ale.COL273 ,
ale.COL274 ,
ale.COL275 ,
ale.COL276 ,
ale.COL277 ,
ale.COL278 ,
ale.COL279 ,
ale.COL280 ,
ale.COL281 ,
ale.COL282 ,
ale.COL283 ,
ale.COL284 ,
ale.COL285 ,
ale.COL286 ,
ale.COL287 ,
ale.COL288 ,
ale.COL289 ,
ale.COL290 ,
ale.COL291 ,
ale.COL292 ,
ale.COL293 ,
ale.COL294 ,
ale.COL295 ,
ale.COL296 ,
ale.COL297 ,
ale.COL298 ,
ale.COL299 ,
ale.COL300 ,
ale.SALES_AGENT_EMAIL_ADDRESS,
ale.RESOURCE_ID,
ale.location_id,
ale.contact_point_id,
ale.orig_system_reference,
ale.CUSTOM_COLUMN1,
ale.CUSTOM_COLUMN2,
ale.CUSTOM_COLUMN3,
ale.CUSTOM_COLUMN4,
ale.CUSTOM_COLUMN5,
ale.CUSTOM_COLUMN6,
ale.CUSTOM_COLUMN7,
ale.CUSTOM_COLUMN8,
ale.CUSTOM_COLUMN9,
ale.CUSTOM_COLUMN10,
ale.CUSTOM_COLUMN11,
ale.CUSTOM_COLUMN12,
ale.CUSTOM_COLUMN13,
ale.CUSTOM_COLUMN14,
ale.CUSTOM_COLUMN15,
ale.CUSTOM_COLUMN16,
ale.CUSTOM_COLUMN17,
ale.CUSTOM_COLUMN18,
ale.CUSTOM_COLUMN19,
ale.CUSTOM_COLUMN20,
ale.CUSTOM_COLUMN21,
ale.CUSTOM_COLUMN22,
ale.CUSTOM_COLUMN23,
ale.CUSTOM_COLUMN24,
ale.CUSTOM_COLUMN25
) =
(select
'Y',
'Y',
ail.SUFFIX,
ail.FIRST_NAME,
ail.LAST_NAME,
ail.CUSTOMER_NAME,
ail.TITLE,
ail.ADDRESS_LINE1,
ail.ADDRESS_LINE2,
ail.CITY,
ail.STATE,
ail.ZIPCODE,
ail.COUNTRY,
ail.FAX,
ail.PHONE,
ail.EMAIL_ADDRESS,
ail.COL1,
ail.COL2,
ail.COL3,
ail.COL4,
ail.COL5,
ail.COL6,
ail.COL7,
ail.COL8,
ail.COL9,
ail.COL10,
ail.COL11,
ail.COL12,
ail.COL13,
ail.COL14,
ail.COL15,
ail.COL16,
ail.COL17,
ail.COL18,
ail.COL19,
ail.COL20,
ail.COL21,
ail.COL22,
ail.COL23,
ail.COL24,
ail.COL25,
ail.COL26,
ail.COL27,
ail.COL28,
ail.COL29,
ail.COL30,
ail.COL31,
ail.COL32,
ail.COL33,
ail.COL34,
ail.COL35,
ail.COL36,
ail.COL37,
ail.COL38,
ail.COL39,
ail.COL40,
ail.COL41,
ail.COL42,
ail.COL43,
ail.COL44,
ail.COL45,
ail.COL46,
ail.COL47,
ail.COL48,
ail.COL49,
ail.COL50,
ail.COL51,
ail.COL52,
ail.COL53,
ail.COL54,
ail.COL55,
ail.COL56,
ail.COL57,
ail.COL58,
ail.COL59,
ail.COL60,
ail.COL61,
ail.COL62,
ail.COL63,
ail.COL64,
ail.COL65,
ail.COL66,
ail.COL67,
ail.COL68,
ail.COL69,
ail.COL70,
ail.COL71,
ail.COL72,
ail.COL73,
ail.COL74,
ail.COL75,
ail.COL76,
ail.COL77,
ail.COL78,
ail.COL79,
ail.COL80,
ail.COL81,
ail.COL82,
ail.COL83,
ail.COL84,
ail.COL85,
ail.COL86,
ail.COL87,
ail.COL88,
ail.COL89,
ail.COL90,
ail.COL91,
ail.COL92,
ail.COL93,
ail.COL94,
ail.COL95,
ail.COL96,
ail.COL97,
ail.COL98,
ail.COL99,
ail.COL100,
ail.COL101,
ail.COL102,
ail.COL103,
ail.COL104,
ail.COL105,
ail.COL106,
ail.COL107,
ail.COL108,
ail.COL109,
ail.COL110,
ail.COL111,
ail.COL112,
ail.COL113,
ail.COL114,
ail.COL115,
ail.COL116,
ail.COL117,
ail.COL118,
ail.COL119,
ail.COL120,
ail.COL121,
ail.COL122,
ail.COL123,
ail.COL124,
ail.COL125,
ail.COL126,
ail.COL127,
ail.COL128,
ail.COL129,
ail.COL130,
ail.COL131,
ail.COL132,
ail.COL133,
ail.COL134,
ail.COL135,
ail.COL136,
ail.COL137,
ail.COL138,
ail.COL139,
ail.COL140,
ail.COL141,
ail.COL142,
ail.COL143,
ail.COL144,
ail.COL145,
ail.COL146,
ail.COL147,
ail.COL148,
ail.COL149,
ail.COL150,
ail.COL151,
ail.COL152,
ail.COL153,
ail.COL154,
ail.COL155,
ail.COL156,
ail.COL157,
ail.COL158,
ail.COL159,
ail.COL160,
ail.COL161,
ail.COL162,
ail.COL163,
ail.COL164,
ail.COL165,
ail.COL166,
ail.COL167,
ail.COL168,
ail.COL169,
ail.COL170,
ail.COL171,
ail.COL172,
ail.COL173,
ail.COL174,
ail.COL175,
ail.COL176,
ail.COL177,
ail.COL178,
ail.COL179,
ail.COL180,
ail.COL181,
ail.COL182,
ail.COL183,
ail.COL184,
ail.COL185,
ail.COL186,
ail.COL187,
ail.COL188,
ail.COL189,
ail.COL190,
ail.COL191,
ail.COL192,
ail.COL193,
ail.COL194,
ail.COL195,
ail.COL196,
ail.COL197,
ail.COL198,
ail.COL199,
ail.COL200,
ail.COL201,
ail.COL202,
ail.COL203,
ail.COL204,
ail.COL205,
ail.COL206,
ail.COL207,
ail.COL208,
ail.COL209,
ail.COL210,
ail.COL211,
ail.COL212,
ail.COL213,
ail.COL214,
ail.COL215,
ail.COL216,
ail.COL217,
ail.COL218,
ail.COL219,
ail.COL220,
ail.COL221,
ail.COL222,
ail.COL223,
ail.COL224,
ail.COL225,
ail.COL226,
ail.COL227,
ail.COL228,
ail.COL229,
ail.COL230,
ail.COL231,
ail.COL232,
ail.COL233,
ail.COL234,
ail.COL235,
ail.COL236,
ail.COL237,
ail.COL238,
ail.COL239,
ail.COL240,
ail.COL241,
ail.COL242,
ail.COL243,
ail.COL244,
ail.COL245,
ail.COL246,
ail.COL247,
ail.COL248,
ail.COL249,
ail.COL250 ,
ail.COL251 ,
ail.COL252 ,
ail.COL253 ,
ail.COL254 ,
ail.COL256 ,
ail.COL255 ,
ail.COL257 ,
ail.COL258 ,
ail.COL259 ,
ail.COL260 ,
ail.COL261 ,
ail.COL262 ,
ail.COL263 ,
ail.COL264 ,
ail.COL265 ,
ail.COL266 ,
ail.COL267 ,
ail.COL268 ,
ail.COL269 ,
ail.COL270 ,
ail.COL271 ,
ail.COL272 ,
ail.COL273 ,
ail.COL274 ,
ail.COL275 ,
ail.COL276 ,
ail.COL277 ,
ail.COL278 ,
ail.COL279 ,
ail.COL280 ,
ail.COL281 ,
ail.COL282 ,
ail.COL283 ,
ail.COL284 ,
ail.COL285 ,
ail.COL286 ,
ail.COL287 ,
ail.COL288 ,
ail.COL289 ,
ail.COL290 ,
ail.COL291 ,
ail.COL292 ,
ail.COL293 ,
ail.COL294 ,
ail.COL295 ,
ail.COL296 ,
ail.COL297 ,
ail.COL298 ,
ail.COL299 ,
ail.COL300 ,
ail.SALES_AGENT_EMAIL_ADDRESS,
ail.RESOURCE_ID,
ail.location_id,
ail.contact_point_id,
ail.orig_system_reference,
ail.CUSTOM_COLUMN1,
ail.CUSTOM_COLUMN2,
ail.CUSTOM_COLUMN3,
ail.CUSTOM_COLUMN4,
ail.CUSTOM_COLUMN5,
ail.CUSTOM_COLUMN6,
ail.CUSTOM_COLUMN7,
ail.CUSTOM_COLUMN8,
ail.CUSTOM_COLUMN9,
ail.CUSTOM_COLUMN10,
ail.CUSTOM_COLUMN11,
ail.CUSTOM_COLUMN12,
ail.CUSTOM_COLUMN13,
ail.CUSTOM_COLUMN14,
ail.CUSTOM_COLUMN15,
ail.CUSTOM_COLUMN16,
ail.CUSTOM_COLUMN17,
ail.CUSTOM_COLUMN18,
ail.CUSTOM_COLUMN19,
ail.CUSTOM_COLUMN20,
ail.CUSTOM_COLUMN21,
ail.CUSTOM_COLUMN22,
ail.CUSTOM_COLUMN23,
ail.CUSTOM_COLUMN24,
ail.CUSTOM_COLUMN25
from ams_list_entries ail,
ams_act_lists als
where als.ACT_LIST_HEADER_ID = ale.list_select_action_id
and als.LIST_HEADER_ID = ail.list_header_id
and ail.list_entry_source_system_id = ale.list_entry_source_system_id
and ail.enabled_flag = 'Y'
and rownum <=1 )
where arc_list_select_action_from = 'LIST'
and list_header_id = p_list_header_id
and exists (select 'x'
from ams_list_entries ail,
ams_act_lists als
where als.ACT_LIST_HEADER_ID = ale.list_select_action_id
and als.LIST_HEADER_ID = ail.list_header_id
and ail.list_entry_source_system_id = ale.list_entry_source_system_id
and ail.enabled_flag = 'Y' )
and exists (select 'x'
from ams_act_lists als1
where als1.ACT_LIST_HEADER_ID = ale.list_select_action_id
and als1.LIST_ACTION_TYPE = 'INCLUDE' );
write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
write_to_act_log('Generating list(which has selections based on LIST) in update mode.','LIST',g_list_header_id,'LOW');
update ams_list_entries ale
set (
ale.newly_updated_flag ,
ale.enabled_flag ,
ale.SUFFIX,
ale.FIRST_NAME,
ale.LAST_NAME,
ale.CUSTOMER_NAME,
ale.TITLE,
ale.ADDRESS_LINE1,
ale.ADDRESS_LINE2,
ale.CITY,
ale.STATE,
ale.ZIPCODE,
ale.COUNTRY,
ale.FAX,
ale.PHONE,
ale.EMAIL_ADDRESS,
ale.COL1,
ale.COL2,
ale.COL3,
ale.COL4,
ale.COL5,
ale.COL6,
ale.COL7,
ale.COL8,
ale.COL9,
ale.COL10,
ale.COL11,
ale.COL12,
ale.COL13,
ale.COL14,
ale.COL15,
ale.COL16,
ale.COL17,
ale.COL18,
ale.COL19,
ale.COL20,
ale.COL21,
ale.COL22,
ale.COL23,
ale.COL24,
ale.COL25,
ale.COL26,
ale.COL27,
ale.COL28,
ale.COL29,
ale.COL30,
ale.COL31,
ale.COL32,
ale.COL33,
ale.COL34,
ale.COL35,
ale.COL36,
ale.COL37,
ale.COL38,
ale.COL39,
ale.COL40,
ale.COL41,
ale.COL42,
ale.COL43,
ale.COL44,
ale.COL45,
ale.COL46,
ale.COL47,
ale.COL48,
ale.COL49,
ale.COL50,
ale.COL51,
ale.COL52,
ale.COL53,
ale.COL54,
ale.COL55,
ale.COL56,
ale.COL57,
ale.COL58,
ale.COL59,
ale.COL60,
ale.COL61,
ale.COL62,
ale.COL63,
ale.COL64,
ale.COL65,
ale.COL66,
ale.COL67,
ale.COL68,
ale.COL69,
ale.COL70,
ale.COL71,
ale.COL72,
ale.COL73,
ale.COL74,
ale.COL75,
ale.COL76,
ale.COL77,
ale.COL78,
ale.COL79,
ale.COL80,
ale.COL81,
ale.COL82,
ale.COL83,
ale.COL84,
ale.COL85,
ale.COL86,
ale.COL87,
ale.COL88,
ale.COL89,
ale.COL90,
ale.COL91,
ale.COL92,
ale.COL93,
ale.COL94,
ale.COL95,
ale.COL96,
ale.COL97,
ale.COL98,
ale.COL99,
ale.COL100,
ale.COL101,
ale.COL102,
ale.COL103,
ale.COL104,
ale.COL105,
ale.COL106,
ale.COL107,
ale.COL108,
ale.COL109,
ale.COL110,
ale.COL111,
ale.COL112,
ale.COL113,
ale.COL114,
ale.COL115,
ale.COL116,
ale.COL117,
ale.COL118,
ale.COL119,
ale.COL120,
ale.COL121,
ale.COL122,
ale.COL123,
ale.COL124,
ale.COL125,
ale.COL126,
ale.COL127,
ale.COL128,
ale.COL129,
ale.COL130,
ale.COL131,
ale.COL132,
ale.COL133,
ale.COL134,
ale.COL135,
ale.COL136,
ale.COL137,
ale.COL138,
ale.COL139,
ale.COL140,
ale.COL141,
ale.COL142,
ale.COL143,
ale.COL144,
ale.COL145,
ale.COL146,
ale.COL147,
ale.COL148,
ale.COL149,
ale.COL150,
ale.COL151,
ale.COL152,
ale.COL153,
ale.COL154,
ale.COL155,
ale.COL156,
ale.COL157,
ale.COL158,
ale.COL159,
ale.COL160,
ale.COL161,
ale.COL162,
ale.COL163,
ale.COL164,
ale.COL165,
ale.COL166,
ale.COL167,
ale.COL168,
ale.COL169,
ale.COL170,
ale.COL171,
ale.COL172,
ale.COL173,
ale.COL174,
ale.COL175,
ale.COL176,
ale.COL177,
ale.COL178,
ale.COL179,
ale.COL180,
ale.COL181,
ale.COL182,
ale.COL183,
ale.COL184,
ale.COL185,
ale.COL186,
ale.COL187,
ale.COL188,
ale.COL189,
ale.COL190,
ale.COL191,
ale.COL192,
ale.COL193,
ale.COL194,
ale.COL195,
ale.COL196,
ale.COL197,
ale.COL198,
ale.COL199,
ale.COL200,
ale.COL201,
ale.COL202,
ale.COL203,
ale.COL204,
ale.COL205,
ale.COL206,
ale.COL207,
ale.COL208,
ale.COL209,
ale.COL210,
ale.COL211,
ale.COL212,
ale.COL213,
ale.COL214,
ale.COL215,
ale.COL216,
ale.COL217,
ale.COL218,
ale.COL219,
ale.COL220,
ale.COL221,
ale.COL222,
ale.COL223,
ale.COL224,
ale.COL225,
ale.COL226,
ale.COL227,
ale.COL228,
ale.COL229,
ale.COL230,
ale.COL231,
ale.COL232,
ale.COL233,
ale.COL234,
ale.COL235,
ale.COL236,
ale.COL237,
ale.COL238,
ale.COL239,
ale.COL240,
ale.COL241,
ale.COL242,
ale.COL243,
ale.COL244,
ale.COL245,
ale.COL246,
ale.COL247,
ale.COL248,
ale.COL249,
ale.COL250 ,
ale.COL251 ,
ale.COL252 ,
ale.COL253 ,
ale.COL254 ,
ale.COL256 ,
ale.COL255 ,
ale.COL257 ,
ale.COL258 ,
ale.COL259 ,
ale.COL260 ,
ale.COL261 ,
ale.COL262 ,
ale.COL263 ,
ale.COL264 ,
ale.COL265 ,
ale.COL266 ,
ale.COL267 ,
ale.COL268 ,
ale.COL269 ,
ale.COL270 ,
ale.COL271 ,
ale.COL272 ,
ale.COL273 ,
ale.COL274 ,
ale.COL275 ,
ale.COL276 ,
ale.COL277 ,
ale.COL278 ,
ale.COL279 ,
ale.COL280 ,
ale.COL281 ,
ale.COL282 ,
ale.COL283 ,
ale.COL284 ,
ale.COL285 ,
ale.COL286 ,
ale.COL287 ,
ale.COL288 ,
ale.COL289 ,
ale.COL290 ,
ale.COL291 ,
ale.COL292 ,
ale.COL293 ,
ale.COL294 ,
ale.COL295 ,
ale.COL296 ,
ale.COL297 ,
ale.COL298 ,
ale.COL299 ,
ale.COL300
) =
(select
'Y',
'Y',
ail.SUFFIX,
ail.FIRST_NAME,
ail.LAST_NAME,
ail.CUSTOMER_NAME,
ail.TITLE,
ail.ADDRESS_LINE1,
ail.ADDRESS_LINE2,
ail.CITY,
ail.STATE,
ail.ZIPCODE,
ail.COUNTRY,
ail.FAX,
ail.PHONE,
ail.EMAIL_ADDRESS,
ail.COL1,
ail.COL2,
ail.COL3,
ail.COL4,
ail.COL5,
ail.COL6,
ail.COL7,
ail.COL8,
ail.COL9,
ail.COL10,
ail.COL11,
ail.COL12,
ail.COL13,
ail.COL14,
ail.COL15,
ail.COL16,
ail.COL17,
ail.COL18,
ail.COL19,
ail.COL20,
ail.COL21,
ail.COL22,
ail.COL23,
ail.COL24,
ail.COL25,
ail.COL26,
ail.COL27,
ail.COL28,
ail.COL29,
ail.COL30,
ail.COL31,
ail.COL32,
ail.COL33,
ail.COL34,
ail.COL35,
ail.COL36,
ail.COL37,
ail.COL38,
ail.COL39,
ail.COL40,
ail.COL41,
ail.COL42,
ail.COL43,
ail.COL44,
ail.COL45,
ail.COL46,
ail.COL47,
ail.COL48,
ail.COL49,
ail.COL50,
ail.COL51,
ail.COL52,
ail.COL53,
ail.COL54,
ail.COL55,
ail.COL56,
ail.COL57,
ail.COL58,
ail.COL59,
ail.COL60,
ail.COL61,
ail.COL62,
ail.COL63,
ail.COL64,
ail.COL65,
ail.COL66,
ail.COL67,
ail.COL68,
ail.COL69,
ail.COL70,
ail.COL71,
ail.COL72,
ail.COL73,
ail.COL74,
ail.COL75,
ail.COL76,
ail.COL77,
ail.COL78,
ail.COL79,
ail.COL80,
ail.COL81,
ail.COL82,
ail.COL83,
ail.COL84,
ail.COL85,
ail.COL86,
ail.COL87,
ail.COL88,
ail.COL89,
ail.COL90,
ail.COL91,
ail.COL92,
ail.COL93,
ail.COL94,
ail.COL95,
ail.COL96,
ail.COL97,
ail.COL98,
ail.COL99,
ail.COL100,
ail.COL101,
ail.COL102,
ail.COL103,
ail.COL104,
ail.COL105,
ail.COL106,
ail.COL107,
ail.COL108,
ail.COL109,
ail.COL110,
ail.COL111,
ail.COL112,
ail.COL113,
ail.COL114,
ail.COL115,
ail.COL116,
ail.COL117,
ail.COL118,
ail.COL119,
ail.COL120,
ail.COL121,
ail.COL122,
ail.COL123,
ail.COL124,
ail.COL125,
ail.COL126,
ail.COL127,
ail.COL128,
ail.COL129,
ail.COL130,
ail.COL131,
ail.COL132,
ail.COL133,
ail.COL134,
ail.COL135,
ail.COL136,
ail.COL137,
ail.COL138,
ail.COL139,
ail.COL140,
ail.COL141,
ail.COL142,
ail.COL143,
ail.COL144,
ail.COL145,
ail.COL146,
ail.COL147,
ail.COL148,
ail.COL149,
ail.COL150,
ail.COL151,
ail.COL152,
ail.COL153,
ail.COL154,
ail.COL155,
ail.COL156,
ail.COL157,
ail.COL158,
ail.COL159,
ail.COL160,
ail.COL161,
ail.COL162,
ail.COL163,
ail.COL164,
ail.COL165,
ail.COL166,
ail.COL167,
ail.COL168,
ail.COL169,
ail.COL170,
ail.COL171,
ail.COL172,
ail.COL173,
ail.COL174,
ail.COL175,
ail.COL176,
ail.COL177,
ail.COL178,
ail.COL179,
ail.COL180,
ail.COL181,
ail.COL182,
ail.COL183,
ail.COL184,
ail.COL185,
ail.COL186,
ail.COL187,
ail.COL188,
ail.COL189,
ail.COL190,
ail.COL191,
ail.COL192,
ail.COL193,
ail.COL194,
ail.COL195,
ail.COL196,
ail.COL197,
ail.COL198,
ail.COL199,
ail.COL200,
ail.COL201,
ail.COL202,
ail.COL203,
ail.COL204,
ail.COL205,
ail.COL206,
ail.COL207,
ail.COL208,
ail.COL209,
ail.COL210,
ail.COL211,
ail.COL212,
ail.COL213,
ail.COL214,
ail.COL215,
ail.COL216,
ail.COL217,
ail.COL218,
ail.COL219,
ail.COL220,
ail.COL221,
ail.COL222,
ail.COL223,
ail.COL224,
ail.COL225,
ail.COL226,
ail.COL227,
ail.COL228,
ail.COL229,
ail.COL230,
ail.COL231,
ail.COL232,
ail.COL233,
ail.COL234,
ail.COL235,
ail.COL236,
ail.COL237,
ail.COL238,
ail.COL239,
ail.COL240,
ail.COL241,
ail.COL242,
ail.COL243,
ail.COL244,
ail.COL245,
ail.COL246,
ail.COL247,
ail.COL248,
ail.COL249,
ail.COL250 ,
ail.COL251 ,
ail.COL252 ,
ail.COL253 ,
ail.COL254 ,
ail.COL256 ,
ail.COL255 ,
ail.COL257 ,
ail.COL258 ,
ail.COL259 ,
ail.COL260 ,
ail.COL261 ,
ail.COL262 ,
ail.COL263 ,
ail.COL264 ,
ail.COL265 ,
ail.COL266 ,
ail.COL267 ,
ail.COL268 ,
ail.COL269 ,
ail.COL270 ,
ail.COL271 ,
ail.COL272 ,
ail.COL273 ,
ail.COL274 ,
ail.COL275 ,
ail.COL276 ,
ail.COL277 ,
ail.COL278 ,
ail.COL279 ,
ail.COL280 ,
ail.COL281 ,
ail.COL282 ,
ail.COL283 ,
ail.COL284 ,
ail.COL285 ,
ail.COL286 ,
ail.COL287 ,
ail.COL288 ,
ail.COL289 ,
ail.COL290 ,
ail.COL291 ,
ail.COL292 ,
ail.COL293 ,
ail.COL294 ,
ail.COL295 ,
ail.COL296 ,
ail.COL297 ,
ail.COL298 ,
ail.COL299 ,
ail.COL300
from ams_list_entries ail,
ams_list_select_actions als
where als.list_select_action_id = ale.list_select_action_id
and als.incl_object_id = ail.list_header_id
and ail.list_entry_source_system_id = ale.list_entry_source_system_id
and ail.enabled_flag = 'Y'
and rownum <=1 )
where arc_list_select_action_from = 'LIST'
and list_header_id = p_list_header_id
and exists (select 'x'
from ams_list_entries ail,
ams_list_select_actions als
where als.list_select_action_id = ale.list_select_action_id
and als.incl_object_id = ail.list_header_id
and ail.list_entry_source_system_id = ale.list_entry_source_system_id
and ail.enabled_flag = 'Y' )
and exists (select 'x'
from ams_list_select_actions als1
where als1.list_select_action_id = ale.list_select_action_id
and als1.list_action_type = 'INCLUDE' );
write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
procedure update_import_list_entries(p_list_header_id in number) is
cursor c1 is
select imp.import_type
from ams_imp_list_headers_all imp,
ams_list_select_actions ail,
ams_list_headers_all alh
where alh.list_header_id = p_list_header_id
and alh.list_header_id = ail.action_used_by_id
and ail.arc_action_used_by = 'LIST'
and ail.arc_incl_object_from = 'IMPH'
and imp.import_list_header_id = ail.incl_object_id ;
write_to_act_log('Generating list(which has selections based on imported B2B list) in update mode.','LIST',g_list_header_id,'LOW');
update ams_list_entries ale
set (
ale.newly_updated_flag ,
-- ale.enabled_flag ,
ale.ADDRESS_LINE1,
ale.ADDRESS_LINE2,
ale.COL127,
ale.COL128,
ale.COL227,
ale.CITY,
ale.COUNTRY,
ale.COL118,
ale.COL142,
ale.COL138,
ale.COL122,
ale.EMAIL_ADDRESS,
ale.COL239,
ale.FIRST_NAME,
ale.COL243,
ale.COL144,
ale.LAST_NAME,
ale.COL251,
ale.COL252,
ale.COL137,
ale.SUFFIX,
ale.COL259,
ale.COL6,
ale.COL5,
ale.COL7,
ale.PHONE,
ale.ZIPCODE,
ale.COL120,
ale.STATE,
ale.COL125,
ale.COL2,
ale.TITLE,
ale.customer_name,
ale.party_id,
ale.COL276 ,
ale.NOTES ,
ale.VEHICLE_RESPONSE_CODE ,
ale.SALES_AGENT_EMAIL_ADDRESS ,
ale.RESOURCE_ID ,
ale.col147,
ale.location_id ,
ale.contact_point_id ,
ale.orig_system_reference,
col116,
col117
)
=
( select
'Y',
-- 'Y',
ail.ADDRESS1,
ail.ADDRESS2,
ail.BEST_TIME_CONTACT_BEGIN,
ail.BEST_TIME_CONTACT_END,
ail.CEO_NAME,
ail.CITY,
ail.COUNTRY,
ail.COUNTY,
ail.DECISION_MAKER_FLAG,
ail.DEPARTMENT,
ail.DUNS_NUMBER,
ail.EMAIL_ADDRESS,
ail.EMPLOYEES_TOTAL,
ail.PERSON_FIRST_NAME,
ail.FISCAL_YEAREND_MONTH,
ail.JOB_TITLE,
ail.PERSON_LAST_NAME,
ail.LEGAL_STATUS,
ail.LINE_OF_BUSINESS,
ail.PERSON_MIDDLE_NAME,
ail.PERSON_NAME_SUFFIX,
ail.party_name,
ail.PHONE_AREA_CODE,
ail.PHONE_COUNTRY_CODE,
ail.PHONE_EXTENTION,
ail.PHONE_NUMBER,
ail.POSTAL_CODE,
ail.PROVINCE,
ail.STATE,
ail.TAX_REFERENCE,
ail.TIME_ZONE,
ail.PERSON_NAME_PREFIX,
ail.party_name
,ail.party_id
,ail.YEAR_ESTABLISHED
,ail.NOTES ,
ail.VEHICLE_RESPONSE_CODE ,
ail.SALES_AGENT_EMAIL_ADDRESS ,
ail.RESOURCE_ID ,
ail.ORGANIZATION_ID,
ail.location_id ,
ail.contact_point_id ,
ail.orig_system_reference,
ail.address3,
ail.address4
from ams_hz_b2b_mapping_v ail,
ams_list_select_actions als
where ail.import_list_header_id = als.incl_object_id
and als.list_select_action_id = ale.list_select_action_id
and ail.party_id = ale.list_entry_source_system_id
and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
where arc_list_select_action_from = 'IMPH'
and list_header_id = p_list_header_id
and exists (select 'x'
from ams_list_select_actions als1
where als1.list_select_action_id = ale.list_select_action_id
and als1.list_action_type = 'INCLUDE' );
write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
write_to_act_log('Generating list(which has selections based on imported B2C list) in update mode.','LIST',g_list_header_id,'LOW');
update ams_list_entries ale
set (
ale.newly_updated_flag ,
-- ale.enabled_flag ,
ale.customer_name,
ale.ADDRESS_LINE1,
ale.ADDRESS_LINE2,
ale.CITY,
ale.COL127,
ale.COL128,
ale.COL118,
ale.COUNTRY,
ale.FIRST_NAME,
ale.LAST_NAME,
ale.COL137,
ale.EMAIL_ADDRESS,
ale.col70,
ale.COL145,
ale.STATE,
ale.ZIPCODE,
ale.COL120,
ale.TITLE,
ale.COL2,
ale.col5,
ale.col6,
ale.PHONE,
ale.col7,
ale.party_id,
ale.SUFFIX ,
ale.NOTES ,
ale.VEHICLE_RESPONSE_CODE ,
ale.SALES_AGENT_EMAIL_ADDRESS ,
ale.RESOURCE_ID ,
ale.location_id ,
ale.contact_point_id ,
ale.orig_system_reference,
ale.col116,
ale.col117
)
=
(select
'Y',
-- 'Y',
ail.PERSON_LAST_NAME || ' , ' || ail.PERSON_FIRST_NAME ,
ail.ADDRESS1,
ail.ADDRESS2,
ail.CITY,
ail.BEST_TIME_CONTACT_BEGIN,
ail.BEST_TIME_CONTACT_END,
ail.COUNTY,
ail.COUNTRY,
ail.PERSON_FIRST_NAME,
ail.PERSON_LAST_NAME,
ail.PERSON_MIDDLE_NAME,
ail.EMAIL_ADDRESS,
ail.GENDER,
ail.HOUSEHOLD_INCOME,
ail.STATE,
ail.POSTAL_CODE,
ail.PROVINCE,
ail.PERSON_NAME_PREFIX,
ail.TIME_ZONE ,
ail.PHONE_COUNTRY_CODE,
ail.PHONE_AREA_CODE ,
ail.PHONE_NUMBER ,
ail.PHONE_EXTENTION ,
ail.party_id,
ail.PERSON_NAME_SUFFIX ,
ail.NOTES ,
ail.VEHICLE_RESPONSE_CODE ,
ail.SALES_AGENT_EMAIL_ADDRESS ,
ail.RESOURCE_ID ,
ail.location_id ,
ail.contact_point_id ,
ail.orig_system_reference,
ail.address3,
ail.address4
from ams_hz_b2c_mapping_v ail,
ams_list_select_actions als
where ail.import_list_header_id = als.incl_object_id
and als.list_select_action_id = ale.list_select_action_id
and ail.party_id = ale.list_entry_source_system_id
and ail.IMPORT_SOURCE_LINE_ID = ale.IMP_SOURCE_LINE_ID)
where ale.arc_list_select_action_from = 'IMPH'
and ale.list_header_id = p_list_header_id
and exists (select 'x'
from ams_list_select_actions als1
where als1.list_select_action_id = ale.list_select_action_id
and als1.list_action_type = 'INCLUDE' );
write_to_act_log(sql%rowcount||' entries updated.','LIST',g_list_header_id,'LOW');
IS SELECT a.list_source_type_id,
a.source_type_code,
a.source_object_name,
a.source_object_pk_field,
a.master_source_type_flag
FROM ams_list_src_types a,
ams_list_src_type_usages b
WHERE a.source_type_code = b.source_type_code
AND b.list_header_id = p_list_header_id
AND master_source_type_flag = 'Y' ;
SELECT al.SUB_SOURCE_TYPE_ID,
al.SUB_SOURCE_TYPE_PK_COLUMN,
als.source_object_name,
als.source_type_code,
al.master_source_type_pk_column
FROM ams_list_src_type_assocs al,
ams_list_src_types als ,
ams_list_src_type_usages b
WHERE al.MASTER_SOURCE_TYPE_ID = c_master_type_id
AND als.list_source_type_id = al.sub_source_type_id
AND als.source_type_code = b.source_type_code
AND b.list_header_id = p_list_header_id ;
SELECT field_column_name,
source_column_name
FROM ams_list_src_fields
WHERE list_source_type_id = p_list_source_type_id
and used_in_list_entries = 'Y';
l_update_str VARCHAR2(32767);
l_select_str VARCHAR2(32767);
select field_column_name
from ams_list_src_fields
where list_source_type_id = l_list_source_type_id--l_sub_source_type_id
and source_column_name = l_dummy_pk_column;
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;
write_to_act_log('No entries in remote schema for this list/target group. Cannot update.', 'LIST', g_list_header_id,'LOW');
write_to_act_log('List entries will be updated in remote schema.', 'LIST', g_list_header_id,'LOW');
l_update_str := 'UPDATE ams_list_entries SET (';
l_select_str := ' ) = ( SELECT ';
l_add_where_clause := ' AND arc_list_select_action_from not in ' ||
-- ' (''LIST'', ''IMPH'') ';LPO
l_update_str := l_update_str||l_list_entry_columns(i)||',';
l_select_str := l_select_str||l_source_columns(i)||',';
l_update_str := substr(l_update_str,1,length(l_update_str) - 1);
l_select_str := substr(l_select_str,1,length(l_select_str) - 1);
/* l_update_str := l_update_str || 'newly_updated_flag' ;
l_select_str := l_select_str || ''''|| 'Y'||'''';
l_update_str := l_update_str || ','|| 'enabled_flag' ;
l_select_str := l_select_str || ','|| ''''|| 'Y'||'''';*/
l_select_str := l_select_str||' FROM '||
l_source_object_name||' WHERE '||
l_source_object_pk_field
||' = list_entry_source_system_id ';
l_update_str := l_update_str||l_select_str||')'||
l_where_clause||l_type_clause || l_add_where_clause;
write_to_act_log('Update statement : '||l_update_str, 'LIST', g_list_header_id,'LOW');
write_to_act_log('Executing the update statement in local schema', 'LIST', g_list_header_id,'LOW');
EXECUTE IMMEDIATE l_update_str using to_char(p_list_header_id) , l_source_code;
write_to_act_log('Executing the update statement in remote schema', 'LIST', g_list_header_id,'LOW');
l_update_str,
l_source_code,
OUT l_tot_cnt,
'UPDATE';
l_update_str := 'UPDATE ams_list_entries SET (';
l_select_str := ' ) = ( SELECT ';
l_add_where_clause := ' AND arc_list_select_action_from not in ' ||
-- ' (''LIST'', ''IMPH'') ';LPO
l_update_str := l_update_str||l_list_entry_columns(i)||',';
l_select_str := l_select_str||l_source_columns(i)||',';
l_update_str := substrb(l_update_str,1,length(l_update_str)-1);
l_select_str := substrb(l_select_str,1,length(l_select_str)-1);
l_select_str := l_select_str||' FROM '||
l_SUB_SOURCE_OBJECT_NAME||' WHERE '||
l_SUB_SOURCE_TYPE_PK_COLUMN
||' = list_entry_source_system_id ';
l_select_str := l_select_str||' FROM '||
l_SUB_SOURCE_OBJECT_NAME||' WHERE '||
l_SUB_SOURCE_TYPE_PK_COLUMN
||' = ' || l_dummy_sr_column ;
l_update_str := l_update_str||l_select_str||')'||
l_where_clause||l_type_clause || l_add_where_clause;
l_no_of_chunks := ceil(length(l_update_str)/2000 );
write_to_act_log(substrb(l_update_str,(2000*i) - 1999,2000), 'LIST', g_list_header_id,'LOW');
EXECUTE IMMEDIATE l_update_str using to_char(p_list_header_id) , l_source_code;
l_update_str,
l_source_code,
OUT l_tot_cnt,
'UPDATE';
write_to_act_log('No field is updated since no field is mapped for data source : '||l_SUB_SOURCE_OBJECT_NAME, 'LIST', g_list_header_id,'LOW');
write_to_act_log('Calling update_list_entries to update selections based on list.','LIST', g_list_header_id,'LOW');
write_to_act_log('Calling update_import_list_entries to update selections based on import.','LIST', g_list_header_id,'LOW');
update_import_list_entries (p_list_header_id);
write_to_act_log('Error while generating list in update mode : '||sqlerrm||sqlcode, 'LIST', g_list_header_id,'HIGH');
select no_of_rows_in_list
from ams_list_headers_all
where list_header_id = c_list_header_id ;
select nvl(stypes.remote_flag,'N') ,database_link
from ams_list_src_types stypes, ams_list_headers_all list
where list.list_source_type = stypes.source_type_code
and list_header_id = p_list_header_id;
l_list_selection varchar2(1);
l_onlylist_selection varchar2(1);
select nvl(no_of_rows_in_list ,0)
from ams_list_headers_all
where list_header_id = p_list_header_id;
cursor c_list_selection is
select 'Y' from ams_list_select_actions
where action_used_by_id = p_list_header_id
and arc_action_used_by = 'LIST'
and arc_incl_object_from in ('CELL','DIWB','SQL');
cursor c_only_list_selection is
select 'Y' from ams_list_select_actions act, ams_list_headers_all head
where act.action_used_by_id = p_list_header_id
and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
and act.INCL_OBJECT_ID = head.list_header_id
and head.status_code = 'AVAILABLE'
and head.MIGRATION_DATE is null;
SELECT list_rule_id
FROM ams_list_rule_usages
WHERE list_header_id = g_list_header_id;*/
SELECT us.list_rule_id
FROM ams_list_rule_usages us, ams_list_rules_all rules
WHERE us.list_header_id = g_list_header_id
AND us.list_rule_id = rules.list_rule_id
AND rules.list_source_type = l_listheader_rec.list_source_type
AND rules.list_rule_type = 'TARGET';
SELECT a.enabled_flag
FROM ams_list_src_types a,
ams_list_headers_all b
WHERE a.source_type_code = b.list_source_type
AND b.list_header_id = c_list_header_id;
select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
where hd.list_header_id = p_list_header_id
and hd.LIST_SOURCE_TYPE = ty.source_type_code
and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
and fd.FIELD_COLUMN_NAME is NOT NULL;
select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty,
ams_list_src_type_assocs ats
where hd.list_header_id = p_list_header_id
and hd.LIST_SOURCE_TYPE = ty.source_type_code
and ty.list_source_type_id = ats.master_source_type_id
and ats.sub_source_type_id = fd.LIST_SOURCE_TYPE_ID
and fd.FIELD_COLUMN_NAME is NOT NULL;
SELECT min(master_child.field_column_name1) ,count(master_child.field_column_name) from
(
SELECT d.field_column_name field_column_name1,d.field_column_name
FROM ams_list_src_types a,
ams_list_headers_all b,
ams_list_src_fields d
WHERE a.source_type_code = b.list_source_type
and b.list_header_id = p_list_header_id
and d.list_source_type_id = a.list_source_type_id
and d.USED_IN_LIST_ENTRIES = 'Y'
union all
SELECT d.field_column_name field_column_name1,d.field_column_name
FROM ams_list_src_types a,
ams_list_headers_all b,
ams_list_src_fields d,
ams_list_src_type_assocs e
WHERE a.source_type_code = b.list_source_type
and b.list_header_id = p_list_header_id
and e.master_source_type_id = a.list_source_type_id
and d.list_source_type_id = e.sub_source_type_id
and d.USED_IN_LIST_ENTRIES = 'Y'
) master_child
GROUP BY master_child.field_column_name
having COUNT(master_child.field_column_name) > 1;
SELECT d.source_column_name, d.field_column_name , d.de_list_source_type_code stc
FROM ams_list_src_types a,
ams_list_headers_all b,
ams_list_src_fields d
WHERE a.source_type_code = b.list_source_type
and b.list_header_id = g_list_header_id
and d.list_source_type_id = a.list_source_type_id
and d.field_column_name = p_col_name
and d.USED_IN_LIST_ENTRIES = 'Y'
union all
SELECT d.source_column_name, d.field_column_name, d.de_list_source_type_code stc
FROM ams_list_src_types a,
ams_list_headers_all b,
ams_list_src_fields d,
ams_list_src_type_assocs e
WHERE a.source_type_code = b.list_source_type
and b.list_header_id = g_list_header_id
and e.master_source_type_id = a.list_source_type_id
and d.list_source_type_id = e.sub_source_type_id
and d.field_column_name = p_col_name
and d.USED_IN_LIST_ENTRIES = 'Y';
SELECT
LIST_HEADER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LIST_USED_BY_ID,
ARC_LIST_USED_BY,
LIST_TYPE,
STATUS_CODE,
STATUS_DATE,
GENERATION_TYPE,
OWNER_USER_ID,
ROW_SELECTION_TYPE,
NO_OF_ROWS_MAX_REQUESTED
FROM AMS_LIST_HEADERS_ALL
WHERE LIST_HEADER_ID = P_LIST_HEADER_ID;
SELECT
LIST_SELECT_ACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ORDER_NUMBER,
LIST_ACTION_TYPE,
INCL_OBJECT_NAME,
ARC_INCL_OBJECT_FROM,
RANK,
NO_OF_ROWS_AVAILABLE,
NO_OF_ROWS_REQUESTED,
NO_OF_ROWS_USED,
DISTRIBUTION_PCT,
ARC_ACTION_USED_BY,
ACTION_USED_BY_ID,
INCL_CONTROL_GROUP,
NO_OF_ROWS_TARGETED,
NO_OF_ROWS_DUPLICATES,
RUNNING_TOTAL,
DELTA
FROM AMS_LIST_SELECT_ACTIONS
WHERE ACTION_USED_BY_ID = P_LIST_HEADER_ID
AND ARC_ACTION_USED_BY = P_ACTION;
l_main_random_nth_row_select number;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = g_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = p_list_header_id;
/*delete from ams_act_logs
where arc_act_log_used_by = 'LIST'
and act_log_used_by_id = p_list_header_id ;*/
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = g_list_header_id;
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');
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
ctrl_status_code = 'DRAFT',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
no_of_rows_in_ctrl_group = null
WHERE list_header_id = g_list_header_id;
write_to_act_log(' List is generated in UPDATE mode', 'LIST', g_list_header_id,'HIGH');
update ams_list_entries
set newly_updated_flag = 'N',
enabled_flag = 'Y'
where list_header_id = l_listheader_rec.list_header_id;
Dynamic procedure will update the list from the remote instance in
case of remote list
*********************************************************************/
/* write_to_act_log(p_msg_data => 'Updating the list in remote instance. ',
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'LOW');
'UPDATE';
write_to_act_log(p_msg_data => 'List generated in UPDATE mode in remote instance. ' ,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'LOW');
update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
write_to_act_log('List is generated in UPDATE mode in local instance.', 'LIST', g_list_header_id,'HIGH');
l_error_position := '<- set enabled flag for gen type UPDATE ';
update ams_list_entries
set enabled_flag = 'N'
where newly_updated_flag = 'N'
and list_header_id = l_listheader_rec.list_header_id;
write_to_act_log(sql%rowcount||' entries disabled when generating list in update mode','LIST',g_list_header_id,'HIGH');
'UPDATE';
UPDATE ams_list_entries a
SET a.enabled_flag = 'N',
a.marked_as_duplicate_flag = 'Y'
WHERE a.list_header_id = p_list_header_id
and a.enabled_flag = 'Y'
AND a.rowid > (SELECT min(b.rowid)
from ams_list_entries b
where b.list_header_id = p_list_header_id
and b.party_id = a.party_id
and b.enabled_flag = 'Y'
);
/* UPDATE ams_list_entries a
SET a.enabled_flag = 'N',
a.marked_as_duplicate_flag = 'Y'
WHERE a.list_header_id = p_list_header_id
and a.enabled_flag = 'Y'
-- AND a.rowid > (SELECT min(b.rowid)
AND a.rank > (SELECT min(b.rank)
from ams_list_entries b
where b.list_header_id = p_list_header_id
and b.party_id = a.party_id
and b.enabled_flag = 'Y'
);*/
fetch g_initial_count into g_no_of_rows_ini_selected;
g_msg_tbl_opt.delete;
write_to_act_log('Deleting the existing ist header record deleted in remote instance.', 'LIST', p_list_header_id,'LOW');
write_to_act_log('Passing list header details to the remote procedure, to insert it there.', 'LIST', p_list_header_id,'HIGH');
ams_remote_listgen_pkg.remote_insert_list_headers'||'@'||g_database_link||'(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)'||';'||
c10_rec.last_update_date,
c10_rec.last_updated_by,
c10_rec.creation_date,
c10_rec.created_by,
c10_rec.last_update_login,
c10_rec.list_used_by_id,
c10_rec.arc_list_used_by,
c10_rec.list_type,
c10_rec.status_code,
c10_rec.status_date,
c10_rec.generation_type,
c10_rec.owner_user_id,
c10_rec.row_selection_type,
c10_rec.no_of_rows_max_requested,
out x_msg_count,
out x_msg_data,
out x_return_status;
write_to_act_log('List header information updated.', 'LIST', g_list_header_id,'LOW');
write_to_act_log('Updating the list selections in the remote instance.', 'LIST', p_list_header_id,'HIGH');
write_to_act_log('Passing the selecions values to remote_insert_list_sel_actions proceudure, to insert it there.', 'LIST', p_list_header_id,'LOW');
ams_remote_listgen_pkg.remote_insert_list_sel_actions'||'@'||g_database_link||'(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25)'||';'||
c11_rec.list_select_action_id,
c11_rec.last_update_date,
c11_rec.last_updated_by,
c11_rec.creation_date,
c11_rec.created_by,
c11_rec.last_update_login,
c11_rec.order_number,
c11_rec.list_action_type,
c11_rec.incl_object_name,
c11_rec.arc_incl_object_from,
c11_rec.rank,
c11_rec.no_of_rows_available,
c11_rec.no_of_rows_requested,
c11_rec.no_of_rows_used,
c11_rec.distribution_pct,
c11_rec.arc_action_used_by,
c11_rec.action_used_by_id,
c11_rec.incl_control_group,
c11_rec.no_of_rows_targeted,
c11_rec.no_of_rows_duplicates,
c11_rec.running_total,
c11_rec.delta,
out x_msg_count,
out x_msg_data,
out x_return_status;
write_to_act_log('Error updating list selections in remote instance.', 'LIST', g_list_header_id,'HIGH');
write_to_act_log('List selections updated in remote instance.', 'LIST', g_list_header_id,'LOW');
execute immediate 'begin Update ams_list_select_actions a1 set no_of_rows_requested =
(select no_of_rows_requested from ams_list_select_actions'||'@'||g_database_link||
' b1 where b1.list_select_action_id = a1.list_select_action_id)
where action_used_by_id = :1 and arc_action_used_by = :2; end; ' using p_list_header_id, l_action;
write_to_act_log('No_of_rows_requested in list header table updated with values from remote instance.', 'LIST', p_list_header_id,'LOW');
l_error_position := '<- update list dets ->';
write_to_act_log('Calling update_list_dets to update list header and selections info.'||x_msg_data , 'LIST', g_list_header_id,'HIGH');
Update_List_Dets(p_list_header_id,x_return_status);
write_to_act_log('Error in updating list header/selections info', 'LIST', g_list_header_id,'HIGH');
write_to_act_log('List header and selections info updated.'||x_msg_data , 'LIST', g_list_header_id,'LOW');
write_to_act_log('Error after calling update_list_dets procedure', 'LIST', p_list_header_id,'HIGH');
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = p_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
last_update_date = sysdate,
status_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = p_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'DRAFT',
user_status_id = 300,
last_update_date = sysdate,
status_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = p_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
last_update_date = sysdate,
status_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = p_list_header_id;
SELECT list_source_type_id
FROM ams_list_src_types a
WHERE a.source_type_code = p_master_type
AND a.master_source_type_flag = 'Y';
select ','||''''||source_type_code||''''
from ams_list_src_types a,
ams_list_src_type_assocs b
where b.master_source_type_id = p_master_type_id
and b.sub_source_type_id = a.list_source_type_id;
l_select_string varchar2(2000) := 'SELECT ' ||''''|| p_master_type || '''';
l_select_string := l_select_string || l_source_type_code;
p_sql_string := l_select_string ||','|| substrb(p_sql_string,instr(upper(p_sql_string),'SELECT',1)+6);
select 'x'
from ams_list_headers_vl
where list_name = p_list_name ;
is select decode(import_type,'B2C','PERSON_LIST','ORGANIZATION_CONTACT_LIST'),
name
from ams_imp_list_headers_vl
where import_list_header_id = p_imp_list_header_id ;
select l_import_list_name|| ' -:'|| to_char(sysdate,'DD-MON-YY HH:MM:SS')
into l_import_list_name
from ams_imp_list_headers_vl
where import_list_header_id = p_imp_list_header_id ;
l_list_select_action_id number;
SELECT a.list_act_type, a.list_used_by,
a.list_action_type, a.order_number,
a.list_header_id ,
-- a.act_list_header_id,
c.list_select_action_id act_list_header_id,
c.distribution_pct distribution_pct,
c.arc_incl_object_from arc_incl_object_from
FROM ams_act_lists a ,ams_act_lists b, ams_list_select_actions c
WHERE a.list_used_by_id = b.list_used_by_id
AND a.list_used_by = b.list_used_by
AND b.list_header_id = p_action_used_by_id
AND b.list_act_type = 'TARGET'
and a.list_act_type <> 'TARGET'
and b.list_header_id = c.action_used_by_id
and a.order_number = c.order_number
ORDER by c.order_number;
l_update_sql VARCHAR2(32767);
select generation_type
from ams_list_headers_all
where list_header_id = l_list_header_id;
cursor c_query(l_query_id number) is select
nvl(PARAMETERIZED_FLAG ,'N')
from ams_list_queries_all
where list_query_id = l_query_id ;
select PARAMETER_ORDER, PARAMETER_VALUE,parameter_name
from ams_list_queries_param
where list_query_id = l_query_id
order by PARAMETER_ORDER;
l_remote_update_sql VARCHAR2(32767);
l_list_select_id num_tbl_type;
select 'Y'
from ams_list_headers_vl a ,
ams_query_template_all b
where a.list_header_id = g_list_header_id
and b.template_type = 'PARAMETERIZED'
and a.query_template_id = b.template_id ;
SELECT list_select_action_id,order_number
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_used_by_id
AND arc_action_used_by = p_action_used_by
AND order_number = l_sorted;
select 'Y' from ams_campaign_schedules_b b, ams_list_headers_all h
where h.list_header_id = g_list_header_id
and h.LIST_USED_BY_ID = b.schedule_id
and b.orig_csch_id is not null;
SELECT a.list_header_id
FROM ams_act_lists a ,ams_act_lists b
WHERE a.list_used_by_id = b.list_used_by_id
AND a.list_used_by = b.list_used_by
AND b.list_header_id = g_list_header_id
AND b.list_act_type = 'TARGET'
and a.list_act_type <> 'TARGET'
and a.list_action_type = 'INCLUDE'
ORDER by a.order_number;
select count(1)
from ams_list_queries_param
where list_query_id = p_incl_object_id;
' select list_entry_source_system_id ' ||
' from ams_list_entries ' ||
' where list_header_id = ' || p_action_used_by_id ;
l_const_sql1 := ' and LIST_SELECT_ACTION_ID = ';
write_to_act_log('Selection included in target group is of type '||l_action_dets_rec.list_act_type||' , action type is '||l_action_dets_rec.list_action_type, 'LIST', g_list_header_id,'HIGH');
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
ctrl_status_code = 'DRAFT',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
no_of_rows_in_ctrl_group = null
WHERE list_header_id = g_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
WHERE list_header_id = g_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = g_list_header_id;
l_list_select_id(l_include_count) := l_action_dets_rec.act_list_header_id;
write_to_act_log('Action on this selection completed. ', 'LIST', g_list_header_id,'LOW');
write_to_act_log('No of inclusions in the selection '|| l_include_count, 'LIST', g_list_header_id,'LOW');
write_to_act_log('Sorting based on rank for the selection. ', 'LIST', g_list_header_id,'LOW');
WRITE_TO_ACT_LOG('List selection id is '||l_l_sele_action_id||' for order number '||l_sorted, 'LIST', g_list_header_id,'LOW');
write_to_act_log('% Requested for this selection is '||l_dist_pct_tbl(l_sorted),'LIST',g_list_header_id,'LOW');
l_temp_sql := 'SELECT count(1) '||substr(l_temp_sql,instr(l_temp_sql, ' FROM '));
-- get_count(l_list_select_id(l_sorted),l_sorted,'OTHERS',l_temp_sql||l_std_sql_tbl(l_sorted)||l_const_sql||l_const_sql1||l_l_sele_action_id||')' );
get_count(l_list_select_id(l_sorted),l_sorted,'OTHERS',l_temp_sql||l_std_sql_tbl(l_sorted)||l_const_sql||')' );
get_count(l_list_select_id(l_sorted),l_sorted,'LIST',null);
get_count(l_list_select_id(l_sorted),l_sorted,'IMPH',null);
write_to_act_log('No of rows requested from the selection is '||g_reqd_num_tbl(l_sorted),'LIST',g_list_header_id,'LOW');
a segment , sql or workbook is in the list selection then it will be generated in the remote
instance through a dynamic procedure call */
l_const_sql1 := ' ';
update ams_list_headers_all
set last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
where list_header_id = g_list_header_id;
update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
update ams_list_headers_all
set last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
where list_header_id = g_list_header_id;
update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
select no_of_rows_in_list
from ams_list_headers_all
where list_header_id = c_list_header_id ;
select nvl(stypes.remote_flag,'N') ,database_link
from ams_list_src_types stypes, ams_list_headers_all list
where list.list_source_type = stypes.source_type_code
and list_header_id = p_list_header_id;
l_list_selection varchar2(1);
l_onlylist_selection varchar2(1);
cursor c_list_selection is
select 'Y' from ams_list_select_actions
where action_used_by_id = p_list_header_id
and arc_action_used_by = 'LIST'
and arc_incl_object_from in ('CELL','DIWB','SQL');
cursor c_only_list_selection is
select 'Y' from ams_list_select_actions act, ams_list_headers_all head
where act.action_used_by_id = p_list_header_id
and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
and act.INCL_OBJECT_ID = head.list_header_id
and head.status_code = 'AVAILABLE'
and head.MIGRATION_DATE is null;
SELECT list_rule_id
FROM ams_list_rule_usages
WHERE list_header_id = g_list_header_id;*/
SELECT us.list_rule_id
FROM ams_list_rule_usages us, ams_list_rules_all rules
WHERE us.list_header_id = g_list_header_id
AND us.list_rule_id = rules.list_rule_id
AND rules.list_source_type = l_listheader_rec.list_source_type
AND rules.list_rule_type = 'TARGET';
select STATUS_CODE from ams_list_headers_all
where list_header_id = p_list_header_id;
select count(1) from ams_list_entries
where list_header_id = p_list_header_id;
select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
where hd.list_header_id = p_list_header_id
and hd.LIST_SOURCE_TYPE = ty.source_type_code
and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
and fd.FIELD_COLUMN_NAME is NOT NULL;
select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty,
ams_list_src_type_assocs ats
where hd.list_header_id = p_list_header_id
and hd.LIST_SOURCE_TYPE = ty.source_type_code
and ty.list_source_type_id = ats.master_source_type_id
and ats.sub_source_type_id = fd.LIST_SOURCE_TYPE_ID
and fd.FIELD_COLUMN_NAME is NOT NULL;
select nvl(no_of_rows_in_list ,0)
from ams_list_headers_all
where list_header_id = g_list_header_id;
SELECT a.enabled_flag
FROM ams_list_src_types a,
ams_list_headers_all b
WHERE a.source_type_code = b.list_source_type
AND b.list_header_id = c_list_header_id;
select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty
where hd.list_header_id = p_list_header_id
and hd.LIST_SOURCE_TYPE = ty.source_type_code
and ty.list_source_type_id = fd.LIST_SOURCE_TYPE_ID
and fd.tca_column_id is NOT NULL;
select 'Y' from ams_list_src_fields fd, ams_list_headers_all hd, ams_list_src_types ty,
ams_list_src_type_assocs ats
where hd.list_header_id = p_list_header_id
and hd.LIST_SOURCE_TYPE = ty.source_type_code
and ty.list_source_type_id = ats.master_source_type_id
and ats.sub_source_type_id = fd.LIST_SOURCE_TYPE_ID
and fd.tca_column_id is NOT NULL;
SELECT min(master_child.field_column_name1) ,count(master_child.field_column_name) from
(
SELECT d.field_column_name field_column_name1,d.field_column_name
FROM ams_list_src_types a,
ams_list_headers_all b,
ams_list_src_fields d
WHERE a.source_type_code = b.list_source_type
and b.list_header_id = p_list_header_id
and d.list_source_type_id = a.list_source_type_id
and d.USED_IN_LIST_ENTRIES = 'Y'
union all
SELECT d.field_column_name field_column_name1,d.field_column_name
FROM ams_list_src_types a,
ams_list_headers_all b,
ams_list_src_fields d,
ams_list_src_type_assocs e
WHERE a.source_type_code = b.list_source_type
and b.list_header_id = p_list_header_id
and e.master_source_type_id = a.list_source_type_id
and d.list_source_type_id = e.sub_source_type_id
and d.USED_IN_LIST_ENTRIES = 'Y'
) master_child
GROUP BY master_child.field_column_name
having COUNT(master_child.field_column_name) > 1;
SELECT d.source_column_name, d.field_column_name , d.de_list_source_type_code stc
FROM ams_list_src_types a,
ams_list_headers_all b,
ams_list_src_fields d
WHERE a.source_type_code = b.list_source_type
and b.list_header_id = g_list_header_id
and d.list_source_type_id = a.list_source_type_id
and d.field_column_name = p_col_name
and d.USED_IN_LIST_ENTRIES = 'Y'
union all
SELECT d.source_column_name, d.field_column_name, d.de_list_source_type_code stc
FROM ams_list_src_types a,
ams_list_headers_all b,
ams_list_src_fields d,
ams_list_src_type_assocs e
WHERE a.source_type_code = b.list_source_type
and b.list_header_id = g_list_header_id
and e.master_source_type_id = a.list_source_type_id
and d.list_source_type_id = e.sub_source_type_id
and d.field_column_name = p_col_name
and d.USED_IN_LIST_ENTRIES = 'Y';
select nvl(apply_suppression_flag,'N')
from ams_list_headers_all
where list_header_id = p_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
WHERE list_header_id = p_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
WHERE list_header_id = p_list_header_id;
/*DELETE FROM ams_act_logs
WHERE arc_act_log_used_by = 'LIST'
AND act_log_used_by_id = p_list_header_id ;*/
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
WHERE list_header_id = g_list_header_id;
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');
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
ctrl_status_code = 'DRAFT',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = g_list_header_id;
write_to_act_log('Target group is generated in UPDATE mode', 'LIST', g_list_header_id,'HIGH');
update ams_list_entries
set newly_updated_flag = 'N' , enabled_flag = 'Y'
where list_header_id = l_listheader_rec.list_header_id;
Dynamic procedure will update the list from the remote instance in
case of remote list
*********************************************************************/
/* if g_remote_list = 'Y' then
write_to_act_log(p_msg_data => 'Updating the target group in remote instance. ' ,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'LOW');
'UPDATE';
write_to_act_log(p_msg_data => 'Target group updated in remote instance. ' ,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'LOW');
update_remote_list_header(g_list_header_id,x_return_status,x_msg_count,x_msg_data);
write_to_act_log('Target group is generated in UPDATE mode in local instance.', 'LIST', g_list_header_id,'HIGH');
UPDATE ams_list_entries a
SET a.enabled_flag = 'N',
a.marked_as_duplicate_flag = 'Y'
WHERE a.list_header_id = p_list_header_id
AND a.enabled_flag = 'Y'
AND a.rowid > (SELECT min(b.rowid)
from ams_list_entries b
where b.list_header_id = p_list_header_id
and b.party_id = a.party_id
and b.enabled_flag = 'Y'
);
fetch g_initial_count into g_no_of_rows_ini_selected;
g_msg_tbl_opt.delete;
g_msg_tbl_opt.delete;
write_to_act_log(p_msg_data => 'Deleting entries in the remote instance. Calling remote procedure with process type as DELETE_LIST_ENTRIES.' ,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level=>'LOW');
'DELETE_LIST_ENTRIES';
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
NO_OF_ROWS_ACTIVE = null,
NO_OF_ROWS_IN_LIST = null,
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
WHERE list_header_id = p_list_header_id;
UPDATE ams_list_headers_all
SET status_code = 'GENERATING',
user_status_id = 302
WHERE list_header_id = p_list_header_id;
/* UPDATE ams_list_entries set
source_code = l_source_code ,
arc_list_used_by_source = p_list_used_by ,
source_code_for_id = p_list_used_by_id
where list_header_id = g_list_header_id ;
AMS_LISTGENERATION_PKG.Update_List_Dets(g_list_header_id ,x_return_status ) ;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
status_date = sysdate,
last_update_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
WHERE list_header_id = p_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
last_update_date = sysdate,
status_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
WHERE list_header_id = p_list_header_id;
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
last_update_date = sysdate,
status_date = sysdate,
main_gen_end_time = sysdate,
ctrl_status_code = 'DRAFT',
no_of_rows_in_ctrl_group = null
WHERE list_header_id = p_list_header_id;
IS SELECT list_rule_id
FROM ams_list_rule_usages
WHERE list_header_id = my_list_header_id
ORDER BY priority;
SELECT field_table_name,
field_column_name,
substring_length,
word_replacement_code
FROM ams_list_rule_fields
WHERE list_rule_id = my_list_rule_id;
SELECT last_deduped_by_user_id
FROM ams_list_headers_all
WHERE list_header_id = my_list_header_id;
SELECT DISTINCT dedupe_key, COUNT (dedupe_key)
FROM ams_list_entries
WHERE list_header_id = my_list_header_id
GROUP BY dedupe_key;
SELECT min(b.rank) FROM ams_list_select_actions b
WHERE b.action_used_by_id = p_list_header_id
and b.arc_action_used_by = 'LIST'
GROUP BY b.rank;
l_sql_stmt1 := 'update ams_list_entries set dedupe_key = ';
/* UPDATE ams_list_entries
SET dedupe_key = NULL
WHERE list_header_id = p_list_header_id; */
'UPDATE ams_list_entries SET dedupe_key = NULL WHERE list_header_id = '||to_char(p_list_header_id),
l_null,
l_null,
OUT l_total_dup_recs,
'DEDUPE1';
SELECT COUNT (rank)
INTO l_rank_count
FROM ams_list_select_actions
WHERE action_used_by_id = p_list_header_id
and arc_action_used_by = 'LIST';
UPDATE ams_list_entries
SET dedupe_key = NULL
WHERE list_header_id = p_list_header_id
AND marked_as_duplicate_flag IS NULL;
UPDATE ams_list_headers_all
SET last_deduped_by_user_id = FND_GLOBAL.User_Id
,last_dedupe_date = SYSDATE
,no_of_rows_duplicates = l_total_dup_recs
WHERE list_header_id = p_list_header_id;
/* savio removing potential multiple update statement */
/*
-- UPDATE ams_list_headers_all
-- SET no_of_rows_duplicates = l_total_dup_recs
-- WHERE list_header_id = p_list_header_id;
write_to_act_log('Updated list header table with the userid and the time','LIST',p_list_header_id, 'LOW');
select head.list_header_id
from ams_list_headers_all head, ams_list_src_types stypes
where head.status_code = 'AVAILABLE'
and head.MIGRATION_DATE is NULL
and head.list_source_type = stypes.source_type_code
and stypes.remote_flag = 'Y';
l_insert_sql varchar2(32767);
select database_link
from ams_list_src_types stypes, ams_list_headers_all list
where list.list_source_type = stypes.source_type_code
and list_header_id = p_list_header_id;
Ams_Utility_Pvt.Write_Conc_log('Start Delete list entries from local instance : ');
delete from ams_list_entries
where list_header_id = p_list_header_id;
Ams_Utility_Pvt.Write_Conc_log('End Delete list entries from local instance : ');
l_insert_sql := 'insert into ams_list_entries
(list_header_id ,
list_entry_id,
object_version_number,
source_code ,
source_code_for_id ,
arc_list_used_by_source ,
arc_list_select_action_from ,
pin_code ,
view_application_id ,
manually_entered_flag ,
marked_as_random_flag ,
marked_as_duplicate_flag ,
part_of_control_group_flag ,
exclude_in_triggered_list_flag ,
enabled_flag ,
LIST_SELECT_ACTION_FROM_NAME,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
list_entry_source_system_id,
list_entry_source_system_type,
list_select_action_id,
SUFFIX,
FIRST_NAME,
LAST_NAME,
CUSTOMER_NAME,
TITLE,
ADDRESS_LINE1,
ADDRESS_LINE2,
CITY,
STATE,
ZIPCODE,
COUNTRY,
FAX,
PHONE,
EMAIL_ADDRESS,
CUSTOMER_ID ,
LIST_SOURCE ,
PARTY_ID ,
PARENT_PARTY_ID ,
IMP_SOURCE_LINE_ID ,
COL1,
COL2,
COL3,
COL4,
COL5,
COL6,
COL7,
COL8,
COL9,
COL10,
COL11,
COL12,
COL13,
COL14,
COL15,
COL16,
COL17,
COL18,
COL19,
COL20,
COL21,
COL22,
COL23,
COL24,
COL25,
COL26,
COL27,
COL28,
COL29,
COL30,
COL31,
COL32,
COL33,
COL34,
COL35,
COL36,
COL37,
COL38,
COL39,
COL40,
COL41,
COL42,
COL43,
COL44,
COL45,
COL46,
COL47,
COL48,
COL49,
COL50,
COL51,
COL52,
COL53,
COL54,
COL55,
COL56,
COL57,
COL58,
COL59,
COL60,
COL61,
COL62,
COL63,
COL64,
COL65,
COL66,
COL67,
COL68,
COL69,
COL70,
COL71,
COL72,
COL73,
COL74,
COL75,
COL76,
COL77,
COL78,
COL79,
COL80,
COL81,
COL82,
COL83,
COL84,
COL85,
COL86,
COL87,
COL88,
COL89,
COL90,
COL91,
COL92,
COL93,
COL94,
COL95,
COL96,
COL97,
COL98,
COL99,
COL100,
COL101,
COL102,
COL103,
COL104,
COL105,
COL106,
COL107,
COL108,
COL109,
COL110,
COL111,
COL112,
COL113,
COL114,
COL115,
COL116,
COL117,
COL118,
COL119,
COL120,
COL121,
COL122,
COL123,
COL124,
COL125,
COL126,
COL127,
COL128,
COL129,
COL130,
COL131,
COL132,
COL133,
COL134,
COL135,
COL136,
COL137,
COL138,
COL139,
COL140,
COL141,
COL142,
COL143,
COL144,
COL145,
COL146,
COL147,
COL148,
COL149,
COL150,
COL151,
COL152,
COL153,
COL154,
COL155,
COL156,
COL157,
COL158,
COL159,
COL160,
COL161,
COL162,
COL163,
COL164,
COL165,
COL166,
COL167,
COL168,
COL169,
COL170,
COL171,
COL172,
COL173,
COL174,
COL175,
COL176,
COL177,
COL178,
COL179,
COL180,
COL181,
COL182,
COL183,
COL184,
COL185,
COL186,
COL187,
COL188,
COL189,
COL190,
COL191,
COL192,
COL193,
COL194,
COL195,
COL196,
COL197,
COL198,
COL199,
COL200,
COL201,
COL202,
COL203,
COL204,
COL205,
COL206,
COL207,
COL208,
COL209,
COL210,
COL211,
COL212,
COL213,
COL214,
COL215,
COL216,
COL217,
COL218,
COL219,
COL220,
COL221,
COL222,
COL223,
COL224,
COL225,
COL226,
COL227,
COL228,
COL229,
COL230,
COL231,
COL232,
COL233,
COL234,
COL235,
COL236,
COL237,
COL238,
COL239,
COL240,
COL241,
COL242,
COL243,
COL244,
COL245,
COL246,
COL247,
COL248,
COL249,
COL250 ,
COL251 ,
COL252 ,
COL253 ,
COL254 ,
COL256 ,
COL255 ,
COL257 ,
COL258 ,
COL259 ,
COL260 ,
COL261 ,
COL262 ,
COL263 ,
COL264 ,
COL265 ,
COL266 ,
COL267 ,
COL268 ,
COL269 ,
COL270 ,
COL271 ,
COL272 ,
COL273 ,
COL274 ,
COL275 ,
COL276 ,
COL277 ,
COL278 ,
COL279 ,
COL280 ,
COL281 ,
COL282 ,
COL283 ,
COL284 ,
COL285 ,
COL286 ,
COL287 ,
COL288 ,
COL289 ,
COL290 ,
COL291 ,
COL292 ,
COL293 ,
COL294 ,
COL295 ,
COL296 ,
COL297 ,
COL298 ,
COL299 ,
COL300 ,
GROUP_CODE,
NEWLY_UPDATED_FLAG,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
NOTES,
VEHICLE_RESPONSE_CODE,
SALES_AGENT_EMAIL_ADDRESS,
RESOURCE_ID,
LOCATION_ID,
CONTACT_POINT_ID,
ORIG_SYSTEM_REFERENCE,
MARKED_AS_FATIGUED_FLAG,
MARKED_AS_SUPPRESSED_FLAG,
REMOTE_LIST_ENTRY_ID,
-- ERROR_TEXT,
-- ERROR_FLAG,
LAST_CONTACTED_DATE,
CUSTOM_COLUMN1,
CUSTOM_COLUMN2,
CUSTOM_COLUMN3,
CUSTOM_COLUMN4,
CUSTOM_COLUMN5,
CUSTOM_COLUMN6,
CUSTOM_COLUMN7,
CUSTOM_COLUMN8,
CUSTOM_COLUMN9,
CUSTOM_COLUMN10,
CUSTOM_COLUMN11,
CUSTOM_COLUMN12,
CUSTOM_COLUMN13,
CUSTOM_COLUMN14,
CUSTOM_COLUMN15,
CUSTOM_COLUMN16,
CUSTOM_COLUMN17,
CUSTOM_COLUMN18,
CUSTOM_COLUMN19,
CUSTOM_COLUMN20,
CUSTOM_COLUMN21,
CUSTOM_COLUMN22,
CUSTOM_COLUMN23,
CUSTOM_COLUMN24,
CUSTOM_COLUMN25,
RANK
)
SELECT
list_header_id,
ams_list_entries_s.nextval,
object_version_number,
source_code ,
source_code_for_id ,
arc_list_used_by_source ,
arc_list_select_action_from ,
pin_code ,
view_application_id ,
manually_entered_flag ,
marked_as_random_flag ,
marked_as_duplicate_flag ,
part_of_control_group_flag ,
exclude_in_triggered_list_flag ,
enabled_flag ,
LIST_SELECT_ACTION_FROM_NAME,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
list_entry_source_system_id,
list_entry_source_system_type,
list_select_action_id,
SUFFIX,
FIRST_NAME,
LAST_NAME,
CUSTOMER_NAME,
TITLE,
ADDRESS_LINE1,
ADDRESS_LINE2,
CITY,
STATE,
ZIPCODE,
COUNTRY,
FAX,
PHONE,
EMAIL_ADDRESS,
CUSTOMER_ID ,
LIST_SOURCE ,
null, -- PARTY_ID ,
null, -- PARENT_PARTY_ID ,
IMP_SOURCE_LINE_ID ,
COL1,
COL2,
COL3,
COL4,
COL5,
COL6,
COL7,
COL8,
COL9,
COL10,
COL11,
COL12,
COL13,
COL14,
COL15,
COL16,
COL17,
COL18,
COL19,
COL20,
COL21,
COL22,
COL23,
COL24,
COL25,
COL26,
COL27,
COL28,
COL29,
COL30,
COL31,
COL32,
COL33,
COL34,
COL35,
COL36,
COL37,
COL38,
COL39,
COL40,
COL41,
COL42,
COL43,
COL44,
COL45,
COL46,
COL47,
COL48,
COL49,
COL50,
COL51,
COL52,
COL53,
COL54,
COL55,
COL56,
COL57,
COL58,
COL59,
COL60,
COL61,
COL62,
COL63,
COL64,
COL65,
COL66,
COL67,
COL68,
COL69,
COL70,
COL71,
COL72,
COL73,
COL74,
COL75,
COL76,
COL77,
COL78,
COL79,
COL80,
COL81,
COL82,
COL83,
COL84,
COL85,
COL86,
COL87,
COL88,
COL89,
COL90,
COL91,
COL92,
COL93,
COL94,
COL95,
COL96,
COL97,
COL98,
COL99,
COL100,
COL101,
COL102,
COL103,
COL104,
COL105,
COL106,
COL107,
COL108,
COL109,
COL110,
COL111,
COL112,
COL113,
COL114,
COL115,
COL116,
COL117,
COL118,
COL119,
COL120,
COL121,
COL122,
COL123,
COL124,
COL125,
COL126,
COL127,
COL128,
COL129,
COL130,
COL131,
COL132,
COL133,
COL134,
COL135,
COL136,
COL137,
COL138,
COL139,
COL140,
COL141,
COL142,
COL143,
COL144,
COL145,
COL146,
COL147,
COL148,
COL149,
COL150,
COL151,
COL152,
COL153,
COL154,
COL155,
COL156,
COL157,
COL158,
COL159,
COL160,
COL161,
COL162,
COL163,
COL164,
COL165,
COL166,
COL167,
COL168,
COL169,
COL170,
COL171,
COL172,
COL173,
COL174,
COL175,
COL176,
COL177,
COL178,
COL179,
COL180,
COL181,
COL182,
COL183,
COL184,
COL185,
COL186,
COL187,
COL188,
COL189,
COL190,
COL191,
COL192,
COL193,
COL194,
COL195,
COL196,
COL197,
COL198,
COL199,
COL200,
COL201,
COL202,
COL203,
COL204,
COL205,
COL206,
COL207,
COL208,
COL209,
COL210,
COL211,
COL212,
COL213,
COL214,
COL215,
COL216,
COL217,
COL218,
COL219,
COL220,
COL221,
COL222,
COL223,
COL224,
COL225,
COL226,
COL227,
COL228,
COL229,
COL230,
COL231,
COL232,
COL233,
COL234,
COL235,
COL236,
COL237,
COL238,
COL239,
COL240,
COL241,
COL242,
COL243,
COL244,
COL245,
COL246,
COL247,
COL248,
COL249,
COL250 ,
COL251 ,
COL252 ,
COL253 ,
COL254 ,
COL256 ,
COL255 ,
COL257 ,
COL258 ,
COL259 ,
COL260 ,
COL261 ,
COL262 ,
COL263 ,
COL264 ,
COL265 ,
COL266 ,
COL267 ,
COL268 ,
COL269 ,
COL270 ,
COL271 ,
COL272 ,
COL273 ,
COL274 ,
COL275 ,
COL276 ,
COL277 ,
COL278 ,
COL279 ,
COL280 ,
COL281 ,
COL282 ,
COL283 ,
COL284 ,
COL285 ,
COL286 ,
COL287 ,
COL288 ,
COL289 ,
COL290 ,
COL291 ,
COL292 ,
COL293 ,
COL294 ,
COL295 ,
COL296 ,
COL297 ,
COL298 ,
COL299 ,
COL300 ,
GROUP_CODE,
NEWLY_UPDATED_FLAG,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
NOTES,
VEHICLE_RESPONSE_CODE,
SALES_AGENT_EMAIL_ADDRESS,
RESOURCE_ID,
LOCATION_ID,
CONTACT_POINT_ID,
ORIG_SYSTEM_REFERENCE,
MARKED_AS_FATIGUED_FLAG,
MARKED_AS_SUPPRESSED_FLAG,
LIST_ENTRY_ID,
-- ERROR_TEXT,
-- ERROR_FLAG,
LAST_CONTACTED_DATE,
CUSTOM_COLUMN1,
CUSTOM_COLUMN2,
CUSTOM_COLUMN3,
CUSTOM_COLUMN4,
CUSTOM_COLUMN5,
CUSTOM_COLUMN6,
CUSTOM_COLUMN7,
CUSTOM_COLUMN8,
CUSTOM_COLUMN9,
CUSTOM_COLUMN10,
CUSTOM_COLUMN11,
CUSTOM_COLUMN12,
CUSTOM_COLUMN13,
CUSTOM_COLUMN14,
CUSTOM_COLUMN15,
CUSTOM_COLUMN16,
CUSTOM_COLUMN17,
CUSTOM_COLUMN18,
CUSTOM_COLUMN19,
CUSTOM_COLUMN20,
CUSTOM_COLUMN21,
CUSTOM_COLUMN22,
CUSTOM_COLUMN23,
CUSTOM_COLUMN24,
CUSTOM_COLUMN25,
RANK
from ams_list_entries@'||l_dblink||' '||
'where list_header_id = ' ||to_char(p_list_header_id);
l_no_of_chunks := ceil(length(l_insert_sql)/2000 );
write_to_act_log('Insert statement formed for migrating the list is..','LIST',p_list_header_id,'LOW');
Ams_Utility_Pvt.Write_Conc_log('l_insert_sql = '||substrb(l_insert_sql,(2000*i) - 1999,2000));
execute immediate l_insert_sql;
SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
WHERE system_status_type = 'AMS_LIST_STATUS' AND
system_status_code = 'AVAILABLE' and default_flag = 'Y';
UPDATE ams_list_headers_all
set status_code = 'AVAILABLE',
user_status_id = l_user_status_id,
migration_date = sysdate
where list_header_id = p_list_header_id;
Update_List_Dets(p_list_header_id,l_return_status);
Ams_Utility_Pvt.Write_Conc_log('After Update_List_Dets call : ');
UPDATE ams_list_headers_all
SET last_generation_success_flag = 'N',
status_code = 'FAILED',
user_status_id = 311,
last_update_date = sysdate,
status_date = sysdate,
main_gen_end_time = sysdate
WHERE list_header_id = p_list_header_id;
l_insert_sql varchar2(32767);
l_delete_sql varchar2(32767);
l_delete_sql := 'Delete from AMS_HZ_WORD_REPLACEMENTS@'||dblink;
l_insert_sql := 'Insert into AMS_HZ_WORD_REPLACEMENTS@'||dblink||' (
ORIGINAL_WORD,
REPLACEMENT_WORD,
TYPE,
COUNTRY_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
WORD_LIST_ID,
OBJECT_VERSION_NUMBER
)
SELECT
ORIGINAL_WORD,
REPLACEMENT_WORD,
TYPE,
COUNTRY_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
WORD_LIST_ID,
OBJECT_VERSION_NUMBER
FROM HZ_WORD_REPLACEMENTS ';
Ams_Utility_Pvt.Write_Conc_log('l_delete_sql = '||l_delete_sql);
execute immediate l_delete_sql;
Ams_Utility_Pvt.Write_Conc_log('l_insert_sql = '||l_insert_sql);
execute immediate l_insert_sql;
PROCEDURE UPDATE_FOR_TRAFFIC_COP( p_list_header_id in number ,
p_list_entry_id in t_number ) IS
l_return_status varchar2(1);
select sum(decode(enabled_flag,'Y',1,0)),
sum(decode(marked_as_fatigued_flag,'Y',1,0))
from ams_list_entries
where list_header_id = cur_p_list_header_id ;
p_msg_data => 'UPDATE_FOR_TRAFFIC_COP : Started ',
p_msg_type => 'DEBUG');
UPDATE ams_list_entries
SET ENABLED_FLAG = 'N',
MARKED_AS_FATIGUED_FLAG = 'Y'
WHERE list_entry_id = p_list_entry_id(i)
AND list_header_id = p_list_header_id ;
UPDATE ams_list_headers_all
SET NO_OF_ROWS_FATIGUED = l_fatigued_records,
NO_OF_ROWS_ACTIVE = l_no_of_rows_active
WHERE list_header_id = p_list_header_id ;
p_msg_data => 'UPDATE_FOR_TRAFFIC_COP : End ',
p_msg_type => 'DEBUG');
end UPDATE_FOR_TRAFFIC_COP;
PROCEDURE calc_selection_running_total
(p_action_used_by_id in number,
p_action_used_by in varchar2 ,-- DEFAULT 'LIST',
p_log_flag in varchar2 ,-- DEFAULT 'Y',
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_request_id number;
end calc_selection_running_total;
l_list_select_action_id number;
SELECT list_select_action_id
FROM ams_list_select_actions
WHERE action_used_by_id = p_action_used_by_id
AND arc_action_used_by = p_action_used_by
ORDER by order_number;
l_update_sql VARCHAR2(32767);
select generation_type
from ams_list_headers_all
where list_header_id = l_list_header_id;
l_selection_results t_number;
l_list_select_action t_number;
select list_select_action_id,order_number,running_total from ams_list_select_actions where
action_used_by_id = p_action_used_by_id and arc_action_used_by = 'LIST'
and list_action_type = 'INCLUDE' order by order_number;
select nvl(last_generation_success_flag,'N')
from ams_list_headers_all
where list_header_id = p_action_used_by_id;
UPDATE ams_list_select_actions
SET RUNNING_TOTAL = null, DELTA = null
WHERE action_used_by_id = p_action_used_by_id
AND arc_action_used_by = 'LIST';
' select list_entry_source_system_id ' ||
' from ams_list_entries ' ||
' where list_header_id = ' || p_action_used_by_id ;
FETCH c_action_dets INTO l_list_select_action_id;
l_tmp_action_rec.list_select_action_id := l_list_select_action_id;
write_to_act_log('Error. The action type of the first selection is NOT INCLUDE.', 'LIST', g_list_header_id,'HIGH');
Ams_Utility_Pvt.Write_Conc_log('calc_selection_running_total : Dynamic ');
p_action_rec.list_select_action_id,
p_action_rec.order_number,
p_action_rec.rank,
'N',--CHECK p_action_rec.incl_control_group,
OUT x_msg_data,
OUT x_msg_count,
in OUT x_return_status ,
OUT l_std_sql ,
OUT l_include_sql;
Ams_Utility_Pvt.Write_Conc_log('calc_selection_running_total : End Dynamic 001-> ');
Ams_Utility_Pvt.Write_Conc_log('calc_selection_running_total include: '||to_char(l_include_count));
l_list_select_action(l_include_count) := l_list_select_action_id;
Ams_Utility_Pvt.Write_Conc_log('calc_selection_running_total : End Dynamic ');
Ams_Utility_Pvt.Write_Conc_log('********calc_selection_running_total: FINAL SQL ************');
EXECUTE IMMEDIATE l_final_big_sql using out l_selection_results(l_sorted);
Ams_Utility_Pvt.Write_Conc_log('*******l_list_select_action(l_sorted) = '||l_list_select_action(l_sorted));
Ams_Utility_Pvt.Write_Conc_log('*******l_selection_results(l_sorted) = '||l_selection_results(l_sorted));
l_running_total := l_running_total + l_selection_results(l_sorted);
Update ams_list_select_actions set RUNNING_TOTAL = l_running_total
Where LIST_SELECT_ACTION_ID = l_list_select_action(l_sorted)
and arc_action_used_by = 'LIST';
Update ams_list_select_actions set delta = l_delta
Where LIST_SELECT_ACTION_ID = l_list_act_id;
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
l_list_entry_source_type varchar2(30);
is select decode(import_type,'B2C','PERSON_LIST','ORGANIZATION_CONTACT_LIST')
from ams_imp_list_headers_all
where import_list_header_id = p_incl_object_id;
select
count(*) into :1
from ams_hz_b2b_mapping_v ail
where enabled_flag = '||''''||'Y'||''''||
' and import_list_header_id =' || p_incl_object_id ||
' and nvl(party_id, import_source_line_id) in (' ;
select
count(*) :1
from ams_hz_b2c_mapping_v
where enabled_flag = '||''''||'Y'||''''||
' and import_list_header_id =' || p_incl_object_id ||
' and nvl(party_id, import_source_line_id) in (' ;
x_std_sql := ' select nvl(party_id,import_source_line_id)
from ams_imp_source_lines
where import_list_header_id = ' || p_incl_object_id ||
' and nvl(duplicate_flag,' ||''''||'N'||''''||') = '||
''''||'N'||'''' ;
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
l_no_of_chunks number;
select
count(*) into :1
from ams_list_entries
where list_header_id = ' || p_incl_object_id ||
' and nvl(enabled_flag,' ||''''||'N'||''''||') = '||
''''||'Y'||'''' ||
' and list_entry_source_system_id in (' ;
x_std_sql := ' select list_entry_source_system_id
from ams_list_entries
where list_header_id = ' || p_incl_object_id ||
' and enabled_flag = ' || ''''||'Y' || '''' ;
PROCEDURE form_rt_sql_statement(p_select_statement in varchar2,
p_select_add_statement in varchar2,
p_master_type in varchar2,
p_child_types in child_type,
p_from_string in sql_string_4K,
p_action_used_by_id in number,
p_list_select_action_id in number,
p_list_action_type in varchar2,
p_order_number in number,
p_rank in number,
x_final_string OUT NOCOPY varchar2
) is
-- child_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
select source_object_name , source_object_name || '.' || source_object_pk_field
from ams_list_src_types
where source_type_code = p_master_type;
select a.source_object_name ,
a.source_object_name || '.' || b.sub_source_type_pk_column
,b.master_source_type_pk_column
from ams_list_src_types a, ams_list_src_type_assocs b
where a.source_type_code = l_child_src_type
and b.sub_source_type_id = a.list_source_type_id;
l_select_clause varchar2(32767);
l_insert_clause varchar2(32767);
l_insert_sql varchar2(32767);
SELECT b.field_column_name ,
c.source_object_name,
b.source_column_name,
b.field_data_type
BULK COLLECT INTO :1 ,:2 ,:3 ,:4
FROM ams_list_src_fields b, ams_list_src_types c
WHERE b.list_source_type_id = c.list_source_type_id
and b.DE_LIST_SOURCE_TYPE_CODE IN '|| l_data_source_types ||
' AND b.ROWID >= (SELECT MAX(a.ROWID)
FROM ams_list_src_fields a
WHERE a.field_column_name= b.field_column_name
AND a.DE_LIST_SOURCE_TYPE_CODE IN '
|| l_data_source_types || ') ;
l_insert_clause := l_insert_clause || ' ,' || l_field_col_tbl(i) ;
l_select_clause := l_select_clause || ' ,' ||
'to_char('||l_view_tbl(i) || '.'||l_source_col_tbl(i)||','||''''||'DD-MM-RRRR'||''''||')' ;
l_select_clause := l_select_clause || ' ,' ||
l_view_tbl(i) || '.'||l_source_col_tbl(i) ;
l_insert_sql := 'select count(*) into :1 ';
l_final_sql := l_insert_sql || ' ' ||
l_from_clause || ' '||
l_where_clause || ' and ' ||
l_master_primary_key|| ' in ( ' ;
PROCEDURE process_rt_insert_sql(p_select_statement in varchar2,
p_select_add_statement in varchar2,
p_master_type in varchar2,
p_child_types in child_type,
p_from_string in sql_string_4K ,
p_action_used_by_id in number,
p_list_select_action_id in number,
p_list_action_type in varchar2,
p_order_number in number,
p_rank in number,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
l_final_sql varchar2(32767);
l_insert_sql varchar2(32767);
l_insert_sql1 varchar2(32767);
write_to_act_log('Execution of procedure process_rt_insert_sql started', 'LIST', g_list_header_id,'LOW');
l_insert_sql := p_select_statement ;
l_insert_sql := l_insert_sql || p_from_string(i);
x_std_sql := l_insert_sql;
form_rt_sql_statement(p_select_statement ,
p_select_add_statement ,
p_master_type ,
p_child_types ,
p_from_string ,
p_action_used_by_id ,
p_list_select_action_id ,
p_list_action_type ,
p_order_number ,
p_rank ,
l_final_sql
) ;
write_to_act_log('Procedure process_rt_insert_sql executed', 'LIST', g_list_header_id,'LOW');
write_to_act_log('Error while executing process_rt_insert_sql '||sqlcode||' '||sqlerrm , 'LIST', g_list_header_id,'HIGH');
END process_rt_insert_sql;
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
p_sql_string in sql_string,
p_primary_key in varchar2,
p_source_object_name in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
l_sql_string sql_string;
l_select_condition varchar2(2000);
l_select_add_condition varchar2(2000);
l_select_condition := 'SELECT ' ||l_source_object_name||'.'
||l_source_object_pk_field;
l_select_add_condition := ','||l_source_object_name||'.'
||l_source_object_pk_field||','||''''
||l_master_type||'''' ;
write_to_act_log('Calling procedure process_rt_insert_sql' , 'LIST', g_list_header_id,'LOW');
process_rt_insert_sql(p_select_statement => l_select_condition,
p_select_add_statement => l_select_add_condition,
p_master_type => l_master_type,
p_child_types => l_child_types,
p_from_string => l_from_string ,
p_list_select_action_id => p_list_select_action_id ,
p_action_used_by_id => p_action_used_by_id ,
p_list_action_type => p_list_action_type ,
p_order_number => p_order_number,
p_rank => p_rank,
x_std_sql => x_std_sql,
x_include_sql => x_include_sql
);
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
------------------------------------------------------------------------------
-- Given the sql id from ams_list_select_actions it will retrieve the
-- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
-- workbook_name.
------------------------------------------------------------------------------
cursor cur_sql is
SELECT query, primary_key, source_object_name
FROM ams_list_queries_all
WHERE (list_query_id = p_incl_object_id )
or (parent_list_query_id = p_incl_object_id )
order by sequence_order;
SELECT sql_string, primary_key, source_object_name
FROM ams_list_queries_all
WHERE (list_query_id = p_incl_object_id )
or (parent_list_query_id = p_incl_object_id )
order by sequence_order;
SELECT lc.SOURCE_OBJECT_NAME, lc.SOURCE_OBJECT_PK_FIELD
FROM ams_list_queries_all lq,
ams_list_headers_all lh,
ams_list_src_types lc
WHERE lq.list_query_id = p_incl_object_id
and lq.ARC_ACT_LIST_QUERY_USED_BY = 'LIST'
and lq.ACT_LIST_QUERY_USED_BY_ID = lh.list_header_id
and lc.source_type_code = lh.list_source_type;
l_select_condition varchar2(2000);
l_select_add_condition varchar2(2000);
p_list_select_action_id => p_list_select_action_id ,
p_order_number => p_order_number ,
p_rank => p_rank ,
p_include_control_group => p_include_control_group,
p_sql_string => l_sql_string ,
p_primary_key => l_primary_key,
p_source_object_name => l_source_object_name,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
x_return_status => x_return_status ,
x_std_sql => x_std_sql,
x_include_sql => x_include_sql
);
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
------------------------------------------------------------------------------
-- Given the sql id from ams_list_select_actions it will retrieve the
-- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
-- workbook_name.
------------------------------------------------------------------------------
l_sql_string sql_string;
l_select_condition varchar2(2000);
l_select_add_condition varchar2(2000);
p_list_select_action_id => p_list_select_action_id ,
p_order_number => p_order_number ,
p_rank => p_rank ,
p_include_control_group => p_include_control_group,
p_sql_string => l_sql_string ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
x_return_status => x_return_status ,
x_std_sql => x_std_sql,
x_include_sql => x_include_sql,
p_primary_key => null,
p_source_object_name => null);
p_list_select_action_id in number,
p_order_number in number,
p_rank in number,
p_include_control_group in varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_std_sql OUT NOCOPY varchar2 ,
x_include_sql OUT NOCOPY varchar2
) is
------------------------------------------------------------------------------
-- Given the sql id from ams_list_select_actions it will retrieve the
-- sql_srtings from ams_discoverer_sql for a particular worksheet_name and
-- workbook_name.
------------------------------------------------------------------------------
cursor cur_diwb(l_incl_object_id in number ) is
SELECT sql_string
FROM ams_discoverer_sql
WHERE (workbook_name, worksheet_name )
IN
( SELECT workbook_name, worksheet_name
FROM ams_discoverer_sql
WHERE discoverer_sql_id = l_incl_object_id)
ORDER BY sequence_order;
l_select_condition varchar2(2000);
l_select_add_condition varchar2(2000);
p_list_select_action_id => p_list_select_action_id ,
p_order_number => p_order_number ,
p_rank => p_rank ,
p_include_control_group => p_include_control_group,
p_sql_string => l_sql_string,
p_primary_key => null,
p_source_object_name => null,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
x_return_status => x_return_status ,
x_std_sql => x_std_sql,
x_include_sql => x_include_sql);
select list_entry_id from ams_list_entries where list_header_id = p_list_header_id
-- and party_id is null
and nvl(tca_load_status,'x') not in ('SUCCESS','ERROR')
and enabled_flag = 'Y'; --bmuthukr. R12 Need to upload only the enabled entries.
select flds.field_column_name , tca.column_name
from ams_list_src_fields flds, AMS_DS_TCA_ENTITY_COLS tca,
ams_list_headers_all hdr, ams_list_src_types typ
where hdr.list_header_id = p_list_header_id
and hdr.LIST_SOURCE_TYPE = typ.SOURCE_TYPE_CODE
and typ.list_source_type_id = flds.list_source_type_id
and flds.enabled_flag = 'Y'
and used_in_list_entries = 'Y'
and flds.tca_column_id = tca.ENTITY_COLUMN_ID;
select source_category from ams_list_src_types types, ams_list_headers_all head
where head.list_header_id = p_list_header_id
and head.list_source_type = types.source_type_code;
l_string := 'begin select '||list_column_name||' into :l_entry_value from ams_list_entries where
list_entry_id = '||to_char(l_list_entry_id)||' ; end;';
update ams_list_entries set error_flag = 'E',
tca_load_status = 'ERROR',
ENABLED_FLAG = 'N',
--error_text = 'TCA API ERROR :'||substr(x_tmp_var1,1,3000)
error_text = 'TCA API ERROR :'||x_tmp_var
where list_entry_id = l_list_entry_id;
update ams_list_entries set error_flag = 'E',
tca_load_status = 'ERROR',
ENABLED_FLAG = 'N',
--error_text = 'TCA API ERROR :'||nvl(x_tmp_var,substr(x_tmp_var1,1,3000))
error_text = 'TCA API ERROR :'||x_tmp_var
where list_entry_id = l_list_entry_id;
update ams_list_entries set party_id = l_party_id,
error_flag = 'S',
ENABLED_FLAG = 'Y',
tca_load_status = 'SUCCESS'
where list_entry_id = l_list_entry_id;
update ams_list_headers_all
set migration_date = sysdate
where list_header_id = p_list_header_id;
l_list_selection varchar2(1);
l_onlylist_selection varchar2(1);
cursor c_list_selection is
select 'Y' from ams_list_select_actions
where action_used_by_id = p_list_header_id
and arc_action_used_by = 'LIST'
and arc_incl_object_from in ('CELL','DIWB','SQL');
cursor c_only_list_selection is
select 'Y' from ams_list_select_actions act, ams_list_headers_all head
where act.action_used_by_id = p_list_header_id
and act.arc_incl_object_from = 'LIST' and act.arc_action_used_by = 'LIST'
and act.INCL_OBJECT_ID = head.list_header_id
and head.status_code = 'AVAILABLE'
and (head.MIGRATION_DATE is null or head.main_gen_end_time > head.migration_date);
open c_list_selection;
fetch c_list_selection into l_list_selection;
close c_list_selection;
if nvl(l_list_selection,'N') = 'Y' then
write_to_act_log(p_msg_data => 'List selection includes segments/workbook/SQL.' ,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'LOW');
write_to_act_log(p_msg_data => 'List selection does not include segments/workbook/SQL.' ,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'LOW');
if l_list_selection is null then
open c_only_list_selection;
fetch c_only_list_selection into l_onlylist_selection;
close c_only_list_selection;
if nvl(l_onlylist_selection,'N') = 'Y' then
write_to_act_log(p_msg_data => 'List/TG will be generated in remote instance.' ,
p_arc_log_used_by => 'LIST',
p_log_used_by_id => p_list_header_id,
p_level => 'LOW');
select list_type
from ams_list_headers_all
where list_header_id = p_list_header_id;
select 1
from ams_list_entries
where list_header_id = p_list_header_id
and enabled_flag = 'Y'
and rownum = 1;
select list_used_by_id
from ams_act_lists
where list_header_id = p_list_header_id
and list_used_by = 'CSCH'
and list_act_type = 'TARGET';
select 1
from ams_list_select_actions
where list_header_id = p_list_header_id
and arc_incl_object_from = 'EMPLOYEE';
select 1
from ams_act_lists
where list_used_by_id = l_list_used_by_id
and list_act_type = 'EMPLOYEE';
update ams_list_headers_all
set status_code = 'AVAILABLE',
user_status_id = 303,
status_date = sysdate,
last_update_date = sysdate
where list_header_id = p_list_header_id;
update ams_list_headers_all
set status_code = 'DRAFT',
user_status_id = 300,
status_date = sysdate,
last_update_date = sysdate
where list_header_id = p_list_header_id;
select stypes.database_link,
list.remote_gen_flag,
list.list_type
from ams_list_src_types stypes, ams_list_headers_all list
where list.list_source_type = stypes.source_type_code
and list_header_id = p_list_header_id;
select sum(decode(enabled_flag,'N',0,1)),
sum(decode(enabled_flag,'Y',0,1)),
sum(1),
sum(decode(manually_entered_flag,'Y',decode(enabled_flag,'Y','1',0),0))
from ams_list_entries
where list_header_id = p_list_header_id;
update ams_list_headers_all
set no_of_rows_in_list = nvl(l_no_of_rows_in_list,0),
no_of_rows_active = nvl(l_no_of_rows_active,0),
no_of_rows_inactive = nvl(l_no_of_rows_inactive,0),
no_of_rows_manually_entered = nvl(l_no_of_rows_manually_entered,0)
where list_header_id = p_list_header_id;