Skip to end of metadata
Go to start of metadata

The KSSA_ACNT table is a central reference point that ties together a great number of tables.
KSSA_ACNT is the central account table. ID is the account identifier as is referenced throughout the rest of the system as ACNT_ID_FK. This is the identifier of a KSA account.
KSSA_ACNT and many of its related tables are used to populate the Account objects. Two key tables store the information that relates to an account. In addition to the core KSSA_ACNT table, there is also KSSA_ACNT_PROTECTED_INFO; a table of details that are considered more sensitive than some other types of data. For the sake of security, a regular user that has reporting access to the data in KSA would NOT have access to this table. This populates the AccountProtectedInformation class, which, in addition to not being loaded as default, triggers memo entries when data within that class are accessed. This table has a one-to-one relationship with ACNT, and therefore KSSA_ACNT_PROTECTED_INFO.ID = KSSA_ACNT.ID
KSSA_TRANSACTION is defined earlier in this document.


Table: ACNT

Tracks the fundamentals of an account within the KSA system.

Column

Description

ID

The account identifier. It is referenced throughout the rest of the system as ACNT_ID_FK. Under normal circumstances, this will be the user id/ net id, as derived from KIM, for adhoc accounts, it will be either generated by the system, or by the creator.

TYPE

Used to disambiguate the classes that can be produced from these tables. Possible values are AND for Account->NonChargeable->Delegate, ANC-> Account->Nonchargable->Collections, ACD for Account->Chargeable->DirectChargeAccount and ACT for Account->Chargeable->Third-party account.

ACNT_STATUS_TYPE_ID_FK

References the ACNT_STATUS_TYPE table, referencing the status of the account. The account status is a single value that, can be used to impose certain limits on the account. This is a high-level status of the account, and is used in combination with other account indicators, such as flags.

ACNT_TYPE_ID_FK

Points to the account type. Distinct from the TYPE (Which dictates the class) this is the institution's account distinction if they use one. For example, this might be a Graduate Student Account, etc.

ENTITY_ID

If the account is derived from a KIM account, then the KIM identifier is stored here. This is often referred to as the NetID of the user, and will often follow the format of first initial/last name. If the entity is not a KIM identity, then the identifier from the other system will be used here, and the account will be set as IS_KIM_ACNT=false.

LAST_KIM_UPDATE

If the account is a KIM account, LAST_KIM_UPDATE is the date and time that the details were validated with the KIM datastore.

IS_KIM_ACNT

Boolean that answers the question "did this account originate from the KIM system?" If true, then the KIM fields are implied. If false, then the KIM fields will be null.

CAN_AUTHENTICATE

Boolean that answers the question "can this user authenticate into the KSA system?"

CREATION_DATE

The date that the account was established within the KSA system.

CREATOR_ID

Identifier of the user who created the account.

EDITOR_ID

Identifier of the user who last edited the account.

LAST_UPDATE

Date and time of the last update to the account.

OUTSTANDING

The balance of the account, in the system currency that is outstanding on the account. This includes any amounts that are not yet due.

UNALLOCATED

Unallocated balance on the account.

DUE

The balance of the account that is due at this moment in time. That is, all the transactions which have an effectiveDate of today or before.

LATE_PERIOD_ID_FK

LATE_PERIOD_ID_FK references the late payment table. This permits the system to have configurable groups of late payment "buckets". For example, a normal student might be considered "late" once their balance has been due for 30 days, and then they are progressively later at 60 and 90 days. Whereas for some customers (maybe a sponsoring employer) there may be an agreement that the account is not past due until 60/90/120 days.

LATE1..3

The aged balance in "buckets" according to the LATE_PERIOD table.

LAST_LATE_UPDATE

Date and time when the account last went through the ageing process.

CREDIT_LIMIT

Credit limit for the account. Enforcement of this limit is during the transaction creation process.

DATE_OF_BIRTH

DirectCharge accounts only, the date of birth of the "student" or other account holder.

ORG_NAME_ID_FK

Pointer to the org name record. If null, this is a personal account.

Table: POSTAL_ADDRESS_ACNT

Simple reference table to link addresses to accounts.

Column

Description

POSTAL_ADDRESS_ID_FK

Foreign key identifying a single address in the POSTAL_ADDRESS table.

