1 2 3 Previous Next 39 Replies Latest reply on May 28, 2013 12:31 PM by NuhaArif

    need help to resolve this issue

    NuhaArif
      can someone help me achive the same results using sql.
      CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);
      
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300); 
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300);  
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300); 
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('D','H170',10000,300); 
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('E','I100',25000,700); 
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('F','I100',25000,200); 
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('G','J100',15000,200); 
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('H','J100',15000,200); 
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('I','J100',11000,200); 
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('J','J100',11000,200); 
      
      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
      Edited by: 998476 on Apr 27, 2013 3:06 AM
        • 1. Re: need help to resolve this issue
          user639304
          Hi,

          It's good that you have posted the input, the procedure and the output. But could you also explain, in a few lines, what you have to do with the input , in order to get the output?
          • 2. Re: need help to resolve this issue
            NuhaArif
            first, i would like to thank for responding to my post, i will explain i have sets of data on which i need to assign serial based on following conditions,each serial number must have sl_desc with sum of sl_qty not more than 1000 and sum of sl_wt not more than 50,000, if it exceeds then another serial number is attached, keep in mind that wieght per each serial number or batch number must have wieght (sl_wt) evenly distributed along with sl_qty (without tampering the qty or wt of sl_pm_code) and the results along with serial numbers to inserted into os_batch table,i have written a procedure to accomplish this and you can check the same.I am posting this to find a simpler solutions from you great guys.Unfortunately nobody has the answer to this.

            Edited by: 998476 on Apr 27, 2013 9:35 AM
            • 3. Re: need help to resolve this issue
              Hoek
              Thanks for posting a testcase.
              It looks like you can do this using analytics.
              You're looking for a way to assign ob_batch based on sl_desc, within those sets on sl_wt in a certain order (descending for sl_wt and sl_pm_code), if I understand correct?

              Here's an example of what you could try:
              SQL> select * from os_batch;
              
              no rows selected
              
              SQL> select * from ow_ship_det;
              
              SL_PM_CODE   SL_DESC                   SL_WT     SL_QTY
              ------------ -------------------- ---------- ----------
              A            H170                      15000        300
              B            H170                      15000        300
              C            H170                      10000        300
              D            H170                      10000        300
              E            I100                      25000        700
              F            I100                      25000        200
              G            J100                      15000        200
              H            J100                      15000        200
              I            J100                      11000        200
              J            J100                      11000        200
              
              10 rows selected.
              
              SQL> insert into os_batch
                2  select lpad(case
                3                when mdr = 1
                4                then rn3
                5                else mdr + rn3
                6              end
                7             , 4, '0')          
                8  ,      sl_pm_code
                9  ,      sl_desc
               10  ,      sl_wt
               11  ,      sl_qty
               12  from ( select dense_rank() over ( order by sl_desc ) mdr
               13         ,      row_number() over ( partition by t.sl_desc, sl_wt, sl_qty
               14                                        order by t.sl_desc, sl_pm_code desc, sl_wt desc) rn3
               15         ,      t.sl_pm_code
               16         ,      t.sl_desc
               17         ,      t.sl_wt
               18         ,      t.sl_qty 
               19         from   ow_ship_det t
               20        );
              
              10 rows created.
              
              SQL> select * from os_batch order by 3, 4 desc, 2 desc;
              
              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
              
              10 rows selected.
              
              SQL> 
              You might need to do some additional tweaking (keep in mind that rows are not stored in any particular order, so you'll probably need an ORDER BY), or, if this isn't getting you anywhere, post the requirement in human language as well.

              edit
              I see my attempt more or less cross-posted the additional explanation, which changes a couple of assumptions I made.
              Back to the drawing board, I guess ;)

              Edited by: Hoek on Apr 27, 2013 6:50 PM
              1 person found this helpful
              • 4. Re: need help to resolve this issue
                NuhaArif
                thanks hoek , the query you gave me was very helpful, it will be great if you had considered the sl_wt > 50000 and sl_qty > 1000 per sl_desc as condition , with each serial number having balanced weight in each serial or batch no .i will explain a very simple example.
                suppose i have following sl_pm_codes under one sl_desc with following sl_wt and sl_qty as below.I dont know how to do this and willing to learn any technique from you greats.
                scenario 1
                
                ow_ship_det table
                
                sl_pm_code sl_desc sl_wt          sl_qty
                a                 aa       20000         300
                b                 aa        6000          200
                c                 aa       20000         300
                d                 aa        6000          200
                
                then based on the above scenario the following result will be in the batch table as below since it has to distribute the sl_wt and sl_qty in equal proportion
                
                
                os_batch   ob_pm_code  ob_desc  ob_wt  ob_qty
                0001           a                  aa        20000   300
                0001           b                  aa          6000   200
                0002           c                  aa        20000   300
                0002           d                 aa           6000   200
                
                
                -- in scenario 1 same batch 0001 is assigned for sl_pm_code a,b as if you add up the total of sl_wt is more than 50000 that is 52000 hence it got splitted into two batches without disturbing the quantities of sl_pm_code and if you see they are distributed in balance.
                Scenario 2
                
                scenario 1
                
                ow_ship_det table
                
                sl_pm_code sl_desc sl_wt          sl_qty
                a                 aa        50000         600
                b                 bb        6000          700
                c                 bb       27000         400
                d                 cc        6000          200
                
                then based on the above scenario the following result will be in the batch table as below since it has to distribute the sl_wt and sl_qty in equal proportion
                
                
                os_batch   ob_pm_code  ob_desc  ob_wt  ob_qty
                0001           a                  aa        50000    600
                0002           b                  bb          6000   700
                0003           c                  bb        27000    400
                0004           d                  cc           6000   200
                
                --in scenario 2 for sl_desc bb if you add up the sl_qty its more than 1000 hence two seperate batches or serials are assigned as 0002 and 0003 , 0001 and 0004 are within range since sl_wt and sl_qty are withing range.
                
                {code]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                • 5. Re: need help to resolve this issue
                  Hoek
                  Thanks for the additional details.
                  On one side it's more clear now, however, on the other side it raises more questions....
                  Given you both scenario's, I wonder: could it be you narrowed down the examples a little bit too much?

                  I assumed it would be 'safe' to group/partition the data on SL_DESC first, however, scenario2 confuses me.
                  Also, is it to be expected to have data like scenario1 and scenario2 in OW_SHIP_DET at the same time?
                  If so, scenario2 needs adjustment imho, given the values for SL_DESC and the results you want from it.
                  There's no logical way to consider the data from scenario2 as one 'group', since SL_DESC has three values instead of one...
                  For obvious reasons I added a 1 do all SL_DESC's in scenario2.

                  That aside:
                  It is an interesting requirement, so add more details or scenario's, whatever you feel like in order to get this solved as elegant as possible.
                  Perhaps a volunteer that knows how to use the MODEL-clause or some neat XML query will add a possible solution as well?

                  I still think this can also be done using analytics, so therefore I'll just post what I've clunged up sofar.
                  Without an update from your side, it's too much guess work to continue.
                  I've commented out possible useful columns that could be of help identifying when to split groups.
                  Play a bit with them and see what data they return and how you could possibly use them:
                  SQL> select * from ow_ship_det
                    2  /
                  
                  SL_PM_CODE   SL_DESC                   SL_WT     SL_QTY
                  ------------ -------------------- ---------- ----------
                  a            aa                        20000        300
                  b            aa                         6000        300
                  c            aa                        20000        300
                  d            aa                         6000        300
                  a            aa1                       50000        600
                  b            bb1                        6000        700
                  c            bb1                       27000        400
                  d            cc1                        6000        200
                  
                  8 rows selected.
                  
                  SQL> --select * from os_batch;
                  SQL> --insert into os_batch
                  SQL> with  limits as ( select 50000 wt_limit
                    2                    ,      1000  qty_limit
                    3                    from   dual
                    4  )
                    5  --
                    6  select least( case 
                    7                  when total_wt > wt_limit
                    8                   and running_total_wt <= total_wt 
                    9                  then 
                   10                    lpad(wt_rnk, 4, '0')
                   11                  else 
                   12                    lpad(rn, 4, '0')
                   13                end
                   14              , case 
                   15                  when total_qty > qty_limit
                   16                   and running_total_qty <= total_qty 
                   17                  then 
                   18                    lpad(qty_rnk, 4, '0')
                   19                  else
                   20                    lpad(rn, 4, '0')
                   21                end
                   22         )
                   23  ,      sl_desc
                   24  ,      sl_pm_code
                   25  ,      sl_wt
                   26  ,      sl_qty   
                   27  --,      total_wt/splits avg_wt  --average weight per split
                   28  --,      total_qty/splits avg_wt --average quantity per split
                   29  from ( select sl_desc
                   30         ,      sl_pm_code
                   31         ,      sl_wt
                   32         ,      sl_qty   
                   33         /* a bunch of analytical aggregations you could use */
                   34         ,      row_number() over (partition by sl_desc order by sl_pm_code)   rn                  -- rownum per sl_desc
                   35         ,      rank() over (partition by sl_desc, sl_wt order by sl_pm_code)  wt_rnk              -- rank per weight
                   36         ,      rank() over (partition by sl_desc, sl_qty order by sl_pm_code) qty_rnk              -- rank per quantity
                   37         ,      count(*) over (partition by sl_desc)                           total_rows_per_desc -- total rows  per sl_desc
                   38         ,      count(*) over (partition by sl_desc)/2                         splits              -- sofar unused     
                   39         ,      sum(sl_wt) over (partition by sl_desc order by sl_pm_code)     running_total_wt    -- running total weight per sl_desc
                   40         ,      sum(sl_qty) over (partition by sl_desc order by sl_pm_code)    running_total_qty   -- running total quantity per sl_desc
                   41         ,      sum(sl_wt) over (partition by sl_desc)                         total_wt            -- total weight per sl_desc
                   42         ,      sum(sl_qty) over (partition by sl_desc)                        total_qty           -- total quantity per sl_desc
                   43         /* the limits for SL_WT and SL_QTY */
                   44         ,      wt_limit
                   45         ,      qty_limit
                   46         from   ow_ship_det
                   47         ,      limits
                   48       )
                   49  order by sl_desc
                   50  ,        sl_pm_code;
                  
                  LEAS SL_DESC              SL_PM_CODE        SL_WT     SL_QTY
                  ---- -------------------- ------------ ---------- ----------
                  0001 aa                   a                 20000        300
                  0001 aa                   b                  6000        300
                  0002 aa                   c                 20000        300
                  0002 aa                   d                  6000        300
                  0001 aa1                  a                 50000        600
                  0001 bb1                  b                  6000        700
                  0001 bb1                  c                 27000        400
                  0001 cc1                  d                  6000        200
                  
                  8 rows selected.
                  • 6. Re: need help to resolve this issue
                    NuhaArif
                    thanks hoek , appreciate your great help , actually i have a total number of 180 rows , can i post it as an test case since the output of those will give us a clear idea.
                    • 7. Re: need help to resolve this issue
                      Hoek
                      Not sure what you mean? Are you reluctant to post the dataset for whatever reason?
                      Or do you think it's just too much?
                      If the latter: 180 inserts isn't that much, I've seen waaaaay larger(unformatted) packages being posted here that were good for endless scrolling, so you could simply post the testcase.
                      Getting a clear idea will get us closer to resolving the issue, so, yes, just post the set (preferrably INSERT INTO statements). (Alternatively you could mail the set me as well (my profile contains my emailaddres), by the way)
                      • 8. Re: need help to resolve this issue
                        NuhaArif
                        thanks very much hoek for the response , your help is awesome , actually i have a huge data i created it as insert statements, based on this inserts i want to assign serial numbers or batch numbers and then insert into os_batch table , see i will check mainly for two conditions if the sl_wt is greater than 50,000 or sl_qty greater than 1000 and then assign serial number in some cases both the situations of sl_wt > 50000 and sl_qty > 1000 will come , in that case i will split and assign the serial numbers as per qty, i will demonstrate below.

                        in the given example below i have two sl_desc HEA160 and HEA200 ,for HEA160 the total sl_wt is 14,922.367 and total of qty is below 1000 hence we need to assign one serial number as its below given range.Whereas for sl_desc HEA200, the total sl_wt is 67216.355     and sl_qty is 2649 which is more than range of sl_wt > 50000 and sl_qty being greater than 1000, but the method splitting them should be even , or they should be evenly distributed like given in following quer as CNT column.In simple terms the qty and weight should not get distributed as one serial number having wt as 50,000 and other having 10000 ,it must be like below query.

                        --i will check how many splits required using the following query 
                         
                        SELECT   sl_desc, SUM (tot_wt) t_wt, SUM (tot_qty) t_att,
                                 CEIL (GREATEST (SUM (tot_wt) / 50000, SUM (tot_qty) / 1000)) cnt
                            FROM (SELECT   sl_desc, sl_pm_code, SUM (sl_wt) tot_wt,
                                           SUM (sl_qty) tot_qty
                                      FROM ow_ship_det
                                  GROUP BY sl_desc, sl_pm_code)
                        GROUP BY sl_desc;
                         
                        --based on the cnt column , the sl_pm_codes for each sl_desc will get distributed
                         
                        SL_DESC     T_WT      T_ATT     CNT
                        HEA160     14922.367       860     1
                        HEA200     67216.355      2649     3
                        
                        
                        iNSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1064', 'HEA160', 211.837, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1065', 'HEA160', 171.62, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1066', 'HEA160', 387.442, 20); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1067', 'HEA160', 387.442, 20); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1068', 'HEA160', 197.646, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1069', 'HEA160', 197.646, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1070', 'HEA160', 175.161, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1071', 'HEA160', 175.161, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1072', 'HEA160', 175.161, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1073', 'HEA160', 175.161, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1074', 'HEA160', 437.83, 28); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1075', 'HEA160', 437.83, 28); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1076', 'HEA160', 230.175, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1077', 'HEA160', 230.175, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1078', 'HEA160', 230.175, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1079', 'HEA160', 230.175, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1080', 'HEA160', 4024.903, 266); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1081', 'HEA160', 2118.33, 140); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1082', 'HEA160', 1544.544, 72); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1083', 'HEA160', 1372.928, 64); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1084', 'HEA160', 686.46, 32); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1085', 'HEA160', 514.851, 24); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1086', 'HEA160', 171.62, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1087', 'HEA160', 97.154, 7); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1088', 'HEA160', 97.154, 7); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1089', 'HEA160', 121.893, 7); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1090', 'HEA160', 121.893, 7); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1029', 'HEA200', 362.982, 38); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1030', 'HEA200', 362.982, 38); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1031', 'HEA200', 362.982, 38); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1032', 'HEA200', 362.982, 38); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1033', 'HEA200', 394.068, 44); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1034', 'HEA200', 326.585, 32); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1035', 'HEA200', 353.287, 34); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1036', 'HEA200', 353.287, 34); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1037', 'HEA200', 325.735, 35); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1038', 'HEA200', 325.735, 35); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1039', 'HEA200', 325.735, 35); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1040', 'HEA200', 325.735, 35); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1041', 'HEA200', 325.145, 35); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1042', 'HEA200', 319.058, 24); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1043', 'HEA200', 319.058, 24); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1044', 'HEA200', 1567.04, 160); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1045', 'HEA200', 1880.448, 192); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1046', 'HEA200', 313.408, 32); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1047', 'HEA200', 313.408, 32); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1048', 'HEA200', 359.095, 38); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1049', 'HEA200', 359.095, 38); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1050', 'HEA200', 359.095, 38); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1051', 'HEA200', 257.662, 23); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1052', 'HEA200', 303.349, 29); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1053', 'HEA200', 257.662, 23); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1054', 'HEA200', 257.662, 23); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1055', 'HEA200', 257.662, 23); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1056', 'HEA200', 303.349, 29); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1057', 'HEA200', 303.349, 29); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1062', 'HEA200', 163.848, 7); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1063', 'HEA200', 163.848, 7); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-1091', 'HEA200', 336.028, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2022', 'HEA200', 271.002, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2023', 'HEA200', 271.002, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2024', 'HEA200', 271.002, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2025', 'HEA200', 271.002, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2026', 'HEA200', 280.468, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2027', 'HEA200', 139.013, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2028', 'HEA200', 1640.658, 72); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2029', 'HEA200', 292.539, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2030', 'HEA200', 292.139, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2031', 'HEA200', 292.139, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2032', 'HEA200', 1640.658, 72); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2033', 'HEA200', 292.539, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2034', 'HEA200', 291.718, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2035', 'HEA200', 291.718, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2036', 'HEA200', 291.718, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2037', 'HEA200', 291.718, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2038', 'HEA200', 292.139, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2039', 'HEA200', 292.139, 14); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2040', 'HEA200', 1640.658, 72); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2041', 'HEA200', 1640.658, 72); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2042', 'HEA200', 235.764, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2043', 'HEA200', 471.538, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2044', 'HEA200', 471.538, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2045', 'HEA200', 235.764, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2046', 'HEA200', 151.251, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2047', 'HEA200', 151.251, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2048', 'HEA200', 151.251, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2049', 'HEA200', 151.251, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2050', 'HEA200', 235.764, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2051', 'HEA200', 258.853, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2052', 'HEA200', 258.853, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2053', 'HEA200', 256.347, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2054', 'HEA200', 256.347, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2055', 'HEA200', 256.347, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2056', 'HEA200', 256.347, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2057', 'HEA200', 235.764, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2058', 'HEA200', 1414.602, 30); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2059', 'HEA200', 1414.602, 30); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2060', 'HEA200', 235.764, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2061', 'HEA200', 235.764, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2062', 'HEA200', 1178.84, 25); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2063', 'HEA200', 1414.602, 30); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2064', 'HEA200', 235.764, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2065', 'HEA200', 193.508, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2066', 'HEA200', 193.508, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2067', 'HEA200', 193.508, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2068', 'HEA200', 387.026, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2069', 'HEA200', 387.026, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2070', 'HEA200', 193.508, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2071', 'HEA200', 193.508, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2072', 'HEA200', 387.026, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2073', 'HEA200', 80.884, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2074', 'HEA200', 80.884, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2075', 'HEA200', 353.16, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2076', 'HEA200', 312.702, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2077', 'HEA200', 277.654, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2078', 'HEA200', 93.029, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2079', 'HEA200', 93.029, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2080', 'HEA200', 193.508, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2081', 'HEA200', 133.304, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2082', 'HEA200', 133.304, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2083', 'HEA200', 122.855, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2084', 'HEA200', 122.855, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2085', 'HEA200', 389.466, 12); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2086', 'HEA200', 389.466, 12); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2087', 'HEA200', 91.138, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2088', 'HEA200', 80.599, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2089', 'HEA200', 80.599, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2090', 'HEA200', 80.599, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2091', 'HEA200', 139.013, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2092', 'HEA200', 591.006, 12); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2093', 'HEA200', 135.756, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2094', 'HEA200', 5741.136, 96); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2095', 'HEA200', 1435.296, 24); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2096', 'HEA200', 2870.232, 48); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2097', 'HEA200', 536.818, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2098', 'HEA200', 531.806, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2099', 'HEA200', 532.09, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2100', 'HEA200', 525.876, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2101', 'HEA200', 787.848, 16); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2102', 'HEA200', 1969.61, 40); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2103', 'HEA200', 928.218, 24); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2104', 'HEA200', 523.076, 10); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2105', 'HEA200', 263.449, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2106', 'HEA200', 91.138, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2107', 'HEA200', 80.599, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2108', 'HEA200', 139.013, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2109', 'HEA200', 139.013, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2110', 'HEA200', 210.068, 2); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2111', 'HEA200', 262.343, 5); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2112', 'HEA200', 141.945, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2113', 'HEA200', 141.945, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2114', 'HEA200', 151.516, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2115', 'HEA200', 151.516, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2116', 'HEA200', 141.945, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-2117', 'HEA200', 141.945, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4001', 'HEA200', 236.607, 3); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4003', 'HEA200', 235.371, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4004', 'HEA200', 235.371, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4005', 'HEA200', 235.371, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4006', 'HEA200', 235.371, 6); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4007', 'HEA200', 234.728, 7); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4008', 'HEA200', 234.728, 7); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4009', 'HEA200', 264.171, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4010', 'HEA200', 264.171, 8); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4011', 'HEA200', 645.132, 12); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4012', 'HEA200', 1290.264, 24); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4013', 'HEA200', 215.044, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4014', 'HEA200', 215.044, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4015', 'HEA200', 645.132, 12); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4016', 'HEA200', 1290.264, 24); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4017', 'HEA200', 215.044, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4018', 'HEA200', 215.044, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4019', 'HEA200', 215.044, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4020', 'HEA200', 215.044, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4021', 'HEA200', 212.603, 2); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4022', 'HEA200', 212.603, 2); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4029', 'HEA200', 215.044, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4030', 'HEA200', 215.044, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4031', 'HEA200', 215.044, 4); 
                        INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
                        '1239606-4032', 'HEA200', 215.044, 4); 
                        COMMIT;
                         
                         
                        --So i will have three serial numbers for HEA200 where all the sl_pm_codes will be grouped in these 3 without tampering the weight and quantities.
                        --since HEA160 has 1 cnt then all the sl_pm_codes will be under 1 serial or batch number.
                         
                        --desired summary of  output will be ,
                         
                        ob_DESC             ob_BATCH_NO                     sum(ob_wt)     sum(ob_qty)
                        HEA160                         0001                         14,922.37      860
                        HEA200                         0002                        25,318.96      989
                        HEA200                         0003                        21,642.83      804
                        HEA200                         0004                        20,254.56      856
                        • 9. Re: need help to resolve this issue
                          sb92075
                          Hoek,

                          FWIW, take a look at URL below, if you are so inclined.

                          http://www.orafaq.com/forum/mv/msg/187315/581429/136107/#msg_581429
                          • 10. Re: need help to resolve this issue
                            NuhaArif
                            hi sb i dont know how to approach this correctly and hence i am approaching you greats, appreciate if you could give me a resolve.
                            • 11. Re: need help to resolve this issue
                              Hoek
                              Didn't have the time to really dive into it, since your question doesn't fall into the 'answered in 2 minutes-category' and I have my own job during the day, but I'm currently working on it, so you can expect an update from my side.
                              • 12. Re: need help to resolve this issue
                                NuhaArif
                                thanks very much hoek , i know this is not so easy and i believe can be accomplished using some procedure.
                                • 13. Re: need help to resolve this issue
                                  Hoek
                                  i know this is not so easy
                                  Well, I can confirm that 100%.
                                  Sofar I've been able to 'break' my own SQL/queries just by adding a few rows to the table.
                                  That's quite frustrating, but educational at the same time. Let's say that I have a 'blueprint', but it's not 100% complete yet.
                                  and i believe can be accomplished using some procedure.
                                  Yes, but you've asked for a SQL solution, so call me stubborn, but I'm sticking to SQL ;)
                                  Seriously:
                                  I tend to think that the MODEL-clause (which I don't really fully master yet) will be the only 'safe SQL way' to use in order to get the desired result.
                                  This requirement can be categorized as a 'bin fitting' or 'bin packing' problem imho, and truly is a tough one (like the most of them).
                                  ( see also: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1769669400346272947 )

                                  The link SB (thanks, SB) posted contains useful code if you want to stick to a PL/SQL solution.
                                  The posts from Barbara Boehmer are very useful, did you check them as well?

                                  A counter-question:
                                  How did you get to sum (ob_wt) = 25,318.96 for batchno 0002?
                                  Based on running totals I get 25066.831 as first value to split on for HEA200 (sl_pm_code is @ 1239606-2047 then)...

                                  will be able to continue this quest after the next 10/12 hours
                                  • 14. Re: need help to resolve this issue
                                    NuhaArif
                                    >
                                    Yes, but you've asked for a SQL solution, so call me stubborn, but I'm sticking to SQL ;)
                                    >

                                    Hoek , i have tried something with procedure but i am not so convinced. please have a look at my procedure and suggest me if there are any better ways.
                                    A counter-question:
                                    How did you get to sum (ob_wt) = 25,318.96 for batchno 0002?
                                    Based on running totals I get 25066.831 as first value to split on for HEA200 (sl_pm_code is @ 1239606-2047 then)...
                                    >

                                    i got the sum(ob_wt) =25,318.96 for batchno 0002 by my procedure which checks for how many splits or serials and then using this splits or cnt column , i am assigning the serial numbers for each sl_desc , like cutting piece by piece into a smaller loop. please see the demonstration below.
                                    --with the following procedure and my previous inputs(insert statements) in ow_ship_det, i will get the data in os_batch table as follows.
                                    
                                    
                                    SQL> DECLARE
                                      2     CURSOR c1
                                      3     IS
                                      4        SELECT   sl_desc, SUM (sl_wt) total_wt, SUM (sl_qty) total_qty,
                                      5                 CEIL (GREATEST (SUM (sl_wt) / 50000, SUM (sl_qty) / 1000)
                                      6                      ) no_of_splits
                                      7            FROM ow_ship_det
                                      8        GROUP BY sl_desc;
                                      9  
                                     10     CURSOR c2 (p_desc VARCHAR2)
                                     11     IS
                                     12        SELECT   sl_pm_code, sl_desc, sl_wt, sl_qty
                                     13            FROM ow_ship_det
                                     14           WHERE sl_desc = p_desc
                                     15        ORDER BY sl_wt DESC, sl_pm_code DESC;
                                     16  
                                     17     CURSOR c3
                                     18     IS
                                     19        SELECT LPAD (NVL (MAX (TO_NUMBER (NVL (ob_batch, '0'))), 0) + 1,
                                     20                     4,
                                     21                     '0'
                                     22                    ) AS new_batch
                                     23          FROM os_batch;
                                     24  
                                     25     no_of_batch_req       NUMBER;
                                     26     no_of_batch           NUMBER;
                                     27     last_batch_no         VARCHAR2 (5);
                                     28     new_batch_no          VARCHAR2 (5);
                                     29     first_batch_in_prof   VARCHAR2 (5);
                                     30  BEGIN
                                     31     IF c3%ISOPEN
                                     32     THEN
                                     33        CLOSE c3;
                                     34     END IF;
                                     35  
                                     36     OPEN c3;
                                     37  
                                     38     FETCH c3
                                     39      INTO last_batch_no;
                                     40  
                                     41     CLOSE c3;
                                     42  
                                     43     new_batch_no := last_batch_no;
                                     44     first_batch_in_prof := last_batch_no;
                                     45  
                                     46     FOR i IN c1
                                     47     LOOP
                                     48        no_of_batch_req := i.no_of_splits;
                                     49        no_of_batch := 1;
                                     50  
                                     51        FOR j IN c2 (i.sl_desc)
                                     52        LOOP
                                     53           INSERT INTO os_batch
                                     54                       (ob_batch, ob_pm_code, ob_desc, ob_wt,
                                     55                        ob_qty
                                     56                       )
                                     57                VALUES (new_batch_no, j.sl_pm_code, j.sl_desc, j.sl_wt,
                                     58                        j.sl_qty
                                     59                       );
                                     60  
                                     61           IF no_of_batch = no_of_batch_req
                                     62           THEN
                                     63              no_of_batch := 1;
                                     64              new_batch_no := first_batch_in_prof;
                                     65           ELSE
                                     66              no_of_batch := no_of_batch + 1;
                                     67              new_batch_no := LPAD (((new_batch_no + 1)), 4, '0');
                                     68           END IF;
                                     69        END LOOP;
                                     70  
                                     71        new_batch_no :=
                                     72                       LPAD (((first_batch_in_prof + i.no_of_splits)), 4, '0');
                                     73        first_batch_in_prof := new_batch_no;
                                     74     END LOOP;
                                     75  
                                     76     COMMIT;
                                     77  END;
                                     78  /
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    
                                    
                                    SQL> column ob_desc  format a9
                                    SQL> column ob_type  format a7
                                    SQL> select * from os_batch order by ob_batch, ob_pm_code;
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0001         1239606-1064 HEA160       211.837         14                       
                                    0001         1239606-1065 HEA160        171.62          8                       
                                    0001         1239606-1066 HEA160       387.442         20                       
                                    0001         1239606-1067 HEA160       387.442         20                       
                                    0001         1239606-1068 HEA160       197.646         10                       
                                    0001         1239606-1069 HEA160       197.646         10                       
                                    0001         1239606-1070 HEA160       175.161          8                       
                                    0001         1239606-1071 HEA160       175.161          8                       
                                    0001         1239606-1072 HEA160       175.161          8                       
                                    0001         1239606-1073 HEA160       175.161          8                       
                                    0001         1239606-1074 HEA160        437.83         28                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0001         1239606-1075 HEA160        437.83         28                       
                                    0001         1239606-1076 HEA160       230.175         14                       
                                    0001         1239606-1077 HEA160       230.175         14                       
                                    0001         1239606-1078 HEA160       230.175         14                       
                                    0001         1239606-1079 HEA160       230.175         14                       
                                    0001         1239606-1080 HEA160      4024.903        266                       
                                    0001         1239606-1081 HEA160       2118.33        140                       
                                    0001         1239606-1082 HEA160      1544.544         72                       
                                    0001         1239606-1083 HEA160      1372.928         64                       
                                    0001         1239606-1084 HEA160        686.46         32                       
                                    0001         1239606-1085 HEA160       514.851         24                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0001         1239606-1086 HEA160        171.62          8                       
                                    0001         1239606-1087 HEA160        97.154          7                       
                                    0001         1239606-1088 HEA160        97.154          7                       
                                    0001         1239606-1089 HEA160       121.893          7                       
                                    0001         1239606-1090 HEA160       121.893          7                       
                                    0002         1239606-1030 HEA200       362.982         38                       
                                    0002         1239606-1034 HEA200       326.585         32                       
                                    0002         1239606-1035 HEA200       353.287         34                       
                                    0002         1239606-1038 HEA200       325.735         35                       
                                    0002         1239606-1043 HEA200       319.058         24                       
                                    0002         1239606-1045 HEA200      1880.448        192                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0002         1239606-1046 HEA200       313.408         32                       
                                    0002         1239606-1049 HEA200       359.095         38                       
                                    0002         1239606-1051 HEA200       257.662         23                       
                                    0002         1239606-1055 HEA200       257.662         23                       
                                    0002         1239606-1056 HEA200       303.349         29                       
                                    0002         1239606-1062 HEA200       163.848          7                       
                                    0002         1239606-2024 HEA200       271.002         10                       
                                    0002         1239606-2026 HEA200       280.468         10                       
                                    0002         1239606-2029 HEA200       292.539         14                       
                                    0002         1239606-2031 HEA200       292.139         14                       
                                    0002         1239606-2032 HEA200      1640.658         72                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0002         1239606-2036 HEA200       291.718         14                       
                                    0002         1239606-2042 HEA200       235.764          5                       
                                    0002         1239606-2043 HEA200       471.538         10                       
                                    0002         1239606-2046 HEA200       151.251          5                       
                                    0002         1239606-2049 HEA200       151.251          5                       
                                    0002         1239606-2054 HEA200       256.347          6                       
                                    0002         1239606-2057 HEA200       235.764          5                       
                                    0002         1239606-2058 HEA200      1414.602         30                       
                                    0002         1239606-2062 HEA200       1178.84         25                       
                                    0002         1239606-2064 HEA200       235.764          5                       
                                    0002         1239606-2066 HEA200       193.508          5                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0002         1239606-2068 HEA200       387.026         10                       
                                    0002         1239606-2071 HEA200       193.508          5                       
                                    0002         1239606-2074 HEA200        80.884          3                       
                                    0002         1239606-2078 HEA200        93.029          4                       
                                    0002         1239606-2084 HEA200       122.855          3                       
                                    0002         1239606-2085 HEA200       389.466         12                       
                                    0002         1239606-2090 HEA200        80.599          3                       
                                    0002         1239606-2093 HEA200       135.756          8                       
                                    0002         1239606-2094 HEA200      5741.136         96                       
                                    0002         1239606-2095 HEA200      1435.296         24                       
                                    0002         1239606-2097 HEA200       536.818         10                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0002         1239606-2100 HEA200       525.876         10                       
                                    0002         1239606-2108 HEA200       139.013          4                       
                                    0002         1239606-2111 HEA200       262.343          5                       
                                    0002         1239606-2113 HEA200       141.945          3                       
                                    0002         1239606-4004 HEA200       235.371          6                       
                                    0002         1239606-4007 HEA200       234.728          7                       
                                    0002         1239606-4010 HEA200       264.171          8                       
                                    0002         1239606-4014 HEA200       215.044          4                       
                                    0002         1239606-4015 HEA200       645.132         12                       
                                    0002         1239606-4019 HEA200       215.044          4                       
                                    0002         1239606-4021 HEA200       212.603          2                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0002         1239606-4030 HEA200       215.044          4                       
                                    0003         1239606-1029 HEA200       362.982         38                       
                                    0003         1239606-1032 HEA200       362.982         38                       
                                    0003         1239606-1033 HEA200       394.068         44                       
                                    0003         1239606-1037 HEA200       325.735         35                       
                                    0003         1239606-1040 HEA200       325.735         35                       
                                    0003         1239606-1042 HEA200       319.058         24                       
                                    0003         1239606-1048 HEA200       359.095         38                       
                                    0003         1239606-1052 HEA200       303.349         29                       
                                    0003         1239606-1054 HEA200       257.662         23                       
                                    0003         1239606-2023 HEA200       271.002         10                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0003         1239606-2028 HEA200      1640.658         72                       
                                    0003         1239606-2030 HEA200       292.139         14                       
                                    0003         1239606-2035 HEA200       291.718         14                       
                                    0003         1239606-2039 HEA200       292.139         14                       
                                    0003         1239606-2041 HEA200      1640.658         72                       
                                    0003         1239606-2048 HEA200       151.251          5                       
                                    0003         1239606-2050 HEA200       235.764          5                       
                                    0003         1239606-2052 HEA200       258.853          6                       
                                    0003         1239606-2053 HEA200       256.347          6                       
                                    0003         1239606-2056 HEA200       256.347          6                       
                                    0003         1239606-2061 HEA200       235.764          5                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0003         1239606-2063 HEA200      1414.602         30                       
                                    0003         1239606-2065 HEA200       193.508          5                       
                                    0003         1239606-2070 HEA200       193.508          5                       
                                    0003         1239606-2072 HEA200       387.026         10                       
                                    0003         1239606-2073 HEA200        80.884          3                       
                                    0003         1239606-2075 HEA200        353.16         10                       
                                    0003         1239606-2076 HEA200       312.702          8                       
                                    0003         1239606-2077 HEA200       277.654          6                       
                                    0003         1239606-2082 HEA200       133.304          4                       
                                    0003         1239606-2083 HEA200       122.855          3                       
                                    0003         1239606-2089 HEA200        80.599          3                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0003         1239606-2091 HEA200       139.013          4                       
                                    0003         1239606-2096 HEA200      2870.232         48                       
                                    0003         1239606-2099 HEA200        532.09         10                       
                                    0003         1239606-2103 HEA200       928.218         24                       
                                    0003         1239606-2104 HEA200       523.076         10                       
                                    0003         1239606-2106 HEA200        91.138          4                       
                                    0003         1239606-2110 HEA200       210.068          2                       
                                    0003         1239606-2112 HEA200       141.945          3                       
                                    0003         1239606-2115 HEA200       151.516          4                       
                                    0003         1239606-2117 HEA200       141.945          3                       
                                    0003         1239606-4003 HEA200       235.371          6                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0003         1239606-4006 HEA200       235.371          6                       
                                    0003         1239606-4009 HEA200       264.171          8                       
                                    0003         1239606-4011 HEA200       645.132         12                       
                                    0003         1239606-4013 HEA200       215.044          4                       
                                    0003         1239606-4016 HEA200      1290.264         24                       
                                    0003         1239606-4018 HEA200       215.044          4                       
                                    0003         1239606-4029 HEA200       215.044          4                       
                                    0003         1239606-4032 HEA200       215.044          4                       
                                    0004         1239606-1031 HEA200       362.982         38                       
                                    0004         1239606-1036 HEA200       353.287         34                       
                                    0004         1239606-1039 HEA200       325.735         35                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0004         1239606-1041 HEA200       325.145         35                       
                                    0004         1239606-1044 HEA200       1567.04        160                       
                                    0004         1239606-1047 HEA200       313.408         32                       
                                    0004         1239606-1050 HEA200       359.095         38                       
                                    0004         1239606-1053 HEA200       257.662         23                       
                                    0004         1239606-1057 HEA200       303.349         29                       
                                    0004         1239606-1063 HEA200       163.848          7                       
                                    0004         1239606-1091 HEA200       336.028          8                       
                                    0004         1239606-2022 HEA200       271.002         10                       
                                    0004         1239606-2025 HEA200       271.002         10                       
                                    0004         1239606-2027 HEA200       139.013          4                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0004         1239606-2033 HEA200       292.539         14                       
                                    0004         1239606-2034 HEA200       291.718         14                       
                                    0004         1239606-2037 HEA200       291.718         14                       
                                    0004         1239606-2038 HEA200       292.139         14                       
                                    0004         1239606-2040 HEA200      1640.658         72                       
                                    0004         1239606-2044 HEA200       471.538         10                       
                                    0004         1239606-2045 HEA200       235.764          5                       
                                    0004         1239606-2047 HEA200       151.251          5                       
                                    0004         1239606-2051 HEA200       258.853          6                       
                                    0004         1239606-2055 HEA200       256.347          6                       
                                    0004         1239606-2059 HEA200      1414.602         30                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0004         1239606-2060 HEA200       235.764          5                       
                                    0004         1239606-2067 HEA200       193.508          5                       
                                    0004         1239606-2069 HEA200       387.026         10                       
                                    0004         1239606-2079 HEA200        93.029          4                       
                                    0004         1239606-2080 HEA200       193.508          5                       
                                    0004         1239606-2081 HEA200       133.304          4                       
                                    0004         1239606-2086 HEA200       389.466         12                       
                                    0004         1239606-2087 HEA200        91.138          4                       
                                    0004         1239606-2088 HEA200        80.599          3                       
                                    0004         1239606-2092 HEA200       591.006         12                       
                                    0004         1239606-2098 HEA200       531.806         10                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0004         1239606-2101 HEA200       787.848         16                       
                                    0004         1239606-2102 HEA200       1969.61         40                       
                                    0004         1239606-2105 HEA200       263.449          5                       
                                    0004         1239606-2107 HEA200        80.599          3                       
                                    0004         1239606-2109 HEA200       139.013          4                       
                                    0004         1239606-2114 HEA200       151.516          4                       
                                    0004         1239606-2116 HEA200       141.945          3                       
                                    0004         1239606-4001 HEA200       236.607          3                       
                                    0004         1239606-4005 HEA200       235.371          6                       
                                    0004         1239606-4008 HEA200       234.728          7                       
                                    0004         1239606-4012 HEA200      1290.264         24                       
                                    
                                    OB_BATCH     OB_PM_CODE   OB_DESC        OB_WT     OB_QTY                       
                                    ------------ ------------ --------- ---------- ----------                       
                                    0004         1239606-4017 HEA200       215.044          4                       
                                    0004         1239606-4020 HEA200       215.044          4                       
                                    0004         1239606-4022 HEA200       212.603          2                       
                                    0004         1239606-4031 HEA200       215.044          4                       
                                    
                                    180 rows selected.
                                    
                                     1  select ob_batch,ob_desc,sum(ob_wt),sum(ob_qty) from os_batch
                                      2* group by ob_batch,ob_desc
                                    SQL> /
                                    
                                    OB_BATCH     OB_DESC   SUM(OB_WT) SUM(OB_QTY)                                   
                                    ------------ --------- ---------- -----------                                   
                                    0002         HEA200     25318.959         989                                   
                                    0003         HEA200     21642.834         804                                   
                                    0004         HEA200     20254.562         856                                   
                                    0001         HEA160     14922.367         860  
                                    
                                    -- if you see above i am getting the total in balanced based on both the conditions sum(sl_wt) > 50000 and sum(sl_qty) > 1000 since sum(sl_wt) in case of HEA200 was 67216.355 and 2649 , i am using ceil function based on mam barbara idea to find out which one is greatest amont sum(sl_wt)/50000 or sum(sl_qty/1000) for split selection or preference, or maxmum of spilts that each sl_desc must have , in HEA200 case tot_qty is 2649 which is greater and if you divide it by 1000 , you will ceil of 3 which is 3 splits or it has to divided into 3 splits or 3 batches equally ,remember 3 equal batches hence the wt and qty got evenly distributed based on procedure.
                                    
                                    --In case of HEA160 the total wt and total qty falls within range and moreover the split is 1 only.
                                    
                                    SQL> 
                                    Wrote file afiedt.buf
                                    
                                      1  select sl_desc,sum(sl_wt) tot_wt,sum(sl_qty) tot_qty
                                      2  from ow_ship_det
                                      3* group by sl_Desc
                                    SQL> /
                                    
                                    SL_DESC                  TOT_WT    TOT_QTY
                                    -------------------- ---------- ----------
                                    HEA160                14922.367        860
                                    HEA200                67216.355       2649
                                    i hope i have explained it clearly.
                                    1 2 3 Previous Next