[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_transaction_history (
p_transaction_history_id in number,
p_creator_person_id in number,
p_assignment_id in number,
p_selected_person_id in number,
p_process_name in varchar2,
p_item_type in varchar2,
p_item_key in varchar2,
p_function_id in number,
p_rptg_grp_id in number,
p_plan_id in number,
p_created_by in number,
p_creation_date in date,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number) is
begin
insert into pqh_ss_transaction_history (
transaction_history_id ,
creator_person_id ,
assignment_id ,
selected_person_id ,
process_name ,
item_type ,
item_key ,
function_id ,
rptg_grp_id ,
plan_id ,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login )
values
(p_transaction_history_id ,
p_creator_person_id ,
p_assignment_id ,
p_selected_person_id ,
p_process_name ,
p_item_type ,
p_item_key ,
p_function_id ,
p_rptg_grp_id ,
p_plan_id ,
p_created_by ,
p_creation_date ,
p_last_update_date ,
p_last_updated_by ,
p_last_update_login );
end insert_transaction_history;
procedure insert_approval_history (
p_approval_history_id in number,
p_transaction_history_id in number,
p_transaction_effective_date in date,
p_action in varchar2,
p_user_name in varchar2,
p_transaction_item_type in varchar2,
p_transaction_item_key in varchar2,
p_created_by in number,
p_creation_date in date,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number,
p_orig_system in varchar2 default null,
p_orig_system_id in number default null) is
begin
insert into pqh_ss_approval_history (
approval_history_id ,
transaction_history_id ,
transaction_effective_date ,
action ,
user_name ,
orig_system ,
orig_system_id ,
transaction_item_type ,
transaction_item_key ,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login )
values
( p_approval_history_id ,
p_transaction_history_id ,
p_transaction_effective_date ,
p_action ,
p_user_name ,
p_orig_system ,
p_orig_system_id ,
p_transaction_item_type ,
p_transaction_item_key ,
p_created_by ,
p_creation_date ,
p_last_update_date ,
p_last_updated_by ,
p_last_update_login );
end insert_approval_history;
procedure insert_step_history (
p_step_history_id in number,
p_approval_history_id in number,
p_transaction_history_id in number,
p_api_name in varchar2,
p_api_display_name in varchar2,
p_processing_order in varchar2,
p_item_type in varchar2,
p_item_key in varchar2,
p_activity_id in number,
p_created_by in number,
p_creation_date in date,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number) is
begin
insert into pqh_ss_step_history
(step_history_id ,
approval_history_id ,
transaction_history_id ,
api_name ,
api_display_name ,
processing_order ,
item_type ,
item_key ,
activity_id ,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login )
values (
p_step_history_id ,
p_approval_history_id ,
p_transaction_history_id ,
p_api_name ,
p_api_display_name ,
p_processing_order ,
p_item_type ,
p_item_key ,
p_activity_id ,
p_created_by ,
p_creation_date ,
p_last_update_date ,
p_last_updated_by ,
p_last_update_login );
end insert_step_history;
Procedure insert_value_history (
p_transaction_value_id in number,
p_step_history_id in number,
p_approval_history_id in number,
p_datatype in varchar2,
p_name in varchar2,
p_value in varchar2,
p_created_by in number,
p_creation_date in date,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number) is
begin
insert into pqh_ss_value_history
(transaction_value_id ,
step_history_id ,
approval_history_id ,
datatype ,
name ,
value ,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login )
values (
p_transaction_value_id ,
p_step_history_id ,
p_approval_history_id ,
p_datatype ,
p_name ,
p_value ,
p_created_by ,
p_creation_date ,
p_last_update_date ,
p_last_updated_by ,
p_last_update_login );
end insert_value_history;
PROCEDURE transfer_delete_to_history (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2 ) IS
l_txnItemType VARCHAR2(10);
,p_action => 'DELETED' );
select *
from hr_api_transactions
where transaction_id = c_txn_id;
select null
from pqh_ss_transaction_history
where transaction_history_id = c_txn_id ;
select nvl(max(approval_history_id),0)+1 approval_history_id
from pqh_ss_approval_history
where transaction_history_id = c_txn_id
and approval_history_id > 0 ;
select *
from hr_api_transaction_steps s
where transaction_id = c_txn_id
and exists (select null
from hr_api_transaction_values v
where v.transaction_step_id = s.transaction_step_id );
select transaction_value_id ,
datatype ,
name ,
decode( datatype, 'VARCHAR2', varchar2_value,
'DATE' , fnd_date.date_to_canonical(date_value),
'NUMBER' , to_char(number_value) , '' ) value ,
decode( datatype, 'VARCHAR2', original_varchar2_value,
'DATE' , fnd_date.date_to_canonical(original_date_value),
'NUMBER' , to_char(original_number_value) , '' ) original_value ,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login
from hr_api_transaction_values
where transaction_step_id = c_step_id;
elsif(p_action='DELETED') then
hr_trans_history_api.archive_delete(p_transactionId,
null,
p_username,
wf_engine.getitemattrtext(p_ItemType,
p_itemKey,
'WF_NOTE'));
procedure update_approval_history (
p_approval_history_id in number,
p_transaction_history_id in number,
p_transaction_effective_date in date,
p_action in varchar2,
p_user_name in varchar2,
p_transaction_item_type in varchar2,
p_transaction_item_key in varchar2,
p_created_by in number,
p_creation_date in date,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number,
p_orig_system in varchar2 default null,
p_orig_system_id in number default null) is
begin
update pqh_ss_approval_history
set transaction_effective_date = p_transaction_effective_date ,
user_name = p_user_name,
orig_system = p_orig_system,
orig_system_id = p_orig_system_id
where transaction_history_id = p_transaction_history_id
and approval_history_id = p_approval_history_id;
insert_approval_history (
p_approval_history_id => p_approval_history_id,
p_transaction_history_id => p_transaction_history_id,
p_transaction_effective_date => p_transaction_effective_date,
p_action => p_action,
p_user_name => p_user_name,
p_transaction_item_type => p_transaction_item_type,
p_transaction_item_key => p_transaction_item_key,
p_created_by => p_created_by,
p_creation_date => p_creation_date,
p_last_update_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_orig_system => p_orig_system,
p_orig_system_id => p_orig_system_id);
end update_approval_history;
procedure update_step_history (
p_step_history_id in number,
p_approval_history_id in number,
p_transaction_history_id in number,
p_api_name in varchar2,
p_api_display_name in varchar2,
p_processing_order in varchar2,
p_item_type in varchar2,
p_item_key in varchar2,
p_activity_id in varchar2,
p_created_by in number,
p_creation_date in date,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number) is
l_var varchar2(20);
select null
into l_var
from pqh_ss_step_history
where transaction_history_id = p_transaction_history_id
and approval_history_id = p_approval_history_id
and step_history_id = p_step_history_id;
insert_step_history
(p_step_history_id => p_step_history_id,
p_approval_history_id => p_approval_history_id,
p_transaction_history_id => p_transaction_history_id,
p_api_name => p_api_name,
p_api_display_name => p_api_display_name,
p_processing_order => p_processing_order,
p_item_type => p_item_type,
p_item_key => p_item_key,
p_activity_id => p_activity_id,
p_created_by => p_created_by,
p_creation_date => p_creation_date,
p_last_update_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login);
end update_step_history;
Procedure update_value_history (
p_transaction_value_id in number,
p_step_history_id in number,
p_approval_history_id in number,
p_datatype in varchar2,
p_name in varchar2,
p_value in varchar2,
p_created_by in number,
p_creation_date in date,
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number) is
begin
update pqh_ss_value_history
set value = p_value
where transaction_value_id = p_transaction_value_id
and step_history_id = p_step_history_id
and approval_history_id = p_approval_history_id
and name = p_name;
insert_value_history (
p_transaction_value_id => p_transaction_value_id,
p_step_history_id => p_step_history_id,
p_approval_history_id => p_approval_history_id,
p_datatype => p_datatype,
p_name => p_name,
p_value => p_value,
p_created_by => p_created_by,
p_creation_date => p_creation_date,
p_last_update_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login);
end update_value_history;
select null from pqh_ss_approval_history
where transaction_history_id = v_transactionId
and approval_history_id = 0;
select *
from hr_api_transactions
where transaction_id = c_txn_id;
select null
from pqh_ss_transaction_history
where transaction_history_id = c_txn_id ;
select *
from hr_api_transaction_steps s
where transaction_id = c_txn_id
and exists (select null
from hr_api_transaction_values v
where v.transaction_step_id = s.transaction_step_id );
select transaction_value_id ,
datatype ,
name ,
decode( datatype, 'VARCHAR2', varchar2_value,
'DATE' , fnd_date.date_to_canonical(date_value),
'NUMBER' , to_char(number_value) , '' ) value ,
decode( datatype, 'VARCHAR2', original_varchar2_value,
'DATE' , fnd_date.date_to_canonical(original_date_value),
'NUMBER' , to_char(original_number_value) , '' ) original_value ,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login
from hr_api_transaction_values
where transaction_step_id = c_step_id;
insert_transaction_history (
p_transaction_history_id => tn.transaction_id,
p_creator_person_id => tn.creator_person_id,
p_assignment_id => tn.assignment_id,
p_selected_person_id => tn.selected_person_id,
p_process_name => tn.process_name,
p_item_type => p_itemType,
p_item_key => p_itemKey,
p_function_id => tn.function_id,
p_rptg_grp_id => tn.rptg_grp_id,
p_plan_id => tn.plan_id,
p_created_by => tn.created_by,
p_creation_date => tn.creation_date,
p_last_update_date => tn.last_update_date,
p_last_updated_by => tn.last_updated_by,
p_last_update_login => tn.last_update_login);
insert_approval_history (
p_approval_history_id => 0,
p_transaction_history_id => l_transactionId,
p_transaction_effective_date => tn.transaction_effective_date,
p_action => 'LATEST_ORIGINAL_VALUE',
p_user_name => l_username,
p_orig_system => p_orig_system,
p_orig_system_id => p_orig_system_id,
p_transaction_item_type => tn.item_type,
p_transaction_item_key => tn.item_key,
p_created_by => tn.created_by,
p_creation_date => tn.creation_date,
p_last_update_date => tn.last_update_date,
p_last_updated_by => tn.last_updated_by,
p_last_update_login => tn.last_update_login);
insert_step_history
(p_step_history_id => stp.transaction_step_id,
p_approval_history_id => 0 ,
p_transaction_history_id => l_transactionId,
p_api_name => stp.api_name,
p_api_display_name => stp.api_display_name,
p_processing_order => stp.processing_order,
p_item_type => stp.item_type,
p_item_key => stp.item_key,
p_activity_id => stp.activity_id,
p_created_by => stp.created_by,
p_creation_date => stp.creation_date,
p_last_update_date => stp.last_update_date,
p_last_updated_by => stp.last_updated_by,
p_last_update_login => stp.last_update_login);
insert_value_history (
p_transaction_value_id => vlue.transaction_value_id,
p_step_history_id => stp.transaction_step_id,
p_approval_history_id => 0 ,
p_datatype => vlue.datatype,
p_name => vlue.name,
p_value => vlue.original_value,
p_created_by => vlue.created_by,
p_creation_date => vlue.creation_date,
p_last_update_date => vlue.last_update_date,
p_last_updated_by => vlue.last_updated_by,
p_last_update_login => vlue.last_update_login);
update_approval_history (
p_approval_history_id => 0,
p_transaction_history_id => l_transactionId,
p_transaction_effective_date => tn.transaction_effective_date,
p_action => 'LATEST_ORIGINAL_VALUE',
p_user_name => l_username,
p_orig_system => p_orig_system,
p_orig_system_id => p_orig_system_id,
p_transaction_item_type => tn.item_type,
p_transaction_item_key => tn.item_key,
p_created_by => tn.created_by,
p_creation_date => tn.creation_date,
p_last_update_date => tn.last_update_date,
p_last_updated_by => tn.last_updated_by,
p_last_update_login => tn.last_update_login);
update_step_history
(p_step_history_id => stp.transaction_step_id,
p_approval_history_id => 0 ,
p_transaction_history_id => l_transactionId,
p_api_name => stp.api_name,
p_api_display_name => stp.api_display_name,
p_processing_order => stp.processing_order,
p_item_type => stp.item_type,
p_item_key => stp.item_key,
p_activity_id => stp.activity_id,
p_created_by => stp.created_by,
p_creation_date => stp.creation_date,
p_last_update_date => stp.last_update_date,
p_last_updated_by => stp.last_updated_by,
p_last_update_login => stp.last_update_login);
update_value_history (
p_transaction_value_id => vlue.transaction_value_id,
p_step_history_id => stp.transaction_step_id,
p_approval_history_id => 0 ,
p_datatype => vlue.datatype,
p_name => vlue.name,
p_value => vlue.original_value,
p_created_by => vlue.created_by,
p_creation_date => vlue.creation_date,
p_last_update_date => vlue.last_update_date,
p_last_updated_by => vlue.last_updated_by,
p_last_update_login => vlue.last_update_login);
SELECT *
FROM hr_api_transaction_steps
WHERE transaction_id = c_txn_id
AND ( (c_api_name is null AND api_name in (
'HR_PAY_RATE_SS.PROCESS_API'
,'HR_SUPERVISOR_SS.PROCESS_API'
,'HR_PROCESS_SIT_SS.PROCESS_API'
,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
,'HR_PROCESS_CONTACT_SS.PROCESS_API'
,'HR_PROCESS_PERSON_SS.PROCESS_API'
,'HR_COMP_PROFILE_SS.PROCESS_API'))
OR api_name = c_api_name);
select *
from hr_api_transactions
where transaction_id = c_txn_id;
select null
from pqh_ss_transaction_history
where transaction_history_id = c_txn_id ;
delete from pqh_ss_value_history
where approval_history_id = -1
and step_history_id in (
select step_history_id from pqh_ss_step_history
where transaction_history_id = p_txnId
and approval_history_id = -1);
hr_utility.set_location('delete value history done', 45);
delete from pqh_ss_step_history
where transaction_history_id = p_txnId
and approval_history_id = -1;
hr_utility.set_location('Calling insert', 15);
insert_transaction_history (
p_transaction_history_id => tn.transaction_id,
p_creator_person_id => tn.creator_person_id,
p_assignment_id => tn.assignment_id,
p_selected_person_id => tn.selected_person_id,
p_process_name => tn.process_name,
p_item_type => tn.item_Type,
p_item_key => tn.item_Key,
p_function_id => tn.function_id,
p_rptg_grp_id => tn.rptg_grp_id,
p_plan_id => tn.plan_id,
p_created_by => tn.created_by,
p_creation_date => tn.creation_date,
p_last_update_date => tn.last_update_date,
p_last_updated_by => tn.last_updated_by,
p_last_update_login => tn.last_update_login);
hr_utility.set_location('Insert history complete', 20);
hr_utility.set_location('delete step history done', 55);
insert_step_history
(p_step_history_id => r_supStep.transaction_step_id,
p_approval_history_id => -1,
p_transaction_history_id => p_txnId,
p_api_name => r_supStep.api_name,
p_api_display_name => r_supStep.api_display_name,
p_processing_order => r_supStep.processing_order,
p_item_type => r_supStep.item_type,
p_item_key => r_supStep.item_key,
p_activity_id => r_supStep.activity_id,
p_created_by => r_supStep.created_by,
p_creation_date => r_supStep.creation_date,
p_last_update_date => r_supStep.last_update_date,
p_last_updated_by => r_supStep.last_updated_by,
p_last_update_login => r_supStep.last_update_login);
hr_utility.set_location('insert step history done', 65);
insert into pqh_ss_value_history (
transaction_value_id ,
step_history_id ,
approval_history_id ,
datatype ,
name ,
value ,
original_value ,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login )
select transaction_value_id ,
transaction_step_id ,
-1 ,
datatype ,
name ,
decode( datatype, 'VARCHAR2', varchar2_value,
'DATE' , fnd_date.date_to_canonical(date_value),
'NUMBER' , number_value , '' ) value ,
decode( datatype, 'VARCHAR2', original_varchar2_value,
'DATE' , fnd_date.date_to_canonical(original_date_value),
'NUMBER' , original_number_value , '' ) original_value ,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login
from hr_api_transaction_values
where transaction_step_id = r_supStep.transaction_step_id ;
SELECT *
FROM hr_api_transaction_steps
WHERE transaction_id = c_txn_id
AND ( (c_api_name is null AND api_name in (
'HR_SUPERVISOR_SS.PROCESS_API'
,'HR_PROCESS_SIT_SS.PROCESS_API'
,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
,'HR_PROCESS_CONTACT_SS.PROCESS_API'
,'HR_PROCESS_PERSON_SS.PROCESS_API'
,'HR_COMP_PROFILE_SS.PROCESS_API'))
OR api_name = c_api_name);
SELECT *
FROM pqh_ss_step_history
WHERE transaction_history_id = c_txn_id
AND approval_history_id = -1
AND api_name in ('HR_SUPERVISOR_SS.PROCESS_API'
,'HR_PROCESS_SIT_SS.PROCESS_API'
,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
,'HR_PROCESS_CONTACT_SS.PROCESS_API'
,'HR_PROCESS_PERSON_SS.PROCESS_API'
,'HR_COMP_PROFILE_SS.PROCESS_API'
)
order by api_name;
delete from hr_api_transaction_values v
where v.transaction_step_id in (select s.transaction_step_id from hr_api_transaction_steps s
where s.transaction_id = p_txnId
and s.api_name not in (select h.api_name from pqh_ss_step_history h
where transaction_history_id = p_txnId
and approval_history_id = -1 )
and s.api_name in (
'HR_PAY_RATE_SS.PROCESS_API'
,'HR_SUPERVISOR_SS.PROCESS_API'
,'HR_PROCESS_SIT_SS.PROCESS_API'
,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
,'HR_PROCESS_CONTACT_SS.PROCESS_API'
,'HR_PROCESS_PERSON_SS.PROCESS_API'
,'HR_COMP_PROFILE_SS.PROCESS_API' ));
delete from hr_api_transaction_steps s
where s.transaction_id = p_txnId
and s.api_name not in (select h.api_name from pqh_ss_step_history h
where transaction_history_id = p_txnId
and approval_history_id = -1 )
and s.api_name in (
'HR_PAY_RATE_SS.PROCESS_API'
,'HR_SUPERVISOR_SS.PROCESS_API'
,'HR_PROCESS_SIT_SS.PROCESS_API'
,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
,'HR_PROCESS_CONTACT_SS.PROCESS_API'
,'HR_PROCESS_PERSON_SS.PROCESS_API'
,'HR_COMP_PROFILE_SS.PROCESS_API' );
delete from hr_api_transaction_values
where transaction_step_id = r_supStep.transaction_step_id;
delete from hr_api_transaction_steps
where transaction_step_id = r_supStep.transaction_step_id;
select hr_api_transaction_steps_s.nextval
into l_step_id
from dual;
insert into hr_api_transaction_steps(
transaction_step_id ,
transaction_id ,
api_name ,
api_display_name ,
processing_order ,
item_type ,
item_key ,
activity_id ,
creator_person_id ,
object_version_number ,
created_by ,
creation_date ,
last_update_date ,
last_updated_by ,
last_update_login )
values (
l_step_id ,
r_supStep_h.transaction_history_id ,
r_supStep_h.api_name ,
r_supStep_h.api_display_name ,
r_supStep_h.processing_order ,
r_supStep_h.item_type ,
r_supStep_h.item_key ,
r_supStep_h.activity_id ,
0,0,
r_supStep_h.created_by ,
r_supStep_h.creation_date ,
r_supStep_h.last_update_date ,
r_supStep_h.last_updated_by ,
r_supStep_h.last_update_login );
delete from hr_api_transaction_values
where transaction_step_id = r_supStep.transaction_step_id;
insert into hr_api_transaction_values (
transaction_value_id,
transaction_step_id,
datatype,
name,
varchar2_value,
number_value,
date_value,
original_varchar2_value,
original_number_value,
original_date_value,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login )
select hr_api_transaction_values_s.nextval ,
l_step_id, -- nvl( r_supStep.transaction_step_id, step_history_id) step_history_id,
datatype,
name,
decode(datatype,'VARCHAR2',value),
decode(datatype,'NUMBER',to_number(value)),
decode(datatype,'DATE',fnd_date.canonical_to_date(value)),
decode(datatype,'VARCHAR2',original_value),
decode(datatype,'NUMBER',to_number(original_value)),
decode(datatype,'DATE',fnd_date.canonical_to_date(original_value)),
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
from pqh_ss_value_history vh
where vh.approval_history_id = -1
and vh.step_history_id = r_supStep_h.step_history_id;
delete from hr_api_transaction_values
where transaction_step_id = r_supStep.transaction_step_id;
delete from hr_api_transaction_steps
where transaction_step_id = r_supStep.transaction_step_id;