Data Model

This section explains the various entities of Arrangement Microservice and their respective roles.

The entities and the relationship among them can be seen here.

The upcoming sections explain the various entities in Arrangement Microservice.

Arrangement

This data model is the main model that holds the details of the arrangement.

Arrangement

Attribute	Description	T24 Mapping
arrangementId	Primary Key-<companyid> - the company Id is appended so that system clearly knows the company of the arrangement if the uniqueness of the key cannot be guaranteed across companies.	Auto-Generated Id
accountCategory	Indicates the category code used to create the Account	AA_ARR_ACCOUNT -> CATEGORY
arrangementStatus	The current status of the arrangement.	AA_ARRANGEMENT -> ARR.STATUS
arrangementStatusDate	The current date of the arrangement stored in the AMS DB	-
	Field to denote the current status of the Portfolio. (If Portfolio Arrangements)	SEC_ACC_MASTER -> PORTFOLIOSTATUS
coolingDate	The cooling date of the arrangement	AA_ACCOUNT_DETAILS -> COOLING.DATE
country	Indicates the country in which the arrangement was created	AA_ARRANGEMENT -> CO.CODE
creationDate	The date on which this arrangement was created in T24	AA.ACCOUNT.DETAILS ->  VALUE.DATE
currency	Currency in which the arrangement is taken.	AA_ARRANGEMENT-> CURRENCY
	Specifies the currency in which the Security Portfolio is to be maintained. (If Portfolio Arrangements)	SEC_ACC_MASTER -> REFERENCECURRENCY
description	General description of the contract - if any	-
dormancyStatus	Indicates the dormancy status of the account	AA_ACCOUNT_DETAILS -> ARR.AGE.STATUS
eStmtEnabled	Sets to true if the value is same as ESTMT or STATMENT from the arrangement	AA_ARRANGEMENT -> PRINTING.OPTIONS
extArrangementId	AA Id of the Contract	AA_ARRANGEMENT -> CO.CODE - AA_ARRANGEMENT -> @ID
externalIndicator	If there is a need to record the system name to which this detail was extracted from - BARCLAYS, NORDEA or software system names	true/false → if the arrangement is from an external system then it's marked as true
iban	IBAN for the arrangement	AA_ARR_ACCOUNT -> T24.IBAN
isPortFolioAccount	Sets to true if the value is set in the Portfolio field of AA.ARR.ACCOUNT	true/false → if the account has portfolio customer id then its marked as true
linkedReference	Indicates the linked reference of this arrangement - like the ACCOUNT id	AA_ARRANGEMENT -> CO.CODE - AA_ARRANGEMENT -> LINKED.APP.ID
originalContractDate	In the case of migrated accounts, this field would hold the original contract date	AA_ARRANGEMENT -> ORIG.CONTRACT.DATE
portfolioId	PortFolio Id for the account linked to the arrangement	AA_ARR_ACCOUNT -> PORTFOLIOID
productGroup	Indicates the group to which arrangement belongs	AA_ARRANGEMENT -> PRODUCT.GROUP
	Hardcoded to Portfolio if it is Portfolio Arrangements	-
productLine	Indicates the Line of arrangement  - like LENDING, DEPOSITS, ACCOUNTS, etc	AA_ARRANGEMENT -> PRODUCT.LINE
	Hardcoded to Portfolio if it is Portfolio Arrangements
relationshipPlan	If there is a relationship that this customer enjoys, this indicates the tier - like GOLD, SILVER etc	-
startDate	The date on which this arrangement was created in T24	AA_ARRANGEMENT -> START.DATE
companyRef	The branch in which the arrangement has been created	AA.ARRANGEMENT -> CO.CODE
officerKey	The Account officer responsible for this arrangement	AA.ARR.OFFICERS → PRIMARY.OFFICER
productIdentifier	Indicates the name of the Product which is currently running on this arrangement	AA.ARRANGEMENT -> PRODUCT
	Indicates the name of the Managed Account which is currently running on this arrangement	SEC_ACC_MASTER -> MANAGED.ACCOUNT
