[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
40.9 22-MAR-1995 N Simpson Modified delete_row to cascade
deletion of children differently
according to the datetrack mode.
See comments within that
procedure.
40.10 22-MAR-1995 N Simpson Removed trace_on/off calls
40.11 05-MAR-1997 J Alloun Changed all occurances of system.dual
to sys.dual for next release requirements.
40.12 ??
40.13 01-JUN-1997 M Lisiecki Bug 481143. Changed message
PAY_6465_LINK_NO_COST_UPD1 to more
generic PAY_52151_ENTRIES_EXIST.
40.14 02-JUN-1997 M Lisiecki Changed 52151 to 52153 as 51 already
existed.
110.3 10-FEB-1999 M Reid 809540: Added segment19 to link test
as it was missing.
115.2 27-APR-1999 S Billing 874781,
pay_element_links_pkg.update_row(),
if updating an element link row with
non-criteria information
(ie. Qualifying Conditions), then the
EED of the updated record or the
newly created record should not exceed
the EED of the original element link row
update
115.4 10-NOV-2000 RThirlby Bug 1490304 Updated procedure
check_deletion_allowed, so that cursor
csr_balance_adjustments only gets
called if p_delete_mode in DELETE or
ZAP.
115.5 27-APR-2001 DSaxby Fix for 1755379. Removed the erroneous
close of the csr_balance_adjustments
cursor in the check_deletion_allowed
procedure.
115.6 15-NOV-2002 ALogue Performance fix for csr_entries in
CHECK_DELETION_ALLOWED. Bug 2667222.
115.7 03-DEC-2002 ALogue dbdrv lines.
115.8 20-MAY-2005 SuSivasu Only update the last date tracked record
with the end of time for the case of
DELETE_NEXT_CHANGE DT mode.
115.9 20-MAY-2005 SuSivasu Fixed NOCOPY and GSCC issues.
115.10 26-SEP-2006 THabara Batch Element Link support. Bug 5512101.
Modified cascade_deletion, update_row,
check_deletion_allowed and
last_exclusive_date.
115.11 14-SEP-2006 THabara Added function pay_basis_exists.
Added pay basis check to insert_row.
115.12 06-FEB-2008 salogana Commented the pay_basis_exists
check as the customer doesnt require
this validation ( BUG NO : 6764215 ).
*/
--------------------------------------------------------------------------------
-- Declare global package variables and constants.
--
c_end_of_time constant date := hr_general.end_of_time;
procedure CASCADE_INSERTION (
--
--******************************************************************************
--* This procedure inserts link input values when an element link is
--* created. It will also insert assignment link usages.
--******************************************************************************
--
-- Parameters:
--
p_element_link_id number,
p_element_type_id number,
p_effective_start_date date,
p_effective_end_date date,
p_people_group_id number,
p_costable_type varchar2,
p_business_group_id number ) is
--
begin
--
hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CASCADE_INSERTION', 1);
hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CASCADE_INSERTION', 2);
pay_asg_link_usages_pkg.insert_alu(
--
p_business_group_id,
p_people_group_id,
p_element_link_id,
p_effective_start_date,
p_effective_end_date);
end cascade_insertion;
p_delete_mode varchar2,
p_effective_start_date date,
p_effective_end_date date,
p_session_date date,
p_validation_start_date date,
p_validation_end_date date) is
--
cursor csr_links_entries is
select element_entry_id
from pay_element_entries_f
where element_link_id = p_element_link_id
and p_session_date between effective_start_date
and effective_end_date;
select rowid, pay_link_input_values_f.*
from pay_link_input_values_f
where element_link_id = p_element_link_id
for update;
hr_entry_api.delete_element_entry (
--
p_delete_mode,
p_session_date,
fetched_entry.element_entry_id);
p_delete_mode,
p_effective_start_date,
p_effective_end_date,
p_validation_start_date,
p_validation_end_date);
if p_delete_mode = 'ZAP' then
pay_batch_object_status_pkg.delete_object_status
(p_object_type => 'EL'
,p_object_id => p_element_link_id
,p_payroll_action_id => null
);
if p_delete_mode = 'ZAP'
or (p_delete_mode = 'DELETE'
and fetched_input_value.effective_start_date > p_session_date ) then
--
delete from pay_link_input_values_f
where current of csr_all_inputs_for_link;
elsif p_delete_mode = 'DELETE'
and p_session_date between fetched_input_value.effective_start_date
and fetched_input_value.effective_end_date then
--
update pay_link_input_values_f
set effective_end_date = p_session_date
where current of csr_all_inputs_for_link;
elsif p_delete_mode = 'DELETE_NEXT_CHANGE'
and p_validation_end_date = hr_general.end_of_time then
--
update pay_link_input_values_f
set effective_end_date = c_end_of_time
where --current of csr_all_inputs_for_link
rowid = fetched_input_value.rowid
and not exists
(select null
from pay_link_input_values_f pliv
where pliv.element_link_id = fetched_input_value.element_link_id
and pliv.input_value_id = fetched_input_value.input_value_id
and pliv.effective_start_date > fetched_input_value.effective_start_date);
select max(effective_end_date)
from pay_element_links_f
where element_link_id = p_link_id;
procedure insert_row(p_rowid in out nocopy varchar2,
p_element_link_id in out nocopy number,
p_effective_start_date date,
p_effective_end_date in out nocopy date,
p_payroll_id number,
p_job_id number,
p_position_id number,
p_people_group_id number,
p_cost_allocation_keyflex_id number,
p_organization_id number,
p_element_type_id number,
p_location_id number,
p_grade_id number,
p_balancing_keyflex_id number,
p_business_group_id number,
p_legislation_code varchar2,
p_element_set_id number,
p_pay_basis_id number,
p_costable_type varchar2,
p_link_to_all_payrolls_flag varchar2,
p_multiply_value_flag varchar2,
p_standard_link_flag varchar2,
p_transfer_to_gl_flag varchar2,
p_comment_id number,
p_employment_category varchar2,
p_qualifying_age number,
p_qualifying_length_of_service number,
p_qualifying_units varchar2,
p_attribute_category varchar2,
p_attribute1 varchar2,
p_attribute2 varchar2,
p_attribute3 varchar2,
p_attribute4 varchar2,
p_attribute5 varchar2,
p_attribute6 varchar2,
p_attribute7 varchar2,
p_attribute8 varchar2,
p_attribute9 varchar2,
p_attribute10 varchar2,
p_attribute11 varchar2,
p_attribute12 varchar2,
p_attribute13 varchar2,
p_attribute14 varchar2,
p_attribute15 varchar2,
p_attribute16 varchar2,
p_attribute17 varchar2,
p_attribute18 varchar2,
p_attribute19 varchar2,
p_attribute20 varchar2) is
cursor csr_new_rowid is
select rowid
from pay_element_links_f
where element_link_id = p_element_link_id
and effective_start_date = p_effective_start_date
and effective_end_date = p_effective_end_date;
select pay_element_links_s.nextval
from sys.dual;
hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.INSERT_ROW',1);
insert into pay_element_links_f(
--
element_link_id,
effective_start_date,
effective_end_date,
payroll_id,
job_id,
position_id,
people_group_id,
cost_allocation_keyflex_id,
organization_id,
element_type_id,
location_id,
grade_id,
balancing_keyflex_id,
business_group_id,
element_set_id,
pay_basis_id,
costable_type,
link_to_all_payrolls_flag,
multiply_value_flag,
standard_link_flag,
transfer_to_gl_flag,
comment_id,
employment_category,
qualifying_age,
qualifying_length_of_service,
qualifying_units,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20)
values (
p_element_link_id,
p_effective_start_date,
p_effective_end_date,
p_payroll_id,
p_job_id,
p_position_id,
p_people_group_id,
p_cost_allocation_keyflex_id,
p_organization_id,
p_element_type_id,
p_location_id,
p_grade_id,
p_balancing_keyflex_id,
p_business_group_id,
p_element_set_id,
p_pay_basis_id,
p_costable_type,
p_link_to_all_payrolls_flag,
p_multiply_value_flag,
p_standard_link_flag,
p_transfer_to_gl_flag,
p_comment_id,
p_employment_category,
p_qualifying_age,
p_qualifying_length_of_service,
p_qualifying_units,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20);
cascade_insertion (
p_element_link_id,
p_element_type_id,
p_effective_start_date,
p_effective_end_date,
p_people_group_id,
p_costable_type,
p_business_group_id);
end insert_row;
select *
from pay_element_links_f
where rowid = p_rowid
for update of element_link_id NOWAIT;
procedure update_row(p_rowid varchar2,
p_element_link_id number,
p_effective_start_date date,
p_effective_end_date in out nocopy date,
p_payroll_id number,
p_job_id number,
p_position_id number,
p_people_group_id number,
p_cost_allocation_keyflex_id number,
p_organization_id number,
p_element_type_id number,
p_location_id number,
p_grade_id number,
p_balancing_keyflex_id number,
p_business_group_id number,
p_legislation_code varchar2,
p_element_set_id number,
p_pay_basis_id number,
p_costable_type varchar2,
p_link_to_all_payrolls_flag varchar2,
p_multiply_value_flag varchar2,
p_standard_link_flag varchar2,
p_transfer_to_gl_flag varchar2,
p_comment_id number,
p_employment_category varchar2,
p_qualifying_age number,
p_qualifying_length_of_service number,
p_qualifying_units varchar2,
p_attribute_category varchar2,
p_attribute1 varchar2,
p_attribute2 varchar2,
p_attribute3 varchar2,
p_attribute4 varchar2,
p_attribute5 varchar2,
p_attribute6 varchar2,
p_attribute7 varchar2,
p_attribute8 varchar2,
p_attribute9 varchar2,
p_attribute10 varchar2,
p_attribute11 varchar2,
p_attribute12 varchar2,
p_attribute13 varchar2,
p_attribute14 varchar2,
p_attribute15 varchar2,
p_attribute16 varchar2,
p_attribute17 varchar2,
p_attribute18 varchar2,
p_attribute19 varchar2,
p_attribute20 varchar2) is
--
begin
--
hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.UPDATE_ROW',1);
update pay_element_links_f
set element_link_id = p_element_link_id,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
payroll_id = p_payroll_id,
job_id = p_job_id,
position_id = p_position_id,
people_group_id = p_people_group_id,
cost_allocation_keyflex_id = p_cost_allocation_keyflex_id,
organization_id = p_organization_id,
element_type_id = p_element_type_id,
location_id = p_location_id,
grade_id = p_grade_id,
balancing_keyflex_id = p_balancing_keyflex_id,
business_group_id = p_business_group_id,
element_set_id = p_element_set_id,
pay_basis_id = p_pay_basis_id,
costable_type = p_costable_type,
link_to_all_payrolls_flag = p_link_to_all_payrolls_flag,
multiply_value_flag = p_multiply_value_flag,
standard_link_flag = p_standard_link_flag,
transfer_to_gl_flag = p_transfer_to_gl_flag,
comment_id = p_comment_id,
employment_category = p_employment_category,
qualifying_age = p_qualifying_age,
qualifying_length_of_service = p_qualifying_length_of_service,
qualifying_units = p_qualifying_units,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20
where rowid = p_rowid;
end update_row;
procedure delete_row(
--
p_rowid varchar2,
p_element_link_id number,
p_delete_mode varchar2,
p_session_date date,
p_validation_start_date date,
p_validation_end_date date,
p_effective_start_date date,
p_business_group_id number,
p_people_group_id number) is
--
v_effective_end_date date;
hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.DELETE_ROW',1);
p_delete_mode,
p_validation_start_date);
if p_delete_mode = 'ZAP' then
--
cascade_deletion (
p_element_link_id ,
p_business_group_id,
p_people_group_id,
p_delete_mode ,
p_effective_start_date,
v_effective_end_date,
p_session_date,
p_validation_start_date ,
p_validation_end_date );
delete from pay_element_links_f
where rowid = p_rowid;
if p_delete_mode <> 'ZAP' then
--
cascade_deletion (
p_element_link_id ,
p_business_group_id,
p_people_group_id,
p_delete_mode ,
p_effective_start_date,
v_effective_end_date,
p_session_date,
p_validation_start_date ,
p_validation_end_date );
end delete_row;
select link.effective_start_date,
link.effective_end_date,
link.organization_id,
link.people_group_id,
link.job_id,
link.position_id,
link.grade_id,
link.location_id,
link.link_to_all_payrolls_flag PAYROLL_FLAG,
link.payroll_id,
link.employment_category,
link.pay_basis_id,
people_group.segment1,
people_group.segment2,
people_group.segment3,
people_group.segment4,
people_group.segment5,
people_group.segment6,
people_group.segment7,
people_group.segment8,
people_group.segment9,
people_group.segment10,
people_group.segment11,
people_group.segment12,
people_group.segment13,
people_group.segment14,
people_group.segment15,
people_group.segment16,
people_group.segment17,
people_group.segment18,
people_group.segment19,
people_group.segment20,
people_group.segment21,
people_group.segment22,
people_group.segment23,
people_group.segment24,
people_group.segment25,
people_group.segment26,
people_group.segment27,
people_group.segment28,
people_group.segment29,
people_group.segment30
from pay_element_links_f LINK,
pay_people_groups PEOPLE_GROUP
where link.people_group_id = people_group.people_group_id(+)
and link.element_type_id = p_element_type_id
and link.element_link_id <> nvl(p_element_link_id,0)
and link.business_group_id + 0 = p_business_group_id
and link.effective_end_date >= p_validation_start_date
--
-- Batch element link support.
--
UNION ALL
select bel.effective_date effective_start_date,
hr_general.end_of_time effective_end_date,
bel.organization_id,
bel.people_group_id,
bel.job_id,
bel.position_id,
bel.grade_id,
bel.location_id,
bel.link_to_all_payrolls_flag PAYROLL_FLAG,
bel.payroll_id,
bel.employment_category,
bel.pay_basis_id,
people_group.segment1,
people_group.segment2,
people_group.segment3,
people_group.segment4,
people_group.segment5,
people_group.segment6,
people_group.segment7,
people_group.segment8,
people_group.segment9,
people_group.segment10,
people_group.segment11,
people_group.segment12,
people_group.segment13,
people_group.segment14,
people_group.segment15,
people_group.segment16,
people_group.segment17,
people_group.segment18,
people_group.segment19,
people_group.segment20,
people_group.segment21,
people_group.segment22,
people_group.segment23,
people_group.segment24,
people_group.segment25,
people_group.segment26,
people_group.segment27,
people_group.segment28,
people_group.segment29,
people_group.segment30
from pay_batch_element_links BEL,
pay_people_groups PEOPLE_GROUP
where bel.people_group_id = people_group.people_group_id(+)
and bel.element_type_id = p_element_type_id
and bel.batch_element_link_id <> nvl(p_element_link_id,0)
and bel.element_link_id is null
and bel.business_group_id + 0 = p_business_group_id
-- exclude the batch link that is currently processing.
and nvl(pay_batch_object_status_pkg.get_status
('BEL',bel.batch_element_link_id),'U') <> 'P'
order by effective_start_date;
select *
from pay_people_groups
where people_group_id = p_people_group_id;
select max(effective_end_date)
from pay_element_types_f
where element_type_id = p_element_type_id;
select max(effective_end_date)
from pay_payrolls_f
where business_group_id + 0 = p_business_group_id;
select max(effective_end_date)
from pay_payrolls_f
where payroll_id = p_payroll_id;
select 1
from pay_element_set_members MEMBER,
pay_element_sets ELEMENT_SET
where element_set.element_set_id = member.element_set_id
and member.element_type_id = p_element_type_id
and element_set.element_set_type = 'D'
and (element_set.business_group_id + 0
= member.business_group_id + 0
and element_set.business_group_id + 0 = p_business_group_id
or (p_business_group_id is null
and element_set.legislation_code =
member.legislation_code
and element_set.legislation_code = p_legislation_code));
select 1
from pay_element_entries_f
where element_link_id = p_element_link_id;
function DATE_EFFECTIVELY_UPDATED (
--
--******************************************************************************
--* Returns TRUE if there exists more than one row with the same link ID *
--******************************************************************************
--
-- Parameters are:
--
p_element_link_id number,
p_rowid varchar2) return boolean is
--
v_updates_exist boolean := FALSE;
cursor csr_updates is
select 1
from pay_element_links_f
where element_link_id = p_element_link_id
and rowid <> p_rowid;
hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DATE_EFFECTIVELY_UPDATED',1);
open csr_updates;
fetch csr_updates into g_dummy;
v_updates_exist := csr_updates%found;
close csr_updates;
return v_updates_exist;
end date_effectively_updated;
p_delete_mode varchar2,
p_validation_start_date date ) is
--
-- Are there entries whose earliest start date is after the deletion date?
cursor csr_entries is
select 1
from pay_element_entries_f ENTRY1
where entry1.element_link_id = p_element_link_id
and not(entry1.effective_start_date < p_validation_start_date);
select 1
from pay_element_entries_f BALANCE_ENTRY
where balance_entry.element_link_id = p_element_link_id
and balance_entry.effective_start_date > p_validation_start_date
-- and p_delete_mode in ('DELETE','ZAP')
and balance_entry.entry_type = 'B';
if p_delete_mode <> 'ZAP' then
pay_batch_object_status_pkg.chk_complete_status
(p_object_type => 'EL'
,p_object_id => p_element_link_id
);
IF p_delete_mode in ('DELETE','ZAP') THEN
open csr_entries;
p_date_effectively_updated out nocopy boolean,
p_element_entries_exist out nocopy boolean ) is
--
begin
--
hr_utility.set_location ('pay_element_links_pkg.check_relationships',1);
p_date_effectively_updated := date_effectively_updated (p_element_link_id,
p_rowid );
select 1
from
pay_input_values_f piv
,per_pay_bases ppb
where
piv.element_type_id = p_element_type_id
and ppb.input_value_id = piv.input_value_id
and ppb.business_group_id = p_business_group_id
;