[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,LAST_UPDATE_ROLE VarChar320TblType
,PARENT_TRANSACTION_ID NumberTblType
,RELAUNCH_FUNCTION VarChar30TblType
,CREATED_BY NumberTblType
,CREATION_DATE DateTblType
,LAST_UPDATE_DATE DateTblType
,LAST_UPDATED_BY NumberTblType
,LAST_UPDATE_LOGIN NumberTblType
);
,last_update_date DateTblType
,last_updated_by NumberTblType
,last_update_login NumberTblType
);
,LAST_UPDATE_DATE DateTblType
,LAST_UPDATED_BY NumberTblType
,LAST_UPDATE_LOGIN NumberTblType
,ITEM_TYPE VarChar08TblType
,ITEM_KEY VarChar240TblType
,ACTIVITY_ID NumberTblType
,OBJECT_TYPE VarChar30TblType
,OBJECT_NAME VarChar150TblType
,OBJECT_IDENTIFIER VarChar240TblType
,OBJECT_STATE VarChar30TblType
,PK1 VarChar240TblType
,PK2 VarChar240TblType
,PK3 VarChar240TblType
,PK4 VarChar240TblType
,PK5 VarChar240TblType
,INFORMATION_CATEGORY VarChar30TblType
,INFORMATION1 VarChar150TblType
,INFORMATION2 VarChar150TblType
,INFORMATION3 VarChar150TblType
,INFORMATION4 VarChar150TblType
,INFORMATION5 VarChar150TblType
,INFORMATION6 VarChar150TblType
,INFORMATION7 VarChar150TblType
,INFORMATION8 VarChar150TblType
,INFORMATION9 VarChar150TblType
,INFORMATION10 VarChar150TblType
,INFORMATION11 VarChar150TblType
,INFORMATION12 VarChar150TblType
,INFORMATION13 VarChar150TblType
,INFORMATION14 VarChar150TblType
,INFORMATION15 VarChar150TblType
,INFORMATION16 VarChar150TblType
,INFORMATION17 VarChar150TblType
,INFORMATION18 VarChar150TblType
,INFORMATION19 VarChar150TblType
,INFORMATION20 VarChar150TblType
,INFORMATION21 VarChar150TblType
,INFORMATION22 VarChar150TblType
,INFORMATION23 VarChar150TblType
,INFORMATION24 VarChar150TblType
,INFORMATION25 VarChar150TblType
,INFORMATION26 VarChar150TblType
,INFORMATION27 VarChar150TblType
,INFORMATION28 VarChar150TblType
,INFORMATION29 VarChar150TblType
,INFORMATION30 VarChar150TblType
);
SELECT MAX(APPROVAL_HISTORY_ID)
FROM PQH_SS_TRANS_STATE_HISTORY
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID;
SELECT MAX(APPROVAL_HISTORY_ID)
FROM PQH_SS_APPROVAL_HISTORY
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID;
Select
ah.TRANSACTION_HISTORY_ID
,ah.APPROVAL_HISTORY_ID
,tx.CREATOR_PERSON_ID
,tx.CREATOR_ROLE
,tx.STATUS
,tx.TRANSACTION_STATE
,ah.TRANSACTION_EFFECTIVE_DATE
,ah.EFFECTIVE_DATE_OPTION
,tx.LAST_UPDATE_ROLE
,tx.PARENT_TRANSACTION_ID
,tx.RELAUNCH_FUNCTION
,ah.CREATED_BY
,ah.CREATION_DATE
,ah.LAST_UPDATE_DATE
,ah.LAST_UPDATED_BY
,ah.LAST_UPDATE_LOGIN
FROM pqh_ss_approval_history ah, HR_API_TRANSACTIONS tx
WHERE TRANSACTION_ID = TRANSACTION_HISTORY_ID
AND TRANSACTION_HISTORY_ID = P_TRANSACTION_ID;
,stateTbl.LAST_UPDATE_ROLE
,stateTbl.PARENT_TRANSACTION_ID
,stateTbl.RELAUNCH_FUNCTION
,stateTbl.CREATED_BY
,stateTbl.CREATION_DATE
,stateTbl.LAST_UPDATE_DATE
,stateTbl.LAST_UPDATED_BY
,stateTbl.LAST_UPDATE_LOGIN;
Insert into pqh_ss_trans_state_history
(
TRANSACTION_HISTORY_ID,
APPROVAL_HISTORY_ID,
CREATOR_PERSON_ID,
CREATOR_ROLE,
STATUS,
TRANSACTION_STATE,
EFFECTIVE_DATE,
EFFECTIVE_DATE_OPTION,
LAST_UPDATE_ROLE,
PARENT_TRANSACTION_ID,
RELAUNCH_FUNCTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values
(
stateTbl.TRANSACTION_HISTORY_ID(i)
,stateTbl.APPROVAL_HISTORY_ID(i)
,stateTbl.CREATOR_PERSON_ID(i)
,stateTbl.CREATOR_ROLE(i)
,stateTbl.STATUS(i)
,stateTbl.TRANSACTION_STATE(i)
,stateTbl.EFFECTIVE_DATE(i)
,stateTbl.EFFECTIVE_DATE_OPTION(i)
,stateTbl.LAST_UPDATE_ROLE(i)
,stateTbl.PARENT_TRANSACTION_ID(i)
,stateTbl.RELAUNCH_FUNCTION(i)
,stateTbl.CREATED_BY(i)
,stateTbl.CREATION_DATE(i)
,stateTbl.LAST_UPDATE_DATE(i)
,stateTbl.LAST_UPDATED_BY(i)
,stateTbl.LAST_UPDATE_LOGIN(i)
);
select pay_transaction_id,
transaction_id ,
transaction_step_id,
item_type ,
item_key ,
pay_proposal_id ,
assignment_id ,
pay_basis_id ,
business_group_id ,
change_date ,
date_to ,
last_change_date ,
reason ,
multiple_components,
component_id ,
change_amount_n ,
change_percentage ,
proposed_salary_n ,
parent_pay_transaction_id,
prior_pay_proposal_id ,
prior_pay_transaction_id ,
prior_proposed_salary_n ,
prior_pay_basis_id ,
approved ,
next_perf_review_date,
next_sal_review_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
comments ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date ,
object_version_number,
status ,
dml_operation ,
display_cd ,
txn_dml_operation
from per_pay_transaction_history
where approval_history_id = C_APPROVAL_HISTORY_ID
and transaction_step_id = P_TRANSACTION_STEP_ID;
delete from per_pay_transactions where transaction_step_id = P_TRANSACTION_STEP_ID;
Insert into per_pay_transactions
( pay_transaction_id,
transaction_id ,
transaction_step_id,
item_type ,
item_key ,
pay_proposal_id ,
assignment_id ,
pay_basis_id ,
business_group_id ,
change_date ,
date_to ,
last_change_date ,
reason ,
multiple_components,
component_id ,
change_amount_n ,
change_percentage ,
proposed_salary_n ,
parent_pay_transaction_id,
prior_pay_proposal_id ,
prior_pay_transaction_id ,
prior_proposed_salary_n ,
prior_pay_basis_id ,
approved ,
next_perf_review_date,
next_sal_review_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
comments ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date ,
object_version_number,
status ,
dml_operation ,
display_cd ,
txn_dml_operation)
values(
csr_per_pay_trans_hist_rec.pay_transaction_id,
csr_per_pay_trans_hist_rec.transaction_id ,
csr_per_pay_trans_hist_rec.transaction_step_id,
csr_per_pay_trans_hist_rec.item_type ,
csr_per_pay_trans_hist_rec.item_key ,
csr_per_pay_trans_hist_rec.pay_proposal_id ,
csr_per_pay_trans_hist_rec.assignment_id ,
csr_per_pay_trans_hist_rec.pay_basis_id ,
csr_per_pay_trans_hist_rec.business_group_id ,
csr_per_pay_trans_hist_rec.change_date ,
csr_per_pay_trans_hist_rec.date_to ,
csr_per_pay_trans_hist_rec.last_change_date ,
csr_per_pay_trans_hist_rec.reason ,
csr_per_pay_trans_hist_rec.multiple_components,
csr_per_pay_trans_hist_rec.component_id ,
csr_per_pay_trans_hist_rec.change_amount_n ,
csr_per_pay_trans_hist_rec.change_percentage ,
csr_per_pay_trans_hist_rec.proposed_salary_n ,
csr_per_pay_trans_hist_rec.parent_pay_transaction_id,
csr_per_pay_trans_hist_rec.prior_pay_proposal_id ,
csr_per_pay_trans_hist_rec.prior_pay_transaction_id ,
csr_per_pay_trans_hist_rec.prior_proposed_salary_n ,
csr_per_pay_trans_hist_rec.prior_pay_basis_id ,
csr_per_pay_trans_hist_rec.approved ,
csr_per_pay_trans_hist_rec.next_perf_review_date,
csr_per_pay_trans_hist_rec.next_sal_review_date ,
csr_per_pay_trans_hist_rec.attribute_category ,
csr_per_pay_trans_hist_rec.attribute1 ,
csr_per_pay_trans_hist_rec.attribute2 ,
csr_per_pay_trans_hist_rec.attribute3 ,
csr_per_pay_trans_hist_rec.attribute4 ,
csr_per_pay_trans_hist_rec.attribute5 ,
csr_per_pay_trans_hist_rec.attribute6 ,
csr_per_pay_trans_hist_rec.attribute7 ,
csr_per_pay_trans_hist_rec.attribute8 ,
csr_per_pay_trans_hist_rec.attribute9 ,
csr_per_pay_trans_hist_rec.attribute10 ,
csr_per_pay_trans_hist_rec.attribute11 ,
csr_per_pay_trans_hist_rec.attribute12 ,
csr_per_pay_trans_hist_rec.attribute13 ,
csr_per_pay_trans_hist_rec.attribute14 ,
csr_per_pay_trans_hist_rec.attribute15 ,
csr_per_pay_trans_hist_rec.attribute16 ,
csr_per_pay_trans_hist_rec.attribute17 ,
csr_per_pay_trans_hist_rec.attribute18 ,
csr_per_pay_trans_hist_rec.attribute19 ,
csr_per_pay_trans_hist_rec.attribute20 ,
csr_per_pay_trans_hist_rec.comments ,
csr_per_pay_trans_hist_rec.last_update_date ,
csr_per_pay_trans_hist_rec.last_updated_by ,
csr_per_pay_trans_hist_rec.last_update_login ,
csr_per_pay_trans_hist_rec.created_by ,
csr_per_pay_trans_hist_rec.creation_date ,
csr_per_pay_trans_hist_rec.object_version_number,
csr_per_pay_trans_hist_rec.status ,
csr_per_pay_trans_hist_rec.dml_operation ,
csr_per_pay_trans_hist_rec.display_cd ,
csr_per_pay_trans_hist_rec.txn_dml_operation);
SELECT
STEP_HISTORY_ID
,TRANSACTION_HISTORY_ID
,API_NAME
,API_DISPLAY_NAME
,PROCESSING_ORDER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ITEM_TYPE
,ITEM_KEY
,ACTIVITY_ID
,OBJECT_TYPE
,OBJECT_NAME
,OBJECT_IDENTIFIER
,OBJECT_STATE
,PK1
,PK2
,PK3
,PK4
,PK5
,INFORMATION_CATEGORY
,INFORMATION1
,INFORMATION2
,INFORMATION3
,INFORMATION4
,INFORMATION5
,INFORMATION6
,INFORMATION7
,INFORMATION8
,INFORMATION9
,INFORMATION10
,INFORMATION11
,INFORMATION12
,INFORMATION13
,INFORMATION14
,INFORMATION15
,INFORMATION16
,INFORMATION17
,INFORMATION18
,INFORMATION19
,INFORMATION20
,INFORMATION21
,INFORMATION22
,INFORMATION23
,INFORMATION24
,INFORMATION25
,INFORMATION26
,INFORMATION27
,INFORMATION28
,INFORMATION29
,INFORMATION30
FROM PQH_SS_STEP_HISTORY
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
AND APPROVAL_HISTORY_ID = P_APPROVAL_HISTORY_ID;
,TxStepTbl.LAST_UPDATE_DATE
,TxStepTbl.LAST_UPDATED_BY
,TxStepTbl.LAST_UPDATE_LOGIN
,TxStepTbl.ITEM_TYPE
,TxStepTbl.ITEM_KEY
,TxStepTbl.ACTIVITY_ID
,TxStepTbl.OBJECT_TYPE
,TxStepTbl.OBJECT_NAME
,TxStepTbl.OBJECT_IDENTIFIER
,TxStepTbl.OBJECT_STATE
,TxStepTbl.PK1
,TxStepTbl.PK2
,TxStepTbl.PK3
,TxStepTbl.PK4
,TxStepTbl.PK5
,TxStepTbl.INFORMATION_CATEGORY
,TxStepTbl.INFORMATION1
,TxStepTbl.INFORMATION2
,TxStepTbl.INFORMATION3
,TxStepTbl.INFORMATION4
,TxStepTbl.INFORMATION5
,TxStepTbl.INFORMATION6
,TxStepTbl.INFORMATION7
,TxStepTbl.INFORMATION8
,TxStepTbl.INFORMATION9
,TxStepTbl.INFORMATION10
,TxStepTbl.INFORMATION11
,TxStepTbl.INFORMATION12
,TxStepTbl.INFORMATION13
,TxStepTbl.INFORMATION14
,TxStepTbl.INFORMATION15
,TxStepTbl.INFORMATION16
,TxStepTbl.INFORMATION17
,TxStepTbl.INFORMATION18
,TxStepTbl.INFORMATION19
,TxStepTbl.INFORMATION20
,TxStepTbl.INFORMATION21
,TxStepTbl.INFORMATION22
,TxStepTbl.INFORMATION23
,TxStepTbl.INFORMATION24
,TxStepTbl.INFORMATION25
,TxStepTbl.INFORMATION26
,TxStepTbl.INFORMATION27
,TxStepTbl.INFORMATION28
,TxStepTbl.INFORMATION29
,TxStepTbl.INFORMATION30;
INSERT INTO HR_API_TRANSACTION_STEPS
(
TRANSACTION_STEP_ID
,TRANSACTION_ID
,API_NAME
,API_DISPLAY_NAME
,PROCESSING_ORDER
,CREATOR_PERSON_ID
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ITEM_TYPE
,ITEM_KEY
,ACTIVITY_ID
,OBJECT_TYPE
,OBJECT_NAME
,OBJECT_IDENTIFIER
,OBJECT_STATE
,PK1
,PK2
,PK3
,PK4
,PK5
,INFORMATION_CATEGORY
,INFORMATION1
,INFORMATION2
,INFORMATION3
,INFORMATION4
,INFORMATION5
,INFORMATION6
,INFORMATION7
,INFORMATION8
,INFORMATION9
,INFORMATION10
,INFORMATION11
,INFORMATION12
,INFORMATION13
,INFORMATION14
,INFORMATION15
,INFORMATION16
,INFORMATION17
,INFORMATION18
,INFORMATION19
,INFORMATION20
,INFORMATION21
,INFORMATION22
,INFORMATION23
,INFORMATION24
,INFORMATION25
,INFORMATION26
,INFORMATION27
,INFORMATION28
,INFORMATION29
,INFORMATION30
)
values
(
TxStepTbl.STEP_HISTORY_ID(i)
,TxStepTbl.TRANSACTION_HISTORY_ID(i)
,TxStepTbl.API_NAME(i)
,TxStepTbl.API_DISPLAY_NAME(i)
,TxStepTbl.PROCESSING_ORDER(i)
,0 -- creator_person_id
,0 -- OVN
,TxStepTbl.CREATED_BY(i)
,TxStepTbl.CREATION_DATE(i)
,TxStepTbl.LAST_UPDATE_DATE(i)
,TxStepTbl.LAST_UPDATED_BY(i)
,TxStepTbl.LAST_UPDATE_LOGIN(i)
,TxStepTbl.ITEM_TYPE(i)
,TxStepTbl.ITEM_KEY(i)
,TxStepTbl.ACTIVITY_ID(i)
,TxStepTbl.OBJECT_TYPE(i)
,TxStepTbl.OBJECT_NAME(i)
,TxStepTbl.OBJECT_IDENTIFIER(i)
,TxStepTbl.OBJECT_STATE(i)
,TxStepTbl.PK1(i)
,TxStepTbl.PK2(i)
,TxStepTbl.PK3(i)
,TxStepTbl.PK4(i)
,TxStepTbl.PK5(i)
,TxStepTbl.INFORMATION_CATEGORY(i)
,TxStepTbl.INFORMATION1(i)
,TxStepTbl.INFORMATION2(i)
,TxStepTbl.INFORMATION3(i)
,TxStepTbl.INFORMATION4(i)
,TxStepTbl.INFORMATION5(i)
,TxStepTbl.INFORMATION6(i)
,TxStepTbl.INFORMATION7(i)
,TxStepTbl.INFORMATION8(i)
,TxStepTbl.INFORMATION9(i)
,TxStepTbl.INFORMATION10(i)
,TxStepTbl.INFORMATION11(i)
,TxStepTbl.INFORMATION12(i)
,TxStepTbl.INFORMATION13(i)
,TxStepTbl.INFORMATION14(i)
,TxStepTbl.INFORMATION15(i)
,TxStepTbl.INFORMATION16(i)
,TxStepTbl.INFORMATION17(i)
,TxStepTbl.INFORMATION18(i)
,TxStepTbl.INFORMATION19(i)
,TxStepTbl.INFORMATION20(i)
,TxStepTbl.INFORMATION21(i)
,TxStepTbl.INFORMATION22(i)
,TxStepTbl.INFORMATION23(i)
,TxStepTbl.INFORMATION24(i)
,TxStepTbl.INFORMATION25(i)
,TxStepTbl.INFORMATION26(i)
,TxStepTbl.INFORMATION27(i)
,TxStepTbl.INFORMATION28(i)
,TxStepTbl.INFORMATION29(i)
,TxStepTbl.INFORMATION30(i)
);
SELECT
transaction_value_id
,step_history_id
,datatype
,name
,value
,original_value
,created_by
,creation_date
,last_update_date
,last_updated_by
,last_update_login
FROM PQH_SS_VALUE_HISTORY
Where step_history_id in
( SELECT STEP_HISTORY_ID
FROM PQH_SS_STEP_HISTORY
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
AND APPROVAL_HISTORY_ID = P_APPROVAL_HISTORY_ID)
AND APPROVAL_HISTORY_ID = P_APPROVAL_HISTORY_ID;
,TxValueTbl.last_update_date
,TxValueTbl.last_updated_by
,TxValueTbl.last_update_login;
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
)
VALUES
(
TxValueTbl.transaction_value_id(i)
,TxValueTbl.step_history_id(i)
,TxValueTbl.datatype(i)
,TxValueTbl.name(i)
,decode (TxValueTbl.datatype(i), 'VARCHAR2', TxValueTbl.value(i), null)
,decode (TxValueTbl.datatype(i), 'NUMBER', decode(TxValueTbl.value(i), null, null, to_number(TxValueTbl.value(i))), null)
,decode (TxValueTbl.datatype(i), 'DATE', decode(TxValueTbl.value(i), null, null, fnd_date.canonical_to_date(TxValueTbl.value(i))), null)
,decode (TxValueTbl.datatype(i), 'VARCHAR2', TxValueTbl.original_value(i), null)
,decode (TxValueTbl.datatype(i), 'NUMBER', decode(TxValueTbl.original_value(i), null, null, to_number(TxValueTbl.original_value(i))), null)
,decode (TxValueTbl.datatype(i), 'DATE', decode(TxValueTbl.original_value(i), null, null, fnd_date.canonical_to_date(TxValueTbl.original_value(i))), null)
,TxValueTbl.created_by(i)
,TxValueTbl.creation_date(i)
,TxValueTbl.last_update_date(i)
,TxValueTbl.last_updated_by(i)
,TxValueTbl.last_update_login(i)
);
SELECT MIN(APPROVAL_HISTORY_ID),MAX(APPROVAL_HISTORY_ID)
FROM PQH_SS_STEP_HISTORY
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID;
SELECT TRANSACTION_VALUE_ID, VALUE, ORIGINAL_VALUE
FROM PQH_SS_STEP_HISTORY step, PQH_SS_VALUE_HISTORY val
WHERE step.TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
and step.step_history_id = val.step_history_id
and step.approval_history_id = val.approval_history_id
and val.approval_history_id = -1;
SELECT 1
FROM PQH_SS_STEP_HISTORY step, PQH_SS_VALUE_HISTORY val
WHERE step.TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
and step.step_history_id = val.step_history_id
and step.approval_history_id = val.approval_history_id
and val.approval_history_id = 0
and val.value IS NOT NULL;
UPDATE pqh_ss_value_history
SET VALUE = t.VALUE, ORIGINAL_VALUE = t.ORIGINAL_VALUE
WHERE TRANSACTION_VALUE_ID = t.TRANSACTION_VALUE_ID
AND APPROVAL_HISTORY_ID = 0;
UPDATE HR_API_TRANSACTIONS
SET (
STATUS
,TRANSACTION_STATE
,TRANSACTION_EFFECTIVE_DATE
,EFFECTIVE_DATE_OPTION
,PARENT_TRANSACTION_ID
,RELAUNCH_FUNCTION
,TRANSACTION_DOCUMENT
)
=
( SELECT
STATUS
,TRANSACTION_STATE
,EFFECTIVE_DATE
,EFFECTIVE_DATE_OPTION
,PARENT_TRANSACTION_ID
,RELAUNCH_FUNCTION
,TRANSACTION_DOCUMENT
FROM PQH_SS_TRANS_STATE_HISTORY
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
AND approval_history_id = l_seq_id
)
WHERE TRANSACTION_ID = P_TRANSACTION_ID
AND exists (SELECT 1 FROM PQH_SS_TRANS_STATE_HISTORY
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
AND approval_history_id = l_seq_id);
select 'Y' into lv_found
from PQH_SS_STEP_HISTORY
where TRANSACTION_HISTORY_ID =P_TRANSACTION_ID
and API_NAME='HR_LOA_SS.PROCESS_API';
DELETE HR_API_TRANSACTION_VALUES
WHERE TRANSACTION_STEP_ID in
( SELECT TRANSACTION_STEP_ID
FROM HR_API_TRANSACTION_STEPS
WHERE TRANSACTION_ID = P_TRANSACTION_ID);
DELETE HR_API_TRANSACTION_STEPS
WHERE TRANSACTION_ID = P_TRANSACTION_ID;
Procedure DeleteStaleData
(
P_TRANSACTION_ID IN NUMBER
,P_ACTION IN VARCHAR DEFAULT 'SFL'
)
IS
l_seq_id NUMBER(5);
l_proc constant varchar2(100) := g_package || ' DeleteStaleData';
select action
from pqh_ss_approval_history
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
and approval_history_id = l_seq_id
and action = 'SFL';
-- Delete State History
DELETE pqh_ss_trans_state_history
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
and approval_history_id = l_seq_id;
-- Delete Routing History
DELETE pqh_ss_approval_history
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
and approval_history_id = l_seq_id;
DELETE pqh_ss_value_history
WHERE approval_history_id = l_seq_id
and step_history_id in (
SELECT step_history_id
FROM pqh_ss_step_history
WHERE transaction_history_id = P_TRANSACTION_ID
and approval_history_id = l_seq_id
);
-- Delete Steps History
DELETE pqh_ss_step_history
WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
and approval_history_id = l_seq_id;
DELETE per_pay_transaction_history
WHERE TRANSACTION_ID = P_TRANSACTION_ID
and approval_history_id = l_seq_id;
End DeleteStaleData;
SELECT ITEM_TYPE, ITEM_KEY, TRANSACTION_EFFECTIVE_DATE, EFFECTIVE_DATE_OPTION
FROM HR_API_TRANSACTIONS
WHERE TRANSACTION_ID = P_TRANSACTION_ID;
select pay_transaction_id,
transaction_id ,
transaction_step_id,
item_type ,
item_key ,
pay_proposal_id ,
assignment_id ,
pay_basis_id ,
business_group_id ,
change_date ,
date_to ,
last_change_date ,
reason ,
multiple_components,
component_id ,
change_amount_n ,
change_percentage ,
proposed_salary_n ,
parent_pay_transaction_id,
prior_pay_proposal_id ,
prior_pay_transaction_id ,
prior_proposed_salary_n ,
prior_pay_basis_id ,
approved ,
next_perf_review_date,
next_sal_review_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
comments ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date ,
object_version_number,
status ,
dml_operation ,
display_cd ,
txn_dml_operation
from per_pay_transactions
where transaction_step_id = P_TRANSACTION_STEP_ID;
Insert into per_pay_transaction_history
( pay_transaction_id,
APPROVAL_HISTORY_ID,
transaction_id ,
transaction_step_id,
item_type ,
item_key ,
pay_proposal_id ,
assignment_id ,
pay_basis_id ,
business_group_id ,
change_date ,
date_to ,
last_change_date ,
reason ,
multiple_components,
component_id ,
change_amount_n ,
change_percentage ,
proposed_salary_n ,
parent_pay_transaction_id,
prior_pay_proposal_id ,
prior_pay_transaction_id ,
prior_proposed_salary_n ,
prior_pay_basis_id ,
approved ,
next_perf_review_date,
next_sal_review_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
comments ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date ,
object_version_number,
status ,
dml_operation ,
display_cd ,
txn_dml_operation)
values(
csr_per_pay_trans_rec.pay_transaction_id,
P_APPROVAL_HISTORY_ID,
csr_per_pay_trans_rec.transaction_id ,
csr_per_pay_trans_rec.transaction_step_id,
csr_per_pay_trans_rec.item_type ,
csr_per_pay_trans_rec.item_key ,
csr_per_pay_trans_rec.pay_proposal_id ,
csr_per_pay_trans_rec.assignment_id ,
csr_per_pay_trans_rec.pay_basis_id ,
csr_per_pay_trans_rec.business_group_id ,
csr_per_pay_trans_rec.change_date ,
csr_per_pay_trans_rec.date_to ,
csr_per_pay_trans_rec.last_change_date ,
csr_per_pay_trans_rec.reason ,
csr_per_pay_trans_rec.multiple_components,
csr_per_pay_trans_rec.component_id ,
csr_per_pay_trans_rec.change_amount_n ,
csr_per_pay_trans_rec.change_percentage ,
csr_per_pay_trans_rec.proposed_salary_n ,
csr_per_pay_trans_rec.parent_pay_transaction_id,
csr_per_pay_trans_rec.prior_pay_proposal_id ,
csr_per_pay_trans_rec.prior_pay_transaction_id ,
csr_per_pay_trans_rec.prior_proposed_salary_n ,
csr_per_pay_trans_rec.prior_pay_basis_id ,
csr_per_pay_trans_rec.approved ,
csr_per_pay_trans_rec.next_perf_review_date,
csr_per_pay_trans_rec.next_sal_review_date ,
csr_per_pay_trans_rec.attribute_category ,
csr_per_pay_trans_rec.attribute1 ,
csr_per_pay_trans_rec.attribute2 ,
csr_per_pay_trans_rec.attribute3 ,
csr_per_pay_trans_rec.attribute4 ,
csr_per_pay_trans_rec.attribute5 ,
csr_per_pay_trans_rec.attribute6 ,
csr_per_pay_trans_rec.attribute7 ,
csr_per_pay_trans_rec.attribute8 ,
csr_per_pay_trans_rec.attribute9 ,
csr_per_pay_trans_rec.attribute10 ,
csr_per_pay_trans_rec.attribute11 ,
csr_per_pay_trans_rec.attribute12 ,
csr_per_pay_trans_rec.attribute13 ,
csr_per_pay_trans_rec.attribute14 ,
csr_per_pay_trans_rec.attribute15 ,
csr_per_pay_trans_rec.attribute16 ,
csr_per_pay_trans_rec.attribute17 ,
csr_per_pay_trans_rec.attribute18 ,
csr_per_pay_trans_rec.attribute19 ,
csr_per_pay_trans_rec.attribute20 ,
csr_per_pay_trans_rec.comments ,
csr_per_pay_trans_rec.last_update_date ,
csr_per_pay_trans_rec.last_updated_by ,
csr_per_pay_trans_rec.last_update_login ,
csr_per_pay_trans_rec.created_by ,
csr_per_pay_trans_rec.creation_date ,
csr_per_pay_trans_rec.object_version_number,
csr_per_pay_trans_rec.status ,
csr_per_pay_trans_rec.dml_operation ,
csr_per_pay_trans_rec.display_cd ,
csr_per_pay_trans_rec.txn_dml_operation);
select transaction_value_id ,
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 = P_TRANSACTION_STEP_ID;
,TxValueTbl.last_update_date
,TxValueTbl.last_updated_by
,TxValueTbl.last_update_login;
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 )
values(
TxValueTbl.transaction_value_id(i)
,P_TRANSACTION_STEP_ID
,P_APPROVAL_HISTORY_ID
,TxValueTbl.datatype(i)
,TxValueTbl.name(i)
,TxValueTbl.value(i)
,TxValueTbl.original_value(i)
,TxValueTbl.created_by(i)
,TxValueTbl.creation_date(i)
,TxValueTbl.last_update_date(i)
,TxValueTbl.last_updated_by(i)
,TxValueTbl.last_update_login(i));
SELECT
TRANSACTION_STEP_ID
,TRANSACTION_ID
,API_NAME
,API_DISPLAY_NAME
,PROCESSING_ORDER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ITEM_TYPE
,ITEM_KEY
,ACTIVITY_ID
,OBJECT_TYPE
,OBJECT_NAME
,OBJECT_IDENTIFIER
,OBJECT_STATE
,PK1
,PK2
,PK3
,PK4
,PK5
,INFORMATION_CATEGORY
,INFORMATION1
,INFORMATION2
,INFORMATION3
,INFORMATION4
,INFORMATION5
,INFORMATION6
,INFORMATION7
,INFORMATION8
,INFORMATION9
,INFORMATION10
,INFORMATION11
,INFORMATION12
,INFORMATION13
,INFORMATION14
,INFORMATION15
,INFORMATION16
,INFORMATION17
,INFORMATION18
,INFORMATION19
,INFORMATION20
,INFORMATION21
,INFORMATION22
,INFORMATION23
,INFORMATION24
,INFORMATION25
,INFORMATION26
,INFORMATION27
,INFORMATION28
,INFORMATION29
,INFORMATION30
FROM hr_api_transaction_steps
WHERE TRANSACTION_ID = P_TRANSACTION_ID;
,TxStepTbl.LAST_UPDATE_DATE
,TxStepTbl.LAST_UPDATED_BY
,TxStepTbl.LAST_UPDATE_LOGIN
,TxStepTbl.ITEM_TYPE
,TxStepTbl.ITEM_KEY
,TxStepTbl.ACTIVITY_ID
,TxStepTbl.OBJECT_TYPE
,TxStepTbl.OBJECT_NAME
,TxStepTbl.OBJECT_IDENTIFIER
,TxStepTbl.OBJECT_STATE
,TxStepTbl.PK1
,TxStepTbl.PK2
,TxStepTbl.PK3
,TxStepTbl.PK4
,TxStepTbl.PK5
,TxStepTbl.INFORMATION_CATEGORY
,TxStepTbl.INFORMATION1
,TxStepTbl.INFORMATION2
,TxStepTbl.INFORMATION3
,TxStepTbl.INFORMATION4
,TxStepTbl.INFORMATION5
,TxStepTbl.INFORMATION6
,TxStepTbl.INFORMATION7
,TxStepTbl.INFORMATION8
,TxStepTbl.INFORMATION9
,TxStepTbl.INFORMATION10
,TxStepTbl.INFORMATION11
,TxStepTbl.INFORMATION12
,TxStepTbl.INFORMATION13
,TxStepTbl.INFORMATION14
,TxStepTbl.INFORMATION15
,TxStepTbl.INFORMATION16
,TxStepTbl.INFORMATION17
,TxStepTbl.INFORMATION18
,TxStepTbl.INFORMATION19
,TxStepTbl.INFORMATION20
,TxStepTbl.INFORMATION21
,TxStepTbl.INFORMATION22
,TxStepTbl.INFORMATION23
,TxStepTbl.INFORMATION24
,TxStepTbl.INFORMATION25
,TxStepTbl.INFORMATION26
,TxStepTbl.INFORMATION27
,TxStepTbl.INFORMATION28
,TxStepTbl.INFORMATION29
,TxStepTbl.INFORMATION30;
INSERT INTO pqh_ss_step_history
(
STEP_HISTORY_ID
,APPROVAL_HISTORY_ID
,TRANSACTION_HISTORY_ID
,API_NAME
,API_DISPLAY_NAME
,PROCESSING_ORDER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ITEM_TYPE
,ITEM_KEY
,ACTIVITY_ID
,OBJECT_TYPE
,OBJECT_NAME
,OBJECT_IDENTIFIER
,OBJECT_STATE
,PK1
,PK2
,PK3
,PK4
,PK5
,INFORMATION_CATEGORY
,INFORMATION1
,INFORMATION2
,INFORMATION3
,INFORMATION4
,INFORMATION5
,INFORMATION6
,INFORMATION7
,INFORMATION8
,INFORMATION9
,INFORMATION10
,INFORMATION11
,INFORMATION12
,INFORMATION13
,INFORMATION14
,INFORMATION15
,INFORMATION16
,INFORMATION17
,INFORMATION18
,INFORMATION19
,INFORMATION20
,INFORMATION21
,INFORMATION22
,INFORMATION23
,INFORMATION24
,INFORMATION25
,INFORMATION26
,INFORMATION27
,INFORMATION28
,INFORMATION29
,INFORMATION30
)
VALUES
(
TxStepTbl.STEP_HISTORY_ID(i)
,P_APPROVAL_HISTORY_ID
,TxStepTbl.TRANSACTION_HISTORY_ID(i)
,TxStepTbl.API_NAME(i)
,TxStepTbl.API_DISPLAY_NAME(i)
,TxStepTbl.PROCESSING_ORDER(i)
,TxStepTbl.CREATED_BY(i)
,TxStepTbl.CREATION_DATE(i)
,TxStepTbl.LAST_UPDATE_DATE(i)
,TxStepTbl.LAST_UPDATED_BY(i)
,TxStepTbl.LAST_UPDATE_LOGIN(i)
,TxStepTbl.ITEM_TYPE(i)
,TxStepTbl.ITEM_KEY(i)
,TxStepTbl.ACTIVITY_ID(i)
,TxStepTbl.OBJECT_TYPE(i)
,TxStepTbl.OBJECT_NAME(i)
,TxStepTbl.OBJECT_IDENTIFIER(i)
,TxStepTbl.OBJECT_STATE(i)
,TxStepTbl.PK1(i)
,TxStepTbl.PK2(i)
,TxStepTbl.PK3(i)
,TxStepTbl.PK4(i)
,TxStepTbl.PK5(i)
,TxStepTbl.INFORMATION_CATEGORY(i)
,TxStepTbl.INFORMATION1(i)
,TxStepTbl.INFORMATION2(i)
,TxStepTbl.INFORMATION3(i)
,TxStepTbl.INFORMATION4(i)
,TxStepTbl.INFORMATION5(i)
,TxStepTbl.INFORMATION6(i)
,TxStepTbl.INFORMATION7(i)
,TxStepTbl.INFORMATION8(i)
,TxStepTbl.INFORMATION9(i)
,TxStepTbl.INFORMATION10(i)
,TxStepTbl.INFORMATION11(i)
,TxStepTbl.INFORMATION12(i)
,TxStepTbl.INFORMATION13(i)
,TxStepTbl.INFORMATION14(i)
,TxStepTbl.INFORMATION15(i)
,TxStepTbl.INFORMATION16(i)
,TxStepTbl.INFORMATION17(i)
,TxStepTbl.INFORMATION18(i)
,TxStepTbl.INFORMATION19(i)
,TxStepTbl.INFORMATION20(i)
,TxStepTbl.INFORMATION21(i)
,TxStepTbl.INFORMATION22(i)
,TxStepTbl.INFORMATION23(i)
,TxStepTbl.INFORMATION24(i)
,TxStepTbl.INFORMATION25(i)
,TxStepTbl.INFORMATION26(i)
,TxStepTbl.INFORMATION27(i)
,TxStepTbl.INFORMATION28(i)
,TxStepTbl.INFORMATION29(i)
,TxStepTbl.INFORMATION30(i)
);
SELECT
t.TRANSACTION_ID
,t.CREATOR_PERSON_ID
,t.ASSIGNMENT_ID
,t.SELECTED_PERSON_ID
,t.ITEM_TYPE
,t.ITEM_KEY
,t.PROCESS_NAME
,t.FUNCTION_ID
,t.RPTG_GRP_ID
,t.PLAN_ID
,t.TRANSACTION_GROUP
,t.TRANSACTION_IDENTIFIER
,t.STATUS
,t.TRANSACTION_STATE
,t.TRANSACTION_EFFECTIVE_DATE
,t.EFFECTIVE_DATE_OPTION
,t.CREATOR_ROLE
,t.LAST_UPDATE_ROLE
,t.PARENT_TRANSACTION_ID
,t.RELAUNCH_FUNCTION
,t.TRANSACTION_DOCUMENT
,pt.transaction_history_id
FROM hr_api_transactions t, pqh_ss_transaction_history pt
WHERE transaction_id = P_TRANSACTION_ID
AND t.transaction_id = pt.transaction_history_ID (+);
,p_selected_person_id => l_trans_details_row.selected_person_id
,p_item_type => l_trans_details_row.item_type
,p_item_key => l_trans_details_row.item_key
,p_process_name => l_trans_details_row.process_name
,p_function_id => l_trans_details_row.function_id
,p_rptg_grp_id => l_trans_details_row.rptg_grp_id
,p_plan_id => l_trans_details_row.plan_id
,p_transaction_group => l_trans_details_row.transaction_group
,p_transaction_identifier => l_trans_details_row.transaction_identifier
,p_transaction_history_id => P_TRANSACTION_ID
);
,p_last_update_role => l_trans_details_row.last_update_role
,p_parent_transaction_id => l_trans_details_row.parent_transaction_id
,p_relaunch_function => l_trans_details_row.relaunch_function
,p_transaction_document => l_trans_details_row.transaction_document
,p_transaction_history_id => P_TRANSACTION_ID
,P_APPROVAL_HISTORY_ID => P_APPROVAL_HISTORY_ID);
deleteStaleData(P_TRANSACTION_ID, P_ACTION);
Procedure ARCHIVE_DELETE
(
P_TRANSACTION_ID IN NUMBER
,P_NOTIFICATION_ID IN NUMBER
,P_USER_NAME IN VARCHAR2
,P_USER_COMMENT IN VARCHAR2
)
IS
l_proc constant varchar2(100) := g_package || ' ARCHIVE_DELETE';
,P_ACTION => 'DELETED'
);
End ARCHIVE_DELETE;