SQL PROCEDURE USING CONDITION

---- EXEC SALARY_FITMENT_SOM 'INC/0003/2013'        
ALTER PROC SALARY_FITMENT_SOM                 
(     
@Auto_code    varchar(20)                             
)         
         
AS         
BEGIN         
     
Declare @From_H01_emp_num empnum, 
@To_H01_emp_num empnum,   
@job_code job, 
@C03_Organisation_code GLCOMPANY,                     
@C29_Location_Code GLLOCN,                     
@lang_id      int                     
      
SET @From_H01_emp_num = (SELECT Code FROM Salary_Fitment_Code WHERE Auto_code = @Auto_code)               
 
 
set @C03_Organisation_code = 'KSLTDJMD'   
set @C29_Location_Code = 'KSSD'   
set @lang_id = 1              
Set @To_H01_emp_num = @From_H01_emp_num      
         
CREATE TABLE  #SALARY_FITMENT_SOM_TABLE                 
         
( 
Auto_code    varchar(20),                 
C03_Organisation_code GLCOMPANY NULL,                             
C29_Location_code GLLOCN NULL,                             
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(60) NULL,         
High_salary cmn_Pamt NULL,         
Low_Salary cmn_Pamt NULL,         
CTC NUMERIC(28,2) NULL,--cmn_Pamt NULL,         
CASH NUMERIC(28,2) NULL,--cmn_Pamt 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,
REC_SAL NUMERIC(28,2) NULL,   
SAL_WORD varchar(40) NULL                                  
)      
   
         
CREATE TABLE  #SALARY_INCREMENT_SOM_TABLE                 
(         
C10_Job_code JOB NULL,                                 
Salary cmn_Pamt NULL         
)    
         