isPortfolio	Indicates whether the arrangement is from ACC_SEC_MASTER or internal arranngements	true/false → if the arrangement is from an SEC_ACC_MASTER system then it's marked as true
postingRef	List of posting restricts linked to the arrangement (Arrangement_postingRef Table)	AA_ARR_ACCOUNT -> POSTING.RESTRICT
postingRestrictStartDate	Posting Restrict Start Date for the arrangement  (Arrangement_postingRef Table)	AA_ARR_ACCOUNT ->START.DATE
shortTitle	Indicates the Language Specific abbreviation of the account title and is used throughout T24 as enrichment. (Arrangement_shortTitle Table)	AA_ARR_ACCOUNT ->SHORT.TITLE
	Specifies the account name of the Portfolio (Arrangement_shortTitle Table)	SEC_ACC_MASTER -> ACCOUNT.NAME
companyLegalUnit	Branch company code	AA_ARRANGEMENT -> CO.CODE / SEC_ACC_MASTER -> CO.CODE

Arrangement Extension

Attribute	Description
id	Primary Key (reflects the arrangement ID)
name	Holds the name of the extension Field
value	Holds the value of the extension Field

Lending Arrangement

Attribute	Description	T24 Mapping
id	Primary Key (reflects the arrangement ID)	Auto-Generated Id -> Arrangement Table
maturityDate	Indicates the maturity date of the loan	AA_ACCOUNT_DETAILS -> MATURITY.DATE
overdueStatus	Indicates the current overdue status of the loan	AA_ACCOUNT_DETAILS -> ARR.AGE.STATUS
renewalDate	Indicates the renewal date of the loan	AA.ACCOUNT.DETAILS -> RENEWAL.DATE
sanctionedDate	Indicates the sanctioned date of the loan	AA.ACCOUNT.DETAILS -> CONTRACT.DATE
termAmount	Indicates the commitment amount of the loan	AA.ARR.TERM.AMOUNT-> AMOUNT

Lending Arrangement Extension

Attribute	Description
lendingArrangementId	Primary Key (reflects the arrangement ID)
name	Holds the name of the extension Field
value	Holds the value of the extension Field

Deposit Arrangement

Attribute	Description	T24 Mapping
id	Primary Key (reflects the arrangement ID)	Auto-Generated Id -> Arrangement Table
maturityDate	Indicates the maturity date of the loan	AA_ACCOUNT_DETAILS -> MATURITY.DATE
renewalDate	Indicates the renewal date of the deposit(if a rolling deposit)	AA_ACCOUNT_DETAILS -> RENEWAL.DATE

Deposit Arrangement Extension

Attribute	Description
depositArrangementId	Primary Key (reflects the arrangement ID)
name	Holds the name of the extension Field
value	Holds the value of the extension Field

Account Arrangement

Attribute	Description	T24 Mapping
id	Primary Key (reflects the arrangement ID)	Auto-Generated Id -> Arrangement Table
closureDate	Indicates the closed date of the account arrangement	ACCOUNT_CLOSED -> ACCT.CLOSE.DATE
overdraftStatus	Indicates the current overdraft status of the account	AA_ACCOUNT_DETAILS -> OVERDRAFT.STATUS

Account Arrangement Extension

Attribute	Description
accountArrangementId	Primary Key (reflects the arrangement ID)
name	Holds the name of the extension Field
value	Holds the value of the extension Field

Portfolio Arrangement

Attribute	Description	T24 Mapping
id	Primary Key (reflects the arrangement ID)	Auto-Generated Id -> Arrangement Table
serviceType	Defines the management regime to be applied to this portfolio.	SEC_ACC_MASTER -> MANAGED.ACCOUNT
marginLending	Allowed value is YES. Enable buying power calculation based on facility OR Margin.	SEC_ACC_MASTER -> MARGIN.LENDING
investmentStrategy	Identifies the investment policy followed by the Account.Officer	SEC_ACC_MASTER -> INVESTMENT.PROGRAM
recommendedInvestStrategy	-	Created / Updated using APIs
riskProfileValidityFlag	-
riskProfileExpiryDate	-

Portfolio Arrangement Extension

Attribute	Description
id	Primary Key (reflects the arrangement ID)
name	Holds the name of the extension Field
value	Holds the value of the extension Field

Arrangement Related Details

Arrangement Interest

