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