CREATE TABLE `ATL_PS_LOGIN` (
  `USERID` INT(10) NOT NULL AUTO_INCREMENT,
  `USERNAME` VARCHAR(100) NOT NULL,
  `PASSWORD` VARCHAR(15) NOT NULL,
  `USERTYPE` VARCHAR(20) NOT NULL,
  `EMAIL` VARCHAR(200) DEFAULT NULL,
  `MOBILE` BIGINT(100) DEFAULT NULL,
  `VERIFICATION` VARCHAR(100) DEFAULT NULL,
  `CREATEDBY` VARCHAR(100) DEFAULT NULL,
  `CREATEDDATE` DATE DEFAULT NULL,
  `UPDATEDBY` VARCHAR(100) DEFAULT NULL,
  `UPDATEDDATE` DATETIME DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_LOGIN_USERID PRIMARY KEY(USERID)
);

CREATE TABLE `ATL_PS_DISTRIBUTOR` (
  `DISTRIBUTORID` varchar(200) NOT NULL,
  `USERID` bigint(20) NOT NULL,
  `DISTRIBUTORNAME` varchar(200) DEFAULT NULL,
  `TERRITORY` varchar(255) DEFAULT NULL,
  `REGION` varchar(20) DEFAULT NULL,
  `AREA` varchar(20) DEFAULT NULL,
  `ZONE` varchar(20) DEFAULT NULL,
  `CITY` varchar(20) DEFAULT NULL,
  `STATE` varchar(20) DEFAULT NULL,
  `ZIPCODE` int(10) DEFAULT NULL
);

/*
	Distributor 
*/
CREATE TABLE `ATL_PS_DISTRIBUTOR` (
  `DISTRIBUTORID` VARCHAR(200) NOT NULL,
  `USERID` BIGINT(20) NOT NULL,
  `DISTRIBUTORNAME` VARCHAR(200) DEFAULT NULL,
  `TERRITORY` VARCHAR(255) DEFAULT NULL,
  `REGION` VARCHAR(20) DEFAULT NULL,
  `AREA` VARCHAR(20) DEFAULT NULL,
  `ZONE` VARCHAR(20) DEFAULT NULL,
  `CITY` VARCHAR(20) DEFAULT NULL,
  `STATE` VARCHAR(20) DEFAULT NULL,
  `ZIPCODE` INT(10) DEFAULT NULL
);

CREATE TABLE `ATL_PS_DIS_SCAN_H` (
  `HEADERID` bigint(20) NOT NULL AUTO_INCREMENT,
  `DISTRIBUTORID` varchar(200) DEFAULT NULL,
  `PRODUCTCODE` varchar(200) DEFAULT NULL,
  `PRODUCTNAME` varchar(250) DEFAULT NULL,
  `LASTSCANDATE` date DEFAULT NULL,
  `STOCK` bigint(20) DEFAULT NULL,
  `CLOSINGSTOCK` bigint(20) DEFAULT NULL,
  `ENTRYSTOCKID` bigint(150) DEFAULT NULL,
  `ENTRYSTOCKDATE` date DEFAULT NULL,
  `DELETEFLAG` varchar(100) DEFAULT NULL,
  `DELETEDATE` date DEFAULT NULL,
  `STOCKCOMMIT` varchar(20) DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_DIS_SCAN_H_HEADERID PRIMARY KEY(`HEADERID`)
);


