prepare_script_update_journal_trx

-- Create temp table

DROP TABLE IF EXISTS temp_fix_coa_trx;

CREATE TABLE temp_fix_coa_trx(
    main_acc CHARACTER VARYING(50),
    sub_acc CHARACTER VARYING(50),
    sign_coa CHARACTER VARYING(1)
);

-- insert coa needed
INSERT INTO temp_fix_coa_trx
VALUES
('610200', '100', 'D'),
('610100', '200', 'D'),
('620100', '300', 'D'),
('620100', '200', 'D'),
('620100', '400', 'D'),
('620500', '300', 'D'),
('620800', '100', 'D'),
('620700', '200', 'D'),
('620700', '300', 'D'),
('620500', '110', 'D'),
('620500', '200', 'D'),
('620500', '210', 'D'),
('620500', '301', 'D'),
('620100', '600', 'D');

-- Check data journal
WITH get_data AS (
    SELECT CASE WHEN X.ou_branch_id = -99 THEN
            CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
        ELSE f_get_ou_code(X.ou_branch_id)
    END AS ou_code,
    X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date, f_get_doc_desc(X.doc_type_id) AS doc_type,
    LEFT(A.remark, 4) AS ou_from_remark, A.remark,
    B.main_acc, B.sub_acc, B.coa_desc, A.sign_journal,
    A.ou_branch_id AS item_ou_branch_id, A.ou_sub_bu_id AS item_ou_sub_bu_id, X.ou_branch_id, X.ou_sub_bu_id
    FROM gl_journal_trx_item A
    INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
    INNER JOIN m_coa B ON A.coa_id = B.coa_id
    INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
    INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
    WHERE A.sign_journal = 'D'
    AND X.doc_date BETWEEN '20201001' AND '20210310'
    AND A.remark <> ''
) SELECT *
FROM get_data A
WHERE A.ou_code IN ('2801', '2803')
AND A.ou_code <> A.ou_from_remark
ORDER BY A.ou_code, A.doc_date;

-- 1. Update gl_journal_trx_item, where ou_from_remark = 2803
WITH prepare_data AS (
    WITH get_data AS (
        SELECT CASE WHEN X.ou_branch_id = -99 THEN
                CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
            ELSE f_get_ou_code(X.ou_branch_id)
        END AS ou_code,
        X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date,
        LEFT(A.remark, 4) AS ou_from_remark, D.ou_id, AS ou_from_remark_id,
        A.ou_branch_id AS item_ou_branch_id, A.ou_sub_bu_id AS item_ou_sub_bu_id
        FROM gl_journal_trx_item A
        INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
        INNER JOIN m_coa B ON A.coa_id = B.coa_id
        INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
        INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
        WHERE A.sign_journal = 'D'
        AND X.doc_date BETWEEN '20201001' AND '20210310'
        AND A.remark <> ''
    ) SELECT *
    FROM get_data A
    WHERE A.ou_code IN ('2801', '2803')
    AND A.ou_code <> A.ou_from_remark
    AND A.ou_from_remark IN = '2803' -- khusus ou_from_remark = 2803
)
UPDATE gl_journal_trx_item X SET ou_branch_id = A.ou_from_remark_id
FROM prepare_data A
WHERE X.journal_trx_item_id = A.journal_trx_item_id
AND X.journal_trx_id = A.journal_trx_id;

