CREATE OR REPLACE FUNCTION HRIS_IS_EMP_IN(
    P_EMPLOYEE_ID  NUMBER,
    P_TABLE_NAME   VARCHAR2,
    P_COLUMN_NAME  VARCHAR2,
    P_COLUMN_VALUE NUMBER )
  RETURN CHAR
AS
  V_COMPANY               VARCHAR2(4000 BYTE);
  V_BRANCH                VARCHAR2(4000 BYTE);
  V_DEPARTMENT            VARCHAR2(4000 BYTE);
  V_DESIGNATION           VARCHAR2(4000 BYTE);
  V_POSITION              VARCHAR2(4000 BYTE);
  V_SERVICE_TYPE          VARCHAR2(4000 BYTE);
  V_EMPLOYEE_TYPE         VARCHAR2(4000 BYTE);
  V_GENDER                VARCHAR2(4000 BYTE);
  V_EMPLOYEE              VARCHAR2(4000 BYTE);
  V_COND_COMPANY          VARCHAR2(3 BYTE);
  V_COND_BRANCH           VARCHAR2(3 BYTE);
  V_COND_DEPARTMENT       VARCHAR2(3 BYTE);
  V_COND_DESIGNATION      VARCHAR2(3 BYTE);
  V_COND_POSITION         VARCHAR2(3 BYTE);
  V_COND_SERVICE_TYPE     VARCHAR2(3 BYTE);
  V_COND_EMPLOYEE_TYPE    VARCHAR2(3 BYTE);
  V_COND_GENDER           VARCHAR2(3 BYTE);
  V_COND_EMPLOYEE         VARCHAR2(3 BYTE);
  V_IN_OPER_COMPANY       VARCHAR2(6 BYTE);
  V_IN_OPER_BRANCH        VARCHAR2(6 BYTE);
  V_IN_OPER_DEPARTMENT    VARCHAR2(6 BYTE);
  V_IN_OPER_DESIGNATION   VARCHAR2(6 BYTE);
  V_IN_OPER_POSITION      VARCHAR2(6 BYTE);
  V_IN_OPER_SERVICE_TYPE  VARCHAR2(6 BYTE);
  V_IN_OPER_EMPLOYEE_TYPE VARCHAR2(6 BYTE);
  V_IN_OPER_GENDER        VARCHAR2(6 BYTE);
  V_IN_OPER_EMPLOYEE      VARCHAR2(6 BYTE);
  V_WHERE_CLAUSE          VARCHAR2(4000 BYTE);
  V_QUERY_FIRST           VARCHAR2(4000 BYTE);
  V_QUERY_SECOND          VARCHAR2(4000 BYTE);
  V_RETURN                CHAR(1 BYTE);
