Balances in Oracle Payroll

Balances in Oracle Payroll

This essay deals with the definition and use of balances and balance dimensions in Oracle Payroll. It also explains how to deal with the issue of loading initial balances. This essay does not provide any detail on how to add balance dimensions to the system.

Terms

This essay assumes that you are already familiar with the database design diagrams and tables contained in the Oracle HRMS Technical Reference Manual.

If you are not already familiar with the setup and use of balances, or the concepts of employee assignment, assignment actions, database items, or payroll processing in Oracle FastFormula you should refer to your Oracle HRMS user guides for more information.

For additional information on how the Payroll Run processes balances, see also: Payroll Run Process - Create and Maintain Balances.

Overview of Balances

In Oracle Payroll a balance is defined as the accumulation of the results of a payroll calculation. The balance has a name, feeds and dimensions.

For example, the balance GROSS PAY is the accumulation of the results of processing all `Earnings'. However, the idea of a dimension is unique to Oracle Payroll. Dimensions enable you to view the value of a balance using a combination of different criteria. So, you might want to view the value of Gross Pay for one employee for the current pay period, or for the year to date. The actual balance and dimension you would use in a formula or a report would be the GROSS_PAY_ASG_PTD or the GROSS_PAY_ASG_YTD.

In general, balances in Oracle Payroll can be thought of as the `calculation rules' for obtaining the balance value. Most values are not held explicitly in the database. This approach has many advantages: New balances can be defined and used at any time with any feeds and dimensions; balance values do not need to be stored explicitly in the database, taking up valuable storage space and causing problems with data archiving and purging.

Balance Types

These are the balance names, for example Gross Pay and Net Pay. Balance types always have a numeric Unit Of Measure, and in some instances a currency code.

Balance Feeds

Balance feeds define the input values that contribute to a balance. For example the pay values of all earnings types contribute to the Gross Pay balance. Feeds can add to (+) or subtract from (-) a balance

Balance Dimensions

The balance dimension is identified by the database item suffix for the balance. For example, '_YTD' indicates the balance value is for the year to date. Balance dimensions are predefined in Oracle Payroll.

Defined Balances

The defined balance is the name used to identify the combination of Balance Type and Balance Dimension. For example, GROSS_PAY_ASG_YTD. When you use the Balance window to define a new balance, Oracle Payroll automatically generates database items for every balance dimension you select. You can then access the value directly within any formula. In any detailed calculation or report on balances you always refer to the `defined balance' to return a value.

Latest Balances

To optimize the performance of payroll processing, some balance values are held explicitly in the database and these are referred to as Latest Balance Values. The payroll process accesses and updates latest balance values as it runs. In some cases it clears and then resets values, for example when you do a rollback. All of this is invisible to the user and is managed by the payroll process.

Note: If you need to return the value of a balance in a report you should use the balance function pay_balance_pkg.get_value. See: Including Balance Values in Reports.

Expiry

An important concept for latest balances is that of `expiry'. For example, consider the GROSS_PAY_YTD balance. When you cross the tax year boundary you would expect the value to return to zero. This `expiry' of a balance is maintained internally by Oracle Payroll and there is code to work out if we have crossed such a boundary.

Important: Even if a defined balance has expired in theory for a payroll run, it is not actually zeroed on the database unless it is subsequently updated by the same payroll run. Thus, following a Payroll Run, you may well see balances that you would have expected to have expired, but have their old values.

Balance Contexts

There is occasionally a requirement to report balances where the combination of ASSIGNMENT_ACTION_ID and BALANCE_TYPE_ID does not uniquely identify the individual balance values that should be reported. For example in the US legislation you need to maintain balance dimensions for particular states, while in the UK legislation you need to maintain balance dimensions for distinct tax offices.

Both of these requirements are met by the definition of special balance contexts. These are legislative specific 'C' code and appear to you as part of the balance dimensions.

User definition of additional balance contexts is not yet supported because of the major impact these may have on the overall performance of the payroll process. Bad code in the definition of these contexts can run exceptionally slowly, especially when you accumulate a large number of run results.

Context Balances - a UK Example

