Upload Produk

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

  pSessionId            ALIAS FOR $1;
  pRecordOwnerId        ALIAS FOR $2;

  vEmptyId              bigint := -99;
  vProductId            bigint;
  vUomId                bigint;
  vFindProductId        bigint;
  vResult               bigint;

  vFail                 character varying := 'FAIL';
  vOk                   character varying := 'OK';

  vYes                  character varying := 'Y';
  vNo                   character varying := 'N';
  vCurrentDateTime      character varying;
  vEmpty                character varying := ' ';

  vCursorRow            tt_ul_product%ROWTYPE;

BEGIN

    SELECT f_datetime((extract(epoch from now())*1000)::bigint) INTO vCurrentDateTime;

    -- Validasi produk tidak boleh duplikate saat upload
    WITH duplicateCode AS (
        SELECT product_code, COUNT(product_code)
        FROM tt_ul_product
        WHERE session_id = pSessionId
            AND record_owner_id = pRecordOwnerId
        GROUP BY product_code
        HAVING COUNT(product_code) > 1
    )
    UPDATE tt_ul_product Z
        SET status = vFail, message = concat(Z.message, 'Kode Produk ', A.product_code, ' Duplikat, ')
    FROM tt_ul_product A
    INNER JOIN duplicateCode B ON A.product_code = B.product_code
    WHERE A.session_id = pSessionId
        AND Z.product_code = A.product_code
        AND Z.record_owner_id = pRecordOwnerId;

    -- Validasi Kode Kategori Produk tidak ditemukan
    UPDATE tt_ul_product Z
        SET status = vFail, message = concat(Z.message, 'Kode Kategori Produk ', A.product_ctgr_code, ' Tidak ditemukan, ')
    FROM tt_ul_product A
    LEFT JOIN m_product_ctgr B ON A.product_ctgr_code = B.product_ctgr_code
        AND B.product_ctgr_parent_id = vEmptyId
    WHERE A.session_id = pSessionId
        AND Z.product_code = A.product_code
        AND Z.record_owner_id = A.record_owner_id
        AND Z.record_owner_id = pRecordOwnerId
        AND Z.session_id = A.session_id
        AND B.product_ctgr_id IS NULL;

    -- Validasi Kode Sub Kategori Produk tidak ditemukan
    UPDATE tt_ul_product Z
        SET status = vFail, message = concat(Z.message, 'Kode Sub Kategori Produk ', A.sub_product_ctgr_code, ' Tidak ditemukan, ')
    FROM tt_ul_product A
    LEFT JOIN m_product_ctgr B ON A.sub_product_ctgr_code = B.product_ctgr_code
        AND B.product_ctgr_parent_id <> vEmptyId
    WHERE A.session_id = pSessionId
        AND Z.product_code = A.product_code
        AND Z.record_owner_id = A.record_owner_id
        AND Z.record_owner_id = pRecordOwnerId
        AND Z.session_id = A.session_id
        AND B.product_ctgr_id IS NULL;

    -- Validasi Kode Uom harus terdaftar di table master
    UPDATE tt_ul_product Z
        SET status = vFail, message = concat(Z.message, 'Kode UOM ', A.uom_code, ' tidak terdaftar, ')
    FROM tt_ul_product A
    WHERE A.session_id = pSessionId
        AND Z.session_id = A.session_id
        AND A.product_code = Z.product_code
        AND f_get_uom_id_by_uom_code(A.uom_code, vEmptyId) = -99;

    FOR vCursorRow IN
        SELECT *
        FROM tt_ul_product A
        WHERE A.session_id = pSessionId
            AND A.status = vOk
            AND NOT EXISTS (SELECT 1 FROM m_product Z WHERE A.product_code = Z.product_code AND A.record_owner_id = Z.record_owner_id)
    LOOP
        vProductId := nextval('m_product_seq');

        SELECT f_get_uom_id_by_uom_code(vCursorRow.uom_code, vEmptyId) INTO vUomId;

        INSERT INTO m_product (
            stock_row_id, product_id, record_owner_id, product_code, product_name, product_local_name,
            product_ctgr_id, product_sub_ctgr_id, part_no, brand_name, color,
            style_product, class_product, minimum_stock_level, image_url, version,
            active, active_datetime, non_active_datetime, create_datetime, create_username,
            update_datetime, update_username, uom_id) VALUES (

            vEmpty, vProductId, vCursorRow.record_owner_id, vCursorRow.product_code, vCursorRow.product_name, vCursorRow.product_name,
            vEmptyId, vEmptyId, vCursorRow.part_no, vCursorRow.brand_name, vCursorRow.color,
            vCursorRow.style_product, vCursorRow.class_product, vCursorRow.minimum_stock_level, vEmpty, 0,
            vYes, vCurrentDateTime, vEmpty, vCursorRow.create_datetime, vCursorRow.create_username,
            vCursorRow.update_datetime, vCursorRow.update_username, vUomId);

        --. Simpan ke table m_product_ctgr_mapping
        INSERT INTO m_product_ctgr_mapping(
            product_id, product_ctgr_id, product_sub_ctgr_id, version, create_username,
            create_datetime, update_username, update_datetime) VALUES (
            vProductId, f_get_product_ctgr_id_by_product_ctgr_code(vCursorRow.product_ctgr_code), f_get_product_ctgr_id_by_product_ctgr_code(vCursorRow.sub_product_ctgr_code), 0, vCursorRow.create_username,
            vCursorRow.update_datetime, vCursorRow.update_username, vCursorRow.update_datetime);

        INSERT INTO m_product_dimension(
                product_id, weight, length, width, height, create_datetime,
                create_username, update_datetime, update_username
                ) VALUES
                (vProductId, vCursorRow.weight, vCursorRow.length, vCursorRow.width, vCursorRow.height, vCursorRow.create_datetime,
                vCursorRow.create_username, vCursorRow.update_datetime, vCursorRow.update_username);

        --. Simpan ke table in_product_balance
        INSERT INTO in_product_balance(
            record_owner_id, product_id, serial_number, lot_number, expired_date,
            year_made, version, create_datetime, create_username, update_datetime, update_username
            ) VALUES
            (vCursorRow.record_owner_id, vProductId, vEmpty, vEmpty, vEmpty,
            vEmpty, 0, vCursorRow.create_datetime, vCursorRow.create_username, vCursorRow.update_datetime, vCursorRow.update_username);

    END LOOP;

    SELECT COUNT(1) INTO vResult
    FROM tt_ul_product
    WHERE session_id = pSessionId
        AND status = vFail;

    RETURN vResult;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path = public, mstr, batch
  COST 100;
  /