Comments by pritam40 - CodeProject

pritam40 11-Dec-16 5:20am View

Deleted

>A==<@@<?@ZBA=<><=>=>@>?@a@?=?<@=<=<c?D==@?<=@?>><<cD==??@>?@?@?oC>=?>>=@??=<rA@=?>@=@<<?<dAA=>>><<><<@=d?>=====?b?>=>@=@=pDB=>@<<<?>=>=>>>>a?===>?@s@?===@>=<?s@@==>=<?>@@A

pritam40 28-Aug-16 1:33am View

Deleted

INSERT INTO tbl_Pay_EmployeeGlobalSalaryDetails(ChangeDate, EgsdPIN, EgsdSbkID,EgsdAmount,EgsdPercentage, EgsdIsComponent, SetDate, UserName)
SELECT @NxtIncDate,b.SbhPin,a.SbhSbkID,a.Amount,(CASE WHEN a.Sbhpercentage>0 THEN cast(a.Sbhpercentage AS VARCHAR) ELSE '' END),(CASE WHEN a.Sbhpercentage>0 THEN 1 ELSE 0 END),GETDATE(),@UserName
FROM #tmpCurrStructure a
LEFT JOIN #tmpCurrStructureFinal b ON a.SbhSbkID = b.SbhSbkID AND a.SbhSlgID = b.SbhSlgID
WHERE b.SbhPin = @PIN

Delete From tbl_Pay_EmployeeWiseSalaryAmountRange WHERE EsarEmpPIN=@PIN AND ESarGroupID = @SalGroupID AND ESarLevelID = @JobLevelNext AND ESarChangeDate = @NxtIncDate
INSERT INTO tbl_Pay_EmployeeWiseSalaryAmountRange(EsarEmpPIN, ESarGroupID,ESarLevelID, ESarChangeDate, ESarEmpGross, ESarMinAmt, ESarMaxAmt,SetDate,UserName)
Values(@PIN,@SalGroupID,@JobLevelNext,@NxtIncDate,@NextGrossTotal,@MinAmt,@MaxAmt, getdate(),@UserName)

Delete FROM StaffSalaryStructureOthers WHERE SssPIN = @PIN
INSERT INTO StaffSalaryStructureOthers(SssPIN, SssSlgID, SssStepID,SssBasicIncreased, SssOtherAmount, SssOtherAmtName, SssConsolidatedYN,SssCashYN, SssLevelAtModify, Setdate, UserName)
SELECT distinct(b.SsdPIN),a.SbhSlgID,a.SbhStepId,0,0,'',b.SssConsolidatedYN,b.SssCashYN,a.SbhJLevelID,GETDATE(),@UserName
FROM #tmpCurrStructure a
LEFT JOIN #tmpPrevStructure b ON a.SbhSbkID = b.SsdSbkID AND a.SbHSlgID = b.SssSlgID WHERE b.SsdPIN = @PIN-- GROUP BY b.SsdPIN

Delete sba FROM AdditionalStaffSalaryBkAmount sba
LEFT OUTER JOIN StaffSalaryDetails ON sba.SbaPIN = StaffSalaryDetails.SsdPIN
AND sba.SbaSbkID = StaffSalaryDetails.SsdSbkID WHERE (StaffSalaryDetails.SsdPIN IS NULL)
AND sba.SbaPIN = @PIN

exec SP_Pay_BackupStaffSalaryStructure @PIN, @NxtIncDate
Delete From tbl_HRD_ConfirmAvailable where hcaConStaffPin=@PIN

DELETE FROM StaffSalaryIncrementDetails WHERE SsdStaffPIN = @PIN AND SsdIncDate = @NxtIncDate
INSERT INTO StaffSalaryIncrementDetails(SsdStaffPIN, SsdLevelID, SsdIncSlno,SsdIncDate, SsdIncAmount, SsIncPercentage, SsdIncNotes, SetDate,UserName)
SELECT @PIN,@JobLevelNext,(CASE WHEN Max(SsdIncSlno) is null THEN 1 ELSE Max(SsdIncSlno) +1 END),@NxtIncDate,isnull(@IncTotalDiff,0),@PerOfIncrement,'',GETDATE(),@UserName
FROM StaffSalaryIncrementDetails WHERE SsdStaffPIN = @PIN

DELETE FROM SelectedStaffsForPercentageWiseIncrement WHERE SsiPIN = @PIN AND SsiSelectedYN = 1

SET @NoOfEmp = @NoOfEmp + 1

DROP TABLE #tmpPrevStructure

FETCH FROM SelectedPinCur INTO @PIN,@JobLevel,@CurStep,@SalGroupID,@JobLevelNext,@PerOfIncrement,@IsPromotion,@IsCarAllwed,@IsGrossWise,@NextGrossTotal,@NextVariableTotal,@NxtIncDate,@IncTotalDiff
END
CLOSE SelectedPinCur
DEALLOCATE SelectedPinCur

GO

pritam40 28-Aug-16 1:31am View

Deleted

INNER JOIN
(
SELECT Max(SbhYear*100+SbhMonth) YearMonth,SbhSlgID, SbhJLevelID
from SalaryBreakupAmountHistory
WHERE SbhYear*100+SbhMonth <= YEAR(@NxtIncDate)*100+MONTH(@NxtIncDate) and SbhSlgID= @SalGroupID and SbhJLevelID= @JobLevelNext and SbhStepId='99'
GROUP BY SbhSlgID,SbhJLevelID
) AS ggr ON ggr.SbhSlgID = gg.SbhSlgID AND ggr.SbhJLevelID = gg.SbhJLevelID and gg.SbhStepId = @NextStep AND gg.SbhYear*100+gg.SbhMonth = ggr.YearMonth

END

INSERT INTO #tmpCurrStructureFinal
SELECT b.SsdPIN,a.* FROM #tmpCurrStructure a
INNER JOIN #tmpPrevStructure b ON a.SbhSbkID = b.SsdSbkId AND a.SbhSlgID = b.SssSlgID
WHERE b.SsdPIN = @PIN

IF cast(@JobLevelNext AS INT)>9
BEGIN
DELETE FROM #tmpCurrStructure
WHERE SbhSlgID = @SalGroupID AND SbhJLevelID = @JobLevelNext AND SbhStepId = @NextStep AND SbhSbkID IN ('007','021')

