[Home] [Help]
PACKAGE: APPS.PAY_IN_24QC_ER_RETURNS
Source
1 PACKAGE pay_in_24qc_er_returns AS
2 /* $Header: pyin24cr.pkh 120.0.12000000.3 2007/03/01 05:32:08 sukukuma noship $ */
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(15) := pay_magtape_generic.get_parameter_value('QUARTER_PARAM');
10 g_action_id VARCHAR2(15) := pay_magtape_generic.get_parameter_value('REF_PARAM');
11 g_salary_detail VARCHAR2(5):= pay_magtape_generic.get_parameter_value('SAL_DET_PARAM');
12
13 --------------------------------------------------------------------------
14 -- --
15 -- Name : GET_FILE_SEQ_NO --
16 -- Type : FUNCTION --
17 -- Access : Public --
18 -- Description : This function returns the file sequence no of the --
19 -- Correction Report --
20 -- Parameters : --
21 -- IN : p_gre_org_id VARCHAR2 --
22 -- p_assess_period VARCHAR2 --
23 -- p_quarter VARCHAR2 --
24 --------------------------------------------------------------------------
25 FUNCTION get_file_seq_no (p_gre_org_id IN VARCHAR2
26 ,p_assess_year IN VARCHAR2
27 ,p_quarter IN VARCHAR2
28 )
29 RETURN VARCHAR2;
30
31 --------------------------------------------------------------------------
32 -- --
33 -- Name : CHALLAN_REC_COUNT_24QC --
34 -- Type : FUNCTION --
35 -- Access : Public --
36 -- Description : This function returns the Total number of challan --
37 -- records for a particular correction type --
38 -- Parameters : --
39 -- IN : p_gre_org_id VARCHAR2 --
40 -- p_assess_period VARCHAR2 --
41 -- p_max_action_id VARCHAR2 --
42 -- p_correction_type VARCHAR2 --
43 --------------------------------------------------------------------------
44 FUNCTION challan_rec_count_24qc (p_gre_org_id IN VARCHAR2
45 ,p_assess_period IN VARCHAR2
46 ,p_max_action_id IN VARCHAR2
47 ,p_correction_type IN VARCHAR2)
48 RETURN VARCHAR2;
49
50 --------------------------------------------------------------------------
51 -- --
52 -- Name : GET_RRR_NO --
53 -- Type : FUNCTION --
54 -- Access : Public --
55 -- Description : This function returns the Original/Last --
56 -- 24Q Receipt Number --
57 -- Parameters : --
58 -- IN : p_gre_org_id VARCHAR2 --
59 -- p_assess_period VARCHAR2 --
60 -- p_quarter VARCHAR2 --
61 -- p_receipt VARCHAR2 --
62 --------------------------------------------------------------------------
63 FUNCTION get_rrr_no (p_gre_org_id IN VARCHAR2
64 ,p_assess_year IN VARCHAR2
65 ,p_quarter IN VARCHAR2
66 ,p_receipt IN VARCHAR2)
67 RETURN VARCHAR2;
68
69
70 --------------------------------------------------------------------------
71 -- --
72 -- Name : TOTAL_GROSS_TOT_INCOME --
73 -- Type : FUNCTION --
74 -- Access : Public --
75 -- Description : This function returns the total of Gross Total --
76 -- Income as per salary details annexure --
77 -- Parameters : --
78 -- IN : p_gre_org_id VARCHAR2 --
79 -- p_assess_period VARCHAR2 --
80 -- p_correction_type VARCHAR2 --
81 -- p_max_action_id VARCHAR2 --
82 --------------------------------------------------------------------------
83 FUNCTION total_gross_tot_income (p_gre_org_id IN VARCHAR2
84 ,p_assess_period IN VARCHAR2
85 ,p_correction_type IN VARCHAR2
86 ,p_max_action_id IN VARCHAR2)
87 RETURN VARCHAR2;
88
89 --------------------------------------------------------------------------
90 -- --
91 -- Name : SALARY_REC_COUNT --
92 -- Type : FUNCTION --
93 -- Access : Public --
94 -- Description : This function returns the Total number of records --
95 -- in the Salary Details of the Magtape --
96 -- Parameters : --
97 -- IN : p_gre_org_id VARCHAR2 --
98 -- p_assess_period VARCHAR2 --
99 -- p_correction_type VARCHAR2 --
100 -- p_max_action_id VARCHAR2 --
101 --------------------------------------------------------------------------
102 FUNCTION salary_rec_count (p_gre_org_id IN VARCHAR2
103 ,p_assess_period IN VARCHAR2
104 ,p_correction_type IN VARCHAR2
105 ,p_max_action_id IN VARCHAR2)
106 RETURN VARCHAR2;
107
108
109 --------------------------------------------------------------------------
110 -- --
111 -- Name : CHAPTER_VIA_REC_COUNT --
112 -- Type : FUNCTION --
113 -- Access : Public --
114 -- Description : This function returns the Total number of records --
115 -- in the Chapter-VIA Details of the Magtape --
116 -- Parameters : --
117 -- IN : p_action_context_id VARCHAR2 --
118 -- p_source_id VARCHAR2 --
119 --------------------------------------------------------------------------
120 FUNCTION chapter_VIA_rec_count (p_action_context_id IN VARCHAR2
121 ,p_source_id IN VARCHAR2)
122 RETURN VARCHAR2;
123
124 --------------------------------------------------------------------------
125 -- --
126 -- Name : GROSS_TOT_TDS_CHALLAN_24Q --
127 -- Type : FUNCTION --
128 -- Access : Public --
129 -- Description : This function returns the Gross Total of TDS --
130 -- --
131 -- Parameters : --
132 -- IN : p_gre_org_id VARCHAR2 --
133 -- p_assess_period VARCHAR2 --
134 -- p_max_action_id VARCHAR2 --
135 -- p_correction_type VARCHAR2 --
136 --------------------------------------------------------------------------
137 FUNCTION gross_tot_tds_challan_24q(p_gre_org_id IN VARCHAR2
138 ,p_assess_period IN VARCHAR2
139 ,p_max_action_id IN VARCHAR2
140 ,p_correction_type IN VARCHAR2)
141 RETURN VARCHAR2;
142
143 --------------------------------------------------------------------------
144 -- --
145 -- Name : GET_PREV_NIL_CHALLAN_IND --
146 -- Type : FUNCTION --
147 -- Access : Public --
148 -- Description : This function returns the last NIL Challan Indicator--
149 -- Parameters : --
150 -- IN : p_gre_org_id VARCHAR2 --
151 -- p_assess_period VARCHAR2 --
152 -- p_max_action_id VARCHAR2 --
153 -- p_nil_challan VARCHAR2 --
154 --------------------------------------------------------------------------
155 FUNCTION get_prev_nil_challan_ind (p_gre_org_id IN VARCHAR2
156 ,p_assess_period IN VARCHAR2
157 ,p_max_action_id IN VARCHAR2)
158 RETURN VARCHAR2;
159
160 --------------------------------------------------------------------------
161 -- --
162 -- Name : DEDUCTEE_REC_COUNT_24Q --
163 -- Type : FUNCTION --
164 -- Access : Public --
165 -- Description : This function returns the count of deductee records --
166 -- for a challan in a correction archival --
167 -- Parameters : --
168 -- IN : p_gre_org_id VARCHAR2 --
169 -- p_max_action_id VARCHAR2 --
170 -- p_challan VARCHAR2 --
171 --------------------------------------------------------------------------
172 FUNCTION deductee_rec_count_24q (p_gre_org_id IN VARCHAR2
173 ,p_max_action_id IN VARCHAR2
174 ,p_challan IN VARCHAR2)
175 RETURN VARCHAR2 ;
176
177 --------------------------------------------------------------------------
178 -- --
179 -- Name : GET_24QC_TAX_VALUES --
180 -- Type : FUNCTION --
181 -- Access : Public --
182 -- Description : This function returns the Tax Values String --
183 -- Parameters : --
184 -- IN : p_challan_number VARCHAR2 --
185 -- p_gre_org_id VARCHAR2 --
186 -- p_max_action_id VARCHAR2 --
187 --------------------------------------------------------------------------
188 FUNCTION get_24QC_tax_values(p_challan_number IN VARCHAR2
189 ,p_gre_org_id IN VARCHAR2
190 ,p_max_action_id IN VARCHAR2
191 )
192 RETURN VARCHAR2;
193
194 --------------------------------------------------------------------------
195 -- --
196 -- Name : GET_ARCHIVE_PAY_ACTION --
197 -- Type : FUNCTION --
198 -- Access : Public --
199 -- Description : This function returns the latest archival payroll --
200 -- action id for a period --
201 -- Parameters : --
202 -- IN : p_gre_org_id VARCHAR2 --
203 -- p_period VARCHAR2 --
204 --------------------------------------------------------------------------
205 FUNCTION get_archive_pay_action (p_gre_org_id IN VARCHAR2
206 ,p_period IN VARCHAR2)
207 RETURN NUMBER;
208
209 --------------------------------------------------------------------------
210 -- --
211 -- Name : REMOVE_CURR_FORMAT --
212 -- Type : FUNCTION --
213 -- Access : Public --
214 -- Description : This function returns the latest archival payroll --
215 -- action id for a period --
216 -- Parameters : --
217 -- IN : p_value VARCHAR2 --
218 -- --
219 --------------------------------------------------------------------------
220 FUNCTION remove_curr_format (p_value IN VARCHAR2)
221 RETURN VARCHAR2;
222
223 /* FILE HEADER RECORD */
224
225 CURSOR c_f24qc_file_header IS
226 SELECT 'TAN_OF_DED=P'
227 , pai.action_information2
228 ,'MAX_ACTION_CONTEXT_ID=P'
229 , pai.action_context_id
230 ,'SUBMIT_DATE=P'
231 , TO_CHAR (SYSDATE,'DDMMYYYY')
232 ,'FILE_SEQ_NO=P'
233 , pay_in_24qc_er_returns.get_file_seq_no(g_gre_org_id, g_assess_year, g_quarter)
234 , 'CHECK_C4=P'
235 , NVL(pai.action_information29,'N')
236 ,'TOTAL_BATCH_NO=P'
237 , DECODE(LENGTH(pai.action_information29), 1, 1, 2, 1, 5, 2, 8, 3, 11, 4, 14, 5, 17,6,1)
238 FROM pay_action_information pai
239 , pay_payroll_actions ppa
240 WHERE pai.action_information_category = 'IN_24QC_ORG'
241 AND pai.action_context_type = 'PA'
242 AND pai.action_information1 = g_gre_org_id
243 AND pai.action_information3 = g_assess_year||g_quarter
244 AND ppa.action_type = 'X'
245 AND ppa.action_status = 'C'
246 AND ppa.report_type = 'IN_24QC_ARCHIVE'
247 AND ppa.report_qualifier = 'IN'
248 AND ppa.payroll_action_id = pai.action_context_id
249 AND pai.action_context_id = NVL(g_action_id,
250 pay_in_24qc_er_returns.get_archive_pay_action(g_gre_org_id
251 , (g_assess_year||g_quarter)))
252 AND ROWNUM = 1;
253
254
255
256 /* BATCH HEADER RECORD*/
257 CURSOR c_f24qc_batch_header IS
258 SELECT DISTINCT 'TOT_CHALLAN_REC=P'
259 , pay_in_24qc_er_returns.challan_rec_count_24qc
260 (g_gre_org_id
261 ,(g_assess_year||g_quarter)
262 , pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
263 , hll.lookup_code)
264 , 'TAN_OF_DED=P'
265 , pai.action_information2
266 , 'SAL_DET=P'
267 , g_salary_detail
268 , 'PAN_OF_TAN=P'
269 , pai.action_information4
270 , 'ASSESS_YEAR=P'
271 , SUBSTR(g_assess_year,1,4)||SUBSTR(g_assess_year,8,2)
272 , 'FIN_YEAR=P'
273 , SUBSTR(g_assess_year,1,4)-1||LPAD(SUBSTR(g_assess_year,8,2)-1,2,'0')
274 , 'PERIOD=P'
275 , g_quarter
279 , SUBSTR(pai.action_information16,1,75)
276 , 'LEGAL_NAME=P'
277 , SUBSTR(pai.action_information5,1,75)
278 , 'LAST_LEGAL_NAME=P'
280 , 'EMPLOYER_DIV=P'
281 , SUBSTR(pai.action_information8,1,75)||'^'
282 , 'EMPLOYER_ADD1=P'
283 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,1,'EMP')
284 , 'EMPLOYER_ADD2=P'
285 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,2,'EMP')
286 , 'EMPLOYER_ADD3=P'
287 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,3,'EMP')
288 , 'EMPLOYER_ADD4=P'
289 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,4,'EMP')
290 , 'EMPLOYER_ADD5=P'
291 , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,5,'EMP')
292 , 'EMP_ADD_CHG=P'
293 , pai.action_information18
294 , 'REP_ADD_CHG=P'
295 , pai.action_information19
296 , 'EMPLOYER_TYPE=P'
297 , action_information7
298 , 'LAST_EMPLOYER_TYPE=P'
299 , action_information17
300 , 'REP_NAME=P'
301 , SUBSTR(pai.action_information9,1,75)
302 , 'REP_DESIG=P'
303 , SUBSTR(pai.action_information11,1,75)||'^'
304 , 'REP_ADD1=P'
305 , pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,1,'REP')
306 , 'REP_ADD2=P'
307 , pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,2,'REP')
308 , 'REP_ADD3=P'
309 , pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,3,'REP')
310 , 'REP_ADD4=P'
311 , pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,4,'REP')
312 , 'REP_ADD5=P'
313 , pay_in_24q_er_returns.get_location_details(pai.action_information12,pai.action_information10,pai.action_information13,5,'REP')
314 , 'ORIG_RRR=P'
315 , pay_in_24qc_er_returns.get_rrr_no(g_gre_org_id, g_assess_year, g_quarter, 'Original')
316 , 'PREV_RRR=P'
317 , pay_in_24qc_er_returns.get_rrr_no(g_gre_org_id, g_assess_year, g_quarter, 'Previous')
318 , 'BATCH_UPD_IND=P'
319 , pai.action_information15
320 , 'CORRECTION_TYPE=P'
321 , hll.lookup_code
322 , 'GROSS_TDS_CHALLAN=P'
323 , pay_in_24qc_er_returns.gross_tot_tds_challan_24q
324 (g_gre_org_id
325 , (g_assess_year||g_quarter)
326 , pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
327 , hll.lookup_code)
328 , 'COUNT_SALARY=P'
329 , pay_in_24qc_er_returns.salary_rec_count(g_gre_org_id
330 ,g_assess_year||g_quarter
331 , hll.lookup_code
332 ,pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID'))
333 , 'GROSS_TOTAL_INCOME=P'
334 , pay_in_24qc_er_returns.total_gross_tot_income(g_gre_org_id
335 , g_assess_year||g_quarter
336 , hll.lookup_code
337 ,pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID'))
338 FROM pay_action_information pai
339 ,hr_leg_lookups hll
340 WHERE pai.action_information_category = 'IN_24QC_ORG'
341 AND pai.action_context_type = 'PA'
342 AND pai.action_information1 = g_gre_org_id
343 AND pai.action_information3 = g_assess_year||g_quarter
344 AND pai.action_context_id = pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
345 and hll.lookup_type = 'IN_FORM_24Q_CORRECTION_TYPES'
346 and INSTR(pai.action_information29,hll.lookup_code) <> 0;
347
348
349
350 /* CHALLAN DETAIL RECORD */
351 CURSOR c_f24qc_challan_det_rec IS
352 SELECT DISTINCT 'TOT_DEDUCTEE_REC=P'
353 , pay_in_24qc_er_returns.deductee_rec_count_24q(g_gre_org_id
354 ,pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
355 ,pai.action_information1)
356 , 'NIL_CHALLAN_IND=P'
357 , pay_in_24qc_er_returns.get_prev_nil_challan_ind(g_gre_org_id
358 , (g_assess_year||g_quarter)
359 , pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID'))
360 , 'QUARTER_LAST_DATE=P'
361 , DECODE(g_quarter,'Q1','3006','Q2','3009','Q3','3112','Q4','3103')
362 ||DECODE(g_quarter,'Q4',
363 SUBSTR(g_assess_year,1,4)
364 ,SUBSTR(g_assess_year,1,4)-1)
365 , 'BANK_CHALLAN_NO=P'
366 , pai.action_information1
367 , 'LAST_BANK_CHALLAN_NO=P'
368 , pai.action_information12
369 , 'BANK_BRANCH_CODE=P'
370 , SUBSTR(pai.action_information4,1,7)||'^'
371 , 'LAST_BANK_BRANCH_CODE=P'
372 , SUBSTR(pai.action_information13,1,7)||'^'
373 , 'CHALLAN_DATE=P'
374 , TO_CHAR(fnd_date.canonical_to_date(pai.action_information5),'DDMMYYYY')
375 , 'LAST_CHALLAN_DATE=P'
376 , TO_CHAR(fnd_date.canonical_to_date(pai.action_information14),'DDMMYYYY')
377 , 'TDS_DEP=P'
378 , NVL(pai.action_information6,'0')||'.00'
379 , 'SURCHARGE=P'
380 , NVL(pai.action_information7,'0')||'.00'
381 , 'EDU_CESS=P'
382 , NVL(pai.action_information8,'0')||'.00'
383 , 'INTEREST=P'
384 , NVL(pai.action_information9,'0')||'.00'
385 , 'OTHERS=P'
386 , NVL(pai.action_information10,'0')||'.00'
387 , 'LAST_TOTAL_AMOUNT_DEPO=P'
388 , NVL(pai.action_information19,'0.00')
389 , 'TAX_VALUES=P'
390 , pay_in_24qc_er_returns.get_24qc_tax_values(pai.action_information1,g_gre_org_id
391 ,pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID'))
392 , 'CHQ_DD_NUM=P'
393 , SUBSTR(pai.action_information11,1,15)||'^'
394 , 'BOOK_ENTRY=P'
395 , pai.action_information16
396 , 'CHALLAN_UPD_IND=P'
397 , DECODE(pai.action_information18, 'U', '1', '0')
398 , 'CHALLAN_REC_INDEX=P'
399 , pai.action_information25
400 FROM pay_action_information pai
401 WHERE action_information_category = 'IN_24QC_CHALLAN'
402 AND action_context_type = 'PA'
403 AND action_information3 = g_gre_org_id
404 AND action_information2 = g_assess_year||g_quarter
405 AND pai.action_context_id = pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
406 AND pai.action_information29 like '%'|| pay_magtape_generic.get_parameter_value('CORRECTION_TYPE')||'%'
407 AND fnd_date.canonical_to_date(pai.action_information5)<=fnd_date.CHARDATE_TO_DATE(SYSDATE)
408 ORDER BY 40;
409
410
411
412 /* DEDUCTEE DETAILS RECORDS */
413
414 CURSOR c_f24qc_ded_det_rec IS
415 SELECT DISTINCT 'PERSON_ID=P'
416 , pai.action_information12
417 , 'LAST_DEDUCTEE_PAN=P'
418 , pai.action_information13
419 , 'DEDUCTEE_PAN=P'
420 , pai.action_information9
421 , 'LAST_PAN_REF_NO=P'
422 , pai.action_information14
423 , 'PAN_REF_NO=P'
424 , pai.action_information11
425 , 'EMPLOYEE_NAME=P'
426 , SUBSTR(pai.action_information10,1,75)
427 , 'INCOME_TAX_D=P'
428 , pay_in_24q_er_returns.get_format_value(NVL(pay_in_24qc_er_returns.remove_curr_format(pai.action_information6),'0'))
429 , 'SURCHARGE_D=P'
430 , pay_in_24q_er_returns.get_format_value(NVL(pay_in_24qc_er_returns.remove_curr_format(pai.action_information7),'0'))
431 , 'EDU_CESS_D=P'
432 , pay_in_24q_er_returns.get_format_value(NVL(pay_in_24qc_er_returns.remove_curr_format(pai.action_information8),'0'))
433 , 'LAST_TOTAL_TDS=P'
434 , pay_in_24q_er_returns.get_format_value(NVL(pay_in_24qc_er_returns.remove_curr_format(pai.action_information17),'0'))
435 , 'LAST_TOTAL_TAX_DEPOSITED=P'
436 , pay_in_24q_er_returns.get_format_value(NVL(pay_in_24qc_er_returns.remove_curr_format(pai.action_information24),'0'))
440 , pay_in_24q_er_returns.get_format_value(NVL(pay_in_24qc_er_returns.remove_curr_format(pai.action_information5),'0'))
437 , 'TOTAL_TAX_DEPOSITED=P'
438 , pay_in_24q_er_returns.get_format_value(NVL(pay_in_24qc_er_returns.remove_curr_format(pai.action_information16),'0'))
439 , 'AMOUNT_PAYMENT=P'
441 , 'PAYMENT_DATE=P'
442 , TO_CHAR(fnd_date.canonical_to_date( pai.action_information4),'DDMMYYYY')
443 , 'TAX_RATE=P'
444 , DECODE(pai.action_information18,'L', 'A^', 'N', 'B^', '^')
445 , 'DED_REC_INDEX=P'
446 , pai.action_information25
447 , 'UPD_MODE=P'
448 , pai.action_information15
449 FROM pay_action_information pai
450 WHERE action_information_category ='IN_24QC_DEDUCTEE'
451 AND action_context_type = 'AAP'
452 AND action_information3 = g_gre_org_id
453 AND action_information1 = pay_magtape_generic.get_parameter_value('BANK_CHALLAN_NO')
454 AND ((pay_magtape_generic.get_parameter_value('CORRECTION_TYPE') = 'C5'
455 AND INSTR(pai.action_information19, 'C5') <> 0)
456 OR
457 pay_magtape_generic.get_parameter_value('CORRECTION_TYPE') <> 'C5'
458 )
459 AND ((pay_magtape_generic.get_parameter_value('CORRECTION_TYPE') = 'C3'
460 and(( INSTR(pai.action_information19, 'C3') <> 0) or action_information19 is null)
461 OR
462 pay_magtape_generic.get_parameter_value('CORRECTION_TYPE') <> 'C3'
463 ))
467 AND paa.assignment_action_id = pai.action_context_id)
464 AND EXISTS (SELECT 1
465 FROM pay_assignment_actions paa
466 WHERE paa.payroll_action_id = pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
468 ORDER BY pai.action_information12, TO_NUMBER(action_information25) ASC;
469
470
471
472 CURSOR c_f24qc_salary_det_rec IS
473 SELECT DISTINCT 'ASSIGNMENT_ID=P'
474 , assignment_id
475 ,'ACTION_CONTEXT_ID=P'
476 , action_context_id
477 , 'SOURCE_ID=P'
478 , source_id
479 , 'EMP_PERSON_ID=P'
480 , action_information1
481 , 'MODE_TYPE=P' --added
482 , action_information10
483 , 'SAL_REC_INDEX=P' --added
484 , action_information12
485 , 'EMP_PAN=P'
486 , action_information4
487 , 'EMP_PAN_REF=P'
488 , action_information5||'^'
489 , 'EMP_FULL_NAME=P'
490 , SUBSTR(action_information6,1,75)
491 , 'EMP_CATEGORY=P'
492 , action_information7
493 , 'EMP_START_DATE=P'
494 , TO_CHAR(fnd_date.canonical_to_date(action_information8),'DDMMYYYY')
495 , 'EMP_END_DATE=P'
496 , TO_CHAR(fnd_date.canonical_to_date(action_information9),'DDMMYYYY')
497 , 'CHAPTER_VIA_COUNT=P'
498 , pay_in_24qc_er_returns.chapter_VIA_rec_count(action_context_id,source_id)
499 , 'F16_SEC17_SAL=P'
500 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Salary Under Section 17',action_context_id,source_id,1)
501 , 'F16_PROFIT_LIEU=P'
502 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Profit in lieu of Salary',action_context_id,source_id,1)
503 , 'F16_OTHER_INC=P'
504 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Other Income',action_context_id,source_id,1)
505 , 'F16_GROSS_TOT_INC=P'
506 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Gross Total Income',action_context_id,source_id,1)
507 , 'PREV_F16_GROSS_TOT_INC=P'
508 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','Prev F16 Gross Total Income',action_context_id,source_id,1) --added
509 , 'F16_GROSS_TOT_INC_24Q=P'
510 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','Form24Q F16 Gross Total Income',action_context_id,source_id,1) --added
511 , 'F16_TOT_CHAP_VIA=P'
512 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Total Chapter VI A Deductions',action_context_id,source_id,1)
513 , 'L_SUR=P'
514 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Surcharge',action_context_id,source_id,1)
515 , 'L_CESS=P'
516 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Education Cess',action_context_id,source_id,1)
517 , 'L_TOT_TDS=P'
518 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 TDS',action_context_id,source_id,1)
519 , 'F16_TOTAL_TAX_PAY=P'
520 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Total Tax payable',action_context_id,source_id,1)
521 , 'F16_RELIEF_89=P'
522 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Relief under Sec 89',action_context_id,source_id,1)
523 , 'F16_MARGINAL_RELIEF=P'
524 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Marginal Relief',action_context_id,source_id,1)
525 , 'ENT_ALW=P'
526 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Entertainment Allowance',action_context_id,source_id,1)
527 , 'EMPLOYMENT_TAX=P'
528 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Employment Tax',action_context_id,source_id,1)
529 , 'F16_DEC_SEC16=P'
530 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Deductions under Sec 16',action_context_id,source_id,1)
531 , 'F16_INC_HEAD_SAL=P'
532 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Income Chargeable Under head Salaries',action_context_id,source_id,1)
533 , 'F16_TOT_INC=P'
534 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Total Income',action_context_id,source_id,1)
535 , 'F16_TAX_ON_TOT_INC=P'
536 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Tax on Total Income',action_context_id,source_id,1)
537 ,'F16_ALW_EXEM=P'
538 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Allowances Exempt',action_context_id,source_id,1)
539 , 'F16_PERQ_VALUE=P'
540 , pay_in_24q_er_returns.get_24Q_values('IN_24QC_SALARY','F16 Value of Perquisites',action_context_id,source_id,1)
541 , 'PERIOD=P'
542 , g_quarter
543 FROM pay_action_information
544 WHERE action_information_category = 'IN_24QC_PERSON'
545 AND action_context_type = 'AAP'
546 AND action_information2 = g_assess_year||g_quarter
547 AND action_information3 = g_gre_org_id
548 AND action_information11=pay_magtape_generic.get_parameter_value('CORRECTION_TYPE')
549 AND action_context_id IN (SELECT MAX(pai.action_context_id)
550 FROM pay_action_information pai
551 ,pay_assignment_actions paa
552 ,per_assignments_f asg
553 WHERE paa.payroll_action_id = pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
554 AND pai.action_context_id = paa.assignment_action_id
555 AND pai.action_information_category = 'IN_24QC_PERSON'
556 AND pai.action_context_type = 'AAP'
557 AND pai.action_information1 = asg.person_id
558 AND pai.action_information2 = g_assess_year||g_quarter
559 AND pai.action_information3 = g_gre_org_id
560 AND pai.assignment_id = asg.assignment_id
564 )
561 AND asg.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
562 AND pai.action_information11=pay_magtape_generic.get_parameter_value('CORRECTION_TYPE')
563 GROUP BY pai.assignment_id,pai.action_information1,pai.action_information9
565 ORDER BY fnd_number.canonical_to_number(action_information1),assignment_id,fnd_number.canonical_to_number(action_information12) ASC;
566
567
568 /*CHAPTER_VIA DETAIL RECORDS*/
569
570 CURSOR c_f24qc_chapter_VIA_rec IS
571 SELECT DISTINCT 'SOURCE_ID=P'
572 ,source_id
573 ,'VIA_SECTION_ID=P'
574 ,action_information1
575 ,'QUALIFY_AMOUNT=P'
576 ,pay_in_24q_er_returns.get_format_value(action_information2)
577 FROM pay_action_information
578 WHERE action_information_category = 'IN_24QC_VIA'
579 AND action_context_type = 'AAP'
580 AND action_context_id = pay_magtape_generic.get_parameter_value('ACTION_CONTEXT_ID')
581 AND source_id =pay_magtape_generic.get_parameter_value('SOURCE_ID')
582 AND action_information1 IS NOT NULL
583 ORDER BY LENGTH(action_information1),source_id;
584
585
586 END pay_in_24qc_er_returns;