GL_Data

Primary source of General Ledger data used for loading the GL Journal Entries fact table and the Xchange GL Data cube in Planning.  It maintains the GL Account transaction detail by

• Conversion Type

• Version

• Year

• GL Type

• Office

• PG/Department

• GL Account

Current and prior year Actuals and current year Budget are loaded into the <GL_Journal_Entries_Fact> table; this also includes beginning balances for the fiscal year for GL Balance Sheet Accounts that are Capital, Expense, or Liability accounts. You should load a minimum of the current year and prior year up to a maximum of 3 years worth of data.

Base amounts should only be populated if using multiple currencies and you want to report Actuals in multiple currencies.  If Base amounts are provided these should be the converted values based on the desired currency exchange rate. Amounts are specified as a debit amount/credit amount pair or a net amount.  If both are specified, the resulting net amount must be:

(Debit_Amount - Credit_Amount) * Multiplier

or the input file will be rejected.

The period consists of 1-12 for the natural months, but there can also be a 13 for beginning balance.  The “Entry description” is a transaction description attached to the journal entry when it's booked in GL.  This provides additional details for the users when performing a drill through.  The description can be text based, an accounts payable voucher, a reversing flag, a currency rate, etc.

Many of the other optional drill-through columns provide transactional details. These columns may or may not be populated. They are not loaded into the cube or visible unless you are drilling into detail.  Instead, the values are brought in and retained in SQL so that it is possible to drill through back to SQL and research the transaction if necessary.  

Idx Field Type Maximum Length Required
1 Year Numeric 4 Yes
2 Period Numeric 2 Yes
3 Office_Code Alphanumeric 50 Yes
4 PG_Dept_Code Alphanumeric 50 Yes
5 Account_Code Alphanumeric 20 Yes
6 GL_Type_Code Alphanumeric 20 Yes
7 Currency_Code Alphanumeric 10 Yes
8 Version Alphanumeric 20 Yes
9 Local_Debit_Amount Money   Yes
10 Base_Debit_Amount Money   No
11 Native_Debit_Amount Money   No
12 Local_Credit_Amount Money   Yes
13 Base_Credit_Amount Money   No
14 Native_Credit_Amount Money   No
15 Local_Net_Amount Money   No
16 Base_Net_Amount Money   No
17 Native_Net_Amount Money   No
18 Journal_Entry_ID Alphanumeric 20 No
19 Journal_Entry_Line Integer   No
20 Batch_ID Alphanumeric 12 No
21 Batch_Status Character 1 No
22 Batch_Open_Username Alphanumeric 8 No
23 Batch_Open_Date Date/Time   No
24 Batch_Close_Date Date/Time   No
25 Entry_Description Alphanumeric 120 No
26 AP_Vendor Alphanumeric 100 No
27 Employee_Code Alphanumeric 20 No
28 Reverse_Flag Character 1 No
29 Currency_Date Date/Time   No

 

Summary

GL_Data

Used as the core source for loading the GL Journal Entries fact table and the Xchange GL Data cube in Planning.

Columns

 

Year

Year associated to the data values.   Required.

Period

Two digit fiscal period number for the period associated to the data values.   Required.

Office_Code

Office code associated to the data values; must match entry in OfficeRequired.

PG_Dept_Code

Practice Group / Department code associated to the data values; must match entry in PG_DeptRequired.

Account_Code

Account code associated to the data values; must match entry in AccountRequired.

GL_Type_Code

GL Type code associated to the data values; must match entry in GL_TypeRequired.

Currency_Code

Currency code of the native currency of this transaction; must match entry in CurrencyRequired.

Version

Version code associated to the data values.   Required.

Debit Amounts

 

Local_Debit_Amount

Debit amount in the local currency of the office.  This field is Required for 'Actual' if the Local_Net_Amount value is not included, this field is Optional for 'Actual' if the Local_Net_Amount value is included.  This field is not used for 'Budget' values

Base_Debit_Amount

Debit amount in base (or reporting) currency. Optional.

Native_Debit_Amount

Debit amount in the native currency of the transaction as specified by Currency_CodeOptional.

Credit Amounts

 

Local_Credit_Amount

Credit amount in the local currency of the office.  This field is Required for 'Actual' if the Local_Net_Amount value is not included, this field is Optional for 'Actual' if the Local_Net_Amount value is included.  This field is not used for 'Budget' values.

Base_Credit_Amount

Credit amount in base (or reporting) currency. Optional.

Native_Credit_Amount

Credit amount in the currency of the transaction as specified by Currency_CodeOptional.

Net Amounts

 

Local_Net_Amount

Net amount in the local currency of the office. This field is Optional for 'Actual' but Required for 'Budget'.

Base_Net_Amount

Net amount in base (or reporting) currency.  This field is Optional for 'Actual' but Required for 'Budget'.

Native_Net_Amount

Net amount in the currency of the transaction as specified by Currency_CodeOptional.

Optional Drill Through Columns

 

Journal_Entry_ID

Journal entry ID for the transaction.  Used for detail transaction drill through. Optional.

