--< AKM 12th October 2021
--< Create FTP Files for BaaN

-- > reconc 
CREATE TABLE kuk.ftp_reconc (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `RECPNO` varchar(30) DEFAULT NULL, `RECFLG` varchar(1) DEFAULT NULL,
  `RECIVPA` int(15) DEFAULT NULL, `RECIVPU` int(15) DEFAULT NULL,
  `Date_Extracted` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

-- > pickplan 
CREATE TABLE kuk.ftp_pickplan (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `PP1ORNO` varchar(10) DEFAULT NULL, `PP1STS` varchar(10) DEFAULT NULL,
  `PP1PSTMP` varchar(14) DEFAULT NULL, `PP1PONO` varchar(4) DEFAULT NULL,
  `PP1TYP` varchar(20) DEFAULT NULL, `PP1PNO` varchar(30) DEFAULT NULL,
  `PP1PCQ` int(9) DEFAULT NULL, `PP1OPER` varchar(10) DEFAULT NULL,
  `PP1ILOC` varchar(10) DEFAULT NULL, `PP1ITM` varchar(40) DEFAULT NULL,
  `PP1BSTMP` varchar(14) DEFAULT NULL, `PP1SSTMP` varchar(14) DEFAULT NULL,
  `PP1DSTR` varchar(20) DEFAULT NULL, `PP1DNM` varchar(30) DEFAULT NULL,
  `PP1PROJ` varchar(30) DEFAULT NULL, `PP1TTYPE` varchar(255),
  PP1PARENT varchar(255), PP1PROJECT varchar(255), 
  `Date_Created` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

-- > pickplan_summary
CREATE TABLE kuk.ftp_pickplan_summary (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `external_order` varchar(20) DEFAULT NULL, `production_date` integer(8) DEFAULT NULL,
  `production_time` int(4) DEFAULT NULL, `pstatus` varchar(20) DEFAULT NULL,
  `trans_type` varchar(20) DEFAULT NULL, `operation` varchar(10) DEFAULT NULL,
  `parent_part` varchar(50) DEFAULT NULL, `build_date` int(8) DEFAULT NULL,
  `build_time` int(4) DEFAULT NULL, `sales_date` int(8) DEFAULT NULL,
  `sales_time` int(4) DEFAULT NULL,
  `distributor` varchar(20) DEFAULT NULL, `distributor_name` varchar(30) DEFAULT NULL,
  `project` varchar(30) DEFAULT NULL,`item` varchar(14) DEFAULT NULL,
  `Date_Created` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

-- > receiptplan
CREATE TABLE kuk.ftp_receiptplan (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `PR1ORNO` varchar(10) DEFAULT NULL, `PR1PONO` varchar(4) DEFAULT NULL,
  `PR1PNO` varchar(30) DEFAULT NULL,  `PR1PCQ` int(9) DEFAULT NULL,
  `PR1RDT` int(8) DEFAULT NULL,  `PR1PCD` varchar(30) DEFAULT NULL,
  `PR1PNM` varchar(30) DEFAULT NULL,  `PR1PFL` varchar(30) DEFAULT NULL,
  `Date_Created` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

-- > container
CREATE TABLE kuk.ftp_container (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Container` varchar(50) DEFAULT NULL, `Supplier` varchar(50) DEFAULT NULL,
  `Cases` varchar(50) DEFAULT NULL, `Reference` varchar(50) DEFAULT NULL,
  `Part_Number` varchar(50) DEFAULT NULL, `Quantity` varchar(50) DEFAULT NULL,
  `Date_Extracted` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
