BW RTR Credit BW_Blocked orders Workbook Description

Credit BW_Blocked orders Workbook Description

Context

We have to provide some statistics in BW about the blocked orders for credit reason.

The indicators will be :

• % of blocked orders for credit reasons

• Total Number of orders blocked during the period divided by the total number of orders 

• Total number of blocked orders for limit reason / total number of blocked orders

• Total number of blocked orders for overdue reason / total number of blocked orders

• Total number of blocked orders for old financial information (expired validity date)

• Total number of blocked orders for mix reason / total number of blocked orders

• Average duration to release a blocked order by blocking reason

Average time in hours to release an order blocked by blocking reason

• Average nbr of orders released in a day

Nbr of orders in average released in a day

• Orders reblocked

Nbr and % of reblocked orders compared to total of orders blocked 

Source of the data :

The VBUK-VBUP tables have the last status of credit blocks in the following fields :

What already exists on the system :

In PF1, some years ago, an specific extractor has been developped for that purpose.

Prg : ZZV_KPI_CREDITBLOCK_ORDERS

This program fills the table zzv_kpi_creditbl (cfr annexe).

The idea is to read the change log tables (CDHDR and CDPOS) to be able to see when an order has been blocked or deblocked during his live.

Because the idea is the same, we will start from that program, and modify the way to prepare the data.

For our needs, some parts should be adapted :

• The date of the blocked order should be a timestamp to be able to extract the average delay to release a blocked order.

• The data were extracted on monthly base, we should be able to extract by week. The jobs takes around 17 hours (a lot of data were read by the program), we would like to work in delta mode to be able to limit the loading time. The old program prepared the data by period, we would like to be able to work with all the past data (it will be possible to slide on every periods).

• Why not improve the presentation of the data and work with blocking segments instead of having one line for each changes.

Several remarks :

• The scope is limited to the orders that :

• are not in a state “cancelled”

• are elligible for credit management (AUART in TVAK-KLIMP = “D”) (cfr infra)

• If an order is created with a blocking code, we will not be able to find the entry in the change log tables. Then the extractor should start from the current situation, and then deduce the starting state of the order.

• If an order has no item, it’s also rejected.

• The active period of an order is not easy to provide : we will use another blocking code (technical) to be able to have this information. This segment will be filled by reading the items of the order : if all items are delivered, the order is considered as inactive.

The new extraction structure (ZZV_KPI_CREDBL_2) :

We will keep all the blocking  segments presented by starting and ending timestamp to be able to present the different credit blocks.

The extraction program (ZZV_KPI_CREDITBLOCK_ORDERS2) :

• Selection :

The first parameter of the program permit to limit the scope of the data loading. It will permit to extract the orders that must be considered in the current extraction.

3 ways will be used to select the orders :

• An order created during this period

• An item of an order has been created during this period

• A change of credit block status has occured during this period

The second parameter will permit to limit the scope to one sales document to be able to make tests if needed.

With the list of orders, we will make the following operations :

1. We will see if a previous data load has already occured for these orders (based on the LASTCHANGED timestamp).

1. If it’s the case, we will continue the previous process without having to go throw the complete live the order.

2. If there is no entry, we begin the data load from the begining.

2. The flow to extract the orders is :

1. Get the last status of the order (VBUK)

2. Then select on the change tables the changes of the credit block status (from ‘B’ or to ‘B’) to fill the segments (CDHDR-CDPOS)

3. Complete the uncomplete segments if needed from the creation time of the order (to manage the case when an order has been created with a credit block not present in the change log tables)

The data load can be loaded by period chosen by the users : for example, it’s possible to split the process by week to reduce the loading time.

Output of the program

The program will load the intermediate table ZZV_KPI_CREDBL_2. The data will be presented by segments with a starting/ending dates and time.

Why to work by segment ? 

• It permits to avoid to duplicate records. If we take a snapshot each month, we will have several time the same order with all the status attached. By segment, the program will automatically fill the old segments if needed without having to repeat the old status.

• It will help us to perform the delay calculation without having to fix the number of hours, the calculation can be done at the query level.

Different cases :

• Case 1:

The segment starts before the current period, and finish during the month.

The segment will count 2 times : for the month M-1, and for the month M.

• Case 2:

The segment starts and finish during the period.

The segment will count 1 time : during the period.

• Case 3:

The segment start during the period and finish after the end of the period.

The segment will count several times : during the period M, and M+... until the segment will be ended.

• Case 4:

The segment start before the period, and end after the end of the period.

The segment will count several times : M-..., during the period, and M+...

