[Home] [Help]
PACKAGE: APPS.BEN_EFC_ADJUSTMENTS
Source
1 package ben_efc_adjustments AUTHID CURRENT_USER as
2 /* $Header: beefcadj.pkh 115.12 2002/12/31 23:58:14 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 Who What?
13 --------- --------- ---------- --------------------------------------------
14 115.0 31-Jan-01 mhoyes Created.
15 115.1 01-Feb-01 mhoyes Added PRV and EEV adjustment covers.
16 115.2 06-Apr-01 mhoyes Total revamp 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 17-Aug-01 mhoyes Enhanced for BEN July patch.
20 115.8 31-Aug-01 mhoyes Enhanced for BEN July patch.
21 115.9 13-Sep-01 mhoyes Enhanced for BEN July patch.
22 115.10 04-Jan-02 mhoyes Enhanced for BEN G patchset.
23 115.12 30-Dec-02 mmudigon NOCOPY
24 -----------------------------------------------------------------------------
25 */
26 --
27 type g_adjustment_counts is record
28 (efcrow_count pls_integer
29 ,calfail_count pls_integer
30 ,calsucc_count pls_integer
31 ,rcoerr_count pls_integer
32 ,faterrs_count pls_integer
33 ,preadjexc_count pls_integer
34 ,dupconv_count pls_integer
35 ,conv_count pls_integer
36 ,actconv_count pls_integer
37 ,unconv_count pls_integer
38 ,tabrow_count pls_integer
39 ,olddata_count pls_integer
40 ,mod_count pls_integer
41 ,modovn1_count pls_integer
42 ,modovn2_count pls_integer
43 ,modovn3_count pls_integer
44 ,modovn4_count pls_integer
45 ,modovn5_count pls_integer
46 ,modovn6_count pls_integer
47 ,modovnov6_count pls_integer
48 ,multtransmod_count pls_integer
49 );
50 --
51 type g_rcoerr_values is record
52 (id number
53 ,esd date
54 ,eed date
55 ,bgp_id number
56 ,lud date
57 ,credt date
58 ,rco_name varchar2(1000)
59 ,sql_error varchar2(3000)
60 );
61 --
62 type g_rcoerr_values_tbl is table of g_rcoerr_values
63 index by binary_integer;
64 --
65 g_pep_rcoerr_val_set g_rcoerr_values_tbl;
66 g_epo_rcoerr_val_set g_rcoerr_values_tbl;
67 g_enb_rcoerr_val_set g_rcoerr_values_tbl;
68 g_epr_rcoerr_val_set g_rcoerr_values_tbl;
69 g_ecr_rcoerr_val_set g_rcoerr_values_tbl;
70 g_prv_rcoerr_val_set g_rcoerr_values_tbl;
71 g_eev_rcoerr_val_set g_rcoerr_values_tbl;
72 g_bpl_rcoerr_val_set g_rcoerr_values_tbl;
73 --
74 type g_failed_adj_values is record
75 (id number
76 ,id1 number
77 ,id2 number
78 ,code1 varchar2(30)
79 ,code2 varchar2(30)
80 ,code3 varchar2(30)
81 ,code4 varchar2(30)
82 ,esd date
83 ,eed date
84 ,bgp_id number
85 ,lud date
86 ,credt date
87 ,cre_by number
88 ,lu_by number
89 ,ovn number
90 ,old_val1 number
91 ,new_val1 number
92 ,old_val2 number
93 ,new_val2 number
94 ,val_type varchar2(100)
95 ,faterr_code varchar2(100)
96 ,faterr_type varchar2(100)
97 );
98 --
99 type g_failed_adj_values_tbl is table of g_failed_adj_values
100 index by binary_integer;
101 --
102 g_pep_success_adj_val_set g_failed_adj_values_tbl;
103 g_pep_failed_adj_val_set g_failed_adj_values_tbl;
104 g_pep_fatal_error_val_set g_failed_adj_values_tbl;
105 --
106 g_epo_success_adj_val_set g_failed_adj_values_tbl;
107 g_epo_failed_adj_val_set g_failed_adj_values_tbl;
108 g_epo_fatal_error_val_set g_failed_adj_values_tbl;
109 --
110 g_enb_success_adj_val_set g_failed_adj_values_tbl;
111 g_enb_failed_adj_val_set g_failed_adj_values_tbl;
112 g_enb_fatal_error_val_set g_failed_adj_values_tbl;
113 --
114 g_epr_success_adj_val_set g_failed_adj_values_tbl;
115 g_epr_failed_adj_val_set g_failed_adj_values_tbl;
116 g_epr_fatal_error_val_set g_failed_adj_values_tbl;
117 --
118 g_ecr_success_adj_val_set g_failed_adj_values_tbl;
119 g_ecr_failed_adj_val_set g_failed_adj_values_tbl;
120 g_ecr_fatal_error_val_set g_failed_adj_values_tbl;
121 --
122 g_prv_success_adj_val_set g_failed_adj_values_tbl;
123 g_prv_failed_adj_val_set g_failed_adj_values_tbl;
124 g_prv_fatal_error_val_set g_failed_adj_values_tbl;
125 --
126 g_eev_success_adj_val_set g_failed_adj_values_tbl;
127 g_eev_failed_adj_val_set g_failed_adj_values_tbl;
128 g_eev_fatal_error_val_set g_failed_adj_values_tbl;
129 --
130 g_bpl_success_adj_val_set g_failed_adj_values_tbl;
131 g_bpl_failed_adj_val_set g_failed_adj_values_tbl;
135 --
132 g_bpl_fatal_error_val_set g_failed_adj_values_tbl;
133 --
134 -- WHO trigger detection
136 type g_who_counts is record
137 (olddata_count pls_integer
138 ,olddata boolean
139 ,olddata12mths boolean
140 ,mod_count pls_integer
141 ,modovn1_count pls_integer
142 ,modovn2_count pls_integer
143 ,modovn3_count pls_integer
144 ,modovn4_count pls_integer
145 ,modovn5_count pls_integer
146 ,modovn6_count pls_integer
147 ,modovnov6_count pls_integer
148 ,multtransmod_count pls_integer
149 ,multtransmod boolean
150 );
151 --
152 -- PIL details
153 --
154 type g_pil_rowtype is record
155 (per_in_ler_id number
156 ,person_id number
157 ,lf_evt_ocrd_dt date
158 );
159 --
160 procedure DetectAppError
161 (p_sqlerrm in varchar2
162 ,p_abr_rt_mlt_cd in varchar2 default null
163 ,p_abr_val in number default null
164 ,p_abr_entr_val_at_enrt_flag in varchar2 default null
165 ,p_abr_id in number default null
166 ,p_eff_date in date default null
167 ,p_penepe_id in number default null
168 --
169 ,p_faterr_code out nocopy varchar2
170 ,p_faterr_type out nocopy varchar2
171 );
172 --
173 procedure DetectWhoInfo
174 (p_creation_date in date
175 ,p_last_update_date in date
176 ,p_object_version_number in number
177 --
178 ,p_who_counts in out nocopy g_who_counts
179 ,p_faterr_code out nocopy varchar2
180 ,p_faterr_type out nocopy varchar2
181 );
182 --
183 -- MLT Cd detection
184 --
185 type g_mlt_cd_counts is record
186 (cl_count pls_integer
187 ,clpflrng_count pls_integer
188 ,clrng_count pls_integer
189 ,flfx_count pls_integer
190 ,flfxpcl_count pls_integer
191 ,flpclrng_count pls_integer
192 ,flrng_count pls_integer
193 ,nsvu_count pls_integer
194 ,rl_count pls_integer
195 ,saaear_count pls_integer
196 ,cvg_count pls_integer
197 ,tplpc_count pls_integer
198 ,ttlcvg_count pls_integer
199 ,ttlprtt_count pls_integer
200 ,ap_count pls_integer
201 ,apandcvg_count pls_integer
202 ,clandcvg_count pls_integer
203 ,prnt_count pls_integer
204 ,prntandcvg_count pls_integer
205 ,sarec_count pls_integer
206 );
207 --
208 -- PIL detection
209 --
210 procedure DetectPILInfo
211 (p_person_id in number
212 ,p_per_in_ler_id in number
213 --
214 ,p_faterr_code out nocopy varchar2
215 );
216 --
217 -- BCOL row detection
218 --
219 procedure DetectBCOLRowInfo
220 (p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
221 ,p_effective_date in date
222 ,p_business_group_id in number
223 --
224 ,p_faterr_code out nocopy varchar2
225 );
226 --
227 -- VAPRO detection
228 --
229 cursor gc_vpfdets
230 (c_vpf_id in number
231 ,c_eff_date in date
232 )
233 is
234 select vpf.mlt_cd,
235 vpf.creation_date,
236 vpf.last_update_date,
237 vpf.VRBL_RT_TRTMT_CD
238 from ben_vrbl_rt_prfl_f vpf
239 where vpf.vrbl_rt_prfl_id = c_vpf_id
240 and c_eff_date
241 between vpf.effective_start_date
242 and vpf.effective_end_date;
243 --
244 -- Detect EPE ENB info
245 --
246 procedure DetectEPEENBInfo
247 (p_elig_per_elctbl_chc_id in number
248 ,p_enrt_bnft_id in number
249 --
250 ,p_detect_mode in varchar2 default null
251 --
252 ,p_currpil_row out nocopy g_pil_rowtype
253 ,p_currepe_row out nocopy ben_determine_rates.g_curr_epe_rec
254 ,p_faterr_code out nocopy varchar2
255 ,p_faterr_type out nocopy varchar2
256 );
257 --
258 procedure DetectVAPROInfo
259 (p_currepe_row in ben_determine_rates.g_curr_epe_rec
260 --
261 ,p_lf_evt_ocrd_dt in date
262 ,p_last_update_date in date
263 --
264 ,p_actl_prem_id in number default null
265 ,p_acty_base_rt_id in number default null
266 ,p_cvg_amt_calc_mthd_id in number default null
267 --
268 ,p_vpfdets out nocopy gc_vpfdets%rowtype
269 ,p_vpf_id out nocopy number
270 ,p_faterr_code out nocopy varchar2
271 ,p_faterr_type out nocopy varchar2
272 );
273 --
274 --
275 -- Detect a hard coded rounding code
276 --
277 procedure DetectRoundInfo
278 (p_rndg_cd in varchar2
279 ,p_rndg_rl in number
280 ,p_old_val in number
281 ,p_new_val in number
282 ,p_eff_date in date
283 --
284 ,p_faterr_code out nocopy varchar2
285 ,p_faterr_type out nocopy varchar2
286 );
287 --
288 -- Detect post conversion information
289 --
290 procedure DetectConvInfo
291 (p_ncucurr_code in varchar2
292 ,p_new_val in number
293 ,p_preconv_val in number
294 --
295 ,p_faterr_code out nocopy varchar2
296 ,p_faterr_type out nocopy varchar2
297 ,p_postconv_val out nocopy number
298 );
299 --
300 -- Insert a validation exception
301 --
305 ,p_ent_scode in varchar2
302 procedure insert_validation_exceptions
303 (p_val_set in g_failed_adj_values_tbl
304 ,p_efc_action_id in number
306 ,p_exception_type in varchar2
307 );
308 --
309 -- Detect invalid assignment information
310 --
311 cursor gc_perasg
312 (c_person_id in number
313 ,c_effective_date in date
314 )
315 is
316 select asg.payroll_id,
317 asg.pay_basis_id,
318 asg.assignment_id
319 from per_all_assignments_f asg
320 where asg.person_id = c_person_id
321 and asg.primary_flag = 'Y'
322 and c_effective_date
323 between asg.effective_start_date
324 and asg.effective_end_date;
325 --
326 procedure DetectInvAsg
327 (p_person_id in number
328 ,p_eff_date in date
329 --
330 ,p_perasg out nocopy gc_perasg%rowtype
331 ,p_noasgpay out nocopy boolean
332 );
333 --
334 procedure Insert_fndsession_row
335 (p_ses_date in date
336 );
337 --
338 procedure pep_adjustments
339 (p_validate in boolean default false
340 ,p_worker_id in number default null
341 ,p_action_id in number default null
342 ,p_total_workers in number default null
343 ,p_pk1 in number default null
344 ,p_chunk in number default null
345 ,p_efc_worker_id in number default null
346 --
347 ,p_valworker_id in number default null
348 ,p_valtotal_workers in number default null
349 --
350 ,p_business_group_id in number default null
351 --
352 ,p_adjustment_counts out nocopy g_adjustment_counts
353 );
354 --
355 procedure epo_adjustments
356 (p_validate in boolean default false
357 ,p_worker_id in number default null
358 ,p_action_id in number default null
359 ,p_total_workers in number default null
360 ,p_pk1 in number default null
361 ,p_chunk in number default null
362 ,p_efc_worker_id in number default null
363 --
364 ,p_valworker_id in number default null
365 ,p_valtotal_workers in number default null
366 --
367 ,p_business_group_id in number default null
368 --
369 ,p_adjustment_counts out nocopy g_adjustment_counts
370 );
371 --
372 procedure enb_adjustments
373 (p_validate in boolean default false
374 ,p_worker_id in number default null
375 ,p_action_id in number default null
376 ,p_total_workers in number default null
377 ,p_pk1 in number default null
378 ,p_chunk in number default null
379 ,p_efc_worker_id in number default null
380 --
381 ,p_valworker_id in number default null
382 ,p_valtotal_workers in number default null
383 --
384 ,p_business_group_id in number default null
385 --
386 ,p_adjustment_counts out nocopy g_adjustment_counts
387 );
388 --
389 procedure epr_adjustments
390 (p_validate in boolean default false
391 ,p_worker_id in number default null
392 ,p_action_id in number default null
393 ,p_total_workers in number default null
394 ,p_pk1 in number default null
395 ,p_chunk in number default null
396 ,p_efc_worker_id in number default null
397 --
398 ,p_valworker_id in number default null
399 ,p_valtotal_workers in number default null
400 --
401 ,p_business_group_id in number default null
402 --
403 ,p_adjustment_counts out nocopy g_adjustment_counts
404 );
405 --
406 procedure ecr_adjustments
407 (p_validate in boolean default false
408 ,p_worker_id in number default null
409 ,p_action_id in number default null
410 ,p_total_workers in number default null
411 ,p_pk1 in number default null
412 ,p_chunk in number default null
413 ,p_efc_worker_id in number default null
414 --
415 ,p_valworker_id in number default null
416 ,p_valtotal_workers in number default null
417 --
418 ,p_business_group_id in number default null
419 --
420 ,p_adjustment_counts out nocopy g_adjustment_counts
421 );
422 --
423 END ben_efc_adjustments;