ProfitAndLoss
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - ProfitAndLoss
Overview
This reporting view provides financial statement data for SAP systems. It includes information such as client, company, chart of accounts, hierarchy name, business area, profit center, cost center, ledger in general ledger accounting, fiscal year, fiscal period, hierarchy node, language, and target currency.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.Languages_T002 |
Table containing language keys. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.fiscal_date_dim |
Table containing fiscal date dimension data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CompaniesMD |
Table containing company master data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CurrencyConversion |
Table containing currency conversion data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.FinancialStatement |
Table containing financial statement data. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.FSVTextsMD |
Table containing financial statement view texts. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.GLAccountsMD |
Table containing general ledger account master data. |
Key Use Cases
- Can be used to analyze financial performance by client, company, and other dimensions.
- Can be used to create reports on profit and loss, balance sheet, and cash flow.
- Can be used to drill down into financial data to identify trends and patterns.
Query Logic
The query first joins the Languages_T002 table to get the language key for the specified language. It then joins the fiscal_date_dim table to get the fiscal year and period for the specified date range. Next, it joins the CompaniesMD table to get the company master data. The CurrencyConversion table is then joined to get the currency conversion rates. The FinancialStatement table is then joined to get the financial statement data. Finally, the FSVTextsMD and GLAccountsMD tables are joined to get the financial statement view texts and general ledger account master data.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| FAGLL03 | Display G/L Accounts Line Items |
| FAGLL04 | Display G/L Accounts Balances |
| FAGLFLEX | Flexible Analysis of G/L Accounts |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| Client | Client ID | |
| CompanyCode | Company Code | |
| FiscalYear | Fiscal Year | |
| FiscalPeriod | Fiscal Period | |
| ChartOfAccounts | Chart of Accounts | |
| GLHierarchy | GL Hierarchy Name | |
| BusinessArea | Business Area | |
| LedgerInGeneralLedgerAccounting | Ledger in General Ledger Accounting | |
| ProfitCenter | Profit Center | |
| CostCenter | Cost Center | |
| GLNode | GL Node | |
| LanguageKey_SPRAS | Language Key | |
| GLParent | GL Parent | |
| GLFinancialItem | GL Financial Item | |
| GLNodeText | GL Node Text | |
| GLParentText | GL Parent Text | |
| GLLevel | GL Level | |
| FiscalQuarter | Fiscal Quarter | |
| GLIsLeafNode | GL Is Leaf Node | |
| CompanyText | Company Text | |
| AmountInLocalCurrency | Amount in Local Currency | |
| CumulativeAmountInLocalCurrency | Cumulative Amount in Local Currency | |
| CurrencyKey | Currency Key | |
| ExchangeRate | Exchange Rate | |
| MaxExchangeRate | Max Exchange Rate | |
| AvgExchangeRate | Avg Exchange Rate | |
| AmountInTargetCurrency | Amount in Target Currency | |
| CumulativeAmountInTargetCurrency | Cumulative Amount in Target Currency | |
| TargetCurrency_TCURR | Target Currency |