F4106

-- ============================================================
-- Table  : F4106 Item base price table: effective-dated unit and credit prices by item and branch, optionally specific to a customer, customer group, currency, and unit of measure.
-- Purpose: Column-selected read of F4106 — auto-generated from field metadata
-- Grain  : One row per BPITM + BPMCU + BPLOCN + BPLOTN + BPAN8 + BPIGID + BPCGID + BPLOTG + BPFRMP + BPCRCD + BPUOM + BPEXDJ + BPUPMJ + BPTDAY
-- 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.BPITM AS "Short internal item number (8-digit numeric key).",
  TRIM(f.BPMCU) AS "Branch/plant (business unit); stored right-justified with leading blanks.",
  f.BPLOCN AS "Storage location within the branch, formatted per the branch location structure.",
  f.BPLOTN AS "Lot or serial number identifying the specific lot of stock.",
  f.BPAN8 AS "Customer address number for a customer-specific price; zero for the base price.",
  f.BPIGID AS "Item price group key id when the price is defined at group level.",
  f.BPCGID AS "Customer price group key id when the price is defined at group level.",
  f.BPLOTG AS "Lot grade code used for grade-controlled items.",
  f.BPFRMP / POWER(10, 3) AS "Lower bound of the potency range this price covers.",  -- implied decimals: 3 (verify in F9210)
  f.BPCRCD AS "Transaction currency code of the price.",
  f.BPUOM AS "Unit of measure the price applies to, as entered.",
  CASE WHEN f.BPEXDJ IS NULL OR f.BPEXDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.BPEXDJ AS INT) DIV 1000, 1, 1), CAST(f.BPEXDJ AS INT) % 1000 - 1) END AS "Expiration date; the record stops applying after this date.",  -- CYYDDD Julian → DATE
  CASE WHEN f.BPUPMJ IS NULL OR f.BPUPMJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.BPUPMJ AS INT) DIV 1000, 1, 1), CAST(f.BPUPMJ AS INT) % 1000 - 1) END AS "Date the row was last updated (part of the unique key on this table).",  -- CYYDDD Julian → DATE
  f.BPTDAY AS "Time of day the row was last updated, HHMMSS (part of the unique key on this table).",
  f.BPLITM AS "Second (long) item number, the customer-facing alphanumeric item identifier.",
  f.BPAITM AS "Third item number, an alternate catalog-style identifier.",
  CASE WHEN f.BPEFTJ IS NULL OR f.BPEFTJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.BPEFTJ AS INT) DIV 1000, 1, 1), CAST(f.BPEFTJ AS INT) % 1000 - 1) END AS "Effective-from date; the record applies on or after this date.",  -- CYYDDD Julian → DATE
  f.BPUPRC / POWER(10, 4) AS "Unit list price in the record currency (4 implied decimals).",  -- implied decimals: 4 (verify in F9210)
  f.BPACRD / POWER(10, 4) AS "Unit credit price applied on credit orders (4 implied decimals).",  -- implied decimals: 4 (verify in F9210)
  f.BPBSCD AS "Basis code: how the price is derived (amount, cost-plus, formula...).",
  f.BPLEDG AS "Cost method referenced when the price is cost-based.",
  f.BPFVTR / POWER(10, 4) AS "Factor applied to the basis when deriving the price.",  -- implied decimals: 4 (verify in F9210)
  f.BPFRMN AS "Price formula name when the price is formula-based.",
  f.BPURCD AS "User-reserved code (open field for site-specific use).",
  CASE WHEN f.BPURDT IS NULL OR f.BPURDT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.BPURDT AS INT) DIV 1000, 1, 1), CAST(f.BPURDT AS INT) % 1000 - 1) END AS "User-reserved date (open field for site-specific use).",  -- CYYDDD Julian → DATE
  f.BPURAT / POWER(10, 2) AS "User-reserved amount (open field for site-specific use).",  -- implied decimals: 2 (verify in F9210)
  f.BPURAB AS "User-reserved number (open field for site-specific use).",
  f.BPURRF AS "User-reserved reference (open field for site-specific use).",
  f.BPAPRS AS "Approval status of the price record."
FROM <catalog>.<schema_data>.f4106 f
WHERE
  f.BPITM = <ITM>
  -- AND f.BPMCU = '<MCU>'
  -- AND f.BPAN8 = <AN8>
ORDER BY f.BPITM;