This is the specification for uploading data extract files into Sygnia’s CRM system.
The following data is extracted daily:
- Members’ market values as at the end of each day
- Member transactions for the day
Market Values:
FIELD |
DESCRIPTION |
|
CLIENT_CODE |
Clients code as it appears in Alchemy |
|
ACCOUNT_CODE |
Clients' account code |
|
ACCOUNT_TYPE_CODE |
Account type code |
|
DATE |
The date that the price is associated with Day End Date |
|
INSTRUMENT_CODE |
Instrument code as per JSE |
|
INSTRUMENT DESCRIPTION |
The description of the underlying asset that the trade relates too. |
|
NOMINAL |
The number of units |
|
PRICE |
The price of the unit trust at this date in CENTS |
|
BV_CLEAN |
The Book Value value with fees accounted for Transaction fees are not included in the book value |
|
MV_ALLIN |
The Market Value with fees accounted for Market Value with including all accruals |
Transactions:
|
FIELD |
DESCRIPTION |
|
CLIENT_CODE |
The client code |
|
ACCOUNT_CODE |
Account code |
|
ACCOUNT_TYPE_CODE |
Account type code |
|
PERIOD |
Account Statement Period e.g. 201305 |
|
DEAL_DATE |
The date that a transaction was requested |
|
DOC_NO |
Unique Deal Number |
|
INSTRUMENT CODE |
The code of the underlying asset that the trade relates to |
|
INSTRUMENT DESCRIPTION |
The description of the underlying asset that the trade relates to |
|
CURRENCY_CODE |
Map the currency code (ZAR) |
|
SETTLE_DATE |
The date that a transaction was completed |
|
PRICE |
The price of the unit at the settle date Trade Price in CENTS |
|
NOMINAL |
The number of units traded |
CLEAN |
Initial investment value if Sale = Units * Avg Cost, if Buy = Amount |
|
INCOME |
Income received on this transaction e.g. interest / dividends |
|
AMOUNT |
Transaction amount |
|
PROFIT |
Realised profit/loss |
|
TRANSACTION_TYPE_CODE |
Transaction type (Buy, Sell, Contrib, Withdrawal) codes |
|
TRANSACTION_TYPE_DESCRIPTION |
Transaction type description (Buy, Sell, Contrib, Withdrawal) |
A batch job will be scheduled to run after the BJU2AA BT Post Batch Job every day.
When the batch job runs, the system will retrieve the balances and Business Transactions (BT’s) from the Member’s accounts as per the details below and a control record will be created with the Timestamp that the batch job completed.
The system will retrieve the latest balances and the BT’s with a Timestamp greater than the Timestamp of the latest batch control record.
The following values for which the Account Code is INVSTMEMUNIT on the Business Transaction Store Balance table will be retrieved:
- Scheme Code
- Membership Reference Number
- Investment Medium Code
- Unit Value Balance
- Monetary Amount Balance
The system will perform the following:
- Find the External Code on the Client record to which the Membership with the Membership Reference Number on the balance extracted is linked.
- Find the Scheme Name for the Scheme Code on the balance record retrieved.
- Find the Name for the Investment Medium with a Code equal to the Investment Medium Code on the balance record retrieved.
- Find the latest Unit Price for the Investment Medium.
- Calculate the market value by multiplying the Unit Value Balance by the Unit Price
- Create a Market Value report with the columns as per the table below:
|
COLUMN |
DATA |
|
CLIENT_CODE |
External Client Code |
|
ACCOUNT_CODE |
Scheme Code |
|
ACCOUNT_TYPE_CODE |
Scheme Name |
|
DATE |
Effective Date of latest Unit Price |
|
INSTRUMENT_CODE |
Investment Medium Code |
|
INSTRUMENT_DESCRIPTION |
Investment Medium Name |
|
NOMINAL |
Unit Value Balance |
|
PRICE |
Unit Price |
|
BV_CLEAN |
Monetary Amount Balance |
|
MV_ALLIN |
Calculated Market Value |
The system will retrieve the Business Transactions (BT’s) on the Business Transaction Store table for which the Account Code is CONTRIBUTION, INVESTMEMB or INVSTMEMUNIT, and for which the Timestamp is greater than the Timestamp of the latest control record.
The following data for each BT will be retrieved where applicable:
- Scheme Code
- Membership Reference Number
- Account Code
- Accounting Activity Code
- Investment Medium Code
- Unit Value
- Monetary Amount
- Business Effective Date
- Actual Transaction Date
- Currency Code
- Income Type Code
- Document Number
- Timestamp
The system will perform the following:
- Find the External Code on the Client record to which the Membership with the Membership Reference Number on the BT extracted is linked.
- Find the Scheme Name for the Scheme Code on the BT retrieved.
- Find the Name for the Investment Medium with a Code equal to the Investment Medium Code on the BT retrieved.
- Find the latest Unit Price for the Investment Medium on the BT’s for which the Account Code is INVSTMEMUNIT with an Effective Date equal to the Effective Date of the BT.
- Find the value for Short Description for the Accounting Activity.
- Create a Transactions report with the columns as per the table below:
|
COLUMN |
DATA |
|
CLIENT_CODE |
External Client Code |
|
ACCOUNT_CODE |
Scheme Code |
|
ACCOUNT_TYPE_CODE |
Scheme Name |
|
PERIOD |
Year and month of the Effective Date |
|
DEAL_DATE |
Effective Date |
|
DOC_NO |
Document Number |
|
INSTRUMENT_CODE |
Investment Medium Code |
|
INSTRUMENT_DESCRIPTION |
Investment Medium Name |
|
CURRENCY_CODE |
Currency |
|
SETTLE_DATE |
Transaction Date |
|
PRICE |
Unit Price |
|
NOMINAL |
Unit Value |
|
BV_CLEAN |
|
|
INCOME |
|
|
AMOUNT |
Monetary Amount |
|
PROFIT |
|
|
TRANSACTION_TYPE_CODE |
Accounting Activity Name |
|
TRANSACTION_TYPE_DESCRIPTION |
Calculated market value |