Results for “psp_rep_ssp_chg_v”

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

Overview

The view APPS.PSP_REP_SSP_CHG_V belongs to the Oracle E-Business Suite product PSP (Labor Distribution) and is shipped with a status of VALID in both EBS 12.1.1 and 12.2.2. It serves as the reporting data source for the Suspense Account Charging report, exposing the set of labor distribution and pre-generation distribution transactions that were charged to a suspense organization account. In Labor Distribution, when a valid account cannot be derived for a distribution line, the cost is directed to a suspense account so that it can be investigated and corrected. This view consolidates those suspense-bearing records from both the distribution history and the pre-generation distribution history, together with any corresponding adjustment history rows, into a single presentation layer for reporting.

As a reporting view rather than a transactional table, PSP_REP_SSP_CHG_V carries no storage of its own. Its significance lies in encapsulating the join, date-effectivity, and adjustment-exclusion logic so that the Suspense Account Charging report and any associated inquiry forms present a consistent, pre-filtered result set.

Underlying Base Objects

The view is defined over the following documented base objects, all referenced through APPS synonyms:

• PSP_DISTRIBUTION_LINES_HISTORY — the primary distribution line history, driving the first UNION ALL branch.
• PSP_PRE_GEN_DIST_LINES_HISTORY — pre-generation distribution lines, driving the second UNION ALL branch.
• PSP_ADJUSTMENT_LINES_HISTORY — adjustment lines, driving the third UNION ALL branch; also used in NOT EXISTS subqueries to suppress superseded lines.
• PSP_SUMMARY_LINES — supplies the summary line and assignment linkage.
• PSP_ORGANIZATION_ACCOUNTS — resolves the suspense organization account identifier.
• PER_ALL_ASSIGNMENTS_F — the assignment and effective-dated organization context.
• HR_ALL_ORGANIZATION_UNITS — provides the organization name.

Structurally, the view is a three-way UNION ALL of the three history tables. Each branch filters on SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL and REVERSAL_ENTRY_FLAG IS NULL, joins to PSP_ORGANIZATION_ACCOUNTS on the suspense account, joins to PER_ALL_ASSIGNMENTS_F with the distribution date falling between the assignment effective start and end dates, and joins to HR_ALL_ORGANIZATION_UNITS for the organization name. Each branch also excludes lines for which an original-flagged adjustment line already exists, keyed by ORIG_LINE_ID and the appropriate ORIG_SOURCE_TYPE ('D', 'P', or 'A').

Key Columns

• ASSIGNMENT_ID — the assignment to which the suspense-charged distribution relates.
• ORGANIZATION_ID — the organization identifier from the assignment or organization unit join.
• DISTRIBUTION_DATE — the effective date used for assignment effectivity matching and reporting.
• NAME — the organization name from HR_ALL_ORGANIZATION_UNITS.
• BUSINESS_GROUP_ID — the business group context carried from the distribution line.
• SET_OF_BOOKS_ID — the ledger (set of books) associated with the transaction.

Common Use Cases and Queries

The principal use case is the Suspense Account Charging report. The view also supports ad hoc inquiry into suspense balances by organization, assignment, and ledger.

• Suspense charges by ledger:

SELECT set_of_books_id,
       organization_id,
       distribution_date,
       assignment_id
FROM   apps.psp_rep_ssp_chg_v
WHERE  set_of_books_id = :p_set_of_books_id
ORDER BY distribution_date;

• Charges for a specific organization:

SELECT assignment_id,
       distribution_date,
       name
FROM   apps.psp_rep_ssp_chg_v
WHERE  organization_id = :p_organization_id;

• Aggregate count by organization:

SELECT organization_id,
       name,
       COUNT(*) AS charge_count
FROM   apps.psp_rep_ssp_chg_v
GROUP BY organization_id, name
ORDER BY charge_count DESC;

Because the view already excludes reversal entries, resolves effectivity dates, and removes lines superseded by adjustments, queries against it require no additional filtering for those conditions. For 12.2.2 environments, results should be scoped by ledger using SET_OF_BOOKS_ID or the appropriate ledger context.