| LOOP [message #339865] |
Sat, 09 August 2008 01:45  |
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
|
Member |
|
|
Hi dear
i am getting problem in forms6i. i Have three block 1.asg_tas_temp,2.asg_tas_abs,3.asg_tas_emp_class.
i am getting data in asg_tas_temp in asg_tas_temp id employee is abesent then it also come in asg_tas_abs block but in this block i am not geeting all absent data only i am gettin last record data please help me.
i have written this code
DECLARE
m_code varchar2(6);
m_name varchar2 (30);
emp_code varchar2(6);
emp_name varchar2 (30);
abs_ecode varchar2(6);
abs_ename varchar2(30);
cursor empname is
select emp_comp_code,emp_code,emp_name
from asg_sm_emp_key
where emp_dept_code = :blk_para.tas_dept_code
and nvl(emp_end_of_service_yn,'N') = 'N'
order by to_number(emp_code);
cursor EXE_NAME is
select tas_exe_emp_code,tas_exe_emp_name
from asg_tas_emp_class
where tas_exe_dept_code =:blk_para.tas_dept_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(:blk_para.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(:blk_para.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_EMP_CLASS');
CLEAR_BLOCK(NO_VALIDATE);
IF EXE_NAME%ISOPEN THEN CLOSE EXE_NAME; END IF;
OPEN EXE_NAME;
LOOP
FETCH EXE_NAME INTO M_CODE,M_NAME;
EXIT WHEN EXE_NAME%NOTFOUND;
:ASG_TAS_EMP_CLASS.TAS_EXE_EMP_CODE := M_CODE;
:ASG_TAS_EMP_CLASS.TAS_EXE_EMP_NAME := M_NAME;
:ASG_TAS_EMP_CLASS.TAS_EXE_STATUS :='P';
GO_BLOCK('BLK_TAS_TEMP');
CLEAR_BLOCK(NO_VALIDATE);
GO_BLOCK('ASG_TAS_ABS');
CLEAR_BLOCK(NO_VALIDATE);
IF EMPNAME%ISOPEN THEN CLOSE EMPNAME; END IF;
OPEN EMPNAME;
LOOP
FETCH EMPNAME INTO EMPNAME_ROW;
EXIT WHEN EMPNAME%NOTFOUND;
EMP_CODE:= EMPNAME_ROW.EMP_CODE;
EMP_NAME:= EMPNAME_ROW.EMP_NAME;
IF EMP_CODE <> M_CODE THEN
:BLK_TAS_TEMP.TAS_TEMP_EMP_CODE:= EMPNAME_ROW.EMP_CODE;
:BLK_TAS_TEMP.TAS_TEMP_EMP_NAME:= EMPNAME_ROW.EMP_NAME;
END IF;
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
:BLK_TAS_TEMP.TAS_TEMP_MORNING_IN:=IN_TIME_ROW.PTIME;
ELSE
:BLK_TAS_TEMP.TAS_TEMP_LUNCH_OUT:=IN_TIME_ROW.PTIME;
END IF;
IF:BLK_TAS_TEMP.TAS_TEMP_LUNCH_OUT IS NULL THEN
:BLK_TAS_TEMP.TAS_TEMP_LUNCH_OUT:= '01:OO PM';
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) ='AM' AND OUT_TIME_ROW.FUNCTIONNO ='1' THEN
:BLK_TAS_TEMP.TAS_TEMP_POST_LUNCH_IN:=OUT_TIME_ROW.OTIME;
ELSE
:BLK_TAS_TEMP.TAS_TEMP_EVENING_OUT:=OUT_TIME_ROW.OTIME;
END IF;
IF :BLK_TAS_TEMP.TAS_TEMP_POST_LUNCH_IN IS NULL THEN
:BLK_TAS_TEMP.TAS_TEMP_POST_LUNCH_IN:='02:00 PM';
END IF;
END LOOP;
NEXT_RECORD;
CLOSE OUT_TIME;
END LOOP;
NEXT_RECORD;
CLOSE IN_TIME;
GO_BLOCK('BLK_TAS_TEMP');
FIRST_RECORD;
LOOP
IF :BLK_TAS_TEMP.TAS_TEMP_EMP_CODE IS NULL OR :BLK_TAS_TEMP.TAS_TEMP_EMP_NAME IS NULL THEN
CLEAR_RECORD;
END IF;
IF :BLK_TAS_TEMP.TAS_TEMP_MORNING_IN IS NULL AND :BLK_TAS_TEMP.TAS_TEMP_LUNCH_OUT IS NULL THEN
:BLK_TAS_TEMP.TAS_TEMP_STATUS :='A';
ELSE
:BLK_TAS_TEMP.TAS_TEMP_STATUS :='P';
END IF;
IF :BLK_TAS_TEMP.TAS_TEMP_STATUS ='A' THEN
ABS_ECODE:=:BLK_TAS_TEMP.TAS_TEMP_EMP_CODE;
ABS_ENAME:=:BLK_TAS_TEMP.TAS_TEMP_EMP_NAME;
END IF;
IF :BLK_TAS_TEMP.TAS_TEMP_STATUS ='A' THEN
:ASG_TAS_ABS.TAS_ABS_EMP_CODE:=ABS_ECODE;
:ASG_TAS_ABS.TAS_ABS_EMP_NAME:=ABS_ENAME;
END IF;
EXIT WHEN :SYSTEM.LAST_RECORD ='TRUE';
NEXT_RECORD;
END LOOP;
NEXT_RECORD;
END LOOP;
CLOSE EMPNAME;
NEXT_RECORD;
END LOOP;
CLOSE EXE_NAME;
FIRST_RECORD;
END;
still i am not getiing any ans from oracle guru.please give me ans .
thanks in advance
[Updated on: Sat, 09 August 2008 23:25] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