Attribute	Description	T24 Mapping
id	Primary Key (reflects the arrangement ID)	Auto-Generated Id -> Arrangement Table
interestProperty	Indicates the property of the interest type for an arrangement	AA_ARR_INTEREST -> @ID
dividentPaidYtd	Total Accrued Amount for the arrangement (sum of interest accrued for the completed cycle)	AA_INTEREST_ACCRUALS -> TOT.POS.ACCR.AMT
effectiveRate	Indicates at which rate is the arrangement currently running on for the date on the Id	AA_ARR_INTEREST -> EFFECTIVE.RATE
fixedRate	A fixed-rate is directly entered by the user specifying the Defined Property and is not defined by an index table	AA_ARR_INTEREST -> FIXED.RATE
floatingIndex	A floating interest rate is tied to a variable base rate	AA_ARR_INTEREST -> FLOATING.INDEX
intRateTierType	Indicates the definition of tiers of interest rates	AA_ARR_INTEREST -> RATE.TIER.TYPE
interestAccrued	Accrued Amount for the arrangement (interest accrued for the running cycle)	AA_INTEREST_ACCRUALS -> TOT.POS.ACCR.AMT
lastPaidInterest	Accrued Amount for the arrangement (interest accrued for the last completed cycle)	AA_INTEREST_ACCRUALS -> TOT.POS.ACCR.AMT
linkedRate	It would be possible to specify that the rate for a given tier be ‘Linked’, by setting the field Linked Rate to Yes.	AA_ARR_INTEREST -> LINKED.RATE
paymentFrequency	Frequency of the arrangement	AA_ARR_PAYMENT_SCHEDULE ->  DUE.FREQ
periodEndingDate	Period Ending Date for the arrangement	AA.INTEREST.ACCRUALS → PERIOD.END
periodicIndex	A periodic rate is tied to an index (e.g. LIBOR) which is dependent on a period of time (e.g. term) and possibly an amount	AA_ARR_INTEREST -> PERIODIC.INDEX

Schedule Details

Attribute	Description	T24 Mapping
id	Primary Key (reflects the arrangement ID)	Auto-Generated Id -> Arrangement Table
paymentFrequency	Indicates the actual frequency when the payment needs to be made due	ARR_PAYMENT_SCHEDULE -> DUE.FREQ
paymentType	Users can define standard payment types that can be used by a product	AA_ARR_PAYMENT_SCHEDULE -> PAYMENT.TYPE
paymentMethod	User can specify whether the amount will be Due (to or from the customer) or Capitalised	AA_ARR_PAYMENT_SCHEDULE -> PAYMENT.METHOD
property	For each Payment definition the user must specify the Properties with amounts to be paid (ScheduleDetails_property Table)	AA_ARR_PAYMENT_SCHEDULE -> PROPERTY

Payment Schedule

Attribute	Description	T24 Mapping
id	Primary Key (reflects the arrangement ID)	Auto-Generated Id -> Arrangement Table
property	Field denoting the property of cash flow from the cash flow type Example: ACCOUNT	EB_CASHFLOW -> CASH.FLOW.TYPE
scheduleDate	This field denotes the date of the cash flow.	EB_CASHFLOW -> CASH.FLOW.DATE
scheduleType	Field denoting the type of cash flow from the cash flow type. Example: PRINCIPAL	EB_CASHFLOW -> CASH.FLOW.TYPE
cashFlowType	Field denoting the type of cash flow from the cash flow type. Example: PRINCIPAL-ACCOUNT	EB_CASHFLOW -> CASH.FLOW.TYPE
outstandingBalance	Stores the cash flow amount for the Principal scheduleType	EB_CASHFLOW -> CASH.FLOW.AMT
scheduleAmount	This field will be a signed amount from the point of view of the bank	EB_CASHFLOW -> CASH.FLOW.AMT
scheduleCcyAmount	Reserved for future	-
scheduleCurrency	Reserved for future	EB_CASHFLOW -> CASHFLOW.CCY
settledDate	This field represents the effective date of the bill status	AA_BILL_DETAILS -> BILL.ST.CHG.DT
status	This field represents the bill status	AA_BILL_DETAILS -> BILL.STATUS
companyLegalUnit	Branch company code	EB_CASHFLOW -> CO.CODE

Arrangement Bills

