create table authorized
(
 authorizedKey TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 authorizedName VARCHAR(32) NOT NULL DEFAULT ' ',
 authorizedPassKey VARCHAR(16) NOT NULL DEFAULT ' ',
 authorizedLevel TINYINT(1) UNSIGNED NOT NULL DEFAULT '2',
 authorizedValid ENUM('Y','N') NOT NULL DEFAULT 'Y'
)type=innodb;

create table customer
(
 customerKey INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 customerName VARCHAR(32) NOT NULL DEFAULT ' ',
 customerAddr VARCHAR(48) NOT NULL DEFAULT ' '
)type=innodb;

create table truckingCompany
(
 truckingCompanyKey SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 truckingName VARCHAR(32) NOT NULL DEFAULT ' '
)type=innodb;

create table transRecord
(
 transRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0' PRIMARY KEY,
 transType ENUM('EXP','IMP') NOT NULL DEFAULT 'EXP',
 transPaidFull ENUM('Y','N') NOT NULL DEFAULT 'N',
 transCustKey INT UNSIGNED NOT NULL DEFAULT '0',
 transTrucking SMALLINT UNSIGNED  NOT NULL DEFAULT '0',
 transDoorLocation VARCHAR(32) NOT NULL DEFAULT ' ',
 transLoadingPort VARCHAR(32) NOT NULL DEFAULT ' ',
 transDate DATE NOT NULL DEFAULT '0000-00-00',
 transInvoiceRemarks VARCHAR(128) NOT NULL DEFAULT ' ',
 transDebitNoteRemarks VARCHAR(128) NOT NULL DEFAULT ' ',
 transInvoiceDT DATE NOT NULL DEFAULT '0000-00-00',
 transLastChange DATE NOT NULL DEFAULT '0000-00-00',
 transLastChangeBy TINYINT UNSIGNED NOT NULL DEFAULT '0'
)type=innodb;

create table transVol
(
 transVolKey INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 transVolVolume INT(3) UNSIGNED NOT NULL DEFAULT '0',
 transVolStowage VARCHAR(8) NOT NULL DEFAULT ' ',
 transVolRefKey INT(5) NOT NULL DEFAULT '0',
 PRIMARY KEY(transVolKey,transVolRefKey)
)type=innodb;

create table transContainer
(
 transContKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 transContNo VARCHAR(32) NOT NULL DEFAULT ' ',
 transContSealNo VARCHAR(32) NOT NULL DEFAULT ' ',
 transContRefNo INT(5) UNSIGNED NOT NULL DEFAULT '0',
 transContLastChange DATE NOT NULL DEFAULT '0000-00-00',
 transContLastChangeBy TINYINT UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(transContKey,transContRefNo)
)type=innodb;

create table transPayRecord
(
 transPayKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 transPayCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 transPayAmt DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 transPayDate DATE NOT NULL DEFAULT '0000-00-00',
 transPayMethod ENUM('CASH','CHEQUE','GIRO','REMITTANCE') NOT NULL 
 DEFAULT 'CASH',
 transPayFromRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 transPayAcctRecKey INT UNSIGNED NOT NULL DEFAULT '0',
 transPayAcctPayKey INT UNSIGNED NOT NULL DEFAULT '0',
 transPayLastChange DATE NOT NULL DEFAULT '0000-00-00',
 transPayLastChangeBy TINYINT UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(transPayKey,transPayFromRefKey)
)type=innodb;

create table transBankRecord
(
 transBankKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 transBankOur VARCHAR(16) NOT NULL DEFAULT ' ',
 transBankTheir VARCHAR(16) NOT NULL DEFAULT ' ',
 transBankAcct VARCHAR(18) NOT NULL DEFAULT ' ',
 transBankDocNo VARCHAR(18) NOT NULL DEFAULT ' ',
 transBankPayKey INT UNSIGNED NOT NULL DEFAULT '0',
 transBankLastChange DATE NOT NULL DEFAULT '0000-00-00',
 transBankLastChangeBy TINYINT UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(transBankKey,transBankPayKey)
)type=innodb;


create table impInfoRecord
(
 impInfoKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 impGateOut DATE NOT NULL DEFAULT '0000-00-00',
 impArrival DATE NOT NULL DEFAULT '0000-00-00',
 impContReturnDate DATE NOT NULL DEFAULT '0000-00-00',
 impFromRefKey INT(5) UNSIGNED  NOT NULL DEFAULT '0',
 impInfoLastChange DATE NOT NULL DEFAULT '0000-00-00',
 impInfoLastChangeBy TINYINT UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(impInfoKey,impFromRefKey)
)type=innodb;

