-----------------------------------------------------------------------------------------------------------------------
--Insert missing Company preference values for the Custom company (customer created company) from Default admin company
-----------------------------------------------------------------------------------------------------------------------
DECLARE @companyId Uniqueidentifier
DECLARE @CreatedBy Uniqueidentifier
DECLARE @UpdatedBy Uniqueidentifier
Declare @CompanyName Nvarchar(500)

-- Get the new servicer company for which missing settings needs to be added
DECLARE db_cursor CURSOR FOR 
SELECT CompanyID,CompanyName from AMS_Company where IsServiceCompany = 1 and CompanyName not in ('AMS_SYSTEM','Workflow Administrator')
OPEN db_cursor 
BEGIN TRY
	FETCH NEXT FROM db_cursor INTO @companyId ,@CompanyName 
	-- Get the comapny id and coresponding User id for Updated and created User columns
	WHILE @@FETCH_STATUS = 0  
	BEGIN
		-- Pik Default values defined by Customer for new Company of an old setting
		SELECT @CreatedBy = CreatedBy,@UpdatedBy = UpdatedBy from AMS_CompanyPreferences where CompanyId =@companyId and PreferenceName ='AMSCompanyName'

		-- Just ensure settings will not get duplicated
		IF EXISTS (SELECT cp.PreferenceName  FROM AMS_Company cmp inner join AMS_CompanyPreferences cp ON cp.CompanyId = cmp.CompanyID
			WHERE cmp.CompanyName ='Workflow Administrator' and PreferenceName not in (SELECT PreferenceName  FROM AMS_CompanyPreferences  WHERE CompanyId =@companyId)) 
		BEGIN 
			Print ('Setting values for the company :- ' + @CompanyName)
			--Default all new values will get inserted into Workflow Administrator company so pick them from admin company and recreate in new company
			INSERT INTO AMS_CompanyPreferences 
			SELECT PreferenceName,PreferenceValue,ValueType,EnumType,@CreatedBy CreatedBy,GetDate() CreatedDate,@UpdatedBy UpdatedBy,GetDate() UpdatedDate,@companyId CompanyId 
			FROM AMS_CompanyPreferences 
			WHERE CompanyId in (SELECT CompanyID FROM AMS_Company WHERE IsServiceCompany =1 and CompanyName  in ('Workflow Administrator'))
			and PreferenceName not in (SELECT PreferenceName FROM AMS_CompanyPreferences WHERE CompanyId =@companyId )
		END

		FETCH NEXT FROM db_cursor INTO @companyId ,@CompanyName 
	End
END TRY
BEGIN CATCH
	Deallocate db_cursor
	Print 'Error Occur that is:'  
	print ERROR_NUMBER()
	Print Error_Message()  	
END CATCH
	Deallocate db_cursor
GO