1 Package Body pqh_gsp_del_grade_ladder_obj as
2 /* $Header: pqgspdel.pkb 120.0 2005/05/29 01:58 appldev noship $ */
3 --
4 --
5 --
6 -- The following function will process the plans that were already linked
7 -- to the program in BEN tables and which were unlinked fron the program as a part
8 -- of this transaction.
9 -- Deleted plip rows(table_alias = 'CPP') will have dml_operation set as 'DELETE'
10 --
11 Function unlink_plan_from_pgm (p_copy_entity_txn_id in number,
12 p_effective_date in date,
13 p_datetrack_mode in varchar2)
14 RETURN varchar2 is
15 --
16 Cursor csr_unlink_pln is
17 Select information1 plip_id,
18 information2 effective_start_date,
19 information3 effective_end_date,
20 information4 business_group_id,
21 information261 pl_id,
22 information265 ovn
23 From ben_copy_entity_results
24 Where copy_entity_txn_id = p_copy_entity_txn_id
25 And table_alias = 'CPP'
26 And dml_operation = 'DELETE';
27 -- And information104 = 'UNLINK';
28 --
29 Cursor csr_ben_plip (p_plip_id in Number) Is
30 Select plip_Id,
31 Effective_Start_Date, Effective_End_Date,
32 Object_version_number
33 From Ben_plip_F
34 Where plip_id = p_plip_id
35 and p_effective_date
36 between Effective_Start_Date and Effective_End_Date;
37 --
38 l_plip_start_date ben_plip_f.effective_start_date%type;
39 l_plip_end_date ben_plip_f.effective_end_date%type;
40 l_plip_id ben_plip_f.plip_id%type;
41 l_plip_ovn ben_plip_f.object_version_number%type;
42 --
43 l_eot date;
44 l_datetrack_mode varchar2(30);
45 --
46 Begin
47 --
48 l_eot := to_date('31/12/4712','dd/mm/yyyy');
49 hr_utility.set_location('Entering: unlink_plan_from_pgm',1);
50 hr_utility.set_location('cet is '||p_copy_entity_txn_id,2);
51 hr_utility.set_location('effdt is '||to_char(p_effective_date,'dd/mm/yyyy'),3);
52 --
53 -- Select all the unlinked plip rows
54 --
55 For del_plip_rec in csr_unlink_pln loop
56 --
57 -- If an existing plan is attached as to the grade ladder as a part of this txn
58 -- the plip record will be created , and when it is deleted,
59 -- the plip_id will be null
60 --
61 hr_utility.set_location('rec found '||del_plip_rec.plip_id,6);
62 If del_plip_rec.plip_id IS NOT NULL then
63 --
64 -- Determine the date-tracked mode to use when deleting the row. If no date-tracked
65 -- mode is passed, the system will determine date-tracked mode to use when deleting
66 -- by reading actual BEN table rows.
67 --
68 --
69 hr_utility.set_location('dt mode passed '||p_datetrack_mode,7);
70 If p_datetrack_mode IS NULL then
71 --
72 Open csr_ben_plip(del_plip_rec.plip_id);
73 Fetch csr_ben_plip into l_plip_id,l_plip_start_date,l_plip_end_date,l_plip_ovn;
74 Close csr_ben_plip;
75 --
76 if l_plip_end_date <> l_eot then
77 hr_utility.set_location('not on last row ',15);
78 l_datetrack_mode := 'FUTURE_CHANGE';
79 else
80 hr_utility.set_location('on last row ',20);
81 l_datetrack_mode := 'DELETE';
82 end if;
83 --
84 Else
85 l_datetrack_mode := p_datetrack_mode;
86 End if;
87 hr_utility.set_location('dt mode used '||l_datetrack_mode,8);
88 --
89 ben_Plan_in_Program_api.delete_Plan_in_Program
90 (p_plip_id => del_plip_rec.plip_id
91 ,p_effective_start_date => del_plip_rec.effective_start_date
92 ,p_effective_end_date => del_plip_rec.effective_end_date
93 ,p_object_version_number => del_plip_rec.ovn
94 ,p_effective_date => p_effective_date
95 ,p_datetrack_mode => l_datetrack_mode);
96 --
97 hr_utility.set_location('delete success '||l_plip_id,9);
98 End if;
99 --
100 End loop;
101 --
102 hr_utility.set_location('Leaving: unlink_plan_from_pgm',10);
103 --
104 Return 'SUCCESS';
105 --
106 Exception
107 When Others Then
108 hr_utility.set_location('Exception raised: unlink_plan_from_pgm'||l_plip_id,99);
109 Return 'FAILURE';
110 End;
111 --
112 -------------------------------------------------------------------------------------
113 --
114 -- The following function will process the options that were already linked
115 -- to the plans in BEN tables and which were unlinked as a part
116 -- of this transaction.
117 -- Deleted oipl rows(table_alias = 'COP') will have dml_operation set as 'DELETE'
118 --
119 Function unlink_oipl_from_plan (p_copy_entity_txn_id in number,
120 p_effective_date in date,
121 p_datetrack_mode in varchar2)
122 RETURN varchar2 is
123 --
124 Cursor csr_unlink_opt is
125 Select information1 oipl_id,
126 information2 effective_start_date,
127 information3 effective_end_date,
128 information4 business_group_id,
129 information247 opt_id,
130 information265 oipl_ovn
131 From ben_copy_entity_results
132 Where copy_entity_txn_id = p_copy_entity_txn_id
133 And table_alias = 'COP'
134 And dml_operation = 'DELETE';
135 -- And information104 = 'UNLINK';
136 --
137 Cursor csr_ben_oipl (p_oipl_id in Number) Is
138 Select oipl_id,
139 Effective_Start_Date, Effective_End_Date,
140 Object_version_number
141 From Ben_oipl_F
142 Where oipl_id = p_oipl_id
143 and p_effective_date
144 between Effective_Start_Date and Effective_End_Date;
145 --
146 l_oipl_start_date ben_oipl_f.effective_start_date%type;
147 l_oipl_end_date ben_oipl_f.effective_end_date%type;
148 l_oipl_id ben_oipl_f.oipl_id%type;
149 l_oipl_ovn ben_oipl_f.object_version_number%type;
150 --
151 l_eot date;
152 l_datetrack_mode varchar2(30);
153 --
154 Begin
155 --
156 l_eot := to_date('31/12/4712','dd/mm/yyyy');
157 hr_utility.set_location('Entering: unlink_oipl_from_plan',5);
158 --
159 -- Select all the unlinked oipl rows
160 --
161 For del_oipl_rec in csr_unlink_opt loop
162 --
163 hr_utility.set_location('checking oipl '||del_oipl_rec.oipl_id,6);
164 hr_utility.set_location('checking oipl '||del_oipl_rec.oipl_ovn,7);
165 If del_oipl_rec.oipl_id is not null then
166 --
167 -- Determine the date-tracked mode to use when deleting the row. If no date-tracked
168 -- mode is passed, the system will determine date-tracked mode to use when deleting
169 -- by reading actual BEN table rows.
170 --
171 If p_datetrack_mode IS NULL then
172 --
173 Open csr_ben_oipl(del_oipl_rec.oipl_id);
174 Fetch csr_ben_oipl into l_oipl_id,l_oipl_start_date,l_oipl_end_date,l_oipl_ovn;
175 Close csr_ben_oipl;
176 --
177 if l_oipl_end_date <> l_eot then
178 hr_utility.set_location('not on last row ',15);
179 l_datetrack_mode := 'FUTURE_CHANGE';
180 else
181 hr_utility.set_location('on last row ',20);
182 l_datetrack_mode := 'DELETE';
183 end if;
184 --
185 hr_utility.set_location('datetrack mode is '||l_datetrack_mode,7);
186 --
187 Else
188 l_datetrack_mode := p_datetrack_mode;
189 End if;
190 --
191 ben_Option_in_Plan_api.delete_Option_in_Plan
192 (p_oipl_id => del_oipl_rec.oipl_id
193 ,p_effective_start_date => del_oipl_rec.effective_start_date
194 ,p_effective_end_date => del_oipl_rec.effective_end_date
195 ,p_object_version_number => del_oipl_rec.oipl_ovn
196 ,p_effective_date => p_effective_date
197 ,p_datetrack_mode => l_datetrack_mode);
198 --
199 End if;
200 --
201 End loop;
202 --
203 Return 'SUCCESS';
204 --
205 Exception
206 When Others Then
207 hr_utility.set_location('Exception raised: unlink_oipl_from_plan',99);
208 Return 'FAILURE';
209 End;
210 --
211 -------------------------------------------------------------------------------------
212 Function Get_Prfl_Del_Eff_Dt (P_Cer_Id In Number,
213 P_Copy_Entity_Txn_Id IN Number,
214 P_Effective_Date IN Date) Return Date is
215
216 Cursor Csr_Tabl_Alias is
217 Select Obj.Table_Alias , Obj.Dml_Operation,
218 Obj.Gs_Parent_Entity_Result_Id
219 From Ben_Copy_Entity_Results Cep,
220 Ben_Copy_Entity_Results Epa,
221 Ben_Copy_Entity_Results Obj
222 Where Cep.Copy_Entity_Result_id = P_Cer_id
223 and Epa.Copy_Entity_Result_id = Cep.GS_MIRROR_SRC_ENTITY_RESULT_ID
224 and Obj.Copy_Entity_Result_id = Epa.GS_MIRROR_SRC_ENTITY_RESULT_ID;
225
226 Cursor Csr_plip (P_Cpp_Cer_Id In Number)Is
227 Select Information253 Grade_Id,
228 Information255 Scale_Id
229 from Ben_Copy_Entity_Results Cpp
230 Where Copy_Entity_Txn_id = P_Copy_Entity_Txn_Id
231 and Copy_Entity_Result_Id = p_Cpp_Cer_Id
232 and Table_Alias = 'CPP'
233 and Result_type_Cd = 'DISPLAY';
234
235 Cursor Csr_Hr_Scale (P_Grade_Id in Number) Is
236 Select Parent_Spine_Id
237 From Per_Grade_Spines_F
238 Where Grade_Id = P_grade_Id
239 and P_Effective_Date
240 Between Effective_Start_Date
241 and Effective_End_Date;
242
243 l_table_Alias Ben_Copy_Entity_Results.table_Alias%TYPE;
244 l_Dml_operation Ben_Copy_Entity_Results.Dml_operation%TYPE;
245 l_Plip_Cer_Id Ben_Copy_Entity_Results.Copy_Entity_Result_id%TYPE;
246 l_Grade_Id Per_Grades.Grade_Id%TYPE;
247 l_Scale_Id Per_Parent_SPines.PARENT_SPINE_ID%TYPE;
248 l_Parent_Spine_Id Per_Parent_SPines.PARENT_SPINE_ID%TYPE;
249 Begin
250
251 Open Csr_Tabl_Alias;
252 Fetch Csr_Tabl_Alias Into l_table_Alias, l_Dml_operation, l_Plip_Cer_Id;
253 Close Csr_Tabl_Alias;
254
255 If l_table_Alias = 'COP' and l_Dml_operation = 'DELETE' then
256
257 Open Csr_plip(l_Plip_Cer_Id);
258 Fetch Csr_plip Into l_Grade_Id, l_Scale_Id;
259 Close Csr_plip;
260
261 Open Csr_Hr_Scale(l_Grade_Id);
262 Fetch Csr_Hr_Scale into l_Parent_Spine_Id;
263 Close Csr_Hr_Scale;
264
265 If Nvl(l_Parent_Spine_Id,-1) = Nvl(l_Scale_Id,-1) Then
266 Return P_Effective_Date;
267 Else
268 Return P_Effective_Date -1;
269 End If;
270
271 Else
272
273 Return P_Effective_Date;
274
275 End If;
276
277 End Get_Prfl_Del_Eff_Dt;
278
279 --
280 -- The following function will process the elif prfl that were already linked
281 -- to the plans in BEN tables and which were unlinked as a part
282 -- of this transaction.
283 --
284 Function unlink_elig_prfl (p_copy_entity_txn_id in number,
285 p_effective_date in date,
286 p_datetrack_mode in varchar2)
287 RETURN varchar2 is
288 --
289 Cursor csr_delete_cep is
290 Select information1 cep_id,
291 information2 effective_start_date,
292 information3 effective_end_date,
293 information4 business_group_id,
294 information265 cep_ovn,
295 Copy_Entity_Result_Id
296 From ben_copy_entity_results
297 Where copy_entity_txn_id = p_copy_entity_txn_id
298 And table_alias = 'CEP'
299 And dml_operation ='DELETE';
300 -- And information104 = 'UNLINK';
301 --
302 Cursor csr_ben_cep (p_prtn_elig_prfl_id in Number) Is
303 Select prtn_elig_prfl_id,
304 Effective_Start_Date, Effective_End_Date,
305 Object_version_number
306 From Ben_prtn_elig_prfl_f
307 Where prtn_elig_prfl_id = p_prtn_elig_prfl_id
308 and p_effective_date
309 between Effective_Start_Date and Effective_End_Date;
310 --
311 Cursor csr_delete_epa is
312 Select information1 epa_id,
313 information2 effective_start_date,
314 information3 effective_end_date,
315 information4 business_group_id,
316 information265 epa_ovn,
317 Copy_Entity_Result_id
318 From ben_copy_entity_results
319 Where copy_entity_txn_id = p_copy_entity_txn_id
320 And table_alias = 'EPA'
321 And dml_operation ='DELETE';
322 -- And information104 = 'UNLINK';
323 --
324 Cursor csr_ben_epa (p_prtn_elig_id in Number) Is
325 Select prtn_elig_id,
326 Effective_Start_Date, Effective_End_Date,
327 Object_version_number
328 From Ben_prtn_elig_f
329 Where prtn_elig_id = p_prtn_elig_id
330 and p_effective_date
331 between Effective_Start_Date and Effective_End_Date;
332
333 Cursor Csr_Epa_cer (P_Epa_Cer_Id IN Number) is
334 Select Copy_Entity_Result_Id
335 From Ben_Copy_Entity_Results
336 Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
337 and GS_MIRROR_SRC_ENTITY_RESULT_ID = P_Epa_Cer_id;
338 --
339 l_cep_start_date Ben_prtn_elig_prfl_f.effective_start_date%type;
340 l_cep_end_date Ben_prtn_elig_prfl_f.effective_end_date%type;
341 l_cep_id Ben_prtn_elig_prfl_f.prtn_elig_prfl_id%type;
342 l_cep_ovn Ben_prtn_elig_prfl_f.object_version_number%type;
343 --
344 l_epa_start_date Ben_prtn_elig_f.effective_start_date%type;
345 l_epa_end_date Ben_prtn_elig_f.effective_end_date%type;
346 l_epa_id Ben_prtn_elig_f.prtn_elig_id%type;
347 l_epa_ovn Ben_prtn_elig_f.object_version_number%type;
348 --
349 l_eot date;
350 l_datetrack_mode varchar2(30);
351 l_Effective_Date Date;
352 l_Cep_Cer Ben_Copy_Entity_Results.Copy_Entity_Result_id%TYPE;
353 --
354 Begin
355 --
356 l_eot := to_date('31/12/4712','dd/mm/yyyy');
357 hr_utility.set_location('Entering: unlink_elig_prfl',5);
358 --
359 -- Select all the unlinked cep rows i.e eligibility profile is unlinked from a
360 -- GSP object
361 --
362
363 For del_cep_rec in csr_delete_cep loop
364 --
365 l_Effective_Date := Get_Prfl_Del_Eff_Dt (del_Cep_Rec.Copy_Entity_Result_Id, p_copy_entity_txn_id, P_Effective_Date);
366
367 If L_Effective_Date is NULL then
368 l_Effective_Date := P_Effective_Date;
369 End If;
370
371 If del_cep_rec.cep_id is not null then
372 --
373 -- Determine the date-tracked mode to use when deleting the row. If no date-tracked
374 -- mode is passed, the system will determine date-tracked mode to use when deleting
375 -- by reading actual BEN table rows.
376 --
377 If p_datetrack_mode IS NULL then
378 --
379 Open csr_ben_cep(del_cep_rec.cep_id);
380 Fetch csr_ben_cep into l_cep_id,l_cep_start_date,l_cep_end_date,l_cep_ovn;
381 Close csr_ben_cep;
382 --
383 if l_cep_end_date <> l_eot then
384 hr_utility.set_location('not on last row ',15);
385 l_datetrack_mode := 'FUTURE_CHANGE';
386 else
387 hr_utility.set_location('on last row ',20);
388 l_datetrack_mode := 'DELETE';
389 end if;
390 --
391 Else
392 l_datetrack_mode := p_datetrack_mode;
393 End if;
394 --
395 ben_PRTN_ELIG_PRFL_api.delete_PRTN_ELIG_PRFL
396 (
397 p_validate => false
398 ,p_prtn_elig_prfl_id => del_cep_rec.cep_id
399 ,p_effective_start_date => del_cep_rec.effective_start_date
400 ,p_effective_end_date => del_cep_rec.effective_end_date
401 ,p_object_version_number => del_cep_rec.cep_ovn
402 ,p_effective_date => l_effective_date
403 ,p_datetrack_mode => l_datetrack_mode
404 );
405 --
406 End if;
407 --
408 End loop;
409 --
410 --
411 -- Select all the unlinked epa rows. EPA row is marked for delete when all profiles
412 -- under a GSP object have been unlinked.
413 --
414 For del_epa_rec in csr_delete_epa loop
415 --
416 Open Csr_Epa_cer (del_epa_rec.Copy_Entity_Result_id);
417 Fetch Csr_Epa_cer Into l_Cep_Cer;
418 Close Csr_Epa_cer;
419
420 l_Effective_Date := Get_Prfl_Del_Eff_Dt (l_Cep_Cer, p_copy_entity_txn_id, P_Effective_Date);
421 If del_epa_rec.epa_id is not null then
422 --
423 -- Determine the date-tracked mode to use when deleting the row. If no date-tracked
424 -- mode is passed, the system will determine date-tracked mode to use when deleting
425 -- by reading actual BEN table rows.
426 --
427 If p_datetrack_mode IS NULL then
428 --
429 Open csr_ben_epa(del_epa_rec.epa_id);
430 Fetch csr_ben_epa into l_epa_id,l_epa_start_date,l_epa_end_date,l_epa_ovn;
431 Close csr_ben_epa;
432 --
433 if l_epa_end_date <> l_eot then
434 hr_utility.set_location('not on last row ',25);
435 l_datetrack_mode := 'FUTURE_CHANGE';
436 else
437 hr_utility.set_location('on last row ',30);
438 l_datetrack_mode := 'DELETE';
439 end if;
440 --
441 Else
442 l_datetrack_mode := p_datetrack_mode;
443 End if;
444 --
445 ben_Participation_Elig_api.delete_Participation_Elig
446 (
447 p_validate => false
448 ,p_prtn_elig_id => del_epa_rec.epa_id
449 ,p_effective_start_date => del_epa_rec.effective_start_date
450 ,p_effective_end_date => del_epa_rec.effective_end_date
451 ,p_object_version_number => del_epa_rec.epa_ovn
452 ,p_effective_date => l_effective_date
453 ,p_datetrack_mode => l_datetrack_mode
454 );
455 --
456 End if;
457 --
458 End loop;
459 --
460 hr_utility.set_location('Leaving: unlink_elig_prfl',10);
461 --
462 Return 'SUCCESS';
463 --
464 Exception
465 When Others Then
466 hr_utility.set_location('Exception raised: unlink_elig_prfl',99);
467 Return 'FAILURE';
468 --
469 End;
470 --
471 -------------------------------------------------------------------------------------
472 --
473 -- The following function deletes the options, marked for delete in the transaction
474 -- The deleted option rows will have dml_operation = 'DELETE'.
475
476 Function delete_option (p_copy_entity_txn_id in number,
477 p_effective_date in date,
478 p_datetrack_mode in varchar2)
479 RETURN varchar2 is
480 --
481 Cursor csr_delete_opt is
482 Select information1 opt_id,
483 information2 effective_start_date,
484 information3 effective_end_date,
485 information4 business_group_id,
486 information265 opt_ovn
487 From ben_copy_entity_results
488 Where copy_entity_txn_id = p_copy_entity_txn_id
489 And table_alias = 'OPT'
490 And dml_operation ='DELETE';
491 -- And information104 = 'UNLINK';
492 --
493 Cursor csr_ben_opt (p_opt_id in Number) Is
494 Select opt_id,
495 Effective_Start_Date , Effective_End_Date,
496 Object_version_number
497 From Ben_opt_F
498 Where opt_id = p_opt_id
499 and p_effective_date
500 between Effective_Start_Date and Effective_End_Date
501 and Mapping_table_name = 'PER_SPINAL_POINTS';
502
503 Cursor csr_Pl_Opt_Type (P_Opt_Id IN Number) is
504 Select Pl_typ_opt_Typ_Id,
505 Effective_Start_Date,
506 Effective_End_Date,
507 Object_Version_Number
508 from Ben_Pl_Typ_Opt_Typ_F
509 Where Opt_Id = P_Opt_id
510 and Pl_Typ_Opt_Typ_Cd = 'GSP';
511
512 Cursor Csr_OIpl (P_Opt_id IN Number) Is
513 Select Oipl_id
514 From Ben_Oipl_F
515 Where Opt_id = p_Opt_id;
516
517 --
518 l_opt_start_date ben_opt_f.effective_start_date%type;
519 l_opt_end_date ben_opt_f.effective_end_date%type;
520 l_opt_id ben_opt_f.opt_id%type;
521 l_opt_ovn ben_opt_f.object_version_number%type;
522 l_Oipl_Id Ben_Oipl_F.Oipl_Id%TYPE;
523
524 --
525 --
526 l_Pl_Typ_Opt_Typ_Id Ben_Pl_Typ_Opt_Typ_F.Pl_Typ_Opt_Typ_Id%TYPE;
527 l_opt_typ_Esd Ben_Pl_Typ_Opt_Typ_F.Effective_Start_Date%TYPE;
528 l_Opt_Typ_Eed Ben_Pl_Typ_Opt_Typ_F.Effective_End_Date%TYPE;
529 l_Opt_typ_Ovn Ben_Pl_Typ_Opt_Typ_F.Object_Version_Number%TYPE;
530 --
531 l_eot date;
532 l_datetrack_mode varchar2(30);
533 --
534 Begin
535 --
536 l_eot := to_date('31/12/4712','dd/mm/yyyy');
537 hr_utility.set_location('Entering: delete_option',5);
538 --
539 -- Select all the deleted opt rows.
540 --
541 For del_opt_rec in csr_delete_opt loop
542 --
543 -- When a point that is not used as step is brought to staging area, an option
544 -- is created for it anyway, if the option does not already exist.
545 -- When the point is deleted the option record will be marked
546 -- delete but theere will be not opt_id as there is no record in BEN.
547 --
548 --
549 If del_opt_rec.opt_id is not null then
550 --
551 -- Determine the date-tracked mode to use when deleting the row. If no date-tracked
552 -- mode is passed, the system will determine date-tracked mode to use when deleting
553 -- by reading actual BEN table rows.
554 --
555 --
556 Open csr_ben_opt(del_opt_rec.opt_id);
557 Fetch csr_ben_opt into l_opt_id,l_opt_start_date,l_opt_end_date, l_opt_ovn;
558 Close csr_ben_opt;
559
560 Open Csr_Oipl (l_opt_Id);
561 Fetch Csr_Oipl into l_Oipl_id;
562 If Csr_Oipl%NOTFOUND then
563 --
564 If p_datetrack_mode IS NULL then
565 if l_opt_end_date <> l_eot then
566 hr_utility.set_location('not on last row ',15);
567 l_datetrack_mode := 'FUTURE_CHANGE';
568 else
569 hr_utility.set_location('on last row ',20);
570 l_datetrack_mode := 'DELETE';
571 end if;
572 --
573 Else
574 l_datetrack_mode := p_datetrack_mode;
575 End if;
576
577 --
578 -- Delete Pl_TYp_Opt_typ record
579 Open csr_Pl_Opt_Type(del_opt_rec.opt_id);
580 Fetch csr_Pl_Opt_Type into l_Pl_Typ_Opt_Typ_Id, l_opt_typ_Esd, l_Opt_Typ_Eed, l_Opt_typ_Ovn;
581 Close csr_Pl_Opt_Type;
582
583
584 If l_Pl_Typ_Opt_Typ_Id is NOT NULL then
585
586 ben_plan_type_option_type_api.Delete_Plan_Type_Option_Type
587 (P_PL_TYP_OPT_TYP_ID => l_Pl_Typ_Opt_Typ_Id
588 ,P_EFFECTIVE_START_DATE => l_opt_typ_Esd
589 ,P_EFFECTIVE_END_DATE => l_Opt_Typ_Eed
590 ,P_OBJECT_VERSION_NUMBER => l_Opt_typ_Ovn
591 ,P_EFFECTIVE_DATE => P_Effective_Date
592 ,P_DATETRACK_MODE => l_datetrack_mode);
593
594 -- Delete Option
595
596 ben_option_definition_api.delete_option_definition
597 (p_opt_id => del_opt_rec.opt_id
598 ,p_effective_start_date => del_opt_rec.effective_start_date
599 ,p_effective_end_date => del_opt_rec.effective_end_date
600 ,p_object_version_number => del_opt_rec.opt_ovn
601 ,p_effective_date => p_effective_date
602 ,p_datetrack_mode => l_datetrack_mode);
603
604 End If;
605 --
606 End if;
607 --
608 End If; -- Csr_Oipl
609 Close Csr_Oipl;
610 End loop;
611 --
612 hr_utility.set_location('Leaving: delete_option',10);
613 --
614 Return 'SUCCESS';
615 --
616 Exception
617 When Others Then
618 hr_utility.set_location('Exception raised: delete_option',99);
619 Return 'FAILURE';
620 End;
621
622 ----------------------------------------------------------------------------
623 --
624 -- This is the main function called before copying BEN objects from staging
625 -- tables to actual BEN tables
626 -- Returns either 'SUCCESS' or 'FAILURE'
627 --
628 Function delete_from_ben (p_copy_entity_txn_id in number,
629 p_effective_date in date,
630 p_datetrack_mode in varchar2)
631 RETURN varchar2 is
632 --
633 l_status varchar2(30);
634 --
635 Begin
636 --
637 l_status := 'SUCCESS';
638 hr_utility.set_location('Entering: delete_from_ben',5);
639 --
640 -- Delete from lowest level
641 -- 1) Delete elig profiles
642 --
643 l_status := unlink_elig_prfl(p_copy_entity_txn_id => p_copy_entity_txn_id,
644 p_effective_date => p_effective_date,
645 p_datetrack_mode => p_datetrack_mode);
646
647 if l_status = 'FAILURE' then
648 hr_utility.set_location('Failed in deleting elig',15);
649 Return 'FAILURE';
650 End if;
651 --
652 -- 2) Delete plip
653 --
654 l_status := unlink_plan_from_pgm (p_copy_entity_txn_id => p_copy_entity_txn_id,
655 p_effective_date => p_effective_date,
656 p_datetrack_mode => p_datetrack_mode);
657 if l_status = 'FAILURE' then
658 hr_utility.set_location('Failed in deleting plip',30);
659 Return 'FAILURE';
660 End if;
661 --
662 -- 3) Delete oipl
663 --
664 l_status := unlink_oipl_from_plan(p_copy_entity_txn_id => p_copy_entity_txn_id,
665 p_effective_date => p_effective_date,
666 p_datetrack_mode => p_datetrack_mode);
667 if l_status = 'FAILURE' then
668 hr_utility.set_location('Failed in deleting oipl',20);
669 Return 'FAILURE';
670 End if;
671 --
672 -- 4) Delete option
673 --
674 l_status := delete_option (p_copy_entity_txn_id => p_copy_entity_txn_id,
675 p_effective_date => p_effective_date,
676 p_datetrack_mode => p_datetrack_mode);
677 if l_status = 'FAILURE' then
678 hr_utility.set_location('Failed in deleting opt',25);
679 Return 'FAILURE';
680 End if;
681 --
682 hr_utility.set_location('Leaving: delete_from_ben',10);
683 --
684 Return 'SUCCESS';
685 --
686 End;
687 --
688 --
689 End pqh_gsp_del_grade_ladder_obj;
[Home] [Help]