CREATE OR REPLACE PROCEDURE HRIS_TRAVEL_REQUEST_PROC(
    P_TRAVEL_ID HRIS_EMPLOYEE_TRAVEL_REQUEST.TRAVEL_ID%TYPE,
    P_LINK_TO_SYNERGY CHAR := 'N' )
AS
  V_FROM_DATE HRIS_EMPLOYEE_TRAVEL_REQUEST.FROM_DATE%TYPE;
  V_TO_DATE HRIS_EMPLOYEE_TRAVEL_REQUEST.TO_DATE%TYPE;
  V_EMPLOYEE_ID HRIS_EMPLOYEE_TRAVEL_REQUEST.EMPLOYEE_ID%TYPE;
  V_STATUS HRIS_EMPLOYEE_TRAVEL_REQUEST.STATUS%TYPE;
  V_ADVANCE_AMOUNT HRIS_EMPLOYEE_TRAVEL_REQUEST.REQUESTED_AMOUNT%TYPE;
  V_EXPENSE_AMOUNT FLOAT;
  V_REQUEST_TYPE HRIS_EMPLOYEE_TRAVEL_REQUEST.REQUESTED_TYPE%TYPE;
  --
  V_LINK_TRAVEL_TO_SYNERGY HRIS_PREFERENCES.VALUE%TYPE;
  V_FORM_CODE HRIS_PREFERENCES.VALUE%TYPE;
  V_DR_ACC_CODE HRIS_PREFERENCES.VALUE%TYPE;
  V_CR_ACC_CODE HRIS_PREFERENCES.VALUE%TYPE;
  V_EXCESS_CR_ACC_CODE HRIS_PREFERENCES.VALUE%TYPE;
  V_LESS_DR_ACC_CODE HRIS_PREFERENCES.VALUE%TYPE;
  V_EQUAL_DR_ACC_CODE HRIS_PREFERENCES.VALUE%TYPE;
  --
  V_COMPANY_CODE VARCHAR2(255 BYTE);
  V_BRANCH_CODE  VARCHAR2(255 BYTE);
  V_CREATED_BY   VARCHAR2(255 BYTE):='ADMIN';
  V_VOUCHER_NO   VARCHAR2(255 BYTE);
