Using the following view I have created the payslip report.
Based on the requirement I have made the view which is working very well.
Guidance for change:
Earning and deduction elements has to be replaced.
Total earning and Total deductions has be calculated accordingly.
For faster output , make it materialized view.
CREATE OR REPLACE FORCE VIEW xx_payslip_register_v (action_type,
--employment_category,
--grade,
--job,
--LOCATION,
employee_number,
full_name,
payroll_name,
period,
business_group_id,
request_id,
pfno,
epsno,
assignment_action_id,
assignment_id,
payroll_id,
time_period_id,
basic,
basic_arrear,
dpay,
dpay_arrear,
interim_allowance,
personal_pay,
personal_pay_arrear,
da,
arrear_da,
deputation_allowance,
deputation_allowance_arrear,
ppay2,
ppay2_arrear,
site_allowance,
site_allowance_arrear,
hardship_allowance,
hardship_allowance_arrear,
house_rent_allowance,
house_rent_allowance_arrear,
cca,
cca_arrear,
sbihf_subsidy,
sbihf_howrah_subsidy,
hdfc_subsidy,
miscelleneous_earnings,
tot_earnings,
pf,
epf,
pension_fund,
vpf,
lic,
ptax,
itax,
company_accomodation,
festival_advance_deduction,
cooperative_deduction,
car_loan_interest,
car_loan_deduction,
car_repair_loan_interest,
officer_car_repair_loan,
officer_sundry_laon_interest,
officer_sundry_loan_deduction,
company_housing_loan_interest,
company_housing_loan,
sbihf,
sbi_howrah,
hdfc,
furniture_loan_interest,
furniture_loan,
pf_loan,
recreation_club_deduction,
hire_fixed_assests,
salary_advance_deduction,
car_expense_recovery,
personal_expense_recovery,
income_tax_arrear,
officer_tiffin,
rd,
donation,
miscelleneous_deduction,
coin_carryover_april,
coin_carryover,
arrears_pt,
basic_adjustment,
car_loan_deduction_adjustment,
stat_pf_arrear,
vol_pf_arrear,
pf_arrear_adj,
vpf_arrear_adj,
tot_deductions,
tot_deductions_wo_income_tax,
net_pay,
net_pay_wo_income_tax
)
AS
SELECT action_type,
--bnr_employment_cat (period, assignment_id) employment_category,
--bnr_employee_grade (period, assignment_id) grade,
--bnr_employee_job (period, assignment_id) job,
--bnr_employee_location (period, assignment_id) LOCATION,
employee_number, full_name, payroll_name, period,
business_group_id, request_id, pfno, epsno, assignment_action_id,
assignment_id, payroll_id, time_period_id,
SUM (NVL (basic, 0)) basic,
SUM (NVL (basic_arrear, 0)) basic_arrear, SUM (NVL (dpay, 0))
dpay,
SUM (NVL (dpay_arrear, 0)) dpay_arrear,
SUM (NVL (interim_allowance, 0)) interim_allowance,
SUM (NVL (personal_pay, 0)) personal_pay,
SUM (NVL (personal_pay_arrear, 0)) personal_pay_arrear,
SUM (NVL (da, 0)) da, SUM (NVL (arrear_da, 0)) arrear_da,
SUM (NVL (deputation_allowance, 0)) deputation_allowance,
SUM
(NVL (deputation_allowance_arrear, 0)
) deputation_allowance_arrear,
SUM (NVL (ppay2, 0)) ppay2,
SUM (NVL (ppay2_arrear, 0)) ppay2_arrear,
SUM (NVL (site_allowance, 0)) site_allowance,
SUM (NVL (site_allowance_arrear, 0)) site_allowance_arrear,
SUM (NVL (hardship_allowance, 0) + NVL (hsal_adj, 0)
) hardship_allowance,
SUM (NVL (hardship_allowance_arrear, 0))
hardship_allowance_arrear,
SUM (NVL (house_rent_allowance, 0) + NVL (hra_adj, 0)
) house_rent_allowance,
SUM
(NVL (house_rent_allowance_arrear, 0)
) house_rent_allowance_arrear,
SUM (NVL (cca, 0)) cca, SUM (NVL (cca_arrear, 0)) cca_arrear,
SUM (NVL (sbihf_subsidy, 0)) sbihf_subsidy,
SUM (NVL (sbihf_howrah_subsidy, 0)) sbihf_howrah_subsidy,
SUM (NVL (hdfc_subsidy, 0)) hdfc_subsidy,
SUM (NVL (miscelleneous_earnings, 0)) miscelleneous_earnings,
SUM ( NVL (basic, 0)
+ NVL (basic_arrear, 0)
+ NVL (dpay, 0)
+ NVL (house_rent_allowance, 0)
+ NVL (house_rent_allowance_arrear, 0)
+ NVL (dpay_arrear, 0)
+ NVL (interim_allowance, 0)
+ NVL (personal_pay, 0)
+ NVL (personal_pay_arrear, 0)
+ NVL (arrear_da, 0)
+ NVL (deputation_allowance, 0)
+ NVL (deputation_allowance_arrear, 0)
+ NVL (ppay2, 0)
+ NVL (ppay2_arrear, 0)
+ NVL (site_allowance, 0)
+ NVL (site_allowance_arrear, 0)
+ NVL (hardship_allowance, 0)
+ NVL (hardship_allowance_arrear, 0)
+ NVL (cca, 0)
+ NVL (cca_arrear, 0)
+ NVL (sbihf_subsidy, 0)
+ NVL (sbihf_howrah_subsidy, 0)
+ NVL (hdfc_subsidy, 0)
+ NVL (miscelleneous_earnings, 0)
+ NVL (da, 0)
+ NVL (hsal_adj, 0)
+ NVL (hra_adj, 0)
) tot_earnings,
SUM (NVL (pf, 0)) pf, SUM (NVL (epf, 0)) epf,
SUM (NVL (pension_fund, 0)) pension_fund, SUM (NVL (vpf, 0)) vpf,
SUM (NVL (lic, 0)) lic, SUM (NVL (ptax, 0)) ptax,
SUM (NVL (itax, 0)) itax,
SUM (NVL (company_accomodation, 0)) company_accomodation,
SUM (NVL (festival_advance_deduction, 0)
) festival_advance_deduction,
SUM (NVL (cooperative_deduction, 0)) cooperative_deduction,
SUM (NVL (car_loan_interest, 0)) car_loan_interest,
SUM (NVL (car_loan_deduction, 0)) car_loan_deduction,
SUM (NVL (car_repair_loan_interest, 0)) car_repair_loan_interest,
SUM (NVL (officer_car_repair_loan, 0)) officer_car_repair_loan,
SUM
(NVL (officer_sundry_laon_interest, 0)
) officer_sundry_laon_interest,
SUM
(NVL (officer_sundry_loan_deduction, 0)
) officer_sundry_loan_deduction,
SUM
(NVL (company_housing_loan_interest, 0)
) company_housing_loan_interest,
SUM (NVL (company_housing_loan, 0)) company_housing_loan,
SUM (NVL (sbihf, 0)) sbihf, SUM (NVL (sbi_howrah, 0)) sbi_howrah,
SUM (NVL (hdfc, 0)) hdfc,
SUM (NVL (furniture_loan_interest, 0)) furniture_loan_interest,
SUM (NVL (furniture_loan, 0)) furniture_loan,
SUM (NVL (pf_loan, 0)) pf_loan,
SUM (NVL (recreation_club_deduction, 0))
recreation_club_deduction,
SUM (NVL (hire_fixed_assests, 0)) hire_fixed_assests,
SUM (NVL (salary_advance_deduction, 0)) salary_advance_deduction,
SUM (NVL (car_expense_recovery, 0)) car_expense_recovery,
SUM (NVL (personal_expense_recovery, 0))
personal_expense_recovery,
SUM (NVL (income_tax_arrear, 0)) income_tax_arrear,
SUM (NVL (officer_tiffin, 0)) officer_tiffin, SUM (NVL (rd, 0))
rd,
SUM (NVL (donation, 0)) donation,
SUM (NVL (miscelleneous_deduction, 0)) miscelleneous_deduction,
SUM (NVL (coin_carryover_april, 0)) coin_carryover_april,
--bnr_coin_cf_calc (period, assignment_id) coin_carryover,
0 coin_carryover,
SUM (NVL (arrears_pt, 0)) arrears_pt,
SUM (NVL (basic_adjustment, 0)) basic_adjustment,
SUM
(NVL (car_loan_deduction_adjustment, 0)
) car_loan_deduction_adjustment,
SUM (NVL (stat_pf_arrear, 0)) stat_pf_arrear,
SUM (NVL (vol_pf_arrear, 0)) vol_pf_arrear,
SUM (NVL (pf_arrear_adj, 0)) pf_arrear_adj,
SUM (NVL (vpf_arrear_adj, 0)) vpf_arrear_adj,
SUM ( NVL (pf, 0)
+ NVL (vpf, 0)
+ NVL (lic, 0)
+ NVL (ptax, 0)
+ NVL (itax, 0)
+ NVL (company_accomodation, 0)
+ NVL (festival_advance_deduction, 0)
+ NVL (cooperative_deduction, 0)
+ NVL (car_loan_interest, 0)
+ NVL (car_loan_deduction, 0)
+ NVL (car_repair_loan_interest, 0)
+ NVL (officer_car_repair_loan, 0)
+ NVL (officer_sundry_laon_interest, 0)
+ NVL (officer_sundry_loan_deduction, 0)
+ NVL (company_housing_loan_interest, 0)
+ NVL (company_housing_loan, 0)
+ NVL (sbihf, 0)
+ NVL (sbi_howrah, 0)
+ NVL (hdfc, 0)
+ NVL (furniture_loan_interest, 0)
+ NVL (furniture_loan, 0)
+ NVL (pf_loan, 0)
+ NVL (recreation_club_deduction, 0)
+ NVL (hire_fixed_assests, 0)
+ NVL (salary_advance_deduction, 0)
+ NVL (car_expense_recovery, 0)
+ NVL (personal_expense_recovery, 0)
+ NVL (income_tax_arrear, 0)
+ NVL (officer_tiffin, 0)
+ NVL (rd, 0)
+ NVL (donation, 0)
+ NVL (miscelleneous_deduction, 0)
+ NVL (coin_carryover_april, 0)
--+ bnr_coin_cf_calc (period, assignment_id)
+ NVL (arrears_pt, 0)
+ NVL (basic_adjustment, 0)
+ NVL (car_loan_deduction_adjustment, 0)
+ NVL (stat_pf_arrear, 0)
+ NVL (vol_pf_arrear, 0)
+ NVL (pf_arrear_adj, 0)
+ NVL (vpf_arrear_adj, 0)
) tot_deductions,
SUM
( NVL (pf, 0)
+ NVL (vpf, 0)
+ NVL (lic, 0)
+ NVL (ptax, 0)
+ NVL (company_accomodation, 0)
+ NVL (festival_advance_deduction, 0)
+ NVL (cooperative_deduction, 0)
+ NVL (car_loan_interest, 0)
+ NVL (car_loan_deduction, 0)
+ NVL (car_repair_loan_interest, 0)
+ NVL (officer_car_repair_loan, 0)
+ NVL (officer_sundry_laon_interest, 0)
+ NVL (officer_sundry_loan_deduction, 0)
+ NVL (company_housing_loan_interest, 0)
+ NVL (company_housing_loan, 0)
+ NVL (sbihf, 0)
+ NVL (sbi_howrah, 0)
+ NVL (hdfc, 0)
+ NVL (furniture_loan_interest, 0)
+ NVL (furniture_loan, 0)
+ NVL (pf_loan, 0)
+ NVL (recreation_club_deduction, 0)
+ NVL (hire_fixed_assests, 0)
+ NVL (salary_advance_deduction, 0)
+ NVL (car_expense_recovery, 0)
+ NVL (personal_expense_recovery, 0)
+ NVL (income_tax_arrear, 0)
+ NVL (officer_tiffin, 0)
+ NVL (rd, 0)
+ NVL (donation, 0)
+ NVL (miscelleneous_deduction, 0)
+ NVL (coin_carryover_april, 0)
--+ bnr_coin_cf_calc (period, assignment_id)
+ NVL (arrears_pt, 0)
+ NVL (basic_adjustment, 0)
+ NVL (car_loan_deduction_adjustment, 0)
+ NVL (stat_pf_arrear, 0)
+ NVL (vol_pf_arrear, 0)
+ NVL (pf_arrear_adj, 0)
+ NVL (vpf_arrear_adj, 0)
) tot_deductions_wo_income_tax,
SUM ( NVL (basic, 0)
+ NVL (basic_arrear, 0)
+ NVL (dpay, 0)
+ NVL (house_rent_allowance, 0)
+ NVL (house_rent_allowance_arrear, 0)
+ NVL (dpay_arrear, 0)
+ NVL (interim_allowance, 0)
+ NVL (personal_pay, 0)
+ NVL (personal_pay_arrear, 0)
+ NVL (arrear_da, 0)
+ NVL (deputation_allowance, 0)
+ NVL (deputation_allowance_arrear, 0)
+ NVL (ppay2, 0)
+ NVL (ppay2_arrear, 0)
+ NVL (site_allowance, 0)
+ NVL (site_allowance_arrear, 0)
+ NVL (hardship_allowance, 0)
+ NVL (hardship_allowance_arrear, 0)
+ NVL (cca, 0)
+ NVL (cca_arrear, 0)
+ NVL (sbihf_subsidy, 0)
+ NVL (sbihf_howrah_subsidy, 0)
+ NVL (hdfc_subsidy, 0)
+ NVL (miscelleneous_earnings, 0)
+ NVL (da, 0)
+ NVL (hsal_adj, 0)
+ NVL (hra_adj, 0)
)
- SUM ( NVL (pf, 0)
+ NVL (vpf, 0)
+ NVL (lic, 0)
+ NVL (ptax, 0)
+ NVL (itax, 0)
+ NVL (company_accomodation, 0)
+ NVL (festival_advance_deduction, 0)
+ NVL (cooperative_deduction, 0)
+ NVL (car_loan_interest, 0)
+ NVL (car_loan_deduction, 0)
+ NVL (car_repair_loan_interest, 0)
+ NVL (officer_car_repair_loan, 0)
+ NVL (officer_sundry_laon_interest, 0)
+ NVL (officer_sundry_loan_deduction, 0)
+ NVL (company_housing_loan_interest, 0)
+ NVL (company_housing_loan, 0)
+ NVL (sbihf, 0)
+ NVL (sbi_howrah, 0)
+ NVL (hdfc, 0)
+ NVL (furniture_loan_interest, 0)
+ NVL (furniture_loan, 0)
+ NVL (pf_loan, 0)
+ NVL (recreation_club_deduction, 0)
+ NVL (hire_fixed_assests, 0)
+ NVL (salary_advance_deduction, 0)
+ NVL (car_expense_recovery, 0)
+ NVL (personal_expense_recovery, 0)
+ NVL (income_tax_arrear, 0)
+ NVL (officer_tiffin, 0)
+ NVL (rd, 0)
+ NVL (donation, 0)
+ NVL (miscelleneous_deduction, 0)
+ NVL (coin_carryover_april, 0)
-- + bnr_coin_cf_calc (period, assignment_id)
+ NVL (arrears_pt, 0)
+ NVL (basic_adjustment, 0)
+ NVL (car_loan_deduction_adjustment, 0)
+ NVL (stat_pf_arrear, 0)
+ NVL (vol_pf_arrear, 0)
+ NVL (pf_arrear_adj, 0)
+ NVL (vpf_arrear_adj, 0)
) net_pay,
SUM ( NVL (basic, 0)
+ NVL (basic_arrear, 0)
+ NVL (dpay, 0)
+ NVL (house_rent_allowance, 0)
+ NVL (house_rent_allowance_arrear, 0)
+ NVL (dpay_arrear, 0)
+ NVL (interim_allowance, 0)
+ NVL (personal_pay, 0)
+ NVL (personal_pay_arrear, 0)
+ NVL (arrear_da, 0)
+ NVL (deputation_allowance, 0)
+ NVL (deputation_allowance_arrear, 0)
+ NVL (ppay2, 0)
+ NVL (ppay2_arrear, 0)
+ NVL (site_allowance, 0)
+ NVL (site_allowance_arrear, 0)
+ NVL (hardship_allowance, 0)
+ NVL (hardship_allowance_arrear, 0)
+ NVL (cca, 0)
+ NVL (cca_arrear, 0)
+ NVL (sbihf_subsidy, 0)
+ NVL (sbihf_howrah_subsidy, 0)
+ NVL (hdfc_subsidy, 0)
+ NVL (miscelleneous_earnings, 0)
+ NVL (da, 0)
+ NVL (hsal_adj, 0)
+ NVL (hra_adj, 0)
)
- SUM ( NVL (pf, 0)
+ NVL (vpf, 0)
+ NVL (lic, 0)
+ NVL (ptax, 0)
+ NVL (company_accomodation, 0)
+ NVL (festival_advance_deduction, 0)
+ NVL (cooperative_deduction, 0)
+ NVL (car_loan_interest, 0)
+ NVL (car_loan_deduction, 0)
+ NVL (car_repair_loan_interest, 0)
+ NVL (officer_car_repair_loan, 0)
+ NVL (officer_sundry_laon_interest, 0)
+ NVL (officer_sundry_loan_deduction, 0)
+ NVL (company_housing_loan_interest, 0)
+ NVL (company_housing_loan, 0)
+ NVL (sbihf, 0)
+ NVL (sbi_howrah, 0)
+ NVL (hdfc, 0)
+ NVL (furniture_loan_interest, 0)
+ NVL (furniture_loan, 0)
+ NVL (pf_loan, 0)
+ NVL (recreation_club_deduction, 0)
+ NVL (hire_fixed_assests, 0)
+ NVL (salary_advance_deduction, 0)
+ NVL (car_expense_recovery, 0)
+ NVL (personal_expense_recovery, 0)
+ NVL (income_tax_arrear, 0)
+ NVL (officer_tiffin, 0)
+ NVL (rd, 0)
+ NVL (donation, 0)
+ NVL (miscelleneous_deduction, 0)
+ NVL (coin_carryover_april, 0)
--+ bnr_coin_cf_calc (period, assignment_id)
+ NVL (arrears_pt, 0)
+ NVL (basic_adjustment, 0)
+ NVL (car_loan_deduction_adjustment, 0)
+ NVL (stat_pf_arrear, 0)
+ NVL (vol_pf_arrear, 0)
+ NVL (pf_arrear_adj, 0)
+ NVL (vpf_arrear_adj, 0)
) net_pay_wo_income_tax
FROM (SELECT ppa.action_type, papf.employee_number, papf.full_name,
ppf.payroll_name, papf.per_information8 pfno,
papf.per_information13 epsno, paa.assignment_action_id,
paaf.assignment_id, paaf.payroll_id, ppa.time_period_id,
TO_CHAR (ppa.effective_date, 'MON-YYYY') period,
ppa.business_group_id, ppa.request_id,
DECODE (prr1.element_name,
'Officer_Basic', prrv1.result_value
) basic,
DECODE (prr1.element_name,
'Officer_Basic_Arrear', prrv1.result_value
) basic_arrear,
DECODE (prr1.element_name,
'Officer_DPay', prrv1.result_value
) dpay,
DECODE (prr1.element_name,
'Officer_DPay_Arrear', prrv1.result_value
) dpay_arrear,
DECODE (prr1.element_name,
'Officer_Interim_Allowance', prrv1.result_value
) interim_allowance,
DECODE (prr1.element_name,
'Officer_Personal_Pay', prrv1.result_value
) personal_pay,
DECODE
(prr1.element_name,
'Officer_Personal_Pay_Arrear', prrv1.result_value
) personal_pay_arrear,
DECODE (prr1.element_name,
'Officer_Dearness_Allowance', prrv1.result_value
) da,
DECODE (prr1.element_name,
'Officer_Dearness_Allowance_Arrear', prrv1.result_value
) arrear_da,
DECODE
(prr1.element_name,
'Officer_Deputation_Allowance', prrv1.result_value
) deputation_allowance,
DECODE
(prr1.element_name,
'Officer_Deputation_Allowance_Arrear', prrv1.result_value
) deputation_allowance_arrear,
DECODE (prr1.element_name,
'Officer_PPay2', prrv1.result_value
) ppay2,
DECODE (prr1.element_name,
'Officer_PPay2_Arrear', prrv1.result_value
) ppay2_arrear,
DECODE (prr1.element_name,
'Officer_Site_Allowance', prrv1.result_value
) site_allowance,
DECODE
(prr1.element_name,
'Officer_Site_Allowance_Arrear', prrv1.result_value
) site_allowance_arrear,
DECODE (prr1.element_name,
'Officer_Hardship_Allowance', prrv1.result_value
) hardship_allowance,
DECODE
(prr1.element_name,
'Officer_Hardship_Allowance_Arrear', prrv1.result_value
) hardship_allowance_arrear,
DECODE
(prr1.element_name,
'Officer_House_Rent_Allowance', prrv1.result_value
) house_rent_allowance,
DECODE
(prr1.element_name,
'Officer_House_Rent_Allowance_Arrear', prrv1.result_value
) house_rent_allowance_arrear,
DECODE (prr1.element_name,
'Officer_CCA', prrv1.result_value
) cca,
DECODE (prr1.element_name,
'Officer_CCA_Arrear', prrv1.result_value
) cca_arrear,
DECODE (prr1.element_name,
'Officer_SBIHF_Subsidy', prrv1.result_value
) sbihf_subsidy,
DECODE
(prr1.element_name,
'Officer_SBI_Howrah_Subsidy', prrv1.result_value
) sbihf_howrah_subsidy,
DECODE (prr1.element_name,
'Officer_HDFC_Subsidy', prrv1.result_value
) hdfc_subsidy,
DECODE
(prr1.element_name,
'Officer_Miscelleneous_Earnings', prrv1.result_value
) miscelleneous_earnings,
DECODE (prr1.element_name,
'Employee Statutory PF Contribution', prrv1.result_value
) pf,
DECODE (prr1.element_name,
'Employer PF Contribution', prrv1.result_value
) epf,
DECODE (prr1.element_name,
'EPS Contribution', prrv1.result_value
) pension_fund,
DECODE (prr1.element_name,
'Employee Voluntary PF Contribution', prrv1.result_value
) vpf,
DECODE (prr1.element_name,
'Officer_LIC', prrv1.result_value
) lic,
DECODE (prr1.element_name,
'Professional Tax Deduction', prrv1.result_value
) ptax,
DECODE (prr1.element_name,
'Income Tax', prrv1.result_value
) itax,
DECODE
(prr1.element_name,
'Officer_Company_Accomodation', prrv1.result_value
) company_accomodation,
DECODE
(prr1.element_name,
'Officer_Festival_Advance_Deduction', prrv1.result_value
) festival_advance_deduction,
DECODE
(prr1.element_name,
'Officer_Cooperative_Deduction', prrv1.result_value
) cooperative_deduction,
DECODE
(prr1.element_name,
'Officer_Car_Loan_Interest_Deduction', prrv1.result_value
) car_loan_interest,
DECODE (prr1.element_name,
'Officer_Car_Loan_Deduction', prrv1.result_value
) car_loan_deduction,
DECODE
(prr1.element_name,
'Officer_Car_Repair_Loan_Interest_Deduction', prrv1.result_value
) car_repair_loan_interest,
DECODE
(prr1.element_name,
'Officer_Car_Repair_Loan_Deduction', prrv1.result_value
) officer_car_repair_loan,
DECODE
(prr1.element_name,
'Officer_Sundry_Loan_Interest_Deduction', prrv1.result_value
) officer_sundry_laon_interest,
DECODE
(prr1.element_name,
'Officer_Sundry_Loan_Deduction', prrv1.result_value
) officer_sundry_loan_deduction,
DECODE
(prr1.element_name,
'Officer_Company_Housing_Loan_Interest_Deduction', prrv1.result_value
) company_housing_loan_interest,
DECODE
(prr1.element_name,
'Officer_Company_Housing_Loan_Deduction', prrv1.result_value
) company_housing_loan,
DECODE (prr1.element_name,
'Officer_SBIHF', prrv1.result_value
) sbihf,
DECODE (prr1.element_name,
'Officer_SBI_Howrah', prrv1.result_value
) sbi_howrah,
DECODE (prr1.element_name,
'Officer_HDFC', prrv1.result_value
) hdfc,
DECODE
(prr1.element_name,
'Officer_Furniture_Loan_Interest_Deduction', prrv1.result_value
) furniture_loan_interest,
DECODE
(prr1.element_name,
'Officer_Furniture_Loan_Deduction', prrv1.result_value
) furniture_loan,
DECODE (prr1.element_name,
'Officer_PF_Loan_Deduction', prrv1.result_value
) pf_loan,
DECODE
(prr1.element_name,
'Officer_Recreation_Club', prrv1.result_value
) recreation_club_deduction,
DECODE (prr1.element_name,
'Officer_Hire_Fixed_Assests', prrv1.result_value
) hire_fixed_assests,
DECODE
(prr1.element_name,
'Officer_Salary_Advance_Deduction', prrv1.result_value
) salary_advance_deduction,
DECODE
(prr1.element_name,
'Officer_Car_Expense_Recovery', prrv1.result_value
) car_expense_recovery,
DECODE
(prr1.element_name,
'Officer_Personal_Expense_Recovery', prrv1.result_value
) personal_expense_recovery,
DECODE (prr1.element_name,
'Officer_Income_Tax_Arrear', prrv1.result_value
) income_tax_arrear,
DECODE (prr1.element_name,
'Officer_Tiffin', prrv1.result_value
) officer_tiffin,
DECODE (prr1.element_name,
'Officer_Recurring_Deposit', prrv1.result_value
) rd,
DECODE (prr1.element_name,
'Officer_Donation', prrv1.result_value
) donation,
DECODE
(prr1.element_name,
'Officer_Miscelleneous_Deduction', prrv1.result_value
) miscelleneous_deduction,
DECODE
(prr1.element_name,
'Officer_Coin_Carryover_April', prrv1.result_value
) coin_carryover_april,
NULL coin_carryover,
DECODE (prr1.element_name,
'Arrears Professional Tax', prrv1.result_value
) arrears_pt,
DECODE (prr1.element_name,
'Officer_Basic_Adjustment', prrv1.result_value
) basic_adjustment,
DECODE
(prr1.element_name,
'Officer_Car_Loan_Deduction_Adjustment', prrv1.result_value
) car_loan_deduction_adjustment,
DECODE (prr1.element_name,
'Officer_Hardship_Allowance_Adjustment', prrv1.result_value
) hsal_adj,
DECODE
(prr1.element_name,
'Officer_House_Rent_Allowance_Adjustment', prrv1.result_value
) hra_adj,
DECODE
(prr1.element_name,
'Officer_Professional_Tax_Arrear_Adjustment', prrv1.result_value
) ptax_adj,
DECODE (prr1.element_name,
'Officer_Net_Pay_Adjustment', prrv1.result_value
) net_pay_adj,
DECODE (prr1.element_name,
'Arrears Employee Statutory PF', prrv1.result_value
) stat_pf_arrear,
DECODE (prr1.element_name,
'Arrears Employee Voluntary PF', prrv1.result_value
) vol_pf_arrear,
DECODE (prr1.element_name,
'Officer_PF_Arrear_Adjustment', prrv1.result_value
) pf_arrear_adj,
DECODE (prr1.element_name,
'Officer_VPF_Arrear_Adjustment', prrv1.result_value
) vpf_arrear_adj,
DECODE (prr1.element_name,
'Officer_Income_Tax_Adjustment', prrv1.result_value
) it_adjustment
FROM per_all_people_f papf,
per_all_assignments_f paaf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payrolls_f ppf,
(SELECT rrs.assignment_action_id, rrs.run_result_id,
etytl.element_name
FROM pay_run_results rrs, pay_element_types_f_tl etytl
WHERE rrs.element_type_id = etytl.element_type_id
AND etytl.LANGUAGE = USERENV ('LANG')) prr1,
(SELECT rrv.run_result_id, invtl.NAME, rrv.result_value
FROM pay_input_values_f_tl invtl,
pay_run_result_values rrv
WHERE invtl.LANGUAGE = USERENV ('LANG')
AND rrv.input_value_id = invtl.input_value_id) prrv1
WHERE TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
--AND papf.current_employee_flag = 'Y'
AND paaf.person_id = papf.person_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
AND paa.assignment_id = paaf.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R', 'Q')
AND paa.run_type_id = 63 --IN (61, 63)
AND ppf.payroll_id = ppa.payroll_id
AND ppf.payroll_id = 61
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND prr1.assignment_action_id(+) = paa.assignment_action_id
AND prrv1.run_result_id(+) = prr1.run_result_id
--AND TO_CHAR (ppa.effective_date, 'MON-YYYY') = 'JAN-2010'
AND prrv1.NAME = 'Pay Value')
GROUP BY employee_number,
full_name,
payroll_name,
period,
business_group_id,
request_id,
pfno,
epsno,
assignment_action_id,
assignment_id,
payroll_id,
time_period_id,
action_type,
coin_carryover;
No comments:
Post a Comment