CashDiscountUtilization
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - CashDiscountUtilization
Overview
The AccountingDocumentsKPI view provides key performance indicators (KPIs) for accounting documents in SAP systems. It includes information such as the client, company code, vendor account number, accounting document number, amount in local currency, clearing date, document number of the clearing document, cash discount received in source and target currencies, target currency, posting date in the document, target cash discount in source and target currencies, and vendor name.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.AccountsPayable |
Contains information about accounts payable, including client, company code, vendor account number, vendor name, accounting document number, amount in local currency, clearing date, document number of the clearing document, cash discount received in source and target currencies, target currency, and posting date in the document. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.AccountingDocuments |
Contains information about accounting documents, including client, company code, vendor account number, accounting document number, amount in local currency, clearing date, document number of the clearing document, cash discount received in source and target currencies, target currency, posting date in the document, and posting key. |
Key Use Cases
- Analyzing vendor payment performance
- Identifying opportunities for cash discount optimization
- Monitoring accounts payable aging
- Reporting on accounting document KPIs
Query Logic
The view is created using a Common Table Expression (CTE) called AccountingDocumentsKPI. The CTE joins the AccountsPayable and AccountingDocuments tables on the client, company code, and accounting document number. It then calculates the target cash discount in source and target currencies based on the posting key, clearing date, baseline date for due date calculation, cash discount days 1, and cash discount percentage 1. The view then selects the client, company code, company text, vendor account number, vendor name, accounting document number, target currency, currency key, amount in local currency, clearing date, document number of the clearing document, posting date in the document, sum of cash discount received in source and target currencies, and average of target cash discount in source and target currencies from the AccountingDocumentsKPI CTE. It also left outer joins to the VendorsMD table to get the vendor name.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| FB03 | Display Document |
| FBL1N | Display Line Items |
| FBL3N | Display Documents: Line Items |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| Client_MANDT | Client ID | |
| CompanyCode_BUKRS | Company Code | |
| CompanyText_BUTXT | Company Text | |
| AccountNumberOfVendorOrCreditor_LIFNR | Account Number of Vendor or Creditor | |
| NAME1 | Vendor Name | |
| AccountingDocumentNumber_BELNR | Accounting Document Number | |
| TargetCurrency_TCURR | Target Currency | |
| CurrencyKey_WAERS | Currency Key | |
| AmountInLocalCurrency_DMBTR | Amount in Local Currency | |
| ClearingDate_AUGDT | Clearing Date | |
| DocumentNumberOfTheClearingDocument_AUGBL | Document Number of the Clearing Document | |
| PostingDateInTheDocument_BUDAT | Posting Date in the Document | |
| CashDiscountReceivedInSourceCurrency | Cash Discount Received in Source Currency | |
| CashDiscountReceivedInTargetCurrency | Cash Discount Received in Target Currency | |
| TargetCashDiscountInSourceCurrency | Target Cash Discount in Source Currency | |
| TargetCashDiscountInTargetCurrency | Target Cash Discount in Target Currency |