LOGOUT ;
        LOGON ('FIFES_HN','FIFES_HN@PRD_253');

	MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));
        MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));
	MESSAGE (:DAILY_TRX_CONT.ORG_ID);
	MESSAGE (:DAILY_TRX_CONT.ORG_ID);
	IF (:DAILY_TRX_CONT.ORG_ID = '1000') THEN
			LOGOUT ;
			LOGON ('FIFES_HN','FIFES_HN@PRD_253');
			-- ASSUIT	
	ELSIF (:DAILY_TRX_CONT.ORG_ID = '1490') THEN
			LOGOUT ;
			LOGON ('FIFES_HN','FIFES_HN@PRD_253');
			
	ELSIF (:DAILY_TRX_CONT.ORG_ID = '1491') THEN
	
			LOGOUT ;
			LOGON ('SIFESD','SIFESD@PRD_253');
			
		END IF;
		 MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));
		 MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));

FUNCTION CHANGE_CONNECTION (
  v_conHandle IN OUT EXEC_SQL.ConnType,
  v_conInfo IN OUT VARCHAR2,
  v_userName IN VARCHAR2 DEFAULT NULL,
  v_password IN VARCHAR2 DEFAULT NULL,
  v_datasource IN VARCHAR2 DEFAULT NULL
) RETURN BOOLEAN IS

  -- This procedure tries to open a connection to
  -- v_username/v_password@v_datasource.  If it succeeds,
  -- it disconnects from the connection on v_conHandle (if there
  -- is one), and sets v_conHandle to point to the new
  -- connection.  If the user hits Cancel on the LOGON_SCREEN
  -- dialog, this function silently returns with the same connection.
  -- If it fails to log on, it will display an alert and bring you
  -- back to the LOGON screen.  You get a maximum of

  c_attempts CONSTANT INTEGER := 3;

  -- attempts to log on.  If after all attempts, you still fail, the
  -- function returns with the same connection handle as it was
  -- passed.
  -- If the connection is changed, v_conInfo will contain
  -- the string v_userName@v_datasource, or if datasource is NULL,
  -- v_userName@(Oracle)

  un VARCHAR2(80);
  pw VARCHAR2(80);
  cn VARCHAR2(80);
  tmpConInfo VARCHAR2(255);
  tmpConHandle EXEC_SQL.ConnType;
  logonSuccessful BOOLEAN := FALSE;

BEGIN
  FOR i IN 1..c_attempts LOOP
    IF v_username is not null or
       v_password is not null or
       v_datasource is not null THEN
      IF i > 1 THEN
        -- Only one attempt if the username/password is specified
        EXIT;
      END IF;
      un := v_username;
      pw := v_password;
      cn := v_datasource;
    ELSE
      LOGON_SCREEN;
      Get_Connect_Info ( un, pw, cn );
    END IF;
    IF ((un is null) and (pw is null) and (cn is null)) THEN
      -- We assume this is cancel, as we have no other way
      -- of telling.
      EXIT;
    END IF;
    BEGIN
      tmpConHandle := EXEC_SQL.OPEN_CONNECTION(un, pw, cn);
    EXCEPTION
    WHEN EXEC_SQL.PACKAGE_ERROR THEN
      -- We failed to log on.
      DECLARE
        tmpAlert PLS_INTEGER;
      BEGIN
        tmpAlert := SHOW_ALERT('LOGON_FAILED');
      END;
    END;
    IF EXEC_SQL.VALID_CONNECTION(tmpConHandle) THEN
      -- We succeeded in connection.  Disconnect old handle,
      -- and set all output variables.
      IF cn IS NULL THEN
        cn := '(Oracle)';
      END IF;
      EXEC_SQL.CLOSE_CONNECTION(v_conHandle);
      v_conHandle := tmpConHandle;
      v_conInfo := un || '@' || cn;
      logonSuccessful := TRUE;
      -- And break the loop.
      EXIT;
    END IF;
  END LOOP;
  IF logonSuccessful THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;

--------------------------------------------------
PACKAGE CON1 IS
  -- This package spec is used to contain global variables related
  -- to the primary connection.  In particular, we stash the
  -- username, password and connection string used on the
  -- primary connection.
  un VARCHAR2(80);
  pw VARCHAR2(80);
  cn VARCHAR2(80);
  PROCEDURE STASH_CURRCONN;
