script_for_update_stock_webclinic

-- BEGIN TRANSACTION
BEGIN;

-- UPDATE ALL STOCK MENJADI 0
UPDATE in_product_balance_stock A SET
    qty = 0,
    version = A.version + 1,
    update_datetime = to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
    update_username = 'SuperAdmin';

-- PREPARE DATA FOR UPDATE STOCK
WITH data_closed_ledger AS (
    SELECT A.record_owner_id, COALESCE(MAX(A.year_month), MIN(A.year_month)) AS year_month_closed,
        to_char((to_date(COALESCE(MAX(A.year_month), MIN(A.year_month)), 'YYYYMM') + interval '1 month'), 'YYYYMM') next_last_closed_ledger
        FROM m_admin_process_ledger A
        WHERE A.status_ledger = '1'
        AND year_month < to_char(current_date, 'YYYYMM')
    GROUP BY A.record_owner_id
), data_summary_and_data_log_transaksi AS (
    SELECT A.record_owner_id, A.product_balance_id, A.product_id, A.warehouse_id, 'GOOD' AS status,
        A.qty
    FROM in_summary_monthly_qty A
    INNER JOIN data_closed_ledger B ON A.record_owner_id = B.record_owner_id
    WHERE A.year_month = B.next_last_closed_ledger

    UNION ALL

    SELECT A.record_owner_id, A.product_balance_id, A.product_id, A.warehouse_id, 'GOOD' AS status,
        A.qty
    FROM in_log_product_balance_stock A
    INNER JOIN data_closed_ledger B ON A.record_owner_id = B.record_owner_id
    WHERE A.ref_doc_date BETWEEN B.next_last_closed_ledger||'01' AND to_char(current_date, 'YYYYMMDD')
), data_for_update AS (
    SELECT A.record_owner_id, B.record_owner_code, A.product_balance_id, A.product_id, A.warehouse_id, A.status, SUM(A.qty) AS qty
    FROM data_summary_and_data_log_transaksi A
    INNER JOIN m_record_owner B ON A.record_owner_id = B.record_owner_id
    GROUP BY A.record_owner_id, B.record_owner_code, A.product_balance_id, A.product_id, A.warehouse_id, A.status
    ORDER BY A.record_owner_id
), update_data_stock AS (
    -- UPDATE DATA STOCK
    UPDATE in_product_balance_stock A SET
        qty = B.qty,
        version = A.version + 1,
        update_datetime = to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
        update_username = 'SuperAdmin'
    FROM data_for_update B
    WHERE A.record_owner_id = B.record_owner_id
    AND A.product_balance_id = B.product_balance_id
    AND A.product_id = B.product_id
    AND A.warehouse_id = B.warehouse_id
    AND A.product_status = B.status
)
-- INSERT DATA STOCK
INSERT INTO in_product_balance_stock(
    record_owner_id, product_balance_id,
    product_id, warehouse_id, product_status, qty, version, create_datetime,
    create_username, update_datetime, update_username)
SELECT A.record_owner_id, A.product_balance_id,
    A.product_id, A.warehouse_id, 'GOOD', A.qty, 0, to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
    'SuperAdmin', to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 'SuperAdmin'
FROM data_for_update A
WHERE NOT EXISTS (
    SELECT 1 FROM in_product_balance_stock Z
    WHERE Z.record_owner_id = A.record_owner_id
    AND Z.product_balance_id = A.product_balance_id
    AND Z.product_id = A.product_id
    AND Z.warehouse_id = A.warehouse_id
    AND Z.product_status = A.status
);

-- END TRANSACTION
END;