upload_sell_price_product_for_so

--Adrian, Jan 11, 2017

CREATE OR REPLACE FUNCTION upload_sell_price_product_for_so(bigint)
  RETURNS integer AS
$BODY$

DECLARE
    pUlHeaderId             ALIAS FOR $1;

    vCount                  bigint;
    vStatusOk               character varying;
    vStatusFail             character varying;
    vStatusX                character varying;
    vEmpty                  character varying;
    vCurrentDateTime        character varying;
    vCurrComboId            character varying;
    vYesNoComboId           character varying;
    vPriceLevelComboId      character varying;
    vYes                    character varying;
    vRounding               integer;
    vRoundingMode           character varying;
    vSpaceValue             character varying;
    vNullValueLong          bigint;
    vTenantId               bigint;
    vVersion                bigint;

BEGIN
    vStatusOk := 'OK';
    vStatusFail := 'FAIL';
    vStatusX := 'X'; -- untuk yang fail karena validasi difungsi ini, akan ditandai X dulu untuk membedakan dengan yang validasi dari Spring batch untuk domain data
    vEmpty := '';
    vCurrComboId := 'CURRENCY';
    vYesNoComboId := 'YESNO';
    vPriceLevelComboId := 'PRICELEVEL';
    vYes := 'Y';
    vSpaceValue := ' ';
    vTenantId := 10;
    vNullValueLong := -99;
    vVersion := 1;
   -- This function returns integer  how many item fails

   -- Untuk yang tidak lolos domain data validation, field status sudah berisi FAIL
   -- Di sini melanjutkan business validation, hanya yang status belum diset sebagai 'FAIL'


    IF EXISTS(SELECT 1 FROM ul_skip_detail WHERE ul_header_id = pUlHeaderId) THEN
        RAISE EXCEPTION 'ADA ITEM YANG DI SKIP';
    END IF;

    SELECT f_datetime((extract(epoch from now())*1000)::bigint) INTO vCurrentDateTime;
    SELECT f_get_value_system_config_by_param_code(vTenantId, 'rounding.mode.non.tax') INTO vRoundingMode;
    SELECT f_get_value_system_config_by_param_code(vTenantId, 'rounding.scale.non.tax') INTO vRounding;

    --Validation currency code must be suitable with combo CURRENCY
    UPDATE ul_sell_price_product_for_so Z  SET status= vStatusX , message = Z.message||'currency not exists; '
    FROM
        ul_sell_price_product_for_so A
    INNER JOIN ul_header C ON C.ul_header_id=A.ul_header_id
    WHERE A.ul_header_id = pUlHeaderId
    AND A.status <> vStatusFail
    AND NOT EXISTS (
        SELECT TRUE
        FROM t_combo_value
        WHERE combo_id = vCurrComboId AND code = A.curr_code
    )
    AND Z.ul_sell_price_product_for_so_id = A.ul_sell_price_product_for_so_id;

    --Validation active must be suitable with combo YES NO
    UPDATE ul_sell_price_product_for_so Z  SET status= vStatusX , message = Z.message||'active type not exists; '
    FROM
        ul_sell_price_product_for_so A
    INNER JOIN ul_header C ON C.ul_header_id=A.ul_header_id
    WHERE A.ul_header_id = pUlHeaderId
    AND A.status <> vStatusFail
    AND NOT EXISTS (
        SELECT TRUE
        FROM t_combo_value
        WHERE combo_id = vYesNoComboId AND code = A.active
    )
    AND Z.ul_sell_price_product_for_so_id = A.ul_sell_price_product_for_so_id;

    --Validation flag vat amount
    UPDATE ul_sell_price_product_for_so Z  SET status= vStatusX , message = Z.message||'inc tax type not exists; '
    FROM
        ul_sell_price_product_for_so A
    INNER JOIN ul_header C ON C.ul_header_id=A.ul_header_id
    WHERE A.ul_header_id = pUlHeaderId
    AND A.status <> vStatusFail
    AND NOT EXISTS (
        SELECT TRUE
        FROM t_combo_value
        WHERE combo_id = vYesNoComboId AND code = A.flg_tax_amount
    )
    AND Z.ul_sell_price_product_for_so_id = A.ul_sell_price_product_for_so_id;

    --Validation priceLevel must be suitable with combo PRICE LEVEL
    UPDATE ul_sell_price_product_for_so Z  SET status= vStatusX , message = Z.message||'price level not exists; '
    FROM
        ul_sell_price_product_for_so A
    INNER JOIN ul_header C ON C.ul_header_id=A.ul_header_id
    WHERE A.ul_header_id = pUlHeaderId
    AND A.status <> vStatusFail
    AND NOT EXISTS (
        SELECT TRUE
        FROM t_combo_value
        WHERE combo_id = vPriceLevelComboId AND code = A.price_level
    )
    AND Z.ul_sell_price_product_for_so_id = A.ul_sell_price_product_for_so_id;

    --validasi ou_code harus ada
    UPDATE ul_sell_price_product_for_so Z  SET status= vStatusX , message = Z.message||'Org Unit not exists; '
    FROM
    ul_sell_price_product_for_so A
    INNER JOIN ul_header C ON C.ul_header_id=A.ul_header_id
    WHERE A.ul_header_id = pUlHeaderId
    AND A.status <> vStatusFail
    AND NOT EXISTS (
        SELECT (1) FROM t_ou B
        WHERE A.ou_code=B.ou_code AND C.tenant_id=B.tenant_id AND B.active=vYes)
    AND Z.ul_sell_price_product_for_so_id = A.ul_sell_price_product_for_so_id;

    --validasi product_code harus ada
    UPDATE ul_sell_price_product_for_so Z  SET status= vStatusX , message = Z.message||'Product Code not exists; '
    FROM
    ul_sell_price_product_for_so A
    INNER JOIN ul_header C ON C.ul_header_id=A.ul_header_id
    WHERE A.ul_header_id = pUlHeaderId
    AND A.status <> vStatusFail
    AND NOT EXISTS (
        SELECT (1) FROM m_product B
        WHERE A.product_code=B.product_code AND C.tenant_id=B.tenant_id AND B.active=vYes AND B.flg_sell=vYes)
    AND Z.ul_sell_price_product_for_so_id = A.ul_sell_price_product_for_so_id;

    -- Validasi date from harus format yyyymmdd
    UPDATE ul_sell_price_product_for_so Z
    SET status = vStatusX, message = Z.message || 'Period Start not in YYYYMMDD format; '
    FROM ul_sell_price_product_for_so A
    WHERE A.ul_header_id = pUlHeaderId
        AND A.ul_sell_price_product_for_so_id = Z.ul_sell_price_product_for_so_id
        AND A.status <> vStatusFail
        AND isValidDate(A.date_from) IS FALSE;

    -- Validasi date to harus format yyyymmdd
    UPDATE ul_sell_price_product_for_so Z
    SET status = vStatusX, message = Z.message || 'Period End not in YYYYMMDD format; '
    FROM ul_sell_price_product_for_so A
    WHERE A.ul_header_id = pUlHeaderId
        AND A.ul_sell_price_product_for_so_id = Z.ul_sell_price_product_for_so_id
        AND A.status <> vStatusFail
        AND isValidDate(A.date_to) IS FALSE;

    -- Validasi sell price harus angka
    UPDATE ul_sell_price_product_for_so Z
    SET status = vStatusX, message = Z.message||'Sell Price must be numeric; '
    FROM ul_sell_price_product_for_so A
    WHERE A.ul_header_id = pUlHeaderId
    AND A.ul_sell_price_product_for_so_id = Z.ul_sell_price_product_for_so_id
    AND A.status <> vStatusFail
    AND isnumeric(A.sell_price) IS FALSE;

    -- Validasi min sell price harus angka
    UPDATE ul_sell_price_product_for_so Z
    SET status = vStatusX, message = Z.message||'Min Sell Price must be numeric; '
    FROM ul_sell_price_product_for_so A
    WHERE A.ul_header_id = pUlHeaderId
    AND A.ul_sell_price_product_for_so_id = Z.ul_sell_price_product_for_so_id
    AND A.status <> vStatusFail
    AND isnumeric(A.min_sell_price) IS FALSE;

    -- Validasi tidak boleh duplikat
    WITH duplicateCode AS (
    SELECT D.tenant_id, E.ou_code, E.product_code, E.date_from, E.price_level, COUNT(E.product_code)
    FROM ul_sell_price_product_for_so E
    INNER JOIN ul_header D ON D.ul_header_id=E.ul_header_id
    WHERE E.ul_header_id = pUlHeaderId
    GROUP BY D.tenant_id, E.ou_code, E.product_code, E.date_from, E.price_level
    HAVING COUNT(E.product_code) > 1
    )
    UPDATE ul_sell_price_product_for_so Z
    SET status = vStatusX, message = Z.message||'Sell Price Product For SO is a duplicate; '
    FROM ul_sell_price_product_for_so A
    INNER JOIN ul_header B ON B.ul_header_id=A.ul_header_id
    INNER JOIN duplicateCode C
        ON B.tenant_id = C.tenant_id
        AND A.ou_code = C.ou_code
        AND A.product_code = C.product_code
        AND A.date_from = C.date_from
        AND A.price_level = C.price_level
    WHERE A.ul_header_id = pUlHeaderId
        AND A.status <> vStatusFail
        AND A.ul_sell_price_product_for_so_id = Z.ul_sell_price_product_for_so_id;


    --validasi overlap
    UPDATE ul_sell_price_product_for_so Z  SET status= vStatusX , message = Z.message||'Period Overlap; '
    FROM
    ul_sell_price_product_for_so A
    INNER JOIN ul_header C ON C.ul_header_id=A.ul_header_id
    WHERE A.ul_header_id = pUlHeaderId
    AND A.status <> vStatusFail
    AND EXISTS (
        SELECT (1)
        FROM ul_sell_price_product_for_so B
        INNER JOIN ul_header D ON D.ul_header_id=B.ul_header_id
        WHERE C.tenant_id = D.tenant_id
            AND A.ou_code = B.ou_code
            AND A.product_code = B.product_code
            AND A.date_from <= B.date_to
            AND A.date_to >= B.date_from
            AND A.ul_sell_price_product_for_so_id <> B.ul_sell_price_product_for_so_id
            AND A.price_level = B.price_level
            AND B.ul_header_id = pUlHeaderId
            )
    AND Z.ul_sell_price_product_for_so_id = A.ul_sell_price_product_for_so_id;


    -- Update semua yang vStatusX jadi xStatusFail
    UPDATE ul_sell_price_product_for_so SET status = vStatusFail
    WHERE status = vStatusX
    AND ul_header_id = pUlHeaderId;

    -- Update semua yang vEmpty jadi vStatusOk
    UPDATE ul_sell_price_product_for_so SET status = vStatusOk
    WHERE status = vEmpty
    AND ul_header_id = pUlHeaderId;

    --Hapus data lama
    DELETE FROM m_sell_price_product_for_so Z
    WHERE EXISTS (
        SELECT TRUE
            FROM ul_sell_price_product_for_so A
            INNER JOIN ul_header B
                ON B.ul_header_id=A.ul_header_id
            INNER JOIN t_ou C
                ON A.ou_code = C.ou_code AND B.tenant_id = C.tenant_id
            INNER JOIN m_product D
                ON A.product_code = D.product_code AND B.tenant_id = D.tenant_id
            WHERE A.ul_header_id=pUlHeaderId AND
                A.status=vStatusOk AND
                Z.tenant_id = B.tenant_id AND
                Z.ou_id = C.ou_id AND
                Z.product_id = D.product_id AND
                Z.date_from = A.date_from
        );

    --Insert semua data
    INSERT INTO m_sell_price_product_for_so
        (tenant_id, ou_id, product_id,
        date_from, date_to,
        curr_code,
        flg_tax_amount,
        price_level,
        min_sell_price,
        active,
        create_datetime, create_user_id, update_datetime, update_user_id, version)
    SELECT B.tenant_id, C.ou_id, D.product_id,
        A.date_from, A.date_to,
        A.curr_code,
        A.flg_tax_amount,
        A.price_level,
        CAST (A.min_sell_price AS numeric(15,2)),
        A.active,
        vCurrentDateTime, B.user_id, vCurrentDateTime, B.user_id, vVersion
    FROM ul_sell_price_product_for_so A
    INNER JOIN ul_header B
        ON B.ul_header_id=A.ul_header_id
    INNER JOIN t_ou C
        ON A.ou_code = C.ou_code AND B.tenant_id = C.tenant_id
    INNER JOIN m_product D
        ON A.product_code = D.product_code AND B.tenant_id = D.tenant_id
    WHERE A.ul_header_id=pUlHeaderId AND
         A.status=vStatusOk;

    --Update price dan tax untuk flg_tax_amount = 'Y' dan flg_tax = 'Y'
    UPDATE m_sell_price_product_for_so Z
    SET gross_sell_price = CAST(A.sell_price AS numeric(15,2)),
        tax_id = F.tax_id,
        tax_amount = ROUND(f_get_amount_before_tax(CAST(A.sell_price AS numeric(15,2)), 'Y', F.percentage, vRounding, vRoundingMode) * F.percentage/100 ,2),
        sell_price = ROUND(f_get_amount_before_tax(CAST(A.sell_price AS numeric(15,2)), 'Y', F.percentage, vRounding, vRoundingMode) ,2)
    FROM ul_sell_price_product_for_so A
    INNER JOIN ul_header B
        ON B.ul_header_id=A.ul_header_id
    INNER JOIN t_ou C
        ON A.ou_code = C.ou_code AND B.tenant_id = C.tenant_id
    INNER JOIN m_product D
        ON A.product_code = D.product_code AND B.tenant_id = D.tenant_id
    --INNER JOIN t_ou_legal E ON E.ou_id = C.ou_id
    INNER JOIN m_tax F ON F.tenant_id = B.tenant_id
    WHERE
        Z.tenant_id = B.tenant_id AND
        Z.ou_id = C.ou_id AND
        Z.product_id = D.product_id AND
        Z.date_from = A.date_from AND
        --E.flg_pkp = vYes AND
        Z.flg_tax_amount = vYes AND
        A.flg_tax = vYes AND
        A.ul_header_id = pUlHeaderId;

    --Update price dan tax untuk flg_tax_amount <> 'Y' dan flg_tax = 'Y'
    UPDATE m_sell_price_product_for_so Z
    SET gross_sell_price = CAST(A.sell_price AS numeric(15,2)),
        tax_id = F.tax_id,
        tax_amount = ROUND((CAST(A.sell_price AS numeric(15,2))) * F.percentage/100 ,2),
        sell_price = CAST(A.sell_price AS numeric(15,2)) + ROUND((CAST(A.sell_price AS numeric(15,2))) * F.percentage/100 ,2)
    FROM ul_sell_price_product_for_so A
    INNER JOIN ul_header B
        ON B.ul_header_id=A.ul_header_id
    INNER JOIN t_ou C
        ON A.ou_code = C.ou_code AND B.tenant_id = C.tenant_id
    INNER JOIN m_product D
        ON A.product_code = D.product_code AND B.tenant_id = D.tenant_id
    INNER JOIN m_tax F ON F.tenant_id = B.tenant_id
    WHERE
        Z.tenant_id = B.tenant_id AND
        Z.ou_id = C.ou_id AND
        Z.product_id = D.product_id AND
        Z.date_from = A.date_from AND
        Z.flg_tax_amount <> vYes AND
        A.flg_tax = vYes AND
        A.ul_header_id = pUlHeaderId;

    --Update price dan tax untuk flg_tax_amount <> 'Y' dan flg_tax <> 'Y'
    UPDATE m_sell_price_product_for_so Z
    SET gross_sell_price = CAST(A.sell_price AS numeric(15,2)),
        tax_id = vNullValueLong,
        tax_amount = 0,
        sell_price = CAST(A.sell_price AS numeric(15,2))
    FROM ul_sell_price_product_for_so A
    INNER JOIN ul_header B
        ON B.ul_header_id=A.ul_header_id
    INNER JOIN t_ou C
        ON A.ou_code = C.ou_code AND B.tenant_id = C.tenant_id
    INNER JOIN m_product D
        ON A.product_code = D.product_code AND B.tenant_id = D.tenant_id
    WHERE
        Z.tenant_id = B.tenant_id AND
        Z.ou_id = C.ou_id AND
        Z.product_id = D.product_id AND
        Z.date_from = A.date_from AND
        Z.flg_tax_amount <> vYes AND
        A.flg_tax <> vYes AND
        A.ul_header_id = pUlHeaderId;

    --Update activeDateTime dan nonActiveDateTime untuk active = 'Y'
    UPDATE m_sell_price_product_for_so Z
    SET active_datetime = vCurrentDateTime,
        non_active_datetime = vSpaceValue
    FROM ul_sell_price_product_for_so A
    INNER JOIN ul_header B
        ON B.ul_header_id=A.ul_header_id
    INNER JOIN t_ou C
        ON A.ou_code = C.ou_code AND B.tenant_id = C.tenant_id
    INNER JOIN m_product D
        ON A.product_code = D.product_code AND B.tenant_id = D.tenant_id
    WHERE
        Z.tenant_id = B.tenant_id AND
        Z.ou_id = C.ou_id AND
        Z.product_id = D.product_id AND
        Z.date_from = A.date_from AND
        Z.price_level = A.price_level AND
        Z.active = vYes AND
        A.ul_header_id = pUlHeaderId;

    --Update activeDateTime dan nonActiveDateTime untuk active <> 'Y'
    UPDATE m_sell_price_product_for_so Z
    SET active_datetime = vSpaceValue,
        non_active_datetime = vSpaceValue
    FROM ul_sell_price_product_for_so A
    INNER JOIN ul_header B
        ON B.ul_header_id=A.ul_header_id
    INNER JOIN t_ou C
        ON A.ou_code = C.ou_code AND B.tenant_id = C.tenant_id
    INNER JOIN m_product D
        ON A.product_code = D.product_code AND B.tenant_id = D.tenant_id
    WHERE
        Z.tenant_id = B.tenant_id AND
        Z.ou_id = C.ou_id AND
        Z.product_id = D.product_id AND
        Z.date_from = A.date_from AND
        Z.price_level = A.price_level AND
        Z.active <> vYes AND
        A.ul_header_id = pUlHeaderId;

    -- Hitung jumlah yang fail, dan kembalikan nilanya
   SELECT COUNT(ul_sell_price_product_for_so_id) INTO vCount
   FROM ul_sell_price_product_for_so
   WHERE
     ul_header_id = pUlHeaderId
     AND status = vStatusFail;

   RETURN vCount;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /