1 PACKAGE BODY PAY_FR_CPAM_PREPROCESSOR AS
2 /* $Header: pyfrcpam.pkb 120.0.12000000.2 2007/02/27 13:47:25 spendhar noship $ */
3 g_info_element_type_id pay_element_types_f.element_type_id%type;
4 g_info_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
5 g_info_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
6 g_info_days_iv_id pay_input_values_f.input_value_id%type;
7 --g_info_subrogated_iv_id pay_input_values_f.input_value_id%type;
8 g_info_gross_amt_iv_id pay_input_values_f.input_value_id%type;
9 g_info_net_amt_iv_id pay_input_values_f.input_value_id%type;
10 g_info_gross_rt_iv_id pay_input_values_f.input_value_id%type;
11 g_info_net_rt_iv_id pay_input_values_f.input_value_id%type;
12
13 g_proc_element_type_id pay_element_types_f.element_type_id%type;
14 g_proc_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
15 g_proc_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
16 g_proc_days_iv_id pay_input_values_f.input_value_id%type;
17 --g_proc_subrogated_iv_id pay_input_values_f.input_value_id%type;
18 g_proc_gross_amt_iv_id pay_input_values_f.input_value_id%type;
19 g_proc_net_amt_iv_id pay_input_values_f.input_value_id%type;
20 g_proc_gross_rt_iv_id pay_input_values_f.input_value_id%type;
21 g_proc_net_rt_iv_id pay_input_values_f.input_value_id%type;
22
23 CURSOR C_info_entry(p_element_entry_id IN NUMBER) IS
24 SELECT 'X' dum
25 FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet
26 WHERE pee.element_link_id = pel.element_link_id
27 AND pet.element_type_id = pel.element_type_id
28 AND pet.element_name = 'FR_SICKNESS_CPAM_INFO'
29 AND pee.element_entry_id = p_element_entry_id;
30
31 -- Query input value ids
32 CURSOR C_iv_ids(p_element_type IN VARCHAR2
33 ,p_effective_start_date IN DATE) IS
34 SELECT max(e.element_type_id)
35 ,max(decode(i.name,'Payment From Date',i.input_value_id,null))
36 ,max(decode(i.name,'Payment To Date',i.input_value_id,null))
37 ,max(decode(i.name,'Days',i.input_value_id,null))
38 --,max(decode(i.name,'Subrogated',i.input_value_id,null))
39 ,max(decode(i.name,'Gross Amount',i.input_value_id,null))
40 ,max(decode(i.name,'Net Amount',i.input_value_id,null))
41 ,max(decode(i.name,'Gross Daily Rate',i.input_value_id,null))
42 ,max(decode(i.name,'Net Daily Rate',i.input_value_id,null))
43 FROM pay_element_types_f e,
44 pay_input_values_f i
45 WHERE e.element_name = p_element_type
46 and e.legislation_code = 'FR'
47 and e.element_type_id = i.element_type_id
48 and p_effective_start_date between e.effective_start_date and e.effective_end_date
49 and p_effective_start_date between i.effective_start_date and i.effective_end_date;
50
51 --
52 CURSOR C_input_values(p_element_entry_id IN NUMBER) IS
53 SELECT max(decode(eev.input_value_id,g_info_pymt_frm_dt_iv_id
54 ,to_date(eev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')
55 ,NULL)) Frm_dt,
56 max(decode(eev.input_value_id,g_info_pymt_to_dt_iv_id
57 ,to_date(eev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')
58 ,NULL)) To_dt,
59 max(decode(eev.input_value_id,g_info_days_iv_id
60 ,to_number(eev.screen_entry_value)
61 ,NULL)) Days,
62 -- max(decode(eev.input_value_id,g_info_subrogated_iv_id,eev.screen_entry_value,NULL)) Subrogated,
63 max(decode(eev.input_value_id,g_info_gross_amt_iv_id
64 ,to_number(eev.screen_entry_value)
65 ,NULL)) Gross_Amount,
66 max(decode(eev.input_value_id,g_info_net_amt_iv_id
67 ,to_number(eev.screen_entry_value)
68 ,NULL)) Net_Amount,
69 max(decode(eev.input_value_id,g_info_gross_rt_iv_id
70 ,to_number(eev.screen_entry_value)
71 ,NULL)) Gross_Daily_Rate,
72 max(decode(eev.input_value_id,g_info_net_rt_iv_id
73 ,to_number(eev.screen_entry_value)
74 ,NULL)) Net_Daily_Rate
75 FROM pay_element_types_f pet, pay_element_entries_f pee,
76 pay_input_values_f piv, pay_element_entry_values_f eev
77 WHERE pee.element_entry_id = eev.element_entry_id
78 and pet.element_type_id = piv.element_type_id
79 and piv.input_value_id = eev.input_value_id
80 and pet.element_name = 'FR_SICKNESS_CPAM_INFO'
81 and pee.element_entry_id = p_element_entry_id;
82 --
83 CURSOR C_element_link(p_element_type_id IN NUMBER,p_source_element_link_id IN NUMBER, p_effective_start_date IN DATE) IS
84 SELECT pel.element_link_id
85 FROM pay_element_types_f pet, pay_element_links_f pel, pay_element_links_f pel1
86 WHERE pet.element_type_id = pel.element_type_id
87 AND pel.business_group_id = pel1.business_group_id
88 AND pet.element_type_id = p_element_type_id
89 AND p_effective_start_date BETWEEN pel.effective_start_date AND pel.effective_end_date
90 AND pel1.element_link_id = p_source_element_link_id;
91
92 --
93
94 PROCEDURE CPAM_INFO_CREATE(
95 p_effective_start_date IN DATE
96 ,p_effective_end_date IN DATE
97 ,p_element_entry_id IN NUMBER
98 ,p_assignment_id IN NUMBER
99 ,p_element_link_id IN NUMBER
100 ,p_entry_type IN VARCHAR2
101 ,p_date_earned IN DATE
102
103 ) IS
104 rec_input_values c_input_values%ROWTYPE;
105 rec_info_entry c_info_entry%ROWTYPE;
106 rec_element_link c_element_link%ROWTYPE;
107
108 CURSOR C_info_entries(p_assignment_id IN Number, p_start_date IN Date, p_end_date IN Date, p_curr_entry_id IN Number) IS
109 SELECT pev.effective_start_date
110 FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet,
111 pay_input_values_f piv, pay_element_entry_values_f pev
112 WHERE pee.element_link_id = pel.element_link_id
113 AND pet.element_type_id = pel.element_type_id
114 AND pee.element_entry_id= pev.element_entry_id
115 AND pet.element_type_id = piv.element_type_id
116 AND pev.input_value_id = piv.input_value_id
117 AND piv.name IN ('Payment From Date', 'Payment To Date')
118 AND pee.assignment_id = p_assignment_id
119 AND fnd_date.canonical_to_date(pev.screen_entry_value) BETWEEN p_start_date AND p_end_date
120 AND pet.element_name = 'FR_SICKNESS_CPAM_INFO'
121 AND pee.element_entry_id <> p_curr_entry_id;
122
123 -- Get absences occuring on or within the payment dates (even overlapping)
124 CURSOR C_absences(p_assignment_id IN Number, p_start_date IN Date, p_end_date IN Date) IS
125 SELECT paa.person_id
126 , paa.absence_attendance_id
127 , to_number(paa.abs_information1) parent_absence_id
128 , paa.date_start date_start
129 , paa.date_end date_end
130 , (paa.date_end - paa.date_start + 1) duration
131 , nvl(decode(paa.abs_information1,NULL,paa.abs_information8,
132 paa_p.abs_information8),'N') pay_estimate
133 , fnd_date.canonical_to_date(decode(paa.abs_information1,NULL,paa.abs_information7,
134 paa_p.abs_information7)) ijss_ineligible_date
135 FROM per_absence_attendances paa,
136 per_absence_attendances paa_p,
137 per_all_people_f pap,
138 per_all_assignments_f pasg
139 WHERE pasg.person_id = pap.person_id
140 AND pap.person_id = paa.person_id
141 AND paa.abs_information1 = paa_p.absence_attendance_id(+)
142 AND p_start_date between pasg.effective_start_date and pasg.effective_end_date
143 AND p_start_date between pap.effective_start_date and pap.effective_end_date
144 AND ( ( (paa.date_end between p_start_date and p_end_date)
145 OR (paa.date_start between p_start_date and p_end_date))
146 OR
147 ( (p_start_date between paa.date_start and paa.date_end)
148 OR (p_end_date between paa.date_start and paa.date_end)))
149 AND pasg.assignment_id = p_assignment_id
150 AND paa.abs_information_category = 'FR_S'
151 ORDER BY paa.date_start ;
152
153 CURSOR C_time_periods(p_assignment_id IN Number, p_start_date IN Date, p_end_date IN Date) IS
154 SELECT ptp.start_date, ptp.end_date
155 FROM per_time_periods ptp, per_all_assignments_f pasg
156 WHERE pasg.payroll_id = ptp.payroll_id
157 AND p_start_date between pasg.effective_start_date and pasg.effective_end_date
158 AND (ptp.start_date BETWEEN p_start_date AND p_end_date
159 OR ptp.end_date BETWEEN p_start_date AND p_end_date)
160 AND pasg.assignment_id = p_assignment_id;
161
162 CURSOR C_proc_entry(p_element_entry_id IN NUMBER) IS
163 SELECT 'X' dum
164 FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet
165 WHERE pee.element_link_id = pel.element_link_id
166 AND pet.element_type_id = pel.element_type_id
167 AND pet.element_name = 'FR_SICKNESS_CPAM_PROCESS'
168 AND pee.creator_type IN ('F','H')
169 AND pee.element_entry_id = p_element_entry_id;
170
171
172 rec_info_entries C_info_entries%ROWTYPE;
173 rec_proc_entry C_proc_entry%ROWTYPE;
174
175 l_process_element_entry_id number;
176 l_effective_start_date date;
177 l_effective_end_date date;
178 l_entry_information_category varchar2(30);
179
180 cnt_absences number;
181 cnt_periods number;
182
183 BEGIN
184 --
185 /* Added for GSI Bug 5472781 */
186 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
187 hr_utility.set_location('Leaving : pay_fr_cpam_preprocessor.cpam_info_create' , 10);
188 return;
189 END IF;
190 --
191
192 hr_utility.trace('Preproc - INS ');
193
194 -- Checking if current entry is of type 'FR_SICKNESS_CPAM_PROCESS'
195 -- and is being made by the EE or BEE form
196 IF C_proc_entry%ISOPEN THEN
197 CLOSE C_proc_entry;
198 END IF;
199 OPEN C_proc_entry(p_element_entry_id);
200 FETCH C_proc_entry INTO rec_proc_entry;
201 IF C_proc_entry%FOUND THEN
202 fnd_message.set_name ('PAY', 'PAY_75075_CPAM_PROCESS_INS_NA');
203 fnd_message.raise_error;
204 END IF;
205
206 --
207 IF C_info_entry%ISOPEN THEN
208 CLOSE C_info_entry;
209 END IF;
210 OPEN C_info_entry(p_element_entry_id);
211 FETCH C_info_entry INTO rec_info_entry;
212 IF C_info_entry%FOUND THEN
213 hr_utility.trace('Preproc - INS - INFO entry found');
214 l_effective_start_date := p_effective_start_date;
215 l_effective_end_date := p_effective_end_date;
216 l_entry_information_category := 'FR_CPAM PROCESS INFORMATION';
217
218 IF C_iv_ids%ISOPEN THEN
219 CLOSE C_iv_ids;
220 END IF;
221 OPEN C_iv_ids('FR_SICKNESS_CPAM_INFO', p_effective_start_date);
222 FETCH C_iv_ids
223 INTO g_info_element_type_id,
224 g_info_pymt_frm_dt_iv_id,
225 g_info_pymt_to_dt_iv_id,
226 g_info_days_iv_id,
227 --g_info_subrogated_iv_id,
228 g_info_gross_amt_iv_id,
229 g_info_net_amt_iv_id,
230 g_info_gross_rt_iv_id,
231 g_info_net_rt_iv_id;
232 CLOSE C_iv_ids;
233
234 IF C_input_values%ISOPEN THEN
235 CLOSE C_input_values;
236 END IF;
237 -- Fetch input values for the entry
238 OPEN C_input_values(p_element_entry_id);
239 FETCH C_input_values INTO rec_input_values;
240 CLOSE C_input_values;
241
242 hr_utility.trace(' INS Fetched Input values');
243 IF rec_input_values.Days IS NULL THEN
244 rec_input_values.Days := (rec_input_values.To_Dt - rec_input_values.Frm_dt + 1);
245 /*
246 -- Since Days is an non enterable input value, wont enter this condn.
247 ELSE
248 -- Validate entry values
249 -- 1) Days
250 IF rec_input_values.Days > (rec_input_values.To_Dt - rec_input_values.Frm_dt + 1) THEN
251 fnd_message.set_name ('PAY', 'PAY_75070_CPAM_INFO_INV_DAYS');
252 fnd_message.raise_error;
253 END IF;
254 */
255 END IF;
256 -- 2) Invalid Gross Amount
257 IF rec_input_values.Gross_Amount <> (rec_input_values.Gross_Daily_Rate * rec_input_values.Days) THEN
258 fnd_message.set_name ('PAY', 'PAY_75071_CPAM_INFO_INV_GR_AMT');
259 fnd_message.raise_error;
260 END IF;
261 -- 3) Invalid Net Amount
262 IF rec_input_values.Net_Amount <> (rec_input_values.Net_Daily_Rate * rec_input_values.Days) THEN
263 fnd_message.set_name ('PAY', 'PAY_75072_CPAM_INFO_INV_NT_AMT');
264 fnd_message.raise_error;
265 END IF;
266 hr_utility.trace(' INS Fetched and cross validated Input values');
267 -- 4) Invalid dates
268 IF C_info_entries%ISOPEN THEN
269 CLOSE C_info_entries;
270 END IF;
271 OPEN C_info_entries(p_assignment_id,rec_input_values.frm_dt,rec_input_values.to_dt,p_element_entry_id);
272 FETCH C_info_entries INTO rec_info_entries;
273 IF C_info_entries%FOUND THEN
274 fnd_message.set_name ('PAY','PAY_75074_CPAM_INFO_INV_DATES');
275 fnd_message.set_token('PAY_PERIOD',to_char(rec_info_entries.effective_start_date));
276 fnd_message.raise_error;
277 END IF;
278 CLOSE C_info_entries;
279 hr_utility.trace(' INS Checked for overlapping INFO entries');
280 IF C_iv_ids%ISOPEN THEN
281 CLOSE C_iv_ids;
282 END IF;
283
284 OPEN C_iv_ids('FR_SICKNESS_CPAM_PROCESS', p_effective_start_date);
285 FETCH C_iv_ids
286 INTO g_proc_element_type_id,
287 g_proc_pymt_frm_dt_iv_id,
288 g_proc_pymt_to_dt_iv_id,
289 g_proc_days_iv_id,
290 --g_proc_subrogated_iv_id,
291 g_proc_gross_amt_iv_id, -- NULL
292 g_proc_net_amt_iv_id, -- NULL
293 g_proc_gross_rt_iv_id,
294 g_proc_net_rt_iv_id;
295 CLOSE C_iv_ids;
296
297 OPEN C_element_link(g_proc_element_type_id, p_element_link_id, p_effective_start_date);
298 FETCH C_element_link INTO rec_element_link;
299 CLOSE C_element_link;
300
301 hr_utility.trace(' INS Fetched and checked Input values');
302 -- Query sickness absences (and Pay IJSS Estimate) between the payments start and end date
303 cnt_absences := 0;
304 FOR rec_absences IN C_absences(p_assignment_id, rec_input_values.Frm_Dt, rec_input_values.To_Dt)
305 LOOP
306 cnt_absences := cnt_absences + 1;
307 hr_utility.trace(' INS Absences found :'||cnt_absences||' with Estimate set to ='||rec_absences.pay_estimate);
308 IF rec_absences.pay_estimate = 'N' THEN
309 hr_utility.trace(' Absences to be preprocessed for:'||cnt_absences);
310 cnt_periods := 0;
311
312 --Bug #3040003
313 IF (rec_absences.ijss_ineligible_date <= rec_input_values.Frm_Dt
314 OR rec_absences.ijss_ineligible_date <= rec_input_values.To_Dt) THEN
315 fnd_message.set_name ('PAY','PAY_75078_CPAM_INFO_IJSS_IN_DT');
316 fnd_message.set_token('INELIG_DT',to_char(rec_absences.ijss_ineligible_date));
317 fnd_message.raise_error;
318 END IF;
319
320 -- Does the absence cross a period boundary?
321 -- Query number of per_time_periods from the payroll on the assignment
322 FOR rec_time_periods IN C_time_periods(p_assignment_id, GREATEST(rec_input_values.Frm_Dt,rec_absences.date_start), LEAST(rec_input_values.To_Dt,rec_absences.date_end))
323 LOOP
324 cnt_periods := cnt_periods + 1;
325 hr_utility.trace(' Period boundaries found :'||cnt_periods);
326 -- Creating entry with relevant dates
327 l_process_element_entry_id := NULL;
328
329 --l_prev_start_date := GREATEST(rec_absences.date_start,rec_input_values.Frm_dt);
330 hr_utility.trace(' INS Creating entry for relevant Period :'||cnt_periods);
331 hr_utility.trace(' With dates: start='||GREATEST(rec_time_periods.start_date,rec_absences.date_start,rec_input_values.Frm_dt)||
332 ' end='||LEAST(rec_time_periods.end_date,rec_absences.date_end, rec_input_values.To_dt));
333 hr_entry_api.insert_element_entry
334 (
335 p_effective_start_date => l_effective_start_date,
339 --
336 p_effective_end_date => l_effective_end_date,
337 --
338 -- Element Entry Table
340 p_element_entry_id => l_process_element_entry_id,
341 p_assignment_id => p_assignment_id,
342 p_element_link_id => rec_element_link.element_link_id,
343 p_creator_type => 'S',
344 p_entry_type => p_entry_type,
345 p_subpriority => to_number(substr(to_char(GREATEST(rec_time_periods.start_date,rec_absences.date_start,rec_input_values.Frm_dt),'J'),4,4)),
346 p_date_earned => p_date_earned,
347 -- Element Entry Values Table
348 --
349 p_input_value_id1 => g_proc_pymt_frm_dt_iv_id,
350 p_input_value_id2 => g_proc_pymt_to_dt_iv_id,
351 --p_input_value_id3 => g_proc_subrogated_iv_id,
352 p_input_value_id4 => g_proc_gross_rt_iv_id,
353 p_input_value_id5 => g_proc_net_rt_iv_id,
354 p_input_value_id6 => g_proc_days_iv_id,
355 p_entry_value1 => GREATEST(rec_time_periods.start_date,rec_absences.date_start,rec_input_values.Frm_dt),
356 p_entry_value2 => LEAST(rec_time_periods.end_date,rec_absences.date_end, rec_input_values.To_dt),
357 --p_entry_value3 => hr_general.decode_lookup('YES_NO', rec_input_values.Subrogated),
358 p_entry_value4 => rec_input_values.Gross_daily_rate,
359 p_entry_value5 => rec_input_values.Net_daily_rate,
360 p_entry_value6 => (LEAST(rec_time_periods.end_date,rec_absences.date_end, rec_input_values.To_dt) -
361 GREATEST(rec_time_periods.start_date,rec_absences.date_start,rec_input_values.Frm_dt)) +1,
362 p_entry_information_category=> l_entry_information_category,
363 p_entry_information1 => p_element_entry_id,
364 p_entry_information2 => rec_absences.absence_attendance_id
365
366 );
367 hr_utility.trace(' INS Created entry for relevant Period :'||cnt_periods||' with id ='||l_process_element_entry_id);
368 END LOOP; -- Time period Loop
369
370 IF cnt_periods = 0 THEN -- No period break during the absence, create a process entry for whole absence
371 --
372 hr_utility.trace(' No period boundaries :Creating entry for relevant absence');
373 l_process_element_entry_id := NULL;
374
375 --l_prev_start_date := GREATEST(rec_absences.date_start,rec_input_values.Frm_dt);
376 hr_utility.trace(' Creating entry for relevant Absence id='||rec_absences.absence_attendance_id);
377 hr_entry_api.insert_element_entry
378 (
379 p_effective_start_date => l_effective_start_date,
380 p_effective_end_date => l_effective_end_date,
381 --
382 -- Element Entry Table
383 --
384 p_element_entry_id => l_process_element_entry_id,
385 p_assignment_id => p_assignment_id,
386 p_element_link_id => rec_element_link.element_link_id,
387 p_creator_type => 'S',
388 p_entry_type => p_entry_type,
389 p_subpriority => to_number(substr(to_char(GREATEST(rec_absences.date_start,rec_input_values.Frm_dt),'J'),4,4)),
390 p_date_earned => p_date_earned,
391 -- Element Entry Values Table
392 --
393 p_input_value_id1 => g_proc_pymt_frm_dt_iv_id,
394 p_input_value_id2 => g_proc_pymt_to_dt_iv_id,
395 --p_input_value_id3 => g_proc_subrogated_iv_id,
396 p_input_value_id4 => g_proc_gross_rt_iv_id,
397 p_input_value_id5 => g_proc_net_rt_iv_id,
398 p_input_value_id6 => g_proc_days_iv_id,
399 p_entry_value1 => GREATEST(rec_absences.date_start,rec_input_values.Frm_dt),
400 p_entry_value2 => LEAST(rec_absences.date_end, rec_input_values.To_dt),
401 --p_entry_value3 => hr_general.decode_lookup('YES_NO', rec_input_values.Subrogated),
402 p_entry_value4 => rec_input_values.Gross_daily_rate,
403 p_entry_value5 => rec_input_values.Net_daily_rate,
404 p_entry_value6 => (LEAST(rec_absences.date_end, rec_input_values.To_dt) - GREATEST(rec_absences.date_start,rec_input_values.Frm_dt)) +1,
405 p_entry_information_category=> l_entry_information_category,
406 p_entry_information1 => p_element_entry_id,
407 p_entry_information2 => rec_absences.absence_attendance_id
408 );
409 hr_utility.trace(' INS Created entry for relevant Absence with id='||l_process_element_entry_id);
410 --
411 END IF; -- Period breaks = 0 Chk
412
413 END IF; -- Pay IJSS Estimate chk
414 END LOOP; -- Absences Loop
415
416 IF cnt_absences = 0 THEN
417 fnd_message.set_name ('PAY', 'PAY_75073_CPAM_INFO_DTS_MIS');
418 fnd_message.raise_error;
419 END IF;
420 END IF; -- Info Entry Chk
421
422 -- #3030587
423 -- Setting value for INFO element's input value Days
424 UPDATE PAY_ELEMENT_ENTRY_VALUES_F SET screen_entry_value = rec_input_values.Days
425 WHERE element_entry_id = p_element_entry_id
426 AND input_value_id = g_info_days_iv_id;
427
431 hr_utility.trace(' Preproc INS Finished');
428 IF C_info_entry%ISOPEN THEN
429 CLOSE C_info_entry;
430 END IF;
432 --hr_utility.trace_off;
433 END CPAM_INFO_CREATE;
434
435
436 --
437 PROCEDURE CPAM_INFO_UPDATE(
438 p_effective_start_date IN DATE
439 ,p_effective_end_date IN DATE
440 ,p_element_entry_id IN NUMBER
441 ,p_date_earned IN DATE
442 ,p_entry_type_o IN VARCHAR2
443 ,p_effective_start_date_o IN DATE
444 ,p_assignment_id_o IN NUMBER
445 ,p_element_link_id_o IN NUMBER
446 ,p_date_earned_o IN DATE
447 ) IS
448 --
449 rec_info_entry c_info_entry%ROWTYPE;
450 --
451 BEGIN
452 --
453 /* Added for GSI Bug 5472781 */
454 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
455 hr_utility.set_location('Leaving : pay_fr_cpam_preprocessor.cpam_info_update' , 10);
456 return;
457 END IF;
458 --
459
460 hr_utility.trace('Preproc - UPD Started');
461 IF C_info_entry%ISOPEN THEN
462 CLOSE C_info_entry;
463 END IF;
464 OPEN C_info_entry(p_element_entry_id);
465 FETCH C_info_entry INTO rec_info_entry;
466 IF C_info_entry%FOUND THEN
467 hr_utility.trace(' Preproc - UPD - INFO entry found');
468 hr_utility.trace(' UPD - Deleting related process element entries');
469 CPAM_INFO_DELETE(
470 p_element_entry_id => p_element_entry_id,
471 p_element_link_id_o => p_element_link_id_o,
472 p_effective_start_date_o => p_effective_start_date_o,
473 p_assignment_id_o => p_assignment_id_o,
474 p_datetrack_mode => 'ZAP'
475 );
476 hr_utility.trace(' UPD - Finished deleting related process element entries');
477
478 hr_utility.trace(' UPD - Recreating process element entries');
479 hr_utility.trace(' UPD - With start dt='||p_effective_start_date||' end dt='||p_effective_end_date);
480 hr_utility.trace(' UPD - entry id='||p_element_entry_id||' assgt id='||p_assignment_id_o||' link id='||p_element_link_id_o);
481 hr_utility.trace(' UPD - entry type='||p_entry_type_o||' dt earned'||p_date_earned);
482
483 CPAM_INFO_CREATE(
484 p_effective_start_date => p_effective_start_date
485 ,p_effective_end_date => p_effective_end_date
486 ,p_element_entry_id => p_element_entry_id
487 ,p_assignment_id => p_assignment_id_o
488 ,p_element_link_id => p_element_link_id_o
489 ,p_entry_type => p_entry_type_o
490 ,p_date_earned => p_date_earned
491 );
492 hr_utility.trace(' UPD - Recreated process element entries');
493 END IF;
494
495 IF C_info_entry%ISOPEN THEN
496 CLOSE C_info_entry;
497 END IF;
498 hr_utility.trace('Preproc UPD Finished');
499
500 END CPAM_INFO_UPDATE;
501 --
502
503
504 PROCEDURE CPAM_INFO_DELETE(
505 p_element_entry_id IN NUMBER,
506 p_element_link_id_o IN NUMBER,
507 p_effective_start_date_o IN DATE,
508 p_assignment_id_o IN NUMBER ,
509 p_datetrack_mode IN VARCHAR2
510 ) IS
511 rec_info_entry c_info_entry%ROWTYPE;
512 rec_element_link c_element_link%ROWTYPE;
513 l_assignment_id pay_element_entries_f.assignment_id%TYPE;
514
515 CURSOR C_linked_process_entries(p_element_entry_id IN NUMBER, p_assignment_id IN NUMBER) IS
516 SELECT pee.element_entry_id
517 FROM pay_element_entries_f pee
518 WHERE pee.entry_information_category = 'FR_CPAM PROCESS INFORMATION'
519 AND pee.assignment_id = p_assignment_id
520 AND pee.entry_information1 = to_char(p_element_entry_id);
521
522 BEGIN
523 --
524 /* Added for GSI Bug 5472781 */
525 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
526 hr_utility.set_location('Leaving : pay_fr_cpam_preprocessor.cpam_info_delete' , 10);
527 return;
528 END IF;
529 --
530 hr_utility.trace(' Preproc DEL Started');
531
532 -- To determine that the entry being deleted is of type 'FR_SICKNESS_CPAM_INFO'
533 -- the element link id of the Info type will be compared with the element link id of the entry being deleted
534 IF C_iv_ids%ISOPEN THEN
535 CLOSE C_iv_ids;
536 END IF;
537 OPEN C_iv_ids('FR_SICKNESS_CPAM_INFO', p_effective_start_date_o);
538 FETCH C_iv_ids
539 INTO g_info_element_type_id,
540 g_info_pymt_frm_dt_iv_id,
541 g_info_pymt_to_dt_iv_id,
542 g_info_days_iv_id,
543 --g_info_subrogated_iv_id,
544 g_info_gross_amt_iv_id,
545 g_info_net_amt_iv_id,
546 g_info_gross_rt_iv_id,
547 g_info_net_rt_iv_id;
548 CLOSE C_iv_ids;
549
550 OPEN C_element_link(g_info_element_type_id, p_element_link_id_o, p_effective_start_date_o);
551 FETCH C_element_link INTO rec_element_link;
552 CLOSE C_element_link;
553
554 hr_utility.trace(' Preproc :: Info element type ='||g_info_element_type_id||' with link='||rec_element_link.element_link_id||' Parameter link='||p_element_link_id_o);
555 IF p_element_link_id_o = rec_element_link.element_link_id THEN
556 -- The entry being currently deleted is of type 'FR_SICKNESS_CPAM_INFO'
557 -- So delete all Process entries linked to the current entry
558 hr_utility.trace(' Preproc :: DT DELETE MODE ='||p_datetrack_mode||' for INFO entry'||p_element_entry_id||' eff start date='||p_effective_start_date_o);
559
560 FOR rec_linked_process_entries IN C_linked_process_entries(p_element_entry_id, p_assignment_id_o )
561 LOOP
562 hr_utility.trace(' Preproc : DELETING entry='||rec_linked_process_entries.element_entry_id);
563 hr_entry_api.delete_element_entry(
564 p_dt_delete_mode => p_datetrack_mode,
565 p_session_date => p_effective_start_date_o,
566 p_element_entry_id => rec_linked_process_entries.element_entry_id);
567 hr_utility.trace(' Preproc : DELETED entry='||rec_linked_process_entries.element_entry_id);
568 END LOOP;
569 END IF;
570
571 hr_utility.trace('Preproc DEL Finished');
572
573 END CPAM_INFO_DELETE;
574
575 END PAY_FR_CPAM_PREPROCESSOR;
[Home] [Help]