Effective Open Orders design in SAP BI / BW

Open Orders are an integral part of a company whose business entails fulfilling orders, whether that encompasses an ecommerce site or a Manufacturing unit. The faster the order shipped the quicker revenue starts to trickle in.

“Open Orders” or “On Orders” are terms used by business, typically “Open Orders” are referred to the orders a business entity needs to deliver to it’s customers.

 

Consider the following Requirement as for there are various rules that determine what an “Open Order” is based on how a company determines it. The following is how I designed it based on the following requirement.

Provide a Bex report that shows all open orders across all Plants in a descending order with Total Open Quantities and  Total dollar value with “Days Past Due” highlighted in Red if the Goods Issue Date is past “Today”

As long as an order is NOT PGI’s (Post goods issued) in ECC it should be considered as an “Open Order”

Essentially we are looking to bring in the values that you view in Transaction code VA33 or VA03 of ECC into BI. For this I used SAP delivered standard datasource 2LIS_11_V_SCL.

“The structure Extraction SD Sales (2LIS_11_V_SCL.) Document Schedule Line Allocation is used to extract open quantities to be delivered from SD order schedule lines to a BI system.”[1]

For this Dataflow I created a DSO before loading to a cube , the key fields that were used for the Data Store Object were.

Sales Document,(0DOC_NUMBER), Sales Document item(0S_ORD_ITEM), Schedule Line Number(0SCHED_LINE),  Material(0MATERIAL)

This was working out well in testing and had good results in matching values from ecc VA33, until some records were update via EDI. A few customers who have scheduled orders send their requests via an EDI file and, it is possible to update already existing orders. When an order is created via EDI  and later if the same order was updated and if there existed a  partial delivery between these two updates the record would show up in the DSO, BUT any subsequent new deliveries after this would overwrite the partial order that was previously sent. To overcome this scenario we needed the Goods Issue Date to be included in the data store objects key field list. By doing this, we are guarded from being overwritten by one or more partial deliveries.

 

1)      For Example initial order# 1001 was for a quantity of 10,000

2)      Partial order fulfillment of 3000, remaining 7000

3)      Data loaded to DSO and now DSO record for quantity 3000 appears.

4)      Order Updated to 20000.

5)      Delivery sent for 20000

If GI Date is not part of the DSO Key fields the record from step 3 is overwritten.

Once an order or a partial order is sent for delivery it is captured by BW datasource 2LIS_12_VCITM

Key fields used for Delivery DSO – 0DOC_NUMBER, 0S_ORD_ITEM, 0DELIV_NUMB

The value for open orders needed for the report comes from infoobject 0OPN_VAL_SC in datasource 2LIS_11_V_SCL, but 2LIS_12_VCITM does not have the value, hence it is to be calculated using a field routine.

In Bex to provide a clear visual of current open orders create  a column “Past Due in Days” which  subtracts Goods Issue Date minus current day. (Current day is delivered by SAP ,[0F_ADAY])

2lis

Another important step is to map the  ROCANCEL Indicator to 0RECORDMODE in “Technical Group” within “Rule Group” with the transformation going to the DSO from datasource. But for allocation datasources such as 2LIS_11_V_SCL, an indicator cannot be sent from ECC, if a record is deleted an after image with inverted +/- sign is sent over instead of an “R” indicator and this changes the value of the key figure rather than deletion of the record.

http://help.sap.com/saphelp_nw70/helpdata/en/41/938d9c476c4ed6a9eed87b59ae19ac/content.htm