[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT transaction_category_id
FROM pqh_transaction_categories
WHERE short_name = p_short_name;
SELECT table_route_id,
from_clause,
where_clause
FROM pqh_table_route
WHERE table_alias = p_alias;
SELECT upper(att.column_name) column_name
FROM pqh_attributes att
, pqh_special_attributes sat
, pqh_txn_category_attributes tca
WHERE att.attribute_id = tca.attribute_id
AND att.master_table_route_id = p_table_route_id
AND tca.transaction_category_id = g_purge_txn_catg_id
AND tca.txn_category_attribute_id = sat.txn_category_attribute_id
AND sat.attribute_type_cd = p_col_type_cd; --'PRIMARY_KEY';
PROCEDURE delete_wf_data(p_pk_value IN NUMBER) IS
l_proc varchar2(80) := g_package||'delete_wf_data';
l_select varchar2(2000);
PROCEDURE delete_process_log_data(p_pk_value IN Number ) IS
CURSOR csr_process_log_id(p_txn_value IN NUMBER,
p_short_name IN VARCHAR2) IS
SELECT process_log_id,object_version_number
FROM pqh_process_log
WHERE module_cd = UPPER(p_short_name)
START WITH process_log_id = (SELECT process_log_id
FROM pqh_process_log
WHERE module_cd = UPPER(p_short_name)
AND master_process_log_id IS NULL
AND txn_id = p_txn_value)
CONNECT BY master_process_log_id = PRIOR process_log_id
ORDER BY level DESC;
l_proc varchar2(80) := g_package||'delete_process_log_data';
pqh_process_log_api.delete_process_log
(p_validate => false
,p_process_log_id => i.process_log_id
,p_object_version_number => i.object_version_number
,p_effective_date => SYSDATE
);
l_select_stmt VARCHAR2(8000);
l_select varchar2(2000);
l_all_txn_rows_array.DELETE;
l_select_stmt :='select ' || ' ' || ' TO_CHAR(' || pk_col_name || ')';
hr_utility.set_location('select stme:' || l_select_stmt,1010);
(p_select_stmt => l_select_stmt,
p_from_clause => l_from_clause_txn,
p_where_clause => l_where_clause_out_txn,
p_total_columns => 1,--Since we are selecting only the primary key only
p_total_rows => l_tot_txn_rows,
p_all_txn_rows => l_all_txn_rows_array );
l_select := 'SELECT wf_transaction_category_id '||' FROM '||l_from_clause_txn||' WHERE '||pk_col_name||' = :1';
EXECUTE IMMEDIATE l_select INTO g_wf_txn_catg_id USING l_parent_pk_value;
call_delete_api(p_tab_route_id => l_master_tab_route_id,
p_pk_value => l_parent_pk_value,
p_from_clause_txn => l_from_clause_txn,
p_pk_col_name => pk_col_name);
SELECT column_type
FROM pqh_attributes
WHERE column_name = UPPER(p_column_name)
AND master_table_route_id = l_master_table_route_id;
SELECT column_type
FROM pqh_attributes
WHERE column_name = UPPER(p_column_name)
AND master_table_route_id is null ;
pqh_refresh_data.g_refresh_tab.DELETE;
l_select_stmt varchar2(8000) DEFAULT null;
SELECT
child_node_type
FROM
per_gen_hier_node_types
WHERE
parent_node_type = UPPER(p_alias_name) AND hierarchy_type = 'GENERIC_PURGE';
SELECT child_node_type
FROM per_gen_hier_node_types
WHERE hierarchy_type = 'GENERIC_PURGE'
START WITH parent_node_type = UPPER(p_alias_name)
CONNECT BY parent_node_type = PRIOR child_node_type;
l_all_child_rows_array.DELETE;
l_select_stmt :='select '|| c_pk_col_name ;
(p_select_stmt => l_select_stmt,
p_from_clause => l_from_clause_txn,
p_where_clause => l_where_clause_out_txn,
p_total_columns => 1,
p_total_rows => l_tot_txn_rows,
p_all_txn_rows => l_all_child_rows_array );
call_delete_api
(p_tab_route_id => l_tab_route_id,
p_pk_value => l_all_child_rows_array(i),
p_from_clause_txn => l_from_clause_txn,
p_pk_col_name => c_pk_col_name);
Procedure call_delete_api
(p_tab_route_id IN pqh_table_route.table_route_id%TYPE,
p_pk_value IN NUMBER,
p_from_clause_txn IN pqh_table_route.from_clause%TYPE,
p_pk_col_name IN pqh_attributes.column_name%TYPE) IS
-- p_errror_flag OUT BOOLEAN ) IS
--This Cursor will get the delete api to be called for the respective tables.
---------------------Cursor and Variable Declarations-------------------------------------
CURSOR
csr_delete_api_name( p_table_route_id IN pqh_table_route.table_route_id%TYPE) IS
SELECT copy_function_name
FROM pqh_copy_entity_functions
WHERE table_route_id = p_table_route_id;
SELECT
process_log_id from pqh_process_log
WHERE txn_id = p_txn_value AND module_cd = UPPER(l_short_name);
SELECT
object_version_number from pqh_process_log
WHERE
process_log_id = l_process_log_id;
l_proc varchar2(72) := g_package||'call_delete_api';
l_select_stmt varchar2(8000);
l_select_stmt := ' select object_version_number ' ;
(p_select_stmt => l_select_stmt,
p_from_clause => l_from_clause_txn,
p_where_clause => l_where_clause_out_txn,
p_total_columns => 1,
p_total_rows => l_ovn_rows,
p_all_txn_rows => l_ovn_value );
l_select_stmt := 'SELECT object_version_number FROM '||l_from_clause_txn||' WHERE '||l_pk_col_name||' = :1';
EXECUTE IMMEDIATE l_select_stmt INTO l_ovn USING l_pk_value ;
OPEN csr_delete_api_name(p_tab_route_id);
FETCH csr_delete_api_name INTO l_dummy_in;
CLOSE csr_delete_api_name;
delete_wf_data(p_pk_value => l_pk_value);
delete_process_log_data(p_pk_value => l_pk_value);
END call_delete_api;
SELECT upper(att.column_name) column_name
FROM pqh_attributes att
, pqh_special_attributes sat
, pqh_txn_category_attributes tca
WHERE att.attribute_id = tca.attribute_id
and att.master_table_route_id = p_table_route_id
and tca.transaction_category_id = g_purge_txn_catg_id
and tca.txn_category_attribute_id = sat.txn_category_attribute_id
and sat.attribute_type_cd = 'ERROR_KEY';
l_select varchar2(8000);
l_select := 'select ';
l_select := l_select || 'TO_CHAR('||lcol ||'),';
L_SELECT := RTRIM(L_SELECT,',');
(p_select_stmt => l_select,
p_from_clause => l_from_clause,
p_where_clause => l_where_clause,
p_total_columns => l_tot_columns,
p_total_rows => l_tot_rows,
p_all_txn_rows => l_txn_value );