PAY Generate Employer Quarterly Tax Returns (India) - Excel

Description
Categories: BI Publisher
Columns: Sd Action Context Id, Sd Bh Batch Num, Sd Source Id, Sd Emp Person Id, Sd Sal Rec Index, Sd Emp Pan, Sd Emp Pan Ref, Sd Emp Full Name, Sd Emp Designation, Sd Emp Category ...
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