USP_DATAFORMTEMPLATE_VIEW_IMPORTPROCESS_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_IMPORTPROCESS_2
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @SCHEDULINGFREQUENCY int = null output,
    @BATCHTEMPLATE nvarchar(255) = null output,
  @FILEMAPPINGTEMPLATE nvarchar(250) = null output,
  @FILENAME nvarchar(255) = null output,
    @CREATEDON datetime = null output,
  @TIMESRUN int = null output,
  @BATCHESCREATED int = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select @DATALOADED = 1,
    @BATCHTEMPLATE = BATCHTEMPLATE.NAME,
    @FILEMAPPINGTEMPLATE = coalesce(IMPORTFILETEMPLATE.NAME, IMPORTFIXEDWIDTHFILETEMPLATE.NAME),
    @FILENAME = IMPORTPROCESS.FILENAME,
    @CREATEDON = IMPORTPROCESS.DATEADDED
    from dbo.IMPORTPROCESS
    inner join dbo.BATCHTEMPLATE on IMPORTPROCESS.BATCHTEMPLATEID = BATCHTEMPLATE.ID
  left join dbo.IMPORTFILETEMPLATE on IMPORTPROCESS.DELIMITEDFILETEMPLATEID = IMPORTFILETEMPLATE.ID
  left join dbo.IMPORTFIXEDWIDTHFILETEMPLATE on IMPORTPROCESS.FIXEDWIDTHFILETEMPLATEID = IMPORTFIXEDWIDTHFILETEMPLATE.ID
    where IMPORTPROCESS.ID = @ID;

  select @SCHEDULINGFREQUENCY = 
    case when (select count(1)
               from msdb.dbo.sysschedules 
               inner join msdb.dbo.sysjobschedules on sysschedules.schedule_id = sysjobschedules.schedule_id
               inner join msdb.dbo.sysjobsteps on sysjobschedules.job_id = sysjobsteps.job_id
               inner join msdb.dbo.sysjobs on sysjobschedules.job_id = sysjobs.job_id
               inner join msdb.dbo.sysproxies on sysjobsteps.proxy_id = sysproxies.proxy_id               
               where sysproxies.name like 'Blackbaud % Proxy - ' + db_name()
               and sysjobsteps.step_name = 'Business process invoke'
               and sysjobsteps.command like '%' + cast(@ID as nvarchar(36)) + '%') > 1 then 0
     else (select sysschedules.freq_type
           from msdb.dbo.sysschedules 
           inner join msdb.dbo.sysjobschedules on sysschedules.schedule_id = sysjobschedules.schedule_id
           inner join msdb.dbo.sysjobsteps on sysjobschedules.job_id = sysjobsteps.job_id
           inner join msdb.dbo.sysjobs on sysjobschedules.job_id = sysjobs.job_id
           inner join msdb.dbo.sysproxies on sysjobsteps.proxy_id = sysproxies.proxy_id
           where sysproxies.name like 'Blackbaud % Proxy - ' + db_name()
           and sysjobsteps.step_name = 'Business process invoke'
           and sysjobsteps.command like '%' + cast(@ID as nvarchar(36)) + '%') 
     end

  select @TIMESRUN = count(1) 
  from dbo.BUSINESSPROCESSSTATUS
  inner join dbo.IMPORTPROCESSSTATUS on BUSINESSPROCESSSTATUS.ID = IMPORTPROCESSSTATUS.ID
  where IMPORTPROCESSSTATUS.PARAMETERSETID = @ID

  set @BATCHESCREATED = 
    (select count(1)
     from dbo.IMPORTPROCESSBATCH
     inner join dbo.BUSINESSPROCESSSTATUS on IMPORTPROCESSBATCH.BUSINESSPROCESSSTATUSID = BUSINESSPROCESSSTATUS.ID
     inner join dbo.IMPORTPROCESSSTATUS on BUSINESSPROCESSSTATUS.ID = IMPORTPROCESSSTATUS.ID
     where IMPORTPROCESSSTATUS.PARAMETERSETID = @ID) +
    (select count(1)
     from dbo.BATCH
     inner join dbo.IMPORTPROCESSBATCH on BATCH.ORIGINATINGBATCHID = IMPORTPROCESSBATCH.BATCHID
     inner join dbo.BUSINESSPROCESSSTATUS on IMPORTPROCESSBATCH.BUSINESSPROCESSSTATUSID = BUSINESSPROCESSSTATUS.ID
     inner join dbo.IMPORTPROCESSSTATUS on BUSINESSPROCESSSTATUS.ID = IMPORTPROCESSSTATUS.ID
     where IMPORTPROCESSSTATUS.PARAMETERSETID = @ID and IMPORTPROCESSBATCH.BATCHCOMMITOPTIONCODE in (2, 3))

    return 0;