Home » SQL & PL/SQL » SQL & PL/SQL » Complex batch allocation logic + summary of alternate rows [2 merged by jd] (Oracle 10g)
Complex batch allocation logic + summary of alternate rows [2 merged by jd] [message #581429] Sat, 06 April 2013 08:02 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have a requirement of combining detail records by summing their weight and number of attachments based on item description and where tmp_ps_code is null, applying the criterias and assign batch numbers to each one in detail records based on some criterias as follows
a) Each batch number can accomodate the weight of not more than 50.
b) Each batch number can accomodate not more than 10 pcs.
c) if both the condition comes together then whichever is highest get priority, like if the sum of qty is 60 and pcs are 50 then 50 pcs will get priority and 5 batch numbers will be allocated.Actually these details records are to be inserted into another table called os_batch. Representing the test case and what i tried.


create table ow_temp_data ( tmp_no varchar2(12),tmp_pm_code varchar2(12),tmp_ps_code varchar2(12),tmp_desc varchar2(30),tmp_wt number ,tmp_qty number,TMP_TYPE VARCHAR2(2) );

insert into ow_temp_data (tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','A' , NULL,'H170',25  ,0,'W' );

insert into ow_temp_data ( tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','A' ,'A01' ,'HEA100' ,0  ,10,'WI' );

insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','B' , NULL,'H170',25  ,0,'W' );

insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','B' ,'B01' ,'HEA100' ,0  ,10,'WI' );



insert into ow_temp_data (tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','C' , NULL,'HB120',25  ,0,'W' );

insert into ow_temp_data ( tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','C' ,'C01' ,'HB100' ,0  ,10,'WI' );

insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','D' , NULL,'HB120',5  ,0,'W' );

insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','D' ,'D01' ,'HB100' ,0  ,40,'WI' );



insert into ow_temp_data (tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','E' , NULL,'PL120',30  ,0,'W' );

insert into ow_temp_data ( tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','E' ,'E01' ,'P100' ,0  ,10,'WI' );

insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','F' , NULL,'PL120',35  ,0,'W' );

insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','F' ,'F01' ,'P100' ,0  ,40,'WI' );


TMP_NO	TMP_PM_CODE	TMP_PS_CODE	TMP_DESC	TMP_WT	TMP_QTY	TMP_TYPE
0	A	           NULL	            H170	  25	   0	   W
0	A	           A01	           HEA100	   0	  10	  WI
0	B	           NULL             H170	  25	   0	   W
0	B	           B01	          HEA100	   0  	  10	  WI
0	C	          NULL	           HB120	  25       0	   W
0	C	           C01	           HB100	   0	  10	  WI
0	D	          NULL	           HB120	   5	   0	   W
0	D	           D01	           HB100	   0	   40	  WI
0	E	          NULL	           PL120	  30	    0	   W
0	E	           E01	            P100	   0	   10	  WI
0	F	          NULL	           PL120	  35	    0	   W
0	F	           F01	            P100	   0	   40	  WI


SELECT b.tmp_pm_code, b.tmp_desc, b.tmp_wt, b.tmp_qty, b.tmp_type,(select sum(a.tmp_qty) from ow_temp_data a
 where a.tmp_pm_code = b.tmp_pm_code and a.tmp_ps_code is not null) no_of_pcs
        FROM ow_temp_data b
       WHERE b.tmp_ps_code IS NULL;

TMP_PM_CODE	TMP_DESC	TMP_WT	TMP_QTY	TMP_TYPE	no_of_pcs
A	         H170	          25	  0	  W	         10
B	         H170	          25	  0	  W	         10
C	         HB120	          25	  0	  W	         10
D	         HB120	           5	  0	  W	         90
E	         PL120	          30	  0	  W	         10
F	         PL120	          35	  0	  W	         90


--the output what i want is
TMP_NO	TMP_PM_CODE	TMP_PS_CODE	TMP_DESC	TMP_WT	TMP_QTY	TMP_TYPE
0001	A	           NULL	            H170	  25	   0	   W  --
0001	A	           A01	           HEA100	   0	  10	  WI
0001	B	           NULL             H170	  25	   0	   W -- same profile and wt reaches 50 here
0001	B	           B01	          HEA100	   0  	  10	  WI

0002	C	          NULL	           HB120	  25       0	   W
0002	C	           C01	           HB100	   0	  10	  WI
0003	D	          NULL	           HB120	   5	   0	   W  -- new batch number as the tmp_qty more than 50 pcs
0003	D	           D01	           HB100	   0	   40	  WI

0004	E	          NULL	           PL120	  30	    0	   W
0004	E	           E01	            P100	   0	   10	  WI
0005	F	          NULL	           PL120	  35	    0	   W
0005	F	           F01	            P100	   0	   40	  WI

--i tried taking into a temporary table called os_batch to assign this but i can manage to get only the weight criteria.

CREATE TABLE OS_BATCH
(
  OB_BATCH    VARCHAR2(12 BYTE),
  OB_PM_CODE  VARCHAR2(12 BYTE),
  OB_DESC     VARCHAR2(30 BYTE),
  OB_WT       NUMBER,
  OB_QTY      NUMBER,
  OB_TYPE     VARCHAR2(2 BYTE)
);



/* Formatted on 2013/04/06 14:30 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c1
   IS
      SELECT tmp_pm_code, tmp_desc, tmp_wt, tmp_qty, tmp_type
        FROM ow_temp_data
       WHERE tmp_ps_code IS NULL;

   CURSOR c2
   IS
      SELECT NVL (MAX (TO_NUMBER (NVL (ob_batch, '0')) + 1), 0) batch_no
        FROM os_batch;

   m_prv_section    VARCHAR2 (30) := NULL;
   tsum             NUMBER        := 0;
   tsum1            NUMBER;
   batch_cnt        NUMBER;
   batch_val        VARCHAR2 (12);
   m_rec_planning   NUMBER        := 0;
BEGIN
   IF c2%ISOPEN
   THEN
      CLOSE c2;
   END IF;

   OPEN c2;

   FETCH c2
    INTO batch_cnt;
   CLOSE c2;

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

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

      IF    (tsum > 50 OR m_prv_section <> i.tmp_desc 
         OR m_rec_planning = c1%ROWCOUNT)
      THEN
         tsum1 := tsum;
      END IF;
      
      
     IF NVL (batch_cnt, 0) <> 0   
     THEN
      IF NVL (batch_cnt, 0) <= 9  
      THEN
         batch_val := '000' || batch_cnt;
      ELSIF batch_cnt BETWEEN 10 AND 99
      THEN
         batch_val := '00' || batch_cnt;
      ELSIF batch_cnt BETWEEN 100 AND 999
      THEN
         batch_val := '0' || batch_cnt;
      ELSIF batch_cnt > 999
      THEN
         batch_val := batch_cnt;
      END IF;
   ELSIF NVL (batch_cnt, 0) = 0
   THEN
      batch_cnt := 1;
      batch_val := '0001';
   END IF;


      IF (tsum > 50 OR m_prv_section <> i.tmp_desc)
      THEN
         tsum := 0;
         batch_cnt := batch_cnt + 1;

         IF batch_cnt < 10
         THEN
            batch_val := '000' || batch_cnt;
         ELSIF batch_cnt BETWEEN 10 AND 99
         THEN
            batch_val := '00' || batch_cnt;
         ELSIF batch_cnt BETWEEN 100 AND 999
         THEN
            batch_val := '0' || batch_cnt;
         ELSIF batch_cnt > 999
         THEN
            batch_val := batch_cnt;
         END IF;
      END IF;

      INSERT INTO os_batch
                  (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty,
                   ob_type
                  )
           VALUES (batch_val, i.tmp_pm_code, i.tmp_desc, i.tmp_wt, i.tmp_qty,
                   i.tmp_type
                  );

      tsum1 := NULL;
      m_prv_section := i.tmp_desc;
   END LOOP;
END;
/

commit;

-- i am getting it as below.
OB_BATCH	OB_PM_CODE	OB_DESC	OB_WT	OB_QTY	OB_TYPE
0001	           A	          H170	 25	0	W
0001	           B	          H170 	 25	0	W
0002	           C	         HB120	 25	0	W
0002	           D	         HB120	  5	0	W
0003	           E	         PL120	 30	0	W
0003	           F	         PL120	 35	0	W













Re: Complex batch allocation logic [message #581443 is a reply to message #581429] Sun, 07 April 2013 12:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
I don't understand your requirements and suspect I am not alone. Your examples don't seem to match your description. In one place you say:

b) Each batch number can accomodate not more than 10 pcs.

yet in other places you say more than 50 pcs.
Re: Complex batch allocation logic [message #581478 is a reply to message #581443] Sun, 07 April 2013 23:30 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks very much for the response mam, will explain it again, actually i am generating the batch number or you can say serial number based on tmp_desc with two criterias of adding up the tmp_qty and adding up tmp_wt as follows.
Criteria for generating batch number is tmp_qty should not exceed 10 per each batch and tmp_wt should not exceed 50 each batch
This i am doing it manually now by the query given below in the test case and using excel splitting it.

Conditions
a) New batch number will be generated with max(batch)+1 from os_batch for every combination of tmp_Desc and till tmp_wt reaches 50 and till tmp_qty reaches 10, in simple each batch must not contain tmp_wt more than 50 and tmp_qty not more than 10 per each tmp_desc.
b) if weight exceed more than 50 for example if its 60 and qty is 10 , two batches will be created with weight as 30 30 each
as the wt criteria is more and it takes the priority.
c) if wight is ok that is less than 50 like 25 and qty is 50 then 50/10 that is 5 batches will be created with weight of batch being 5 and qty being 10.
d) if both the cases come together like wt is 60 and qty is 50 , priority goes to qty and it will 50/10 -- 5 batches with 60/5 wt per each batch.



insert into ow_temp_data (tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','A' , NULL,'H170',25 ,0,'W' );
insert into ow_temp_data ( tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','A' ,'A01' ,'HEA100' ,0 ,10,'WI' );
insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','B' , NULL,'H170',25 ,0,'W' );
insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','B' ,'B01' ,'HEA100' ,0 ,10,'WI' );
insert into ow_temp_data (tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','C' , NULL,'HB120',30 ,0,'W' );
insert into ow_temp_data ( tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','C' ,'C01' ,'HB100' ,0 ,10,'WI' );
insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','D' , NULL,'HB120',40 ,0,'W' );
insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','D' ,'D01' ,'HB100' ,0 ,10,'WI' );
insert into ow_temp_data (tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','E' , NULL,'PL120',30 ,0,'W' );
insert into ow_temp_data ( tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','E' ,'E01' ,'P100' ,0 ,10,'WI' );
insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','F' , NULL,'PL120',35 ,0,'W' );
insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','F' ,'F01' ,'P100' ,0 ,50,'WI' );

commit;


--query on which criterias to be applied and serial numbers to be generated.

select TMP_PM_CODE,TMP_DESC,TMP_WT, att TMP_QTY,TMP_TYPE
     from
       (
        SELECT b.tmp_pm_code tmp_pm_code, b.tmp_desc tmp_desc, b.tmp_wt tmp_wt,
          b.tmp_qty tmp_qty, b.tmp_type tmp_type,tmp_ps_code,
               SUM (b.tmp_qty) over(partition by b.tmp_pm_code) att
        FROM ow_temp_data b
      )
       WHERE tmp_ps_code IS NULL;


TMP_PM_CODE	TMP_DESC	TMP_WT	TMP_QTY	TMP_TYPE
A	         H170	           25	 10	W       --this line and second line will go to batch 0001 as tmp_wt reaches 50
B	         H170	           25	 10	W       --0001  
C	         HB120	           30	 10	W       --this will go into two batches as tmp_wt reaches 70 and                  divided by 2 and two batches for C AND D --0003 
D	         HB120	           40	 10	W     --0004
E	         PL120	           30	 10	W      --In this case Both qty and wt are crossing their limits and this case will
have total of 6 batch numbers since tmp_qty becomes 60 here and it has to be splitted into each batch with qty as 10.
F	         PL120	           35	 50	W



--the output i want in os_batch table is as follows.

CREATE TABLE OS_BATCH
(
  OB_BATCH    VARCHAR2(12 BYTE),
  OB_PM_CODE  VARCHAR2(12 BYTE),
  OB_DESC     VARCHAR2(30 BYTE),
  OB_WT       NUMBER,
  OB_QTY      NUMBER,
  OB_TYPE     VARCHAR2(2 BYTE)
)

--the following is the result data i want in os_batch table.

ob_batch ob_pm_code ob_desc  ob_wt   ob_qty  ob_type
0001      A          H170      25     10      W
0001      B          H170      25     10      W 
0002      C         HB120      35     10      W
0003      D         HB120      35     10      W
0004      E         PL120   10.83     10      W
0005      F         PL120   10.83     10      W
0006      F         PL120   10.83     10      W
0007      F         PL120   10.83     10      W
0008      F         PL120   10.83     10      W
0009      F         PL120   10.83     10      W

[Updated on: Sun, 07 April 2013 23:41]

Report message to a moderator

Re: Complex batch allocation logic [message #581487 is a reply to message #581443] Mon, 08 April 2013 04:04 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

tmp_qty <= 10
and tmp_wt <=50
Re: Complex batch allocation logic [message #581544 is a reply to message #581487] Mon, 08 April 2013 14:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
Your explanation still does not match your desired results in various ways. For example, you say, "each batch must not contain tmp_wt more than 50 and tmp_qty not more than 10 per each tmp_desc", but your first batch contains 10 tmp_qty for tmp_pm_code A and 10 tmp_qty for tmp_pm_code B for a total of 20 tmp_qty for tmp_desc H170. Conversely, there is no rationale for dividing F into 5 batches, instead of less. So, the following matches your posted desired results, instead of your explanation. There may be ways to simplify this.

SCOTT@orcl_11gR2> set	 null NULL
SCOTT@orcl_11gR2> column tmp_desc format a9
SCOTT@orcl_11gR2> column tmp_type format a8
SCOTT@orcl_11gR2> select * from ow_temp_data order by tmp_pm_code, tmp_ps_code desc
  2  /

TMP_NO       TMP_PM_CODE  TMP_PS_CODE  TMP_DESC      TMP_WT    TMP_QTY TMP_TYPE
------------ ------------ ------------ --------- ---------- ---------- --------
0            A            NULL         H170              25          0 W
0            A            A01          HEA100             0         10 WI
0            B            NULL         H170              25          0 W
0            B            B01          HEA100             0         10 WI
0            C            NULL         HB120             30          0 W
0            C            C01          HB100              0         10 WI
0            D            NULL         HB120             40          0 W
0            D            D01          HB100              0         10 WI
0            E            NULL         PL120             30          0 W
0            E            E01          P100               0         10 WI
0            F            NULL         PL120             35          0 W
0            F            F01          P100               0         50 WI

12 rows selected.

SCOTT@orcl_11gR2> select * from os_batch
  2  /

no rows selected

SCOTT@orcl_11gR2> declare
  2    v_batch	number;
  3    v_wt	number := 0;
  4    v_desc	varchar2 (30 byte);
  5    v_col	number;
  6  begin
  7    select nvl (max (to_number (nvl (ob_batch, '0'))), 0)
  8    into   v_batch
  9    from   os_batch;
 10    for c1 in
 11  	 (select tmp_pm_code, tmp_desc,
 12  		 sum_tmp_wt / pieces wt,
 13  		 sum_tmp_qty / pieces qty,
 14  		 tmp_type, column_value
 15  	  from	 (select tmp_pm_code, tmp_desc, sum_tmp_wt,
 16  			 sum (tmp_qty) over (partition by tmp_desc) sum_tmp_qty,
 17  			 ceil (sum (tmp_qty) over (partition by tmp_desc) / 10) pieces,
 18  			 tmp_type, tmp_qty
 19  		  from	 (select tmp_pm_code, tmp_desc,
 20  				 sum (tmp_wt) over (partition by tmp_desc) sum_tmp_wt,
 21  				 sum (tmp_qty) over (partition by tmp_pm_code) tmp_qty,
 22  				 tmp_type, tmp_ps_code
 23  			  from	 ow_temp_data)
 24  		  where  tmp_ps_code is null) t,
 25  		 table
 26  		   (cast
 27  		      (multiset
 28  			 (select level
 29  			  from	 dual
 30  			  connect by level <= ceil (tmp_qty / 10))
 31  		       as sys.odcinumberlist))
 32  	  order  by tmp_pm_code, column_value desc)
 33    loop
 34  	 v_wt := v_wt + c1.wt;
 35  	 if v_wt > 50 or
 36  	    v_desc is null or c1.tmp_desc != v_desc or
 37  	    v_col is null or c1.column_value != v_col then
 38  	   v_batch := v_batch + 1;
 39  	   v_wt := c1.wt;
 40  	   v_desc := c1.tmp_desc;
 41  	   v_col := c1.column_value;
 42  	 end if;
 43  	 insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty, ob_type)
 44  	 values (lpad (v_batch, 4, '0'), c1.tmp_pm_code, c1.tmp_desc, c1.wt, c1.qty, c1.tmp_type);
 45    end loop;
 46  end;
 47  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> column ob_desc  format a9
SCOTT@orcl_11gR2> column ob_type  format a7
SCOTT@orcl_11gR2> select * from os_batch order by ob_batch, ob_pm_code
  2  /

OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE
------------ ------------ --------- ---------- ---------- -------
0001         A            H170              25         10 W
0001         B            H170              25         10 W
0002         C            HB120             35         10 W
0003         D            HB120             35         10 W
0004         E            PL120     10.8333333         10 W
0005         F            PL120     10.8333333         10 W
0006         F            PL120     10.8333333         10 W
0007         F            PL120     10.8333333         10 W
0008         F            PL120     10.8333333         10 W
0009         F            PL120     10.8333333         10 W

10 rows selected.

[Updated on: Mon, 08 April 2013 14:59]

Report message to a moderator

Re: Complex batch allocation logic [message #581557 is a reply to message #581544] Mon, 08 April 2013 23:22 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks very much for your help , i will explain it properly again its my mistake of not defining things properly and giving the wrong test case for the H170 desc actually it has to be 5 per each then it will be in one batch and program will be more simplified too.This is a very complex program.

Quote:

"each batch must not contain tmp_wt more than 50 and tmp_qty not more than 10 per each tmp_desc", but your first batch contains 10 tmp_qty for tmp_pm_code A and 10 tmp_qty for tmp_pm_code B for a total of 20 qty for tmp_desc H170.


sorry i gave a wrong example of 10 per each instead it has to be 5 per each , i will present the case again.Extremely sorry.


create table ow_temp_data ( tmp_no varchar2(12),tmp_pm_code varchar2(12),tmp_ps_code varchar2(12),tmp_desc varchar2(30),tmp_wt number ,tmp_qty number,TMP_TYPE VARCHAR2(2) );

insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','A' , NULL,   'H170',25  ,0,'W'  );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','A' ,'A01' ,'HEA100' ,0 , 5,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','B' , NULL,   'H170',25 , 0,'W'  );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','B' ,'B01' ,'HEA100' ,0 , 5,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','C' , NULL,'  HB120',30 , 0, 'W' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','C' ,'C01' , 'HB100' ,0 , 5,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','D' , NULL,'HB120',  40 , 0, 'W' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','D' ,'D01' ,'HB100'  ,0 , 5,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','E' , NULL,'PL120',  30  ,0,'W ' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','E' ,'E01' ,'P100'   ,0 ,10,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','F' , NULL,'PL120',  35 , 0,'W'  );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','F' ,'F01' ,'P100'   ,0 ,50,'WI' );


 1   declare
  2        v_batch	number;
  3        v_wt	number := 0;
  4        v_desc	varchar2 (30 byte);
  5        v_col	number;
  6      begin
  7        select nvl (max (to_number (nvl (ob_batch, '0'))), 0)
  8        into   v_batch
  9        from   os_batch;
 10       for c1 in
 11     	 (select tmp_pm_code, tmp_desc,
 12     		 sum_tmp_wt / pieces wt,
 13     		 sum_tmp_qty / pieces qty,
 14     		 tmp_type, column_value
 15     	  from	 (select tmp_pm_code, tmp_desc, sum_tmp_wt,
 16     			 sum (tmp_qty) over (partition by tmp_desc) sum_tmp_qty,
 17     			 ceil (sum (tmp_qty) over (partition by tmp_desc) / 10) pieces,
 18    			 tmp_type, tmp_qty
 19     		  from	 (select tmp_pm_code, tmp_desc,
 20     				 sum (tmp_wt) over (partition by tmp_desc) sum_tmp_wt,
 21     				 sum (tmp_qty) over (partition by tmp_pm_code) tmp_qty,
 22     				 tmp_type, tmp_ps_code
 23     			  from	 ow_temp_data)
 24     		  where  tmp_ps_code is null) t,
 25     		 table
 26     		   (cast
 27     		      (multiset
 28     			 (select level
 29     			  from	 dual
 30     			  connect by level <= ceil (tmp_qty / 10))
 31     		       as sys.odcinumberlist))
 32     	  order  by tmp_pm_code, column_value desc)
 33       loop
 34     	 v_wt := v_wt + c1.wt;
 35     	 if v_wt > 50 or
 36     	    v_desc is null or c1.tmp_desc != v_desc or
 37     	    v_col is null or c1.column_value != v_col then
 38     	   v_batch := v_batch + 1;
 39     	   v_wt := c1.wt;
 40     	   v_desc := c1.tmp_desc;
 41     	   v_col := c1.column_value;
 42     	 end if;
 43     	 insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty, ob_type)
 44     	 values (lpad (v_batch, 4, '0'), c1.tmp_pm_code, c1.tmp_desc, c1.wt, c1.qty, c1.tmp_type);
 45       end loop;
 46*    end;
 47  /

PL/SQL procedure successfully completed.



SQL> commit;

Commit complete.

SQL> select * from os_batch;

OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE               
------------ ------------ --------- ---------- ---------- -------               
0001         A            H170              50         10 W                     
0002         B            H170              50         10 W                     
0003         C              HB120           30          5 W                     
0004         D            HB120             40          5 W                     
0005         E            PL120     10.8333333         10 W                     
0006         F            PL120     10.8333333         10 W                     
0007         F            PL120     10.8333333         10 W                     
0008         F            PL120     10.8333333         10 W                     
0009         F            PL120     10.8333333         10 W                     
0010         F            PL120     10.8333333         10 W                     

10 rows selected.

--Desired result what i want is as follows

SQL> select * from os_batch;

OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE               
------------ ------------ --------- ---------- ---------- -------               
0001         A            H170              25          5 W                     
0001         B            H170              25          5 W --it reaches 50 here and it should stop with batch 0001             
0002         C            HB120             35          5 W                     
0003         D            HB120             35          5 W  --Since it has 70 it will get divided equally into 2 batches   as 35 each adding two and / 2                   
0005         E            PL120     10.8333333         10 W  -- this is correct as both the conditions are not satisfying.                   
0006         F            PL120     10.8333333         10 W                     
0007         F            PL120     10.8333333         10 W                     
0008         F            PL120     10.8333333         10 W                     
0009         F            PL120     10.8333333         10 W                     
0010         F            PL120     10.8333333         10 W  


Quote:

Conversely, there is no rationale for dividing F into 5 batches, instead of less.


--for PL 120 both the conditions are failing that is 65 and 60 pcs so the precedence is given to qty and rationale is 60/10 = 6 batches will be created
--and both qty and wt will be evenly distributed.

--In simple terms , New batch number will be generated based on two conditions , for tmp_desc the tmp_wt should not be more than 50 and tmp_qty should not be more than 10, if it tmp_wt crosess 50 new batch number will created and tmp_wt will be evenly distributed into two batches, if the tmp_qty is more than 10 for example if its 40 and still the tmp_wt is below 50, then tmp_wt will get evenly distributed into 4 batches. I hope i have explained it correctly this time.

THanks and Regards
Arif


[mod-edit: code tags fixed by bb]

[Updated on: Mon, 08 April 2013 23:45] by Moderator

Report message to a moderator

Re: Complex batch allocation logic [message #581559 is a reply to message #581557] Mon, 08 April 2013 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
how SAD! Sad
why do you continue to not format your posts to benefit those who try to provide answers?

if input data is like below, what should be the results (& why)?

insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','C' , NULL,' HB120',81 , 0, 'W' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','D' , NULL,'HB120', 90 , 0, 'W' );
Re: Complex batch allocation logic [message #581561 is a reply to message #581557] Mon, 08 April 2013 23:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
If you want 5 instead of 10, then just change 10 to 5 in the code that I provided. You also need to remove the extra spaces in front of one of your values of HB120. However, if you want 5 instead of 10, then why do you still show 10 in your desired results?
Re: Complex batch allocation logic [message #581565 is a reply to message #581561] Tue, 09 April 2013 00:25 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Quote:

If you want 5 instead of 10, then just change 10 to 5 in the code that I provided. You also need to remove the extra spaces in front of one of your values of HB120. However, if you want 5 instead of 10, then why do you still show 10 in your desired results?



--i will explain ,this is very important , if the qty is less than 50 per each tmp_pm_code in each tmp_desc then it has to be as its and get added till it reaches 50 and will have same batch number until it reaches 50 or tmp_qty reaches 10.i dont want to change it in program from 10 to 5,its like New batch number will be generated based on two conditions , for tmp_desc the tmp_wt should not be more than 50 and tmp_qty should not be more than 10, if it tmp_wt crosess 50 new batch number will created and tmp_wt will be evenly distributed into two batches, if the tmp_qty is more than 10 for example if its 40 and still the tmp_wt is below 50, then tmp_wt will get evenly distributed into 4 batches. I hope i have explained it correctly this time.





delete from ow_temp_data

insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','A' , NULL,   'H170',25  ,0,'W'  );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','A' ,'A01' ,'HEA100' ,0 , 5,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','B' , NULL,   'H170',25 , 0,'W'  );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','B' ,'B01' ,'HEA100' ,0 , 5,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','C' , NULL,'HB120',30 , 0, 'W' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','C' ,'C01' , 'HB100' ,0 , 5,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','D' , NULL,'HB120',  40 , 0, 'W' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','D' ,'D01' ,'HB100'  ,0 , 5,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','E' , NULL,'PL120',  30  ,0,'W ' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','E' ,'E01' ,'P100'   ,0 ,10,'WI' );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','F' , NULL,'PL120',  35 , 0,'W'  );
insert into ow_temp_data (tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt ,tmp_qty ,tmp_type ) values ('0','F' ,'F01' ,'P100'   ,0 ,50,'WI' );


delete from os_batch;

commit;

SQL> declare
  2       v_batch      number;
  3       v_wt number := 0;
  4       v_desc       varchar2 (30 byte);
  5       v_col        number;
  6     begin
  7       select nvl (max (to_number (nvl (ob_batch, '0'))), 0)
  8       into   v_batch
  9       from   os_batch;
 10      for c1 in
 11             (select tmp_pm_code, tmp_desc,
 12                     sum_tmp_wt / pieces wt,
 13                     sum_tmp_qty / pieces qty,
 14                     tmp_type, column_value
 15              from   (select tmp_pm_code, tmp_desc, sum_tmp_wt,
 16                             sum (tmp_qty) over (partition by tmp_desc) sum_tmp_qty,
 17                             ceil (sum (tmp_qty) over (partition by tmp_desc) / 10) pieces,
 18                     tmp_type, tmp_qty
 19                      from   (select tmp_pm_code, tmp_desc,
 20                                     sum (tmp_wt) over (partition by tmp_desc) sum_tmp_wt,
 21                                     sum (tmp_qty) over (partition by tmp_pm_code) tmp_qty,
 22                                     tmp_type, tmp_ps_code
 23                              from   ow_temp_data)
 24                      where  tmp_ps_code is null) t,
 25                     table
 26                       (cast
 27                          (multiset
 28                             (select level
 29                              from   dual
 30                              connect by level <= ceil (tmp_qty / 10))
 31                           as sys.odcinumberlist))
 32              order  by tmp_pm_code, column_value desc)
 33      loop
 34             v_wt := v_wt + c1.wt;
 35             if v_wt > 50 or
 36                v_desc is null or c1.tmp_desc != v_desc or
 37                v_col is null or c1.column_value != v_col then
 38               v_batch := v_batch + 1;
 39               v_wt := c1.wt;
 40               v_desc := c1.tmp_desc;
 41               v_col := c1.column_value;
 42             end if;
 43             insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty, ob_type)
 44             values (lpad (v_batch, 4, '0'), c1.tmp_pm_code, c1.tmp_desc, c1.wt, c1.qty, c1.tmp_type);
 45      end loop;
 46    end;
 47  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from os_batch;

--result i am getting

OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE
------------ ------------ --------- ---------- ---------- -------
0001         A            H170              50         10 W  --- i dont 10 to be defaulted and qty to be same if its less than 50
0002         B            H170              50         10 W
0003         C            HB120             70         10 W
0004         D            HB120             70         10 W
0005         E            PL120     10.8333333         10 W
0006         F            PL120     10.8333333         10 W
0007         F            PL120     10.8333333         10 W
0008         F            PL120     10.8333333         10 W
0009         F            PL120     10.8333333         10 W
0010         F            PL120     10.8333333         10 W



--result what i want is 
OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE
------------ ------------ --------- ---------- ---------- -------
0001         A            H170              25          5 W
0001         B            H170              25          5 W   --if it less qty will be in the same batch till it gets 50
0003         C            HB120             35          5 W
0004         D            HB120             35          5 W
0005         E            PL120     10.8333333         10 W
0006         F            PL120     10.8333333         10 W
0007         F            PL120     10.8333333         10 W
0008         F            PL120     10.8333333         10 W
0009         F            PL120     10.8333333         10 W
0010         F            PL120     10.8333333         10 W


--it should be evenly distributed based on two conditions if the tmp_wt more than 50 and tmp_qty less than or equal to 10 per tmp_desc  .For example if its 70 it needs get divided by 2 (70/2=35) since 35 is lesser than 50 for tmp_desc, if tmp_wt is 110, then it shoud be 110/3 becuase if we divide it by 2 it becomes 55 so that exceeding out batch capacity so it has to be divided into 3 and program must check this per tmp_desc and for tmp_qty it should not be more than 10 at any point.








Re: Complex batch allocation logic [message #581567 is a reply to message #581561] Tue, 09 April 2013 01:00 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

i will brief it again with more examples,simply we are batching or grouping all tmp_Desc together amd assigning the serials number as per tmp_desc/tmp_wt/tmp_qty combinations, and there are certain rules for that,
rule are explained with following examples.


rule1)

--if the data in ow_temp_data is like this then no need to change as its falling under limits of not exceeding more than 50 and tmp_qty < 10

tmp_pm_code tmp_ps_desc tmp_Wt tmp_qty
 A            'H100'    25      3
 B            'H100'    25      2

 --the resultant output in os_batch table will be 
ob_batch     ob_desc   ob_Wt  ob_qty
 0001          'H100'    25     3
 0001          'H100'    25     2



rule2)

-- if its 70 it needs get divided by 2 (70/2=35) since 35 is lesser than 50 for tmp_desc, if tmp_wt is 110, then it shoud be 110/3 becuase if we divide it by 2 it becomes 55 so that exceeding out batch capacity so it has to be divided into 3

data in ow_temp_data

tmp_pm_code tmp_ps_desc tmp_Wt tmp_qty
 A            'H100'    30      3
 B            'H100'    30      2
 C            'H100'    10      3

--the resultant output in os_batch table will be 
ob_batch     ob_desc   ob_Wt  ob_qty
 0001          'H100'    35      4
 0002          'H100'    35      4



rule3)

--data in ow_Temp_data
--in this case both qty and wt are more
tmp_pm_code tmp_ps_desc tmp_Wt tmp_qty
 A            'H100'    30      20
 B            'H100'    30      20 

--output will be like below

ob_batch     ob_desc   ob_Wt  ob_qty
 0001          'H100'    15     10
 0002          'H100'    15     10
 0003          'H100'    15     10
 0004          'H100'    15     10







Re: Complex batch allocation logic [message #581679 is a reply to message #581567] Tue, 09 April 2013 15:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> set	 null NULL
SCOTT@orcl_11gR2> column tmp_desc format a9
SCOTT@orcl_11gR2> column tmp_type format a8
SCOTT@orcl_11gR2> select * from ow_temp_data order by tmp_pm_code, tmp_ps_code desc
  2  /

TMP_NO       TMP_PM_CODE  TMP_PS_CODE  TMP_DESC      TMP_WT    TMP_QTY TMP_TYPE
------------ ------------ ------------ --------- ---------- ---------- --------
0            A            NULL         H170              25          0 W
0            A            A01          HEA100             0          5 WI
0            B            NULL         H170              25          0 W
0            B            B01          HEA100             0          5 WI
0            C            NULL         HB120             30          0 W
0            C            C01          HB100              0          5 WI
0            D            NULL         HB120             40          0 W
0            D            D01          HB100              0          5 WI
0            E            NULL         PL120             30          0 W
0            E            E01          P100               0         10 WI
0            F            NULL         PL120             35          0 W
0            F            F01          P100               0         50 WI

12 rows selected.

SCOTT@orcl_11gR2> select * from os_batch
  2  /

no rows selected

SCOTT@orcl_11gR2> declare
  2    v_batch	number;
  3    v_wt	number := 0;
  4    v_desc	varchar2 (30 byte);
  5    v_col	number;
  6  begin
  7    select nvl (max (to_number (nvl (ob_batch, '0'))), 0)
  8    into   v_batch
  9    from   os_batch;
 10    for c1 in
 11  	 (select tmp_pm_code, tmp_ps_code, tmp_desc,
 12  		 sum_wt/pieces wt, sum_qty/pieces qty,
 13  		 tmp_type, column_value
 14  	  from	 (select tmp_pm_code, tmp_ps_code, tmp_desc,
 15  			 sum_tmp_wt/cnt sum_wt, sum_tmp_qty/cnt sum_qty,
 16  			 ceil (greatest (sum_tmp_wt/50, sum_tmp_qty/10) / cnt) pieces,
 17  			 tmp_type,
 18  			 round ((tmp_qty/(sum_tmp_qty/cnt))
 19  			 * ceil (greatest (sum_tmp_wt/50, sum_tmp_qty/10) / cnt)) rs
 20  		  from	 (select tmp_pm_code, tmp_ps_code, tmp_desc,
 21  				 sum (tmp_wt) over (partition by tmp_desc) sum_tmp_wt,
 22  				 sum (tmp_qty) over (partition by tmp_desc) sum_tmp_qty,
 23  				 count (*) over (partition by tmp_desc) cnt,
 24  				 tmp_type, tmp_qty
 25  			  from	 (select a.tmp_pm_code, b.tmp_ps_code, a.tmp_desc,
 26  					 sum (a.tmp_wt) tmp_wt, sum (b.tmp_qty) tmp_qty,
 27  					 a.tmp_type
 28  				  from	 ow_temp_data a, ow_temp_data b
 29  				  where  a.tmp_pm_code = b.tmp_pm_code
 30  				  and	 a.tmp_ps_code is null
 31  				  and	 b.tmp_ps_code is not null
 32  				  group  by a.tmp_pm_code, b.tmp_ps_code,
 33  					    a.tmp_desc, a.tmp_type))) t,
 34  		 table
 35  		   (cast
 36  		      (multiset
 37  			 (select level
 38  			  from	 dual
 39  			  connect by level <= t.rs)
 40  		       as sys.odcinumberlist))
 41  	  order  by tmp_pm_code, column_value desc)
 42    loop
 43  	 v_wt := v_wt + c1.wt;
 44  	 if v_wt > 50 or
 45  	    v_desc is null or c1.tmp_desc != v_desc or
 46  	    v_col is null or c1.column_value != v_col then
 47  	   v_batch := v_batch + 1;
 48  	   v_wt := c1.wt;
 49  	   v_desc := c1.tmp_desc;
 50  	   v_col := c1.column_value;
 51  	 end if;
 52  	 insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty, ob_type)
 53  	 values (lpad (v_batch, 4, '0'), c1.tmp_pm_code, c1.tmp_desc, c1.wt, c1.qty, c1.tmp_type);
 54    end loop;
 55  end;
 56  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> column ob_desc  format a9
SCOTT@orcl_11gR2> column ob_type  format a7
SCOTT@orcl_11gR2> select * from os_batch order by ob_batch, ob_pm_code
  2  /

OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE
------------ ------------ --------- ---------- ---------- -------
0001         A            H170              25          5 W
0001         B            H170              25          5 W
0002         C            HB120             35          5 W
0003         D            HB120             35          5 W
0004         E            PL120     10.8333333         10 W
0005         F            PL120     10.8333333         10 W
0006         F            PL120     10.8333333         10 W
0007         F            PL120     10.8333333         10 W
0008         F            PL120     10.8333333         10 W
0009         F            PL120     10.8333333         10 W

10 rows selected.

[Updated on: Tue, 09 April 2013 15:59]

Report message to a moderator

icon14.gif  Re: Complex batch allocation logic [message #581686 is a reply to message #581679] Tue, 09 April 2013 23:19 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Many many thanks mam, this is what exactly i want.You are just amazing,awesome.I would like to appreciate your goodself mam, and the great work you, michael,littlefoot and cookiemonster are doing for this site by helping me and others .Keep the good work.Million thanks.
Re: Complex batch allocation logic [message #582152 is a reply to message #581679] Sun, 14 April 2013 12:01 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Sorry for disturbing you again , when i used different sets of data to accomplish the task using the given procedure by changing the wieght criteria from 50 to 50000 and from qty 10 to 1000, i am not getting the desired output.I even tried changing the analytical query but still not able to figure out whats going wrong.Please help.


Please find below the test case.


CREATE TABLE OW_TEMP_DATA
(
  TMP_NO       VARCHAR2(12 BYTE),
  TMP_PM_CODE  VARCHAR2(12 BYTE),
  TMP_PS_CODE  VARCHAR2(12 BYTE),
  TMP_DESC     VARCHAR2(30 BYTE),
  TMP_WT       NUMBER,
  TMP_QTY      NUMBER,
  TMP_TYPE     VARCHAR2(2 BYTE)
);




CREATE TABLE OS_BATCH
(
  OB_BATCH    VARCHAR2(12 BYTE),
  OB_PM_CODE  VARCHAR2(12 BYTE),
  OB_DESC     VARCHAR2(30 BYTE),
  OB_WT       NUMBER,
  OB_QTY      NUMBER,
  OB_TYPE     VARCHAR2(2 BYTE)
);





insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A',NULL,'H170',28000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A','H1701',NULL,0,650,'W');
Insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A','W166',NULL,0,100,'W');
Insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A','PL1',NULL,0,100,'P');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','B',NULL,'H170',23000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','B','W266',NULL,0,50,'W');
Insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','B','L100',NULL,0,50,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','C',NULL,'IPE100',22000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','C','PL12',NULL,0,5000,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','D',NULL,'IPE200',22000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','D','PL12',NULL,0,100,'W');


commit;

--program

declare 
v_batch number;
v_wt number :=0;
v_desc varchar2(30);
v_col number;

begin

select nvl(max(to_number(nvl(ob_batch,'0'))),0)
into v_batch from os_batch;

for c1 in
(SELECT tmp_pm_code,tmp_ps_code,tmp_desc,sum_wt/pieces wt,tmp_wt, sum_qty/pieces qty,tmp_qty,tmp_type,column_value from(
select tmp_pm_code, tmp_ps_code, tmp_desc,
                sum_tmp_wt/cnt sum_wt, sum_tmp_qty/cnt sum_qty,tmp_qty,tmp_wt,
                ceil (greatest (sum_tmp_wt/50000, sum_tmp_qty/1000) / cnt) pieces,
                tmp_type,
                ceil ((tmp_qty/(sum_tmp_qty/cnt))
                 * ceil (greatest (sum_tmp_wt/50000, sum_tmp_qty/1000) / cnt)) rs
                from
(select tmp_pm_code, tmp_ps_code, tmp_desc,
                    sum (tmp_wt) over (partition by tmp_desc) sum_tmp_wt,
                    sum (tmp_qty) over (partition by tmp_desc) sum_tmp_qty,
                    count (*) over (partition by tmp_desc) cnt,
                    tmp_type, tmp_qty,tmp_wt
                    from     (select a.tmp_pm_code, b.tmp_ps_code, a.tmp_desc,
                              sum (a.tmp_wt) tmp_wt, sum (b.tmp_qty) tmp_qty,
                             a.tmp_type
                      from     ow_temp_data a, ow_temp_data b
                     where  a.tmp_pm_code = b.tmp_pm_code
                       and     a.tmp_ps_code is null
                       and     b.tmp_ps_code is not null
                       and     b.tmp_type = 'W' --ps_code not null should only consider the sum of qty where tmp_type is 'W'
                      group  by a.tmp_pm_code, b.tmp_ps_code,
                           a.tmp_desc, a.tmp_type order by 1))) t,table (cast(multiset(select level from dual connect by level < t.rs) as sys.odcinumberlist)))
loop
v_wt := v_wt + c1.wt;
if v_wt > 50000 or
v_desc is null or c1.tmp_Desc != v_desc or
v_col is null or c1.column_value != v_col then
v_batch := v_batch+1;
v_wt := c1.wt;
v_desc := c1.tmp_desc;
v_col := c1.column_value;
end if;
insert into os_batch (ob_batch,ob_pm_code,ob_desc,ob_wt,ob_qty,ob_type)
values (lpad(v_batch,4,'0'),c1.tmp_pm_code,c1.tmp_desc,c1.wt,c1.qty,c1.tmp_type);
end loop;
end;    


--the output i am getting is

OB_BATCH	OB_PM_CODE	OB_DESC	OB_WT	OB_QTY	OB_TYPE
0001	           A	         H170	25500	212.5	  W
0002	           A	         H170	25500	212.5	  W
0003	           A	         H170	25500	212.5	  W
0004	           A	         H170	25500	212.5	  W
0005	           B	         H170	25500	212.5	  W
0006	           B	         H170	25500	212.5	  W
0007	           C	       IPE100	 4400	 1000	  W
0008	           C	       IPE100	 4400	 1000	  W
0009	           C	       IPE100	 4400	 1000	  W
0010	           C	       IPE100	 4400	 1000	  W
0011	           D	       IPE200	22000	  100	  W


-- the output should be as per A the wieght is more than 50,000 but qty is still less than 1000 hence it has to be divided into two batches to accomodate the weight and qty equally as below.


OB_BATCH	OB_PM_CODE	OB_DESC	OB_WT	OB_QTY	OB_TYPE
0001	           A	         H170	25500	  425	  W
0002	           B	         H170	25500	  425	  W
0003	           C	       IPE100	 4400	 1000	  W
0004	           C	       IPE100	 4400	 1000	  W
0005	           C	       IPE100	 4400	 1000	  W
0006	           C	       IPE100	 4400	 1000	  W
0007	           D	       IPE200	22000	  100	  W

Re: Complex batch allocation logic [message #582159 is a reply to message #582152] Sun, 14 April 2013 16:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
The following works for the test cases presented thus far, but may not work for other unanticipated cases, so you may need to make further modifications.

SCOTT@orcl_11gR2> set	 null NULL
SCOTT@orcl_11gR2> column tmp_desc format a9
SCOTT@orcl_11gR2> column tmp_type format a8
SCOTT@orcl_11gR2> select * from ow_temp_data order by tmp_pm_code, tmp_ps_code desc
  2  /

TMP_NO       TMP_PM_CODE  TMP_PS_CODE  TMP_DESC      TMP_WT    TMP_QTY TMP_TYPE
------------ ------------ ------------ --------- ---------- ---------- --------
0            A            NULL         H170           28000          0 W
0            A            W166         NULL               0        100 W
0            A            PL1          NULL               0        100 P
0            A            H1701        NULL               0        650 W
0            B            NULL         H170           23000          0 W
0            B            W266         NULL               0         50 W
0            B            L100         NULL               0         50 W
0            C            NULL         IPE100         22000          0 W
0            C            PL12         NULL               0       5000 W
0            D            NULL         IPE200         22000          0 W
0            D            PL12         NULL               0        100 W

11 rows selected.

SCOTT@orcl_11gR2> select * from os_batch
  2  /

no rows selected

SCOTT@orcl_11gR2> declare
  2    v_batch	number;
  3    v_wt	number := 0;
  4    v_desc	varchar2 (30 byte);
  5    v_col	number;
  6  begin
  7    select nvl (max (to_number (nvl (ob_batch, '0'))), 0)
  8    into   v_batch
  9    from   os_batch;
 10    for c1 in
 11  	 (select tmp_pm_code, tmp_desc, tmp_type, wt, qty, adj_pieces, column_value
 12  	  from	 (select f.*,
 13  			 decode (pieces, 0, decode (lead_pieces, 0, 0, 1),
 14  					    decode (lag_pieces, 0, pieces - 1, pieces)) adj_pieces
 15  		  from	 (select e.*,
 16  				 lag (pieces) over (partition by tmp_desc order by pieces) lag_pieces,
 17  				 lead (pieces) over (partition by tmp_desc order by pieces) lead_pieces
 18  			  from	 (select d.*,
 19  					 round ((tmp_qty/sum_tmp_qty) * tot_pieces) pieces
 20  				  from	 (select c.*,
 21  						 sum_tmp_wt/tot_pieces wt, sum_tmp_qty/tot_pieces qty
 22  					  from	 (select b.*,
 23  							 ceil
 24  							   (greatest
 25  							      ((sum_tmp_wt/50000000),
 26  							       (sum_tmp_qty/1000), cnt)) tot_pieces
 27  						  from	 (select a.*,
 28  								 sum (tmp_wt) over
 29  								   (partition by tmp_desc) sum_tmp_wt,
 30  								 sum (tmp_qty) over
 31  								   (partition by tmp_desc) sum_tmp_qty,
 32  								 count (*) over
 33  								   (partition by tmp_desc) cnt
 34  							  from	 (select tmp_pm_code,
 35  									 max
 36  									   (decode
 37  									      (tmp_ps_code,
 38  									       null, tmp_desc,
 39  									       null)) tmp_desc,
 40  									 max
 41  									   (decode
 42  									      (tmp_ps_code,
 43  									       null, tmp_type,
 44  									       null)) tmp_type,
 45  									 sum (tmp_wt) tmp_wt,
 46  									 sum (tmp_qty) tmp_qty
 47  								  from	 ow_temp_data
 48  								  group  by tmp_pm_code) a) b) c) d) e) f) t,
 49  		 table
 50  		   (cast
 51  		      (multiset
 52  			 (select level
 53  			  from	 dual
 54  			  connect by level <= t.adj_pieces)
 55  		       as sys.odcinumberlist))
 56  	  order  by tmp_pm_code, column_value desc)
 57    loop
 58  	 v_wt := v_wt + c1.wt;
 59  	 if v_wt > 50000 or
 60  	    v_desc is null or c1.tmp_desc != v_desc or
 61  	    v_col is null or c1.column_value != v_col then
 62  	   v_batch := v_batch + 1;
 63  	   v_wt := c1.wt;
 64  	   v_desc := c1.tmp_desc;
 65  	   v_col := c1.column_value;
 66  	 end if;
 67  	 insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty, ob_type)
 68  	 values (lpad (v_batch, 4, '0'), c1.tmp_pm_code, c1.tmp_desc, c1.wt, c1.qty, c1.tmp_type);
 69    end loop;
 70  end;
 71  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> column ob_desc  format a9
SCOTT@orcl_11gR2> column ob_type  format a7
SCOTT@orcl_11gR2> select * from os_batch order by ob_batch, ob_pm_code
  2  /

OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE
------------ ------------ --------- ---------- ---------- -------
0001         A            H170           25500        475 W
0002         B            H170           25500        475 W
0003         C            IPE100          4400       1000 W
0004         C            IPE100          4400       1000 W
0005         C            IPE100          4400       1000 W
0006         C            IPE100          4400       1000 W
0007         C            IPE100          4400       1000 W
0008         D            IPE200         22000        100 W

8 rows selected.

[Updated on: Sun, 14 April 2013 17:01]

Report message to a moderator

icon14.gif  Re: Complex batch allocation logic [message #582199 is a reply to message #582159] Mon, 15 April 2013 12:17 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks very much for the excellent help mam,just one doubt mam, what i presented in test case is just a few records , i actually have thousands of records with similar combinations , if i make it a combination of tmp_desc and tmp_pm_code in main query how will it make difference, i will try to modify this program on my own and see.Appreciate your goodself for giving me your precious time.Thanks very much.
Re: Complex batch allocation logic [message #582364 is a reply to message #582159] Wed, 17 April 2013 08:32 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

i am really sorry for disturbance as there is one change in the requirement ,the item qty should not be tampered and it has to be inserted into os_batch table as its , i tried my best but could not suceed , i dont know why the condition is failing.I think this is going to be very complex.


CREATE TABLE OW_TEMP_DATA
(
  TMP_NO       VARCHAR2(12 BYTE),
  TMP_PM_CODE  VARCHAR2(12 BYTE),
  TMP_PS_CODE  VARCHAR2(12 BYTE),
  TMP_DESC     VARCHAR2(30 BYTE),
  TMP_WT       NUMBER,
  TMP_QTY      NUMBER,
  TMP_TYPE     VARCHAR2(2 BYTE)
);

CREATE TABLE OS_BATCH
(
  OB_BATCH    VARCHAR2(12 BYTE),
  OB_PM_CODE  VARCHAR2(12 BYTE),
  OB_DESC     VARCHAR2(30 BYTE),
  OB_WT       NUMBER,
  OB_QTY      NUMBER,
  OB_TYPE     VARCHAR2(2 BYTE)
);


insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A',NULL,'H170',28000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A','H1701',NULL,0,650,'W');
Insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A','W166',NULL,0,100,'W');
Insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A','PL1',NULL,0,100,'P');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','B',NULL,'H170',23000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','B','W266',NULL,0,50,'W');
Insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','B','L100',NULL,0,50,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','C',NULL,'IPE100',10000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','C','PL12',NULL,0,500,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','D',NULL,'IPE100',10000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','D','W10',NULL,0,500,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','E',NULL,'IPE100',10000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','E','W101',NULL,0,500,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','F',NULL,'IPE100',10000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','F','W102',NULL,0,500,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','G',NULL,'IPE100',15000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','G','W103',NULL,0,500,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','H',NULL,'IPE200',22000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','H','PL12',NULL,0,100,'W');



commit;


declare
      v_batch    number;
      v_wt       number := 0;
      v_qty      number :=0;
      v_desc    varchar2 (30 byte);
      v_col    number;
    begin
      select nvl (max (to_number (nvl (ob_batch, '0'))), 0)
      into   v_batch
      from   os_batch;
      for c1 in
         (select tmp_pm_code,max (decode(tmp_ps_code,null, tmp_desc,null)) tmp_desc,
                                        max(decode(tmp_ps_code,null, tmp_type,null)) tmp_type,
                                        sum (tmp_wt) tmp_wt,
                                        sum (tmp_qty) tmp_qty
                                     from     ow_temp_data
                                     group  by tmp_pm_code)
           
     loop
        v_wt := v_wt + c1.tmp_wt;
        v_qty := v_qty + c1.tmp_qty;
        if v_wt > 50000 or v_qty > 1000 or
           v_desc is null or c1.tmp_desc != v_desc then           
           v_batch := v_batch + 1;
           v_wt := c1.tmp_wt;
           v_qty := c1.tmp_qty;
           v_desc := c1.tmp_desc;           
        end if;
        insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty, ob_type)
        values (lpad (v_batch, 4, '0'), c1.tmp_pm_code, c1.tmp_desc, c1.tmp_wt, c1.tmp_qty, c1.tmp_type);
     end loop;
   end;
   /


--the output expected was

OB_BATCH	OB_PM_CODE	OB_DESC	OB_WT	OB_QTY	OB_TYPE
0001	             A	         H170	28000	850	W
0002	             B	         H170	23000	100	W  -- here the wt is exceeding so two batches
0003	             C	       IPE100	10000	500	W  --0003 to 0007 because wt is more than 50,000 and qty more than 1000
0004	             D	       IPE100	10000	500	W
0005	             E	       IPE100	10000	500	W
0006	             F	       IPE100	10000	500	W
0007	             G	       IPE100	15000	500	W 
0008	             H	       IPE200	22000	100	W  -- no change as the criteria For qty and wt are below range.



Re: Complex batch allocation logic [message #582377 is a reply to message #582364] Wed, 17 April 2013 14:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
This appears to be a much simpler problem than what you previously presented. It appears that you are not altering weights or quantities, just assigning batch numbers. However, your batch numbers in your posted desired result do not match your description. The following only adds batch numbers, according to your description, not your posted desired result.


SCOTT@orcl_11gR2> set	 null NULL
SCOTT@orcl_11gR2> column tmp_desc format a9
SCOTT@orcl_11gR2> column tmp_type format a8
SCOTT@orcl_11gR2> select * from ow_temp_data order by tmp_pm_code, tmp_ps_code desc
  2  /

TMP_NO       TMP_PM_CODE  TMP_PS_CODE  TMP_DESC      TMP_WT    TMP_QTY TMP_TYPE
------------ ------------ ------------ --------- ---------- ---------- --------
0            A            NULL         H170           28000          0 W
0            A            W166         NULL               0        100 W
0            A            PL1          NULL               0        100 P
0            A            H1701        NULL               0        650 W
0            B            NULL         H170           23000          0 W
0            B            W266         NULL               0         50 W
0            B            L100         NULL               0         50 W
0            C            NULL         IPE100         10000          0 W
0            C            PL12         NULL               0        500 W
0            D            NULL         IPE100         10000          0 W
0            D            W10          NULL               0        500 W
0            E            NULL         IPE100         10000          0 W
0            E            W101         NULL               0        500 W
0            F            NULL         IPE100         10000          0 W
0            F            W102         NULL               0        500 W
0            G            NULL         IPE100         15000          0 W
0            G            W103         NULL               0        500 W
0            H            NULL         IPE200         22000          0 W
0            H            PL12         NULL               0        100 W

19 rows selected.

SCOTT@orcl_11gR2> select * from os_batch
  2  /

no rows selected

SCOTT@orcl_11gR2> declare
  2    v_batch	number;
  3    v_wt	number := 0;
  4    v_qty	number := 0;
  5    v_desc	varchar2 (30 byte) := ' ';
  6  begin
  7    select nvl (max (to_number (nvl (ob_batch, '0'))), 0)
  8    into   v_batch
  9    from   os_batch;
 10    for c1 in
 11  	 (select tmp_pm_code, sum (tmp_wt) tmp_wt, sum (tmp_qty) tmp_qty,
 12  		 max (decode (tmp_ps_code, null, tmp_desc, null)) tmp_desc,
 13  		 max (decode (tmp_ps_code, null, tmp_type, null)) tmp_type
 14  	  from	 ow_temp_data
 15  	  group  by tmp_pm_code
 16  	  order  by tmp_pm_code)
 17    loop
 18  	 v_wt := v_wt + c1.tmp_wt;
 19  	 v_qty := v_qty + c1.tmp_qty;
 20  	 if v_wt > 50000 or v_qty > 1000 or v_desc != c1.tmp_desc then
 21  	   v_batch := v_batch + 1;
 22  	   v_wt := c1.tmp_wt;
 23  	   v_qty := c1.tmp_qty;
 24  	   v_desc := c1.tmp_desc;
 25  	 end if;
 26  	 insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty, ob_type)
 27  	 values (lpad (v_batch, 4, '0'), c1.tmp_pm_code, c1.tmp_desc, c1.tmp_wt, c1.tmp_qty, c1.tmp_type);
 28    end loop;
 29  end;
 30  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> column ob_desc  format a9
SCOTT@orcl_11gR2> column ob_type  format a7
SCOTT@orcl_11gR2> select * from os_batch order by ob_batch, ob_pm_code
  2  /

OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE
------------ ------------ --------- ---------- ---------- -------
0001         A            H170           28000        850 W
0002         B            H170           23000        100 W
0003         C            IPE100         10000        500 W
0003         D            IPE100         10000        500 W
0004         E            IPE100         10000        500 W
0004         F            IPE100         10000        500 W
0005         G            IPE100         15000        500 W
0006         H            IPE200         22000        100 W

8 rows selected.

[Updated on: Wed, 17 April 2013 14:17]

Report message to a moderator

Re: Complex batch allocation logic [message #582386 is a reply to message #582377] Wed, 17 April 2013 23:03 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks very much mam,this is the result i was expecting ,i just failed to add the order by clause in my cursor.I really appreciate your wonderfull help mam.If possible , please do let me know if we can achieve the same result using sql , if you have time.Thanks and Best Regards.
Re: Complex batch allocation logic [message #582445 is a reply to message #582386] Thu, 18 April 2013 07:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
If you are willing to upgrade to 11g, then you can use recursive subquery factoring, as demonstrated below.

SCOTT@orcl_11gR2> set	 null NULL
SCOTT@orcl_11gR2> column tmp_desc format a9
SCOTT@orcl_11gR2> column tmp_type format a8
SCOTT@orcl_11gR2> select * from ow_temp_data order by tmp_pm_code, tmp_ps_code desc
  2  /

TMP_NO       TMP_PM_CODE  TMP_PS_CODE  TMP_DESC      TMP_WT    TMP_QTY TMP_TYPE
------------ ------------ ------------ --------- ---------- ---------- --------
0            A            NULL         H170           28000          0 W
0            A            W166         NULL               0        100 W
0            A            PL1          NULL               0        100 P
0            A            H1701        NULL               0        650 W
0            B            NULL         H170           23000          0 W
0            B            W266         NULL               0         50 W
0            B            L100         NULL               0         50 W
0            C            NULL         IPE100         10000          0 W
0            C            PL12         NULL               0        500 W
0            D            NULL         IPE100         10000          0 W
0            D            W10          NULL               0        500 W
0            E            NULL         IPE100         10000          0 W
0            E            W101         NULL               0        500 W
0            F            NULL         IPE100         10000          0 W
0            F            W102         NULL               0        500 W
0            G            NULL         IPE100         15000          0 W
0            G            W103         NULL               0        500 W
0            H            NULL         IPE200         22000          0 W
0            H            PL12         NULL               0        100 W

19 rows selected.

SCOTT@orcl_11gR2> select * from os_batch
  2  /

no rows selected

SCOTT@orcl_11gR2> insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty, ob_type)
  2  with
  3    data1 as
  4  	 (select tmp_pm_code, tmp_wt, tmp_qty, tmp_desc, tmp_type, rownum rn1
  5  	  from	 (select tmp_pm_code, sum (tmp_wt) tmp_wt, sum (tmp_qty) tmp_qty,
  6  			 max (decode (tmp_ps_code, null, tmp_desc, null)) tmp_desc,
  7  			 max (decode (tmp_ps_code, null, tmp_type, null)) tmp_type
  8  		  from	 ow_temp_data
  9  		  group  by tmp_pm_code
 10  		  order  by tmp_pm_code)),
 11    data2
 12  	 (batch_id, tmp_pm_code, tmp_wt, tmp_qty, tmp_desc, tmp_type, rn1, run_qty, run_wt) as
 13  	 (select 1, tmp_pm_code, tmp_wt, tmp_qty, tmp_desc, tmp_type, rn1, tmp_qty, tmp_wt
 14  	  from	 data1
 15  	  where  rn1 = 1
 16  	  union all
 17  	  select d2.batch_id +
 18  		 case when d1.tmp_desc != d2.tmp_desc
 19  			or d1.tmp_qty + d2.run_qty > 1000
 20  			or d1.tmp_wt + d2.run_wt > 50000 then 1
 21  		      else 0
 22  		 end,
 23  		 d1.tmp_pm_code, d1.tmp_wt, d1.tmp_qty, d1.tmp_desc, d1.tmp_type, d1.rn1,
 24  		 d1.tmp_qty +
 25  		 case when d1.tmp_desc != d2.tmp_desc
 26  			or d1.tmp_qty + d2.run_qty > 1000
 27  			or d1.tmp_wt + d2.run_wt > 50000 then 0
 28  		      else d2.run_qty
 29  		 end,
 30  		 d1.tmp_wt +
 31  		 case when d1.tmp_desc != d2.tmp_desc
 32  			or d1.tmp_qty + d2.run_qty > 1000
 33  			or d1.tmp_wt + d2.run_wt > 50000 then 0
 34  		      else d2.run_wt
 35  		 end
 36  	  from	 data2 d2, data1 d1
 37  	  where  d1.rn1 = d2.rn1 + 1)
 38  select batch_id, tmp_pm_code, tmp_desc, tmp_wt, tmp_qty, tmp_type
 39  from   data2
 40  /

8 rows created.

SCOTT@orcl_11gR2> column ob_desc  format a9
SCOTT@orcl_11gR2> column ob_type  format a7
SCOTT@orcl_11gR2> select * from os_batch order by ob_batch, ob_pm_code
  2  /

OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE
------------ ------------ --------- ---------- ---------- -------
1            A            H170           28000        850 W
2            B            H170           23000        100 W
3            C            IPE100         10000        500 W
3            D            IPE100         10000        500 W
4            E            IPE100         10000        500 W
4            F            IPE100         10000        500 W
5            G            IPE100         15000        500 W
6            H            IPE200         22000        100 W

8 rows selected.

[Updated on: Thu, 18 April 2013 07:33]

Report message to a moderator

Re: Complex batch allocation logic [message #582460 is a reply to message #582445] Thu, 18 April 2013 09:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
In 10g, you could use a user-defined aggregate function as shown below.

SCOTT@orcl_11gR2> set	 null NULL
SCOTT@orcl_11gR2> column tmp_desc format a9
SCOTT@orcl_11gR2> column tmp_type format a8
SCOTT@orcl_11gR2> select * from ow_temp_data order by tmp_pm_code, tmp_ps_code desc
  2  /

TMP_NO       TMP_PM_CODE  TMP_PS_CODE  TMP_DESC      TMP_WT    TMP_QTY TMP_TYPE
------------ ------------ ------------ --------- ---------- ---------- --------
0            A            NULL         H170           28000          0 W
0            A            W166         NULL               0        100 W
0            A            PL1          NULL               0        100 P
0            A            H1701        NULL               0        650 W
0            B            NULL         H170           23000          0 W
0            B            W266         NULL               0         50 W
0            B            L100         NULL               0         50 W
0            C            NULL         IPE100         10000          0 W
0            C            PL12         NULL               0        500 W
0            D            NULL         IPE100         10000          0 W
0            D            W10          NULL               0        500 W
0            E            NULL         IPE100         10000          0 W
0            E            W101         NULL               0        500 W
0            F            NULL         IPE100         10000          0 W
0            F            W102         NULL               0        500 W
0            G            NULL         IPE100         15000          0 W
0            G            W103         NULL               0        500 W
0            H            NULL         IPE200         22000          0 W
0            H            PL12         NULL               0        100 W

19 rows selected.

SCOTT@orcl_11gR2> select * from os_batch
  2  /

no rows selected

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE batch_input AS OBJECT
  2    (tmp_desc	      VARCHAR2(4000),
  3  	tmp_qty 	      NUMBER,
  4  	tmp_wt		      NUMBER);
  5  /

Type created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE batch_t AS OBJECT
  2    (batch_id	      NUMBER,
  3  	run_desc	      VARCHAR2(4000),
  4  	run_qty 	      NUMBER,
  5  	run_wt		      NUMBER,
  6  	STATIC FUNCTION ODCIAggregateInitialize
  7  	  (sctx        IN OUT batch_t)
  8  	  RETURN	      NUMBER,
  9  	MEMBER FUNCTION ODCIAggregateIterate
 10  	  (self        IN OUT batch_t,
 11  	   value       IN     batch_input)
 12  	  RETURN	      NUMBER,
 13  	MEMBER FUNCTION ODCIAggregateTerminate
 14  	  (self        IN     batch_t,
 15  	   returnValue OUT    NUMBER,
 16  	   flags       IN     NUMBER)
 17  	  RETURN	      NUMBER,
 18  	MEMBER FUNCTION ODCIAggregateMerge
 19  	  (self        IN OUT batch_t,
 20  	   ctx2        IN     batch_t)
 21  	  RETURN	      NUMBER);
 22  /

Type created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE BODY batch_t AS
  2    STATIC FUNCTION ODCIAggregateInitialize
  3  	 (sctx	       IN OUT batch_t)
  4  	 RETURN 	      NUMBER
  5    IS
  6    BEGIN
  7  	 sctx := batch_t (NULL, NULL, NULL, NULL);
  8  	 RETURN ODCIConst.Success;
  9    END ODCIAggregateInitialize;
 10  
 11    MEMBER FUNCTION ODCIAggregateIterate
 12  	 (self	       IN OUT batch_t,
 13  	  value        IN     batch_input)
 14  	 RETURN 	      NUMBER
 15    IS
 16    BEGIN
 17  	 IF self.batch_id IS NULL THEN
 18  	   self.batch_id := 1;
 19  	   self.run_desc := value.tmp_desc;
 20  	   self.run_qty := value.tmp_qty;
 21  	   self.run_wt := value.tmp_wt;
 22  	 ELSIF
 23  	   self.run_desc != value.tmp_desc OR
 24  	   self.run_qty + value.tmp_qty > 1000 OR
 25  	   self.run_wt + value.tmp_wt > 50000
 26  	 THEN
 27  	   self.batch_id := self.batch_id + 1;
 28  	   self.run_desc := value.tmp_desc;
 29  	   self.run_qty := value.tmp_qty;
 30  	   self.run_wt := value.tmp_wt;
 31  	 ELSE
 32  	   self.run_qty := self.run_qty + value.tmp_qty;
 33  	   self.run_wt := self.run_wt + value.tmp_wt;
 34  	 END IF;
 35  	 RETURN ODCIConst.Success;
 36    END ODCIAggregateIterate;
 37  
 38    MEMBER FUNCTION ODCIAggregateTerminate
 39  	 (self	       IN     batch_t,
 40  	  returnValue  OUT    NUMBER,
 41  	  flags        IN     NUMBER)
 42  	 RETURN 	      NUMBER
 43    IS
 44    BEGIN
 45  	 returnValue := batch_id;
 46  	 RETURN ODCIConst.Success;
 47    END ODCIAggregateTerminate;
 48  
 49    MEMBER FUNCTION ODCIAggregateMerge
 50  	 (self	       IN OUT batch_t,
 51  	  ctx2	       IN     batch_t)
 52  	 RETURN 	      NUMBER
 53    IS
 54  	 c_self 	      VARCHAR2(1);
 55  	 c_ctx2 	      VARCHAR2(1);
 56  	 c_ini		      VARCHAR2(1);
 57  	 i		      INTEGER;
 58    BEGIN
 59  	 self.batch_id := ctx2.batch_id;
 60  	 RETURN ODCIConst.Success;
 61    END ODCIAggregateMerge;
 62  END;
 63  /

Type body created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION batch
  2    (input batch_input)
  3    RETURN NUMBER
  4    PARALLEL_ENABLE AGGREGATE USING batch_t;
  5  /

Function created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty, ob_type)
  2  select batch (batch_input (tmp_desc, tmp_qty, tmp_wt)) over (order by tmp_pm_code) batch_id,
  3  	    tmp_pm_code, tmp_desc, tmp_wt, tmp_qty, tmp_type
  4  from   (select tmp_pm_code, sum (tmp_wt) tmp_wt, sum (tmp_qty) tmp_qty,
  5  		    max (decode (tmp_ps_code, null, tmp_desc, null)) tmp_desc,
  6  		    max (decode (tmp_ps_code, null, tmp_type, null)) tmp_type
  7  	     from   ow_temp_data
  8  	     group  by tmp_pm_code)
  9  order  by tmp_pm_code
 10  /

8 rows created.

SCOTT@orcl_11gR2> column ob_desc  format a9
SCOTT@orcl_11gR2> column ob_type  format a7
SCOTT@orcl_11gR2> select * from os_batch order by ob_batch, ob_pm_code
  2  /

OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY OB_TYPE
------------ ------------ --------- ---------- ---------- -------
1            A            H170           28000        850 W
2            B            H170           23000        100 W
3            C            IPE100         10000        500 W
3            D            IPE100         10000        500 W
4            E            IPE100         10000        500 W
4            F            IPE100         10000        500 W
5            G            IPE100         15000        500 W
6            H            IPE200         22000        100 W

8 rows selected.

Re: Complex batch allocation logic [message #582488 is a reply to message #582460] Thu, 18 April 2013 14:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
I just realized that I forgot to add the leading zeroes in front of the batch numbers, but I'm sure you can figure that out. If this is a true sample and not a simplification where there are other characters concatenated, then it would be better to store the batch numbers as numeric values and format them using lpad or to_char or something else when you select them.
Re: Complex batch allocation logic [message #582604 is a reply to message #582488] Sat, 20 April 2013 00:23 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks very much mam, i will try to work on this query of 10g version , as there is some problem with my procedure as the procedure is failing while inserting the first batch, some of the different Tmp_desc or sld_flex_17 which is the current description, belonging to second batch are comming in first one while other two criterias of tmp_wt > 50,000 and tmp_qty > 1000 are getting satisfied, i have a list of 1970 records so i am preparing a insert scripts for your review, i dont know why its happening , my actual table is ow_stage_ship which is ow_temp_data initially and where it has to inserted is ow_ship_batch which is os_batch table,the problem is why there is a mix of profile in batch one alone.I have attached the inserts scripts of ow_stage_ship as attachment in ow_stage_ship.sql .see the output sld_flex_17 in batch 0001 is also having 'HEA200' whereas it should have only one 'HEA160'


CREATE TABLE OW_STAGE_SHIP
(
  SL_PM_CODE   VARCHAR2(35 BYTE)                NOT NULL,
  SL_PS_CODE   VARCHAR2(12 BYTE),
  SLD_FLEX_17  VARCHAR2(240 BYTE),
  SL_WT        NUMBER,
  SL_QTY       NUMBER,
  SLD_FLEX_01  VARCHAR2(240 BYTE),
  SLD_FLEX_02  VARCHAR2(240 BYTE)
);


--DATA TO BE UPLOADED INTO OW_SHIP_BATCH

CREATE TABLE OS_SHIP_BATCH
(
  SB_BATCH_NO  VARCHAR2(12 BYTE),
  SB_PM_CODE   VARCHAR2(30 BYTE),
  SM_PS_CODE   VARCHAR2(12 BYTE),
  SB_PS_DESC   VARCHAR2(30 BYTE),
  SB_QTY       NUMBER,
  SB_TOT_WT    NUMBER
)


DECLARE
   v_batch             NUMBER;
   batch_val           VARCHAR2 (12);
   v_wt                NUMBER                             := 0;
   v_qty               NUMBER                             := 0;
   v_desc              VARCHAR2 (30 BYTE);
  
  BEGIN
  
  select nvl (max (to_number (nvl (sb_batch_no, '0'))), 0)+1
     into   v_batch
     from   os_ship_batch;
       
   FOR c1 IN
      ( SELECT   x.sl_pm_code sl_pm_code,
                 x.sld_flex_17 tmp_desc,
                 x.sl_ps_code sl_ps_code,
                 x.sld_flex_01 tmp_type,
                 x.sl_qty* x.sl_wt tmp_wt,x.sl_qty t_qty,
                 (SELECT SUM(b.sl_qty) FROM ow_stage_ship b
                                      WHERE b.sl_pm_code = x.sl_pm_code
                                        AND b.sl_ps_code IS NOT NULL
                                        AND NVL(b.sld_flex_02,'X') ='WI')tmp_qty
                                       FROM ow_stage_ship x
                                      WHERE NVL(x.sld_flex_01,'X') ='W'
                                      and  x.sl_ps_code is null
                                       order by tmp_desc)  
                                            
 loop
          v_wt := v_wt + c1.tmp_wt;
          v_qty := v_qty + c1.tmp_qty;
         if v_wt > 50000 or v_qty > 1000 or v_desc != c1.tmp_desc then
            v_batch := v_batch + 1;
            v_wt := c1.tmp_wt;
            v_qty := c1.tmp_qty;
            v_desc := c1.tmp_desc;
         end if;
        
 
  INSERT INTO os_ship_batch
                  (sb_batch_no,
                   sb_pm_code, 
                   sm_ps_code, sb_ps_desc,sb_qty, sb_tot_wt                  
                  )
           VALUES (lpad (v_batch, 4, '0'),
                   c1.sl_pm_code, 
                   c1.sl_ps_code,c1.tmp_desc, c1.t_qty, c1.tmp_wt
                   );
  
     end loop;
     commit;
    end;   



./fa/10753/0/
Re: Complex batch allocation logic [message #582642 is a reply to message #582488] Sat, 20 April 2013 12:46 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

sorry mam,i have disturbed you a lot , thanks for the wonderfull help rendered. one last problem as its gettig very tough for me now , there is one small change in requirement,the weight in each batch or serial assigned must be balanced properly(in equal proportion not like one serial having 50,000 and another having 10,000 in total of 60000, it must be 30,000 each, the same goes for tot_qty as if the tmp_Qty is 2700 then wt should be in 3 batches with 900 each in such a way that tmp_pm_Code qty is not tampered) without comprimising the tmp_pm_code as the case below.


CREATE TABLE OW_TEMP_DATA
(
  TMP_NO       VARCHAR2(12 BYTE),
  TMP_PM_CODE  VARCHAR2(12 BYTE),
  TMP_PS_CODE  VARCHAR2(12 BYTE),
  TMP_DESC     VARCHAR2(30 BYTE),
  TMP_WT       NUMBER,
  TMP_QTY      NUMBER,
  TMP_TYPE     VARCHAR2(2 BYTE)
)
 
 
 
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A',NULL,'H170',25000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','A','A1',NULL,0,100,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','X',NULL,'H170',25000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','X','A2',NULL,0,100,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','B',NULL,'I170',15000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','B','B2',NULL,0,100,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','C',NULL,'I170',11000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','C','C2',NULL,0,100,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','D',NULL,'I170',15000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','D','D3',NULL,0,100,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','E',NULL,'I170',11000,0,'W');
insert into ow_temp_data (tmp_no,tmp_pm_code,tmp_ps_code,tmp_desc,tmp_wt,tmp_qty,tmp_type) values ('0','E','E4',NULL,0,100,'W');
 
commit;
 
 
 
--Select TMP_PM_CODE, max (decode(tmp_ps_code,null, tmp_desc,null)) tmp_desc,
                                        max(decode(tmp_ps_code,null, tmp_type,null)) tmp_type,
                                        sum (tmp_wt) tmp_wt,
                                        sum (tmp_qty) tmp_qty
                                     from     ow_temp_data
                                     group  by TMP_PM_CODE
                                     ORDER BY TMP_DESC
 
 
TMP_PM_CODE	TMP_DESC	TMP_TYPE	TMP_WT	TMP_QTY
X	                     H170    W	25000	100
A	                     H170	W	25000	100
D	                     I170	W	15000	100
B	                     I170	W	15000	100
C	                     I170	W	11000	100
E	                     I170	W	11000	100
 
 
--the following output i want in os_batch table as below
 
 
CREATE TABLE OS_BATCH
(
  OB_BATCH    VARCHAR2(12 BYTE),
  OB_PM_CODE  VARCHAR2(12 BYTE),
  OB_DESC     VARCHAR2(30 BYTE),
  OB_WT       NUMBER,
  OB_QTY      NUMBER,
  OB_TYPE     VARCHAR2(2 BYTE)
)
 
--desired output that i want is
ob_batch TMP_PM_CODE	TMP_DESC	TMP_TYPE	TMP_WT	TMP_QTY
      0001         X	   H170           W	         25000	100
      0001         A	   H170	          W	         25000	100
      0002         D	   I170	          W	         15000	100  (d+c) as quantity per tmp_pm_code should be equally      distributed by picking alternate or random wt
      0003         B	   I170           W	         15000	100
      0002         C	   I170	          W	         11000	100
      0003         E	   I170           W	         11000	100  
(b+e) since quantity is equally divided in equal proportion.
Re: Complex batch allocation logic [message #582643 is a reply to message #582642] Sat, 20 April 2013 13:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
Sorry, but I am tired of changing table names and column names and requirements. You probably need to hire a consultant.
Re: Complex batch allocation logic [message #582644 is a reply to message #582643] Sat, 20 April 2013 13:11 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
>You probably need to hire a consultant.
his boss needs to hire a replacement.
Re: Complex batch allocation logic [message #582662 is a reply to message #582643] Sat, 20 April 2013 22:44 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Its okay mam, i should feel sorry for troubling you that much.i will try my best to do something with lead function and i did not change the table names its the same table with different sets of data just to explain the test case.Thanks very much for the great help rendered.
Re: Complex batch allocation logic [message #582663 is a reply to message #582644] Sat, 20 April 2013 22:48 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Actually this is one of the toughest requirement i ever had so i posted this , so that i can benefit from the great likes of Barbara,Michel or littlefoot.I dont know why is that a problem to you.
Re: Complex batch allocation logic [message #582664 is a reply to message #582460] Sat, 20 April 2013 23:23 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks for the help.

[Updated on: Sat, 20 April 2013 23:38]

Report message to a moderator

Re: Complex batch allocation logic [message #582668 is a reply to message #582644] Sun, 21 April 2013 01:18 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Quote:
his boss needs to hire a replacement.


Blackswann are you talking about your own experience. Laughing
summary of alternate rows [message #582680 is a reply to message #581429] Sun, 21 April 2013 07:58 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Hi , i have one table ow_ship_det, which contains Four fields sl_desc,sl_qty,sl_wt and sl_pm_code ,based on summary of sl_qty and sl_wt by sl_desc , i need to generate the serial numbers for group of sl_pm_code together , i want to add the qunatites and weight based on alternate rows per each sl_desc so that quantity and weight are evenly distributed and then i am inserting this result into another table called os_batch.test case as below.

CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);

                                                                                         row
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300); --2 
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('D','H170',10000,300); --4
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('E','I100',25000,700); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('F','I100',25000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('G','J100',15000,200); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('H','J100',15000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('I','J100',11000,200); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('J','J100',11000,200); --4

commit;

--i will check the criteria based on this query whether sl_wt > 50000 and sl_qty > 1000, if they are more they will be under
--one serial number but with balanced weight and balanced qty eually or proportionally distributed by picking the alternate rows.


select a.*, sum (sl_wt) over(partition by sl_desc) sum_sl_wt,
                                    sum (sl_qty) over
                                      (partition by sl_desc) sum_sl_qty
                                    from (
SELECT SL_PM_CODE,SL_DESC,SL_QTY,SL_WT FROM OW_SHIP_DET) a ;

--based on the total weight and total qty records to be inserted into os_batch table.

SL_PM_CODE	SL_DESC	SL_QTY	SL_WT	SUM_SL_WT	SUM_SL_QTY
A	           H170	  300	15000	50000	           1200 ---sl_qty is more than 1000 so we need 2 splits
B	           H170	  300	15000	50000	           1200
C	           H170	  300	10000	50000	           1200
D	           H170	  300	10000	50000	           1200
E	           I100	  700	25000	50000	            900  --sl_qty and sl_wt are withing range hence no splits
F	           I100	  200	25000	50000	            900
G	           J100	  200	15000	52000	            800  --weight is more than 50,000 so we need two splits as follows
H	           J100	  200	15000	52000	            800
I	           J100	  200	11000	52000	            800
J	           J100	  200	11000	52000	            800

--i want the result in the os_batch table as follows

CREATE TABLE OS_BATCH
(
  OB_BATCH    VARCHAR2(12 BYTE),
  OB_PM_CODE  VARCHAR2(12 BYTE),
  OB_DESC     VARCHAR2(30 BYTE),
  OB_WT       NUMBER,
  OB_QTY      NUMBER
  );

declare
      v_batch    number;
      v_wt       number := 0;
      v_qty      number :=0;
      v_desc    varchar2 (30 byte);
     
    begin
      select nvl (max (to_number (nvl (ob_batch, '0'))), 0)+1
      into   v_batch
      from   os_batch;
      for c1 in
         (select sl_pm_code,sl_desc,sl_qty,sl_wt
         from ow_ship_det order by sl_pm_code)
           
     loop
        v_wt := v_wt + c1.sl_wt;
        v_qty := v_qty + c1.sl_qty;
        if v_wt > 50000 or v_qty > 1000 or
            c1.sl_desc != v_desc then           
           v_batch := v_batch + 1;
           v_wt := c1.sl_wt;
           v_qty := c1.sl_qty;
           v_desc := c1.sl_desc;           
        end if;
        insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty)
        values (lpad (v_batch, 4, '0'), c1.sl_pm_code, c1.sl_desc, c1.sl_wt, c1.sl_qty);
     end loop;
     commit;
   end;
 
 
--the output i am getting now is wrong since ob_batch is having 45000 and 900 instead i want the wt and qty to be evenly distributed as the output i want.
 
OB_BATCH	OB_PM_CODE	OB_DESC	OB_WT	OB_QTY	OB_TYPE
0001	     A	                 H170	15000	300	null
0001	     B	                 H170	15000	300	null
0001	     C	                 H170	10000	300	null
0002	     D                   H170	10000	300	null
0003	     E	                 I100	25000	700	null
0003	     F	                 I100	25000	200	null
0004	     G	                 J100	15000	200	null
0004	     H	                 J100	15000	200	null
0004	     I	                 J100	11000	200	null
0005	     J	                 J100	11000	200	null


--Desired output in os_batch table


ob_batch   OB_PM_CODE	OB_DESC	OB_QTY	OB_WT	
0001		A	   H170	  300	15000	
0001            C          H170	  300	10000	
0002		B	   H170	  300	15000	
0002		D	   H170	  300	10000
0003		E	   I100	  700	25000	
0003		F	   I100	  200	25000	
0004		G	   J100	  200	15000
0004		I          J100	  200	11000	
0005		H          J100	  200	15000	
0005	        J          J100	  200	11000	




[Edit MC: removed useless empty lines at the bottom]

[Updated on: Sun, 21 April 2013 11:40]

Report message to a moderator

Re: summary of alternate rows [message #582766 is a reply to message #582680] Mon, 22 April 2013 08:29 Go to previous messageGo to next message
joy_division
Messages: 4527
Registered: February 2005
Location: East Coast USA
Senior Member
How long is your cheating going to continue? You don't seem to know how to do your job or you are in way over your head.
Do you tell your boss that you cannot do it and that you are getting help from users on the internet.
You are not just asking for help where you are stuck. you are asking people to write entire applications for you for free.
What are you going to do when people stop doing your work for you?

[Updated on: Mon, 22 April 2013 08:30]

Report message to a moderator

Re: summary of alternate rows [message #582826 is a reply to message #582766] Mon, 22 April 2013 22:45 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Hi joy , its neither of them, nor i am cheating my boss nor i am getting the work done from these forums, if you have read my post properly i have given out the entire test case along with what i have tried,sometimes when i struck and dont go further beyond i seek help from these respected and exceptionally talented guys, and if its disturbing you sorry i cant help.I would appreciate if you could help me in this tough situation.
Re: summary of alternate rows [message #582957 is a reply to message #582766] Tue, 23 April 2013 13:25 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks joy , i found out the solution by the following procedure , i wonder if there is any other better solution than this.



CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);

                                                                                         row
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300); --2 
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('D','H170',10000,300); --4
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('E','I100',25000,700); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('F','I100',25000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('G','J100',15000,200); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('H','J100',15000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('I','J100',11000,200); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('J','J100',11000,200); --4

commit;

CREATE TABLE OS_BATCH
(
  OB_BATCH    VARCHAR2(12 BYTE),
  OB_PM_CODE  VARCHAR2(12 BYTE),
  OB_DESC     VARCHAR2(30 BYTE),
  OB_WT       NUMBER,
  OB_QTY      NUMBER
)

--The procedure what i wrote is 

DECLARE
   CURSOR c1
   IS
      SELECT   sl_desc, SUM (sl_wt) t_wt, SUM (sl_qty) t_att,
               CEIL (GREATEST (SUM (sl_wt) / 50000, SUM (sl_qty) / 1000)) cnt
          FROM ow_ship_det
      GROUP BY sl_desc;

   CURSOR c2 (p_desc VARCHAR2)
   IS
      SELECT   sl_pm_code, sl_desc, sl_wt, sl_qty
          FROM ow_ship_det
         WHERE sl_desc = p_desc
      ORDER BY sl_wt DESC, sl_pm_code DESC;

   CURSOR c3
   IS
     select LPAD ( nvl (max (to_number (nvl (ob_batch, '0'))), 0)+1,4,'0')
      AS NEW_BATCH
      from   os_batch;

   no_of_batch_req       NUMBER;
   no_of_batch           NUMBER;
   last_batch_no         VARCHAR2 (5);
   new_batch_no          VARCHAR2 (5);
   first_batch_in_prof   VARCHAR2 (5);
BEGIN
   IF c3%ISOPEN
   THEN
      CLOSE c3;
   END IF;

   OPEN c3;

   FETCH c3
    INTO last_batch_no;

   CLOSE c3;

   new_batch_no := last_batch_no;
   first_batch_in_prof := last_batch_no;

   FOR i IN c1
   LOOP
      no_of_batch_req := i.cnt;
      no_of_batch := 1;

      FOR j IN c2 (i.sl_desc)
      LOOP
         INSERT INTO os_batch
                     (ob_batch, ob_pm_code, ob_desc, ob_wt,
                      ob_qty
                     )
              VALUES (new_batch_no, j.sl_pm_code, j.sl_desc, j.sl_wt,
                      j.sl_qty
                     );

         IF no_of_batch = no_of_batch_req
         THEN
            no_of_batch := 1;
            new_batch_no := first_batch_in_prof;
         ELSE
            no_of_batch := no_of_batch + 1;
            new_batch_no := LPAD (((new_batch_no + 1)), 4, '0');
         END IF;
      END LOOP;

      new_batch_no := LPAD (((first_batch_in_prof + i.cnt)), 4, '0');
      first_batch_in_prof := new_batch_no;
   END LOOP;

   COMMIT;
END;

OB_BATCH	OB_PM_CODE	OB_DESC	OB_WT	OB_QTY
0001	            B	          H170	15000	300
0002	            A	          H170	15000	300
0001	            D	          H170	10000	300
0002	            C	          H170	10000	300
0003	            F	          I100	25000	200
0003	            E	          I100	25000	700
0004	            H	          J100	15000	200
0005	            G	          J100	15000	200
0004	            J	          J100	11000	200
0005	            I	          J100	11000	200

Re: Complex batch allocation logic [message #582958 is a reply to message #582643] Tue, 23 April 2013 13:32 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

finally mam, i found out the solution myself by trying different methods, ofcourse with your ideas and guidance, please find below the test case with solution and what i want , but i think still there is a better workaround than this.Please dont think that i am getting programs written by this site, yes sometimes i really get struck and as i said there is no one to help me out here as i am all alone here as programmer in my company, sometimes the preassures are too much , hence i seek your valuable guidance to do my work better, nothing else.With all due respect , i really appreciate your help .THanks very much.If you have some time , please review this code and kindly advice.


CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);

                                                                                         row
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300); --2 
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('D','H170',10000,300); --4
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('E','I100',25000,700); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('F','I100',25000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('G','J100',15000,200); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('H','J100',15000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('I','J100',11000,200); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('J','J100',11000,200); --4

commit;

CREATE TABLE OS_BATCH
(
  OB_BATCH    VARCHAR2(12 BYTE),
  OB_PM_CODE  VARCHAR2(12 BYTE),
  OB_DESC     VARCHAR2(30 BYTE),
  OB_WT       NUMBER,
  OB_QTY      NUMBER
)

--The procedure what i wrote is 

DECLARE
   CURSOR c1
   IS
      SELECT   sl_desc, SUM (sl_wt) t_wt, SUM (sl_qty) t_att,
               CEIL (GREATEST (SUM (sl_wt) / 50000, SUM (sl_qty) / 1000)) cnt
          FROM ow_ship_det
      GROUP BY sl_desc;

   CURSOR c2 (p_desc VARCHAR2)
   IS
      SELECT   sl_pm_code, sl_desc, sl_wt, sl_qty
          FROM ow_ship_det
         WHERE sl_desc = p_desc
      ORDER BY sl_wt DESC, sl_pm_code DESC;

   CURSOR c3
   IS
     select LPAD ( nvl (max (to_number (nvl (ob_batch, '0'))), 0)+1,4,'0')
      AS NEW_BATCH
      from   os_batch;

   no_of_batch_req       NUMBER;
   no_of_batch           NUMBER;
   last_batch_no         VARCHAR2 (5);
   new_batch_no          VARCHAR2 (5);
   first_batch_in_prof   VARCHAR2 (5);
BEGIN
   IF c3%ISOPEN
   THEN
      CLOSE c3;
   END IF;

   OPEN c3;

   FETCH c3
    INTO last_batch_no;

   CLOSE c3;

   new_batch_no := last_batch_no;
   first_batch_in_prof := last_batch_no;

   FOR i IN c1
   LOOP
      no_of_batch_req := i.cnt;
      no_of_batch := 1;

      FOR j IN c2 (i.sl_desc)
      LOOP
         INSERT INTO os_batch
                     (ob_batch, ob_pm_code, ob_desc, ob_wt,
                      ob_qty
                     )
              VALUES (new_batch_no, j.sl_pm_code, j.sl_desc, j.sl_wt,
                      j.sl_qty
                     );

         IF no_of_batch = no_of_batch_req
         THEN
            no_of_batch := 1;
            new_batch_no := first_batch_in_prof;
         ELSE
            no_of_batch := no_of_batch + 1;
            new_batch_no := LPAD (((new_batch_no + 1)), 4, '0');
         END IF;
      END LOOP;

      new_batch_no := LPAD (((first_batch_in_prof + i.cnt)), 4, '0');
      first_batch_in_prof := new_batch_no;
   END LOOP;

   COMMIT;
END;

OB_BATCH	OB_PM_CODE	OB_DESC	OB_WT	OB_QTY
0001	            B	          H170	15000	300
0002	            A	          H170	15000	300
0001	            D	          H170	10000	300
0002	            C	          H170	10000	300
0003	            F	          I100	25000	200
0003	            E	          I100	25000	700
0004	            H	          J100	15000	200
0005	            G	          J100	15000	200
0004	            J	          J100	11000	200
0005	            I	          J100	11000	200

Re: Complex batch allocation logic [message #583101 is a reply to message #582643] Thu, 25 April 2013 15:51 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

mam sorry for disturbing you can we accomplish the same thing using sql query.


CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);

                                                                                         row
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300); --2 
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('D','H170',10000,300); --4
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('E','I100',25000,700); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('F','I100',25000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('G','J100',15000,200); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('H','J100',15000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('I','J100',11000,200); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('J','J100',11000,200); --4

commit;

CREATE TABLE OS_BATCH
(
  OB_BATCH    VARCHAR2(12 BYTE),
  OB_PM_CODE  VARCHAR2(12 BYTE),
  OB_DESC     VARCHAR2(30 BYTE),
  OB_WT       NUMBER,
  OB_QTY      NUMBER
)

--The procedure what i wrote is 

DECLARE
   CURSOR c1
   IS
      SELECT   sl_desc, SUM (sl_wt) t_wt, SUM (sl_qty) t_att,
               CEIL (GREATEST (SUM (sl_wt) / 50000, SUM (sl_qty) / 1000)) cnt
          FROM ow_ship_det
      GROUP BY sl_desc;

   CURSOR c2 (p_desc VARCHAR2)
   IS
      SELECT   sl_pm_code, sl_desc, sl_wt, sl_qty
          FROM ow_ship_det
         WHERE sl_desc = p_desc
      ORDER BY sl_wt DESC, sl_pm_code DESC;

   CURSOR c3
   IS
     select LPAD ( nvl (max (to_number (nvl (ob_batch, '0'))), 0)+1,4,'0')
      AS NEW_BATCH
      from   os_batch;

   no_of_batch_req       NUMBER;
   no_of_batch           NUMBER;
   last_batch_no         VARCHAR2 (5);
   new_batch_no          VARCHAR2 (5);
   first_batch_in_prof   VARCHAR2 (5);
BEGIN
   IF c3%ISOPEN
   THEN
      CLOSE c3;
   END IF;

   OPEN c3;

   FETCH c3
    INTO last_batch_no;

   CLOSE c3;

   new_batch_no := last_batch_no;
   first_batch_in_prof := last_batch_no;

   FOR i IN c1
   LOOP
      no_of_batch_req := i.cnt;
      no_of_batch := 1;

      FOR j IN c2 (i.sl_desc)
      LOOP
         INSERT INTO os_batch
                     (ob_batch, ob_pm_code, ob_desc, ob_wt,
                      ob_qty
                     )
              VALUES (new_batch_no, j.sl_pm_code, j.sl_desc, j.sl_wt,
                      j.sl_qty
                     );

         IF no_of_batch = no_of_batch_req
         THEN
            no_of_batch := 1;
            new_batch_no := first_batch_in_prof;
         ELSE
            no_of_batch := no_of_batch + 1;
            new_batch_no := LPAD (((new_batch_no + 1)), 4, '0');
         END IF;
      END LOOP;

      new_batch_no := LPAD (((first_batch_in_prof + i.cnt)), 4, '0');
      first_batch_in_prof := new_batch_no;
   END LOOP;

   COMMIT;
END;

OB_BATCH	OB_PM_CODE	OB_DESC	OB_WT	OB_QTY
0001	            B	          H170	15000	300
0002	            A	          H170	15000	300
0001	            D	          H170	10000	300
0002	            C	          H170	10000	300
0003	            F	          I100	25000	200
0003	            E	          I100	25000	700
0004	            H	          J100	15000	200
0005	            G	          J100	15000	200
0004	            J	          J100	11000	200
0005	            I	          J100	11000	200

Previous Topic: Hierarical query
Next Topic: Merge is not working properly
Goto Forum:
  


Current Time: Tue Oct 21 02:57:30 CDT 2014

Total time taken to generate the page: 0.09453 seconds