--Employee
Declare @FName Varchar(25);
Declare @LName Varchar(25);
Declare @Address Varchar(100);
Declare @LookupCode varchar(25);
Declare @LookUpCodeid int;
Declare @EmployeeEntitty int;
Declare @UniqContactId int; 
--Agency
Declare @AgencyCode Varchar(25);
Declare @AgencyName Varchar(25);
Declare @AgencyID as int; 


--Change Variables here
--Employee Variables Start
set @FName = 'Rob'
Set @LName = 'Smith'
set @Address ='55 Fake St'
--Employee Variables End
--Agency Variables Start

--Agency Variables End
Set @AgencyCode = 'CI2'
Set @AgencyCode = 'Charlie Insurance'

-- Find Lookupcode
Set @LookUpCodeid = 1 
Set @LookupCode = Upper(substring(@lname ,1,3 ) + substring (@Fname,1,2) + Convert(Varchar(3),@LookUpCodeid ))
While Exists(Select LookupCode  From Entity Where LookupCode = @LookUpCode)
begin
set @LookUpCodeid = @LookUpCodeid + 1
Set @LookupCode = Upper(substring(@lname ,1,3 ) + substring (@Fname,1,2) + Convert(Varchar(3),@LookUpCodeid ))
end

--Create Employee
INSERT INTO entity (entkey, lookupcode, nameof) VALUES ('EMPL', @LookupCode, @FName + ' ' + @LName)

set @EmployeeEntitty = @@identity

INSERT INTO dbo.contactname (uniqentity, uniqcontactaddressmain, lkprefix, firstname, middlename, lastname, lksuffix, categorycode, 
descriptionof, comments, insertedbycode, flags, lklanguage) 
Values (@EmployeeEntitty, -1, '', @FName , '', @LName, '', 'C', '', '', 'ENTERPRISEADMIN', 64, '')

Set @UniqContactId = (Select uniqcontactname From dbo.ContactName Where uniqentity = @EmployeeEntitty )

INSERT INTO employee (uniqentity, lookupcode, nameof, uniqcontactnameaccount, uniqcontactaddressaccount, uniqcontactnumberresidenceaccount, uniqcontactnumbermobileaccount, batchpaymentmethodcode, uniqcontactnumberfaxaccount, uniqcontactnumberemailaccount, uniqglaccountpay, uniqglaccountdef, uniqglaccountexp, uniqglaccountabill, uniqglaccountdbill, insertedbycode, roleflags, flags, uniqglaccountpaywriteoff) Values (@EmployeeEntitty, @LookupCode, @FName + ' ' + @LName, @UniqContactId , -1, -1, -1, '', -1, -1, -1, -1, -1, -1, -1, 'ENTERPRISEADMIN', 0 & 2145386497, 0 | 154 & 2147479707, -1)

INSERT quicklinkemployeenodeorder SELECT @EmployeeEntitty, q.uniqquicklinknode, (SELECT COUNT(*) FROM quicklinknode qn WHERE qn.nodenumber <= q.nodenumber) FROM quicklinknode q

INSERT quicklinkemployeeservicingrole SELECT @EmployeeEntitty, uniqcdservicingrole FROM cdservicingrole WHERE uniqcdservicingrole > 0 

INSERT INTO distributionemailaccount(uniqentity, uniqagency, displayname, replyaddress, insertedbycode) SELECT @EmployeeEntitty, sc.uniqagency, e.nameof, cn.emailweb, 'ENTERPRISEADMIN' FROM employee e CROSS JOIN agency a CROSS JOIN contactnumber cn CROSS JOIN structurecombination sc WHERE a.uniqagency = sc.uniqagency AND e.uniqcontactnumberemailaccount = cn.uniqcontactnumber AND e.uniqentity = @EmployeeEntitty AND e.flags & 128 = 128 GROUP BY e.uniqentity, sc.uniqagency, e.nameof, cn.emailweb 

UPDATE dea SET dea.flags = dea.flags | 2 FROM distributionemailaccount dea INNER JOIN(SELECT d.uniqentity, d.uniqagency 					 FROM distributionemailaccount d            WHERE d.uniqentity = @EmployeeEntitty 					 AND d.uniqagency = (SELECT MIN(d2.uniqagency) 															 FROM distributionemailaccount d2 															 WHERE d.uniqentity = d2.uniqentity) 					 GROUP BY d.uniqentity, d.uniqagency) AS dt ON dea.uniqentity = dt.uniqentity AND dea.uniqagency = dt.uniqagency WHERE dea.uniqentity = @EmployeeEntitty

--Create Agency
INSERT INTO agency (agencycode, nameof, licensenumber, uniqregion, initialmonth, firstfiscalmonth, address1main, address2main, address3main, citymain, cdstatecodemain, postalcodemain, countymain, cdcountrycodemain, address1mailing, address2mailing, address3mailing, citymailing, cdstatecodemailing, postalcodemailing, countymailing, cdcountrycodemailing, phonenumber, phoneextension, phonedescription, faxnumber, faxextension, faxdescription, email, website, fein, insertedbycode, phonecountrycode, faxphonecountrycode, regionprovincemain, regionprovincemailing) Values (@AgencyCode, @AgencyName , '', -1, year(getdate())+ Month(getdate()), '03', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'ENTERPRISEADMIN', '', '', '', '')

set @AgencyID = @@identity

assp_insert_agency_subaccounts  @agencyid, @AgencyCode, @AgencyName, 'ENTERPRISEADMIN'

INSERT INTO DMSEmail (uniqagency, accountname, displayname, replyaddress, emailaddress, username, pswd, primaryserveraddress, primaryportnumber, backupserveraddress, backupportnumber, customdisclaimer, flags, insertedbycode, inserteddate, updatedbycode, updateddate, attachastype) SELECT @agencyid, @AgencyCode, '', '', '', '', '', '', 25, '', 25, '', 0, 'ENTERPRISEADMIN', current_timestamp, '', NULL, 0 FROM DMSEmail WHERE UniqAgency = @agencyid HAVING COUNT(UniqAgency) = 0

....
set @AgencyID = @@identity

EXEC assp_insert_agency_subaccounts  @agencyid, @AgencyCode, @AgencyName, 'ENTERPRISEADMIN'
....