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