script update tax no

-- A. Sehubungan 5 tax_no yg di email
    -- 1. Cek apakah dari daftar tax no yg di email sudah dipakai di invoice
    SELECT * FROM m_gen_tax_number
    WHERE tax_no IN ('017-17.89289593', '017-17.89289592', '017-17.89289594', '017-17.89289591', '017-17.89289595')

    -- 2. update m_gen_tax_number dengan info dari invoice yg terkait
    --    a. '017-17.89289593': 19589/17/AI-CL/YM/KM
          SELECT * FROM sl_invoice WHERE doc_no = '19589/17/AI-CL/YM/KM' AND doc_type_id = 321;

          UPDATE m_gen_tax_number  A SET
            invoice_id = B.invoice_id,
            invoice_doc_type_id = B.doc_type_id,
            invoice_doc_no = B.doc_no,
            invoice_doc_date = B.doc_date,
            update_user_id = -3,
            update_datetime = '20170812152500',
            version = A.version + 1
          FROM sl_invoice B
          WHERE A.tenant_id = 10
            AND A.tax_no = '017-17.89289593'
            AND B.doc_no = '19589/17/AI-CL/YM/KM'
            AND B.doc_type_id = 321;

    --    b. '017-17.89289592': 19647/17/AI-CL/YM/KM
          SELECT * FROM sl_invoice WHERE doc_no = '19647/17/AI-CL/YM/KM' AND doc_type_id = 321;

          UPDATE m_gen_tax_number  A SET
            invoice_id = B.invoice_id,
            invoice_doc_type_id = B.doc_type_id,
            invoice_doc_no = B.doc_no,
            invoice_doc_date = B.doc_date,
            update_user_id = -3,
            update_datetime = '20170812152500',
            version = A.version + 1
          FROM sl_invoice B
          WHERE A.tenant_id = 10
            AND A.tax_no = '017-17.89289592'
            AND B.doc_no = '19647/17/AI-CL/YM/KM'
            AND B.doc_type_id = 321;

    --    c. '017-17.89289594': 19686/17/AI-CL/YM/KM
          SELECT * FROM sl_invoice WHERE doc_no = '19686/17/AI-CL/YM/KM' AND doc_type_id = 321;

          UPDATE m_gen_tax_number  A SET
            invoice_id = B.invoice_id,
            invoice_doc_type_id = B.doc_type_id,
            invoice_doc_no = B.doc_no,
            invoice_doc_date = B.doc_date,
            update_user_id = -3,
            update_datetime = '20170812152500',
            version = A.version + 1
          FROM sl_invoice B
          WHERE A.tenant_id = 10
            AND A.tax_no = '017-17.89289594'
            AND B.doc_no = '19686/17/AI-CL/YM/KM'
            AND B.doc_type_id = 321;

    --    d. '017-17.89289591': 21456/17/AI-CL/IL/KM
          SELECT * FROM sl_invoice WHERE doc_no = '21456/17/AI-CL/IL/KM' AND doc_type_id = 321;

          UPDATE m_gen_tax_number  A SET
            invoice_id = B.invoice_id,
            invoice_doc_type_id = B.doc_type_id,
            invoice_doc_no = B.doc_no,
            invoice_doc_date = B.doc_date,
            update_user_id = -3,
            update_datetime = '20170812152500',
            version = A.version + 1
          FROM sl_invoice B
          WHERE A.tenant_id = 10
            AND A.tax_no = '017-17.89289591'
            AND B.doc_no = '21456/17/AI-CL/IL/KM'
            AND B.doc_type_id = 321;

    --    e. '017-17.89289595': 21446/17/AI-CL/IL/KM
          SELECT * FROM sl_invoice WHERE doc_no = '21446/17/AI-CL/IL/KM' AND doc_type_id = 321;

          UPDATE m_gen_tax_number  A SET
            invoice_id = B.invoice_id,
            invoice_doc_type_id = B.doc_type_id,
            invoice_doc_no = B.doc_no,
            invoice_doc_date = B.doc_date,
            update_user_id = -3,
            update_datetime = '20170812152500',
            version = A.version + 1
          FROM sl_invoice B
          WHERE A.tenant_id = 10
            AND A.tax_no = '017-17.89289595'
            AND B.doc_no = '21446/17/AI-CL/IL/KM'
            AND B.doc_type_id = 321;

    -- 3. update m_gen_tax_number dengan info dari invoice yg terkait
        UPDATE sl_invoice_tax A SET
            tax_no = '010.'||C.tax_no,
            update_user_id = -3,
            update_datetime = '20170812152500',
            version = A.version + 1
        FROM sl_invoice B, m_gen_tax_number C
        WHERE A.invoice_id = B.invoice_id
            AND B.doc_type_id = 321
            AND B.tenant_id = C.tenant_id
            AND B.invoice_id = C.invoice_id
            AND B.doc_type_id = C.invoice_doc_type_id
            AND C.tax_no IN ('017-17.89289593', '017-17.89289592', '017-17.89289594', '017-17.89289591', '017-17.89289595');


