data used in document creation of sales overview

SAP BusinessObjects Sales Rapid Mart XI 3.2, version for SAP solutions - Business Guide

Version 12.2.0.0

October 2009

Copyright © Copyright 2009 SAP AG. All rights reserved.No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice.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. Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the United States and in other countries. Business Objects is an SAP company.All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary.These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.

Contents
Chapter 1 Introduction 7
What is a Rapid Mart? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8Rapid Marts packages accelerate Time to Value . . . . . . . . . . . . . . . . . 9SAP BusinessObjects Rapid Marts packages architecture . . . . . . . . . 10

About this document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Chapter 2 Overview 13Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14What you can do with this Rapid Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14Supported analyses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16Related Rapid Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Sharing components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18SAP BusinessObjects Sales Rapid Mart, version for SAP solutions schema 20Where the Rapid Mart fits in the customer order management cycle . . . . 21

Chapter 3 Subject Areas 23Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24Sales Order Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26Rapid Mart Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

Backlog Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

Schedule Lines Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Delivery Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

SAP BusinessObjects Sales Rapid Mart XI 3.2 for SAP solutions Business Guide 3

Contents

Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48Billing Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Sales Order History Preservation Section . . . . . . . . . . . . . . . . . . . . . . . . . . 57Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Document Flow Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62Rapid Mart Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62Rapid Mart Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

On Time Delivery Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66Rapid Mart Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Document Validation Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Rapid Mart Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Rapid Mart Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Chapter 4 Reports 75Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76Sales Orders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Deliveries and On-Time Delivery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

Billing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111Summary Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115Backlog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

Sample report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Schedule Lines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

4 SAP BusinessObjects Sales Rapid Mart XI 3.2 for SAP solutions Business Guide

Contents

Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125Sales Order History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129

Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

Document Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132Recommended Table Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135

Invalid Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137Recommended Table Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

SAP Master Data Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140Data Discovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140Data Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155


Contents


SAP BusinessObjects Sales Rapid Mart XI 3.2 for SAP solutions Business Guide

Introduction

chapter

IntroductionWhat is a Rapid Mart?1
What is a Rapid Mart?
SAP BusinessObjects Rapid Marts packages are blueprints for building data marts with SAP BusinessObjects technology: • Data Services• Universe Designer• Web Intelligence. Rapid Marts packages deliver jump-start ETL (extract, transform & load) mappings, schema, and initial reporting content, accelerating the deployment of BI (business intelligence) for enterprise applications from SAP, PeopleSoft, Oracle, and Siebel. Each Rapid Mart is designed to address the reporting needs of a specific business area (or department) like accounting, sales, or purchasing. A component-based framework allows conducting analysis across these selected business areas by combining different packages within the same source application suite.Rapid Marts focus on basic standard configuration of the enterprise applications. They are country and industry neutral templates meant to be easily modified and extended to suit customer specific application implementation and reporting needs.Rapid Marts incorporate best practices, and provide easily modifiable templates. With Rapid Marts total development time of a data mart solution is greatly reduced.Each customer situation is different. You will probably encounter one or more of these:• Customizations you have made to SAP solutions the Rapid Mart does not

cover. • Standard SAP solutions data fields important for your specific business,

but not included in the Rapid Mart templates.• Optimization and performance challenges unique to your environment. • Reporting requirements not covered within the Rapid Mart template

reports.Rapid Marts are flexible templates that you can adjust to account for these unique situations. It is expected that some customization and optimization will be needed to suit your individual environment. Rapid Marts are not an out-of-the-box solution.


IntroductionWhat is a Rapid Mart? 1

BI tools and analytic tools can access Rapid Mart data through SQL queries. Rapid Marts can be implemented individually, or in any combination, to form a single platform that delivers the infrastructure for your company’s internal and external information needs. They can also serve as a staging area for enterprise analytic applications.Rapid Marts provide your business with an accelerated time to value through rapid BI deployments because you can implement them quickly and easily customize them to meet specific analytic requirements.

Rapid Marts packages accelerate Time to ValueRapid Marts are packaged data solutions that you can quickly deploy to address specific areas of business analysis. Available Rapid Marts packages for SAP solutions include:Financial Suite:• SAP BusinessObjects General Ledger Rapid Mart, version for SAP

solutions• SAP BusinessObjects Accounts Payable Rapid Mart, version for SAP

solutions• SAP BusinessObjects Accounts Receivable Rapid Mart, version for SAP

solutions• SAP BusinessObjects Cost Center Rapid Mart, version for SAP solutionsOperational Suite:• SAP BusinessObjects Inventory Rapid Mart, version for SAP solutions • SAP BusinessObjects Purchasing Rapid Mart, version for SAP solutions• SAP BusinessObjects Sales Rapid Mart, version for SAP solutionsManufacturing Suite:• SAP BusinessObjects Plant Maintenance Rapid Mart, version for SAP

solutions • SAP BusinessObjects Production Planning Rapid Mart, version for SAP

solutions • SAP BusinessObjects Project Systems Rapid Mart, version for SAP

solutionsHuman Capital Management (HCM) Suite:• SAP BusinessObjects Human Resources Rapid Mart, version for SAP

solutions


IntroductionWhat is a Rapid Mart?1

You can combine multiple Rapid Marts packages into a single environment to build the foundation for your data warehouse or use them as a staging area for business intelligence applications.

SAP BusinessObjects Rapid Marts packages architectureRapid Marts architecture is driven by SAP BusinessObjects technology.Rapid Mart Architecture:

Rapid Marts packages include the following components:


IntroductionWhat is a Rapid Mart? 1

• Data Movement Jobs - packaged source-to-target mappings and data transformations. Each job is designed to perform initial and incremental data movement;

• Data Model & Schema - set of data mart database objects designed with dimensional data modeling approach. Rapid Marts packages for SAP solutions has a single integrated data model. The schema are available for Oracle, SQL Server IBM DB2, and Teradata;

• Semantic Layer (Universes) - SAP BusinessObjects metadata packages for efficient query generation. There can be one or more universes per Rapid Mart. Each universe is developed using Rapid Marts design principles to ensure compatibility, code readability, and component re-use. In addition, there is one Master universe for development/maintenance of multiple use objects (like Customer, Material, Currency, etc.);

• Reports (Samples) - set of 15-20 Web Intelligence reports per Rapid Mart. They represent answers to mostly asked business questions (for example, in the SAP BusinessObjects Sales Rapid Mart, version for SAP solutions - 'What is the monthly Revenue trend by Division for this year?'). The reports are developed using Rapid Mart color/layout templates. Reports are examples of Web Intelligence best practice development (trends, listing, guided analysis, roll ups and downs, etc.).


IntroductionAbout this document1
About this document
This document describes the SAP BusinessObjects Sales Rapid Mart, version for SAP solutions. This document contains information for a variety of users—information that helps you understand the use of the Rapid Mart, the data in the Rapid Mart, the reports you can create with the Rapid Mart, and how to use and update the Rapid Mart.This document contains the following chapters:• Chapter 2: Overview — Describes the business problems you can solve

and the types of analyses you can do with this Rapid Mart• Chapter 3: Subject Areas — Contains detailed information about each

section in the Rapid Mart, including the processes each section captures• Chapter 4: Reports — Provides examples of reports you can produce



Overview

chapter

OverviewOverview2
Overview
This chapter describes the business problems you can solve with the SAP BusinessObjects Sales Rapid Mart, version for SAP solutions and the types of analyses you can do with this Rapid Mart. The information in this chapter is useful for those who want a business-level overview of the Rapid Mart and its benefits. This chapter contains the following sections:• What you can do with this Rapid Mart• Supported analyses• Related Rapid Marts• SAP BusinessObjects Sales Rapid Mart, version for SAP solutions

schema• Where the Rapid Mart fits in the customer order management cycle

What you can do with this Rapid MartThe SAP BusinessObjects Sales Rapid Mart, version for SAP solutions supports analysis of data in the Sales and Distribution (SD) module of SAP solutions. This module allows businesses to manage products, sales, and services and to manage their performance with customers and other business partners. Using this Rapid Mart, you can: • Analyze sales trends• Determine customer behavior• Forecast product sales with greater accuracy • Plan product life cycles• Track deliveries you make to your customers• Track On Time Delivery Performance• Report Bookings, Billings, Backlog in SAP solutions Document Currency

or your Global CurrencyThe SAP BusinessObjects Sales Rapid Mart, version for SAP solutions addresses three sub-modules of SD:• Sales (SD-SLS)• Shipping (SD-SHP)• Billing (SD-BIL)


OverviewWhat you can do with this Rapid Mart 2

With the SAP BusinessObjects Sales Rapid Mart, version for SAP solutions you can answer the following kinds of questions:• How are new product features affecting sales?• How do sales vary by region?• What is the average delivery time for a product?• How well are we meeting our scheduled deliveries to customers?• How well are we planning our deliveries for our customers?• How do distribution, pricing, and promotional activities compare across

geographically diverse locations and various time periods?• Are we fulfilling our commitments to customers in a timely fashion?• What would our revenue have been if all originally entered sales orders

had been fulfilled?• What are the trends in my sales orders history?• What are my monetary exchange rate variances between my document

currency and Global currency?• How do my bookings (sales orders) compare to my billings in a given

time period?• How many credit memos are we issuing?• What impact do credit memos have on revenue?


OverviewSupported analyses2
Supported analyses
The SAP BusinessObjects Sales Rapid Mart, version for SAP solutions supports several types of analyses.

Business function

Types of analysis

Measures available in the Rapid Mart

Sales Orders (Bookings)

Sales • Sales order volumes and trends across time at summary level for the enterprise or any level down to individual line-item information

• Sales ranking by customer, product, sales representative, region, or sales type

• Detailed sales order analysis by customer, product, sales representative, region, sales type

• Customer requests fulfillment in terms of delivery dates and quantities

Returns • Sales orders of return category• Return trends over time by customer, product,

region, sales typeBacklog By Delivery • Un-delivered sales orders

By Billing • Un-billed sales orders

Schedule Lines Planning • Days Early or Late (Customer Requested Delivery Date vs. Planned Delivery Dates).

• Compare Returns vs. Sales (booking) TrendsDelivery Efficiency • Average order-to-delivery cycle time

• Cycle time patterns between order and delivery by customer, product, sales office

• Comparison of scheduled and actual material availability, loading, goods issue, and delivery dates

Service Level • Backlog quantities by customer, product, region• Completion status by customer, product, region• Shipped products for revenue recognition


OverviewSupported analyses 2

To support these analyses, the SAP BusinessObjects Sales Rapid Mart, version for SAP solutions contains a number of dimension tables. Tables extracted from SAP solutions master data and customizable tables include enterprise entities such as country, region, currency, plant, customer, and material data. Tables extracted from the SD module include pricing, organizational elements and their hierarchies (sales organization, division, distribution channel, sales office, sales group, sales person), and sales documents.

Billing Discounting • Trends for discounting by product over time for different customers

• Relationship between discounting and sales volumeMargins • Base price to discount price and product cost

comparisons to identify margins and time by productRevenue • Revenue over time by customer, product, sales

organizationsSales Order History

Sales order changes preservation

• What if revenue analysis• Deletion of sales orders by sales person, by product,

by customerDocument Flow Sales Order

Lifecycle• Unfulfilled Sales Orders• Sales Order fulfillment Lifecycle (sales order

creation to billing time)• Lifecycle by customer, product, sales representative,

region, sales typeOn Time Delivery By Schedule

Line or Delivery

• Average Days Delivery is Early or Late• Quantities Early or Late• Net Value Early or Late• On Time Delivery Performance

Invalid Documents by Sales Order or Billing

• Sales Orders rejected by the Data Services• Billings rejected by the Data Services

Business function

Types of analysis

Measures available in the Rapid Mart


OverviewRelated Rapid Marts2
Related Rapid Marts
Each Rapid Mart is composed of multiple components. A component is a stand-alone work flow that completes a particular task, such as loading a specific dimension table. Components can contain other components. A section is a set of components that address a particular business problem or subject area. A section is itself a component.Components of the SAP BusinessObjects Sales Rapid Mart, version for SAP solutions are related to other SAP BusinessObjects Rapid Mart components. For example:• Sales orders for make-to-order materials are included in the:

• SAP BusinessObjects Inventory Rapid Mart, version for SAP solutions - linked by material movements.

• SAP BusinessObjects Production Planning Rapid Mart, version for SAP solutions - linked by reservations, and production orders.

These linkages give a complete view of the manufacturing process.• Sales orders for Purchased materials are tracked in the:

• SAP BusinessObjects Purchasing Rapid Mart, version for SAP solutions. - linked by purchasing documents, goods receipts, and invoice receipts.

• SAP BusinessObjects Accounts Payable Rapid Mart, version for SAP solutions - linked by purchasing documents (financial view) and vendor financial documents.

• Tracking of customer payments for sales billings are included in the SAP BusinessObjects Accounts Receivable Rapid Mart, version for SAP solutions.

Sharing components The same components can be used in multiple Rapid Marts. For example, a component that extracts information about materials bought, produced, and sold is needed for a Rapid Mart that supports sales analysis and also for a Rapid Mart that supports inventory analysis. Work flows that extract star schema “dimensions” are components. You can add a component to any Rapid Mart using a simple import procedure. A Data Services job can include multiple instances of a component. For example, each section includes all the required dimension components. Therefore, a job with several sections may include several instances of a particular dimension component. Components are set to execute only once within a job. This “execute once” feature ensures that shared components do


OverviewRelated Rapid Marts 2

not cause duplicate data extraction from SAP solutions. For more information about the “execute once” feature, see the Data Integrator/Data Services Designer Guide.Each of the sections listed in “What you can do with this Rapid Mart” on page 14 are considered components. You can identify a component within an Data Services job by a “C_” prefix before its name. For example, the component that contains sales order and the associated reporting dimensions is named C_SalesOrder_Section.


OverviewSAP BusinessObjects Sales Rapid Mart, version for SAP solutions schema2
SAP BusinessObjects Sales Rapid Mart, version for SAP solutions schema
The following diagram shows an overview of the SAP BusinessObjects Sales Rapid Mart, version for SAP solutions Star Schema Layout. Tables in the center of the diagram are the fact tables and correspond to particular sections; the side boxes contain dimension tables that may apply to multiple sections.


OverviewWhere the Rapid Mart fits in the customer order management cycle 2

Additionally the Sales Rapid Mart includes the following hierarchy, staging, validation, and auxiliary tables that are not organized as a star schema:

Where the Rapid Mart fits in the customer order management cycle

The SAP solutions customer order management cycle consists of the following elements:• Pre-sales activities (such as quotations, inquiries, contracts, and

scheduling agreements)• Sales order processing• Inventory sourcing• Delivery• Billing• Payment


OverviewWhere the Rapid Mart fits in the customer order management cycle2

Other Rapid Marts (see “Related Rapid Marts” on page 18) fit into the Order Management Cycle as shown below:.



Subject Areas

chapter

Subject AreasOverview3
Overview
Each section in the SAP BusinessObjects Sales Rapid Mart, version for SAP solutions pertains to a particular subject area. This chapter describes each section and the processes each section captures. The information in this chapter is useful to understand the data in the Rapid Mart and how it relates to SAP solutions. This chapter discusses:• Sales Order Section• Backlog Section• Schedule Lines Section• Delivery Section• Billing Section• Sales Order History Preservation Section• Document Flow Section• On Time Delivery Section• Document Validation Section

Sales Order SectionA sales order is a document that captures and records customer commitment to purchase goods or services. The sales order contains all the information needed to process the customer commitment throughout the SD module.The SAP BusinessObjects Sales Rapid Mart, version for SAP solutions has no restrictions on sales document categories. The sales order section captures:• sales orders, • returns, • credit and debit memo requests, • pre-sales documents, such as

• quotes, • inquiries, • contracts, • scheduling agreements.

When creating a sales order, SAP solutions copies default information from the following:


Subject AreasSales Order Section 3

• Customer master• Material master• Condition records for pricing• Pre-set configuration for shipping point and routeSAP solutions can create a sales order that references another sales document, such as a quote, an inquiry, scheduling agreement, or a contract. SAP solutions can overwrite copied information for a particular sales order. SAP solutions supports many types of sales transactions within sales order processing, including:• Standard order — Product request from a customer with 'normal' delivery

requirements• Rush order — Product request from a customer with immediate delivery• Returns authorization — Product return from a customer• Delivery free of charge — Product delivered to a customer with no charge• Consignment — Product sent to a customer but paid for only as it is used• Service Contracts — Used by the SAP solutions Service Module• Quotation — Initial quote to a customer before sales order is created.

Can be turned into a sales order once the sale is agreed to.• Debit Memo Request — A precursor to a Debit Memo. These are

instructions for additional charges to a customer.• Credit Memo Request — A precursor to Credit Memo. These are

instructions to reduce charges to a customer.• Inquiries — A precursor to a quotation. A Customer can ask for detailed

information about specific goods, in particular their prices.The sales order section extracts order-related data from SAP solutions header and line item tables. The section stores the transactions for individual sales order documents in the SALES_ORDER_FACT table. This section does not store the history of data changes to sales orders.Sales order processing in Data Services for the Rapid Mart has two major branches. The first is the initial load. This is a full refresh of all sales orders in the SAP solutions system. Typically, you will run this refresh only once when you first implement the Rapid Mart.The second branch is the incremental or “delta” load of the sales orders. The Data Services keeps track of the run date for each Rapid Mart refresh, including the first. Subsequent refreshes load only those sales orders that have been added, deleted, or changed in SAP solutions since the previous refresh.


Subject AreasSales Order Section3

Note: The Rapid Mart treats deleted sales orders differently than SAP solutions. In SAP solutions the sales orders are physically deleted from the database. However, the Rapid Mart does not delete the records, but assigns them a reject code.Both the first and incremental refreshes use a staging table to prepare sales order information for the Rapid Mart. The table is named SALES_ORDER_STAGE. Data extracted from SAP solutions is loaded here first. Further transformations of the data take place in the stage table. These include lookup of Global Currency exchange rates and of the ship to address for each sales orderOnce the staging table is fully updated, the global currency exchange rates lookups are validated using a Validation transform in the Rapid Mart. Rows that do not validate are written to the SALES_ORDER_FACT_BAD table. Rows that pass validation update the SALES_ORDER_FACT table. See the Document Validation Section later in this chapter for more details.

Rapid Mart processingSales orders are stored in the SALES_ORDER_FACT table of the target database. Additionally, this section captures the following dimensional attributes and their associated dimension tables.

Attribute Dimension Table Names

Billing Block BILLING_BLOCKCentral Address CENTRAL_ADDRESSCompany Code COMPANY_CODEControlling Area CONTROLLING_AREACredit Control Area CREDIT_CONTROL_AREACredit Management Risk Class CREDIT_MGMT_RISK_CLASSMonetary Currency CURRENCYCustomer CUSTOMERCustomer Group CUSTOMER_GROUPDelivery Block DELIVERY_BLOCKDelivery Route DELIVERY_ROUTEDistribution Channel DISTRIBUTION_CHANNELDivision DIVISION



Fact Table FieldsThe SALES_ORDER_FACT table contains the following measures

Document Category DOCUMENT_CATEGORYDocument Type DOCUMENT_TYPE &

DOCUMENT_CATEGORYInter Company Terms INTER_COMPANY_TERM01Material & Product Hierarchy MATERIAL,

PRODUCT_HIERARCHYMaterial Alternate Unit of Measure MATERIAL_ALT_UOMMaterial By Plant MATERIAL_BY_PLANTMaterial Group MATERIAL_GROUPOrder Reason ORDER_REASONPayment Term PAYMENT_TERMPlant PLANTPricing Procedure (Horizontal & Vertical)

PRICING_PROCEDURE_HZ, PRICING_PROCEDURE_VR

Profit Center & Hierarchy PROFIT_CENTER, PROFIT_CENTER_HIER, PROFIT_CENTER_HIER_BAD, PROFIT_CENTER_HIER_HZ, PROFIT_CENTER_HIER_VR

Reject Reason REJECT_REASONSales District SALES_DISTRICTSales Group SALES_GROUPSales Office SALES_OFFICESales Order Status SALES_ORDER_STATUSSales Organization & Hierarchy SALES_ORGANIZATION,

SALES_ORGANIZATION_HIERSales Person SALES_PERSONShipping Point SHIPPING_POINTStorage Location STORAGE_LOCATIONTime Dimension TIME_DIMUnit Of Measure UNIT_OF_MEASURE

Attribute Dimension Table Names



Note: The suffix GBL is for Global Currency. The suffix TRX is for transaction (or Document) Currency.:

Col. Name Description

AMT_NET_PRICE_GBL VBAP-NETPR - Net Price -after discounts and surcharges are applied, but excluding tax - Global Currency

AMT_NET_PRICE_TRX VBAP-NETPR - Net Price -after discounts and surcharges are applied, but excluding tax - Transaction Currency

AMT_NET_VALUE_GBL VBAP-NETWR - Net value of the order line item in Global currency

AMT_NET_VALUE_HDR_GBL VBAK-NETWR - Net value of the order (all line items) in Global currency

AMT_NET_VALUE_HDR_TRX VBAK-NETWR - Net value of the order (all line items) in transaction currency

AMT_NET_VALUE_TRX VBAP-NETWR - Net value of the order line item in transaction currency

AMT_RECEIVED_GBL Calculated field: for sales documents of categories A or B it is 0, for sales documents of categories H or K without rejection reasons it is set to a negative of the net item value, for the rest sales documents without rejection reasons it is the net item value - In Global Currency

AMT_RECEIVED_TRX Calculated field: for sales documents of categories A or B it is 0, for sales documents of categories H or K without rejection reasons it is set to a negative of the net item value, for the rest sales documents without rejection reasons it is the net item value - In Transaction Currency

AMT_STD_COST_GBL VBAP-WAVWR - Cost in transaction currency

AMT_STD_COST_TRX VBAP-WAVWR - Cost in transaction currency

AMT_SUBTTL_01_GBL VBAP-KZWI1 - Customer-configurable subtotal 1

AMT_SUBTTL_01_TRX VBAP-KZWI1 - Customer-configurable subtotal 1













AMT_TRGT_GBL VBAP-ZWERT - Target value for outline agreement in transaction currency

AMT_TRGT_TRX VBAP-ZWERT - Target value for outline agreement in transaction currency

QTY_CONFIRM_SLSUOM VBAP-KBMENG - Cumulative confirmed quantity in sales unit

QTY_ORDER_SLSUOM VBAP- KWMENG - Cumulative Order Quantity in Sales Units

QTY_PRICE_UNIT VBAP-KPEIN - Condition pricing unitQTY_RECEIVED_SLSUOM Calculated field: for sales documents of

categories A or B it is 0, for sales documents of categories H or K without rejection reasons it is set to a negative of the sales qty, for the rest sales documents without rejection reasons it is the sales qty

