---- EXEC SALARY_FITMENT_SOM1 'KSLTDJMD','KSSD','1','PRF/001/Nov/12'
---- EXEC SALARY_FITMENT_SOM1 'PRF/001/Nov/12'
--- grant exec on SALARY_FITMENT_SOM1 to public
CREATE PROC SALARY_FITMENT_SOM1
(
@Auto_code varchar(20)
)
AS
BEGIN
DECLARE @COMPANY GLCOMPANY,
@LOCATION GLLOCN,
@LANGUAGE INT
SET @COMPANY = 'KSLTDJMD'
SET @LOCATION = 'KSSD'
SET @LANGUAGE = 1
SET NOCOUNT ON
CREATE TABLE #SALARY_FITMENT_SOM_TABLE1
(
C03_Organisation_code GLCOMPANY NULL,
C29_Location_code GLLOCN NULL,
Auto_code varchar(20),
H01_Emp_Num empnum NULL,
H10_Assignment_No tinyint NULL,
C02_Function_Code FUNCODE NULL,
C12_Positioncode POS NULL,
C08_Catg_code CATG NULL,
C09_Grade GRA NULL,
C10_Job_code JOB NULL,
H10_Effective_Date_From datetime,
C02_Function_Desc FUNCDESC NULL,
C12_Positiondesc POSDESC NULL,
H10_Base_Salary cmn_Pamt NULL,
H10_Base_Salary_G cmn_Pamt NULL,
emp_name varchar(100) NULL,
High_salary cmn_Pamt NULL,
Low_Salary cmn_Pamt NULL,
CTC NUMERIC(28,2) NULL,
CASH NUMERIC(28,2) NULL,
HRA cmn_Pamt NULL,
HRA_G cmn_Pamt NULL,
BONUS cmn_Pamt NULL,
PF cmn_Pamt NULL,
GRATUITY cmn_Pamt NULL,
EL cmn_Pamt NULL,
ML cmn_Pamt NULL,
TA cmn_Pamt NULL,
EDU cmn_Pamt NULL,
EDU_G cmn_Pamt NULL,
STATUS varchar(10) NULL,
DOB SMALLDATETIME,
REC_SAL NUMERIC(28,2) NULL,
SAL_WORD varchar(40) NULL
)
CREATE TABLE #SALARY_FITMENT_SOM_TABLE2
(
C10_Job_code JOB NULL,
Salary cmn_Pamt NULL
)
---------------------------------------------------------------------------------------------
DECLARE @NAME VARCHAR(100), @JOB_CODE job, @CTC CMN_PAMT, @DOB SMALLDATETIME, @Date DATETIME, @Type varchar(10),@Grade varchar(10)
SET @NAME = (SELECT EMP_NAME FROM Salary_Fitment_Code WHERE Auto_code = @Auto_code)
SET @JOB_CODE = (SELECT JobCode FROM Salary_Fitment_Code WHERE Auto_code = @Auto_code)
SET @CTC = (SELECT CTC FROM Salary_Fitment_Code WHERE Auto_code = @Auto_code)
SET @DOB = (SELECT DOB FROM Salary_Fitment_Code WHERE Auto_code = @Auto_code)
SET @DATE = (SELECT Doj FROM Salary_Fitment_Code WHERE Auto_code = @Auto_code)
SET @Type = (SELECT Type FROM Salary_Fitment_Code WHERE Auto_code = @Auto_code)
IF(LTRIM(RTRIM(@Type)) = 'CTC')
BEGIN
Set @Grade = 'OM'
END
ELSE IF(LTRIM(RTRIM(@Type)) = 'GROSS')
BEGIN
Set @Grade = 'SW'
END
ELSE IF(LTRIM(RTRIM(@Type)) = 'SDCWT')
BEGIN
Set @Grade = 'ST'
END
---------------------------------------------------------------------------------------------
DECLARE @SLAB CATG, @H10_Base_Salary cmn_Pamt
IF(@Grade='OM')
BEGIN
IF(@CTC > 38400)
BEGIN
Set @SLAB = 'SDCO6'
Set @H10_Base_Salary = @CTC*0.4167
END
ELSE IF(@CTC > 19100)
BEGIN
Set @SLAB = 'SDCO5'
Set @H10_Base_Salary = @CTC*0.4712
END
ELSE IF(@CTC > 12825)
BEGIN
Set @SLAB = 'SDCO4'
Set @H10_Base_Salary = @CTC*0.5263
END
ELSE IF(@CTC > 11169)
BEGIN
Set @SLAB = 'SDC3A'
Set @H10_Base_Salary = @CTC*0.5820
END
ELSE IF(@CTC > 7560)
BEGIN
Set @SLAB = 'SDCO3'
Set @H10_Base_Salary = @CTC*0.5820
END
ELSE IF(@CTC > 5999)
BEGIN
Set @SLAB = 'SDCO2'
Set @H10_Base_Salary = @CTC*0.6383
END
ELSE IF(@CTC > 4649)
BEGIN
Set @SLAB = 'SDCO1'
Set @H10_Base_Salary = @CTC*0.8201
END
END
ELSE IF(@Grade='ST')
BEGIN
IF(@CTC > 40000)
BEGIN
Set @SLAB = 'SDWT6'
Set @H10_Base_Salary = '10000.00'
END
ELSE IF(@CTC > 20000)
BEGIN
Set @SLAB = 'SDWT5'
Set @H10_Base_Salary = '8000.00'
END
ELSE IF(@CTC > 13500)
BEGIN
Set @SLAB = 'SDWT4'
Set @H10_Base_Salary = '7000.00'
END
ELSE IF(@CTC > 8000)
BEGIN
Set @SLAB = 'SDWT3'
Set @H10_Base_Salary = '4000.00'
END
ELSE IF(@CTC > 6000)
BEGIN
Set @SLAB = 'SDWT2'
Set @H10_Base_Salary = '3800.00'
END
ELSE IF(@CTC > 4300)
BEGIN
Set @SLAB = 'SDWT1'
Set @H10_Base_Salary = '3800.00'
END
END
ELSE IF(@Grade='SW')
BEGIN
IF(@CTC > 7600)
BEGIN
Set @SLAB = 'SDCW3'
Set @H10_Base_Salary = @CTC*0.5
END
ELSE IF(@CTC > 5900)
BEGIN
Set @SLAB = 'SDCW2'
Set @H10_Base_Salary = @CTC*0.65
END
ELSE IF(@CTC >= 3800)
BEGIN
Set @SLAB = 'SDCW1'
Set @H10_Base_Salary = @CTC*0.77
IF(@H10_Base_Salary<3800 br="" nbsp="">begin
SET @H10_Base_Salary = 3800
END
END
END
------------------------------------------
------------------------------------------
INSERT INTO #SALARY_FITMENT_SOM_TABLE1
(DOB,C03_Organisation_code,C29_Location_code,Auto_code,H10_Assignment_No,C08_Catg_code,C09_Grade,
C10_Job_code,H10_Effective_Date_From,C02_Function_Desc,C12_Positiondesc,H10_Base_Salary,H10_Base_Salary_G,emp_name,High_salary,
Low_Salary,CTC,CASH,HRA_G,EDU_G,STATUS,HRA,BONUS,PF,GRATUITY,EL,ML,TA,EDU)
select @DOB,@COMPANY,@LOCATION,@Auto_code,1,@SLAB,a.Grade_Code,@JOB_CODE,@DATE,a.Department,a.Position,
@H10_Base_Salary,0,@NAME,0,0,@CTC,0,0,0,'CTC',
HRA = CASE
WHEN @SLAB = 'SDCO1' THEN @H10_Base_Salary*0.0
WHEN @SLAB = 'SDCO2' THEN @H10_Base_Salary*0.3
WHEN @SLAB = 'SDCO3' THEN @H10_Base_Salary*0.3
WHEN @SLAB = 'SDC3A' THEN @H10_Base_Salary*0.4361
WHEN @SLAB = 'SDCO4' THEN @H10_Base_Salary*0.4
WHEN @SLAB = 'SDCO5' THEN @H10_Base_Salary*0.45
WHEN @SLAB = 'SDCO6' THEN @H10_Base_Salary*0.5
WHEN @SLAB = 'SDCW2' THEN @H10_Base_Salary*0.4
WHEN @SLAB = 'SDCW3' THEN @H10_Base_Salary*0.6
WHEN @SLAB = 'SDWT1' THEN @H10_Base_Salary/15.2
WHEN @SLAB = 'SDWT2' THEN @H10_Base_Salary/7.6
WHEN @SLAB = 'SDWT3' THEN @H10_Base_Salary/4
WHEN @SLAB = 'SDWT4' THEN (@H10_Base_Salary*2)/7
WHEN @SLAB = 'SDWT5' THEN @H10_Base_Salary/2
WHEN @SLAB = 'SDWT6' THEN (@H10_Base_Salary*3)/5
ELSE 0
END,
BONUS = CASE
WHEN @SLAB = 'SDCO1' THEN @H10_Base_Salary*0.0833
WHEN @SLAB = 'SDCO2' THEN @H10_Base_Salary*0.0833
WHEN @SLAB = 'SDCO3' THEN @H10_Base_Salary*0.0833
WHEN @SLAB = 'SDC3A' THEN @H10_Base_Salary*0.0833
WHEN @SLAB = 'SDCO4' THEN @H10_Base_Salary*0.0833
WHEN @SLAB = 'SDCO5' THEN @H10_Base_Salary*0.0833
WHEN @SLAB = 'SDCO6' THEN @H10_Base_Salary*0.0833
ELSE 0
END,
PF = CASE
WHEN @SLAB = 'SDCO1' THEN @H10_Base_Salary*0.12
WHEN @SLAB = 'SDCO2' THEN @H10_Base_Salary*0.12
WHEN @SLAB = 'SDCO3' THEN @H10_Base_Salary*0.12
WHEN @SLAB = 'SDCW1' THEN @H10_Base_Salary*0.12
WHEN @SLAB = 'SDCW2' THEN @H10_Base_Salary*0.12
WHEN @SLAB = 'SDCW3' THEN @H10_Base_Salary*0.12
WHEN @SLAB = 'SDWT1' THEN @H10_Base_Salary*0.12
WHEN @SLAB = 'SDWT2' THEN @H10_Base_Salary*0.12
WHEN @SLAB = 'SDWT3' THEN @H10_Base_Salary*0.12
ELSE 0
END,
GRATUITY = CASE
WHEN @SLAB = 'SDCO1' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO2' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO3' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDC3A' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO4' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO5' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO6' THEN @H10_Base_Salary*0.05
ELSE 0
END,
EL = CASE
WHEN @SLAB = 'SDCO1' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO2' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO3' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDC3A' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO4' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO5' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO6' THEN @H10_Base_Salary*0.05
ELSE 0
END,
ML = CASE
WHEN @SLAB = 'SDCO3' THEN @H10_Base_Salary*0.10
WHEN @SLAB = 'SDC3A' THEN @H10_Base_Salary*0.10
WHEN @SLAB = 'SDCO4' THEN @H10_Base_Salary*0.10
WHEN @SLAB = 'SDCO5' THEN @H10_Base_Salary*0.10
WHEN @SLAB = 'SDCO6' THEN @H10_Base_Salary*0.15
ELSE 0
END,
TA = CASE
WHEN @SLAB = 'SDCO3' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDC3A' THEN @H10_Base_Salary*0.05
WHEN @SLAB = 'SDCO4' THEN @H10_Base_Salary*0.10
WHEN @SLAB = 'SDCO5' THEN @H10_Base_Salary*0.20
WHEN @SLAB = 'SDCO6' THEN @H10_Base_Salary*0.30
ELSE 0
END,
EDU = CASE
WHEN @SLAB = 'SDCO2' THEN @H10_Base_Salary*0.0473
WHEN @SLAB = 'SDCO3' THEN @H10_Base_Salary*0.0488
WHEN @SLAB = 'SDC3A' THEN @H10_Base_Salary*0.0488
WHEN @SLAB = 'SDCO4' THEN @H10_Base_Salary*0.2167
WHEN @SLAB = 'SDCO5' THEN @H10_Base_Salary*0.2889
WHEN @SLAB = 'SDCO6' THEN @H10_Base_Salary*0.3667
WHEN @SLAB = 'SDCW1' THEN @CTC-@H10_Base_Salary
WHEN @SLAB = 'SDCW2' THEN @H10_Base_Salary*0.1384
WHEN @SLAB = 'SDCW3' THEN @H10_Base_Salary*0.4
WHEN @SLAB = 'SDWT1' THEN @H10_Base_Salary/15.2
WHEN @SLAB = 'SDWT2' THEN @H10_Base_Salary/7.6
WHEN @SLAB = 'SDWT3' THEN @H10_Base_Salary/4
WHEN @SLAB = 'SDWT4' THEN @H10_Base_Salary/7
WHEN @SLAB = 'SDWT5' THEN (@H10_Base_Salary*3)/8
WHEN @SLAB = 'SDWT6' THEN (@H10_Base_Salary*2)/5
ELSE 0
END
from jobcode_master a
where a.Job_code = @JOB_CODE
update #SALARY_FITMENT_SOM_TABLE1
set Cash = CTC - (H10_Base_Salary+HRA+EDU)
where C08_Catg_code IN ('SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')
update #SALARY_FITMENT_SOM_TABLE1
set CTC = (H10_Base_Salary+HRA+EDU)
where C08_Catg_code IN ('SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')
update #SALARY_FITMENT_SOM_TABLE1
set HRA_G = HRA
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')
update #SALARY_FITMENT_SOM_TABLE1
set HRA = 0
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')
update #SALARY_FITMENT_SOM_TABLE1
set EDU_G = EDU
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')
update #SALARY_FITMENT_SOM_TABLE1
set EDU = 0
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')
update #SALARY_FITMENT_SOM_TABLE1
set H10_Base_Salary_G = H10_Base_Salary
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')
update #SALARY_FITMENT_SOM_TABLE1
set H10_Base_Salary = 0
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')
update #SALARY_FITMENT_SOM_TABLE1
set STATUS = 'GROSS'
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')
INSERT INTO #SALARY_FITMENT_SOM_TABLE2
Select C10_Job_Code,High_Salary
From salary_approval1
where C10_Job_Code = @JOB_CODE
INSERT INTO #SALARY_FITMENT_SOM_TABLE2
Select C10_Job_Code,Low_Salary
From salary_approval1
where C10_Job_Code = @JOB_CODE
INSERT INTO #SALARY_FITMENT_SOM_TABLE2
Select C10_Job_Code,CTC
From salary_approval1
where C10_Job_Code = @JOB_CODE
Delete from #SALARY_FITMENT_SOM_TABLE2
where salary = 0
update #SALARY_FITMENT_SOM_TABLE1
set High_salary = (Select MAX(Salary) FROM #SALARY_FITMENT_SOM_TABLE2)
update #SALARY_FITMENT_SOM_TABLE1
set Low_salary = (Select MIN(Salary) FROM #SALARY_FITMENT_SOM_TABLE2)
update #SALARY_FITMENT_SOM_TABLE1
set REC_SAL = CASE
when cash = 0.00 then CTC
else (CTC + CASH)
end
update #SALARY_FITMENT_SOM_TABLE1
set SAL_WORD = CASE
when cash = 0.00 then CONVERT(VARCHAR,CTC)
else CONVERT(VARCHAR,CTC)+ ' + ' + CONVERT(VARCHAR,CASH)
end
select * from #SALARY_FITMENT_SOM_TABLE1
END
---- EXEC SALARY_FITMENT_SOM1 'PRF/001/Nov/10'
No comments:
Post a Comment