Data Warehouse

IS&T Home >   Data Warehouse

MIT Data Warehouse LIBRARY FINANCIAL DETAIL Table


Includes details of the Budget Transactions for Invoices and Encumbrances only (e.g., Transaction Dates, Amounts). See LIBRARY_ALLOCATION_DETAIL for other transaction types. Data in this table is taken from the Z601 table in Aleph.


Field Name

Description

Data Type

Length

CURRENCY_CODE

Three-letter codes for the currency (follows ISO code structure) (e.g., USD, EUR) (Z82-CURRENCY)

VARCHAR2

3

CURRENCY_CONVERSION_DATE

For encumbrances, this is generally the same date as the transaction date (though not necessarily for converted data, which uses 6/13/2001 for USD). For invoices, this should be the Payment Date. (Z601-CURRENCY-DATE)

DATE

8

CURRENCY_RATIO

Conversion, or exchange, rate on the date the transaction occurred; sometimes referred to as Explicit Ratio. Probably best not to use this field for calculations, since almost all of the amounts in the Warehouse are in USD. (Z601-CURRENCY-RATIO, not Z82-R)

NUMBER

22

ENCUMBRANCE_AMOUNT

The dollar amount (USD) associated with this order encumbrance. This value equates to the 'actual amounts' seen in the Financial Detail table. (Z601-LOCAL-SUM). May have 0 (zero) if postage.

NUMBER

22

ENCUMBRANCE_AMOUNT_ORIG_CURR

Encumbrance amount in the original currency; DO NOT use this field for calculations. (Z601-ORIGINAL-SUM)

NUMBER

22

INVOICE_AMOUNT

The dollar amount associated with this transaction which has been invoiced. This value equates to the 'actual amounts' seen in the Financial Detail table.

NUMBER

22

INVOICE_AMOUNT_ORIG_CURR

Invoice amount in the original currency; DO NOT use this field for calculations. (Z601-ORIGINAL-SUM)

NUMBER

22

IS_INVOICE_PAID

For invoices, if the Payment Status in Aleph is P, this field will say Y; for all other Payment Statuses, this field will say N. For encumbrances, this field is blank. (Z601-PAID, coordinates with Z77-P-STATUS)

VARCHAR2

1

LIBRARY_FUND_KEY

Key used to join to the LIBRARY_FUND table. This field should only be used for joining data, NOT for reporting.

VARCHAR2

20

LIBRARY_INVOICE_LINE_ITEM

Line number from the invoice for transactions of the type INV. (Z601-LINE-NUMBER or Z75-LINE-NUMBER))

NUMBER

22

LIBRARY_INVOICE_NUMBER

Invoice number for transactions of the type INV. (Z601-INVOICE-NUMBER, Z77-INVOICE-NUMBER, or Z75-INVOICE-NUMBER; they are all the same)

VARCHAR2

15

LIBRARY_ORDER_KEY

Key used to join to the LIBRARY_ORDER table. This field should only be used for joining data, NOT for reporting.

VARCHAR2

14

LIBRARY_VENDOR_KEY

Key used to join to the LIBRARY_VENDOR table. This field should only be used for joining data, NOT for reporting.

VARCHAR2

20

NOTE

For the LIBRARY FINANCIAL DETAIL and LIBRARY ALLOCATION DETAIL tables, the NOTE field contains detailed explanation of the budget transaction. May be system assigned e.g. INVOICE, ORDER ENCUMBRANCE (Z601-NOTE). For the CIP table, this field contains additional information regarding the CIP program.

VARCHAR2

100

RECORD_COUNTER

Used to count records within a group on a report. Always contains a value of 1.

NUMBER

1

TRANSACTION_DATE

Original date of the transaction. (Z601-OPEN-DATE or just Date in the client)

DATE

8

TRANSACTION_SORT_SEQUENCE

A unique sequence number, usually a time and date stamp, assigned by Aleph. Not sure if there is an accurate time and date stamp associated with migrated data, but it should be accurate for all Aleph-born data. (Z601-SEQUENCE)

VARCHAR2

15

TRANSACTION_TYPE_KEY

Key used to join to the LIBRARY_TRANSACTION_TYPE table. This field should only be used for joining data, NOT for reporting.

VARCHAR2

3

TRANSACTION_USER

Original user (person) who created the transaction. (Z601-USER-NAME)

VARCHAR2

10

WAREHOUSE_LOAD_DATE

The date the record was loaded into the Data Warehouse from the source system.

DATE

8