SQL & PL/SQL

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