PAY Generate Employer Quarterly Tax Returns (India) - Excel

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: Generate Employer Quarterly Tax Returns (India) - Excel
Short Name: PAYINF24Q
DB package:
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