parent_child_history
stock_move_line_stock_production_lot_rel used
user_0223141589
sql
3 years ago
12 kB
7
Indexable
-- 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_IDEditor is loading...