CREATE TABLE `ATL_PS_DIS_SCAN_L` (
  `LINEID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `HEADERID` BIGINT(20) DEFAULT NULL,
  `DISTRIBUTORID` VARCHAR(200) NOT NULL,
  `RETAILERID` VARCHAR(200) DEFAULT NULL,
  `PRODUCTCODE` VARCHAR(200) NOT NULL,
  `BATCHNO` VARCHAR(200) DEFAULT NULL,
  `BOXNO` BIGINT(20) DEFAULT NULL,
  `MFGDATE` DATE DEFAULT NULL,
  `EXPDATE` DATE DEFAULT NULL,
  `SCANDATE` DATE NOT NULL,
  `STOCK` BIGINT(20) DEFAULT NULL,
  `BARCODENO` VARCHAR(200) DEFAULT NULL,
  `OPENINGSTOCK` BIGINT(30) DEFAULT NULL,
  `CLOSINGSTOCK` BIGINT(30) DEFAULT NULL,
  `DELETEFLAG` VARCHAR(20) DEFAULT NULL,
  `DELETEDATE` DATE DEFAULT NULL,
  `STOCKCOMMIT` VARCHAR(20) DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_DIS_SCAN_L_LINEID PRIMARY KEY(`LINEID`)
);

CREATE TABLE `ATL_PS_DIS_STK_ENTRY` (
  `STOCKID` BIGINT(100) NOT NULL AUTO_INCREMENT,
  `SALESREGISTERID` BIGINT(150) DEFAULT NULL,
  `ORDERNUMBER` VARCHAR(250) DEFAULT NULL,
  `ORDERDATE` DATE DEFAULT NULL,
  `DISTRIBUTORID` VARCHAR(250) DEFAULT NULL,
  `DISTRIBUTORNAME` VARCHAR(250) DEFAULT NULL,
  `PRODUCTCODE` VARCHAR(250) DEFAULT NULL,
  `PRODUCTNAME` VARCHAR(250) DEFAULT NULL,
  `UNITSELLINGPRICE` DOUBLE DEFAULT NULL,
  `UOMCODE` VARCHAR(150) DEFAULT NULL,
  `QUANTITY` BIGINT(100) DEFAULT NULL,
  `TOTALININR` DOUBLE DEFAULT NULL,
  `LOTNUMBER` VARCHAR(202) DEFAULT NULL,
  `STOCKCONSIDER` CHAR(2) DEFAULT NULL,
  `STOCKENTRYDATE` DATE DEFAULT NULL,
  `SHIPPEDQUANTITY` VARCHAR(200) DEFAULT NULL,
  `CONVERSIONRATE` VARCHAR(150) DEFAULT NULL,
  `MFGDATE` DATE DEFAULT NULL,
  `EXPDATE` DATE DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_DIS_STK_ENTRY_STOCKID PRIMARY KEY(`STOCKID`)
);


/*
	Retailer
*/
CREATE TABLE `ATL_PS_RETAILER` (
  `RETAILERID` VARCHAR(200) NOT NULL,
  `USERID` INT(10) NOT NULL,
  `RETAILERNAME` VARCHAR(200) DEFAULT NULL,
  `ZONE` VARCHAR(20) DEFAULT NULL,
  `REGION` VARCHAR(20) DEFAULT NULL,
  `STATE` VARCHAR(20) DEFAULT NULL,
  `CITY` VARCHAR(20) DEFAULT NULL,
  `AREA` VARCHAR(20) DEFAULT NULL,
  `ZIPCODE` INT(10) DEFAULT NULL,
  `DISTRIBUTORID` VARCHAR(200) DEFAULT NULL
);

CREATE TABLE `ATL_PS_RET_SCAN_H` (
  `HEADERID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `RETAILERID` VARCHAR(200) DEFAULT NULL,
  `PRODUCTCODE` VARCHAR(200) DEFAULT NULL,
  `PRODUCTNAME` VARCHAR(200) DEFAULT NULL,
  `LASTSCANDATE` DATE DEFAULT NULL,
  `STOCK` BIGINT(20) DEFAULT NULL,
  `STOCKENTRYID` BIGINT(50) DEFAULT NULL,
  `STOCKENTRYDATE` DATE DEFAULT NULL,
  `DELETEFLAG` VARCHAR(100) DEFAULT NULL,
  `DELETEDATE` DATE DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_RET_SCAN_H_HEADERID PRIMARY KEY (`HEADERID`)
);

