[Home] [Help]
PACKAGE BODY: APPS.BEN_EFC_FUNCTIONS
Source
1 package body ben_efc_functions as
2 /* $Header: beefcfnc.pkb 115.13 2002/12/31 23:58:28 mmudigon noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Version Date Author Comments
13 --------- --------- ---------- --------------------------------------------
14 115.0 07-Jan-01 mhoyes Created.
15 115.1 31-Jan-01 mhoyes Added more functions.
16 115.2 06-Apr-01 mhoyes Enhanced for Patchset D.
17 115.3 12-Jul-01 mhoyes Enhanced for Patchset E.
18 115.6 26-Jul-01 mhoyes Enhanced for Patchset E+ patch.
19 115.7 13-Aug-01 mhoyes Enhanced for Patchset E+ patch.
20 115.8 27-Aug-01 mhoyes Enhanced for July EFC patch.
21 115.9 13-Sep-01 mhoyes Enhanced for July EFC patch.
22 115.10 26-Sep-01 mhoyes Enhanced for Patchset G.
23 115.11 26-Sep-01 mhoyes Enhanced for Patchset G.
24 115.13 30-Dec-02 mmudigon NOCOPY
25 -----------------------------------------------------------------------------
26 */
27 --
28 -- Globals.
29 --
30 g_package varchar2(50) := 'ben_efc_functions.';
31 --
32 g_curr_pil_id number;
33 --
34 procedure setup_workers
35 (p_component_name in varchar2
36 ,p_sub_step in varchar2
37 ,p_table_name in varchar2
38 ,p_worker_id in number
39 ,p_total_workers in number
40 --
41 ,p_business_group_id in number default null
42 --
43 ,p_chunk out nocopy varchar2
44 ,p_status out nocopy varchar2
45 ,p_action_id out nocopy number
46 ,p_pk1 out nocopy number
47 ,p_efc_worker_id out nocopy number
48 )
49 is
50
51 l_proc varchar2(1000) := 'setup_workers';
52
53 l_chunk NUMBER;
54 l_action_id NUMBER;
55 l_bg NUMBER;
56 l_component_id NUMBER;
57
58 l_status varchar2(100);
59
60 l_pk2char varchar2(100) := '';
61 l_pk3char varchar2(100) := '';
62 l_pk4char varchar2(100) := '';
63 l_pk5char varchar2(100) := '';
64 --
65 CURSOR csr_fetch_details
66 (c_bgp_id in number
67 )
68 IS
69 SELECT act.efc_action_id,
70 act.business_group_id
71 FROM hr_efc_actions act
72 WHERE act.efc_action_status = 'P'
73 AND act.efc_action_type = 'C'
74 and act.business_group_id = c_bgp_id;
75
76 begin
77 --
78 -- switch off who triggers
79 --
80 hr_general.g_data_migrator_mode := 'Y';
81 --
82 -- Get the details for this particular actions
83 -- e.g. action_id, bg_id and chunk size
84 --
85 -- Check if we know the business group that we are dealing with
86 --
87 if p_business_group_id is null then
88 --
89 hr_efc_info.get_action_details
90 (l_action_id
91 ,l_bg
92 ,p_chunk
93 );
94 --
95 else
96 --
97 open csr_fetch_details
98 (c_bgp_id => p_business_group_id
99 );
100 fetch csr_fetch_details into l_action_id, l_bg;
101 close csr_fetch_details;
102 --
103 p_chunk := hr_efc_info.get_chunk;
104 --
105 end if;
106 --
107 -- Validate that conversion started with correct no. of total workers
108 --
109 hr_efc_info.validate_total_workers
110 (p_action_id => l_action_id
111 ,p_component_name => p_component_name
112 ,p_sub_step => p_sub_step
113 ,p_step => 'C_RECAL'
114 ,p_total_workers => p_total_workers
115 );
116 --
117 -- First processor only - insert a row into the HR_EFC_PROCESS_COMPONENTS
118 -- table (procedure includes locking so that only 1 row is inserted)
119 --
120 hr_efc_info.insert_or_select_comp_row
121 (p_action_id => l_action_id
122 ,p_process_component_name => p_component_name
123 ,p_table_name => p_table_name
124 ,p_total_workers => p_total_workers
125 ,p_worker_id => p_worker_id
126 ,p_step => 'C_RECAL'
127 ,p_sub_step => p_sub_step
128 ,p_process_component_id => l_component_id
129 );
130 --
131 -- Call procedure to check if this worker has already started (will detect
132 -- if this worker has been restarted).
133 --
134 hr_efc_info.insert_or_select_worker_row
135 (p_efc_worker_id => p_efc_worker_id
136 ,p_status => p_status
137 ,p_process_component_id => l_component_id
138 ,p_process_component_name => p_component_name
139 ,p_action_id => l_action_id
140 ,p_worker_number => p_worker_id
141 ,p_pk1 => p_pk1
142 ,p_pk2 => l_pk2char
143 ,p_pk3 => l_pk3char
144 ,p_pk4 => l_pk4char
145 ,p_pk5 => l_pk5char
146 );
147 --
148 -- Set out parameters
149 --
150 p_action_id := l_action_id;
151 --
152 end setup_workers;
153 --
154 procedure maintain_chunks
155 (p_row_count in out nocopy number
156 ,p_pk1 in number
157 ,p_chunk_size in number
158 ,p_efc_worker_id in number
159 )
160 is
161
162 l_proc varchar2(1000) := 'maintain_chunks';
163
164 begin
165 --
166 -- Update the count for sake of chunk size
167 --
168 p_row_count := p_row_count + 1;
169 --
170 -- Check whether or not we wish to commit
171 --
172 IF (p_row_count >= p_chunk_size) THEN
173 --
174 -- Update worker table
175 hr_efc_info.update_worker_row
176 (p_efc_worker_id => p_efc_worker_id
177 ,p_pk1 => p_pk1
178 );
179 -- Reset the counter.
180 p_row_count := 0;
181 -- Commit details
182 COMMIT;
183 END IF;
184 --
185 end maintain_chunks;
186 --
187 procedure conv_check
188 (p_table_name in varchar2
189 ,p_efctable_name in varchar2
190 ,p_tabwhere_clause in varchar2 default null
191 --
192 ,p_bgp_id in number default null
193 ,p_action_id in number default null
194 --
195 ,p_table_sql in varchar2 default null
196 ,p_efctable_sql in varchar2 default null
197 --
198 ,p_tabrow_count out nocopy number
199 ,p_conv_count out nocopy number
200 ,p_unconv_count out nocopy number
201 )
202 is
203 --
204 TYPE cur_type IS REF CURSOR;
205 --
206 c_conv_count cur_type;
207 --
208 l_proc varchar2(1000) := 'conv_check';
209 --
210 l_sql_str long;
211 --
212 l_conv_count number;
213 l_tabrow_count pls_integer;
214 --
215 l_business_group_id number;
216 --
217 begin
218 --
219 if p_action_id is not null then
220 --
221 select business_group_id
222 into l_business_group_id
223 from hr_efc_actions
224 where efc_action_id = p_action_id;
225 --
226 elsif p_bgp_id is not null then
227 --
228 l_business_group_id := p_bgp_id;
229 --
230 end if;
231 --
232 if p_table_sql is not null
233 then
234 --
235 open c_conv_count FOR p_table_sql;
236 FETCH c_conv_count INTO l_tabrow_count;
237 CLOSE c_conv_count;
238 --
239 elsif p_table_name is not null then
240 --
241 l_sql_str := 'select count(*) '
242 ||' from '||p_table_name
243 ||' where business_group_id is not null ';
244 --
245 if l_business_group_id is not null then
246 --
247 l_sql_str := l_sql_str||' and business_group_id = '||l_business_group_id;
248 --
249 end if;
250 --
251 if p_tabwhere_clause is not null then
252 --
253 l_sql_str := l_sql_str||' and '||p_tabwhere_clause;
254 --
255 end if;
256 --
257 open c_conv_count FOR l_sql_str;
258 FETCH c_conv_count INTO l_tabrow_count;
259 CLOSE c_conv_count;
260 --
261 end if;
262 --
263 if p_efctable_sql is not null
264 and p_action_id is not null
265 then
266 --
267 open c_conv_count FOR p_efctable_sql;
268 FETCH c_conv_count INTO l_conv_count;
269 CLOSE c_conv_count;
270 --
271 elsif p_efctable_name is not null
272 and p_action_id is not null
273 then
274 --
275 l_sql_str := 'select count(*) '
276 ||' from '||p_efctable_name
277 ||' where efc_action_id = '||p_action_id;
278 --
279 open c_conv_count FOR l_sql_str;
280 FETCH c_conv_count INTO l_conv_count;
281 CLOSE c_conv_count;
282 --
283 end if;
284 --
285 p_tabrow_count := l_tabrow_count;
286 p_conv_count := l_conv_count;
287 p_unconv_count := l_tabrow_count-l_conv_count;
288 --
289 end conv_check;
290 --
291 procedure EPEorENB_InitCounts
292 is
293 --
294 l_proc varchar2(1000) := 'EPEorENB_InitCounts';
295 --
296 begin
297 --
298 g_epe_count := 0;
299 g_enb_count := 0;
300 g_epeenbnull_count := 0;
301 g_noepedets_count := 0;
302 g_noenbdets_count := 0;
303 --
304 end EPEorENB_InitCounts;
305 --
306 procedure EPEorENB_GetEPEDets
307 (p_elig_per_elctbl_chc_id in number default null
308 ,p_enrt_bnft_id in number default null
309 --
310 ,p_currepe_row out nocopy ben_epe_cache.g_pilepe_inst_row
311 )
312 is
313 --
314 l_proc varchar2(1000) := 'EPEorENB_GetEPEDets';
315 --
316 cursor c_epedets
317 (c_epe_id in number
318 )
319 is
320 select pil.lf_evt_ocrd_dt,
321 pil.person_id,
322 pil.per_in_ler_id,
323 pil.business_group_id,
324 pil.ler_id,
325 pil.per_in_ler_stat_cd
326 from BEN_ELIG_PER_ELCTBL_CHC epe,
327 ben_per_in_ler pil,
328 per_all_people_f per
329 where pil.per_in_ler_id = epe.per_in_ler_id
330 and epe.ELIG_PER_ELCTBL_CHC_id = c_epe_id
331 and per.person_id = pil.person_id
332 and pil.lf_evt_ocrd_dt
333 between per.effective_start_date and per.effective_end_date;
334 --
335 l_epedets c_epedets%rowtype;
336 --
337 cursor c_enbdets
338 (c_enb_id in number
339 )
340 is
341 select pil.lf_evt_ocrd_dt,
342 pil.person_id,
343 pil.per_in_ler_id,
344 pil.business_group_id,
345 pil.ler_id,
346 pil.per_in_ler_stat_cd,
347 enb.val
348 from ben_enrt_bnft enb,
349 BEN_ELIG_PER_ELCTBL_CHC epe,
350 ben_per_in_ler pil,
351 per_all_people_f per
352 where enb.ELIG_PER_ELCTBL_CHC_id = epe.ELIG_PER_ELCTBL_CHC_id
353 and pil.per_in_ler_id = epe.per_in_ler_id
354 and enb.enrt_bnft_id = c_enb_id
355 and per.person_id = pil.person_id
356 and pil.lf_evt_ocrd_dt
357 between per.effective_start_date and per.effective_end_date;
358 --
359 l_enbdets c_enbdets%rowtype;
360 --
361 l_currepe_set ben_epe_cache.g_pilepe_inst_tbl;
362 --
363 l_currepe_row ben_epe_cache.g_pilepe_inst_row;
364 --
365 l_business_group_id number;
366 l_lf_evt_ocrd_dt date;
367 l_person_id number;
368 l_per_in_ler_id number;
369 --
370 begin
371 --
372 if p_elig_per_elctbl_chc_id is not null
373 then
374 --
375 open c_epedets
376 (c_epe_id => p_elig_per_elctbl_chc_id
377 );
378 fetch c_epedets into l_epedets;
379 close c_epedets;
380 --
381 l_business_group_id := l_epedets.business_group_id;
382 l_lf_evt_ocrd_dt := l_epedets.lf_evt_ocrd_dt;
383 l_person_id := l_epedets.person_id;
384 ben_epe_cache.EPE_GetEPEDets
385 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
386 ,p_per_in_ler_id => l_epedets.per_in_ler_id
387 ,p_inst_row => l_currepe_row
388 );
389 --
390 elsif p_enrt_bnft_id is not null then
391 --
392 open c_enbdets
393 (c_enb_id => p_enrt_bnft_id
394 );
395 fetch c_enbdets into l_enbdets;
396 close c_enbdets;
397 --
398 l_business_group_id := l_enbdets.business_group_id;
399 l_lf_evt_ocrd_dt := l_enbdets.lf_evt_ocrd_dt;
400 l_person_id := l_enbdets.person_id;
401 l_per_in_ler_id := l_enbdets.per_in_ler_id;
402 --
403 -- Get the EPE details
404 --
405 ben_epe_cache.ENBEPE_GetEPEDets
406 (p_enrt_bnft_id => p_enrt_bnft_id
407 ,p_per_in_ler_id => l_enbdets.per_in_ler_id
408 ,p_inst_row => l_currepe_row
409 );
410 --
411 end if;
412 --
413 p_currepe_row := l_currepe_row;
414 --
415 end EPEorENB_GetEPEDets;
416 --
417 procedure CompObject_ChkAttachDF
418 (p_coent_scode in varchar2
419 ,p_compobj_id in number default null
420 --
421 ,p_counts out nocopy g_attach_df_counts
422 )
423 is
424 --
425 TYPE cur_type IS REF CURSOR;
426 --
427 c_df_count cur_type;
428 --
429 l_proc varchar2(1000) := 'CompObject_ChkAttachDF';
430 --
431 l_sql_str long;
432 --
433 l_count pls_integer;
434 --
435 l_cocol_name varchar2(100);
436 --
437 begin
438 --
439 if p_coent_scode = 'COP' then
440 --
441 l_cocol_name := 'oipl_id';
442 --
443 elsif p_coent_scode = 'CPP' then
444 --
445 l_cocol_name := 'plip_id';
446 --
447 elsif p_coent_scode = 'PLN' then
448 --
449 l_cocol_name := 'pl_id';
450 --
451 elsif p_coent_scode = 'CTP' then
452 --
453 l_cocol_name := 'ptip_id';
454 --
455 elsif p_coent_scode = 'PGM' then
456 --
457 l_cocol_name := 'pgm_id';
458 --
459 end if;
460 --
461 l_sql_str := 'select count(*) '
462 ||' from BEN_PRTN_ELIG_F tab '
463 ||' where tab.'||l_cocol_name||' = :id ';
464 --
465 open c_df_count FOR l_sql_str using p_compobj_id;
466 FETCH c_df_count INTO l_count;
467 CLOSE c_df_count;
468 --
469 p_counts.epa_count := l_count;
470 --
471 if l_count = 0 then
472 --
473 l_sql_str := 'select count(*) '
474 ||' from ben_acty_base_rt_f tab '
475 ||' where tab.'||l_cocol_name||' = :id ';
476 --
477 open c_df_count FOR l_sql_str using p_compobj_id;
478 FETCH c_df_count INTO l_count;
479 CLOSE c_df_count;
480 --
481 p_counts.abr_count := l_count;
482 --
483 if l_count = 0 then
484 --
485 -- Only relevant for plans and oipls
486 --
487 if p_coent_scode in('COP','PLN') then
488 --
489 l_sql_str := 'select count(*) '
490 ||' from ben_actl_prem_f tab '
491 ||' where tab.'||l_cocol_name||' = :id ';
492 --
493 open c_df_count FOR l_sql_str using p_compobj_id;
494 FETCH c_df_count INTO l_count;
495 CLOSE c_df_count;
496 --
497 p_counts.apr_count := l_count;
498 --
499 end if;
500 --
501 if l_count = 0 then
502 --
503 l_sql_str := 'select count(*) '
504 ||' from ben_cvg_amt_calc_mthd_f tab '
505 ||' where tab.'||l_cocol_name||' = :id ';
506 --
507 open c_df_count FOR l_sql_str using p_compobj_id;
508 FETCH c_df_count INTO l_count;
509 CLOSE c_df_count;
510 --
511 p_counts.ccm_count := l_count;
512 --
513 if l_count = 0 then
514 --
515 p_counts.noattdf_count := 0;
516 --
517 end if;
518 --
519 end if;
520 --
521 end if;
522 --
523 end if;
524 --
525 end CompObject_ChkAttachDF;
526 --
527 procedure BGP_WriteEFCAction
528 (p_bgp_id in number
529 --
530 ,p_efc_action_id out nocopy number
531 )
532 is
533 --
534 l_proc varchar2(1000) := 'BGP_WriteEFCAction';
535 --
536 CURSOR csr_check_action_exists
537 (c_bgp_id in number
538 )
539 IS
540 SELECT 'Y'
541 FROM hr_efc_actions
542 WHERE efc_action_status = 'P'
543 and business_group_id = c_bgp_id;
544 --
545 CURSOR csr_fetch_id
546 IS
547 SELECT hr_efc_actions_s.nextval
548 FROM dual;
549 --
550 CURSOR csr_get_sequence
551 (c_bg IN number
552 )
553 IS
554 SELECT max(action_sequence)
555 FROM hr_efc_actions
556 WHERE business_group_id = c_bg;
557 --
558 CURSOR csr_find_lowest_phase
559 IS
560 SELECT to_number(substr(lok.lookup_code,2)) action_num
561 FROM hr_lookups lok
562 WHERE lok.lookup_type = 'EFC_PROGRESS_STATUS'
563 AND substr(lok.lookup_code,1,1) = 'C'
564 ORDER BY lok.lookup_code;
565 --
566 l_exists varchar2(1);
567 l_id number;
568 l_max number;
569 l_low number := 99999999;
570 --
571 BEGIN
572 --
573 OPEN csr_check_action_exists
574 (c_bgp_id => p_bgp_id
575 );
576 FETCH csr_check_action_exists INTO l_exists;
577 IF csr_check_action_exists%NOTFOUND THEN
578 -- Fetch pk
579 OPEN csr_fetch_id;
580 FETCH csr_fetch_id INTO l_id;
581 CLOSE csr_fetch_id;
582 -- Fetch sequence
583 OPEN csr_get_sequence(p_bgp_id);
584 FETCH csr_get_sequence INTO l_max;
585 CLOSE csr_get_sequence;
586 --
587 FOR c1 IN csr_find_lowest_phase LOOP
588 IF l_low > c1.action_num THEN
589 l_low := c1.action_num;
590 END IF;
591 END LOOP;
592 --
593 IF l_max IS NULL THEN
594 l_max := 1;
595 ELSE
596 l_max := l_max +1;
597 END IF;
598 --
599 INSERT INTO hr_efc_actions
600 (efc_action_id
601 ,efc_action_type
602 ,efc_action_status
603 ,efc_progress_status
604 ,business_group_id
605 ,action_sequence
606 ,start_date
607 ,finish_date
608 ,matching_efc_action_id
609 ,last_update_date
610 ,last_updated_by
611 ,last_update_login
612 ,created_by
613 ,creation_date
614 )
615 VALUES
616 (l_id
617 ,'C'
618 ,'P'
619 ,'C' || to_char(l_low)
620 ,p_bgp_id
621 ,l_max
622 ,sysdate
623 ,null
624 ,null
625 ,sysdate
626 ,-1
627 ,-1
628 ,-1
629 ,sysdate
630 );
631 --
632 END IF;
633 CLOSE csr_check_action_exists;
634 --
635 COMMIT;
636 --
637 p_efc_action_id := l_id;
638 --
639 END BGP_WriteEFCAction;
640 --
641 procedure BGP_SetupEFCAction
642 (p_bgp_id in number
643 --
644 ,p_efc_action_id out nocopy number
645 )
646 is
647 --
648 l_proc varchar2(1000) := 'BGP_SetupEFCAction';
649 --
650 l_efc_action_id number;
651 --
652 CURSOR c_getprevbgpactid
653 (c_bgp_id in number
654 )
655 IS
656 select efc.efc_action_id
657 from hr_efc_actions efc
658 where efc.business_group_id = c_bgp_id;
659 --
660 BEGIN
661 --
662 -- Get the previous action id for the bgp id
663 --
664 open c_getprevbgpactid
665 (c_bgp_id => p_bgp_id
666 );
667 fetch c_getprevbgpactid into l_efc_action_id;
668 if c_getprevbgpactid%found then
669 --
670 -- Remove action information for the business group
671 --
672 delete from BEN_ENRT_RT_EFC
673 where EFC_ACTION_ID = l_efc_action_id;
674 --
675 delete from ben_prtt_rt_val_efc
676 where EFC_ACTION_ID = l_efc_action_id;
677 --
678 delete from PAY_ELEMENT_ENTRY_VALUES_F_efc
679 where EFC_ACTION_ID = l_efc_action_id;
680 --
681 delete from HR_EFC_WORKER_AUDITS
682 where exists (select efc_worker_id
683 from HR_EFC_WORKERS
684 where efc_action_id = l_efc_action_id);
685 --
686 delete from HR_EFC_WORKERS
687 where efc_action_id = l_efc_action_id;
688 --
689 delete from HR_EFC_PROCESS_COMPONENTS
690 where efc_action_id = l_efc_action_id;
691 --
692 delete from HR_EFC_ROUNDING_ERRORS
693 where efc_action_id = l_efc_action_id;
694 --
695 delete from hr_efc_actions
696 where efc_action_id = l_efc_action_id;
697 --
698 commit;
699 --
700 end if;
701 close c_getprevbgpactid;
702 --
703 -- Simulate a conversion for each business group
704 --
705 -- Write an EFC action for the BGP
706 --
707 ben_efc_functions.BGP_WriteEFCAction
708 (p_bgp_id => p_bgp_id
709 --
710 ,p_efc_action_id => p_efc_action_id
711 );
712 --
713 END BGP_SetupEFCAction;
714 --
715 /*
716 procedure BGP_GetEFCActDetails
717 (p_bgp_id in number
718 --
719 ,p_efcact_dets out nocopy gc_currefcact%rowtype
720 )
721 is
722 --
723 l_proc varchar2(1000) := 'BGP_GetEFCActDetails';
724 --
725
726 --
727 BEGIN
728 --
729 open gc_currefcact
730 (c_bgp_id => p_bgp_id
731 );
732 fetch gc_currefcact into p_efcact_dets;
733 close gc_currefcact;
734 --
735 END BGP_GetEFCActDetails;
736 --
737 */
738 function CurrCode_IsNCU
739 (p_curr_code in varchar2
740 )
741 return boolean
742 is
743 --
744 l_proc varchar2(1000) := 'CurrCode_IsNCU';
745 --
746 cursor c_ncu
747 (c_curr_code varchar2
748 )
749 is
750 select 1
751 from hr_ncu_currencies
752 where currency_code = c_curr_code;
753 --
754 l_ncu c_ncu%rowtype;
755 --
756 BEGIN
757 --
758 open c_ncu
759 (c_curr_code => p_curr_code
760 );
761 fetch c_ncu into l_ncu;
762 if c_ncu%notfound then
763 --
764 return FALSE;
765 --
766 else
767 --
768 return TRUE;
769 --
770 end if;
771 close c_ncu;
772 --
773 END CurrCode_IsNCU;
774 --
775 function UOM_IsCurrency
776 (p_uom in varchar2
777 )
778 return boolean
779 is
780 --
781 l_proc varchar2(1000) := 'UOM_IsCurrency';
782 --
783 cursor c_currency
784 (c_uom varchar2
785 )
786 is
787 select 1
788 from fnd_currencies
789 where currency_code = c_uom;
790 --
791 l_currency c_currency%rowtype;
792 --
793 BEGIN
794 --
795 open c_currency
796 (c_uom => p_uom
797 );
798 fetch c_currency into l_currency;
799 if c_currency%notfound then
800 --
801 return FALSE;
802 --
803 else
804 --
805 return TRUE;
806 --
807 end if;
808 close c_currency;
809 --
810 END UOM_IsCurrency;
811 --
812 procedure CompObject_GetParUom
813 (p_pgm_id in number
814 ,p_ptip_id in number
815 ,p_pl_id in number
816 ,p_plip_id in number
817 ,p_oipl_id in number
818 ,p_oiplip_id in number
819 ,p_eff_date in date
820 --
821 ,p_paruom out nocopy varchar2
822 ,p_faterr_code out nocopy varchar2
823 ,p_faterr_type out nocopy varchar2
824 )
825 is
826 --
827 l_proc varchar2(1000) := 'CompObject_GetParUom';
828 --
829 l_par_pgm_id number;
830 l_par_pl_id number;
831 --
832 cursor c_pgmdets
833 (c_pgm_id number
834 ,c_eff_date date
835 )
836 is
837 select pgm.pgm_uom
838 from ben_pgm_f pgm
839 where pgm.pgm_id = c_pgm_id
840 and c_eff_date
841 between pgm.effective_start_date and pgm.effective_end_date;
842 --
843 l_pgmdets c_pgmdets%rowtype;
844 --
845 cursor c_plnipdets
846 (c_pln_id number
847 ,c_eff_date date
848 )
849 is
850 select pln.nip_pl_uom
851 from ben_pl_f pln
852 where pln.pl_id = c_pln_id
853 and c_eff_date
854 between pln.effective_start_date and pln.effective_end_date;
855 --
856 l_plnipdets c_plnipdets%rowtype;
857 --
858 BEGIN
859 --
860 l_par_pgm_id := ben_global_functions.get_par_pgm_id
861 (p_pgm_id
862 ,p_ptip_id
863 ,p_pl_id
864 ,p_plip_id
865 ,p_oipl_id
866 ,p_oiplip_id
867 );
868 --
869 if l_par_pgm_id is null then
870 --
871 l_par_pl_id := ben_global_functions.get_par_plnip_id
872 (p_pl_id
873 ,p_oipl_id
874 );
875 --
876 end if;
877 --
878 if l_par_pgm_id is not null
879 then
880 --
881 open c_pgmdets
882 (c_pgm_id => l_par_pgm_id
883 ,c_eff_date => p_eff_date
884 );
885 fetch c_pgmdets into l_pgmdets;
886 if c_pgmdets%notfound then
887 --
888 p_faterr_code := 'NOPRVABRPGM';
889 p_faterr_type := 'DATACORRUPT';
890 --
891 end if;
892 close c_pgmdets;
893 --
894 p_paruom := l_pgmdets.pgm_uom;
895 --
896 elsif l_par_pgm_id is null
897 and l_par_pl_id is not null
898 then
899 --
900 open c_plnipdets
901 (c_pln_id => l_par_pl_id
902 ,c_eff_date => p_eff_date
903 );
904 fetch c_plnipdets into l_plnipdets;
905 if c_plnipdets%notfound then
906 --
907 p_faterr_code := 'NOPRVABRPLNIP';
908 p_faterr_type := 'DATACORRUPT';
909 --
910 end if;
911 close c_plnipdets;
912 --
913 p_paruom := l_plnipdets.nip_pl_uom;
914 --
915 end if;
916 --
917 END CompObject_GetParUom;
918 --
919 end ben_efc_functions;