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 <[email protected]> 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: [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/
