Hello,
I need assistance with the count because v cnt=1 works perfectly while v cnt 2 and 3 fail to get the desired outcome.
please assist me in formulating the logic
---code
declare
cursor c1 is
with store_info as
(select 12345 item,9999 det_num,20 s1_qty from dual union all
select 13456 item,9999 det_num,50 s1_qty from dual union all
select 14567 item,9999 det_num,50 s1_qty from dual union all
select 17890 item,9999 det_num,50 s1_qty from dual union all
select 18900 item,9999 det_num,50 s1_qty from dual )select item,det_num,s1_qty from store_info;
cursor c2(i_item in number,i_det_num in number) is
with order_info as
(select 12345 item,9999 det_num,30 need_qty from dual union all
select 13456 item,9999 det_num,30 need_qty from dual union all
select 14567 item,9999 det_num,40 need_qty from dual union all
select 14567 item,9999 det_num,30 need_qty from dual union all
select 17890 item,9999 det_num,40 need_qty from dual union all
select 17890 item,9999 det_num,5 need_qty from dual union all
select 17890 item,9999 det_num,5 need_qty from dual union all
select 18900 item,9999 det_num,40 need_qty from dual union all
select 18900 item,9999 det_num,10 need_qty from dual union all
select 18900 item,9999 det_num,51 need_qty from dual
) select * from order_info where item=i_item and det_num=i_det_num;
s_item number;
s_det_num number;
s_s1_qty number;
o_item number;
o_det_num number;
o_need_qty number;
v_cnt number;
w1 number;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO s_item,s_det_num,s_s1_qty;
EXIT WHEN c1%notfound;
OPEN c2(s_item,s_det_num); --<--This is how you pass the paramater to your cursor
LOOP
FETCH c2 INTO o_item,o_det_num,o_need_qty;
EXIT WHEN c2%notfound;
-- dbms_output.put_line(o_item || ' ---- ' || o_det_num || ' ---- ' || o_need_qty);
with order_info as
(select 12345 item,9999 det_num,30 need_qty from dual union all
select 13456 item,9999 det_num,30 need_qty from dual union all
select 14567 item,9999 det_num,40 need_qty from dual union all
select 14567 item,9999 det_num,30 need_qty from dual union all
select 17890 item,9999 det_num,40 need_qty from dual union all
select 17890 item,9999 det_num,5 need_qty from dual union all
select 17890 item,9999 det_num,5 need_qty from dual union all
select 18900 item,9999 det_num,40 need_qty from dual union all
select 18900 item,9999 det_num,10 need_qty from dual union all
select 18900 item,9999 det_num,10 need_qty from dual
)
select count(*) into v_cnt from order_info where item=s_item;
if v_cnt=1 and (s_s1_qty<o_need_qty) then
w1:=s_s1_qty;
dbms_output.put_line(s_item || ' ---- ' || s_det_num || ' ---- ' || w1);
else
w1:=o_need_qty-1;
dbms_output.put_line(s_item || ' ---- ' || s_det_num || ' ---- ' || w1);
end if;
**/*
if v_cnt=2 then
* For requirements with two records, choose o_need_qty high, then choose s s1 qty > o_need_qty, allocate o_need_qty, and finally allocate the remaining quantity to the second record.
example first records 40 and second record 10
if v_cnt=3 then
* For requirements with three records, choose o_need_qty high, then choose s_s1 qty > o_need_qty, allocate o_need_qty, and chose second records compare the s_s1 qty > o_need_qty(remaing) then allocate o_need_qty again third records allocate o_need_qty
example first records 40 and second record 5 and thirds 5
else
For requirements with three records, choose o_need_qty high, then choose s_s1 qty > o_need_qty, allocate o_need_qty, and chose second records compare the s_s1 qty > o_need_qty(remaing) then allocate o_need_qty again comapre third records allocate s1 qty < o_need_qty leave it the third record
example first records 40 and second record 10 and thirds no allocation
END LOOP;
CLOSE c2;
-- dbms_output.put_line(s_item || ' ---- ' || s_det_num || ' ---- ' || s_s1_qty);
END LOOP;
CLOSE c1;
END;