The Secret Life of Initial Balance Upload

THE SECRET LIFE OF INITIAL BALANCE UPLOAD
-----------------------------------------
Checked for relevance on 30-Jun-2006
PURPOSE
-------

A technical perspective of the Initial Balance Upload procedure.

SCOPE & APPLICATION
-------------------

Meant as an overview & guide for HRMS analysts, consultants & customers.
Should be used in conjunction with the referenced documents & assumes some
knowledge of the concept of balances & balance dimensions in Release 10 
Oracle Payroll.

REFERENCES
----------

The UK & US Payoll User's Guides cover concepts & use of balances.
The technical essay 'Balance Initialization Steps' covers the set up
& use of balance initializtion functionality.


NOTE BODY
---------

1. INTRODUCTION
2. SETTING UP A BALANCE INITIALIZATION
3. WHAT HAPPENS WHEN A BALANCE UPLOAD RUNS?
4. EXAMPLE UPLOAD OF SEEDED & USER BALANCE VALUES
5. PROBLEMS WITH THE BALANCE UPLOAD PROCEDURE
6. RUNNING THE BALANCE UPLOAD MANUALLY


1. INTRODUCTION
---------------

The balance upload procedure transfers accumulated seeded & user balance
values into Oracle Payroll for use by implementations which begin processing 
mid-financial year.

Year to date & other balance values are required by Oracle Payroll to 
calculate earnings & deductions in accordance with local legislative 
requirements.  Often processing under Oracle Payroll begins during 
financial year & no accumulated balance values will be held at the
commencement of this processing.  It follows that these required balance
values must be loaded into Oracle Payroll prior to processing payrolls
mid-year.  This is achieved through the Initial Balance Upload procedure.


2.  SETTING UP A BALANCE INITIALIZATION
---------------------------------------

The steps are well covered in the referenced documents.  The basic 
pre-requisites are:

a. Create payolls as appropriate in Oracle Payroll.
b. Group employees on those payrolls into batches & create batch headers.
c. Create corresponding batch lines for the headers.
d. Create & link an element for user balances (or link the seeded element
   for seeded balances) from 01-JAN-0001.
e. Run 'Initial Balance Upload' from the Submit Reports & Processes window.

The example below illustrates how this works in practice.


3.  WHAT HAPPENS WHEN A BALANCE UPLOAD RUNS?
--------------------------------------------

The initial balance upload procedure uploads balance values into
PAY_ASSIGNMENT_LATEST_BALANCES as appropriate to the balance (some
balances do not hold latest values) & also creates the values as run 
results as appropriate.

In Release 10 balance values are calculated dynamically by the function
pay_balance_pkg.get_value, based on summing the relevant run results at a
given date.  An example of the function & its parameters is given below. 

FUNCTION pay_balance_pkg.get_value RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_DEFINED_BALANCE_ID           NUMBER                  IN
 P_ASSIGNMENT_ID                NUMBER                  IN
 P_VIRTUAL_DATE                 DATE                    IN
 P_ALWAYS_GET_DB_ITEM           BOOLEAN                 IN

PAY_ASSIGNMENT_LATEST_BALANCES exists to hold only the latest balance values
explicitly in order that payroll processes will run more quickly (holding the
latest balance value explicitly is faster than dynamically calculating it).

Initial Balance values are created as run results for the Initial Balance
Upload procedure because there are no actual run results to sum to
calculate the balance values dynamically.  Values on 
PAY_ASSIGNMENT_LATEST_BALANCES are deleted when a payroll is rolled back & 
therefore initial balance values would be lost were the first payroll to be 
rolled back, since the balance values from this first run would overwrite
the initial balance upload values but then this first run's latest balances
would be deleted by the rollback.

It follows that a balance upload creates a payroll action, assignment actions,
run results & run result values.  The uploaded balance value can then be derived
from the uploaded date from pay_balance_pkg.get_value.

4. EXAMPLE UPLOAD OF SEEDED & USER BALANCE VALUES
-------------------------------------------------

Example #1
----------

Uploading the seeded balance Gross Pay_ASG_TD_YTD on 01-AUG-98 for employee
Mr. November November with a value of 12345.00.

The balance Gross Pay is an accumulation of all relevant earnings, the 
_ASG_TD_YTD dimension specifying all earnings for a given assignment in the
current tax year.  We are required to load the balance value as at 
01-AUG-98.

The steps to achieve this are:

- Establish the assignment_id & the payroll_id for Mr. November.

  e.g.
     SQL> select payroll_id,assignment_id from per_assignments_f
       2  where person_id = (select distinct(person_id) from per_people_f
       3                     where full_name like 'November%November%');
     PAYROLL_ID  ASSIGNMENT_ID
     ----------  -------------
             50            809


