[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.6 2006/06/20 14:52:54 npershad noship $ */
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 ses.session_id = userenv('sessionid')
76 AND ppa.payroll_action_id = paa.payroll_action_id
77 AND ses.effective_date between ptp.start_date and ptp.end_date
78 AND ppa.time_period_id = ptp.time_period_id
79 AND (paa.source_action_id is not null
80 or ppa.action_type in ('I','V','B'))
81 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B'))
82 AND prr.element_type_id = ele.element_type_id
83 AND prr.source_id = p_ent_id;
84
85 cursor c_ptd_asgact(p_asg in NUMBER,
86 p_ent in NUMBER,
87 p_element_type_id in NUMBER) is
88 select prr.assignment_action_id,
89 prr.source_id
90 from pay_run_results prr,
91 pay_element_types_f ele
92 where prr.assignment_action_id in (
93 SELECT /*+ use_nl(paa,ppa,ptp,ses) */
94 to_number(substr(max(lpad(paa.action_sequence,15,'0')||
95 paa.assignment_action_id),16))
96 FROM pay_assignment_actions paa,
97 pay_payroll_actions ppa,
98 fnd_sessions ses,
99 per_time_periods ptp
100 WHERE paa.assignment_id = p_asg
101 AND paa.action_status = 'C'
102 AND ses.session_id = userenv('sessionid')
103 AND ptp.payroll_id = ppa.payroll_id
104 AND ses.effective_date between ptp.start_date and ptp.end_date
105 AND ppa.effective_date between ptp.start_date and ptp.end_date
106 AND ppa.payroll_action_id = paa.payroll_action_id
107 AND (paa.source_action_id is not null
108 or ppa.action_type in ('I','V','B'))
109 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B'))
110 AND prr.element_type_id = ele.element_type_id
111 and ele.element_type_id = p_element_type_id
112 and prr.source_id = p_ent;
113
114 l_proc VARCHAR(72) := g_package||'.FETCH_BALANCES';
115 l_itd_action_id PAY_ASSIGNMENT_ACTIONS.assignment_action_id%TYPE;
116 l_itd_source_id PAY_RUN_RESULTS.source_id%TYPE;
117 l_ptd_action_id PAY_ASSIGNMENT_ACTIONS.assignment_action_id%TYPE;
118 l_ptd_source_id PAY_RUN_RESULTS.source_id%TYPE;
119 l_balance_type_id PAY_BALANCE_TYPES.balance_type_id%TYPE;
120 l_balance_type_id2 PAY_BALANCE_TYPES.balance_type_id%TYPE;
121 l_effective_date DATE;
122 l_name PAY_BALANCE_TYPES.BALANCE_NAME%TYPE;
123 l_name2 PAY_BALANCE_TYPES.BALANCE_NAME%TYPE;
124
125 BEGIN
126
127 hr_utility.set_location('Entering..'||l_proc,10);
128
129 If p_element_name='Student Loan' then
130 l_name := 'Student Loan';
131 elsif instr(upper(p_element_name),'COURT') >0 then
132 l_name := 'Court Order';
133 elsif instr(upper(p_element_name),'CAO SCOTLAND') >0 then
134 l_name := 'CAO Scotland Payments CMA';
135 l_name2 := 'CAO Scotland Payments EAS';
136 elsif instr(upper(p_element_name),'CMA SCOTLAND') >0 then
137 l_name := 'CMA Scotland';
138 elsif instr(upper(p_element_name),'EAS SCOTLAND') >0 then
139 l_name := 'EAS Scotland';
140 end if;
141
142 open c_balance_id(l_name);
143 fetch c_balance_id into l_balance_type_id;
144 close c_balance_id;
145
146 if l_name2 is not null then
147 open c_balance_id(l_name2);
148 fetch c_balance_id into l_balance_type_id2;
149 close c_balance_id;
150 end if;
151
152 open c_ptd_asgact(p_assignment_id,p_element_entry_id,p_element_type_id);
153 fetch c_ptd_asgact into l_ptd_action_id,l_ptd_source_id;
154
155 if c_ptd_asgact%NOTFOUND then
156
157 p_ptd_balance := NULL;
158 close c_ptd_asgact;
159 else
160 if l_name = 'Court Order' then
161 p_ptd_balance := NVL(hr_gbbal.calc_element_ptd_bal(
162 l_ptd_action_id,
163 l_balance_type_id,
164 l_ptd_source_id),0.00);
165 elsif l_name = 'Student Loan' then
166 p_ptd_balance := NVL(hr_gbbal.calc_asg_tfr_ptd_action(
167 l_ptd_action_id,
168 l_balance_type_id,
169 NULL),0.00);
170 elsif l_name = 'CAO Scotland Payments CMA' then
171 p_ptd_balance := NVL(hr_gbbal.calc_asg_proc_ptd_action(
172 l_ptd_action_id,
173 l_balance_type_id,
174 NULL),0.00) +
175 NVL(hr_gbbal.calc_asg_proc_ptd_action(
176 l_ptd_action_id,
177 l_balance_type_id2,
178 NULL),0.00);
179 else
180 p_ptd_balance := NVL(hr_gbbal.calc_asg_proc_ptd_action(
181 l_ptd_action_id,
182 l_balance_type_id,
183 NULL),0.00);
184 end if;
185 close c_ptd_asgact;
186
187 end if;
188
189 open c_itd_asgact(p_assignment_id,p_element_entry_id,p_element_type_id);
190 fetch c_itd_asgact into l_itd_action_id,l_itd_source_id;
191
192 if c_itd_asgact%NOTFOUND then
193 p_itd_balance := NULL;
194 close c_itd_asgact;
195 else
196 if l_name = 'Court Order' then
197 p_itd_balance := NVL(hr_gbbal.calc_element_itd_bal(
198 l_itd_action_id,
199 l_balance_type_id,
200 l_itd_source_id),0.00);
201 elsif l_name = 'Student Loan' then
202
203 /* Use the same variables, even though they are really for itd,
204 and let the balance retrieval code handle the expiration */
205
206 p_itd_balance := NVL(hr_gbbal.calc_asg_td_ytd_action(
207 l_itd_action_id,
208 l_balance_type_id,
209 NULL),0.00);
210 else
211
212 /* There are no ITD or YTD for Scottish Court Orders so set the
213 itd balance to null */
214
215 p_itd_balance := null;
216 end if;
217 close c_itd_asgact;
218 end if;
219
220 hr_utility.set_location('leaving..'||l_proc,20);
221
222 END Fetch_Balances;
223
224 PROCEDURE Update_Court_Order(
225 p_datetrack_update_mode in varchar2
226 ,p_effective_date in date
227 ,p_business_group_id in number
228 ,p_element_entry_id in number
229 ,p_object_version_number in out nocopy number
230 ,p_subpriority in number
231 ,p_effective_start_date out nocopy date
232 ,p_effective_end_date out nocopy date) is
233
234 l_update_warning BOOLEAN;
235
236 BEGIN
237
238 py_element_entry_api.update_element_entry(
239 p_validate => FALSE,
240 p_datetrack_update_mode => p_datetrack_update_mode,
241 p_effective_date => p_effective_date,
242 p_business_group_id => p_business_group_id,
243 p_element_entry_id => p_element_entry_id,
244 p_object_version_number => p_object_version_number,
245 p_subpriority => p_subpriority,
246 P_EFFECTIVE_START_DATE =>p_effective_start_date,
247 P_EFFECTIVE_END_DATE =>p_effective_end_date,
248 P_UPDATE_WARNING =>l_update_warning);
249 --
250 -- Done the update
251 --
252
253 END Update_Court_Order;
254
255 PROCEDURE Create_Student_Loan(
256 P_EFFECTIVE_DATE in Date,
257 P_BUSINESS_GROUP_ID in Number,
258 P_ASSIGNMENT_ID in Number,
259 P_START_DATE in Varchar2,
260 P_END_DATE in Varchar2,
261 P_SUBPRIORITY in Number,
262 P_EFFECTIVE_START_DATE out nocopy Date,
263 P_EFFECTIVE_END_DATE out nocopy Date,
264 P_ELEMENT_ENTRY_ID out nocopy Number,
265 P_OBJECT_VERSION_NUMBER out nocopy Number) is
266
267 cursor c_effective_date is
268 select effective_date
269 from fnd_sessions
270 where session_id = userenv('sessionid');
271
272 cursor c_element_type is
273 select element_type_id
274 from pay_element_types_f
275 where element_name = 'Student Loan'
276 and legislation_code = 'GB';
277
278 l_element_link_id PAY_ELEMENT_LINKS_F.ELEMENT_LINK_ID%TYPE;
279 l_element_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
280 l_create_warning BOOLEAN;
281 l_effective_date DATE;
282
283 BEGIN
284
285 --
286 -- Obtain the element link using the element type and
287 -- the assignment (payroll)
288 --
289 /* this is not needed, instead use the effective date passed
290 in the parameter
291 open c_effective_date;
292 fetch c_effective_date into l_effective_date;
293 close c_effective_date;
294 */
295 l_effective_date := P_EFFECTIVE_DATE;
296 open c_element_type;
297 fetch c_element_type into l_element_id;
298 close c_element_type;
299
300 l_element_link_id := hr_entry_api.get_link(
301 p_assignment_id,
302 l_element_id,
303 l_effective_date);
304 --
305 -- Create the entry
306 --
307 py_element_entry_api.create_element_entry(
308 P_VALIDATE =>FALSE,
309 P_EFFECTIVE_DATE =>p_effective_date,
310 P_BUSINESS_GROUP_ID =>p_business_group_id,
311 P_ORIGINAL_ENTRY_ID =>NULL,
312 P_ASSIGNMENT_ID =>p_assignment_id,
313 P_ELEMENT_LINK_ID =>l_element_link_id,
314 P_ENTRY_TYPE =>'E',
315 P_COST_ALLOCATION_KEYFLEX_ID=>NULL,
316 P_UPDATING_ACTION_ID =>NULL,
317 P_COMMENT_ID =>NULL,
318 P_REASON =>NULL,
319 P_TARGET_ENTRY_ID =>NULL,
320 P_SUBPRIORITY =>P_SUBPRIORITY,
321 P_DATE_EARNED =>NULL,
322 P_PERSONAL_PAYMENT_METHOD_ID=>NULL,
323 P_ATTRIBUTE_CATEGORY =>NULL,
324 P_ATTRIBUTE1 =>NULL,
325 P_ATTRIBUTE2 =>NULL,
326 P_ATTRIBUTE3 =>NULL,
327 P_ATTRIBUTE4 =>NULL,
328 P_ATTRIBUTE5 =>NULL,
329 P_ATTRIBUTE6 =>NULL,
330 P_ATTRIBUTE7 =>NULL,
331 P_ATTRIBUTE8 =>NULL,
332 P_ATTRIBUTE9 =>NULL,
333 P_ATTRIBUTE10 =>NULL,
334 P_ATTRIBUTE11 =>NULL,
335 P_ATTRIBUTE12 =>NULL,
336 P_ATTRIBUTE13 =>NULL,
337 P_ATTRIBUTE14 =>NULL,
338 P_ATTRIBUTE15 =>NULL,
339 P_ATTRIBUTE16 =>NULL,
340 P_ATTRIBUTE17 =>NULL,
341 P_ATTRIBUTE18 =>NULL,
342 P_ATTRIBUTE19 =>NULL,
343 P_ATTRIBUTE20 =>NULL,
344 P_INPUT_VALUE_ID1 =>Get_Input_Value_Id('Start Date',
345 p_effective_date),
346 P_INPUT_VALUE_ID2 =>Get_Input_Value_Id('End Date',
347 p_effective_date),
348 P_INPUT_VALUE_ID3 =>NULL,
349 P_INPUT_VALUE_ID4 =>NULL,
350 P_INPUT_VALUE_ID5 =>NULL,
351 P_INPUT_VALUE_ID6 =>NULL,
352 P_INPUT_VALUE_ID7 =>NULL,
353 P_INPUT_VALUE_ID8 =>NULL,
354 P_INPUT_VALUE_ID9 =>NULL,
355 P_INPUT_VALUE_ID10 =>NULL,
356 P_INPUT_VALUE_ID11 =>NULL,
357 P_INPUT_VALUE_ID12 =>NULL,
358 P_INPUT_VALUE_ID13 =>NULL,
359 P_INPUT_VALUE_ID14 =>NULL,
360 P_INPUT_VALUE_ID15 =>NULL,
361 P_ENTRY_VALUE1 =>p_start_date,
362 P_ENTRY_VALUE2 =>p_end_date,
363 P_ENTRY_VALUE3 =>NULL,
364 P_ENTRY_VALUE4 =>NULL,
365 P_ENTRY_VALUE5 =>NULL,
366 P_ENTRY_VALUE6 =>NULL,
367 P_ENTRY_VALUE7 =>NULL,
368 P_ENTRY_VALUE8 =>NULL,
369 P_ENTRY_VALUE9 =>NULL,
370 P_ENTRY_VALUE10 =>NULL,
371 P_ENTRY_VALUE11 =>NULL,
372 P_ENTRY_VALUE12 =>NULL,
373 P_ENTRY_VALUE13 =>NULL,
374 P_ENTRY_VALUE14 =>NULL,
375 P_ENTRY_VALUE15 =>NULL,
376 P_EFFECTIVE_START_DATE =>p_effective_start_date,
377 P_EFFECTIVE_END_DATE =>p_effective_end_date,
378 P_ELEMENT_ENTRY_ID =>p_element_entry_id,
379 P_OBJECT_VERSION_NUMBER =>p_object_version_number,
380 P_CREATE_WARNING =>l_create_warning);
381
382 END Create_Student_Loan;
383
384 --
385 -- -------------------- Delete Process ----------------------
386 --
387
388 PROCEDURE Delete_Student_Loan(
389 p_datetrack_mode in VARCHAR2
390 ,p_element_entry_id in NUMBER
391 ,p_effective_date in DATE
392 ,p_object_version_number in NUMBER) IS
393
394 l_object_version_number NUMBER;
395 l_effective_start_date DATE;
396 l_effective_end_date DATE;
397 l_delete_warning BOOLEAN;
398
399 BEGIN
400
401 l_object_version_number := p_object_version_number;
402
403 py_element_entry_api.delete_element_entry(
404 p_validate => FALSE,
405 p_datetrack_delete_mode => p_datetrack_mode,
406 p_effective_date => p_effective_date,
407 p_element_entry_id => p_element_entry_id,
408 p_object_version_number => l_object_version_number,
409 p_effective_start_date => l_effective_start_date,
410 p_effective_end_date => l_effective_end_date,
411 p_delete_warning => l_delete_warning
412 );
413
414 END Delete_Student_Loan;
415
416 PROCEDURE Update_Student_Loan(
417 p_datetrack_update_mode in varchar2
418 ,p_effective_date in date
419 ,p_business_group_id in number
420 ,p_element_entry_id in number
421 ,p_object_version_number in out nocopy number
422 ,p_start_date in VARCHAR2
423 ,p_end_date in VARCHAR2
424 ,p_subpriority in number
425 ,p_effective_start_date out nocopy date
426 ,p_effective_end_date out nocopy date) is
427
428 l_update_warning BOOLEAN;
429
430 BEGIN
431
432 py_element_entry_api.update_element_entry(
433 p_validate => FALSE,
434 p_datetrack_update_mode => p_datetrack_update_mode,
435 p_effective_date => p_effective_date,
436 p_business_group_id => p_business_group_id,
437 p_element_entry_id => p_element_entry_id,
438 p_object_version_number => p_object_version_number,
439 P_INPUT_VALUE_ID1 =>Get_Input_Value_Id('Start Date',
440 p_effective_date),
441 P_INPUT_VALUE_ID2 =>Get_Input_Value_Id('End Date',
442 p_effective_date),
443 P_SUBPRIORITY =>p_subpriority,
444 P_ENTRY_VALUE1 =>p_start_date,
445 P_ENTRY_VALUE2 =>p_end_date,
446 P_EFFECTIVE_START_DATE =>p_effective_start_date,
447 P_EFFECTIVE_END_DATE =>p_effective_end_date,
448 P_UPDATE_WARNING =>l_update_warning);
449 --
450 -- Done the update
451 --
452 END Update_Student_Loan;
453
454
455 /*Added below functions for bug fix 3336452*/
456
460 CURSOR get_asg_tax_ref IS
457 /*Function to get the tax district Reference*/
458 FUNCTION get_tax_ref(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
459
461 SELECT scl.segment1
462 FROM hr_soft_coding_keyflex scl,
463 fnd_sessions fs,
464 pay_payrolls_f ppf,
465 per_all_assignments_f paaf
466 WHERE paaf.assignment_id = p_assignment_id
467 AND fs.session_id = userenv('sessionid')
468 AND fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
469 AND ppf.payroll_id = paaf.payroll_id
470 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
471 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
472
473 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
474 l_proc VARCHAR2(50);
475
476
477 BEGIN
478 l_proc := g_package_functions || 'get_tax_ref';
479 -- Get tax reference of current assignment.
480 hr_utility.set_location('Entering ' || l_proc,10);
481
482 OPEN get_asg_tax_ref;
483 FETCH get_asg_tax_ref INTO l_asg_tax_ref;
484 CLOSE get_asg_tax_ref;
485
486 hr_utility.set_location('Leaving ' || l_proc,30);
487
488 RETURN l_asg_tax_ref;
489
490 END get_tax_ref;
491
492 /*Function to get the Input value Id's*/
493 FUNCTION get_input_value(p_ele_name IN VARCHAR2, p_iv_name IN VARCHAR2) RETURN NUMBER IS
494
495 CURSOR get_input_value_ids IS
496 SELECT piv.input_value_id
497 FROM fnd_sessions fs,
498 pay_element_types_f pet,
499 pay_input_values_f piv
500 WHERE fs.session_id = userenv('sessionid')
501 AND pet.element_name = p_ele_name
502 AND pet.business_group_id IS NULL
503 AND pet.legislation_code = 'GB'
504 AND fs.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
505 AND pet.element_type_id = piv.element_type_id
506 AND piv.name = p_iv_name
507 AND piv.business_group_id IS NULL
508 AND piv.legislation_code = 'GB'
509 AND fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
510
511 l_cto_input_id NUMBER;
512 l_proc VARCHAR2(50);
513
514 BEGIN
515 l_proc := g_package_functions || 'get_input_value';
516 hr_utility.set_location('Entering ' || l_proc,10);
517
518 OPEN get_input_value_ids;
519 FETCH get_input_value_ids INTO l_cto_input_id;
520 CLOSE get_input_value_ids;
521
522 hr_utility.set_location('Leaving ' || l_proc,30);
523 RETURN l_cto_input_id;
524
525 END get_input_value;
526
527 /*Function to get person id*/
528 FUNCTION get_person_id(p_assignment_id IN NUMBER) RETURN NUMBER IS
529
530 CURSOR get_person(p_assignment_id IN NUMBER) IS
531 SELECT person_id
532 FROM per_all_assignments_f paaf,
533 fnd_sessions fs
534 WHERE fs.session_id = userenv('sessionid')
535 AND paaf.assignment_id =p_assignment_id
536 AND fs.effective_date between paaf.effective_start_date and paaf.effective_end_date;
537
538 l_per_id NUMBER;
539 l_proc VARCHAR2(50);
540 BEGIN
541
542 l_proc := g_package_functions || 'get_person_id';
543 hr_utility.set_location('Entering ' || l_proc,10);
544 OPEN get_person(p_assignment_id);
545 FETCH get_person INTO l_per_id;
546 CLOSE get_person;
547
548 hr_utility.set_location('Leaving ' || l_proc,30);
549 RETURN l_per_id;
550 END get_person_id;
551
552 /*Function to get the current frequency of the assignment*/
553 FUNCTION get_current_freq(p_assignment_id IN NUMBER,
554 p_date_earned IN DATE,
555 p_reference IN VARCHAR2
556 ) RETURN NUMBER IS
557
558 CURSOR get_freq IS
559 SELECT ptpt.number_per_fiscal_year
560 FROM per_all_assignments_f papf,
561 pay_all_payrolls_f pap,
562 per_time_period_types ptpt
563 WHERE papf.assignment_id = p_assignment_id
564 AND p_date_earned BETWEEN papf.effective_start_date and papf.effective_end_date
565 AND pap.payroll_id = papf.payroll_id
566 AND p_date_earned BETWEEN pap.effective_start_date and pap.effective_end_date
567 AND pap.period_type=ptpt.period_type;
568
569 l_freq per_time_period_types.number_per_fiscal_year%TYPE;
570 l_proc VARCHAR2(50) ;
571
572 BEGIN
573 l_proc := g_package_functions || 'get_current_freq';
574 hr_utility.set_location('Entering ' || l_proc,10);
575
576 OPEN get_freq;
577 FETCH get_freq INTO l_freq;
578 CLOSE get_freq;
579
580 hr_utility.set_location('Leaving ' || l_proc,30);
581 RETURN l_freq;
582 END get_current_freq;
583
584 /*Function to get the current pay date of the assignment*/
585 FUNCTION get_current_pay_date(p_assignment_id IN NUMBER
586 ,p_date_earned IN DATE
587 ,p_reference IN VARCHAR2
588 ) RETURN DATE IS
589
590 CURSOR get_curr_pay_date IS
591 SELECT nvl(ptp.regular_payment_date , to_date('01-01-0001','DD-MM-YYYY'))
592 FROM per_all_assignments_f papf,
593 pay_all_payrolls_f pap,
594 per_time_periods ptp
595 WHERE papf.assignment_id = p_assignment_id
596 AND p_date_earned BETWEEN papf.effective_start_date and papf.effective_end_date
597 AND pap.payroll_id = papf.payroll_id
598 AND p_date_earned BETWEEN pap.effective_start_date and pap.effective_end_date
599 AND pap.payroll_id=ptp.payroll_id
600 AND p_date_earned BETWEEN ptp.start_date and ptp.end_date;
601
602 l_current_pay_date DATE;
606 l_proc := g_package_functions || 'get_current_pay_date';
603 l_proc VARCHAR2(50);
604
605 BEGIN
607 hr_utility.set_location('Entering ' || l_proc,10);
608 OPEN get_curr_pay_date;
609 FETCH get_curr_pay_date INTO l_current_pay_date;
610 CLOSE get_curr_pay_date;
611
612 hr_utility.set_location('Leaving ' || l_proc,30);
613 RETURN l_current_pay_date;
614
615 END get_current_pay_date;
616
617 /*Function to count the number of Main CTO Entries*/
618 FUNCTION count_main_cto_entry(p_assignment_id IN NUMBER,
619 p_date_earned IN DATE,
620 p_reference IN VARCHAR2) RETURN NUMBER IS
621
622 l_count NUMBER := 0;
623 l_count_n NUMBER :=0;
624 l_count_null NUMBER :=0;
625 g_cto_main_iv_id NUMBER;
626 g_cto_ntpp_main_iv_id NUMBER;
627 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
628 g_cto_main_ref_id NUMBER;
629 g_cto_ntpp_main_ref_id NUMBER;
630 l_person_id NUMBER;
631 l_count_total NUMBER;
632
633 CURSOR get_main_count(p_asg_tax_ref IN VARCHAR2,p_person_id IN NUMBER,p_entry_value IN VARCHAR2) IS
634 SELECT /*+ ORDERED use_nl(papf, paaf1, paaf2, ppf, piv1, piv2, peef1, peef2, scl)*/
635 count(*) cnt
636 from per_all_people_f papf,
637 per_all_assignments_f paaf1,
638 per_all_assignments_f paaf2,
639 pay_all_payrolls_f ppf,
640 pay_input_values_f piv1 ,
641 pay_input_values_f piv2,
642 pay_element_entries_f peef1,
643 pay_element_entries_f peef2,
644 pay_element_entry_values_f peev1,
645 pay_element_entry_values_f peev2,
646 hr_soft_coding_keyflex scl
647 where papf.person_id = p_person_id
648 and papf.person_id = paaf1.person_id
649 and papf.person_id = paaf2.person_id
650 -- and paaf1.person_id = paaf2.person_id -- redundant
651 and ppf.payroll_id = paaf2.payroll_id
652 and ppf.payroll_id = paaf1.payroll_id
653 AND scl.segment1 = p_asg_tax_ref
654 and piv1.input_value_id in (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
655 and piv2.input_value_id in (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
656 and piv1.input_value_id = peev1.input_value_id
657 and piv2.input_value_id = peev2.input_value_id
658 AND paaf1.assignment_id = peef1.assignment_id
659 AND paaf2.assignment_id = peef2.assignment_id
660 and peef1.element_entry_id = peef2.element_entry_id
661 and peev1.element_entry_id = peev2.element_entry_id
662 and piv1.element_type_id = piv2.element_type_id
663 AND peef1.element_entry_id = peev1.element_entry_id
664 AND peef2.element_entry_id = peev2.element_entry_id -- AND fs.session_id = userenv('sessionid')
665 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
666 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
667 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
668 AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
669 AND p_date_earned BETWEEN peef1.effective_start_date AND peef1.effective_end_date
670 AND p_date_earned BETWEEN peef2.effective_start_date AND peef2.effective_end_date
671 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
672 AND p_date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
673 AND p_date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
674 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
675 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
676 AND scl.segment1 = p_asg_tax_ref
677 and peev1.screen_entry_value = p_reference
678 group by peev1.screen_entry_value,peev2.screen_entry_Value
679
680 having ( peev2.screen_entry_Value =p_entry_value) ;
681
682
683 CURSOR get_main_entry_count(p_asg_tax_ref IN VARCHAR2,p_person_id IN NUMBER) IS
684 SELECT /*+ ORDERED use_nl(papf, paaf1, paaf2, ppf, piv1, piv2, peef1, peef2, scl)*/
685 count(*) cnt
686 from per_all_people_f papf,
687 per_all_assignments_f paaf1,
688 per_all_assignments_f paaf2,
689 pay_all_payrolls_f ppf,
690 pay_input_values_f piv1 ,
691 pay_input_values_f piv2,
692 pay_element_entries_f peef1,
693 pay_element_entries_f peef2,
694 pay_element_entry_values_f peev1,
695 pay_element_entry_values_f peev2,
696 hr_soft_coding_keyflex scl
697 where papf.person_id = p_person_id
698 and papf.person_id = paaf1.person_id
699 and papf.person_id = paaf2.person_id
700 -- and paaf1.person_id = paaf2.person_id -- redundant
701 and ppf.payroll_id = paaf2.payroll_id
702 and ppf.payroll_id = paaf1.payroll_id
703 AND scl.segment1 = p_asg_tax_ref
704 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
705 and piv1.input_value_id in (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
706 and piv2.input_value_id in (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
707 and piv1.input_value_id = peev1.input_value_id
708 and piv2.input_value_id = peev2.input_value_id
709 AND paaf1.assignment_id = peef1.assignment_id
710 AND paaf2.assignment_id = peef2.assignment_id
711 and peef1.element_entry_id = peef2.element_entry_id
712 and peev1.element_entry_id = peev2.element_entry_id
713 and piv1.element_type_id = piv2.element_type_id
714 AND peef1.element_entry_id = peev1.element_entry_id
715 AND peef2.element_entry_id = peev2.element_entry_id -- AND fs.session_id = userenv('sessionid')
716 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
717 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
721 AND p_date_earned BETWEEN peef2.effective_start_date AND peef2.effective_end_date
718 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
719 AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
720 AND p_date_earned BETWEEN peef1.effective_start_date AND peef1.effective_end_date
722 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
723 AND p_date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
724 AND p_date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
725 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
726 and peev1.screen_entry_value = p_reference
727 group by peev1.screen_entry_value;
728
729
730 --
731 CURSOR chk_prim_asg(p_asg_tax_ref IN VARCHAR2) IS
732 SELECT 1 cnt
733 FROM per_all_assignments_f paaf1,
734 per_all_assignments_f paaf2,
735 pay_all_payrolls_f ppf,
736 hr_soft_coding_keyflex scl,
737 pay_element_entries_f peef,
738 pay_element_entry_values_f peev
739 WHERE paaf1.assignment_id = p_assignment_id
740 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
741 AND paaf1.person_id = paaf2.person_id
742 AND nvl(paaf2.primary_flag, 'N') = 'Y'
743 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
744 AND paaf2.payroll_id = ppf.payroll_id
745 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
746 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
747 AND scl.segment1 = p_asg_tax_ref
748 AND paaf2.assignment_id = peef.assignment_id
749 AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
750 AND peef.element_entry_id = peev.element_entry_id
751 AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
752 AND peev.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
753 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = p_reference;
754
755 --
756 l_proc VARCHAR2(50);
757 BEGIN
758
759 l_proc := g_package_functions || 'count_main_cto_entry';
760 hr_utility.set_location('Entering ' || l_proc,10);
761
762 /*Get tax reference of current assignment*/
763 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
764
765 /*Get Input value id*/
766 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
767 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
768
769 g_cto_main_ref_id := get_input_value('Court Order','Reference');
770 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
771
772 /*Get Person Id*/
773 l_person_id := get_person_id(p_assignment_id);
774
775
776 OPEN get_main_count(l_asg_tax_ref,l_person_id,'Y');
777 FETCH get_main_count INTO l_count;
778 CLOSE get_main_count;
779 --
780
781 IF l_count = 0 THEN
782 OPEN get_main_count(l_asg_tax_ref,l_person_id,'N');
783 FETCH get_main_count INTO l_count;
784 OPEN get_main_entry_count(l_asg_tax_ref,l_person_id);
785 FETCH get_main_entry_count INTO l_count_total;
786 CLOSE get_main_entry_count;
787 CLOSE get_main_count;
788 if l_count=l_count_total then
789 l_count := 0;
790 else
791 OPEN chk_prim_asg(l_asg_tax_ref);
792 FETCH chk_prim_asg INTO l_count;
793 IF chk_prim_asg%NOTFOUND THEN
794 l_count := 0;
795 END IF;
796 CLOSE chk_prim_asg;
797
798 end if;
799 --
800 END IF;
801 --
802 g_count_main_cto_entry := l_count;
803 --
804 hr_utility.set_location('Leaving ' || l_proc,30);
805 RETURN l_count;
806
807 END count_main_cto_entry;
808
809 FUNCTION get_main_cto_pay_date(p_assignment_id IN NUMBER,
810 p_date_earned IN DATE,
811 p_reference IN VARCHAR2
812 ) RETURN DATE IS
813
814
815 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
816 l_asg_payroll_id pay_payrolls_f.payroll_id%TYPE;
817 l_asg_period_start_date per_time_periods.start_date%TYPE;
818 g_cto_main_iv_id NUMBER;
819 g_cto_ntpp_main_iv_id NUMBER;
820 g_cto_main_ref_id NUMBER;
821 g_cto_ntpp_main_ref_id NUMBER;
822 l_person_id NUMBER;
823
824
825 CURSOR get_asg_period_start_date IS
826 SELECT ptp.start_date
827 FROM per_time_periods ptp
828 WHERE ptp.payroll_id = l_asg_payroll_id
829 AND p_date_earned =ptp.regular_payment_date;
830
831 CURSOR get_main_payroll_id(p_asg_tax_ref VARCHAR2,p_person_id NUMBER) is
832 SELECT ppf.payroll_id
833 FROM per_all_assignments_f paaf1,
834 per_all_assignments_f paaf2,
835 pay_all_payrolls_f ppf,
836 hr_soft_coding_keyflex scl,
837 pay_element_entries_f peef,
838 pay_element_entry_values_f peev,
839 pay_element_entry_values_f peev1,
840 per_all_people_f papf
841 WHERE
842 p_date_earned BETWEEN paaf1.effective_start_date and paaf1.effective_end_date
843 AND paaf1.person_id = paaf2.person_id
844 AND p_date_earned BETWEEN paaf2.effective_start_date and paaf2.effective_end_date
845 AND paaf2.payroll_id = ppf.payroll_id
846 AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
847 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
848 AND scl.segment1 = p_asg_tax_ref
849 AND paaf2.assignment_id = peef.assignment_id
850 AND p_date_earned BETWEEN peef.effective_start_date and peef.effective_end_date
851 AND peef.element_entry_id = peev.element_entry_id
855 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = 'Y'
852 AND p_date_earned BETWEEN peev.effective_start_date and peev.effective_end_date
853 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
854 AND peev1.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
856 AND nvl(peev1.SCREEN_ENTRY_VALUE, 'N') = p_reference
857 AND peev.element_entry_id=peev1.element_entry_id
858 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
859 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
860 AND papf.person_id = p_person_id
861 AND papf.person_id = paaf1.person_id
862 AND papf.person_id = paaf2.person_id;
863
864
865 CURSOR get_prim_payroll_id(p_asg_tax_ref VARCHAR2) IS
866 SELECT ppf.payroll_id
867 FROM per_all_assignments_f paaf1,
868 per_all_assignments_f paaf2,
869 pay_all_payrolls_f ppf,
870 hr_soft_coding_keyflex scl,
871 pay_element_entries_f peef,
872 pay_element_entry_values_f peev
873 WHERE paaf1.assignment_id = p_assignment_id
874 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
875 AND paaf1.person_id = paaf2.person_id
876 AND nvl(paaf2.primary_flag, 'N') = 'Y'
877 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
878 AND paaf2.payroll_id = ppf.payroll_id
879 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
880 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
881 AND scl.segment1 = p_asg_tax_ref
882 AND paaf2.assignment_id = peef.assignment_id
883 AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
884 AND peef.element_entry_id = peev.element_entry_id
885 AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
886 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id);
887
888 l_payroll_id NUMBER;
889 l_pay_date DATE;
890 l_count NUMBER := 0;
891
892
893 CURSOR get_pay_date(p_payroll_id IN NUMBER) IS
894 SELECT nvl(ptp.regular_payment_date , to_date('01-01-0001','DD-MM-YYYY'))
895 FROM per_time_periods ptp
896 WHERE p_date_earned BETWEEN ptp.start_date and ptp.end_date
897 AND ptp.payroll_id =p_payroll_id;
898
899 l_proc VARCHAR2(50);
900
901 BEGIN
902 l_proc := g_package_functions || 'get_main_cto_pay_date';
903 hr_utility.set_location('Entering ' || l_proc,10);
904 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
905
906 l_person_id := get_person_id(p_assignment_id);
907
908 /*Get Input value id*/
909 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
910 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
911
912 g_cto_main_ref_id := get_input_value('Court Order','Reference');
913 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
914
915 OPEN get_asg_period_start_date;
916 FETCH get_asg_period_start_date into l_asg_period_start_date;
917 CLOSE get_asg_period_start_date;
918
919 --
920 IF nvl(p_assignment_id, -1) <> nvl(g_asg_id, -999) THEN
921 l_count := count_main_cto_entry(p_assignment_id,p_date_earned,p_reference);
922 ELSE
923 l_count := g_count_main_cto_entry;
924 END IF;
925
926 --
927 IF nvl(l_count, 0) = 1 THEN
928 --
929 OPEN get_main_payroll_id(l_asg_tax_ref,l_person_id);
930 FETCH get_main_payroll_id INTO l_payroll_id;
931
932 IF get_main_payroll_id%NOTFOUND THEN
933 l_payroll_id := NULL;
934 END IF;
935 CLOSE get_main_payroll_id;
936 --
937 IF l_payroll_id IS NULL THEN
938 OPEN get_prim_payroll_id(l_asg_tax_ref);
939 FETCH get_prim_payroll_id INTO l_payroll_id;
940 IF get_prim_payroll_id%NOTFOUND THEN
941 l_payroll_id := NULL;
942 END IF;
943 CLOSE get_prim_payroll_id;
944 END IF;
945 --
946 IF l_payroll_id IS NULL THEN
947 RETURN to_date('01-01-0001', 'DD-MM-YYYY');
948 ELSE
949 OPEN get_pay_date(l_payroll_id);
950 FETCH get_pay_date INTO l_pay_date;
951
952 IF get_pay_date%NOTFOUND THEN
953 l_pay_date := to_date('01-01-0001', 'DD-MM-YYYY');
954 END IF;
955 CLOSE get_pay_date;
956 END IF;
957 ELSE
958
959 l_pay_date := to_date('01-01-0001', 'DD-MM-YYYY');
960 END IF;
961 --
962 hr_utility.set_location('Leaving ' || l_proc,30);
963 RETURN l_pay_date;
964
965 END get_main_cto_pay_date;
966
967
968
969 FUNCTION get_main_cto_freq(p_assignment_id IN NUMBER,
970 p_date_earned IN DATE,
971 p_reference IN VARCHAR2
972 ) RETURN NUMBER IS
973
974 --
975 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
976 l_person_id NUMBER;
977 g_cto_main_iv_id NUMBER;
978 g_cto_ntpp_main_iv_id NUMBER;
979 g_cto_main_ref_id NUMBER;
980 g_cto_ntpp_main_ref_id NUMBER;
981
982 --
983 CURSOR get_main_payroll_id(p_asg_tax_ref VARCHAR2, p_person_id NUMBER) IS
984 SELECT ppf.payroll_id
985 FROM per_all_assignments_f paaf1,
986 per_all_assignments_f paaf2,
987 pay_all_payrolls_f ppf,
988 hr_soft_coding_keyflex scl,
989 pay_element_entries_f peef,
990 pay_element_entry_values_f peev,
991 pay_element_entry_values_f peev1,
992 per_all_people_f papf
993 WHERE p_date_earned BETWEEN paaf1.effective_start_date and paaf1.effective_end_date
997 AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
994 AND paaf1.person_id = paaf2.person_id
995 AND p_date_earned BETWEEN paaf2.effective_start_date and paaf2.effective_end_date
996 AND paaf2.payroll_id = ppf.payroll_id
998 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
999 AND scl.segment1 = p_asg_tax_ref
1000 AND paaf2.assignment_id = peef.assignment_id
1001 AND p_date_earned BETWEEN peef.effective_start_date and peef.effective_end_date
1002 AND peef.element_entry_id = peev.element_entry_id
1003 AND p_date_earned BETWEEN peev.effective_start_date and peev.effective_end_date
1004 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
1005 AND peev1.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
1006 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = 'Y'
1007 AND nvl(peev1.SCREEN_ENTRY_VALUE, 'N') = p_reference
1008 AND peev.element_entry_id=peev1.element_entry_id
1009 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
1010 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
1011 AND papf.person_id = p_person_id
1012 AND papf.person_id = paaf1.person_id
1013 AND papf.person_id = paaf2.person_id;
1014 --
1015 CURSOR get_prim_payroll_id(p_asg_tax_ref VARCHAR2) IS
1016 SELECT ppf.payroll_id
1017 FROM fnd_sessions fs,
1018 per_all_assignments_f paaf1,
1019 per_all_assignments_f paaf2,
1020 pay_all_payrolls_f ppf,
1021 hr_soft_coding_keyflex scl,
1022 pay_element_entries_f peef,
1023 pay_element_entry_values_f peev
1024 WHERE paaf1.assignment_id = p_assignment_id
1025 AND fs.session_id = userenv('sessionid')
1026 AND fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1027 AND paaf1.person_id = paaf2.person_id
1028 AND nvl(paaf2.primary_flag, 'N') = 'Y'
1029 AND fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1030 AND paaf2.payroll_id = ppf.payroll_id
1031 AND fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1032 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1033 AND scl.segment1 = p_asg_tax_ref
1034 AND paaf2.assignment_id = peef.assignment_id
1035 AND fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
1036 AND peef.element_entry_id = peev.element_entry_id
1037 AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1038 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id);
1039 --
1040 l_payroll_id NUMBER;
1041 l_freq NUMBER;
1042 l_count NUMBER := 0;
1043
1044 --
1045 CURSOR get_freq IS
1046 SELECT number_per_fiscal_year
1047 FROM per_time_periods ptp,
1048 per_time_period_types ptpt
1049 WHERE p_date_earned BETWEEN ptp.start_date AND ptp.end_Date
1050 AND ptp.payroll_id = l_payroll_id
1051 AND ptp.period_type = ptpt.period_type;
1052 --
1053
1054 l_proc VARCHAR2(50);
1055 BEGIN
1056 --
1057 l_proc := g_package_functions || 'get_main_cto_freq';
1058 hr_utility.set_location('Entering ' || l_proc,10);
1059
1060 -- Get tax ref of current asg.
1061 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
1062 l_person_id := get_person_id(p_assignment_id);
1063
1064 /*Get Input value id*/
1065 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
1066 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
1067
1068 g_cto_main_ref_id := get_input_value('Court Order','Reference');
1069 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
1070 --
1071 IF nvl(p_assignment_id, -1) <> nvl(g_asg_id, -999) THEN
1072
1073 l_count := count_main_cto_entry(p_assignment_id,p_date_earned,p_reference);
1074 ELSE
1075 l_count := g_count_main_cto_entry;
1076 END IF;
1077 --
1078 IF nvl(l_count, 0) = 1 THEN
1079
1080 OPEN get_main_payroll_id(l_asg_tax_ref,l_person_id);
1081 FETCH get_main_payroll_id INTO l_payroll_id;
1082 IF get_main_payroll_id%NOTFOUND THEN
1083
1084 l_payroll_id := NULL;
1085 END IF;
1086 CLOSE get_main_payroll_id;
1087 --
1088 IF l_payroll_id IS NULL THEN
1089
1090 OPEN get_prim_payroll_id(l_asg_tax_ref);
1091 FETCH get_prim_payroll_id INTO l_payroll_id;
1092 IF get_prim_payroll_id%NOTFOUND THEN
1093 l_payroll_id := NULL;
1094 END IF;
1095 CLOSE get_prim_payroll_id;
1096 END IF;
1097 --
1098 IF l_payroll_id IS NULL THEN
1099 RETURN null;
1100 ELSE
1101 OPEN get_freq;
1102 FETCH get_freq INTO l_freq;
1103 IF get_freq%NOTFOUND THEN
1104 l_freq := 0;
1105 END IF;
1106 CLOSE get_freq;
1107 END IF;
1108 --
1109 ELSE
1110
1111 l_freq := 0;
1112 END IF;
1113 --
1114 hr_utility.set_location('Leaving ' || l_proc,30);
1115 RETURN l_freq;
1116
1117 END get_main_cto_freq;
1118
1119
1120 FUNCTION get_main_entry_values(p_assignment_id IN NUMBER,
1121 p_date_earned IN DATE,
1122 p_reference IN VARCHAR2,
1123 p_input_value_name IN VARCHAR2,
1124 p_count OUT NOCOPY NUMBER
1125 ) RETURN VARCHAR2 IS
1126
1127 --
1128 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
1129 --
1130
1131 l_cto_iv_id NUMBER;
1132 l_cto_ntpp_iv_id NUMBER;
1133
1137 g_cto_ntpp_main_ref_id NUMBER;
1134 g_cto_main_iv_id NUMBER;
1135 g_cto_ntpp_main_iv_id NUMBER;
1136 g_cto_main_ref_id NUMBER;
1138 l_person_id NUMBER;
1139
1140 CURSOR get_main_entry_id(p_asg_tax_ref VARCHAR2,p_person_id NUMBER) IS
1141 SELECT peef.element_entry_id
1142 FROM per_all_assignments_f paaf1,
1143 per_all_assignments_f paaf2,
1144 pay_all_payrolls_f ppf,
1145 hr_soft_coding_keyflex scl,
1146 pay_element_entries_f peef,
1147 pay_element_entry_values_f peev,
1148 pay_element_entry_values_f peev1,
1149 per_all_people_f papf
1150 WHERE --paaf1.assignment_id = p_assignment_id AND
1151 p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1152 AND paaf1.person_id = paaf2.person_id
1153 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1154 AND paaf2.payroll_id = ppf.payroll_id
1155 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1156 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1157 AND scl.segment1 = p_asg_tax_ref
1158 AND paaf2.assignment_id = peef.assignment_id
1159 AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
1160 AND peef.element_entry_id = peev.element_entry_id
1161 AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
1162 AND peev.input_value_id IN (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
1163 AND peev1.input_value_id IN (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
1164 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = 'Y'
1165 AND nvl(peev1.SCREEN_ENTRY_VALUE, 'N') = p_reference
1166 AND peev1.element_entry_id=peev.element_entry_id
1167 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
1168 AND peef.target_entry_id IS NULL
1169 AND papf.person_id =p_person_id
1170 AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
1171 AND papf.person_id = paaf1.person_id
1172 AND papf.person_id = paaf2.person_id;
1173
1174 --
1175 CURSOR chk_prim_entry_id(p_asg_tax_ref VARCHAR2) IS
1176 SELECT peef.element_entry_id
1177 FROM
1178 per_all_assignments_f paaf1,
1179 per_all_assignments_f paaf2,
1180 pay_all_payrolls_f ppf,
1181 hr_soft_coding_keyflex scl,
1182 pay_element_entries_f peef,
1183 pay_element_entry_values_f peev
1184 WHERE paaf1.assignment_id = p_assignment_id
1185 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1186 AND paaf1.person_id = paaf2.person_id
1187 AND nvl(paaf2.primary_flag, 'N') = 'Y'
1188 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1189 AND paaf2.payroll_id = ppf.payroll_id
1190 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1194 AND p_date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
1191 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1192 AND scl.segment1 = p_asg_tax_ref
1193 AND paaf2.assignment_id = peef.assignment_id
1195 AND peef.element_entry_id = peev.element_entry_id
1196 AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
1197 AND peev.input_value_id IN (g_cto_main_ref_id, g_cto_ntpp_main_ref_id)
1198 AND peef.target_entry_id IS NULL
1199 AND nvl(peev.SCREEN_ENTRY_VALUE, 'N') = p_reference;
1200 --
1201 l_entry_id NUMBER;
1202
1203
1204 CURSOR get_value IS
1205 SELECT peev.screen_entry_value
1206 FROM pay_element_entry_values_f peev
1207 WHERE p_date_earned BETWEEN peev.effective_start_date and peev.effective_end_date
1208 AND peev.element_entry_id = l_entry_id
1209 AND peev.input_value_id IN (l_cto_iv_id, l_cto_ntpp_iv_id);
1210
1211 l_value pay_element_entry_values_f.screen_entry_value%TYPE;
1212 l_count NUMBER;
1213 l_proc VARCHAR2(50);
1214
1215 BEGIN
1216
1217 l_proc := g_package_functions || 'get_main_entry_values';
1218 hr_utility.set_location('Entering ' || l_proc,10);
1219
1220 -- Get tax ref of current asg.
1221 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
1222 l_person_id := get_person_id(p_assignment_id);
1223 --
1224 IF nvl(p_assignment_id, -1) <> nvl(g_asg_id, -999) THEN
1225
1226 l_count := count_main_cto_entry(p_assignment_id,p_date_earned,p_reference);
1227 ELSE
1228 l_count := g_count_main_cto_entry;
1229 END IF;
1230
1231 p_count := l_count;
1232 --
1233 IF nvl(l_count, 0) = 1 THEN
1234
1235
1236 /*Get Input value id*/
1237 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
1238 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
1239 g_cto_main_ref_id := get_input_value('Court Order','Reference');
1240 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
1241
1242 l_cto_iv_id := get_input_value('Court Order',p_input_value_name);
1243 l_cto_ntpp_iv_id := get_input_value('Court Order NTPP',p_input_value_name);
1244
1245
1246 OPEN get_main_entry_id(l_asg_tax_ref,l_person_id);
1247 FETCH get_main_entry_id INTO l_entry_id;
1248
1249 IF get_main_entry_id%NOTFOUND THEN
1250
1251 OPEN chk_prim_entry_id(l_asg_tax_ref);
1252 FETCH chk_prim_entry_id INTO l_entry_id;
1253 CLOSE chk_prim_entry_id;
1254
1255 END IF;
1256 CLOSE get_main_entry_id;
1257 --
1258 OPEN get_value;
1259 FETCH get_value INTO l_value;
1260 CLOSE get_value;
1261 --
1262 ELSE
1263 l_value := NULL;
1264 END IF;
1265 --
1266 hr_utility.set_location('Leaving ' || l_proc,30);
1267 RETURN l_value;
1268
1269 END get_main_entry_values;
1270
1271 FUNCTION get_main_initial_debt(p_assignment_id IN NUMBER,
1272 p_date_earned IN DATE,
1273 p_reference IN VARCHAR2
1274 ) RETURN NUMBER IS
1275
1276 l_value NUMBER;
1277 l_count NUMBER;
1278 l_proc VARCHAR2(50);
1279
1280 BEGIN
1281
1282 l_proc := g_package_functions || 'get_main_initial_debt';
1283 hr_utility.set_location('Entering ' || l_proc,10);
1284 l_value := nvl(to_number(get_main_entry_values(p_assignment_id,p_date_earned,p_reference,'Initial Debt',l_count)),0);
1285 hr_utility.set_location('Leaving ' || l_proc,30);
1286
1287 RETURN l_value;
1288
1289
1290 END get_main_initial_debt;
1291
1292 FUNCTION get_main_fee(p_assignment_id IN NUMBER,
1293 p_date_earned IN DATE,
1294 p_reference IN VARCHAR2
1295 ) RETURN NUMBER IS
1296
1297 l_value NUMBER;
1298 l_count NUMBER;
1299 l_proc VARCHAR2(50);
1300
1301 BEGIN
1302 l_proc := g_package_functions || 'get_main_fee';
1303 hr_utility.set_location('Entering ' || l_proc,10);
1304 l_value := nvl(to_number(get_main_entry_values(p_assignment_id,p_date_earned,p_reference,'Fee',l_count)),0);
1305 hr_utility.set_location('Leaving ' || l_proc,30);
1306
1307
1308 RETURN l_value;
1309
1310
1311 END get_main_fee;
1312
1313 FUNCTION check_ref(p_assignment_id IN NUMBER,
1314 p_date_earned IN DATE,
1315 p_reference IN VARCHAR2
1316 ) RETURN VARCHAR2 IS
1317
1318 l_main_ref pay_element_entry_values_f.screen_entry_value%TYPE;
1319 l_count NUMBER;
1320 l_proc VARCHAR2(50);
1321
1322 BEGIN
1323 l_proc := g_package_functions || 'check_ref';
1324 hr_utility.set_location('Entering ' || l_proc,10);
1325 l_main_ref := nvl(get_main_entry_values(p_assignment_id,p_date_earned,p_reference,'Reference',l_count),'Unknown');
1326
1327
1328 IF nvl(l_count, 0) = 1 and l_main_ref = p_reference then
1329 -- Valid reference
1330 RETURN 'Y';
1331 ELSE
1332 -- Invalid reference
1333 IF l_main_ref = 'Unknown' THEN
1334 RETURN 'K';
1335 ELSE
1336 RETURN 'N';
1337 END IF;
1338 END IF;
1339
1340 hr_utility.set_location('Leaving ' || l_proc,30);
1341
1342 END check_ref;
1343
1344
1345 FUNCTION get_main_entry_value(p_assignment_id IN NUMBER,
1346 p_date_earned IN DATE,
1347 p_reference IN VARCHAR2
1348 ) RETURN VARCHAR2 is
1349
1353 l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
1350
1351 g_cto_main_iv_id NUMBER;
1352 g_cto_ntpp_main_iv_id NUMBER;
1354 g_cto_main_ref_id NUMBER;
1355 g_cto_ntpp_main_ref_id NUMBER;
1356 l_main_entry_value VARCHAR2(10);
1357
1358 CURSOR get_main_value(p_asg_tax_ref IN VARCHAR2) IS
1359 SELECT /*+ ORDERED use_nl(paaf1, paaf2, ppf, piv1, piv2, peef1, peef2, scl)*/
1360 peev2.screen_entry_value main_entry_value
1361 from per_all_assignments_f paaf1,
1362 per_all_assignments_f paaf2,
1363 pay_all_payrolls_f ppf,
1364 pay_input_values_f piv1 ,
1365 pay_input_values_f piv2,
1366 pay_element_entries_f peef1,
1367 pay_element_entries_f peef2,
1368 pay_element_entry_values_f peev1,
1369 pay_element_entry_values_f peev2 ,
1370 hr_soft_coding_keyflex scl
1371 where paaf1.assignment_id = p_assignment_id
1372 AND paaf2.assignment_id = paaf1.assignment_id
1373 and paaf2.payroll_id = ppf.payroll_id
1374 and paaf1.payroll_id = ppf.payroll_id
1375 AND peef1.assignment_id = paaf1.assignment_id
1376 AND peef2.assignment_id = paaf2.assignment_id
1377 and piv1.input_value_id in (g_cto_main_ref_id,g_cto_ntpp_main_ref_id)
1378 and piv2.input_value_id in (g_cto_main_iv_id, g_cto_ntpp_main_iv_id)
1379 and piv1.input_value_id = peev1.input_value_id
1380 and piv2.input_value_id = peev2.input_value_id
1381 and peef1.element_entry_id = peef2.element_entry_id
1382 and peev1.element_entry_id = peev2.element_entry_id
1383 and piv1.element_type_id = piv2.element_type_id
1384 AND peef1.element_entry_id = peev1.element_entry_id
1385 AND peef2.element_entry_id = peev2.element_entry_id
1386 AND p_date_earned BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1387 AND p_date_earned BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1388 AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
1389 AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
1390 AND p_date_earned BETWEEN peef1.effective_start_date AND peef1.effective_end_date
1391 AND p_date_earned BETWEEN peef2.effective_start_date AND peef2.effective_end_date
1392 AND p_date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
1393 AND p_date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
1394 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1395 AND scl.segment1 = p_asg_tax_ref
1396 and peev1.screen_entry_value = p_reference;
1397
1398 l_proc VARCHAR2(50);
1399
1400 BEGIN
1401
1402 l_proc := g_package_functions || 'get_main_entry_value';
1403 hr_utility.set_location('Entering ' || l_proc,10);
1404
1405 /*Get tax reference of current assignment*/
1406 l_asg_tax_ref:= get_tax_ref(p_assignment_id);
1407
1408
1409 /*Get Input value id*/
1410 g_cto_main_iv_id := get_input_value('Court Order','Main CTO Entry');
1411 g_cto_ntpp_main_iv_id := get_input_value('Court Order NTPP','Main CTO Entry');
1412
1413 g_cto_main_ref_id := get_input_value('Court Order','Reference');
1414 g_cto_ntpp_main_ref_id := get_input_value('Court Order NTPP','Reference');
1415
1416 OPEN get_main_value(l_asg_tax_ref);
1417 FETCH get_main_value INTO l_main_entry_value;
1418 CLOSE get_main_value;
1419
1420 hr_utility.set_location('Leaving ' || l_proc,30);
1421 RETURN l_main_entry_value;
1422
1423 END get_main_entry_value;
1424
1425
1426 /*Added for bug fix 4395503*/
1427 FUNCTION entry_exists( p_element_entry_id in number
1428 ,p_date_earned in date
1429 ,p_asg_action_id in number
1430 ,p_reference in varchar2) RETURN VARCHAR2
1431 IS
1432 --
1433 v_exists VARCHAR2(100) := 'N';
1434 v_reversed VARCHAR2(1) := 'N';
1435 v_message varchar2(10) := 'N';
1436 v_value varchar2(100) := 'Unknown';
1437
1438 CURSOR csr_get_reference
1439 IS
1440 SELECT nvl(prrv.result_value,'Unknown')
1441 FROM pay_run_results prr,
1442 pay_run_result_values prrv,
1443 pay_assignment_actions pac,
1444 pay_input_values_f piv ,
1445 pay_payroll_actions ppa
1446 WHERE prr.run_result_id = prrv.run_result_id
1447 AND prr.entry_type = 'E'
1448 AND PRR.source_type IN ('E', 'I')
1449 AND prr.source_id = p_element_entry_id
1450 AND pac.assignment_action_id = prr.assignment_action_id
1451 AND pac.action_status in ('C')
1452 and ppa.action_type in ('R','Q')
1453 AND ppa.payroll_action_id = pac.payroll_action_id
1454 AND pac.assignment_action_id = (SELECT max(pac1.assignment_action_id)
1455 FROM pay_assignment_actions pac1,
1456 pay_run_results prr1,
1457 pay_payroll_actions ppa1
1458 WHERE pac1.assignment_action_id <> p_asg_action_id
1459 AND pac1.assignment_action_id = prr1.assignment_action_id
1460 AND ppa1.payroll_action_id = pac1.payroll_action_id
1461 AND prr1.source_id = p_element_entry_id
1462 AND pac1.action_status in ('C')
1463 and ppa1.action_type in ('R','Q')
1464 and prr1.entry_type = 'E'
1465 AND PRR1.source_type IN ('E', 'I') )
1466 AND piv.legislation_code = 'GB'
1467 AND piv.name = 'Reference'
1468 AND piv.input_value_id = prrv.input_value_id
1469 AND p_date_earned between piv.effective_start_date and piv.effective_end_date ;
1470
1471
1472 CURSOR csr_get_results
1473 IS
1474 SELECT PRR.run_result_id
1475 FROM pay_run_results PRR,
1476 pay_assignment_actions ASA,
1477 pay_payroll_actions PPA
1478 WHERE PRR.source_id = p_element_entry_id
1479 AND PRR.source_type IN ('E', 'I')
1480 AND PRR.status in ('P', 'PA', 'R', 'O')
1481 AND ASA.assignment_action_id = PRR.assignment_action_id
1482 AND asa.action_status in ( 'C')
1483 and ppa.action_type in ('R','Q')
1484 AND PPA.payroll_action_id = ASA.payroll_action_id
1485 -- Check whether the run_result has been revered.
1486 AND not exists (SELECT null
1487 FROM pay_run_results prr2
1488 WHERE prr2.source_id = PRR.run_result_id
1489 AND prr2.source_type in ('R', 'V'));
1490 --
1491
1492 l_proc VARCHAR2(100);
1493
1494 BEGIN
1495 --
1496 l_proc := g_package_functions || 'entry_exists';
1497 hr_utility.set_location('Entering ' || l_proc,10);
1498
1499 IF p_date_earned >= to_date('06-04-2006','DD-MM-YYYY') then
1500
1501 OPEN csr_get_results;
1502 FETCH csr_get_results INTO v_exists;
1503
1504 IF csr_get_results%NOTFOUND THEN
1505
1506 IF (p_reference is null or p_reference = 'Unknown') THEN
1507 v_message := 'X';
1508 ELSE
1509 v_message := 'Y';
1510 END IF;
1511
1512 ELSE
1513
1514 OPEN csr_get_reference;
1515 FETCH csr_get_reference INTO v_value;
1516
1517 IF v_value = p_reference THEN
1518 v_message := 'Y';
1519 ELSE
1520 v_message := 'N';
1521 END IF;
1522 CLOSE csr_get_reference;
1523
1524 END IF;
1525 CLOSE csr_get_results;
1526
1527 RETURN v_message;
1528 ELSE
1529 v_message := 'Y';
1530 RETURN v_message;
1531 END IF;
1532
1533 hr_utility.set_location('Leaving ' || l_proc,30);
1534
1535 EXCEPTION when NO_DATA_FOUND then
1536 hr_utility.set_location('entry_exists', 30);
1537 RETURN v_message;
1538 --
1539 END entry_exists;
1540
1541 END PAY_GB_STUDENT_LOANS_PKG;