Script Update Issue JEN-6

--> pu_po
UPDATE pu_po SET ou_id = 10
WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
AND doc_type_id = 160;
--> 5 Rows Data

--> pu_po_balance_invoice
UPDATE pu_po_balance_invoice SET ou_id = 10
WHERE ref_doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
AND ref_doc_type_id = 160;
--> 9 Rows Data

--> in_product_price_balance
UPDATE in_product_price_balance SET ou_id = 10
WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
AND doc_type_id = 160;
--> 9 Rows Data

--> in_log_product_balance_stock
UPDATE in_log_product_balance_stock SET ou_id = 10
WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
AND doc_type_id = 160;
--> 9 Rows Data

--> in_log_product_price_balance_stock
UPDATE in_log_product_price_balance_stock SET ou_id = 10
WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
AND doc_type_id = 160;
--> 9 Rows Data

--> in_balance_receive_goods_item
UPDATE in_balance_receive_goods_item A SET ou_id = 10
WHERE EXISTS (SELECT 1 FROM pu_po B
              WHERE A.po_id = B.po_id
              AND A.po_no = B.doc_no
              AND B.doc_type_id = 160
              AND B.doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031'));
--> 9 Rows Data

--> pu_monthly_price_product
UPDATE pu_monthly_price_product SET ou_id = 10
WHERE ref_doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
AND ref_doc_type_id = 160;
--> 5 Rows Data

--> pu_log_monthly_price_product
UPDATE pu_log_monthly_price_product SET ou_id = 10
WHERE ref_doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
AND ref_doc_type_id = 160;
--> 9 Rows Data

--> fi_invoice_ap_balance
UPDATE fi_invoice_ap_balance SET ou_id = 10
WHERE doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031')
AND doc_type_id = 160;
--> 5 Rows Data

--> gl_admin_journal_trx OU Jenindo
--> Insert gl_admin_journal_trx OU Jenindo
INSERT INTO gl_admin_journal_trx
        (tenant_id, ou_id, sub_ou_id, journal_type, ledger_code,
        journal_process_periode, journal_year_month, journal_counter,
        process_datetime, process_user_id, create_datetime, create_user_id,
        update_datetime, update_user_id)
SELECT tenant_id, 10, 10, journal_type, ledger_code,
        journal_process_periode, journal_year_month, 0,
        process_datetime, process_user_id, create_datetime, create_user_id,
        update_datetime, update_user_id
FROM gl_admin_journal_trx A
WHERE A.tenant_id = 10
AND A.ou_id = 11
AND A.journal_type IN ('AP.PURCH','STOCK.PURCH')
AND A.journal_year_month = '202108'
AND A.journal_process_periode = 'DAILY'
AND NOT EXISTS (SELECT 1 FROM gl_admin_journal_trx B
                WHERE B.tenant_id = 10
                AND A.admin_journal_trx_id = B.admin_journal_trx_id
                AND B.ou_id = 10
                AND B.journal_type IN ('AP.PURCH','STOCK.PURCH')
                AND B.journal_year_month = '202108'
                AND B.journal_process_periode = 'DAILY');

--> Update gl_admin_journal_trx OU Jenindo
WITH temp_data_admin_journal AS (
    SELECT A.tenant_id, A.ou_bu_id, A.journal_type, LEFT(A.doc_date,6) AS journal_year_month,
           COUNT(A.journal_trx_id) AS total_journal
    FROM gl_journal_trx A
    WHERE EXISTS (SELECT 1 FROM pu_po B
                  WHERE A.doc_id = B.po_id
                  AND A.doc_no = B.doc_no
                  AND B.doc_type_id = 160
                  AND B.doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031'))
    GROUP BY A.tenant_id, A.ou_bu_id, A.journal_type, journal_year_month
)
UPDATE gl_admin_journal_trx A
SET journal_counter = A.journal_counter + B.total_journal
FROM temp_data_admin_journal B
WHERE A.tenant_id = B.tenant_id
AND A.ou_id = 10
AND A.journal_type = B.journal_type
AND A.journal_year_month = B.journal_year_month
AND EXISTS (SELECT 1 FROM gl_admin_journal_trx C
               WHERE C.tenant_id = 10
               AND A.admin_journal_trx_id = C.admin_journal_trx_id
               AND C.ou_id = 10
               AND C.journal_type IN ('AP.PURCH','STOCK.PURCH')
               AND C.journal_year_month = '202108'
               AND C.journal_process_periode = 'DAILY');
--> 2 Rows Data

--> gl_admin_journal_trx OU HB
--> Update gl_admin_journal_trx OU HB
WITH temp_data_admin_journal AS (
    SELECT A.tenant_id, A.ou_bu_id, A.journal_type, LEFT(A.doc_date,6) AS journal_year_month,
           COUNT(A.journal_trx_id) AS total_journal
    FROM gl_journal_trx A
    WHERE EXISTS (SELECT 1 FROM pu_po B
                  WHERE A.doc_id = B.po_id
                  AND A.doc_no = B.doc_no
                  AND B.doc_type_id = 160
                  AND B.doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031'))
    GROUP BY A.tenant_id, A.ou_bu_id, A.journal_type, journal_year_month
)
UPDATE gl_admin_journal_trx A
SET journal_counter = A.journal_counter - B.total_journal
FROM temp_data_admin_journal B
WHERE A.tenant_id = B.tenant_id
AND A.ou_id = B.ou_bu_id
AND A.journal_type = B.journal_type
AND A.journal_year_month = B.journal_year_month;
--> 2 Rows Data

--> gl_journal_trx
UPDATE gl_journal_trx A SET ou_bu_id = 10
WHERE EXISTS (SELECT 1 FROM pu_po B
              WHERE A.doc_id = B.po_id
              AND A.doc_no = B.doc_no
              AND B.doc_type_id = 160
              AND B.doc_no IN ('HGP-2108-00027','HGP-2108-00028','HGP-2108-00029','HGP-2108-00030','HGP-2108-00031'));
--> 10 Rows Data