daogu

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

---

Answer:Populate block using cursor

http://www.orafaq.com/forum/m/605/67467/?srch=populate block cursor create_record#msg_605

and my feelings about it.

http://www.orafaq.com/forum/m/134237/67467/?srch=populate block cursor create_record#msg_134237

Another one of my rants.

http://www.orafaq.com/forum/mv/msg/74947/213213/67467/#msg_213213

Just use ‘master-detail’ and let Oracle Forms do the work for you.

Also PLEASE use ‘code’ tags when posting structured text.

David

Related posts:

1. calculate the time Dear All I am getting a problem in forms...
2. Loop Problem Dear All I have a sno (seriel no) column...
3. Problem in loop to search values I have a problem the scene is I have...
4. cursor Dear All, I am displaying multiple records by fetching...
5. FRM-40102: Record must be entered or deleted first Any ideas why am I getting following error message...