ENGINATICS
SYSTEM_ADMINISTRATOR
SYSADMIN
82288223F2EE3869E053B46B63588994Y
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
PAY
PAYINF24Q
US
PAY Generate Employer Quarterly Tax Returns (India) - Excel
Application: Payroll
Source: Generate Employer Quarterly Tax Returns (India) - Excel
Short Name: PAYINF24Q
DB package:
BI Publisher
:bh_batch_num
:p_assessment_year
:p_gre_organization
:p_quarter