BEGIN
  V_QUERY_FIRST :='SELECT COMPANY_ID,BRANCH_ID,DEPARTMENT_ID,DESIGNATION_ID,POSITION_ID,SERVICE_TYPE_ID,EMPLOYEE_TYPE,GENDER_ID,EMPLOYEE_ID,COND_COMPANY_ID,COND_BRANCH_ID,COND_DEPARTMENT_ID,COND_DESIGNATION_ID,COND_POSITION_ID,COND_SERVICE_TYPE_ID,COND_EMPLOYEE_TYPE,COND_GENDER_ID,COND_EMPLOYEE_ID,IN_OPER_COMPANY_ID,IN_OPER_BRANCH_ID,IN_OPER_DEPARTMENT_ID,IN_OPER_DESIGNATION_ID,IN_OPER_POSITION_ID,IN_OPER_SERVICE_TYPE_ID,IN_OPER_EMPLOYEE_TYPE,IN_OPER_GENDER_ID,IN_OPER_EMPLOYEE_ID,WHERE_CLAUSE FROM '||P_TABLE_NAME||' WHERE '||P_COLUMN_NAME||' ='||P_COLUMN_VALUE;
  EXECUTE IMMEDIATE V_QUERY_FIRST INTO V_COMPANY, V_BRANCH, V_DEPARTMENT, V_DESIGNATION, V_POSITION, V_SERVICE_TYPE, V_EMPLOYEE_TYPE, V_GENDER, V_EMPLOYEE,V_COND_COMPANY, V_COND_BRANCH, V_COND_DEPARTMENT, V_COND_DESIGNATION, V_COND_POSITION, V_COND_SERVICE_TYPE, V_COND_EMPLOYEE_TYPE, V_COND_GENDER, V_COND_EMPLOYEE,V_IN_OPER_COMPANY, V_IN_OPER_BRANCH, V_IN_OPER_DEPARTMENT, V_IN_OPER_DESIGNATION, V_IN_OPER_POSITION, V_IN_OPER_SERVICE_TYPE, V_IN_OPER_EMPLOYEE_TYPE, V_IN_OPER_GENDER, V_IN_OPER_EMPLOYEE,V_WHERE_CLAUSE;
  IF V_WHERE_CLAUSE IS NOT NULL THEN
    V_QUERY_SECOND  :='SELECT EMPLOYEE_ID FROM HRIS_EMPLOYEES WHERE '||V_WHERE_CLAUSE;
  ELSE
    V_QUERY_SECOND   :='SELECT EMPLOYEE_ID FROM HRIS_EMPLOYEES WHERE 1=1';
    IF(V_COMPANY     IS NOT NULL) THEN
      V_QUERY_SECOND := V_QUERY_SECOND||' '||V_COND_COMPANY||' COMPANY_ID '||V_IN_OPER_COMPANY||' ('||V_COMPANY||')';
    END IF;
    IF(V_BRANCH      IS NOT NULL) THEN
      V_QUERY_SECOND := V_QUERY_SECOND||' '||V_COND_BRANCH||' BRANCH_ID '||V_IN_OPER_BRANCH||' ('||V_BRANCH||')';
    END IF;
    IF(V_DEPARTMENT  IS NOT NULL) THEN
      V_QUERY_SECOND := V_QUERY_SECOND||' '||V_COND_DEPARTMENT||' DEPARTMENT_ID '||V_IN_OPER_DEPARTMENT||' ('||V_DEPARTMENT||')';
    END IF;
    IF(V_DESIGNATION IS NOT NULL) THEN
      V_QUERY_SECOND := V_QUERY_SECOND||' '||V_COND_DESIGNATION||' DESIGNATION_ID '||V_IN_OPER_DESIGNATION||' ('||V_DESIGNATION||')';
    END IF;
    IF(V_POSITION    IS NOT NULL) THEN
      V_QUERY_SECOND := V_QUERY_SECOND||' '||V_COND_POSITION||' POSITION_ID '||V_IN_OPER_POSITION||' ('||V_POSITION||')';
    END IF;
    IF(V_SERVICE_TYPE IS NOT NULL) THEN
      V_QUERY_SECOND  := V_QUERY_SECOND||' '||V_COND_SERVICE_TYPE||' SERVICE_TYPE_ID '||V_IN_OPER_SERVICE_TYPE||' ('||V_SERVICE_TYPE||')';
    END IF;
    IF(V_EMPLOYEE_TYPE IS NOT NULL) THEN
      V_QUERY_SECOND   := V_QUERY_SECOND||' '||V_COND_EMPLOYEE_TYPE||' EMPLOYEE_TYPE '||V_IN_OPER_EMPLOYEE_TYPE||' ('||V_EMPLOYEE_TYPE||')';
    END IF;
    IF(V_GENDER      IS NOT NULL) THEN
      V_QUERY_SECOND := V_QUERY_SECOND||' '||V_COND_GENDER||' GENDER_ID '||V_IN_OPER_GENDER||' ('||V_GENDER||')';
    END IF;
    IF(V_EMPLOYEE    IS NOT NULL) THEN
      V_QUERY_SECOND := V_QUERY_SECOND||' '||V_COND_EMPLOYEE||' EMPLOYEE_ID '||V_IN_OPER_EMPLOYEE||' ('||V_EMPLOYEE||')';
    END IF;
  END IF;
EXECUTE IMMEDIATE 'SELECT (CASE WHEN COUNT(*) >0 THEN  ''Y'' ELSE ''N'' END) FROM ('||V_QUERY_SECOND||') WHERE EMPLOYEE_ID = '||P_EMPLOYEE_ID INTO V_RETURN;
RETURN V_RETURN;
END;