[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hatv.number_value
from hr_api_transaction_steps hats,
hr_api_transactions hat,
hr_api_transaction_values hatv
where hats.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
and hatv.transaction_step_id = hats.transaction_step_id
and hatv.name = 'P_GRADE_LADDER_PGM_ID'
and hats.TRANSACTION_ID = hat.TRANSACTION_ID
and hat.TRANSACTION_ID = p_transaction_id;
Select Nvl(Gsp_Allow_Override_Flag,'Y')
From Ben_Pgm_f Pgm,
Per_all_assignments_F paa
Where paa.Assignment_Id = p_assignment_id
and p_effective_date between paa.Effective_Start_Date and paa.Effective_End_Date
and paa.GRADE_LADDER_PGM_ID is Not NULL
and pgm.pgm_id = paa.Grade_Ladder_Pgm_Id
and p_effective_date between Pgm.Effective_Start_Date and Pgm.Effective_End_Date
and Pgm_typ_Cd = 'GSP'
and Pgm_stat_Cd = 'A'
and Update_Salary_Cd = 'SALARY_BASIS';
Select Nvl(Gsp_Allow_Override_Flag,'Y')
From Ben_Pgm_f Pgm
Where pgm.pgm_id = l_transaction_ladder_id
and p_effective_date between Pgm.Effective_Start_Date and Pgm.Effective_End_Date
and Pgm_typ_Cd = 'GSP'
and Pgm_stat_Cd = 'A'
and Update_Salary_Cd = 'SALARY_BASIS';
select transaction_id,transaction_step_id
from hr_api_transaction_steps
where transaction_step_id = (Select transaction_step_id from hr_api_transaction_steps
where item_key = p_item_key
and item_type = p_item_type
and api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API');
select max(col1) as assignment_id,
max(col2) as pay_basis_id
from
(select decode(NAME, 'P_ASSIGNMENT_ID', NUMBER_VALUE) col1,
decode(NAME, 'P_PAY_BASIS_ID', NUMBER_VALUE) col2
from hr_api_transaction_values
where TRANSACTION_STEP_ID = c_transaction_step_id);
select pay_basis_id
from per_all_assignments_f
where assignment_id = c_assignment_id
and c_effective_date between effective_start_date and effective_end_date;
select change_date
from per_pay_transactions
where p_transaction_step_id is not null
and transaction_step_id = p_transaction_step_id
and PARENT_PAY_TRANSACTION_ID is null
and status <> 'DELETE';
select change_date
from per_pay_proposals
where assignment_id = p_assignment_id
and pay_proposal_id not in (select pay_proposal_id
from per_pay_transactions
where p_transaction_step_id is not null
and transaction_step_id = p_transaction_step_id
and PARENT_PAY_TRANSACTION_ID is null
and status <> 'DELETE');
select APPLICATION_COLUMN_NAME from
fnd_descr_flex_col_usage_vl
where APPLICATION_ID = 800
and DESCRIPTIVE_FLEXFIELD_NAME = p_dff_name
and nvl(REQUIRED_FLAG,'N') = 'Y';
P_DATETRACK_UPDATE_MODE IN VARCHAR2 ,
P_EFFECTIVE_DATE IN DATE ,
P_EFFECTIVE_DATE_OPTION IN VARCHAR2 ,
P_LOGIN_PERSON_ID IN NUMBER ,
P_APPROVER_ID IN NUMBER default null,
P_SAVE_MODE IN VARCHAR2 default null) IS
--
--
l_tx_name t_tx_name;
l_update_mode boolean := true;
Select * from per_all_assignments_f
Where assignment_id = p_assignment_id
and trunc(p_effective_date) between effective_start_date and effective_end_date;
p_update_mode => l_update_mode);
Update hr_api_transactions
set transaction_effective_date = trunc(P_EFFECTIVE_DATE)
where transaction_id = l_transaction_id;
If l_update_mode then
--
l_count := 1;
l_tx_name(l_count) := 'P_DATETRACK_UPDATE_MODE';
l_tx_char(l_count) := P_DATETRACK_UPDATE_MODE;
update hr_api_transaction_values
set
varchar2_value = l_tx_char(i),
number_value = l_tx_num(i),
date_value = l_tx_date(i)
where transaction_step_id = l_transaction_step_id
and name = l_tx_name(i);
l_tx_name(l_count) := 'P_DATETRACK_UPDATE_MODE';
l_tx_char(l_count) := P_DATETRACK_UPDATE_MODE;
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)
Values
( hr_api_transaction_values_s.nextval,
l_transaction_step_id,
l_tx_type(i),
l_tx_name(i),
l_tx_char(i),
l_tx_num(i),
l_tx_date(i),
l_tx_char(i),
l_tx_num(i),
l_tx_date(i));
update hr_api_transaction_values
set
number_value = p_pay_basis_id
where transaction_step_id = l_transaction_step_id
and name = 'P_PAY_BASIS_ID';
select hatv1.date_value date_value
from hr_api_transaction_values hatv,
hr_api_transaction_steps hats,
hr_api_transactions hat,
hr_api_transaction_values hatv1
where hatv.NAME = 'P_PAY_BASIS_ID'
and hatv1.NAME = 'P_EFFECTIVE_DATE'
and hatv1.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
and hatv.NUMBER_VALUE <> hatv.ORIGINAL_NUMBER_VALUE
and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
and hats.TRANSACTION_ID = hat.TRANSACTION_ID
and hat.ASSIGNMENT_ID = p_assignment_id
and hat.ITEM_KEY = p_item_key;
select effective_start_date date_value
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date >= p_effective_date
and pay_basis_id <> (Select pay_basis_id from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date)
order by date_value desc;
PROCEDURE delete_transaction(p_assgn_id IN number,
p_effective_dt IN date,
p_transaction_id IN number,
p_transaction_step_id IN number,
p_item_key IN varchar2,
p_item_type IN varchar2,
p_next_change_date In date,
p_changedt_curr IN date,
p_changedt_last IN date default Null,
p_failed_to_delete IN OUT NOCOPY varchar2,
p_busgroup_id IN number)
IS
--
cursor csr_recs_on_top(c_assignment_id number, c_change_date date) is
select max(change_date)
from per_pay_transactions
where ASSIGNMENT_ID = c_assignment_id
and change_date > c_change_date;
cursor csr_delete_recs(c_effective_dt date, c_assgn_id number, c_changedt_curr date, c_changedt_last date
,c_transaction_id number) is
Select
ppt.PAY_TRANSACTION_ID,
ppt.TRANSACTION_ID,
ppt.TRANSACTION_STEP_ID,
ppt.ITEM_TYPE,
ppt.ITEM_KEY,
ppt.PAY_PROPOSAL_ID,
ppt.ASSIGNMENT_ID,
ppt.COMPONENT_ID,
ppt.REASON,
ppt.PAY_BASIS_ID,
ppt.BUSINESS_GROUP_ID,
ppt.CHANGE_DATE,
ppt.DATE_TO,
ppt.last_change_date,
ppt.PROPOSED_SALARY_N,
ppt.CHANGE_AMOUNT_N,
ppt.CHANGE_PERCENTAGE,
ppb.PAY_ANNUALIZATION_FACTOR,
pet.INPUT_CURRENCY_CODE,
ppt.STATUS,
ppt.DML_OPERATION,
'TRANSACTION' from_tab,
ppt.PRIOR_PROPOSED_SALARY_N,
ppt.PRIOR_PAY_BASIS_ID,
ppt.ATTRIBUTE_CATEGORY,
ppt.ATTRIBUTE1,
ppt.ATTRIBUTE2,
ppt.ATTRIBUTE3,
ppt.ATTRIBUTE4,
ppt.ATTRIBUTE5,
ppt.ATTRIBUTE6,
ppt.ATTRIBUTE7,
ppt.ATTRIBUTE8,
ppt.ATTRIBUTE9,
ppt.ATTRIBUTE10,
ppt.ATTRIBUTE11,
ppt.ATTRIBUTE12,
ppt.ATTRIBUTE13,
ppt.ATTRIBUTE14,
ppt.ATTRIBUTE15,
ppt.ATTRIBUTE16,
ppt.ATTRIBUTE17,
ppt.ATTRIBUTE18,
ppt.ATTRIBUTE19,
ppt.ATTRIBUTE20,
ppt.MULTIPLE_COMPONENTS,
ppt.PARENT_PAY_TRANSACTION_ID,
ppt.PRIOR_PAY_PROPOSAL_ID,
ppt.PRIOR_PAY_TRANSACTION_ID,
ppt.APPROVED,
ppt.object_version_number
from per_pay_transactions ppt,
per_pay_bases ppb,
pay_input_values_f piv,
pay_element_types_f pet
where ppt.assignment_id = c_assgn_id
AND ppt.PARENT_PAY_TRANSACTION_ID is null
AND ppt.TRANSACTION_ID = c_transaction_id
AND ppt.change_date between c_changedt_last and c_changedt_curr
AND ppb.pay_basis_id = ppt.pay_basis_id
AND ppb.input_value_id = piv.input_value_id
AND c_effective_dt BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.element_type_id = pet.element_type_id
AND c_effective_dt BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppt.status <> 'DELETE'
Union
Select
null PAY_TRANSACTION_ID,
null TRANSACTION_ID,
null TRANSACTION_STEP_ID,
null ITEM_TYPE,
null ITEM_KEY,
pay.PAY_PROPOSAL_ID,
pay.ASSIGNMENT_ID ASSIGNMENT_ID,
null COMPONENT_ID,
pay.PROPOSAL_REASON REASON,
paaf.PAY_BASIS_ID PAY_BASIS_ID,
pay.BUSINESS_GROUP_ID,
pay.CHANGE_DATE,
pay.DATE_TO,
pay.last_change_date,
pay.PROPOSED_SALARY_N,
null change_amount_n,
null change_percentage,
ppb.PAY_ANNUALIZATION_FACTOR,
pet.INPUT_CURRENCY_CODE,
null STATUS,
null DML_OPERATION,
'PROPOSAL' from_tab,
null PRIOR_PROPOSED_SALARY_N,
null PRIOR_PAY_BASIS_ID,
pay.ATTRIBUTE_CATEGORY,
pay.ATTRIBUTE1,
pay.ATTRIBUTE2,
pay.ATTRIBUTE3,
pay.ATTRIBUTE4,
pay.ATTRIBUTE5,
pay.ATTRIBUTE6,
pay.ATTRIBUTE7,
pay.ATTRIBUTE8,
pay.ATTRIBUTE9,
pay.ATTRIBUTE10,
pay.ATTRIBUTE11,
pay.ATTRIBUTE12,
pay.ATTRIBUTE13,
pay.ATTRIBUTE14,
pay.ATTRIBUTE15,
pay.ATTRIBUTE16,
pay.ATTRIBUTE17,
pay.ATTRIBUTE18,
pay.ATTRIBUTE19,
pay.ATTRIBUTE20,
pay.MULTIPLE_COMPONENTS,
null PARENT_PAY_TRANSACTION_ID,
null PRIOR_PAY_PROPOSAL_ID,
null PRIOR_PAY_TRANSACTION_ID,
null APPROVED,
pay.object_version_number
from per_pay_proposals pay,
per_all_assignments_f paaf,
per_pay_bases ppb,
pay_input_values_f piv,
pay_element_types_f pet
where pay.assignment_id = c_assgn_id
AND pay.change_date between c_changedt_last and c_changedt_curr
AND pay.assignment_id = paaf.assignment_id
and c_effective_dt BETWEEN paaf.effective_start_date AND paaf.effective_end_date
--AND (p_changedt_curr BETWEEN paaf.effective_start_date AND paaf.effective_end_date
-- OR p_changedt_last BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
AND ppb.pay_basis_id = paaf.pay_basis_id AND ppb.input_value_id = piv.input_value_id
AND c_effective_dt BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.element_type_id = pet.element_type_id
AND c_effective_dt BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pay.pay_proposal_id not in (select nvl(pay_proposal_id, -1) from per_pay_transactions
where assignment_id = pay.assignment_id
and TRANSACTION_ID = c_transaction_id)
ORDER by change_date asc;
cursor csr_update_comps(c_parent_proposal_id in number) is
select
component_id ,
pay_proposal_id ,
business_group_id ,
approved ,
component_reason ,
change_amount ,
change_percentage ,
comments ,
new_amount ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
change_amount_n ,
object_version_number
from per_pay_proposal_components
where PAY_PROPOSAL_ID = c_parent_proposal_id;
l_last_row csr_delete_recs%rowtype;
l_proc varchar2(72) := g_package||'delete_transaction';
l_do_delete varchar2(20);
l_failed_to_delete varchar2(2) := 'N';
p_failed_to_delete := l_failed_to_delete;
l_do_delete := check_Salary_Basis_Change(p_assgn_id,p_changedt_curr);
if l_do_delete = 'NONE' then
--
l_failed_to_delete := 'N';
p_failed_to_delete := l_failed_to_delete;
elsif l_do_delete = 'F_ASSIGNMENT' then
--
l_failed_to_delete := 'Y';
p_failed_to_delete := l_failed_to_delete;
l_failed_to_delete := 'N';
p_failed_to_delete := l_failed_to_delete;
for delete_recs in csr_delete_recs(p_effective_dt, p_assgn_id, p_changedt_curr, l_changedt_last, p_transaction_id) loop
--
if g_debug then
hr_utility.set_location(l_proc, 40);
l_last_rec_from := delete_recs.from_tab;
l_last_trans_id := delete_recs.pay_transaction_id;
l_last_row := delete_recs;
if delete_recs.from_tab = 'TRANSACTION' then
--
if delete_recs.pay_proposal_id is null then
--
delete from per_pay_transactions
where parent_pay_transaction_id = delete_recs.pay_transaction_id;
delete from per_pay_transactions
where pay_transaction_id = delete_recs.pay_transaction_id;
update per_pay_transactions
set STATUS = 'DELETE',
DML_OPERATION = 'DELETE'
where parent_pay_transaction_id = delete_recs.pay_transaction_id;
update per_pay_transactions
set STATUS = 'DELETE',
DML_OPERATION = 'DELETE'
where pay_transaction_id = delete_recs.pay_transaction_id;
hr_utility.set_location('Inserting when p_changedt_last NULL:'|| l_proc, 70);
select PER_PAY_TRANSACTIONS_S.NEXTVAL into l_seq_val from dual;
insert into per_pay_transactions
(PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
TRANSACTION_ID, -- TRANSACTION_ID,
TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
ITEM_TYPE,-- ITEM_TYPE,
ITEM_KEY,-- ITEM_KEY,
PAY_PROPOSAL_ID,
ASSIGNMENT_ID,-- ASSIGNMENT_ID,
COMPONENT_ID,-- COMPONENT_ID,
REASON,-- REASON,
PAY_BASIS_ID,-- PAY_BASIS_ID,
BUSINESS_GROUP_ID,
CHANGE_DATE,
DATE_TO,
PROPOSED_SALARY_N,
change_amount_n,
change_percentage,
STATUS,-- STATUS,
DML_OPERATION,-- DML_OPERATION,
PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
MULTIPLE_COMPONENTS,
PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
APPROVED, -- APPROVED
object_version_number)
values(l_seq_val ,--PAY_TRANSACTION_ID,
p_transaction_id, -- TRANSACTION_ID,
p_transaction_step_id,-- TRANSACTION_STEP_ID,
p_item_type,-- ITEM_TYPE,
p_item_key,-- ITEM_KEY,
l_last_row.PAY_PROPOSAL_ID,
l_last_row.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
l_last_row.COMPONENT_ID,
l_last_row.REASON,-- REASON,
l_last_row.PAY_BASIS_ID,-- PAY_BASIS_ID,
l_last_row.BUSINESS_GROUP_ID,
l_last_row.CHANGE_DATE,
l_curr_date_to, --update last recs date_to to curr_rec
l_last_row.PROPOSED_SALARY_N,-- proposed_salary_n,
l_last_row.change_amount_n, -- change_amount_n,
l_last_row.change_percentage,-- change_percentage,
'DELETE',-- STATUS,
'DELETE',-- DML_OPERATION,
l_last_row.PRIOR_PROPOSED_SALARY_N,
l_last_row.PRIOR_PAY_BASIS_ID,
l_last_row.ATTRIBUTE_CATEGORY,
l_last_row.ATTRIBUTE1,
l_last_row.ATTRIBUTE2,
l_last_row.ATTRIBUTE3,
l_last_row.ATTRIBUTE4,
l_last_row.ATTRIBUTE5,
l_last_row.ATTRIBUTE6,
l_last_row.ATTRIBUTE7,
l_last_row.ATTRIBUTE8,
l_last_row.ATTRIBUTE9,
l_last_row.ATTRIBUTE10,
l_last_row.ATTRIBUTE11,
l_last_row.ATTRIBUTE12,
l_last_row.ATTRIBUTE13,
l_last_row.ATTRIBUTE14,
l_last_row.ATTRIBUTE15,
l_last_row.ATTRIBUTE16,
l_last_row.ATTRIBUTE17,
l_last_row.ATTRIBUTE18,
l_last_row.ATTRIBUTE19,
l_last_row.ATTRIBUTE20,
l_last_row.MULTIPLE_COMPONENTS,
l_last_row.PARENT_PAY_TRANSACTION_ID,
l_last_row.PRIOR_PAY_PROPOSAL_ID,
l_last_row.PRIOR_PAY_TRANSACTION_ID,
l_last_row.APPROVED,
l_last_row.OBJECT_VERSION_NUMBER);
for rec_update_comps in csr_update_comps(l_last_row.pay_proposal_id) loop
--
insert into per_pay_transactions
(PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
TRANSACTION_ID, -- TRANSACTION_ID,
TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
ITEM_TYPE,-- ITEM_TYPE,
ITEM_KEY,-- ITEM_KEY,
PAY_PROPOSAL_ID,
ASSIGNMENT_ID,-- ASSIGNMENT_ID,
COMPONENT_ID,-- COMPONENT_ID,
REASON,-- REASON,
PAY_BASIS_ID,-- PAY_BASIS_ID,
BUSINESS_GROUP_ID,
CHANGE_DATE,
DATE_TO,
PROPOSED_SALARY_N,
change_amount_n,
change_percentage,
STATUS,-- STATUS,
DML_OPERATION,-- DML_OPERATION,
COMMENTS,
PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
MULTIPLE_COMPONENTS,
PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
APPROVED,
object_version_number
)
values(PER_PAY_TRANSACTIONS_S.NEXTVAL ,--PAY_TRANSACTION_ID,
p_transaction_id, -- TRANSACTION_ID,
p_transaction_step_id,-- TRANSACTION_STEP_ID,
p_item_type,-- ITEM_TYPE,
p_item_key,-- ITEM_KEY,
rec_update_comps.PAY_PROPOSAL_ID,
l_last_row.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
rec_update_comps.COMPONENT_ID,
rec_update_comps.component_reason,-- REASON,
l_last_row.PAY_BASIS_ID,-- PAY_BASIS_ID,
l_last_row.BUSINESS_GROUP_ID,
null,
null, --update last recs date_to to curr_rec
null,-- proposed_salary_n,
rec_update_comps.CHANGE_AMOUNT_N,-- change_amount_n,
rec_update_comps.CHANGE_PERCENTAGE, -- change_percentage,
'DELETE',-- STATUS,
'DELETE',-- DML_OPERATION,
rec_update_comps.comments,
null, --
null, --l_last_row.PRIOR_PAY_BASIS_ID,
rec_update_comps.ATTRIBUTE_CATEGORY,
rec_update_comps.ATTRIBUTE1,
rec_update_comps.ATTRIBUTE2,
rec_update_comps.ATTRIBUTE3,
rec_update_comps.ATTRIBUTE4,
rec_update_comps.ATTRIBUTE5,
rec_update_comps.ATTRIBUTE6,
rec_update_comps.ATTRIBUTE7,
rec_update_comps.ATTRIBUTE8,
rec_update_comps.ATTRIBUTE9,
rec_update_comps.ATTRIBUTE10,
rec_update_comps.ATTRIBUTE11,
rec_update_comps.ATTRIBUTE12,
rec_update_comps.ATTRIBUTE13,
rec_update_comps.ATTRIBUTE14,
rec_update_comps.ATTRIBUTE15,
rec_update_comps.ATTRIBUTE16,
rec_update_comps.ATTRIBUTE17,
rec_update_comps.ATTRIBUTE18,
rec_update_comps.ATTRIBUTE19,
rec_update_comps.ATTRIBUTE20,
null, --l_last_row.MULTIPLE_COMPONENTS,
l_seq_val, --l_last_row.PARENT_PAY_TRANSACTION_ID,
null, --l_last_row.PRIOR_PAY_PROPOSAL_ID,
null, --l_last_row.PRIOR_PAY_TRANSACTION_ID,
rec_update_comps.APPROVED,
rec_update_comps.OBJECT_VERSION_NUMBER
);
l_curr_rec_from := delete_recs.from_tab;
l_curr_date_to := delete_recs.date_to;
l_curr_rec_proposal_id := delete_recs.pay_proposal_id;
update per_pay_transactions
set date_to = l_curr_date_to
where pay_transaction_id = l_last_trans_id;
hr_utility.set_location('Inserting last rec from PROPO:'|| l_proc, 120);
select PER_PAY_TRANSACTIONS_S.NEXTVAL into l_seq_val from dual;--replace by Seq number
insert into per_pay_transactions
(PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
TRANSACTION_ID, -- TRANSACTION_ID,
TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
ITEM_TYPE,-- ITEM_TYPE,
ITEM_KEY,-- ITEM_KEY,
PAY_PROPOSAL_ID,
ASSIGNMENT_ID,-- ASSIGNMENT_ID,
COMPONENT_ID,-- COMPONENT_ID,
REASON,-- REASON,
PAY_BASIS_ID,-- PAY_BASIS_ID,
BUSINESS_GROUP_ID,
CHANGE_DATE,
DATE_TO,
last_change_date,
PROPOSED_SALARY_N,
change_amount_n,
change_percentage,
STATUS,-- STATUS,
DML_OPERATION,-- DML_OPERATION,
PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
MULTIPLE_COMPONENTS,
PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
APPROVED, -- APPROVED
object_version_number)
values(l_seq_val ,--PAY_TRANSACTION_ID,
p_transaction_id, -- TRANSACTION_ID,
p_transaction_step_id,-- TRANSACTION_STEP_ID,
p_item_type,-- ITEM_TYPE,
p_item_key,-- ITEM_KEY,
l_last_row.PAY_PROPOSAL_ID,
l_last_row.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
l_last_row.COMPONENT_ID,
l_last_row.REASON,-- REASON,
l_last_row.PAY_BASIS_ID,-- PAY_BASIS_ID,
l_last_row.BUSINESS_GROUP_ID,
l_last_row.CHANGE_DATE,
l_curr_date_to, --update last recs date_to to curr_rec
l_last_row.last_change_date,
l_last_row.PROPOSED_SALARY_N, -- proposed_salary_n,
l_last_row.change_amount_n, -- change_amount_n,
l_last_row.change_percentage, -- change_percentage,
'DATE_ADJUSTED',-- STATUS,
'UPDATE',-- DML_OPERATION,
l_last_row.PRIOR_PROPOSED_SALARY_N,
l_last_row.PRIOR_PAY_BASIS_ID,
l_last_row.ATTRIBUTE_CATEGORY,
l_last_row.ATTRIBUTE1,
l_last_row.ATTRIBUTE2,
l_last_row.ATTRIBUTE3,
l_last_row.ATTRIBUTE4,
l_last_row.ATTRIBUTE5,
l_last_row.ATTRIBUTE6,
l_last_row.ATTRIBUTE7,
l_last_row.ATTRIBUTE8,
l_last_row.ATTRIBUTE9,
l_last_row.ATTRIBUTE10,
l_last_row.ATTRIBUTE11,
l_last_row.ATTRIBUTE12,
l_last_row.ATTRIBUTE13,
l_last_row.ATTRIBUTE14,
l_last_row.ATTRIBUTE15,
l_last_row.ATTRIBUTE16,
l_last_row.ATTRIBUTE17,
l_last_row.ATTRIBUTE18,
l_last_row.ATTRIBUTE19,
l_last_row.ATTRIBUTE20,
l_last_row.MULTIPLE_COMPONENTS,
l_last_row.PARENT_PAY_TRANSACTION_ID,
l_last_row.PRIOR_PAY_PROPOSAL_ID,
l_last_row.PRIOR_PAY_TRANSACTION_ID,
l_last_row.APPROVED,
l_last_row.OBJECT_VERSION_NUMBER
);
for rec_update_comps in csr_update_comps(l_last_row.pay_proposal_id) loop
insert into per_pay_transactions
(PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
TRANSACTION_ID, -- TRANSACTION_ID,
TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
ITEM_TYPE,-- ITEM_TYPE,
ITEM_KEY,-- ITEM_KEY,
PAY_PROPOSAL_ID,
ASSIGNMENT_ID,-- ASSIGNMENT_ID,
COMPONENT_ID,-- COMPONENT_ID,
REASON,-- REASON,
PAY_BASIS_ID,-- PAY_BASIS_ID,
BUSINESS_GROUP_ID,
CHANGE_DATE,
DATE_TO,
PROPOSED_SALARY_N,
change_amount_n,
change_percentage,
STATUS,-- STATUS,
DML_OPERATION,-- DML_OPERATION,
COMMENTS,
PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
MULTIPLE_COMPONENTS,
PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
APPROVED,
object_version_number
)
values(PER_PAY_TRANSACTIONS_S.NEXTVAL ,--PAY_TRANSACTION_ID,
p_transaction_id, -- TRANSACTION_ID,
p_transaction_step_id,-- TRANSACTION_STEP_ID,
p_item_type,-- ITEM_TYPE,
p_item_key,-- ITEM_KEY,
rec_update_comps.PAY_PROPOSAL_ID,
l_last_row.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
rec_update_comps.COMPONENT_ID,
rec_update_comps.component_reason,-- REASON,
l_last_row.PAY_BASIS_ID,-- PAY_BASIS_ID,
l_last_row.BUSINESS_GROUP_ID,
null,
null, --update last recs date_to to curr_rec
null,-- proposed_salary_n,
rec_update_comps.CHANGE_AMOUNT_N,-- change_amount_n,
rec_update_comps.CHANGE_PERCENTAGE, -- change_percentage,
'DATE_ADJUSTED',-- STATUS,
'UPDATE',-- DML_OPERATION,
rec_update_comps.comments,
null, --
null, --l_last_row.PRIOR_PAY_BASIS_ID,
rec_update_comps.ATTRIBUTE_CATEGORY,
rec_update_comps.ATTRIBUTE1,
rec_update_comps.ATTRIBUTE2,
rec_update_comps.ATTRIBUTE3,
rec_update_comps.ATTRIBUTE4,
rec_update_comps.ATTRIBUTE5,
rec_update_comps.ATTRIBUTE6,
rec_update_comps.ATTRIBUTE7,
rec_update_comps.ATTRIBUTE8,
rec_update_comps.ATTRIBUTE9,
rec_update_comps.ATTRIBUTE10,
rec_update_comps.ATTRIBUTE11,
rec_update_comps.ATTRIBUTE12,
rec_update_comps.ATTRIBUTE13,
rec_update_comps.ATTRIBUTE14,
rec_update_comps.ATTRIBUTE15,
rec_update_comps.ATTRIBUTE16,
rec_update_comps.ATTRIBUTE17,
rec_update_comps.ATTRIBUTE18,
rec_update_comps.ATTRIBUTE19,
rec_update_comps.ATTRIBUTE20,
null, --l_last_row.MULTIPLE_COMPONENTS,
l_seq_val, --l_last_row.PARENT_PAY_TRANSACTION_ID,
null, --l_last_row.PRIOR_PAY_PROPOSAL_ID,
null, --l_last_row.PRIOR_PAY_TRANSACTION_ID,
rec_update_comps.APPROVED,
rec_update_comps.OBJECT_VERSION_NUMBER
);
if delete_recs.from_tab = 'TRANSACTION' then
--
--
if g_debug then
hr_utility.set_location('Entering curr rec from TRANS:'|| l_proc, 100);
if delete_recs.pay_proposal_id is null then
--
l_last_change_date_curr := delete_recs.last_change_date;
delete from per_pay_transactions
where parent_pay_transaction_id = delete_recs.pay_transaction_id;
delete from per_pay_transactions
where pay_transaction_id = delete_recs.pay_transaction_id;
l_last_change_date_curr := delete_recs.last_change_date;
update per_pay_transactions
set STATUS = 'DELETE',
DML_OPERATION = 'DELETE'
where parent_pay_transaction_id = delete_recs.pay_transaction_id;
update per_pay_transactions
set STATUS = 'DELETE',
DML_OPERATION = 'DELETE'
where pay_transaction_id = delete_recs.pay_transaction_id;
select PER_PAY_TRANSACTIONS_S.NEXTVAL into l_seq_val from dual; --replace by Seq number
hr_utility.set_location('Inserting curr rec PROPOSAL:'|| l_proc, 110);
l_last_change_date_curr := delete_recs.last_change_date;
insert into per_pay_transactions
(PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
TRANSACTION_ID, -- TRANSACTION_ID,
TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
ITEM_TYPE,-- ITEM_TYPE,
ITEM_KEY,-- ITEM_KEY,
PAY_PROPOSAL_ID,
ASSIGNMENT_ID,-- ASSIGNMENT_ID,
COMPONENT_ID,-- COMPONENT_ID,
REASON,-- REASON,
PAY_BASIS_ID,-- PAY_BASIS_ID,
BUSINESS_GROUP_ID,
CHANGE_DATE,
DATE_TO,
last_change_date,
PROPOSED_SALARY_N,
change_amount_n,
change_percentage,
STATUS,-- STATUS,
DML_OPERATION,-- DML_OPERATION,
PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
MULTIPLE_COMPONENTS,
PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
APPROVED,-- APPROVED
object_version_number)
values(l_seq_val ,--PAY_TRANSACTION_ID,
p_transaction_id, -- TRANSACTION_ID,
p_transaction_step_id,-- TRANSACTION_STEP_ID,
p_item_type,-- ITEM_TYPE,
p_item_key,-- ITEM_KEY,
delete_recs.PAY_PROPOSAL_ID,
delete_recs.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
delete_recs.COMPONENT_ID,
delete_recs.REASON,-- REASON,
delete_recs.PAY_BASIS_ID,-- PAY_BASIS_ID,
delete_recs.BUSINESS_GROUP_ID,
delete_recs.CHANGE_DATE,
delete_recs.DATE_TO,
delete_recs.last_change_date,
delete_recs.PROPOSED_SALARY_N,
delete_recs.change_amount_n,
delete_recs.change_percentage,
'DELETE',-- STATUS,
'DELETE',-- DML_OPERATION,
delete_recs.PRIOR_PROPOSED_SALARY_N,
delete_recs.PRIOR_PAY_BASIS_ID,
delete_recs.ATTRIBUTE_CATEGORY,
delete_recs.ATTRIBUTE1,
delete_recs.ATTRIBUTE2,
delete_recs.ATTRIBUTE3,
delete_recs.ATTRIBUTE4,
delete_recs.ATTRIBUTE5,
delete_recs.ATTRIBUTE6,
delete_recs.ATTRIBUTE7,
delete_recs.ATTRIBUTE8,
delete_recs.ATTRIBUTE9,
delete_recs.ATTRIBUTE10,
delete_recs.ATTRIBUTE11,
delete_recs.ATTRIBUTE12,
delete_recs.ATTRIBUTE13,
delete_recs.ATTRIBUTE14,
delete_recs.ATTRIBUTE15,
delete_recs.ATTRIBUTE16,
delete_recs.ATTRIBUTE17,
delete_recs.ATTRIBUTE18,
delete_recs.ATTRIBUTE19,
delete_recs.ATTRIBUTE20,
delete_recs.MULTIPLE_COMPONENTS,
delete_recs.PARENT_PAY_TRANSACTION_ID,
delete_recs.PRIOR_PAY_PROPOSAL_ID,
delete_recs.PRIOR_PAY_TRANSACTION_ID,
delete_recs.APPROVED,
delete_recs.OBJECT_VERSION_NUMBER
);
if delete_recs.MULTIPLE_COMPONENTS = 'Y' then
--
for rec_update_comps in csr_update_comps(delete_recs.pay_proposal_id) loop
insert into per_pay_transactions
(PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
TRANSACTION_ID, -- TRANSACTION_ID,
TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
ITEM_TYPE,-- ITEM_TYPE,
ITEM_KEY,-- ITEM_KEY,
PAY_PROPOSAL_ID,
ASSIGNMENT_ID,-- ASSIGNMENT_ID,
COMPONENT_ID,-- COMPONENT_ID,
REASON,-- REASON,
PAY_BASIS_ID,-- PAY_BASIS_ID,
BUSINESS_GROUP_ID,
CHANGE_DATE,
DATE_TO,
PROPOSED_SALARY_N,
change_amount_n,
change_percentage,
STATUS,-- STATUS,
DML_OPERATION,-- DML_OPERATION,
COMMENTS,
PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
MULTIPLE_COMPONENTS,
PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
APPROVED,
object_version_number
)
values(PER_PAY_TRANSACTIONS_S.NEXTVAL ,--PAY_TRANSACTION_ID,
p_transaction_id, -- TRANSACTION_ID,
p_transaction_step_id,-- TRANSACTION_STEP_ID,
p_item_type,-- ITEM_TYPE,
p_item_key,-- ITEM_KEY,
rec_update_comps.PAY_PROPOSAL_ID,
delete_recs.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
rec_update_comps.COMPONENT_ID,
rec_update_comps.component_reason,-- REASON,
delete_recs.PAY_BASIS_ID,-- PAY_BASIS_ID,
delete_recs.BUSINESS_GROUP_ID,
null,
null, --update last recs date_to to curr_rec
null,-- proposed_salary_n,
rec_update_comps.CHANGE_AMOUNT_N,-- change_amount_n,
rec_update_comps.CHANGE_PERCENTAGE, -- change_percentage,
'DELETE',-- STATUS,
'DELETE',-- DML_OPERATION,
rec_update_comps.comments,
null, --
null, --l_last_row.PRIOR_PAY_BASIS_ID,
rec_update_comps.ATTRIBUTE_CATEGORY,
rec_update_comps.ATTRIBUTE1,
rec_update_comps.ATTRIBUTE2,
rec_update_comps.ATTRIBUTE3,
rec_update_comps.ATTRIBUTE4,
rec_update_comps.ATTRIBUTE5,
rec_update_comps.ATTRIBUTE6,
rec_update_comps.ATTRIBUTE7,
rec_update_comps.ATTRIBUTE8,
rec_update_comps.ATTRIBUTE9,
rec_update_comps.ATTRIBUTE10,
rec_update_comps.ATTRIBUTE11,
rec_update_comps.ATTRIBUTE12,
rec_update_comps.ATTRIBUTE13,
rec_update_comps.ATTRIBUTE14,
rec_update_comps.ATTRIBUTE15,
rec_update_comps.ATTRIBUTE16,
rec_update_comps.ATTRIBUTE17,
rec_update_comps.ATTRIBUTE18,
rec_update_comps.ATTRIBUTE19,
rec_update_comps.ATTRIBUTE20,
null, --l_last_row.MULTIPLE_COMPONENTS,
l_seq_val, --l_last_row.PARENT_PAY_TRANSACTION_ID,
null, --l_last_row.PRIOR_PAY_PROPOSAL_ID,
null, --l_last_row.PRIOR_PAY_TRANSACTION_ID,
rec_update_comps.APPROVED,
rec_update_comps.OBJECT_VERSION_NUMBER
);
update_transaction(p_assgn_id, p_transaction_id, l_changedt_last,l_last_change_date_curr, p_busgroup_id);
delete from per_pay_transactions
where PAY_TRANSACTION_ID = l_last_row.PAY_TRANSACTION_ID;
End delete_transaction;
function update_component_transaction(p_pay_transaction_id Number
,p_ASSIGNMENT_ID Number
,p_change_date date
,p_prior_proposed_salary Number default Null
,p_prior_proposal_id Number default Null
,p_prior_transaction_id Number default Null
,p_prior_pay_basis_id Number default Null
,p_update_prior varchar2 default 'N'
,p_xchg_rate in Number
)
return Number
IS
cursor csr_update_comp(p_pay_transaction_id number,p_ASSIGNMENT_ID number,p_change_date date)
IS
Select
ppt.pay_transaction_id,
ppt.PROPOSED_SALARY_N,
ppt.CHANGE_AMOUNT_N,
ppt.CHANGE_PERCENTAGE
from per_pay_transactions ppt
where ppt.PARENT_PAY_TRANSACTION_ID = p_pay_transaction_id
AND ppt.assignment_id = p_ASSIGNMENT_ID
--AND ppt.change_date = p_change_date
AND ppt.status <> 'DELETE';
l_proc varchar2(72) := g_package||'update_component_transaction';
for update_comp_recs in csr_update_comp (p_pay_transaction_id,
p_ASSIGNMENT_ID,
p_change_date) loop
--
--computing the change amount for each component and storing it
l_change_amount_comp := l_change_amount_comp + (update_comp_recs.change_percentage * p_prior_proposed_salary*p_xchg_rate/100);
if p_update_prior = 'Y' then
--
--
if g_debug then
hr_utility.set_location('Entering: prior Update:p_prior_transaction_id'||p_prior_transaction_id, 10);
update per_pay_transactions
set change_amount_n = (update_comp_recs.change_percentage * p_prior_proposed_salary*p_xchg_rate/100),
PRIOR_PROPOSED_SALARY_N = p_prior_proposed_salary,
PRIOR_PAY_PROPOSAL_ID = p_prior_proposal_id,
PRIOR_PAY_TRANSACTION_ID = p_prior_transaction_id,
PRIOR_PAY_BASIS_ID = p_prior_pay_basis_id
where PAY_TRANSACTION_ID = update_comp_recs.PAY_TRANSACTION_ID
--and change_date = p_change_date
and assignment_id = p_ASSIGNMENT_ID;
hr_utility.set_location('Entering: Else of prior Update'||l_proc, 10);
update per_pay_transactions
set change_amount_n = (update_comp_recs.change_percentage * p_prior_proposed_salary*p_xchg_rate/100)
where PAY_TRANSACTION_ID = update_comp_recs.PAY_TRANSACTION_ID
--and change_date = p_change_date
and assignment_id = p_ASSIGNMENT_ID;
end update_component_transaction;
PROCEDURE update_transaction(p_assgn_id IN number,
p_transaction_id IN number,
p_changedate_curr IN date,
p_last_change_date IN date,
p_busgroup_id IN number)
IS
cursor csr_update_recs(c_assgn_id number, c_changedate_curr date, c_transaction_id number) is
--cursor to fetch data from transactions which needs to be updated
Select
ppt.pay_transaction_id,
ppt.pay_proposal_id,
ppt.pay_basis_id,
ppt.assignment_id,
ppt.change_date,
ppt.last_change_date,
ppt.MULTIPLE_COMPONENTS,
ppt.PROPOSED_SALARY_N,
ppt.CHANGE_AMOUNT_N,
ppt.CHANGE_PERCENTAGE,
ppt.PRIOR_PROPOSED_SALARY_N,
ppt.PRIOR_PAY_BASIS_ID,
ppt.PARENT_PAY_TRANSACTION_ID,
ppt.PRIOR_PAY_PROPOSAL_ID,
ppt.PRIOR_PAY_TRANSACTION_ID,
pet.input_currency_code,
ppt.object_version_number
from per_pay_transactions ppt,
per_pay_bases ppb,
pay_input_values_f piv,
pay_element_types_f pet
where ppt.assignment_id = c_assgn_id
AND ppt.PARENT_PAY_TRANSACTION_ID is null
AND ppt.TRANSACTION_ID = c_transaction_id
AND ppt.change_date >= c_changedate_curr
AND ppb.pay_basis_id = ppt.pay_basis_id
AND ppb.input_value_id = piv.input_value_id
AND ppt.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.element_type_id = pet.element_type_id
AND ppt.change_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppt.status <> 'DELETE'
--where ppt.assignment_id = c_assgn_id
-- AND ppt.TRANSACTION_ID = c_transaction_id
-- AND ppt.change_date >= c_changedate_curr
-- AND ppt.status <> 'DELETE'
-- AND ppt.PARENT_PAY_TRANSACTION_ID is null
order by change_date asc;
l_update_rec csr_update_recs%rowtype;
l_proc varchar2(72) := g_package||'update_transaction';
for update_recs in csr_update_recs(p_assgn_id, p_changedate_curr, p_transaction_id) loop
--
if g_debug then
hr_utility.set_location(l_proc, 25);
l_prior_trans_id := update_recs.pay_transaction_id;
l_prior_proposal_id := update_recs.pay_proposal_id;
l_prior_proposed_sal := update_recs.PROPOSED_SALARY_N;
l_prior_pay_basis_id := update_recs.pay_basis_id;
l_last_change_date := update_recs.change_date;
l_last_currency := update_recs.input_currency_code;
update per_pay_transactions
set CHANGE_PERCENTAGE = null,
CHANGE_AMOUNT_N = 0
where parent_pay_transaction_id = l_prior_trans_id;
update per_pay_transactions
set CHANGE_AMOUNT_N = l_prior_proposed_sal,
CHANGE_PERCENTAGE = null,
last_change_date = null,
PRIOR_PAY_PROPOSAL_ID = null,
PRIOR_PAY_TRANSACTION_ID = null,
PRIOR_PROPOSED_SALARY_N = 0
-- PRIOR_PAY_BASIS_ID = null
where pay_transaction_id = l_prior_trans_id;
if update_recs.MULTIPLE_COMPONENTS = 'N' then
--
if g_debug then
hr_utility.set_location('No MULTIPLE_COMPONENTS '||l_prior_proposed_sal||l_proc, 25);
if l_last_currency <> update_recs.input_currency_code then
select PER_SALADMIN_UTILITY.get_currency_rate(l_last_currency,update_recs.input_currency_code,update_recs.change_date,p_busgroup_id) into l_xchg_rate
from dual;
update per_pay_transactions
set PRIOR_PROPOSED_SALARY_N = l_prior_proposed_sal,
PRIOR_PAY_PROPOSAL_ID = l_prior_proposal_id,
PRIOR_PAY_TRANSACTION_ID = l_prior_trans_id,
PRIOR_PAY_BASIS_ID = l_prior_pay_basis_id,
last_change_date = l_last_change_date,
CHANGE_PERCENTAGE = round(((update_recs.proposed_salary_n - (l_prior_proposed_sal*l_xchg_rate))/(l_prior_proposed_sal*l_xchg_rate) * 100), 6),
CHANGE_AMOUNT_N = (update_recs.proposed_salary_n - (l_prior_proposed_sal*l_xchg_rate))
where PAY_TRANSACTION_ID = update_recs.PAY_TRANSACTION_ID;
if l_last_currency <> update_recs.input_currency_code then
select PER_SALADMIN_UTILITY.get_currency_rate(l_last_currency,update_recs.input_currency_code,update_recs.change_date,p_busgroup_id) into l_xchg_rate
from dual;
l_change_amount := update_component_transaction(update_recs.pay_transaction_id,
update_recs.ASSIGNMENT_ID,
update_recs.change_date,
l_prior_proposed_sal,
l_prior_proposal_id,
l_prior_trans_id,
l_prior_pay_basis_id,
'Y',
l_xchg_rate);
update per_pay_transactions
set PRIOR_PROPOSED_SALARY_N = l_prior_proposed_sal,
PRIOR_PAY_PROPOSAL_ID = l_prior_proposal_id,
PRIOR_PAY_TRANSACTION_ID = l_prior_trans_id,
PRIOR_PAY_BASIS_ID = l_prior_pay_basis_id,
last_change_date = l_last_change_date,
PROPOSED_SALARY_N = (l_prior_proposed_sal*l_xchg_rate+l_change_amount),
change_amount_n = l_change_amount,
CHANGE_PERCENTAGE = round((l_change_amount/(l_prior_proposed_sal*l_xchg_rate) * 100), 6)
where PAY_TRANSACTION_ID = update_recs.PAY_TRANSACTION_ID;
if update_recs.MULTIPLE_COMPONENTS = 'N' then
--
if g_debug then
hr_utility.set_location(l_proc||'No MULTIPLE_COMPONENTS'||l_count, 25);
if l_last_currency <> update_recs.input_currency_code then
select PER_SALADMIN_UTILITY.get_currency_rate(l_last_currency,update_recs.input_currency_code,update_recs.change_date,p_busgroup_id) into l_xchg_rate
from dual;
update per_pay_transactions
set CHANGE_PERCENTAGE = round(((update_recs.proposed_salary_n - (l_prior_proposed_sal*l_xchg_rate))/(l_prior_proposed_sal*l_xchg_rate) * 100), 6),
CHANGE_AMOUNT_N = (update_recs.proposed_salary_n - (l_prior_proposed_sal*l_xchg_rate))
where PAY_TRANSACTION_ID = update_recs.PAY_TRANSACTION_ID;
if l_last_currency <> update_recs.input_currency_code then
select PER_SALADMIN_UTILITY.get_currency_rate(l_last_currency,update_recs.input_currency_code,update_recs.change_date,p_busgroup_id) into l_xchg_rate
from dual;
l_change_amount := update_component_transaction(update_recs.pay_transaction_id,
update_recs.ASSIGNMENT_ID,
update_recs.change_date,
l_prior_proposed_sal,
l_prior_proposal_id,
l_prior_trans_id,
l_prior_pay_basis_id,
'Y',
l_xchg_rate);
update per_pay_transactions
set PROPOSED_SALARY_N = (PRIOR_PROPOSED_SALARY_N*l_xchg_rate + l_change_amount),
CHANGE_AMOUNT_N = l_change_amount,
CHANGE_PERCENTAGE = round((l_change_amount/(prior_proposed_salary_n*l_xchg_rate)*100), 6)
where PAY_TRANSACTION_ID = update_recs.PAY_TRANSACTION_ID;
End update_transaction;
cursor csr_rows_to_be_deleted(c_item_type in varchar2, c_item_key in varchar2, c_assgn_id in number) is
select trans.pay_basis_id,
trans.pay_transaction_id
from per_pay_transactions trans,
hr_api_transaction_steps tr_steps,
hr_api_transaction_values tr_values,
hr_api_transaction_values tr_values2
where trans.assignment_id = c_assgn_id
and trans.item_type = c_item_type
and trans.item_key = c_item_key
and tr_steps.item_type = c_item_type
and tr_steps.item_key = c_item_key
and tr_steps . api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
and tr_values.TRANSACTION_STEP_ID = tr_steps.transaction_step_id
and tr_values2.TRANSACTION_STEP_ID = tr_steps.TRANSACTION_STEP_ID
and tr_values.name = 'P_EFFECTIVE_DATE'
and tr_values.date_value between trans.change_date and trans.date_to
and tr_values2.name = 'P_PAY_BASIS_ID'
and tr_values2.number_value <> trans.pay_basis_id;
select trans.pay_basis_id
from per_pay_transactions trans,
per_all_assignments_f asg
where trans.assignment_id = c_assgn_id
and trans.item_type = c_item_type
and trans.item_key = c_item_key
and asg.assignment_id = trans.assignment_id
and asg.pay_basis_id <> trans.pay_basis_id
and trans.change_date between asg.effective_start_date and asg.effective_end_date
and not exists ( select '1'
from hr_api_transaction_steps
where api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
and item_type = c_item_type
and item_key = c_item_key );
open csr_rows_to_be_deleted(p_item_type, p_item_key, p_assignment_id);
fetch csr_rows_to_be_deleted into l_pay_basis_id, l_pay_trans_id;
if (csr_rows_to_be_deleted%found AND l_pay_trans_id is not null) then
--
delete from per_pay_transactions
where item_key = p_item_key
and item_type = p_item_type;
delete from per_pay_transactions
where item_key = p_item_key
and item_type = p_item_type;
close csr_rows_to_be_deleted;
p_update_mode out nocopy varchar2,
p_effective_date_option in varchar2)
IS
l_update_mode boolean;
p_update_mode => l_update_mode);
if l_update_mode then
p_update_mode:='Y';
p_update_mode:='N';
p_update_mode out nocopy boolean) IS
--
cursor csr_txn_step is
select hats.transaction_step_id
from hr_api_transaction_steps hats
where hats.item_type = p_item_type
and hats.item_key = p_item_key
-- and hats.activity_id = p_activity_id
and hats.api_name = upper(p_api_name)
order by hats.transaction_step_id;
p_update_mode := true;
p_update_mode := false;
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)
Values
( hr_api_transaction_values_s.nextval,
l_transaction_step_id,
l_tx_type(i),
l_tx_name(i),
l_tx_char(i),
l_tx_num(i),
l_tx_date(i),
l_tx_char(i),
l_tx_num(i),
l_tx_date(i));
Select transaction_step_id,api_name
from hr_api_transaction_steps
where transaction_id = (Select transaction_id
from hr_api_transaction_steps
Where transaction_step_id = p_transaction_step_id)
and api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';
Cursor csr_insert_pay is
Select * from per_pay_transactions
where transaction_step_id = p_transaction_step_id
and dml_operation = 'INSERT'
and PARENT_PAY_TRANSACTION_ID is null
order by CHANGE_DATE;
Cursor csr_insert_comp is
Select * from per_pay_transactions
where transaction_step_id = p_transaction_step_id
and dml_operation = 'INSERT'
and PARENT_PAY_TRANSACTION_ID is not null
order by PARENT_PAY_TRANSACTION_ID;
Select item_type,item_key
from hr_api_transaction_steps
where transaction_step_id = p_transaction_step_id;
Select TRANSACTION_EFFECTIVE_DATE, EFFECTIVE_DATE_OPTION
From hr_api_transactions
Where transaction_id = (Select transaction_id from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id);
hr_new_user_reg_ss.process_selected_transaction
(p_item_type => l_item_type,
p_item_key => l_item_key);
For l_pay_rec in csr_insert_pay loop
--
If l_g_assignment_id is not null THEN
l_assignment_id := l_g_assignment_id;
hr_maintain_proposal_api.insert_salary_proposal(
p_pay_proposal_id => l_pay_proposal_id,
p_assignment_id => l_assignment_id,
p_business_group_id => l_pay_rec.business_group_id,
p_change_date => l_change_date,
p_comments => l_pay_rec.comments,
p_next_sal_review_date => l_pay_rec.next_sal_review_date,
p_proposal_reason => l_pay_rec.reason,
p_proposed_salary_n => l_pay_rec.proposed_salary_n,
p_date_to => l_pay_rec.date_to ,
p_attribute_category => l_pay_rec.attribute_category,
p_attribute1 => l_pay_rec.attribute1,
p_attribute2 => l_pay_rec.attribute2,
p_attribute3 => l_pay_rec.attribute3,
p_attribute4 => l_pay_rec.attribute4,
p_attribute5 => l_pay_rec.attribute5,
p_attribute6 => l_pay_rec.attribute6,
p_attribute7 => l_pay_rec.attribute7,
p_attribute8 => l_pay_rec.attribute8,
p_attribute9 => l_pay_rec.attribute9,
p_attribute10 => l_pay_rec.attribute10,
p_attribute11 => l_pay_rec.attribute11,
p_attribute12 => l_pay_rec.attribute12,
p_attribute13 => l_pay_rec.attribute13,
p_attribute14 => l_pay_rec.attribute14,
p_attribute15 => l_pay_rec.attribute15,
p_attribute16 => l_pay_rec.attribute16,
p_attribute17 => l_pay_rec.attribute17,
p_attribute18 => l_pay_rec.attribute18,
p_attribute19 => l_pay_rec.attribute19,
p_attribute20 => l_pay_rec.attribute20,
p_object_version_number => l_pay_ovn,
p_multiple_components => l_pay_rec.multiple_components,
p_approved => 'Y',
p_validate => FALSE,
p_element_entry_id => l_element_entry_id,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning);
Update per_pay_transactions
set PAY_PROPOSAL_ID = l_pay_proposal_id
Where transaction_step_id = p_transaction_step_id
and PARENT_PAY_TRANSACTION_ID = l_pay_rec.pay_transaction_id;
For l_comp_rec in csr_insert_comp loop
--
hr_maintain_proposal_api.insert_proposal_component(
p_component_id => l_component_id ,
p_pay_proposal_id => l_comp_rec.pay_proposal_id,
p_business_group_id => l_comp_rec.business_group_id ,
p_approved => l_comp_rec.approved,
p_component_reason => l_comp_rec.reason,
p_change_amount_n => l_comp_rec.change_amount_n,
p_change_percentage => l_comp_rec.change_percentage,
p_comments => l_comp_rec.comments,
p_attribute_category => l_comp_rec.attribute_category,
p_attribute1 => l_comp_rec.attribute1,
p_attribute2 => l_comp_rec.attribute2,
p_attribute3 => l_comp_rec.attribute3,
p_attribute4 => l_comp_rec.attribute4,
p_attribute5 => l_comp_rec.attribute5,
p_attribute6 => l_comp_rec.attribute6,
p_attribute7 => l_comp_rec.attribute7,
p_attribute8 => l_comp_rec.attribute8,
p_attribute9 => l_comp_rec.attribute9,
p_attribute10 => l_comp_rec.attribute10,
p_attribute11 => l_comp_rec.attribute11,
p_attribute12 => l_comp_rec.attribute12,
p_attribute13 => l_comp_rec.attribute13,
p_attribute14 => l_comp_rec.attribute14,
p_attribute15 => l_comp_rec.attribute15,
p_attribute16 => l_comp_rec.attribute16,
p_attribute17 => l_comp_rec.attribute17,
p_attribute18 => l_comp_rec.attribute18,
p_attribute19 => l_comp_rec.attribute19,
p_attribute20 => l_comp_rec.attribute20,
p_object_version_number => l_comp_ovn,
p_validation_strength => 'STRONG',
p_validate => FALSE);
Procedure process_update_pay_action(
p_transaction_step_id in number) IS
--
--
Cursor csr_update_pay is
Select * from per_pay_transactions
where transaction_step_id = p_transaction_step_id
and dml_operation = 'UPDATE'
and PARENT_PAY_TRANSACTION_ID is null
order by CHANGE_DATE desc;
Cursor csr_update_comp is
Select * from per_pay_transactions
where transaction_step_id = p_transaction_step_id
and dml_operation = 'UPDATE'
and PARENT_PAY_TRANSACTION_ID is not null
order by PARENT_PAY_TRANSACTION_ID;
l_proc varchar2(61) := 'process_update_pay_action' ;
For l_pay_rec in csr_update_pay loop
--
-- Query update pay actions.
-- Call Update API to Update salary proposal record.
--
Select object_version_number into l_pay_ovn
From per_pay_proposals where pay_proposal_id = l_pay_rec.pay_proposal_id;
hr_maintain_proposal_api.update_salary_proposal(
p_pay_proposal_id => l_pay_rec.pay_proposal_id,
p_change_date => l_pay_rec.change_date,
p_comments => l_pay_rec.comments,
p_next_sal_review_date => l_pay_rec.next_sal_review_date,
p_proposal_reason => l_pay_rec.reason,
p_proposed_salary_n => l_pay_rec.proposed_salary_n,
p_date_to => l_pay_rec.date_to ,
p_attribute_category => l_pay_rec.attribute_category,
p_attribute1 => l_pay_rec.attribute1,
p_attribute2 => l_pay_rec.attribute2,
p_attribute3 => l_pay_rec.attribute3,
p_attribute4 => l_pay_rec.attribute4,
p_attribute5 => l_pay_rec.attribute5,
p_attribute6 => l_pay_rec.attribute6,
p_attribute7 => l_pay_rec.attribute7,
p_attribute8 => l_pay_rec.attribute8,
p_attribute9 => l_pay_rec.attribute9,
p_attribute10 => l_pay_rec.attribute10,
p_attribute11 => l_pay_rec.attribute11,
p_attribute12 => l_pay_rec.attribute12,
p_attribute13 => l_pay_rec.attribute13,
p_attribute14 => l_pay_rec.attribute14,
p_attribute15 => l_pay_rec.attribute15,
p_attribute16 => l_pay_rec.attribute16,
p_attribute17 => l_pay_rec.attribute17,
p_attribute18 => l_pay_rec.attribute18,
p_attribute19 => l_pay_rec.attribute19,
p_attribute20 => l_pay_rec.attribute20,
p_object_version_number => l_pay_ovn,
p_multiple_components => l_pay_rec.multiple_components,
p_approved => 'Y',
p_validate => FALSE,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning);
For l_comp_rec in csr_update_comp loop
--
hr_maintain_proposal_api.update_proposal_component(
--
p_component_id => l_comp_rec.component_id ,
p_approved => l_comp_rec.approved,
p_component_reason => l_comp_rec.reason,
p_change_amount_n => l_comp_rec.change_amount_n,
p_change_percentage => l_comp_rec.change_percentage,
p_comments => l_comp_rec.comments,
p_attribute_category => l_comp_rec.attribute_category,
p_attribute1 => l_comp_rec.attribute1,
p_attribute2 => l_comp_rec.attribute2,
p_attribute3 => l_comp_rec.attribute3,
p_attribute4 => l_comp_rec.attribute4,
p_attribute5 => l_comp_rec.attribute5,
p_attribute6 => l_comp_rec.attribute6,
p_attribute7 => l_comp_rec.attribute7,
p_attribute8 => l_comp_rec.attribute8,
p_attribute9 => l_comp_rec.attribute9,
p_attribute10 => l_comp_rec.attribute10,
p_attribute11 => l_comp_rec.attribute11,
p_attribute12 => l_comp_rec.attribute12,
p_attribute13 => l_comp_rec.attribute13,
p_attribute14 => l_comp_rec.attribute14,
p_attribute15 => l_comp_rec.attribute15,
p_attribute16 => l_comp_rec.attribute16,
p_attribute17 => l_comp_rec.attribute17,
p_attribute18 => l_comp_rec.attribute18,
p_attribute19 => l_comp_rec.attribute19,
p_attribute20 => l_comp_rec.attribute20,
p_object_version_number => l_comp_ovn,
p_validation_strength => 'STRONG',
p_validate => FALSE);
End process_update_pay_action;
Procedure process_delete_pay_action(
p_transaction_step_id in number) IS
--
--
Cursor csr_delete_pay is
Select * from per_pay_transactions
where transaction_step_id = p_transaction_step_id
and dml_operation = 'DELETE'
and PARENT_PAY_TRANSACTION_ID is null
order by CHANGE_DATE;
Cursor csr_delete_comp is
Select * from per_pay_transactions
where transaction_step_id = p_transaction_step_id
and dml_operation = 'DELETE'
and PARENT_PAY_TRANSACTION_ID is not null
order by PARENT_PAY_TRANSACTION_ID;
l_proc varchar2(61) := 'process_delete_pay_action' ;
For l_comp_rec in csr_delete_comp loop
--
Select object_version_number into l_comp_ovn
From per_pay_proposal_components where component_id = l_comp_rec.component_id;
hr_maintain_proposal_api.delete_proposal_component(
p_component_id => l_comp_rec.component_id,
p_validation_strength => 'STRONG',
p_object_version_number => l_comp_ovn,
p_validate => FALSE);
For l_pay_rec in csr_delete_pay loop
--
Select object_version_number into l_pay_ovn
From per_pay_proposals where pay_proposal_id = l_pay_rec.pay_proposal_id;
hr_maintain_proposal_api.delete_salary_proposal
(p_pay_proposal_id => l_pay_rec.pay_proposal_id
,p_business_group_id => l_pay_rec.business_group_id
,p_object_version_number => l_pay_ovn
,p_validate => FALSE
,p_salary_warning => l_salary_warning);
End process_delete_pay_action;
process_delete_pay_action(
p_transaction_step_id => p_transaction_step_id);
hr_utility.set_location('After Deletes '||l_proc,10);
process_update_pay_action(
p_transaction_step_id => p_transaction_step_id);
hr_utility.set_location('After Updates '||l_proc,10);
hr_utility.set_location('After Inserts '||l_proc,10);
Delete from per_pay_transactions
where transaction_step_id = p_transaction_step_id;
process_delete_pay_action(
p_transaction_step_id => p_transaction_step_id);
hr_utility.set_location('After Deletes '||l_proc,10);
process_update_pay_action(
p_transaction_step_id => p_transaction_step_id);
hr_utility.set_location('After Updates '||l_proc,10);
hr_utility.set_location('After Inserts '||l_proc,10);
Delete from per_pay_transactions
where transaction_step_id = p_transaction_step_id;
select '1'
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;
select change_date
from per_pay_proposals
where assignment_id = p_assignment_id
union
select change_date
from per_pay_transactions
where assignment_id = p_assignment_id
and PARENT_PAY_TRANSACTION_ID is null
and status <> 'DELETE'
order by change_date desc;
select hatv1.date_value ,hatv.number_value, hatv.original_number_value
from hr_api_transaction_values hatv,
hr_api_transaction_steps hats,
hr_api_transactions hat,
hr_api_transaction_values hatv1
where hatv.NAME = 'P_PAY_BASIS_ID'
and hatv1.NAME = 'P_EFFECTIVE_DATE'
and hatv1.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
and hatv.NUMBER_VALUE <> hatv.ORIGINAL_NUMBER_VALUE
and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
and hats.TRANSACTION_ID = hat.TRANSACTION_ID
and hat.ASSIGNMENT_ID = p_assignment_id
and hat.TRANSACTION_ID = p_transaction_id
order by hatv1.date_value desc ;
select hatv.date_value
from hr_api_transaction_steps hats,
hr_api_transactions hat,
hr_api_transaction_values hatv
where hats.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
and hatv.transaction_step_id = hats.transaction_step_id
and hatv.name = 'P_EFFECTIVE_DATE'
and hats.TRANSACTION_ID = hat.TRANSACTION_ID
and hat.ASSIGNMENT_ID = p_assignment_id
and hat.TRANSACTION_ID = p_transaction_id;
select effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date > l_min_change_date
and effective_start_date < l_change_date
order by effective_start_date desc;
select effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
order by effective_start_date asc;
select effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date > l_max_change_date
order by effective_start_date asc;
select effective_start_date,pay_basis_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date > l_max_change_date
and pay_basis_id <> (Select pay_basis_id from per_all_assignments_f
where assignment_id = p_assignment_id
and l_max_change_date between effective_start_date
and effective_end_date)
order by effective_start_date asc;
select min(ptp.start_date) next_payroll_date
from per_time_periods ptp
,per_all_assignments_f paaf
where ptp.payroll_id = paaf.payroll_id
and paaf.assignment_id = l_assignment_id
and ptp.start_date > l_date ;
select pay_basis_id
from per_all_assignments_f
where assignment_id = c_assignment_id
and c_effective_date between effective_start_date and effective_end_date;
select hatv.number_value,
hatv.original_number_value
from hr_api_transaction_values hatv,
hr_api_transaction_steps hats,
hr_api_transactions hat
where hatv.NAME = 'P_PAY_BASIS_ID'
and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
and hats.TRANSACTION_ID = hat.TRANSACTION_ID
and hat.TRANSACTION_ID = p_transaction_id;
select pay_basis_id
from per_all_assignments_f
where assignment_id = c_assignment_id
and c_effective_date between effective_start_date and effective_end_date;
select change_date
from per_pay_proposals
where assignment_id = p_assignment_id
union
select change_date
from per_pay_transactions
where assignment_id = p_assignment_id
and PARENT_PAY_TRANSACTION_ID is null
and status <> 'DELETE'
order by change_date desc;
select hatv1.date_value ,hatv.number_value
from hr_api_transaction_values hatv,
hr_api_transaction_steps hats,
hr_api_transactions hat,
hr_api_transaction_values hatv1
where hatv.NAME = 'P_PAY_BASIS_ID'
and hatv1.NAME = 'P_EFFECTIVE_DATE'
and hatv1.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
and hatv.NUMBER_VALUE <> hatv.ORIGINAL_NUMBER_VALUE
and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
and hats.TRANSACTION_ID = hat.TRANSACTION_ID
and hat.ASSIGNMENT_ID = p_assignment_id
and hat.TRANSACTION_ID = p_transaction_id
order by hatv1.date_value desc ;
select effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date <= l_curr_change_date
order by effective_start_date desc;
select effective_start_date,pay_basis_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date > l_max_change_date
order by effective_start_date asc;
select min(ptp.start_date) next_payroll_date
from per_time_periods ptp
,per_all_assignments_f paaf
where ptp.payroll_id = paaf.payroll_id
and paaf.assignment_id = p_assignment_id
and ptp.start_date > p_effective_date ;
hr_utility.set_location('Selected p_effective_date '||p_effective_date, 10);
select nvl(DESCRIPTION,ptt.period_type)
from PER_TIME_PERIOD_TYPES ptt
,pay_all_payrolls_f pap
,per_all_assignments_f paa
where pap.payroll_id = p_payroll_id
and ptt.period_type = pap.period_type;
PROCEDURE get_update_param
( p_assignment_id in Number
, p_transaction_id in Number
, p_current_date in Date
, p_previous_date in Date
, p_proposal_exists in Varchar2
, p_allow_basis_change out NOCOPY varchar2
, p_min_update_date out NOCOPY date
, p_allow_date_change out NOCOPY varchar2
, p_status out NOCOPY Number
, p_basis_default_date out NOCOPY date
, p_basis_default_min_date out NOCOPY date
, p_orig_basis_id out NOCOPY Number)
is
--
--
Cursor csr_txn_basis_change_date Is
select hatv1.date_value ,hatv.number_value, hatv.original_number_value
from hr_api_transaction_values hatv,
hr_api_transaction_steps hats,
hr_api_transactions hat,
hr_api_transaction_values hatv1
where hatv.NAME = 'P_PAY_BASIS_ID'
and hatv1.NAME = 'P_EFFECTIVE_DATE'
and hatv1.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
and hatv.NUMBER_VALUE <> hatv.ORIGINAL_NUMBER_VALUE
and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
and hats.TRANSACTION_ID = hat.TRANSACTION_ID
and hat.ASSIGNMENT_ID = p_assignment_id
and hat.TRANSACTION_ID = p_transaction_id
order by hatv1.date_value desc ;
select hatv.date_value
from hr_api_transaction_steps hats,
hr_api_transactions hat,
hr_api_transaction_values hatv
where hats.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
and hatv.transaction_step_id = hats.transaction_step_id
and hatv.name = 'P_EFFECTIVE_DATE'
and hats.TRANSACTION_ID = hat.TRANSACTION_ID
and hat.ASSIGNMENT_ID = p_assignment_id
and hat.TRANSACTION_ID = p_transaction_id;
select effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date > l_min_change_date
and effective_start_date < l_change_date
order by effective_start_date desc;
select effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date > l_max_change_date
order by effective_start_date asc;
select effective_start_date,pay_basis_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date > l_max_change_date
and pay_basis_id <> (Select pay_basis_id from per_all_assignments_f
where assignment_id = p_assignment_id
and l_max_change_date between effective_start_date
and effective_end_date)
order by effective_start_date asc;
select min(ptp.start_date) next_payroll_date
from per_time_periods ptp
,per_all_assignments_f paaf
where ptp.payroll_id = paaf.payroll_id
and paaf.assignment_id = l_assignment_id
and ptp.start_date > l_date ;
p_min_update_date := null;
hr_utility.set_location('get_update_param ', 5);
p_min_update_date := l_min_create_date;
p_min_update_date := l_min_create_date;
hr_utility.set_location('p_min_update_date '||p_min_update_date, 40);
p_min_update_date := l_min_create_date;
hr_utility.set_location('p_min_update_date '||p_min_update_date, 65);
p_min_update_date := l_min_create_date;
hr_utility.set_location('p_min_update_date '||p_min_update_date, 75);
p_min_update_date := l_min_create_date;
hr_utility.set_location('p_min_update_date '||p_min_update_date, 85);
p_min_update_date := l_txn_basis_change_date;
hr_utility.set_location('p_min_update_date '||p_min_update_date, 95);
hr_utility.set_location('p_min_update_date '||p_min_update_date, 105);
p_min_update_date := l_asst_basis_change_date;
hr_utility.set_location('p_min_update_date '||p_min_update_date, 115);
End get_update_param;
select nvl(value, 1) val
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and unit = 'FTE'
and p_effective_date BETWEEN effective_start_date AND effective_end_date;
select nvl(value, 1) val
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and unit = 'PFT'
and p_effective_date BETWEEN effective_start_date AND effective_end_date;
select max(astHoursCol) as astHours,
decode(max(frequencyCol)
,'Y',1
,'M',12
,'W',52
,'D',365
,1) as frequency
from(
select decode(NAME, 'P_FREQUENCY', VARCHAR2_VALUE) frequencyCol,
decode(NAME, 'P_NORMAL_HOURS', NUMBER_VALUE) astHoursCol
from hr_api_transaction_values
where TRANSACTION_STEP_ID = (select TRANSACTION_STEP_ID from hr_api_transaction_steps
where API_NAME = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
and TRANSACTION_ID = p_transaction_id)
) ;
select null
from per_all_assignments_f paa
where paa.assignment_id = p_assignment_id;
Select max(position_id) position_id , max(org_id) org_id, max(bg_id) bg_id from (
select decode (NAME, 'P_POSITION_ID', NUMBER_VALUE) position_id,
decode (NAME, 'P_ORGANIZATION_ID', NUMBER_VALUE) org_id,
decode (NAME, 'P_BUSINESS_GROUP_ID', NUMBER_VALUE) bg_id
from hr_api_transaction_values
where TRANSACTION_STEP_ID = (select TRANSACTION_STEP_ID from hr_api_transaction_steps
where API_NAME = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
and TRANSACTION_ID = p_transaction_id));
select pos.working_hours,
decode(pos.frequency
,'Y',1
,'M',12
,'W',52
,'D',365
,1)
from hr_all_positions pos
where pos.position_id = l_pos_id;
select fnd_number.canonical_to_number(org.org_information3) normal_hours
, decode(org.org_information4
,'Y',1
,'M',12
,'W',52
,'D',365
,1)
from HR_ORGANIZATION_INFORMATION org
where org.organization_id(+) = l_org_id
and org.org_information_context(+) = 'Work Day Information';
select fnd_number.canonical_to_number(bus.working_hours) normal_hours
, decode(bus.frequency
,'Y',1
,'M',12
,'W',52
,'D',365
,1)
from per_business_groups bus
where bus.business_group_id = l_bg_id;