Upload Bulk Journal File

Bulk Journals are uploaded using BJU3ANBTLOAD, as follows:

From the Main Menu, select processes then select file transfer.

The following top-menu will be displayed:

Select a scheme, then select File Transfer.

The File Upload menu will be displayed.

To upload a bulk journal file, select Upload Bulk Journal File from the sub-menu on the left.

The File Upload screen will be displayed.

Click BROWSE.  This will allow you to search the available drives (user network or local) for the file to be loaded.

Highlight the required file, then click OPEN.

The file name will appear in the box.  Click SUBMIT.

Initiate a batch run

Initiate the BJU3ANBTLOAD batch run.

Log in to the system from the Logon page.

Click infrastructure.

The Welcome screen will be displayed.

From the main menu on top, select System Infrastructure.  A sub-menu will be displayed on the left.

Select Batch Jobs from the sub-menu on the left.  The Batch Job Submission screen will be displayed.

Locate the following batch job called from the list:

To initiate the batch job, click Submit in the Action column.

Validations

The journal load program (BJU3AN BTLOAD) will perform the following validations:

-          portfolio, income type, benefit event, bank account and pay centres as per validations currently performed for accounting activity journals

-          if the stakeholder for the business transaction associated to the accounting activity is a member, then a reference number must exist on the file

-          scheme code must be a valid code

-          none of the accounting activities have end dates

-          transaction date month does not fall into a closed financial period

-          If there is a value for the Secondary Member Number field, the system will read the Client records linked to the Membership with a Reference Number equal to the value in the MEM NO. field with a Client Relationship Type of SPOUSE, DEPENDANT, DISABLED DEPENDNT or FIN DEPENDANT.  The system will validate that the value for Secondary Member Number is equal to the value for Client Instance ID for one of the Client records found.  If it is, the Pre-Authorised Business Transaction will be created with the Ben Client Instance ID equal to the Instance ID of the Secondary Membership Number.  If not, the system will stop processing and write an error message to the error log.

-          For UK schemes, if there is a value for Secondary Membership Number, the system will find the Client Relationship record with a value for NIC Reference Number equal to the Secondary Membership Number value and check that the Membership Reference Number of the Membership to which it is linked is equal to the value for the Member Number field.  (If the field value is valid and there are no other errors in the file, the system will create the Pre-Authorise BT record with the Ben Client Instance ID field equal to the Secondary Membership Number value).

If any data fails validation, the whole file will be rejected and moved to the error directory.  Messages will be created in the error log for all of the members for whom the investment balance is negative or for which the transaction will result in a negative balance.

Note:

When a Bulk Journal file is loaded and there are values in the DT Bank Account Number or CT Bank Account Number columns and the Payment Type is not EFT, the system will read the Accounting Rule (BT Type) linked to the Accounting Activity to find whether the Bank Account is required for the Debit Account or the Credit Account.

If the Account BANK is the Debit Account in the Accounting Rule, the system will check that a bank account number has been included in the file in the DT Bank Account Number column.

-  If a bank account number has not been included, an error message will be written to the error log.

-  If a bank account number has been included, the system will read the Scheme Bank Accounts.  If a Bank Account is not found with an Account Number equal to the account number in the file, an error message will be written to the error log. 

-  Scheme Bank Accounts for which the value for End Date is not null will be included i.e. transactions will be enabled to be posted to a discontinued bank account.

If the Payment Type is EFT, the system will check that there are values in the DT Bank Account Number and CT Bank Account Number columns.  If not, an error message will be written to the error log.

If there are values for both columns, the system will read the Accounting Rule (BT Type) linked to the Accounting Activity to find whether the Bank Account is required for the Debit Account or the Credit Account.

-  If the Account BANK is the Debit Account in the Accounting Rule the system will read the Scheme Bank Accounts and if a Bank Account is not found with an Account Number equal to the account number in the DT Bank Account Number column in the file, an error message will be written to the error log.  Scheme Bank Accounts for which the value for End Date is not null will be included. i.e. transactions will be enabled to be posted to a discontinued bank account.

-  If the Account BANK is the Credit Account in the Accounting Rule, the system will read the Scheme Bank Accounts and if a Bank Account is not found with an Account Number equal to the account number in the CT Bank Account Number column in the file, an error message will be written to the error log.  Scheme Bank Accounts for which the value for End Date is not null will be included. i.e. transactions will be enabled to be posted to a discontinued bank account.

Refer to

Supplements

Processes

Annuity Data Migration

Refer to

Reports

General

Error Log Report

File layout

An Excel file named Bulk Journal Load.xls must be created, and it must contain the worksheets and data as described below.

The worksheets must contain the following columns:

Note:

All optional text fields must be padded with trailing spaces.

All numeric fields must be padded with leading zeroes.

Notes:

-          Negative amounts are not permitted

-          If an alphanumeric column contains no data, a space must be inserted in order for the column to align with the columns in the Save as Text worksheet.

-          There must be no blank rows in the Save as Text worksheet between the last completed row and the EOF line.

-          The last row in the Save as Text worksheet must read EOF.

-          The completed file must be saved with another name and an extension of .TXT.