QTY_TRGT_SLSUOM VBAP-ZMENG - Target quantity in sales units




Status fields (for the whole document and for each line item) for tracking sales orders include:

Other important attributes (fields) of the SALES_ORDER_FACT table are:


BILL_STATUS_ID VBUP-FKSAA - Billing Status of ORder-Related BIlling Document

BILL_STATUS_ID_HDR VBUK-FKSAK - Billing Status of ORder-Related Billing Document (lookup)

DELIV_STATUS_ID VBUP-LFGSA - Overall Delivery Status of the Item

DELIV_STATUS_ID_HDR

VBUK-LFGSK - Overall Delivery Status of the Item (lookup)

INCOMPLT_STATUS_ID VBUP-UVALL - General Incompletion Status of the Item

INCOMPLT_STATUS_ID_HDR

VBUK-UVALL - General Incompletion Status of the Item

PROCESS_STATUS_ID VBUP-GBSTA - Overall Processing Status of the SD item

PROCESS_STATUS_ID_HDR

VBUK-GBSTK - Overall Processing Status of the SD item (lookup)

REF_STATUS_ID VBUP-RFGSA - Overall status of referenceREF_STATUS_ID_HDR VBAK-RFGSK - Total reference status of all

itemsREJECT_STATUS_ID VBAP-ABSTA - Rejection status for SD itemREJECT_STATUS_ID_HDR

VBUK-ABSTK - Rejection status

SALES_DOC_STATUS_KEY

Concatenated values of Sales Document Status fields


CURR_ID_GBL Global Currency (Global Variable Set in Rapid Mart Job)

CURR_ID_STATS VBAK-STWAE - Statistics currencyCURR_ID_TRX VBAP-WAERK - Line Item transaction currencyCURR_ID_TRX_HDR VBAK-WAERK - Document header Currency

code



CUSTOMER_ID_BILLTO

VBPA-KUNNR - Bill-to Customer Number (lookup)

CUSTOMER_ID_SHIPTO

VBPA-KUNNR - Ship-to Customer Number (lookup)

CUSTOMER_ID_SOLDTO

VBAK-KUNNR - Sold-to Customer Number

EXCH_RATE_GBL TCURR.UKURS - Exchange rate from transaction currency to Global Currency

EXCH_RATE_STATS VBAP-STCUR - Exchange rate for statistics (Exch.rate at time of creation)

EXCH_TYPE_ID_GBL Global Exchange Rate Type (Global Variable Set in Rapid Mart Job)

EXCH_TYPE_ID_STATS

VBAK.KURST - Exchange rate type

SALES_TO_BASE_DIVISOR

VBAP-UMVKN - Denominator (divisor) for conversion of sales qty. into Stock-keeping unit of measure

SALES_TO_BASE_MULTIPLR

VBAP-UMVKZ - Numerator (factor) for conversion of sales quantity into Stock-keeping unit of measure

VALID_FROM_DATE VBAK-GUEBG - Valid-from date (outline agreements, product proposals)

VALID_TO_DATE VBAK-GUEEN - Valid-to date (outline agreements, product proposals)



Rapid Mart Data
The sales order section includes sales transaction detail. The SALES_ORDER_FACT stores these details. SALES_ORDER_FACT and associated dimensions are organized as a star schema as shown below:



AnalysisWith the tables in this section, you can analyze sales orders along several dimensions:• By material• By customer• By sales organization elements• By order reason• By rejection reason• By billing or delivery blocking codes• By various sales order statuses• By profit center and associated hierarchy• By various sales order operational dimensions• By time period vs. previous period, quarter vs. previous quarter, year vs.

previous yearTypical queries include:• What was the top selling product in the line last quarter, last year?• Who is our top sales rep. in the company?• What are our sales trends in this region for the last 5 years?• How have our sales numbers per sales office changed since the sales

groups reassignments?• How well do we honor our contract obligations?• Who are our 10 biggest customers by bookings value?• How do my sales vary seasonally (or quarterly)?• Which customers are most satisfied / dissatisfied (as measured by

returns)?• Which materials (or products) are my best sellers?• Which products are not well received by my customers (as measured by

slow sales and high rate of returns)?• Which factories (or plants) are processing the most bookings?• Which operating company had the most sales this quarter? This year?• What are the gross margins on each product line we sell?• Which sales district generated the most bookings last year?• Did our bookings numbers look better using last year’s sales organization

structure or this years?• How do my bookings compare to my billings for this quarter? This year?


Subject AreasBacklog Section3
Backlog Section
The backlog section stores backlog quantities for open sales orders. The section calculates the backlog as the difference between booked quantities and shipped quantities. All backlog processing takes place in the Rapid Mart. It is important that Sales Orders, Deliveries, and Billings be loaded before backlog processing, as backlog relies on data from those subject ares. The backlog section calculates backlog using the SALES_ORDER_FACT and DELIVERY_FACT tables. Additionally, in the case of drop shipment or intangible delivery orders, the backlog section also uses the BILLING_FACT table.For a sales order item line item, the backlog is the quantity that has not been delivered yet (the ordered quantity less the delivered quantity.) The backlog section stores this data in the BACKLOG_FACT table. The section uses several rules when calculating backlog: • Includes sales orders of category 'C.'

The Rapid Mart makes use of the fact that a delivery line item refers to one and only one sales order line item.

• Excludes contracts and scheduling agreements.Contracts are sales orders that eventually turn into category 'C' orders. You can customize the Rapid Mart to include contracts and scheduling agreements by allowing categories 'E' and 'G.'

• Excludes billed line items with no delivery information.Note: Drop Shipments / Intangible DeliveriesSome companies use drop shipments (third-party delivery). In this case, a sales order line item has the “Billing Relevant” indicator set to “B” or “F”. No Delivery document is generated in your SAP solutions system (as the third party does that). However, an invoice is produced and backlog is reduced by the invoice rather than the delivery. The Billing Relevant indicator is also set to “B” or “F” in cases of an intangible delivery. That is a delivery where no physical material is delivered, such as when a service is purchased.If you use drop shipments or intangible deliveries, set the $G_DROP_SHIP global variable to “Y” (yes). The Rapid Mart will segregate these orders and reduce backlog when invoices are generated for these orders (ordered quantity less invoice quantity).


Subject AreasBacklog Section 3

With every incremental load, the section removes deleted or cancelled orders from the BACKLOG_FACT table. It also removes orders that are fully delivered and therefore no longer backlog.As an alternative backlog calculation you can use SALES_DOC_FLOW_VIEW. The view represents a union of SALES_ORDER_FACT, DELIVERY_FACT, and BILLING_FACT. It has fact table common attribute fields such as sales order number, customer id, material id, sales document category id. In addition, it contains fact table measurement fields, such as sales order requested quantity, net item value, delivery quantity, billing document quantity and net item value. Aggregating by common fields you can calculate the difference between ordered, delivered, and billed quantities and amounts by sales order number, by customer, by material.

Rapid Mart processingBacklog is stored in the BACKLOG_FACT table. Which is associated with the following dimensional attributes and dimension tables. Additionally, in the universe, BACKLOG_FACT is joined to SALES_ORDER_FACT as both tables have the same primary key. This allows the use of any Sales Order dimension with Backlog.

Attribute Dimension Table Name

Company Code COMPANY_CODEControlling Area CONTROLLING_AREACustomer CUSTOMERMaterial & Product Hierarchy

MATERIAL, PRODUCT_HIERARCHY

Material By Plant MATERIAL_BY_PLANTPlant PLANTProfit Center & Hierarchy PROFIT_CENTER, PROFIT_CENTER_HIER,

PROFIT_CENTER_HIER_BAD, PROFIT_CENTER_HIER_HZ, PROFIT_CENTER_HIER_VR

Storage Location STORAGE_LOCATIONTime Dimension TIME_DIM


Subject AreasBacklog Section3
Fact Table Fields
The Backlog table includes the following measures:

Rapid Mart dataBACKLOG_FACT and associated dimensions are organized as a star schema as shown below.


QTY_BACKLOG_SLSUOM • Regular Backlog: SALES_ORDER_FACT.QTY_ORDER_SLSUOM - DELIVERY_FACT.QTY_DELIV_SLSUOM

• Third Party (Drop Ship) Backlog: SALES_ORDER_FACT.QTY_ORDER_SLSUOM - BILLING_FACT.QTY_BILL_SLSUOM

QTY_DELIV_SLSUOM DELIVERY_FACT.QTY_DELIV_SLSUOM - Actually Delivered Qty

QTY_ORDER_SLSUOM SALES_ORDER_FACT.QTY_ORDER_SLSUOM - Cumulative Sales Order Qty


Subject AreasBacklog Section 3

AnalysisWith the dimensions in this section, plus sales order dimensions you can analyze backlog along several dimensions:• By customer• By material• By sales organization elements• By period vs. previous period, quarter vs. previous quarter, year vs.

previous year• By any sales order attribute.Typical queries include:• What was ordered but not delivered yet, listed by customer and product?• What was ordered, delivered but not billed yet by plant?• What is the gap between ordered and delivered products in this quarter?• Which products have the greatest delivery backlog?• Who is the most unsatisfied customer in terms of unfulfilled orders?• Where is my greatest revenue potential as measured by backlog? By

customer? By Sales Organization? By material?• How old is my backlog? (How long are customers waiting for delivery?)All dimensions associated with sales orders can be used with backlog. Refer to the Sales Order Section -Analysis in this chapter for more details.


Subject AreasSchedule Lines Section3
Schedule Lines Section
The schedule lines section extracts detailed information about scheduled deliveries for sales orders. In SAP solutions, all sales documents have the same structure—a document header with any number of items. The items can have any number of schedule lines.

A schedule line contains all the data needed for a delivery. For example, suppose a customer orders 20 units of a particular material. You enter this as one item in the sales order. Suppose, however, that you can only deliver 15 pieces now. The customer wants the remaining 5 pieces next month. In this case, you need to schedule two deliveries. SAP solutions stores the data for these deliveries (dates, confirmed quantities, and so forth) in two separate schedule lines. These schedule lines differ by delivery date and quantity.Schedule lines can also have different control elements. For example, material requirements planning might be completed for some schedule lines but not other schedule lines. Similarly, schedule lines defined in a returns document post a goods receipt, not a goods issue. Data recorded in schedule lines includes:• Schedule line quantity• Schedule Line Date • Delivery Date (also called Planned Delivery Date in SAP solutions)• Goods Issue Date (also called Planned Goods Issue Date in SAP

solutions)• Sales Order Create Date • Requested Delivery Date (from the sales order)


Subject AreasSchedule Lines Section 3

• Loading Date• Material Available Date• Transportation Planning Date• Confirmed Quantity• Order Quantity• Rounded Quantity• Required QuantityFor sales documents where delivery data is not relevant—for example, in contracts, credit and debit memo requests—SAP solutions does not create schedule lines.The schedule lines section extracts detailed scheduled delivery information for sales orders from SAP solutions table VBEP into the SALES_SCHED_LINE_FACT table. This table only contains information for relevant sales orders. Some sales documents, such as inquiries, quotations, and contracts, do not contain delivery data, and therefore have not schedule lines. The section uses the same approach SAP solutions uses for handling deleted schedule lines information. When SAP solutions removes an existing sales order or sales order line item and all related schedule lines, the Rapid Mart removes the corresponding records from the schedule line fact table.This section only extracts current information. This section does not store historical information.The section uses an initial full load strategy, followed by subsequent incremental (delta) loads. Only schedule lines added, changed, or deleted since the previous Rapid Mart refresh are loaded in an incremental load.

Rapid Mart processingSchedule Lines include the following dimensional attributes and associated dimension tables:


Delivery Block DELIVERY_BLOCKMaterial & Product Hierarchy MATERIAL,

PRODUCT_HIERARCHYMaterial Alternate Unit of Measure MATERIAL_ALT_UOMMaterial by Plant MATERIAL_BY_PLANTMaterial Group MATERIAL_GROUP



Fact Table FieldsThe schedule lines include the following measures:

These are additional important attributes for the Schedule Line:

Additional information on these attributes:• DELIV_DATE (also called Planned Delivery Date in SAP solutions)

A requested delivery date or confirmed delivery date. This is the date you expect the customer to receive the goods.

Plant PLANTTime Dimension TIME_DIMUnit of Measure UNIT_OF_MEASURE


Column Name Description

DAYS_EARLY_LATE Difference between Requested Deliv. Date (VBAK.VDATU) and Planned Goods Issue Date (VBEP.WADAT). A negative number indicates Late Delivery. A positive number indicates Early Delivery.

QTY_ADJUST_SLSUOM VBEP-CMENG - Corrected quantity in sales unit

QTY_CONFIRM_BASEUOM Confirmed quantity in Base UnitsQTY_CONFIRM_SLSUOM VBEP-BMENG - Confirmed quantity - sales

unitsQTY_ORDER_BASEUOM Order quantity in Base UnitsQTY_ORDER_SLSUOM VBEP-WMENG - Order quantity in sales units


DELIV_DATE VBEP-EDATU - Delivery date for the schedule line

GI_DATE VBEP-WADAT - Goods issue date

MTRL_AVAIL_DATE VBEP-MBDAT - Material availability date

RQST_DELIV_DATE VBAK-VDATU - Proposed schedule line date

SCHED_LOAD_DATE VBEP-LDDAT - Scheduled Loading date

TRANSPORT_SCHED_DATE VBEP-TDDAT - Transportation scheduling date


Subject AreasSchedule Lines Section 3

For most sales orders, SAP solutions creates schedule lines automatically. When you enter a requested delivery date for a sales order or a sales order line item, SAP solutions runs a material availability check to calculate a confirmed delivery date. If the requested quantity can be delivered on the requested date, SAP solutions creates one schedule line. If the requested quantity cannot be delivered as requested, SAP solutions creates multiple schedule lines. The first line contains the requested delivery date in the DELIVERY_DATE field. Subsequent lines have confirmed delivery dates in this field.

• GI_DATE (also called Planned Goods Issue Date in SAP solutions)The date when the goods should leave the shipping point. Calculated by adding loading lead time to the loading date.

• MTRL_AVAIL_DATE The date when the goods are available and the delivery process can begin.

• RQST_DELIV_DATEThe requested delivery date from the sales order header. This is used to calculate the delivery date (above). It is also used in measuring how well we are planning vs. customer requests.

• SCHED_LOAD_DATEThe date when the goods are scheduled to be loaded for transport. SAP solutions calculates by adding the pick, pack, and stage lead times to the material availability date.

• TRANSPORT_SCHED_DATEThe date when transportation planning is scheduled to start. Calculated by subtracting transportation lead time from the load date.


Rapid Mart data
SALES_SCHED_LINE_FACT and associated dimensions are organized as a star schema as shown below

AnalysisWith the tables in this section, you can analyze scheduled deliveries for sales orders along several dimensions:• By material and its product hierarchy• By customer• By sales organization elements• By time period vs. previous period, quarter vs. previous quarter, or year

vs. previous year.Typical queries include:• How many sales orders can not meet the customer’s requested delivery

date?• How often do we need to schedule multiple deliveries for a single order

line item because we do not have enough quantity in stock to satisfy the demand?

• How well are we planning to meet customer requested delivery date?• When are our deliveries scheduled? (By month, quarter, or year)• What are our average days early (or late) meeting customer requested

delivery dates?


Subject AreasDelivery Section 3

Delivery SectionThis section extracts information about deliveries, return deliveries, shipments, and shipping notifications from SAP solutions.Creating the delivery document signals the start of the shipping activities for the sales order. It is the electronic means to manage all the activities of delivery processing, including picking product, packing, planning and monitoring shipments, preparing shipping papers, and goods issue documents. Delivery includes several shipping processes that take place within the plant. When the product is available and ready to be processed in the warehouse, a shipping scheduler creates a delivery document so that the warehouse can efficiently pick, stage, and release (goods issue) the product to the customer. When creating a delivery document that references a sales order, SAP solutions copies critical shipping information from the sales order into the delivery document. SAP solutions can overwrite some of this information (such as quantities to deliver) on a particular delivery document.The delivery section extracts delivery information from the SAP solutions tables at the header (Table LIKP) and line-item levels (table LIPS). The section stores the extracted data in a fact table called DELIVERY_FACT. The section extracts all categories of delivery documents, including delivery, returns delivery, shipment, and shipping notification. The section stores the latest version of each delivery. This section does not store the history of data changes to deliveries.The Rapid Mart has an initial (first) load and an incremental (delta) load. Unlike the Sales Orders section, this load does not use a Stage table. Both the initial and incremental loads are direct to the Delivery Fact table.Note: This section follows the convention of SAP solutions for handling deleted delivery information. Existing deliveries or delivery line items that are deleted within SAP solutions are also removed from the delivery fact table.Note: The Rapid Mart does not maintain global currency values for deliveries. However, the SAP BusinessObjects Universe calculates global currency values using the exchange rates from the associated sales order.


Subject AreasDelivery Section3
Rapid Mart processing
Deliveries include the following dimensional attributes and associated dimension tables.

Attribute Name Dimension Table

Billing Block BILLING_BLOCKCompany Code COMPANY_CODEControlling Area CONTROLLING_AREACredit Control Area CREDIT_CONTROL_AREACredit Mgmt Risk Class CREDIT_MGMT_RISK_CLASSMonetary Currency CURRENCYCustomer CUSTOMERCustomer Group CUSTOMER_GROUPDelivery Block DELIVERY_BLOCKDelivery Priority DELIVERY_PRIORITYDelivery Route DELIVERY_ROUTEDelivery Status DELIVERY_STATUSDistribution Channel DISTRIBUTION_CHANNELDivision DIVISIONDocument Category DOCUMENT_CATEGORYDocument Type DOCUMENT_TYPE &

DOCUMENT_CATEGORYInter Company Terms INTER_COMPANY_TERM01Material & Product Hierarchy MATERIAL,

PRODUCT_HIERARCHYMaterial Alternative Unit of Measure MATERIAL_ALT_UOMMaterial By Plant MATERIAL_BY_PLANTMaterial Group MATERIAL_GROUPPlant PLANTPricing Procedure (Horizontal & Vertical)

PRICING_PROCEDURE_VR, PRICING_PROCEDURE_HZ



Fact Table FieldsDeliveries contain the following measures:

Profit Center & Hierarchy PROFIT_CENTER, PROFIT_CENTER_HIER, PROFIT_CENTER_HIER_BAD, PROFIT_CENTER_HIER_HZ, PROFIT_CENTER_HIER_V

Sales Group SALES_GROUPSales Office SALES_OFFICESales Organization & Sales Organization Hierarchy

SALES_ORGANIZATION & SALES_ORGANIZATION_HIER

Shipping Point SHIPPING_POINTStorage Location STORAGE_LOCATIONTime Dimension TIME_DIMUnit Of Measure UNIT_OF_MEASURE



AMT_STD_COST_TRX LIPS-WAVWR - Cost in Document Currency

AMT_SUBTTL_01_TRX LIPS-KZWI1 - Customer-Configurable Subtotal 1






QTY_DELIV_BASEUOM LIPS-LGMNG - Actual Quantity Delivered (in Base Units)

QTY_DELIV_SLSUOM LIPS-LFIMG - Actual Quantity Delivered (in Sales Units)

QTY_GROSS_WEIGHT LIPS-BRGEW - Gross weight



Note: AMT_SUBTTL_01_TRX - AMT_SUBTTL_06_TRX: These subtotals (in sales document currency) contain the values of certain pricing conditions used in a specific pricing procedure. Typically, Subtotal 1 is the List Price. Price calculation in SAP solutions depends on various factors such as: • Customer• Sales order type• Sales organization• QuantityEach delivery can have its own definition for what is contained in subtotal 1-6 fields. The descriptions of these fields are in the Pricing Procedure tables.

Additional important fields in the DELIVERY_FACT table are:

QTY_NET_WEIGHT LIPS-NTGEW - Net weightQTY_VOLUME LIPS-VOLUM - Volume


Status Columns Values: • Blank = Not Applicable• A = Not yet processed • B = Partially processed • C = Completely ProcessedBILL_STATUS_DELIV_RELTD_ID VBUP-FKSTA - Billing Status of

Delivery-Related Billing DocumentsDELIV_STATUS_KEY Concatenated values of Sales

Document Status fieldsGI_STATUS_ID VBUP-WBSTA - Goods Issue status

(indicates whether goods have left the warehouse)

PACKNG_STATUS_ID VBUP-PKSTA - Packing statusPICKNG_CONFIRM_STATUS_ID VBUP.KOQUA - Picking confirmation

statusPICKNG_STATUS_ID VBUP-KOSTA - Picking StatusPROCESS_STATUS_ID VBUP-GBSTA - Overall processing

status of the SD document itemDate/Day Columns




ACTL_GI_DATE LIKP.WADAT_IST - Actual Goods Issue Date. The date the goods actually left the shipping point.

DAYS_FIXED_PROCESS LIPS-VBEAF - Fixed Shipping Processing Time in Days (setup time). This field contains a fixed shipping processing time in days. This time is needed in shipping to process a specific quantity of a material in base units. An example is the time required to load a van that transports the material to a customer

DAYS_SHIP_PROCESS LIPS-VBEAV - Variable Shipping Processing Time in Days. This field contains the processing time in days for shipping a particular quantity of an item. It is used for capacity planning for deliveries. Formula:Confirmed_Qty x Processing_Time / BASE_QTY

DELIV_DATE LIKP-LFDAT - Delivery Date. The date by which the delivery should be received by the customer.

DELIV_LOAD_DATE LIKP-LDDAT - Loading Date. The date by which picking and packing must be completed so that the goods are ready for loading and for the delivery to the customer

MTRL_AVAIL_DATE LIPS-MBDAT - Material Availability Date. The date by which sufficient quantities of the item must be available for picking to begin

PICKNG_DATE LIKP-KODAT - Picking Date. The date by which picking must begin for the delivery item to reach the customer on time.

PLAN_GI_DATE LIKP-WADAT - Planned Goods Issue Date. The date on which the goods must physically leave the shipping point to reach the customer on time.



Rapid Mart data
The Delivery Fact and its associated dimensions form a Star Schema as shown below:

AnalysisWith the tables in this section, you can analyze deliveries along several dimensions:• By material• By customer• By sales organization elements• By region• By plant• By billing or delivery blocking codes• By delivery priority



Typical queries include:• Which customers did not receive deliveries on the requested date?• What is the average delivery time by product, by customer?• How big are my shipments in terms of volume and weight monthly?• What is the value of my deliveries, in global currency or document

currency?• How long between planned goods issue and actual goods issue?• Which deliveries are in process right now?