- Establish the relevant balance type id & the dimension id.

  e.g.
     SQL> select balance_type_id from pay_balance_types where 
       2  balance_name = 'Gross Pay';
     BALANCE_TYPE_ID
     ---------------
               50271

     SQL> select BALANCE_DIMENSION_ID from pay_balance_dimensions where
       2  DIMENSION_NAME = '_ASG_TD_YTD';
     BALANCE_DIMENSION_ID
     --------------------
                       11


- Link the seeded element Setup Tax Balance from 01-JAN-0001 to the
  relevant payroll.

- Create rows in the balance tables based on the upload value, the required
  date & the assignment & payroll information.

   insert into pay_balance_batch_headers
   (BUSINESS_GROUP_ID
   ,PAYROLL_ID 
   ,BATCH_ID  
   ,BATCH_NAME 
   ,BATCH_STATUS 
   ,UPLOAD_DATE
   ,BATCH_REFERENCE  
   ,BATCH_SOURCE  
   ,BUSINESS_GROUP_NAME
   ,PAYROLL_NAME)
   values
   (320
   ,50
   ,1
   ,'IMLOAD'
   ,'U'
   ,'01-AUG-98'
   ,'IM'
   ,'IM'
   ,'IM'
   ,'');
  1 row created.
  
  Note that, in this case, we specify a batch name of 'IMLOAD' & references 
  'IM' but these are free format.  The payroll_id is taken from the previous 
  select statement.  We derive the next batch id, insert the relevant date & 
  also set the batch status to 'U' for Unprocessed.

   insert into pay_balance_batch_lines
   (ASSIGNMENT_ID
   ,BALANCE_DIMENSION_ID
   ,BALANCE_TYPE_ID
   ,PAYROLL_ACTION_ID
   ,BATCH_ID
   ,BATCH_LINE_ID 
   ,BATCH_LINE_STATUS
   ,VALUE 
   ,ASSIGNMENT_NUMBER 
   ,BALANCE_NAME 
   ,DIMENSION_NAME 
   ,GRE_NAME 
   ,JURISDICTION_CODE
   ,ORIGINAL_ENTRY_ID
   ,TAX_UNIT_ID) 
   values
   (809
   ,11
   ,50271
   ,''
   ,1
   ,1
   ,'U'
   ,12345
   ,''
   ,''
   ,''
   ,''
   ,''
   ,''
   ,'')
  1 row created.

  Note that we can leave some columns blank if others are populated (e.g. if
  we fill ASSIGNMENT_ID we can leave out ASSIGNMENT_NUMBER).  The assignment
  id, balance type id & dimension id are taken from the select statements 
  above & the batch id comes from the previous insert into the headers table.
  The batch line status is 'U' for Unprocessed.

- Run the 'Initial Balance Upload' procedure, which has 2 parameters, Mode &
  Batch.  Specify 'Transfer' (or another option as desired) & pick in
  the name of the batch previously created (as above, 'IMLOAD').

- If the process completes cleanly, check the results through View ->
  Assignment Process Results, checking the type 'Balance Initialization' 
  & hitting the 'Balance Adj.' button.


Example #2
----------

Uploading the user balance IAN_ASG_TD_YTD for employee Miss Juliet Juliet at 
30-JUN-98 with a value of 98765.00. 

The steps to achieve this are:

- Establish the assignment_id & the payroll_id for Miss Juliet.

  e.g.
     SQL> select payroll_id,assignment_id from per_assignments_f
       2  where person_id = (select distinct(person_id) from per_people_f
       3                     where full_name like 'Juliet%Juliet%');
     PAYROLL_ID  ASSIGNMENT_ID
     ----------  -------------
             50            594


- Establish the relevant balance type id & the dimension id.

  e.g.
     SQL> select balance_type_id from pay_balance_types where 
       2  balance_name = 'IAN';
     BALANCE_TYPE_ID
     ---------------
               50367 

     SQL> select BALANCE_DIMENSION_ID from pay_balance_dimensions where
       2  DIMENSION_NAME = '_ASG_TD_YTD';
     BALANCE_DIMENSION_ID
     --------------------
                       11


- Create element, say, 'IMUPLOADIAN' from 01-JAN-0001 with one input value 
  'IAN' of type 'Money' (element is Nonrecurring, Adjustment Only, Last 
  Standard Process with a classification of 'Balance Initialization').

- Create the Initial Feed from the balance IAN to the element 'IMUPLOADIAN' 
  input value 'IAN' in the Compensation & Benefits -> Balance window under
  the 'Initial Feed' button.

