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