CREATE OR REPLACE FUNCTION sl_submit_mapping_invoice_to_promo_coin(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE

    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId          bigint;
    vMappingInvoiceId   bigint;
    vUserId             bigint;
    vRefNullValue       bigint := -99;
    vZeroValue          bigint := 0;
    vNolValue           numeric := 0;
    vDatetime           character varying(14);
    vYes                character varying(1) := 'Y';
    vStatusRelease      character varying(1) := 'R';
    vFlgPromoCoin       character varying(1) := 'P';
    vFlgSubPromoCoin    character varying(1) := 'S';
    vFlgBonusCoin       character varying(1) := 'L';
    vEmptyValue         character varying := '';
    vDocTypeMappingInvoiceToPromoCoin       bigint := 374;

BEGIN

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'sl_submit_mapping_invoice_to_promo_coin' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vMappingInvoiceId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'mappingInvoiceId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    -- Mengurangi saldo koin di balance yang sebelum nya di tambah saat membuat data penjualan dengan
    -- informasi promo, sub promo, dan launching lama
    WITH data_for_update_balance_coin AS (
        SELECT A.tenant_id, A.partner_id, A.promo_id, -1*SUM(A.coin_promo) AS coin_promo, -1*SUM(A.coin_sub_promo) AS coin_sub_promo, -1*SUM(A.coin_launching) AS coin_launching
        FROM sl_log_coin_promo_balance A
        INNER JOIN sl_mapping_invoice_promo_coin_item B ON A.so_id = B.so_id
        WHERE B.mapping_invoice_promo_coin_id = vMappingInvoiceId
        GROUP BY A.tenant_id, A.partner_id, A.promo_id
    )
    UPDATE sl_coin_promo_balance A SET
        coin_promo = A.coin_promo + B.coin_promo ,
        coin_sub_promo = A.coin_sub_promo + B.coin_sub_promo ,
        coin_launching = A.coin_launching + B.coin_launching ,
        coin_total = (A.coin_promo + B.coin_promo) + (A.coin_sub_promo + B.coin_sub_promo) + (A.coin_launching + B.coin_launching) + coin_adjustment + coin_periodic_adjustment,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = version + 1
    FROM data_for_update_balance_coin B
    WHERE A.tenant_id = B.tenant_id
    AND A.partner_id = B.partner_id
    AND A.promo_id = B.promo_id;

    -- Membuat log saldo koin pengurangan koin di balance yang sebelum nya di tambah saat membuat data penjualan dengan
    -- informasi promo, sub promo, dan launching lama
    INSERT INTO sl_log_coin_promo_balance(
            tenant_id, partner_id, so_id, ref_id,
            ref_doc_type_id, promo_id, flg_promo_coin, sub_promo_id, flg_sub_promo_coin,
            product_launching_id, flg_launching_coin, brand_id, group_brand,
            salesman_id, qty, coin_promo, coin_sub_promo, coin_launching,
            coin_adjustment, coin_periodic_adjustment, create_datetime, create_user_id,
            update_datetime, update_user_id, version)
    SELECT A.tenant_id, A.partner_id, A.so_id, vMappingInvoiceId,
            vDocTypeMappingInvoiceToPromoCoin, B.promo_id, B.flg_promo_coin, B.sub_promo_id, B.flg_sub_promo_coin,
            B.product_launching_id, B.flg_launching_coin, vRefNullValue, vEmptyValue,
            A.salesman_id, vNolValue, -1*SUM(A.coin_promo) AS coin_promo, -1*SUM(A.coin_sub_promo) AS coin_sub_promo, -1*SUM(A.coin_launching) AS coin_launching,
            vNolValue, vNolValue, vDatetime, vUserId,
            vDatetime, vUserId, vZeroValue
    FROM sl_log_coin_promo_balance A
    INNER JOIN sl_mapping_invoice_promo_coin_item B ON A.so_id = B.so_id
    WHERE B.mapping_invoice_promo_coin_id = vMappingInvoiceId
    GROUP BY A.tenant_id, A.partner_id, A.so_id, B.promo_id, B.flg_promo_coin,
    B.sub_promo_id, B.flg_sub_promo_coin, B.product_launching_id, B.flg_launching_coin, A.salesman_id;


    -- Menambah saldo koin dengan promo, sub promo, dan launching baru.
    /**
     * Selalu mendapat koin dari promo jika :
     * 1. jika memilih launching => launching coin L dan promo coin Y atau launching coin P
     * 2. jika memilih sub promo => sub promo coin S dan promo coin Y atau sub promo coin P
     * 3. jika tidak memilih launching dan sub promo => promo coin Y
     *
     * Hanya mendapat koin dari sub promo jika :
     * 1. jika memilih sub promo => sub promo coin S, dan promo coin N
     *
     * Hanya mendapat koin dari Launching jika :
     * 1. jika memilih launching => launching coin L, dan promo coin N
     *
     */
    WITH data_so_with_qty_brand AS (
        SELECT A.mapping_invoice_promo_coin_id, A.so_id,
        f_get_brand_by_product_id(B.product_id) brand_id, SUM(C.qty_dlv_int - C.qty_return_int - C.qty_cancel_int + C.qty_add_int) qty_brand
        FROM sl_mapping_invoice_promo_coin_item A
        INNER JOIN sl_so_item B ON A.so_id = B.so_id
        INNER JOIN sl_so_balance_item C ON B.so_item_id = C.so_item_id
        WHERE A.mapping_invoice_promo_coin_id = vMappingInvoiceId
        GROUP BY A.mapping_invoice_promo_coin_id, A.so_id, brand_id
    ), data_for_update_balance_coin AS (
        SELECT B.tenant_id, A.partner_id, B.promo_id,
        SUM(CASE WHEN (B.product_launching_id != vRefNullValue AND B.flg_launching_coin =vFlgBonusCoin AND B.flg_promo_coin = vYes) THEN C.qty_brand * D.coin
            WHEN (B.product_launching_id != vRefNullValue AND B.flg_launching_coin =vFlgPromoCoin) THEN C.qty_brand * D.coin
            WHEN (B.sub_promo_id != vRefNullValue AND B.flg_sub_promo_coin =vFlgSubPromoCoin AND B.flg_promo_coin = vYes) THEN C.qty_brand * D.coin
            WHEN (B.sub_promo_id != vRefNullValue AND B.flg_sub_promo_coin =vFlgPromoCoin) THEN C.qty_brand * D.coin
            WHEN (B.product_launching_id = vRefNullValue AND B.sub_promo_id = vRefNullValue AND B.flg_promo_coin = vYes) THEN C.qty_brand * D.coin
            ELSE 0
        END)AS coin_promo,
        SUM(CASE WHEN (B.sub_promo_id != vRefNullValue AND B.flg_sub_promo_coin =vFlgSubPromoCoin) THEN C.qty_brand * E.coin
            ELSE 0
        END) AS coin_sub_promo,
        SUM(CASE WHEN (B.product_launching_id != vRefNullValue AND B.flg_launching_coin =vFlgPromoCoin) THEN C.qty_brand * F.coin
            ELSE 0
        END) AS coin_launching
        FROM sl_mapping_invoice_promo_coin_item A
        INNER JOIN sl_mapping_invoice_promo_coin B ON A.mapping_invoice_promo_coin_id = B.mapping_invoice_promo_coin_id
        INNER JOIN sl_so BX ON A.so_id = BX.so_id
        INNER JOIN data_so_with_qty_brand C ON A.mapping_invoice_promo_coin_id = C.mapping_invoice_promo_coin_id AND A.so_id = C.so_id
        INNER JOIN m_brand_ext CX ON C.brand_id = CX.brand_id
        INNER JOIN m_promo_item D ON B.promo_id = D.promo_id AND C.brand_id = D.brand_id
        LEFT JOIN m_sub_promo_item E ON B.sub_promo_id = E.sub_promo_id AND C.brand_id = E.brand_id
        LEFT JOIN m_promo_item F ON B.product_launching_id = D.promo_id AND C.brand_id = F.brand_id
        GROUP BY B.tenant_id, A.partner_id, B.promo_id
    )
    UPDATE sl_coin_promo_balance A SET
        coin_promo = A.coin_promo + B.coin_promo ,
        coin_sub_promo = A.coin_sub_promo + B.coin_sub_promo ,
        coin_launching = A.coin_launching + B.coin_launching ,
        coin_total = (A.coin_promo + B.coin_promo) + (A.coin_sub_promo + B.coin_sub_promo) + (A.coin_launching + B.coin_launching) + coin_adjustment + coin_periodic_adjustment,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = version + 1
    FROM data_for_update_balance_coin B
    WHERE A.tenant_id = B.tenant_id
    AND A.partner_id = B.partner_id
    AND A.promo_id = B.promo_id;

    -- Membuat log saldo koin dengan promo, sub promo, dan launching baru.
    WITH data_so_with_qty_brand AS (
        SELECT A.mapping_invoice_promo_coin_id, A.so_id,
            f_get_brand_by_product_id(B.product_id) brand_id, SUM(C.qty_dlv_int - C.qty_return_int - C.qty_cancel_int + C.qty_add_int) qty_brand
        FROM sl_mapping_invoice_promo_coin_item A
        INNER JOIN sl_so_item B ON A.so_id = B.so_id
        INNER JOIN sl_so_balance_item C ON B.so_item_id = C.so_item_id
        WHERE A.mapping_invoice_promo_coin_id = vMappingInvoiceId
        GROUP BY A.mapping_invoice_promo_coin_id, A.so_id, brand_id
    )
    INSERT INTO sl_log_coin_promo_balance(
        tenant_id, partner_id, so_id, ref_id,
        ref_doc_type_id, promo_id, flg_promo_coin, sub_promo_id, flg_sub_promo_coin,
        product_launching_id, flg_launching_coin, brand_id, group_brand,
        salesman_id, qty, coin_promo, coin_sub_promo, coin_launching,
        coin_adjustment, coin_periodic_adjustment, create_datetime, create_user_id,
        update_datetime, update_user_id, version)

    SELECT B.tenant_id, A.partner_id, A.so_id, vMappingInvoiceId,
            vDocTypeMappingInvoiceToPromoCoin, B.promo_id, B.flg_promo_coin, B.sub_promo_id, B.flg_sub_promo_coin,
            B.product_launching_id, B.flg_launching_coin, C.brand_id, CX.group_brand,
            BX.salesman_id, C.qty_brand,
            CASE WHEN (B.product_launching_id != vRefNullValue AND B.flg_launching_coin =vFlgBonusCoin AND B.flg_promo_coin = vYes) THEN C.qty_brand * D.coin
                WHEN (B.product_launching_id != vRefNullValue AND B.flg_launching_coin =vFlgPromoCoin) THEN C.qty_brand * D.coin
                WHEN (B.sub_promo_id != vRefNullValue AND B.flg_sub_promo_coin =vFlgSubPromoCoin AND B.flg_promo_coin = vYes) THEN C.qty_brand * D.coin
                WHEN (B.sub_promo_id != vRefNullValue AND B.flg_sub_promo_coin =vFlgPromoCoin) THEN C.qty_brand * D.coin
                WHEN (B.product_launching_id = vRefNullValue AND B.sub_promo_id = vRefNullValue AND B.flg_promo_coin = vYes) THEN C.qty_brand * D.coin
                ELSE 0
            END AS coin_promo,
            CASE WHEN (B.sub_promo_id != vRefNullValue AND B.flg_sub_promo_coin =vFlgSubPromoCoin) THEN C.qty_brand * E.coin
                ELSE 0
            END AS coin_sub_promo,
            CASE WHEN (B.product_launching_id != vRefNullValue AND B.flg_launching_coin =vFlgPromoCoin) THEN C.qty_brand * F.coin
                ELSE 0
            END AS coin_launching,
            vNolValue, vNolValue, vDatetime, vUserId,
            vDatetime, vUserId, vZeroValue
    FROM sl_mapping_invoice_promo_coin_item A
    INNER JOIN sl_mapping_invoice_promo_coin B ON A.mapping_invoice_promo_coin_id = B.mapping_invoice_promo_coin_id
    INNER JOIN sl_so BX ON A.so_id = BX.so_id
    INNER JOIN data_so_with_qty_brand C ON A.mapping_invoice_promo_coin_id = C.mapping_invoice_promo_coin_id AND A.so_id = C.so_id
    INNER JOIN m_brand_ext CX ON C.brand_id = CX.brand_id
    INNER JOIN m_promo_item D ON B.promo_id = D.promo_id AND C.brand_id = D.brand_id
    LEFT JOIN m_sub_promo_item E ON B.sub_promo_id = E.sub_promo_id AND C.brand_id = E.brand_id
    LEFT JOIN m_promo_item F ON B.product_launching_id = D.promo_id AND C.brand_id = F.brand_id;

    -- Update status item balance menjadi R, sebelumnya di ubah menjadi M saat add dokumen
    -- Update juga informasi promo, sub promo dan launching
    UPDATE sl_invoice_balance_promo_coin A SET
        promo_id = B.promo_id,
        flg_promo_coin = B.flg_promo_coin,
        sub_promo_id = B.sub_promo_id,
        flg_sub_promo_coin = B.flg_sub_promo_coin,
        product_launching_id = B.product_launching_id,
        flg_launching_coin = B.flg_launching_coin,
        status_item = vStatusRelease,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = A.version + 1
    FROM sl_mapping_invoice_promo_coin B
    INNER JOIN sl_mapping_invoice_promo_coin_item C ON B.mapping_invoice_promo_coin_id = C.mapping_invoice_promo_coin_id
    WHERE A.invoice_id = C.ref_id
    AND A.invoice_doc_type_id = C.ref_doc_type_id
    AND B.mapping_invoice_promo_coin_id = vMappingInvoiceId;

    -- Update sl_so_info
    UPDATE sl_so_info A SET
        promo_id = B.promo_id,
        flg_promo_coin = B.flg_promo_coin,
        sub_promo_id = B.sub_promo_id,
        flg_sub_promo_coin = B.flg_sub_promo_coin,
        product_launching_id = B.product_launching_id,
        flg_launching_coin = B.flg_launching_coin,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = A.version + 1
    FROM sl_mapping_invoice_promo_coin B
    INNER JOIN sl_mapping_invoice_promo_coin_item C ON B.mapping_invoice_promo_coin_id = C.mapping_invoice_promo_coin_id
    WHERE A.so_id = C.so_id
    AND B.mapping_invoice_promo_coin_id = vMappingInvoiceId;

    -- Update status doc dokumen menjadi R
    UPDATE sl_mapping_invoice_promo_coin A SET
        status_doc = vStatusRelease,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = A.version + 1
    WHERE A.mapping_invoice_promo_coin_id = vMappingInvoiceId;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
/