[Home] [Help]
PACKAGE: APPS.PAY_IN_24Q_ER_RETURNS
Source
1 PACKAGE pay_in_24q_er_returns AUTHID CURRENT_USER AS
2 /* $Header: pyineqit.pkh 120.27.12020000.2 2012/07/06 11:01:26 vmaripal ship $ */
3
4 level_cnt NUMBER;
5
6
7 g_gre_org_id VARCHAR2(15):=pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM');
8 g_assess_year VARCHAR2(15) :=pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM');
9 g_quarter VARCHAR2(5):=pay_magtape_generic.get_parameter_value('QUARTER_PARAM');
10
11
12 --------------------------------------------------------------------------
13 -- --
14 -- Name : CHALLAN_REC_COUNT --
15 -- Type : FUNCTION --
16 -- Access : Public --
17 -- Description : This function returns the Total number of records --
18 -- in the Challan Details of the Magtape --
19 -- Parameters : --
20 -- IN : p_gre_org_id VARCHAR2 --
21 -- p_assess_period VARCHAR2 --
22 -- p_max_action_id VARCHAR2 --
23 --------------------------------------------------------------------------
24 FUNCTION challan_rec_count (p_gre_org_id IN VARCHAR2
25 ,p_assess_period IN VARCHAR2
26 ,p_max_action_id IN VARCHAR2)
27 RETURN VARCHAR2;
28
29 --------------------------------------------------------------------------
30 -- --
31 -- Name : GET_PRODUCT_RELEASE --
32 -- Type : FUNCTION --
33 -- Access : Public --
34 -- Description : This function returns the name of the software --
38 FUNCTION get_product_release
35 -- used for preparing the e-TDS statement in File --
36 -- Header --
37 --------------------------------------------------------------------------
39 RETURN VARCHAR2;
40
41 --------------------------------------------------------------------------
42 -- --
43 -- Name : SALARY_REC_COUNT --
44 -- Type : FUNCTION --
45 -- Access : Public --
46 -- Description : This function returns the Total number of records --
47 -- in the Salary Details of the Magtape --
48 -- Parameters : --
49 -- IN : p_gre_org_id VARCHAR2 --
50 -- p_assess_period VARCHAR2 --
51 --------------------------------------------------------------------------
52 FUNCTION salary_rec_count (p_gre_org_id IN VARCHAR2
53 ,p_assess_period IN VARCHAR2)
54 RETURN VARCHAR2;
55
56 --------------------------------------------------------------------------
57 -- --
58 -- Name : GROSS_TOT_TDS_CHALLAN --
59 -- Type : FUNCTION --
60 -- Access : Public --
61 -- Description : This function returns the Gross Total of TDS --
62 -- deducted as per Challan details annexure --
63 -- Parameters : --
64 -- IN : p_gre_org_id VARCHAR2 --
65 -- p_assess_period VARCHAR2 --
66 -- p_max_action_id VARCHAR2 --
67 --------------------------------------------------------------------------
68 FUNCTION gross_tot_tds_challan (p_gre_org_id IN VARCHAR2
69 ,p_assess_period IN VARCHAR2
70 ,p_max_action_id IN VARCHAR2)
71 RETURN VARCHAR2;
72
73
74 --------------------------------------------------------------------------
75 -- --
76 -- Name : TOTAL_GROSS_TOT_INCOME --
77 -- Type : FUNCTION --
78 -- Access : Public --
79 -- Description : This function returns the total of Gross Total --
80 -- Income as per salary details annexure --
81 -- Parameters : --
82 -- IN : p_gre_org_id VARCHAR2 --
83 -- p_assess_period VARCHAR2 --
84 --------------------------------------------------------------------------
85 FUNCTION total_gross_tot_income (p_gre_org_id IN VARCHAR2
86 ,p_assess_period IN VARCHAR2)
87 RETURN VARCHAR2;
88
89 --------------------------------------------------------------------------
90 -- --
91 -- Name : GET_24Q_VALUES --
92 -- Type : FUNCTION --
93 -- Access : Public --
94 -- Description : This function returns the values corresponding to --
95 -- the F16 Balances --
96 -- Parameters : --
97 -- IN : p_category VARCHAR2 --
98 -- p_component_name VARCHAR2 --
99 -- p_context_id NUMBER --
100 -- p_source_id NUMBER --
101 -- p_segment_num NUMBER --
102 --------------------------------------------------------------------------
103 FUNCTION get_24Q_values (p_category IN VARCHAR2
104 ,p_component_name IN VARCHAR2
105 ,p_context_id IN NUMBER
106 ,p_source_id IN NUMBER
107 ,p_segment_num IN NUMBER
108 )
109 RETURN VARCHAR2;
110
111 --------------------------------------------------------------------------
112 -- --
113 -- Name : GET_24Q_TAX_VALUES --
114 -- Type : FUNCTION --
115 -- Access : Public --
116 -- Description : This function returns the Gross Total of TDS --
117 -- deducted as per Deductee details annexure --
118 -- Parameters : --
119 -- IN :p_callan_number VARCHAR2 --
120 -- p_gre_org_id VARCHAR2 --
121 --------------------------------------------------------------------------
122 FUNCTION get_24Q_tax_values(
123 p_challan_number IN VARCHAR2
124 ,p_gre_org_id IN VARCHAR2
125 ,p_max_action_id IN VARCHAR2
126 )
127 RETURN VARCHAR2;
128
129 --------------------------------------------------------------------------
130 -- --
131 -- Name : DEDUCTEE_REC_COUNT --
135 -- in the Deductee Details of the Magtape --
132 -- Type : FUNCTION --
133 -- Access : Public --
134 -- Description : This function returns the Total number of records --
136 -- Parameters : --
137 -- IN : p_gre_org_id VARCHAR2 --
138 -- p_assess_year VARCHAR2 --
139 -- p_challan VARCHAR2 --
140 --------------------------------------------------------------------------
141 FUNCTION deductee_rec_count (p_gre_org_id IN VARCHAR2
142 ,p_max_action_id IN VARCHAR2
143 ,p_challan IN VARCHAR2)
144 RETURN VARCHAR2;
145
146
147 --------------------------------------------------------------------------
148 -- --
149 -- Name : CHAPTER_VIA_REC_COUNT --
150 -- Type : FUNCTION --
151 -- Access : Public --
152 -- Description : This function returns the Total number of records --
153 -- in the Chapter-VIA Details of the Magtape --
154 -- Parameters : --
155 -- IN : p_action_context_id VARCHAR2 --
156 -- p_source_id VARCHAR2 --
157 --------------------------------------------------------------------------
158 FUNCTION chapter_VIA_rec_count (p_action_context_id IN VARCHAR2
159 ,p_source_id IN VARCHAR2)
160 RETURN VARCHAR2;
161
162 --------------------------------------------------------------------------
163 -- --
164 -- Name : DEDUCTEE_SALARY_COUNT --
165 -- Type : FUNCTION --
166 -- Access : Public --
167 -- Description : This function returns the Total number of Salary --
168 -- Details records of a particular employee --
169 -- Parameters : --
170 -- IN : p_gre_org_id VARCHAR2 --
171 -- p_assess_period VARCHAR2 --
172 -- p_assignment_id VARCHAR2 --
173 --------------------------------------------------------------------------
174 FUNCTION deductee_salary_count (p_gre_org_id IN VARCHAR2
175 ,p_assess_year IN VARCHAR2
176 ,p_assignment_id IN VARCHAR2)
177 RETURN VARCHAR2;
178
179
180 --------------------------------------------------------------------------
181 -- --
182 -- Name : GET_EMPLOYER_CLASS --
183 -- Type : FUNCTION --
184 -- Access : Public --
185 -- Description : This function returns the employer classfication --
186 -- --
187 -- Parameters : --
188 -- IN : p_gre_org_id VARCHAR2 --
189 --------------------------------------------------------------------------
190 FUNCTION get_emplr_class (p_gre_org_id IN VARCHAR2)
191 RETURN VARCHAR2;
192
193 --------------------------------------------------------------------------
194 -- --
195 -- Name : GET_LOCATION_DETAILS --
196 -- Type : FUNCTION --
197 -- Access : Public --
198 -- Description : This function gets the gre location details --
199 -- --
200 -- Parameters : --
201 -- IN : p_location_id hr_locations.location_id --
202 -- : p_concatenate VARCHAR2 --
203 -- p_field VARCHAR2 --
204 --------------------------------------------------------------------------
205 FUNCTION get_location_details ( p_location_id IN hr_locations.location_id%TYPE
206 ,p_rep_email_id IN VARCHAR2
207 ,p_rep_phone IN VARCHAR2
208 ,p_segment_num IN NUMBER
209 ,p_person_type IN VARCHAR2)
210 RETURN VARCHAR2;
211
212
213 --------------------------------------------------------------------------
214 -- --
215 -- Name : GET_FORMAT_VALUE --
216 -- Type : FUNCTION --
217 -- Access : Public --
218 -- Description : This function returns value with precision --
219 -- of two decimal place --
220 -- --
221 -- Parameters : --
222 -- IN : p_value VARCHAR2 --
223 --------------------------------------------------------------------------
224 FUNCTION get_format_value(p_value IN VARCHAR2)
225 RETURN VARCHAR2;
229 -- --
226
227
228 --------------------------------------------------------------------------
230 -- Name : GET_EMP_CATEGORY --
231 -- Type : FUNCTION --
232 -- Access : Public --
233 -- Description : This function gets the employee category --
234 -- --
235 -- Parameters : --
236 -- IN : p_person_id VARCHAR2 --
237 -- : --
238 -- Returns : l_emp_category --
239 --------------------------------------------------------------------------
240 FUNCTION get_emp_category(p_person_id IN VARCHAR2)
241 RETURN VARCHAR2;
242
243
244 /* FILE HEADER RECORD */
245
246 CURSOR c_f24q_file_header IS
247 SELECT 'TAN_OF_DED=P'
248 , pai.action_information2
249 , 'MAX_ACTION_CONTEXT_ID=P'
250 , pai.action_context_id
251 , 'SUBMIT_DATE=P'
252 , TO_CHAR (SYSDATE,'DDMMYYYY')
253 , 'NAME_OF_UTILITY=P'
254 , pay_in_24q_er_returns.get_product_release
255 FROM pay_action_information pai
256 , pay_payroll_actions ppa
257 WHERE pai.action_information_category = 'IN_24Q_ORG'
258 AND pai.action_context_type = 'PA'
259 AND pai.action_information1 = g_gre_org_id
260 AND pai.action_information3 =g_assess_year||g_quarter
261 AND ppa.action_type='X'
262 AND ppa.action_status = 'C'
263 AND ppa.report_type='IN_24Q_ARCHIVE'
264 AND ppa.report_qualifier = 'IN'
265 AND ppa.payroll_action_id = pai.action_context_id
266 AND pai.action_context_id = ( SELECT MAX(action_context_id)
267 FROM pay_action_information
268 WHERE action_information1 = g_gre_org_id
269 AND action_information3 = g_assess_year||g_quarter
270 AND action_context_type = 'PA'
271 AND action_information_category = 'IN_24Q_ORG')
272 AND ROWNUM=1;
273
274
275
276 /* BATCH HEADER RECORD*/
277
278 CURSOR c_f24q_batch_header IS
279 SELECT 'TOT_CHALLAN_REC=P'
280 , pay_in_24q_er_returns.challan_rec_count( g_gre_org_id,
281 (g_assess_year||g_quarter),
282 pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID'))
283 , 'TAN_OF_DED=P'
284 , pai.action_information2
285 , 'PAN_OF_TAN=P'
286 , pai.action_information4||'^'
287 , 'ASSESS_YEAR=P'
288 , SUBSTR(g_assess_year,1,4)||SUBSTR(g_assess_year,8,2)
289 , 'FIN_YEAR=P'
290 , SUBSTR(g_assess_year,1,4)-1||LPAD(SUBSTR(g_assess_year,8,2)-1,2,'0')
291 , 'PERIOD=P'
292 , g_quarter
293 , 'LEGAL_NAME=P'
294 , SUBSTR(pai.action_information5,1,75)
295 , 'EMPLOYER_DIV=P'
296 , SUBSTR(pai.action_information8,1,75)||'^'
297 , 'EMPLOYER_ADD1=P'
298 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,1,'EMP')
299 , 'EMPLOYER_ADD2=P'
300 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,2,'EMP')
301 , 'EMPLOYER_ADD3=P'
302 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,3,'EMP')
303 , 'EMPLOYER_ADD4=P'
304 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,4,'EMP')
305 , 'EMPLOYER_ADD5=P'
306 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,5,'EMP')
307 , 'EMP_ADD_CHG=P'
308 , pay_magtape_generic.get_parameter_value('EMP_ADD_CHG')
309 , 'EMPLOYER_TYPE=P'
310 , pay_in_24q_er_returns.get_emplr_class(g_gre_org_id )
311 , 'REP_NAME=P'
312 , SUBSTR(pai.action_information9,1,75)
313 , 'REP_DESIG=P'
314 , SUBSTR(pai.action_information11,1,75)||'^'
315 , 'REP_ADD1=P'
316 , pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,1,'REP')
317 , 'REP_ADD2=P'
318 , pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,2,'REP')
319 , 'REP_ADD3=P'
320 , pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,3,'REP')
321 , 'REP_ADD4=P'
322 , pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,4,'REP')
323 , 'REP_ADD5=P'
324 -- Bug 12332524
325 , (pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information22,6,'REP')
326 || pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,5,'REP'))
327 , 'REP_ADD_CHG=P'
328 , pay_magtape_generic.get_parameter_value('REP_ADD_CHG')
329 , 'GROSS_TDS_CHALLAN=P'
330 , pay_in_24q_er_returns.gross_tot_tds_challan
331 (g_gre_org_id ,
332 (g_assess_year
333 ||g_quarter),
334 pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID'))
335 , 'COUNT_SALARY=P'
339 , 'SAL_DET=P'
336 , pay_in_24q_er_returns.salary_rec_count(g_gre_org_id ,(g_assess_year||g_quarter))
337 , 'GROSS_TOT_TOTAL=P'
338 , pay_in_24q_er_returns.total_gross_tot_income(g_gre_org_id,(g_assess_year||g_quarter))
340 , pay_magtape_generic.get_parameter_value('SAL_DETAILS')
341 , 'STATE_NAME=P'
342 , hr_general.decode_lookup('IN_STATE_CODES',pai.action_information14)
343 , 'PAO_CODE=P'
344 , pai.action_information15
345 , 'DDO_CODE=P'
346 , pai.action_information16
347 , 'MINISTRY_NAME=P'
348 , pai.action_information17
349 , 'OTHER_MINISTRY_NAME=P'
350 , pai.action_information18
351 , 'PAO_REG_CODE=P'
352 , pai.action_information19
353 , 'DDO_REG_CODE=P'
354 , pai.action_information20
355 FROM pay_action_information pai
356 WHERE pai.action_information_category = 'IN_24Q_ORG'
357 AND pai.action_context_type = 'PA'
358 AND pai.action_information1 = g_gre_org_id
359 AND pai.action_information3 = g_assess_year||g_quarter
360 AND pai.action_context_id = pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
361 AND ROWNUM = 1;
362
363
364
365 /* CHALLAN DETAIL RECORD */
366 CURSOR c_f24q_challan_det_rec IS
367 SELECT * from (
368 SELECT DISTINCT 'TOT_DEDUCTEE_REC=P'
369 , pay_in_24q_er_returns.deductee_rec_count(g_gre_org_id
370 ,pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
371 ,pai.action_information1)
372 , 'NIL_CHALLAN_IND=P'
373 , 'N'
374 , 'QUARTER_LAST_DATE=P'
375 , DECODE(g_quarter,'Q1','3006','Q2','3009','Q3','3112','Q4','3103')
376 ||DECODE(g_quarter,'Q4',
377 SUBSTR(g_assess_year,1,4)
378 ,SUBSTR(g_assess_year,1,4)-1)
379 , 'BANK_CHALLAN_NO=P'
380 , pai.action_information1
381 , 'BANK_BRANCH_CODE=P'
382 , SUBSTR(pai.action_information4,1,7)||'^'
383 , 'CHALLAN_DATE=P'
384 , TO_CHAR(fnd_date.canonical_to_date(pai.action_information5),'DDMMYYYY')
385 , 'TDS_DEP=P'
386 , NVL(pai.action_information6,'0')||'.00'
387 , 'SURCHARGE=P'
388 , NVL(pai.action_information7,'0')||'.00'
389 , 'EDU_CESS=P'
390 , NVL(pai.action_information8,'0')||'.00'
391 , 'INTEREST=P'
392 , NVL(pai.action_information9,'0')||'.00'
393 , 'OTHERS=P'
394 , NVL(pai.action_information10,'0')||'.00'
395 , 'TAX_VALUES=P'
396 , pay_in_24q_er_returns.get_24Q_tax_values
397 (pai.action_information1,g_gre_org_id
398 ,pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID'))
399 , 'CHQ_DD_NUM=P'
400 , SUBSTR(pai.action_information11,1,15)||'^'
401 , 'BOOK_ENTRY=P'
402 , pai.action_information12
403 , 'REMARKS=P'
404 , SUBSTR(pai.action_information13,1,14)||'^'
405 , 'CHALLAN_REC_INDEX=P'
406 , pai.action_information25 challan_count
407 FROM pay_action_information pai
408 WHERE action_information_category = 'IN_24Q_CHALLAN'
409 AND action_context_type = 'PA'
410 AND action_information3 = g_gre_org_id
411 AND action_information2 = g_assess_year||g_quarter
412 AND pai.action_context_id= pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
413 AND fnd_date.canonical_to_date(pai.action_information5)<=fnd_date.CHARDATE_TO_DATE(SYSDATE)
414 UNION
415 SELECT
416 'TOT_DEDUCTEE_REC=P'
417 , '0'
418 , 'NIL_CHALLAN_IND=P'
419 , 'Y'
420 , 'QUARTER_LAST_DATE=P'
421 , DECODE(g_quarter,'Q1','3006','Q2','3009','Q3','3112','Q4','3103')
422 ||DECODE(g_quarter,'Q4',
423 SUBSTR(g_assess_year,1,4)
424 ,SUBSTR(g_assess_year,1,4)-1)
425 , 'BANK_CHALLAN_NO=P'
426 , ''
427 , 'BANK_BRANCH_CODE=P'
428 , '^'
429 , 'CHALLAN_DATE=P'
430 , ''
431 , 'TDS_DEP=P'
432 , '0.00'
433 , 'SURCHARGE=P'
434 , '0.00'
435 , 'EDU_CESS=P'
436 , '0.00'
437 , 'INTEREST=P'
438 , '0.00'
439 , 'OTHERS=P'
440 , '0.00'
441 , 'TAX_VALUES=P'
442 , '0.00^0.00^0.00^0.00^0.00^'
443 , 'CHQ_DD_NUM=P'
444 , '^'
445 , 'BOOK_ENTRY=P'
446 , ''
447 , 'REMARKS=P'
448 , '^'
449 , 'CHALLAN_REC_INDEX=P'
450 , '1' challan_count
451 FROM dual
452 WHERE NOT EXISTS ( SELECT 'EXISTS'
453 FROM pay_action_information pai
454 WHERE action_information_category = 'IN_24Q_CHALLAN'
455 AND action_context_type = 'PA'
456 AND action_information3 = g_gre_org_id
457 AND action_information2 = g_assess_year||g_quarter
458 AND pai.action_context_id= pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
459 AND fnd_date.canonical_to_date(pai.action_information5)<=fnd_date.CHARDATE_TO_DATE(SYSDATE)
460 )
461 )
462 ORDER BY lpad(challan_count, 20);
463
464
465 /* DEDUCTEE DETAILS RECORDS */
466
467 CURSOR c_f24q_ded_det_rec IS
468 SELECT 'PERSON_ID=P'
469 , pai.action_information2||'^'
470 , 'DEDUCTEE_PAN=P'
471 , pai.action_information10||'^'
472 , 'PAN_REF_NO=P'
473 , pai.action_information11||'^'
474 , 'EMPLOYEE_NAME=P'
475 , SUBSTR(pai.action_information12,1,75)
476 , 'INCOME_TAX_D=P'
480 , 'EDU_CESS_D=P'
477 , pay_in_24q_er_returns.get_format_value(NVL(pai.action_information6,'0'))
478 , 'SURCHARGE_D=P'
479 , pay_in_24q_er_returns.get_format_value(NVL(pai.action_information7,'0'))
481 , pay_in_24q_er_returns.get_format_value(NVL(pai.action_information8,'0'))
482 , 'TOTAL_TAX_DEPOSITED=P'
483 , pay_in_24q_er_returns.get_format_value(NVL(pai.action_information9,'0'))
484 , 'AMOUNT_PAYMENT=P'
485 , pay_in_24q_er_returns.get_format_value(NVL(pai.action_information5,'0'))
486 , 'PAYMENT_DATE=P'
487 , TO_CHAR(fnd_date.canonical_to_date( pai.action_information4),'DDMMYYYY')
488 , 'TAX_RATE=P'
489 , NVL(pai.action_information13,'0')
490 , 'DED_REC_INDEX=P'
491 , action_information25
492 FROM pay_action_information pai
493 WHERE action_information_category ='IN_24Q_DEDUCTEE'
494 AND action_context_type = 'AAP'
495 AND action_information3 =g_gre_org_id
496 AND action_information1 = pay_magtape_generic.get_parameter_value('BANK_CHALLAN_NO')
497 AND pay_in_24q_er_returns.get_format_value(NVL(pai.action_information5,'0')) <> '0.00'
498 AND EXISTS (SELECT 1
499 FROM pay_assignment_actions paa
500 WHERE paa.payroll_action_id = pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
501 AND paa.assignment_action_id = pai.action_context_id)
502 ORDER BY pai.action_information2, TO_NUMBER(action_information25) ASC;
503
504
505
506 /*SALARY DETAIL RECORDS*/
507
508 CURSOR c_f24q_salary_det_rec IS
509 SELECT 'ACTION_CONTEXT_ID=P'
510 , action_context_id
511 , 'SOURCE_ID=P'
512 , source_id
513 , 'EMP_PERSON_ID=P'
514 , action_information1
515 , 'SAL_REC_INDEX=P'
516 , action_information11
517 , 'EMP_PAN=P'
518 , action_information4
519 , 'EMP_PAN_REF=P'
520 , action_information5||'^'
521 , 'EMP_FULL_NAME=P'
522 , SUBSTR(action_information6,1,75)
523 , 'EMP_DESIGNATION=P'
524 , SUBSTR (action_information8,1,15)||'^'
525 , 'EMP_START_DATE=P'
526 , TO_CHAR(fnd_date.canonical_to_date(action_information9),'DDMMYYYY')
527 , 'EMP_END_DATE=P'
528 , TO_CHAR(fnd_date.canonical_to_date(action_information10),'DDMMYYYY')
529 , 'CHAPTER_VIA_COUNT=P'
530 , pay_in_24q_er_returns.chapter_VIA_rec_count(action_context_id,source_id)
531 , 'F16_SEC17_SAL=P'
532 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Salary Under Section 17',action_context_id,source_id,1)
533 , 'F16_PROFIT_LIEU=P'
534 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Profit in lieu of Salary',action_context_id,source_id,1)
535 , 'EXCESS_INTEREST=P'
536 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','Excess Interest Amount',action_context_id,source_id,1)
537 , 'EXCESS_PF=P'
538 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','Excess PF Amount',action_context_id,source_id,1)
539 , 'ALLOWANCE_AMOUNT=P'
540 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','Allowance Amount',action_context_id,source_id,1)
541 , 'F16_ALW_EXEM=P'
542 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Allowances Exempt',action_context_id,source_id,1)
543 , 'F16_PERQ_VALUE=P'
544 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Value of Perquisites',action_context_id,source_id,1)
545 , 'F16_GROSS_LESS_ALW=P'
546 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Gross Salary less Allowances',action_context_id,source_id,1)
547 , 'F16_DEC_SEC16=P'
548 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Deductions under Sec 16',action_context_id,source_id,1)
549 , 'F16_INC_HEAD_SAL=P'
550 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Income Chargeable Under head Salaries',action_context_id,source_id,1)
551 , 'F16_OTHER_INC=P'
552 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Other Income',action_context_id,source_id,1)
553 , 'F16_GROSS_TOT_INC=P'
554 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Gross Total Income',action_context_id,source_id,1)
555 , 'F16_TOT_INC=P'
556 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Total Income',action_context_id,source_id,1)
557 , 'F16_TAX_ON_TOT_INC=P'
558 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Tax on Total Income',action_context_id,source_id,1)
559 , 'F16_TOTAL_TAX_PAY=P'
560 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Total Tax payable',action_context_id,source_id,1)
561 , 'F16_RELIEF_89=P'
562 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Relief under Sec 89',action_context_id,source_id,1)
563 , 'F16_MARGINAL_RELIEF=P'
564 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Marginal Relief',action_context_id,source_id,1)
565 , 'F16_TOT_CHAP_VIA=P'
566 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Total Chapter VI A Deductions',action_context_id,source_id,1)
567 , 'ENT_ALW=P'
568 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Entertainment Allowance',action_context_id,source_id,1)
569 , 'EMPLOYMENT_TAX=P'
570 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Employment Tax',action_context_id,source_id,1)
571 , 'COMP_ACC_EMP_CONTRI=P'
572 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_PERQ','Employee Contribution for Company Accommodation',action_context_id,source_id,2)
573 , 'COST_RENT_FUR=P'
574 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_PERQ','Cost and Rent of Furniture',action_context_id,source_id,2)
575 , 'COMP_ACC=P'
576 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_PERQ','Company Accommodation',action_context_id,source_id,2)
577 , 'FUR_PERQ=P'
578 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_PERQ','Furniture Perquisite',action_context_id,source_id,2)
579 , 'MONTH_FUR_COST=P'
580 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_PERQ','Monthly Furniture Cost',action_context_id,source_id,2)
581 , 'DOMESTIC_PERSONAL_PERQ=P'
582 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_PERQ','Domestic and Personal Services Perquisite',action_context_id,source_id,2)
583 , 'CAR=P'
584 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_PERQ','Motor Car Perquisite',action_context_id,source_id,2)
585 , 'LTC=P'
586 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_PERQ','Leave Travel Concession',action_context_id,source_id,2)
587 , 'OTHER_PERQ=P'
588 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_PERQ','Other Perquisites',action_context_id,source_id,2)
589 , 'PERIOD=P'
590 , g_quarter
591 , 'EMP_CATEGORY=P'
592 , pay_in_24q_er_returns.get_emp_category(action_information1)||'^'
593 , 'L_SUR=P'
594 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Surcharge',action_context_id,source_id,1)
595 , 'L_CESS=P'
596 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 Education Cess',action_context_id,source_id,1)
597 , 'L_TOT_TDS=P'
598 , pay_in_24q_er_returns.get_24Q_values('IN_24Q_SALARY','F16 TDS',action_context_id,source_id,1)
599 FROM pay_action_information
600 WHERE action_information_category = 'IN_24Q_PERSON'
601 AND action_context_type = 'AAP'
602 AND action_information2 = g_assess_year||g_quarter
603 AND action_information3 = g_gre_org_id
604 AND action_context_id IN (SELECT MAX(pai.action_context_id)
605 FROM pay_action_information pai
606 ,pay_assignment_actions paa
607 ,per_assignments_f asg
608 WHERE pai.action_information_category = 'IN_24Q_PERSON'
609 AND pai.action_context_type = 'AAP'
610 AND pai.action_information1 = asg.person_id
611 AND pai.action_information2 = g_assess_year||g_quarter
612 AND pai.action_information3 = g_gre_org_id
613 AND pai.source_id = paa.assignment_action_id
614 AND pai.assignment_id = asg.assignment_id
615 AND asg.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
616 GROUP BY pai.assignment_id,pai.action_information1,pai.action_information9
617 )
618 ORDER BY fnd_number.canonical_to_number(action_information11) ASC;
619
620
621 /*CHAPTER_VIA DETAIL RECORDS*/
622
623 CURSOR c_f24q_chapter_VIA_rec IS
624 SELECT 'VIA_SECTION_ID=P'
625 ,action_information1
626 ,'GROSS_AMOUNT=P'
627 ,pay_in_24q_er_returns.get_format_value(action_information3)
628 ,'QUALIFY_AMOUNT=P'
629 ,pay_in_24q_er_returns.get_format_value(action_information2)
630 ,'PERIOD=P'
631 ,g_quarter
632 FROM pay_action_information
633 WHERE action_information_category = 'IN_24Q_VIA'
634 AND action_context_type = 'AAP'
635 AND action_context_id = pay_magtape_generic.get_parameter_value('ACTION_CONTEXT_ID')
636 AND source_id =pay_magtape_generic.get_parameter_value('SOURCE_ID')
637 AND action_information1 IS NOT NULL
638 ORDER BY LENGTH(action_information1),decode(action_information1,'80CCE',1,'80CCF',2,3),source_id;
639
640
641 END pay_in_24q_er_returns;