RA_CUSTOMER_TRX_LINES_ALL

-- ============================================================
-- Table  : RA_CUSTOMER_TRX_LINES_ALL — Receivables transaction lines — mixed row kinds under each transaction: LINE rows carry the goods/services amounts, TAX and FREIGHT rows attach to their parent line, CB rows carry chargebacks
-- Purpose: Column-selected read of RA_CUSTOMER_TRX_LINES_ALL — auto-generated from field metadata
-- Grain  : One row per business unit (ORG_ID) + CUSTOMER_TRX_LINE_ID
-- Caution: Mixed line kinds share the table — filter LINE_TYPE = 'LINE' before summing revenue, or tax and freight inflate it; TAX/FREIGHT rows point at their parent via LINK_TO_CUST_TRX_LINE_ID
-- Notes  : Auto-generated skeleton for Oracle Fusion Cloud data landed in your lakehouse by a BICC extract — there is no SQL path to the SaaS database. Column names follow Oracle's table documentation — if your landed data still carries PVO attribute headers, map names first; see quirks #pvo-drift. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
  t.CUSTOMER_TRX_LINE_ID AS "Surrogate key of the line",
  t.CUSTOMER_TRX_ID AS "The transaction header",
  t.ORG_ID AS "The business unit owning the line",
  t.LINE_NUMBER AS "Line number within the transaction",
  CASE t.LINE_TYPE WHEN 'LINE' THEN 'Standard line' WHEN 'TAX' THEN 'Tax line' WHEN 'FREIGHT' THEN 'Freight line' WHEN 'CB' THEN 'Chargeback line' ELSE t.LINE_TYPE END AS "Row kind — LINE rows carry the goods/services amounts; TAX, FREIGHT, and CB rows share the table",  -- lookup: STD_LINE_TYPE
  t.LINK_TO_CUST_TRX_LINE_ID AS "For TAX and FREIGHT rows, the LINE row they attach to (self-referencing)",
  t.INVENTORY_ITEM_ID AS "Item on the line — description-only in the dictionary, no formal FK; mutually exclusive with the memo line",
  t.WAREHOUSE_ID AS "Ship-from organization for taxation — description-only, no formal FK",
  t.MEMO_LINE_SEQ_ID AS "Standard memo line, for non-item lines",
  t.QUANTITY_INVOICED AS "Quantity invoiced",
  t.QUANTITY_CREDITED AS "Quantity credited (credit memo lines)",
  t.UNIT_SELLING_PRICE AS "Selling price per unit",
  t.EXTENDED_AMOUNT AS "Line amount",
  t.REVENUE_AMOUNT AS "Line revenue amount",
  t.UOM_CODE AS "Unit of measure",
  t.SALES_ORDER AS "Originating sales order reference — text, no FK; the AR → OM link is configuration-defined",
  t.SALES_ORDER_LINE AS "Originating sales order line reference — text, no FK"
FROM <catalog>.<schema>.RA_CUSTOMER_TRX_LINES_ALL t
WHERE
  t.ORG_ID = <business_unit_id>  -- business unit — Fusion _ALL means BU, not the EBS operating unit; see quirks guide #all-means-bu
  -- AND t.CUSTOMER_TRX_LINE_ID = <CUSTOMER_TRX_LINE_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.CUSTOMER_TRX_LINE_ID;