Results for “psp_er_status_v”

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

Overview

PSP_ER_STATUS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PSP (Labor Distribution) product module. Its documented purpose is to "Show Effort Report Error Status Summary." In practice, the view consolidates the outcome of the Effort Report (ER) submission process into a compact, query-friendly result set that pairs each concurrent request with a status message and a count of affected Effort Reports.

The view plays a supporting role in error monitoring and reconciliation for Labor Distribution. Rather than joining report tables to the message repository manually, users and integrators can query PSP_ER_STATUS_V to obtain pre-aggregated status information. Because the view references FND_NEW_MESSAGES, it is tightly coupled to Oracle Application Object Library's message dictionary, meaning the human-readable status text returned is the same text used elsewhere in the EBS messaging framework. This makes the view suitable for diagnostic queries, exception reporting, and lightweight integration extracts without the need to hard-code message strings.

Underlying Base Objects

The view is defined over three documented base objects, each referenced through a synonym, consistent with standard APPS-layer coding practice in EBS 12.1.1 and 12.2.2:

• FND_NEW_MESSAGES — the Application Object Library message repository, joined on MESSAGE_NAME to retrieve the ER_STATUS message text. Two message names are used: PSP_ER_INITIATOR_PROCESSED and PSP_ER_INITIATOR_NOT_PROCESSED.
• PSP_EFF_REPORTS — the Effort Report header table, contributing processed-report rows and the REQUEST_ID used for grouping.
• PSP_REPORT_ERRORS — the Effort Report error detail table, contributing error rows grouped by REQUEST_ID, MESSAGE_LEVEL, SOURCE_ID, and ERROR_MESSAGE.

Functionally, the view is a UNION of two branches. The first branch counts Effort Reports in PSP_EFF_REPORTS for the 'PSP_ER_INITIATOR_PROCESSED' message, effectively summarising successfully processed reports. The second branch counts distinct error occurrences from PSP_REPORT_ERRORS for the 'PSP_ER_INITIATOR_NOT_PROCESSED' message, summarising reports that failed processing. Both branches group by REQUEST_ID and the message text.

Key Columns

The view exposes four columns:

• ROWIDENT — a literal discriminator produced by the query (1 for the processed branch, 2 for the not-processed/error branch). It partitions the result set and drives the final ORDER BY.
• REQUEST_ID — the concurrent request identifier associated with the Effort Report run. This is the primary grouping key and links the view to concurrent manager request history.
• ER_STATUS — the descriptive status text sourced from FND_NEW_MESSAGES.MESSAGE_TEXT, providing the readable status for the row.
• ER_COUNT — the aggregate count. In the processed branch this is COUNT(*) of Effort Reports; in the error branch it is COUNT(DISTINCT MESSAGE_LEVEL || TO_CHAR(SOURCE_ID) || ERROR_MESSAGE), i.e. a count of distinct error signatures per request.

Common Use Cases and Queries

The view is typically used to build error-summary reports and to quickly triage Effort Report submissions by request. A basic query returns the full status summary ordered as the view defines:

• SELECT ROWIDENT, REQUEST_ID, ER_STATUS, ER_COUNT FROM APPS.PSP_ER_STATUS_V ORDER BY ROWIDENT;
• Filtering to failing requests: SELECT REQUEST_ID, ER_STATUS, ER_COUNT FROM APPS.PSP_ER_STATUS_V WHERE ROWIDENT = 2 ORDER BY ER_COUNT DESC;
• Inspecting a specific run: SELECT * FROM APPS.PSP_ER_STATUS_V WHERE REQUEST_ID = :request_id;

Because the view performs its own aggregation, it is well suited to dashboard tiles, concurrent-program output, and reconciliation scripts that must distinguish clean runs from runs carrying errors. Access should be granted via the APPS schema in keeping with EBS security conventions.