Only the orders released during the period will be counted to provide the average release time of the credit check. 

How to know that an order is active or not ?

To check if an order is active or not, there is a status in the table VBUK, but for a reason that I ignore, this field is changed without having a entry in the change log (probably done in an user-exit).

Then the only way to process is to go throw the items, and consider an order with all his items delivered as closed.

To complete this task, we have created a new technical blocking type “CREAT” to store the status active of an order with a start and end time.

The attribution of the GBU

The GBU is attributed in PF1 and RHO by the business area, in WP1, the GBU is attributed by the division and the distribution channel. For the second case, the division is present at the header and item level. The logic is to read first the division at the header level, then replace it by the value found at the item level.

Sales orders elligible for credit management

The sales orders that will be extracted by the program must have a sales document type that are activated for credit managment check (TVAK-KLIMP = ‘D’).

The sales document types are not harmonized, you will find a list by system in the Annexes.

Extra information about particular blocking codes

• CREAT : Life of the order (technical field)

This blocking type is a technical blocking type to be able to recognize the active orders during a period.

• CMGST : Overall status of credit checks

Main status of the order. This order should reflect the status of the sub-blocking types.

In the ERP, the overall status is sometimes close, but not the sub-blocking types. Then, the extractor automatically close the sub-status if the overall status has been closed to provide correct data in the report.

• CMPSL : Status of credit check of reserves 4

Status of credit check of reserves 4 - I do not know this field and it seems there are no programs/FM using or populating it. I checked VBUK and there are no SO with it populated, so I would say, not used in SOLVAY.

• CMPS0 : Tolerance (Status of credit check for customer reserve 1)

Tolerance (Status of credit check for customer reserve 1) - Used in SOLVAY - this blocking is activated for risk categories that have a bonus of 5% or 10% of its credit limit (please see risk categories info on page 12 - Dynamic Check table, Column Tolerance). But we set a boundary of 100K€ (Risk categories 3X) or 250K€ (Risk categories 1X and 2X), depending on risk category, for the maximum bonus.

• CMPS1 : Advance payment (Status of credit check for reserve 2)

Advance payment (Status of credit check for reserve 2) - Used in SOLVAY - this blocking is activated when you have credit status 003 (Doubtful Customer), 004 (Letter of Credit) or 005 (Payment in Advance) in FD33 transaction.

• CMPS2 : Status of credit check for customer reserve 3

Not used for the moment.

In the report

We have to present the following indicators with the corresponding  technical field :

How to obtain the different  indicators :

• % of blocked orders for credit reasons

• Total Number of orders blocked during the period divided by the total number of orders 

By order, check if a blocking segment exists, if yes count it. At the end divide the total by the total of active orders.

• Total number of blocked orders for limit reason / total number of blocked orders

By order, check if a blocking segment exists (CMPSB), if yes count i. At the end divide the total by the total of active orders.

• Total number of blocked orders for overdue reason / total number of blocked orders

By order, check if a blocking segment exists (CMPSF), if yes count it. At the end divide the total by the total of active orders.

• Total number of blocked orders for old financial information (expired validity date)

By order, check if a blocking segment exists (CMPSE), if yes count it. At the end divide the total by the total of active orders.

• Total number of blocked orders for mix reason / total number of blocked orders

Same as the first indicator.

• Average duration to release a blocked order by blocking reason

Average time in hours to release an order blocked by blocking reason

For each order and segment, calculate the average time (end time – start time)

• Average nbr of orders released in a day

Nbr of orders in average released in a day

Count the number of orders with the rule : end time – start time < (3600*24)

• Orders reblocked

Nbr and % of reblocked orders compared to total of orders blocked 

Based on the overall status of the order (CMGST)

The queries

2 queries have been developped for this subject :

• BW_QRY_MVFIAR03_0001 : Detailled query

• BW_QRY_MVFIAR03_0002 : Evolution

A workbook has been created to present the both queries.

Selection screen for both reports :

Key date for blocked orders : the period is deduced from the day indicated in this field.

For example, if you indicate the 01.12.2014, the period deduced will be 01.12.2014 to 31.12.2014.

The blocking types in the queries have sometimes other texts as in the ERP :

BW_QRY_MVFIAR03_0001 : Detailled query

BW_QRY_MVFIAR03_0002 : Evolution

This report presents data by period calculated from the calendar day chosen in the selection screen.

The blocking types are represented by columns instead of lines in the previous report.

Annexes

• Sales document types

PF1 :

WP1 :

• Structure of the table zzv_kpi_creditbl (old structure)