DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Loop Contained in PKG looping twice!

  1. #1
    Join Date
    Aug 2000
    Posts
    68

    Loop Contained in PKG looping twice!

    Hi there the following is a pkg i have wrote to insert records into a table based on order_category, so basically,

    The inputs(parameters) to the pkg are the reporting_date, & header_id.


    Then i declare 2 cursor's, 1 of these cursors is to split the order_id's by order category, and then insert rows to the base table based on this cursor values as below.

    I have hit a brick wall, with testing this, ive hardcoded values & the pgm works. But runs * 2(inserts dubplicate values) when i run the pkg, ive also tried changing the loop, w/o any success!

    Can anybody see if i am doing somthing wrong?


    Thanks in advace for any help!

    PROCEDURE NET_CHANGES(p_header_id IN NUMBER, p_reporting_date IN DATE)

    -- declare local variables
    l_max_group_id NUMBER;
    l_group_id NUMBER;
    l_header_id NUMBER;
    l_err_code varchar2(200);
    l_category varchar2(10);


    cursor c_category IS --Will return order_category's 'A' & 'B'
    select order_category
    from so_headers_all sha
    where sha.header_id = p_header_id;


    cursor c_max_group_id is
    SELECT max(group_id)
    FROM emcoe_bookings_ledger
    WHERE header_id = p_header_id
    AND pbs_spread_flag <> 'C'


    BEGIN

    FOR r_category in c_category Loop


    l_category := r_category.order_category;

    EXIT WHEN c_category%NOTFOUND;



    OPEN c_max_group_id;
    LOOP
    FETCH c_max_group_id into l_max_group_id;

    EXIT WHEN c_max_group_id%NOTFOUND;
    END LOOP;
    CLOSE c_max_group_id;

    IF l_category = 'A' THEN


    SELECT bookings_ledger_s2.nextval
    INTO l_group_id
    FROM SYS.DUAL;


    INSERT INTO BOOKINGS_LEDGER
    (

    SELECT

    BOOKINGS_LEDGER_S1.NEXTVAL, /*TRANSACTION_ID */
    SYSDATE,
    SYSDATE,
    CREATED_BY,
    SYSDATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    SO_LINES_TRIG_FLAG,
    ORG_ID,
    l_group_id,
    HEADER_ID, LINE_ID, PARENT_LINE_ID,
    LINK_TO_LINE_ID,
    OPTION_FLAG
    INVENTORY_ITEM_ID,
    COMPONENT_CODE,
    COMPONENT_SORT_CODE,
    COMPONENT_SEQUENCE_ID,
    LINE_NUMBER, ORDERED_QUANTITY,
    CANCELLED_QUANTITY,
    QUANTITY*(-1),


    FROM EMCOE_BOOKINGS_LEDGER

    where GROUP_ID = l_max_group_id

    and HEADER_ID = p_header_id

    and order_category = 'A'
    )
    ;


    ELSE IF l_rma_category = 'B'

    then


    INSERT INTO BOOKINGS_LEDGER
    (

    SELECT

    BOOKINGS_LEDGER_S1.NEXTVAL, /*TRANSACTION_ID */
    SYSDATE,
    SYSDATE,
    CREATED_BY,
    SYSDATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    SO_LINES_TRIG_FLAG,
    ORG_ID,
    l_group_id,
    HEADER_ID, LINE_ID, PARENT_LINE_ID,
    LINK_TO_LINE_ID,
    OPTION_FLAG
    INVENTORY_ITEM_ID,
    COMPONENT_CODE,
    COMPONENT_SORT_CODE,
    COMPONENT_SEQUENCE_ID,
    LINE_NUMBER, ORDERED_QUANTITY,
    CANCELLED_QUANTITY,
    QUANTITY*(-1)

    FROM EMCOE_BOOKINGS_LEDGER
    where HEADER_ID = p_header_id
    and order_category = 'B'

    )
    ;

    END IF;
    END IF;

    END Loop; -- end r_category

    END Net_changes;
    Carpe Diem

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I dont understand why you need this cursor

    cursor c_max_group_id is
    SELECT max(group_id)
    FROM emcoe_bookings_ledger
    WHERE header_id = p_header_id
    AND pbs_spread_flag <> 'C'



    That always returns one row isnt it?

    these two lines

    l_category := r_category.order_category;
    EXIT WHEN c_category%NOTFOUND;

    seems redundant to me, you can simply use r_category.order_category to reference

  3. #3
    Join Date
    Aug 2000
    Posts
    68
    thanks for posting, it is necessary.

    I need it as an insert condition, i did'nt include the group_id part in the insert statement,

    order type 'A', inserts based on this max group_id, thus all 'A' orders have the same group_id

    Order_type 'B' have each has a unique group_id for each row inserted into the table(i just a sequence)

    Sorry for the confusion created by me here! Anything stand out as to why this loops twice?
    Carpe Diem

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I mean cant you simply do

    SELECT max(group_id)
    into l_max_group_id;
    FROM emcoe_bookings_ledger
    WHERE header_id = p_header_id
    AND pbs_spread_flag <> 'C'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width