USP_CLONESITE_ROLESELECTIONCONTROL

CREATE procedure [dbo].[USP_CLONESITE_ROLESELECTIONCONTROL]
(
     @SourceClientSitesID    as int 
    ,@TargetClientSitesID    as int 
    ,@CurrentUserID            as int
    ,@CopyBatchGUID            as uniqueidentifier 
)
as
begin

    set nocount on;

declare @CHANGEAGENTID uniqueidentifier
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
insert into dbo.FAFFUNDRAISINGROLESCONTENT
    (ID, 
    REGISTRATIONPAGEID,     
    STARTACOMPANYTITLE,
    STARTACOMPANYDESCRIPTION,
    STARTACOMPANYIMAGEID,
    STARTATEAMTITLE,
    STARTATEAMDESCRIPTION,
    STARTATEAMTITLEIMAGEID,
    STARTATEAMUNDERGROUPTITLE,
    STARTATEAMUNDERGROUPDESCRIPTION,
    STARTATEAMUNDERGROUPIMAGEID,
    STARTATEAMINDEPENDENTTITLE,
    STARTATEAMINDEPENDENTDESCRIPTION,
    STARTATEAMINDEPENDENTIMAGEID,
    JOINATEAMTITLE,
    JOINATEAMDESCRIPTION,
    JOINATEAMTITLEIMAGEID,
    STARTAHOUSEHOLDTITLE,
    STARTAHOUSEHOLDDESCRIPTION,
    STARTAHOUSEHOLDIMAGEID,
    STARTAHOUSEHOLDUNDERCOMPANYTITLE,
    STARTAHOUSEHOLDUNDERCOMPANYDESCRIPTION,
    STARTAHOUSEHOLDUNDERCOMPANYIMAGEID,
    STARTAHOUSEHOLDUNDERTEAMTITLE,
    STARTAHOUSEHOLDUNDERTEAMDESCRIPTION,
    STARTAHOUSEHOLDUNDERTEAMIMAGEID,
    STARTAHOUSEHOLDINDEPENDENTTITLE,
    STARTAHOUSEHOLDINDEPENDENTDESCRIPTION,
    STARTAHOUSEHOLDINDEPENDENTIMAGEID,
    JOINASINDIVIDUALTITLE,
    JOINASINDIVIDUALDESCRIPTION,
    JOINASINDIVIDUALIMAGEID,
    JOINASINDIVIDUALUNDERCOMPANYTITLE,
    JOINASINDIVIDUALUNDERCOMPANYDESCRIPTION,
    JOINASINDIVIDUALUNDERCOMPANYIMAGEID,
    JOINASINDIVIDUALPARTICIPANTTITLE,
    JOINASINDIVIDUALPARTICIPANTDESCRIPTION,
    JOINASINDIVIDUALPARTICIPANTIMAGEID, 
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED)
select 
    SC2.Guid, 
    P.ID, 
    STARTACOMPANYTITLE,
    STARTACOMPANYDESCRIPTION,
    COALESCE(IMG.ID, 0),
    STARTATEAMTITLE,
    STARTATEAMDESCRIPTION,
    COALESCE(IMG2.ID, 0),
    STARTATEAMUNDERGROUPTITLE,
    STARTATEAMUNDERGROUPDESCRIPTION,
    COALESCE(IMG3.ID, 0),
    STARTATEAMINDEPENDENTTITLE,
    STARTATEAMINDEPENDENTDESCRIPTION,
    COALESCE(IMG4.ID, 0),
    JOINATEAMTITLE,
    JOINATEAMDESCRIPTION,
    COALESCE(IMG5.ID, 0),
    STARTAHOUSEHOLDTITLE,
    STARTAHOUSEHOLDDESCRIPTION,
    COALESCE(IMG6.ID, 0),
    STARTAHOUSEHOLDUNDERCOMPANYTITLE,
    STARTAHOUSEHOLDUNDERCOMPANYDESCRIPTION,
    COALESCE(IMG7.ID, 0),
    STARTAHOUSEHOLDUNDERTEAMTITLE,
    STARTAHOUSEHOLDUNDERTEAMDESCRIPTION,
    COALESCE(IMG8.ID, 0),
    STARTAHOUSEHOLDINDEPENDENTTITLE,
    STARTAHOUSEHOLDINDEPENDENTDESCRIPTION,
    COALESCE(IMG9.ID, 0),
    JOINASINDIVIDUALTITLE,
    JOINASINDIVIDUALDESCRIPTION,
    COALESCE(IMG10.ID, 0),
    JOINASINDIVIDUALUNDERCOMPANYTITLE,
    JOINASINDIVIDUALUNDERCOMPANYDESCRIPTION,
    COALESCE(IMG11.ID, 0),
    JOINASINDIVIDUALPARTICIPANTTITLE,
    JOINASINDIVIDUALPARTICIPANTDESCRIPTION,
    COALESCE(IMG12.ID, 0),
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CURRENTDATE, 
    @CURRENTDATE  
    from FAFFUNDRAISINGROLESCONTENT FC 
    inner join SiteContent SC 
        on FC.ID = SC.Guid and SC.ClientSitesID = @SourceClientSitesID
    inner join SiteContent SC2 
        on SC2.CopyBatchGUID = @CopyBatchGUID
        and SC.ID = SC2.CopySourceID  and SC2.ClientSitesID = @TargetClientSitesID
    inner join SitePages P
        on P.COPYSOURCEID = FC.REGISTRATIONPAGEID and P.COPYBATCHGUID = @CopyBatchGUID and P.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG
        on IMG.CopySourceID = FC.STARTACOMPANYIMAGEID and IMG.COPYBATCHGUID = @CopyBatchGUID and IMG.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG2
        on IMG2.CopySourceID = FC.STARTATEAMTITLEIMAGEID and IMG2.COPYBATCHGUID = @CopyBatchGUID and IMG2.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG3
        on IMG3.CopySourceID = FC.STARTATEAMUNDERGROUPIMAGEID and IMG3.COPYBATCHGUID = @CopyBatchGUID and IMG3.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG4
        on IMG4.CopySourceID = FC.STARTATEAMINDEPENDENTIMAGEID and IMG4.COPYBATCHGUID = @CopyBatchGUID and IMG4.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG5
        on IMG5.CopySourceID = FC.JOINATEAMTITLEIMAGEID and IMG5.COPYBATCHGUID = @CopyBatchGUID and IMG5.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG6
        on IMG6.CopySourceID = FC.STARTAHOUSEHOLDIMAGEID and IMG6.COPYBATCHGUID = @CopyBatchGUID and IMG6.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG7
        on IMG7.CopySourceID = FC.STARTAHOUSEHOLDUNDERCOMPANYIMAGEID and IMG7.COPYBATCHGUID = @CopyBatchGUID and IMG7.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG8
        on IMG8.CopySourceID = FC.STARTAHOUSEHOLDUNDERTEAMIMAGEID and IMG8.COPYBATCHGUID = @CopyBatchGUID and IMG8.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG9
        on IMG9.CopySourceID = FC.STARTAHOUSEHOLDINDEPENDENTIMAGEID and IMG9.COPYBATCHGUID = @CopyBatchGUID and IMG9.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG10
        on IMG10.CopySourceID = FC.JOINASINDIVIDUALIMAGEID and IMG10.COPYBATCHGUID = @CopyBatchGUID and IMG10.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG11
        on IMG11.CopySourceID = FC.JOINASINDIVIDUALUNDERCOMPANYIMAGEID and IMG11.COPYBATCHGUID = @CopyBatchGUID and IMG11.ClientSitesID = @TargetClientSitesID
    left outer join SiteImages IMG12
        on IMG12.CopySourceID = FC.JOINASINDIVIDUALPARTICIPANTIMAGEID and IMG12.COPYBATCHGUID = @CopyBatchGUID and IMG12.ClientSitesID = @TargetClientSitesID

    set nocount off;
end