Imagine the credit status of one specific order been changed from status ‘A’ to status ‘B’ a few days back, then finally status ‘C’ recently. Now user interest in how long this order had been held by specific status, for example, status ‘B’. That summary could help to monitor the reason why order processing had been holding and which procedure we could do something to improve efficiency accordingly.

How to achieve that? Change history table CDHDR and item table CDPOS immediately jump out. Yes, change history can be fetched easily by monitoring specific table fields at CDHDR/CDPOS (for credit status which is VBUK-CMGST). But calculate the holding period of status ‘B’ is far more complex than I assumed. No doubt the general formula to calculate the holding period is:

Holding period of status ‘B’ = <Ending date > minus <Starting date >

1.Calculation of holding period at the header level

Then let’s think about common scenarios that could be for the status changes at the header level:

Scenario 1: Order status has been changed from ‘A’ to ‘B’ then ‘C’

• The starting date of status ‘B’: the date when status ‘A’ been changed into ‘B’
• The ending date of status ‘B’: the date when status ‘B’ been changed into ‘C’

Scenario 2: Order status has been changed from ‘A’ to ‘B’ till now

• The starting date of status ‘B’: the date when status ‘A’ been changed into ‘B’
• The ending date of status ‘B’: today

Scenario 3: Order status has been created with status ‘B’ then changed to ‘C’

• The starting date of status ‘B’: order creation date
• The ending date of status ‘B’: the date when status ‘B’ been changed into ‘C’

Scenario 4: Order status has been created with status ‘B’ till now

• The starting date of status ‘B’: order creation date
• The ending date of status ‘B’: today

The calculation of the holding period at the header level need to fetch all related CDPOS entry then loops each item of CDPOS:

1. Check whether the order has been created with specific status which impacts the starting date by comparing the current CDPOS entry with the first entry of the sorted CDPOS table. If yes, then use logic with the order creation date as the starting date.
2. Check whether the order has been stuck with specific status till now which impacts the ending date by comparing the current CDPOS entry with the last entry of the sorted CDPOS table. If yes, then using logic with the current date as the ending date.
3. If the check failed at point1&2, then fetch the current CDPOS’s previous entry by using CDPOS-NEW_VALUE equal to current CDPOS-OLD_VALUE; meanwhile, CDPOS-CHANGENR should be the largest except current entry’s CHANGENR.

Of course, one status could repeatedly occur at different periods like from ‘A’ to ‘B’, then ‘C’ following by ‘B’ again. Simply do the summary by status.

Caution: larger CHANGENR doesn’t always mean that it happens later. We did find rare cases that smaller CHANGENR been updated newer than larger CHANGENR with few seconds interval. So CDHDR-UDATE could be more suitable when fetching the previous change logs.

2.Calculation of holding period at the item level

Very similar compared with header level for the above four common scenarios but becomes a little bit tricky for special cases.

Special Scenario 1: Order item been created with ‘B’ but after order been created.

For example, one order was created with 2 items only. After one day, it created 3rd item with the status ‘B’.

• The starting date of status ‘B’: not order creation date, but item creation date!

Then need to fetch this date from field CDPOS-CHNGIND = ‘I’ (noted that FNAME is ‘KEY’!):

Special Scenario 2: Order item been created with ‘B’ but been deleted later~!

For example, one order was created with 2 items only. After one day, 2nd item with the status ‘B’ has been deleted. But still, we concern about the holding period for deleted items.

• The starting date of status ‘B’: not the item creation date, but the item creation date before it’s been deleted!

Then need to fetch this date from field CDPOS-CHNGIND = ‘D'(noted that FNAME is ‘KEY’!):

Special Scenario 3: Order item been created with ‘B’ but been deleted then been re-create! !~

• The starting date of status ‘B’: correct one among multiply item creation dates!~

How about users create and delete one item multiply times? I’m going to sleep, haha. Don’t worry we have CDPOS-CHANGENR and CDHDR-UTIME, just fetch the nearest entry will do with correct sorting.

3.Calculation of holding period at the schedule item level

What?! Besides the above four common scenarios at the header level and three special scenarios at the item level, need to consider the below rare scenarios at the schedule item level.

Rare Scenarios: Schedule item been created with status ‘B’ after item been created and schedule line been deleted and re-created; then item been deleted and recreated with new the same schedule item line with another status, etc…

The starting date of status ‘B’ could be:

• order creation date?
• item creation date?
• schedule line item creation date?
• one of multiply item creation dates?
• one of the schedule line item creation dates?
• last change date of previous change logs?

Till now, no better methods besides more loops plus more IF/ELSE conditions from my point of view. Please correct me if I’m overthinking or any better approach.