SELECT
 rownum AS id,
 TO_CHAR(anl.municipalityno2007)||' - '||kl.longtext AS kommune, 
 vtno.companytype AS virksomhedstype,
 anl.plantid AS anlaegid,
 anl.plantname AS anlaegsnavn, 
 REPLACE(bo.boreholeno,' ','') AS dgunr,
 CONCAT('https://data.geus.dk/JupiterWWW/anlaeg.jsp?anlaegid=',anl.plantid) AS anlaeg_link,
 CASE WHEN anl.active IS NOT NULL THEN anl.active||' - '||anlst.longtext END AS anlaeg_aktivstatus,
 'https://data.geus.dk/JupiterWWW/borerapport.jsp?dgunr='|| REPLACE(bo.boreholeno,' ','') borerapport,
 bo.use||' - '||ba.shorttext AS boringsanvendelse,
 CASE WHEN ia.intakeusage IS NOT NULL THEN TO_CHAR(ia.intakeusage)||' - '||ianv.longtext END AS indtagsanvendelse,
 CASE WHEN bo.use = 'S' OR anl.active = 2 OR ia.intakeusage = 7 OR ia.enddate < SYSDATE THEN 'Inaktiv' ELSE 'Aktiv' END status,
 ROUND(SQRT(POWER(ABS(anl.xutm32euref89-bo.xutm32euref89),2)+POWER(ABS(anl.yutm32euref89-bo.yutm32euref89),2))) afstand_m,
 CASE WHEN anl.xutm32euref89 > 0 AND anl.yutm32euref89 > 0 AND bo.xutm32euref89 > 0 AND bo.yutm32euref89 > 0 THEN
   mdsys.sdo_geometry( 2002, 25832, null, mdsys.sdo_elem_info_array(1,2,1), mdsys.sdo_ordinate_array( anl.xutm32euref89, anl.yutm32euref89, bo.xutm32euref89, bo.yutm32euref89 ) )
 END AS geom
FROM drwplantintake ia
LEFT JOIN code_816 ianv ON ia.intakeusage = ianv.code /* Indtagsanvendelse https://data.geus.dk/tabellerkoder/koder.html?codetype=816 */
INNER JOIN borehole bo ON ia.boreholeid = bo.boreholeid
LEFT JOIN code_855 ba ON bo.use = ba.code /* Boringsanvendelse https://data.geus.dk/tabellerkoder/koder.html?codetype=855 */
INNER JOIN drwplant anl ON ia.plantid = anl.plantid 
LEFT JOIN code_733 anlst ON anl.active = anlst.code /* Anlaegsstatus https://data.geus.dk/tabellerkoder/koder.html?codetype=733 */
LEFT JOIN code_808 kl ON anl.municipalityno2007 = kl.code /* Kommune https://data.geus.dk/tabellerkoder/koder.html?codetype=808 */
LEFT JOIN ( 
 SELECT /* Virksomhedstype https://data.geus.dk/tabellerkoder/?tablename=DRWPLANTCOMPANYTYPE */
   vt.plantid, listagg(vt.companytype||' - '||vl.longtext,', ') WITHIN GROUP (ORDER BY vt.companytype) AS companytype
 FROM drwplantcompanytype vt
 LEFT JOIN code_852 vl ON vt.companytype = vl.code /* Virksomhedstype https://data.geus.dk/tabellerkoder/koder.html?codetype=852 */
 GROUP BY vt.plantid) vtno ON anl.plantid = vtno.plantid