Search Results sys_il0000410030c00015

Overview

PSP.PSP_EFF_REPORTS is a transactional table in the Oracle EBS Projects (PSP) schema that stores effort report process request information, primarily supporting the OTL/PSP effort reporting and cost distribution workflow. Each row represents a person-level effort report generated for a given reporting period, retaining the source XML payload, status information, and the identifiers required to tie the report back to the concurrent request that created it. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes are distributed across APPS_TS_TX_IDX and APPS_TS_TX_DATA.

Applying Data Vault modeling heuristics to the documented foreign key structure, this object classifies as a standalone structure — effectively a hub-satellite combination, since the table carries both a durable business key (EFFORT_REPORT_ID) and descriptive, non-key attributes that change over time. This classification is a modeling suggestion rather than a functional property of the table.

Key Information Stored

EFFORT_REPORT_ID is the surrogate primary key, enforced by the primary key constraint PSP_EFF_REPORT_PK and reinforced by the unique normal index PSP_EFF_REPORTS_U1. It is also the strongest business-key candidate documented for this table; the LOB index SYS_IL0000410030C00015$$ is associated with the PERSON_XML column rather than a business attribute.

• EFFORT_REPORT_ID — Person Effort Report Identifier; the surrogate primary key and unique index candidate.
• PERSON_ID — Person identifier, the principal link to the employee/assignee dimension.
• FULL_NAME — Employee full name captured at the time of report generation.
• START_DATE / END_DATE — Effort report period boundaries.
• STATUS_CODE — Effort report status (N-New, A-Approved, S-Superceded, R-Rejected).
• TEMPLATE_ID — Foreign key to PSP_REPORT_TEMPLATES_H, defining the report layout.
• REQUEST_ID — Identifier of the PSP Create effort report concurrent request.
• BUSINESS_GROUP_ID / SET_OF_BOOKS_ID — Organizational and ledger context.
• CURRENCY_CODE — Currency in which the report amounts are expressed.
• SUPERCEDING_SUMMARY_LINE_ID / SUPERCEDING_REQUEST_ID — Pointers linking a superseded report to the report that replaced it.
• PERSON_XML — CLOB containing the person-level effort report XML payload.
• MAILSTOP, EMPLOYEE_NUMBER, EMP_PRIMARY_ORG_NAME, EMP_PRIMARY_ORG_ID — Denormalized employee attributes reproduced for reporting convenience.

Standard Who columns (CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY) and OBJECT_VERSION_NUMBER provide audit and optimistic-locking support.

Common Use Cases and Queries

Typical reporting requirements include retrieving the latest approved effort report for a person and period, auditing rejected or superseded reports, and reconciling effort reports against distribution lines. The nonunique indexes PSP_EFF_REPORTS_N1 (REQUEST_ID, PERSON_ID) and PSP_EFF_REPORTS_N2 (PERSON_ID, START_DATE, END_DATE, CURRENCY_CODE) directly support these access paths.

• Locating reports created by a specific concurrent request: select from PSP_EFF_REPORTS where REQUEST_ID = :request_id.
• Fetching a person's report for a period: filter on PERSON_ID, START_DATE, and END_DATE, ordering by CREATION_DATE descending.
• Status-based audit: select EFFORT_REPORT_ID, PERSON_ID, STATUS_CODE from PSP_EFF_REPORTS where STATUS_CODE in ('R','S').
• Drill-down to the XML payload: join on EFFORT_REPORT_ID and extract PERSON_XML for downstream parsing.

Related Objects

PSP_EFF_REPORTS is referenced by several downstream distribution and adjustment objects through the EFFORT_REPORT_ID foreign key:

• PSP_PRE_GEN_DIST_LINES_HISTORY.EFFORT_REPORT_ID → PSP_EFF_REPORTS
• PSP_PRE_GEN_DIST_LINES_ARCH.EFFORT_REPORT_ID → PSP_EFF_REPORTS
• PSP_DISTRIBUTION_LINES_ARCH.EFFORT_REPORT_ID → PSP_EFF_REPORTS
• PSP_ADJUSTMENT_LINES_ARCH.EFFORT_REPORT_ID → PSP_EFF_REPORTS
• PSP_REPORT_TEMPLATES_H — referenced via TEMPLATE_ID.

These relationships allow effort reports to be traced into the generated pre-generation distribution lines, archived distribution lines, and adjustment lines used during cost distribution processing.