ACNT_ID_FK

Foreign key identifying a single account in the ACNT table.

Table: POSTAL_ADDRESS

Tracks a postal address. This structure closely mimics the KIM name standard, and is stored in its own table to permit changes that may occur in the future. In particular, this format may be a problem for internationalization.

ColumnDescription

ID

Autonumbered primary key

KIM_ADDRESS_TYPE

If the address is derived from KIM, then this is set to the address type. If it is null, then the address is stored locally only, in KSA.

LINE1..3

Lines 1 through 3 of the address.

CITY

City part of the address. This should be interpreted openly as the locality name.

STATE_CODE

If the country has states or other major localities as part of its addresses, then they are stored here.

POSTAL_CODE

If the address has a coded address, it is stored here.

COUNTRY_CODE

Code for the country of the address.

IS_DEFAULT

Answers the question "is this the default address on the account?"

Table: ELECTRONIC_CONTACT_ACNT

Simple reference table to link names to accounts.

Column

Description

ELECTRONIC_CONTACT_ID_FK

Foreign key identifying a single set of electronic contact information in the ELECTRONIC_CONTACT table.

ACNT_ID_FK

Foreign key identifying a single account in the ACNT table.

Table: ELECTRONIC_CONTACT

Tracks the electronic contact information for an account. Closely mimics KIM information.

Column

Description

ID

Autonumbered primary key

KIM_EMAIL_ADDRESS_TYPE

If the email address is derived from KIM, then this is set to the address type. If it is null, then the email address is stored locally only, in KSA.

EMAIL_ADDRESS

The actual email address.

KIM_PHONE_TYPE

If the phone number is derived from KIM, then this is set to the phone type. If it is null, then the number only exists within KSA.

PHONE_COUNTRY

The country code for the number. In the absence of a precedent on the use of this field, and the constraints of the field, we will store an ISO3166 country code.

PHONE_NUMBER

The major numerical part of the number.

PHONE_EXTN

The extension part of the number, if applicable.

IS_DEFAULT

Answers the question "is this the default contact information used on the account"?

Table: ACNT_STATUS_TYPE

Storage for the different account statuses that can be applied to an account.

Column

Description

ID

Autonumbered primary key.

NAME

Friendly name for the account status. For example "In good standing".

CODE

Short code describing the status type.

DESCRIPTION

A longer description of what it means to be in that status.

Table: ACNT _TYPE

(Storage for the different account types that can be applied to an account.)

Column

Description

ID

Autonumbered primary key.

NAME

Friendly name for the account status. For example "Freshman Account".

CODE

Short code describing the account type.

DESCRIPTION

A longer description of what it means be of that type.

Table: LATE_PERIOD

Stores the different period definitions against which an account might be aged.

Column

Description

ID

Autonumbered primary key.

NAME

The name of the late period definition as displayed to the user.

DAYS_LATE1..3

The number of days after the effective date of a transaction that the account is considered to be in the appropriate late bucket. The standard reference model would be LATE1=30, LATE2=60, LATE3=90.

DESCRIPTION

A more verbose description of the late model, giving a representative better information as to what types of accounts this might be the appropriate late period definition for.

IS_DEFAULT

Boolean that answers the question "is this the default late period definition?"

Table: USER_PREF

Stores simple key/value pairs for an account permitting the storage of simple preferences.

Column

Description

ID

Autonumbered primary key.

ACNT_ID_FK

Reference to the account to which this key/value pair belongs.

NAME

Name of the key.

VALUE

Value of the key.

 

Account Helper Tables

(info) Note: These tables are extracted purely for document readability.


Table: ACNT_PROTECTED_INFO

Tracks types of data that are required for system functioning that are considered more sensitive than other types of data.

Column

Description

ID

The account identifier. It is referenced throughout the rest of the system as ACNT_ID_FK. This is the identifier of a KSA (not a KIM) account.

TAX_TYPE_ID_FK

A foreign key to the TAX_TYPE table. This is a configurable list of types of tax identifiers that the system might accept. If the system is deployed in the US, a TAX_TYPE might be "Social Security Number".

TAX_REFERENCE

The actual tax identifier as referenced in TAX_TYPE.

BANK_TYPE_ID_FK

