|
So your data for BusRouteNo table was (3 rows):
BNo
---
73
36
457
And for BusStops table it was (9 rows):
BNo Stops
--- -----
73 abc
73 xyz
73 wer
36 qwe
36 abc
36 wer
457 abc
457 ghi
457 klm
And you wanted to get busses 73 and 36 based on their stops so that if the bus stops on both 'abc' and 'wer' they are selected.
If you don't want any data from busstops I would use correlated exists-clause like following:
SELECT *
FROM bus
WHERE EXISTS (SELECT 1
FROM busstops bs1
WHERE bs1.BNo = bus.BNo
AND bs1.Stops = 'abc')
AND EXISTS (SELECT 1
FROM busstops bs2
WHERE bs2.BNo = bus.BNo
AND bs2.Stops = 'wer')
Would this help you
Mika
|
|
|
|
|
thanx Mika,seems this would help..
lukin forward for future help f needed.
thanx for the effort..
Regards...
Amit 
|
|
|
|
|
You're welcome
Mika
|
|
|
|
|
hi mika,what u told did work.
one more help from u..
i am usin Visual Studio 2005.
here i am using DropDown List to select the Source and Destination for the Bus.
Now my problem is that in the Select Query u told me,how to pass the values of the Items Selected from the DropDownList.SelectedItems.Value.
the Selected Buses can be Idsplayed using a GridView.
the Select Query is working fine when i pass the values explicitly in the Sql Server 2000-Query Analyser.
i am not able to understand where to write the Sql Select Query with the
"DropDownList.SelectedItems.Value" in my Visual Sudio ".aspx" file.
m having tables exactly the way u told me.
hope u got what i want to say...
thanx...
|
|
|
|
|
This can be done by using parameters. In this cas when user selects a bus, you should retrieve the selected value and place it in a parameter. The sql statement could look like following (just using a parameter for bus):
SELECT *
FROM bus
WHERE BusNo = @BusNo
Then you create a parameter named @BusNo (the name can be whatever you like as long as it's the same in parameter object and sql statement) using SqlParameter-class and set a value to the parameter.
For further details see SqlParameter Class[^]
Mika
|
|
|
|
|
hi all
we are developing windows application ,for this we have to take database backup from our application using C#,we wrote code for this ,but it is not working ,it is showing below error message.
error Message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
please help me.
|
|
|
|
|
Could you post the t-sql statement for backup. Most likely the directory does not exist or the backup device does not exist.
Mika
|
|
|
|
|
Hello Friend
I have faced a problem in SQL Server
I have a stored procedure which works as follows
Declare @Id int
Select @id=max(id) from emp
Set @id=@id+1
update emp Set id=@id
return @id
Now, we call this stored procedure from our ASP.net application. It is working perfecly in single user environment,
but the problem is that when we are working in multi-user environment some time the procedure returns same id instead of unique id.
How can I solve it, so that every time the procedure returns unique value for each user.
Is there any technique to execute this stored procedure in queue.
Thanks in advance
|
|
|
|
|
Hi,
I would suggest that you reconsider the logic in your procedure from multi-user point of view.
However, if you want to prevent simultaneous operations with this structure, simply start with the update to get an exclusive lock. For example:
DECLARE @Id int
UPDATE emp SET id=(SELECT MAX(id) FROM emp);
SELECT @id=MAX(id) FROM emp;
RETURN @id;
Mika
|
|
|
|
|
I have written an SP which is inserting in all the dat in all the tables of SP in subsequent way and while designing I have taken care of all the foreign key constraints too.
following is the SP
CREATE PROCEDURE dbo.usp_create_new_sysid(
@p_client_prefix varchar(20),
@p_client_name varchar(256),
@p_branch_name varchar(256),
@p_location_name varchar(256))
AS
BEGIN
DECLARE @v_sys_id int,
@v_prefix_exists int,
@v_email_id_name varchar(250),
@v_count int
select @v_count=0
/* Check if prefix already exists */
select @v_prefix_exists = 1 from tblsystem where prefix = @p_client_prefix;
if (@v_prefix_exists = 1)
begin
select 0 SYSID
return
end
/* Get max sys_id + 1 to assign sys_id for the new company */
select @v_sys_id = max(id) + 1 from tblSystem
/* 12 NOV 2007 Ravi Sankar 1. From address changed from hardcoded 'resume@netedgecomputing.com' to 'noreply@companyname.com' */
/* create email name for the new company */
select @v_email_id_name = 'noreply@' + lower(replace(@p_client_name, ' ','')) + '.com'
BEGIN TRANSACTION
---1. Replace all ClientSysId by the new sysId that is to be created.
---2. In tblsystem please change the client name and client's prefix
---3. In tblprincipal please modify the principal that is being inserted.
--- Make sure that principal has the same PREFIX as that added to tblSystem
---4. In tblRoleMapping please modify the principal that is being inserted.
--- Make sure that principal has the same PREFIX as that added to tblSystem
---5. In tblCompany please modify the name and prefix of company.
--- Make sure that name and prefix are the same as that added to tblSystem
---6. In tblBranch please modify the name and description of the branch.
---7. In tblStaff please modify the username, it should be same as that added in Principal.
/*
01 JUN 2007 : Ravi Sankar: Modified to add more tables to tblNext_ID
05 JUN 2007 : Sahil Gupta: Modified tblNext_id entry for tblcandidateevaluationround
05 JUN 2007 : Sahil Gupta: Added script for creating a default workflow with a test and an interview round.
*/
insert into tblcustom (sys_id,context_path,is_limited_version) values (@v_sys_id,'D:/jboss-4.0.1/server/rdproductconfig/deploy/rd.ear/rd.war','y');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblsystem (id,name,prefix,created_on) values (@v_sys_id, @p_client_name, @p_client_prefix,getdate());
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 03 Aug 2007 Ravi Sankar data in tblRoles changed
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Admin',@v_sys_id,'y','1','y','n','Admin')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Candidate',@v_sys_id,'n','0','n','n','Candidate')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Consultant',@v_sys_id,'y','1','y','n','Consultant')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('ContactPerson',@v_sys_id,'n','0','c','n','Contact Person Role')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('HrExecutive',@v_sys_id,'y','2','y','n','HR Executive Role')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('HrHead',@v_sys_id,'y','1','y','y','HR Head Role')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Interviewer',@v_sys_id,'n','5','y','n','Interviewer')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('JobPortal',@v_sys_id,'y','1','y','n','Job Portal')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Sales',@v_sys_id,'n','1','y','n','Sales')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Staff',@v_sys_id,'y','10','y','n','Staff')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('SuperUser',@v_sys_id,'y','1','y','n','Super User')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Netedge',@v_sys_id,'y','1','y','n','Netedge')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Testadmin',@v_sys_id,'y','20','y','n','Testadmin')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Careers',@v_sys_id,'y','20','y','n','Careers')
*/
/*
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Admin',@v_sys_id,'y','1','n','n','Admin','a')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Candidate',@v_sys_id,'n','0','n','n','Candidate','n')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Consultant',@v_sys_id,'y','1','n','n','Consultant','n')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('ContactPerson',@v_sys_id,'n','0','n','n','Contact Person Role','n')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('HrExecutive',@v_sys_id,'y','4','y','n','HR Executive Role','y')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('HrHead',@v_sys_id,'y','2','y','y','HR Head Role','y')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Interviewer',@v_sys_id,'n','5','n','n','Interviewer','c')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Sales',@v_sys_id,'n','1','n','n','Sales','y')
*/
-- 15 Jan 2008 Ravi Sankar 1. when new sys_id is being created staff role is_limited should go as 'n'
-- INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
-- VALUES('Staff',@v_sys_id,'y','10','n','n','Staff','y')
/*
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Staff',@v_sys_id,'n','10','n','n','Staff','y')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('SuperUser',@v_sys_id,'y','1','n','n','Super User','s')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Netedge',@v_sys_id,'y','1','n','n','Netedge','s')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Testadmin',@v_sys_id,'y','20','n','n','Testadmin','y')
*/
-- 30 NOV 2007 Ravi Sankar New role 'Accounts' added to tblRoles
/*
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Accounts',@v_sys_id,'y','2','n','n','Handles Billing','y')
--26 feb 2008 Kusum Bhardwaj New role 'Accounts' added to tblRoles
INSERT tblroles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Coordinator',@v_sys_id,'y','1','y','n','Screening Coordinator','y')
*/
INSERT tblroles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
select ROLE,@v_sys_id,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal from tblroles where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblprincipal (principal,sys_id,password,first_name) values (@p_client_prefix + '.admin', @v_sys_id, 'password', 'admin');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix + '.admin', 'Admin', @v_sys_id);
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblcompany (sys_id,id,name,prefix,is_internal) values (@v_sys_id, 1, @p_client_name, @p_client_prefix,'Y');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT into tblcurrency(sys_id,id,name,symbol,is_deleted) values(@v_sys_id,'1','Rupees','Rs.','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT into tblcurrency(sys_id,id,name,symbol,is_deleted) values(@v_sys_id,'2','Dollar','$','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT into tblcurrency(sys_id,id,name,symbol,is_deleted) values(@v_sys_id,'3','Pound','GBP','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblregion (sys_id,id,name,description) values (@v_sys_id,1,'NR','North Region')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tbllocation(sys_id,id,state_id,name) values(@v_sys_id,1,null, @p_location_name)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 24 JUL 2007 Ravi Sankar Location/Region 'Others' added */
insert into tblregion (sys_id,id,name,description) values (@v_sys_id,2,'Others','Some other Region')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
--insert into tblState(sys_id,id,region_id,name,description) values(@v_sys_id,2,2, 'Others', 'Some other State')
insert into tbllocation(sys_id,id,state_id,name,description) values(@v_sys_id,2,null, 'Others', 'Some other Location')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblbranch (sys_id,id,name,description,company_id,location_id,currency_id) values (@v_sys_id,1, @p_branch_name, @p_branch_name,1,1,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 16 Jan 2008 Ravi Sankar 'Admin' user in tblstaff to be allowed for all companies/functions (ieflag4 and flag5 to be made 'Y')
-- insert into tblstaff (sys_id,id,company_id,branch_id,first_name,principal_id,username) values (@v_sys_id,1,1,1,'Administrator',[dbo].fnGetMaxID(), @p_client_prefix + '.admin')
insert into tblstaff (sys_id,id,company_id,branch_id,first_name,principal_id,username,flag4, flag5) values (@v_sys_id,1,1,1,'Administrator',[dbo].fnGetMaxID(), @p_client_prefix + '.admin', 'Y', 'Y')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblprincipal (principal,sys_id,password,first_name) values (@p_client_prefix +'.SuperUser',@v_sys_id,'password','SuperUser')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix +'.SuperUser','SuperUser',@v_sys_id)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblstaff (sys_id,id,company_id,branch_id,first_name,principal_id,username,is_super_user) values (@v_sys_id,2,1,1,'SuperUser',[dbo].fnGetMaxID(),@p_client_prefix +'.SuperUser','Y')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
--insert corresponding to TblJobtype
--08-aug-2008 Kusum Bhardwaj 0 sys_id is maintained for trial version so instead of inserting row by row we are directly inserting from 0 sys_id
INSERT tbljobtype(sys_id,id,name,description,created_by,created_on,modified_by,modified_on,is_deleted)
select @v_sys_id,id,name,description,created_by,created_on,modified_by,modified_on,is_deleted from tbljobtype where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
---In tblcountry id column should be the incremented id
-- 09-may-2008 Kusum Bhardwaj the commented inserts of TblCountry are uncommented
--08-aug-2008 Kusum Bhardwaj 0 sys_id is maintained for trial version so instead of inserting row by row we are directly inserting from 0 sys_id
INSERT tblcountry(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on)
select @v_sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on from tblcountry where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/*
insert into tblcountry(sys_id,id,name,description) values (@v_sys_id,1,'India','India')
insert into tblcountry(sys_id,id,name,description) values (@v_sys_id,2,'USA','USA')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblcountry(sys_id,id,name,description) values (@v_sys_id,3,'China','China')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblcountry(sys_id,id,name,description) values (@v_sys_id,4,'UK','UK')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
*/
-- 09-may-2008 Kusum Bhardwaj inserts for TblState are added
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'1',null,'Andaman & Nicobar','Andaman & Nicobar','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'2',null,'Andhra Pradesh','Andhra Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'3',null,'Arunachal Pradesh','Arunachal Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'5',null,'Bihar','Bihar','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'6',null,'Chandigarh','Chandigarh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'7',null,'Chhattisgarh','Chhattisgarh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'8',null,'Dadra & Nagar Haveli','Dadra & Nagar Haveli','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'9',null,'Lakshadweep','Lakshadweep','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'10',null,'Madhya Pradesh','Madhya Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'11',null,'Maharashtra','Maharashtra','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'12',null,'Manipur','Manipur','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'13',null,'Meghalaya','Meghalaya','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'14',null,'Mizoram','Mizoram','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'15',null,'Nagaland','Nagaland','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'16',null,'Orissa','Orissa','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'17',null,'Daman & Diu','Daman & Diu','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'18',null,'Delhi','Delhi','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'19',null,'Goa','Goa','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'20',null,'Gujarat','Gujarat','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'21',null,'Haryana','Haryana','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'22',null,'Himachal Pradesh','Himachal Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'23',null,'Jammu & Kashmir','Jammu & Kashmir','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'24',null,'Jharkhand','Jharkhand','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'25',null,'Karnataka','Karnataka','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'26',null,'Kerala','Kerala','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'27',null,'Pondicherry','Pondicherry','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'28',null,'Punjab','Punjab','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'29',null,'Rajasthan','Rajasthan','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'30',null,'Sikkim','Sikkim','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'31',null,'Tamil Nadu','Tamil Nadu','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'32',null,'Tripura','Tripura','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'33',null,'Uttar Pradesh','Uttar Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'34',null,'Uttarakhand','Previous name Uttaranchal','N','0',null,'1',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'35',null,'West Bengal','West Bengal','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'36',null,'Others','Some Other State','N','0',null,'0',null,null)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblLanguage(sys_id,id,name,description) values (@v_sys_id,1,'Hindi','Hindi')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblLanguage(sys_id,id,name,description) values (@v_sys_id,2,'English','English')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
--Generate script for various keywords
insert into tblKeyword(id,sys_id,name,description) values (1,@v_sys_id,'IIT','Indian Institute of Technology')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblKeyword(id,sys_id,name,description) values (2,@v_sys_id,'IIM','Indian Institute of Management')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 07 Sep 2007 Ravi sankar 1. AIIMS keyword and synonym related records to be deleted.
-- insert into tblKeyword(id,sys_id,name,description) values (3,@v_sys_id,'AIIMS','All India Institute of Madical Sciences')
--Generate script for various Synonyms
insert into tblSynonyms(id,sys_id,keyword_id,name,description) values (1,@v_sys_id,1,'I.I.T.','Indian Institute of Technology')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblSynonyms(id,sys_id,keyword_id,name,description) values (2,@v_sys_id,2,'I.I.M.','Indian Institute of Management')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 07 Sep 2007 Ravi sankar 1. AIIMS keyword and synonym related records to be deleted.
-- insert into tblSynonyms(id,sys_id,keyword_id,name,description) values (3,@v_sys_id,3,'A.I.I.M.S.','All India Institute of Madical Sciences')
set IDENTITY_INSERT tblEscalation ON
insert into tblEscalation(sys_id,id,SLA,alert_before_days) values (@v_sys_id,1,7,3)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
set IDENTITY_INSERT tblEscalation OFF
insert into tblFunction(sys_id,id,name) values (@v_sys_id,1,'Marketing')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblFunction(sys_id,id,name) values (@v_sys_id,2,'Technical Support')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblFunction(sys_id,id,name) values (@v_sys_id,3,'Accounts')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblFunction(sys_id,id,name) values (@v_sys_id,4,'Administration')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- insert into tblFunction(sys_id,id,name) values (@v_sys_id,5,'Marketing/Solutions')
-- 20 Aug 2007 Ravi sankar 1. Standard functions to be added to tblFunction
INSERT tblFunction(sys_id,id,name) VALUES(@v_sys_id,'5','Sales')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblFunction(sys_id,id,name) VALUES(@v_sys_id,'6','IT software')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblFunction(sys_id,id,name) VALUES(@v_sys_id,'7','Human Resources')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 06 Sep 2007 Ravi sankar 2. Standard functions are modified in tblFunction
-- Uptill seven functions only to be sent
/*
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'8','Networking','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'9','Web Designing','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'10','Customer Support','','N','0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'11','project mgt','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'12','architect','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'13','globus release','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'14','UNIX','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'15','BPO','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'16','testing','','N', '0',convert(datetime,0),'0',convert(datetime,0))
*/
-- 06 Sep 2007 Ravi sankar 3. Standard positions are modified in tblPosition
-- Only Four positions
-- 20 Aug 2007 Ravi sankar 2. Standard positions to be added to tblPosition
/*
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'1','Consultant','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'2','Senior Consultant','1','6','','N','1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'3','Project Manager','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'4','Developer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'5','Tester','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'6','Tech Support and Maintenance','1','6','','N','1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'7','Search Engine optimization','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'8','Technical Writer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'9','sales manager','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'10','Web Designer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'11','Architect','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'12','Recruiter','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'13','Operations','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'14','Senior Tester','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'15','Test Lead','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'16','Trainer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'17','Sales Executive','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'18','Telecaller','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'19','Software Engineer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'20','Sr Sales Executive','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'21','Sr Developer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'22','OSS Enginer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'23','Tech project manager','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'24','Technical project manager','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'25','Sn Software Engineer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'26','Project Lead','1','6','Project Lead','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'27','Globus Developer ','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'28','Junior level','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
*/
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted) VALUES(@v_sys_id,'1','Junior Management','1','6','','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted) VALUES(@v_sys_id,'2','Middle Management','1','6','','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted) VALUES(@v_sys_id,'3','Senior management','1','6','','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted) VALUES(@v_sys_id,'4','Executive','1','6','','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
--Generate script for various statuses
INSERT tblstatus(sys_id,id,name,description,type,sequence,screen_acronym,created_by,created_on,modified_by,modified_on,display_name,is_deleted,is_mandatory,Process_manual,UserDefinedStage_displayYN,DisplayName_externalInterface)
select @v_sys_id,id,name,description,type,sequence,screen_acronym,created_by,created_on,modified_by,modified_on,display_name,is_deleted,is_mandatory,Process_manual,UserDefinedStage_displayYN,DisplayName_externalInterface from tblstatus where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 6-05-2008 Kusum Bhardwaj Channeltype 'News paper ADs' is changed to 'Career site' added
insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,1,'Career Site');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,2,'Staff');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,3,'Placement Consultants');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,4,'Job Portal');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 24 Jan 2008 Ravi Sankar Channeltype 'candidate' added
insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,5,'Candidate');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- Add test types
insert into tbltesttype (sys_id,id,name) values (@v_sys_id,1,'TEST');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tbltesttype (sys_id,id,name) values (@v_sys_id,2,'INTERVIEW');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- Add test types in tbletesttypeRd
insert into tbltesttyperd (sys_id,test_type_id,is_screening_candidate_assessment_YN,screening_type) values (@v_sys_id,1,'N','TEST');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tbltesttyperd (sys_id,test_type_id,is_screening_candidate_assessment_YN,screening_type) values (@v_sys_id,2,'N','INT');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
--Add Email Templates
/* 03 Jan 2008 Ravi Sankar In tblEmailtemplate, Acknowledgement spelling corrected for ids 2,3,5 for shortname/subject */
/* 12 NOV 2007 Ravi Sankar 1. From address changed from hardcoded 'resume@netedgecomputing.com' to 'noreply@companyname.com'
2. New Email template added for CandidateRegistration mail. */
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted,created_by,created_on,modified_by,modified_on)
select @v_sys_id,id,ShortName,Description,FileName,@v_email_id_name ,subject,body,toId,cc,bcc,is_deleted,created_by,created_on,modified_by,modified_on from tblemailtemplate where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'1','Forgot Password','When the user forgets his password','forgotPasswordTemplate.html',@v_email_id_name,'Login details for RD','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'2','Resume Upload Acknowledgement','When the candidate uploads his resume','resumeUploadAck.html',@v_email_id_name,'Resume Uploaded Acknowledgement','','','','','Y')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'3','Resume Upload Staff Candidate','When a staff member uploads a friends resume,this mail goes to the candidate.','resumeUploadStaffCandidate.html',@v_email_id_name,'Resume Uploaded Acknowledgement','','','','','Y')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'4','Resume Upload Staff ','When a staff member uploads a friends resume,this mail goes to the staff member. ','resumeUploadStaff.html',@v_email_id_name,'Thank you for your reference','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'5','Resume Upload Placement','When a placement consultant uploads a resume,this mail goes to the consultant.','resumeUploadPlacement.html',@v_email_id_name,'Resume Uploaded Acknowledgement','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'6','Resume Upload Bulk Admin','When resumes are uploaded by bulk mail utility,this mail goes to admin on preconfigured email id.','resumeUploadBulkAdmin.html',@v_email_id_name,'Details of Resumes uploaded by Bulk Upload','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'7','Resume Upload Bulk','When resumes are uploaded by bulk mail utility,this mail goes to uploader with resumes detail.','resumeUploadBulk.html',@v_email_id_name,'Details of Resumes uploaded by Bulk Upload ','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'8','Invitation Mail','This mail is sent to selected candidates inviting them to upload their latest resume. The mail also has UserId and Password.','invitationMail.html',@v_email_id_name,'Invitation mail for your latest Resume','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'9','Invitation Mail Against Requisition','This mail is sent to selected candidates inviting them to upload their latest resumes against a specific PR.','invitationMailAgainstPR.html',@v_email_id_name,'Invitation mail for your latest Resume against a PR','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'10','Resume Submission Alert ','This mail goes to candidate, when his resume is sent to a client by recruiter.','resumeSubmissionAlert.html',@v_email_id_name,'Alert for your Resume Submission','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'11','Availability Check','This mail goes to candidate to check his availability for a PR.','availabilityCheck.html',@v_email_id_name,'Your availability for a PR','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'12','Test Schedule','This mail goes to candidate giving him his link for appearing in the test.','testSchedule.html',@v_email_id_name,'Test Schedule','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'13','Interview Schedule Candidate','This mail goes to candidate informing him about interview schedule.','interviewScheduleCandidate.html',@v_email_id_name,'Interview Schedule','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'14','Interview Schedule Interviewer','This mail goes to interviewer informing him about interview schedule.','interviewScheduleInterviewer.html',@v_email_id_name,'Interview Schedule','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'15','Selection Information Candidate','This mail is sent to candidate informing him about his selection.','selectionInfoCandidate.html',@v_email_id_name,'You have been selected','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'16','Rejection Information Candidate','This mail is sent to candidate informing him about his rejection.','rejectionInfoCandidate.html',@v_email_id_name,'You have been Rejected','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'17','Selection Rejection Channel','This mail is sent to channel(Staff/Placement Consultant) informing him about the candidate status.','selectionRejectionChannel.html',@v_email_id_name,'Status of Candidate','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'18','Position Requisition Alert','This mail is sent to subscribers informing that a new position has been posted.','positionRequisitionAlert.html',@v_email_id_name,'Alert for new Position Requisition','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'19','Position Requition Skill Alert','This mail is sent to subscribers informing that a new position on their skill has been posted.','PRSkillAlert.html',@v_email_id_name,'Alert for new Skill','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'20','Resume Submission Alert to Recruiter','This mail goes to recruiter working on a requirement when a resume is posted against that requisition.','resumeSubmissionAlertRecruiter.html',@v_email_id_name,'Alert for resume Submission','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'21','Bill Alert to Candidate','This mail is sent to candidate has joined a bill becomes due.','billingAlert.html',@v_email_id_name,'Billing Alert','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'22','SLA Alert ','This mail is sent to the resource executive that the position has not been closed and needs to be closed in next n hours/days configurable.','SLAAlert.html',@v_email_id_name,'SLA Alert','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'23','Alert For No. of resumes sent to client','Alert to preconfigured emails giving number of resumes sent to client against requisition.','noOfResumeSenttoClient.html',@v_email_id_name,'Alert for No. of resume sent to client','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'24','Resumes Sent to Client','This mail is sent to client with candidates resume as attachments.','resumesSenttoClient.html',@v_email_id_name,'Candidate Resumes','','','','','N')
*/
/* 06 Sep 2007 Ravi sankar 1. New Email template added for offerletter */
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'25','OfferLetter','This mail is sent to candidate for whom offer letter is generated.', 'OfferLettertoCandidate.html',@v_email_id_name,'Offer letter','','','','','N')
*/
/* 23 OCT 2007 Ravi Sankar Delete invitation mail template tblMailtemplate (id 26). */
/* 01 Oct 2007 Ravi sankar 1. New Email template added for Invitation mail */
/* INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'26','Invitation Mail','This mail is sent to candidate for invitation.', 'invitationMail.html',@v_email_id_name,'Invitation Mail','','','','','N')
*/
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'26','CandidateRegistration','When Candidate self Registered.', 'CandidateRegistrationTemplate.html',@v_email_id_name,'Please Find Your Login Information','','','','','N')
*/
/* 30 NOV 2007 Ravi Sankar Email template for interview Schedule for Interviewer added. (id 27) */
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'27','Automated Interview Schedule Interviewer','This automated mail goes to interviewer informing him about interview schedule.', 'automatedInterviewScheduleInterviewer.html',@v_email_id_name,'Interview Schedule','','','','','N')
*/
/* 06 May 2008 Kusum Bhardwaj (id 28 to 35) */
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'28','Candidate Birthday Template','Everyday Greeting','birthdayGreetingMail.html','@v_email_id_name','Happy Birthday','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'29','Test ReSchedule','This mail goes to candidate informing him about the rescheduled test.','testReSchedule.html','@v_email_id_name','Test ReScheduled','','','','','n')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'30','Interview ReSchedule Candidate','This mail goes to candidate informing him about the rescheduled interview.','interviewReScheduleCandidate.html','@v_email_id_name','Interview ReScheduled','','','','','n')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,is_deleted)
VALUES(@v_sys_id,'31','ResumeUploadChannelAgainstPR','This mail goes to channel who has referred a candidate.','resumeUploadChannelAgainstPR.html','@v_email_id_name','Resume Referral Acknowledgement','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,is_deleted)
VALUES(@v_sys_id,'32','ReferCandidateAcknowledgement','This mail goes to candidate who has been referred by a channel.','ReferCandidateAcknowledgement.html','@v_email_id_name','Resume Referral Acknowledgement','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,is_deleted)
VALUES(@v_sys_id,'33','Scheduler Job Failed Mail','This mail goes to Admin when Scheduler Job fails.','schedulerJobFailedMailAdmin.html','@v_email_id_name','Scheduler Job Failed','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,is_deleted)
VALUES(@v_sys_id,'34','Job Alert','Send mail to subscriber about matched fresh jobs.','JobAlert.html','@v_email_id_name','Job Alert - Fresh Jobs','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,cc,bcc,is_deleted)
VALUES(@v_sys_id,'35','Registration Verification','When a Candidate self Registered.','CandidateVerificationTemplate.html','@v_email_id_name','Candidate Registration Verification','','','n')
*/
-- 05-jun-2005 Kusum Bhardwaj TblEmailtemplate new entry
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,cc,bcc,is_deleted)
VALUES(@v_sys_id,'36','Request to reschedule interview','This mail goes to Interviewer to reschedule Interview.','RequestToRescheduleInterview.html','@v_email_id_name','Request to reschedule Interview','','','n')
*/
/*
21 JUN 2007 Ravi Sankar tblroledashboard entries modified such that admin will have no dashboard and
Hrhead to have default dashboard
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','1','5')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','2','6')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','3','3')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','4','4')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','5','1')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','6','2')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','1','5')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','2','6')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','3','3')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','4','4')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','5','1')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','6','2')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','1','1')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','2','6')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','3','4')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','4','3')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','5','5')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','6','2')
*/
/*
26 JUL 2007 Ravi Sankar data in tblRoleDashBoard changed
*/
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo)
select @v_sys_id,role,dashboard_id,sequence,dateFrom,dateTo from tblroledashboard where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 05-May-2008 Kusum Bhardwaj New role added in Tblroledashboard for Superuser and HrHead
/*
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'ContactPerson','5','1','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'ContactPerson','6','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','1','6','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','2','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','3','5','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','4','4','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','5','1','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','6','3','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','8','8','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','1','6','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','2','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','3','5','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','4','4','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','5','1','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','6','3','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','7','7','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','8','8','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'Interviewer','5','1','0','0')
-- 17 Jan 2008 Ravi Sankar Pending feedback dashboard added to Interviewer
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'Interviewer','3','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','1','6','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','2','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','3','5','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','4','4','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','5','1','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','6','3','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','7','7','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','8','8','0','0')
*/
SELECT @v_count=COUNT(*) FROM tblbill WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblBill',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblbillreceipt WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblbillreceipt',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblbranch WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblBranch',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblCandidate WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidate',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblcandidateEvaluationRound WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidateEvaluationRound',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblCandidateRequisition WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidateRequisition',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblCandidateRequisitionHistory WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidateRequisitionHistory',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblCandidateRequisitionNotes WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidateRequisitionNotes',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/*
SELECT @v_count=COUNT(*) FROM tblChannel WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblChannel',@v_sys_id,@v_count)
*/
-- 24 Jan 2008 Ravi Sankar Channeltype 'candidate' added
SELECT @v_count=COUNT(*) FROM tblChannelType WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblChannelType',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblCompany WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCompany',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblCompetency WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCompetency',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblContactPerson WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblContactPerson',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblCostHead WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCostHead',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblCurrency WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCurrency',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblDesignation WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblDesignation',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblEvaluationWorkflow WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblEvaluationWorkflow',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 06 Sep 2007 Ravi sankar 2. Standard functions are modified in tblFunction
-- Only seven Functions
SELECT @v_count=COUNT(*) FROM tblFunction WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblFunction',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 07 Sep 2007 Ravi sankar 1. AIIMS keyword and synonym related records to be deleted.
SELECT @v_count=COUNT(*) FROM tblKeyword WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblKeyword',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblLocation WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblLocation',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblOfferLetterDetails WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblOfferLetterDetails',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 06 Sep 2007 Ravi sankar 3. Standard positions are modified in tblPosition
-- Only Four positions
SELECT @v_count=COUNT(*) FROM tblPosition WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPosition',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblPositionRequisition WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPositionRequisition',@v_sys_id,isnull(@v_count,@v_count))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblPositionRequisitionExpenses WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPositionRequisitionExpenses',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblQualification WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblQualification',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblRegion WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblRegion',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblSearchAgent WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblSearchAgent',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblSkill WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblSkill',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblStaff WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblStaff',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 25 OCT 2007 Ravi Sankar 'tblState' ebtry in tblNext_id.
SELECT @v_count=COUNT(*) FROM tblState WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblState',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblStatus WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblStatus',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblSubCategoryExp WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblSubCategoryExp',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 07 Sep 2007 Ravi sankar 1. AIIMS keyword and synonym related records to be deleted.
SELECT @v_count=COUNT(*) FROM tblSynonyms WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblSynonyms',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblTest WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblTest',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblTesttype WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblTestType',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 30 NOV 2007 Ravi Sankar Email template for interview Schedule for Interviewer added. (id 27) */
/* 12 NOV 2007 Ravi Sankar 1. From address changed from hardcoded 'resume@netedgecomputing.com' to 'noreply@companyname.com'
2. New Email template added for CandidateRegistration mail. */
/* 23 OCT 2007 Ravi Sankar Delete invitation mail template tblMailtemplate (id 26). */
/* 01 Oct 2007 Ravi sankar 1. New Email template added for Invitation mail */
/* 06 Sep 2007 Ravi sankar 1. New Email template added for offerletter */
SELECT @v_count=COUNT(*) FROM tblEmailtemplate WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblemailtemplate',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 01 JUN 2007 : Ravi Sankar: Modified to add more tables to tblNext_ID */
SELECT @v_count=COUNT(*) FROM tblPotentialRecruit WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPotentialRecruit',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblPotentialClients WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPotentialClients',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblDocument WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblDocument',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblDocumentHistory WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblDocumentHistory',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblCategory WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCategory',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 13 NOV 2007 Ravi Sankar 1. 'tblUnit' row is added to tblNext_id */
SELECT @v_count=COUNT(*) FROM tblUnit WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblUnit',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 15 NOV 2007 Ravi Sankar 1. 'tblCountry' row is added to tblNext_id */
SELECT @v_count=COUNT(*) FROM tblCountry WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCountry',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 10 Jan 2008 Ravi Sankar In Next_id, rows added for 'tblIndustry', 'tblJobType', 'tblVisaStatus' */
SELECT @v_count=COUNT(*) FROM tblIndustry WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblIndustry',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tbljobtype WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblJobType',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
SELECT @v_count=COUNT(*) FROM tblVisaStatus WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblVisaStatus',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 09-may-2008 Kusum Bhardwaj Insert for TblVerifyRegistration
SELECT @v_count=COUNT(*) FROM tblVerifyRegistration WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblVerifyRegistration',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 09-may-2008 Kusum Bhardwaj Insert for TblJobAlert
SELECT @v_count=COUNT(*) FROM tblJobAlert WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblJobAlert',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 10 DEC 2007 Ravi sankar Added paymentmode entries and 'tblreceipt','tblpaymentmode', in tblnext_id */
Insert into tblPaymentMode(sys_id, id, name, description) values (@v_sys_id, 1, 'Cash', 'Cash')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
Insert into tblPaymentMode(sys_id, id, name, description) values (@v_sys_id, 2, 'Credit Card', 'Credit Card')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
Insert into tblPaymentMode(sys_id, id, name, description) values (@v_sys_id, 3, 'Cheque', 'Cheque')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
Insert into tblPaymentMode(sys_id, id, name, description) values (@v_sys_id, 4, 'Draft', 'Draft')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
Insert into tblNext_id(table_name, sys_id, id) values ('tblPaymentMode', @v_sys_id,5)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
Insert into tblNext_id(table_name, sys_id, id) values ('tblReceipt', @v_sys_id,0)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 24 DEC 2007 Ravi sankar Added 'tblCandidateDocument' in tblnext_id */
Insert into tblNext_id(table_name, sys_id, id) values ('tblCandidateDocument', @v_sys_id,0)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblEvaluationWorkflow (sys_id, id, name, description, is_deleted)
values (@v_sys_id,1, 'Default', 'Default', 'N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblCompanyFunctionPositionEvaluation(sys_id, evaluation_workflow_id, is_deleted)
values(@v_sys_id,1,'N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 20 Aug 2007 Ravi sankar 3. Default test table to contain only one test 'Face to face Interview round'
-- 20 Aug 2007 Ravi sankar 4. Default evaluation work flow to contain only one test 'Face to face Interview round'
-- insert into tbltest(sys_id, id, name, description, lockedYN, password, duration_minutes, is_deleted)
-- values (@v_sys_id, 1, 'Test' , 'Test', 'N', '', 30.0, 'N')
-- insert into tbltest(sys_id, id, name, description, lockedYN, password, duration_minutes, is_deleted)
-- values (@v_sys_id, 2, 'Interview' , 'Interview', 'N', '', 30.0, 'N')
INSERT tblTest(sys_id,id,Name,Description,LockedYN,password,duration_minutes,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'1','Face_to_face Interview round','',' ','','30','N','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tbltesttypemapping (sys_id, test_type_id, test_id)
values(@v_sys_id, 2,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- insert into tbltesttypemapping (sys_id, test_type_id, test_id)
-- values(@v_sys_id, 2,2)
insert into tblCFPEvaluationRound (sys_id, cfpe_id, screening_round_order, test_type_id, test_id, before_sending_resumes_to_client_YN, is_mandatory_to_clear_YN, pass_percentage, duration_minutes)
values (@v_sys_id, [dbo].fnGetMaxIDEvaluation(), 1, 2,1,'N', 'N',30.0, 30.0)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- insert into tblCFPEvaluationRound (sys_id, cfpe_id, screening_round_order, test_type_id, test_id, before_sending_resumes_to_client_YN, is_mandatory_to_clear_YN, pass_percentage, duration_minutes)
-- values (@v_sys_id, [dbo].fnGetMaxIDEvaluation(), 2, 2,2,'N', 'N',30.0, 30.0)
insert into tblprincipal (principal,sys_id,password,first_name,channeltype_id) values (@p_client_prefix + '.Naukri',@v_sys_id,'password','Naukri',4);
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix + '.Naukri','JobPortal',@v_sys_id); */
insert into tblprincipal (principal,sys_id,password,first_name,channeltype_id) values (@p_client_prefix + '.TimesJob',@v_sys_id,'password','Timesjob',4);
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix + '.TimesJob','JobPortal',@v_sys_id); */
insert into tblprincipal (principal,sys_id,password,first_name,channeltype_id) values (@p_client_prefix + '.Monster',@v_sys_id,'password','Monster',4);
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix + '.Monster','JobPortal',@v_sys_id); */
/*
15 JUN 2007 Ravi Sankar tblStatus record where name is 'offerletter' Display name changed
from 'Offer Letter Generated' to 'Offer Letter'
*/
update tblStatus set display_name = 'Offer Letter' where sys_id = @v_sys_id and name = 'offerletter'
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/*
11 JUL 2007 Ravi Sankar tblWorkflow row in tblNext_Id changed to 1 from 0 since default is already added
*/
UPDATE tblnext_ID set id = 1 where sys_id = @v_sys_id and table_name = 'tblEvaluationWorkflow' and id = 0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 20 Aug 2007 Ravi sankar 5. Default tblQuestionGroup to contain one question group
-- 20 Aug 2007 Ravi sankar 6. Default Question bank to contain default questions
-- 20 Aug 2007 Ravi sankar 7. For each question populate tables tblGroupQuestionmap, tblQuestionCorrectAnswer,
-- 20 Aug 2007 Ravi sankar tblAnswerType, tbltestQuestion
declare @v_question_group_id int,
@v_question_id int
INSERT tblQuestionGroup(sys_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Face_to_face','','n','1',convert(datetime,0),'0',convert(datetime,0))
select @v_question_group_id = @@identity
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Personality','radiobutton','0','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity
INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'1','Very Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'2','Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'3','Satisfactory','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'4','Poor','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Confidence','radiobutton','0','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity
INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'1','Very Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'2',' Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'3',' Satisfactory','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'4',' Poor','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Communication Skills','radiobutton','0','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity
INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'1','Very Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'2',' Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'3',' Satisfactory','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'4',' Poor','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Job Knowledge','radiobutton','0','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity
INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'1','Very Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'2',' Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'3',' Satisfactory','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'4',' Poor','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Recommendations','textbox','250','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity
INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Technical Knowledge Skills','textbox','250','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity
INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 04-aug-2008 Kusum Bhardwaj 0 sysid is maintained in all the tables to maintain data for trail version
Now the data would be directly uploaded from the tables for the new trail version*/
INSERT tblswitch(sys_id,id,switch_key,attribute)
select @v_sys_id,id,switch_key,attribute from tblswitch where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
COMMIT TRANSACTION
SELECT @v_sys_id SYSID
END
GO
The problem is while inserting when I am making anonymous block of it then it is inserting in all the tables but when I am calling the block as an SP. then it is skipping one table.
I am not getting what exactly is teh problem.
Please help if anybody knows
Thanks
Kusum Bhardwaj
|
|
|
|
|
seriously no one is going to help you. you cannot post such a big statement
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Can you please tell me how long SSAS, SSRS, SSIS will take us to get mastered?
|
|
|
|
|
JAnthonyRaj wrote: how long SSAS, SSRS, SSIS will take us to get mastered?
Depends on how fast of a learner you are.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Thank you J Anthony Raj, I needed a good chuckle this morning. Yuo really do take the cake for dumb questions. Based on this I would say for you oh, about 14 years.
Please note there is no joke icon on this post!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That's real gem isn't it 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I was tempted to reply with the question "how long is a piece of string" but I'm absolutely sure he would understand the reference.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: "how long is a piece of string"
[Obi-wan Kenobi voice over] Depends on the point of view 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
does SQL server 2005 include query analyzer? I installed it and couldn't find the query analyzer. anyone knows where I can download SQL query analyzer? many thanks..
|
|
|
|
|
It's still there. Just not called Query Analyzer anymore. Are you using SQL Management Studio?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
yes, i am using that, so as you said, what is the query analyzer called in SQL Server management studio? thanks....
|
|
|
|
|
It should be in the query editor toolbar under Display Estimated Execution Plan, IIRC...
You might want to click on the button that says Include In Execution Plan, as well. This will give you query stats along side with any query results.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
|
You're welcome.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hey,
So I am trying to get the Highest MenuOrder Value there is increment that by one and then use that value to insert a new object in with the next highest menu order
My attempt so far is as so:
Declare @MenuSeed as int
Select @MenuSeed = Top(1) MenuOrder
From Pages
Order By MenuOrder DESC
And of course that doesn't work, but I am kinda lost on what to do here, can anyone shed some light?
Thanks Alot
-Seth
|
|
|
|
|
Declare @MenuSeed as int<br />
<br />
Set @MenuSeed = (select max(MenuOrder) From PagesOrder)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
