1 PACKAGE BODY pqp_us_ff_functions AS
2 /* $Header: pqusfffn.pkb 115.19 2002/12/02 23:42:36 rpinjala ship $ */
3 ----------------------------------------------------------------------------+
4 -- FUNCTION GET_COL_VAL
5 ----------------------------------------------------------------------------+
6 FUNCTION get_col_val(p_assignment_id IN NUMBER
7 ,p_payroll_action_id IN NUMBER
8 ,p_column_name IN VARCHAR2
9 ,p_income_code IN VARCHAR2 )
10 RETURN varchar2 IS
11 --+
12 l_col_val varchar2(60);
13 l_string varchar2(1000);
14 --+
15 l_effective_date date;
16 BEGIN
17 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
18 (p_payroll_action_id));
19
20 IF upper(p_column_name) IN ('CURRENT_RESIDENCY_STATUS',
21 'DATE_8233_SIGNED') THEN
22 l_string := 'SELECT pad.'||p_column_name ;
23 ELSE
24 l_string := 'SELECT NVL(pdd.'||p_column_name||', ''0'') ';
25 END IF;
26 l_string := l_string||
27 ' FROM pqp_analyzed_alien_data pad,
28 pqp_analyzed_alien_details pdd
29 WHERE pad.assignment_id = :b1
30 AND to_char(:b2,''yyyy'') = pad.tax_year
31 AND pad.analyzed_data_id = pdd.analyzed_data_id
32 AND pdd.income_code = :b3
33 AND rownum < 2';
34 --+
35 BEGIN
36 EXECUTE IMMEDIATE l_string INTO l_col_val
37 USING p_assignment_id, l_effective_date, p_income_code;
38 --+
39 IF l_col_val IS NULL THEN
40 l_col_val := '0';
41 END IF;
42 --+
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 RETURN '0';
46 END;
47 --+
48 RETURN l_col_val;
49 END get_col_val;
50
51 ----------------------------------------------------------------------------+
52 -- FUNCTION STATE_HONORS_TREATY
53 ----------------------------------------------------------------------------+
54 FUNCTION state_honors_treaty ( p_payroll_action_id IN NUMBER
55 ,p_ele_iv_jur_code IN VARCHAR2
56 ,p_override_loc_state IN VARCHAR2 )
57 RETURN varchar2 IS
58 --+
59 l_honor pqp_alien_state_treaties_f.treaty_honored_flag%TYPE := 'N';
60 l_state_code pay_state_rules.state_code%TYPE;
61 l_effective_date date;
62 --+
63 CURSOR c_state_honor (l_state_code varchar2) IS
64 SELECT pas.treaty_honored_flag
65 FROM pqp_alien_state_treaties_f pas
66 WHERE l_effective_date BETWEEN
67 pas.effective_start_date AND pas.effective_end_date
68 AND pas.state_code = l_state_code;
69 --+
70 CURSOR c_jurisdiction IS
71 SELECT state_code
72 FROM pay_state_rules
73 WHERE substr(jurisdiction_code,1,2) = substr(p_ele_iv_jur_code,1,2);
74 --+
75 BEGIN
76 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
77 (p_payroll_action_id));
78 IF p_ele_iv_jur_code = 'NOT ENTERED' THEN
79 l_state_code := p_override_loc_state;
80 ELSE
81 --+ get state-code for the user entered jurisdiction code
82 FOR c_rec IN c_jurisdiction LOOP
83 l_state_code := c_rec.state_code;
84 END LOOP;
85 END IF;
86 --+
87 --+ find out if the state honors the treaty
88 --+
89 FOR c_rec in c_state_honor (l_state_code) LOOP
90 l_honor := c_rec.treaty_honored_flag;
91 END LOOP;
92 --+
93 RETURN l_honor;
94 --+
95 END state_honors_treaty;
96 --+
97 ----------------------------------------------------------------------------+
98 -- FUNCTION ALIEN_TREATY_VALID
99 ----------------------------------------------------------------------------+
100 FUNCTION alien_treaty_valid (p_assignment_id IN NUMBER
101 ,p_payroll_action_id IN NUMBER
102 ,p_income_code IN VARCHAR2 )
103 RETURN varchar2 IS
104 l_effective_date date;
105 l_col_val number;
106 l_string varchar2(1000);
107 --+
108 BEGIN
109 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
110 (p_payroll_action_id));
111 l_string := 'SELECT 1
112 FROM pqp_analyzed_alien_data pad,
113 pqp_analyzed_alien_details pdd
114 WHERE pad.assignment_id = :b1
115 AND to_char(:b2,''yyyy'') = pad.tax_year
116 AND pad.analyzed_data_id = pdd.analyzed_data_id
117 AND :b2 BETWEEN NVL(treaty_benefits_start_date,:b2)
118 AND NVL(date_benefit_ends, :b2)
119 AND pdd.income_code = :b3
120 AND rownum < 2';
121 BEGIN
122 EXECUTE IMMEDIATE l_string INTO l_col_val
123 USING p_assignment_id, l_effective_date, l_effective_date,
124 l_effective_date, l_effective_date, p_income_code;
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127 RETURN 'N';
128 END;
129 --+
130 RETURN 'Y';
131 END alien_treaty_valid;
132 ----------------------------------------------------------------------------+
133 -- FUNCTION GET_ALIEN_BAL
134 ----------------------------------------------------------------------------+
135 --Note : IF the p_fit_wh_bal_flag = 'P' then this returns
136 -- the FIT pre tax balances. This has been done to
137 -- avoid adding another parameter to this pkg.
138
139 FUNCTION get_alien_bal(p_assignment_id IN NUMBER
140 ,p_effective_date IN DATE
141 ,p_payroll_action_id IN NUMBER DEFAULT NULL
142 ,p_tax_unit_id IN NUMBER DEFAULT NULL
143 ,p_income_code IN VARCHAR2 DEFAULT NULL
144 ,p_balance_name IN VARCHAR2 DEFAULT NULL
145 ,p_dimension_name IN VARCHAR2 DEFAULT NULL
146 ,p_state_code IN VARCHAR2 DEFAULT NULL
147 ,p_fit_wh_bal_flag IN VARCHAR2 DEFAULT 'N' )
148 RETURN NUMBER IS
149 --+
150 l_bal_name pay_balance_types.balance_name%type;
151 l_dim_name pay_balance_dimensions.dimension_name%type;
152 l_def_bal_id number;
153 l_amt number;
154 l_tax_unit_id varchar2(30);
155 l_boolean boolean := TRUE;
156 l_jd_code varchar2(30);
157 l_effective_date date;
158 --+
159 CURSOR c_bal_name IS
160 SELECT meaning
161 FROM hr_lookups
162 WHERE lookup_code = p_income_code
163 AND lookup_type = 'PQP_US_ALIEN_INCOME_BALANCE';
164 --+
165 CURSOR c_defined_bal IS
166 SELECT pdb.defined_balance_id
167 FROM pay_balance_types pbt,
168 pay_defined_balances pdb,
169 pay_balance_dimensions pbd
170 WHERE pbt.balance_name = l_bal_name
171 AND pbt.balance_type_id = pdb.balance_type_id
172 AND pbd.balance_dimension_id = pdb.balance_dimension_id
173 AND pbd.dimension_name = l_dim_name
174 AND NVL(pbd.legislation_code,'US') = 'US';
175 --+
176 CURSOR c_tax_unit IS
177 SELECT SFT.segment1
178 FROM hr_soft_coding_keyflex SFT,
179 per_assignments_f ASG
180 WHERE SFT.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
181 AND ASG.assignment_id = p_assignment_id;
182 --+
183 CURSOR c_jurisdiction IS
184 SELECT jurisdiction_code
185 FROM pay_state_rules
186 WHERE state_code = p_state_code;
187 --+
188 BEGIN
189 IF p_payroll_action_id IS NOT NULL THEN
190 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
191 (p_payroll_action_id));
192 ELSE
193 l_effective_date :=p_effective_date;
194 END IF;
195 --+
196 --+ If the dimension name is not passed default it
197 --+
198 IF p_dimension_name IS NULL OR p_dimension_name = 'NULL' THEN
199 l_dim_name := 'Person within Government Reporting Entity Year to Date';
200 ELSE
201 l_dim_name := p_dimension_name;
202 END IF;
203 --+
204 --+ If the income code is passed fetch the balance name
205 --+
206 IF p_income_code IS NOT NULL THEN
207 FOR c_rec IN c_bal_name LOOP
208 l_bal_name := c_rec.meaning;
209 END LOOP;
210 ELSE
211 l_bal_name := p_balance_name;
212 END IF;
213 --+
214 --+ If the requested balance is a withheld balance
215 --+
216 IF p_fit_wh_bal_flag = 'Y' THEN
217 l_bal_name := l_bal_name||' FIT WH';
218 END IF;
219 --+
220 --+ If the requested balance is a Pre-Tax balance
221 --+
222 IF p_fit_wh_bal_flag = 'P' THEN
223 l_bal_name := l_bal_name||' FIT PT';
224 END IF;
225 --+
226 --+
227 --+ If the tax unit id is not passed then fetch it
228 --+
229 IF p_tax_unit_id IS NULL THEN
230 FOR c_rec IN c_tax_unit LOOP
231 l_tax_unit_id := c_rec.segment1;
232 END LOOP;
233 ELSE
234 l_tax_unit_id := p_tax_unit_id;
235 END IF;
236 --+
237 --+ set the tax unit id context
238 --+
239 pay_balance_pkg.set_context('tax_unit_id', l_tax_unit_id);
240 --+
241 --+ fetch the defined balance it
242 --+
243 FOR c_rec IN c_defined_bal LOOP
244 l_def_bal_id := c_rec.defined_balance_id;
245 END LOOP;
246 --+
247 --+ Get the jurisdiction code and set the JD context
248 --+
249 IF NVL(p_state_code, 'NULL') <> 'NULL' THEN
250 FOR c_rec IN c_jurisdiction LOOP
251 l_jd_code := c_rec.jurisdiction_code;
252 END LOOP;
253 pay_balance_pkg.set_context('jurisdiction_code', l_jd_code);
254 END IF;
255 --+
256 --+ Finally get the actual balance
257 --+
258 l_amt := pay_balance_pkg.get_value(l_def_bal_id,
259 p_assignment_id,
260 l_effective_date);
261 --+
262 RETURN l_amt;
263 --+
264 END get_alien_bal;
265 ----------------------------------------------------------------------------+
266 -- FUNCTION IS_WINDSTAR
267 ----------------------------------------------------------------------------+
268 FUNCTION is_windstar(p_person_id IN NUMBER DEFAULT NULL
269 ,p_assignment_id IN NUMBER DEFAULT NULL)
270 --+
271 --+ Function to return TRUE/FALSE value if the assignment was/is being
272 --+ processed by windstar
273 --+
274 RETURN VARCHAR2 IS
275 --+
276 l_result VARCHAR2(30) := 'FALSE';
277 --+
278 CURSOR c_person IS
279 SELECT 'x'
280 FROM per_people_extra_info PEI
281 WHERE PEI.information_type = 'PER_US_ADDITIONAL_DETAILS'
282 AND PEI.pei_information12 = 'WINDSTAR'
283 AND PEI.person_id = p_person_id;
284 --+
285 CURSOR c_assignment IS
286 SELECT 'x'
287 FROM per_people_extra_info PEI,
288 per_all_assignments_f PAA
289 WHERE PEI.information_type = 'PER_US_ADDITIONAL_DETAILS'
290 AND PEI.pei_information12 = 'WINDSTAR'
291 AND PEI.person_id = PAA.person_id
292 AND PAA.assignment_id = p_assignment_id;
293 --+
294 BEGIN
295 IF p_person_id IS NOT NULL THEN
296 FOR c_rec in c_person LOOP
297 l_result := 'TRUE';
298 exit;
299 END LOOP;
300 ELSIF p_assignment_id IS NOT NULL THEN
301 FOR c_rec IN c_assignment LOOP
302 l_result := 'TRUE';
303 exit;
304 END LOOP;
305 END IF;
306 --+
307 RETURN l_result;
308 --+
309 END is_windstar;
310
311 ----------------------------------------------------------------------------+
312 -- FUNCTION PQP_IS_WINDSTAR
313 ----------------------------------------------------------------------------+
314 FUNCTION pqp_is_windstar( p_assignment_id IN NUMBER DEFAULT NULL)
315
316 RETURN VARCHAR2 IS
317
318 l_ret_val VARCHAR2(30);
319 --+
320 --+ Function to return a true/false value if the assignment was/is being
321 --+ processed by windstar. Function has been added as person_id
322 --+ is not available as a CTX. This calls the function IS_WINDSTAR
323 --+
324 BEGIN
325
326 l_ret_val := is_windstar( NULL,p_assignment_id);
327 RETURN l_ret_val;
328
329 END pqp_is_windstar;
330
331 ----------------------------------------------------------------------------+
332 -- FUNCTION get_nonw2_bal
333 ----------------------------------------------------------------------------+
334 FUNCTION get_nonw2_bal (p_balance_name IN VARCHAR2,
335 p_period IN VARCHAR2,
336 p_assignment_action_id IN NUMBER,
337 p_jurisdiction_code IN VARCHAR2 DEFAULT NULL,
338 p_tax_unit_id IN NUMBER)
339 RETURN NUMBER IS
340
341 l_balance_amount NUMBER;
342 l_defined_balance_id NUMBER;
343 l_dimension_name pay_balance_dimensions.dimension_name%type;
344
345 CURSOR c1 (c_balance_name varchar2, c_dimension_name varchar2) IS
346 SELECT defined_balance_id
347 FROM pay_defined_balances pdb,
348 pay_balance_types pbt,
349 pay_balance_dimensions pbd
350 WHERE pdb.balance_type_id = pbt.balance_type_id
351 AND pdb.balance_dimension_id = pbd.balance_dimension_id
352 AND pbt.balance_name = c_balance_name
353 AND pbd.dimension_name = c_dimension_name
354 AND nvl(pdb.legislation_code, 'US') = 'US';
355
356 BEGIN
357
358 IF p_balance_name = 'Non W2 FIT Withheld' THEN
359 SELECT DECODE (upper(p_period),
360 'CURRENT','Assignment-Level Current Run',
361 'RUN','Assignment within Government Reporting Entity Run',
362 'PAY','Assignment within Government Reporting Entity Pay Date',
363 'MONTH','Assignment within Government Reporting Entity Month',
364 'QTD','Assignment within Government Reporting Entity Quarter to Date',
365 'YTD','Assignment within Government Reporting Entity Year to Date',null)
366 INTO l_dimension_name
367 FROM DUAL;
368 ELSIF p_balance_name = 'SIT Alien Withheld' THEN
369 SELECT DECODE (upper(p_period),
370 'RUN','Assignment in JD within GRE Run',
371 'MONTH','Assignment in JD within GRE Month',
372 'QTD','Assignment in JD within GRE Quarter to Date',
373 'YTD','Assignment in JD within GRE Year to Date',null)
374 INTO l_dimension_name
375 FROM DUAL;
376 ELSE
377 RETURN 0;
378 END IF;
379
380 l_balance_amount := 0;
381
382 IF l_dimension_name IS NOT NULL THEN
383
384 FOR c1_rec IN c1 (p_balance_name, l_dimension_name)
385 LOOP
386 l_defined_balance_id := c1_rec.defined_balance_id;
387 END LOOP;
388
389 --+ Set up the GRE and Jurisdicton context
390
391 pay_balance_pkg.set_context('tax_unit_id', p_tax_unit_id);
392
393 IF p_balance_name <> 'Non W2 FIT Withheld' THEN
394 pay_balance_pkg.set_context('jurisdiction_code',p_jurisdiction_code);
395 END IF;
396
397 l_balance_amount := pay_balance_pkg.get_value(l_defined_balance_id,
398 p_assignment_action_id);
399 END IF;
400
401 RETURN l_balance_amount;
402
403 END;
404
405 ----------------------------------------------------------------------------+
406 -- FUNCTION GET_PREV_CONTRIB
407 ----------------------------------------------------------------------------+
408 FUNCTION get_prev_contrib(p_assignment_id IN NUMBER
409 ,p_payroll_action_id IN NUMBER
410 ,p_income_code IN VARCHAR2 )
411 --+
412 --+ Function to return the previous contribution of the employee for the
413 --+ income code
414 --+
415 RETURN NUMBER IS
416 --+
417 l_result NUMBER := 0;
418 l_effective_date date;
419 --+
420 CURSOR c_prev_contrib IS
421 SELECT pei.pei_information6
422 FROM per_people_extra_info pei
423 WHERE pei.pei_information7 = to_char(l_effective_date,'YYYY')
424 AND pei.pei_information5 = p_income_code
425 AND pei.information_type = 'PER_US_PAYROLL_DETAILS'
426 AND person_id IN
427 (SELECT pas.person_id
428 FROM per_all_assignments_f pas
429 WHERE pas.assignment_id = p_assignment_id
430 AND l_effective_date BETWEEN pas.effective_start_date
431 AND pas.effective_end_date);
432 --+
433 BEGIN
434 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
435 (p_payroll_action_id));
436 --+
437 FOR c_rec in c_prev_contrib LOOP
438 l_result := c_rec.pei_information6;
439 END LOOP;
440 --+
441 RETURN l_result;
442 --+
443 END get_prev_contrib;
444 --
445 ----------------------------------------------------------------------------+
446 -- FUNCTION PQP_PROCESS_EVENTS_EXISTS
447 ----------------------------------------------------------------------------+
448 FUNCTION pqp_process_events_exist(p_assignment_id IN NUMBER
449 ,p_income_code IN VARCHAR2 )
450 --+
451 --+ Function to check whether there are any changes to the alien data that
452 --+ are not analyzed by Windstar
453 --+
454 RETURN VARCHAR2 IS
455 --+
456 CURSOR c_process_events IS
457 SELECT ppe.process_event_id
458 FROM pay_process_events ppe
459 WHERE ppe.assignment_id = p_assignment_id
460 AND ppe.change_type = 'PQP_US_ALIEN_WINDSTAR'
461 AND ppe.status IN ('N','D','R'); --+ Not read,Read,Data validation error
462 --+
463 l_value varchar2(10) := 'N';
464 --+
465 BEGIN
466 --+
467 FOR c_rec in c_process_events LOOP
468 l_value := 'Y';
469 END LOOP;
470 --+
471 RETURN l_value;
472 --+
473 END pqp_process_events_exist;
474 --
475 ----------------------------------------------------------------------------+
476 -- FUNCTION PQP_ALIEN_TAX_ELE_EXIST
477 ----------------------------------------------------------------------------+
478 FUNCTION pqp_alien_tax_ele_exist (p_assignment_id IN NUMBER
479 ,p_effective_date IN DATE
480 )
481 --+
482 --+ Function to check whether the ALIEN_TAXATION element is attached if
483 --+ there are earnings for classification Alien Earnings.
484 --+ If the function returns 'N' then there is an Alien Earnings and the
485 --+ ALIEN_TAXATION element is not attached.
486 --+
487 RETURN VARCHAR2 IS
488 --+
489 CURSOR c_alien_earn_exist IS
490 SELECT 'x'
491 FROM pay_element_entries_f pee
492 ,pay_element_links_f pel
493 ,pay_element_types_f pet
494 ,pay_element_classifications pec
495 WHERE pee.assignment_id = p_assignment_id
496 AND pee.element_link_id = pel.element_link_id
497 AND pel.element_type_id = pet.element_type_id
498 AND pet.classification_id = pec.classification_id
499 AND pec.classification_name = 'Alien/Expat Earnings'
500 AND pec.legislation_code = 'US'
501 AND p_effective_date BETWEEN pee.effective_start_date
502 AND pee.effective_end_date
503 AND p_effective_date BETWEEN pel.effective_start_date
504 AND pel.effective_end_date
505 AND p_effective_date BETWEEN pet.effective_start_date
506 AND pet.effective_end_date;
507 --+
508 CURSOR c_alien_taxation IS
509 SELECT 'x'
510 FROM pay_element_entries_f pee
511 ,pay_element_links_f pel
512 ,pay_element_types_f pet
513 WHERE pee.assignment_id = p_assignment_id
514 AND pee.element_link_id = pel.element_link_id
515 AND pel.element_type_id = pet.element_type_id
516 AND pet.element_name = 'ALIEN_TAXATION'
517 AND p_effective_date BETWEEN pee.effective_start_date
518 AND pee.effective_end_date
519 AND p_effective_date BETWEEN pel.effective_start_date
520 AND pel.effective_end_date
521 AND p_effective_date BETWEEN pet.effective_start_date
522 AND pet.effective_end_date;
523 --+
524 l_exist varchar2(1);
525 --+
526 BEGIN
527 FOR c_rec IN c_alien_earn_exist LOOP
528 --+
529 FOR c_rec_tax IN c_alien_taxation LOOP
530 --+
531 --+ All fine as ALIEN_TAXATION element is attached to the asg
532 --+
533 RETURN 'Y';
534 END LOOP;
535 --+
536 --+ Alien earnings exists but ALIEN_TAXATION element is NOT attached to the asg
537 --+
538 RETURN 'N';
539 END LOOP;
540 --+
541 --+ All fine as there are no Alien earnings and ALIEN_TAXATION
542 --+ element is not required
543 --+
544 RETURN 'Y';
545 --+
546 END pqp_alien_tax_ele_exist;
547 --
548 -------------------------------------------------------------------+
549 --
550 -- FUNCTION get_trr_nonw2_bal
551 --
552 -- ** NOTE ** Removed the Summing of balances for all the Asingments
553 -- and replaced the dimensions with the new GRE_JD dim.
554 -- 14-JUN-2002 -- tmehra
555 --
556 -- Function to return the GRE level balances, Since we do no store
557 -- GRE level balances, we compute this by adding balances of all the
558 -- assignments for a given GRE. Function written to compute 'Non W2'
559 -- balances at the 'GRE' level.
560 -------------------------------------------------------------------+
561 FUNCTION get_trr_nonw2_bal (p_gre IN NUMBER,
562 p_jd IN VARCHAR2 DEFAULT NULL,
563 p_start_date IN DATE,
564 p_end_date IN DATE,
565 p_bal_name IN VARCHAR2,
566 p_dim IN VARCHAR2)
567 RETURN NUMBER IS
568
569 --
570 -- Cursor to get all the processed assignemtns in
571 -- a given GRE withing a date range
572 --
573
574 l_dimension pay_balance_dimensions.dimension_name%type;
575 l_defined_bal_id NUMBER;
576 l_def_pre_tax_id NUMBER;
577 l_bal_name pay_balance_types.balance_name%type;
578 l_bal_amt NUMBER;
579 l_asg_action_id NUMBER;
580 ---------------------------------------+
581 -- Funtion to get the defined balance Id
582 -- for a given balance and dimension
583 ---------------------------------------+
584 FUNCTION get_defined_bal_id (p_bal_name VARCHAR2
585 ,p_dimension VARCHAR2) RETURN number IS
586
587 l_id number := 0;
588
589 --+ Cursor to get the defined balance id for a given balance and a dimension
590
591 CURSOR crs_get_defined_bal_id (p_bal_name VARCHAR2
592 ,p_dimension VARCHAR2) IS
593 SELECT dbl.defined_balance_id
594 FROM pay_defined_balances dbl
595 WHERE dbl.balance_type_id =
596 (SELECT balance_type_id
597 FROM pay_balance_types blt
598 WHERE blt.balance_name = p_bal_name
599 AND blt.legislation_code = 'US')
600 AND dbl.balance_dimension_id =
601 (SELECT balance_dimension_id
602 FROM pay_balance_dimensions bld
603 WHERE bld.database_item_suffix =
604 '_'|| p_dimension
605 AND bld.legislation_code = 'US')
606 AND dbl.legislation_code = 'US';
607
608 BEGIN
609 --+ Get the defined balance id for the passed balance and dimension
610
611 FOR i IN crs_get_defined_bal_id (p_bal_name, p_dimension)
612 LOOP
613 l_id := i.defined_balance_id;
614 END LOOP;
615
616 RETURN l_id;
617
618 END;
619
620 ----------------------------------------+
621 -- end of the get_defined_bal_id function
622 ----------------------------------------+
623
624 BEGIN
625
626 l_bal_amt := 0;
627 l_asg_action_id := NULL;
628
629 -- Commented out and replaced by the following line
630 -- on 13-Jun-2002 tmehra
631 -- pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre);
632 -- pay_us_balance_view_pkg.set_context('DATE_EARNED',p_end_date);
633
634 pay_balance_pkg.set_context('TAX_UNIT_ID',p_gre);
635 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(p_end_date));
636 pay_balance_pkg.set_context('BALANCE_DATE',fnd_date.date_to_canonical(p_start_date));
637
638 IF p_bal_name = 'Non W2 FIT' THEN
639
640 IF p_dim = 'CTD' THEN
641 l_dimension := 'GRE_PYDATE';
642 ELSIF p_dim = 'MTD' THEN
643 l_dimension := 'GRE_MONTH';
644 ELSIF p_dim = 'QTD' THEN
645 l_dimension := 'GRE_QTD';
646 ELSIF p_dim = 'YTD' THEN
647 l_dimension := 'GRE_YTD';
648 END IF;
649
650 l_defined_bal_id := get_defined_bal_id ('Non W2 FIT Withheld',l_dimension);
651
652 l_bal_amt := pay_balance_pkg.get_value(l_defined_bal_id
653 ,l_asg_action_id);
654 ELSIF p_bal_name = 'Non W2 SIT' THEN
655
656 IF p_dim = 'CTD' THEN
657 l_dimension := 'GRE_JD_PYDATE';
658 ELSIF p_dim = 'MTD' THEN
659 l_dimension := 'GRE_JD_MONTH';
660 ELSIF p_dim = 'QTD' THEN
661 l_dimension := 'GRE_JD_QTD';
662 ELSIF p_dim = 'YTD' THEN
663 l_dimension := 'GRE_JD_YTD';
664 END IF;
665
666 l_defined_bal_id := get_defined_bal_id ('SIT Alien Withheld',l_dimension);
667
668 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jd);
669
670 l_bal_amt := pay_balance_pkg.get_value(l_defined_bal_id
671 ,l_asg_action_id);
672
673
674 ELSIF p_bal_name = 'Non W2 FIT Wages' THEN
675
676 IF p_dim = 'CTD' THEN
677 l_dimension := 'GRE_PYDATE';
678 ELSIF p_dim = 'MTD' THEN
679 l_dimension := 'GRE_MONTH';
680 ELSIF p_dim = 'QTD' THEN
681 l_dimension := 'GRE_QTD';
682 ELSIF p_dim = 'YTD' THEN
683 l_dimension := 'GRE_YTD';
684 END IF;
685
686 l_defined_bal_id := get_defined_bal_id ('FIT Alien Subj Whable',l_dimension);
687 l_def_pre_tax_id := get_defined_bal_id ('FIT Non W2 Pre Tax Dedns',l_dimension);
688
689 l_bal_amt := l_bal_amt
690 + pay_balance_pkg.get_value(l_defined_bal_id
691 ,l_asg_action_id)
692 - pay_balance_pkg.get_value(l_def_pre_tax_id
693 ,l_asg_action_id);
694
695 ELSIF p_bal_name = 'Non W2 SIT Wages' THEN
696
697 IF p_dim = 'CTD' THEN
698 l_dimension := 'GRE_JD_PYDATE';
699 ELSIF p_dim = 'MTD' THEN
700 l_dimension := 'GRE_JD_MONTH';
701 ELSIF p_dim = 'QTD' THEN
702 l_dimension := 'GRE_JD_QTD';
703 ELSIF p_dim = 'YTD' THEN
704 l_dimension := 'GRE_JD_YTD';
705 END IF;
706
707 l_defined_bal_id := get_defined_bal_id ('SIT Alien Subj Whable',l_dimension);
708 l_def_pre_tax_id := get_defined_bal_id ('SIT Non W2 Pre Tax Dedns',l_dimension);
709
710 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jd);
711
712 l_bal_amt := l_bal_amt
713 + pay_balance_pkg.get_value(l_defined_bal_id
714 ,l_asg_action_id)
715 - pay_balance_pkg.get_value(l_def_pre_tax_id
716 ,l_asg_action_id);
717 END IF;
718
719
720 RETURN l_bal_amt;
721 END;
722 --------------------------------------------------------------------------+
723
724
725 END pqp_us_ff_functions;
[Home] [Help]