[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert of new primary address record - bug 2485
15 Jul 99 isen 115.15 Fixed bug arising out of above change
20 Jul 99 isen 115.16 Added log_abs_chg for absence change events
09 Aug 99 isen 115.17 Added new and old values
29 Sep 99 isen 115.18 Added new change events
05 Oct 99 thayden 115.19 Added Reinstate Benefit.
12 Oct 99 thayden 115.20 Fixed person type usages bug.
19 Oct 99 thayden 115.21 Fixed application bug.
09 Nov 99 thayden 115.22 Fix BC date format bug. 1050311
25 Jan 99 thayden 115.23 New arguments for TBBC and TBAC.
26 Jan 99 thayden 115.24 Added log_dependent_chg.
28 Jan 99 thayden 115.25 Old New values for COPR
30 Jan 99 thayden 115.26 Added element entries.
15 Mar 00 shdas 115.27 fixed bug 1187479. changed log_benefit_chg.
15 Mar 00 thayden 115.28 COUN old and new values.
20 Mar 00 shdas 115.29 CCFN,CCPA,CCMA,CCGE,CCDB,
added log_cont_chg
07 Nov 00 tilak 115.30 COCN- update primary country is added
01 Dec 00 rchase 115.31 Bug 1518466. Make sure the payroll is
a change and not an add.
09 Jan 00 tmathers 115.32 Bug 1530471. Added nvl
around p_new_rec.date_start.
in log_cont_chg.
24 jul 01 tilak 115.31 email can be more than 200 char ,change logh support
200 only so the value is substr to 200
26 jul o1 tilak 115.32 COPOS cahnge of periods of service added
27 oct 01 tjesumic 115.33 l_full_name lenght was 50 , changed to full name type
bug : 2070576 fixed
06 feb 02 tjesdumic 115.36 delete EE entry fixed by changing UPDATE to DELETE
11 mar 02 tjesumic 115.37 UTF8 changes , variable size set dynomically
14 Mar 02 rpillay 115.38 UTF Changes Bug 2254683
23 May 02 tjesumic 115.39 change log for per_information1, per_informatiuon10
uses_tobacco_flag added
30 may 02 tjesumic 115.40 log_per_pay procedured added for base salary change
log_school_chg procedure added
log_prem_mo_chg proecude added
Address are substring to 600 fro bug 2383576
31 may 02 tjesumic 115.41 Curosr c_old_sal cahnged ,date param added
13-Aug 02 glingapp 115.42 Bug 1554477 Modified code to trigger extract change event when
home phone, fax, work phone, mobile is entered deleted or updated,
date_to for phones is entered, when working hours of assignment is
updated
15-Jul-03 tjesumic 115.43 for COTR the effective date is passed instead of sysdate to
change event effective date
18-Jul-03 tjesumic 115.44 for COTR the actual termn date is passed instead of sysdate
Since the changes effected from termination date, termn date
became effective date for the changes
09-Jan-04 rpgupta 115.45 Bug 3361237 Do not pass date to find business group.
A person can only have one business group. Hence its ok
if we get from the first record returned
10-Jan-03 tjesumic 115.46 coen added for employee number
09-Jan-04 rpgupta 115.47 Make change eff dt for delete phones as
greatest(nvl(dt_to, dt_from), sysdate)
26-Jan-04 vjhanak 115.48 Changes made to track the creation of secondary
employeee assignment for NL.
24-Feb-05 vborkar 115.49 In calls to ben_ext_chg_evt_api.create_ext_chg_evt,
whenever person's full name was getting truncated
to 200 characters, increased it to 240 characters.
24-mar-05 tjesumic 115.50 position added in log
11-Aug-05 rgajula 115.51 Bug 4539732 Changed the date type of l_chg_eff_dt to
date table type in procedure log_pos_chg.
30-Aug-05 tjesumic 115.52 log_per_chg overload procedure created,
that will be called from SSHR api
26-Sep-05 tjesumic 115.53 ethnick orign lookup values stored for old value and new values
insted of the codes
14-Oct-05 vjhanak 115.54 Changes made to track the change to ASG Softcoded KFF id
28-Oct-05 rgajula 115.55 Bug 4699556 Made changes in the log_add_chg where previously when APA
and AMA both were triggered at the same time only APA was logged .
Chaged the code so as to log the events independent of each other.
31-Oct-05 rbingi 115.55 4705814, passing person_type ( Initially passed NULLs )
to Old, New Values for 'A%PTU' change event
27-Dec-05 rtagarra 115.57 Function "HR_GENERAL.DECODE_LOOKUP" has been called for
bug#4699913 so that meaning ll be
populated instead of lookup code for Assignment Category.
01-Sep-06 tjesumic 115.59 new change event added to log changes of contact of any person type
CCNFN,CCNDB,CCNDD,CCNSS,CCNGE,CCNRE,CCNPC,CCNPA,CCNMA
14-Sep-06 tjesumic 115.60 log_per_pay_chg's cursor c_person is opend withing the changes
14-Nov-06 tjesumic 115.62 per_pay_proposal trigger moved to api (pepyprhi.pkb 115.60)
the global record changed to have all the column of the table
for future requirements.
Rqd: pepyprhi.pkb,115.60 benxchglg.pkb 115.28,benxbstg.sql 115.3
14-Nov-06 tjesumic 115.63 approved status for old record is fixed
09-Jan-07 tjesumic 115.64 the lookup code COPMPRM changed to CCOPMPRM
09-Jan-07 tjesumic 115.65 the lookup code COPMPRM changed to CCOPMPRM
08-Jul-08 pvelugul 115.66 Modified for 6517369.
24-Nov-09 vkodedal 115.67 Overloaded log_asg_chg, called from core HR bug#9092938
09-Feb-11 vkodedal 115.68 commented second cwh logging bug#11711776
*/
--
function change_exists_in_db
(p_person_id in number
,p_chg_evt_cd in varchar2
,p_chg_eff_dt in date
) return boolean is
--
cursor get_change is
SELECT null
FROM ben_ext_chg_evt_log a
WHERE a.person_id = p_person_id
AND a.chg_evt_cd = p_chg_evt_cd
AND trunc(a.chg_eff_dt) = trunc(p_chg_eff_dt);
SELECT null
FROM hr_lookups h
WHERE h.lookup_type = 'BEN_EXT_CHG_EVT'
AND h.lookup_code = p_chg_evt_cd
AND h.enabled_flag = 'Y'
AND p_effective_date between nvl(start_date_active,p_effective_date)
and nvl(end_date_active,p_effective_date);
l_new_rec.update_mode := 'CORRECTION';
l_old_rec.update_mode := 'CORRECTION';
l_new_rec.update_mode := 'UPDATE';
l_old_rec.update_mode := 'UPDATE';
l_event := nvl(p_event ,'UPDATE') ;
select meaning
from hr_lookups hl
where hl.lookup_type = p_lookup_type
and hl.lookup_code = p_lookup_code;
select name
from ben_benfts_grp bbg
where bbg.benfts_grp_id = p_benfts_grp_id;
SELECT pcr.contact_relationship_id,pcr.person_id
FROM per_contact_relationships pcr,
hr_lookups hl
WHERE pcr.contact_person_id = p_person_id
AND pcr.contact_type = hl.lookup_code
AND hl.lookup_type = 'CONTACT';
select ppt.system_person_type
from per_person_types ppt
where ppt.person_type_id = p_person_type_id;
SELECT pcr.contact_relationship_id, pcr.person_id,pcr.contact_type
FROM per_contact_relationships pcr
WHERE pcr.contact_person_id = p_person_id
AND pcr.personal_flag = 'Y'
and p_date between pcr.date_start and nvl(pcr.date_end, p_date)
;
if p_event = 'UPDATE' then
--
if nvl(p_old_rec.national_identifier, '000-00-0000')
<> nvl(p_new_rec.national_identifier, '000-00-0000') then
l_tab_counter := l_tab_counter + 1;
,p_prmtr_10 => p_new_rec.update_mode
,p_person_id => p_new_rec.person_id
,p_business_group_id => p_new_rec.business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_new_rec.effective_start_date
,p_old_val1 => l_old_val1(l_count)
,p_old_val2 => l_old_val2(l_count)
,p_old_val3 => l_old_val3(l_count)
,p_old_val4 => l_old_val4(l_count)
,p_old_val5 => l_old_val5(l_count)
,p_old_val6 => l_old_val6(l_count)
,p_new_val1 => l_new_val1(l_count)
,p_new_val2 => l_new_val2(l_count)
,p_new_val3 => l_new_val3(l_count)
,p_new_val4 => l_new_val4(l_count)
,p_new_val5 => l_new_val5(l_count)
,p_new_val6 => l_new_val6(l_count)
);
,p_prmtr_10 => p_new_rec.update_mode
,p_person_id => l_relationship
,p_business_group_id => p_new_rec.business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_new_rec.effective_start_date
,p_old_val1 => l_old_val1(l_count)
,p_old_val2 => l_old_val2(l_count)
,p_old_val3 => l_old_val3(l_count)
,p_old_val4 => l_old_val4(l_count)
,p_old_val5 => l_old_val5(l_count)
,p_old_val6 => l_old_val6(l_count)
,p_new_val1 => l_new_val1(l_count)
,p_new_val2 => l_new_val2(l_count)
,p_new_val3 => l_new_val3(l_count)
,p_new_val4 => l_new_val4(l_count)
,p_new_val5 => l_new_val5(l_count)
,p_new_val6 => l_new_val6(l_count)
);
l_cont_chg_evt_tab.delete ;
,p_prmtr_10 => p_new_rec.update_mode
,p_person_id => l_relationship
,p_business_group_id => p_new_rec.business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_new_rec.effective_start_date
,p_old_val1 => l_old_val1(l_count)
,p_old_val2 => l_old_val2(l_count)
,p_old_val3 => l_old_val3(l_count)
,p_old_val4 => l_old_val4(l_count)
,p_old_val5 => l_old_val5(l_count)
,p_old_val6 => l_old_val6(l_count)
,p_new_val1 => l_new_val1(l_count)
,p_new_val2 => l_new_val2(l_count)
,p_new_val3 => l_new_val3(l_count)
,p_new_val4 => l_new_val4(l_count)
,p_new_val5 => l_new_val5(l_count)
,p_new_val6 => l_new_val6(l_count)
);
select ppt.system_person_type,ppf.full_name
from per_person_types ppt,per_all_people_f ppf
where ppf.person_type_id = ppt.person_type_id
and ppf.person_id = p_person_id;
select meaning
from hr_lookups hl
where hl.lookup_type = 'CONTACT'
and hl.lookup_code = p_lookup_code;
SELECT pcr.contact_relationship_id,pcr.person_id,ppf.full_name
FROM per_contact_relationships pcr,
per_all_people_f ppf,
per_person_types ppt,
hr_lookups hl
WHERE pcr.contact_person_id = p_person_id
AND ppf.person_id = p_person_id
AND ppf.person_type_id = ppt.person_type_id
AND ppt.system_person_type = 'OTHER'
AND pcr.contact_type = hl.lookup_code
AND hl.lookup_type = 'CONTACT';
SELECT pcr.contact_relationship_id,pcr.person_id,ppf.full_name
FROM per_contact_relationships pcr,
per_all_people_f ppf,
hr_lookups hl
WHERE pcr.contact_person_id = p_person_id
AND ppf.person_id = p_person_id
AND pcr.contact_type = hl.lookup_code
AND hl.lookup_type = 'CONTACT'
AND pcr.personal_flag = 'Y'
and p_effective_date between pcr.date_start and nvl(pcr.date_end, p_effective_date)
;
if p_event = 'INSERT' then
l_person_id := p_new_rec.person_id;
elsif p_event = 'DELETE' then
l_person_id := p_old_rec.person_id;
elsif p_event = 'UPDATE' then
-- cache this data for later use.
hr_utility.set_location(' in address changes ' , 673);
l_new_rec.update_mode := 'CORRECTION';
l_old_rec.update_mode := 'CORRECTION';
l_new_rec.update_mode := 'UPDATE';
l_old_rec.update_mode := 'UPDATE';
l_event := nvl(p_event ,'UPDATE') ;
select name
from HR_ALL_POSITIONS_F pos
where pos.position_id = p_position_id
and p_effective_date between nvl(pos.effective_start_date, sysdate)
and nvl(pos.effective_end_date, sysdate)
;
select location_code
from hr_locations_all hl
where hl.location_id = p_location_id;
select ast.user_status
from per_assignment_status_types ast
where ast.assignment_status_type_id = p_assignment_status_type_id;
select ppf.payroll_name
from pay_all_payrolls_f ppf
where payroll_id = p_payroll_id
and p_effective_date between nvl(ppf.effective_start_date, sysdate)
and nvl(ppf.effective_end_date, sysdate);
select g.name
from per_grades g
where g.grade_id = p_grade_id
and g.date_from <= p_effective_date
and ((g.date_to is null) or (g.date_to >= p_effective_date )) ;
if p_event = 'UPDATE' then
--
if nvl(p_old_rec.assignment_status_type_id, 1)
<> nvl(p_new_rec.assignment_status_type_id, 1) then
-- read old and new assignment status
open c_assignment_status(p_old_rec.assignment_status_type_id);
elsif p_event = 'INSERT' then
if p_new_rec.assignment_type = 'B' then
--l_person_id := p_new_rec.person_id;
,p_prmtr_10 => p_new_rec.update_mode
,p_person_id => p_new_rec.person_id
,p_business_group_id => p_new_rec.business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_new_rec.effective_start_date
,p_new_val1 => l_new_val1(l_count)
,p_old_val1 => l_old_val1(l_count)
);
select aat.name
from per_absence_attendance_types aat
where aat.absence_attendance_type_id = p_absence_attendance_type_id;
select aar.name
from per_abs_attendance_reasons aar
where aar.abs_attendance_reason_id = p_abs_attendance_reason_id;
if p_event = 'UPDATE' then
--
l_business_group_id := p_new_rec.business_group_id;
elsif p_event = 'INSERT' then
--
open c_absence_type(p_new_rec.absence_attendance_type_id);
elsif p_event = 'DELETE' then
--
open c_absence_type(p_old_rec.absence_attendance_type_id);
,p_prmtr_10 => p_new_rec.update_mode
,p_person_id => l_person_id
,p_business_group_id => l_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => l_chg_eff_dt
,p_new_val1 => l_new_val1(l_count)
,p_new_val2 => l_new_val2(l_count)
,p_new_val3 => l_new_val3(l_count)
,p_new_val4 => l_new_val4(l_count)
,p_old_val1 => l_old_val1(l_count)
,p_old_val2 => l_old_val2(l_count)
,p_old_val3 => l_old_val3(l_count)
,p_old_val4 => l_old_val4(l_count)
);
p_action in varchar2, -- CREATE, REINSTATE, UPDATE, or DELETE
p_pl_id in number default null,
p_old_pl_id in number default null,
p_oipl_id in number default null,
p_old_oipl_id in number default null,
p_enrt_cvg_strt_dt in date default null,
p_enrt_cvg_end_dt in date default null,
p_old_enrt_cvg_strt_dt in date default null,
p_old_enrt_cvg_end_dt in date default null,
p_bnft_amt in number default null,
p_old_bnft_amt in number default null,
p_pen_attribute1 in varchar2 default null,
p_pen_attribute2 in varchar2 default null,
p_pen_attribute3 in varchar2 default null,
p_pen_attribute4 in varchar2 default null,
p_pen_attribute5 in varchar2 default null,
p_pen_attribute6 in varchar2 default null,
p_pen_attribute7 in varchar2 default null,
p_pen_attribute8 in varchar2 default null,
p_pen_attribute9 in varchar2 default null,
p_pen_attribute10 in varchar2 default null,
p_old_pen_attribute1 in varchar2 default null,
p_old_pen_attribute2 in varchar2 default null,
p_old_pen_attribute3 in varchar2 default null,
p_old_pen_attribute4 in varchar2 default null,
p_old_pen_attribute5 in varchar2 default null,
p_old_pen_attribute6 in varchar2 default null,
p_old_pen_attribute7 in varchar2 default null,
p_old_pen_attribute8 in varchar2 default null,
p_old_pen_attribute9 in varchar2 default null,
p_old_pen_attribute10 in varchar2 default null,
p_effective_start_date in date default null,
p_effective_end_date in date default null,
p_prtt_enrt_rslt_id in number default null,
p_old_prtt_enrt_rslt_id in number default null,
p_per_in_ler_id in number default null,
p_old_per_in_ler_id in number default null,
p_person_id in number,
p_business_group_id in number,
p_effective_date in date ) is
-- Local variable declarations
--
l_proc varchar2(72) := 'ben_ext_chlg.log_benefit_chg';
select name
from ben_pl_f pl
where pl.pl_id = p_pl_id
and p_effective_date between pl.effective_start_date and pl.effective_end_date;
select opt.name
from ben_oipl_f cop,
ben_opt_f opt
where cop.oipl_id = p_oipl_id
and p_effective_date between cop.effective_start_date and cop.effective_end_date
and cop.opt_id = opt.opt_id
and p_effective_date between opt.effective_start_date and opt.effective_end_date;
a benefit amount update in which case we want the change log
to show 'Update enrollment coverage amount'--shdas*/
if p_old_pl_id is not null and p_pl_id = p_old_pl_id then
if p_old_oipl_id is null or (p_old_oipl_id
is not null and p_oipl_id = p_old_oipl_id) then
if nvl(p_bnft_amt,0) <> nvl(p_old_bnft_amt,0) and
change_event_is_enabled('COECA',p_effective_date) then
ben_ext_chg_evt_api.create_ext_chg_evt
(p_validate => FALSE
,p_ext_chg_evt_log_id => l_ext_chg_evt_log_id
,p_chg_evt_cd => 'COECA' -- Change Coverage Amount
,p_chg_eff_dt => p_effective_date
,p_prmtr_01 => to_char(p_pl_id)
,p_prmtr_02 => to_char(p_oipl_id)
,p_prmtr_03 => to_char(p_prtt_enrt_rslt_id)
,p_prmtr_05 => to_char(p_per_in_ler_id)
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_old_val1 => p_old_bnft_amt
,p_new_val1 => p_bnft_amt
);
if p_action = 'UPDATE' then
if p_pl_id <> p_old_pl_id then
--
-- For Extract purposes, we recognize an update to plan as 2 separate
-- changes: Terminate Benefit, and Add Benefit.
--
if p_effective_date < p_old_enrt_cvg_strt_dt then
--
open c_plan_name(p_old_pl_id);
end if; -- UPDATE
if p_action = 'DELETE' then
--
if nvl(p_enrt_cvg_end_dt,p_effective_date) < p_enrt_cvg_strt_dt then
--
open c_plan_name(p_old_pl_id);
end if; -- DELETE
p_action in varchar2, -- CREATE, REINSTATE, or DELETE
p_pl_id in number default null,
p_oipl_id in number default null,
p_cvg_strt_dt in date default null,
p_cvg_end_dt in date default null,
p_old_cvg_strt_dt in date default null,
p_old_cvg_end_dt in date default null,
p_effective_start_date in date default null,
p_effective_end_date in date default null,
p_prtt_enrt_rslt_id in number default null,
p_per_in_ler_id in number default null,
p_elig_cvrd_dpnt_id in number default null,
p_person_id in number,
p_dpnt_person_id in number,
p_business_group_id in number,
p_effective_date in date) is
-- Local variable declarations
--
l_proc varchar2(72) := 'ben_ext_chlg.log_dependent_chg';
select pl.name
from ben_pl_f pl
where pl.pl_id = p_pl_id
and p_effective_date between pl.effective_start_date and pl.effective_end_date;
select opt.name
from ben_oipl_f cop,
ben_opt_f opt
where cop.oipl_id = p_oipl_id
and p_effective_date between cop.effective_start_date and cop.effective_end_date
and cop.opt_id = opt.opt_id
and p_effective_date between opt.effective_start_date and opt.effective_end_date;
SELECT ppf.full_name
FROM per_all_people_f ppf
WHERE ppf.person_id = p_person_id
AND p_effective_date between ppf.effective_start_date
and ppf.effective_end_date
AND ppf.business_group_id = p_business_group_id;
SELECT hl.meaning
FROM per_contact_relationships pcr,
hr_lookups hl
WHERE pcr.person_id = p_person_id
AND pcr.contact_person_id = p_dpnt_person_id
AND pcr.contact_type = hl.lookup_code
AND hl.lookup_type = 'CONTACT';
elsif p_action = 'DELETE' then
--
if p_cvg_end_dt < p_cvg_strt_dt then
--
if change_event_is_enabled('TBBC',p_effective_date) then
ben_ext_chg_evt_api.create_ext_chg_evt
(p_validate => FALSE
,p_ext_chg_evt_log_id => l_ext_chg_evt_log_id
,p_chg_evt_cd => 'TBBC' -- Terminate Benefit before Coverage
,p_chg_eff_dt => p_effective_date
,p_prmtr_01 => to_char(p_pl_id)
,p_prmtr_02 => to_char(p_oipl_id)
,p_prmtr_03 => to_char(p_prtt_enrt_rslt_id)
,p_prmtr_04 => to_char(p_elig_cvrd_dpnt_id)
,p_prmtr_05 => to_char(p_per_in_ler_id)
,p_prmtr_06 => to_char(p_person_id)
,p_person_id => p_dpnt_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_old_val1 => l_plan_name
,p_old_val2 => l_option_name
,p_old_val3 => p_old_cvg_strt_dt
,p_old_val4 => p_old_cvg_end_dt
,p_new_val1 => l_plan_name
,p_new_val2 => l_option_name
,p_new_val3 => p_cvg_strt_dt
,p_new_val4 => p_cvg_end_dt
);
,p_chg_evt_cd => 'DD' -- Delete Dependent
,p_chg_eff_dt => p_effective_date
,p_prmtr_01 => to_char(p_pl_id)
,p_prmtr_02 => to_char(p_oipl_id)
,p_prmtr_03 => to_char(p_prtt_enrt_rslt_id)
,p_prmtr_04 => to_char(p_elig_cvrd_dpnt_id)
,p_prmtr_05 => to_char(p_per_in_ler_id)
,p_prmtr_06 => to_char(p_dpnt_person_id)
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_old_val1 => l_plan_name
,p_old_val2 => l_option_name
,p_old_val3 => p_old_cvg_strt_dt
,p_old_val4 => p_old_cvg_end_dt
,p_old_val5 => l_dpnt_full_name
,p_old_val6 => l_relationship
,p_new_val1 => l_plan_name
,p_new_val2 => l_option_name
,p_new_val3 => p_cvg_strt_dt
,p_new_val4 => p_cvg_end_dt
,p_new_val5 => l_dpnt_full_name
,p_new_val6 => l_relationship
);
SELECT pcr.contact_relationship_id,pcr.person_id,ppf.full_name
FROM per_contact_relationships pcr,
per_all_people_f ppf,
per_person_types ppt,
hr_lookups hl
WHERE pcr.contact_person_id = p_person_id
AND ppf.person_id = p_person_id
AND ppf.person_type_id = ppt.person_type_id
AND ppt.system_person_type = 'OTHER'
AND pcr.contact_type = hl.lookup_code
AND hl.lookup_type = 'CONTACT';
SELECT pcr.contact_relationship_id,pcr.person_id,ppf.full_name
FROM per_contact_relationships pcr,
per_all_people_f ppf,
hr_lookups hl
WHERE pcr.contact_person_id = p_person_id
AND ppf.person_id = p_person_id
AND pcr.contact_type = hl.lookup_code
AND hl.lookup_type = 'CONTACT'
AND pcr.personal_flag = 'Y'
and p_effective_date between pcr.date_start and nvl(pcr.date_end, p_effective_date)
;
select pen.person_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date;
select ecd.dpnt_person_id
from ben_elig_cvrd_dpnt_f ecd
where ecd.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and ecd.business_group_id = p_business_group_id
and p_effective_date between ecd.effective_start_date and ecd.effective_end_date;
select hl.meaning
from hr_lookups hl
where hl.lookup_type = 'BEN_PRMRY_CARE_PRVDR_TYP'
and hl.lookup_code = prmry_care_prvdr_typ_cd;
elsif p_action = 'UPDATE' then
if p_prmry_care_prvdr_typ_cd <> p_old_prmry_care_prvdr_typ_cd then
--
if change_event_is_enabled('COPT',p_effective_date) then
l_pcp_chg := TRUE;
elsif p_action = 'DELETE' then
--
if change_event_is_enabled('DPCP',p_effective_date) then
l_pcp_chg := TRUE;
elsif p_action = 'UPDATE' then
if p_prmry_care_prvdr_typ_cd is not null then
v_prmry_care_prvdr_typ_cd := l_prmry_care_prvdr_typ_cd;
elsif p_action = 'DELETE' then
ben_ext_chg_evt_api.create_ext_chg_evt
(p_validate => FALSE
,p_ext_chg_evt_log_id => l_ext_chg_evt_log_id
,p_chg_evt_cd => 'CCPC'
,p_chg_eff_dt => p_effective_date
,p_prmtr_01 => l_relationship
,p_prmtr_03 => p_prtt_enrt_rslt_id
,p_prmtr_04 => p_elig_cvrd_dpnt_id
,p_prmtr_08 => p_prmry_care_prvdr_id
,p_person_id => l_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_old_val1 => substr(l_name,1,240)
,p_old_val2 => p_old_name
,p_old_val3 => l_old_prmry_care_prvdr_typ_cd);
elsif p_action = 'UPDATE' then
if p_prmry_care_prvdr_typ_cd is not null then
v_prmry_care_prvdr_typ_cd := l_prmry_care_prvdr_typ_cd;
elsif p_action = 'DELETE' then
ben_ext_chg_evt_api.create_ext_chg_evt
(p_validate => FALSE
,p_ext_chg_evt_log_id => l_ext_chg_evt_log_id
,p_chg_evt_cd => 'CCNPC'
,p_chg_eff_dt => p_effective_date
,p_prmtr_01 => l_relationship
,p_prmtr_03 => p_prtt_enrt_rslt_id
,p_prmtr_04 => p_elig_cvrd_dpnt_id
,p_prmtr_08 => p_prmry_care_prvdr_id
,p_person_id => l_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_old_val1 => substr(l_name,1,240)
,p_old_val2 => p_old_name
,p_old_val3 => l_old_prmry_care_prvdr_typ_cd);
if p_event = 'UPDATE' then
--
if p_old_rec.actual_termination_date is null and p_new_rec.actual_termination_date is not null then
l_tab_counter := l_tab_counter + 1;
elsif p_event = 'INSERT' then
l_tab_counter := l_tab_counter + 1;
,p_prmtr_10 => p_new_rec.update_mode
,p_person_id => p_new_rec.person_id
,p_business_group_id => p_new_rec.business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => l_chg_eff_dt(l_count)
,p_new_val1 => l_new_val1(l_count)
,p_old_val1 => l_old_val1(l_count)
);
select hl.meaning
from hr_lookups hl
where hl.lookup_type = 'TERM_APL_REASON'
and hl.lookup_code = p_termination_reason
and hl.enabled_flag = 'Y';
select person_id
from ben_per_in_ler pil,
ben_prtt_prem_f ppe
where ppe.prtt_prem_id = p_prtt_prem_id
and ppe.per_in_ler_id = pil.per_in_ler_id ;
select name from PER_ESTABLISHMENTS
where establishment_id = p_establishment_id ;
select person_id ,business_group_id
from per_all_Assignments_f asg
where asg.assignment_id = p_assignment_id ;
select
proposed_salary_n, approved
from per_pay_proposals b
where
b.assignment_id = p_assignment_id
and b.change_date =
(select max(d.change_date)
from per_pay_proposals d
where d.assignment_id = b.assignment_id
and approved = 'Y'
and change_date < p_change_date )
;
if p_event = 'INSERT' then
hr_utility.set_location('change_date' || p_new_rec.change_date,10);
select meaning
from hr_lookups hl
where hl.lookup_type = 'TERM_APL_REASON'
and hl.lookup_code = p_termination_reason
and hl.enabled_flag = 'Y';
select ppf.business_group_id
from per_all_people_f ppf
where ppf.person_id = p_person_id;
l_update_mode varchar2(15); --Bug 1554477
if p_event = 'UPDATE' and p_new_rec.parent_table = 'PER_ALL_PEOPLE_F'
and p_new_rec.phone_type in ('H1', 'W1', 'HF','M') then
--
--Start Bug 1554477
if p_old_rec.date_to is null and
p_new_rec.date_to is not null then
if p_new_rec.phone_type = 'H1' then
l_chg_evt_code := 'AHPDT';
l_update_mode := p_new_rec.update_mode;
l_update_mode := p_new_rec.update_mode;
elsif p_event = 'INSERT' and p_new_rec.parent_table = 'PER_ALL_PEOPLE_F'
and p_new_rec.phone_type in ('H1', 'W1', 'HF', 'M') then
--
if p_old_rec.date_to is null and
p_new_rec.date_to is not null then
if p_new_rec.phone_type = 'H1' then
l_chg_evt_code := 'AHPDT';
l_update_mode := p_new_rec.update_mode;
l_update_mode := p_new_rec.update_mode;
elsif p_event = 'DELETE' and p_old_rec.parent_table = 'PER_ALL_PEOPLE_F'
and p_old_rec.phone_type in ('H1', 'W1', 'HF', 'M') then
--
if p_old_rec.phone_type = 'H1' then
l_chg_evt_code := 'DHP';
l_update_mode := p_old_rec.update_mode;
,p_prmtr_10 => l_update_mode --Bug 1554477
,p_person_id => l_person_id --Bug 1554477
,p_business_group_id => l_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => l_chg_eff_dt
,p_new_val1 => l_new_val1(l_count)
,p_old_val1 => l_old_val1(l_count)
);
select ppt.system_person_type, ppt.user_person_type, ppt.business_group_id
from per_person_types ppt
where ppt.person_type_id = p_person_type_id;
if p_event = 'INSERT' then
--
open c_person_type(p_new_rec.person_type_id);
,p_prmtr_10 => p_new_rec.update_mode
,p_person_id => p_new_rec.person_id
,p_business_group_id => l_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => l_chg_eff_dt
,p_new_val1 => l_new_val1(l_count)
,p_old_val1 => l_old_val1(l_count)
);
select iv.name, et.element_name
from pay_input_values_f iv,
pay_element_types_f et
where iv.input_value_id = p_input_value_id
and iv.element_type_id = et.element_type_id
and p_effective_date between iv.effective_start_date and iv.effective_end_date
and p_effective_date between et.effective_start_date and et.effective_end_date;
elsif p_action = 'UPDATE' then
if change_event_is_enabled('UEE',p_effective_date) then
ben_ext_chg_evt_api.create_ext_chg_evt
(p_validate => FALSE
,p_ext_chg_evt_log_id => l_ext_chg_evt_log_id
,p_chg_evt_cd => 'UEE'
,p_chg_eff_dt => p_effective_date
,p_prmtr_01 => p_element_entry_id
,p_prmtr_02 => p_input_value_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_old_val1 => l_element_info.element_name
,p_old_val2 => l_element_info.name
,p_old_val3 => p_old_amt
,p_new_val1 => l_element_info.element_name
,p_new_val2 => l_element_info.name
,p_new_val3 => p_amt);
elsif p_action = 'DELETE' then
hr_utility.set_location('deleteing element entry ' , 195);
select meaning
from hr_lookups hl
where hl.lookup_type = p_lookup_type
and hl.lookup_code = p_lookup_code;
if p_event = 'UPDATE' then
if nvl(p_old_rec.incident_id,1) <> nvl(p_new_rec.incident_id,1) then
select incident_reference into l_old_incident
from per_work_incidents
where incident_id = p_old_rec.incident_id ;
select incident_reference into l_new_incident
from per_work_incidents
where incident_id = l_new_incident ;
select name into l_old_organization
from hr_all_organization_units
where organization_id = p_old_rec.organization_id ;
select name into l_new_organization
from hr_all_organization_units
where organization_id = p_new_rec.organization_id ;
,p_prmtr_10 => p_new_rec.update_mode
,p_person_id => p_new_rec.person_id
,p_business_group_id => p_new_rec.business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_new_rec.effective_start_date
,p_old_val1 => l_old_val1(l_count)
,p_old_val2 => l_old_val2(l_count)
,p_old_val3 => l_old_val3(l_count)
,p_old_val4 => l_old_val4(l_count)
,p_old_val5 => l_old_val5(l_count)
,p_old_val6 => l_old_val6(l_count)
,p_new_val1 => l_new_val1(l_count)
,p_new_val2 => l_new_val2(l_count)
,p_new_val3 => l_new_val3(l_count)
,p_new_val4 => l_new_val4(l_count)
,p_new_val5 => l_new_val5(l_count)
,p_new_val6 => l_new_val6(l_count)
);