Results for “pa_transaction_controls”

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

Overview

PA_TRANSACTION_CONTROLS is a Projects (PA) module table that stores transaction controls governing the chargeable and billable status of project and task expenditures in Oracle E-Business Suite. The table allows organizations to define, at a granular level, whether specific combinations of project, task, expenditure category, person, expenditure type, and non-labor resource may be charged or billed. When a user enters a pre-approved expenditure, Oracle Projects evaluates the applicable control record to determine whether the transaction is chargeable, billable, or both, thereby enforcing project billing and cost policies at the point of entry.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as a link. This is consistent with its structure: the table resolves relationships among multiple business entities — projects, tasks, expenditure categories, expenditure types, persons, and non-labor resources — rather than storing a single descriptive subject on its own. Treating it as a link in a dimensional or Data Vault model is a reasonable suggestion, with the dependent flag attributes (chargeable, billable, scheduled-only) behaving as link-level descriptive data.

Key Information Stored

The physical table in ETRM 12.2.2 contains 18 columns. The most important are:

• PROJECT_ID – Identifies the project to which the control applies; foreign key to PA_PROJECTS_ALL.
• TASK_ID – Identifies the specific task within the project; foreign key to PA_TASKS.
• EXPENDITURE_CATEGORY – The expenditure category scope of the control; foreign key to PA_EXPENDITURE_CATEGORIES.
• EXPENDITURE_TYPE – The expenditure type to which the control applies; foreign key to PA_EXPENDITURE_TYPES.
• PERSON_ID – Restricts the control to a specific person (employee or contingent worker) where applicable.
• NON_LABOR_RESOURCE – Restricts the control to a specific non-labor resource; foreign key to PA_NON_LABOR_RESOURCES.
• START_DATE_ACTIVE and END_DATE_ACTIVE – Define the effective date range during which the control is valid.
• CHARGEABLE_FLAG – Indicates whether matching transactions may be charged to the project or task.
• BILLABLE_INDICATOR – Indicates whether matching transactions may be billed to the customer.
• SCHEDULED_EXP_ONLY – Restricts the control to scheduled expenditures only.
• EMPLOYEES_ONLY_FLAG and WORKPLAN_RES_ONLY_FLAG – Further qualify the population of resources subject to the control.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN – Standard EBS audit columns.

The documented primary key is PA_TRANSACTION_CONTROLS_PK, spanning PROJECT_ID, TASK_ID, EXPENDITURE_CATEGORY, START_DATE_ACTIVE, PERSON_ID, EXPENDITURE_TYPE, and NON_LABOR_RESOURCE. A unique index, PA_TRANSACTION_CONTROLS_U1, mirrors most of these columns (ordered as PROJECT_ID, EXPENDITURE_CATEGORY, TASK_ID, PERSON_ID, EXPENDITURE_TYPE, NON_LABOR_RESOURCE, START_DATE_ACTIVE) and represents the business-key candidate. Together these enforce that only one active control record exists per project/task/category/type/person/resource/date combination.

Common Use Cases and Queries

Typical uses include auditing which expenditure types are billable on a given project, validating that a person is permitted to charge time to a task, and reporting on chargeability rules before running cost or revenue distribution.

A common query returns all active controls for a project and task:

• SELECT project_id, task_id, expenditure_category, expenditure_type, person_id, non_labor_resource, chargeable_flag, billable_indicator, start_date_active, end_date_active FROM pa_transaction_controls WHERE project_id = :p_project_id AND task_id = :p_task_id AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE));

Another frequent pattern joins to PA_PROJECTS_ALL and PA_TASKS to report billable controls by project name and task number, filtered on BILLABLE_INDICATOR = 'Y'. Analysts also join to PA_EXPENDITURE_TYPES to translate EXPENDITURE_TYPE values into descriptive names for reporting, and to PA_PEOPLE or per_all_people_f via PERSON_ID when worker-level controls are in play.

Related Objects

The foreign key relationships documented in the ETRM metadata tie PA_TRANSACTION_CONTROLS to several core Projects tables:

• PA_PROJECTS_ALL – joined on PROJECT_ID; supplies project attributes such as project number and name.
• PA_TASKS – joined on TASK_ID; supplies task number and description.
• PA_EXPENDITURE_CATEGORIES – joined on EXPENDITURE_CATEGORY; provides category code and name.
• PA_EXPENDITURE_TYPES – joined on EXPENDITURE_TYPE; provides expenditure type and unit of measure.
• PA_NON_LABOR_RESOURCES – joined on NON_LABOR_RESOURCE; identifies the specific resource for non-labor controls.
• PA_PEOPLE / PER_ALL_PEOPLE_F – referenced through PERSON_ID when the control is person-specific.
• PA_EXPENDITURES_ALL / PA_COST_DISTRIBUTION_LINES_ALL – downstream tables whose chargeable/billable treatment depends on the controls defined here.

Because almost every expenditure entered into Oracle Projects can be validated against this table, it is a foundational reference for chargeability and billability logic across the PA module and for integration with Oracle Receivables billing flows.