SELECT 
  soc_cdp,
  soc_nom,
  soc_prenom,
  soc_au_soin_de,
  soc_appartement,
  soc_no_civique,
  soc_rue,
  soc_case_postale,
  soc_succursale,
  soc_ville,
  soc_province,
  soc_pays,
  soc_code_postal,
  soc_sexe,
  soc_langue,
  soc_tel_residence,
  soc_tel_travail,
  soc_tel_poste,
  soc_tel_fax,
  soc_no_da,
  soc_soci_mot_passe,
  inc_session,
  inc_annee,
  inc_dt_echeance_devoir -  30 AS  inc_dt_echeance_devoir,
  crs_cours,
  crs_option,
  crs_version,
  crs_edition,
  tut_no,
  tut_nom,
  tut_prenom,
  tut_sexe,
  sti_titre,
  fac_typ,
  fac_no
FROM 
  social,
  sobec_titre,
  cours,
  tuteur,
  tuteur_cours_groupe,
  inscription,
  facture_cours,
  facturation,
  (SELECT  fac_typ  as  a_tri_fac_typ,
       fac_no AS a_tri_fac_no,
       MIN(inc_cours) AS a_tri_inc_cours  FROM  inscription,
       facture_cours,
      facturation
  WHERE 
       inc_seq_no = fco_inc_seq_no AND
      fco_fac_typ =  fac_typ  AND
       fco_fac_no = fac_no AND
       fco_inc_typ  IN  ('E1','E2','G1','I1','I2','R1','R2','RA','RM')  AND
       fac_nvl_expedition=1
  GROUP BY fac_typ,  fac_no  )  
WHERE 
  soc_seq_no  =  fac_soc_seq_no  AND
  sti_cours(+) = crs_cours AND
  crs_seq_no  =  inc_crs_seq_no  AND
  tut_no  =  tcg_tut_no  AND
  tcg_crs_seq_no = inc_crs_seq_no AND
  tcg_groupe = inc_groupe AND
  (tcg_dt_debut  =  
          (SELECT  MAX(tcg_dt_debut)  FROM  tuteur_cours_groupe  WHERE  tcg_crs_seq_no  =  inc_crs_seq_no   AND
         tcg_groupe = inc_groupe AND
       tcg_ind_temporaire=0 AND
       tcg_dt_debut<=SYSDATE)) AND
  inc_seq_no  = fco_inc_seq_no AND
  inc_ind_inactif=0 AND
  inc_cours NOT LIKE  ('990%')  AND
  fco_fac_typ  =  fac_typ AND
  fco_fac_no = fac_no AND
  fco_inc_typ IN ('G1','I1','I2','R1','R2','RA','RM')  AND
  fac_typ = a_tri_fac_typ  AND
  fac_no  =  a_tri_fac_no  ORDER  BY  a_tri_inc_cours,  soc_cdp,  a_tri_fac_typ, a_tri_fac_no, inc_cours