To report on context balances, we must define the relevant balances with the ELEMENT_PTD and ELEMENT_ITD dimensions. The further context that is required to identify the values is taken from the PAY_RUN_RESULTS.SOURCE_ID. This is obtained from the balance feed joining to the PAY_RUN_RESULT_VALUES table, then to PAY_RUN_RESULTS.

Using this value, we can select via the PAY_ASSIGNMENT_LATEST_BALANCES -> PAY_BALANCE_CONTEXT_VALUES method. Or, if there is no latest balance, by the route code call, which in the UK can be done with a function call:

hr_gbbal.calc_element_ptd_bal(ASSIGNMENT_ACTION_ID, 

                              BALANCE_TYPE_ID, 

                              SOURCE_ID); 

(or calc_element_itd_bal with the same parameters).  

Balance Dimensions

This essay describes what a balance dimension is and what it does, and how the various parts interact with formulas and the Payroll Run.

A balance dimension defines how the value of a specific balance should be calculated. The balance dimension is also an entity with its own attributes that are associated with balance calculations.

Database Item Suffix

The database item suffix identifies the specific dimension for any named balance. The `defined balance' name is the combination of the balance and the suffix. For example, the suffix '_ASG_YTD' in 'GROSS_SALARY_ASG_YTD' identifies that the value for the gross salary balance is calculated for one assignment, for the year to date.

Routes

The balance dimension route is a foreign key to the FF_ROUTES table. A route is a fragment of SQL code that defines the value to be returned when you access a balance. As with other database items, the text is held in the DEFINITION_TEXT column of the FF_DATABASE_ITEMS table.

The select clause of the statement is always:

select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)

Thus, a balance could be defined as the sum of those run result values that feed the balance type (`Gross Salary' in our example), across a certain span of time (in our example, this is since the start of the current tax year).

The SQL statement itself must follow a number of rules, and an example appears below:

        pay_balance_feeds_f      FEED
       ,pay_run_result_values    TARGET
       ,pay_run_results          RR
       ,pay_payroll_actions      PACT
       ,pay_assignment_actions   ASSACT
       ,pay_payroll_actions      BACT
       ,pay_assignment_actions   BAL_ASSACT
where  BAL_ASSACT.assignment_action_id = \&B1
and    BAL_ASSACT.payroll_action_id    = BACT.payroll_action_id
and    FEED.balance_type_id            = \&U1
and    FEED.input_value_id             = TARGET.input_value_id
and    TARGET.run_result_id            = RR.run_result_id
and    RR.assignment_action_id         = ASSACT.assign_action_id
and    ASSACT.payroll_action_id        = PACT.payroll_action_id
and    PACT.effective_date between
          FEED.effective_start_date and FEED.effective_end_date
and    RR.status in ('P','PA')
and    PACT.effective_date >=
          (select to_date('06-04-' || to_char( to_number(
                  to_char( BACT.effective_date,'YYYY'))
           +  decode(sign( BACT.effective_date - to_date('06-04-'
               || to_char(BACT.effective_date,'YYYY'),'DD-MM-YYYY')),-1,-1,0)),'DD-MM-YYYY')
           from dual)
and    ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and    ASSACT.assignment_id = BAL_ASSACT.assignment_id'); 

This example is the route for a UK based assignment level year to date balance that uses the 6th of April as the start of the tax year.

Comments

The route is made up of the following parts:

1. Return all possible actions for the assignment

2. Identify the possible feeds to the balance

3. - feed checking

4. Restrict the period for which you sum the balance

   - expiry checking

Note: The expiry and feed checking parts have a special significance that will become obvious later.

Specific table aliases should be used as they have a particular meaning.

• The BAL_ASSACT table is the `source' assignment action, that is, the current action for this assignment.

• The ASSACT table is the `target' assignment action, that is, the action for those results that feed the balance.

