The NetSuite Quirks Guide

-- Everything on this site reads the LANDED copy of a record — the Delta
-- output of a SuiteAnalytics Connect (or SuiteQL) extract in your own
-- lakehouse. No snippet here addresses live NetSuite.
SELECT
  t.id,
  t.tranid,
  t.trandate,
  t.entity,
  t.foreigntotal
FROM <catalog>.<schema>.transaction t
WHERE
  1 = 1
  -- AND t.type = '<type>'  -- discover values in-account: SELECT DISTINCT type
  -- AND t.lastmodifieddate >= TIMESTAMP '<watermark>'  -- inserts and updates only — deletes never appear here; diff deletedrecord, which is itself blind to line-level deletes (see quirks #deletes)
ORDER BY t.trandate;

-- The delivered ways analytics data leaves NetSuite, all reading the SAME
-- NetSuite2.com source:
--   SuiteAnalytics Connect - ODBC / JDBC / ADO.NET drivers; the bulk path
--   SuiteQL                - SQL-92 in-account (N/query) or over REST at
--                            /services/rest/query/v1/suiteql, which caps a
--                            response at 100,000 rows and pages with
--                            limit / offset
--   Records Catalog        - live metadata, in-account only (it needs a
--                            NetSuite login, so nothing on this site cites it)
-- The legacy NetSuite.com data source is REMOVED as of 2026.1 - NetSuite2.com
-- is the one door.

-- Connect metadata records list what your credentials can actually see:
SELECT * FROM oa_tables;   -- one row per exposed record
SELECT * FROM oa_columns;  -- one row per exposed column

-- OneWorld partitions by SUBSIDIARY - and the column is on the LINE, not on
-- the transaction header. The header record has no subsidiary column at all.
SELECT
  tl.transaction,
  tl.subsidiary,
  tl.item,
  tl.quantity,
  tl.netamount
FROM <catalog>.<schema>.transactionline tl
WHERE
  tl.subsidiary = <subsidiary_id>  -- OneWorld partition — see quirks guide #oneworld
  AND tl.mainline = 'F'  -- line grain; 'T' is the document header row — see quirks guide #transaction-spine
;

-- Anything that carries the column anchors it the same way, records with a
-- location column included:
SELECT
  iil.item,
  iil.location,
  iil.quantityonhand
FROM <catalog>.<schema>.inventoryitemlocations iil
WHERE
  iil.location = <location_id>  -- location scope — see quirks guide #oneworld
;

-- Two rate records, two jobs. currencyrate is the DAILY transaction rate,
-- one row per currency pair per effective date:
SELECT
  cr.basecurrency,
  cr.transactioncurrency,
  cr.effectivedate,
  cr.exchangerate
FROM <catalog>.<schema>.currencyrate cr
WHERE cr.effectivedate <= DATE '<DATE_TO>';

-- consolidatedexchangerate is what CONSOLIDATION uses: one row per accounting
-- book + posting period + from/to subsidiary pair, carrying three rate
-- flavors as three COLUMNS. Pick one deliberately per measure - they are not
-- interchangeable, and averaging them is never right.
SELECT
  cer.accountingbook,
  cer.postingperiod,
  cer.fromsubsidiary,
  cer.tosubsidiary,
  cer.currentrate,      -- balance-sheet style restatement
  cer.averagerate,      -- period-average style restatement
  cer.historicalrate    -- historical/equity style restatement
FROM <catalog>.<schema>.consolidatedexchangerate cer;

-- Every transactional document lives in ONE table family. A sales order, a
-- purchase order, a fulfillment, a receipt and a journal are all rows in
-- transaction / transactionline, told apart by the type column.
--
-- Oracle publishes no list of the type values, so this reference ships none.
-- Read them from your own account once and keep them in your own mapping:
SELECT DISTINCT t.type
FROM <catalog>.<schema>.transaction t;

-- Header grain: Oracle's own SuiteQL examples filter the header row with
-- mainline = 'T'. Line-grain analytics take the inverse.
SELECT
  t.id,
  t.tranid,
  t.trandate,
  t.postingperiod,
  tl.item,
  tl.quantity,
  tl.netamount
FROM <catalog>.<schema>.transaction t
JOIN <catalog>.<schema>.transactionline tl
  ON tl.transaction = t.id
WHERE
  tl.subsidiary = <subsidiary_id>  -- OneWorld partition — see quirks guide #oneworld
  AND tl.mainline = 'F'  -- line grain; 'T' is the document header row — see quirks guide #transaction-spine
  -- AND t.type = '<type>'  -- discover values in-account: SELECT DISTINCT type
;

-- Documents chain to each other rather than nesting: the line records where
-- it came from, so a fulfillment line points back at its order line.
SELECT
  tl.transaction,
  tl.uniquekey,
  tl.createdfrom     -- the upstream transaction (internal id)
FROM <catalog>.<schema>.transactionline tl
WHERE tl.createdfrom IS NOT NULL;

-- Status is a real, populated column - on the transaction header, and on the
-- fulfillment and pick records too. What does not exist is a published list of
-- its VALUES: Oracle enumerates them nowhere public, exactly as with the type
-- discriminator, so this reference ships none of either.

-- Statuses are TYPE-SCOPED. The same stored value under two document types is
-- not the same state, so never compare a status across types and never filter
-- one without pinning the type beside it. Read the real pairs from your own
-- account:
SELECT DISTINCT
  t.type,
  t.status
FROM <catalog>.<schema>.transaction t
ORDER BY t.type, t.status;

-- Then build your OWN decode dimension from that result and curate the labels
-- against what your account's UI shows for each pair:
CREATE OR REPLACE TABLE <catalog>.<schema>.transaction_status_decode AS
SELECT DISTINCT
  t.type                AS transaction_type,
  t.status              AS status_value,
  CAST(NULL AS STRING)  AS status_label   -- fill in from your account's UI
FROM <catalog>.<schema>.transaction t;

-- And decode by joining it, the way every other coded column in this source is
-- decoded:
SELECT
  t.id,
  t.tranid,
  t.trandate,
  d.status_label
FROM <catalog>.<schema>.transaction t
LEFT JOIN <catalog>.<schema>.transaction_status_decode d
  ON  d.transaction_type = t.type
  AND d.status_value     = t.status
WHERE
  1 = 1
  -- AND t.type = '<type>'  -- discover values in-account: SELECT DISTINCT type
;

-- Treat that decode table as CODE, not as a lookup you can copy between
-- accounts: a customer configuration adds states, and a stale label is worse
-- than a raw value because it looks answered.

-- Three different mechanisms point one document at another, and they are not
-- interchangeable:
--
--   1. transactionline.createdfrom  - the upstream TRANSACTION the line was
--      created from. Cheap, but header-grain: it names the document, not the
--      line.
--   2. nexttransactionlinelink      - LINE to LINE, travelling downstream.
--   3. previoustransactionlinelink  - the same links, read upstream.
--
-- Pick the table that matches your direction of travel. Joining both "to be
-- safe" multiplies the chain by itself.

-- Downstream from a sales-order line: what did this line become?
SELECT
  tl.transaction        AS order_doc,
  tl.id                 AS order_line,
  tl.item,
  tl.quantity,
  ntll.nextdoc          AS downstream_doc,
  ntll.nexttype         AS downstream_type,
  ntll.nextline         AS downstream_line,
  ntll.linktype,
  ntll.foreignamount
FROM <catalog>.<schema>.transactionline tl
JOIN <catalog>.<schema>.nexttransactionlinelink ntll
  ON  ntll.previousdoc  = tl.transaction
  AND ntll.previousline = tl.id
WHERE
  tl.subsidiary = <subsidiary_id>  -- OneWorld partition — see quirks guide #oneworld
  AND tl.mainline = 'F'  -- line grain; 'T' is the document header row — see quirks guide #transaction-spine
;

-- Upstream from an invoice line, the mirror read - same shape, other table:
SELECT
  tl.transaction        AS invoice_doc,
  ptll.previousdoc      AS upstream_doc,
  ptll.previoustype     AS upstream_type,
  ptll.previousline     AS upstream_line
FROM <catalog>.<schema>.transactionline tl
JOIN <catalog>.<schema>.previoustransactionlinelink ptll
  ON  ptll.nextdoc  = tl.transaction
  AND ptll.nextline = tl.id
WHERE
  tl.subsidiary = <subsidiary_id>  -- OneWorld partition — see quirks guide #oneworld
  AND tl.mainline = 'F'  -- line grain; 'T' is the document header row — see quirks guide #transaction-spine
;

-- FAN-OUT is the thing that bites. One order line can produce many fulfillment
-- lines and many invoice lines, so a chained join returns the order line once
-- per downstream line. Count DISTINCT, or collapse the links first:
SELECT
  COUNT(DISTINCT tl.transaction)  AS orders,        -- NOT COUNT(*)
  COUNT(DISTINCT ntll.nextdoc)    AS linked_documents
FROM <catalog>.<schema>.transactionline tl
JOIN <catalog>.<schema>.nexttransactionlinelink ntll
  ON  ntll.previousdoc  = tl.transaction
  AND ntll.previousline = tl.id
WHERE
  tl.subsidiary = <subsidiary_id>  -- OneWorld partition — see quirks guide #oneworld
  AND tl.mainline = 'F'  -- line grain; 'T' is the document header row — see quirks guide #transaction-spine
;

-- The order-to-cash chain these links traverse - quote, sales order, item
-- fulfillment, invoice, customer payment - is five type values on ONE spine.
-- The record map on this site names which analytics table each of those
-- documents lives in; the answer is always transaction.

-- transactionaccountingline (TAL) has NO id of its own. Its key is the
-- transaction, the transaction line, and the ACCOUNTING BOOK - so with
-- multi-book accounting on, every line's accounting detail repeats once per
-- book (up to five). Without a book anchor, every amount multiplies.
SELECT
  tal.transaction,
  tal.transactionline,
  tal.accountingbook,
  tal.account,
  tal.amount,       -- subsidiary BASE currency
  tal.credit,
  tal.debit,
  tal.netamount
FROM <catalog>.<schema>.transactionline tl
JOIN <catalog>.<schema>.transactionaccountingline tal
  ON  tal.transaction     = tl.transaction
  AND tal.transactionline = tl.id
  AND tal.accountingbook = <accounting_book_id>
WHERE
  tl.subsidiary = <subsidiary_id>  -- OneWorld partition — see quirks guide #oneworld
  AND tl.mainline = 'F'  -- line grain; 'T' is the document header row — see quirks guide #transaction-spine
;
-- TAL duplicates once per accounting book (max 5) and is stated in the subsidiary's base currency; TL is transaction currency — see quirks #tal-and-books

-- Which book is the primary one is a stored flag, not a constant - read it
-- rather than hard-coding an id:
SELECT ab.id, ab.name, ab.isprimary
FROM <catalog>.<schema>.accountingbook ab;

-- Currency discipline: the LINE carries transaction-currency amounts
-- (foreignamount, creditforeignamount, debitforeignamount); TAL carries the
-- subsidiary's base currency. Mixing them in one SUM is the quiet way to
-- produce a number nobody can tie out.
--
-- And never join the HEADER straight to TAL: one header has many lines, so a
-- direct join fans the accounting detail out across all of them. The only
-- supported path is transaction -> transactionline -> transactionaccountingline,
-- which is why this reference draws no such edge.

-- Select-type columns hold NUMERIC INTERNAL IDS, not the text the UI shows.
-- t.entity is an id; tl.item is an id; t.location is an id.
SELECT
  t.id,
  t.entity,     -- internal id, not a customer name
  t.location,   -- internal id, not a warehouse name
  t.currency    -- internal id, not 'USD'
FROM <catalog>.<schema>.transaction t;

-- BUILTIN.DF() resolves a display value - but it is an EXTRACTION-TIME
-- device, evaluated by NetSuite while the query runs there. It does not exist
-- in Databricks, so in the lakehouse you decode by joining the landed records:
SELECT
  t.id,
  t.tranid,
  c.companyname   AS customer_name,
  l.name          AS location_name,
  cur.symbol      AS currency_iso_code
FROM <catalog>.<schema>.transaction t
LEFT JOIN <catalog>.<schema>.customer c ON c.id = t.entity
LEFT JOIN <catalog>.<schema>.location l ON l.id = t.location
LEFT JOIN <catalog>.<schema>.currency cur ON cur.id = t.currency
WHERE
  1 = 1
  -- AND t.lastmodifieddate >= TIMESTAMP '<watermark>'  -- inserts and updates only — deletes never appear here; diff deletedrecord, which is itself blind to line-level deletes (see quirks #deletes)
;

-- One trap worth memorizing on the currency record: the ISO code lives in
-- symbol, and displaysymbol holds the glyph. Joining on the wrong one gives
-- you a column full of dollar signs.

-- The practical rule: land the list records you decode against (customer,
-- vendor, item, location, subsidiary, currency, accountingperiod) in the same
-- extract cadence as the facts, or your labels drift behind your ids.

-- Customization is not an edge case in NetSuite - it is how most accounts
-- are configured, and it shows up in the schema by PREFIX.
--
--   custbody_    custom column on a transaction BODY
--   custcol_     custom column on a transaction LINE
--   custentity_  custom column on an entity record (customer, vendor, ...)
--   custitem_    custom column on an item record
--
-- These columns exist in one account and not the next, so nothing prefixed
-- this way is cataloged here - your account's Records Catalog is the only
-- authority for yours.
SELECT
  t.id,
  t.tranid
  -- , t.custbody_<your_field>   -- account-specific; confirm before relying on it
FROM <catalog>.<schema>.transaction t;

-- Custom RECORD types become tables of their own, named customrecord_ plus
-- the id given at creation. Treat them exactly like stock records once
-- landed - and expect their names to differ account to account:
--   SELECT * FROM customrecord_<your_record_id>;

-- There is no query that lists your account's custom records. The in-account
-- Records Catalog is the discovery authority - nothing in the landed copy
-- enumerates them. The deletion ledger is not a discovery mechanism: it only
-- ever sees records that were DELETED. What it does give you is a flag, so a
-- pipeline reconciling deletes can tell a custom record apart from a stock
-- one instead of failing on an unknown name:
SELECT
  dr.recordtypeid,
  dr.name,
  dr.iscustomrecord   -- 'T' when the DELETED record was a custom one
FROM <catalog>.<schema>.deletedrecord dr;

-- One item record covers every item type - inventory, non-inventory, service,
-- kit, assembly, group - and itemtype is the discriminator. Most columns are
-- meaningful for some types and null for the rest.
SELECT DISTINCT
  i.itemtype,
  i.subtype
FROM <catalog>.<schema>.item i;

-- So pin the type BEFORE aggregating anything numeric. A costing method means
-- one thing on an assembly and another on a service item, and a column that is
-- null for half the types averages to a number with no referent:
SELECT
  i.itemtype,
  COUNT(*)                      AS item_count,
  COUNT(i.costingmethod)        AS with_costing_method
FROM <catalog>.<schema>.item i
WHERE i.isinactive = 'F'   -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
GROUP BY i.itemtype;

-- The generic member record this catalog ships in place of the per-type
-- clones: itemmember, the component lines behind a parent item. Both of its
-- item pointers hit the SAME master, so a readable component list joins item
-- twice under two aliases:
SELECT
  parent.itemid     AS parent_item,
  parent.itemtype   AS parent_item_type,
  comp.itemid       AS component_item,
  im.quantity,
  im.bomquantity,
  im.memberunit,    -- TEXT, not a unit id - it cannot be joined
  im.componentyield,
  im.effectivedate
FROM <catalog>.<schema>.itemmember im
JOIN <catalog>.<schema>.item parent ON parent.id = im.parentitem
JOIN <catalog>.<schema>.item comp   ON comp.id   = im.item
WHERE im.obsoletedate IS NULL
ORDER BY parent.itemid, im.linenumber;

-- What NOT to look for: a table per item type. The analytics catalog does list
-- per-type record pages, but they carry no fields of their own - they exist to
-- hold joins - so there is nothing to extract from them. The unified master
-- plus the generic bridges is the whole model.

-- Manufacturing has master data of its own, but the DOCUMENTS are spine rows.
-- The work order, the issue of components, the completion, the assembly build
-- and unbuild, the close - every one of them is a type value on
-- transaction / transactionline, exactly like a sales order.
--
-- Oracle publishes no list of those type values either, so read them once from
-- your own account and keep the mapping yourself:
SELECT DISTINCT t.type
FROM <catalog>.<schema>.transaction t;

-- Which means WIP analysis is a SPINE query. Component consumption and
-- finished-goods receipt are inventory-affecting lines like any other, so
-- material usage comes from the line record joined to the item master - not
-- from the BOM, which is the plan rather than what happened:
SELECT
  t.tranid,
  t.trandate,
  i.itemid,
  tl.quantity,
  tl.location
FROM <catalog>.<schema>.transaction t
JOIN <catalog>.<schema>.transactionline tl
  ON tl.transaction = t.id
JOIN <catalog>.<schema>.item i
  ON i.id = tl.item
WHERE
  tl.subsidiary = <subsidiary_id>  -- OneWorld partition — see quirks guide #oneworld
  AND tl.mainline = 'F'  -- line grain; 'T' is the document header row — see quirks guide #transaction-spine
  -- AND t.type = '<type>'  -- discover values in-account: SELECT DISTINCT type
  -- AND t.lastmodifieddate >= TIMESTAMP '<watermark>'  -- inserts and updates only — deletes never appear here; diff deletedrecord, which is itself blind to line-level deletes (see quirks #deletes)
;

-- The one record that points back at the order: manufacturingoperationtask.
-- Its workorder column holds a transaction internal id, which is what makes a
-- standard-versus-actual read possible at all.
  -- no lastmodifieddate on this record — there is no watermark column, so incremental extraction must full-refresh it (see quirks #deletes)
SELECT
  t.tranid,
  mot.operationsequence,
  mot.title,
  mot.inputquantity,
  mot.completedquantity,
  mot.setuptime      AS standard_setup_minutes,
  mot.actualsetuptime,
  mot.runrate        AS standard_minutes_per_unit,
  mot.actualruntime
FROM <catalog>.<schema>.manufacturingoperationtask mot
JOIN <catalog>.<schema>.transaction t
  ON t.id = mot.workorder
ORDER BY t.tranid, mot.operationsequence;

-- The master data behind the order IS a set of real records - and it is
-- date-effective, so pick the revision in force rather than the newest one:
SELECT
  b.name           AS bom_name,
  br.name          AS revision_name,
  comp.itemid      AS component_item,
  brc.quantity,
  brc.componentyield
FROM <catalog>.<schema>.bom b
JOIN <catalog>.<schema>.bomrevision br
  ON br.billofmaterials = b.id
JOIN <catalog>.<schema>.bomrevisioncomponent brc
  ON brc.bomrevision = br.id
JOIN <catalog>.<schema>.item comp
  ON comp.id = brc.item
-- The revision currently in force is normally OPEN-ENDED, so a plain BETWEEN
-- on the two dates drops exactly the revision you usually want:
WHERE br.effectivestartdate <= DATE '<DATE_TO>'
  AND (br.effectiveenddate IS NULL OR br.effectiveenddate >= DATE '<DATE_TO>')
ORDER BY b.name, comp.itemid;

-- The modification stamp catches INSERTS AND UPDATES ONLY. A deleted row
-- simply stops existing - it never appears in a watermark window, so a
-- watermark-only pipeline keeps deleted documents forever.
SELECT
  t.id,
  t.lastmodifieddate
FROM <catalog>.<schema>.transaction t
WHERE
  1 = 1
  -- AND t.lastmodifieddate >= TIMESTAMP '<watermark>'  -- inserts and updates only — deletes never appear here; diff deletedrecord, which is itself blind to line-level deletes (see quirks #deletes)
;

-- deletedrecord is the ledger of what went away - record type, name, script
-- id, when, and by whom. Note what is NOT there: the deleted row's internal
-- id. The analytics record does not publish one, so this surface tells you
-- WHICH tables went stale, not which keys to delete.
SELECT
  dr.recordtypeid,
  dr.type,
  COUNT(*)            AS deleted_rows,
  MAX(dr.deleteddate) AS last_deletion
FROM <catalog>.<schema>.deletedrecord dr
WHERE dr.deleteddate >= TIMESTAMP '<watermark>'
GROUP BY dr.recordtypeid, dr.type;

-- Act on it with a refresh, not a keyed DELETE: any record type listed above
-- has lost rows your landed copy still holds. A keyed anti-join delete is not
-- buildable from this surface.

-- The gap that costs people a quarter-end: the ledger records DELETED
-- RECORDS, not deleted LINES. Remove one line from a surviving order and
-- nothing is written here at all. The only safe policy for line-grain tables
-- (transactionline, transactionaccountingline, inventoryassignment) is a
-- periodic full refresh - or a re-pull of every document touched in the
-- window, keyed on the header id.
--
-- Note the naming: the Help Center documents this surface as
-- deletedRecordInConnect; the analytics record you query is deletedrecord.
-- There is no separate record under the docs name.

-- NetSuite values inventory by a COSTING METHOD chosen per item - Average,
-- FIFO, LIFO or Standard. The method is a stored column:
SELECT
  i.id,
  i.itemid,
  i.itemtype,
  i.costingmethod
FROM <catalog>.<schema>.item i;

-- On-hand VALUE is the OUTPUT of that method, not a sum of what moved. The
-- per-item-per-location balance record carries both the quantity and the
-- valuation NetSuite computed:
SELECT
  iil.item,
  iil.location,
  iil.quantityonhand,
  iil.onhandvaluemli,    -- valuation, produced by the costing engine
  iil.averagecostmli
FROM <catalog>.<schema>.inventoryitemlocations iil
WHERE
  iil.location = <location_id>  -- location scope — see quirks guide #oneworld
;

-- What NOT to do: rebuild value by summing transaction amounts.
--   SELECT SUM(tl.netamount) ...   -- does not reproduce inventory value
-- Under Average the unit cost moves with every receipt; under FIFO and LIFO
-- the layers consumed depend on order, not on any column you can sum; under
-- Standard the variance goes somewhere else entirely. Report QUANTITY from
-- the flows if you like - report VALUE from the balance record, and say in
-- the dashboard which one you used.