Script Update Issue MF-208

--> Update Summary Sales Invoice
UPDATE sl_invoice_tax A SET base_amount = 75000, tax_amount = 75000, gov_tax_amount = 75000
WHERE EXISTS (SELECT 1 FROM sl_invoice B WHERE A.invoice_id = B.invoice_id AND B.doc_no = 'MEL22.01.1803');

--> Update Journal
UPDATE gl_journal_trx_item SET amount = 75000, gl_amount = 75000 WHERE journal_trx_item_id = 175874;
UPDATE gl_journal_trx_item SET amount = 68181.82, gl_amount = 68181.82 WHERE journal_trx_item_id = 175873;

--> Insert VAT Out For Ongkir
INSERT INTO fi_vat_out_for_efaktur
(tenant_id, doc_id, doc_type_id, doc_no, doc_date, record_type, ou_id, partner_id, tax_no, tax_date,
 column_count, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14,
 col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, create_datetime, update_datetime,
 create_user_id, update_user_id, version)
SELECT tenant_id, doc_id, doc_type_id, doc_no, doc_date, record_type, ou_id, partner_id, tax_no, tax_date,
 column_count, 'ONGKIR', 'ONGKOS KIRIM', '68181.82', col4, '68181.82', col6, '68181.82', '68181.82', col9, col10, col11, col12, col13, col14,
 col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, create_datetime, update_datetime,
 create_user_id, update_user_id, version
FROM fi_vat_out_for_efaktur
WHERE doc_no = 'MEL22.01.1803' AND record_type = 'OF';

--> Delete VAT Out yang terlanjur masuk gunggung
DELETE FROM fi_vat_out_balance_efaktur_gunggung WHERE doc_no  = 'MEL22.01.1803';


--Update fi_invoice_ar_balance
UPDATE fi_invoice_ar_balance X SET amount = A.total_amount + COALESCE(B.gov_tax_amount, 0)
FROM sl_invoice A
LEFT OUTER JOIN sl_invoice_tax B ON A.invoice_id = B.invoice_id
WHERE EXISTS (
    SELECT 1
        FROM temp_sl_so_progress_show_data_20220218 Z
        WHERE Z.invoice_id = A.invoice_id
        AND A.ref_id = Z.so_id
    ) AND A.invoice_id = X.invoice_ar_id AND A.doc_type_id = X.doc_type_id;

--> Update sl_so_balance_invoice
UPDATE sl_so_balance_invoice A SET tax_id = 10, flg_tax_amount = 'Y', tax_percentage = 10 WHERE EXISTS (SELECT 1 FROM sl_so B WHERE A.so_id = B.so_id AND B.doc_no = 'MEL-SO22.01.0605');

--> Update sl_so_balance_invoice_tax
UPDATE sl_so_balance_invoice_tax X SET invoice_id = A.invoice_id
    FROM sl_so_balance_invoice A
    WHERE EXISTS (SELECT 1 FROM sl_so B WHERE A.so_id = B.so_id AND B.doc_no = 'MEL-SO22.01.0605')
    AND A.ref_doc_no = 'DO-MF/2201/001320'
    AND X.so_id = A.so_id
    AND X.ref_id = A.ref_id
    AND X.ref_item_id = A.ref_item_id;