upload

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

    vKeyTenantId            character varying := 'tenantId';
    vKeyUserId              character varying := 'userId';
    vKeyDatetime            character varying := 'datetime';
    vKeyInventoryId         character varying := 'inventoryId';

    vFail                   character varying := 'FAIL';
    vOk                     character varying := 'OK';
    vYes                    character varying := 'Y';
    vNo                     character varying := 'N';
    vEmpty                  character varying := '';
    vSpaceValue             character varying := ' ';
    vNullValueLong          bigint := -99;
    vZero                   numeric := 0;

    vTenantId               bigint;
    vUserId                 bigint;
    vDatetime               character varying;
    vinventoryId            bigint;
    vWarehouseId            bigint;

    vCountFail              bigint;
    vHeaderGroupProductId   bigint;

BEGIN
    -- siapkan parameter
    vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, vKeyTenantId) AS bigint );
    vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, vKeyUserId) AS bigint );
    vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, vKeyDatetime) AS character varying );
    vinventoryId = CAST( f_get_upload_parameter(pUploadHeaderId, vKeyInventoryId) AS bigint );

    SELECT warehouse_from_id, group_product_id FROM in_inventory WHERE inventory_id = vinventoryId INTO vWarehouseId, vHeaderGroupProductId;

    PERFORM SETVAL('up_adj_stock_qty_item_line_no_seq', (SELECT MAX(line_no) FROM in_inventory_item WHERE inventory_id = vInventoryId));

    -- Validasi
    -- 1. qty tidak boleh 0
    -- 2. product harus ada
    -- 3. tenant product harus sesuai
    -- 4. product harus active
    -- 5. product tidak boleh duplicate
        -- A. update product_id
    -- 6. group product id header in_inventory harus sama dgn group product id dari ctgr product
    -- 7. product status exists
        -- B. update product_balance_id -- bisa tidak ada

    -- validasi untuk product yg product_balance_id <> -99
    -- 8. qty product balance stock - qty adj harus >= 0
        -- C. update product_balance_stock_id


    INSERT INTO up_adj_stock_qty_item_detail(adj_stock_qty_item_id, product_id, product_balance_id, product_balance_stock_id)
    SELECT adj_stock_qty_item_id, vNullValueLong, vNullValueLong, vNullValueLong FROM up_adj_stock_qty_item WHERE upload_header_id = pUploadHeaderId;

    --Update serial_number, year_made, expired_date empty menjadi space
    UPDATE up_adj_stock_qty_item A
    SET serial_number = vSpaceValue,
        year_made = vSpaceValue,
        expired_date = vSpaceValue
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.serial_number = vEmpty
        AND A.year_made = vEmpty
        AND A.expired_date = vEmpty;

    -- 1. qty tidak boleh 0
    UPDATE up_adj_stock_qty_item A
    SET status = vFail,
        message = message || 'Qty must not zero, '
    WHERE A.upload_header_id = pUploadHeaderId
    AND qty_adj::numeric = 0;

    -- 2. product harus ada
    UPDATE up_adj_stock_qty_item A
    SET status = vFail,
        message = message || 'Product not found, '
    WHERE A.upload_header_id = pUploadHeaderId
    AND NOT EXISTS (
        SELECT 1
        FROM  m_product B
        WHERE A.product_code = B.product_code
    );

    -- 3. tenant product harus sesuai
    UPDATE up_adj_stock_qty_item A
    SET status = vFail,
        message = message || 'Product tenant is different, '
    WHERE A.upload_header_id = pUploadHeaderId
    AND EXISTS (
        SELECT 1
        FROM  m_product B
        WHERE A.product_code = B.product_code
            AND vTenantId <> B.tenant_id
    );

    -- 4. product harus active
    UPDATE up_adj_stock_qty_item A
    SET status = vFail,
        message = message || 'Product not active, '
    WHERE A.upload_header_id = pUploadHeaderId
    AND EXISTS (
        SELECT 1
        FROM  m_product B
        WHERE A.product_code = B.product_code
            AND vTenantId = B.tenant_id
            AND B.active <> vYes
    );

    -- 5. product must not duplicate
    UPDATE up_adj_stock_qty_item A
    SET status = vFail,
        message = message || 'Product must not duplicate, '
    WHERE A.upload_header_id = pUploadHeaderId
    AND A.product_code IN (
        SELECT product_code
        FROM up_adj_stock_qty_item B
        WHERE B.upload_header_id = pUploadHeaderId
        GROUP BY B.product_code, serial_number, year_made, expired_date
        HAVING count(1) > 1
    );

    -- A. update product_id
    UPDATE up_adj_stock_qty_item_detail A
    SET product_id = C.product_id,
        base_uom_id = C.base_uom_id
    FROM up_adj_stock_qty_item B
    JOIN m_product C ON B.product_code = C.product_code
    WHERE B.upload_header_id = pUploadHeaderId
    AND B.status <> vFail
    AND A.adj_stock_qty_item_id = B.adj_stock_qty_item_id;

    -- 6. group product id header in_inventory harus sama dgn group product id dari ctgr product
    UPDATE up_adj_stock_qty_item A
    SET status = vFail,
        message = message || 'Product must in one group, '
    FROM up_adj_stock_qty_item_detail B
    JOIN m_product C ON B.product_id = C.product_id
    JOIN m_ctgr_product D ON C.ctgr_product_id = D.ctgr_product_id
    WHERE A.upload_header_id = pUploadHeaderId
    AND A.adj_stock_qty_item_id = B.adj_stock_qty_item_id
    AND D.group_product_id <> vHeaderGroupProductId;

    -- 7. product status exists
    UPDATE up_adj_stock_qty_item A
    SET status = vFail,
        message = message || 'Product Status Not Exists, '
    WHERE A.upload_header_id = pUploadHeaderId
    AND EXISTS (
        SELECT 1
        FROM  m_product_status B
        WHERE A.product_status = B.product_status_code
            AND vTenantId = B.tenant_id
            AND B.active <> vYes
    );

    -- B. update product_balance_id -- bisa tidak ada
    UPDATE up_adj_stock_qty_item_detail A
    SET product_balance_id = C.product_balance_id
    FROM up_adj_stock_qty_item B
    JOIN in_product_balance C ON B.serial_number = C.serial_number
                                AND B.year_made = C.product_year_made
                                AND B.expired_date = C.product_expired_date
                                AND C.tenant_id = vTenantId
    WHERE B.upload_header_id = pUploadHeaderId
    AND B.status <> vFail
    AND A.adj_stock_qty_item_id = B.adj_stock_qty_item_id
    AND A.product_id = C.product_id;

    -- validasi untuk product yg product_balance_id = -99
    UPDATE up_adj_stock_qty_item A
    SET status = vFail,
        message = message || 'Qty must > 0 for new product balance, '
    FROM up_adj_stock_qty_item_detail B
    WHERE A.upload_header_id = pUploadHeaderId
    AND A.adj_stock_qty_item_id = B.adj_stock_qty_item_id
    AND qty_adj::numeric <= 0
    AND B.product_balance_id = -99;

    -- validasi untuk product yg product_balance_id <> -99
    -- 8. qty product balance stock - qty adj harus >= 0
    UPDATE up_adj_stock_qty_item A
    SET status = vFail,
        message = message || 'Insufficient product balance stock, '
    FROM up_adj_stock_qty_item_detail B
    JOIN in_product_balance_stock C ON B.product_id = C.product_id
                                AND C.tenant_id = vTenantId
                                AND C.warehouse_id = vWarehouseId
                                AND C.product_balance_id = B.product_balance_id
    WHERE A.upload_header_id = pUploadHeaderId
    AND A.status <> vFail
    AND C.product_status = A.product_status
    AND B.product_balance_id = -99
    AND (A.qty_adj::numeric) < 0
    AND (C.qty + A.qty_adj::numeric) < 0;

    -- C. update product_balance_stock_id
    UPDATE up_adj_stock_qty_item_detail A
    SET product_balance_stock_id = C.product_balance_stock_id
    FROM up_adj_stock_qty_item B
    JOIN in_product_balance_stock C ON C.tenant_id = vTenantId
                                AND C.warehouse_id = vWarehouseId
                                AND C.product_status = B.product_status
    WHERE B.upload_header_id = pUploadHeaderId
    AND B.status <> vFail
    AND A.product_id = C.product_id
    AND C.product_balance_id = A.product_balance_id
    AND A.adj_stock_qty_item_id = B.adj_stock_qty_item_id;

    -- Update status non FAIL to OK
    UPDATE up_adj_stock_qty_item
    SET status = vOk
    WHERE upload_header_id = pUploadHeaderId
    AND status = vEmpty;

    SELECT COALESCE( COUNT(1) , 0) INTO vCountFail
    FROM up_adj_stock_qty_item
    WHERE upload_header_id = pUploadHeaderId
    AND status = vFail;

     IF (vCountFail = 0 ) THEN

        RAISE NOTICE 'INSERT';