A foreign key to the bank type table, permitting the storage of different types of bank information. For example, an ACH type used in the US would require a routing number, and account number, and an account type (checking, savings, etc.) An IBAN type would store the information differently.

BANK_DETAILS

The actual detail as references in BANK_TYPE, for example, the actual IBAN of the account holder.

ID_TYPE_FK

Foreign key to the ID_TYPE table that defines the different types of identity documentation that are accepted.

ID_SERIAL

The actual serial number of the document defined by ID_TYPE. For example, if the document is a passport, this would likely be the passport's number.

ID_ISSUER

The issuing authority of a document. For a passport, this would often be a country. For a US Driver's license, it would most often be the state that issued the license.

Table: BANK_TYPE

A simple type table that defines the different types of bank information that might be stored in the KSA system.

Column

Description

ID

Autonumbered primary key.

NAME

Friendly name for the type of bank information. For example "ACH" or "IBAN".

CODE

Short code to reference the bank type.

DESCRIPTION

A longer description of the expected value in the field.

Table: TAX_TYPE

A simple type table that defines the different types of tax information that might be stored in the KSA system.

Column

Description

ID

Autonumbered primary key.

NAME

Friendly name for the tax type identifier. For example "U.S. Social Security Number" or "British National Insurance Number".

CODE

Short code to reference the tax type.

DESCRIPTION

A longer description of the tax number type to assist operators in understanding where this information may come from, expected format, etc.

Table: ID_TYPE

A simple type table that defines the different types of identity information that might be stored in the KSA system.

Column

Description

ID

Autonumbered primary key.

NAME

Friendly name for the tax type identifier. For example "US Driver's license", "French Passport"

CODE

Short code to reference the ID Type.

DESCRIPTION

A longer description of the identification number type to assist operators in understanding where this information may come from, expected format, etc.

Table: ACNT_NAME

Link to the name records.

Column

Description

ID

Autonumbered primary key.

ACNT_ID_FK

Link to the account to which this name record is linked.

NAME_ID_FK

Link to the name record.

Table: NAME

Stores a name record. This structure closely mimics the KIM name standard, and is stored in its own table to permit changes that may occur in the future. This format might be problematic for internationalization. For organizations, a simple String name is used.

Column

Description

ID

Name identifier.

TYPE

O for Organization (Company, department, etc.) or P for Person.

ENTITY_NAME

Name of the organization (company name, etc.)

CONTACT_NAME_ID_FK

Reference to a contact name for this account.

KIM_NAME_TYPE

If the name is derived from KIM, the name type is stored here. This permits us to update the name from KIM by retrieving the correct name record if a student has more than one registered name. If the field is null, then the name is only stored within KSA.

FIRST_NAME

First name of the person.

MIDDLE_NAME

Middle name of the person.

LAST_NAME

Last name of the person.

SUFFIX

Freeform suffix, allowing for appended titles or generational information.

TITLE

Freeform prefix, allowing for titles that come before the name.

IS_DEFAULT

Answers the question "is this the default name used on the account"?

Table: COLLECTION_ACNT

Reciprocal reference table recording accounts in collections.

Column

Description

ID

Autonumbered primary key.

AGENCY_ACNT_ID_FK

Pointer to the collections account to which this account has been assigned.

ACNT_ID_FK

Pointer to the account that has been assigned to collections.

START_DATE

Timestamp when the account was assigned to collections.

END_DATE

If the account has been taken from collections, the end date will be stored here. If null, this account is still in collections.

Table: BILL_RECEIVER

Reciprocal reference table recording the permission for another account to receive bill notifications.

Column

Description

ID

Autonumbered primary key.

AUTZ_DATE

Date when this authorization was granted.

OWNER_ACNT_ID_FK

Owner of the account.

RECEIVER_ACNT_ID_FK

Account which is authorized to receive billing statements from the owner account.

Table: ACNT_AUTHZ

Reciprocal reference table recording the permission for one account to view the activity of another account.

Column

Description

ID

Autonumbered primary key.

AUTZ_DATE

Date when this authorization was granted.

AUTHZ_ACNT_ID_FK

Account that has authorized access to itself.

DEPENDENT_ACNT_ID_FK

Account which has been granted access to this account.

  • No labels