F41061

-- ============================================================
-- Table  : F41061 Supplier price catalog: supplier- and catalog-specific purchase prices with quantity breaks and effective date ranges, feeding PO unit cost retrieval.
-- Purpose: Column-selected read of F41061 — auto-generated from field metadata
-- Grain  : One row per CBMCU + CBAN8 + CBITM + CBCATN + CBCRCD + CBUOM + CBUORG + CBEXDJ
-- 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
  TRIM(f.CBMCU) AS "Branch/plant (business unit); stored right-justified with leading blanks.",
  f.CBAN8 AS "Supplier address number the catalog price belongs to.",
  f.CBITM AS "Short internal item number (8-digit numeric key).",
  f.CBCATN AS "Supplier catalog name grouping this set of prices.",
  f.CBCRCD AS "Transaction currency code of the price.",
  f.CBUOM AS "Unit of measure the price applies to, as entered.",
  f.CBUORG AS "Quantity break threshold: the price applies at or above this quantity.",
  CASE WHEN f.CBEXDJ IS NULL OR f.CBEXDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CBEXDJ AS INT) DIV 1000, 1, 1), CAST(f.CBEXDJ AS INT) % 1000 - 1) END AS "Expiration date; the record stops applying after this date.",  -- CYYDDD Julian → DATE
  f.CBLITM AS "Second (long) item number, the customer-facing alphanumeric item identifier.",
  f.CBAITM AS "Third item number, an alternate catalog-style identifier.",
  f.CBDMCT AS "Distribution agreement (contract) number tied to the price.",
  f.CBDMCS AS "Supplement number of the distribution agreement.",
  f.CBKCOO AS "Company of the related blanket/quote order.",
  f.CBDOCO AS "Related blanket/quote order number.",
  f.CBDCTO AS "Order type of the related blanket/quote order.",
  f.CBLNID / POWER(10, 3) AS "Line number on the related order.",  -- implied decimals: 3 (verify in F9210)
  f.CBPRRC / POWER(10, 4) AS "Supplier's unit purchase price from the catalog (4 implied decimals).",  -- implied decimals: 4 (verify in F9210)
  CASE WHEN f.CBEFTJ IS NULL OR f.CBEFTJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.CBEFTJ AS INT) DIV 1000, 1, 1), CAST(f.CBEFTJ AS INT) % 1000 - 1) END AS "Effective-from date; the record applies on or after this date."  -- CYYDDD Julian → DATE
FROM <catalog>.<schema_data>.f41061 f
WHERE
  f.CBMCU = '<MCU>'
  -- AND f.CBAN8 = <AN8>
  -- AND f.CBITM = <ITM>
ORDER BY f.CBMCU;