IF NOT EXISTS(SELECT * FROM #tmpCurrStructureFinal WHERE SbhSlgID = @SalGroupID AND SbhJLevelID = @JobLevelNext AND SbhStepId = @NextStep AND SbhSbkID = '022')
BEGIN
INSERT INTO #tmpCurrStructureFinal(SbhPin, SbhSlgID, SbhJLevelID,SbhStepId, SbhSbkID, Amount, Sbhpercentage)
VALUES(@PIN,@SalGroupID,@JobLevelNext,@NextStep,'022',0,0)
END

END
ELSE IF cast(@JobLevelNext AS INT)=9
BEGIN
IF @IsCarAllwed = 1
BEGIN
DELETE FROM #tmpCurrStructure
WHERE SbhSlgID = @SalGroupID AND SbhJLevelID = @JobLevelNext AND SbhStepId = @NextStep AND SbhSbkID IN ('007','021')
IF NOT EXISTS(SELECT * FROM #tmpCurrStructureFinal WHERE SbhSlgID = @SalGroupID AND SbhJLevelID = @JobLevelNext AND SbhStepId = @NextStep AND SbhSbkID = '022')
BEGIN
INSERT INTO #tmpCurrStructureFinal(SbhPin, SbhSlgID, SbhJLevelID,SbhStepId, SbhSbkID, Amount, Sbhpercentage)
VALUES(@PIN,@SalGroupID,@JobLevelNext,@NextStep,'022',0,0)
END
END
ELSE
BEGIN
DELETE FROM #tmpCurrStructure
WHERE SbhSlgID = @SalGroupID AND SbhJLevelID = @JobLevelNext AND SbhStepId = @NextStep AND SbhSbkID IN ('007','022')
IF NOT EXISTS(SELECT * FROM #tmpCurrStructureFinal WHERE SbhSlgID = @SalGroupID AND SbhJLevelID = @JobLevelNext AND SbhStepId = @NextStep AND SbhSbkID = '021')
BEGIN
INSERT INTO #tmpCurrStructureFinal(SbhPin, SbhSlgID, SbhJLevelID,SbhStepId, SbhSbkID, Amount, Sbhpercentage)
VALUES(@PIN,@SalGroupID,@JobLevelNext,@NextStep,'021',0,0)
END

END
END
ELSE
BEGIN
DELETE FROM #tmpCurrStructure
WHERE SbhSlgID = @SalGroupID AND SbhJLevelID = @JobLevelNext AND SbhStepId = @NextStep AND SbhSbkID IN ('021','022')
IF NOT EXISTS(SELECT * FROM #tmpCurrStructureFinal WHERE SbhSlgID = @SalGroupID AND SbhJLevelID = @JobLevelNext AND SbhStepId = @NextStep AND SbhSbkID = '007')
BEGIN
INSERT INTO #tmpCurrStructureFinal(SbhPin, SbhSlgID, SbhJLevelID,SbhStepId, SbhSbkID, Amount, Sbhpercentage)
VALUES(@PIN,@SalGroupID,@JobLevelNext,@NextStep,'007',0,0)
END

END

DELETE FROM StaffSalaryDetails WHERE SsdPIN = @PIN
INSERT INTO StaffSalaryDetails(SsdPIN, SsdSbkID, SsdIncreasedAmt, SetDate,UserName)
SELECT b.SbhPin,a.SbhSbkID,0,GETDATE(),@UserName
FROM #tmpCurrStructure a
LEFT JOIN #tmpCurrStructureFinal b ON a.SbhSbkID = b.SbhSbkID AND a.SbhSlgID = b.SbhSlgID
WHERE b.SbhPin = @PIN

Delete From AdditionalStaffSalaryBkAmount WHERE SbaPIN=@PIN
INSERT INTO AdditionalStaffSalaryBkAmount(SbaPIN, SbaSbkID, SbaAmount,SbaPayRatio, sbaPercentage, SbaNotes, SetDate, UserName)
SELECT b.SbhPIN,a.SbhSbkID,a.Amount,100,0,'',GETDATE(),@UserName FROM #tmpCurrStructure a
LEFT JOIN #tmpCurrStructureFinal b ON a.SbhSbkID = b.SbhSbkID AND a.SbhSlgID = b.SbhSlgID
WHERE b.SbhPin = @PIN

Delete From tbl_Pay_EmployeeGlobalSalaryDetails WHERE EgsdPIN=@PIN AND ChangeDate = @NxtIncDate
INSERT INTO tb

pritam40 28-Aug-16 1:30am View

Deleted

INNER JOIN StaffStatus b on ssPIN=SsdPIN
Inner Join StaffJobStatus on StaffJobStatus.SjsPIN=SsdPIN
Inner Join StaffPromotion on b.SsPIN =StaffPromotion.SpPIN
INNER JOIN StaffSalaryStructureOthers_History c ON Ssd.SsdPIN = c.SssPIN And StaffPromotion.SpJLevelID = c.SssLevelAtModify
INNER JOIN SalaryBreakupAmount d ON c.SssSlgID=d.SbaSlgID AND StaffPromotion.SpJLevelID =d.SbaJLevelID AND SsdSbkID=d.SbaSbkID and c.SssStepId = d.SbaStepId
LEFT JOIN AdditionalStaffSalaryBkAmount_History i ON Ssd.SsdPIN = i.SbaPIN AND i.SbaSbkID = Ssd.SsdSbkID
and i.ChangeDate=c.ChangeDate and i.SbaStatus <> 'D'
WHERE c.ChangeDate = (Select Max(ChangeDate) from StaffSalaryStructureOthers_History Where SssPIN=StaffJobStatus.SjsPIN and StaffSalaryStructureOthers_History.ChangeDate <=@NxtIncDate )
And StaffJobStatus.SjsEffectiveDate = (Select max(SjsEffectiveDate) from StaffJobStatus Where SssPIN=StaffJobStatus.SjsPIN and SjsEffectiveDate < = @NxtIncDate)
And Ssd.ChangeDate = (Select Max(ChangeDate) from StaffSalaryDetails_History Where SsdPIN=StaffJobStatus.SjsPIN and StaffSalaryDetails_History.ChangeDate <=@NxtIncDate)
And StaffPromotion.SpEffectiveDate=(Select max(SpEffectiveDate) from StaffPromotion Where SssPIN=StaffPromotion.SpPIN and SpEffectiveDate <= @NxtIncDate)
AND Ssd.SsdPIN = @PIN

---Calculate All breaukup Amount

SET @AmountToDistribute = 0
SET @TotalFixed = 0

IF cast(@JobLevelNext AS INT)>=9
BEGIN
SELECT @TotalFixed=Sum(gg.SbhConfirmed)
FROM SalaryBreakupAmountHistory gg
INNER JOIN
(
SELECT Max(SbhYear*100+SbhMonth) YearMonth,SbhSlgID, SbhJLevelID
from SalaryBreakupAmountHistory
WHERE SbhYear*100+SbhMonth <= YEAR(@NxtIncDate)*100+MONTH(@NxtIncDate) and SbhSlgID= @SalGroupID and SbhJLevelID= @JobLevelNext and SbhStepId='99'
GROUP BY SbhSlgID,SbhJLevelID
) AS ggr ON ggr.SbhSlgID = gg.SbhSlgID AND ggr.SbhJLevelID = gg.SbhJLevelID and gg.SbhStepId = @NextStep AND gg.SbhYear*100+gg.SbhMonth = ggr.YearMonth
WHERE gg.SbhSbkID NOT IN ('007','022','021')

set @AmountToDistribute=@NextGrossTotal-@TotalFixed

INSERT INTO #tmpCurrStructure
SELECT gg.SbhSlgID,gg.SbhJLevelID,gg.SbhStepId,gg.SbhSbkID,Amount = (CASE WHEN gg.Sbhpercentage>0 THEN round(@AmountToDistribute*(gg.Sbhpercentage/100),0) ELSE gg.SbhConfirmed END),gg.Sbhpercentage
FROM SalaryBreakupAmountHistory gg
INNER JOIN
(
SELECT Max(SbhYear*100+SbhMonth) YearMonth,SbhSlgID, SbhJLevelID
from SalaryBreakupAmountHistory
WHERE SbhYear*100+SbhMonth <= YEAR(@NxtIncDate)*100+MONTH(@NxtIncDate) and SbhSlgID= @SalGroupID and SbhJLevelID= @JobLevelNext and SbhStepId='99'
GROUP BY SbhSlgID,SbhJLevelID
) AS ggr ON ggr.SbhSlgID = gg.SbhSlgID AND ggr.SbhJLevelID = gg.SbhJLevelID and gg.SbhStepId = @NextStep AND gg.SbhYear*100+gg.SbhMonth = ggr.YearMonth
--WHERE gg.SbhSbkID NOT IN ('007','022','021')

END
ELSE
BEGIN
SELECT @TotalFixed=Sum(gg.SbhConfirmed)
FROM SalaryBreakupAmountHistory gg
INNER JOIN
(
SELECT Max(SbhYear*100+SbhMonth) YearMonth,SbhSlgID, SbhJLevelID
from SalaryBreakupAmountHistory
WHERE SbhYear*100+SbhMonth <= YEAR(@NxtIncDate)*100+MONTH(@NxtIncDate) and SbhSlgID= @SalGroupID and SbhJLevelID= @JobLevelNext and SbhStepId='99'
GROUP BY SbhSlgID,SbhJLevelID--,SbhSbkID
) AS ggr ON ggr.SbhSlgID = gg.SbhSlgID AND ggr.SbhJLevelID = gg.SbhJLevelID and gg.SbhStepId = @NextStep AND gg.SbhYear*100+gg.SbhMonth = ggr.YearMonth

set @AmountToDistribute=@NextGrossTotal-@TotalFixed

INSERT INTO #tmpCurrStructure
SELECT gg.SbhSlgID,gg.SbhJLevelID,gg.SbhStepId,gg.SbhSbkID,Amount = (CASE WHEN gg.Sbhpercentage>0 THEN round(@AmountToDistribute*(gg.Sbhpercentage/100),0) ELSE gg.SbhConfirmed END),gg.Sbhpercentage
FROM SalaryBreakupAmountHistory gg
INNER JOIN
(
SELECT Max(SbhYear*100+SbhMonth) YearMonth,SbhSlgID, SbhJLevelID
from SalaryBreakupAmountHi

pritam40 28-Aug-16 1:24am View

Deleted

/****** Object: StoredProcedure [dbo].[SP_Pay_IncreaseBasic] Script Date: 08/02/2016 10:33:54 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SP_Pay_IncreaseBasic]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_Pay_IncreaseBasic]
GO

/****** Object: StoredProcedure [dbo].[SP_Pay_IncreaseBasic] Script Date: 08/02/2016 10:33:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*

BEGIN TRAN
declare @P1 int
set @P1=0
exec SP_Pay_IncreaseBasic 'Aug 18 2016 12:00:00:000AM', '', 'admin', 1, '%', @P1 output
select @P1
ROLLBACK tran
*/

-----------------------------------------------------------
-- Type: Stored Procedure
-- Name: SP_Pay_IncreaseBasic
-----------------------------------------------------------
CREATE PROCEDURE [dbo].[SP_Pay_IncreaseBasic]
@IncDate datetime,
@Notes nvarchar(2000),
@UserName nvarchar(20),
@NoOfInc tinyint,
@JLevel VARCHAR(3),
@NoOfEmp int OUTPUT
--With Encryption
AS

DECLARE @PIN NCHAR(8)
DECLARE @JobLevel NCHAR(3)
DECLARE @CurStep NCHAR(2)
DECLARE @NextStep NCHAR(2)
DECLARE @JobLevelNext NCHAR(3)
DECLARE @PerOfIncrement FLOAT
DECLARE @IsPromotion BIT
DECLARE @IsCarAllwed BIT
DECLARE @IsGrossWise BIT
DECLARE @NextGrossTotal MONEY
DECLARE @NextVariableTotal MONEY
DECLARE @NxtIncDate DATETIME
DECLARE @IncSLNo INT
DECLARE @IncTotalDiff MONEY
DECLARE @SalGroupID NCHAR(2)
Declare @TotalFixed Money
Declare @AmountToDistribute MONEY
DECLARE @MinAmt MONEY
DECLARE @MaxAmt MONEY

SET @NoOfEmp = 0
SET @NextStep = '99'
SET @AmountToDistribute = 0
SET @TotalFixed = 0
SET @MinAmt = 0
SET @MaxAmt = 0

--gg.SbhSlgID,gg.SbhJLevelID,gg.SbhStepId,gg.SbhSbkID,Amount,gg.Sbhpercentage
Create table #tmpCurrStructure(
SbhSlgID NCHAR(2),
SbhJLevelID NCHAR(3),
SbhStepId NCHAR(2),
SbhSbkID NCHAR(3),
Amount Money,
Sbhpercentage Money
)

Create table #tmpCurrStructureFinal(
SbhPin VARCHAR(8),
SbhSlgID NCHAR(2),
SbhJLevelID NCHAR(3),
SbhStepId NCHAR(2),
SbhSbkID NCHAR(3),
Amount Money,
Sbhpercentage Money
)

DECLARE @CarMainID AS NCHAR(3)
SELECT @CarMainID = b.tsbSbkID FROM Pay_Tax_RequiredInfo a INNER JOIN tbl_Pay_TempSalaryBreakup b ON a.CarAllowanceBkID=b.tsbSbkID

DECLARE SelectedPinCur CURSOR FOR
SELECT SsiPIN,SsiJobLevel,SsiCurStep,SsiSlgID,SsiNextJobLevel,SsiPerOfIncrement,SsiIsPromotion,SsiIsCarAllwed,SsiIsGrossWise,SsiNextGrossTotal,SsiNextVariableTotal, SsiNextIncDate,SsiIncTotalDiff
FROM SelectedStaffsForPercentageWiseIncrement
WHERE SsiSelectedYN = 1 AND SsiJobLevel LIKE @JLevel AND SsiNextIncDate>='01-Jul-2016' --As the new business has been incorporated from 01072016 thats why hard coded date has been placed
OPEN SelectedPinCur
FETCH FROM SelectedPinCur INTO @PIN,@JobLevel,@CurStep,@SalGroupID,@JobLevelNext,@PerOfIncrement,@IsPromotion,@IsCarAllwed,@IsGrossWise,@NextGrossTotal,@NextVariableTotal,@NxtIncDate,@IncTotalDiff
WHILE @@FETCH_STATUS = 0
BEGIN
SET @IncSLNo = 0
DELETE FROM #tmpCurrStructure
DELETE FROM #tmpCurrStructureFinal
Select Top 1 @MinAmt= ISNULL(SarMinAmt,0), @MaxAmt = isnull(SarMaxAmt,0) from tbl_Pay_SalaryAmountRange
where SarGroupID = @SalGroupID and SarLevelID= @JobLevelNext and (SarYear*100)+SarMonth<= (Year(@NxtIncDate)*100) + Month(@NxtIncDate) order by (SarYear*100)+SarMonth DESC

SELECT Ssd.SsdPIN,Ssd.SsdSbkID,SbkAmt=isnull(i.SbaAmount,(Case b.SsStatusID When 'C' Then d.SbaConfirmed When 'N' Then d.SbaNonConfirmed End))+isnull(Ssd.SsdIncreasedAmt,0)
,c.SssSlgID,c.SssConsolidatedYN,c.SssCashYN INTO #tmpPrevStructure
FROM StaffSalaryDetails_History Ssd
INNER JOIN SalaryBreakup Sbk on Sbk.SbkID=Ssd.SsdSbkID --AND Sbk.SbkID <> @CarMainID --Added by Gomes regarding *Gomes-1
INNER JOIN StaffStatus b on ssPIN=SsdPIN
Inner Join StaffJobStatus on StaffJobStatus.SjsPIN=SsdPIN
Inner Join StaffPromotion on b.SsPIN =StaffPromotion.SpPIN
INNER JOIN StaffSalaryStructureOthers_History c ON Ssd.SsdPIN = c.SssPIN And StaffPr

pritam40 23-Aug-15 4:36am View

Deleted

if @NextEffectiveDate = @CurMonLastDate AND @StructureEffectiveDate IS NULL
BEGIN
IF @SumOfWorkingDay = 29 Set @CalDays = @CalDays +1
ELSE IF @SumOfWorkingDay = 28 Set @CalDays = @CalDays +2
ELSE IF @SumOfWorkingDay = 27 Set @CalDays = @CalDays + 3
END
IF @RemainWorkingDay >= @CalDays
Set @RemainWorkingDay = @RemainWorkingDay - @CalDays
ELSE
Set @RemainWorkingDay = 0 --@RemainWorkingDay - @CalDays
SELECT @MultipleLFAAmount = dbo.GetBreakupAmtWithSpecificYearMonth(@StaffPin,@LastEffectiveDate,@LFABreakup)

Set @TotalMultipleLFAAmount= @TotalMultipleLFAAmount + isnull(@MultipleLFAAmount,0)*@CalDays/@DaysInMonth
End
Fetch NEXT FROM Cursor_MultipleStaffBreakUp into @LastEffectiveDate
End

Close Cursor_MultipleStaffBreakUp
Deallocate Cursor_MultipleStaffBreakUp
UPDATE #FinalStaffUnderLFA SET LFAAmount = @TotalMultipleLFAAmount WHERE LFAPin = @StaffPin
Fetch NEXT FROM Cursor_MultipleStaff into @StaffPin

End
Close Cursor_MultipleStaff
Deallocate Cursor_MultipleStaff
---------------------------------------------End Multiple LFA------------------------

pritam40 23-Aug-15 4:35am View

Deleted

---For Multiple structure LFA---------------------------------
Insert into @TempMultipleBreakupStaffTable
Select distinct LFAPin,ChangeDate From #FinalStaffUnderLFA inner join StaffSalaryStructureOthers_History on SssPIN = LFAPin
INNER JOIN staff s ON s.StaPIN = LFAPin
Where ((ChangeDate between @CurMonFirstDate and @CurMonLastDate) --and JoiningDate < ChangeDate
)

DECLARE @StaffJoinDate AS DATETIME
Declare @LastStructureChangedate smallDatetime
Declare @NextEffectiveDate as smalldatetime
Declare @LastEffectiveDate smalldatetime
Declare @RemainWorkingDay tinyint
Declare @CalDays tinyint
Declare @vLastEffectiveDate smalldatetime
Declare @vLastStatusEffectiveDate smalldatetime
Declare @MultipleLFAAmount Numeric(14,4)
Declare @TotalMultipleLFAAmount Numeric(14,4)
Declare @SumOfWorkingDay TINYINT
Declare @StructureEffectiveDate as SMALLDATETIME
Set @vLastStatusEffectiveDate=null
SET @SumOfWorkingDay = 0

SET @TotalMultipleLFAAmount = 0
SET @MultipleLFAAmount = 0
Set @RemainWorkingDay=0
Set @CalDays=0
Set @LastStructureChangedate=NULL

Set @RemainWorkingDay = isnull(@WorkingDays,day(@CurMonLastDate))

Declare Cursor_MultipleStaff CURSOR FOR
SELECT distinct PinID FROM @TempMultipleBreakupStaffTable
OPEN Cursor_MultipleStaff
Fetch NEXT FROM Cursor_MultipleStaff into @StaffPin

WHILE @@FETCH_STATUS = 0
Begin
SELECT @StaffJoinDate = StaJoinDate FROM Staff WHERE StaPIN = @StaffPin
IF @StaffJoinDate> @CurMonFirstDate SET @CurMonFirstDate = @StaffJoinDate

Select @LastStructureChangedate= max(ChangeDate) from StaffSalaryStructureOthers_History
Where SssPIN = @StaffPin and cast(cast(ChangeDate as nchar(12)) as smalldatetime) <= @CurMonFirstDate

if Isnull(@LastStructureChangedate,'') = ''
Select @LastStructureChangedate= min(ChangeDate) from StaffSalaryStructureOthers_History
Where SssPIN = @StaffPin and cast(cast(ChangeDate as nchar(12)) as smalldatetime) > @CurMonFirstDate

Declare Cursor_MultipleStaffBreakUp CURSOR FOR
SELECT ChangeDate FROM StaffSalaryStructureOthers_History Where SssPIN=@StaffPin and ChangeDate between @LastStructureChangedate and @CurMonLastDate order By ChangeDate Asc

OPEN Cursor_MultipleStaffBreakUp
Fetch NEXT FROM Cursor_MultipleStaffBreakUp into @LastEffectiveDate

While @@fetch_Status=0
BEGIN
SELECT @NextEffectiveDate= min(ChangeDate) FROM StaffSalaryStructureOthers_History
Where SssPIN=@StaffPin and cast(cast(ChangeDate as nchar(12)) as smalldatetime) > @LastEffectiveDate

SET @StructureEffectiveDate = @NextEffectiveDate -- added by Pritam

if @NextEffectiveDate > @CurMonLastDate
Set @NextEffectiveDate = @CurMonLastDate

Set @NextEffectiveDate = isnull(@NextEffectiveDate,@CurMonLastDate)
if month(@LastEffectiveDate) = month(@CurMonFirstDate) and year(@LastEffectiveDate) = year(@CurMonFirstDate)
Set @vLastEffectiveDate = @CurMonFirstDate
else
Set @vLastEffectiveDate = @NextEffectiveDate--@LastEffectiveDate

if @LastEffectiveDate < @CurMonFirstDate
Set @vLastEffectiveDate = @CurMonFirstDate
else
Set @vLastEffectiveDate = @LastEffectiveDate

if @RemainWorkingDay <> 0
Begin
if month(@NextEffectiveDate) <> month (@CurMonFirstDate) and Year(@NextEffectiveDate) = Year(@CurMonFirstDate)
Set @CalDays = @RemainWorkingDay
else
Set @CalDays = day(@NextEffectiveDate) -day(@vLastEffectiveDate)

if @CalDays >@RemainWorkingDay
Set @CalDays=@RemainWorkingDay

set @SumOfWorkingDay = @SumOfWorkingDay + @CalDays

if @NextEffectiveDate = @CurMonLastDate AND @StructureEffectiveDate IS NULL
BEGIN
IF @SumOfWorkingDay = 29 Set @CalDays = @CalDays +1
ELSE IF @Sum

pritam40 9-Apr-15 1:55am View

Deleted

UNION ALL
SELECT spin,sname,sdesignation,LonNo,SanctionAmt,disversdate,LoanId,LoanName+' ('+LoanId+')' as LoanName
,FlatYN=case when LnsFlatYN=1 then 'Yes' else 'No' end
,FlatIntPercent=case when LnsFlatYN=1 then 'Flat Int. Rate   : '+cast(LnsFlatIntPercent as varchar) else '' end
,LnsReduceIntPercent,year(l.LfrGenDate) fyear, MONTH(l.LfrGenDate) fmonth,l.LfrPrinOutstanding AS openbal,
 (l.LfrPrinOutstanding+l.LfrIntOutstanding) as installment,l.LfrRealizationAmt-l.LfrPrinOutstanding Interest,
 l.LfrPrinOutstanding principal, 0 as DisburseAmt, (l.LfrPrinOutstanding+l.LfrIntOutstanding-l.LfrRealizationAmt) closebal,a.stlCompoundInterval
  FROM #StaffInfo a
inner join LnLoanFinalRealization l ON a.LonNo = l.LfrStlSlNo
WHERE LfrStlSlNo = @LoanNO
ORDER by fyear,fmonth

pritam40 16-Feb-15 4:16am View

Deleted

begin tran
select * from tbl_Leave_GroupWiseLeaveTypeSetup where GlsLvTypeId in('13','14','15','16')
delete from tbl_Leave_GroupWiseLeaveTypeSetup where GlsLvTypeId in('13','14','15','16')

select * from tbl_Leave_YearlyStaffCLeaveStatusHistory where LshLvTypeID in('13','14','15','16')
delete from tbl_Leave_YearlyStaffCLeaveStatusHistory where LshLvTypeID in('13','14','15','16')

select * from tbl_Leave_StaffCLeaveStatus where SlsLvTypeID in('13','14','15','16')
delete from tbl_Leave_StaffCLeaveStatus where SlsLvTypeID in('13','14','15','16')

select * from tbl_leave_LeaveTypeSetUp where LtsLvTypeID in('13','14','15','16')
delete from tbl_leave_LeaveTypeSetUp where LtsLvTypeID in('13','14','15','16')
update tbl_leave_LeaveTypeSetUp set LtsExcessBalCalYN = 1 where LtsLvTypeID in('01','02')

select * from tbl_leave_BaseLeaveType where bltBaseLvTypeID in ('03','08')
delete from tbl_leave_BaseLeaveType where bltBaseLvTypeID in ('03','08')

rollback

pritam40 16-Feb-15 4:09am View

Deleted

else IF @JobBaseID in ('C','0')
begin
Set @NewCurrentBalance = @CurrentBalance
SET @NewExcessBalance = 0
end --End Job Base Condition 0,C

DELETE From tbl_Leave_StaffCLeaveStatus WHERE slsPIN = @CurPIN AND slsLvTypeID = @CurrentLeaveID
INSERT INTO tbl_Leave_StaffCLeaveStatus( slsPIN, slsLvTypeID, slsEwdId, slsLvBalance , slsExcessLvBalance,SetDate ) VALUES
(@CurPIN, @CurrentLeaveID, @WorkingDay, @NewCurrentBalance, @NewExcessBalance,getdate())

DELETE FROM tbl_Leave_YearlyStaffCLeaveStatusHistory WHERE LshPIN = @CurPIN AND LshYear = @LeaveYear AND LshLvTypeID = @CurrentLeaveID
INSERT INTO tbl_Leave_YearlyStaffCLeaveStatusHistory( LshPIN,LshYear,LshLvTypeID, LshBeginnBal, LshBegExcessBal , LshEndBal, LshEndExcessBal,SetDate) VALUES
(@CurPIN, @LeaveYear, @CurrentLeaveID, @NewCurrentBalance, @NewExcessBalance, 0,0,getdate())

FETCH Next FROM CurAllLeave INTO @CurrentLeaveID, @YearlyAllowDays , @BalanceForwardYN , @MaxBalance ,@ExcessBalCalYN ,
@DaysForContSanction ,@MaxSanctServiceLife ,@InterValInSameLeave, @EarnYN, @EarnEarlyYN, @MaternityYN
END -- CurAllLeave end

CLOSE CurAllLeave
DEALLOCATE CurAllLeave

FETCH Next FROM CurAllPIN INTO @CurPIN,@StatusID,@JobBaseID,@WorkingDay,@Gender
END --CurAllPIN end

CLOSE CurAllPIN
DEALLOCATE CurAllPIN

SET @NextYearDate = DATEADD(yyyy,1,@YearEndDate)

UPDATE BranchModuleFor SET LeaveYearEndDate = @NextYearDate

GO

pritam40 16-Feb-15 4:08am View

Deleted

DECLARE CurAllPIN CURSOR FOR
SELECT * FROM @SelectedStaffForLeave
OPEN CurAllPIN
FETCH Next FROM CurAllPIN INTO @CurPIN,@StatusID,@JobBaseID,@WorkingDay,@Gender
WHILE @@Fetch_Status = 0
BEGIN
set @NextYearLeave = 0
DECLARE CurAllLeave CURSOR FOR
SELECT L.LtsLvTypeID, L.LtsYearlyAllowDays, L.LtsBalanceForwardYN, L.LtsMaxBalance, L.LtsExcessBalCalYN,
L.LtsDaysForContSanction, L.LtsMaxSanctServiceLife, L.LtsIntervalInSameLeave, B.BltEarnYN, B.BltEarnEarlyYN,B.BltMaternityYN
FROM tbl_leave_StaffLeaveGroupMapping M
Inner Join tbl_leave_GroupWiseLeaveTypeSetup G ON G.GlsLvGroupID = M.sgmlgsLvGroupID
Inner Join tbl_leave_LeaveTypeSetUp L ON G.GlsLvTypeId = L.LtsLvTypeID
Inner Join tbl_leave_BaseLeaveType B ON L.ltsBaseTypeID = B.bltBaseLvTypeID
inner join tbl_Leave_StaffCLeaveStatus sls on sls.SlsPin = m.SgmPin and sls.SlsLvTypeID=l.LtsLvTypeID
WHERE M.SgmPIN = @CurPIN
OPEN CurAllLeave
SET @InterValInSameLeave = 0
SET @MaxSanctServiceLife = 0
FETCH Next FROM CurAllLeave INTO @CurrentLeaveID, @YearlyAllowDays , @BalanceForwardYN , @MaxBalance ,@ExcessBalCalYN ,
@DaysForContSanction ,@MaxSanctServiceLife ,@InterValInSameLeave, @EarnYN, @EarnEarlyYN, @MaternityYN

WHILE @@fetch_Status = 0
BEGIN
SET @InterValInSameLeave = 0
SET @MaxSanctServiceLife = 0
SET @NewCurrentBalance = 0
Set @NewExcessBalance = 0

SELECT @NextYearLeave = isnull(Sum(SltWdaysEnjoyed),0) FROM StaffLeaveTaken slt WHERE slt.SltPIN = @CurPIN
AND slt.SltLvTypeID = @CurrentLeaveID AND slt.SltLAppCancelYN = 0 AND Year(slt.SltFrom) = @LeaveYear

AND YEAR(slt.SltTo) = @LeaveYear
SET @CurrentBalance = NULL
SET @ExcessBalance = NULL
SELECT @CurrentBalance = SlsLvBalance , @ExcessBalance =SlsExcessLvBalance FROM tbl_Leave_StaffCleaveStatus WHERE SlsPIN = @CurPIN AND SlsLvTypeID = @CurrentLeaveID
IF @CurrentBalance IS NULL SET @CurrentBalance = 0
IF @ExcessBalance IS NULL SET @ExcessBalance = 0

IF EXISTS( SELECT LshPIN FROM tbl_Leave_YearlyStaffCLeaveStatusHistory WHERE LshPIN = @CurPIN AND LshYear = @LastYear AND LshLvTypeID = @CurrentLeaveID)
UPDATE tbl_Leave_YearlyStaffCLeaveStatusHistory
SET LshEndBal = @CurrentBalance, LshEndExcessBal = @ExcessBalance, SetDate = getdate()
WHERE LshPIN = @CurPIN AND LshLvTypeID = @CurrentLeaveID AND LshYear = @LastYear

ELSE
INSERT INTO tbl_Leave_YearlyStaffCLeaveStatusHistory( LshPIN,LshYear,LshLvTypeID, LshBeginnBal, LshBegExcessBal , LshEndBal, LshEndExcessBal,SetDate) VALUES
(@CurPIN, @LastYear, @CurrentLeaveID, 0,0, @CurrentBalance, @ExcessBalance,getdate())
IF @JobBaseID in ('R','O','S')
BEGIN
IF @BalanceForwardYN = 1
BEGIN
IF @CurrentBalance + @YearlyAllowDays > @MaxBalance
BEGIN
SET @NewCurrentBalance = @MaxBalance
SET @NewExcessBalance = (@CurrentBalance + @YearlyAllowDays) - @MaxBalance
END
ELSE BEGIN
SET @NewCurrentBalance = @CurrentBalance + @YearlyAllowDays
SET @NewExcessBalance = 0
END

SET @NewExcessBalance = @NewExcessBalance + @ExcessBalance

End --End BalanceForwardYN
else
begin
SET @NewCurrentBalance = @YearlyAllowDays
SET @NewExcessBalance = 0
end --End Else BalanceForwardYN
end --End JobBaseId Condition R,O,S

pritam40 16-Feb-15 4:06am View

Deleted

/****** Object: StoredProcedure [dbo].[SP_Leave_UpdateYearlyLeaveBalance] Script Date: 11/24/2014 11:38:26 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Leave_UpdateYearlyLeaveBalance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_Leave_UpdateYearlyLeaveBalance]
GO

/****** Object: StoredProcedure [dbo].[SP_Leave_UpdateYearlyLeaveBalance] Script Date: 11/24/2014 11:38:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
BEGIN TRAN
exec SP_Leave_UpdateYearlyLeaveBalance
select * from tbl_Leave_StaffCLeaveStatus where SlsPin = '00000053'
select * from tbl_Leave_YearlyStaffCLeaveStatusHistory where lshPin = '00000053'
ROLLBACK
*/
-----------------------------------------------------------
-- Type: Stored Procedure
-- Name: SP_Leave_UpdateYearlyLeaveBalance
-----------------------------------------------------------

--DEVELOPED GAFFAR SHAHIN
--AT THE END OF THE YEAR THIS STORED PROCEDURE UPDATES LEAVE BALANCE
--Modified by JK Dated: 02062005

CREATE PROCEDURE [dbo].[SP_Leave_UpdateYearlyLeaveBalance]

--With Encryption
AS

SET Nocount ON
DECLARE @LeaveYear AS SMALLINT,
@YearEndDate DATETIME,
@LastYear AS SMALLINT,
@NextYearDate DATETIME,
@CurrentLeaveID Nchar(2),
@CurPIN AS NCHAR(8),
@YearlyAllowDays NUMERIC(12,8),
@MaxSanctServiceLife smallInt,
@DaysForContSanction smallInt,
@EarnYN BIT,
@EarnEarlyYN BIT,
@BalanceForwardYN BIT,
@MaxBalance smallInt,
@ExcessBalCalYN BIT,
@InterValInSameLeave SMALLINT,
@MaternityYN BIT

DECLARE @CurrentBalance NUMERIC(12,8),
@ExcessBalance NUMERIC(12,8),
@NewCurrentBalance NUMERIC(12,8),
@NewExcessBalance NUMERIC(12,8),
@LastExcessBalance NUMERIC(12,8),
@WorkingDay int

DECLARE @AvailedEarnLeave DECIMAL,
@FirstDateOfYear AS DATETIME,
@LastDateOfYear AS DATETIME,
@StatusID AS NCHAR(1),
@JobBaseID AS NCHAR(1),
@Gender as nchar(1),
@NextYearLeave as decimal

Declare @SelectedStaffForLeave as TABLE (StaffPIN NCHAR(8), StatusID nchar(1), JobBaseID nchar(1), WorkingDay int, Gender nchar(1))

SELECT @YearEndDate=LeaveYearEndDate FROM BranchModuleFor

IF DateDiff(Day,@YearEndDate,getDate()) <0
RETURN

SELECT @LeaveYear=year(@YearEndDate)+1
SELECT @LastYear = @LeaveYear -1

SET @FirstDateOfYear = dbo.MakeDate(year(@YearEndDate),01,1)
SET @LastDateOfYear = dbo.MakeDate(year(@YearEndDate),12,31)

INSERT INTO @SelectedStaffForLeave
select SsPIN,SsStatusID,SsJbID,sls.SlsEwdID,s.StaSex from StaffStatus ss
inner join staff s on s.StaPIN=ss.SsPIN
inner join tbl_Leave_StaffLeaveGroupMapping a on a.SgmPin = SsPIN
inner join (
select distinct slspin,SlsEwdID from tbl_Leave_StaffCLeaveStatus
) sls on sls.SlsPin = ss.SsPIN
where SsStatusID in ('C','N') and SsJbID in ('R','O','S','C','0') order by SsPIN

pritam40 2-Feb-15 1:18am View

Deleted

BEGIN TRAN

Declare @CollectionDate AS Datetime
Declare @Year AS INT
DECLARE @Month AS INT
Declare @StaffPIN AS VARCHAR(8)
Declare @StaffSlNo AS INT
DECLARE @LastOutStanding AS MONEY
DECLARE @LastColDate AS DATETIME
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @LoopDate AS DATETIME

SET @StaffPIN = '00000137'
SET @StaffSlNo = 12

--exec Sp_Loan_IndStaffLoanRealizationReport @StaffSlNo,NULL
SELECT * FROM LnLoanCollectionStatus llcs WHERE llcs.LcsStlSlNo = @StaffSlNo
delete FROM LnLoanCollectionStatus WHERE LcsStlSlNo = @StaffSlNo
SELECT @LastColDate = lldd.LddDisburseDate, @LastOutStanding = lldd.LddDisburseAmt FROM LnLoanDisbursementDetails lldd WHERE lldd.LddStlSlNo = @StaffSlNo
UPDATE LnStaffLoan SET StlLastPrinOutStanding = @LastOutStanding , StlLastPrinOutDate = @LastColDate WHERE StlSlNo=@StaffSlNo AND StlPIN=@StaffPIN

set @StartDate = @LastColDate
SET @EndDate ='31-Jan-2015'
SET @LoopDate = @LastColDate

WHILE (@EndDate>@LoopDate)
BEGIN

set @Year = Year(@LoopDate)
set @Month = MONTH(@LoopDate)
SET @CollectionDate = dbo.MakeDate(@Year,@Month,dbo.DaysOfMonth(@Year,@Month))
declare @p10 int
set @p10=1
exec SP_Loan_StaffLoanCollection_AsDBL @CollectionDate,'Support',@Year,@Month,@StaffPIN,@StaffSlNo,NULL,360,NULL,@p10 OUTPUT
if(SELECT isnull(mph.MpoLoanReaAmount,0) FROM MonthlyPSOthersHistory mph WHERE mph.MpoPIN = @StaffPIN AND mph.MpoYear=@Year AND mph.MpoMonth=@Month)>0
BEGIN
UPDATE LnLoanCollectionStatus SET LcsPickupYN = 1 WHERE LcsStlSlNo = @StaffSlNo AND LcsYear=@Year AND LcsMonth=@Month
END
ELSE IF (SELECT isnull(CmpLoanReaAmount,0) FROM CurrentMonPSOthers WHERE CmpPIN = @StaffPIN AND CmpYear=@Year AND CmpMonth=@Month)>0
BEGIN
UPDATE LnLoanCollectionStatus SET LcsPickupYN = 1 WHERE LcsStlSlNo = @StaffSlNo AND LcsYear=@Year AND LcsMonth=@Month
END
--SELECT @Year,@Month,@CollectionDate

SET @LoopDate=DATEADD(MONTH,1,@LoopDate)
END
SELECT * FROM LnLoanCollectionStatus llcs WHERE llcs.LcsStlSlNo = @StaffSlNo
ROLLBACK TRAN

pritam40 28-Jan-15 4:29am View

Deleted

insert into #AreaDivDeptCalculation Values(@AID,@DID,@DpID,@NextDate,@TotalAbsent,@TotalLeave,@TotalLate,@TotalPresent,@TotalWeekend,@TotalStaff)
--Create Table #AreaDivDeptCalculation(AreaId varchar(4),DivisionID NVARCHAR(4),ProjectID NVARCHAR(3),MonDate datetime, AbsentStaff int, LeaveStaff int,LateStaff int,PresentStaff int, WeekendStaff int,TotalStaff int)
Set @NextDate = DateAdd(d,1,@NextDate)

End

FETCH NEXT FROM AreaDivDept_Cursor
INTO @AID,@DID,@DpID
End

CLOSE AreaDivDept_Cursor
DEALLOCATE AreaDivDept_Cursor

--Select * from #AreaDivDeptCalculation

--Select AreaId,DivisionID,DeptID,MonDate,ROUND(AbsentStaff*100/TotalStaff,2) AbsentStaff,ROUND(LeaveStaff*100/TotalStaff,2) LeaveStaff,ROUND(LateStaff*100/TotalStaff,2) LateStaff,ROUND(PresentStaff*100/TotalStaff,2) PresentStaff,ROUND(WeekendStaff*100/TotalStaff,2) WeekendStaff,Avg(TotalStaff)TotalStaff from #AreaDivDeptCalculation
--Group By AreaId,DivisionID,DeptID,MonDate

select c.AreaId,a.AreaName,c.DivisionID,d.ordDivisionName,c.DeptID,p.ProjectName,MonDate, Cast(AbsentStaff*100/TotalStaff as decimal(10,2)) AbsentStaff,cast(LeaveStaff*100/TotalStaff as decimal(10,2))LeaveStaff,Cast(LateStaff*100/TotalStaff as decimal(10,2)) LateStaff,Cast(PresentStaff*100/TotalStaff as decimal(10,2)) PresentStaff,Cast(WeekendStaff*100/TotalStaff as decimal(10,2)) WeekendStaff,Cast(TotalStaff as Int) TotalStaff from #AreaDivDeptCalculation c
inner join Area a on a.AreaID=c.AreaId
inner join tbl_HRD_OrgDivision d on d.ordDivisionID=c.DivisionID
inner join Project p on p.ProjectId=c.DeptID
Group By c.AreaId,a.AreaName,c.DivisionID,d.ordDivisionName,c.DeptID,p.ProjectName,MonDate,AbsentStaff,LeaveStaff,LateStaff,PresentStaff,WeekendStaff,TotalStaff
GO

pritam40 28-Jan-15 4:26am View

Deleted

--- exec Sp_Att_DailyAttendanceSummaryMonthly '%','%','%','%','01-Oct-2014','31-Oct-2014'
Alter PROCEDURE [dbo].[Sp_Att_DailyAttendanceSummaryMonthly]

@AreaID NVARCHAR(4) = '%',
@DivisionID NVARCHAR(4) = '%',
@ProjectID NVARCHAR(3) = '%',
@SectionID nvarchar(4)='%',
@StrDate Datetime,
@EndDate Datetime

--With Encryption

AS
Set Nocount on

Create Table #AreaDivDeptStaff (AreaId varchar(4),DivisionID NVARCHAR(4),DeptID NVARCHAR(3),StaffPIN varchar(8))
Create Table #AreaDivDeptCalculation(AreaId varchar(4),DivisionID NVARCHAR(4),DeptID NVARCHAR(3),MonDate datetime, AbsentStaff decimal(10,2), LeaveStaff decimal(10,2),LateStaff decimal(10,2),PresentStaff decimal(10,2), WeekendStaff decimal(10,2),TotalStaff int)

Insert Into #AreaDivDeptStaff
SELECT c.SsAreaID,c.SsDivisionID,c.SsProjectID,c.SsPIN
FROM StaffStatus c
INNER JOIN Staff a ON c.SsPIN = a.StaPIN
Inner Join StaffTransfer st on st.StPIN=c.SsPIN And st.StAreaID=c.SsAreaID And st.StDivisionID=c.SsDivisionID And st.StProjectID=c.SsProjectID
Inner Join
(SELECT StPIN, Max(StEffectiveDate) as StEffectiveDate
FROM StaffTransfer
WHERE StEffectiveDate <=@StrDate
GROUP BY StPIN) d On d.StPIN = st.StPIN And d.StEffectiveDate = st.StEffectiveDate
WHERE c.SsProjectID Like @ProjectID
And c.SsAreaID Like @AreaID
AND c.SsDivisionID Like @DivisionID
And c.SsUnitID Like @SectionID
And c.SsStatusID in('C','N')

DECLARE @PIN AS NCHAR(8)

DECLARE @TotalAbsent AS decimal(18,2)
DECLARE @TotalLeave AS decimal(18,2)
DECLARE @TotalLate AS decimal(18,2)
DECLARE @TotalPresent AS decimal(18,2)
DECLARE @TotalWeekend AS decimal(18,2)
DECLARE @TotalStaff AS decimal(18,2)

set @TotalAbsent =0
set @TotalLeave =0
set @TotalLate =0
set @TotalPresent =0
set @TotalWeekend =0
set @TotalStaff =0

DECLARE @AID AS NCHAR(4)
DECLARE @DID AS NCHAR(4)
DECLARE @DpID AS NCHAR(4)

DECLARE AreaDivDept_Cursor CURSOR FOR
SELECT distinct c.AreaID,c.DivisionID,c.DeptID
FROM #AreaDivDeptStaff c
OPEN AreaDivDept_Cursor

FETCH NEXT FROM AreaDivDept_Cursor
INTO @AID,@DID,@DpID

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @NextDate AS DATETIME
SET @NextDate = @StrDate

While @NextDate <= @EndDate

BEGIN
set @TotalAbsent =0
set @TotalLeave =0
set @TotalLate =0
set @TotalPresent =0
set @TotalWeekend =0
set @TotalStaff =0
DECLARE AreaDivDeptPin_Cursor CURSOR FOR
SELECT distinct c.StaffPIN
FROM #AreaDivDeptStaff c
Where c.AreaID=@AID And c.DivisionID =@DID And c.DeptID=@DpID
OPEN AreaDivDeptPin_Cursor
FETCH NEXT FROM AreaDivDeptPin_Cursor
INTO @PIN

WHILE @@FETCH_STATUS = 0
BEGIN

SET @TotalStaff = @TotalStaff + 1
IF dbo.GetDayStatusByDate(@PIN,@NextDate)='A'
SET @TotalAbsent = @TotalAbsent + 1
IF dbo.GetDayStatusByDate(@PIN,@NextDate)='L'
SET @TotalLeave = @TotalLeave + 1
IF dbo.GetDayStatusByDate(@PIN,@NextDate)='U'
SET @TotalLeave = @TotalLeave + 1
IF dbo.GetDayStatusByDate(@PIN,@NextDate)='Q'
SET @TotalLeave = @TotalLeave + 1
IF dbo.GetDayStatusByDate(@PIN,@NextDate)='P'
SET @TotalPresent = @TotalPresent + 1
IF dbo.GetDayStatusByDate(@PIN,@NextDate)='D'
Begin
SET @TotalLate = @TotalLate + 1
SET @TotalPresent = @TotalPresent + 1
End
IF dbo.GetDayStatusByDate(@PIN,@NextDate)='W'
SET @TotalWeekend = @TotalWeekend + 1
IF dbo.GetDayStatusByDate(@PIN,@NextDate)='X'
SET @TotalWeekend = @TotalWeekend + 1

FETCH NEXT FROM AreaDivDeptPin_Cursor
INTO @PIN
End

CLOSE AreaDivDeptPin_Cursor
DEALLOCATE AreaDivDeptPin_Cursor

insert into #AreaDivDeptCalculation Values(@AID,@DID,@DpID,@NextDate,@TotalAbsent,@TotalLeave,@To

pritam40 25-Jan-15 9:14am View

Deleted

FROM MonthlyPaymentTransAccList
WHERE MptYear = @PinYear
AND MptMonth = @PinMonth
) AS e ON B.PinID = e.MptPIN
INNER JOIN DrawnOnBankList R ON R.DblBankId = e.MptBankID
INNER JOIN #TBanklist L ON L.BankId = e.MptBankId
INNER JOIN StaffStatus S ON S.ssPIn = a.StaPin
INNER JOIN desiGnation G ON G.DesId = S.ssDesId
INNER JOIN #tmpProgram tp ON tp.PinID = b.PINID
LEFT JOIN #JobLevelWiseEligibleStaff jwes ON jwes.StaffPin=a.StaPIN
ORDER BY L.BankId ,
b.ProjectID ,
a.StaPin
END

DROP TABLE #tmpProgram

GO

pritam40 25-Jan-15 9:12am View

Deleted

MptBankID ,
MptAccountNo
FROM MonthlyPaymentTransAccList
WHERE MptYear = @PinYear
AND MptMonth = @PinMonth
) AS e ON B.PinID = e.MptPIN
INNER JOIN DrawnOnBankList R ON R.DblBankId = e.MptBankID
INNER JOIN #TBanklist L ON L.BankId = e.MptBankId
INNER JOIN StaffStatus S ON S.ssPIn = a.StaPin
INNER JOIN desiGnation G ON G.DesId = S.ssDesId
INNER JOIN Pay_BaseLineSalaryTrtoBank Stb ON B.PINID = Stb.SslStaffPIN
AND Stb.sslYear = @PinYear
AND Stb.SslMonth = @PinMonth
INNER JOIN #TempBaseLine tbl ON stb.SslNo = tbl.SlNo
INNER JOIN #tmpProgram tp ON tp.PinID = b.PINID
LEFT JOIN #JobLevelWiseEligibleStaff jwes ON jwes.StaffPin=a.StaPIN
ORDER BY L.BankId ,
b.ProjectID ,
a.StaPin

DROP TABLE #TempBaseLine

END

ELSE
BEGIN
SELECT StaPin ,
tp.ProgramID ,
StaffName = StaFname + ' ' + ISNULL(StaMname, ' ') + ' '
+ ISNULL(StaLname, '') ,
b.ProjectID ,
ProjectName ,
b.AreaId ,
AreaName ,
ISNULL(BasicAmount, 0) AS BasicAmount ,
ISNULL(HRent, 0) AS HRent ,
ISNULL(Medical, 0) AS Medical ,
ISNULL(ConAll, 0) AS ConAll ,
ISNULL(Entertain, 0) AS Entertain ,
ISNULL(Utility, 0) AS Utility ,
ISNULL(Technical, 0) AS Technical ,
OtherAm = ISNULL(Other, 0) + ISNULL(OtherAmount, 0) ,
GrossSalary = ISNULL(BasicAmount, 0) + ISNULL(HRent, 0)
+ ISNULL(Medical, 0) + ISNULL(ConAll, 0)
+ ISNULL(Entertain, 0) + ISNULL(Utility, 0)
+ ISNULL(Technical, 0) + ISNULL(Other, 0)
+ ISNULL(OtherAmount, 0)-ISNULL(jwes.bKashAmount,0) ,
ISNULL(PFAmount, 0) AS PFAmount ,
ISNULL(TranDeduction, 0) AS TranDeduction ,
ISNULL(IncomeTax, 0) AS IncomeTax ,
ISNULL(LoanReaAmount, 0) AS LoanReaAmount ,
ISNULL(UPLDeduction, 0) AS UPLDeduction ,
ISNULL(DPSDeduction, 0) AS DPSDeduction ,
ISNULL(WelfareFundDeduct, 0) AS WelfareFundDeduct ,
ISNULL(OtherAdjAmount, 0) AS OtherAdjAmount ,
GDeduct = ISNULL(TranDeduction, 0) + ISNULL(PFAmount, 0)
+ ISNULL(LoanReaAmount, 0) + ISNULL(IncomeTax, 0)
+ ISNULL(UPLDeduction, 0) + ISNULL(WelfareFundDeduct, 0)
+ ISNULL(DPSDeduction, 0) ,
R.DblName AS BankName ,
B.CashYN ,
e.MptAccountNo AS AccNO ,
L.BankId ,
G.DesName
FROM Staff AS a
INNER JOIN #TMonthlyPsothersHis AS B ON a.StaPin = B.PinID
INNER JOIN Project AS C ON b.ProjectID = C.ProjectID
INNER JOIN Area ON B.AreaID = Area.AreaID
LEFT JOIN #TMonthlyPsSalary AS D ON B.pinId = D.PinID
INNER JOIN ( SELECT MptPIN ,
MptBankID ,
MptAccountNo
FROM MonthlyPay

pritam40 25-Jan-15 9:10am View

Deleted

UPDATE a SET a.JobLevelID=b.EquivalentJLevelID
FROM #JobLevelWiseEligibleStaff a
INNER JOIN #TempZeroEquivalentLevel b ON a.StaffPin = b.StaffPin

DELETE FROM #JobLevelWiseEligibleStaff WHERE JobLevelID='000'

-----------------------------------------------------------------------------------------------------------------

IF LEN(@BaseLineInfo) > 0
BEGIN
WHILE @Counter <= LEN(@BaseLineInfo)
BEGIN
SELECT @Value = SUBSTRING(@BaseLineInfo, @Counter, 1)

IF @Value IN ( '', ',' )
BEGIN
IF LEN(@tempValue) > 0
BEGIN
INSERT INTO #TempBaseLine
SELECT CAST(@tempValue AS INT)
END
SELECT @tempValue = ''
END
ELSE
BEGIN
SELECT @tempValue = @tempValue + @Value
END

SELECT @Counter = @Counter + 1
END

IF LEN(@tempValue) > 0
BEGIN
INSERT INTO #TempBaseLine
SELECT CAST(@tempValue AS INT)
END

--SELECT * FROM #TempBaseLine

SELECT StaPin ,
tp.ProgramID ,
StaffName = StaFname + ' ' + ISNULL(StaMname, ' ') + ' '
+ ISNULL(StaLname, '') ,
b.ProjectID ,
ProjectName ,
b.AreaId ,
AreaName ,
ISNULL(BasicAmount, 0) AS BasicAmount ,
ISNULL(HRent, 0) AS HRent ,
ISNULL(Medical, 0) AS Medical ,
ISNULL(ConAll, 0) AS ConAll ,
ISNULL(Entertain, 0) AS Entertain ,
ISNULL(Utility, 0) AS Utility ,
ISNULL(Technical, 0) AS Technical ,
OtherAm = ISNULL(Other, 0) + ISNULL(OtherAmount, 0) ,
GrossSalary = ISNULL(BasicAmount, 0) + ISNULL(HRent, 0)
+ ISNULL(Medical, 0) + ISNULL(ConAll, 0)
+ ISNULL(Entertain, 0) + ISNULL(Utility, 0)
+ ISNULL(Technical, 0) + ISNULL(Other, 0)
+ ISNULL(OtherAmount, 0)- ISNULL(jwes.bKashAmount,0) ,
ISNULL(PFAmount, 0) AS PFAmount ,
ISNULL(TranDeduction, 0) AS TranDeduction ,
ISNULL(IncomeTax, 0) AS IncomeTax ,
ISNULL(LoanReaAmount, 0) AS LoanReaAmount ,
ISNULL(UPLDeduction, 0) AS UPLDeduction ,
ISNULL(DPSDeduction, 0) AS DPSDeduction ,
ISNULL(WelfareFundDeduct, 0) AS WelfareFundDeduct ,
ISNULL(OtherAdjAmount, 0) AS OtherAdjAmount ,
GDeduct = ISNULL(TranDeduction, 0) + ISNULL(PFAmount, 0)
+ ISNULL(LoanReaAmount, 0) + ISNULL(IncomeTax, 0)
+ ISNULL(UPLDeduction, 0) + ISNULL(WelfareFundDeduct, 0)
+ ISNULL(DPSDeduction, 0) ,
R.DblName AS BankName ,
B.CashYN ,
e.MptAccountNo AS AccNO ,
L.BankId ,
G.DesName
FROM Staff AS a
INNER JOIN #TMonthlyPsothersHis AS B ON a.StaPin = B.PinID
INNER JOIN Project AS C ON b.ProjectID = C.ProjectID
INNER JOIN Area ON B.AreaID = Area.AreaID
LEFT JOIN #TMonthlyPsSalary AS D ON B.pinId = D.PinID
INNER JOIN ( SELECT MptPIN ,

pritam40 25-Jan-15 9:09am View

Deleted

--------------------------------****Modified by Nur--------------------------------------------

CREATE TABLE #JobLevelWiseEligibleStaff
(
StaffPin NCHAR(8) ,
JobLevelID NCHAR(3) ,
bKashAmount MONEY
)
CREATE TABLE #StafflWiseEligibleStaff
(
StaffPin NCHAR(8) ,
JobLevelID NCHAR(3) ,
bKashAmount MONEY
)

INSERT INTO #JobLevelWiseEligibleStaff
SELECT a.SsPIN ,
c.bKashJobLevelID ,
c.bKashAmount
FROM StaffStatus a
INNER JOIN StaffSalaryStructureOthers b ON a.SsPIN = b.SssPIN
INNER JOIN Tbl_Pay_JobLevelWisebKashEligibility c ON a.SsJLevelID = c.bKashJobLevelID
INNER JOIN ( SELECT *
FROM StaffBankAccountInfo
WHERE SbabKashAccountNo IS NOT NULL
) d ON a.SsPIN = d.SbaStaffPIN
WHERE c.bKashEligibilityStatus = 1
AND c.bKashAmount > 0
AND a.SsStatusID IN ( 'C', 'N' )
AND c.bKashAmountTypeID = '01'

--SELECT * FROM #JobLevelWiseEligibleStaff

INSERT INTO #StafflWiseEligibleStaff
SELECT a.SsPIN ,
a.SsJLevelID ,
c.bKashAmount
FROM StaffStatus a
INNER JOIN StaffSalaryStructureOthers b ON a.SsPIN = b.SssPIN
INNER JOIN Tbl_Pay_StaffWisebKashEligibility c ON a.SsPIN = c.StaffPIN
INNER JOIN ( SELECT *
FROM StaffBankAccountInfo
WHERE SbabKashAccountNo IS NOT NULL
) d ON a.SsPIN = d.SbaStaffPIN
WHERE c.EligibilityStatus = 1
AND c.bKashAmount > 0
AND a.SsStatusID IN ( 'C', 'N' )
AND c.bKashAmountTypeID = '01'

--SELECT * FROM #StafflWiseEligibleStaff

--SELECT * FROM #JobLevelWiseEligibleStaff a INNER JOIN #StafflWiseEligibleStaff b ON a.StaffPin = b.StaffPin AND a.JobLevelID = b.JobLevelID

DELETE a
FROM #JobLevelWiseEligibleStaff a
INNER JOIN #StafflWiseEligibleStaff b ON a.StaffPin = b.StaffPin
AND a.JobLevelID = b.JobLevelID

INSERT INTO #JobLevelWiseEligibleStaff
SELECT *
FROM #StafflWiseEligibleStaff

SELECT a.StaffPin ,
b.EquivalentJLevelID ,
b.bKashAmount
INTO #TempZeroEquivalentLevel
FROM #JobLevelWiseEligibleStaff a
INNER JOIN ( SELECT a.SsPIN ,
a.SsJLevelID ,
b.EquivalentJLevelID ,
d.bKashAmount
FROM StaffStatus a
INNER JOIN tbl_HRD_ZeroLevelMapping b ON a.SsPIN = b.PIN
AND a.SsJLevelID = b.CurrentJLevelID
INNER JOIN StaffSalaryStructureOthers c ON a.SsPIN = c.SssPIN
INNER JOIN Tbl_Pay_JobLevelWisebKashEligibility d ON b.EquivalentJLevelID = d.bKashJobLevelID
INNER JOIN ( SELECT *
FROM StaffBankAccountInfo
WHERE SbabKashAccountNo IS NOT NULL
) e ON a.SsPIN = e.SbaStaffPIN
WHERE a.SsStatusID IN ( 'C', 'N' )
AND d.bKashEligibilityStatus = 1
AND d.bKashAmount > 0
AND d.bKashAmountTypeID = '01'
AND a.SsJLevelID = '000'
) b ON a.StaffPin = b.SsPIN
AND a.JobLevelID = b.SsJLevelID
WHERE b.EquivalentJLevelID IN('018','019','020','021')

--SELECT * FROM #TempZeroEquivalentLevel

pritam40 25-Jan-15 9:08am View

Deleted

THEN Amount
ELSE 0
END)
FROM #TMonthyPsHistory
GROUP BY PinID

CREATE TABLE #TempBaseLine ( SlNo INT )

DECLARE @Counter AS INT
DECLARE @Value AS VARCHAR(1)
DECLARE @tempValue AS VARCHAR(5)
SELECT @Counter = 1 ,
@Value = '' ,
@tempValue = ''

IF @ProgramStatus = '1'
BEGIN
INSERT INTO #tmpProgram
SELECT a.PINID ,
c.ProgramID
FROM #TMonthlyPsothersHis a
INNER JOIN Project AS C ON a.ProjectID = C.ProjectID
INNER JOIN Program p ON p.ProgramID = c.ProgramID
WHERE p.ProgramID <> '22'
END

ELSE
IF @ProgramStatus = '2'
BEGIN

INSERT INTO #tmpProgram
SELECT a.PINID ,
c.ProgramID
FROM #TMonthlyPsothersHis a
INNER JOIN Project AS C ON a.ProjectID = C.ProjectID
INNER JOIN Program p ON p.ProgramID = c.ProgramID
WHERE p.ProgramID = '22'
END
ELSE
BEGIN

INSERT INTO #tmpProgram
SELECT a.PINID ,
c.ProgramID
FROM #TMonthlyPsothersHis a
INNER JOIN Project AS C ON a.ProjectID = C.ProjectID
INNER JOIN Program p ON p.ProgramID = c.ProgramID
END

--SELECT * FROM #tmpProgram

pritam40 25-Jan-15 9:05am View

Deleted

-----------------------------------------Find Staff Project and Area and his salary Others --------------------------------

INSERT INTO #TMonthlyPsothersHis
SELECT CmpPIN ,
CmpBasicAmount ,
CmpOtherAmount ,
CmpPFAmount ,
CmpTranDeduction ,
CmpIncomeTax ,
CmpLoanReaAmount ,
CmpOtherAdjAmount ,
CmpUPLDeduction ,
CmpWFDeductionAmt ,
CmpDPSDeductionAmt ,
PjaProjectId ,
PjaAreaID ,
CmpCashYN
FROM CurrentMonPsothers AS a
INNER JOIN #TProjectArea AS b ON a.CmpAccCode = b.PjaAccCode
WHERE CmpYear = @PinYear
AND CmpMonth = @PinMonth
AND a.VerifiedYN = 1
UNION ALL
SELECT MpoPIN ,
MpoBasicAmount ,
MpoOtherAmount ,
MpoPFAmount ,
MpoTranDeduction ,
MpoIncomeTax ,
MpoLoanReaAmount ,
MpoOtherAdjAmount ,
MpoUPLDeduction ,
MpoWFDeductionAmt ,
MpoDPSDeductionAmt ,
PjaProjectId ,
PjaAreaID ,
MpoCashYN
FROM MonthlyPsothersHistory AS a
INNER JOIN #TProjectArea AS b ON a.MpoAccCode = b.PjaAccCode
WHERE MpoYear = @PinYear
AND MpoMonth = @PinMonth
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO #TMonthyPsHistory
SELECT CmdPIN ,
b.SbkSbsId ,
SUM(CmdAmount) AS Amount
FROM currentMonPsDetails AS a
INNER JOIN SalaryBreakup AS b ON a.CmdSbkID = b.SbkID
WHERE CmdYear = @PinYear
AND CmdMonth = @PinMonth
GROUP BY CmdPIN ,
b.SbkSbsId
UNION ALL
SELECT MphPIN ,
b.SbkSbsId ,
SUM(MphAmount) AS Amount
FROM MonthlyPsDetailsHistory AS a
INNER JOIN SalaryBreakup AS b ON a.MphSbkID = b.SbkID
WHERE MphYear = @PinYear
AND MphMonth = @PinMonth
GROUP BY MphPIN ,
b.SbkSbsId

INSERT INTO #TMonthlyPsSalary
SELECT PinID ,
HRent = SUM(CASE WHEN SbkID = '04' THEN Amount
ELSE 0
END) ,
Medical = SUM(CASE WHEN SbkID = '06' THEN Amount
ELSE 0
END) ,
ConAll = SUM(CASE WHEN SbkID = '07' THEN Amount
ELSE 0
END) ,
Entertain = SUM(CASE WHEN SbkID = '05' THEN Amount
ELSE 0
END) ,
Utility = SUM(CASE WHEN SbkID = '08' THEN Amount
ELSE 0
END) ,
Tech = SUM(CASE WHEN SbkID = '09' THEN Amount
ELSE 0
END) ,
Other = SUM(CASE WHEN SbkID NOT IN ( '01', '02', '03',
'04', '05', '06',
'07', '08', '09' )
T

pritam40 25-Jan-15 9:03am View

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF
GO
--exec Sp_Pay_RptStaffSalaryTransfertoBank 2014, 1, default, default, default,'',''

ALTER PROCEDURE [Sp_Pay_RptStaffSalaryTransfertoBank]
@PinYear SMALLINT ,
@PinMonth TINYINT ,
@BankID NCHAR(3) = '%' ,
@ProjectID NVARCHAR(3) = '%' ,
@AreaID NVARCHAR(4) = '%' ,
@BaseLineInfo VARCHAR(50) ,
@ProgramStatus NVARCHAR(1)
WITH ENCRYPTION
AS
DECLARE @YearMonth NVARCHAR(6)
SET Nocount ON
SET dateformat 'DMY'

CREATE TABLE #TMonthyPsHistory
(
PINID NCHAR(8) ,
SbkId NCHAR(2) ,
Amount NUMERIC(12, 2)
)

CREATE TABLE #TMonthlyPsothersHis
(
PINID NCHAR(8) ,
BasicAmount NUMERIC(12, 2) ,
OtherAmount NUMERIC(11, 2) ,
PFAmount NUMERIC(11, 2) ,
TranDeduction NUMERIC(11, 2) ,
IncomeTax NUMERIC(11, 2) ,
LoanReaAmount NUMERIC(11, 2) ,
OtherAdjAmount NUMERIC(11, 2) ,
UPLDeduction NUMERIC(11, 2) ,
DPSDeduction NUMERIC(11, 2) ----> This column is added by Md Arifuzzaman
,
WelfareFundDeduct NUMERIC(11, 2) -----> This Column is added by JK on 24112005 to incorporate to Welfae Fund Deduction
,
ProjectID NCHAR(3) ,
AreaID NCHAR(4) ,
CashYN BIT
)

CREATE TABLE #TMonthlyPsSalary
(
PinID NCHAR(8) ,
HRent NUMERIC(11, 2) ,
Medical NUMERIC(11, 2) ,
ConAll NUMERIC(11, 2) ,
Entertain NUMERIC(11, 2) ,
Utility NUMERIC(11, 2) ,
Technical NUMERIC(11, 2) ,
Other NUMERIC(11, 2)
)

CREATE TABLE #TProjectArea
(
PjaProjectID NCHAR(3) ,
PjaAreaID NCHAR(4) ,
PjaAccCode NVARCHAR(12)
)

CREATE TABLE #TBankList ( BankId NCHAR(3) )

CREATE TABLE #tmpProgram
(
PinID NCHAR(8) ,
ProgramID NCHAR(2)
)

IF @bankID = '%'
BEGIN
INSERT INTO #TbankList
SELECT DISTINCT
MptBankId
FROM MonthlyPaymentTransAccList
END
ELSE
BEGIN
INSERT INTO #TbankList
SELECT DISTINCT
MptBankId
FROM MonthlyPaymentTransAccList
WHERE MptBankId = @BankID
END

INSERT INTO #TProjectArea
SELECT PjaProjectId ,
PjaAreaID ,
PjaAccCode
FROM ProjectArea
WHERE PjaProjectId LIKE @ProjectID
AND PjaAreaID LIKE @AreaID

pritam40 21-Jan-15 7:50am View

Deleted

BEGIN TRAN

insert into PFCalculatedInterest
select SsPIN,2013,12,'31-Dec-2013',0,0,0,0,NULL,'21-Jan-2015','Support' from StaffStatus
inner join PFStaffStatus a on a.PssPin = SsPIN
where SsStatusID in ('C','N') and ssPin not in (
select PciPIN from PFCalculatedInterest group by PciPIN )

select SsPIN,2013,12,'31-Dec-2013',0,0,0,0,NULL,'21-Jan-2015','Support' from StaffStatus
inner join PFStaffStatus a on a.PssPin = SsPIN
where SsStatusID in ('C','N') and ssPin not in (
select PciPIN from PFCalculatedInterest group by PciPIN )

ROLLBACK

pritam40 28-Dec-14 4:51am View

BEGIN TRAN

DELETE FROM tbl_Leave_StaffCLeaveStatus WHERE SlsLvTypeID = '11'
INSERT INTO tbl_Leave_StaffCLeaveStatus(SlsPin, SlsLvTypeID, SlsEwdID,
SlsLvBalance, SlsExcessLvBalance, SetDate, UserName)
SELECT DISTINCT SlsPin,'11',SlsEwdID,0,0,SetDate, UserName FROM tbl_Leave_StaffCLeaveStatus WHERE SlsLvTypeID = '01'

Declare @PIN AS VARCHAR(8)
DECLARE CursorName CURSOR FOR
SELECT lscs.SlsPin FROM tbl_Leave_StaffCLeaveStatus lscs WHERE lscs.SlsEwdID = '2' ORDER BY lscs.SlsPin,lscs.SlsLvTypeID ASC
OPEN CursorName
FETCH FROM CursorName INTO @PIN
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE tbl_Leave_StaffCLeaveStatus SET SlsEwdID = '2' WHERE SlsPin = @PIN
FETCH FROM CursorName INTO @PIN
END
CLOSE CursorName
DEALLOCATE CursorName

SELECT * FROM tbl_Leave_StaffCLeaveStatus ORDER BY SlsPin
ROLLBACK

pritam40 28-Dec-14 3:36am View

Deleted

DECLARE @alvID AS VARCHAR(8)
DECLARE @SlNo AS INT
DECLARE @StaffPin AS VARCHAR(8)
SET @StaffPin = '00001006'
SET @SlNo = 1

select @alvID = alvID from tbl_Leave_ApplicantLeaveList where alvSltPin= @StaffPin and AlvSltSlNo = @SlNo
SELECT @alvID
DELETE FROM tbl_leave_PrescriptionList WHERE AlvID = @alvID
delete from tbl_Leave_ApplicantLeaveList where alvID = @alvID
delete from tbl_Leave_LeaveApplicationStatusHistory where LahAlvID = @alvID
delete from tbl_leave_leaveApplicationStatus where LasAlvID = @alvID
delete from tbl_leave_AppApproverHierarchy where AahAppID = @alvID
delete from staffleavetaken where SltPin = @StaffPin and SltSlNo = @SlNo

SELECT * FROM staffleavetaken WHERE SltPIN = @StaffPin

pritam40 28-Dec-14 0:21am View

Deleted

BEGIN TRAN
update tbl_Leave_StaffCLeaveStatus SET SlsLvTypeID = '01' WHERE SlsLvTypeID = '15'
update tbl_Leave_StaffCLeaveStatus SET SlsLvTypeID = '02' WHERE SlsLvTypeID = '14'

update tbl_Leave_StaffLeaveGroupMapping SET SgmLgsLvGroupID = '00' WHERE SgmLgsLvGroupID = '01'

Update tbl_Leave_YearlyStaffCLeaveStatusHistory SET LshLvTypeID = '01' WHERE LshLvTypeID = '15'
Update tbl_Leave_YearlyStaffCLeaveStatusHistory SET LshLvTypeID = '02' WHERE LshLvTypeID = '14'

UPDATE StaffLeaveTaken SET SltLvTypeID = '01' WHERE SltLvTypeID = '15'
UPDATE StaffLeaveTaken SET SltLvTypeID = '02' WHERE SltLvTypeID = '14'

UPDATE tbl_Leave_ApplicantLeaveList SET AlvSltLvTypeID = '01' WHERE AlvSltLvTypeID = '15'
UPDATE tbl_Leave_ApplicantLeaveList SET AlvSltLvTypeID = '02' WHERE AlvSltLvTypeID = '14'

--delete
delete FROM tbl_Leave_GroupWiseLeaveTypeSetup WHERE GlsLvGroupId = '01'
delete FROM tbl_Leave_LeaveGroupSetup WHERE LgsLvGroupID = '01'
delete FROM tbl_Leave_LeaveTypeSetup WHERE LtsLvTypeID IN ('15','14')

---

SELECT * FROM tbl_Leave_StaffCLeaveStatus lscs WHERE lscs.SlsLvTypeID = '15'
SELECT * FROM tbl_Leave_StaffCLeaveStatus lscs WHERE lscs.SlsLvTypeID = '14'
SELECT * FROM tbl_Leave_StaffLeaveGroupMapping lslgm WHERE lslgm.SgmLgsLvGroupID = '01'
SELECT * FROM tbl_Leave_YearlyStaffCLeaveStatusHistory lyscsh WHERE lyscsh.LshLvTypeID = '15'
SELECT * FROM tbl_Leave_YearlyStaffCLeaveStatusHistory lyscsh WHERE lyscsh.LshLvTypeID = '14'
SELECT * FROM StaffLeaveTaken slt WHERE slt.SltLvTypeID = '15'
SELECT * FROM StaffLeaveTaken slt WHERE slt.SltLvTypeID = '14'
SELECT * FROM tbl_Leave_ApplicantLeaveList lall WHERE lall.AlvSltLvTypeID = '15'
SELECT * FROM tbl_Leave_ApplicantLeaveList lall WHERE lall.AlvSltLvTypeID = '14'

SELECT * FROM tbl_Leave_GroupWiseLeaveTypeSetup lgwlts WHERE lgwlts.GlsLvGroupId = '01'
SELECT * FROM tbl_Leave_LeaveGroupSetup llgs WHERE llgs.LgsLvGroupID = '01'
SELECT * FROM tbl_Leave_LeaveTypeSetup llts WHERE llts.LtsLvTypeID IN ('15','14')
ROLLBACK

pritam40 18-Dec-14 3:40am View

Deleted

Alter Table dbo.FinalData
Drop Column StaAPin
Alter Table dbo.FinalData
Drop Column StaDPin
Alter Table dbo.FinalData
Drop Column StaffPin
Alter Table dbo.FinalData
Drop Column SYear
Alter Table dbo.FinalData
Drop Column SMonth

--DECLARE @sqlU NVARCHAR(Max)
--SET @sqlU = STUFF((
--SELECT N' UPDATE FinalData SET [' + column_name + ']= 0 ' +
--+ N' Where [' + column_name + '] IS NULL'
--FROM INFORMATION_SCHEMA.COLUMNS
--WHERE table_name = 'FinalData' --AND Substring(COLUMN_NAME,0,3) Like '%[0-9]%'
--FOR XML PATH('')), 1, 10, '')

--exec(@sqlU)

Select * from FinalData

DROP TABLE TempTblStaffAllLoanDetails

GO

pritam40 18-Dec-14 3:38am View

Deleted

insert into #TMonthyAdjustmentAddition
Select a.SsodPIN, Sum(a.SsodAmount) from dbo.tbl_PayStaffSalaryOthersDetails a inner join dbo.tbl_PaySalaryAddDeductPurposeSetup b on b.SapPurposeID=a.SsodSbkID
where b.SapIsEarning=1 And a.SsodYear=@PinYear And a.SsodMonth=@PinMonth Group By a.SsodPIN

insert into #TMonthyAdjustmentDeduction
Select a.SsodPIN, Sum(a.SsodAmount) from dbo.tbl_PayStaffSalaryOthersDetails a inner join dbo.tbl_PaySalaryAddDeductPurposeSetup b on b.SapPurposeID=a.SsodSbkID
where b.SapIsEarning=0 And a.SsodYear=@PinYear And a.SsodMonth=@PinMonth Group By a.SsodPIN

--Collect all Loan details information
exec Sp_Loan_rptMonthlyLoanDetails @PinYear,@PinMonth

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FinalData]') AND type in (N'U'))
DROP TABLE [dbo].FinalData

SELECT dbo.Fun_GetFormatedStaffPin(a.StaPin)as StaPin, StaffName = StaFname + ' ' + isnull(StaMname,' ') + ' ' + isnull(StaLname,'') ,G.DesName,a.StaJoinDate JoinDate, b.ProjectID, ProjectName,s.SsDivisionID DivisionID,div.ordDivisionID DivisionName, b. AreaId AreaID, AreaName,
isnull(BasicAmount,0) AS BasicAmount, isnull(HRent,0) AS HRent, isnull(Medical,0) AS Medical, isnull(Convayenc,0) AS Convayenc, isnull(Entertain,0) AS Entertain,
isnull([DOM Service],0) AS [DOM Service], isnull([DOM Staff],0) AS [DOM Staff], OtherAm=isnull(Other,0) + isnull(OtherAmount,0),
ab.*,
ISNULL(ad.AdditionAmount,0) as TotalAdjustmentEarning,
TotalFixedSalary = isnull(BasicAmount,0) + isnull(HRent,0) + isnull(Medical,0) + isnull(Convayenc,0) + isnull(Entertain,0) + isnull([DOM Service],0) + isnull([DOM Staff],0) + isnull(Other,0) + isnull(OtherAmount,0),

isnull(PFAmount,0) AS PFAmount, isnull(TranDeduction ,0) AS TranDeduction, isnull( IncomeTax,0) AS IncomeTax, ldls.*,B.LoanReaAmount,
isnull (OtherAdjAmount ,0) AS OtherAdjAmount,
db.*,
isnull(dd.DeductionAmount,0) TotalAdjustmentDeduction,
TotalFixedDeduction= isnull(TranDeduction ,0) + isnull(PFAmount,0) + isnull( LoanReaAmount,0) + isnull( RCDeductAmt,0) + isnull(SecurityAmt,0) + isnull( IncomeTax,0) + isnull(UPLDeduction,0),

TotalSalary = isnull(BasicAmount,0) + isnull(HRent,0) + isnull(Medical,0) + isnull(Convayenc,0) + isnull(Entertain,0) + isnull([DOM Service],0) + isnull([DOM Staff],0) + isnull(Other,0) + isnull(OtherAmount,0)+ISNULL(ad.AdditionAmount,0),
TotalDeduction = isnull(TranDeduction ,0) + isnull(PFAmount,0) + isnull( LoanReaAmount,0) + isnull( RCDeductAmt,0) + isnull(SecurityAmt,0) + isnull( IncomeTax,0) + isnull(UPLDeduction,0)+isnull(dd.DeductionAmount,0),

NetPay=(isnull(BasicAmount,0) + isnull(HRent,0) + isnull(Medical,0) + isnull(Convayenc,0) + isnull(Entertain,0) + isnull([DOM Service],0) + isnull([DOM Staff],0) + isnull(Other,0) + isnull(OtherAmount,0))-(isnull(TranDeduction ,0) + isnull(PFAmount,0) + isnull( LoanReaAmount,0) + isnull( RCDeductAmt,0) + isnull(SecurityAmt,0) + isnull( IncomeTax,0) + isnull(UPLDeduction,0))+isnull (OtherAdjAmount ,0)+ISNULL(ad.AdditionAmount,0)-isnull(dd.DeductionAmount,0) into FinalData
FROM Staff AS a Inner join #TMonthlyPsothersHis AS B ON a.StaPin=B.PinID
Inner join Project AS C ON b.ProjectID=C.ProjectID
Inner join Area ON B.AreaID=Area.AreaID
left Join #TMonthlyPsSalary AS D ON B.pinId=D.PinID
inner Join StaffStatus S
ON S.ssPIn = a.StaPin
inner join dbo.tbl_HRD_OrgDivision Div on div.ordDivisionID=S.SsDivisionID
inner join desiGnation G
ON G.DesId = S.ssDesId
Left Join TMonthyAdjustmentAdditionBreakup ab on ab.StaAPin=B.PinID
Left Join TMonthyAdjustmentDeductionBreakup db on db.StaDPin=B.PinID
Left Join #TMonthyAdjustmentAddition ad on ad.AddPIN=B.PinID
Left Join #TMonthyAdjustmentDeduction dd on dd.DedPIN=B.PinID
LEFT JOIN TempTblStaffAllLoanDetails ldls ON ldls.StaffPin = b.PINID
Where S.SsDivisionID Like @DivisionID And S.SsProjectID like @ProjectID and S.SsAreaID like @AreaID
--And StaPin='00000163'
---- ORDER BY L.BankId, b.ProjectID, a.StaPin
ORDER BY a.StaPin
---- O

pritam40 18-Dec-14 3:37am View

Deleted

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Select * from #TMonthlyPsothersHis Order By PINID

INSERT INTO #TMonthyPsHistory
SELECT CmdPIN, b.SbkSbsId, sum(CmdAmount) AS Amount FROM currentMonPsDetails AS a
inner join SalaryBreakup AS b ON a.CmdSbkID=b.SbkID
WHERE CmdYear=@PinYear and CmdMonth=@PinMonth And SbkTaxableYN=1
GROUP BY CmdPIN, b.SbkSbsId
union all
SELECT MphPIN, b.SbkSbsId, sum(MphAmount) AS Amount FROM MonthlyPsDetailsHistory AS a
inner join SalaryBreakup AS b ON a.MphSbkID=b.SbkID
WHERE MphYear=@PinYear and MphMonth=@PinMonth And SbkTaxableYN=1
GROUP BY MphPIN, b.SbkSbsId

--Select * from #TMonthyPsHistory

INSERT INTO #TMonthlyPsSalary
SELECT PinID, HRent=sum(CASE WHEN SbkID = '04' THEN Amount ELSE 0 END), Medical=sum(CASE WHEN SbkID='06' THEN Amount ELSE 0 END),
ConAll=sum(CASE WHEN SbkID = '07' THEN Amount ELSE 0 END ), Entertain=sum(CASE WHEN SbkID='05' THEN Amount ELSE 0 END ),
Utility=sum(CASE WHEN SbkID='08' THEN Amount ELSE 0 END), Tech=sum(CASE WHEN SbkID='09' THEN Amount ELSE 0 END ),
Other=sum(CASE WHEN SbkID not in ( '01','02','03','04', '05','06','07','08','09') THEN Amount ELSE 0 END ) FROM #TMonthyPsHistory
GROUP BY PinID

--Select * from #TMonthlyPsSalary WHERE PinID = '00000163'
--return
--SELECT * FROM #TMonthlyPsothersHis WHERE PINID = '00002510'
--SELECT * FROM MonthlyPaymentTransAccList WHERE MptPIN = '00002510'
-- return

CREATE TABLE #TMonthyAdjustmentAddition(AddPIN NCHAR(8), AdditionAmount NUMERIC(12,2) )
CREATE TABLE #TMonthyAdjustmentDeduction(DedPIN NCHAR(8),DeductionAmount NUMERIC(12,2) )
declare @SQL nvarchar(max)
declare @ColumnName nvarchar(max)
declare @ColumnID nvarchar(max)

-- Pivot Table for Additional Breakup

select @ColumnName = stuff((select distinct ','+quotename(SapPurposeID)+'as'+quotename(SapPurposeName)
from tbl_PaySalaryAddDeductPurposeSetup where SapIsEarning=1
order by 1
for xml path('')), 1, 1, '')
select @ColumnID = stuff((select distinct ','+quotename(SapPurposeID)
from tbl_PaySalaryAddDeductPurposeSetup where SapIsEarning=1
order by 1
for xml path('')), 1, 1, '')
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TMonthyAdjustmentAdditionBreakup]') AND type in (N'U'))
DROP TABLE [dbo].[TMonthyAdjustmentAdditionBreakup]
set @SQL = '
select SsodPIN as StaAPin,'+@ColumnName+' into TMonthyAdjustmentAdditionBreakup from (
select SsodPIN,isnull(SsodAmount,0)SsodAmount ,SsodSbkID
from tbl_PayStaffSalaryOthersDetails
) as T
pivot(min(SsodAmount) for SsodSbkID in ('+@ColumnID+')) as P'
exec (@SQL)

-- Pivot Table for Deduction Breakup
select @ColumnName = stuff((select distinct ','+quotename(SapPurposeID)+'as'+quotename(SapPurposeName)
from tbl_PaySalaryAddDeductPurposeSetup where SapIsEarning=0
order by 1
for xml path('')), 1, 1, '')
select @ColumnID = stuff((select distinct ','+quotename(SapPurposeID)
from tbl_PaySalaryAddDeductPurposeSetup where SapIsEarning=0
order by 1
for xml path('')), 1, 1, '')
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TMonthyAdjustmentDeductionBreakup]') AND type in (N'U'))
DROP TABLE [dbo].[TMonthyAdjustmentDeductionBreakup]
set @SQL = '
select SsodPIN as StaDPin,'+@ColumnName+' into TMonthyAdjustmentDeductionBreakup from (
select SsodPIN,isnull(SsodAmount,0)SsodAmount ,SsodSbkID
from tbl_PayStaffSalaryOthersDetails
) as T
pivot(min(SsodAmount) for SsodSbkID in ('+@ColumnID+')) as P'

