Results for “pay_coin_anal_elements_pk”

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

Overview

HR.PAY_COIN_ANAL_ELEMENTS is a transactional table in the Oracle E-Business Suite HR (Payroll) schema that stores the definitions describing the make-up of a cash payment. In payroll and cash-management terms, a coin analysis records how a disbursed cash amount is broken down across individual monetary units — for example, how many notes or coins of each denomination were issued to satisfy a payment. Each row represents a single analysis line, pairing one pre-payment with one monetary unit and a quantity.

The table is classified in the ETRM metadata with the FND Design Data application short name PAY, resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and carries a VALID status in both the 12.1.1 and 12.2.2 releases. Because the object sits at the intersection of two parent entities, Pre-Payments and Monetary Units, maintaining a quantity on each intersection row, a Data Vault classification of link is the most appropriate modeling suggestion. It is not a pure hub, since no standalone business key is generated independently of its parents, and it is not a satellite, since it stores a measurable quantity rather than descriptive attributes of a single parent.

Key Information Stored

The physical schema documented for 12.2.2 exposes four columns. These are the essential attributes of every coin-analysis record:

• COIN_ANAL_ELEMENT_ID — NUMBER(15), system-generated. This is the surrogate primary key defined by the unique index PAY_COIN_ANAL_ELEMENTS_PK. It is the column most commonly referenced by the search term "pay_coin_anal_elements_pk".
• PRE_PAYMENT_ID — NUMBER(15), foreign key to PAY_PRE_PAYMENTS. Identifies the cash pre-payment whose make-up is being described. Indexed by the non-unique PAY_COIN_ANAL_ELEMENTS_FK1.
• MONETARY_UNIT_ID — NUMBER, foreign key to PAY_MONETARY_UNITS. Identifies the denomination or unit being counted. Indexed by the non-unique PAY_COIN_ANAL_ELEMENTS_FK2.
• NUMBER_OF_MONETARY_UNITS — NUMBER(15). The quantity of the referenced monetary unit contributing to the payment.

No business-key candidate beyond the surrogate primary key is documented; the natural uniqueness of a row is effectively the (PRE_PAYMENT_ID, MONETARY_UNIT_ID) combination, though the metadata does not register a unique constraint on that pair.

Common Use Cases and Queries

Reporting on coin analysis is primarily used to reconcile cash disbursements, particularly where payroll or third-party payments are issued in physical cash and the paying entity must account for the denomination mix.

• Retrieving all analysis lines for a given pre-payment to see the full denomination breakdown:

SELECT COIN_ANAL_ELEMENT_ID, MONETARY_UNIT_ID, NUMBER_OF_MONETARY_UNITS
FROM   HR.PAY_COIN_ANAL_ELEMENTS
WHERE  PRE_PAYMENT_ID = :p_pre_payment_id;

• Aggregating quantities per monetary unit across a range of payments for cash-requisition and vault-stocking reports:

SELECT MONETARY_UNIT_ID, SUM(NUMBER_OF_MONETARY_UNITS)
FROM   HR.PAY_COIN_ANAL_ELEMENTS
GROUP  BY MONETARY_UNIT_ID;

• Joining to PAY_MONETARY_UNITS to translate unit identifiers into human-readable denomination descriptions for payment audit trails.
• Serialising a pre-payment's full make-up as a consolidated string for cheque-printing or cash-advice documents.
• Detecting anomalies where the sum of quantities multiplied by unit values does not equal the payment amount, a common reconciliation test.

Related Objects

The ETRM documentation records two explicit foreign-key relationships and confirms that the table is referenced by the APPS and PUBLIC synonyms of PAY_COIN_ANAL_ELEMENTS itself. The most significant related objects are:

• HR.PAY_PRE_PAYMENTS — parent of the foreign key on PRE_PAYMENT_ID. Holds the payment header that the coin analysis describes.
• HR.PAY_MONETARY_UNITS — parent of the foreign key on MONETARY_UNIT_ID. Holds the definition of each monetary unit or denomination.
• APPS.PAY_COIN_ANAL_ELEMENTS — the APPS synonym through which application code and concurrent programs normally access the table.
• PUBLIC.PAY_COIN_ANAL_ELEMENTS — the PUBLIC synonym permitting access from schemas outside HR.
• PAY_PRE_PAYMENTS and its associated payment APIs, which populate the coin analysis rows as part of pre-payment processing.
• Payroll cash-requisition and cheque-printing concurrent programs that read this table when producing cash disbursement documents.

All joins should follow the documented foreign-key columns PRE_PAYMENT_ID and MONETARY_UNIT_ID, and queries can safely filter or aggregate on the surrogate key COIN_ANAL_ELEMENT_ID when a specific analysis line is required.