Solved

# oracle function to get percentge of shelf life remaining

Posted on 2016-09-15
45 Views
`````` select
1 -
( (shelf_exp_date-trunc(sysdate))
from built_items where bar_code = 33502238

select shelf_exp_date, trunc(sysdate), shelf_exp_date-trunc(sysdate)
from built_items where bar_code = 33502238;

from dual;
``````

I have included a screen print that shows the shelf life. It may be years, days, weeks etc. So I want one function that can take input of shelf life and convert to days and get the percentage shelf life remaining. Help appreciated.
0
Question by:anumoses

LVL 34

Accepted Solution

johnsone earned 250 total points
ID: 41799774
Given the information you have given us, this would be a function to return the number of days remaining:
``````CREATE OR replace FUNCTION Days_remaining (expire_date DATE)
RETURN NUMBER
AS
BEGIN
RETURN ( expire_date - Trunc(SYSDATE) );
END;

/
``````
You need to figure out what you want to do with fractions of a day (unless the expire date is always midnight as a time).

If you want days remaining and percentage of life left, then you need to supply two of these three pieces of information; date put on the shelf, shelf life (need to know units too), date expires.  I only see one of those pieces of information, unless shelf life is always 5 years.

Also, a function can only return one value, so if you want 2 values, you would have to use either 2 functions or a function (or a procedure) that contains OUT parameters.
0

LVL 6

Author Comment

ID: 41799789
For any given part in the inventory the shelf life of the part is always one. I mean either years, months, weeks or days. So for the above function, will this function return days for any expiration date I give? I mean I need to get expiry date for that part that is in the built items table.
0

LVL 34

Expert Comment

ID: 41799802
Given your sample queries, the expiration date of a part is stored in the SHELF_EXP_DATE, correct?  If you pass that field into the function it will return number of days remaining.  I took your query and literally put it in a function.  If your sample queries are incorrect, then I need more information.

The easiest way to find out if it works, it to try it.  Create the function and the run:

select shelf_exp_date, trunc(sysdate), shelf_exp_date-trunc(sysdate), days_remaining(shelf_exp_date)
from built_items where bar_code = 33502238;
0

LVL 34

Assisted Solution

johnsone earned 250 total points
ID: 41799833
Since you stated that life can be days, weeks, months, or years, this function should be able to handle all those situations.  I tried to test it as much as I could.  It returns values that seem correct and errors when it needs to.
``````CREATE OR replace FUNCTION Pct_remaining (expire_date       DATE,
shelf_life_days   NUMBER DEFAULT NULL,
shelf_life_weeks  NUMBER DEFAULT NULL,
shelf_life_months NUMBER DEFAULT NULL,
shelf_life_years  NUMBER DEFAULT NULL)
RETURN NUMBER
AS
num_null     PLS_INTEGER := 0;
num_not_null PLS_INTEGER := 0;
sdays        PLS_INTEGER;
BEGIN
IF ( shelf_life_days IS NULL ) THEN
num_null := num_null + 1;
END IF;

IF ( shelf_life_weeks IS NULL ) THEN
num_null := num_null + 1;
END IF;

IF ( shelf_life_months IS NULL ) THEN
num_null := num_null + 1;
END IF;

IF ( shelf_life_years IS NULL ) THEN
num_null := num_null + 1;
END IF;

IF ( num_null = 4 ) THEN
Raise_application_error(-20001, 'Must supply one shelf life');
END IF;

IF ( shelf_life_days IS NOT NULL ) THEN
sdays := shelf_life_days;

num_not_null := num_not_null + 1;
END IF;

IF ( shelf_life_weeks IS NOT NULL ) THEN
sdays := shelf_life_weeks * 7;

num_not_null := num_not_null + 1;
END IF;

IF ( shelf_life_months IS NOT NULL ) THEN
sdays := expire_date - Add_months(expire_date, shelf_life_months * -1);

num_not_null := num_not_null + 1;
END IF;

IF ( shelf_life_years IS NOT NULL ) THEN
sdays := expire_date - Add_months(expire_date, shelf_life_years * -12);

num_not_null := num_not_null + 1;
END IF;

IF ( num_not_null != 1 ) THEN
Raise_application_error(-20002, 'Cannot supply more than one shelf life');
END IF;

RETURN ( ( Trunc(expire_date) - Trunc(SYSDATE) ) / sdays );
END;

/
``````
0

LVL 6

Author Comment

ID: 41799837
I understand. Now my other request was to calculate percentage shelf life remaining
0

LVL 6

Author Comment

ID: 41799844
what were your test values used in the function? if you dont mind?
0

LVL 31

Assisted Solution

awking00 earned 250 total points
ID: 41799848
For percentage of remaining shelf life use johnsone's days_remaining divided by (add_months(trunc(sysdate),60) - trunc(sysdate))
0

LVL 4

Expert Comment

ID: 41799856
Hi Anumoses,

I have one question, what is the basis of total shell life

SHELL_EXP_LIFE --> Some date in future
Life Remaining --> Future date - current date

Total Life --> How to determine that, is it always going to be from a fixed date or there is SHELL_START_DATE defined as well ?

Here is an assumption of given scenario

Lets's say ,

shelf life start date is 54 days in past from today  = (sysdate -  54)
shelf life expiration date is 18 months from now i.e. shelf_exp_date = add_months(sysdate,18)

Life remaining =  (add_months(sysdate,18) - sysdate )       -- 546 days
Total life = (add_months(sysdate,18) - (sysdate -  54) )    -- 600 days

% life remaining

SELECT   (add_months(sysdate,18) - sysdate ) * 100
/ (add_months(sysdate,18) - (sysdate -  54) )
FROM DUAL

91% Life Remaining

Please do consider the points mentioned by Johnsone regarding midnight and function
0

LVL 6

Author Comment

ID: 41799857
I think the other function johnsone created gets the percentage if I have understood correctly.
0

LVL 6

Author Comment

ID: 41799862
Abhimanyu Suri

We have a built_items table that has barcodes and part numbers. Each part has a shelf_exp_date

Taking system date I have to get percentage shelf life left. we provide parts to all major airlines.
0

LVL 6

Author Comment

ID: 41799872

Included data as a screen shot for shelf life
0

LVL 34

Expert Comment

ID: 41799886
This is what I used for tests.

select pct_remaining(trunc(sysdate)+4) from dual;
select pct_remaining(trunc(sysdate)+4, 4, 4) from dual;
select pct_remaining(trunc(sysdate)+4, 4) from dual;
select pct_remaining(trunc(sysdate)+3, 4) from dual;
select pct_remaining(trunc(sysdate)+2, 4) from dual;
select pct_remaining(trunc(sysdate)+1, 4) from dual;
select pct_remaining(sysdate, null, 7) from dual;
select pct_remaining(sysdate+3, null, 1) from dual;
select pct_remaining(sysdate+3, null, null, 1) from dual;
select pct_remaining(sysdate+3) from dual;
select pct_remaining(sysdate+3, 1, 1, 1, 1) from dual;
select pct_remaining(sysdate+3, 1, 1, 1) from dual;

That is by no way conclusive and I really just checked to see that it looked right.  It could be totally incorrect.  You should be the one testing and verifying the code.  If it needs to be changed, by all means change it.
0

LVL 6

Author Comment

ID: 41800171
johnsone please let me know if I am testing correctly. I get errors

select fun_percent_remaining(trunc(sysdate)+4) from dual;

ORA-20001: Must supply one shelf life
ORA-06512: at "DSS.FUN_PERCENT_REMAINING", line 29
ORA-06512: at line 1

select fun_percent_remaining(trunc(sysdate)+4, 4, 4) from dual;

ORA-20002: Cannot supply more than one shelf life
ORA-06512: at "DSS.FUN_PERCENT_REMAINING", line 57
ORA-06512: at line 1

I just changed the function name according to naming convention but everything is as you gave me

``````create or replace
FUNCTION fun_percent_remaining (expire_date       DATE,
shelf_life_days   NUMBER DEFAULT NULL,
shelf_life_weeks  NUMBER DEFAULT NULL,
shelf_life_months NUMBER DEFAULT NULL,
shelf_life_years  NUMBER DEFAULT NULL)
RETURN NUMBER
AS
num_null     PLS_INTEGER := 0;
num_not_null PLS_INTEGER := 0;
sdays        PLS_INTEGER;
BEGIN
IF ( shelf_life_days IS NULL ) THEN
num_null := num_null + 1;
END IF;

IF ( shelf_life_weeks IS NULL ) THEN
num_null := num_null + 1;
END IF;

IF ( shelf_life_months IS NULL ) THEN
num_null := num_null + 1;
END IF;

IF ( shelf_life_years IS NULL ) THEN
num_null := num_null + 1;
END IF;

IF ( num_null = 4 ) THEN
Raise_application_error(-20001, 'Must supply one shelf life');
END IF;

IF ( shelf_life_days IS NOT NULL ) THEN
sdays := shelf_life_days;

num_not_null := num_not_null + 1;
END IF;

IF ( shelf_life_weeks IS NOT NULL ) THEN
sdays := shelf_life_weeks * 7;

num_not_null := num_not_null + 1;
END IF;

IF ( shelf_life_months IS NOT NULL ) THEN
sdays := expire_date - Add_months(expire_date, shelf_life_months * -1);

num_not_null := num_not_null + 1;
END IF;

IF ( shelf_life_years IS NOT NULL ) THEN
sdays := expire_date - Add_months(expire_date, shelf_life_years * -12);

num_not_null := num_not_null + 1;
END IF;

IF ( num_not_null != 1 ) THEN
Raise_application_error(-20002, 'Cannot supply more than one shelf life');
END IF;

RETURN ( ( Trunc(expire_date) - Trunc(SYSDATE) ) / sdays );
END;
``````
0

LVL 6

Author Comment

ID: 41800185
select fun_percent_remaining(sysdate+3, null, null, 1) from dual;

This worked fine

0.0967741935483870967741935483870967741935
0

LVL 34

Expert Comment

ID: 41800297
As you stated, the shelf life has units of either days, weeks, months, or years.  Not a combination of them.  So, you can only specify one of them.  If it would be possible to have more than one specified, then change the function.
0

LVL 6

Author Comment

ID: 41804553
Thanks
0

LVL 6

Author Closing Comment

ID: 41804554
Thanks for all the help
0

## Featured Post

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacyâ€¦
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-orâ€¦
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.