[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE copy_selections
(p_old_header_id in number,
p_new_header_id in number,
p_list_name IN varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_query_id OUT NOCOPY NUMBER
);
PROCEDURE UPDATE_LIST_STATUS_TO_LOCKED
(
P_OBJECT_TYPE IN VARCHAR2,
P_OBJ_ID IN NUMBER,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
SELECT cell_name
FROM ams_cells_vl
WHERE cell_id = p_cell_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
,g_list_header_id
,'LIST'
,ams_act_logs_transaction_id_s.NEXTVAL
,g_message_table(i)
) ;
SELECT nvl(Min(order_number),-1)
FROM ams_act_lists
WHERE list_used_by_id = p_act_list_rec.list_used_by_id and
list_used_by = p_act_list_rec.list_used_by and
list_act_type <> 'TARGET';
SELECT list_action_type
FROM ams_act_lists
WHERE list_used_by_id = p_act_list_rec.list_used_by_id and
list_used_by = p_act_list_rec.list_used_by and
list_act_type <> 'TARGET' and
order_number = l_min_order;
SELECT AMS_ACT_LISTS_s.NEXTVAL
FROM dual;
SELECT 1
FROM AMS_ACT_LISTS
WHERE ACT_LIST_HEADER_ID = l_id;
select alg.act_list_group_id
from ams_act_list_groups alg,
ams_act_lists acl
where alg.arc_act_list_used_by = 'TARGET'
and alg.group_code = cur_group_code
and alg.act_list_used_by_id = acl.list_header_id
and acl.list_used_by = 'LIST'
and acl.list_used_by_id = p_act_list_rec.list_used_by_id
and acl.list_act_type = 'TARGET' ;
l_list_group_rec.last_update_date := sysdate;
l_list_group_rec.last_updated_by := fnd_global.user_id;
AMS_ACT_LISTS_PKG.Insert_Row(
px_act_list_header_id => l_act_list_header_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
px_object_version_number => l_object_version_number,
p_last_update_login => FND_GLOBAL.conc_LOGIN_ID,
p_list_header_id => l_act_list_rec.list_header_id,
p_group_code => l_act_list_rec.group_code ,
p_list_used_by_id => l_act_list_rec.list_used_by_id,
p_list_used_by => l_act_list_rec.list_used_by,
p_list_act_type => l_act_list_rec.list_act_type,
p_list_action_type => l_act_list_rec.list_action_type,
p_order_number => l_act_list_rec.order_number
);
x_act_list_rec.last_update_date := FND_API.g_miss_date ;
x_act_list_rec.last_updated_by := FND_API.g_miss_num ;
x_act_list_rec.last_update_login := FND_API.g_miss_num ;
SELECT *
FROM ams_act_lists
WHERE act_list_header_id = p_act_list_rec.act_list_header_id;
IF p_act_list_rec.last_update_date = FND_API.g_miss_date THEN
x_complete_rec.last_update_date := l_act_list_rec.last_update_date;
IF p_act_list_rec.last_updated_by = FND_API.g_miss_num THEN
x_complete_rec.last_updated_by := l_act_list_rec.last_updated_by;
IF p_act_list_rec.last_update_login = FND_API.g_miss_num THEN
x_complete_rec.last_update_login := l_act_list_rec.last_update_login;
PROCEDURE Update_Act_List(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_act_list_rec IN act_list_rec_type,
x_object_version_number OUT NOCOPY NUMBER
)
IS
-- Get the smallest order number
l_min_order number;
SELECT nvl(Min(order_number),-1)
FROM ams_act_lists
WHERE list_used_by_id = p_act_list_rec.list_used_by_id and
list_used_by = p_act_list_rec.list_used_by and
list_act_type <> 'TARGET';
SELECT list_action_type
FROM ams_act_lists
WHERE list_used_by_id = p_act_list_rec.list_used_by_id and
list_used_by = p_act_list_rec.list_used_by and
list_act_type <> 'TARGET' and
order_number = l_min_order;
SELECT *
FROM AMS_ACT_LISTS
where act_list_header_id = cur_act_list_header_id ;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Act_List';
select alg.act_list_group_id
from ams_act_list_groups alg,
ams_act_lists acl
where alg.arc_act_list_used_by = 'TARGET'
and alg.group_code = cur_group_code
and p_act_list_rec.list_header_id = alg.act_list_used_by_id
and acl.list_used_by = 'LIST'
and acl.list_used_by_id = p_act_list_rec.list_used_by_id
and acl.list_act_type = 'TARGET' ;
SAVEPOINT UPDATE_Act_List_PVT;
AMS_Utility_PVT.debug_message('Private API: - Open Cursor to Select');
'AMS_API_MISSING_UPDATE_TARGET',
p_token_name => 'INFO',
p_token_value => 'Act_List') ;
p_token_value => 'Last_Update_Date') ;
l_list_group_rec.last_update_date := sysdate;
l_list_group_rec.last_updated_by := fnd_global.user_id;
AMS_ACT_LISTS_PKG.Update_Row(
p_act_list_header_id => l_act_list_rec.act_list_header_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_object_version_number => p_act_list_rec.object_version_number,
p_last_update_login => FND_GLOBAL.conc_LOGIN_ID,
p_list_header_id => l_act_list_rec.list_header_id,
p_group_code => l_act_list_rec.group_code,
p_list_used_by_id => l_act_list_rec.list_used_by_id,
p_list_used_by => l_act_list_rec.list_used_by,
p_list_act_type => l_act_list_rec.list_act_type,
p_list_action_type => l_act_list_rec.list_action_type,
p_order_number => l_act_list_rec.order_number
);
ROLLBACK TO UPDATE_Act_List_PVT;
ROLLBACK TO UPDATE_Act_List_PVT;
ROLLBACK TO UPDATE_Act_List_PVT;
End Update_Act_List;
PROCEDURE Delete_Act_List(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_act_list_header_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Act_List';
select 'x'
from ams_act_lists a, ams_campaign_schedules_vl b
where act_list_header_id = p_act_list_header_id
and a.list_used_by_id = b.schedule_id
and a.list_used_by = 'CSCH'
and b.status_code = 'ACTIVE'
union
select 'x'
from ams_act_lists a, ams_event_offers_vl b
where p_act_list_header_id = p_act_list_header_id
and a.list_used_by_id = b.event_offer_id
and a.list_used_by in('EVEO','EONE')
and b.system_status_code = 'ACTIVE';
SAVEPOINT DELETE_Act_List_PVT;
AMS_Utility_PVT.debug_message( 'Private API: Calling delete table
handler');
FND_MESSAGE.set_name('AMS', 'AMS_DELETE_TARGET');
AMS_ACT_LISTS_PKG.Delete_Row(
p_ACT_LIST_HEADER_ID => p_ACT_LIST_HEADER_ID);
ROLLBACK TO DELETE_Act_List_PVT;
ROLLBACK TO DELETE_Act_List_PVT;
ROLLBACK TO DELETE_Act_List_PVT;
End Delete_Act_List;
SELECT ACT_LIST_HEADER_ID
FROM AMS_ACT_LISTS
WHERE ACT_LIST_HEADER_ID = p_ACT_LIST_HEADER_ID
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
SELECT count(1) FROM ams_act_lists
WHERE list_used_by_id = p_list_used_by_id
AND list_used_by = p_list_used_by
AND LIST_ACT_TYPE = p_LIST_ACT_TYPE
AND list_header_id = p_list_header_id
AND act_list_header_id <> p_act_list_header_id;
/* Update Record */
IF p_act_list_rec.act_list_header_id IS NULL THEN
AMS_Utility_PVT.Error_Message(p_message_name =>
'AMS_API_MISSING_FIELD');
p_lookup_type => 'AMS_LIST_SELECT_ACTION',
p_lookup_code => p_act_list_rec.list_action_type
) = FND_API.g_false
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
THEN
FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_TYPE_INVALID');
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => x_return_status
);
select count(1)
from ams_act_lists
where list_used_by = p_list_used_by
and list_used_by_id = p_list_used_by_id
and list_act_type = 'TARGET' ;
SELECT list_entry_id
FROM ams_list_entries
WHERE list_header_id = p_list_header_id
AND enabled_flag = 'Y'
ORDER BY randomly_generated_number ;
SELECT count(1)
FROM ams_list_entries
WHERE list_header_id = p_list_header_id
AND enabled_flag = 'Y';
UPDATE ams_list_entries
SET randomly_generated_number = DBMS_RANDOM.random
WHERE list_header_id = p_list_header_id
and enabled_flag = 'Y';
UPDATE ams_list_entries
SET part_of_control_group_flag = 'Y',
enabled_flag = 'N'
WHERE list_header_id = p_list_header_id
AND list_entry_id = l_list_entries_id(i);
SELECT acr.list_header_id
FROM ams_list_cont_restrictions acr,
ams_list_headers_all alh
WHERE acr.list_header_id = alh.list_header_id
AND alh.status_code in ('AVAILABLE','LOCKED')
AND alh.list_source_type = l_source_type
AND acr.media_id = l_media_id;
UPDATE ams_list_entries a
SET a.enabled_flag = 'N', a.MARKED_AS_SUPPRESSED_FLAG = 'Y'
WHERE a.list_header_id = p_list_header_id
AND a.enabled_flag = 'Y'
AND exists (SELECT 'x'
FROM ams_list_entries b
WHERE b.list_header_id = l_list_header_id
AND a.party_id = b.party_id
AND b.enabled_flag = 'Y');
UPDATE ams_list_entries a SET a.enabled_flag = 'N', a.MARKED_AS_SUPPRESSED_FLAG = 'Y'
WHERE a.list_header_id = p_list_header_id
AND a.enabled_flag = 'Y'
AND exists (SELECT 'x'
FROM ams_list_entries b
WHERE b.list_header_id = l_list_header_id
AND b.dedupe_key = a.dedupe_key
AND b.enabled_flag = 'Y');
SELECT am.list_rule_id,
ac.campaign_id,
ac.activity_id
FROM ams_campaign_schedules_b ac,
ams_list_rule_usages am,
ams_list_headers_all al
WHERE al.list_used_by_id = ac.schedule_id
AND am.list_header_id(+) = al.list_header_id
AND ac.schedule_id = p_list_used_by_id
AND al.list_header_id = p_list_header_id ;
SELECT list_source_type
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id ;
SELECT b.field_column_name
FROM ams_list_rule_fields a,
ams_list_src_fields b
WHERE a.list_rule_id = cur_rule_id
AND a.LIST_SOURCE_FIELD_ID = b.LIST_SOURCE_FIELD_ID;
SELECT 1
FROM ams_list_src_fields f,
ams_list_src_types t
WHERE t.LIST_SOURCE_TYPE_ID = f.LIST_SOURCE_TYPE_ID
AND f.FIELD_COLUMN_NAME = 'PARTY_ID'
AND f.SOURCE_COLUMN_NAME = t.SOURCE_OBJECT_PK_FIELD
AND t.SOURCE_TYPE_CODE = l_source_type;
l_string := 'update ams_list_entries set dedupe_key = ';
write_to_act_log(p_msg_data => 'SQL string with DD keys from the selected DD Rule = '||l_string,p_arc_log_used_by => 'LIST',p_log_used_by_id => p_list_header_id,p_level=>'LOW');
SELECT source_code,source_code_for_id INTO x_source_code,x_source_id
FROM ams_source_codes
WHERE arc_source_code_for = UPPER(p_activity_type)
AND source_code_for_id = UPPER(p_activity_id)
and active_flag = 'Y';
select sc.campaign_id , sc.use_parent_code_flag
from ams_campaign_schedules_vl sc
where sc.SCHEDULE_ID = cur_list_used_by_id ;
SAVEPOINT Update_ListEntry_Source_Code;
FND_MESSAGE.Set_Token('ROW', 'Update_Source_Code: Start', TRUE);
AMS_Utility_PVT.debug_message('Update_Source_Code:Start');
FND_MESSAGE.Set_Token('ROW', 'AMS_List_Entry_PVT.Update_ListEntry_Source_Code', TRUE);
FND_MESSAGE.Set_Token('ROW', 'Update_Source_Code: END', TRUE);
select al.list_header_id,
al.ctrl_random_pct_row_selection,
al.ctrl_random_nth_row_selection,
al.generate_control_group_flag, al.generation_type
from ams_act_lists acl,ams_list_headers_all al
where acl.list_used_by = p_list_used_by
and acl.list_used_by_id = p_list_used_by_id
and acl.list_act_type = 'TARGET'
and al.list_header_id = acl.list_header_id ;
select list_header_id
from ams_act_lists
where list_used_by = p_list_used_by
and list_used_by_id = p_list_used_by_id
and list_act_type <> 'TARGET' ;
select acl.list_act_type,
acl.list_header_id,
acl.list_used_by_id, acl.act_list_header_id
from ams_act_lists acl
where acl.list_used_by = p_list_used_by
and acl.list_used_by_id = p_list_used_by_id
and acl.list_act_type = 'CELL' ;
select status_code from ams_list_headers_all
where list_header_id = l_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 = list_head_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 nvl(stypes.remote_flag,'N')
from ams_list_src_types stypes, ams_list_headers_all list
where list.list_source_type = stypes.source_type_code
and list_header_id = list_head_id ;
/* Bug fix: 3799192. Added by rrajesh on 07/30/04. If there is no maapping to TCA, update the
status to FAILED and return */
write_to_act_log(p_msg_data => 'Checking if datasource fields are mapped to TCA fields. ',
p_arc_log_used_by => 'TARGET',
p_log_used_by_id => l_list_header_id,
p_level => '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 = l_list_header_id;
UPDATE ams_list_entries a
SET a.enabled_flag = 'N',
a.marked_as_duplicate_flag = 'Y'
WHERE a.list_header_id = l_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 = l_list_header_id
and b.dedupe_key = a.dedupe_key
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 = l_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 = l_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 =l_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 = l_list_header_id
and b.party_id = a.party_id
and b.enabled_flag = 'Y'
and b.rank = a.rank
);
UPDATE ams_list_entries a
SET a.enabled_flag = 'N',
a.marked_as_duplicate_flag = 'Y'
WHERE a.list_header_id = l_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 = l_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 = l_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 = l_list_header_id
and b.dedupe_key = a.dedupe_key
and b.enabled_flag = 'Y'
);*/
/*select count(1)
into l_count
from ams_list_entries
where list_header_id = l_list_header_id
and enabled_flag = 'Y';
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 = l_list_header_id ;
AMS_LISTGENERATION_PKG.Update_List_Dets(l_list_header_id ,x_return_status ) ;
select al.list_header_id
from ams_act_lists acl,ams_list_headers_all al
where acl.list_used_by = p_list_used_by
and acl.list_used_by_id = p_list_used_by_id
and acl.list_act_type = 'TARGET'
and al.list_header_id = acl.list_header_id ;
select acl.list_header_id
from ams_act_lists acl
where acl.list_used_by = p_list_used_by
and acl.list_used_by_id = p_list_used_by_id
and acl.list_act_type = 'CELL' ;
SELECT status_code
FROM ams_list_headers_all
WHERE list_header_id = l_list_header_id ;
PROCEDURE form_sql_statement(p_select_statement in varchar2,
p_select_add_statement in varchar2,
p_master_type in varchar2,
p_child_types in AMS_LISTGENERATION_PKG.child_type,
p_from_string in AMS_LISTGENERATION_PKG.sql_string ,
p_act_list_header_id in number,
p_action_used_by_id 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 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
BULK COLLECT INTO :1 ,:2 ,:3
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 || ' ,' ||
l_view_tbl(i) || '.'||l_source_col_tbl(i) ;
WRITE_TO_ACT_LOG('form_sql_statement:before insert_sql ','LIST',g_list_header_id);
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 , '||
' 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||''''||','||
0 || ',' ||
to_char(g_list_header_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|| ' 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 AMS_LISTGENERATION_PKG.child_type,
p_from_string in AMS_LISTGENERATION_PKG.sql_string ,
p_act_list_header_id in number,
p_action_used_by_id 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('process_insert_sql:-->begin<--',null,null);
l_insert_sql := p_select_statement ;
write_to_act_log(l_insert_sql,null,null);
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_act_list_header_id ,
p_action_used_by_id ,
l_final_sql
) ;
END process_insert_sql;
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('Process_all_sql: ***********insert sql ***********',null,null);
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_act_list_header_id => p_act_list_header_id ,
p_action_used_by_id => p_action_used_by_id ,
x_std_sql => x_std_sql,
x_include_sql => x_include_sql
);
write_to_act_log('Process_all_sql: ***********end insert sql->' ,null,null);
l_select_condition varchar2(2000);
l_select_add_condition varchar2(2000);
' select list_entry_source_system_id ' ||
' from ams_list_entries ' ||
' where list_header_id = ' || g_list_header_id ;
select b.list_name, b.list_header_id, a.list_used_by, a.list_used_by_id,
a.list_act_type, a.group_code, a.list_action_type, a.order_number,b.query_template_id,b.purpose_code,b.APPLY_TRAFFIC_COP,
-- ckapoor R12 copy tg enhancements
b.CTRL_CONF_LEVEL, b.CTRL_REQ_RESP_RATE, b.CTRL_LIMIT_OF_ERROR, b.STATUS_CODE_OLD, b.CTRL_CONC_JOB_ID, b.CTRL_STATUS_CODE, b.CTRL_GEN_MODE, b.APPLY_SUPPRESSION_FLAG,
-- end ckapoor R12 copy tg enhancements
c.schedule_name || ' - ' || c.source_code list_name1
,b.main_random_pct_row_selection,b.row_selection_type,b.no_of_rows_max_requested --rmbhanda bug#4667513
from ams_act_lists a, ams_list_headers_vl b, ams_campaign_schedules_vl c
where a.list_used_by = p_list_used_by
and a.list_used_by_id = p_from_schedule_id
and a.list_act_type = l_list_type
and a.list_header_id = b.list_header_id
and c.schedule_id = p_from_schedule_id
and c.schedule_id = a.list_used_by_id ;
select a.list_header_id, a.list_used_by, a.list_used_by_id,
a.list_act_type, a.group_code, a.list_action_type, a.order_number
from ams_act_lists a
where a.list_used_by = p_list_used_by
and a.list_used_by_id = p_from_schedule_id
and a.list_act_type <> l_list_type
order by order_number ;
select
a.tgrp_exclude_prev_flag
from ams_campaign_schedules_vl a
where a.schedule_id = p_from_schedule_id ;
select c.SCHEDULE_ID,c.SOURCE_CODE,c.SCHEDULE_NAME,c.schedule_name || ' - ' || c.source_code list_name
from ams_campaign_schedules_vl c
where c.schedule_id = p_to_schedule_id;
SELECT template_type
FROM ams_query_template_all
WHERE template_id=p_templete_id;
SELECT no_of_rows_active
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id;
select list_rule_id from ams_list_rule_usages
where list_header_id = p_list_header_id;
l_listheader_rec.row_selection_type:= l_target_group_rec.row_selection_type; --rmbhanda bug#4667513
l_listheader_rec.main_random_pct_row_selection:=l_target_group_rec.main_random_pct_row_selection; --rmbhanda bug#4667513
UPDATE ams_list_headers_all SET query_template_id = l_query_temp_id
WHERE list_header_id = l_list_header_id;
copy_selections
( p_old_header_id => l_old_list_header_id,
p_new_header_id => l_list_header_id,
p_list_name => l_listheader_rec.list_name,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_query_id =>l_query_id
);
AMS_Utility_PVT.debug_message('Private API: copy_target_group, done copy_selections '||FND_API.G_VALID_LEVEL_FULL);
insert into ams_list_rule_usages
(LIST_RULE_USAGE_ID, LIST_HEADER_ID, LIST_RULE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, ACTIVE_FROM_DATE,
ACTIVE_TO_DATE, PRIORITY, SECURITY_GROUP_ID)
values (
ams_list_rule_usages_s.nextval,
l_list_header_id,
l_list_rule_id,
sysdate,
FND_GLOBAL.User_Id,
sysdate,
FND_GLOBAL.User_Id,
FND_GLOBAL.Conc_Login_Id,
1,
sysdate,
null,
null,
null
);
l_exclude_action_rec.list_select_action_id := NULL;
PROCEDURE copy_selections
(p_old_header_id in number,
p_new_header_id in number,
p_list_name IN varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_query_id OUT NOCOPY NUMBER
) is
CURSOR c_select_actions(p_header_id IN number) IS
SELECT LIST_SELECT_ACTION_ID
FROM ams_list_select_actions WHERE action_used_by_id=p_header_id;
CURSOR fetch_list_select_actions(p_header_id NUMBER) IS
SELECT incl_object_id,rank,order_number,description,list_action_type
,no_of_rows_requested,no_of_rows_available,no_of_rows_used
,distribution_pct,no_of_rows_targeted,incl_object_name,arc_incl_object_from,arc_action_used_by
FROM ams_list_select_actions
WHERE action_used_by_id =p_header_id
order by order_number;
l_list_select_action_id NUMBER;
AMS_Utility_PVT.debug_message('Private API: copy_selections: Begin');
OPEN c_select_actions(p_old_header_id);
FETCH c_select_actions INTO l_list_select_action_id;
AMS_Utility_PVT.debug_message('copy_selections old '||p_old_header_id);
AMS_Utility_PVT.debug_message('copy_selections new '||p_new_header_id);
FOR l_list_actions_rec IN fetch_list_select_actions(p_old_header_id)
LOOP
IF (AMS_DEBUG_HIGH_ON) THEN
AMS_Utility_PVT.debug_message('********************copy_selections enter loop');
l_action_rec.list_select_action_id := NULL;
AMS_Utility_PVT.debug_message('CAlling Create List Action in Copy Selections');
UPDATE ams_list_select_actions SET incl_object_name = l_list_actions_rec.incl_object_name
WHERE list_select_action_id = l_action_id;
AMS_Utility_PVT.debug_message('copy_selections done creation');
END copy_selections;
SELECT LIST_QUERY_ID
FROM ams_list_queries_all WHERE list_query_id = p_query_id;
SELECT list_query_id,name,type,query,primary_key,source_object_name,act_list_query_used_by_id,arc_act_list_query_used_by,seed_flag,parameterized_flag,admin_flag,query_template_id,query_type
FROM ams_list_queries_vl
WHERE list_query_id = p_query_id;
SELECT query
FROM ams_list_queries_all
WHERE list_query_id = p_query_id;
UPDATE ams_list_queries_all
SET --query = long_var,
parameterized_flag = l_queries_rec.parameterized_flag,
admin_flag = l_queries_rec.admin_flag,
query_template_id = l_queries_rec.query_template_id,
query_type = l_queries_rec.query_type,
seed_flag = l_queries_rec.seed_flag,
arc_act_list_query_used_by = l_queries_rec.arc_act_list_query_used_by
WHERE list_query_id = l_list_query_id;
SELECT list_query_param_id, list_query_id, parameter_order, parameter_value, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, object_version_number, attb_lov_id, param_value_2, condition_value, parameter_name, display_name
FROM AMS_LIST_QUERIES_PARAM_VL
WHERE list_query_id =p_query_id;
SELECT AMS_LIST_QUERIES_PARAM_s.NEXTVAL FROM dual;
AMS_LIST_QUERIES_PARAM_PKG.INSERT_ROW (
X_LIST_QUERY_PARAM_ID => l_list_query_param_id,
X_ATTB_LOV_ID =>l_query_param_rec.attb_lov_id,
X_PARAM_VALUE_2 => l_query_param_rec.param_value_2,
X_CONDITION_VALUE => l_query_param_rec.condition_value,
X_PARAMETER_NAME => l_query_param_rec.parameter_name,
X_LIST_QUERY_ID =>p_new_query_id,
X_PARAMETER_ORDER => l_query_param_rec.parameter_order,
X_PARAMETER_VALUE =>l_query_param_rec.parameter_value,
X_OBJECT_VERSION_NUMBER => 1,
X_DISPLAY_NAME => l_query_param_rec.display_name,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => FND_GLOBAL.User_Id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => FND_GLOBAL.User_Id,
X_LAST_UPDATE_LOGIN =>FND_GLOBAL.CONC_LOGIN_ID
);
SELECT ams_query_template_instance_s.NEXTVAL FROM dual;
SELECT template_instance_id, admin_indicator_flag, request_id, view_application_id, instance_used_by, instance_used_by_id
FROM ams_query_template_instance
where instance_used_by_id = p_header_id;
AMS_QUERY_TEMP_INST_PKG.INSERT_ROW (
X_TEMPLATE_INSTANCE_ID => l_templ_inst_id,
X_TEMPLATE_ID => p_query_templ_id,
X_ADMIN_INDICATOR_FLAG => l_templ_inst_rec.admin_indicator_flag,
X_OBJECT_VERSION_NUMBER => 1,
X_REQUEST_ID => NULL,
X_VIEW_APPLICATION_ID => l_templ_inst_rec.view_application_id,
X_INSTANCE_USED_BY => 'LIST',
X_INSTANCE_USED_BY_ID => p_new_header_id ,
X_CREATION_DATE => SYSDATE ,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE ,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
);
SELECT ams_query_temp_inst_cond_asc_s.NEXTVAL FROM dual;
SELECT ams_query_condition_value_s.NEXTVAL FROM dual;
SELECT assoc_id ,template_instance_id,query_condition_id,condition_sequence,running_total, delta
FROM ams_query_temp_inst_cond_assoc
where template_instance_id = p_templ_inst_id;
SELECT query_cond_value_id,assoc_id,query_cond_disp_struct_id,value,lov_values_included_flag
FROM ams_query_condition_value
where assoc_id = p_assoc_id;
ams_query_tmp_assoc_pkg.insert_row (
X_ASSOC_ID => l_assoc_id,
X_TEMPLATE_INSTANCE_ID=>p_new_templ_inst_id,
X_QUERY_CONDITION_ID => l_cond_assoc_rec.query_condition_id ,
X_CONDITION_SEQUENCE => l_cond_assoc_rec.condition_sequence ,
X_RUNNING_TOTAL => l_cond_assoc_rec.running_total ,
X_DELTA => l_cond_assoc_rec.delta ,
X_OBJECT_VERSION_NUMBER =>1,
X_REQUEST_ID =>NULL,
X_CREATION_DATE => SYSDATE ,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE ,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
);
AMS_QUERY_CONDITION_VALUE_PKG.INSERT_ROW (
X_QUERY_COND_VALUE_ID=>l_cond_value_id,
X_ASSOC_ID => l_assoc_id,
X_QUERY_COND_DISP_STRUCT_ID => l_cond_value_rec.query_cond_disp_struct_id,
X_VALUE =>l_cond_value_rec.value,
X_LOV_VALUES_INCLUDED_FLAG => l_cond_value_rec.lov_values_included_flag,
X_OBJECT_VERSION_NUMBER =>1,
X_REQUEST_ID => NULL,
X_CREATION_DATE => SYSDATE ,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE ,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
);
UPDATE_LIST_STATUS_TO_LOCKED(P_OBJECT_TYPE => l_related_event_obj_type ,
P_OBJ_ID => l_related_event_id,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA =>l_msg_data,
X_RETURN_STATUS => l_return_status);
UPDATE_LIST_STATUS_TO_LOCKED(P_OBJECT_TYPE => l_obj_type ,
P_OBJ_ID => l_obj_id,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA =>l_msg_data,
X_RETURN_STATUS => l_return_status);
UPDATE_LIST_STATUS_TO_LOCKED(P_OBJECT_TYPE => l_obj_type ,
P_OBJ_ID => l_obj_id,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA =>l_msg_data,
X_RETURN_STATUS => l_return_status);
PROCEDURE UPDATE_LIST_STATUS_TO_LOCKED
(
P_OBJECT_TYPE IN VARCHAR2,
P_OBJ_ID IN NUMBER,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
IS
BEGIN
-- INITIALIZE RETURN STATUS TO SUCCESS
X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
AMS_Utility_PVT.debug_message('****FUNCTION: UPDATE_LIST_STATUS_TO_LOCKED start****');
UPDATE AMS_LIST_HEADERS_ALL
SET STATUS_CODE = 'LOCKED',
USER_STATUS_ID = (select user_status_id from ams_user_statuses_vl where system_status_type ='AMS_LIST_STATUS' and system_status_code ='LOCKED' and default_flag ='Y' ),
STATUS_DATE = SYSDATE,
LAST_UPDATE_DATE = SYSDATE
WHERE LIST_USED_BY_ID = P_OBJ_ID
AND ARC_LIST_USED_BY = P_OBJECT_TYPE
AND LIST_TYPE = 'TARGET';
AMS_Utility_PVT.debug_message('TARGET GROUP LOCKED while executing procedure UPDATE_LIST_STATUS_TO_LOCKED');
AMS_Utility_PVT.debug_message('Exception while executing procedure UPDATE_LIST_STATUS_TO_LOCKED'||' '||sqlerrm||' '||sqlcode);
AMS_Utility_PVT.debug_message('UNEXPECTED_ERROR while executing procedure UPDATE_LIST_STATUS_TO_LOCKED'||' '||sqlerrm||' '||sqlcode);
AMS_Utility_PVT.debug_message('Other Error while executing procedure UPDATE_LIST_STATUS_TO_LOCKED'||' '||sqlerrm||' '||sqlcode);
END UPDATE_LIST_STATUS_TO_LOCKED;