create table expInfoRecord
(
 expInfoKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 expSINo VARCHAR(24) NOT NULL DEFAULT ' ',
 expBLNo VARCHAR(24) NOT NULL DEFAULT ' ',
 expDestName VARCHAR(32) NOT NULL DEFAULT ' ',
 expVesselName VARCHAR(32) NOT NULL DEFAULT ' ',
 expStuffingDate DATE NOT NULL DEFAULT '0000-00-00',
 expETD DATE NOT NULL DEFAULT '0000-00-00',
 expFromRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 expInfoLastChange DATE NOT NULL DEFAULT '0000-00-00',
 expInfoLastChangeBy TINYINT NOT NULL DEFAULT '0',
 PRIMARY KEY(expInfoKey,expFromRefKey)
)type=innodb;

create table costRecord
(
 costKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 costTotalOp DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 costTotalOpCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 costTrucking DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 costTruckingCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 costCommission DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 costCommissionCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 costFromRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 costLastChange DATE NOT NULL DEFAULT '0000-00-00',
 costLastChangeBy TINYINT NOT NULL DEFAULT '0',
 PRIMARY KEY(costKey,costFromRefKey)
)type=innodb;

create table otherCost
(
 costOthKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 costOthDesc VARCHAR(32) NOT NULL DEFAULT ' ',
 costOthAmt DECIMAL(14,2) UNSIGNED  NOT NULL DEFAULT '0.00',
 costOthCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 costOthRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 costOthLastChange DATE NOT NULL DEFAULT '0000-00-00',
 costOthLastChangeBy TINYINT NOT NULL DEFAULT '0',
 PRIMARY KEY(costOthKey,costOthRefKey)
)type=innodb;

create table invoiceRecord
(
 invoiceRecKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 invoiceSellAmt DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 invoiceSellCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 invoiceHandleAmt DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 invoiceHandleCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 invoiceRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 invoiceLastChange DATE NOT NULL DEFAULT '0000-00-00',
 invoiceLastChangeBy TINYINT NOT NULL DEFAULT '0',
 PRIMARY KEY(invoiceRecKey,invoiceRefKey)
)type=innodb;

create table invoiceOtherCharges
(
 invoiceOthKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 invoiceOthDesc VARCHAR(32) NOT NULL DEFAULT ' ',
 invoiceOthAmt DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 invoiceOthCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 invoiceOthRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 invoiceOthLastChange DATE NOT NULL DEFAULT '0000-00-00',
 invoiceOthLastChangeBy TINYINT NOT NULL DEFAULT '0',
 PRIMARY KEY(invoiceOthKey,invoiceOthRefKey)
)type=innodb;

create table claimableCharges
(
 claimableChargesKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 claimableTotalLiftOnAmt DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 claimableTotalLiftOnCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 claimableTotalLiftOffAmt DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 claimableTotalLiftOffCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 claimablePrintable ENUM('YES','NO') NOT NULL DEFAULT 'YES',
 claimableRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 claimableChargeLastChange DATE NOT NULL DEFAULT '0000-00-00',
 claimableChargeLastChangeBy TINYINT NOT NULL DEFAULT '0',
 PRIMARY KEY(claimableChargesKey,claimableRefKey)
)type=innodb;

create table claimableOtherCharges
(
 claimableOthChargesKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 claimableOthDesc VARCHAR(32) NOT NULL DEFAULT ' ',
 claimableOthAmt DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 claimableOthCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 claimableOthRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 claimableOthLastChange DATE NOT NULL DEFAULT '0000-00-00',
 claimableOthLastChangeBy TINYINT NOT NULL DEFAULT '0',
 PRIMARY KEY(claimableOthChargesKey,claimableOthRefKey)
)type=innodb;

create table accountReceivable
(
 acctRecKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 acctRecAmt DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 acctRecBalance DECIMAL (14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 acctRecCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 acctRecFromCustKey INT UNSIGNED NOT NULL DEFAULT '0',
 acctRecFromRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 acctRecPaidFull ENUM('Y','N') NOT NULL DEFAULT 'N',
 acctRecLastChange DATE NOT NULL DEFAULT '0000-00-00',
 acctRecLastChangeBy TINYINT UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(acctRecKey,acctRecFromRefKey)
)type=innodb;

create table accountPayable
(
 acctPayKey INT UNSIGNED NOT NULL AUTO_INCREMENT,
 acctPayAmt DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 acctPayBalance DECIMAL(14,2) UNSIGNED NOT NULL DEFAULT '0.00',
 acctPayCurr VARCHAR(3) NOT NULL DEFAULT 'IDR',
 acctPayToTruckCoKey INT UNSIGNED NOT NULL DEFAULT '0',
 acctPayToRefKey INT(5) UNSIGNED NOT NULL DEFAULT '0',
 acctPayPaidFull ENUM('Y','N') NOT NULL DEFAULT 'N',
 acctPayLastChange DATE NOT NULL DEFAULT '0000-00-00',
 acctPayLastChangeBy TINYINT UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(acctPayKey,acctPayToRefKey)
)type=innodb;

