Search Results ben_tcs_report_details




Overview
Explore in AI Chat

Overview

BEN.BEN_TCS_REPORT_DETAILS is a table within the Oracle E-Business Suite Advanced Benefits (BEN) module. Its documented purpose is to identify the details of a Statement Generation request — that is, it stores the per-person, per-assignment outcome rows produced when a Total Compensation Statement (TCS) or benefits statement generation process is submitted. Each row represents one individual's participation in a specific statement generation run, capturing the identity of the person, the assignment context, the statement to which the row belongs, and status/error information describing whether that individual's statement was created successfully.

The table resides in the BEN schema and is marked VALID in the ETRM 12.2.2 documentation, with 18 documented columns. It is a transactional/details table rather than a setup or configuration table; it is populated and consumed by the concurrent programs that generate statements.

From a heuristic Data Vault classification standpoint — inferred from the mined foreign-key structure — the table appears to behave as a link/satellite hybrid: it carries a surrogate key (BEN_TCS_RPT_DET_ID) and foreign keys to BEN_TCS_STMT and BEN_BENEFIT_ACTIONS, with descriptive attributes about the generated statement. A dimensional-modeling suggestion would be to treat it as a detail fact/satellite over the statement and benefit-action dimensions. Because the mined structure is described as "standalone," this classification is a modeling heuristic rather than a documented ETRM fact.

Key Information Stored

The most significant columns documented for BEN_TCS_REPORT_DETAILS include:

  • BEN_TCS_RPT_DET_ID — the surrogate primary key; it is the sole column in the unique index BEN_TCS_REPORT_DETAILS_U1, making it the documented business-key/unique-identifier candidate.
  • STMT_ID — foreign key to BEN_TCS_STMT; identifies the statement run to which this detail row belongs.
  • BENEFIT_ACTION_ID — foreign key to BEN_BENEFIT_ACTIONS; ties the row to the benefit action that triggered or is associated with the statement generation.
  • PERSON_ID and ASSIGNMENT_ID — the person and assignment for whom the statement detail was produced.
  • FULL_NAME, EMPLOYEE_NUMBER, and ASSIGNMENT_NUMBER — denormalized identity attributes carried on the row for reporting convenience.
  • JOB_NAME — the job associated with the individual's assignment at the time of statement generation.
  • BUSINESS_GROUP_ID and BUSINESS_GROUP_NAME — the business group context for the record.
  • ELIGY_ID and ELIGY_PROF_NAME — the eligibility record and eligibility profile name applied to the individual for statement purposes.
  • STMT_NAME and STMT_CREATED — the statement name and whether the statement was created (a status/flag column).
  • ERROR and SETUP_VALID — diagnostic/validation columns indicating whether the row produced an error and whether the underlying setup was valid.
  • TOTAL_PERSONS — an aggregate count captured on the detail row.

Together these columns allow both row-level identity resolution and run-level validation reporting.

Common Use Cases and Queries

Typical uses include auditing statement generation runs, isolating failed generations, and reproducing statement counts per business group or eligibility profile. A representative query to identify rows that errored during a run:

  • SELECT d.employee_number, d.full_name, d.stmt_name, d.error FROM ben.ben_tcs_report_details d WHERE d.stmt_created = 'N' AND d.error IS NOT NULL;
  • SELECT d.business_group_name, COUNT(*) FROM ben.ben_tcs_report_details d WHERE d.stmt_id = :stmt_id GROUP BY d.business_group_name; — to reconcile TOTAL_PERSONS by group.
  • Joining to BEN_TCS_STMT via STMT_ID to retrieve run-level parameters alongside detail rows.
  • Reporting eligibility outcomes using ELIGY_PROF_NAME for population analysis.

Related Objects

The most significant related objects, based on documented foreign-key relationships, are:

  • BEN_TCS_STMT — joined on STMT_ID; the parent statement definition/run.
  • BEN_BENEFIT_ACTIONS — joined on BENEFIT_ACTION_ID; the triggering benefit action.
  • PER_ALL_PEOPLE_F / PER_ALL_ASSIGNMENTS_F — referenced indirectly via PERSON_ID and ASSIGNMENT_ID for identity and assignment context.
  • BEN_ELIGY_PRFL — associated through ELIGY_ID / ELIGY_PROF_NAME for eligibility profile detail.
  • HR_ALL_ORGANIZATION_UNITS — for business group resolution via BUSINESS_GROUP_ID.

These relationships support the principal reporting and reconciliation workflows over statement generation output.