review query terkait jen-6

SET SEARCH_PATH TO jenindo;

SELECT * FROM pu_po
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

SELECT * FROM pu_po_balance_invoice
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

SELECT * FROM in_product_price_balance
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

SELECT * FROM in_log_product_balance_stock
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

SELECT * FROM in_log_product_price_balance_stock
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

SELECT * FROM in_balance_receive_goods_item A
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

SELECT *
FROM  pu_monthly_price_product
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

SELECT * FROM pu_log_monthly_price_product
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

SELECT * FROM fi_invoice_ap_balance
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

SELECT * 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'));
-- 10 rows



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;

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;

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;