F4072

-- ============================================================
-- Table  : F4072 One row per adjustment rate: the factor value for a given adjustment name, item or item group, customer or customer group, currency, UOM, quantity level break, and effective-date window. The rate lookup behind every Advanced Pricing calculation.
-- Purpose: Column-selected read of F4072 — auto-generated from field metadata
-- Grain  : One row per ADAST + ADITM + ADAN8 + ADIGID + ADCGID + ADOGID + ADCRCD + ADUOM + ADMNQ + ADEXDJ + ADUPMJ + ADTDAY
-- 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.ADAST AS "Adjustment name this rate belongs to; joins to F4071 and to F4074 ledger rows.",
  f.ADITM AS "Short item number when the rate targets a specific item (zero when keyed by item group).",
  f.ADAN8 AS "Address number when the rate targets a specific customer or supplier (zero when keyed by group).",
  f.ADIGID AS "Item group key ID — resolves via F40941 to the item category-code combination this rate covers.",
  f.ADCGID AS "Customer group key ID — resolves via F40942 to the customer category-code combination this rate covers.",
  f.ADOGID AS "Order group key ID — resolves via F40943 to the order-detail value combination this rate covers.",
  f.ADCRCD AS "Currency the rate is expressed in.",
  f.ADUOM AS "Unit of measure the quantity break and rate are stated in.",
  f.ADMNQ AS "Level break: minimum quantity (or weight/amount, per the F4071 level-break type) at which this rate starts.",
  CASE WHEN f.ADEXDJ IS NULL OR f.ADEXDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.ADEXDJ AS INT) DIV 1000, 1, 1), CAST(f.ADEXDJ AS INT) % 1000 - 1) END AS "Date the rate expires (Julian CYYDDD).",  -- CYYDDD Julian → DATE
  CASE WHEN f.ADUPMJ IS NULL OR f.ADUPMJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.ADUPMJ AS INT) DIV 1000, 1, 1), CAST(f.ADUPMJ AS INT) % 1000 - 1) END AS "Last-updated date (Julian CYYDDD); part of the primary key, so treat as a version stamp on the rate row.",  -- CYYDDD Julian → DATE
  f.ADTDAY AS "Last-updated time of day; part of the primary key alongside UPMJ.",
  f.ADLITM AS "Second (customer-facing) item number for the targeted item.",
  f.ADAITM AS "Third item number for the targeted item.",
  CASE WHEN f.ADEFTJ IS NULL OR f.ADEFTJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.ADEFTJ AS INT) DIV 1000, 1, 1), CAST(f.ADEFTJ AS INT) % 1000 - 1) END AS "Date the rate becomes effective (Julian CYYDDD).",  -- CYYDDD Julian → DATE
  f.ADBSCD AS "Basis code: what the factor is applied against — base price percent, cost percent, cost plus amount, flat amount, formula, etc. Determines how to interpret FVTR.",
  f.ADLEDG AS "Cost method used as the basis when the basis code is cost-driven.",
  f.ADFRMN AS "Price formula name applied when the basis code calls for a formula.",
  f.ADFVTR / POWER(10, 4) AS "Factor value — the percent, amount, or override price depending on the basis code; the stored value carries 4 implied decimals regardless.",  -- implied decimals: 4 (verify in F9210)
  f.ADFGY AS "Y/N: this rate row awards free goods (details in the free goods catalog).",
  f.ADATID AS "Adjustment key ID — system row identifier, unique within an adjustment name; useful as a stable join handle.",
  f.ADNBRORD AS "Number of orders threshold for volume-incentive adjustments.",
  f.ADUOMVID AS "Unit of measure the volume-incentive accumulation is tracked in.",
  f.ADFVUM AS "Unit of measure the factor value itself is stated in (for per-unit amount factors).",
  f.ADPARTFG AS "Flag allowing the rate to price partial quantities.",
  f.ADAPRS AS "Approval status of the rate row when price approval workflow is active — filter on approved rows for effective-price analysis.",
  f.ADBKTPID AS "Parent ID linking basket-level rate rows to their basket master.",
  f.ADCRCDVID AS "Currency the volume-incentive accumulation is tracked in.",
  f.ADRULENAME AS "Rounding rule applied to the calculated adjusted price."
FROM <catalog>.<schema_data>.f4072 f
WHERE
  f.ADITM = <ITM>
  -- AND f.ADAN8 = <AN8>
ORDER BY f.ADAST;