Subject AreasBilling Section3
Billing Section
The billing section extracts billing information from SAP solutions. The section extracts information about invoices, pro forma invoices, credit memos, and debit memos. After a material is shipped, an invoice is sent to the customer and a billing document is created. In the SD module, order processing ends with billing. SAP solutions creates a billing document by copying billing information from the sales order and/or delivery document. For example, copied information includes the name of the business partner who receives and verifies the invoice address, the name of the business partner who pays the invoice, the address and payment terms, and material and delivered quantity. SAP solutions can overwrite some of the copied data, such as price and quantity, for a particular billing document.Typically, when creating a billing document, SAP solutions automatically updates general ledger accounts. For example, SAP solutions debits the customer receivable account and credits the revenue account.The billing section extracts billing information from SAP solutions at header (table VBRK) and line item level (table VBRP). The section stores the extracted data in a fact table called BILLING_FACT. The section extracts all categories of billing documents, including invoices, credit and debit memos, pro-forma items, intercompany invoices, and intercompany credit memos. It also captures any cancellation documents such as a cancelled credit memo or invoice. The section stores the latest version of each billing document. The section does not store changes as a billing document history. Billings have an initial full load where all billing records are transferred from SAP solutions. Subsequent loads are incremental (Delta) loads where only changed, or cancelled billing documents are loaded into the Rapid Mart. Like sales orders, billings are first loaded to a BILLING_STAGE table. Here transforms such as the lookup of Global Currency exchange rates occur.Once the staging table is fully updated, these changes are validated using a Validation transform in the Rapid Mart. Rows that do not validate are written to the BAD_BILLING_FACT table. Rows that do pass validation update the BILLING_FACT table. See the Document Validation Section later in this chapter for more details.


Subject AreasBilling Section 3

Rapid Mart processingBillings include the following dimensional attributes and associated dimension tables:

Attribute Name Dimension Tables

Company Code COMPANY_CODEControlling Area CONTROLLING_AREACredit Control Area CREDIT_CONTROL_AREAMonetary Currency CURRENCYCustomer CUSTOMERCustomer Group CUSTOMER_GROUPDistribution Channel DISTRIBUTION_CHANNELDivision DIVISIONDocument Category DOCUMENT_CATEGORYDocument Type DOCUMENT_TYPE &

DOCUMENT_CATEGORYFiscal Period FISC_PERIODInter Company Terms INTER_COMPANY_TERM01Material & Product Hierarchy MATERIAL,

PRODUCT_HIERARCHYMaterial Alternative Unit of Measure MATERIAL_ALT_UOMMaterial By Plant MATERIAL_BY_PLANTMaterial Group MATERIAL_GROUPPayment Term PAYMENT_TERMPlant PLANTPricing Procedure PRICING_PROCEDURE_HZ,

PRICING_PROCEDURE_VRProfit Center & Hierarchy PROFIT_CENTER,

PROFIT_CENTER_HIER, PROFIT_CENTER_HIER_BAD, PROFIT_CENTER_HIER_HZ, PROFIT_CENTER_HIER_V

Sales Document Status SALES_DOC_STATUSSales Group SALES_GROUPSales Office SALES_OFFICE



Fact Table FieldsBillings contain the following measures:

Sales Organization & Sales Organization Hierarchy

SALES_ORGANIZATION & SALES_ORGANIZATION_HIER

Shipping Point SHIPPING_POINTStorage Location STORAGE_LOCATIONTime Dim TIME_DIMUnit Of Measure UNIT_OF_MEASURE



AMT_NET_VALUE_GBL VBRP-NETWR - Net Value (Global Currency)

AMT_NET_VALUE_HDR_GBL Net value in document currency (Global Currency)

AMT_NET_VALUE_HDR_TRX VBRK-NETWR - Net Value of the billing document in Document Currency

AMT_NET_VALUE_TRX VBRP-NETWR - Net value of the line item in document currency

AMT_REVENUE_GBL Calculated field (Global Currency): for sales documents of categories U or 5 or 6 it 0, for sales documents of categories N or O it set to a negative of the net item value, for the rest sales documents it the net item value

AMT_REVENUE_TRX Calculated field: for sales documents of categories U or 5 or 6 it 0, for sales documents of categories N or O it set to a negative of the net item value, for the rest sales documents it the net item value

AMT_STD_COST_GBL VBRP-WAVWR - Cost in Global CurrencyAMT_STD_COST_TRX VBRP-WAVWR - Cost in Document

CurrencyAMT_SUBTTL_01_GBL VBRP-KZWI1 - Customer Configurable

Subtotal 1 (Global Currency)AMT_SUBTTL_01_TRX VBRP-KZWI1 - Customer-Configurable

Subtotal 1



Note: AMT_SUBTTL_01_TRX - AMT_SUBTTL_06_TRX and AMT_SUBTTL_01_GBL- AMT_SUBTTL_06_GBL: These subtotals (in transaction or global currency) contain the values of certain pricing conditions used in a specific pricing procedure. Typically, Subtotal 1 is the List Price. Price calculation in SAP solutions depends on various factors such as: • Customer• Sales order type• Sales organization• Quantity

AMT_SUBTTL_02_GBL VBRP-KZWI2 - Customer Configurable Subtotal 2 (Global Currency)

AMT_SUBTTL_02_TRX VBRP-KZWI2 - Customer-Configurable Subtotal 2

AMT_SUBTTL_03_GBL VBRP-KZWI3 - Customer Configurable Subtotal (Global Currency)








QTY_BILL_SLSUOM Calculated field = QTY_BILL_STOCKUOM X SALES_TO_BASE_DIVISOR /SALES_TO_BASE_MULTIPLR

QTY_BILL_STOCKUOM VBRP-FKLMG - Billing Qty in Stock-keeping Unit of Measure




Each billing can have its own definition for what is contained in subtotal 1-6 fields. The descriptions of these fields are in the Pricing Procedure tables.

Additional important fields in the BILLING_FACT table are:


BILL_DATE VBRK.FKDAT - Billing Date or Invoice Date.BILL_DOC_ID_CANCEL VBRK-SFAKN - Cancelled billing document

number. This field references either an invoice, a credit memo, or a credit memo list that has been cancelled by the current billing document. This field is only populated for billing documents of type cancellation,

DOC_CANCEL_FLAG Calculated field - If the Billing document line item is cancelled it is set to “1”

PROCESS_STATUS_ID VBUP-GBSTA - Overall Processing Status of the SD item. This field allows you to monitor whether a line item is open, being processed, or complete.

REF_DOC_ID VBRP-VGBEL - Document number of the reference document (allows you to monitor a referenced delivery.)

REF_LINE_ITEM_ID VBRP-VGPOS - Item number of the reference item (allows you to monitor a referenced delivery.)



Rapid Mart dataThe following diagram shows the billing tables organized as a star schema.

AnalysisWith the tables in this section, you can analyze billing documents along several dimensions:• By material• By customer• By payer (can be different from Customer who placed a sales order)• By sales organization elements• By plant• By billing document type• By period vs. previous period, quarter vs. previous quarter, year vs.

previous year via Fiscal Period Dimension• By period vs. previous period, quarter vs. previous quarter, year vs.

previous year via Time DimensionTypical queries include:



• What is the average time between booking a sales order and issuing a bill by customer and product?

• What are discount/margin trends by customer and product?• What is the company revenue by month, quarter, and year?• What is the difference between bookings and expected revenue?• What are my gross margins? By material? By factory (plant)?• Which customers have the most credit memos? • What are billings by company code? By Sales Office?• How do foreign currency exchange variances affect the bottom line?• Are there seasonal variations in my revenue (Billings) flow?• Who are my top 10 customers by revenue (Billings generated)?


Subject AreasSales Order History Preservation Section 3

Sales Order History Preservation SectionThe sales order history preservation section tracks changes to sales orders. By using this section, you can avoid querying SAP solutions’s transaction log tables (CDHDR - the change document header table and CDPOS - the change line item table) to determine changes. In most cases, querying SAP solutions transaction logs is very resource intensive. Therefore, most SAP solutions sites avoid such queries. All Sales Order History Preservation processing takes place in the Rapid Mart.The sales order history preservation section extracts changes to a sales order and stores the changes in the SALES_ORDER_HISTORY table. In particular, the section stores these changes:• A sales order (or a sales order line item) is deleted and the requested

quantity of the deleted line items and their net value become 0.• The quantity of a sales order line item is increased or reduced.• The line item net value is increased or reduced.There are limitations to the captured changes:• When multiple changes occur in the same field of a sales order between

two incremental loads, the Rapid Mart only captures the net effect of all the changes.

• The Rapid Mart does not detect deleted line items for free goods (goods with a net item value of 0) because the SAP solutions sales order header table does not change when such line items are deleted.

• The Rapid Mart does not capture sales orders that are created and deleted between two incremental loads.

See Chapter 8: Sales order history preservation section for more details about how the section captures changes.

Rapid Mart processingSales Order History includes the following dimensional attributes and associated dimension tables:


Company Code COMPANY_CODEControlling Area CONTROLLING_AREACurrency CURRENCY


Subject AreasSales Order History Preservation Section3

Fact table fieldsSales Order History contains the following measures:

Customer CUSTOMERPlant PLANTProfit Center & Hierarchy PROFIT_CENTER,

PROFIT_CENTER_HIER, PROFIT_CENTER_HIER_BAD, PROFIT_CENTER_HIER_HZ, PROFIT_CENTER_HIER_V

Sales Organization SALES_ORGANIZATIONSales Person SALES_PERSONStorage Location STORAGE_LOCATIONTime Dim TIME_DIMUnit Of Measure UNIT_OF_MEASURE



AMT_NET_VALUE_DELTA_TRX Calculated change in net item value since last run. (A negative number indicates a decrease in net value)

AMT_NET_VALUE_TRX VBAP-NETWR - Net Value of the Order Line Item in Document Currency for validity dates shown on this record. This is set to zero on a deleted record.

QTY_ORDER_DELTA_SLSUOM Calculated change in quantity since last run. (A negative number indicates the quantity decreased on the sales order.)

QTY_ORDER_SLSUOM VBAP- KWMENG - Cumulative Order Quantity In Sales Units for validity dates shown on this record. This is set to zero on a deleted record.



Additional important fields in Sales Order History are:

Example: history for a sales order: Original order is for 10 items at 10.00 each. (Total net value 100.00.) This is followed by a change in quantity, then a change in price, and finally order cancellation.

The initialization values of these fields in the First Load (or for the new rows in incremental load) is configured with the $G_INIT_HIST_CHG global variable: they can be set to 0 if you want to be able to sum and obtain the total changes made to the orders, or they can be set to the values of the QTY_RECEIVED_SLSUOM and AMT_RECEIVED_TRX fields if you want to obtain the current values by summing.See Chapter 8: Sales order history preservation section for details about how this global variable is used.


CURRENT_FLAG C = Current version, H = Historical version (older version)

SURROGATE_KEY_ID A surrogate primary key generated by Data Services. (The natural primary key would be composed of the SALES_ORDER_NUMBER, SALES_LINE_ITEM_ID, and VALID_FROM_DATE columns.)

VALID_FROM_DATE Calculated date - Starting Validity Date for this Record

VALID_TO_DATE Calculated date - Ending Validity Date for this Record

Surro-gate Key Id

Current Flag

Valid From Date

Valid To Date

Qty Order Slsuom

Amt Net Value Trx

Qty Order Delta Slsuom

Amt Net Value Delta Trx

Comment

1 H 01-Jan-2009

03-Jan-2009

10 100.00 10 100.00 Original Order

2 H 04-Jan-2009

09-Jan-2009

7 70.00 -3 -30.00 Quantity Decreased by 3

3 H 10-Jan-2009

16-Jan-2009

7 84.00 0 14.00 Price Increased by 2.00

4 C 17-Jan-2009

31-Dec-9999

0 0.00 -7 -84.00 Order Cancelled


Subject AreasSales Order History Preservation Section3
Rapid Mart data
The sales order history preservation section complements the sales order section with an additional fact table as shown below:

AnalysisYou can analyze deleted or changed sales orders along several dimensions:• By customer• By sales organization• By sales person• By time period vs. previous period, quarter vs. previous quarter, or year

vs. previous year



Typical queries posed with the Sales Order History table include:• How many sales orders or line items have been deleted per customer?• Which sales person has the most sales orders or line items deleted?• What would our revenue have been if all deleted sales orders and line

items had been delivered and the quantities of other orders and line items had not been reduced?

• Which time periods show the most deleted sales orders?• Which companies or plants are most affected by order changes and

deletions?


Subject AreasDocument Flow Section3
Document Flow Section
The Document Flow is a view (SALES_DOC_FLOW_VIEW) in the Rapid Mart database creates a union of information from Sales Orders, Deliveries, and Billings. This section allows you to see query related sales order, delivery and billing documents. You can see the entire order fulfillment lifecycle. This view has fact tables common attribute fields such as sales order number, customer id, material id, sales document category id. In addition, it contains fact table measurement fields, such as sales order requested quantity, net item value, delivery quantity, billing document quantity and net item value. Aggregating by common fields you can trace sales orders history. For example what quantities and amounts were delivered and billed for each sales order. All Document Flow processing takes place in the Rapid Mart.The Rapid Mart simply contains a database view that creates a union of the 3 fact tables (SALES_ORDER_FACT, DELIVERY_FACT, & BILLING_FACT) together. The join is on the Sales Order Number and Sales Order Line Item. These two fields are the primary key to the Sales Orders Fact, and are foreign keys in Delivery Fact and Billing Fact.

Rapid Mart ProcessingDocument Flow includes the following dimensional attributes and associated dimension tables:


Company Code COMPANY_CODEControlling Area CONTROLLING_AREACurrency CURRENCYCustomer CUSTOMERDistribution Channel DISTRIBUTION_CHANNELDivision DIVISIONDocument Category DOCUMENT_CATEGORYMaterial MATERIALMaterial Alternative Unit of Measure MATERIAL_ALT_UOMMaterial By Plant MATERIAL_BY_PLANTPlant PLANT


Subject AreasDocument Flow Section 3

View FieldsThe Document Flow View contains the following measures:

Profit Center & Hierarchy PROFIT_CENTER, PROFIT_CENTER_HIER, PROFIT_CENTER_HIER_BAD, PROFIT_CENTER_HIER_HZ, PROFIT_CENTER_HIER_V

Sales District SALES_DISTRICTSales Group SALES_GROUPSales Office SALES_OFFICESales Organization & Hierarchy SALES_ORGANIZATION,

SALES_ORGANIZATION_HIERSales Person SALES_PERSONStorage Location STORAGE_LOCATION



AMT_BILL_NET_VALUE_GBL Net value (Global Currency)AMT_BILL_NET_VALUE_TRX Net value in document currencyAMT_BILL_REVENUE_GBL Calculated field: for sales

documents of categories U or 5 or 6 it is 0, for sales documents of categories N or O it is set to a negative of the net item value, for the rest sales documents it is the net item value - Global Currency

AMT_BILL_REVENUE_TRX Calculated field: for sales documents of categories U or 5 or 6 it is 0, for sales documents of categories N or O it is set to a negative of the net item value, for the rest sales documents it is the net item value - Transaction Currency

AMT_SALES_NET_VALUE_GBL Net value in Global currencyAMT_SALES_NET_VALUE_TRX Net value in document currency


Subject AreasDocument Flow Section3

AMT_SALES_RECEIVED_GBL Calculated field: for sales documents of categories A or B it is 0, for sales documents of categories H or K without rejection reasons it is set to a negative of the net item value, for the rest sales documents without rejection reasons it is the net item value - Global Currency

AMT_SALES_RECEIVED_TRX Calculated field: for sales documents of categories A or B it is 0, for sales documents of categories H or K without rejection reasons it is set to a negative of the net item value, for the rest sales documents without rejection reasons it is the net item value - Transaction Currency

QTY_BILL_SLSUOM Calculated field as QTY_INVOICED * SLS_TO_BASE_DIVISOR / SLS_TO_BASE_NUMERATOR


QTY_ORDER_SLSUOM Cumulative Order Quantity in Sales Units



Subject AreasDocument Flow Section 3

Rapid Mart DataThe Document Flow Union View links the Sales Orders, Deliveries, and Billings as shown below

AnalysisDocument Flow allows you analyze along several dimensions:• By bill-to, ship-to, or sold-to customer• By sales order create date• By sales organization attributes• By material and factory attributes• By financial organization attributes.• By sales organization attributes• By Profit center and hierarchyTypical queries on document flow include:• How many deliveries were issued for this sales order?• What is the average number of deliveries for a sales order?


Subject AreasOn Time Delivery Section3

• Which sales orders are delivered, but not yet invoiced?• What is the value of deliveries or billings vs. the booked value of the sales

orders?

On Time Delivery SectionThis section uses information derived from the Schedule Lines and Deliveries sections described earlier in this chapter. On Time Delivery allows you to track how well you are delivering to schedule by calculating the days a delivery is early or late. On Time Delivery focuses on a subset of Schedule Line and Delivery information. The idea is focus on the deliveries that are due in the near future or near past. The default range is 15 days past and 15 days future. The range is configured with global variable $G_ONTIME_DELIV_WINDOW.On Time Delivery also has a history table that keeps a copy of all previous runs. This allows you to see how delivery schedules have changed over time. Example:

your current snapshot of On Time Delivery shows a sales order delivered on schedule. But when you examine the history table you find the sales order was rescheduled several times over a period of weeks and was originally planned to be delivered weeks earlier. By this measure the delivery is late.

All On Time Delivery processing occurs in the Rapid Mart. Sales Orders, Schedule Lines, and Deliveries must be loaded before On Time Delivery processing can occur. Data from the Schedule Lines is merged with data from the deliveries. The delivery quantity is allocated to the schedule line.When there is more than one schedule line or more than one delivery (or both) for an item, we distribute the total delivered quantity for the item across the schedule lines. The earliest schedule line is assigned the quantity from the earliest delivery.Example:

A sales order item has three schedule lines, each with an order quantity of 50 pieces. You make two deliveries: the first for 20 pieces, the second for 60 pieces. The delivered quantity allocated to the first schedule line is 50 pieces (20 from the first delivery and 30 from the second delivery). This leaves 30 pieces remaining on the second delivery, which is allocated to the second schedule line. The allocation quantity for the third schedule line is zero.


Subject AreasOn Time Delivery Section 3

If you delete the first delivery (20 pieces), the delivered quantity for this schedule line is still 50 pieces, but now this allocated from the second delivery. There are still 10 pieces available on the second delivery to further allocate. The system allocates the remaining 10 pieces to the next schedule line. The third schedule, still has zero allocated.

The tables below illustrate this example:

With Both Deliveries:

With First Delivery Deleted:

The Rapid Mart also calculates if a delivery is on time, late, or early. The formula uses the Planned Goods Issue Date from the Schedule Line and the Actual Goods Issue Date from the Delivery. These correspond to the SAP solutions fields VBEP.WADAT and LIKP.WADAT_IST, respectively. If the Planned Goods Issue Date is in the past and there is no Delivery, we use the current system date instead to the Actual Goods Issue Date.The formula is:

Days Early or Late =

Sched Line Number

Delivery Number

Sched Line Qty

Delivery Qty

Allocated Delivery Qty

First Delivery1 1 50 20 202 1 50 20 03 1 50 20 0

Second Delivery1 2 50 60 302 2 50 60 303 2 50 60 0

Sched Line Number

Delivery Number

Sched Line Qty

Delivery Qty

Allocated Delivery Qty

Second Delivery Only1 2 50 60 502 2 50 60 103 2 50 60 0



Planned Goods Issue Date - Actual Goods Issue DateA late delivery will be a negative number, and an early delivery will be a positive number. Zero indicates an on time delivery.Each schedule line is also assigned a delivery status based on these two dates.• EARLY - The Actual Goods Issue Date is before the Planned Goods

Issue Date• ON TIME - Actual Goods Issue Date and Planned Goods Issue Date are

the same• LATE - Actual Goods Issue Date is after the Planned Goods Issue Date• DND - (Did Not Deliver) Planned Goods Issue Date is in the Past and

there is no Delivery. This is a missed delivery.• N/A - (Not Applicable) Planned Goods Issue Date is in the future and

there is no Delivery. No delivery is due yetThe Rapid Mart also relies on a global variable that sets the date range window. The default setting is 15 days. This tells the system to look only at schedule lines that have a Planned Goods Issue Date 15 days in the past or future from today. This allows you to focus on the immediate deliveries due or past due. This date range is expandable, but keep in mind that the loading job will run longer as the window expands.The final feature in On Time Delivery is the ability to focus on changes between runs. The Rapid Mart initially loads data to a staging table and compares this stage table to the results of the last run. A change status is assigned by doing this comparison:• NEW - The schedule line / delivery combination was not included in the

previous run, but is in the current run. This may be because the record is new in the SAP solutions system or because, until now, the Planned Goods Issue Date was outside the date range set by the global variable. On the very first run of On Time Delivery all records will be assigned status “NEW”.

• CHANGE - The schedule line / delivery information has changed since the previous run. A change in any of the fields in the On Time Delivery fact table when compared to the previous run will generate this change status.

• NO CHANGE - The schedule line / delivery information has not changed since the previous run.

• DELETED - The schedule line / delivery combination was in the previous run, but not the current run. This may be because of physical deletion of records in SAP solutions, or because the Planned Goods Issue Date has changed and is now outside the date range set by the global variable.



Rapid Mart ProcessingOn Time Delivery has two fact tables. • ONTIME_DELIVERY_FACT - A picture of the current delivery status

within the On Time Delivery window.• ONTIME_DELIVERY_HISTORY - Historical snapshots of previous runs

of On Time Delivery. The contents of ONTIME_DELIVERY_FACT are appended to ONTIME_DELIVERY_HISTORY before each refresh of the data.

On Time Delivery & On Time Delivery History include the following dimensional attributes and associated dimension tables:

On Time Delivery and On Time Delivery History contain the following Measures


Monetary Currency CURRENCYCustomer CUSTOMERDelivery Block DELIVERY_BLOCKDistribution Channel DISTRIBUTION_CHANNELDivision DIVISIONInter Company Terms INTER_COMPANY_TERM01Material & Product Hierarchy MATERIAL,

PRODUCT_HIERARCHYMaterial Alternative Unit of Measure MATERIAL_ALT_UOMMaterial By Plant MATERIAL_BY_PLANTPlant PLANTSales Office SALES_OFFICESales Organization SALES_ORGANIZATIONSales Person SALES_PERSONTime Dimension TIME_DIM


AMT_NET_PRICE_GBL VBAP-NETPR - Net PriceAMT_NET_PRICE_TRX VBAP-NETPR - Net Price



DAYS_EARLY_LATE Calculated - difference between Sched. Goods Issue Date and Actual Goods Issue Date. A positive number indicates an early delivery. A negative number indicates a late delivery.

QTY_ALLOCTN_SLSUOM Calculated - Sched Line Quantity allocated to the Delivery


