USP_DATAFORMTEMPLATE_VIEW_EMAILSTATUSHISTORYSETTINGS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EMAILSTATUSHISTORYSETTINGS
(
    @DATALOADED bit = 0 output
  ,@GLOBALCHANGEID uniqueidentifier = null output
  ,@DELETEGENERALPURPOSESTATUSOLDERTHAN integer = null output
  ,@DELETEGENERALPURPOSEINTERVALTYPE varchar(10) = null output
  ,@DELETETRANSACTIONALSTATUSOLDERTHAN integer = null output
  ,@DELETETRANSACTIONALINTERVALTYPE varchar(10) = null output
)
as
begin

    set nocount on;

  -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.  
  if exists(select top 1 ID from dbo.GLOBALCHANGE where GLOBALCHANGECATALOGID='8C3ECDB1-BE50-4898-AD1B-98ECB6D07812')
  begin
      select top 1
      @DATALOADED = 1
      ,@GLOBALCHANGEID = ID
      ,@DELETEGENERALPURPOSESTATUSOLDERTHAN = GLOBALCHANGEDATAFORMITEM.value('declare namespace bb=''bb_appfx_dataforms''; (/bb:DataFormItem/bb:Values/bb:fv[@ID=''DeleteGeneralPurposeStatusOlderThan'']/bb:Value)[1]', 'integer')
      ,@DELETEGENERALPURPOSEINTERVALTYPE = (case  when GLOBALCHANGEDATAFORMITEM.value('declare namespace bb=''bb_appfx_dataforms''; (/bb:DataFormItem/bb:Values/bb:fv[@ID=''DeleteGeneralPurposeIntervalType'']/bb:Value)[1]', 'integer') = 0 then 'Year(s)'
                                                  when GLOBALCHANGEDATAFORMITEM.value('declare namespace bb=''bb_appfx_dataforms''; (/bb:DataFormItem/bb:Values/bb:fv[@ID=''DeleteGeneralPurposeIntervalType'']/bb:Value)[1]', 'integer') = 2 then 'Month(s)'
                                                  when GLOBALCHANGEDATAFORMITEM.value('declare namespace bb=''bb_appfx_dataforms''; (/bb:DataFormItem/bb:Values/bb:fv[@ID=''DeleteGeneralPurposeIntervalType'']/bb:Value)[1]', 'integer') = 4 then 'Day(s)' end) 
      ,@DELETETRANSACTIONALSTATUSOLDERTHAN = GLOBALCHANGEDATAFORMITEM.value('declare namespace bb=''bb_appfx_dataforms''; (/bb:DataFormItem/bb:Values/bb:fv[@ID=''DeleteTransactionalStatusOlderThan'']/bb:Value)[1]', 'integer')
      ,@DELETETRANSACTIONALINTERVALTYPE = (case  when GLOBALCHANGEDATAFORMITEM.value('declare namespace bb=''bb_appfx_dataforms''; (/bb:DataFormItem/bb:Values/bb:fv[@ID=''DeleteTransactionalIntervalType'']/bb:Value)[1]', 'integer') = 0 then 'Year(s)'
                                                   when GLOBALCHANGEDATAFORMITEM.value('declare namespace bb=''bb_appfx_dataforms''; (/bb:DataFormItem/bb:Values/bb:fv[@ID=''DeleteTransactionalIntervalType'']/bb:Value)[1]', 'integer') = 2 then 'Month(s)'
                                                   when GLOBALCHANGEDATAFORMITEM.value('declare namespace bb=''bb_appfx_dataforms''; (/bb:DataFormItem/bb:Values/bb:fv[@ID=''DeleteTransactionalIntervalType'']/bb:Value)[1]', 'integer') = 4 then 'Day(s)' end)
      from dbo.GLOBALCHANGE
      where GLOBALCHANGECATALOGID='8C3ECDB1-BE50-4898-AD1B-98ECB6D07812'   --Global Change Spec ID for email services
  end
  else
  begin
      select top 1
      @DATALOADED = 1
      ,@GLOBALCHANGEID = null
      ,@DELETEGENERALPURPOSESTATUSOLDERTHAN = null
      ,@DELETEGENERALPURPOSEINTERVALTYPE = null
      ,@DELETETRANSACTIONALSTATUSOLDERTHAN = null
      ,@DELETETRANSACTIONALINTERVALTYPE = null
  end
    return 0;

end