Transcript Victoria University

THEVictoria
VU University
AGENDA
EXCELLENT,
ENGAGED
AND
ACCESSIBLE
Alesco
Custom Business Rules
Outline
• Background
• Introduction to Custom Business Rules
• Prerequisites
• Set Up Custom Business Rules
• An Example
• Summary – Pros and Cons
Outline
 Background
• Introduction to Custom Business Rules
• Prerequisites
• Set Up Custom Business Rules
• An Example of Custom Business Rules
• Summary – Pros and Cons
Problem
• HR data is growing incredibly
• Data quality issues are always existing
• Inaccurate data result in errors in reports, WSS, and
so on
Solutions
At VU we are utilising different methods to monitor
and improve the data integrity/quality in Alesco:
• Regular Reports
• Email Notifications
post check
• Custom Business Rules
pre check, prevent
Outline
• Background
 Introduction to Custom Business Rules
• Prerequisites
• Set Up Custom Business Rules
• An Example of Custom Business Rules
• Summary – Pros and Cons
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
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
Outline
• Background
• Introduction to Custom Business Rules
 Prerequisites
• Set Up Custom Business Rules
• An Example of Custom Business Rules
• Summary – Pros and Cons
Prerequisites
Before creating business rules, you will need to:
1. Know the Alesco tables and fields
– Used to store Alesco data in the backend Oracle database.
2. 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
Outline
• Background
• Introduction to Custom Business Rules
• Prerequisites
 Set Up Custom Business Rules
• An Example of Custom Business Rules
• Summary – Pros and Cons
Steps
1. FA003 – Codes Maintenance
– Define the business rule name
2. FG377 –Business Rules Triggers
– Define when and where the rule is applied
3. FG376 – Business Rules Definitions
– Define what the rule does and how
Log into Alesco as a master user
Define The Rule Name
FA003 – Codes Maintenance
Kind = BUSINESS_RULE
Business rule code name
and description
Where/When The Rule Applies
FG377 – Business Rules Triggers
1.(a) What data to
verify - Table name
1.(b) What data to
verify - Field name
3. How to verify the
Data – Delegate the
verification task to the
business rule
2. When to verify
the data
Where/When The Rule Applies
FG377 – Business Rules Triggers
Quick
summary:
The
predefined parameters passing to the rule:
1. Define
a trigger – what
to verify and when
1. p_table_name
anddata
p_column_name
2. Specify
theoflinked
business
rule the
– delegate
theis actual
– name
table and
column where
business rule
applied verification
task
the rule
2. to
p_code_col_value
the value ofpending
the column
where the–rule
is applied
3. One –important
question
How
does the trigger pass the
data
the delegated business rule?
3. to
p_messages
– OUT parameter returning the error message if the business rule
validation fails.
4.
p_employee# and p_job#
– populated when the table has an employee or job column
What The Rule Does and How
FG376 – Business Rules Definitions
Oracle’s built-in function which
Business
rule to (only
converts a string
to uppercase
defineare applicable)
alpha characters
New Position title
being updated or
inserted
IF UPPER(p_code_col_value) <> p_code_col_value
THEN
p_messages := 'Using lower case characters in position tile
is not allowed.';
The error message shown to
END IF;
the alesco user if the rule
Define
the verification
validation
fails
logic of the business
rule
What The Rule Does and How
FG376 – Business Rules Definitions
Define additional parameters
System generated package,
Can be ignored
Pre-defined values for conditional verification
Outline
• Background
• Introduction to Custom Business Rules
• Prerequisites
• Set Up Custom Business Rules
 An Example of Custom Business Rules
• Summary – Pros and Cons
Changing Position tile
Warning !! There are two lowercase
characters in the
position title.
Outline
• Background
• Introduction to Custom Business Rules
• Prerequisites
• Set Up Custom Business Rules
• An Example of Custom Business Rules
 Summary – Pros and Cons
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
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
THE VU
Questions??
AGENDA
EXCELLENT,
ENGAGED
AND
ACCESSIBLE
The
End! Thanks for listening!