Search Results ce_cp_worksheet_lines_pk

Overview

CE_CP_WORKSHEET_LINES is a Cash Management (CE) table within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. Per the ETRM documentation, it stores "the Cash Position worksheet details." Its functional purpose is to persist the individual query lines that make up a Cash Position worksheet — the selection criteria, filters, and balance parameters an organization defines when projecting cash balances across legal entities, bank accounts, and transaction types. Each row represents one configured line on a worksheet, defining which transactions are included and under what conditions.

From a data modeling perspective, the metadata suggests a standalone Data Vault classification (heuristic, mined from the FK structure). This indicates the table is largely independent within the warehouse-style view of the schema, holding its own descriptive attributes rather than acting primarily as a junction between two hubs. Its foreign keys to FV_LEGAL_ENTITIES and AR_RECEIPT_METHODS provide the principal external linkage, but no dependent child tables were surfaced in the relationship data.

Key Information Stored

The surrograte primary key is WORKSHEET_LINE_ID, enforced by the CE_CP_WORKSHEET_LINES_PK constraint and reinforced by the unique index CE_CP_WORKSHEET_LINES_U1. This column uniquely identifies each worksheet line. The parent grouping is established by WORKSHEET_HEADER_ID, which links each line to its owning worksheet header.

The most significant columns include:

• WORKSHEET_HEADER_ID — the parent worksheet that owns this line.
• SOURCE_TYPE and EXT_SOURCE_TYPE — classify where the projected balance originates (for example, bank, AR, AP, or external source).
• INCLUDE_FLAG and INDICATIVE_FLAG — control whether the line participates in the computation and whether its amount is treated as indicative (non-binding).
• DESCRIPTION — user-facing label for the line.
• LEGAL_ENTITY_ID — foreign key to FV_LEGAL_ENTITIES, scoping the line to a legal entity.
• BANK_NAME and BANK_ACCOUNT_ID — identify the bank account being considered.
• BALANCE_TYPE, FLOAT_TYPE, and TRX_TYPE — define the accounting balance, float, and transaction classification.
• PAYMENT_METHOD and RECEIPT_METHOD_ID — channel filters; RECEIPT_METHOD_ID references AR_RECEIPT_METHODS.
• CLEARED_TRX_FLAG, OVERDUE_TRX_FLAG, and CUT_OFF_DAYS — define clearing, overdue, and time-window selection rules.
• AMOUNT — the associated monetary value.
• PAYROLL_ID — links the line to a specific payroll where applicable.
• CRITERIA_CATEGORY and CRITERIA1 through CRITERIA15 — a flexible set of up to fifteen user-defined filter criteria.
• CASHPOOL_ID and TRXN_SUBTYPE_CODE_ID — further categorize the line within pooling or transaction subtype dimensions.
• Standard audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY).

Common Use Cases and Queries

Typical usage centers on reconstructing or auditing the composition of a Cash Position worksheet. Analysts retrieve all lines belonging to a worksheet header to review inclusion rules, cut-off windows, and source types. A representative join pattern retrieves lines with their legal entity and receipt method labels:

• SELECT wl.worksheet_line_id, wl.description, wl.source_type, wl.amount, le.legal_entity_name FROM ce.ce_cp_worksheet_lines wl JOIN fv.fv_legal_entities le ON wl.legal_entity_id = le.legal_entity_id WHERE wl.worksheet_header_id = :header_id;
• Filtering on INCLUDE_FLAG = 'Y' isolates only active lines contributing to the projected balance.
• Grouping by BALANCE_TYPE, SOURCE_TYPE, or BANK_ACCOUNT_ID supports balance-breakdown reporting.
• Ordering by CUT_OFF_DAYS helps verify aging-based selection logic.

These queries support reconciliation, troubleshooting why a particular cash figure appears (or is missing), and migration activities when worksheets are copied between environments.

Related Objects

The documented foreign keys and structural relationships identify the following significant related objects:

• CE_CP_WORKSHEET_HEADERS — the parent table referenced by WORKSHEET_HEADER_ID, supplying worksheet-level context.
• FV_LEGAL_ENTITIES — referenced via LEGAL_ENTITY_ID for legal entity validation.
• AR_RECEIPT_METHODS — referenced via RECEIPT_METHOD_ID for receipt channel filtering.
• CE_BANK_ACCOUNTS — implied owner of BANK_ACCOUNT_ID for bank account detail.
• CE_CASH_POOLS — implied owner of CASHPOOL_ID for cash pooling configuration.
• AP/AR transaction tables — the operational sources evaluated by SOURCE_TYPE and the CRITERIA columns.
• GL_JE_* journal tables — underlying balance sources for balance-type projections.

Together these objects form the Cash Positioning framework, with CE_CP_WORKSHEET_LINES functioning as the granular, configurable rule store beneath each worksheet header.