CREATE TABLE DV5RV308 AS (
    SELECT 
        fzg_prod_werk AS fzg_prod_werk, 
        fzg_auftragsnumme AS fzg_auftragsnumme, 
        fzg_fahrgestellen AS fzg_fahrgestellen, 
        fzg_baumuster AS fzg_baumuster, 
        fzg_akt_va_abs_nr AS fzg_akt_va_abs_nr, 
        fzg_versandstatus AS fzg_versandstatus, 
        fara_f0tosab AS fara_f0tosab, 
        fara_f0tosan AS fara_f0tosan, 
        fara_f0dienstleister AS fara_f0dienstleister,
        fzte_auslieferung AS fzte_auslieferung, 
--- PKs
        fzg_objektid AS fzg_objektid, 
        fara_lfd_key AS fara_lfd_key, 
        fzro_lfd_key AS fzro_lfd_key,
        fzte_lfd_key AS fzte_lfd_key
FROM DV5RFZG, DV5RFZRO, DV5RFZTE, DV5RFARA
WHERE
   FZG_f_aktuelleroute = FZRO_LFD_KEY
   AND fzro_F_AKTUEL_FAHRZE = fara_lfd_key
   AND fzg_f_fzg_termin = fzte_lfd_key
   AND fzg_ARCHIVFLAG = 0
   AND fzg_VERSANDSTATUS IN ('85', '90'))
DATA INITIALLY DEFERRED REFRESH IMMEDIATE MAINTAINED BY SYSTEM

SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1

DSN11015

CREATE TABLE KD280.DV5RFZG
(
 FZG_OBJEKTID CHAR(10) NOT NULL
, FZG_PROD_WERK CHAR(4) NOT NULL
, FZG_AUFTRAGSNUMME CHAR(10) NOT NULL
, FZG_VIN CHAR(17) 
, FZG_FAHRGESTELLEN CHAR(6) 
, FZG_AKT_VA_ABS_NR CHAR(2) NOT NULL
, FZG_VERSANDSTATUS CHAR(2) NOT NULL
, FZG_BAUMUSTER CHAR(8) NOT NULL

, FZG_ARCHIVFLAG SMALLINT NOT NULL
, FZG_F_FZG_TERMIN DECIMAL(18,0) NOT NULL
, FZG_F_AKTUELLEROUTE DECIMAL(18,0) 
)
IN DATABASE KD280;

CREATE UNIQUE INDEX KD280.DV5CFZG1
ON KD280.DV5RFZG
(


 FZG_OBJEKTID 

)
;

CREATE TABLE KD280.DV5RFZRO
(
FZRO_LFD_KEY DECIMAL(18,0) NOT NULL
, FZRO_F_AKTUEL_FAHRZE DECIMAL(18,0) 
)
IN DATABASE KD280;

CREATE UNIQUE INDEX KD280.DV5CFZRO1
ON KD280.DV5RFZRO
(
FZRO_LFD_KEY
)
;

CREATE TABLE KD280.DV5RFARA
(
FARA_LFD_KEY DECIMAL(18,0) NOT NULL

, FARA_F0TOSAB CHAR(6) NOT NULL
, FARA_F0TOSAN CHAR(6) NOT NULL
, FARA_F0DIENSTLEISTER CHAR(4) 
, FARA_F_FAHRZEUGROUTE DECIMAL(18,0) NOT NULL
, FARA_F0FAHRZEUG CHAR(10) NOT NULL
)
IN DATABASE KD280;



CREATE UNIQUE INDEX KD280.DV5CFARA1
ON KD280.DV5RFARA
(
FARA_LFD_KEY
)
;


CREATE TABLE KD280.DV5RFZTE
(
FZTE_LFD_KEY DECIMAL(18,0) NOT NULL


, FZTE_AUSLIEFERUNG TIMESTAMP 
, FZTE_BEREITSTELLUN TIMESTAMP 
)
IN DATABASE KD280;


CREATE UNIQUE INDEX KD280.DV5CFZTE1
ON KD280.DV5RFZTE
(
FZTE_LFD_KEY
)
;

COMMIT;