Home » Developer & Programmer » Forms » problem in loop
problem in loop [message #338433] Tue, 05 August 2008 01:30 Go to next message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
Dear all i am getting problem when i am using loop in form 6i ig is giving result but in first block data comming exact but in second block only first record is comming i have written these code please help me
declare

		cursor empname is
				select emp_comp_code,emp_code,emp_name
				from asg_sm_emp_key
				where emp_dept_code = :asg_ngac_log.dept_code
				and nvl(emp_end_of_service_yn,'N') = 'N'
				order by to_number(emp_code);			
	
		cursor in_time(empno varchar2) is
				select logindex,logtime,functionno,authtype,to_char(logtime,'HH:MI AM')PTIME
				from asg_ngac_log
				where to_number(substr(userid,1,4)) = to_number(empno)
				and to_char(logtime,'DD/MM/YYYY') = to_char(:asg_ngac_log.dt_tas_date,'DD/MM/YYYY')
				and to_char(logtime,'hh24:mi:ss') between '07:30:25' and '13:10:00'
				order by logtime asc;
				
		cursor out_time(empno varchar2) is
				select logindex,logtime,functionno,authtype,to_char(logtime,'HH:MI AM')OTIME
				from asg_ngac_log
				where to_number(substr(userid,1,4)) = to_number(empno)
				and to_char(logtime,'DD/MM/YYYY') = to_char(:asg_ngac_log.dt_tas_date,'DD/MM/YYYY')
				and to_char(logtime,'hh24:mi:ss') between '13:40:25' and '18:10:00'
				order by logtime asc;
				EMPNAME_ROW EMPNAME%ROWTYPE;
				IN_TIME_ROW IN_TIME%ROWTYPE;
				OUT_TIME_ROW OUT_TIME%ROWTYPE;
begin
		go_block('asg_tas');
		clear_block(NO_VALIDATE);
		if empname%isopen then close empname; end if;
		OPEN EMPNAME;
		LOOP
		FETCH EMPNAME INTO EMPNAME_ROW; 
		EXIT WHEN EMPNAME%NOTFOUND;
				:asg_tas.tas_emp_code:=EMPNAME_ROW.emp_code;
				:asg_tas.tas_emp_name:=EMPNAME_ROW.emp_name;
				
		if in_time%isopen then close in_time; end if;
		OPEN IN_TIME(EMPNAME_ROW.EMP_CODE);
		LOOP
			FETCH IN_TIME INTO IN_TIME_ROW;
			EXIT WHEN IN_TIME%NOTFOUND;
			  if substr(IN_TIME_ROW.PTIME,7,2) = 'AM' and IN_TIME_ROW.functionno = '1'then 
				:asg_tas.tas_morning_in:=IN_TIME_ROW.PTIME;
		else
				:asg_tas.tas_lunch_out:=IN_TIME_ROW.PTIME;
				end if;
				if :asg_tas.tas_morning_in is null or :asg_tas.tas_lunch_out is null  then 
				:asg_tas.tas_tot_morning := '5.00';
		else
				:asg_tas.tas_tot_morning:=(to_date(:asg_tas.tas_lunch_out,'HH:MI AM')-to_date(:asg_tas.tas_morning_in,'HH:MI AM'))*24 ;
				end if;		 	
				 
		if out_time%isopen then close out_time; end if;
		OPEN OUT_TIME(EMPNAME_ROW.EMP_CODE);
		LOOP
			FETCH OUT_TIME INTO OUT_TIME_ROW;
			EXIT WHEN OUT_TIME%NOTFOUND;
				if substr(OUT_TIME_ROW.OTIME,7,2) = 'PM' and OUT_TIME_ROW.functionno = '1'then 
				:asg_tas.tas_post_lunch_in:=OUT_TIME_ROW.OTIME;
			else
				:asg_tas.tas_evening_out:=OUT_TIME_ROW.OTIME;
			end if;
				if :asg_tas.tas_post_lunch_in is null or :asg_tas.tas_evening_out is null then
					 :asg_tas.tas_tot_evening:= '4.00';
			else
				   :asg_tas.tas_tot_evening:=(to_date(:asg_tas.tas_evening_out,'HH:MI AM')-to_date(:asg_tas.tas_post_lunch_in,'HH:MI AM'))*24 ;
				end if;	
		
		end loop;
		CLOSE OUT_TIME;
	
		end loop;
		CLOSE IN_TIME;
			go_block('asg_tas');
			first_record;
			loop
					if  :asg_tas.tas_morning_in is null and :asg_tas.tas_lunch_out is null  then 
					
						 	:ASG_TAS.TAS_PSTATUS :='A';					
					else 
							
						:ASG_TAS.TAS_PSTATUS :='P';							
					end if;
			exit when :system.last_record = 'TRUE';
			next_record;
			end loop;
			next_record;
			end loop;
			CLOSE EMPNAME;								
			first_record;
		
		IF EMPNAME%ISOPEN THEN CLOSE EMPNAME; END IF;
		OPEN EMPNAME;
		LOOP
		FETCH EMPNAME INTO EMPNAME_ROW;
		EXIT WHEN EMPNAME%NOTFOUND;
		GO_BLOCK('ASG_TAS');
		FIRST_RECORD;
		IF :ASG_TAS.TAS_PSTATUS = 'A' THEN
	
			CLEAR_RECORD;
			
			GO_BLOCK('ASG_TAS_ABS');
			FIRST_RECORD;
			CLEAR_BLOCK(NO_VALIDATE);
			:ASG_TAS_ABS.ASG_TAS_EMP_CODE := EMPNAME_ROW.EMP_CODE;
			:ASG_TAS_ABS.ASG_TAS_EMP_NAME := EMPNAME_ROW.EMP_NAME;
			:ASG_TAS_ABS.ASG_TAS_STATUS := 'A';
		END IF;
		NEXT_RECORD;
		END LOOP;
		CLOSE EMPNAME;
		FIRST_RECORD;
		
end;

[EDITED by LF: added [code] tags]

[Updated on: Tue, 05 August 2008 04:11] by Moderator

Report message to a moderator

Re: problem in loop [message #338450 is a reply to message #338433] Tue, 05 August 2008 02:31 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Read the OraFAQ Forum Guide before posting.

Why have you posted this in the Unix section....
Re: problem in loop [message #338474 is a reply to message #338450] Tue, 05 August 2008 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And why was is moves to Sql/Plsql.....

Booting it onwards to Forms.
Re: problem in loop [message #338531 is a reply to message #338450] Tue, 05 August 2008 05:22 Go to previous messageGo to next message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
sory for this and thanks
Re: problem in loop [message #338617 is a reply to message #338433] Tue, 05 August 2008 08:24 Go to previous messageGo to next message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
Dear all still i am getting same problem
please help me out of this problem
Re: problem in loop [message #339600 is a reply to message #338617] Thu, 07 August 2008 21:35 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Just base your block on the tables concerned. Forget about using cursors.

David
Previous Topic: how to close a window in form (urgent)
Next Topic: populate radio group
Goto Forum:
  


Current Time: Wed Dec 07 18:27:44 CST 2016

Total time taken to generate the page: 0.05379 seconds