END CON1;

-------------------------

PACKAGE BODY CON1 IS
  PROCEDURE STASH_CURRCONN IS
  BEGIN
    un := GET_APPLICATION_PROPERTY(USERNAME);
    pw := GET_APPLICATION_PROPERTY(PASSWORD);
    cn := GET_APPLICATION_PROPERTY(CONNECT_STRING);
    IF cn IS NULL THEN
       :control.con1_connected_as := un || '@(Oracle)';
    ELSE
       :control.con1_connected_as := un || '@' || cn;
    END IF;
  END STASH_CURRCONN;
END;
--------------------
PACKAGE CON2 IS
  -- This package contains global variables used by the second
  -- Connection block.  We use packages so we can maintain variables
  -- that are global to this form, but are not of type CHAR (:GLOBAL
  -- variables are restricted to CHAR types).

  conHandle EXEC_SQL.ConnType;
  selectCursor EXEC_SQL.CursType;
  updateCursor EXEC_SQL.CursType;
  deleteCursor EXEC_SQL.CursType;
  insertCursor EXEC_SQL.CursType;
  commitCursor EXEC_SQL.CursType;
  doneFetching BOOLEAN := FALSE;

  PROCEDURE NEW_CONNECT (
    copyConnect BOOLEAN DEFAULT FALSE -- If set to true, then con2
    -- CONNECT connects to the same datasource as the primary forms
    -- connection.
  );

  -- Commits the current connection
  PROCEDURE DO_COMMIT( rollback IN BOOLEAN DEFAULT FALSE);

  -- Called to delete each record
  PROCEDURE DO_DELETE;

  -- Fetches N records into the block
  PROCEDURE DO_FETCH;

  -- Inserts a records into the database
  PROCEDURE DO_INSERT;

  -- Executes a select statement against the database
  PROCEDURE DO_SELECT;

  -- Updates records in the database
  PROCEDURE DO_UPDATE;

END;
-------------------------

