WITH main_query AS
     (SELECT /*+ materialize */
             pe.beginn pebeginn, vla.vertragsnummer vertragspartner, vla.vertragsbezeichnung,
             CASE
                 WHEN vpe.serien_id IS NULL THEN DECODE (vla.titel,
                                                         NULL, DECODE (vpe.serien_id,
                                                                       NULL, vpe.titel,
                                                                       vpe.serientitel
                                                                      ),
                                                         DECODE (vla.hidden_serien_id,
                                                                 NULL, vla.titel,
                                                                 vla.serientitel
                                                                )
                                                        )
                 ELSE DECODE (vpe.serien_id,
                              NULL, getpresstitle (vpe.fassungmrg_id),
                              vla.serienoriginaltitel
                             )
             END titel,
             DECODE (vla.titel,
                     NULL, DECODE (vpe.serien_id, NULL, '', vpe.titel),
                     DECODE (vla.hidden_serien_id, NULL, '', vla.titel)
                    ) serientitel,
             DECODE (dp.episode, NULL, TO_CHAR (vpe.episodennummer), dp.episode) episodennummer,
             CASE
                 WHEN vpe.serien_id IS NULL THEN DECODE
                                                    (vpe.serien_id,
                                                     NULL, getpresstitle (vpe.fassungmrg_id),
                                                     vla.serienoriginaltitel
                                                    )
                 ELSE DECODE (vla.titel,
                              NULL, DECODE (vpe.serien_id, NULL, vpe.titel, vpe.serientitel),
                              DECODE (vla.hidden_serien_id, NULL, vla.titel, vla.serientitel)
                             )
             END originaltitel,
             pp.programmtyp, SUBSTR (fnc_sys_datetime.nframes2string (pe.beginn), 0,
                                     11) bc_date_old,
             getbroadcastdaystring (:contentid, pe.beginn) bc_date,
             SUBSTR (fnc_sys_datetime.nframes2string (pe.beginn), 12, 5) beginn,
             getlocalamountforlicence
                   (pe.lizenz_id,
                    1,
                    getactivationforlicence (pe.lizenz_id,
                                             fnc_sys_datetime.nframes2datetime (pe.beginn)
                                            )
                   ) loc_preis,
             getusedrunsforlicence (pe.lizenz_id, :END, 0, pe.service_id) exhibitionday,
             getusedrunsforlicence (pe.lizenz_id, :END, 1,
                                    pe.service_id) wiederholung_exhibitionday,
             CASE
                 WHEN getusedrunsforlicence (pe.lizenz_id, :END, 0) IS NOT NULL THEN getlocalamountforlicence
                                                                                        (pe.lizenz_id,
                                                                                         1,
                                                                                         getamortizationforlicence
                                                                                             (pe.lizenz_id,
                                                                                              fnc_sys_datetime.nframes2datetime
                                                                                                  (pe.beginn
                                                                                                  )
                                                                                             )
                                                                                        )
             END loc_amortization,
             pa.methode,
             CASE pa.abschreibemethode_id
                 WHEN 651 THEN CASE getusedrunsforlicence (pe.lizenz_id,
                                                           :END,
                                                           0,
                                                           pe.service_id
                                                          )
                                  WHEN 1 THEN ROUND
                                                 ((  getlocalamountforlicence
                                                         (pe.lizenz_id,
                                                          1,
                                                          getactivationforlicence
                                                              (pe.lizenz_id,
                                                               fnc_sys_datetime.nframes2datetime
                                                                                          (pe.beginn)
                                                              )
                                                         )
                                                   * 0.67
                                                  ),
                                                  2
                                                 )
                                  WHEN 2 THEN ROUND
                                                 ((  getlocalamountforlicence
                                                         (pe.lizenz_id,
                                                          1,
                                                          getactivationforlicence
                                                              (pe.lizenz_id,
                                                               fnc_sys_datetime.nframes2datetime
                                                                                          (pe.beginn)
                                                              )
                                                         )
                                                   * 0.33
                                                  ),
                                                  2
                                                 )
                              END
                 WHEN 652 THEN CASE
                                  WHEN getusedrunsforlicence (pe.lizenz_id, :END, 0, pe.service_id) =
                                                                                                   1
                                   OR getusedrunsforlicence (pe.lizenz_id, :END, 0, pe.service_id) =
                                                                                                   2 THEN ROUND
                                                                                                             ((  getlocalamountforlicence
                                                                                                                     (pe.lizenz_id,
                                                                                                                      1,
                                                                                                                      getactivationforlicence
                                                                                                                          (pe.lizenz_id,
                                                                                                                           fnc_sys_datetime.nframes2datetime
                                                                                                                               (pe.beginn
                                                                                                                               )
                                                                                                                          )
                                                                                                                     )
                                                                                                               * 0.5
                                                                                                              ),
                                                                                                              2
                                                                                                             )
                              END
                 WHEN 650 THEN CASE getusedrunsforlicence (pe.lizenz_id, :END, 0, pe.service_id)
                                  WHEN 1 THEN ROUND
                                                 (getlocalamountforlicence
                                                      (pe.lizenz_id,
                                                       1,
                                                       getactivationforlicence
                                                           (pe.lizenz_id,
                                                            fnc_sys_datetime.nframes2datetime
                                                                                          (pe.beginn)
                                                           )
                                                      ),
                                                  2
                                                 )
                              END
             END cost_saving_exhibitionday,
             0 cost_saving_exhibitionday_wdh, DECODE (pe.exhibitionday, NULL, 'WH', 'RUN') info
        FROM pln_event pe,
             v_pln_beitraege vpe,
             v_lizenz_programm vla,
             liz_konto lk,
             par_abschreibemethode pa,
             dok_programmfassung dpf,
             dok_programm dp,
             par_programmtyp pp,
             liz_vertrag lv,
             par_vertragsart pva
       WHERE pe.lizenz_id = vla.hidden_lizenz_id(+)
         AND pe.merge_id = vpe.fassungmrg_id
         AND vla.hidden_konto_id = lk.konto_id(+)
         AND lk.abschreibemethode_id = pa.abschreibemethode_id(+)
         AND pe.event_id = pe.main_event_id
         AND pe.merge_id = dpf.merge_id
         AND dpf.programm_id = dp.programm_id
         AND dp.programmtyp_id = pp.programmtyp_id
         AND vla.hidden_vertrag_id = lv.vertrag_id
         AND lv.vertragsart_id = pva.vertragsart_id
         AND pva.vertragsart = 'License contract'
         AND pp.kuerzel NOT IN ('LOCAL', 'NEWS')
         AND pe.beginn >= fnc_sys_datetime.datetime2nframes (:BEGIN)
         AND pe.beginn < fnc_sys_datetime.datetime2nframes (:END)
         AND pe.service_id = :contentid)
