-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|