Coba cek ulang pada WHERE clause utk cursornya, khususnya pada bagian berikut ini: ... AND (NVL(STATUS,'-') <> 'GENERATE' OR NVL(STATUS,'-') IS NULL) ...
NVL(STATUS,'-')=> pasti akan menghasilkan nilai bukan NULL. sehingga OR NVL(STATUS,'-') IS NULL) tidak ada fungsinya. Seharusnya OR STATUS IS NULL, bukan? cmiiw, Bowo --- In [email protected], Rudy W Sitanggang <rudy.sitangg...@...> wrote: > > Bro, > > parameter procedure nya yakin nih? > > PROCEDURE insert_do (vdo_no char, vdo_no char, vord_no char, vcode char) IS > > > trus, pada proses update: > > UPDATE do_cust_item > SET status = 'GENERATE' > WHERE do_no = vdo_no > AND assy_no = vassy_no > AND code = vcode > AND status is null; > > kan tergantung pada vdo_no,vcode (dikirim lewat paramater) juga tuh, jadi > mesti pastiin juga ketika panggil procedure ini, apakah sudah bener-bener > terkirim semua value parameter2(vdo_no,vcode) untuk row-row yang ingin > diupdate. > > Kalo udah yakin baru deh check script procedure nya(kira kira masih salah > dimana). > > Gitu aja dulu dari saya bro. :) > > > On Wed, Apr 28, 2010 at 4:32 PM, Jack Mania <j4ck...@...> wrote: > > > > > > > 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] > > > > > > > > > > -- > Warm regards, > > > Rudy Wardhany > > > Yahoo ID: rudy_stare...@... > > > [Non-text portions of this message have been removed] >
