[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_CWB
Source
1 package body BEN_EXT_CWB as
2 /* $Header: benxcwbn.pkb 120.1 2006/03/22 11:00:13 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ext_cwb.'; -- Global package name
9 --
10 -- procedure to initialize enrt globals - May, 99
11 -- ----------------------------------------------------------------------------
12 -- |------< initialize_enrt_globals >------------------------------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 Procedure initialize_group_globals IS
16 --
17 l_proc varchar2(72) := g_package||'initialize_group_globals';
18 --
19 Begin
20 --
21 hr_utility.set_location('Entering'||l_proc, 5);
22 --
23 ben_ext_person.g_CWB_Budget_PL_ID := null ;
24 ben_ext_person.g_CWB_Budget_Access := null ;
25 ben_ext_person.g_CWB_Budget_Approval := null ;
26 ben_ext_person.g_CWB_Budget_Approval_Date := null ;
27 ben_ext_person.g_CWB_Budget_Dist_Budget_Value := null ;
28 ben_ext_person.g_CWB_Budget_Due_Date := null ;
29 ben_ext_person.g_CWB_Budget_Group_Option_Name := null ;
30 ben_ext_person.g_CWB_Budget_Group_Plan_Name := null ;
31 ben_ext_person.g_CWB_Budget_Last_Updt_By := null ;
32 ben_ext_person.g_CWB_Budget_Last_Updt_dt := null ;
33 ben_ext_person.g_CWB_Budget_Population := null ;
34 ben_ext_person.g_CWB_Budget_Resv_Max_Value := null ;
35 ben_ext_person.g_CWB_Budget_Resv_Min_Value := null ;
36 ben_ext_person.g_CWB_Budget_Resv_Value := null ;
37 ben_ext_person.g_CWB_Budget_Resv_Val_Updt_By := null ;
38 ben_ext_person.g_CWB_Budget_Resv_Val_Updt_dt := null ;
39 ben_ext_person.g_CWB_Budget_Submit_date := null ;
40 ben_ext_person.g_CWB_Budget_Submit_Name := null ;
41 ben_ext_person.g_CWB_Budget_WS_Budget_Value := null ;
42 ben_ext_person.g_CWB_Dist_Budget_Default_Val := null ;
43 ben_ext_person.g_CWB_Dist_Budget_Issue_date := null ;
44 ben_ext_person.g_CWB_Dist_Budget_Issue_Value := null ;
45 ben_ext_person.g_CWB_Dist_Budget_Max_Value := null ;
46 ben_ext_person.g_CWB_Dist_Budget_Min_Value := null ;
47 ben_ext_person.g_CWB_Dist_Budget_Val_Updt_By := null ;
48 ben_ext_person.g_CWB_Dist_Budget_Val_Updt_dt := null ;
49 ben_ext_person.g_CWB_WS_Budget_Issue_Date := null ;
50 ben_ext_person.g_CWB_WS_Budget_Issue_Value := null ;
51 ben_ext_person.g_CWB_WS_Budget_Max_Value := null ;
52 ben_ext_person.g_CWB_WS_Budget_Min_Value := null ;
53 ben_ext_person.g_CWB_WS_Budget_Val_Updt_By := null ;
54 ben_ext_person.g_CWB_WS_Budget_Val_Updt_dt := null ;
55
56
57 --
58 hr_utility.set_location('Exiting'||l_proc, 15);
59 --
60 End initialize_group_globals;
61
62
63
64 Procedure initialize_rate_globals IS
65 --
66 l_proc varchar2(72) := g_package||'initialize_rate_globals';
67 --
68 Begin
69 --
70 hr_utility.set_location('Entering'||l_proc, 5);
71 ben_ext_person.g_CWB_Awrd_Elig_Flag := null ;
72 ben_ext_person.g_CWB_Awrd_Elig_Salary_Value := null ;
73 ben_ext_person.g_CWB_Awrd_Group_Option_Name := null ;
74 ben_ext_person.g_CWB_Awrd_Group_Plan_Name := null ;
75 ben_ext_person.g_CWB_Awrd_Plan_Name := null ;
76 ben_ext_person.g_CWB_Awrd_Option_Name := null ;
77 ben_ext_person.g_CWB_Awrd_Misc_Value1 := null ;
78 ben_ext_person.g_CWB_Awrd_Misc_Value2 := null ;
79 ben_ext_person.g_CWB_Awrd_Misc_Value3 := null ;
80 ben_ext_person.g_CWB_Awrd_Other_Comp_Value := null ;
81 ben_ext_person.g_CWB_Awrd_Recorded_Value := null ;
82 ben_ext_person.g_CWB_Awrd_Stated_Salary_Value := null ;
83 ben_ext_person.g_CWB_Awrd_Total_Comp_Value := null ;
84 ben_ext_person.g_CWB_Awrd_WS_Maximum_Value := null ;
85 ben_ext_person.g_CWB_Awrd_WS_Minimum_Value := null ;
86 ben_ext_person.g_CWB_Awrd_WS_Value := null ;
87 hr_utility.set_location('Exiting'||l_proc, 15);
88 --
89 End initialize_rate_globals;
90
91
92
93 PROCEDURE extract_person_groups
94 ( p_person_id in number,
95 p_per_in_ler_id in number,
96 p_ext_rslt_id in number,
97 p_ext_file_id in number,
98 p_data_typ_cd in varchar2,
99 p_ext_typ_cd in varchar2,
100 p_business_group_id in number,
101 p_effective_date in date) is
102
103 l_proc varchar2(72) := g_package||'extract_person_groups';
104
105 cursor c_cwg is
106 select
107 cwg.GROUP_PL_ID,
108 cwg.GROUP_OIPL_ID,
109 cwg.LF_EVT_OCRD_DT,
110 cwg.ACCESS_CD,
111 cwg.APPROVAL_CD,
112 cwg.APPROVAL_DATE,
113 cwg.DIST_BDGT_VAL,
114 cwg.DUE_DT,
115 pl.name pl_name ,
116 --oipl.name opt_name ,
117 cwg.LAST_UPDATED_BY,
118 cwg.LAST_UPDATE_DATE,
119 cwg.BDGT_POP_CD,
120 cwg.RSRV_MX_VAL,
121 cwg.RSRV_MN_VAL,
122 cwg.RSRV_VAL,
123 cwg.RSRV_VAL_LAST_UPD_BY,
124 cwg.RSRV_VAL_LAST_UPD_DATE,
125 cwg.SUBMIT_DATE,
126 cwg.SUBMIT_CD,
127 cwg.WS_BDGT_VAL,
128 cwg.DFLT_DIST_BDGT_VAL,
129 cwg.DIST_BDGT_ISS_DATE,
130 cwg.DIST_BDGT_ISS_VAL,
131 cwg.DIST_BDGT_MX_VAL,
132 cwg.DIST_BDGT_MN_VAL,
133 cwg.DIST_BDGT_VAL_LAST_UPD_BY,
134 cwg.DIST_BDGT_VAL_LAST_UPD_DATE,
135 cwg.WS_BDGT_ISS_DATE,
136 cwg.WS_BDGT_ISS_VAL,
137 cwg.WS_BDGT_MN_VAL,
138 cwg.WS_BDGT_MX_VAL,
139 cwg.WS_BDGT_VAL_LAST_UPD_DATE ,
140 cwg.WS_BDGT_VAL_LAST_UPD_BY
141 from BEN_CWB_PERSON_GROUPS cwg ,
142 ben_cwb_pl_dsgn pl
143 -- ben_cwb_pl_dsgn oipl
144 where GROUP_PER_IN_LER_ID = p_per_in_ler_id
145 and pl.pl_id = cwg.GROUP_PL_ID
146 and pl.oipl_id = -1 --- for the plan record oipl is -1
147 and cwg.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
148 --and oipl.oipl_id = cwg.GROUP_OIPL_ID
149 --and oipl.pl_id = cwg.GROUP_PL_ID
150 --and cwg.lf_evt_ocrd_dt = oipl.lf_evt_ocrd_dt
151
152 ;
153
154
155 cursor c_name (l_pl_id number ,
156 l_oipl_id number ,
157 l_date date ) is
158 select name
159 from ben_cwb_pl_dsgn
160 where pl_id = l_pl_id
161 and oipl_id = l_oipl_id
162 and lf_evt_ocrd_dt = l_date
163 ;
164
165
166 begin
167
168 hr_utility.set_location('Entering'||l_proc, 5);
169 --
170 initialize_group_globals ;
171 --
172 for l_cwg in c_cwg
173 Loop
174 ben_ext_person.g_CWB_Budget_PL_ID := l_cwg.GROUP_PL_ID ;
175 ben_ext_person.g_CWB_Budget_Access := l_cwg.ACCESS_CD ;
176 ben_ext_person.g_CWB_Budget_Approval := l_cwg.APPROVAL_CD ;
177 ben_ext_person.g_CWB_Budget_Approval_Date := l_cwg.APPROVAL_DATE ;
178 ben_ext_person.g_CWB_Budget_Dist_Budget_Value := l_cwg.DIST_BDGT_VAL ;
179 ben_ext_person.g_CWB_Budget_Due_Date := l_cwg.DUE_DT ;
180 --ben_ext_person.g_CWB_Budget_Group_Option_Name := l_cwg.opt_name ;
181 ben_ext_person.g_CWB_Budget_Group_Plan_Name := l_cwg.pl_name ;
182 ben_ext_person.g_CWB_Budget_Last_Updt_By := l_cwg.LAST_UPDATED_BY ;
183 ben_ext_person.g_CWB_Budget_Last_Updt_dt := l_cwg.LAST_UPDATE_DATE ;
184 ben_ext_person.g_CWB_Budget_Population := l_cwg.BDGT_POP_CD ;
185 ben_ext_person.g_CWB_Budget_Resv_Max_Value := l_cwg.RSRV_MX_VAL ;
186 ben_ext_person.g_CWB_Budget_Resv_Min_Value := l_cwg.RSRV_MN_VAL ;
187 ben_ext_person.g_CWB_Budget_Resv_Value := l_cwg.RSRV_VAL ;
188 ben_ext_person.g_CWB_Budget_Resv_Val_Updt_By := l_cwg.RSRV_VAL_LAST_UPD_BY ;
192 ben_ext_person.g_CWB_Budget_WS_Budget_Value := l_cwg.DFLT_DIST_BDGT_VAL ;
189 ben_ext_person.g_CWB_Budget_Resv_Val_Updt_dt := l_cwg.RSRV_VAL_LAST_UPD_DATE ;
190 ben_ext_person.g_CWB_Budget_Submit_date := l_cwg.SUBMIT_DATE ;
191 ben_ext_person.g_CWB_Budget_Submit_Name := l_cwg.SUBMIT_CD ;
193 ben_ext_person.g_CWB_Dist_Budget_Default_Val := l_cwg.DFLT_DIST_BDGT_VAL ;
194 ben_ext_person.g_CWB_Dist_Budget_Issue_date := l_cwg.DIST_BDGT_ISS_DATE ;
195 ben_ext_person.g_CWB_Dist_Budget_Issue_Value := l_cwg.DIST_BDGT_ISS_VAL ;
196 ben_ext_person.g_CWB_Dist_Budget_Max_Value := l_cwg.DIST_BDGT_MX_VAL ;
197 ben_ext_person.g_CWB_Dist_Budget_Min_Value := l_cwg.DIST_BDGT_MN_VAL ;
198 ben_ext_person.g_CWB_Dist_Budget_Val_Updt_By := l_cwg.DIST_BDGT_VAL_LAST_UPD_BY ;
199 ben_ext_person.g_CWB_Dist_Budget_Val_Updt_dt := l_cwg.DIST_BDGT_VAL_LAST_UPD_DATE ;
200 ben_ext_person.g_CWB_WS_Budget_Issue_Date := l_cwg.WS_BDGT_ISS_DATE ;
201 ben_ext_person.g_CWB_WS_Budget_Issue_Value := l_cwg.WS_BDGT_ISS_VAL ;
202 ben_ext_person.g_CWB_WS_Budget_Max_Value := l_cwg.WS_BDGT_MX_VAL ;
203 ben_ext_person.g_CWB_WS_Budget_Min_Value := l_cwg.WS_BDGT_MN_VAL ;
204 ben_ext_person.g_CWB_WS_Budget_Val_Updt_By := l_cwg.WS_BDGT_VAL_LAST_UPD_BY ;
205 ben_ext_person.g_CWB_WS_Budget_Val_Updt_dt := l_cwg.WS_BDGT_VAL_LAST_UPD_DATE ;
206
207 --- get the option namme
208 if l_cwg.group_oipl_id is not null and l_cwg.group_oipl_id <> -1 then
209
210 open c_name (l_cwg.group_pl_id ,
211 l_cwg.group_oipl_id ,
212 l_cwg.lf_evt_ocrd_dt) ;
213 fetch c_name into ben_ext_person.g_CWB_Budget_Group_Option_Name ;
214 close c_name ;
215
216 end if ;
217
218 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
219 p_ext_file_id => p_ext_file_id,
220 p_data_typ_cd => p_data_typ_cd,
221 p_ext_typ_cd => p_ext_typ_cd,
222 p_rcd_typ_cd => 'D',
223 p_low_lvl_cd => 'WG',
224 p_person_id => p_person_id,
225 p_business_group_id => p_business_group_id,
226 p_effective_date => p_effective_date
227 );
228 end Loop ;
229 hr_utility.set_location('Exiting'||l_proc, 15);
230
231
232 end extract_person_groups ;
233
234
235
236 PROCEDURE extract_person_rates
237 ( p_person_id in number,
238 p_per_in_ler_id in number,
239 p_ext_rslt_id in number,
240 p_ext_file_id in number,
241 p_data_typ_cd in varchar2,
242 p_ext_typ_cd in varchar2,
243 p_business_group_id in number,
244 p_effective_date in date) is
245
246
247 l_proc varchar2(72) := g_package||'extract_person_rates';
248
249 cursor c_cwr is
250 select
251 cpr.Group_pl_id,
252 cpr.pl_id ,
253 cpr.group_oipl_id,
254 cpr.oipl_id ,
255 pl1.name group_pl_name,
256 pl2.name Pl_name ,
257 cpr.ELIG_FLAG ,
258 cpr.ELIG_SAL_VAL ,
259 cpr.MISC1_VAL ,
260 cpr.MISC2_VAL ,
261 cpr.MISC3_VAL ,
262 cpr.OTH_COMP_VAL ,
263 cpr.REC_VAL ,
264 cpr.STAT_SAL_VAL ,
265 cpr.TOT_COMP_VAL ,
266 cpr.WS_MN_VAL ,
267 cpr.WS_MX_VAL ,
268 cpr.WS_VAL ,
269 cpr.lf_evt_ocrd_dt
270 From
271 ben_cwb_person_rates cpr ,
272 ben_cwb_pl_dsgn pl1 ,
273 ben_cwb_pl_dsgn pl2
274 where
275 cpr.group_per_in_ler_id = p_per_in_ler_id
276 and cpr.group_pl_id = pl1.pl_id
277 and pl1.oipl_id = -1
278 and cpr.lf_evt_ocrd_dt = pl1.lf_evt_ocrd_dt
279 and cpr.group_pl_id = pl2.pl_id
280 and pl2.oipl_id = -1
281 and cpr.lf_evt_ocrd_dt = pl2.lf_evt_ocrd_dt
282 ;
283
284 cursor c_oipl (p_oipl_id number,
285 p_pl_id number,
286 p_lf_evt_ocrd_dt date) is
287 select oipl.name
288 from ben_cwb_pl_dsgn oipl
289 where oipl.oipl_id = p_oipl_id
290 and oipl.pl_id = p_pl_id
291 and p_lf_evt_ocrd_dt = oipl.lf_evt_ocrd_dt
292 ;
293
294 begin
295
296 hr_utility.set_location('Entering'||l_proc, 5);
297 --
298 initialize_rate_globals() ;
299 --
300 for l_cwr in c_cwr
301 Loop
302
303 ben_ext_person.g_CWB_Awrd_Elig_Flag := l_Cwr.ELIG_FLAG ;
304 ben_ext_person.g_CWB_Awrd_Elig_Salary_Value := l_Cwr.ELIG_SAL_VAL ;
305 ben_ext_person.g_CWB_Awrd_Group_Plan_Name := l_cwr.group_pl_name ;
306 ben_ext_person.g_CWB_Awrd_Plan_Name := l_cwr.pl_name ;
307 ben_ext_person.g_CWB_Awrd_Misc_Value1 := l_Cwr.MISC1_VAL ;
308 ben_ext_person.g_CWB_Awrd_Misc_Value2 := l_Cwr.MISC2_VAL ;
309 ben_ext_person.g_CWB_Awrd_Misc_Value3 := l_Cwr.MISC3_VAL ;
310 ben_ext_person.g_CWB_Awrd_Other_Comp_Value := l_Cwr.OTH_COMP_VAL ;
311 ben_ext_person.g_CWB_Awrd_Recorded_Value := l_Cwr.REC_VAL ;
312 ben_ext_person.g_CWB_Awrd_Stated_Salary_Value := l_Cwr.STAT_SAL_VAL ;
316 ben_ext_person.g_CWB_Awrd_WS_Value := l_Cwr.WS_VAL ;
313 ben_ext_person.g_CWB_Awrd_Total_Comp_Value := l_Cwr.TOT_COMP_VAL ;
314 ben_ext_person.g_CWB_Awrd_WS_Maximum_Value := l_Cwr.WS_MN_VAL ;
315 ben_ext_person.g_CWB_Awrd_WS_Minimum_Value := l_Cwr.WS_MX_VAL ;
317
318 if nvl(l_cwr.group_oipl_id,-1) <> -1 then
319 open c_oipl(l_cwr.group_oipl_id,l_cwr.group_pl_id , l_cwr.lf_evt_ocrd_dt) ;
320 fetch c_oipl into ben_ext_person.g_CWB_Awrd_Group_Option_Name ;
321 close c_oipl ;
322 end if ;
323
324 if nvl(l_cwr.oipl_id,-1) <> -1 then
325 open c_oipl(l_cwr.oipl_id,l_cwr.pl_id , l_cwr.lf_evt_ocrd_dt) ;
326 fetch c_oipl into ben_ext_person.g_CWB_Awrd_Option_Name ;
327 close c_oipl ;
328 end if ;
329
330
331 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
332 p_ext_file_id => p_ext_file_id,
333 p_data_typ_cd => p_data_typ_cd,
334 p_ext_typ_cd => p_ext_typ_cd,
335 p_rcd_typ_cd => 'D',
336 p_low_lvl_cd => 'WR',
337 p_person_id => p_person_id,
338 p_business_group_id => p_business_group_id,
339 p_effective_date => p_effective_date
340 );
341
342 end Loop ;
343 hr_utility.set_location('Exiting'||l_proc, 15);
344
345
346 end extract_person_rates ;
347
348
349
350
351 --
352 END; -- package