Prozeduren

CMS_DATA_EXPORT

Eingabeparameter

Ausgabeparameter

Beschreibung

Mit dieser Stored Procedure wird der CMS Export realisiert.

Definition

CREATE PROCEDURE CMS_DATA_EXPORT(
    SRCFAID INTEGER,
    SRCWELLE INTEGER,
    SRCDATE DATE)
RETURNS (
    AENDERUNG CHAR(40),
    FAID INTEGER,
    FAFILEPFAD CHAR(100),
    PFANAME CHAR(30),
    FANAME CHAR(30),
    SNMODERATOR1 CHAR(30),
    SEZEIT INTEGER,
    SEISTZEIT INTEGER,
    SESTATUS SMALLINT,
    TIID INTEGER,
    TIFILENAME CHAR(20),
    TITYP CHAR(2),
    TIBEARBDATUM DATE,
    TITITELTHEMA CHAR(80),
    TIINTERPRET CHAR(80),
    TIAUTOR CHAR(80),
    TISTARTNEXT INTEGER,
    TIARCHIVNR CHAR(50),
    TISYSTEMNR CHAR(20),
    TIKURZBEZ CHAR(10),
    TIANMODERATION BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    TITEXT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    TIABMODERATION BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    TIZUSATZTEXT BLOB SUB_TYPE 1 SEGMENT SIZE 80)
AS
declare variable AAENDERUNG CHAR(40);
declare variable AFAID INTEGER;
declare variable AFAFILEPFAD CHAR(100);
declare variable APFANAME CHAR(30);
declare variable AFANAME CHAR(30);
declare variable ASNMODERATOR1 CHAR(30);
declare variable ASEZEIT INTEGER;
declare variable ASEISTZEIT INTEGER;
declare variable ASESTATUS SMALLINT;
declare variable ATIID INTEGER;
declare variable ATIFILENAME CHAR(20);
declare variable ATITYP CHAR(2);
declare variable ATIBEARBDATUM TIMESTAMP;
declare variable ATITITELTHEMA CHAR(80);
declare variable ATIINTERPRET CHAR(80);
declare variable ATIAUTOR CHAR(80);
declare variable ATISTARTNEXT INTEGER;
declare variable ATIARCHIVNR CHAR(50);
declare variable ATISYSTEMNR CHAR(20);
declare variable ATIKURZBEZ CHAR(10);
declare variable ATIANMODERATION BLOB SUB_TYPE TEXT SEGMENT SIZE 80;
declare variable ATITEXT BLOB SUB_TYPE TEXT SEGMENT SIZE 80;
declare variable ATIABMODERATION BLOB SUB_TYPE TEXT SEGMENT SIZE 80;
declare variable ATIZUSATZTEXT BLOB SUB_TYPE TEXT SEGMENT SIZE 80;
declare variable APREVTIID INTEGER;
BEGIN
  APREVTIID = 0;
  FOR 
  select 
       CAST(TIBEARBDATUM AS VARCHAR(30)) || CAST(SEZEIT AS VARCHAR(10)) AS AENDERUNG,
       FA.FAID, 
       FA.FAFILEPFAD, 
       PFA.FANAME AS PFANAME, 
       FA.FANAME AS FANAME,
       SNMODERATOR1, 
       SEZEIT, 
       SEISTZEIT, 
       SESTATUS,
       TIID, 
       TIFILENAME, 
       TITYP, 
       TIBEARBDATUM, 
       TITITELTHEMA, 
       TIINTERPRET, 
       TIAUTOR, 
       TISTARTNEXT,
       TIARCHIVNR, 
       TISYSTEMNR, 
       TIKURZBEZ, 
       TIANMODERATION, 
       TITEXT, 
       TIABMODERATION, 
       TIZUSATZTEXT

       from SENDEPLAENE SP, SENDUNGEN SN, SENDEELEMENTE SE, TITEL TI, FAECHER FA, FAECHER PFA

       where SP.SPID = SN.SNSPID and SN.SNID = SE.SESNID and TI.TIID = SE.SETIID and FA.FAID = TI.TIFAID and FA.FAIDUEBER = PFA.FAID
             and SP.SPWELLE = :SRCWELLE and SP.SPDATUM = :SRCDATE and FA.FAID = :SRCFAID
      
       order by TIID asc, SEZEIT desc

       into AAENDERUNG, AFAID, AFAFILEPFAD, APFANAME, AFANAME, ASNMODERATOR1, ASEZEIT, ASEISTZEIT, ASESTATUS, ATIID, ATIFILENAME, ATITYP, ATIBEARBDATUM, 
            ATITITELTHEMA, ATIINTERPRET, ATIAUTOR, ATISTARTNEXT, ATIARCHIVNR, ATISYSTEMNR, ATIKURZBEZ, ATIANMODERATION, ATITEXT, ATIABMODERATION, ATIZUSATZTEXT

  DO 
  BEGIN
    IF (ATIID <> APREVTIID) THEN
    BEGIN
      AENDERUNG = AAENDERUNG;
      FAID = AFAID; 
      FAFILEPFAD = AFAFILEPFAD; 
      PFANAME = APFANAME;
      FANAME = AFANAME;
      SNMODERATOR1 = ASNMODERATOR1;
      SEZEIT = ASEZEIT; 
      SEISTZEIT = ASEISTZEIT;
      SESTATUS = ASESTATUS;
      TIID = ATIID; 
      TIFILENAME = ATIFILENAME;
      TITYP = ATITYP; 
      TIBEARBDATUM = ATIBEARBDATUM;
      TITITELTHEMA = ATITITELTHEMA;
      TIINTERPRET = ATIINTERPRET;
      TIAUTOR = ATIAUTOR;
      TISTARTNEXT = ATISTARTNEXT;
      TIARCHIVNR = ATIARCHIVNR;
      TISYSTEMNR = ATISYSTEMNR;
      TIKURZBEZ = ATIKURZBEZ;
      TIANMODERATION = ATIANMODERATION; 
      TITEXT = ATITEXT;
      TIABMODERATION = ATIABMODERATION;
      TIZUSATZTEXT = ATIZUSATZTEXT;
      SUSPEND;
    END

    APREVTIID = ATIID;
  END
END