[Home] [Help]
PACKAGE BODY: APPS.PQH_GSP_GRD_STEP_REMOVE
Source
1 Package Body Pqh_Gsp_Grd_Step_Remove As
2 /* $Header: pqgspsde.pkb 120.2 2006/01/10 02:05 ggnanagu noship $ */
3 --
4 --------------------------- get_ovn -----------------------------
5 --
6 g_package VARCHAR2 (30) := 'Pqh_Gsp_Grd_Step_Remove.';
7
8 FUNCTION get_ovn (p_copy_entity_result_id IN NUMBER)
9 RETURN NUMBER
10 IS
11 l_ovn NUMBER;
12
13 CURSOR csr_ovn IS
14 SELECT object_version_number
15 FROM ben_copy_entity_results
16 WHERE copy_entity_result_id = p_copy_entity_result_id;
17 BEGIN
18 hr_utility.set_location ('Entering get_ovn', 10);
19 OPEN csr_ovn;
20 FETCH csr_ovn INTO l_ovn;
21 CLOSE csr_ovn;
22 hr_utility.set_location ('Leaving get_ovn'||l_ovn, 100);
23 RETURN l_ovn;
24 END get_ovn;
25 --
26 --------------------------- get_dml_operation -----------------------------
27 --
28
29 FUNCTION get_dml_operation (p_copy_entity_result_id IN NUMBER)
30 RETURN VARCHAR
31 IS
32 l_dml_operation VARCHAR2 (40);
33
34 CURSOR csr_dml_operation IS
35 SELECT dml_operation
36 FROM ben_copy_entity_results
37 WHERE copy_entity_result_id = p_copy_entity_result_id;
38 BEGIN
39 hr_utility.set_location ('Entering get_dml_operation', 10);
40 OPEN csr_dml_operation;
41 FETCH csr_dml_operation INTO l_dml_operation;
42 CLOSE csr_dml_operation;
43 hr_utility.set_location ('Leaving get_dml_operation '||l_dml_operation, 100);
44 RETURN l_dml_operation;
45 END get_dml_operation;
46
47 --
48 --------------------------- delete_rec -----------------------------
49 --
50
51 PROCEDURE delete_rec (
52 p_copy_entity_result_id IN NUMBER,
53 p_effective_date IN DATE,
54 p_object_version_number in NUMBER default null
55 )
56 IS
57 l_ovn NUMBER;
58 BEGIN
59 hr_utility.set_location ('Entering delete_rec', 10);
60 if p_object_version_number is null then
61 l_ovn := get_ovn (p_copy_entity_result_id => p_copy_entity_result_id);
62 else
63 l_ovn := p_object_version_number;
64 end if;
65 ben_copy_entity_results_api.delete_copy_entity_results (
66 p_copy_entity_result_id => p_copy_entity_result_id,
67 p_effective_date => p_effective_date,
68 p_object_version_number => l_ovn
69 );
70 hr_utility.set_location ('Purged the record Sucessfully... :', 90);
71 hr_utility.set_location ('Leaving delete_rec', 100);
72 EXCEPTION
73 WHEN OTHERS THEN
74 hr_utility.set_location ('Errors in delete_rec ...', 100);
75 raise;
76 END delete_rec;
77
78 --
79 --------------------------- update_rec -----------------------------
80 --
81
82 PROCEDURE update_rec (
83 p_copy_entity_result_id IN NUMBER,
84 p_effective_date IN DATE
85 )
86 IS
87 l_ovn NUMBER;
88 BEGIN
89 hr_utility.set_location ('Entering update_rec', 10);
90 l_ovn := get_ovn (p_copy_entity_result_id => p_copy_entity_result_id);
91 ben_copy_entity_results_api.update_copy_entity_results (
92 p_copy_entity_result_id => p_copy_entity_result_id,
93 p_effective_date => p_effective_date,
94 p_information104 => 'UNLINK',
95 p_object_version_number => l_ovn,
96 p_information323 => NULL
97 );
98 hr_utility.set_location ('Marked for Deletion Sucessfully... :', 90);
99 hr_utility.set_location ('Leaving update_rec', 100);
100 EXCEPTION
101 WHEN OTHERS THEN
102 hr_utility.set_location ('Errors in update_rec ...', 150);
103 END update_rec;
104 --
105 --------------------------- purge_pay_scale-----------------------------
106 --
107
108 PROCEDURE purge_pay_scale (
109 p_opt_result_id IN NUMBER,
110 p_effective_date IN DATE,
111 p_copy_entity_txn_id IN NUMBER
112 )
113 IS
114 l_scale_cer_id NUMBER;
115 l_cet_id NUMBER;
116 l_count NUMBER;
117
118 CURSOR csr_scale_cer_id IS
119 SELECT opt.copy_entity_txn_id, opt.information256 -- Pay Scale Cer Id
120 FROM ben_copy_entity_results opt
121 WHERE opt.copy_entity_result_id = p_opt_result_id
122 AND opt.table_alias = 'OPT'
123 AND NVL (opt.information104, 'PPP') <> 'UNLINK'
124 AND opt.copy_entity_txn_id = p_copy_entity_txn_id;
125
126 -- Count the Number of Options attached to Pay Scale
127 CURSOR csr_num_opts (l_scale_cer_id NUMBER) IS
128 SELECT COUNT (opt.copy_entity_result_id)
129 FROM ben_copy_entity_results opt
130 WHERE opt.information256 = l_scale_cer_id
131 AND opt.copy_entity_txn_id = l_cet_id
132 AND opt.table_alias = 'OPT';
133
134 --And Nvl(opt.Information104,'PPP') <> 'UNLINK';
135
136 CURSOR csr_plip_cer_id (p_scale_cer_id IN NUMBER) IS
137 SELECT copy_entity_result_id
138 FROM ben_copy_entity_results
139 WHERE information258 = p_scale_cer_id
140 AND copy_entity_txn_id = l_cet_id;
141 BEGIN
142 hr_utility.set_location ('Entering purge_pay_scale', 10);
143 -- Get Pay Scale CER Id;
144 OPEN csr_scale_cer_id;
145 FETCH csr_scale_cer_id INTO l_cet_id,l_scale_cer_id;
146 CLOSE csr_scale_cer_id;
147 hr_utility.set_location ('Pay Scale CER ID ' || l_scale_cer_id, 10);
148 hr_utility.set_location ('cet ID ' || l_cet_id, 10);
149 IF (l_scale_cer_id IS NOT NULL and l_cet_id is not null) THEN
150 OPEN csr_num_opts (l_scale_cer_id);
151 FETCH csr_num_opts INTO l_count;
152 CLOSE csr_num_opts;
153 IF (l_count = 1) THEN
154 delete_rec ( p_copy_entity_result_id => l_scale_cer_id,
155 p_effective_date => p_effective_date);
156 FOR rec_plip_cer_id IN csr_plip_cer_id (l_scale_cer_id) LOOP
157 hr_utility.set_location ( 'Plip Cer Id ' || rec_plip_cer_id.copy_entity_result_id, 10);
158 UPDATE ben_copy_entity_results
159 SET information98 = NULL,
160 information255 = NULL,
161 information258 = NULL,
162 information259 = NULL,
163 information262 = NULL
164 WHERE copy_entity_result_id = rec_plip_cer_id.copy_entity_result_id;
165 hr_utility.set_location ('Plip updated successfully', 40);
166 END LOOP;
167 END IF;
168 else
169 hr_utility.set_location ('null data found ', 40);
170 END IF;
171 hr_utility.set_location ('Leaving purge_pay_scale', 100);
172 EXCEPTION
173 WHEN OTHERS THEN
174 hr_utility.set_location ('Errors in purge_pay_scale ...', 100);
175 END purge_pay_scale;
176 --
177 --------------------------- purge_opt_abr_hrrate_crrate -----------------------------
178 --
179 -- To purge
180 -- Stdandard Rates of Grade Step i.e ABR, HRRATE
181 -- Criteria Rates of Grade Step i.e CRRATE
182
183 PROCEDURE purge_opt_abr_hrrate_crrate ( p_opt_result_id IN NUMBER,
184 p_copy_entity_txn_id IN NUMBER,
185 p_effective_date IN DATE) IS
186 -- Get Standard Rates i.e HRRATE, ABR Attached to OPT
187 CURSOR csr_std_rates IS
188 SELECT stdrate.copy_entity_result_id, stdrate.object_version_number
189 FROM ben_copy_entity_results stdrate
190 WHERE stdrate.table_alias IN ('HRRATE', 'ABR')
191 AND stdrate.information278 = p_opt_result_id
192 AND stdrate.copy_entity_txn_id = p_copy_entity_txn_id;
193
194 -- Get Criteria Rate GRRATE
195 CURSOR csr_crrate IS
196 SELECT crrate.copy_entity_result_id, crrate.object_version_number
197 FROM ben_copy_entity_results crrate
198 WHERE crrate.copy_entity_txn_id = p_copy_entity_txn_id
199 AND crrate.table_alias = 'CRRATE'
200 AND crrate.information169 = p_opt_result_id;
201 BEGIN
202 hr_utility.set_location ('Entering purge_opt_abr_hrrate_crrate', 10);
203 -- purge HRRATE, ABR Records
204 FOR rec_std_rates IN csr_std_rates LOOP
205 delete_rec ( p_copy_entity_result_id => rec_std_rates.copy_entity_result_id,
206 p_effective_date => p_effective_date,
207 p_object_version_number => rec_std_rates.object_version_number);
208 END LOOP;
209
210 hr_utility.set_location ('Purged HRRATE, ABR Recs sucessully ', 40);
211
212 -- purge CRRATE Records
213 FOR rec_crrates IN csr_crrate LOOP
214 delete_rec ( p_copy_entity_result_id => rec_crrates.copy_entity_result_id,
215 p_effective_date => p_effective_date,
216 p_object_version_number => rec_crrates.object_version_number
217 );
218 END LOOP;
219
220 hr_utility.set_location ('Purged CRRATE Recs sucessully ', 55);
221 hr_utility.set_location ('Leaving purge_opt_abr_hrrate_crrate', 100);
222 EXCEPTION
223 WHEN OTHERS THEN
224 hr_utility.set_location ('purge_opt_abr_hrrate_crrate ', 110);
225 END purge_opt_abr_hrrate_crrate;
226
227 --
228 --------------------------- remove_opt -----------------------------
229 --
230 -- To Remove OPT Rec
231 -- if opt.dml_operation = 'INSERT' then
232 -- 1) Purge Standard Rates i.e ABR, HRRATE
233 -- 2) Purge Crit Rates i.e CRRATE
234 -- 3) Purge Pay Scale if the OPT is the last rec
235 -- 4) Purge OPT Rec
236 -- else if opt.dml_operation in ('COPIED','UPD_INS','UPDATE')
237 -- 1) copt.information104 = UNLINK
238
239 PROCEDURE remove_opt (
240 p_copy_entity_txn_id IN NUMBER,
241 p_copy_entity_result_id IN NUMBER,
242 p_effective_date IN DATE
243 )
244 IS
245 l_point_or_step VARCHAR2 (20);
246 l_dml_operation VARCHAR2 (40);
247 BEGIN
248 hr_utility.set_location ('Entering remove_opt', 100);
249 l_point_or_step :=
250 pqh_gsp_utility.use_point_or_step (
251 p_copy_entity_txn_id => p_copy_entity_txn_id
252 );
253 l_dml_operation :=
254 get_dml_operation (
255 p_copy_entity_result_id => p_copy_entity_result_id
256 );
257 hr_utility.set_location ('l_point_or_step :' || l_point_or_step, 40);
258 hr_utility.set_location ('DML Operation :' || l_dml_operation, 45);
259
260 -- If GL Using Direct Steps
261 -- if l_point_or_step= 'STEP' Then
262
263 IF l_dml_operation = 'INSERT' THEN
264 -- 1) Purge ABR,HRRATE, CRRATE
265 hr_utility.set_location ( 'Calling purge_opt_abr_hrrate_crrate... :', 30);
266 pqh_gsp_grd_step_remove.purge_opt_abr_hrrate_crrate (
267 p_opt_result_id => p_copy_entity_result_id,
268 p_copy_entity_txn_id => p_copy_entity_txn_id,
269 p_effective_date => p_effective_date
270 );
271 -- 2) Get The Number of Options attached to this Pay Scale Cer Id
272 -- suppose only one then purge Pay Scale record.
273 hr_utility.set_location ('Calling purge_pay_scale... :', 40);
274
275 IF l_point_or_step = 'STEP'
276 THEN
277 pqh_gsp_grd_step_remove.purge_pay_scale (
278 p_opt_result_id => p_copy_entity_result_id,
279 p_copy_entity_txn_id => p_copy_entity_txn_id,
280 p_effective_date => p_effective_date
281 );
282 END IF; -- end of STEP
283 -- 3) purge OPT Rec
284 hr_utility.set_location ('Purge OPT ... :', 50);
285 delete_rec (
286 p_copy_entity_result_id => p_copy_entity_result_id,
287 p_effective_date => p_effective_date
288 );
289 ELSE -- l_dml_operation is UPDATE/UPD_INS/COPIED
290
291
292 pqh_gsp_grd_step_remove.unlink_opt_abr_hrrate_crrate (
293 p_opt_result_id => p_copy_entity_result_id,
294 p_copy_entity_txn_id => p_copy_entity_txn_id,
295 p_effective_date => p_effective_date
296 );
297 update_rec (
298 p_copy_entity_result_id => p_copy_entity_result_id,
299 p_effective_date => p_effective_date
300 );
301 END IF; -- end of dml_operation
302 -- end if; -- end of STEP
303 hr_utility.set_location ('Leaving remove_opt', 100);
304 END remove_opt;
305 --
306 ---------------------------remove_elig_profile-----------------------------
307 --
308 -- To Remove (Purage/Mark for Deletion)Eligibility Profiles
309
310
311 PROCEDURE remove_elig_profile (
312 p_copy_entity_txn_id IN NUMBER,
313 p_copy_entity_result_id IN NUMBER
314 )
315 IS
316 l_ovn NUMBER;
317 l_exits VARCHAR2 (40);
318 l_elp_cer_id NUMBER;
319
320 CURSOR csr_elp_cer_ids IS
321 SELECT elp.copy_entity_result_id
322 FROM ben_copy_entity_results elp
323 WHERE elp.copy_entity_txn_id = p_copy_entity_txn_id
324 AND elp.gs_parent_entity_result_id = p_copy_entity_result_id
325 AND elp.table_alias = 'ELP';
326 BEGIN
327 hr_utility.set_location ('Entering remove_elig_profile', 10);
328 -- Check Eligibility Profiles Exists for OIPL Rec
329 l_exits := pqh_gsp_utility.chk_profile_exists ( p_copy_entity_result_id => p_copy_entity_result_id,
330 p_copy_entity_txn_id => p_copy_entity_txn_id);
331
332 hr_utility.set_location ( 'Eligibility Profiles Exists (Y/N) :' || l_exits, 50);
333 IF (l_exits = 'Y') THEN
334 FOR elp_recs IN csr_elp_cer_ids LOOP
335 pqh_gsp_prgrules.delete_eligibility (
336 p_copy_entity_txn_id => p_copy_entity_txn_id,
337 p_copy_entity_result_id => elp_recs.copy_entity_result_id
338 );
339 END LOOP;
340 END IF;
341
342 hr_utility.set_location ('Removed Elig Profiles sucessfully ... ', 60);
343 hr_utility.set_location ('Leaving remove_elig_profile', 100);
344 EXCEPTION
345 WHEN OTHERS THEN
346 hr_utility.set_location ( 'Error in Removing Elig Profiles ... ', 100);
347 END remove_elig_profile;
348 --
349 ---------------------------remove_oipl_STEP_flavour -----------------------------
350 --
351 -- To Remove OIPL Rec in STEP Flavour
352 /*
353
354 if use_prg_points = 'STEP'
355 if oipl.dml_operation = 'INSERT'
356 1) Purge Rates Std Rates : ABR,HRRATE,GSRATE Crit Rates : CRRATE,GRRATE
357 2) Purge Elig Profile call procedure remove_elig_profile
358 3) purge OIPL
359
360 else if opt.dml_operation in ('COPIED','UPD_INS','UPDATE')
361 1) Mark for deletion call procedure remove_elig_profile
362 2) oipl.information104 = UNLINK
363
364 */
365 PROCEDURE remove_oipl_step_flavour (
366 p_copy_entity_txn_id IN NUMBER,
367 p_copy_entity_result_id IN NUMBER,
368 p_effective_date IN DATE,
369 p_remove_opt IN VARCHAR2 DEFAULT 'Y'
370 )
371 IS
372 l_point_or_step VARCHAR2 (20);
373 l_dml_operation VARCHAR2 (40);
374 l_opt_result_id NUMBER;
375
376 CURSOR csr_opt_result_id IS
377 SELECT oipl.information262
378 FROM ben_copy_entity_results oipl
379 WHERE oipl.copy_entity_txn_id = p_copy_entity_txn_id
383 hr_utility.set_location ('Entering remove_oipl_STEP_flavour', 10);
380 AND oipl.table_alias = 'COP'
381 AND oipl.copy_entity_result_id = p_copy_entity_result_id;
382 BEGIN
384 l_point_or_step := pqh_gsp_utility.use_point_or_step ( p_copy_entity_txn_id => p_copy_entity_txn_id);
385 l_dml_operation := get_dml_operation ( p_copy_entity_result_id => p_copy_entity_result_id);
386
387 -- Get OPT Cer ID
388 OPEN csr_opt_result_id;
389 FETCH csr_opt_result_id INTO l_opt_result_id;
390 CLOSE csr_opt_result_id;
391 hr_utility.set_location ('l_point_or_step :' || l_point_or_step, 40);
392 hr_utility.set_location ('DML Operation :' || l_dml_operation, 45);
393 hr_utility.set_location ('OPT Result Id:' || l_opt_result_id, 46);
394 hr_utility.set_location ('remove_opt value:' || p_remove_opt, 46);
395
396 IF l_point_or_step = 'STEP' THEN
397 IF l_dml_operation = 'INSERT' OR l_dml_operation ='COPIED' OR l_dml_operation = 'UPD_INS' THEN
398 -- 1) Call Pqh_Gsp_Grd_Step_Remove.remove_opt
399 IF p_remove_opt = 'Y' THEN
400 hr_utility.set_location ('Calling remove_opt... :', 60);
401 pqh_gsp_grd_step_remove.remove_opt (
402 p_copy_entity_txn_id => p_copy_entity_txn_id,
403 p_copy_entity_result_id => l_opt_result_id,
404 p_effective_date => p_effective_date);
405 ELSE
406 hr_utility.set_location ('Not calling remove_opt ', 60);
407 END IF;
408
409 -- 2) Purge Elig Profile call procedure remove_elig_profile
410 hr_utility.set_location ('Calling remove_elig_profile... :', 70);
411 pqh_gsp_grd_step_remove.remove_elig_profile (
412 p_copy_entity_txn_id => p_copy_entity_txn_id,
413 p_copy_entity_result_id => p_copy_entity_result_id
414 );
415 -- 3) purge OIPL
416 hr_utility.set_location ('Purge OIPL... :', 80);
417 delete_rec (
418 p_copy_entity_result_id => p_copy_entity_result_id,
419 p_effective_date => p_effective_date
420 );
421 ELSE -- dml_operation = COPIED/UPD_INS/UPDATE
422 -- 1) Mark for deletion call procedure remove_elig_profile
423 pqh_gsp_grd_step_remove.remove_elig_profile (
424 p_copy_entity_txn_id => p_copy_entity_txn_id,
425 p_copy_entity_result_id => p_copy_entity_result_id
426 );
427
428 -- UNLINK OPT
429 IF p_remove_opt = 'Y' Then
430 pqh_gsp_grd_step_remove.remove_opt (
431 p_copy_entity_txn_id => p_copy_entity_txn_id,
432 p_copy_entity_result_id => l_opt_result_id,
433 p_effective_date => p_effective_date);
434 end if;
435
436 -- 2) oipl.information104 = UNLINK
437 update_rec (
438 p_copy_entity_result_id => p_copy_entity_result_id,
439 p_effective_date => p_effective_date
440 );
441 END IF; -- end of dml_operation
442 END IF; -- end of STEP
443 hr_utility.set_location ('Leaving remove_oipl_STEP_flavour', 100);
444 END remove_oipl_step_flavour;
445 --
446 --------------------------- remove_oipl_POINT_flavour-----------------------------
447 --
448 -- To Remove OIPL Rec in POINT Flavour
449 /*
450 if use_prg_points = 'POINT'
451 if oipl.dml_operation = 'INSERT'
452 1) Purge Standard Rates and Criteria Rates
453 1.a) Purge Standard Rates i.e ABR, HRRATE
454 1.b) Purge Criteria Rates i.e CRRATE
455 2) Purge Elig Profile call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
456 3) purge OIPL
457 else if opt.dml_operation in ('COPIED','UPD_INS','UPDATE')
458 1) Mark for deletion call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
459 2) oipl.information104 = UNLINK
460 */
461 PROCEDURE remove_oipl_point_flavour (
462 p_copy_entity_txn_id IN NUMBER,
463 p_copy_entity_result_id IN NUMBER,
464 p_effective_date IN DATE
465 )
466 IS
467 l_point_or_step VARCHAR2 (20);
468 l_dml_operation VARCHAR2 (40);
469 l_opt_result_id NUMBER;
470
471 CURSOR csr_opt_result_id IS
472 SELECT oipl.information262
473 FROM ben_copy_entity_results oipl
474 WHERE oipl.copy_entity_txn_id = p_copy_entity_txn_id
475 AND oipl.table_alias = 'COP'
476 AND oipl.copy_entity_result_id = p_copy_entity_result_id;
477 BEGIN
478 hr_utility.set_location ('Entering remove_oipl_POINT_flavour', 10);
479 l_point_or_step := pqh_gsp_utility.use_point_or_step ( p_copy_entity_txn_id => p_copy_entity_txn_id);
480 l_dml_operation := get_dml_operation ( p_copy_entity_result_id => p_copy_entity_result_id);
481
482 -- Get OPT Cer ID
483 OPEN csr_opt_result_id;
484 FETCH csr_opt_result_id INTO l_opt_result_id;
485 CLOSE csr_opt_result_id;
486 hr_utility.set_location ('l_point_or_step :' || l_point_or_step, 40);
487 hr_utility.set_location ('DML Operation :' || l_dml_operation, 45);
491 IF l_dml_operation = 'INSERT' OR l_dml_operation ='COPIED' OR l_dml_operation = 'UPD_INS' THEN
488 hr_utility.set_location ('OPT Result Id:' || l_opt_result_id, 46);
489
490 IF l_point_or_step = 'POINT' THEN
492 -- 1) Purge Standard Rates : ABR, HRRATE and Criteria Rates : CRRATE
493 /* hr_utility.set_location ( 'Calling purge_opt_abr_hrrate_crrate ... :', 70);
494 pqh_gsp_grd_step_remove.purge_opt_abr_hrrate_crrate (
495 p_opt_result_id => l_opt_result_id,
496 p_copy_entity_txn_id => p_copy_entity_txn_id,
497 p_effective_date => p_effective_date
498 );
499 */
500 -- 2) Purge Elig Profile call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
501 hr_utility.set_location ( 'Calling Pqh_Gsp_Grd_Step_Remove.remove_elig_profile... :', 75);
502 pqh_gsp_grd_step_remove.remove_elig_profile (
503 p_copy_entity_txn_id => p_copy_entity_txn_id,
504 p_copy_entity_result_id => p_copy_entity_result_id
505 );
506 -- 3) purge OIPL
507 hr_utility.set_location ('Purge OIPL ... :', 80);
508 delete_rec (
509 p_copy_entity_result_id => p_copy_entity_result_id,
510 p_effective_date => p_effective_date
511 );
512 ELSE -- dml_operation = COPIED/UPD_INS/UPDATE
513 -- 1) Mark for deletion call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
514 pqh_gsp_grd_step_remove.remove_elig_profile (
515 p_copy_entity_txn_id => p_copy_entity_txn_id,
516 p_copy_entity_result_id => p_copy_entity_result_id
517 );
518 -- 2) oipl.information104 = UNLINK
519 update_rec (
520 p_copy_entity_result_id => p_copy_entity_result_id,
521 p_effective_date => p_effective_date
522 );
523 END IF; -- end of dml_operation
524 END IF; -- end of POINT
525 hr_utility.set_location ('Leaving remove_oipl_POINT_flavour', 100);
526 END remove_oipl_point_flavour;
527 --
528 --------------------------- remove_oipl -----------------------------
529 --
530 -- To Remove OIPL Rec
531
532 PROCEDURE remove_oipl (
533 p_copy_entity_txn_id IN NUMBER,
534 p_copy_entity_result_id IN NUMBER,
535 p_effective_date IN DATE,
536 p_remove_opt IN VARCHAR2 DEFAULT 'Y'
537 )
538 IS
539 l_which_flavour VARCHAR2 (20);
540 BEGIN
541 hr_multi_message.enable_message_list;
542 hr_utility.set_location ('Entering remove_oipl', 10);
543 l_which_flavour := pqh_gsp_utility.use_point_or_step ( p_copy_entity_txn_id => p_copy_entity_txn_id);
544 hr_utility.set_location ('POINT/STEP :' || l_which_flavour, 30);
545 IF (l_which_flavour = 'POINT') THEN
546 pqh_gsp_grd_step_remove.remove_oipl_point_flavour (
547 p_copy_entity_txn_id => p_copy_entity_txn_id,
548 p_copy_entity_result_id => p_copy_entity_result_id,
549 p_effective_date => p_effective_date
550 );
551 END IF;
552 IF (l_which_flavour = 'STEP') THEN
553 pqh_gsp_grd_step_remove.remove_oipl_step_flavour (
554 p_copy_entity_txn_id => p_copy_entity_txn_id,
555 p_copy_entity_result_id => p_copy_entity_result_id,
556 p_effective_date => p_effective_date,
557 p_remove_opt => p_remove_opt
558 );
559 END IF;
560 hr_utility.set_location ('Leaving remove_oipl', 100);
561 Exception
562 when others then
563 hr_utility.set_location ('Caught an Exception', 100);
564 hr_multi_message.add;
565 END remove_oipl;
566 --
567 --------------------------- remove_plip -----------------------------
568 --
569 -- To Remove PLIP Rec
570
571 PROCEDURE remove_plip (
572 p_copy_entity_txn_id IN NUMBER,
573 p_copy_entity_result_id IN NUMBER,
574 p_effective_date IN DATE
575 )
576 IS
577 l_which_flavour VARCHAR2 (20);
578 l_dml_operation VARCHAR2 (20);
579 l_pln_cer_id NUMBER;
580
581 CURSOR csr_plan_result_id IS
582 SELECT pln.copy_entity_result_id
583 FROM ben_copy_entity_results pln
584 WHERE pln.gs_mirror_src_entity_result_id = p_copy_entity_result_id
585 AND pln.copy_entity_txn_id = p_copy_entity_txn_id
586 AND pln.table_alias = 'PLN';
587
588 CURSOR csr_oipl_ids IS
589 SELECT oipl.copy_entity_result_id
590 FROM ben_copy_entity_results oipl
591 WHERE oipl.gs_parent_entity_result_id = p_copy_entity_result_id
592 AND oipl.table_alias = 'COP'
593 AND oipl.copy_entity_txn_id = p_copy_entity_txn_id;
594
595 BEGIN
596 hr_utility.set_location ('Entering remove_plip', 10);
597
598 l_which_flavour := pqh_gsp_utility.use_point_or_step ( p_copy_entity_txn_id => p_copy_entity_txn_id);
599 l_dml_operation := get_dml_operation ( p_copy_entity_result_id => p_copy_entity_result_id);
600 hr_utility.set_location ('POINT/STEP :' || l_which_flavour, 30);
601 hr_utility.set_location ('DML Operation :' || l_dml_operation, 45);
602
603 -- Remove Rates, Elig Profiles attahced to OIPL
604 -- Remove OIPL
605 FOR rec_oipls IN csr_oipl_ids LOOP
606 IF (l_which_flavour = 'POINT') THEN
607 pqh_gsp_grd_step_remove.remove_oipl_point_flavour (
608 p_copy_entity_txn_id => p_copy_entity_txn_id,
609 p_copy_entity_result_id => rec_oipls.copy_entity_result_id,
610 p_effective_date => p_effective_date
611 );
612 END IF;
613
614 IF (l_which_flavour = 'STEP') THEN
615 pqh_gsp_grd_step_remove.remove_oipl_step_flavour (
616 p_copy_entity_txn_id => p_copy_entity_txn_id,
617 p_copy_entity_result_id => rec_oipls.copy_entity_result_id,
618 p_effective_date => p_effective_date
619 );
620 END IF;
621 END LOOP;
622
623 IF l_dml_operation = 'INSERT' THEN
624 -- 1) Purge Elig Profile call procedure remove_elig_profile
625 hr_utility.set_location ('Calling remove_elig_profile... :', 70);
626 pqh_gsp_grd_step_remove.remove_elig_profile (
627 p_copy_entity_txn_id => p_copy_entity_txn_id,
628 p_copy_entity_result_id => p_copy_entity_result_id
629 );
630
631 -- 2) Purge PLIP Rec
632 OPEN csr_plan_result_id;
633 FETCH csr_plan_result_id INTO l_pln_cer_id;
634 IF csr_plan_result_id%FOUND THEN
635 update ben_copy_entity_results
636 set gs_mirror_src_entity_result_id = null
637 where copy_entity_result_id = l_pln_cer_id;
638 END IF;
639 CLOSE csr_plan_result_id;
640
641 delete_rec ( p_copy_entity_result_id => p_copy_entity_result_id,
642 p_effective_date => p_effective_date);
643
644 hr_utility.set_location ('Purged PLIP Rec Sucessfully... :', 85);
645 ELSE -- dml_operation = UPDATE/COPIED/UPD_INS
646 -- Mark PLIP for Deletion
647 update_rec (
648 p_copy_entity_result_id => p_copy_entity_result_id,
649 p_effective_date => p_effective_date
650 );
651 hr_utility.set_location ('Marked for Deletion PLIP Rec Sucessfully... :',90);
652 END IF; -- DML Operation
653 hr_utility.set_location ('Leaving remove_plip', 100);
654 END remove_plip;
655 PROCEDURE unlink_opt_abr_hrrate_crrate (
656 p_opt_result_id IN NUMBER,
657 p_copy_entity_txn_id IN NUMBER,
658 p_effective_date IN DATE
659 )
660 IS
661 l_proc VARCHAR2 (72) := g_package || 'purge_opt_abr_hrrate_crrate ';
662
663 -- Get Standard Rates i.e HRRATE, ABR Attached to OPT
664 CURSOR csr_std_rates
665 IS
666 SELECT stdrate.copy_entity_result_id, stdrate.object_version_number
667 FROM ben_copy_entity_results stdrate
668 WHERE stdrate.table_alias IN ('HRRATE', 'ABR')
672 -- Get Criteria Rate GRRATE
669 AND stdrate.information278 = p_opt_result_id
670 AND stdrate.copy_entity_txn_id = p_copy_entity_txn_id;
671
673 CURSOR csr_crrate
674 IS
675 SELECT crrate.copy_entity_result_id, crrate.object_version_number
676 FROM ben_copy_entity_results crrate
677 WHERE crrate.copy_entity_txn_id = p_copy_entity_txn_id
678 AND crrate.table_alias = 'CRRATE'
679 AND crrate.information169 = p_opt_result_id;
680 BEGIN
681 hr_utility.set_location ('Entering ' || l_proc, 10);
682
683 -- purge HRRATE, ABR Records
684 FOR rec_std_rates IN csr_std_rates
685 LOOP
686 update_rec (
687 p_copy_entity_result_id => rec_std_rates.copy_entity_result_id,
688 p_effective_date => p_effective_date
689 );
690 END LOOP;
691
692 hr_utility.set_location ('Purged HRRATE, ABR Recs sucessully ', 40);
693
694 -- purge CRRATE Records
695 FOR rec_crrates IN csr_crrate
696 LOOP
697 update_rec (
698 p_copy_entity_result_id => rec_crrates.copy_entity_result_id,
699 p_effective_date => p_effective_date
700 );
701 END LOOP;
702
703 hr_utility.set_location ('Purged CRRATE Recs sucessully ', 55);
704 hr_utility.set_location ('Leaving ' || l_proc, 100);
705 EXCEPTION
706 WHEN OTHERS
707 THEN
708 hr_utility.set_location ('purge_opt_abr_hrrate_crrate ', 110);
709 END unlink_opt_abr_hrrate_crrate;
710
711 END pqh_gsp_grd_step_remove;