- Increase Efficiency (both technically and functionally)
- Increase Accuracy
- Maintain Flexibility
Each of the areas that can "house" business logic will be evaluated using these three criteria. Now that that's out of the way, lets talk terminology. In Argos, there are three main arenas that can be used to "contain" business logic: Reports, DataBlocks, and the Database (in the form of stored functions and procedures). Generally speaking, as your organization matures, you'll want to move further away from storing your business logic at the report level.
The three goals stated above are distinctly effected the further from the DataBase you are. For example:
Let's say we're trying to report current, clerical employees. Well, it wouldn't be difficult to write a query that retreived all employees:
select pebempl_pidm
from pebempl;
Not bad! From a DataBlock design standpoint, this is really easy! However, that puts the responsability for finding current clerical employees on the Report writer, forcing them to add whatever conditions identify such employees at the report level. For the sake of argument, let's assume that an employee class equal to 'CL' and a status of 'A' indicate a current, clerical employee. This means the report writer would have to hard code those conditions into the report object to get the appropriate information.
Now, imagine there are 50 different employee related reports. Each one of those reports must now be maintained and filtered according to whatever subgroup of employees the report writer is looking for. Each of those reports is now serving a single purpose. Additionally, the logic loses a performance edge as it has to be run each time the individual reports are invoked, which doesn't allow the code to take advantage of some of Oracle's built in performance engines (buffer, cache, etc).
So, let's imagine removing that logic from the report level and giving the user a toggle in the datablock that indicates if they want current clerical employees or not. This is good because we can then use a modified query:
select pebempl_pidm
from pebempl
where pebempl_ecls_code = :ecls
and pebempl_status = :status;
This now gives the report writer further flexibility, allowing them to choose different options with which to run a single report; a single report "template" can be used for any combination of status and employee class! This is quite a bit more flexible (as you can see), however, you're still lacking some of the performance benefits of server side code, so the final step would be to move the logic to the database in the form of a stored function! In that case, the query in the DataBlock might look like this:
select pebempl_pidm
from pebempl
where f_include_employee(pebempl_pidm,'flag1','flag2') = 'Y';
In this case, the function f_include_employee() does all the work and simply returns a Y or N to let the system know whether to include the employee or not. This is extensible, reusable code that only has to be written once. Additionally, it is pre-compiled and stored in oracle so it fully utilizes oracle's performance enhancing rules! The code inside the function is completely up to the institution and can contain whatever logic you wish! If you'd like an example of some functions that may be useful, let me know! Happy Hunting!
Posts
