Hi , i have one table ow_ship_det, which contains Four fields sl_desc,sl_qty,sl_wt and sl_pm_code ,based on summary of sl_qty and sl_wt by sl_desc , i need to generate the serial numbers for group of sl_pm_code together , i want to add the qunatites and weight based on alternate rows per each sl_desc so that quantity and weight are evenly distributed and then i am inserting this result into another table called os_batch.test case as below.
CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);
row
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('D','H170',10000,300); --4
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('E','I100',25000,700); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('F','I100',25000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('G','J100',15000,200); --1
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('H','J100',15000,200); --2
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('I','J100',11000,200); --3
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('J','J100',11000,200); --4
commit;
--i will check the criteria based on this query whether sl_wt > 50000 and sl_qty > 1000, if they are more they will be under
--one serial number but with balanced weight and balanced qty eually or proportionally distributed by picking the alternate rows.
select a.*, sum (sl_wt) over(partition by sl_desc) sum_sl_wt,
sum (sl_qty) over
(partition by sl_desc) sum_sl_qty
from (
SELECT SL_PM_CODE,SL_DESC,SL_QTY,SL_WT FROM OW_SHIP_DET) a ;
SL_PM_CODE SL_DESC SL_QTY SL_WT SUM_SL_WT SUM_SL_QTY
A H170 300 15000 50000 1200 ---sl_qty is more than 1000 so we need 2 splits
B H170 300 15000 50000 1200
C H170 300 10000 50000 1200
D H170 300 10000 50000 1200
E I100 700 25000 50000 900 --sl_qty and sl_wt are withing range hence no splits
F I100 200 25000 50000 900
G J100 200 15000 52000 800 --weight is more than 50,000 so we need two splits as follows
H J100 200 15000 52000 800
I J100 200 11000 52000 800
J J100 200 11000 52000 800
--i want the result in the os_batch table as follows
CREATE TABLE OS_BATCH
(
OB_BATCH VARCHAR2(12 BYTE),
OB_PM_CODE VARCHAR2(12 BYTE),
OB_DESC VARCHAR2(30 BYTE),
OB_WT NUMBER,
OB_QTY NUMBER
);
SL_PM_CODE SL_DESC SL_QTY SL_WT SUM_SL_WT SUM_SL_QTY
A H170 300 15000 50000 1200 ---sl_qty is more than 1000 so we need 2 splits
B H170 300 15000 50000 1200
C H170 300 10000 50000 1200
D H170 300 10000 50000 1200
E I100 700 25000 50000 900 --sl_qty and sl_wt are withing range hence no splits
F I100 200 25000 50000 900
G J100 200 15000 52000 800 --weight is more than 50,000 so we need two splits as follows
H J100 200 15000 52000 800
I J100 200 11000 52000 800
J J100 200 11000 52000 800
--Desired output in os_batch table
ob_batch OB_PM_CODE OB_DESC OB_QTY OB_WT
0001 A H170 300 15000
0001 C H170 300 10000
0002 B H170 300 15000
0002 D H170 300 10000
0003 E I100 700 25000
0003 F I100 200 25000
0004 G J100 200 15000
0004 I J100 200 11000
0005 H J100 200 15000
0005 J J100 200 11000