121 lines
3.8 KiB
Markdown
121 lines
3.8 KiB
Markdown
# GET_REPORT_CONSUNTIVO_PER_DATA
|
|
|
|
## Colonne
|
|
|
|
| Colonna | Tipo |
|
|
|---------|------|
|
|
| ORDINE | NUMBER |
|
|
| ID | NUMBER |
|
|
| DATA | DATE |
|
|
| TIPO | VARCHAR2(4000) |
|
|
| NUMERO | NUMBER |
|
|
| COSTO | NUMBER |
|
|
| SCONTO | NUMBER |
|
|
| PERCIVA | NUMBER |
|
|
| TOTALE | NUMBER |
|
|
| IVA | NUMBER |
|
|
| TOTALE_IVATO | NUMBER |
|
|
|
|
## Definizione
|
|
|
|
```sql
|
|
CREATE OR REPLACE VIEW GET_REPORT_CONSUNTIVO_PER_DATA AS
|
|
select
|
|
edo.ordine,
|
|
e.id,
|
|
e.data,
|
|
nvl2(edo.note, tto.descrizione || ': ' || edo.note, tto.descrizione) as tipo,
|
|
nullif(edo.numero, 0) as numero,
|
|
nullif(edo.costo, 0) as costo,
|
|
nullif(edo.sconto, 0) as sconto,
|
|
nullif(case when edo.numero > 0 then 0.10 else 0 end, 0) as perciva,
|
|
nullif(edo.numero * (edo.COSTO - (edo.COSTO*(edo.sconto/100))), 0) as totale,
|
|
nullif((edo.numero * (edo.COSTO - (edo.COSTO*(edo.sconto/100))) * 0.10), 0) as iva,
|
|
edo.numero * (edo.COSTO - (edo.COSTO*(edo.sconto/100))) + (edo.numero * (edo.COSTO - (edo.COSTO*(edo.sconto/100))) * 0.10) as totale_ivato
|
|
from eventi e
|
|
left join eventi_det_ospiti edo on edo.id_evento = e.id
|
|
left join tb_tipi_ospiti tto on tto.cod_tipo = edo.cod_tipo_ospite
|
|
where edo.ordine != 3
|
|
|
|
union all
|
|
|
|
select
|
|
rownum+49 as ordine,
|
|
e.id_evento as id,
|
|
d.data,
|
|
'Degustazioni effettuate' as tipo,
|
|
nullif(numero, 0) as numero,
|
|
nullif(costo, 0) as costo,
|
|
null as sconto,
|
|
null as perciva,
|
|
nullif(costo*-1, 0) as totale,
|
|
null as iva,
|
|
costo*-1 as totale_ivato
|
|
from get_costo_degus_evt e
|
|
join eventi d on d.id = e.id_evento
|
|
|
|
union all
|
|
|
|
select rownum+99 as ordine, t."ID",t."DATA",t."TIPO",t."NUMERO",t."COSTO",t."SCONTO",t."PERCIVA",t."TOTALE",t."IVA",t."TOTALE_IVATO" from (
|
|
select
|
|
e.id_evento as id,
|
|
d.data,
|
|
replace(REGEXP_REPLACE(replace(a.descrizione, ' ', '_'), '[^0-9A-Za-z_()]', ''), '_', ' ') as tipo,
|
|
nullif(e.numero, 0) as numero,
|
|
nullif(e.costo_uni, 0) as costo,
|
|
null as sconto,
|
|
nullif(case when e.COSTO > 0 then (a.perc_iva/100) else 0 end, 0) as perciva,
|
|
nullif(e.COSTO, 0) as totale,
|
|
nullif(case when e.COSTO > 0 then e.COSTO_IVATO - e.COSTO else 0 end, 0) as iva,
|
|
nullif(case when e.COSTO > 0 then e.COSTO_IVATO else 0 end, 0) as totale_ivato
|
|
from get_costo_art_evt e
|
|
join articoli a on e.cod_articolo = a.cod_articolo
|
|
join EVENTI_DET_OSPITI edoa on edoa.id_evento = e.id_evento and edoa.cod_tipo_ospite = 8
|
|
join tb_codici_categ g on g.cod_categ = a.cod_categ
|
|
join eventi d on d.id = e.id_evento
|
|
where nvl(e.COSTO, 0) > 0
|
|
and g.show_print = 1
|
|
|
|
union all
|
|
|
|
select
|
|
e.id_evento as id,
|
|
d.data,
|
|
'Altri Articoli (dettaglio a pagina 3)' as tipo,
|
|
null as numero,
|
|
null as costo,
|
|
null as sconto,
|
|
null as perciva,
|
|
nullif(sum(e.COSTO), 0) as totale,
|
|
nullif(sum(case when e.COSTO > 0 then e.COSTO_IVATO - e.COSTO else 0 end), 0) as iva,
|
|
nullif(sum(case when e.COSTO > 0 then e.COSTO_IVATO else 0 end), 0) as totale_ivato
|
|
from get_costo_art_evt e
|
|
join articoli a on e.cod_articolo = a.cod_articolo
|
|
join EVENTI_DET_OSPITI edoa on edoa.id_evento = e.id_evento and edoa.cod_tipo_ospite = 8
|
|
join tb_codici_categ g on g.cod_categ = a.cod_categ
|
|
join eventi d on d.id = e.id_evento
|
|
where nvl(e.COSTO, 0) > 0
|
|
and g.show_print = 0
|
|
group by 'Altri Articoli', e.id_evento, d.data, 0, 0, 0, 0
|
|
) t
|
|
|
|
union all
|
|
|
|
select
|
|
ea.ordine+199 as ordine,
|
|
e.id,
|
|
e.data,
|
|
ea.descrizione as tipo,
|
|
ea.quantity as numero,
|
|
nullif(ea.costo, 0) as costo,
|
|
null as sconto,
|
|
nullif(case when ea.costo > 0 then 0.1 else 0 end, 0) as perciva,
|
|
nullif(ea.costo * ea.quantity, 0) as totale,
|
|
nullif(case when ea.COSTO > 0 then (ea.costo * ea.quantity) * 0.10 else 0 end, 0) as iva,
|
|
nullif(case when ea.COSTO > 0 then (ea.costo * ea.quantity) + ((ea.costo * ea.quantity) * 0.10) else (ea.costo * ea.quantity) end, 0) as totale_ivato
|
|
from eventi e
|
|
left join eventi_altricosti ea on ea.id_evento = e.id
|
|
where ea.costo is not null
|
|
order by 1
|
|
```
|