Victoria University

1. Victoria University THE VU AGENDA EXCELLENT, ENGAGED AND ACCESSIBLE Alesco Custom Business Rules

2. Outline • Background • Introduction to Custom Business Rules • Prerequisites • Set Up Custom Business Rules • An Example • Summary – Pros and Cons

3. Outline • Background • Introduction to Custom Business Rules • Prerequisites • Set Up Custom Business Rules • An Example of Custom Business Rules • Summary – Pros and Cons

4. Problem • HR data is growing incredibly • Data quality issues are always existing • Inaccurate data result in errors in reports, WSS, and so on

5. Solutions At VU we are utilising different methods to monitor and improve the data integrity/quality in Alesco: • Regular Reports • Email Notifications • Custom Business Rules post check pre check, prevent

6. Outline • Background • Introduction to Custom Business Rules • Prerequisites • Set Up Custom Business Rules • An Example of Custom Business Rules • Summary – Pros and Cons

7. Definition What exactly is a custom business rule? • A functionality in Alesco used for data validation • Implemented through Oracle triggers at the database level • Proactively prevents data entry errors from being entered

8. Scenarios Some scenarios this functionality is used for at VU: • Prevent expired Clevels from being used • Limit the total number of hours claimed in the online timesheet to 12 hours per day • Limit the number of days which can be claimed for certain leave codes in WSS

9. Outline • Background • Introduction to Custom Business Rules • Prerequisites • Set Up Custom Business Rules • An Example of Custom Business Rules • Summary – Pros and Cons

10. Prerequisites Before creating business rules, you will need to: • Know the Alesco tables and fields • Used to store Alesco data in the backend Oracle database. • Have a sound knowledge of Oracle SQL and PL/SQL • Used to manipulate Alesco data directly without using the frontend interface • PL/SQL is more powerful and complex than SQL • Typical tools include SQL Plus, TOAD, SQL Developer, etc. • Data operations should be limited to QUERY only

11. Outline • Background • Introduction to Custom Business Rules • Prerequisites • Set Up Custom Business Rules • An Example of Custom Business Rules • Summary – Pros and Cons

12. Steps • FA003 – Codes Maintenance • Define the business rule name • FG377 –Business Rules Triggers • Define whenand wherethe rule is applied • FG376 – Business Rules Definitions • Define what the rule does and how • Log into Alesco as a master user

13. Define The Rule Name FA003 – Codes Maintenance Kind = BUSINESS_RULE Business rule code name and description

14. Where/When The Rule Applies FG377 – Business Rules Triggers 1.(a) What data to verify - Table name 2. When to verify the data 1.(b) What data to verify - Field name 3. How to verify the Data – Delegate the verification task to the business rule

15. Where/When The Rule Applies FG377 – Business Rules Triggers Quick summary: • Define a trigger – what data to verify and when • Specify the linked business rule – delegate the actual verification task to the rule • One important pending question – How does the trigger pass the data to the delegated business rule? The predefined parameters passing to the rule: • p_table_nameand p_column_name – name of table and column where the business rule is applied • p_code_col_value – the value of the column where the rule is applied • p_messages – OUT parameter returning the error message if the business rule validation fails. • p_employee# and p_job# – populated when the table has an employee or job column

16. What The Rule Does and How IF UPPER(p_code_col_value) <> p_code_col_valueTHEN p_messages:= 'Using lower case characters in position tile is not allowed.'; END IF; FG376 – Business Rules Definitions The error message shown to the alesco user if the rule validation fails Business rule to define New Position title being updated or inserted Oracle’s built-in function which converts a string to uppercase (only alpha characters are applicable) Define the verification logic of the business rule

17. Define additional parameters What The Rule Does and How FG376 – Business Rules Definitions System generated package, Can be ignored Pre-defined values for conditional verification

18. Outline • Background • Introduction to Custom Business Rules • Prerequisites • Set Up Custom Business Rules • An Example of Custom Business Rules • Summary – Pros and Cons

19. Changing Position tile Warning !! - There are two lowercase characters in the position title.

20. Outline • Background • Introduction to Custom Business Rules • Prerequisites • Set Up Custom Business Rules • An Example of Custom Business Rules • Summary – Pros and Cons

21. Summary Pros • Can be used for a huge variety of scenarios, e.g. Clevel, position titles character caps, date of birth caps, upper case and lower case controls etc. • Easy to set up if you have good SQL and Alesco tables knowledge

22. Summary Cons • The error message returned for the user is not ‘nice’ looking, staff need to be instructed on how to view the error message • Does not work for complex business logics i.e. it cannot be used for multiple tables in one rule

23. Questions?? THE VU AGENDA EXCELLENT, ENGAGED AND ACCESSIBLE The End! Thanks for listening!