?
Solved

Oracle PL/SQL - Working with Ranges

Posted on 2011-10-25
16
Medium Priority
?
390 Views
Last Modified: 2012-06-27
Working with Ranges - in three tiers.

Looking for ideas on creating a Function or a Select statement that will translate a Inventory quantity based on the following hierarchy:

1. SKU
2. TYPE
3. GENERAL

For example, if a SKU override does not exist, then look for a match on the item's product Type. If neither of these exist, follow the General rule. In other words, if a match on #1 - stop evaluating #2 and #3.

Using the following table (derrived from a Lookup in EBS 12):

 
MEANING          DESCRIPTION    TAG
-----------      -----------    ---------
SKU123_          <15            .99
TYPNON_SERIAL_1  <=3            1
TYPNON_SERIAL_2  >4 AND <=10    .5
TYPNON_SERIAL_3  >10 AND <=25   .75
TYPNON_SERIAL_4  >25            25
GEN_1            <=4            1
GEN_2            >4 AND <=15    .5
GEN_3            >15 AND <20    .99
GEN_4            >20            20

Open in new window


Say we have item = 321 and the Type is NON_SERIAL with a Quantity of 6 - we would return 3 (.5 x 6).

Say we have item = 123 with a Quantity of 9 - we would return a Quantity of 9 (.99 x 9, rounding up always)

Say we have item = 111 with a Quantity = 25 - we would return a Quantity of 20.

Rules implied:
1. If Tag value < 1 then the number is a multiplier (rounding up).
2. If the Tag value is >=1 (Integer) then the quantity is absolute.

I'm flexible on the cofiguration of the Lookup Table's values so these can be reworked if needed to accomplish the end result. The suffixes (_1, _2) are only added to make Meaning unique (EBS Requirement).

If the Function route, I would look to be passing in 3 vals: (SKU, TYPE, INV)...

Interested in the best route to accomplish the end result. Remember, the data in the EBS form can be changed as long as the outcome is the same.

Thanks!
0
Comment
Question by:globalwm2
  • 9
  • 7
16 Comments
 

Author Comment

by:globalwm2
ID: 37029176
Small Correction:
GEN_3            >15 AND <=20    .99
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37029179
does your flexibility on the lookup table extend to the structure? that is,  can I add new columns?

or only on the data within those 3 columns?


and, can we assume no item will ever fit more than one rule (assuming we stop searching appropriately)
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37029278
I made the assumption that you could not change your structure only the data,  so, to make parsing easier I changed the meaning column a little bit

I named my lookup table "ee_lookup"  but change it to whatever you are using

SKU_123             <15	            0.99
TYP_1_NON_SERIAL    <=3	            1
TYP_2_NON_SERIAL    >4 AND <=10	    0.5
TYP_3_NON_SERIAL    >10 AND <=25    0.75
TYP_4_NON_SERIAL    >25	            25
GEN_1               <=4	            1
GEN_2               >4 AND <=15	    0.5
GEN_3               >15 AND <=20    0.99
GEN_4               >20             20

Open in new window

CREATE OR REPLACE FUNCTION item_lookup(p_sku IN VARCHAR2, p_type IN VARCHAR2, p_quantity IN NUMBER)
    RETURN NUMBER
IS
    v_result NUMBER;
BEGIN
    SELECT CASE WHEN tag >= 1 THEN tag ELSE tag * p_quantity END
      INTO v_result
      FROM (SELECT *
              FROM (SELECT sku,
                           typ,
                           gen,
                           gt,
                           TO_NUMBER(NVL2(gt, REGEXP_SUBSTR(description, '[0-9]+'), NULL)) rmin,
                           lt,
                           TO_NUMBER(NVL2(
                                         lt,
                                         REGEXP_SUBSTR(
                                             description,
                                             '[0-9]+',
                                             1,
                                             NVL2(gt, 2, 1)
                                         ),
                                         NULL
                                     ))
                               rmax,
                           tag
                      FROM (SELECT meaning,
                                   description,
                                   tag,
                                   CASE WHEN meaning LIKE 'SKU%' THEN SUBSTR(meaning, 5) END sku,
                                   CASE WHEN meaning LIKE 'TYP%' THEN SUBSTR(meaning, 7) END typ,
                                   CASE WHEN meaning LIKE 'GEN%' THEN 'GEN' END gen,
                                   REGEXP_SUBSTR(description, '>=?') gt,
                                   REGEXP_SUBSTR(description, '<= ?') lt
                              FROM ee_lookup))
             WHERE (sku = p_sku OR typ = p_type OR gen = 'GEN')
               AND (CASE
                        WHEN gt = '>' AND p_quantity > rmin THEN 1
                        WHEN gt = '>=' AND p_quantity >= rmin THEN 1
                        WHEN gt IS NULL THEN 1
                    END = 1
                AND CASE
                        WHEN lt = '<' AND p_quantity < rmax THEN 1
                        WHEN lt = '<=' AND p_quantity <= rmax THEN 1
                        WHEN lt IS NULL THEN 1
                    END = 1)
            ORDER BY CASE WHEN sku = p_sku THEN 1 WHEN typ = p_type THEN 2 ELSE 3 END) x
     WHERE ROWNUM = 1;

    RETURN v_result;
