Search Results fiibv_proj_task_lcv




Overview
Explore in AI Chat

Overview

FIIBV_PROJ_TASK_LCV is a database view owned by the APPS schema within the Oracle E-Business Suite environment, and it is classified under the FII (Financial Intelligence) product family. As documented in the ETRM repository for EBS 12.1.1 and 12.2.2, the view serves as the Project Task level base view for the Project dimension in the Enterprise Data Warehouse (EDW). In this capacity, it acts as a semantic layer that flattens and conforms Project and Project Task information into a form suitable for dimensional modeling, reporting, and extraction into the EDW.

The "LCV" suffix denotes a level or load-consistent view used to materialize the hierarchy of the Project dimension at the Task level. The view is essential for Financial Intelligence reporting because it resolves the relationship between programs, projects, and tasks, and it provides surrogate key construction, denormalized naming, and dummy rows that allow fact tables to reference a Project or Task even when the transaction is recorded only at the project level. Because it is a view and not a table, it always reflects the current state of the underlying transactional data without requiring a separate staging load.

Underlying Base Objects

The view is defined over three principal base objects, with one additional source referenced in a union branch. The documented referenced base objects in the ETRM metadata are not separately enumerated, but the view text clearly identifies the following:

  • PA_TASKS — the Project Tasks table, aliased as PT, which supplies task identifiers, names, numbers, parent/child relationships, dates, and service type attributes.
  • PA_PROJECTS_ALL — the Projects table, aliased as PA, which supplies the project identifier, segment1 (project number), project name, and carrying-out organization.
  • EDW_LOCAL_INSTANCE — the EDW instance registry, which contributes the instance code appended to nearly every key to distinguish data originating from different source instances.
  • PJM_SEIBAN_NUMBERS — referenced in the final union branch to introduce Seiban-numbered pseudo-projects that must be represented in the Project dimension for cost collection purposes.

The view is constructed as a UNION ALL of three branches. The first branch returns actual project tasks, filtering out parent tasks that have children in order to expose only leaf tasks. The second branch generates a dummy project-level row for every project in PA_PROJECTS_ALL. The third branch generates equivalent dummy rows for Seiban numbers, ensuring that the dimension contains a complete and consistent set of members.

Key Columns

The view exposes thirteen documented columns. The most significant are summarized below.

  • TASK_PK — the concatenated primary key, formed as TASK_ID || '-' || INSTANCE_CODE, used as the surrogate identifier for the task member of the Project dimension.
  • NAME — the display name combining the task name with the project segment, presented as TASK_NAME || ', ' || SEGMENT1.
  • TASK — the task name on its own.
  • TASK_NUMBER — the user-facing task number for the task row, and the project segment1 for project-level and Seiban rows.
  • TOP_TASK_FK — the concatenated reference to the top-level task, enabling hierarchy traversal within the dimension.
  • INSTANCE — the EDW instance code identifying the source instance.
  • TASK_START_DATE / TASK_END_DATE — the task's start and completion dates. These are null for the dummy project and Seiban rows, which are created with TO_DATE(NULL).
  • SERVICE_TYPE_CODE — the service type classification of the task, populated only for actual task rows.
  • LABOR_COST_MULT — the labor cost multiplier name, suppressed via DECODE for top tasks (where TASK_ID equals TOP_TASK_ID) and null for dummy rows.
  • DENORM_TASK_ORG_FK — the concatenated carrying-out organization key, TASK_ORG_ID || '-' || INSTANCE_CODE.
  • LAST_UPDATE_DATE — the audit timestamp used to drive incremental extraction.
  • _DF:TSK:_EDW — a descriptive flexfield descriptor column referencing PA_TASKS_DESC_FLEX, which allows EDW consumers to attach DFF attribute metadata to the task dimension.

Common Use Cases and Queries

The view is typically consumed by EDW extraction programs, Financial Intelligence dashboards, and custom operational reports that require project task attributes in a denormalized form. A representative query listing the leaf tasks for a given project is shown below.

SELECT task_pk, name, task_number, top_task_fk, task_start_date, task_end_date FROM apps.fiibv_proj_task_lcv WHERE instance = 'PROD' AND top_task_fk = '12345-PROD' ORDER BY task_start_date;

Because the view includes project-level dummy rows, it is also used to reconcile project facts that are recorded without a task reference.

SELECT task_pk, name FROM apps.fiibv_proj_task_lcv WHERE task_number = 'PRJ-1001' AND task_pk LIKE '%-PJ-PRJ';

Other common scenarios include loading the Project dimension into the EDW, validating hierarchical parent-child relationships between tasks, extracting DFF metadata for tasks, and filtering on LAST_UPDATE_DATE for incremental (delta) extracts. The Seiban union branch ensures that cost collectors associated with Seiban numbers receive a valid Project dimension member, which is critical for cost and revenue fact reconciliation in Japanese manufacturing implementations.