oke

-- # cari qty reserved stock yg selisih
WITH detail_transaksi AS (
    -- ambil booking reserved dari settlement (belum di DO maupun lagi proses verify)
     SELECT f_get_product_name(B.product_id) AS product_name, B.product_id,
            A.doc_no, A.doc_date, B.qty_settlement AS qty
     FROM sl_so_settlement A
     INNER JOIN sl_so_settlement_item B ON A.so_settlement_id = B.so_settlement_id
     WHERE EXISTS(
         SELECT 1
         FROM sl_so_settlement C
         INNER JOIN sl_so X ON X.so_id = C.ref_id AND X.doc_type_id = C.ref_doc_type_id
         WHERE A.tenant_id = C.tenant_id
         AND A.so_settlement_id = C.so_settlement_id
         AND X.status_doc <> 'V'
         AND NOT EXISTS (SELECT 1 FROM sl_do D WHERE C.ref_id = D.ref_id AND C.ref_doc_type_id = D.ref_doc_type_id AND D.status_doc = 'R' )
         AND NOT EXISTS (SELECT 1 FROM sl_so_status E WHERE X.so_id = E.so_id AND E.status_do = 'V' )
         )
     AND A.tenant_id = 10
     AND A.ou_id = 10
     AND B.qty_settlement > 0

     UNION ALL

    -- Ambil bookingan reserved dari verifikasi DO (sudah diverify /scan lengkap dan belum sampai di DO)
     SELECT f_get_product_name(B.product_id) AS product_name, B.product_id,
            A.doc_no, A.doc_date, B.qty_verify AS qty
     FROM in_verification_header A
     INNER JOIN in_verification_item B ON A.verification_id = B.verification_id
     INNER JOIN sl_so X ON X.so_id = A.doc_id AND X.doc_type_id = A.doc_type_id
     WHERE A.tenant_id = 10
     AND B.qty_verify > 0
     AND X.ou_id = 10
     AND X.status_doc <> 'V'
     AND NOT EXISTS (SELECT 1 FROM sl_do D WHERE X.so_id = D.ref_id AND X.doc_type_id = D.ref_doc_type_id  AND D.status_doc = 'R' )
     AND EXISTS (SELECT 1 FROM sl_so_status E WHERE X.so_id = E.so_id AND E.status_do = 'V' )
     UNION ALL
     SELECT f_get_product_name(B.product_id) AS product_name, B.product_id,
            A.ref_doc_no AS doc_no, A.ref_doc_date AS doc_date, B.qty_current_scan AS qty
     FROM in_scanner_header A
     INNER JOIN in_scanner_item B ON A.scanner_header_id = B.scanner_header_id
     INNER JOIN sl_so X ON X.so_id = A.ref_doc_id AND X.doc_type_id = A.ref_doc_type_id
     WHERE A.tenant_id = 10
     AND B.qty_current_scan > 0 AND B.scan_status = 'SL'
     AND X.ou_id = 10
     AND X.status_doc <> 'V'
     AND NOT EXISTS (SELECT 1 FROM sl_do D WHERE X.so_id = D.ref_id AND X.doc_type_id = D.ref_doc_type_id  AND D.status_doc = 'R' )
     AND EXISTS (SELECT 1 FROM sl_so_status E WHERE X.so_id = E.so_id AND E.status_do = 'V' )

     UNION ALL

    -- Ambil qty reserved dari cancel sob, dan sudah disettlement (belum di DO maupun lagi proses di verify)
     SELECT f_get_product_name(F.product_id) AS product_name, F.product_id,
            A.doc_no, A.doc_date, -1*F.qty_settlement AS qty
     FROM sl_manage_so A
     INNER JOIN sl_manage_so_item B ON A.manage_so_id = B.manage_so_id
     INNER JOIN sl_so_item C ON B.so_item_id = C.so_item_id
     INNER JOIN sl_so D ON C.so_id = D.so_id
     INNER JOIN sl_so_settlement E ON D.so_id = E.ref_id AND D.doc_type_id = E.ref_doc_type_id
     INNER JOIN sl_so_settlement_item F ON E.so_settlement_id = F.so_settlement_id AND C.product_id = F.product_id
     WHERE A.tenant_id = 10
     AND A.ou_id = 10
     AND A.doc_type_id = 392
     AND A.status_doc = 'R'
     AND D.status_doc <> 'V'
     AND F.qty_settlement > 0
     AND NOT EXISTS (SELECT 1 FROM sl_do X WHERE E.ref_id = X.ref_id AND E.ref_doc_type_id = X.ref_doc_type_id AND X.status_doc = 'R')
     AND NOT EXISTS (SELECT 1 FROM sl_so_status Y WHERE A.so_id = Y.so_id AND Y.status_do = 'V' )

     UNION ALL

    -- ambil booking reserved dari sob yang belum di settle
     SELECT f_get_product_name(B.product_id) AS product_name, B.product_id,
            A.doc_no, A.doc_date, B.qty_int AS qty
     FROM sl_so A
     INNER JOIN sl_so_item B ON A.so_id = B.so_id
     WHERE NOT EXISTS(
         SELECT 1
         FROM sl_so_settlement C
         WHERE A.tenant_id = C.tenant_id
         AND A.so_id = C.ref_id AND A.doc_type_id = C.ref_doc_type_id
         )
     AND A.tenant_id = 10
     AND A.ou_id = 10
     AND A.status_doc IN ('R' , 'I')
     AND A.workflow_status <> 'CHANGES REQUESTED'

     UNION ALL

    -- ambil booking reserved dari REXO (belum di EXO maupun lagi proses verify)
     SELECT f_get_product_name(B.product_id) AS product_name, B.product_id,
            A.doc_no, A.doc_date, B.qty
     FROM in_in_out_exchange A
     INNER JOIN in_in_out_exchange_item B ON A.in_out_exchange_id = B.in_out_exchange_id
     INNER JOIN in_in_out_exchange_status C ON C.in_out_exchange_id = A.in_out_exchange_id
     WHERE A. tenant_id = 10
     AND A.ou_id = 10
     AND A.doc_type_id = 599
     AND C.status_exchange_out NOT IN ('V', 'Y')

     UNION ALL

    -- Ambil bookingan reserved dari verifikasi EXO (sudah diverify /scan lengkap dan belum sampai di EXO)
     SELECT f_get_product_name(B.product_id) AS product_name, B.product_id,
            A.doc_no, A.doc_date, B.qty_verify AS qty
     FROM in_verification_header A
     INNER JOIN in_verification_item B ON A.verification_id = B.verification_id
     INNER JOIN in_in_out_exchange X ON X.in_out_exchange_id = A.doc_id AND X.doc_type_id = A.doc_type_id
     WHERE A.tenant_id = 10
     AND B.qty_verify > 0
     AND X.ou_id = 10
     AND X.doc_type_id = 599
     AND NOT EXISTS (SELECT 1 FROM in_in_out_exchange D
                    WHERE A.doc_id = D.req_id AND A.doc_type_id = D.req_doc_type_id AND D.doc_type_id = 565 AND D.status_doc = 'R' )
     AND EXISTS (SELECT 1 FROM in_in_out_exchange_status E
                    WHERE X.in_out_exchange_id = E.in_out_exchange_id AND E.status_exchange_out = 'V' )
     UNION ALL
     SELECT f_get_product_name(B.product_id) AS product_name, B.product_id,
            A.ref_doc_no AS doc_no, A.ref_doc_date AS doc_date,  B.qty_current_scan AS qty
     FROM in_scanner_header A
     INNER JOIN in_scanner_item B ON A.scanner_header_id = B.scanner_header_id
     INNER JOIN in_in_out_exchange X ON X.in_out_exchange_id = A.ref_doc_id AND X.doc_type_id = A.ref_doc_type_id
     WHERE A.tenant_id = 10
     AND B.qty_current_scan > 0AND B.scan_status = 'SL'
     AND X.ou_id = 10
     AND X.doc_type_id = 599
     AND NOT EXISTS (SELECT 1 FROM in_in_out_exchange D
                    WHERE A.doc_id = D.req_id AND A.doc_type_id = D.req_doc_type_id AND D.doc_type_id = 565 AND D.status_doc = 'R' )
     AND EXISTS (SELECT 1 FROM in_in_out_exchange_status E
                    WHERE X.in_out_exchange_id = E.in_out_exchange_id AND E.status_exchange_out = 'V' )
), stock_reserved_in_trx AS (
    SELECT product_id, SUM(qty) AS qty
    FROM detail_transaksi
    GROUP BY product_id
)
SELECT A.product_id, C.product_code, C.product_name,
       A.qty AS qty_reserved_stock, COALESCE(B.qty, 0) AS qty_trx,
       (A.qty - COALESCE(B.qty, 0)) AS qty_diff
FROM in_product_balance_stock_reserved A
INNER JOIN m_product C ON A.product_id = C.product_id
LEFT JOIN stock_reserved_in_trx B ON A.product_id = B.product_id
WHERE (A.qty - COALESCE(B.qty, 0)) <> 0
ORDER BY C.product_name;