[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* will get the WS status updated to
* processed
* 02-MAR-02 aprabhak 115.4 Added a new message for effective date
* before assigned life event date
* 08-MAR-02 aprabhak 115.5 Modified the salary rise to consider
* the precision. The logging will be done
* only when you have the audit_log flag is
* 'Y'. Modified the choice loop to avoid
* the multi-row edit for the persons of
* x-bg. Modified the name of the routines
* of default_comp_obj and process_default
* _enrt.
* 10-MAR-02 aprabhak 115.6 The salary conversions now uses the newly
* developed benutils routine plan_to_basis_
* _conversion. Change the position of the
* p_preson_id in the process routine.
* 11-MAR-02 aprabhak 115.7 added the per_pay_bases to the element
* cursor and approved condition to the
* salary cursor
* 15-MAR-02 aprabhak 115.8 after the pp runs the worksheet access
* to RO it the prior access is updateable
* 18-MAR-02 aprabhak 115.9 the parameter p_annulization_factor is
* changed to p_assignment_id to the call
* to plan_to_basis_conversion.
* 27-MAR-02 aprabhak 115.10 Adding the business_group_id condition
* to the c_pl_typ_rt_val cursor to avoid
* full table scan.
* 03-Sep-02 maagrawa 115.11 Added new procedures for promotion
* and performance rating updates.
* 08-Nov-02 aprabhak 115.12 Included the changes for itemization.
* 19-Feb-03 maagrawa 115.13 Make calls to ben_cwb_asg_update to
* update performance and promotions.
* 05-Feb-03 aprabhak 115.14 Fixed 2815207
* 11-Mar-03 pbodla 115.15 Changes for : 2695023 - When pay proposal
* is created link it to associated
* participant rate row to allow backout if
* needed.
* 13-Mar-03 pbodla 115.16 Changes for : 2695023 - Fixed typo in
* update_prtt_rt_val api call.
* 20-Mar-03 aprabhak 115.17 Fixed 2857327 and 2357197.
* 20-May-03 aprabhak 115.18 Fixed 2968662.
* 18-Jun-03 aprabhak 115.19 Fixed 3011682
* 27-Jul-03 aprabhak 115.20 Fixed 3005203
* 09-sep-03 sthota 115.21 Modified the effective date format. Fixed
* 3084042
* 09-Oct-03 sthota 115.26 Fixing the bug 3084042.
* 23-Nov-03 aprabhak 115.27 Fixing the bug 3259373.
* 02-Jan-04 aprabhak 115.28 Global Budgeting
* 10-Feb-04 aprabhak 115.29 Ignore backed out pils in pil cursor
* 22-Feb-04 aprabhak 115.31 Uncommented code for comp posting date
* 22-Feb-04 aprabhak 115.32 nvl condition to p_debug_level
* 08-Mar-04 aprabhak 115.36 Fixed 3490171.
* 09-Mar-04 aprabhak 115.37 Fixed the null rows in audit log report
* 16-Mar-04 aprabhak 115.38 For Drop3
* 20-Mar-04 aprabhak 115.39 Fixed 3490387, 3484230
* 25-May-04 maagrawa 115.40 Splitting of perf/promo record.
* 04-Jun-04 aprabhak 115.41 Corrected the threading issue.
* 04-Jun-04 aprabhak 115.42 Corrected the thread process name
* 29-Jun-04 aprabhak 115.43 Fixed the bug #3712169
* 30-Jun-04 aprabhak 115.44 bg_id passed to the assignment changes
* is obtained ben_cwb_person_info
* 06-Jul-04 aprabhak 115.45 Access Change Routine
* 09-Jul-04 aprabhak 115.46 Recent Sal Change Message Corrected
* 14-Jul-04 aprabhak 115.47 fixed the issues reported in drop 13
* 22-Jul-04 aprabhak 115.48 Fixed the caching issues in error report.
* 10-Dec-04 aprabhak 115.49 Fixed bug#4030870
* 01-Feb-05 steotia 115.50 ben_cwb_audit_api call to record end of
* Compensation event post process
* 31-May-05 steotia 115.51 Bugfix 4387327
* 25-Jul-05 steotia 115.52 Bugfix 4503153
* 28-Jul-05 maagrawa 115.53 4510733: Auto Aprove pay proposal
* when components are involved.
* 01-Aug-05 steotia 115.54 Added NOCOPY hint
* 05-Oct-05 steotia 115.55 4607721: Fix in process of rating.
* 17-Nov-05 maagrawa 115.56 Do not error for terminated emps
* when posting salary/elements.
* 28-Nov-05 maagrawa 115.57 4752433:Allow salary components along
* with other compensation components.
* 30-Nov-05 maagrawa 115.58 Fixed no-data-found error with optName.
* 06-Dec-05 maagrawa 115.59 Fixed salary components cursors.
* 04-Jan-06 steotia 115.60 Override dates functionality added.
* 14-Feb-06 steotia 115.61 4997896:Termination check for perf/prom
* not on run date but resp. eff/ovrd date
* 06-Mar-06 steotia 115.62 Enhancing logging for new audit report
* and logging
* 16-Mar-06 steotia 115.63 same as above
* 21-Mar-06 steotia 115.64 equalising population across
* compensation, perf or asgn changes
* 22-Mar-06 steotia 115.65 5109850: taking in LE date as varchar2
* 23-Mar-06 steotia 115.66 Fixing component plan logging
* [l_warning_text size increased, logging
* at -1 level also for component plan,
* ws_sub_acty_typ_cd added to comp cursor],
* All or nothing error flagging,
* elmnt_processing_type for recurring el,
* ineligible persons logged
* 03-Apr-06 steotia 115.67 Logging even in error for sal_rate
* Element determintion rule called
* 07-Apr-06 steotia 115.68 5141153: Corrections on el. detn. rule
* 12-Apr-06 steotia 115.70 Fixing a possible char to numeric convern.
* problem in extracting message number
* 26-Apr-06 steotia 115.71 Fixing component plan logging for plan
* lvl amount posted record, checking
* input currency of element, inserting
* 2 new error messages
* 29-Apr-06 steotia 115.73 Correcting sal change reason logic
* 10-May-06 steotia 115.74 5158117: Non-Mon rate exclusion
* 5181394: Future dated sal prop warning
* 16-May-06 steotia 115.75 5222874: missing data for recurring element
* 16-May-06 steotia 115.76 5158117: Salary basis element check add
* 19-May-06 steotia 115.78 Logging changes
* 14-Jul-06 steotia 115.79 Added force close of LE
* 17-Jul-06 steotia 115.80 5392779: Properly converting base_salary
* 5375170: String overflow error
* 11-Aug-06 steotia 115.81 5413842: In case of one emp in multiple
* local plan
* 25-Aug-06 steotia 115.82 5487492: No force close in rollback
* 12-Sep-06 steotia 115.83 5413842: Downloads need complete rows
* 13-Sep-06 steotia 115.84 5483387: Wrong order of concatenation
* 13-Sep-06 steotia 115.85 5528259: (+) in c_posted_promotions reqd.
* 20-Sep-06 steotia 115.86 5531065: Using Performance Overrides (but
* only if used through SS)
* 28-Sep-06 steotia 115.87 5413842: moving to parent thread
* 05-Oct-06 steotia 115.88 Putting do_not_process_flag check with
* rates to take care of multiple
* enrollments
* 17-Oct-06 steotia 115.89 5527054: using 5 precision if uom of
* input_value is not null or Money
* 5460693: using option level effective
* for salary proposal changes
* 02-Nov-06 steotia 115.90 5521472: if slave errors master errors
* 06-Nov-06 steotia 115.91 5235393: null->0 for amounts/salary
* 17-Nov-06 maagrawa 115.92 Do not post zeros or nulls for salary
* components.
* 23-Nov-06 steotia 115.93 5659359: No more error stacking
* 3926221: Ineligs get no perf/promotion
* 3928529: process_access overhauled
* trunc used to get effective date
* 18-Jan-07 maagrawa 115.94 Log old and new salary when salary
* changed error is thrown.
* 04-Mar-07 steotia 115.95 5505775: CWB Enhancement
* Introducing Person Selection Rule
* 25-Apr-07 steotia 115.96 Closing LE of placeholder mgs also.
* 16-Jan-08 steotia 115.101 Compare rounded [proposal vs base sal]
* Rate Start Date enabled
* 18-Jan-08 steotia 115.102 Checking for ws_abr_id for above
* 18-Jan-08 steotia 115.103 Overriding modified
* 08-Apr-08 sgnanama 115.104 Added p_use_rate_start_date in submit_request
* 22-Apr-08 cakunuru 115.105 Changed the cursor c_placeholder_selection:
* will check for ineligible employees who are not managers.
* 7-May-08 sgnanama 115.106 selected business_group_id of the person in the
* c_person_selection and c_placeholder_selection and pass
* the same to the procedure evaluating the person seelction rule.
* 20-May-08 cakunuru 115.107 Changed the message in process_sal_comp_rates.
* 27-May-08 sgnanama 115.108 7126872:Added g_is_cwb_component_plan which is
* used by salary api to distinguish unapproved
* proposal from cwb
* 10-Jun-08 cakunuru 115.109 7155018: Added a condition for the cursor
* c_pils_for_access to check for approval_cd with 'AP'.
* 10-Jun-08 cakunuru 115.110 Changed the dbdrv checkfile comment.
* 18-Aug-08 cakunuru 115.111 6994188: Set the effective_date as null if error occurs.
* 05-Nov-08 cakunuru 115.112 7042887: Modified reason to get the meaning
* instead of reason code in the print_cache procedure.
* 13-Nov-08 sgnanama 115.113 7218121: Modified the check to assign the warning text
* to p_cache_cwb_rpt_person in process_sal_comp_rates
* --------------------------------------------------------------------------
*/
--
-- Global cursor and variables declaration
--
TYPE plan_override_date IS RECORD (
plan ben_cwb_pl_dsgn.pl_id%type,
date ben_cwb_pl_dsgn.ovrid_rt_strt_dt%type);
g_person_selected NUMBER (9) := 0;
SELECT o.oipl_id,
rpt.group_per_in_ler_id,
rpt.pl_id,
group_oipl.oipl_id group_oipl_id,
rpt.person_id
FROM ben_oipl_f o,
ben_cwb_rpt_detail rpt,
ben_oipl_f local_oipl,
ben_opt_f local_opt,
ben_opt_f group_opt,
ben_oipl_f group_oipl
WHERE o.pl_id = rpt.pl_id
AND rpt.benefit_action_id = v_benefit_action_id
AND rpt.oipl_id = -1
AND local_oipl.oipl_id = o.oipl_id
AND local_opt.opt_id = local_oipl.opt_id
AND group_opt.group_opt_id = local_opt.group_opt_id
AND group_oipl.opt_id = group_opt.group_opt_id
AND NOT EXISTS
(SELECT NULL
FROM ben_cwb_rpt_detail
WHERE oipl_id = o.oipl_id
AND benefit_action_id = v_benefit_action_id)
GROUP BY o.oipl_id,
rpt.group_per_in_ler_id,
rpt.pl_id,
group_oipl.oipl_id,
rpt.person_id;
SELECT ws_element_type_id, ws_input_value_id
FROM ben_cwb_pl_dsgn
WHERE lf_evt_ocrd_dt = v_lf_evt_ocrd_date
AND pl_id = v_pl_id
AND oipl_id = v_oipl_id;
SELECT ovrid_rt_strt_dt
FROM ben_cwb_pl_dsgn dsgn
WHERE group_pl_id = v_group_pl_id
AND pl_id = v_pl_id
AND group_oipl_id = v_group_oipl_id
AND oipl_id = v_oipl_id
AND lf_evt_ocrd_dt = v_lf_evt_ocrd_date;
SELECT object_version_number
FROM ben_per_in_ler pil
WHERE pil.per_in_ler_id = v_per_in_ler_id;
SELECT object_version_number
FROM ben_cwb_person_info info
WHERE info.group_per_in_ler_id = v_group_per_in_ler_id;
SELECT object_version_number
FROM ben_cwb_person_rates rt
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.pl_id = v_pl_id
AND rt.oipl_id = v_oipl_id;
SELECT object_version_number
,access_cd
,approval_cd
FROM ben_cwb_person_groups grp
WHERE grp.group_per_in_ler_id = v_group_per_in_ler_id
AND grp.group_pl_id = v_group_pl_id
AND grp.group_oipl_id = v_group_oipl_id;
SELECT bg.NAME
, per.full_name
, info.business_group_id
FROM per_business_groups_perf bg
, ben_cwb_person_info info
, per_all_people_f per
, ben_per_in_ler pil
WHERE info.group_per_in_ler_id = v_group_per_in_ler_id
AND bg.business_group_id = info.business_group_id
AND v_effective_date >= bg.date_from
AND ( bg.date_to IS NULL
OR bg.date_to >= v_effective_date)
AND info.group_per_in_ler_id = pil.per_in_ler_id
AND pil.ws_mgr_id = per.person_id(+)
AND v_effective_date BETWEEN per.effective_start_date(+) AND per.effective_end_date(+);
SELECT nvl(per.custom_name,per.full_name) full_name
, per.employee_number
, per.assignment_id
, per.business_group_id
, per.legislation_code
FROM ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
SELECT nvl(per.custom_name,per.full_name) full_name
, per.employee_number
, per.assignment_id
, per.business_group_id
, per.legislation_code
, per.job_id
, job.name job
, per.position_id
, pos.name position
, per.grade_id
, grades.name grade
, per.people_group_id
, ppl_groups.group_name
, per.ass_attribute1
, per.ass_attribute2
, per.ass_attribute3
, per.ass_attribute4
, per.ass_attribute5
, per.ass_attribute6
, per.ass_attribute7
, per.ass_attribute8
, per.ass_attribute9
, per.ass_attribute10
, per.ass_attribute11
, per.ass_attribute12
, per.ass_attribute13
, per.ass_attribute14
, per.ass_attribute15
, per.ass_attribute16
, per.ass_attribute17
, per.ass_attribute18
, per.ass_attribute19
, per.ass_attribute20
, per.ass_attribute21
, per.ass_attribute22
, per.ass_attribute23
, per.ass_attribute24
, per.ass_attribute25
, per.ass_attribute26
, per.ass_attribute27
, per.ass_attribute28
, per.ass_attribute29
, per.ass_attribute30
FROM ben_cwb_person_info per
, per_jobs_tl job
, hr_all_positions_f_tl pos
, per_grades_tl grades
, pay_people_groups ppl_groups
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
AND job.job_id(+) = per.job_id
AND job.language(+) = userenv('LANG')
AND pos.position_id(+) = per.position_id
AND pos.language(+) = userenv('LANG')
AND grades.grade_id(+) = per.grade_id
AND grades.language(+) = userenv('LANG')
AND ppl_groups.people_group_id(+) = per.people_group_id;
SELECT info.batch_proc_id
, info.object_version_number
FROM ben_batch_proc_info info
WHERE info.benefit_action_id = v_benefit_action_id;
SELECT COUNT (*) amount
FROM ben_cwb_rpt_detail
WHERE person_rate_id = -9999
AND status_cd = 'E'
AND benefit_action_id = v_benefit_action_id;
SELECT COUNT (*) amount
FROM ben_cwb_rpt_detail
WHERE person_rate_id = -9999
AND status_cd IN ('WC', 'SC', 'W')
AND benefit_action_id = v_benefit_action_id;
SELECT COUNT (*) amount
FROM ben_cwb_rpt_detail
WHERE person_rate_id = -9999
AND lf_evt_closed_flag = 'N'
AND benefit_action_id = v_benefit_action_id;
SELECT COUNT (*) amount
FROM ben_cwb_rpt_detail
WHERE person_rate_id = -9999
AND lf_evt_closed_flag = 'Y'
AND benefit_action_id = v_benefit_action_id;
CURSOR c_placeholder_selection (
v_pl_id IN NUMBER
, v_lf_evt_orcd_date IN DATE
, v_person_id IN NUMBER
, v_manager_id IN NUMBER
, v_business_group_id IN NUMBER
, v_effective_date IN DATE
)
IS
SELECT pil.person_id
, (pil.per_in_ler_id) per_in_ler_id
, (nvl(per.custom_name,per.full_name)) full_name
, bg.NAME
, per.legislation_code
, per.business_group_id
FROM
ben_per_in_ler pil,
ben_per_in_ler mgr_pil,
ben_cwb_person_info per,
ben_cwb_group_hrchy hrchy,
per_business_groups_perf bg
where pil.per_in_ler_stat_cd = 'STRTD'
AND pil.group_pl_id = per.group_pl_id
AND pil.lf_evt_ocrd_dt = per.lf_evt_ocrd_dt
AND per.group_per_in_ler_id = pil.per_in_ler_id
AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
AND hrchy.mgr_per_in_ler_id = mgr_pil.per_in_ler_id
AND ( v_person_id IS NULL
OR pil.person_id = v_person_id)
AND ( ( v_manager_id IS NULL
AND hrchy.lvl_num = (SELECT MAX (lvl_num)
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = hrchy.emp_per_in_ler_id)
)
OR ( mgr_pil.person_id = v_manager_id
AND hrchy.lvl_num > 0)
)
and not exists(
select null from
ben_cwb_person_rates
where group_per_in_ler_id = pil.per_in_ler_id
)
and (v_business_group_id is null or
per.business_group_id = v_business_group_id)
AND bg.business_group_id = per.business_group_id
AND v_effective_date >= bg.date_from
AND ( bg.date_to IS NULL
OR bg.date_to >= v_effective_date)
and per.group_pl_id = v_pl_id
and per.lf_evt_ocrd_dt = v_lf_evt_orcd_date
;
CURSOR c_person_selection (
v_pl_id IN NUMBER
, v_lf_evt_orcd_date IN DATE
, v_person_id IN NUMBER
, v_manager_id IN NUMBER
, v_business_group_id IN NUMBER
, v_effective_date IN DATE
)
IS
SELECT pil.person_id
, max(pil.per_in_ler_id) per_in_ler_id
, max(nvl(per.custom_name,per.full_name)) full_name
, max(bg.NAME) NAME
, per.business_group_id
FROM ben_per_in_ler pil
, ben_per_in_ler mgr_pil
, ben_cwb_group_hrchy hrchy
, per_business_groups_perf bg
, ben_cwb_person_info per
, ben_cwb_person_rates rates
, ben_cwb_pl_dsgn dsgn
WHERE pil.per_in_ler_stat_cd = 'STRTD'
AND pil.group_pl_id = v_pl_id
AND pil.lf_evt_ocrd_dt = v_lf_evt_orcd_date
AND per.group_per_in_ler_id = pil.per_in_ler_id
AND ( v_person_id IS NULL
OR pil.person_id = v_person_id)
AND ( v_business_group_id IS NULL
OR per.business_group_id = v_business_group_id)
AND per.business_group_id = bg.business_group_id
AND v_effective_date >= bg.date_from
AND ( bg.date_to IS NULL
OR bg.date_to >= v_effective_date)
AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
AND hrchy.mgr_per_in_ler_id = mgr_pil.per_in_ler_id
AND ( ( v_manager_id IS NULL
AND hrchy.lvl_num = (SELECT MAX (lvl_num)
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = hrchy.emp_per_in_ler_id)
)
OR ( mgr_pil.person_id = v_manager_id
AND hrchy.lvl_num > 0)
)
AND rates.group_per_in_ler_id = pil.per_in_ler_id
AND rates.pl_id = dsgn.pl_id
AND rates.oipl_id = dsgn.oipl_id
--AND rates.elig_flag = 'Y'
AND rates.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
AND dsgn.oipl_id=-1
AND nvl(dsgn.do_not_process_flag,'N') <> 'Y'
GROUP BY pil.person_id, per.business_group_id
ORDER BY full_name;
select null
from ben_cwb_person_rates
where group_per_in_ler_id = v_group_per_in_ler_id
and oipl_id = -1
and elig_flag = 'Y';
SELECT pil.per_in_ler_id
, pil.per_in_ler_stat_cd
, pil.object_version_number
FROM ben_per_in_ler pil
WHERE pil.group_pl_id = v_group_pl_id
AND pil.person_id = v_person_id
AND pil.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND pil.per_in_ler_stat_cd = 'STRTD'
AND ( v_employee_in_bg IS NULL
OR pil.business_group_id = v_employee_in_bg);
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 = v_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
, ben.non_person_cd
FROM ben_person_actions ben
WHERE ben.benefit_action_id = v_benefit_action_id
AND ben.action_status_cd <> 'P'
AND ben.person_action_id BETWEEN v_start_person_action_id AND v_end_person_action_id
ORDER BY ben.person_action_id;
SELECT ben.*
FROM ben_benefit_actions ben
WHERE ben.benefit_action_id = v_benefit_action_id;
SELECT actual_termination_date
FROM per_periods_of_service
WHERE person_id = v_person_id
AND date_start = (SELECT MAX (date_start)
FROM per_periods_of_service
WHERE person_id = v_person_id
GROUP BY person_id);
SELECT dsgn.perf_revw_strt_dt
, nvl(dsgn.ovr_perf_revw_strt_dt, dsgn.perf_revw_strt_dt)
, dsgn.asg_updt_eff_date
, dsgn.emp_interview_typ_cd
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.pl_id = v_pl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND dsgn.oipl_id = -1;
SELECT COUNT (*)
FROM ben_oipl_f oipl
, ben_opt_f opt
WHERE oipl.pl_id = v_pl_id
AND oipl.opt_id = opt.opt_id
and opt.component_reason is not null
AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date;
SELECT dsgn.ws_sub_acty_typ_cd
FROM ben_cwb_person_rates rt
, ben_cwb_pl_dsgn dsgn
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.pl_id = dsgn.pl_id
AND rt.oipl_id = dsgn.oipl_id
AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt;
SELECT decode(piv.uom,NULL,2,'M',nvl(curr.PRECISION,2),5) PRECISION
FROM per_all_assignments_f asg,
per_pay_bases ppb,
pay_input_values_f piv,
pay_element_types_f pet,
fnd_currencies curr
WHERE asg.assignment_id = v_assignment_id
AND v_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.pay_basis_id = ppb.pay_basis_id
AND ppb.input_value_id = piv.input_value_id
AND v_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND piv.element_type_id = pet.element_type_id
AND v_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.input_currency_code = curr.currency_code;
SELECT SUM (rt.ws_val)
FROM ben_cwb_person_rates rt
,ben_oipl_f oipl
,ben_opt_f opt
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.group_pl_id = v_group_pl_id
AND rt.oipl_id <> -1
AND rt.lf_evt_ocrd_dt = v_lf_evt_orcd_dt
AND rt.ws_val <> 0
AND oipl.oipl_id = rt.oipl_id
AND oipl.opt_id = opt.opt_id
AND opt.component_reason is not null
AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date
AND rt.elig_flag = 'Y';
SELECT DECODE(v_rule_based,'Y',
min(WS_RT_START_DATE),
min(OVRID_RT_STRT_DT)) effective_date
FROM ben_cwb_person_rates rt
, ben_oipl_f oipl
, ben_cwb_pl_dsgn dsgn
, ben_opt_f opt
, ben_cwb_person_info info
, ben_per_in_ler pil
, ben_cwb_xchg xchg
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.pl_id = dsgn.pl_id
AND rt.oipl_id = dsgn.oipl_id
AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
AND rt.group_per_in_ler_id = info.group_per_in_ler_id
AND rt.group_per_in_ler_id = pil.per_in_ler_id
AND oipl.oipl_id = rt.oipl_id
AND oipl.opt_id = opt.opt_id
AND opt.component_reason is not null
AND OVRID_RT_STRT_DT BETWEEN opt.effective_start_date AND opt.effective_end_date
AND OVRID_RT_STRT_DT BETWEEN oipl.effective_start_date AND oipl.effective_end_date
and xchg.group_pl_id = rt.group_pl_id
and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.currency = rt.currency
AND exists (select null from ben_cwb_pl_dsgn where pl_id = rt.pl_id and oipl_id = -1 and nvl(do_not_process_flag,'N') <> 'Y');
SELECT nvl(rt.ws_val,0) ws_val
, rt.person_rate_id
, opt.component_reason
, dsgn.salary_change_reason
, dsgn.pl_id
, dsgn.oipl_id
, dsgn.group_pl_id
, dsgn.group_oipl_id
, dsgn.ws_nnmntry_uom units
, dsgn.ws_sub_acty_typ_cd
, pil.ws_mgr_id
, info.full_name
, info.employee_number
, info.business_group_id
, rt.elig_sal_val
, xchg.xchg_rate
, rt.elig_flag
, rt.currency
FROM ben_cwb_person_rates rt
, ben_oipl_f oipl
, ben_cwb_pl_dsgn dsgn
, ben_opt_f opt
, ben_cwb_person_info info
, ben_per_in_ler pil
, ben_cwb_xchg xchg
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.group_pl_id = v_group_pl_id
--AND rt.oipl_id <> -1
--AND rt.elig_flag = 'Y' (for logging)
AND rt.lf_evt_ocrd_dt = v_lf_evt_orcd_dt
--AND nvl(rt.ws_val,0) <> 0
AND rt.pl_id = dsgn.pl_id
AND rt.oipl_id = dsgn.oipl_id
AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
AND rt.group_per_in_ler_id = info.group_per_in_ler_id
AND rt.group_per_in_ler_id = pil.per_in_ler_id
AND oipl.oipl_id = rt.oipl_id
AND oipl.opt_id = opt.opt_id
AND opt.component_reason is not null
AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date
and xchg.group_pl_id = rt.group_pl_id
and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.currency = rt.currency
AND exists (select null from ben_cwb_pl_dsgn where pl_id = rt.pl_id and oipl_id = -1 and nvl(do_not_process_flag,'N') <> 'Y');
SELECT rt.ws_val
, rt.person_rate_id
, rt.pl_id
, rt.oipl_id
, rt.object_version_number
, dsgn.ws_sub_acty_typ_cd
, dsgn.ws_abr_id
, dsgn.salary_change_reason
, dsgn.ws_nnmntry_uom units
, dsgn.acty_ref_perd_cd
, dsgn.business_group_id
, dsgn.group_pl_id
, dsgn.group_oipl_id
, pil.ws_mgr_id
, info.full_name
, info.employee_number
, info.assignment_id
, opt.component_reason
, info.base_salary_currency
, dsgn.uom_precision
, info.base_salary
, rt.elig_sal_val
, initcap(info.base_salary_frequency) base_salary_frequency
, info.pay_annulization_factor
, dsgn.pl_annulization_factor
, xchg.xchg_rate
, rt.elig_flag
, info.fte_factor
, rt.currency
FROM ben_cwb_person_rates rt
, ben_cwb_pl_dsgn dsgn
, ben_cwb_person_info info
, ben_per_in_ler pil
, ben_oipl_f oipl
, ben_opt_f opt
, ben_cwb_xchg xchg
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.pl_id = dsgn.pl_id
AND rt.oipl_id = dsgn.oipl_id
--AND rt.elig_flag = 'Y' (for logging)
AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
AND rt.group_per_in_ler_id = info.group_per_in_ler_id
AND rt.group_per_in_ler_id = pil.per_in_ler_id
AND rt.oipl_id = oipl.oipl_id (+)
AND oipl.opt_id = opt.opt_id (+)
AND v_effective_date BETWEEN opt.effective_start_date (+) AND opt.effective_end_date (+)
AND v_effective_date BETWEEN oipl.effective_start_date (+)AND oipl.effective_end_date (+)
and xchg.group_pl_id = rt.group_pl_id
and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.currency = rt.currency
AND exists (select null from ben_cwb_pl_dsgn where pl_id = rt.pl_id and oipl_id = -1 and nvl(do_not_process_flag,'N') <> 'Y');
SELECT xtra_info.aei_information1
, xtra_info.aei_information2
, xtra_info.aei_information4
, xtra_info.assignment_id
, xtra_info.object_version_number
, xtra_info.assignment_extra_info_id
FROM per_assignment_extra_info xtra_info
, ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
AND xtra_info.assignment_id = per.assignment_id
AND xtra_info.information_type = 'CWBRANK'
AND xtra_info.aei_information1 IS NOT NULL
AND xtra_info.aei_information3 IS NULL;
SELECT xtra_info.aei_information1
, xtra_info.aei_information2
, xtra_info.aei_information4
, xtra_info.assignment_id
, xtra_info.object_version_number
FROM per_assignment_extra_info xtra_info
, ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
AND xtra_info.assignment_id = per.assignment_id
AND xtra_info.information_type = 'CWBRANK'
AND xtra_info.aei_information5 = fnd_date.date_to_canonical(v_eff_dt)
AND xtra_info.aei_information2 = v_ranked_by;
SELECT asg.assignment_id
, asg.pay_basis_id
, ppp.proposed_salary_n
, ppp.object_version_number
FROM per_all_assignments_f asg
, per_pay_bases ppb
, per_pay_proposals ppp
, ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
AND asg.assignment_id = per.assignment_id
AND v_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND ppb.pay_basis_id = asg.pay_basis_id
AND ppp.assignment_id = asg.assignment_id
AND ppp.approved = 'Y'
AND ppp.change_date =
(SELECT MAX (ppp1.change_date)
FROM per_pay_proposals ppp1
WHERE ppp1.assignment_id = asg.assignment_id
AND ppp1.approved = 'Y'
AND change_date < v_effective_date);
SELECT ppp.proposed_salary_n
FROM per_pay_proposals ppp
, ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
AND ppp.assignment_id = per.assignment_id
AND ppp.change_date > v_effective_date;
SELECT ele.element_entry_id
,ele.element_type_id
FROM per_pay_bases bas
, pay_element_entries_f ele
, pay_element_entry_values_f entval
WHERE bas.pay_basis_id = v_pay_basis_id
AND entval.input_value_id = bas.input_value_id
AND v_effective_date BETWEEN entval.effective_start_date AND entval.effective_end_date
AND ele.assignment_id = v_assignmnet_id
AND v_effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date
AND ele.element_entry_id = entval.element_entry_id;
SELECT SUM (comp.change_amount_n) tamt
FROM per_pay_proposal_components comp
WHERE comp.pay_proposal_id = v_pay_proposal_id;
SELECT per.business_group_id
, per.base_salary
, per.base_salary_currency
, initcap(base_salary_frequency) base_salary_frequency
, pay_annulization_factor
, fte_factor
FROM ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
SELECT dsgn.NAME
, dsgn.group_pl_id
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.pl_id = v_group_pl_id
AND dsgn.pl_id = dsgn.group_pl_id
AND dsgn.group_oipl_id = -1
AND dsgn.oipl_id = dsgn.group_oipl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt;
SELECT dsgn.NAME
, dsgn.group_oipl_id
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.pl_id = v_group_pl_id
AND dsgn.pl_id = dsgn.group_pl_id
AND dsgn.oipl_id = dsgn.group_oipl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND dsgn.oipl_id <> -1;
SELECT dsgn.NAME
, dsgn.pl_id
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.group_pl_id = v_group_pl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND dsgn.oipl_id = -1
AND dsgn.pl_id <> dsgn.group_pl_id;
SELECT dsgn.NAME
, dsgn.oipl_id
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.group_pl_id = v_group_pl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND dsgn.pl_id <> dsgn.group_pl_id
AND dsgn.oipl_id <> -1;
SELECT COUNT (*) amount
, action_status_cd
FROM ben_person_actions act
WHERE act.benefit_action_id = v_benefit_action_id
AND act.action_status_cd IN ('P', 'E', 'U')
GROUP BY action_status_cd;
SELECT pil.per_in_ler_id
,nvl(info.custom_name,info.full_name) full_name
FROM ben_per_in_ler pil
,ben_cwb_person_groups pgroup
,ben_cwb_person_info info
WHERE pil.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND pil.group_pl_id = v_group_pl_id
AND pgroup.group_per_in_ler_id = pil.per_in_ler_id
AND pgroup.group_oipl_id = -1
AND ( nvl(pgroup.access_cd,'UP') = 'UP' OR approval_cd = 'AP' )
AND info.group_per_in_ler_id = pil.per_in_ler_id
AND NOT EXISTS (
SELECT NULL
FROM ben_cwb_group_hrchy h, ben_per_in_ler p
,ben_cwb_person_rates r
WHERE h.mgr_per_in_ler_id = pil.per_in_ler_id
AND h.lvl_num > 0
AND p.per_in_ler_id = h.emp_per_in_ler_id
AND p.lf_evt_ocrd_dt = pil.lf_evt_ocrd_dt
AND p.group_pl_id = pil.group_pl_id
AND p.per_in_ler_stat_cd = 'STRTD'
AND r.group_per_in_ler_id = h.emp_per_in_ler_id
AND r.oipl_id = -1
AND r.elig_flag = 'Y'
)
AND NOT EXISTS (
SELECT NULL
FROM ben_per_in_ler p
WHERE p.per_in_ler_id = pil.per_in_ler_id
AND p.lf_evt_ocrd_dt = pil.lf_evt_ocrd_dt
AND p.group_pl_id = pil.group_pl_id
AND p.per_in_ler_stat_cd = 'STRTD'
AND NOT EXISTS (
SELECT NULL
FROM ben_cwb_group_hrchy h
WHERE h.mgr_per_in_ler_id = p.per_in_ler_id
)
);
SELECT emp_per_in_ler_id
FROM ben_per_in_ler pil
,ben_cwb_group_hrchy hrchy
WHERE hrchy.mgr_per_in_ler_id = v_mgr_per_in_ler_id
AND hrchy.lvl_num > 0
AND pil.per_in_ler_id = hrchy.emp_per_in_ler_id
AND pil.per_in_ler_stat_cd = 'STRTD'
AND rownum = 1;
SELECT dsgn.pl_annulization_factor,
dsgn.uom_precision,
info.pay_annulization_factor,
dsgn.salary_change_reason
FROM ben_cwb_pl_dsgn dsgn,
ben_cwb_person_info info
WHERE dsgn.group_pl_id = v_group_pl_id
AND dsgn.group_pl_id = dsgn.pl_id
AND dsgn.oipl_id = -1
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND info.group_per_in_ler_id = v_group_per_in_ler_id;
SELECT input_currency_code
FROM pay_element_types_f
WHERE element_type_id = v_element_type_id
AND v_effective_date BETWEEN effective_start_date AND effective_end_date;
select pet.element_name||': '||piv.name
, processing_type
, input_currency_code
from pay_input_values_f piv,
pay_element_types_f pet
where piv.input_value_id = v_input_value_id
and piv.element_type_id = v_element_type_id
and piv.element_type_id = pet.element_type_id
and v_effective_date between piv.effective_start_date and piv.effective_end_date
and v_effective_date between pet.effective_start_date and pet.effective_end_date;
select eev.screen_entry_value
from pay_element_entries_f ee,
pay_element_entry_values_f eev
where ee.assignment_id = v_assignment_id
and ee.element_type_id = v_element_type_id
and v_effective_date between ee.effective_start_date and ee.effective_end_date
and eev.element_entry_id = ee.element_entry_id
and eev.input_value_id = v_input_value_id
and eev.effective_start_date = ee.effective_start_date
and eev.effective_end_date = ee.effective_end_date
and eev.screen_entry_value is not null
order by ee.effective_start_date;
select proposed_salary_n
from per_pay_proposals
where pay_proposal_id = v_pay_proposal_id;
select perf.performance_rating
from per_performance_reviews perf
where perf.person_id = v_person_id
and perf.review_date = v_effective_date;
select asgn.job_id
, job.name job
, asgn.position_id
, pos.name position
, asgn.grade_id
, grade.name grade
, asgn.people_group_id
, people_group.group_name
, asgn.ass_attribute1
, asgn.ass_attribute2
, asgn.ass_attribute3
, asgn.ass_attribute4
, asgn.ass_attribute5
, asgn.ass_attribute6
, asgn.ass_attribute7
, asgn.ass_attribute8
, asgn.ass_attribute9
, asgn.ass_attribute10
, asgn.ass_attribute11
, asgn.ass_attribute12
, asgn.ass_attribute13
, asgn.ass_attribute14
, asgn.ass_attribute15
, asgn.ass_attribute16
, asgn.ass_attribute17
, asgn.ass_attribute18
, asgn.ass_attribute19
, asgn.ass_attribute20
, asgn.ass_attribute21
, asgn.ass_attribute22
, asgn.ass_attribute23
, asgn.ass_attribute24
, asgn.ass_attribute25
, asgn.ass_attribute26
, asgn.ass_attribute27
, asgn.ass_attribute28
, asgn.ass_attribute29
, asgn.ass_attribute30
from per_all_assignments_f asgn
, per_jobs_tl job
, hr_all_positions_f_tl pos
, per_grades_tl grade
, pay_people_groups people_group
where assignment_id = v_assignment_id
and v_effective_date between effective_start_date and effective_end_date
and job.job_id(+) = asgn.job_id
and job.language(+) = userenv('LANG')
and pos.position_id(+) = asgn.position_id
and pos.language(+) = userenv('LANG')
and grade.grade_id(+) = asgn.grade_id
and grade.language(+) = userenv('LANG')
and people_group.people_group_id(+) = asgn.people_group_id;
select job.name job
, pos.name position
, grade.name grade
, people_group.group_name
from ben_transaction asgn
, per_jobs_tl job
, hr_all_positions_f_tl pos
, per_grades_tl grade
, pay_people_groups people_group
where asgn.transaction_id = v_transaction_id
and asgn.transaction_type = v_transaction_type
and job.job_id(+) = asgn.attribute5
and job.language(+) = userenv('LANG')
and pos.position_id(+) = asgn.attribute6
and pos.language(+) = userenv('LANG')
and grade.grade_id(+) = asgn.attribute7
and grade.language(+) = userenv('LANG')
and people_group.people_group_id(+) = asgn.attribute8;
SELECT trans.transaction_id,
trans.attribute1,
trans.attribute2
FROM ben_transaction trans,
ben_cwb_pl_dsgn dsgn
WHERE trans.transaction_id IN
(SELECT DISTINCT pl_id
FROM ben_cwb_person_rates rates
WHERE group_per_in_ler_id = v_group_per_in_ler_id)
AND trans.transaction_type = 'CWBPPOVDT' || to_char(v_lf_evt_ocrd_dt,'yyyy/mm/dd');
Select null
From fnd_concurrent_requests fnd
Where request_id = v_request_id
and status_code = 'E';
SELECT WS_RT_START_DATE
FROM BEN_CWB_PERSON_RATES
WHERE GROUP_PER_IN_LER_ID = v_group_per_in_ler_id
AND PL_ID = v_pl_id
AND OIPL_ID = v_oipl_id
AND LF_EVT_OCRD_DT = v_lf_evt_ocrd_dt
AND GROUP_PL_ID = v_group_pl_id
AND GROUP_OIPL_ID = v_group_oipl_id
;
write_h('All Employees are processed for this manager and eligible for status update');
write_h('Some Employees are not processed for this manager and not eligible for status update');
write_m('Access and approval cd update for '||l_pils_for_access.full_name);
ben_cwb_person_groups_api.update_group_budget
(p_group_per_in_ler_id => l_pils_for_access.per_in_ler_id
, p_group_pl_id => p_group_pl_id
, p_group_oipl_id => -1
, p_access_cd => 'RO'
, p_approval_date => sysdate
, p_approval_cd => 'PR'
, p_object_version_number => l_grp_ovn.object_version_number
);
write_m('Access and approval cd not update for '||l_pils_for_access.full_name || ' as status is not UP');
, p_person_selected IN NUMBER
, p_business_group_id IN NUMBER DEFAULT NULL
)
IS
l_actions c_actions%ROWTYPE;
g_exec_param_rec.persons_selected :=
g_exec_param_rec.persons_errored + g_exec_param_rec.persons_proc_succ;
, p_per_slctd => g_exec_param_rec.persons_selected
, p_per_proc => g_exec_param_rec.lf_evt_closed
, p_per_unproc => g_exec_param_rec.lf_evt_not_closed
, p_per_proc_succ => g_exec_param_rec.persons_proc_succ
, p_per_err => g_exec_param_rec.persons_errored
, p_business_group_id => p_business_group_id
, p_object_version_number => l_object_version_number
);
PROCEDURE insert_person_actions (
p_per_actn_id_array IN g_number_type
, p_per_id IN g_number_type
, p_group_per_in_ler_id IN g_number_type
, p_benefit_action_id IN NUMBER
, p_is_placeholder IN g_number_type
)
IS
l_num_rows NUMBER := p_per_actn_id_array.COUNT;
g_proc := 'insert_person_actions';
write('Time before inserting person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO ben_person_actions
(person_action_id
, person_id
, ler_id
, benefit_action_id
, action_status_cd
, object_version_number
, NON_PERSON_CD
)
VALUES (p_per_actn_id_array (l_count)
, p_per_id (l_count)
, p_group_per_in_ler_id (l_count)
, p_benefit_action_id
, 'U'
, 1
, decode(p_is_placeholder (l_count),1,'Y','N')
);
write_m ('Time before inserting ben batch ranges '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO ben_batch_ranges
(range_id
, benefit_action_id
, range_status_cd
, starting_person_action_id
, ending_person_action_id
, object_version_number
)
VALUES (ben_batch_ranges_s.NEXTVAL
, p_benefit_action_id
, 'U'
, p_per_actn_id_array (1)
, p_per_actn_id_array (l_num_rows)
, 1
);
write_m ('Time at end of insert person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO ben_cwb_rpt_detail
(benefit_action_id
, person_rate_id
, pl_id
, person_id
, country_code
, group_per_in_ler_id
, oipl_id
, group_pl_id
, group_oipl_id
, ws_mgr_id
, lf_evt_ocrd_dt
, full_name
, employee_number
, business_group_id
, business_group_name
, manager_name
, pl_name
, opt_name
, amount
, units
, performance_rating
, assignment_changed_flag
, status_cd
, lf_evt_closed_flag
, error_or_warning_text
, cwb_rpt_detail_id
, base_salary_currency
, currency
, base_salary
, elig_salary
, percent_of_elig_sal
, base_sal_freq
, pay_ann_factor
, pl_ann_factor
, conversion_factor
, adjusted_amount
, prev_sal
, new_sal
, pay_proposal_id
, pay_basis_id
, element_entry_id
, exchange_rate
, effective_date
, reason
, eligibility
, fte_factor
, element_input_value
, amount_posted
, assignment_id
, element_entry_value_id
, input_value_id
, element_type_id
, eev_screen_entry_value
, elmnt_processing_type
, uom_precision
, ws_sub_acty_typ_cd
, posted_rating
, rating_type
, rating_date
, prior_job
, posted_job
, proposed_job
, prior_position
, posted_position
, proposed_position
, prior_grade
, posted_grade
, proposed_grade
, prior_group
, posted_group
, proposed_group
, prior_flex1
, posted_flex1
, proposed_flex1
, prior_flex2
, posted_flex2
, proposed_flex2
, prior_flex3
, posted_flex3
, proposed_flex3
, prior_flex4
, posted_flex4
, proposed_flex4
, prior_flex5
, posted_flex5
, proposed_flex5
, prior_flex6
, posted_flex6
, proposed_flex6
, prior_flex7
, posted_flex7
, proposed_flex7
, prior_flex8
, posted_flex8
, proposed_flex8
, prior_flex9
, posted_flex9
, proposed_flex9
, prior_flex10
, posted_flex10
, proposed_flex10
, prior_flex11
, posted_flex11
, proposed_flex11
, prior_flex12
, posted_flex12
, proposed_flex12
, prior_flex13
, posted_flex13
, proposed_flex13
, prior_flex14
, posted_flex14
, proposed_flex14
, prior_flex15
, posted_flex15
, proposed_flex15
, prior_flex16
, posted_flex16
, proposed_flex16
, prior_flex17
, posted_flex17
, proposed_flex17
, prior_flex18
, posted_flex18
, proposed_flex18
, prior_flex19
, posted_flex19
, proposed_flex19
, prior_flex20
, posted_flex20
, proposed_flex20
, prior_flex21
, posted_flex21
, proposed_flex21
, prior_flex22
, posted_flex22
, proposed_flex22
, prior_flex23
, posted_flex23
, proposed_flex23
, prior_flex24
, posted_flex24
, proposed_flex24
, prior_flex25
, posted_flex25
, proposed_flex25
, prior_flex26
, posted_flex26
, proposed_flex26
, prior_flex27
, posted_flex27
, proposed_flex27
, prior_flex28
, posted_flex28
, proposed_flex28
, prior_flex29
, posted_flex29
, proposed_flex29
, prior_flex30
, posted_flex30
, proposed_flex30
, asgn_change_reason
, pending_workflow
, new_rpt
, prev_eev_screen_entry_value)
VALUES (benutils.g_benefit_action_id
, g_cache_cwb_rpt_person (i).person_rate_id
, g_cache_cwb_rpt_person (i).pl_id
, g_cache_cwb_rpt_person (i).person_id
, g_cache_cwb_rpt_person (i).country_code
, g_cache_cwb_rpt_person (i).group_per_in_ler_id
, g_cache_cwb_rpt_person (i).oipl_id
, g_cache_cwb_rpt_person (i).group_pl_id
, g_cache_cwb_rpt_person (i).group_oipl_id
, g_cache_cwb_rpt_person (i).ws_mgr_id
, g_cache_cwb_rpt_person (i).lf_evt_ocrd_date
, g_cache_cwb_rpt_person (i).full_name
, g_cache_cwb_rpt_person (i).emp_number
, g_cache_cwb_rpt_person (i).business_group_id
, g_cache_cwb_rpt_person (i).business_group_name
, g_cache_cwb_rpt_person (i).manager_name
, g_cache_cwb_rpt_person (i).pl_name
, g_cache_cwb_rpt_person (i).opt_name
, g_cache_cwb_rpt_person (i).amount
, g_cache_cwb_rpt_person (i).units
, g_cache_cwb_rpt_person (i).performance_rating
, g_cache_cwb_rpt_person (i).assignment_changed
, g_cache_cwb_rpt_person (i).status
, g_cache_cwb_rpt_person (i).lf_evt_closed
, l_message_text
, ben_cwb_rpt_detail_s.NEXTVAL
, g_cache_cwb_rpt_person (i).base_salary_currency
, g_cache_cwb_rpt_person (i).currency
, round(g_cache_cwb_rpt_person (i).base_salary*g_cache_cwb_rpt_person (i).pay_ann_factor/g_cache_cwb_rpt_person (i).pl_ann_factor,
nvl(g_cache_cwb_rpt_person (i).uom_precision,2))
, g_cache_cwb_rpt_person (i).elig_salary
, g_cache_cwb_rpt_person (i).percent_of_elig_sal
, g_cache_cwb_rpt_person (i).base_sal_freq
, g_cache_cwb_rpt_person (i).pay_ann_factor
, g_cache_cwb_rpt_person (i).pl_ann_factor
, g_cache_cwb_rpt_person (i).conversion_factor
, g_cache_cwb_rpt_person (i).adjusted_amount
, g_cache_cwb_rpt_person (i).prev_sal
, g_cache_cwb_rpt_person (i).new_sal
, g_cache_cwb_rpt_person (i).pay_proposal_id
, g_cache_cwb_rpt_person (i).pay_basis_id
, g_cache_cwb_rpt_person (i).element_entry_id
, g_cache_cwb_rpt_person (i).exchange_rate
, g_cache_cwb_rpt_person (i).effective_date
, hr_general.decode_lookup('PROPOSAL_REASON',g_cache_cwb_rpt_person (i).reason) -- bug : 7042887
, g_cache_cwb_rpt_person (i).eligibility
, g_cache_cwb_rpt_person (i).fte_factor
, g_cache_cwb_rpt_person (i).element_input_value
, nvl(g_cache_cwb_rpt_person (i).amount_posted,0)
, g_cache_cwb_rpt_person (i).assignment_id
, g_cache_cwb_rpt_person (i).element_entry_value_id
, g_cache_cwb_rpt_person (i).input_value_id
, g_cache_cwb_rpt_person (i).element_type_id
, g_cache_cwb_rpt_person (i).eev_screen_entry_value
, g_cache_cwb_rpt_person (i).elmnt_processing_type
, g_cache_cwb_rpt_person (i).uom_precision
, g_cache_cwb_rpt_person (i).ws_sub_acty_typ_cd
, substr(g_cache_cwb_rpt_person (i).posted_rating,1,30)
, substr(g_cache_cwb_rpt_person (i).rating_type,1,30)
, substr(g_cache_cwb_rpt_person (i).rating_date,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_job,1,700) --sg
, substr(g_cache_cwb_rpt_person (i).posted_job,1,700) --sg
, substr(g_cache_cwb_rpt_person (i).proposed_job,1,700) --sg
, substr(g_cache_cwb_rpt_person (i).prior_position,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).posted_position,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).proposed_position,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).prior_grade,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).posted_grade,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).proposed_grade,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).prior_group,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).posted_group,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).proposed_group,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).prior_flex1,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex1,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex1,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex2,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex2,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex2,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex3,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex3,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex3,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex4,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex4,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex4,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex5,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex5,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex5,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex6,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex6,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex6,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex7,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex7,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex7,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex8,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex8,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex8,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex9,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex9,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex9,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex10,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex10,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex10,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex11,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex11,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex11,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex12,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex12,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex12,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex13,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex13,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex13,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex14,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex14,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex14,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex15,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex15,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex15,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex16,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex16,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex16,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex17,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex17,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex17,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex18,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex18,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex18,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex19,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex19,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex19,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex20,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex20,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex20,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex21,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex21,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex21,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex22,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex22,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex22,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex23,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex23,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex23,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex24,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex24,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex24,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex25,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex25,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex25,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex26,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex26,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex26,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex27,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex27,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex27,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex28,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex28,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex28,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex29,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex29,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex29,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex30,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex30,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex30,1,30)
, substr(g_cache_cwb_rpt_person (i).asgn_change_reason,1,30)
, g_cache_cwb_rpt_person (i).pending_workflow
, 'Y'
, g_cache_cwb_rpt_person (i).prev_eev_screen_entry_value);
INSERT INTO ben_cwb_rpt_detail
(benefit_action_id
, person_rate_id
, person_id
, country_code
, business_group_id
, business_group_name
, status_cd
, lf_evt_closed_flag
, cwb_rpt_detail_id
)
VALUES (benutils.g_benefit_action_id
, -9999
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).person_id
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).country_code
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_id
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_name
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).lf_evt_closed
, ben_cwb_rpt_detail_s.NEXTVAL
);
INSERT INTO ben_cwb_rpt_detail (
benefit_action_id,
person_id,
pl_id,
oipl_id,
group_per_in_ler_id,
group_oipl_id,
cwb_rpt_detail_id
)
VALUES (
p_benefit_action_id,
l_table_correction_rec.person_id,
l_table_correction_rec.pl_id,
l_table_correction_rec.oipl_id,
l_table_correction_rec.group_per_in_ler_id,
l_table_correction_rec.group_oipl_id,
ben_cwb_rpt_detail_s.NEXTVAL
);
write_h ('selected per_in_ler_id ' || pil_rec.per_in_ler_id || ' for closing');
ben_person_life_event_api.update_person_life_event
(p_per_in_ler_id => pil_rec.per_in_ler_id
, p_per_in_ler_stat_cd => 'PROCD'
, p_procd_dt => l_procd_dt
, p_voidd_dt => l_voidd_dt
, p_strtd_dt => l_strtd_dt
, p_object_version_number => pil_rec.object_version_number
, p_effective_date => p_effective_date
);
write_h ('selected the group_per_in_ler_id ' || p_group_per_in_ler_id || ' for closing');
ben_person_life_event_api.update_person_life_event
(p_per_in_ler_id => p_group_per_in_ler_id
, p_per_in_ler_stat_cd => 'PROCD'
, p_procd_dt => l_procd_dt
, p_voidd_dt => l_voidd_dt
, p_strtd_dt => l_strtd_dt
, p_object_version_number => l_pil_ovn.object_version_number
, p_effective_date => p_effective_date
);
ben_cwb_person_info_api.update_person_info
(p_group_per_in_ler_id => p_group_per_in_ler_id
, p_post_process_stat_cd => 'PR'
, p_object_version_number => l_info_ovn.object_version_number
);
ben_cwb_audit_api.update_per_record(p_per_in_ler_id => p_group_per_in_ler_id);
'This employee had a recent update to Base Salary '
|| 'or Pay Basis. The new salary could not be posted.'
|| ' Either delete the recent update and rerun this process,'
|| 'or apply the new salary manually. ';
WRITE ('Inserting salary proposal...');
write_m ('Time before inserting the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
hr_maintain_proposal_api.insert_salary_proposal
(p_pay_proposal_id => l_pay_proposal_id
, p_assignment_id => asg_rec.assignment_id
, p_business_group_id => per_bg_rec.business_group_id
, p_change_date => l_effective_date
, p_object_version_number => l_object_version_number
, p_multiple_components => 'Y'
, p_approved => 'N'
, p_validate => FALSE
, p_element_entry_id => l_ele_ent_id
, p_inv_next_sal_date_warning => l_dummy1
, p_proposed_salary_warning => l_dummy2
, p_approved_warning => l_dummy3
, p_payroll_warning => l_dummy4
);
WRITE('Error in insert_salary_proposal : '||SQLERRM);
write_m ('Time after inserting the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
WRITE ('Inserting salary proposal component...');
write_h ('==============Inserting Salary component ========');
write_m ('Time before inserting the salary proposal components '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
hr_maintain_proposal_api.insert_proposal_component
(p_component_id => l_component_id
, p_pay_proposal_id => l_pay_proposal_id
, p_business_group_id => per_bg_rec.business_group_id
, p_approved => 'Y'
, p_component_reason => sal_rate_rec.component_reason
, p_change_amount_n => l_sal_incr
, p_object_version_number => l_component_ovn
);
WRITE('Error in insert_proposal_component : '||SQLERRM);
write_m('Time after inserting the salary proposal components '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
ben_cwb_person_rates_api.update_person_rate
(p_group_per_in_ler_id => p_group_per_in_ler_id
, p_pl_id => sal_rate_rec.pl_id
, p_oipl_id => sal_rate_rec.oipl_id
, p_pay_proposal_id => l_pay_proposal_id
, p_comp_posting_date => l_effective_date
, p_object_version_number => l_rate_ovn.object_version_number
);
hr_maintain_proposal_api.update_salary_proposal
(p_pay_proposal_id => l_pay_proposal_id
, p_object_version_number => l_object_version_number
, p_proposed_salary_n => (l_prev_sal
+ tot_com_amt_rec.tamt)
, p_approved => 'Y'
, p_inv_next_sal_date_warning => l_dummy1
, p_proposal_reason => l_sal_factors.salary_change_reason
, p_proposed_salary_warning => l_dummy2
, p_approved_warning => l_dummy3
, p_payroll_warning => l_dummy4
);
WRITE('Error in update_salary_proposal : '||SQLERRM);
'This employee had a recent update to Base Salary '
|| 'or Pay Basis. The new salary could not be posted.'
|| ' Either delete the recent update and rerun this process,'
|| 'or apply the new salary manually. ';
g_actn := 'Inserting salary proposal for non_comp_salary';
write_h ('==============Inserting Salary proposal========');
write_m ('Time before inserting the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
hr_maintain_proposal_api.insert_salary_proposal
(p_pay_proposal_id => l_pay_proposal_id
, p_assignment_id => asg_rec.assignment_id
, p_business_group_id => p_business_group_id
, p_change_date => p_effective_date
, p_proposal_reason => p_salary_change_reason
, p_proposed_salary_n => (l_prev_sal
+ p_ws_val
--l_sal_incr --p_ws_val
)
, p_object_version_number => l_object_version_number
, p_multiple_components => 'N'
, p_approved => 'Y'
, p_validate => FALSE
, p_element_entry_id => l_ele_ent_id
, p_inv_next_sal_date_warning => l_dummy1
, p_proposed_salary_warning => l_dummy2
, p_approved_warning => l_dummy3
, p_payroll_warning => l_dummy4
);
WRITE('Exception at insert_salary_proposal : '||SQLERRM);
write_m ('Time after inserting the salary proposal '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
ben_cwb_person_rates_api.update_person_rate
(p_group_per_in_ler_id => p_group_per_in_ler_id
, p_pl_id => p_pl_id
, p_oipl_id => p_oipl_id
--, p_element_entry_value_id => l_ele_ent_id
, p_pay_proposal_id => l_pay_proposal_id
, p_object_version_number => l_rate_ovn.object_version_number
);
ben_cwb_person_rates_api.update_person_rate
(p_group_per_in_ler_id => p_group_per_in_ler_id
, p_pl_id => p_pl_id
, p_oipl_id => p_oipl_id
, p_element_entry_value_id => p_element_entry_value_id
, p_object_version_number => l_rate_ovn.object_version_number
);
WRITE('Exception at update_person_rate : '||SQLERRM);
ben_cwb_person_rates_api.update_person_rate
(p_group_per_in_ler_id => p_group_per_in_ler_id
, p_pl_id => rt_rec.pl_id
, p_oipl_id => rt_rec.oipl_id
, p_comp_posting_date => l_effective_date
, p_object_version_number => l_rate_ovn.object_version_number);
l_perf_txn ben_cwb_asg_update.g_txn%ROWTYPE;
l_asg_txn ben_cwb_asg_update.g_txn%ROWTYPE;
OPEN ben_cwb_asg_update.g_txn (l_emp_num_and_emp_name.assignment_id,
ben_cwb_asg_update.g_ws_perf_rec_type||to_char(l_perf_revw_strt_dt, 'yyyy/mm/dd')
||l_interview_typ_cd);
FETCH ben_cwb_asg_update.g_txn INTO l_perf_txn;
CLOSE ben_cwb_asg_update.g_txn;
OPEN ben_cwb_asg_update.g_txn (l_emp_num_and_emp_name.assignment_id,
ben_cwb_asg_update.g_ws_asg_rec_type||to_char(l_asg_updt_eff_date, 'yyyy/mm/dd'));
FETCH ben_cwb_asg_update.g_txn INTO l_asg_txn;
CLOSE ben_cwb_asg_update.g_txn;
ben_cwb_asg_update.process_rating (p_person_id => p_person_id
, p_txn_rec => l_perf_txn
, p_business_group_id => l_emp_num_and_emp_name.business_group_id
, p_audit_log => p_audit_log
, p_process_status => l_rating_status
, p_group_per_in_ler_id => p_group_per_in_ler_id
, p_effective_date => l_perf_revw_strt_dt
);
ben_cwb_asg_update.g_ws_asg_rec_type
||l_asg_txn.attribute1);
WRITE(ben_cwb_asg_update.g_ws_asg_rec_type||l_asg_txn.attribute1);
ben_cwb_asg_update.process_promotions (p_person_id => p_person_id
, p_asg_txn_rec => l_asg_txn
, p_business_group_id => l_emp_num_and_emp_name.business_group_id
, p_audit_log => p_audit_log
, p_process_status => l_promotion_status
, p_group_per_in_ler_id => p_group_per_in_ler_id
, p_effective_date => l_asg_updt_eff_date
);
write ('Found a rank which needs to be updated...');
WRITE ('Found a rank which need not be updated...');
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
, p_action_status_cd => 'P'
, p_object_version_number => p_object_version_number
, p_effective_date => p_effective_date
);
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
, p_action_status_cd => 'E'
, p_object_version_number => p_object_version_number
, p_effective_date => p_effective_date
);
write_h (' and Ending Person Action id ' || l_end_person_action_id || ' is selected');
UPDATE ben_batch_ranges ran
SET ran.range_status_cd = 'P'
WHERE ran.range_id = l_range_id;
g_cache_person_process.DELETE;
WRITE ('Number of Persons selected in this range ' || g_cache_person_process.COUNT);
, p_person_selection_rule_id IN NUMBER DEFAULT NULL
, p_use_rate_start_date IN VARCHAR2 DEFAULT 'N'
)
IS
--
-- local variable declaration.
--
l_effective_date DATE;
ps_rec c_person_selection%ROWTYPE;
pl_rec c_placeholder_selection%ROWTYPE;
g_exec_param_rec.persons_selected := 0;
, p_program_update_date => SYSDATE
, p_bft_attribute1 => l_process_compents
, p_bft_attribute3 => p_employees_in_bg
, p_bft_attribute4 => p_manager_id
);
g_actn := 'Inserting Person Actions...';
write_m ('Time before processing the person selections '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
OPEN c_placeholder_selection (p_pl_id
, l_lf_evt_orcd_date
, p_person_id
, p_manager_id
, p_employees_in_bg
, l_effective_date
);
FETCH c_placeholder_selection
INTO pl_rec;
EXIT WHEN c_placeholder_selection%NOTFOUND;
If p_person_selection_rule_id is not NULL then
--
begin
ben_batch_utils.person_selection_rule
(p_person_id => l_person_id
,p_business_group_id => pl_rec.business_group_id
,p_person_selection_rule_id=> p_person_selection_rule_id
,p_effective_date => l_effective_date
,p_return => l_person_ok
,p_err_message => l_err_message );
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
insert_person_actions (p_per_actn_id_array => l_person_action_ids
, p_per_id => l_person_ids
, p_group_per_in_ler_id => l_per_in_ler_ids
, p_benefit_action_id => l_benefit_action_id
, p_is_placeholder => l_is_placeholder
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_per_in_ler_ids.DELETE;
l_is_placeholder.DELETE;
CLOSE c_placeholder_selection;
OPEN c_person_selection (p_pl_id
, l_lf_evt_orcd_date
, p_person_id
, p_manager_id
, p_employees_in_bg
, l_effective_date
);
FETCH c_person_selection
INTO ps_rec;
EXIT WHEN c_person_selection%NOTFOUND;
If p_person_selection_rule_id is not NULL then
--
begin
ben_batch_utils.person_selection_rule
(p_person_id => l_person_id
,p_business_group_id => ps_rec.business_group_id
,p_person_selection_rule_id=> p_person_selection_rule_id
,p_effective_date => l_effective_date
,p_return => l_person_ok
,p_err_message => l_err_message );
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
insert_person_actions (p_per_actn_id_array => l_person_action_ids
, p_per_id => l_person_ids
, p_group_per_in_ler_id => l_per_in_ler_ids
, p_benefit_action_id => l_benefit_action_id
, p_is_placeholder => l_is_placeholder
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_per_in_ler_ids.DELETE;
l_is_placeholder.DELETE;
CLOSE c_person_selection;
g_person_selected := l_num_rows;
WRITE ('Total no of person selected - ' || g_person_selected);
g_actn := 'Inserting the last range of persons if exists...';
write_m ('Time after processing the person selections '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
insert_person_actions (p_per_actn_id_array => l_person_action_ids
, p_per_id => l_person_ids
, p_group_per_in_ler_id => l_per_in_ler_ids
, p_benefit_action_id => l_benefit_action_id
, p_is_placeholder => l_is_placeholder
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_per_in_ler_ids.DELETE;
l_is_placeholder.DELETE;
ben_batch_utils.g_processes_tbl.DELETE;
WRITE ('<< No Person got selected with above selection criteria >>');
write_h ('||p_person_selected- ' || l_num_persons);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);