exec (@SQL)

pritam40 18-Dec-14 3:34am View

Deleted

/****** Object: StoredProcedure [dbo].[Sp_Pay_RptMonthlySalaryAnalysisforAll] Script Date: 12/18/2014 14:31:33 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Pay_RptMonthlySalaryAnalysisforAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Sp_Pay_RptMonthlySalaryAnalysisforAll]
GO

/****** Object: StoredProcedure [dbo].[Sp_Pay_RptMonthlySalaryAnalysisforAll] Script Date: 12/18/2014 14:31:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--exec Sp_Pay_RptMonthlySalaryAnalysisforAll 2014,7,'%','%','%'

CREATE PROCEDURE [dbo].[Sp_Pay_RptMonthlySalaryAnalysisforAll]
@PinYear SMALLINT ,
@PinMonth TINYINT ,
@AreaID NVARCHAR(4)='%' ,
@DivisionID NVARCHAR(4) = '%',
@ProjectID NVARCHAR(3) = '%'
--With Encryption
AS
DECLARE @YearMonth NVARCHAR(6)
SET Nocount ON
SET dateformat 'DMY'

CREATE TABLE #TMonthyPsHistory(PINID NCHAR(8), SbkId NCHAR(2), Amount NUMERIC(12,2) )

CREATE TABLE #TMonthlyPsothersHis(PINID NCHAR(8), BasicAmount NUMERIC(12,2),
OtherAmount NUMERIC(11,2) , PFAmount NUMERIC(11,2),
TranDeduction NUMERIC(11,2), IncomeTax NUMERIC(11,2), LoanReaAmount NUMERIC(11,2),
RCDeductAmt MONEY, SecurityAmt MONEY,
OtherAdjAmount NUMERIC(11,2) ,UPLDeduction NUMERIC(11,2), ProjectID NCHAR(3),
AreaID NCHAR(4), CashYN BIT )

CREATE TABLE #TMonthlyPsSalary(PinID NCHAR(8), HRent NUMERIC(11,2),
Medical NUMERIC(11,2), Convayenc NUMERIC(11,2), Entertain NUMERIC(11,2),
[DOM Service] NUMERIC(11,2), [DOM Staff] NUMERIC(11,2), Other NUMERIC(11,2))

CREATE TABLE #TProjectArea(stPIN NVARCHAR(8), PjaProjectID NCHAR(3), PjaAreaID NCHAR(4), PjaUnitID NCHAR(4)) ---- PjaAccCode NVARCHAR(12) )
Declare @tmpDate as datetime
--set @tmpDate = cast(@PinYear as nchar(4)) + '-' + cast(@PinMonth as nchar(2)) + '-1'
set @tmpDate = '01' + '-' + cast(@PinMonth as nchar(2))+ '-' +cast(@PinYear as nchar(4))
--Select @tmpDate
Set @tmpDate = Dateadd(day,-1,(dateadd(month, 1, @tmpDate)))

INSERT INTO #TProjectArea
Select st.StPIN, st.StProjectID, st.StAreaID,StUnitID From StaffTransfer st Inner Join
(Select StPIN, Max(StEffectiveDate) as MaxEffectiveDate
From StaffTransfer Where StEffectiveDate<=@tmpDate
Group by StPIN ) a On st.StPIN = a.StPIN And st.StEffectiveDate = a.MaxEffectiveDate
--And StDivisionID Like '0001' And StProjectId like '019' and StAreaID like '0001'
And StProjectId like @ProjectID and StAreaID like @AreaID

-----------------------------------------Find Staff Project and Area and his salary Others--------------------------------

INSERT INTO #TMonthlyPsothersHis
SELECT CmpPIN,CmpBasicAmount,CmpOtherAmount,CmpPFAmount, CmpTranDeduction,CmpIncomeTax,
CmpLoanReaAmount,CmpRCDeductAmt,CmpSecurityAmt,
CmpOtherAdjAmount,CmpUPLDeduction, PjaProjectId, PjaAreaID,CmpCashYN
FROM CurrentMonPsothers AS a
Inner join #TProjectArea AS b ON a.CmpPIN=b.stPIN
WHERE CmpYear= @PinYear and CmpMonth=@PinMonth
union all
SELECT MpoPIN,MpoBasicAmount,MpoOtherAmount,MpoPFAmount,MpoTranDeduction,MpoIncomeTax,
MpoLoanReaAmount,MpoRCDeductAmt,MpoSecurityAmt,
MpoOtherAdjAmount,MpoUPLDeduction, PjaProjectId, PjaAreaID, MpoCashYN FROM MonthlyPsothersHistory AS a
Inner join #TProjectArea AS b ON a.MpoPIN=b.stPIN
WHERE MpoYear=@PinYear and MpoMonth=@PinMonth

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Select * from #TMonthlyPsothersHis Order By PINID

INSERT INTO #TMonthyPsHistory
SELECT CmdPIN, b.SbkSbsId, sum(CmdAmount) AS Amount FROM currentMonPsDetails AS a
inner join SalaryBreakup AS b ON a.CmdSbkID=b.SbkID
WHERE CmdYear=@PinYear and CmdMonth=@PinMonth And SbkTaxableYN=1
GROUP BY CmdPIN, b.SbkSbsId
union all
SELECT MphPIN, b.SbkSbsId, sum(MphAmount) AS Amount FROM MonthlyPsDetailsHistory AS a
inner join SalaryBreakup AS b ON a.MphSbkID=b.SbkID
WHERE MphYear=@PinYear and Mp

Comments by pritam40 (Top 30 by date)