Custom Field: expiration date

​@MHRDonato your query is incorrect as it should only return one value (now it returns two). Besides that its the result of the query that gets shown into the custom field so no need for the AS clause. 

So I think this should be more correct 

SELECT TRUNC(a.expiration_date) - TRUNC(SYSDATE)

FROM ifsapp.inventory_part_in_stock a

WHERE a.LOT_BATCH_NO = :LOT_BATCH_NO AND a.expiration_date IS NOT NULL

no ; is needed either.

Good luck!

​@kvbe , thank you for your response.

The new SQL Statement worked, now it does validate the SQL, however It show’s a new message, I tried to publish aiming to see the possible error, and it shows the following error.

I saw something similar at a customer, and I recall the solution required a persistent custom field and a scheduled database task to run once daily to calculate the value and insert/update it in this field.

Hi,
just want to thank you for a new idea!
 

FACT_InventoryPartInStock
We take that from a Delta-Table where every night the plus/minus for every part will go into.
 

In the MS SQL DWH we take a snapshot every night.

Hope that brings new ideas to every reader.
All the best.

Michael
 

​@MHRDonato could you show the Implementation Type page of the custom field definition where you have the Arguments and Select Statement?

​@matt.watters yes, here it is.

Also, do you know if the Method Signature below is the information that I have to input at Server Method (PL/SQL function) while creating a custom event?