OraFAQ Forum: SQL & PL/SQL » Help in building batch logic

Home » SQL & PL/SQL » SQL & PL/SQL » Help in building batch logic (Oracle 10.2.0.4.0)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Help in building batch logic [message #574814]

Wed, 16 January 2013 00:29

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

Dear All, I am trying to build one logic , there is one table with lot of item descriptions and qty , i need to split these items as batches with qty not exceeding more than 10 and with one more condition that previous description of item should not be same , this i am doing by inserting them into a different table.Kindly check below the test case along with what i tried and i am getting qty more than 10 in batch 2 as provided in test case below.


CREATE TABLE ow_stg_ship (ps_code VARCHAR2(12),ps_desc VARCHAR2(20),wt NUMBER);

CREATE TABLE OS_BTCH_ALLOC (BPS_BTCH NUMBER,BPS_CODE VARCHAR2(12),BPS_DESC VARCHAR2(20),BPS_WT NUMBER);
   
   
   INSERT INTO OW_STG_SHIP VALUES ('A','AAA',3);
   
   INSERT INTO OW_STG_SHIP VALUES ('B','AAA',5);
   
   INSERT INTO OW_STG_SHIP VALUES ('C','AAA',2);
   
   INSERT INTO OW_STG_SHIP VALUES ('D','BBB',5);
   
   INSERT INTO OW_STG_SHIP VALUES ('C','BBB',6);
   
   INSERT INTO OW_STG_SHIP VALUES ('E','BBB',2);
   
   INSERT INTO OW_STG_SHIP VALUES ('F','CCC',2);
   
   INSERT INTO OW_STG_SHIP VALUES ('G','CCC',3);


--Procedure for inserting the batches.

/* Formatted on 2013/01/16 10:09 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c1
   IS
      SELECT   ps_code, ps_desc, wt
          FROM ow_stg_ship
      ORDER BY ps_desc ASC;

   m_prv       VARCHAR2 (50);
   m_all_wt    NUMBER;
   m_rec       NUMBER        := 0;
   tsum        NUMBER        := 0;
   tsum1       NUMBER;
   batch_cnt   NUMBER        := 1;
BEGIN
   m_all_wt := 10;
   m_prv := NULL;

   FOR i IN c1
   LOOP
      m_rec := c1%ROWCOUNT;
   END LOOP;

   FOR i IN c1
   LOOP
      tsum := tsum + i.wt;

      IF (tsum > m_all_wt AND m_prv <> i.ps_desc) OR m_rec = c1%ROWCOUNT
      THEN
         tsum1 := tsum;
      END IF;

      IF (tsum > m_all_wt AND m_prv <> i.ps_desc)
      THEN
         tsum := 0;
         batch_cnt := batch_cnt + 1;
      END IF;

      tsum1 := NULL;

      INSERT INTO os_btch_alloc
                  (bps_btch, bps_code, bps_desc, bps_wt
                  )
           VALUES (batch_cnt, i.ps_code, i.ps_desc, i.wt
                  );

      m_prv := i.ps_desc;
   END LOOP;
END;

commit;

  SELECT * FROM os_btch_alloc;

BPS_BTCH	BPS_CODE	BPS_DESC	BPS_WT
1	            A	             AAA	3
1	            B	             AAA	5
1	            C	             AAA	2
2	            D	             BBB	5
2	            E	             BBB	2
2	            C	             BBB	6
2	            F	             CCC	2
2	            G	             CCC	3


--If you see above the batch 2 has more than 10 qty, it doesnt has to be more than 10 in any case

--Desired output is

BPS_BTCH	BPS_CODE	BPS_DESC	BPS_WT
1	           A	            AAA	          3
1	           B	            AAA	          5
1	           C	            AAA	          2
2	           D	            BBB	          5
2	           E	            BBB	          2
2	           C	            BBB           3
3	           F	            CCC	          5
3	           G	            CCC	          3

[Updated on: Wed, 16 January 2013 00:42]