--berbaikan data DO terkait rab238
-- product TPT307300 duplikat 3 degn qty 2
-- product TPT557200 duplikat 3 degn qty 3

--backup dulu

CREATE TABLE sl_do_item_20200904 AS (
    SELECT * FROM sl_do_item WHERE do_id = 15033554
);

CREATE TABLE in_log_product_balance_stock_20200904 AS (
    SELECT * FROM in_log_product_balance_stock
    WHERE ref_id=15033554 and doc_type_id =311
);

CREATE TABLE sl_so_balance_invoice_20200904 AS (
    SELECT * FROM sl_so_balance_invoice WHERE invoice_id = 15034418
);

CREATE TABLE sl_so_balance_invoice_tax_20200904 AS (
    SELECT * FROM sl_so_balance_invoice_tax WHERE invoice_id=15034418
);

CREATE TABLE sl_invoice_20200904 AS (
    SELECT * FROM sl_invoice WHERE invoice_id=15034418
);

CREATE TABLE sl_invoice_item_20200904 AS (
    SELECT * FROM sl_invoice_item WHERE invoice_id=15034418
);

CREATE TABLE sl_invoice_tax_20200904 AS (
    SELECT * FROM sl_invoice_tax WHERE invoice_id=15034418
);

CREATE TABLE fi_invoice_ar_balance_20200904 AS (
    SELECT * FROM fi_invoice_ar_balance
    WHERE  doc_type_id=321 and invoice_ar_id=15034418
);

CREATE TABLE gl_journal_trx_item_20200904 AS (
    SELECT *
    FROM gl_journal_trx_item
    WHERE journal_trx_id IN (15214349, 15213513)
);

CREATE TABLE gl_journal_trx_mapping_20200904 AS (
    SELECT *
    FROM gl_journal_trx_mapping
    WHERE journal_trx_id IN (15214349, 15213513)
);

CREATE TABLE sl_so_item_20200904 AS (
    SELECT *
    FROM sl_so_item
    WHERE so_id = 15047588
);

CREATE TABLE sl_so_balance_item_20200904 AS (
    SELECT A.*
    FROM sl_so_balance_item A
    INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
    WHERE B.so_id = 15047588
);

CREATE TABLE sl_log_partner_credit_limit_usage_20200904 AS (
    SELECT *
    FROM sl_log_partner_credit_limit_usage A
    WHERE log_partner_credit_limit_usage_id = 130722;
);

CREATE TABLE sl_partner_credit_limit_usage_20200904 AS (
    SELECT *
    FROM sl_partner_credit_limit_usage A
    WHERE partner_credit_limit_usage_id = 69163;
);

CREATE TABLE sl_partner_credit_limit_usage_20200904 AS (
    SELECT *
    FROM sl_partner_credit_limit_usage A
    WHERE partner_credit_limit_usage_id = 69163;
);

CREATE TABLE sl_log_so_balance_item_20200904 AS (
    SELECT A.*
    FROM sl_log_so_balance_item A
    INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
    WHERE B.so_id = 15047588
);

CREATE TABLE sl_customer_info_20200904 AS (
    SELECT A.*
    FROM sl_customer_info A
    WHERE doc_id = 15047588 and doc_type_id = 301
);

CREATE TABLE sl_so_tax_20200904 AS (
    SELECT A.*
    FROM sl_so_tax A
    WHERE so_id = 15047588
);

--mulai perbaikan
-- hapus data duplikat item do
DELETE FROM sl_do_item
WHERE do_id = 15033554 and
      do_item_id IN (15346331, 15346329, 15346330, 15346328);

--perbaikan data item do TPT307300
UPDATE sl_do_item
SET qty_dlv_so = qty_dlv_so*3,
    qty_dlv_int = qty_dlv_int*3
WHERE do_item_id = 15346326;

--perbaikan data item do TPT557200
UPDATE sl_do_item
SET qty_dlv_so = qty_dlv_so*3,
    qty_dlv_int = qty_dlv_int*3
WHERE do_item_id = 15346325;


-- hapus data duplikat balance so invoice dan tax
DELETE FROM sl_so_balance_invoice
WHERE invoice_id = 15034418 and
      ref_id = 15033554 and
      ref_item_id IN (15346331, 15346329, 15346330, 15346328);

--perbaikan data item so balance_invoice TPT307300
UPDATE sl_so_balance_invoice
SET item_amount = item_amount*3,
    qty_dlv_so = qty_dlv_int*3
WHERE ref_id = 15033554 and
      ref_item_id=15346326;

--perbaikan data item so balance_invoice TPT557200
UPDATE sl_so_balance_invoice
SET item_amount = item_amount*3,
    qty_dlv_so = qty_dlv_int*3
WHERE ref_id = 15033554 and
      ref_item_id=15346325;

DELETE FROM sl_so_balance_invoice_tax
WHERE invoice_id = 15034418 and
      ref_id = 15033554 and
      ref_item_id IN (15346331, 15346329, 15346330, 15346328);

--perbaikan data item so balance_invoice TPT307300
UPDATE sl_so_balance_invoice_tax
SET base_amount = base_amount * 3,
    tax_amount = tax_amount * 3
WHERE ref_id = 15033554 and
      ref_item_id=15346326;

--perbaikan data item so balance_invoice TPT557200
UPDATE sl_so_balance_invoice_tax
SET base_amount = base_amount * 3,
    tax_amount = tax_amount * 3
WHERE ref_id = 15033554 and
      ref_item_id=15346325;

--berbaikan log stock
--product TPT307300
--UPDATE in_log_product_balance_stock
--SET qty = qty + 4
--WHERE ref_id=15033554 and doc_type_id =311 and product_id = 15100481;