INSERT INTO #SALARY_FITMENT_SOM_TABLE         
(Auto_code,C03_Organisation_code,C29_Location_code,H01_Emp_Num,H10_Assignment_No,C02_Function_Code,C12_Positioncode,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 @Auto_code,a.C03_Organisation_code,a.C29_Location_code,a.H01_Emp_Num,a.H10_Assignment_No,a.C02_Function_Code,a.C12_Positioncode,         
a.C08_Catg_code,a.C09_Grade,a.C10_Job_code,a.H10_Effective_Date_From,b.C02_Function_Desc,c.C12_Positiondesc,a.H10_Base_Salary,0,         
ISNULL(d.H04_First_Name,'')+' '+ISNULL(d.H04_Middle_Name,'')+' '+ISNULL(d.H04_Last_Name,''),e.High_salary,e.Low_Salary,e.CTC,e.CASH,         
0,0,'CTC',         
HRA = CASE          
WHEN a.C08_Catg_code = 'SDCO1' THEN a.H10_Base_Salary*0.0         
WHEN a.C08_Catg_code = 'SDCO2' THEN a.H10_Base_Salary*0.3         
WHEN a.C08_Catg_code = 'SDCO3' THEN a.H10_Base_Salary*0.3         
WHEN a.C08_Catg_code = 'SDC3A' THEN a.H10_Base_Salary*0.4361         
WHEN a.C08_Catg_code = 'SDCO4' THEN a.H10_Base_Salary*0.4         
WHEN a.C08_Catg_code = 'SDCO5' THEN a.H10_Base_Salary*0.45         
WHEN a.C08_Catg_code = 'SDCO6' THEN a.H10_Base_Salary*0.5         
WHEN a.C08_Catg_code = 'SDCW2' THEN a.H10_Base_Salary*0.4         
WHEN a.C08_Catg_code = 'SDCW3' THEN a.H10_Base_Salary*0.6         
WHEN a.C08_Catg_code = 'SDWT1' THEN a.H10_Base_Salary/15.2         
WHEN a.C08_Catg_code = 'SDWT2' THEN a.H10_Base_Salary/7.6         
WHEN a.C08_Catg_code = 'SDWT3' THEN a.H10_Base_Salary/4         
WHEN a.C08_Catg_code = 'SDWT4' THEN (a.H10_Base_Salary*2)/7         
WHEN a.C08_Catg_code = 'SDWT5' THEN a.H10_Base_Salary/2         
WHEN a.C08_Catg_code = 'SDWT6' THEN (a.H10_Base_Salary*3)/5         
ELSE 0         
END,         
BONUS = CASE          
WHEN a.C08_Catg_code = 'SDCO1' THEN a.H10_Base_Salary*0.0833         
WHEN a.C08_Catg_code = 'SDCO2' THEN a.H10_Base_Salary*0.0833         
WHEN a.C08_Catg_code = 'SDCO3' THEN a.H10_Base_Salary*0.0833         
WHEN a.C08_Catg_code = 'SDC3A' THEN a.H10_Base_Salary*0.0833         
WHEN a.C08_Catg_code = 'SDCO4' THEN a.H10_Base_Salary*0.0833         
WHEN a.C08_Catg_code = 'SDCO5' THEN a.H10_Base_Salary*0.0833         
WHEN a.C08_Catg_code = 'SDCO6' THEN a.H10_Base_Salary*0.0833         
ELSE 0         
END,         
PF = CASE          
WHEN a.C08_Catg_code = 'SDCO1' THEN a.H10_Base_Salary*0.12         
WHEN a.C08_Catg_code = 'SDCO2' THEN a.H10_Base_Salary*0.12         
WHEN a.C08_Catg_code = 'SDCO3' THEN a.H10_Base_Salary*0.12         
WHEN a.C08_Catg_code = 'SDCW1' THEN a.H10_Base_Salary*0.12         
WHEN a.C08_Catg_code = 'SDCW2' THEN a.H10_Base_Salary*0.12         
WHEN a.C08_Catg_code = 'SDCW3' THEN a.H10_Base_Salary*0.12         
WHEN a.C08_Catg_code = 'SDWT1' THEN a.H10_Base_Salary*0.12         
WHEN a.C08_Catg_code = 'SDWT2' THEN a.H10_Base_Salary*0.12         
WHEN a.C08_Catg_code = 'SDWT3' THEN a.H10_Base_Salary*0.12         
ELSE 0         
END,         
GRATUITY = CASE          
WHEN a.C08_Catg_code = 'SDCO1' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO2' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO3' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDC3A' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO4' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO5' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO6' THEN a.H10_Base_Salary*0.05         
ELSE 0         
END,         
EL = CASE          
WHEN a.C08_Catg_code = 'SDCO1' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO2' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO3' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDC3A' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO4' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO5' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO6' THEN a.H10_Base_Salary*0.05         
ELSE 0         
END,         
ML = CASE         
WHEN a.C08_Catg_code = 'SDCO3' THEN a.H10_Base_Salary*0.10         
WHEN a.C08_Catg_code = 'SDC3A' THEN a.H10_Base_Salary*0.10         
WHEN a.C08_Catg_code = 'SDCO4' THEN a.H10_Base_Salary*0.10         
WHEN a.C08_Catg_code = 'SDCO5' THEN a.H10_Base_Salary*0.10         
WHEN a.C08_Catg_code = 'SDCO6' THEN a.H10_Base_Salary*0.15         
ELSE 0         
END,         
TA = CASE         
WHEN a.C08_Catg_code = 'SDCO3' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDC3A' THEN a.H10_Base_Salary*0.05         
WHEN a.C08_Catg_code = 'SDCO4' THEN a.H10_Base_Salary*0.10         
WHEN a.C08_Catg_code = 'SDCO5' THEN a.H10_Base_Salary*0.20         
WHEN a.C08_Catg_code = 'SDCO6' THEN a.H10_Base_Salary*0.30         
ELSE 0         
END,         
EDU = CASE          
WHEN a.C08_Catg_code = 'SDCO2' THEN a.H10_Base_Salary*0.0473         
WHEN a.C08_Catg_code = 'SDCO3' THEN a.H10_Base_Salary*0.0488         
WHEN a.C08_Catg_code = 'SDC3A' THEN a.H10_Base_Salary*0.0488         
WHEN a.C08_Catg_code = 'SDCO4' THEN a.H10_Base_Salary*0.2167         
WHEN a.C08_Catg_code = 'SDCO5' THEN a.H10_Base_Salary*0.2889         
WHEN a.C08_Catg_code = 'SDCO6' THEN a.H10_Base_Salary*0.3667         
WHEN a.C08_Catg_code = 'SDCW1' THEN e.CTC-a.H10_Base_Salary         
WHEN a.C08_Catg_code = 'SDCW2' THEN a.H10_Base_Salary*0.1384         
WHEN a.C08_Catg_code = 'SDCW3' THEN a.H10_Base_Salary*0.4         
WHEN a.C08_Catg_code = 'SDWT1' THEN a.H10_Base_Salary/15.2         
WHEN a.C08_Catg_code = 'SDWT2' THEN a.H10_Base_Salary/7         
WHEN a.C08_Catg_code = 'SDWT3' THEN a.H10_Base_Salary/4         
WHEN a.C08_Catg_code = 'SDWT4' THEN a.H10_Base_Salary/7         
WHEN a.C08_Catg_code = 'SDWT5' THEN (a.H10_Base_Salary*3)/8         
WHEN a.C08_Catg_code = 'SDWT6' THEN (a.H10_Base_Salary*2)/5         
ELSE 0         
END         
from H10_EmpAsgn a,C02_Function_Details b,C12_Position c,common..H04_NAMEMASTER d,salary_approval1 e         
where a.C03_Organisation_code = @C03_Organisation_code                 
  and a.C29_Location_Code = @C29_Location_Code             
  and a.H01_emp_num between @From_H01_emp_num and @To_H01_emp_num                   
  and a.H10_Effective_Date_To IS NULL                        
  and b.C03_Organisation_code = @C03_Organisation_code                 
  and b.C02_Function_Code = a.C02_Function_Code                 
  and c.C03_Organisation_code = @C03_Organisation_code                 
  and c.C12_positioncode = a.C12_Positioncode                 
  and a.H01_emp_num = d.H01_emp_num             
  and a.H01_Emp_Num = e.H01_emp_num                    
  and e.H10_Effective_Date_To IS NULL          
         
Select @Job_Code = (Select distinct C10_Job_code from #SALARY_FITMENT_SOM_TABLE)    
        
update #SALARY_FITMENT_SOM_TABLE         
set HRA_G = HRA         
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')         
         
         
update #SALARY_FITMENT_SOM_TABLE         
set HRA = 0         
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')         
         
         
update #SALARY_FITMENT_SOM_TABLE         
set EDU_G = EDU         
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')         
         
         
update #SALARY_FITMENT_SOM_TABLE         
set EDU = 0         
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')         
         
update #SALARY_FITMENT_SOM_TABLE         
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_TABLE         
set H10_Base_Salary = 0         
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')         
         
         
update #SALARY_FITMENT_SOM_TABLE         
set STATUS = 'GROSS'         
where C08_Catg_code IN ('SDCW1','SDCW2','SDCW3','SDWT1','SDWT2','SDWT3','SDWT4','SDWT5','SDWT6')   
   
INSERT INTO #SALARY_INCREMENT_SOM_TABLE        
Select C10_Job_Code,CTC         
From salary_approval1         
where C10_Job_Code = @JOB_CODE    
   
Delete from #SALARY_INCREMENT_SOM_TABLE   
where salary = 0   
             
update #SALARY_FITMENT_SOM_TABLE             
set High_salary = (Select MAX(Salary) FROM #SALARY_INCREMENT_SOM_TABLE)         
         
update #SALARY_FITMENT_SOM_TABLE             
set Low_salary = (Select MIN(Salary) FROM #SALARY_INCREMENT_SOM_TABLE)        
   
update #SALARY_FITMENT_SOM_TABLE     
  set REC_SAL = CASE     
  when cash = 0.00 then CTC     
  else (CTC + CASH)     
  end     
   
  update #SALARY_FITMENT_SOM_TABLE     
  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_TABLE order by H01_Emp_NUM         
         
END
---- EXEC SALARY_FITMENT_SOM 'INC/0003/2013'