[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_TRR_PKG
Source
1 PACKAGE BODY pay_mx_trr_pkg AS
2 /* $Header: pymxtrr.pkb 120.17.12020000.2 2012/08/03 13:33:46 sjawid ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, IN *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_mx_trr_pkg
21
22 Description : This package is used by the Payroll Tax Remittance Report
23 AND Payroll Tax Remittance XML Report
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 01-Dec-2004 ssmukher 115.0 Created.
30 10-Dec-2004 kthirmiy 115.1 changed to encoding="UTF-8"
31 25-Feb-2005 kthirmiy 115.2 4208324 corrected the social security quota
32 ER.
33 28-Feb-2005 kthirmiy 115.3 4212591 Sum the Ins type soc sec quota AND
34 show as soc sec quota EE AND ER
35 total .
36 12-Apr-2005 kthirmiy 115.4 4288436 Added the logic to show state
37 earning details.
38 18-Apr-2005 kthirmiy 115.5 4309627 Added IF condtion to check state
39 earning table count is > 0.
40 26-Apr-2005 kthirmiy 115.6 4322395, corrected tag to DTD FROM CTD
41 4324839 corrected IN the fetch_define_bal
42 03-May-2005 kthirmiy 115.7 4341243 changed to show state_name instead
43 of state_code at the parameters
44 display section
45 04-May-2005 kthirmiy 115.8 Corrected tag to
46 ST_STATE_DETAILS_SUBJ_DTD
47 FROM ST_STATE_DETAILS_SUBJ_CTD.
48 11-May-2005 kthirmiy 115.9 4355490 added code to calculate AND show the
49 state tax withheld for chihuahua.
50 13-May-2005 kthirmiy 115.10 4355490 moved the calc logic at state level
51 state tax withheld shows only at
52 state AND Legal Employer level for
53 chihauhau.
54 19-May-2005 kthirmiy 115.11 4380947 Added a new tag
55 SHOW_STATE_DETAILS_FLAG to
56 conditionally display state earnings
57 29-Jul-2005 kthirmiy 115.12 4526042 Chihuahua state tax calculation
58 marginal rate IN user table is
59 stored as a percentage so changed
60 percentage rate divided by 100
61 19-Dec-2005 kthirmiy 115.13 Increased wait time to 2400 secs to
62 finish
63 the XML report process
64 Added hr_mx_utility.get_IANA_charset
65 to get the charset encoding
66 dynamically.
67 13-Jan-2005 kthirmiy 115.14 modified fetch_active_assg_act to
68 get the assignment action id.
69 Added p_gre_id IN the CURSOR
70 get_states_for_legal_emp
71 to fix RGIS Tar 15658700.600
72 23-Mar-2006 vpandya 115.15 4864237 No balance call IF function
73 fetch_active_assg_act returns 0 for
74 assignment action id. Also changed
75 cursors get_soc_sec_quota_details,
76 get_states_for_legal_emp and
77 get_gres_within_state for
78 performance.
79 25-Apr-2006 vpandya 115.16 5179412 Changed parameters in call XMLReport
80 Publisher(XDOREPPB). Switch value
81 between argument2 and 3. Passing
82 applicatio short name instead ID.
83 30-Jun-2006 vpandya 115.17 5236202 Changed parameters in call XMLReport
84 Publisher(XDOREPPB). Switch value
85 application id instead appl short
86 name for argument2 as per Jackie
87 XDO team.
88 30-May-2007 vpandya 115.18 5976541 Changed populate_plsql_table.
89 Fetching data in varchar variables
90 get_soc_sec_quota_details cursor
91 and it will be converted to number.
92 05-Jan-2009 sjawid 115.22 7565304 moved the calc logic at state level
93 state tax withheld shows only at
94 state AND Legal Employer level for
95 Queretaro. The state tax exemption
96 logic implemented at TRR level.
97 24-Apr-2009 vvijayk2 115.24 7660624 Increased the size of the variable
98 l_convert_data and l_xml_string to
99 VARCHAR2 (250).
100 03-Nov-2010 sjawid 115.26 10236983 Added code to calculate and show the
101 State tax Withheld for Campache with using
102 User table-STATE_TAX_CAMP(similar to the state
103 Chihuahua
104 27-Jun-2011 vvijayku 115.27 12684476 Added fnd_number.canonical_to_number in the
105 cursors get_states_for_legal_emp and
106 get_gres_within_state.
107 03-Aug-2012 sjawid 115.28 14395400 Added fnd_number.canonical_to_number to avoid conversion error
108 while calling the function hruserdt.get_table_value.
109 ****************************************************************************/
110
111 --
112 -- < PRIVATE GLOBALS > ---------------------------------------------------
113 --
114
115 g_package VARCHAR2(100) ;
116
117 -- flag to write the debug messages IN the concurrent program log file
118 g_concurrent_flag VARCHAR2(1) ;
119
120 -- flag to write the debug messages IN the trace file
121 g_debug_flag VARCHAR2(1) ;
122
123 /*****************************************************************************
124 Name : msg
125 Purpose : Log a message, either using fnd_file, or hr_utility.trace
126 *****************************************************************************/
127
128 PROCEDURE msg(p_text VARCHAR2)
129 IS
130 --
131 BEGIN
132 -- Write to the concurrent request log
133 fnd_file.put_line(fnd_file.log, p_text);
134
135 END msg;
136
137 /*****************************************************************************
138 Name : dbg
139 Purpose : Log a message, either using fnd_file, or hr_utility.trace
140 IF debuggging is enabled
141 *****************************************************************************/
142 PROCEDURE dbg(p_text VARCHAR2) IS
143
144 BEGIN
145
146 IF (g_debug_flag = 'Y') THEN
147
148 IF (g_concurrent_flag = 'Y') THEN
149
150 -- Write to the concurrent request log
151 fnd_file.put_line(fnd_file.log, p_text);
152
153 ELSE
154
155 -- Use HR trace
156 hr_utility.trace(p_text);
157
158 END IF;
159
160 END IF;
161
162 END dbg;
163
164
165
166 /*****************************************************************************
167 Name : fetch_define_bal
168 Purpose : Function to fetch the Defined Balance Id for a particular
169 Suffix like MTD,QTD,YTD
170 *****************************************************************************/
171 FUNCTION fetch_define_bal (p_bal_name IN VARCHAR2,
172 p_data_suffix IN VARCHAR2) RETURN NUMBER
173 IS
174
175 CURSOR get_def_bal( c_bal_name VARCHAR2
176 ,c_data_suffix VARCHAR2) IS
177 SELECT pdb.defined_balance_id def_bal
178 FROM pay_defined_balances pdb,
179 pay_balance_dimensions pbd,
180 pay_balance_types pbt
181 WHERE pbt.balance_type_id = pdb.balance_type_id
182 AND pbd.balance_dimension_id = pdb.balance_dimension_id
183 AND pbd.database_item_suffix = c_data_suffix
184 AND pbt.balance_name = c_bal_name
185 AND nvl(pdb.legislation_code,'MX') = 'MX';
186
187 l_return NUMBER;
188
189 BEGIN
190
191 OPEN get_def_bal(p_bal_name,p_data_suffix);
192 FETCH get_def_bal INTO l_return;
193 cLOSE get_def_bal;
194
195 RETURN (l_return);
196
197 END fetch_define_bal;
198
199 /*****************************************************************************
200 Name : fetch_active_assg_act
201 Purpose : Function to fetch Max Assignment Action id for a GRE
202 *****************************************************************************/
203 FUNCTION fetch_active_assg_act ( p_business_group_id IN NUMBER
204 ,p_tax_unit_id IN NUMBER
205 ,p_start_date_earned IN date
206 ,p_end_date_earned IN date )
207 RETURN NUMBER IS
208
209 CURSOR fetch_assg_act ( c_business_group_id IN NUMBER
210 ,c_tax_unit_id IN NUMBER
211 ,c_start_date_earned IN date
212 ,c_end_date_earned IN date ) IS
213 SELECT max(paa.assignment_action_id)
214 FROM pay_consolidation_sets pcs,
215 pay_payroll_actions ppa,
216 pay_assignment_actions paa
217 WHERE pcs.business_group_id = c_business_group_id
218 AND ppa.payroll_action_id = paa.payroll_action_id
219 AND ppa.consolidation_set_id = pcs.consolidation_set_id + 0
220 AND paa.tax_unit_id = c_tax_unit_id
221 AND ppa.business_group_id+0 = c_business_group_id
222 AND ppa.action_type IN ('R', 'Q', 'B', 'V', 'I')
223 AND paa.action_status = 'C'
224 AND ppa.effective_date BETWEEN c_start_date_earned
225 AND c_end_date_earned;
226
227 l_assg NUMBER(10);
228 BEGIN
229
230 l_assg := 0;
231
232 OPEN fetch_assg_act(p_business_group_id
233 ,p_tax_unit_id
234 ,p_start_date_earned
235 ,p_end_date_earned);
236 FETCH fetch_assg_act INTO l_assg;
237 CLOSE fetch_assg_act;
238
239 RETURN (l_assg);
240
241 END fetch_active_assg_act;
242
243 /*****************************************************************************
244 Name : get_yesno_value
245 Purpose : Function to get the lookup value
246 *****************************************************************************/
247 FUNCTION get_yesno_value (p_lookup_value IN VARCHAR2)
248 RETURN VARCHAR2 IS
249
250 CURSOR get_yesno(c_lookup_value IN VARCHAR2) IS
251 SELECT meaning
252 FROM hr_lookups
253 WHERE lookup_type = 'YES_NO'
254 AND lookup_code = c_lookup_value ;
255
256 l_meaning VARCHAR2(10);
257
258 BEGIN
259 l_meaning := '';
260 -- Commenting the below code because of Translational Issue refer Bug No:7353201
261 /*OPEN get_yesno(p_lookup_value) ;
262 FETCH get_yesno INTO l_meaning ;
263 CLOSE get_yesno ;*/
264
265 IF p_lookup_value = 'Y'
266 THEN l_meaning := 'Yes';
267 ELSE
268 l_meaning := 'No';
269 END IF;
270
271 RETURN l_meaning ;
272
273 END get_yesno_value;
274
275
276 /*****************************************************************************
277 Name : get_dimension_desc
278 Purpose : Function to get dimension description
279 *****************************************************************************/
280 FUNCTION get_dimension_desc (p_value IN VARCHAR2)
281 RETURN VARCHAR2 IS
282
283 CURSOR get_dim_desc(c_value IN VARCHAR2) IS
284 SELECT decode(c_value,'CTD', ffv.description,
285 substr( ffv.description,
286 instr(ffv.DESCRIPTION,'Period Date Range AND ')+
287 length('Period Date Range AND ') ))
288 FROM fnd_flex_values_vl ffv,
289 fnd_flex_value_sets ffs
290 WHERE ffs.flex_value_set_name = 'PAY_MX_TRR_DIMENSION'
291 AND ffv.flex_value_set_id = ffs.flex_value_set_id
292 AND ffv.flex_value = c_value ;
293
294 l_desc VARCHAR2(100);
295
296 BEGIN
297
298 l_desc :='';
299
300 OPEN get_dim_desc(p_value) ;
301 FETCH get_dim_desc INTO l_desc;
302 CLOSE get_dim_desc ;
303
304 RETURN l_desc ;
305
306 END get_dimension_desc;
307
308
309 /****************************************************************************
310 Name : insert_xml_plsql_table
311 Purpose : Procedure for inserting data intO the PL/SQL table
312 *****************************************************************************/
313 PROCEDURE insert_xml_plsql_table( p_xml_data IN OUT NOCOPY xml_data,
314 p_tag_name IN VARCHAR2,
315 p_tag_value IN VARCHAR2,
316 p_tag_type IN CHAR,
317 p_tag_value_type IN CHAR) IS
318 BEGIN
319 l_counter:= p_xml_data.count + 1 ;
320
321 p_xml_data(l_counter).tag_name := p_tag_name;
322
323 IF p_tag_value_type = 'P' THEN
324
325 p_xml_data(l_counter).tag_value := to_char(to_number(p_tag_value),
326 '99990.900');
327
328 ELSIF p_tag_value_type = 'B' THEN
329
330 p_xml_data(l_counter).tag_value := to_char(to_number(p_tag_value),
331 '9999990.90');
332
333 ELSE
334
335 p_xml_data(l_counter).tag_value := p_tag_value;
336
337 END IF;
338
339 p_xml_data(l_counter).tag_type := p_tag_type;
340
341 END insert_xml_plsql_table;
342
343
344
345 /*****************************************************************************
346 Name : populate_plsql_table
347 Purpose : Procedure to populate the balance data IN the plsql table
348 - Initialize state AND legal employer level totals
349 - Get defined balance Id for
350 Gross Earnings GRE_PYDATE
351
352
353 *****************************************************************************/
354 PROCEDURE populate_plsql_table
355 ( p_start_date_earned IN DATE,
356 p_end_date_earned IN DATE,
357 p_legal_employer_id IN NUMBER,
358 p_state_code IN VARCHAR2,
359 p_gre_id IN NUMBER,
360 p_show_isr IN VARCHAR2,
361 p_show_soc_security IN VARCHAR2,
362 p_show_state IN VARCHAR2,
363 p_dimension IN VARCHAR2,
364 p_business_group_id IN NUMBER,
365 p_xml_data IN OUT NOCOPY XML_DATA ) IS
366
367 -- CURSOR to get legal employer details
368 CURSOR get_legal_employer_details (p_legal_employer_id NUMBER) IS
369 SELECT hoi.org_information1 legal_employer_name,
370 hoi.org_information2 RFC_ID
371 FROM hr_organization_units hou,
372 hr_organization_information hoi
373 WHERE hoi.organization_id = hou.organization_id
374 AND hoi.org_information_context = 'MX_TAX_REGISTRATION'
375 AND hoi.organization_id = p_legal_employer_id ;
376
377 -- CURSOR to get gre details
378 CURSOR get_gre_details (p_gre_id NUMBER) IS
379 SELECT hou.name GRE_Name,
380 hoi.org_information1 ss_id
381 FROM hr_organization_units hou,
382 hr_organization_information hoi
383 WHERE hou.organization_id = hoi.organization_id
384 AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
385 AND hoi.organization_id = p_gre_id;
386
387 -- CURSOR to get state earnings details
388 CURSOR get_state_details (p_state VARCHAR2) IS
389 SELECT pay_ac_utility.get_balance_name(pbt.balance_type_id) balance_name
390 ,pay_ac_utility.get_bal_reporting_name(pbt.balance_type_id) rep_name
391 FROM pay_balance_types pbt,
392 pay_defined_balances pdb,
393 pay_balance_attributes pba,
394 pay_bal_attribute_definitions pbad
395 WHERE pbad.attribute_name LIKE 'Tax Remittance%'
396 AND pbad.business_group_id IS NULL
397 AND pbad.legislation_code = 'MX'
398 AND pba.attribute_id = pbad.attribute_id
399 AND pdb.defined_balance_id = pba.defined_balance_id
400 AND pdb.balance_type_id = pbt.balance_type_id
401 AND pdb.balance_type_id = pbt.balance_type_id
402 AND RTRIM(SUBSTR(pbad.attribute_name,
403 INSTR(pbad.attribute_name,'Tax Remittance for ')+
404 LENGTH('Tax Remittance for '))) =
405 NVL(p_state, RTRIM(substr(pbad.attribute_name,
406 INSTR(pbad.attribute_name,'Tax Remittance for ')+
407 LENGTH('Tax Remittance for '))));
408
409 -- CURSOR to get soc sec quota details
410 CURSOR get_soc_sec_quota_details (p_effective_date IN DATE) IS
411 SELECT pbt.balance_name,
412 pbt.reporting_name,
413 rtrim(substr(pbt.balance_name,1,
414 (length(pbt.balance_name)-3))) bal_name,
415 rtrim(substr(pbt.balance_name,
416 length(pbt.balance_name)-1)) ee_or_er,
417 pli.legislation_info4 soc_sec_tax_pct_ee,
418 pli.legislation_info5 soc_sec_tax_pct_er
419 FROM pay_balance_types pbt,
420 pay_defined_balances pdb,
421 pay_balance_attributes pba,
422 pay_bal_attribute_definitions pbad,
423 pay_mx_legislation_info_f pli,
424 fnd_lookup_values hrl
425 WHERE pbad.attribute_name LIKE 'Social Security Quota%'
426 AND pbad.business_group_id IS NULL
427 AND pbad.legislation_code = 'MX'
428 AND pbt.business_group_id IS NULL
429 AND pbt.legislation_code = 'MX'
430 AND pba.attribute_id = pbad.attribute_id
431 AND pdb.defined_balance_id = pba.defined_balance_id
432 AND pdb.balance_type_id = pbt.balance_type_id
433 AND hrl.lookup_type = 'MX_INSURANCE_TYPES'
434 AND hrl.language = 'US'
435 AND pli.legislation_info_type = 'MX Social Security Information'
436 AND pli.legislation_info1 = hrl.lookup_code
437 AND hrl.meaning = substr(pbt.balance_name, 1,
438 (length(pbt.balance_name)-3))
439 AND p_effective_date BETWEEN pli.effective_start_date
440 AND pli.effective_end_date
441 AND p_effective_date BETWEEN hrl.start_date_active
442 AND nvl(hrl.end_date_active,
443 TO_DATE('12/31/4712', 'MM/DD/YYYY'))
444 ORDER BY to_number(pli.legislation_info6);
445
446 -- Cursor to fetch the distinct States with IN a Legal Employer
447 CURSOR get_states_for_legal_emp ( p_business_group_id IN NUMBER,
448 p_legal_employer_id IN NUMBER,
449 p_state_code IN VARCHAR2,
450 p_gre_id IN NUMBER) IS
451 SELECT DISTINCT pmx.state state_code,
452 pmx.state_name state_name
453 FROM per_mx_gres_legal_employer_v pmx
454 WHERE pmx.business_group_id = p_business_group_id
455 AND fnd_number.canonical_to_number(pmx.legal_employer_id) = fnd_number.canonical_to_number(p_legal_employer_id)
456 --AND pmx.legal_employer_id = p_legal_employer_id
457 AND (( p_state_code IS NULL ) OR ( p_state_code IS NOT NULL and
458 p_state_code = pmx.state))
459 AND (( p_gre_id IS NULL ) OR ( p_gre_id IS NOT NULL and
460 fnd_number.canonical_to_number(p_gre_id) = fnd_number.canonical_to_number(pmx.tax_unit_id)));
461
462 -- CURSOR to get gres within state
463 CURSOR get_gres_within_state ( p_business_group_id IN NUMBER,
464 p_legal_employer_id IN NUMBER,
465 p_gre_id IN NUMBER,
466 p_state IN VARCHAR2) IS
467 SELECT pmx.tax_unit_id tax_unit_id,
468 pmx.gre_name gre_name,
469 pmx.ss_id
470 FROM per_mx_gres_legal_employer_v pmx
471 WHERE pmx.business_group_id = p_business_group_id
472 AND fnd_number.canonical_to_number(pmx.legal_employer_id) = fnd_number.canonical_to_number(p_legal_employer_id)
473 AND (( p_state IS NULL ) OR ( p_state IS NOT NULL and
474 p_state = pmx.state))
475 AND (( p_gre_id IS NULL ) OR ( p_gre_id IS NOT NULL and
476 fnd_number.canonical_to_number(p_gre_id) = fnd_number.canonical_to_number(pmx.tax_unit_id)));
477
478 -- CURSOR to get state name
479 CURSOR get_st_name (p_state_code IN VARCHAR2) IS
480 SELECT fcl.meaning
481 FROM fnd_common_lookups fcl
482 WHERE fcl.lookup_type='MX_STATE'
483 AND fcl.lookup_code=p_state_code ;
484
485
486 TYPE soc_sec_detail_tab IS RECORD ( balance_name VARCHAR2(50),
487 soc_sec_tax_pct_ee NUMBER,
488 soc_sec_tax_pct_er NUMBER,
489 soc_sec_ee_ctd_id NUMBER,
490 soc_sec_ee_dtd_id NUMBER,
491 soc_sec_er_ctd_id NUMBER,
492 soc_sec_er_dtd_id NUMBER,
493 soc_sec_quo_ee_ctd NUMBER,
494 soc_sec_quo_ee_dtd NUMBER,
495 soc_sec_quo_er_ctd NUMBER,
496 soc_sec_quo_er_dtd NUMBER);
497
498 TYPE tot_soc_sec_detail_tab IS RECORD ( balance_name VARCHAR2(50),
499 soc_sec_quo_ee_ctd NUMBER,
500 soc_sec_quo_ee_dtd NUMBER,
501 soc_sec_quo_er_ctd NUMBER,
502 soc_sec_quo_er_dtd NUMBER);
503
504 TYPE state_earnings_detail_tab IS RECORD (
505 balance_name VARCHAR2(50),
506 state_earnings_ctd_id NUMBER,
507 state_earnings_dtd_id NUMBER);
508
509
510 TYPE tot_state_earnings_detail_tab IS RECORD (
511 balance_name VARCHAR2(50),
512 state_earnings_ctd_value NUMBER,
513 state_earnings_dtd_value NUMBER);
514
515
516 TYPE soc_sec_detail IS TABLE OF soc_sec_detail_tab
517 INDEX BY BINARY_INTEGER;
518
519 TYPE tot_soc_sec_detail IS TABLE OF tot_soc_sec_detail_tab
520 INDEX BY BINARY_INTEGER;
521
522 TYPE state_earnings_detail IS TABLE OF state_earnings_detail_tab
523 INDEX BY BINARY_INTEGEr;
524
525 TYPE tot_state_earnings_detail IS TABLE OF tot_state_earnings_detail_tab
526 INDEX BY BINARY_INTEGER;
527
528
529 soc_sec_det_tab soc_sec_detail;
530
531 st_soc_sec_det_tab tot_soc_sec_detail;
532
533 le_soc_sec_det_tab tot_soc_sec_detail;
534
535 state_earnings state_earnings_detail ;
536 st_state_earnings tot_state_earnings_detail ;
537
538
539 xml_total_tab xml_data;
540 l_counter1 NUMBER;
541 l_legal_employer_name VARCHAR2(100);
542 l_RFC_code VARCHAR2(50);
543 l_gre_param VARCHAR2(50);
544 l_gre_name VARCHAR2(100);
545 l_ss_id VARCHAR2(20);
546 l_gre VARCHAR2(100);
547 l_def_bal NUMBER(9);
548 l_prev_state VARCHAR2(50);
549 l_prev_gre VARCHAR2(50);
550 l_state VARCHAR2(5);
551 l_state_name VARCHAR2(50);
552 l_st_name VARCHAR2(50);
553 l_tax_unit_id NUMBER(10);
554 l_ee_or_er VARCHAR2(20);
555 l_soc_sec_tax_per_ee NUMBER;
556 l_soc_sec_tax_per_er NUMBER;
557 l_bal_name VARCHAR2(100);
558 l_balance_name VARCHAR2(100);
559 l_prev_bal_name VARCHAR2(100);
560 lv_soc_sec_tax_per_ee VARCHAR2(100);
561 lv_soc_sec_tax_per_er VARCHAR2(100);
562
563 l_report_name VARCHAR2(100);
564 l_bal_id NUMBER(9);
565 l_state_heading VARCHAR2(100);
566 l_exit_status NUMBER(1);
567
568 /* Variables to store the State Total Gross Earning values */
569 l_gross_subj_ctd NUMBER;
570 l_gross_subj_dtd NUMBER;
571 l_st_gross_sub_ctd NUMBER;
572 l_st_gross_sub_dtd NUMBER;
573
574 /* Variables to store the State Total For ISR values */
575 l_ctd_db_item_suffix VARCHAR2(10);
576 l_database_suffix VARCHAR2(20);
577 l_dim_database_item_suffix VARCHAR2(20);
578 l_isr_witheld_ctd NUMBER;
579 l_isr_witheld_dtd NUMBER;
580 l_isr_subj_ctd NUMBER;
581 l_isr_subj_dtd NUMBER;
582
583 l_st_isr_witheld_ctd NUMBER;
584 l_st_isr_witheld_dtd NUMBER;
585 l_st_isr_subj_ctd NUMBER;
586 l_st_isr_subj_dtd NUMBER;
587
588 /* Variables to store the Social Security Values */
589
590 l_ss_ee_ctd NUMBER ;
591 l_ss_er_ctd NUMBER ;
592 l_ss_ee_dtd NUMBER ;
593 l_ss_er_dtd NUMBER ;
594
595 l_soc_sec_ee_ctd NUMBER;
596 l_soc_sec_ee_dtd NUMBER;
597 l_soc_sec_er_ctd NUMBER;
598 l_soc_sec_er_dtd NUMBER;
599 l_soc_sec_tot_ctd NUMBER;
600 l_soc_sec_tot_dtd NUMBER;
601
602 l_st_soc_sec_ee_ctd NUMBER;
603 l_st_soc_sec_ee_dtd NUMBER;
604 l_st_soc_sec_er_ctd NUMBER;
605 l_st_soc_sec_er_dtd NUMBER;
606 l_st_soc_sec_tot_ctd NUMBER;
607 l_st_soc_sec_tot_dtd NUMBER;
608
609 /* Variables to store the State Tax values */
610
611 l_st_witheld_ctd NUMBER;
612 l_st_subj_ctd NUMBER;
613 l_st_witheld_dtd NUMBER;
614 l_st_subj_dtd NUMBER;
615
616 l_st_st_witheld_ctd NUMBER;
617 l_st_st_witheld_dtd NUMBER;
618 l_st_st_subj_ctd NUMBER;
619 l_st_st_subj_dtd NUMBER;
620
621 /* Variables to store the State Chihuahua/campache rates*/
622
623 l_fixed_rate NUMBER;
624 l_marginal_rate NUMBER;
625 l_lower_bound NUMBER;
626 l_table_name pay_user_tables.USER_TABLE_NAME%type;
627
628 /* Variables to store the State Queretaro Values*/
629 l_rate NUMBER;
630 lv_rate VARCHAR2(20);
631 l_dummy NUMBER;
632 l_leg_info CHAR(1);
633 l_min_wage NUMBER;
634 l_rate_type VARCHAR2(20);
635 l_st_st_exemption NUMBER;
636
637 /* Variables to store the State Earnings Values */
638 l_state_earning_name VARCHAR2(100);
639 l_st_earn_ctd_def_bal_id NUMBER;
640 l_st_earn_dtd_def_bal_id NUMBER;
641
642 l_st_earn_ctd_value NUMBER;
643 l_st_earn_dtd_value NUMBER;
644
645
646 /* Variables to store the Social Security Quota Values */
647 l_ins_type_ctd_id NUMBER(10);
648 l_ins_type_dtd_id NUMBER(10);
649 l_soc_sec_quo_ee_ctd NUMBER;
650 l_soc_sec_quo_ee_dtd NUMBER;
651 l_soc_sec_quo_er_ctd NUMBER;
652 l_soc_sec_quo_er_dtd NUMBER;
653
654 l_st_soc_sec_quo_ee_ctd NUMBER;
655 l_st_soc_sec_quo_ee_dtd NUMBER;
656 l_st_soc_sec_quo_er_ctd NUMBER;
657 l_st_soc_sec_quo_er_dtd NUMBER;
658
659 /* Variables to store Legal Employer Values */
660 l_lt_gross_sub_ctd NUMBER;
661 l_lt_gross_sub_dtd NUMBER;
662
663 l_lt_isr_witheld_ctd NUMBER;
664 l_lt_isr_witheld_dtd NUMBER;
665 l_lt_isr_subj_ctd NUMBER;
666 l_lt_isr_subj_dtd NUMBER;
667
668 l_lt_soc_sec_ee_ctd NUMBER;
669 l_lt_soc_sec_ee_dtd NUMBER;
670 l_lt_soc_sec_er_ctd NUMBER;
671 l_lt_soc_sec_er_dtd NUMBER;
672 l_lt_soc_sec_tot_ctd NUMBER;
673 l_lt_soc_sec_tot_dtd NUMBER;
674
675 l_lt_st_witheld_ctd NUMBER;
676 l_lt_st_witheld_dtd NUMBER;
677 l_lt_st_subj_ctd NUMBER;
678 l_lt_st_subj_dtd NUMBER;
679
680 l_lt_soc_sec_tax_per_ee NUMBER;
681 l_lt_soc_sec_tax_per_er NUMBER;
682 l_lt_soc_sec_quo_ee_ctd NUMBER;
683 l_lt_soc_sec_quo_ee_dtd NUMBER;
684 l_lt_soc_sec_quo_er_ctd NUMBER;
685 l_lt_soc_sec_quo_er_dtd NUMBER;
686
687
688 l_procedure_name VARCHAR2(100);
689 l_error_message VARCHAR2(200);
690 l_step NUMBER;
691
692 l_gross_ctd_def_bal_id NUMBER;
693 l_gross_dtd_def_bal_id NUMBER;
694
695 l_isr_withheld_ctd_def_bal_id NUMBER;
696 l_isr_subj_ctd_def_bal_id NUMBER;
697 l_isr_withheld_dtd_def_bal_id NUMBER;
698 l_isr_subj_dtd_def_bal_id NUMBER;
699
700 l_st_withheld_ctd_def_bal_id NUMBER;
701 l_st_subj_ctd_def_bal_id NUMBER;
702 l_st_withheld_dtd_def_bal_id NUMBER;
703 l_st_subj_dtd_def_bal_id NUMBER;
704
705
706 l_ss_ee_ctd_def_bal_id NUMBER;
707 l_ss_er_ctd_def_bal_id NUMBER;
708 l_ss_ee_dtd_def_bal_id NUMBER;
709 l_ss_er_dtd_def_bal_id NUMBER;
710
711 l_assignment_act NUMBER;
712 l_show VARCHAR2(10);
713 i NUMBER;
714
715 BEGIN
716
717 l_procedure_name := '.populate_plsql_table';
718
719 dbg('Entering Populate plsql Table .........');
720 dbg('Initializing Local variables');
721
722 l_counter := 0;
723 l_counter1 := 0;
724 l_exit_status := 0;
725
726 l_prev_bal_name := null;
727
728 l_st_soc_sec_quo_ee_ctd := 0;
729 l_st_soc_sec_quo_ee_dtd := 0;
730 l_st_soc_sec_quo_er_ctd := 0;
731 l_st_soc_sec_quo_er_dtd := 0;
732
733 /* Initializing the Legal Employer Variables */
734
735 l_lt_gross_sub_ctd := 0;
736 l_lt_gross_sub_dtd := 0;
737 l_lt_isr_witheld_ctd := 0;
738 l_lt_isr_witheld_dtd := 0;
739 l_lt_isr_subj_ctd := 0;
740 l_lt_isr_subj_dtd := 0;
741
742 l_lt_soc_sec_ee_ctd := 0;
743 l_lt_soc_sec_ee_dtd := 0;
744 l_lt_soc_sec_er_ctd := 0;
745 l_lt_soc_sec_er_dtd := 0;
746 l_lt_soc_sec_tot_ctd := 0;
747 l_lt_soc_sec_tot_dtd := 0;
748
749 l_lt_st_witheld_ctd := 0;
750 l_lt_st_witheld_dtd := 0;
751 l_lt_st_subj_ctd := 0;
752 l_lt_st_subj_dtd := 0;
753
754 -- database item suffix for CTD is
755
756 l_database_suffix := '_GRE_PYDATE';
757
758 -- From the parameter p_dimension get the dimension database_item_suffix
759
760 IF p_dimension <> 'CTD' THEN
761
762 IF p_dimension = 'MONTH' THEN
763
764 l_dim_database_item_suffix := '_GRE_MTD';
765
766 ELSIF p_dimension = 'QTD' THEN
767
768 l_dim_database_item_suffix := '_GRE_QTD';
769
770 ELSIF p_dimension ='YTD' THEN
771
772 l_dim_database_item_suffix := '_GRE_YTD';
773
774 END IF;
775
776 ELSE
777
778 l_dim_database_item_suffix := null ;
779
780 END IF;
781
782 dbg('Get the Defined balance Ids');
783 l_step := 1;
784 hr_utility.set_location(g_package || l_procedure_name, 10);
785
786 -- Get the defined balance Id for Gross Earnings AND GRE_PYDATE
787
788 l_gross_ctd_def_bal_id := fetch_define_bal ('Gross Earnings',
789 l_database_suffix );
790
791 dbg('Gross Earnings '|| l_database_suffix || ' : '||l_gross_ctd_def_bal_id);
792 l_step := 2;
793 hr_utility.set_location(g_package || l_procedure_name, 20);
794
795 -- Get the defined balance Id for Gross Earnings AND dimension
796
797 l_gross_dtd_def_bal_id := NULL ;
798
799 IF l_dim_database_item_suffix IS NOT NULL THEN
800
801 l_gross_dtd_def_bal_id := fetch_define_bal ('Gross Earnings',
802 l_dim_database_item_suffix );
803
804 END IF;
805
806 dbg('Gross Earnings ' || l_dim_database_item_suffix || ' : '||
807 l_gross_dtd_def_bal_id);
808
809
810 IF p_show_isr = 'Y' THEN
811
812 l_step := 3;
813 hr_utility.set_location(g_package || l_procedure_name, 30);
814
815 -- Get defined balance Id for ISR WIthheld AND ISR Subject
816
817 l_isr_withheld_ctd_def_bal_id := fetch_define_bal ('ISR Withheld',
818 l_database_suffix );
819
820 l_isr_subj_ctd_def_bal_id := fetch_define_bal ('ISR Subject',
821 l_database_suffix );
822
823 l_isr_withheld_dtd_def_bal_id := NULL ;
824 l_isr_subj_dtd_def_bal_id := NULL ;
825
826 IF l_dim_database_item_suffix is not null THEN
827
828 l_step := 4;
829 hr_utility.set_location(g_package || l_procedure_name, 40);
830
831 l_isr_withheld_dtd_def_bal_id := fetch_define_bal ('ISR Withheld',
832 l_dim_database_item_suffix );
833 l_isr_subj_dtd_def_bal_id := fetch_define_bal ('ISR Subject',
834 l_dim_database_item_suffix );
835
836 END IF;
837
838 END IF;
839
840 dbg('ISR Withheld ' || l_database_suffix || ' : '||
841 l_isr_withheld_ctd_def_bal_id);
842 dbg('ISR Subject ' || l_database_suffix || ' : '||
843 l_isr_subj_ctd_def_bal_id);
844 dbg('ISR Withheld ' || l_dim_database_item_suffix || ' : '||
845 l_isr_withheld_dtd_def_bal_id);
846 dbg('ISR Subject ' || l_dim_database_item_suffix || ' : '||
847 l_isr_subj_dtd_def_bal_id);
848
849 IF p_show_soc_security = 'Y' THEN
850
851 -- Get defined balance Id for Social Security Quota EE
852 -- AND Social Security Quota ER
853
854 l_step := 5;
855 hr_utility.set_location(g_package || l_procedure_name, 50);
856
857 l_ss_ee_ctd_def_bal_id := fetch_define_bal ('Social Security Quota EE',
858 l_database_suffix );
859 l_ss_er_ctd_def_bal_id := fetch_define_bal ('Social Security Quota ER',
860 l_database_suffix );
861
862 l_ss_ee_dtd_def_bal_id := NULL;
863 l_ss_er_dtd_def_bal_id := NULL;
864
865 IF l_dim_database_item_suffix is not null THEN
866 l_step := 6;
867 hr_utility.set_location(g_package || l_procedure_name, 60);
868
869 l_ss_ee_dtd_def_bal_id := fetch_define_bal('Social Security Quota EE'
870 ,l_dim_database_item_suffix );
871 l_ss_er_dtd_def_bal_id := fetch_define_bal('Social Security Quota ER'
872 ,l_dim_database_item_suffix );
873 END IF;
874
875 dbg('Social Security Quota EE ' || l_database_suffix || ' : ' ||
876 l_ss_ee_ctd_def_bal_id);
877 dbg('Social Security Quota ER ' || l_database_suffix || ' : ' ||
878 l_ss_er_ctd_def_bal_id);
879 dbg('Social Security Quota EE ' || l_dim_database_item_suffix || ' : ' ||
880 l_ss_ee_dtd_def_bal_id);
881 dbg('Social Security Quota ER ' || l_dim_database_item_suffix || ' : ' ||
882 l_ss_er_dtd_def_bal_id);
883
884
885 dbg('Get Social Security Insurance Types ');
886 l_step := 7;
887 hr_utility.set_location(g_package || l_procedure_name, 70);
888
889 -- get the social security ins types
890
891 OPEN get_soc_sec_quota_details(p_start_date_earned);
892
893 LOOP
894
895 hr_utility.trace('IN LOOP...');
896
897 FETCH get_soc_sec_quota_details INTO
898 l_bal_name, -- with EE or ER at the end
899 l_report_name, -- same as l_bal_name
900 l_balance_name, -- without EE or ER
901 l_ee_or_er,
902 lv_soc_sec_tax_per_ee,
903 lv_soc_sec_tax_per_er;
904
905 EXIT WHEN get_soc_sec_quota_details%notfound;
906
907 hr_utility.trace('l_prev_bal_name: ' ||nvl(l_prev_bal_name,'NULL'));
908 hr_utility.trace('l_balance_name: ' ||l_balance_name);
909 hr_utility.trace('lv_soc_sec_tax_per_ee: ' ||lv_soc_sec_tax_per_ee);
910 hr_utility.trace('lv_soc_sec_tax_per_er: ' ||lv_soc_sec_tax_per_er);
911
912 l_soc_sec_tax_per_ee :=
913 fnd_number.canonical_to_number(lv_soc_sec_tax_per_ee);
914
915 hr_utility.trace('l_soc_sec_tax_per_ee: ' ||l_soc_sec_tax_per_ee);
916
917 l_soc_sec_tax_per_er :=
918 fnd_number.canonical_to_number(lv_soc_sec_tax_per_er);
919
920 hr_utility.trace('l_soc_sec_tax_per_er: ' ||l_soc_sec_tax_per_er);
921
922 IF ( l_prev_bal_name IS NULL OR l_prev_bal_name <> l_balance_name )
923 THEN
924
925 l_counter1:= l_counter1+ 1;
926
927 hr_utility.trace('l_counter1: ' ||l_counter1);
928 dbg('The Counter value '||l_counter1);
929 dbg('The Balance Name '||l_balance_name);
930 dbg('The Balance Name '||l_bal_name);
931
932 hr_utility.trace('l_bal_name: ' ||l_bal_name);
933
934 soc_sec_det_tab(l_counter1).balance_name := l_balance_name;
935 soc_sec_det_tab(l_counter1).soc_sec_tax_pct_ee :=
936 l_soc_sec_tax_per_ee;
937 soc_sec_det_tab(l_counter1).soc_sec_tax_pct_er :=
938 l_soc_sec_tax_per_er;
939
940 l_prev_bal_name := l_balance_name;
941
942 END IF;
943
944 IF l_prev_bal_name = l_balance_name THEN
945
946 hr_utility.trace('Balances are same: ');
947 hr_utility.trace('l_bal_name: '||l_bal_name);
948 hr_utility.trace('l_database_suffix: '||l_database_suffix);
949
950 l_ins_type_ctd_id := fetch_define_bal (l_bal_name,
951 l_database_suffix );
952
953 hr_utility.trace('l_ins_type_ctd_id: '||
954 l_ins_type_ctd_id);
955
956 hr_utility.trace('l_dim_database_item_suffix: '||
957 l_dim_database_item_suffix);
958
959 IF l_dim_database_item_suffix IS NOT NULL THEN
960
961 l_ins_type_dtd_id := fetch_define_bal (l_bal_name,
962 l_dim_database_item_suffix);
963 hr_utility.trace('l_ins_type_dtd_id: '||
964 l_ins_type_dtd_id);
965 END IF;
966
967 hr_utility.trace('l_ee_or_er: '||l_ee_or_er);
968
969 IF l_ee_or_er ='EE' THEN
970
971 soc_sec_det_tab(l_counter1).soc_sec_ee_ctd_id :=
972 l_ins_type_ctd_id;
973
974 dbg('soc_sec_det_tab(l_counter1).soc_sec_ee_ctd_id: '||
975 soc_sec_det_tab(l_counter1).soc_sec_ee_ctd_id);
976
977 soc_sec_det_tab(l_counter1).soc_sec_ee_dtd_id :=
978 l_ins_type_dtd_id;
979
980 dbg('soc_sec_det_tab(l_counter1).soc_sec_ee_dtd_id: '||
981 soc_sec_det_tab(l_counter1).soc_sec_ee_dtd_id);
982
983 ELSE
984
985 soc_sec_det_tab(l_counter1).soc_sec_er_ctd_id :=
986 l_ins_type_ctd_id;
987
988 dbg('Else soc_sec_det_tab(l_counter1).soc_sec_er_ctd_id: '||
989 soc_sec_det_tab(l_counter1).soc_sec_er_ctd_id);
990
991 soc_sec_det_tab(l_counter1).soc_sec_er_dtd_id :=
992 l_ins_type_dtd_id;
993
994 dbg('Else soc_sec_det_tab(l_counter1).soc_sec_er_dtd_id: '||
995 soc_sec_det_tab(l_counter1).soc_sec_er_dtd_id);
996
997 END IF;
998
999 END IF;
1000
1001 END LOOP;
1002
1003 dbg('The value of the PLSQl table counter '||l_counter1);
1004 l_step := 8;
1005 hr_utility.set_location(g_package || l_procedure_name, 80);
1006
1007 FOR m IN 1 ..soc_sec_det_tab.count LOOP
1008
1009 dbg(soc_sec_det_tab(m).balance_name ||' '||
1010 soc_sec_det_tab(m).soc_sec_tax_pct_ee||' '||
1011 soc_sec_det_tab(m).soc_sec_tax_pct_er);
1012
1013 dbg(' EE CTD id ' || soc_sec_det_tab(m).soc_sec_ee_ctd_id ||' '||
1014 ' ER CTD id ' || soc_sec_det_tab(m).soc_sec_er_ctd_id ||' '||
1015 ' EE DTD id ' || soc_sec_det_tab(m).soc_sec_ee_dtd_id ||' '||
1016 ' ER DTD id ' || soc_sec_det_tab(m).soc_sec_er_dtd_id );
1017
1018 END LOOP;
1019
1020
1021 END IF; -- p_show_soc_security
1022
1023 IF p_show_state = 'Y' THEN
1024
1025 hr_utility.set_location(g_package || l_procedure_name, 85);
1026
1027 -- Get defined balance Id for Employer State Tax WIthheld AND
1028 -- Employer State Tax Subject
1029
1030 l_st_withheld_ctd_def_bal_id :=
1031 fetch_define_bal ('Employer State Tax Withheld',l_database_suffix );
1032 l_st_subj_ctd_def_bal_id :=
1033 fetch_define_bal ('Employer State Tax Subject' ,l_database_suffix );
1034
1035 l_st_withheld_dtd_def_bal_id := NULL;
1036 l_st_subj_dtd_def_bal_id := NULL;
1037
1038 IF l_dim_database_item_suffix IS NOT NULL THEN
1039
1040 l_st_withheld_dtd_def_bal_id :=
1041 fetch_define_bal('Employer State Tax Withheld',
1042 l_dim_database_item_suffix );
1043 l_st_subj_dtd_def_bal_id :=
1044 fetch_define_bal('Employer State Tax Subject' ,
1045 l_dim_database_item_suffix );
1046
1047 END IF;
1048
1049 dbg('Employer State Tax Withheld ' || l_database_suffix || ' : '||
1050 l_st_withheld_ctd_def_bal_id);
1051 dbg('Employer State Tax Subject ' || l_database_suffix || ' : '||
1052 l_st_subj_ctd_def_bal_id);
1053 dbg('Employer State Tax Withheld ' || l_dim_database_item_suffix || ' : ' || l_st_withheld_dtd_def_bal_id);
1054 dbg('Employer State Tax Subject ' || l_dim_database_item_suffix || ' : ' || l_st_subj_dtd_def_bal_id);
1055
1056
1057 END IF; -- p_show_state
1058
1059 l_step := 9;
1060 hr_utility.set_location(g_package || l_procedure_name, 90);
1061
1062 dbg('Get Legal Employer Name ');
1063
1064 OPEN get_legal_employer_details(p_legal_employer_id);
1065 FETCH get_legal_employer_details INTO l_legal_employer_name,l_RFC_code ;
1066 CLOSE get_legal_employer_details;
1067
1068 dbg('Legal Employer Name : '||l_legal_employer_name);
1069 dbg('Legal Employer Id : '||p_legal_employer_id);
1070 dbg('RFC Id : '||l_RFC_code);
1071
1072 IF p_gre_id IS NOT NULL THEN
1073
1074 OPEN get_gre_details(p_gre_id);
1075 FETCH get_gre_details INTO l_gre_param,l_ss_id;
1076 CLOSE get_gre_details;
1077
1078 ELSE
1079
1080 dbg('The Gre value is all');
1081 l_gre_param :='All';
1082
1083 END IF;
1084
1085 IF p_state_code IS NOT NULL THEN
1086
1087 OPEN get_st_name(p_state_code);
1088 FETCH get_st_name INTO l_st_name;
1089 CLOSE get_st_name;
1090
1091 ELSE
1092
1093 dbg('The State value is All');
1094 l_st_name :='All';
1095
1096 END IF;
1097
1098 dbg('GRE Name : '||l_gre_param);
1099 dbg('GRE Id : '||p_gre_id);
1100 dbg('SS Id : '||l_ss_id);
1101 dbg('State : '||l_st_name);
1102
1103 /* Initialize pl/sql table */
1104 insert_xml_plsql_table( p_xml_data,'TRR',NULL,'T','C');
1105
1106 dbg('Insert parameters to plsql table ');
1107
1108 l_step := 10;
1109 hr_utility.set_location(g_package || l_procedure_name, 100);
1110
1111 /* insert parameter records IN pl/sql table */
1112
1113 insert_xml_plsql_table( p_xml_data,'START_DATE_EARNED',
1114 to_char(p_start_date_earned,'DD-MON-YYYY'),'D','C');
1115 insert_xml_plsql_table( p_xml_data,'END_DATE_EARNED',
1116 to_char(p_end_date_earned,'DD-MON-YYYY'),'D','C');
1117 insert_xml_plsql_table( p_xml_data,'LEGAL_EMPLOYER_NAME',
1118 l_legal_employer_name,'D','C');
1119 insert_xml_plsql_table( p_xml_data,'GRE_PARAMETER',l_gre_param,'D','C');
1120 insert_xml_plsql_table( p_xml_data,'STATE_PARAMETER',l_st_name,'D','C');
1121 insert_xml_plsql_table( p_xml_data,'SHOW_FEDERAL',
1122 get_yesno_value(p_show_isr),'D','C');
1123 insert_xml_plsql_table( p_xml_data,'SHOW_SOCIAL_SECURITY',
1124 get_yesno_value(p_show_soc_security),'D','C');
1125 insert_xml_plsql_table( p_xml_data,'SHOW_STATE',
1126 get_yesno_value(p_show_state),'D','C');
1127 insert_xml_plsql_table( p_xml_data,'DIMENSION',
1128 get_dimension_desc(p_dimension),'D','C');
1129 insert_xml_plsql_table(p_xml_data,'RFC_ID',l_RFC_code,'D','C');
1130
1131 IF p_dimension = 'CTD' THEN
1132
1133 insert_xml_plsql_table( p_xml_data,'SHOW_DIMENSION','No','D','C');
1134
1135 ELSE
1136
1137 insert_xml_plsql_table( p_xml_data,'SHOW_DIMENSION','Yes','D','C');
1138
1139 END IF;
1140
1141 l_step := 11;
1142 hr_utility.set_location(g_package || l_procedure_name, 110);
1143
1144 FOR l_cnt1 IN get_states_for_legal_emp(p_business_group_id,
1145 p_legal_employer_id,
1146 p_state_code,p_gre_id)
1147 LOOP
1148
1149 l_state := l_cnt1.state_code;
1150 l_state_name := l_cnt1.state_name;
1151
1152 dbg('Processing State : '||l_state ||' ' ||l_state_name);
1153
1154 -- Initialize state level balances
1155 l_st_gross_sub_ctd := 0;
1156 l_st_gross_sub_dtd := 0;
1157
1158 l_st_isr_witheld_ctd := 0;
1159 l_st_isr_witheld_dtd := 0;
1160 l_st_isr_subj_ctd := 0;
1161 l_st_isr_subj_dtd := 0;
1162
1163 l_st_st_witheld_ctd := 0;
1164 l_st_st_witheld_dtd := 0;
1165 l_st_st_subj_ctd := 0;
1166 l_st_st_subj_dtd := 0;
1167
1168 l_st_soc_sec_ee_ctd := 0;
1169 l_st_soc_sec_ee_dtd := 0;
1170 l_st_soc_sec_er_ctd := 0;
1171 l_st_soc_sec_er_dtd := 0;
1172 l_st_soc_sec_tot_ctd := 0;
1173 l_st_soc_sec_tot_dtd := 0;
1174
1175 insert_xml_plsql_table( p_xml_data,'STATE',NULL,'T','C');
1176 insert_xml_plsql_table( p_xml_data,'STATE_NAME',l_state_name,'D','C');
1177
1178 --
1179 -- get state specific earning details AND get the ctd and
1180 -- dtd defined balance id
1181 --
1182
1183 IF p_show_state = 'Y' THEN
1184
1185 -- initialize the plsql table
1186 state_earnings.delete ;
1187
1188 i := 0 ;
1189 FOR l_cnt10 IN get_state_details(l_state_name)
1190 LOOP
1191
1192 l_state_earning_name := l_cnt10.balance_name ;
1193 l_st_earn_ctd_def_bal_id := fetch_define_bal(l_state_earning_name,
1194 l_database_suffix );
1195 l_st_earn_dtd_def_bal_id := null ;
1196
1197 IF l_dim_database_item_suffix IS NOT NULL THEN
1198 l_st_earn_dtd_def_bal_id :=
1199 fetch_define_bal(l_state_earning_name,
1200 l_dim_database_item_suffix);
1201 END IF ;
1202
1203 i := i + 1 ;
1204 state_earnings(i).balance_name :=
1205 l_cnt10.balance_name;
1206 state_earnings(i).state_earnings_ctd_id :=
1207 l_st_earn_ctd_def_bal_id;
1208 state_earnings(i).state_earnings_dtd_id :=
1209 l_st_earn_dtd_def_bal_id;
1210
1211 dbg( 'State Earning :' || state_earnings(i).balance_name );
1212 dbg( 'ctd def bal id :' || l_st_earn_ctd_def_bal_id );
1213 dbg( 'dtd def bal id :' || l_st_earn_dtd_def_bal_id );
1214
1215 END LOOP ;
1216
1217 END IF ;
1218
1219 -- state_earnings records exists THEN set to Yes otherwise No
1220 -- This will be used IN the template to print the
1221 -- state_earnings details or not
1222
1223 IF state_earnings.count > 0 THEN
1224 insert_xml_plsql_table( p_xml_data,'SHOW_STATE_DETAILS_FLAG',
1225 'Yes','D','C');
1226 ELSE
1227 insert_xml_plsql_table( p_xml_data,'SHOW_STATE_DETAILS_FLAG',
1228 'No','D','C');
1229 END IF;
1230
1231 FOR l_cnt2 IN get_gres_within_state (p_business_group_id
1232 ,p_legal_employer_id
1233 ,p_gre_id
1234 ,l_state)
1235 LOOP
1236
1237 BEGIN
1238
1239 l_tax_unit_id := l_cnt2.tax_unit_id;
1240 l_gre_name := l_cnt2.gre_name;
1241 l_ss_id := l_cnt2.ss_id ;
1242
1243 dbg('Processing GRE '||l_gre_name ||' Tax Unit Id '||
1244 l_tax_unit_id || ' IN state '||l_state);
1245
1246 -- Initialize GRE level balances
1247 l_gross_subj_ctd := 0;
1248 l_gross_subj_dtd := 0;
1249
1250 l_isr_witheld_ctd := 0;
1251 l_isr_witheld_dtd := 0;
1252 l_isr_subj_ctd := 0;
1253 l_isr_subj_dtd := 0;
1254
1255 l_st_witheld_ctd := 0;
1256 l_st_witheld_dtd := 0;
1257 l_st_subj_ctd := 0;
1258 l_st_subj_dtd := 0;
1259
1260 l_soc_sec_ee_ctd := 0;
1261 l_soc_sec_ee_dtd := 0;
1262 l_soc_sec_er_ctd := 0;
1263 l_soc_sec_er_dtd := 0;
1264 l_soc_sec_tot_ctd := 0;
1265 l_soc_sec_tot_dtd := 0;
1266
1267
1268 l_soc_sec_quo_ee_ctd := 0;
1269 l_soc_sec_quo_ee_dtd := 0;
1270 l_soc_sec_quo_er_ctd := 0;
1271 l_soc_sec_quo_er_dtd := 0;
1272
1273 dbg(' SS ID'||l_ss_id);
1274
1275 l_assignment_act := fetch_active_assg_act(p_business_group_id
1276 ,l_tax_unit_id
1277 ,p_start_date_earned
1278 ,p_end_date_earned);
1279
1280 dbg('The Assignment Action Id ' ||l_assignment_act);
1281
1282 insert_xml_plsql_table( p_xml_data,'GRE',NULL,'T','C');
1283 insert_xml_plsql_table( p_xml_data,'GRE_NAME',l_gre_name,'D','C');
1284 insert_xml_plsql_table( p_xml_data,'SOCIAL_SECURITY_ID',l_ss_id,
1285 'D','C');
1286
1287 /* Setting the Context for the Balances to be Fetched */
1288 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1289 pay_balance_pkg.set_context('DATE_EARNED',
1290 to_char(p_end_date_earned,'YYYY/MM/DD'));
1291 pay_balance_pkg.set_context('BALANCE_DATE',
1292 to_char(p_start_date_earned,'YYYY/MM/DD'));
1293
1294 dbg('Get Gross Earnings Balances');
1295
1296 IF l_assignment_act <> 0 THEN
1297 l_gross_subj_ctd := pay_balance_pkg.get_value(
1298 p_assignment_action_id =>l_assignment_act,
1299 p_defined_balance_id =>l_gross_ctd_def_bal_id);
1300 END IF;
1301
1302
1303 IF l_gross_dtd_def_bal_id IS NOT NULL THEN
1304
1305 IF l_assignment_act <> 0 THEN
1306
1307 l_gross_subj_dtd := pay_balance_pkg.get_value(
1308 p_assignment_action_id =>l_assignment_act,
1309 p_defined_balance_id =>l_gross_dtd_def_bal_id);
1310
1311 END IF;
1312
1313 END IF;
1314
1315 dbg('The CTD value for Gross earnings '||l_gross_subj_ctd);
1316 dbg('The DTD value for Gross earnings '||l_gross_subj_dtd);
1317
1318 -- insert Gross Earnings records to plsql table
1319 insert_xml_plsql_table( p_xml_data,'GROSS_EARNINGS',NULL,'T','C');
1320 insert_xml_plsql_table( p_xml_data,'GROSS_SUBJ_CTD',l_gross_subj_ctd,
1321 'D','B');
1322
1323 IF p_dimension <> 'CTD' THEN
1324
1325 insert_xml_plsql_table( p_xml_data,'GROSS_SUBJ_DTD',
1326 l_gross_subj_dtd,'D','B');
1327
1328 END IF;
1329
1330 insert_xml_plsql_table( p_xml_data,'/GROSS_EARNINGS',NULL,'T','C');
1331
1332 -- Add to state totals AND gross total
1333
1334 l_st_gross_sub_ctd := l_st_gross_sub_ctd + l_gross_subj_ctd;
1335 l_st_gross_sub_dtd := l_st_gross_sub_dtd + l_gross_subj_dtd;
1336
1337 IF p_show_isr = 'Y' THEN
1338
1339 dbg('Get ISR Tax Balance');
1340
1341 IF l_assignment_act <> 0 THEN
1342 l_isr_witheld_ctd := pay_balance_pkg.get_value(
1343 p_assignment_action_id =>l_assignment_act,
1344 p_defined_balance_id =>l_isr_withheld_ctd_def_bal_id);
1345
1346 l_isr_subj_ctd := pay_balance_pkg.get_value(
1347 p_assignment_action_id =>l_assignment_act,
1348 p_defined_balance_id =>l_isr_subj_ctd_def_bal_id);
1349 END IF;
1350
1351 IF l_isr_withheld_dtd_def_bal_id is not null AND
1352 l_isr_subj_dtd_def_bal_id is not null THEN
1353
1354 IF l_assignment_act <> 0 THEN
1355
1356 l_isr_witheld_dtd := pay_balance_pkg.get_value(
1357 p_assignment_action_id =>l_assignment_act,
1358 p_defined_balance_id =>l_isr_withheld_dtd_def_bal_id);
1359
1360 l_isr_subj_dtd := pay_balance_pkg.get_value(
1361 p_assignment_action_id =>l_assignment_act,
1362 p_defined_balance_id =>l_isr_subj_dtd_def_bal_id);
1363
1364 END IF;
1365
1366 END IF;
1367
1368 dbg('The cTD value for ISR withheld '||l_isr_witheld_ctd);
1369 dbg('The cTD value for ISR Subject '||l_isr_subj_ctd);
1370
1371 dbg('The DTD value for ISR withheld '||l_isr_witheld_dtd);
1372 dbg('The DTD value for ISR Subject '||l_isr_subj_dtd);
1373
1374 -- insert ISR balance records to plsql table
1375 insert_xml_plsql_table( p_xml_data,'ISR',NULL,'T','C');
1376 insert_xml_plsql_table( p_xml_data,'ISR_WITHHELD_CTD',
1377 l_isr_witheld_ctd,'D','B');
1378 insert_xml_plsql_table( p_xml_data,'ISR_SUBJ_CTD',l_isr_subj_ctd,
1379 'D','B');
1380
1381 IF p_dimension <>'CTD' THEN
1382
1383 insert_xml_plsql_table( p_xml_data,'ISR_WITHHELD_DTD',
1384 l_isr_witheld_dtd,'D','B');
1385 insert_xml_plsql_table( p_xml_data,'ISR_SUBJ_DTD',
1386 l_isr_subj_dtd,'D','B');
1387
1388 END IF;
1389
1390
1391 insert_xml_plsql_table( p_xml_data,'/ISR',NULL,'T','C');
1392
1393 -- Add to state totals AND gross total
1394 l_st_isr_witheld_ctd := l_st_isr_witheld_ctd + l_isr_witheld_ctd;
1395 l_st_isr_witheld_dtd := l_st_isr_witheld_dtd + l_isr_witheld_dtd;
1396 l_st_isr_subj_ctd := l_st_isr_subj_ctd + l_isr_subj_ctd;
1397 l_st_isr_subj_dtd := l_st_isr_subj_dtd + l_isr_subj_dtd;
1398
1399 END IF;
1400
1401 IF p_show_soc_security = 'Y' THEN
1402
1403 l_soc_sec_ee_ctd := 0 ;
1404 l_soc_sec_er_ctd := 0 ;
1405 l_soc_sec_ee_dtd := 0 ;
1406 l_soc_sec_er_dtd := 0 ;
1407
1408
1409 dbg('Get Social Security Quota Balances');
1410
1411 FOR l_cnt5 IN soc_sec_det_tab.FIRST .. soc_sec_det_tab.LAST
1412 LOOP
1413
1414 l_ss_ee_ctd := 0 ;
1415 l_ss_er_ctd := 0 ;
1416 l_ss_ee_dtd := 0 ;
1417 l_ss_er_dtd := 0 ;
1418
1419 dbg('l_cnt5' || l_cnt5 );
1420 dbg('Soc Sec Ins Type: '||soc_sec_det_tab(l_cnt5).balance_name);
1421
1422 IF l_assignment_act <> 0 THEN
1423 l_ss_ee_ctd := pay_balance_pkg.get_value(
1424 p_assignment_action_id =>l_assignment_act,
1425 p_defined_balance_id =>
1426 soc_sec_det_tab(l_cnt5).soc_sec_ee_ctd_id);
1427
1428 l_ss_er_ctd := pay_balance_pkg.get_value(
1429 p_assignment_action_id =>l_assignment_act,
1430 p_defined_balance_id =>
1431 soc_sec_det_tab(l_cnt5).soc_sec_er_ctd_id);
1432 END IF;
1433
1434 IF p_dimension <> 'CTD' THEN
1435
1436 IF l_assignment_act <> 0 THEN
1437
1438 l_ss_ee_dtd := pay_balance_pkg.get_value(
1439 p_assignment_action_id =>l_assignment_act,
1440 p_defined_balance_id =>
1441 soc_sec_det_tab(l_cnt5).soc_sec_ee_dtd_id);
1442 l_ss_er_dtd := pay_balance_pkg.get_value(
1443 p_assignment_action_id =>l_assignment_act,
1444 p_defined_balance_id =>
1445 soc_sec_det_tab(l_cnt5).soc_sec_er_dtd_id);
1446
1447 END IF;
1448
1449 END IF;
1450
1451 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd := l_ss_ee_ctd ;
1452 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd := l_ss_er_ctd ;
1453 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd := l_ss_ee_dtd ;
1454 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd := l_ss_er_dtd ;
1455
1456 IF UPPER(soc_sec_det_tab(l_cnt5).balance_name) <>'INFONAVIT' THEN
1457
1458 l_soc_sec_ee_ctd := l_soc_sec_ee_ctd + l_ss_ee_ctd ;
1459 l_soc_sec_er_ctd := l_soc_sec_er_ctd + l_ss_er_ctd ;
1460 l_soc_sec_ee_dtd := l_soc_sec_ee_dtd + l_ss_ee_dtd ;
1461 l_soc_sec_er_dtd := l_soc_sec_er_dtd + l_ss_er_dtd ;
1462
1463 END IF;
1464
1465
1466 END loop;
1467
1468 dbg('The CTD value for Social Security Quota EE '||
1469 l_soc_sec_ee_ctd);
1470 dbg('The cTD value for Social Security Quota ER '||
1471 l_soc_sec_er_ctd);
1472 dbg('The DTD value for Social Security Quota EE '||
1473 l_soc_sec_ee_dtd);
1474 dbg('The DTD value for Social Security Quota ER '||
1475 l_soc_sec_er_dtd);
1476
1477
1478 l_soc_sec_tot_ctd := l_soc_sec_ee_ctd + l_soc_sec_er_ctd ;
1479 l_soc_sec_tot_dtd := l_soc_sec_ee_dtd + l_soc_sec_er_dtd ;
1480
1481
1482 insert_xml_plsql_table( p_xml_data,'SOCIAL_SECURITY',NULL,'T','C');
1483 insert_xml_plsql_table( p_xml_data,'SOC_SEC_EE_CTD',
1484 l_soc_sec_ee_ctd,'D','B');
1485 insert_xml_plsql_table( p_xml_data,'SOC_SEC_ER_CTD',
1486 l_soc_sec_er_ctd,'D','B');
1487 insert_xml_plsql_table( p_xml_data,'SOC_SEC_TOTAL_CTD',
1488 l_soc_sec_tot_ctd,'D','B');
1489
1490 IF p_dimension <> 'CTD' THEN
1491 insert_xml_plsql_table( p_xml_data,'SOC_SEC_EE_DTD',
1492 l_soc_sec_ee_dtd,'D','B');
1493 insert_xml_plsql_table( p_xml_data,'SOC_SEC_ER_DTD',
1494 l_soc_sec_er_dtd,'D','B');
1495 insert_xml_plsql_table( p_xml_data,'SOC_SEC_TOTAL_DTD',
1496 l_soc_sec_tot_dtd,'D','B');
1497 END IF ;
1498
1499 insert_xml_plsql_table(p_xml_data,'/SOCIAL_SECURITY',NULL,'T','C');
1500
1501 dbg('The current value of the Counter is '||p_xml_data.count);
1502
1503 l_st_soc_sec_ee_ctd := l_st_soc_sec_ee_ctd + l_soc_sec_ee_ctd;
1504 l_st_soc_sec_er_ctd := l_st_soc_sec_er_ctd + l_soc_sec_er_ctd;
1505 l_st_soc_sec_ee_dtd := l_st_soc_sec_ee_dtd + l_soc_sec_ee_dtd ;
1506 l_st_soc_sec_er_dtd := l_st_soc_sec_er_dtd + l_soc_sec_er_dtd;
1507 l_st_soc_sec_tot_ctd := l_st_soc_sec_tot_ctd + l_soc_sec_tot_ctd;
1508 l_st_soc_sec_tot_dtd := l_st_soc_sec_tot_dtd + l_soc_sec_tot_dtd;
1509
1510 END IF;
1511
1512
1513 IF p_show_state = 'Y' THEN
1514
1515 --get the state tax withheld AND subject
1516
1517 l_st_witheld_ctd := 0 ;
1518 l_st_subj_ctd := 0 ;
1519 l_st_witheld_dtd := 0 ;
1520 l_st_subj_dtd := 0 ;
1521
1522 IF l_st_withheld_ctd_def_bal_id IS NOT NULL THEN /*7687079*/
1523
1524 IF l_assignment_act <> 0 THEN
1525
1526 l_st_subj_ctd := pay_balance_pkg.get_value(
1527 p_assignment_action_id =>l_assignment_act,
1528 p_defined_balance_id =>l_st_subj_ctd_def_bal_id);
1529
1530 END IF;
1531
1532 END IF;
1533
1534 IF l_state <> 'CHIH' OR l_state <> 'CAMP' THEN /*bug 10236983 */
1535
1536 IF l_st_withheld_ctd_def_bal_id IS NOT NULL THEN
1537
1538 IF l_assignment_act <> 0 THEN
1539
1540 l_st_witheld_ctd := pay_balance_pkg.get_value(
1541 p_assignment_action_id =>l_assignment_act,
1542 p_defined_balance_id =>l_st_withheld_ctd_def_bal_id);
1543
1544 END IF;
1545
1546 END IF;
1547
1548 ELSE
1549
1550 l_st_witheld_ctd := 0 ;
1551
1552 END IF;
1553
1554 dbg('State tax withheld ctd ' || to_char(l_st_witheld_ctd) ) ;
1555
1556 IF l_st_withheld_dtd_def_bal_id IS NOT NULL AND
1557 l_st_subj_dtd_def_bal_id IS NOT NULL THEN
1558
1559 IF l_assignment_act <> 0 THEN
1560
1561 l_st_subj_dtd := pay_balance_pkg.get_value(
1562 p_assignment_action_id =>l_assignment_act,
1563 p_defined_balance_id =>l_st_subj_dtd_def_bal_id);
1564
1565 END IF;
1566
1567 IF l_state <> 'CHIH' OR l_state <> 'CAMP' THEN /*bug 10236983 */
1568
1569 IF l_assignment_act <> 0 THEN
1570
1571 l_st_witheld_dtd := pay_balance_pkg.get_value(
1572 p_assignment_action_id =>l_assignment_act,
1573 p_defined_balance_id =>l_st_withheld_dtd_def_bal_id);
1574
1575 END IF;
1576
1577 ELSE
1578
1579 l_st_witheld_dtd := 0 ;
1580
1581 END IF;
1582
1583 dbg('State tax withheld dtd ' || to_char(l_st_witheld_dtd) ) ;
1584
1585 END IF;
1586
1587 dbg('The cTD value for Employer State Tax withheld '||
1588 l_st_witheld_ctd);
1589 dbg('The cTD value for Employer State Tax Subject '||
1590 l_st_subj_ctd);
1591 dbg('The DTD value for Employer State Tax withheld '||
1592 l_st_witheld_dtd);
1593 dbg('The DTD value for Employer State Tax Subject '||
1594 l_st_subj_dtd);
1595
1596 -- insert ISR balance records to plsql table
1597 insert_xml_plsql_table( p_xml_data,'STATE_TAX',NULL,'T','C');
1598
1599 IF l_state = 'QRO' THEN /*7565304*/
1600 insert_xml_plsql_table( p_xml_data,'STATE_WITHHELD_CTD',
1601 0,'D','B');
1602 ELSE
1603 insert_xml_plsql_table( p_xml_data,'STATE_WITHHELD_CTD',
1604 l_st_witheld_ctd,'D','B');
1605 END IF;
1606 insert_xml_plsql_table( p_xml_data,'STATE_SUBJ_CTD',
1607 l_st_subj_ctd,'D','B');
1608
1609 IF p_dimension <>'CTD' THEN
1610
1611 IF l_state ='QRO' then /*7565304*/
1612 insert_xml_plsql_table( p_xml_data,'STATE_WITHHELD_DTD',
1613 0,'D','B');
1614 ELSE
1615 insert_xml_plsql_table( p_xml_data,'STATE_WITHHELD_DTD',
1616 l_st_witheld_dtd,'D','B');
1617 END IF;
1618
1619 insert_xml_plsql_table( p_xml_data,'STATE_SUBJ_DTD',
1620 l_st_subj_dtd,'D','B');
1621
1622 END IF;
1623
1624
1625 insert_xml_plsql_table( p_xml_data,'/STATE_TAX',NULL,'T','C');
1626
1627 dbg('Before adding to State totals ');
1628
1629 -- Add to state totals AND gross total
1630 l_st_st_witheld_ctd := l_st_st_witheld_ctd + l_st_witheld_ctd;
1631 l_st_st_witheld_dtd := l_st_st_witheld_dtd + l_st_witheld_dtd;
1632 l_st_st_subj_ctd := l_st_st_subj_ctd + l_st_subj_ctd;
1633 l_st_st_subj_dtd := l_st_st_subj_dtd + l_st_subj_dtd;
1634
1635 --get state earnings details
1636
1637 dbg('After adding to State totals ');
1638 dbg('The value of The PLSQL counter for state_earnings table is '||
1639 state_earnings.count);
1640
1641 IF state_earnings.count > 0 THEN
1642
1643 FOR l_cnt50 IN state_earnings.FIRST .. state_earnings.LAST
1644 LOOP
1645
1646 dbg('State Earning : '|| state_earnings(l_cnt50).balance_name );
1647
1648 l_st_earn_ctd_value := 0 ;
1649 l_st_earn_dtd_value := 0 ;
1650
1651 IF state_earnings(l_cnt50).state_earnings_ctd_id IS NOT NULL THEN
1652
1653 IF l_assignment_act <> 0 THEN
1654
1655 l_st_earn_ctd_value := pay_balance_pkg.get_value(
1656 p_assignment_action_id =>l_assignment_act,
1657 p_defined_balance_id =>
1658 state_earnings(l_cnt50).state_earnings_ctd_id);
1659
1660 END IF;
1661
1662 END IF;
1663
1664 IF p_dimension <> 'CTD' THEN
1665
1666 IF state_earnings(l_cnt50).state_earnings_dtd_id IS NOT NULL
1667 THEN
1668
1669 IF l_assignment_act <> 0 THEN
1670
1671 l_st_earn_dtd_value := pay_balance_pkg.get_value(
1672 p_assignment_action_id =>l_assignment_act,
1673 p_defined_balance_id =>
1674 state_earnings(l_cnt50).state_earnings_dtd_id);
1675
1676 END IF;
1677
1678 END IF;
1679
1680 END IF;
1681
1682 dbg('The CTD value '||l_st_earn_ctd_value);
1683 dbg('The DTD value '||l_st_earn_dtd_value);
1684
1685 insert_xml_plsql_table( p_xml_data,'STATE_DETAILS',NULL,'T','C');
1686 insert_xml_plsql_table( p_xml_data,'STATE_DETAILS_NAME',
1687 state_earnings(l_cnt50).balance_name,'D','C');
1688 insert_xml_plsql_table( p_xml_data,'STATE_DETAILS_SUBJ_CTD',
1689 l_st_earn_ctd_value,'D','B');
1690 IF p_dimension <> 'CTD' THEN
1691 insert_xml_plsql_table( p_xml_data,'STATE_DETAILS_SUBJ_DTD',
1692 l_st_earn_dtd_value,'D','B');
1693 END IF;
1694 insert_xml_plsql_table( p_xml_data,'/STATE_DETAILS',NULL,'T','C');
1695 --
1696 -- Add the total to state level balance
1697 --
1698 st_state_earnings(l_cnt50).balance_name :=
1699 state_earnings(l_cnt50).balance_name ;
1700 st_state_earnings(l_cnt50).state_earnings_ctd_value :=
1701 nvl(st_state_earnings(l_cnt50).state_earnings_ctd_value,0) +
1702 l_st_earn_ctd_value ;
1703 st_state_earnings(l_cnt50).state_earnings_dtd_value :=
1704 nvl(st_state_earnings(l_cnt50).state_earnings_dtd_value,0) +
1705 l_st_earn_dtd_value ;
1706
1707 END LOOP ;
1708
1709 END IF;
1710
1711 END IF;
1712
1713 IF p_show_soc_security = 'Y' THEN
1714
1715 dbg('The value of The PLSQL counter for Social '||
1716 soc_sec_det_tab.count);
1717
1718 FOR l_cnt5 IN soc_sec_det_tab.FIRST .. soc_sec_det_tab.LAST
1719 LOOP
1720
1721 dbg('counter ' || l_cnt5 );
1722 dbg('Soc Sec Ins Type is '||
1723 soc_sec_det_tab(l_cnt5).balance_name);
1724
1725 dbg('The CTD value for EE '||
1726 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd );
1727 dbg('The CTD value for ER '||
1728 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd );
1729 dbg('The DTD value for EE '||
1730 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd );
1731 dbg('The DTD value for ER '||
1732 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd );
1733
1734 insert_xml_plsql_table( p_xml_data,'SOCIAL_SECURITY_QUOTA',
1735 NULL,'T','C');
1736 insert_xml_plsql_table( p_xml_data,'SOC_SEC_INS',
1737 soc_sec_det_tab(l_cnt5).balance_name,'D','C');
1738 insert_xml_plsql_table( p_xml_data,'SOC_SEC_INS_EE_PCT',
1739 soc_sec_det_tab(l_cnt5).soc_sec_tax_pct_ee,'D','P');
1740
1741 IF soc_sec_det_tab(l_cnt5).balance_name = 'Work Risk Incident'
1742 THEN
1743
1744 -- For Work Risk Employer pcts get the wrip FROM GRE
1745 dbg('Before Calling get_wrip to get the workrisk premium');
1746 dbg('Business group id ' ||p_business_group_id);
1747 dbg('Tax Unit id ' ||l_tax_unit_id);
1748
1749 insert_xml_plsql_table( p_xml_data,'SOC_SEC_INS_ER_PCT',
1750 hr_mx_utility.get_wrip(p_business_group_id,
1751 l_tax_unit_id),'D','P');
1752 dbg('After Calling get_wrip to get the workrisk premium');
1753
1754 ELSE
1755
1756 insert_xml_plsql_table( p_xml_data,'SOC_SEC_INS_ER_PCT',
1757 soc_sec_det_tab(l_cnt5).soc_sec_tax_pct_er,'D','P');
1758
1759 END IF;
1760
1761 insert_xml_plsql_table( p_xml_data,'SOC_SEC_INS_EE_CTD',
1762 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd,'D','B');
1763 insert_xml_plsql_table( p_xml_data,'SOC_SEC_INS_ER_CTD',
1764 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd,'D','B');
1765
1766 IF p_dimension <> 'CTD' THEN
1767
1768 insert_xml_plsql_table( p_xml_data,'SOC_SEC_INS_EE_DTD',
1769 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd,'D','B');
1770 insert_xml_plsql_table( p_xml_data,'SOC_SEC_INS_ER_DTD',
1771 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd,'D','B');
1772
1773 END IF;
1774
1775 insert_xml_plsql_table( p_xml_data,'/SOCIAL_SECURITY_QUOTA',
1776 NULL,'T','C');
1777
1778 --
1779 -- Add the total to state level balance
1780 --
1781
1782 st_soc_sec_det_tab(l_cnt5).balance_name :=
1783 soc_sec_det_tab(l_cnt5).balance_name ;
1784 st_soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd :=
1785 nvl(st_soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd,0) +
1786 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd;
1787
1788 st_soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd :=
1789 nvl(st_soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd,0) +
1790 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd;
1791
1792 st_soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd :=
1793 nvl(st_soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd,0) +
1794 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd;
1795
1796 st_soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd :=
1797 nvl(st_soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd,0) +
1798 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd;
1799
1800 --
1801 -- Add the total to legal employer level balance
1802 --
1803
1804 le_soc_sec_det_tab(l_cnt5).balance_name :=
1805 soc_sec_det_tab(l_cnt5).balance_name ;
1806 le_soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd :=
1807 nvl(le_soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd,0) +
1808 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd;
1809
1810 le_soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd :=
1811 nvl(le_soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd,0) +
1812 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd;
1813
1814 le_soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd :=
1815 nvl(le_soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd,0) +
1816 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd;
1817
1818 le_soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd :=
1819 nvl(le_soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd,0) +
1820 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd;
1821
1822 -- reset values
1823
1824 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_ctd := 0 ;
1825 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_ctd := 0 ;
1826 soc_sec_det_tab(l_cnt5).soc_sec_quo_ee_dtd := 0 ;
1827 soc_sec_det_tab(l_cnt5).soc_sec_quo_er_dtd := 0 ;
1828
1829 END LOOP;
1830
1831 END IF; /* End of p_show_soc_security = 'Y' */
1832
1833
1834 insert_xml_plsql_table( p_xml_data,'/GRE',NULL,'T','C');
1835
1836 END;
1837
1838 END LOOP; /*End Loop for the Second Cursor fetch next GRE within a State */
1839
1840 dbg('Adding up the Total for Legal Employer ' ) ;
1841
1842 /* Adding up the Total for the Legal Employer */
1843 l_lt_gross_sub_ctd := l_lt_gross_sub_ctd + l_st_gross_sub_ctd;
1844 l_lt_gross_sub_dtd := l_lt_gross_sub_dtd + l_st_gross_sub_dtd;
1845 l_lt_isr_witheld_ctd := l_lt_isr_witheld_ctd + l_st_isr_witheld_ctd ;
1846 l_lt_isr_witheld_dtd := l_lt_isr_witheld_dtd + l_st_isr_witheld_dtd;
1847 l_lt_isr_subj_ctd := l_lt_isr_subj_ctd + l_st_isr_subj_ctd ;
1848 l_lt_isr_subj_dtd := l_lt_isr_subj_dtd + l_st_isr_subj_dtd ;
1849
1850
1851 l_lt_st_subj_ctd := l_lt_st_subj_ctd + l_st_st_subj_ctd ;
1852 l_lt_st_subj_dtd := l_lt_st_subj_dtd + l_st_st_subj_dtd ;
1853
1854 /* Bug: 10236983
1855 The state tax for Campache is based on total wages paid to all employees for a legal employer,
1856 the liability is not calculated in the payroll run. The application maintains the subject wages
1857 for each employee. The tax rates are delivered in the user table. The total liability will not be
1858 displayed at the SOE or in the Run Results report/window. The TRR should calculate and the total
1859 liability by applying the appropriate rate to the total subject wages paid by the employer.
1860 */
1861
1862 IF l_state = 'CHIH' OR l_state = 'CAMP' THEN
1863
1864 -- calculate the withheld for state tax chihuahua/Campache FROM the user table
1865 -- use fix rate, marginal rate AND lower bound columns
1866 -- calculate state tax withheld for ctd
1867
1868 IF l_st_st_subj_ctd > 0 THEN
1869
1870 l_fixed_rate := 0 ;
1871 l_marginal_rate := 0 ;
1872 l_lower_bound := 0 ;
1873 l_table_name := 'STATE_TAX_RATES_'||l_state ;
1874
1875 -- calculate based on the subject earnings
1876 -- get the fixed rate
1877
1878 dbg('Get Fixed Rate');
1879 dbg('Table Name - '||l_table_name );
1880 dbg('Col Name - Fixed Rate');
1881 dbg('Row value ' || to_char(l_st_st_subj_ctd) ) ;
1882 dbg('effective date ' || to_char(p_end_date_earned) ) ;
1883
1884 /*bug 14395400 : Adding fnd_number.canonical_to_number*/
1885 l_fixed_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
1886 p_bus_group_id => p_business_group_id,
1887 p_table_name => l_table_name,
1888 p_col_name => 'Fixed Rate',
1889 p_row_value => l_st_st_subj_ctd,
1890 p_effective_date => p_end_date_earned));
1891
1892 dbg('Fixed Rate ' || to_char(l_fixed_rate) ) ;
1893 -- get the marginal rate
1894 dbg('Get Marginal Rate');
1895 dbg('Table Name - '||l_table_name);
1896 dbg('Col Name - Marginal Rate');
1897 dbg('Row value ' || to_char(l_st_st_subj_ctd) ) ;
1898 dbg('effective date ' || to_char(p_end_date_earned) ) ;
1899
1900 l_marginal_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
1901 p_bus_group_id => p_business_group_id,
1902 p_table_name => l_table_name,
1903 p_col_name => 'Marginal Rate',
1904 p_row_value => l_st_st_subj_ctd,
1905 p_effective_date => p_end_date_earned));
1906
1907 dbg('Marginal Rate ' || to_char(l_marginal_rate) ) ;
1908 -- get the Lower Bound
1909 dbg('Get Lower Bound');
1910 dbg('Table Name - '||l_table_name);
1911 dbg('Col Name - Lower Bound');
1912 dbg('Row value ' || to_char(l_st_st_subj_ctd) ) ;
1913 dbg('effective date ' || to_char(p_end_date_earned) ) ;
1914
1915 l_lower_bound := fnd_number.canonical_to_number(hruserdt.get_table_value(
1916 p_bus_group_id => p_business_group_id,
1917 p_table_name => l_table_name,
1918 p_col_name => 'Lower Bound',
1919 p_row_value => l_st_st_subj_ctd,
1920 p_effective_date => p_end_date_earned));
1921
1922 dbg('Lower Bound ' || to_char(l_lower_bound) ) ;
1923 /* bug fix 4526042 */
1924 l_st_st_witheld_ctd := l_fixed_rate +
1925 ( (l_st_st_subj_ctd - l_lower_bound) * l_marginal_rate / 100 );
1926
1927 ELSE
1928
1929 l_st_st_witheld_ctd := 0 ;
1930
1931 END IF ;
1932
1933 -- state tax withheld for dtd
1934
1935 l_fixed_rate := 0 ;
1936 l_marginal_rate := 0 ;
1937 l_lower_bound := 0 ;
1938 l_table_name := 'STATE_TAX_RATES_'||l_state ;
1939 IF l_st_st_subj_dtd > 0 THEN
1940
1941 -- calculate based on the subject earnings
1942 -- get the fixed rate
1943
1944 dbg('Get Fixed Rate');
1945 dbg('Table Name - '||l_table_name);
1946 dbg('Col Name - Fixed Rate');
1947 dbg('Row value ' || to_char(l_st_st_subj_dtd) ) ;
1948 dbg('effective date ' || to_char(p_end_date_earned) ) ;
1949
1950 l_fixed_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
1951 p_bus_group_id => p_business_group_id,
1952 p_table_name => l_table_name,
1953 p_col_name => 'Fixed Rate',
1954 p_row_value => l_st_st_subj_dtd,
1955 p_effective_date => p_end_date_earned));
1956
1957 dbg('Fixed Rate ' || to_char(l_fixed_rate) ) ;
1958 -- get the marginal rate
1959 dbg('Get Marginal Rate');
1960 dbg('Table Name - '||l_table_name);
1961 dbg('Col Name - Marginal Rate');
1962 dbg('Row value ' || to_char(l_st_st_subj_dtd) ) ;
1963 dbg('effective date ' || to_char(p_end_date_earned) ) ;
1964
1965 l_marginal_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
1966 p_bus_group_id => p_business_group_id,
1967 p_table_name => l_table_name,
1968 p_col_name => 'Marginal Rate',
1969 p_row_value => l_st_st_subj_dtd,
1970 p_effective_date => p_end_date_earned));
1971
1972 dbg('Marginal Rate ' || to_char(l_marginal_rate) ) ;
1973 -- get the Lower Bound
1974 dbg('Get Lower Bound');
1975 dbg('Table Name - '||l_table_name);
1976 dbg('Col Name - Lower Bound');
1977 dbg('Row value ' || to_char(l_st_st_subj_dtd) ) ;
1978 dbg('effective date ' || to_char(p_end_date_earned) ) ;
1979
1980 l_lower_bound := fnd_number.canonical_to_number(hruserdt.get_table_value(
1981 p_bus_group_id => p_business_group_id,
1982 p_table_name => l_table_name,
1983 p_col_name => 'Lower Bound',
1984 p_row_value => l_st_st_subj_dtd,
1985 p_effective_date => p_end_date_earned));
1986
1987 dbg('Lower Bound ' || to_char(l_lower_bound) ) ;
1988 /* bug fix 4526042 */
1989 l_st_st_witheld_dtd := l_fixed_rate +
1990 ( (l_st_st_subj_dtd - l_lower_bound) * l_marginal_rate / 100 );
1991
1992 ELSE
1993
1994 l_st_st_witheld_dtd := 0 ;
1995
1996 END IF;
1997
1998 END IF; -- l_state
1999
2000 /* 7565304 */
2001 IF l_state = 'QRO' THEN
2002
2003 l_rate:=0;
2004 l_dummy:=0;
2005 l_leg_info:='N';
2006 l_min_wage:=0;
2007 l_rate_type:='FLAT_RATE';
2008 l_st_st_exemption:=0;
2009
2010 /* Bug:9451129 Modified code to avoid char to number conversion error
2011 when number format is 10.000,00 */
2012
2013 l_dummy:= pay_mx_tax_functions.get_mx_tax_info(
2014 p_business_group_id
2015 ,l_tax_unit_id
2016 ,p_end_date_earned
2017 ,l_state
2018 ,'MX State Tax Rate'
2019 ,l_rate_type
2020 ,lv_rate
2021 ,l_leg_info
2022 ,l_leg_info
2023 ,l_leg_info
2024 ,l_leg_info );
2025
2026 dbg('lv_rate '||lv_rate) ;
2027 l_rate :=fnd_number.canonical_to_number(lv_rate);
2028
2029 /* Bug:9451129 pay_mx_tax_functions.get_min_wage changed to
2030 pay_mx_utility.get_min_wage to avoid char to number conversion error
2031 when number format is 10.000,00 */
2032
2033 l_min_wage:= pay_mx_utility.get_min_wage(
2034 p_end_date_earned,
2035 l_leg_info,
2036 'C' );
2037
2038 dbg('l_min_wage '||l_min_wage);
2039 l_st_st_exemption :=((l_min_wage*8)*
2040 ((p_end_date_earned - p_start_date_earned)+1)) * l_rate/100;
2041 -- calculate state tax withheld for ctd
2042
2043 IF l_st_st_witheld_ctd > 0 THEN
2044 l_st_st_witheld_ctd := l_st_st_witheld_ctd - l_st_st_exemption;
2045
2046 IF l_st_st_witheld_ctd < 0 THEN
2047 l_st_st_witheld_ctd := 0 ;
2048 END IF;
2049
2050 ELSE
2051
2052 l_st_st_witheld_ctd := 0 ;
2053 END IF;
2054 IF l_st_st_subj_dtd > 0 THEN
2055
2056 -- calculate state tax withheld for dtd
2057
2058 l_st_st_witheld_dtd := l_st_st_witheld_dtd - l_st_st_exemption;
2059
2060 IF l_st_st_witheld_dtd < 0 THEN
2061 l_st_st_witheld_dtd := 0 ;
2062 END IF;
2063
2064 ELSE
2065
2066 l_st_st_witheld_dtd := 0 ;
2067
2068 END IF;
2069 END IF; /*l_state = 'QRO'*/
2070
2071 l_lt_st_witheld_ctd := l_lt_st_witheld_ctd + l_st_st_witheld_ctd ;
2072 l_lt_st_witheld_dtd := l_lt_st_witheld_dtd + l_st_st_witheld_dtd;
2073
2074
2075 l_lt_soc_sec_ee_ctd := l_lt_soc_sec_ee_ctd + l_st_soc_sec_ee_ctd;
2076 l_lt_soc_sec_ee_dtd := l_lt_soc_sec_ee_dtd + l_st_soc_sec_ee_dtd;
2077 l_lt_soc_sec_er_ctd := l_lt_soc_sec_er_ctd + l_st_soc_sec_er_ctd;
2078 l_lt_soc_sec_er_dtd := l_lt_soc_sec_er_dtd + l_st_soc_sec_er_dtd;
2079 l_lt_soc_sec_tot_ctd := l_lt_soc_sec_tot_ctd + l_st_soc_sec_tot_ctd ;
2080 l_lt_soc_sec_tot_dtd := l_lt_soc_sec_tot_dtd + l_st_soc_sec_tot_dtd ;
2081
2082
2083 /* writing the State Level Details */
2084 dbg('Writing State Level Details ' ) ;
2085
2086 l_state_heading := 'State Total Report All GREs for '|| l_state_name;
2087
2088 dbg('Writing State Level Details ....... 1' ) ;
2089
2090 insert_xml_plsql_table( p_xml_data,'STATE_TOTAL',NULL,'T','C');
2091
2092 dbg('Writing State Level Details ........ 2 ' ) ;
2093
2094 insert_xml_plsql_table( p_xml_data,'STATE_TOTAL_HEAD',l_state_heading,
2095 'D','C');
2096
2097 dbg('Writing State Level Gross Subject CTD Details ' ) ;
2098
2099 insert_xml_plsql_table( p_xml_data,'ST_GROSS',NULL,'T','C');
2100 insert_xml_plsql_table( p_xml_data,'ST_GROSS_SUBJ_CTD',l_st_gross_sub_ctd,
2101 'D','B');
2102
2103 dbg('Writing State Level Gross Subject DTD Details ' ) ;
2104
2105 IF p_dimension <> 'CTD' THEN
2106
2107 insert_xml_plsql_table( p_xml_data,'ST_GROSS_SUBJ_DTD',
2108 l_st_gross_sub_dtd,'D','B');
2109
2110 END IF;
2111
2112 insert_xml_plsql_table( p_xml_data,'/ST_GROSS',NULL,'T','C');
2113
2114 dbg('Writing State Level ISR Details ' ) ;
2115
2116 IF p_show_isr = 'Y' THEN
2117
2118 insert_xml_plsql_table( p_xml_data,'ST_ISR',NULL,'T','C');
2119 insert_xml_plsql_table( p_xml_data,'ST_ISR_WITHHELD_CTD',
2120 l_st_isr_witheld_ctd,'D','B');
2121 insert_xml_plsql_table( p_xml_data,'ST_ISR_SUBJ_CTD',
2122 l_st_isr_subj_ctd,'D','B');
2123
2124 IF p_dimension <> 'CTD' THEN
2125
2126 insert_xml_plsql_table( p_xml_data,'ST_ISR_WITHHELD_DTD',
2127 l_st_isr_witheld_dtd,'D','B');
2128 insert_xml_plsql_table( p_xml_data,'ST_ISR_SUBJ_DTD',
2129 l_st_isr_subj_dtd,'D','B');
2130
2131 END IF;
2132
2133 insert_xml_plsql_table( p_xml_data,'/ST_ISR',NULL,'T','C');
2134
2135 END IF;
2136
2137 IF p_show_soc_security = 'Y' THEN
2138
2139 dbg('Writing State Level Social Security Details ' ) ;
2140
2141 insert_xml_plsql_table( p_xml_data,'ST_SOCIAL_SECURITY',NULL,'T','C');
2142 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_EE_CTD',
2143 l_st_soc_sec_ee_ctd,'D','B');
2144 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_ER_CTD',
2145 l_st_soc_sec_er_ctd,'D','B');
2146 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_TOTAL_CTD',
2147 l_st_soc_sec_tot_ctd,'D','B');
2148
2149 IF p_dimension <> 'CTD' THEN
2150
2151 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_EE_DTD',
2152 l_st_soc_sec_ee_dtd,'D','B');
2153 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_ER_DTD',
2154 l_st_soc_sec_er_dtd,'D','B');
2155 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_TOTAL_DTD',
2156 l_st_soc_sec_tot_dtd,'D','B');
2157
2158 END IF;
2159
2160 insert_xml_plsql_table( p_xml_data,'/ST_SOCIAL_SECURITY',NULL,'T','C');
2161
2162 END IF;
2163
2164 IF p_show_state = 'Y' THEN
2165
2166 -- write state tax subject AND withheld
2167 dbg('Writing State Level state Details ' ) ;
2168
2169 insert_xml_plsql_table( p_xml_data,'ST_STATE_TAX',NULL,'T','C');
2170 insert_xml_plsql_table( p_xml_data,'ST_STATE_WITHHELD_CTD',
2171 l_st_st_witheld_ctd,'D','B');
2172 insert_xml_plsql_table( p_xml_data,'ST_STATE_SUBJ_CTD',
2173 l_st_st_subj_ctd,'D','B');
2174
2175 IF p_dimension <> 'CTD' THEN
2176
2177 insert_xml_plsql_table( p_xml_data,'ST_STATE_WITHHELD_DTD',
2178 l_st_st_witheld_dtd,'D','B');
2179 insert_xml_plsql_table( p_xml_data,'ST_STATE_SUBJ_DTD',
2180 l_st_st_subj_dtd,'D','B');
2181
2182 END IF;
2183
2184 insert_xml_plsql_table( p_xml_data,'/ST_STATE_TAX',NULL,'T','C');
2185
2186 dbg('The value of The PLSQL counter for st_state_earnings table is '||
2187 st_state_earnings.count);
2188 IF st_state_earnings.count > 0 THEN
2189
2190 -- write state level totals for state earnings
2191
2192 FOR m IN 1 .. st_state_earnings.COUNT
2193 LOOP
2194 insert_xml_plsql_table( p_xml_data,'ST_STATE_DETAILS',NULL,'T','C');
2195 insert_xml_plsql_table( p_xml_data,'ST_STATE_DETAILS_NAME',
2196 st_state_earnings(m).balance_name,'D','C');
2197 insert_xml_plsql_table( p_xml_data,'ST_STATE_DETAILS_SUBJ_CTD',
2198 st_state_earnings(m).state_earnings_ctd_value,'D','B');
2199
2200 IF p_dimension <> 'CTD' THEN
2201
2202 insert_xml_plsql_table( p_xml_data,'ST_STATE_DETAILS_SUBJ_DTD',
2203 st_state_earnings(m).state_earnings_dtd_value,'D','B');
2204
2205 END IF;
2206
2207 insert_xml_plsql_table(p_xml_data,'/ST_STATE_DETAILS',NULL,'T','C');
2208
2209 END LOOP;
2210
2211 END IF;
2212
2213 END IF;
2214
2215 IF p_show_soc_security = 'Y' THEN
2216
2217 FOR m IN 1 .. st_soc_sec_det_tab.COUNT
2218 LOOP
2219
2220 insert_xml_plsql_table( p_xml_data,'ST_SOCIAL_SECURITY_QUOTA',NULL,
2221 'T','C');
2222 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_INS',
2223 st_soc_sec_det_tab(m).balance_name,'D','C');
2224 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_INS_EE_CTD',
2225 st_soc_sec_det_tab(m).soc_sec_quo_ee_ctd,
2226 'D','B');
2227 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_INS_ER_CTD',
2228 st_soc_sec_det_tab(m).soc_sec_quo_er_ctd,
2229 'D','B');
2230
2231 IF p_dimension <> 'CTD' THEN
2232
2233 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_INS_EE_DTD',
2234 st_soc_sec_det_tab(m).soc_sec_quo_ee_dtd,
2235 'D','B');
2236 insert_xml_plsql_table( p_xml_data,'ST_SOC_SEC_INS_ER_DTD',
2237 st_soc_sec_det_tab(m).soc_sec_quo_er_dtd,
2238 'D','B');
2239
2240 END IF;
2241
2242 insert_xml_plsql_table( p_xml_data,'/ST_SOCIAL_SECURITY_QUOTA',
2243 NULL,'T','C');
2244 END LOOP;
2245
2246 END IF;
2247
2248 insert_xml_plsql_table( p_xml_data,'/STATE_TOTAL',NULL,'T','C');
2249 insert_xml_plsql_table( p_xml_data,'/STATE',NULL,'T','C');
2250
2251 dbg('Initializing state level social security insurance type balances');
2252
2253 -- initialize state level soc security ins type balances
2254 IF p_show_soc_security = 'Y' THEN
2255
2256 FOR cnt IN 1 .. st_soc_sec_det_tab.LAST
2257 LOOP
2258
2259 st_soc_sec_det_tab(cnt).balance_name := '';
2260 st_soc_sec_det_tab(cnt).soc_sec_quo_ee_ctd := 0 ;
2261 st_soc_sec_det_tab(cnt).soc_sec_quo_er_ctd := 0 ;
2262 st_soc_sec_det_tab(cnt).soc_sec_quo_ee_dtd := 0 ;
2263 st_soc_sec_det_tab(cnt).soc_sec_quo_er_dtd := 0 ;
2264
2265 END loop ;
2266
2267 END IF;
2268
2269 -- initialize state level earning details
2270
2271 IF p_show_state = 'Y' THEN
2272
2273 dbg('The value of The PLSQL counter for st_state_earnings table is '||
2274 st_state_earnings.count);
2275
2276 IF st_state_earnings.count > 0 THEN
2277
2278 FOR cnt IN 1 .. st_state_earnings.LAST
2279 LOOP
2280
2281 st_state_earnings(cnt).balance_name := '';
2282 st_state_earnings(cnt).state_earnings_ctd_value := 0 ;
2283 st_state_earnings(cnt).state_earnings_dtd_value := 0 ;
2284
2285 END loop ;
2286
2287 END IF ;
2288
2289 END IF;
2290
2291 END LOOP; /* For the Outer Distinct State Loop */
2292
2293 l_step := 12;
2294 hr_utility.set_location(g_package || l_procedure_name, 120);
2295
2296 /* Writing the Legal Employer Level Totals into the PL/SQL data */
2297 /* Legal Employer Gross Earning Data */
2298
2299 dbg('Printing the Legal Employer Details');
2300
2301 insert_xml_plsql_table( p_xml_data,'LEGAL_EMPLOYER_TOTAL',NULL,'T','C');
2302 insert_xml_plsql_table( p_xml_data,'LE_TOTAL_HEAD',
2303 'Legal Employer Total Report','D','C');
2304 insert_xml_plsql_table( p_xml_data,'LE_GROSS',NULL,'T','C');
2305 insert_xml_plsql_table( p_xml_data,'LE_GROSS_SUBJ_CTD',
2306 l_lt_gross_sub_ctd,'D','B');
2307
2308 IF p_dimension <> 'CTD' THEN
2309
2310 insert_xml_plsql_table( p_xml_data,'LE_GROSS_SUBJ_DTD',
2311 l_lt_gross_sub_dtd,'D','B');
2312
2313 END IF;
2314
2315 insert_xml_plsql_table( p_xml_data,'/LE_GROSS',NULL,'T','C');
2316
2317 IF p_show_isr = 'Y' THEN
2318
2319 /* Legal Employer ISR data */
2320 insert_xml_plsql_table( p_xml_data,'LE_ISR',NULL,'T','C');
2321 insert_xml_plsql_table( p_xml_data,'LE_ISR_WITHHELD_CTD',
2322 l_lt_isr_witheld_ctd,'D','B');
2323 insert_xml_plsql_table( p_xml_data,'LE_ISR_SUBJ_CTD',
2324 l_lt_isr_subj_ctd,'D','B');
2325
2326 IF p_dimension <>'CTD' THEN
2327 insert_xml_plsql_table( p_xml_data,'LE_ISR_WITHHELD_DTD',
2328 l_lt_isr_witheld_dtd,'D','B');
2329 insert_xml_plsql_table( p_xml_data,'LE_ISR_SUBJ_DTD',
2330 l_lt_isr_subj_dtd,'D','B');
2331 END IF;
2332
2333 insert_xml_plsql_table( p_xml_data,'/LE_ISR',NULL,'T','C');
2334
2335 END IF;
2336
2337 IF p_show_soc_security ='Y' THEN
2338
2339 /* Legal Employer Social Security Data */
2340 insert_xml_plsql_table( p_xml_data,'LE_SOCIAL_SECURITY',NULL,'T','C');
2341 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_EE_CTD',
2342 l_lt_soc_sec_ee_ctd,'D','B');
2343 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_ER_CTD',
2344 l_lt_soc_sec_er_ctd,'D','B');
2345 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_TOTAL_CTD',
2346 l_lt_soc_sec_tot_ctd,'D','B');
2347
2348 IF p_dimension <> 'CTD' THEN
2349
2350 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_EE_DTD',
2351 l_lt_soc_sec_ee_dtd,'D','B');
2352 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_ER_DTD',
2353 l_lt_soc_sec_er_dtd,'D','B');
2354 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_TOTAL_DTD',
2355 l_lt_soc_sec_tot_dtd,'D','B');
2356
2357 END IF;
2358
2359 insert_xml_plsql_table( p_xml_data,'/LE_SOCIAL_SECURITY',NULL,'T','C');
2360
2361 END IF ;
2362
2363
2364 IF p_show_state ='Y' THEN
2365
2366 -- write legal employer level state tax subject AND withheld
2367
2368 insert_xml_plsql_table( p_xml_data,'LE_STATE_TAX',NULL,'T','C');
2369 insert_xml_plsql_table( p_xml_data,'LE_STATE_WITHHELD_CTD',
2370 l_lt_st_witheld_ctd,'D','B');
2371 insert_xml_plsql_table( p_xml_data,'LE_STATE_SUBJ_CTD',
2372 l_lt_st_subj_ctd,'D','B');
2373
2374 IF p_dimension <> 'CTD' THEN
2375
2376 insert_xml_plsql_table( p_xml_data,'LE_STATE_WITHHELD_DTD',
2377 l_lt_st_witheld_dtd,'D','B');
2378 insert_xml_plsql_table( p_xml_data,'LE_STATE_SUBJ_DTD',
2379 l_lt_st_subj_dtd,'D','B');
2380
2381 END IF;
2382
2383 insert_xml_plsql_table( p_xml_data,'/LE_STATE_TAX',NULL,'T','C');
2384
2385 END IF;
2386
2387 IF p_show_soc_security = 'Y' THEN
2388
2389 l_step := 13;
2390 hr_utility.set_location(g_package || l_procedure_name, 130);
2391
2392 /* Legal Employer social security Quota details */
2393
2394 FOR m IN 1 .. le_soc_sec_det_tab.COUNT
2395 LOOP
2396 insert_xml_plsql_table( p_xml_data,'LE_SOCIAL_SECURITY_QUOTA',
2397 NULL,'T','C');
2398 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_INS',
2399 le_soc_sec_det_tab(m).balance_name,'D','C');
2400 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_INS_EE_CTD',
2401 le_soc_sec_det_tab(m).soc_sec_quo_ee_ctd,
2402 'D','B');
2403 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_INS_ER_CTD',
2404 le_soc_sec_det_tab(m).soc_sec_quo_er_ctd,
2405 'D','B');
2406
2407 IF p_dimension <> 'CTD' THEN
2408
2409 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_INS_EE_DTD',
2410 le_soc_sec_det_tab(m).soc_sec_quo_ee_dtd,
2411 'D','B');
2412 insert_xml_plsql_table( p_xml_data,'LE_SOC_SEC_INS_ER_DTD',
2413 le_soc_sec_det_tab(m).soc_sec_quo_er_dtd,
2414 'D','B');
2415
2416 END IF;
2417
2418 insert_xml_plsql_table( p_xml_data,'/LE_SOCIAL_SECURITY_QUOTA',
2419 NULL,'T','C');
2420 END loop;
2421
2422 END IF;
2423
2424 insert_xml_plsql_table( p_xml_data,'/LEGAL_EMPLOYER_TOTAL',NULL,'T','C');
2425 insert_xml_plsql_table( p_xml_data,'/TRR',NULL,'T','C');
2426
2427 dbg('Exiting Populate plsql table.........');
2428
2429 EXCEPTION
2430 when others THEN
2431 l_error_message := 'Error at step ' || l_step || ' IN ' ||
2432 g_package || l_procedure_name;
2433 dbg(l_error_message || '-' || sqlerrm);
2434 hr_utility.raise_error;
2435
2436 END populate_plsql_table ;
2437
2438 /*****************************************************************************
2439 Name : convert_into_xml
2440 Purpose : function to convert the data into an XML String
2441 *****************************************************************************/
2442
2443 FUNCTION convert_into_xml( p_name IN VARCHAR2,
2444 p_value IN VARCHAR2,
2445 p_type IN char)
2446 RETURN VARCHAR2 IS
2447
2448 l_convert_data VARCHAR2(250);
2449
2450 BEGIN
2451
2452 IF p_type = 'D' THEN
2453
2454 l_convert_data := '<'||p_name||'>'||p_value||'</'||p_name||'>';
2455
2456 ELSE
2457
2458 l_convert_data := '<'||p_name||'>';
2459
2460 END IF;
2461
2462 RETURN(l_convert_data);
2463
2464 END convert_into_xml;
2465
2466
2467 /*****************************************************************************
2468 Name : populate_trr_report
2469 Purpose :
2470 *****************************************************************************/
2471 PROCEDURE populate_trr_report
2472 ( errbuf OUT NOCOPY VARCHAR2,
2473 retcode OUT NOCOPY NUMBER,
2474 p_business_group_id IN NUMBER,
2475 p_start_date_earned IN VARCHAR2,
2476 p_end_date_earned IN VARCHAR2,
2477 p_legal_employer_id IN NUMBER,
2478 p_state_code IN VARCHAR2,
2479 p_gre_id IN NUMBER,
2480 p_show_isr IN VARCHAR2,
2481 p_show_soc_security IN VARCHAR2,
2482 p_show_state IN VARCHAR2,
2483 p_dimension IN VARCHAR2,
2484 p_session_date IN VARCHAR2) IS
2485
2486 xml_data_table xml_data;
2487 l_xml_string VARCHAR2(250);
2488
2489 l_procedure_name VARCHAR2(100);
2490 l_error_message VARCHAR2(200);
2491 l_step NUMBER;
2492 m NUMBER;
2493
2494 BEGIN
2495
2496 g_package := 'pay_mx_trr_pkg' ;
2497 g_debug_flag := 'Y' ;
2498
2499 -- g_concurrent_flag := 'Y' ;
2500
2501 l_procedure_name := '.populate_trr_report';
2502 dbg('Entering Populate TRR Report .........');
2503
2504 dbg('Parameters');
2505 dbg('Business Group Id : '||p_business_group_id);
2506 dbg('Starting Date Earned : '||p_start_date_earned);
2507 dbg('Ending Date Date : '||p_end_date_earned);
2508 dbg('Legal Employer Id : '||p_legal_employer_id);
2509 dbg('State : '||p_state_code);
2510 dbg('Gre Id : '||p_gre_id);
2511 dbg('ISR Tax : '||p_show_isr);
2512 dbg('Social Security Tax : '||p_show_soc_security);
2513 dbg('State Tax : '||p_show_state);
2514 dbg('Dimension : '||p_dimension);
2515 dbg('Session Date : '||p_session_date);
2516
2517 insert into fnd_sessions
2518 (session_id, effective_date)
2519 SELECT userenv('sessionid'),fnd_date.canonical_to_date(p_session_date)
2520 FROM sys.dual
2521 WHERE not exists
2522 (SELECT 1
2523 FROM fnd_sessions fs
2524 WHERE fs.session_id = userenv('sessionid')) ;
2525
2526 l_step := 1;
2527 hr_utility.set_location(g_package || l_procedure_name, 10);
2528 dbg('Calling Populate plsql table');
2529
2530 populate_plsql_table(fnd_date.canonical_to_date(p_start_date_earned) ,
2531 fnd_date.canonical_to_date(p_end_date_earned) ,
2532 p_legal_employer_id ,
2533 p_state_code ,
2534 p_gre_id ,
2535 p_show_isr ,
2536 p_show_soc_security ,
2537 p_show_state ,
2538 p_dimension ,
2539 p_business_group_id ,
2540 xml_data_table );
2541
2542 dbg('After Populate PlSql table procedure');
2543
2544 l_step := 2;
2545 hr_utility.set_location(g_package || l_procedure_name, 20);
2546
2547 dbg('The total records IN PLsql Table is '||xml_data_table.count);
2548
2549 FOR m IN 1 ..xml_data_table.COUNT LOOP
2550
2551 dbg(xml_data_table(m).tag_name ||' '||
2552 xml_data_table(m).tag_type||' '||xml_data_table(m).tag_value);
2553
2554 END LOOP;
2555
2556 -- Write XML header line
2557 dbg('Write XML header line');
2558
2559 l_step := 3;
2560 hr_utility.set_location(g_package || l_procedure_name, 30);
2561
2562 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8" ?>');
2563
2564 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="' ||
2565 hr_mx_utility.get_IANA_charset || '"?>' ) ;
2566
2567 l_step := 4;
2568 hr_utility.set_location(g_package || l_procedure_name, 40);
2569
2570 -- Write XML data FROM plsql table
2571 dbg('Convert AND Write XML data IN the output file');
2572
2573 FOR l IN 1 .. xml_data_table.COUNT
2574 LOOP
2575
2576 l_xml_string := convert_into_xml(xml_data_table(l).tag_name,
2577 xml_data_table(l).tag_value,
2578 xml_data_table(l).tag_type);
2579 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
2580
2581 END LOOP ;
2582
2583
2584 l_step := 5;
2585 hr_utility.set_location(g_package || l_procedure_name, 50);
2586
2587 dbg('cleaning up the plsql table');
2588
2589 xml_data_table.delete;
2590
2591 dbg('Exiting Populate TRR Report .........');
2592
2593 END populate_trr_report ; --End for the Procedure Body populate_trr_report
2594
2595
2596 /*****************************************************************************
2597 Name : trr_report_wrapper
2598 Purpose :
2599 *****************************************************************************/
2600 PROCEDURE trr_report_wrapper
2601 ( errbuf OUT NOCOPY VARCHAR2,
2602 retcode OUT NOCOPY NUMBER,
2603 p_business_group_id IN NUMBER,
2604 p_start_date_earned IN VARCHAR2,
2605 p_end_date_earned IN VARCHAR2,
2606 p_legal_employer_id IN NUMBER,
2607 p_state_code IN VARCHAR2,
2608 p_gre_id IN NUMBER,
2609 p_show_isr IN VARCHAR2,
2610 p_show_soc_security IN VARCHAR2,
2611 p_show_state IN VARCHAR2,
2612 p_dimension IN VARCHAR2,
2613 p_template IN VARCHAR2,
2614 p_template_locale IN VARCHAR2,
2615 p_session_date IN VARCHAR2
2616 ) IS
2617
2618 l_req_id NUMBER;
2619 l_req_id2 NUMBER;
2620 l_program VARCHAR2(100);
2621 l_err_msg VARCHAR2(240);
2622 l_wait_outcome BOOLEAN;
2623 l_phase VARCHAR2(80);
2624 l_status VARCHAR2(80);
2625 l_dev_phase VARCHAR2(80);
2626 l_dev_status VARCHAR2(80);
2627 l_message VARCHAR2(80);
2628 l_errbuf VARCHAR2(240);
2629
2630 l_procedure_name VARCHAR2(100);
2631 l_error_message VARCHAR2(200);
2632 l_step NUMBER;
2633
2634 /* adding a new variable. */
2635 l_arg1_result VARCHAR2(10);
2636 l_arg7_result VARCHAR2(10);
2637
2638 CURSOR get_l_arg1_result IS
2639 SELECT XDO_CP_DATA_SECURITY_PKG.GET_CONCURRENT_REQUEST_IDS FROM DUAL;
2640
2641 CURSOR get_l_arg7_result IS
2642 select template_type_code from xdo_templates_vl where template_code = p_template and application_short_name = (select application_short_name from fnd_application_vl where application_id = 801);
2643
2644 BEGIN
2645
2646 g_package := 'pay_mx_trr_pkg' ;
2647
2648 g_debug_flag := 'Y' ;
2649 -- g_concurrent_flag := 'Y' ;
2650
2651 l_procedure_name := '.trr_report_wrapper';
2652
2653 dbg('Entering TRR Report wrapper.........');
2654 dbg('Parameters');
2655 dbg('Business Group Id : '||p_business_group_id);
2656 dbg('Starting Date Earned : '||p_start_date_earned);
2657 dbg('Ending Date Date : '||p_end_date_earned);
2658 dbg('Legal Employer Id : '||p_legal_employer_id);
2659 dbg('State : '||p_state_code);
2660 dbg('Gre Id : '||p_gre_id);
2661 dbg('ISR Tax : '||p_show_isr);
2662 dbg('Social Security Tax : '||p_show_soc_security);
2663 dbg('State Tax : '||p_show_state);
2664 dbg('Dimension : '||p_dimension);
2665 dbg('Template : '||p_template);
2666 dbg('Template Locale : '||p_template_locale);
2667 dbg('Session Date : '||p_session_date);
2668
2669 dbg('Submitting concurrent request for Payroll Tax Remittance Report');
2670
2671 l_step := 1;
2672 hr_utility.set_location(g_package || l_procedure_name, 10);
2673
2674 l_program := 'PYMXTRRXML';
2675 l_req_id := Fnd_request.submit_request(
2676 application => 'PAY',
2677 program => l_program,
2678 argument1 => p_business_group_id,
2679 argument2 => p_start_date_earned,
2680 argument3 => p_end_date_earned,
2681 argument4 => p_legal_employer_id,
2682 argument5 => p_state_code,
2683 argument6 => p_gre_id,
2684 argument7 => p_show_isr,
2685 argument8 => p_show_soc_security,
2686 argument9 => p_show_state,
2687 argument10 => p_dimension,
2688 argument11 => p_session_date );
2689
2690 dbg('Request Id for Payroll Tax Remittance Report is '||l_req_id);
2691
2692 If l_req_id = 0 THEN
2693
2694 fnd_message.retrieve(l_errbuf);
2695 dbg('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
2696 hr_utility.raise_error;
2697
2698 ELSE
2699
2700 dbg('Waiting for the First Request to get complete');
2701 COMMIT;
2702 dbg('Commited the First Request');
2703
2704 l_step := 2;
2705 hr_utility.set_location(g_package || l_procedure_name, 20);
2706
2707 l_wait_outcome := fnd_concurrent.WAIT_FOR_REQUEST(
2708 request_id => l_req_id,
2709 interval => 15,
2710 max_wait => 2400, -- 180,
2711 phase => l_phase,
2712 status => l_status,
2713 dev_phase => l_dev_phase,
2714 dev_status => l_dev_status,
2715 message => l_message);
2716 END IF;
2717
2718 dbg('status is '||l_status);
2719 dbg('The status of Development Phase is '||l_dev_phase);
2720 dbg('dev status is '||l_dev_status);
2721
2722 /* argument3 hard coded as 801
2723 need to take FROM by setting the l_req_id
2724 SELECT FCP.APPLICATION_ID
2725 FROM FND_CONCURRENT_PROGRAMS FCP,FND_CONCURRENT_REQUESTS R
2726 WHERE FCP.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID AND
2727 R.REQUEST_ID = :$FLEX$.XDO_REPORT_REQUEST_ID
2728 */
2729
2730 IF l_req_id > 0 AND l_dev_phase ='COMPLETE' THEN
2731
2732 dbg('Submitting XML Report Publisher concurrent request');
2733
2734 l_step := 3;
2735 hr_utility.set_location(g_package || l_procedure_name, 30);
2736
2737 l_program := 'XDOREPPB';
2738
2739 /* assigning the argument1 value to l_sql_arg1 */
2740 OPEN get_l_arg1_result;
2741 FETCH get_l_arg1_result INTO l_arg1_result;
2742 CLOSE get_l_arg1_result;
2743 /* assigning the argument7 value to l_sql_arg7 */
2744 OPEN get_l_arg7_result;
2745 FETCH get_l_arg7_result INTO l_arg7_result;
2746 CLOSE get_l_arg7_result;
2747
2748 l_req_id2 := fnd_request.submit_request(
2749 application => 'XDO',
2750 program => l_program,
2751 argument1 => l_arg1_result,
2752 argument2 => l_req_id,
2753 argument3 => 801, --'PAY',
2754 argument4 => p_template,
2755 argument5 => p_template_locale,
2756 argument6 => 'N',
2757 argument7 => l_arg7_result,
2758 argument8 => 'PDF');
2759 ELSE
2760
2761 fnd_message.retrieve(l_errbuf);
2762 dbg('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
2763
2764 END IF;
2765
2766 IF l_req_id2 > 0 THEN
2767
2768 Commit;
2769
2770 ELSE
2771
2772 fnd_message.retrieve(l_errbuf);
2773 dbg('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
2774 -- Will Raise an User Defined Error
2775
2776 END IF;
2777
2778 dbg('Exiting TRR Report wrapper.........');
2779
2780 EXCEPTION
2781 when others THEN
2782 l_error_message := 'Error at step ' || l_step || ' IN ' ||
2783 g_package || l_procedure_name;
2784 dbg(l_error_message || '-' || sqlerrm);
2785 hr_utility.raise_error;
2786
2787 End trr_report_wrapper ; -- End Of Procedure TRR_Report_wrapper
2788
2789 --begin
2790 --hr_utility.trace_on (null, 'MXTRR');
2791
2792 END pay_mx_trr_pkg; -- End Of Package Body