HZ_CUST_ACCT_SITES_ALL

-- ============================================================
-- Table  : HZ_CUST_ACCT_SITES_ALL — Customer account sites — an account's use of a party site (address); the layer order and invoice addresses hang off, with bill-to/ship-to/market purpose flags
-- Purpose: Column-selected read of HZ_CUST_ACCT_SITES_ALL — auto-generated from field metadata
-- Grain  : One row per CUST_ACCT_SITE_ID + SET_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.CUST_ACCT_SITE_ID AS "Surrogate key of the account site",
  t.SET_ID AS "Reference data set stripe — the column that replaced the EBS operating-unit ORG_ID, and part of both documented unique keys; see quirks #set-id",
  t.CUST_ACCOUNT_ID AS "The customer account the site belongs to",
  t.PARTY_SITE_ID AS "The party site (address) this account site uses",
  t.STATUS AS "Site status",  -- decode t.STATUS via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'CODE_STATUS', LANGUAGE-filtered)
  t.BILL_TO_FLAG AS "Bill-to purpose — Y, N, or P for the account's primary bill-to site",
  t.SHIP_TO_FLAG AS "Ship-to purpose — Y, N, or P for primary",
  t.MARKET_FLAG AS "Marketing purpose — Y, N, or P for primary",
  t.CUSTOMER_CATEGORY_CODE AS "User-defined site category",  -- decode t.CUSTOMER_CATEGORY_CODE via FND_LOOKUP_VALUES (LOOKUP_TYPE = 'ADDRESS_CATEGORY', LANGUAGE-filtered)
  t.START_DATE AS "Site validity start",
  t.END_DATE AS "Site validity end",
  t.ORIG_SYSTEM_REFERENCE AS "Legacy-system address key"
FROM <catalog>.<schema>.HZ_CUST_ACCT_SITES_ALL t
WHERE
  t.SET_ID = <SET_ID>  -- reference data set — the stripe that replaced the org stripe; one set or codes double-load; see quirks guide #set-id
  -- AND t.CUST_ACCT_SITE_ID = <CUST_ACCT_SITE_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.CUST_ACCT_SITE_ID;