[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_EOY_REPORTS
Source
1 PACKAGE BODY pay_in_eoy_reports AS
2 /* $Header: pyineoyr.pkb 120.21.12010000.3 2008/08/06 07:26:46 ubhat ship $ */
3 g_tmp_clob CLOB;
4 g_clob_cnt NUMBER;
5 g_fetch_clob_cnt NUMBER;
6 g_chunk_size NUMBER;
7 g_business_group_id NUMBER;
8 -- g_package VARCHAR2(100);
9 g_assessment_year VARCHAR2(20);
10 g_tax_year VARCHAR2(20);
11 g_tax_end_date DATE;
12 g_tax_start_date DATE;
13
14 g_index NUMBER;
15 g_index_16aa NUMBER;
16 g_debug BOOLEAN ;
17 g_package CONSTANT VARCHAR2(100) := 'pay_in_eoy_reports.';
18
19 TYPE record_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
20 t_table_1 record_type;
21 t_table_surcharge record_type;
22 t_table_ec record_type;
23
24 TYPE XMLRec
25 IS RECORD
26 (
27 Bank VARCHAR2(2000),
28 VDate VARCHAR2(240),
29 VNumber VARCHAR2(240),
30 DDCheque_Num VARCHAR2(240)
31 );
32 TYPE tXMLTable IS TABLE OF XMLRec INDEX BY BINARY_INTEGER;
33 g_Bank_Details_tbl tXMLTable;
34
35 TYPE XMLRec_16aa
36 IS RECORD
37 (
38 Bank VARCHAR2(2000),
39 VDate VARCHAR2(240),
40 VNumber VARCHAR2(240)
41 );
42 TYPE tXMLTable_16aa IS TABLE OF XMLRec_16aa INDEX BY BINARY_INTEGER;
43 g_Bank_Details_tbl_16aa tXMLTable_16aa;
44
45
46 TYPE Emp_XMLRec
47 IS RECORD
48 (
49 emp_tds VARCHAR2(2000),
50 emp_sur VARCHAR2(240),
51 emp_cess VARCHAR2(240),
52 emp_voucher VARCHAR2(240),
53 emp_amount VARCHAR2(240)
54 );
55 TYPE Emp_tXMLTable IS TABLE OF Emp_XMLRec INDEX BY BINARY_INTEGER;
56 g_emp_challan_details_tbl Emp_tXMLTable;
57
58 TYPE Emp_XMLRec_16aa
59 IS RECORD
60 (
61 emp_voucher VARCHAR2(240),
62 emp_amount VARCHAR2(240)
63 );
64 TYPE Emp_tXMLTable_16aa IS TABLE OF Emp_XMLRec_16aa INDEX BY BINARY_INTEGER;
65 g_emp_challan_details_tbl_16aa Emp_tXMLTable_16aa;
66
67 g_salary_record pay_in_xml_utils.tXMLTable;
68 g_Other_Income_tbl pay_in_xml_utils.tXMLTable;
69 p_rem_pay_period NUMBER;
70 p_flag NUMBER;
71
72 TYPE clob_tab_type IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
73
74 TYPE perq_record IS RECORD
75 ( perq_value1 pay_action_information.action_information1%TYPE
76 , perq_value2 pay_action_information.action_information1%TYPE
77 );
78
79 TYPE t_perq_record is table of perq_record INDEX BY BINARY_INTEGER;
80
81 g_perq_record t_perq_record;
82 g_clob clob_tab_type;
83
84 g_80cce_limit NUMBER;
85
86
87
88 --------------------------------------------------------------------------
89 -- --
90 -- Name : init_form12ba_code --
91 -- Type : PROCEDURE --
92 -- Access : Private --
93 -- Description : This procedure initializes the form12ba record --
94 -- --
95 -- Parameters : None --
96 --------------------------------------------------------------------------
97 PROCEDURE init_form12ba_code
98 IS
99 l_procedure varchar2(100);
100
101 BEGIN
102 g_debug := hr_utility.debug_enabled;
103 l_procedure := g_package||'init_form12ba_code';
104 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
105
106 g_perq_record(1).perq_value1 := 0;
107 g_perq_record(1).perq_value2 := 0;
108
109 g_perq_record(2).perq_value1 := 0;
110 g_perq_record(2).perq_value2 := 0;
111
112 g_perq_record(3).perq_value1 := 0;
113 g_perq_record(3).perq_value2 := 0;
114
115 g_perq_record(4).perq_value1 := 0;
116 g_perq_record(4).perq_value2 := 0;
117
118 g_perq_record(5).perq_value1 := 0;
119 g_perq_record(5).perq_value2 := 0;
120
121 g_perq_record(6).perq_value1 := 0;
122 g_perq_record(6).perq_value2 := 0;
123
124 g_perq_record(7).perq_value1 := 0;
125 g_perq_record(7).perq_value2 := 0;
126
127 g_perq_record(8).perq_value1 := 0;
128 g_perq_record(8).perq_value2 := 0;
129
130 g_perq_record(9).perq_value1 := 0;
131 g_perq_record(9).perq_value2 := 0;
132
133 g_perq_record(10).perq_value1 := 0;
134 g_perq_record(10).perq_value2 := 0;
135
136 g_perq_record(11).perq_value1 := 0;
137 g_perq_record(11).perq_value2 := 0;
138
139 g_perq_record(12).perq_value1 := 0;
140 g_perq_record(12).perq_value2 := 0;
141
142 g_perq_record(13).perq_value1 := 0;
143 g_perq_record(13).perq_value2 := 0;
144
145 g_perq_record(14).perq_value1 := 0;
146 g_perq_record(14).perq_value2 := 0;
147
148 g_perq_record(15).perq_value1 := 0;
149 g_perq_record(15).perq_value2 := 0;
150
151 g_perq_record(16).perq_value1 := 0;
152 g_perq_record(16).perq_value2 := 0;
153
154 g_perq_record(17).perq_value1 := 0;
155 g_perq_record(17).perq_value2 := 0;
156
157 g_perq_record(18).perq_value1 := 0;
158 g_perq_record(18).perq_value2 := 0;
159
160 g_perq_record(19).perq_value1 := 0;
161 g_perq_record(19).perq_value2 := 0;
162
163 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
164
165 END init_form12ba_code;
166
167 --------------------------------------------------------------------------
168 -- --
169 -- Name : GET_MAX_CONTEXT_ID --
170 -- Type : PROCEDURE --
171 -- Access : Private --
172 -- Description : This procedure gets the maximum action context id --
173 -- for a specified GRE in an assessment year --
174 -- --
175 -- Parameters : --
176 -- IN : p_gre_id NUMBER --
177 -- : p_assessment_year VARCHAR2 --
178 --------------------------------------------------------------------------
179 FUNCTION get_max_context_id ( p_gre_id IN hr_organization_units.organization_id%TYPE
180 , p_assessment_year IN pay_action_information.action_information3%TYPE
181 )
182 RETURN NUMBER
183 IS
184 --
185 CURSOR csr_max_action_context_id
186 IS
187 SELECT MAX(pai.action_context_id)
188 FROM pay_action_information pai
189 WHERE pai.action_information_category ='IN_EOY_ORG'
190 AND pai.action_context_type = 'PA'
191 AND pai.Action_information1 = p_gre_id
192 AND pai.action_information3 = p_assessment_year;
193 --
194 l_procedure VARCHAR2(100);
195 l_action_context_id pay_assignment_actions.assignment_action_id%TYPE;
196 --
197 BEGIN
198 g_debug := hr_utility.debug_enabled;
199 l_procedure := g_package ||'get_max_context_id';
200 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
201
202
203 IF g_debug THEN
204 pay_in_utils.trace('**************************************************','********************');
205 pay_in_utils.trace('p_gre_id',to_char(p_gre_id));
206 pay_in_utils.trace('p_assessment_year',to_char(p_assessment_year));
207 pay_in_utils.trace('**************************************************','********************');
208 END IF;
209
210 OPEN csr_max_action_context_id;
211 FETCH csr_max_action_context_id INTO l_action_context_id;
212 CLOSE csr_max_action_context_id;
213
214 IF g_debug THEN
215 pay_in_utils.trace('l_action_context_id',l_action_context_id);
216 END IF;
217 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
218 RETURN l_action_context_id;
219
220 END get_max_context_id;
221
222 --------------------------------------------------------------------------
223 -- --
224 -- Name : GET_LOCATION_DETAILS --
225 -- Type : FUNCTION --
226 -- Access : Private --
227 -- Description : This procedure gets the gre location details --
228 -- --
229 -- Parameters : --
230 -- IN : p_location_id hr_locations.location_id --
231 -- : p_concatenate VARCHAR2 --
232 -- p_field VARCHAR2 --
233 --------------------------------------------------------------------------
234 FUNCTION get_location_details ( p_location_id IN hr_locations.location_id%TYPE
235 , p_concatenate IN VARCHAR2 DEFAULT 'N'
236 , p_field IN VARCHAR2 DEFAULT NULL
237 )
238 RETURN VARCHAR2
239 IS
240
241 CURSOR csr_get_location_details
242 IS
243 SELECT hr_loc.address_line_1
244 , hr_loc.address_line_2
245 , hr_loc.address_line_3
246 , hr_loc.loc_information14
247 , hr_loc.loc_information15
248 , hr_general.decode_lookup('IN_STATES',hr_loc.loc_information16)
249 , hr_general.decode_lookup('PER_US_COUNTRY_CODE',hr_loc.country)
250 , hr_loc.postal_code
251 , hr_loc.loc_information16
252 FROM hr_locations hr_loc
253 WHERE location_id = p_location_id;
254
255 l_procedure VARCHAR2(100);
256 l_location_address1 hr_locations.address_line_1%TYPE;
257 l_location_address2 hr_locations.address_line_2%TYPE;
258 l_location_address3 hr_locations.address_line_3%TYPE;
259 l_location_address4 hr_locations.loc_information14%TYPE;
260 l_location_city hr_locations.loc_information15%TYPE;
261 l_location_state hr_locations.loc_information16%TYPE;
262 l_location_country hr_locations.country%TYPE;
263 l_location_zipcode hr_locations.postal_code%TYPE;
264 l_state_code hr_locations.loc_information16%TYPE;
265 l_details VARCHAR2(1000);
266
267 BEGIN
268 g_debug := hr_utility.debug_enabled;
269 l_procedure := g_package ||'get_location_details';
270 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
271
272
273 IF g_debug THEN
274 pay_in_utils.trace('**************************************************','********************');
275 pay_in_utils.trace('p_location_id',to_char(p_location_id));
276 pay_in_utils.trace('p_concatenate',p_concatenate);
277 pay_in_utils.trace('p_field', p_field);
278 pay_in_utils.trace('**************************************************','********************');
279 END IF;
280
281 OPEN csr_get_location_details;
282 FETCH csr_get_location_details
283 INTO l_location_address1
284 , l_location_address2
285 , l_location_address3
286 , l_location_address4
287 , l_location_city
288 , l_location_state
289 , l_location_country
290 , l_location_zipcode
291 , l_state_code;
292 CLOSE csr_get_location_details;
293
294 IF p_concatenate = 'Y' THEN
295
296 SELECT l_location_address1 || DECODE(l_location_address1,NULL,NULL,',' || fnd_global.local_chr(10)) ||
297 l_location_address2 || DECODE(l_location_address2,NULL,NULL,',' || fnd_global.local_chr(10)) ||
298 l_location_address3 || DECODE(l_location_address3,NULL,NULL,',' || fnd_global.local_chr(10)) ||
299 l_location_address4 || DECODE(l_location_address4,NULL,NULL,',' || fnd_global.local_chr(10)) ||
300 l_location_city || DECODE(l_location_city ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
301 l_location_state || DECODE(l_location_state ,NULL,NULL,',')||
302 l_location_country || DECODE(l_location_country ,NULL,NULL,',')||
303 l_location_zipcode
304 INTO l_details
305 FROM DUAL;
306
307 ELSIF p_field = 'EMPLOYER_ADDRESS1' THEN
308 l_details := l_location_address1;
309 ELSIF p_field = 'EMPLOYER_ADDRESS2' THEN
310 l_details := l_location_address2;
311 ELSIF p_field = 'EMPLOYER_ADDRESS3' THEN
312 l_details := l_location_address3;
313 ELSIF p_field = 'EMPLOYER_ADDRESS4' THEN
314 l_details := l_location_address4;
315 ELSIF p_field = 'CITY' THEN
316 l_details := l_location_city;
317 ELSIF p_field = 'EMPLOYER_STATE' THEN
318 l_details := l_location_state;
319 ELSIF p_field = 'EMPLOYER_STATE_CODE' THEN
320 l_details := l_state_code;
321 ELSIF p_field = 'POSTAL_CODE' THEN
322 l_details := l_location_zipcode;
323 ELSIF p_field = 'COUNTRY' THEN
324 l_details := l_location_country;
325 END IF;
326 l_details :=RTRIM(l_details,fnd_global.local_chr(10));
327 l_details :=RTRIM(l_details,',');
328
329 IF g_debug THEN
330 pay_in_utils.trace('l_details', l_details );
331 END IF;
332
333 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
334
335 RETURN l_details;
336
337 END get_location_details;
338
339 --------------------------------------------------------------------------
340 -- --
341 -- Name : GET_ADDRESS_DETAILS --
342 -- Type : FUNCTION --
343 -- Access : Private --
344 -- Description : This procedure gets the employee address details --
345 -- --
346 -- Parameters : --
347 -- IN : p_location_id per_addresses.address_id --
348 -- : p_concatenate VARCHAR2 --
349 -- p_field VARCHAR2 --
350 --------------------------------------------------------------------------
351 FUNCTION get_address_details ( p_address_id IN per_addresses.address_id%TYPE
352 , p_concatenate IN VARCHAR2 DEFAULT 'N'
353 , p_field IN VARCHAR2 DEFAULT NULL
354 )
355 RETURN VARCHAR2
356 IS
357
358 CURSOR csr_get_address_details
359 IS
360 SELECT pad.address_line1
361 , pad.address_line2
362 , pad.address_line3
363 , pad.add_information13
364 , pad.add_information14
365 , hr_general.decode_lookup('IN_STATES',pad.add_information15)
366 , hr_general.decode_lookup('PER_US_COUNTRY_CODE',pad.country)
367 , pad.postal_code
368 FROM per_addresses pad
369 WHERE pad.address_id = p_address_id;
370
371 l_procedure VARCHAR2(100);
372 l_location_address1 hr_locations.address_line_1%TYPE;
373 l_location_address2 hr_locations.address_line_2%TYPE;
374 l_location_address3 hr_locations.address_line_3%TYPE;
375 l_location_address4 hr_locations.loc_information14%TYPE;
376 l_location_city hr_locations.loc_information15%TYPE;
377 l_location_state hr_locations.loc_information16%TYPE;
378 l_location_country hr_locations.country%TYPE;
379 l_location_zipcode hr_locations.postal_code%TYPE;
380 l_details VARCHAR2(1000);
381
382 BEGIN
383 g_debug := hr_utility.debug_enabled;
384 l_procedure := g_package ||'get_location_details';
385 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
386
387 IF g_debug THEN
388 pay_in_utils.trace('**************************************************','********************');
389 pay_in_utils.trace('p_address_id ',p_address_id );
393 END IF;
390 pay_in_utils.trace('p_concatenate',p_concatenate);
391 pay_in_utils.trace('p_field' ,p_field );
392 pay_in_utils.trace('**************************************************','********************');
394
395 OPEN csr_get_address_details;
396 FETCH csr_get_address_details
397 INTO l_location_address1
398 , l_location_address2
399 , l_location_address3
400 , l_location_address4
401 , l_location_city
402 , l_location_state
403 , l_location_country
404 , l_location_zipcode;
405 CLOSE csr_get_address_details;
406
407 IF p_concatenate = 'Y' THEN
408
409 SELECT l_location_address1 || DECODE(l_location_address1,NULL,NULL,',' || fnd_global.local_chr(10)) ||
410 l_location_address2 || DECODE(l_location_address2,NULL,NULL,',' || fnd_global.local_chr(10)) ||
411 l_location_address3 || DECODE(l_location_address3,NULL,NULL,',' || fnd_global.local_chr(10)) ||
412 l_location_address4 || DECODE(l_location_address4,NULL,NULL,',' || fnd_global.local_chr(10)) ||
413 l_location_city || DECODE(l_location_city ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
414 l_location_state || DECODE(l_location_state ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
415 l_location_country
416 INTO l_details
417 FROM DUAL;
418
419 ELSIF p_field = 'ADDRESS1' THEN
420 l_details := l_location_address1;
421 ELSIF p_field = 'ADDRESS2' THEN
422 l_details := l_location_address2;
423 ELSIF p_field = 'ADDRESS3' THEN
424 l_details := l_location_address3;
425 ELSIF p_field = 'ADDRESS4' THEN
426 l_details := l_location_address4;
427 ELSIF p_field = 'CITY' THEN
428 l_details := l_location_city;
429 ELSIF p_field = 'STATE' THEN
430 l_details := l_location_state;
431 ELSIF p_field = 'POSTAL_CODE' THEN
432 l_details := l_location_zipcode;
433 ELSIF p_field = 'COUNTRY' THEN
434 l_details := l_location_country;
435 END IF;
436
437 l_details :=RTRIM(l_details,','||fnd_global.local_chr(10));
438
439
440
441 IF g_debug THEN
442 pay_in_utils.trace('l_details',l_details);
443 END IF;
444
445 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
446 RETURN l_details;
447
448 END get_address_details;
449
450 --------------------------------------------------------------------------
451 -- --
452 -- Name : WRITE_TAG --
453 -- Type : PROCEDURE --
454 -- Access : Public --
455 -- Description : This procedure appends the tag --
456 -- --
457 -- Parameters : --
458 -- IN : p_tag_name VARCHAR2 --
459 -- p_tag_value VARCHAR2 --
460 --------------------------------------------------------------------------
461 PROCEDURE write_tag ( p_tag_name IN VARCHAR2
462 , p_tag_value IN VARCHAR2)
463 IS
464 l_tag VARCHAR2(10000);
465 l_procedure varchar2(100);
466
467 BEGIN
468 g_debug := hr_utility.debug_enabled;
469 l_procedure := g_package ||'write_tag';
470 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
471
472 IF g_debug THEN
473 pay_in_utils.trace('**************************************************','********************');
474 pay_in_utils.trace('p_tag_name ',p_tag_name );
475 pay_in_utils.trace('p_tag_value',p_tag_value);
476 pay_in_utils.trace('**************************************************','********************');
477 END IF;
478
479 l_tag := pay_in_xml_utils.getTag( p_tag_name => p_tag_name
480 , p_tag_value => p_tag_value
481 );
482
483 dbms_lob.writeAppend(g_tmp_clob,length(l_tag),l_tag);
484 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
485 END write_tag;
486
487 --------------------------------------------------------------------------
488 -- --
489 -- Name : BUILD_GRE_XML --
490 -- Type : PROCEDURE --
491 -- Access : Public --
492 -- Description : This procedure builds the XML for GRE --
493 -- --
494 -- Parameters : --
495 -- IN : p_gre_id NUMBER --
496 --------------------------------------------------------------------------
497 PROCEDURE build_gre_xml ( p_gre_id IN hr_organization_units.organization_id%TYPE)
498 IS
499 CURSOR csr_get_gre_details (p_action_context_id NUMBER)
500 IS
501 SELECT 'ER_LEGAL' er_legal
502 , UPPER(action_information8) er_legal_value
503 , 'ER_ADDRESS' er_address
504 , get_location_details ( TO_NUMBER(action_information7)
508 , 'TAN' er_tan
505 , 'Y') er_address_value
506 , 'ER_NAME' er_org
507 , UPPER(action_information6) er_org_value
509 , UPPER(action_information4) er_tan_value
510 , 'GIR' er_gir
511 , UPPER(action_information2) er_gir_value
512 , 'TDS_CIRCLE' er_tds
513 , UPPER(action_information9) er_tds_value
514 , 'REP_NAME' rep_name
515 , UPPER(Action_information11) rep_name_value
516 , 'REP_TITLE_NAME' rep_title_name
517 , Upper(Action_information12) || Upper(Action_information11) rep_title_value
518 , 'REP_FATHER_NAME' rep_father_name
519 , UPPER(Action_information14) rep_father_value
520 , 'REP_POSITION' rep_designation
521 , Action_information13 rep_designation_value
522 , 'PLACE' gre_place
523 , pay_in_eoy_reports.get_location_details ( TO_NUMBER(action_information7)
524 , NULL
525 , 'CITY') gre_place_value
526 FROM pay_action_information
527 WHERE action_context_id = p_action_context_id
528 AND action_information_category = 'IN_EOY_ORG'
529 AND action_information1 = p_gre_id
530 AND ROWNUM =1;
531
532 --
533 -- Bug # 4506944 : Changed the cursor to include Bank Code instead of Bank Name and Branch,
534 -- Cheque /DD No and Transfer Voucher Number
535 --
536 CURSOR csr_bank_pymt IS
537 ((SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
538 ,hoi_bank.org_information4 Bank
539 ,hoi_challan.org_information3 Voucher_Num
540 ,hoi_challan.org_information11 DD_Cheque_Num
541 FROM hr_organization_information hoi_bank
542 ,hr_organization_information hoi_challan
543 WHERE hoi_bank.organization_id = p_gre_id
544 AND hoi_challan.organization_id = hoi_bank.organization_id
545 AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
546 AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
547 AND hoi_bank.org_information_id = hoi_challan.org_information5
548 AND hoi_challan.org_information12 = 'N'
549 AND hoi_challan.org_information1 = to_char(to_number(substr(g_assessment_year, 1,4))-1)||'-'||to_char(to_number(substr(g_assessment_year, 6,4))-1)
550 )
551 UNION ALL
552 (SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
553 ,hoi_challan.org_information5 Bank
554 ,hoi_challan.org_information3 Voucher_Num
555 ,hoi_challan.org_information11 DD_Cheque_Num
556 FROM hr_organization_information hoi_challan
557 WHERE hoi_challan.organization_id = p_gre_id
558 AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
559 AND hoi_challan.org_information12 = 'Y'
560 AND hoi_challan.org_information5 is null
561 AND hoi_challan.org_information6 is null
562 AND hoi_challan.org_information1 = to_char(to_number(substr(g_assessment_year, 1,4))-1)||'-'||to_char(to_number(substr(g_assessment_year, 6,4))-1)
563 )) ORDER BY Payment_Date;
564
565 --Cursor to get challan details for Form16 AA
566
567 CURSOR csr_bank_pymt_16aa IS
568 ((SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
569 ,hr_general.decode_lookup('IN_BANK',hoi_bank.org_information1)||','||hr_general.decode_lookup('IN_BANK_BRANCH',hoi_bank.org_information2) Bank
570 ,hoi_challan.org_information3 Voucher_Num
571 FROM hr_organization_information hoi_bank
572 ,hr_organization_information hoi_challan
573 WHERE hoi_bank.organization_id = p_gre_id
574 AND hoi_challan.organization_id = hoi_bank.organization_id
575 AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
576 AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
577 AND hoi_bank.org_information_id = hoi_challan.org_information5
578 AND hoi_challan.org_information12 = 'N'
579 AND hoi_challan.org_information1 = to_char(to_number(substr(g_assessment_year, 1,4))-1)||'-'||to_char(to_number(substr(g_assessment_year, 6,4))-1)
580 )
581 UNION ALL
582 (SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
583 ,hoi_challan.org_information5 Bank
584 ,hoi_challan.org_information3 Voucher_Num
585 FROM hr_organization_information hoi_challan
586 WHERE hoi_challan.organization_id = p_gre_id
587 AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
588 AND hoi_challan.org_information12 = 'Y'
589 AND hoi_challan.org_information5 is null
590 AND hoi_challan.org_information6 is null
591 AND hoi_challan.org_information1 = to_char(to_number(substr(g_assessment_year, 1,4))-1)||'-'||to_char(to_number(substr(g_assessment_year, 6,4))-1)
592 )) ORDER BY Payment_Date;
593
594 CURSOR csr_form24q_receipt IS
595 SELECT org_information2 quarter,DECODE(org_information6,'O',' Regular','C',' Correction') Nature,
596 org_information4 receipt
597 FROM hr_organization_information
598 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
599 AND org_information1 = g_assessment_year
600 AND organization_id = p_gre_id
601 ORDER BY quarter;
602
603
604 l_pymt_date VARCHAR2(240);
605 l_procedure VARCHAR2(100);
606 l_open_tag VARCHAR2(100);
607 l_last_quarter VARCHAR2(10);
608 l_action_context_id pay_assignment_actions.assignment_action_id%TYPE;
609
610
611 BEGIN
612 g_debug := hr_utility.debug_enabled;
613 l_procedure := g_package ||'build_gre_xml';
614 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
615
616
617 g_Bank_Details_tbl.DELETE;
618 g_Bank_Details_tbl_16aa.DELETE;
619
620 IF g_debug THEN
621 pay_in_utils.trace('p_gre_id ',to_char(p_gre_id ));
622 END IF;
623
624 l_action_context_id := get_max_context_id(p_gre_id,g_assessment_year);
625
626 FOR i IN csr_get_gre_details(l_action_context_id)
627 LOOP
628 write_tag(i.er_legal,i.er_legal_value);
629 write_tag(i.er_address,UPPER(i.er_address_value));
630 write_tag(i.er_org,i.er_org_value);
631 write_tag(i.er_tan,i.er_tan_value);
632 write_tag(i.er_gir,i.er_gir_value);
633 write_tag(i.er_tds,i.er_tds_value);
634 write_tag(i.rep_name,i.rep_name_value);
635 write_tag(i.rep_title_name,i.rep_title_value);
636 write_tag(i.rep_father_name,i.rep_father_value);
637 write_tag(i.rep_designation,i.rep_designation_value);
638 write_tag(i.gre_place,i.gre_place_value);
639 END LOOP;
640 --
641 -- Bug 4506944 : Changed as part of changes to be done to Form 16/16AA
642 --
643 g_index := 0;
644 g_index_16aa := 0;
645 FOR i IN csr_bank_pymt
646 LOOP
647 l_pymt_date:= TO_CHAR(i.Payment_date,'DD/MM/YYYY');
648
649 g_index := g_index + 1;
650 g_Bank_Details_tbl(g_index).VDate := l_pymt_date;
651 g_Bank_Details_tbl(g_index).Bank := i.Bank;
652 g_Bank_Details_tbl(g_index).DDCheque_Num := i.DD_Cheque_Num;
653 g_Bank_Details_tbl(g_index).VNumber := i.Voucher_Num;
654 END LOOP;
655
656 FOR i IN csr_bank_pymt_16aa
657 LOOP
658 g_index_16aa := g_index_16aa + 1;
659 g_Bank_Details_tbl_16aa(g_index_16aa).VDate := i.Payment_date;
660 g_Bank_Details_tbl_16aa(g_index_16aa).VNumber := i.Voucher_Num;
661 g_Bank_Details_tbl_16aa(g_index_16aa).Bank := i.Bank;
662 END LOOP;
663
664 l_last_quarter :='N';
665 FOR i in csr_form24q_receipt
666 LOOP
667 l_open_tag := '<Receipt>';
668 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
669
670 write_tag('QR',i.Quarter||i.Nature);
671 write_tag('RCPT',i.Receipt);
672 IF i.Quarter = 'Q4' THEN
673 l_last_quarter := 'Y';
674 END IF;
675 l_open_tag := '</Receipt>';
676 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
677 END LOOP;
678
679 IF (l_last_quarter = 'N' AND SYSDATE >= g_tax_end_date ) THEN
680
681 l_open_tag := '<Receipt>';
682 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
683
684 write_tag('QR','Q4');
685 write_tag('RCPT','Not Available as the last Quarterly Statement is yet to be furnished');
686 l_open_tag := '</Receipt>';
687 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
688
689
690 END IF;
691
692 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
693
694 EXCEPTION
695
696 WHEN OTHERS THEN
697 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
698 RAISE;
699
700 END build_gre_xml;
701
702 --------------------------------------------------------------------------
703 -- --
704 -- Name : BUILD_EMPLOYEE_XML --
705 -- Type : PROCEDURE --
709 -- Parameters : --
706 -- Access : Public --
707 -- Description : This procedure builds the XML for Employee --
708 -- --
710 -- IN : p_action_context_id NUMBER --
711 -- p_source_id NUMBER --
712 -- p_rem_pay_period NUMBER --
713 -- p_flag NUMBER --
714 --------------------------------------------------------------------------
715 PROCEDURE build_employee_xml (p_action_context_id IN pay_assignment_actions.assignment_action_id%TYPE
716 ,p_source_id IN pay_payroll_actions.payroll_action_id%TYPE
717 ,p_rem_pay_period OUT NOCOPY NUMBER
718 ,p_flag OUT NOCOPY NUMBER
719 )
720 IS
721
722 l_procedure VARCHAR2(100);
723
724 CURSOR csr_get_person_data
725 IS
726 SELECT 'EID' empno_tag
727 , pai.action_information1 empno_value
728 , 'EE_DETAILS' emp_details
729 , UPPER(pai.action_information6
730 || pai.action_information5
731 || DECODE(pai.action_information9,NULL,'',fnd_global.local_chr(10))
732 || pai.action_information9) emp_details_value
733 , 'E_F_NAME' emp_full_name
734 , UPPER(pai.action_information5) emp_full_value
735 , 'E_TITLE' emp_title
736 , UPPER(pai.action_information6) emp_title_value
737 , 'PAN' emp_pan
738 , DECODE(pai.action_information4,'Y','APPLIED FOR','N','',pai.action_information4) emp_pan_value
739 , 'E_DESG' emp_designation
740 , UPPER(pai.action_information9) emp_designation_value
741 , 'E_FAT_NAME' emp_father_name
742 , UPPER(pai.action_information7) emp_father_value
743 , 'DOB' emp_dob
744 , TO_CHAR(fnd_date.canonical_to_date(pai.action_information10),'DD-MM-YYYY') emp_dob_value
745 , 'GENDER' emp_gender
746 , UPPER(pai.action_information11) emp_gender_value
747 , 'E_INTEREST' emp_interest
748 , DECODE(pai.action_information12,'N','No','Y','Yes') emp_interest_value
749 , 'ASG_START' emp_asg_start
750 , TO_DATE(pai.action_information17,'DD-MM-RRRR') emp_asg_start_value
751 , 'ASG_END' emp_asg_end
752 , TO_DATE(pai.action_information18,'DD-MM-RRRR') emp_asg_end_value
753 , 'E_ADDRESS' emp_address
754 , get_address_details( pai.action_information14
755 , 'Y','NULL'
756 ) emp_address_value
757 , 'EMP_POSTAL_CODE' emp_zipcode
758 , get_address_details( pai.action_information14
759 , 'N', 'POSTAL_CODE'
760 ) emp_zipcode_value
761 , action_information20 emp_date_earned
762 , assignment_id emp_asg_id
763 , action_information15 emp_resident_status
764 , 'EMP_PHONE' emp_phone
765 , action_information16 emp_phone_value
766 FROM pay_action_information pai
767 WHERE pai.action_information_category = 'IN_EOY_PERSON'
768 AND pai.action_context_id = p_action_context_id
769 AND pai.source_id = p_source_id;
770
771 CURSOR csr_payroll_id(p_assignment_id NUMBER,p_date DATE)
772 IS
773 SELECT paf.payroll_id
774 FROM per_all_assignments_f paf
775 WHERE paf.assignment_id =p_assignment_id
776 AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
777
778 l_total_pay_period NUMBER;
779 l_current_pay_period NUMBER;
780 l_asg_id NUMBER;
781 l_date VARCHAR2(30);
782 l_date_earned DATE;
783 l_resident_status VARCHAR2(30);
784 l_payroll_id NUMBER;
785 l_asg_end DATE;
786
787 BEGIN
788 g_debug := hr_utility.debug_enabled;
789 l_procedure := g_package ||'build_employee_xml';
790 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
791
792
793 IF g_debug THEN
794 pay_in_utils.trace('**************************************************','********************');
795 pay_in_utils.trace('p_action_context_id ',p_action_context_id );
796 pay_in_utils.trace('p_source_id ',p_source_id );
797 pay_in_utils.trace('**************************************************','********************');
798 END IF;
799
800
801 FOR i IN csr_get_person_data
802 LOOP
803 write_tag(i.empno_tag,i.empno_value);
804 write_tag(i.emp_details,i.emp_details_value);
805 write_tag(i.emp_full_name,i.emp_full_value);
806 write_tag(i.emp_title,i.emp_title_value);
807 write_tag(i.emp_pan,i.emp_pan_value);
808 write_tag(i.emp_designation,i.emp_designation_value);
809 write_tag(i.emp_father_name,i.emp_father_value);
810 write_tag(i.emp_dob,i.emp_dob_value);
811 write_tag(i.emp_gender,i.emp_gender_value);
812 write_tag(i.emp_interest,i.emp_interest_value);
813 write_tag(i.emp_asg_start,to_char(i.emp_asg_start_value,'DD-Mon-RRRR'));
817 write_tag(i.emp_phone,i.emp_phone_value);
814 write_tag(i.emp_asg_end,to_char(i.emp_asg_end_value,'DD-Mon-RRRR'));
815 write_tag(i.emp_address,i.emp_address_value);
816 write_tag(i.emp_zipcode,i.emp_zipcode_value);
818 l_date := i.emp_date_earned;
819 l_asg_end := i.emp_asg_end_value;
820 l_resident_status := i.emp_resident_status;
821 l_asg_id := i.emp_asg_id;
822 END LOOP;
823
824 l_date_earned := fnd_date.canonical_to_date(l_date);
825
826 OPEN csr_payroll_id(l_asg_id,l_date_earned);
827 FETCH csr_payroll_id INTO l_payroll_id;
828 CLOSE csr_payroll_id;
829
830 l_total_pay_period := pay_in_tax_utils.get_period_number(l_payroll_id,l_asg_end);
831 l_current_pay_period := pay_in_tax_utils.get_period_number(l_payroll_id,l_date_earned);
832 p_rem_pay_period := GREATEST((l_total_pay_period - l_current_pay_period),0);
833
834
835
836 IF(l_resident_status = 'RO') THEN
837 p_flag := 1;
838 ELSE
839 p_flag := 0;
840 END IF;
841
842 IF g_debug THEN
843 pay_in_utils.trace('**************************************************','********************');
844 pay_in_utils.trace('p_rem_pay_period ',to_char(p_rem_pay_period ));
845 pay_in_utils.trace('p_flag ',to_char(p_flag ));
846 pay_in_utils.trace('**************************************************','********************');
847 END IF;
848
849 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
850
851
852 EXCEPTION
853 --
854 WHEN OTHERS THEN
855 pay_in_utils.set_location(g_debug,'Error in: '||l_procedure,30);
856 RAISE;
857 END build_employee_xml;
858
859 --------------------------------------------------------------------------
860 -- --
861 -- Name : BUILD_FORM16_XML --
862 -- Type : PROCEDURE --
863 -- Access : Public --
864 -- Description : This procedure builds the XML for Form 16 and 16AA --
865 -- --
866 -- Parameters : --
867 -- IN : p_action_context_id NUMBER --
868 -- p_source_id NUMBER --
869 -- p_rem_pay_period NUMBER --
870 -- p_flag NUMBER --
871 --------------------------------------------------------------------------
872 PROCEDURE build_form16_xml (p_action_context_id IN pay_assignment_actions.assignment_action_id%TYPE
873 ,p_source_id IN pay_payroll_actions.payroll_action_id%TYPE
874 ,p_rem_pay_period IN NUMBER
875 ,p_flag IN NUMBER
876 ,p_flag_for_16aa OUT NOCOPY NUMBER)
877 IS
878 --
879 l_procedure VARCHAR2(100);
880 j NUMBER;
881 l_prev_tds NUMBER;
882 l_tax_in_words varchar2(240);
883 l_Non_Taxable_Amt NUMBER;
884 sort_index NUMBER;
885 l_flag_for_12ba number;
886 l_tds_value NUMBER;
887 l_open_tag VARCHAR2(100);
888 l_tax_refundable NUMBER;
889 l_marginal_relief NUMBER;
890 l_prev_earnings NUMBER;
891 l_qualifying_amt NUMBER;
892 l_tot_80c_gross NUMBER;
893 l_tot_80c_qual NUMBER;
894 l_tot_80ccc_gross NUMBER;
895 l_tot_80ccc_qual NUMBER;
896
897 l_total_via VARCHAR2(30);
898
899 l_flag_rep_gen NUMBER;
900 emp_pos NUMBER;
901 l_tax_deposited NUMBER;
902 l_serial_number NUMBER;
903
904
905 c_index NUMBER;
906 l_emp_tds NUMBER;
907 l_emp_sur NUMBER;
908 l_emp_cess NUMBER;
909 l_emp_amount NUMBER;
910 l_entry_exists NUMBER;
911 c_index_16aa NUMBER;
912 l_emp_amount_16aa NUMBER;
913 l_entry_exists_16aa NUMBER;
914
915
916 l_via_seq_80c_num NUMBER;
917 l_via_seq_80cce_num NUMBER;
918 l_via_seq_80d_u_num NUMBER;
919
920 l_80c_tag_seq VARCHAR2(20);
921 l_80cce_tag_seq VARCHAR2(20);
922 l_80du_tag_seq VARCHAR2(20);
923
924 l_tag VARCHAR2(5);
925 l_via_80c_flag NUMBER;
926 l_via_cce_flag NUMBER;
927 l_via_oth_flag NUMBER;
928 l_seq CHAR(1) ;
929 l_count NUMBER ;
930 l_loss_from_house NUMBER ;
931 l_other_income NUMBER ;
932
933
934
935 CURSOR csr_salary_components
936 IS
937 SELECT DECODE(pai.action_information1,'F16 Salary Under Section 17', 1,
938 'F16 Value of Perquisites',2,
939 'F16 Profit in lieu of Salary', 3,
940 'F16 Gross Salary',4,
941 'F16 Gross Salary less Allowances',6,
942 'F16 Entertainment Allowance', 7,
943 'F16 Employment Tax',8,
944 'F16 Deductions under Sec 16',9,
948 'F16 Total Income',13,
945 'F16 Income Chargeable Under head Salaries',10,
946 'F16 Other Income',11,
947 'F16 Gross Total Income',12,
949 'F16 Tax on Total Income',14,
950 'F16 Surcharge',15,
951 'F16 Education Cess',16,
952 'F16 Relief under Sec 89',18,
953 'F16 Total Tax payable',19,
954 'Income Tax Deduction',20,
955 'F16 Balance Tax',21,
956 'ER Paid Tax on Non Monetary Perquisite',22,
957 0) sort_index,
958 action_information2 balance_value
959 FROM pay_action_information pai
960 WHERE action_information_category = 'IN_EOY_ASG_SAL'
961 AND action_context_id = p_action_context_id
962 AND source_id = p_source_id;
963
964 CURSOR csr_prev_employment_tds
965 IS
966 SELECT 1
967 FROM pay_action_information pai
968 WHERE action_information_category = 'IN_EOY_ASG_SAL'
969 AND action_context_id = p_action_context_id
970 AND source_id = p_source_id
971 AND action_information1 IN('TDS on Previous Employment',
972 'CESS on Previous Employment',
973 'SC on Previous Employment');
974
975
976 CURSOR csr_other_components(p_action_information1 pay_action_information.action_information1%TYPE)
977 IS
978 SELECT action_information2 balance_value
979 FROM pay_action_information pai
980 WHERE action_information_category = 'IN_EOY_ASG_SAL'
981 AND action_context_id = p_action_context_id
982 AND source_id = p_source_id
983 AND action_information1 = p_action_information1;
984
985 CURSOR csr_other_income IS
986 SELECT action_information1 balance_name,
987 action_information2 balance_value
988 FROM pay_action_information pai
989 WHERE action_information_category = 'IN_EOY_ASG_SAL'
990 AND action_context_id = p_action_context_id
991 AND source_id = p_source_id
992 AND action_information1 IN('Long Term Capital Gains',
993 'Short Term Capital Gains',
994 'Business and Profession Gains',
995 'Other Sources of Income',
996 'Loss From House Property')
997 AND action_information2 IS NOT NULL;
998
999 CURSOR csr_allowances IS
1000 SELECT action_information1 Allowance_name,
1001 action_information2 Amt,
1002 action_information3 Std_Amt,
1003 action_information4 Taxable_Amt,
1004 action_information5 Std_Taxable_Amt
1005 FROM pay_action_information
1006 WHERE action_information_category = 'IN_EOY_ALLOW'
1007 AND action_context_id = p_action_context_id
1008 AND action_information1 <> 'Taxable Allowances'
1009 AND source_id =p_source_id;
1010
1011
1012 /* Get all Section 80C elements where the Gross Amount is greater than 0 */
1013
1014 CURSOR csr_deduction_via
1015 IS
1016 SELECT DECODE(action_information1, 'Life Insurance Premium','Life Insurance Premium',
1017 'Deferred Annuity','Deferred Annuity',
1018 'Senior Citizens Savings Scheme','Senior Citizens Savings Scheme',
1019 'Five Year Post Office Time Deposit Account','Five Year Post Office Time Deposit Account',
1020 'NABARD Bank Deposits','NABARD Bank Deposits',
1021 'Public Provident Fund','Public Provident Fund',
1022 'Interest on NSC','Interest on National Savings Certificate reinvested',
1023 'House Loan Repayment', 'Principal Loan (Housing Loan) Repayment',
1024 'Mutual Fund or UTI','Notified units of Mutual Funds/UTI',
1025 'National Housing Bank', 'National Housing Bank Scheme',
1026 'ULIP','Unit Linked Insurance Plan (UTI,LIC etc)',
1027 'Notified Annuity Plan','Notified Annuity Plan',
1028 'Notified Pension Fund','Notified Pension Fund',
1029 'Public Sector Scheme','Public Sector Company Scheme',
1030 'Infrastructure Bonds','Investment in Infrastructure Bonds',
1031 'Tuition fee','Tuition Fees per children (max 2 children allowed)',
1032 'Superannuation Fund', 'Employee Contribution to an approved superannuation fund',
1033 'F16 Employee PF Contribution','Employee Contribution to Provident Fund',
1034 'NSC','NSC',
1035 'Deposits in Govt. Security','Deposits in Govt. Security',
1036 'Notified Deposit Scheme','Notified Deposit Scheme',
1037 'Approved Shares or Debentures','Approved Shares or Debentures',
1038 'Approved Mutual Fund','Approved Mutual Fund',
1042 , nvl(action_information3,action_information2) Gross_Value
1039 'Fixed Deposits','Fixed Deposits',
1040 'X')Description_Value
1041 , action_information2 Qualifying_Value
1043 FROM pay_action_information pai
1044 WHERE action_information_category = 'IN_EOY_VIA'
1045 AND action_context_id = p_action_context_id
1046 AND NVL(action_information3,action_information2) > 0
1047 AND source_id =p_source_id
1048 ORDER BY Description_Value;
1049
1050 /* Get all Chapter VIA elements excluding 80C elements where the Gross Amount is greater than 0 */
1051 CURSOR csr_deduction_via_d_to_u
1052 IS
1053 SELECT DECODE(action_information1, 'F16 Deductions Sec 80D','80D',
1054 'F16 Deductions Sec 80DD','80DD',
1055 'F16 Deductions Sec 80DDB','80DDB',
1056 'F16 Deductions Sec 80E','80E',
1057 'F16 Deductions Sec 80G','80G',
1058 'F16 Deductions Sec 80GG','80GG',
1059 'F16 Deductions Sec 80GGA','80GGA',
1060 'F16 Deductions Sec 80U','80U',
1061 'Pension Fund 80CCC','80CCC',
1062 'Govt Pension Scheme 80CCD','80CCD',
1063 'F16 Total Chapter VI A Deductions','TOTAL_V1A',
1064 'X')Description_Value
1065 , action_information2 Qualifying_Value
1066 , nvl(action_information3,action_information2) Gross_Value
1067 FROM pay_action_information pai
1068 WHERE action_information_category = 'IN_EOY_VIA'
1069 AND action_context_id = p_action_context_id
1070 AND NVL(action_information3,action_information2) > 0
1071 AND source_id =p_source_id
1072 ORDER BY Description_Value;
1073
1074 /* Get Maxium assignment action of the run for each pay period of the tax year*/
1075 CURSOR csr_max_run_assact_period IS
1076 SELECT MAX(action_information4) run_assact
1077 FROM pay_action_information pai
1078 WHERE pai.action_information_category ='IN_EOY_ASG_SAL'
1079 AND pai.action_information1='Income Tax This Pay'
1080 AND pai.action_context_id = p_action_context_id
1081 AND pai.source_id = p_source_id
1082 GROUP BY TRUNC(TO_DATE(Action_information3,'DD-MM_RRRR'),'MM')
1083 ORDER BY TRUNC(TO_DATE(Action_information3,'DD-MM_RRRR'),'MM');
1084
1085 -- Bug 4506944 : Changed the cursor as part of changes to be done to Form 16/16AA
1086 /*Get TDS paid in each pay period from the max assigment action id*/
1087 CURSOR csr_tds_paid(p_max_run_action_id NUMBER
1088 ,p_information VARCHAR2) IS
1089 SELECT action_information2 tds_value
1090 ,DECODE(TO_CHAR(TO_DATE(Action_information3,'DD-MM-RRRR'),'MM'),
1091 '04',1,
1092 '05',2,
1093 '06',3,
1094 '07',4,
1095 '08',5,
1096 '09',6,
1097 '10',7,
1098 '11',8,
1099 '12',9,
1100 '01',10,
1101 '02',11,
1102 '03',12)sort_index
1103 FROM pay_action_information
1104 WHERE action_context_id = p_action_context_id
1105 AND source_id =p_source_id
1106 AND action_information_category = 'IN_EOY_ASG_SAL'
1107 AND action_information1= p_information
1108 AND action_information4 = p_max_run_action_id;
1109
1110 -- Added with changes to Form 24q
1111 CURSOR emp_challan_details IS
1112 SELECT input.name name
1113 , value.screen_entry_value value
1114 , entries.element_entry_id
1115 FROM per_assignments_f assign
1116 ,pay_element_entries_f entries
1117 ,pay_element_types_f type
1118 ,pay_input_values_f input
1119 ,pay_element_entry_values_f value
1120 ,pay_element_links_f links
1121 WHERE assign.assignment_id =
1122 (SELECT assignment_id
1123 FROM pay_assignment_actions
1124 WHERE assignment_action_id = p_action_context_id)
1125 AND links.element_type_id = type.element_type_id
1126 AND links.element_type_id = entries.element_type_id
1127 AND links.element_link_id = entries.element_link_id
1128 AND type.element_name = 'Income Tax Challan Information'
1129 AND type.element_type_id = entries.element_type_id
1130 AND entries.assignment_id = assign.assignment_id
1131 AND type.element_type_id = input.element_type_id
1132 AND value.element_entry_id = entries.element_entry_id
1133 AND value.input_value_id = input.input_value_id
1134 AND input.name in ('Amount Deposited'
1135 , 'Education Cess Deducted'
1136 , 'Income Tax Deducted'
1137 , 'Surcharge Deducted'
1138 , 'Challan or Voucher Number')
1139 AND type.legislation_code ='IN'
1140 AND entries.effective_start_date BETWEEN assign.effective_start_date AND assign.effective_end_date
1141 AND entries.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
1142 AND entries.effective_start_date BETWEEN type.effective_start_date AND type.effective_end_date
1143 AND entries.effective_start_date BETWEEN input.effective_start_date AND input.effective_end_date
1144 AND entries.effective_start_date BETWEEN links.effective_start_date AND links.effective_end_date
1145 AND value.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
1146 ORDER BY entries.element_entry_id
1147 , input.name;
1148
1149 CURSOR emp_challan_details_16aa IS
1150 SELECT input.name name
1154 ,pay_element_entries_f entries
1151 , value.screen_entry_value value
1152 , entries.element_entry_id
1153 FROM per_assignments_f assign
1155 ,pay_element_types_f type
1156 ,pay_input_values_f input
1157 ,pay_element_entry_values_f value
1158 ,pay_element_links_f links
1159 WHERE assign.assignment_id =
1160 (SELECT assignment_id
1161 FROM pay_assignment_actions
1162 WHERE assignment_action_id = p_action_context_id)
1163 AND links.element_type_id = type.element_type_id
1164 AND links.element_type_id = entries.element_type_id
1165 AND links.element_link_id = entries.element_link_id
1166 AND type.element_name = 'Income Tax Challan Information'
1167 AND type.element_type_id = entries.element_type_id
1168 AND entries.assignment_id = assign.assignment_id
1169 AND type.element_type_id = input.element_type_id
1170 AND value.element_entry_id = entries.element_entry_id
1171 AND value.input_value_id = input.input_value_id
1172 AND input.name in ('Amount Deposited'
1173 , 'Challan or Voucher Number')
1174 AND type.legislation_code ='IN'
1175 AND entries.effective_start_date BETWEEN assign.effective_start_date AND assign.effective_end_date
1176 AND entries.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
1177 AND entries.effective_start_date BETWEEN type.effective_start_date AND type.effective_end_date
1178 AND entries.effective_start_date BETWEEN input.effective_start_date AND input.effective_end_date
1179 AND entries.effective_start_date BETWEEN links.effective_start_date AND links.effective_end_date
1180 AND value.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
1181 ORDER BY entries.element_entry_id
1182 , input.name;
1183
1184
1185 BEGIN
1186 g_debug := hr_utility.debug_enabled;
1187 l_procedure := g_package ||'build_form16_xml';
1188 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1189
1190
1191 IF g_debug THEN
1192 pay_in_utils.trace('**************************************************','********************');
1193 pay_in_utils.trace('p_action_context_id ',p_action_context_id );
1194 pay_in_utils.trace('p_source_id ',p_source_id );
1195 pay_in_utils.trace('p_rem_pay_period ',to_char(p_rem_pay_period));
1196 pay_in_utils.trace('p_flag ',to_char(p_flag));
1197 pay_in_utils.trace('**************************************************','********************');
1198 END IF;
1199
1200 l_flag_for_12ba :=0;
1201 l_flag_rep_gen := -1;
1202
1203
1204 g_salary_record(1).Value := 0;
1205 g_salary_record(2).Value := 0;
1206 g_salary_record(3).Value := 0;
1207 g_salary_record(4).Value := 0;
1208 g_salary_record(5).Value := 0;
1209 g_salary_record(6).Value := 0;
1210 g_salary_record(7).Value := 0;
1211 g_salary_record(8).Value := 0;
1212 g_salary_record(9).Value := 0;
1213 g_salary_record(10).Value := 0;
1214 g_salary_record(11).Value := 0;
1215 g_salary_record(12).Value := 0;
1216 g_salary_record(13).Value := 0;
1217 g_salary_record(14).Value := 0;
1218 g_salary_record(15).Value := 0;
1219 g_salary_record(16).Value := 0;
1220 g_salary_record(17).Value := 0;
1221 g_salary_record(18).Value := 0;
1222 g_salary_record(19).Value := 0;
1223 g_salary_record(20).Value := 0;
1224 g_salary_record(21).Value := 0;
1225 g_salary_record(22).Value := 0;
1226 l_tot_80ccc_qual := 0;
1227 l_tot_80c_qual := 0;
1228
1229 l_tot_80ccc_gross := 0;
1230 l_tot_80c_gross := 0;
1231
1232
1233
1234 FOR i IN csr_salary_components
1235 LOOP
1236 IF i.sort_index <> '0' THEN
1237 IF(l_flag_rep_gen = -1 )THEN
1238 l_flag_rep_gen := 1;
1239 END IF ;
1240 g_salary_record(i.sort_index).Value := i.balance_value;
1241 END IF;
1242 END LOOP;
1243
1244 OPEN csr_other_components('F16 Tax Refundable');
1245 FETCH csr_other_components INTO l_tax_refundable;
1246 IF csr_other_components%NOTFOUND THEN
1247 l_tax_refundable := 0;
1248 END IF;
1249 CLOSE csr_other_components;
1250
1251 OPEN csr_other_components('F16 Marginal Relief');
1252 FETCH csr_other_components INTO l_marginal_relief;
1253 IF csr_other_components%NOTFOUND THEN
1254 l_marginal_relief := 0;
1255 END IF;
1256 CLOSE csr_other_components;
1257
1258 --
1259 -- Bug 4557407 removed surcharge to Tax on Income
1260 --
1261 -- Total Nontaxable Allowance
1262 -- Tax Paybale and Refundable
1263
1264 g_salary_record(5).Value := nvl(g_salary_record(4).Value,0) - nvl(g_salary_record(6).Value,0);
1265
1266 IF (g_salary_record(21).Value = 0) THEN
1267 g_salary_record(21).Value := -l_tax_refundable;
1268 END IF;
1269
1270 FOR i in 1..22 LOOP
1271 write_tag(g_salary_record(i).Name,pay_us_employee_payslip_web.get_format_value(g_business_group_id,g_salary_record(i).Value));
1272 END LOOP;
1273 -- write_tag('DUMMY',pay_us_employee_payslip_web.get_format_value(g_business_group_id,g_salary_record(22).Value));
1274
1275 -- g_873 := g_868 + g_872;
1276 write_tag('TDS_DEDUCTED',pay_us_employee_payslip_web.get_format_value(g_business_group_id,(NVL(g_salary_record(20).Value,0) + NVL(g_salary_record(22).Value,0))));
1277
1278 write_tag('TAX_ON_TOT_INCOME',pay_us_employee_payslip_web.get_format_value(g_business_group_id,(NVL(g_salary_record(14).Value,0) + NVL(g_salary_record(15).Value,0) + NVL(g_salary_record(16).Value,0) - l_marginal_relief)));
1279
1280 OPEN csr_other_components('Previous Employment Earnings');
1281 FETCH csr_other_components INTO l_prev_earnings;
1282 IF csr_other_components%NOTFOUND THEN
1283 l_prev_earnings := 0;
1284 END IF;
1285 CLOSE csr_other_components;
1286
1287
1288 OPEN csr_prev_employment_tds;
1289 FETCH csr_prev_employment_tds INTO l_prev_tds;
1290 IF csr_prev_employment_tds%NOTFOUND THEN
1291 l_prev_tds := 0;
1292 END IF;
1293 CLOSE csr_prev_employment_tds;
1294
1295 IF (g_salary_record(21).Value <> 0 AND l_flag_rep_gen = -1) THEN
1296 l_flag_rep_gen := 1;
1297 END IF;
1298
1299 /* Chapter VIA Start */
1300 l_total_via :=0;
1301
1302 l_via_80c_flag := 0;
1303 l_via_cce_flag := 0;
1304 l_via_oth_flag := 0;
1305
1306 l_via_seq_80c_num := 1;
1307 l_via_seq_80cce_num := 97;
1308 l_via_seq_80d_u_num := 97;
1309
1310 l_80cce_tag_seq := '('||fnd_global.local_chr(l_via_seq_80cce_num)||')';
1311
1312
1313 FOR i IN csr_deduction_via LOOP
1314 IF (l_via_80c_flag = 0 and i.Description_Value <> 'X') THEN
1315 l_via_80c_flag := 1;
1316 l_via_cce_flag := 1;
1317 write_tag('SEC80C',l_via_80c_flag);
1318 write_tag('AS',l_80cce_tag_seq);
1319 l_via_seq_80cce_num := l_via_seq_80cce_num + 1;
1320
1321 IF(l_flag_rep_gen = -1) THEN
1322 l_flag_rep_gen := 1;
1323 END IF;
1324
1325 END IF;
1326
1327 IF(i.Description_Value <> 'X')THEN
1328 l_80c_tag_seq := ltrim(lower(to_char(l_via_seq_80c_num,'RM')),' ' )||')';
1329
1330 l_open_tag := '<CGRP>';
1331 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1332
1333 write_tag('RN',l_80c_tag_seq);
1334 write_tag('NAME',i.Description_Value);
1335 write_tag('GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,nvl(i.Gross_Value,0)));
1336 l_via_seq_80c_num := l_via_seq_80c_num + 1;
1337 l_tot_80c_gross := l_tot_80c_gross + nvl(i.Gross_Value,0);
1338 l_tot_80c_qual := l_tot_80c_qual + nvl(i.Qualifying_Value,0);
1339 l_open_tag := '</CGRP>';
1340 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1341 END IF;
1342 END LOOP;
1343
1344 l_tot_80c_qual := LEAST(l_tot_80c_qual,g_80cce_limit);
1345
1346 write_tag('TOT_80C_GR',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80c_gross));
1347 write_tag('TOT_80C_QA',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80c_qual));
1348
1349 FOR i in csr_deduction_via_d_to_u LOOP
1350 IF(l_flag_rep_gen = -1) THEN
1351 l_flag_rep_gen := 1;
1352 END IF;
1353
1354 IF(i.Description_Value = 'TOTAL_V1A') THEN
1355 l_total_via := nvl(i.Qualifying_Value,0);
1356 ELSIF(i.Description_Value <> 'X')THEN
1357 IF i.Description_Value IN('80D',
1358 '80DD',
1359 '80DDB',
1360 '80E',
1361 '80G',
1362 '80GG',
1363 '80GGA',
1364 '80U') THEN
1365 l_via_oth_flag := 1;
1366 l_80du_tag_seq := '('||fnd_global.local_chr(l_via_seq_80d_u_num)||')';
1367 l_open_tag := '<OTHER_VIA>';
1368 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1369
1370 l_qualifying_amt := nvl(i.Qualifying_Value,0);
1371 write_tag('SN',l_80du_tag_seq);
1372 write_tag('NAME',i.Description_Value);
1373 write_tag('GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,nvl(i.Gross_Value,0)));
1374 write_tag('QUAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_qualifying_amt));
1375
1376 l_open_tag := '</OTHER_VIA>';
1377 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1378 l_via_seq_80d_u_num := l_via_seq_80d_u_num +1;
1379 ELSIF i.Description_Value IN('80CCC','80CCD') THEN
1380 l_via_cce_flag := 1;
1381 l_80cce_tag_seq := '('||fnd_global.local_chr(l_via_seq_80cce_num)||')';
1382 l_qualifying_amt := LEAST(g_80cce_limit,nvl(i.Qualifying_Value,0));
1383 l_tag := substr(i.Description_Value,3);
1384 l_open_tag := '<'||l_tag||'GRP>';
1385 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1386
1387 write_tag('AS',l_80cce_tag_seq);
1388
1389 write_tag('GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,nvl(i.Gross_Value,0)));
1390 write_tag('QUAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_qualifying_amt));
1391
1392 l_via_seq_80cce_num := l_via_seq_80cce_num + 1;
1393
1394 l_open_tag := '</'||l_tag||'GRP>';
1395 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1396 l_tot_80ccc_gross := l_tot_80ccc_gross + nvl(i.Gross_Value,0);
1397 l_tot_80ccc_qual := l_tot_80ccc_qual + l_qualifying_amt;
1398 ELSE
1399 NULL;
1400 END IF;
1401 END IF;
1402 END LOOP;
1403 l_tot_80ccc_gross := l_tot_80ccc_gross + l_tot_80c_gross;
1404 l_tot_80ccc_qual := l_tot_80ccc_qual + l_tot_80c_qual;
1405
1406 IF (l_via_cce_flag <> 1 ) THEN
1407 write_tag('VIACCE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,0));
1408 END IF;
1409 write_tag('SEC80CCE',l_via_cce_flag);
1410 write_tag('TOT_80CCC_GR',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80ccc_gross));
1411 write_tag('TOT_80CCC_QA',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80ccc_qual));
1412
1413 IF (l_via_oth_flag <> 1)THEN
1414 write_tag('VIAOTH',pay_us_employee_payslip_web.get_format_value(g_business_group_id,0));
1415 END IF;
1416
1417 write_tag('TOTAL_V1A',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_total_via));
1418 /* Chapter VIA End */
1419
1420
1421 /* Check for Form Generation and Other Income Start*/
1422 t_table_1(1) :=0;
1423 t_table_1(2) :=0;
1424 t_table_1(3) :=0;
1425 t_table_1(4) :=0;
1426 t_table_1(5) :=0;
1427
1428 FOR i IN csr_other_income LOOP
1429 IF(l_flag_rep_gen = -1) THEN
1430 l_flag_rep_gen := 1;
1431 END IF;
1432
1433 IF (i.balance_name ='Business and Profession Gains') THEN
1434 t_table_1(1) := i.balance_value;
1435 ELSIF (i.balance_name ='Long Term Capital Gains' ) THEN
1436 t_table_1(2) := i.balance_value;
1437 ELSIF (i.balance_name ='Short Term Capital Gains' ) THEN
1438 t_table_1(3) := i.balance_value;
1439 ELSIF (i.balance_name ='Other Sources of Income' ) THEN
1440 t_table_1(4) := i.balance_value;
1441 ELSIF (i.balance_name ='Loss From House Property') THEN
1442 t_table_1(5) := i.balance_value;
1443 END IF;
1444 END LOOP;
1445
1446 IF l_flag_rep_gen = -1 THEN
1447 /* Do not generate any report for this employee */
1448 p_flag_for_16aa := -1;
1449 emp_pos := INSTR(g_tmp_clob,'<EMPLOYEE>',-1);
1450 dbms_lob.TRIM(g_tmp_clob,emp_pos-1);
1451 ELSE
1452 /* Generate either 16 /16AA for this employee */
1453 p_flag_for_16aa := 1;
1454
1455 IF ((g_salary_record(10).Value = 0)
1456 OR ((g_salary_record(6).Value + l_prev_earnings)>150000)
1457 OR (l_prev_earnings <>0 AND l_prev_tds > 0)
1458 OR (t_table_1(1) <>0 OR t_table_1(2) <>0)
1459 OR p_flag = 0 )
1460 THEN
1461 p_flag_for_16aa :=0; -- Not eligible for 16AA
1462 END IF;
1463
1464
1465 FOR i IN 1..5 LOOP
1466 IF(t_table_1(i) <>0) THEN
1467 l_open_tag := '<Other_Income>';
1468 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1469
1470 IF (i=1 )THEN
1471 write_tag('NAME','Business and Profession Gains');
1472 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,t_table_1(1)));
1473 ELSIF (i=2 ) THEN
1474 write_tag('NAME','Long Term Capital Gains');
1475 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,t_table_1(2)));
1476 ELSIF (i=3) THEN
1477 write_tag('NAME','Short Term Capital Gains');
1478 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,t_table_1(3)));
1479 ELSIF (i=4) THEN
1480 write_tag('NAME','Other Sources of Income');
1481 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,t_table_1(4)));
1482 ELSIF (i=5) THEN
1483 write_tag('NAME','Loss From House Property');
1484 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,0-t_table_1(5)));
1485 END IF;
1486
1487
1488 l_open_tag := '</Other_Income>';
1489 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1490 END IF;
1491 END LOOP;
1492
1493 IF p_flag_for_16aa = 1 THEN
1494
1495 l_open_tag := '<SEC_OTHERS>';
1496 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1497 write_tag('SECTION','(h) 80C');
1498 write_tag('SEC_GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80c_gross));
1499 write_tag('SEC_QUAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80c_qual));
1500 l_open_tag := '</SEC_OTHERS>';
1501 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1502
1503 l_count :=0;
1504
1505 FOR rec_deduction_via_d_to_u IN csr_deduction_via_d_to_u
1506 LOOP
1507 IF (rec_deduction_via_d_to_u.Description_Value NOT IN ('TOTAL_V1A','X')) THEN
1508 IF rec_deduction_via_d_to_u.Description_Value = '80CCC' THEN
1509 write_tag('SEC80CCC',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1510 write_tag('SEC80CCC_QAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1511 ELSIF rec_deduction_via_d_to_u.Description_Value = '80D' THEN
1512 write_tag('SEC80D',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1513 write_tag('SEC80D_QAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1514 ELSIF rec_deduction_via_d_to_u.Description_Value = '80E' THEN
1515 write_tag('SEC80E',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1516 write_tag('SEC80E_QAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1517 ELSIF rec_deduction_via_d_to_u.Description_Value = '80G' THEN
1518 write_tag('SEC80G',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1519 write_tag('SEC80G_QAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1520 ELSE
1521
1522
1523 l_count := l_count + 1;
1524
1525 select lower(fnd_global.local_chr(r+64)) INTO l_seq
1526 FROM
1527 ( SELECT LEVEL r
1528 FROM dual
1529 CONNECT BY LEVEL <= 26 )
1530 WHERE r+64 = 72 + l_count;
1531
1532 l_open_tag := '<SEC_OTHERS>';
1533 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1534 write_tag('SECTION','('||l_seq||') '||rec_deduction_via_d_to_u.Description_Value);
1535 write_tag('SEC_GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1536 write_tag('SEC_QUAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1537 l_open_tag := '</SEC_OTHERS>';
1538 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1539 END IF ;
1540 END IF ;
1541 END LOOP ;
1542
1543
1544
1545
1546 FOR i IN 1..5 LOOP
1547 IF(t_table_1(i) <>0) THEN
1548
1549 IF (i=5) THEN
1550 l_loss_from_house := t_table_1(i) ;
1551 ELSIF (i=4) THEN
1552 l_other_income := t_table_1(i) ;
1553 END IF;
1554
1555 END IF;
1556 END LOOP;
1557
1561 END IF ;
1558 write_tag('OI_HOUSE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_loss_from_house));
1559 write_tag('OI_OTHER',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_other_income));
1560
1562
1563 t_table_1.DELETE;
1564 /* Check for 16aa and Other Income End*/
1565
1566 IF ((g_salary_record(4).Value + l_prev_earnings)>150000 OR
1567 g_salary_record(2).Value > 0 OR
1568 g_salary_record(3).Value > 0
1569 ) THEN
1570 l_flag_for_12ba :=1; --Eligible for 12BA
1571 END IF;
1572
1573 write_tag('EE_INCOME',pay_us_employee_payslip_web.get_format_value(g_business_group_id,g_salary_record(10).Value - g_salary_record(2).Value));
1574 write_tag('C_16AA_FLAG',p_flag_for_16aa );
1575 write_tag('C_12BA_FLAG',l_flag_for_12ba );
1576
1577 l_tax_in_words := pay_in_utils.number_to_words(g_salary_record(20).Value);
1578 write_tag('TOTAL',l_tax_in_words );
1579
1580 /* Allowances Start*/
1581 FOR i in csr_allowances LOOP
1582 --
1583 IF (i.Allowance_Name='House Rent Allowance') THEN
1584 l_Non_Taxable_Amt := nvl(i.Amt,0) + nvl(i.Std_AMt,0)* p_rem_pay_period - nvl(i.Std_Taxable_Amt,0);
1585 ELSE
1586 l_Non_Taxable_Amt := nvl(i.Amt,0) - nvl(i.Taxable_Amt,0) + (nvl(i.Std_AMt,0) - nvl(i.Std_Taxable_Amt,0)) * p_rem_pay_period ;
1587 END IF;
1588 --
1589 IF( l_Non_Taxable_Amt > 0) THEN
1590 l_open_tag := '<Allowance>';
1591 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1592
1593 write_tag('NAME',i.Allowance_Name);
1594 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_Non_Taxable_Amt));
1595
1596 l_open_tag := '</Allowance>';
1597 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1598
1599 END IF;
1600 --
1601 END LOOP;
1602 /* Allowances End*/
1603
1604 /* Challan Start */
1605
1606 IF p_flag_for_16aa = 0 THEN
1607 g_emp_challan_details_tbl.DELETE;
1608 c_index := 1;
1609
1610 FOR emp_challan IN emp_challan_details
1611 LOOP
1612 IF emp_challan.name = 'Amount Deposited' THEN
1613 g_emp_challan_details_tbl(c_index).emp_amount := nvl(emp_challan.value, 0);
1614 END IF;
1615
1616 IF emp_challan.name = 'Challan or Voucher Number' THEN
1617 g_emp_challan_details_tbl(c_index).emp_voucher := emp_challan.value;
1618 END IF;
1619
1620 IF emp_challan.name = 'Education Cess Deducted' THEN
1621 g_emp_challan_details_tbl(c_index).emp_cess := nvl(emp_challan.value, 0);
1622 END IF;
1623
1624 IF emp_challan.name = 'Income Tax Deducted' THEN
1625 g_emp_challan_details_tbl(c_index).emp_tds := nvl(emp_challan.value, 0);
1626 END IF;
1627
1628 IF emp_challan.name = 'Surcharge Deducted' THEN
1629 g_emp_challan_details_tbl(c_index).emp_sur := nvl(emp_challan.value, 0);
1630 c_index := c_index + 1;
1631 END IF;
1632 END LOOP;
1633
1634 /*Merge Bank Details at Org Level with TDS Details at Person level*/
1635 l_serial_number := 1;
1636 FOR I IN 1..g_index LOOP
1637
1638 l_emp_tds := 0;
1639 l_emp_sur := 0;
1640 l_emp_cess := 0;
1641 l_emp_amount := 0;
1642
1643 IF g_Bank_Details_tbl.exists(i) THEN
1644
1645 l_entry_exists := 0;
1646
1647 FOR j IN 1..(c_index-1) LOOP
1648 IF g_Bank_Details_tbl(i).VNumber = NVL(g_emp_challan_details_tbl(j).emp_voucher, '-1') THEN
1649 l_emp_tds := l_emp_tds + g_emp_challan_details_tbl(j).emp_tds;
1650 l_emp_sur := l_emp_sur + g_emp_challan_details_tbl(j).emp_sur;
1651 l_emp_cess := l_emp_cess + g_emp_challan_details_tbl(j).emp_cess;
1652 l_emp_amount := l_emp_amount + g_emp_challan_details_tbl(j).emp_amount;
1653 l_entry_exists := 1;
1654 END IF;
1655 END LOOP;
1656
1657 IF l_entry_exists = 1 THEN
1658 l_open_tag := '<t_month>';
1659 dbms_lob.writeAppend(g_tmp_clob,LENGTH(l_open_tag),l_open_tag);
1660 write_tag('SNO',l_serial_number);
1661 l_serial_number := l_serial_number + 1;
1662 write_tag('TDS_PERIOD',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_tds));
1663 write_tag('SURCHARGE_PERIOD',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_sur));
1664 write_tag('ECESS_PERIOD',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_cess));
1665 write_tag('TAX_DEPOSITED',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_amount));
1666 write_tag('PYMT_DATE',g_Bank_Details_tbl(i).VDate);
1667 write_tag('BANK_BRANCH',g_Bank_Details_tbl(i).Bank);
1668 write_tag('VOUCHER_NUM',g_Bank_Details_tbl(i).VNumber);
1669 write_tag('CHEQUE_DD_NUMBER',g_Bank_Details_tbl(i).DDCheque_Num);
1670 l_open_tag := '</t_month>';
1671 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1672 END IF;
1673
1674 END IF;
1675
1679 g_emp_challan_details_tbl_16aa.DELETE;
1676 END LOOP;
1677
1678 ELSIF p_flag_for_16aa = 1 THEN
1680 c_index_16aa := 1;
1681
1682 FOR emp_challan_16aa IN emp_challan_details_16aa
1683 LOOP
1684 IF emp_challan_16aa.name = 'Amount Deposited' THEN
1685 g_emp_challan_details_tbl_16aa(c_index_16aa).emp_amount := nvl(emp_challan_16aa.value, 0);
1686 END IF;
1687
1688 IF emp_challan_16aa.name = 'Challan or Voucher Number' THEN
1689 g_emp_challan_details_tbl_16aa(c_index_16aa).emp_voucher := emp_challan_16aa.value;
1690 c_index_16aa := c_index_16aa + 1;
1691 END IF;
1692 END LOOP;
1693
1694 /*Merge Bank Details at Org Level with TDS Details at Person level*/
1695 FOR I IN 1..g_index_16aa LOOP
1696 l_emp_amount_16aa := 0;
1697
1698 IF g_Bank_Details_tbl_16aa.exists(I) THEN
1699 l_entry_exists_16aa := 0;
1700
1701 FOR j IN 1..(c_index_16aa-1) loop
1702 IF g_Bank_Details_tbl_16aa(I).VNumber = NVL(g_emp_challan_details_tbl_16aa(j).emp_voucher, '-1') then
1703 l_emp_amount_16aa := l_emp_amount_16aa + g_emp_challan_details_tbl_16aa(j).emp_amount;
1704 l_entry_exists_16aa := 1;
1705 END IF;
1706 END LOOP;
1707
1708 IF l_entry_exists_16aa = 1 THEN
1709 l_open_tag := '<t_month_aa>';
1710 dbms_lob.writeAppend(g_tmp_clob,LENGTH(l_open_tag),l_open_tag);
1711 write_tag('TDS_PERIOD_AA',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_amount_16aa));
1712 write_tag('PYMT_DATE_AA',g_Bank_Details_tbl_16aa(i).VDate);
1713 write_tag('BANK_BRANCH_AA',g_Bank_Details_tbl_16aa(i).Bank);
1714 l_open_tag := '</t_month_aa>';
1715 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1716 END IF;
1717
1718 END IF;
1719
1720 END LOOP;
1721 END IF;
1722
1723 END IF;
1724 /* Challan End */
1725 IF g_debug THEN
1726 pay_in_utils.trace('p_flag_for_16aa ', p_flag_for_16aa );
1727 END IF;
1728 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1729 END build_form16_xml;
1730
1731 --------------------------------------------------------------------------
1732 -- --
1733 -- Name : BUILD_FORM12BA_XML --
1734 -- Type : PROCEDURE --
1735 -- Access : Public --
1736 -- Description : This procedure builds the XML for Form 12BA --
1737 -- --
1738 -- Parameters : --
1739 -- IN : p_action_context_id NUMBER --
1740 --------------------------------------------------------------------------
1741 PROCEDURE build_form12ba_xml(p_action_context_id IN pay_assignment_actions.assignment_action_id%TYPE
1742 ,p_source_id IN pay_payroll_actions.payroll_action_id%TYPE)
1743 IS
1744 l_total_taxable_perq NUMBER;
1745 l_total_emp_contr NUMBER;
1746
1747 CURSOR csr_get_perq_values
1748 IS
1749 SELECT DECODE(action_information1, 'Company Accommodation', 1
1750 , 'Motor Car Perquisite',2
1751 , 'Domestic Servant',3
1752 , 'Gas / Water / Electricity', 4
1753 , 'Loan at Concessional Rate',5
1754 , 'Travel / Tour / Accommodation',7
1755 , 'Leave Travel Concession',7
1756 , 'Lunch Perquisite',8
1757 , 'Free Education', 9
1758 , 'Gift Voucher', 10
1759 , 'Credit Cards', 11
1760 , 'Club Expenditure', 12
1761 , 'Company Movable Assets',13
1762 , 'Transfer of Company Assets',14
1763 , 'Employer Paid Tax',15
1764 , 'Shares',16
1765 , 20) sort_index
1766 , SUM(NVL(action_information2,0)) value1
1767 , SUM(NVL(action_information3,0)) value2
1768 FROM pay_action_information
1769 WHERE action_information_category = 'IN_EOY_PERQ'
1770 AND action_context_id = p_action_context_id
1771 AND source_id =p_source_id
1772 GROUP BY DECODE(action_information1, 'Company Accommodation', 1
1773 , 'Motor Car Perquisite',2
1774 , 'Domestic Servant',3
1775 , 'Gas / Water / Electricity', 4
1776 , 'Loan at Concessional Rate',5
1777 , 'Travel / Tour / Accommodation',7
1778 , 'Leave Travel Concession',7
1779 , 'Lunch Perquisite',8
1780 , 'Free Education', 9
1781 , 'Gift Voucher', 10
1785 , 'Transfer of Company Assets',14
1782 , 'Credit Cards', 11
1783 , 'Club Expenditure', 12
1784 , 'Company Movable Assets',13
1786 , 'Employer Paid Tax',15
1787 , 'Shares',16
1788 , 20) ;
1789
1790 CURSOR csr_get_total_perq(p_action_information1 pay_action_information.action_information1%TYPE)
1791 IS
1792 SELECT NVL(action_information2,0) value1
1793 FROM pay_action_information
1794 WHERE action_information_category = 'IN_EOY_PERQ'
1795 AND action_context_id = p_action_context_id
1796 AND source_id =p_source_id
1797 AND action_information1 = p_action_information1;
1798
1799
1800 l_procedure varchar2(100);
1801
1802 BEGIN
1803 g_debug := hr_utility.debug_enabled;
1804 l_procedure := g_package ||'build_form12ba_xml';
1805 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1806
1807
1808 IF g_debug THEN
1809 pay_in_utils.trace('**************************************************','********************');
1810 pay_in_utils.trace('p_action_context_id ', p_action_context_id );
1811 pay_in_utils.trace('p_source_id ', p_source_id );
1812 pay_in_utils.trace('**************************************************','********************');
1813 END IF;
1814
1815 FOR j in csr_get_perq_values
1816 LOOP
1817 g_perq_record(j.sort_index).perq_value1 := j.value1;
1818 g_perq_record(j.sort_index).perq_value2 := j.value2;
1819 END LOOP;
1820
1821 FOR i in 1..16
1822 LOOP
1823 g_perq_record(17).perq_value1 := g_perq_record(17).perq_value1 + g_perq_record(i).perq_value1;
1824 g_perq_record(17).perq_value2 := g_perq_record(17).perq_value2 + g_perq_record(i).perq_value2;
1825 END LOOP;
1826
1827
1828
1829 OPEN csr_get_total_perq('Taxable Perquisites');
1830 FETCH csr_get_total_perq INTO l_total_taxable_perq;
1831 IF csr_get_total_perq%NOTFOUND THEN
1832 l_total_taxable_perq := 0;
1833 END IF;
1834 CLOSE csr_get_total_perq;
1835
1836 OPEN csr_get_total_perq('Perquisite Employee Contribution');
1837 FETCH csr_get_total_perq INTO l_total_emp_contr;
1838 IF csr_get_total_perq%NOTFOUND THEN
1839 l_total_emp_contr := 0;
1840 END IF;
1841 CLOSE csr_get_total_perq;
1842
1843 g_perq_record(18).perq_value1 := l_total_taxable_perq;
1844 g_perq_record(18).perq_value2 := l_total_emp_contr;
1845
1846 g_perq_record(17).perq_value1 := g_perq_record(18).perq_value1 - g_perq_record(17).perq_value1;
1847 g_perq_record(17).perq_value2 := g_perq_record(18).perq_value2 - g_perq_record(17).perq_value2;
1848
1849 FOR i in 1..18
1850 LOOP
1851
1852 write_tag( p_tag_name => 'P' || i || '_V3'
1853 , p_tag_value => pay_us_employee_payslip_web.get_format_value
1854 ( g_business_group_id
1855 , g_perq_record(i).perq_value1
1856 )
1857 );
1858 write_tag( p_tag_name => 'P' || i || '_V2'
1859 , p_tag_value => pay_us_employee_payslip_web.get_format_value
1860 ( g_business_group_id
1861 , g_perq_record(i).perq_value2
1862 )
1863 );
1864 write_tag( p_tag_name => 'P' || i || '_V1'
1865 , p_tag_value => pay_us_employee_payslip_web.get_format_value
1866 ( g_business_group_id
1867 , g_perq_record(i).perq_value1 + g_perq_record(i).perq_value2
1868 )
1869 );
1870
1871 END LOOP;
1872 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1873
1874 END build_form12ba_xml;
1875
1876 --------------------------------------------------------------------------
1877 -- --
1878 -- Name : GET_TEMPLATE --
1879 -- Type : PROCEDURE --
1880 -- Access : Public --
1881 -- Description : This procedure gets the payslip template code --
1882 -- --
1883 -- Parameters : --
1884 -- IN : p_business_group_id NUMBER --
1885 -- OUT : p_template VARCHAR2 --
1886 --------------------------------------------------------------------------
1887 PROCEDURE get_template (p_business_group_id IN NUMBER
1888 ,p_template OUT NOCOPY VARCHAR2
1889 )
1890 IS
1891
1892 l_procedure varchar2(100);
1893
1894 BEGIN
1895 g_debug := hr_utility.debug_enabled;
1896 l_procedure := g_package ||'build_form12ba_xml';
1897 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1898
1899
1903
1900 IF g_debug THEN
1901 pay_in_utils.trace('p_business_group_id ', to_char(p_business_group_id) );
1902 END IF;
1904 p_template := 'PAY_IN_ITR_EE_05';
1905 g_chunk_size := 10;
1906 g_business_group_id := p_business_group_id;
1907
1908 IF g_debug THEN
1909 pay_in_utils.trace('p_template ', p_template);
1910 END IF;
1911 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1912 EXCEPTION
1913
1914 WHEN OTHERS THEN
1915 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,20);
1916 RAISE;
1917
1918 END get_template;
1919
1920 --------------------------------------------------------------------------
1921 -- --
1922 -- Name : FETCH_XML --
1923 -- Type : PROCEDURE --
1924 -- Access : Public --
1925 -- Description : This procedure returns the next CLOB available in --
1926 -- global CLOB array --
1927 -- --
1928 -- Parameters : --
1929 -- IN : N/A --
1930 -- OUT : p_clob CLOB --
1931 --------------------------------------------------------------------------
1932 PROCEDURE fetch_xml (p_clob OUT NOCOPY CLOB)
1933 IS
1934 l_procedure varchar2(100);
1935
1936 BEGIN
1937 g_debug := hr_utility.debug_enabled;
1938 l_procedure := g_package ||'fetch_xml';
1939 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1940
1941 -- If Clobs exists return next clob else exit NULL
1942 hr_utility.trace('Clob Count : ' || g_clob_cnt);
1943 hr_utility.trace('Clob Fetch Count : ' || g_fetch_clob_cnt);
1944 IF (g_clob_cnt <> 0 ) AND (g_fetch_clob_cnt < g_clob_cnt) THEN
1945 g_fetch_clob_cnt := g_fetch_clob_cnt + 1;
1946 p_clob := g_clob(g_fetch_clob_cnt);
1947 ELSE
1948 p_clob := null;
1949 END IF;
1950
1951 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1952
1953 END fetch_xml;
1954
1955 --------------------------------------------------------------------------
1956 -- --
1957 -- Name : LOAD_XML --
1958 -- Type : PROCEDURE --
1959 -- Access : Public --
1960 -- Description : This procedure makes a list of XMLs in a global --
1961 -- CLOB array --
1962 -- --
1963 -- Parameters : --
1964 -- IN : p_business_group_id NUMBER --
1965 -- p_assessment_year VARCHAR2 --
1966 -- p_gre_organization VARCHAR2 --
1967 -- p_employee_type VARCHAR2 --
1968 -- p_employee_number VARCHAR2 --
1969 -- OUT : p_clob_cnt NUMBER --
1970 --------------------------------------------------------------------------
1971 -- Flow : --
1972 -- For EACH GRE --
1973 -- Get the details of GRE --
1974 -- For EACH Employee --
1975 -- Get the details for Form 16, 16AA, 12BA and --
1976 -- Build the XML for every employee --
1977 -- by calling the respective procedures --
1978 -- END FOR EACH EMPLOYEE --
1979 -- END FOR EACH GRE --
1980 --------------------------------------------------------------------------
1981 PROCEDURE load_xml (p_business_group_id IN NUMBER
1982 ,p_assessment_year IN VARCHAR2
1983 ,p_gre_organization IN VARCHAR2 DEFAULT NULL
1984 ,p_employee_type IN VARCHAR2
1985 ,p_employee_number IN VARCHAR2 DEFAULT NULL
1986 ,p_clob_cnt OUT NOCOPY NUMBER
1987 )
1988 IS
1989 l_procedure VARCHAR2(100);
1990 l_open_tag VARCHAR2(100);
1991 l_gre_id hr_organization_units.organization_id%TYPE;
1992 l_action_context_id pay_assignment_actions.assignment_action_id%TYPE;
1993 l_emp_number per_people_f.employee_number%TYPE;
1994 l_emp_count NUMBER;
1995 l_start_date DATE;
1996 l_end_date DATE;
1997 l_source_id NUMBER;
1998 l_flag_for_16aa NUMBER;
1999
2000
2001
2002 CURSOR csr_fetch_gre( p_gre_id IN hr_organization_units.organization_id%TYPE)
2003 IS
2004 SELECT hou.organization_id orgid
2005 FROM hr_all_organization_units hou
2006 , hr_organization_information hoi
2007 WHERE hou.organization_id = hoi.organization_id
2011 AND hou.organization_id = NVL(p_gre_id,hou.organization_id)
2008 AND hoi.org_information_context = 'CLASS'
2009 AND hoi.org_information1 = 'HR_LEGAL'
2010 AND hoi.org_information2 = 'Y'
2012 AND hou.business_group_id = p_business_group_id
2013 AND EXISTS (SELECT 1
2014 FROM pay_action_information pai,
2015 pay_payroll_actions ppa
2016 WHERE pai.action_information_category = 'IN_EOY_ORG'
2017 AND pai.action_information1 = hou.organization_id
2018 AND pai.action_information3 = p_assessment_year
2019 AND pai.action_context_type ='PA'
2020 AND pai.action_context_id = ppa.payroll_action_id
2021 AND ppa.report_qualifier ='IN'
2022 AND ppa.report_type ='IN_EOY_ARCHIVE'
2023 AND ppa.report_category ='ARCHIVE'
2024 AND ROWNUM < 2)
2025 ORDER BY hou.name;
2026
2027 CURSOR csr_fetch_employees( p_gre_id hr_organization_units.organization_id%TYPE)
2028 IS
2029 SELECT MAX(pai.action_context_id) action_context_id
2030 , pai.action_information17 start_date
2031 , pai.action_information1 employee_number
2032 FROM pay_action_information pai
2033 ,per_assignments_f asg
2034 WHERE pai.action_information_category = 'IN_EOY_PERSON'
2035 AND asg.assignment_id = pai.assignment_id
2036 AND asg.business_group_id = p_business_group_id
2037 AND pai.action_information3 = p_gre_id
2038 AND pai.action_information2 = p_assessment_year
2039 AND pai.action_information1 LIKE NVL(p_employee_number,'%')
2040 GROUP BY pai.action_information1,pai.action_information17
2041 ORDER BY LENGTH(pai.action_information1), pai.action_information1;
2042 /* This order by ensures that the employee number is sorted in the ascending
2043 order based on the order of the characters according to length
2044 */
2045
2046 CURSOR csr_emp_source_id(p_start_date DATE
2047 ,p_employee_number VARCHAR2
2048 ,p_gre_id hr_organization_units.organization_id%TYPE
2049 ,p_action_context_id NUMBER)
2050 IS
2051 SELECT pai.source_id Payroll_run_action_id
2052 ,pai.action_information18 end_date
2053 FROM pay_action_information pai
2054 WHERE pai.action_information_category ='IN_EOY_PERSON'
2055 AND pai.action_information17 = p_start_date
2056 AND pai.action_information1 = p_employee_number
2057 AND pai.action_information2 = p_assessment_year
2058 AND pai.action_information3 = p_gre_id
2059 AND pai.action_context_id = p_action_context_id
2060 AND EXISTS (SELECT 1
2061 FROM pay_assignment_actions paa
2062 ,pay_payroll_actions ppa
2063 WHERE pai.source_id = paa.assignment_action_id
2064 AND paa.payroll_action_id = ppa.payroll_action_id
2065 AND ppa.business_group_id = p_business_group_id );
2066
2067
2068 CURSOR csr_global_value(p_global_name ff_globals_f.global_name%TYPE)
2069 IS
2070 SELECT global_value
2071 FROM ff_globals_f
2072 WHERE global_name =p_global_name
2073 AND legislation_code='IN'
2074 AND g_tax_end_date BETWEEN effective_start_date and effective_end_date;
2075
2076 begin
2077 g_debug := hr_utility.debug_enabled;
2078 l_procedure := g_package ||'load_xml';
2079 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2080
2081
2082 IF g_debug THEN
2083 pay_in_utils.trace('**************************************************','********************');
2084 pay_in_utils.trace('Business Group ID',to_char(p_business_group_id ));
2085 pay_in_utils.trace('Assessment Year ',p_assessment_year );
2086 pay_in_utils.trace('GRE Organization ',p_gre_organization );
2087 pay_in_utils.trace('Employee Type ',p_employee_type );
2088 pay_in_utils.trace('Employee Number ',p_employee_number );
2089 pay_in_utils.trace('**************************************************','********************');
2090 END IF;
2091
2092 l_emp_count := 0;
2093 g_assessment_year := p_assessment_year;
2094 g_tax_year := (to_number(SUBSTR(g_assessment_year,1,4)) - 1)||'-'||SUBSTR(g_assessment_year,3,2);
2095 g_tax_end_date := fnd_date.string_to_date(('31/03/'|| SUBSTR(g_assessment_year,1,4)),'DD/MM/YYYY');
2096 g_tax_start_date := ADD_MONTHS(g_tax_end_date,-12) +1;
2097
2098
2099
2100 OPEN csr_global_value('IN_SECTION_80CCE_LIMIT');
2101 FETCH csr_global_value INTO g_80cce_limit;
2102 CLOSE csr_global_value;
2103
2104
2105 FOR gre_record IN csr_fetch_gre(p_gre_organization)
2106 LOOP
2107
2108 /* Fetch the gre id for each GRE*/
2109 l_gre_id := gre_record.orgid;
2110 /* Close and reopen tag for GRE */
2111 IF l_emp_count <>0 THEN
2112 l_open_tag := '</GRE>';
2113 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2114
2115 l_open_tag := '<GRE>';
2116 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2117 build_gre_xml(l_gre_id);
2118 END IF;
2119
2120 /* Fetch the Employees in the GRE and build the XML for each employee */
2121 FOR emp_record IN csr_fetch_employees(l_gre_id)
2122 LOOP
2123 l_action_context_id := emp_record.action_context_id;
2124 l_start_date := emp_record.start_date;
2125 l_emp_number := emp_record.employee_number;
2126
2127 OPEN csr_emp_source_id(l_start_date,l_emp_number,l_gre_id,l_action_context_id);
2128 FETCH csr_emp_source_id INTO l_source_id,l_end_date;
2129 IF csr_emp_source_id%FOUND THEN
2130 IF ((l_end_date = g_tax_end_date AND (p_employee_type = 'TRANSFERRED' OR p_employee_type = 'TERMINATED')) OR
2131 (l_end_date <> g_tax_end_date AND p_employee_type ='CURRENT')
2132 )THEN
2133 NULL;
2134 ELSE
2135 IF l_emp_count = 0 OR l_emp_count > g_chunk_size THEN
2136 IF l_emp_count <> 0 THEN
2137 /* Close all the open tags */
2138 l_open_tag := '</GRE></EOY>';
2139 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2140 /* Close the temporary CLOB opened */
2141 dbms_lob.close(g_tmp_clob);
2142 /* Store the temporary CLOB in the Global CLOB array */
2143 g_clob_cnt := g_clob_cnt + 1;
2144 g_clob(g_clob_cnt) := g_tmp_clob;
2145 /* Reset the employees count to 1 */
2146 l_emp_count := 1;
2147 END IF;
2148 /* Create a new temporary CLOB for writing XML Data */
2149 dbms_lob.createtemporary(g_tmp_clob,FALSE,DBMS_LOB.CALL);
2150 dbms_lob.open(g_tmp_clob,dbms_lob.lob_readwrite);
2151 /* Open the parent Tags */
2152 l_open_tag := '<?xml version="1.0" encoding="UTF-8"?><EOY>';
2153 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2154 /* Write the Common data in the New CLOB created */
2155 write_tag('REPORT_DATE',TO_CHAR(TRUNC(SYSDATE),'DD-MM-YYYY'));
2156 write_tag('ASSESS_YR',SUBSTR(g_assessment_year,1,5) || SUBSTR(g_assessment_year,8,2));
2157 write_tag('FIN_YEAR',g_tax_year);
2158 write_tag('REPORT_DATE_TIME',to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS'));
2159 -- write_tag('DUMMY',pay_us_employee_payslip_web.get_format_value(g_business_group_id,0));
2160 /* The following call gets the GRE Related Data to generate the report*/
2161 l_open_tag := '<GRE>';
2162 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2163
2164 build_gre_xml(l_gre_id);
2165 END IF;
2166 l_emp_count := l_emp_count + 1;
2167 l_open_tag := '<EMPLOYEE>';
2168 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2169 /* The following calls builds the XML related to form16, form 16aa and 12ba
2170 required to generate the report
2171 */
2172
2173 pay_in_utils.set_location(g_debug,'INDIA F16: Building XML for Employee',70);
2174 build_employee_xml(l_action_context_id,l_source_id,p_rem_pay_period,p_flag);
2175 pay_in_utils.set_location(g_debug,'Building XML for Form 16/16AA',80);
2176 build_form16_xml(l_action_context_id,l_source_id,p_rem_pay_period,p_flag,l_flag_for_16aa);
2177 IF (l_flag_for_16aa = -1) THEN
2178 l_emp_count := l_emp_count - 1;
2179 ELSE
2180 pay_in_utils.set_location(g_debug,'Building XML for Form 12BA',90);
2181 init_form12ba_code;
2182 build_form12ba_xml(l_action_context_id,l_source_id);
2183 l_open_tag := '</EMPLOYEE>';
2184 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2185 END IF;
2186 END IF;
2187 END IF;
2188 CLOSE csr_emp_source_id;
2189 END LOOP;
2190
2191
2192 END LOOP;
2193 IF l_emp_count <> 0 THEN
2194 l_open_tag := '</GRE></EOY>';
2195 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2196 /* Close the temporary CLOB opened which is not yet closed*/
2197 dbms_lob.close(g_tmp_clob);
2198 /* Copy the Temporary CLOB into the Global CLOB Array */
2199 g_clob_cnt := g_clob_cnt + 1;
2200 g_clob(g_clob_cnt) := g_tmp_clob;
2201 p_clob_cnt := g_clob_cnt;
2202 ELSE
2203 p_clob_cnt := g_clob_cnt;
2204 END IF;
2205
2206 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,100);
2207 END load_xml;
2208
2209 BEGIN
2210
2211 -- Initialize Globals
2212 g_clob_cnt := 0;
2213 g_fetch_clob_cnt := 0;
2214 -- g_package := 'pay_in_eoy_reports';
2215 g_chunk_size := 10;
2216 g_salary_record(1).Name := 'SECTION_17_1';
2217 g_salary_record(2).Name := 'SECTION_17_2';
2218 g_salary_record(3).Name := 'SECTION_17_3';
2219 g_salary_record(4).Name := 'SECTION_17';
2220 g_salary_record(5).Name := 'SEC10_TOTAL';
2221 g_salary_record(6).Name := 'SEC17_SEC10';
2222 g_salary_record(7).Name := 'ENT_ALLOWANCE';
2223 g_salary_record(8).Name := 'EMPLOYMENT_TAX';
2224 g_salary_record(9).Name := 'SEC16_TOTAL';
2225 g_salary_record(10).Name := 'HEAD_SALARIES';
2226 g_salary_record(11).Name := 'OTHER_INCOME';
2227 g_salary_record(12).Name := 'GROSS_INCOME';
2228 g_salary_record(13).Name := 'TOTAL_INCOME';
2229 g_salary_record(14).Name := 'TAX_ON_INCOME';
2230 g_salary_record(15).Name := 'SURCHARGE';
2231 g_salary_record(16).Name := 'CESS';
2232 g_salary_record(17).Name := 'TAX_PAYABLE';
2233 g_salary_record(18).Name := 'SEC89_RELIEF';
2234 g_salary_record(19).Name := 'TOTAL_TAX_PAYABLE';
2235 g_salary_record(20).Name := 'TDS_DEDUCTED_SANS_ER_TAX';
2236 g_salary_record(21).Name := 'BALANCE_TAX';
2237 g_salary_record(22).Name := 'EMPLOYER_TAX';
2238 g_salary_record(23).Name := 'TDS_DEDUCTED';
2239
2240 END PAY_IN_EOY_REPORTS;