1 Reply Latest reply on Apr 7, 2013 5:58 AM by HamidHelal

    Generate Serial numbers based on criterias

    NuhaArif
      I have a data on which i need to set serial numbers based on two conditions , one is weight and the other is qty , its like cummulative total of weight and qty based on description of profile , if the description changes the accumulation starts again and continues till qty reaches 50 pcs or wieght reaches 50, please find below a test case.
      the following is the detail table
      
      create table ow_temp_data ( tmp_no varchar2(12),tmp_pm_code varchar2(12),tmp_ps_code varchar2(12),tmp_desc varchar2(30),tmp_wt number ,tmp_qty number,TMP_TYPE VARCHAR2(2) );
      
      insert into ow_temp_data (tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','A' , NULL,'H170',25  ,0,'W' );
      insert into ow_temp_data ( tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','A' ,'A01' ,'HEA100' ,0  ,10,'WI' );
      insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','B' , NULL,'H170',25  ,0,'W' );
      insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','B' ,'B01' ,'HEA100' ,0  ,10,'WI' );
      insert into ow_temp_data (tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','C' , NULL,'HB120',25  ,0,'W' );
      insert into ow_temp_data ( tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','C' ,'C01' ,'HB100' ,0  ,10,'WI' );
      insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','D' , NULL,'HB120',5  ,0,'W' );
      insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','D' ,'D01' ,'HB100' ,0  ,50,'WI' );
      insert into ow_temp_data (tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','E' , NULL,'PL120',30  ,0,'W' );
      insert into ow_temp_data ( tmp_no, tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','E' ,'E01' ,'P100' ,0  ,10,'WI' );
      insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','F' , NULL,'PL120',35  ,0,'W' );
      insert into ow_temp_data ( tmp_no,tmp_pm_code ,tmp_ps_code ,tmp_desc ,tmp_wt  ,tmp_qty ,tmp_type ) values ('0','F' ,'F01' ,'P100' ,0  ,30,'WI' );
      
      --now the query
      
          SELECT b.tmp_pm_code tmp_pm_code, b.tmp_desc tmp_desc, b.tmp_wt tmp_wt,
                   b.tmp_qty tmp_qty, b.tmp_type tmp_type,
                   (SELECT SUM (a.tmp_qty)
                      FROM ow_temp_data a
                     WHERE a.tmp_pm_code = b.tmp_pm_code
                       AND a.tmp_ps_code IS NOT NULL) att
              FROM ow_temp_data b
             WHERE b.tmp_ps_code IS NULL;
      
      
      
      TMP_PM_CODE     TMP_DESC     TMP_WT     TMP_QTY     TMP_TYPE     ATT
      A                      H170       25     0     W     10
      B                      H170       25     0     W     10
      C                    HB120       25     0     W     10
      D                    HB120         5     0     W     50
      E                    PL120       30     0     W     10
      F                    PL120       35     0     W     30
      --now what i want as output is to insert this data along with serial numbers or batch numbers into another table called os_batch
      
      CREATE TABLE OS_BATCH
      (  OB_BATCH    VARCHAR2(12 BYTE),
        OB_PM_CODE  VARCHAR2(12 BYTE),
        OB_DESC     VARCHAR2(30 BYTE),
        OB_WT       NUMBER,
        OB_QTY      NUMBER,
        OB_TYPE     VARCHAR2(2 BYTE)
      )
      
      -- I want the final output as follows in the os_batch table.
      
      OB_BATCH     OB_PM_CODE     OB_DESC     OB_WT     OB_QTY     OB_TYPE
      0001           A                        H170       25     10     W
      0001           B                        H170       25     10     W
      0002           C                      HB120       25     10     W
      
      0003           D                      HB120       1     10     W
      0004           D                      HB120       1     10     W   --in this case qty was 50 since each batch must not exceed 10 it got splitted into 5 with appropriate wt 
      0005           D                      HB120       1     10     W
      0006           D                      HB120       1     10     W
      0007           D                      HB120       1     10     W
      
      
      0008           E                      PL120       30     10     W
      0009           F                      PL120       8.75     10     W
      0010           F                      PL120       8.75     10     W    --in this  case qty was 50 and wt was more than 50 so qty took the priority and hence 5 rows
      0011           F                      PL120       8.75     10     W
      0012           F                      PL120       8.75     10     W
      
      
      [/CODE]