Attribute	Description	T24 Mapping
id	Primary Key (reflects the arrangement ID)	Auto-Generated Id -> Arrangement Table
billId	Indicates the Bill Id for the Bills	AA_BILL_DETAILS -> @Id
adjustAmount	This field represents the amount adjusted after the bill was issued	AA_BILL_DETAILS -> ADJUST.AMT
advancePayment	This field represents the advance payment made on the arrangement	AA_BILL_DETAILS -> ADVANCE.PAYMENT
agingStatus	This field represents the aging status the bill underwent	AA_BILL_DETAILS -> AGING.STATUS
billStatus	This field represents the bill status	AA_BILL_DETAILS -> BILL.STATUS
deferDate	The date on which the customer will see this bill in his statement	AA_BILL_DETAILS -> DEFER.DATE
delinOsAmount	This field represents the delinquent outstanding amoun	AA_BILL_DETAILS -> DELIN.OS.AMT
infoPayType	This field gives the list of properties that are linked with info payment type	AA_BILL_DETAILS -> INFO.PAY.TYPE
originalTotalAmount	This field represents the total original amount of the bill	AA_BILL_DETAILS -> OR.TOTAL.AMOUNT
outstandingTotalAmount	This field represents the total outstanding amount of the bill	AA_BILL_DETAILS -> OS.TOTAL.AMOUNT
paymentDate	This field represents the payment date	AA_BILL_DETAILS -> PAYMENT.DATE
paymentIndicator	This field is updated with either credit or debit indicator based on the property of the bill	AA_BILL_DETAILS -> PAYMENT.INDICATOR
repayAmount	This field represents the amount repaid	AA_BILL_DETAILS -> REPAY.AMOUNT
settleStatus	This field represents the current settle status	AA_BILL_DETAILS -> SETTLE.STATUS
billDate	This field denotes the bill creation date (ArrangementBills_billType Table)	AA_BILL_DETAILS -> BILL.DATE
billType	This field signifies the type of the bill (ArrangementBills_billType Table)	AA_BILL_DETAILS -> BILL.TYPE
paymentMethod	This field represents the method of payment (ArrangementBills_billType Table)	AA_BILL_DETAILS -> PAYMENT.METHOD
paymentType	This field represents the payment type (ArrangementBills_billType Table)	AA_BILL_DETAILS -> PAYMENT.TYPE
property	This field represents the payment property for the given payment date (ArrangementBills_property Table)	AA_BILL_DETAILS -> PROPERTY
companyLegalUnit	Branch company code	AA_BILL_DETAILS -> CO.CODE

Enrichment Details

ms_reference_data (Language)

Attribute	Description	T24 Mapping
type	Hard coded to LANGUAGE	-
value	Primary key of the Langugae	LANGUAGE -> @ID
description	Description for the Language	LANGUAGE -> MNEMONIC

MsAltKey

Attribute	Description
alternateKey	Alternate Key for Arrangement / Party Details
alternateName	Alternate Name for Arrangement / Party Details
entityId	Arrangement / Party Details @ID

Officer Details

Attribute	Description	T24 Mapping
officerKey	Primary Key of the Officer Details	DEPT_ACCT_OFFICER -> @ID
description	Description for the Officer Details	DEPT_ACCT_OFFICER -> NAME
companyLegalUnit	Branch company code	DEPT_ACCT_OFFICER -> CO.CODE

Product Details

Attribute	Description	T24 Mapping
productIdentifier	Primary Key of the Product Details	AA_PRODUCT-> @ID / MANAGED_ACCOUNT@ID
description	Language Specific Description for the Product Details  (ProductDetails_description Table)	AA_PRODUCT-> DESCRIPTION/ MANAGED_ACCOUNT->  DESCRIPTION
companyLegalUnit	Branch company code	AA_PRODUCT-> CO.CODE

Company Details

Attribute	Description	T24 Mapping
companyRef	Primary Key of the Product Details	COMPANY -> @ID
customerCompanyRef	Indicates under which company the Customer files are maintained for this company	COMPANY -> CUSTOMER.COMPANY
leadCompanyRef	Indicates whether this is a Lead Company or a branch company	COMPANY -> FINANCIAL.COM
defaultFinancialCompany	Indicates under which company the Financial Static table files are maintained for this Company	COMPANY -> DEFAULT.FINAN.COM
defaultLanguageCode	Identifies the language used for this Company	COMPANY -> LANGUAGE.CODE
description	Language Specific Description for the Product Details  (CompanyDetails_description Table)	COMPANY-> DESCRIPTION
companyLegalUnit	Branch company code	COMPANY -> CO.CODE

Company Details Extension

Attribute	Description
companyRef	Primary Key (reflects the company Ref)
name	Holds the name of the extension Field
value	Holds the value of the extension Field

Posting Restrict

Attribute	Description	T24 Mapping
postingRef	Primary Key of the Posting Restrict Details	POSTING_RESTRICT-> @ID
restrictionType	Identifies the type of entries that are to be restricted, either only debits, only credits or both	POSTING_RESTRICT -> RESTRICTION.TYPE
description	Language Specific Description for the Posting Restrict Details  (PostingRestrict_description Table)	POSTING_RESTRICT-> DESCRIPTION
companyLegalUnit	Branch company code	POSTING_RESTRICT-> CO.CODE

