Dear rekan-rekan indo-oracle,
Saya lagi ada masalah, minta tolong sharing dan bantuannya.
Saya membuat procedure seperti di bawah ini.
Pertanyaannya kenapa setelah saya running procedure (proses generate)
data yang terupdate hanya sebagian ('Status ='GENERATE')
yang saya inginkan semua data tersebut terupdate.
Apa ada yang salah dengan procedure yang saya buat.
Terima kasih sebelumnya atas sharing dan bantuannya
Salam,
Andika
PROCEDURE insert_do (vdo_no char, vdo_no char, vord_no char, vcode char) IS
BEGIN
DECLARE
vassy_no finished_good.assy_no%type;
vassy_no2 finished_good.assy_no%type;
vcust_qty do_cust_item.qty%type;
vcar_line cust_ord_item.car_line%type;
vpo_qty cust_ord_item.fixed_order%type;
dummy varchar2(1);
CURSOR c_do IS
select assy_no, sum(qty)
from do_cust_item
where do_no= vdo_no
and code = vcode
AND (NVL(STATUS,'-') <> 'GENERATE'
OR NVL(STATUS,'-') IS NULL)
group by assy_no
order by assy_no;
BEGIN
FOR n IN c_do LOOP
timed_pause(.3);
spin.nexticonspin;
synchronize;
FETCH c_do into vassy_no, vcust_qty;
EXIT WHEN c_do%NOTFOUND;
select f.assy_no,c.car_line,nvl(c.fixed_order,0)-nvl(c.do_qty,0)
po_qty into vassy_no2,vcar_line,vpo_qty
from finished_good f, cust_ord_item c
where f.assy_no=c.assy_no
and f.assy_no = vassy_no
and c.ord_no = vord_no;
if vassy_no is null then
msg_alert('Cust Assy No: '||vassy_no||' Data Not Found in Master
FG','E',true);
end if;
if vpo_qty < vcust_qty then
msg_alert('Outstanding Order '||ltrim(to_char(vpo_qty))||' <
'||'Delivery Order '||ltrim(to_char(vcust_qty)),'E',true);
end if;
begin
select null into dummy
from do_item
where do_no = vdo_no
and assy_no = vassy_no;
exception
when too_many_rows then null;
when no_data_found then
INSERT INTO
do_item(DO_NO,ASSY_NO,LOC_NO,WH_CODE,DO_QTY,INV_NO,RETUR_QTY,RETUR_PRICE,CAR_LINE)
VALUES
(vdo_no,vassy_no,'FA-0000000001','FA',vcust_qty,null,null,null,vcar_line);
UPDATE cust_ord_item coi SET coi.do_qty = nvl(coi.do_qty,0) + vcust_qty
WHERE coi.assy_no = vassy_no
and coi.ord_no = vord_no;
UPDATE do_cust_item
SET status = 'GENERATE'
WHERE do_no = vdo_no
AND assy_no = vassy_no
AND code = vcode
AND status is null;
COMMIT;
end;
END LOOP;
execute_query;
EXCEPTION
when no_data_found then
msg_alert('Data not found','E',true);
when others then
msg_alert(sqlerrm,'E',true);
END;
END;
Data sebelum digenerate :
DO_NO ASSY_NO QTY CODE STATUS FLAG
003048099 82111-BZ430-00 4 TR Y
003048099 82111-BZ450-00 4 TR Y
003048099 82121-BZ320-00 14 TR Y
003048099 82122-BZ120-00 20 TR Y
003048099 82141-BZ620-00 4 TR Y
003048099 82141-BZ640-00 2 TR Y
003048099 82141-BZ650-00 4 TR Y
003048099 82141-BZ660-00 2 TR Y
003048099 82151-BZ170-00 10 TR Y
003048099 82151-BZ180-00 10 TR Y
003048099 82152-BZ170-00 10 TR Y
003048099 82152-BZ180-00 10 TR Y
003048099 82153-BZ110-00 15 TR Y
003048099 82154-BZ050-00 15 TR Y
003048099 82161-BZ380-00 5 TR Y
003048099 82161-BZ401-00 5 TR Y
003048099 82161-BZ410-00 5 TR Y
003048099 82162-BZ090-00 20 TR Y
003048099 82171-BZ140-00 10 TR Y
003048099 82184-BZ130-00 10 TR Y
003048099 82184-BZ140-00 10 TR Y
003048099 82185-BZ090-00 10 TR Y
003048099 82185-BZ100-00 10 TR Y
003048099 82415-BZ010-00 20 TR Y
Data sesudah digenerate :
DO_NO ASSY_NO QTY CODE STATUS FLAG
003048099 82111-BZ430-00 4 TR Y
003048099 82111-BZ450-00 4 TR GENERATE Y
003048099 82121-BZ320-00 14 TR Y
003048099 82122-BZ120-00 20 TR GENERATE Y
003048099 82141-BZ620-00 4 TR Y
003048099 82141-BZ640-00 2 TR GENERATE Y
003048099 82141-BZ650-00 4 TR Y
003048099 82141-BZ660-00 2 TR GENERATE Y
003048099 82151-BZ170-00 10 TR Y
003048099 82151-BZ180-00 10 TR GENERATE Y
003048099 82152-BZ170-00 10 TR Y
003048099 82152-BZ180-00 10 TR GENERATE Y
003048099 82153-BZ110-00 15 TR Y
003048099 82154-BZ050-00 15 TR GENERATE Y
003048099 82161-BZ380-00 5 TR Y
003048099 82161-BZ401-00 5 TR GENERATE Y
003048099 82161-BZ410-00 5 TR Y
003048099 82162-BZ090-00 20 TR GENERATE Y
003048099 82171-BZ140-00 10 TR Y
003048099 82184-BZ130-00 10 TR GENERATE Y
003048099 82184-BZ140-00 10 TR Y
003048099 82185-BZ090-00 10 TR GENERATE Y
003048099 82185-BZ100-00 10 TR Y
003048099 82415-BZ010-00 20 TR GENERATE Y
[Non-text portions of this message have been removed]
