Expense Report - Custom Business Rules via Save / Submit Validations (Stored Procedure)

<stored_procedure_name> (expense_report_key IN  number(15,0),
                         submitter_key      IN  number(15,0),       (use optional submitter_key with submit validations and saver_key with save validations)
                         return_code        OUT number(15,0),
                         error_message      OUT varchar(2000))

Stored Procedure Name

The name of the store procedure is defined by the customer.  This name must match the value that is set with the Submit Stored Procedure (unasense.submit.stored_procedure) property or Save Stored Procedure (unasense.save.stored_procedure) property.  

For example: unasense.submit.stored_procedure=sp_expense_submit_validation.

Note: Be careful to not use the same name that you may have used for the timesheet stored procedure if you have one already installed.

Input Parameters

• Person Expense Key

Identifies the specific expense report being saved or submitted.

This is a required input parameter.

• Submitter Key, or

• Saver Key

Identifies the key of the user saving or submitting the expense report.

This is a optional input parameter.

Note:  You should only pass this optional parameter if the stored procedure logic requires the additional input data.  When passing this optional parameter, you must also enable the corresponding   Include Submitter when calling Submit Stored Procedure (unasense.submit.stored_procedure.include_submitter) or  Include Saver when calling Save Stored Procedure (unasense.save.stored_procedure.include_saver) property (as this property instructs the stored procedure call to expect the additional parameter).  

Requiring the additional parameter and not providing it -- or -- providing the additional parameter while not setting the property to expect it will both result in a sql exception at runtime.

• Return Code

The first output parameter is expected to be the return code from the stored procedure.  

• A value of 0 (zero) will indicate success
• A positive value will indicate an Error condition
• A negative value will indicate a Warning condition.  

When using the 'submit' validation (vs. the 'save' validation); in the case that a zero (0) (success) is returned, the Unanet submit logic will continue.  In the case of a non-zero return code (Error or Warning), the contents of the second output parameter (Error Message) will be displayed to the screen and the submit logic will not continue.  In the case of the Warning message, the user is subsequently presented with an option to either re-edit the expense report, or to proceed with the submittal.

• Error Message

The second output parameter is expected to contain a message to accompany the non-zero return code, presumably to explain the reason for failure.  If a non-zero return code is returned, but no value is supplied in the message parameter, Unanet will supply a default message indicating no custom message provided.  

--

-- Create the validation procedure.

-- The procedure checks if a non-employee has charged expense to the type AIR.

-- If true, then an error is presented to the user.

--

CREATE OR REPLACE procedure sp_submit_validation_expense(

   expenseReportKey in number,

   returnCode out number,

   errorMessage out varchar)

as   

nonEmployeeCount number;

nonExemptCount number;

expTypeKey number;

begin

returnCode := 0;

errorMessage := null;

select expense_type_key into expTypeKey from expense_type where expense_type = 'AIR';

select count(*) into nonEmployeeCount

   from expense_report er

   join expense_data ed on ed.expense_report_key = er.expense_report_key

   join person_rate pr on pr.person_key = er.owner_key

   where er.expense_report_key = expenseReportKey

     and ed.expense_type_key = expTypeKey

     and pr.exempt_status = 'X'

     and ed.expense_date between pr.begin_date and pr.end_date;

select count(*) into nonExemptCount

   from expense_report er

   join expense_data ed on ed.expense_report_key = er.expense_report_key

   join person_rate pr on pr.person_key = er.owner_key

   where er.expense_report_key = expenseReportKey

     and ed.expense_type_key = expTypeKey

     and pr.exempt_status = 'N'

     and ed.expense_date between pr.begin_date and pr.end_date;

if nonEmployeeCount > 0

then

      returnCode := 1;

      errorMessage := 'Non-employees cannot charge expenses for expense type AIR.';      

else

   if nonExemptCount > 0

   then

      returnCode := -1;

      errorMessage := 'WARNING: Non-exempt employee can only charge preapproved expenses for expense type AIR.';

   end if;

end if;

end;

/

grant all on sp_submit_validation_expense to unanet
/

SET QUOTED_IDENTIFIER OFF

-- Drop the stored procedures if they already exist.

--

if exists(select name from sysobjects where name = 'sp_submit_validation_expense' AND type = 'P')

   drop procedure sp_submit_validation_expense

go

--

-- Create the validation procedure.

-- The procedure checks if a non-employee has charged expense to the type AIR.

-- If true, then an error is presented to the user.

--

create procedure sp_submit_validation_expense

   @expenseReportKey decimal(15,0),

   @returnCode    decimal(15,0) output,

   @errorMessage  varchar(2000) output

as

   declare @nonEmployeeCount decimal(15,0);

   declare @nonExemptCount decimal(15,0);

   declare @expTypeKey decimal(15,0);

   set @returnCode = 0;

   set @errorMessage = '';

   select @expTypeKey = expense_type_key from expense_type where expense_type = 'AIR';

   select @nonEmployeeCount = count(*)

   from expense_report er

   join expense_data ed on ed.expense_report_key = er.expense_report_key

   join person_rate pr on pr.person_key = er.owner_key

   where er.expense_report_key = @expenseReportKey

     and ed.expense_type_key = @expTypeKey

     and pr.exempt_status = 'X'

     and ed.expense_date between pr.begin_date and pr.end_date;

   select @nonExemptCount = count(*)

   from expense_report er

   join expense_data ed on ed.expense_report_key = er.expense_report_key

   join person_rate pr on pr.person_key = er.owner_key

   where er.expense_report_key = @expenseReportKey

     and ed.expense_type_key = @expTypeKey

     and pr.exempt_status = 'N'

     and ed.expense_date between pr.begin_date and pr.end_date;

   if(@nonEmployeeCount > 0 )

   begin

      set @returnCode = 1

      set @errorMessage = 'Non-employees cannot charge expenses for expense type AIR.'

   end

   else if(@nonExemptCount > 0 )

   begin

      set @returnCode = -1

      set @errorMessage = 'WARNING: Non-exempt employee can only charge preapproved expenses for expense type AIR.'

   end

go

grant all on sp_submit_validation_expense to unanet