The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_values_table IN hr_du_do_datapump.R_INSERT_STATEMENT_TYPE
,p_upload_id IN NUMBER
,p_batch_id IN NUMBER
,p_api_module_id IN NUMBER
,p_process_order IN NUMBER
,p_upload_line_id IN NUMBER
,p_api_name IN VARCHAR2
,p_pump_batch_line_id IN NUMBER)
IS
e_fatal_error EXCEPTION;
l_insert_statement VARCHAR2(32767);
SELECT PVAl001
FROM hr_du_upload_lines
WHERE UPLOAD_LINE_ID = p_upload_line_id;
hr_du_di_insert.g_current_delimiter := ',';
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_parent_line_id);
l_number_refs := hr_du_di_insert.WORDS_ON_LINE(
p_values_table.r_pval_parent_line_id);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_parent_line_id );
l_pval_parent_line_id := hr_du_di_insert.Return_Word(
p_values_table.r_pval_parent_line_id , j);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_parent_api_module_number);
l_parent_api_module_number := hr_du_di_insert.Return_Word(
p_values_table.r_parent_api_module_number , j);
l_insert_statement := 'insert into HRDPV_' || p_api_name || '(' ||
'BATCH_ID, BATCH_LINE_ID, '||
'API_MODULE_ID, LINE_STATUS, '||
'USER_SEQUENCE, LINK_VALUE, '||
l_foreign_user_key || ',';
l_insert_statement := l_insert_statement || 'p_assignment_user_key, ' ;
l_insert_statement := l_insert_statement || p_values_table.r_insert_string;
l_insert_statement := l_insert_statement || ')';
l_insert_statement := l_insert_statement || 'select ' ||
p_batch_id || ',' || p_pump_batch_line_id ||','||
p_api_module_id || ', ''U'',' ||
p_process_order || ',' || '1' || ',' ||
'''' ||l_parent_user_key || ''',' ||
'''' ||l_parent_user_key || ':ASG' || ''',';
l_insert_statement := l_insert_statement || p_values_table.r_PVAL_string;
l_insert_statement := l_insert_statement || ' FROM HR_DU_UPLOAD_LINES ' ||
'WHERE UPLOAD_LINE_ID = ' || p_upload_line_id;
hr_du_utility.message('INFO', 'l_insert_statement - ' || l_insert_statement,35);
hr_du_utility.dynamic_sql(l_insert_statement);
p_values_table IN hr_du_do_datapump.R_INSERT_STATEMENT_TYPE
,p_upload_id IN NUMBER
,p_batch_id IN NUMBER
,p_api_module_id IN NUMBER
,p_process_order IN NUMBER
,p_upload_line_id IN NUMBER
,p_api_name IN VARCHAR2
,p_pump_batch_line_id IN NUMBER)
IS
e_fatal_error EXCEPTION;
l_insert_statement VARCHAR2(32767);
SELECT PVAl001
FROM hr_du_upload_lines
WHERE UPLOAD_LINE_ID = p_upload_line_id;
hr_du_di_insert.g_current_delimiter := ',';
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_parent_line_id);
l_number_refs := hr_du_di_insert.WORDS_ON_LINE(
p_values_table.r_pval_parent_line_id);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_parent_line_id);
l_pval_parent_line_id := hr_du_di_insert.Return_Word(
p_values_table.r_pval_parent_line_id , j);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_parent_api_module_number);
l_parent_api_module_number := hr_du_di_insert.Return_Word(
p_values_table.r_parent_api_module_number, j);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_api_module_number);
l_pval_api_module_number := hr_du_di_insert.Return_Word(
p_values_table.r_pval_api_module_number , j);
l_insert_statement := 'insert into HRDPV_' || p_api_name || '(' ||
'BATCH_ID, BATCH_LINE_ID, '||
'API_MODULE_ID, LINE_STATUS, '||
'USER_SEQUENCE, LINK_VALUE, '||
l_foreign_user_key || ',';
l_insert_statement := l_insert_statement || l_foreign_user_key_2 || ',';
l_insert_statement := l_insert_statement || p_values_table.r_insert_string;
l_insert_statement := l_insert_statement || ')';
l_insert_statement := l_insert_statement || 'select ' ||
p_batch_id || ',' || p_pump_batch_line_id ||','||
p_api_module_id || ', ''U'',' ||
p_process_order || ',' || '1' || ',' ||
'''' ||l_parent_user_key || ''',';
l_insert_statement := l_insert_statement || '''' || l_parent_user_key_2 || ''',';
l_insert_statement := l_insert_statement || p_values_table.r_PVAL_string;
l_insert_statement := l_insert_statement || ' FROM HR_DU_UPLOAD_LINES ' ||
'WHERE UPLOAD_LINE_ID = ' || p_upload_line_id;
hr_du_utility.message('INFO', 'l_insert_statement - ' || l_insert_statement,35);
hr_du_utility.dynamic_sql(l_insert_statement);
PROCEDURE UPDATE_EMP_ASG_CRITERIA(
p_values_table IN hr_du_do_datapump.R_INSERT_STATEMENT_TYPE
,p_upload_id IN NUMBER
,p_batch_id IN NUMBER
,p_api_module_id IN NUMBER
,p_process_order IN NUMBER
,p_upload_line_id IN NUMBER
,p_api_name IN VARCHAR2
,p_pump_batch_line_id IN NUMBER)
IS
e_fatal_error EXCEPTION;
l_insert_statement VARCHAR2(32767);
SELECT PVAl001
FROM hr_du_upload_lines
WHERE UPLOAD_LINE_ID = p_upload_line_id;
'entry:hr_du_do_entities.update_emp_asg_criteria', 5);
hr_du_di_insert.g_current_delimiter := ',';
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_parent_line_id);
l_number_refs := hr_du_di_insert.WORDS_ON_LINE(
p_values_table.r_pval_parent_line_id);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_parent_line_id);
l_pval_parent_line_id :=hr_du_di_insert.Return_Word(
p_values_table.r_pval_parent_line_id , j);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_parent_api_module_number);
l_parent_api_module_number := hr_du_di_insert.Return_Word(
p_values_table.r_parent_api_module_number, j);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_api_module_number);
l_pval_api_module_number := hr_du_di_insert.Return_Word(
p_values_table.r_pval_api_module_number , j);
l_insert_statement := 'insert into HRDPV_' || p_api_name || '(' ||
'BATCH_ID, BATCH_LINE_ID, '||
'API_MODULE_ID, LINE_STATUS, '||
'USER_SEQUENCE, LINK_VALUE, '||
l_foreign_user_key || ',';
l_insert_statement := l_insert_statement || p_values_table.r_insert_string;
l_insert_statement := l_insert_statement || ')';
l_insert_statement := l_insert_statement || 'select ' ||
p_batch_id || ',' || p_pump_batch_line_id || ',' ||
p_api_module_id || ', ''U'',' ||
p_process_order || ',' || '1' || ',' ||
'''' ||l_parent_user_key_2 || ':ASG' || ''',';
l_insert_statement := l_insert_statement || p_values_table.r_PVAL_string;
l_insert_statement := l_insert_statement || ' FROM HR_DU_UPLOAD_LINES ' ||
'WHERE UPLOAD_LINE_ID = ' || p_upload_line_id;
hr_du_utility.message('INFO', 'l_insert_statement - ' || l_insert_statement,35);
hr_du_utility.dynamic_sql(l_insert_statement);
hr_du_utility.message('ROUT','exit:hr_du_do_entities.update_emp_asg_criteria', 100);
hr_du_utility.error(SQLCODE,'hr_du_do_entities.update_emp_asg_criteria'
,l_fatal_error_message, 'R');
hr_du_utility.error(SQLCODE, 'hr_du_do_entities.update_emp_asg_criteria',
'(none)', 'R');
END UPDATE_EMP_ASG_CRITERIA;
p_values_table IN hr_du_do_datapump.R_INSERT_STATEMENT_TYPE
,p_upload_id IN NUMBER
,p_batch_id IN NUMBER
,p_api_module_id IN NUMBER
,p_process_order IN NUMBER
,p_upload_line_id IN NUMBER
,p_api_name IN VARCHAR2
,p_pump_batch_line_id IN NUMBER)
IS
e_fatal_error EXCEPTION;
l_insert_statement VARCHAR2(32767);
SELECT PVAl001
FROM hr_du_upload_lines
WHERE UPLOAD_LINE_ID = p_upload_line_id;
hr_du_di_insert.g_current_delimiter := ',';
hr_du_di_insert.g_delimiter_count := 0;
l_number_refs := hr_du_di_insert.WORDS_ON_LINE(
p_values_table.r_pval_parent_line_id);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_parent_line_id);
l_pval_parent_line_id := hr_du_di_insert.Return_Word(
p_values_table.r_pval_parent_line_id , j);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_parent_api_module_number);
l_parent_api_module_number := hr_du_di_insert.Return_Word(
p_values_table.r_parent_api_module_number, j);
hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
p_values_table.r_pval_api_module_number);
l_pval_api_module_number := hr_du_di_insert.Return_Word(
p_values_table.r_pval_api_module_number , j);
l_insert_statement := 'insert into HRDPV_' || p_api_name || '(' ||
'BATCH_ID, BATCH_LINE_ID, '||
'API_MODULE_ID, LINE_STATUS, '||
'USER_SEQUENCE, LINK_VALUE, ';
l_insert_statement := l_insert_statement || l_foreign_user_key_2 || ',';
l_insert_statement := l_insert_statement || p_values_table.r_insert_string;
l_insert_statement := l_insert_statement || ')';
l_insert_statement := l_insert_statement || 'select ' ||
p_batch_id || ',' || p_pump_batch_line_id ||','||
p_api_module_id || ', ''U'',' ||
p_process_order || ',' || '1' || ',';
l_insert_statement := l_insert_statement || '''' || l_parent_user_key_2 || ''',';
l_insert_statement := l_insert_statement || p_values_table.r_PVAL_string;
l_insert_statement := l_insert_statement || ' FROM HR_DU_UPLOAD_LINES ' ||
'WHERE UPLOAD_LINE_ID = ' || p_upload_line_id;
hr_du_utility.message('INFO', 'l_insert_statement - ' || l_insert_statement,35);
hr_du_utility.dynamic_sql(l_insert_statement);