1. Purpose

This document explains the standard mechanism used to initialize and maintain user context in the Oracle APEX application.

The application supports multiple authentication methods, including:

• OTP-based login
• Token-based login
• Username & password login

Regardless of the login method, the application must always know:

• Which user is logged in
• Which organization/company the user belongs to

This is achieved using Application Items populated through a single post-authentication procedure.

2. Design Principle

Core Rule

User and Organization context must be initialized exactly once after successful authentication and remain available throughout the session.

To enforce this rule:

• No page-level login logic is used
• No duplicate authentication-related processes exist
• A single post-authentication procedure is executed after every successful login

3. Application Items Used

The following Application Items are defined and used globally:

Scope

• Available on all pages
• Valid for the entire session
• Automatically cleared when the session ends

4. Post-Authentication Procedure

Procedure Name

XXEWAY_POST_LOGIN_PROC 

Execution Point

This procedure is configured in the Authentication Scheme under:

Login Processing → Post-Authentication Procedure Name 

It is executed after every successful login, regardless of authentication type.

Procedure Logic

CREATE OR REPLACE PROCEDURE XXEWAY_POST_LOGIN_PROC IS    l_emp_id    xxeway_employee.employeeid%TYPE;    l_org_id    xxeway_employee.org_id%TYPE;    l_org_name  xxeway_organization_profile.organization_name%TYPE;    l_org_code  xxeway_organization_profile.organization_code%TYPE;    l_user      VARCHAR2(200); BEGIN    /* Retrieve authenticated APEX user */    l_user := v('APP_USER');    /* Fetch employee and organization context */    SELECT e.employeeid,           e.org_id,           o.organization_name,           o.organization_code    INTO   l_emp_id,           l_org_id,           l_org_name,           l_org_code    FROM   xxeway_employee e    JOIN   xxeway_organization_profile o           ON o.organization_code = e.orgcode    WHERE  e.username = l_user    AND    e.canlogin = 'Y';    /* Set global application context */    APEX_UTIL.SET_SESSION_STATE('APP_EMPLOYEE_ID',     l_emp_id);    APEX_UTIL.SET_SESSION_STATE('APP_ORG_PROFILE_ID', l_org_id);    APEX_UTIL.SET_SESSION_STATE('APP_ORG_NAME',       l_org_name);    APEX_UTIL.SET_SESSION_STATE('APP_ORG_CODE',       l_org_code); EXCEPTION    WHEN NO_DATA_FOUND THEN        NULL; -- Optional: logging or audit END; / 

5. Execution Flow

User Login (OTP / Token / Password)        ↓ Authentication Function        ↓ Post-Authentication Procedure        ↓ Application Items Initialized        ↓ User Redirected to Application

6. Usage Across the Application

6.1 SQL Queries

SELECT * FROM xxeway_api_config WHERE organization_code = :APP_ORG_CODE;

6.2 PL/SQL Logic

IF :APP_ORG_CODE = 'ROSTAN' THEN   -- Authorized logic END IF;

6.3 Authorization Schemes

RETURN :APP_ORG_CODE = 'ROSTAN';

6.4 Default Page Item Values

:APP_ORG_NAME

7. Security Considerations

• Passwords are never stored in session state
• Only identifiers and organization metadata are stored
• Application Items are session-bound and cleared automatically
• No page-level authentication logic exists

8. Advantages of This Design

9. Compliance With Best Practices

This implementation follows:

• Oracle APEX recommended authentication patterns
• Enterprise multi-tenant application design
• Separation of authentication and session context logic

10. Summary

The application uses a single post-authentication procedure to initialize global session context using Application Items.
This ensures consistent behavior across all login mechanisms while maintaining security, scalability, and maintainability.

Other Process

CREATE OR REPLACE PROCEDURE XXEWAY_POST_LOGIN_PROC
IS
   l_emp_id    xxxway_employee.employeeid%TYPE;
   l_org_id    xxxway_employee.org_id%TYPE;
   l_org_name  xxxway_organization_profile.organization_name%TYPE;
   l_org_code  xxxway_organization_profile.organization_code%TYPE;
   l_app_user  VARCHAR2(200);
BEGIN
   /* 🔐 Logged-in user from APEX session */
   l_app_user := v('APP_USER');

   /* Employee + Organization fetch */
   SELECT e.employeeid,
          e.org_id,
          o.organization_name,
          o.organization_code
   INTO   l_emp_id,
          l_org_id,
          l_org_name,
          l_org_code
   FROM   xxxway_employee e
   JOIN   xxxway_organization_profile o
          ON o.organization_code = e.orgcode
   WHERE  e.username = l_app_user
   AND    e.canlogin = 'Y';

   /* 🌍 Application Items set */
   APEX_UTIL.SET_SESSION_STATE('APP_EMPLOYEE_ID',     l_emp_id);
   APEX_UTIL.SET_SESSION_STATE('APP_ORG_PROFILE_ID', l_org_id);
   APEX_UTIL.SET_SESSION_STATE('APP_ORG_NAME',       l_org_name);
   APEX_UTIL.SET_SESSION_STATE('APP_ORG_CODE',       l_org_code);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
       NULL; -- optional logging
END;
/