• The PACT table is the `target' payroll action, that is, used to define the date of the ASSACT assignment actions.

• We join to the BACT table, getting all the Payroll Actions in which the assignment appears.

• We join to the FEED table for the balance type and get all the TARGET input values that could possibly feed this balance.

• The run results that feed must be processed ('P' or 'PA').

• The complicated looking sub-query returns the start of the current tax year, which is from when we are summing the balance. That is, the results that feed the balance will be between the start of the current tax year and the current action sequence.

Dimension Type

Dimension type determines how a balance is treated by the Payroll Run, and for predefined dimensions this is optimized for performance of the payroll run.

The dimension type can take one of the following values:

• N - Not fed and not stored. This dimension type does not create a latest balance at any time. A balance with this dimension will always have its SQL re-executed whenever that balance is executed.

• F - Fed but not stored. This dimension type creates a balance `in memory' during the Payroll Run. This balance is fed by the run code but it does not store a latest balance on the database.

• R - Run Level balance. This dimension type is used specifically for those balances that total for the current run and must be used with the appropriate route. No latest balance value is stored on the database.

• A - Fed and stored at assignment level. This dimension type creates an assignment level latest balance and stores it in the PAY_ASSIGNMENT_LATEST_BALANCES table.

• P - Fed and stored at person level. This dimension type creates a person level latest balance and stores it in the PAY_PERSON_LATEST_BALANCES table.

Feed Checking Type

The feed checking type controls the feed checking strategy used during the payroll run. This type is used to keep the in memory balance up to date by deciding whether a run result should feed the balance. It can have the following values:

• Null This is the default value, and means that all the run result values included by the existing balance feeds will feed the balance.

• P Payroll Run executes the package procedure defined in the expiry_checking_code column on the dimension. An expiry flag parameter indicates whether feeding should occur or not.

• E Equality feed checking is done. That is, feeding occurs if there is a match between the in memory balance context values and the contexts held in the UDCA (User Defined Context Area).

The following additional types are for US and Canadian legislative balances only:

• J Jurisdiction checking is done.

• S Subject Feed Checking is done.

• T A combination of 'E' and 'S' feed checking types.

• M A combination of feed checking types 'S', 'J' and 'E'.

Expiry Checking Type

Latest balances should expire (that is, return to zero) at a time determined by their dimension. For example, a YTD (Year to Date) balance expires at the end of the year.

All loaded balances are checked for expiry by the Payroll Run, according to their expiry checking type:

• N - Never expires: balances are never set to zero.

• P - Payroll Action Level: for these types, a list of the expiry check results for each owning action/balance dimension are kept.

  Once expiry checking code has been called for such a combination, it does not need to be checked again for other balances that have the same combination, thus avoiding multiple calls to the database.

  The expiry checking is balance context independent - the list of balance contexts is not passed to the expiry checking code.

• A - Assignment Action Level: no assumptions can be made, expiry checking code is always called. The expiry checking is balance context dependent - the list of the balance contexts is passed to the expiry checking code.

• D - Date Expiry: the date expiry checking mechanism looks at the balance dimension/balance contexts combination of the balance being expiry checked, and scans the in-memory list to see if a balance with the same combination has already been expiry checked.

  If so, the expiry date is taken from that stored on the in-memory balance.

  The expiry checking is balance context dependent-the list of the balance contexts is passed to the expiry checking code.

Initial Balance Loading for Oracle Payroll

This essay describes the functionality available with Oracle Payroll to assist in the loading of initial balance values from an existing payroll system.

Introduction

Whether you are implementing Oracle Payroll for the first time, or upgrading from an earlier release you will need to set initial values for your legislative balances. It is essential for the accurate calculation of legislated deductions in Oracle Payroll that the initial values for these balances are correct.

This section shows you how to set up and load these initial balance values before you begin to process payrolls. After you have begun processing payrolls you may need to repeat this process for additional user balances you define in the future.

Warning: The steps you follow to load initial balances are completely different from the steps an end user follows to adjust a balance. You must not use the balance loading method to make balance adjustments.

Balances and Balance Adjustments in Oracle Payroll

In Oracle Payroll a balance is the accumulation of the results of a payroll calculation. The balance has a name, feeds and dimensions. The results that feed a specific balance are known as the `balance feeds' and these can add or subtract from the total. The balance loading process calculates and inserts the correct run results to set the initial values with effect from the upload date.

Balances are calculated directly from the run results that are designated as feeding the balance. This approach ensures run results and balance values are always in step and it removes the need to store and maintain extra information in the database. In effect, the definition of a balance is really the definition of the `calculation' that is performed to return the balance value.

