stored procedure - Command(s) completed successfully.

Question

stored procedure - Command(s) completed successfully.

abk365

SSC Enthusiast

Points: 125
More actions
July 30, 2012 at 4:05 am

#278740

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--sp_helptext WageGenProcForSCons 2
-- exec WageGenProcForSCons 2
ALTER procedure [dbo].[WageGenProcForSCons-2] @InputBranchId Int
as
Begin
/*
declare @InputBranchId Int
set @InputBranchId=2
*/
-- Modified by Joji To include FoodCoupenamount
-- Loss of pay for Food coupen is deducted from Other Deduction.
-- Food coupen amount will give as full amonteventhough loss of pay
-- is there for that Employee.
-- Modified by Arun cs to update the field otherdeductions with the telephone expense in the salary table
--SCH/PAA/24
declare @EmployeeId smallint,
@BasicPay money,
@VariableDA money,
@HRA money,
@HpBasicPay money,
@HpVariableDA money,
@HpHRA money,
@SpecialAllowance money,
@VehicleAllowance Money,
@TravelAllowance money,
@FestivalAllowance money,
@Arrears Money,
@PLEncash Money,
@ProfessionTax Money,
@OtherDeductions Money,
@Catagory char(1),
@DailyAllowance money,
@FieldAllowance money,
@CityCompAllowance money,
@AdvanceDeduction Money,
@ClubDeduction Money,
@ESIRequired Char(1),
@EmployeeESI Money,
@EmployerESI Money,
@TDS Money,
@EmployerEPF money,
@EmployerFPF money,
@EmployeeEPF money,
@EmployeeFPF money,
@LoanAmount Money,
@AmountPaid Money,
@MonthCutting Money,
@LOPMonthTaken Money,
@HalfPayMonthTaken Money,
@ESIMonthTaken Money,
@LossOfPay money,
@ESILOP money,
@CurYearMonth Integer,
@CurrentMonth smallint,
@CurrentYear Integer,
@FoodAllowance money,
@Allowance1 money,
@Allowance2 money,
@Allowance3 money,
@Deduction1 money,
@Deduction2 money,
@Deduction3 money,
@MonthStartDay smallint,
@MonthEndingDay smallint,
@Amount Money,
@AdjArrears Money,
@Basic_Arrears as Decimal(18,2),
@Hra_Arrears Decimal(18,2),
@Conv_Arrears Decimal(18,2),
@Spl_Arrears Decimal(18,2),
@AdjDeductions Money,
@Temp Float(10),
@BranchId SmallInt,
@NoOfLeaveEncashed Float,
@GrossSalaryDeductingLOP Money,
@gtdate DateTime,
@NoOfDays SmallInt,
@WageForPFCal Decimal(18,2) ,
@TempEmoEpf Money,
@EncashRate Decimal(18,2),
@OverTime Decimal(18,2),
@OverTimeGrossSalary Decimal(18,2),
@JoinDate DateTime,
@BasicPayPerc Decimal(6,3),
@VDAPerc Decimal(6,3),
@HRAPerc Decimal(6,3),
@TravelAllowPerc Decimal(6,3),
@FieldAllowPerc Decimal(6,3),
@CityCompenPerc Decimal(6,3),
@SpecialPerc Decimal(6,3),
--@BasicPayPerc float,
--@VDAPerc float,
-- @HRAPerc float,
--@TravelAllowPerc float,
--@FieldAllowPerc float,
--@CityCompenPerc float,
--@SpecialPerc float,
@LeftCompany char(1),
@ReleivedDate datetime,
--@LeftCompany,@ReleivedDate
@BasciPFPerc Decimal(6,2),
@VDAPFPerc Decimal(6,2),
@Foodperc Decimal(6,2),
@Datechar2 VarChar(20),
@FirstDateoftheMonth datetime,
@FestivalAllowPerc float,
@EmpEPFPercent Money,
@EmpFPFPercent Money,
@EmployerEPFPercent Money,
@EmployerFPFPercent Money,
@PFCeilingAmount Money,
@TotPf Money,
@BalPf Money,
@PFforBasicArrears Money,
@PFforVDAArrears Money ,
@Location Integer,
@PFLimited VarChar(1),
@OrginalBasicPay money,
@GrossSalaryPrTax money,
@PFEdited money,
@PFArrearsEmployerEPF money,
@PFArrearsEmployerFPF money ,
@BasicPayPercActual Decimal(6,3) ,
@TelephoneBillAmount Decimal(18,2) ,
@Bankid int ,
@BankAcNo VarChar(50)
DECLARE @empPfPerc Numeric(18,2), @emplyrPfPerc Numeric(18,2),
@empPfAmt Numeric(18,2), @emplyrPfAmt Numeric(18,2),
@empAge Integer
select @MonthStartDay = ParamValue from controls where ParamId = 1
and BranchId=@InputBranchId
select @MonthEndingDay = ParamValue from controls where ParamId = 2
and BranchId=@InputBranchId
select @CurrentMonth = ParamValue from Controls where ParamId = 3
and BranchId=@InputBranchId
select @CurrentYear = ParamValue from Controls where ParamId = 6
and BranchId=@InputBranchId
Select @EncashRate=ParamValue from Controls Where ParamName='Encash Rate' and
BranchId=@InputBranchId
select @PFCeilingAmount = ParamValue from Controls where ParamId = 17
and BranchId=@InputBranchId
select @BasicPayPerc=IsNull(BasicPay,0),@VDAPerc=IsNull(VariableDA,0),@HRAPerc=IsNull(HRA,0),
@TravelAllowPerc=IsNull(TravelAllowance,0),@FieldAllowPerc=IsNull(FieldAllowance,0),
@FestivalAllowPerc=IsNull(FestivalAllowance,0),
@CityCompenPerc=CityCompAllowance,@SpecialPerc=SpecialAllowance
from CompanyPayScale
Where BranchId=@InputBranchId
Exec GetNoOfDaysInaMonthProc_LOP @InputBranchId,@LastDayOfMonth= @gtdate Output
Select @Datechar2='01/'+Convert(VarChar(2),@CurrentMonth)+'/'+
Convert(VarChar(4),@CurrentYear)
Select @FirstDateoftheMonth = Convert(DateTime,@Datechar2,103)
Select @NoOfDays=DatePart(Day,@gtdate)
--@FirstDateoftheMonth,@gtdate
Select @NoOfDays
select @CurYearMonth = @CurrentYear * 100 + @currentMonth
-- Deletes from salary if any records exits for the current month ---
Delete from Salary Where YearMonth=@CurYearMonth
and BranchId=@InputBranchId and employeeid Not in (Select employeeid from
EmployeeFinalSettlement where Branchid = @BranchID and YearMonth=@CurYearMonth)
Delete from BankofEachEmp Where YearMonth=@CurYearMonth
and BranchId=@InputBranchId
--Update Paymaster Set TotEmployeeEPF=(BasicPay+VariableDA) * (Select ParamValue
--from Controls Where ParamId=7 And BranchId=1)/100 Where
--BranchId=@InputBranchId
update LEAVESUMMARY set lopmonthtaken = 0,halfpaymonthtaken = 0,
ESImonthTaken = 0
update LEAVESUMMARY set lopmonthtaken =
(
select sum(NoOfDaYs) from Leaves
where leavetype = 5 and
leaves.employeeid = leavesummary.employeeid and
leaves.Branchid = leavesummary.Branchid and (datepart(month,Startingdate) = @CurrentMonth) and
(DatePart(Year,StartingDate)=@CurrentYear) and
Leaves.BranchId = @InputBranchId
)
update LEAVESUMMARY set halfpaymonthtaken =
(
select sum(NoOfDaYs) from Leaves
where leavetype = 6 and
leaves.employeeid = leavesummary.employeeid and
leaves.Branchid = leavesummary.Branchid and
(datepart(month,Startingdate) = @CurrentMonth) and
(DatePart(Year,StartingDate)=@CurrentYear) and
Leaves.BranchId=@InputBranchId
)
update LEAVESUMMARY set ESImonthtaken =
(
select sum(NoOfDaYs) from Leaves
where leavetype = 8 and
leaves.employeeid = leavesummary.employeeid and
leaves.Branchid = leavesummary.Branchid and
(datepart(month,Startingdate) = @CurrentMonth) and
(DatePart(Year,StartingDate)=@CurrentYear) and
Leaves.BranchId=@InputBranchId
)
--
-- Updation of VariableDa of all employee's
-- Updation of PL Encash of all employee's
--
Declare PL_Encash Cursor for
select EmployeeId,NoOfLeaveEncashed,BranchId from PLEncash where
Encashed = 'N' and BranchId=@InputBranchId
Open PL_Encash
Fetch PL_Encash into @EmployeeId,@NoOfLeaveEncashed,@BranchId
While (@@Fetch_Status = 0)
Begin
update paymaster set PLEncash =
((VariableDA+BasicPay+HRA+FieldAllowance+
FestivalAllowance+TravelAllowance)/30) *
@NoOfLeaveEncashed * @EncashRate
Where EmployeeId = @EmployeeId and BranchId=@BranchId
Fetch PL_Encash into @EmployeeId,@NoOfLeaveEncashed,@BranchId
End
Close PL_Encash
deallocate PL_Encash
--
-- Adjustment Entry Updation
--
update PAYMASTER set OtherDeductions = 0,
Arrears = 0
declare ADJ_CUR cursor for
select EmployeeId, ISNull(arrears,0),ISNull(Hra_Arrears,0),ISNull(Conv_Arrears,0),
ISNull(Spl_Arrears,0), ISNull(deductions,0),BranchId from ADJUSTMENTS
where ( (datepart(month,adjustmentdatetime) = @CurrentMonth )
and (DatePart(Year,Adjustmentdatetime)=@CurrentYear)) and
BranchId=@InputBranchId
open ADJ_CUR
fetch ADJ_CUR into @EmployeeId, @AdjArrears,@Hra_Arrears,@Conv_Arrears,@Spl_Arrears,
@AdjDeductions,@BranchId
while (@@fetch_status = 0)
begin
update PAYMASTER set OtherDeductions = OtherDeductions+@AdjDeductions
where EmployeeId = @EmployeeId and BranchId=@BranchId
update PAYMASTER set Arrears = Arrears+@ADjArrears+@Hra_Arrears+
@Conv_Arrears+@Spl_Arrears,
Basic_Arrears = @ADjArrears,Hra_Arrears = @Hra_Arrears,
Conv_Arrears=@Conv_Arrears,Spl_Arrears=@Spl_Arrears
where EmployeeId = @EmployeeId and BranchId=@BranchId
fetch ADJ_CUR into @EmployeeId, @AdjArrears,@Hra_Arrears,@Conv_Arrears,@Spl_Arrears,
@AdjDeductions,@BranchId
end
close ADJ_CUR
deallocate ADJ_CUR
EXEC LoanProc @CurYearMonth,@InputBranchId
/************************************************************************************
New field added in the ProffesionTaxSlab and EmployeeInfo tables.
Include the LocationId in the cursor
************************************************************************************/
declare SAL_CUR cursor for
select PAYMASTER.EmployeeId, BasicPay, VariableDA, HRA, SpecialAllowance,
VehicleAllowance, TravelAllowance,FestivalAllowance,DailyAllowance,
FieldAllowance, Arrears, PLEncash, ProfessionTax,
OtherDeductions, AdvanceDeduction, ClubDeduction,ESIRequired,TDS,
EMPLOYEEINFO.Catagory,
LoanAmount, AmountPaid, MonthCutting, IsNull(LOPMonthTaken,0), IsNull(HalfPayMonthTaken,0),
ESImonthTaken,PAYMASTER.BranchId,PayMaster.TotEmployerEPF,
PayMaster.TotEmployerFPF,PayMaster.TotEmployeeFPF,
PayMaster.TotEmployeeEPF,EmployeeInfo.joindate,CityCompAllowance,
EmployeeInfo.RelievedDate, EmployeeInfo.LeftCompany, EmployeeInfo.LocationId,
isnull(PAYMASTER.FoodAllowance,0),isnull(PAYMASTER.Allowance1,0),
isnull(PAYMASTER.Allowance2,0),
isnull(PAYMASTER.Allowance3,0),
isnull(PAYMASTER.Deduction1,0),isnull(PAYMASTER.Deduction2,0),
isnull(PAYMASTER.Deduction3 ,0),
ISNull(Basic_Arrears,0),ISNull(Hra_Arrears,0),ISNull(Conv_Arrears,0),
ISNull(Spl_Arrears,0),EMPLOYEEINFO.Bankid ,EMPLOYEEINFO.BankAcno
from PAYMASTER, EMPLOYEEINFO, LOANSUMMARY, LEAVESUMMARY
where PAYMASTER.EmployeeID = EMPLOYEEINFO.EmployeeId
and PAYMASTER.EmployeeID = LOANSUMMARY.EmployeeId
and PAYMASTER.EmployeeID = LEAVESUMMARY.EmployeeId
and PAYMASTER.BranchID = EMPLOYEEINFO.BranchId
and PAYMASTER.BranchID = LOANSUMMARY.BranchId
and PAYMASTER.BranchID = LEAVESUMMARY.BranchId
and (EMPLOYEEINFO.LeftCompany = 'N' or (EMPLOYEEINFO.LeftCompany = 'Y' and
RelievedDate >= @FirstDateoftheMonth))
and EMPLOYEEINFO.BranchId=@InputBranchId
and EmployeeInfo.joindate < = @gtdate
--and EMPLOYEEINFO.employeeid Not in (Select employeeid from
--EmployeeFinalSettlement where Branchid = @BranchID and YearMonth=@CurYearMonth)
open SAL_CUR
select @BasicPay = 0,
@OrginalBasicPay = 0,
@VariableDA = 0,
@HRA = 0,
@SpecialAllowance = 0,
@CityCompAllowance = 0,
@VehicleAllowance = 0,
@TravelAllowance = 0,
@FieldAllowance = 0,
@DailyAllowance = 0,
@FestivalAllowance = 0,
@Arrears = 0,
@PLEncash = 0,
@MonthCutting = 0,
@AdvanceDeduction = 0,
@LossOfPay = 0,
@ESILOP = 0,
@ProfessionTax = 0,
@ClubDeduction = 0,
@OtherDeductions = 0,
@EmployeeEPF = 0,
@EmployeeFPF = 0,
@EmployerEPF = 0,
@EmployerFPF = 0,
@TDS = 0,
@EmployeeESI = 0,
@EmployerESI = 0,
@HpBasicPay = 0,
@HpVariableDA = 0,
@HpHRA = 0,
@LoanAmount = 0,
@AmountPaid = 0,
@MonthCutting = 0,
@LOPMonthTaken = 0,
@ESIMonthTaken = 0,
@HalfPayMonthTaken = 0,
@Amount = 0,
@AdjArrears = 0,
@Hra_Arrears = 0,
@Conv_Arrears = 0,
@Spl_Arrears = 0,
@AdjDeductions = 0,
@Temp = 0,
@Location = 0,
@FoodAllowance =0,
@Allowance1=0,
@Allowance2=0,
@Allowance3=0,
@Deduction1=0,
@Deduction2=0,
@Deduction3=0
fetch SAL_CUR into
@EmployeeId, @BasicPay, @VariableDA, @HRA,@SpecialAllowance,
@VehicleAllowance, @TravelAllowance, @FestivalAllowance, @DailyAllowance,
@FieldAllowance, @Arrears, @PLEncash, @ProfessionTax,
@OtherDeductions, @AdvanceDeduction,@ClubDeduction,@ESIRequired,@TDS,@Catagory,
@LoanAmount, @AmountPaid, @MonthCutting, @LOPMonthTaken,@HalfPayMonthTaken,
@ESIMonthTaken,@BranchId,@EmployeeEPF,
@EmployeeFPF,@EmployerEPF,@EmployerFPF,@JoinDate,@CityCompAllowance,
@ReleivedDate,@LeftCompany, @Location,
@FoodAllowance,@Allowance1,@Allowance2,
@Allowance3,@Deduction1,@Deduction2,
@Deduction3,@Basic_Arrears,@Hra_Arrears,@Conv_Arrears,@Spl_Arrears,@Bankid ,@BankAcNo
while (@@fetch_status = 0)
begin
if @BasicPay + @VariableDA + @HRA + @SpecialAllowance +
@FieldAllowance + @CityCompAllowance + @FoodAllowance >0
Begin
select @OrginalBasicPay = @BasicPay
Select @GrossSalaryDeductingLOP = 0
--Since in this company we don't have loans and club deductions
-- Select @MonthCutting = 0
-- Select @ClubDeduction = 0
-- select @Overtime=0
if @HalfPayMonthTaken > 0
begin
select @LossOfPay = Convert(Money,(Convert(Float(10),
(@BasicPay+@VariableDA+@HRA)) / 60
* Convert(Float(10),@HalfPayMonthTaken)))
end
if month(@JoinDate)=@CurrentMonth and Year(@JoinDate)=@CurrentYear
begin
select @LOPMonthTaken =isnull(@LOPMonthTaken,0)
+ isnull(datediff(D,@FirstDateoftheMonth, @JoinDate),0)
end
if @LeftCompany='Y'
Begin
select @LOPMonthTaken =isnull(@LOPMonthTaken,0)
+ isnull(datediff(D,@ReleivedDate, @gtdate),0)
end
if @Arrears >0
Begin
select @BasciPFPerc = (@BasicPay /(@BasicPay + @VariableDA + @HRA +
@SpecialAllowance +
@FieldAllowance + @CityCompAllowance + @FestivalAllowance)) * 100
select @VDAPFPerc = (@VariableDA /(@BasicPay + @VariableDA + @HRA +
@SpecialAllowance +
@FieldAllowance + @CityCompAllowance + @FestivalAllowance)) * 100
if @Basic_Arrears >0
begin
select @PFEdited = PFarrears from Paymaster where
EmployeeId=@EmployeeId and BranchId=@BranchId
-- select @PFforBasicArrears = @Basic_Arrears * 0.01 * @EmpEPFPercent
select @PFforBasicArrears = @Basic_Arrears * 0.01 * @EmpEPFPercent
begin
if @PFEdited <> @PFforBasicArrears
begin
if @PFEdited > 0
begin
select @PFforBasicArrears = @PFEdited
end
else
begin
select @PFforBasicArrears = @PFforBasicArrears
end
end
end
end
-- select @PFforVDAArrears = @Arrears * 0.01 * @VDAPFPerc
end
else
Begin
select @PFforBasicArrears = 0
select @PFforVDAArrears = 0
end
--select @BasicPayPerc=IsNull(BasicPay,0),=IsNull(VariableDA,0),@HRAPerc=IsNull(HRA,0),
--@TravelAllowPerc=IsNull(TravelAllowance,0),@FieldAllowPerc=IsNull(FieldAllowance,0),
--@FestivalAllowPerc=IsNull(FestivalAllowance,0),
--@CityCompenPerc=CityCompAllowance,@SpecialPerc=SpecialAllowance
select @BasicPayPerc = (cast(@BasicPay as float)/(@BasicPay + @VariableDA + @HRA + @SpecialAllowance +
@FieldAllowance + @CityCompAllowance + @FoodAllowance+@FestivalAllowance )) * 100
select @VDAPerc = (cast(@VariableDA as float)/(@BasicPay + @VariableDA + @HRA + @SpecialAllowance +
@FieldAllowance + @CityCompAllowance + @FoodAllowance+@FestivalAllowance)) * 100
select @HRAPerc = (cast(@HRA as float)/(@BasicPay + @VariableDA + @HRA + @SpecialAllowance +
@FieldAllowance + @CityCompAllowance+@FoodAllowance+@FestivalAllowance)) * 100
select @CityCompenPerc = (cast(@CityCompAllowance as float) /(@BasicPay + @VariableDA + @HRA +
@SpecialAllowance + @FieldAllowance + @CityCompAllowance + @FoodAllowance+@FestivalAllowance)) * 100
select @FieldAllowPerc = (cast(@FieldAllowance as float) /(@BasicPay + @VariableDA + @HRA +
@SpecialAllowance + @FieldAllowance + @CityCompAllowance+@FoodAllowance+@FestivalAllowance)) * 100
select @SpecialPerc = (cast(@SpecialAllowance as float) /(@BasicPay + @VariableDA + @HRA +
@SpecialAllowance + @FieldAllowance + @CityCompAllowance+@FoodAllowance+@FestivalAllowance)) * 100
select @FoodPerc = (cast(@FoodAllowance as float)/(@BasicPay + @VariableDA + @HRA +
@SpecialAllowance + @FieldAllowance + @CityCompAllowance+@FoodAllowance+@FestivalAllowance)) * 100
select @FestivalAllowPerc = (cast(@FestivalAllowance as float) /(@BasicPay + @VariableDA + @HRA + @SpecialAllowance +
@FieldAllowance + @CityCompAllowance+@FoodAllowance+@FestivalAllowance)) * 100
if @LOPMonthTaken > 0
begin
select @LossOfPay = @LossOfPay +
Convert(Money,(Convert(Float(10),
(@BasicPay+@VariableDA+@HRA+@FieldAllowance+
@SpecialAllowance+@CityCompAllowance+@FoodAllowance +@FestivalAllowance )) / @NoOfDays *
Convert(Float(10),
@LOPMonthTaken)))
end
--@CityCompenPerc Decimal(3,0),
--@SpecialPerc Decimal(3,0),
--Finding the BP,VDA and others according to LOP
if @BasicPay >0
Select @BasicPay=@BasicPay-(@LossOfPay*(@BasicPayPerc/100))
if @VariableDA >0
Select @VariableDA=@VariableDA-(@LossOfPay*(@VDAPerc/100))
if @HRA >0
Select @HRA=@HRA-(@LossOfPay*(@HRAPerc/100))
if @FestivalAllowance > 0
Select @FestivalAllowance=@FestivalAllowance-(@LossOfPay*(@FestivalAllowPerc/100))
if @FieldAllowance >0
Select @FieldAllowance=@FieldAllowance-(@LossOfPay*(@FieldAllowPerc/100))
if @SpecialAllowance >0
select @SpecialAllowance=@SpecialAllowance-(@LossOfPay*(@SpecialPerc/100))
if @CityCompAllowance >0
select @CityCompAllowance=@CityCompAllowance-(@LossOfPay*(@CityCompenPerc/100))
--Loss of pay in Food coupen Added to Other Deductions
if @FoodAllowance >0
select @OtherDeductions=@OtherDeductions+(@LossOfPay*(@FoodPerc/100))
--@CityCompenPerc,@SpecialPerc
Select @GrossSalaryDeductingLOP=Convert(Money,(@BasicPay + @VariableDA + @TravelAllowance +
@HRA + @SpecialAllowance + @CityCompAllowance +
@FestivalAllowance + @FieldAllowance + @FoodAllowance+
@Allowance1+@Allowance2+@Allowance3)) --- - @LossOfPay
/************************************************************************************
New field added in the ProffesionTaxSlab and EmployeeInfo tables.
************************************************************************************/
select @GrossSalaryPrTax = @GrossSalaryDeductingLOP
-- For checking the Arrears and Amount greater than zero PF Tax
-- should consider for it
if @Arrears > 0
begin
select @GrossSalaryPrTax = @GrossSalaryPrTax + @Arrears
end
if @FoodAllowance > 0
Begin
select @GrossSalaryPrTax = @GrossSalaryPrTax - @FoodAllowance
end
Select @ProfessionTax=Convert(Money,Tax) from ProffesionTaxSlab
Where Convert(Float,@GrossSalaryPrTax) Between StartingWage
and EndingWage and BranchId = @BranchId and LocationId = @Location
--select @EmployeeId,@GrossSalaryDeductingLOP,@ProfessionTax
If @@RowCount=0
Select @ProfessionTax=0
Update Paymaster Set ProfessionTax=IsNull(@ProfessionTax,0),
PFarrears = @PFforBasicArrears where
EmployeeId=@EmployeeId and BranchId=@BranchId
select @BasicPayPercActual = BasicPay from CompanyPayScale where Branchid = @BranchId
If @@RowCount=0
Select @ProfessionTax=0
Select @WageForPFCal = (Convert(Float(10),@BasicPay) + ((@Arrears * @BasicPayPercActual) *.01))
--Convert(Float(10),@VariableDA)) -- + @PFforBasicArrears + @PFforVDAArrears
--Modified by Joji
--To take the PF Percentage from the table salry controls
--19-06-2003
select @EmpEPFPercent =value from SalaryControls where
EmployeeId=@EmployeeId and BranchId=@BranchId and typeId=1
select @EmpFPFPercent =value from SalaryControls where
EmployeeId=@EmployeeId and BranchId=@BranchId and typeId=2
select @EmployerEPFPercent =value from SalaryControls where
EmployeeId=@EmployeeId and BranchId=@BranchId and typeId=3
select @EmployerFPFPercent =value from SalaryControls where
EmployeeId=@EmployeeId and BranchId=@BranchId and typeId=4
-- Here The EPF and FPF calculated only for Employee and Probetionary
if @Catagory not in ('T','C','S')
begin
If (@BasicPay+@VariableDA)>=6500
Begin
select @EmployeeEPF = Convert( Smallmoney,Round(@WageForPFCal
* .01 * 12,2) )
select @EmployeeFPF = 0
Select @TempEmoEpf = Convert(Smallmoney,(round(6500 * .01 * 12,2)))
Exec RoundRuppee @TempEmoEpf, @RoundedAmount=@TempEmoEpf Output
select @EmployerEPF = Convert(Smallmoney,(round(6500 * .01 * 8.33,2)))
Exec RoundRuppee @EmployerEPF, @RoundedAmount=@EmployerEPF Output
-- select @EmployerFPF = Convert(Smallmoney,(round(5000 * .01 * 3.67,2)))
select @EmployerFPF = @TempEmoEpf - @EmployerEPF
select @EmployeeESI = 0
select @EmployerESI = 0
End
Else
Begin
select @EmployeeEPF = Convert(
Smallmoney,(round(@WageForPFCal * .01 * 12,2)))
select @EmployeeFPF = 0
select @EmployerEPF = Convert(
Smallmoney,(round(@WageForPFCal * .01 * 3.67,2)))
select @EmployerFPF = Convert(
Smallmoney,(round(@WageForPFCal * .01 * 8.33,2)))
select @EmployeeESI = 0
select @EmployerESI = 0
End
end
else
begin
select @EmployeeEPF = 0
select @EmployeeFPF = 0
select @EmployerEPF = 0
select @EmployerFPF = 0
select @EmployeeESI = 0
select @EmployerESI = 0
End
/*==============================================================='*/
--select @PFforBasicArrears = 0
if @Basic_Arrears > 0
begin
select @PFArrearsEmployerEPF = 0
select @PFArrearsEmployerFPF =0
-- select @PFforBasicArrears = @PFforBasicArrears
-- select @EmployeeEPF = @EmployeeEPF + @PFforBasicArrears
-- select @PFArrearsEmployerEPF = (@PFforBasicArrears * 3.67/12)
-- select @PFArrearsEmployerFPF = @PFforBasicArrears - @PFArrearsEmployerEPF
-- select @EmployerEPF = @EmployerEPF + @PFArrearsEmployerEPF
-- select @EmployerFPF = @EmployerFPF + @PFArrearsEmployerFPF
end
/*====================================================================*/
if @ESIRequired='Y'
Begin
select @EmployeeESI =@GrossSalaryPrTax * 1.75 * 0.01
select @EmployerESI = @GrossSalaryPrTax * 4.75 * 0.01
end
Exec RoundRuppee @BasicPay, @RoundedAmount=@BasicPay Output
Exec RoundRuppee @VariableDA, @RoundedAmount=@VariableDA Output
Exec RoundRuppee @HRA, @RoundedAmount=@HRA Output
Exec RoundRuppee @SpecialAllowance, @RoundedAmount=@SpecialAllowance Output
Exec RoundRuppee @CityCompAllowance, @RoundedAmount=@CityCompAllowance Output
Exec RoundRuppee @VehicleAllowance, @RoundedAmount=@VehicleAllowance Output
Exec RoundRuppee @TravelAllowance, @RoundedAmount=@TravelAllowance Output
Exec RoundRuppee @FieldAllowance, @RoundedAmount=@FieldAllowance Output
Exec RoundRuppee @DailyAllowance, @RoundedAmount=@DailyAllowance Output
Exec RoundRuppee @FestivalAllowance, @RoundedAmount=@FestivalAllowance Output
Exec RoundRuppee @Arrears, @RoundedAmount=@Arrears Output
Exec RoundRuppee @PLEncash, @RoundedAmount=@PLEncash Output
--Exec RoundRuppee @MonthCutting, @RoundedAmount=@MonthCutting Output
Exec RoundRuppee @AdvanceDeduction, @RoundedAmount=@AdvanceDeduction Output
Exec RoundRuppee @LossOfPay, @RoundedAmount=@LossOfPay Output
Exec RoundRuppee @ProfessionTax, @RoundedAmount=@ProfessionTax Output
Exec RoundRuppee @ClubDeduction, @RoundedAmount=@ClubDeduction Output
Exec RoundRuppee @OtherDeductions, @RoundedAmount=@OtherDeductions Output
Exec RoundRuppee @EmployeeEPF, @RoundedAmount=@EmployeeEPF Output
Exec RoundRuppee @EmployeeFPF, @RoundedAmount=@EmployeeFPF Output
Exec RoundRuppee @EmployerEPF, @RoundedAmount=@EmployerEPF Output
Exec RoundRuppee @EmployerFPF, @RoundedAmount=@EmployerFPF Output
Exec RoundRuppee @TDS, @RoundedAmount=@TDS Output
Exec RoundRuppee @EmployeeESI, @RoundedAmount=@EmployeeESI Output
Exec RoundRuppee @EmployerESI, @RoundedAmount=@EmployerESI Output
Exec RoundRuppee @ESILOP, @RoundedAmount=@ESILOP Output
Exec RoundRuppee @FoodAllowance, @RoundedAmount=@FoodAllowance Output
Exec RoundRuppee @Allowance1, @RoundedAmount=@Allowance1 Output
Exec RoundRuppee @Allowance2, @RoundedAmount=@Allowance2 Output
Exec RoundRuppee @Allowance3, @RoundedAmount=@Allowance3 Output
Exec RoundRuppee @Deduction1, @RoundedAmount=@Deduction1 Output
Exec RoundRuppee @Deduction2, @RoundedAmount=@Deduction2 Output
Exec RoundRuppee @Deduction3, @RoundedAmount=@Deduction3 Output
Exec RoundRuppee @PFforBasicArrears, @RoundedAmount= @PFforBasicArrears output
insert SALARY
(
EmployeeId, BranchId,Catagory, YearMonth, BasicPay, VariableDA,
HRA, SpecialAllowance, CityCompAllowance, VehicleAllowance,
TravelAllowance, FieldAllowance, DailyAllowance, FestivalAllowance,
Arrears,PLEncash,Loans,AdvanceDeduction,LossOfPay,
ProfessionTax, ClubDeduction, OtherDeductions, EmployeeEPF,
EmployeeFPF, EmployerEPF, EmployerFPF,TDS, EmployeeESI,
EmployerESI,ESILOP,FoodAllowance,Allowance1,Allowance2,Allowance3,
Deduction1,Deduction2,Deduction3,
Basic_Arrears,Hra_Arrears,Conv_Arrears,Spl_Arrears, PFArrears
)
values
(
@EmployeeId,@BranchId, @Catagory, @CurYearMonth, @BasicPay,
@VariableDA,
@HRA, @SpecialAllowance, @CityCompAllowance,@VehicleAllowance,
@TravelAllowance,round(@FieldAllowance,0),@DailyAllowance,@FestivalAllowance,
@Arrears,@PLEncash,@MonthCutting,@AdvanceDeduction,@LossOfPay,
@ProfessionTax,@ClubDeduction,@OtherDeductions,@EmployeeEPF,
@EmployeeFPF,@EmployerEPF,@EmployerFPF,@TDS,@EmployeeESI,
@EmployerESI,@ESILOP,@FoodAllowance,@Allowance1,@Allowance2,@Allowance3,
@Deduction1,@Deduction2,@Deduction3,
@Basic_Arrears,@Hra_Arrears,@Conv_Arrears,@Spl_Arrears, @PFforBasicArrears
)
insert into BankofEachEmp(Branchid ,Employeeid ,YearMonth ,Bankid ,BankAcNo) values(@BranchId,@EmployeeId,@CurYearMonth,@Bankid,@BankAcNo)
end
select @BasicPay = 0,
@VariableDA = 0,
@HRA = 0,
@SpecialAllowance = 0,
@CityCompAllowance = 0,
@VehicleAllowance = 0,
@TravelAllowance = 0,
@FieldAllowance = 0,
@DailyAllowance = 0,
@FestivalAllowance = 0,
@Arrears = 0,
@PLEncash = 0,
@MonthCutting = 0,
@AdvanceDeduction = 0,
@LossOfPay = 0,
@ESILOP = 0,
@ProfessionTax = 0,
@ClubDeduction = 0,
@OtherDeductions = 0,
@EmployeeEPF = 0,
@EmployeeFPF = 0,
@EmployerEPF = 0,
@EmployerFPF = 0,
@TDS = 0,
@EmployeeESI = 0,
@EmployerESI = 0,
@HpBasicPay = 0,
@HpVariableDA = 0,
@HpHRA = 0,
@LoanAmount = 0,
@AmountPaid = 0,
@MonthCutting = 0,
@LOPMonthTaken = 0,
@HalfPayMonthTaken = 0,
@ESIMonthTaken = 0,
@Amount = 0,
@AdjArrears = 0,
@Basic_Arrears =0,
@Hra_Arrears = 0,
@Conv_Arrears = 0,
@Spl_Arrears = 0,
@AdjDeductions = 0,
@Temp = 0,
@FoodAllowance =0,
@Allowance1=0,
@Allowance2=0,
@Allowance3=0,
@Deduction1=0,
@Deduction2=0,
@Deduction3=0,
@PFforBasicArrears = 0,
@PFEdited = 0,
@Basic_Arrears = 0,
@Hra_Arrears = 0,
@Conv_Arrears = 0,
@Spl_Arrears = 0
fetch SAL_CUR into
@EmployeeId, @BasicPay, @VariableDA, @HRA,@SpecialAllowance,
@VehicleAllowance, @TravelAllowance, @FestivalAllowance, @DailyAllowance,
@FieldAllowance, @Arrears, @PLEncash, @ProfessionTax,
@OtherDeductions, @AdvanceDeduction,@ClubDeduction,@ESIRequired,@TDS,@Catagory,
@LoanAmount, @AmountPaid, @MonthCutting, @LOPMonthTaken,@HalfPayMonthTaken,
@ESIMonthTaken,@BranchId,@EmployeeEPF,
@EmployeeFPF,@EmployerEPF,@EmployerFPF,@JoinDate,@CityCompAllowance,
@ReleivedDate,@LeftCompany, @Location,
@FoodAllowance,@Allowance1,@Allowance2,
@Allowance3,@Deduction1,@Deduction2,
@Deduction3,@Basic_Arrears,@Hra_Arrears,@Conv_Arrears,@Spl_Arrears,@Bankid ,@BankAcNo
end
-- Added by Arun cs to update the field otherdeductions with the telephone expense in the salary table
--SCH/PAA/24
declare Tel_Cur cursor for
Select Distinct TD.EmployeeId From
TelephoneBill TB,TelephoneBillDetails TD Where
TB.BillId = TD.BillId And TB.BranchId = TD.BranchId And TB.BranchId = @InputBranchId And
TB.YearMonth = @CurYearMonth
Open Tel_Cur
Fetch Tel_Cur Into @EmployeeId
while @@fetch_status =0
begin
Select @TelephoneBillAmount = 0
Select @TelephoneBillAmount= IsNull(sum(TD.AmountPaid - TM.AmountAllowed),0)
From TelephoneBill TB,TelephoneBillDetails TD,TelephoneMaster TM
Where TB.BillId = TD.BillId And TB.BranchId = td.branchid and TD.EmployeeId = TM.EmployeeId And
TB.BranchId = TD.BranchId And
td.telephoneid = tm.telephoneid and
TB.YearMonth = @CurYearMonth And
TB.BranchId = @InputBranchid And
TD.EmployeeId = @employeeid And TD.AmountPaid > 0 And
(TD.AmountPaid - TM.AmountAllowed) >= 0
Group By TD.EmployeeId
Update Salary Set OtherDeductions = IsNull(OtherDeductions,0) --+ @TelephoneBillAmount
Where yearMonth = @CurYearMonth and EmployeeID = @EmployeeId And Branchid = @InputBranchId
fetch Tel_Cur into @EmployeeId
End
Close Tel_Cur
deallocate Tel_Cur
close sal_cur
deallocate sal_cur
End
----AFter executing i get the below message
exec WageGenProcForSCons-2 2
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '2'.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

