[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
rem 115.2 8/9/2006 nhunur person selection rule changes.
rem 115.3 10/3/2006 gsehgal SSN no was not printed and from date parameter
rem was not displayed
rem 115.4 10/13/2006 gsehgal Bug: 5589226. Process was erroring out when
rem no persons were selected.
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
*/
--
-- global variables
g_package VARCHAR2 (80) := 'ben_reopen_ler_conc';
p_person_selection_rl IN NUMBER DEFAULT NULL,
p_debug_messages IN VARCHAR2 DEFAULT 'N'
)
IS
--
-- Local variable declaration.
--
l_proc VARCHAR2 (100) := g_package || '.process';
SELECT per.person_id, pil.per_in_ler_id
FROM per_all_people_f per, ben_per_in_ler pil, ben_ler_f ler
WHERE pil.person_id = per.person_id
AND pil.per_in_ler_stat_cd = 'PROCD'
AND pil.ler_id = p_ler_id
AND pil.ler_id = ler.ler_id
AND l_effective_date BETWEEN ler.effective_start_date
AND ler.effective_end_date
AND l_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND ler.typ_cd NOT IN ('GSP', 'COMP', 'SCHEDDU', 'ABS', 'IREC')
AND pil.lf_evt_ocrd_dt >= l_from_ocrd_date
AND ( p_organization_id IS NULL
OR EXISTS (
SELECT NULL
FROM per_all_assignments_f paa
WHERE paa.person_id = per.person_id
AND l_effective_date
BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND paa.business_group_id = per.business_group_id
AND paa.primary_flag = 'Y'
AND paa.organization_id = p_organization_id)
)
AND ( p_location_id IS NULL
OR EXISTS (
SELECT NULL
FROM per_all_assignments_f paa
WHERE paa.person_id = per.person_id
AND l_effective_date
BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND paa.business_group_id = per.business_group_id
AND paa.primary_flag = 'Y'
AND paa.location_id = p_location_id)
)
AND ( p_benfts_grp_id IS NULL
OR EXISTS (
SELECT NULL
FROM per_all_people_f pap
WHERE pap.person_id = per.person_id
AND pap.business_group_id = per.business_group_id
AND l_effective_date
BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND pap.benefit_group_id = p_benfts_grp_id)
)
AND ( p_legal_entity_id IS NULL
OR EXISTS (
SELECT NULL
FROM per_assignments_f paf,
hr_soft_coding_keyflex soft
WHERE paf.person_id = per.person_id
AND paf.assignment_type <> 'C'
AND l_effective_date
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.business_group_id = per.business_group_id
AND paf.primary_flag = 'Y'
AND soft.soft_coding_keyflex_id =
paf.soft_coding_keyflex_id
AND soft.segment1 = TO_CHAR (p_legal_entity_id))
)
ORDER BY pil.person_id ASC, pil.lf_evt_ocrd_dt DESC;
p_person_selection_rl => p_person_selection_rl,
p_ler_id => p_ler_id,
p_organization_id => p_organization_id,
p_benfts_grp_id => p_benfts_grp_id,
p_location_id => p_location_id,
p_legal_entity_id => p_legal_entity_id,
p_debug_messages_flag => p_debug_messages,
p_object_version_number => l_object_version_number,
p_effective_date => l_effective_date,
p_request_id => fnd_global.conc_request_id,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_program_update_date => SYSDATE,
p_date_from => l_from_ocrd_date
);
DELETE FROM ben_batch_ranges
WHERE benefit_action_id = l_benefit_action_id;
If p_person_selection_rl is not NULL then
--
ben_batch_utils.person_selection_rule
(p_person_id => l_rec.person_id
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id=> p_person_selection_rl
,p_effective_date => l_effective_date
,p_return => l_person_ok
,p_err_message => l_err_message );
p_person_selection_rule_id => p_person_selection_rl,
p_ler_id => p_ler_id,
p_organization_id => p_organization_id,
p_benfts_grp_id => p_benfts_grp_id,
p_location_id => p_location_id,
p_legal_entity_id => p_legal_entity_id
);
p_person_selected => l_person_actn_cnt,
p_business_group_id => p_business_group_id
);
p_person_selected => l_person_actn_cnt,
p_business_group_id => p_business_group_id
);
SELECT ran.range_id, ran.starting_person_action_id,
ran.ending_person_action_id
FROM ben_batch_ranges ran
WHERE ran.range_status_cd = 'U'
AND ran.benefit_action_id = p_benefit_action_id
AND ROWNUM < 2
FOR UPDATE OF ran.range_status_cd;
SELECT ben.person_id, ben.person_action_id,
ben.object_version_number, ben.ler_id
FROM ben_person_actions ben
WHERE ben.benefit_action_id = p_benefit_action_id
AND ben.action_status_cd <> 'P'
AND ben.person_action_id BETWEEN l_start_person_action_id
AND l_end_person_action_id
ORDER BY ben.person_action_id;
SELECT *
FROM ben_benefit_actions ben
WHERE ben.benefit_action_id = p_benefit_action_id;
SELECT NAME
FROM ben_ler_f
WHERE ler_id = p_ler_id;
SELECT lf_evt_ocrd_dt, object_version_number
FROM ben_per_in_ler
WHERE per_in_ler_id = p_per_in_ler_id;
p_person_selection_rule_id => l_parm.person_selection_rl,
p_organization_id => l_parm.organization_id,
p_benfts_grp_id => l_parm.benfts_grp_id,
p_location_id => l_parm.location_id,
p_legal_entity_id => l_parm.legal_entity_id,
p_ler_id => p_ler_id
);
UPDATE ben_batch_ranges ran
SET ran.range_status_cd = 'P'
WHERE ran.range_id = l_range_id;
hr_utility.set_location ('Updated range '|| TO_CHAR (l_range_id)|| ' status code to P',10);
g_cache_per_proc.DELETE;
UPDATE ben_person_actions
SET action_status_cd = 'T'
WHERE person_action_id = l_person_action_id;
UPDATE ben_person_actions
SET action_status_cd = 'P'
WHERE person_action_id = g_cache_per_proc (l_cnt).person_action_id;
UPDATE ben_person_actions
SET action_status_cd = 'E'
WHERE person_action_id = g_cache_per_proc(l_cnt).person_action_id;