[Home] [Help]
PACKAGE BODY: APPS.PQH_GSP_GRD_STEP_REMOVE
Source
4 --------------------------- get_ovn -----------------------------
1 Package Body Pqh_Gsp_Grd_Step_Remove As
2 /* $Header: pqgspsde.pkb 120.2.12010000.2 2009/04/10 12:06:11 lbodired ship $ */
3 --
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;
107
104 --
105 --------------------------- purge_pay_scale-----------------------------
106 --
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);
228 --------------------------- remove_opt -----------------------------
225 END purge_opt_abr_hrrate_crrate;
226
227 --
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
360 else if opt.dml_operation in ('COPIED','UPD_INS','UPDATE')
357 2) Purge Elig Profile call procedure remove_elig_profile
358 3) purge OIPL
359
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
380 AND oipl.table_alias = 'COP'
381 AND oipl.copy_entity_result_id = p_copy_entity_result_id;
382 BEGIN
383 hr_utility.set_location ('Entering remove_oipl_STEP_flavour', 10);
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
398 /*IF l_dml_operation = 'INSERT' OR l_dml_operation ='COPIED' OR l_dml_operation = 'UPD_INS' THEN */
399 --bug#8392638
400 -- To divert control of execution to the else part during create mode of the grade ladder.
401 -- In create mode dml_operation of the points is COPIED
402
403 IF l_dml_operation = 'INSERT' OR l_dml_operation = 'UPD_INS' THEN
404 -- 1) Call Pqh_Gsp_Grd_Step_Remove.remove_opt
405 IF p_remove_opt = 'Y' THEN
406 hr_utility.set_location ('Calling remove_opt... :', 60);
407 pqh_gsp_grd_step_remove.remove_opt (
408 p_copy_entity_txn_id => p_copy_entity_txn_id,
409 p_copy_entity_result_id => l_opt_result_id,
410 p_effective_date => p_effective_date);
411 ELSE
412 hr_utility.set_location ('Not calling remove_opt ', 60);
413 END IF;
414
415 -- 2) Purge Elig Profile call procedure remove_elig_profile
416 hr_utility.set_location ('Calling remove_elig_profile... :', 70);
417 pqh_gsp_grd_step_remove.remove_elig_profile (
418 p_copy_entity_txn_id => p_copy_entity_txn_id,
419 p_copy_entity_result_id => p_copy_entity_result_id
420 );
421 -- 3) purge OIPL
422 hr_utility.set_location ('Purge OIPL... :', 80);
423 delete_rec (
424 p_copy_entity_result_id => p_copy_entity_result_id,
425 p_effective_date => p_effective_date
426 );
427 ELSE -- dml_operation = COPIED/UPD_INS/UPDATE
428 -- 1) Mark for deletion call procedure remove_elig_profile
429 pqh_gsp_grd_step_remove.remove_elig_profile (
430 p_copy_entity_txn_id => p_copy_entity_txn_id,
431 p_copy_entity_result_id => p_copy_entity_result_id
432 );
433
434 -- UNLINK OPT
435 IF p_remove_opt = 'Y' Then
436 pqh_gsp_grd_step_remove.remove_opt (
437 p_copy_entity_txn_id => p_copy_entity_txn_id,
438 p_copy_entity_result_id => l_opt_result_id,
439 p_effective_date => p_effective_date);
440 end if;
441
442 -- 2) oipl.information104 = UNLINK
443 update_rec (
444 p_copy_entity_result_id => p_copy_entity_result_id,
445 p_effective_date => p_effective_date
446 );
447 END IF; -- end of dml_operation
448 END IF; -- end of STEP
449 hr_utility.set_location ('Leaving remove_oipl_STEP_flavour', 100);
450 END remove_oipl_step_flavour;
451 --
452 --------------------------- remove_oipl_POINT_flavour-----------------------------
453 --
454 -- To Remove OIPL Rec in POINT Flavour
455 /*
456 if use_prg_points = 'POINT'
457 if oipl.dml_operation = 'INSERT'
458 1) Purge Standard Rates and Criteria Rates
459 1.a) Purge Standard Rates i.e ABR, HRRATE
460 1.b) Purge Criteria Rates i.e CRRATE
461 2) Purge Elig Profile call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
462 3) purge OIPL
463 else if opt.dml_operation in ('COPIED','UPD_INS','UPDATE')
464 1) Mark for deletion call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
465 2) oipl.information104 = UNLINK
466 */
467 PROCEDURE remove_oipl_point_flavour (
468 p_copy_entity_txn_id IN NUMBER,
469 p_copy_entity_result_id IN NUMBER,
470 p_effective_date IN DATE
471 )
472 IS
473 l_point_or_step VARCHAR2 (20);
474 l_dml_operation VARCHAR2 (40);
478 SELECT oipl.information262
475 l_opt_result_id NUMBER;
476
477 CURSOR csr_opt_result_id IS
479 FROM ben_copy_entity_results oipl
480 WHERE oipl.copy_entity_txn_id = p_copy_entity_txn_id
481 AND oipl.table_alias = 'COP'
482 AND oipl.copy_entity_result_id = p_copy_entity_result_id;
483 BEGIN
484 hr_utility.set_location ('Entering remove_oipl_POINT_flavour', 10);
485 l_point_or_step := pqh_gsp_utility.use_point_or_step ( p_copy_entity_txn_id => p_copy_entity_txn_id);
486 l_dml_operation := get_dml_operation ( p_copy_entity_result_id => p_copy_entity_result_id);
487
488 -- Get OPT Cer ID
489 OPEN csr_opt_result_id;
490 FETCH csr_opt_result_id INTO l_opt_result_id;
491 CLOSE csr_opt_result_id;
492 hr_utility.set_location ('l_point_or_step :' || l_point_or_step, 40);
493 hr_utility.set_location ('DML Operation :' || l_dml_operation, 45);
494 hr_utility.set_location ('OPT Result Id:' || l_opt_result_id, 46);
495
496 IF l_point_or_step = 'POINT' THEN
497
498 /* IF l_dml_operation = 'INSERT' OR l_dml_operation ='COPIED' OR l_dml_operation = 'UPD_INS' THEN */
499 --bug#8392638
500 -- To divert control of execution to the else part during create mode of the grade ladder.
501 -- In create mode dml_operation of the points is COPIED
502
503 IF l_dml_operation = 'INSERT' OR l_dml_operation = 'UPD_INS' THEN
504 -- 1) Purge Standard Rates : ABR, HRRATE and Criteria Rates : CRRATE
505 /* hr_utility.set_location ( 'Calling purge_opt_abr_hrrate_crrate ... :', 70);
506 pqh_gsp_grd_step_remove.purge_opt_abr_hrrate_crrate (
507 p_opt_result_id => l_opt_result_id,
508 p_copy_entity_txn_id => p_copy_entity_txn_id,
509 p_effective_date => p_effective_date
510 );
511 */
512 -- 2) Purge Elig Profile call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
513 hr_utility.set_location ( 'Calling Pqh_Gsp_Grd_Step_Remove.remove_elig_profile... :', 75);
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 -- 3) purge OIPL
519 hr_utility.set_location ('Purge OIPL ... :', 80);
520 delete_rec (
521 p_copy_entity_result_id => p_copy_entity_result_id,
522 p_effective_date => p_effective_date
523 );
524 ELSE -- dml_operation = COPIED/UPD_INS/UPDATE
525 -- 1) Mark for deletion call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
526 pqh_gsp_grd_step_remove.remove_elig_profile (
527 p_copy_entity_txn_id => p_copy_entity_txn_id,
528 p_copy_entity_result_id => p_copy_entity_result_id
529 );
530 -- 2) oipl.information104 = UNLINK
531 update_rec (
532 p_copy_entity_result_id => p_copy_entity_result_id,
533 p_effective_date => p_effective_date
534 );
535 END IF; -- end of dml_operation
536 END IF; -- end of POINT
537 hr_utility.set_location ('Leaving remove_oipl_POINT_flavour', 100);
538 END remove_oipl_point_flavour;
539 --
540 --------------------------- remove_oipl -----------------------------
541 --
542 -- To Remove OIPL Rec
543
544 PROCEDURE remove_oipl (
545 p_copy_entity_txn_id IN NUMBER,
546 p_copy_entity_result_id IN NUMBER,
547 p_effective_date IN DATE,
548 p_remove_opt IN VARCHAR2 DEFAULT 'Y'
549 )
550 IS
551 l_which_flavour VARCHAR2 (20);
552 BEGIN
553 hr_multi_message.enable_message_list;
554 hr_utility.set_location ('Entering remove_oipl', 10);
555 l_which_flavour := pqh_gsp_utility.use_point_or_step ( p_copy_entity_txn_id => p_copy_entity_txn_id);
556 hr_utility.set_location ('POINT/STEP :' || l_which_flavour, 30);
557 IF (l_which_flavour = 'POINT') THEN
558 pqh_gsp_grd_step_remove.remove_oipl_point_flavour (
559 p_copy_entity_txn_id => p_copy_entity_txn_id,
560 p_copy_entity_result_id => p_copy_entity_result_id,
561 p_effective_date => p_effective_date
562 );
563 END IF;
564 IF (l_which_flavour = 'STEP') THEN
565 pqh_gsp_grd_step_remove.remove_oipl_step_flavour (
566 p_copy_entity_txn_id => p_copy_entity_txn_id,
567 p_copy_entity_result_id => p_copy_entity_result_id,
568 p_effective_date => p_effective_date,
569 p_remove_opt => p_remove_opt
570 );
571 END IF;
572 hr_utility.set_location ('Leaving remove_oipl', 100);
573 Exception
574 when others then
575 hr_utility.set_location ('Caught an Exception', 100);
576 hr_multi_message.add;
577 END remove_oipl;
578 --
579 --------------------------- remove_plip -----------------------------
580 --
581 -- To Remove PLIP Rec
582
583 PROCEDURE remove_plip (
584 p_copy_entity_txn_id IN NUMBER,
585 p_copy_entity_result_id IN NUMBER,
586 p_effective_date IN DATE
587 )
588 IS
589 l_which_flavour VARCHAR2 (20);
590 l_dml_operation VARCHAR2 (20);
594 SELECT pln.copy_entity_result_id
591 l_pln_cer_id NUMBER;
592
593 CURSOR csr_plan_result_id IS
595 FROM ben_copy_entity_results pln
596 WHERE pln.gs_mirror_src_entity_result_id = p_copy_entity_result_id
597 AND pln.copy_entity_txn_id = p_copy_entity_txn_id
598 AND pln.table_alias = 'PLN';
599
600 CURSOR csr_oipl_ids IS
601 SELECT oipl.copy_entity_result_id
602 FROM ben_copy_entity_results oipl
603 WHERE oipl.gs_parent_entity_result_id = p_copy_entity_result_id
604 AND oipl.table_alias = 'COP'
605 AND oipl.copy_entity_txn_id = p_copy_entity_txn_id;
606
607 BEGIN
608 hr_utility.set_location ('Entering remove_plip', 10);
609
610 l_which_flavour := pqh_gsp_utility.use_point_or_step ( p_copy_entity_txn_id => p_copy_entity_txn_id);
611 l_dml_operation := get_dml_operation ( p_copy_entity_result_id => p_copy_entity_result_id);
612 hr_utility.set_location ('POINT/STEP :' || l_which_flavour, 30);
613 hr_utility.set_location ('DML Operation :' || l_dml_operation, 45);
614
615 -- Remove Rates, Elig Profiles attahced to OIPL
616 -- Remove OIPL
617 FOR rec_oipls IN csr_oipl_ids LOOP
618 IF (l_which_flavour = 'POINT') THEN
619 pqh_gsp_grd_step_remove.remove_oipl_point_flavour (
620 p_copy_entity_txn_id => p_copy_entity_txn_id,
621 p_copy_entity_result_id => rec_oipls.copy_entity_result_id,
622 p_effective_date => p_effective_date
623 );
624 END IF;
625
626 IF (l_which_flavour = 'STEP') THEN
627 pqh_gsp_grd_step_remove.remove_oipl_step_flavour (
628 p_copy_entity_txn_id => p_copy_entity_txn_id,
629 p_copy_entity_result_id => rec_oipls.copy_entity_result_id,
630 p_effective_date => p_effective_date
631 );
632 END IF;
633 END LOOP;
634
635 IF l_dml_operation = 'INSERT' THEN
636 -- 1) Purge Elig Profile call procedure remove_elig_profile
637 hr_utility.set_location ('Calling remove_elig_profile... :', 70);
638 pqh_gsp_grd_step_remove.remove_elig_profile (
639 p_copy_entity_txn_id => p_copy_entity_txn_id,
640 p_copy_entity_result_id => p_copy_entity_result_id
641 );
642
643 -- 2) Purge PLIP Rec
644 OPEN csr_plan_result_id;
645 FETCH csr_plan_result_id INTO l_pln_cer_id;
646 IF csr_plan_result_id%FOUND THEN
647 update ben_copy_entity_results
648 set gs_mirror_src_entity_result_id = null
649 where copy_entity_result_id = l_pln_cer_id;
650 END IF;
651 CLOSE csr_plan_result_id;
652
653 delete_rec ( p_copy_entity_result_id => p_copy_entity_result_id,
654 p_effective_date => p_effective_date);
655
656 hr_utility.set_location ('Purged PLIP Rec Sucessfully... :', 85);
657 ELSE -- dml_operation = UPDATE/COPIED/UPD_INS
658 -- Mark PLIP for Deletion
659 update_rec (
660 p_copy_entity_result_id => p_copy_entity_result_id,
661 p_effective_date => p_effective_date
662 );
663 hr_utility.set_location ('Marked for Deletion PLIP Rec Sucessfully... :',90);
664 END IF; -- DML Operation
665 hr_utility.set_location ('Leaving remove_plip', 100);
666 END remove_plip;
667 PROCEDURE unlink_opt_abr_hrrate_crrate (
668 p_opt_result_id IN NUMBER,
669 p_copy_entity_txn_id IN NUMBER,
670 p_effective_date IN DATE
671 )
672 IS
673 l_proc VARCHAR2 (72) := g_package || 'purge_opt_abr_hrrate_crrate ';
674
675 -- Get Standard Rates i.e HRRATE, ABR Attached to OPT
676 CURSOR csr_std_rates
677 IS
678 SELECT stdrate.copy_entity_result_id, stdrate.object_version_number
679 FROM ben_copy_entity_results stdrate
680 WHERE stdrate.table_alias IN ('HRRATE', 'ABR')
681 AND stdrate.information278 = p_opt_result_id
682 AND stdrate.copy_entity_txn_id = p_copy_entity_txn_id;
683
684 -- Get Criteria Rate GRRATE
685 CURSOR csr_crrate
686 IS
687 SELECT crrate.copy_entity_result_id, crrate.object_version_number
688 FROM ben_copy_entity_results crrate
689 WHERE crrate.copy_entity_txn_id = p_copy_entity_txn_id
690 AND crrate.table_alias = 'CRRATE'
691 AND crrate.information169 = p_opt_result_id;
692 BEGIN
693 hr_utility.set_location ('Entering ' || l_proc, 10);
694
695 -- purge HRRATE, ABR Records
696 FOR rec_std_rates IN csr_std_rates
697 LOOP
698 update_rec (
699 p_copy_entity_result_id => rec_std_rates.copy_entity_result_id,
700 p_effective_date => p_effective_date
701 );
702 END LOOP;
703
704 hr_utility.set_location ('Purged HRRATE, ABR Recs sucessully ', 40);
705
706 -- purge CRRATE Records
707 FOR rec_crrates IN csr_crrate
708 LOOP
709 update_rec (
710 p_copy_entity_result_id => rec_crrates.copy_entity_result_id,
711 p_effective_date => p_effective_date
712 );
713 END LOOP;
714
715 hr_utility.set_location ('Purged CRRATE Recs sucessully ', 55);
716 hr_utility.set_location ('Leaving ' || l_proc, 100);
717 EXCEPTION
721 END unlink_opt_abr_hrrate_crrate;
718 WHEN OTHERS
719 THEN
720 hr_utility.set_location ('purge_opt_abr_hrrate_crrate ', 110);
722
723 END pqh_gsp_grd_step_remove;