exec [WageGenProcForSCons-2] 2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

GilaMonster (7/30/2012)
exec [WageGenProcForSCons-2] 2

That will certainly fix the ability to call this but the real issue here is the code itself.

To the OP. Some might actually consider it a good thing you couldn't call this. The performance of this is going to be awful. Cursors are one of the worst performing constructs in tsql and your proc has not 1 but 4 of them!!! And at least 2 of them are nested. :w00t:

I would also recommend you use the new join types instead of the old style joins.

FROM PAYMASTER

,EMPLOYEEINFO

,LOANSUMMARY

,LEAVESUMMARY

WHERE PAYMASTER.EmployeeID = EMPLOYEEINFO.EmployeeId

AND PAYMASTER.EmployeeID = LOANSUMMARY.EmployeeId

AND PAYMASTER.EmployeeID = LEAVESUMMARY.EmployeeId

AND PAYMASTER.BranchID = EMPLOYEEINFO.BranchId

AND PAYMASTER.BranchID = LOANSUMMARY.BranchId

AND PAYMASTER.BranchID = LEAVESUMMARY.BranchId

becomes

FROM PAYMASTER

inner join EMPLOYEEINFO on PAYMASTER.EmployeeID = EMPLOYEEINFO.EmployeeId AND PAYMASTER.BranchID = EMPLOYEEINFO.BranchId

