DROP VIEW IF EXISTS V_ADR_FULL;
DROP VIEW IF EXISTS V_LOCA;
DROP TABLE IF EXISTS LOCA;
DROP TABLE IF EXISTS ADRHDR;
CREATE TABLE `adrhdr` (
  `ADRNO` bigint(20) NOT NULL,
  `DTERF` datetime NOT NULL,
  `AANRD` smallint(6) NOT NULL,
  `AANRDD` tinyint(4) NOT NULL,
  `FIRMA` varchar(30) default NULL,
  `TITEL` varchar(30) default NULL,
  `NAME` varchar(30) default NULL,
  `VNAME` varchar(30) default NULL,
  `FKT` varchar(30) default NULL,
  `ILN` varchar(25) default NULL,
  `FKTD` tinyint(4) NOT NULL,
  `NAME2` varchar(30) default NULL,
  `NAMED` tinyint(4) NOT NULL,
  `DTGEB` date default NULL,
  `SEX` int(11) NOT NULL,
  `LANG` tinyint(4) NOT NULL,
  `BANRD` smallint(6) NOT NULL,
  `ADKAT` smallint(6) NOT NULL,
  `MEMO` text,
  `PICNO` int(11) default NULL,
  `AKTIV` tinyint(4) NOT NULL,
  `MODIF` datetime default NULL,
  `MUSER` smallint(6) default NULL,
  `DOCNO` int(11) default NULL,
  `UPDLOCK` int(11) default NULL,
  `REPLID` bigint(20) default NULL,
  PRIMARY KEY  (`ADRNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `loca` (
  `ADRNO` bigint(20) NOT NULL,
  `LNO` int(11) NOT NULL,
  `ORD` smallint(6) NOT NULL,
  `LOCAA` smallint(6) NOT NULL,
  `STR` varchar(30) default NULL,
  `STR2` varchar(30) default NULL,
  `STR3` varchar(30) default NULL,
  `LAND` varchar(3) NOT NULL,
  `PLZ` varchar(8) default NULL,
  `ORT` varchar(30) default NULL,
  `PF` tinyint(4) NOT NULL,
  `PFNO` varchar(10) default NULL,
  `PFPLZ` varchar(8) default NULL,
  `PFORT` varchar(30) default NULL,
  `RADRNO` bigint(20) default NULL,
  `RLNO` int(11) default NULL,
  `ABTL` varchar(30) default NULL,
  `ABTLD` tinyint(4) NOT NULL,
  `BANRD` smallint(6) default NULL,
  `DARS` smallint(6) default NULL,
  `MEMO` text,
  `MODIF` datetime default NULL,
  `MUSER` smallint(6) default NULL,
  `UPDLOCK` int(11) default NULL,
  `REPLID` bigint(20) default NULL,
  PRIMARY KEY  (`ADRNO`,`LNO`),
  CONSTRAINT `FK_LOCA_1` FOREIGN KEY (`ADRNO`) REFERENCES `adrhdr` (`ADRNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE VIEW V_LOCA (ADRNO, LNO, ORD, LOCAA, STR, STR2, STR3, LAND, PLZ, ORT, PF, PFNO, PFPLZ, PFORT, RADRNO, 
			RLNO, ABTL, ABTLD, BANRD, NOTE, DARS, MEMO)  AS
	SELECT L.ADRNO, L.LNO, L.ORD, L.LOCAA, L.STR, L.STR2, L.STR3, L.LAND, L.PLZ, L.ORT, L.PF, L.PFNO, L.PFPLZ, L.PFORT, L.RADRNO,
			 L.RLNO, L.ABTL, L.ABTLD, L.BANRD, LENGTH(L.MEMO), L.DARS, L.MEMO
	FROM LOCA L;
CREATE VIEW V_ADR_FULL (ADRNO, FIRMA, NAME, VNAME, NAME2, PICNO, DOCNO, AKTIV, LANG, ILN, DTGEB, ADKAT, SEX, DTERF, FNAME, 
			AANRD, AANRDD, NAMED, FKT, FKTD, TITEL, LNO, ABTL, ABTLD, RADRNO, RLNO, DARS, L_ORD, LOCAA, STR, 
			STR2, STR3, LAND, PLZ, ORT, PF, PFNO, PFPLZ, PFORT, BANRD )
AS SELECT A.ADRNO, A.FIRMA, A.NAME, A.VNAME, A.NAME2, A.PICNO, A.DOCNO, A.AKTIV, A.LANG, A.ILN, A.DTGEB, A.ADKAT, A.SEX, A.DTERF, 
TRIM(CASE A.NAMED 
WHEN 3 THEN TRIM(IFNULL(A.NAME, '')||' '||IFNULL(A.VNAME,'') ) ||' '|| IFNULL(A.FIRMA,'') 
WHEN 4 THEN TRIM(IFNULL(A.NAME,'')||' '||IFNULL(A.VNAME,'') ) ||' '|| IFNULL(A.FIRMA,'') 
ELSE IFNULL(A.FIRMA,'') ||' '|| TRIM(IFNULL(A.NAME,'')||' '||IFNULL(A.VNAME,'') )END ),
	A.AANRD, A.AANRDD, A.NAMED, A.FKT, A.FKTD, A.TITEL, L.LNO, L.ABTL, L.ABTLD, L.RADRNO, L.RLNO, L.DARS, L.ORD, L.LOCAA, L.STR, L.STR2, L.STR3, L.LAND, L.PLZ, L.ORT, L.PF, L.PFNO, L.PFPLZ,  L.PFORT, IFNULL( L.BANRD,A.BANRD) 
FROM ADRHDR A LEFT OUTER JOIN LOCA L ON (A.ADRNO=L.ADRNO);
INSERT INTO `adrhdr` (`ADRNO`,`DTERF`,`AANRD`,`AANRDD`,`FIRMA`,`TITEL`,`NAME`,`VNAME`,`FKT`,`ILN`,`FKTD`,`NAME2`,`NAMED`,`DTGEB`,`SEX`,`LANG`,`BANRD`,`ADKAT`,`MEMO`,`PICNO`,`AKTIV`,`MODIF`,`MUSER`,`DOCNO`,`UPDLOCK`,`REPLID`) VALUES 
 (10000117,'2001-03-13 13:28:00',1,0,'Eastern','Dr.','Ab','H.',NULL,NULL,0,'Off.',3,'2005-07-01',2,2,1,4,NULL,NULL,1,'2001-03-13 13:28:00',4,NULL,0,NULL),
 (10001124,'2002-02-13 08:29:39',3,0,'L\'Hebdo, c/o Cash',NULL,'Abbet','M',NULL,NULL,0,NULL,1,'2005-07-01',1,2,2,4,NULL,NULL,1,'2003-10-17 09:07:17',4,NULL,0,NULL);
INSERT INTO `loca` (`ADRNO`,`LNO`,`ORD`,`LOCAA`,`STR`,`STR2`,`STR3`,`LAND`,`PLZ`,`ORT`,`PF`,`PFNO`,`PFPLZ`,`PFORT`,`RADRNO`,`RLNO`,`ABTL`,`ABTLD`,`BANRD`,`DARS`,`MEMO`,`MODIF`,`MUSER`,`UPDLOCK`,`REPLID`) VALUES 
 (10000117,10000000,1,1,'P.O. Box',NULL,NULL,'EG','21511','Alex',0,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,NULL,'2001-03-13 13:28:00',4,0,NULL),
 (10001124,10000000,1,1,'B',NULL,NULL,'CH','8021','Z',1,'3810',NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,'2003-10-17 09:07:17',4,0,NULL);
COMMIT;