[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
thabara 26-MAR-2007 Modified maintain_retro_entry to update
retro_component_id.
nbristow 15-JAN-2007 Added overlap_adjustments.
alogue 12-JAN-2007 Further changes to get_source_element_type.
alogue 10-JAN-2007 Further changes to get_source_element_type
and debug if required.
alogue 09-JAN-2007 Re-implement get_source_element_type
changes. Bug 5747560.
alogue 30-NOV-2006 Change comment for last change!
alogue 24-NOV-2006 Undo recent changes to get_source_element_type.
nbristow 06-NOV-2006 get_ee_overlap_date was not joining to
the retro assignments table correctly
in a multi-assignment environment.
mreid 06-OCT-2006 Added hint to business group sql
nbristow 28-SEP-2006 Fixed test harness failures.
alogue 13-SEP-2006 Avoid ORA-01422 in get_source_element_type.
Bug 5482805.
nbristow 12-SEP-2006 Added new get_entry_path.
alogue 07-SEP-2006 Ensure run result is a processed one in
get_source_element_type. Bug 5482805.
alogue 01-SEP-2006 Performance fix to get_ee_overlap_date_int.
Bug 5482574.
nbristow 01-JUN-2006 Added process_retro_entry
alogue 17-MAR-2006 Caches in get_reprocess_type. Bug 5101847.
nbristow 14-MAR-2006 Added get_retro_asg_id.
alogue 07-MAR-2006 Enhanced generate_obj_grp_actions so
don't create actions for assignments
only existing in the future. Bug 5082050.
alogue 01-MAR-2006 Enhanced get_ee_overlap_date_int to
account for POG master actions owning
the retro element entries. Bug 5057817.
kkawol 26-JAN-2006 added get_entry_path to convert entry
paths for retropay into new shorter format.
nbristow 28-SEP-2005 get_asg_from_pg_action needs
to take into account of date
effectiveity.
nbristow 29-JUN-2005 Overlap satetment was using the
wrong entry table.
nbristow 23-MAR-2005 Performance improvement to the overlap
statements.
nbristow 22-MAR-2005 get_ee_overlap_date now only overrides
the date if there are retro entries for
the overlapping period.
nbristow 12-JAN-2005 Added date effective joins to
retro_component_usages.
nbristow 25-NOV-2004 Retropay multi assignments
jford 08-SEP-2004 Get_retro_component moved to pyretutl.pkb
tbattoo 09-AUG-2004 Added functions to suporrt reversals in retropay
jford 05-AUG-2004 maintain_entries now Merges System and User
nbristow 14-JUL-2004 Changes for Enhanced version of Retro
NOticfications.
nbristow 26-MAY-2004 Fixed previous change.
alogue 06-MAY-2004 Qualify result to be PROCESSED ones in
get_source_element_type to avoid ORA-01422.
Bug 3598256.
alogue 27-APR-2004 Performance fix in latest_replace_ovl_del_ee.
nbristow 15-MAR-2004 Added is_retro_rr.
kkawol 07-JAN-2004 Added latest_replace_ovl_ee,
latest_replace_ovl_del_ee.
kkawol 20-NOV-2003 Passing bus grp id to get_retro_element,
this is required when calling is_date_in_span.
nbristow 07-OCT-2003 Added nocopy to get_ee_overlap_date.
nbristow 07-OCT-2003 Added process_retro_value.
nbristow 03-OCT-2003 Added get_ee_overlap_date.
nbristow 02-SEP-2003 Changed get_retro_element to
return correct element.
nbristow 28-AUG-2003 Added dbdrv statements.
nbristow 28-AUG-2003 Uncommented exit.
nbristow 27-AUG-2003 Changes for Advanced Retropay
jalloun 30-JUL-1996 Added error handling.
nbristow 12-MAR-1996 Created
*/
-- Caches for get_reprocess_type
g_bus_grp per_business_groups_perf.business_group_id%type := null;
select 1
into l_result
from dual
where pay_retro_pkg.process_retro_value(
p_element_entry_id,
p_element_type_id,
p_retro_comp_id,
p_retro_asg_id
) = 'Y'
and exists (select ''
from pay_assignment_actions paa
where paa.assignment_action_id = p_ee_creator_id
and paa.action_sequence < p_action_sequence
);
select 1
into l_dummy
from pay_retro_entries pre
where retro_assignment_id = p_retro_asg_id
and retro_component_id = p_retro_comp_id
and element_entry_id = p_entry_id;
select prcu.reprocess_type
into l_reprocess_type
from pay_retro_component_usages prcu
where prcu.retro_component_id = p_retro_comp_id
and prcu.creator_id = p_element_type_id
and prcu.creator_type = 'ET'
and (( prcu.business_group_id = pay_proc_environment_pkg.bgid
and prcu.legislation_code is null)
or
( prcu.legislation_code = pay_proc_environment_pkg.legc
and prcu.business_group_id is null)
or
( prcu.legislation_code is null
and prcu.business_group_id is null)
);
select creator_type
into l_creator_type
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and p_date between effective_start_date
and effective_end_date;
select prr2.element_type_id, prr2.element_entry_id
from pay_run_results prr2
where prr2.source_id = p_entry_id
and nvl(prr2.element_entry_id,-999) = p_entry_id
and prr2.source_type = 'E'
and prr2.assignment_action_id = p_aa_id;
select distinct prr2.element_type_id
into l_src_et_id
from pay_run_results prr2
where prr2.source_id = p_entry_id
and nvl(prr2.element_entry_id,-999) = p_entry_id
and prr2.source_type = 'E'
and prr2.assignment_action_id = p_aa_id;
select nvl(pet1.retro_summ_ele_id, pet1.element_type_id)
into l_retro_ele_type_id
from pay_element_types_f pet1
where pet1.element_type_id = p_element_type_id
and p_retro_eff_date between pet1.effective_start_date
and pet1.effective_end_date;
select legislation_code
into l_leg_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
select pesu.retro_element_type_id
into l_retro_ele_type_id
from pay_element_span_usages pesu,
pay_retro_component_usages prcu,
pay_time_spans pts
where prcu.retro_component_id = p_retro_comp_id
and prcu.creator_id = p_element_type_id
and prcu.creator_type = 'ET'
and prcu.retro_component_usage_id = pesu.retro_component_usage_id
and nvl(pesu.adjustment_type, 'A') = p_adjustment_type
and pay_core_dates.is_date_in_span
(pts.start_time_def_id,
pts.end_time_def_id,
p_run_eff_date,
p_retro_eff_date,
p_business_group_id) = 'Y'
and pts.time_span_id = pesu.time_span_id
and pts.creator_id = prcu.retro_component_id
and (( prcu.business_group_id = p_business_group_id
and prcu.legislation_code is null)
or
( prcu.legislation_code = l_leg_code
and prcu.business_group_id is null)
or
( prcu.legislation_code is null
and prcu.business_group_id is null)
)
and (( pesu.business_group_id = p_business_group_id
and pesu.legislation_code is null)
or
( pesu.legislation_code = l_leg_code
and pesu.business_group_id is null)
or
( pesu.legislation_code is null
and pesu.business_group_id is null)
);
select pesu.retro_element_type_id
into l_retro_ele_type_id
from pay_element_span_usages pesu,
pay_retro_component_usages prcu,
pay_time_spans pts
where prcu.retro_component_id = p_retro_comp_id
and prcu.creator_id = p_element_type_id
and prcu.creator_type = 'ET'
and prcu.retro_component_usage_id = pesu.retro_component_usage_id
and nvl(pesu.adjustment_type, 'A') = 'A'
and pay_core_dates.is_date_in_span
(pts.start_time_def_id,
pts.end_time_def_id,
p_run_eff_date,
p_retro_eff_date,
p_business_group_id) = 'Y'
and pts.time_span_id = pesu.time_span_id
and pts.creator_id = prcu.retro_component_id
and (( prcu.business_group_id = p_business_group_id
and prcu.legislation_code is null)
or
( prcu.legislation_code = l_leg_code
and prcu.business_group_id is null)
or
( prcu.legislation_code is null
and prcu.business_group_id is null)
)
and (( pesu.business_group_id = p_business_group_id
and pesu.legislation_code is null)
or
( pesu.legislation_code = l_leg_code
and pesu.business_group_id is null)
or
( pesu.legislation_code is null
and pesu.business_group_id is null)
);
select min(ppa.start_date)
into l_start_date
from pay_payroll_actions ppa,
pay_assignment_actions paa_ret,
pay_assignment_actions paa_mret
where ppa.effective_date between p_start_date
and p_effective_date
and ppa.action_type = 'L'
and paa_ret.payroll_action_id = ppa.payroll_action_id
and paa_ret.assignment_id = p_asg_id
and paa_mret.object_id = paa_ret.object_id
and paa_mret.object_type = paa_ret.object_type
and paa_mret.payroll_action_id = paa_ret.payroll_action_id
and paa_mret.source_action_id is null
and exists (select ''
from pay_element_entries_f pee
where pee.creator_id = paa_mret.assignment_action_id
and pee.creator_type in ('RR', 'EE', 'NR', 'PR')
and pee.assignment_id = paa_ret.assignment_id);
select min(pra.reprocess_date)
into l_reprocess_start_date
from pay_retro_assignments pra,
pay_payroll_actions ppa,
pay_assignment_actions paa_ret,
pay_assignment_actions paa_mret
where ppa.effective_date between p_start_date
and p_effective_date
and ppa.action_type = 'L'
and paa_ret.payroll_action_id = ppa.payroll_action_id
and paa_ret.assignment_id = p_asg_id
and pra.retro_assignment_action_id = paa_mret.assignment_action_id
+ decode(paa_ret.action_sequence, 0, 0, 0)
and paa_mret.object_id = paa_ret.object_id
and paa_mret.object_type = paa_ret.object_type
and paa_mret.payroll_action_id = paa_ret.payroll_action_id
and paa_mret.source_action_id is null
and exists (select ''
from pay_element_entries_f pee
where pee.creator_id = paa_mret.assignment_action_id
and pee.creator_type in ('RR', 'EE', 'NR', 'PR')
and pee.assignment_id = paa_ret.assignment_id);
select paa.assignment_id
from pay_assignment_actions paa,
pay_assignment_actions paa2
where paa2.assignment_action_id = p_assact
and paa2.object_id = paa.object_id
and paa2.object_type = paa.object_type
and paa2.payroll_action_id = paa.payroll_action_id
and paa.assignment_id is not null;
select count(*)
into l_ovl_exists
from pay_entry_process_details pepd1,
pay_entry_process_details pepd2
where pepd1.element_entry_id = p_element_entry_id
and pepd2.element_entry_id > pepd1.element_entry_id
and pepd1.run_result_id = pepd2.run_result_id
and pepd1.source_entry_id = pepd2.source_entry_id
and pepd1.source_asg_action_id = pepd2.source_asg_action_id
and pepd1.source_element_type_id = pepd2.source_element_type_id
and pepd1.retro_component_id = pepd2.retro_component_id
and ((pepd1.tax_unit_id is null
and pepd2.tax_unit_id is null
) OR
(pepd1.tax_unit_id is not null
and pepd2.tax_unit_id is not null
and pepd1.tax_unit_id = pepd2.tax_unit_id
));
select count(*)
into l_ovl_exists
from pay_entry_process_details pepd1,
pay_entry_process_details pepd2
where pepd1.element_entry_id = p_element_entry_id
and pepd2.element_entry_id > pepd1.element_entry_id
and pepd1.run_result_id = pepd2.run_result_id
and pepd1.source_entry_id = pepd2.source_entry_id
and pepd1.source_asg_action_id = pepd2.source_asg_action_id
and pepd1.source_element_type_id = pepd2.source_element_type_id
and pepd1.retro_component_id = pepd2.retro_component_id
and ((pepd1.tax_unit_id is null
and pepd2.tax_unit_id is null
) OR
(pepd1.tax_unit_id is not null
and pepd2.tax_unit_id is not null
and pepd1.tax_unit_id = pepd2.tax_unit_id
));
select count(*)
into l_matching_pr
from pay_entry_process_details pepd1,
pay_element_entries_f pee1
where pepd1.element_entry_id = p_element_entry_id
and pepd1.element_entry_id = pee1.element_entry_id
and exists
(select 'Y'
from pay_entry_process_details pepd2,
pay_element_entries_f pee2
where pee2.creator_type = 'PR'
and pee2.element_entry_id = pepd2.element_entry_id
and pee2.assignment_id = pee1.assignment_id
and pepd1.run_result_id = pepd2.run_result_id
and pepd1.source_entry_id = pepd2.source_entry_id
and pepd1.source_asg_action_id = pepd2.source_asg_action_id
and pepd1.source_element_type_id = pepd2.source_element_type_id
and pepd1.retro_component_id = pepd2.retro_component_id
and ((pepd1.tax_unit_id is null
and pepd2.tax_unit_id is null
) OR
(pepd1.tax_unit_id is not null
and pepd2.tax_unit_id is not null
and pepd1.tax_unit_id = pepd2.tax_unit_id
))
and pee1.creator_id = pee2.creator_id
);
INSERT INTO pay_retro_entries
( retro_assignment_id
, element_entry_id
, reprocess_date
, effective_date
, retro_component_id
, element_type_id
, owner_type
, system_reprocess_date
)
VALUES
( p_retro_assignment_id
, p_element_entry_id
, p_reprocess_date
, p_eff_date
, p_retro_component_id
, p_element_type_id
, p_owner_type
, p_system_reprocess_date
);
select reprocess_date,
effective_date,
owner_type,
nvl(system_reprocess_date, hr_api.g_eot),
retro_component_id
into l_min_reprocess_date,
l_min_effective_date,
l_owner_type,
l_min_sys_reprocess_date,
l_retro_component_id
from pay_retro_entries
where retro_assignment_id = p_retro_assignment_id
and element_entry_id = p_element_entry_id;
update pay_retro_entries
set reprocess_date = n_min_reprocess_date,
effective_date = n_min_effective_date,
retro_component_id = n_retro_component_id,
owner_type = n_owner_type,
system_reprocess_date = n_min_sys_reprocess_date
where retro_assignment_id = p_retro_assignment_id
and element_entry_id = p_element_entry_id;
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
from pay_retro_assignments pra,
pay_retro_entries pre
where pra.assignment_id = p_assignment_id
and pra.retro_assignment_action_id is null
and pra.retro_assignment_id = pre.retro_assignment_id;
select retro_assignment_id,
assignment_id
from pay_retro_assignments
where retro_assignment_action_id = p_asg_act_id;
delete from pay_retro_entries
where element_entry_id = unprocrec.element_entry_id
and retro_assignment_id = unprocrec.retro_assignment_id;
delete from pay_retro_assignments
where assignment_id = retasgrec.assignment_id
and retro_assignment_action_id is null;
select source_id
into l_source_id
from pay_run_results
where run_result_id=p_rr_id;
select source_type
into l_source_type
from pay_run_results
where run_result_id=p_rr_id;
select paa.assignment_action_id,
paa.object_id process_group_id,
ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.payroll_action_id = p_pactid
and ppa.payroll_action_id = p_pactid
and paa.source_action_id is null
and paa.chunk_number = p_chunk_number;
select distinct
pog.source_id,
hr_dynsql.get_tax_unit(pog.source_id,
p_eff_date) tax_unit_id
from pay_object_groups pog
where pog.parent_object_group_id = p_proc_grp_id
and pog.source_type = 'PAF'
and p_eff_date between pog.start_date
and pog.end_date;
insert into pay_assignment_actions (
assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
chunk_number,
action_sequence,
object_version_number,
tax_unit_id,
source_action_id,
object_id,
object_type
)
select pay_assignment_actions_s.nextval,
asgrec.source_id,
p_pactid,
'U',
p_chunk_number,
pay_assignment_actions_s.nextval,
1,
asgrec.tax_unit_id,
actrec.assignment_action_id,
actrec.process_group_id,
'POG'
from dual;
update pay_retro_assignments
set retro_assignment_action_id = actrec.assignment_action_id
where assignment_id = asgrec.source_id
and retro_assignment_action_id is null;
update pay_assignment_actions
set action_sequence = pay_assignment_actions_s.nextval
where assignment_action_id = actrec.assignment_action_id;
select paa2.assignment_id
into l_assignment
from pay_assignment_actions paa2,
per_all_assignments_f paf,
pay_payroll_actions ppa
where p_obj_grp_id = paa2.object_id
and p_obj_type = paa2.object_type
and p_pactid = paa2.payroll_action_id
and ppa.payroll_action_id = p_pactid
and paa2.assignment_id is not null
and paa2.assignment_id = paf.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and rownum = 1;
select source_type
into l_src_type
from pay_run_results prr2
where prr2.run_result_id = p_run_result_id;
select entry_process_path,
source_type,
element_type_id
into l_entry_process_path,
l_source_type,
l_element_type_id
from pay_run_results
where run_result_id = p_run_result_id;
select retro_assignment_id
into l_ret_asg
from pay_retro_assignments
where retro_assignment_action_id = p_assignment_action
and rownum = 1;
SELECT /*+ INDEX(piv pay_input_values_f_pk)
INDEX(pet pay_element_types_f_pk)
USE_NL(piv pet) */
pee.element_entry_id,
pee.source_start_date,
pee.source_end_date,
piv1.input_value_id,
peev.screen_entry_value,
piv.mandatory_flag,
pet1.element_type_id,
pepd.source_entry_id,
pepd.run_result_id,
pepd.tax_unit_id,
pepd.time_definition_id,
pee.source_run_type,
pee.assignment_id
FROM pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_entry_values_f peev,
pay_element_types_f pet,
pay_element_types_f pet1,
pay_input_values_f piv1,
pay_entry_process_details pepd,
pay_retro_components prc,
pay_retro_defn_components prdc2,
pay_retro_defn_components prdc
WHERE pet1.element_type_id = pepd.source_element_type_id
and piv1.element_type_id = pet1.element_type_id
and pepd.source_asg_action_id = p_asg_act_id
AND pee.element_entry_id = peev.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND piv.name = piv1.NAME
AND piv.uom NOT IN ('D','T','C')
AND pee.element_type_id = pet.element_type_id
AND pee.effective_end_date between peev.effective_start_date AND
peev.effective_end_date
AND pee.effective_end_date between piv.effective_start_date AND
piv.effective_end_date
AND pee.effective_end_date between pet.effective_start_date AND
pet.effective_end_date
AND pee.effective_end_date between pet1.effective_start_date AND
pet1.effective_end_date
AND pee.effective_end_date between piv1.effective_start_date AND
piv1.effective_end_date
AND pepd.element_entry_id = pee.element_entry_id
AND pepd.retro_component_id = prc.retro_component_id (+)
AND prc.retro_component_id = prdc.retro_component_id (+)
AND prdc.retro_definition_id (+) = p_definition_id
AND prdc2.retro_component_id (+) = p_component_id
AND prdc2.retro_definition_id (+) = p_definition_id
AND nvl(prdc.priority, 99) <= nvl(prdc2.priority, 99)
AND ( prc.recalculation_style is null
OR
( prc.recalculation_style <> 'R'
OR
/* Replacement overlap entries, bring back all PR OR
* fetch NR with no matching PR, i.e. deleted entries.
*/
( prc.recalculation_style = 'R'
AND
( (pee.creator_type = 'PR'
AND pay_retro_pkg.latest_replace_ovl_ee (pee.element_entry_id) = 'Y'
)
OR
(pee.creator_type = 'NR'
AND pay_retro_pkg.latest_replace_ovl_del_ee (pee.element_entry_id) = 'Y'
)
)
)
)
)
AND ( p_ele_set_id = 0
or (p_ele_set_id <> 0
and EXISTS
(
SELECT NULL
FROM pay_ele_classification_rules ECR
WHERE ECR.element_set_id = p_ele_set_id
AND pet1.classification_id = ECR.classification_id
AND NOT EXISTS
(
SELECT NULL
FROM pay_element_type_rules ETR
WHERE ETR.element_set_id = p_ele_set_id
AND ETR.element_type_id = pet1.element_type_id
AND ETR.include_or_exclude = 'E'
)
UNION
SELECT NULL
FROM pay_element_type_rules ETR
WHERE ETR.element_set_id = p_ele_set_id
AND ETR.element_type_id = pet1.element_type_id
AND ETR.include_or_exclude = 'I'
)
)
)
ORDER by pepd.tax_unit_id,
pee.source_run_type,
pee.element_entry_id,
piv.input_value_id;
SELECT /*+ INDEX(piv pay_input_values_f_pk)
INDEX(pet pay_element_types_f_pk)
USE_NL(piv pet) */
pee.element_entry_id,
pee.source_start_date,
pee.source_end_date,
piv1.input_value_id,
peev.screen_entry_value,
pet1.element_type_id,
pepd.source_entry_id,
pepd.run_result_id,
pepd.tax_unit_id,
pepd.time_definition_id,
pee.source_run_type,
pee.assignment_id
FROM pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_entry_values_f peev,
pay_element_types_f pet,
pay_element_types_f pet1,
pay_input_values_f piv1,
pay_run_results prr,
pay_entry_process_details pepd,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_retro_components prc,
pay_retro_defn_components prdc2,
pay_retro_defn_components prdc
where paa.assignment_action_id = p_asg_act_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = pee.assignment_id
and ppa.date_earned between pee.effective_start_date and pee.effective_end_date
and pee.element_entry_id = pepd.element_entry_id
and pet1.element_type_id = pepd.source_element_type_id
and piv1.element_type_id = pet1.element_type_id
AND pee.element_entry_id = peev.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND piv.name = piv1.NAME
AND piv.uom NOT IN ('D','T','C')
AND pee.element_type_id = pet.element_type_id
and prr.element_type_id = pee.element_type_id
and prr.source_id = pee.element_entry_id
AND pee.effective_end_date between peev.effective_start_date AND
peev.effective_end_date
AND pee.effective_end_date between piv.effective_start_date AND
piv.effective_end_date
AND pee.effective_end_date between pet.effective_start_date AND
pet.effective_end_date
AND pee.effective_end_date between pet1.effective_start_date AND
pet1.effective_end_date
AND pee.effective_end_date between piv1.effective_start_date AND
piv1.effective_end_date
AND pepd.retro_component_id = prc.retro_component_id (+)
AND prc.retro_component_id = prdc.retro_component_id (+)
AND prdc.retro_definition_id (+) = p_definition_id
AND prdc2.retro_component_id (+) = p_component_id
AND prdc2.retro_definition_id (+) = p_definition_id
AND nvl(prdc.priority, 99) <= nvl(prdc2.priority, 99);
select pay_payroll_actions_s.nextval,
ppa.business_group_id,
ppa.consolidation_set_id,
ppa.payroll_id,
ppa.effective_date,
ppa.date_earned,
ppa.time_period_id,
pbg.legislation_code
into l_pactid,
l_business_group_id,
l_consolidation_set_id,
l_payroll_id,
l_effective_date,
l_date_earned,
l_time_period_id,
legcode
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_business_groups pbg
where ppa.payroll_action_id = paa.payroll_action_id
and pbg.business_group_id = ppa.business_group_id
and paa.assignment_action_id = p_asg_act_id;
insert into pay_payroll_actions(
payroll_action_id,
action_type,
business_group_id,
consolidation_set_id,
payroll_id,
action_population_status,
action_status,
effective_date,
date_earned,
time_period_id,
object_version_number)
values (
l_pactid,
'B',
l_business_group_id,
l_consolidation_set_id,
l_payroll_id,
'C',
'C',
l_effective_date,
l_date_earned,
l_time_period_id,
1);
select reprocess_date into l_min_retro_asg_date
from pay_retro_assignments
where assignment_id =p_assignment_id
and retro_assignment_action_id = p_assact_id;
update pay_assignment_actions
set serial_number = substr(fnd_date.date_to_canonical(v_recorded_date),1,11)
where assignment_action_id = p_assact_id;
select to_date(substr(serial_number, 1,11), 'YYYY/MM/DD'), assignment_id
into l_prev_rec_date, l_assign_id
from pay_assignment_actions
where assignment_action_id = p_assact_id;
delete from pay_recorded_requests
where ATTRIBUTE_CATEGORY = 'RETRO_OVERLAP'
and ATTRIBUTE1 =to_char(l_assign_id);
update pay_recorded_requests
set RECORDED_DATE = l_prev_rec_date
where ATTRIBUTE_CATEGORY = 'RETRO_OVERLAP'
and ATTRIBUTE1 = to_char(l_assign_id);