QTY_ORDER_SLSUOM VBAP- KWMENG - Cumulative Order Quantity in Sales Units

QTY_SCHED_CONFIRM_SLSUOM Schedule line confirmed quantity




Rapid Mart dataThe On Time Delivery section is composed of two fact tables ONTIME_DELIVERY_FACT and ONTIME_DELIVERY_HISTORY. There is also a staging table called ONTIME_DELIVERY_STAGE. The fact tables share the same set of dimensions. They are organized as a star schema as shown below:

AnalysisWith the tables in this section, you can analyze billing documents along several dimensions• By Material• By Plant• By Division, Distribution Channel, or Sales Organization• By Sales Person• By Inter-Company TermsTypical queries include:


Subject AreasDocument Validation Section3

• How is my On Time delivery performance?• Are we meeting delivery schedules?• Which customers are getting the most late deliveries?• Which plants are producing the most late deliveries?• Which plants are delivering early? (may indicate unused capacity).• What sales orders must I prepare to ship in the next week? Month?• Which sales orders need to be expedited because they did not ship as

scheduled?

Document Validation SectionThe Data Services load job validates that sales orders and billings meet certain validation criteria. The Rapid Mart validates both sales orders and billings for accurate exchange rates and conversion of monetary values to global currency. Invalid records are not loaded into the Sales Orders Fact or Billing Fact tables. Instead, they are loaded to two special tables that can be queried in the SAP BusinessObjects Universe. You must make corrections to the sales order or billings in SAP solutions and then re-load the Rapid Mart with the corrected records. All validation occurs in the Rapid Mart.The Rapid Mart uses the Data Services validation transform to check that documents in the BILLING_STAGE and SALES_ORDER_STAGE tables have correct global currency and exchange rates. Documents passing this check are loaded to the BILLING_FACT and SALES_ORDER_FACT tables. Documents failing this check are loaded to the BILLING_FACT_BAD and SALES_ORDER_FACT_BAD instead.

Rapid Mart Processing

The Rapid Mart data consists of two tables• SALES_ORDER_FACT_BAD• BILLING_FACT_BAD

They have these associated dimensions

Attribute Table Names

Customer CUSTOMERMaterial MATERIALPlant PLANT


Subject AreasDocument Validation Section 3

Fact Table FieldsSALES_ORDER_FACT_BAD and BILLNG_FACT_BAD tables are almost identical to the SALES_ORDER_FACT and BILLING_FACT tables. The differences are:

• They each include two additional fields:• ETL_ERROR_ACTN - An error message describing the why the

validation failed• ETL_ERROR_COLUMN - Lists the column names that caused

validation to fail• The primary keys also includes the LOAD_DATE (includes load date

and timestamp). This allows multiple versions of the invalid documents can be loaded and gives you an audit trail of how often the same document has failed validation.

Rapid Mart DataThe Validation tables for Sales Orders and Billings can be organized as star schemas with the same dimensions as the Sales Order Fact and Billing Fact tables: “Rapid Mart Data” on page 32 for Sales Orders“Rapid Mart data” on page 55 for Billings.

AnalysisYou can analyze the invalid documents by several dimensions. • By Material and Product Hierarchy• By Plant• By Sales Organization, Division, Distribution Channel• By Document (or Transaction) Currency• By Global Currency

Sales Organization SALES_ORGANIZATIONDivision DIVISIONDistribution Channel DISTRIBUTION_CHANNELMaterial By Plant MATERIAL_BY_PLANT

Attribute Table Names


Subject AreasDocument Validation Section3

Typical queries include:• Which sales orders and billings failed validation?• What errors caused the validation to fail?• Which fields caused the validation to fail?



Reports

chapter

ReportsOverview4
Overview
You can use the SAP BusinessObjects Sales Rapid Mart, version for SAP solutions to produce many kinds of reports. This chapter provides examples of reports you could generate using the Sales Rapid Mart universe. The information in this chapter is useful for those who design reporting solutions for Sales Analysis.This chapter provides reports and the recommended joins for each section in the Rapid Mart:• Sales Orders• Deliveries and On-Time Delivery• Billing• Backlog• Schedule Lines• Sales Order History• Document Flow• Invalid Documents• SAP Master Data ReportsNote that all report SQL is located in the Rapid Mart installation directory under "\Sample\DML".

Sales OrdersThe sales order section captures sales orders, returns, credit and debit memo requests, and pre-sales documents, such as quotes, inquiries, contracts, and scheduling agreements.

ReportsUsing the sales order information, you can complete numerous analyses and generate different reports. For example, reports you can generate include:• Sales Order Discount Analysis • Number of Deals Trend Analysis • Bookings vs Returns• Returns Trend Analysis• Top 10 Bookings• Booking Trends


ReportsSales Orders 4

• Top Sales Reps by Quarter

Sales Order Discount AnalysisThis report analyzes sales order discounts by Product Hierarchy, Company Code, and Sales Rep.

By Company CodeView overall trend summaries by Company Code based on net value and also on Discount %. The report also includes a section for each Company Code with detailed discount information.


ReportsSales Orders4

By Product HierarchyView the Top 10 discounts by Product Hierarchy Level 1. This report uses the Web Intelligence ranking feature to limit displayed results to Top 10 by Discount %..

By Sales RepView Peer Performance and detailed discount information for each Sales Rep.



Report SQLAll report SQL is located in the Rapid Mart installation directory under "\Sample\DML".

Number of Deals Trend AnalysisThis report analyzes Number of Deals trends; providing a high-level summary analysis and detailed analysis by Sales Rep and Company Code

SummaryView dashboard-style summary for Number of Deals for the entire enterprise.

Report SQLSELECT COMPANY_CODE.CMPNY_CODE_NAME, SALES_ORGANIZATION.SALES_ORG_DESCR, DIVISION.DIVISION_DESCR, sum(SALES_ORDER_FACT.AMT_SUBTTL_01_GBL), SALES_PERSON.SALES_PERSON_FIRST_NAME, SALES_PERSON.SALES_PERSON_LAST_NAME, sum(SALES_ORDER_FACT.AMT_SUBTTL_01_GBL -

SALES_ORDER_FACT.AMT_NET_VALUE_GBL), TIMEDIM_CREATE_DATE.CALENDAR_YEAR, sum(SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL), MATERIAL.MTRL_TYPE_DESCR, CUSTOMER_SOLDTO.CUSTOMER_NAME_01,



CUSTOMER_SOLDTO.CUSTOMER_NAME_02, TIMEDIM_CREATE_DATE.CALENDAR_QUARTER, PRODUCT_HIERARCHY.LVL_01_DESCR, CURRENCY_GLOBAL.CURR_ISO_CODE, avg(SALES_ORDER_FACT.QTY_ORDER_SLSUOM), sum(SALES_ORDER_FACT.QTY_ORDER_SLSUOM), sum(SALES_ORDER_FACT.AMT_NET_VALUE_GBL -

SALES_ORDER_FACT.AMT_STD_COST_GBL), case when SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL < 200 then 'Small

Order (less than 200.00)' else case when SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL >= 200 and

SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL < 500 then 'Medium Order(200.00 to 499.99)' else

case when SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL >= -30 and SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL < 0 then '1 - 30 Days Late' else

case when SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL >= 500 then 'Large Order (500.00 or more)' else

'N/A' end end end end , COUNT( DISTINCT SALES_ORDER_FACT.SALES_DOC_ID)FROM COMPANY_CODE, SALES_ORGANIZATION, DIVISION, SALES_ORDER_FACT, SALES_PERSON, TIME_DIM TIMEDIM_CREATE_DATE, MATERIAL, CUSTOMER CUSTOMER_SOLDTO, PRODUCT_HIERARCHY, CURRENCY CURRENCY_GLOBALWHERE (

SALES_ORDER_FACT.CREATE_DATE=TIMEDIM_CREATE_DATE.CALENDAR_DATE )

AND ( SALES_ORGANIZATION.SALES_ORG_ID=SALES_ORDER_FACT.SALES_ORG_ID )

AND ( SALES_PERSON.SALES_PERSON_ID=SALES_ORDER_FACT.SALES_PERSON_ID )

AND ( DIVISION.DIVISION_ID=SALES_ORDER_FACT.DIVISION_ID ) AND (

CUSTOMER_SOLDTO.CUSTOMER_ID=SALES_ORDER_FACT.CUSTOMER_ID_SOLDTO )

AND ( SALES_ORDER_FACT.MATERIAL_ID=MATERIAL.MATERIAL_ID ) AND (

COMPANY_CODE.CMPNY_CODE_ID=SALES_ORDER_FACT.CMPNY_CODE_ID )

AND ( MATERIAL.PROD_HIER_ID=PRODUCT_HIERARCHY.PROD_HIER_ID ) AND ( CURRENCY_GLOBAL.CURR_ID=SALES_ORDER_FACT.CURR_ID_GBL

) AND ( SALES_ORDER_FACT.SALES_DOC_ID<> '?' ) AND (



( (SALES_ORDER_FACT.DOC_CATEG_ID= 'C' or SALES_ORDER_FACT.DOC_CATEG_ID= 'I') ) AND TIMEDIM_CREATE_DATE.CALENDAR_YEAR In @prompt('Calendar

Year:','N','Doc. Creation Date Time Dim\Calendar Year',Multi,Free,Persistent,,User:0)

)GROUP BY COMPANY_CODE.CMPNY_CODE_NAME, SALES_ORGANIZATION.SALES_ORG_DESCR, DIVISION.DIVISION_DESCR, SALES_PERSON.SALES_PERSON_FIRST_NAME, SALES_PERSON.SALES_PERSON_LAST_NAME, TIMEDIM_CREATE_DATE.CALENDAR_YEAR, MATERIAL.MTRL_TYPE_DESCR, CUSTOMER_SOLDTO.CUSTOMER_NAME_01, CUSTOMER_SOLDTO.CUSTOMER_NAME_02, TIMEDIM_CREATE_DATE.CALENDAR_QUARTER, PRODUCT_HIERARCHY.LVL_01_DESCR, CURRENCY_GLOBAL.CURR_ISO_CODE, case when SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL < 200 then 'Small

Order (less than 200.00)' else case when SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL >= 200 and

SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL < 500 then 'Medium Order(200.00 to 499.99)' else

case when SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL >= -30 and SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL < 0 then '1 - 30 Days Late' else

case when SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL >= 500 then 'Large Order (500.00 or more)' else

'N/A' end end end end HAVING sum(SALES_ORDER_FACT.AMT_SUBTTL_01_GBL -

SALES_ORDER_FACT.AMT_NET_VALUE_GBL) <> 0

Bookings vs ReturnsThis report analyzes company Bookings vs Returns trends by Company Code and Plant for selected Calendar Years. This report uses two synchronized queries, one to retrieve Bookings data and the other for Returns data. Common dimensions are merged and the Net Value amount from each query is used in the report.



Returns Trend AnalysisView Returns Trend Analysis for a selected set of Calendar Years by:• Company Code• Sales Org• Plant



Top 10 Bookings
View summary of top bookings for the enterprise over the selected Calendar Year, including Top 10:• Customers• Sales Reps• ProductsThis report uses the Web Intelligence ranking feature to limit displayed results to Top 10.

Report SQLSELECT TIMEDIM_CREATE_DATE.CALENDAR_QUARTER, TIMEDIM_CREATE_DATE.CALENDAR_YEAR, CUSTOMER_SOLDTO.CUSTOMER_NAME_01, CUSTOMER_SOLDTO.CUSTOMER_NAME_02, SALES_PERSON.SALES_PERSON_FIRST_NAME, SALES_PERSON.SALES_PERSON_LAST_NAME,



sum(SALES_ORDER_FACT.AMT_NET_VALUE_HDR_GBL), PRODUCT_HIERARCHY.LVL_01_DESCRFROM SALES_ORDER_FACT, TIME_DIM TIMEDIM_CREATE_DATE, SALES_PERSON, CUSTOMER CUSTOMER_SOLDTO, PRODUCT_HIERARCHYWHERE (

SALES_ORDER_FACT.CREATE_DATE=TIMEDIM_CREATE_DATE.CALENDAR_DATE )

AND ( SALES_PERSON.SALES_PERSON_ID=SALES_ORDER_FACT.SALES_PERSON_ID )

AND ( CUSTOMER_SOLDTO.CUSTOMER_ID=SALES_ORDER_FACT.CUSTOMER_ID_SOLDTO )

AND ( SALES_ORDER_FACT.PROD_HIER_ID=PRODUCT_HIERARCHY.PROD_HIER_ID )

AND ( SALES_ORDER_FACT.SALES_DOC_ID<> '?' ) AND ( TIMEDIM_CREATE_DATE.CALENDAR_YEAR = @prompt('Calendar

Year:','N','Doc. Creation Date Time Dim\Calendar Year',Mono,Free,Persistent,,User:0)

AND ( (SALES_ORDER_FACT.DOC_CATEG_ID= 'C' or SALES_ORDER_FACT.DOC_CATEG_ID= 'I') ) )GROUP BY TIMEDIM_CREATE_DATE.CALENDAR_QUARTER, TIMEDIM_CREATE_DATE.CALENDAR_YEAR, CUSTOMER_SOLDTO.CUSTOMER_NAME_01, CUSTOMER_SOLDTO.CUSTOMER_NAME_02, SALES_PERSON.SALES_PERSON_FIRST_NAME, SALES_PERSON.SALES_PERSON_LAST_NAME, PRODUCT_HIERARCHY.LVL_01_DESCR


Booking Trends
This report analyzes Booking Trends for the entire enterprise over the selected set of Calendar Years by:• Company Code• Sales Org• Product Hierarchy




Top Sales Reps by QuarterView Top Sales Reps for the selected Calendar Years. Bookings amounts are displayed for each quarter. Sales Reps are sorted according to highest Bookings amount



Recommended table joins
When analyzing data from the sales order section, you need to create joins between different tables. Specifically, you need to create joins between the SALES_ORDERS_FACT table and other dimension tables in the Rapid MartNote: BACKLOG_FACT and SALES_ORDER_FACT are joined because they share the same primary key. This allows Backlog and Sales Orders to share the same set of dimensions in the universe.

Dimension Table Join Expression

BACKLOG_FACT (Fact to Fact - allows Backlog Orders to be used as a dimension to Sales Orders)

BACKLOG_FACT.SALES_DOC_ID=SALES_ORDER_FACT.SALES_DOC_ID AND BACKLOG_FACT.SALES_LINE_ITEM_ID=SALES_ORDER_FACT.SALES_LINE_ITEM_ID

BILLING_BLOCK BILLING_BLOCK.BILL_BLOCK_ID=SALES_ORDER_FACT.BILL_BLOCK_ID

CENTRAL_ADDRESS CENTRAL_ADDRESS.ADDRESS_ID= SALES_ORDER_FACT.ADDRESS_ID_SHIPTO

COMPANY_CODE COMPANY_CODE.CMPNY_CODE_ID=SALES_ORDER_FACT.CMPNY_CODE_ID

CONTROLLING_AREA CONTROLLING_AREA.CTRL_AREA_ID=SALES_ORDER_FACT.CTRL_AREA_ID

CREDIT_CONTROL_AREA SALES_ORDER_FACT.CR_CTRL_AREA_ID = CREDIT_CONTROL_AREA.CR_CTRL_AREA_ID

CREDIT_MGMT_RISK_CLASS

SALES_ORDER_FACT.CR_CTRL_AREA_ID = CREDIT_MGMT_RISK_CLASS.CR_CTRL_AREA_ID and SALES_ORDER_FACT.CR_MGMT_RISK_CLASS_ID = CREDIT_MGMT_RISK_CLASS.CR_MGMT_RISK_CLASS_ID

CURRENCY CURRENCY.CURR_ID=SALES_ORDER_FACT.CURR_ID_GBL

CURRENCY CURRENCY.CURR_ID=SALES_ORDER_FACT.CURR_ID_STATS

CURRENCY CURRENCY.CURR_ID=SALES_ORDER_FACT.CURR_ID_TRX



CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_ORDER_FACT.CUSTOMER_ID_BILLTO

CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_ORDER_FACT.CUSTOMER_ID_SHIPTO

CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_ORDER_FACT.CUSTOMER_ID_SOLDTO

CUSTOMER_GROUP SALES_ORDER_FACT.CUSTOMER_GRP_01_ID=CUSTOMER_GROUP.CUSTOMER_GRP_ID

DELIVERY_BLOCK DELIVERY_BLOCK.DELIV_BLOCK_ID=SALES_ORDER_FACT.DELIV_BLOCK_ID

DELIVERY_ROUTE SALES_ORDER_FACT.DELIV_ROUTE_ID=DELIVERY_ROUTE.DELIV_ROUTE_ID

DISTRIBUTION_CHANNEL DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=SALES_ORDER_FACT.DISTRIBTN_CHANNEL_ID

DIVISION DIVISION.DIVISION_ID=SALES_ORDER_FACT.DIVISION_ID

DOCUMENT_CATEGORY SALES_ORDER_FACT.DOC_CATEG_ID=DOCUMENT_CATEGORY.DOC_CATEG_ID and DOCUMENT_CATEGORY.DIM_SRC_ID = 'SALES'

DOCUMENT_TYPE SALES_ORDER_FACT.DOC_CATEG_ID=DOCUMENT_TYPE.DOC_CATEG_ID and SALES_ORDER_FACT.DOC_TYPE_ID=DOCUMENT_TYPE.DOC_TYPE_ID and DOCUMENT_TYPE.DIM_SRC_ID = 'SALES'

INTER_COMPANY_TERM01 SALES_ORDER_FACT.INTER_CMPNY_TERM01_ID=INTER_COMPANY_TERM01.INTER_CMPNY_TERM01_ID

MATERIAL SALES_ORDER_FACT.MATERIAL_ID=MATERIAL.MATERIAL_ID

MATERIAL_ALT_UOM SALES_ORDER_FACT.MATERIAL_ID=MATERIAL_ALT_UOM.MATERIAL_ID and MATERIAL_ALT_UOM.UOM_ID_ALT = @Prompt('Enter Alt. Unit of Meas.','A', ,MONO,FREE)




MATERIAL_BY_PLANT MATERIAL_BY_PLANT.PLANT_ID=SALES_ORDER_FACT.PLANT_ID and MATERIAL_BY_PLANT.MATERIAL_ID=SALES_ORDER_FACT.MATERIAL_ID

MATERIAL_GROUP SALES_ORDER_FACT.MTRL_GRP_ID=MATERIAL_GROUP.MTRL_GRP_ID

ORDER_REASON SALES_ORDER_FACT.ORDER_REASON_ID=ORDER_REASON.ORDER_REASON_ID

PAYMENT_TERM SALES_ORDER_FACT.PAYMNT_TERM_ID=PAYMENT_TERM.PAYMNT_TERM_ID AND PAYMENT_TERM.PAYMNT_TERM_DAY_LIMIT = '?'

PLANT PLANT.PLANT_ID=SALES_ORDER_FACT.PLANT_ID

PRICING_PROCEDURE_HZ SALES_ORDER_FACT.PRICE_PROCEDURE_ID=PRICING_PROCEDURE_HZ.PRICE_PROCEDURE_ID and PRICING_PROCEDURE_HZ.APPLICTN_ID = 'V' and PRICING_PROCEDURE_HZ.USAGE_ID = 'A'

PRODUCT_HIERARCHY SALES_ORDER_FACT.PROD_HIER_ID=PRODUCT_HIERARCHY.PROD_HIER_ID

PROFIT_CENTER SALES_ORDER_FACT.PROFIT_CNTR_ID = PROFIT_CENTER.PROFIT_CNTR_ID AND SALES_ORDER_FACT.CTRL_AREA_ID = PROFIT_CENTER.CTRL_AREA_ID

PROFIT_CENTER_HIER_HZ SALES_ORDER_FACT.PROFIT_CNTR_ID = PROFIT_CENTER_HIER_HZ.PROFIT_CNTR_ID AND SALES_ORDER_FACT.CTRL_AREA_ID = PROFIT_CENTER_HIER_HZ.CTRL_AREA_ID




PROFIT_CENTER_HIER_VR SALES_ORDER_FACT.PROFIT_CNTR_ID = PROFIT_CENTER_HIER_VR.CHILD_ID AND SALES_ORDER_FACT.CTRL_AREA_ID = PROFIT_CENTER_HIER_VR.CTRL_AREA_ID

REJECT_REASON SALES_ORDER_FACT.REJECT_REASON_ID=REJECT_REASON.REJECT_REASON_ID

SALES_DISTRICT SALES_DISTRICT.SALES_DISTRICT_ID=SALES_ORDER_FACT.SALES_DISTRICT_ID

SALES_GROUP SALES_GROUP.SALES_GRP_ID=SALES_ORDER_FACT.SALES_GRP_ID

SALES_OFFICE SALES_OFFICE.SALES_OFFICE_ID=SALES_ORDER_FACT.SALES_OFFICE_ID

SALES_ORDER_FACT SALES_ORDER_FACT.SALES_DOC_ID<> '?'

SALES_ORDER_STATUS SALES_ORDER_FACT.SALES_DOC_STATUS_KEY=SALES_ORDER_STATUS.SALES_DOC_STATUS_KEY

SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=SALES_ORDER_FACT.SALES_ORG_ID




SALES_ORGANIZATION_HIER

SALES_ORDER_FACT.SALES_ORG_ID=SALES_ORGANIZATION_HIER.SALES_ORG_ID and SALES_ORDER_FACT.SALES_GRP_ID=SALES_ORGANIZATION_HIER.SALES_GRP_ID and SALES_ORDER_FACT.SALES_OFFICE_ID=SALES_ORGANIZATION_HIER.SALES_OFFICE_ID and SALES_ORDER_FACT.DISTRIBTN_CHANNEL_ID=SALES_ORGANIZATION_HIER.DISTRIBTN_CHANNEL_ID and SALES_ORDER_FACT.DIVISION_ID_HDR=SALES_ORGANIZATION_HIER.DIVISION_ID and @Prompt('Enter Sales Org Hierarchy Date','D',,MONO,FREE) between SALES_ORGANIZATION_HIER.VALID_FROM_DATE and SALES_ORGANIZATION_HIER.VALID_TO_DATE

SALES_PERSON SALES_PERSON.SALES_PERSON_ID=SALES_ORDER_FACT.SALES_PERSON_ID

SHIPPING_POINT SALES_ORDER_FACT.SHIP_POINT_ID=SHIPPING_POINT.SHIP_POINT_ID

STORAGE_LOCATION STORAGE_LOCATION.STORAGE_LOCATN_ID=SALES_ORDER_FACT.STORAGE_LOCATN_ID and STORAGE_LOCATION.PLANT_ID=SALES_ORDER_FACT.PLANT_ID

TIME_DIM SALES_ORDER_FACT.CREATE_DATE=TIME_DIM.CALENDAR_DATE

TIME_DIM SALES_ORDER_FACT.DOC_DATE=TIME_DIM.CALENDAR_DATE

TIME_DIM SALES_ORDER_FACT.RQST_DELIV_DATE_HDR=TIME_DIM.CALENDAR_DATE

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=SALES_ORDER_FACT.UOM_ID_BASE

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=SALES_ORDER_FACT.UOM_ID_SALES

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=SALES_ORDER_FACT.UOM_ID_TRGT



ReportsDeliveries and On-Time Delivery 4

Deliveries and On-Time DeliveryThe delivery and on-time delivery section contains information about all deliveries; including returns, shipments, shipment notifications, and information for tracking delivery status.

ReportsUsing the delivery information, you can complete numerous analyses and generate different reports, including:• On-time Delivery Trends • On-time Delivery Details • On-Time Delivery Summary• On-Time Delivery Top 10 Customers• On-Time Delivery Top 10 Documents

On-time Delivery TrendsThis report analyzes On-Time delivery trends for selected Calendar Years by:• Plant• Division


ReportsDeliveries and On-Time Delivery4

Report SQLSELECT PLANT.PLANT_ID, PLANT.PLANT_NAME_01, DIVISION.DIVISION_ID, DIVISION.DIVISION_DESCR, MATERIAL.MTRL_DESCR, TIMEDIM_GOODS_ISSUE_DATE.CALENDAR_YEAR, TIMEDIM_GOODS_ISSUE_DATE.CALENDAR_QUARTER, AVG(ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE), AVG(case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -60 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE >= -60 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -30 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE >= -30 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < 0 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE = 0 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 0 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE <= 30 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 30 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE <= 60 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 60 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else 0.0 end end end end end end end ), SUM(case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -60 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *

ONTIME_DELIVERY_FACT.AMT_NET_PRICE_TRX) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE >= -60 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -30 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *


ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < 0 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *

ONTIME_DELIVERY_FACT.AMT_NET_PRICE_TRX) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE = 0 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *

ONTIME_DELIVERY_FACT.AMT_NET_PRICE_TRX) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 0 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE <= 30 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *



ONTIME_DELIVERY_FACT.AMT_NET_PRICE_TRX) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 60 then



(ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM * ONTIME_DELIVERY_FACT.AMT_NET_PRICE_TRX) else

0.0 end end end end end end end ), case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -60 then 'More than

60 Days Late' else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE >= -60 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -30 then '31 - 60 Days Late' else

case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE >= -30 and ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < 0 then '1 - 30 Days Late' else

case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE = 0 then 'On Time' else

case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 0 and ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE <= 30 then '1 - 30 Days Early' else


case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 60 then 'More than 60 Days Early' else

'N/A' end end end end end end endFROM PLANT, DIVISION, MATERIAL, TIME_DIM TIMEDIM_GOODS_ISSUE_DATE, ONTIME_DELIVERY_FACTWHERE (

ONTIME_DELIVERY_FACT.SCHED_GI_DATE=TIMEDIM_GOODS_ISSUE_DATE.CALENDAR_DATE )

AND ( ONTIME_DELIVERY_FACT.DIVISION_ID=DIVISION.DIVISION_ID ) AND ( PLANT.PLANT_ID=ONTIME_DELIVERY_FACT.PLANT_ID ) AND ( MATERIAL.MATERIAL_ID=ONTIME_DELIVERY_FACT.MATERIAL_ID ) AND TIMEDIM_GOODS_ISSUE_DATE.CALENDAR_YEAR In @prompt('Calendar

Year(s):','N','Planned Goods Issue Date Time Dim\Calendar Year',Multi,Free,Persistent,,User:0)

GROUP BY PLANT.PLANT_ID, PLANT.PLANT_NAME_01, DIVISION.DIVISION_ID, DIVISION.DIVISION_DESCR, MATERIAL.MTRL_DESCR, TIMEDIM_GOODS_ISSUE_DATE.CALENDAR_YEAR, TIMEDIM_GOODS_ISSUE_DATE.CALENDAR_QUARTER, case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -60 then 'More than










'N/A' end end end end end end end



On-time Delivery DetailsOperational report showing current deliveries. For a selected Plant, Division, and Scheduled delivery date between the selected date range, view details by (Rapid Mart calculated) delivery status:• Overdue• Late• Early• On time



On-Time Delivery Summary
View a summary of On-Time Delivery metrics for the selected Calendar Year and Quarter by:• Division• Company CodeThis report offers drill down capability to see Top 10 Customers for a Plant (by opening On-Time Delivery Top 10 Customers).




On-Time Delivery Top 10 CustomersView Top 10 Customers based on Net Value for a selected Plant, Calendar Year, and Delivery Status. This report uses the Web Intelligence ranking feature to limit to the Top 10 Customers by Total Delivered Net Value.This report offers drill down capability to see Top 10 Documents for a Customer (by opening On-Time Delivery Top 10 Documents).



On-Time Delivery Top 10 Documents
View Top 10 Sales Documents for a selected Customer, Calendar Year, and Delivery Status.This report uses the Web Intelligence ranking feature to limit to the Top 10 Customers by Total Delivered Net Value.

Report SQLSELECT PLANT.PLANT_ID, PLANT.PLANT_NAME_01, DIVISION.DIVISION_ID, DIVISION.DIVISION_DESCR, MATERIAL.MTRL_DESCR, ONTIME_DELIVERY_FACT.NAME_01_SHIPTO, ONTIME_DELIVERY_FACT.NAME_02_SHIPTO, ONTIME_DELIVERY_FACT.COUNTRY_ID_SHIPTO, ONTIME_DELIVERY_FACT.REGION_ID_SHIPTO, ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE, ONTIME_DELIVERY_FACT.SCHED_GI_DATE, ONTIME_DELIVERY_FACT.ACTL_GI_DATE, ONTIME_DELIVERY_FACT.DELIV_STATUS_FLAG, ONTIME_DELIVERY_FACT.QTY_SCHED_CONFIRM_SLSUOM, ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM, ONTIME_DELIVERY_FACT.SALES_DOC_ID, ONTIME_DELIVERY_FACT.SALES_LINE_ITEM_ID, ONTIME_DELIVERY_FACT.SCHED_LINE_ITEM_ID, ONTIME_DELIVERY_FACT.DELIV_ID, ONTIME_DELIVERY_FACT.DELIV_LINE_ITEM_ID, ONTIME_DELIVERY_FACT.CHANGE_STATUS_FLAG,



AVG(ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE), SUM(case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -60 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *


ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -30 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *


ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < 0 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *

ONTIME_DELIVERY_FACT.AMT_NET_PRICE_TRX) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE = 0 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *





ONTIME_DELIVERY_FACT.AMT_NET_PRICE_TRX) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 60 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM *

ONTIME_DELIVERY_FACT.AMT_NET_PRICE_TRX) else 0.0 end end end end end end end ), case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -60 then 'More than








'N/A' end end end end end end end, SUM(case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -60 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE >= -60 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -30 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE >= -30 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < 0 then



(ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE = 0 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 0 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE <= 30 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 30 and

ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE <= 60 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE > 60 then (ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM) else 0.0 end end end end end end end )FROM PLANT, DIVISION, MATERIAL, ONTIME_DELIVERY_FACTWHERE ( ONTIME_DELIVERY_FACT.DIVISION_ID=DIVISION.DIVISION_ID ) AND ( PLANT.PLANT_ID=ONTIME_DELIVERY_FACT.PLANT_ID ) AND ( MATERIAL.MATERIAL_ID=ONTIME_DELIVERY_FACT.MATERIAL_ID ) AND ( PLANT.PLANT_NAME_01 = @prompt('Plant:','A','Plant\Plant Name

1',Mono,Free,Persistent,,User:0) AND ONTIME_DELIVERY_FACT.DELIV_STATUS_FLAG = @prompt('Delivery

Status:','A','Curr On Time - Additional Details (IDs)\Delivery Status',Mono,Free,Persistent,,User:1)

AND ONTIME_DELIVERY_FACT.NAME_01_SHIPTO = @prompt('Customer

Name:','A','Curr On Time - Ship To Address\Name1',Mono,Free,Persistent,,User:2) )GROUP BY PLANT.PLANT_ID, PLANT.PLANT_NAME_01, DIVISION.DIVISION_ID, DIVISION.DIVISION_DESCR, MATERIAL.MTRL_DESCR, ONTIME_DELIVERY_FACT.NAME_01_SHIPTO, ONTIME_DELIVERY_FACT.NAME_02_SHIPTO, ONTIME_DELIVERY_FACT.COUNTRY_ID_SHIPTO, ONTIME_DELIVERY_FACT.REGION_ID_SHIPTO, ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE, ONTIME_DELIVERY_FACT.SCHED_GI_DATE, ONTIME_DELIVERY_FACT.ACTL_GI_DATE, ONTIME_DELIVERY_FACT.DELIV_STATUS_FLAG, ONTIME_DELIVERY_FACT.QTY_SCHED_CONFIRM_SLSUOM, ONTIME_DELIVERY_FACT.QTY_ALLOCTN_SLSUOM, ONTIME_DELIVERY_FACT.SALES_DOC_ID, ONTIME_DELIVERY_FACT.SALES_LINE_ITEM_ID, ONTIME_DELIVERY_FACT.SCHED_LINE_ITEM_ID, ONTIME_DELIVERY_FACT.DELIV_ID, ONTIME_DELIVERY_FACT.DELIV_LINE_ITEM_ID,



ONTIME_DELIVERY_FACT.CHANGE_STATUS_FLAG, case when ONTIME_DELIVERY_FACT.DAYS_EARLY_LATE < -60 then 'More than








'N/A' end end end end end end end

Recommended table joins When analyzing data from the delivery section, you need to create joins between different tables. Specifically, you need to create joins between DELIVERY_FACT, ON_TIME_DELIVERY_FACT, and ON_TIME_DELIVERY_HISTORY tables and other dimension tables in the Rapid Mart.


Delivery Fact

BILLING_BLOCK BILLING_BLOCK.BILL_BLOCK_ID=DELIVERY_FACT.BILL_BLOCK_ID

COMPANY_CODE COMPANY_CODE.CMPNY_CODE_ID=DELIVERY_FACT.CMPNY_CODE_ID

CONTROLLING_AREA DELIVERY_FACT.CTRL_AREA_ID=CONTROLLING_AREA.CTRL_AREA_ID

CREDIT_CONTROL_AREA DELIVERY_FACT.CR_CTRL_AREA_ID = CREDIT_CONTROL_AREA.CR_CTRL_AREA_ID

CREDIT_MGMT_RISK_CLASS

DELIVERY_FACT.CR_CTRL_AREA_ID = CREDIT_MGMT_RISK_CLASS.CR_CTRL_AREA_ID and DELIVERY_FACT.CR_MGMT_RISK_CLASS_ID = CREDIT_MGMT_RISK_CLASS.CR_MGMT_RISK_CLASS_ID

CURRENCY CURRENCY.CURR_ID=DELIVERY_FACT.CURR_ID_STATS

CURRENCY CURRENCY.CURR_ID=DELIVERY_FACT.CURR_ID_TRX

CUSTOMER CUSTOMER.CUSTOMER_ID=DELIVERY_FACT.CUSTOMER_ID_SHIPTO

CUSTOMER CUSTOMER.CUSTOMER_ID=DELIVERY_FACT.CUSTOMER_ID_SOLDTO

CUSTOMER_GROUP DELIVERY_FACT.CUSTOMER_GRP_ID=CUSTOMER_GROUP.CUSTOMER_GRP_ID

DELIVERY_BLOCK DELIVERY_BLOCK.DELIV_BLOCK_ID=DELIVERY_FACT.DELIV_BLOCK_ID

DELIVERY_FACT DELIVERY_FACT.DELIV_ID<> '?'DELIVERY_PRIORITY DELIVERY_FACT.DELIV_PRIORITY_ID=DELIVERY_PRI

ORITY.DELIV_PRIORITY_IDDELIVERY_ROUTE DELIVERY_ROUTE.DELIV_ROUTE_ID=DELIVERY_FAC

T.DELIV_ROUTE_IDDELIVERY_STATUS DELIVERY_FACT.DELIV_STATUS_KEY=DELIVERY_STA

TUS.DELIV_STATUS_KEYDISTRIBUTION_CHANNEL DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=

DELIVERY_FACT.DISTRIBTN_CHANNEL_IDDIVISION DIVISION.DIVISION_ID=DELIVERY_FACT.DIVISION_ID



DOCUMENT_CATEGORY DELIVERY_FACT.DOC_CATEG_ID=DOCUMENT_CATEGORY.DOC_CATEG_ID and DOCUMENT_CATEGORY.DIM_SRC_ID = 'SALES'

DOCUMENT_TYPE DELIVERY_FACT.DOC_CATEG_ID=DOCUMENT_TYPE.DOC_CATEG_ID and DELIVERY_FACT.DOC_TYPE_ID=DOCUMENT_TYPE.DOC_TYPE_ID and DOCUMENT_TYPE.DIM_SRC_ID = 'SALES'

INTER_COMPANY_TERM01 INTER_COMPANY_TERM01.INTER_CMPNY_TERM01_ID=DELIVERY_FACT.INTER_CMPNY_TERM01_ID

MATERIAL DELIVERY_FACT.MATERIAL_ID=MATERIAL.MATERIAL_ID

MATERIAL_ALT_UOM DELIVERY_FACT.MATERIAL_ID=MATERIAL_ALT_UOM.MATERIAL_ID and MATERIAL_ALT_UOM.UOM_ID_ALT = @Prompt('Enter Alt. Unit of Meas.','A', ,MONO,FREE)

MATERIAL_BY_PLANT MATERIAL_BY_PLANT.PLANT_ID=DELIVERY_FACT.PLANT_ID and MATERIAL_BY_PLANT.MATERIAL_ID=DELIVERY_FACT.MATERIAL_ID

MATERIAL_GROUP MATERIAL_GROUP.MTRL_GRP_ID=DELIVERY_FACT.MTRL_GRP_ID

PLANT PLANT.PLANT_ID=DELIVERY_FACT.PLANT_IDPRICING_PROCEDURE_HZ DELIVERY_FACT.PRICE_PROCEDURE_ID=PRICING_P

ROCEDURE_HZ.PRICE_PROCEDURE_ID and PRICING_PROCEDURE_HZ.APPLICTN_ID = 'V' and PRICING_PROCEDURE_HZ.USAGE_ID = 'A'

PRODUCT_HIERARCHY PRODUCT_HIERARCHY.PROD_HIER_ID=DELIVERY_FACT.PROD_HIER_ID

PROFIT_CENTER PROFIT_CENTER.PROFIT_CNTR_ID=DELIVERY_FACT.PROFIT_CNTR_ID AND PROFIT_CENTER.CTRL_AREA_ID = DELIVERY_FACT.CTRL_AREA_ID

PROFIT_CENTER_HIER_HZ PROFIT_CENTER_HIER_HZ.PROFIT_CNTR_ID=DELIVERY_FACT.PROFIT_CNTR_ID AND PROFIT_CENTER_HIER_HZ.CTRL_AREA_ID = DELIVERY_FACT.CTRL_AREA_ID

PROFIT_CENTER_HIER_VR PROFIT_CENTER_HIER_VR.CHILD_ID=DELIVERY_FACT.PROFIT_CNTR_ID AND PROFIT_CENTER_HIER_VR.CTRL_AREA_ID = DELIVERY_FACT.CTRL_AREA_ID




SALES_GROUP SALES_GROUP.SALES_GRP_ID=DELIVERY_FACT.SALES_GRP_ID

SALES_OFFICE DELIVERY_FACT.SALES_OFFICE_ID=SALES_OFFICE.SALES_OFFICE_ID

SALES_ORDER_FACT (Fact to Fact - allows Sales Orders to be used as a dimension to Deliveries)

SALES_ORDER_FACT.SALES_DOC_ID=DELIVERY_FACT.SALES_DOC_ID and SALES_ORDER_FACT.SALES_LINE_ITEM_ID=DELIVERY_FACT.SALES_LINE_ITEM_ID

SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=DELIVERY_FACT.SALES_ORG_ID


DELIVERY_FACT.SALES_ORG_ID=SALES_ORGANIZATION_HIER.SALES_ORG_ID and DELIVERY_FACT.SALES_GRP_ID=SALES_ORGANIZATION_HIER.SALES_GRP_ID and DELIVERY_FACT.SALES_OFFICE_ID=SALES_ORGANIZATION_HIER.SALES_OFFICE_ID and DELIVERY_FACT.DISTRIBTN_CHANNEL_ID=SALES_ORGANIZATION_HIER.DISTRIBTN_CHANNEL_ID and DELIVERY_FACT.DIVISION_ID=SALES_ORGANIZATION_HIER.DIVISION_ID and @Prompt('Enter Sales Org Hierarchy Date','D',,MONO,FREE) between SALES_ORGANIZATION_HIER.VALID_FROM_DATE and SALES_ORGANIZATION_HIER.VALID_TO_DATE

SHIPPING_POINT SHIPPING_POINT.SHIP_POINT_ID=DELIVERY_FACT.SHIP_POINT_ID

STORAGE_LOCATION DELIVERY_FACT.PLANT_ID=STORAGE_LOCATION.PLANT_ID and DELIVERY_FACT.STORAGE_LOCATN_ID=STORAGE_LOCATION.STORAGE_LOCATN_ID

TIME_DIM DELIVERY_FACT.ACTL_GI_DATE=TIME_DIM.CALENDAR_DATE

TIME_DIM DELIVERY_FACT.CREATE_DATE_HDR=TIME_DIM.CALENDAR_DATE

TIME_DIM DELIVERY_FACT.DELIV_DATE=TIME_DIM.CALENDAR_DATE

TIME_DIM DELIVERY_FACT.PLAN_GI_DATE=TIME_DIM.CALENDAR_DATE

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=DELIVERY_FACT.UOM_ID_BASE




UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=DELIVERY_FACT.UOM_ID_SALES

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=DELIVERY_FACT.UOM_ID_VOLUME

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=DELIVERY_FACT.UOM_ID_WEIGHT



Current On-Time Delivery

CURRENCY CURRENCY.CURR_ID=ONTIME_DELIVERY_FACT.CURR_ID_GBL

CURRENCY CURRENCY.CURR_ID=ONTIME_DELIVERY_FACT.CURR_ID_TRX

CUSTOMER CUSTOMER.CUSTOMER_ID=ONTIME_DELIVERY_FACT.CUSTOMER_ID_SHIPTO

DELIVERY_BLOCK DELIVERY_BLOCK.DELIV_BLOCK_ID=ONTIME_DELIVERY_FACT.DELIV_BLOCK_ID

DISTRIBUTION_CHANNEL DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=ONTIME_DELIVERY_FACT.DISTRIBTN_CHANNEL_ID

DIVISION ONTIME_DELIVERY_FACT.DIVISION_ID=DIVISION.DIVISION_ID

INTER_COMPANY_TERM01 ONTIME_DELIVERY_FACT.INTER_CMPNY_TERM01_ID=INTER_COMPANY_TERM01.INTER_CMPNY_TERM01_ID

MATERIAL MATERIAL.MATERIAL_ID=ONTIME_DELIVERY_FACT.MATERIAL_ID

MATERIAL_ALT_UOM ONTIME_DELIVERY_FACT.MATERIAL_ID=MATERIAL_ALT_UOM.MATERIAL_ID and MATERIAL_ALT_UOM.UOM_ID_ALT = @Prompt('Enter Alt. Unit of Meas.','A', ,MONO,FREE)

MATERIAL_BY_PLANT MATERIAL_BY_PLANT.PLANT_ID=ONTIME_DELIVERY_FACT.PLANT_ID and MATERIAL_BY_PLANT.MATERIAL_ID=ONTIME_DELIVERY_FACT.MATERIAL_ID

PLANT PLANT.PLANT_ID=ONTIME_DELIVERY_FACT.PLANT_ID

PRODUCT_HIERARCHY PRODUCT_HIERARCHY.PROD_HIER_ID=ONTIME_DELIVERY_FACT.PROD_HIER_ID

SALES_OFFICE SALES_OFFICE.SALES_OFFICE_ID=ONTIME_DELIVERY_FACT.SALES_OFFICE_ID

SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=ONTIME_DELIVERY_FACT.SALES_ORG_ID

SALES_PERSON SALES_PERSON.SALES_PERSON_ID=ONTIME_DELIVERY_FACT.SALES_PERSON_ID



TIME_DIM ONTIME_DELIVERY_FACT.ACTL_GI_DATE=TIME_DIM.CALENDAR_DATE

TIME_DIM ONTIME_DELIVERY_FACT.SCHED_GI_DATE=TIME_DIM.CALENDAR_DATE



History On-Time Delivery

CURRENCY CURRENCY.CURR_ID=ONTIME_DELIVERY_HISTORY.CURR_ID_GBL

CURRENCY CURRENCY.CURR_ID=ONTIME_DELIVERY_HISTORY.CURR_ID_TRX

CUSTOMER CUSTOMER.CUSTOMER_ID=ONTIME_DELIVERY_HISTORY.CUSTOMER_ID_SHIPTO

DELIVERY_BLOCK DELIVERY_BLOCK.DELIV_BLOCK_ID=ONTIME_DELIVERY_HISTORY.DELIV_BLOCK_ID

DISTRIBUTION_CHANNEL DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=ONTIME_DELIVERY_HISTORY.DISTRIBTN_CHANNEL_ID

DIVISION ONTIME_DELIVERY_HISTORY.DIVISION_ID=DIVISION.DIVISION_ID

INTER_COMPANY_TERM01 ONTIME_DELIVERY_HISTORY.INTER_CMPNY_TERM01_ID=INTER_COMPANY_TERM01.INTER_CMPNY_TERM01_ID

MATERIAL MATERIAL.MATERIAL_ID=ONTIME_DELIVERY_HISTORY.MATERIAL_ID

MATERIAL_ALT_UOM ONTIME_DELIVERY_HISTORY.MATERIAL_ID=MATERIAL_ALT_UOM.MATERIAL_ID and MATERIAL_ALT_UOM.UOM_ID_ALT = @Prompt('Enter Alt. Unit of Meas.','A', ,MONO,FREE)

MATERIAL_BY_PLANT MATERIAL_BY_PLANT.PLANT_ID=ONTIME_DELIVERY_HISTORY.PLANT_ID and MATERIAL_BY_PLANT.MATERIAL_ID=ONTIME_DELIVERY_HISTORY.MATERIAL_ID

PLANT PLANT.PLANT_ID=ONTIME_DELIVERY_HISTORY.PLANT_ID

PRODUCT_HIERARCHY PRODUCT_HIERARCHY.PROD_HIER_ID=ONTIME_DELIVERY_HISTORY.PROD_HIER_ID

SALES_OFFICE SALES_OFFICE.SALES_OFFICE_ID=ONTIME_DELIVERY_HISTORY.SALES_OFFICE_ID

SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=ONTIME_DELIVERY_HISTORY.SALES_ORG_ID

SALES_PERSON SALES_PERSON.SALES_PERSON_ID=ONTIME_DELIVERY_HISTORY.SALES_PERSON_ID


ReportsBilling 4

BillingThe billing section stores billing information, including invoices, credit and debit memos, pro-forma items, intercompany invoices, and intercompany credit memos, as well as status information to track bills.

Summary ReportsUsing the billing information, you can complete numerous analyses and generate different reports. For example, reports you can generate include:• Operational Revenue per Quarter • Bookings vs Billings• Credit Memo Analysis• Credit Memo Customer Detail• Credit Memo Trends

Operational Revenue per QuarterOperational Revenue for selected Calendar Years by• Calendar Year• Calendar Year and QtrCharts for each calendar year use the Web Intelligencereport ranking feature.

TIME_DIM ONTIME_DELIVERY_HISTORY.ACTL_GI_DATE=TIME_DIM.CALENDAR_DATE

TIME_DIM ONTIME_DELIVERY_HISTORY.SCHED_GI_DATE=TIME_DIM.CALENDAR_DATE



ReportsBilling4


ReportsBilling 4


Bookings vs BillingsAnalyze sales orders bookings amounts against invoice amounts for selected Calendar Years- by Company Code- by Sales RepThis report uses two queries, one to retrieve Bookings data, the other to retrieve Billings. It also includes Alerts for when the variance % between net Bookings and Billings amounts is greater than 20%.


Credit Memo AnalysisAnalyze credit memos for selected Calendar Year and Quarter by Company Code. This report also includes drill down to Credit Memo Customer Detail report by clicking the desired Customer Name.


ReportsBilling4


Credit Memo Customer DetailView credit memo details for a specific Customer during a selected Calendar Year and Quarter.


ReportsBilling 4


Credit Memo TrendsAnalyze credit memo trends for selected Calendar Years. View data by Company Code, Sales Org and Calendar Quarter.


Recommended table joins When analyzing data from the billing section, you need to create joins between different tables. Specifically, you need to create joins between the BILLING_FACT table and other dimension tables in the Rapid Mart.


BILLING_FACT BILLING_FACT.BILL_DOC_ID<> '?'COMPANY_CODE COMPANY_CODE.CMPNY_CODE_ID=BIL

LING_FACT.CMPNY_CODE_ID


ReportsBilling4

CONTROLLING_AREA CONTROLLING_AREA.CTRL_AREA_ID=BILLING_FACT.CTRL_AREA_ID

CREDIT_CONTROL_AREA BILLING_FACT.CR_CTRL_AREA_ID = CREDIT_CONTROL_AREA.CR_CTRL_AREA_ID

CURRENCY CURRENCY.CURR_ID=BILLING_FACT.CURR_ID_GBL

CURRENCY CURRENCY.CURR_ID=BILLING_FACT.CURR_ID_STATS

CURRENCY CURRENCY.CURR_ID=BILLING_FACT.CURR_ID_TRX

CUSTOMER CUSTOMER.CUSTOMER_ID=BILLING_FACT.CUSTOMER_ID_PAYER

CUSTOMER CUSTOMER.CUSTOMER_ID=BILLING_FACT.CUSTOMER_ID_SOLDTO

CUSTOMER_GROUP BILLING_FACT.CUSTOMER_GRP_ID=CUSTOMER_GROUP.CUSTOMER_GRP_ID

DISTRIBUTION_CHANNEL DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=BILLING_FACT.DISTRIBTN_CHANNEL_ID

DIVISION DIVISION.DIVISION_ID=BILLING_FACT.DIVISION_ID

DOCUMENT_CATEGORY BILLING_FACT.DOC_CATEG_ID=DOCUMENT_CATEGORY.DOC_CATEG_ID and DOCUMENT_CATEGORY.DIM_SRC_ID = 'SALES'

DOCUMENT_TYPE BILLING_FACT.DOC_CATEG_ID=DOCUMENT_TYPE.DOC_CATEG_ID and BILLING_FACT.DOC_TYPE_ID=DOCUMENT_TYPE.DOC_TYPE_ID and DOCUMENT_TYPE.DIM_SRC_ID = 'SALES'

FISC_PERIOD BILLING_FACT.FISC_PERIOD=FISC_PERIOD.FISC_PERIOD and BILLING_FACT.FISC_YEAR=FISC_PERIOD.FISC_YEAR and BILLING_FACT.FISC_YR_VARIANT_ID=FISC_PERIOD.FISC_YR_VARIANT_ID



ReportsBilling 4

INTER_COMPANY_TERM01 INTER_COMPANY_TERM01.INTER_CMPNY_TERM01_ID=BILLING_FACT.INTER_CMPNY_TERM01_ID

MATERIAL BILLING_FACT.MATERIAL_ID=MATERIAL.MATERIAL_ID

MATERIAL_ALT_UOM BILLING_FACT.MATERIAL_ID=MATERIAL_ALT_UOM.MATERIAL_ID and MATERIAL_ALT_UOM.UOM_ID_ALT = @Prompt('Enter Alt. Unit of Meas.','A', ,MONO,FREE)

MATERIAL_BY_PLANT MATERIAL_BY_PLANT.PLANT_ID=BILLING_FACT.PLANT_ID and MATERIAL_BY_PLANT.MATERIAL_ID=BILLING_FACT.MATERIAL_ID

MATERIAL_GROUP MATERIAL_GROUP.MTRL_GRP_ID=BILLING_FACT.MTRL_GRP_ID

PAYMENT_TERM PAYMENT_TERM.PAYMNT_TERM_ID=BILLING_FACT.PAYMNT_TERM_ID

PLANT PLANT.PLANT_ID=BILLING_FACT.PLANT_ID

PRICING_PROCEDURE_HZ BILLING_FACT.PRICE_PROCEDURE_ID=PRICING_PROCEDURE_HZ.PRICE_PROCEDURE_ID and PRICING_PROCEDURE_HZ.APPLICTN_ID = 'V' and PRICING_PROCEDURE_HZ.USAGE_ID = 'A'

PRODUCT_HIERARCHY PRODUCT_HIERARCHY.PROD_HIER_ID=BILLING_FACT.PROD_HIER_ID

PROFIT_CENTER PROFIT_CENTER.PROFIT_CNTR_ID=BILLING_FACT.PROFIT_CNTR_ID and PROFIT_CENTER.CTRL_AREA_ID=BILLING_FACT.CTRL_AREA_ID

PROFIT_CENTER_HIER_HZ PROFIT_CENTER_HIER_HZ.PROFIT_CNTR_ID=BILLING_FACT.PROFIT_CNTR_ID and PROFIT_CENTER_HIER_HZ.CTRL_AREA_ID=BILLING_FACT.CTRL_AREA_ID



ReportsBilling4

PROFIT_CENTER_HIER_VR PROFIT_CENTER_HIER_VR.CHILD_ID=BILLING_FACT.PROFIT_CNTR_ID and PROFIT_CENTER_HIER_VR.CTRL_AREA_ID=BILLING_FACT.CTRL_AREA_ID

SALES_DOC_STATUS BILLING_FACT.PROCESS_STATUS_ID=SALES_DOC_STATUS.SALES_DOC_STATUS_ID

SALES_GROUP SALES_GROUP.SALES_GRP_ID=BILLING_FACT.SALES_GRP_ID

SALES_OFFICE BILLING_FACT.SALES_OFFICE_ID=SALES_OFFICE.SALES_OFFICE_ID

SALES_ORDER_FACT (Fact to Fact - allows Sales Orders to be used as a Dimension for Billings)

SALES_ORDER_FACT.SALES_DOC_ID=BILLING_FACT.SALES_DOC_ID and SALES_ORDER_FACT.SALES_LINE_ITEM_ID=BILLING_FACT.SALES_LINE_ITEM_ID

SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=BILLING_FACT.SALES_ORG_ID


BILLING_FACT.SALES_ORG_ID=SALES_ORGANIZATION_HIER.SALES_ORG_ID and BILLING_FACT.SALES_GRP_ID=SALES_ORGANIZATION_HIER.SALES_GRP_ID and BILLING_FACT.SALES_OFFICE_ID=SALES_ORGANIZATION_HIER.SALES_OFFICE_ID and BILLING_FACT.DISTRIBTN_CHANNEL_ID=SALES_ORGANIZATION_HIER.DISTRIBTN_CHANNEL_ID and BILLING_FACT.DIVISION_ID=SALES_ORGANIZATION_HIER.DIVISION_ID and @Prompt('Enter Sales Org Hierarchy Date','D',,MONO,FREE) between SALES_ORGANIZATION_HIER.VALID_FROM_DATE and SALES_ORGANIZATION_HIER.VALID_TO_DATE

SHIPPING_POINT SHIPPING_POINT.SHIP_POINT_ID=BILLING_FACT.SHIP_POINT_ID

STORAGE_LOCATION BILLING_FACT.PLANT_ID=STORAGE_LOCATION.PLANT_ID and BILLING_FACT.STORAGE_LOCATN_ID=STORAGE_LOCATION.STORAGE_LOCATN_ID



ReportsBacklog 4

BacklogThe backlog section stores information about backlog quantities—the difference between booked quantities and shipped quantities.

Sample reportUsing the backlog information, you can complete numerous analyses and generate different reports. Backlog Analysis is an example of one report you could generate.

Backlog AnalysisAnalyze order backlog for selected Plants by:• Product Hierarchy• Company Code and Plant

TIME_DIM BILLING_FACT.CREATE_DATE=TIME_DIM.CALENDAR_DATE

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=BILLING_FACT.UOM_ID_BASE

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=BILLING_FACT.UOM_ID_SALES



ReportsBacklog4

This report uses the Web Intelligence ranking feature in the charts to see only the Top 10 Materials backlog for each Division.

Report SQLSELECT SALES_ORGANIZATION.SALES_ORG_DESCR, COMPANY_CODE.CMPNY_CODE_NAME, DIVISION.DIVISION_DESCR, PLANT.PLANT_NAME_01, DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_DESCR, MATERIAL.MTRL_DESCR, UOM_SALES.UOM_DESCR, SUM(BACKLOG_FACT.QTY_BACKLOG_SLSUOM), CURRENCY_GLOBAL.CURR_ISO_CODE,

SUM(BACKLOG_FACT.QTY_BACKLOG_SLSUOM*SALES_ORDER_FACT.AMT_NET_PRICE_GBL),

PRODUCT_HIERARCHY.LVL_01_DESCR, PRODUCT_HIERARCHY.LVL_02_DESCRFROM SALES_ORGANIZATION, COMPANY_CODE, DIVISION, PLANT, DISTRIBUTION_CHANNEL, MATERIAL, UNIT_OF_MEASURE UOM_SALES, BACKLOG_FACT, CURRENCY CURRENCY_GLOBAL,


ReportsBacklog 4

SALES_ORDER_FACT, PRODUCT_HIERARCHYWHERE (

SALES_ORGANIZATION.SALES_ORG_ID=SALES_ORDER_FACT.SALES_ORG_ID )

AND ( DIVISION.DIVISION_ID=SALES_ORDER_FACT.DIVISION_ID ) AND (

DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=SALES_ORDER_FACT.DISTRIBTN_CHANNEL_ID )

AND ( SALES_ORDER_FACT.MATERIAL_ID=MATERIAL.MATERIAL_ID ) AND ( UOM_SALES.UOM_ID=SALES_ORDER_FACT.UOM_ID_SALES ) AND ( PLANT.PLANT_ID=SALES_ORDER_FACT.PLANT_ID ) AND (

COMPANY_CODE.CMPNY_CODE_ID=SALES_ORDER_FACT.CMPNY_CODE_ID )

AND ( MATERIAL.PROD_HIER_ID=PRODUCT_HIERARCHY.PROD_HIER_ID ) AND ( CURRENCY_GLOBAL.CURR_ID=SALES_ORDER_FACT.CURR_ID_GBL

) AND (

BACKLOG_FACT.SALES_DOC_ID=SALES_ORDER_FACT.SALES_DOC_ID AND

BACKLOG_FACT.SALES_LINE_ITEM_ID=SALES_ORDER_FACT.SALES_LINE_ITEM_ID )

AND ( SALES_ORDER_FACT.SALES_DOC_ID<> '?' ) AND PLANT.PLANT_NAME_01 In @prompt('Plant Name(s):','A','Plant\Plant Name

1',Multi,Free,Persistent,,User:0)GROUP BY SALES_ORGANIZATION.SALES_ORG_DESCR, COMPANY_CODE.CMPNY_CODE_NAME, DIVISION.DIVISION_DESCR, PLANT.PLANT_NAME_01, DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_DESCR, MATERIAL.MTRL_DESCR, UOM_SALES.UOM_DESCR, CURRENCY_GLOBAL.CURR_ISO_CODE, PRODUCT_HIERARCHY.LVL_01_DESCR, PRODUCT_HIERARCHY.LVL_02_DESCR

Recommended table joins BACKLOG_FACT is joined to SALES_ORDER_FACT in the universe. Therefore, BACKLOG_FACT can use any of the dimensions associated with SALES_ORDER_FACT. For the full list of joins, see “Recommended table joins” on page 88 in the Sales Order section.


ReportsSchedule Lines4
Schedule Lines
The schedule lines section stores detailed information about scheduled deliveries for sales orders. Using data in the schedule lines section, you can complete numerous analyses and generate different reports.

ReportsSchedule line analysis can show how your company is performing on customer requested versus planned delivery date. Included with this Rapid Mart is one report to see Sales Orders Scheduling Efficiency.


ReportsSchedule Lines 4

Sales Orders Scheduling EfficiencyThe Sales Orders Scheduling Efficiency report analyzes Sales Order Schedule Lines metrics for a selected Company Code and set of Calendar Year(s):• by Plant• by Division

Report SQLSELECT PLANT.PLANT_NAME_01, DIVISION.DIVISION_DESCR, CUSTOMER_SHIPTO.CUSTOMER_NAME_01, CUSTOMER_SHIPTO.COUNTRY_NAME, CUSTOMER_SHIPTO.REGION_NAME, CUSTOMER_SHIPTO.CITY, AVG(SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE), case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE < -60 then 'More than

60 Days Late' else case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE >= -60 and

SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE < -30 then '31 - 60 Days Late' else

case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE >= -30 and SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE < 0 then '1 - 30 Days Late' else

case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE = 0 then 'On Time' else

case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE > 0 and SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE <= 30 then '1 - 30 Days Early' else


case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE > 60 then 'More than 60 Days Early' else

'N/A' end end end end end end end,



TIMEDIM_DELIVERY_DATE.CALENDAR_YEAR, AVG(case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE < -60 then (SALES_SCHED_LINE_FACT.QTY_CONFIRM_SLSUOM) else case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE >= -60 and

SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE < -30 then (SALES_SCHED_LINE_FACT.QTY_CONFIRM_SLSUOM) else case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE >= -30 and

SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE < 0 then (SALES_SCHED_LINE_FACT.QTY_CONFIRM_SLSUOM) else case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE = 0 then (SALES_SCHED_LINE_FACT.QTY_CONFIRM_SLSUOM) else case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE > 0 and

SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE <= 30 then (SALES_SCHED_LINE_FACT.QTY_CONFIRM_SLSUOM) else case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE > 30 and

SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE <= 60 then (SALES_SCHED_LINE_FACT.QTY_CONFIRM_SLSUOM) else case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE > 60 then (SALES_SCHED_LINE_FACT.QTY_CONFIRM_SLSUOM) else 0.0 end end end end end end end )FROM PLANT, DIVISION, CUSTOMER CUSTOMER_SHIPTO, SALES_SCHED_LINE_FACT, TIME_DIM TIMEDIM_DELIVERY_DATE, COMPANY_CODE, SALES_ORDER_FACT SODIM_SCHED_LINESWHERE (

SODIM_SCHED_LINES.SALES_DOC_ID=SALES_SCHED_LINE_FACT.SALES_DOC_ID and SODIM_SCHED_LINES.SALES_LINE_ITEM_ID=SALES_SCHED_LINE_FACT.SALES_LINE_ITEM_ID )

AND ( SALES_SCHED_LINE_FACT.DELIV_DATE=TIMEDIM_DELIVERY_DATE.CALENDAR_DATE )

AND ( PLANT.PLANT_ID=SALES_SCHED_LINE_FACT.PLANT_ID ) AND ( DIVISION.DIVISION_ID=SODIM_SCHED_LINES.DIVISION_ID_HDR ) AND (

COMPANY_CODE.CMPNY_CODE_ID=SODIM_SCHED_LINES.CMPNY_CODE_ID )

AND ( CUSTOMER_SHIPTO.CUSTOMER_ID=SODIM_SCHED_LINES.CUSTOMER_ID_SHIPTO )

AND ( COMPANY_CODE.CMPNY_CODE_NAME = @prompt('Company

Code:','A','Company Code\Company Name',Mono,Free,Persistent,,User:1) AND TIMEDIM_DELIVERY_DATE.CALENDAR_YEAR In @prompt('Calendar

Year(s):','N','Delivery Date Time Dim\Calendar Year',Multi,Free,Persistent,,User:0) )GROUP BY



PLANT.PLANT_NAME_01, DIVISION.DIVISION_DESCR, CUSTOMER_SHIPTO.CUSTOMER_NAME_01, CUSTOMER_SHIPTO.COUNTRY_NAME, CUSTOMER_SHIPTO.REGION_NAME, CUSTOMER_SHIPTO.CITY, case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE < -60 then 'More than

60 Days Late' else case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE >= -60 and

SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE < -30 then '31 - 60 Days Late' else

case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE >= -30 and SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE < 0 then '1 - 30 Days Late' else

case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE = 0 then 'On Time' else



case when SALES_SCHED_LINE_FACT.DAYS_EARLY_LATE > 60 then 'More than 60 Days Early' else

'N/A' end end end end end end end, TIMEDIM_DELIVERY_DATE.CALENDAR_YEAR

Recommended table joins When analyzing data from the schedule lines section, you need to create joins between the SALES_SCHED_LINE_FACT table and other tables in the Rapid Mart.


COMPANY_CODE COMPANY_CODE.CMPNY_CODE_ID=SALES_ORDER_FACT.CMPNY_CODE_ID

CONTROLLING_AREA CONTROLLING_AREA.CTRL_AREA_ID=SALES_ORDER_FACT.CTRL_AREA_ID

CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_ORDER_FACT.CUSTOMER_ID_BILLTO

CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_ORDER_FACT.CUSTOMER_ID_SHIPTO

CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_ORDER_FACT.CUSTOMER_ID_SOLDTO

DISTRIBUTION_CHANNEL DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=SALES_ORDER_FACT.DISTRIBTN_CHANNEL_ID



DIVISION DIVISION.DIVISION_ID=SALES_ORDER_FACT.DIVISION_ID_HDR

DOCUMENT_CATEGORY SALES_ORDER_FACT.DOC_CATEG_ID=DOCUMENT_CATEGORY.DOC_CATEG_ID and DOCUMENT_CATEGORY.DIM_SRC_ID = 'SALES'

DOCUMENT_TYPE SALES_ORDER_FACT.DOC_CATEG_ID=DOCUMENT_TYPE.DOC_CATEG_ID and SALES_ORDER_FACT.DOC_TYPE_ID=DOCUMENT_TYPE.DOC_TYPE_ID and DOCUMENT_TYPE.DIM_SRC_ID = 'SALES'

PROFIT_CENTER SALES_ORDER_FACT.PROFIT_CNTR_ID = PROFIT_CENTER.PROFIT_CNTR_ID AND SALES_ORDER_FACT.CTRL_AREA_ID = PROFIT_CENTER.CTRL_AREA_ID

PROFIT_CENTER_HIER_HZ SALES_ORDER_FACT.PROFIT_CNTR_ID = PROFIT_CENTER_HIER_HZ.PROFIT_CNTR_ID AND SALES_ORDER_FACT.CTRL_AREA_ID = PROFIT_CENTER_HIER_HZ.CTRL_AREA_ID

PROFIT_CENTER_HIER_VR SALES_ORDER_FACT.PROFIT_CNTR_ID = PROFIT_CENTER_HIER_VR.CHILD_ID AND SALES_ORDER_FACT.CTRL_AREA_ID = PROFIT_CENTER_HIER_VR.CTRL_AREA_ID

SALES_DISTRICT SALES_DISTRICT.SALES_DISTRICT_ID=SALES_ORDER_FACT.SALES_DISTRICT_ID

SALES_GROUP SALES_GROUP.SALES_GRP_ID=SALES_ORDER_FACT.SALES_GRP_ID

SALES_OFFICE SALES_OFFICE.SALES_OFFICE_ID=SALES_ORDER_FACT.SALES_OFFICE_ID

SALES_ORDER_STATUS SALES_ORDER_FACT.SALES_DOC_STATUS_KEY=SALES_ORDER_STATUS.SALES_DOC_STATUS_KEY




SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=SALES_ORDER_FACT.SALES_ORG_ID


SALES_ORDER_FACT.SALES_ORG_ID=SALES_ORGANIZATION_HIER.SALES_ORG_ID and SALES_ORDER_FACT.SALES_GRP_ID=SALES_ORGANIZATION_HIER.SALES_GRP_ID and SALES_ORDER_FACT.SALES_OFFICE_ID=SALES_ORGANIZATION_HIER.SALES_OFFICE_ID and SALES_ORDER_FACT.DISTRIBTN_CHANNEL_ID=SALES_ORGANIZATION_HIER.DISTRIBTN_CHANNEL_ID and SALES_ORDER_FACT.DIVISION_ID_HDR=SALES_ORGANIZATION_HIER.DIVISION_ID and @Prompt('Enter Sales Org Hierarchy Date','D',,MONO,FREE) between SALES_ORGANIZATION_HIER.VALID_FROM_DATE and SALES_ORGANIZATION_HIER.VALID_TO_DATE

SALES_PERSON SALES_PERSON.SALES_PERSON_ID=SALES_ORDER_FACT.SALES_PERSON_ID

STORAGE_LOCATION STORAGE_LOCATION.STORAGE_LOCATN_ID=SALES_ORDER_FACT.STORAGE_LOCATN_ID and STORAGE_LOCATION.PLANT_ID = SALES_ORDER_FACT.PLANT_ID

DELIVERY_BLOCK DELIVERY_BLOCK.DELIV_BLOCK_ID=SALES_SCHED_LINE_FACT.DELIV_BLOCK_ID

MATERIAL MATERIAL.MATERIAL_ID=SALES_SCHED_LINE_FACT.MATERIAL_ID

MATERIAL_ALT_UOM SALES_SCHED_LINE_FACT.MATERIAL_ID=MATERIAL_ALT_UOM.MATERIAL_ID and MATERIAL_ALT_UOM.UOM_ID_ALT = @Prompt('Enter Alt. Unit of Meas.','A', ,MONO,FREE)




MATERIAL_BY_PLANT MATERIAL_BY_PLANT.PLANT_ID=SALES_SCHED_LINE_FACT.PLANT_ID and MATERIAL_BY_PLANT.MATERIAL_ID=SALES_SCHED_LINE_FACT.MATERIAL_ID

MATERIAL_GROUP SALES_SCHED_LINE_FACT.MTRL_GRP_ID=MATERIAL_GROUP.MTRL_GRP_ID

PLANT PLANT.PLANT_ID=SALES_SCHED_LINE_FACT.PLANT_ID

PRODUCT_HIERARCHY PRODUCT_HIERARCHY.PROD_HIER_ID=SALES_SCHED_LINE_FACT.PROD_HIER_ID

SALES_ORDER_FACT SALES_ORDER_FACT.SALES_DOC_ID=SALES_SCHED_LINE_FACT.SALES_DOC_ID and SALES_ORDER_FACT.SALES_LINE_ITEM_ID=SALES_SCHED_LINE_FACT.SALES_LINE_ITEM_ID

TIME_DIM SALES_SCHED_LINE_FACT.CREATE_DATE=TIME_DIM.CALENDAR_DATE

TIME_DIM SALES_SCHED_LINE_FACT.DELIV_DATE=TIME_DIM.CALENDAR_DATE

TIME_DIM SALES_SCHED_LINE_FACT.GI_DATE=TIME_DIM.CALENDAR_DATE

TIME_DIM SALES_SCHED_LINE_FACT.RQST_DELIV_DATE=TIME_DIM.CALENDAR_DATE

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=SALES_SCHED_LINE_FACT.UOM_ID_BASE

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=SALES_SCHED_LINE_FACT.UOM_ID_SALES



ReportsSales Order History 4

Sales Order HistoryThe sales order history preservation section tracks changes to sales orders. In particular, the section stores information about deleted sales orders and changes in the quantity or net item value of sales order line items.

ReportsYou can perform many different types of analysis on sales order history. Deleted Sales Order Audit is included to show one example of the usefulness of sales order history.

Deleted Sales Order AuditThis report shows the net value of Sales Orders that were deleted by sales representative during the selected Calendar Year. This report includes a graph showing the Top 10 Sales Reps by highest net value (using the Web Intelligence ranking feature).

Report SQLSELECT SALES_PERSON.SALES_PERSON_FIRST_NAME, SALES_PERSON.SALES_PERSON_LAST_NAME, TIMEDIM_CREATE_DATE.CALENDAR_YEAR, TIMEDIM_CREATE_DATE.CALENDAR_MONTH, TIMEDIM_CREATE_DATE.CALENDAR_QUARTER, sum(SALES_ORDER_HISTORY.AMT_NET_VALUE_DELTA_TRX), sum(SALES_ORDER_HISTORY.QTY_ORDER_DELTA_SLSUOM),


ReportsSales Order History4

Avg (SALES_ORDER_HISTORY.QTY_ORDER_DELTA_SLSUOM), SALES_ORGANIZATION.SALES_ORG_DESCR, SALES_ORDER_HISTORY.CREATE_DATE, SALES_ORDER_HISTORY.VALID_FROM_DATE, COMPANY_CODE.CMPNY_CODE_NAME, COMPANY_CODE.COUNTRY_NAME, SALES_ORDER_HISTORY.SALES_DOC_ID, SALES_ORDER_HISTORY.SALES_LINE_ITEM_IDFROM SALES_PERSON, TIME_DIM TIMEDIM_CREATE_DATE, SALES_ORDER_HISTORY, SALES_ORGANIZATION, COMPANY_CODE, SALES_ORDER_FACT SODIM_SALES_ORDERS_HISTWHERE ( SALES_ORDER_HISTORY.SALES_DOC_ID

=SODIM_SALES_ORDERS_HIST.SALES_DOC_ID and SALES_ORDER_HISTORY.SALES_LINE_ITEM_ID=SODIM_SALES_ORDERS_HIST.SALES_LINE_ITEM_ID )

AND ( SALES_ORDER_HISTORY.SALES_PERSON_ID=SALES_PERSON.SALES_PERSON_ID )

AND ( SALES_ORGANIZATION.SALES_ORG_ID=SALES_ORDER_HISTORY.SALES_ORG_ID )

AND ( SALES_ORDER_HISTORY.CREATE_DATE=TIMEDIM_CREATE_DATE.CALENDAR_DATE )

AND ( COMPANY_CODE.CMPNY_CODE_ID=SALES_ORDER_HISTORY.CMPNY_CODE_ID )

AND ( ( SODIM_SALES_ORDERS_HIST.REJECT_REASON_ID = 'DD' ) AND TIMEDIM_CREATE_DATE.CALENDAR_YEAR = @prompt('Calendar


)GROUP BY SALES_PERSON.SALES_PERSON_FIRST_NAME, SALES_PERSON.SALES_PERSON_LAST_NAME, TIMEDIM_CREATE_DATE.CALENDAR_YEAR, TIMEDIM_CREATE_DATE.CALENDAR_MONTH, TIMEDIM_CREATE_DATE.CALENDAR_QUARTER, SALES_ORGANIZATION.SALES_ORG_DESCR, SALES_ORDER_HISTORY.CREATE_DATE, SALES_ORDER_HISTORY.VALID_FROM_DATE, COMPANY_CODE.CMPNY_CODE_NAME, COMPANY_CODE.COUNTRY_NAME, SALES_ORDER_HISTORY.SALES_DOC_ID, SALES_ORDER_HISTORY.SALES_LINE_ITEM_ID


ReportsSales Order History 4

Recommended table joins When analyzing data from the sales order history preservation section, you need to create joins between the SALES_ORDERS_HISTORY table and other tables in the Rapid Mart.


COMPANY_CODE COMPANY_CODE.CMPNY_CODE_ID=SALES_ORDER_HISTORY.CMPNY_CODE_ID

CONTROLLING_AREA CONTROLLING_AREA.CTRL_AREA_ID=SALES_ORDER_HISTORY.CTRL_AREA_ID

CURRENCY CURRENCY.CURR_ID=SALES_ORDER_HISTORY.CURR_ID_TRX

CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_ORDER_HISTORY.CUSTOMER_ID_SOLDTO

PLANT PLANT.PLANT_ID = SALES_ORDER_HISTORY.PLANT_ID

PROFIT_CENTER SALES_ORDER_HISTORY.CTRL_AREA_ID=PROFIT_CENTER.CTRL_AREA_ID and SALES_ORDER_HISTORY.PROFIT_CNTR_ID=PROFIT_CENTER.PROFIT_CNTR_ID

PROFIT_CENTER_HIER_HZ SALES_ORDER_HISTORY.CTRL_AREA_ID=PROFIT_CENTER_HIER_HZ.CTRL_AREA_ID and SALES_ORDER_HISTORY.PROFIT_CNTR_ID=PROFIT_CENTER_HIER_HZ.PROFIT_CNTR_ID

PROFIT_CENTER_HIER_VR SALES_ORDER_HISTORY.CTRL_AREA_ID=PROFIT_CENTER_HIER_VR.CTRL_AREA_ID and SALES_ORDER_HISTORY.PROFIT_CNTR_ID=PROFIT_CENTER_HIER_VR.CHILD_ID

SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=SALES_ORDER_HISTORY.SALES_ORG_ID

SALES_PERSON SALES_ORDER_HISTORY.SALES_PERSON_ID=SALES_PERSON.SALES_PERSON_ID


ReportsDocument Flow4

Document FlowThe Document Flow is a view (SALES_DOC_FLOW_VIEW) in the Rapid Mart database that joins information from Sales Orders, Deliveries, and Billings. This allows analyses across the entire order fulfillment lifecycle with queries against related sales order, delivery, and billing documents.

ReportsNumerous analyses can be created to analyze the fulfillment lifecycle. The sample report included with this Rapid Mart is Sales Order History Document Flow.

Sales Order History Document FlowThis report shows sales order history document flow by Company Code for orders within the selected Calendar Year(s).

SODIM_SALES_ORDERS_HIST

SALES_ORDER_HISTORY.SALES_DOC_ID =SODIM_SALES_ORDERS_HIST.SALES_DOC_ID and SALES_ORDER_HISTORY.SALES_LINE_ITEM_ID=SODIM_SALES_ORDERS_HIST.SALES_LINE_ITEM_ID

STORAGE_LOCATION SALES_ORDER_HISTORY.STORAGE_LOCATN_ID=STORAGE_LOCATION.STORAGE_LOCATN_ID and SALES_ORDER_HISTORY.PLANT_ID=STORAGE_LOCATION.PLANT_ID

TIME_DIM SALES_ORDER_HISTORY.CREATE_DATE=TIME_DIM.CALENDAR_DATE

UNIT_OF_MEASURE UNIT_OF_MEASURE.UOM_ID=SALES_ORDER_HISTORY.UOM_ID_SALES

SALES_ORDER_STATUS SODIM_SALES_ORDERS_HIST.SALES_DOC_STATUS_KEY=SALES_ORDER_STATUS.SALES_DOC_STATUS_KEY



ReportsDocument Flow 4

Report SQLSELECT UOM_SALES.UOM_DESCR, CUSTOMER_SOLDTO.CUSTOMER_NAME_01, CUSTOMER_SOLDTO.CUSTOMER_NAME_02, MATERIAL.MTRL_DESCR, sum(SALES_DOC_FLOW_VIEW.QTY_BILL_SLSUOM), SUM(SALES_DOC_FLOW_VIEW.QTY_ORDER_SLSUOM), SALES_DOC_FLOW_VIEW.SALES_DOC_ID, COMPANY_CODE.CMPNY_CODE_NAME, sum(SALES_DOC_FLOW_VIEW.QTY_DELIV_SLSUOM), SODIM_DOCFLOW.CREATE_DATE_HDR, SALES_DOC_FLOW_VIEW.SALES_LINE_ITEM_ID, DOCFLOW_DELIVERY_DIM.DELIV_DATE, DOCFLOW_DELIVERY_DIM.PLAN_GI_DATE, SODIM_DOCFLOW.RQST_DELIV_DATE_HDR, sum(SALES_DOC_FLOW_VIEW.AMT_BILL_NET_VALUE_TRX), sum(SALES_DOC_FLOW_VIEW.AMT_SALES_NET_VALUE_TRX), SODIM_DOCFLOW.CURR_ID_TRXFROM UNIT_OF_MEASURE UOM_SALES,



CUSTOMER CUSTOMER_SOLDTO, MATERIAL, SALES_DOC_FLOW_VIEW, COMPANY_CODE, SALES_ORDER_FACT SODIM_DOCFLOW, DELIVERY_FACT DOCFLOW_DELIVERY_DIM, TIME_DIM TIMEDIM_CREATE_DATEWHERE (

SODIM_DOCFLOW.SALES_DOC_ID=SALES_DOC_FLOW_VIEW.SALES_DOC_ID and SODIM_DOCFLOW.SALES_LINE_ITEM_ID=SALES_DOC_FLOW_VIEW.SALES_LINE_ITEM_ID )

AND ( DOCFLOW_DELIVERY_DIM.DELIV_ID=SALES_DOC_FLOW_VIEW.DELIV_ID and DOCFLOW_DELIVERY_DIM.DELIV_LINE_ITEM_ID=SALES_DOC_FLOW_VIEW.DELIV_LINE_ITEM_ID )

AND ( MATERIAL.MATERIAL_ID=SALES_DOC_FLOW_VIEW.MATERIAL_ID ) AND (

CUSTOMER_SOLDTO.CUSTOMER_ID=SALES_DOC_FLOW_VIEW.CUSTOMER_ID_SOLDTO )

AND ( COMPANY_CODE.CMPNY_CODE_ID = SALES_DOC_FLOW_VIEW.CMPNY_CODE_ID )

AND ( UOM_SALES.UOM_ID=SODIM_DOCFLOW.UOM_ID_SALES ) AND (

SODIM_DOCFLOW.CREATE_DATE=TIMEDIM_CREATE_DATE.CALENDAR_DATE )

AND ( TIMEDIM_CREATE_DATE.CALENDAR_YEAR = @prompt('Calendar


AND SALES_DOC_FLOW_VIEW.SALES_DOC_ID In @prompt('Sales Order

Number(s):','A','Doc Flow - Additional Details (IDs)\Sales Document Id',Multi,Free,Persistent,,User:0)

)GROUP BY UOM_SALES.UOM_DESCR, CUSTOMER_SOLDTO.CUSTOMER_NAME_01, CUSTOMER_SOLDTO.CUSTOMER_NAME_02, MATERIAL.MTRL_DESCR, SALES_DOC_FLOW_VIEW.SALES_DOC_ID, COMPANY_CODE.CMPNY_CODE_NAME, SODIM_DOCFLOW.CREATE_DATE_HDR, SALES_DOC_FLOW_VIEW.SALES_LINE_ITEM_ID, DOCFLOW_DELIVERY_DIM.DELIV_DATE, DOCFLOW_DELIVERY_DIM.PLAN_GI_DATE, SODIM_DOCFLOW.RQST_DELIV_DATE_HDR, SODIM_DOCFLOW.CURR_ID_TRX


ReportsDocument Flow 4

Recommended Table JoinsWhen analyzing data from the sales document flow section, you need to create joins between the SALES_DOC_FLOW_VIEW and other tables in the Rapid Mart.


BILLING_FACT (Fact to Fact - allows billings to be used as a dimension to the view)

BILLING_FACT.BILL_DOC_ID=SALES_DOC_FLOW_VIEW.BILL_DOC_ID and BILLING_FACT.BILL_LINE_ITEM_ID=SALES_DOC_FLOW_VIEW.BILL_LINE_ITEM_ID

COMPANY_CODE COMPANY_CODE.CMPNY_CODE_ID = SALES_DOC_FLOW_VIEW.CMPNY_CODE_ID

CONTROLLING_AREA SALES_DOC_FLOW_VIEW.CTRL_AREA_ID = CONTROLLING_AREA.CTRL_AREA_ID

CURRENCY CURRENCY.CURR_ID=SALES_DOC_FLOW_VIEW.CURR_ID_GBL

CURRENCY CURRENCY.CURR_ID=SALES_DOC_FLOW_VIEW.CURR_ID_TRX

CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_DOC_FLOW_VIEW.CUSTOMER_ID_BILLTO

CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_DOC_FLOW_VIEW.CUSTOMER_ID_SHIPTO

CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_DOC_FLOW_VIEW.CUSTOMER_ID_SOLDTO

DELIVERY_FACT DELIVERY_FACT.DELIV_ID=SALES_DOC_FLOW_VIEW.DELIV_ID and DELIVERY_FACT.DELIV_LINE_ITEM_ID=SALES_DOC_FLOW_VIEW.DELIV_LINE_ITEM_ID

DISTRIBUTION_CHANNEL DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=SALES_DOC_FLOW_VIEW.DISTRIBTN_CHANNEL_ID

DIVISION DIVISION.DIVISION_ID=SALES_DOC_FLOW_VIEW.DIVISION_ID



DOCUMENT_CATEGORY SALES_DOC_FLOW_VIEW.DOC_CATEG_ID=DOCUMENT_CATEGORY.DOC_CATEG_ID and DOCUMENT_CATEGORY.DIM_SRC_ID = 'SALES'

MATERIAL MATERIAL.MATERIAL_ID=SALES_DOC_FLOW_VIEW.MATERIAL_ID

MATERIAL_ALT_UOM SALES_DOC_FLOW_VIEW.MATERIAL_ID=MATERIAL_ALT_UOM.MATERIAL_ID and MATERIAL_ALT_UOM.UOM_ID_ALT = @Prompt('Enter Alt. Unit of Meas.','A', ,MONO,FREE)

MATERIAL_BY_PLANT MATERIAL_BY_PLANT.PLANT_ID=SALES_DOC_FLOW_VIEW.PLANT_ID and MATERIAL_BY_PLANT.MATERIAL_ID=SALES_DOC_FLOW_VIEW.MATERIAL_ID

PLANT PLANT.PLANT_ID=SALES_DOC_FLOW_VIEW.PLANT_ID

PROFIT_CENTER PROFIT_CENTER.PROFIT_CNTR_ID=SALES_DOC_FLOW_VIEW.PROFIT_CNTR_ID

PROFIT_CENTER_HIER_VR SALES_DOC_FLOW_VIEW.CTRL_AREA_ID=PROFIT_CENTER_HIER_VR.CTRL_AREA_ID and SALES_DOC_FLOW_VIEW.PROFIT_CNTR_ID=PROFIT_CENTER_HIER_VR.CHILD_ID

SALES_DISTRICT SALES_DISTRICT.SALES_DISTRICT_ID=SALES_DOC_FLOW_VIEW.SALES_DISTRICT_ID

SALES_GROUP SALES_GROUP.SALES_GRP_ID=SALES_DOC_FLOW_VIEW.SALES_GRP_ID

SALES_OFFICE SALES_OFFICE.SALES_OFFICE_ID=SALES_DOC_FLOW_VIEW.SALES_OFFICE_ID

SALES_ORDER_FACT SALES_ORDER_FACT.SALES_DOC_ID=SALES_DOC_FLOW_VIEW.SALES_DOC_ID and SALES_ORDER_FACT.SALES_LINE_ITEM_ID=SALES_DOC_FLOW_VIEW.SALES_LINE_ITEM_ID



ReportsInvalid Documents 4

Invalid DocumentsThis Rapid Mart comes with BILLING_FACT_BAD and SALES_ORDER_FACT_BAD tables. Upon running the Rapid Mart load data is loaded into these tables where there are issues with monetary exchange rates for converting documents to global currency. You have the ability to report on these tables via the Universe.

ReportsTwo reports are included to report on Invalid Documents. Sales Order Data Validation and Billing Data Validation.

Sales Order Data ValidationInvalid Sales Documents identified during Rapid Mart load.

SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=SALES_DOC_FLOW_VIEW.SALES_ORG_ID

SALES_PERSON SALES_PERSON.SALES_PERSON_ID=SALES_DOC_FLOW_VIEW.SALES_PERSON_ID

STORAGE_LOCATION STORAGE_LOCATION.STORAGE_LOCATN_ID = SALES_DOC_FLOW_VIEW.STORAGE_LOCATN_ID AND STORAGE_LOCATION.PLANT_ID = SALES_DOC_FLOW_VIEW.PLANT_ID



ReportsInvalid Documents4

Report SQLSELECT SALES_ORDER_FACT_BAD.SALES_DOC_ID, SALES_ORDER_FACT_BAD.SALES_LINE_ITEM_ID, SALES_ORDER_FACT_BAD.ETL_ERROR_ACTN, SALES_ORDER_FACT_BAD.ETL_ERROR_COLUMN, SALES_ORDER_FACT_BAD.AMT_NET_VALUE_HDR_TRX, SALES_ORDER_FACT_BAD.EXCH_TYPE_ID_GBL, SALES_ORDER_FACT_BAD.EXCH_RATE_GBL, SALES_ORDER_FACT_BAD.FROM_FACTOR_GBL, SALES_ORDER_FACT_BAD.TO_FACTOR_GBL, SALES_ORDER_FACT_BAD.AMT_NET_VALUE_HDR_GBL, SALES_ORDER_FACT_BAD.CREATE_DATE_HDR, SALES_ORDER_FACT_BAD.CURR_ID_GBL, SALES_ORDER_FACT_BAD.CURR_ID_TRX, PLANT.PLANT_NAME_01, MATERIAL.MTRL_DESCRFROM SALES_ORDER_FACT_BAD, PLANT, MATERIALWHERE ( MATERIAL.MATERIAL_ID=SALES_ORDER_FACT_BAD.MATERIAL_ID ) AND ( PLANT.PLANT_ID=SALES_ORDER_FACT_BAD.PLANT_ID ) AND ( SALES_ORDER_FACT_BAD.CREATE_DATE_HDR BETWEEN @prompt('Order

Create Date (Start):','D','Sales Order Objects\Order Create Date',Mono,Free,Persistent,,User:1) AND @prompt('Order Create Date (End):','D','Sales Order Objects\Order Create Date',Mono,Free,Persistent,,User:2)

AND SALES_ORDER_FACT_BAD.CURR_ID_TRX In @prompt('Document Currency

Id(s):','A','Sales Order Objects\Doc Currency Id',Multi,Free,Persistent,,User:0) )

Billing Data ValidationInvalid Billing Documents identified during Rapid Mart load.



ReportsInvalid Documents 4

Recommended Table JoinsThese Bad data tables are included to help identify problems with data in the source SAP solutions system. Here are the Rapid Mart joins that would be useful to further analyze the bad data identified by the load.

Invalid Sales OrdersThis is the list of joins relevant to Sales Order Fact validation.

Invalid Billing DocumentsThis is the list of joins relevant to Billing Fact validation.


CUSTOMER CUSTOMER.CUSTOMER_ID=SALES_ORDER_FACT_BAD.CUSTOMER_ID_SOLDTO

DISTRIBUTION_CHANNEL DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=SALES_ORDER_FACT_BAD.DISTRIBTN_CHANNEL_ID

DIVISION SALES_ORDER_FACT_BAD.DIVISION_ID_HDR=DIVISION.DIVISION_ID

MATERIAL MATERIAL.MATERIAL_ID=SALES_ORDER_FACT_BAD.MATERIAL_ID

MATERIAL_BY_PLANT MATERIAL_BY_PLANT.PLANT_ID=SALES_ORDER_FACT_BAD.PLANT_ID and MATERIAL_BY_PLANT.MATERIAL_ID=SALES_ORDER_FACT_BAD.MATERIAL_ID

PLANT PLANT.PLANT_ID=SALES_ORDER_FACT_BAD.PLANT_ID

SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=SALES_ORDER_FACT_BAD.SALES_ORG_ID


CUSTOMER CUSTOMER.CUSTOMER_ID=BILLING_FACT_BAD.CUSTOMER_ID_SOLDTO

DISTRIBUTION_CHANNEL DISTRIBUTION_CHANNEL.DISTRIBTN_CHANNEL_ID=BILLING_FACT_BAD.DISTRIBTN_CHANNEL_ID

DIVISION BILLING_FACT_BAD.DIVISION_ID=DIVISION.DIVISION_ID


ReportsSAP Master Data Reports4

SAP Master Data ReportsSAP Master Data reports provide a tool for Data Discovery and Data Auditing. These reports are used by all Rapid Marts in the SAP BusinessObjects Rapid Mart Suite for SAP. Note: This universe only includes joins for hierarchy dimensions. No other joins are provided as they are specific to each Rapid Mart. Recommended table joins contains a complete list of joins provided in the SAP Master Data universe.

Data DiscoveryThese reports allow users to view hierarchy data. The full breadth of the data can be ascertained and used to make informed decisions when designing a report for any given subject area. The reports are:• Activity Hierarchy Data Discovery• Cost Center Hierarchy Data Discovery• Profit Center Hierarchy Data Discovery• Cost Element Hierarchy Data Discovery• Internal Order Hierarchy Data Discovery• Functional Area Hierarchy Data Discovery• GL Account Hierarchy Data Discovery• FS Account Hierarchy Data Discovery• Project Element Hierarchy Data Discovery

MATERIAL MATERIAL.MATERIAL_ID=BILLING_FACT_BAD.MATERIAL_ID

MATERIAL_BY_PLANT MATERIAL_BY_PLANT.PLANT_ID=BILLING_FACT_BAD.PLANT_ID and MATERIAL_BY_PLANT.MATERIAL_ID=BILLING_FACT_BAD.MATERIAL_ID

PLANT PLANT.PLANT_ID=BILLING_FACT_BAD.PLANT_ID

SALES_ORGANIZATION SALES_ORGANIZATION.SALES_ORG_ID=BILLING_FACT_BAD.SALES_ORG_ID



ReportsSAP Master Data Reports 4

• Equipment Hierarchy Data Discovery• Functional Location Hierarchy Data Discovery• Work Center Hierarchy Data Discovery• BOM Hierarchy Data Discovery• Organization Unit Data Discovery• Position Data Discovery

Activity Hierarchy Data DiscoveryView Activity Hierarchies for a specified Controlling Area, Parent Activity, and Activity Hierarchy.

Report SQLSELECT DISTINCT ACTIVITY_HIER_HR.ACTIVITY_ID, ACTIVITY_HIER_HR.CTRL_AREA_ID, ACTIVITY_HIER_HR.LEAF_LEVEL, ACTIVITY_HIER_HR.LEVEL_0_FULLNAME, ACTIVITY_HIER_HR.LEVEL_1_FULLNAME, ACTIVITY_HIER_HR.LEVEL_2_FULLNAME, ACTIVITY_HIER_HR.LEVEL_3_FULLNAME, ACTIVITY_HIER_HR.LEVEL_4_FULLNAME, ACTIVITY_HIER_HR.LEVEL_5_FULLNAME, ACTIVITY_HIER_HR.LEVEL_6_FULLNAME,



ACTIVITY_HIER_HR.LEVEL_7_FULLNAMEFROM ACTIVITY_HIER_HR, ACTIVITY_HIER_VR, ACTIVITYWHERE ( ACTIVITY.CTRL_AREA_ID = ACTIVITY_HIER_VR.CTRL_AREA_ID and ACTIVITY.ACTIVITY_ID = ACTIVITY_HIER_VR.DESCENDANT_ID ) AND ( ACTIVITY.CTRL_AREA_ID=ACTIVITY_HIER_HR.CTRL_AREA_ID and

ACTIVITY.ACTIVITY_ID=ACTIVITY_HIER_HR.ACTIVITY_ID ) AND ( ( ACTIVITY_HIER_VR.ANCESTOR_ID in ('A1') AND

ACTIVITY_HIER_HR.LEVEL_0_ID in ('A1') ) AND ACTIVITY_HIER_HR.CTRL_AREA_ID = '1000' AND ACTIVITY_HIER_HR.CTRL_AREA_ID <> '?' AND ACTIVITY_HIER_HR.LEVEL_0_ID <> '?' )

Cost Center Hierarchy Data Discovery View Cost Center Hierarchies for a specified Controlling Area, Parent Cost Center, and Cost Center Hierarchy.



Report SQLAll report SQL is located in the Rapid Mart installation directory under "\Sample\DDL".


Profit Center Hierarchy Data Discovery
View Profit Center Hierarchies for a specified Controlling Area, Parent Profit Center(s), and Profit Center Hierarchy (or Hierarchies).


Cost Element Hierarchy Data DiscoveryView Cost Element Hierarchies for a specified Chart of Accounts, Parent Cost Element, and Cost Element Hierarchy.




Internal Order Hierarchy Data DiscoveryView Internal Order Hierarchies for a specified Controlling Area, Parent Internal Order, and Internal Order Hierarchy.



Functional Area Hierarchy Data Discovery
View Functional Area Hierarchies for a specified Parent Functional Area and Functional Area Hierarchy.




GL Account Hierarchy Data DiscoveryView GL Account, Profit Center GL Account, and Financial Statement (FS) Hierarchies for a specified Chart of Accounts, Parent GL Account, and GL Account Hierarchy.This report uses a prompt to indicate which type of financial hierarchy should be analyzed: • GL Account Hierarchy = @GL• Profit Center GL Account Hierarchy = @PC• FS Hierarchy = code of the financial statement (ex: CAUS)



FS Account Hierarchy Data Discovery
View account information for a specified Chart of Accounts and FS Account hierarchy, along with account ranges, credit and debit flags, and account full names.

Report SQLSELECT DISTINCT GL_ACCOUNT_HIER_HZ.LEVEL_0_FULLNAME, GL_ACCOUNT_HIER_HZ.LEVEL_1_FULLNAME, GL_ACCOUNT_HIER_HZ.LEVEL_2_FULLNAME,



GL_ACCOUNT_HIER_HZ.LEVEL_3_FULLNAME, GL_ACCOUNT_HIER_HZ.LEVEL_4_FULLNAME, GL_ACCOUNT_HIER_HZ.LEVEL_5_FULLNAME, GL_ACCOUNT_HIER_HZ.LEVEL_6_FULLNAME, GL_ACCOUNT_HIER_HZ.LEVEL_7_FULLNAME, FS_HIERARCHY_DISCOVERY_HZ.CREDIT_FLAG, FS_HIERARCHY_DISCOVERY_HZ.DEBIT_FLAG, FS_HIERARCHY_DISCOVERY_HZ.LOWER_ACCT_LIMIT, FS_HIERARCHY_DISCOVERY_HZ.UPPER_ACCT_LIMIT, GL_ACCOUNT_DISCOVERY_HZ.LEAF_FLAG, GL_ACCOUNT_DISCOVERY_HZ.HIERARCHY_NAMEFROM GL_ACCOUNT_HIER_HZ, FS_HIERARCHY FS_HIERARCHY_DISCOVERY_HZ, GL_ACCOUNT_HIER GL_ACCOUNT_DISCOVERY_HZ, GL_ACCOUNT_MASTERWHERE (

GL_ACCOUNT_MASTER.CHART_OF_ACCTS_ID=GL_ACCOUNT_HIER_HZ.CHART_OF_ACCTS_ID and GL_ACCOUNT_MASTER.GL_ACCOUNT_ID=GL_ACCOUNT_HIER_HZ.CURRENT_LEAF AND

GL_ACCOUNT_MASTER.HIERARCHY_NAME in ('ACCOUNT','CAUS')AND GL_ACCOUNT_HIER_HZ.HIERARCHY_NAME = 'CAUS' ) AND (

GL_ACCOUNT_HIER_HZ.HIERARCHY_NAME=GL_ACCOUNT_DISCOVERY_HZ.HIERARCHY_NAME and GL_ACCOUNT_HIER_HZ.CHART_OF_ACCTS_ID=GL_ACCOUNT_DISCOVERY_HZ.CHART_OF_ACCTS_ID and GL_ACCOUNT_HIER_HZ.CURRENT_LEAF=GL_ACCOUNT_DISCOVERY_HZ.CHILD_ID )

AND ( GL_ACCOUNT_HIER_HZ.CHART_OF_ACCTS_ID=FS_HIERARCHY_DISCOVERY_HZ.CHART_OF_ACCTS_ID and GL_ACCOUNT_HIER_HZ.HIERARCHY_NAME=FS_HIERARCHY_DISCOVERY_HZ.FS_VERSION_ID and GL_ACCOUNT_HIER_HZ.CURRENT_LEAF=FS_HIERARCHY_DISCOVERY_HZ.CHILD_ID )

AND ( GL_ACCOUNT_HIER_HZ.CHART_OF_ACCTS_ID = 'CAUS' AND ( GL_ACCOUNT_MASTER.HIERARCHY_NAME NOT IN ( '@PC', '@GL') ) AND GL_ACCOUNT_HIER_HZ.LEVEL_2_ID <>

GL_ACCOUNT_HIER_HZ.LEVEL_1_ID AND GL_ACCOUNT_HIER_HZ.LEVEL_3_ID <>



GL_ACCOUNT_HIER_HZ.LEVEL_4_ID )


Project Element Hierarchy Data Discovery
View Project Element Hierarchies for a specified Parent Project Element and Project Element Hierarchy.


Equipment Hierarchy Data DiscoveryView Equipment Hierarchies for a specified Parent Equipment and Equipment Hierarchy.




Functional Location Hierarchy Data DiscoveryView Functional Location Hierarchies for a specified Parent Functional Location and Functional Location Hierarchy.


Work Center Hierarchy Data DiscoveryView Work Center Hierarchies for a specified Parent Work Center and Work Center Hierarchy.



BOM Hierarchy Data Discovery
View BOM Hierarchies for a specified BOM Id and Parent BOM Hierarchy.




Organization Unit Data DiscoveryView Organization Unit Hierarchies for a specified Parent Organization Unit(s).


Position Data DiscoveryView Position Hierarchies for the specified Parent Position(s).



Data Auditing
These reports allow users to view hierarchy data records that have failed validation in Data Services. This enables correction of the data in the SAP solutions source system. Nine data auditing reports are included with the SAP Master Data universe. These reports display data in validation tables as follows:

All data auditing reports are all similar in structure. Invalid Cost Center Hierarchy is show below as an example of the hierarchy data auditing reports.

Invalid Cost Center HierarchyShow Cost Center Hierarchy records that have failed validation in Data Services.

Report SQLSELECT DISTINCT COST_CNTR_HIER_BAD.PARENT_ID, COST_CNTR_HIER_BAD.PARENT_DESC, COST_CNTR_HIER_BAD.CTRL_AREA_ID, COST_CNTR_HIER_BAD.CHILD_ID,

Report Name Invalid Data Table

Invalid Cost Center Hierarchy COST_CENTER_HIER_BADInvalid Activity Hierarchy ACTIVITY_HIER_BADInvalid Cost Element Hierarchy COST_ELEMENT_HIER_BADInvalid Functional Area Hierarchy FUNCTIONAL_AREA_HIER_BADInvalid GL Account Hierarchy GL_ACCOUNT_HIER_BADInvalid Internal Order Hierarchy INTERNAL_ORDER_HIER_BADInvalid Profit Center Hierarchy PROFIT_CENTER_HIER_BADInvalid Project Element Hierarchy PROJECT_ELEMENT_HIER_BADInvalid Work Center Hierarchy WORK_CENTER_HIER_BAD



COST_CNTR_HIER_BAD.CHILD_DESC, COST_CNTR_HIER_BAD.DI_ERRORACTION, COST_CNTR_HIER_BAD.DI_ERRORCOLUMNS, COST_CNTR_HIER_BAD.LEAF_FLAG, COST_CNTR_HIER_BAD.LOAD_DATE, COST_CNTR_HIER_BAD.LOAD_TIME, COST_CNTR_HIER_BAD.ROOT_FLAGFROM COST_CNTR_HIER_BAD

Recommended table joins When analyzing data hierarchy data, you need to create joins between the dimension table and the hierarchy tables in the Rapid Mart. The following shows joins provided in the SAP Master Data universe.

Dimension Table Hierarchy Table Join Text

ACTIVITY ACTIVITY_HIER_HZ ACTIVITY.CTRL_AREA_ID=ACTIVITY_HIER_HZ.CTRL_AREA_ID and ACTIVITY.ACTIVITY_ID=ACTIVITY_HIER_HZ.ACTIVITY_ID

ACTIVITY ACTIVITY_HIER_VR ACTIVITY.CTRL_AREA_ID = ACTIVITY_HIER_VR.CTRL_AREA_ID and ACTIVITY.ACTIVITY_ID = ACTIVITY_HIER_VR.CHILD_ID

BILL_OF_MATERIALS BILL_OF_MATERIALS_HIER_HZ

BILL_OF_MATERIALS.BOM_CATEG_ID=BILL_OF_MATERIALS_HIER_HZ.BOM_CATEG_ID and BILL_OF_MATERIALS.BOM_ID=BILL_OF_MATERIALS_HIER_HZ.BOM_ID and BILL_OF_MATERIALS.ALT_BOM_ID=BILL_OF_MATERIALS_HIER_HZ.ALT_BOM_ID and BILL_OF_MATERIALS.BOM_NODE_ID=BILL_OF_MATERIALS_HIER_HZ.BOM_NODE_ID

BILL_OF_MATERIALS BILL_OF_MATERIALS_HIER_VR

BILL_OF_MATERIALS.BOM_CATEG_ID=BILL_OF_MATERIALS_HIER_VR.BOM_CATEG_ID and BILL_OF_MATERIALS.BOM_ID=BILL_OF_MATERIALS_HIER_VR.BOM_ID and BILL_OF_MATERIALS.ALT_BOM_ID=BILL_OF_MATERIALS_HIER_VR.ALT_BOM_ID and BILL_OF_MATERIALS.BOM_NODE_ID=BILL_OF_MATERIALS_HIER_VR.CHILD_ID

COST_CENTER COST_CENTER_HIER_HZ COST_CENTER.CTRL_AREA_ID=COST_CENTER_HIER_HZ.CTRL_AREA_ID and COST_CENTER.COST_CNTR_ID=COST_CENTER_HIER_HZ.COST_CNTR_ID



COST_CENTER COST_CENTER_HIER_VR COST_CENTER.CTRL_AREA_ID=COST_CENTER_HIER_VR.CTRL_AREA_ID and COST_CENTER.COST_CNTR_ID=COST_CENTER_HIER_VR.CHILD_ID

COST_ELEMENT COST_ELEMENT_HIER_HZ

COST_ELEMENT.COST_ELEM_ID=COST_ELEMENT_HIER_HZ.COST_ELEM_ID and COST_ELEMENT.CHART_OF_ACCT_ID=COST_ELEMENT_HIER_HZ.CHART_OF_ACCT_ID

COST_ELEMENT COST_ELEMENT_HIER_VR

COST_ELEMENT.CHART_OF_ACCT_ID=COST_ELEMENT_HIER_VR.CHART_OF_ACCT_ID and COST_ELEMENT.COST_ELEM_ID=COST_ELEMENT_HIER_VR.CHILD_ID

COUNTRY_GROUP COUNTRY_GROUP_STATUS

COUNTRY_GROUP.COUNTRY_GRP_ID = COUNTRY_GROUP_STATUS.COUNTRY_GRP_ID

EQUIPMENT EQUIPMENT_HIER_HZ EQUIPMENT.EQUIPMNT_ID=EQUIPMENT_HIER_HZ.EQUIPMNT_ID

EQUIPMENT EQUIPMENT_HIER_VR EQUIPMENT.EQUIPMNT_ID=EQUIPMENT_HIER_VR.CHILD_ID

FS_HIERARCHY GL_ACCOUNT_HIER_HZ GL_ACCOUNT_HIER_HZ.CHART_OF_ACCT_ID=FS_HIERARCHY.CHART_OF_ACCT_ID and GL_ACCOUNT_HIER_HZ.HIER_ID=FS_HIERARCHY.FS_VERSION_ID and GL_ACCOUNT_HIER_HZ.GL_ACCT_ID=FS_HIERARCHY.CHILD_ID

FS_HIERARCHY GL_ACCOUNT_HIER_VR GL_ACCOUNT_HIER_VR.CHART_OF_ACCT_ID=FS_HIERARCHY.CHART_OF_ACCT_ID and GL_ACCOUNT_HIER_VR.HIER_ID=FS_HIERARCHY.FS_VERSION_ID and GL_ACCOUNT_HIER_VR.CHILD_ID=FS_HIERARCHY.CHILD_ID

FUNCTIONAL_AREA FUNCTIONAL_AREA_HIER_HZ

FUNCTIONAL_AREA.FUNCTNL_AREA_ID=FUNCTIONAL_AREA_HIER_HZ.FUNCTNL_AREA_ID

FUNCTIONAL_AREA FUNCTIONAL_AREA_HIER_VR

FUNCTIONAL_AREA.FUNCTNL_AREA_ID=FUNCTIONAL_AREA_HIER_VR.CHILD_ID

FUNCTIONAL_LOCATION FUNCTIONAL_LOCATION_HIER_HZ

FUNCTIONAL_LOCATION.FUNCTNL_LOCATN_ID=FUNCTIONAL_LOCATION_HIER_HZ.FUNCTNL_LOCATN_ID

FUNCTIONAL_LOCATION FUNCTIONAL_LOCATION_HIER_VR

FUNCTIONAL_LOCATION.FUNCTNL_LOCATN_ID=FUNCTIONAL_LOCATION_HIER_VR.CHILD_ID

GL_ACCOUNT_HIER GL_ACCOUNT_HIER_HZ GL_ACCOUNT_HIER_HZ.HIER_ID=GL_ACCOUNT_HIER.HIER_ID and GL_ACCOUNT_HIER_HZ.CHART_OF_ACCT_ID=GL_ACCOUNT_HIER.CHART_OF_ACCT_ID and GL_ACCOUNT_HIER_HZ.GL_ACCT_ID=GL_ACCOUNT_HIER.CHILD_ID




GL_ACCOUNT_HIER GL_ACCOUNT_HIER_VR GL_ACCOUNT_HIER_VR.HIER_ID=GL_ACCOUNT_HIER.HIER_ID and GL_ACCOUNT_HIER_VR.CHART_OF_ACCT_ID=GL_ACCOUNT_HIER.CHART_OF_ACCT_ID and GL_ACCOUNT_HIER_VR.CHILD_ID=GL_ACCOUNT_HIER.CHILD_ID

GL_ACCOUNT_MASTER GL_ACCOUNT_HIER_HZ GL_ACCOUNT_MASTER.CHART_OF_ACCT_ID=GL_ACCOUNT_HIER_HZ.CHART_OF_ACCT_ID and GL_ACCOUNT_MASTER.GL_ACCT_ID=GL_ACCOUNT_HIER_HZ.GL_ACCT_ID AND GL_ACCOUNT_MASTER.HIER_ID in ('ACCOUNT',@Prompt('Select GL Account Hierarchy Code','A','GL Account\Hierarchy Name',mono,free)) AND GL_ACCOUNT_HIER_HZ.HIER_ID = @Prompt('Select GL Account Hierarchy Code','A','GL Account Hierarchy Hz\Hierarchy Name',mono,free)

GL_ACCOUNT_MASTER GL_ACCOUNT_HIER_VR GL_ACCOUNT_MASTER.CHART_OF_ACCT_ID=GL_ACCOUNT_HIER_VR.CHART_OF_ACCT_ID and GL_ACCOUNT_MASTER.GL_ACCT_ID=GL_ACCOUNT_HIER_VR.CHILD_ID AND GL_ACCOUNT_MASTER.HIER_ID in ('ACCOUNT',@Prompt('Select GL Account Hierarchy Code','A','GL Account\Hierarchy Name',mono,free)) AND GL_ACCOUNT_HIER_VR.HIER_ID = @Prompt('Select GL Account Hierarchy Code','A','GL Account Hierarchy Vr\Hierarchy Name',mono,free)

INTERNAL_ORDER INTERNAL_ORDER_HIER_HZ

INTERNAL_ORDER.INTERNAL_ORDER_ID=INTERNAL_ORDER_HIER_HZ.INTERNAL_ORDER_ID

INTERNAL_ORDER INTERNAL_ORDER_HIER_VR

INTERNAL_ORDER.INTERNAL_ORDER_ID=INTERNAL_ORDER_HIER_VR.CHILD_ID

MATERIAL PRODUCT_HIERARCHY MATERIAL.PROD_HIER_ID=PRODUCT_HIERARCHY.PROD_HIER_ID

OBJECT_STATUS_HZ OBJECT_STATUS OBJECT_STATUS_HZ.OBJ_ID=OBJECT_STATUS.OBJ_ID

ORGANIZATION_UNIT ORGANIZATION_UNIT_HIER_HZ

ORGANIZATION_UNIT.ORG_UNIT_KEY =ORGANIZATION_UNIT_HIER_HZ.ORG_UNIT_KEY

ORGANIZATION_UNIT ORGANIZATION_UNIT_HIER_VR

ORGANIZATION_UNIT.ORG_UNIT_KEY=ORGANIZATION_UNIT_HIER_VR.CHILD_KEY

PAYROLL_PERIOD PAYROLL_PERIOD_PARAMETER

PAYROLL_PERIOD.PERIOD_PARAMETER_ID = PAYROLL_PERIOD_PARAMETER.PERIOD_PARAMETER_ID




POSITION POSITION_HIER_HZ POSITION.POSITION_ID=POSITION_HIER_HZ.POSITION_ID

POSITION POSITION_HIER_VR POSITION.POSITION_ID=POSITION_HIER_VR.CHILD_ID

PROFIT_CENTER PROFIT_CENTER_HIER_HZ

PROFIT_CENTER.CTRL_AREA_ID=PROFIT_CENTER_HIER_HZ.CTRL_AREA_ID and PROFIT_CENTER.PROFIT_CNTR_ID=PROFIT_CENTER_HIER_HZ.PROFIT_CNTR_ID

PROFIT_CENTER PROFIT_CENTER_HIER_VR

PROFIT_CENTER.CTRL_AREA_ID=PROFIT_CENTER_HIER_VR.CTRL_AREA_ID and PROFIT_CENTER.PROFIT_CNTR_ID=PROFIT_CENTER_HIER_VR.CHILD_ID

PROJECT_ELEMENT PROJECT_ELEMENT_HIER_HZ

PROJECT_ELEMENT.PROJ_ELEM_ID=PROJECT_ELEMENT_HIER_HZ.PROJ_ELEM_ID

PROJECT_ELEMENT PROJECT_ELEMENT_HIER_VR

PROJECT_ELEMENT.PROJ_ELEM_ID=PROJECT_ELEMENT_HIER_VR.CHILD_ID

WORK_CENTER WORK_CENTER_HIER_HZ WORK_CENTER.WORK_CNTR_ID=WORK_CENTER_HIER_HZ.WORK_CNTR_ID and WORK_CENTER.PLANT_ID=WORK_CENTER_HIER_HZ.PLANT_ID

WORK_CENTER WORK_CENTER_HIER_VR WORK_CENTER.WORK_CNTR_ID=WORK_CENTER_HIER_VR.CHILD_ID AND WORK_CENTER.PLANT_ID=WORK_CENTER_HIER_VR.PLANT_ID

