CREATE  PROCEDURE dbo.usp_UI_MoveAccount
(
	@AccountID int,
	@SourceDeptID int,
	@DestDeptID int
)

AS
   
SET NOCOUNT ON
SET XACT_ABORT ON
set transaction isolation level read uncommitted

BEGIN TRAN

DECLARE  @tblAccount_KeywordCreative TABLE
(
	Ident int IDENTITY (1, 1) NOT NULL,
	KeywordCreativeID int,
	SortIndex int
)
INSERT INTO @tblAccount_KeywordCreative 
SELECT KeywordCreativeID,SortIndex
FROM tblAccount_KeywordCreative AKC
WHERE AKC.AccountID=@AccountID

print '@tblAccount_KeywordCreative created'

DECLARE  @tblListing TABLE
(
	ListingID int,
	AdGroupID int
)
INSERT INTO @tblListing 
SELECT ListingID,AdGroupID
FROM tblListing
WHERE AccountID=@AccountID

print '@tblListing created'
DECLARE  @tblAdGroupCreative TABLE
(
	AdGroupCreativeID int,
	KeywordCreativeID int	
)

IF EXISTS (Select * from @tblListing WHERE AdGroupID IS NOT NULL)
BEGIN
	INSERT INTO @tblAdGroupCreative
	Select AGC.AdGroupCreativeID,AGC.KeywordCreativeID
	FROM @tblListing l 
	inner join tblAdGroup_AdGroupCreative A_AC on A_AC.AdGroupID=l.AdGroupID
	inner join tblAdGroupCreative AGC on AGC.AdGroupCreativeID=A_AC.AdGroupCreativeID
	WHERE l.AdGroupID IS NOT NULL

END

print '@tblAdGroupCreative created'
DECLARE  @tblListingCreative TABLE
(
	ListingCreativeID int,
	KeywordCreativeID int	
)
INSERT INTO @tblListingCreative 
Select LC.ListingCreativeID,LC.KeywordCreativeID
FROM @tblListing l 
inner join tblListing_ListingCreative L_LC on L_LC.ListingID=l.ListingID
inner join tblListingCreative LC on LC.ListingCreativeID=L_LC.ListingCreativeID

print '@tblListingCreative created'

DELETE FROM tblAccount_KeywordCreative WHERE AccountID=@AccountID
print '@tblListingCreative deleted'

DECLARE  @curr_id int,@highest_id int,@next_id int
DECLARE @NewKeywordCreativeID int,@NewKeywordTemplateGroupID int,@CurrKeywordCreativeID int

SELECT  @curr_id=0
SELECT  @highest_id=MAX(Ident) FROM @tblAccount_KeywordCreative 
WHILE @curr_id <@highest_id
BEGIN
	SELECT TOP 1 @next_id=Ident
	FROM @tblAccount_KeywordCreative
	WHERE Ident>@curr_id
	ORDER BY Ident
