• Home
• it.cnr.si.sigla
• sigla-backend
• 6.6.4
• source code
• V_COSTI_PDGP_ENTRATE.sql

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

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

--------------------------------------------------------
--  DDL for View V_COSTI_PDGP_ENTRATE
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "V_COSTI_PDGP_ENTRATE" ("ESERCIZIO", "CD_CENTRO_RESPONSABILITA", "CD_DIPARTIMENTO", "PG_PROGETTO", "CD_PROGETTO", "CD_COMMESSA", "CD_LIVELLO1", "DS_CLASSIFICAZIONE", "TOT_ENT_IST_A1", "TOT_ENT_IST_A2", "TOT_ENT_IST_A3", "TOT_ENT_AREE_A1", "TOT_ENT_AREE_A2", "TOT_ENT_AREE_A3", "TOT_ENT_A1", "TOT_ENT_A2", "TOT_ENT_A3") AS 
  (
SELECT V_CONS_PDG_ETR_BIL_IST_DIP_FO.ESERCIZIO,V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA,V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO,
V_CONS_PDG_ETR_BIL_IST_DIP_FO.PG_PROGETTO,V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO,V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA,V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_LIVELLO1,
CLASSIFICAZIONE_VOCI.DS_CLASSIFICAZIONE,NVL(SUM(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_IST_A1),0) TOT_ENT_IST_A1,NVL(SUM(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_IST_A2),0) TOT_ENT_IST_A2,
NVL(SUM(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_IST_A3),0) TOT_ENT_IST_A3,NVL(SUM(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_AREE_A1),0) TOT_ENT_AREE_A1,
NVL(SUM(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_AREE_A2),0) TOT_ENT_AREE_A2,NVL(SUM(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_AREE_A3),0) TOT_ENT_AREE_A3,
NVL(SUM(nvl(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_IST_A1,0)),0)+NVL(SUM(nvl(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_AREE_A1,0)),0) TOT_ENT_A1,
NVL(SUM(nvl(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_IST_A2,0)),0)+NVL(SUM(nvl(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_AREE_A2,0)),0) TOT_ENT_A2,
NVL(SUM(nvl(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_IST_A3,0)),0)+NVL(SUM(nvl(V_CONS_PDG_ETR_BIL_IST_DIP_FO.TOT_ENT_AREE_A3,0)),0) TOT_ENT_A3
FROM
        V_CONS_PDG_ETR_BIL_IST_DIP_FO,
        CLASSIFICAZIONE_VOCI,
        V_CDR_VALIDO_LIV1 
WHERE
--( SUBSTR(V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA,1,3) = '057' ) AND
( V_CONS_PDG_ETR_BIL_IST_DIP_FO.ESERCIZIO=CLASSIFICAZIONE_VOCI.ESERCIZIO ) AND
( CLASSIFICAZIONE_VOCI.TI_GESTIONE = 'E' ) AND
( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_LIVELLO1=CLASSIFICAZIONE_VOCI.CD_LIVELLO1 ) AND
( CLASSIFICAZIONE_VOCI.CD_LIVELLO2 IS NULL ) AND
( V_CONS_PDG_ETR_BIL_IST_DIP_FO.ESERCIZIO=V_CDR_VALIDO_LIV1.ESERCIZIO ) AND
( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA=V_CDR_VALIDO_LIV1.CD_CENTRO_RESPONSABILITA ) --AND
  --( V_CDR_VALIDO_LIV1.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  --( V_CDR_VALIDO_LIV1.ESERCIZIO = 2017 ) AND
  /*((( V_CONS_PDG_ETR_BIL_IST_DIP_FO.ESERCIZIO = 2017 )) AND
  ((((((((((((((((((((( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.048' )) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.054' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.073' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.075' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.079' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.081' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.083' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.090' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.091' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.160' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.161' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.166' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD001' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD001.167' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD003' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD003.049' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD003' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD003.199' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD003' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD003.209' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD003' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD003.215' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD003' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD003.223' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD005' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD005.111' ))) OR
  (( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_CENTRO_RESPONSABILITA = '057.000.000' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_DIPARTIMENTO = 'MD' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_PROGETTO = 'DFM.AD005' ) AND
  ( V_CONS_PDG_ETR_BIL_IST_DIP_FO.CD_COMMESSA = 'DFM.AD005.177' ))))*/
  GROUP BY v_cons_pdg_etr_bil_ist_dip_fo.esercizio,v_cons_pdg_etr_bil_ist_dip_fo.cd_centro_responsabilita,v_cons_pdg_etr_bil_ist_dip_fo.cd_dipartimento,
  v_cons_pdg_etr_bil_ist_dip_fo.cd_progetto,v_cons_pdg_etr_bil_ist_dip_fo.pg_progetto,v_cons_pdg_etr_bil_ist_dip_fo.cd_commessa,
  v_cons_pdg_etr_bil_ist_dip_fo.cd_livello1,classificazione_voci.ds_classificazione);