| Logical error [message #307007] |
Mon, 17 March 2008 08:55  |
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
|
Member |
|
|
Dear all,
Actually am developing a logic for introduction of rack number concept.But am getting a logical error with it.my basic logic is
if i enter multiple records in my form in its " gmi_xfer_mst" block when its saved it should introduce a serial number into a temporary table and its corresponding lotid,itemid,location,warehouse,last_updated_Date
if i delete the record then it should update the item_id and lot_id of that record to null.But my problem is if i delete it is creating a duplicate row and then its deleting.kindly help me out.
PROCEDURE rack_no IS
l_item_type varchar2(10);
l_attrib7 varchar2(10);
l_location varchar2(10);
l_whse_code varchar2(10);
l_count number;
l_min_rack_no number;
l_max_rack_no number;
BEGIN
if :TNQ_OPM_MIT_HDR.DOC_TYPE='SCN' then --1
fnd_message.debug('HI VIZITH');
l_whse_code:=null;
l_location:=null;
l_item_type:=null;
l_attrib7:=-null;
l_min_rack_no:=null;
l_max_rack_no:=null;
l_count:=null;
go_block('gmi_xfer_mst_v');
first_record;
LOOP
BEGIN
select parameter2,parameter3 into l_whse_code,l_location
from tnq_all_parameter_tb
where
parameter2=:GMI_XFER_MST_V.TO_WAREHOUSE
and parameter3=:GMI_XFER_MST_V.TO_LOCATION
and parameter5='YES' and rownum<2;
EXCEPTION
when others then
null;
end;
fnd_message.debug('2'||L_WHSE_CODE);
if l_whse_code is not null then --2
select inv_type,attribute7 into l_item_type,l_attrib7
from ic_item_mst_b
where item_id=:GMI_XFER_MST_V.ITEM_ID;
fnd_message.debug('3'||l_item_type||l_attrib7||:GMI_XFER_MST_V.TRANSFER_STATUS );
if l_item_type='FG' and l_attrib7<>'Z' and :GMI_XFER_MST_V.lot_id>0 AND :GMI_XFER_MST_V.TRANSFER_STATUS=1 then --3
fnd_message.debug('4');
select
min(rack_number) into l_min_rack_no from tnq_opm_fg_rack_no_Alloc where whse_code=l_whse_code
and location=l_location and item_id is null and lot_id is null;
fnd_message.debug('5');
if l_min_rack_no is not null then --4
/*insert into tnq_opm_fg_rack_no_alloc
values(l_min_rack_no,:GMI_XFER_MST_V.ITEM_id,:GMI_XFER_MST_V.lot_id,l_whse_code,l_location,sysdate);*/
fnd_message.debug('6'||l_min_rack_no );
update tnq_opm_fg_rack_no_alloc
set item_id=:GMI_XFER_MST_V.ITEM_id,lot_id=:GMI_XFER_MST_V.LOT_ID,last_updated_date=sysdate
where whse_code=:GMI_XFER_MST_V.TO_WAREHOUSE
and location=:GMI_XFER_MST_V.TO_LOCATION
and rack_number=l_min_rack_no;
fnd_message.debug('7');
ELSE if l_min_rack_no is null then --5
select count(rack_number) into l_count from
tnq_opm_fg_rack_no_alloc where whse_code=:GMI_XFER_MST_V.TO_WAREHOUSE
AND location=:GMI_XFER_MST_V.TO_LOCATION;
fnd_message.debug('8'||l_count);
if l_count>0 then --6
fnd_message.debug('9'||:GMI_XFER_MST_V.lot_id||:GMI_XFER_MST_V.ITEM_id||l_whse_code||l_location);
select max(rack_number) into l_max_rack_no from tnq_opm_fg_rack_no_alloc
where whse_code=l_whse_code and location=l_location;
fnd_message.debug('9'||l_max_rack_no);
insert into tnq_opm_fg_rack_no_alloc
values(l_max_rack_no+1,:GMI_XFER_MST_V.ITEM_id,:GMI_XFER_MST_V.lot_id,l_whse_code,l_location,sysdate);
ELSE
fnd_message.debug('10'||:GMI_XFER_MST_V.lot_id||:GMI_XFER_MST_V.ITEM_id||l_whse_code||l_location);
insert into tnq_opm_fg_rack_no_alloc
values(1,:GMI_XFER_MST_V.ITEM_id,:GMI_XFER_MST_V.lot_id,l_whse_code,l_location,sysdate);
end if;--6
end if; --5
end if;--4
end if;--3
fnd_message.debug('12'||:GMI_XFER_MST_V.lot_id||:GMI_XFER_MST_V.ITEM_id||:GMI_XFER_MST_V.DELETE_MARK||:GMI_XFER_MST_V.TRANSFER_STATUS );
end if;--2
if :TNQ_OPM_MIT_HDR.DOC_TYPE='SCN' and :GMI_XFER_MST_V.DELETE_MARK=1 AND :GMI_XFER_MST_V.TRANSFER_STATUS=4 THEN --7
-- and AND :GMI_XFER_MST_V.TRANSFER_STATUS=4 THEN
-- fnd_message.debug('12'||:GMI_XFER_MST_V.lot_id||:GMI_XFER_MST_V.ITEM_id||l_whse_code||l_location||:GMI_XFER_MST_V.DELETE_MARK||:GMI_XFER_MST_V.TRANSFER_STATUS );
UPDATE TNQ_OPM_FG_RACK_NO_ALLOC SET ITEM_ID=NULL , LOT_ID=NULL,LAST_UPDATED_DATE=SYSDATE WHERE
ITEM_ID=:GMI_XFER_MST_V.ITEM_id
AND LOT_ID=:GMI_XFER_MST_V.lot_id;
-- fnd_message.debug('10'||:GMI_XFER_MST_V.lot_id||:GMI_XFER_MST_V.ITEM_id||l_whse_code||l_location);
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE' ;
next_record;
END IF ;--1
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE' ;
next_record;
END LOOP;
first_record;
--DO_KEY('COMMIT_FORM');
APP_STANDARD.EVENT('KEY-COMMIT');
end if; --1
commit;
EXCEPTION
when others then
NULL;
END;
--vizith
|
|
|
|
|
|
| Re: Logical error [message #307476 is a reply to message #307466] |
Wed, 19 March 2008 00:03  |
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
|
Member |
|
|
Dear martin,
Thanks for your reply .. it is almost over friend.just facing a small error.when i press delete but it is giving an error dilagoue.but if i click ok thn its going on .
regds
vizith
|
|
|
|
|
|
| Re: Logical error [message #307528 is a reply to message #307493] |
Wed, 19 March 2008 01:22 |
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
|
Member |
|
|
Dear david,
oh sorry david,anyway in the following procedure am basically inserting and updating a table named "tnq_opm_fg_rack_no_alloc "
sir,and for each and every newly inserted record it should generate a serial number,
if am deleting a recrd from the table it should make item_id and lot_id feilds to null and
if i am inserting a new row then it should check the warehouse and location feilds in the parameters l_whse_code and l_location if they are same then it should update that table with new item_id lot_id of new record
else t should create a new record
its dsiplaying as 'ic_record_not_found '
if i press ok then no problem its working properly but dont know why that error came.
|
|
|
|
|
|
| Re: Logical error [message #307537 is a reply to message #307528] |
Wed, 19 March 2008 01:44 |
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
|
Member |
|
|
hi david,
I done it,now that error is not displayiing sir,i removed the delete code in the procedure and called where theay are updating delete mark =1 from 0 and transfer_status to 4 i.e. delete. After that step i done done
if :TNQ_OPM_MIT_HDR.DOC_TYPE='SCN' and :GMI_XFER_MST_V.DELETE_MARK=1 AND :GMI_XFER_MST_V.TRANSFER_STATUS=4 THEN --7
-- and AND :GMI_XFER_MST_V.TRANSFER_STATUS=4 THEN
-- fnd_message.debug('12'||:GMI_XFER_MST_V.lot_id||:GMI_XFER_MST_V.ITEM_id||l_whse_code||l_location||:GMI_XFER_MST_V.DELETE_MARK||:GMI_XFER_MST_V.TRANSFER_STATUS );
UPDATE TNQ_OPM_FG_RACK_NO_ALLOC SET ITEM_ID=NULL , LOT_ID=NULL,LAST_UPDATED_DATE=SYSDATE WHERE
ITEM_ID=:GMI_XFER_MST_V.ITEM_id
AND LOT_ID=:GMI_XFER_MST_V.lot_id;
-- fnd_message.debug('10'||:GMI_XFER_MST_V.lot_id||:GMI_XFER_MST_V.ITEM_id||l_whse_code||l_location);
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE' ;
-- next_record;
END IF ;--1
|
|
|
|
| Re: Logical error [message #307542 is a reply to message #307537] |
Wed, 19 March 2008 01:50 |
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
|
Member |
|
|
Dear David,
Thank you so much,Even though am not getting this error now i will check entire package and then confirm.thank you so much.
its really great to have help from you.Any way if i found that phrase in my code how should i eliminate this error david.because this error was occured only when the location is not available in the paramater table. i.e if the data is not found in the update query.
UPDATE TNQ_OPM_FG_RACK_NO_ALLOC SET ITEM_ID=NULL , LOT_ID=NULL,LAST_UPDATED_DATE=SYSDATE WHERE
ITEM_ID=:GMI_XFER_MST_V.ITEM_id
AND LOT_ID=:GMI_XFER_MST_V.lot_id;
[/CODE]
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
