Results for “ca_status_event_time”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IBY_F_T_CRDT_APP_ST_H is a table in the IBY schema (Oracle Payments) that stores the status-change history of credit applications processed within Oracle E-Business Suite. It records transitions of a credit application from one credit application status to another, capturing the before and after state along with the responsible user, event classification, and event timestamp. Within Oracle Payments, this object supports auditability and lifecycle tracking for credit application processing, enabling inquiry and reporting on how and when an application moved through its status model.

The ETRM metadata classifies this object heuristically as a link in a Data Vault modeling sense. As a modeling suggestion, this classification reflects its primary role: it associates a credit application with status reference records (old status and new status) plus the user/time context of the transition, rather than acting as a standalone descriptive hub or a wide descriptive satellite.

Key Information Stored

The table contains 20 documented columns. The most significant are:

• STATUS_HISTORY_ID — the surrogate primary key of the history record. It is the single documented unique index candidate (IBY_F_T_CRDT_APP_ST_H_U1) and is defined by the IBY_F_T_CRDT_APP_ST_H_PK constraint.
• CREDIT_APP_ID — foreign key to IBY_F_T_CRDT_APPS_ALL_B; identifies the credit application whose status changed.
• OLD_STATUS_ID — foreign key to IBY_F_CRDT_APP_STATUS_B; the status the application held before the transition.
• NEW_STATUS_ID — foreign key to IBY_F_CRDT_APP_STATUS_B; the status the application assumed after the transition.
• CA_STATUS_EVENT_CODE, CA_STATUS_EVENT_TIME, CA_STATUS_EVENT_DESC — the classification, timing, and description of the status event.
• USER_ID, USER_TYPE_CODE — the user or system principal that triggered the transition.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, applying Multi-Org Access Control and data security to the record.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — standard concurrent program execution context, identifying the request that generated the history row.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO columns supporting optimistic locking and auditability.

The surrogate key is STATUS_HISTORY_ID; the business key naturally corresponds to the (CREDIT_APP_ID, OLD_STATUS_ID, NEW_STATUS_ID, CA_STATUS_EVENT_TIME) combination, though only the surrogate is documented as a unique index.

Common Use Cases and Queries

Typical uses include audit reporting, lifecycle analysis, and investigation of credit application processing anomalies.

• Status change history for a specific credit application:

  SELECT h.status_history_id, h.credit_app_id,
         h.old_status_id, h.new_status_id,
         h.ca_status_event_code, h.ca_status_event_time,
         h.user_id, h.last_update_date
  FROM   iby.iby_f_t_crdt_app_st_h h
  WHERE  h.credit_app_id = :p_credit_app_id
  ORDER  BY h.ca_status_event_time;

• Joining to status reference data to resolve descriptions:

  SELECT h.credit_app_id, os.status_code, ns.status_code,
         h.ca_status_event_time
  FROM   iby.iby_f_t_crdt_app_st_h h,
         iby.iby_f_crdt_app_status_b os,
         iby.iby_f_crdt_app_status_b ns
  WHERE  h.old_status_id = os.status_id
  AND    h.new_status_id = ns.status_id;

• Volume of transitions per status event for a period, filtered by SECURITY_GROUP_ID for ORG security.
• Reconciliation of concurrent program runs using REQUEST_ID against FND_CONCURRENT_REQUESTS.

Related Objects

• IBY_F_T_CRDT_APPS_ALL_B — joined on CREDIT_APP_ID; the master credit application record.
• IBY_F_CRDT_APP_STATUS_B — joined twice via OLD_STATUS_ID and NEW_STATUS_ID; status reference lookup.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID for data security.
• FND_CONCURRENT_REQUESTS / FND_CONCURRENT_PROGRAMS — via REQUEST_ID, PROGRAM_APPLICATION_ID and PROGRAM_ID for program context.
• IBY_F_T_CRDT_APP_ST_H_PK / IBY_F_T_CRDT_APP_ST_H_U1 — primary key and unique index supporting the STATUS_HISTORY_ID.