The run results that feed a defined balance are usually the results of processing elements during a payroll run. However, there may be times when balance values have to be adjusted manually. You do this by making an entry of an element as a `balance adjustment'. When you make a balance adjustment online, the effect is to create a single processed run result for the element. This run result automatically feeds, or adjusts, all the balances that are normally fed by the element. In this way, you are able to cascade the adjustment to all affected balances.

Important: When performing an online balance adjustment you must be careful to choose the right element and input value. However, if you make a mistake you can always go back and delete and re-enter the adjustment. You delete balance adjustments from the Payroll or Assignment Actions windows.

Steps

There are three basic steps involved in loading initial balance values:

1. Define an element and input value to feed each specific balance

2. Set up the initial balance values in the tables

   PAY_BALANCE_BATCH_HEADERS
   

   PAY_BALANCE_BATCH_LINES
   

3. Run the Initial Balance Upload process

   • Use the SRS window.

   • Use Validate, Transfer, Undo and Purge modes as needed.

Balance Loading Process

When you run the initial balance loading process you set values for each balance relative to a specific date - the Upload Date. The process creates run results, to ensure your legislative balances are correct from the upload date. Maintenance of balance information after this date is managed by the system, or by using the balance adjustments.

The upload date represents the effective date of the initial balance load. For example, you run the first payroll on 01-March, with wages of 5,000 and taxes of 1,000. The salary PTD, MTD, QTD and YTD are all 5,000. The taxes PTD, MTD, QTD, and YTD are all 1,000. If you require YTD balances for Jan and Feb, run the balance initialization for a date other than 01-March. If you want values of PTD = 0, MTD = 0, YTD 200 on 01-March, you need to run the Initial Balance Upload with a date of 28- February or 01-February, and with a dimension of YTD, and a value of 200. At 01-March the values are PTD = 0, MTD = 0, YTD 200.

Consider the following example of three dimensions for gross pay balance values for one employee.

• Gross Pay Ptd 1000.00

• Gross Pay Qtd 3250.00

• Gross Pay Ytd 6250.00

The balance loading process must calculate the actual values required for each entry and the effective date for these entries. The result of the calculation is the creation of 3 balance entries.

• _PTD balance entry value is 1000.00

• _QTD balance entry value is 2250.00

• _YTD balance entry value is 3000.00

Balance Loading

The result is that the cumulative values of the individual entries match the initial requirement for each balance.

• Gross Pay Ptd = 1000.00

• Gross Pay Qtd = 1000.00 + 2250.00 = 3250.00

• Gross Pay Ytd = 1000.00 + 2250.00 + 3000.00 = 6250.00

Latest Balances

To improve payroll run performance Oracle Payroll sets and maintains 'Latest Balance Values'. If these values are not set, the balance value is created by summing the run results for the balance. If a large number of assignments have no value then there could be a significant impact on the first payroll run. Therefore, loading the latest balances prior to the first payroll run has significant implications for performance.

Note: Some balances cannot have latest balances, such as those that are used in-memory but not stored.

When you are deciding which balances and dimensions you should include in the initial loading process, consider the balances that are used in the payroll run. For example, if the payroll run uses the balance bal_YTD, but the upload process loads bal_PTD only, then the latest balance value for bal_PTD exists but not for bal_YTD. The first payroll run would have to evaluate bal_YTD.

In the normal payroll run the latest balance value is associated with the last assignment action that uses the defined balance. The balance upload process attempts to simulate this action by creating a number of balance adjustment entries prior to the upload date.

Important: If the defined balance includes contexts then the latest balance can only be created on a balance adjustment payroll action that has context values that do not contradict the latest balance that is to be created.

In Oracle Payroll, each balance adjustment entry is considered to be a separate assignment action. These adjustments are performed in date order - earliest first. The last balance adjustment, with the highest assignment action number, is used to create the latest balance.

Setting Up an Element to Feed Initial Balances

Because of the complex web of feeds that can exist for any specific balance there is a simple mechanism to let you set the initial value for any specific balance. The basic principle is that you require a special element input value to feed each specific balance; and you set each balance separately.

Elements to Initialize Legislative Balances

Oracle Payroll comes with the predefined elements and input values you need to set initial values for all your legislative balances.

Important: US and Canadian users should run a special PL/SQL script (paybalup.pkb) to create the elements and inputs needed to feed the predefined legislative balances. This script has been registered as an SRS process - Initial Balance Structure Creation. You will need to create batch lines for each of these elements.

Users in other legislations need only link the predefined elements that feed the legislative balances that must be initialized.

Elements to Initialize User-defined Balances

For all other balances you need to set up the elements that will provide the entry values for each of your initial balances. There are some rules for setting up elements for initial balance feeds.

Element

• Must have a start date 01-JAN-0001

  This rule simplifies the validation by making sure that the element and input value to feed the balance are always available.

• Must have a classification of 'Initial Balance Feed'

  This classification is excluded from the list of classifications available when you define a balance. You can only set up manual balance feeds for this type of element.

• Must be `Adjustment Only'

