[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_CD_SUMMARY_PKG
Source
1 PACKAGE BODY BEN_CWB_CD_SUMMARY_PKG as
2 /* $Header: bencwbsum.pkb 120.0.12020000.2 2012/12/06 10:21:22 sgnanama noship $ */
3 FUNCTION Check_refresh_jobs
4 RETURN NUMBER
5 IS
6 l_check NUMBER;
7 BEGIN
8 SELECT 1
9 INTO l_check
10 FROM BEN_CWB_COMP_DETAILS
11 WHERE Nvl(bg_id, -1) = -2
12 AND Nvl(org_id, -1) = -2;
13
14 RETURN l_check;
15 EXCEPTION
16 WHEN no_data_found THEN
17 l_check := 0;
18
19 RETURN l_check;
20 END;
21 PROCEDURE Insert_refresh_jobs
22 IS
23 PRAGMA autonomous_transaction;
24 BEGIN
25 INSERT INTO BEN_CWB_COMP_DETAILS
26 (GROUP_PER_IN_LER_ID,
27 PERSON_ID,
28 GROUP_PL_ID,
29 LF_EVT_OCRD_DT,
30 bg_id,
31 org_id,
32 conc_request_id)
33 VALUES (-2,
34 -2,
35 -2,
36 To_date('01-01-0001', 'dd-mm-yyyy'),
37 -2,
38 -2,
39 Conc_Prog_Id);
40
41 ben_cwb_cd_summary_pkg.Message_log_proc('Inserted Pilot row in BEN_CWB_COMP_DETAILS', 5);
42
43 COMMIT;
44 END insert_refresh_jobs;
45
46 PROCEDURE Delete_refresh_jobs
47 IS
48 PRAGMA autonomous_transaction;
49 BEGIN
50 ben_cwb_cd_summary_pkg.Message_log_proc('Deleting Pilot row in BEN_CWB_COMP_DETAILS', 99);
51
52 DELETE FROM BEN_CWB_COMP_DETAILS
53 WHERE Nvl(bg_id, -1) = -2
54 AND Nvl(org_id, -1) = -2
55 AND Nvl(GROUP_PER_IN_LER_ID, -1) = -2
56 AND Nvl(PERSON_ID, -1) = -2;
57
58 COMMIT;
59 END delete_refresh_jobs;
60
61 PROCEDURE Ben_cwb_summary_populate (p_group_pl_id NUMBER,
62 p_lf_evt_ocrd_dt DATE)
63 IS
64 PRAGMA autonomous_transaction;
65 TYPE comp_analytics_bulk_collect
66 IS TABLE OF ben_cwb_comp_details%ROWTYPE;
67 psa COMP_ANALYTICS_BULK_COLLECT;
68 l_Start_Date Date;
69 CURSOR COMPANALYTICS IS SELECT per.group_per_in_ler_id
70 , per.group_pl_id
71 , per.lf_evt_ocrd_dt
72 , per.person_id
73 , temp.ploiplid
74 , temp.oip1plid
75 , temp.oip2plid
76 , temp.oip3plid
77 , temp.oip4plid
78 , temp.oipl1id
79 , temp.oipl2id
80 , temp.oipl3id
81 , temp.oipl4id
82 , temp.opt1oiplordrnum
83 , temp.opt2oiplordrnum
84 , temp.opt3oiplordrnum
85 , temp.opt4oiplordrnum
86 , temp.oip1grpoiplid
87 , temp.oip2grpoiplid
88 , temp.oip3grpoiplid
89 , temp.oip4grpoiplid
90 , plrt.elig_flag
91 , nvl2 (temp.oipl1id, opt1rt.elig_flag
92 , NULL) opt1_elig_flag
93 , nvl2 (temp.oipl2id, opt2rt.elig_flag
94 , NULL) opt2_elig_flag
95 , nvl2 (temp.oipl3id, opt3rt.elig_flag
96 , NULL) opt3_elig_flag
97 , nvl2 (temp.oipl4id, opt4rt.elig_flag
98 , NULL) opt4_elig_flag
99 , temp.planufactor
100 , temp.grpufactor
101 , pil.per_in_ler_stat_cd
102 , pil.procd_dt
103 , bgtl.organization_id bg_id
104 , orgtl.organization_id org_id
105 , per.location_id loc_id
106 , decode (per.grd_min_val, NULL
107 , to_char (NULL), ltrim (to_char (per.grd_min_val * per.grade_annulization_factor / per.pay_annulization_factor, '999G999G999G990D00'))
108 || '~'
109 || ltrim (to_char (per.grd_max_val * per.grade_annulization_factor / per.pay_annulization_factor, '999G999G999G990D00'))) grade_range
110 , per.grd_mid_point * per.grade_annulization_factor / per.pay_annulization_factor grade_mid_point
111 , decode (per.base_salary_frequency, 'HOURLY'
112 , per.base_salary, per.base_salary / decode (per.fte_factor, 0
113 , to_number (NULL), per.fte_factor)) prior_fte_salary
114 , (per.base_salary + decode (temp.plsalarycomp, 'ICM7'
115 , plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
116 , decode (temp.opt1salarycomp, 'ICM7'
117 , opt1rt.ws_val, 0), 0)
118 , 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
119 , decode (temp.opt2salarycomp, 'ICM7'
120 , opt2rt.ws_val, 0), 0)
121 , 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
122 , decode (temp.opt3salarycomp, 'ICM7'
123 , opt3rt.ws_val, 0), 0)
124 , 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
125 , decode (temp.opt4salarycomp, 'ICM7'
126 , opt4rt.ws_val, 0), 0)
127 , 0))) * (temp.planufactor / per.pay_annulization_factor)) / decode (per.base_salary_frequency, 'HOURLY'
128 , 1, decode (per.fte_factor, 0
129 , to_number (NULL), per.fte_factor)) new_fte_salary
130 , ben_cwb_person_info_pkg.get_grd_quartile ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
131 , plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
132 , decode (temp.opt1salarycomp, 'ICM7'
133 , opt1rt.ws_val, 0), 0)
134 , 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
135 , decode (temp.opt2salarycomp, 'ICM7'
136 , opt2rt.ws_val, 0), 0)
137 , 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
138 , decode (temp.opt3salarycomp, 'ICM7'
139 , opt3rt.ws_val, 0), 0)
140 , 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
141 , decode (temp.opt4salarycomp, 'ICM7'
142 , opt4rt.ws_val, 0), 0)
143 , 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
144 , 1, decode (per.fte_factor, 0
145 , to_number (NULL), per.fte_factor)), per.grd_min_val * per.grade_annulization_factor
146 , per.grd_max_val * per.grade_annulization_factor, per.grd_mid_point * per.grade_annulization_factor) new_quartile
147 , ben_cwb_person_info_pkg.get_grd_quintile ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
148 , plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
149 , decode (temp.opt1salarycomp, 'ICM7'
150 , opt1rt.ws_val, 0), 0)
151 , 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
152 , decode (temp.opt2salarycomp, 'ICM7'
153 , opt2rt.ws_val, 0), 0)
154 , 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
155 , decode (temp.opt3salarycomp, 'ICM7'
156 , opt3rt.ws_val, 0), 0)
157 , 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
158 , decode (temp.opt4salarycomp, 'ICM7'
159 , opt4rt.ws_val, 0), 0)
160 , 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
161 , 1, decode (per.fte_factor, 0
162 , to_number (NULL), per.fte_factor)), per.grd_min_val * per.grade_annulization_factor
163 , per.grd_max_val * per.grade_annulization_factor) new_quintile
164 , ben_cwb_person_info_pkg.get_grd_decile ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
165 , plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
166 , decode (temp.opt1salarycomp, 'ICM7'
167 , opt1rt.ws_val, 0), 0)
168 , 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
169 , decode (temp.opt2salarycomp, 'ICM7'
170 , opt2rt.ws_val, 0), 0)
171 , 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
172 , decode (temp.opt3salarycomp, 'ICM7'
173 , opt3rt.ws_val, 0), 0)
174 , 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
175 , decode (temp.opt4salarycomp, 'ICM7'
176 , opt4rt.ws_val, 0), 0)
177 , 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
178 , 1, decode (per.fte_factor, 0
179 , to_number (NULL), per.fte_factor)), per.grd_min_val * per.grade_annulization_factor
180 , per.grd_max_val * per.grade_annulization_factor) new_decile
181 , round (ben_cwb_person_info_pkg.get_grd_pct_in_range ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
182 , plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
183 , decode (temp.opt1salarycomp, 'ICM7'
184 , opt1rt.ws_val, 0), 0)
185 , 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
186 , decode (temp.opt2salarycomp, 'ICM7'
187 , opt2rt.ws_val, 0), 0)
188 , 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
189 , decode (temp.opt3salarycomp, 'ICM7'
190 , opt3rt.ws_val, 0), 0)
191 , 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
192 , decode (temp.opt4salarycomp, 'ICM7'
193 , opt4rt.ws_val, 0), 0)
194 , 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
195 , 1, decode (per.fte_factor, 0
196 , to_number (NULL), per.fte_factor)), per.grd_min_val * per.grade_annulization_factor
197 , per.grd_max_val * per.grade_annulization_factor), temp.pct_decs) new_percentile
198 , round (ben_cwb_person_info_pkg.get_grd_comparatio ((per.base_salary + decode (temp.plsalarycomp, 'ICM7'
199 , plrt.ws_val, (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
200 , decode (temp.opt1salarycomp, 'ICM7'
201 , opt1rt.ws_val, 0), 0)
202 , 0) + nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
203 , decode (temp.opt2salarycomp, 'ICM7'
204 , opt2rt.ws_val, 0), 0)
205 , 0) + nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
206 , decode (temp.opt3salarycomp, 'ICM7'
207 , opt3rt.ws_val, 0), 0)
208 , 0) + nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
209 , decode (temp.opt4salarycomp, 'ICM7'
210 , opt4rt.ws_val, 0), 0)
211 , 0))) * (temp.planufactor / per.pay_annulization_factor)) * per.pay_annulization_factor / decode (per.base_salary_frequency, 'HOURLY'
212 , 1, decode (per.fte_factor, 0
213 , to_number (NULL), per.fte_factor)), per.grd_mid_point * per.grade_annulization_factor), temp.pct_decs) new_comparatio
214 ,
215 (
216 SELECT ptxn.attribute3
217 FROM ben_transaction ptxn
218 WHERE ptxn.transaction_id = per.assignment_id
219 AND ptxn.transaction_type = 'CWBPERF'
220 || temp.perf_date
221 || temp.perf_type
222 ) proposed_performance_rating
223 , jobtl.job_id job_id
224 , jobdef.segment1 job_flex1
225 , jobdef.segment2 job_flex2
226 , jobdef.segment3 job_flex3
227 , jobdef.segment4 job_flex4
228 , jobdef.segment5 job_flex5
229 ,
230 (
231 SELECT to_number (atxn.attribute5)
232 FROM ben_transaction atxn
233 WHERE atxn.transaction_id = per.assignment_id
234 AND atxn.transaction_type = 'CWBASG'
235 || temp.asg_date
236 ) proposed_job_id
237 , postl.position_id position_id
238 ,
239 (
240 SELECT to_number (atxn.attribute6)
241 FROM ben_transaction atxn
242 WHERE atxn.transaction_id = per.assignment_id
243 AND atxn.transaction_type = 'CWBASG'
244 || temp.asg_date
245 ) proposed_position_id
246 , grdtl.grade_id grade_id
247 ,
248 (
249 SELECT to_number (atxn.attribute7)
250 FROM ben_transaction atxn
251 WHERE atxn.transaction_id = per.assignment_id
252 AND atxn.transaction_type = 'CWBASG'
253 || temp.asg_date
254 ) proposed_grade_id
255 , per.people_group_name people_group
256 ,
257 (
258 SELECT grp.group_name
259 FROM ben_transaction atxn
260 , pay_people_groups grp
261 WHERE atxn.transaction_id = per.assignment_id
262 AND atxn.transaction_type = 'CWBASG'
263 || temp.asg_date
264 AND to_number (atxn.attribute8) = grp.people_group_id
265 ) proposed_group
266 , asttl.assignment_status_type_id assignment_status_id
267 , ((per.base_salary * per.pay_annulization_factor / nvl (temp.planufactor, temp.grpufactor)) /
268 (
269 SELECT xchg_rate
270 FROM ben_cwb_xchg xchg
271 WHERE xchg.group_pl_id = temp.grpplid
272 AND xchg.lf_evt_ocrd_dt = temp.grplfevtocrddt
273 AND xchg.currency = per.base_salary_currency
274 )) corp_base_salary
275 , nvl (temp.plname, temp.grpplname) pl_name
276 , temp.opt1name opt1_name
277 , temp.opt2name opt2_name
278 , temp.opt3name opt3_name
279 , temp.opt4name opt4_name
280 , nvl (temp.plunits, plrt.currency) pl_uom
281 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
282 , nvl (temp.opt1units, opt1rt.currency), to_char (NULL))
283 , to_char (NULL)) opt1_uom
284 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
285 , nvl (temp.opt2units, opt2rt.currency), to_char (NULL))
286 , to_char (NULL)) opt2_uom
287 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
288 , nvl (temp.opt3units, opt3rt.currency), to_char (NULL))
289 , to_char (NULL)) opt3_uom
290 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
291 , nvl (temp.opt4units, opt4rt.currency), to_char (NULL))
292 , to_char (NULL)) opt4_uom
293 , plrt.currency pl_currency
294 , nvl2 (temp.oipl1id, opt1rt.currency
295 , to_char (NULL)) opt1_currency
296 , nvl2 (temp.oipl2id, opt2rt.currency
297 , to_char (NULL)) opt2_currency
298 , nvl2 (temp.oipl3id, opt3rt.currency
299 , to_char (NULL)) opt3_currency
300 , nvl2 (temp.oipl4id, opt4rt.currency
301 , to_char (NULL)) opt4_currency
302 , plrt.elig_sal_val pl_elig_sal_val
303 , plrt.ws_val pl_ws_val
304 , round (decode (plrt.elig_sal_val, NULL
305 , to_number (NULL), 0
306 , to_number (NULL), plrt.ws_val * 100 / plrt.elig_sal_val), temp.pct_decs) pl_pct_of_elig_sal
307 , plrt.ws_mn_val pl_ws_min_val
308 , plrt.ws_mx_val pl_ws_max_val
309 , plrt.ws_incr_val pl_ws_incr_val
310 , decode (temp.plsalarycomp, 'ICM7'
311 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + plrt.ws_val, to_number (NULL)) pl_new_salary
312 , decode (plrt.ws_mn_val, NULL
313 , to_char (NULL), ltrim (to_char (plrt.ws_mn_val, '999G999G999G990D00'))
314 || '~'
315 || ltrim (to_char (plrt.ws_mx_val, '999G999G999G990D00'))) pl_ws_val_limit
316 , plrt.rec_val pl_rec_val
317 , plrt.rec_mn_val pl_rec_mn_val
318 , plrt.rec_mx_val pl_rec_mx_val
319 , decode (plrt.rec_mn_val, NULL
320 , to_char (NULL), ltrim (to_char (plrt.rec_mn_val, '999G999G999G990D00'))
321 || '~'
322 || ltrim (to_char (plrt.rec_mx_val, '999G999G999G990D00'))) pl_rec_val_limit
323 , round (decode (plrt.elig_sal_val, NULL
324 , to_number (NULL), 0
325 , to_number (NULL), plrt.rec_val * 100 / plrt.elig_sal_val), temp.pct_decs) pl_rec_val_pct
326 , decode (plrt.rec_mn_val, NULL
327 , to_char (NULL), decode (plrt.elig_sal_val, NULL
328 , to_char (NULL), 0
329 , to_char (NULL), round (plrt.rec_mn_val * 100 / plrt.elig_sal_val, temp.pct_decs)
330 || '~'
331 || round (plrt.rec_mx_val * 100 / plrt.elig_sal_val, temp.pct_decs))) pl_rec_val_pct_limit
332 , plrt.misc1_val pl_misc1_val
333 , plrt.misc2_val pl_misc2_val
334 , plrt.misc3_val pl_misc3_val
335 , plrt.stat_sal_val pl_stat_sal_val
336 , plrt.tot_comp_val pl_tot_comp_val
337 , plrt.oth_comp_val pl_oth_comp_val
338 , round (plxchg.xchg_rate, 4) pl_xchg_rate
339 , plrt.elig_sal_val / decode (temp.plesunits, NULL
340 , plxchg.xchg_rate, 1) pl_corp_elig_sal_val
341 , plrt.ws_val / decode (temp.plunits, NULL
342 , plxchg.xchg_rate, 1) pl_corp_ws_val
343 , decode (temp.plsalarycomp, 'ICM7'
344 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + plrt.ws_val, to_number (NULL)) / plxchg.xchg_rate pl_corp_new_salary
345 , temp.plsalarycomp pl_salary_comp
346 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
347 , opt1rt.elig_sal_val, to_number (NULL))
348 , to_number (NULL)) opt1_elig_sal_val
349 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
350 , opt1rt.ws_val, to_number (NULL))
351 , to_number (NULL)) opt1_ws_val
352 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
353 , round (decode (opt1rt.elig_sal_val, NULL
354 , to_number (NULL), 0
355 , to_number (NULL), opt1rt.ws_val * 100 / opt1rt.elig_sal_val), temp.pct_decs), to_number (NULL))
356 , to_number (NULL)) opt1_pct_of_elig_sal
357 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
358 , opt1rt.ws_mn_val, to_number (NULL))
359 , to_number (NULL)) opt1_ws_min_val
360 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
361 , opt1rt.ws_mx_val, to_number (NULL))
362 , to_number (NULL)) opt1_ws_max_val
363 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
364 , opt1rt.ws_incr_val, to_number (NULL))
365 , to_number (NULL)) opt1_ws_incr_val
366 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
367 , decode (temp.opt1salarycomp, 'ICM7'
368 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt1rt.ws_val, to_number (NULL)), to_number (NULL))
369 , to_number (NULL)) opt1_new_salary
370 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
371 , decode (opt1rt.ws_mn_val, NULL
372 , to_char (NULL), ltrim (to_char (opt1rt.ws_mn_val, '999G999G999G990D00'))
373 || '~'
374 || ltrim (to_char (opt1rt.ws_mx_val, '999G999G999G990D00'))), to_char (NULL))
375 , to_char (NULL)) opt1_ws_val_limit
376 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
377 , opt1rt.rec_val, to_number (NULL))
378 , to_number (NULL)) opt1_rec_val
379 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
380 , opt1rt.rec_mn_val, to_number (NULL))
381 , to_number (NULL)) opt1_rec_mn_val
382 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
383 , opt1rt.rec_mx_val, to_number (NULL))
384 , to_number (NULL)) opt1_rec_mx_val
385 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
386 , decode (opt1rt.rec_mn_val, NULL
387 , to_char (NULL), ltrim (to_char (opt1rt.rec_mn_val, '999G999G999G990D00'))
388 || '~'
389 || ltrim (to_char (opt1rt.rec_mx_val, '999G999G999G990D00'))), to_char (NULL))
390 , to_char (NULL)) opt1_rec_val_limit
391 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
392 , round (decode (opt1rt.elig_sal_val, NULL
393 , to_number (NULL), 0
394 , to_number (NULL), opt1rt.rec_val * 100 / opt1rt.elig_sal_val), temp.pct_decs), to_number (NULL))
395 , to_number (NULL)) opt1_rec_val_pct
396 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
397 , decode (opt1rt.rec_mn_val, NULL
398 , to_char (NULL), decode (opt1rt.elig_sal_val, NULL
399 , to_char (NULL), 0
400 , to_char (NULL), round (opt1rt.rec_mn_val * 100 / opt1rt.elig_sal_val, temp.pct_decs)
401 || '~'
402 || round (opt1rt.rec_mx_val * 100 / opt1rt.elig_sal_val, temp.pct_decs))), to_char (NULL))
403 , to_char (NULL)) opt1_rec_val_pct_limit
404 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
405 , opt1rt.misc1_val, to_number (NULL))
406 , to_number (NULL)) opt1_misc1_val
407 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
408 , opt1rt.misc2_val, to_number (NULL))
409 , to_number (NULL)) opt1_misc2_val
410 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
411 , opt1rt.misc3_val, to_number (NULL))
412 , to_number (NULL)) opt1_misc3_val
413 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
414 , opt1rt.stat_sal_val, to_number (NULL))
415 , to_number (NULL)) opt1_stat_sal_val
416 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
417 , opt1rt.tot_comp_val, to_number (NULL))
418 , to_number (NULL)) opt1_tot_comp_val
419 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
420 , opt1rt.oth_comp_val, to_number (NULL))
421 , to_number (NULL)) opt1_oth_comp_val
422 , round (nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
423 , opt1xchg.xchg_rate, to_number (NULL))
424 , to_number (NULL)), 4) opt1_xchg_rate
425 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
426 , opt1rt.elig_sal_val / decode (temp.opt1esunits, NULL
427 , opt1xchg.xchg_rate, 1), to_number (NULL))
428 , to_number (NULL)) opt1_corp_elig_sal_val
429 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
430 , opt1rt.ws_val / decode (temp.opt1units, NULL
431 , opt1xchg.xchg_rate, 1), to_number (NULL))
432 , to_number (NULL)) opt1_corp_ws_val
433 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
434 , decode (temp.opt1salarycomp, 'ICM7'
435 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt1rt.ws_val, to_number (NULL)) / opt1xchg.xchg_rate, to_number (NULL))
436 , to_number (NULL)) opt1_corp_new_salary
437 , nvl2 (temp.oipl1id, decode (opt1rt.elig_flag, 'Y'
438 , temp.opt1salarycomp, to_number (NULL))
439 , to_number (NULL)) opt1_salary_comp
440 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
441 , opt2rt.elig_sal_val, to_number (NULL))
442 , to_number (NULL)) opt2_elig_sal_val
443 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
444 , opt2rt.ws_val, to_number (NULL))
445 , to_number (NULL)) opt2_ws_val
446 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
447 , round (decode (opt2rt.elig_sal_val, NULL
448 , to_number (NULL), 0
449 , to_number (NULL), opt2rt.ws_val * 100 / opt2rt.elig_sal_val), temp.pct_decs), to_number (NULL))
450 , to_number (NULL)) opt2_pct_of_elig_sal
451 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
452 , opt2rt.ws_mn_val, to_number (NULL))
453 , to_number (NULL)) opt2_ws_min_val
454 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
455 , opt2rt.ws_mx_val, to_number (NULL))
456 , to_number (NULL)) opt2_ws_max_val
457 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
458 , opt2rt.ws_incr_val, to_number (NULL))
459 , to_number (NULL)) opt2_ws_incr_val
460 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
461 , decode (temp.opt2salarycomp, 'ICM7'
462 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt2rt.ws_val, to_number (NULL)), to_number (NULL))
463 , to_number (NULL)) opt2_new_salary
464 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
465 , decode (opt2rt.ws_mn_val, NULL
466 , to_char (NULL), ltrim (to_char (opt2rt.ws_mn_val, '999G999G999G990D00'))
467 || '~'
468 || ltrim (to_char (opt2rt.ws_mx_val, '999G999G999G990D00'))), to_char (NULL))
469 , to_char (NULL)) opt2_ws_val_limit
470 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
471 , opt2rt.rec_val, to_number (NULL))
472 , to_number (NULL)) opt2_rec_val
473 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
474 , opt2rt.rec_mn_val, to_number (NULL))
475 , to_number (NULL)) opt2_rec_mn_val
476 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
477 , opt2rt.rec_mx_val, to_number (NULL))
478 , to_number (NULL)) opt2_rec_mx_val
479 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
480 , decode (opt2rt.rec_mn_val, NULL
481 , to_char (NULL), ltrim (to_char (opt2rt.rec_mn_val, '999G999G999G990D00'))
482 || '~'
483 || ltrim (to_char (opt2rt.rec_mx_val, '999G999G999G990D00'))), to_char (NULL))
484 , to_char (NULL)) opt2_rec_val_limit
485 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
486 , round (decode (opt2rt.elig_sal_val, NULL
487 , to_number (NULL), 0
488 , to_number (NULL), opt2rt.rec_val * 100 / opt2rt.elig_sal_val), temp.pct_decs), to_number (NULL))
489 , to_number (NULL)) opt2_rec_val_pct
490 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
491 , decode (opt2rt.rec_mn_val, NULL
492 , to_char (NULL), decode (opt2rt.elig_sal_val, NULL
493 , to_char (NULL), 0
494 , to_char (NULL), round (opt2rt.rec_mn_val * 100 / opt2rt.elig_sal_val, temp.pct_decs)
495 || '~'
496 || round (opt2rt.rec_mx_val * 100 / opt2rt.elig_sal_val, temp.pct_decs))), to_char (NULL))
497 , to_char (NULL)) opt2_rec_val_pct_limit
498 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
499 , opt2rt.misc1_val, to_number (NULL))
500 , to_number (NULL)) opt2_misc1_val
501 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
502 , opt2rt.misc2_val, to_number (NULL))
503 , to_number (NULL)) opt2_misc2_val
504 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
505 , opt2rt.misc3_val, to_number (NULL))
506 , to_number (NULL)) opt2_misc3_val
507 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
508 , opt2rt.stat_sal_val, to_number (NULL))
509 , to_number (NULL)) opt2_stat_sal_val
510 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
511 , opt2rt.tot_comp_val, to_number (NULL))
512 , to_number (NULL)) opt2_tot_comp_val
513 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
514 , opt2rt.oth_comp_val, to_number (NULL))
515 , to_number (NULL)) opt2_oth_comp_val
516 , round (nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
517 , opt2xchg.xchg_rate, to_number (NULL))
518 , to_number (NULL)), 4) opt2_xchg_rate
519 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
520 , opt2rt.elig_sal_val / decode (temp.opt2esunits, NULL
521 , opt2xchg.xchg_rate, 1), to_number (NULL))
522 , to_number (NULL)) opt2_corp_elig_sal_val
523 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
524 , opt2rt.ws_val / decode (temp.opt2units, NULL
525 , opt2xchg.xchg_rate, 1), to_number (NULL))
526 , to_number (NULL)) opt2_corp_ws_val
527 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
528 , decode (temp.opt2salarycomp, 'ICM7'
529 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt2rt.ws_val, to_number (NULL)) / opt2xchg.xchg_rate, to_number (NULL))
530 , to_number (NULL)) opt2_corp_new_salary
531 , nvl2 (temp.oipl2id, decode (opt2rt.elig_flag, 'Y'
532 , temp.opt2salarycomp, to_number (NULL))
533 , to_number (NULL)) opt2_salary_comp
534 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
535 , opt3rt.elig_sal_val, to_number (NULL))
536 , to_number (NULL)) opt3_elig_sal_val
537 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
538 , opt3rt.ws_val, to_number (NULL))
539 , to_number (NULL)) opt3_ws_val
540 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
541 , round (decode (opt3rt.elig_sal_val, NULL
542 , to_number (NULL), 0
543 , to_number (NULL), opt3rt.ws_val * 100 / opt3rt.elig_sal_val), temp.pct_decs), to_number (NULL))
544 , to_number (NULL)) opt3_pct_of_elig_sal
545 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
546 , opt3rt.ws_mn_val, to_number (NULL))
547 , to_number (NULL)) opt3_ws_min_val
548 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
549 , opt3rt.ws_mx_val, to_number (NULL))
550 , to_number (NULL)) opt3_ws_max_val
551 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
552 , opt3rt.ws_incr_val, to_number (NULL))
553 , to_number (NULL)) opt3_ws_incr_val
554 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
555 , decode (temp.opt3salarycomp, 'ICM7'
556 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt3rt.ws_val, to_number (NULL)), to_number (NULL))
557 , to_number (NULL)) opt3_new_salary
558 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
559 , decode (opt3rt.ws_mn_val, NULL
560 , to_char (NULL), ltrim (to_char (opt3rt.ws_mn_val, '999G999G999G990D00'))
561 || '~'
562 || ltrim (to_char (opt3rt.ws_mx_val, '999G999G999G990D00'))), to_char (NULL))
563 , to_char (NULL)) opt3_ws_val_limit
564 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
565 , opt3rt.rec_val, to_number (NULL))
566 , to_number (NULL)) opt3_rec_val
567 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
568 , opt3rt.rec_mn_val, to_number (NULL))
569 , to_number (NULL)) opt3_rec_mn_val
570 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
571 , opt3rt.rec_mx_val, to_number (NULL))
572 , to_number (NULL)) opt3_rec_mx_val
573 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
574 , decode (opt3rt.rec_mn_val, NULL
575 , to_char (NULL), ltrim (to_char (opt3rt.rec_mn_val, '999G999G999G990D00'))
576 || '~'
577 || ltrim (to_char (opt3rt.rec_mx_val, '999G999G999G990D00'))), to_char (NULL))
578 , to_char (NULL)) opt3_rec_val_limit
579 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
580 , round (decode (opt3rt.elig_sal_val, NULL
581 , to_number (NULL), 0
582 , to_number (NULL), opt3rt.rec_val * 100 / opt3rt.elig_sal_val), temp.pct_decs), to_number (NULL))
583 , to_number (NULL)) opt3_rec_val_pct
584 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
585 , decode (opt3rt.rec_mn_val, NULL
586 , to_char (NULL), decode (opt3rt.elig_sal_val, NULL
587 , to_char (NULL), 0
588 , to_char (NULL), round (opt3rt.rec_mn_val * 100 / opt3rt.elig_sal_val, temp.pct_decs)
589 || '~'
590 || round (opt3rt.rec_mx_val * 100 / opt3rt.elig_sal_val, temp.pct_decs))), to_char (NULL))
591 , to_char (NULL)) opt3_rec_val_pct_limit
592 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
593 , opt3rt.misc1_val, to_number (NULL))
594 , to_number (NULL)) opt3_misc1_val
595 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
596 , opt3rt.misc2_val, to_number (NULL))
597 , to_number (NULL)) opt3_misc2_val
598 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
599 , opt3rt.misc3_val, to_number (NULL))
600 , to_number (NULL)) opt3_misc3_val
601 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
602 , opt3rt.stat_sal_val, to_number (NULL))
603 , to_number (NULL)) opt3_stat_sal_val
604 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
605 , opt3rt.tot_comp_val, to_number (NULL))
606 , to_number (NULL)) opt3_tot_comp_val
607 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
608 , opt3rt.oth_comp_val, to_number (NULL))
609 , to_number (NULL)) opt3_oth_comp_val
610 , round (nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
611 , opt3xchg.xchg_rate, to_number (NULL))
612 , to_number (NULL)), 4) opt3_xchg_rate
613 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
614 , opt3rt.elig_sal_val / decode (temp.opt3esunits, NULL
615 , opt3xchg.xchg_rate, 1), to_number (NULL))
616 , to_number (NULL)) opt3_corp_elig_sal_val
617 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
618 , opt3rt.ws_val / decode (temp.opt3units, NULL
619 , opt3xchg.xchg_rate, 1), to_number (NULL))
620 , to_number (NULL)) opt3_corp_ws_val
621 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
622 , decode (temp.opt3salarycomp, 'ICM7'
623 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt3rt.ws_val, to_number (NULL)) / opt3xchg.xchg_rate, to_number (NULL))
624 , to_number (NULL)) opt3_corp_new_salary
625 , nvl2 (temp.oipl3id, decode (opt3rt.elig_flag, 'Y'
626 , temp.opt3salarycomp, to_number (NULL))
627 , to_number (NULL)) opt3_salary_comp
628 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
629 , opt4rt.elig_sal_val, to_number (NULL))
630 , to_number (NULL)) opt4_elig_sal_val
631 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
632 , opt4rt.ws_val, to_number (NULL))
633 , to_number (NULL)) opt4_ws_val
634 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
635 , round (decode (opt4rt.elig_sal_val, NULL
636 , to_number (NULL), 0
637 , to_number (NULL), opt4rt.ws_val * 100 / opt4rt.elig_sal_val), temp.pct_decs), to_number (NULL))
638 , to_number (NULL)) opt4_pct_of_elig_sal
639 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
640 , opt4rt.ws_mn_val, to_number (NULL))
641 , to_number (NULL)) opt4_ws_min_val
642 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
643 , opt4rt.ws_mx_val, to_number (NULL))
644 , to_number (NULL)) opt4_ws_max_val
645 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
646 , opt4rt.ws_incr_val, to_number (NULL))
647 , to_number (NULL)) opt4_ws_incr_val
648 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
649 , decode (temp.opt4salarycomp, 'ICM7'
650 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt4rt.ws_val, to_number (NULL)), to_number (NULL))
651 , to_number (NULL)) opt4_new_salary
652 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
653 , decode (opt4rt.ws_mn_val, NULL
654 , to_char (NULL), ltrim (to_char (opt4rt.ws_mn_val, '999G999G999G990D00'))
655 || '~'
656 || ltrim (to_char (opt4rt.ws_mx_val, '999G999G999G990D00'))), to_char (NULL))
657 , to_char (NULL)) opt4_ws_val_limit
658 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
659 , opt4rt.rec_val, to_number (NULL))
660 , to_number (NULL)) opt4_rec_val
661 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
662 , opt4rt.rec_mn_val, to_number (NULL))
663 , to_number (NULL)) opt4_rec_mn_val
664 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
665 , opt4rt.rec_mx_val, to_number (NULL))
666 , to_number (NULL)) opt4_rec_mx_val
667 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
668 , decode (opt4rt.rec_mn_val, NULL
669 , to_char (NULL), ltrim (to_char (opt4rt.rec_mn_val, '999G999G999G990D00'))
670 || '~'
671 || ltrim (to_char (opt4rt.rec_mx_val, '999G999G999G990D00'))), to_char (NULL))
672 , to_char (NULL)) opt4_rec_val_limit
673 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
674 , round (decode (opt4rt.elig_sal_val, NULL
675 , to_number (NULL), 0
676 , to_number (NULL), opt4rt.rec_val * 100 / opt4rt.elig_sal_val), temp.pct_decs), to_number (NULL))
677 , to_number (NULL)) opt4_rec_val_pct
678 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
679 , decode (opt4rt.rec_mn_val, NULL
680 , to_char (NULL), decode (opt4rt.elig_sal_val, NULL
681 , to_char (NULL), 0
682 , to_char (NULL), round (opt4rt.rec_mn_val * 100 / opt4rt.elig_sal_val, temp.pct_decs)
683 || '~'
684 || round (opt4rt.rec_mx_val * 100 / opt4rt.elig_sal_val, temp.pct_decs))), to_char (NULL))
685 , to_char (NULL)) opt4_rec_val_pct_limit
686 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
687 , opt4rt.misc1_val, to_number (NULL))
688 , to_number (NULL)) opt4_misc1_val
689 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
690 , opt4rt.misc2_val, to_number (NULL))
691 , to_number (NULL)) opt4_misc2_val
692 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
693 , opt4rt.misc3_val, to_number (NULL))
694 , to_number (NULL)) opt4_misc3_val
695 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
696 , opt4rt.stat_sal_val, to_number (NULL))
697 , to_number (NULL)) opt4_stat_sal_val
698 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
699 , opt4rt.tot_comp_val, to_number (NULL))
700 , to_number (NULL)) opt4_tot_comp_val
701 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
702 , opt4rt.oth_comp_val, to_number (NULL))
703 , to_number (NULL)) opt4_oth_comp_val
704 , round (nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
705 , opt4xchg.xchg_rate, to_number (NULL))
706 , to_number (NULL)), 4) opt4_xchg_rate
707 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
708 , opt4rt.elig_sal_val / decode (temp.opt4esunits, NULL
709 , opt4xchg.xchg_rate, 1), to_number (NULL))
710 , to_number (NULL)) opt4_corp_elig_sal_val
711 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
712 , opt4rt.ws_val / decode (temp.opt4units, NULL
713 , opt4xchg.xchg_rate, 1), to_number (NULL))
714 , to_number (NULL)) opt4_corp_ws_val
715 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
716 , decode (temp.opt4salarycomp, 'ICM7'
717 , (per.base_salary * per.pay_annulization_factor / temp.planufactor) + opt4rt.ws_val, to_number (NULL)) / opt4xchg.xchg_rate, to_number (NULL))
718 , to_number (NULL)) opt4_corp_new_salary
719 , nvl2 (temp.oipl4id, decode (opt4rt.elig_flag, 'Y'
720 , temp.opt4salarycomp, to_number (NULL))
721 , to_number (NULL)) opt4_salary_comp
722 ,
723 (
724 SELECT txn.attribute3
725 FROM ben_transaction txn
726 WHERE txn.transaction_id = per.assignment_id
727 AND txn.transaction_type = 'CWBASG'
728 || temp.asg_date
729 AND txn.attribute3 IS NOT NULL
730 ) change_reason
731 ,
732 (
733 SELECT bcpi.full_name
734 FROM ben_cwb_person_info bcpi
735 , ben_cwb_group_hrchy bcgh
736 WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
737 AND bcgh.lvl_num =
738 (
739 SELECT max (lvl_num) - 1 + 1
740 FROM ben_cwb_group_hrchy
741 WHERE emp_per_in_ler_id = per.group_per_in_ler_id
742 )
743 AND bcgh.lvl_num > 0
744 AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
745 ) level1mgr
746 ,
747 (
748 SELECT bcpi.full_name
749 FROM ben_cwb_person_info bcpi
750 , ben_cwb_group_hrchy bcgh
751 WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
752 AND bcgh.lvl_num =
753 (
754 SELECT max (lvl_num) - 2 + 1
755 FROM ben_cwb_group_hrchy
756 WHERE emp_per_in_ler_id = per.group_per_in_ler_id
757 )
758 AND bcgh.lvl_num > 0
759 AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
760 ) level2mgr
761 ,
762 (
763 SELECT bcpi.full_name
764 FROM ben_cwb_person_info bcpi
765 , ben_cwb_group_hrchy bcgh
766 WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
767 AND bcgh.lvl_num =
768 (
769 SELECT max (lvl_num) - 3 + 1
770 FROM ben_cwb_group_hrchy
771 WHERE emp_per_in_ler_id = per.group_per_in_ler_id
772 )
773 AND bcgh.lvl_num > 0
774 AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
775 ) level3mgr
776 ,
777 (
778 SELECT bcpi.full_name
779 FROM ben_cwb_person_info bcpi
780 , ben_cwb_group_hrchy bcgh
781 WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
782 AND bcgh.lvl_num =
783 (
784 SELECT max (lvl_num) - 4 + 1
785 FROM ben_cwb_group_hrchy
786 WHERE emp_per_in_ler_id = per.group_per_in_ler_id
787 )
788 AND bcgh.lvl_num > 0
789 AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
790 ) level4mgr
791 ,
792 (
793 SELECT bcpi.full_name
794 FROM ben_cwb_person_info bcpi
795 , ben_cwb_group_hrchy bcgh
796 WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
797 AND bcgh.lvl_num =
798 (
799 SELECT max (lvl_num) - 5 + 1
800 FROM ben_cwb_group_hrchy
801 WHERE emp_per_in_ler_id = per.group_per_in_ler_id
802 )
803 AND bcgh.lvl_num > 0
804 AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
805 ) level5mgr
806 ,
807 (
808 SELECT bcpi.full_name
809 FROM ben_cwb_person_info bcpi
810 , ben_cwb_group_hrchy bcgh
811 WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
812 AND bcgh.lvl_num =
813 (
814 SELECT max (lvl_num) - 6 + 1
815 FROM ben_cwb_group_hrchy
816 WHERE emp_per_in_ler_id = per.group_per_in_ler_id
817 )
818 AND bcgh.lvl_num > 0
819 AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
820 ) level6mgr
821 ,
822 (
823 SELECT bcpi.full_name
824 FROM ben_cwb_person_info bcpi
825 , ben_cwb_group_hrchy bcgh
826 WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
827 AND bcgh.lvl_num =
828 (
829 SELECT max (lvl_num) - 7 + 1
830 FROM ben_cwb_group_hrchy
831 WHERE emp_per_in_ler_id = per.group_per_in_ler_id
832 )
833 AND bcgh.lvl_num > 0
834 AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
835 ) level7mgr
836 ,
837 (
838 SELECT bcpi.full_name
839 FROM ben_cwb_person_info bcpi
840 , ben_cwb_group_hrchy bcgh
841 WHERE bcgh.emp_per_in_ler_id = per.group_per_in_ler_id
842 AND bcgh.lvl_num =
843 (
844 SELECT max (lvl_num) - 8 + 1
845 FROM ben_cwb_group_hrchy
846 WHERE emp_per_in_ler_id = per.group_per_in_ler_id
847 )
848 AND bcgh.lvl_num > 0
849 AND bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id
850 ) level8mgr
851 , decode (temp.plsalarycomp, 'ICM7'
852 , plrt.currency, decode (temp.opt1salarycomp, 'ICM7'
853 , opt1rt.currency, decode (temp.opt2salarycomp, 'ICM7'
854 , opt2rt.currency, decode (temp.opt3salarycomp, 'ICM7'
855 , opt3rt.currency, decode (temp.opt4salarycomp, 'ICM7'
856 , opt4rt.currency, temp.grpcurr))))) sal_stat_currency
857 ,
858 (
859 SELECT decode (temp.name_profile, 'BN'
860 , mgrper.brief_name, 'CN'
861 , mgrper.custom_name, mgrper.full_name)
862 FROM ben_cwb_group_hrchy hrchy1
863 , ben_cwb_person_info mgrper
864 WHERE hrchy1.emp_per_in_ler_id = per.group_per_in_ler_id
865 AND hrchy1.lvl_num = 1
866 AND hrchy1.mgr_per_in_ler_id = mgrper.group_per_in_ler_id
867 ) wrk_mgr_name
868 , temp.grpcurr corp_currency
869 , fnd_global.conc_request_id conc_request_id
870 , NULL start_date
871 , NULL end_date
872 FROM ben_cwb_person_rates plrt
873 , ben_cwb_person_rates opt1rt
874 , ben_cwb_person_rates opt2rt
875 , ben_cwb_person_rates opt3rt
876 , ben_cwb_person_rates opt4rt
877 , ben_cwb_xchg plxchg
878 , ben_cwb_xchg opt1xchg
879 , ben_cwb_xchg opt2xchg
880 , ben_cwb_xchg opt3xchg
881 , ben_cwb_xchg opt4xchg
882 , ben_per_in_ler pil
883 , ben_cwb_person_info per
884 , per_assignment_status_types_tl asttl
885 , hr_all_organization_units_tl bgtl
886 , hr_all_organization_units_tl orgtl
887 , hr_locations_all_tl loctl
888 , per_jobs job
889 , per_jobs_tl jobtl
890 , per_job_definitions jobdef
891 , hr_all_positions_f_tl postl
892 , per_grades_tl grdtl
893 ,
894 (
895 SELECT /*+ merge leading(grp_pl)*/
896 fnd_profile.value ('BEN_DISPLAY_EMPLOYEE_NAME') name_profile
897 , nvl (fnd_profile.value ('BEN_CWB_WS_PCT_ES_DECS_DISP'), 2) pct_decs
898 , pl1.pl_id plid
899 , pl1.name plname
900 , pl1.oipl_id ploiplid
901 , pl1.ws_abr_id plwsabrid
902 , pl1.pl_annulization_factor planufactor
903 , pl1.ws_sub_acty_typ_cd plsalarycomp
904 , hrl1.meaning plunits
905 , pl1.elig_sal_nnmntry_uom plesunits
906 , opt1.oipl_id oipl1id
907 , opt1.group_pl_id oip1grpplid
908 , opt1.group_oipl_id oip1grpoiplid
909 , opt1.pl_id oip1plid
910 , opt1.name opt1name
911 , opt1.ws_abr_id opt1wsabrid
912 , opt1.oipl_ordr_num opt1oiplordrnum
913 , opt1.ws_sub_acty_typ_cd opt1salarycomp
914 , hrl2.meaning opt1units
915 , opt1.elig_sal_nnmntry_uom opt1esunits
916 , opt2.oipl_id oipl2id
917 , opt2.group_pl_id oip2grpplid
918 , opt2.group_oipl_id oip2grpoiplid
919 , opt2.pl_id oip2plid
920 , opt2.name opt2name
921 , opt2.ws_abr_id opt2wsabrid
922 , opt2.oipl_ordr_num opt2oiplordrnum
923 , opt2.ws_sub_acty_typ_cd opt2salarycomp
924 , hrl3.meaning opt2units
925 , opt2.elig_sal_nnmntry_uom opt2esunits
926 , opt3.oipl_id oipl3id
927 , opt3.group_pl_id oip3grpplid
928 , opt3.group_oipl_id oip3grpoiplid
929 , opt3.pl_id oip3plid
930 , opt3.name opt3name
931 , opt3.ws_abr_id opt3wsabrid
932 , opt3.oipl_ordr_num opt3oiplordrnum
933 , opt3.ws_sub_acty_typ_cd opt3salarycomp
934 , hrl4.meaning opt3units
935 , opt3.elig_sal_nnmntry_uom opt3esunits
936 , opt4.oipl_id oipl4id
937 , opt4.group_pl_id oip4grpplid
938 , opt4.group_oipl_id oip4grpoiplid
939 , opt4.pl_id oip4plid
940 , opt4.name opt4name
941 , opt4.ws_abr_id opt4wsabrid
942 , opt4.oipl_ordr_num opt4oiplordrnum
943 , opt4.ws_sub_acty_typ_cd opt4salarycomp
944 , hrl5.meaning opt4units
945 , opt4.elig_sal_nnmntry_uom opt4esunits
946 , grp_pl.pl_id grpplid
947 , grp_pl.oipl_id grpoiplid
948 , grp_pl.lf_evt_ocrd_dt grplfevtocrddt
949 , grp_pl.name grpplname
950 , to_char (grp_pl.perf_revw_strt_dt, 'yyyy/mm/dd') perf_date
951 , grp_pl.emp_interview_typ_cd perf_type
952 , to_char (grp_pl.asg_updt_eff_date, 'yyyy/mm/dd') asg_date
953 , grp_pl.ws_abr_id grpwsabrid
954 , grp_pl.pl_annulization_factor grpufactor
955 , hr_general.decode_lookup ('BEN_NNMNTRY_UOM', grp_pl.ws_nnmntry_uom) grpunits
956 , grp_pl.pl_uom grpcurr
957 FROM ben_cwb_pl_dsgn grp_pl
958 , ben_cwb_pl_dsgn pl1
959 , ben_cwb_pl_dsgn opt1
960 , ben_cwb_pl_dsgn opt2
961 , ben_cwb_pl_dsgn opt3
962 , ben_cwb_pl_dsgn opt4
963 , hr_lookups hrl1
964 , hr_lookups hrl2
965 , hr_lookups hrl3
966 , hr_lookups hrl4
967 , hr_lookups hrl5
968 WHERE p_group_pl_id = grp_pl.pl_id
969 AND p_lf_evt_ocrd_dt = grp_pl.lf_evt_ocrd_dt
970 AND grp_pl.oipl_id = - 1
971 AND grp_pl.group_pl_id = grp_pl.pl_id
972 AND grp_pl.oipl_id = grp_pl.group_oipl_id
973 AND pl1.group_pl_id (+) = grp_pl.group_pl_id
974 AND pl1.lf_evt_ocrd_dt (+) = grp_pl.lf_evt_ocrd_dt
975 AND pl1.actual_flag (+) = 'Y'
976 AND pl1.oipl_id (+) = - 1
977 AND opt1.pl_id (+) = pl1.pl_id
978 AND opt1.lf_evt_ocrd_dt (+) = pl1.lf_evt_ocrd_dt
979 AND opt1.oipl_id (+) <> - 1
980 AND opt1.oipl_ordr_num (+) = 1
981 AND opt2.pl_id (+) = pl1.pl_id
982 AND opt2.lf_evt_ocrd_dt (+) = pl1.lf_evt_ocrd_dt
983 AND opt2.oipl_id (+) <> - 1
984 AND opt2.oipl_ordr_num (+) = 2
985 AND opt3.pl_id (+) = pl1.pl_id
986 AND opt3.lf_evt_ocrd_dt (+) = pl1.lf_evt_ocrd_dt
987 AND opt3.oipl_id (+) <> - 1
988 AND opt3.oipl_ordr_num (+) = 3
989 AND opt4.pl_id (+) = pl1.pl_id
990 AND opt4.lf_evt_ocrd_dt (+) = pl1.lf_evt_ocrd_dt
991 AND opt4.oipl_id (+) <> - 1
992 AND opt4.oipl_ordr_num (+) = 4
993 AND hrl1.lookup_type (+) = 'BEN_NNMNTRY_UOM'
994 AND hrl1.lookup_code (+) = pl1.ws_nnmntry_uom
995 AND hrl2.lookup_type (+) = 'BEN_NNMNTRY_UOM'
996 AND hrl2.lookup_code (+) = opt1.ws_nnmntry_uom
997 AND hrl3.lookup_type (+) = 'BEN_NNMNTRY_UOM'
998 AND hrl3.lookup_code (+) = opt2.ws_nnmntry_uom
999 AND hrl4.lookup_type (+) = 'BEN_NNMNTRY_UOM'
1000 AND hrl4.lookup_code (+) = opt3.ws_nnmntry_uom
1001 AND hrl5.lookup_type (+) = 'BEN_NNMNTRY_UOM'
1002 AND hrl5.lookup_code (+) = opt4.ws_nnmntry_uom
1003 ) temp
1004 WHERE per.group_per_in_ler_id = pil.per_in_ler_id
1005 AND pil.per_in_ler_stat_cd <> 'BCKDT'
1006 AND p_lf_evt_ocrd_dt = per.lf_evt_ocrd_dt
1007 AND per.group_pl_id = temp.grpplid
1008 AND per.group_per_in_ler_id = plrt.group_per_in_ler_id
1009 AND plrt.oipl_id = - 1
1010 AND plrt.elig_flag = 'Y'
1011 AND plrt.pl_id = temp.plid
1012 AND plrt.lf_evt_ocrd_dt = temp.grplfevtocrddt
1013 AND plrt.group_pl_id = plxchg.group_pl_id
1014 AND plrt.lf_evt_ocrd_dt = plxchg.lf_evt_ocrd_dt
1015 AND plrt.currency = plxchg.currency
1016 AND per.group_per_in_ler_id = opt1rt.group_per_in_ler_id
1017 AND opt1rt.pl_id = plrt.pl_id
1018 AND opt1rt.lf_evt_ocrd_dt = plrt.lf_evt_ocrd_dt
1019 AND opt1rt.oipl_id = nvl (temp.oipl1id, - 1)
1020 AND opt1rt.group_pl_id = opt1xchg.group_pl_id
1021 AND opt1rt.lf_evt_ocrd_dt = opt1xchg.lf_evt_ocrd_dt
1022 AND opt1rt.currency = opt1xchg.currency
1023 AND per.group_per_in_ler_id = opt2rt.group_per_in_ler_id
1024 AND opt2rt.pl_id = plrt.pl_id
1025 AND opt2rt.lf_evt_ocrd_dt = plrt.lf_evt_ocrd_dt
1026 AND opt2rt.oipl_id = nvl (temp.oipl2id, - 1)
1027 AND opt2rt.group_pl_id = opt2xchg.group_pl_id
1028 AND opt2rt.lf_evt_ocrd_dt = opt2xchg.lf_evt_ocrd_dt
1029 AND opt2rt.currency = opt2xchg.currency
1030 AND per.group_per_in_ler_id = opt3rt.group_per_in_ler_id
1031 AND opt3rt.pl_id = plrt.pl_id
1032 AND opt3rt.lf_evt_ocrd_dt = plrt.lf_evt_ocrd_dt
1033 AND opt3rt.oipl_id = nvl (temp.oipl3id, - 1)
1034 AND opt3rt.group_pl_id = opt3xchg.group_pl_id
1035 AND opt3rt.lf_evt_ocrd_dt = opt3xchg.lf_evt_ocrd_dt
1036 AND opt3rt.currency = opt3xchg.currency
1037 AND per.group_per_in_ler_id = opt4rt.group_per_in_ler_id
1038 AND opt4rt.pl_id = plrt.pl_id
1039 AND opt4rt.lf_evt_ocrd_dt = plrt.lf_evt_ocrd_dt
1040 AND opt4rt.oipl_id = nvl (temp.oipl4id, - 1)
1041 AND opt4rt.group_pl_id = opt4xchg.group_pl_id
1042 AND opt4rt.lf_evt_ocrd_dt = opt4xchg.lf_evt_ocrd_dt
1043 AND opt4rt.currency = opt4xchg.currency
1044 AND per.assignment_status_type_id = asttl.assignment_status_type_id (+)
1045 AND asttl.language (+) = userenv ('lang')
1046 AND per.business_group_id = bgtl.organization_id (+)
1047 AND bgtl.language (+) = userenv ('lang')
1048 AND per.organization_id = orgtl.organization_id (+)
1049 AND orgtl.language (+) = userenv ('lang')
1050 AND per.location_id = loctl.location_id (+)
1051 AND loctl.language (+) = userenv ('lang')
1052 AND per.job_id = job.job_id (+)
1053 AND job.job_definition_id = jobdef.job_definition_id (+)
1054 AND job.job_id = jobtl.job_id (+)
1055 AND jobtl.language (+) = userenv ('lang')
1056 AND per.position_id = postl.position_id (+)
1057 AND postl.language (+) = userenv ('lang')
1058 AND per.grade_id = grdtl.grade_id (+)
1059 AND grdtl.language (+) = userenv ('lang');
1060
1061 BEGIN
1062 OPEN companalytics;
1063
1064 l_Start_Date := sysdate;
1065
1066 LOOP
1067 FETCH companalytics bulk collect INTO PSA limit 2000;
1068
1069 forall i IN 1..PSA.COUNT
1070 INSERT INTO BEN_CWB_COMP_DETAILS
1071 VALUES Psa(i);
1072
1073 ben_cwb_cd_summary_pkg.Message_log_proc('INSERTED '||SQL%ROWCOUNT || ' Rows in to BEN_CWB_COMP_DETAILS TABLE', 50);
1074
1075 EXIT WHEN companalytics%NOTFOUND;
1076
1077 COMMIT;
1078 END LOOP;
1079
1080 INSERT INTO BEN_CWB_COMP_DETAILS
1081 (GROUP_PER_IN_LER_ID,
1082 PERSON_ID,
1083 GROUP_PL_ID,
1084 LF_EVT_OCRD_DT,
1085 conc_request_id,
1086 Start_date,
1087 end_Date)
1088 VALUES (-1,
1089 -1,
1090 P_GROUP_PL_ID,
1091 P_LF_EVT_OCRD_DT,
1092 Conc_Prog_Id,
1093 l_Start_Date,
1094 SYSDATE);
1095
1096 COMMIT;
1097
1098 CLOSE companalytics;
1099 END ben_cwb_summary_populate;
1100
1101 PROCEDURE Ben_cwb_cd_summary_proc(errbuf OUT nocopy VARCHAR2,
1102 retcode OUT nocopy NUMBER,
1103 p_group_pl_id IN NUMBER DEFAULT NULL,
1104 p_lf_evt_ocrd_dt IN VARCHAR2 DEFAULT NULL,
1105 p_purge IN VARCHAR2 DEFAULT NULL)
1106 IS
1107 l_check_var NUMBER;
1108 table_name_var VARCHAR2(300);
1109 l_param_group_pl_id NUMBER;
1110 l_param_pl_name VARCHAR(300);
1111 l_param_evt_ocrd_dt DATE;
1112 -- Cursor to select the plans based up on the security
1113 CURSOR c_group_plan_list(
1114 p_pl_id NUMBER,
1115 p_lf_evt_ocrd_dt DATE) IS
1116 SELECT DISTINCT pl.pl_id,
1117 pl.name,
1118 pl.lf_evt_ocrd_dt
1119 FROM ben_cwb_pl_dsgn pl
1120 WHERE pl.pl_stat_cd IN ( 'A', 'I' )
1121 AND pl.group_pl_id = pl.pl_id
1122 AND pl.oipl_id = -1
1123 AND ( fnd_profile.Value('HR_CROSS_BUSINESS_GROUP') = 'Y'
1124 OR hr_general.get_business_group_id = pl.business_group_id )
1125 AND ( Nvl(fnd_profile.Value('BEN_CWB_PLAN_SECURITY'), 'N') = 'N'
1126 OR EXISTS (SELECT NULL
1127 FROM ben_resp_plan_mapping mapg
1128 WHERE mapg.responsibility_id = fnd_global.resp_id
1129 AND mapg.pl_id = pl.pl_id) )
1130 AND Nvl(p_pl_id, pl.group_pl_id) = pl.group_pl_id
1131 AND Nvl(p_lf_evt_ocrd_dt, pl.lf_evt_ocrd_dt) = pl.lf_evt_ocrd_dt
1132 ORDER BY pl.pl_id,
1133 pl.lf_evt_ocrd_dt;
1134 BEGIN
1135 SELECT FND_GLOBAL.conc_request_id
1136 INTO conc_prog_id
1137 FROM DUAL;
1138
1139 SELECT s.table_owner
1140 ||'.'
1141 ||Nvl(ev.table_name, s.table_name) table_name
1142 INTO table_name_var
1143 FROM user_synonyms s,
1144 dba_editioning_views ev
1145 WHERE synonym_name = 'BEN_CWB_COMP_DETAILS'
1146 AND ev.owner(+) = s.table_owner
1147 AND ev.view_name(+) = s.table_name;
1148
1149 ben_cwb_cd_summary_pkg.Message_log_proc('-----Concurrent Program Compensation Detail Report Summary Refresh Started-------',10);
1150
1151 ben_cwb_cd_summary_pkg.Message_log_proc('Entering Ben_cwb_cd_summary_proc PROC', 10);
1152 ben_cwb_cd_summary_pkg.Message_log_proc('Parameter - Plan Id : '||Nvl(To_char(P_GROUP_PL_ID), 'Not Mentioned'), 10);
1153 ben_cwb_cd_summary_pkg.Message_log_proc('Parameter - Plan Effective Date : '||Nvl(P_LF_EVT_OCRD_DT, 'Not Mentioned'), 10);
1154 ben_cwb_cd_summary_pkg.Message_log_proc('Parameter - Purge or Not : '||Nvl(P_PURGE, 'Not Mentioned'), 10);
1155
1156 l_check_var := BEN_CWB_CD_SUMMARY_PKG.check_refresh_jobs;
1157
1158 IF fnd_global.conc_request_id = -1
1159 AND l_check_var = 1 THEN
1160 ben_cwb_cd_summary_pkg.Message_log_proc('This Concurrent Program already in Progress, Cannot run this request', 20);
1161
1162 fnd_message.Set_name('BEN', 'BEN_CWB_CD_REFRESH_RUNG');
1163
1164 fnd_message.raise_error;
1165 END IF;
1166
1167 IF ( P_GROUP_PL_ID IS NULL
1168 AND P_LF_EVT_OCRD_DT IS NULL
1169 AND fnd_profile.Value('BEN_CWB_PLAN_SECURITY') <> 'Y' ) THEN
1170 ben_cwb_cd_summary_pkg.Message_log_proc('Plan Security is disabled', 30);
1171
1172 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||table_name_var;
1173
1174 ben_cwb_cd_summary_pkg.Message_log_proc('Purged Table BEN_CWB_COMP_DETAILS', 40);
1175
1176 IF Nvl(Lower(Trim(P_PURGE)), 'n') <> 'y' THEN
1177 BEN_CWB_CD_SUMMARY_PKG.insert_refresh_jobs;
1178
1179 OPEN c_group_plan_list (P_GROUP_PL_ID, To_date(P_LF_EVT_OCRD_DT, 'yyyy/mm/dd HH24:MI:SS'));
1180
1181 LOOP
1182 FETCH c_group_plan_list INTO L_PARAM_GROUP_PL_ID, L_PARAM_PL_NAME, L_PARAM_EVT_OCRD_DT;
1183
1184 EXIT WHEN c_group_plan_list%NOTFOUND;
1185
1186 ben_cwb_cd_summary_pkg.Message_log_proc('Populating for the plan '||L_PARAM_PL_NAME||'-'||L_PARAM_EVT_OCRD_DT, 60);
1187
1188 Ben_cwb_summary_populate(L_PARAM_GROUP_PL_ID, L_PARAM_EVT_OCRD_DT);
1189
1190 COMMIT;
1191 END LOOP;
1192 END IF;
1193
1194 COMMIT;
1195 ELSE
1196 BEN_CWB_CD_SUMMARY_PKG.insert_refresh_jobs;
1197
1198 ben_cwb_cd_summary_pkg.Message_log_proc('Specific Plan is Selected (And/or) Plan Security is Enabled', 20);
1199
1200 OPEN c_group_plan_list (P_GROUP_PL_ID, To_date(P_LF_EVT_OCRD_DT, 'yyyy/mm/dd HH24:MI:SS'));
1201
1202 LOOP
1203 FETCH c_group_plan_list INTO L_PARAM_GROUP_PL_ID, L_PARAM_PL_NAME, L_PARAM_EVT_OCRD_DT;
1204
1205 EXIT WHEN c_group_plan_list%NOTFOUND;
1206
1207 DELETE FROM BEN_CWB_COMP_DETAILS
1208 WHERE group_pl_id = L_PARAM_GROUP_PL_ID
1209 AND LF_EVT_OCRD_DT = L_PARAM_EVT_OCRD_DT;
1210
1211 ben_cwb_cd_summary_pkg.Message_log_proc('Deleted '||SQL%ROWCOUNT ||' Rows for ' ||L_PARAM_PL_NAME||'-'||L_PARAM_EVT_OCRD_DT||' from BEN_CWB_COMP_DETAILS', 40);
1212
1213 COMMIT;
1214 END LOOP;
1215
1216 COMMIT;
1217
1218 CLOSE c_group_plan_list;
1219
1220 IF Nvl(Lower(Trim(P_PURGE)), 'n') <> 'y' THEN
1221 OPEN c_group_plan_list (P_GROUP_PL_ID, To_date(P_LF_EVT_OCRD_DT, 'yyyy/mm/dd HH24:MI:SS'));
1222
1223 LOOP
1224 FETCH c_group_plan_list INTO L_PARAM_GROUP_PL_ID, L_PARAM_PL_NAME, L_PARAM_EVT_OCRD_DT;
1225
1226 EXIT WHEN c_group_plan_list%NOTFOUND;
1227
1228 ben_cwb_cd_summary_pkg.Message_log_proc('Populating for the plan ' ||L_PARAM_PL_NAME||'-'||L_PARAM_EVT_OCRD_DT, 60);
1229
1230 Ben_cwb_summary_populate(L_PARAM_GROUP_PL_ID, L_PARAM_EVT_OCRD_DT);
1231
1232 COMMIT;
1233 END LOOP;
1234
1235 COMMIT;
1236
1237 CLOSE c_group_plan_list;
1238 END IF;
1239 END IF;
1240
1241 BEN_CWB_CD_SUMMARY_PKG.delete_refresh_jobs;
1242
1243 ben_cwb_cd_summary_pkg.Message_log_proc('Gathering in Statistics BEN_CWB_COMP_DETAILS', 80);
1244 fnd_stats.Gather_table_stats('BEN', 'BEN_CWB_COMP_DETAILS');
1245 ben_cwb_cd_summary_pkg.Message_log_proc('Leaving BEN_CWB_COMP_DETAILS_PROC', 95);
1246 ben_cwb_cd_summary_pkg.Message_log_proc('-----Concurrent Program Compensation Detail Report Summary Refresh Finished Successfully-------', 100);
1247
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 errbuf := errbuf
1251 ||SQLERRM;
1252
1253 retcode := '1';
1254
1255 DELETE FROM BEN_CWB_COMP_DETAILS
1256 WHERE CONC_REQUEST_ID = Conc_Prog_Id;
1257
1258 ben_cwb_cd_summary_pkg.Message_log_proc('Deleted '||SQL%ROWCOUNT ||' from summary Table', 90);
1259
1260 COMMIT;
1261
1262 ben_cwb_cd_summary_pkg.Message_log_proc('-----Concurrent Program Compensation Detail Report Summary Refresh Throwed an Exception-------', 100);
1263 ben_cwb_cd_summary_pkg.Message_log_proc('Error in BEN_CWB_COMP_DETAILS_PROC: '||SQLCODE, 100);
1264 ben_cwb_cd_summary_pkg.Message_log_proc(1, 100);
1265 ben_cwb_cd_summary_pkg.Message_log_proc('Error Msg: '||Substr(SQLERRM, 1, 700), 100);
1266
1267 END ben_cwb_cd_summary_proc;
1268
1269
1270
1271 PROCEDURE Message_log_proc(message IN VARCHAR2,
1272 stage NUMBER)
1273 IS
1274 BEGIN
1275 IF Conc_Prog_Id = -1 THEN
1276 hr_utility.Set_location (MESSAGE, STAGE);
1277 ELSE
1278 fnd_file.Put_line(fnd_file.log, MESSAGE);
1279 END IF;
1280 END message_log_proc;
1281 END BEN_CWB_CD_SUMMARY_PKG;