USP_DATAFORMTEMPLATE_ADD_JOBOCCURRENCE_1_1

The save procedure used by the add dataform template "Job Occurrence Add Form 1.1".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@JOBID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@DESCRIPTION	nvarchar(30)	IN	Occurrence name
@DEPARTMENTCODEID	uniqueidentifier	IN	Department
@LOCATIONCODEID	uniqueidentifier	IN	Location
@TYPECODE	tinyint	IN	Type
@STARTDATE	datetime	IN	Start date
@ENDDATE	datetime	IN	End date
@STARTMONTHDAY	UDT_MONTHDAY	IN	Start day (mm/dd)
@ENDMONTHDAY	UDT_MONTHDAY	IN	End day (mm/dd)
@ONESTARTTIME	UDT_HOURMINUTE	IN	Start time
@ONEENDTIME	UDT_HOURMINUTE	IN	End time
@RECSTARTTIME	UDT_HOURMINUTE	IN	Start time
@RECENDTIME	UDT_HOURMINUTE	IN	End time
@DAYOFWEEKCODE	tinyint	IN	Day of week
@EVENTID	uniqueidentifier	IN	Event
@VOLUNTEERSNEEDED	int	IN	Volunteers needed
@COMMENTS	nvarchar(max)	IN	Comment
@SITEID	uniqueidentifier	IN	Site
@ESTIMATEDVALUE	money	IN	Estimated value
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@RECURRENCESTARTDATE	datetime	IN
@RECURRENCEENDDATE	datetime	IN

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_JOBOCCURRENCE_1_1]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @JOBID uniqueidentifier,
  @DESCRIPTION nvarchar(30),
  @DEPARTMENTCODEID uniqueidentifier = null,
  @LOCATIONCODEID uniqueidentifier = null,
  @TYPECODE tinyint = 0,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @STARTMONTHDAY dbo.[UDT_MONTHDAY] = '0000',
  @ENDMONTHDAY dbo.[UDT_MONTHDAY] = '0000',
  @ONESTARTTIME dbo.[UDT_HOURMINUTE] = null,
  @ONEENDTIME dbo.[UDT_HOURMINUTE] = null,
  @RECSTARTTIME dbo.[UDT_HOURMINUTE] = null,
  @RECENDTIME dbo.[UDT_HOURMINUTE] = null,
  @DAYOFWEEKCODE tinyint = 0,
  @EVENTID uniqueidentifier = null,
  @VOLUNTEERSNEEDED integer = 0,
  @COMMENTS nvarchar(max) = '',
  @SITEID uniqueidentifier = null,
  @ESTIMATEDVALUE money = 0,
  @CURRENTAPPUSERID uniqueidentifier,
  @RECURRENCESTARTDATE datetime = null,
  @RECURRENCEENDDATE datetime = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @STARTTIME dbo.[UDT_HOURMINUTE];
  declare @ENDTIME dbo.[UDT_HOURMINUTE];
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONESTIMATEDVALUE money;

  if @SITEID is null and dbo.[UFN_SITEREQUIREDFORUSER](@CURRENTAPPUSERID) = 1 
    begin
      raiserror('Site is required.', 13, 1);
      return 1;
    end

  if @ID is null set @ID = newid();

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

  set @CURRENTDATE = getdate();

  if @TYPECODE = 0 
    select @STARTMONTHDAY = '0000',
           @ENDMONTHDAY = '0000',
           @STARTTIME = @ONESTARTTIME,
           @ENDTIME = @ONEENDTIME,
           @DAYOFWEEKCODE = 0,
           @RECURRENCESTARTDATE = null,
           @RECURRENCEENDDATE = null;

  if @TYPECODE = 1 
    select @STARTDATE = null,
           @ENDDATE = null,
           @STARTTIME = @RECSTARTTIME,
           @ENDTIME = @RECENDTIME,
           @STARTMONTHDAY = '0101',
           @ENDMONTHDAY = '1231';

  if @TYPECODE = 2
    select @STARTDATE = null,
           @ENDDATE = null,
           @STARTMONTHDAY = '0000',
           @ENDMONTHDAY = '0000',
           @STARTTIME = @RECSTARTTIME,
           @ENDTIME = @RECENDTIME,
           @RECURRENCESTARTDATE = null,
           @RECURRENCEENDDATE = null;

  select @BASECURRENCYID = [BASECURRENCYID] from dbo.[JOB] where [ID] = @JOBID;
  set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
  set @ORGANIZATIONESTIMATEDVALUE = @ESTIMATEDVALUE;

  if @ORGANIZATIONCURRENCYID <> @BASECURRENCYID
    begin
      set @ORGANIZATIONEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
      set @ORGANIZATIONESTIMATEDVALUE = dbo.[UFN_CURRENCY_CONVERT](@ESTIMATEDVALUE, @ORGANIZATIONEXCHANGERATEID);
    end

  begin try
    insert into dbo.[JOBOCCURRENCE]
    (
      [ID],
      [JOBID],
      [DESCRIPTION],
      [TYPECODE],
      [STARTDATE],
      [ENDDATE],
      [STARTMONTHDAY],
      [ENDMONTHDAY],
      [STARTTIME],
      [ENDTIME],
      [DAYOFWEEKCODE],
      [EVENTID],
      [VOLUNTEERSNEEDED],
      [DEPARTMENTCODEID],
      [LOCATIONCODEID],
      [COMMENTS],
      [SITEID],
      [BASECURRENCYID],
      [ORGANIZATIONEXCHANGERATEID],
      [ESTIMATEDVALUE],
      [ORGANIZATIONESTIMATEDVALUE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [RECURRENCESTARTDATE],
      [RECURRENCEENDDATE]
    ) values (
      @ID,
      @JOBID,
      @DESCRIPTION,
      @TYPECODE,
      @STARTDATE,
      @ENDDATE,
      @STARTMONTHDAY,
      @ENDMONTHDAY,
      @STARTTIME,
      @ENDTIME,
      @DAYOFWEEKCODE,
      @EVENTID,
      coalesce(@VOLUNTEERSNEEDED, 0),
      @DEPARTMENTCODEID,
      @LOCATIONCODEID,
      @COMMENTS,
      @SITEID,
      @BASECURRENCYID,
      @ORGANIZATIONEXCHANGERATEID,
      @ESTIMATEDVALUE,
      @ORGANIZATIONESTIMATEDVALUE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      @RECURRENCESTARTDATE,
      @RECURRENCEENDDATE
    );
  end try

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

  return 0;