• Must be a nonrecurring type

• Must be processable in a payroll run

Input Values

• Must have a start date 01-JAN-0001

• Each input value must feed only one balance

  If you need to set initial values for a large number of balances you can define multiple input values for a single element with each input value feeding a different balance.

Element Link

• Must have a start date 01-JAN-0001

• Criteria must be only Link To All Payrolls - 'Yes'

Supported Balances

All the balances supported by the initialization process are set at the assignment level. Balances at the person level are set indirectly by accumulating the values from all the assignments.

Setting Up the Initial Balance Values

There can be many different sources for the initial balance value to be loaded. For example, you may be migrating from a previous version of Oracle Payroll, or from another payroll system, or you may hold this information in another system.

Two batch interface tables are supplied with Oracle HRMS to standardize the process of loading the initial balance values. You can load information directly into these tables and you can also review, update and insert values manually. This gives you total flexibility for setting values. It also enables you to define and manage the loading of separate batches as logical groups.

PAY_BALANCE_BATCH_HEADERS

Name	Null?	Type
BUSINESS_GROUP_ID		NUMBER(15)
PAYROLL_ID		NUMBER(9)
BATCH_ID	NOT NULL	NUMBER(9)
BATCH_NAME	NOT NULL	VARCHAR2(30)
BATCH_STATUS	NOT NULL	VARCHAR2(30)
UPLOAD_DATE	NOT NULL	DATE
BATCH_REFERENCE		VARCHAR2(30)
BATCH_SOURCE		VARCHAR2(30)
BUSINESS_GROUP_NAME		VARCHAR2(60)
PAYROLL_NAME		VARCHAR2(80)

Each batch identifies the payroll that is being uploaded and the date of the upload. Other identifiers can be set to identify uniquely each batch as shown, for example, in the following table.

Batch Name	Batch Ref	Batch Source	Payroll	Upload Date
Weekly Payroll	0001	SQL*Loader	Pay1	01-Jan-1995
Weekly Payroll	0002	SQL*Loader	Pay1	01-Jan-1995
Monthly Payroll	0003	SQL*Loader	Pay2	01-Jan-1995
Semi Monthly Payroll	0001	Screen	Pay3	01-Aug-1995

Tip: Truncate the table PAY_BALANCE_BATCH_HEADERS after you complete the first Year End process to avoid any constraint violations on the table.

PAY_BALANCE_BATCH_LINES

Name	Null?	Type
ASSIGNMENT_ID		NUMBER(10)
BALANCE_DIMENSION_ID		NUMBER(9)
BALANCE_TYPE_ID		NUMBER(9)
PAYROLL_ACTION_ID		NUMBER(9)
BATCH_ID	NOT NULL	NUMBER(9)
BATCH_LINE_ID	NOT NULL	NUMBER(9)
BATCH_LINE_STATUS	NOT NULL	VARCHAR2(30)
VALUE	NOT NULL	NUMBER
ASSIGNMENT_NUMBER		VARCHAR2(30)
BALANCE_NAME		VARCHAR2(80)
DIMENSION_NAME		VARCHAR2(80)
GRE_NAME		VARCHAR2(60)
JURISDICTION_CODE		VARCHAR2(30)
ORIGINAL_ENTRY_ID		NUMBER(15)

Each batch has a set of batch lines that include details of the assignment, the balance and the value for each dimension. You can also include other contexts for a specific balance.

