Home » Developer & Programmer » Forms » Logical error (forms 6i,oracle apps 11.5.10)
Logical error [message #307007] Mon, 17 March 2008 08:55 Go to next message
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 #307466 is a reply to message #307007] Tue, 18 March 2008 23:38 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Re: Logical error [message #307476 is a reply to message #307466] Wed, 19 March 2008 00:03 Go to previous messageGo to next message
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 #307493 is a reply to message #307476] Wed, 19 March 2008 00:37 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
My name is 'David'. It is the name that I place at the end of all of my posts.

What is the message that is being displayed?

David
Re: Logical error [message #307528 is a reply to message #307493] Wed, 19 March 2008 01:22 Go to previous messageGo to next message
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 #307532 is a reply to message #307528] Wed, 19 March 2008 01:41 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
The obvious answer is because the 'ic_record' was not found.

In your trigger there must be calls to various routines. One of these has 'ic_record_not_found' in it.

Search your code and then the packages for this phrase.

David
Re: Logical error [message #307537 is a reply to message #307528] Wed, 19 March 2008 01:44 Go to previous messageGo to next message
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 Go to previous message
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]
Previous Topic: no of rows in a block
Next Topic: How to send message to client with Oracle
Goto Forum:
  


Current Time: Thu Dec 08 00:31:34 CST 2016

Total time taken to generate the page: 0.12754 seconds