sql

WITH quality_checks AS (
  SELECT
    lot.PART_NUMBER,
    lot.PART_NAME,
    lot.SERIAL_LOT_NUMBER,
    qc.NAME AS "QCP#",
    CASE
      WHEN qc.POINT_ID IN (1717, 1024, 63797, 1378, 1381) THEN 'Mass'
      ELSE 'CG'
    END AS TYPE,
    CASE
      WHEN qc.REPAIR_ID IS NULL THEN 'Manufacturing'
      ELSE 'Field'
    END AS method,
    qc.REPAIR_ID,
    qc.WORKORDER_ID,
    user.NAME,
    TO_CHAR(qc.WRITE_DATE, 'YYYY-MM-DD') AS Date,
    qc.measure,
    qc.MEASURE_SUCCESS,
    CASE
      WHEN qc.REPAIR_ID IS NOT NULL THEN qc.REPAIR_ID
      ELSE qc.WORKORDER_ID
    END AS source
  FROM
    BIZ.POSTGRES_RDS_ODOO_PUBLIC.QUALITY_CHECK qc
    JOIN BIZ.POSTGRES_RDS_ODOO_PUBLIC.QUALITY_POINT qcp ON qcp.id = qc.POINT_ID FULL
    JOIN BIZ.DBT.PRODUCT_LOT_SERIALS lot ON (
      (lot.LOT_ID = qc.FINISHED_LOT_ID)
      OR (lot.LOT_ID = qc.LOT_ID)
    ) FULL
    JOIN BIZ.DBT_ODOO.USERS user ON user.USER_ID = qc.USER_ID
  WHERE
    (
      qc.point_id IN (
        1159,
        4058,
        63798,
        1382,
        1718,
        1717,
        1024,
        63797,
        1378,
        1381
      )
    ) --   AND lot.PART_NUMBER LIKE '%8563%'
)
SELECT DISTINCT
  m.part_number,
  m.part_name,
  m.serial_lot_number,
  m.method,
  m.date,
  CASE
    WHEN m.repair_id IS NOT NULL THEN ro.name
    ELSE mo.name
  END AS reference,
  ro.stage_ID,
  CASE
    WHEN m.repair_id IS NOT NULL THEN wh.NAME
  END AS location,
  m.name,
  m.measure AS mass,
  cg.measure AS cg
FROM
  quality_checks AS m
  LEFT JOIN quality_checks AS cg ON m.source = cg.source
  FULL JOIN BIZ.POSTGRES_RDS_ODOO_PUBLIC.FLEET_REPAIR ro on ro.ID = m.repair_id
  FULL JOIN BIZ.POSTGRES_RDS_ODOO_PUBLIC.MRP_WORKORDER wo on wo.id = m.workorder_id
  FULL JOIN BIZ.POSTGRES_RDS_ODOO_PUBLIC.MRP_PRODUCTION mo on mo.id = wo.PRODUCTION_ID
  FULL JOIN BIZ.POSTGRES_RDS_ODOO_PUBLIC.STOCK_WAREHOUSE wh on wh.id = ro.warehouse_id
WHERE
  m.type = 'Mass'
  AND cg.type = 'CG'
  AND m.serial_lot_number is not NULL
  AND m.part_number LIKE '%8563%' -- body
  -- AND m.serial_lot_number = 'SBDY-000919'
ORDER BY m.date DESC