SELECT   *
    FROM (SELECT 1 group_nr, TO_CHAR (pebeginn) group_data, pebeginn, vertragspartner,
                 vertragsbezeichnung, titel, serientitel, episodennummer, originaltitel,
                 programmtyp, bc_date_old, bc_date, beginn, loc_preis, exhibitionday,
                 wiederholung_exhibitionday, loc_amortization, methode, cost_saving_exhibitionday,
                 cost_saving_exhibitionday_wdh, info
            FROM main_query v
          UNION ALL
          SELECT   1.5 group_nr, getbroadcastdaystring (:contentid, pebeginn) group_data,
                   TO_NUMBER (NULL) pebeginn, TO_CHAR (NULL) vertragspartner,
                   TO_CHAR (NULL) vertragsbezeichnung, TO_CHAR (NULL) titel,
                   TO_CHAR (NULL) serientitel, '' episodennummer, TO_CHAR (NULL) originaltitel,
                   TO_CHAR (NULL) programmtyp, '' bc_date_old, TO_CHAR (NULL) bc_date,
                   TO_CHAR (NULL) beginn, SUM (NVL (loc_preis, 0)) loc_preis,
                   SUM (exhibitionday) exhibitionday,
                   SUM (wiederholung_exhibitionday) wiederholung_exhibitionday,
                   SUM (NVL (loc_amortization, 0)) loc_amortization, TO_CHAR (NULL) methode,
                   SUM (DECODE (info, 'RUN', NVL (cost_saving_exhibitionday, 0))
                       ) cost_saving_exhibitionday,
                   SUM (cost_saving_exhibitionday_wdh) cost_saving_exhibitionday_wdh, '' info
              FROM main_query v
          GROUP BY getbroadcastdaystring (:contentid, pebeginn)
          UNION ALL
          SELECT   2 group_nr, programmtyp group_data, TO_NUMBER (NULL) pebeginn,
                   TO_CHAR (NULL) vertragspartner, TO_CHAR (NULL) vertragsbezeichnung,
                   TO_CHAR (NULL) titel, TO_CHAR (NULL) serientitel, '' episodennummer,
                   TO_CHAR (NULL) originaltitel, TO_CHAR (NULL) programmtyp, '' bc_date_old,
                   TO_CHAR (NULL) bc_date, TO_CHAR (NULL) beginn, SUM (NVL (loc_preis, 0))
                                                                                          loc_preis,
                   SUM (exhibitionday) exhibitionday,
                   SUM (wiederholung_exhibitionday) wiederholung_exhibitionday,
                   SUM (NVL (loc_amortization, 0)) loc_amortization, TO_CHAR (NULL) methode,
                   SUM (DECODE (info, 'RUN', NVL (cost_saving_exhibitionday, 0))
                       ) cost_saving_exhibitionday,
                   SUM (cost_saving_exhibitionday_wdh) cost_saving_exhibitionday_wdh, '' info
              FROM main_query v
          GROUP BY programmtyp
          UNION ALL
          SELECT   3 group_nr, vertragsbezeichnung || ' ' || programmtyp group_data,
                   TO_NUMBER (NULL) pebeginn, TO_CHAR (NULL) vertragspartner,
                   TO_CHAR (NULL) vertragsbezeichnung, TO_CHAR (NULL) titel,
                   TO_CHAR (NULL) serientitel, '' episodennummer, TO_CHAR (NULL) originaltitel,
                   TO_CHAR (NULL) programmtyp, '' bc_date_old, TO_CHAR (NULL) bc_date,
                   TO_CHAR (NULL) beginn, SUM (NVL (loc_preis, 0)) loc_preis,
                   SUM (exhibitionday) exhibitionday,
                   SUM (wiederholung_exhibitionday) wiederholung_exhibitionday,
                   SUM (NVL (loc_amortization, 0)) loc_amortization, TO_CHAR (NULL) methode,
                   SUM (DECODE (info, 'RUN', NVL (cost_saving_exhibitionday, 0))
                       ) cost_saving_exhibitionday,
                   SUM (cost_saving_exhibitionday_wdh) cost_saving_exhibitionday_wdh, '' info
              FROM main_query v
          GROUP BY vertragsbezeichnung || ' ' || programmtyp
          UNION ALL
          SELECT 4 group_nr, '' group_data, TO_NUMBER (NULL) pebeginn,
                 TO_CHAR (NULL) vertragspartner, TO_CHAR (NULL) vertragsbezeichnung,
                 TO_CHAR (NULL) titel, TO_CHAR (NULL) serientitel, '' episodennummer,
                 TO_CHAR (NULL) originaltitel, TO_CHAR (NULL) programmtyp, '' bc_date_old,
                 TO_CHAR (NULL) bc_date, TO_CHAR (NULL) beginn, SUM (NVL (loc_preis, 0)) loc_preis,
                 SUM (exhibitionday) exhibitionday,
                 SUM (wiederholung_exhibitionday) wiederholung_exhibitionday,
                 SUM (NVL (loc_amortization, 0)) loc_amortization, TO_CHAR (NULL) methode,
                 SUM (DECODE (info, 'RUN', NVL (cost_saving_exhibitionday, 0))
                     ) cost_saving_exhibitionday,
                 SUM (cost_saving_exhibitionday_wdh) cost_saving_exhibitionday_wdh, '' info
            FROM main_query v) mainselect
ORDER BY group_nr,
         group_data,
         bc_date,
         beginn,
         programmtyp,
         vertragspartner,
         vertragsbezeichnung,
         titel,
         episodennummer