19 KiB
19 KiB
F_REP_ALLESTIMENTI
Codice Sorgente
FUNCTION "F_REP_ALLESTIMENTI" ( p_data_in IN varchar2 default to_char(sysdate,'YYYYMMD'),
p_data_fi IN varchar2 default to_char(sysdate + 30,'YYYYMMD')
)
RETURN t_rep_allestimenti_tab PIPELINED AS
v_data_in varchar2(100);
v_data_fi varchar2(100);
cursor c_evento is
with t as (--select e.id, e.data, l.location, e.torta, e.confettata, e.stampa_menu
select e.id, e.data, l.location, e.torta,
e.altro_a as confettata,
e.sedia as stampa_menu
from eventi e
left join location l on e.id_location = l.id
--where data >= sysdate
where e.data between to_date(v_data_in,'YYYYMMDD') and to_date(v_data_fi,'YYYYMMDD')
and e.stato in (300, 400) -- 300 Scheda Confermata, 400 Confermato (Lista)
and e.disabled = 0
and e.deleted = 0
order by data, to_number(to_char(e.ora_evento,'HH24MI'))
)
, q as (select t.* , rownum as order_id
from t
order by data
)
, r as (select
case when order_id =1 then id else null end as id1,
case when order_id =2 then id else null end as id2,
case when order_id =3 then id else null end as id3,
case when order_id =4 then id else null end as id4,
case when order_id =5 then id else null end as id5,
case when order_id =6 then id else null end as id6,
case when order_id =7 then id else null end as id7,
case when order_id =8 then id else null end as id8,
case when order_id =9 then id else null end as id9,
case when order_id =10 then id else null end as id10,
case when order_id =11 then id else null end as id11,
case when order_id =12 then id else null end as id12,
case when order_id =1 then data else null end as data1,
case when order_id =2 then data else null end as data2,
case when order_id =3 then data else null end as data3,
case when order_id =4 then data else null end as data4,
case when order_id =5 then data else null end as data5,
case when order_id =6 then data else null end as data6,
case when order_id =7 then data else null end as data7,
case when order_id =8 then data else null end as data8,
case when order_id =9 then data else null end as data9,
case when order_id =10 then data else null end as data10,
case when order_id =11 then data else null end as data11,
case when order_id =12 then data else null end as data12,
case when order_id =1 then location else null end as location1,
case when order_id =2 then location else null end as location2,
case when order_id =3 then location else null end as location3,
case when order_id =4 then location else null end as location4,
case when order_id =5 then location else null end as location5,
case when order_id =6 then location else null end as location6,
case when order_id =7 then location else null end as location7,
case when order_id =8 then location else null end as location8,
case when order_id =9 then location else null end as location9,
case when order_id =10 then location else null end as location10,
case when order_id =11 then location else null end as location11,
case when order_id =12 then location else null end as location12,
case when order_id =1 then torta else null end as torta1,
case when order_id =2 then torta else null end as torta2,
case when order_id =3 then torta else null end as torta3,
case when order_id =4 then torta else null end as torta4,
case when order_id =5 then torta else null end as torta5,
case when order_id =6 then torta else null end as torta6,
case when order_id =7 then torta else null end as torta7,
case when order_id =8 then torta else null end as torta8,
case when order_id =9 then torta else null end as torta9,
case when order_id =10 then torta else null end as torta10,
case when order_id =11 then torta else null end as torta11,
case when order_id =12 then torta else null end as torta12,
case when order_id =1 then confettata else null end as confettata1,
case when order_id =2 then confettata else null end as confettata2,
case when order_id =3 then confettata else null end as confettata3,
case when order_id =4 then confettata else null end as confettata4,
case when order_id =5 then confettata else null end as confettata5,
case when order_id =6 then confettata else null end as confettata6,
case when order_id =7 then confettata else null end as confettata7,
case when order_id =8 then confettata else null end as confettata8,
case when order_id =9 then confettata else null end as confettata9,
case when order_id =10 then confettata else null end as confettata10,
case when order_id =11 then confettata else null end as confettata11,
case when order_id =12 then confettata else null end as confettata12,
case when order_id =1 then stampa_menu else null end as stampa_menu1,
case when order_id =2 then stampa_menu else null end as stampa_menu2,
case when order_id =3 then stampa_menu else null end as stampa_menu3,
case when order_id =4 then stampa_menu else null end as stampa_menu4,
case when order_id =5 then stampa_menu else null end as stampa_menu5,
case when order_id =6 then stampa_menu else null end as stampa_menu6,
case when order_id =7 then stampa_menu else null end as stampa_menu7,
case when order_id =8 then stampa_menu else null end as stampa_menu8,
case when order_id =9 then stampa_menu else null end as stampa_menu9,
case when order_id =10 then stampa_menu else null end as stampa_menu10,
case when order_id =11 then stampa_menu else null end as stampa_menu11,
case when order_id =12 then stampa_menu else null end as stampa_menu12
from q
)
select min(id1) as id1,
min(id2) as id2,
min(id3) as id3,
min(id4) as id4,
min(id5) as id5,
min(id6) as id6,
min(id7) as id7,
min(id8) as id8,
min(id9) as id9,
min(id10) as id10,
min(id11) as id11,
min(id12) as id12,
min(data1) as d1,
min(data2) as d2,
min(data3) as d3,
min(data4) as d4,
min(data5) as d5,
min(data6) as d6,
min(data7) as d7,
min(data8) as d8,
min(data9) as d9,
min(data10) as d10,
min(data11) as d11,
min(data12) as d12,
min(location1) as l1,
min(location2) as l2,
min(location3) as l3,
min(location4) as l4,
min(location5) as l5,
min(location6) as l6,
min(location7) as l7,
min(location8) as l8,
min(location9) as l9,
min(location10) as l10,
min(location11) as l11,
min(location12) as l12,
min(torta1) as t1,
min(torta2) as t2,
min(torta3) as t3,
min(torta4) as t4,
min(torta5) as t5,
min(torta6) as t6,
min(torta7) as t7,
min(torta8) as t8,
min(torta9) as t9,
min(torta10) as t10,
min(torta11) as t11,
min(torta12) as t12,
min(confettata1) as c1,
min(confettata2) as c2,
min(confettata3) as c3,
min(confettata4) as c4,
min(confettata5) as c5,
min(confettata6) as c6,
min(confettata7) as c7,
min(confettata8) as c8,
min(confettata9) as c9,
min(confettata10) as c10,
min(confettata11) as c11,
min(confettata12) as c12,
min(stampa_menu1) as SM1,
min(stampa_menu2) as SM2,
min(stampa_menu3) as SM3,
min(stampa_menu4) as SM4,
min(stampa_menu5) as SM5,
min(stampa_menu6) as SM6,
min(stampa_menu7) as SM7,
min(stampa_menu8) as SM8,
min(stampa_menu9) as SM9,
min(stampa_menu10) as SM10,
min(stampa_menu11) as SM11,
min(stampa_menu12) as SM12
from r;
c_evt c_evento%ROWTYPE;
type v_TOVAGLIATO_AR IS VARRAY(12) OF VARCHAR2(4000);
v_TOVAGLIATO v_TOVAGLIATO_AR;
C1 varchar2(100);
C2 varchar2(100);
C3 varchar2(100);
C4 varchar2(100);
C5 varchar2(100);
C6 varchar2(100);
C7 varchar2(100);
C8 varchar2(100);
C9 varchar2(100);
C10 varchar2(100);
C11 varchar2(100);
C12 varchar2(100);
type v_TOVAGLIOLO_AR IS VARRAY(12) OF VARCHAR2(1000);
v_TOVAGLIOLO v_TOVAGLIOLO_AR;
type v_AN_GELATO_AR IS VARRAY(12) OF VARCHAR2(1000);
v_AN_GELATO v_AN_GELATO_AR;
type v_AN_GELATO2_AR IS VARRAY(12) OF VARCHAR2(1000);
v_AN_GELATO2 v_AN_GELATO2_AR;
type v_AN_OPENBAR_AR IS VARRAY(12) OF VARCHAR2(1000);
v_AN_OPENBAR v_AN_OPENBAR_AR;
type v_AN_RUM_AR IS VARRAY(12) OF VARCHAR2(1000);
v_AN_RUM v_AN_RUM_AR;
T1 varchar2(100);
T2 varchar2(100);
T3 varchar2(100);
T4 varchar2(100);
T5 varchar2(100);
T6 varchar2(100);
T7 varchar2(100);
T8 varchar2(100);
T9 varchar2(100);
T10 varchar2(100);
T11 varchar2(100);
T12 varchar2(100);
v_appo varchar2(100);
v_dato varchar2(100);
v_id_str varchar2(100);
v_id number;
v_qry varchar2(1000);
type v_IDEVT_AR IS VARRAY(12) OF number;
v_IDEVT v_IDEVT_AR;
BEGIN
--default su date
if p_data_in is null then
v_data_in := to_char(sysdate,'YYYYMMD');
else
v_data_in := p_data_in;
end if;
if p_data_fi is null then
v_data_fi := to_char(sysdate + 30,'YYYYMMD');
else
v_data_fi := p_data_fi;
end if;
v_TOVAGLIATO := v_TOVAGLIATO_AR(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
v_TOVAGLIOLO := v_TOVAGLIOLO_AR(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
v_AN_GELATO := v_AN_GELATO_AR(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
v_AN_GELATO2 := v_AN_GELATO2_AR(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
v_AN_OPENBAR := v_AN_OPENBAR_AR(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
v_AN_RUM := v_AN_RUM_AR(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
v_IDEVT := v_IDEVT_AR(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
open c_evento;
fetch c_evento into c_evt;
--exit when c_evento%NOTFOUND;
--caricare su vettore gli id:
v_IDEVT(1) := c_evt.id1;
v_IDEVT(2) := c_evt.id2;
v_IDEVT(3) := c_evt.id3;
v_IDEVT(4) := c_evt.id4;
v_IDEVT(5) := c_evt.id5;
v_IDEVT(6) := c_evt.id6;
v_IDEVT(7) := c_evt.id7;
v_IDEVT(8) := c_evt.id8;
v_IDEVT(9) := c_evt.id9;
v_IDEVT(10) := c_evt.id10;
v_IDEVT(11) := c_evt.id11;
v_IDEVT(12) := c_evt.id12;
FOR i in 1 .. 12 LOOP
v_dato := '';
v_id := to_char(v_IDEVT(i));
v_TOVAGLIATO(i) := f_get_tovagliato_allestimento ('TVB', v_IDEVT(i) ) ;
v_AN_GELATO(i) := f_get_angolo_allestimento ('AN-GELAT', v_IDEVT(i) ) ;
v_AN_GELATO2(i) := f_get_angolo_allestimento ('AN-GEL-BOM', v_IDEVT(i) ) ;
--v_AN_OPENBAR(i) := f_get_angolo_allestimento ('OPEN-B', v_IDEVT(i) ) ;
v_AN_OPENBAR(i) := f_get_angolo_allestimento_OB ('%OPEN BAR%', v_IDEVT(i) ) ;
v_AN_RUM(i) := f_get_angolo_allestimento ('AN-RUM-CI', v_IDEVT(i) ) ;
END LOOP;
PIPE ROW(t_rep_allestimenti_row(c_evt.D1 ,
c_evt.D2 ,
c_evt.D3 ,
c_evt.D4 ,
c_evt.D5 ,
c_evt.D6 ,
c_evt.D7 ,
c_evt.D8 ,
c_evt.D9 ,
c_evt.D10 ,
c_evt.D11 ,
c_evt.D12 ,
c_evt.L1 ,
c_evt.L2 ,
c_evt.L3 ,
c_evt.L4 ,
c_evt.L5 ,
c_evt.L6 ,
c_evt.L7 ,
c_evt.L8 ,
c_evt.L9 ,
c_evt.L10 ,
c_evt.L11 ,
c_evt.L12 ,
v_TOVAGLIATO(1),
v_TOVAGLIATO(2),
v_TOVAGLIATO(3),
v_TOVAGLIATO(4),
v_TOVAGLIATO(5),
v_TOVAGLIATO(6),
v_TOVAGLIATO(7),
v_TOVAGLIATO(8),
v_TOVAGLIATO(9),
v_TOVAGLIATO(10),
v_TOVAGLIATO(11),
v_TOVAGLIATO(12),
c_evt.C1 ,
c_evt.C2 ,
c_evt.C3 ,
c_evt.C4 ,
c_evt.C5 ,
c_evt.C6 ,
c_evt.C7 ,
c_evt.C8 ,
c_evt.C9 ,
c_evt.C10 ,
c_evt.C11 ,
c_evt.C12 ,
v_TOVAGLIOLO(1),
v_TOVAGLIOLO(2),
v_TOVAGLIOLO(3),
v_TOVAGLIOLO(4),
v_TOVAGLIOLO(5),
v_TOVAGLIOLO(6),
v_TOVAGLIOLO(7),
v_TOVAGLIOLO(8),
v_TOVAGLIOLO(9),
v_TOVAGLIOLO(10),
v_TOVAGLIOLO(11),
v_TOVAGLIOLO(12),
v_AN_GELATO(1) || ', ' || v_AN_GELATO2(1),
v_AN_GELATO(2) || ', ' || v_AN_GELATO2(2),
v_AN_GELATO(3) || ', ' || v_AN_GELATO2(3),
v_AN_GELATO(4) || ', ' || v_AN_GELATO2(4),
v_AN_GELATO(5) || ', ' || v_AN_GELATO2(5),
v_AN_GELATO(6) || ', ' || v_AN_GELATO2(6),
v_AN_GELATO(7) || ', ' || v_AN_GELATO2(7),
v_AN_GELATO(8) || ', ' || v_AN_GELATO2(8),
v_AN_GELATO(9) || ', ' || v_AN_GELATO2(9),
v_AN_GELATO(10) || ', ' || v_AN_GELATO2(10),
v_AN_GELATO(11) || ', ' || v_AN_GELATO2(11),
v_AN_GELATO(12) || ', ' || v_AN_GELATO2(12),
v_AN_OPENBAR(1),
v_AN_OPENBAR(2),
v_AN_OPENBAR(3),
v_AN_OPENBAR(4),
v_AN_OPENBAR(5),
v_AN_OPENBAR(6),
v_AN_OPENBAR(7),
v_AN_OPENBAR(8),
v_AN_OPENBAR(9),
v_AN_OPENBAR(10),
v_AN_OPENBAR(11),
v_AN_OPENBAR(12),
v_AN_RUM(1),
v_AN_RUM(2),
v_AN_RUM(3),
v_AN_RUM(4),
v_AN_RUM(5),
v_AN_RUM(6),
v_AN_RUM(7),
v_AN_RUM(8),
v_AN_RUM(9),
v_AN_RUM(10),
v_AN_RUM(11),
v_AN_RUM(12),
c_evt.T1 ,
c_evt.T2 ,
c_evt.T3 ,
c_evt.T4 ,
c_evt.T5 ,
c_evt.T6 ,
c_evt.T7 ,
c_evt.T8 ,
c_evt.T9 ,
c_evt.T10 ,
c_evt.T11 ,
c_evt.T12 ,
c_evt.SM1 ,
c_evt.SM2 ,
c_evt.SM3 ,
c_evt.SM4 ,
c_evt.SM5 ,
c_evt.SM6 ,
c_evt.SM7 ,
c_evt.SM8 ,
c_evt.SM9 ,
c_evt.SM10 ,
c_evt.SM11 ,
c_evt.SM12
));
close c_evento;
RETURN;
END;```