1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| select distinct "ITEMS_IN_CARTON",
"WT_NET_PER_CARTON" as net_weight,
"WT_GROSS_PER_CARTON" as gross_weight,
"CTN_DIM_L" as length,
"CTN_DIM_W" as width,
"CTN_DIM_H" as height,
"INVOICE_NO"
from (select listagg(t.item_no || '|' || t.qty_per_carton_item, ',') within group(order by t.item_no) as items_in_carton,
sum(t.wt_net_per_carton) as wt_net_per_carton,
sum(t.wt_net_per_carton) + t.unit_wt_ctn_item as wt_gross_per_carton,
t.ctn_dim_l,
t.ctn_dim_w,
t.ctn_dim_h,
t.invoice_no
from (select scd.fg_qty as qty_per_carton_item,
scd.unit_wt_fg_item * scd.fg_qty as wt_net_per_carton,
scd.unit_wt_ctn_item,
scd.ctn_dim_l,
scd.ctn_dim_w,
scd.ctn_dim_h,
scd.item_no,
scd.pallet_seq,
not_null_pallet_ctn_seq as pallet_ctn_seq,
seh.invoice_no,
seh.invoice_pack_type
from som_packing_headers seh,
(select s.*,
nvl(s.pallet_ctn_seq, row_number() over (partition by s.packing_header_id order by s.pallet_ctn_seq)) as not_null_pallet_ctn_seq
from som_packing_plt_ctn_details s) scd
where seh.packing_header_id = scd.packing_header_id) t
group by t.pallet_seq,
t.pallet_ctn_seq,
t.ctn_dim_l,
t.ctn_dim_w,
t.ctn_dim_h,
t.unit_wt_ctn_item,
t.invoice_no)
where invoice_no = p_invoice_no;
|
