AccountsPayable
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (ecc) - AccountsPayable
Overview
This view is a reporting view for SAP systems that provides insights into accounting invoices and related data. It combines data from various SAP tables to provide a comprehensive view of vendor invoices, parked invoices, purchase orders, and other relevant information. The view is designed to support analysis and reporting on vendor payments, cash discounts, overdue invoices, and other key metrics.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.AccountingDocuments |
Accounting documents table containing details of accounting documents, such as invoices, payments, and credit memos. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.InvoiceDocuments_Flow |
Invoice documents table containing details of invoice documents, such as invoice numbers, invoice dates, and invoice amounts. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CompaniesMD |
Companies master data table containing details of companies, such as company codes, company names, and fiscal year variants. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.VendorsMD |
Vendors master data table containing details of vendors, such as vendor names, vendor addresses, and vendor payment terms. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.VendorConfig |
Vendor configuration table containing vendor-specific configuration settings, such as low field values for potential penalty calculations. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PurchaseDocumentsHistory |
Purchase documents history table containing details of purchase orders and related documents, such as purchase order numbers, item numbers, and quantities. |
{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.CurrencyConversion |
Currency conversion table containing exchange rates for different currencies and conversion dates. |
Key Use Cases
- Analyze vendor payment patterns and identify potential savings opportunities through early payment discounts.
- Monitor overdue invoices and take necessary actions to prevent late payment penalties.
- Identify and track parked invoices that need to be processed.
- Gain insights into purchase order history and match it with invoice data for better reconciliation.
- Calculate potential penalty amounts based on vendor-specific configurations.
Query Logic
The view combines data from the source tables using a series of joins and unions. It applies filters and calculations to derive the desired metrics and attributes. The view also leverages custom SQL functions and subqueries to enhance the data and provide additional insights.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| FB03 | Display Document |
| FB60 | Enter Incoming Invoice |
| FBL1N | Display Line Items: Document Overview |
| FBL3N | Display Line Items: Due Date List |
| FBL5N | Display Line Items: Clearing Cockpit |
| ME23N | Display Purchase Order |
| XK03 | Display Vendor Master Record |
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 |