-- LOT Split/Merge History Query (拆併批歷史紀錄)
-- Query by CONTAINERID list from same work order
-- Check both TARGET (CONTAINERID) and SOURCE (FROMCONTAINERID) to find all related records

WITH work_order_lots AS (
    SELECT CONTAINERID
    FROM DWH.DW_MES_CONTAINER
    WHERE MFGORDERNAME = :work_order
)
SELECT
    h.HISTORYMAINLINEID,
    h.CDONAME AS OPERATION_TYPE,
    h.CONTAINERID AS TARGET_CONTAINERID,
    h.CONTAINERNAME AS TARGET_LOT,
    h.FROMCONTAINERID AS SOURCE_CONTAINERID,
    h.FROMCONTAINERNAME AS SOURCE_LOT,
    h.QTY AS TARGET_QTY,
    h.TXNDATE
FROM DWH.DW_MES_HM_LOTMOVEOUT h
WHERE (
    h.CONTAINERID IN (SELECT CONTAINERID FROM work_order_lots)
    OR h.FROMCONTAINERID IN (SELECT CONTAINERID FROM work_order_lots)
)
  AND h.FROMCONTAINERID IS NOT NULL
ORDER BY h.TXNDATE
FETCH FIRST 100 ROWS ONLY
