Results for “external_effort_line_id”
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.
- EXTERNAL_EFFORT_LINE_ID — surrogate primary key for the row, enforced by PSP_EXTERNAL_EFFORT_LINES_PK and also by the unique index PSP_EXTERNAL_EFFORT_LINES_U1.
- BATCH_NAME — groups lines belonging to a single external import run.
- BUSINESS_GROUP_ID and SET_OF_BOOKS_ID — the operating unit and ledger context for the effort line; together with BATCH_NAME they form the business-key candidate PSP_EXTERNAL_EFFORT_LINES_U2.
- PERSON_ID and ASSIGNMENT_ID — identify the employee and assignment to which the effort applies.
- DISTRIBUTION_DATE — the accounting or effort date for the line.
- DISTRIBUTION_AMOUNT and CURRENCY_CODE — the monetary value and currency of the effort distribution.
- PROJECT_ID, TASK_ID, and AWARD_ID — the sponsored project, task, and award context, with AWARD_ID referencing IGF_AW_AWARD_ALL.
- EXPENDITURE_ORGANIZATION_ID and EXPENDITURE_TYPE — the owning expenditure organization and expenditure classification.
- GL_CODE_COMBINATION_ID — the accounting flexfield combination for the distribution.
- OBJECT_VERSION_NUMBER — optimistic locking column supporting the EBS concurrent and OAF framework.
- ATTRIBUTE1 through ATTRIBUTE15 plus ATTRIBUTE_CATEGORY — the standard EBS descriptive flexfield columns for custom external data.
- LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE — standard who-column auditing.
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.
-
Stores effort report lines information imported from external/custom processes
-
PACKAGE: APPS.PSP_PEE_SHD 12.2.2
-
PACKAGE: APPS.PSP_PEE_SHD 12.1.1
-
PACKAGE: APPS.PSP_PEE_INS 12.1.1
-
PACKAGE: APPS.PSP_PEE_INS 12.2.2
-
PACKAGE: APPS.PSP_PEE_DEL 12.2.2
-
PACKAGE: APPS.PSP_PEE_DEL 12.1.1
-
PACKAGE: APPS.PSP_PEE_UPD 12.2.2
-
PACKAGE: APPS.PSP_PEE_UPD 12.1.1
-
PACKAGE: APPS.PSP_PEE_SHD 12.1.1
-
PACKAGE: APPS.PSP_PEE_SHD 12.2.2