BEGIN
DECLARE
L_NO NUMBER;
BEGIN
SELECT COUNT(EMPCODE) INTO L_NO FROM ADJFSCHDR WHERE EMPCODE=:adjfschdr.empcode;
IF L_NO=0 THEN 
 SELECT BILLUNIT,EMPNAME,RLYJOINDATE,PAYRATE,SCALECODE,DESIGNATION 
 into :adjfschdr.billunit,:adjfschdr.empname,:adjfschdr.dateofappointment,:adjfschdr.basicrate,
      :adjfschdr.scalecode,:adjfschdr.designation
 FROM AFPBLEMP
  WHERE EMPNO=:adjfschdr.empcode
  AND PAYBILLID IN (SELECT MAX(PAYBILLID) FROM AFPBLEMP
  WHERE EMPNO=:adjfschdr.empcode AND PAYBILLID LIKE '200%');

BEGIN
   select deptcode,birthdate,ticketno,empgroup
   into :adjfschdr.department,:adjfschdr.dateofbirth,:adjfschdr.staffno,
   :adjfschdr.empgroup
   from prmaemp 
   WHERE EMPno=:adjfschdr.empcode;
       EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    NULL;
END;
   SELECT SHORTDESC INTO 
   :adjfschdr.department
   from afgencod
   where codetype='DT'
   AND CODE=:adjfschdr.department;
ELSE
SELECT billunit, empcode, staffno, empname, department,
       dateofappointment, basicrate, scalecode, designation,
       dateofbirth, dateofsecondment, yearsofservice, officeorder,
       corporatebody, dateofrepatriation,empgroup
  INTO :adjfschdr.billunit, :adjfschdr.empcode, :adjfschdr.staffno, :adjfschdr.empname, 
       :adjfschdr.department,:adjfschdr.dateofappointment, :adjfschdr.basicrate,
       :adjfschdr.scalecode, :adjfschdr.designation,
       :adjfschdr.dateofbirth, :adjfschdr.dateofsecondment, :adjfschdr.yearsofservice,
       :adjfschdr.officeorder,:adjfschdr.corporatebody, :adjfschdr.dateofrepatriation,
       :adjfschdr.empgroup
  FROM adjfschdr
  WHERE EMPCODE=:adjfschdr.empcode;
END IF;
     :adjfschdr.yearsofservice:=round(MONTHS_BETWEEN( sysdate, :adjfschdr.dateofappointment) / 12,1);
END;
END;