Journal_Entry_Line

Journal entry line for the transaction.  Used for detail transaction drill through. Optional.

Batch_ID

Batch ID for the transaction.  Used for detail transaction drill through. Optional.

Batch_Status

Batch status for the transaction.  Used for detail transaction drill through. Optional.

Batch_Open_Username

Batch open username for the transaction.  Used for detail transaction drill through. Optional.

Batch_Open_Date

Batch open date for the transaction.  Used for detail transaction drill through. Optional.

Batch_Close_Date

Batch close date for the transaction.  Used for detail transaction drill through. Optional.

Entry_Description

Entry description for the transaction.  Used for detail transaction drill through. Optional.

AP_Vendor

AP Vendor for the transaction.  Used for detail transaction drill through. Optional.

Employee_Code

Employee code associated to the data values; must match entry in EmployeeOptional.

Reverse_Flag

Reversal flag for the transaction.  Used for detail transaction drill through. Optional.

Currency_Date

Currency date timestamp for transaction. Optional.

Documentation

 

Business Rules

These rules govern creation of this table and cover what the column does and how it may be calculated.

What to Load

Data should be loaded at least for the current planning year.

Currency Implementation

Records must be loaded using a consistent currency theme for either a Single-Currency Implementation or a Multi-Currency Implementation.

Sample CSV Load File

This load file is a small sample of a GL Data load.

Sample CSV Load File

This load file is a small sample of a GL Data load.

Filename: GL_Data.csv

2007,1,01,062,6300,U,USD,A,530.34,530.34,530.34,0.00,0.00,0.00,,,,,,,,,,,,,02790,,
2007,1,01,062,6300,U,USD,A,73.00,73.00,73.00,0.00,0.00,0.00,,,,,,,,,,,,,02790,,
2007,1,01,000,1012,U,USD,A,0.00,0.00,0.00,608057.40,608057.40,608057.40,,,,,,,,,,,,,05861,,
2007,1,01,000,1012,U,USD,A,608057.40,608057.40,608057.40,0.00,0.00,0.00,,,,,,,,,,,,,05861,,
2007,1,01,000,1012,U,USD,A,0.00,0.00,0.00,608057.40,608057.40,608057.40,,,,,,,,,,,,,05861,,
2007,1,01,000,1012,U,USD,A,608057.40,608057.40,608057.40,0.00,0.00,0.00,,,,,,,,,,,,,05861,,
2007,1,01,000,1570,U,USD,A,0.00,0.00,0.00,47.16,47.16,47.16,,,,,,,,,,,,,05861,,
2007,1,41,000,3997,U,GBP,A,0.00,0.00,0.00,438188.6841,254412.35,254412.35,,,,,,,,,,,,,06397,,
2007,1,41,000,3998,U,GBP,A,0.00,0.00,0.00,1014692.525,589130.48,589130.48,,,,,,,,,,,,,06397,,
2007,1,01,000,3998,U,USD,A,0.00,0.00,0.00,1768899.97,1768899.97,1768899.97,,,,,,,,,,,,,05861,,

Columns

Year

Year numeric(

4

) NOT NULL

Year associated to the data values.   Required.

 

 

Field Load File Format
Type Numeric
Maximum Length 4
Required Yes

Period

Period numeric(

2

) NOT NULL

Two digit fiscal period number for the period associated to the data values.   Required.

 

 

Field Load File Format
Type Numeric
Maximum Length 2
Required Yes

Office_Code

Office_Code varchar(

50

) NOT NULL

Office code associated to the data values; must match entry in OfficeRequired.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 50
Required Yes

PG_Dept_Code

PG_Dept_Code varchar(

50

) NOT NULL

Practice Group / Department code associated to the data values; must match entry in PG_DeptRequired.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 50
Required Yes

Account_Code

Account_Code varchar(

20

) NOT NULL

Account code associated to the data values; must match entry in AccountRequired.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required Yes

GL_Type_Code

GL_Type_Code varchar(

20

) NOT NULL

GL Type code associated to the data values; must match entry in GL_TypeRequired.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required Yes

Currency_Code

Currency_Code varchar(

10

) NOT NULL

Currency code of the native currency of this transaction; must match entry in CurrencyRequired.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 10
Required Yes

 

Version

Version varchar(

20

) NOT NULL

Version code associated to the data values.  This value should be set to A for 'Actual' and B for 'Budget'.   Required.

 

Versions used in Planning

A

Actual

B

Budget

 
 

 

Field Load File Format
Type Alphanumeric
Maximum Length 1
Required Yes

Debit Amounts

Local_Debit_Amount

Local_Debit_Amount money NULL

Debit amount in the local currency of the office. Required.  See note above under Summary section.

 

 

Field Load File Format
Type Money
Required No

Base_Debit_Amount

Base_Debit_Amount money NULL

Debit amount in base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Native_Debit_Amount

Native_Debit_Amount money NULL

Debit amount in the native currency of the transaction as specified by Currency_CodeOptional.

 

 

