expsigladb.View.V_COSTI_PDGP_USCITE.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of sigla-backend Show documentation
There is a newer version: 6.6.11
--------------------------------------------------------
--  DDL for View V_COSTI_PDGP_USCITE
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "V_COSTI_PDGP_USCITE" ("ESERCIZIO", "CD_CENTRO_RESPONSABILITA", "CD_DIPARTIMENTO", "PG_PROGETTO", "CD_PROGETTO", "CD_COMMESSA", "CD_LIVELLO1", "DS_CLASSIFICAZIONE", "IM_PRESUNTI_PREC", "IM_COSTI_FIGURATIVI", "IM_COSTI_GENERALI", "IM_DEC_IST_INT", "IM_DEC_IST_EST", "IM_DEC_AREA_INT", "IM_DEC_AREA_EST", "IMP_TOT_DEC_INT", "IMP_TOT_DEC_EST", "IMP_TOT_DECENTRATO", "TRATT_ECON_INT", "TRATT_ECON_EST", "IM_ACC_ALTRE_SP_INT", "IMP_TOT_COMP_EST", "IMP_TOT_COMP_INT", "IM_PREV_A2", "IM_PREV_A3") AS 
  SELECT --CLASSIFICAZIONE_VOCI.TI_GESTIONE,
  V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO,V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA,V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_DIPARTIMENTO,V_CONS_PDG_SPE_BIL_IST_DIP_FO.PG_PROGETTO,
  V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_PROGETTO,V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_COMMESSA,V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_LIVELLO1,CLASSIFICAZIONE_VOCI.DS_CLASSIFICAZIONE,
  Decode (V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_LIVELLO1,'1',(SELECT SUM(nvl(PDG_MODULO_COSTI.RIS_PRES_ES_PREC_TIT_I,0))
          FROM
              PDG_MODULO_COSTI WHERE
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO=PDG_MODULO_COSTI.ESERCIZIO ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA=PDG_MODULO_COSTI.CD_CENTRO_RESPONSABILITA ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.PG_MODULO=PDG_MODULO_COSTI.PG_PROGETTO(+) )),'2',(SELECT SUM(nvl(PDG_MODULO_COSTI.RIS_PRES_ES_PREC_TIT_II,0))
          FROM
              PDG_MODULO_COSTI WHERE
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO=PDG_MODULO_COSTI.ESERCIZIO ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA=PDG_MODULO_COSTI.CD_CENTRO_RESPONSABILITA ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.PG_MODULO=PDG_MODULO_COSTI.PG_PROGETTO(+) )),0) IM_PRESUNTI_PREC,Decode (V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_LIVELLO1,'2',(SELECT nvl(sum(NVL(PDG_MODULO_COSTI.IM_CF_TFR,0) + NVL(PDG_MODULO_COSTI.IM_CF_AMM_IMMOBILI,0)+ NVL(PDG_MODULO_COSTI.IM_CF_AMM_ATTREZZ,0)+ NVL(PDG_MODULO_COSTI.IM_CF_AMM_ALTRO,0)),0)
          FROM
              PDG_MODULO_COSTI WHERE
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO=PDG_MODULO_COSTI.ESERCIZIO ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA=PDG_MODULO_COSTI.CD_CENTRO_RESPONSABILITA ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.PG_MODULO=PDG_MODULO_COSTI.PG_PROGETTO(+) ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO=PDG_MODULO_COSTI.ESERCIZIO ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA=PDG_MODULO_COSTI.CD_CENTRO_RESPONSABILITA ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.PG_MODULO=PDG_MODULO_COSTI.PG_PROGETTO(+) )),0) IM_COSTI_FIGURATIVI,Decode (V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_LIVELLO1,'2',(SELECT nvl(SUM(nvl(PDG_MODULO_COSTI.IM_COSTI_GENERALI,0)),0)
          FROM
              PDG_MODULO_COSTI WHERE
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO=PDG_MODULO_COSTI.ESERCIZIO ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA=PDG_MODULO_COSTI.CD_CENTRO_RESPONSABILITA ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.PG_MODULO=PDG_MODULO_COSTI.PG_PROGETTO(+) ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO=PDG_MODULO_COSTI.ESERCIZIO ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA=PDG_MODULO_COSTI.CD_CENTRO_RESPONSABILITA ) AND
          ( V_CONS_PDG_SPE_BIL_IST_DIP_FO.PG_MODULO=PDG_MODULO_COSTI.PG_PROGETTO(+) )),0) IM_COSTI_GENERALI,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_IST_INT),0) IM_DEC_IST_INT,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_IST_EST),0) IM_DEC_IST_EST,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_AREA_INT),0) IM_DEC_AREA_INT,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_AREA_EST),0) IM_DEC_AREA_EST,NVL(SUM(nvl(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_IST_INT,0)),0)+NVL(SUM(nvl(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_AREA_INT,0)),0) IMP_TOT_DEC_INT,NVL(SUM(nvl(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_IST_EST,0)),0)+NVL(SUM(nvl(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_AREA_EST,0)),0) IMP_TOT_DEC_EST,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_AREA_EST),0)+NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_AREA_INT),0)+NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_IST_INT),0)+NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_IST_EST),0) IMP_TOT_DECENTRATO,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.TRATT_ECON_INT),0) TRATT_ECON_INT,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.TRATT_ECON_EST),0) TRATT_ECON_EST,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_ACC_ALTRE_SP_INT),0) IM_ACC_ALTRE_SP_INT,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.TRATT_ECON_EST),0)+NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_IST_EST),0)+NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_AREA_EST),0) IMP_TOT_COMP_EST,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.TRATT_ECON_INT),0)+NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_IST_INT),0)+NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_DEC_AREA_INT),0)+NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_ACC_ALTRE_SP_INT),0) IMP_TOT_COMP_INT,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_PREV_A2),0) IM_PREV_A2,NVL(SUM(V_CONS_PDG_SPE_BIL_IST_DIP_FO.IM_PREV_A3),0) IM_PREV_A3
FROM
    V_CONS_PDG_SPE_BIL_IST_DIP_FO,
    CLASSIFICAZIONE_VOCI,
    V_CDR_VALIDO_LIV1 
WHERE
( V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO=CLASSIFICAZIONE_VOCI.ESERCIZIO ) AND
( V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_LIVELLO1=CLASSIFICAZIONE_VOCI.CD_LIVELLO1 ) AND
( CLASSIFICAZIONE_VOCI.CD_LIVELLO2 IS NULL ) AND
( V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO=V_CDR_VALIDO_LIV1.ESERCIZIO ) AND
( V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA=V_CDR_VALIDO_LIV1.CD_CENTRO_RESPONSABILITA ) AND
--( SUBSTR(V_CONS_PDG_SPE_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA,1,3) = '075' ) AND
( CLASSIFICAZIONE_VOCI.TI_GESTIONE = 'S' ) --AND --"Definisce la sezione (entrata o spesa) delle voci del piano dei conti finanziario Dominio: E=Entrate S=Spese"
--( V_CDR_VALIDO_LIV1.CD_CENTRO_RESPONSABILITA = '075.000.000' ) AND
--( V_CDR_VALIDO_LIV1.ESERCIZIO = 2017 ) 
--(( V_CONS_PDG_SPE_BIL_IST_DIP_FO.ESERCIZIO = 2017 ))
GROUP BY v_cons_pdg_spe_bil_ist_dip_fo.esercizio,v_cons_pdg_spe_bil_ist_dip_fo.cd_centro_responsabilita,v_cons_pdg_spe_bil_ist_dip_fo.cd_dipartimento,v_cons_pdg_spe_bil_ist_dip_fo.cd_progetto,
	v_cons_pdg_spe_bil_ist_dip_fo.pg_progetto,v_cons_pdg_spe_bil_ist_dip_fo.cd_commessa,v_cons_pdg_spe_bil_ist_dip_fo.cd_livello1,classificazione_voci.ds_classificazione,V_CONS_PDG_SPE_BIL_IST_DIP_FO.PG_MODULO, CLASSIFICAZIONE_VOCI.TI_GESTIONE;