Results for “pay_coin_anal_elements”

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

Overview

PAY_COIN_ANAL_ELEMENTS is a Payroll (PAY) module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the monetary unit quantities used by the automatic cash make-up process, which determines the denomination mix (banknotes and coins) that a payroll or payments run should disburse when paying employees or payees in cash. Each row binds a prepayment definition to a specific monetary unit and records how many units of that denomination are required.

In heuristic Data Vault terms, the foreign key structure (PRE_PAYMENT_ID → PAY_PRE_PAYMENTS and MONETARY_UNIT_ID → PAY_MONETARY_UNITS, with no dependent child tables) suggests this object behaves as a link table — it resolves a many-to-many-style association between a prepayment context and the monetary units that satisfy it. It is a modeling suggestion only; the delivered EBS schema is a conventional relational table with a surrogate primary key.

Key Information Stored

The table is documented with four columns in the 12.2.2 physical schema. The most significant are:

• COIN_ANAL_ELEMENT_ID — Surrogate primary key, enforced by the unique index PAY_COIN_ANAL_ELEMENTS_PK. It uniquely identifies each denomination-quantity record and is the column referenced by any downstream or custom dependent object.
• PRE_PAYMENT_ID — Foreign key to PAY_PRE_PAYMENTS. It ties the denomination breakdown to the prepayment (cash make-up) definition being processed. This is the principal business-key candidate for grouping rows into a single analytical result set.
• MONETARY_UNIT_ID — Foreign key to PAY_MONETARY_UNITS. It identifies the specific currency denomination (note or coin) whose quantity is being recorded for the prepayment.
• NUMBER_OF_MONETARY_UNITS — The quantity of the referenced monetary unit to be disbursed. This is the core fact value of the row, and it is the column most commonly summed in reporting.

The unique index PAY_COIN_ANAL_ELEMENTS_PK on COIN_ANAL_ELEMENT_ID is the only documented unique constraint. The natural business key is therefore the combination of PRE_PAYMENT_ID and MONETARY_UNIT_ID, since each prepayment should carry exactly one quantity per denomination; this pairing should be treated as the effective alternate key when validating or loading data.

Common Use Cases and Queries

Typical scenarios include verifying the denomination make-up generated for a cash payment run, auditing that the sum of denominations reconciles to the net pay amount, and driving cash requisition or vault-loading reports for payroll disbursement.

A standard join to obtain readable denomination detail is:

• SELECT ce.coin_anal_element_id, ce.pre_payment_id, mu.monetary_unit_name, ce.number_of_monetary_units FROM pay_coin_anal_elements ce, pay_monetary_units mu WHERE ce.monetary_unit_id = mu.monetary_unit_id AND ce.pre_payment_id = :p_pre_payment_id ORDER BY mu.monetary_unit_value;

To reconcile the total cash value produced for a prepayment, multiply quantity by unit value and aggregate:

• SELECT ce.pre_payment_id, SUM(ce.number_of_monetary_units * mu.monetary_unit_value) total_cash FROM pay_coin_anal_elements ce, pay_monetary_units mu WHERE ce.monetary_unit_id = mu.monetary_unit_id GROUP BY ce.pre_payment_id;

Because the table is populated by the cash make-up process rather than by end-user maintenance, it is most often consumed in read-only reporting extracts and pre-disbursement validation scripts.

Related Objects

The following objects are most significant to PAY_COIN_ANAL_ELEMENTS, based on the documented foreign key relationships:

• PAY_PRE_PAYMENTS — Parent of PRE_PAYMENT_ID; defines the prepayment (cash make-up) header to which each denomination record belongs.
• PAY_MONETARY_UNITS — Parent of MONETARY_UNIT_ID; supplies denomination descriptions and unit values used to translate quantities into cash amounts.
• PAY_COIN_ANAL_ELEMENTS_PK — Unique index on COIN_ANAL_ELEMENT_ID; enforces row identity and supports direct lookups.
• PAY_PAYROLL_ACTIONS / PAY_ASSIGNMENT_ACTIONS (via the prepayment context) — the payroll processing entities whose cash make-up output this table ultimately reflects.
• PAY_PAYMENT_METHODS — determines whether a payment is made in cash, i.e., whether coin analysis applies at all for a given assignment or prepayment.