parent_child_history
stock_move_line_stock_production_lot_rel useduser_0223141589
sql
3 years ago
12 kB
6
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_ID
Editor is loading...