1 PACKAGE BODY pay_kr_wg_report_pkg AS
2 /* $Header: pykrwgrp.pkb 120.1 2005/12/15 05:51:17 pdesu noship $ */
3 --
4 -- Defining Global Variables
5 --
6 g_element_entry_id NUMBER;
7 g_attachment_seq_no VARCHAR2(100);
8 g_effective_date DATE;
9
10 -- global var introduced for bug 3223825
11
12 g_int_asgitd NUMBER;
13 g_int_asgrun NUMBER;
14 g_int_asgwgitd NUMBER;
15 g_ded_asgitd NUMBER;
16 g_ded_asgrun NUMBER;
17 g_ded_asgwgitd NUMBER;
18 g_debug constant BOOLEAN :=hr_utility.debug_enabled;
19
20 ---------------------------------------------------------------------------------
21 /* FUNCTION processing_type */
22 ---------------------------------------------------------------------------------
23 FUNCTION processing_type (p_element_entry_id IN NUMBER) RETURN VARCHAR2
24 IS
25 CURSOR csr_processing_type
26 IS
27 select peev.screen_entry_value processing_type
28 from pay_element_entries_f pee
29 ,pay_element_entry_values_f peev
30 ,pay_input_values_f piv
31 where pee.element_entry_id = p_element_entry_id
32 and peev.element_entry_id = pee.element_entry_id
33 and piv.name = 'Processing Type'
34 and piv.input_value_id = peev.input_value_id
35 and g_effective_date between piv.effective_start_date and piv.effective_end_date
36 and pee.effective_start_date = peev.effective_start_date
37 and pee.effective_end_date = peev.effective_end_date
38 order by pee.effective_start_date desc;
39 l_processing_type VARCHAR2(10);
40 BEGIN
41 OPEN csr_processing_type;
42 FETCH csr_processing_type INTO l_processing_type;
43 CLOSE csr_processing_type;
44 return l_processing_type;
45 END processing_type;
46 --===============================================================================
47 -------------------------------------------------------------------------------
48 /* FUNCTION Obligation_exists */
49 -------------------------------------------------------------------------------
50 FUNCTION Obligation_exists (p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
51 ,p_effective_date IN DATE DEFAULT NULL) RETURN BOOLEAN
52 IS
53 CURSOR csr_obligation
54 IS
55 select peev.screen_entry_value obligation_release
56 ,peev.effective_end_date
57 from pay_element_entries_f pee
58 ,pay_element_entry_values_f peev
59 ,pay_input_values_f piv
60 where pee.element_entry_id = p_element_entry_id
61 and peev.element_entry_id = pee.element_entry_id
62 and piv.name ='Obligation Release'
63 and piv.input_value_id = peev.input_value_id
64 and g_effective_date between piv.effective_start_date and piv.effective_end_date
65 and pee.effective_start_date = peev.effective_start_date
66 and pee.effective_end_date = peev.effective_end_date
67 order by peev.effective_start_date desc;
68 l_obligation VARCHAR2(10);
69 l_effective_date DATE;
70 BEGIN
71 OPEN csr_obligation;
72 FETCH csr_obligation INTO l_obligation, l_effective_date;
73 CLOSE csr_obligation;
74 if g_debug then
75 hr_utility.trace('l_obligation : '||l_obligation);
76 end if;
77 -- Bug : 4866417
78 -- Removed the equal condition
79 IF l_obligation ='Y' AND nvl(p_effective_date, g_effective_date) > l_effective_date THEN
80 RETURN true;
81 END IF;
82 RETURN false;
83 EXCEPTION
84 WHEN OTHERS THEN
85 if g_debug then
86 hr_utility.trace('Error Occured Obligation_exists');
87 end if;
88 raise;
89 END Obligation_exists;
90 --==================================================================================
91 ------------------------------------------------------------------------------------
92 /* FUNCTION get_element_entry_id */
93 ------------------------------------------------------------------------------------
94 /* Bug 2856663 : condition for assignment_id added to the where clause */
95 ------------------------------------------------------------------------------------
96 FUNCTION get_element_entry_id (p_assignment_id IN per_assignments_f.assignment_id%type
97 ,p_attachment_seq_no IN VARCHAR2) RETURN NUMBER
98 IS
99 CURSOR csr_element_entry
100 IS
101 Select pee.element_entry_id
102 from pay_element_types_f pet
103 ,pay_input_values_f piv
104 ,pay_element_entries_f pee
105 ,pay_element_entry_values_f peev
106 ,pay_element_links_f pel
107 where piv.input_value_id = peev.input_value_id
108 and pet.element_type_id = piv.element_type_id
109 and piv.name = 'Attachment Seq No'
110 and pet.element_name = 'Wage Garnishments'
111 and pet.legislation_code = 'KR'
112 and pee.entry_type = 'E'
113 and pel.element_type_id = pet.element_type_id
114 and pee.assignment_id = p_assignment_id
115 and pel.element_link_id = pee.element_link_id
116 and peev.screen_entry_value = p_attachment_seq_no
117 and pee.element_entry_id = peev.element_entry_id
118 and peev.effective_start_date = pee.effective_start_date
119 and peev.effective_end_date = pee.effective_end_date
120 order by pee.element_entry_id, pee.effective_start_date ;
121 l_element_entry_id NUMBER;
122 BEGIN
123 OPEN csr_element_entry ;
124 FETCH csr_element_entry INTO l_element_entry_id;
125 CLOSE csr_element_entry ;
126 if g_debug then
127 hr_utility.trace('Element Entry Id : '||to_char(l_element_entry_id));
128 end if ;
129 return l_element_entry_id;
130 EXCEPTION
131 WHEN OTHERS THEN
132 if g_debug then
133 hr_utility.trace('Error Occured get_element_entry_id');
134 end if;
135 raise;
136 END get_element_entry_id;
137 --======================================================================================
138 ------------------------------------------------------------------------------------
139 /* FUNCTION get_attach_seq_no */
140 ------------------------------------------------------------------------------------
141 FUNCTION get_attach_seq_no (p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE) RETURN VARCHAR2
142 IS
143 CURSOR csr_attach_seq
144 IS
145 select peev.screen_entry_value
146 from pay_element_entries_f pee
147 ,pay_element_entry_values_f peev
148 ,pay_input_values_f piv
149 ,pay_element_types_f pet
150 ,pay_element_links_f pel
151 where pee.element_entry_id = p_element_entry_id
152 and peev.element_entry_id = pee.element_entry_id
153 and peev.input_value_id = piv.input_value_id
154 and piv.name ='Attachment Seq No'
155 and pet.element_type_id = piv.element_type_id
156 and pet.element_name ='Wage Garnishments'
157 and pet.legislation_code ='KR'
158 and pel.element_link_id = pee.element_link_id
159 and pel.element_type_id = pet.element_type_id
160 and g_effective_date between pet.effective_start_date and pet.effective_end_date
161 and g_effective_date between pel.effective_start_date and pel.effective_end_date
162 and pee.effective_start_date = peev.effective_start_date
163 and pee.effective_end_date = peev.effective_end_date
164 order by peev.effective_start_date desc;
165 l_attach_seq_no VARCHAR2(100);
166 BEGIN
167 OPEN csr_attach_seq;
168 FETCH csr_attach_seq INTO l_attach_seq_no;
169 CLOSE csr_attach_seq;
170 return l_attach_seq_no;
171 EXCEPTION
172 WHEN OTHERS THEN
173 if g_debug then
174 hr_utility.trace('Error Occured get_attach_seq_no');
175 end if;
176 raise;
177 END get_attach_seq_no;
178 --======================================================================================
179 ---------------------------------------------------------------------------------
180 /* FUNCTION prev_case_attachment_seq_no */
181 ---------------------------------------------------------------------------------
182 FUNCTION prev_case_attachment_seq_no (p_element_entry_id IN NUMBER) RETURN VARCHAR2
183 IS
184 CURSOR csr_attachment_seq_no
185 IS
186 Select peev1.screen_entry_value attachment_seq_no
187 from pay_element_entries_f pee
188 ,pay_element_entry_values_f peev
189 ,pay_element_entries_f pee1
190 ,pay_input_values_f piv
191 ,pay_element_types_f pet
192 ,pay_element_entry_values_f peev1
193 ,pay_input_values_f piv1
194 where pee.element_entry_id = p_element_entry_id
195 and peev.screen_entry_value = pee.entry_information21
196 and piv.input_value_id = peev.input_value_id
197 and piv.name = 'Case Number'
198 and piv.legislation_code = 'KR'
199 and pet.element_type_id = piv.element_type_id
200 and pet.element_name = 'Wage Garnishments'
201 and pet.legislation_code = 'KR'
202 and pee1.element_entry_id = peev.element_entry_id
203 and pee1.entry_type = 'E'
204 and peev1.element_entry_id = pee1.element_entry_id
205 and piv1.input_value_id = peev1.input_value_id
206 and piv1.name = 'Attachment Seq No'
207 and piv1.legislation_code = 'KR'
208 and pee.assignment_id = pee1.assignment_id
209 and piv.element_type_id = piv1.element_type_id
210 and pee1.effective_start_date = peev.effective_start_date
211 and pee1.effective_end_date = peev.effective_end_date
212 and pee1.effective_start_date = peev1.effective_start_date
213 and pee1.effective_end_date = peev1.effective_end_date
214 and g_effective_date between pet.effective_start_date and pet.effective_end_date
215 and g_effective_date between piv.effective_start_date and piv.effective_end_date
216 and g_effective_date between piv1.effective_start_date and piv1.effective_end_date
217 order by peev1.effective_start_date desc ;
218 l_attachment_seq_no VARCHAR2(100);
219 BEGIN
220 OPEN csr_attachment_seq_no;
221 FETCH csr_attachment_seq_no INTO l_attachment_seq_no;
222 CLOSE csr_attachment_seq_no;
223 return l_attachment_seq_no;
224 END prev_case_attachment_seq_no;
225 --==================================================================================
226 ---------------------------------------------------------------------------------------------
227 /* FUNCTION wage_garnishment_exists */
228 ---------------------------------------------------------------------------------------------
229 FUNCTION wage_garnishment_exists (p_assignment_id IN per_assignments_f.assignment_id%TYPE
230 ,p_effective_date IN DATE DEFAULT NULL ) RETURN boolean
231 IS
232 CURSOR csr_wg_exists (p_date date)
233 IS
234 select distinct pee.element_entry_id
235 from pay_element_entries_f pee
236 ,pay_element_types_f pet
237 ,pay_element_links_f pel
238 where pee.assignment_id = p_assignment_id
239 and pet.element_name = 'Wage Garnishments'
240 and pet.legislation_code = 'KR'
241 and pee.entry_type = 'E'
242 and pel.element_link_id = pee.element_link_id
243 and pel.element_type_id = pet.element_type_id
244 and p_date between pee.effective_start_date and pee.effective_end_date
245 and p_date between pet.effective_start_date and pet.effective_end_date
246 and p_date between pel.effective_start_date and pel.effective_end_date;
247 l_exists BOOLEAN;
248 l_element_entry_id NUMBER;
249 l_effective_date DATE;
250 BEGIN
251 IF p_effective_date IS NULL THEN
252 l_effective_date := g_effective_date;
253 ELSE
254 l_effective_date := p_effective_date;
255 END IF;
256 OPEN csr_wg_exists (l_effective_date);
257 FETCH csr_wg_exists INTO l_element_entry_id;
258 IF csr_wg_exists%FOUND THEN
259 l_exists := true;
260 ELSE
261 l_exists := false;
262 END IF;
263 CLOSE csr_wg_exists;
264 RETURN l_exists;
265 EXCEPTION
266 WHEN OTHERS THEN
267 if g_debug then
268 hr_utility.trace('Error Occured wage_garnishment_exists');
269 end if;
270 raise;
271 END wage_garnishment_exists;
272 --============================================================================================
273 ---------------------------------------------------------------------------
274 /* FUNCTION get_wg_paid_amount */
275 ---------------------------------------------------------------------------
276 FUNCTION get_wg_paid_amount (p_assignment_action_id IN NUMBER
277 ,p_source_text IN VARCHAR2
278 ,p_dim_name IN VARCHAR2) RETURN NUMBER
279 IS
280 CURSOR csr_defined_bal_id
281 IS
282 SELECT pdb.defined_balance_id
283 from pay_balance_types pbt
284 ,pay_balance_dimensions pbd
285 ,pay_defined_balances pdb
286 where pbt.balance_name ='WG Deductions'
287 and pbt.legislation_code ='KR'
288 and pbd.database_item_suffix = p_dim_name
289 and pbd.legislation_code ='KR'
290 and pdb.balance_type_id = pbt.balance_type_id
291 and pdb.balance_dimension_id = pbd.balance_dimension_id
292 and pdb.legislation_code ='KR';
293
294 l_amount NUMBER ;
295 l_defined_balance_id NUMBER;
296
297 BEGIN
298 l_amount := 0;
299 l_defined_balance_id :=0;
300 -- Bug No 3550515
301 IF p_assignment_action_id is not NULL then
302
303 pay_balance_pkg.set_context('SOURCE_TEXT', p_source_text);
304
305 If (p_dim_name='_ASG_ITD') and (g_ded_asgitd is not null) then
306 l_defined_balance_id:=g_ded_asgitd;
307 ELSIF p_dim_name='_ASG_RUN'and g_ded_asgrun is not null then
308 l_defined_balance_id:= g_ded_asgrun;
309 ELSIF p_dim_name='_ASG_WG_ITD'and g_ded_asgwgitd is not null then
310 l_defined_balance_id:= g_ded_asgwgitd;
311 ELSE
312
313 OPEN csr_defined_bal_id;
314 FETCH csr_defined_bal_id INTO l_defined_balance_id;
315 CLOSE csr_defined_bal_id;
316
317 IF p_dim_name='_ASG_ITD' THEN
318 g_ded_asgitd:=l_defined_balance_id;
319 ELSIF p_dim_name='_ASG_RUN' THEN
320 g_ded_asgrun :=l_defined_balance_id;
321 ELSIF p_dim_name='_ASG_WG_ITD' THEN
322 g_ded_asgwgitd:=l_defined_balance_id;
323 END IF;
324 END IF;
325
326 l_amount:=pay_balance_pkg.get_value (l_defined_balance_id, p_assignment_action_id);
327 END IF;
328 IF g_debug then
329 hr_utility.trace('l_amount : '||to_char(l_amount));
330 END IF;
331
332 return nvl(l_amount,0);
333
334 EXCEPTION
335 WHEN OTHERS THEN
336 if g_debug then
337 hr_utility.trace('Error Occured get_wg_paid_amount');
338 end if;
339 raise;
340 END get_wg_paid_amount;
341 --==================================================================================
342 ---------------------------------------------------------------------------
343 /* FUNCTION get_wg_interest_paid */
344 ---------------------------------------------------------------------------
348 IS
345 FUNCTION get_wg_interest_paid (p_assignment_action_id IN NUMBER
346 ,p_source_text IN VARCHAR2
347 ,p_dim_name IN VARCHAR2) RETURN NUMBER
349 CURSOR csr_wg_interest_paid
350 IS
351
352 SELECT pdb.defined_balance_id
353 from pay_balance_types pbt
354 ,pay_balance_dimensions pbd
355 ,pay_defined_balances pdb
356 where pbt.balance_name ='WG Paid Interest'
357 and pbt.legislation_code ='KR'
358 and pbd.database_item_suffix = p_dim_name
359 and pbd.legislation_code ='KR'
360 and pdb.balance_type_id = pbt.balance_type_id
361 and pdb.balance_dimension_id = pbd.balance_dimension_id
362 and pdb.legislation_code ='KR';
363 l_amount NUMBER;
364 l_defined_balance_id NUMBER;
365
366 BEGIN
367 l_amount :=0;
368 l_defined_balance_id :=0;
369 -- Bug No 3550515
370 IF p_assignment_action_id is not NULL then
371
372 pay_balance_pkg.set_context('SOURCE_TEXT', p_source_text);
373
374 IF p_dim_name='_ASG_ITD' and g_int_asgitd is not null then
375 l_defined_balance_id:=g_int_asgitd;
376
377 ELSIF p_dim_name='_ASG_RUN'and g_int_asgrun is not null then
378 l_defined_balance_id:=g_int_asgrun;
379
380 ELSIF p_dim_name='_ASG_WG_ITD'and g_int_asgwgitd is not null then
381 l_defined_balance_id:=g_int_asgwgitd;
382
383 ELSE
384 OPEN csr_wg_interest_paid;
385 FETCH csr_wg_interest_paid INTO l_defined_balance_id;
386 CLOSE csr_wg_interest_paid;
387 IF p_dim_name='_ASG_ITD' then
388 g_int_asgitd:=l_defined_balance_id;
389 ELSIF p_dim_name='_ASG_RUN' then
390 g_int_asgrun:=l_defined_balance_id;
391 ELSIF p_dim_name='_ASG_WG_ITD'then
392 g_int_asgwgitd:=l_defined_balance_id;
393 END IF;
394 END IF;
395 l_amount:=pay_balance_pkg.get_value (l_defined_balance_id, p_assignment_action_id);
396 END IF;
397 if g_debug then
398 hr_utility.trace('l_amount : '||to_char(l_amount));
399 end if;
400 return nvl(l_amount,0);
401 EXCEPTION
402 WHEN OTHERS THEN
403 if g_debug then
404 hr_utility.trace('Error Occured get_wg_interest_paid');
405 end if;
406 raise;
407 END get_wg_interest_paid;
408 --==================================================================================
409 ----------------------------------------------------------------------------------------
410 /* FUNCTION get_max_asg_action_id */
411 ----------------------------------------------------------------------------------------
412 FUNCTION get_max_asg_action_id (p_assignment_id IN NUMBER
413 ,p_effective_date IN DATE DEFAULT NULL)
414 RETURN pay_assignment_actions.assignment_action_id%type
415 IS
416 --
417 -- Modified cursor for bug 3899565
418 --
419 CURSOR csr_max_action_seq (p_assignment_id number,
420 p_date date)
421 IS
422 select max(pac.action_sequence)
423 from pay_payroll_actions ppa
424 ,pay_assignment_actions pac
425 where ppa.payroll_action_id = pac.payroll_action_id
426 and pac.assignment_id = p_assignment_id
427 and ppa.effective_date <= p_date
428 and ppa.action_type in ('B','R','Q')
429 and pac.action_status = 'C'
430 and ppa.action_status = 'C'
431 and decode(ppa.action_type,'B',0, decode(pac.source_action_id,null,-1,0)) = 0;
432 --
433 CURSOR csr_asg_action (p_assignment_id number,
434 p_action_sequence number)
435 IS
436 select
437 pac.assignment_action_id
438 from
439 pay_assignment_actions pac
440 where
441 pac.assignment_id = p_assignment_id
442 and pac.action_sequence = p_action_sequence
443 and pac.action_status = 'C';
444 --
445 l_asg_action_id NUMBER;
446 l_effective_date DATE;
447 l_action_sequence pay_assignment_actions.action_sequence%type;
448 --
449 BEGIN
450 --
451 IF p_effective_date IS NULL THEN
452 l_effective_date := g_effective_date;
453 ELSE
454 l_effective_date := p_effective_date;
455 END IF;
456
457 OPEN csr_max_action_seq (p_assignment_id, l_effective_date);
458 FETCH csr_max_action_seq INTO l_action_sequence;
459 CLOSE csr_max_action_seq;
460
461 if l_action_sequence is not null then
462 OPEN csr_asg_action (p_assignment_id, l_action_sequence);
463 FETCH csr_asg_action INTO l_asg_action_id;
464 CLOSE csr_asg_action;
465 end if;
466
467 if g_debug then
468 hr_utility.trace('l_action_sequence : '||to_char(l_action_sequence));
469 hr_utility.trace('l_asg_action_id : '||to_char(l_asg_action_id));
470 end if;
471 --
472 return l_asg_action_id;
473 --
474 EXCEPTION
475 WHEN OTHERS THEN
476 if g_debug then
477 hr_utility.trace('Error Occured get_max_asg_action_id');
481 --===========================================================================================
478 end if;
479 raise;
480 END get_max_asg_action_id;
482 ---------------------------------------------------------------------------------------------
483 /* FUNCTION paid_amount_this_run (for single creditor) */
484 ---------------------------------------------------------------------------------------------
485 FUNCTION paid_amount_this_run (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
486 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE ) RETURN NUMBER
487 IS
488 Cursor csr_paid_amount
489 IS
490 Select sum(prrv.result_value)
491 from pay_run_result_values prrv
492 ,pay_input_values_f piv
493 ,pay_run_results prr
494 where prr.source_id = p_element_entry_id
495 and prr.assignment_action_id = p_assignment_action_id
496 and prr.run_result_id = prrv.run_result_id
497 and prrv.input_value_id = piv.input_value_id
498 and piv.name = 'Pay Value'
499 and piv.legislation_code = 'KR'
500 and g_effective_date between piv.effective_start_date and piv.effective_end_date
501 and prr.element_type_id in (Select element_type_id
502 from pay_element_types_f pet
503 where element_name in ('Wage Garnishments', 'WG Redistributed Amount')
504 and legislation_code = 'KR'
505 and g_effective_date between pet.effective_start_date and pet.effective_end_date
506 );
507 l_paid_amount_this_run NUMBER;
508 BEGIN
509 l_paid_amount_this_run:= 0;
510 OPEN csr_paid_amount;
511 FETCH csr_paid_amount INTO l_paid_amount_this_run;
512 CLOSE csr_paid_amount;
513 if g_debug then
514 hr_utility.trace('Paid amount this run : '||to_char(l_paid_amount_this_run));
515 end if;
516 return l_paid_amount_this_run;
517 EXCEPTION
518 WHEN OTHERS THEN
519 if g_debug then
520 hr_utility.trace('Error Ocured in paid_amount_this_run');
521 end if;
522 raise;
523 END paid_amount_this_run;
524 --===========================================================================================
525 ---------------------------------------------------------------------------------------------
526 /* FUNCTION paid_amount_this_run (for all creditors) */
527 ---------------------------------------------------------------------------------------------
528 FUNCTION paid_amount_this_run (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE) RETURN NUMBER
529 IS
530 l_paid_amount_this_run NUMBER;
531 BEGIN
532 l_paid_amount_this_run := 0;
533 l_paid_amount_this_run := get_wg_paid_amount(p_assignment_action_id, NULL, '_ASG_RUN' );
534 if g_debug then
535 hr_utility.trace('total_paid_amount_this_run : '||to_char(l_paid_amount_this_run));
536 end if;
537 return l_paid_amount_this_run;
538 EXCEPTION
539 WHEN OTHERS THEN
540 if g_debug then
541 hr_utility.trace('Error Occured total_paid_amount_this_run');
542 end if;
543 raise;
544 END paid_amount_this_run;
545 --===========================================================================================
546 ---------------------------------------------------------------------------------------------
547 /* FUNCTION attachment_total_base */
548 ---------------------------------------------------------------------------------------------
549 FUNCTION attachment_total_base (p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
550 ,p_effective_date IN DATE DEFAULT NULL) RETURN NUMBER
551 IS
552 CURSOR csr_attachment_base (p_date DATE)
553 IS
554 select sum(nvl(peev.screen_entry_value, 0)) attachment_base
555 from pay_element_entries_f pee
556 ,pay_element_entry_values_f peev
557 ,pay_input_values_f piv
558 ,pay_element_types_f pet
559 ,pay_element_links_f pel
560 where pee.element_entry_id = p_element_entry_id
561 and pee.entry_type = 'E'
562 and peev.element_entry_id = pee.element_entry_id
563 and piv.input_value_id = peev.input_value_id
564 and pet.element_type_id = piv.element_type_id
565 and piv.name in ('Principal Base', 'Court Fee Base', 'Interest Base')
566 and pet.element_name ='Wage Garnishments'
567 and pet.legislation_code = 'KR'
568 and pel.element_link_id = pee.element_link_id
569 and pel.element_type_id = pet.element_type_id
570 and p_date between pee.effective_start_date and pee.effective_end_date
571 and p_date between peev.effective_start_date and peev.effective_end_date
572 and p_date between piv.effective_start_date and piv.effective_end_date
573 and p_date between pel.effective_start_date and pel.effective_end_date;
574 l_attachment_total_base NUMBER ;
575 l_effective_date DATE;
576 BEGIN
577 l_attachment_total_base := 0;
581 l_effective_date := p_effective_date;
578 IF p_effective_date IS NULL THEN
579 l_effective_date := g_effective_date;
580 ELSE
582 END IF;
583 OPEN csr_attachment_base (l_effective_date);
584 FETCH csr_attachment_base INTO l_attachment_total_base;
585 CLOSE csr_attachment_base;
586 if g_debug then
587 hr_utility.trace('attachment_total_base : '||to_char(l_attachment_total_base));
588 end if;
589 return nvl(l_attachment_total_base, 0);
590 EXCEPTION
591 WHEN OTHERS THEN
592 if g_debug then
593 hr_utility.trace('Error Occured in attachment_total_base');
594 end if;
595 raise;
596 END attachment_total_base;
597 --===========================================================================================
598 ---------------------------------------------------------------------------------------------
599 /* FUNCTION real_attachment_total */
600 ---------------------------------------------------------------------------------------------
601 FUNCTION real_attachment_total (p_assignment_id IN per_assignments_f.assignment_id%TYPE
602 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
603 ,p_effective_date IN DATE DEFAULT NULL) RETURN NUMBER
604 IS
605 l_attachment_base NUMBER ;
606 l_total_interest_paid NUMBER ;
607 l_real_attachment_total NUMBER ;
608 BEGIN
609 l_attachment_base := 0;
610 l_total_interest_paid := 0;
611 l_real_attachment_total := 0;
612 l_attachment_base := attachment_total_base (p_element_entry_id, p_effective_date);
613 l_total_interest_paid := paid_interest (p_assignment_id, p_element_entry_id, p_effective_date);
614 l_real_attachment_total := l_attachment_base + l_total_interest_paid;
615 if g_debug then
616 hr_utility.trace('Real Attachment Total : '||to_char(l_real_attachment_total));
617 end if;
618 return l_real_attachment_total;
619 EXCEPTION
620 WHEN OTHERS THEN
621 if g_debug then
622 hr_utility.trace('Error Ocured in real_attachment_total');
623 end if;
624 raise;
625 END real_attachment_total;
626 --===========================================================================================
627 ---------------------------------------------------------------------------------------------
628 /* FUNCTION unpaid_debt */
629 ---------------------------------------------------------------------------------------------
630 FUNCTION unpaid_debt (p_assignment_id IN per_assignments_f.assignment_id%TYPE
631 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
632 ,p_effective_date IN DATE DEFAULT NULL ) RETURN NUMBER
633 IS
634 l_unpaid_debt NUMBER;
635 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
636 BEGIN
637 l_unpaid_debt := 0;
638 IF processing_type (p_element_entry_id) = 'P' AND Obligation_exists (p_element_entry_id, p_effective_date)
639 THEN
640 RETURN 0;
641 END IF;
642 l_assignment_action_id := get_max_asg_action_id (p_assignment_id, p_effective_date);
643 l_unpaid_debt:= real_attachment_total(p_assignment_id => p_assignment_id
644 ,p_element_entry_id => p_element_entry_id
645 ,p_effective_date => p_effective_date)
646 -
647 paid_amount(p_assignment_id, p_element_entry_id, p_effective_date);
648 if g_debug then
649 hr_utility.trace('Unpaid Debt : '||to_char(l_unpaid_debt));
650 end if;
651 return l_unpaid_debt;
652 EXCEPTION
653 WHEN OTHERS THEN
654 if g_debug then
655 hr_utility.trace('End Of unpaid_debt');
656 end if;
657 raise;
658 END unpaid_debt;
659 --===============================================================================================
660 -------------------------------------------------------------------------------------------------
661 /* FUNCTION paid_amount (for single creditor) */
662 -------------------------------------------------------------------------------------------------
663 FUNCTION paid_amount (p_assignment_id IN per_assignments_f.assignment_id%TYPE
664 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
665 ,p_effective_date IN DATE DEFAULT NULL ) RETURN NUMBER
666 IS
667 l_paid_amount NUMBER ;
668 l_processing_type VARCHAR2(10);
669 l_prev_case_att_seq_no VARCHAR2(100);
670 l_assignment_action_id NUMBER;
671 l_attachment_seq_no VARCHAR2(100);
672 BEGIN
673 l_paid_amount := 0;
674 l_assignment_action_id := get_max_asg_action_id (p_assignment_id, p_effective_date);
675 l_processing_type := processing_type (p_element_entry_id);
676 l_attachment_seq_no := get_attach_seq_no (p_element_entry_id);
677 IF l_processing_type = 'P' AND Obligation_exists (p_element_entry_id, p_effective_date) THEN
678 l_paid_amount := 0;
679 ELSIF l_processing_type IN ('AS', 'AA') THEN
680 l_prev_case_att_seq_no := prev_case_attachment_seq_no (p_element_entry_id);
684 ELSE
681 l_paid_amount := get_wg_paid_amount (l_assignment_action_id, l_prev_case_att_seq_no, '_ASG_WG_ITD');
682 l_paid_amount := l_paid_amount
683 + get_wg_paid_amount (l_assignment_action_id, l_attachment_seq_no, '_ASG_WG_ITD' );
685 l_paid_amount := get_wg_paid_amount (l_assignment_action_id, l_attachment_seq_no, '_ASG_WG_ITD');
686 END IF;
687 if g_debug then
688 hr_utility.trace('l_paid_amount : '||to_char(l_paid_amount));
689 end if;
690 return l_paid_amount;
691 EXCEPTION
692 WHEN OTHERS THEN
693 if g_debug then
694 hr_utility.trace('Error occured in paid_amount to single Creditor');
695 end if;
696 raise;
697 END paid_amount;
698 --===============================================================================================
699 -------------------------------------------------------------------------------------------------
700 /* FUNCTION paid_interest_this_run (for single creditor) */
701 -------------------------------------------------------------------------------------------------
702 FUNCTION paid_interest_this_run (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
703 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE) RETURN NUMBER
704 IS
705 Cursor csr_interest
706 IS
707 Select prrv.result_value
708 from pay_run_result_values prrv
709 ,pay_input_values_f piv
710 ,pay_run_results prr
711 ,pay_element_types_f pet
712 where prr.source_id = p_element_entry_id
713 and prr.assignment_action_id = p_assignment_action_id
714 and prr.run_result_id = prrv.run_result_id
715 and prrv.input_value_id = piv.input_value_id
716 and piv.name = 'Interest This Period'
717 and piv.legislation_code = 'KR'
718 and prr.element_type_id = pet.element_type_id
719 and pet.element_name = 'WG Results'
720 and pet.legislation_code = 'KR'
721 and g_effective_date between piv.effective_start_date and piv.effective_end_date
722 and g_effective_date between pet.effective_start_date and pet.effective_end_date;
723 l_interest_paid_this_run NUMBER ;
724 BEGIN
725 l_interest_paid_this_run := 0;
726 OPEN csr_interest;
727 FETCH csr_interest INTO l_interest_paid_this_run;
728 CLOSE csr_interest;
729 if g_debug then
730 hr_utility.trace('paid_interest_this_run to a creditor '||to_char(p_element_entry_id)||' is :'||to_char(l_interest_paid_this_run));
731 end if;
732 return l_interest_paid_this_run;
733 EXCEPTION
734 WHEN OTHERS THEN
735 if g_debug then
736 hr_utility.trace('Error Ocured in paid_interest_this_run to a creditor');
737 end if;
738 raise;
739 END paid_interest_this_run;
740 --================================================================================================
741 -------------------------------------------------------------------------------------------------
742 /* FUNCTION paid_interest_this_run (for all creditors) */
743 -------------------------------------------------------------------------------------------------
744 FUNCTION paid_interest_this_run (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE) RETURN NUMBER
745 IS
746 l_paid_interest_to_all NUMBER;
747 BEGIN
748 l_paid_interest_to_all := 0;
749 l_paid_interest_to_all := get_wg_interest_paid (p_assignment_action_id, NULL, '_ASG_RUN' );
750 if g_debug then
751 hr_utility.trace('l_paid_interest_to_all : '||to_char(l_paid_interest_to_all));
752 end if;
753 return l_paid_interest_to_all;
754 EXCEPTION
755 WHEN OTHERS THEN
756 if g_debug then
757 hr_utility.trace('Error Ocured in paid_interest_this_run to all creditors');
758 end if;
759 raise;
760 END paid_interest_this_run;
761 --====================================================================================================
762 -------------------------------------------------------------------------------------------------
763 /* FUNCTION paid_interest (for single creditor) */
764 -------------------------------------------------------------------------------------------------
765 FUNCTION paid_interest (p_assignment_id IN per_assignments_f.assignment_id%TYPE
766 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
767 ,p_effective_date IN DATE DEFAULT NULL) RETURN NUMBER
768 IS
769 l_paid_interest NUMBER;
770 l_assignment_action_id NUMBER;
771 l_attachment_seq_no VARCHAR2(100);
772 BEGIN
773 l_paid_interest := 0;
774 l_assignment_action_id := get_max_asg_action_id (p_assignment_id, p_effective_date);
775 l_attachment_seq_no := get_attach_seq_no (p_element_entry_id);
776 l_paid_interest := get_wg_interest_paid (l_assignment_action_id, l_attachment_seq_no, '_ASG_WG_ITD');
777 --
778 if g_debug then
779 hr_utility.trace('Total Interest paid to the creditor '||to_char(p_element_entry_id)||' is : '||to_char(l_paid_interest));
780 end if;
781 return l_paid_interest;
782 EXCEPTION
783 WHEN OTHERS THEN
784 if g_debug then
785 hr_utility.trace('Error Occured in paid_interest to a creditors');
786 end if;
787 raise;
788 END paid_interest;
789 --====================================================================================================
790 BEGIN
791 DECLARE
792 CURSOR csr_eff_date
793 IS
794 Select ses.effective_date
795 from fnd_sessions ses
796 Where ses.session_id = userenv('sessionid');
797 CURSOR csr_sysdate
798 IS
799 Select sysdate
800 from dual;
801 BEGIN
802 OPEN csr_eff_date;
803 FETCH csr_eff_date INTO g_effective_date;
804 IF csr_eff_date%NOTFOUND THEN
805 OPEN csr_sysdate;
806 FETCH csr_sysdate INTO g_effective_date;
807 CLOSE csr_sysdate;
808 END IF;
809 CLOSE csr_eff_date;
810 END;
811 END pay_kr_wg_report_pkg;
[Home] [Help]