Using Input Variable Within A Comparison To Display Text String

From: <jsmith75_at_gmail.com>
Date: Mon, 02 Jul 2007 15:44:25 -0000
Message-ID: <1183391065.811778.148710@m36g2000hse.googlegroups.com>

Hello,

I am attempting to determine the correct way to capture an input variable (a quantity entered by the end user), use this variable value to compare to another value retrieved from a view (also numeric), and then return one of three text strings depending on the outcome of the comparison.

My process consists of a view, a procedure (for display the initial HTML to the user), and a package (for completing the data retrieval, comparison, and display of output to user.)

My initial procedure displays four input fields to the user allowing them to complete one of two search options:

part_id + desired_qty - OR - part_description + desired_qty

So I have possibly four variables that can be retrieved from this procedure: part_id, part_description, desired_qty1, desired_qty2.

Here is the context of the view:

CREATE OR REPLACE VIEW CUST_INV_CK_VIEW1 (PART_ID, DESCRIPTION, PRODUCT_CODE, COMMODITY_CODE, UPC, QTY, WAREHOUSE_ID, QTY_IN_CUST_ORDER, QTY_IN_IBT, TOTAL_DEMAND, QTY_AVAIL, FLOOR_QTY_AVAIL, ADJ_QTY_AVAIL, USER_1) AS
select p.id, p.description, p.product_code, p.commodity_code,

        p.user_10, pl.qty, pl.warehouse_id,
        nvl(wq.qty_in_cust_order, 0) "QTY_IN_CUST_ORDER",
        nvl(iq.qty_in_ibt, 0) "QTY_IN_IBT",
        sum(nvl(wq.qty_in_cust_order, 0) + nvl(iq.qty_in_ibt, 0))
"TOTAL_DEMAND",
        nvl((pl.qty-nvl(wq.qty_in_cust_order, 0) - nvl(iq.qty_in_ibt,
0)),0) "QTY_AVAIL",
        decode(greatest((pl.qty-nvl(wq.qty_in_cust_order, 0) -
nvl(iq.qty_in_ibt, 0)),0),
        0,0,
        (pl.qty-nvl(wq.qty_in_cust_order, 0) - nvl(iq.qty_in_ibt, 0)))
"FLOOR_QTY_AVAIL",
        decode(greatest((pl.qty-nvl(wq.qty_in_cust_order, 0) -
nvl(iq.qty_in_ibt, 0)),0),
        0,0,
        (pl.qty-nvl(wq.qty_in_cust_order, 0) - nvl(iq.qty_in_ibt, 0)))
- 1 "ADJ_QTY_AVAIL",
        p.user_1
from    part p,
        part_location pl,
        sw_custord_open_parts_dcms wq,
        sw_ibt_open_parts_whs_2 iq
where   p.id = pl.part_id
and     user_1 = 'PRICESHEET'
and     pl.warehouse_id = 'DCMS'
and     pl.location_id = 'DCMS'
and     pl.part_id = wq.part_id(+)
and     pl.part_id = iq.part_id(+)

group by p.id, p.description, p.product_code, p.commodity_code, p.user_10, pl.qty, pl.warehouse_id,

wq.qty_in_cust_order, iq.qty_in_ibt, p.user_1 WITH READ ONLY
/

Here is the context of my package spec so far:

CREATE OR REPLACE PACKAGE CUST_INV_SEARCH AS top varchar2(5000) := '
<html>
 <head>
<title>Inventory Availability Search Results</title>
 </head>

  <body>


    <h1><center>Inventory Availability Search Results</center></h1>

    <br>

 
 
Displayed availability is not a guarantee of assignment to a
specific order.

The displayed availability is dynamic and subject to continuous change throughout any given business day.

       <br>
       <br>
       Warehouse = <b>WHS1</b>
       <br>
       <br>

Below are the results of your search:
<table cellspacing=15>
<th>Part Id</th><th>Description</th><th>UPC</th><th>Availability</
th>
 ';
 bottom varchar2(5000) := '
</table>

 
 
 

 </body>
 </html>
 ';

PROCEDURE cust_inv_data(
p_part_id user1.cust_inv_ck_view1.part_id%TYPE );

