O2C Process Data Model Setup

Thank you for your question and apologies for the late reply.

Different options exist to connect the Sales Order Item Document (VBAP) with the Delivery Document (LIPS) and the Billing Document (VBRP), please find the information below, alongside the advantages and disadvantages for each option.

In general, the Sales Document Flow is recorded in table VBFA, which stores the combination of preceding and subsequent sales and distribution documents.

In case a Delivery Document (LIPS) or a Billing Document (VBRP) is created from a Sales Document, this connection is stored in the table VBFA. It is possible that one or more Delivery, as well as Billing Documents, are created from a Sales Order. All combinations are stored in the VBFA. Each of the three tables (VBAP, LIPS, VBRP) has a 1-N connection to the Sales Document Flow Table (VBFA).

When deciding on a join between the three tables (VBAP, LIPS, VBRP), one should keep in mind that different options exist, but impact the way how data of those tables can be used within analyses. 

In the following, different options with their advantages and disadvantages are described:

1. Option: VBAP and LIPS joined to VBFA and VBRP joined to LIPS

Advantages:

• All Delivery Documents related to a Sales Order are available
• LIPS and VBRP are directly connected, which gives the opportunity to analyze invoices based on deliveries as well as sales orders (relevant for 3-way-match)

Disadvantages:

• Only Invoices that are connected to a Delivery Document can be analyzed, as table VBRP is left joined to LIPS. Not all invoices related to the Sales document are available.
• The transformation script joins VBRP to VBFA, when creating invoice activities. For consistency reasons, the data model should be equally constructed.

2. Option: VBAP and LIPS directly joined to VBFA by adding columns VBELV and POSNV to the DM tables/views LIPS and VBRP

Advantages:

• All Delivery Documents related to a Sales Order are available
• All Invoices related to a Sales Order are available
• The transformation script joins VBRP to VBFA, when creating invoice activities. The data model is consistent with the script.
• Aggregation functions are possible in PQL as VBAP is a parent table of LIPS and VBRP

Disadvantages:

• Delivery Documents and Billing Documents can't be directly related, only to the Sales Order
• The views of LIPS and VBRP are modified and can include duplicate rows, if e.g. a delivery document relates to two sales documents. If another view needs to be added to the data model connecting to LIPS or VBRP N-M relationships can results that are not supported
• The joins in the data model between VBAP - LIPS and VBAP - VBRP are not straightforward, as the added columns from VBFA need to be used

3. Option: LIPS and VBRP directly joined to VBAP

ATTENTION:

When using this option, make sure that the fields KDAUF, KDPOS in table LIPS and AUBEL, AUPOS in table VBRP are filled. This might require an SAP system setup or manual insertion of the sales document numbers into the LIPS and VBRP table.

Advantages:

• Aggregation functions are possible in PQL as VBAP is a parent table of LIPS and VBRP (direct join)
• Smaller data model, as we do not load VBFA

Disadvantages:

• The transformation script joins VBRP to VBFA, when creating invoice activities. For consistency reasons, the data model should be equally constructed.
• Some delivery documents or invoice documents might be missing in case the fields with the sales document numbers are not filled consistently (manual insertion errors might be possible or the system needs to be set up accordingly)

4. Option: VBAP and LIPS and VBRP joined to VBFA (Applied in the standard O2C process connector)

ATTENTION:

It is possible that a 1-1 relation between VBFA-LIPS / VBFA-VBRP exists instead of N-1 if only one delivery/invoice document is generated from every sales order. In this special case, it is important to choose LIPS / VBRP as the source table and VBFA as the target table for the joins in the data model to define the correct parent table, as the parent needs to be defined clearly. If VBFA is the parent table, an N-M relationship between VBRP and LIPS results in the data model (even though in reality N and M both equal 1).

Advantages:

• All Delivery Documents related to a Sales Order are available
• All Invoices related to a Sales Order are available
• The transformation script joins VBRP to VBFA when creating invoice activities. The data model is consistent with the script.

Disadvantages:

• Delivery Documents and Billing Documents can't be directly related, only to the Sales Order
• No Pull-up aggregation functions (e.g. PU_SUM()) are possible between VBAP - LIPS / VBAP - VBRP / LIPS - VBRP, as all of them have a 1-N connection to the Sales Document Flow Table (VBFA). This leads to an N-M connection between VBAP - LIPS and between VBAP - VBRP

Current Solution:

Option 4 is implemented in the Standard SAP ECC O2C Process connector, as the aggregation problem can be partly solved by adding aggregated values of the relevant columns of LIPS and VBRP to the VBAP view in the transformation script.

In SQL, aggregations using the joins as shown in the 3rd version can be performed, and e.g. the sum of all delivery quantities related to a sales order item is added in an extra column to the VBAP view.

Those aggregated values can then be used in the analysis and further aggregations on SO item level are possible.

Thanks to these insights, very helpful. Indeed results of the analyses highly depend on the data model. How do we apply this in practice?

• Have 1,2,3 or 4 options implemented, understanding differences?
• Choose one solution of many?
• Something else?