1 PACKAGE BODY pay_gb_eas_scotland_functions AS
2 /* $Header: pygbeasf.pkb 115.3 2003/09/22 09:56:26 rmakhija noship $ */
3
4 g_asg_id NUMBER;
5 g_count_main_eas_entry NUMBER := 0;
6 g_eas_main_iv_id NUMBER;
7 g_eas_ntpp_main_iv_id NUMBER;
8
9 FUNCTION get_current_freq(p_assignment_id IN NUMBER) RETURN NUMBER IS
10 --
11 CURSOR get_freq IS
12 SELECT ptpt.number_per_fiscal_year
13 FROM per_all_assignments_f paaf, pay_all_payrolls_f pap, per_time_period_types ptpt, fnd_sessions fs
14 WHERE fs.session_id = userenv('sessionid')
15 AND paaf.assignment_id = p_assignment_id
16 AND fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_Date
17 AND pap.payroll_id = paaf.payroll_id
18 AND fs.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_Date
19 AND pap.period_type = ptpt.period_type;
20 --
21 l_freq per_time_period_types.number_per_fiscal_year%TYPE;
22 --
23 BEGIN
24 --
25 hr_utility.trace('Entering GET_CURRENT_FREQ, p_assignment_id='||p_assignment_id);
26 --
27 OPEN get_freq;
28 FETCH get_freq INTO l_freq;
29 CLOSE get_freq;
30 --
31 hr_utility.trace('Leaving GET_CURRENT_FREQ, l_freq='||l_freq);
32 RETURN l_freq;
33 END get_current_freq;
34
35 /*
36 FUNCTION get_ni_process_type(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
37
38 CURSOR get_value IS
39 SELECT nvl(min(peev.screen_entry_value), 'NP')
40 FROM fnd_sessions fs,
41 pay_element_types_f pet,
42 pay_input_values_f piv,
43 pay_element_entries_f peef,
44 pay_element_entry_values_f peev
45 WHERE fs.session_id = userenv('sessionid')
46 AND pet.element_name = 'NI'
47 AND pet.business_group_id IS NULL
48 AND pet.legislation_code = 'GB'
49 AND fs.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
50 AND pet.element_type_id = piv.element_type_id
51 AND piv.name = 'Process Type'
52 AND piv.business_group_id IS NULL
53 AND piv.legislation_code = 'GB'
54 AND fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
55 AND peef.assignment_id = p_assignment_id
56 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
57 AND peef.element_entry_id = peev.element_entry_id
58 AND peev.input_value_id = piv.input_value_id
59 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date;
60 --
61 l_value pay_element_entry_values_f.screen_entry_value%TYPE
62 BEGIN
63 hr_utility.trace('Entering GET_NI_PROCESS_TYPE: p_assignment_id='||p_assignment_id);
64 --
65 OPEN get_value;
66 FETCH get_value INTO l_value;
67 CLOSE get_value;
68 --
69 RETURN get_value;
70 END get_ni_process_type;
71 */
72
73 FUNCTION count_main_eas_entry(p_assignment_id IN NUMBER) RETURN NUMBER IS
74
75 CURSOR get_asg_tax_ref IS
76 SELECT scl.segment1
77 FROM hr_soft_coding_keyflex scl,
78 fnd_sessions fs,
79 pay_payrolls_f ppf,
80 per_all_assignments_f paaf
81 WHERE paaf.assignment_id = p_assignment_id
82 AND fs.session_id = userenv('sessionid')
83 AND fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
84 AND ppf.payroll_id = paaf.payroll_id
85 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
86 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
87 --
88 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
89 --
90 CURSOR get_input_value_id(p_ele_name IN VARCHAR2) IS
91 SELECT piv.input_value_id
92 FROM fnd_sessions fs,
93 pay_element_types_f pet,
94 pay_input_values_f piv
95 WHERE fs.session_id = userenv('sessionid')
96 AND pet.element_name = p_ele_name
97 AND pet.business_group_id IS NULL
98 AND pet.legislation_code = 'GB'
99 AND fs.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
100 AND pet.element_type_id = piv.element_type_id
101 AND piv.name = 'Main Entry'
102 AND piv.business_group_id IS NULL
103 AND piv.legislation_code = 'GB'
104 AND fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
105 --
106 CURSOR get_main_count(p_asg_tax_ref IN VARCHAR2) IS
107 SELECT count(*) cnt
108 FROM fnd_sessions fs,
109 per_all_assignments_f paaf1,
110 per_all_assignments_f paaf2,
111 pay_all_payrolls_f ppf,
112 hr_soft_coding_keyflex scl,
113 pay_element_entries_f peef,
114 pay_element_entry_values_f peev
115 WHERE paaf1.assignment_id = p_assignment_id
116 AND fs.session_id = userenv('sessionid')
117 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
118 AND paaf1.person_id = paaf2.person_id
119 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
120 AND paaf2.payroll_id = ppf.payroll_id
121 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
122 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
123 AND scl.segment1 = p_asg_tax_ref
124 AND paaf2.assignment_id = peef.assignment_id
125 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
126 AND peef.element_entry_id = peev.element_entry_id
127 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
128 AND peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
129 AND nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y';
130 --
131 l_count NUMBER := 0;
132 --
133 CURSOR chk_prim_asg(p_asg_tax_ref IN VARCHAR2) IS
134 SELECT 1 cnt
135 FROM fnd_sessions fs,
136 per_all_assignments_f paaf1,
137 per_all_assignments_f paaf2,
138 pay_all_payrolls_f ppf,
139 hr_soft_coding_keyflex scl,
140 pay_element_entries_f peef,
141 pay_element_entry_values_f peev
142 WHERE paaf1.assignment_id = p_assignment_id
143 AND fs.session_id = userenv('sessionid')
144 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
145 AND paaf1.person_id = paaf2.person_id
146 AND nvl(paaf2.primary_flag, 'N') = 'Y'
147 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
148 AND paaf2.payroll_id = ppf.payroll_id
149 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
150 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
151 AND scl.segment1 = p_asg_tax_ref
152 AND paaf2.assignment_id = peef.assignment_id
153 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
154 AND peef.element_entry_id = peev.element_entry_id
155 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
156 AND peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id);
157 --
158 BEGIN
159 hr_utility.trace('Entering COUNT_MAIN_EAS_ENTRY, assignment_id='||p_assignment_id);
160 -- Get tax ref of current asg.
161 OPEN get_asg_tax_ref;
162 FETCH get_asg_tax_ref INTO l_asg_tax_ref;
163 CLOSE get_asg_tax_ref;
164 --
165 hr_utility.trace('COUNT_MAIN_EAS_ENTRY: l_asg_tax_ref='||l_asg_tax_ref);
166 --
167 OPEN get_input_value_id('EAS Scotland');
168 FETCH get_input_value_id INTO g_eas_main_iv_id;
169 CLOSE get_input_value_id;
170 --
171 OPEN get_input_value_id('EAS Scotland NTPP');
172 FETCH get_input_value_id INTO g_eas_ntpp_main_iv_id;
173 CLOSE get_input_value_id;
174 --
175 OPEN get_main_count(l_asg_tax_ref);
176 FETCH get_main_count INTO l_count;
177 CLOSE get_main_count;
178 --
179 hr_utility.trace('COUNT_MAIN_EAS_ENTRY: After main count, l_count='||l_count);
180 IF l_count = 0 THEN
181 OPEN chk_prim_asg(l_asg_tax_ref);
182 FETCH chk_prim_asg INTO l_count;
183 IF chk_prim_asg%NOTFOUND THEN
184 l_count := 0;
185 END IF;
186 CLOSE chk_prim_asg;
187 --
188 hr_utility.trace('COUNT_MAIN_EAS_ENTRY: After check primary asg, l_count='||l_count);
189 --
190 END IF;
191 --
192 g_count_main_eas_entry := l_count;
193 --
194 hr_utility.trace('Leaving COUNT_MAIN_EAS_ENTRY: l_count='||l_count);
195 RETURN l_count;
196 END count_main_eas_entry;
197
198 FUNCTION get_main_eas_pay_date(p_assignment_id IN NUMBER) RETURN DATE IS
199
200 CURSOR get_asg_tax_ref IS
201 SELECT scl.segment1, ppf.payroll_id
202 FROM hr_soft_coding_keyflex scl,
203 fnd_sessions fs,
204 pay_payrolls_f ppf,
205 per_all_assignments_f paaf
206 WHERE paaf.assignment_id = p_assignment_id
207 AND fs.session_id = userenv('sessionid')
208 AND fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
209 AND ppf.payroll_id = paaf.payroll_id
210 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
211 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
212 --
213 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
214 l_asg_payroll_id pay_payrolls_f.payroll_id%TYPE;
215 l_asg_period_start_date per_time_periods.start_date%TYPE;
216 --
217 CURSOR get_asg_period_start_date IS
218 SELECT ptp.start_date
219 FROM per_time_periods ptp, fnd_sessions fs
220 WHERE fs.session_id = userenv('sessionid')
221 AND ptp.payroll_id = l_asg_payroll_id
222 AND fs.effective_date = ptp.regular_payment_date;
223 --
224 CURSOR get_main_payroll_id IS
225 SELECT ppf.payroll_id
226 FROM fnd_sessions fs,
227 per_all_assignments_f paaf1,
228 per_all_assignments_f paaf2,
229 pay_all_payrolls_f ppf,
230 hr_soft_coding_keyflex scl,
231 pay_element_entries_f peef,
232 pay_element_entry_values_f peev
233 WHERE paaf1.assignment_id = p_assignment_id
234 AND fs.session_id = userenv('sessionid')
235 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
236 AND paaf1.person_id = paaf2.person_id
237 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
238 AND paaf2.payroll_id = ppf.payroll_id
239 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
240 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
241 AND scl.segment1 = l_asg_tax_ref
242 AND paaf2.assignment_id = peef.assignment_id
243 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
244 AND peef.element_entry_id = peev.element_entry_id
245 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
246 AND peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
247 AND nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y';
248 --
249 CURSOR get_prim_payroll_id IS
250 SELECT ppf.payroll_id
251 FROM fnd_sessions fs,
252 per_all_assignments_f paaf1,
253 per_all_assignments_f paaf2,
254 pay_all_payrolls_f ppf,
255 hr_soft_coding_keyflex scl,
256 pay_element_entries_f peef,
257 pay_element_entry_values_f peev
258 WHERE paaf1.assignment_id = p_assignment_id
259 AND fs.session_id = userenv('sessionid')
260 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
261 AND paaf1.person_id = paaf2.person_id
262 AND nvl(paaf2.primary_flag, 'N') = 'Y'
263 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
264 AND paaf2.payroll_id = ppf.payroll_id
265 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
266 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
267 AND scl.segment1 = l_asg_tax_ref
268 AND paaf2.assignment_id = peef.assignment_id
269 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
270 AND peef.element_entry_id = peev.element_entry_id
271 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
272 AND peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id);
273 --
274 l_payroll_id NUMBER;
275 l_pay_date DATE;
276 l_count NUMBER := 0;
277 --
278 CURSOR get_pay_date IS
279 SELECT nvl(regular_payment_date, to_date('01-01-0001', 'DD-MM-YYYY'))
280 FROM per_time_periods ptp
281 WHERE l_asg_period_start_date BETWEEN ptp.start_date AND ptp.end_Date
282 AND ptp.payroll_id = l_payroll_id;
283 --
284 BEGIN
285 --
286 hr_utility.trace('Entering GET_MAIN_EAS_PAY_DATE, p_assignment_id='||p_assignment_id||', g_asg_id='||g_asg_id);
287 --
288 -- Get tax ref of current asg.
289 OPEN get_asg_tax_ref;
290 FETCH get_asg_tax_ref INTO l_asg_tax_ref, l_asg_payroll_id;
291 CLOSE get_asg_tax_ref;
292 --
293 OPEN get_asg_period_start_date;
294 FETCH get_asg_period_start_date INTO l_asg_period_start_date;
295 CLOSE get_asg_period_start_date;
296 --
297 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: l_asg_tax_ref='||l_asg_tax_ref);
298 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: l_asg_period_start_date='||fnd_date.date_to_displaydate(l_asg_period_start_date));
299 --
300 IF nvl(p_assignment_id, -1) <> nvl(g_asg_id, -999) THEN
301 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: Get count again.');
302 l_count := count_main_eas_entry(p_assignment_id);
303 ELSE
304 l_count := g_count_main_eas_entry;
305 END IF;
306 --
307 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: l_count='||l_count);
308 --
309 IF nvl(l_count, 0) = 1 THEN
310 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: Finding pay date on main entry.');
311 --
312 OPEN get_main_payroll_id;
313 FETCH get_main_payroll_id INTO l_payroll_id;
314 IF get_main_payroll_id%NOTFOUND THEN
315 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: Main entry not found.');
316 l_payroll_id := NULL;
317 END IF;
318 CLOSE get_main_payroll_id;
319 --
320 IF l_payroll_id IS NULL THEN
321 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: Checking primary assignment for payroll id.');
322 OPEN get_prim_payroll_id;
323 FETCH get_prim_payroll_id INTO l_payroll_id;
324 IF get_prim_payroll_id%NOTFOUND THEN
325 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: Payroll Id not found.');
326 l_payroll_id := NULL;
327 END IF;
328 CLOSE get_prim_payroll_id;
329 END IF;
330 --
331 IF l_payroll_id IS NULL THEN
332 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: No Payroll found, Return default date.');
333 RETURN to_date('01-01-0001', 'DD-MM-YYYY');
334 ELSE
335 OPEN get_pay_date;
336 FETCH get_pay_date INTO l_pay_date;
337 IF get_pay_date%NOTFOUND THEN
338 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: Pay date not found, default date.');
339 l_pay_date := to_date('01-01-0001', 'DD-MM-YYYY');
340 END IF;
341 CLOSE get_pay_date;
342 END IF;
343 ELSE
344 hr_utility.trace('GET_MAIN_EAS_PAY_DATE: Main entry not found, Return default date.');
345 l_pay_date := to_date('01-01-0001', 'DD-MM-YYYY');
346 END IF;
347 --
348 hr_utility.trace('Leaving GET_MAIN_EAS_PAY_DATE: l_pay_date='||fnd_date.date_to_displaydate(l_pay_date));
349 RETURN l_pay_date;
350 END get_main_eas_pay_date;
351
352 FUNCTION get_main_eas_freq(p_assignment_id IN NUMBER) RETURN NUMBER IS
353
354 CURSOR get_asg_tax_ref IS
355 SELECT scl.segment1
356 FROM hr_soft_coding_keyflex scl,
357 fnd_sessions fs,
358 pay_payrolls_f ppf,
359 per_all_assignments_f paaf
360 WHERE paaf.assignment_id = p_assignment_id
361 AND fs.session_id = userenv('sessionid')
362 AND fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
363 AND ppf.payroll_id = paaf.payroll_id
364 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
365 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
366 --
367 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
368 --
369 CURSOR get_main_payroll_id IS
370 SELECT ppf.payroll_id
371 FROM fnd_sessions fs,
372 per_all_assignments_f paaf1,
373 per_all_assignments_f paaf2,
374 pay_all_payrolls_f ppf,
375 hr_soft_coding_keyflex scl,
376 pay_element_entries_f peef,
377 pay_element_entry_values_f peev
378 WHERE paaf1.assignment_id = p_assignment_id
379 AND fs.session_id = userenv('sessionid')
380 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
381 AND paaf1.person_id = paaf2.person_id
382 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
383 AND paaf2.payroll_id = ppf.payroll_id
387 AND paaf2.assignment_id = peef.assignment_id
384 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
385 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
386 AND scl.segment1 = l_asg_tax_ref
388 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
389 AND peef.element_entry_id = peev.element_entry_id
390 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
391 AND peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
392 AND nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y';
393 --
394 CURSOR get_prim_payroll_id IS
395 SELECT ppf.payroll_id
396 FROM fnd_sessions fs,
397 per_all_assignments_f paaf1,
398 per_all_assignments_f paaf2,
399 pay_all_payrolls_f ppf,
400 hr_soft_coding_keyflex scl,
401 pay_element_entries_f peef,
402 pay_element_entry_values_f peev
403 WHERE paaf1.assignment_id = p_assignment_id
404 AND fs.session_id = userenv('sessionid')
405 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
406 AND paaf1.person_id = paaf2.person_id
407 AND nvl(paaf2.primary_flag, 'N') = 'Y'
408 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
409 AND paaf2.payroll_id = ppf.payroll_id
410 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
411 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
412 AND scl.segment1 = l_asg_tax_ref
413 AND paaf2.assignment_id = peef.assignment_id
414 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
415 AND peef.element_entry_id = peev.element_entry_id
416 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
417 AND peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id);
418 --
419 l_payroll_id NUMBER;
420 l_freq NUMBER;
421 l_count NUMBER := 0;
422 --
423 CURSOR get_freq IS
424 SELECT number_per_fiscal_year
425 FROM per_time_periods ptp, per_time_period_types ptpt, fnd_sessions fs
426 WHERE fs.session_id = userenv('sessionid')
427 AND fs.effective_date BETWEEN ptp.start_date AND ptp.end_Date
428 AND ptp.payroll_id = l_payroll_id
429 AND ptp.period_type = ptpt.period_type;
430 --
431 BEGIN
432 --
433 hr_utility.trace('Entering GET_MAIN_EAS_FREQ, p_assignment_id='||p_assignment_id||', g_asg_id='||g_asg_id);
434 --
435 -- Get tax ref of current asg.
436 OPEN get_asg_tax_ref;
437 FETCH get_asg_tax_ref INTO l_asg_tax_ref;
438 CLOSE get_asg_tax_ref;
439 --
440 hr_utility.trace('GET_MAIN_EAS_FREQ: l_asg_tax_ref='||l_asg_tax_ref);
441 --
442 IF nvl(p_assignment_id, -1) <> nvl(g_asg_id, -999) THEN
443 hr_utility.trace('GET_MAIN_EAS_FREQ: Get count again.');
444 l_count := count_main_eas_entry(p_assignment_id);
445 ELSE
446 l_count := g_count_main_eas_entry;
447 END IF;
448 --
449 hr_utility.trace('GET_MAIN_EAS_FREQ: l_count='||l_count);
450 --
451 IF nvl(l_count, 0) = 1 THEN
452 hr_utility.trace('GET_MAIN_EAS_FREQ: Finding frequency on main entry.');
453 --
454 OPEN get_main_payroll_id;
455 FETCH get_main_payroll_id INTO l_payroll_id;
456 IF get_main_payroll_id%NOTFOUND THEN
457 hr_utility.trace('GET_MAIN_EAS_FREQ: Main entry not found.');
458 l_payroll_id := NULL;
459 END IF;
460 CLOSE get_main_payroll_id;
461 --
462 IF l_payroll_id IS NULL THEN
463 hr_utility.trace('GET_MAIN_EAS_FREQ: Checking primary assignment for payroll id.');
464 OPEN get_prim_payroll_id;
465 FETCH get_prim_payroll_id INTO l_payroll_id;
466 IF get_prim_payroll_id%NOTFOUND THEN
467 hr_utility.trace('GET_MAIN_EAS_FREQ: Payroll Id not found.');
468 l_payroll_id := NULL;
469 END IF;
470 CLOSE get_prim_payroll_id;
471 END IF;
472 --
473 IF l_payroll_id IS NULL THEN
474 hr_utility.trace('GET_MAIN_EAS_FREQ: No Payroll found, Return 0.');
475 RETURN 0;
476 ELSE
477 OPEN get_freq;
478 FETCH get_freq INTO l_freq;
479 IF get_freq%NOTFOUND THEN
480 hr_utility.trace('GET_MAIN_EAS_FREQ: Frequency not found, default to 0.');
481 l_freq := 0;
482 END IF;
483 CLOSE get_freq;
484 END IF;
485 --
486 ELSE
487 hr_utility.trace('GET_MAIN_EAS_FREQ: Main entry not found, Return 0.');
488 l_freq := 0;
489 END IF;
490 --
491 hr_utility.trace('Leaving GET_MAIN_EAS_FREQ: l_freq='||l_freq);
492 RETURN l_freq;
493 END get_main_eas_freq;
494
495 FUNCTION get_main_entry_value(p_assignment_id IN NUMBER,
496 p_input_value_name IN VARCHAR2,
497 p_count OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
498
499 CURSOR get_asg_tax_ref IS
500 SELECT scl.segment1
501 FROM hr_soft_coding_keyflex scl,
502 fnd_sessions fs,
503 pay_payrolls_f ppf,
504 per_all_assignments_f paaf
505 WHERE paaf.assignment_id = p_assignment_id
506 AND fs.session_id = userenv('sessionid')
507 AND fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
508 AND ppf.payroll_id = paaf.payroll_id
509 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
510 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
511 --
512 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
513 --
517 pay_element_types_f pet,
514 CURSOR get_input_value_id(p_ele_name IN VARCHAR2, p_iv_name IN VARCHAR2) IS
515 SELECT piv.input_value_id
516 FROM fnd_sessions fs,
518 pay_input_values_f piv
519 WHERE fs.session_id = userenv('sessionid')
520 AND pet.element_name = p_ele_name
521 AND pet.business_group_id IS NULL
522 AND pet.legislation_code = 'GB'
523 AND fs.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
524 AND pet.element_type_id = piv.element_type_id
525 AND piv.name = p_iv_name
526 AND piv.business_group_id IS NULL
527 AND piv.legislation_code = 'GB'
528 AND fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
529 --
530 l_eas_iv_id NUMBER;
531 l_eas_ntpp_iv_id NUMBER;
532 --
533 CURSOR get_main_entry_id IS
534 SELECT peef.element_entry_id
535 FROM fnd_sessions fs,
536 per_all_assignments_f paaf1,
537 per_all_assignments_f paaf2,
538 pay_all_payrolls_f ppf,
539 hr_soft_coding_keyflex scl,
540 pay_element_entries_f peef,
541 pay_element_entry_values_f peev
542 WHERE paaf1.assignment_id = p_assignment_id
543 AND fs.session_id = userenv('sessionid')
544 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
545 AND paaf1.person_id = paaf2.person_id
546 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
547 AND paaf2.payroll_id = ppf.payroll_id
548 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
549 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
550 AND scl.segment1 = l_asg_tax_ref
551 AND paaf2.assignment_id = peef.assignment_id
552 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
553 AND peef.element_entry_id = peev.element_entry_id
554 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
555 AND peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
556 AND nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y'
557 AND peef.target_entry_id IS NULL;
558 --
559 CURSOR chk_prim_entry_id IS
560 SELECT peef.element_entry_id
561 FROM fnd_sessions fs,
562 per_all_assignments_f paaf1,
563 per_all_assignments_f paaf2,
564 pay_all_payrolls_f ppf,
565 hr_soft_coding_keyflex scl,
566 pay_element_entries_f peef,
567 pay_element_entry_values_f peev
568 WHERE paaf1.assignment_id = p_assignment_id
569 AND fs.session_id = userenv('sessionid')
570 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
571 AND paaf1.person_id = paaf2.person_id
572 AND nvl(paaf2.primary_flag, 'N') = 'Y'
573 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
574 AND paaf2.payroll_id = ppf.payroll_id
575 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
576 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
577 AND scl.segment1 = l_asg_tax_ref
578 AND paaf2.assignment_id = peef.assignment_id
579 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
580 AND peef.element_entry_id = peev.element_entry_id
581 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
582 AND peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
583 AND peef.target_entry_id IS NULL;
584 --
585 l_entry_id NUMBER;
586 --
587 CURSOR get_value IS
588 SELECT peev.screen_entry_value
589 FROM fnd_sessions fs, pay_element_entry_values_f peev
590 WHERE fs.session_id = userenv('sessionid')
591 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
592 AND peev.element_entry_id = l_entry_id
593 AND peev.input_value_id IN (l_eas_iv_id, l_eas_ntpp_iv_id);
594 --
595 l_value pay_element_entry_values_f.screen_entry_value%TYPE;
596 l_count NUMBER;
597 --
598 BEGIN
599 hr_utility.trace('Entering GET_MAIN_ENTRY_VALUE, p_assignment_id='||p_assignment_id||', p_input_value_name='||p_input_value_name);
600 --
601 -- Get tax ref of current asg.
602 OPEN get_asg_tax_ref;
603 FETCH get_asg_tax_ref INTO l_asg_tax_ref;
604 CLOSE get_asg_tax_ref;
605 --
606 hr_utility.trace('GET_MAIN_EAS_ENTRY_VALUE: l_asg_tax_ref='||l_asg_tax_ref);
607 --
608 IF nvl(p_assignment_id, -1) <> nvl(g_asg_id, -999) THEN
609 hr_utility.trace('GET_MAIN_ENTRY_VALUE: Get count again.');
610 l_count := count_main_eas_entry(p_assignment_id);
611 ELSE
612 l_count := g_count_main_eas_entry;
613 END IF;
614 --
615 hr_utility.trace('GET_MAIN_ENTRY_VALUE: l_count='||l_count||
616 ', g_asg_id='||g_asg_id||
617 ', g_eas_main_iv_id='||g_eas_main_iv_id||
618 ', g_eas_ntpp_main_iv_id='||g_eas_ntpp_main_iv_id);
619 p_count := l_count;
620 --
621 IF nvl(l_count, 0) = 1 THEN
622 hr_utility.trace('GET_MAIN_ENTRY_VALUE: Finding input value on main entry.');
623 --
624 OPEN get_input_value_id('EAS Scotland', p_input_value_name);
625 FETCH get_input_value_id INTO l_eas_iv_id;
626 CLOSE get_input_value_id;
627 --
628 OPEN get_input_value_id('EAS Scotland NTPP', p_input_value_name);
629 FETCH get_input_value_id INTO l_eas_ntpp_iv_id;
630 CLOSE get_input_value_id;
631 --
632 hr_utility.trace('GET_MAIN_ENTRY_VALUE: l_eas_iv_id='||l_eas_iv_id||', l_eas_ntpp_iv_id='||l_eas_ntpp_iv_id);
633 --
634 OPEN get_main_entry_id;
638 hr_utility.trace('GET_MAIN_ENTRY_VALUE: Input value not found on main entry, checking primary assignment.');
635 FETCH get_main_entry_id INTO l_entry_id;
636 hr_utility.trace('GET_MAIN_ENTRY_VALUE: After get_main_entry_id, l_entry_id='||l_entry_id);
637 IF get_main_entry_id%NOTFOUND THEN
639 OPEN chk_prim_entry_id;
640 FETCH chk_prim_entry_id INTO l_entry_id;
641 CLOSE chk_prim_entry_id;
642 hr_utility.trace('GET_MAIN_ENTRY_VALUE: After chk_prim_entry_id, l_entry_id='||l_entry_id);
643 END IF;
644 CLOSE get_main_entry_id;
645 --
646 hr_utility.trace('GET_MAIN_ENTRY_VALUE: l_elntry_id='||l_entry_id);
647 --
648 OPEN get_value;
649 FETCH get_value INTO l_value;
650 CLOSE get_value;
651 --
652 hr_utility.trace('GET_MAIN_ENTRY_VALUE: l_value='||l_value);
653 ELSE
654 l_value := NULL;
655 END IF;
656 --
657 hr_utility.trace('GET_MAIN_ENTRY_VALUE: Returning l_value='||l_value);
658 RETURN l_value;
659 END get_main_entry_value;
660
661 FUNCTION get_main_initial_debt(p_assignment_id IN NUMBER) RETURN NUMBER IS
662 l_value NUMBER;
663 l_count NUMBER;
664 BEGIN
665 hr_utility.trace('Entering GET_MAIN_INITIAL_DEBT: p_assignment_id='||p_assignment_id);
666 --
667 l_value := nvl(to_number(get_main_entry_value(p_assignment_id, 'Initial Debt', l_count)), 0);
668 --
669 hr_utility.trace('Leaving GET_MAIN_INITIAL_DEBT: l_value='||l_value);
670 RETURN l_value;
671 END get_main_initial_debt;
672
673 FUNCTION get_main_fee(p_assignment_id IN NUMBER) RETURN NUMBER IS
674 l_value NUMBER;
675 l_count NUMBER;
676 BEGIN
677 hr_utility.trace('Entering GET_MAIN_FEE, p_assignment_id='||p_assignment_id);
678 --
679 l_value := nvl(to_number(get_main_entry_value(p_assignment_id, 'Fee', l_count)), 0);
680 --
681 hr_utility.trace('Leaving GET_MAIN_FEE: l_value='||l_value);
682 RETURN l_value;
683 END get_main_fee;
684
685 FUNCTION check_ref(p_assignment_id IN NUMBER, p_reference IN VARCHAR2) RETURN VARCHAR2 IS
686 l_main_ref pay_element_entry_values_f.screen_entry_value%TYPE;
687 l_count NUMBER;
688 BEGIN
689 hr_utility.trace('Entering CHECK_REF, p_assignment_id='||p_assignment_id||
690 ', p_reference='||p_reference);
691 --
692 l_main_ref := nvl(get_main_entry_value(p_assignment_id, 'Reference', l_count), 'Unknown');
693 hr_utility.trace('CHECK_REF: Main ref='||l_main_ref||', l_count='||l_count);
694 --
695 IF nvl(l_count, 0) = 1 AND l_main_ref = p_reference THEN
696 -- Valid reference
697 RETURN 'Y';
698 ELSE
699 -- Invalid Reference
700 RETURN 'N';
701 END IF;
702 END check_ref;
703
704 END pay_gb_eas_scotland_functions;
[Home] [Help]