END;

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 74

Expert Comment

by:sdstuber
ID: 37029289
I also made the assumption that your lookups will be unique, assuming sku/typ/gen order

that is,  nothing will match more than one sku   or more than one type   or more than one gen
0
 

Author Comment

by:globalwm2
ID: 37032085
sdstuber:

The data is limited to three columns (basically) - I'm flexible with what's in those columns though.
0
 

Author Comment

by:globalwm2
ID: 37032097
sdstuber: I will take a look at what you have provided and get some feedback....
0
 

Author Comment

by:globalwm2
ID: 37032944
I set up the table with the sample values... and updated the inner SQL with actual FROM table and WHERE clause (which runs fine).

Now I'm trying to test with some sample values using the following - but I'm getting a error:

ORA-00933: SQL command not properly ended on Line 47 which is:

ORDER BY CASE WHEN sku = 123 THEN 1 WHEN typ = 'OC_NO_SL'  THEN 2 ELSE 3 END) x

Here is the script with sample values:
 
-- Sample Values Used:
-- p_sku =  123
-- p_type = 'OC_NO_SL'
-- p_quantity = 3
--
SELECT *
              FROM (SELECT sku,
                           typ,
                           gen,
                           gt,
                           TO_NUMBER(NVL2(gt, REGEXP_SUBSTR(description, '[0-9]+'), NULL)) rmin,
                           lt,
                           TO_NUMBER(NVL2(
                                         lt,
                                         REGEXP_SUBSTR(
                                             description,
                                             '[0-9]+',
                                             1,
                                             NVL2(gt, 2, 1)
                                         ),
                                         NULL
                                     ))
                               rmax,
                           tag
                      FROM (SELECT meaning,
                   description,
                   tag,
                   CASE WHEN meaning LIKE 'SKU%' THEN SUBSTR (meaning, 5) END sku,
                   CASE WHEN meaning LIKE 'TYP%' THEN SUBSTR (meaning, 7) END typ,
                   CASE WHEN meaning LIKE 'GEN%' THEN 'GEN' END gen,
                   REGEXP_SUBSTR (description, '>=?') gt,
                   REGEXP_SUBSTR (description, '<= ?') lt
              FROM FND_LOOKUP_VALUES FLV
             WHERE LOOKUP_TYPE = 'OC_BUY_INVENTORY_RULES' AND ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN flv.start_date_active AND NVL (flv.end_date_active, SYSDATE + 1)))
             WHERE (sku = 123 OR typ = 'OC_NO_SL' OR gen = 'GEN')
               AND (CASE
                        WHEN gt = '>' AND 3 > rmin THEN 1
                        WHEN gt = '>=' AND 3 >= rmin THEN 1
                        WHEN gt IS NULL THEN 1
                    END = 1
                AND CASE
                        WHEN lt = '<' AND 3 < rmax THEN 1
                        WHEN lt = '<=' AND 3 <= rmax THEN 1
                        WHEN lt IS NULL THEN 1
                    END = 1
                    )
            ORDER BY CASE WHEN sku = 123 THEN 1 WHEN typ = 'OC_NO_SL'  THEN 2 ELSE 3 END) x
     WHERE ROWNUM = 1

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37033088
you dropped the outer select from my original query

either add

SELECT CASE WHEN tag >= 1 THEN tag ELSE tag * p_quantity END
      FROM (

to the beginning of what you have

or

remove

 ) x
     WHERE ROWNUM = 1


from the end
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37033094
easiest way to test - create the function and test it directly

0
 

Author Comment

by:globalwm2
ID: 37033106
Gotcha - will try and test both ways and come back...
0
 

Author Comment

by:globalwm2
ID: 37034102
Running just the SQL with hard values looks good so far - I just tested with one type (GEN).

When I try to compile the original SQL, I'm seeing this error:

LINE/COL ERROR                                                            
-------- -----------------------------------------------------------------
6/49     PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got NUMB
         ER                                                              
                                                                               
6/5      PL/SQL: SQL Statement ignored                                    


Line #6 is:
    SELECT CASE WHEN tag >= 1 THEN tag ELSE tag * p_quantity END

??
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37034145
I had to guess at your table structure.  Change data types as needed.
0
 

Author Comment

by:globalwm2
ID: 37034156
In the "ee_lookup" table, TAG is a VARCHAR2(150)
0
 

Author Comment

by:globalwm2
ID: 37034171
Got it:

    SELECT CASE WHEN to_number(tag, '999.99') >= 1 THEN to_number(tag, '999.99') ELSE to_number(tag, '999.99') * p_quantity END
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37034209
you could make it even easier...


in the inner-most query

change
 description,
                   tag,
                   CASE WHEN meaning LIKE 'SKU%' THEN SUBSTR (meaning, 5) END sku,

Open in new window


to

 description,
                   to_number(tag) tag,  -- you can format it or not, your choice
                   CASE WHEN meaning LIKE 'SKU%' THEN SUBSTR (meaning, 5) END sku,

Open in new window



then it will propagate up as a numeric type through the rest of the query

0
 

Author Closing Comment

by:globalwm2
ID: 37039264
Awesome - was able to make work for all three scenarios!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question