F03012

-- ============================================================
-- Table  : F03012 Customer master by line of business: one row per customer address number and company holding A/R credit terms, collection settings, sales defaults, and rolled-up invoice history.
-- Purpose: Column-selected read of F03012 — auto-generated from field metadata
-- Grain  : One row per AIAN8 + AICO
-- Notes  : Auto-generated skeleton. Julian dates (CYYDDD) are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210); padded business-unit/UDC keys are TRIMmed. Audit columns (…USER/…PID/…UPMJ) are omitted — see the quirks guide. Set catalog/schema to your Unity Catalog location; fill remaining placeholders (<...>) for your tenant.
-- ============================================================
SELECT
  f.AIAN8 AS "Customer address book number; join to F0101 for name and to F03B11 for invoices",
  f.AICO AS "Company this customer record applies to; company 00000 holds the default line-of-business record",
  f.AIARC AS "G/L offset class that routes A/R postings to the right trade account via AAIs",
  f.AICRCD AS "Default transaction currency assigned to this customer",
  f.AITXA1 AS "Default tax rate/area applied to invoices for this customer",
  f.AIACL AS "Credit limit granted to the customer, in whole currency units",
  f.AIHDAR AS "Hold-invoices flag that blocks new invoice entry for the customer",
  f.AITRAR AS "A/R payment terms code that drives due-date and discount calculation",
  f.AIRYIN AS "Default payment instrument (check, EFT, draft) expected from this customer",
  f.AIARPY AS "Alternate payor address number when someone other than the customer pays",
  f.AIDSO AS "Days sales outstanding as computed by the credit analysis refresh",
  f.AICMGR AS "Credit manager assigned to the account",
  f.AICLMG AS "Collection manager assigned to the account",
  f.AIAFC AS "Whether finance charges are applied to late balances for this customer",
  CASE WHEN f.AIDLIJ IS NULL OR f.AIDLIJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.AIDLIJ AS INT) DIV 1000, 1, 1), CAST(f.AIDLIJ AS INT) % 1000 - 1) END AS "Date of the most recent invoice raised for the customer",  -- CYYDDD Julian → DATE
  f.AIABC1 AS "ABC ranking of the customer by sales volume",
  CASE WHEN f.AIDLP IS NULL OR f.AIDLP = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.AIDLP AS INT) DIV 1000, 1, 1), CAST(f.AIDLP AS INT) % 1000 - 1) END AS "Date a payment was last received from the customer",  -- CYYDDD Julian → DATE
  f.AIAVD AS "Average days late across paid invoices, from credit analysis",
  f.AIAD / POWER(10, 2) AS "Total amount currently due from the customer",  -- implied decimals: 2 (verify in F9210)
  f.AIASTY / POWER(10, 2) AS "Amount invoiced year-to-date",  -- implied decimals: 2 (verify in F9210)
  f.AISPYE / POWER(10, 2) AS "Amount invoiced in the prior year",  -- implied decimals: 2 (verify in F9210)
  f.AIAHB / POWER(10, 2) AS "Highest open balance the account has reached",  -- implied decimals: 2 (verify in F9210)
  f.AIAPRC / POWER(10, 2) AS "Value of open sales orders not yet invoiced (credit exposure)",  -- implied decimals: 2 (verify in F9210)
  CASE WHEN f.AIDAOJ IS NULL OR f.AIDAOJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.AIDAOJ AS INT) DIV 1000, 1, 1), CAST(f.AIDAOJ AS INT) % 1000 - 1) END AS "Date the customer account was opened",  -- CYYDDD Julian → DATE
  f.AICPGP AS "Customer price group used by base pricing and adjustments",
  f.AITRDC / POWER(10, 3) AS "Trade discount percent applied to sales order pricing",  -- implied decimals: 3 (verify in F9210)
  f.AIHOLD AS "Order hold code that stops new sales orders for this customer",
  f.AIROUT AS "Delivery route code for shipment planning",
  f.AIZON AS "Delivery zone number for freight and route assignment",
  f.AICARS AS "Default carrier address number for shipments to this customer",
  f.AILTDT AS "Standard transit days from ship point to the customer",
  f.AIFRTH AS "Freight handling code defining FOB terms and freight responsibility",
  f.AIBACK AS "Whether backorders are allowed on sales orders for this customer",
  f.AIPORQ AS "Whether a customer PO number is required on sales orders",
  f.AIARTO AS "Level at which credit checking runs (customer, parent, or line of business)",
  f.AIBLFR AS "Billing frequency for invoice consolidation and cycle billing",
  f.AIAC01 AS "Address book category code 01 — first of thirty customer classification buckets",
  f.AIAC11 AS "Address book category code 11, commonly repurposed as sales region",
  f.AIBSC AS "Buying segment code used by CRM and sales analysis",
  f.AICUSTS AS "Customer status code (active, inactive, prospect)"
FROM <catalog>.<schema_data>.f03012 f
WHERE
  f.AIAN8 = <AN8>
  -- AND f.AICO = '<CO>'
ORDER BY f.AIAN8;