Results for “iby_f_t_ca_status_h_v”

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

Overview

The IBY_F_T_CA_STATUS_H_V view is an APPS-owned database view within the Oracle Payments (IBY) module of Oracle E-Business Suite. It exposes the credit application status history recorded against credit applications processed by the Payments credit-management and funds-capture infrastructure. The underlying transaction table, IBY_F_T_CRDT_APP_ST_H, stores one row for every status transition a credit application undergoes, capturing the prior status, the new status, the actor responsible, the timestamp of the event, and associated descriptive codes. The view enriches that raw history with decoded status code, name, and description values, and resolves the acting user to a readable FND_USER name where applicable.

This object is primarily a read-only reporting and integration view. It is used by inquiry screens, dashboards, and extract routines that need to present a chronological audit trail of how a credit application moved between statuses, including system-driven transitions (for example, concurrent-program updates) and manual transitions performed by buyers, finance managers, order administrators, or guarantors.

Underlying Base Objects

The ETRM 12.2.2 metadata documents three referenced base objects:

• IBY_F_T_CRDT_APP_ST_H (synonym) — the credit application status history table. This is the driving table of the view; every column from STH appears in the select list.
• IBY_F_CRDT_APP_STATUS_VL (view) — the credit application status lookup, joined twice. STVL resolves OLD_STATUS_ID, and STVL1 resolves NEW_STATUS_ID. The lookup supplies the STATUS_CODE, NAME, and DESCRIPTION used to render the decoded status columns.
• FND_USER (synonym) — the Oracle Applications user table, outer-joined on USER_ID to resolve a displayable user name.

The join structure is significant. The join from OLD_STATUS_ID to STVL is an outer join, reflecting the fact that the very first status history row for a credit application has no predecessor status. The join from NEW_STATUS_ID to STVL1 is an inner join, because a status history row must, by definition, record the status being entered. The join to FND_USER is an outer join, since system-initiated transitions carry no meaningful user_id.

Key Columns

• STATUS_HISTORY_ID — primary identifier for the individual status-history record.
• CREDIT_APP_ID — foreign key to the credit application whose lifecycle this row describes; the principal grouping key when reconstructing a full audit trail.
• OLD_STATUS_ID / OLD_STATUS_CODE / OLD_STATUS_NAME / OLD_STATUS_DESC — the status the application held before the transition. These are null on the initial history row.
• NEW_STATUS_ID / NEW_STATUS_CODE / NEW_STATUS_NAME / NEW_STATUS_DESC — the status the application entered as a result of the event. The new_status_id column is frequently used as a search predicate when filtering history to transitions into a particular status.
• USER_ID / USER_NAME / USER_TYPE_CODE — the actor responsible for the transition. USER_NAME is derived through a DECODE: BUYER, FINANCE_MGR, and ORDER_ADMIN resolve to the FND_USER user name; GUARANTOR maps to the literal 'PERSONAL GUARANTOR'; all other cases (including a null USER_TYPE_CODE defaulted to 'CONCUR') display 'SYSTEM'.
• CA_STATUS_EVENT_TIME — the effective timestamp of the status event, used for chronological ordering.
• CA_STATUS_EVENT_CODE / CA_STATUS_EVENT_DESC — the coded event that triggered the transition.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent-program context identifying the background process that performed the update.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, SECURITY_GROUP_ID — standard EBS audit and multi-org (security group) columns.

Common Use Cases and Queries

Typical uses include credit application audit reports, dispute and escalation analysis, SLA/processing-time measurement between status events, and integration extracts where downstream systems require the latest status of a credit application together with how it was reached.

Example: retrieve the complete status transition history for a given credit application, in order:

SELECT sth.credit_app_id,
       sth.ca_status_event_time,
       sth.old_status_code,
       sth.new_status_code,
       sth.new_status_name,
       sth.user_name,
       sth.user_type_code,
       sth.ca_status_event_code
FROM   apps.iby_f_t_ca_status_h_v sth
WHERE  sth.credit_app_id = :p_credit_app_id
ORDER  BY sth.ca_status_event_time, sth.status_history_id;

Example: locate all transitions into a specific status using new_status_id, which is the term most commonly searched against this view:

SELECT sth.credit_app_id,
       sth.old_status_name,
       sth.new_status_name,
       sth.ca_status_event_time,
       sth.user_name
FROM   apps.iby_f_t_ca_status_h_v sth
WHERE  sth.new_status_id = :p_new_status_id
AND    sth.ca_status_event_time >= :p_from_date
ORDER  BY sth.ca_status_event_time DESC;

Example: identify system-generated (non-user) transitions for operational monitoring:

SELECT sth.credit_app_id,
       sth.new_status_code,
       sth.ca_status_event_time,
       sth.request_id,
       sth.program_id
FROM   apps.iby_f_t_ca_status_h_v sth
WHERE  sth.user_type_code IS NULL
OR     sth.user_id IS NULL
ORDER  BY sth.ca_status_event_time DESC;

Because the view performs the status and user decoding inline, reporting queries can avoid re-joining IBY_F_CRDT_APP_STATUS_VL and FND_USER. However, for high-volume extracts it is worth noting the two lookup joins and applying selective predicates on CREDIT_APP_ID, NEW_STATUS_ID, or CA_STATUS_EVENT_TIME to limit the driving set from IBY_F_T_CRDT_APP_ST_H.