--< AKM 12th October 2021
--<  Create tables used in Daily Target

 --> Create BAOR10I4 for Daily Targets processing
CREATE TABLE kuk.daytgt_baor10i4
( `id` bigint(20) NOT NULL AUTO_INCREMENT,
`PROJ` varchar(20), `MODL` varchar(30), `SERL` varchar(30),
 `SPECIF` varchar(50), `PROD_START` varchar(10),
 `PROD_STARTT` varchar(10), `PLAN_COMP` varchar(10), `PLAN_COMPT` varchar(10), `PLAN_SHIP` varchar(10),
 `PLAN_SHIPT` varchar(10), `ACT_COMP` varchar(10), `ACT_COMPT` varchar(10),
 `DB_REF` varchar(20), `SE_NUM` varchar(20), `CUST_ORD` varchar(20),
 `ACT_SHIP` varchar(10), `ACT_SHIPT` varchar(10), `RESER` varchar(20), `ORD_NO` varchar(9),
 `FUT_START` varchar(10), `MC_STS` varchar(50),
 `RESERV` varchar(20), `QUALITY` varchar(20), `SALES` varchar(20), `RES_REASON` varchar(50),
 `SORT1` varchar(50), `SORT2` varchar(50), `SORT3` varchar(50), `MC_AGE` varchar(10),
 `BASE_COMP` varchar(10), `TARG_COMP` varchar(10), `PDI_REQD` varchar(20), `NOISE_TEST` varchar(20),
 `STOCK_STS` varchar(50), `SALE_STS` varchar(50), `PL_TARGET` varchar(50), `BAAN_STS` varchar(50),
 `LEV` varchar(50), `LEV_COMP` varchar(20), `MOD_TYPE` varchar(50), `HARD_SHORT` int(11),
 `SOFT_SHORT` int(11), `PICKLINES` int(11), `ALLOCATED` int(11), `PICKED` int(11),
 `DELIVERED` int(11), `SHORTAGE` int(11), `AAA_OUTS` int(11), `AAA_DEL` int(11),
 `PICK_RUN` varchar(6), `DISTR` varchar(10), `DIST_NAME` varchar(50), `PROD_YMD` varchar(10),
 `BUILD_YMD` varchar(10), `SALE_YMD` varchar(10), `H_SHORT_STS` varchar(256), `S_SHORT_STS` varchar(256),
 `PLAN_V_TARGET` int(11), `PROJKEY` varchar(30), `OPCOMP` varchar(20), `OFFLINE_SHORT` int(11),
 PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 --> Create OLSWRK (Offline Shortages) for Daily Targets processing
CREATE TABLE kuk.daytgt_olswrk
( `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `OLSDATE` varchar(10), `OLSTIME` varchar(8), `OLSLOC` varchar(10),
 `OLSTEXT` varchar(20), `OLSPNO` varchar(30),
 `OLSPNM` varchar(30), `OLSQTY` INT(11), `OLSPROJ` varchar(20), `OLSORNO` varchar(9),
 `OLSMODL` varchar(20), `OLSSERL` varchar(20), `OLSPDT` varchar(10),
 `OLSPTM` varchar(8),
 PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --> Create DTFMSP (Daily Target Milestones) for Daily Targets processing
CREATE TABLE kuk.daytgt_dtfmsp
( `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `DTMORNO` varchar(10), `DTMTXT` varchar(35),
 `DTMPSD` varchar(10), `DTMPST` varchar(8), `DTMPCD` varchar(10),
 `DTMPCT` varchar(8), `DTMASD` varchar(10), `DTMAST` varchar(8),
 `DTMACD` varchar(10), `DTMACT` varchar(8),
 PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
  --> Create VELCOM (comments downloaded) for Daily Targets processing
 CREATE TABLE kuk.daytgt_velcom
 ( `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `PR1PNO` varchar(30), `PR1COMM` varchar(50),
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--> Create VELCOMP (extracted/ built from VELCOM) for Daily Target Comments
 CREATE TABLE kuk.daytgt_velcomp
 ( `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `part` varchar(30), `comment` varchar(50),
 `reason` varchar(50),`date_extracted` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--> Create TARGETMS table to store the field definitions for MSTARGET table creation.
  CREATE TABLE kuk.daytgt_targetms
  ( `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `mstxt` varchar(35) DEFAULT NULL,
  `msabbr` varchar(25) DEFAULT NULL,
  `msseq` int(5) DEFAULT NULL,
  `date_extracted` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

