F0006

-- ============================================================
-- Table  : F0006 Business unit master — branch/plants, cost centers, and jobs with their reporting category codes
-- Purpose: Column-selected read of F0006 — auto-generated from field metadata
-- Grain  : One row per MCMCU
-- 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(mc.MCMCU) AS "Business unit number - the key (right-justified, space-padded to 12)",
  mc.MCSTYL AS "Business unit type - groups business units for reporting",
  mc.MCDC AS "Compressed description used for word search",
  mc.MCLDM AS "Level of detail for financial rollups",
  mc.MCCO AS "Company that owns the business unit",
  mc.MCAN8 AS "Address book number linked to the business unit",
  mc.MCCNTY AS "County code",
  mc.MCADDS AS "State code",
  mc.MCFMOD AS "Model/consolidation flag - marks model or consolidation records",
  mc.MCDL01 AS "Business unit description",
  mc.MCDL02 AS "Description line 2",
  mc.MCRP01 AS "Category code 01 - division",
  mc.MCRP02 AS "Category code 02 - region",
  mc.MCRP03 AS "Category code 03 - group",
  mc.MCRP04 AS "Category code 04 - branch office",
  mc.MCRP05 AS "Category code 05 - department type",
  mc.MCRP06 AS "Category code 06 - person responsible",
  mc.MCRP07 AS "Category code 07 - line of business",
  mc.MCRP08 AS "Category code 08",
  mc.MCRP09 AS "Category code 09",
  mc.MCRP10 AS "Category code 10",
  mc.MCTXA1 AS "Default tax rate/area",
  mc.MCEXR1 AS "Default tax explanation code",
  mc.MCPECC AS "Posting edit code - controls whether posting is allowed",
  mc.MCSBLI AS "Subledger inactive code",
  mc.MCANPA AS "Supervisor address book number",
  TRIM(mc.MCMCUS) AS "Subsequent business unit",
  mc.MCPC AS "Percent complete (job costing)",
  CASE WHEN mc.MCD1J IS NULL OR mc.MCD1J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(mc.MCD1J AS INT) DIV 1000, 1, 1), CAST(mc.MCD1J AS INT) % 1000 - 1) END AS "Planned start date (job costing)",  -- CYYDDD Julian → DATE
  CASE WHEN mc.MCD2J IS NULL OR mc.MCD2J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(mc.MCD2J AS INT) DIV 1000, 1, 1), CAST(mc.MCD2J AS INT) % 1000 - 1) END AS "Actual start date",  -- CYYDDD Julian → DATE
  CASE WHEN mc.MCD3J IS NULL OR mc.MCD3J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(mc.MCD3J AS INT) DIV 1000, 1, 1), CAST(mc.MCD3J AS INT) % 1000 - 1) END AS "Planned completion date",  -- CYYDDD Julian → DATE
  CASE WHEN mc.MCD4J IS NULL OR mc.MCD4J = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(mc.MCD4J AS INT) DIV 1000, 1, 1), CAST(mc.MCD4J AS INT) % 1000 - 1) END AS "Actual completion date",  -- CYYDDD Julian → DATE
  CASE WHEN mc.MCFPDJ IS NULL OR mc.MCFPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(mc.MCFPDJ AS INT) DIV 1000, 1, 1), CAST(mc.MCFPDJ AS INT) % 1000 - 1) END AS "Final payment date",  -- CYYDDD Julian → DATE
  mc.MCCAC / POWER(10, 2) AS "Projected cost at completion",  -- implied decimals: 2 (verify in F9210)
  mc.MCPAC / POWER(10, 2) AS "Projected profit at completion",  -- implied decimals: 2 (verify in F9210)
  TRIM(mc.MCTSBU) AS "Target business unit"
FROM <catalog>.<schema_data>.f0006 mc
WHERE
  mc.MCMCU = '<MCU>'
ORDER BY mc.MCMCU;