in_upload_put_away_item_20170928

/**
 * Adrian, Sep 27, 2017
 * Function upload item Put Away
 */

CREATE OR REPLACE FUNCTION in_upload_put_away_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;

    vCountFail              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 );

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

    -- Validasi
    -- 1. product harus ada
    -- 2. tenant product harus sesuai
    -- 3. product harus active
    -- 4. product balance harus ada
    -- 5. tenant product balance harus sama
    -- 6. product balance stock harus ada
    -- 7. tenant product balance stock harus sesuai
    -- 8. tidak boleh ada product balance stock yang sama
    -- 9. stok product balance stock setelah put away harus >= 0
    -- 10. sub category product harus terdapat dalam item warehouse

    --Update serial_number, year_made, expired_date empty menjadi space
    UPDATE up_put_away_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. product harus ada
    UPDATE up_put_away_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
        );

    -- 2. tenant product harus sesuai
    UPDATE up_put_away_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
        );

    -- 3. product harus active
    UPDATE up_put_away_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
        );

    -- 4. product balance harus ada
    UPDATE up_put_away_item A
    SET status = vFail,
        message = message || 'Product balance not found, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND NOT EXISTS (
            SELECT 1
            FROM  m_product B
            INNER JOIN in_product_balance C
                ON B.product_id = C.product_id
                    AND A.serial_number = C.serial_number
                    AND A.year_made = C.product_year_made
                    AND A.expired_date = C.product_expired_date
            WHERE vTenantId = B.tenant_id
                AND A.product_code = B.product_code
        );

    -- 5. tenant product balance harus sama
    UPDATE up_put_away_item A
    SET status = vFail,
        message = message || 'Product balance tenant is different, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1
            FROM  m_product B
            INNER JOIN in_product_balance C
                ON B.product_id = C.product_id
                    AND A.serial_number = C.serial_number
                    AND A.year_made = C.product_year_made
                    AND A.expired_date = C.product_expired_date
            WHERE vTenantId = B.tenant_id
                AND A.product_code = B.product_code
                AND C.tenant_id <> vTenantId
        );

    -- 6. product balance stock harus ada
    UPDATE up_put_away_item A
    SET status = vFail,
        message = message || 'Product balance stock not found, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND NOT EXISTS (
            SELECT 1
            FROM  m_product B
            INNER JOIN in_product_balance C
                ON B.product_id = C.product_id
                    AND A.serial_number = C.serial_number
                    AND A.year_made = C.product_year_made
                    AND A.expired_date = C.product_expired_date
                    AND C.tenant_id = vTenantId
            INNER JOIN in_inventory D ON D.inventory_id = vInventoryId
            INNER JOIN in_product_balance_stock E
                ON C.product_balance_id = E.product_balance_id
                    AND E.warehouse_id = D.warehouse_from_id
                    AND E.product_id = B.product_id
                    AND E.product_status = A.product_status
            WHERE vTenantId = B.tenant_id
                AND A.product_code = B.product_code
        );

    -- 7. tenant product balance stock harus sesuai
    UPDATE up_put_away_item A
    SET status = vFail,
        message = message || 'Product balance stock not found, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1
            FROM  m_product B
            INNER JOIN in_product_balance C
                ON B.product_id = C.product_id
                    AND A.serial_number = C.serial_number
                    AND A.year_made = C.product_year_made
                    AND A.expired_date = C.product_expired_date
                    AND C.tenant_id = vTenantId
            INNER JOIN in_inventory D ON D.inventory_id = vInventoryId
            INNER JOIN in_product_balance_stock E
                ON C.product_balance_id = E.product_balance_id
                    AND E.warehouse_id = D.warehouse_from_id
                    AND E.product_id = B.product_id
                    AND E.product_status = A.product_status
            WHERE vTenantId = B.tenant_id
                AND A.product_code = B.product_code
                AND E.tenant_id <> vTenantId
        );

    -- 9. stok product balance stock setelah put away harus >= 0
     WITH grouped_put_away_item AS(
        SELECT E.product_balance_stock_id, SUM (A.qty_transfer::numeric) AS summed_qty_transfer
        FROM up_put_away_item A
        INNER JOIN m_product B
            ON vTenantId = B.tenant_id
                AND A.product_code = B.product_code
        INNER JOIN in_product_balance C
            ON B.product_id = C.product_id
                AND A.serial_number = C.serial_number
                AND A.year_made = C.product_year_made
                AND A.expired_date = C.product_expired_date
                AND C.tenant_id = vTenantId
        INNER JOIN in_inventory D ON D.inventory_id = vInventoryId
        INNER JOIN in_product_balance_stock E
            ON C.product_balance_id = E.product_balance_id
                AND E.warehouse_id = D.warehouse_from_id
                AND E.product_id = B.product_id
                AND E.product_status = A.product_status
                AND E.tenant_id = vTenantId
        AND A.upload_header_id = pUploadHeaderId
        GROUP BY E.product_balance_stock_id
    )
    UPDATE up_put_away_item A
    SET status = vFail,
        message = message || 'Product balance stock not sufficient, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1
            FROM  m_product B
            INNER JOIN in_product_balance C
                ON B.product_id = C.product_id
                    AND A.serial_number = C.serial_number
                    AND A.year_made = C.product_year_made
                    AND A.expired_date = C.product_expired_date
                    AND C.tenant_id = vTenantId
            INNER JOIN in_inventory D ON D.inventory_id = vInventoryId
            INNER JOIN in_product_balance_stock E
                ON C.product_balance_id = E.product_balance_id
                    AND E.warehouse_id = D.warehouse_from_id
                    AND E.product_id = B.product_id
                    AND E.product_status = A.product_status
                    AND E.tenant_id = vTenantId
            INNER JOIN grouped_put_away_item F ON E.product_balance_stock_id = F.product_balance_stock_id
            WHERE vTenantId = B.tenant_id
                AND A.product_code = B.product_code
                AND E.qty - F.summed_qty_transfer < 0
        );

    -- 10. sub category product harus terdapat dalam item warehouse
    UPDATE up_put_away_item A
    SET status = vFail,
        message = message || 'Product not authorized on destination warehouse, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND NOT EXISTS (
            SELECT 1
            FROM  m_product B
            INNER JOIN in_product_balance C ON B.product_id = C.product_id
            INNER JOIN in_inventory D ON D.inventory_id = vInventoryId
            INNER JOIN m_item_warehouse E ON B.sub_ctgr_product_id = E.sub_ctgr_product_id AND D.warehouse_to_id = E.warehouse_id AND vTenantId = E.tenant_id
        );

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

    SELECT COALESCE( COUNT(1) , 0) INTO vCountFail
    FROM up_put_away_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
        WITH grouped_put_away_item AS(
            SELECT B.product_id, C.product_balance_id, A.product_status,
            A.serial_number, A.expired_date, A.year_made, E.product_balance_stock_id,
            SUM (A.qty_transfer::numeric) AS summed_qty_transfer
            FROM up_put_away_item A
            INNER JOIN m_product B
                ON vTenantId = B.tenant_id
                    AND A.product_code = B.product_code
            INNER JOIN in_product_balance C
                ON B.product_id = C.product_id
                    AND A.serial_number = C.serial_number
                    AND A.year_made = C.product_year_made
                    AND A.expired_date = C.product_expired_date
                    AND C.tenant_id = vTenantId
            INNER JOIN in_inventory D ON D.inventory_id = vInventoryId
            INNER JOIN in_product_balance_stock E
                ON C.product_balance_id = E.product_balance_id
                    AND E.warehouse_id = D.warehouse_from_id
                    AND E.product_id = B.product_id
                    AND E.product_status = A.product_status
                    AND E.tenant_id = vTenantId
            AND A.upload_header_id = pUploadHeaderId
            AND A.status = vOk
            GROUP BY B.product_id, C.product_balance_id, A.product_status,
            A.serial_number, A.expired_date, A.year_made, E.product_balance_stock_id
        )
        UPDATE in_inventory_item F
        SET qty_request = F.qty_request + A.summed_qty_transfer,
            qty_realization = F.qty_realization + A.summed_qty_transfer,
            update_user_id = vUserId,
            update_datetime = vDatetime,
            version = F.version + 1
        FROM grouped_put_away_item A
        WHERE F.inventory_id = vInventoryId
            AND F.product_id = A.product_id
            AND F.product_balance_id = A.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 = A.product_balance_stock_id;

        -- 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_put_away_item_line_no_seq'),
            B.product_id, C.product_balance_id, A.product_status,
            A.serial_number, A.expired_date, A.year_made, vSpaceValue,
            vNullValueLong, vNullValueLong, E.product_balance_stock_id,
            vNullValueLong::character varying, vZero,
            B.base_uom_id, A.qty_transfer::numeric, A.qty_transfer::numeric,
            A.remark,
            vDatetime, vUserId, vDatetime, vUserId, 0
        FROM up_put_away_item A
        INNER JOIN m_product B
            ON vTenantId = B.tenant_id
                AND A.product_code = B.product_code
        INNER JOIN in_product_balance C
            ON B.product_id = C.product_id
                AND A.serial_number = C.serial_number
                AND A.year_made = C.product_year_made
                AND A.expired_date = C.product_expired_date
                AND C.tenant_id = vTenantId
        INNER JOIN in_inventory D ON D.inventory_id = vInventoryId
        INNER JOIN in_product_balance_stock E
            ON C.product_balance_id = E.product_balance_id
                AND E.warehouse_id = D.warehouse_from_id
                AND E.product_id = B.product_id
                AND E.product_status = A.product_status
                AND E.tenant_id = vTenantId
        WHERE NOT EXISTS(
            SELECT 1
            FROM in_inventory_item F
            WHERE F.inventory_id = vInventoryId
                AND F.product_id = B.product_id
                AND F.product_balance_id = C.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 = E.product_balance_stock_id
        )
        AND A.upload_header_id = pUploadHeaderId
        AND A.status = vOk;

        -- UPDATE OK data to in_product_balance_stock
        WITH grouped_put_away_item AS(
            SELECT E.product_balance_stock_id, SUM (A.qty_transfer::numeric) AS summed_qty_transfer
            FROM up_put_away_item A
            INNER JOIN m_product B
                ON vTenantId = B.tenant_id
                    AND A.product_code = B.product_code
            INNER JOIN in_product_balance C
                ON B.product_id = C.product_id
                    AND A.serial_number = C.serial_number
                    AND A.year_made = C.product_year_made
                    AND A.expired_date = C.product_expired_date
                    AND C.tenant_id = vTenantId
            INNER JOIN in_inventory D ON D.inventory_id = vInventoryId
            INNER JOIN in_product_balance_stock E
                ON C.product_balance_id = E.product_balance_id
                    AND E.warehouse_id = D.warehouse_from_id
                    AND E.product_id = B.product_id
                    AND E.product_status = A.product_status
                    AND E.tenant_id = vTenantId
            AND A.upload_header_id = pUploadHeaderId
            AND A.status = vOk
            GROUP BY E.product_balance_stock_id
        )
        UPDATE in_product_balance_stock Z
        SET qty = Z.qty - A.summed_qty_transfer,
            update_user_id = vUserId,
            update_datetime = vDatetime,
            version = Z.version + 1
        FROM grouped_put_away_item A
        WHERE Z.product_balance_stock_id = A.product_balance_stock_id;

        -- 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;
  /