Entity Definition

Logical Name : CustomerAccountJournal
Physical Name : JL_CT_ACNT_JR

A journal that captures transaction-level changes to customer account balances. The balance values may represent monetary and/or non-monetary units of measure. For example customer purchases and returns may be reflected in monetary terms and in unit count terms. Additionally, non-monetary units like points, miles and other volume-oriented activity measures may be used to aggregate customer activity levels for the purpose of deriving discounts that reduce the out of pocket monetary price based on customer purchase volume (i.e. buy more, save more). The CustomerAccountJournal is a generalized entity type that may be used to support different kinds of customer account-level reporting. As modeled it is intended to allow a retailer to use a common entity structure to support different kinds of CustomerAccountLedgers and dependent CustomerAccountJournal entries.

Data Definition

Attribute Description Physical Name Domain Data Type Foreign Key Table
JournalEntryID (PK) Token identification number for a CustomerAccountJournal entry ID_JR_ENR Identity integer
CustomerAccountID (FK) A unique identifier for a customer account. ID_CTAC Identity integer CustomerAccountLedger(LE_CT_ACNT_LDG)
ReportingClassificationCode (FK) A code used to classify the monetary values, unit counts and quantities that are accumulated in the CustomerAccountLedger and CustomerAccountJournal. The range of values varies depending on the CustomerAccountType and varieites of financial, non-finanicl unit counts and other quantities tracked by a retailer. Some samples include SALES $'s and units RETURN $;s and units TRANSACTION_COUNTs etc. It is up to each retailer to design its ledger categories for reporting and analyizing its business. CD_RP_CLSFN Code varchar(20) CustomerAccountLedger(LE_CT_ACNT_LDG)
CreateDateTime The date and time a jounal entry is created. DT_CRT AuditDateTime datetime
StatusCode The status of this CustomerAccountJournal row (journal entry). Valid values are: P - Posted T - Tentative (incomplete) CD_STS Code varchar(20)
ReportingPeriodID (FK) The unique system assigned identifier for a particular ReportingPeriod. ID_PRD_RP Identity integer ReportingPeriod(CA_PRD_RP)
JournalEntryMonetaryAmount The monetary value captured i this journal entry. This is used to capture the financial impact of transaction activity on a CustomerAccount. MO_JR_ENR_MNTY_AMT Money decimal(16,5)
JournalEntryQuantity The quantity (with fractional units) associated with this journal entry. This is intended to caputre and accumulate statistical information related to product and service activity volumes. Examples include litres purchased, kilograms purchased, square meters purchased, etc. In addition to such physical volume related quantities, this attribute can be used to capture points and other derived loyalty quantities that are accumulated over time. It is separated from MonetaryAmount to clarify the nature of the journal entry measurement QU_JR_ENR Quantity DECIMAL(9,3)
CustomerAccountInvoiceID (FK) A unique system assigned identifier for the CustomerAccountInvoice ID_INVC_CTAC Identity integer CustomerAccountInvoice(DO_INVC_CTAC)
TransactionID (FK) A universally unique identifier (UUID) for the Transaction. This may be assembled from alternate key members. ID_TRN IdentityUUID char(32) RetailTransactionLineItem(TR_LTM_RTL_TRN)
RetailTransactionLineItemSequenceNumber (FK) The sequence number of line item within the context of this RetailTransaction. IC_LN_ITM LineNumber smallint RetailTransactionLineItem(TR_LTM_RTL_TRN)
CustomerOrderID (FK) A unique system assigned identifier for the CustomerOrder. ID_ORD Identity integer CustomerOrderLineItem(DO_CTOLTM)
CustomerOrderLineItemSequenceNumber (FK) A unique system assigned identifier for the LineItem within the confines of the CustomerOrder. IC_OR_LTM LineNumber smallint CustomerOrderLineItem(DO_CTOLTM)
CustomerAccountTypeCode (FK) Determines what kind of customer account this is. Examples include a trade account, a school account, a not-for-profit, regular customer, etc. LOYALTY - customer loyalty account for tracking points earned/redeemed REBATE -Customer reward account that pays customers based on cumulative net purchases over a deisgnated period of time TRADE - customer business credit account CONSUMER_CHG - customer individual consumer credit extended by retailer INSTALLMENT - credit account set up for one time purchase LAYAWAY - cash time payment account (not credit) RENTAL - customer rental for videos, equipment WORKORDER - account for handling services like installation and set up BACKORDER - account for handling backorder deposit and adjustments. TY_CTAC Code varchar(20) CustomerAccountLedger(LE_CT_ACNT_LDG)


Parent Entity Verb Phrase Child Entity
RetailTransactionLineItem is posted to CustomerAccountJournal
CustomerAccountLedger identifies summarization account for CustomerAccountJournal
ReportingPeriod defines time period of CustomerAccountJournal
CustomerAccountInvoice is posted to CustomerAccountJournal
CustomerOrderLineItem is posted by CustomerAccountJournal

Logical Views containing CustomerAccountJournal

Logical View
Logical 07010 - Customer Account Information Extended
Logical 07040 - Customer Segment Dependent Behavior