PAY Generate Employer Quarterly Tax Returns (India) - Excel
Description
- Categories
- BI Publisher
- Application
- Payroll
- Source
- Generate Employer Quarterly Tax Returns (India) - Excel
- Short Name
- PAYINF24Q
DB package:
SQL 488 lines
SELECT action_context_id SD_ACTION_CONTEXT_ID ,:BH_BATCH_NUM SD_BH_BATCH_NUM ,source_id SD_SOURCE_ID ,emp_person_id SD_EMP_PERSON_ID ,sal_rec_index SD_SAL_REC_INDEX ,emp_pan SD_EMP_PAN ,emp_pan_ref SD_EMP_PAN_REF ,emp_full_name SD_EMP_FULL_NAME ,emp_designation SD_EMP_DESIGNATION ,emp_category SD_EMP_CATEGORY ,DECODE(emp_category,'W','Women','S','Senior Citizen','O','Super Senior Citizen','Others') SD_REP_EMP_CATEGORY ,to_char (emp_start_date ,'DDMMYYYY') SD_EMP_START_DATE ,fnd_date.date_to_displaydate (emp_start_date) SD_REP_EMP_START_DATE ,to_char (emp_end_date ,'DDMMYYYY') SD_EMP_END_DATE ,fnd_date.date_to_displaydate (emp_end_date) SD_REP_EMP_END_DATE ,chapter_via_count sd_chapter_via_count ,f16_sec17_sal + f16_profit_lieu - f16_alw_exem + l_prev_earnings SD_335 ,(CASE WHEN ent_alw <> 0 THEN CASE WHEN employment_tax <> 0 THEN 2 ELSE 1 END ELSE CASE WHEN employment_tax <> 0 THEN 1 ELSE 0 END END) SD_SECTION_16_COUNT ,f16_dec_sec16 SD_336_337 ,f16_inc_head_sal + l_prev_earnings SD_338 ,f16_other_inc SD_339 ,f16_gross_tot_inc + l_prev_earnings SD_340 ,chapter_via_count SD_CHAPTER_VIA_COUNT ,f16_tot_chap_via SD_F16_TOT_CHAP_VIA ,f16_tot_inc + l_prev_earnings SD_344 ,f16_tax_on_tot_inc - f16_marginal_relief SD_345 ,l_sur SD_SURCHARGE ,l_cess SD_346 ,f16_relief_89 SD_347 ,f16_total_tax_pay SD_348 ,CASE WHEN nvl(l_fund_tax_paid,0) <> 0 THEN l_tot_tds + l_prev_sur + l_prev_tds + l_prev_cess + l_prev_he_cess + l_fund_tax_paid ELSE l_tot_tds + l_prev_sur + l_prev_tds + l_prev_cess + l_prev_he_cess END SD_351 ,CASE WHEN nvl(l_fund_tax_paid,0) <> 0 THEN f16_total_tax_pay - l_tot_tds - l_fund_tax_paid ELSE f16_total_tax_pay - l_tot_tds END SD_352 ,f16_sec17_sal + f16_profit_lieu - f16_alw_exem + f16_perq_value SD_333 ,l_prev_earnings SD_334 ,l_tot_tds SD_349 ,l_prev_sur + l_prev_tds + l_prev_cess + l_prev_he_cess SD_353 ,decode (emp_pan ,'PANNOTAVBL' ,'Y' ,'N') SD_354 ,hr_general.decode_lookup('YES_NO',decode (emp_pan,'PANNOTAVBL','Y','N')) SD_REP_354 ,l_annual_rent SD_357 ,hr_general.decode_lookup('YES_NO',l_annual_rent) SD_REP_357 ,l_landlord_pan_count SD_LANDLORD_PAN_COUNT ,l_landlord_pan1 SD_LANDLORD_PAN1 ,l_landlord_name1 SD_LANDLORD_NAME1 ,l_landlord_pan2 SD_LANDLORD_PAN2 ,l_landlord_name2 SD_LANDLORD_NAME2 ,l_landlord_pan3 SD_LANDLORD_PAN3 ,l_landlord_name3 SD_LANDLORD_NAME3 ,l_landlord_pan4 SD_LANDLORD_PAN4 ,l_landlord_name4 SD_LANDLORD_NAME4 ,l_lender_interest SD_LENDER_INTEREST ,hr_general.decode_lookup('YES_NO',l_lender_interest) SD_REP_LENDER_INTEREST ,l_lender_pan_count SD_LENDER_PAN_COUNT ,l_lender_pan1 SD_LENDER_PAN1 ,l_lender_name1 SD_LENDER_NAME1 ,l_lender_pan2 SD_LENDER_PAN2 ,l_lender_name2 SD_LENDER_NAME2 ,l_lender_pan3 SD_LENDER_PAN3 ,l_lender_name3 SD_LENDER_NAME3 ,l_lender_pan4 SD_LENDER_PAN4 ,l_lender_name4 SD_LENDER_NAME4 ,l_super_annuation SD_SUPER_ANNUATION ,hr_general.decode_lookup('YES_NO',l_super_annuation) SD_REP_SUPER_ANNUATION ,l_fund_name SD_FUND_NAME ,l_fund_start SD_FUND_START ,l_fund_end SD_FUND_END ,l_fund_repaid SD_FUND_REPAID ,l_fund_tax_rate SD_FUND_TAX_RATE ,l_fund_tax_paid SD_FUND_TAX_PAID ,CASE WHEN l_super_annuation = 'Y' THEN to_number(f16_gross_tot_inc) + to_number(l_prev_earnings) + to_number(l_fund_repaid) ELSE to_number(l_fund_repaid) END SD_INCOME_WITH_FUND ,EMPLOYMENT_TAX SD_EMPLOYMENT_TAX ,ENT_ALW SD_ENT_ALW FROM ( SELECT action_context_id action_context_id ,source_id source_id ,action_information1 emp_person_id ,action_information11 sal_rec_index ,action_information4 emp_pan ,action_information5 emp_pan_ref ,substr (action_information6 ,1 ,75) emp_full_name ,substr (action_information8 ,1 ,15) emp_designation ,fnd_date.canonical_to_date (action_information9) emp_start_date ,fnd_date.canonical_to_date (action_information10) emp_end_date ,pay_in_24q_er_returns.chapter_via_rec_count (action_context_id ,source_id) chapter_via_count ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Salary Under Section 17' ,action_context_id ,source_id ,1) f16_sec17_sal ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Profit in lieu of Salary' ,action_context_id ,source_id ,1) f16_profit_lieu ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Excess Interest Amount' ,action_context_id ,source_id ,1) excess_interest ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Excess PF Amount' ,action_context_id ,source_id ,1) excess_pf ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Allowance Amount' ,action_context_id ,source_id ,1) allowance_amount ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Allowances Exempt' ,action_context_id ,source_id ,1) f16_alw_exem ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Value of Perquisites' ,action_context_id ,source_id ,1) f16_perq_value ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Gross Salary less Allowances' ,action_context_id ,source_id ,1) f16_gross_less_alw ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Deductions under Sec 16' ,action_context_id ,source_id ,1) f16_dec_sec16 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Income Chargeable Under head Salaries' ,action_context_id ,source_id ,1) f16_inc_head_sal ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Other Income' ,action_context_id ,source_id ,1) f16_other_inc ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Gross Total Income' ,action_context_id ,source_id ,1) f16_gross_tot_inc ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Total Income' ,action_context_id ,source_id ,1) f16_tot_inc ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Tax on Total Income' ,action_context_id ,source_id ,1) f16_tax_on_tot_inc ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Total Tax payable' ,action_context_id ,source_id ,1) f16_total_tax_pay ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Relief under Sec 89' ,action_context_id ,source_id ,1) f16_relief_89 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Marginal Relief' ,action_context_id ,source_id ,1) f16_marginal_relief ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Total Chapter VI A Deductions' ,action_context_id ,source_id ,1) f16_tot_chap_via ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Entertainment Allowance' ,action_context_id ,source_id ,1) ent_alw ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Employment Tax' ,action_context_id ,source_id ,1) employment_tax ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_PERQ' ,'Employee Contribution for Company Accommodation' ,action_context_id ,source_id ,2) comp_acc_emp_contri ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_PERQ' ,'Cost and Rent of Furniture' ,action_context_id ,source_id ,2) cost_rent_fur ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_PERQ' ,'Company Accommodation' ,action_context_id ,source_id ,2) comp_acc ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_PERQ' ,'Furniture Perquisite' ,action_context_id ,source_id ,2) fur_perq ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_PERQ' ,'Monthly Furniture Cost' ,action_context_id ,source_id ,2) month_fur_cost ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_PERQ' ,'Domestic and Personal Services Perquisite' ,action_context_id ,source_id ,2) domestic_personal_perq ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_PERQ' ,'Motor Car Perquisite' ,action_context_id ,source_id ,2) car ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_PERQ' ,'Leave Travel Concession' ,action_context_id ,source_id ,2) ltc ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_PERQ' ,'Other Perquisites' ,action_context_id ,source_id ,2) other_perq ,:P_QUARTER period5 ,pay_in_24q_er_returns.get_emp_category (assignment_id ,fnd_date.canonical_to_date (action_information10)) emp_category ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Surcharge' ,action_context_id ,source_id ,1) l_sur ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 Education Cess' ,action_context_id ,source_id ,1) l_cess ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'F16 TDS' ,action_context_id ,source_id ,1) l_tot_tds ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'CESS on Previous Employment' ,action_context_id ,source_id ,1) l_prev_cess ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Sec and HE Cess on Previous Employment' ,action_context_id ,source_id ,1) l_prev_he_cess ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'SC on Previous Employment' ,action_context_id ,source_id ,1) l_prev_sur ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Previous Employment Earnings' ,action_context_id ,source_id ,1) l_prev_earnings ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'TDS on Previous Employment' ,action_context_id ,source_id ,1) l_prev_tds ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Aggregate Rent' ,action_context_id ,source_id ,1 ,NULL) l_annual_rent ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lanlord PAN Count' ,action_context_id ,source_id ,1 ,NULL) l_landlord_pan_count ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lanlord 1' ,action_context_id ,source_id ,1 ,1) l_landlord_pan1 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lanlord 1' ,action_context_id ,source_id ,2 ,1) l_landlord_name1 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lanlord 2' ,action_context_id ,source_id ,1 ,2) l_landlord_pan2 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lanlord 2' ,action_context_id ,source_id ,2 ,2) l_landlord_name2 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lanlord 3' ,action_context_id ,source_id ,1 ,3) l_landlord_pan3 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lanlord 3' ,action_context_id ,source_id ,2 ,3) l_landlord_name3 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lanlord 4' ,action_context_id ,source_id ,1 ,4) l_landlord_pan4 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lanlord 4' ,action_context_id ,source_id ,2 ,4) l_landlord_name4 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Interest paid' ,action_context_id ,source_id ,1 ,NULL) l_lender_interest ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lender PAN Count' ,action_context_id ,source_id ,1 ,NULL) l_lender_pan_count ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lender 1' ,action_context_id ,source_id ,1 ,1) l_lender_pan1 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lender 1' ,action_context_id ,source_id ,2 ,1) l_lender_name1 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lender 2' ,action_context_id ,source_id ,1 ,2) l_lender_pan2 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lender 2' ,action_context_id ,source_id ,2 ,2) l_lender_name2 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lender 3' ,action_context_id ,source_id ,1 ,3) l_lender_pan3 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lender 3' ,action_context_id ,source_id ,2 ,3) l_lender_name3 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lender 4' ,action_context_id ,source_id ,1 ,4) l_lender_pan4 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Lender 4' ,action_context_id ,source_id ,2 ,4) l_lender_name4 ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Superannuation Fund' ,action_context_id ,source_id ,1 ,NULL) l_super_annuation ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Fund Name' ,action_context_id ,source_id ,1 ,NULL) l_fund_name ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Contribution Start Date' ,action_context_id ,source_id ,1 ,NULL) l_fund_start ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Contribution End Date' ,action_context_id ,source_id ,1 ,NULL) l_fund_end ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Fund Amount Repaid' ,action_context_id ,source_id ,1 ,NULL) l_fund_repaid ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Avg Tax Rate of Prev 3 FYs' ,action_context_id ,source_id ,1 ,NULL) l_fund_tax_rate ,pay_in_24q_er_returns.get_24q_values ('IN_24Q_SALARY' ,'Tax Deducted on Fund' ,action_context_id ,source_id ,1 ,NULL) l_fund_tax_paid FROM pay_action_information WHERE action_information_category = 'IN_24Q_PERSON' AND action_context_type = 'AAP' AND action_information2 = :P_ASSESSMENT_YEAR || :P_QUARTER AND action_information3 = :P_GRE_ORGANIZATION AND action_context_id IN ( SELECT max (pai.action_context_id) FROM pay_action_information pai ,pay_assignment_actions paa ,per_assignments_f asg WHERE pai.action_information_category = 'IN_24Q_PERSON' AND pai.action_context_type = 'AAP' AND pai.action_information1 = asg.person_id AND pai.action_information2 = :P_ASSESSMENT_YEAR || :P_QUARTER AND pai.action_information3 = :P_GRE_ORGANIZATION AND pai.source_id = paa.assignment_action_id AND pai.assignment_id = asg.assignment_id GROUP BY pai.assignment_id ,pai.action_information1 ,pai.action_information9 ) ORDER BY fnd_number.canonical_to_number (action_information11) ASC ) sal |