scripts-pde/resources/get_factures.sql

/* Selectionne les factures 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
  cad_cpt.COD_COLL,
  cad_cpt.COD_BUDG,
  cad_cpt.NUM_EXBUDG,
  cad_cpt.COD_SECTION,
  cad_cpt.TYP_MVT,
  REAL_DEP.NUM_MANDAT,
  REAL_DEP.NUM_LIQ_MDT,
  REAL_DEP.NUM_LIG_MDT,
  round(sum(distinct decode(sign(REAL_DEP.num_exbudg - 2002), -1, REAL_DEP.MNT_TTC_MDT, REAL_DEP.MNT_TTC_MDT) + to_number(rawtohex(substr(rowidtochar(dbms_rowid.rowid_to_restricted(REAL_DEP.rowid, 0)), 1, 8)) || rawtohex(substr(rowidtochar(dbms_rowid.rowid_to_restricted(REAL_DEP.rowid, 0)), 10, 4)) || rawtohex(substr(rowidtochar(dbms_rowid.rowid_to_restricted(REAL_DEP.rowid, 0)), 15, 4))) / 1E38), 2),
  round(sum(distinct decode(sign(REAL_DEP.num_exbudg - 2002), -1, REAL_DEP.MNT_TVA_MDT, REAL_DEP.MNT_TVA_MDT) + to_number(rawtohex(substr(rowidtochar(dbms_rowid.rowid_to_restricted(REAL_DEP.rowid, 0)), 1, 8)) || rawtohex(substr(rowidtochar(dbms_rowid.rowid_to_restricted(REAL_DEP.rowid, 0)), 10, 4)) || rawtohex(substr(rowidtochar(dbms_rowid.rowid_to_restricted(REAL_DEP.rowid, 0)), 15, 4))) / 1E38), 2),
  round(sum(distinct decode(sign(VENT_D_SO.COD_EXEBUD - 2002), -1, VENT_D_SO.MNT_VENT, VENT_D_SO.MNT_VENT) + to_number(rawtohex(substr(rowidtochar(dbms_rowid.rowid_to_restricted(VENT_D_SO.rowid, 0)), 1, 8)) || rawtohex(substr(rowidtochar(dbms_rowid.rowid_to_restricted(VENT_D_SO.rowid, 0)), 10, 4)) || rawtohex(substr(rowidtochar(dbms_rowid.rowid_to_restricted(VENT_D_SO.rowid, 0)), 15, 4))) / 1E38), 2),
  VENT_D_SO.COD_AXE,
  AXEREA2.LIB_AXE,
  VENT_D_SO.COD_C_COUT,
  C_COUT.LIB_C_COUT,
  to_date(REAL_DEP.DAT_MDT, 'J'),
  REAL_DEP.NUM_BJ,
  REAL_DEP.NUM_TIERS,
  decode(REAL_DEP.NUM_TIERS, null, REAL_DEP.LIB_RAIS1_FOUR || ' ' || REAL_DEP.LIB_RAIS2_FOUR || ' ' || REAL_DEP.LIB_RAIS3_FOUR, W_TIERS.LIB_RAIS1 || ' ' || W_TIERS.LIB_RAIS2 || ' ' || W_TIERS.LIB_RAIS3),
  cad_cpt.NUM_ENV,
  REAL_DEP.REF_INT_MDT,
  ELEANA.COD_NOMANA,
  VENT_E.COD_PERIODE,
  to_date(REAL_DEP.DAT_DEP_DELAI, 'J')
,
  REAL_DEP.TYP_NOMENC_MAR
FROM
  AXE AXEREA2,
  ELEANA,
  VENT_E,
  AN_C_COUT_ELEANA,
  C_COUT,
  VENT_D_SO,
  W_TIERS,
  cad_cpt,
  REAL_DEP
WHERE
  (cad_cpt.COD_COLL = REAL_DEP.COD_COLL)
  AND(cad_cpt.COD_BUDG = REAL_DEP.COD_BUDG)
  AND(cad_cpt.NUM_ENV = REAL_DEP.NUM_ENV)
  AND(cad_cpt.COD_BUDG = REAL_DEP.COD_BUDG and cad_cpt.COD_COLL = REAL_DEP.COD_COLL and cad_cpt.NUM_ENV = REAL_DEP.NUM_ENV and cad_cpt.NUM_EXBUDG = REAL_DEP.NUM_EXBUDG)
  AND(VENT_D_SO.COD_C_COUT = C_COUT.COD_C_COUT(+) and VENT_D_SO.COD_AXE = C_COUT.COD_AXE(+))
  AND(REAL_DEP.COD_BUDG = decode('02', '**', REAL_DEP.COD_BUDG, '02'))
  AND(REAL_DEP.COD_COLL = upper('CG67'))
  AND(REAL_DEP.COD_COLL = W_TIERS.COD_COLL(+) and REAL_DEP.NUM_TIERS = W_TIERS.NUM_TIERS(+))
  AND(REAL_DEP.COD_BUDG = VENT_D_SO.COD_BUD(+) and REAL_DEP.COD_COLL = upper(VENT_D_SO.COD_ORG(+)) and REAL_DEP.NUM_EXBUDG = VENT_D_SO.COD_EXEBUD(+) and REAL_DEP.NUM_LIQ_MDT = VENT_D_SO.NUM_LIQ(+) and REAL_DEP.NUM_LIG_MDT = VENT_D_SO.NUM_LIG(+) and
VENT_D_SO.SENS_MVT(+) = 'D')
  AND(AXEREA2.COD_AXE(+) = VENT_D_SO.COD_AXE)
  AND(AN_C_COUT_ELEANA.ELEANA_ID = ELEANA.ELEANA_ID(+))
  AND(C_COUT.COD_AXE = AN_C_COUT_ELEANA.COD_AXE(+) and C_COUT.COD_C_COUT = AN_C_COUT_ELEANA.COD_C_COUT(+))
  AND(REAL_DEP.COD_BUDG = VENT_E.COD_BUD(+) and REAL_DEP.COD_COLL = upper(VENT_E.COD_ORG(+)) and REAL_DEP.NUM_EXBUDG = VENT_E.COD_EXEBUD(+) and REAL_DEP.NUM_LIQ_MDT = VENT_E.NUM_LIQ(+) and REAL_DEP.NUM_LIG_MDT = VENT_E.NUM_LIG(+) and
VENT_E.SENS_MVT(+) = 'D')
  AND
  VENT_D_SO.COD_AXE Is Not Null
  AND
  cad_cpt.NUM_EXBUDG BETWEEN {annee_deb} AND {annee_fin}    
GROUP BY
  cad_cpt.COD_COLL, 
  cad_cpt.COD_BUDG, 
  cad_cpt.NUM_EXBUDG, 
  cad_cpt.COD_SECTION, 
  cad_cpt.TYP_MVT, 
  REAL_DEP.NUM_MANDAT, 
  REAL_DEP.NUM_LIQ_MDT, 
  REAL_DEP.NUM_LIG_MDT,
  VENT_D_SO.COD_AXE, 
  AXEREA2.LIB_AXE, 
  VENT_D_SO.COD_C_COUT, 
  C_COUT.LIB_C_COUT, 
  to_date(REAL_DEP.DAT_MDT, 'J'), 
  REAL_DEP.NUM_BJ, 
  REAL_DEP.NUM_TIERS, 
  decode(REAL_DEP.NUM_TIERS, null, REAL_DEP.LIB_RAIS1_FOUR || ' ' || REAL_DEP.LIB_RAIS2_FOUR || ' ' || REAL_DEP.LIB_RAIS3_FOUR, W_TIERS.LIB_RAIS1 || ' ' || W_TIERS.LIB_RAIS2 || ' ' || W_TIERS.LIB_RAIS3), 
  cad_cpt.NUM_ENV, 
  REAL_DEP.REF_INT_MDT, 
  ELEANA.COD_NOMANA, 
  VENT_E.COD_PERIODE, 
  to_date(REAL_DEP.DAT_DEP_DELAI, 'J'),
  REAL_DEP.TYP_NOMENC_MAR