[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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('manual list:before insert_sql ','LIST',p_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 , '||
' 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||''''||','||
0 || ',' ||1||','||
p_list_header_id || ',' ||''''||
to_char(sysdate )|| ''''||','||
to_char(FND_GLOBAL.login_id )|| ',' ||''''||
to_char(sysdate )|| ''''||','||
l_created_by|| ',' ||
'ams_list_entries_s.nextval' || ','||
1 || ','||
''''||'NONE' ||'''' || ','||
0 || ',' ||
''''||'NONE' ||'''' || ','||
''''||'NONE' ||'''' || ','||
'ams_list_entries_s.currval'|| ','||
530 || ','||
''''||'Y' ||''''|| ','||
''''||'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 ||''''|| ' = ' ;
l_final_sql := l_insert_sql || ' ' ||
l_from_clause || ' '||
l_where_clause || ' and ' ||
l_master_primary_key|| ' = ' ;
PROCEDURE do_bulk_insert
( p_sql_string IN VARCHAR2
,p_list_header_id in NUMBER
,p_primary_key_tbl IN JTF_NUMBER_TABLE--primary_key_Tbl_Type ,
,x_added_entry_count OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
l_api_name constant varchar2(30) := 'do_bulk_insert';
not exists (select 1
from ams_list_entries
where list_entry_source_system_id = :tab(i)
and list_header_id = :5 and enabled_flag=''Y'');
END do_bulk_insert;
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;
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))
from ams_list_entries
where list_header_id = cur_p_list_header_id ;
select no_of_rows_max_requested - no_of_rows_active
from ams_list_headers_all
where list_header_id = p_list_header_id ;
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) ;
update ams_list_entries
set marked_flag = null
where list_header_id = p_list_header_id ;
not exists (select 1
from ams_list_entries
where list_entry_source_system_id = :tab(i)
and list_header_id = :5 and enabled_flag=''Y'');
do_bulk_insert(
p_sql_string => l_sql_string
,p_list_header_id => p_list_header_id
,p_primary_key_tbl => p_primary_key_tbl
,x_added_entry_count => l_added_entry_count
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
do_bulk_insert(
p_sql_string => l_sql_string
, p_list_header_id => p_list_header_id
, p_primary_key_tbl => l_primary_tbl
, x_added_entry_count => l_added_entry_count
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
l_primary_tbl.delete;
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 = l_no_of_rows_in_list,
no_of_rows_active = l_no_of_rows_active,
no_of_rows_inactive = l_no_of_rows_inactive,
no_of_rows_in_ctrl_group = l_no_of_rows_in_ctrl_group,
no_of_rows_random = l_no_of_rows_random,
no_of_rows_duplicates = l_no_of_rows_duplicates,
no_of_rows_manually_entered = l_no_of_rows_manually_entered ,
status_code = l_new_status,
user_status_id = l_new_status_id,
status_date = sysdate
WHERE list_header_id = p_list_header_id;
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.manually_entered_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'
and b.manually_entered_flag ='Y'
);
/*update ams_list_headers_all
set no_of_rows_in_list = no_of_rows_in_list + x_added_entry_count,
no_of_rows_active = no_of_rows_active + x_added_entry_count,
no_of_rows_manually_entered = no_of_rows_manually_entered + x_added_entry_count,
status_code = l_new_status,
user_status_id = l_new_status_id,
status_date = sysdate
WHERE list_header_id = p_list_header_id; */
update ams_list_headers_all
set no_of_rows_in_list = no_of_rows_in_list + x_added_entry_count,
no_of_rows_active = no_of_rows_active + x_added_entry_count - l_no_of_rows_duplicated ,
no_of_rows_manually_entered = no_of_rows_manually_entered + x_added_entry_count,
status_code = l_new_status,
user_status_id = l_new_status_id,
status_date = sysdate,
no_of_rows_duplicates = no_of_rows_duplicates + l_no_of_rows_duplicated
WHERE 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(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))
from ams_list_entries
where list_header_id = cur_p_list_header_id ;
SELECT created_by
FROM ams_list_headers_all
WHERE list_header_id= x_list_header_id;
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,
marked_flag ,
object_version_number,
first_name,
last_name,
email_address
)
( 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,
0,
'NONE',
'NONE',
'NONE',
0,
p_list_header_id,
ams_list_entries_s.currval,
p_primary_key_tbl(i) ,
p_master_type,
530,
'Y',
'N',
'N',
'N',
'N',
'Y',
'Y',
1,
p_first_name_tbl(i),
p_last_name_tbl(i),
p_email_tbl(i)
from dual
where not exists (select 'x'
from ams_list_entries
where list_entry_source_system_id = p_primary_key_tbl(i)
and list_header_id = p_list_header_id));
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 = l_no_of_rows_in_list,
no_of_rows_active = l_no_of_rows_active,
no_of_rows_inactive = l_no_of_rows_inactive,
no_of_rows_in_ctrl_group = l_no_of_rows_in_ctrl_group,
no_of_rows_random = l_no_of_rows_random,
no_of_rows_duplicates = l_no_of_rows_duplicates,
no_of_rows_manually_entered = l_no_of_rows_manually_entered ,
status_code = l_new_status,
user_status_id = l_new_status_id,
status_date = sysdate
WHERE list_header_id = p_list_header_id;