Manager Time Approval Preview Custom Business Rules (Stored Procedure)

Please see this page first - Custom Business Rules (Stored Procedures) - for details common to all stored procedures, then read this page for details specific to Manager Time Approval Preview Custom Business Rules.

As an optional feature, the Manager’s version of the timesheet approval preview screen can include the display of custom messages that appear in a blue informational text block at the top of the preview screen. These messages may contain additional details of which the current user would like to make the Manager aware. The details of the custom message are generated by the execution of a custom stored procedure. This stored procedure is invoked in a similar manner as our timesheet stored procedure.  This stored procedure does not prevent the approval of the timesheet but simply provides for the ability to display a custom message. This stored procedure is only accessed from the manager timesheet approval preview page.

You invoke the feature by enabling the Manager Time  Approval Preview Stored Procedure (unatime.manager.approval.preview.stored_procedure) property. This property is used to define the name of a database stored procedure that should be called when managers preview a timesheet during approval. These messages are only viewable to managers, or alternate managers, during timesheet approval.

 

This page covers the following topics:

 


Syntax

<stored_procedure_name> (person_time_key IN  number(15,0),
                         message   OUT varchar(2000))

 

Note: The output message's size will depend on your database field size limit.

The following rules must be followed when using this feature:

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 Manager Time  Approval Preview Stored Procedure (unatime.manager.approval.preview.stored_procedure) property.

For example: unatime.manager.approval.preview.stored_procedure=sp_approval_process

Input Parameters

 

  • Person Time Key

Identifies the specific timesheet being previewed.

This is a required input parameter.

Output Parameters

 
  • Message

The output parameter contains the message to display.  

 


Examples

The following sample stored procedures could be used to display a message on the timesheet approval preview page.  There are two versions to illustrate the syntax for both Oracle and SQL Server.

*For specific information regarding the creation and maintenance of stored procedures, please refer to your database specific documentation.

Oracle Stored Procedure

create or replace procedure sp_approval_process(
    p_person_time_key in number,
    p_message out varchar)
as
    p_quantity number;
begin
    p_message := null;
    select sum(quantity)
    into p_quantity
    from person_time_data
    where person_time_key = p_person_time_key;
    if p_quantity > 40
    then
        p_message := 'This user has worked more than 40 hours.<br>  Please use Unanet planning and forecasting features to optimize the user''s work!';
    end if;
end;
/
grant all on sp_approval_process to unanet
/

 

SQL Server Stored Procedure

if exists(select name from sysobjects where name = 'sp_approval_process' AND type = 'P')
    drop procedure sp_approval_process
go
create procedure sp_approval_process
    @personTimeKey decimal(15,0),
    @message varchar(2000) output
as
    select @message = null;
    declare @hours decimal(15,2)
    select @hours = sum(quantity)
    from person_time_data
    where person_time_key = @personTimeKey
    if (@hours > 40)
    begin
    select @message = 'This user has worked more than 40 hours.<br>'
    + 'Please use Unanet planning and forecasting features to optimize the user''s work!'
    end
go
grant all on sp_approval_process to unanet

 

Related Topics