Results for “psp_external_effort_lines”

50+ results




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

Overview

The PSP_EXTERNAL_EFFORT_LINES table resides in the PSP (Labor Distribution) schema and is a core staging and storage object for effort report line data that originates outside of Oracle EBS standard effort processing. Its documented purpose is to store effort report line information imported from external or custom processes, positioning it as the integration point between third-party time and effort systems and the Oracle Labor Distribution engine.

In the Oracle EBS 12.1.1 and 12.2.2 releases, this table supports the OTL-to-Labor Distribution flow by accepting externally sourced salary and effort distributions, which are subsequently validated and processed against projects, awards, and general ledger accounts. Because the table carries a foreign key to IGF_AW_AWARD_ALL and key derivation classifiers such as PROJECT_ID, TASK_ID, and EXPENDITURE_TYPE, it acts as the inbound fact surface for sponsored-effort accounting.

Following the heuristic Data Vault classification mined from the foreign key structure, PSP_EXTERNAL_EFFORT_LINES is best modeled as a standalone table rather than a strict hub, link, or satellite. It does not sit within a normalized Data Vault relationship network in the ETRM metadata; instead, it functions as a flat inbound staging record. Analysts designing a warehouse model may therefore treat it as a source-stage satellite that can later be normalized into person, award, and project hubs plus an effort distribution link.

Key Information Stored

The table documents 37 columns. The most significant group is the identity and batch keys, followed by the financial and assignment attributes.

Common Use Cases and Queries

Typical use cases center on verifying imported data, reconciling totals, and tracing failed or pending effort lines prior to Labor Distribution processing. A common reconciliation query aggregates the distribution amount per batch and ledger:

SELECT batch_name, set_of_books_id,
       SUM(distribution_amount) total_amount
FROM   psp.psp_external_effort_lines
GROUP  BY batch_name, set_of_books_id;

A second pattern traces effort attributed to a specific person or award:

SELECT person_id, assignment_id, distribution_date,
       distribution_amount, project_id, task_id, award_id
FROM   psp.psp_external_effort_lines
WHERE  person_id = :p_person_id
AND    award_id  = :p_award_id;

Support and reporting users also query by batch to identify incomplete loads, and join to GL code combinations to validate the accounting flexfield before the Labor Distribution process consumes the rows.

Related Objects

  • IGF_AW_AWARD_ALL — referenced by PSP_EXTERNAL_EFFORT_LINES.AWARD_ID; join on AWARD_ID to retrieve award attributes.
  • PSP_EXTERNAL_EFFORT_LINES_PK — primary key constraint on EXTERNAL_EFFORT_LINE_ID.
  • PSP_EXTERNAL_EFFORT_LINES_U1 — unique index on EXTERNAL_EFFORT_LINE_ID.
  • PSP_EXTERNAL_EFFORT_LINES_U2 — composite unique index on BATCH_NAME, BUSINESS_GROUP_ID, SET_OF_BOOKS_ID.
  • PER_ALL_ASSIGNMENTS_F — logical join on PERSON_ID and ASSIGNMENT_ID for assignment detail.
  • PA_PROJECTS_ALL and PA_TASKS — logical joins on PROJECT_ID and TASK_ID.
  • GL_CODE_COMBINATIONS — logical join on GL_CODE_COMBINATION_ID to resolve the accounting flexfield.