select 
 p.ENT_I_CODEENTREP as CodeEntreprise
,b.ETA_I_CODEETAB   as CodeEtablissement
,i.INS_I_CODEINST   as CodeInstallation
,u.UCO_S_CODEUC     as CodeUC
--
,p.ENT_S_LIBELLE as LibEntreprise
,b.ETA_S_LIBELLE as LibEtablissement
,i.INS_S_LIBELLE as LibInstallation
,u.UCT_S_LIBELLE as LibUC
--
,a.LID_S_TENEUR as CodeTeneur
,count(*) as NbrLignes
,'DEC' as Provenance
from DR_UC u
  inner join DR_EXPLOITANT e on u.UCO_S_CODEUC=e.UCO_S_CODEUC
    inner join DM_INST i on e.INS_I_CODEINST=i.INS_I_CODEINST
      inner join DM_ETAB b on i.ETA_I_CODEETAB=b.ETA_I_CODEETAB
        inner join DM_ENTREP p on b.ENT_I_CODEENTREP=p.ENT_I_CODEENTREP
    inner join DM_DECLARATION d on u.UCO_S_CODEUC=d.UCO_S_CODEUC
	 and ( /* cf. FDI 126 : ventilation par installation de la SUC emettrice */
	 case when (d.OPE_S_CODEOP in ('11','12','17')) then d.EXP_L_ID_destinataire
						else d.EXP_L_ID end ) = e.EXP_L_ID
    inner join dm_annexe_dec a on d.DEC_L_ID=a.DEC_L_ID
where d.DEC_I_TYPECODIF = 1
  and a.LDI_D_ANNUL is null
group by
 p.ENT_I_CODEENTREP
,b.ETA_I_CODEETAB
,i.INS_I_CODEINST
,u.UCO_S_CODEUC
--
,p.ENT_S_LIBELLE
,b.ETA_S_LIBELLE
,i.INS_S_LIBELLE
,u.UCT_S_LIBELLE
--
,a.LID_S_TENEUR
UNION (
select
 p.ENT_I_CODEENTREP as CodeEntreprise
,b.ETA_I_CODEETAB   as CodeEtablissement
,i.INS_I_CODEINST   as CodeInstallation
,u.UCO_S_CODEUC     as CodeUC
--
,p.ENT_S_LIBELLE as LibEntreprise
,b.ETA_S_LIBELLE as LibEtablissement
,i.INS_S_LIBELLE as LibInstallation
,u.UCT_S_LIBELLE as LibUC
--
,s.STK_S_TENEUR as "CodeTeneur"
,count(*) as NbrLignes
,'STK' as Provenance
from DR_UC u
  inner join DR_EXPLOITANT e on u.UCO_S_CODEUC=e.UCO_S_CODEUC
  inner join DM_INST i on e.INS_I_CODEINST=i.INS_I_CODEINST
  inner join DM_ETAB b on i.ETA_I_CODEETAB=b.ETA_I_CODEETAB
  inner join DM_ENTREP p on b.ENT_I_CODEENTREP=p.ENT_I_CODEENTREP
  inner join DM_STOCK s on u.UCO_S_CODEUC=s.STK_S_CODEUC and e.EXP_L_ID=s.EXP_L_ID
  inner join DM_VARIATION_STK v on s.STK_L_IDSTK=v.STK_L_IDSTK
  inner join dm_annexe_dec a on v.LID_L_ID=a.LID_L_ID
  inner join dm_declaration d on a.DEC_L_ID=d.DEC_L_ID
where s.STK_I_TYPECODIF= 1
group by
 p.ENT_I_CODEENTREP
,b.ETA_I_CODEETAB
,i.INS_I_CODEINST
,u.UCO_S_CODEUC
--
,p.ENT_S_LIBELLE
,b.ETA_S_LIBELLE
,i.INS_S_LIBELLE
,u.UCT_S_LIBELLE
--
,s.STK_S_TENEUR
)
order by CodeUC,CodeInstallation,CodeTeneur,Provenance
go