Wiki: PTS_CHCK_ROUTINE(System

PL/SQL validation routine can be called to replace the default "submit" function on a timesheet. When a timesheet is submitted, if the timesheet does not meet the criteria of the validation routine, a customer defined message will appear as a pop up dialog and the timesheet will not change status.

Required Set up#

Maintain System Messages (IMMS)#

A user defined system message must be created on IMMS. The message should be prefixed with PTSV_###### When the PL/SQL function is created, the return value will be the ##### portion of the IMMS code. The "PTSV_" will be assumed by the process calling the custom code.

Function Definition #

The function must be defined in the data base as a PL/SQL function.

The PL/SQL function must receive exactly one parameter, the ID of the P2K_PR_TIME_SHEETS record to be examined
The function must return either a NULL or the number portion of a code name of a user defined message defined in the IMMS screen.
Best Practices and Recommendations
- The function should be added to the P2K schema (i.e. the function is created by the P2K user) so that it is carried forward in any export/import operations.
- The function name should NOT start with P2K_ so as to preclude any confusion about its source (P2K_xxxx is reserved for those supplied with the application).
- Your function names start with an identifiable code that will distinguish this as yours (i.e. if your company is Acme Corp, call the functions you create ACME_xxxx)
- If you are going to have many conditions that will require validation, you may find it advantageous to create a package with your customer prefix, and then include all the different validation routines as seperate functions within that package. This keeps all your work together in one place.
- It is important to ensure that all error conditions are trapped or any errors that arise may cause unexpected or unexplainable screen errors.
- It is important to ensure that a public synonym is created and grants made to the P2K_USER or users will get unexpected results in the screens where you may be using them.

Form Definition (IMFDH)#

Once the function and system message has been established, all the functions that will use the routine must be set up. There are two steps required on each function in which the routine is to be called 1) On the Function Info tab, add the preference "PTS_CHK_ROUTINE". The value will be the name of the Function or Package to be called. 2) On the Form Layout tab, add the PTS.ACT_VALIDATE_SUBMIT_TIME_SHEET_NO_DLG to the form. If the form is "Pre-Loaded" use object security to remove the default PTS.ACT_SUBMIT_TIME_SHEET option (If custom, the PTS.ACT_SUBMIT_TIME_SHEET can be delete from the form).

Example#

In this example employees must account for all the time in their timesheet based on their hours per pay set up on IEAS.
If the total number of hours in the timesheet is less than the number defined on the IEAS, the function will return the number "0001".
On IMMS a user defined system message has been created as "PTSV_0001" and contains the message "The hours in your timesheet do not match your scheduled hours. Please make to account for all daily hours."

The following function will take the ID of the P2K_PR_TIME_SHEETS record and return a null or the value on IMMS for the MESSAGE_CODE of "0001". Any exceptional condition (no records found, etc.) will result in NULL being returned.

 CREATE OR REPLACE
  FUNCTION ACME_PTS_SCHED_HOURS(P_PTS_ID IN NUMBER) 
    RETURN VARCHAR2 AS
    --Return system message 0001 if the TS total is less than 40.
    
    v_return        VARCHAR2(60);
    v_pts_id        NUMBER(10):=P_PTS_ID;
    v_total_hrs     NUMBER(10,2);
  
  BEGIN
    SELECT SUM(TIME_OR_AMOUNT)
    INTO v_total_hrs
    FROM p2k_PR_TIME_SHEET_ENTRIES
    WHERE TIME_SHEET_ENTRY_SOURCE <> '05'
    AND PTS_ID = v_pts_id
    AND DTC_ID IN 
        (SELECT DTCD.DTC_ID
          FROM P2K_CM_TIME_CODE_SETS DTCS, P2K_CM_TIME_CODE_SET_DETAILS DTCD
          WHERE DTCS.TIME_CODE_SET_CODE = 'TIME_SHEET_TOTALS'
          AND DTCS.ID = DTCD.DTCS_ID);
          
    IF v_total_hrs > 40 
        THEN v_return:='0001';
    END IF;
    
    RETURN v_return;
    
    EXCEPTION
        WHEN OTHERS THEN
        RETURN '0000';
  END ACME_PTS_SCHED_HOURS;
/

The two slashes are an indicator to SQL to execute the lines preceding.

The creation of a Public Synonym means that users other than P2K (the schema owner) will have access to the function. The grant of the execute privilege is required so that other users (other than P2K) will be able to execute this. All users of the application must have the P2K_USER role granted in IMUS or IMUR.

create public synonym ACME_PTS_SCHED_HOURSfor ACME_PTS_SCHED_HOURS;
grant execute on ACME_PTS_SCHED_HOURSto P2K_USER;

More User Formula Examples

This function can be tested with the following select statements:

SELECT ACME_PADAYS(12345) FROM DUAL; where 12345 is the ID of an employee's EMPLOYMENT record (IEEI)
SELECT ACME_PADAYS(ID) FROM P2K_HR_EMPLOYMENTS WHERE ...etc. will take the ID straight from the table.

Table Definition (IMCD)#

Once we have the function defined and working, we can extend the table (in our example P2K_HR_EMPLOYMENTS) to include this User Formula Column. This is done in the IMCD screen by adding a new column with the following fields filled in:

Sequence: It is recommended that you start your sequencing at 9000 and work down, so as to not collide with provided sequence numbers

Column Name: The name you wish to refer to this column in screen design. It is recommended that you start your column name with the same identifier as your function, so as to preclude confusion with provided values. (i.e. ACME_xxxx)

Prompt: This is optional, but recommended. Any prompt you provide here will be used in screen layouts, but can always be overridden at each usage.

Column Usage: Must be User Formula

Data Type: Must be the same type that the function returns (Number, Date, Varchar2)

Domain Name: Must be of the type indicated by the function return (DATE for date fields; VARCHARnn for character fields (e.g. VARCHAR30); NUMBERn for whole number fields; AMTnnVn for numeric fields with decimals, must be one of the standard set.

Length: Optional, but recommended

Format: Optional, but may be used for date or number variables

User Formula (Defaulted From): Must contain the name of the PL/SQL Function

Note that once the field has been added to the table, you will not see this new field right away as this information is key to the application and thus will only be picked up on an instance restart.

Restart the application instance.

Screen Definition (IMFD)#

Now that we have a field on the table that will return to us the value we wish, we can add this field to any form that has access to the ID referred to in the function / table definition. This is performed on the IMFD screen's form layout tab.

It is important to note that any such fields added, must be added with:

Item Type: Display Only so end users are not given the impression that they can update this value on the screen indicated.

Item Usage: User Defined so it is not overlaid by a subsequent release.

Notes #

Click to create a new notes page