USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION

CREATE procedure dbo.USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION
(
    @IDSETREGISTERID uniqueidentifier = null,
    @RECORDTYPECODE tinyint = 0,
    @CHOSENITEMS xml = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output
)
as
    /*
        @RECORDTYPECODE
            1    Designation.  Maps to dbo.DESIGNATION
            2    Purpose.  Maps to dbo.DESIGNATIONLEVEL

        @CHOSENITEMS
            ID
                0    (Purpose) Designation Level Category Code
                1    (Purpose) SITE
                2    (Purpose) Report 1 Code
                3    (Purpose) Report 2 Code
                4    (Designation) Report 1 Code
                5    (Designation) Report 2 Code
                6    (Designation) Is Revenue Designation
                7    (Designation) Designation Use Code
                8    (Designation) VSE Category
                9    (Designation) VSE Subcategory

            SELECTEDACTION
                0    Set new value (used on bit fields)
                1    Set value if no value exists
                2    Set value if no value exists, or overwrite existing values
                3    Set to null
    */

    set nocount off
    begin try
        declare @ACTION table (
              ID int,
              SELECTEDACTION int,
              NEWVALUE uniqueidentifier
        );
        declare @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION as table (ID uniqueidentifier primary key);
        declare @UPDATED as table (ID uniqueidentifier);
        declare @CURRENTDATE datetime;

        set @CURRENTDATE = getdate();
        set @NUMBERADDED = 0;
        set @NUMBEREDITED = 0;
        set @NUMBERDELETED = 0;

        if @CHANGEAGENTID is null
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

        insert into @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION(ID)
        select distinct ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID)
        where @IDSETREGISTERID is not null

        union all

        select ID from dbo.DESIGNATION
        where @IDSETREGISTERID is null and @RECORDTYPECODE = 1

        union all

        select ID from dbo.DESIGNATIONLEVEL
        where @IDSETREGISTERID is null and @RECORDTYPECODE = 2;

        insert into @ACTION (ID,SELECTEDACTION,NEWVALUE)
        select ID,SELECTEDACTION,NEWVALUE
        from dbo.UFN_MANAGEPURPOSEDESIGNATIONINFORMATION_GETCHOSENITEMS_FROMITEMLISTXML(@CHOSENITEMS);

        if (@RECORDTYPECODE = 1)
            begin
                update dbo.DESIGNATION
                    set DESIGNATION.DESIGNATIONREPORT1CODEID = NEWVALUE.NEWVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATION
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATION.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 4
                    cross apply
                    (
                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 1
                            and DESIGNATIONREPORT1CODEID is null

                        union all

                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 2
                            and (DESIGNATIONREPORT1CODEID is null
                                or ACTIONITEM.NEWVALUE <> DESIGNATION.DESIGNATIONREPORT1CODEID)

                        union all

                        select ID as DESIGNATIONID, null as NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 3
                            and DESIGNATIONREPORT1CODEID is not null
                    ) as NEWVALUE
                where (DESIGNATION.ID = NEWVALUE.DESIGNATIONID)
                    and (exists (select ID from dbo.DESIGNATIONREPORT1CODE where ID = NEWVALUE.NEWVALUE) or (ACTIONITEM.SELECTEDACTION = 3));


                update dbo.DESIGNATION
                    set DESIGNATIONREPORT2CODEID = NEWVALUE.NEWVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATION
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATION.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 5
                    cross apply
                    (
                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 1
                            and DESIGNATIONREPORT2CODEID is null

                        union all

                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 2
                            and (DESIGNATIONREPORT2CODEID is null
                                or ACTIONITEM.NEWVALUE <> DESIGNATION.DESIGNATIONREPORT2CODEID)

                        union all

                        select ID as DESIGNATIONID, null as NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 3
                            and DESIGNATIONREPORT2CODEID is not null
                    ) as NEWVALUE
                where (DESIGNATION.ID = NEWVALUE.DESIGNATIONID)
                    and (exists (select ID from dbo.DESIGNATIONREPORT2CODE where ID = NEWVALUE.NEWVALUE) or (ACTIONITEM.SELECTEDACTION = 3));


                update dbo.DESIGNATION
                set ISREVENUEDESIGNATION = NEWVALUE.NEWVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATION
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATION.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 6
                    cross apply
                    (
                        select ID as DESIGNATIONID, case when ACTIONITEM.NEWVALUE = '00000000-0000-0000-0000-000000000001' then cast(1 as bit) else cast(0 as bit) end as NEWVALUE
                        from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 0
                    ) as NEWVALUE
                where DESIGNATION.ID = NEWVALUE.DESIGNATIONID
                    and DESIGNATION.ISREVENUEDESIGNATION <> NEWVALUE.NEWVALUE;


                update dbo.DESIGNATION
                    set DESIGNATIONUSECODEID = NEWVALUE.NEWVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATION
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATION.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 7
                    cross apply
                    (
                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 1
                            and DESIGNATIONUSECODEID is null

                        union all

                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 2
                            and (DESIGNATIONUSECODEID is null
                                or ACTIONITEM.NEWVALUE <> DESIGNATION.DESIGNATIONUSECODEID)

                        union all

                        select ID as DESIGNATIONID, null as NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 3
                            and DESIGNATIONUSECODEID is not null
                    ) as NEWVALUE
                where (DESIGNATION.ID = NEWVALUE.DESIGNATIONID)
                    and (exists (select ID from dbo.DESIGNATIONUSECODE where ID = NEWVALUE.NEWVALUE) or (ACTIONITEM.SELECTEDACTION = 3));


                update dbo.DESIGNATION
                    set VSECATEGORYID = NEWVALUE.NEWVALUE
                    ,VSESUBCATEGORYID =
                        case
                            when (VSESUBCATEGORYID is not null and (ACTIONITEM.SELECTEDACTION = 3 or VSECATEGORYID <> NEWVALUE.NEWVALUE)) then null
                            else VSESUBCATEGORYID 
                        end
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATION
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATION.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 8
                    cross apply
                    (
                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 1
                            and VSECATEGORYID is null

                        union all

                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 2
                            and (VSECATEGORYID is null 
                                or ACTIONITEM.NEWVALUE <> dbo.DESIGNATION.VSECATEGORYID)

                        union all

                        select ID as DESIGNATIONID, null as NEWVALUE from DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 3
                            and VSECATEGORYID is not null 
                    ) as NEWVALUE
                where DESIGNATION.ID = NEWVALUE.DESIGNATIONID
                    and (exists (select ID from dbo.VSECATEGORY where ID = NEWVALUE.NEWVALUE) or (ACTIONITEM.SELECTEDACTION = 3));


                update dbo.DESIGNATION
                    set VSESUBCATEGORYID = NEWVALUE.NEWVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATION
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATION.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 9
                    cross apply
                    (
                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 1
                            and VSESUBCATEGORYID is null

                        union all

                        select ID as DESIGNATIONID, ACTIONITEM.NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 2
                            and (VSESUBCATEGORYID is null 
                                or ACTIONITEM.NEWVALUE <> DESIGNATION.VSESUBCATEGORYID)

                        union all

                        select ID as DESIGNATIONID, null as NEWVALUE from dbo.DESIGNATION
                        where ACTIONITEM.SELECTEDACTION = 3
                            and VSESUBCATEGORYID is not null 
                    ) as NEWVALUE
                where DESIGNATION.ID = NEWVALUE.DESIGNATIONID
                    and (exists (select ID from dbo.VSESUBCATEGORY where ID = NEWVALUE.NEWVALUE) or (ACTIONITEM.SELECTEDACTION = 3));
            end
        else
            begin
                update DESIGNATIONLEVEL
                    set DESIGNATIONLEVELCATEGORYCODEID = NEWVALUE.NEWVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATIONLEVEL
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATIONLEVEL.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 0
                    cross apply
                    (
                        select ID as DESIGNATIONLEVELID, ACTIONITEM.NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 1
                            and DESIGNATIONLEVELCATEGORYCODEID is null

                        union all

                        select ID as DESIGNATIONLEVELID, ACTIONITEM.NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 2
                            and (DESIGNATIONLEVELCATEGORYCODEID is null
                                or ACTIONITEM.NEWVALUE <> DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID)

                        union all

                        select ID as DESIGNATIONLEVELID, null as NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 3
                            and DESIGNATIONLEVELCATEGORYCODEID is not null
                    ) as NEWVALUE
                where (DESIGNATIONLEVEL.ID = NEWVALUE.DESIGNATIONLEVELID)
                    and (exists (select ID from dbo.DESIGNATIONLEVELCATEGORYCODE where ID = NEWVALUE.NEWVALUE) or (ACTIONITEM.SELECTEDACTION = 3));


                update dbo.DESIGNATIONLEVEL
                    set SITEID = NEWVALUE.NEWVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATIONLEVEL
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATIONLEVEL.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 1
                    cross apply
                    (
                        select ID as DESIGNATIONLEVELID, ACTIONITEM.NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 1
                            and SITEID is null

                        union all

                        select ID as DESIGNATIONLEVELID, ACTIONITEM.NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 2
                            and (SITEID is null 
                                or ACTIONITEM.NEWVALUE <> dbo.DESIGNATIONLEVEL.SITEID)

                        union all

                        select ID as DESIGNATIONLEVELID, null as NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 3
                            and SITEID is not null
                    ) as NEWVALUE
                where DESIGNATIONLEVEL.ID = NEWVALUE.DESIGNATIONLEVELID
                    and (exists (select ID from dbo.SITE where ID = NEWVALUE.NEWVALUE) or (ACTIONITEM.SELECTEDACTION = 3));


                update dbo.DESIGNATIONLEVEL
                    set DESIGNATIONREPORT1CODEID = NEWVALUE.NEWVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATIONLEVEL
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATIONLEVEL.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 2
                    cross apply
                    (
                        select ID as DESIGNATIONLEVELID, ACTIONITEM.NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 1
                            and DESIGNATIONREPORT1CODEID is null

                        union all

                        select ID as DESIGNATIONLEVELID, ACTIONITEM.NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 2
                            and (DESIGNATIONREPORT1CODEID is null
                                or ACTIONITEM.NEWVALUE <> DESIGNATIONLEVEL.DESIGNATIONREPORT1CODEID)

                        union all

                        select ID as DESIGNATIONLEVELID, null as NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 3
                            and DESIGNATIONREPORT1CODEID is not null
                    ) as NEWVALUE
                where (DESIGNATIONLEVEL.ID = NEWVALUE.DESIGNATIONLEVELID)
                    and (exists (select ID from dbo.DESIGNATIONREPORT1CODE where ID = NEWVALUE.NEWVALUE) or (ACTIONITEM.SELECTEDACTION = 3));


                update dbo.DESIGNATIONLEVEL
                    set DESIGNATIONREPORT2CODEID = NEWVALUE.NEWVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                output
                    inserted.ID
                into
                    @UPDATED(ID)
                from dbo.DESIGNATIONLEVEL
                    inner join @GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONINFORMATION MANAGEPURPOSEDESIGNATION on DESIGNATIONLEVEL.ID = MANAGEPURPOSEDESIGNATION.ID
                    inner join @ACTION as ACTIONITEM on ACTIONITEM.ID = 3
                    cross apply
                    (
                        select ID as DESIGNATIONLEVELID, ACTIONITEM.NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 1
                            and DESIGNATIONREPORT2CODEID is null

                        union all

                        select ID as DESIGNATIONLEVELID, ACTIONITEM.NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 2
                            and (DESIGNATIONREPORT2CODEID is null
                                or ACTIONITEM.NEWVALUE <> DESIGNATIONLEVEL.DESIGNATIONREPORT2CODEID)

                        union all

                        select ID as DESIGNATIONLEVELID, null as NEWVALUE from dbo.DESIGNATIONLEVEL
                        where ACTIONITEM.SELECTEDACTION = 3
                            and DESIGNATIONREPORT2CODEID is not null
                    ) as NEWVALUE
                where (DESIGNATIONLEVEL.ID = NEWVALUE.DESIGNATIONLEVELID)
                    and (exists (select ID from dbo.DESIGNATIONREPORT2CODE where ID = NEWVALUE.NEWVALUE) or (ACTIONITEM.SELECTEDACTION = 3));
            end
            set @NUMBEREDITED = (select count(distinct ID) from @UPDATED);
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch