Search Results cst_rev_rec_lines_n2

Overview

BOM.CST_REVENUE_RECOGNITION_LINES is a transactional table in the Oracle E-Business Suite Cost Management (CST) schema, owned physically by the BOM schema, that stores revenue recognition percentages for Order Management (OM) lines across accounting periods. Its central role is to support Cost of Goods Sold (COGS) matching and recognition. Data is sourced from two upstream systems: Oracle Order Management and Oracle Receivables. Receivables data is loaded through a Receivables-supplied API during the second phase of the Generate COGS Recognition Events concurrent request, while OM data is loaded through a Costing API invoked by OM when an order line is closed without ever being invoiced. The Matching program compares the percentage stored here against the latest percentage in CST_COGS_EVENTS for each period, and any difference is used to build COGS Recognition events.

The physical schema documented for ETRM 12.2.2 (also applicable to 12.1.1 architecture) lists 18 columns. The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10. Its heuristic Data Vault classification is link, indicating a table that resolves relationships between business entities — in this case, between an OM order line, a customer transaction line, a ledger, and an accounting period. This classification should be treated as a modeling suggestion rather than a strict architectural mandate.

Key Information Stored

The primary business-key candidate is defined by the unique index CST_REV_REC_LINES_U1 over REVENUE_OM_LINE_ID and ACCT_PERIOD_NUM. This composite effectively serves as the table's logical primary key, because it uniquely identifies a revenue recognition percent for a given order line and accounting period. There is no separate surrogate primary key column beyond this combination.

• REVENUE_OM_LINE_ID — the OM order line from oe_order_lines_all for which a revenue recognition percentage is relevant; determined by whether the line is invoicable or is the top model of a configuration.
• ACCT_PERIOD_NUM — a unique representation of an accounting period for a given set of books and application ID, corresponding to GL_PERIOD_STATUSES.EFFECTIVE_PERIOD_NUM.
• POTENTIALLY_UNMATCHED_FLAG — set to 'Y' whenever an action is taken that may have caused a Revenue/COGS mismatch; cleared to NULL after the concurrent request processes the row. Indexed via CST_REV_REC_LINES_N1.
• REVENUE_RECOGNITION_PERCENT — the recognition percentage value driving the matching logic.
• LEDGER_ID — the ledger context, indexed by the nonunique index CST_REV_REC_LINES_N2.
• CUSTOMER_TRX_LINE_ID — the Receivables transaction line contributing the AR data.
• INVENTORY_ITEM_ID — the item tied to the order line.
• OPERATING_UNIT_ID — the operating unit owning the transaction.
• LAST_EVENT_DATE — the date of the most recent event affecting recognition.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, plus concurrent program columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical scenarios include reconciling revenue-to-COGS percentages, identifying order lines flagged for potential mismatch, and tracing recognition data back to Receivables and Order Management sources.

To find records awaiting mismatch resolution:

• SELECT REVENUE_OM_LINE_ID, ACCT_PERIOD_NUM FROM BOM.CST_REVENUE_RECOGNITION_LINES WHERE POTENTIALLY_UNMATCHED_FLAG = 'Y';

To retrieve the recognition percentage for a specific line and period (exploiting the unique index):

• SELECT REVENUE_RECOGNITION_PERCENT FROM BOM.CST_REVENUE_RECOGNITION_LINES WHERE REVENUE_OM_LINE_ID = :line_id AND ACCT_PERIOD_NUM = :period_num;

To trace a recognition record to its Receivables origin:

• SELECT r.CUSTOMER_TRX_LINE_ID, r.REVENUE_RECOGNITION_PERCENT FROM BOM.CST_REVENUE_RECOGNITION_LINES r WHERE r.CUSTOMER_TRX_LINE_ID IS NOT NULL;

Related Objects

• OE_ORDER_LINES_ALL — joined on REVENUE_OM_LINE_ID = OE_ORDER_LINES_ALL.LINE_ID; supplies the invoicable or top-model order line.
• RA_CUSTOMER_TRX_LINES_ALL — joined on CUSTOMER_TRX_LINE_ID = CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID; supplies Receivables transaction detail.
• CST_COGS_EVENTS — holds the latest recognition percentage compared during matching to generate COGS Recognition events.
• GL_PERIOD_STATUSES — source of EFFECTIVE_PERIOD_NUM, aligning ACCT_PERIOD_NUM to accounting periods.
• Generate COGS Recognition Events concurrent program — populates this table via OPM/Receivables APIs and clears the mismatch flag.