- Link the element IMUPLOADIAN from 01-JAN-0001 to the relevant payroll.

- Create rows in the balance tables based on the upload value, the required
  date & the assignment & payroll information.

   insert into pay_balance_batch_headers
   (BUSINESS_GROUP_ID
   ,PAYROLL_ID 
   ,BATCH_ID  
   ,BATCH_NAME 
   ,BATCH_STATUS 
   ,UPLOAD_DATE
   ,BATCH_REFERENCE  
   ,BATCH_SOURCE  
   ,BUSINESS_GROUP_NAME
   ,PAYROLL_NAME)
   values
   (320
   ,50
   ,3
   ,'IMLOADUSER'
   ,'U'
   ,'30-JUN-98'
   ,'IMUSER'
   ,'IMUSER'
   ,'IMUSER'
   ,'');
  1 row created.
  
   insert into pay_balance_batch_lines
   (ASSIGNMENT_ID
   ,BALANCE_DIMENSION_ID
   ,BALANCE_TYPE_ID
   ,PAYROLL_ACTION_ID
   ,BATCH_ID
   ,BATCH_LINE_ID 
   ,BATCH_LINE_STATUS
   ,VALUE 
   ,ASSIGNMENT_NUMBER 
   ,BALANCE_NAME 
   ,DIMENSION_NAME 
   ,GRE_NAME 
   ,JURISDICTION_CODE
   ,ORIGINAL_ENTRY_ID
   ,TAX_UNIT_ID) 
   values
   (594
   ,11
   ,50367
   ,''
   ,3
   ,3
   ,'U'
   ,98765
   ,''
   ,''
   ,''
   ,''
   ,''
   ,''
   ,'');
  1 row created.


- Run the 'Initial Balance Upload' procedure, specifying 'Transfer' (or 
  another option as desired) & pick in the name of the batch above, 
  'IMLOADUSER'.

- If the process completes cleanly, check the results through View ->
  Assignment Process Results, checking the type 'Balance Initialization' 
  & hitting the 'Balance Adj.' button.


5. PROBLEMS WITH THE BALANCE UPLOAD PROCEDURE

There are a number of common errors experienced when running the balance
upload.  

- APP-51053 Assignment has already been processed before the upload date.   

  This error is returned where the specified upload date is after the 
  effective date for a run for the specified assignment.  If an assignment
  has processed through, say, May then it's not possible to try to 
  initialize balances for that assignment effective from, say, July
  because there are already balance values held for the assignment.

- APP-7789 Assignment is not linked to payroll on <date>. 

  This can happen where the batch upload date is the start of a period but
  the assignment was not linked to the payroll until mid-period.
  For example, where running a balance upload from 01-MAY-98 where some new
  assignments were linked to a payroll from 15-MAY-98, the balance upload
  procedure will try to establish that the assignment is valid throughout
  the current period for the payroll frequency (so, for the whole of May).
  If this error occures, place the errored records into a new batch with an
  upload date after the date of the assignment joining the payroll.  In
  this case, create the new batch, reset the old batch to 'U' since the
  whole batch will error & run both batches through the Initial Balance
  Upload process.

- ORA-1403: No Data Found

  The balance upload fails & the following is reported to the log file:
  ORA-1403: No Data Found
  ORA-6512: at apps.pay_balance_upload line 916 
  ORA-6512: at apps.pay_balance_upload line 3469 
  ORA-6512: at apps.pay_balance_upload line 3632 
  ORA-6512: at apps.pay_balance_upload line 4045 
  ORA-6512: at line 4
  The cause is that there is no element link for the balance element at the 
  upload date.  Ensure the 'Balance Initialization' elements have current 
  links for all the assignments for which balances are being uploaded.


6. RUNNING THE BALANCE UPLOAD MANUALLY

It is useful on occasion to run the balance upload process outside the
concurrent manager / application.  The following documentation outlines how
this can be done with a single batch.


Script 'manual_upload'

      declare
        l_errbuf   varchar2(240);
        l_retcode  number;
        l_mode     varchar2(30) := 'TRANSFER';
      begin
       pay_balance_upload.process
       (l_errbuf
       ,l_retcode
       ,l_mode
       ,&l_batch_id);
      end;
      /


$ sqlplus apps/apps @manual_upload

......will prompt for the batch id.


Script 'manual_undo'
 
      declare
        l_errbuf   varchar2(240);
        l_retcode  number;
        l_mode     varchar2(30) := 'UNDO';
      begin
       pay_balance_upload.process
       (l_errbuf
       ,l_retcode
       ,l_mode
       ,&l_batch_id);
      end;
      /

$ sqlplus apps/apps @manual_undo

......will prompt for the batch id.

推荐到鲜果：