Results for “psp_er_initiator_processed”

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

Overview

APPS.PSP_ER_STATUS_V is a status aggregation view in the Oracle E-Business Suite Payables and Payments product family, specifically within the Payments Process (PSP) schema supporting the Expense Report (ER) initiator flow used by Oracle Internet Expenses and Oracle Payables reimbursement processing. The view is documented in ETRM 12.2.2 and is also present in the 12.1.1 code line. Its purpose is to present a consolidated, per-concurrent-request status of expense report initiator processing, classifying outcomes into two buckets: records that were processed successfully by the ER initiator and records that were not processed.

Rather than exposing raw error rows, the view uses a UNION of two aggregation branches to return a small, readable result set keyed by REQUEST_ID, making it suitable for diagnostic reporting, concurrent program monitoring, and downstream integration checks that need to answer "did the ER initiator finish cleanly for this request?"

Underlying Base Objects

The view is defined over three documented objects, all referenced through APPS synonyms:

• PSP_EFF_REPORTS — the effective/processed reports table. It is the driving table for the first UNION branch, counting reports successfully handled by the initiator.
• PSP_REPORT_ERRORS — the error staging table populated when the ER initiator encounters problems. It drives the second UNION branch.
• FND_NEW_MESSAGES — the Oracle Application Object Library message repository. It supplies the human-readable STATUS text (MESSAGE_TEXT) for the two message names used: PSP_ER_INITIATOR_PROCESSED and PSP_ER_INITIATOR_NOT_PROCESSED.

Because the view joins both data tables to FND_NEW_MESSAGES on a constant MESSAGE_NAME predicate, the counts are labelled with the corresponding translated message text rather than a hard-coded literal.

Key Columns

• ROWIDENT — a literal discriminator: 1 for the "processed" branch, 2 for the "not processed" branch. It guarantees deterministic ordering and allows pivoting.
• REQUEST_ID — the concurrent request identifier from FND_CONCURRENT_REQUESTS. This is the primary grouping key across both branches.
• ER_STATUS — the message text retrieved from FND_NEW_MESSAGES, i.e. the displayed status label for that request.
• ER_COUNT — in the first branch, COUNT(*) of rows in PSP_EFF_REPORTS per request; in the second branch, COUNT(DISTINCT MESSAGE_LEVEL || TO_CHAR(SOURCE_ID) || ERROR_MESSAGE), which de-duplicates errors by severity, source and message text so repeated identical errors are not double counted.

Common Use Cases and Queries

The view is typically queried to audit a specific expense report initiator run, or to find requests where errors were logged. A representative query for a given request is:

• SELECT rowident, request_id, er_status, er_count FROM apps.psp_er_status_v WHERE request_id = :req_id ORDER BY rowident;
• To list all requests with unprocessed reports: SELECT request_id, er_status, er_count FROM apps.psp_er_status_v WHERE rowident = 2 AND er_count > 0 ORDER BY er_count DESC;
• To obtain a pivoted success/failure summary: SELECT request_id, MAX(CASE WHEN rowident=1 THEN er_count END) processed, MAX(CASE WHEN rowident=2 THEN er_count END) not_processed FROM apps.psp_er_status_v GROUP BY request_id;

Note that a request will only appear in a branch when rows exist in the corresponding base table; a fully successful run therefore returns only the ROWIDENT = 1 row. Requests with no rows in either base table are absent entirely, so the view should not be used as a complete roster of concurrent requests.