Value Accelerator Event - Structure question

[Sanddie]

Dear Community, I am reaching out to you because I am trying to understand how one of the events that I have in a Value Accelerator.

This event is suppose to materialize the Supplier invoice due date. 

Here is the code associated : 

SELECT
c_caseid,
c_time,
c_eventname,
"Number of Invoice Line Items",
"Accounting Document Primary Key"
FROM (

SELECT
CONCAT(bseg_bkpf.mandt, bseg_bkpf.bukrs, bseg_bkpf.gjahr, bseg_bkpf.belnr) AS c_caseid,
CASE -- always adding 23:59:59 as time
WHEN COALESCE(zbd3t, 0) > 0 THEN DATE_ADD('day', CAST(zbd3t AS INT) ,from_unixtime(zfbdt/1000 + 86399))
WHEN COALESCE(zbd3t, 0) = 0 AND COALESCE(zbd2t, 0) > 0 THEN DATE_ADD('day', CAST(zbd2t AS INT) ,from_unixtime(zfbdt/1000 + 86399))
WHEN COALESCE(zbd3t, 0) = 0 AND COALESCE(zbd2t, 0) = 0 AND COALESCE(zbd1t, 0) > 0 THEN DATE_ADD('day', CAST(zbd1t AS INT) ,from_unixtime(zfbdt/1000 + 86399))
ELSE from_unixtime(zfbdt / 1000 + 86399)
END AS c_time,
'Due Date passed' AS c_eventname,
COUNT(bseg_bkpf.buzei) OVER (PARTITION BY bseg_bkpf.belnr) AS "Number of Invoice Line Items",
bseg_bkpf.bkpf_c_key AS "Accounting Document Primary Key"
FROM bseg_bkpf
WHERE zfbdt IS NOT NULL
AND (bseg_bkpf.tcode != 'MR8M' OR bseg_bkpf.tcode IS NULL)
AND bschl IN (
-- Invoices
'31', -- Vendor Invoices
'22', -- Reverse Invoice
'21', -- Credit Memos
'32' -- Reverse Credit Memo
)
)

WHERE c_eventname IS NOT NULL
AND c_caseid IS NOT NULL
AND c_time IS NOT NULL

What I am not sure to understand is that it seems we are adding a timestamp to the due date but I don't get why as if the invoice is due today : then it is easy to determine just using the date if the invoice is overdue or not. 

Any idea ???

Thank you

Sanddie

[fprice]

Hi Sanddie,

The event you have posted is relating to 'Due Date passed'.

The timestamp that is being added leads to the invoice being overdue after baseline's due date at 23:59:59 that day, which would then be 00:00:00 the next day.

This leads to the overdue event being created on the day it is overdue.

There are 24 * 60 * 60 = 86.400 seconds in a day, so 68.399 are being added to do get to 23:59:59.

Hope this helps,
Fabian

[Sanddie]

Hello Fabian thank you for your answer. Which kind of confirm what I understood. What I am not clear of though : is why do we need a timestamp. Indeed if my invoice is due today it will be overdue tomorrow (D+1) : no need to set a 23:59:59 time stamp for that ! Have a nice day. Sanddie

[fprice]

Hi Sanddie,

I think this is basically only a design question on how the accelerators have been set up. AFAIK, the accelerators use this event in certain other calculations in dashboards, e.g. when calculating ageing. So removing it will also most likely impair standard dashboards.

But as you mentioned, there would also be other ways to calculate this.

Best