PACKAGE BODY CON2 IS
  PROCEDURE NEW_CONNECT (
    copyConnect BOOLEAN DEFAULT FALSE -- If set to true, then con2
    -- CONNECT connects to the same datasource as the primary forms
    -- connection.
  ) IS
   connectStatus BOOLEAN;
  BEGIN
    -- This procedure connects the con2 connection handle to a 
    -- datasource.
    IF copyConnect THEN
      connectStatus := CHANGE_CONNECTION(con2.conHandle,
        :control.con2_connected_as,
        con1.un, con1.pw, con1.cn);
    ELSE
      connectStatus := CHANGE_CONNECTION(con2.conHandle,
        :control.con2_connected_as,
        '','','');
    END IF;

    IF NOT connectStatus THEN
      -- Logon failed
      IF NOT EXEC_SQL.VALID_CONNECTION(conHandle) THEN
        :control.con2_connected_as := 'Not Connected';
      END IF;
    ELSE
      -- Logon succeeded.
      IF NOT copyConnect THEN
        -- This was a user-selected logon.
        GO_BLOCK('CON2');
        CLEAR_BLOCK;
      END IF;
    END IF;
  END NEW_CONNECT;

  PROCEDURE DO_COMMIT( rollback IN BOOLEAN DEFAULT FALSE) IS
    retVal PLS_INTEGER;
  BEGIN
    IF NOT EXEC_SQL.IS_OPEN(conHandle, commitCursor) THEN
      commitCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
      EXEC_SQL.PARSE(conHandle, commitCursor, 'commit');
    END IF;
    retVal := EXEC_SQL.EXECUTE(conHandle, commitCursor);
  EXCEPTION
    WHEN EXEC_SQL.PACKAGE_ERROR THEN
      HANDLE_ERROR(conHandle);
  END DO_COMMIT;

  PROCEDURE DO_DELETE IS
    retVal PLS_INTEGER;
  BEGIN
    IF NOT EXEC_SQL.IS_OPEN(conHandle, deleteCursor) THEN
      deleteCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
      EXEC_SQL.PARSE(conHandle, deleteCursor,
       'delete from emp where empno = :empno');
    END IF;
    -- Bind the variables.
    EXEC_SQL.BIND_VARIABLE(conHandle, deleteCursor,
     ':empno', :CON2.EMPNO);
    -- And execute.
    retVal := EXEC_SQL.EXECUTE(conHandle, deleteCursor);
  EXCEPTION
    WHEN EXEC_SQL.PACKAGE_ERROR THEN
      HANDLE_ERROR(conHandle);
  END DO_DELETE;

  PROCEDURE DO_FETCH IS
    fetchCode PLS_INTEGER;
  BEGIN
    FOR i IN 1..TO_NUMBER(GET_BLOCK_PROPERTY('CON2', RECORDS_TO_FETCH)) LOOP
      IF NOT doneFetching THEN
        fetchCode := EXEC_SQL.FETCH_ROWS(conHandle, selectCursor);
        IF fetchCode > 0 THEN
          -- We have values to retrieve.  Do so.
          CREATE_QUERIED_RECORD;
          EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
          1, :CON2.EMPNO);
          EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
          2, :CON2.ENAME);
          EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
          3, :CON2.MGR);
          EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
          4, :CON2.JOB);
          EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
          5, :CON2.HIREDATE);
          EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
          6, :CON2.SAL);
          EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
          7, :CON2.COMM);
          EXEC_SQL.COLUMN_VALUE(conHandle, selectCursor,
          8, :CON2.DEPTNO);
        ELSE
          doneFetching := TRUE;
        END IF;
      END IF;
    END LOOP;
  EXCEPTION
    WHEN EXEC_SQL.PACKAGE_ERROR THEN
      HANDLE_ERROR(conHandle);
  END DO_FETCH;

  PROCEDURE DO_INSERT IS
    retVal PLS_INTEGER;
  BEGIN
    IF NOT EXEC_SQL.IS_OPEN(conHandle, insertCursor) THEN
      insertCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
      EXEC_SQL.PARSE(conHandle, insertCursor,
       'insert into emp (empno, ename, job, mgr, hiredate, sal,
        comm, deptno) values (:empno, :ename, :job,
        :mgr, :hiredate, :sal, :comm, :deptno)');
    END IF;
    -- Bind the variables.
    EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
     ':empno', :CON2.EMPNO);
    EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
     ':ename', :CON2.ENAME);
    EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
     ':job', :CON2.JOB);
    EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
     ':mgr', :CON2.MGR);
    EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
     ':hiredate', :CON2.hiredate);
    EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
     ':sal', :CON2.sal);
    EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
     ':comm', :CON2.comm);
    EXEC_SQL.BIND_VARIABLE(conHandle, insertCursor,
     ':deptno', :CON2.deptno);
  
    -- And execute.
    retVal := EXEC_SQL.EXECUTE(conHandle, insertCursor);
  EXCEPTION
    WHEN EXEC_SQL.PACKAGE_ERROR THEN
      HANDLE_ERROR(conHandle);
  END DO_INSERT;

  PROCEDURE DO_SELECT IS
    numRows PLS_INTEGER;
  BEGIN
    IF NOT EXEC_SQL.IS_OPEN(conHandle, selectCursor) THEN
      -- We need to open the cursor.
      selectCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
      EXEC_SQL.PARSE(conHandle, selectCursor,
         'select empno, ename, mgr, job, hiredate, sal, comm, deptno from emp');
      -- And define all the columns we need.
      EXEC_SQL.DEFINE_COLUMN(conHandle,  selectCursor,
         1, :CON2.EMPNO);
      EXEC_SQL.DEFINE_COLUMN(conHandle,  selectCursor,
         2, :CON2.ENAME, 30);
      EXEC_SQL.DEFINE_COLUMN(conHandle,  selectCursor,
         3, :CON2.MGR);
      EXEC_SQL.DEFINE_COLUMN(conHandle,  selectCursor,
         4, :CON2.JOB, 30);
      EXEC_SQL.DEFINE_COLUMN(conHandle,  selectCursor,
         5, :CON2.HIREDATE);
      EXEC_SQL.DEFINE_COLUMN(conHandle,  selectCursor,
         6, :CON2.SAL);
      EXEC_SQL.DEFINE_COLUMN(conHandle,  selectCursor,
         7, :CON2.COMM);
      EXEC_SQL.DEFINE_COLUMN(conHandle,  selectCursor,
         8, :CON2.DEPTNO);
    END IF;
    doneFetching := FALSE;
    numRows := EXEC_SQL.EXECUTE(conHandle, selectCursor);
  EXCEPTION
    WHEN EXEC_SQL.PACKAGE_ERROR THEN
      HANDLE_ERROR(conHandle);
  END DO_SELECT;

  PROCEDURE DO_UPDATE IS
    retVal PLS_INTEGER;
    oldEmpno NUMBER;
  BEGIN
    IF NOT EXEC_SQL.IS_OPEN(conHandle, updateCursor) THEN
      updateCursor := EXEC_SQL.OPEN_CURSOR(conHandle);
      EXEC_SQL.PARSE(conHandle, updateCursor,
       'update emp set empno = :empno, ename = :ename, job = :job,
        mgr = :mgr, hiredate = :hiredate, sal = :sal, comm = :comm,
        deptno = :deptno where empno = :key');
    END IF;
    -- Bind the variables.
    EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
     ':empno', :CON2.EMPNO);
    EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
     ':ename', :CON2.ENAME);
    EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
     ':job', :CON2.JOB);
    EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
     ':mgr', :CON2.MGR);
    EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
     ':hiredate', :CON2.hiredate);
    EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
     ':sal', :CON2.sal);
    EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
     ':comm', :CON2.comm);
    EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
     ':deptno', :CON2.deptno);
    EXEC_SQL.BIND_VARIABLE(conHandle, updateCursor,
     ':key', TO_NUMBER(GET_ITEM_PROPERTY('CON2.EMPNO',
         DATABASE_VALUE)));
  
    -- And execute.
    retVal := EXEC_SQL.EXECUTE(conHandle, updateCursor);
  EXCEPTION
    WHEN EXEC_SQL.PACKAGE_ERROR THEN
      HANDLE_ERROR(conHandle);
  END DO_UPDATE;

