1 package body ben_custom_formula as
2 /* $Header: bencustf.pkb 120.0 2005/05/28 03:55:51 appldev noship $ */
3 --
4 /*
5 This package is to be used to deliver custom formula examples.
6 see bencustf.sql for delivering formula functions.
7
8 Function overview
9 *****************
10 contact_valid
11 -------------
12 This function allows formula to test validity of
13 contact types. I.E. Do I have a spouse who is over
14 25 years old. If so if she causes a boundary event
15 then create a temporal event.
16 */
17 --
18 g_package varchar2(30) := 'ben_custom_formula.';
19 --
20 function get_age_change_life_event
21 (p_business_group_id in number,
22 p_effective_date in date) return number is
23 --
24 cursor c1 is
25 select ler.ler_id
26 from ben_ler_f ler
27 where ler.business_group_id = p_business_group_id
28 and ler.typ_cd = 'DRVDAGE'
29 and p_effective_date
30 between ler.effective_start_date
31 and ler.effective_end_date;
32 --
33 l_ler_id number;
34 l_proc varchar2(80) := g_package||'get_age_change_life_event';
35 --
36 begin
37 --
38 hr_utility.set_location('Entering :'||l_proc,10);
39 --
40 open c1;
41 --
42 fetch c1 into l_ler_id;
43 --
44 close c1;
45 --
46 hr_utility.set_location('Leaving :'||l_proc,10);
47 --
48 return l_ler_id;
49 --
50 end get_age_change_life_event;
51 --
52 function get_person_id
53 (p_assignment_id in number,
54 p_effective_date in date) return number is
55 --
56 l_person_id number;
57 --
58 cursor c1 is
59 select paf.person_id
60 from per_all_assignments_f paf
61 where paf.assignment_id = p_assignment_id
62 and p_effective_date
63 between paf.effective_start_date
64 and paf.effective_end_date;
65 --
66 l_proc varchar2(80) := g_package||'get_person_id';
67 --
68 begin
69 --
70 hr_utility.set_location('Entering :'||l_proc,10);
71 hr_utility.set_location('Assignment ID :'||p_assignment_id,10);
72 hr_utility.set_location('Effective Date :'||p_effective_date,10);
73 --
74 open c1;
75 --
76 fetch c1 into l_person_id;
77 --
78 close c1;
79 --
80 hr_utility.set_location('Leaving :'||l_proc,10);
81 --
82 hr_utility.set_location('Person ID :'||l_person_id,10);
83 return l_person_id;
84 --
85 end get_person_id;
86 --
87 function get_spouses_age
88 (p_person_id in number,
89 p_contact_type in varchar2,
90 p_effective_date in date) return date is
91 --
92 cursor c_per_spouse is
93 select per.date_of_birth
94 from per_contact_relationships ctr,
95 per_all_people_f per
96 where ctr.person_id = p_person_id
97 and per.person_id = ctr.contact_person_id
98 and ctr.personal_flag = 'Y'
99 and ctr.contact_type = 'S'
100 and p_effective_date
101 between per.effective_start_date
102 and per.effective_end_date;
103 --
104 l_date_of_birth date;
105 --
106 l_proc varchar2(80) := g_package||'get_spouses_age';
107 --
108 begin
109 --
110 hr_utility.set_location('Entering :'||l_proc,10);
111 hr_utility.set_location('p_contact_type :'||p_contact_type,10);
112 hr_utility.set_location('p_effective_date :'||p_effective_date,10);
113 hr_utility.set_location('p_person_id :'||p_person_id,10);
114 --
115 if p_contact_type = 'PS' then
116 --
117 open c_per_spouse;
118 --
119 fetch c_per_spouse into l_date_of_birth;
120 --
121 close c_per_spouse;
122 --
123 end if;
124 --
125 hr_utility.set_location('Leaving :'||l_proc,10);
126 hr_utility.set_location('dob :'||l_date_of_birth,10);
127 --
128 return l_date_of_birth;
129 --
130 end get_spouses_age;
131 --
132 function contact_valid
133 (p_assignment_id in number, -- Context
134 p_effective_date in date, -- Context
135 p_business_group_id in number, -- Context
136 p_pgm_id in number, -- Context
137 p_pl_typ_id in number, -- Context
138 p_pl_id in number, -- Context
139 p_opt_id in number, -- Context
140 p_contact_type in varchar2,
141 p_min_age_val in number,
142 p_max_age_val in number,
143 p_age_det_cd in varchar2, -- Add extra variables that may be needed
144 p_age_det_rl in number, -- Add extra variables that may be needed
145 p_age_uom in varchar2, -- Add extra variables that may be needed
146 p_rndg_cd in varchar2, -- Add extra variables that may be needed
147 p_rndg_rl in number, -- Add extra variables that may be needed
148 p_create_tmprl_event in varchar2 default 'N') return varchar2 is
149 --
150 l_proc varchar2(80) := g_package||'contact_valid';
151 l_person_id number;
152 l_dob date;
153 l_age number;
154 l_ler_id number;
155 l_ptnl_ler_for_per_id number;
156 l_object_version_number number;
157 --
158 begin
159 --
160 hr_utility.set_location('Entering '||l_proc,10);
161 --
162 -- We only support contact types that we support through the derived age
163 -- factor form (BENFCTRS).
164 --
165 if p_contact_type not in ('IA','P','PC1',
166 'PD1','PCO','PDO',
167 'PS','PCY','PDY') then
168 --
169 -- Person is not valid
170 --
171 return 'N';
172 --
173 end if;
174 --
175 -- Get the person_id first
176 --
177 l_person_id := get_person_id(p_assignment_id => p_assignment_id,
178 p_effective_date => p_effective_date);
179 --
180 hr_utility.set_location('Person ID is '||l_person_id,10);
181 --
182 -- Get the spouses date of birth
183 --
184 l_dob := get_spouses_age(p_person_id => l_person_id,
185 p_contact_type => p_contact_type,
186 p_effective_date => p_effective_date);
187 --
188 if l_dob is null then
189 --
190 hr_utility.set_location('Spouses Age is null',10);
191 return 'N';
192 --
193 end if;
194 --
195 -- Determine what date we are going to use in this case assume effective date
196 -- and calculate the persons age
197 --
198 l_age := months_between(p_effective_date,l_dob) / 12;
199 --
200 -- Now lets assume if the person we are trying to validate has an age that
201 -- falls out of the range then we should create a life event.
202 --
203 if p_create_tmprl_event = 'Y' and
204 (l_age < nvl(p_min_age_val,l_age) or
205 l_age >= nvl(p_max_age_val,l_age+1)) then
206 --
207 -- Create a temporal life event of age changed for the contact we are
208 -- processing.
209 --
210 l_ler_id := get_age_change_life_event
211 (p_business_group_id => p_business_group_id,
212 p_effective_date => p_effective_date);
213 --
214 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
215 (p_validate => false,
216 p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
217 p_lf_evt_ocrd_dt => p_effective_date,
218 p_ptnl_ler_for_per_stat_cd => 'DTCTD',
219 p_ler_id => l_ler_id,
220 p_person_id => l_person_id,
221 p_business_group_id => p_business_group_id,
222 p_object_version_number => l_object_version_number,
223 p_effective_date => p_effective_date,
224 p_program_application_id => fnd_global.prog_appl_id,
225 p_program_id => fnd_global.conc_program_id,
226 p_request_id => fnd_global.conc_request_id,
227 p_program_update_date => p_effective_date,
228 p_ntfn_dt => trunc(p_effective_date),
229 p_dtctd_dt => p_effective_date);
230 --
231 end if;
232 --
233 hr_utility.set_location('Leaving '||l_proc,10);
234 --
235 return 'Y';
236 --
237 end contact_valid;
238 --
239 end ben_custom_formula;
[Home] [Help]