inner join LOANSUMMARY on PAYMASTER.EmployeeID = LOANSUMMARY.EmployeeId AND PAYMASTER.BranchID = LOANSUMMARY.BranchId

inner join LEAVESUMMARY on PAYMASTER.EmployeeID = LEAVESUMMARY.EmployeeId AND PAYMASTER.BranchID = LEAVESUMMARY.BranchId

This is a lot cleaner and easier to read. It prevents accidental cross joins. From the original the table condition were not even grouped together which just makes this so difficult to maintain.

There are dozens and dozens of call to the RoundRuppee scalar udf.

The more I look at this the more I think you need a total rewrite of this thing to make it perform even marginally acceptable. I think the scope of this is probably more than online forum can do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442470 More actions · Answer 3

Sean Lange (7/30/2012)
GilaMonster (7/30/2012)
exec [WageGenProcForSCons-2] 2
That will certainly fix the ability to call this but the real issue here is the code itself.
To the OP. Some might actually consider it a good thing you couldn't call this. The performance of this is going to be awful. Cursors are one of the worst performing constructs in tsql and your proc has not 1 but 4 of them!!! And at least 2 of them are nested. :w00t:
I would also recommend you use the new join types instead of the old style joins.
FROM PAYMASTER
,EMPLOYEEINFO
,LOANSUMMARY
,LEAVESUMMARY
WHERE PAYMASTER.EmployeeID = EMPLOYEEINFO.EmployeeId
AND PAYMASTER.EmployeeID = LOANSUMMARY.EmployeeId
AND PAYMASTER.EmployeeID = LEAVESUMMARY.EmployeeId
AND PAYMASTER.BranchID = EMPLOYEEINFO.BranchId
AND PAYMASTER.BranchID = LOANSUMMARY.BranchId
AND PAYMASTER.BranchID = LEAVESUMMARY.BranchId
becomes
FROM PAYMASTER
inner join EMPLOYEEINFO on PAYMASTER.EmployeeID = EMPLOYEEINFO.EmployeeId AND PAYMASTER.BranchID = EMPLOYEEINFO.BranchId
inner join LOANSUMMARY on PAYMASTER.EmployeeID = LOANSUMMARY.EmployeeId AND PAYMASTER.BranchID = LOANSUMMARY.BranchId
inner join LEAVESUMMARY on PAYMASTER.EmployeeID = LEAVESUMMARY.EmployeeId AND PAYMASTER.BranchID = LEAVESUMMARY.BranchId
This is a lot cleaner and easier to read. It prevents accidental cross joins. From the original the table condition were not even grouped together which just makes this so difficult to maintain.
There are dozens and dozens of call to the RoundRuppee scalar udf.
The more I look at this the more I think you need a total rewrite of this thing to make it perform even marginally acceptable. I think the scope of this is probably more than online forum can do.

I have to agree with Sean. If I was presented with this code at work I would be looking at rewriting it.

Sean, RoundRuppee isn't a scalar udf, it is a stored procedure.

Sean Lange SSC Guru Points: 286609 More actions · Answer 4