[Home] [Help]
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:
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: --------------------------------------------------------------------------
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: -- --
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||'^'
291: , pay_in_24q_er_returns.get_location_details(pai.action_information6,pai.action_information10,pai.action_information13,4,'EMP')
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'
295: , pay_magtape_generic.get_parameter_value('EMP_ADD_CHG')
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)
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
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))
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
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'
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
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'
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:
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')
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:
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:
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: