SAP Smart Business for Retail Promotion Execution: Query Views and Their Usage on the UI

The application SAP Smart Business for Retail Promotion Execution contains several query views that have been specifically designed for consumption by this or by a similar application.

These query views belong to the product SAP Smart Business 1.0 for Retail Promotion Execution (SAP SMART BUSINESS FOR RTL 1.0) and are provided in two different packages which are contained in two different software components/delivery units. The query views are based on reuse views contained in the virtual data model of software component HCO_HBA_RTL in product SAP HANA Live for Retail:

SAP Smart Business Product	SAP Smart Business 1.0 for retail promotion execution SPS 2 (SAP SMART BUSINESS FOR RTL 1.0)
Query Views	RetailPromnPrepIssuesTileQuery RetailPromnPrepIssuesQuery RetailPromnProcessStepsQuery RetailPromnAllocTableItmIssuesQuery RetailPromnPurPriceItmIssuesQuery RetailPromnSlsPriceItmIssuesQuery RetailPromnDCPriceItmIssuesQuery RetailPromnListingItmIssuesQuery RetailPromnSOSDetnItmIssuesQuery RetailPromnBlockedItmIssuesQuery RetailPromnAllocPurOrdItmIssQuery RetailPromnAllocStkTransptItmIssQuery RetailPromnGdsRcptFromDCItmIssQuery RetailPromnGdsRcptFromDCSchedLineIssQuery RetailPromnGdsRcptFromVendorItmIssQuery RetailPromnGdsRcptFromVendorSchedLineIssQuery
Software Component/Delivery Unit for Query Views	HANA CONTENT REUSE RTL 100 (HCO_HBA_R_RTL) HANA CONTENT HBA RTL S 100 (HCO_HBA_A_RTL_S)
Packages for Query Views	sap.hba.r.rtl.v sap.hba.apps.rtl.s.v
Contains Virtual Data Model (VDM)	SAP HANA Live 1.0 for Retail SPS 3 (SAP HANA ANALYTICS F. RTL 1.0), software component HCO_HBA_RTL Note: The VDM is part of the HANA Content Component.

Relationship UI Artifact (KPI Tile/Screen Area) – Query Views

The following table shows how the query views relate to the KPI tiles shown on the Fiori Launchpad (which are the entry points to the application), and how they relate to the different screen areas of the UI application. The communication is performed via OData services. In the communication between the UI and query views, different input parameters and/or filters are used, which the query views apply to reduce the result set - see separate section "Filters" in this documentation.

In the tile configuration, the mandatory input parameters and the optional filters are pre-defined specifically per tile. They are applied to the initial set of data shown when entering the overview screen. Some filters and input parameters can be changed by the user on the overview screen via filter options. After these filters are applied on the UI, the query is called again to determine an updated result.

UI artifact (KPI tile / Screen area)	Query views
Promotions Containing Issues: In All Process Steps (KPI Tile)	RetailPromnPrepIssuesTileQuery
Overdue Issues in Promotions: In All Process Steps (KPI Tile)	RetailPromnPrepIssuesTileQuery
Overdue Issues in Promotions: Logistics Manager View (KPI Tile)	RetailPromnPrepIssuesTileQuery
Total Issues in Promotions: In All Process Steps (KPI Tile)	RetailPromnPrepIssuesTileQuery
Overview screen	RetailPromnPrepIssuesQuery
Master detail screen: Master list with processing steps with issues for a promotion which was selected in the overview screen Header area of detailed screen per process step	RetailPromnProcessStepsQuery
Master detail screen: Details for process step Purchase Price Activation selected in the master list of processing steps with issue	RetailPromnPurPriceItmIssuesQuery
Master detail screen: Details for process step Sales Price Activation selected in the master list of processing steps with issue	RetailPromnSlsPriceItmIssuesQuery
Master detail screen: Details for process step DC Price Activation selected in the master list of processing steps with issue	RetailPromnDCPriceItmIssuesQuery
Master detail screen: Details for process step Listing selected in the master list of processing steps with issue	RetailPromnListingItmIssuesQuery
Master detail screen: Details for process step Source of Supply Determination selected in the master list of processing steps with issue	RetailPromnSOSDetnItmIssuesQuery
Master detail screen: Details for process step Allocation Table Generation selected in the master list of processing steps with issue	RetailPromnAllocTableItmIssuesQuery
Master detail screen: Details for process step Blocked selected in the master list of processing steps with issue	RetailPromnBlockedItmIssuesQuery
Master detail screen: Details for process step Vendor PO creation selected in the master list of processing steps with issue	RetailPromnAllocPurOrdItmIssQuery
Master detail screen: Details for process step Stock Transport Order creation selected in the master list of processing steps with issue	RetailPromnAllocStkTransptItmIssQuery
Master detail screen: Details for process step Open Goods Receipt Vendor PO selected in the master list of processing steps with issue	RetailPromnGdsRcptFromVendorItmIssQuery
Master detail screen: Details for process step Open Goods Receipt Stock Transport Order selected in the master list of processing steps with issue	RetailPromnGdsRcptFromDCItmIssQuery
Item detail screen: Item Details for process step Open Goods Receipt Vendor PO selected in the master detail screen.	RetailPromnGdsRcptFromVendorSchedLineIssQuery
Item detail screen: Item Details for process step Open Goods Receipt Stock Transport Order selected in the master detail screen.	RetailPromnGdsRcptFromDCSchedLineIssQuery

Query View Description

Overview Query for Preparation Issues in Promotions (Cross-Promotion)

(RetailPromnPrepIssuesTileQuery in package sap.hba.r.rtl.v)

This central query view is mainly based on reuse views RetailPromnAllocationOrderIssues and RetailPromnGoodsReceiptIssues (all in package sap.hba.rtl of product SAP HANA Live for Retail).

It provides information about promotion issues across multiple promotions, leaving out attributes on promotion level and language-dependent names. It provides filter capabilities on input parameters for issue urgency (due, overdue, or blocking issues), error category (issues with or without errors), and process step (such as Purchase Price Activation) - see also the separate section "Filters" in this documentation.

This query view is designed for consumption by the following KPI tiles, which all use the same query view, but with different input parameters and/or filters, to receive the specific information shown on the KPI tile (see also the configuration settings for the tiles):

Promotions Containing Issues: In All Process Steps (KPI Tile), see also Configuration Settings: Promotions Containing Issues
Overdue Issues in Promotions: In All Process Steps (KPI Tile), see also Configuration Settings: Overdue Issues in Promotions - In All Process Steps
Overdue Issues in Promotions: Logistics Manager View (KPI Tile), see also Configuration Settings: Overdue Issues in Promotions - Logistics Manager View
Total Issues in Promotions: In All Process Steps (KPI Tile), see also Configuration Settings: Total Issues in Promotions
Overdue Issues in Promotions - Per Area of Responsibility, see also Configuration Settings: Overdue Issues in Promotions - Per Area of Responsibility

Input parameters (mandatory – the concrete values for the input parameters are defined in the tile configuration):

SAP Client: Client for SAP ERP system.
Evaluation Time Frame in Days from Today: Only Promotions where the start of promotion sales is between the current day and the current day + Evaluation Time Frame (in days) are checked for issues.
Currency for Result Display: Currency in which the currency amounts should be displayed. Ensure that the exchange rates for this currency exist in your SAP ERP system.
Currency Exchange Rate Type: Exchange rate type that is used to convert the currency into the display currency. The current date is used as the date for the currency conversion.
Retail Promotion Process Step: Process steps which are to be evaluated regarding issues. A concatenation of one or more of the following values is possible:
- Ppa: Purchase Price Activation

- Spa: Sales Price Activation

- Dca:DC Price Activation

- Lia: Listing

- Ala: Allocation Table Generation

- Blo: Blockings

- Sos: Source of Supply Determination

- Epo: Vendor PO creation

- Ipo: Stock Transport Order creation

- Edo: Open Goods Receipt Vendor PO

- Ido: Open Goods Receipt Stock Transport Order
Urgency of a promotion process step, only one single value is possible:
A stands for all issues irrespective of urgency

O stands for overdue issues only

D stands for due

B stands for blocked
Error category of a promotion process step, only one single value is possible:
A stands for all issues

E stands for issues with ERP processing errors only

N stands for issues without ERP processing errors

Overview Query for Preparation Issues in Promotions

(RetailPromnPrepIssuesQuery in package sap.hba.apps.rtl.s.v)

This central query view is mainly based on reuse views RetailPromnPreparationIssues, RetailPromnAllocationOrderIssues and RetailPromnGoodsReceiptIssues (all in package sap.hba.rtl of product SAP HANA Live for Retail).

It basically provides the same information as the underlying reuse view, mainly the number of overdue issues, due issues, errors and blocking issues, enriched with language dependent names for some of the fields.

It also provides filter capabilities on input parameters for issue urgency (due, overdue or blocking issues), error category (issues with or without errors) and process step (such as Purchase Price Activation) - see the separate section "Filters" in this documentation. This query view is designed for consumption by the overview screen of SAP Smart Business for Retail Promotion Execution.

This query is called twice from the overview screen, with different aggregation and filters:

For the information in the individual lines, the query is called to provide one entry per promotion with issues. All selected filters, including the fast filter Urgency, are considered
For the aggregated issue sum per urgency category (overdue/due/blocking) that is shown with the appropriate fast filter button, the query is called to provide an aggregated result per urgency category across promotions with issues. All selected filters except the fast filter Urgency itself are considered.

Input parameters (mandatory - the concrete values are filled according to the selected filter values in the appropriate UI filter on the overview screen, or they are inherited from the tile configuration):

SAP Client: see description at tile query
Evaluation Time Frame in Days from Today: see description at tile query
Currency for Result Display: see description at tile query
Currency Exchange Rate Type: see description at tile query
Retail Promotion Process Steps: see description at tile query
Urgency of a promotion process step: see description at tile query
Error category of a promotion process step: see description at tile query

Overview Query for Preparation Issues in a Promotion per Process Step

(RetailPromnProcessStepsQuery in package sap.hba.apps.rtl.s.v)

This central query view is mainly based on reuse views RetailPromnPreparationIssues, RetailPromnAllocationOrderIssues, RetailPromnGdsRcptFromVendorIssues, and RetailPromnGdsRcptFromDCIssues (all in package sap.hba.rtl of product SAP HANA Live for Retail).

It basically provides the same information as the underlying reuse view, enriched with language dependent names for some of the fields and for one promotion only, which is provided as an input parameter. It returns one entry per process step that contains issues (due, overdue, or blocking issues, with and without errors). It also provides filter capabilities on input parameters for issue urgency (due, overdue or blocking issues), error category (issues with or without errors) and process step (such as Purchase Price Activation).

This query view is designed for consumption by the master detail screen of SAP Smart Business for Retail Promotion Execution. Each process step with issues that is shown in the master list for a specific promotion refers to one entry provided by the query. In addition, the header area of the detail screen for each processing step of a promotion is filled with information from this query. Input parameters (mandatory - the concrete values are filled according to the selected filter values in the appropriate UI filter on the overview screen, or they are inherited from the tile configuration):

SAP Client: see description at tile query
Retail Promotion: Retail promotion (as selected in the lines of the overview screen)
Retail Promotion Process Steps: see description at tile query
Urgency of a promotion process step: see description at tile query
Error category of a promotion process step: see description at tile query

Queries: Issues in Promotions on Item Level for a Particular Process Step

These query views are based on reuse views RetailPromnPreparationItemIssues, RetailPromnAllocPurOrdItemIssues, RetailPromnAllocStkTransptOrdItemIssues, RetailPromnGdsRcptFromVendorItemIssues, RetailPromnGdsRcptFromDCItemIssues (all in package sap.hba.rtl of product SAP HANA Live for Retail).

Filters are passed over from the UI to the detailed screens and are applied there, as well.

These query views are designed for consumption by the “Master detail screen - Details”. For each process step, there is a specific query providing exactly the details needed for this process step. This information is shown in the “Details” area when the respective process step is selected in the master list of processing steps with issue.

Detailed query for allocation table generation issues for promotion items (RetailPromnAllocTableItmIssuesQuery in package sap.hba.apps.rtl.s.v)
Detailed query for DC price activation issues for promotion items (RetailPromnDCPriceItmIssuesQuery in package sap.hba.apps.rtl.s.v)
Detailed query for listing generation issues for promotion items (RetailPromnListingItmIssuesQuery in package sap.hba.apps.rtl.s.v)
Detailed query for purchase price activation issues for promotion items (RetailPromnPurPriceItmIssuesQuery in package sap.hba.apps.rtl.s.v)
Detailed query for sales price activation issues for promotion items (RetailPromnSlsPriceItmIssuesQuery in package sap.hba.apps.rtl.s.v)
Detailed query for source of supply determination issues for promotion items (RetailPromnSOSDetnItmIssuesQuery in package sap.hba.apps.rtl.s.v)
Detailed query for blocking issues in promotion items (RetailPromnBlockedItmIssuesQuery in package sap.hba.apps.rtl.s.v)
Detailed query for Vendor PO creation issues for promotion items (RetailPromnAllocPurOrdItmIssQuery in package sap.hba.apps.rtl.s.v)
Detailed query for Stock Transport Order creation issues for promotion items (RetailPromnStkTransptItmIssQuery in package sap.hba.apps.rtl.s.v)
Detailed query for Open Goods Receipt Vendor PO issues for promotion items (RetailPromnGdsRcptFromVendorItmIssQuery in package sap.hba.apps.rtl.s.v)
Detailed query for Open Goods Receipt Stock Transport Order issues for promotion items (RetailPromnGdsRcptFromDCItmIssQuery in package sap.hba.apps.rtl.s.v)

Queríes: Detailed Information about the Database Entries Contributing to the Issues of one Particular Process Step Item

For the two process steps Open Goods Receipt Vendor PO and Open Goods Receipt Stock Transport Order two query views provide detail information of the specific database entries which contribute to the issue of one particular process step item. These query views are based on reuse views RetailPromnGdsRcptFromVendorSchedLineIss, and RetailPromnGdsRcptFromDCSchedLineIss (all in package sap.hba.rtl of product SAP HANA Live for Retail).

Filters are passed over from the UI to the item detailed screens.

Detailed schedule line item query for Open Goods Receipt Vendor PO issues (RetailPromnGdsRcptFromVendorSchedLineIssQuery in package sap.hba.apps.rtl.s.v)
Detailed schedule line item query for Open Goods Receipt Stock Transport Order issues (RetailPromnGdsRcptFromDCSchedLineIssQuery in package sap.hba.apps.rtl.s.v)

Filters

The input parameters of the query views have a filtering effect on the result set in a way that the result set is reduced (meaning less promotions are selected), but also the calculation of some measures related to promotion issues is done differently based on certain input parameters which have an effect on the determination of issues WITHIN a promotion (see details below).

Example Example

The process step Purchase Price Activation is overdue with errors, the process step Allocation Table Generation is overdue, and the process steps Sales Price Activation is due. At first of all there is no filtering.

Then, the filter Retail Promotion Process Steps = PpaSpa is applied (meaning that only the process steps Purchase Price Activation and Sales Price Activation should be considered). In addition, the Urgency filter is set to ‘O’ (meaning that only overdue issues should be shown). This results in the following values for the key figures.

