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;