select FNC_EMPLOYEE_INFO (EMP_NO,'EUID') EMPLOYEE_ID

,FNC_EMPLOYEE_INFO (EMP_NO,'NAME') NAME_OF_EMPLOYEE

,FNC_EMPLOYEE_INFO (EMP_NO,'JDATE') JOINING_DATE
, (SELECT NVL(BASIC,0) FROM HRM_EMP_SALARY_SETUP where EMPLOYEE_NO=E.EMPLOYEE_NO and nvl(ACTIVE_STATUS,'N')='Y' AND company_no = E.company_no) OLD_BASIC_SALARY
, (SELECT nvl(BASIC,0) + nvl(HOUSE_RENT,0) + nvl(MEDICAL_ALLOW,0) + nvl(CONVEYANCE_ALLOW,0) + nvl(SPECIAL_ALLOW,0) + nvl(MOBILE_BILL,0) + nvl(ENTERTAIN_ALLOW,0)
 + nvl(CITY_ALLOW,0) + nvl(TAX_COMPANY,0) + nvl(MODEM_BILL,0) + nvl(LUNCH_BILL,0) + nvl(CAR_ALLOW,0) + nvl(BIKE_ALLOW,0) + nvl(FUEL_BILL,0) + nvl(CAR_INS_FIT,0)
 + nvl(TRANSPORT_ALLOW,0) + nvl(DAILY_ALLOW,0) + nvl(UTILITY,0) + nvl(SITE_ALLOW,0) + nvl(EXTRA_ALLOW,0) + nvl(MAINTAN_BILL,0)
 + nvl(DRIVER_SAL,0) + nvl(TIFFIN_BILL,0) + nvl(DINNER_BILL,0) + nvl(NIGHT_BILL,0) + nvl(ATTENDANCE_BONUS,0) + nvl(OT_HONORIUM,0) + nvl(SHOE_CLEANING_BILL,0)
 --+ nvl(REV_STAMP,0) + nvl(TAX_EMPLOYEE,0)

 OLD_GROSS_SALARY
 FROM HRM_EMP_SALARY_SETUP H  where EMPLOYEE_NO=E.EMPLOYEE_NO and nvl(ACTIVE_STATUS,'N')='Y' AND company_no = E.COMPANY_NO) OLD_GROSS_SALARY
, APPRAISAL_TYPE
,PROMOTION_AMOUNT AS INCREMENT_AMOUNT
,PROMOTED_GRADE AS NEW_GRADE
,(select GRADE_NAME from HRM_SALARY_SCALE_MST where   nvl(company_no,0)= AP.COMPANY_NO AND SALARY_NO =AP.PROMOTED_GRADE) NEW_GRADE2
,PROMOTED_DESIGNATION AS NEW_DESIGNATION
,(SELECT  DESIGNATION_NAME FROM HRM_DESIGNATION where   nvl(company_no,0)= AP.COMPANY_NO AND DESIGNATION_NO =AP.PROMOTED_DESIGNATION) NEW_DESIGNATION2
 from HRM_EMP_APPRAISAL AP , HRM_EMPLOYEE_PROFILE E
WHERE AP.EMP_NO = E.EMPLOYEE_NO