P4106

-- ============================================================
-- Program: P4106 Base Price Revisions — maintain base sales prices by item, branch, customer, or customer group with effective dates
-- Purpose: Base Price Revisions — maintain base sales prices by item, branch, customer, or customer group with effective dates — auto-generated boilerplate from program-table-map
-- Grain  : F4106 × F4101, F4102 — 1:N joins yield one row per detail line
-- Tables : F4106, F4101, F4102
-- 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.BPITM AS "Short internal item number (8-digit numeric key).",
  TRIM(h.BPMCU) AS "Branch/plant (business unit); stored right-justified with leading blanks.",
  h.BPLOCN AS "Storage location within the branch, formatted per the branch location structure.",
  h.BPLOTN AS "Lot or serial number identifying the specific lot of stock.",
  h.BPAN8 AS "Customer address number for a customer-specific price; zero for the base price.",
  h.BPIGID AS "Item price group key id when the price is defined at group level.",
  h.BPCGID AS "Customer price group key id when the price is defined at group level.",
  h.BPLOTG AS "Lot grade code used for grade-controlled items.",
  h.BPFRMP / POWER(10, 3) AS "Lower bound of the potency range this price covers.",  -- implied decimals: 3 (verify in F9210)
  h.BPCRCD AS "Transaction currency code of the price.",
  h.BPUOM AS "Unit of measure the price applies to, as entered.",
  CASE WHEN h.BPEXDJ IS NULL OR h.BPEXDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.BPEXDJ AS INT) DIV 1000, 1, 1), CAST(h.BPEXDJ AS INT) % 1000 - 1) END AS "Expiration date; the record stops applying after this date.",  -- CYYDDD Julian → DATE
  CASE WHEN h.BPUPMJ IS NULL OR h.BPUPMJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.BPUPMJ AS INT) DIV 1000, 1, 1), CAST(h.BPUPMJ AS INT) % 1000 - 1) END AS "Date the row was last updated (part of the unique key on this table).",  -- CYYDDD Julian → DATE
  h.BPTDAY AS "Time of day the row was last updated, HHMMSS (part of the unique key on this table)."
FROM <catalog>.<schema_data>.f4106 h
LEFT JOIN <catalog>.<schema_data>.f4101 im
  ON im.IMITM = h.BPITM
LEFT JOIN <catalog>.<schema_data>.f4102 ib
  ON ib.IBITM = h.BPITM
 AND ib.IBMCU = h.BPMCU
WHERE
  h.BPITM = <ITM>
  AND h.BPMCU = '<MCU>'
  AND h.BPAN8 = <AN8>
ORDER BY h.BPITM;