-----------------------------------------------------------------------------------------------------------------------------------------
-- B. Sehubungan 6 partner yg di issue KAT-168
    -- 1. Get data invoice 6 partner tsb
        SELECT f_get_partner_code(partner_id) AS partner_code, f_get_partner_name(partner_id) AS partner_name,
            A.invoice_id, A.invoice_tax_id, A.tax_no, A.tax_date,
            ROW_NUMBER() OVER (ORDER BY f_get_partner_code(partner_id), A.tax_date) AS nomor_data_invoice, A.*
        FROM sl_invoice_tax A
        INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
        WHERE B.doc_type_id = 321
            AND B.doc_date BETWEEN '20170701' and '20170731'
            AND B.partner_id IN (300646,300244,302212,300200,300184,302199)
        ORDER BY partner_code, A.tax_date;

    -- 2. Update data m_gen_tax_number , untuk booked invoice-invoice yg ingin diupdate

        WITH data_gen_tax_number AS (
            -- get data tax_no yg belum dipakai
            SELECT ROW_NUMBER() OVER (ORDER BY tax_no) AS nomor_tax_number,
                tenant_id, gen_tax_number_id, tax_no
            FROM m_gen_tax_number
            WHERE invoice_id = -99
                AND year::text = '2017'
                AND tenant_id = 10
            ORDER BY tax_no
        ), data_invoice AS (
            -- get data invoice dari partner-partner yg dilaporkan untuk bulan juli 2017
            SELECT f_get_partner_code(partner_id) AS partner_code, f_get_partner_name(partner_id) AS partner_name,
                A.tenant_id, A.invoice_id, B.doc_type_id, B.doc_no, B.doc_date,
                A.invoice_tax_id, A.tax_no, A.tax_date,
                ROW_NUMBER() OVER (ORDER BY f_get_partner_code(partner_id), A.tax_date) AS nomor_data_invoice
            FROM sl_invoice_tax A
            INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
            WHERE B.doc_type_id = 321
                AND B.doc_date BETWEEN '20170701' and '20170731'
                AND B.partner_id IN (300646,300244,302212,300200,300184,302199)
            ORDER BY partner_code, A.tax_date
        )
            -- update m_gen_tax_number
            UPDATE m_gen_tax_number A SET
                invoice_id = C.invoice_id,
                invoice_doc_type_id = C.doc_type_id,
                invoice_doc_no = C.doc_no,
                invoice_doc_date = C.doc_date,
                update_user_id = -3,
                update_datetime = '20170812152500',
                version = A.version + 1
            FROM data_gen_tax_number B, data_invoice C
            WHERE A.gen_tax_number_id = B.gen_tax_number_id
                AND B.tenant_id = C.tenant_id
                AND B.nomor_tax_number = C.nomor_data_invoice;

    -- 3. Update tax_no di sl_invoice_tax berdasarkan tax_no yg sudah di booking sebelumnya di m_gen_tax_number
        UPDATE sl_invoice_tax A SET
            tax_no = '010.'||C.tax_no,
            update_user_id = -3,
            update_datetime = '20170812152500',
            version = A.version + 1
        FROM sl_invoice B, m_gen_tax_number C
        WHERE A.invoice_id = B.invoice_id
            AND B.doc_type_id = 321
            AND B.tenant_id = C.tenant_id
            AND B.invoice_id = C.invoice_id
            AND B.doc_type_id = C.invoice_doc_type_id
            AND B.partner_id IN (300646,300244,302212,300200,300184,302199)
            AND B.doc_date BETWEEN '20170701' and '20170731'
            AND C.update_user_id = -3
            AND C.update_datetime = '20170812152500';