asked on

Oracle PL/SQL - Working with Ranges

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!

globalwm2

ASKER

Small Correction:
GEN_3 >15 AND <=20 .99

Sean Stuber

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)

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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

globalwm2

ASKER

sdstuber:

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

globalwm2

ASKER

sdstuber: I will take a look at what you have provided and get some feedback....

globalwm2

ASKER

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

Sean Stuber

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

Sean Stuber

easiest way to test - create the function and test it directly

globalwm2

ASKER

Gotcha - will try and test both ways and come back...

globalwm2

ASKER

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

??

Sean Stuber

I had to guess at your table structure. Change data types as needed.

globalwm2

ASKER

In the "ee_lookup" table, TAG is a VARCHAR2(150)

globalwm2

ASKER

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

Sean Stuber

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

 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

globalwm2

ASKER

Awesome - was able to make work for all three scenarios!