Assignment	Balance	Dimension	Value
101	Gross Pay	PTD	1000.00
101	Gross Pay	QTD	3250.00
101	Gross Pay	YTD	6250.00
101-2	Gross Pay	PTD	750.00

Note: The tables provide support for either a system ID (such as assignment_id) or a user ID (such as assignment_number) for each piece of information. This allows maximum flexibility when you are populating the batch tables.

The rule is that if both are specified then the system ID overrides the user ID. Here is a list of the system IDs and user IDs that can be specified when setting up the tables:

System ID	User ID
BUSINESS_GROUP_ID	BUSINESS_GROUP_NAME
PAYROLL_ID	PAYROLL_NAME
ASSIGNMENT_ID	ASSIGNMENT_NUMBER
BALANCE_DIMENSION_ID	DIMENSION_NAME
BALANCE_TYPE_ID	BALANCE_NAME
ORIGINAL_ENTRY_ID
GRE_NAME (US and Canada only)
JURISDICTION_CODE (US and Canada only)

If an error occurs during the processing of the batch, the error message is written to the PAY_MESSAGE_LINES table with a source_type of H (header) or L (line).

Running the Initial Balance Upload Process

You run the Initial Balance Upload process from the SRS window to upload values from the batch tables. You can run this process in one of four modes:

• Validate

• Transfer

• Undo Transfer

• Purge

Prerequisites

On the upload date, every assignment in the batch must belong to the payroll identified in the batch header.

The payroll must have a sufficient number of time periods prior to the upload date to allow the setting of the initial balances.

Other specific criteria, such as the GRE or Legal Company, are not validated by the initial balance loading process. It is your responsibility to validate this information.

Note: The validation process contains a predefined hook to enable you to apply your own additional validation procedure to your own balances. The procedure should be named validate_batch_line.

The process will check for valid data but will not set it.

Modes

Validate Mode

There is no validation of the batch tables prior to running this process. The process validates data in PAY_BALANCE_BATCH_LINES, but does not transfer these to the Oracle HRMS database. It marks valid lines with V (Validated), and lines in error with E (Error), and sends error messages to the PAY_MESSAGE_LINES table.

The validation process is split into two phases:

• The first phase checks the integrity of the data in the batch tables.

• The second phase checks that it is possible to create all the required balance adjustment entries.

The validate process also populates the system ID entries in the table. This ensures that all subsequent processing has access to the system IDs.

All batch lines are validated independently and are marked with their individual status at the end of the process.

Transfer Mode

Transfer mode repeats the first phase of the validation check to ensure the integrity of the data in the batch tables and the existence of all system IDs.

The process calculates the balance adjustment entries required for each assignment. This list is checked and aggregated where values are shared and actual entries are then created for the assignment. This is repeated for each assignment in the batch. Successful transfer is marked with a status of T - Transferred.

Note: If any line for an assignment is in error, none of the lines for the assignment are transferred into the HRMS database. Failures are logged in the messages table against the batch line being processed and the batch line is marked as I - Invalid.

If the value of the adjustment is zero then no entry is created. For example:

Balance_PTD = 500

Balance_QTD = 500

There is no need for an adjustment to the QTD dimension since the value is already set by the PTD.

It is likely that there will be large volumes of data to load, so the work is periodically committed to preserve successful work and to reduce the number of rollback segments required.

Note: The commit size is specified by the CHUNK_SIZE parameter in PAY_ACTION_PARAMETERS. The default for CHUNK_SIZE is 20 successful assignments.

This is the same parameter used by other payroll processes to determine commit frequency.

If a batch has been processed with partial success, you can resubmit the batch and only those assignments with batch lines that have not been Transferred are processed again. You can also restart the batch process if it failed during processing, for example if it ran out of tablespace.

Undo Transfer

This mode removes all the balance adjustment entries created by the transfer process and return the status of the batch lines to U.

Note: The data in the batch tables is kept. You can correct any batch lines with incorrect values and repeat the transfer.

Purge

Purges all data in a batch regardless of current status. When a batch is purged all the messages, batch lines and the batch header are removed. This enables you to reclaim space once a batch is successfully transferred.

