1 PACKAGE BODY BEN_BEN_US_COBNL_XMLP_PKG AS
2 /* $Header: BEN_US_COBNLB.pls 120.1.12010000.2 2009/01/30 14:09:09 sagnanas ship $ */
3
4 function cf_1formula(admin_name in varchar2, loc_addr1 in varchar2, loc_Addr2 in varchar2,loc_Addr3 in varchar2, loc_city in varchar2, loc_state in varchar2, loc_zip in varchar2, loc_phone in varchar2,per_cm_prvdd_id in number) return varchar2 is
5 mvar varchar2(3500) ;
6 begin
7 begin
8 select admin_name || ', ' || loc_addr1 ||', ' ||
9 loc_Addr2 || decode(loc_addr2, null,'',', ') ||
10 loc_Addr3 || decode(loc_addr3, null,'',', ') ||
11 loc_city || ', ' || loc_state || ', ' ||
12 loc_zip || decode( loc_phone , null,'',', Phone : ')||loc_phone ||'.'
13 into mvar
14 from dual ;
15
16 update_pcd_sent_dt(p_per_cm_prvdd_id => per_cm_prvdd_id
17 ,p_effective_Date => p_effective_Date );
18 cp_effective_Date := p_effective_Date ;
19
20
21
22 return mvar ;
23 exception
24 when others then
25 return null ;
26 end ;
27
28 end;
29
30 procedure update_pcd_sent_dt(p_per_cm_prvdd_id in number
31 ,p_effective_date in date ) is
32
33 PRAGMA AUTONOMOUS_TRANSACTION;
34 BEGIN
35
36 update ben_per_cm_prvdd_f
37 set sent_Dt = p_effective_Date
38 where per_cm_prvdd_id = p_per_cm_prvdd_id
39 and sent_dt is null
40 and p_effective_Date between
41 effective_Start_date and
42 effective_end_Date ;
43 commit;
44 END;
45
46 function AfterReport return boolean is
47 begin
48 return (TRUE);
49 end;
50
51 function BeforeReport return boolean is
52 begin
53 return (TRUE);
54 end;
55
56 function CF_STANDARD_HEADERFormula return Number is
57 begin
58
59
60 return 1;
61 end;
62
63 function cf_cmcd_acty_rep_prdformula(cmcd_acty_ref_perd_cd in varchar2) return char is
64 cursor c1 is
65 select meaning from
66 hr_lookups hr
67 where hr.lookup_code = cmcd_acty_ref_perd_cd
68 and hr.lookup_type = 'BEN_ENRT_INFO_RT_FREQ' ;
69 l_return varchar2(80) ;
70
71 begin
72 if cmcd_acty_ref_perd_cd is not null then
73 open c1 ;
74 fetch c1 into l_return ;
75 close c1 ;
76 end if ;
77
78 return l_return ;
79
80
81 end;
82
83 --Functions to refer Oracle report placeholders--
84
85 Function cp_effective_date_p return date is
86 Begin
87 return cp_effective_date;
88 END;
89 Function CP_ler_text_p(ler_type in varchar2,ler_name in varchar2,pcm_ocrd_dt in date) return varchar2 is
90 Begin
91 if ler_type = 'SCHEDDU' or ler_type is null then
92 cp_ler_text := 'one of the following event,
93
94 [ ] End of employment
95 [ ] Reduction in hours of employment
96 [ ] Death of employee
97 [ ] Divorce or legal separation
98 [ ] Entitlement to Medicare
99 [ ] Loss of dependent child status ' ;
100 else
101 cp_ler_text := 'the qualifying event '|| ler_name ||
102 ' that occurred on '|| to_char(pcm_ocrd_dt,'DD fmMonth RRRR') ||'.';
103 end if ;
104
105 return CP_ler_text;
106 END;
107 END BEN_BEN_US_COBNL_XMLP_PKG ;
[Home] [Help]