P4072

-- ============================================================
-- Program: P4072 Price Adjustment Detail Revisions — the screen where the actual pricing rules are set for an adjustment: basis codes, factor values, level breaks, and effective dates.
-- Purpose: Price Adjustment Detail Revisions — the screen where the actual pricing rules are set for an adjustment: basis codes, factor values, level breaks, and effective dates. — auto-generated boilerplate from program-table-map
-- Grain  : F4072 × F4071, F4092 — 1:N joins yield one row per detail line
-- Tables : F4072, F4071, F4092
-- Notes  : Auto-generated skeleton. Julian dates are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210). 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
  h.ADAST AS "Adjustment name this rate belongs to; joins to F4071 and to F4074 ledger rows.",
  h.ADITM AS "Short item number when the rate targets a specific item (zero when keyed by item group).",
  h.ADAN8 AS "Address number when the rate targets a specific customer or supplier (zero when keyed by group).",
  h.ADIGID AS "Item group key ID — resolves via F40941 to the item category-code combination this rate covers.",
  h.ADCGID AS "Customer group key ID — resolves via F40942 to the customer category-code combination this rate covers.",
  h.ADOGID AS "Order group key ID — resolves via F40943 to the order-detail value combination this rate covers.",
  h.ADCRCD AS "Currency the rate is expressed in.",
  h.ADUOM AS "Unit of measure the quantity break and rate are stated in.",
  h.ADMNQ AS "Level break: minimum quantity (or weight/amount, per the F4071 level-break type) at which this rate starts.",
  CASE WHEN h.ADEXDJ IS NULL OR h.ADEXDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.ADEXDJ AS INT) DIV 1000, 1, 1), CAST(h.ADEXDJ AS INT) % 1000 - 1) END AS "Date the rate expires (Julian CYYDDD).",  -- CYYDDD Julian → DATE
  CASE WHEN h.ADUPMJ IS NULL OR h.ADUPMJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.ADUPMJ AS INT) DIV 1000, 1, 1), CAST(h.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
  h.ADTDAY AS "Last-updated time of day; part of the primary key alongside UPMJ.",
  h.ADRULENAME AS "Rounding rule applied to the calculated adjusted price.",
  f4.GPGPTY AS "Group type: whether this definition is an item, customer, or order detail group.",
  f4.GPGPC AS "Group code — the price group name referenced by F4071 adjustments and item/customer masters."
FROM <catalog>.<schema_data>.f4072 h
LEFT JOIN <catalog>.<schema_data>.f4071 f
  ON f.ATAST = h.ADAST
LEFT JOIN <catalog>.<schema_data>.f4092 f4
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
WHERE
  h.ADITM = <ITM>
  AND h.ADAN8 = <AN8>
ORDER BY h.ADITM;