[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update ams_list_headers_all
set status_code = 'PENDING', status_date = sysdate
where list_header_id = p_list_header_id;
Cursor C_Source_Fields Is Select f.Field_Column_Name,replace(replace(substr(f.SOURCE_COLUMN_MEANING,1,30),' ','_'),'-','')
From Ams_List_Src_Fields f,
Ams_List_Src_Types t
Where upper(t.Source_Type_Code) = upper(P_Source_Type_Code)
And upper(t.list_source_type) = upper(P_List_Source_Type)
And f.list_source_type_id = t.list_source_type_id
order by 2;
Is Select f.Field_Column_Name,replace(replace(substr(f.SOURCE_COLUMN_MEANING,1,30),' ','_'),'-','')
From Ams_List_Src_Fields f,
Ams_List_Src_Types t,
Ams_List_Src_Type_Assocs a
Where upper(t.Source_Type_Code) = upper(P_Source_Type_Code)
And upper(t.list_source_type) = upper(P_List_Source_Type)
And a.master_source_type_id = t.list_source_type_id
And a.sub_source_type_id = f.list_source_type_id
order by 2;
l_sql_str := ' create or replace view '||l_view_name||' as Select ';
||' ale.LIST_ENTRY_ID,ale.LIST_HEADER_ID,ale.OBJECT_VERSION_NUMBER,ale.LIST_SELECT_ACTION_ID,ale.ARC_LIST_SELECT_ACTION_FROM,ale.LIST_SELECT_ACTION_FROM_NAME,'
||' ale.SOURCE_CODE,ale.ARC_LIST_USED_BY_SOURCE,ale.PIN_CODE,ale.LIST_ENTRY_SOURCE_SYSTEM_ID,ale.LIST_ENTRY_SOURCE_SYSTEM_TYPE,'
||'ale.VIEW_APPLICATION_ID,ale.MANUALLY_ENTERED_FLAG,ale.MARKED_AS_DUPLICATE_FLAG,ale.MARKED_AS_RANDOM_FLAG,ale.PART_OF_CONTROL_GROUP_FLAG,'
||' ale.ENABLED_FLAG,ale.CELL_CODE,ale.CAMPAIGN_ID,ale.MEDIA_ID,ale.CHANNEL_ID,ale.CHANNEL_SCHEDULE_ID,ale.EVENT_OFFER_ID,ale.LAST_UPDATE_DATE,ale.LAST_UPDATED_BY,ale.CREATION_DATE,ale.CREATED_BY FROM ams_list_entries ale '
||' WHERE ale.LIST_ENTRY_SOURCE_SYSTEM_TYPE ='||''''||P_SOURCE_TYPE_CODE||'''';
|| ' ale.OBJECT_VERSION_NUMBER,ale.LAST_UPDATE_DATE, '
|| ' ale.LAST_UPDATED_BY,ale.CREATION_DATE,ale.CREATED_BY,'
|| ' ale.LAST_UPDATE_LOGIN,ale.IMPORT_LIST_HEADER_ID, '
|| ' ale.IMPORT_SUCCESSFUL_FLAG,ale.ENABLED_FLAG, '
|| ' ale.IMPORT_FAILURE_REASON,'
|| ' ale.RE_IMPORT_LAST_DONE_DATE,ale.DEDUPE_KEY '
|| ' FROM AMS_IMP_SOURCE_LINES ale , '
|| ' ams_imp_list_headers_all ail, '
|| ' ams_list_src_types alt '
||' WHERE alt.list_source_type = ' || ''''|| 'IMPORT' ||''''
||' and ail.list_source_type_id = alt.list_source_type_id '
||' and alt.source_type_code = '|| ''''||
upper(p_source_type_code) ||''''
||' and ale.import_list_header_id = ail.import_list_header_id ';
SELECT
list_header_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,request_id
,program_id
,program_application_id
,program_update_date
,view_application_id
,list_name
,list_used_by_id
,arc_list_used_by
,list_type
,status_code
,status_date
,generation_type
,repeat_exclude_type
,row_selection_type
,owner_user_id
,access_level
,enable_log_flag
,enable_word_replacement_flag
,enable_parallel_dml_flag
,dedupe_during_generation_flag
,generate_control_group_flag
,last_generation_success_flag
,forecasted_start_date
,forecasted_end_date
,actual_end_date
,sent_out_date
,dedupe_start_date
,last_dedupe_date
,last_deduped_by_user_id
,workflow_item_key
,no_of_rows_duplicates
,no_of_rows_min_requested
,no_of_rows_max_requested
,no_of_rows_in_list
,no_of_rows_in_ctrl_group
,no_of_rows_active
,no_of_rows_inactive
,no_of_rows_manually_entered
,no_of_rows_do_not_call
,no_of_rows_do_not_mail
,no_of_rows_random
,org_id
,main_gen_start_time
,main_gen_end_time
,main_random_nth_row_selection
,main_random_pct_row_selection
,ctrl_random_nth_row_selection
,ctrl_random_pct_row_selection
,repeat_source_list_header_id
,result_text
,keywords
,description
,list_priority
,assign_person_id
,list_source
,list_source_type
,list_online_flag
,random_list_id
,enabled_flag
,assigned_to
,query_id
,owner_person_id
,archived_by
,archived_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,timezone_id
,user_entered_start_time
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id;
IS SELECT
list_select_action_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,list_header_id
,order_number
,list_action_type
,incl_object_name
,arc_incl_object_from
,incl_object_id
,incl_object_wb_sheet
,incl_object_wb_owner
,incl_object_cell_code
,rank
,no_of_rows_available
,no_of_rows_requested
,no_of_rows_used
,distribution_pct
,result_text
,description
FROM ams_list_select_actions
WHERE list_header_id = p_list_header_id
ORDER BY order_number;
SELECT list_header_Id,
list_name
FROM ams_list_headers_all
WHERE repeat_source_list_header_id = p_source_list_id
AND list_header_id <> p_current_list_id
ORDER BY list_header_id;
g_listaction_rec.list_select_action_id := NULL;
SELECT COUNT(*)
INTO l_repeat_list_count
FROM ams_list_headers_all
WHERE repeat_source_list_Header_id = p_list_id;
Cursor C_ListEntry_Dets IS Select LIST_ENTRY_ID,LIST_HEADER_ID,
SOURCE_CODE,ARC_LIST_USED_BY_SOURCE,
SOURCE_CODE_FOR_ID,PIN_CODE,
LIST_ENTRY_SOURCE_SYSTEM_ID,
LIST_ENTRY_SOURCE_SYSTEM_TYPE,
VIEW_APPLICATION_ID,
CELL_CODE,CAMPAIGN_ID,
CHANNEL_SCHEDULE_ID,
EVENT_OFFER_ID,SUFFIX,
FIRST_NAME,LAST_NAME,
CUSTOMER_NAME,TITLE,
ADDRESS_LINE1,ADDRESS_LINE2,
CITY,STATE,ZIPCODE,COUNTRY,FAX,PHONE,EMAIL_ADDRESS
From ams_list_entries
Where ams_list_entries.pin_code = p_pincode;