This section describes the PL/SQL interface for the GB balance function that enables you to access balance values for inquiry and reporting tools.

Advantages

Using this PL/SQL function to retrieve balance values has several advantages:

• You can easily call the function from a form or report.

• The function accesses latest balance values, where they exist.

  This optimizes performance automatically.

• You can call the function from a user-defined view.

  This is because the function has pragma levels WNPS and WNDS set (Write No Package State, and Write No DML).

The interface to the function is flexible and easy to use. Hard coded knowledge of contexts within the function are kept to a minimum and the balance function is controlled as follows:

• The GB Balance User Exit works in two modes: date and assignment action mode.

  For the balance function the interface is simplified using the PL/SQL overloading feature. The same function name is used, but different parameters are passed in according to the mode. If in Date Mode, the function calculates the values using the assignment action previous to the date passed in. The value obtained is checked to make sure it hasn't expired between the assignment action that it represents and the date passed in.

• The function uses the rubric of 'quickest value first'.

  If a value can be retrieved from the latest balances table, it will be. This is so that performance of the code is optimized.

Although one interface is used to directly call the GB Balance value function, the PL/SQL code resides in three packages:

• hr_dirbal

• hr_gbbal

• hr_routes

Function: Get balance value (Assignment Action mode):

function get_balance (p_assignment_action_id	in	number,
					p_defined_balance_id	in	number)

return number;

Function: Get balance value (Date mode):

function get_balance (p_assignment_id		in	number,
					p_defined_balance_id	in	number,
					p_effective_date	in 	date)
return number;

The balance value is returned by these functions. The parameters required for the function have been kept to a minimum.

Example

Supposing we take an assignment action id of 12345 and a defined balance id of 111:

l_balance := hr_dirbal.get_balance (12345, 111); 

This would return a balance value, using the Assignment Action mode call to the package.

Supposing we take an assignment id of 2, the same defined balance id, and an arbitrary date:

l_balance := hr_dirbal.get_balance (2,111,to_date('01/01/1998','DD/MM/YYYY'));

This would return a balance value, using the Date mode call to the package.