PROCEDURE cust_inv_data(
p_description user1.cust_inv_ck_view1.description%TYPE );

END CUST_INV_SEARCH;
/

Here is my package body so far:

CREATE OR REPLACE PACKAGE BODY CUST_INV_SEARCH AS

  v_cust_inv_part_id user1.cust_inv_ck_view1.part_id%TYPE;
  v_cust_inv_description user1._cust_inv_ck_view1.description%TYPE;
  v_cust_inv_upc user1.cust_inv_ck_view1.upc%TYPE;

FUNCTION PRINT_RESULTS(

    p_part_id uesr1.cust_inv_ck_view1.part_id%TYPE,
    p_description user1.cust_inv_ck_view1.description%TYPE,
    p_upc user1.cust_inv_ck_view1.upc%TYPE,
    p_avail_qty uesr1.cust_inv_ck_view1.floor_qty_avail%TYPE)

  RETURN VARCHAR2 IS
  temp varchar2(250);
  BEGIN

    temp := '<tr><td>'||p_part_id||'</td>
             <td>'||p_description||'</td>
             <td>'||p_upc||'</td>
             <td>'||p_avail_qty||'</td></tr>';

    RETURN temp;
  END PRINT_RESULTS;   PROCEDURE cust_inv_data(p_part_id uer1.cust_inv_ck_view1.part_id %TYPE)
  IS
  Cursor c_cust_inv_data IS
    SELECT part_id, description, upc
    FROM cust_inv_ck_view1
    WHERE part_id LIKE UPPER(p_part_id)||'%'     ORDER BY part_id;
  BEGIN
    htp.prn(cust_inv_search.top);
    FOR l_cust_inv_data_rec IN c_cust_inv_data LOOP

      v_cust_inv_part_id := l_cust_inv_data_rec.part_id;
      v_cust_inv_description := l_cust_inv_data_rec.description;
      v_cust_inv_upc := l_cust_inv_data_rec.upc;
      htp.prn(print_results(v_cust_inv_part_id,
                            v_cust_inv_description,
                            v_cust_inv_upc
                           ));

    END LOOP;   htp.prn(cust_inv_search.bottom);
EXCEPTION
  WHEN OTHERS THEN
    htp.prn(sqlcode||' : '||sqlerrm);
END cust_inv_data;

  PROCEDURE cust_inv_data(p_description
user1.cust_inv_ck_view1.description%TYPE)   IS
  Cursor c_cust_inv_data IS
    SELECT part_id, description, upc
    FROM cust_inv_ck_view1
    WHERE description LIKE '%'||UPPER(p_description)||'%'     ORDER BY part_id;
  BEGIN
    htp.prn(cust_inv_search.top);
    FOR l_cust_inv_data_rec IN c_cust_inv_data LOOP

      v_cust_inv_part_id := l_cust_inv_data_rec.part_id;
      v_cust_inv_description := l_cust_inv_data_rec.description;
      v_cust_inv_upc := l_cust_inv_data_rec.upc;
      htp.prn(print_results(v_cust_inv_part_id,
                            v_cust_inv_description,
                            v_cust_inv_upc
                            ));

    END LOOP;   htp.prn(cust_inv_search.bottom);
EXCEPTION
  WHEN OTHERS THEN
    htp.prn(sqlcode||' : '||sqlerrm);
END cust_inv_data;

END cust_INV_SEARCH;
/

I need to compare the adj_qty_avail from the view with one of the desired quantities the user enters through the procedure and return a text string based on the following rules:

If adj_qty_avail < desired qty entered then display "Partial in stock" else
If adj_qty_avail > desired qty entered then display "In stock" else
If adj_qty_avail <= 0 then display "Out of stock"

I feel that I have most of the components in place, but I have not found the correct way to capture the desired qty entered through the procedure, do the comparison, and return one of the strings.

The output displayed back to the user would be as follows:

Part ID, Description, UPC, Availability (as text string)

Example output:

PART1234 Widget #1234 123456789 In stock

Any suggestions on how to finailize my package code would be greatly appreciated.

Best regards. Received on Mon Jul 02 2007 - 10:44:25 CDT