USP_ADDUPDATE_CONTACTCONSENT

CREATE procedure dbo.USP_ADDUPDATE_CONTACTCONSENT
(

     @ID  uniqueidentifier,
     @SOLICITCODES  xml

)
as
            if @SOLICITCODES is not null
            begin
                declare @CHANGEAGENTID UNIQUEIDENTIFIER;
                exec dbo.Usp_changeagent_getorcreatechangeagent @CHANGEAGENTID OUTPUT;

                declare @DATE datetime
                set @DATE = Getdate()

                -- address the auto end date issues for consent based SC

                exec USP_CONSTITUENTSOLICITCODE_ADJUSTSOLICITCODEDATERANGES @ID, @SOLICITCODES, @CHANGEAGENTID;

                     begin try
                     -- If two consents are added on same date, then it will update row and date with latest one.

                      with UPDATEROWS as
                      (

                         select CONSTITUENTSOLICITCODE.ID  CONSTITUENTSOLICITCODEID,
                      solicitinput.SOLICITCODEID, 
                      case when solicitinput.STARTDATE = '0001-01-01' then null
                      else solicitinput.STARTDATE
                      end as STARTDATE,
                      case when solicitinput.ENDDATE = '0001-01-01' then null
                      else solicitinput.ENDDATE
                      end as ENDDATE,
                      solicitinput.COMMENTS, 
                      solicitinput.CONSENTPREFERENCECODE,
                      solicitinput.SOURCECODEID, 
                      solicitinput.SOURCEFILEPATH, 
                      solicitinput.PRIVACYPOLICYFILEPATH, 
                      solicitinput.SUPPORTINGINFORMATION, 
                      solicitinput.CONSENTSTATEMENT  
                      from dbo.UFN_CONSTITUENTUPDATE_GETSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES) solicitinput
                      inner join dbo.CONSTITUENTSOLICITCODE on solicitinput.SOLICITCODEID = CONSTITUENTSOLICITCODE.SOLICITCODEID
                      inner join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
                        where
                          CONSTITUENTSOLICITCODE.CONSTITUENTID = @ID
                          and (solicitinput.STARTDATE = '0001-01-01' or [dbo].[UFN_DATE_GETEARLIESTTIME](CONSTITUENTSOLICITCODE.STARTDATE) = [dbo].[UFN_DATE_GETEARLIESTTIME](solicitinput.STARTDATE))
                          and (CONSTITUENTSOLICITCODE.ENDDATE is null or SOLICITCODE.CONSENTCODE =0)
                      )
                      update dbo.CONSTITUENTSOLICITCODE
                      set
                        STARTDATE = UPDATEROWS.STARTDATE,
                        ENDDATE = UPDATEROWS.ENDDATE,
                        CONSENTPREFERENCECODE = UPDATEROWS.CONSENTPREFERENCECODE,
                        SOURCECODEID = case 
                          when UPDATEROWS.SOURCECODEID = '00000000-0000-0000-0000-000000000000' then null
                          else UPDATEROWS.SOURCECODEID
                        end,
                        SOURCEFILEPATH =UPDATEROWS.SOURCEFILEPATH,
                        CONSENTSTATEMENT = UPDATEROWS.CONSENTSTATEMENT,
                        PRIVACYPOLICYFILEPATH = UPDATEROWS.PRIVACYPOLICYFILEPATH,
                        SUPPORTINGINFORMATION = UPDATEROWS.SUPPORTINGINFORMATION,
                        DATECHANGED = @DATE,
                        CHANGEDBYID = @CHANGEAGENTID
                      from UPDATEROWS
                      where
                        CONSTITUENTSOLICITCODE.ID = UPDATEROWS.CONSTITUENTSOLICITCODEID

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

                    -- This will insert new consent into constituentsolicitcoe table

                    insert into CONSTITUENTSOLICITCODE (
                      ID,   
            CONSTITUENTID, 
                      SOLICITCODEID, 
                      SEQUENCE, 
                      ADDEDBYID, 
                      CHANGEDBYID, 
                      DATEADDED, 
                      DATECHANGED, 
                      STARTDATE,   
                      COMMENTS, 
                      CONSENTPREFERENCECODE, 
                      SOURCECODEID, 
                      SOURCEFILEPATH, 
                      PRIVACYPOLICYFILEPATH, 
                      SUPPORTINGINFORMATION,  
                      CONSENTSTATEMENT)
                    select  
                      newid(), 
                      @ID, 
                      solicit.SOLICITCODEID, 
                      solicit.SEQUENCE, 
                      @CHANGEAGENTID, 
                      @CHANGEAGENTID, 
                      @DATE, 
                      @DATE, 
                        case
                          when solicit.STARTDATE = '0001-01-01' then null
                          else solicit.STARTDATE
                        end,  
                      solicit.COMMENTS, 
                      solicit.CONSENTPREFERENCECODE,
                        case 
                          when solicit.SOURCECODEID = '00000000-0000-0000-0000-000000000000' then null
                          else solicit.SOURCECODEID
                        end, 
                      solicit.SOURCEFILEPATH, 
                      solicit.PRIVACYPOLICYFILEPATH, 
                      solicit.SUPPORTINGINFORMATION, 
                      solicit.CONSENTSTATEMENT  
                    from dbo.UFN_CONSTITUENTUPDATE_GETSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES) solicit
                    where solicit.SOLICITCODEID not in (select  SOLICITCODEID from CONSTITUENTSOLICITCODE where CONSTITUENTID = @ID and  ENDDATE is null )
                    and solicit.ENDDATE = '0001-01-01'

            end