[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_STUDENT_LOANS_PKG
Source
1 PACKAGE BODY PAY_GB_STUDENT_LOANS_PKG AS
2 /* $Header: pygbslco.pkb 120.7.12020000.4 2013/01/25 13:58:53 rsadhana ship $ */
3
4 g_package_functions varchar2(50) := 'pay_gb_student_loans_pkg';
5 g_asg_id NUMBER;
6 g_count_main_cto_entry NUMBER := 0;
7
8 --
9 -- Private declarations
10 --
11
12 g_package VARCHAR2(31) := 'PAY_GB_TAX_CREDIT_PKG';
13
14 FUNCTION Get_Input_Value_Id(
15 p_name in VARCHAR2,
16 p_effective_date in DATE
17 ) RETURN NUMBER is
18 l_input_value_id PAY_INPUT_VALUES_F.input_value_id%TYPE;
19
20 BEGIN
21
22 SELECT ipv.input_value_id INTO l_input_value_id
23 FROM PAY_INPUT_VALUES_F ipv,
24 PAY_ELEMENT_TYPES_F ele
25 WHERE ele.element_name = 'Student Loan'
26 and ele.legislation_code = 'GB'
27 AND ipv.name = p_name
28 and ipv.legislation_code = 'GB'
29 AND ele.element_type_id = ipv.element_type_id
30 AND p_effective_date between ele.effective_start_date
31 and ele.effective_end_date
32 AND p_effective_date between ipv.effective_start_date
33 and ipv.effective_end_date;
34
35 RETURN l_input_value_id;
36
37 END Get_Input_Value_Id;
38
39 --
40 -- Public Declarations
41 --
42
43 PROCEDURE Fetch_Balances(
44 p_assignment_id in PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID%TYPE,
45 p_element_type_id in PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_Id%TYPE,
46 p_element_name in PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
47 p_element_entry_id in PAY_RUN_RESULTS.SOURCE_ID%TYPE,
48 p_itd_balance OUT NOCOPY NUMBER,
49 p_ptd_balance OUT NOCOPY NUMBER
50 ) is
51
52 cursor c_balance_id(p_name in VARCHAR2) is
53 select balance_type_id
54 from pay_balance_types
55 where balance_name = p_name
56 and legislation_code = 'GB';
57
58 cursor c_itd_asgact(p_asg in NUMBER,
59 p_ent_id in NUMBER,
60 p_element_type_id in NUMBER) is
61 select prr.assignment_action_id,
62 prr.source_id
63 from pay_run_results prr,
64 pay_element_types_f ele
65 where prr.assignment_action_id in (
66 SELECT /*+ use_nl(paa,ppa,ptp,ses) */
67 to_number(substr(max(lpad(paa.action_sequence,15,'0')||
68 paa.assignment_action_id),16))
69 FROM pay_assignment_actions paa,
70 pay_payroll_actions ppa,
71 per_time_periods ptp,
72 fnd_sessions ses
73 WHERE paa.assignment_id = p_asg
74 --AND paa.action_status = 'C'
75 AND paa.action_status in ('C','S') --Modified for the bug 10066755
76 AND ses.session_id = userenv('sessionid')
77 AND ppa.payroll_action_id = paa.payroll_action_id
78 AND ses.effective_date between ptp.start_date and ptp.end_date
79 AND ppa.time_period_id = ptp.time_period_id
80 AND (paa.source_action_id is not null
81 or ppa.action_type in ('I','V','B'))
82 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B'))
83 AND prr.element_type_id = ele.element_type_id
84 AND prr.source_id = p_ent_id;
85
86 cursor c_ptd_asgact(p_asg in NUMBER,
87 p_ent in NUMBER,
88 p_element_type_id in NUMBER) is
89 select prr.assignment_action_id,
90 prr.source_id
91 from pay_run_results prr,
92 pay_element_types_f ele
93 where prr.assignment_action_id in (
94 SELECT /*+ use_nl(paa,ppa,ptp,ses) */
95 to_number(substr(max(lpad(paa.action_sequence,15,'0')||
96 paa.assignment_action_id),16))
97 FROM pay_assignment_actions paa,
98 pay_payroll_actions ppa,
99 fnd_sessions ses,
100 per_time_periods ptp
101 WHERE paa.assignment_id = p_asg
102 --AND paa.action_status = 'C'
103 AND paa.action_status in ('C','S') --Modified for the bug 10066755
104 AND ses.session_id = userenv('sessionid')
105 AND ptp.payroll_id = ppa.payroll_id
106 AND ses.effective_date between ptp.start_date and ptp.end_date
107 AND ppa.effective_date between ptp.start_date and ptp.end_date
108 AND ppa.payroll_action_id = paa.payroll_action_id
109 AND (paa.source_action_id is not null
110 or ppa.action_type in ('I','V','B'))
111 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B'))
112 AND prr.element_type_id = ele.element_type_id
113 and ele.element_type_id = p_element_type_id
114 and prr.source_id = p_ent;
115
116 l_proc VARCHAR(72) := g_package||'.FETCH_BALANCES';
117 l_itd_action_id PAY_ASSIGNMENT_ACTIONS.assignment_action_id%TYPE;
118 l_itd_source_id PAY_RUN_RESULTS.source_id%TYPE;
119 l_ptd_action_id PAY_ASSIGNMENT_ACTIONS.assignment_action_id%TYPE;
120 l_ptd_source_id PAY_RUN_RESULTS.source_id%TYPE;
121 l_balance_type_id PAY_BALANCE_TYPES.balance_type_id%TYPE;
122 l_balance_type_id2 PAY_BALANCE_TYPES.balance_type_id%TYPE;
123 l_effective_date DATE;
124 l_name PAY_BALANCE_TYPES.BALANCE_NAME%TYPE;
125 l_name2 PAY_BALANCE_TYPES.BALANCE_NAME%TYPE;
126
127 BEGIN
128
129 hr_utility.set_location('Entering..'||l_proc,10);
130
131 If p_element_name='Student Loan' then
132 l_name := 'Student Loan';
133 elsif instr(upper(p_element_name),'COURT') >0 then
134 l_name := 'Court Order';
135 elsif instr(upper(p_element_name),'CAO SCOTLAND') >0 then
136 l_name := 'CAO Scotland Payments CMA';
137 l_name2 := 'CAO Scotland Payments EAS';
138 elsif instr(upper(p_element_name),'CMA SCOTLAND') >0 then
139 l_name := 'CMA Scotland';
140 elsif instr(upper(p_element_name),'EAS SCOTLAND') >0 then
141 l_name := 'EAS Scotland';
142 end if;
143
144 open c_balance_id(l_name);
145 fetch c_balance_id into l_balance_type_id;
146 close c_balance_id;
147
148 if l_name2 is not null then
149 open c_balance_id(l_name2);
150 fetch c_balance_id into l_balance_type_id2;
151 close c_balance_id;
152 end if;
153
154 open c_ptd_asgact(p_assignment_id,p_element_entry_id,p_element_type_id);
155 fetch c_ptd_asgact into l_ptd_action_id,l_ptd_source_id;
156
157 if c_ptd_asgact%NOTFOUND then
158
159 p_ptd_balance := NULL;
160 close c_ptd_asgact;
161 else
162 if l_name = 'Court Order' then
163 p_ptd_balance := NVL(hr_gbbal.calc_element_ptd_bal(
164 l_ptd_action_id,
165 l_balance_type_id,
166 l_ptd_source_id),0.00);
167 elsif l_name = 'Student Loan' then
168 p_ptd_balance := NVL(hr_gbbal.calc_asg_tfr_ptd_action(
169 l_ptd_action_id,
170 l_balance_type_id,
171 NULL),0.00);
172 elsif l_name = 'CAO Scotland Payments CMA' then
173 p_ptd_balance := NVL(hr_gbbal.calc_asg_proc_ptd_action(
174 l_ptd_action_id,
175 l_balance_type_id,
176 NULL),0.00) +
177 NVL(hr_gbbal.calc_asg_proc_ptd_action(
178 l_ptd_action_id,
179 l_balance_type_id2,
180 NULL),0.00);
181 else
182 p_ptd_balance := NVL(hr_gbbal.calc_asg_proc_ptd_action(
183 l_ptd_action_id,
184 l_balance_type_id,
185 NULL),0.00);
186 end if;
187 close c_ptd_asgact;
188
189 end if;
190
191 open c_itd_asgact(p_assignment_id,p_element_entry_id,p_element_type_id);
192 fetch c_itd_asgact into l_itd_action_id,l_itd_source_id;
193
194 if c_itd_asgact%NOTFOUND then
195 p_itd_balance := NULL;
196 close c_itd_asgact;
197 else
198 if l_name = 'Court Order' then
199 p_itd_balance := NVL(hr_gbbal.calc_element_itd_bal(
200 l_itd_action_id,
201 l_balance_type_id,
202 l_itd_source_id),0.00);
203 elsif l_name = 'Student Loan' then
204
205 /* Use the same variables, even though they are really for itd,
206 and let the balance retrieval code handle the expiration */
207
208 p_itd_balance := NVL(hr_gbbal.calc_asg_td_ytd_action(
209 l_itd_action_id,
210 l_balance_type_id,
211 NULL),0.00);
212 else
213
214 /* There are no ITD or YTD for Scottish Court Orders so set the
215 itd balance to null */
216
217 p_itd_balance := null;
218 end if;
219 close c_itd_asgact;
220 end if;
221
222 hr_utility.set_location('leaving..'||l_proc,20);
223
224 END Fetch_Balances;
225
226 PROCEDURE Update_Court_Order(
227 p_datetrack_update_mode in varchar2
228 ,p_effective_date in date
229 ,p_business_group_id in number
230 ,p_element_entry_id in number
231 ,p_object_version_number in out nocopy number
232 ,p_subpriority in number
233 ,p_effective_start_date out nocopy date
234 ,p_effective_end_date out nocopy date) is
235
236 l_update_warning BOOLEAN;
237
238 BEGIN
239
240 py_element_entry_api.update_element_entry(
241 p_validate => FALSE,
242 p_datetrack_update_mode => p_datetrack_update_mode,
243 p_effective_date => p_effective_date,
244 p_business_group_id => p_business_group_id,
245 p_element_entry_id => p_element_entry_id,
246 p_object_version_number => p_object_version_number,
247 p_subpriority => p_subpriority,
248 P_EFFECTIVE_START_DATE =>p_effective_start_date,
249 P_EFFECTIVE_END_DATE =>p_effective_end_date,
250 P_UPDATE_WARNING =>l_update_warning);
251 --
252 -- Done the update
253 --
254
255 END Update_Court_Order;
256
257 PROCEDURE Create_Student_Loan(
258 P_EFFECTIVE_DATE in Date,
259 P_BUSINESS_GROUP_ID in Number,
260 P_ASSIGNMENT_ID in Number,
261 P_START_DATE in Varchar2,
262 P_END_DATE in Varchar2,
263 P_SUBPRIORITY in Number,
264 P_EFFECTIVE_START_DATE out nocopy Date,
265 P_EFFECTIVE_END_DATE out nocopy Date,
266 P_ELEMENT_ENTRY_ID out nocopy Number,
267 P_OBJECT_VERSION_NUMBER out nocopy Number) is
268
269 cursor c_effective_date is
270 select effective_date
271 from fnd_sessions
272 where session_id = userenv('sessionid');
273
274 cursor c_element_type is
275 select element_type_id
276 from pay_element_types_f
277 where element_name = 'Student Loan'
278 and legislation_code = 'GB';
279
280 l_element_link_id PAY_ELEMENT_LINKS_F.ELEMENT_LINK_ID%TYPE;
281 l_element_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
282 l_create_warning BOOLEAN;
283 l_effective_date DATE;
284
285 BEGIN
286
287 --
288 -- Obtain the element link using the element type and
289 -- the assignment (payroll)
290 --
291 /* this is not needed, instead use the effective date passed
292 in the parameter
293 open c_effective_date;
294 fetch c_effective_date into l_effective_date;
295 close c_effective_date;
296 */
297 l_effective_date := P_EFFECTIVE_DATE;
298 open c_element_type;
299 fetch c_element_type into l_element_id;
300 close c_element_type;
301
302 l_element_link_id := hr_entry_api.get_link(
303 p_assignment_id,
304 l_element_id,
305 l_effective_date);
306 --
307 -- Create the entry
308 --
309 py_element_entry_api.create_element_entry(
310 P_VALIDATE =>FALSE,
311 P_EFFECTIVE_DATE =>p_effective_date,
312 P_BUSINESS_GROUP_ID =>p_business_group_id,
313 P_ORIGINAL_ENTRY_ID =>NULL,
314 P_ASSIGNMENT_ID =>p_assignment_id,
315 P_ELEMENT_LINK_ID =>l_element_link_id,
316 P_ENTRY_TYPE =>'E',
317 P_COST_ALLOCATION_KEYFLEX_ID=>NULL,
318 P_UPDATING_ACTION_ID =>NULL,
319 P_COMMENT_ID =>NULL,
320 P_REASON =>NULL,
321 P_TARGET_ENTRY_ID =>NULL,
322 P_SUBPRIORITY =>P_SUBPRIORITY,
323 P_DATE_EARNED =>NULL,
324 P_PERSONAL_PAYMENT_METHOD_ID=>NULL,
325 P_ATTRIBUTE_CATEGORY =>NULL,
326 P_ATTRIBUTE1 =>NULL,
327 P_ATTRIBUTE2 =>NULL,
328 P_ATTRIBUTE3 =>NULL,
329 P_ATTRIBUTE4 =>NULL,
330 P_ATTRIBUTE5 =>NULL,
331 P_ATTRIBUTE6 =>NULL,
332 P_ATTRIBUTE7 =>NULL,
333 P_ATTRIBUTE8 =>NULL,
334 P_ATTRIBUTE9 =>NULL,
335 P_ATTRIBUTE10 =>NULL,
336 P_ATTRIBUTE11 =>NULL,
337 P_ATTRIBUTE12 =>NULL,
338 P_ATTRIBUTE13 =>NULL,
339 P_ATTRIBUTE14 =>NULL,
340 P_ATTRIBUTE15 =>NULL,
341 P_ATTRIBUTE16 =>NULL,
342 P_ATTRIBUTE17 =>NULL,
343 P_ATTRIBUTE18 =>NULL,
344 P_ATTRIBUTE19 =>NULL,
345 P_ATTRIBUTE20 =>NULL,
346 P_INPUT_VALUE_ID1 =>Get_Input_Value_Id('Start Date',
347 p_effective_date),
348 P_INPUT_VALUE_ID2 =>Get_Input_Value_Id('End Date',
349 p_effective_date),
350 P_INPUT_VALUE_ID3 =>NULL,
351 P_INPUT_VALUE_ID4 =>NULL,
352 P_INPUT_VALUE_ID5 =>NULL,
353 P_INPUT_VALUE_ID6 =>NULL,
354 P_INPUT_VALUE_ID7 =>NULL,
355 P_INPUT_VALUE_ID8 =>NULL,
356 P_INPUT_VALUE_ID9 =>NULL,
357 P_INPUT_VALUE_ID10 =>NULL,
358 P_INPUT_VALUE_ID11 =>NULL,
359 P_INPUT_VALUE_ID12 =>NULL,
360 P_INPUT_VALUE_ID13 =>NULL,
361 P_INPUT_VALUE_ID14 =>NULL,
362 P_INPUT_VALUE_ID15 =>NULL,
363 P_ENTRY_VALUE1 =>p_start_date,
364 P_ENTRY_VALUE2 =>p_end_date,
365 P_ENTRY_VALUE3 =>NULL,
366 P_ENTRY_VALUE4 =>NULL,
367 P_ENTRY_VALUE5 =>NULL,
368 P_ENTRY_VALUE6 =>NULL,
369 P_ENTRY_VALUE7 =>NULL,
370 P_ENTRY_VALUE8 =>NULL,
371 P_ENTRY_VALUE9 =>NULL,
372 P_ENTRY_VALUE10 =>NULL,
373 P_ENTRY_VALUE11 =>NULL,
374 P_ENTRY_VALUE12 =>NULL,
375 P_ENTRY_VALUE13 =>NULL,
376 P_ENTRY_VALUE14 =>NULL,
377 P_ENTRY_VALUE15 =>NULL,
378 P_EFFECTIVE_START_DATE =>p_effective_start_date,
379 P_EFFECTIVE_END_DATE =>p_effective_end_date,
380 P_ELEMENT_ENTRY_ID =>p_element_entry_id,
381 P_OBJECT_VERSION_NUMBER =>p_object_version_number,
382 P_CREATE_WARNING =>l_create_warning);
383
384 END Create_Student_Loan;
385
386 --
387 -- -------------------- Delete Process ----------------------
388 --
389
390 PROCEDURE Delete_Student_Loan(
391 p_datetrack_mode in VARCHAR2
392 ,p_element_entry_id in NUMBER
393 ,p_effective_date in DATE
394 ,p_object_version_number in NUMBER) IS
395
396 l_object_version_number NUMBER;
397 l_effective_start_date DATE;
398 l_effective_end_date DATE;
399 l_delete_warning BOOLEAN;
400
401 BEGIN
402
403 l_object_version_number := p_object_version_number;
404
405 py_element_entry_api.delete_element_entry(
406 p_validate => FALSE,
407 p_datetrack_delete_mode => p_datetrack_mode,
408 p_effective_date => p_effective_date,
409 p_element_entry_id => p_element_entry_id,
410 p_object_version_number => l_object_version_number,
411 p_effective_start_date => l_effective_start_date,
412 p_effective_end_date => l_effective_end_date,
413 p_delete_warning => l_delete_warning
414 );
415
416 END Delete_Student_Loan;
417
418 PROCEDURE Update_Student_Loan(
419 p_datetrack_update_mode in varchar2
420 ,p_effective_date in date
421 ,p_business_group_id in number
422 ,p_element_entry_id in number
423 ,p_object_version_number in out nocopy number
424 ,p_start_date in VARCHAR2
425 ,p_end_date in VARCHAR2
426 ,p_subpriority in number
427 ,p_effective_start_date out nocopy date
428 ,p_effective_end_date out nocopy date) is
429
430 l_update_warning BOOLEAN;
431
432 BEGIN
433
434 py_element_entry_api.update_element_entry(
435 p_validate => FALSE,
436 p_datetrack_update_mode => p_datetrack_update_mode,
437 p_effective_date => p_effective_date,
438 p_business_group_id => p_business_group_id,
439 p_element_entry_id => p_element_entry_id,
440 p_object_version_number => p_object_version_number,
441 P_INPUT_VALUE_ID1 =>Get_Input_Value_Id('Start Date',
442 p_effective_date),
443 P_INPUT_VALUE_ID2 =>Get_Input_Value_Id('End Date',
444 p_effective_date),
445 P_SUBPRIORITY =>p_subpriority,
446 P_ENTRY_VALUE1 =>p_start_date,
447 P_ENTRY_VALUE2 =>p_end_date,
448 P_EFFECTIVE_START_DATE =>p_effective_start_date,
449 P_EFFECTIVE_END_DATE =>p_effective_end_date,
450 P_UPDATE_WARNING =>l_update_warning);
451 --
452 -- Done the update
453 --
454 END Update_Student_Loan;
455
456
457 /*Added below functions for bug fix 3336452*/
458
459 /*Function to get the tax district Reference*/
460 FUNCTION get_tax_ref(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
461
462 CURSOR get_asg_tax_ref IS
463 SELECT scl.segment1
464 FROM hr_soft_coding_keyflex scl,
465 fnd_sessions fs,
466 pay_payrolls_f ppf,
467 per_all_assignments_f paaf
468 WHERE paaf.assignment_id = p_assignment_id
469 AND fs.session_id = userenv('sessionid')
470 AND fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
471 AND ppf.payroll_id = paaf.payroll_id
472 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
473 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
474
475 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
476 l_proc VARCHAR2(50);
477
478
479 BEGIN
480 l_proc := g_package_functions || 'get_tax_ref';
481 -- Get tax reference of current assignment.
482 hr_utility.set_location('Entering ' || l_proc,10);
483
484 OPEN get_asg_tax_ref;
485 FETCH get_asg_tax_ref INTO l_asg_tax_ref;
486 CLOSE get_asg_tax_ref;
487
488 hr_utility.set_location('Leaving ' || l_proc,30);
489
490 RETURN l_asg_tax_ref;
491
492 END get_tax_ref;
493
494 /*Function to get the Input value Id's*/
495 FUNCTION get_input_value(p_ele_name IN VARCHAR2, p_iv_name IN VARCHAR2) RETURN NUMBER IS
496
497 CURSOR get_input_value_ids IS
498 SELECT piv.input_value_id
499 FROM fnd_sessions fs,
500 pay_element_types_f pet,
501 pay_input_values_f piv
502 WHERE fs.session_id = userenv('sessionid')
503 AND pet.element_name = p_ele_name
504 AND pet.business_group_id IS NULL
505 AND pet.legislation_code = 'GB'
506 AND fs.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
507 AND pet.element_type_id = piv.element_type_id
508 AND piv.name = p_iv_name
509 AND piv.business_group_id IS NULL
510 AND piv.legislation_code = 'GB'
511 AND fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
512
513 l_cto_input_id NUMBER;
514 l_proc VARCHAR2(50);
515
516 BEGIN
517 l_proc := g_package_functions || 'get_input_value';
518 hr_utility.set_location('Entering ' || l_proc,10);
519
520 OPEN get_input_value_ids;
521 FETCH get_input_value_ids INTO l_cto_input_id;
522 CLOSE get_input_value_ids;
523
524 hr_utility.set_location('Leaving ' || l_proc,30);
525 RETURN l_cto_input_id;
526
527 END get_input_value;
528
529 /*Function to get person id*/
530 FUNCTION get_person_id(p_assignment_id IN NUMBER) RETURN NUMBER IS
531
532 CURSOR get_person(p_assignment_id IN NUMBER) IS
533 SELECT person_id
534 FROM per_all_assignments_f paaf,
535 fnd_sessions fs
536 WHERE fs.session_id = userenv('sessionid')
537 AND paaf.assignment_id =p_assignment_id
538 AND fs.effective_date between paaf.effective_start_date and paaf.effective_end_date;
539
540 l_per_id NUMBER;
541 l_proc VARCHAR2(50);
542 BEGIN
543
544 l_proc := g_package_functions || 'get_person_id';
545 hr_utility.set_location('Entering ' || l_proc,10);
546 OPEN get_person(p_assignment_id);
547 FETCH get_person INTO l_per_id;
548 CLOSE get_person;
549
550 hr_utility.set_location('Leaving ' || l_proc,30);
551 RETURN l_per_id;
552 END get_person_id;
553
554 /*Function to get the current frequency of the assignment*/
555 FUNCTION get_current_freq(p_assignment_id IN NUMBER,
556 p_date_earned IN DATE,
557 p_reference IN VARCHAR2
558 ) RETURN NUMBER IS
559
560 CURSOR get_freq IS
561 SELECT ptpt.number_per_fiscal_year
562 FROM per_all_assignments_f papf,
563 pay_all_payrolls_f pap,
564 per_time_period_types ptpt
565 WHERE papf.assignment_id = p_assignment_id
566 AND p_date_earned BETWEEN papf.effective_start_date and papf.effective_end_date
567 AND pap.payroll_id = papf.payroll_id
568 AND p_date_earned BETWEEN pap.effective_start_date and pap.effective_end_date
569 AND pap.period_type=ptpt.period_type;
570
571 l_freq per_time_period_types.number_per_fiscal_year%TYPE;
572 l_proc VARCHAR2(50) ;
573
574 BEGIN
575 l_proc := g_package_functions || 'get_current_freq';
576 hr_utility.set_location('Entering ' || l_proc,10);
577
578 OPEN get_freq;
579 FETCH get_freq INTO l_freq;
580 CLOSE get_freq;
581
582 hr_utility.set_location('Leaving ' || l_proc,30);
583 RETURN l_freq;
584 END get_current_freq;
585
586 /*Function to get the current pay date of the assignment*/
587 FUNCTION get_current_pay_date(p_assignment_id IN NUMBER
588 ,p_date_earned IN DATE
589 ,p_reference IN VARCHAR2
590 ) RETURN DATE IS
591
592 CURSOR get_curr_pay_date IS
593 SELECT nvl(ptp.regular_payment_date , to_date('01-01-0001','DD-MM-YYYY'))
594 FROM per_all_assignments_f papf,
595 pay_all_payrolls_f pap,
596 per_time_periods ptp
597 WHERE papf.assignment_id = p_assignment_id
598 AND p_date_earned BETWEEN papf.effective_start_date and papf.effective_end_date
599 AND pap.payroll_id = papf.payroll_id
600 AND p_date_earned BETWEEN pap.effective_start_date and pap.effective_end_date
601 AND pap.payroll_id=ptp.payroll_id
602 AND p_date_earned BETWEEN ptp.start_date and ptp.end_date;
603
604 l_current_pay_date DATE;
605 l_proc VARCHAR2(50);
606
607 BEGIN
608 l_proc := g_package_functions || 'get_current_pay_date';
609 hr_utility.set_location('Entering ' || l_proc,10);
610 OPEN get_curr_pay_date;
611 FETCH get_curr_pay_date INTO l_current_pay_date;
612 CLOSE get_curr_pay_date;
613
614 hr_utility.set_location('Leaving ' || l_proc,30);
615 RETURN l_current_pay_date;
616
617 END get_current_pay_date;
618
619 /*Function to count the number of Main CTO Entries*/
620 FUNCTION count_main_cto_entry(p_assignment_id IN NUMBER,
621 p_date_earned IN DATE,
622 p_reference IN VARCHAR2) RETURN NUMBER IS
623
624 l_count NUMBER := 0;
625 l_count_n NUMBER :=0;
626 l_count_null NUMBER :=0;
627 g_cto_main_iv_id NUMBER;
628 g_cto_ntpp_main_iv_id NUMBER;
629 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
630 g_cto_main_ref_id NUMBER;
631 g_cto_ntpp_main_ref_id NUMBER;
632 l_person_id NUMBER;
633 l_count_total NUMBER;
634
635 CURSOR get_main_count(p_asg_tax_ref IN VARCHAR2,p_person_id IN NUMBER,p_entry_value IN VARCHAR2) IS
636 SELECT /*+ ORDERED use_nl(papf, paaf1, paaf2, ppf, piv1, piv2, peef1, peef2, scl)*/
637 count(*) cnt
638 from per_all_people_f papf,
639 per_all_assignments_f paaf1,
640 per_all_assignments_f paaf2,
641 pay_all_payrolls_f ppf,
642 pay_input_values_f piv1 ,
643 pay_input_values_f piv2,
644 pay_element_entries_f peef1,
645 pay_element_entries_f peef2,
646 pay_element_entry_values_f peev1,
647 pay_element_entry_values_f peev2,
648 hr_soft_coding_keyflex scl
649 where papf.person_id = p_person_id
650 and papf.person_id = paaf1.person_id
651 and papf.person_id = paaf2.person_id
652 -- and paaf1.person_id = paaf2.person_id -- redundant
653 and ppf.payroll_id = paaf2.payroll_id
654 and ppf.payroll_id = paaf1.payroll_id
655 AND scl.segment1 = p_asg_tax_ref
656 and piv1.input_value_id in (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
657 and piv2.input_value_id in (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
658 and piv1.input_value_id = peev1.input_value_id
659 and piv2.input_value_id = peev2.input_value_id
660 AND paaf1.assignment_id = peef1.assignment_id
661 AND paaf2.assignment_id = peef2.assignment_id
662 and peef1.element_entry_id = peef2.element_entry_id
663 and peev1.element_entry_id = peev2.element_entry_id
664 and piv1.element_type_id = piv2.element_type_id
665 AND peef1.element_entry_id = peev1.element_entry_id
666 AND peef2.element_entry_id = peev2.element_entry_id -- AND fs.session_id = userenv('sessionid')
667 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
668 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
669 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
670 AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
671 AND p_date_earned BETWEEN peef1.effective_start_date AND peef1.effective_end_date
672 AND p_date_earned BETWEEN peef2.effective_start_date AND peef2.effective_end_date
673 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
674 AND p_date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
675 AND p_date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
676 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
677 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
678 AND scl.segment1 = p_asg_tax_ref
679 and peev1.screen_entry_value = p_reference
680 group by peev1.screen_entry_value,peev2.screen_entry_Value
681
682 having ( peev2.screen_entry_Value =p_entry_value) ;
683
684
685 CURSOR get_main_entry_count(p_asg_tax_ref IN VARCHAR2,p_person_id IN NUMBER) IS
686 SELECT /*+ ORDERED use_nl(papf, paaf1, paaf2, ppf, piv1, piv2, peef1, peef2, scl)*/
687 count(*) cnt
688 from per_all_people_f papf,
689 per_all_assignments_f paaf1,
690 per_all_assignments_f paaf2,
691 pay_all_payrolls_f ppf,
692 pay_input_values_f piv1 ,
693 pay_input_values_f piv2,
694 pay_element_entries_f peef1,
695 pay_element_entries_f peef2,
696 pay_element_entry_values_f peev1,
697 pay_element_entry_values_f peev2,
698 hr_soft_coding_keyflex scl
699 where papf.person_id = p_person_id
700 and papf.person_id = paaf1.person_id
701 and papf.person_id = paaf2.person_id
702 -- and paaf1.person_id = paaf2.person_id -- redundant
703 and ppf.payroll_id = paaf2.payroll_id
704 and ppf.payroll_id = paaf1.payroll_id
705 AND scl.segment1 = p_asg_tax_ref
706 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
707 and piv1.input_value_id in (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
708 and piv2.input_value_id in (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
709 and piv1.input_value_id = peev1.input_value_id
710 and piv2.input_value_id = peev2.input_value_id
711 AND paaf1.assignment_id = peef1.assignment_id
712 AND paaf2.assignment_id = peef2.assignment_id
713 and peef1.element_entry_id = peef2.element_entry_id
714 and peev1.element_entry_id = peev2.element_entry_id
715 and piv1.element_type_id = piv2.element_type_id
716 AND peef1.element_entry_id = peev1.element_entry_id
717 AND peef2.element_entry_id = peev2.element_entry_id -- AND fs.session_id = userenv('sessionid')
718 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
719 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
720 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
721 AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
722 AND p_date_earned BETWEEN peef1.effective_start_date AND peef1.effective_end_date
723 AND p_date_earned BETWEEN peef2.effective_start_date AND peef2.effective_end_date
724 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
725 AND p_date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
726 AND p_date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
727 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
728 and peev1.screen_entry_value = p_reference
729 group by peev1.screen_entry_value;
730
731
732 --
733 CURSOR chk_prim_asg(p_asg_tax_ref IN VARCHAR2) IS
734 SELECT 1 cnt
735 FROM per_all_assignments_f paaf1,
736 per_all_assignments_f paaf2,
737 pay_all_payrolls_f ppf,
738 hr_soft_coding_keyflex scl,
739 pay_element_entries_f peef,
740 pay_element_entry_values_f peev
741 WHERE paaf1.assignment_id = p_assignment_id
742 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
743 AND paaf1.person_id = paaf2.person_id
744 AND nvl(paaf2.primary_flag, 'N') = 'Y'
745 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
746 AND paaf2.payroll_id = ppf.payroll_id
747 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
748 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
749 AND scl.segment1 = p_asg_tax_ref
750 AND paaf2.assignment_id = peef.assignment_id
751 AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
752 AND peef.element_entry_id = peev.element_entry_id
753 AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
754 AND peev.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
755 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = p_reference;
756
757 --
758 l_proc VARCHAR2(50);
759 BEGIN
760
761 l_proc := g_package_functions || 'count_main_cto_entry';
762 hr_utility.set_location('Entering ' || l_proc,10);
763
764 /*Get tax reference of current assignment*/
765 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
766
767 /*Get Input value id*/
768 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
769 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
770
771 g_cto_main_ref_id := get_input_value('Court Order','Reference');
772 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
773
774 /*Get Person Id*/
775 l_person_id := get_person_id(p_assignment_id);
776
777
778 OPEN get_main_count(l_asg_tax_ref,l_person_id,'Y');
779 FETCH get_main_count INTO l_count;
780 CLOSE get_main_count;
781 --
782
783 IF l_count = 0 THEN
784 OPEN get_main_count(l_asg_tax_ref,l_person_id,'N');
785 FETCH get_main_count INTO l_count;
786 OPEN get_main_entry_count(l_asg_tax_ref,l_person_id);
787 FETCH get_main_entry_count INTO l_count_total;
788 CLOSE get_main_entry_count;
789 CLOSE get_main_count;
790 if l_count=l_count_total then
791 l_count := 0;
792 else
793 OPEN chk_prim_asg(l_asg_tax_ref);
794 FETCH chk_prim_asg INTO l_count;
795 IF chk_prim_asg%NOTFOUND THEN
796 l_count := 0;
797 END IF;
798 CLOSE chk_prim_asg;
799
800 end if;
801 --
802 END IF;
803 --
804 g_count_main_cto_entry := l_count;
805 --
806 hr_utility.set_location('Leaving ' || l_proc,30);
807 RETURN l_count;
808
809 END count_main_cto_entry;
810
811 FUNCTION get_main_cto_pay_date(p_assignment_id IN NUMBER,
812 p_date_earned IN DATE,
813 p_reference IN VARCHAR2
814 ) RETURN DATE IS
815
816
817 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
818 l_asg_payroll_id pay_payrolls_f.payroll_id%TYPE;
819 l_asg_period_start_date per_time_periods.start_date%TYPE;
820 g_cto_main_iv_id NUMBER;
821 g_cto_ntpp_main_iv_id NUMBER;
822 g_cto_main_ref_id NUMBER;
823 g_cto_ntpp_main_ref_id NUMBER;
824 l_person_id NUMBER;
825
826
827 CURSOR get_asg_period_start_date IS
828 SELECT ptp.start_date
829 FROM per_time_periods ptp
830 WHERE ptp.payroll_id = l_asg_payroll_id
831 AND p_date_earned =ptp.regular_payment_date;
832
833 CURSOR get_main_payroll_id(p_asg_tax_ref VARCHAR2,p_person_id NUMBER) is
834 SELECT ppf.payroll_id
835 FROM per_all_assignments_f paaf1,
836 per_all_assignments_f paaf2,
837 pay_all_payrolls_f ppf,
838 hr_soft_coding_keyflex scl,
839 pay_element_entries_f peef,
840 pay_element_entry_values_f peev,
841 pay_element_entry_values_f peev1,
842 per_all_people_f papf
843 WHERE
844 p_date_earned BETWEEN paaf1.effective_start_date and paaf1.effective_end_date
845 AND paaf1.person_id = paaf2.person_id
846 AND p_date_earned BETWEEN paaf2.effective_start_date and paaf2.effective_end_date
847 AND paaf2.payroll_id = ppf.payroll_id
848 AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
849 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
850 AND scl.segment1 = p_asg_tax_ref
851 AND paaf2.assignment_id = peef.assignment_id
852 AND p_date_earned BETWEEN peef.effective_start_date and peef.effective_end_date
853 AND peef.element_entry_id = peev.element_entry_id
854 AND p_date_earned BETWEEN peev.effective_start_date and peev.effective_end_date
855 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
856 AND peev1.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
857 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = 'Y'
858 AND nvl(peev1.SCREEN_ENTRY_VALUE, 'N') = p_reference
859 AND peev.element_entry_id=peev1.element_entry_id
860 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
861 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
862 AND papf.person_id = p_person_id
863 AND papf.person_id = paaf1.person_id
864 AND papf.person_id = paaf2.person_id;
865
866
867 CURSOR get_prim_payroll_id(p_asg_tax_ref VARCHAR2) IS
868 SELECT ppf.payroll_id
869 FROM per_all_assignments_f paaf1,
870 per_all_assignments_f paaf2,
871 pay_all_payrolls_f ppf,
872 hr_soft_coding_keyflex scl,
873 pay_element_entries_f peef,
874 pay_element_entry_values_f peev
875 WHERE paaf1.assignment_id = p_assignment_id
876 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
877 AND paaf1.person_id = paaf2.person_id
878 AND nvl(paaf2.primary_flag, 'N') = 'Y'
879 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
880 AND paaf2.payroll_id = ppf.payroll_id
881 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
882 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
883 AND scl.segment1 = p_asg_tax_ref
884 AND paaf2.assignment_id = peef.assignment_id
885 AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
886 AND peef.element_entry_id = peev.element_entry_id
887 AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
888 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id);
889
890 l_payroll_id NUMBER;
891 l_pay_date DATE;
892 l_count NUMBER := 0;
893
894
895 CURSOR get_pay_date(p_payroll_id IN NUMBER) IS
896 SELECT nvl(ptp.regular_payment_date , to_date('01-01-0001','DD-MM-YYYY'))
897 FROM per_time_periods ptp
898 WHERE p_date_earned BETWEEN ptp.start_date and ptp.end_date
899 AND ptp.payroll_id =p_payroll_id;
900
901 l_proc VARCHAR2(50);
902
903 BEGIN
904 l_proc := g_package_functions || 'get_main_cto_pay_date';
905 hr_utility.set_location('Entering ' || l_proc,10);
906 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
907
908 l_person_id := get_person_id(p_assignment_id);
909
910 /*Get Input value id*/
911 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
912 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
913
914 g_cto_main_ref_id := get_input_value('Court Order','Reference');
915 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
916
917 OPEN get_asg_period_start_date;
918 FETCH get_asg_period_start_date into l_asg_period_start_date;
919 CLOSE get_asg_period_start_date;
920
921 --
922 IF nvl(p_assignment_id, -1) <> nvl(g_asg_id, -999) THEN
923 l_count := count_main_cto_entry(p_assignment_id,p_date_earned,p_reference);
924 ELSE
925 l_count := g_count_main_cto_entry;
926 END IF;
927
928 --
929 IF nvl(l_count, 0) = 1 THEN
930 --
931 OPEN get_main_payroll_id(l_asg_tax_ref,l_person_id);
932 FETCH get_main_payroll_id INTO l_payroll_id;
933
934 IF get_main_payroll_id%NOTFOUND THEN
935 l_payroll_id := NULL;
936 END IF;
937 CLOSE get_main_payroll_id;
938 --
939 IF l_payroll_id IS NULL THEN
940 OPEN get_prim_payroll_id(l_asg_tax_ref);
941 FETCH get_prim_payroll_id INTO l_payroll_id;
942 IF get_prim_payroll_id%NOTFOUND THEN
943 l_payroll_id := NULL;
944 END IF;
945 CLOSE get_prim_payroll_id;
946 END IF;
947 --
948 IF l_payroll_id IS NULL THEN
949 RETURN to_date('01-01-0001', 'DD-MM-YYYY');
950 ELSE
951 OPEN get_pay_date(l_payroll_id);
952 FETCH get_pay_date INTO l_pay_date;
953
954 IF get_pay_date%NOTFOUND THEN
955 l_pay_date := to_date('01-01-0001', 'DD-MM-YYYY');
956 END IF;
957 CLOSE get_pay_date;
958 END IF;
959 ELSE
960
961 l_pay_date := to_date('01-01-0001', 'DD-MM-YYYY');
962 END IF;
963 --
964 hr_utility.set_location('Leaving ' || l_proc,30);
965 RETURN l_pay_date;
966
967 END get_main_cto_pay_date;
968
969
970
971 FUNCTION get_main_cto_freq(p_assignment_id IN NUMBER,
972 p_date_earned IN DATE,
973 p_reference IN VARCHAR2
974 ) RETURN NUMBER IS
975
976 --
977 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
978 l_person_id NUMBER;
979 g_cto_main_iv_id NUMBER;
980 g_cto_ntpp_main_iv_id NUMBER;
981 g_cto_main_ref_id NUMBER;
982 g_cto_ntpp_main_ref_id NUMBER;
983
984 --
985 CURSOR get_main_payroll_id(p_asg_tax_ref VARCHAR2, p_person_id NUMBER) IS
986 SELECT ppf.payroll_id
987 FROM per_all_assignments_f paaf1,
988 per_all_assignments_f paaf2,
989 pay_all_payrolls_f ppf,
990 hr_soft_coding_keyflex scl,
991 pay_element_entries_f peef,
992 pay_element_entry_values_f peev,
993 pay_element_entry_values_f peev1,
994 per_all_people_f papf
995 WHERE p_date_earned BETWEEN paaf1.effective_start_date and paaf1.effective_end_date
996 AND paaf1.person_id = paaf2.person_id
997 AND p_date_earned BETWEEN paaf2.effective_start_date and paaf2.effective_end_date
998 AND paaf2.payroll_id = ppf.payroll_id
999 AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
1000 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1001 AND scl.segment1 = p_asg_tax_ref
1002 AND paaf2.assignment_id = peef.assignment_id
1003 AND p_date_earned BETWEEN peef.effective_start_date and peef.effective_end_date
1004 AND peef.element_entry_id = peev.element_entry_id
1005 AND p_date_earned BETWEEN peev.effective_start_date and peev.effective_end_date
1006 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
1007 AND peev1.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
1008 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = 'Y'
1009 AND nvl(peev1.SCREEN_ENTRY_VALUE, 'N') = p_reference
1010 AND peev.element_entry_id=peev1.element_entry_id
1011 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
1012 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
1013 AND papf.person_id = p_person_id
1014 AND papf.person_id = paaf1.person_id
1015 AND papf.person_id = paaf2.person_id;
1016 --
1017 CURSOR get_prim_payroll_id(p_asg_tax_ref VARCHAR2) IS
1018 SELECT ppf.payroll_id
1019 FROM fnd_sessions fs,
1020 per_all_assignments_f paaf1,
1021 per_all_assignments_f paaf2,
1022 pay_all_payrolls_f ppf,
1023 hr_soft_coding_keyflex scl,
1024 pay_element_entries_f peef,
1025 pay_element_entry_values_f peev
1026 WHERE paaf1.assignment_id = p_assignment_id
1027 AND fs.session_id = userenv('sessionid')
1028 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1029 AND paaf1.person_id = paaf2.person_id
1030 AND nvl(paaf2.primary_flag, 'N') = 'Y'
1031 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1032 AND paaf2.payroll_id = ppf.payroll_id
1033 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1034 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1035 AND scl.segment1 = p_asg_tax_ref
1036 AND paaf2.assignment_id = peef.assignment_id
1037 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
1038 AND peef.element_entry_id = peev.element_entry_id
1039 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1040 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id);
1041 --
1042 l_payroll_id NUMBER;
1043 l_freq NUMBER;
1044 l_count NUMBER := 0;
1045
1046 --
1047 CURSOR get_freq IS
1048 SELECT number_per_fiscal_year
1049 FROM per_time_periods ptp,
1050 per_time_period_types ptpt
1051 WHERE p_date_earned BETWEEN ptp.start_date AND ptp.end_Date
1052 AND ptp.payroll_id = l_payroll_id
1053 AND ptp.period_type = ptpt.period_type;
1054 --
1055
1056 l_proc VARCHAR2(50);
1057 BEGIN
1058 --
1059 l_proc := g_package_functions || 'get_main_cto_freq';
1060 hr_utility.set_location('Entering ' || l_proc,10);
1061
1062 -- Get tax ref of current asg.
1063 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
1064 l_person_id := get_person_id(p_assignment_id);
1065
1066 /*Get Input value id*/
1067 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
1068 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
1069
1070 g_cto_main_ref_id := get_input_value('Court Order','Reference');
1071 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
1072 --
1073 IF nvl(p_assignment_id, -1) <> nvl(g_asg_id, -999) THEN
1074
1075 l_count := count_main_cto_entry(p_assignment_id,p_date_earned,p_reference);
1076 ELSE
1077 l_count := g_count_main_cto_entry;
1078 END IF;
1079 --
1080 IF nvl(l_count, 0) = 1 THEN
1081
1082 OPEN get_main_payroll_id(l_asg_tax_ref,l_person_id);
1083 FETCH get_main_payroll_id INTO l_payroll_id;
1084 IF get_main_payroll_id%NOTFOUND THEN
1085
1086 l_payroll_id := NULL;
1087 END IF;
1088 CLOSE get_main_payroll_id;
1089 --
1090 IF l_payroll_id IS NULL THEN
1091
1092 OPEN get_prim_payroll_id(l_asg_tax_ref);
1093 FETCH get_prim_payroll_id INTO l_payroll_id;
1094 IF get_prim_payroll_id%NOTFOUND THEN
1095 l_payroll_id := NULL;
1096 END IF;
1097 CLOSE get_prim_payroll_id;
1098 END IF;
1099 --
1100 IF l_payroll_id IS NULL THEN
1101 RETURN null;
1102 ELSE
1103 OPEN get_freq;
1104 FETCH get_freq INTO l_freq;
1105 IF get_freq%NOTFOUND THEN
1106 l_freq := 0;
1107 END IF;
1108 CLOSE get_freq;
1109 END IF;
1110 --
1111 ELSE
1112
1113 l_freq := 0;
1114 END IF;
1115 --
1116 hr_utility.set_location('Leaving ' || l_proc,30);
1117 RETURN l_freq;
1118
1119 END get_main_cto_freq;
1120
1121
1122 FUNCTION get_main_entry_values(p_assignment_id IN NUMBER,
1123 p_date_earned IN DATE,
1124 p_reference IN VARCHAR2,
1125 p_input_value_name IN VARCHAR2,
1126 p_count OUT NOCOPY NUMBER
1127 ) RETURN VARCHAR2 IS
1128
1129 --
1130 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
1131 --
1132
1133 l_cto_iv_id NUMBER;
1134 l_cto_ntpp_iv_id NUMBER;
1135
1136 g_cto_main_iv_id NUMBER;
1137 g_cto_ntpp_main_iv_id NUMBER;
1138 g_cto_main_ref_id NUMBER;
1139 g_cto_ntpp_main_ref_id NUMBER;
1140 l_person_id NUMBER;
1141
1142 CURSOR get_main_entry_id(p_asg_tax_ref VARCHAR2,p_person_id NUMBER) IS
1143 SELECT peef.element_entry_id
1144 FROM per_all_assignments_f paaf1,
1145 per_all_assignments_f paaf2,
1146 pay_all_payrolls_f ppf,
1147 hr_soft_coding_keyflex scl,
1148 pay_element_entries_f peef,
1149 pay_element_entry_values_f peev,
1150 pay_element_entry_values_f peev1,
1151 per_all_people_f papf
1152 WHERE --paaf1.assignment_id = p_assignment_id AND
1153 p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1154 AND paaf1.person_id = paaf2.person_id
1155 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1156 AND paaf2.payroll_id = ppf.payroll_id
1157 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1158 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1159 AND scl.segment1 = p_asg_tax_ref
1160 AND paaf2.assignment_id = peef.assignment_id
1161 AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
1162 AND peef.element_entry_id = peev.element_entry_id
1163 AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
1164 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
1165 AND peev1.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
1166 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = 'Y'
1167 AND nvl(peev1.SCREEN_ENTRY_VALUE, 'N') = p_reference
1168 AND peev1.element_entry_id=peev.element_entry_id
1169 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
1170 AND peef.target_entry_id IS NULL
1171 AND papf.person_id =p_person_id
1172 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
1173 AND papf.person_id = paaf1.person_id
1174 AND papf.person_id = paaf2.person_id;
1175
1176 --
1177 CURSOR chk_prim_entry_id(p_asg_tax_ref VARCHAR2) IS
1178 SELECT peef.element_entry_id
1179 FROM
1180 per_all_assignments_f paaf1,
1181 per_all_assignments_f paaf2,
1182 pay_all_payrolls_f ppf,
1183 hr_soft_coding_keyflex scl,
1184 pay_element_entries_f peef,
1185 pay_element_entry_values_f peev
1186 WHERE paaf1.assignment_id = p_assignment_id
1187 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1188 AND paaf1.person_id = paaf2.person_id
1189 AND nvl(paaf2.primary_flag, 'N') = 'Y'
1190 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1191 AND paaf2.payroll_id = ppf.payroll_id
1192 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1193 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1194 AND scl.segment1 = p_asg_tax_ref
1195 AND paaf2.assignment_id = peef.assignment_id
1196 AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
1197 AND peef.element_entry_id = peev.element_entry_id
1198 AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
1199 AND peev.input_value_id IN (g_cto_main_ref_id, g_cto_ntpp_main_ref_id)
1200 AND peef.target_entry_id IS NULL
1201 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = p_reference;
1202 --
1203 l_entry_id NUMBER;
1204
1205
1206 CURSOR get_value IS
1207 SELECT peev.screen_entry_value
1208 FROM pay_element_entry_values_f peev
1209 WHERE p_date_earned BETWEEN peev.effective_start_date and peev.effective_end_date
1210 AND peev.element_entry_id = l_entry_id
1211 AND peev.input_value_id IN (l_cto_iv_id, l_cto_ntpp_iv_id);
1212
1213 l_value pay_element_entry_values_f.screen_entry_value%TYPE;
1214 l_count NUMBER;
1215 l_proc VARCHAR2(50);
1216
1217 BEGIN
1218
1219 l_proc := g_package_functions || 'get_main_entry_values';
1220 hr_utility.set_location('Entering ' || l_proc,10);
1221
1222 -- Get tax ref of current asg.
1223 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
1224 l_person_id := get_person_id(p_assignment_id);
1225 --
1226 IF nvl(p_assignment_id, -1) <> nvl(g_asg_id, -999) THEN
1227
1228 l_count := count_main_cto_entry(p_assignment_id,p_date_earned,p_reference);
1229 ELSE
1230 l_count := g_count_main_cto_entry;
1231 END IF;
1232
1233 p_count := l_count;
1234 --
1235 IF nvl(l_count, 0) = 1 THEN
1236
1237
1238 /*Get Input value id*/
1239 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
1240 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
1241 g_cto_main_ref_id := get_input_value('Court Order','Reference');
1242 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
1243
1244 l_cto_iv_id := get_input_value('Court Order',p_input_value_name);
1245 l_cto_ntpp_iv_id := get_input_value('Court Order NTPP',p_input_value_name);
1246
1247
1248 OPEN get_main_entry_id(l_asg_tax_ref,l_person_id);
1249 FETCH get_main_entry_id INTO l_entry_id;
1250
1251 IF get_main_entry_id%NOTFOUND THEN
1252
1253 OPEN chk_prim_entry_id(l_asg_tax_ref);
1254 FETCH chk_prim_entry_id INTO l_entry_id;
1255 CLOSE chk_prim_entry_id;
1256
1257 END IF;
1258 CLOSE get_main_entry_id;
1259 --
1260 OPEN get_value;
1261 FETCH get_value INTO l_value;
1262 CLOSE get_value;
1263 --
1264 ELSE
1265 l_value := NULL;
1266 END IF;
1267 --
1268 hr_utility.set_location('Leaving ' || l_proc,30);
1269 RETURN l_value;
1270
1271 END get_main_entry_values;
1272
1273 FUNCTION get_main_initial_debt(p_assignment_id IN NUMBER,
1274 p_date_earned IN DATE,
1275 p_reference IN VARCHAR2
1276 ) RETURN NUMBER IS
1277
1278 l_value NUMBER;
1279 l_count NUMBER;
1280 l_proc VARCHAR2(50);
1281
1282 BEGIN
1283
1284 l_proc := g_package_functions || 'get_main_initial_debt';
1285 hr_utility.set_location('Entering ' || l_proc,10);
1286 l_value := nvl(to_number(get_main_entry_values(p_assignment_id,p_date_earned,p_reference,'Initial Debt',l_count)),0);
1287 hr_utility.set_location('Leaving ' || l_proc,30);
1288
1289 RETURN l_value;
1290
1291
1292 END get_main_initial_debt;
1293
1294 FUNCTION get_main_fee(p_assignment_id IN NUMBER,
1295 p_date_earned IN DATE,
1296 p_reference IN VARCHAR2
1297 ) RETURN NUMBER IS
1298
1299 l_value NUMBER;
1300 l_count NUMBER;
1301 l_proc VARCHAR2(50);
1302
1303 BEGIN
1304 l_proc := g_package_functions || 'get_main_fee';
1305 hr_utility.set_location('Entering ' || l_proc,10);
1306 l_value := nvl(to_number(get_main_entry_values(p_assignment_id,p_date_earned,p_reference,'Fee',l_count)),0);
1307 hr_utility.set_location('Leaving ' || l_proc,30);
1308
1309
1310 RETURN l_value;
1311
1312
1313 END get_main_fee;
1314
1315 FUNCTION check_ref(p_assignment_id IN NUMBER,
1316 p_date_earned IN DATE,
1317 p_reference IN VARCHAR2
1318 ) RETURN VARCHAR2 IS
1319
1320 l_main_ref pay_element_entry_values_f.screen_entry_value%TYPE;
1321 l_count NUMBER;
1322 l_proc VARCHAR2(50);
1323
1324 BEGIN
1325 l_proc := g_package_functions || 'check_ref';
1326 hr_utility.set_location('Entering ' || l_proc,10);
1327 l_main_ref := nvl(get_main_entry_values(p_assignment_id,p_date_earned,p_reference,'Reference',l_count),'Unknown');
1328
1329
1330 IF nvl(l_count, 0) = 1 and l_main_ref = p_reference then
1331 -- Valid reference
1332 RETURN 'Y';
1333 ELSE
1334 -- Invalid reference
1335 IF l_main_ref = 'Unknown' THEN
1336 RETURN 'K';
1337 ELSE
1338 RETURN 'N';
1339 END IF;
1340 END IF;
1341
1342 hr_utility.set_location('Leaving ' || l_proc,30);
1343
1344 END check_ref;
1345
1346
1347 FUNCTION get_main_entry_value(p_assignment_id IN NUMBER,
1348 p_date_earned IN DATE,
1349 p_reference IN VARCHAR2
1350 ) RETURN VARCHAR2 is
1351
1352
1353 g_cto_main_iv_id NUMBER;
1354 g_cto_ntpp_main_iv_id NUMBER;
1355 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
1356 g_cto_main_ref_id NUMBER;
1357 g_cto_ntpp_main_ref_id NUMBER;
1358 l_main_entry_value VARCHAR2(10);
1359
1360 CURSOR get_main_value(p_asg_tax_ref IN VARCHAR2) IS
1361 SELECT /*+ ORDERED use_nl(paaf1, paaf2, ppf, piv1, piv2, peef1, peef2, scl)*/
1362 peev2.screen_entry_value main_entry_value
1363 from per_all_assignments_f paaf1,
1364 per_all_assignments_f paaf2,
1365 pay_all_payrolls_f ppf,
1366 pay_input_values_f piv1 ,
1367 pay_input_values_f piv2,
1368 pay_element_entries_f peef1,
1369 pay_element_entries_f peef2,
1370 pay_element_entry_values_f peev1,
1371 pay_element_entry_values_f peev2 ,
1372 hr_soft_coding_keyflex scl
1373 where paaf1.assignment_id = p_assignment_id
1374 AND paaf2.assignment_id = paaf1.assignment_id
1375 and paaf2.payroll_id = ppf.payroll_id
1376 and paaf1.payroll_id = ppf.payroll_id
1377 AND peef1.assignment_id = paaf1.assignment_id
1378 AND peef2.assignment_id = paaf2.assignment_id
1379 and piv1.input_value_id in (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
1380 and piv2.input_value_id in (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
1381 and piv1.input_value_id = peev1.input_value_id
1382 and piv2.input_value_id = peev2.input_value_id
1383 and peef1.element_entry_id = peef2.element_entry_id
1384 and peev1.element_entry_id = peev2.element_entry_id
1385 and piv1.element_type_id = piv2.element_type_id
1386 AND peef1.element_entry_id = peev1.element_entry_id
1387 AND peef2.element_entry_id = peev2.element_entry_id
1388 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1389 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1390 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
1391 AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
1392 AND p_date_earned BETWEEN peef1.effective_start_date AND peef1.effective_end_date
1393 AND p_date_earned BETWEEN peef2.effective_start_date AND peef2.effective_end_date
1394 AND p_date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
1395 AND p_date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
1396 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1397 AND scl.segment1 = p_asg_tax_ref
1398 and peev1.screen_entry_value = p_reference;
1399
1400 l_proc VARCHAR2(50);
1401
1402 BEGIN
1403
1404 l_proc := g_package_functions || 'get_main_entry_value';
1405 hr_utility.set_location('Entering ' || l_proc,10);
1406
1407 /*Get tax reference of current assignment*/
1408 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
1409
1410
1411 /*Get Input value id*/
1412 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
1413 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
1414
1415 g_cto_main_ref_id := get_input_value('Court Order','Reference');
1416 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
1417
1418 OPEN get_main_value(l_asg_tax_ref);
1419 FETCH get_main_value INTO l_main_entry_value;
1420 CLOSE get_main_value;
1421
1422 hr_utility.set_location('Leaving ' || l_proc,30);
1423 RETURN l_main_entry_value;
1424
1425 END get_main_entry_value;
1426
1427
1428 /*Added for bug fix 4395503*/
1429 FUNCTION entry_exists( p_element_entry_id in number
1430 ,p_date_earned in date
1431 ,p_asg_action_id in number
1432 ,p_reference in varchar2) RETURN VARCHAR2
1433 IS
1434 --
1435 v_exists VARCHAR2(100) := 'N';
1436 v_reversed VARCHAR2(1) := 'N';
1437 v_message varchar2(10) := 'N';
1438 v_value varchar2(100) := 'Unknown';
1439
1440 CURSOR csr_get_reference
1441 IS
1442 SELECT nvl(prrv.result_value,'Unknown')
1443 FROM pay_run_results prr,
1444 pay_run_result_values prrv,
1445 pay_assignment_actions pac,
1446 pay_input_values_f piv ,
1447 pay_payroll_actions ppa
1448 WHERE prr.run_result_id = prrv.run_result_id
1449 AND prr.entry_type = 'E'
1450 AND PRR.source_type IN ('E', 'I')
1451 AND prr.source_id = p_element_entry_id
1452 AND pac.assignment_action_id = prr.assignment_action_id
1453 --AND pac.action_status in ('C')
1454 AND pac.action_status in ('C','S') --Modified for the bug 10066755
1455 and ppa.action_type in ('R','Q')
1456 AND ppa.payroll_action_id = pac.payroll_action_id
1457 AND pac.assignment_action_id = (SELECT max(pac1.assignment_action_id)
1458 FROM pay_assignment_actions pac1,
1459 pay_run_results prr1,
1460 pay_payroll_actions ppa1
1461 WHERE pac1.assignment_action_id <> p_asg_action_id
1462 AND pac1.assignment_action_id = prr1.assignment_action_id
1463 AND ppa1.payroll_action_id = pac1.payroll_action_id
1464 AND prr1.source_id = p_element_entry_id
1465 --AND pac1.action_status in ('C')
1466 AND pac1.action_status in ('C','S') --Modified for the bug 10066755
1467 and ppa1.action_type in ('R','Q')
1468 and prr1.entry_type = 'E'
1469 AND PRR1.source_type IN ('E', 'I') )
1470 AND piv.legislation_code = 'GB'
1471 AND piv.name = 'Reference'
1472 AND piv.input_value_id = prrv.input_value_id
1473 AND p_date_earned between piv.effective_start_date and piv.effective_end_date ;
1474
1475
1476 CURSOR csr_get_results
1477 IS
1478 SELECT PRR.run_result_id
1479 FROM pay_run_results PRR,
1480 pay_assignment_actions ASA,
1481 pay_payroll_actions PPA
1482 WHERE PRR.source_id = p_element_entry_id
1483 AND PRR.source_type IN ('E', 'I')
1484 AND PRR.status in ('P', 'PA', 'R', 'O')
1485 AND ASA.assignment_action_id = PRR.assignment_action_id
1486 --AND asa.action_status in ( 'C')
1487 AND asa.action_status in ('C','S') --Modified for the bug 10066755
1488 and ppa.action_type in ('R','Q')
1489 AND PPA.payroll_action_id = ASA.payroll_action_id
1490 -- Check whether the run_result has been revered.
1491 AND not exists (SELECT null
1492 FROM pay_run_results prr2
1493 WHERE prr2.source_id = PRR.run_result_id
1494 AND prr2.source_type in ('R', 'V'));
1495 --
1496
1497 l_proc VARCHAR2(100);
1498
1499 BEGIN
1500 --
1501 l_proc := g_package_functions || 'entry_exists';
1502 hr_utility.set_location('Entering ' || l_proc,10);
1503
1504 IF p_date_earned >= to_date('06-04-2006','DD-MM-YYYY') then
1505
1506 OPEN csr_get_results;
1507 FETCH csr_get_results INTO v_exists;
1508
1509 IF csr_get_results%NOTFOUND THEN
1510
1511 IF (p_reference is null or p_reference = 'Unknown') THEN
1512 v_message := 'X';
1513 ELSE
1514 v_message := 'Y';
1515 END IF;
1516
1517 ELSE
1518
1519 OPEN csr_get_reference;
1520 FETCH csr_get_reference INTO v_value;
1521
1522 IF v_value = p_reference THEN
1523 v_message := 'Y';
1524 ELSE
1525 v_message := 'N';
1526 END IF;
1527 CLOSE csr_get_reference;
1528
1529 END IF;
1530 CLOSE csr_get_results;
1531
1532 RETURN v_message;
1533 ELSE
1534 v_message := 'Y';
1535 RETURN v_message;
1536 END IF;
1537
1538 hr_utility.set_location('Leaving ' || l_proc,30);
1539
1540 EXCEPTION when NO_DATA_FOUND then
1541 hr_utility.set_location('entry_exists', 30);
1542 RETURN v_message;
1543 --
1544 END entry_exists;
1545
1546 FUNCTION GET_CO_EARNINGS_PTD
1547 (p_assignment_id IN NUMBER ,
1548 p_date_earned IN DATE,
1549 p_element_entry_id IN NUMBER,
1550 p_element_type_id IN NUMBER)
1551 return number
1552 --return varchar2
1553 is
1554 cursor c_input_value
1555 is
1556 select max(decode(trim(pivf.name),'Type',pivf.input_value_id, null)) type_id,
1557 max(decode(trim(pivf.name),'Order Amount',pivf.input_value_id, null)) ndr_id,
1558 max(decode(trim(pivf.name),'DEO Overriding Frequency',pivf.input_value_id, null)) override_freq_id,
1559 max(decode(trim(pivf.name),'Reference',pivf.input_value_id, null)) reference_id
1560 from pay_element_types_f petf,
1561 pay_input_values_f pivf
1562 where petf.element_type_id = p_element_type_id -- in ('Court Order','Court Order NTPP')
1563 and petf.LEGISLATION_CODE ='GB'
1564 and petf.element_type_id = pivf.element_type_id
1565 and p_date_earned between petf.effective_start_date and petf.effective_end_date
1566 and p_date_earned between pivf.effective_start_date and pivf.effective_end_date;
1567
1568 cursor c_entry_values(p_input_value c_input_value%rowtype)
1569 is
1570 select max(decode(trim(peevf.input_value_id),p_input_value.type_id,screen_entry_value, null)) deo_type,
1571 max(decode(trim(peevf.input_value_id),p_input_value.ndr_id,screen_entry_value, null)) deo_ndr,
1572 max(decode(trim(peevf.input_value_id),p_input_value.override_freq_id,screen_entry_value, null)) override_freq
1573 from pay_element_entry_values_f peevf
1574 where p_element_entry_id = peevf.element_entry_id
1575 and peevf.input_value_id in (p_input_value.type_id ,
1576 p_input_value.ndr_id ,
1577 p_input_value.override_freq_id,
1578 p_input_value.reference_id)
1579 and p_date_earned between peevf.effective_start_date and peevf.effective_end_date;
1580
1581 cursor c_asg_actions (p_start_date date, p_end_date date)
1582 is
1583 SELECT MAX(ASSIGNMENT_ACTION_ID) assignment_action_id
1584 FROM PAY_PAYROLL_ACTIONS PPA,
1585 PAY_ASSIGNMENT_ACTIONS PAA,
1586 PER_ALL_ASSIGNMENTS_F PAAF,
1587 PER_TIME_PERIODS PTP
1588 WHERE PPA.PAYROLL_ACTION_ID=PAA.PAYROLL_ACTION_ID
1589 AND REGULAR_PAYMENT_DATE = PPA.EFFECTIVE_DATE
1590 AND ptp.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
1591 AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
1592 AND PAAF.PERSON_ID in ( select PERSON_ID from per_all_assignments_f
1593 where assignment_id = p_assignment_id
1594 and p_date_earned between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)
1595 AND PPA.ACTION_TYPE IN ('Q','R')
1596 AND PPA.EFFECTIVE_DATE BETWEEN p_start_date AND p_end_date
1597 AND PAA.ACTION_STATUS ='C'
1598 AND SOURCE_ACTION_ID IS NOT NULL
1599 GROUP BY PPA.TIME_PERIOD_ID;
1600
1601 -------------------
1602 l_input_value c_input_value%rowtype;
1603 l_entry_values c_entry_values%rowtype;
1604 l_override_freq varchar2(20);
1605 l_freq number;
1606 l_period_start date;
1607 l_period_end date;
1608 l_def_balance_id number;
1609 l_net_earnings_ptd number;
1610 x number;
1611 -------------------
1612
1613
1614 begin
1615 /*
1616 Bi-Week - 04
1617 Quarter - 06
1618 Lunar Month - 08
1619 Year - 03
1620 Week - 02
1621 Semi-Year - 07
1622 Calendar Month - 01
1623 Bi-Month -05
1624 */
1625 open c_input_value;
1626 fetch c_input_value into l_input_value;
1627 close c_input_value;
1628
1629 hr_utility.trace(l_input_value.type_id);
1630 hr_utility.trace(l_input_value.ndr_id);
1631 hr_utility.trace(l_input_value.override_freq_id);
1632
1633 open c_entry_values(l_input_value);
1634 fetch c_entry_values into l_entry_values;
1635 close c_entry_values;
1636
1637
1638 l_override_freq := l_entry_values.override_freq;
1639
1640
1641
1642 hr_utility.trace('l_override_freq Value: '||l_override_freq);
1643
1644 IF l_override_freq = '01' THEN
1645 l_override_freq := 'Calendar Month';
1646 ELSIF l_override_freq = '02' THEN
1647 l_override_freq := 'Week';
1648 ELSIF l_override_freq = '03' THEN
1649 l_override_freq := 'Year';
1650 ELSIF l_override_freq = '04' THEN
1651 l_override_freq := 'Bi-Week';
1652 ELSIF l_override_freq = '05' THEN
1653 l_override_freq := 'Bi-Month';
1654 ELSIF l_override_freq = '06' THEN
1655 l_override_freq := 'Quarter';
1656 ELSIF l_override_freq = '07' THEN
1657 l_override_freq := 'Semi-Year';
1658 ELSIF l_override_freq = '08' THEN
1659 l_override_freq := 'Lunar Month';
1660 END IF;
1661
1662 select ptpt.NUMBER_PER_FISCAL_YEAR
1663 into l_freq
1664 from
1665 per_time_period_types ptpt
1666 where ptpt.period_type = l_override_freq;
1667
1668 l_period_start := hr_gbbal.span_start(p_input_date => p_date_earned,
1669 p_frequency => l_freq,
1670 p_start_dd_mm => '06-04');
1671
1672 l_period_end := hr_gbbal.span_end(p_input_date => p_date_earned,
1673 p_frequency => l_freq,
1674 p_start_dd_mm => '06-04');
1675
1676 hr_utility.trace('l_period_start : '|| l_period_start);
1677 hr_utility.trace('l_period_end : '|| l_period_end);
1678 hr_utility.trace('l_override_freq Period Type:'||l_override_freq);
1679
1680 SELECT PDB.DEFINED_BALANCE_ID
1681 INTO l_def_balance_id
1682 FROM PAY_BALANCE_TYPES PBT,
1683 PAY_DEFINED_BALANCES PDB,
1684 PAY_BALANCE_DIMENSIONS PBD
1685 WHERE PDB.BALANCE_TYPE_ID=PBT.BALANCE_TYPE_ID
1686 AND PBT.BALANCE_NAME = 'Court Order'
1687 AND PBD.DATABASE_ITEM_SUFFIX='_ASG_PROC_PTD'
1688 AND PBD.BALANCE_DIMENSION_ID=PDB.BALANCE_DIMENSION_ID;
1689 l_net_earnings_ptd := 0;
1690 for l_asg_actions in c_asg_actions(l_period_start,l_period_end)
1691 loop
1692 x:=0;
1693 hr_utility.trace('Inside LOOP');
1694 x:= PAY_BALANCE_PKG.GET_VALUE ( P_DEFINED_BALANCE_ID =>l_def_balance_id ,
1695 P_ASSIGNMENT_ACTION_ID=>l_asg_actions.assignment_action_id ,P_GET_RR_ROUTE => FALSE ,
1696 P_GET_RB_ROUTE => FALSE );
1697 hr_utility.trace('Balance: '||x);
1698 l_net_earnings_ptd :=l_net_earnings_ptd + x;
1699 end loop;
1700 hr_utility.trace('Net Earnings: ' ||l_net_earnings_ptd );
1701
1702 return l_net_earnings_ptd;
1703
1704 end GET_CO_EARNINGS_PTD;
1705
1706
1707 FUNCTION GET_OVERRIDE_NDR(p_assignment_id IN NUMBER ,
1708 p_date_earned IN DATE,
1709 p_element_entry_id IN NUMBER,
1710 p_element_type_id IN NUMBER)
1711 return number
1712 --return varchar2
1713 is
1714 l_assignment_id number;
1715 --l_payroll_id number;
1716
1717 Cursor c_get_all_assignments is
1718 select assignment_id from per_all_assignments_f
1719 where person_id in (select person_id from per_all_assignments_f
1720 where assignment_id = p_assignment_id
1721 and p_date_earned between effective_start_date and effective_end_date)
1722 and p_date_earned between effective_start_date and effective_end_date;
1723 /*
1724 cursor c_input_value (p_element_name varchar2)
1725 is
1726 select max(decode(trim(pivf.name),'Type',pivf.input_value_id, null)) type_id,
1727 max(decode(trim(pivf.name),'Order Amount',pivf.input_value_id, null)) ndr_id,
1728 max(decode(trim(pivf.name),'DEO Overriding Frequency',pivf.input_value_id, null)) override_freq_id
1729 from pay_element_types_f petf,
1730 pay_input_values_f pivf
1731 where petf.element_name = p_element_name
1732 and petf.LEGISLATION_CODE ='GB'
1733 and p_date_earned between petf.effective_start_date and petf.effective_end_date
1734 and p_date_earned between pivf.effective_start_date and pivf.effective_end_date;
1735 */
1736 cursor c_input_value (p_element_name varchar2)
1737 is
1738 select max(decode(trim(pivf.name),'Type',pivf.input_value_id, null)) type_id,
1739 max(decode(trim(pivf.name),'Order Amount',pivf.input_value_id, null)) ndr_id,
1740 max(decode(trim(pivf.name),'DEO Overriding Frequency',pivf.input_value_id, null)) override_freq_id
1741 from pay_element_types_f petf,
1742 pay_input_values_f pivf
1743 where petf.element_name = p_element_name -- in ('Court Order','Court Order NTPP')
1744 and petf.LEGISLATION_CODE ='GB'
1745 and petf.element_type_id = pivf.element_type_id
1746 and p_date_earned between petf.effective_start_date and petf.effective_end_date
1747 and p_date_earned between pivf.effective_start_date and pivf.effective_end_date;
1748
1749 cursor c_entry_values(p_input_value c_input_value%rowtype,p_element_name varchar2,p_asg_id number)
1750 is
1751 select max(decode(trim(peevf.input_value_id),p_input_value.type_id,screen_entry_value, null)) deo_type,
1752 max(decode(trim(peevf.input_value_id),p_input_value.ndr_id,screen_entry_value, null)) deo_ndr,
1753 max(decode(trim(peevf.input_value_id),p_input_value.override_freq_id,screen_entry_value, null)) override_freq
1754 from pay_element_entry_values_f peevf,
1755 pay_element_entries_f peef,
1756 pay_element_types_f petf
1757 where
1758 peef.element_type_id = petf.element_type_id
1759 and peef.element_entry_id = peevf.element_entry_id
1760 and petf.element_name = p_element_name
1761 and peef.assignment_id = p_asg_id
1762 and peevf.input_value_id in (p_input_value.type_id ,
1763 p_input_value.ndr_id ,
1764 p_input_value.override_freq_id)
1765 and p_date_earned between peef.effective_start_date and peef.effective_end_date
1766 and p_date_earned between petf.effective_start_date and petf.effective_end_date
1767 and p_date_earned between peevf.effective_start_date and peevf.effective_end_date;
1768
1769 -------------------
1770 l_input_value c_input_value%rowtype;
1771 l_entry_values c_entry_values%rowtype;
1772 g_override_freq varchar2(20);
1773 l_override_freq varchar2(20);
1774 l_ndr_check number;
1775 l_element_exists varchar2(1);
1776 -------------------
1777 function c_get_payroll(p_asg_id number)
1778 return varchar2
1779 is
1780 l_payroll varchar2(20);
1781 cursor c_payroll is
1782 select period_type from pay_payrolls_f where
1783 payroll_id in (select payroll_id from per_all_assignments_f where assignment_id = p_asg_id
1784 and p_date_earned between effective_start_date and effective_end_date)
1785 and p_date_earned between effective_start_date and effective_end_date;
1786
1787 begin
1788 open c_payroll;
1789 fetch c_payroll into l_payroll;
1790 close c_payroll;
1791 return l_payroll;
1792 end;
1793
1794
1795
1796 begin
1797 /*
1798 Bi-Week - 04
1799 Quarter - 06
1800 Lunar Month - 08
1801 Year - 03
1802 Week - 02
1803 Semi-Year - 07
1804 Calendar Month - 01
1805 Bi-Month -05
1806 */
1807 open c_input_value('Court Order NTPP');
1808 fetch c_input_value into l_input_value;
1809 close c_input_value;
1810
1811 hr_utility.trace(l_input_value.type_id);
1812 hr_utility.trace(l_input_value.ndr_id);
1813 hr_utility.trace(l_input_value.override_freq_id);
1814
1815 open c_entry_values(l_input_value,'Court Order NTPP',p_assignment_id);
1816 fetch c_entry_values into l_entry_values;
1817 if c_entry_values%notfound then
1818 open c_entry_values(l_input_value,'Court Order',p_assignment_id);
1819 fetch c_entry_values into l_entry_values;
1820 close c_entry_values;
1821 end if;
1822 close c_entry_values;
1823
1824
1825 g_override_freq := l_entry_values.override_freq;
1826
1827
1828
1829 hr_utility.trace('g_override_freq : '||g_override_freq);
1830 l_ndr_check := -1;
1831 l_element_exists := 'Y';
1832 for assg in c_get_all_assignments
1833 loop
1834 hr_utility.trace('Assignment Id: '||assg.assignment_id);
1835 ------ 'Court Order NTPP' Logic
1836 l_override_freq := null;
1837 open c_entry_values(l_input_value,'Court Order NTPP',assg.assignment_id);
1838 fetch c_entry_values into l_entry_values;
1839 close c_entry_values;
1840
1841 if l_entry_values.deo_type is null then
1842 l_element_exists := 'N';
1843 end if;
1844
1845 IF l_element_exists = 'Y' then --Element Exists
1846 hr_utility.trace(l_entry_values.deo_type);
1847 hr_utility.trace(l_entry_values.deo_ndr);
1848 hr_utility.trace(l_entry_values.override_freq);
1849
1850 if (g_override_freq <> l_entry_values.override_freq) THEN
1851 return 1; -- Error that Not all the assignments have the same overriding frequency
1852 end if;
1853
1854 IF g_override_freq = '01' THEN
1855 l_override_freq := 'Calendar Month';
1856 ELSIF g_override_freq = '02' THEN
1857 l_override_freq := 'Week';
1858 ELSIF g_override_freq = '03' THEN
1859 l_override_freq := 'Year';
1860 ELSIF g_override_freq = '04' THEN
1861 l_override_freq := 'Bi-Week';
1862 ELSIF g_override_freq = '05' THEN
1863 l_override_freq := 'Bi-Month';
1864 ELSIF g_override_freq = '06' THEN
1865 l_override_freq := 'Quarter';
1866 ELSIF g_override_freq = '07' THEN
1867 l_override_freq := 'Semi-Year';
1868 ELSIF g_override_freq = '08' THEN
1869 l_override_freq := 'Lunar Month';
1870 END IF;
1871
1872 hr_utility.trace('l_override_freq :'||l_override_freq);
1873 hr_utility.trace('func returned: '||c_get_payroll(assg.assignment_id));
1874 if l_override_freq = c_get_payroll(assg.assignment_id) then
1875 hr_utility.trace('func returned: '||c_get_payroll(assg.assignment_id));
1876 if l_ndr_check = -1 then -- first time
1877 l_ndr_check := to_number(l_entry_values.deo_ndr);
1878 elsif (l_ndr_check <> to_number(l_entry_values.deo_ndr)) then
1879 return 2; -- Error that Value of Overriding NDR is not the same across the assignments
1880 end if;
1881
1882 end if;
1883 END IF;--Element Exists
1884 end loop;
1885
1886 -- c_get_payroll(p_assignment_id);
1887
1888 return l_ndr_check;
1889
1890 end GET_OVERRIDE_NDR;
1891
1892 END PAY_GB_STUDENT_LOANS_PKG;