CREATE TABLE `ATL_PS_RET_SCAN_L` (
  `LINEID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `HEADERID` BIGINT(20) NOT NULL,
  `RETAILERID` VARCHAR(200) DEFAULT NULL,
  `PRODUCTCODE` VARCHAR(200) DEFAULT NULL,
  `CUSTOMERNAME` VARCHAR(200) DEFAULT NULL,
  `BATCHNO` VARCHAR(200) DEFAULT NULL,
  `BOXNO` BIGINT(20) DEFAULT NULL,
  `MFGDATE` DATE DEFAULT NULL,
  `EXPDATE` DATE DEFAULT NULL,
  `SCANDATE` DATE NOT NULL,
  `STOCK` BIGINT(20) DEFAULT NULL,
  `OPENINGSTOCK` BIGINT(50) DEFAULT NULL,
  `CLOSINGSTOCK` BIGINT(50) DEFAULT NULL,
  `BARCODENO` VARCHAR(200) DEFAULT NULL,
  `STOCKCOMMIT` VARCHAR(20) DEFAULT NULL,
  `DELETEFLAG` VARCHAR(20) DEFAULT NULL,
  `DELETEDATE` DATE DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_RET_SCAN_L_LINEID PRIMARY KEY (`LINEID`)
);

CREATE TABLE `ATL_PS_RET_STK_ENTRY` (
  `STOCKID` bigint(100) NOT NULL AUTO_INCREMENT,
  `DISTRIBUTORLINEID` bigint(150) DEFAULT NULL,
  `RETAILERID` varchar(200) DEFAULT NULL,
  `DISTRIBUTORID` varchar(250) DEFAULT NULL,
  `PRODUCTCODE` varchar(250) DEFAULT NULL,
  `PRODUCTNAME` varchar(250) DEFAULT NULL,
  `UOMCODE` varchar(150) DEFAULT NULL,
  `QUANTITY` bigint(100) DEFAULT NULL,
  `SCANDATE` date DEFAULT NULL,
  `LOTNUMBER` varchar(202) DEFAULT NULL,
  `STOCKENTRYDATE` date DEFAULT NULL,
  `CONVERSIONRATE` varchar(150) DEFAULT NULL,
  `MFGDATE` date DEFAULT NULL,
  `EXPDATE` date DEFAULT NULL,
  `STOCKCONSIDER` char(2) DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_RET_STK_ENTRY_STOCKID PRIMARY KEY (`STOCKID`)
);

CREATE TABLE `ATL_PS_RET_ORDER_H` (
  `HEADERID` BIGINT(30) NOT NULL AUTO_INCREMENT,
  `ORDERNO` BIGINT(30) DEFAULT NULL,
  `RETAILERID` VARCHAR(300) DEFAULT NULL,
  `DISTRIBUTORID` VARCHAR(300) DEFAULT NULL,
  `ORDERDATE` DATE DEFAULT NULL,
  `ORDERSTATUS` VARCHAR(20) DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_RET_ORDER_H_HEADERID PRIMARY KEY (`HEADERID`)
);

CREATE TABLE `ATL_PS_RET_ORDER_L` (
  `LINEID` BIGINT(30) NOT NULL AUTO_INCREMENT,
  `HEADERID` BIGINT(30) DEFAULT NULL,
  `PRODUCTCODE` VARCHAR(300) DEFAULT NULL,
  `PRIMARYQTY` BIGINT(30) DEFAULT NULL,
  `SECONDRYQTY` BIGINT(30) DEFAULT NULL,
  `UOM` VARCHAR(100) DEFAULT NULL,
  `PRICE` BIGINT(50) DEFAULT NULL,
  `EXPECTEDDATE` DATE DEFAULT NULL,
  `DELIVERYFLAG` VARCHAR(10) DEFAULT NULL,
  `PAYMENTFLAG` VARCHAR(10) DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_RET_ORDER_L_LINEID PRIMARY KEY (`LINEID`)
);


CREATE TABLE ATL_PS_RET_RECEIPT_SCAN
(
  RECEIPTSCANID INT(10) NOT NULL AUTO_INCREMENT,  
  RETAILERID VARCHAR(100) NOT NULL,
  PRODUCTCODE VARCHAR(100) NOT NULL,
  BATCHNO VARCHAR(100) NOT NULL,
  BOXNO VARCHAR(200),
  QUANTITY VARCHAR(200),
  SCANDATE DATE,
  DISTRIBUTORID VARCHAR(100),
  STOCKCOMMIT VARCHAR(50),
  CONSTRAINT PK_ATL_PS_RET_RECEIPT_SCAN_RECEIPTSCANID PRIMARY KEY(RECEIPTSCANID)
);


/*
	Mapping 
*/
CREATE TABLE `ATL_PS_DIS_RET_MAPPING` (
  `MAPPINGID` INT(10) NOT NULL AUTO_INCREMENT,
  `DISTRIBUTORID` VARCHAR(200) NOT NULL,
  `RETAILERID` VARCHAR(200) NOT NULL,
  `ZONE` VARCHAR(200) DEFAULT NULL,
  `CREATEDBY` VARCHAR(200) NOT NULL,
  `CREATEDDATE` DATE DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_DIS_RET_MAPPING_MAPPINGID PRIMARY KEY (`MAPPINGID`)
);

CREATE TABLE `ATL_PS_STATE` (
  `STATEID` bigint(60) NOT NULL AUTO_INCREMENT,
  `STATENAME` varchar(200) DEFAULT NULL,
  `ZONE` varchar(100) DEFAULT NULL,
  `COUNTRYID` bigint(60) DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_STATE_STATEID PRIMARY KEY (`STATEID`)
);

CREATE TABLE `ATL_PS_DISTRICT` (
  `DISTRICTID` BIGINT(60) NOT NULL AUTO_INCREMENT,
  `DISTRICTNAME` VARCHAR(200) DEFAULT NULL,
  `STATEID` BIGINT(60) DEFAULT NULL,
  `CREATEDBY` VARCHAR(100) DEFAULT NULL,
  `CREATEDDATE` DATE DEFAULT NULL,
  `UPDATEDBY` VARCHAR(100) DEFAULT NULL,
  `UPDATEDDATE` DATETIME DEFAULT NULL,
  CONSTRAINT PK_ATL_PS_DISTRICT_DISTRICTID PRIMARY KEY (`DISTRICTID`)
);



