箱号
1
2
3
4
5
合并成 ,1,2,3,4,5
create or replace function get_carton_list (
p_pkt_ctrl_nbr in carton_hdr.pkt_ctrl_nbr%type,
p_sku_id in carton_hdr.sku_id%type,
p_carton_creation_code in carton_hdr.carton_creation_code%type
)
return varchar as
v_carton_list varchar2(2000);
begin
select text into v_carton_list from (
select row_number()over(partition by groupname order by groupname,lvl desc) rn,groupname,text from (
select a.groupname,level lvl,SYS_CONNECT_BY_PATH(a.carton_nbr_x_of_y,',') text
from (
select a.pkt_ctrl_nbr||'-'||a.sku_id||'-'||a.carton_creation_code groupname,
a.carton_nbr_x_of_y,row_number() over(partition by a.pkt_ctrl_nbr||'-'||a.sku_id||'-'||a.carton_creation_code order by a.carton_nbr_x_of_y) x
from (select distinct a.pkt_ctrl_nbr,b.sku_id,case when a.carton_creation_code in (5,26) then '拼箱' else '整箱' end carton_creation_code,a.carton_nbr_x_of_y from carton_hdr a inner join carton_dtl b on a.carton_nbr=b.carton_nbr
where 1=1 and a.stat_code='90' and a.pkt_ctrl_nbr=p_pkt_ctrl_nbr and b.sku_id=p_sku_id
and (case when a.carton_creation_code in (5,26) then '拼箱' else '整箱' end)=(case when p_carton_creation_code in (5,26) then '拼箱' else '整箱' end)
) a
) a connect by a.groupname=prior a.groupname and x-1=prior x
) t ) t where rn=1;
return v_carton_list;
exception
when others then
return null;
end;