With the following SQL queries, you can check the values in Lucanet.Financial Warehouse. Run the queries with the Analyze database function (see Analyzing Databases).
The SQL queries for Lucanet.Financial Warehouse help in the following situations:
Incorrect values are displayed in Lucanet, but the correct values are contained in Lucanet.Financial Warehouse. In this case, the import into the reporting entities must be checked.
The expected values are not in Lucanet.Financial Warehouse. In this case, a check must be performed to determine whether the import into Lucanet.Financial Warehouse and the script used for the import were configured correctly.
Individual lines can be commented out using //, which means the lines are no longer taken into account in the SQL query.
Texts, and therefore also dimension values, are put in single quotation marks ('). Figures, such as the fiscal year and periods, are not.
You can combine various queries with UNION or UNION ALL if the number of result columns, their names, and their data types are identical. With UNION, data records that occur multiple times are not returned, which only affects duplicates. With UNION ALL, all data records are returned.
For the SQL statements, the following aliases can be used for the tables, if necessary, to make the queries clearer: B for Bundle, OB for OpeningBalance, FP for FinancialPosting, and FPL for FinancialPostingLine.
The following operators can be used in the SQL queries for Lucanet.Financial Warehouse:
Operator
Description
=
Equal to
!=
Not equal to
<>
Not equal to
>
Greater than
<
Less than
>=
Greater than or equal to
<=
Less than or equal to
BETWEEN x AND y
Is between value x and y, each included
LIKE %%
Placeholder for character strings
IN ()
Matches any value in the specified list
Examples of the LIKE and IN operators:
"Dim_GLAccount" LIKE '%1000' means accounts that end with 1000.
"Dim_GLAccount" LIKE '1000%' means all accounts that start with 1000.
"Dim_GLAccount" IN ('1000', '2000', '3000') means the accounts 1000, 2000, and 3000.
Conditions can be linked with AND, OR, or NOT, for example NOT IN (x, y, z), and can be encapsulated using brackets.
The following default dimensions are available in Lucanet.Financial Warehouse, if configured:
Dimension code
Description
GLAccount
Ledger account
GroupAccount
Group account
SubLedgerAccount
Sub-account of ledger account, for example debtor, creditor, or asset
CostCenter
Cost center
FunctionArea
Functional area
LegalEntityPartner
Partner
TransactionType
Transaction type for schedules
BusinessUnit
Business unit
CostElement
Costs and/or revenue type from cost accounting
CostUnit
Cost unit
Order
Order
OrganizationUnit
Organization unit
ProfitCenter
Profit center
Project
Project
StatisticAccount
Statistical account
Dimension codes are specified for querying the master data from the ElementAttribute table without Dim_, for example GLAccount instead of Dim_GLAccount. For the ElementCode of subledger accounts (SubLedgerAccount), a prefix is usually used that specifies the account more specifically: C_ for creditors, D_ for debtors, and A_ for assets.
Determines the opening balance sheet values for a specific reporting entity and fiscal year. It is possible to restrict the result to various dimensions.