[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure retro_ent_tab_insert(
p_retro_assignment_id IN NUMBER
, p_element_entry_id IN NUMBER
, p_reprocess_date IN DATE
, p_eff_date IN DATE) is
--
l_retro_component_id NUMBER;
l_proc varchar2(80) := g_package||'.retro_ent_tab_insert';
INSERT INTO pay_retro_entries
( retro_assignment_id
, element_entry_id
, reprocess_date
, effective_date
, retro_component_id
)
VALUES
( p_retro_assignment_id
, p_element_entry_id
, p_reprocess_date
, p_eff_date
, l_retro_component_id
);
End retro_ent_tab_insert;
Procedure retro_asg_tab_insert(
p_assignment_id IN NUMBER
, p_payroll_id IN NUMBER
, p_reprocess_date IN DATE
, p_start_date IN DATE
, p_retro_assignment_id OUT nocopy NUMBER) is
--
l_proc varchar2(30) := 'retro_asg_tab_insert';
select pay_retro_assignments_s.nextval
into p_retro_assignment_id
from sys.dual;
INSERT INTO pay_retro_assignments
( retro_assignment_id
, assignment_id
, reprocess_date
, start_date
, approval_status
, retro_assignment_action_id
)
VALUES
( p_retro_assignment_id
, p_assignment_id
, p_reprocess_date
, p_start_date
, l_creation_status
, null
);
End retro_asg_tab_insert;
SELECT
pra.start_date
,pra.retro_assignment_id ret_asg_id
,pra.created_by
FROM pay_retro_assignments pra
WHERE pra.assignment_id = cp_asg
AND pra.retro_assignment_action_id is null
AND pra.superseding_retro_asg_id is null
AND pra.retro_assignment_id <> p_ret_asg_id
AND approval_status in ('P','A','D');
select pra.retro_assignment_id,
pre.element_entry_id,
pre.element_type_id,
pre.reprocess_date,
pre.effective_date,
pre.retro_component_id,
pre.owner_type,
pre.system_reprocess_date,
pre.created_by
from pay_retro_assignments pra,
pay_retro_entries pre
where pra.retro_assignment_id = p_ret_asg_id
and pra.retro_assignment_id = pre.retro_assignment_id;
retro_asg_tab_insert(
p_assignment_id => p_asg_id
, p_payroll_id => p_payroll_id
, p_reprocess_date => p_reprocess_date
, p_start_date => hr_api.g_eot
, p_retro_assignment_id => l_ret_asg_id);
update pay_retro_assignments
set superseding_retro_asg_id = l_ret_asg_id
where retro_assignment_id = rarec.ret_asg_id;
update pay_retro_assignments
set start_date = rarec.start_date
where retro_assignment_id = l_ret_asg_id;
update pay_retro_entries
set created_by = unprocrec.created_by
where retro_assignment_id = l_ret_asg_id
and element_entry_id = unprocrec.element_entry_id;
update pay_retro_assignments
set reprocess_date = l_min_reprocess_date
,created_by = nvl(l_created_by, created_by)
where retro_assignment_id = l_ret_asg_id;
SELECT
pra.start_date
,pra.retro_assignment_id ret_asg_id
FROM pay_retro_assignments pra
WHERE pra.assignment_id = cp_asg
AND pra.retro_assignment_action_id is null
AND pra.superseding_retro_asg_id is null
AND approval_status in ('P','A','D');
hr_utility.trace('+ RetroAsg exists so update, retro-asg = '||l_ret_asg_id);
update PAY_RETRO_ASSIGNMENTS
set APPROVAL_STATUS = 'P'
where ASSIGNMENT_ID = p_asg_id
/*Bug#8306525*/
and RETRO_ASSIGNMENT_ACTION_ID IS NULL;
delete from PAY_RETRO_ENTRIES
where RETRO_ASSIGNMENT_ID = exist_retro_asg.ret_asg_id
and owner_type = 'S';
retro_asg_tab_insert(
p_assignment_id => p_asg_id
,p_payroll_id => p_payroll_id
,p_reprocess_date => p_eff_date -- overriden after child
-- entries are created in pay_retro_notif
,p_start_date => p_min_date
,p_retro_assignment_id => l_ret_asg_id);
select user_name
from fnd_user
where user_id = cp_usr_id
and sysdate between start_date and nvl(end_date,hr_api.g_eot);
select pdt.table_name,peu.column_name,
pdt.start_date_name,pdt.end_date_name,
pdt.surrogate_key_name,ppe.surrogate_key,ppe.effective_date
from pay_process_events ppe
,Pay_event_updates peu
,pay_dated_tables pdt
where ppe.process_event_id = cp_ppe_id
and ppe.event_update_id = peu.event_update_id
and peu.dated_table_id = pdt.dated_table_id;
l_statement := 'SELECT last_updated_by' ||
' FROM ' || l_table_name ||
' WHERE ' || l_surr_key_name || ' = '||l_surr_key||
' AND ' || 'to_date('''||
to_char(l_eff_date,'DD-MON-RR')
||''',''DD-MON-RR'') '
|| ' BETWEEN ' || l_sd_name || ' AND ' || l_ed_name ;
select item_key from wf_items
where item_type = 'PYRETRO'
and root_activity = 'PAY_RETROPAY'
AND end_date is null;
select prcu.retro_component_id
from
pay_retro_component_usages prcu
where prcu.creator_id = cp_et_id
and prcu.creator_type = 'ET'
and prcu.default_component = 'Y'
and (( prcu.business_group_id = cp_bus_grp
and prcu.legislation_code is null)
or
( prcu.legislation_code = cp_leg_code
and prcu.business_group_id is null)
or
( prcu.legislation_code is null
and prcu.business_group_id is null)
);
select distinct pee.assignment_id
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id;
select distinct paf.business_group_id
from per_all_assignments_f paf
where paf.assignment_id = cp_asg_id;
select pbg.legislation_code
from per_business_groups_perf pbg
where pbg.business_group_id = cp_bg_id;