hai shawn green,
thanks for ur response.My situation is to generate a production
plan.for this I collect the data from sales orders and compare the stock
availability in rthe warehouse and if the stock is less than the ordered
quantity I plan for production and for this I have to issue the raw material
required for the production by calculating based on bom(bill of material)
details.I am doing this but for every item in the sales oredr I am inserting
the record even though same item is in the another order.I have to update
the quantity i the item is already in the list otherwise i have to insert.
please give me solution if u got my situation.
On Sun, Apr 12, 2009 at 1:07 AM, Shawn Green <shawn.gr...@sun.com> wrote:
> syed basha wrote:
>
>> delimiter //
>> create procedure sample1(in p_item varchar(30),
>> in p_size varchar(6),
>> in p_quantity decimal(10,3),
>> in p_unit varchar(3),
>> in p_autoincrement varchar(30),
>> out v_mess varchar(50)
>>
>> )
>> begin
>> declare done int default 0;
>> declare v_bhqty decimal(10,3);
>> declare v_bhunit varchar(3);
>> declare v_blrawm varchar(30);
>> declare v_blqty decimal(10,3);
>> declare v_blunit varchar(3);
>> declare v_puid varchar(30);
>> declare v_conqty decimal(10,3);
>> declare v_plid int(3) default 1;
>> declare v_calqty decimal(10,3);
>> declare c_bomdet cursor for select bh.qty,bh.unit,bl.rawm,bl.qty,bl.unit
>> from bomhead bh,bomlins bl
>> where bl.item=bh.item and bl.szid=bh.szid and bl.item=p_item and
>> bl.szid=p_size;
>> declare continue handler for not found set done=1;
>> select ppid into v_puid from prplrawm where ppid=(select
>> concat('pph',max(convert(substr(ppid,4),unsigned integer))) from
>> prpllins);
>> open c_bomdet;
>> repeat
>> fetch c_bomdet into v_bhqty,v_bhunit,v_blrawm,v_blqty,v_blunit;
>> if not done then
>> call convertion(p_unit,v_bhunit,p_item,p_size,p_quantity,v_conqty);
>> set v_calqty=v_conqty*v_blqty;
>> if v_puid is not null then
>> set v_mess='max id is exists';
>> else
>>
>> insert into prplrawm(ppid,plid,item,szid,rawm,rqty,runt)
>> values(p_autoincrement,v_plid,p_item,p_size,v_blrawm,v_blrqty,v_blrunt);
>> set v_plid=v_plid+1;
>> end if;
>> end if;
>> until done end repeat;
>> close c_bomdet;
>> end
>> //
>> delimiter ;
>>
>> when i call this procedure it is not performing the insert statement even
>> though the condition is true.
>>
>> please tell me why.
>>
>> thank you
>>
>>
>>
> How do you know your condition is true?
>
> Could this be part of a transaction that is rolled-back instead of
> committed?
>
> There are too many unknowns about your situation to make a clear judgement
> on this particular problem.
>
> --
> Shawn Green, MySQL Senior Support Engineer
> Sun Microsystems, Inc.
> Office: Blountville, TN
>
>
>
--
sibasha
