Saya concern bagian ini dulu ya:
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;
proses insert do_item, update cust_ord_item, update do_cust_item dijalankan
jika terjadi exception pada query yang saya highlight merah.
Coba review lagi, kira- kira akan selalu masuk exception ngk ?
Warm regards,
Rudy Wardhany
Yahoo ID: [email protected]
2010/4/30 Jack Mania <[email protected]>
>
>
> Buat Pak Bowo,
>
> Terima kasih atas responsnya.
> Saya sudah coba untuk rubah di cursornya sesuai dengan masukan dari Pak
> Bowo, bahkan saya coba untuk remark, tetapi tetap saja yang 'tergenerate'
> datanya seperti yang sudah saya posting sebelumnya. Dan yang masih bikin
> puyeng saya, kenapa data yang terupdate/tergenerate seperti lompat2 1
> record.
>
> CURSOR c_do_cust IS
>
> select assy_no, sum(qty)
> from do_cust_item
> where do_no= vdo_no
> and code = vcode
> -- AND (STATUS <> 'GENERATE'
> -- OR STATUS IS NULL)
>
> group by assy_no
> order by assy_no;
>
> Buat Pak Rudi,
>
> Terima kasih atas koreksinya.
> Maaf memang saya ada salah ketik di procedure tersebut, yang benar
> adalah
> PROCEDURE insert_do (vdo_no char, vord_no char, vcode char) IS
> Untuk nilai parameter yang masuk, dari contoh yang sudah saya kasih, nilai
> untuk Do No dan Code hanya ada 1 Pak
> - Do No = '003048099'
> - Code = 'TR'
>
> Salam,
> Andika
>
>
> --- In [email protected] <indo-oracle%40yahoogroups.com>,
> "yulius_wibowo" <yulius_wib...@...> wrote:
> >
> >
> > 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] <indo-oracle%40yahoogroups.com>, Rudy
> W Sitanggang rudy.sitanggang@ 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 j4ckm4n@ 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_staredge@
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
>
> [Non-text portions of this message have been removed]
>
>
>
--
Warm regards,
Rudy Wardhany
Yahoo ID: [email protected]
[Non-text portions of this message have been removed]
------------------------------------
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [email protected]
Website: http://indooracle.wordpress.com
http://www.facebook.com/group.php?gid=51973053515
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.blogspot.comYahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/indo-oracle/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