Party Roles

Attribute	Description	T24 Mapping
roleId	Primary Key of the AA Customer Role Details	AA_CUSTOMER_ROLE -> @ID
description	Language Specific Description for the Party Roles  (PartyRoles_description Table)	AA_CUSTOMER_ROLE-> DESCRIPTION
isBeneficialOwner	This field indicates whether the customer(s) pertaining to the role is a benefical owner of the arrangemnt.	AA_CUSTOMER_ROLE-> BENEFICIAL.OWNER
maxTaxLiabPercentage	Max Limit Liability Percentage of the Roles	AA_CUSTOMER_ROLE-> MAX.LIMIT.LIAB.PERC
companyLegalUnit	Branch company code	AA_CUSTOMER_ROLE-> CO.CODE

Party Arrangement

Attribute	Description	T24 Mapping
recId	Primary Key of the Party Arrangement Details	AA_ARR_CUSTOMER-> CO.CODE - AA_ARR_CUSTOMER-> @ID -> @ID $ Auto-Generated Arrangement Id
partyRole	Role of the Party linked to the Arrangement	AA_ARR_CUSTOMER -> CUSTOMER.ROLE
arrangementId	Arrangement Id from the Arrangement Table	Auto-Generated Arrangement Id
partyId	Party Id from the Party Details Table	AA_ARR_CUSTOMER-> CO.CODE - AA_ARR_CUSTOMER-> @ID
companyLegalUnit	Branch company code	AA_ARR_CUSTOMER-> CO.CODE

Arrangement Event Processed

Attribute	Description	T24 Mapping
arrangementId	Arrangement Id from the Arrangement Table	Auto-Generated Arrangement Id
eventName	Holds the information of event that comes for an arrangement	-

Party Details

This data model stores the details of the party.

Attribute	Description	T24 Mapping from Customer
partyId	Primary Key of the Customer Details	CUSTOMER -> CO.CODE - CUSTOMER -> @ID
postingRef	List of posting restricts linked to the customer (PartyDetails_PostingRestrict Table)	CUSTOMER -> POSTING.RESTRICT
processingDate	Date on which the party detail is processed and stored
companyLegalUnit	Branch company code	CUSTOMER -> CO.CODE

Party Details Extension

Attribute	Description
partyId	Primary Key (reflects the party ID)
name	Holds the name of the extension Field
value	Holds the value of the extension Field

Extensibility

This section explains about Extensibility feature and JOLT framework.

Extensibility Feature

Extensibility Feature in AMS is acheived using JOLT Framework. An open source framework which transforms the incoming JSON event into some specific JSON object that can be derived from the incoming event with the help of a spec file. The Spec file is generally a specification on how the incoming object needs to be transformed based.

Jolt Spec File Format

The JOLT spec file name should follow some specific format,

• To update Extension Entity - EventName_EntityNameExtn.json
• To update Alternate Entity - EventName_EntityNameAlt.json

Properties Used

The property "temn.config.file.path" will be used to mention the path where the spec files are available.

For example:

• For spec files to be read from container file path or system file path - temn.config.file.path: path:C:/specfiles/
• For files to be read from class path or resource path - temn.config.file.path: specfiles/ (specfiles may be a folder inside the war)

Default Jolt Spec

Some default jolt spec files will be available in the packaged war file in the below mentioned path:

WEB-INF\lib\ms-arrangement-package-<env>-<release>.jar\schemas\avro\

Jolt Spec Example

Below screenshot is used to update extension entity of arrangement, the file name of the spec file is "AA_ARR_ACCOUNT_ArrangementExtn.json".

The transformed object will be as below; (the fields should be inside the "extensionData" object).

Below screenshot is used to update alternate entity, the file name of the spec file is "AA_ARR_ACCOUNT_ArrangementAlt.json".

The transformed object will be as below; (the fields should be inside the "altKeyRecords" array object).

Moving Jolt Spec Into Container

In Docker environment the jolt spec files to be moved into the ingesterT24 container.

Command to move the files into the docker container:

docker cp <path of the spec files> <ingesterT24 containerid>:<path where you need to place the jolt files inside the container>

Example: docker cp C:\Testing\avro 4d5e3a698e80:/etc/mapping