consolidatedexchangerate

-- ============================================================
-- Table  : consolidatedexchangerate — Period consolidation rates: one row per accounting book, posting period and from/to subsidiary pair, carrying the current, average and historical rate flavors side by side.
-- Purpose: Column-selected read of consolidatedexchangerate — auto-generated from field metadata
-- Grain  : One row per id + accountingbook + postingperiod + fromsubsidiary + tosubsidiary
-- Caution: three rate columns, not three rows — pick currentrate, averagerate or historicalrate deliberately per measure (balance-sheet vs P&L vs equity); see quirks #oneworld.
-- Notes  : Auto-generated skeleton for NetSuite data landed in your lakehouse from a SuiteAnalytics Connect (or SuiteQL) extract — it never addresses live NetSuite. Identifiers are lowercase as NetSuite2.com renders them. Select-type columns hold numeric internal ids: BUILTIN.DF() display resolution exists only at extraction time, so decode ids by joining the landed list records; see quirks #display-values. Check-box columns arrive as 'T'/'F' strings; see quirks #tf-booleans.
-- ============================================================
SELECT
  t.id AS "Internal ID",
  t.accountingbook AS "Accounting book (internal id)",
  t.postingperiod AS "Posting period (internal id)",
  t.fromsubsidiary AS "From subsidiary (internal id)",
  t.tosubsidiary AS "To subsidiary (internal id)",
  t.fromcurrency AS "From currency (internal id)",
  t.tocurrency AS "To currency (internal id)",
  t.currentrate AS "Current rate — period-end balances",
  t.averagerate AS "Average rate — period activity",
  t.historicalrate AS "Historical rate — equity and fixed items",
  t.externalid AS "External ID"
FROM <catalog>.<schema>.consolidatedexchangerate t
WHERE
  1 = 1  -- no partition column on this record; the filters below are optional
  -- AND t.id = <id>
  -- AND t.accountingbook = <accountingbook>
  -- AND t.postingperiod = <postingperiod>
  -- AND t.fromsubsidiary = <fromsubsidiary>
  -- AND t.tosubsidiary = <tosubsidiary>
  -- no lastmodifieddate on this record — there is no watermark column, so incremental extraction must full-refresh it (see quirks #deletes)
ORDER BY t.id;