--product TPT557200
--UPDATE in_log_product_balance_stock
--SET qty = qty + 6
--WHERE ref_id=15033554 and doc_type_id =311 and product_id = 15100558;


---nilai total net amount yg dihapus di do  722910
--select sum(item_amount) FROM sl_so_balance_invoice
--WHERE invoice_id = 15034418 and
--    ref_id = 15033554 and
--    ref_item_id IN (15346331, 15346329, 15346330, 15346328);
---nilai total tax amount yg dihapus di do  72290
--select sum(tax_amount) FROM sl_so_balance_invoice_tax
--WHERE invoice_id = 15034418 and
--    ref_id = 15033554 and
--    ref_item_id IN (15346331, 15346329, 15346330, 15346328);

--update header invoice
--UPDATE sl_invoice
--SET gross_amount = gross_amount -(722910 + 72290),
--  tax_amount = tax_amount - 72290,
--  total_amount = total_amount - 722910
--WHERE invoice_id = 15034418;

--delete item invoice
DELETE FROM sl_invoice_item
WHERE invoice_id = 15034418 AND
      ref_item_id IN (15346331, 15346329, 15346330, 15346328);

UPDATE sl_invoice_item
SET ref_item_amount = ref_item_amount * 3
WHERE invoice_id = 15034418 AND
      ref_item_id IN (15346326, 15346325);

--update invoice tax
--UPDATE sl_invoice_tax
--SET base_amount = base_amount -722910,
--  tax_amount = tax_amount - 72290,
--  gov_tax_amount = gov_tax_amount - 72290
--WHERE invoice_id = 15034418;

--UPDATE fi_invoice_ar_balance
--SET  amount = amount - (722910 + 72290)
--WHERE  doc_type_id=321 and invoice_ar_id=15034418;

--perbaikan jurnal DO (hapus data duplikat)
DELETE FROM gl_journal_trx_item
WHERE journal_trx_id =15213513 AND
      journal_trx_item_id IN (16240881, 16240880, 16240879, 16240878);

--perbaikan jurnal DO (penyesuaian qty)
UPDATE gl_journal_trx_item
SET qty = qty * 3
WHERE journal_trx_id =15213513 AND
      journal_trx_item_id IN (16240877, 16240876);

--perbaikan jurnal SI (hapus data duplikat)
DELETE FROM gl_journal_trx_item
WHERE journal_trx_id =15214349 AND
      journal_trx_item_id IN (16247144, 16247143, 16247142, 16247141);

--perbaikan jurnal SI (penyesuaian qty)
UPDATE gl_journal_trx_item
SET amount = amount * 3
WHERE journal_trx_id =15214349 AND
      journal_trx_item_id IN (16247140, 16247139);

--========= masuk perbaikan SO ===============-------------------
--update SO ITEM
--TPT557200
UPDATE sl_so_item
set qty_so = qty_so * 3,
    qty_int = qty_int * 3,
    tax_amount = tax_amount * 3,
    nett_item_amount = nett_item_amount * 3
WHERE so_item_id in (15432792);

--TPT307300
UPDATE sl_so_item
set qty_so = qty_so * 3,
    qty_int = qty_int * 3,
    tax_amount = tax_amount * 3,
    nett_item_amount = nett_item_amount * 3
WHERE so_item_id in (15432809);

--update SO ITEM balance
--TPT557200
UPDATE sl_so_balance_item
SET qty_so = qty_so + 6,
    qty_so_int = qty_so_int + 6,
    qty_dlv = qty_dlv * 3,
    qty_dlv_int = qty_dlv_int * 3
WHERE so_item_id in (15432792);

--TPT307300
UPDATE sl_so_balance_item
SET qty_so = qty_so * 3,
    qty_so_int = qty_so_int * 3,
    qty_dlv = qty_dlv * 3,
    qty_dlv_int = qty_dlv_int * 3
WHERE so_item_id in (15432809);

--perbaikan credit limit usage
UPDATE sl_log_partner_credit_limit_usage
SET amount = amount + ((244200 +153400)*2)
WHERE log_partner_credit_limit_usage_id = 130722;

UPDATE sl_partner_credit_limit_usage
SET amount = amount + ((244200 +153400)*2)
partner_credit_limit_usage_id = 69163;

--log so balance item TPT557200
UPDATE sl_log_so_balance_item
SET qty_trx = qty_trx + 6,
    qty_int = qty_int + 6
WHERE log_so_balance_item_id = 15857657;

--log so balance item  TPT307300
UPDATE sl_log_so_balance_item
SET qty_trx = qty_trx + 4,
    qty_int = qty_int + 4
WHERE log_so_balance_item_id = 15857674;

-- perbaikan informasi pop up plafon so
UPDATE sl_customer_info
SET remain_plafon_amount = remain_plafon_amount + ((244200 +153400)*2),
    trx_amount = trx_amount + ((244200 +153400)*2)
WHERE doc_type_id = 301 AND
      doc_id = 15047588;

-- perbaikan so tax
UPDATE sl_so_tax
SET base_amount = base_amount + (( 222000 + 139455 ) * 2),
    tax_amount = tax_amount + ((22200 + 13945) * 2)
WHERE so_id = 15047588;

--UPDATE gl_journal_trx_item
--SET amount = amount - 72290
--WHERE journal_trx_id =15214349 AND
--    journal_trx_item_id = 16247145;

--UPDATE gl_journal_trx_mapping
--SET amount = amount - (722910 + 72290)
--WHERE journal_trx_id = 15214349;