-- 2. Update gl_journal_trx_item, where ou_from_remark NOT IN (2801, 2803)
WITH prepare_data AS (
    WITH get_data AS (
        SELECT CASE WHEN X.ou_branch_id = -99 THEN
                CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
            ELSE f_get_ou_code(X.ou_branch_id)
        END AS ou_code,
        X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date,
        LEFT(A.remark, 4) AS ou_from_remark, D.ou_id, AS ou_from_remark_id,
        A.ou_branch_id AS item_ou_branch_id, A.ou_sub_bu_id AS item_ou_sub_bu_id
        FROM gl_journal_trx_item A
        INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
        INNER JOIN m_coa B ON A.coa_id = B.coa_id
        INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
        INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
        WHERE A.sign_journal = 'D'
        AND X.doc_date BETWEEN '20201001' AND '20210310'
        AND A.remark <> ''
    ) SELECT *
    FROM get_data A
    WHERE A.ou_code IN ('2801', '2803')
    AND A.ou_code <> A.ou_from_remark
    AND A.ou_from_remark NOT IN ('2801', '2803') -- ou_from_remark NOT IN (2801, 2803)
)
UPDATE gl_journal_trx_item X SET ou_sub_bu_id = A.ou_from_remark_id
FROM prepare_data A
WHERE X.journal_trx_item_id = A.journal_trx_item_id
AND X.journal_trx_id = A.journal_trx_id;

-- 3. Update gl_journal_trx, where ou_from_remark = 2803
WITH prepare_data AS (
    WITH get_data AS (
        SELECT CASE WHEN X.ou_branch_id = -99 THEN
                CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
            ELSE f_get_ou_code(X.ou_branch_id)
        END AS ou_code,
        X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date,
        LEFT(A.remark, 4) AS ou_from_remark, D.ou_id, AS ou_from_remark_id,
        X.ou_branch_id, X.ou_sub_bu_id
        FROM gl_journal_trx_item A
        INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
        INNER JOIN m_coa B ON A.coa_id = B.coa_id
        INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
        INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
        WHERE A.sign_journal = 'D'
        AND X.doc_date BETWEEN '20201001' AND '20210310'
        AND A.remark <> ''
    ) SELECT *
    FROM get_data A
    WHERE A.ou_code IN ('2801', '2803')
    AND A.ou_code <> A.ou_from_remark
    AND A.ou_from_remark IN = '2803' -- khusus ou_from_remark = 2803
)
UPDATE gl_journal_trx X SET ou_branch_id = A.ou_from_remark_id
FROM prepare_data A
WHERE X.journal_trx_id = A.journal_trx_id;

-- 4. Update gl_journal_trx, where ou_from_remark NOT IN (2801, 2803)
WITH prepare_data AS (
    WITH get_data AS (
        SELECT CASE WHEN X.ou_branch_id = -99 THEN
                CASE WHEN X.ou_sub_bu_id = -99 THEN f_get_ou_code(X.ou_bu_id) ELSE f_get_ou_code(X.ou_sub_bu_id) END
            ELSE f_get_ou_code(X.ou_branch_id)
        END AS ou_code,
        X.journal_trx_id, A.journal_trx_item_id, X.doc_no, X.doc_date,
        LEFT(A.remark, 4) AS ou_from_remark, D.ou_id, AS ou_from_remark_id,
        X.ou_branch_id, X.ou_sub_bu_id
        FROM gl_journal_trx_item A
        INNER JOIN gl_journal_trx X ON A.journal_trx_id = X.journal_trx_id
        INNER JOIN m_coa B ON A.coa_id = B.coa_id
        INNER JOIN temp_fix_coa_trx C ON B.main_acc = C.main_acc AND B.sub_acc = C.sub_acc
        INNER JOIN t_ou D ON LEFT(A.remark, 4) = D.ou_code
        WHERE A.sign_journal = 'D'
        AND X.doc_date BETWEEN '20201001' AND '20210310'
        AND A.remark <> ''
    ) SELECT *
    FROM get_data A
    WHERE A.ou_code IN ('2801', '2803')
    AND A.ou_code <> A.ou_from_remark
    AND A.ou_from_remark NOT IN ('2801', '2803') -- ou_from_remark NOT IN (2801, 2803)
)
UPDATE gl_journal_trx X SET ou_sub_bu_id = A.ou_from_remark_id
FROM prepare_data A
WHERE X.journal_trx_id = A.journal_trx_id;