recalculate reserved stock

 -- Script untuk recalculate reserved stock

 -- Create table temporary
  CREATE TEMPORARY TABLE temp_trx_of_reserved_stock (
     session_id character varying (100) NOT NULL,
     data_type character varying (50) NOT NULL,
     product_name character varying (100),
     product_id bigint,
     doc_no character varying (50),
     doc_date character varying (8),
     qty numeric
 );

 -- # Insert transaksi-transaksi yang mempengaruhi reserved stock ke table temp
    -- ambil booking reserved dari settlement (belum di DO maupun lagi proses verify)
     INSERT INTO temp_trx_of_reserved_stock (
        session_id, data_type, product_name, product_id, doc_no, doc_date, qty)
     SELECT 'RECALCULATE-RESERVEDSTOCK' AS session_id, 'qty_sos_non_do_non_verify' AS data_type,
        f_get_product_name(B.product_id) AS product_name, B.product_id, A.doc_no, A.doc_date, B.qty_settlement
     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
     ORDER BY f_get_product_name(B.product_id);

    -- Ambil bookingan reserved dari verifikasi DO (sudah diverify /scan lengkap dan belum sampai di DO)
     INSERT INTO temp_trx_of_reserved_stock (
        session_id, data_type, product_name, product_id, doc_no, doc_date, qty)
     SELECT 'RECALCULATE-RESERVEDSTOCK' AS session_id, 'qty_verify_non_do' AS data_type,
        f_get_product_name(B.product_id) AS product, B.product_id, A.doc_no, A.doc_date, B.qty_verify
     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 'RECALCULATE-RESERVEDSTOCK' AS session_id, 'qty_verify_non_do' AS data_type,
        f_get_product_name(B.product_id) AS product, B.product_id, A.ref_doc_no AS doc_no, A.ref_doc_date AS doc_date,
     B.qty_current_scan  AS qty_verify
     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' )
     ORDER BY product;

    -- Ambil qty reserved dari cancel sob, dan sudah disettlement (belum di DO maupun lagi proses di verify)
     INSERT INTO temp_trx_of_reserved_stock (
        session_id, data_type, product_name, product_id, doc_no, doc_date, qty)
     SELECT 'RECALCULATE-RESERVEDSTOCK' AS session_id, 'qty_cancel_so_with_settle' AS data_type,
        f_get_product_name(F.product_id), F.product_id, A.doc_no, A.doc_date, -1*F.qty_settlement AS qty_cancel
     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' )
     ORDER BY f_get_product_name(F.product_id);

    -- ambil booking reserved dari sob yang belum di settle
     INSERT INTO temp_trx_of_reserved_stock (
        session_id, data_type, product_name, product_id, doc_no, doc_date, qty)
     SELECT 'RECALCULATE-RESERVEDSTOCK' AS session_id, 'qty_sob_non_settle' AS data_type,
        f_get_product_name(B.product_id), 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'
     ORDER BY f_get_product_name(B.product_id);

    -- ambil booking reserved dari REXO (belum di EXO maupun lagi proses verify)
     INSERT INTO temp_trx_of_reserved_stock (
        session_id, data_type, product_name, product_id, doc_no, doc_date, qty)
     SELECT 'RECALCULATE-RESERVEDSTOCK' AS session_id, 'qty_rexo_non_exo_non_verify' AS data_type,
        f_get_product_name(B.product_id), 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')
     ORDER BY f_get_product_name(B.product_id);

    -- Ambil bookingan reserved dari verifikasi EXO (sudah diverify /scan lengkap dan belum sampai di EXO)
     INSERT INTO temp_trx_of_reserved_stock (
        session_id, data_type, product_name, product_id, doc_no, doc_date, qty)
     SELECT 'RECALCULATE-RESERVEDSTOCK' AS session_id, 'qty_verify_non_exo' AS data_type,
        f_get_product_name(B.product_id) AS product, B.product_id, A.doc_no, A.doc_date, B.qty_verify
     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 'RECALCULATE-RESERVEDSTOCK' AS session_id, 'qty_verify_non_exo' AS data_type,
        f_get_product_name(B.product_id) AS product, B.product_id, A.ref_doc_no AS doc_no, A.ref_doc_date AS doc_date,
     B.qty_current_scan  AS qty_verify
     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' )
     ORDER BY product;

     -- Apakah ada qty reserved stock yang tidak sesuai
     -- ( qty in_product_balance_stock_reserved vs qty transaksi )
     WITH stock_reserved_in_trx AS (
        SELECT product_id, SUM(qty) AS qty
        FROM temp_trx_of_reserved_stock
        WHERE session_id = 'RECALCULATE-RESERVEDSTOCK'
        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;

     -- backup data product balance stock reserved sebelum diupdate
     CREATE TABLE in_product_balance_stock_reserved_20210528 AS
        SELECT * FROM in_product_balance_stock_reserved;

     -- Recalculate qty reserved stock berdasarkan total qty transaksi
     -- hanya untuk product yg qty reserved stock nya tidak sesuai
     BEGIN;
         WITH data_temp_reserved_stock AS (
            SELECT A.product_id, A.qty AS qty_reserved_stock, COALESCE(B.qty_trx, 0) AS qty_trx
            FROM in_product_balance_stock_reserved A
            LEFT JOIN (
                SELECT product_id, SUM(qty) AS qty_trx
                FROM temp_trx_of_reserved_stock
                WHERE session_id = 'RECALCULATE-RESERVEDSTOCK'
                GROUP BY product_id
            ) B ON A.product_id = B.product_id
            WHERE (A.qty - COALESCE(B.qty_trx, 0)) <> 0
         )
         UPDATE in_product_balance_stock_reserved A
         SET qty = B.qty_trx,
             version = A.version+1,
             update_user_id = -2,
             update_datetime = TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS')
         FROM data_temp_reserved_stock B
         WHERE A.product_id = B.product_id;
     --ROLLBACK;
     END;