[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT, UPDATE and DELETE pay bases:
PER_PAY_BASES
Change List
-----------
Version Date Author ER/CR No. Description of Change
------------------------------------------------------------
80.0 11-NOV-1993 msingh Date Created
80.1 20-DEC-1993 msingh G311 chk_duplicate_element and
chk_input_val_rate_uk take into
account template elements
spanning business groups
70.1 23-NOV-1993 rfine Suppressed index on business_group_id
70.2 01-MAR-1994 gpaytonm Removed reference to bg_id in
chk_input_val_rate_uk
70.4 20-NOV-1996 fshojaas The bg_id was added to the
chk_input_val_rate_uk.
This change was done to fix bug #412780.
115.2 16-Sep-2000 mmillmor 1385192 Added element_type_id output and
translated element and input value
115.3 09-Dec-2002 pkakar Added nocopy to parameters
115.4 05-Jan-2006 rthiagar 4894015 Changed the use of per_assignments_f
to per_all_assignments_f in
chk_basis_assignment.
--------------------------------------------------------------- */
--
FUNCTION generate_unique_id RETURN NUMBER IS
--
v_pay_basis_id NUMBER;
select per_pay_bases_s.nextval
into v_pay_basis_id
from sys.dual;
PROCEDURE insert_row (p_pay_basis_id IN OUT NOCOPY NUMBER,
p_business_group_id NUMBER,
p_name VARCHAR2,
p_pay_basis VARCHAR2,
p_input_value_id NUMBER,
p_rate_id NUMBER,
p_rate_basis VARCHAR2) IS
--
--
Begin
--
hr_utility.set_location ('hr_salary_date.insert_pay_basis',1);
INSERT INTO PER_PAY_BASES(pay_basis_id,
business_group_id,
name,
pay_basis,
input_value_id,
rate_id,
rate_basis,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
VALUES (p_pay_basis_id ,
p_business_group_id,
p_name,
p_pay_basis,
p_input_value_id,
p_rate_id,
p_rate_basis,
trunc(sysdate),
-1,
-1,
-1,
trunc(sysdate));
hr_utility.set_message_token('PROCEDURE','hr_pay_basis.insert_row',1 );
End insert_row;
SELECT 'Y'
INTO duplicate
FROM sys.dual
WHERE EXISTS
(select 'Y'
from per_pay_bases
where upper(p_name) = upper(name)
and business_group_id + 0 = p_business_group_id
and (p_row_id <> rowid
or p_row_id is null)
);
SELECT 'Y'
INTO duplicate
FROM sys.dual
WHERE EXISTS
(select 'Y'
from per_pay_bases
where input_value_id = p_input_value_id
and nvl(p_rate_id,-1) = nvl(rate_id,-1)
and p_business_group_id = business_group_id
and (p_row_id <> rowid
or p_row_id IS NULL)
);
SELECT 'Y'
INTO v_validation_chk
FROM sys.dual
WHERE EXISTS
(select 'Y'
from per_pay_bases ppb,
pay_input_values_f piv
where piv.element_type_id = p_element_type_id
and ppb.input_value_id = piv.input_value_id
and (p_row_id <> ppb.rowid
or p_row_id is null)
and ppb.business_group_id + 0 = p_business_group_id
);
SELECT 'Y'
INTO v_validation_chk
FROM sys.dual
WHERE EXISTS
(select 'Y'
from pay_element_entry_values_f pev
where pev.input_value_id = p_input_value_id
);
SELECT 'Y'
INTO v_validation_chk
FROM sys.dual
WHERE EXISTS
(select 'Y'
from per_all_assignments_f ass
where ass.pay_basis_id = p_pay_basis_id
);
select hlu.meaning into v_basis_meaning
from hr_lookups hlu
where hlu.lookup_code = p_basis_code
and hlu.lookup_type = 'PAY_BASIS';
select pivtl.name,
pettl.element_name,
pet.element_type_id
into p_iv_name,
p_element_name,
p_element_type_id
from pay_input_values_f piv,
pay_input_values_f_tl pivtl,
pay_element_types_f pet,
pay_element_types_f_tl pettl
where pet.element_type_id = piv.element_type_id
and pet.element_type_id = pettl.element_type_id
and p_session_date between pet.effective_start_date
and pet.effective_end_date
and piv.input_value_id = p_input_value_id
and pivtl.input_value_id = p_input_value_id
and p_session_date between piv.effective_start_date
and piv.effective_end_date
and pivtl.language=userenv('LANG')
and pettl.language=userenv('LANG');
select name into v_rate_name
from pay_rates
where rate_id = p_rate_id;
select min(effective_start_date),
max(effective_end_date)
into p_start_date,
p_end_date
from pay_input_values_f
where input_value_id = p_input_value_id;
select 'V'
into v_valid
from sys.dual
where exists
(select 1
from pay_input_values_f
where input_value_id = p_input_value_id
and p_session_date between effective_start_date
and effective_end_date);
Procedure validate_insert (p_business_group_id NUMBER,
p_row_id VARCHAR2,
p_name VARCHAR2,
p_input_value_id NUMBER,
p_rate_id NUMBER,
p_pay_basis_id IN OUT NOCOPY NUMBER) IS
--
Begin
--
chk_name_uniqueness (p_business_group_id,
p_name,
p_row_id);
End validate_insert;
Procedure validate_update (p_row_id VARCHAR2,
p_input_value_id NUMBER,
p_pay_basis VARCHAR2) IS
--
v_pay_basis VARCHAR2 (30);
CURSOR C IS SELECT pay_basis, input_value_id
from per_pay_bases
where rowid = p_row_id;
hr_utility.set_location ('hr_pay_basis.validate_update',1);
'hr_pay_basis.validate_update');