[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'INSERT INTO fem_mp_process_ctl_t' ||
'(req_id, rule_id, slice_id, partition, data_slice, process_num, '||
'rows_processed, rows_loaded, rows_rejected, status, message)'||
' SELECT '||
p_req_id || ',' || p_rule_id || ',rownum +' || p_rownum ||
', ''' || p_part_name || ''', data_slice, 0, null, null, null, null, null' ||
' FROM ' ||
'(SELECT DISTINCT ' || p_data_slc || ' data_slice' ||
' FROM ' || p_data_table || v_part_clause ||
' WHERE ' || v_condition || ')';
SELECT NVL(MAX(slice_id), 0)
INTO p_rownum
FROM FEM_MP_PROCESS_CTL_T
WHERE req_id = p_req_id;
'INSERT INTO fem_mp_process_ctl_t ' ||
'(req_id,rule_id,' ||
'slice_id,partition,' ||
'data_slice,' ||
'process_num,rows_processed,rows_loaded,' ||
'rows_rejected,status,message) ' ||
'SELECT ' ||
p_req_id || ', ' || p_rule_id || ', ' ||
'rownum + ' || p_rownum || ', ''' || p_part_name || ''', ' ||
'minval||''{#}''||maxval' ||
', 0, null, null, ' ||
'null, null, null ' ||
'FROM ' ||
'(SELECT ' ||
'MIN(slice_column) minval, ' ||
'MAX(slice_column) maxval ' ||
'FROM ' ||
'(SELECT ' ||
p_slc_col || ' slice_column, ' ||
'NTILE(' || p_num_slices || ') ' ||
'OVER (ORDER BY ' || p_slc_col || ') tile ' ||
'FROM ' || p_data_table || v_part_clause ||
' WHERE ' || v_condition || ') ' ||
'GROUP BY tile)';
SELECT NVL(MAX(slice_id), 0)
INTO p_rownum
FROM FEM_MP_PROCESS_CTL_T
WHERE req_id = p_req_id;
INSERT INTO fem_mp_process_ctl_t
(req_id, rule_id, slice_id, partition, data_slice, process_num,
rows_processed, rows_loaded, rows_rejected, status, message)
VALUES
(p_req_id, p_rule_id, p_rownum, p_part_name, p_data_slc, 0,
null, null, null, null, null);
'SELECT ' ||
'MIN(slice_column) minval, ' ||
'MAX(slice_column) maxval ' ||
'FROM ' ||
'(SELECT ' ||
p_slc_col || ' slice_column, ' ||
'NTILE(' || p_num_slices || ') ' ||
'OVER (ORDER BY ' || p_slc_col || ') tile ' ||
'FROM ' || p_data_table || v_part_clause ||
' WHERE ' || v_condition || ') ' ||
'GROUP BY tile ' ||
'ORDER BY minval ASC';
'SELECT MIN(' || p_slc_col || ') ' ||
'FROM ' || p_data_table || v_part_clause ||
' WHERE ' || v_condition ||
' AND ' || p_slc_col || ' > :b_last_max';
SELECT column_name
FROM fem_mp_data_slice_cols
WHERE process_data_slices_cd = p_slc_code
ORDER BY process_data_slice_seq;
SELECT table_name,table_owner
INTO v_tab_name,v_tab_owner
FROM user_synonyms
WHERE synonym_name = p_data_table;
'SELECT table_name,table_owner'||
' FROM user_synonyms@'||p_source_db_link||
' WHERE synonym_name = :b_data_table';
DELETE FROM FEM_MP_PROCESS_CTL_T
WHERE req_id = p_req_id;
SELECT UPPER(column_name)
INTO v_slc_col
FROM fem_mp_data_slice_cols
WHERE process_data_slices_cd = p_slc_code
AND process_data_slice_seq = 1;
SELECT num_of_slices,rows_per_slice
INTO v_num_slices, v_rows_slice
FROM fem_mp_data_slices
WHERE process_data_slices_cd = p_slc_code;
SELECT data_type
INTO v_col_type
FROM all_tab_columns
WHERE owner = v_tab_owner
AND table_name = v_tab_name
AND column_name = v_slc_col;
'SELECT data_type'||
' FROM dba_tab_columns@'||p_source_db_link||
' WHERE owner = :b_tab_owner'||
' AND table_name = :b_tab_name'||
' AND column_name = :b_slc_col';
'SELECT partition_name'||
' FROM all_tab_partitions'||
' WHERE table_owner = '''||v_tab_owner||''''||
' AND table_name = '''||v_tab_name||''''||
' ORDER BY partition_position';
'SELECT partition_name'||
' FROM all_tab_partitions@'||p_source_db_link||
' WHERE table_owner = '''||v_tab_owner||''''||
' AND table_name = '''||v_tab_name||''''||
' ORDER BY partition_position';
'SELECT COUNT(*)'||
' FROM '||v_data_table||
' WHERE '||v_condition
INTO v_count;
'SELECT COUNT(*)'||
' FROM '||v_data_table||' PARTITION('||v_part_name||')'||
' WHERE '||v_condition
INTO v_count;
'SELECT COUNT(*)'||
' FROM '||v_data_table||' PARTITION('||v_part_name||')'||
' WHERE '||v_condition||
' AND ROWNUM = 1'
INTO v_count;
SELECT request_id
FROM fnd_concurrent_requests R,
fnd_concurrent_programs P
WHERE parent_request_id = v_req_id
AND R.concurrent_program_id = P.concurrent_program_id
AND P.concurrent_program_name = c_mp_sub_prg
AND P.application_id = c_mp_app_id
ORDER BY request_id;
SELECT object_type_code
INTO v_obj_type
FROM fem_object_catalog_b
WHERE object_id = p_rule_id;
SELECT step_name,
TO_NUMBER(mp_method_code)
INTO v_eng_step,
v_mp_method
FROM fem_mp_obj_step_methods
WHERE object_type_code = v_obj_type
AND step_name = p_eng_step;
SELECT 'ALL',
TO_NUMBER(mp_method_code)
INTO v_eng_step,
v_mp_method
FROM fem_mp_obj_step_methods
WHERE object_type_code = v_obj_type
AND step_name = 'ALL';
SELECT step_name,
process_data_slices_cd,
TO_NUMBER(data_slice_type_code),
process_partition_cd,
num_of_processes,
array_size_rows
INTO v_eng_step,
v_slc_code,
v_slc_type,
v_part_code,
v_num_procs,
v_fetch_limit
FROM fem_mp_process_options
WHERE object_type_code = v_obj_type
AND step_name = p_eng_step
AND object_id = p_rule_id;
SELECT 'ALL',
process_data_slices_cd,
TO_NUMBER(data_slice_type_code),
process_partition_cd,
num_of_processes,
array_size_rows
INTO v_eng_step,
v_slc_code,
v_slc_type,
v_part_code,
v_num_procs,
v_fetch_limit
FROM fem_mp_process_options
WHERE object_type_code = v_obj_type
AND step_name = 'ALL'
AND object_id = p_rule_id;
SELECT step_name,
process_data_slices_cd,
TO_NUMBER(data_slice_type_code),
process_partition_cd,
num_of_processes,
array_size_rows
INTO v_eng_step,
v_slc_code,
v_slc_type,
v_part_code,
v_num_procs,
v_fetch_limit
FROM fem_mp_process_options
WHERE object_type_code = v_obj_type
AND step_name = p_eng_step
AND object_id IS NULL;
SELECT 'ALL',
process_data_slices_cd,
TO_NUMBER(data_slice_type_code),
process_partition_cd,
num_of_processes,
array_size_rows
INTO v_eng_step,
v_slc_code,
v_slc_type,
v_part_code,
v_num_procs,
v_fetch_limit
FROM fem_mp_process_options P
WHERE object_type_code = v_obj_type
AND step_name = 'ALL'
AND object_id IS NULL;
DELETE FROM fem_mp_process_ctl_t T
WHERE rule_id = p_rule_id
AND (req_id IN (SELECT request_id
FROM fnd_concurrent_requests
WHERE phase_code = 'C')
OR NOT EXISTS (SELECT 1 FROM fnd_concurrent_requests
WHERE request_id = T.req_id));
DELETE FROM fem_mp_process_args_t T
WHERE rule_id = p_rule_id
AND (req_id IN (SELECT request_id
FROM fnd_concurrent_requests
WHERE phase_code = 'C')
OR NOT EXISTS (SELECT 1 FROM fnd_concurrent_requests
WHERE request_id = T.req_id));
DELETE FROM fem_mp_process_ctl_t
WHERE req_id = v_req_id;
SELECT MAX(REPLACE(data_slice,'{#}',''))
INTO v_max_slice
FROM fem_mp_process_ctl_t
WHERE req_id = v_req_id;
SELECT COUNT(*)
INTO v_num_slices
FROM fem_mp_process_ctl_t
WHERE req_id = v_req_id;
UPDATE fem_mp_process_ctl_t
SET req_id = v_req_id,
process_num = 0
WHERE req_id = p_failed_req_id
AND status IS NULL;
UPDATE fem_mp_process_ctl_t
SET process_num = 0,
rows_processed = null,
rows_loaded = null,
rows_rejected = null,
status = null,
message = null
WHERE req_id = v_req_id;
DELETE FROM fem_mp_process_args_t
WHERE req_id = v_req_id;
p_msg_text => 'Inserting into FEM_MP_PROCESS_ARGS_T...');
INSERT INTO fem_mp_process_args_t
(req_id,rule_id,eng_prg,eng_sql,slc_pred,arg_count,
arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8,
arg9,arg10,arg11,arg12,arg13,arg14,arg15,arg16,
arg17,arg18,arg19,arg20,arg21,arg22,arg23,arg24,
arg25,arg26,arg27,arg28,arg29,arg30,arg31,arg32,
arg33,arg34,arg35,arg36,arg37,arg38,arg39,arg40)
VALUES
(v_req_id,p_rule_id,v_eng_prg,
p_eng_sql,v_slc_pred,v_args_count,
p_arg1, p_arg2, p_arg3, p_arg4,
p_arg5, p_arg6, p_arg7, p_arg8,
p_arg9, p_arg10,p_arg11,p_arg12,
p_arg13,p_arg14,p_arg15,p_arg16,
p_arg17,p_arg18,p_arg19,p_arg20,
p_arg21,p_arg22,p_arg23,p_arg24,
p_arg25,p_arg26,p_arg27,p_arg28,
p_arg29,p_arg30,p_arg31,p_arg32,
p_arg33,p_arg34,p_arg35,p_arg36,
p_arg37,p_arg38,p_arg39,p_arg40);
SELECT rule_id,eng_prg,eng_sql,slc_pred,arg_count
INTO v_rule_id,v_eng_prg,v_eng_sql_param,v_slc_pred,v_args_count
FROM fem_mp_process_args_t
WHERE req_id = p_req_id;
SELECT
rule_id,eng_prg,eng_sql,slc_pred,arg_count,
arg1, arg2, arg3, arg4,
arg5, arg6, arg7, arg8,
arg9, arg10,arg11,arg12,
arg13,arg14,arg15,arg16,
arg17,arg18,arg19,arg20,
arg21,arg22,arg23,arg24,
arg25,arg26,arg27,arg28,
arg29,arg30,arg31,arg32,
arg33,arg34,arg35,arg36,
arg37,arg38,arg39,arg40
INTO
v_rule_id,v_eng_prg,v_eng_sql_param,v_slc_pred,v_args_count,
t_arg(1), t_arg(2), t_arg(3), t_arg(4),
t_arg(5), t_arg(6), t_arg(7), t_arg(8),
t_arg(9), t_arg(10),t_arg(11),t_arg(12),
t_arg(13),t_arg(14),t_arg(15),t_arg(16),
t_arg(17),t_arg(18),t_arg(19),t_arg(20),
t_arg(21),t_arg(22),t_arg(23),t_arg(24),
t_arg(25),t_arg(26),t_arg(27),t_arg(28),
t_arg(29),t_arg(30),t_arg(31),t_arg(32),
t_arg(33),t_arg(34),t_arg(35),t_arg(36),
t_arg(37),t_arg(38),t_arg(39),t_arg(40)
FROM fem_mp_process_args_t
WHERE req_id = p_req_id;
' UPDATE fem_mp_process_ctl_t'||
' SET rows_processed = x_rows_processed,'||
' rows_loaded = x_rows_loaded,'||
' rows_rejected = x_rows_rejected,'||
' status = x_slc_stat,'||
' message = x_slc_msg'||
' WHERE req_id = :b_req_id'||
' AND slice_id = :b_slc_id2 ;'||
SELECT status,message
INTO v_slc_stat,v_slc_msg
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND slice_id = v_slc_id;
UPDATE fem_mp_process_ctl_t
SET status = v_slc_stat,
message = v_slc_msg,
rows_processed = v_rows_processed
WHERE req_id = p_req_id
AND slice_id = v_slc_id;
SELECT MAX(status)
INTO v_sub_stat
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND process_num = p_proc_num;
SELECT COUNT(DISTINCT partition)
INTO v_part_count
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id;
SELECT slice_id, process_num
INTO v_slc_id, v_proc_num0
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND slice_id =
(SELECT MIN(slice_id)
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND process_num = 0)
FOR UPDATE;
UPDATE fem_mp_process_ctl_t
SET process_num = p_proc_num
WHERE req_id = p_req_id
AND slice_id = v_slc_id;
Select a partition using the following order of precedence:
1. Previously used partition:
If a partition has alredy been used, select it
again if there are still unprocessed slices
2. Next unprocessed partition
3. Least processed partition
------------------------------------------------------------*/
----------------------------
-- Previously used partition
----------------------------
IF (p_proc_num <= v_part_count)
THEN
FEM_ENGINES_PKG.TECH_MESSAGE
(p_severity => c_log_level_2,
p_module => v_block||'.Get_Slice{680}',
p_msg_text => 'Searching previously used partition');
SELECT MIN(partition)
INTO v_part_next
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND process_num = p_proc_num
AND partition IN
(SELECT partition
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND process_num = 0);
SELECT MIN(partition)
INTO v_part_next
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND partition NOT IN
(SELECT partition
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND process_num > 0);
SELECT MIN(partition)
INTO v_part_next
FROM
(SELECT partition, count(*) stat_0
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND process_num = 0
GROUP BY partition)
WHERE stat_0 =
(SELECT max(stat_0) FROM
(SELECT count(*) stat_0
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND process_num = 0
GROUP BY partition));
SELECT slice_id, process_num
INTO v_slc_id, v_proc_num0
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND slice_id =
(SELECT MIN(slice_id)
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND process_num = 0
AND partition = v_part_next)
FOR UPDATE;
UPDATE fem_mp_process_ctl_t
SET process_num = p_proc_num
WHERE req_id = p_req_id
AND slice_id = v_slc_id;
SELECT data_slice
INTO v_data_slc
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND slice_id = v_slc_id
AND process_num = p_proc_num;
UPDATE fem_mp_process_ctl_t
SET rows_processed = p_rows_processed,
rows_loaded = p_rows_loaded,
rows_rejected = p_rows_rejected,
status = p_status,
message = p_message
WHERE req_id = p_req_id
AND slice_id = p_slc_id;
SELECT DISTINCT(message)
BULK COLLECT INTO v_msg_list
FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id
AND status IN (1,2);
Delete Data Slices
=============================
**************************************************************************
**************************************************************************/
PROCEDURE Delete_Data_Slices(
p_req_id IN NUMBER)
IS
BEGIN
DELETE FROM fem_mp_process_ctl_t
WHERE req_id = p_req_id;
DELETE FROM fem_mp_process_args_t
WHERE req_id = p_req_id;
END Delete_Data_Slices;