parent_child_history

stock_move_line_stock_production_lot_rel used
 avatar
user_0223141589
sql
2 years ago
12 kB
3
Indexable
Never
-- Query Logic
-- Backbone of this query is building 2 tables and then mashing them together. Adds & removes which records every transaction that either added a part to another or removed a part from another
WITH adds AS (
-- Add transaction come from multiple sources. So we pull together each source and combine into add table
-- Sources: Manufacturing, Manufacturing Re-Work, Maintenance Repair, and historical 'fix/patch' transactions
  SELECT DISTINCT
    lot.LOT_ID AS lot_id,
    plot.LOT_ID AS parent_lot_id,
    lot.SERIAL_LOT_NUMBER AS lot_serial,
    plot.SERIAL_LOT_NUMBER AS parent_lot_serial,
    CONCAT(lot.LOT_ID, '-', plot.LOT_ID) AS pc_unique,
    sm.date AS date_added,
    'manufacturing_order' AS add_type,
    sml.PRODUCT_ID,
    sml.QTY_DONE,
    sm.reference
  FROM {{ source('odoo', 'stock_move_line_stock_production_lot_rel') }} mm
  LEFT JOIN {{ source('odoo', 'stock_move_line') }} AS sml
    ON mm.STOCK_MOVE_LINE_ID = sml.ID
  LEFT JOIN {{ ref('product_lot_serials') }} AS lot
    ON sml.lot_id = lot.LOT_ID
  LEFT JOIN {{ ref('product_lot_serials') }} AS plot
    ON mm.STOCK_PRODUCTION_LOT_ID = plot.LOT_ID
  LEFT JOIN {{ source('odoo', 'stock_move') }} AS sm
    ON sml.MOVE_ID = sm.id
  UNION
  SELECT
    lot.LOT_ID AS lot_id,
    plot.LOT_ID AS parent_lot_id,
    lot.SERIAL_LOT_NUMBER AS lot_serial,
    plot.SERIAL_LOT_NUMBER AS parent_lot_serial,
    CONCAT(lot.LOT_ID, '-', plot.LOT_ID) AS pc_unique,
    sm.date AS date_added,
    'repair' AS add_type,
    sm.PRODUCT_ID,
    sm.PRODUCT_UOM_QTY AS qty_done,
    sm.reference
  FROM {{ source('odoo', 'fleet_repair_line') }} AS frl
  LEFT JOIN {{ ref('product_lot_serials') }} AS lot
    ON frl.lot_id = lot.LOT_ID
  LEFT JOIN {{ source('odoo', 'stock_move') }} AS sm
    ON frl.MOVE_ID = sm.id
  LEFT JOIN {{ source('odoo', 'fleet_repair') }} AS fr
    ON frl.REPAIR_ID = fr.id
  LEFT JOIN {{ ref('product_lot_serials') }} AS plot
    ON fr.lot_id = plot.LOT_ID
  WHERE
    frl.state = 'done'
    AND frl.TYPE = 'add'
    AND sm.STATE = 'done'
    AND fr.STAge_id = 3
  UNION
  SELECT
    lot.LOT_ID AS lot_id,
    plot.LOT_ID AS parent_lot_id,
    lot.SERIAL_LOT_NUMBER AS lot_serial,
    plot.SERIAL_LOT_NUMBER AS parent_lot_serial,
    CONCAT(lot.LOT_ID, '-', plot.LOT_ID) AS pc_unique,
    sm.date AS date_added,
    'manufacturing_rework' AS add_type,
    sm.PRODUCT_ID,
    sm.PRODUCT_UOM_QTY AS qty_done,
    sm.reference
  FROM {{ source('odoo', 'repair_line') }} AS frl
  LEFT JOIN {{ ref('product_lot_serials') }} AS lot
    ON frl.lot_id = lot.LOT_ID
  LEFT JOIN {{ source('odoo', 'stock_move') }} AS sm
    ON frl.MOVE_ID = sm.id
  LEFT JOIN {{ source('odoo', 'repair_order') }} AS fr
    ON frl.REPAIR_ID = fr.id
  LEFT JOIN {{ ref('product_lot_serials') }} AS plot ON
    fr.lot_id = plot.LOT_ID
  WHERE frl.state = 'done'
    AND frl.TYPE = 'add'
  UNION
  SELECT
    lot.LOT_ID AS lot_id,
    tif.parent_lot_id AS parent_lot_id,
    lot.SERIAL_LOT_NUMBER AS lot_serial,
    tif.PARENT_SERIAL_NUMBER AS parent_lot_serial,
    tif.ASSEMBLY_UNIQUE_ID AS pc_unique,
    tif.DATE_ADDED,
    tif.ADD_TYPE,
    spl.PRODUCT_ID,
    '1' AS qty_done,
    'zipbase or other' AS reference
  FROM {{ source('component_add_dates', 'transaction_issue_fixes') }} AS tif
  LEFT JOIN {{ ref('product_lot_serials') }} AS lot
    ON tif.lot_id = lot.LOT_ID
  LEFT JOIN {{ source('odoo', 'stock_production_lot') }} AS spl
    ON tif.lot_id = spl.id
  WHERE tif.DATE_ADDED IS NOT NULL
    AND tif.ISSUE IN (
      'No Add Transaction prior to Removal',
      'Installed with no Add Transaction'
    )
    AND tif.DATE_ADDED REGEXP '^\\d{4}[-](0?[1-9]|1[0-2])[-](0?[1-9]|[12]\\d|30|31)($| (([0-9]|0[0-9]|1[0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$))' -- True for timestamps that are correctly formatted as yyyy-mm-dd, leading 0s optional, time optional
),
removes AS (
  -- Removes also come from multiple sources
  -- Sources: Maintenance Repair, Manufacturing Re-Work, and historical 'fix/patch' transactions
  SELECT
    lot.LOT_ID AS lot_id,
    plot.LOT_ID AS parent_lot_id,
    lot.SERIAL_LOT_NUMBER AS lot_serial,
    plot.SERIAL_LOT_NUMBER AS parent,
    CONCAT(lot.LOT_ID, '-', plot.LOT_ID) AS pc_unique,
    sm.date AS date_removed,
    'repair' AS remove_type,
    sm.PRODUCT_ID,
    sm.PRODUCT_UOM_QTY AS qty_done,
    sm.reference
  FROM {{ source('odoo', 'fleet_repair_line') }} AS frl
  LEFT JOIN {{ ref('product_lot_serials') }} AS lot
    ON frl.lot_id = lot.LOT_ID
  LEFT JOIN {{ source('odoo', 'stock_move') }} AS sm
    ON frl.MOVE_ID = sm.id
  LEFT JOIN {{ source('odoo', 'fleet_repair') }} AS fr
    ON frl.REPAIR_ID = fr.id
  LEFT JOIN {{ ref('product_lot_serials') }} AS plot
    ON fr.lot_id = plot.LOT_ID
  WHERE frl.state = 'done'
    AND frl.TYPE = 'remove'
    AND sm.STATE = 'done'
    AND fr.STAge_id = 3
  UNION
  -- This are the transactions that come from manufacturing re-work
  SELECT
    lot.LOT_ID AS lot_id,
    plot.LOT_ID AS parent_lot_id,
    lot.SERIAL_LOT_NUMBER AS lot_serial,
    plot.SERIAL_LOT_NUMBER AS parent_lot_serial,
    CONCAT(lot.LOT_ID, '-', plot.LOT_ID) AS pc_unique,
    sm.date AS date_removed,
    'manufacturing_rework' AS remove_type,
    sm.PRODUCT_ID,
    sm.PRODUCT_UOM_QTY AS qty_done,
    sm.reference
  FROM {{ source('odoo', 'repair_line') }} AS frl
  LEFT JOIN {{ ref('product_lot_serials') }} AS lot
    ON frl.lot_id = lot.LOT_ID
  LEFT JOIN {{ source('odoo', 'stock_move') }} AS sm
    ON frl.MOVE_ID = sm.id
  LEFT JOIN {{ source('odoo', 'repair_order') }} AS fr
    ON frl.REPAIR_ID = fr.id
  LEFT JOIN {{ ref('product_lot_serials') }} AS plot ON
    fr.lot_id = plot.LOT_ID
  WHERE frl.state = 'done'
    AND frl.TYPE = 'remove'
  UNION
  -- Include the google sheet transaction issue patch
  SELECT
    lot.LOT_ID AS lot_id,
    tif.parent_lot_id AS parent_lot_id,
    lot.SERIAL_LOT_NUMBER AS lot_serial,
    tif.PARENT_SERIAL_NUMBER AS parent_lot_serial,
    tif.ASSEMBLY_UNIQUE_ID AS pc_unique,
    tif.DATE_ADDED,
    tif.REMOVE_TYPE,
    spl.PRODUCT_ID,
    '1' AS qty_done,
    'zipbase or other' AS reference
  FROM {{ source('component_add_dates', 'transaction_issue_fixes') }} AS tif
  LEFT JOIN {{ ref('product_lot_serials') }} AS lot
    ON tif.lot_id = lot.LOT_ID
  LEFT JOIN {{ source('odoo', 'stock_production_lot') }} AS spl
    ON tif.lot_id = spl.id
  WHERE tif.DATE_REMOVED IS NOT NULL
    AND tif.ISSUE = 'Not Assembled but Never Removed'
    AND tif.DATE_REMOVED REGEXP '^\\d{4}[-](0?[1-9]|1[0-2])[-](0?[1-9]|[12]\\d|30|31)($| (([0-9]|0[0-9]|1[0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$))' -- True for timestamps that are correctly formatted as yyyy-mm-dd, leading 0s optional, time optional
),
-- current_assy is looking at what lot numbers are currently, in odoo, assembled onto another lot number. We concatentate those 2 ids to get a unique identifier.
-- This model is affected by deleted record snowflake issue. Risk is here is we wrongly tag the trustworthiness of some transactions.
curent_assy AS (
  SELECT
    CONCAT(ca.LOT_ID, '-', ca.INSTALLED_LOCATION_ID) AS pc_unique,
    ca.lot_id,
    ca.INSTALLED_LOCATION_ID AS parent_lot_id
  FROM {{ source('odoo', 'installed_location_rel') }} AS ca
),
-- now we combine the adds and removes together into one large table. 
adds_and_removes AS (
  SELECT DISTINCT
    CASE
      WHEN a.product_id IS NOT NULL THEN a.product_id
      WHEN a.product_id IS NULL
      AND rm.product_id IS NOT NULL THEN rm.product_id
    END AS odoo_product_id,
    CASE
      WHEN a.lot_id IS NOT NULL THEN a.lot_id
      WHEN a.lot_id IS NULL
      AND rm.lot_id IS NOT NULL THEN rm.lot_id
    END AS odoo_lot_id,
    CASE
      WHEN a.parent_lot_id IS NOT NULL THEN a.parent_lot_id
      WHEN a.parent_lot_id IS NULL
      AND rm.parent_lot_id IS NOT NULL THEN rm.parent_lot_id
    END AS odoo_parent_lot_id,
    CASE
      WHEN a.pc_unique IS NOT NULL THEN a.pc_unique
      WHEN a.pc_unique IS NULL
      AND rm.pc_unique IS NOT NULL THEN rm.pc_unique
    END AS parent_child_unique,
    a.pc_unique AS as_assembled_parent_child_unique,
    ca.pc_unique AS current_parent_child_unique,
    a.add_type,
    rm.remove_type,
    a.date_added,
    rm.date_removed AS date_removed,
    a.reference as add_reference,
    rm.reference as removal_refereance,
    CASE
      WHEN date_removed IS NOT NULL THEN DATEDIFF(days, date_added, date_removed)
      ELSE DATEDIFF(days, date_added, GETDATE())
    END AS days_assembled,
    CASE
      WHEN date_added IS NULL
      AND date_removed IS NOT NULL THEN 'No Add Transaction prior to Removal'
      WHEN date_removed IS NOT NULL THEN 'TRUST'
      WHEN date_removed IS NULL
      AND a.pc_unique = ca.pc_unique THEN 'TRUST'
      WHEN ca.pc_unique IS NULL
      AND date_added IS NOT NULL
      AND date_removed IS NULL THEN 'Not Assembled but Never Removed'
      ELSE 'NO TRUST'
    END AS trustworthy
  FROM adds AS a FULL
  OUTER JOIN removes AS rm
    ON a.pc_unique = rm.pc_unique
  LEFT JOIN curent_assy AS ca
    ON a.pc_unique = ca.pc_unique
  LEFT JOIN {{ ref('product_lot_serials') }} AS lot
    ON a.lot_id = lot.LOT_ID
  LEFT JOIN {{ ref('products') }} AS prod
    ON a.product_id = prod.PRODUCT_ID
),
-- combined brings the add + removes and joins them to the currently assembled so we get the full error list of all historical adds that were
-- not captured in Odoo over the years.
combined AS (
  SELECT DISTINCT
    CASE
      WHEN ar.add_type IS NULL
      AND ca.lot_id IS NOT NULL THEN 'Installed with no Add Transaction'
      ELSE ar.TRUSTWORTHY
    END AS trust,
    CASE
      WHEN ar.odoo_product_id IS NOT NULL THEN ar.odoo_product_id
      ELSE prod.PRODUCT_ID
    END AS n_product_id,
    CASE
      WHEN ar.odoo_lot_id IS NOT NULL THEN ar.odoo_lot_id
      ELSE ca.lot_id
    END AS n_lot_id,
    CASE
      WHEN ar.odoo_parent_lot_id IS NOT NULL THEN ar.odoo_parent_lot_id
      ELSE ca.parent_lot_id
    END AS n_parent_lot_id,
    ca.pc_unique,
    ca.lot_id,
    ca.parent_lot_id,
    ar.*
  FROM adds_and_removes AS ar 
  FULL OUTER JOIN curent_assy AS ca
    ON ar.parent_child_unique = ca.pc_unique
  LEFT JOIN {{ ref('product_lot_serials') }} AS ls
    ON ca.lot_id = ls.LOT_ID
  LEFT JOIN {{ ref('products') }} AS prod
    ON ls.PART_NUMBER = prod.BARCODE
)
SELECT DISTINCT 
  ci.trust AS trustworthiness, --trustworthiness of the add and remove date for the parent and child components. Identifies issues with transactions causing something to be un-trustworthy
  prod.SYSTEM, -- System based on the part number's LRU. Ths could be smarter if we pulled system from used on LRU. But each part could have multiple used on LRUs and thus multiple systems. Maybe in DBT we extend this to show a list in this column
  prod.LRU, -- If the part number is a LRU it will be displayed here
  prod.BARCODE AS part_number, -- part number of the child serial number
  prod.PRODUCT_NAME AS part_name, -- part name of the child serial number
  ls.SERIAL_LOT_NUMBER, -- serial number of child
  pls.SERIAL_LOT_NUMBER AS parent_serial_number, -- serial number of the parent
  ci.add_reference,
  ci.removal_refereance,
  ci.add_type, -- indicates the type of transaction that added the child part to the parent part
  ci.remove_type, -- indicates the type of transaction that removed the child part from the parent part
  ci.date_added, -- date the child part was added to the parent part
  ci.date_removed, -- date the child part was removed from the parent part
  ci.n_lot_id AS lot_id, -- lot_id in odoo of the child part. Used to join to other tables
  ci.n_parent_lot_id AS parent_lot_id, -- lot_id in odoo of the parent part. Used to join to other tables
  CASE
    WHEN ci.parent_child_unique IS NOT NULL THEN ci.parent_child_unique
    ELSE CONCAT(ci.n_lot_id, '-', ci.n_parent_lot_id)
  END AS assembly_unique_id -- unique identifier of the child and parent combination. Risk here as we are joining this as the unique identified. In reality a child can be added and removed multiple times to a parent. So maybe we need to extend this to include the add date.
FROM combined AS ci
LEFT JOIN {{ ref('products') }} AS prod
  ON ci.n_product_id = prod.PRODUCT_ID
LEFT JOIN {{ ref('product_lot_serials') }} AS ls
  ON ci.n_lot_id = ls.LOT_ID
LEFT JOIN {{ ref('product_lot_serials') }} AS pls
  ON ci.n_parent_lot_id = pls.LOT_ID