END CON2;

----------------------------------------------
PROCEDURE DROP_CONNECTION (
  conHandle IN OUT EXEC_SQL.ConnType
) IS
BEGIN
  -- This procedure disconnects from the datasource we connected
  -- to via EXEC_SQL.
  EXEC_SQL.CLOSE_CONNECTION(conHandle);
END;
-----------------------------
PROCEDURE Get_Connect_Info (
  un OUT VARCHAR2,
  pw OUT VARCHAR2,
  cn OUT VARCHAR2
) IS
BEGIN
  un := GET_APPLICATION_PROPERTY(USERNAME);
  pw := GET_APPLICATION_PROPERTY(PASSWORD);
  cn := GET_APPLICATION_PROPERTY(CONNECT_STRING);
END;
------------------------------

PROCEDURE HANDLE_ERROR (
  v_conHandle IN EXEC_SQL.ConnType
) IS
  alert_retval NUMBER;
BEGIN
  -- This is called after a EXEC_SQL.PACKAGE_ERROR is raised.
  -- It displays the raised error and continues.
  SET_ALERT_PROPERTY('DISPLAY_ERROR', ALERT_MESSAGE_TEXT,
    'Error ' || TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(v_conHandle))
    || ': ' || EXEC_SQL.LAST_ERROR_MESG(v_conHandle));
  alert_retval := SHOW_ALERT('DISPLAY_ERROR');
EXCEPTION
  WHEN EXEC_SQL.PACKAGE_ERROR THEN
    SET_ALERT_PROPERTY('DISPLAY_ERROR', ALERT_MESSAGE_TEXT,
     'Error: Unknown EXEC_SQL error');
    alert_retval := SHOW_ALERT('DISPLAY_ERROR');
  WHEN OTHERS THEN
    SET_ALERT_PROPERTY('DISPLAY_ERROR', ALERT_MESSAGE_TEXT,
     'Error: Unknown Forms error');
    alert_retval := SHOW_ALERT('DISPLAY_ERROR');
END;


   -----WHEN-NEW-FORM-INSTANCE

CON1.STASH_CURRCONN; -- Stash the current connection values.
CON2.NEW_CONNECT(TRUE);

 --------POST-FORMS-COMMIT-------
  CON2.DO_COMMIT;

--------WHEN-TAB-PAGE-CHANGED
IF :SYSTEM.TAB_NEW_PAGE = 'CON2' THEN
  go_block('CON2');
elsif :SYSTEM.TAB_NEW_PAGE = 'DYNAMIC' THEN
  go_block('DYNAMIC');
else
  go_block('EMP');
end if;