User manual BUSINESS OBJECTS ACCOUNTS RECEIVABLE RAPID MART XI 3.2 FOR SAP SOLUTIONS BUSINESS GUIDE 12.2.0.0

[. . . ] SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2, version for SAP solutions - Business Guide Version 12. 2. 0. 0 October 2009 Copyright © Copyright 2009 SAP AG. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Trademarks All rights reserved. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries. [. . . ] The fact table for this section contains data for each financial document line item. Specifically, the table stores monetary amounts and quantities for each financial document line item. SAP solutions processes vendor related financial documents and stores them in two tables: BSID (Secondary Index for Open Customer Items) and BSAD (Secondary Index for Cleared Customer Items). When an invoice or other customer document is received, SAP solutions posts a corresponding financial document, to the BSID table. As these items are cleared (by payment, credit memo, etc. ) SAP solutions posts entries to the BSAD table. Therefore, these two tables are the source for Customer Items in the Rapid Mart. If the clearing procedure is separate from the invoice receipt procedure, SAP solutions keeps the payment and the invoice open until it posts the clearing. Thus, until the clearing procedure is complete you may have unapplied payments (or credits). When clearing an item, SAP solutions updates the item with the date and document number of the payment document (or the financial document that cleared the item). 24 SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide Subject Areas Customer Items Section 3 The Customer Items section loads data into fact table CUSTOMER_ITEM_FACT. This fact table contains data from the vendor related financial document line items (tables BSID and BSAD in SAP solutions). The Accounts Receivable Rapid Mart calculates past due amounts, amounts paid on time, amounts paid late, amounts due in the future, and other measures from the SAP solutions source tables. There is also a database view based on CUSTOMER_ITEM_FACT (called CUSTOMER_ITEM_AR_BALANCE_VIEW). Additionally, any records with containing dates before the year 1900 are loaded into the CUSTOMER_ITEM_FACT_BAD table. We reject these records because a date before the year 1900 is legal in SAP solutions but not in the Microsoft SQL-Server database, and would otherwise cause an error. Rapid Mart Processing Customer Items data is stored in the CUSTOMER_ITEM_FACT table of the target database. Additionally this section captures the following dimensional attributes and their associated dimension tables Attribute Name Accounting Clerk Aging Interval Business Area Chart Of Accounts Company Code Credit Control Area Currency Currency Conversion Rate Customer Customer By Company Debit Credit Indicator Document Type Fiscal Period GL Account By Company Dimension Table(s) ACCOUNTING_CLERK AGING_INTERVAL BUSINESS_AREA CHART_OF_ACCOUNTS COMPANY_CODE CREDIT_CONTROL_AREA CURRENCY CURRENCY_CONVERT_RATE CUSTOMER CUSTOMER_BY_COMPANY DEBIT_CREDIT_INDICATOR DOCUMENT_TYPE & DOCUMENT_CATEGORY FISC_PERIOD GL_ACCOUNT_BY_COMPANY SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide 25 3 Subject Areas Customer Items Section Attribute Name Monetary Currency Payment Block Payment Reason Code Payment Terms Posting Key Type Special GL Indicator Time Dimension Dimension Table(s) CURRENCY PAYMENT_BLOCK PAYMENT_REASON_CODE PAYMENT_TERM POSTING_KEY_TYPE SPECIAL_GL_INDICATOR TIME_DIM, TIME_DIM_WEEKLY Fact Table Fields The Customer Items table (CUSTOMER_ITEM_FACT) contains the following measures Note: Suffixes indicate the monetary currency of the measure. · · · LOC - Local or Company Currency TRX - Transaction or Document Currency UPDT - Update Currency Note: Intervals "A" through "G" are user definable aging periods set up in the Data Services job At the universe level, we use the currency conversion rates to obtain measures in a user-defined global currency. Column Name AMT_DISCNT_TAKEN_LOC AMT_DUE_IN_E_DAYS_LOC AMT_DUE_IN_F_DAYS_LOC AMT_DUE_IN_G_DAYS_LOC AMT_DUE_OVER_G_DAYS_LOC AMT_ELIGBL_FOR_DISCNT_TRX AMT_LOC Description Discount amount taken Amount due in interval "E" days in local (company) currency Amount due in interval "F" days in local (company) currency Amount due in interval "G" days in local (company) currency Amount due in more than interval "G" days in local (company) currency Amount of invoice that can be discounted as appropriate Amount in local (company) currency 26 SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide Subject Areas Customer Items Section 3 Column Name AMT_PAID_DISCNT_PER01_LOC Description Local currency amount paid during the first discount period (includes any discounts applicable) Local currency amount paid after the first and during the second discount period (includes any discounts applicable) Paid after due date Local currency amount paid before due date and after any discount periods (includes only non-timerelated discounts) Local currency amount paid by due date (includes any discounts applicable) Local currency amount past due more than interval "A" days Local currency amount past due more than interval "B" days Local currency amount past due more than interval "C" days Local currency amount past due more than interval "D" days Local currency amount paid more than interval "A" days late Local currency amount paid more than interval "B" days late Local currency amount paid more than interval "C" days late Local currency amount paid more than interval "D" days late Tax amount in local (company) currency Tax amount in document (transaction) currency Amount in document (Transaction) currency AMT_PAID_DISCNT_PER02_LOC AMT_PAID_LATE_LOC AMT_PAID_NET_LOC AMT_PAID_ON_TIME_LOC AMT_PAST_DUE_GT_A_LOC AMT_PAST_DUE_GT_B_LOC AMT_PAST_DUE_GT_C_LOC AMT_PAST_DUE_GT_D_LOC AMT_PD_GT_A_LATE_LOC AMT_PD_GT_B_LATE_LOC AMT_PD_GT_C_LATE_LOC AMT_PD_GT_D_LATE_LOC AMT_TAX_LOC AMT_TAX_TRX AMT_TRX SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide 27 3 Subject Areas Customer Items Section Column Name AMT_TTL_PAST_DUE_LOC AMT_UPDT AMT_WEIGHT_PAST_DUE_LOC Description Total Amount Past Due in local (Company) currency Update Currency Amt. Past Due times Number of Days Past Due Rapid Mart Data The Customer Items section includes customer transaction detail. CUSTOMER_ITEM_FACT table and associated dimension tables are organized as a star schema as shown below You can analyze Vendor Items along several dimensions: · · · · Accounting Clerk Aging Interval - user definable A/P Aging Intervals Business Area Company 28 SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide Subject Areas Customer Summary Section 3 · · · · · · · · · · · · · · · · · · · · · · · Currency Conversion Rates Monetary Currency Payment Block Payment Reason Fiscal Periods Time - By posting date and document creation dates Customer by Company - A snowflaked dimension that cross-references Customer with Company Customer Debit / Credit Indicator Special GL Indicator Payment Terms Document Type - Financial Document Type GL Account by Company - Also a snowflaked dimension. Includes both the regular and "special" GL account numbers from SAP solutions, crossreferenced by Company Code Posting Key "What is a current snapshot of my Accounts Receivables?" "Which fiscal periods show the slowest payment rates?" "Which customers are blocked for delivery?" "Which customers should I focus on to reduce the aging receivables?" "Do my best paying customers have the most favorable credit limit?" "Are my customers taking advantage of discounts offered on payment terms to pay early?" "Are my customers trending towards earlier or later payments?" "What are my top 25 open invoices (by invoice value)?" "Which customer payments or credit memos have not been applied to an outstanding receivable?" For the customer items section, typical queries include: Customer Summary Section The customer totals section of this Rapid Mart stores customer balances. The two fact tables for this section summarizes yearly, periodic, and quarterly customer activity totals. SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide 29 3 Subject Areas Customer Summary Section Information includes customer total debits, credits, and sales by fiscal year, quarter, and period. The first, carries the data in horizontal format with one row of information for each combination of customer, company code, and fiscal year. This mimics the organization of the source SAP solutions table (KNC1 - Customer Transaction Figures). [. . . ] This report has two tabs to allow for trend analysis by Period and by Quarter. Report SQL SELECT DISTINCT FISC_PERIOD. FISC_PERIOD, FISC_PERIOD. FISC_QUARTER, FISC_PERIOD. FISC_YEAR, FISC_PERIOD. FISC_YEAR_PERIOD, FISC_PERIOD. FISC_YEAR_QUARTER, CUSTOMER_SUMMARY_FACT_VR. CURR_ID_GBL, SUM(CUSTOMER_SUMMARY_FACT_VR. AMT_SALES_LOC * CURRENCY_CONVERT_RATE. EXCH_RATE / CURRENCY_CONVERT_RATE. FROM_FACTOR / CURRENCY_CONVERT_RATE. TO_FACTOR), COMPANY_CODE. COUNTRY_NAME, COMPANY_CODE. CMPNY_CODE_NAME FROM FISC_PERIOD, CUSTOMER_SUMMARY_FACT_VR, CURRENCY_CONVERT_RATE, COMPANY_CODE 60 SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide Reports Accounts Receivable Summary 4 WHERE ( CUSTOMER_SUMMARY_FACT_VR. FISC_YEAR=FISC_PERIOD. FISC_YEAR and CUSTOMER_SUMMARY_FACT_VR. FISC_PERIOD=FISC_PERIOD. FISC_PER IOD and CUSTOMER_SUMMARY_FACT_VR. FISC_YR_VARIANT_ID=FISC_PERIOD. FISC_YR_VARIANT_ID ) AND ( CUSTOMER_SUMMARY_FACT_VR. CMPNY_CODE_ID = COMPANY_CODE. CMPNY_CODE_ID ) AND ( CUSTOMER_SUMMARY_FACT_VR. EXCH_TYPE_ID_GBL = CURRENCY_CONVERT_RATE. EXCH_TYPE_ID AND CUSTOMER_SUMMARY_FACT_VR. CURR_ID_LOC = CURRENCY_CONVERT_RATE. CURR_ID_FROM AND CUSTOMER_SUMMARY_FACT_VR. CURR_ID_GBL = CURRENCY_CONVERT_RATE. CURR_ID_TO AND CUSTOMER_SUMMARY_FACT_VR. CREATE_DATE >= CURRENCY_CONVERT_RATE. VALID_FROM_DATE AND CUSTOMER_SUMMARY_FACT_VR. CREATE_DATE < CURRENCY_CONVERT_RATE. VALID_TO_DATE ) AND ( CUSTOMER_SUMMARY_FACT_VR. FISC_YEAR = CUSTOMER_SUMMARY_FACT_VR. PARTITION_YEAR ) AND FISC_PERIOD. FISC_YEAR_PERIOD BETWEEN '2001-P01' AND '2001-P15' GROUP BY FISC_PERIOD. FISC_PERIOD, FISC_PERIOD. FISC_QUARTER, FISC_PERIOD. FISC_YEAR, FISC_PERIOD. FISC_YEAR_PERIOD, FISC_PERIOD. FISC_YEAR_QUARTER, CUSTOMER_SUMMARY_FACT_VR. CURR_ID_GBL, COMPANY_CODE. COUNTRY_NAME, COMPANY_CODE. CMPNY_CODE_NAME Top 10 Customer AR Revenue Analysis View top 10 customers by revenue amount for selected fiscal years. The report uses the Webi report ranking feature for the top 10 analysis. It is easily customizable to allow for Top X sales. SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide 61 4 Reports Accounts Receivable Summary Top 10 Sales Overall View top 10 Sales according to revenue (LC Sales Ttl). 62 SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide Reports Accounts Receivable Summary 4 Top10 Sales - by Fiscal Year View top 10 Sales according to revenue (LC Sales Ttl) for each Fiscal Year. Report SQL All report SQL is located in the Rapid Mart installation directory under "\Sample\DML". SAP BusinessObjects Accounts Receivable Rapid Mart XI 3. 2 for SAP solutions Business Guide 63 4 Reports Accounts Receivable Summary AR Revenue Year Trend Comparison View revenue trend by period for selected Fiscal Years. Compare Total Amounts, Variance Amount versus Previous Period, Variance % Against Previous Period, and Variance % against Average. [. . . ]