f_sdp_intgr_process_product

/**
 * author fredi, 28 Feb 2018
 */
CREATE OR REPLACE FUNCTION f_sdp_intgr_process_product(bigint)
    RETURNS bigint AS
$BODY$
DECLARE
    pHeaderId           ALIAS FOR $1;

    vStatusOK           character varying := 'OK';
    vStatusFail         character varying := 'FAIL';
    vEmpty              character varying := '';
    vProductStatusGood  character varying := 'GOOD';
    vEmptyId            bigint := -99;
    vInitial            character varying := 'INITIAL';

    vDatetime           character varying;
    vUserId             bigint;
    vTenantId           bigint;
    vWarehouseId        bigint;
    vUomId              bigint;
    vDate               character varying;
BEGIN
    vDatetime   := f_get_intgr_parameter(pHeaderId, 'datetime');
    vUserId     := CAST( f_get_intgr_parameter(pHeaderId, 'userId') AS bigint);
    vTenantId   := CAST( f_get_intgr_parameter(pHeaderId, 'tenantId') AS bigint);
    vWarehouseId    := CAST( f_get_intgr_parameter(pHeaderId, 'warehouseId') AS bigint);
    vUomId  := CAST( f_get_intgr_parameter(pHeaderId, 'uomId') AS bigint);

    SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDD')::CHARACTER VARYING INTO vDate;

    UPDATE ul_intgr_product
    SET status = vStatusOK
    WHERE header_id = pHeaderId;

    /**
     * 1. Cek product_code tidak boleh ada yg sama di CSV
     * 2. Cek product code tidak boleh ada yg sama di database SDP
     */

    UPDATE ul_intgr_product
    SET status = vStatusFail,
        message = 'Duplicate Entry For this Product Code '||product_code
    WHERE header_id = pHeaderId
        AND EXISTS (
            SELECT 1
            FROM ul_intgr_product B
            WHERE ul_intgr_product.header_id = B.header_id
                AND ul_intgr_product.product_code = B.product_code
                AND ul_intgr_product.intgr_product_id <> B.intgr_product_id
        );

    UPDATE ul_intgr_product
    SET status = vStatusFail,
        message = message || ( CASE WHEN message <> vEmpty THEN ', ' ELSE '' END ) || 'Product code/Merchant SKU '||product_code||' already exists in database'
    WHERE header_id = pHeaderId
        AND EXISTS (
            SELECT 1
            FROM m_product A
            WHERE ul_intgr_product.product_code = A.merchant_sku
        );

    UPDATE ul_intgr_product
    SET status = vStatusFail,
        message = 'Product Code '||product_code||' already found in the system, '
    WHERE header_id = pHeaderId
        AND EXISTS (
            SELECT 1
            FROM ul_intgr_product B
            WHERE ul_intgr_product.header_id = B.header_id
                AND ul_intgr_product.product_code = B.product_code
                AND ul_intgr_product.intgr_product_id <> B.intgr_product_id
        );

    WITH inserted_product AS (
        INSERT INTO m_product(
            merchant_sku, product_name,
            product_url, product_description, upc_code,
            length, width, height, weight,
            specification_detail, unique_selling_point, product_story,

            create_datetime, create_user_id, update_datetime, update_user_id,
            version, active,  active_datetime, non_active_datetime )
        SELECT A.product_code, A.product_name,
            vEmpty, vEmpty, A.product_barcode,
            CAST (A."length" AS numeric), CAST(A.width AS numeric), CAST(A.height AS numeric), CAST(A.weight AS numeric) AS weight,
            vEmpty, vEmpty, vEmpty,

            vDatetime, vUserId, vDatetime, vUserId,
            0, 'Y', vDatetime, vDatetime
        FROM ul_intgr_product A
        WHERE A.header_id = pHeaderId
            AND A.status = vStatusOK
        RETURNING *
    ), inserted_product_balance AS (
        INSERT INTO sdp_product_balance(
                tenant_id, product_id, serial_number, lot_number,
                product_expired_date, product_year_made, create_user_id, create_datetime,
                update_user_id, update_datetime, version)
        SELECT vTenantId, A.product_id, vEmpty, vEmpty,
                vEmpty, vEmpty, vUserId, vDatetime,
                vUserId, vDatetime, 0
        FROM inserted_product A
        RETURNING *
    ), inserted_product_balance_stock AS (
        INSERT INTO sdp_product_balance_available_stock(
                tenant_id, warehouse_id,
                product_id, product_balance_id, product_status, base_uom_id,
                qty, create_user_id, create_datetime, update_user_id, update_datetime,
                version)
        SELECT A.tenant_id, vWarehouseId,
                A.product_id, A.product_balance_id, vProductStatusGood, vUomId,
                C.available_stock::numeric, vUserId, vDatetime, vUserId, vDatetime,
                0
        FROM inserted_product_balance A
        INNER JOIN inserted_product B ON A.product_id = B.product_id
        INNER JOIN ul_intgr_product C ON B.merchant_sku = C.product_code
        WHERE C.header_id = pHeaderId
            AND C.status = vStatusOK
        RETURNING *
    )
    INSERT INTO sdp_log_product_balance_stock(
            tenant_id, ref_id, ref_doc_no,
            ref_doc_date, channel_id, product_id, product_balance_id, warehouse_id,
            product_status, base_uom_id, qty, create_user_id, create_datetime,
            update_user_id, update_datetime, version, ref_doc_type_id)
    SELECT A.tenant_id, vEmptyId, vInitial,
            vDate, vEmptyId, A.product_id, A.product_balance_id, A.warehouse_id,
            A.product_status, A.base_uom_id, A.qty, vUserId, vDatetime,
            vUserId, vDatetime, 0, vEmptyId
    FROM inserted_product_balance_stock A;

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