A program extracts data from the system each week and inserts rows directly into a worksheet using the Java POI library.
The MI Dashboard consists of the following Excel Workbooks:
Home
Benefits
Maintenance
Investments
Finance
Actuarial Home
Actuarial
Pay Centres
Issue Summary
Error Log
Scorecard
Help
FAQ
Note:
The term workbook is used for the Excel document, which has multiple worksheets or tabs within it.
The following data will be extracted:
The data for the fields in the table below will be extracted as per the information in the Source column.
|
Group |
Field |
Source |
|
ASSET LIABILITY MATCHING |
ALM Done Period |
Read the Period Ended on the latest Process Control record with a Business Process of A/L MATCHING for which the Date Completed for the Process Function of MATCH A/L is not null. |
|
|
Percentage issues outstanding |
Read the Notation records with a Note Type Code of ASSET LIABILITY MATCHING and count the records found. Calculated the percentage of records for which the Date Completed is not null to the total records found. |
|
CLAIMS |
Claims older than 6 months |
Find the Benefit Request records for which the Status is not FINALISED and for which the Notification Timestamp is greater than 6 months prior to the extract date. Count the number of Benefit Requests. |
|
|
Claims older than 24 months |
Find the Benefit Request records for which the Status is not FINALISED and for which the Notification Timestamp is greater than 24 months prior to the extract date. Count the number of Benefit Requests. |
|
|
Finalised exits |
Find the Memberships for which the Membership Status is EXIT FINALISED and for which there is a balance on the Member’s BENPAYABLE account. Count the number of Memberships. |
|
FINANCIAL STATEMENTS |
Audit Preparation Completed |
Read the Date Completed on the latest Process Control record with a Business Process of FIN STATEMENTS for the Process. |
|
|
Audit Release Date |
Read the Date Completed on the latest Process Control record with a Business Process of FIN STATEMENTS for the Process. |
|
|
Trustee Signoff Date |
Read the Date Signed Off on the latest Process Control record with a Business Process of FIN STATEMENTS for the Process Function of AUDIT SIGN OFF. |
|
|
Date Completed |
Read the Date Completed on the latest Process Control record with a Business Process of FIN STATEMENTS for the Process Function of COMPILE AFS. |
|
|
Date Submitted |
Read the Date Completed on the latest Process Control record with a Business Process of FIN STATEMENTS for the Process Function of COMPILE AFS. |
|
|
Audit information submitted to Actuarial |
Read the Date Completed on the latest Process Control record with a Business Process of FIN STATEMENTS for the Process Function of AUDIT TO ACT. |
|
|
Period |
Read the Period Ended Date on the latest Process Control record with a Business Process of FIN STATEMENTS |
|
LIFECYCLE |
Due Date of last Lifecycle Investments |
Read the Frequency on the Life Cycle Switching Rules per Scheme and based on the Frequency and the Switching Dates determine the last Switching Date less than or equal to the extract effective date. |
|
|
Date last Lifecycle Investment Authorised |
Find the latest Switching Detail record for the Scheme for which the Bulk Type is LIFE CYC SWITCH the Status Code is C and with a Sell Due Date less than or equal to the extract effective date and read the Date Authorised (D_DATE_AUTH_REJECT). |
|
ROLES |
Valuator |
Scheme Contact Person with a CART of VALUATOR |
|
|
Principal Officer |
Scheme Contact Person with a CART of PRINCIPAL OFF |
|
|
Broker/Consultant |
Person or Corporate Client linked to the Scheme with a CART of INTERMEDIARY or CONSULTANT |
|
|
Regional Consulting Manager |
Person linked to the Admin Branch to which the Scheme is linked, with a Client Relationship Type of REG CONSULT MNR |
|
ACTUARIAL |
Actuarial Billing |
Read the value for Type of Service on the latest Scheme Details. |
|
|
Date scheme deregistered |
End Date on the Fund Registration record for the Required Registration for the Legislative Body with a value for LEGISLTIVE BODY of REGISTRAR OF PF. |
|
|
Pension Increase Date |
Read the value for the latest Scheme Parameter with a Parameter Type of ANN INCR DATE |
|
|
FICA Status |
Read the value for UDPV BANK ACC REG on the Scheme’s Bank Account. |
|
|
CASA Number |
Registration Number on the Fund Registration record for the Required Registration for the Legislative Body with a value for LEGISLTIVE BODY of CASA |
|
|
Principal Officer CASA Number |
New field on Contact Person record |
|
|
Valuator Appointment Date |
New field on Contact Person record |
|
|
Valuator FSB Approval |
New field on Contact Person record |
|
|
Date Next Valuation Due |
Can be derived from the Period and Period Ended fields on the existing Scheme Note Type VALUATION |
|
|
Extension Date Requested |
Read the Date Submitted on the Notation record with a Document Type of VALUATION and a Header of EXTENSION linked to the latest Process Control record with a Business Process of VALUATION. A Note with a Header of EXTENSION must be captured to record the details of the application to the FSB for an extension with the following dates (see 2.3.2.4) |
|
|
Extension Date Granted |
Read the Date Extended To on the Notation record with a Document Type of VALUATION and a Header of EXTENSION linked to the latest Process Control record with a Business Process of VALUATION. |
|
|
Date Expected by FSB |
|
|
|
Number of Days Valuation Overdue |
Read the Date Completed on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL TO FSB. If the date is null read the Date Signed Off on the Notation record with a Document Type of VALUATION and a Header of EXTENSION linked to the latest Process Control record with a Business Process of VALUATION. If the date is less than current date calculate the number of days from the Date Signed Off to current date. Note: The Frequency Base Date will be set to the date the valuation is due at the FSB (see 2.3.2.3) If no Notation record is found with a Document Type of VALUATION and a Header of EXTENSION read the Due Date on the Process Control record and calculate the number of days from the Due Date to current date. If no Process Control record with a Business Process of VALUATION is found and the value for the field Date Expected by FSB is less than current date calculate the number of days from the Date Expected by FSB to current date. |
|
|
Date Audit Submitted to Actuarial |
|
|
|
Valuation Period |
Read the Period on the latest Process Control record with a Business Process of VALUATION |
|
|
Valuation Date |
Read the Date Completed on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL DATA RECD. |
|
|
Valuation Actuary Signoff |
Read the Date Completed on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL SIGN OFF. |
|
|
Valuation Submitted to FSB |
Read the Date Started on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL TO FSB. |
|
|
Valuation Accepted by FSB |
Read the Date Completed on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL TO FSB. |
|
|
Valuation last submitted |
Read the Period Ended on the last Process Control record with a Business Process of VALUATION for which the Date Completed for the Process Function of VAL TO FSB is not null. |
|
|
Valuation surplus apportionment date |
Read the Frequency Base Date on the Diary record with a Description of SURPLUS, VALUATION SURPLUS OR SURPLUS APPORTIONMENT. |
|
|
SARS Number |
Add a column to the extract but leave blank until the changes to accommodate the SARS Number have been deployed. |
|
|
Commissioner of Inland Revenue Number |
Extract the Registration Number for the Scheme Registration with a Description of South African Revenue Services. |
|
SCHEME STATUS |
Group Scheme Indicator |
Read value for Scheme Group Type of PRODUCT and if the value is GROUP RISK set the value for this field to Y. If not set to N. |
|
|
Unclaimed Benefits |
Count the Memberships with a Membership Status of UNCLAIMED BEN |
|
|
DB or DC Scheme |
Read the Scheme Group Type of PRODUCT. If it is DEFINED BENEFIT or DEFINED BENEFIT (PENSIONS IN SCHEME) set the value for this field to DB. If it is DEFINED CONTRIBUTION or DEFINED CONTRIBUTION (PENSIONS IN SCHEME) set it to DC. If it is DB/DC COMBO set it to BOTH. |
|
|
Temporary Absence Member |
Find the Temporary Absence and Offshore Detail records with a Start Date less than the extract date and for which the End Date is null or greater than the extract date. Count the records found. |
|
|
Pensioners in Scheme |
Find the Membership records with a Membership Status of ANNUITANT. Count the records found. |
|
|
Fund Amendment Date |
Find the authorised Fund Update record with the latest Timestamp. |
|
|
PI Insurance Renewal Date |
Read the Frequency Base Date DD, Frequency Base Date MM, Frequency Base Date CCYY and the Frequency on the Fund Diary Date record with a Description of PI INSURANCE and determine the next date. |
|
|
Scheme Indicator |
Read the Pooling Status for the Scheme and if it is MAIN UMBRELLA or SUB UMBRELLA set this value to UMBRELLA. If it is not read the value for Scheme Group Type of PRODUCT and if it is INDUSTRY set this value to INDUSTRY. If the Pooling Status is not MAIN UMBRELLA or SUB UMBRELLA set this value to PRIVATE. |
|
|
Priority Status |
Read the value for the Scheme Group Type of TYPE OF FUND and if it is ACTUARIAL set this value to A and if it is CONSULTING set this value to C. If it is not ACTUARIAL or CONSULTING read the value for Priority Status on the Scheme Details set this value as follows depending on the Priority Status: HIGH = H NORMAL or no value found = N TEST = E |
|
|
SARS number / Commissioner of Inland Revenue |
A column will be added for the Commissioner of Inland Revenue Number and populated with the value currently extracted for the SARS Number. A column will be included for the SARS Number and the value extracted when it is available. |
|
TRANSFERS |
Number of Transfers |
Retrieve the Business Transactions in the Members’ CONTRIBUTION Account with a Timestamp greater than the Timestamp of the previous extract and with an Income Type equal to ECP, EVP, PII, PRE, PTU or RVP. Count the transactions |
|
UNITISED AND BONUS |
Unitised or Bonus Indicator |
Read Earning Allocation Basis on the Investment Mediums for the active Portfolios linked to the Scheme. If it is BONUS for all of the Portfolios set this value to B. If it is UNITISED for all of the Portfolios set this value to U. If it is BONUS some of the Portfolios and UNITISED for some of the Portfolios set this value to BOTH. |
|
|
Date of Last Final Bonus |
Read the Effective Date for the latest Bonus Rate with a Type of FINAL for the Portfolios for which the Earning Allocation Basis is BONUS. Note: If there is more than one Bonus Portfolio for the Scheme the Effective Date of the latest Bonus Rate will be displayed. |
|
|
Date of Interim Rate |
Read the Effective Date for the latest Bonus Rate with a Type of INTERIM for the Portfolios for which the Earning Allocation Basis is BONUS. Note: Date of Interim Rate is an Effective Until date. If there is more than one Bonus Portfolio for the Scheme the Effective Date of the latest Bonus Rate will be displayed. |
|
EXCO REPORTING |
New Business |
For each Scheme read the Effective Date on the Fund Detail record and if it is less than 12 months prior to extract date set this value to Y. If greater than 12 months prior to extract date set it to N. |
|
|
Date New Client Joined |
Fund Details Effective Date |
|
|
Business Lost |
For each Schemes read the Discontinuance Date and if it not null and the date is less than 12 months prior to the extract date set the value for this field to Y. If null or greater than 12 months prior to the extract date set it to N |
|
|
Termination Date |
Fund Discontinuance Date. |
|
|
Total Pensionable Salaries per Fund |
Sum the values for the latest Actual Earning records with a Salary Nomination with a Type of BASIC for all of the Memberships for which the Membership Status is LIVE |
|
|
EFT Compliance |
Retrieve all of the Business Transactions in the Scheme’s BANK account for which the Payment Method is EFT or CHEQUE and the Transaction Date is Less than or equal to the extract date and greater than the extract date less 1 month. Count the number of BT’s and count the number of BT’s with the Payment Method of EFT. Calculate the percentage of BT’s with Payment Method of EFT. |
|
PAYCENTRE |
Priority Status |
|
|
|
Billing Instructions (ACB runs) per Pay Centre |
Retrieve the latest Business Transaction in the Scheme’s CONTRIBFUND account with an Accounting Activity SFNOEFTP per Pay Centre for Schemes for which the Type of Fund is DEBIT ORDER UMBRELLA FUND and set this value to the Due Date. |
|
|
Dormant Members |
|
|
ERROR LOG |
Error number |
Read the Translation Values with a Translation Purpose of BATCH JOBS and find the value for the Target System Attribute of Description for the Source System Attribute of Code equal to the Program Name on the error message. |
|
CONTROL ACCOUNTS |
Memberdep account |
Retrieve the balances on the Members’ MEM DEPOSIT accounts and sum the balances per Scheme. Retrieve the balances on the Members’ BENPAYABLE accounts and sum the balances. Subtract the sum of the BENPAYABLE account balances from the sum of the MEM DEPOSIT account balances and Display the difference. |
|
|
Investmember account |
Retrieve the monetary balances on the Members’ INVESTMEMB accounts for which the Earning Allocation Basis on the Investment Medium to which the Portfolio is linked is UNITISED as at the last day of the month prior to the extract date and sum the balances per Scheme. Note: The balance in the INVESTMEMB account for unitised portfolios represents un-unitised transactions and indicates a delay in the capturing of unit prices. There will always be at least a 1 day delay in the receipt of unit prices and therefore a balance on these accounts will not necessarily indicate a problem. As the bulk of the amounts waiting to be unitised will be contributions and because the majority of contributions are processed after the end of the month the balances will only be extracted as at the end of the month. By the end of the month the contributions should have been unitised. |
|
|
Investtranst account % of assets |
Extract the balance on the INVESTTRANST account and the balance on the INVESTMENT account. Calculate the value of the INVESTTRANST account balance as a percentage of the INVESTMENT account balance. |
|
|
Disinvtranst account % of assets |
Extract the balance on the DISINVTRANST account and the balance on the INVESTMENT account. Calculate the value of the DISINVTRANST account balance as a percentage of the INVESTMENT account balance. |
|
FINANCE |
Annual PAYE Recon submission |
Read the Date Completed on the last Process Control record with a Business Process of TAX RECON for the Process Function of SUBMIT TAX RECON . |
|
|
Opening Balances – measure Trial Balance in balance |
The date of the last retained earnings journal processed as the only reason the trial balance does not balance should be because the retained earnings journal has not been processed. |
|
|
Commission paid to brokers |
Retrieve the latest Business Transaction in the Scheme’s COMMISSION account with an Accounting Activity of PAYCOMPAYABL and set this value to the Due Date on the Business Transaction |
|
|
Dormant Members per Pay Centre |
Count the number of Memberships per Pay Centre linked to a Membership Group that is linked to a Membership Grouping of DORMANT or for which the Contribution Status on one of the Membership Groups to which the Member is linked is DORMANT.) |
|
VALUATION |
Date Signed Off |
Date Signed Off on the latest Scheme Note with a Type of VALUATION |
|
|
Due Date |
Read the Date Signed Off on the Notation record with a Document Type of VALUATION and a Header of EXTENSION linked to the latest Process Control record with a Business Process of VALUATION. If no Notation record with a Document Type of VALUATION and a Header of EXTENSION read the Due Date on the latest Process Control record with a Business Process of VALUATION. If no Process Control record is found derive the due date from the Frequency Base Date and the Frequency on the Diary record with a Description of VALUATION and if the due date less the warning period is less than or equal to current date set this date to the due date calculated. Note: The Frequency Base Date will be set to the date the valuation is due at the FSB (see 2.3.2.3) |
|
|
Date Submitted |
Date Submitted on the latest Scheme Note with a Type of VALUATION |
|
|
Valuation Period |
Period on the latest Scheme Note with a Type of VALUATION |
|
|
Extension Date Requested |
Read the Date Submitted on the Notation record with a Document Type of VALUATION and a Header of EXTENSION linked to the latest Process Control record with a Business Process of VALUATION. A Note with a Header of EXTENSION must be captured to record the details of the application to the FSB for an extension with the following dates (see 2.3.2.4) |
|
|
Extension Date Granted |
Read the Date Completed on the Notation record with a Document Type of VALUATION and a Header of EXTENSION linked to the latest Process Control record with a Business Process of VALUATION. |
|
|
Number of Days Valuation Overdue |
Read the Date Completed on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL TO FSB. If the date is null read the Date Signed Off on the Notation record with a Document Type of VALUATION and a Header of EXTENSION linked to the latest Process Control record with a Business Process of VALUATION. If the date is less than current date calculate the number of days from the Date Signed Off to current date. Note: The Frequency Base Date will be set to the date the valuation is due at the FSB (see 2.3.2.3) If no Notation record is found with a Document Type of VALUATION and a Header of EXTENSION read the Due Date on the Process Control record and calculate the number of days from the Due Date to current date. If no Process Control record with a Business Process of VALUATION is found and the value for the field Date Expected by FSB is less than current date calculate the number of days from the Date Expected by FSB to current date. |
|
|
Valuation data received |
Read the Date Completed on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL DATA RECD. |
|
|
Valuation Actuary Signoff |
Read the Date Completed on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL SIGN OFF. |
|
|
Valuation Submitted to FSB |
Read the Date Started on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL TO FSB. |
|
|
Valuation Accepted by FSB |
Read the Date Completed on the latest Process Control record with a Business Process of VALUATION for the Process Function of VAL TO FSB. |
|
|
Valuation last submitted |
Read the Date Completed on the last Process Control record with a Business Process of VALUATION for the Process Function of VAL TO FSB. |
|
|
Valuation period last submitted |
Read the Period Ended on the last Process Control record with a Business Process of VALUATION for which the Date Completed for the Process Function of VAL TO FSB is not null. |
|
|
Valuation surplus apportionment date |
Read the Frequency Base Date on the Diary record with a Description of SURPLUS, VALUATION SURPLUS OR SURPLUS APPORTIONMENT. |
|
RESERVE BANK REPORTING |
Q1 (First quarter) |
Date submitted on the latest Scheme Note with a Type of RESERVE BANK and for which the day and month of the Period Ended is 31/03. |
|
|
Q2 (Second quarter) |
Date submitted on the latest Scheme Note with a Type of RESERVE BANK and for which the day and month of the Period Ended is 30/06. |
|
|
Q3 (Third quarter) |
Date submitted on the latest Scheme Note with a Type of RESERVE BANK and for which the day and month of the Period Ended is 30/09. |
|
|
Q4 (Fourth quarter) |
Date submitted on the latest Scheme Note with a Type of RESERVE BANK and for which the day and month of the Period Ended is 31/12. |
|
ROLES |
Regional Admin Manager |
Person linked to the Admin Branch to which the Scheme is linked, with a Client Relationship Type of REGIONAL MNGR |
|
|
Regional Finance Manager |
Person linked to the Admin Branch to which the Scheme is linked, with a Client Relationship Type of REG FIN MNGR |
|
|
Actuarial Manager |
Person linked to the Admin Branch to which the Scheme is linked, with a Client Relationship Type of ACTUARIAL MNGR |
|
|
Admin Manager |
Person linked to the Admin Branch to which the Scheme is linked, with a Client Relationship Type of ADMIN MNGR |
|
|
Finance Manager |
Person linked to the Admin Branch to which the Scheme is linked, with a Client Relationship Type of FINANCIAL MNGR |
|
|
Consulting Manager |
Person linked to the Admin Branch to which the Scheme is linked, with a Client Relationship Type of CONSULTING MNGR |
|
|
Valuator |
Scheme Contact Person with a Client Agreement Role Type (CART) of ACTUARY |
|
|
Auditor |
Scheme Contact Person with a Client Agreement Role Type (CART)of AUDITOR |
|
TERMINATION |
Date Submitted |
Date Submitted on the latest Scheme Note with a Type of TERMINATING |
The MI Dashboard indicates whether or not Distribution is applicable for schemes for which the unit prices are net of the investment fees and therefore are not apportioned using the distribution process.
Similarly for schemes for which Expense Billing is not required, the MI Dashboard indicates whether or not this is applicable.
If the Business Process Templates have been set up with a Business Process of MONTHLY CYCLE, then when the dates are extracted for the following fields for the MI Dashboard. The system will read the Business Process Template with a Process of MONTHLY CYCLE linked to the Scheme, and if the equivalent Process Function is not linked to the Business Process Template, the date will be set to 00/00/0000 to indicate that it is not applicable:
- Distribution Batch Date Last Authorised
- Distribution Batch Period Last Authorised
- Expense Billing Date Last Authorised
- Risk Premiums Paid Date Last Paid
- Admin Fees Paid Date Last Paid
- Admin Fees Paid Period Last Paid
- ITax Fees Paid Date Last Paid
- ITax Fees Paid Period Last Paid
The following table indicates the Process Function applicable to each of the MI Dashboard fields above.
|
MI Dashboard Field |
Process Function |
|
Distribution Batch Date Last Authorised |
DISTRIBUTION |
|
Distribution Batch Period Last Authorised |
DISTRIBUTION |
|
Expense Billing Date Last Authorised |
EXPENSE BILLING |
|
Risk Premiums Paid Date Last Paid |
PAY PREMIUMS |
|
Admin Fees Paid Date Last Paid |
PAY FEES |
|
Admin Fees Paid Period Last Paid |
PAY FEES |
|
ITax Fees Paid Date Last Paid |
PAY TAX FEES |
|
ITax Fees Paid Period Last Paid |
PAY TAX FEES |