ul_upload_stock_product

CREATE OR REPLACE FUNCTION ul_upload_stock_product(bigint)
  RETURNS bigint AS
$BODY$
DECLARE
    pUploadHeaderId         ALIAS FOR $1;

    vFail                   character varying(4) := 'FAIL';
    vOk                     character varying(2) := 'OK';
    vYes                    character varying(1) := 'Y';
    vNo                     character varying(1) := 'N';
    vActionInsert           character varying(1) := 'I';
    vActionUpdate           character varying(1) := 'U';
    vActionError            character varying(1) := 'E';
    vEmpty                  character varying(1) := '';
    vSpaceValue             character varying(1) := ' ';

    vTenantId               bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vYearMonth              character varying(6);

    vEmptyId                bigint := -99;
    vOuCompanyId            bigint := -99;
    vCountFail              bigint := 0;

    vKeyYearMonth           character varying := 'yearMonth';

BEGIN
    -- siapkan parameter
    vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
    vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
    vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );

    vYearMonth = CAST( f_get_upload_parameter(pUploadHeaderId, vKeyYearMonth) AS character varying );

    -- GET id OU company
    SELECT A.ou_id INTO vOuCompanyId
    FROM t_ou A
    INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
    WHERE A.tenant_id = vTenantId
    AND B.flg_bu = vYes
    AND B.flg_sub_bu = vNo
    AND B.flg_branch = vNo
    AND B.tenant_id = vTenantId;

    -- CHANGE EMPTY VALYE TO SPACE VALUE
    UPDATE ul_import_data_stock_product A SET
        serial_number = CASE WHEN TRIM(A.serial_number) = vEmpty THEN vSpaceValue ELSE A.serial_number END,
        expired_date = CASE WHEN TRIM(A.expired_date) = vEmpty THEN vSpaceValue ELSE A.expired_date END,
        year_made = CASE WHEN TRIM(A.year_made) = vEmpty THEN vSpaceValue ELSE A.year_made END
    WHERE A.upload_header_id = pUploadHeaderId;

    -- Validasi
    -- # Kode produk wajib diisi dan harus terdaftar di dalam system (m_product)
    -- # Kode gudang wajib diisi dan harus terdaftar di dalam system (m_warehouse)
    -- # Status product harus diisi, dan harus terdaftar di dala system (m_product_status)
    -- # Qty wajib diisi, dengan type data adalah numeric, dan tidak boleh < 0
    -- # Hanya produk dengan flg_serial_number = Y yang boleh diisi kolom Serial Number, Year Made dan Expired Date nya
    -- # List tidak boleh duplikat by Kode produk, Kode gudang, Serial Number, Expired date, Year made, Status produk

    -- # Kode produk wajib diisi dan harus terdaftar di dalam system (m_product)
    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Product Code must be filled, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_produk) = vEmpty;

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Product Code is not registered on system, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_produk) <> vEmpty
        AND NOT EXISTS (
            SELECT 1
            FROM  m_product Z
            WHERE Z.product_code = A.kode_produk
            AND Z.tenant_id = vTenantId
        );

    -- # Kode gudang wajib diisi dan harus terdaftar di dalam system (m_warehouse)
    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Warehouse Code must be filled, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_gudang) = vEmpty;

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Warehouse Code is not registered on system, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_gudang) <> vEmpty
        AND NOT EXISTS (
            SELECT 1
            FROM  m_warehouse Z
            WHERE Z.warehouse_code = A.kode_gudang
            AND Z.tenant_id = vTenantId
        );

    -- # Status product harus diisi, dan harus terdaftar di dala system (m_product_status)
    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Status Produk must be filled, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.status_produk) = vEmpty;

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Status Produk is not registered on system, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.status_produk) <> vEmpty
        AND NOT EXISTS (
            SELECT 1
            FROM  m_product_status Z
            WHERE Z.product_status_code = A.status_produk
            AND Z.tenant_id = vTenantId
        );

    -- # Qty wajib diisi, dengan type data adalah numeric, dan tidak boleh < 0
    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Qty must be filled, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.qty) = vEmpty;

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Qty must be numeric, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND NOT is_numeric(A.qty);

    WITH data_stock_product AS (
        SELECT ul_import_data_stock_product_id, qty
        FROM ul_import_data_stock_product
        WHERE upload_header_id = pUploadHeaderId
        AND TRIM(qty) <> vEmpty
        AND is_numeric(qty)
    )
    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Qty must be greater than or equals zero, '
    FROM data_stock_product B
    WHERE A.ul_import_data_stock_product_id = B.ul_import_data_stock_product_id
        AND B.qty::numeric < 0;

    -- # Hanya produk dengan flg_serial_number = Y yang boleh diisi kolom Serial Number, Year Made dan Expired Date nya
    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Serial Number must be filled, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.serial_number) = vEmpty
        AND TRIM(A.kode_produk) <> vEmpty
        AND EXISTS (
            SELECT 1 FROM m_product Z
            INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
            WHERE A.kode_produk = Z.product_code
            AND Z.tenant_id = vTenantId
            AND Y.flg_serial_number = vYes
        );

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Year Made must be filled, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.year_made) = vEmpty
        AND TRIM(A.kode_produk) <> vEmpty
        AND EXISTS (
            SELECT 1 FROM m_product Z
            INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
            WHERE A.kode_produk = Z.product_code
            AND Z.tenant_id = vTenantId
            AND Y.flg_serial_number = vYes
        );

    WITH groupped_year AS (
        SELECT year_date
        FROM dt_date
        GROUP BY year_date
    )
    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Year Made is not valid (format:YYYY), '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.year_made) <> vEmpty
        AND NOT EXISTS (
            SELECT 1 FROM groupped_year Z WHERE A.year_made = Z.year_date
        );

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Expired Date must be filled, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.expired_date) = vEmpty
        AND TRIM(A.kode_produk) <> vEmpty
        AND EXISTS (
            SELECT 1 FROM m_product Z
            INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
            WHERE A.kode_produk = Z.product_code
            AND Z.tenant_id = vTenantId
            AND Y.flg_serial_number = vYes
        );

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Expired Date is not valid (format:YYYYMMDD), '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.expired_date) <> vEmpty
        AND NOT EXISTS (
            SELECT 1 FROM dt_date Z WHERE A.expired_date = Z.string_date
        );

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Serial Number must be empty value cause product does not support serial number, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.serial_number) <> vEmpty
        AND TRIM(A.kode_produk) <> vEmpty
        AND NOT EXISTS (
            SELECT 1 FROM m_product Z
            INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
            WHERE A.kode_produk = Z.product_code
            AND Z.tenant_id = vTenantId
            AND Y.flg_serial_number = vYes
        );

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Year Made must be empty value cause product does not support serial number, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.year_made) <> vEmpty
        AND TRIM(A.kode_produk) <> vEmpty
        AND NOT EXISTS (
            SELECT 1 FROM m_product Z
            INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
            WHERE A.kode_produk = Z.product_code
            AND Z.tenant_id = vTenantId
            AND Y.flg_serial_number = vYes
        );

    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Expired Date must be empty value cause product does not support serial number, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.expired_date) <> vEmpty
        AND TRIM(A.kode_produk) <> vEmpty
        AND NOT EXISTS (
            SELECT 1 FROM m_product Z
            INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
            WHERE A.kode_produk = Z.product_code
            AND Z.tenant_id = vTenantId
            AND Y.flg_serial_number = vYes
        );

    -- # List tidak boleh duplikat by Kode produk, Kode gudang, Serial Number, Expired date, Year made, Status produk
    WITH duplicate_data_stock_product AS (
        SELECT A.kode_produk, A.kode_gudang, A.serial_number, A.expired_date, A.year_made, A.status_produk
        FROM ul_import_data_stock_product A
        WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_produk) <> vEmpty
        AND TRIM(A.kode_gudang) <> vEmpty
        AND TRIM(A.status_produk) <> vEmpty
        GROUP BY A.kode_produk, A.kode_gudang, A.serial_number, A.expired_date, A.year_made, A.status_produk
        HAVING COUNT(1) > 1
    )
    UPDATE ul_import_data_stock_product A
    SET status = vFail,
        message = message || 'Duplicate data by "Kode Produk, Kode Gudang, Serial Number, Expired Date, Year Made, Status Produk", '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1 FROM duplicate_data_stock_product Z
            WHERE A.kode_produk = Z.kode_produk
            AND A.kode_gudang = Z.kode_gudang
            AND A.serial_number = Z.serial_number
            AND A.expired_date = Z.expired_date
            AND A.year_made = Z.year_made
            AND A.status_produk = Z.status_produk
        );

    -- Cek apakah ada item CSV yang statusnya fail
    IF EXISTS ( SELECT 1 FROM ul_import_data_stock_product B
                WHERE B.upload_header_id = pUploadHeaderId
                AND B.status = vFail ) THEN

        -- Sesuaikan message, agar message paling belakang tidak ada koma
        UPDATE ul_import_data_stock_product A
        SET message = substr(A.message, 1, length(A.message)-2)
        WHERE A.upload_header_id = pUploadHeaderId
            AND A.status = vFail
            AND TRIM(A.message) != vEmpty;

        -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
        -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
        -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
        UPDATE ul_import_data_stock_product A
        SET status = vFail,
            update_status = vActionError
        WHERE upload_header_id = pUploadHeaderId;

        -- Hitung jumlah data yang FAIL
        SELECT COUNT(1) INTO vCountFail
        FROM ul_import_data_stock_product
        WHERE upload_header_id = pUploadHeaderId
            AND status = vFail;
    ELSE
        -- RESET TEMP TABLE
        DELETE FROM tt_ul_product_balance WHERE upload_header_id = pUploadHeaderId;

        -- Update status non FAIL to OK
        UPDATE ul_import_data_stock_product
        SET status = vOk
        WHERE upload_header_id = pUploadHeaderId;

        -- UPDATE SEMUA DATA EXISTING DI SYSTME MENJADI qty = 0
        UPDATE in_product_balance_stock A SET
            qty = 0,
            version = A.version+1,
            update_datetime = vDatetime,
            update_user_id = vUserId
        WHERE A.tenant_id = vTenantId;

        -- DELETE ALL DATA SUMMARY MONTHLY QTY
        DELETE FROM in_summary_monthly_qty Z WHERE Z.tenant_id = vTenantId;

        -- UPDATE EXISTING DATA PADA in_product_balance_stock
        WITH update_data_balance AS (
            UPDATE in_product_balance A SET
                product_expired_date=B.expired_date,
                product_year_made=B.year_made,
                version = A.version+1,
                update_datetime = vDatetime,
                update_user_id = vUserId
            FROM ul_import_data_stock_product B
            INNER JOIN m_product C ON C.product_code = B.kode_produk AND C.tenant_id = vTenantId
            WHERE B.upload_header_id = pUploadHeaderId
            AND A.tenant_id = vTenantId
            AND A.product_id = C.product_id
            AND A.serial_number = B.serial_number
            AND A.lot_number = vSpaceValue

            RETURNING A.*
        )
        INSERT INTO tt_ul_product_balance(
            upload_header_id, action_type, product_balance_id, tenant_id,
            product_id, serial_number, lot_number, product_expired_date,
            product_year_made, version, create_datetime, create_user_id,
            update_datetime, update_user_id)
        SELECT pUploadHeaderId, vActionUpdate, product_balance_id, tenant_id,
            product_id, serial_number, lot_number, product_expired_date,
            product_year_made, version, create_datetime, create_user_id,
            update_datetime, update_user_id
        FROM update_data_balance;

        -- Ubah update_status menjadi U jika data digunakan untuk Update
        UPDATE ul_import_data_stock_product A SET
            update_status = vActionUpdate
        WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1 FROM ul_import_data_stock_product Z
            INNER JOIN m_product ZX ON ZX.product_code = Z.kode_produk AND ZX.tenant_id = vTenantId
            INNER JOIN tt_ul_product_balance Y
                ON Y.product_id = ZX.product_id
                AND Y.serial_number = Z.serial_number
                AND Y.upload_header_id = Z.upload_header_id
                AND Y.action_type = vActionUpdate
                AND Y.tenant_id = vTenantId
                AND Y.lot_number = vSpaceValue
            WHERE Z.upload_header_id = pUploadHeaderId
            AND A.kode_produk = ZX.product_code
            AND A.kode_gudang = Z.kode_gudang
            AND A.serial_number = Y.serial_number
            AND A.expired_date = Y.product_expired_date
            AND A.year_made = Y.product_year_made
            AND A.status_produk = Z.status_produk
        );

        -- INSERT UNTUK DATA BARU
        WITH insert_data_balance AS (
            INSERT INTO in_product_balance(
                    tenant_id, product_id, serial_number, lot_number,
                    product_expired_date, product_year_made, version, create_datetime,
                    create_user_id, update_datetime, update_user_id)
            SELECT vTenantId, B.product_id, A.serial_number, vSpaceValue,
                    A.expired_date, A.year_made, 0, vDatetime,
                    vUserId, vDatetime, vUserId
            FROM ul_import_data_stock_product A
            INNER JOIN m_product B ON A.kode_produk = B.product_code AND B.tenant_id = vTenantId
            WHERE A.upload_header_id = pUploadHeaderId
            AND NOT EXISTS (
                SELECT 1 FROM in_product_balance Z
                WHERE Z.tenant_id = vTenantId
                AND Z.product_id = B.product_id
                AND Z.serial_number = A.serial_number
                AND Z.lot_number = vSpaceValue
            )
            GROUP BY B.product_id, A.serial_number, A.expired_date, A.year_made
            RETURNING *
        )
        INSERT INTO tt_ul_product_balance(
            upload_header_id, action_type, product_balance_id, tenant_id,
            product_id, serial_number, lot_number, product_expired_date,
            product_year_made, version, create_datetime, create_user_id,
            update_datetime, update_user_id)
        SELECT pUploadHeaderId, vActionInsert, product_balance_id, tenant_id,
            product_id, serial_number, lot_number, product_expired_date,
            product_year_made, version, create_datetime, create_user_id,
            update_datetime, update_user_id
        FROM insert_data_balance;

        -- Ubah update_status menjadi I jika data digunakan untuk Insert
        UPDATE ul_import_data_stock_product A SET
            update_status = vActionInsert
        WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1 FROM ul_import_data_stock_product Z
            INNER JOIN m_product ZX ON ZX.product_code = Z.kode_produk AND ZX.tenant_id = vTenantId
            INNER JOIN tt_ul_product_balance Y
                ON Y.product_id = ZX.product_id
                AND Y.serial_number = Z.serial_number
                AND Y.upload_header_id = Z.upload_header_id
                AND Y.action_type = vActionInsert
                AND Y.tenant_id = vTenantId
                AND Y.lot_number = vSpaceValue
            WHERE Z.upload_header_id = pUploadHeaderId
            AND A.kode_produk = ZX.product_code
            AND A.kode_gudang = Z.kode_gudang
            AND A.serial_number = Y.serial_number
            AND A.expired_date = Y.product_expired_date
            AND A.year_made = Y.product_year_made
            AND A.status_produk = Z.status_produk
        );

        -- Update existing data balance stock
        UPDATE in_product_balance_stock A SET
            qty = B.qty::NUMERIC,
            VERSION = A.VERSION+1,
            update_datetime = vDatetime,
            update_user_id = vUserId
        FROM ul_import_data_stock_product B
        INNER JOIN m_product BX ON BX.product_code = B.kode_produk AND BX.tenant_id = vTenantId
        INNER JOIN tt_ul_product_balance C
                ON C.product_id = BX.product_id
                AND C.serial_number = B.serial_number
                AND C.upload_header_id = B.upload_header_id
                AND C.tenant_id = vTenantId
                AND C.lot_number = vSpaceValue
        INNER JOIN m_warehouse D ON D.warehouse_code = B.kode_gudang AND D.tenant_id = vTenantId
        WHERE B.upload_header_id = pUploadHeaderId
        AND A.tenant_id = vTenantId
        AND A.warehouse_id = D.warehouse_id
        AND A.product_id = BX.product_id
        AND A.product_balance_id = C.product_balance_id
        AND A.product_status = B.status_produk;

        -- Insert new data balance stock
        INSERT INTO in_product_balance_stock(
            tenant_id, warehouse_id, product_id,
            product_balance_id, product_status, base_uom_id, qty, VERSION,
            create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT vTenantId, D.warehouse_id, B.product_id,
                C.product_balance_id, A.status_produk, B.base_uom_id, A.qty::NUMERIC, 0,
                vDatetime, vUserId, vDatetime, vUserId
        FROM ul_import_data_stock_product A
        INNER JOIN m_product B ON B.product_code = A.kode_produk AND B.tenant_id = vTenantId
        INNER JOIN tt_ul_product_balance C
                ON C.product_id = B.product_id
                AND C.serial_number = A.serial_number
                AND C.upload_header_id = A.upload_header_id
                AND C.tenant_id = vTenantId
                AND C.lot_number = vSpaceValue
        INNER JOIN m_warehouse D ON A.kode_gudang = D.warehouse_code AND D.tenant_id = vTenantId
        WHERE A.upload_header_id = pUploadHeaderId
        AND NOT EXISTS (
            SELECT 1 FROM in_product_balance_stock Z
            WHERE Z.tenant_id = vTenantId
            AND Z.warehouse_id = D.warehouse_id
            AND Z.product_id = B.product_id
            AND Z.product_balance_id = C.product_balance_id
            AND Z.product_status = A.status_produk
        );

        -- Insert data summary monthly qty
        INSERT INTO in_summary_monthly_qty(
            date_year_month, tenant_id, ou_id, sub_ou_id,
            doc_type_id, warehouse_id, product_id, product_balance_id, product_status,
            base_uom_id, qty, VERSION, create_datetime, create_user_id, update_datetime,
            update_user_id)
        SELECT vYearMonth, vTenantId, vOuCompanyId, vOuCompanyId,
                vEmptyId, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status,
                A.base_uom_id, A.qty, 0, vDatetime, vUserId, vDatetime,
                vUserId
        FROM in_product_balance_stock A
        WHERE A.tenant_id = vTenantId;

        -- RESET TEMP TABLE
        DELETE FROM tt_ul_product_balance WHERE upload_header_id = pUploadHeaderId;

    END IF;

    RETURN vCountFail;

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