--      -- UPDATE OK data to inventory item
--      -- if product exists
--      UPDATE in_inventory_item F
--      SET qty_request = F.qty_request + A.qty_adj::numeric,
--          qty_realization = F.qty_realization + A.qty_adj::numeric,
--          update_user_id = vUserId,
--          update_datetime = vDatetime,
--          version = F.version + 1
--      FROM up_adj_stock_qty_item A
--      INNER JOIN up_adj_stock_qty_item_detail B ON A.adj_stock_qty_item_id = B.adj_stock_qty_item_id
--      WHERE A.upload_header_id = pUploadHeaderId
--      AND A.status = vOk
--          AND F.inventory_id = vInventoryId
--          AND F.product_id = B.product_id
--          AND F.product_balance_id = B.product_balance_id
--          AND F.product_status = A.product_status
--          AND F.serial_number = A.serial_number
--          AND F.product_expired_date = A.expired_date
--          AND F.product_year_made = A.year_made
--          AND F.lot_number = vSpaceValue
--          AND F.ref_doc_type_id = vNullValueLong
--          AND F.ref_id = vNullValueLong
--          AND F.ref_item_id = vNullValueLong;

        -- INSERT OK data to inventory item
        -- if product not exists
        INSERT INTO in_inventory_item(
            tenant_id, inventory_id, line_no,
            product_id, product_balance_id, product_status,
            serial_number, product_expired_date, product_year_made, lot_number,
            ref_doc_type_id, ref_id, ref_item_id,
            curr_code, amount,
            base_uom_id, qty_request, qty_realization,
            remark,
            create_datetime, create_user_id, update_datetime, update_user_id, version)
        SELECT vTenantId, vInventoryId, NEXTVAL('up_adj_stock_qty_item_line_no_seq'),
            B.product_id, B.product_balance_id, A.product_status,
            A.serial_number, A.expired_date, A.year_made, vSpaceValue,
            vNullValueLong, vNullValueLong, vNullValueLong,
            vNullValueLong::character varying, vZero,
            B.base_uom_id, A.qty_adj::numeric, A.qty_adj::numeric,
            A.remark,
            vDatetime, vUserId, vDatetime, vUserId, 0
        FROM up_adj_stock_qty_item A
        INNER JOIN up_adj_stock_qty_item_detail B ON A.adj_stock_qty_item_id = B.adj_stock_qty_item_id
        WHERE A.upload_header_id = pUploadHeaderId
        AND A.status = vOk;
