AccountsPayable
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - AccountsPayable
Overview
This view provides a consolidated view of accounting invoices and related data for reporting purposes. It combines data from multiple SAP tables to provide a comprehensive dataset for analysis and reporting.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.AccountingDocuments |
Accounting documents table containing invoice-related information such as document numbers, posting dates, amounts, and account details. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.InvoiceDocuments_Flow |
Invoice documents table containing invoice-specific information such as invoice document numbers, invoice dates, and vendor details. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CompaniesMD |
Company master data table containing company-related information such as company names and fiscal year variants. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.VendorsMD |
Vendor master data table containing vendor-related information such as vendor names and addresses. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.VendorConfig |
Vendor configuration table containing vendor-specific configuration settings. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PurchaseDocumentsHistory |
Purchase documents history table containing purchase order-related information such as purchasing document numbers, item numbers, and material document details. |
| CurrencyConversion | Currency conversion table containing exchange rate information for currency conversion. |
Key Use Cases
- Analyzing vendor payment patterns and trends.
- Identifying overdue and upcoming payments.
- Monitoring purchase order activity and matching it with invoice data.
- Performing cash flow analysis and forecasting.
- Providing insights for vendor management and payment optimization.
Query Logic
The query combines data from the source tables using a series of joins and filters. It first creates a common table expression (CTE) called CurrencyConversion to handle currency conversion based on the specified currency parameter. Then, it joins the AccountingDocuments and InvoiceDocuments_Flow tables to create a comprehensive dataset of accounting invoices. The query also includes calculations for key performance indicators (KPIs) such as overdue amount, outstanding but not overdue amount, and potential penalty. It leverages custom scalar functions (Fiscal_Period, Fiscal_Case1, Fiscal_Case2, and Fiscal_Case3) to determine fiscal periods based on company-specific fiscal year variants.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| FB03 | Display Document |
| FB60 | Enter Incoming Invoice |
| FBL1N | Display Line Items: Document Overview |
| FBL3N | Line Item Display: G/L Account |
| FBL5N | Line Item Display: Document |
| FK03 | Display Vendor Master Record |
| ME23N | Display Purchase Order |
| ME2L | Display Purchase Order History |
| SE16 | Table Display |
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 Name | |
| AccountNumberOfVendorOrCreditor_LIFNR | Vendor/Creditor Account Number | |
| NAME1 | Vendor/Creditor Name | |
| AmountInLocalCurrency_DMBTR | Amount in Local Currency | |
| AccountingDocumentNumber_BELNR | Accounting Document Number | |
| NumberOfLineItemWithinAccountingDocument_BUZEI | Line Item Number within Accounting Document | |
| DocumentNumberOfTheClearingDocument_AUGBL | Document Number of the Clearing Document | |
| TermsOfPaymentKey_ZTERM | Terms of Payment Key | |
| AccountType_KOART | Account Type | |
| ReasonCodeForPayments_RSTGR | Reason Code for Payments | |
| PaymentBlockKey_ZLSPR | Payment Block Key | |
| ClearingDate_AUGDT | Clearing Date | |
| PostingDateInTheDocument_BUDAT | Posting Date in the Document | |
| FiscalYear_GJAHR | Fiscal Year | |
| FiscalPeriod_MONAT | Fiscal Period | |
| DocFiscPeriod | Document Fiscal Period | |
| KeyFiscPeriod | Key Fiscal Period | |
| NetDueDate | Net Due Date | |
| InvStatus_RBSTAT | Invoice Status | |
| PostingDate_BUDAT | Posting Date | |
| PurchasingDocumentNumber_EBELN | Purchasing Document Number | |
| CurrencyKey_WAERS | Currency Key | |
| SupplyingCountry_LANDL | Supplying Country | |
| AccountingDocumenttype_BLART | Accounting Document Type | |
| InvoiceDocumenttype_BLART | Invoice Document Type | |
| MovementType__inventoryManagement___BWART | Movement Type | |
| POOrderHistory_AmountInLocalCurrency_DMBTR | Purchase Order History Amount in Local Currency | |
| POOrderHistory_AmountInTargetCurrency_DMBTR | Purchase Order History Amount in Target Currency | |
| YearOfPostingDateInTheDocument_BUDAT | Year of Posting Date in the Document | |
| MonthOfPostingDateInTheDocument_BUDAT | Month of Posting Date in the Document | |
| WeekOfPostingDateInTheDocument_BUDAT | Week of Posting Date in the Document | |
| QuarterOfPostingDateInTheDocument_BUDAT | Quarter of Posting Date in the Document | |
| AmountInTargetCurrency_DMBTR | Amount in Target Currency | |
| ExchangeRate_UKURS | Exchange Rate | |
| TargetCurrency_TCURR | Target Currency | |
| OverdueAmountInSourceCurrency | Overdue Amount in Source Currency | |
| OverdueAmountInTargetCurrency | Overdue Amount in Target Currency | |
| OutstandingButNotOverdueInSourceCurrency | Outstanding But Not Overdue in Source Currency | |
| OutstandingButNotOverdueInTargetCurrency | Outstanding But Not Overdue in Target Currency | |
| OverdueOnPastDateInSourceCurrency | Overdue On Past Date in Source Currency | |
| OverdueOnPastDateInTargetCurrency | Overdue On Past Date in Target Currency | |
| PartialPaymentsInSourceCurrency | Partial Payments in Source Currency | |
| PartialPaymentsInTargetCurrency | Partial Payments in Target Currency | |
| LatepaymentsInSourceCurrency | Late Payments in Source Currency | |
| LatePaymentsInTargetCurrency | Late Payments in Target Currency | |
| UpcomingPaymentsInSourceCurrency | Upcoming Payments in Source Currency | |
| UpcomingPaymentsInTargetCurrency | Upcoming Payments in Target Currency | |
| PotentialPenaltyInSourceCurrency | Potential Penalty in Source Currency | |
| PotentialPenaltyInTargetCurrency | Potential Penalty in Target Currency | |
| PurchaseInSourceCurrency | Purchase in Source Currency | |
| PurchaseInTargetCurrency | Purchase in Target Currency | |
| IsParkedInvoice | Is Parked Invoice | |
| IsBlockedInvoice | Is Blocked Invoice | |
| 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 | |
| AmountOfOpenDebitItemsInSourceCurrency | Amount Of Open Debit Items in Source Currency | |
| AmountOfOpenDebitItemsInTargetCurrency | Amount Of Open Debit Items in Target Currency | |
| AmountOfReturnInSourceCurrency | Amount Of Return in Source Currency | |
| AmountOfReturnInTargetCurrency | Amount Of Return in Target Currency |