Is it possible to build a custom field for the Inventory Part In Stock table that shows how many days we have before the expiration?
I’ve been trying to build a read only field, as a select statement, even though I’m able to save the custom field I cannot validate it.
here are my SQL, image of the Custom and Image of error
SELECT a.expiration_date, TRUNC(a.expiration_date) - TRUNC(SYSDATE) AS CF$_DAYS_TO_EXPIRE FROM inventory_part_in_stock a WHERE a.LOT_BATCH_NO = :LOT_BATCH_NO AND a.expiration_date IS NOT NULL;
Page 1 / 1
@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
Hello,
I had my SQL sentence fixed and it worked.
Here is the new SQL, if anyone would like to implement:
SELECT TRUNC(a.expiration_date) - TRUNC(SYSDATE)
FROM inventory_part_in_stock a
WHERE a.LOT_BATCH_NO = :LOT_BATCH_NO AND a.expiration_date IS NOT NULL
I had to do an extra change from @kvbe ‘s SQL
Thank you for the help.
All the best Murilo.
@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?
