CREATE Procedure Proc_PutEmp_Mst_type
@strParam varchar(2000),
@strMsgCode varchar(10) output
AS
BEGIN
declare @modifier varchar(25)
DECLARE @ECODE VARCHAR(10)
DECLARE @NAME VARCHAR(50)
DECLARE @GRADE VARCHAR(10)
DECLARE @QUALI VARCHAR(30)
DECLARE @ADD1 VARCHAR(50)
DECLARE @ADD2 VARCHAR(50)
DECLARE @ADD3 VARCHAR(50)
DECLARE @ADD4 VARCHAR(50)
DECLARE @PIN VARCHAR(10)
DECLARE @PHONE VARCHAR(20)
DECLARE @FAX VARCHAR(20)
DECLARE @DESC VARCHAR(50)
DECLARE @DOB varchar(20)
DECLARE @DOJ varchar(20)
DECLARE @DOW varchar(20)
DECLARE @EMAIL VARCHAR(50)
DECLARE @DESIG VARCHAR(10)
DECLARE @BNKCODE VARCHAR(50)
DECLARE @ACCNO VARCHAR(50)
DECLARE @Mode int
DECLARE @CNT INT
DECLARE @INTPOS INT
DECLARE @REMAIN VARCHAR(2000)
SET @REMAIN=@STRPARAM
DECLARE @DOBd datetime
DECLARE @DOJd datetime
DECLARE @DOWd datetime
declare @doc datetime
declare @dor datetime
declare @docd datetime
declare @dord datetime
declare @title varchar(6)
declare @sex char(3)
declare @deptment varchar(10)
declare @location varchar(10)
declare @offph varchar(20)
declare @offExten varchar(20)
declare @emailpers varchar(50)
declare @role varchar(10)
DECLARE @PARENT VARCHAR(10)
DECLARE @JObDesc VARCHAR(50)
DECLARE @HQAddress VARCHAR(200)
Declare @Padd1 VARCHAR(200)
Declare @Padd2 VARCHAR(200)
Declare @Padd3 VARCHAR(200)
Declare @Padd4 VARCHAR(200)
Declare @madd1 VARCHAR(200)
Declare @madd2 VARCHAR(200)
Declare @madd3 VARCHAR(200)
Declare @madd4 VARCHAR(200)
Declare @branchname VARCHAR(100)
Declare @rtgs VARCHAR(20)
Declare @hpin VARCHAR(20)
Declare @ppin VARCHAR(20)
Declare @mpin VARCHAR(20)
Declare @emptype INT
declare @pan varchar(10)
Declare @autoemail varchar(100)
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @title=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @sex=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @deptment=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @location=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @offph=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @offExten=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @doc=convert(datetime,SUBSTRING(@REMAIN,1,@INTPOS-1),103)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @dor=convert(datetime,SUBSTRING(@REMAIN,1,@INTPOS-1),103)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @emailpers=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @role=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @modifier=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ECODE=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @NAME=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @GRADE=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @QUALI=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ADD1=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ADD2=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ADD3=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ADD4=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @PIN=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @PHONE=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @FAX=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @DESC=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @DOB=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @DOJ=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @DOW=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @email=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @desig=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @BNKCODE=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ACCNO=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Mode=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @PARENT=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @JObDesc=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @HQAddress=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Padd1=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Padd2=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Padd3=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Padd4=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @madd1=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @madd2=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @madd3=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @madd4=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @branchname =SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @rtgs=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @hpin=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ppin=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
-- SET @INTPOS=CHARINDEX('»',@REMAIN)
-- SET @emptype=SUBSTRING(@REMAIN,1,@INTPOS-1)
-- SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
--
-- SET @INTPOS=CHARINDEX('»',@REMAIN)
-- SET @mpin=@REMAIN
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @mpin=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @emptype=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @pan=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @autoemail=@REMAIN
if @doc=''
begin
set @docd=NULL
end
else
begin
set @docd=convert(datetime,@doc,103)
end
if @dor=''
begin
set @dord=NULL
end
else
begin
set @dord=convert(datetime,@dor,103)
end
IF @DOj=''
BEGIN
set @DOjd=NULL
END
else
begin
set @DOjd=convert(datetime,@DOj,103)
end
IF @DOb=''
BEGIN
set @DObd=NULL
END
else
begin
set @DObd=convert(datetime,@DOb,103)
end
IF @DOw=''
BEGIN
set @DOwd=NULL
END
else
begin
set @DOwd=convert(datetime,@DOw,103)
end
select @Mode
if @Mode = 0
BEGIN
set @cnt=(SELECT COUNT(*) FROM TBL_Emp_MST WHERE c_Empcode = @Ecode)
IF @cnt=0
BEGIN
INSERT INTO
Tbl_Emp_Mst
(
c_Empcode,
C_name,
c_Grade,
c_Qualification,
c_Add_1,
c_Add_2,
c_Add_3,
c_Add_4,
c_pin,
c_mobile_no,
c_Fax,
c_desc,
D_DOB,
D_DOj,
D_DOW,
c_email_office,
c_desig,
c_bankcode,
C_Accno,
n_deleted,
d_created,
c_modifier,
d_modified,
c_nameprefix,c_sex,c_dept_code,c_loc_code,c_office_phone_no,c_office_phone_extnno,c_email_personal,c_role,
d_conf_date,d_relieve_date,c_jobdesc,c_hq_address ,c_padd_1, c_padd_2,c_padd_3,c_padd_4 ,C_MAdd_1,C_MAdd_2,C_MAdd_3,C_MAdd_4
,c_branch_name,c_rtgs_neft,c_hq_pin,c_padd_pin ,c_madd_pin,c_emp_type,c_pan,c_auto_report_email
)
VALUES
(
@ECODE,
@NAME,
@GRADE,
@QUALI,
@ADD1,
@ADD2,
@ADD3,
@ADD4,
@PIN,
@PHONE,
@FAX,
@DESC,
@DOBd,
@DOJd,
@DOWd,
@EMAIL,
@desig,
@BNKCODE,
@ACCNO,
0,
getdate(),
@modifier,
getdate(),
@title,@sex,@deptment,@location,@offph,@offExten,@emailpers,@role,
@docd,@dord,@JObDesc,@HQAddress , @Padd1,@Padd2,@Padd3,@Padd4,@madd1,@madd2,@madd3,@madd4
,@branchname, @rtgs, @hpin,@ppin,@mpin ,@emptype,@pan,@autoemail
)
INSERT INTO
Tbl_Emp_parent_fs
(
c_Empcode,
C_parentCode
)
values
(
@ECODE,@PARENT
)
END
ELSE
BEGIN
SET @strMsgCode='3001'
RETURN
END
END
if @Mode = 1
BEGIN IF (SELECT COUNT(*) FROM Tbl_Emp_Mst WHERE c_empcode = @ecode ) = 1
BEGIN
UPDATE
Tbl_Emp_Mst
SET
C_NAME=@NAME,
C_Grade=@GRADE,
C_Qualification=@QUALI,
C_Add_1=@ADD1,
C_Add_2=@ADD2,
C_Add_3=@ADD3,
C_Add_4=@ADD4,
C_Pin=@PIN,
C_mobile_no=@PHONE,
C_Fax=@FAX,
C_Desc=@DESC,
D_DOB=@DOBd,
D_DOJ=@DOJd,
D_DOW=@DOWd,
C_Email_office=@EMAIL,
c_desig=@desig,
c_bankcode=@BNKCODE,
C_Accno=@ACCNO,
c_modifier=@modifier,
c_padd_1=@Padd1,
c_padd_2=@Padd2,
c_padd_3=@Padd3,
c_padd_4=@Padd4,
c_madd_1=@madd1,
c_madd_2=@madd2,
c_madd_3=@madd3,
c_madd_4=@madd4,
c_branch_name=@branchname,
c_rtgs_neft=@rtgs,
c_hq_pin=@hpin,
c_padd_pin=@ppin,
c_madd_pin=@mpin ,
c_emp_type=@emptype,
d_modified=getdate(),c_nameprefix=@title,c_sex=@sex,c_dept_code=@deptment,c_loc_code=@location,
c_office_phone_no=@offph,c_office_phone_extnno=@offExten,c_email_personal=@emailpers,c_role=@role,
d_conf_date=@docd,d_relieve_date=@dord,c_jobdesc=@JObDesc,c_hq_address= @HQAddress ,
c_pan=@pan ,c_auto_report_email=@autoemail
WHERE
c_EMpcode = @ecode
IF (SELECT COUNT(*) FROM Tbl_Emp_parent_fs WHERE c_empcode = @ecode ) = 1
BEGIN
update Tbl_Emp_parent_fs set C_parentCode = @PARENT where c_Empcode = @ECODE
END
Else
Begin
INSERT INTO
Tbl_Emp_parent_fs
(
c_Empcode,
C_parentCode
)
values
(
@ECODE,@PARENT
)
End
SET @strMsgCode='3002'
RETURN
END
ELSE
BEGIN
SET @strMsgCode='3001'
RETURN
END
END
IF @@ERROR<>0
BEGIN SET @strMsgCode='9000'
END
ELSE
BEGIN
SET @strMsgCode='3000'
END
end