Search Results cln_3c3_invoice_line_v




Overview
Explore in AI Chat

Overview

CLN_3C3_INVOICE_LINE_V is an APPS-owned database view in the Oracle E-Business Suite Release 12.1.1 and 12.2.2 environment, registered under the Supply Chain Trading Connector for RosettaNet (CLN) product family. Its documented purpose is to expose invoice line details required to generate outbound RosettaNet 3C3 business documents — the standard message used for invoice notification and reconciliation between trading partners. The view denormalizes transactional invoice line data from Oracle Receivables into a flat, integration-friendly shape so that the RosettaNet message generation logic can retrieve quantity, pricing, item, and sales order context in a single query without navigating multiple application tables.

Underlying Base Objects

The view text is defined over four documented base objects:

  • RA_CUSTOMER_TRX_LINES_ALL (SYNONYM) — the primary source of invoice/credit memo lines, aliased as L. This is the driving table.
  • AR_MEMO_LINES_ALL_VL (VIEW) — supplies memo-line names and descriptions for lines linked to a credit or memo line, aliased as M.
  • MTL_SYSTEM_ITEMS (SYNONYM) — provides inventory item descriptions, aliased as I.
  • FA_RX_FLEX_PKG (PACKAGE) — invoked through FA_RX_FLEX_PKG.GET_VALUE(401, 'MSTK', ...) to resolve the item value for non-memo lines.

Joins are outer ((+)) for the item and memo lookups, with org_id correlation enforced between the line, item, and memo records. The view filters to LINK_TO_CUST_TRX_LINE_ID IS NULL and LINE_TYPE = 'LINE', thereby excluding tax, freight, and other non-line components, and eliminating lines that are linked to a parent line.

Key Columns

The view exposes sixteen columns. The most operationally significant are:

Common Use Cases and Queries

The principal use case is outbound 3C3 invoice generation, where the connector reads line quantity and pricing to populate the RosettaNet message payload. A typical query retrieves lines for a given transaction:

SELECT customer_trx_line_id,
       line_number,
       quantity,
       unit_selling_price,
       line_amount,
       item,
       uom
FROM   apps.cln_3c3_invoice_line_v
WHERE  customer_trx_id = :p_trx_id
ORDER  BY line_number;

A second scenario validates credited quantities, since quantity falls back to QUANTITY_CREDITED when invoiced quantity is null. Reconciliation queries joining the view to RA_CUSTOMER_TRX_ALL by customer_trx_id allow partners to confirm credit memo lines. Finally, the view supports troubleshooting of RosettaNet interface errors by exposing the exact item, reference number, and sales order attributes transmitted in the outbound document.