Field Load File Format
Type Money
Required No

Credit Amounts

Local_Credit_Amount

Local_Credit_Amount money NULL

Credit amount in the local currency of the office. Required.  See note above under Summary section.

 

 

Field Load File Format
Type Money
Required No

Base_Credit_Amount

Base_Credit_Amount money NULL

Credit amount in base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Native_Credit_Amount

Native_Credit_Amount money NULL

Credit amount in the currency of the transaction as specified by Currency_CodeOptional.

 

 

Field Load File Format
Type Money
Required No

Net Amounts

Local_Net_Amount

Local_Net_Amount money NULL

Net amount in the local currency of the office. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Net_Amount

Base_Net_Amount money NULL

Net amount in base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Native_Net_Amount

Native_Net_Amount money NULL

Net amount in the currency of the transaction as specified by Currency_CodeOptional.

 

 

Field Load File Format
Type Money
Required No

Optional Drill Through Columns

Journal_Entry_ID

Journal_Entry_ID varchar(

20

) NULL

Journal entry ID for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required No

Journal_Entry_Line

Journal_Entry_Line int NULL

Journal entry line for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Integer
Required No

Batch_ID

Batch_ID varchar(

12

) NULL

Batch ID for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 12
Required No

Batch_Status

Batch_Status char(

1

) NULL

Batch status for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Character
Maximum Length 1
Required No

Batch_Open_Username

Batch_Open_Username varchar(

8

) NULL

Batch open username for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 8
Required No

Batch_Open_Date

Batch_Open_Date datetime NULL

Batch open date for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Date/Time
Required No

Batch_Close_Date

Batch_Close_Date datetime NULL

Batch close date for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Date/Time
Required No

Entry_Description

Entry_Description varchar(

120

) NULL

Entry description for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 120
Required No

AP_Vendor

AP_Vendor varchar(

100

) NULL

AP Vendor for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 100
Required No

Employee_Code

Employee_Code varchar(

20

) NULL

Employee code associated to the data values; must match entry in EmployeeOptional.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required No

Reverse_Flag

Reverse_Flag char(

1

) NULL

Reversal flag for the transaction.  Used for detail transaction drill through. Optional.

 

 

Field Load File Format
Type Character
Maximum Length 1
Required No

Currency_Date

Currency_Date datetime NULL

Currency date timestamp for transaction. Optional.

 

 

Field Load File Format
Type Date/Time
Required No

Documentation

Business Rules

These rules govern creation of this table and cover what the column does and how it may be calculated.

Loading Actuals

<Local_Debit_Amount> and <Local_Credit_Amount> should be specified for actuals.  The following columns require the specified values.

Version

This value should be set to A.

Loading Beginning Balances

Annual beginning balances balances may be loaded and require these columns contain the following values.

Version

Set to A.

Period

Set to 13.

Debit_Amount

Set to 0.

Credit_Amount

Set to 0.

Loading Budgets

Budgets can also be loaded into the <GL_Journal_Entries_Fact> table.  The following columns must be set when loading budgets.

Version

This value should be set to B.

GL_Type

Universal record from GL_Type.

Debit_Amount

Set to 0.

Credit_Amount

Set to 0.

Loading Variances

Additionally, variance records can be added to the <GL_Journal_Entries_Fact> table.  These versions are used in Reporting and the Dashboard and are not necessary or used for Planning.  The variance records are created with <usp_Load_GLJournalEntriesVariances>.

What to Load

Data should be loaded at least for the current planning year.  Historical information may also be loaded for reporting purposes.

Currency Implementation

Records must be loaded using a consistent currency theme for either a Single-Currency Implementation or a Multi-Currency Implementation.

Note that GL data is loaded in a different fashion than other staging fact tables with amounts; in this table, an additional amount prefixed with “native” is also loaded and that is the value of the transaction amount in the actual (or native) currency of the transaction.  <Currency_Code> is used to specify the currency of the native transaction which is different than the usual use of Currency_Code (it typically represents the currency of the local amount).

Single-Currency Implementation

Only a single currency may appear (and must be the same as the base currency) and all records must have that <Currency_Code>.  Typically for most implementations, base currency will be USD.

In a single-currency implementation, Local_* amounts, Base_* amounts and Native_* amounts should all have the same values; for example, <Local_Debit_Amount> == <Base_Debit_Amount> == <Native_Debit_Amount>

Multi-Currency Implementations

If the GL record is associated to an office whose local currency is not the same as the base currency, the local amount will be the native transactional amount converted into the currency local to the office of the transaction.  The base amount will be the native transactional amount converted into base currency.  If the GL record is associated to an office whose local currency is the same as the base currency, the two amounts (local and base) should be equal and they would be equivalent to the native amount (most likely in base currency) converted into base currency.

In a muti-currency implementation, Local_* amounts, Base_* amounts and Native_* amounts should all have equivalent values based on conversion rates.

The <Currency_Code> is the actual (or native) currency used in the transaction.  Note this is different than other staging tables where the currency code is meant to validate the amount type in the local amount column.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk