-- EXEC PERDB..ctc_payset 'STGEF','RJS','1','01',NULL,NULL,01,12
ALTER procedure [dbo].[ctc_payset]
@comapnyvarchar(20),
@loc varchar(20),
@lan_idvarchar(10),
@payset varchar(20),
@deptvarchar(20) ,
@emp_code varchar(20),
@fmonth int,
@tmonth int
as
begin
if isnull(@dept,'')=''
select @dept = null
if isnull(@emp_code, '')=''
select @emp_code = null
create table #temp
(
H01_EMP_NUM varchar(40),
H01_Contact_Name2 varchar(40),
h01_social_security_no varchar(20),
h01_social_insurance_no varchar(20),
DOB datetime,
DOJ datetime,
P08_Payset_Desc varchar(40),
H01_KNOWN_AS varchar(40),
DESIGNATION varchar(40),
h01_contact_name1 varchar(40),
h40_emp_status4 varchar(40),
basic float,
hra float,
ca float ,
gross float,
basic_bonus float,
bonus float,
basic_pf float,
pf float,
lww float,
cl float,
sl float,
esi_grossfloat,
esi float,
ctcfloat ,
DOLvarchar(20),
dept_namevarchar(50),
reim float,
medical float,
conv float,
enter float,
lib float,
driver float,
helper float,
lta float,
uni float,
total float,
Payset varchar(50),
RES_addressvarchar(200) ,
PER_address varchar(200) ,
emp_count float ,
ter_status char(1),
Hra_min float,
Ca_min float,
Cat varchar(20),
SMART_CARD VARCHAR(20),
eff_dt datetime ,
title varchar(10) ,
weekly_off varchar(20),
weekly_desc varchar(50)
)
insert into #temp
SELECT distinct b.H01_EMP_NUM, b.H01_Contact_Name2 ,
b.h01_social_security_no,b.h01_social_insurance_no,
b.h01_birth_date ,b.h01_join_date,
g.P08_Payset_Desc,
(isnull(H01_First_Name,'') +' '+ isnull(H01_Middle_Name,'') +' '+ isnull(H01_Last_Name,'')) as H01_KNOWN_AS,H.c12_positiondesc ,
b.h01_contact_name1,c.h40_emp_status4,
c.h10_base_salary,'','','','','' ,'','','','','','','','','','' ,'','',
'','','','', '','','','','' ,'','' ,'','N','','',c.c08_catg_code ,'' ,c.H10_Effective_Date_From ,b.H03_title ,'',''
FROM
h01_identfcatnB (nolock),
h10_empasgn c (nolock),
p15_payroll_empasgn f (nolock),
p08_payset g(nolock),
c12_position H(nolock)
WHERE
b.h01_status in ('C','R')
and b.h01_status <>'T'
AND B.H01_EFFECTIVE_DATE_TOIS NULL
and c.h10_effective_date_to is null
and datepart(mm,h01_join_date) between @fmonthand @tmonth
and p15_eff_process_period_to is null
and f.p08_payset_code in (select payset from payset_group (nolock)where code = @PAYSET)
and g.p08_payset_code in (select payset from payset_group (nolock)where code = @payset)
and b.h01_emp_num = f.h01_emp_num
and f.p06_payroll_code = g.p06_payroll_code
and f.p08_payset_code= g.p08_payset_code
and B.H01_EMP_NUM = c.H01_EMP_NUM
and H.c12_positioncode= C.c12_positioncode
andb.h01_emp_num= isnull(@emp_code,b.h01_emp_num)
group by
b.H01_EMP_NUM,b.H01_Contact_Name2,b.h01_social_security_no,b.h01_social_insurance_no,b.h01_birth_date
,b.h01_join_date,g.P08_Payset_Desc ,
(isnull(H01_First_Name,'') +' '+ isnull(H01_Middle_Name,'') +' '+ isnull(H01_Last_Name,'')),H.c12_positiondesc ,b.h01_contact_name1,c.h40_emp_status4,
c.h10_base_salary,g.p08_payset_desc,c.c08_catg_code ,c.H10_Effective_Date_From ,b.H03_title
update #temp
set payset = (select distinct payset_desc from payset_group (nolock)where code = @PAYSET)
update #temp
set hra = (select round((basic*30/100),0)
WHERE h01_emp_num <> 'MD0001' and #temp.cat <>'MIn')
update #temp
set hra = 0
where p08_payset_desc like ('%TRAINE%')--,'PLANT TRAINEES')
update #temp
set hra_min = round(isnull(a.hra,0),0) from min_wages_det a
where a.h01_emp_num = #temp.h01_emp_num and #temp.cat ='Min' and H10_Effective_Date_From = #temp.eff_dt
update #temp
set ca = (select round((basic*15/100),0)
WHERE h01_emp_num <> 'MD0001' and #temp.cat <> 'Min' )
update #temp
set ca = '1600'
where ca >= '1600'
update #temp
set ca = 0
where p08_payset_desc like ('%TRAINE%')
update #temp
set Ca_min = round(isnull(a.conv,0),0) from min_wages_det a
where a.h01_emp_num = #temp.H01_EMP_NUM and #temp.cat = 'Min' and H10_Effective_Date_From = #temp.eff_dt
update #temp
set hra = case isnull(#temp.hra,'') when '' then #temp.hra_min else #temp.hra endfrom #temp
update #temp
set ca = case isnull(#temp.ca,'') when '' then #temp.ca_min else #temp.ca endfrom #temp
update #temp
set gross = isnull(basic,0)+isnull(hra,0)+isnull(ca,0)
update #temp
set basic_bonus = basic
where p08_payset_desc not like ('%TRAINEE%')
and h01_emp_num not in ('WK0690','WK0067','MD0001')
update #temp
set basic_bonus = 3500
where basic_bonus >= 3500
update #temp
set bonus = round(basic_bonus*8.50/100,0)
where h01_emp_num not in ('WK0690','WK0067','MD0001')
and p08_payset_desc not like ('%TRAINE%')
update #temp
set basic_pf = basic
where h01_emp_num not in ('WK0690')
update #temp
set basic_pf = 15000
where basic_pf >= 15000
and h01_emp_num <>'WK0067'
update #temp
set basic_pf = 0
where p08_payset_desc like ('%TRAINEE%')
update #temp
set pf =round(basic_pf*12/100,0)
WHERE p08_payset_desc not like ('%TRAINE%')
and h01_emp_num not in ('WK0690')
update #temp
set pf = 1800
WHERE pf > = 1800
and h01_emp_num not in ('WK0067','MD0001')
update #temp
set lww = round(basic/30/12*15,0)
where h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINE%')
update #temp
set cl =round(gross/30/12*7,0)
where h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINEE%')
update #temp
set sl =round(gross/30/12*7,0)
where gross >= 21000
AND h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINE%')
update #temp
set esi_gross = gross
update #temp
set esi_gross = 21000
where esi_gross >= 21000
AND h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINEE%')
update #temp
set esi =round(esi_gross*4.75/100,0)
where sl = '0'
AND h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINEE%')
update #temp
set medical = (select max(medical)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and medical > 0 )
update #temp
set conv = (select max(conv)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and conv > 0)
update #temp
set enter = (select max(enter)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and enter > 0)
update #temp
set lib = (select max(lib)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and lib > 0)
update #temp
set driver = (select max(driver)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and driver > 0)
update #temp
set helper = (select max(helper)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and helper > 0)
update #temp
set lta = (select max(lta)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and lta > 0)
update #temp
set uni = (select max(uni)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and uni > 0)
update #temp
set reim = round(ISNULL(medical,0)+ISNULL(conv,0)+ISNULL(enter,0)+ISNULL(lib,0)+ISNULL(driver,0)+ISNULL(helper,0)+ISNULL(lta,0)+ISNULL(uni,0),0)
update #temp
set ctc = isnull(basic,0)+isnull(hra,0)+isnull(ca,0)+isnull(bonus,0)+isnull(pf,0)+isnull(lww,0)
+isnull(cl,0)+isnull(sl,0)+isnull(esi,0)+isnull(medical,0)+isnull(conv,0)+isnull(enter,0)+
isnull(lib,0)+isnull(driver,0)+isnull(helper,0)+isnull(lta,0)+isnull(uni,0)
update #temp
set weekly_desc = case when #temp.weekly_off = 'WC001' Then 'SUNDAY WEEKLY OFF'
when #temp.weekly_off = 'MON01' Then 'MONDAY WEEKLY OFF'
when #temp.weekly_off = 'TUE01' Then 'THESDAY WEEKLY OFF'
when #temp.weekly_off = 'WED01' Then 'WEDNESDAY WEEKLY OFF'
when #temp.weekly_off = 'WC002' Then 'THRUSDAY WEEKLY OFF'
when #temp.weekly_off = 'FRI01' Then 'FRIDAY WEEKLY OFF'
when #temp.weekly_off = 'SAT01' Then 'SATURDAY WEEKLY OFF'
ELSE 'NOT ASIGN WEEKLY OFF'
END
update #temp
set DOL =e01_pay_thru_date
from #temp a,e01_exit_details b
where b.h01_emp_num = a.h01_emp_num
update #temp
set dept_name = a.c01_desc
from C01_Codedesc_Mas a ,h10_empasgn b
where b.h40_emp_status4 = a.c01_code and A.C01_TYPE = 'ES4'
ANDB.H01_EMP_NUM = #temp.H01_EMP_NUM
and h10_effective_date_to is null
update #temp
set emp_count = (select count(distinct #temp.h01_emp_num) from #temp)
update #temp
set RES_address = LTRIM(RTRIM(H02_ADDRESS1)) + ', ' + LTRIM(RTRIM(H02_ADDRESS2))+ ', ' + LTRIM(RTRIM(H02_ADDRESS3))+ ', '+
(SELECT LTRIM(RTRIM(MAX(c.C14_STATE_NAME )))
FROM C14_STATECODE c
WHERE C.C14_STATE_CODE = H2.C14_STATE_CODE)
FROMh02_address H2
WHERE H2.H02_ADDRESS_OF = #temp.H01_EMP_NUM
AND H2.H02_ADDRESS_TYPE = 'RES'
and h2.h02_effective_to is null
update #temp
set SMART_CARD =H41_ID_NO
from H41_IdDetails h
where h.h01_emp_num = #temp.H01_EMP_NUM
and h41_id_type ='ESI'
update #temp
set ter_status='Y'
from #temp a,h01_identfcatn b (nolock) ,e01_exit_details c (nolock)
where a.H01_EMP_NUM=b.H01_EMP_NUM
andb.H01_EMP_NUM=c.H01_EMP_NUM
andb.h01_effective_date_to is null
andh01_status<>'R'
select * from #temp
declare @tot_ter int
select @tot_ter=count(*) from #temp where ter_status='Y'
select @tot_ter tot_terMINATED,*, datepart(year,getdate())- datepart(year,dob) age ,datepart(year,getdate())- datepart(year,doj) tot_exp from #temp
end
when i execute the procedure with parameters its working fine ( EXEC PERDB..ctc_payset 'STGEF','RJS','1','01',NULL,NULL,01,12 )
I want to use output of the procedure in View / table format so that user list it and use search / sort / records per page features etc.
I can't use table because, if two or more users execute the procedure at same time the results will not correct.