--      AND NOT EXISTS(
--          SELECT 1
--          FROM in_inventory_item D
--          WHERE D.inventory_id = vInventoryId
--              AND D.product_id = B.product_id
--              AND D.product_balance_id = B.product_balance_id
--              AND D.product_status = C.product_status
--              AND D.serial_number = C.serial_number
--              AND D.product_expired_date = C.expired_date
--              AND D.product_year_made = C.year_made
--              AND D.lot_number = vSpaceValue
--              AND D.ref_doc_type_id = vNullValueLong
--              AND D.ref_id = vNullValueLong
--              AND D.ref_item_id = B.product_balance_stock_id
--         );


        -- UPDATE OK data to in_product_balance_stock
        UPDATE in_product_balance_stock Z
        SET qty = Z.qty + A.qty_adj::numeric,
            update_user_id = vUserId,
            update_datetime = vDatetime,
            version = Z.version + 1
        FROM up_adj_stock_qty_item A
        INNER JOIN up_adj_stock_qty_item_detail B ON A.adj_stock_qty_item_id = B.adj_stock_qty_item_id
        WHERE A.upload_header_id = pUploadHeaderId
        AND A.status = vOk
        AND B.product_balance_stock_id <> vNullValueLong
        AND Z.product_balance_stock_id = B.product_balance_stock_id
        AND A.qty_adj::numeric < 0;

        -- UPDATE in_inventory
        UPDATE in_inventory Z
        SET update_user_id = vUserId,
            update_datetime = vDatetime,
            version = Z.version + 1;

    END IF;

    RETURN vCountFail;

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