print @next_id
	SELECT @NewKeywordCreativeID=dbo.ufn_CkeckKeywordCreativeExists(@DestDeptID, ProductTypeCode,
	LanguageCode, PRDTitle,PRDDescription, PRDDescription1, PRDDisplayUrl,
	CTYTitle, CTYDescription, CTYDescription1, CTYDisplayUrl,
	CNTTitle, CNTDescription, CNTDescription1,CNTDisplayUrl,
	GENTitle, GENDescription, GENDescription1, GENDisplayUrl)
	FROM @tblAccount_KeywordCreative t
	Inner Join tblKeywordCreative KC ON KC.KeywordCreativeID=t.KeywordCreativeID 
	WHERE t.Ident=@next_id

	IF @NewKeywordCreativeID=0 
	BEGIN
		INSERT INTO tblKeywordCreative
		SELECT @DestDeptID, ProductTypeCode, LanguageCode, KeywordCreativeName, PRDTitle, 
		PRDDescription, PRDDescription1, PRDDisplayUrl, CTYTitle, CTYDescription, 
		CTYDescription1, CTYDisplayUrl, CNTTitle, CNTDescription, CNTDescription1,
		CNTDisplayUrl, GENTitle, GENDescription, GENDescription1, GENDisplayUrl, 
		InsertDate, UpdateDate, UpdatedByUserID, TempDataImport_NonGoogleKeywordDetailID, 
		KC.TempDataImport_GoogleCountryID
		FROM @tblAccount_KeywordCreative t
		Inner Join tblKeywordCreative KC ON KC.KeywordCreativeID=t.KeywordCreativeID 
		WHERE t.Ident=@next_id
		
		SELECT @NewKeywordCreativeID=SCOPE_IDENTITY()
	END 


	IF NOT EXISTS (SELECT * FROM tblAccount_KeywordCreative WHERE AccountID=@AccountID and  KeywordCreativeID=@NewKeywordCreativeID)
	BEGIN
		INSERT INTO tblAccount_KeywordCreative 
		SELECT @AccountID,@NewKeywordCreativeID,SortIndex
		FROM @tblAccount_KeywordCreative t
		WHERE t.Ident=@next_id
	END

	SELECT @CurrKeywordCreativeID=KeywordCreativeID FROM @tblAccount_KeywordCreative t WHERE t.Ident=@next_id

	IF EXISTS (Select * from @tblAdGroupCreative)
	BEGIN
		UPDATE tblAdGroupCreative SET KeywordCreativeID=@NewKeywordCreativeID
		WHERE AdGroupCreativeID  IN (select AdGroupCreativeID FROM @tblAdGroupCreative WHERE KeywordCreativeID=@CurrKeywordCreativeID	)
	END

	IF EXISTS (Select * from @tblListingCreative)
	BEGIN
		UPDATE tblListingCreative SET KeywordCreativeID=@NewKeywordCreativeID
		WHERE ListingCreativeID IN (select ListingCreativeID FROM @tblListingCreative WHERE KeywordCreativeID= @CurrKeywordCreativeID)
	END
	SET @curr_id=@next_id

END 

DECLARE @S_KeywordTemplateGroupID int

SELECT @S_KeywordTemplateGroupID=KeywordTemplateGroupID 
FROM tblAccount 
WHERE  AccountID=@AccountID and DepartmentID=@SourceDeptID

IF EXISTS (Select * from tblKeywordTemplateGroup_KeywordTemplate WHERE KeywordTemplateGroupID=@S_KeywordTemplateGroupID )
BEGIN
	SELECT @NewKeywordTemplateGroupID=dbo.ufn_CheckKeywordTemplateGroupExists(@S_KeywordTemplateGroupID,@DestDeptID)
	IF @NewKeywordTemplateGroupID=0
	BEGIN
		INSERT INTO tblKeywordTemplateGroup
		SELECT 	@DestDeptID,KTG.KeywordTemplateGroupName,
		KTG.InsertDate,KTG.UpdateDate,KTG.UpdatedByUserID,
		KTG.TempDataImport_OldTemplateGroupID 
		FROM tblKeywordTemplateGroup KTG 
		WHERE KTG.KeywordTemplateGroupID=@S_KeywordTemplateGroupID
	
		SELECT @NewKeywordTemplateGroupID=SCOPE_IDENTITY()
		
		INSERT INTO tblKeywordTemplateGroup_KeywordTemplate
		SELECT  @NewKeywordTemplateGroupID,KTGKT.KeywordTemplateID,
			KTGKT.InsertDate,KTGKT.InsertedByUserID
		FROM tblKeywordTemplateGroup_KeywordTemplate KTGKT
		WHERE KeywordTemplateGroupID=@S_KeywordTemplateGroupID
	END
	
	UPDATE tblAccount SET DepartmentID=@DestDeptID,KeywordTemplateGroupID=@NewKeywordTemplateGroupID
	WHERE AccountID=@AccountID and DepartmentID=@SourceDeptID
END
ELSE
BEGIN
	UPDATE tblAccount SET DepartmentID=@DestDeptID
	WHERE AccountID=@AccountID and DepartmentID=@SourceDeptID
END

insert into temp values('sss')

COMMIT TRAN

GO