Script to update batch number for every 20 records of even numbers

 Hi All,

We recently had a request from user to group then item and split the shipment for every 20 quantities.

Below is a POC of how to create the shipment (Batch) based on type of item (Odd/even).

Let me insert 1 to 100 number in a table and create a unique batch number only for even numbers where every batch has 20 numbers.

-- Table Creation
CREATE TABLE batch_test
(
    id            NUMBER
   ,batch_name    VARCHAR2 (20)
);


-- Insert records into table
BEGIN
    FOR i IN 1 .. 100
    LOOP
        INSERT INTO batch_test (id)
             VALUES (i);
    END LOOP;

    COMMIT;
END;
/

-- Script to create Batch number

SET SERVEROUTPUT ON;

DECLARE
    v_count      NUMBER;
    loop_count   NUMBER := 0;
    v_limit NUMBER:=20; 
BEGIN
    SELECT COUNT (*)
      INTO v_count
      FROM batch_test
     WHERE MOD (id, 2) = 0
       AND batch_name IS NULL;

    DBMS_OUTPUT.put_line ('Even count ' || v_count);

    WHILE v_count > v_limit
    LOOP
        loop_count   := loop_count + 1;

        UPDATE batch_test
           SET batch_name   = TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') || loop_count
         WHERE MOD (id, 2) = 0
           AND batch_name IS NULL
           AND ROWNUM <= v_limit;

        v_count      := v_count - v_limit;
    END LOOP;

    IF v_count > 0
    THEN
        loop_count   := loop_count + 1;

        UPDATE batch_test
           SET batch_name   = TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') || loop_count
         WHERE MOD (id, 2) = 0
           AND batch_name IS NULL
           AND ROWNUM <= v_limit;
    END IF;
    COMMIT;
END;
/