F4105

-- ============================================================
-- Table  : F4105 Item cost table: one row per item, branch, location, lot, and cost method carrying the unit cost used to value inventory and purchasing transactions.
-- Purpose: Column-selected read of F4105 — auto-generated from field metadata
-- Grain  : One row per COITM + COMCU + COLOCN + COLOTN + COLEDG
-- 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.COITM AS "Short internal item number (8-digit numeric key).",
  TRIM(f.COMCU) AS "Branch/plant (business unit); stored right-justified with leading blanks.",
  f.COLOCN AS "Storage location within the branch, formatted per the branch location structure.",
  f.COLOTN AS "Lot or serial number identifying the specific lot of stock.",
  f.COLEDG AS "Cost method code (e.g. 01 last-in, 02 weighted average, 07 standard) selecting which cost applies.",
  f.COLITM AS "Second (long) item number, the customer-facing alphanumeric item identifier.",
  f.COAITM AS "Third item number, an alternate catalog-style identifier.",
  f.COLOTG AS "Lot grade code used for grade-controlled items.",
  f.COUNCS / POWER(10, 4) AS "Unit cost for this item/branch/location/lot under the given cost method (4 implied decimals).",  -- implied decimals: 4 (verify in F9210)
  f.COCSPO AS "Marks the cost method purchasing uses for this item.",
  f.COCSIN AS "Marks the cost method inventory valuation uses for this item.",
  f.COURCD AS "User-reserved code (open field for site-specific use).",
  CASE WHEN f.COURDT IS NULL OR f.COURDT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.COURDT AS INT) DIV 1000, 1, 1), CAST(f.COURDT AS INT) % 1000 - 1) END AS "User-reserved date (open field for site-specific use).",  -- CYYDDD Julian → DATE
  f.COURAT / POWER(10, 2) AS "User-reserved amount (open field for site-specific use).",  -- implied decimals: 2 (verify in F9210)
  f.COURAB AS "User-reserved number (open field for site-specific use).",
  f.COURRF AS "User-reserved reference (open field for site-specific use).",
  f.COCCFL AS "Flag set when the cost has been changed and downstream updates are pending.",
  f.COCRCS / POWER(10, 4) AS "Per-unit carrying cost used by planning cost models.",  -- implied decimals: 4 (verify in F9210)
  f.COOSTC / POWER(10, 4) AS "Per-unit overstock cost used by planning cost models.",  -- implied decimals: 4 (verify in F9210)
  f.COSTOC / POWER(10, 4) AS "Per-unit stockout cost used by planning cost models."  -- implied decimals: 4 (verify in F9210)
FROM <catalog>.<schema_data>.f4105 f
WHERE
  f.COITM = <ITM>
  -- AND f.COMCU = '<MCU>'
ORDER BY f.COITM;