PL/SQL script...

Guys,

[Quoted] [Quoted] Don't laugh on me but I have a quite dumb and annoying problem:

[Quoted] The following script is inserting the rows at the insert statement but, [Quoted] [Quoted] more important, I need to display the records. [Quoted] [Quoted] The insert was done to see if it was working, It did, should not be there.
[Quoted] [Quoted] I want to display all records that are matching the loop with the [Quoted] [Quoted] dbms_output line (the only possible to display data so far I know). [Quoted] I have tried the dbms_output statement in many ways but didn't work either.

[Quoted] [Quoted] Any thoughts, please help...

CM.

• WHENEVER SQLERROR EXIT FAILURE; DECLARE

    CURSOR cs_prods is

        select  msi.inventory_item_id,
                msi.segment1
        from    mtl_system_items  msi
        where   msi.organization_id     = 7
        and     msi.inventory_item_status_code  = 'Active'
        and     msi.customer_order_enabled_flag = 'Y'
        ;

    cs_item_id          mtl_system_items.inventory_item_id%type;
    cs_item_num         mtl_system_items.segment1%type;
    l_item_id           mtl_system_items.inventory_item_id%type;
    l_item_num          mtl_system_items.segment1%type;
    l_open_qty          number;
    l_onhand_qty        number;

BEGIN     OPEN cs_prods;

    LOOP

        FETCH cs_prods INTO
              cs_item_id,
              cs_item_num;

        EXIT when cs_prods%NOTFOUND;

        BEGIN

                select  sum(moq.transaction_quantity)
                into    l_onhand_qty
                from    mtl_onhand_quantities   moq
                where   moq.organization_id     = 2
                and     moq.inventory_item_id   = cs_item_id
                and     moq.subinventory_code   = 'Finish'
                group by moq.inventory_item_id;

        exception
                when no_data_found then
                        l_onhand_qty := 0;

        END;

        insert into items_stocks
        (item_id,
        product_number,
        quantity_ordered)
        values (cs_item_id,
                cs_item_num,
                l_onhand_qty);
        commit;

    dbms_output.put_line
('stock: '||cs_item_id||cs_item_num||l_onhand_qty);

   END LOOP;   close cs_prods;

END;
/
-- exit;

$

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 15 2000 - 16:35:57 CET