Use Purge mode only when you are sure that the balances for all assignments in a batch have been successfully entered into the HRMS database.

Warning: Once you have purged a batch, all the entries for that batch are deleted. This action cannot be undone.

Process Flow

The normal sequence for using these modes to load initial balances is shown in the following diagram:

Process Flow

Error Statuses

Any errors encountered are recorded in the messages table against the object being validated: either the batch itself or an individual batch line. The status set against the batch or batch lines is dependent on the mode the process is running in as well as the status of other batch lines.

Batch Line Status

The status of each batch line can be one of the following :

• V - Valid; the batch line is OK

• E - Invalid; the batch line has an error

• T - Transferred; the batch line has been successfully transferred

Batch Status

The status of the batch is dependent on the statuses of the batch lines within the batch:

• T - Transferred; all lines in the batch have been transferred

• P - Partially Transferred; some lines in the batch have been transferred

• V - Valid; all the lines in the batch are valid and none have been transferred

• E - Invalid; some of the lines in the batch are invalid and none have been transferred

Validation Problems

There are two common problems you should check.

The adjustment request for a balance dimension may be incorrect. For example, suppose an assignment has the following upload requests:

• <Balance>_QTD = 1500.00

• <Balance>_YTD = 1000.00

The YTD value is lower than the QTD value. This may be valid, if the balance decreases over time. However, balances normally increase so it is advisable to check a balance that has been decreased.

Secondly, an invalid adjustment error may occur, where the process could not find the correct date to do the adjustment. The cause of this error depend on the balance dimension that is being processed.

However, it is always good practice to make sure that all the business group details are correct, and there are enough payroll periods for the balance to be set. To check which date is being used for each assignment balance, use the following SQL:

select BL.dimension_name,
pay_balance_upload.dim_expiry_date
(BH.business_group_id
,BH.upload_date
,BL.dimension_name
,BL.assignment_id
,BL.gre_name
,BL.jurisdiction_code
,BL.original_entry_id)    expiry_date
from pay_balance_batch_headers BH
,pay_balance_batch_lines   BL
where BH.batch_name    = '&Batch_Name'
and BL.batch_id      = BH.batch_id
and BL.assignment_number = '&Assignment_Number'
and BL.balance_name  = '&Balance_Name'
;

If the expiry date is set to '31-DEC-4712' then the adjustment date could not be found.

Balance Initialization Steps

Here's a simple check list on how to set up the data:

1. Create payrolls in Oracle Payroll with periods going back to the start of the year. Enter all employees into Oracle HRMS and give them assignments to these payrolls.

   Important: The next step applies to US and Canadian users only. Users in other legislations need only define links for the predefined balance loading elements.

2. From the Submit Requests window, run the Initial Balance Structure Creation process, selecting a batch name as the parameter. For each batch, this process creates:

   • An input value to hold the amount of each balance and of any context, and enough elements with the special classification Balance Initialization to hold all the input values created

   • The necessary links and balance feeds for these elements

3. Create any other elements you need to initialize balances for your own earnings and deductions.

   • Follow the requirements listed above. See: Setting Up an Element to Feed Initial Balances.

   • Use multiple input values to reduce the number of elements

   • Define one balance feed for each input value

     Note: Each balance must have one initial balance feed only.

     Multiple input values for one element must feed balances that have the same 'upload date'.

4. Group employees into batches for managing initialization of their balances. Enter an identifying header for each batch (these headers go into the PAY_BALANCE_BATCH_HEADERS table). Each header contains the following information:

   • Business Group name and payroll name

   • Batch name and ID number

   • Upload date: the date on which the balances in the current system will be correct and ready for transfer

   For example:

       Batch Name   Business Group   Payroll Name   Upload Date
   

       Upload 1     BG name          Full Time 1    13-AUG-1995
   

