SCRIPT UNTUK TAGGING RSI KE MOU DARI SO NON MOU

/** SCRIPT UNTUK TAGGING RSI KE MOU DARI SO NON MOU **/

BEGIN;

WITH summary AS (
    SELECT E.ref_id AS so_id, 3247 AS mou_id,
        SUM(A.item_amount-A.regular_disc_amount)+SUM(COALESCE(C.tax_amount,0)) AS amount_and_tax
    FROM sl_so_balance_invoice A
    INNER JOIN in_inventory D ON A.ref_id = D.inventory_id AND A.ref_doc_type_id = D.doc_type_id
    INNER JOIN sl_request_return_sales E ON D.ref_id = E.request_return_sales_id AND D.ref_doc_type_id = E.doc_type_id
    LEFT JOIN sl_so_balance_invoice_tax C
        ON A.partner_id = C.partner_id
        AND A.ref_doc_type_id = C.ref_doc_type_id
        AND A.ref_id = C.ref_id
        AND A.ref_item_id = C.ref_item_id
        AND A.do_receipt_item_id = C.do_receipt_item_id
    WHERE A.tenant_id = 10
        AND A.ref_id = 1815
        AND A.ref_doc_type_id = 502
    GROUP BY E.ref_id
)
UPDATE sl_mou_balance D
SET usage_balance = D.usage_balance - E.amount_and_tax,
    version = D.version + 1,
    update_datetime = TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'),
    update_user_id = -1
FROM summary E
WHERE D.mou_id = E.mou_id;

--insert log untuk amount nya
WITH summary AS (
    SELECT A.tenant_id, A.ou_from_id, 3247 AS mou_id, A.doc_type_id,
        A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code,
        SUM(B.item_amount-B.regular_disc_amount) AS amount
    FROM in_inventory A
        INNER JOIN sl_request_return_sales D ON A.ref_id = D.request_return_sales_id AND A.ref_doc_type_id = D.doc_type_id
        INNER JOIN sl_so_balance_invoice B ON A.inventory_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
    WHERE A.tenant_id = 10
         AND A.inventory_id = 1815
         AND A.doc_type_id = 502
    GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
        A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code
)
INSERT INTO sl_mou_balance_log (
    tenant_id, ou_id, mou_id, ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
    partner_id, curr_code, amount, remark,
    create_datetime, create_user_id, update_datetime, update_user_id, version)
SELECT D.tenant_id, D.ou_from_id, D.mou_id, D.doc_type_id, D.inventory_id, D.doc_no, D.doc_date,
    D.partner_id, D.curr_code,  D.amount , 'Return Balance MOU from Return-Note',
    TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, 0
FROM summary D;

--insert log untuk amount - tax nya
WITH summary AS (
    SELECT A.tenant_id, A.ou_from_id, 3247 AS mou_id, A.doc_type_id,
        A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code,
        SUM(B.tax_amount) AS tax_amount
    FROM in_inventory A
        INNER JOIN sl_request_return_sales D ON A.ref_id = D.request_return_sales_id AND A.ref_doc_type_id = D.doc_type_id
        INNER JOIN sl_so_balance_invoice_tax B ON A.inventory_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
    WHERE A.tenant_id = 10
         AND A.inventory_id = 1815
         AND A.doc_type_id = 502
    GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
        A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code
)
INSERT INTO sl_mou_balance_log (
    tenant_id, ou_id, mou_id, ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
    partner_id, curr_code, amount, remark,
    create_datetime, create_user_id, update_datetime, update_user_id, version)
SELECT D.tenant_id, D.ou_from_id, D.mou_id, D.doc_type_id, D.inventory_id, D.doc_no, D.doc_date,
    D.partner_id, D.curr_code,  D.tax_amount , 'Return Balance MOU from Return-Note - Tax',
    TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, 0
FROM summary D;

-- Insert ke table in_return_tagging
INSERT INTO in_return_tagging (
tenant_id, ou_id, inventory_id, tag_key, tag_doc_id, tag_doc_no, remark,
create_datetime, create_user_id, update_datetime, update_user_id, version)
SELECT A.tenant_id, A.ou_from_id, 1815, 'MOU', 3247, '056/SK/KM/IV/2018 ', 'Tag To MOU (Default by SO)',
    TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, 0
FROM in_inventory A
INNER JOIN sl_request_return_sales C
    ON A.ref_id = C.request_return_sales_id
    AND A.ref_doc_type_id = C.doc_type_id
WHERE A.inventory_id = 1815;

-- Insert ke invoice tagging
INSERT INTO sl_invoice_tagging
        (tenant_id, ou_id, invoice_id, tag_key, tag_doc_id, tag_doc_no, remark,
        create_datetime, create_user_id, update_datetime, update_user_id, version)
SELECT 10, 10, 138485, 'MOU', 3247, '056/SK/KM/IV/2018 ', '',
        TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, 0;
-- Insert ke invoice tagging log
INSERT INTO sl_invoice_tagging_log
    (tenant_id, ou_id, invoice_id, tag_key, tag_doc_id,
    tag_doc_no, action_mapping, remark,
    create_datetime, create_user_id, update_datetime, update_user_id, version)
SELECT 10, 10,  138485, 'MOU', 3247,
    '056/SK/KM/IV/2018 ', 'TAG', '',
    TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), -1, 0;

END;