[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_EOY_REPORTS
Source
1 PACKAGE BODY pay_in_eoy_reports AS
2 /* $Header: pyineoyr.pkb 120.38.12020000.6 2012/12/20 13:49:48 anchhetr 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 g_er_type_flag NUMBER;
14 g_org_id hr_organization_units.organization_id%TYPE;
15 l_date_earned DATE;
16
17 qtr_index NUMBER;
18 g_index NUMBER;
19 g_debug BOOLEAN ;
20 g_package CONSTANT VARCHAR2(100) := 'pay_in_eoy_reports.';
21
22 TYPE record_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
23 t_table_1 record_type;
24 t_table_surcharge record_type;
25 t_table_ec record_type;
26
27 TYPE XMLRec
28 IS RECORD
29 (
30 Bank VARCHAR2(2000),
31 VDate DATE,
32 VNumber VARCHAR2(240),
33 DDCheque_Num VARCHAR2(240)
34 );
35 TYPE tXMLTable IS TABLE OF XMLRec INDEX BY BINARY_INTEGER;
36 g_Bank_Details_tbl tXMLTable;
37
38 TYPE Quarter_XMLRec
39 IS RECORD
40 (
41 Quarter varchar2(5),
42 Receipt Varchar2(240),
43 Nature Varchar2(20),
44 OrgID NUMBER,
45 ActionID NUMBER
46 );
47 TYPE Quarter_tXMLTable IS TABLE OF Quarter_XMLRec INDEX BY BINARY_INTEGER;
48 g_qtr_action_id_details Quarter_tXMLTable;
49
50 TYPE Emp_XMLRec
51 IS RECORD
52 (
53 emp_tds VARCHAR2(2000),
54 emp_sur VARCHAR2(240),
55 emp_cess VARCHAR2(240),
56 emp_voucher VARCHAR2(240),
57 emp_amount VARCHAR2(240)
58 );
59 TYPE Emp_tXMLTable IS TABLE OF Emp_XMLRec INDEX BY BINARY_INTEGER;
60 g_emp_challan_details_tbl Emp_tXMLTable;
61
62 g_salary_record pay_in_xml_utils.tXMLTable;
63 g_Other_Income_tbl pay_in_xml_utils.tXMLTable;
64 p_rem_pay_period NUMBER;
65 p_flag NUMBER;
66
67 TYPE clob_tab_type IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
68
69 TYPE perq_record IS RECORD
70 ( perq_value1 pay_action_information.action_information1%TYPE
71 , perq_value2 pay_action_information.action_information1%TYPE
72 );
73
74 TYPE t_perq_record is table of perq_record INDEX BY BINARY_INTEGER;
75
76 g_perq_record t_perq_record;
77 g_clob clob_tab_type;
78
79 g_80cce_limit NUMBER;
80
81
82
83 --------------------------------------------------------------------------
84 -- --
85 -- Name : init_form12ba_code --
86 -- Type : PROCEDURE --
87 -- Access : Private --
88 -- Description : This procedure initializes the form12ba record --
89 -- --
90 -- Parameters : None --
91 --------------------------------------------------------------------------
92 PROCEDURE init_form12ba_code
93 IS
94 l_procedure varchar2(100);
95
96 BEGIN
97 g_debug := hr_utility.debug_enabled;
98 l_procedure := g_package||'init_form12ba_code';
99 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
100
101 g_perq_record(1).perq_value1 := 0;
102 g_perq_record(1).perq_value2 := 0;
103
104 g_perq_record(2).perq_value1 := 0;
105 g_perq_record(2).perq_value2 := 0;
106
107 g_perq_record(3).perq_value1 := 0;
108 g_perq_record(3).perq_value2 := 0;
109
110 g_perq_record(4).perq_value1 := 0;
111 g_perq_record(4).perq_value2 := 0;
112
113 g_perq_record(5).perq_value1 := 0;
114 g_perq_record(5).perq_value2 := 0;
115
116 g_perq_record(6).perq_value1 := 0;
117 g_perq_record(6).perq_value2 := 0;
118
119 g_perq_record(7).perq_value1 := 0;
120 g_perq_record(7).perq_value2 := 0;
121
122 g_perq_record(8).perq_value1 := 0;
123 g_perq_record(8).perq_value2 := 0;
124
125 g_perq_record(9).perq_value1 := 0;
126 g_perq_record(9).perq_value2 := 0;
127
128 g_perq_record(10).perq_value1 := 0;
129 g_perq_record(10).perq_value2 := 0;
130
131 g_perq_record(11).perq_value1 := 0;
132 g_perq_record(11).perq_value2 := 0;
133
134 g_perq_record(12).perq_value1 := 0;
135 g_perq_record(12).perq_value2 := 0;
136
137 g_perq_record(13).perq_value1 := 0;
138 g_perq_record(13).perq_value2 := 0;
139
140 g_perq_record(14).perq_value1 := 0;
141 g_perq_record(14).perq_value2 := 0;
142
143 g_perq_record(15).perq_value1 := 0;
144 g_perq_record(15).perq_value2 := 0;
145
146 g_perq_record(16).perq_value1 := 0;
147 g_perq_record(16).perq_value2 := 0;
148
149 g_perq_record(17).perq_value1 := 0;
150 g_perq_record(17).perq_value2 := 0;
151
152 g_perq_record(18).perq_value1 := 0;
153 g_perq_record(18).perq_value2 := 0;
154
155 g_perq_record(19).perq_value1 := 0;
156 g_perq_record(19).perq_value2 := 0;
157
158 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
159
160 END init_form12ba_code;
161
162 --------------------------------------------------------------------------
163 -- --
164 -- Name : GET_MAX_CONTEXT_ID --
165 -- Type : PROCEDURE --
166 -- Access : Private --
167 -- Description : This procedure gets the maximum action context id --
168 -- for a specified GRE in an assessment year --
169 -- --
170 -- Parameters : --
171 -- IN : p_gre_id NUMBER --
172 -- : p_assessment_year VARCHAR2 --
173 --------------------------------------------------------------------------
174 FUNCTION get_max_context_id ( p_gre_id IN hr_organization_units.organization_id%TYPE
175 , p_assessment_year IN pay_action_information.action_information3%TYPE
176 )
177 RETURN NUMBER
178 IS
179 --
180 CURSOR csr_max_action_context_id
181 IS
182 SELECT MAX(pai.action_context_id)
183 FROM pay_action_information pai
184 WHERE pai.action_information_category ='IN_EOY_ORG'
185 AND pai.action_context_type = 'PA'
186 AND pai.Action_information1 = to_char(p_gre_id) /* Bug 11698592 */
187 AND pai.action_information3 = p_assessment_year;
188 --
189 l_procedure VARCHAR2(100);
190 l_action_context_id pay_assignment_actions.assignment_action_id%TYPE;
191 --
192 BEGIN
193 g_debug := hr_utility.debug_enabled;
194 l_procedure := g_package ||'get_max_context_id';
195 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
196
197
198 IF g_debug THEN
199 pay_in_utils.trace('**************************************************','********************');
200 pay_in_utils.trace('p_gre_id',to_char(p_gre_id));
201 pay_in_utils.trace('p_assessment_year',to_char(p_assessment_year));
202 pay_in_utils.trace('**************************************************','********************');
203 END IF;
204
205 OPEN csr_max_action_context_id;
206 FETCH csr_max_action_context_id INTO l_action_context_id;
207 CLOSE csr_max_action_context_id;
208
209 IF g_debug THEN
210 pay_in_utils.trace('l_action_context_id',l_action_context_id);
211 END IF;
212 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
213 RETURN l_action_context_id;
214
215 END get_max_context_id;
216
217 --------------------------------------------------------------------------
218 -- --
219 -- Name : GET_LOCATION_DETAILS --
220 -- Type : FUNCTION --
221 -- Access : Private --
225 -- IN : p_location_id hr_locations.location_id --
222 -- Description : This procedure gets the gre location details --
223 -- --
224 -- Parameters : --
226 -- : p_concatenate VARCHAR2 --
227 -- p_field VARCHAR2 --
228 --------------------------------------------------------------------------
229 FUNCTION get_location_details ( p_location_id IN hr_locations.location_id%TYPE
230 , p_concatenate IN VARCHAR2 DEFAULT 'N'
231 , p_field IN VARCHAR2 DEFAULT NULL
232 )
233 RETURN VARCHAR2
234 IS
235
236 CURSOR csr_get_location_details
237 IS
238 SELECT hr_loc.address_line_1
239 , hr_loc.address_line_2
240 , hr_loc.address_line_3
241 , hr_loc.loc_information14
242 , hr_loc.loc_information15
243 , hr_general.decode_lookup('IN_STATES',hr_loc.loc_information16)
244 , hr_general.decode_lookup('PER_US_COUNTRY_CODE',hr_loc.country)
245 , hr_loc.postal_code
246 , hr_loc.loc_information16
247 FROM hr_locations hr_loc
248 WHERE location_id = p_location_id;
249
250 l_procedure VARCHAR2(100);
251 l_location_address1 hr_locations.address_line_1%TYPE;
252 l_location_address2 hr_locations.address_line_2%TYPE;
253 l_location_address3 hr_locations.address_line_3%TYPE;
254 l_location_address4 hr_locations.loc_information14%TYPE;
255 l_location_city hr_locations.loc_information15%TYPE;
256 l_location_state hr_locations.loc_information16%TYPE;
257 l_location_country hr_locations.country%TYPE;
258 l_location_zipcode hr_locations.postal_code%TYPE;
259 l_state_code hr_locations.loc_information16%TYPE;
260 l_details VARCHAR2(1000);
261
262 BEGIN
263 g_debug := hr_utility.debug_enabled;
264 l_procedure := g_package ||'get_location_details';
265 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
266
267
268 IF g_debug THEN
269 pay_in_utils.trace('**************************************************','********************');
270 pay_in_utils.trace('p_location_id',to_char(p_location_id));
271 pay_in_utils.trace('p_concatenate',p_concatenate);
272 pay_in_utils.trace('p_field', p_field);
273 pay_in_utils.trace('**************************************************','********************');
274 END IF;
275
276 OPEN csr_get_location_details;
277 FETCH csr_get_location_details
278 INTO l_location_address1
279 , l_location_address2
280 , l_location_address3
281 , l_location_address4
282 , l_location_city
283 , l_location_state
284 , l_location_country
285 , l_location_zipcode
286 , l_state_code;
287 CLOSE csr_get_location_details;
288
289 IF p_concatenate = 'Y' THEN
290
291 SELECT l_location_address1 || DECODE(l_location_address1,NULL,NULL,',' || fnd_global.local_chr(10)) ||
292 l_location_address2 || DECODE(l_location_address2,NULL,NULL,',' || fnd_global.local_chr(10)) ||
293 l_location_address3 || DECODE(l_location_address3,NULL,NULL,',' || fnd_global.local_chr(10)) ||
294 l_location_address4 || DECODE(l_location_address4,NULL,NULL,',' || fnd_global.local_chr(10)) ||
295 l_location_city || DECODE(l_location_city ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
296 l_location_state || DECODE(l_location_state ,NULL,NULL,',')||
297 l_location_country || DECODE(l_location_country ,NULL,NULL,',')||
298 l_location_zipcode
299 INTO l_details
300 FROM DUAL;
301
302 ELSIF p_field = 'EMPLOYER_ADDRESS1' THEN
303 l_details := l_location_address1;
304 ELSIF p_field = 'EMPLOYER_ADDRESS2' THEN
305 l_details := l_location_address2;
306 ELSIF p_field = 'EMPLOYER_ADDRESS3' THEN
307 l_details := l_location_address3;
308 ELSIF p_field = 'EMPLOYER_ADDRESS4' THEN
309 l_details := l_location_address4;
310 ELSIF p_field = 'CITY' THEN
311 l_details := l_location_city;
312 ELSIF p_field = 'EMPLOYER_STATE' THEN
313 l_details := l_location_state;
314 ELSIF p_field = 'EMPLOYER_STATE_CODE' THEN
315 l_details := l_state_code;
316 ELSIF p_field = 'POSTAL_CODE' THEN
317 l_details := l_location_zipcode;
318 ELSIF p_field = 'COUNTRY' THEN
319 l_details := l_location_country;
320 END IF;
321 l_details :=RTRIM(l_details,fnd_global.local_chr(10));
322 l_details :=RTRIM(l_details,',');
323
324 IF g_debug THEN
325 pay_in_utils.trace('l_details', l_details );
326 END IF;
327
328 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
329
330 RETURN l_details;
331
332 END get_location_details;
333
334 --------------------------------------------------------------------------
335 -- --
336 -- Name : GET_ADDRESS_DETAILS --
337 -- Type : FUNCTION --
338 -- Access : Private --
339 -- Description : This procedure gets the employee address details --
340 -- --
341 -- Parameters : --
345 --------------------------------------------------------------------------
342 -- IN : p_location_id per_addresses.address_id --
343 -- : p_concatenate VARCHAR2 --
344 -- p_field VARCHAR2 --
346 FUNCTION get_address_details ( p_address_id IN per_addresses.address_id%TYPE
347 , p_concatenate IN VARCHAR2 DEFAULT 'N'
348 , p_field IN VARCHAR2 DEFAULT NULL
349 )
350 RETURN VARCHAR2
351 IS
352
353 CURSOR csr_get_address_details
354 IS
355 SELECT pad.address_line1
356 , pad.address_line2
357 , pad.address_line3
358 , pad.add_information13
359 , pad.add_information14
360 , hr_general.decode_lookup('IN_STATES',pad.add_information15)
361 , hr_general.decode_lookup('PER_US_COUNTRY_CODE',pad.country)
362 , pad.postal_code
363 FROM per_addresses pad
364 WHERE pad.address_id = p_address_id;
365
366 l_procedure VARCHAR2(100);
367 l_location_address1 hr_locations.address_line_1%TYPE;
368 l_location_address2 hr_locations.address_line_2%TYPE;
369 l_location_address3 hr_locations.address_line_3%TYPE;
370 l_location_address4 hr_locations.loc_information14%TYPE;
371 l_location_city hr_locations.loc_information15%TYPE;
372 l_location_state hr_locations.loc_information16%TYPE;
373 l_location_country hr_locations.country%TYPE;
374 l_location_zipcode hr_locations.postal_code%TYPE;
375 l_details VARCHAR2(1000);
376
377 BEGIN
378 g_debug := hr_utility.debug_enabled;
379 l_procedure := g_package ||'get_location_details';
380 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
381
382 IF g_debug THEN
383 pay_in_utils.trace('**************************************************','********************');
384 pay_in_utils.trace('p_address_id ',p_address_id );
385 pay_in_utils.trace('p_concatenate',p_concatenate);
386 pay_in_utils.trace('p_field' ,p_field );
387 pay_in_utils.trace('**************************************************','********************');
388 END IF;
389
390 OPEN csr_get_address_details;
391 FETCH csr_get_address_details
392 INTO l_location_address1
393 , l_location_address2
394 , l_location_address3
395 , l_location_address4
396 , l_location_city
397 , l_location_state
398 , l_location_country
399 , l_location_zipcode;
400 CLOSE csr_get_address_details;
401
402 IF p_concatenate = 'Y' THEN
403
404 SELECT l_location_address1 || DECODE(l_location_address1,NULL,NULL,',' || fnd_global.local_chr(10)) ||
405 l_location_address2 || DECODE(l_location_address2,NULL,NULL,',' || fnd_global.local_chr(10)) ||
406 l_location_address3 || DECODE(l_location_address3,NULL,NULL,',' || fnd_global.local_chr(10)) ||
407 l_location_address4 || DECODE(l_location_address4,NULL,NULL,',' || fnd_global.local_chr(10)) ||
408 l_location_city || DECODE(l_location_city ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
409 l_location_state || DECODE(l_location_state ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
410 l_location_country
411 INTO l_details
412 FROM DUAL;
413
414 ELSIF p_field = 'ADDRESS1' THEN
415 l_details := l_location_address1;
416 ELSIF p_field = 'ADDRESS2' THEN
417 l_details := l_location_address2;
418 ELSIF p_field = 'ADDRESS3' THEN
419 l_details := l_location_address3;
420 ELSIF p_field = 'ADDRESS4' THEN
421 l_details := l_location_address4;
422 ELSIF p_field = 'CITY' THEN
423 l_details := l_location_city;
424 ELSIF p_field = 'STATE' THEN
425 l_details := l_location_state;
426 ELSIF p_field = 'POSTAL_CODE' THEN
427 l_details := l_location_zipcode;
428 ELSIF p_field = 'COUNTRY' THEN
429 l_details := l_location_country;
430 END IF;
431
432 l_details :=RTRIM(l_details,','||fnd_global.local_chr(10));
433
434
435
436 IF g_debug THEN
437 pay_in_utils.trace('l_details',l_details);
438 END IF;
439
440 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
441 RETURN l_details;
442
443 END get_address_details;
444
445 --------------------------------------------------------------------------
446 -- --
447 -- Name : WRITE_TAG --
448 -- Type : PROCEDURE --
449 -- Access : Public --
450 -- Description : This procedure appends the tag --
451 -- --
452 -- Parameters : --
453 -- IN : p_tag_name VARCHAR2 --
454 -- p_tag_value VARCHAR2 --
455 --------------------------------------------------------------------------
456 PROCEDURE write_tag ( p_tag_name IN VARCHAR2
457 , p_tag_value IN VARCHAR2)
458 IS
459 l_tag VARCHAR2(10000);
460 l_procedure varchar2(100);
461
462 BEGIN
463 g_debug := hr_utility.debug_enabled;
464 l_procedure := g_package ||'write_tag';
465 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
466
467 IF g_debug THEN
468 pay_in_utils.trace('**************************************************','********************');
469 pay_in_utils.trace('p_tag_name ',p_tag_name );
470 pay_in_utils.trace('p_tag_value',p_tag_value);
471 pay_in_utils.trace('**************************************************','********************');
472 END IF;
473
474 l_tag := pay_in_xml_utils.getTag( p_tag_name => p_tag_name
475 , p_tag_value => p_tag_value
476 );
477
478 dbms_lob.writeAppend(g_tmp_clob,length(l_tag),l_tag);
479 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
480 END write_tag;
481
482 --------------------------------------------------------------------------
483 -- --
484 -- Name : get_qtr_tax_deducted --
485 -- Type : FUNCTION --
486 -- Access : Private --
487 -- Description : This function returns the amount of tax deducted --
488 -- start of finanacial yeat to latest run in a quarter --
489 -- --
490 -- Parameters : --
491 -- IN : p_assignment_id NUMBER --
492 -- : p_tax_unit_id hr_organization_units.organization_id%TYPE --
493 -- p_qtr_start DATE --
494 -- p_qtr_end DATE --
495 --------------------------------------------------------------------------
496 FUNCTION get_qtr_tax_deducted(p_assignment_id IN NUMBER,
497 p_tax_unit_id IN hr_organization_units.organization_id%TYPE,
498 p_qtr_start date,
499 p_qtr_end date)
500 RETURN NUMBER IS
501 cursor csr_action_id is
502 SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
503 FROM pay_assignment_actions paa
504 ,pay_payroll_actions ppa
505 ,per_assignments_f paf
506 WHERE paf.assignment_id = paa.assignment_id
507 AND paf.assignment_id = p_assignment_id
508 AND paa.tax_unit_id = p_tax_unit_id
509 AND paa.payroll_action_id = ppa.payroll_action_id
510 AND ppa.action_type IN('R','Q','I','B')
511 AND ppa.payroll_id = paf.payroll_id
512 AND ppa.action_status ='C'
513 AND ppa.effective_date between p_qtr_start and p_qtr_end
514 AND paa.source_action_id IS NULL
515 AND (1 = DECODE(ppa.action_type,'I',1,0)
516 OR EXISTS (SELECT ''
517 FROM pay_action_interlocks intk,
518 pay_assignment_actions paa1,
519 pay_payroll_actions ppa1
520 WHERE intk.locked_action_id = paa.assignment_Action_id
521 AND intk.locking_action_id = paa1.assignment_action_id
522 AND paa1.payroll_action_id =ppa1.payroll_action_id
523 AND paa1.assignment_id = p_assignment_id
524 AND ppa1.action_type in('P','U')
525 AND ppa.action_type in('R','Q','B')
526 AND ppa1.action_status ='C'
527 AND ppa1.effective_date BETWEEN p_qtr_start and p_qtr_end
528 AND ROWNUM =1 ));
529
530 CURSOR c_defined_balance_id
531 IS
532 SELECT pdb.defined_balance_id balance_id
533 ,pbt.balance_name balance_name
534 FROM pay_balance_types pbt
535 ,pay_balance_dimensions pbd
536 ,pay_defined_balances pdb
537 WHERE pbt.balance_name IN('Education Cess This Pay'
538 ,'Income Tax This Pay'
539 ,'Sec and HE Cess This Pay'
540 ,'TDS on Direct Payments'
541 )
542 AND pbd.dimension_name='_ASG_LE_YTD'
543 AND pbt.legislation_code = 'IN'
544 AND pbd.legislation_code = 'IN'
545 AND pbt.balance_type_id = pdb.balance_type_id
546 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
547
548 TYPE t_bal_name_rec IS RECORD
549 (
550 balance_name VARCHAR2(240)
551 );
552 TYPE t_bal_name_tab IS TABLE OF t_bal_name_rec
553 INDEX BY BINARY_INTEGER;
554 g_bal_name_tab t_bal_name_tab;
555 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
556 g_context_table pay_balance_pkg.t_context_tab;
557 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
558 i NUMBER;
559 l_max_asg_action_id number;
560 l_total_quarter number;
561 BEGIN
562 i := 1;
563 IF g_debug THEN
564 pay_in_utils.trace('p_tax_unit_id ',to_char(p_tax_unit_id ));
565 pay_in_utils.trace('p_assignment_id ',p_assignment_id);
566 pay_in_utils.trace('p_qtr_start ',p_qtr_start);
567 pay_in_utils.trace('p_qtr_end ',p_qtr_end);
568 END IF;
569
570 g_bal_name_tab.DELETE;
571 g_balance_value_tab.DELETE;
572 g_result_table.DELETE;
573 g_context_table.DELETE;
574 g_context_table(1).tax_unit_id := p_tax_unit_id;
575 l_total_quarter := 0;
576
577 FOR c_rec IN c_defined_balance_id
578 LOOP
579 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
580 g_bal_name_tab(i).balance_name := c_rec.balance_name;
581 i := i + 1;
582 END LOOP;
583 FOR c_rec_action IN csr_action_id
584 LOOP
585 l_max_asg_action_id := c_rec_action.run_asg_action_id;
586 END LOOP;
587 -- pay_balance_pkg.get_value(l_max_asg_action_id,g_balance_value_tab);
588 pay_balance_pkg.get_value(p_assignment_action_id => l_max_asg_action_id
589 ,p_defined_balance_lst => g_balance_value_tab
590 ,p_context_lst => g_context_table
591 ,p_output_table => g_result_table
592 );
593
594 FOR i IN 1..g_result_table.COUNT LOOP
595 l_total_quarter := l_total_quarter + g_result_table(i).balance_value;
596 END LOOP;
597 RETURN l_total_quarter;
598 EXCEPTION
599 WHEN OTHERS THEN
600 l_total_quarter := 0;
601 RETURN l_total_quarter;
602 END get_qtr_tax_deducted;
603
604 --------------------------------------------------------------------------
605 -- --
606 -- Name : BUILD_GRE_XML --
607 -- Type : PROCEDURE --
608 -- Access : Public --
609 -- Description : This procedure builds the XML for GRE --
610 -- --
611 -- Parameters : --
612 -- IN : p_gre_id NUMBER --
613 --------------------------------------------------------------------------
614 PROCEDURE build_gre_xml ( p_gre_id IN hr_organization_units.organization_id%TYPE,
615 p_designation VARCHAR2)
616 IS
617 CURSOR csr_get_gre_details (p_action_context_id NUMBER)
618 IS
619 SELECT 'ER_LEGAL' er_legal
620 , UPPER(action_information8) er_legal_value
621 , 'ER_TYPE' er_type
622 , action_information20 er_type_value
623 , 'ER_ADDRESS' er_address
624 , get_location_details ( TO_NUMBER(action_information7)
625 , 'Y') er_address_value
626 , 'ER_NAME' er_org
627 , UPPER(action_information6) er_org_value
628 , 'TAN' er_tan
629 , UPPER(action_information4) er_tan_value
630 , 'GIR' er_gir
631 , UPPER(action_information2) er_gir_value
632 , 'TDS_CIRCLE' er_tds
633 , UPPER(action_information9) er_tds_value
634 , 'REP_NAME' rep_name
635 , UPPER(Action_information11) rep_name_value
636 , 'REP_TITLE_NAME' rep_title_name
637 , Upper(Action_information12) || Upper(Action_information11) rep_title_value
638 , 'REP_FATHER_NAME' rep_father_name
639 , UPPER(Action_information15) || UPPER(Action_information14) rep_father_value
640 , 'REP_POSITION' rep_designation
641 , CASE WHEN p_designation ='POS' THEN Action_information13
642 ELSE Action_information21 END rep_designation_value
643 ,'ITAX_ADDRESS' itax_address
644 ,get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS1') || DECODE(get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS1'),NULL,NULL,',' || fnd_global.local_chr(10)) ||
645 get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS2') || DECODE(get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS2'),NULL,NULL,',' || fnd_global.local_chr(10)) ||
649 , get_location_details ( TO_NUMBER(action_information19),'N','CITY') itax_city_value
646 get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS3') || DECODE(get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS3'),NULL,NULL,',' || fnd_global.local_chr(10)) ||
647 get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS4') itax_address_value
648 , 'ITAX_CITY' itax_city
650 ,'ITAX_PIN_CODE' itax_pin_code
651 , get_location_details ( TO_NUMBER(action_information19),'N','POSTAL_CODE') itax_pin_code_value
652 , 'PLACE' gre_place
653 , pay_in_eoy_reports.get_location_details ( TO_NUMBER(action_information7)
654 , NULL
655 , 'CITY') gre_place_value
656 ,'DIG_SIGN' dig_sign_name
657 ,Action_information22 dig_sign_value
658 FROM pay_action_information
659 WHERE action_context_id = p_action_context_id
660 AND action_information_category = 'IN_EOY_ORG'
661 AND action_information1 = to_char(p_gre_id)
662 AND ROWNUM =1;
663
664
665
666 --
667 -- Bug # 4506944 : Changed the cursor to include Bank Code instead of Bank Name and Branch,
668 -- Cheque /DD No and Transfer Voucher Number
669 --
670 CURSOR csr_bank_pymt IS
671 ((SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
672 ,hoi_bank.org_information4 Bank
673 ,hoi_challan.org_information3 Voucher_Num
674 ,hoi_challan.org_information11 DD_Cheque_Num
675 FROM hr_organization_information hoi_bank
676 ,hr_organization_information hoi_challan
677 WHERE hoi_bank.organization_id = p_gre_id
678 AND hoi_challan.organization_id = hoi_bank.organization_id
679 AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
680 AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
681 AND hoi_bank.org_information_id = hoi_challan.org_information5
682 AND hoi_challan.org_information12 = 'N'
683 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)
684 )
685 UNION ALL
686 (SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
687 ,hoi_challan.org_information5 Bank
688 ,hoi_challan.org_information3 Voucher_Num
689 ,hoi_challan.org_information11 DD_Cheque_Num
690 FROM hr_organization_information hoi_challan
691 WHERE hoi_challan.organization_id = p_gre_id
692 AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
693 AND hoi_challan.org_information12 = 'Y'
694 AND hoi_challan.org_information5 is null
695 AND hoi_challan.org_information6 is null
696 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)
697 )) ORDER BY Payment_Date;
698
699 CURSOR csr_form24q_receipt IS
700 SELECT org_information2 quarter,DECODE(org_information6,'O',' Regular','C',' Correction') Nature,
701 org_information4 receipt,
702 organization_id orgID,
703 to_number(org_information3) ActionID
704 FROM hr_organization_information
705 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
706 AND org_information1 = g_assessment_year
707 AND organization_id = p_gre_id
708 AND org_information5 = 'A'
709 ORDER BY quarter,Nature desc;
710
711
712 l_pymt_date VARCHAR2(240);
713 l_procedure VARCHAR2(100);
714 l_open_tag VARCHAR2(100);
715 l_last_quarter VARCHAR2(10);
716 l_action_context_id pay_assignment_actions.assignment_action_id%TYPE;
717
718
719 BEGIN
720 g_debug := hr_utility.debug_enabled;
721 l_procedure := g_package ||'build_gre_xml';
722 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
723
724
725 g_Bank_Details_tbl.DELETE;
726 g_qtr_action_id_details.DELETE;
727
728
729 IF g_debug THEN
730 pay_in_utils.trace('p_gre_id ',to_char(p_gre_id ));
731 pay_in_utils.trace('p_designation ',p_designation);
732 END IF;
733
734 l_action_context_id := get_max_context_id(p_gre_id,g_assessment_year);
735 g_er_type_flag := 0;
736 g_org_id := p_gre_id;
737
738
739 FOR i IN csr_get_gre_details(l_action_context_id)
740 LOOP
741 write_tag(i.er_legal,i.er_legal_value);
742 IF (i.er_type_value = 'A' OR i.er_type_value = 'S' OR i.er_type_value = 'D' OR i.er_type_value = 'E'
743 OR i.er_type_value = 'G' OR i.er_type_value = 'H' OR i.er_type_value = 'L' OR i.er_type_value = 'N')
744 THEN
745 g_er_type_flag := 1;
746 END IF;
747 write_tag(i.er_type,g_er_type_flag);
748 write_tag(i.er_address,UPPER(i.er_address_value));
749 write_tag(i.er_org,i.er_org_value);
750 write_tag(i.er_tan,i.er_tan_value);
751 write_tag(i.er_gir,i.er_gir_value);
752 write_tag(i.er_tds,i.er_tds_value);
753 write_tag(i.rep_name,i.rep_name_value);
754 write_tag(i.rep_title_name,i.rep_title_value);
755 write_tag(i.rep_father_name,i.rep_father_value);
756 write_tag(i.rep_designation,i.rep_designation_value);
757 write_tag(i.itax_address,i.itax_address_value);
758 write_tag(i.itax_city,i.itax_city_value);
759 write_tag(i.itax_pin_code,i.itax_pin_code_value);
760 write_tag(i.gre_place,i.gre_place_value);
761 write_tag(i.dig_sign_name,i.dig_sign_value);
762 END LOOP;
763
764
765 --
766 -- Bug 4506944 : Changed as part of changes to be done to Form 16/16AA
767 --
768 g_index := 0;
769 qtr_index := 0;
770
771 FOR i IN csr_bank_pymt
772 LOOP
773 l_pymt_date:= i.Payment_date;
774
775 g_index := g_index + 1;
776 g_Bank_Details_tbl(g_index).VDate := l_pymt_date;
777 g_Bank_Details_tbl(g_index).Bank := i.Bank;
778 g_Bank_Details_tbl(g_index).DDCheque_Num := i.DD_Cheque_Num;
779 g_Bank_Details_tbl(g_index).VNumber := i.Voucher_Num;
780 END LOOP;
781
782 l_last_quarter :='N';
783
784 FOR i in csr_form24q_receipt
785 LOOP
786
787 qtr_index:= qtr_index + 1 ;
788 g_qtr_action_id_details(qtr_index).Quarter := i.quarter;
789 g_qtr_action_id_details(qtr_index).Receipt := i.receipt;
790 g_qtr_action_id_details(qtr_index).Nature := i.Nature;
791 g_qtr_action_id_details(qtr_index).OrgID := i.OrgID;
792 g_qtr_action_id_details(qtr_index).ActionID := i.ActionID;
793 END LOOP;
794 pay_in_utils.set_location(g_debug,'At: '||l_procedure,14);
795 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
796
797 EXCEPTION
798
799 WHEN OTHERS THEN
800 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
801 RAISE;
802
803 END build_gre_xml;
804
805 --------------------------------------------------------------------------
806 -- --
807 -- Name : BUILD_EMPLOYEE_XML --
808 -- Type : PROCEDURE --
809 -- Access : Public --
810 -- Description : This procedure builds the XML for Employee --
811 -- --
812 -- Parameters : --
813 -- IN : p_action_context_id NUMBER --
814 -- p_source_id NUMBER --
815 -- p_rem_pay_period NUMBER --
816 -- p_flag NUMBER --
817 --------------------------------------------------------------------------
818 PROCEDURE build_employee_xml (p_action_context_id IN pay_assignment_actions.assignment_action_id%TYPE
819 ,p_source_id IN pay_payroll_actions.payroll_action_id%TYPE
820 ,p_designation IN VARCHAR2
821 ,p_rem_pay_period OUT NOCOPY NUMBER
822 ,p_flag OUT NOCOPY NUMBER
823 )
824 IS
825
826 l_procedure VARCHAR2(100);
827
828 CURSOR csr_get_person_data
829 IS
830 SELECT 'EID' empno_tag
831 , pai.action_information1 empno_value
832 , 'EE_DETAILS' emp_details
833 ,CASE WHEN p_designation ='POS' THEN
834 UPPER(pai.action_information6
835 || pai.action_information5
836 || DECODE(pai.action_information9,NULL,'',fnd_global.local_chr(10))
837 || pai.action_information9)
838 ELSE
839 UPPER(pai.action_information6
840 || pai.action_information5
841 || DECODE(pai.action_information22,NULL,'',fnd_global.local_chr(10))
842 || pai.action_information22)
843 END emp_details_value
844 , 'E_F_NAME' emp_full_name
845 , UPPER(pai.action_information5) emp_full_value
846 , 'E_TITLE' emp_title
847 , UPPER(pai.action_information6) emp_title_value
848 , 'PAN' emp_pan
849 , DECODE(pai.action_information4,'Y','APPLIED FOR','N','',pai.action_information4) emp_pan_value
850 , 'E_DESG' emp_designation
851 , CASE WHEN p_designation ='POS' THEN
852 UPPER(pai.action_information9)
853 ELSE UPPER(pai.action_information22)
854 END emp_designation_value
855 , 'E_FAT_NAME' emp_father_name
856 , UPPER(pai.action_information7) emp_father_value
857 , 'DOB' emp_dob
858 , TO_CHAR(fnd_date.canonical_to_date(pai.action_information10),'DD-MM-YYYY') emp_dob_value
859 , 'GENDER' emp_gender
860 , UPPER(pai.action_information11) emp_gender_value
861 , 'E_INTEREST' emp_interest
862 , DECODE(pai.action_information12,'N','No','Y','Yes') emp_interest_value
863 , 'ASG_START' emp_asg_start
864 , TO_DATE(pai.action_information17,'DD-MM-RRRR') emp_asg_start_value
865 , 'ASG_END' emp_asg_end
866 , TO_DATE(pai.action_information18,'DD-MM-RRRR') emp_asg_end_value
867 , 'E_ADDRESS' emp_address
868 , get_address_details( pai.action_information14
869 , 'Y','NULL'
870 ) emp_address_value
871 , 'EMP_POSTAL_CODE' emp_zipcode
872 , get_address_details( pai.action_information14
873 , 'N', 'POSTAL_CODE'
874 ) emp_zipcode_value
875 , action_information20 emp_date_earned
876 , assignment_id emp_asg_id
877 , action_information15 emp_resident_status
878 , 'EMP_PHONE' emp_phone
879 , action_information16 emp_phone_value
880 , 'EMP_EMAIL' emp_email
881 , action_information21 emp_email_value
882 FROM pay_action_information pai
883 WHERE pai.action_information_category = 'IN_EOY_PERSON'
884 AND pai.action_context_id = p_action_context_id
885 AND pai.source_id = p_source_id;
886
887
888
889 CURSOR csr_payroll_id(p_assignment_id NUMBER,p_date DATE)
890 IS
891 SELECT paf.payroll_id
892 FROM per_all_assignments_f paf
893 WHERE paf.assignment_id =p_assignment_id
894 AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
895
896 l_total_pay_period NUMBER;
897 l_current_pay_period NUMBER;
898 l_asg_id NUMBER;
899 l_date VARCHAR2(30);
900 --l_date_earned DATE;
901 l_resident_status VARCHAR2(30);
902 l_payroll_id NUMBER;
903 l_asg_end DATE;
904
905 BEGIN
906 g_debug := hr_utility.debug_enabled;
907 l_procedure := g_package ||'build_employee_xml';
908 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
909
910
911 IF g_debug THEN
912 pay_in_utils.trace('**************************************************','********************');
913 pay_in_utils.trace('p_action_context_id ',p_action_context_id );
914 pay_in_utils.trace('p_source_id ',p_source_id );
915 pay_in_utils.trace('p_designation ',p_designation );
916 pay_in_utils.trace('**************************************************','********************');
917 END IF;
918
919
920 FOR i IN csr_get_person_data
921 LOOP
922 write_tag(i.empno_tag,i.empno_value);
923 write_tag(i.emp_details,i.emp_details_value);
924 write_tag(i.emp_full_name,i.emp_full_value);
925 write_tag(i.emp_title,i.emp_title_value);
926 write_tag(i.emp_pan,i.emp_pan_value);
927 write_tag(i.emp_designation,i.emp_designation_value);
928 write_tag(i.emp_father_name,i.emp_father_value);
929 write_tag(i.emp_dob,i.emp_dob_value);
930 write_tag(i.emp_gender,i.emp_gender_value);
931 write_tag(i.emp_interest,i.emp_interest_value);
932 write_tag(i.emp_asg_start,to_char(i.emp_asg_start_value,'DD-Mon-RRRR'));
933 write_tag(i.emp_asg_end,to_char(i.emp_asg_end_value,'DD-Mon-RRRR'));
934 write_tag(i.emp_address,i.emp_address_value);
935 write_tag(i.emp_zipcode,i.emp_zipcode_value);
936 write_tag(i.emp_phone,i.emp_phone_value);
937 write_tag(i.emp_email,i.emp_email_value);
938 l_date := i.emp_date_earned;
939 l_asg_end := i.emp_asg_end_value;
940 l_resident_status := i.emp_resident_status;
941 l_asg_id := i.emp_asg_id;
942 END LOOP;
943
944
945 l_date_earned := fnd_date.canonical_to_date(l_date);
946
947 OPEN csr_payroll_id(l_asg_id,l_date_earned);
948 FETCH csr_payroll_id INTO l_payroll_id;
949 CLOSE csr_payroll_id;
950
951 l_total_pay_period := pay_in_tax_utils.get_period_number(l_payroll_id,l_asg_end);
952 l_current_pay_period := pay_in_tax_utils.get_period_number(l_payroll_id,l_date_earned);
953 p_rem_pay_period := GREATEST((l_total_pay_period - l_current_pay_period),0);
954
955
956
957 IF(l_resident_status = 'RO') THEN
958 p_flag := 1;
959 ELSE
960 p_flag := 0;
961 END IF;
962
963 IF g_debug THEN
964 pay_in_utils.trace('**************************************************','********************');
965 pay_in_utils.trace('p_rem_pay_period ',to_char(p_rem_pay_period ));
966 pay_in_utils.trace('p_flag ',to_char(p_flag ));
967 pay_in_utils.trace('**************************************************','********************');
968 END IF;
969
970 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
971
972
973 EXCEPTION
974 --
975 WHEN OTHERS THEN
976 pay_in_utils.set_location(g_debug,'Error in: '||l_procedure,30);
977 RAISE;
978 END build_employee_xml;
979
980 --------------------------------------------------------------------------
981 -- --
982 -- Name : BUILD_FORM16_XML --
986 -- --
983 -- Type : PROCEDURE --
984 -- Access : Public --
985 -- Description : This procedure builds the XML for Form 16 and 16AA --
987 -- Parameters : --
988 -- IN : p_action_context_id NUMBER --
989 -- p_source_id NUMBER --
990 -- p_rem_pay_period NUMBER --
991 -- p_flag NUMBER --
992 --------------------------------------------------------------------------
993 PROCEDURE build_form16_xml (p_action_context_id IN pay_assignment_actions.assignment_action_id%TYPE
994 ,p_source_id IN pay_payroll_actions.payroll_action_id%TYPE
995 ,p_rem_pay_period IN NUMBER
996 ,p_flag IN NUMBER
997 ,p_flag_for_16aa OUT NOCOPY NUMBER)
998 IS
999 --
1000 l_procedure VARCHAR2(100);
1001 j NUMBER;
1002 l_prev_tds NUMBER;
1003 l_tax_in_words varchar2(240);
1004 l_Non_Taxable_Amt NUMBER;
1005 sort_index NUMBER;
1006 l_flag_for_12ba number;
1007 l_tds_value NUMBER;
1008 l_open_tag VARCHAR2(100);
1009 l_tax_refundable NUMBER;
1010 l_marginal_relief NUMBER;
1011 l_prev_earnings NUMBER;
1012 l_qualifying_amt NUMBER;
1013 l_tot_80c_gross NUMBER;
1014 l_tot_80c_qual NUMBER;
1015 l_tot_80ccc_gross NUMBER;
1016 l_tot_80ccc_qual NUMBER;
1017
1018 l_total_via VARCHAR2(30);
1019
1020 l_flag_rep_gen NUMBER;
1021 emp_pos NUMBER;
1022 l_tax_deposited NUMBER;
1023 l_serial_number NUMBER;
1024
1025
1026 c_index NUMBER;
1027 l_emp_tds NUMBER;
1028 l_emp_sur NUMBER;
1029 l_emp_cess NUMBER;
1030 l_emp_amount NUMBER;
1031 l_entry_exists NUMBER;
1032
1033 l_via_seq_80c_num NUMBER;
1034 l_via_seq_80cce_num NUMBER;
1035 l_via_seq_80d_u_num NUMBER;
1036
1037 l_80c_tag_seq VARCHAR2(20);
1038 l_80cce_tag_seq VARCHAR2(20);
1039 l_80du_tag_seq VARCHAR2(20);
1040
1041 l_tag VARCHAR2(5);
1042 l_via_80c_flag NUMBER;
1043 l_via_cce_flag NUMBER;
1044 l_via_oth_flag NUMBER;
1045 l_seq CHAR(1) ;
1046 l_count NUMBER ;
1047 l_loss_from_house NUMBER ;
1048 l_other_income NUMBER ;
1049
1050 l_24qc_tax_deposited NUMBER;
1051 l_tds_total NUMBER;
1052 l_qtr1_tax_deduct_amt NUMBER;
1053 l_qtr2_tax_deduct_amt NUMBER;
1054 l_qtr3_tax_deduct_amt NUMBER;
1055 l_qtr4_tax_deduct_amt NUMBER;
1056 l_tot_24q_deposited NUMBER;
1057 l_tot_24qc_deposited NUMBER;
1058 l_assignment_id NUMBER;
1059 l_qtr_start date;
1060 l_qtr_end date;
1061 l_last_quarter VARCHAR2(10);
1062 l_year_start DATE ;
1063 l_year_end DATE ;
1064 l_asg_end_date DATE;
1065 LRPP NUMBER;
1066
1067 CURSOR csr_salary_components
1068 IS
1069 SELECT DECODE(pai.action_information1,'F16 Salary Under Section 17', 1,
1070 'F16 Value of Perquisites',2,
1071 'F16 Profit in lieu of Salary', 3,
1072 'F16 Gross Salary',4,
1073 'F16 Gross Salary less Allowances',6,
1074 'F16 Entertainment Allowance', 7,
1075 'F16 Employment Tax',8,
1076 'F16 Deductions under Sec 16',9,
1077 'F16 Income Chargeable Under head Salaries',10,
1078 'F16 Other Income',11,
1079 'F16 Gross Total Income',12,
1080 'F16 Total Income',13,
1081 'F16 Tax on Total Income',14,
1082 'F16 Surcharge',15,
1083 'F16 Education Cess',16,
1084 'F16 Relief under Sec 89',18,
1085 'F16 Total Tax payable',19,
1086 'Income Tax Deduction',20,
1087 'F16 Balance Tax',21,
1088 'ER Paid Tax on Non Monetary Perquisite',22,
1089 0) sort_index,
1090 action_information2 balance_value
1091 FROM pay_action_information pai
1092 WHERE action_information_category = 'IN_EOY_ASG_SAL'
1093 AND action_context_id = p_action_context_id
1094 AND source_id = p_source_id;
1095
1096 CURSOR csr_prev_employment_tds
1097 IS
1098 SELECT 1
1099 FROM pay_action_information pai
1100 WHERE action_information_category = 'IN_EOY_ASG_SAL'
1101 AND action_context_id = p_action_context_id
1102 AND source_id = p_source_id
1103 AND action_information1 IN('TDS on Previous Employment',
1104 'CESS on Previous Employment',
1105 'SC on Previous Employment');
1106
1107
1108 CURSOR csr_other_components(p_action_information1 pay_action_information.action_information1%TYPE)
1109 IS
1110 SELECT action_information2 balance_value
1114 AND source_id = p_source_id
1111 FROM pay_action_information pai
1112 WHERE action_information_category = 'IN_EOY_ASG_SAL'
1113 AND action_context_id = p_action_context_id
1115 AND action_information1 = p_action_information1;
1116
1117 CURSOR csr_other_income IS
1118 SELECT action_information1 balance_name,
1119 action_information2 balance_value
1120 FROM pay_action_information pai
1121 WHERE action_information_category = 'IN_EOY_ASG_SAL'
1122 AND action_context_id = p_action_context_id
1123 AND source_id = p_source_id
1124 AND action_information1 IN('Long Term Capital Gains',
1125 'Short Term Capital Gains',
1126 'Business and Profession Gains',
1127 'Other Sources of Income',
1128 'Loss From House Property')
1129 AND action_information2 IS NOT NULL;
1130
1131 CURSOR csr_allowances IS
1132 SELECT action_information1 Allowance_name,
1133 action_information2 Amt,
1134 action_information3 Std_Amt,
1135 action_information4 Taxable_Amt,
1136 action_information5 Std_Taxable_Amt
1137 FROM pay_action_information
1138 WHERE action_information_category = 'IN_EOY_ALLOW'
1139 AND action_context_id = p_action_context_id
1140 AND action_information1 <> 'Taxable Allowances'
1141 AND source_id =p_source_id;
1142
1143
1144 /* Get all Section 80C elements where the Gross Amount is greater than 0 */
1145
1146 CURSOR csr_deduction_via
1147 IS
1148 SELECT DECODE(action_information1, 'Life Insurance Premium','Life Insurance Premium',
1149 'Deferred Annuity','Deferred Annuity',
1150 'Senior Citizens Savings Scheme','Senior Citizens Savings Scheme',
1151 'Five Year Post Office Time Deposit Account','Five Year Post Office Time Deposit Account',
1152 'NABARD Bank Deposits','NABARD Bank Deposits',
1153 'Public Provident Fund','Public Provident Fund',
1154 'Interest on NSC','Interest on National Savings Certificate reinvested',
1155 'House Loan Repayment', 'Principal Loan (Housing Loan) Repayment',
1156 'Mutual Fund or UTI','Notified units of Mutual Funds/UTI',
1157 'National Housing Bank', 'National Housing Bank Scheme',
1158 'ULIP','Unit Linked Insurance Plan (UTI,LIC etc)',
1159 'Notified Annuity Plan','Notified Annuity Plan',
1160 'Notified Pension Fund','Notified Pension Fund',
1161 'Public Sector Scheme','Public Sector Company Scheme',
1162 'Infrastructure Bonds','Investment in Infrastructure Bonds',
1163 'Tuition fee','Tuition Fees per children (max 2 children allowed)',
1164 'Superannuation Fund', 'Employee Contribution to an approved superannuation fund',
1165 'F16 Employee PF Contribution','Employee Contribution to Provident Fund',
1166 'NSC','NSC',
1167 'Deposits in Govt. Security','Deposits in Govt. Security',
1168 'Notified Deposit Scheme','Notified Deposit Scheme',
1169 'Approved Shares or Debentures','Approved Shares or Debentures',
1170 'Approved Mutual Fund','Approved Mutual Fund',
1171 'Fixed Deposits','Fixed Deposits',
1172 'Stamp Duty for House Property','Stamp Duty for House Property',
1173 'Registration Fees for House Property','Registration Fees for House Property',
1174 'GSLI','GSLI',
1175 'General Provident Fund','General Provident Fund',
1176 'General Insurance Scheme','General Insurance Scheme',
1177 'X')Description_Value
1178 , action_information2 Qualifying_Value
1179 , nvl(action_information3,action_information2) Gross_Value
1180 FROM pay_action_information pai
1181 WHERE action_information_category = 'IN_EOY_VIA'
1182 AND action_context_id = p_action_context_id
1183 AND NVL(action_information3,action_information2) > 0
1184 AND source_id =p_source_id
1185 ORDER BY Description_Value;
1186
1187 /* Get all Chapter VIA elements excluding 80C elements where the Gross Amount is greater than 0 */
1188 CURSOR csr_deduction_via_d_to_u
1189 IS
1190 SELECT DECODE(action_information1, 'F16 Deductions Sec 80D','80D',
1191 'F16 Deductions Sec 80DD','80DD',
1192 'F16 Deductions Sec 80DDB','80DDB',
1193 'F16 Deductions Sec 80E','80E',
1194 'F16 Deductions Sec 80G','80G',
1195 'F16 Deductions Sec 80GG','80GG',
1196 'F16 Deductions Sec 80GGA','80GGA',
1197 'F16 Deductions Sec 80U','80U',
1198 'Pension Fund 80CCC','80CCC',
1199 'Govt Pension Scheme 80CCD','80CCD',
1200 'F16 Total Chapter VI A Deductions','TOTAL_V1A',
1201 'F16 Deductions Sec 80CCF','80CCF',
1202 'X')Description_Value
1203 , action_information2 Qualifying_Value
1204 , nvl(action_information3,action_information2) Gross_Value
1208 AND NVL(action_information3,action_information2) > 0
1205 FROM pay_action_information pai
1206 WHERE action_information_category = 'IN_EOY_VIA'
1207 AND action_context_id = p_action_context_id
1209 AND source_id =p_source_id
1210 ORDER BY Description_Value;
1211
1212 /* Get Maxium assignment action of the run for each pay period of the tax year*/
1213 CURSOR csr_max_run_assact_period IS
1214 SELECT MAX(action_information4) run_assact
1215 FROM pay_action_information pai
1216 WHERE pai.action_information_category ='IN_EOY_ASG_SAL'
1217 AND pai.action_information1='Income Tax This Pay'
1218 AND pai.action_context_id = p_action_context_id
1219 AND pai.source_id = p_source_id
1220 GROUP BY TRUNC(TO_DATE(Action_information3,'DD-MM_RRRR'),'MM')
1221 ORDER BY TRUNC(TO_DATE(Action_information3,'DD-MM_RRRR'),'MM');
1222
1223 -- Bug 4506944 : Changed the cursor as part of changes to be done to Form 16/16AA
1224 /*Get TDS paid in each pay period from the max assigment action id*/
1225 CURSOR csr_tds_paid(p_max_run_action_id NUMBER
1226 ,p_information VARCHAR2) IS
1227 SELECT action_information2 tds_value
1228 ,DECODE(TO_CHAR(TO_DATE(Action_information3,'DD-MM-RRRR'),'MM'),
1229 '04',1,
1230 '05',2,
1231 '06',3,
1232 '07',4,
1233 '08',5,
1234 '09',6,
1235 '10',7,
1236 '11',8,
1237 '12',9,
1238 '01',10,
1239 '02',11,
1240 '03',12)sort_index
1241 FROM pay_action_information
1242 WHERE action_context_id = p_action_context_id
1243 AND source_id =p_source_id
1244 AND action_information_category = 'IN_EOY_ASG_SAL'
1245 AND action_information1= p_information
1246 AND action_information4 = p_max_run_action_id;
1247
1248 -- Added with changes to Form 24q
1249 CURSOR emp_challan_details IS
1250 SELECT input.name name
1251 ,value.screen_entry_value value
1252 , entries.element_entry_id
1253 FROM per_assignments_f assign
1254 ,pay_element_entries_f entries
1255 ,pay_element_types_f type
1256 ,pay_input_values_f input
1257 ,pay_element_entry_values_f value
1258 ,pay_element_links_f links
1259 WHERE assign.assignment_id =
1260 (SELECT assignment_id
1261 FROM pay_assignment_actions
1262 WHERE assignment_action_id = p_action_context_id)
1263 AND links.element_type_id = type.element_type_id
1264 AND links.element_type_id = entries.element_type_id
1265 AND links.element_link_id = entries.element_link_id
1266 AND type.element_name = 'Income Tax Challan Information'
1267 AND type.element_type_id = entries.element_type_id
1268 AND entries.assignment_id = assign.assignment_id
1269 AND type.element_type_id = input.element_type_id
1270 AND value.element_entry_id = entries.element_entry_id
1271 AND value.input_value_id = input.input_value_id
1272 AND input.name in ('Amount Deposited'
1273 , 'Education Cess Deducted'
1274 , 'Income Tax Deducted'
1275 , 'Surcharge Deducted'
1276 , 'Challan or Voucher Number')
1277 AND type.legislation_code ='IN'
1278 AND entries.effective_start_date BETWEEN assign.effective_start_date AND assign.effective_end_date
1279 AND entries.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
1280 AND entries.effective_start_date BETWEEN type.effective_start_date AND type.effective_end_date
1281 AND entries.effective_start_date BETWEEN input.effective_start_date AND input.effective_end_date
1282 AND entries.effective_start_date BETWEEN links.effective_start_date AND links.effective_end_date
1283 AND value.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
1284 ORDER BY entries.element_entry_id
1285 , input.name;
1286
1287 CURSOR csr_tax_deposite_details (p_org_id number,p_action_id number,p_assignment_id number)
1288 IS
1289 SELECT nvl(sum(action_information9),0) Tax_Deposited
1290 FROM pay_action_information pai
1291 WHERE action_information_category ='IN_24Q_DEDUCTEE'
1292 AND action_context_type = 'AAP'
1293 AND action_information3 =p_org_id
1294 AND EXISTS (SELECT 1
1295 FROM pay_assignment_actions paa
1296 WHERE paa.payroll_action_id = p_action_id
1297 AND paa.assignment_action_id = pai.action_context_id
1298 AND paa.assignment_id = p_assignment_id);
1299
1300 CURSOR csr_24QC_tax_deposite_details (p_org_id number,p_action_id number,p_assignment_id number)
1301 IS
1302 SELECT sum(action_information16 - action_information17) Tax_Deposited
1303 FROM pay_action_information pai
1304 WHERE action_information_category ='IN_24QC_DEDUCTEE'
1305 AND action_context_type = 'AAP'
1306 AND action_information3 =p_org_id
1307 AND EXISTS (SELECT 1
1308 FROM pay_assignment_actions paa
1309 WHERE paa.payroll_action_id = p_action_id
1310 AND paa.assignment_action_id = pai.action_context_id
1311 AND paa.assignment_id = p_assignment_id);
1312
1313 cursor csr_assignment_id is
1314 SELECT pai.assignment_id
1315 FROM pay_action_information pai
1316 WHERE pai.action_information_category ='IN_EOY_PERSON'
1317 AND action_context_id = p_action_context_id
1318 AND source_id = p_source_id;
1319
1320 BEGIN
1321 g_debug := hr_utility.debug_enabled;
1322 l_procedure := g_package ||'build_form16_xml';
1323 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1324
1325
1326 IF g_debug THEN
1327 pay_in_utils.trace('**************************************************','********************');
1331 pay_in_utils.trace('p_flag ',to_char(p_flag));
1328 pay_in_utils.trace('p_action_context_id ',p_action_context_id );
1329 pay_in_utils.trace('p_source_id ',p_source_id );
1330 pay_in_utils.trace('p_rem_pay_period ',to_char(p_rem_pay_period));
1332 pay_in_utils.trace('**************************************************','********************');
1333 END IF;
1334
1335 l_flag_for_12ba :=0;
1336 l_flag_rep_gen := -1;
1337
1338
1339 g_salary_record(1).Value := 0;
1340 g_salary_record(2).Value := 0;
1341 g_salary_record(3).Value := 0;
1342 g_salary_record(4).Value := 0;
1343 g_salary_record(5).Value := 0;
1344 g_salary_record(6).Value := 0;
1345 g_salary_record(7).Value := 0;
1346 g_salary_record(8).Value := 0;
1347 g_salary_record(9).Value := 0;
1348 g_salary_record(10).Value := 0;
1349 g_salary_record(11).Value := 0;
1350 g_salary_record(12).Value := 0;
1351 g_salary_record(13).Value := 0;
1352 g_salary_record(14).Value := 0;
1353 g_salary_record(15).Value := 0;
1354 g_salary_record(16).Value := 0;
1355 g_salary_record(17).Value := 0;
1356 g_salary_record(18).Value := 0;
1357 g_salary_record(19).Value := 0;
1358 g_salary_record(20).Value := 0;
1359 g_salary_record(21).Value := 0;
1360 g_salary_record(22).Value := 0;
1361 l_tot_80ccc_qual := 0;
1362 l_tot_80c_qual := 0;
1363
1364 l_tot_80ccc_gross := 0;
1365 l_tot_80c_gross := 0;
1366
1367 l_qtr1_tax_deduct_amt := 0;
1368 l_qtr2_tax_deduct_amt := 0;
1369 l_qtr3_tax_deduct_amt := 0;
1370 l_qtr4_tax_deduct_amt := 0;
1371 l_tot_24q_deposited := 0;
1372 l_tot_24qc_deposited :=0;
1373 l_tax_deposited := 0;
1374 l_24qc_tax_deposited := 0;
1375 l_last_quarter := 'N';
1376
1377
1378 FOR crec_asg in csr_assignment_id loop
1379 l_assignment_id := crec_asg.assignment_id;
1380 END LOOP;
1381
1382 FOR i in 1..4 LOOP
1383 IF (i = 1)
1384 THEN
1385 l_qtr_start := to_date('01-04-'||to_char(to_number(substr(g_assessment_year, 1,4))-1),'dd-mm-yyyy');
1386 l_qtr_end := to_date('30-06-'||to_char(to_number(substr(g_assessment_year, 1,4))-1),'dd-mm-yyyy');
1387 l_qtr1_tax_deduct_amt := get_qtr_tax_deducted(l_assignment_id,g_org_id,l_qtr_start,l_qtr_end);
1388 write_tag('Q1_Tax_Deducted',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_qtr1_tax_deduct_amt));
1389 ELSIF (i = 2)
1390 THEN
1391 l_qtr_start := to_date('01-07-'||to_char(to_number(substr(g_assessment_year, 1,4))-1),'dd-mm-yyyy');
1392 l_qtr_end := to_date('30-09-'||to_char(to_number(substr(g_assessment_year, 1,4))-1),'dd-mm-yyyy');
1393 l_qtr2_tax_deduct_amt := get_qtr_tax_deducted(l_assignment_id,g_org_id,l_qtr_start,l_qtr_end);
1394 IF l_qtr2_tax_deduct_amt > 0 THEN
1395 l_qtr2_tax_deduct_amt := l_qtr2_tax_deduct_amt - l_qtr1_tax_deduct_amt;
1396 END IF;
1397 write_tag('Q2_Tax_Deducted',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_qtr2_tax_deduct_amt));
1398 ELSIF (i = 3)
1399 THEN
1400 l_qtr_start := to_date('01-10-'||to_char(to_number(substr(g_assessment_year, 1,4))-1),'dd-mm-yyyy');
1401 l_qtr_end := to_date('31-12-'||to_char(to_number(substr(g_assessment_year, 1,4))-1),'dd-mm-yyyy');
1402 l_qtr3_tax_deduct_amt := get_qtr_tax_deducted(l_assignment_id,g_org_id,l_qtr_start,l_qtr_end);
1403 IF l_qtr3_tax_deduct_amt > 0 THEN
1404 l_qtr3_tax_deduct_amt := l_qtr3_tax_deduct_amt - (l_qtr1_tax_deduct_amt + l_qtr2_tax_deduct_amt);
1405 END IF;
1406 write_tag('Q3_Tax_Deducted',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_qtr3_tax_deduct_amt));
1407 ELSE
1408 l_qtr_start := to_date('01-01-'||to_char(to_number(substr(g_assessment_year, 6,4))-1),'dd-mm-yyyy');
1409 l_qtr_end := to_date('31-03-'||to_char(to_number(substr(g_assessment_year, 6,4))-1),'dd-mm-yyyy');
1410 l_qtr4_tax_deduct_amt := get_qtr_tax_deducted(l_assignment_id,g_org_id,l_qtr_start,l_qtr_end);
1411 IF l_qtr4_tax_deduct_amt > 0 THEN
1412 l_qtr4_tax_deduct_amt := l_qtr4_tax_deduct_amt - (l_qtr1_tax_deduct_amt + l_qtr2_tax_deduct_amt + l_qtr3_tax_deduct_amt);
1413 END IF;
1414 write_tag('Q4_Tax_Deducted',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_qtr4_tax_deduct_amt));
1415 END IF;
1416 END LOOP;
1417
1418 FOR k in 1..qtr_index
1419 LOOP
1420 IF g_qtr_action_id_details(k).Nature =' Regular'
1421 THEN
1422 FOR tax_deposit in csr_tax_deposite_details(g_qtr_action_id_details(k).OrgID,g_qtr_action_id_details(k).ActionID,l_assignment_id)
1423 LOOP
1424 l_tax_deposited := tax_deposit.Tax_Deposited;
1425 END LOOP;
1426
1427 IF (g_qtr_action_id_details(k).Quarter ='Q1')
1428 THEN
1429 write_tag('QR1',g_qtr_action_id_details(k).Nature);
1430 write_tag('Q1_RCPT',g_qtr_action_id_details(k).Receipt);
1431 write_tag('Q1_Tax_Deposited',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tax_deposited));
1432 l_tot_24q_deposited := l_tot_24q_deposited + l_tax_deposited;
1433 ELSIF (g_qtr_action_id_details(k).Quarter ='Q2')
1434 THEN
1435 write_tag('QR2',g_qtr_action_id_details(k).Nature);
1436 write_tag('Q2_RCPT',g_qtr_action_id_details(k).Receipt);
1437 write_tag('Q2_Tax_Deposited',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tax_deposited));
1438 l_tot_24q_deposited := l_tot_24q_deposited + l_tax_deposited;
1439 ELSIF (g_qtr_action_id_details(k).Quarter ='Q3')
1440 THEN
1444 l_tot_24q_deposited := l_tot_24q_deposited + l_tax_deposited;
1441 write_tag('QR3',g_qtr_action_id_details(k).Nature);
1442 write_tag('Q3_RCPT',g_qtr_action_id_details(k).Receipt);
1443 write_tag('Q3_Tax_Deposited',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tax_deposited));
1445 ELSE
1446 write_tag('QR4',g_qtr_action_id_details(k).Nature);
1447 write_tag('Q4_RCPT',g_qtr_action_id_details(k).Receipt);
1448 write_tag('Q4_Tax_Deposited',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tax_deposited));
1449 l_tot_24q_deposited := l_tot_24q_deposited + l_tax_deposited;
1450 l_last_quarter := 'Y';
1451 END IF;
1452 ELSE
1453 FOR crec_24qc_tax_deposit in csr_24QC_tax_deposite_details(g_qtr_action_id_details(k).OrgID,g_qtr_action_id_details(k).ActionID,l_assignment_id)
1454 LOOP
1455 l_24qc_tax_deposited := crec_24qc_tax_deposit.Tax_Deposited;
1456 END LOOP;
1457
1458 IF (g_qtr_action_id_details(k).Quarter ='Q1' AND l_24qc_tax_deposited IS NOT NULL)
1459 THEN
1460 write_tag('QC1',g_qtr_action_id_details(k).Nature);
1461 write_tag('QC1_RCPT',g_qtr_action_id_details(k).Receipt);
1462 write_tag('QC1_Tax_Deposited',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_24qc_tax_deposited));
1463 l_tot_24qc_deposited:=l_tot_24qc_deposited+l_24qc_tax_deposited;
1464 ELSIF (g_qtr_action_id_details(k).Quarter ='Q2' AND l_24qc_tax_deposited IS NOT NULL)
1465 THEN
1466 write_tag('QC2',g_qtr_action_id_details(k).Nature);
1467 write_tag('QC2_RCPT',g_qtr_action_id_details(k).Receipt);
1468 write_tag('QC2_Tax_Deposited',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_24qc_tax_deposited));
1469 l_tot_24qc_deposited := l_tot_24qc_deposited + l_24qc_tax_deposited;
1470 ELSIF (g_qtr_action_id_details(k).Quarter ='Q3' AND l_24qc_tax_deposited IS NOT NULL)
1471 THEN
1472 write_tag('QC3',g_qtr_action_id_details(k).Nature);
1473 write_tag('QC3_RCPT',g_qtr_action_id_details(k).Receipt);
1474 write_tag('QC3_Tax_Deposited',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_24qc_tax_deposited));
1475 l_tot_24qc_deposited:=l_tot_24qc_deposited+l_24qc_tax_deposited;
1476 ELSE
1477 IF l_24qc_tax_deposited IS NOT NULL THEN
1478 write_tag('QC4',g_qtr_action_id_details(k).Nature);
1479 write_tag('QC4_RCPT',g_qtr_action_id_details(k).Receipt);
1480 write_tag('QC4_Tax_Deposited',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_24qc_tax_deposited));
1481 l_tot_24qc_deposited := l_tot_24qc_deposited + l_24qc_tax_deposited;
1482 END IF;
1483 END IF;
1484 END IF;
1485 END LOOP;
1486 IF (l_last_quarter = 'N' AND SYSDATE >= g_tax_end_date )
1487 THEN
1488 write_tag('QR4','Not Available as the last Quarterly Statement is yet to be furnished');
1489 write_tag('Q4_Tax_Deposited',' Yet to be deposited');
1490 END IF;
1491 write_tag('Total_Tax_Deducted',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_qtr1_tax_deduct_amt+l_qtr2_tax_deduct_amt+l_qtr3_tax_deduct_amt+l_qtr4_tax_deduct_amt));
1492 write_tag('Total_Tax_Deposited',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_24q_deposited+l_tot_24qc_deposited));
1493
1494 FOR i IN csr_salary_components
1495 LOOP
1496 IF i.sort_index <> '0' THEN
1497 IF(l_flag_rep_gen = -1 )THEN
1498 l_flag_rep_gen := 1;
1499 END IF ;
1500 g_salary_record(i.sort_index).Value := i.balance_value;
1501 END IF;
1502 END LOOP;
1503
1504 OPEN csr_other_components('F16 Tax Refundable');
1505 FETCH csr_other_components INTO l_tax_refundable;
1506 IF csr_other_components%NOTFOUND THEN
1507 l_tax_refundable := 0;
1508 END IF;
1509 CLOSE csr_other_components;
1510
1511 OPEN csr_other_components('F16 Marginal Relief');
1512 FETCH csr_other_components INTO l_marginal_relief;
1513 IF csr_other_components%NOTFOUND THEN
1514 l_marginal_relief := 0;
1515 END IF;
1516 CLOSE csr_other_components;
1517
1518 --
1519 -- Bug 4557407 removed surcharge to Tax on Income
1520 --
1521 -- Total Nontaxable Allowance
1522 -- Tax Paybale and Refundable
1523
1524 g_salary_record(5).Value := nvl(g_salary_record(4).Value,0) - nvl(g_salary_record(6).Value,0);
1525
1526 IF (g_salary_record(21).Value = 0) THEN
1527 g_salary_record(21).Value := -l_tax_refundable;
1528 END IF;
1529
1530 FOR i in 1..22 LOOP
1531 write_tag(g_salary_record(i).Name,pay_us_employee_payslip_web.get_format_value(g_business_group_id,g_salary_record(i).Value));
1532 END LOOP;
1533 -- write_tag('DUMMY',pay_us_employee_payslip_web.get_format_value(g_business_group_id,g_salary_record(22).Value));
1534
1535 -- g_873 := g_868 + g_872;
1536 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))));
1537
1538 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)));
1539
1540 OPEN csr_other_components('Previous Employment Earnings');
1541 FETCH csr_other_components INTO l_prev_earnings;
1542 IF csr_other_components%NOTFOUND THEN
1543 l_prev_earnings := 0;
1544 END IF;
1545 CLOSE csr_other_components;
1546
1547
1548 OPEN csr_prev_employment_tds;
1552 END IF;
1549 FETCH csr_prev_employment_tds INTO l_prev_tds;
1550 IF csr_prev_employment_tds%NOTFOUND THEN
1551 l_prev_tds := 0;
1553 CLOSE csr_prev_employment_tds;
1554
1555 IF (g_salary_record(21).Value <> 0 AND l_flag_rep_gen = -1) THEN
1556 l_flag_rep_gen := 1;
1557 END IF;
1558
1559 /* Chapter VIA Start */
1560 l_total_via :=0;
1561
1562 l_via_80c_flag := 0;
1563 l_via_cce_flag := 0;
1564 l_via_oth_flag := 0;
1565
1566 l_via_seq_80c_num := 1;
1567 l_via_seq_80cce_num := 97;
1568 l_via_seq_80d_u_num := 97;
1569
1570 l_80cce_tag_seq := '('||fnd_global.local_chr(l_via_seq_80cce_num)||')';
1571
1572
1573 FOR i IN csr_deduction_via LOOP
1574 IF (l_via_80c_flag = 0 and i.Description_Value <> 'X') THEN
1575 l_via_80c_flag := 1;
1576 l_via_cce_flag := 1;
1577 write_tag('SEC80C',l_via_80c_flag);
1578 write_tag('AS',l_80cce_tag_seq);
1579 l_via_seq_80cce_num := l_via_seq_80cce_num + 1;
1580
1581 IF(l_flag_rep_gen = -1) THEN
1582 l_flag_rep_gen := 1;
1583 END IF;
1584
1585 END IF;
1586
1587 IF(i.Description_Value <> 'X')THEN
1588 l_80c_tag_seq := ltrim(lower(to_char(l_via_seq_80c_num,'RM')),' ' )||')';
1589
1590 l_open_tag := '<CGRP>';
1591 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1592
1593 write_tag('RN',l_80c_tag_seq);
1594 write_tag('NAME',i.Description_Value);
1595 write_tag('GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,nvl(i.Gross_Value,0)));
1596 l_via_seq_80c_num := l_via_seq_80c_num + 1;
1597 l_tot_80c_gross := l_tot_80c_gross + nvl(i.Gross_Value,0);
1598 l_tot_80c_qual := l_tot_80c_qual + nvl(i.Qualifying_Value,0);
1599 l_open_tag := '</CGRP>';
1600 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1601 END IF;
1602 END LOOP;
1603
1604 l_tot_80c_qual := LEAST(l_tot_80c_qual,g_80cce_limit);
1605
1606 write_tag('TOT_80C_GR',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80c_gross));
1607 write_tag('TOT_80C_QA',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80c_qual));
1608
1609 FOR i in csr_deduction_via_d_to_u LOOP
1610 IF(l_flag_rep_gen = -1) THEN
1611 l_flag_rep_gen := 1;
1612 END IF;
1613
1614 IF(i.Description_Value = 'TOTAL_V1A') THEN
1615 l_total_via := nvl(i.Qualifying_Value,0);
1616 ELSIF(i.Description_Value <> 'X')THEN
1617 IF i.Description_Value IN('80D',
1618 '80DD',
1619 '80DDB',
1620 '80E',
1621 '80G',
1622 '80GG',
1623 '80GGA',
1624 '80U',
1625 '80CCF') THEN
1626 l_via_oth_flag := 1;
1627 l_80du_tag_seq := '('||fnd_global.local_chr(l_via_seq_80d_u_num)||')';
1628 l_open_tag := '<OTHER_VIA>';
1629 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1630
1631 l_qualifying_amt := nvl(i.Qualifying_Value,0);
1632 write_tag('SN',l_80du_tag_seq);
1633 write_tag('NAME',i.Description_Value);
1634 write_tag('GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,nvl(i.Gross_Value,0)));
1635 write_tag('QUAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_qualifying_amt));
1636
1637 l_open_tag := '</OTHER_VIA>';
1638 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1639 l_via_seq_80d_u_num := l_via_seq_80d_u_num +1;
1640 ELSIF i.Description_Value IN('80CCC','80CCD') THEN
1641 l_via_cce_flag := 1;
1642 l_80cce_tag_seq := '('||fnd_global.local_chr(l_via_seq_80cce_num)||')';
1643 l_qualifying_amt := LEAST(g_80cce_limit,nvl(i.Qualifying_Value,0));
1644 l_tag := substr(i.Description_Value,3);
1645 l_open_tag := '<'||l_tag||'GRP>';
1646 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1647
1648 write_tag('AS',l_80cce_tag_seq);
1649
1650 write_tag('GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,nvl(i.Gross_Value,0)));
1651 write_tag('QUAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_qualifying_amt));
1652
1653 l_via_seq_80cce_num := l_via_seq_80cce_num + 1;
1654
1655 l_open_tag := '</'||l_tag||'GRP>';
1656 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1657 l_tot_80ccc_gross := l_tot_80ccc_gross + nvl(i.Gross_Value,0);
1658 l_tot_80ccc_qual := l_tot_80ccc_qual + l_qualifying_amt;
1659 ELSE
1660 NULL;
1661 END IF;
1662 END IF;
1663 END LOOP;
1664 l_tot_80ccc_gross := l_tot_80ccc_gross + l_tot_80c_gross;
1665 l_tot_80ccc_qual := l_tot_80ccc_qual + l_tot_80c_qual;
1666
1667 IF (l_via_cce_flag <> 1 ) THEN
1668 write_tag('VIACCE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,0));
1669 END IF;
1670 write_tag('SEC80CCE',l_via_cce_flag);
1671 write_tag('TOT_80CCC_GR',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80ccc_gross));
1672 write_tag('TOT_80CCC_QA',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80ccc_qual));
1673
1674 IF (l_via_oth_flag <> 1)THEN
1675 write_tag('VIAOTH',pay_us_employee_payslip_web.get_format_value(g_business_group_id,0));
1676 END IF;
1677
1681
1678 write_tag('TOTAL_V1A',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_total_via));
1679 /* Chapter VIA End */
1680
1682 /* Check for Form Generation and Other Income Start*/
1683 t_table_1(1) :=0;
1684 t_table_1(2) :=0;
1685 t_table_1(3) :=0;
1686 t_table_1(4) :=0;
1687 t_table_1(5) :=0;
1688
1689 FOR i IN csr_other_income LOOP
1690 IF(l_flag_rep_gen = -1) THEN
1691 l_flag_rep_gen := 1;
1692 END IF;
1693
1694 IF (i.balance_name ='Business and Profession Gains') THEN
1695 t_table_1(1) := i.balance_value;
1696 ELSIF (i.balance_name ='Long Term Capital Gains' ) THEN
1697 t_table_1(2) := i.balance_value;
1698 ELSIF (i.balance_name ='Short Term Capital Gains' ) THEN
1699 t_table_1(3) := i.balance_value;
1700 ELSIF (i.balance_name ='Other Sources of Income' ) THEN
1701 t_table_1(4) := i.balance_value;
1702 ELSIF (i.balance_name ='Loss From House Property') THEN
1703 t_table_1(5) := i.balance_value;
1704 END IF;
1705 END LOOP;
1706
1707 IF l_flag_rep_gen = -1 THEN
1708 /* Do not generate any report for this employee */
1709 p_flag_for_16aa := -1;
1710 emp_pos := INSTR(g_tmp_clob,'<EMPLOYEE>',-1);
1711 dbms_lob.TRIM(g_tmp_clob,emp_pos-1);
1712 ELSE
1713 /* Generate either 16 /16AA for this employee */
1714 p_flag_for_16aa := 1;
1715
1716 IF ((g_salary_record(10).Value = 0)
1717 OR ((g_salary_record(6).Value + l_prev_earnings)>150000)
1718 OR (l_prev_earnings <>0 AND l_prev_tds > 0)
1719 OR (t_table_1(1) <>0 OR t_table_1(2) <>0)
1720 OR p_flag = 0 )
1721 THEN
1722 p_flag_for_16aa :=0; -- Not eligible for 16AA
1723 END IF;
1724
1725
1726 FOR i IN 1..5 LOOP
1727 IF(t_table_1(i) <>0) THEN
1728 l_open_tag := '<Other_Income>';
1729 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1730
1731 IF (i=1 )THEN
1732 write_tag('NAME','Business and Profession Gains');
1733 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,t_table_1(1)));
1734 ELSIF (i=2 ) THEN
1735 write_tag('NAME','Long Term Capital Gains');
1736 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,t_table_1(2)));
1737 ELSIF (i=3) THEN
1738 write_tag('NAME','Short Term Capital Gains');
1739 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,t_table_1(3)));
1740 ELSIF (i=4) THEN
1741 write_tag('NAME','Other Sources of Income');
1742 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,t_table_1(4)));
1743 ELSIF (i=5) THEN
1744 write_tag('NAME','Loss From House Property');
1745 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,0-t_table_1(5)));
1746 END IF;
1747
1748
1749 l_open_tag := '</Other_Income>';
1750 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1751 END IF;
1752 END LOOP;
1753
1754 IF p_flag_for_16aa = 1 THEN
1755
1756 l_open_tag := '<SEC_OTHERS>';
1757 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1758 write_tag('SECTION','(h) 80C');
1759 write_tag('SEC_GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80c_gross));
1760 write_tag('SEC_QUAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tot_80c_qual));
1761 l_open_tag := '</SEC_OTHERS>';
1762 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1763
1764 l_count :=0;
1765
1766 FOR rec_deduction_via_d_to_u IN csr_deduction_via_d_to_u
1767 LOOP
1768 IF (rec_deduction_via_d_to_u.Description_Value NOT IN ('TOTAL_V1A','X')) THEN
1769 IF rec_deduction_via_d_to_u.Description_Value = '80CCC' THEN
1770 write_tag('SEC80CCC',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1771 write_tag('SEC80CCC_QAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1772 ELSIF rec_deduction_via_d_to_u.Description_Value = '80D' THEN
1773 write_tag('SEC80D',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1774 write_tag('SEC80D_QAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1775 ELSIF rec_deduction_via_d_to_u.Description_Value = '80E' THEN
1776 write_tag('SEC80E',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1777 write_tag('SEC80E_QAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1778 ELSIF rec_deduction_via_d_to_u.Description_Value = '80G' THEN
1779 write_tag('SEC80G',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1780 write_tag('SEC80G_QAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1781 ELSE
1782
1783
1784 l_count := l_count + 1;
1785
1789 FROM dual
1786 select lower(fnd_global.local_chr(r+64)) INTO l_seq
1787 FROM
1788 ( SELECT LEVEL r
1790 CONNECT BY LEVEL <= 26 )
1791 WHERE r+64 = 72 + l_count;
1792
1793 l_open_tag := '<SEC_OTHERS>';
1794 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1795 write_tag('SECTION','('||l_seq||') '||rec_deduction_via_d_to_u.Description_Value);
1796 write_tag('SEC_GROSS',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Gross_Value));
1797 write_tag('SEC_QUAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,rec_deduction_via_d_to_u.Qualifying_Value));
1798 l_open_tag := '</SEC_OTHERS>';
1799 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1800 END IF ;
1801 END IF ;
1802 END LOOP ;
1803
1804
1805
1806
1807 FOR i IN 1..5 LOOP
1808 IF(t_table_1(i) <>0) THEN
1809
1810 IF (i=5) THEN
1811 l_loss_from_house := t_table_1(i) ;
1812 ELSIF (i=4) THEN
1813 l_other_income := t_table_1(i) ;
1814 END IF;
1815
1816 END IF;
1817 END LOOP;
1818
1819 write_tag('OI_HOUSE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_loss_from_house));
1820 write_tag('OI_OTHER',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_other_income));
1821
1822 END IF ;
1823
1824 t_table_1.DELETE;
1825 /* Check for 16aa and Other Income End*/
1826
1827 IF ((g_salary_record(4).Value + l_prev_earnings)>150000 OR
1828 g_salary_record(2).Value > 0 OR
1829 g_salary_record(3).Value > 0
1830 ) THEN
1831 l_flag_for_12ba :=1; --Eligible for 12BA
1832 END IF;
1833
1834 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));
1835 write_tag('C_16AA_FLAG',p_flag_for_16aa );
1836 write_tag('C_12BA_FLAG',l_flag_for_12ba );
1837
1838 l_tax_in_words := pay_in_utils.number_to_words(g_salary_record(20).Value);
1839 write_tag('TOTAL',l_tax_in_words );
1840
1841 SELECT nvl(pps.actual_termination_date,paa.effective_end_Date)
1842 INTO l_asg_end_date
1843 FROM per_Assignments_f paa,-- Modified this for 4774108 to remove NMV
1844 per_periods_of_Service pps
1845 WHERE paa.assignment_id = l_assignment_id
1846 AND paa.period_of_service_id =pps.period_of_service_id
1847 AND paa.effective_end_date = ( SELECT MAX (b.effective_end_date)
1848 FROM per_all_assignments_f b
1849 WHERE paa.assignment_id=b.assignment_id );
1850
1851 l_year_start := pay_in_tax_utils.get_financial_year_start(l_date_earned );
1852 l_year_end := pay_in_tax_utils.get_financial_year_end(l_date_earned );
1853
1854 LRPP := p_rem_pay_period;
1855
1856 IF (l_asg_end_date < l_year_end AND l_asg_end_date < l_year_start)THEN
1857 LRPP := 0;
1858 END IF;
1859
1860 /* Allowances Start*/
1861 FOR i in csr_allowances LOOP
1862 --
1863 IF (i.Allowance_Name='House Rent Allowance') THEN
1864 l_Non_Taxable_Amt := nvl(i.Amt,0) + nvl(i.Std_AMt,0)* LRPP - nvl(i.Std_Taxable_Amt,0);
1865 ELSE
1866 l_Non_Taxable_Amt := nvl(i.Amt,0) - nvl(i.Taxable_Amt,0) + (nvl(i.Std_AMt,0) - nvl(i.Std_Taxable_Amt,0)) * LRPP ;
1867 END IF;
1868 --
1869 IF( l_Non_Taxable_Amt > 0) THEN
1870 l_open_tag := '<Allowance>';
1871 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1872
1873 write_tag('NAME',i.Allowance_Name);
1874 write_tag('VALUE',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_Non_Taxable_Amt));
1875
1876 l_open_tag := '</Allowance>';
1877 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1878
1879 END IF;
1880 --
1881 END LOOP;
1882 /* Allowances End*/
1883
1884 /* Challan Start */
1885
1886 /*As per FY 2009, the format for Form 16AA is similar to Form16.
1887 So the code related to Form16AA is deleted*/
1888 g_emp_challan_details_tbl.DELETE;
1889 c_index := 1;
1890
1891 FOR emp_challan IN emp_challan_details
1892 LOOP
1893 IF emp_challan.name = 'Amount Deposited' THEN
1894 g_emp_challan_details_tbl(c_index).emp_amount := nvl(emp_challan.value, 0);
1895 END IF;
1896
1897 IF emp_challan.name = 'Challan or Voucher Number' THEN
1898 g_emp_challan_details_tbl(c_index).emp_voucher := emp_challan.value;
1899 END IF;
1900
1901 IF emp_challan.name = 'Education Cess Deducted' THEN
1902 g_emp_challan_details_tbl(c_index).emp_cess := nvl(emp_challan.value, 0);
1903 END IF;
1904
1905 IF emp_challan.name = 'Income Tax Deducted' THEN
1906 g_emp_challan_details_tbl(c_index).emp_tds := nvl(emp_challan.value, 0);
1907 END IF;
1908
1909 IF emp_challan.name = 'Surcharge Deducted' THEN
1910 g_emp_challan_details_tbl(c_index).emp_sur := nvl(emp_challan.value, 0);
1911 c_index := c_index + 1;
1912 END IF;
1913 END LOOP;
1914
1915 /*Merge Bank Details at Org Level with TDS Details at Person level*/
1916 l_serial_number := 1;
1920 l_emp_tds := 0;
1917 l_tds_total := 0;
1918 FOR I IN 1..g_index LOOP
1919
1921 l_emp_sur := 0;
1922 l_emp_cess := 0;
1923 l_emp_amount := 0;
1924
1925 IF g_Bank_Details_tbl.exists(i) THEN
1926
1927 l_entry_exists := 0;
1928
1929 FOR j IN 1..(c_index-1) LOOP
1930 IF (g_Bank_Details_tbl(i).VNumber =
1931 NVL( substr(g_emp_challan_details_tbl(j).emp_voucher, instr(g_emp_challan_details_tbl(j).emp_voucher,' - ',1)+3, (instr(g_emp_challan_details_tbl(j).emp_voucher,' - ',-1) - instr(g_emp_challan_details_tbl(j).emp_voucher,' - ',1))-3) , '-1')
1932 and upper(to_char(g_Bank_Details_tbl(i).VDate,'DD-Mon-RRRR')) = upper(substr(g_emp_challan_details_tbl(j).emp_voucher, instr(g_emp_challan_details_tbl(j).emp_voucher,' - ',-1)+3))
1933 and g_Bank_Details_tbl(i).Bank = substr(g_emp_challan_details_tbl(j).emp_voucher,1,instr(g_emp_challan_details_tbl(j).emp_voucher,' - ',1)-1)
1934 )THEN
1935 l_emp_tds := l_emp_tds + g_emp_challan_details_tbl(j).emp_tds;
1936 l_emp_sur := l_emp_sur + g_emp_challan_details_tbl(j).emp_sur;
1937 l_emp_cess := l_emp_cess + g_emp_challan_details_tbl(j).emp_cess;
1938 l_emp_amount := l_emp_amount + g_emp_challan_details_tbl(j).emp_amount;
1939 l_entry_exists := 1;
1940 END IF;
1941 END LOOP;
1942
1943 IF l_entry_exists = 1 THEN
1944 l_open_tag := '<t_month>';
1945 dbms_lob.writeAppend(g_tmp_clob,LENGTH(l_open_tag),l_open_tag);
1946 write_tag('SNO',l_serial_number);
1947 l_serial_number := l_serial_number + 1;
1948 IF (g_tax_start_date >= TO_DATE('01/04/2009','DD/MM/YYYY')) THEN
1949 write_tag('TDS_PERIOD',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_tds+l_emp_sur+l_emp_cess));
1950 write_tag('SURCHARGE_PERIOD',pay_us_employee_payslip_web.get_format_value(g_business_group_id,0));
1951 ELSE
1952 write_tag('TDS_PERIOD',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_tds));
1953 write_tag('SURCHARGE_PERIOD',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_sur));
1954 END IF ;
1955 write_tag('ECESS_PERIOD',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_cess));
1956 write_tag('TAX_DEPOSITED',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_emp_amount));
1957 write_tag('PYMT_DATE',to_char(g_Bank_Details_tbl(i).VDate,'DD/MM/RRRR'));
1958 write_tag('BANK_BRANCH',g_Bank_Details_tbl(i).Bank);
1959 write_tag('VOUCHER_NUM',g_Bank_Details_tbl(i).VNumber);
1960 write_tag('CHEQUE_DD_NUMBER',g_Bank_Details_tbl(i).DDCheque_Num);
1961 l_open_tag := '</t_month>';
1962 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1963 END IF;
1964 l_tds_total := l_tds_total + l_emp_tds + l_emp_cess;
1965 END IF;
1966
1967 END LOOP;
1968 write_tag('TDS_TOTAL',pay_us_employee_payslip_web.get_format_value(g_business_group_id,l_tds_total));
1969
1970 END IF;
1971 /* Challan End */
1972 IF g_debug THEN
1973 pay_in_utils.trace('p_flag_for_16aa ', p_flag_for_16aa );
1974 END IF;
1975 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1976 END build_form16_xml;
1977
1978 --------------------------------------------------------------------------
1979 -- --
1980 -- Name : BUILD_FORM12BA_XML --
1981 -- Type : PROCEDURE --
1982 -- Access : Public --
1983 -- Description : This procedure builds the XML for Form 12BA --
1984 -- --
1985 -- Parameters : --
1986 -- IN : p_action_context_id NUMBER --
1987 --------------------------------------------------------------------------
1988 PROCEDURE build_form12ba_xml(p_action_context_id IN pay_assignment_actions.assignment_action_id%TYPE
1989 ,p_source_id IN pay_payroll_actions.payroll_action_id%TYPE)
1990 IS
1991 l_total_taxable_perq NUMBER;
1992 l_total_emp_contr NUMBER;
1993
1994 CURSOR csr_get_perq_values
1995 IS
1996 SELECT DECODE(action_information1, 'Company Accommodation', 1
1997 , 'Motor Car Perquisite',2
1998 , 'Domestic Servant',3
1999 , 'Gas / Water / Electricity', 4
2000 , 'Loan at Concessional Rate',5
2001 , 'Travel / Tour / Accommodation',7
2002 , 'Leave Travel Concession',7
2003 , 'Lunch Perquisite',8
2004 , 'Free Education', 9
2005 , 'Gift Voucher', 10
2006 , 'Credit Cards', 11
2007 , 'Club Expenditure', 12
2008 , 'Company Movable Assets',13
2009 , 'Transfer of Company Assets',14
2010 , 'Employer Paid Tax',15
2011 , 'Shares',16
2012 , 20) sort_index
2013 , SUM(NVL(action_information2,0)) value1
2014 , SUM(NVL(action_information3,0)) value2
2018 AND source_id =p_source_id
2015 FROM pay_action_information
2016 WHERE action_information_category = 'IN_EOY_PERQ'
2017 AND action_context_id = p_action_context_id
2019 GROUP BY DECODE(action_information1, 'Company Accommodation', 1
2020 , 'Motor Car Perquisite',2
2021 , 'Domestic Servant',3
2022 , 'Gas / Water / Electricity', 4
2023 , 'Loan at Concessional Rate',5
2024 , 'Travel / Tour / Accommodation',7
2025 , 'Leave Travel Concession',7
2026 , 'Lunch Perquisite',8
2027 , 'Free Education', 9
2028 , 'Gift Voucher', 10
2029 , 'Credit Cards', 11
2030 , 'Club Expenditure', 12
2031 , 'Company Movable Assets',13
2032 , 'Transfer of Company Assets',14
2033 , 'Employer Paid Tax',15
2034 , 'Shares',16
2035 , 20) ;
2036
2037 CURSOR csr_get_total_perq(p_action_information1 pay_action_information.action_information1%TYPE)
2038 IS
2039 SELECT NVL(action_information2,0) value1
2040 FROM pay_action_information
2041 WHERE action_information_category = 'IN_EOY_PERQ'
2042 AND action_context_id = p_action_context_id
2043 AND source_id =p_source_id
2044 AND action_information1 = p_action_information1;
2045
2046
2047 l_procedure varchar2(100);
2048
2049 BEGIN
2050 g_debug := hr_utility.debug_enabled;
2051 l_procedure := g_package ||'build_form12ba_xml';
2052 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2053
2054
2055 IF g_debug THEN
2056 pay_in_utils.trace('**************************************************','********************');
2057 pay_in_utils.trace('p_action_context_id ', p_action_context_id );
2058 pay_in_utils.trace('p_source_id ', p_source_id );
2059 pay_in_utils.trace('**************************************************','********************');
2060 END IF;
2061
2062 FOR j in csr_get_perq_values
2063 LOOP
2064 g_perq_record(j.sort_index).perq_value1 := j.value1;
2065 g_perq_record(j.sort_index).perq_value2 := j.value2;
2066 END LOOP;
2067
2068 FOR i in 1..16
2069 LOOP
2070 g_perq_record(17).perq_value1 := g_perq_record(17).perq_value1 + g_perq_record(i).perq_value1;
2071 g_perq_record(17).perq_value2 := g_perq_record(17).perq_value2 + g_perq_record(i).perq_value2;
2072 END LOOP;
2073
2074
2075
2076 OPEN csr_get_total_perq('Taxable Perquisites');
2077 FETCH csr_get_total_perq INTO l_total_taxable_perq;
2078 IF csr_get_total_perq%NOTFOUND THEN
2079 l_total_taxable_perq := 0;
2080 END IF;
2081 CLOSE csr_get_total_perq;
2082
2083 OPEN csr_get_total_perq('Perquisite Employee Contribution');
2084 FETCH csr_get_total_perq INTO l_total_emp_contr;
2085 IF csr_get_total_perq%NOTFOUND THEN
2086 l_total_emp_contr := 0;
2087 END IF;
2088 CLOSE csr_get_total_perq;
2089
2090 g_perq_record(18).perq_value1 := l_total_taxable_perq;
2091 g_perq_record(18).perq_value2 := l_total_emp_contr;
2092
2093 g_perq_record(17).perq_value1 := g_perq_record(18).perq_value1 - g_perq_record(17).perq_value1;
2094 g_perq_record(17).perq_value2 := g_perq_record(18).perq_value2 - g_perq_record(17).perq_value2;
2095
2096 FOR i in 1..18
2097 LOOP
2098
2099 write_tag( p_tag_name => 'P' || i || '_V3'
2100 , p_tag_value => pay_us_employee_payslip_web.get_format_value
2101 ( g_business_group_id
2102 , g_perq_record(i).perq_value1
2103 )
2104 );
2105 write_tag( p_tag_name => 'P' || i || '_V2'
2106 , p_tag_value => pay_us_employee_payslip_web.get_format_value
2107 ( g_business_group_id
2108 , g_perq_record(i).perq_value2
2109 )
2110 );
2111 write_tag( p_tag_name => 'P' || i || '_V1'
2112 , p_tag_value => pay_us_employee_payslip_web.get_format_value
2113 ( g_business_group_id
2114 , g_perq_record(i).perq_value1 + g_perq_record(i).perq_value2
2115 )
2116 );
2117
2118 END LOOP;
2119 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
2120
2121 END build_form12ba_xml;
2122
2123 --------------------------------------------------------------------------
2124 -- --
2125 -- Name : GET_TEMPLATE --
2126 -- Type : PROCEDURE --
2127 -- Access : Public --
2128 -- Description : This procedure gets the payslip template code --
2129 -- --
2130 -- Parameters : --
2131 -- IN : p_business_group_id NUMBER --
2135 ,p_template OUT NOCOPY VARCHAR2
2132 -- OUT : p_template VARCHAR2 --
2133 --------------------------------------------------------------------------
2134 PROCEDURE get_template (p_business_group_id IN NUMBER
2136 )
2137 IS
2138
2139 l_procedure varchar2(100);
2140
2141 BEGIN
2142 g_debug := hr_utility.debug_enabled;
2143 l_procedure := g_package ||'build_form12ba_xml';
2144 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2145
2146
2147 IF g_debug THEN
2148 pay_in_utils.trace('p_business_group_id ', to_char(p_business_group_id) );
2149 END IF;
2150
2151 p_template := 'PYINEOYR';
2152 g_chunk_size := 10;
2153 g_business_group_id := p_business_group_id;
2154
2155 IF g_debug THEN
2156 pay_in_utils.trace('p_template ', p_template);
2157 END IF;
2158 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2159 EXCEPTION
2160
2161 WHEN OTHERS THEN
2162 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,20);
2163 RAISE;
2164
2165 END get_template;
2166
2167 --------------------------------------------------------------------------
2168 -- --
2169 -- Name : FETCH_XML --
2170 -- Type : PROCEDURE --
2171 -- Access : Public --
2172 -- Description : This procedure returns the next CLOB available in --
2173 -- global CLOB array --
2174 -- --
2175 -- Parameters : --
2176 -- IN : N/A --
2177 -- OUT : p_clob CLOB --
2178 --------------------------------------------------------------------------
2179 PROCEDURE fetch_xml (p_clob OUT NOCOPY CLOB)
2180 IS
2181 l_procedure varchar2(100);
2182
2183 BEGIN
2184 g_debug := hr_utility.debug_enabled;
2185 l_procedure := g_package ||'fetch_xml';
2186 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2187
2188 -- If Clobs exists return next clob else exit NULL
2189 hr_utility.trace('Clob Count : ' || g_clob_cnt);
2190 hr_utility.trace('Clob Fetch Count : ' || g_fetch_clob_cnt);
2191 IF (g_clob_cnt <> 0 ) AND (g_fetch_clob_cnt < g_clob_cnt) THEN
2192 g_fetch_clob_cnt := g_fetch_clob_cnt + 1;
2193 p_clob := g_clob(g_fetch_clob_cnt);
2194 ELSE
2195 p_clob := null;
2196 END IF;
2197
2198 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2199
2200 END fetch_xml;
2201
2202 --------------------------------------------------------------------------
2203 -- --
2204 -- Name : GET_IMAGE_DETAILS --
2205 -- Type : PROCEDURE --
2206 -- Access : Public --
2207 -- Description : This procedure builds the xml for image files
2208 -- used in Form 16 and Form 12BA
2209 -- --
2210 -- Parameters : --
2211 -- IN : p_gre_id NUMBER
2212 --------------------------------------------------------------------------
2213
2214 PROCEDURE get_image_details ( p_gre_id IN hr_organization_units.organization_id%TYPE)
2215 IS
2216 CURSOR csr_get_image_details (p_action_context_id NUMBER)
2217 IS
2218 SELECT
2219 'IMAGE_FILE1' image_form16_name
2220 ,Action_information23 image_form16_value
2221 FROM pay_action_information
2222 WHERE action_context_id = p_action_context_id
2223 AND action_information_category = 'IN_EOY_ORG'
2224 AND action_information1 = to_char(p_gre_id)
2225 AND ROWNUM =1;
2226
2227 l_procedure VARCHAR2(100);
2228 l_action_context_id pay_assignment_actions.assignment_action_id%TYPE;
2229
2230
2231 BEGIN
2232 g_debug := hr_utility.debug_enabled;
2233 l_procedure := g_package ||'get_gre_details';
2234 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2235
2236 IF g_debug THEN
2237 pay_in_utils.trace('p_gre_id ',to_char(p_gre_id ));
2238 END IF;
2239
2240 l_action_context_id := get_max_context_id(p_gre_id,g_assessment_year);
2241
2242 FOR i IN csr_get_image_details(l_action_context_id)
2243 LOOP
2244 write_tag(i.image_form16_name,i.image_form16_value);
2245 END LOOP;
2246
2247 EXCEPTION
2248
2249 WHEN OTHERS THEN
2250 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,20);
2251 RAISE;
2252
2253 END get_image_details;
2254 --------------------------------------------------------------------------
2255 -- --
2256 -- Name : LOAD_XML --
2257 -- Type : PROCEDURE --
2258 -- Access : Public --
2259 -- Description : This procedure makes a list of XMLs in a global --
2260 -- CLOB array --
2264 -- p_assessment_year VARCHAR2 --
2261 -- --
2262 -- Parameters : --
2263 -- IN : p_business_group_id NUMBER --
2265 -- p_gre_organization VARCHAR2 --
2266 -- p_employee_type VARCHAR2 --
2267 -- p_employee_number VARCHAR2 --
2268 -- p_report_date VARCHAR2 --
2269 -- OUT : p_clob_cnt NUMBER --
2270 --------------------------------------------------------------------------
2271 -- Flow : --
2272 -- For EACH GRE --
2273 -- Get the details of GRE --
2274 -- For EACH Employee --
2275 -- Get the details for Form 16, 16AA, 12BA and --
2276 -- Build the XML for every employee --
2277 -- by calling the respective procedures --
2278 -- END FOR EACH EMPLOYEE --
2279 -- END FOR EACH GRE --
2280 --------------------------------------------------------------------------
2281 -- Bug # 12700621 : Adding paramter for report date
2282 PROCEDURE load_xml (p_business_group_id IN NUMBER
2283 ,p_assessment_year IN VARCHAR2
2284 ,p_gre_organization IN VARCHAR2 DEFAULT NULL
2285 ,p_employee_type IN VARCHAR2
2286 ,p_employee_number IN VARCHAR2 DEFAULT NULL
2287 ,p_report_date IN VARCHAR2
2288 ,p_designation IN VARCHAR2
2289 ,p_clob_cnt OUT NOCOPY NUMBER
2290 )
2291 IS
2292 l_procedure VARCHAR2(100);
2293 l_open_tag VARCHAR2(100);
2294 l_gre_id hr_organization_units.organization_id%TYPE;
2295 l_action_context_id pay_assignment_actions.assignment_action_id%TYPE;
2296 l_emp_number per_people_f.employee_number%TYPE;
2297 l_emp_count NUMBER;
2298 l_start_date DATE;
2299 l_end_date DATE;
2300 l_source_id NUMBER;
2301 l_flag_for_16aa NUMBER;
2302
2303
2304
2305 CURSOR csr_fetch_gre( p_gre_id IN hr_organization_units.organization_id%TYPE)
2306 IS
2307 SELECT hou.organization_id orgid
2308 FROM hr_all_organization_units hou
2309 , hr_organization_information hoi
2310 WHERE hou.organization_id = hoi.organization_id
2311 AND hoi.org_information_context = 'CLASS'
2312 AND hoi.org_information1 = 'HR_LEGAL'
2313 AND hoi.org_information2 = 'Y'
2314 AND hou.organization_id = NVL(p_gre_id,hou.organization_id)
2315 AND hou.business_group_id = p_business_group_id
2316 AND EXISTS (SELECT 1
2317 FROM pay_action_information pai,
2318 pay_payroll_actions ppa
2319 WHERE pai.action_information_category = 'IN_EOY_ORG'
2320 AND pai.action_information1 = hou.organization_id
2321 AND pai.action_information3 = p_assessment_year
2322 AND pai.action_context_type ='PA'
2323 AND pai.action_context_id = ppa.payroll_action_id
2324 AND ppa.report_qualifier ='IN'
2325 AND ppa.report_type ='IN_EOY_ARCHIVE'
2326 AND ppa.report_category ='ARCHIVE'
2327 AND ROWNUM < 2)
2328 ORDER BY hou.name;
2329
2330 CURSOR csr_fetch_employees( p_gre_id hr_organization_units.organization_id%TYPE)
2331 IS
2332 SELECT MAX(pai.action_context_id) action_context_id
2333 , pai.action_information17 start_date
2334 , pai.action_information1 employee_number
2335 FROM pay_action_information pai
2336 ,per_assignments_f asg
2337 WHERE pai.action_information_category = 'IN_EOY_PERSON'
2338 AND asg.assignment_id = pai.assignment_id
2339 AND asg.business_group_id = p_business_group_id
2340 AND pai.action_information3 = to_char(p_gre_id) /*Bug 13564801 */
2341 AND pai.action_information2 = p_assessment_year
2342 AND pai.action_information1 LIKE NVL(p_employee_number,'%')
2343 GROUP BY pai.action_information1,pai.action_information17
2344 ORDER BY LENGTH(pai.action_information1), pai.action_information1;
2345 /* This order by ensures that the employee number is sorted in the ascending
2346 order based on the order of the characters according to length
2347 */
2348
2349 CURSOR csr_emp_source_id(p_start_date DATE
2350 ,p_employee_number VARCHAR2
2351 ,p_gre_id hr_organization_units.organization_id%TYPE
2352 ,p_action_context_id NUMBER)
2353 IS
2354 SELECT pai.source_id Payroll_run_action_id
2355 ,pai.action_information18 end_date
2356 FROM pay_action_information pai
2357 WHERE pai.action_information_category ='IN_EOY_PERSON'
2358 AND pai.action_information17 = p_start_date
2359 AND pai.action_information1 = p_employee_number
2360 AND pai.action_information2 = p_assessment_year
2361 AND pai.action_information3 = to_char(p_gre_id) /*Bug 13564801 */
2362 AND pai.action_context_id = p_action_context_id
2363 AND EXISTS (SELECT 1
2367 AND paa.payroll_action_id = ppa.payroll_action_id
2364 FROM pay_assignment_actions paa
2365 ,pay_payroll_actions ppa
2366 WHERE pai.source_id = paa.assignment_action_id
2368 AND ppa.business_group_id = p_business_group_id );
2369
2370
2371 CURSOR csr_global_value(p_global_name ff_globals_f.global_name%TYPE)
2372 IS
2373 SELECT global_value
2374 FROM ff_globals_f
2375 WHERE global_name =p_global_name
2376 AND legislation_code='IN'
2377 AND g_tax_end_date BETWEEN effective_start_date and effective_end_date;
2378
2379 begin
2380
2381 g_debug := hr_utility.debug_enabled;
2382 l_procedure := g_package ||'load_xml';
2383 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2384
2385
2386 IF g_debug THEN
2387 pay_in_utils.trace('**************************************************','********************');
2388 pay_in_utils.trace('Business Group ID',to_char(p_business_group_id ));
2389 pay_in_utils.trace('Assessment Year ',p_assessment_year );
2390 pay_in_utils.trace('GRE Organization ',p_gre_organization );
2391 pay_in_utils.trace('Employee Type ',p_employee_type );
2392 pay_in_utils.trace('Employee Number ',p_employee_number );
2393 pay_in_utils.trace('Designation ',p_designation );
2394 pay_in_utils.trace('**************************************************','********************');
2395 END IF;
2396
2397 l_emp_count := 0;
2398 g_assessment_year := p_assessment_year;
2399 g_tax_year := (to_number(SUBSTR(g_assessment_year,1,4)) - 1)||'-'||SUBSTR(g_assessment_year,3,2);
2400 g_tax_end_date := fnd_date.string_to_date(('31/03/'|| SUBSTR(g_assessment_year,1,4)),'DD/MM/YYYY');
2401 g_tax_start_date := ADD_MONTHS(g_tax_end_date,-12) +1;
2402
2403
2404 OPEN csr_global_value('IN_SECTION_80CCE_LIMIT');
2405 FETCH csr_global_value INTO g_80cce_limit;
2406 CLOSE csr_global_value;
2407
2408
2409 FOR gre_record IN csr_fetch_gre(p_gre_organization)
2410 LOOP
2411
2412 /* Fetch the gre id for each GRE*/
2413 l_gre_id := gre_record.orgid;
2414 /* Close and reopen tag for GRE */
2415 IF l_emp_count <>0 THEN
2416 l_open_tag := '</GRE>';
2417 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2418
2419 l_open_tag := '<GRE>';
2420 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2421 build_gre_xml(l_gre_id,p_designation);
2422 END IF;
2423
2424 /* Fetch the Employees in the GRE and build the XML for each employee */
2425 FOR emp_record IN csr_fetch_employees(l_gre_id)
2426 LOOP
2427 l_action_context_id := emp_record.action_context_id;
2428 l_start_date := emp_record.start_date;
2429 l_emp_number := emp_record.employee_number;
2430
2431
2432 OPEN csr_emp_source_id(l_start_date,l_emp_number,l_gre_id,l_action_context_id);
2433 FETCH csr_emp_source_id INTO l_source_id,l_end_date;
2434 IF csr_emp_source_id%FOUND THEN
2435 IF ((l_end_date = g_tax_end_date AND (p_employee_type = 'TRANSFERRED' OR p_employee_type = 'TERMINATED')) OR
2436 (l_end_date <> g_tax_end_date AND p_employee_type ='CURRENT')
2437 )THEN
2438 NULL;
2439 ELSE
2440 IF l_emp_count = 0 OR l_emp_count > g_chunk_size THEN
2441 IF l_emp_count <> 0 THEN
2442 /* Close all the open tags */
2443 l_open_tag := '</GRE></EOY>';
2444 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2445 /* Close the temporary CLOB opened */
2446 dbms_lob.close(g_tmp_clob);
2447 /* Store the temporary CLOB in the Global CLOB array */
2448 g_clob_cnt := g_clob_cnt + 1;
2449 g_clob(g_clob_cnt) := g_tmp_clob;
2450 /* Reset the employees count to 1 */
2451 l_emp_count := 1;
2452 END IF;
2453 /* Create a new temporary CLOB for writing XML Data */
2454 dbms_lob.createtemporary(g_tmp_clob,FALSE,DBMS_LOB.CALL);
2455 dbms_lob.open(g_tmp_clob,dbms_lob.lob_readwrite);
2456 /* Open the parent Tags */
2457 l_open_tag := '<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?><EOY>';
2458 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2459 /* Write the Common data in the New CLOB created */
2460
2461 -- Bug # 12700621 : Adding paramter for report date
2462 /* write_tag('REPORT_DATE',TO_CHAR(TRUNC(SYSDATE),'DD-MM-YYYY')); */
2463 IF p_report_date IS NOT NULL THEN
2464 write_tag('REPORT_DATE',TO_CHAR(TO_DATE(p_report_date,'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YYYY'));
2465 ELSE
2466 write_tag('REPORT_DATE',TO_CHAR(TO_DATE(sysdate,'DD-MM-RRRR HH24:MI:SS'),'DD-MM-YYYY'));
2467 END IF;
2468
2469 write_tag('ASSESS_YR',SUBSTR(g_assessment_year,1,5) || SUBSTR(g_assessment_year,8,2));
2470 write_tag('FIN_YEAR',g_tax_year);
2471
2472 -- Bug # 12700621 : Adding paramter for report date
2473 /* write_tag('REPORT_DATE_TIME',to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS')); */
2474 IF p_report_date IS NOT NULL THEN
2475 write_tag('REPORT_DATE_TIME',TO_CHAR(TO_DATE(p_report_date,'YYYY-MM-DD HH24:MI:SS'),'DD-Mon-YYYY HH24:MI:SS'));
2476 ELSE
2477 write_tag('REPORT_DATE_TIME',TO_CHAR(TO_DATE(sysdate,'DD-MM-RRRR HH24:MI:SS'),'DD-Mon-YYYY HH24:MI:SS'));
2478 END IF;
2479
2480 -- write_tag('DUMMY',pay_us_employee_payslip_web.get_format_value(g_business_group_id,0));
2481 /* The following call gets the GRE Related Data to generate the report*/
2482 l_open_tag := '<GRE>';
2483 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2484
2485 build_gre_xml(l_gre_id,p_designation);
2486 END IF;
2487 l_emp_count := l_emp_count + 1;
2488
2489 l_open_tag := '<EMPLOYEE>';
2490 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2491 /* The following calls builds the XML related to form16, form 16aa and 12ba
2492 required to generate the report
2493 */
2494
2495 pay_in_utils.set_location(g_debug,'INDIA F16: Building XML for Employee',70);
2496 build_employee_xml(l_action_context_id,l_source_id,p_designation,p_rem_pay_period,p_flag);
2497 pay_in_utils.set_location(g_debug,'Building XML for Form 16/16AA',80);
2498 build_form16_xml(l_action_context_id,l_source_id,p_rem_pay_period,p_flag,l_flag_for_16aa);
2499 IF (l_flag_for_16aa = -1) THEN
2500 l_emp_count := l_emp_count - 1;
2501 ELSE
2502 pay_in_utils.set_location(g_debug,'Building XML for Form 12BA',90);
2503 init_form12ba_code;
2504 build_form12ba_xml(l_action_context_id,l_source_id);
2505 get_image_details(l_gre_id);
2506 l_open_tag := '<C_FORM12_FLAG>0</C_FORM12_FLAG>';
2507 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2508 l_open_tag := '<C_FORM16_FLAG>0</C_FORM16_FLAG>';
2509 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2510 l_open_tag := '<C_ANNEX_B>0</C_ANNEX_B>';
2511 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2512 l_open_tag := '</EMPLOYEE>';
2513 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2514 END IF;
2515 END IF;
2516 END IF;
2517 CLOSE csr_emp_source_id;
2518 END LOOP;
2519
2520
2521 END LOOP;
2522 IF l_emp_count <> 0 THEN
2523 l_open_tag := '</GRE></EOY>';
2524 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2525 /* Close the temporary CLOB opened which is not yet closed*/
2526 dbms_lob.close(g_tmp_clob);
2527 /* Copy the Temporary CLOB into the Global CLOB Array */
2528 g_clob_cnt := g_clob_cnt + 1;
2529 g_clob(g_clob_cnt) := g_tmp_clob;
2530 p_clob_cnt := g_clob_cnt;
2531 ELSE
2532 p_clob_cnt := g_clob_cnt;
2533 END IF;
2534 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,100);
2535 END load_xml;
2536
2537
2538 PROCEDURE load_xml_burst (p_business_group_id IN NUMBER
2539 ,p_assessment_year IN VARCHAR2
2540 ,p_gre_organization IN VARCHAR2 DEFAULT NULL
2541 ,p_employee_type IN VARCHAR2
2542 ,p_employee_number IN VARCHAR2 DEFAULT NULL
2543 ,p_report_date IN VARCHAR2
2544 ,p_report_type IN VARCHAR2
2545 ,p_designation IN VARCHAR2
2546 ,p_xml OUT NOCOPY CLOB
2547 )
2548 IS
2549 l_procedure VARCHAR2(100);
2550 l_open_tag VARCHAR2(100);
2551 l_gre_id hr_organization_units.organization_id%TYPE;
2552 l_action_context_id pay_assignment_actions.assignment_action_id%TYPE;
2553 l_emp_number per_people_f.employee_number%TYPE;
2554 l_emp_count NUMBER;
2555 l_start_date DATE;
2556 l_end_date DATE;
2557 l_source_id NUMBER;
2558 l_flag_for_16aa NUMBER;
2559 --l_emp_id per_people_f.employee_number%TYPE;
2560
2561
2562 CURSOR csr_fetch_gre( p_gre_id IN hr_organization_units.organization_id%TYPE)
2563 IS
2564 SELECT hou.organization_id orgid
2565 FROM hr_all_organization_units hou
2566 , hr_organization_information hoi
2570 AND hoi.org_information2 = 'Y'
2567 WHERE hou.organization_id = hoi.organization_id
2568 AND hoi.org_information_context = 'CLASS'
2569 AND hoi.org_information1 = 'HR_LEGAL'
2571 AND hou.organization_id = NVL(p_gre_id,hou.organization_id)
2572 AND hou.business_group_id = p_business_group_id
2573 AND EXISTS (SELECT 1
2574 FROM pay_action_information pai,
2575 pay_payroll_actions ppa
2576 WHERE pai.action_information_category = 'IN_EOY_ORG'
2577 AND pai.action_information1 = hou.organization_id
2578 AND pai.action_information3 = p_assessment_year
2579 AND pai.action_context_type ='PA'
2580 AND pai.action_context_id = ppa.payroll_action_id
2581 AND ppa.report_qualifier ='IN'
2582 AND ppa.report_type ='IN_EOY_ARCHIVE'
2583 AND ppa.report_category ='ARCHIVE'
2584 AND ROWNUM < 2)
2585 ORDER BY hou.name;
2586
2587 CURSOR csr_fetch_employees( p_gre_id hr_organization_units.organization_id%TYPE)
2588 IS
2589 SELECT MAX(pai.action_context_id) action_context_id
2590 , pai.action_information17 start_date
2591 , pai.action_information1 employee_number
2592 FROM pay_action_information pai
2593 ,per_assignments_f asg
2594 WHERE pai.action_information_category = 'IN_EOY_PERSON'
2595 AND asg.assignment_id = pai.assignment_id
2596 AND asg.business_group_id = p_business_group_id
2597 AND pai.action_information3 = to_char(p_gre_id) /*Bug 13564801 */
2598 AND pai.action_information2 = p_assessment_year
2599 AND pai.action_information1 LIKE NVL(p_employee_number,'%')
2600 AND pai.action_information21 is not null
2601 GROUP BY pai.action_information1,pai.action_information17
2602 ORDER BY LENGTH(pai.action_information1), pai.action_information1;
2603 /* This order by ensures that the employee number is sorted in the ascending
2604 order based on the order of the characters according to length
2605 */
2606
2607 CURSOR csr_emp_source_id(p_start_date DATE
2608 ,p_employee_number VARCHAR2
2609 ,p_gre_id hr_organization_units.organization_id%TYPE
2610 ,p_action_context_id NUMBER)
2611 IS
2612 SELECT pai.source_id Payroll_run_action_id
2613 ,pai.action_information18 end_date
2614 FROM pay_action_information pai
2615 WHERE pai.action_information_category ='IN_EOY_PERSON'
2616 AND pai.action_information17 = p_start_date
2617 AND pai.action_information1 = p_employee_number
2618 AND pai.action_information2 = p_assessment_year
2619 AND pai.action_information3 = to_char(p_gre_id) /*Bug 13564801 */
2620 AND pai.action_context_id = p_action_context_id
2621 AND EXISTS (SELECT 1
2622 FROM pay_assignment_actions paa
2623 ,pay_payroll_actions ppa
2624 WHERE pai.source_id = paa.assignment_action_id
2625 AND paa.payroll_action_id = ppa.payroll_action_id
2626 AND ppa.business_group_id = p_business_group_id );
2627
2628
2629 CURSOR csr_global_value(p_global_name ff_globals_f.global_name%TYPE)
2630 IS
2631 SELECT global_value
2632 FROM ff_globals_f
2633 WHERE global_name =p_global_name
2634 AND legislation_code='IN'
2635 AND g_tax_end_date BETWEEN effective_start_date and effective_end_date;
2636
2637 begin
2638
2639 g_debug := hr_utility.debug_enabled;
2640 l_procedure := g_package ||'load_xml_burst';
2641 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2642
2643
2644 IF g_debug THEN
2645 pay_in_utils.trace('**************************************************','********************');
2646 pay_in_utils.trace('Business Group ID',to_char(p_business_group_id ));
2647 pay_in_utils.trace('Assessment Year ',p_assessment_year );
2648 pay_in_utils.trace('GRE Organization ',p_gre_organization );
2649 pay_in_utils.trace('Employee Type ',p_employee_type );
2650 pay_in_utils.trace('Employee Number ',p_employee_number );
2651 pay_in_utils.trace('Report Type ',p_report_type ); /*XML = Form16, XML2= Form12B */
2652 pay_in_utils.trace('Designation ',p_designation ); /*POS = Position,JOB= Job */
2653 pay_in_utils.trace('**************************************************','********************');
2654 END IF;
2655
2656 l_emp_count := 0;
2657 g_assessment_year := p_assessment_year;
2658 g_tax_year := (to_number(SUBSTR(g_assessment_year,1,4)) - 1)||'-'||SUBSTR(g_assessment_year,3,2);
2659 g_tax_end_date := fnd_date.string_to_date(('31/03/'|| SUBSTR(g_assessment_year,1,4)),'DD/MM/YYYY');
2660 g_tax_start_date := ADD_MONTHS(g_tax_end_date,-12) +1;
2661
2662
2663 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,20);
2664 OPEN csr_global_value('IN_SECTION_80CCE_LIMIT');
2665 FETCH csr_global_value INTO g_80cce_limit;
2666 CLOSE csr_global_value;
2667
2668 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,30);
2669 FOR gre_record IN csr_fetch_gre(p_gre_organization)
2670 LOOP
2671 l_gre_id := gre_record.orgid;
2672 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,40);
2673 pay_in_utils.set_location(g_debug,'l_gre_id: '||l_gre_id,45);
2674 IF l_emp_count <>0 THEN
2675 l_open_tag := '</GRE>';
2676 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2677 l_open_tag := '<GRE>';
2678 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2679 build_gre_xml(l_gre_id,p_designation);
2680 END IF;
2681 FOR emp_record IN csr_fetch_employees(l_gre_id)
2682 LOOP
2683 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,50);
2684 l_action_context_id := emp_record.action_context_id;
2685 l_start_date := emp_record.start_date;
2686 l_emp_number := emp_record.employee_number;
2687 OPEN csr_emp_source_id(l_start_date,l_emp_number,l_gre_id,l_action_context_id);
2688 FETCH csr_emp_source_id INTO l_source_id,l_end_date;
2689 IF csr_emp_source_id%FOUND THEN
2690 IF ((l_end_date = g_tax_end_date AND (p_employee_type = 'TRANSFERRED' OR p_employee_type = 'TERMINATED')) OR
2691 (l_end_date <> g_tax_end_date AND p_employee_type ='CURRENT')
2692 )THEN
2693 NULL;
2694 ELSE
2695 IF l_emp_count = 0 OR l_emp_count > 0 THEN
2696 IF l_emp_count <> 0 THEN
2697
2698 l_emp_count := 1;
2699 END IF;
2700 IF l_emp_count = 0 THEN
2701
2702 dbms_lob.createtemporary(g_tmp_clob,FALSE,DBMS_LOB.CALL);
2703 dbms_lob.open(g_tmp_clob,dbms_lob.lob_readwrite);
2704
2705 l_open_tag := '<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?><EOY>';
2706 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2707
2708 IF p_report_date IS NOT NULL THEN
2709 write_tag('REPORT_DATE',TO_CHAR(TO_DATE(p_report_date,'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YYYY'));
2710 ELSE
2711 write_tag('REPORT_DATE',TO_CHAR(TO_DATE(sysdate,'DD-MM-RRRR HH24:MI:SS'),'DD-MM-YYYY'));
2712 END IF;
2713 write_tag('ASSESS_YR',SUBSTR(g_assessment_year,1,5) || SUBSTR(g_assessment_year,8,2));
2714 write_tag('FIN_YEAR',g_tax_year);
2715 IF p_report_date IS NOT NULL THEN
2716 write_tag('REPORT_DATE_TIME',TO_CHAR(TO_DATE(p_report_date,'YYYY-MM-DD HH24:MI:SS'),'DD-Mon-YYYY HH24:MI:SS'));
2717 ELSE
2718 write_tag('REPORT_DATE_TIME',TO_CHAR(TO_DATE(sysdate,'DD-MM-RRRR HH24:MI:SS'),'DD-Mon-YYYY HH24:MI:SS'));
2719 END IF;
2720 l_open_tag := '<GRE>';
2721 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2722
2723 build_gre_xml(l_gre_id,p_designation);
2724 END IF;
2725 END IF;
2726 l_emp_count := l_emp_count + 1;
2727 l_open_tag := '<EMPLOYEE>';
2728 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2729 pay_in_utils.set_location(g_debug,'INDIA F16: Building XML for Employee',70);
2730 build_employee_xml(l_action_context_id,l_source_id,p_designation,p_rem_pay_period,p_flag);
2731 pay_in_utils.set_location(g_debug,'Building XML for Form 16/16AA',80);
2732 build_form16_xml(l_action_context_id,l_source_id,p_rem_pay_period,p_flag,l_flag_for_16aa);
2733 IF (l_flag_for_16aa = -1) THEN
2734 l_emp_count := l_emp_count - 1;
2735 ELSE
2736 pay_in_utils.set_location(g_debug,'Building XML for Form 12BA',90);
2737 init_form12ba_code;
2738 build_form12ba_xml(l_action_context_id,l_source_id);
2739 get_image_details(l_gre_id);
2740 IF (p_report_type = 'XML') THEN ---------- Mail Form16
2741 l_open_tag := '<C_FORM12_FLAG>0</C_FORM12_FLAG>';
2742 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2743 l_open_tag := '<C_FORM16_FLAG>1</C_FORM16_FLAG>';
2744 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2745 l_open_tag := '<C_ANNEX_B>0</C_ANNEX_B>';
2746 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2747 l_open_tag := '<FORM_NAME>Form 16</FORM_NAME>';
2748 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2749
2750 ELSIF (p_report_type = 'XML2') THEN -------- Mail Form12B
2751 l_open_tag := '<C_FORM12_FLAG>1</C_FORM12_FLAG>';
2752 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2753 l_open_tag := '<C_FORM16_FLAG>0</C_FORM16_FLAG>';
2754 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2755 l_open_tag := '<C_ANNEX_B>0</C_ANNEX_B>';
2756 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2757 l_open_tag := '<FORM_NAME>Form 12BA</FORM_NAME>';
2758 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2759
2760 ELSE -------- Mail Annexure B
2761 l_open_tag := '<C_FORM12_FLAG>0</C_FORM12_FLAG>';
2762 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2763 l_open_tag := '<C_FORM16_FLAG>0</C_FORM16_FLAG>';
2764 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2765 l_open_tag := '<C_ANNEX_B>1</C_ANNEX_B>';
2766 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2767 l_open_tag := '<FORM_NAME>Annexure-B</FORM_NAME>';
2768 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2769
2770
2771 END IF;
2772 l_open_tag := '</EMPLOYEE>';
2773 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2774 END IF;
2775 END IF;
2776 END IF;
2777 CLOSE csr_emp_source_id;
2778 END LOOP;
2779 END LOOP;
2780 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,60);
2781 IF l_emp_count <> 0 THEN
2782 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,70);
2783 l_open_tag := '</GRE></EOY>';
2784 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2785 dbms_lob.close(g_tmp_clob);
2786 p_xml :=g_tmp_clob;
2787 ELSE
2788 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,80);
2789 p_xml :=g_tmp_clob;
2790 END IF;
2791 fnd_file.put_line(fnd_file.log,dbms_lob.substr(g_tmp_clob));
2792 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,100);
2793 END load_xml_burst;
2794
2795
2796
2797 BEGIN
2798
2799 -- Initialize Globals
2800 g_clob_cnt := 0;
2801 g_fetch_clob_cnt := 0;
2802 -- g_package := 'pay_in_eoy_reports';
2803 g_chunk_size := 10;
2804 g_salary_record(1).Name := 'SECTION_17_1';
2805 g_salary_record(2).Name := 'SECTION_17_2';
2806 g_salary_record(3).Name := 'SECTION_17_3';
2807 g_salary_record(4).Name := 'SECTION_17';
2808 g_salary_record(5).Name := 'SEC10_TOTAL';
2809 g_salary_record(6).Name := 'SEC17_SEC10';
2810 g_salary_record(7).Name := 'ENT_ALLOWANCE';
2811 g_salary_record(8).Name := 'EMPLOYMENT_TAX';
2812 g_salary_record(9).Name := 'SEC16_TOTAL';
2813 g_salary_record(10).Name := 'HEAD_SALARIES';
2814 g_salary_record(11).Name := 'OTHER_INCOME';
2815 g_salary_record(12).Name := 'GROSS_INCOME';
2816 g_salary_record(13).Name := 'TOTAL_INCOME';
2817 g_salary_record(14).Name := 'TAX_ON_INCOME';
2818 g_salary_record(15).Name := 'SURCHARGE';
2819 g_salary_record(16).Name := 'CESS';
2820 g_salary_record(17).Name := 'TAX_PAYABLE';
2821 g_salary_record(18).Name := 'SEC89_RELIEF';
2822 g_salary_record(19).Name := 'TOTAL_TAX_PAYABLE';
2823 g_salary_record(20).Name := 'TDS_DEDUCTED_SANS_ER_TAX';
2824 g_salary_record(21).Name := 'BALANCE_TAX';
2825 g_salary_record(22).Name := 'EMPLOYER_TAX';
2826 g_salary_record(23).Name := 'TDS_DEDUCTED';
2827
2828 END PAY_IN_EOY_REPORTS;