BEGIN
  BEGIN
    SELECT TR.FROM_DATE ,
      TR.TO_DATE,
      TR.EMPLOYEE_ID,
      TR.STATUS,
      CASE WHEN TR.REQUESTED_AMOUNT IS NULL
      THEN 0
      ELSE TR.REQUESTED_AMOUNT
      END,
      TR.REQUESTED_TYPE,
      C.COMPANY_CODE,
      CASE WHEN E.EMPOWER_BRANCH_CODE IS NULL OR E.EMPOWER_BRANCH_CODE='-1'
      THEN C.COMPANY_CODE||'.01'
      ELSE E.EMPOWER_BRANCH_CODE
      END,
      C.LINK_TRAVEL_TO_SYNERGY,
      C.FORM_CODE,
      C.DR_ACC_CODE,
      C.CR_ACC_CODE,
      C.EXCESS_CR_ACC_CODE,
      C.LESS_DR_ACC_CODE,
      C.equal_cr_acc_code,
      E.FULL_NAME
    INTO V_FROM_DATE,
      V_TO_DATE,
      V_EMPLOYEE_ID,
      V_STATUS,
      V_ADVANCE_AMOUNT,
      V_REQUEST_TYPE,
      V_COMPANY_CODE,
      V_BRANCH_CODE,
      V_LINK_TRAVEL_TO_SYNERGY,
      V_FORM_CODE,
      V_DR_ACC_CODE,
      V_CR_ACC_CODE,
      V_EXCESS_CR_ACC_CODE,
      V_LESS_DR_ACC_CODE,
      v_equal_dr_acc_code,
      V_CREATED_BY
    FROM HRIS_EMPLOYEE_TRAVEL_REQUEST TR
    JOIN HRIS_EMPLOYEES E
    ON (TR.EMPLOYEE_ID = E.EMPLOYEE_ID )
    JOIN HRIS_COMPANY C
    ON (E.COMPANY_ID= C.COMPANY_ID)
    WHERE TRAVEL_ID =P_TRAVEL_ID;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT('NO DATA FOUND FOR ID =>'|| P_TRAVEL_ID);
    RETURN;
  END;
  --
  IF V_STATUS IN ('AP','C') AND V_FROM_DATE <TRUNC(SYSDATE) THEN
    HRIS_REATTENDANCE(V_FROM_DATE,V_EMPLOYEE_ID,V_TO_DATE);
  END IF;
  --
  IF(LOWER(V_REQUEST_TYPE) ='ep') THEN
    SELECT SUM(TOTAL_AMOUNT)
    INTO V_EXPENSE_AMOUNT
    FROM HRIS_EMP_TRAVEL_EXPENSE_DTL
    WHERE TRAVEL_ID = P_TRAVEL_ID;
  END IF;
  --
  IF V_LINK_TRAVEL_TO_SYNERGY = 'Y' AND P_LINK_TO_SYNERGY = 'Y' THEN
    SELECT FN_NEW_VOUCHER_NO(V_COMPANY_CODE,V_FORM_CODE,TRUNC(SYSDATE),'FA_DOUBLE_VOUCHER')
    INTO V_VOUCHER_NO
    FROM DUAL;
    IF LOWER(V_REQUEST_TYPE) = 'ad' THEN
      HRIS_TRAVEL_ADVANCE(V_COMPANY_CODE,V_FORM_CODE,TRUNC(SYSDATE),V_BRANCH_CODE,V_CREATED_BY,TRUNC(SYSDATE),V_DR_ACC_CODE,V_CR_ACC_CODE,'TRAVEL ADVANCE',V_ADVANCE_AMOUNT,'E'||V_EMPLOYEE_ID,V_VOUCHER_NO);
    END IF;
    IF (LOWER(V_REQUEST_TYPE) ='ep') AND (V_ADVANCE_AMOUNT = V_EXPENSE_AMOUNT) THEN
      HRIS_TRAVEL_SETTLEMENT_EQUAL(V_COMPANY_CODE,V_FORM_CODE,TRUNC(SYSDATE),V_BRANCH_CODE,V_CREATED_BY,TRUNC(SYSDATE),v_equal_dr_acc_code,V_DR_ACC_CODE,'TRAVEL ADVANCE',V_EXPENSE_AMOUNT,'E'||V_EMPLOYEE_ID,V_VOUCHER_NO);
    END IF;
    IF (LOWER(V_REQUEST_TYPE) ='ep') AND (V_ADVANCE_AMOUNT < V_EXPENSE_AMOUNT) THEN
      HRIS_TRAVEL_SETTLEMENT_EXCESS(V_COMPANY_CODE,V_FORM_CODE,TRUNC(SYSDATE),V_BRANCH_CODE,V_CREATED_BY,TRUNC(SYSDATE),v_equal_dr_acc_code,V_DR_ACC_CODE,V_EXCESS_CR_ACC_CODE,V_ADVANCE_AMOUNT,V_EXPENSE_AMOUNT-V_ADVANCE_AMOUNT,'TRAVEL ADVANCE','E'||V_EMPLOYEE_ID,V_VOUCHER_NO);
    END IF;
    IF (LOWER(V_REQUEST_TYPE) ='ep') AND (V_ADVANCE_AMOUNT > V_EXPENSE_AMOUNT) THEN
      HRIS_TRAVEL_SETTLEMENT_LESS(V_COMPANY_CODE,V_FORM_CODE,TRUNC(SYSDATE),V_BRANCH_CODE,V_CREATED_BY,TRUNC(SYSDATE),v_equal_dr_acc_code,V_LESS_DR_ACC_CODE,V_DR_ACC_CODE,V_ADVANCE_AMOUNT,V_ADVANCE_AMOUNT-V_EXPENSE_AMOUNT,'TRAVEL ADVANCE','E'||V_EMPLOYEE_ID,V_VOUCHER_NO);
    END IF;
    UPDATE HRIS_EMPLOYEE_TRAVEL_REQUEST
    SET VOUCHER_NO  = V_VOUCHER_NO
    WHERE TRAVEL_ID = P_TRAVEL_ID;
  END IF;
  --
END;
