Prepare update akses OU RC XX Penjualan

/*
SASA-410
Yang akan dilakukan:
1. perbaikan formula di laporan laba rugi detail [OK]
2. Menonaktifkan OU RC 28XX PENJUALAN dan mencabut hak akses dari semua user untuk akses data ke OU RC 28XX PENJUALAN
*/
-- 1. Daftar OU RC 28XX Penjualan

    SELECT *
    FROM t_ou
    WHERE ou_type_id = 12 -- type ou Departement
    --AND ou_code <> 'ALL_DEPT'
    AND ou_code LIKE '28%'

-- 2. Update status active = 'N'

    UPDATE t_ou SET active = 'N'
    WHERE ou_type_id = 12 -- type ou Departement
    --AND ou_code <> 'ALL_DEPT'
    AND ou_code LIKE '28%'

-- 3. Nonaktifkan policy untuk akses OU di atas

    DELETE FROM t_policy_ou
    WHERE EXISTS(
        SELECT 1
        FROM t_ou A
        WHERE A.ou_type_id = 12 -- type ou Departement
        --AND ou_code <> 'ALL_DEPT'
        AND A.ou_code LIKE '28%'
        AND A.ou_id = t_policy_ou.ou_id
    )

/*
3. data jurnal transaksi yg masih mengandugn OU RC 28XX PENJUALAN akan dipindahkan ke OU RC PENJUALAN mulai bulan Januari 2021 s/d sekarang
*/
-- 1. Check data trx jurnal yang ada OU RC 28XX PENJUALAN
    SELECT * FROM vw_gl_journal_trx_details B
    WHERE EXISTS (
        SELECT 1
        FROM t_ou A
        WHERE A.ou_type_id = 12 -- type ou Departement
        AND A.ou_code LIKE '28%'
        AND (A.ou_id = B.ou_rc_id)
    )

-- 2. Update OU RC = 93 (ou_id PENJUALAN)
-- table yg di update: gl_journal_trx_item, gl_journal_trx_mapping, gl_journal_trx_fx

 UPDATE gl_journal_trx_item SET ou_rc_id = 93 -- OU PENJUALAN
 -- SELECT * FROM gl_journal_trx_item
 WHERE EXISTS(
        SELECT 1
        FROM gl_journal_trx B
        INNER JOIN t_ou C ON C.ou_id = gl_journal_trx_item.ou_rc_id
        WHERE C.ou_type_id = 12 -- type ou Departement
        AND C.ou_code LIKE '28%'
        AND B.doc_date >= '20210101' -- periode >= '20210101'
        AND gl_journal_trx_item.journal_trx_id = B.journal_trx_id
 )

 UPDATE gl_journal_trx_mapping SET ou_rc_id = 93
 -- SELECT * FROM gl_journal_trx_mapping
 WHERE EXISTS(
        SELECT 1
        FROM gl_journal_trx B
        INNER JOIN t_ou C ON C.ou_id = gl_journal_trx_mapping.ou_rc_id
        WHERE C.ou_type_id = 12 -- type ou Departement
        AND C.ou_code LIKE '28%'
        AND B.doc_date >= '20210101'
        AND gl_journal_trx_mapping.journal_trx_id = B.journal_trx_id
 )

 UPDATE gl_journal_trx_fx SET ou_rc_id = 93
 -- SELECT * FROM gl_journal_trx_fx
 WHERE EXISTS(
        SELECT 1
        FROM gl_journal_trx B
        INNER JOIN t_ou C ON C.ou_id = gl_journal_trx_fx.ou_rc_id
        WHERE C.ou_type_id = 12 -- type ou Departement
        AND C.ou_code LIKE '28%'
        AND B.doc_date >= '20210101'
        AND gl_journal_trx_fx.journal_trx_id = B.journal_trx_id
 )