SET feedback OFF verify OFF heading OFF pagesize 0
--SET feedback ON
SET linesize 1000;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';
SET serveroutput ON;

DECLARE
  schuljahr CONSTANT VARCHAR2(4) := '2020';
  eh_typ    CONSTANT VARCHAR2(2) := 'US';
  eh_datum  varchar2(200); 
  date_format CONSTANT  VARCHAR2(200) := 'yyyy-mm-dd';

  cursor schule IS
  (
    select * from 
        (SELECT    
            dsttyp.wl_kurz_bezeichnung as dienststellentyp,
            nvl(mb.dst_kurz,'N/A') as mb,
            nvl(reg.dst_kurz,'N/A') as reg,
            nvl(samt.dst_kurz,'N/A') as samt,
            sart.wl_kurz_bezeichnung as sart,
            dst.DST_SCHLUESSEL || ' ' || sst.sst_name_offiziell AS schule, 
            es.ES_ERZ_DATUM as datum, 
            sta.ss_kurz_bezeichnung as status,
            count(*) over () as tot_rows
        FROM asd.ASD_ERHEBUNGSSTATUS es
            JOIN asd.ASD_DIENSTSTELLE dst             ON es.ES_DST_DIENSTSTELLE_ID = dst.DST_DIENSTSTELLE_ID
            join asd.asd_schulstelle sst on sst.sst_schulstellen_id = dst.dst_sst_hauptstelle_id
            join asd.asd_sd_werteliste dsttyp on dsttyp.wl_werteliste_id = dst.dst_wl_dsttyp_id
            left outer join asd.asd_dienststelle mb on mb.dst_dienststelle_id = dst.dst_dst_mb_bezirk_id
            left outer join asd.asd_dienststelle reg on reg.dst_dienststelle_id = dst.dst_dst_regierung_id
            left outer join asd.asd_dienststelle samt on samt.dst_dienststelle_id = dst.dst_dst_schulamt_id
            join asd.asd_sd_werteliste sart on sart.wl_werteliste_id = dst.dst_wl_schulart_id
            JOIN asd.ASD_SD_SCHULJAHR sja             ON es.ES_WL_SCHULJAHR_ID = sja.SJA_SCHULJAHR_ID
            JOIN asd.ASD_SD_WERTELISTE eht            ON es.ES_WL_ERHEBUNGSTYP_ID = eht.WL_WERTELISTE_ID
            JOIN asd.ASD_SD_STATISTIK_STATUS sta      ON es.ES_WL_STATUS_ID = sta.SS_STATISTIK_STATUS_ID
            LEFT OUTER JOIN asd.ASD_SST_AUFTRAG aft   ON es.ES_AFT_AUFTRAG_ID = aft.AFT_AUFTRAG_ID
            LEFT OUTER JOIN asd.ASD_SST_LIEFERUNG lfg ON aft.AFT_LIEFERUNG_ID   = lfg.LFG_LIEFERUNG_ID
        WHERE sja.SJA_SCHLUESSEL  = schuljahr
            AND eht.WL_KUERZEL        = eh_typ
            AND (es.ES_STATUS_AKT_ID IN
                (select max(es_status_akt_id) 
                from asd.ASD_erhebungsstatus es
                    join asd.ASD_SD_SCHULJAHR sja  on es.ES_WL_SCHULJAHR_ID = sja.SJA_SCHULJAHR_ID
                    join asd.ASD_SD_WERTELISTE eht on es.ES_WL_ERHEBUNGSTYP_ID = eht.WL_WERTELISTE_ID 
                where sja.SJA_SCHLUESSEL = schuljahr
                    and eht.WL_KUERZEL = eh_typ
                group by es_dst_dienststelle_id
                )    
            )
        order by dst.DST_SCHLUESSEL asc )
    );    
      
    begin        
    dbms_output.put('[');
    for rec in schule loop
        dbms_output.put('{"Dienststellentyp":"' || rec.dienststellentyp || '","MB_Bezirk":"' || rec.mb || '","Regierung":"' || rec.reg || '","Schulamt":"' || rec.samt || '","Schulart":"' || rec.sart || '","Übermittlungsstatus":"' || rec.status || '","Schule":"' || rec.schule || '","Datum":"' || TO_CHAR(rec.datum,'dd.mm.yyyy HH24:MI') || '"}');
        if schule%rowcount != rec.tot_rows then
            dbms_output.put_line(',');
        end if;
    end loop;
    dbms_output.put_line(']');
end;
/