Field	Value Without Filtering	Value when Filtering on Process Steps = PpaSpa	Value when Additionally Filtering on Urgency = O
NmbrOfDueIssInPromn	1	1	0
NmbrOfDueErrorsInProm	0	0	0
NmbrOfOverdueIssInPromn	2	1	1
NmbrOfOverdueErrorsInPromn	1	1	1
TotalNmbrOfIssuesInProm	3	2	1

If no issue is left in a promotion after applying this filtering logic, the respective promotion is not contained in the result set any more.

End of the example.

This is the list of input parameters with filtering effect also on the calculation of key figures. They can be freely combined:

Retail Promotion Process Steps: see description at tile query
Urgency of a promotion process step (in the UI shown as fast filter): see description at tile query
Error category of a promotion process step: see description at tile query.

Key figure fields in the query that are calculated based on these filters:

NmbrOfDueIssInPromn No. of Due Iss. in Promo: This is the sum of all “due” indicators of process steps (for example PurPriceActivationIsDue, SlsPriceActivationIsDue, …) in the promotion that correspond to the combination of selected filter criteria: The due indicator of a process step is only considered in the calculation only if:
- The process step must be explicitly contained in the list for filter Retail Promotion Process Steps

- The fast filter Urgency of a promotion process step is set to “D” for due, or to “A” for all

- The filter Error category of a promotion process step is set to “A”, or if it is set to “E” for errors and the error indicator of the process step (for example PurPriceActivationHasError) is set or if it is set to “N” for “No errors” and the error indicator of the process step (for example PurPriceActivationHasError) is not set
NmbrOfOverdueIssInPromn No. of Overdue Iss. in Promo: This is the sum of all “overdue” indicators of process steps (for example PurPriceActivationIsOverdue, SlsPriceActivationIsOverdue, …) in the promotion that correspond to the combination of selected filter criteria. The overdue indicator of a process step is considered in the calculation only if:
- The process step must be explicitly contained in the list for filter Retail Promotion Process Steps

- The fast filter Urgency of a promotion process step is set to “O” for overdue, or to “A” for all

- The filter Error category of a promotion process step is set to “A”, or if it is set to “E” for errors and the error indicator of the process step (for example PurPriceActivationHasError) is set, or if it is set to “N” for “No errors” and the error indicator of the process step (for example PurPriceActivationHasError) is not set
NmbrOfDueErrorsInPromn No. of Due Issues w/ Err.: This is the sum of all “error” indicators of process steps (for example PurPriceActivationHasError, SlsPriceActivationHasError, …) in the promotion that correspond to the combination of selected filter criteria: The error indicator of a process step is considered in the calculation only if:
- The process step must be explicitly contained in the list for filter Retail Promotion Process Steps

- The fast filter Urgency of a promotion process step is set to “D” for due, or to “A” for all and the due indicator of the process step (for example PurPriceActivationIsDue ) is set

- The filter Error category of a promotion process step is set to “A”, or if it is set to “E” for errors
NmbrOfOverdueErrorsInPromn No. of Overdue Issues w/ Err.: follows the same principle
NmbrOfPromnWithBlocks No. of Promos w/ Blocks: This is the number of blocking issues, which has a maximum of 1 within a promotion, as all possible blocks on header and item level are combined into 1 blocking issue per promotion. This is set in a promotion depending on the following filter criteria settings:
- If the list for filter Retail Promotion Process Steps contains “Blo”

- If the fast filter Urgency of a promotion process step is set to “B” for Blocks

- If the filter Error category of a promotion process step is set to “A”. or if it is set to “N” for “No errors”
TotalNmbrOfIssuesInPromn Total No. of Iss. in Promo: This is the sum of the filtered number of overdue issues, due issues, and blocks of a promotion.
EarliestOverdueDate: The earliest overdue date is the earliest of all overdue dates for individual process steps that correspond to the filter criteria. Only process steps with issues contribute to the calculation of the earliest overdue date. It is freshly determined in the query view where the input parameters are available.

This logic needs to be considered when new process steps should be added to the queries. For more information, see Extensibility: SAP Smart Business for Retail Promotion Execution.