[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYUSTOT_XMLP_PKG
Source
4 BEGIN
1 PACKAGE BODY PAY_PAYUSTOT_XMLP_PKG AS
2 /* $Header: PAYUSTOTB.pls 120.0 2008/01/07 11:56:49 srikrish noship $ */
3 FUNCTION beforereport RETURN boolean IS
5
6 --hr_standard.event('BEFORE REPORT');
7 -- Commented by Raj Starts
8 -- p_start_date := TRUNC(p_end_date, 'Year');
9 -- cp_state_status := pay_us_payroll_utils.check_balance_status(p_start_date, p_business_group_id, 'GRE_TOTALS_FEDERAL');
10 -- cp_fed_status := pay_us_payroll_utils.check_balance_status(p_start_date, p_business_group_id, 'GRE_TOTALS_STATE');
11 -- Commented by Raj Ends
12
13 p_start_date_m := TRUNC(p_end_date, 'Year');
14 cp_state_status := pay_us_payroll_utils.check_balance_status(p_start_date_m, p_business_group_id, 'GRE_TOTALS_FEDERAL');
15 cp_fed_status := pay_us_payroll_utils.check_balance_status(p_start_date_m, p_business_group_id, 'GRE_TOTALS_STATE');
16
17 IF cp_state_status = 'Y' OR cp_fed_status = 'Y' THEN
18 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(p_end_date));
19 pay_balance_pkg.set_context('BALANCE_DATE', fnd_date.date_to_canonical(p_end_date));
20 pay_us_balance_view_pkg.set_session_var('GROUP_RB_REPORT', 'TRUE');
21 pay_us_balance_view_pkg.set_session_var('REPORT_TYPE', 'W2');
22 -- Commented by Raj Starts
23 --pay_us_balance_view_pkg.set_session_var('GROUP_RB_SDATE', p_start_date);
24 -- Commented by Raj Ends
25 pay_us_balance_view_pkg.set_session_var('GROUP_RB_SDATE', p_start_date_m);
26 pay_us_balance_view_pkg.set_session_var('GROUP_RB_EDATE', p_end_date);
27 END IF;
28
29 DECLARE trace VARCHAR2(30) := '';
30 BEGIN
31
32 p_session_date := p_end_date;
33 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
34
35 IF p_tax_unit_id IS NULL THEN
36 c_tax_unit_name := 'ALL';
37 ELSE
38 c_tax_unit_name := hr_us_reports.get_tax_unit(p_tax_unit_id);
39 END IF;
40
41 SELECT UPPER(parameter_value)
42 INTO trace
43 FROM pay_action_parameters
44 WHERE parameter_name = 'TRACE';
45
46 IF trace <> 'N' THEN
47
48 /*srw.do_sql('alter session set SQL_TRACE TRUE');null;*/ EXECUTE IMMEDIATE 'alter session set SQL_TRACE TRUE';
49
50 END IF;
51
52 EXCEPTION
53 WHEN no_data_found THEN
54 NULL;
55 END;
56
57 RETURN(TRUE);
58 END;
59
60 FUNCTION cf_fed_gross_wagesformula(gre_id IN NUMBER) RETURN NUMBER IS l_value1 NUMBER;
61 l_value2 NUMBER;
62 l_value3 NUMBER;
63 l_value4 NUMBER;
64 l_value5 NUMBER;
65 l_value6 NUMBER;
66 l_value7 NUMBER;
67 l_value8 NUMBER;
68 l_value9 NUMBER;
69 l_value10 NUMBER;
70 CURSOR federal_balance_value IS
71 SELECT d_tax_type,
72 d_tax_classification,
73 d_wage_classification,
74 d_wage_otd_value,
75 d_tax_otd_value
76 FROM pay_us_federal_tax_bal_gre_v
77 WHERE d_balance_set_name LIKE 'GRE_TOTALS_FED_YTD'
78 AND d_tax_unit_id = gre_id;
79
80 l_tax_type VARCHAR2(240);
81 l_tax_classification VARCHAR2(240);
82 l_wage_classification VARCHAR2(240);
83 l_tax_otd_val NUMBER;
84 l_wage_otd_value NUMBER;
85
86 BEGIN
87
88 IF cp_fed_status = 'Y' THEN
89
90 OPEN federal_balance_value;
91 LOOP
92 FETCH federal_balance_value
93 INTO l_tax_type,
94 l_tax_classification,
95 l_wage_classification,
96 l_wage_otd_value,
97 l_tax_otd_val;
98 EXIT
99 WHEN federal_balance_value % NOTFOUND;
100
101 IF l_tax_type = 'FIT'
102 AND(l_tax_classification = 'WITHHELD' OR l_wage_classification = 'REDUCED_SUBJ_WHABLE') THEN
103 cp_fit_withheld := nvl(l_tax_otd_val, 0);
104 cp_fed_others := nvl(l_wage_otd_value, 0);
105
106 ELSIF l_tax_type = 'SS'
107 AND(l_tax_classification = 'WITHHELD' OR l_wage_classification = 'TAXABLE') THEN
108 cp_ss_ee_withheld := nvl(l_tax_otd_val, 0);
109 cp_ss_ee_taxable := nvl(l_wage_otd_value, 0);
110
111 ELSIF l_tax_type = 'MEDICARE'
112 AND(l_tax_classification = 'WITHHELD' OR l_wage_classification = 'TAXABLE') THEN
113 cp_medicare_ee_withheld := nvl(l_tax_otd_val, 0);
114 cp_medicare_ee_taxable := nvl(l_wage_otd_value, 0);
115
116 ELSIF l_tax_type = 'GROSS_EARNINGS'
117 AND l_tax_classification = 'NONE' THEN
118 l_value4 := nvl(l_tax_otd_val, 0);
119
120 END IF;
121
122 END LOOP;
123
124 CLOSE federal_balance_value;
125
126 ELSE
127 pay_us_taxbal_view_pkg.us_gp_multiple_gre_ytd(p_tax_unit_id => gre_id, p_effective_date => p_end_date, p_balance_name1 => 'Pre Tax Deductions', p_balance_name2 => 'FIT Non W2 Pre Tax Dedns',
128 p_balance_name3 => 'FIT Withheld', p_balance_name4 => 'Gross Earnings', p_balance_name5 => 'Medicare EE Taxable', p_balance_name6 => 'Medicare EE Withheld', p_balance_name7 => 'Regular Earnings',
129 p_balance_name8 => 'SS EE Taxable', p_balance_name9 => 'SS EE Withheld', p_balance_name10 => NULL, p_value1 => l_value1, p_value2 => l_value2, p_value3 => l_value3, p_value4 => l_value4,
133 cp_fit_non_w2_pre_tax_dedns := l_value2;
130 p_value5 => l_value5, p_value6 => l_value6, p_value7 => l_value7, p_value8 => l_value8, p_value9 => l_value9, p_value10 => l_value10);
131
132 cp_pre_tax_deductions := l_value1;
134 cp_fwt_regular_earnings := l_value7;
135
136 cp_fit_withheld := l_value3;
137 cp_medicare_ee_taxable := l_value5;
138 cp_medicare_ee_withheld := l_value6;
139 cp_ss_ee_taxable := l_value8;
140 cp_ss_ee_withheld := l_value9;
141
142 END IF;
143
144 RETURN l_value4;
145 END;
146
147 FUNCTION cf_fed_wages_tips_otherformula(gre_id IN NUMBER) RETURN NUMBER IS
148
149 l_balance NUMBER;
150 l_value1 NUMBER;
151 l_value2 NUMBER;
152 l_value3 NUMBER;
153 l_value4 NUMBER;
154 l_value5 NUMBER;
155 BEGIN
156
157 IF cp_fed_status = 'Y' THEN
158 l_balance := nvl(cp_fed_others, 0);
159 ELSE
160 pay_us_taxbal_view_pkg.us_gp_subject_to_tax_gre_ytd(p_balance_name1 => 'Supplemental Earnings for NWFIT', p_balance_name2 => 'Supplemental Earnings for FIT', p_balance_name3 => 'Pre Tax Deductions for FIT',
161 p_balance_name4 => NULL, p_balance_name5 => NULL, p_effective_date => p_end_date, p_tax_unit_id => gre_id, p_value1 => l_value1, p_value2 => l_value2, p_value3 => l_value3,
162 p_value4 => l_value4, p_value5 => l_value5);
163
164 cp_fwt_supp_earn_nwfit := l_value1;
165 cp_fwt_supp_earn_fit := l_value2;
166 cp_pre_tax_deductions_for_fit := l_value3;
167
168 l_balance := cp_fwt_supp_earn_nwfit + cp_fwt_regular_earnings + cp_fwt_supp_earn_fit -(cp_pre_tax_deductions -cp_pre_tax_deductions_for_fit -cp_fit_non_w2_pre_tax_dedns);
169 END IF;
170
171 RETURN(l_balance);
172 END;
173
174 FUNCTION cf_state_gross_wagesformula(gre_id IN NUMBER, state_code IN VARCHAR2, state IN VARCHAR2) RETURN NUMBER IS l_balance NUMBER;
175 l_value1 NUMBER;
176 l_value2 NUMBER;
177 l_value3 NUMBER;
178 l_value4 NUMBER;
179 l_value5 NUMBER;
180 l_value6 NUMBER;
181 l_value7 NUMBER;
182
183 CURSOR state_balance_value IS
184 SELECT DISTINCT d_tax_classification,
185 d_wage_classification,
186 d_tax_otd_value,
187 d_wage_otd_value
188 FROM pay_us_state_tax_bal_gre_v
189 WHERE d_balance_set_name = 'GRE_TOTALS_STATE_YTD'
190 AND d_tax_unit_id = gre_id
191 AND SUBSTR(d_state_code, 1, 2) = state_code
192 AND d_tax_type = 'SIT';
193
194 l_tax_classification VARCHAR2(240);
195 l_wage_classification VARCHAR2(240);
196 l_tax_otd_val NUMBER;
197 l_wage_otd_value NUMBER;
198
199 BEGIN
200
201 cp_sit_ee_withheld := 0;
202 cp_state_wages_tips_other := 0;
203
204 IF cp_state_status = 'Y' THEN
205
206 OPEN state_balance_value;
207 LOOP
208 FETCH state_balance_value
209 INTO l_tax_classification,
210 l_wage_classification,
211 l_tax_otd_val,
212 l_wage_otd_value;
213 EXIT
214 WHEN state_balance_value % NOTFOUND;
215
216 IF l_tax_classification = 'GROSS' THEN
217 l_value2 := nvl(l_tax_otd_val, 0);
218 ELSIF(l_tax_classification = 'WITHHELD' OR l_wage_classification = 'REDUCED_SUBJ_WHABLE') THEN
219 cp_sit_ee_withheld := nvl(l_tax_otd_val, 0);
220 cp_state_wages_tips_other := nvl(l_wage_otd_value, 0);
221 END IF;
222
223 END LOOP;
224
225 CLOSE state_balance_value;
226 ELSE
227
228 IF pay_us_tax_info_pkg.get_sit_exist(p_state_abbrev => state, p_date => p_end_date) = FALSE THEN
229 l_value2 := 0;
230 cp_sit_ee_withheld := 0;
231 cp_state_wages_tips_other := 0;
232 ELSE
233 pay_us_taxbal_view_pkg.us_gp_gre_jd_ytd(p_balance_name1 => 'SIT Withheld', p_balance_name2 => 'SIT Gross', p_balance_name3 => 'SIT Subj Whable', p_balance_name4 => 'SIT Pre Tax Redns',
234 p_balance_name5 => NULL, p_balance_name6 => NULL, p_balance_name7 => 'SIT Subj NWhable', p_effective_date => p_end_date, p_tax_unit_id => gre_id, p_state_code => state_code, p_value1 => l_value1,
235 p_value2 => l_value2, p_value3 => l_value3, p_value4 => l_value4, p_value5 => l_value5, p_value6 => l_value6, p_value7 => l_value7);
236
237 cp_sit_ee_withheld := l_value1;
238 cp_state_wages_tips_other := l_value3 + l_value7 -l_value4;
239 END IF;
240
241 END IF;
242
243 RETURN(l_value2);
244 END;
245
246 FUNCTION gre_tax_balance(p_business_group_id IN NUMBER, p_gre_org_id IN NUMBER, p_def_bal_id IN NUMBER, p_start_date IN DATE, p_end_date IN DATE) RETURN NUMBER IS
247
248 l_balance_total NUMBER := 0;
249
250 CURSOR asg_cur IS
251 SELECT paf.person_id,
252 paf.assignment_id,
253 paaf.assignment_action_id
254 FROM per_assignments_f paf,
255 pay_assignment_actions paaf,
256 pay_payroll_actions ppa,
257 hr_soft_coding_keyflex hsck
258 WHERE(ppa.effective_date BETWEEN p_start_date
259 AND p_end_date)
260 AND ppa.payroll_action_id = paaf.payroll_action_id
261 AND paaf.assignment_id = paf.assignment_id
262 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
266 BEGIN
263 AND hsck.segment1 = p_gre_org_id
264 AND paf.business_group_id = p_business_group_id;
265
267
268 pay_balance_pkg.set_context('TAX_UNIT_ID', to_char(p_gre_org_id));
269
270 FOR asgc IN asg_cur
271 LOOP
272
273 l_balance_total := l_balance_total + pay_balance_pkg.get_value(p_def_bal_id, asgc.assignment_action_id, FALSE);
274
275 END LOOP;
276
277 RETURN l_balance_total;
278
279 END;
280
281 FUNCTION cf_1formula(gre_id IN NUMBER, state IN VARCHAR2) RETURN VARCHAR2 IS
282 BEGIN
283 DECLARE l_sui_ein VARCHAR2(20);
284 BEGIN
285
286 SELECT sein.org_information3
287 INTO l_sui_ein
288 FROM hr_organization_information sein
289 WHERE sein.organization_id = gre_id
290 AND sein.org_information1 = state
291 AND sein.org_information_context = 'State Tax Rules';
292
293 cp_state_tax_unit := l_sui_ein;
294
295 RETURN cp_state_tax_unit;
296
297 EXCEPTION
298 WHEN no_data_found THEN
299 RETURN 'No State EIN';
300 END;
301 RETURN NULL;
302 END;
303
304 FUNCTION cp_fit_withheldformula RETURN NUMBER IS
305 BEGIN
306 NULL;
307 RETURN NULL;
308 END;
309
310 FUNCTION cp_medicare_ee_taxableformula RETURN NUMBER IS
311 BEGIN
312 NULL;
313 RETURN NULL;
314 END;
315
316 FUNCTION cp_medicare_ee_withheldformula RETURN NUMBER IS
317 BEGIN
318 NULL;
319 RETURN NULL;
320 END;
321
322 FUNCTION cp_ss_ee_taxableformula RETURN NUMBER IS
323 BEGIN
324 NULL;
325 RETURN NULL;
326 END;
327
328 FUNCTION cp_ss_ee_withheldformula RETURN NUMBER IS
329 BEGIN
330 NULL;
331 RETURN NULL;
332 END;
333
334 FUNCTION cp_def_comp_401kformula RETURN NUMBER IS
335 BEGIN
336 NULL;
337 RETURN NULL;
338 END;
339
340 FUNCTION cp_regular_earningsformula RETURN NUMBER IS
341 BEGIN
342 NULL;
343 RETURN NULL;
344 END;
345
346 FUNCTION cp_section_125formula RETURN NUMBER IS
347 BEGIN
348 NULL;
349 RETURN NULL;
350 END;
351
352 FUNCTION cp_fwt_supp_earn_nwfitformula RETURN NUMBER IS
353 BEGIN
354 NULL;
355 RETURN NULL;
356 END;
357
358 FUNCTION cp_fwt_supp_earn_fitformula RETURN NUMBER IS
359 BEGIN
360 NULL;
361 RETURN NULL;
362 END;
363
364 FUNCTION cp_def_comp_401k_for_fitformul RETURN NUMBER IS
365 BEGIN
366 NULL;
367 RETURN NULL;
368 END;
369
370 FUNCTION cp_state_wages_tips_otherformu RETURN NUMBER IS
371 BEGIN
372 NULL;
373 RETURN NULL;
374 END;
375
376 FUNCTION cp_sit_ee_withheldformula RETURN NUMBER IS
377 BEGIN
378 NULL;
379 RETURN NULL;
380 END;
381
382 FUNCTION cf_message_lineformula(cf_fed_gross_wages IN NUMBER) RETURN VARCHAR2 IS ret_val VARCHAR2(100) := ' ';
383
384 BEGIN
385
386 IF cf_fed_gross_wages = 0 THEN
387 ret_val := '**** No Wages paid during the period reported ****';
388 ELSE
389 ret_val := NULL;
390 END IF;
391
392 RETURN ret_val;
393
394 END;
395
396 FUNCTION afterreport RETURN boolean IS
397 BEGIN
398
399 --hr_standard.event('AFTER REPORT');
400 RETURN(TRUE);
401 END;
402
403 --Functions to refer Oracle report placeholders--
404
405 FUNCTION cp_fed_others_p RETURN NUMBER IS
406 BEGIN
407 RETURN cp_fed_others;
408 END;
409 FUNCTION cp_fwt_supp_earn_nwfit_p RETURN NUMBER IS
410 BEGIN
411 RETURN cp_fwt_supp_earn_nwfit;
412 END;
413 FUNCTION cp_fwt_supp_earn_fit_p RETURN NUMBER IS
414 BEGIN
415 RETURN cp_fwt_supp_earn_fit;
416 END;
417 FUNCTION cp_pre_tax_deductions_for_p RETURN NUMBER IS
418 BEGIN
419 RETURN cp_pre_tax_deductions_for_fit;
420 END;
421 FUNCTION cp_fit_non_w2_pre_tax_dedns_p RETURN NUMBER IS
422 BEGIN
423 RETURN cp_fit_non_w2_pre_tax_dedns;
424 END;
425 FUNCTION cp_fwt_regular_earnings_p RETURN NUMBER IS
426 BEGIN
427 RETURN cp_fwt_regular_earnings;
428 END;
429 FUNCTION cp_pre_tax_deductions_p RETURN NUMBER IS
430 BEGIN
431 RETURN cp_pre_tax_deductions;
432 END;
433 FUNCTION cp_fit_withheld_p RETURN NUMBER IS
434 BEGIN
435 RETURN cp_fit_withheld;
436 END;
437 FUNCTION cp_ss_ee_taxable_p RETURN NUMBER IS
438 BEGIN
439 RETURN cp_ss_ee_taxable;
440 END;
441 FUNCTION cp_ss_ee_withheld_p RETURN NUMBER IS
442 BEGIN
443 RETURN cp_ss_ee_withheld;
444 END;
445 FUNCTION cp_medicare_ee_taxable_p RETURN NUMBER IS
446 BEGIN
447 RETURN cp_medicare_ee_taxable;
448 END;
449 FUNCTION cp_medicare_ee_with_p RETURN NUMBER IS
450 BEGIN
451 RETURN cp_medicare_ee_withheld;
452 END;
453 FUNCTION cp_state_wages_tips_other_p RETURN NUMBER IS
454 BEGIN
455 RETURN cp_state_wages_tips_other;
456 END;
457 FUNCTION cp_sit_ee_withheld_p RETURN NUMBER IS
458 BEGIN
459 RETURN cp_sit_ee_withheld;
460 END;
461 FUNCTION c_business_group_name_p RETURN VARCHAR2 IS
462 BEGIN
463 RETURN c_business_group_name;
464 END;
465 FUNCTION c_report_subtitle_p RETURN VARCHAR2 IS
466 BEGIN
467 RETURN c_report_subtitle;
468 END;
469 FUNCTION c_tax_unit_name_p RETURN VARCHAR2 IS
470 BEGIN
471 RETURN c_tax_unit_name;
472 END;
473 FUNCTION cp_state_tax_unit_p RETURN VARCHAR2 IS
474 BEGIN
475 RETURN cp_state_tax_unit;
476 END;
477 FUNCTION cp_state_status_p RETURN VARCHAR2 IS
478 BEGIN
479 RETURN cp_state_status;
480 END;
481 FUNCTION cp_fed_status_p RETURN VARCHAR2 IS
482 BEGIN
483 RETURN cp_fed_status;
484 END;
485 END PAY_PAYUSTOT_XMLP_PKG;