Search Results pay_ca_pmed_accounts




Overview
Explore in AI Chat

Overview

PAY_CA_PMED_ACCOUNTS is a Payroll (PAY) module table owned by the HR schema that stores Canadian Provincial Medical account information. It holds the account identifier definitions used by Canadian payroll processing when provincial medical premiums or similar provincial medical plan deductions are calculated and remitted. Because Canadian payroll requires that employer and employee provincial medical contributions reference a valid account number, this table serves as the reference source for those account values within an organization's business group.

The table is classified as VALID in the ETRM 12.2.2 physical schema, containing 43 columns. Its primary key is PAY_CA_PMED_ACCOUNTS_PK, defined on the SOURCE_ID column. Two foreign keys are documented, both referencing HR_ALL_ORGANIZATION_UNITS: one from ORGANIZATION_ID and one from BUSINESS_GROUP_ID.

Using heuristic Data Vault classification mined from the FK structure, this object is best modeled as a link. It resolves a relationship between a business group/organization and a provincial medical account, and it carries descriptive attributes such as the account number, enabled flag, and description, functioning as an intersection or association record keyed by a surrogate SOURCE_ID.

Key Information Stored

The following columns are the most significant for understanding and querying this table:

  • SOURCE_ID — The surrogate primary key (PAY_CA_PMED_ACCOUNTS_PK). It is the stable system-generated identifier for each provincial medical account record.
  • ORGANIZATION_ID — Foreign key to HR_ALL_ORGANIZATION_UNITS; identifies the organization associated with the account.
  • BUSINESS_GROUP_ID — Foreign key to HR_ALL_ORGANIZATION_UNITS; scopes the record to a business group, which is the primary security and data-partitioning boundary in Oracle HRMS/Payroll.
  • ACCOUNT_NUMBER — The actual provincial medical account number, a key business attribute used on remittance and reporting.
  • ENABLED — Status flag indicating whether the account is active and available for use.
  • DESCRIPTION — Free-form descriptive text for the account.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 … ATTRIBUTE30 — The standard Oracle EBS descriptive flexfield (DFF) columns, providing 30 configurable attribute segments for customer-specific extensions.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard WHO (audit) columns tracking row creation and modification.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Applications framework to detect concurrent updates.

The surrogate key is SOURCE_ID; ACCOUNT_NUMBER combined with BUSINESS_GROUP_ID is the most likely business-key candidate, since account numbers must be unique within a business group context.

Common Use Cases and Queries

Typical use cases include validating that a provincial medical account exists and is enabled before payroll processing, and reporting on accounts across organizations within a business group.

Retrieving active accounts for a business group:

  • SELECT account_number, description FROM pay_ca_pmed_accounts WHERE business_group_id = :p_bg_id AND enabled = 'Y';

Joining to the organization unit for reporting:

  • SELECT a.account_number, o.name FROM pay_ca_pmed_accounts a, hr_all_organization_units o WHERE a.organization_id = o.organization_id AND a.business_group_id = :p_bg_id;

Reporting queries frequently filter by ENABLED = 'Y' and by BUSINESS_GROUP_ID to enforce row-level security, and may extract DFF attribute segments for regulatory reporting.

Related Objects

  • HR_ALL_ORGANIZATION_UNITS — Referenced twice via foreign keys: PAY_CA_PMED_ACCOUNTS.ORGANIZATION_ID and PAY_CA_PMED_ACCOUNTS.BUSINESS_GROUP_ID.
  • PAY_CA_PMED_ACCOUNTS_PK — The primary key constraint enforcing uniqueness on SOURCE_ID.
  • Canadian payroll element and deduction definitions that consume the account number during provincial medical premium processing.
  • Payroll balance and remittance reporting objects that reference the provincial medical account for statutory reporting.

Because SOURCE_ID is the PK and the table links organizations to accounts, downstream payroll processes select it to resolve the correct account number at calculation and remittance time.