upload_warranty_type_for_sell_price_so

--Adrian, Jan 12, 2017
/**
 * Modified by Adrian, Sep 18, 2017
 * Hapus data yang sell_price_product_for_so_id-nya tidak ditemukan pada m_sell_price_product_for_so
 * sell_price_date diubah menjadi harus di antara date_to dan date_from dari m_sell_price_product_for_so
 */

CREATE OR REPLACE FUNCTION upload_warranty_type_for_sell_price_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;
    vCurrCode               character varying;
    vPriceLevelComboId      character varying;
    vYes                    character varying;
    vSpaceValue             character varying;
    vAmount                 character varying;
    vPercentage             character varying;
    vAmt                    character varying;
    vPct                    character varying;
    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 := '';
    vCurrCode := 'IDR';
    vPriceLevelComboId := 'PRICELEVEL';
    vYes := 'Y';
    vSpaceValue := ' ';
    vAmount := 'Amount';
    vPercentage := 'Percentage';
    vAmt := 'AMT';
    vPct := 'PCT';
    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;

    --validasi ou_code harus ada
    UPDATE ul_warranty_type_for_sell_price_so Z  SET status= vStatusX , message = Z.message||'OU BU or Branch not exists; '
    FROM
    ul_warranty_type_for_sell_price_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 Z.ul_warranty_type_for_sell_price_so_id = A.ul_warranty_type_for_sell_price_so_id;

    --validasi product_code harus ada
    UPDATE ul_warranty_type_for_sell_price_so Z  SET status= vStatusX , message = Z.message||'Product Code not exists; '
    FROM
    ul_warranty_type_for_sell_price_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 Z.ul_warranty_type_for_sell_price_so_id = A.ul_warranty_type_for_sell_price_so_id;

    --Validation priceLevel must be suitable with combo PRICE LEVEL
    UPDATE ul_warranty_type_for_sell_price_so Z  SET status= vStatusX , message = Z.message||'Price Level not exists; '
    FROM
        ul_warranty_type_for_sell_price_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 prop_key = A.price_level
    )
    AND Z.ul_warranty_type_for_sell_price_so_id = A.ul_warranty_type_for_sell_price_so_id;

    --validasi warranty_type_code harus ada
    UPDATE ul_warranty_type_for_sell_price_so Z  SET status= vStatusX , message = Z.message||'Warranty Type Code not exists; '
    FROM
    ul_warranty_type_for_sell_price_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_warranty_type B
        WHERE A.warranty_type_code=B.warranty_type_code AND C.tenant_id=B.tenant_id)
    AND Z.ul_warranty_type_for_sell_price_so_id = A.ul_warranty_type_for_sell_price_so_id;

    --validasi warranty_sell_price_type harus 'Amount' atau 'Percentage'
    UPDATE ul_warranty_type_for_sell_price_so Z  SET status= vStatusX , message = Z.message||'Warranty Sell Price Type must be either Amount or Percentage; '
    FROM
    ul_warranty_type_for_sell_price_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 ((A.warranty_sell_price_type <> vAmount) AND (A.warranty_sell_price_type <> vPercentage))
    AND Z.ul_warranty_type_for_sell_price_so_id = A.ul_warranty_type_for_sell_price_so_id;

    -- Validasi warranty_sell_price harus angka
    UPDATE ul_warranty_type_for_sell_price_so Z
    SET status = vStatusX, message = Z.message||'Warranty Sell Price must be numeric; '
    FROM ul_warranty_type_for_sell_price_so A
    WHERE A.ul_header_id = pUlHeaderId
    AND A.ul_warranty_type_for_sell_price_so_id = Z.ul_warranty_type_for_sell_price_so_id
    AND A.status <> vStatusFail
    AND isnumeric(A.warranty_sell_price) IS FALSE;

    --validasi warranty_sell_price harus >= 0
    UPDATE ul_warranty_type_for_sell_price_so Z  SET status= vStatusX , message = Z.message||'Minimum Warranty Sell Price is 0; '
    FROM
    ul_warranty_type_for_sell_price_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 (CAST(A.warranty_sell_price AS numeric(15,2)) < 0)
    AND Z.ul_warranty_type_for_sell_price_so_id = A.ul_warranty_type_for_sell_price_so_id;

    --validasi sell_price_product_for_so harus ada
    UPDATE ul_warranty_type_for_sell_price_so Z  SET status= vStatusX , message = Z.message||'Sell Price Product For SO not exists; '
    FROM
    ul_warranty_type_for_sell_price_so A
    INNER JOIN ul_header C ON C.ul_header_id=A.ul_header_id
    INNER JOIN t_ou D
        ON A.ou_code = D.ou_code AND C.tenant_id = C.tenant_id
    INNER JOIN m_product E
        ON A.product_code = E.product_code AND C.tenant_id = E.tenant_id
    INNER JOIN t_combo_value F
        ON F.combo_id = vPriceLevelComboId AND F.prop_key = A.price_level
    WHERE A.ul_header_id = pUlHeaderId
    AND A.status <> vStatusFail
    AND NOT EXISTS (
        SELECT (1) FROM m_sell_price_product_for_so B
        WHERE C.tenant_id=B.tenant_id
            AND D.ou_id=B.ou_id
            AND E.product_id=B.product_id
            AND A.sell_price_date > B.date_from
            AND A.sell_price_date < B.date_to
            AND F.code = B.price_level
        )
    AND Z.ul_warranty_type_for_sell_price_so_id = A.ul_warranty_type_for_sell_price_so_id;

    -- Validasi tidak boleh duplikat
    WITH duplicateCode AS (
        SELECT D.tenant_id, E.ou_code, E.product_code, E.sell_price_date, E.price_level, E.warranty_type_code, COUNT(E.product_code)
        FROM ul_warranty_type_for_sell_price_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.sell_price_date, E.price_level, E.warranty_type_code
        HAVING COUNT(E.product_code) > 1
    )
    UPDATE ul_warranty_type_for_sell_price_so Z
    SET status = vStatusX, message = Z.message||'Warranty Type For Sell Price SO is a duplicate; '
    FROM ul_warranty_type_for_sell_price_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.sell_price_date = C.sell_price_date
        AND A.price_level = C.price_level
        AND A.warranty_type_code = C.warranty_type_code
    WHERE A.ul_header_id = pUlHeaderId
        AND A.status <> vStatusFail
        AND A.ul_warranty_type_for_sell_price_so_id = Z.ul_warranty_type_for_sell_price_so_id;


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

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

    --Hapus data yang sell_price_product_for_so_id-nya tidak ditemukan pada m_sell_price_product_for_so
    DELETE FROM m_warranty_type_for_sell_price_so Z
    WHERE Z.sell_price_product_for_so_id NOT IN (
        SELECT sell_price_product_for_so_id
            FROM m_sell_price_product_for_so
        );

    --Hapus data lama
    DELETE FROM m_warranty_type_for_sell_price_so Z
    WHERE EXISTS (
        SELECT TRUE
            FROM ul_warranty_type_for_sell_price_so A
            INNER JOIN ul_header B
                ON B.ul_header_id=A.ul_header_id
            INNER JOIN t_ou D
                ON A.ou_code = D.ou_code AND B.tenant_id = D.tenant_id
            INNER JOIN m_product E
                ON A.product_code = E.product_code AND B.tenant_id = E.tenant_id
            INNER JOIN t_combo_value F
                ON F.combo_id = vPriceLevelComboId AND F.prop_key = A.price_level
            INNER JOIN m_sell_price_product_for_so G
                ON B.tenant_id=G.tenant_id
                AND D.ou_id=G.ou_id
                AND E.product_id=G.product_id
                AND A.sell_price_date > G.date_from
                AND A.sell_price_date < G.date_to
                AND F.code = G.price_level
            INNER JOIN m_warranty_type H
                ON A.warranty_type_code=H.warranty_type_code AND H.tenant_id=B.tenant_id
            WHERE A.ul_header_id=pUlHeaderId AND
                A.status=vStatusOk AND
                Z.tenant_id = B.tenant_id AND
                Z.sell_price_product_for_so_id = G.sell_price_product_for_so_id AND
                Z.warranty_type_id = H.warranty_type_id
        );

    --Insert semua data
    INSERT INTO m_warranty_type_for_sell_price_so
        (tenant_id,
        sell_price_product_for_so_id,
        warranty_type_id,
        warranty_sell_price_type,
        warranty_sell_price,
        active, active_datetime, non_active_datetime,
        create_datetime, create_user_id, update_datetime, update_user_id, version)
    SELECT B.tenant_id,
        G.sell_price_product_for_so_id,
        H.warranty_type_id,
        CASE
            WHEN (A.warranty_sell_price_type = vAmount) THEN vAmt
            WHEN (A.warranty_sell_price_type = vPercentage) THEN vPct
        END,
        CAST(A.warranty_sell_price AS numeric(15,2)),
        vYes, vCurrentDateTime, vSpaceValue,
        vCurrentDateTime, B.user_id, vCurrentDateTime, B.user_id, vVersion
    FROM ul_warranty_type_for_sell_price_so A
    INNER JOIN ul_header B
        ON B.ul_header_id=A.ul_header_id
    INNER JOIN t_ou D
        ON A.ou_code = D.ou_code AND B.tenant_id = D.tenant_id
    INNER JOIN m_product E
        ON A.product_code = E.product_code AND B.tenant_id = E.tenant_id
    INNER JOIN t_combo_value F
        ON F.combo_id = vPriceLevelComboId AND F.prop_key = A.price_level
    INNER JOIN m_sell_price_product_for_so G
        ON B.tenant_id=G.tenant_id
        AND D.ou_id=G.ou_id
        AND E.product_id=G.product_id
        AND A.sell_price_date > G.date_from
        AND A.sell_price_date < G.date_to
        AND F.code = G.price_level
    INNER JOIN m_warranty_type H
        ON A.warranty_type_code=H.warranty_type_code AND H.tenant_id=B.tenant_id
    WHERE A.ul_header_id=pUlHeaderId AND
         A.status=vStatusOk;

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

   RETURN vCount;

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