[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: -- --
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||'^'
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)
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
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'
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))
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))
339: , 'SAL_DET=P'
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
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:
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'
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
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'
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);
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')
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:
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:
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: