scripts-pde/resources/get_titres.sql

/* Selectionne les titres depuis ASTRE GF pour import dans Analytique */
/* ATTENTION: {annee_deb} et {annee_fin} doivent être mis à jour à l'exécution avec un query.format() */
SELECT
  REAL_REC.REF_INT_PER,
  REAL_REC.NUM_TITRE,
  to_date(REAL_REC.DAT_PER,'J'),
  INFO_COL2.COD_COLL,
  INFO_BUD2.COD_BUDG,
  REAL_REC.NUM_EXBUDG,
  REAL_REC.NUM_BJ
FROM
  INFO_COL  INFO_COL2,
  INFO_BUD  INFO_BUD2,
  REAL_REC,
  W_CAD_CPT,
  CUM_ENVPHASE
WHERE
  ( CUM_ENVPHASE.COD_BUDG=W_CAD_CPT.COD_BUDG(+) and CUM_ENVPHASE.COD_COLL=W_CAD_CPT.COD_COLL(+) 
	  and CUM_ENVPHASE.NUM_ENV=W_CAD_CPT.NUM_ENV(+) and CUM_ENVPHASE.NUM_EXBUDG=W_CAD_CPT.NUM_EXBUDG(+) 
	  and CUM_ENVPHASE.COD_COLL=upper('CG67') and CUM_ENVPHASE.COD_BUDG=decode('02','**',CUM_ENVPHASE.COD_BUDG,'02') 
	  and CUM_ENVPHASE.NUM_EXBUDG BETWEEN {annee_deb} and {annee_fin}  )
  AND  ( nvl(REAL_REC.IDPHASE(+),'xx')=nvl(CUM_ENVPHASE.IDPHASE,'xx') 
	  and nvl(REAL_REC.IDOPERAT(+),'xx')=nvl(CUM_ENVPHASE.IDOPERAT,'xx') 
	  and REAL_REC.COD_BUDG(+)=CUM_ENVPHASE.COD_BUDG and REAL_REC.COD_COLL(+)=CUM_ENVPHASE.COD_COLL 
	  and REAL_REC.NUM_ENV(+)=CUM_ENVPHASE.NUM_ENV and REAL_REC.NUM_EXBUDG(+)=CUM_ENVPHASE.NUM_EXBUDG 
	  and CUM_ENVPHASE.COD_COLL(+)=upper('CG67') and CUM_ENVPHASE.COD_BUDG(+)=decode('02','**',CUM_ENVPHASE.COD_BUDG(+),'02') 
	  and CUM_ENVPHASE.NUM_EXBUDG(+) BETWEEN {annee_deb} and {annee_fin}  )
  AND  ( REAL_REC.COD_COLL(+)=upper('CG67')  )
  AND  ( REAL_REC.COD_BUDG(+)=decode('02','**',REAL_REC.COD_BUDG(+),'02')  )
  AND  ( REAL_REC.NUM_EXBUDG(+) BETWEEN {annee_deb} and {annee_fin} )
  AND  ( W_CAD_CPT.NUM_EXBUDG(+) BETWEEN {annee_deb} and {annee_fin} )
  AND  ( W_CAD_CPT.COD_COLL(+)=upper('CG67')  )
  AND  ( W_CAD_CPT.COD_BUDG(+)=decode('02','**',W_CAD_CPT.COD_BUDG(+),'02')  )
  AND  ( INFO_BUD2.COD_BUDG(+)=W_CAD_CPT.COD_BUDG and INFO_BUD2.COD_COLL(+)=W_CAD_CPT.COD_COLL and INFO_BUD2.NUM_EXBUDG(+)=W_CAD_CPT.NUM_EXBUDG  )
  AND  ( INFO_COL2.COD_COLL(+)=W_CAD_CPT.COD_COLL  )
  AND  
  (
  REAL_REC.REF_INT_PER  LIKE  'DDA%'
   AND
   to_date(REAL_REC.DAT_PER,'J') BETWEEN to_date('{annee_deb}-01-01','yyyy-mm-dd') AND to_date('{annee_fin}-12-31','yyyy-mm-dd')