5. Create a batch line for each balance to be transferred (these lines go into the PAY_BALANCE_BATCH_LINES table). A batch line includes the following information:

   • Employee assignment number

   • Balance name and dimension, such as quarter to date or year to date

   • Balance value

   • Balance context where appropriate. For US and Canadian users the context may include a GRE and a jurisdiction (federal, state, local, or provincial).

   Note: The process uses your balance feed definitions to determine which element input value to use.

   • For example:

       Asg. Number   Balance     Dimension         Value 
   

       60001         Salary      PTD               700 
       60001         Salary      QTD               1400 
       60001         Salary      YTD               2400 
       60001         Tax Paid    PTD               2200 
       60001         Tax Paid    QTD               2400 
       60001         Tax Paid    YTD               2400
   

   Important: The Tax Paid YTD value is not required because it has the same value as the QTD. However, this balance is included to create a value for the latest balance, and improve the performance of the first payroll run.

6. From the Submit Requests window, run the Initial Balance Upload process. Select the mode in which to run this process as a parameter. Available modes are:

   • Validate

     Validate batch lines but do not transfer

     Send error messages to PAY_MESSAGE_LINES

   • Transfer

     Validate and transfer batch lines

     If any line for an assignment is in error, none of the lines for the assignment are transferred

   • Undo

     Removes balance initialization entries from the database and marks the lines as U in the batch lines table.

   • Purge

     Purges all lines in the batch lines table, regardless of how they are marked.

     Note: Use Purge mode only when you are sure that the balances for all assignments in a batch have been successfully entered into the HRMS database.

Including Balance Values in Reports

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

UK users - see: Including Balance Values in Reports (UK Only), Oracle HRMS Implementation Guide (UK)

Tip: If you need to report the same balance value many times in different reports you might consider creating a reporting table. You would simply include the balance function in your PL/SQL script to populate this table.

Advantages

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

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

• You can access latest balance values, where they exist. This will optimize performance automatically.

The Balance Function

The interface to the balance 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:

• Before the function is called, calls are made to another PL/SQL function to set up the contexts to be used. These are held in package level PL/SQL tables. This enables the balance function to operate without hard coded knowledge of the contexts, and reduces client-server calls for several balances.

• The 'C' balance user exit works in two modes: date and assignment action. The balance function does not pass a mode parameter; instead the mode is resolved by using the PL/SQL overloading feature. This simplifies the interface.

The PL/SQL code resides in one package.

pay_balance_pkg

Procedure : Initialize the contexts:

procedure set_context (p_context_name  in varchar2, p_context_value in varchar2);

For example:

pay_balance_pkg.set_context ('TAX_UNIT_ID', p_tax_unit_id);

This is called to set up ALL contexts required for a balance, with the exclusion of assignment action id. Context values are maintained throughout the entire session. Subsequent calls with the same context name update the value.

Note: The context name can be specified in any case. The routine converts all context names to upper case.

Function : Get balance value (Assignment action mode):

function get_value (p_defined_balance_id    in number, 
p_assignment_action_id  in number, 
p_always_get_db_item   in boolean default false)
return number;

Function : Get balance value (Date mode):

function get_value  (p_defined_balance_id    in number, 
p_assignment_id         in number, 
p_virtual_date          in date, 
p_always_get_db_item   in boolean default false) 
return number;

The balance value is returned by this function. The parameters required for the function have been kept to a minimum. Legislation code and business group id are derived by the PL/SQL function when the balance SQL has to be built up from ff_routes.

Note: If the balance uses business_group_id as a context then this must be set up using the set_context routine.

The parameter 'p_always_get_db_item' can be ignored. It is used for testing purposes. If this value is set to 'true' then the function will not even look for a latest balance value, and will always derive the balance from the database item.

Example

This example shows how to access parameterized balances supporting jurisdiction- and GRE-based taxation (US and Canada specific).

In the UK, with the exception of court orders, no use is made of parameterized balances.

Note: For balances that are not parameterized, no calls to pay_balance_pkg.set_context are necessary.

1. Set up the contexts

   pay_balance_pkg.set_context ('TAX_UNIT_ID',  1);
   pay_balance_pkg.set_context ('JURISDICTION_CODE', '01-123-4567');
   

2. Retrieve the balance value

   bal_value := pay_balance_pkg.get_value (p_def_balance_id, p_asg_action_id);
   

3. Retrieve the balance for a different jurisdiction code but using the same value for tax unit id

   pay_balance_pkg.set_context ('JURISDICTION_CODE', '99-999-1234');
   bal_value := pay_balance_pkg.get_value (p_def_balance_id, p_asg_action_id);