Db

From UG

Contents 1 Intro 2 Table Structures 2.1 mymfavorites 2.2 tblAddressBillToParty 2.3 tblAddressCompanyCity 2.4 tblAddressJaguarVendor 2.5 tblAddressTransportation 2.6 tblAirline

[edit] Intro

This is CT2 DB Schema. Originally received from below:

/*

MySQL Data Transfer

Source Host: 64.115.223.6

Source Database: cybertrax

Target Host: 64.115.223.6

Target Database: cybertrax

Date: 12/18/2009 3:17:18 PM

*/

SET FOREIGN_KEY_CHECKS=0;

[edit] Table Structures

[edit] mymfavorites

DROP TABLE IF EXISTS `mymfavorites`;

CREATE TABLE `mymfavorites` (
  `id` int(11) NOT NULL auto_increment,
  `fav_name` text,
  `fav_source` text,
  `fav_path` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Please do not modify this table!';

[edit] tblAddressBillToParty

DROP TABLE IF EXISTS `tblAddressBillToParty`;

CREATE TABLE `tblAddressBillToParty` (
  `Id` int(10) NOT NULL auto_increment,
  `CompanyName` varchar(70) NOT NULL default '',
  `Attn` varchar(25) NOT NULL,
  `AddressLine1` varchar(100) default NULL,
  `AddressLine2` varchar(100) default NULL,
  `AddressLine3` varchar(100) default NULL,
  `Street` varchar(30) NOT NULL,
  `Building` varchar(5) NOT NULL,
  `City` varchar(30) NOT NULL,
  `Postcode` varchar(10) NOT NULL,
  `ContactPerson` varchar(90) NOT NULL,
  `Remark` varchar(40) NOT NULL,
  `CountryId` int(10) default NULL,
  `USstateId` int(10) default NULL,
  `NonUS_state` varchar(40) default NULL,
  `PhoneCountryCode` varchar(4) NOT NULL,
  `PhoneCityCode` varchar(4) NOT NULL,
  `PhoneNumber` varchar(8) NOT NULL,
  `PhoneExt` varchar(4) NOT NULL,
  `FaxCountryCode` varchar(4) NOT NULL,
  `FaxCityCode` varchar(4) NOT NULL,
  `FaxNumber` varchar(8) NOT NULL,
  `FaxExt` varchar(4) NOT NULL,
  `IsArchived` bit(1) default NULL,
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `CompanyName` (`CompanyName`,`City`,`Remark`),
  KEY `constraintAddressBillToPartyCountry` (`CountryId`),
  KEY `constraintAddressBillToPartyState` (`USstateId`),
  CONSTRAINT `constraintAddressBillToPartyCountry` FOREIGN KEY (`CountryId`) REFERENCES `tblCountry` (`Id`),
  CONSTRAINT `constraintAddressBillToPartyState` FOREIGN KEY (`USstateId`) REFERENCES `tblUSAstate` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 7168 kB; (`CountryId`) REFER `cybertrax/tblCoun';

[edit] tblAddressCompanyCity

DROP TABLE IF EXISTS `tblAddressCompanyCity`;

CREATE TABLE `tblAddressCompanyCity` (
  `Id` int(10) NOT NULL auto_increment,
  `CompanyName` varchar(70) NOT NULL default '',
  `CityName` varchar(30) NOT NULL,
  `IsArchived` bit(1) NOT NULL default '\0',
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `CompanyName` (`CompanyName`,`CityName`)
) ENGINE=InnoDB AUTO_INCREMENT=643 DEFAULT CHARSET=latin1;

[edit] tblAddressJaguarVendor

DROP TABLE IF EXISTS `tblAddressJaguarVendor`;

CREATE TABLE `tblAddressJaguarVendor` (
  `Id` int(10) NOT NULL auto_increment,
  `CompanyCityId` int(10) NOT NULL,
  `Attn` varchar(25) NOT NULL,
  `AddressLine1` varchar(100) default NULL,
  `AddressLine2` varchar(100) default NULL,
  `AddressLine3` varchar(100) default NULL,
  `Street` varchar(30) NOT NULL,
  `Building` varchar(5) NOT NULL,
  `Postcode` varchar(10) NOT NULL,
  `ContactPerson` varchar(90) NOT NULL,
  `Remark` varchar(40) NOT NULL,
  `IsAirline` bit(1) NOT NULL default '\0',
  `IsSteamshipline` bit(1) NOT NULL default '\0',
  `IsTruckingCompany` bit(1) NOT NULL default '\0',
  `IsCustomsBroker` bit(1) NOT NULL default '\0',
  `IsCoLoaderOcean` bit(1) NOT NULL default '\0',
  `IsCoLoaderAir` bit(1) NOT NULL default '\0',
  `IsCargoLocation` bit(1) NOT NULL default '\0',
  `IsConsolidationWarehouse` bit(1) NOT NULL default '\0',
  `IsCustomsInspectionFacility` bit(1) NOT NULL default '\0',
  `IsJaguarOffice` bit(1) NOT NULL default '\0',
  `IsOverseasAgent` bit(1) NOT NULL default '\0',
  `CountryId` int(10) default NULL,
  `USstateId` int(10) default NULL,
  `NonUS_state` varchar(40) default NULL,
  `PhoneCountryCode` varchar(4) NOT NULL,
  `PhoneCityCode` varchar(4) NOT NULL,
  `PhoneNumber` varchar(8) NOT NULL,
  `PhoneExt` varchar(4) NOT NULL,
  `FaxCountryCode` varchar(4) NOT NULL,
  `FaxCityCode` varchar(4) NOT NULL,
  `FaxNumber` varchar(8) NOT NULL,
  `FaxExt` varchar(4) NOT NULL,
  `IsArchived` bit(1) default NULL,
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `AddressId` (`CompanyCityId`,`Remark`),
  KEY `constraintAddressJaguarVendorCountry` (`CountryId`),
  KEY `constraintAddressJaguarVendorState` (`USstateId`),
  CONSTRAINT `constraintAddressJaguarVendorCountry` FOREIGN KEY (`CountryId`) REFERENCES `tblCountry` (`Id`),
  CONSTRAINT `constraintAddressJaguarVendorState` FOREIGN KEY (`USstateId`) REFERENCES `tblUSAstate` (`Id`),
  CONSTRAINT `tblAddressJaguarVendor_ibfk_1` FOREIGN KEY (`CompanyCityId`) REFERENCES `tblAddressCompanyCity` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=166 DEFAULT CHARSET=latin1;

[edit] tblAddressTransportation

DROP TABLE IF EXISTS `tblAddressTransportation`;

CREATE TABLE `tblAddressTransportation` (
  `Id` int(10) NOT NULL auto_increment,
  `CompanyCityId` int(10) NOT NULL,
  `Attn` varchar(25) NOT NULL,
  `AddressLine1` varchar(100) default NULL,
  `AddressLine2` varchar(100) default NULL,
  `AddressLine3` varchar(100) default NULL,
  `Street` varchar(30) NOT NULL,
  `Building` varchar(5) NOT NULL,
  `Postcode` varchar(10) NOT NULL,
  `ContactPerson` varchar(90) NOT NULL,
  `Remark` varchar(40) NOT NULL,
  `IsShipper` bit(1) NOT NULL default '\0',
  `IsConsignee` bit(1) NOT NULL default '\0',
  `IsNotify` bit(1) NOT NULL default '\0',
  `ExportPickUpLocation` bit(1) NOT NULL default '\0',
  `ExportDeliveryLocation` bit(1) NOT NULL default '\0',
  `ImportPickUpLocation` bit(1) NOT NULL default '\0',
  `ImportDeliveryLocation` bit(1) NOT NULL default '\0',
  `IsDestinationReceivingAgent` bit(1) NOT NULL default '\0',
  `MasterPickUp` bit(1) NOT NULL default '\0',
  `MasterDeliveryAddress` bit(1) NOT NULL default '\0',
  `MasterShipper` bit(1) NOT NULL default '\0',
  `MasterConsignee` bit(1) NOT NULL default '\0',
  `MasterNotify` bit(1) NOT NULL default '\0',
  `CountryId` int(10) default NULL,
  `USstateId` int(10) default NULL,
  `NonUS_state` varchar(40) default NULL,
  `PhoneCountryCode` varchar(4) NOT NULL,
  `PhoneCityCode` varchar(4) NOT NULL,
  `PhoneNumber` varchar(8) NOT NULL,
  `PhoneExt` varchar(4) NOT NULL,
  `FaxCountryCode` varchar(4) NOT NULL,
  `FaxCityCode` varchar(4) NOT NULL,
  `FaxNumber` varchar(8) NOT NULL,
  `FaxExt` varchar(4) NOT NULL,
  `IsArchived` bit(1) default NULL,
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `AddressId` (`CompanyCityId`,`Remark`),
  KEY `constraintAddressTransportationCountry` (`CountryId`),
  KEY `constraintAddressTransportationState` (`USstateId`),
  CONSTRAINT `constraintAddressTransportationCountry` FOREIGN KEY (`CountryId`) REFERENCES `tblCountry` (`Id`),
  CONSTRAINT `constraintAddressTransportationState` FOREIGN KEY (`USstateId`) REFERENCES `tblUSAstate` (`Id`),
  CONSTRAINT `tblAddressTransportation_ibfk_1` FOREIGN KEY (`CompanyCityId`) REFERENCES `tblAddressCompanyCity` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=415 DEFAULT CHARSET=latin1;

[edit] tblAirline

DROP TABLE IF EXISTS `tblAirline`;

CREATE TABLE `tblAirline` (
  `Id` int(10) NOT NULL auto_increment,
  `TwoLetterCode` varchar(2) NOT NULL,
  `Name` varchar(30) NOT NULL,
  `NumericCode` varchar(3) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblAirport

-- ----------------------------

DROP TABLE IF EXISTS `tblAirport`;

CREATE TABLE `tblAirport` (

 `Id` int(10) NOT NULL auto_increment,
 `City` varchar(50) NOT NULL,
 `CountryID` int(10) NOT NULL,
 `LetterCode` varchar(3) default NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`),
 KEY `Airport_CountryID` (`CountryID`),
 CONSTRAINT `tblAirport_ibfk_1` FOREIGN KEY (`CountryID`) REFERENCES `tblCountry` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblAirShipment

-- ----------------------------

DROP TABLE IF EXISTS `tblAirShipment`;

CREATE TABLE `tblAirShipment` (

 `AirShipmentId` int(10) NOT NULL auto_increment,
 `GenericShipmentId` int(10) NOT NULL,
 `Hazardous` tinyint(1) NOT NULL default '0',
 `HazardousNote` varchar(1500) default NULL,
 `Freight` int(1) default '0',
 `AirlineId` int(10) default NULL,
 `CoLoaderAirId` int(10) default NULL,
 `Hawb` varchar(30) default NULL,
 `MasterBillA` varchar(3) NOT NULL default ,
 `MasterBillB` varchar(4) NOT NULL default ,
 `MasterBillC` varchar(4) NOT NULL default ,
 `HouseBillOfLanding` varchar(30) default NULL,
 `FlightOne` varchar(30) default NULL,
 `FlightTwo` varchar(30) default NULL,
 `AirportOfDepartureId` int(10) default NULL,
 `AirportOfDepartureETD` date default NULL,
 `AirportOfDepartureATD` date default NULL,
 `TransShipmentAirportId` int(10) default NULL,
 `TransShipmentAirportETA` date default NULL,
 `TransShipmentAirportATA` date default NULL,
 `AirportOfDestinationId` int(10) default NULL,
 `AirportOfDestinationETA` date default NULL,
 `AirportOfDestinationATA` date default NULL,
 `DestinationReceivingAgentId` int(10) default NULL,
 `HouseAirwayBillComments` varchar(1500) default NULL,
 `EnabledArrivalToWH` tinyint(1) default '1',
 `ArrivalToWarehouseDate` date default NULL,
 `ItNumber` varchar(17) default NULL,
 `ItDate` date default NULL,
 `ItLocation` varchar(20) default NULL,
 `CustomsBrokerId` int(10) default NULL,
 `CustomsBrokerDescription` varchar(1500) default NULL,
 `CargoLocationId` int(10) default NULL,
 `CargoLocationDescription` varchar(1500) default NULL,
 `LastFreeDay` date default NULL,
 `ArrivalNoticeComments` varchar(1500) default NULL,
 `DeliveryAddressId` int(10) default NULL,
 `DeliveryAddressDescription` varchar(1500) default NULL,
 `DeliveryTruckerId` int(10) default NULL,
 `ImportEstimatedDeliveryDate` date default NULL,
 `ImportActualDeliveryDate` date default NULL,
 `DeliveryComments` varchar(100) default NULL,
 `TruckerDeliveryInstructions` varchar(1500) default NULL,
 `PaymentFreightChargesRequired` int(1) default '0',
 `CustomsStatusId` int(10) default NULL,
 `CustomsStatusDate` date default NULL,
 `FDAStatusId` int(10) default NULL,
 `FDAStatusDate` date default NULL,
 `DeliveryDateTypeId` int(10) default NULL,
 `ExportCustoms` varchar(30) default NULL,
 `ActualDeliveryDateEnabled` tinyint(1) default '0',
 PRIMARY KEY  (`AirShipmentId`),
 KEY `OceanShipment_GenericShipmentId` (`GenericShipmentId`),
 KEY `AirShipment_AirlineId` (`AirlineId`),
 KEY `AirShipment_CoLoaderAirId` (`CoLoaderAirId`),
 KEY `AirShipment_AirportOfDepartureId` (`AirportOfDepartureId`),
 KEY `AirShipment_TransShipmentAirportId` (`TransShipmentAirportId`),
 KEY `AirShipment_AirportOfDestinationId` (`AirportOfDestinationId`),
 KEY `AirShipment_DestinationReceivingAgentId` (`DestinationReceivingAgentId`),
 KEY `AirShipment_CustomsBrokerId` (`CustomsBrokerId`),
 KEY `AirShipment_CargoLocationId` (`CargoLocationId`),
 KEY `AirShipment_DeliveryAddressId` (`DeliveryAddressId`),
 KEY `AirShipment_DeliveryTruckerId` (`DeliveryTruckerId`),
 KEY `AirShipment_CustomsStatusId` (`CustomsStatusId`),
 KEY `AirShipment_FDAStatusId` (`FDAStatusId`),
 CONSTRAINT `tblAirShipment_ibfk_1` FOREIGN KEY (`GenericShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE,
 CONSTRAINT `tblAirShipment_ibfk_10` FOREIGN KEY (`DeliveryAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_11` FOREIGN KEY (`DeliveryTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_12` FOREIGN KEY (`CustomsStatusId`) REFERENCES `tblCustomsStatus` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_13` FOREIGN KEY (`FDAStatusId`) REFERENCES `tblFDAStatus` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_2` FOREIGN KEY (`AirlineId`) REFERENCES `tblAirline` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_3` FOREIGN KEY (`CoLoaderAirId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_4` FOREIGN KEY (`AirportOfDepartureId`) REFERENCES `tblAirport` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_5` FOREIGN KEY (`TransShipmentAirportId`) REFERENCES `tblAirport` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_6` FOREIGN KEY (`AirportOfDestinationId`) REFERENCES `tblAirport` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_7` FOREIGN KEY (`DestinationReceivingAgentId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_8` FOREIGN KEY (`CustomsBrokerId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblAirShipment_ibfk_9` FOREIGN KEY (`CargoLocationId`) REFERENCES `tblAddressTransportation` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=504 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblAlwaysBCCEmails

-- ----------------------------

DROP TABLE IF EXISTS `tblAlwaysBCCEmails`;

CREATE TABLE `tblAlwaysBCCEmails` (

 `Id` int(10) NOT NULL auto_increment,
 `UserId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `UserId` (`UserId`),
 CONSTRAINT `tblAlwaysBCCEmails_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblAuthorizationMethod

-- ----------------------------

DROP TABLE IF EXISTS `tblAuthorizationMethod`;

CREATE TABLE `tblAuthorizationMethod` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientCompanyId` int(11) NOT NULL,
 `Method` varchar(30) NOT NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `uniqueMethodClientCompanyId` (`Method`,`ClientCompanyId`)

) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblAuthorizationMethodSettings

-- ----------------------------

DROP TABLE IF EXISTS `tblAuthorizationMethodSettings`;

CREATE TABLE `tblAuthorizationMethodSettings` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientCompanyId` int(10) NOT NULL,
 `UseDefaultMethods` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=220 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblBusiestSeaPort

-- ----------------------------

DROP TABLE IF EXISTS `tblBusiestSeaPort`;

CREATE TABLE `tblBusiestSeaPort` (

 `Id` int(10) NOT NULL auto_increment,
 `SeaPortId` int(10) NOT NULL,
 `Year` smallint(6) default NULL,
 `TEU` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `SeaPortId` (`SeaPortId`),
 CONSTRAINT `tblBusiestSeaPort_fk` FOREIGN KEY (`SeaPortId`) REFERENCES `tblSeaPort` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblChargeBackInvoice

-- ----------------------------

DROP TABLE IF EXISTS `tblChargeBackInvoice`;

CREATE TABLE `tblChargeBackInvoice` (

 `Id` int(10) NOT NULL auto_increment,
 `Number` int(10) NOT NULL,
 `OfficeId` int(10) NOT NULL,
 `CurrencyId` int(10) NOT NULL,
 `Amount` double NOT NULL,
 `Date` date NOT NULL,
 `ShipmentId` int(10) default NULL,
 `MasterId` int(10) default NULL,
 `UserId` int(10) NOT NULL,
 `PurchaseInvoiceId` int(10) NOT NULL,
 `ExchangeRate` double NOT NULL default '1',
 PRIMARY KEY  (`Id`),
 KEY `OfficeId` (`OfficeId`),
 KEY `CurrencyId` (`CurrencyId`),
 KEY `ShipmentId` (`ShipmentId`),
 KEY `MasterId` (`MasterId`),
 KEY `UserId` (`UserId`),
 KEY `PurchaseInvoiceId` (`PurchaseInvoiceId`),
 CONSTRAINT `tblChargeBackInvoice_fk` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblChargeBackInvoice_fk1` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblChargeBackInvoice_fk4` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblChargeBackInvoice_fk5` FOREIGN KEY (`PurchaseInvoiceId`) REFERENCES `tblPurchaseInvoice` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblChargeCode

-- ----------------------------

DROP TABLE IF EXISTS `tblChargeCode`;

CREATE TABLE `tblChargeCode` (

 `Id` int(10) NOT NULL auto_increment,
 `ChargeCodeNumber` int(10) NOT NULL,
 `Description` varchar(150) NOT NULL,
 `MorI` tinyint(1) NOT NULL,
 `CalculationType` int(10) default NULL,
 `QuantityField` varchar(25) default NULL,
 `QuantityUnitTypeId` int(10) default NULL,
 `RateUnitTypeId` int(10) default NULL,
 `QuantityFieldId` int(10) default NULL,
 `CBVType` smallint(6) NOT NULL default '0',
 PRIMARY KEY  (`Id`),
 KEY `QuantityUnitTypeId` (`QuantityUnitTypeId`),
 KEY `RateUnitTypeId` (`RateUnitTypeId`),
 CONSTRAINT `tblChargeCode_fk1` FOREIGN KEY (`RateUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblChargeCode_fk` FOREIGN KEY (`QuantityUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

-- ----------------------------

-- Table structure for tblClient

-- ----------------------------

DROP TABLE IF EXISTS `tblClient`;

CREATE TABLE `tblClient` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(50) NOT NULL,
 `Email` varchar(50) NOT NULL,
 `ContactName` varchar(50) NOT NULL,
 `ContactPhone` varchar(20) NOT NULL,
 `NotifyOnEstimatedDepartureDate` int(1) NOT NULL,
 `ReceiveDateChangeNotification` int(1) NOT NULL,
 `RecepientEmail` varchar(50) NOT NULL,
 `CurrentTopVendorID` int(10) default NULL,
 `CurrentTopConsigneeID` int(10) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientCompany

-- ----------------------------

DROP TABLE IF EXISTS `tblClientCompany`;

CREATE TABLE `tblClientCompany` (

 `Id` int(10) NOT NULL auto_increment,
 `CompanyName` varchar(50) NOT NULL,
 `InternalOpsGroupId` int(10) default NULL,
 `SalesPersonId` int(10) default NULL,
 `CreditStatusId` int(10) default NULL,
 `CreditLimitId` int(10) default NULL,
 `CreditTermId` int(10) default NULL,
 `DisplayExchangeRates` tinyint(1) NOT NULL default '0',
 `ShowDeliveryLocation` tinyint(1) NOT NULL default '0',
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`),
 KEY `tblClientCompany_ibfk_1` (`InternalOpsGroupId`),
 KEY `tblClientCompany_ibfk_2` (`SalesPersonId`),
 KEY `tblClientCompany_ibfk_3` (`CreditStatusId`),
 KEY `tblClientCompany_ibfk_4` (`CreditLimitId`),
 KEY `tblClientCompany_ibfk_5` (`CreditTermId`),
 CONSTRAINT `tblClientCompany_ibfk_1` FOREIGN KEY (`InternalOpsGroupId`) REFERENCES `tblJaguarUserGroup` (`Id`),
 CONSTRAINT `tblClientCompany_ibfk_2` FOREIGN KEY (`SalesPersonId`) REFERENCES `tblJaguarUser` (`Id`),
 CONSTRAINT `tblClientCompany_ibfk_3` FOREIGN KEY (`CreditStatusId`) REFERENCES `tblCreditStatus` (`Id`),
 CONSTRAINT `tblClientCompany_ibfk_4` FOREIGN KEY (`CreditLimitId`) REFERENCES `tblCreditLimit` (`Id`),
 CONSTRAINT `tblClientCompany_ibfk_5` FOREIGN KEY (`CreditTermId`) REFERENCES `tblCreditTerm` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=270 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientCompanyGroup

-- ----------------------------

DROP TABLE IF EXISTS `tblClientCompanyGroup`;

CREATE TABLE `tblClientCompanyGroup` (

 `Id` int(10) NOT NULL auto_increment,
 `GroupName` varchar(30) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientCompanyGroupRelation

-- ----------------------------

DROP TABLE IF EXISTS `tblClientCompanyGroupRelation`;

CREATE TABLE `tblClientCompanyGroupRelation` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientCompanyGroupId` int(10) NOT NULL,
 `ClientCompanyId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `ClientCompanyId` (`ClientCompanyId`),
 KEY `ClientCompanyGroupId` (`ClientCompanyGroupId`),
 CONSTRAINT `tblClientCompanyGroupRelation_fk` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblClientCompanyGroupRelation_fk1` FOREIGN KEY (`ClientCompanyGroupId`) REFERENCES `tblClientCompanyGroup` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientExchangeRate

-- ----------------------------

DROP TABLE IF EXISTS `tblClientExchangeRate`;

CREATE TABLE `tblClientExchangeRate` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientID` int(10) NOT NULL,
 `ExchangeRateNumber` int(10) NOT NULL,
 `ThisClientExchangeRate` double NOT NULL,
 `LastUpdated` datetime NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientMode

-- ----------------------------

DROP TABLE IF EXISTS `tblClientMode`;

CREATE TABLE `tblClientMode` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientCompanyId` int(10) NOT NULL,
 `TransportModeId` int(10) NOT NULL,
 `ClientMode` varchar(30) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientsGroup

-- ----------------------------

DROP TABLE IF EXISTS `tblClientsGroup`;

CREATE TABLE `tblClientsGroup` (

 `Id` int(10) NOT NULL auto_increment,
 `GroupName` varchar(30) NOT NULL default ,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientsGroupRelation

-- ----------------------------

DROP TABLE IF EXISTS `tblClientsGroupRelation`;

CREATE TABLE `tblClientsGroupRelation` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientsGroupId` int(10) NOT NULL,
 `ClientUserId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientUser

-- ----------------------------

DROP TABLE IF EXISTS `tblClientUser`;

CREATE TABLE `tblClientUser` (

 `Id` int(10) NOT NULL auto_increment,
 `GenericUserId` int(10) NOT NULL,
 `EmployedById` int(10) default NULL,
 `RSSFeedId` int(10) default NULL,
 `AddressBillToPartyId` int(10) default NULL,
 `AddressJaguarVendorId` int(10) default NULL,
 `AddressTransportationId` int(10) default NULL,
 `ClientCompanyId` int(10) default NULL,
 `IsAllShippersVisible` bit(1) NOT NULL default '�',
 `IsAllConsigneesVisible` bit(1) NOT NULL default '�',
 `IsAllOriginRegionsVisible` bit(1) NOT NULL default '�',
 `IsAllDestinationRegionsVisible` bit(1) NOT NULL default '�',
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `GenericUserId` (`GenericUserId`),
 KEY `EmployedById` (`EmployedById`),
 KEY `AddressBillToPartyId` (`AddressBillToPartyId`),
 KEY `AddressJaguarVendorId` (`AddressJaguarVendorId`),
 KEY `AddressTransportationId` (`AddressTransportationId`),
 KEY `ClientCompanyId` (`ClientCompanyId`),
 CONSTRAINT `tblClientUser_fk` FOREIGN KEY (`AddressBillToPartyId`) REFERENCES `tblAddressBillToParty` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblClientUser_fk1` FOREIGN KEY (`AddressJaguarVendorId`) REFERENCES `tblAddressJaguarVendor` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblClientUser_fk2` FOREIGN KEY (`AddressTransportationId`) REFERENCES `tblAddressTransportation` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblClientUser_fk4` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblClientUser_ibfk_1` FOREIGN KEY (`GenericUserId`) REFERENCES `tblGenericUser` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientUser2AddressTransportation

-- ----------------------------

DROP TABLE IF EXISTS `tblClientUser2AddressTransportation`;

CREATE TABLE `tblClientUser2AddressTransportation` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientUserId` int(10) default NULL,
 `AddressTransportationId` int(10) default NULL,
 `IsShipper` bit(1) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `ClientUserId` (`ClientUserId`),
 KEY `AddressTransportationId` (`AddressTransportationId`),
 CONSTRAINT `tblClientUser2AddressTransportation_fk` FOREIGN KEY (`ClientUserId`) REFERENCES `tblClientUser` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblClientUser2AddressTransportation_fk1` FOREIGN KEY (`AddressTransportationId`) REFERENCES `tblAddressTransportation` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientUser2Region

-- ----------------------------

DROP TABLE IF EXISTS `tblClientUser2Region`;

CREATE TABLE `tblClientUser2Region` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientUserId` int(10) default NULL,
 `RegionId` int(10) default NULL,
 `IsOrigin` bit(1) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `ClientUserId` (`ClientUserId`),
 KEY `RegionId` (`RegionId`),
 CONSTRAINT `tblClientUser2Region_fk` FOREIGN KEY (`ClientUserId`) REFERENCES `tblClientUser` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblClientUser2Region_fk1` FOREIGN KEY (`RegionId`) REFERENCES `tblRegion` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblClientUserWatchList

-- ----------------------------

DROP TABLE IF EXISTS `tblClientUserWatchList`;

CREATE TABLE `tblClientUserWatchList` (

 `Id` int(10) NOT NULL auto_increment,
 `UserId` int(10) NOT NULL,
 `ShipmentId` int(10) NOT NULL,
 `NickName` varchar(45) default NULL,
 `IsInTop5` bit(1) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `UserId` (`UserId`),
 KEY `ShipmentId` (`ShipmentId`),
 CONSTRAINT `tblClientUserWatchList_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblClientUserWatchList_ibfk_2` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=175 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCommentsMailedTo

-- ----------------------------

DROP TABLE IF EXISTS `tblCommentsMailedTo`;

CREATE TABLE `tblCommentsMailedTo` (

 `CommentId` int(10) NOT NULL,
 `UserId` int(10) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCommodity

-- ----------------------------

DROP TABLE IF EXISTS `tblCommodity`;

CREATE TABLE `tblCommodity` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentId` int(10) default NULL,
 `TotalHazardousOfPlts` int(10) default NULL,
 `TotalHazardousPkgsOnPlts` int(10) default NULL,
 `TotalHazardousLoosePkgs` int(11) NOT NULL default '0',
 `TotalHazardousGrossKg` float(9,2) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `ShipmentId` (`ShipmentId`),
 CONSTRAINT `tblCommodity_fk` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1191 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCommodityDisplaySettings

-- ----------------------------

DROP TABLE IF EXISTS `tblCommodityDisplaySettings`;

CREATE TABLE `tblCommodityDisplaySettings` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientCompanyId` int(10) default NULL,
 `InvoiceViewable` tinyint(1) NOT NULL default '0',
 `POViewable` tinyint(1) NOT NULL default '0',
 `SKUViewable` tinyint(1) NOT NULL default '0',
 `QuantityViewable` tinyint(1) NOT NULL default '0',
 `PackagingTypeViewable` tinyint(1) NOT NULL default '0',
 `ItemDescriptionViewable` tinyint(1) NOT NULL default '0',
 `PiecesPerPackagingViewable` tinyint(1) NOT NULL default '0',
 `ExtraField1Label` varchar(30) default NULL,
 `ExtraField1Viewable` tinyint(1) NOT NULL default '0',
 `ExtraField2Label` varchar(30) default NULL,
 `ExtraField2Viewable` tinyint(1) NOT NULL default '0',
 `ExtraField3Label` varchar(30) default NULL,
 `ExtraField3Viewable` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`Id`),
 KEY `ClientCompanyId_FK` (`ClientCompanyId`),
 CONSTRAINT `ClientCompanyId_FK` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=246 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCommodityItem

-- ----------------------------

DROP TABLE IF EXISTS `tblCommodityItem`;

CREATE TABLE `tblCommodityItem` (

 `Id` int(10) NOT NULL auto_increment,
 `CommodityId` int(10) default NULL,
 `Invoice` varchar(30) default NULL,
 `PurchaseOrder` varchar(30) default NULL,
 `SQU` varchar(30) default NULL,
 `Quantity` int(10) default NULL,
 `PackagingTypeId` int(10) default NULL,
 `Description` varchar(60) default NULL,
 `PiecesPerPackaging` int(10) default NULL,
 `TotalOfPlts` int(10) default NULL,
 `TotalPkgsOnPlts` int(10) default NULL,
 `TotalPkgsOnPltsTypeId` int(10) default NULL,
 `LoosePkgsOnPlts` int(10) default NULL,
 `LoosePkgsOnPltsTypeId` int(10) default NULL,
 `TotalGrossKg` float(9,2) default NULL,
 `ContainerId` int(10) default NULL,
 `ExtraField1` varchar(30) default NULL,
 `ExtraField2` varchar(30) default NULL,
 `ExtraField3` varchar(30) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `tblCommodityItem_ibfk_1` (`ContainerId`),
 KEY `CommodityId` (`CommodityId`),
 CONSTRAINT `tblCommodityItem_fk` FOREIGN KEY (`CommodityId`) REFERENCES `tblCommodity` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblCommodityItem_ibfk_1` FOREIGN KEY (`ContainerId`) REFERENCES `tblContainer` (`Id`) ON DELETE SET NULL

) ENGINE=InnoDB AUTO_INCREMENT=1094 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblContainer

-- ----------------------------

DROP TABLE IF EXISTS `tblContainer`;

CREATE TABLE `tblContainer` (

 `Id` int(10) NOT NULL auto_increment,
 `ContainerIdA` char(4) default NULL,
 `ContainerIdB` char(6) default NULL,
 `ContainerIdC` char(1) default NULL,
 `Seal` varchar(20) default ,
 `ContainerTypeId` int(10) default NULL,
 `ContainerSizeId` int(10) default NULL,
 `EstimatedCollection` date default NULL,
 `ActualCollection` date default NULL,
 `ContainerRequestDelivery` date default NULL,
 `ContainerActualDelivery` date default NULL,
 `DeliveryLocationId` int(10) default NULL,
 `ShipmentId` int(10) default NULL,
 `MasterId` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `ContainerTypeId` (`ContainerTypeId`),
 KEY `tblContainer_ibfk_2` (`ShipmentId`),
 KEY `tblContainer_ibfk_3` (`MasterId`),
 KEY `tblContainer_ibfk_4` (`DeliveryLocationId`),
 CONSTRAINT `tblContainer_ibfk_1` FOREIGN KEY (`ContainerTypeId`) REFERENCES `tblContainerType` (`Id`),
 CONSTRAINT `tblContainer_ibfk_2` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE,
 CONSTRAINT `tblContainer_ibfk_3` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON DELETE CASCADE,
 CONSTRAINT `tblContainer_ibfk_4` FOREIGN KEY (`DeliveryLocationId`) REFERENCES `tblDeliveryLocation` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=670 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblContainer2Master

-- ----------------------------

DROP TABLE IF EXISTS `tblContainer2Master`;

CREATE TABLE `tblContainer2Master` (

 `ContainerId` int(10) NOT NULL,
 `MasterId` int(10) NOT NULL,
 UNIQUE KEY `ContainerId` (`ContainerId`,`MasterId`),
 KEY `MasterId` (`MasterId`),
 CONSTRAINT `tblContainer2Master_ibfk_1` FOREIGN KEY (`ContainerId`) REFERENCES `tblContainer` (`Id`) ON DELETE CASCADE,
 CONSTRAINT `tblContainer2Master_ibfk_2` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblContainer2Shipment

-- ----------------------------

DROP TABLE IF EXISTS `tblContainer2Shipment`;

CREATE TABLE `tblContainer2Shipment` (

 `ContainerId` int(10) NOT NULL,
 `ShipmentId` int(10) NOT NULL,
 UNIQUE KEY `ContainerId` (`ContainerId`,`ShipmentId`),
 KEY `ShipmentId` (`ShipmentId`),
 CONSTRAINT `tblContainer2Shipment_ibfk_1` FOREIGN KEY (`ContainerId`) REFERENCES `tblContainer` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblContainerSize

-- ----------------------------

DROP TABLE IF EXISTS `tblContainerSize`;

CREATE TABLE `tblContainerSize` (

 `Id` int(10) NOT NULL auto_increment,
 `Description` varchar(30) NOT NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblContainerType

-- ----------------------------

DROP TABLE IF EXISTS `tblContainerType`;

CREATE TABLE `tblContainerType` (

 `Id` int(10) NOT NULL auto_increment,
 `Description` varchar(30) NOT NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCountry

-- ----------------------------

DROP TABLE IF EXISTS `tblCountry`;

CREATE TABLE `tblCountry` (

 `Id` int(10) NOT NULL auto_increment,
 `ISO2` char(2) NOT NULL,
 `Name` varchar(80) NOT NULL,
 `PrintableName` varchar(80) NOT NULL,
 `ISO3` char(3) default NULL,
 `NumCode` smallint(6) default NULL,
 `RegionId` int(10) default NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=241 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCreditLimit

-- ----------------------------

DROP TABLE IF EXISTS `tblCreditLimit`;

CREATE TABLE `tblCreditLimit` (

 `Id` int(10) NOT NULL auto_increment,
 `Limit` varchar(50) NOT NULL default ,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCreditStatus

-- ----------------------------

DROP TABLE IF EXISTS `tblCreditStatus`;

CREATE TABLE `tblCreditStatus` (

 `Id` int(10) NOT NULL auto_increment,
 `Status` varchar(50) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCreditTerm

-- ----------------------------

DROP TABLE IF EXISTS `tblCreditTerm`;

CREATE TABLE `tblCreditTerm` (

 `Id` int(10) NOT NULL auto_increment,
 `Term` int(10) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCurrency

-- ----------------------------

DROP TABLE IF EXISTS `tblCurrency`;

CREATE TABLE `tblCurrency` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(50) NOT NULL,
 `Abbreviation` varchar(3) NOT NULL,
 `IsMain` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCurrencyExchangeRate

-- ----------------------------

DROP TABLE IF EXISTS `tblCurrencyExchangeRate`;

CREATE TABLE `tblCurrencyExchangeRate` (

 `Id` int(10) NOT NULL auto_increment,
 `FromCurrencyId` int(10) NOT NULL,
 `ToCurrencyId` int(10) NOT NULL,
 `Rate` double default NULL,
 `OfficeId` int(10) NOT NULL,
 `ClientCompanyId` int(10) default NULL,
 `DateTime` datetime default NULL,
 PRIMARY KEY  (`Id`),
 KEY `FromCurrencyId` (`FromCurrencyId`),
 KEY `ToCurrencyId` (`ToCurrencyId`),
 KEY `OfficeId` (`OfficeId`),
 KEY `ClientCompanyId` (`ClientCompanyId`),
 CONSTRAINT `tblCurrencyExchangeRate_fk` FOREIGN KEY (`FromCurrencyId`) REFERENCES `tblCurrency` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblCurrencyExchangeRate_fk1` FOREIGN KEY (`ToCurrencyId`) REFERENCES `tblCurrency` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblCurrencyExchangeRate_fk2` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblCurrencyExchangeRate_fk3` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=2094 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCurrencyExchangeRateChange

-- ----------------------------

DROP TABLE IF EXISTS `tblCurrencyExchangeRateChange`;

CREATE TABLE `tblCurrencyExchangeRateChange` (

 `Id` int(10) NOT NULL auto_increment,
 `OfficeId` int(10) NOT NULL,
 `FromCurrencyAbbreviation` varchar(3) NOT NULL default ,
 `ToCurrencyAbbreviation` varchar(3) NOT NULL default ,
 `OldRate` double default NULL,
 `NewRate` double default NULL,
 `DateTime` datetime NOT NULL,
 `GenericUserId` int(10) NOT NULL,
 `ClientCompanyId` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `OfficeId` (`OfficeId`),
 KEY `GenericUserId` (`GenericUserId`),
 KEY `ClientCompanyId` (`ClientCompanyId`),
 CONSTRAINT `tblCurrencyExchangeRateChange_fk` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblCurrencyExchangeRateChange_fk1` FOREIGN KEY (`GenericUserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblCurrencyExchangeRateChange_fk2` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCustomer

-- ----------------------------

DROP TABLE IF EXISTS `tblCustomer`;

CREATE TABLE `tblCustomer` (

 `Id` int(10) NOT NULL auto_increment,
 `UserID` int(10) NOT NULL,
 `ClientID` int(10) NOT NULL,
 `AdditionalClientID` int(10) NOT NULL,
 `ThirdClientID` int(10) NOT NULL,
 `ShipperFilterID` int(10) NOT NULL,
 `ConsigneeFilterID` int(10) NOT NULL,
 `Name` varchar(50) NOT NULL,
 `CompanyName` varchar(50) NOT NULL,
 `Email` varchar(50) NOT NULL,
 `UserType` int(10) NOT NULL,
 `Enabled` int(1) NOT NULL,
 `Permissions` int(10) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblCustomsStatus

-- ----------------------------

DROP TABLE IF EXISTS `tblCustomsStatus`;

CREATE TABLE `tblCustomsStatus` (

 `Id` int(10) NOT NULL auto_increment,
 `CustomsStatusName` varchar(30) NOT NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblDailyQuotes

-- ----------------------------

DROP TABLE IF EXISTS `tblDailyQuotes`;

CREATE TABLE `tblDailyQuotes` (

 `Id` int(10) NOT NULL auto_increment,
 `Text` varchar(512) default NULL,
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `Id` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblDeliveryDateTypes

-- ----------------------------

DROP TABLE IF EXISTS `tblDeliveryDateTypes`;

CREATE TABLE `tblDeliveryDateTypes` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(50) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblDeliveryLocation

-- ----------------------------

DROP TABLE IF EXISTS `tblDeliveryLocation`;

CREATE TABLE `tblDeliveryLocation` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientCompanyId` int(10) default NULL,
 `LocationName` varchar(50) NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `tblDeliveryLocation_ibfk_1` (`ClientCompanyId`),
 CONSTRAINT `tblDeliveryLocation_ibfk_1` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblDropClientTops

-- ----------------------------

DROP TABLE IF EXISTS `tblDropClientTops`;

CREATE TABLE `tblDropClientTops` (

 `NumId` int(10) unsigned NOT NULL auto_increment,
 `ClientId` int(10) default NULL,
 PRIMARY KEY  (`NumId`)

) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblDropCountryTops

-- ----------------------------

DROP TABLE IF EXISTS `tblDropCountryTops`;

CREATE TABLE `tblDropCountryTops` (

 `NumId` int(10) unsigned NOT NULL auto_increment,
 `CountryId` int(10) default NULL,
 PRIMARY KEY  (`NumId`)

) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblEmailQueryHistory

-- ----------------------------

DROP TABLE IF EXISTS `tblEmailQueryHistory`;

CREATE TABLE `tblEmailQueryHistory` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentId` int(10) NOT NULL,
 `Date` datetime NOT NULL,
 `Subject` varchar(255) NOT NULL,
 `To` varchar(100) NOT NULL,
 `From` varchar(100) NOT NULL,
 `Body` varchar(255) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=168 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblEmailQueryHistoryCCAddresses

-- ----------------------------

DROP TABLE IF EXISTS `tblEmailQueryHistoryCCAddresses`;

CREATE TABLE `tblEmailQueryHistoryCCAddresses` (

 `Id` int(10) NOT NULL auto_increment,
 `EmailQueryId` int(10) NOT NULL,
 `CCAddress` varchar(100) NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `EmailQueryId` (`EmailQueryId`),
 CONSTRAINT `EmailQueryId_FK` FOREIGN KEY (`EmailQueryId`) REFERENCES `tblEmailQueryHistory` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblEstimatedTransitTime

-- ----------------------------

DROP TABLE IF EXISTS `tblEstimatedTransitTime`;

CREATE TABLE `tblEstimatedTransitTime` (

 `Id` int(10) NOT NULL auto_increment,
 `TransportModeId` int(10) NOT NULL,
 `ClientCompanyId` int(10) NOT NULL,
 `OriginCountryId` int(10) NOT NULL,
 `DestinationCountryId` int(10) NOT NULL,
 `TransitDays` int(10) NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `tblEstimatedTransitTime_ibfk_1` (`TransportModeId`),
 KEY `tblEstimatedTransitTime_ibfk_2` (`OriginCountryId`),
 KEY `tblEstimatedTransitTime_ibfk_3` (`DestinationCountryId`),
 KEY `tblEstimatedTransitTime_ibfk_4` (`ClientCompanyId`),
 CONSTRAINT `tblEstimatedTransitTime_ibfk_1` FOREIGN KEY (`TransportModeId`) REFERENCES `tblTransportMode` (`Id`),
 CONSTRAINT `tblEstimatedTransitTime_ibfk_2` FOREIGN KEY (`OriginCountryId`) REFERENCES `tblCountry` (`Id`),
 CONSTRAINT `tblEstimatedTransitTime_ibfk_3` FOREIGN KEY (`DestinationCountryId`) REFERENCES `tblCountry` (`Id`),
 CONSTRAINT `tblEstimatedTransitTime_ibfk_4` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblExchangeRate

-- ----------------------------

DROP TABLE IF EXISTS `tblExchangeRate`;

CREATE TABLE `tblExchangeRate` (

 `Id` int(10) NOT NULL auto_increment,
 `ExchangeRateNumber` int(10) NOT NULL,
 `ThisCurrencyID` int(10) NOT NULL,
 `OtherCurrencyID` int(10) NOT NULL,
 `ExchangeRate` double NOT NULL,
 `LastUpdated` datetime NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblFDAStatus

-- ----------------------------

DROP TABLE IF EXISTS `tblFDAStatus`;

CREATE TABLE `tblFDAStatus` (

 `Id` int(10) NOT NULL auto_increment,
 `FDAStatusName` varchar(30) NOT NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblGenericShipment

-- ----------------------------

DROP TABLE IF EXISTS `tblGenericShipment`;

CREATE TABLE `tblGenericShipment` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientCompanyId` int(10) NOT NULL,
 `ShipperAddressId` int(10) default NULL,
 `ShipperAddressDescription` varchar(1500) default NULL,
 `ConsigneeAddressId` int(10) default NULL,
 `ConsigneeAddressDescription` varchar(1500) default NULL,
 `NotifyAddressId` int(10) default NULL,
 `NotifyAddressDescription` varchar(1500) default NULL,
 `PickupAddressId` int(10) default NULL,
 `PickupAddressDescription` varchar(1500) default NULL,
 `GeneralDeliveryToAddressId` int(10) default NULL,
 `GeneralDeliveryToAddressDescription` varchar(1500) default NULL,
 `PickupComments` varchar(1500) default NULL,
 `PickupTruckerId` int(10) default NULL,
 `EstimatedPickupDate` date default NULL,
 `ActualPickupDate` date default NULL,
 `OriginPoint` varchar(30) default NULL,
 `DestinationPoint` varchar(30) default NULL,
 `TransportModeId` int(10) default NULL,
 `ClientModeId` int(10) default NULL,
 `ShippingTermId` int(10) default NULL,
 `ShippingTermLocation` varchar(30) default NULL,
 `CommodityDescription` varchar(1500) default NULL,
 `MarksOrNumber` varchar(1500) default NULL,
 `AuthorizedBy` varchar(30) default NULL,
 `AuthorizedDate` date default NULL,
 `EstimatedDeliveryDate` date default NULL,
 `AuthorizationMethodId` int(10) default NULL,
 `ExportRef` varchar(30) default NULL,
 `ImportRef` varchar(30) default NULL,
 `CustomerRef` varchar(30) default NULL,
 `Jaguar3rdRef` varchar(30) default NULL,
 `MasterId` int(10) default NULL,
 `IsDeleted` bit(1) NOT NULL default '\0',
 `LastModifiedUserId` int(10) default NULL,
 `CreatedOnDate` datetime default NULL,
 `IsDimsMode` tinyint(1) NOT NULL default '0',
 `TotalHUInCubicMeters` double default '0',
 `TotalHUInCubicFeets` double default '0',
 `OriginPointCountryId` int(10) default NULL,
 `DestinationPointCountryId` int(10) default NULL,
 `ShipmentGroupId` int(10) default NULL,
 `NotificationDate` date default NULL,
 `EnabledTransShipment` tinyint(1) default '0',
 `EnabledOriginTerminal` tinyint(1) default '0',
 `EnabledDestinationTerminal` tinyint(1) default '0',
 `ShowPickupAddress` tinyint(1) default '0',
 `ShowDeliveryToAddress` tinyint(1) default '0',
 `ShowOriginPointCountry` tinyint(1) default '0',
 `ShowDestinationPointCountry` tinyint(1) default '0',
 `AuthorizedMOT` int(10) default NULL,
 `TableOfChargesId` int(10) default NULL,
 `SalesPersonId` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `GenericShipment_ClientCompanyId` (`ClientCompanyId`),
 KEY `GenericShipment_ShipperAddressId` (`ShipperAddressId`),
 KEY `GenericShipment_ConsigneeAddressId` (`ConsigneeAddressId`),
 KEY `GenericShipment_NotifyAddressId` (`NotifyAddressId`),
 KEY `GenericShipment_PickupAddressId` (`PickupAddressId`),
 KEY `GenericShipment_GeneralDeliveryToAddressId` (`GeneralDeliveryToAddressId`),
 KEY `GenericShipment_PickupTruckerId` (`PickupTruckerId`),
 KEY `GenericShipment_TransportModeId` (`TransportModeId`),
 KEY `GenericShipment_ShippingTermId` (`ShippingTermId`),
 KEY `GenericShipment_AuthorizationMethodId` (`AuthorizationMethodId`),
 KEY `GenericShipment_MasterId` (`MasterId`),
 KEY `GenericShipment_LastModifiedUserId` (`LastModifiedUserId`),
 KEY `tblGenericShipment_ibfk_27` (`ClientModeId`),
 KEY `ShipmentGroupId` (`ShipmentGroupId`),
 KEY `SalesPersonId` (`SalesPersonId`),
 KEY `TableOfChargesId` (`TableOfChargesId`),
 CONSTRAINT `tblGenericShipment_fk` FOREIGN KEY (`TableOfChargesId`) REFERENCES `tblTableOfCharges` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT `tblGenericShipment_ibfk_1` FOREIGN KEY (`ShipmentGroupId`) REFERENCES `tblShipmentGroup` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT `tblGenericShipment_ibfk_10` FOREIGN KEY (`AuthorizationMethodId`) REFERENCES `tblAuthorizationMethod` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_11` FOREIGN KEY (`SalesPersonId`) REFERENCES `tblJaguarUser` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblGenericShipment_ibfk_2` FOREIGN KEY (`ShipperAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_25` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON DELETE SET NULL,
 CONSTRAINT `tblGenericShipment_ibfk_26` FOREIGN KEY (`LastModifiedUserId`) REFERENCES `tblJaguarUser` (`Id`) ON DELETE SET NULL,
 CONSTRAINT `tblGenericShipment_ibfk_27` FOREIGN KEY (`ClientModeId`) REFERENCES `tblClientMode` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_28` FOREIGN KEY (`PickupTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_29` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_3` FOREIGN KEY (`ConsigneeAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_4` FOREIGN KEY (`NotifyAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_5` FOREIGN KEY (`GeneralDeliveryToAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_6` FOREIGN KEY (`PickupAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_8` FOREIGN KEY (`TransportModeId`) REFERENCES `tblTransportMode` (`Id`),
 CONSTRAINT `tblGenericShipment_ibfk_9` FOREIGN KEY (`ShippingTermId`) REFERENCES `tblShippingTerm` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=251003 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblGenericUser

-- ----------------------------

DROP TABLE IF EXISTS `tblGenericUser`;

CREATE TABLE `tblGenericUser` (

 `Id` int(10) NOT NULL auto_increment,
 `Password` varchar(30) NOT NULL,
 `Active` bit(1) NOT NULL,
 `FirstName` varchar(30) NOT NULL,
 `LastName` varchar(30) NOT NULL,
 `Birthday` date default NULL,
 `Email` varchar(50) NOT NULL,
 `PhoneNumber` varchar(15) NOT NULL,
 `RSSFeedId` int(10) default '2',
 `SkypeId` varchar(255) default NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=159 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblGroupObjectPermission

-- ----------------------------

DROP TABLE IF EXISTS `tblGroupObjectPermission`;

CREATE TABLE `tblGroupObjectPermission` (

 `Id` int(10) NOT NULL auto_increment,
 `GroupObjectID` int(10) NOT NULL,
 `CanCreate` int(1) NOT NULL default '0',
 `CanView` int(1) NOT NULL default '0',
 `CanModify` int(1) NOT NULL default '0',
 `CanDelete` int(1) NOT NULL default '0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblHandlingRemark

-- ----------------------------

DROP TABLE IF EXISTS `tblHandlingRemark`;

CREATE TABLE `tblHandlingRemark` (

 `Id` int(10) NOT NULL auto_increment,
 `HandlingRemark` varchar(150) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblHandlingUnit

-- ----------------------------

DROP TABLE IF EXISTS `tblHandlingUnit`;

CREATE TABLE `tblHandlingUnit` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentId` int(10) NOT NULL,
 `Quantity` int(10) NOT NULL,
 `Length` double NOT NULL,
 `Width` double NOT NULL,
 `Height` double NOT NULL,
 `UnitId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `ShipmentId` (`ShipmentId`),
 CONSTRAINT `tblHandlingUnit_ibfk_1` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblJaguarOpsGroup

-- ----------------------------

DROP TABLE IF EXISTS `tblJaguarOpsGroup`;

CREATE TABLE `tblJaguarOpsGroup` (

 `Id` int(10) NOT NULL auto_increment,
 `GroupName` varchar(30) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblJaguarOpsGroupClientRelation

-- ----------------------------

DROP TABLE IF EXISTS `tblJaguarOpsGroupClientRelation`;

CREATE TABLE `tblJaguarOpsGroupClientRelation` (

 `Id` int(10) NOT NULL auto_increment,
 `JaguarOpsGroupId` int(10) NOT NULL,
 `ClientUserId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblJaguarOpsGroupCompanyRelation

-- ----------------------------

DROP TABLE IF EXISTS `tblJaguarOpsGroupCompanyRelation`;

CREATE TABLE `tblJaguarOpsGroupCompanyRelation` (

 `Id` int(10) NOT NULL auto_increment,
 `JaguarOpsGroupId` int(10) NOT NULL,
 `ClientCompanyId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblJaguarUser

-- ----------------------------

DROP TABLE IF EXISTS `tblJaguarUser`;

CREATE TABLE `tblJaguarUser` (

 `Id` int(10) NOT NULL auto_increment,
 `UserId` int(10) NOT NULL,
 `OfficeId` int(10) NOT NULL,
 `TypeId` int(10) NOT NULL,
 `GroupId` int(10) default NULL,
 `IsBCC` tinyint(1) NOT NULL default '0',
 `IsSalesPerson` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `UserId` (`UserId`),
 KEY `OfficeId` (`OfficeId`),
 KEY `TypeId` (`TypeId`),
 KEY `tblJaguarUser_ibfk_4` (`GroupId`),
 CONSTRAINT `tblJaguarUser_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`),
 CONSTRAINT `tblJaguarUser_ibfk_2` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`),
 CONSTRAINT `tblJaguarUser_ibfk_3` FOREIGN KEY (`TypeId`) REFERENCES `tblJaguarUserType` (`Id`),
 CONSTRAINT `tblJaguarUser_ibfk_4` FOREIGN KEY (`GroupId`) REFERENCES `tblJaguarUserGroup` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=124 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblJaguarUserBCCList

-- ----------------------------

DROP TABLE IF EXISTS `tblJaguarUserBCCList`;

CREATE TABLE `tblJaguarUserBCCList` (

 `Id` int(10) NOT NULL auto_increment,
 `JaguarUserId` int(10) NOT NULL,
 `ClientUserAccounting` tinyint(1) default '1',
 `ClientUserCommCenter` tinyint(1) default '1',
 `ShipmentQueries` tinyint(1) default '1',
 `ClientUserShipmentAuthorization` tinyint(1) default '1',
 PRIMARY KEY  (`Id`),
 KEY `JaguarUserId` (`JaguarUserId`),
 CONSTRAINT `tblJaguarUserBCCList_fk` FOREIGN KEY (`JaguarUserId`) REFERENCES `tblJaguarUser` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblJaguarUserGroup

-- ----------------------------

DROP TABLE IF EXISTS `tblJaguarUserGroup`;

CREATE TABLE `tblJaguarUserGroup` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(30) NOT NULL,
 `OfficeId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `OfficeId` (`OfficeId`),
 CONSTRAINT `OfficeId` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblJaguarUserType

-- ----------------------------

DROP TABLE IF EXISTS `tblJaguarUserType`;

CREATE TABLE `tblJaguarUserType` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(30) NOT NULL,
 `Authority` varchar(15) NOT NULL,
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `Name` (`Name`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblLoose

-- ----------------------------

DROP TABLE IF EXISTS `tblLoose`;

CREATE TABLE `tblLoose` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(50) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblMailTemplates

-- ----------------------------

DROP TABLE IF EXISTS `tblMailTemplates`;

CREATE TABLE `tblMailTemplates` (

 `Id` int(10) NOT NULL auto_increment,
 `Header` varchar(100) NOT NULL,
 `Footer` varchar(100) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblMaster

-- ----------------------------

DROP TABLE IF EXISTS `tblMaster`;

CREATE TABLE `tblMaster` (

 `Id` int(10) NOT NULL auto_increment,
 `MasterTypeCode` int(10) NOT NULL,
 `CreateDate` date NOT NULL,
 `ShipperId` int(10) default NULL,
 `ShipperDescription` varchar(100) default NULL,
 `ConsigneeId` int(10) default NULL,
 `ConsigneeDescripion` varchar(100) default NULL,
 `NotifyId` int(10) default NULL,
 `NotifyDescription` varchar(100) default NULL,
 `OriginTruckerId` int(10) default NULL,
 `DestinationTruckerId` int(10) default NULL,
 `ShipperRef` varchar(20) default NULL,
 `ConsigneeRef` varchar(20) default NULL,
 `TransportModeId` int(10) default NULL,
 `PickUpId` int(10) default NULL,
 `PickUpDescription` varchar(100) default NULL,
 `DeliveryAddressId` int(10) default NULL,
 `DeliveryAddressDescription` varchar(100) default NULL,
 `MarksNumbers` varchar(100) default NULL,
 `CommodityDescription` varchar(100) default NULL,
 `PickUpComments` varchar(100) default NULL,
 `DeliveryComments` varchar(100) default NULL,
 `Freight` int(1) default '0',
 `MoveTypeId` int(10) default NULL,
 `SteamshiplineId` int(10) default NULL,
 `CoLoaderOceanId` int(10) default NULL,
 `Booking` varchar(30) default NULL,
 `MasterBillOfLoading` varchar(30) default NULL,
 `AMSSubmissionDate` date default NULL,
 `VesselName` varchar(30) default NULL,
 `Voyage1` varchar(30) default NULL,
 `FeederVesselname` varchar(30) default NULL,
 `Voyage2` varchar(30) default NULL,
 `OriginalTerminalId` int(10) default NULL,
 `OriginalTerminalETD` date default NULL,
 `OriginalTerminalATD` date default NULL,
 `PortOfLoadingId` int(10) default NULL,
 `PortOfLoadingETD` date default NULL,
 `PortOfLoadingATD` date default NULL,
 `PortOfTransShipmentId` int(10) default NULL,
 `PortOfTransShipmentETD` date default NULL,
 `PortOfTransShipmentATD` date default NULL,
 `PortOfDischargeId` int(10) default NULL,
 `PortOfDischargeETD` date default NULL,
 `PortOfDischargeATD` date default NULL,
 `DestinationalTerminalId` int(10) default NULL,
 `DestinationalTerminalETD` date default NULL,
 `DestinationalTerminalATD` date default NULL,
 `DestinationReceivingAgentId` int(10) default NULL,
 `MasterBillComments` varchar(150) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `ShipperId` (`ShipperId`),
 KEY `ConsigneeId` (`ConsigneeId`),
 KEY `NotifyId` (`NotifyId`),
 KEY `OriginTruckerId` (`OriginTruckerId`),
 KEY `DestinationTruckerId` (`DestinationTruckerId`),
 KEY `TransportModeId` (`TransportModeId`),
 KEY `PickUpId` (`PickUpId`),
 KEY `DeliveryAddressId` (`DeliveryAddressId`),
 KEY `MoveTypeId` (`MoveTypeId`),
 KEY `SteamshiplineId` (`SteamshiplineId`),
 KEY `CoLoaderOceanId` (`CoLoaderOceanId`),
 KEY `OriginalTerminalId` (`OriginalTerminalId`),
 KEY `PortOfLoadingId` (`PortOfLoadingId`),
 KEY `PortOfTransShipmentId` (`PortOfTransShipmentId`),
 KEY `PortOfDischargeId` (`PortOfDischargeId`),
 KEY `DestinationalTerminalId` (`DestinationalTerminalId`),
 KEY `DestinationReceivingAgentId` (`DestinationReceivingAgentId`),
 CONSTRAINT `tblMaster_ibfk_1` FOREIGN KEY (`ShipperId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMaster_ibfk_10` FOREIGN KEY (`SteamshiplineId`) REFERENCES `tblSteamshipline` (`Id`),
 CONSTRAINT `tblMaster_ibfk_2` FOREIGN KEY (`ConsigneeId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMaster_ibfk_3` FOREIGN KEY (`NotifyId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMaster_ibfk_4` FOREIGN KEY (`OriginTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblMaster_ibfk_5` FOREIGN KEY (`DestinationTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblMaster_ibfk_6` FOREIGN KEY (`TransportModeId`) REFERENCES `tblMasterTransportMode` (`Id`),
 CONSTRAINT `tblMaster_ibfk_7` FOREIGN KEY (`PickUpId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMaster_ibfk_8` FOREIGN KEY (`DeliveryAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMaster_ibfk_9` FOREIGN KEY (`MoveTypeId`) REFERENCES `tblMoveType` (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblMasterComments

-- ----------------------------

DROP TABLE IF EXISTS `tblMasterComments`;

CREATE TABLE `tblMasterComments` (

 `Id` int(10) NOT NULL auto_increment,
 `MasterId` int(10) NOT NULL,
 `ReplyDateTime` datetime NOT NULL,
 `UserId` int(10) NOT NULL,
 `Body` varchar(150) NOT NULL default ,
 PRIMARY KEY  (`Id`),
 KEY `MasterId` (`MasterId`),
 KEY `UserId` (`UserId`),
 CONSTRAINT `tblMasterComments_ibfk_1` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON DELETE CASCADE,
 CONSTRAINT `tblMasterComments_ibfk_2` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblMasterCommentsMailedTo

-- ----------------------------

DROP TABLE IF EXISTS `tblMasterCommentsMailedTo`;

CREATE TABLE `tblMasterCommentsMailedTo` (

 `CommentId` int(10) NOT NULL,
 `UserId` int(10) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblMasterOcean

-- ----------------------------

DROP TABLE IF EXISTS `tblMasterOcean`;

CREATE TABLE `tblMasterOcean` (

 `Id` int(10) NOT NULL auto_increment,
 `MasterTypeCode` int(10) NOT NULL,
 `CreateDate` datetime NOT NULL,
 `ShipperId` int(10) default NULL,
 `ShipperDescription` varchar(1500) default NULL,
 `ConsigneeId` int(10) default NULL,
 `ConsigneeDescripion` varchar(1500) default NULL,
 `NotifyId` int(10) default NULL,
 `NotifyDescription` varchar(1500) default NULL,
 `OriginTruckerId` int(10) default NULL,
 `DestinationTruckerId` int(10) default NULL,
 `ShipperRef` varchar(20) default NULL,
 `ConsigneeRef` varchar(20) default NULL,
 `TransportModeId` int(10) default NULL,
 `PickUpId` int(10) default NULL,
 `PickUpDescription` varchar(1500) default NULL,
 `DeliveryAddressId` int(10) default NULL,
 `DeliveryAddressDescription` varchar(1500) default NULL,
 `MarksNumbers` varchar(1500) default NULL,
 `CommodityDescription` varchar(1500) default NULL,
 `PickUpComments` varchar(1500) default NULL,
 `DeliveryComments` varchar(1500) default NULL,
 `Freight` int(1) default '0',
 `MoveTypeId` int(10) default NULL,
 `SteamshiplineId` int(10) default NULL,
 `CoLoaderOceanId` int(10) default NULL,
 `Booking` varchar(30) default NULL,
 `MasterBillOfLoading` varchar(30) default NULL,
 `AMSSubmissionDate` date default NULL,
 `VesselName` varchar(30) default NULL,
 `Voyage1` varchar(30) default NULL,
 `FeederVesselname` varchar(30) default NULL,
 `Voyage2` varchar(30) default NULL,
 `OriginalTerminalId` int(10) default NULL,
 `OriginalTerminalETD` date default NULL,
 `OriginalTerminalATD` date default NULL,
 `PortOfLoadingId` int(10) default NULL,
 `PortOfLoadingETD` date default NULL,
 `PortOfLoadingATD` date default NULL,
 `PortOfTransShipmentId` int(10) default NULL,
 `PortOfTransShipmentETD` date default NULL,
 `PortOfTransShipmentATD` date default NULL,
 `PortOfDischargeId` int(10) default NULL,
 `PortOfDischargeETD` date default NULL,
 `PortOfDischargeATD` date default NULL,
 `DestinationalTerminalId` int(10) default NULL,
 `DestinationalTerminalETD` date default NULL,
 `DestinationalTerminalATD` date default NULL,
 `DestinationReceivingAgentId` int(10) default NULL,
 `MasterBillComments` varchar(1500) default NULL,
 `AirlineId` int(10) default NULL,
 `CoLoaderAirId` int(10) default NULL,
 `MasterAirwayBill` varchar(30) default NULL,
 `MasterAirwayBillA` varchar(3) default NULL,
 `MasterAirwayBillB` varchar(4) default NULL,
 `MasterAirwayBillC` varchar(4) default NULL,
 `MasterAirwayBillComment` varchar(1500) default NULL,
 `Flight1` varchar(30) default NULL,
 `Flight2` varchar(30) default NULL,
 `AirportOfDepartureId` int(10) default NULL,
 `AirportOfDepartureETD` date default NULL,
 `AirportOfDepartureATD` date default NULL,
 `TransShipmentAirportId` int(10) default NULL,
 `TransShipmentAirportETA` date default NULL,
 `TransShipmentAirportATA` date default NULL,
 `AirportOfDestinationId` int(10) default NULL,
 `AirportOfDestinationETA` date default NULL,
 `AirportOfDestinationATA` date default NULL,
 PRIMARY KEY  (`Id`),
 KEY `ShipperId` (`ShipperId`),
 KEY `ConsigneeId` (`ConsigneeId`),
 KEY `NotifyId` (`NotifyId`),
 KEY `OriginTruckerId` (`OriginTruckerId`),
 KEY `DestinationTruckerId` (`DestinationTruckerId`),
 KEY `PickUpId` (`PickUpId`),
 KEY `DeliveryAddressId` (`DeliveryAddressId`),
 KEY `TransportModeId` (`TransportModeId`),
 KEY `tblMasterOcean_ibfk_9` (`AirlineId`),
 KEY `tblMasterOcean_ibfk_10` (`CoLoaderAirId`),
 KEY `tblMasterOcean_ibfk_11` (`AirportOfDepartureId`),
 KEY `tblMasterOcean_ibfk_12` (`TransShipmentAirportId`),
 KEY `tblMasterOcean_ibfk_13` (`AirportOfDestinationId`),
 KEY `tblMasterOcean_ibfk_14` (`CoLoaderOceanId`),
 KEY `tblMasterOcean_ibfk_15` (`DestinationReceivingAgentId`),
 KEY `tblMasterOcean_ibfk_16` (`MoveTypeId`),
 KEY `tblMasterOcean_ibfk_17` (`SteamshiplineId`),
 KEY `tblMasterOcean_ibfk_18` (`OriginalTerminalId`),
 KEY `tblMasterOcean_ibfk_19` (`PortOfLoadingId`),
 KEY `tblMasterOcean_ibfk_20` (`PortOfTransShipmentId`),
 KEY `tblMasterOcean_ibfk_21` (`PortOfDischargeId`),
 KEY `tblMasterOcean_ibfk_22` (`DestinationalTerminalId`),
 CONSTRAINT `tblMasterOcean_ibfk_1` FOREIGN KEY (`ShipperId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_10` FOREIGN KEY (`CoLoaderAirId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_11` FOREIGN KEY (`AirportOfDepartureId`) REFERENCES `tblAirport` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_12` FOREIGN KEY (`TransShipmentAirportId`) REFERENCES `tblAirport` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_13` FOREIGN KEY (`AirportOfDestinationId`) REFERENCES `tblAirport` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_14` FOREIGN KEY (`CoLoaderOceanId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_15` FOREIGN KEY (`DestinationReceivingAgentId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_16` FOREIGN KEY (`MoveTypeId`) REFERENCES `tblMoveType` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_17` FOREIGN KEY (`SteamshiplineId`) REFERENCES `tblSteamshipline` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_18` FOREIGN KEY (`OriginalTerminalId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_19` FOREIGN KEY (`PortOfLoadingId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_2` FOREIGN KEY (`ConsigneeId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_20` FOREIGN KEY (`PortOfTransShipmentId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_21` FOREIGN KEY (`PortOfDischargeId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_22` FOREIGN KEY (`DestinationalTerminalId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_3` FOREIGN KEY (`NotifyId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_4` FOREIGN KEY (`OriginTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_5` FOREIGN KEY (`DestinationTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_6` FOREIGN KEY (`PickUpId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_7` FOREIGN KEY (`DeliveryAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_8` FOREIGN KEY (`TransportModeId`) REFERENCES `tblMasterTransportMode` (`Id`),
 CONSTRAINT `tblMasterOcean_ibfk_9` FOREIGN KEY (`AirlineId`) REFERENCES `tblAirline` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=452 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblMasterPDFReports

-- ----------------------------

DROP TABLE IF EXISTS `tblMasterPDFReports`;

CREATE TABLE `tblMasterPDFReports` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(50) NOT NULL,
 `MasterId` int(10) NOT NULL,
 `Path` varchar(150) NOT NULL,
 `IssueDate` date default NULL,
 `IssuedBy` varchar(50) NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `tblMasterPDFReports_ibfk_01` (`MasterId`),
 CONSTRAINT `tblMasterPDFReports_ibfk_01` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblMasterTransportMode

-- ----------------------------

DROP TABLE IF EXISTS `tblMasterTransportMode`;

CREATE TABLE `tblMasterTransportMode` (

 `Id` int(10) NOT NULL auto_increment,
 `TransportModeValue` varchar(30) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblMoveType

-- ----------------------------

DROP TABLE IF EXISTS `tblMoveType`;

CREATE TABLE `tblMoveType` (

 `Id` int(10) NOT NULL auto_increment,
 `Type` varchar(30) NOT NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblOceanModeType

-- ----------------------------

DROP TABLE IF EXISTS `tblOceanModeType`;

CREATE TABLE `tblOceanModeType` (

 `Id` int(10) NOT NULL auto_increment,
 `Type` varchar(20) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblOceanShipment

-- ----------------------------

DROP TABLE IF EXISTS `tblOceanShipment`;

CREATE TABLE `tblOceanShipment` (

 `OceanShipmentId` int(10) NOT NULL auto_increment,
 `GenericShipmentId` int(10) NOT NULL,
 `Hazardous` tinyint(1) NOT NULL default '0',
 `HazardousNote` varchar(1500) default NULL,
 `Freight` int(1) default '0',
 `MoveTypeId` int(10) default NULL,
 `OriginalHouseBill` int(1) default '0',
 `SteamshiplineId` int(10) default NULL,
 `CoLoaderOceanId` int(10) default NULL,
 `Booking` varchar(30) default NULL,
 `MasterBill` varchar(30) default NULL,
 `AMSSubmissionDate` date default NULL,
 `VesselName` varchar(30) default NULL,
 `Voyage1` varchar(30) default NULL,
 `FeederVesselname` varchar(30) default NULL,
 `Voyage2` varchar(30) default NULL,
 `OriginalTerminalId` int(10) default NULL,
 `OriginalTerminalETD` date default NULL,
 `OriginalTerminalATD` date default NULL,
 `PortOfLoadingId` int(10) default NULL,
 `PortOfLoadingETD` date default NULL,
 `PortOfLoadingATD` date default NULL,
 `PortOfTransShipmentId` int(10) default NULL,
 `PortOfTransShipmentETA` date default NULL,
 `PortOfTransShipmentATA` date default NULL,
 `PortOfDischargeId` int(10) default NULL,
 `PortOfDischargeETA` date default NULL,
 `PortOfDischargeATA` date default NULL,
 `DestinationalTerminalId` int(10) default NULL,
 `DestinationalTerminalETA` date default NULL,
 `DestinationalTerminalATA` date default NULL,
 `DestinationReceivingAgentId` int(10) default NULL,
 `HouseBillComments` varchar(1500) default NULL,
 `EnabledArrivalToWH` tinyint(1) default '1',
 `ArrivalToWarehouseDate` date default NULL,
 `ItNumber` varchar(17) default NULL,
 `ItDate` date default NULL,
 `ItLocation` varchar(20) default NULL,
 `CustomsBrokerID` int(10) default NULL,
 `CustomsBrokerDescription` varchar(1500) default NULL,
 `DeliveryAddressId` int(10) default NULL,
 `DeliveryAddressDescription` varchar(1500) default NULL,
 `DeliveryComments` varchar(1500) default NULL,
 `LastFreeDay` date default NULL,
 `CargoLocationId` int(10) default NULL,
 `CargoLocationDescription` varchar(1500) default NULL,
 `DeliveryTruckerId` int(10) default NULL,
 `ImportEstimatedDeliveryDate` date default NULL,
 `ActualDeliveryDate` date default NULL,
 `ArrivalNoticeComments` varchar(1500) default NULL,
 `OriginalBillLandingRequired` int(1) default '0',
 `PaymentFreightChargesRequired` int(1) default '0',
 `CustomsStatusId` int(10) default NULL,
 `CustomsStatusDate` date default NULL,
 `FDAStatusId` int(10) default NULL,
 `FDAStatusDate` date default NULL,
 `HouseBill` varchar(30) default NULL,
 `DeliveryDateTypeId` int(10) default NULL,
 `JaguarDelivery` tinyint(1) default '0',
 `ExportCustoms` varchar(30) default NULL,
 `ActualDeliveryDateEnabled` tinyint(1) default '0',
 PRIMARY KEY  (`OceanShipmentId`),
 KEY `OceanShipment_GenericShipmentId` (`GenericShipmentId`),
 KEY `OceanShipment_MoveTypeId` (`MoveTypeId`),
 KEY `OceanShipment_SteamshiplineId` (`SteamshiplineId`),
 KEY `OceanShipment_CoLoaderOceanId` (`CoLoaderOceanId`),
 KEY `OceanShipment_OriginalTerminalId` (`OriginalTerminalId`),
 KEY `OceanShipment_PortOfLoadingId` (`PortOfLoadingId`),
 KEY `OceanShipment_PortOfTransShipmentId` (`PortOfTransShipmentId`),
 KEY `OceanShipment_PortOfDischargeId` (`PortOfDischargeId`),
 KEY `OceanShipment_DestinationalTerminalId` (`DestinationalTerminalId`),
 KEY `OceanShipment_DestinationReceivingAgentId` (`DestinationReceivingAgentId`),
 KEY `OceanShipment_CustomsBrokerID` (`CustomsBrokerID`),
 KEY `OceanShipment_DeliveryAddressId` (`DeliveryAddressId`),
 KEY `OceanShipment_CargoLocationId` (`CargoLocationId`),
 KEY `OceanShipment_FDAStatusId` (`FDAStatusId`),
 KEY `OceanShipment_CustomsStatusId` (`CustomsStatusId`),
 KEY `tblOceanShipment_ibfk_27` (`DeliveryTruckerId`),
 CONSTRAINT `tblOceanShipment_ibfk_27` FOREIGN KEY (`DeliveryTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblOceanShipment_ibfk_11` FOREIGN KEY (`MoveTypeId`) REFERENCES `tblMoveType` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_12` FOREIGN KEY (`SteamshiplineId`) REFERENCES `tblSteamshipline` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_13` FOREIGN KEY (`CoLoaderOceanId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_14` FOREIGN KEY (`OriginalTerminalId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_15` FOREIGN KEY (`PortOfLoadingId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_16` FOREIGN KEY (`PortOfTransShipmentId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_17` FOREIGN KEY (`PortOfDischargeId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_18` FOREIGN KEY (`DestinationalTerminalId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_19` FOREIGN KEY (`DestinationReceivingAgentId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_20` FOREIGN KEY (`CustomsBrokerID`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_21` FOREIGN KEY (`DeliveryAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_22` FOREIGN KEY (`CargoLocationId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_23` FOREIGN KEY (`FDAStatusId`) REFERENCES `tblFDAStatus` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_24` FOREIGN KEY (`CustomsStatusId`) REFERENCES `tblCustomsStatus` (`Id`),
 CONSTRAINT `tblOceanShipment_ibfk_26` FOREIGN KEY (`GenericShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=705 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblOffice

-- ----------------------------

DROP TABLE IF EXISTS `tblOffice`;

CREATE TABLE `tblOffice` (

 `Id` int(10) NOT NULL auto_increment,
 `City` varchar(30) NOT NULL,
 `Flag` varchar(30) default NULL,
 `Abbreviation` varchar(3) default ,
 `Address` varchar(1000) default NULL,
 `CurrencyId` int(10) default NULL,
 `VAT` double default NULL,
 `ImageLogo` blob,
 `Name` varchar(60) NOT NULL default ,
 `PhoneNumber` varchar(15) default NULL,
 `FaxNumber` varchar(15) default NULL,
 `Email` varchar(50) default NULL,
 `IsMainOffice` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`),
 KEY `CurrencyId` (`CurrencyId`),
 CONSTRAINT `tblOffice_fk` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblOnPallets

-- ----------------------------

DROP TABLE IF EXISTS `tblOnPallets`;

CREATE TABLE `tblOnPallets` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(50) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblPackagingCommodityType

-- ----------------------------

DROP TABLE IF EXISTS `tblPackagingCommodityType`;

CREATE TABLE `tblPackagingCommodityType` (

 `Id` int(10) NOT NULL auto_increment,
 `Description` varchar(30) default NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblPackagingPalletType

-- ----------------------------

DROP TABLE IF EXISTS `tblPackagingPalletType`;

CREATE TABLE `tblPackagingPalletType` (

 `Id` int(10) NOT NULL auto_increment,
 `Description` varchar(30) default NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblPackagingType

-- ----------------------------

DROP TABLE IF EXISTS `tblPackagingType`;

CREATE TABLE `tblPackagingType` (

 `Id` int(10) NOT NULL auto_increment,
 `LooseID` int(10) NOT NULL,
 `OnPalletsID` int(10) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblPDFReports

-- ----------------------------

DROP TABLE IF EXISTS `tblPDFReports`;

CREATE TABLE `tblPDFReports` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(50) NOT NULL,
 `ShipmentId` int(10) NOT NULL,
 `Path` varchar(150) NOT NULL,
 `ShowToClient` tinyint(1) NOT NULL default '0',
 `IssueDate` date default NULL,
 `IssuedBy` varchar(50) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=214 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblPurchaseInvoice

-- ----------------------------

DROP TABLE IF EXISTS `tblPurchaseInvoice`;

CREATE TABLE `tblPurchaseInvoice` (

 `Id` int(10) NOT NULL auto_increment,
 `VendorId` int(10) default NULL,
 `Number` varchar(20) default NULL,
 `CurrencyId` int(10) default NULL,
 `Amount` double default NULL,
 `OfficeId` int(10) default NULL,
 `DateTime` datetime NOT NULL,
 `Note` varchar(1024) default NULL,
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `uniqueOfficeIdVendorIdNumber` (`OfficeId`,`VendorId`,`Number`),
 KEY `VendorId` (`VendorId`),
 KEY `CurrencyId` (`CurrencyId`),
 KEY `OfficeId` (`OfficeId`),
 CONSTRAINT `tblPurchaseInvoices_fk` FOREIGN KEY (`VendorId`) REFERENCES `tblAddressJaguarVendor` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblPurchaseInvoice_fk` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblPurchaseInvoice_fk1` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=224 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblPurchaseInvoiceChargeCode

-- ----------------------------

DROP TABLE IF EXISTS `tblPurchaseInvoiceChargeCode`;

CREATE TABLE `tblPurchaseInvoiceChargeCode` (

 `Id` int(10) NOT NULL auto_increment,
 `PurchaseInvoiceId` int(10) default NULL,
 `ChargeCodeId` int(10) default NULL,
 `Description` varchar(150) default NULL,
 `Amount` double default NULL,
 `ShipmentId` int(10) default NULL,
 `MasterId` int(10) default NULL,
 `ChargeBackAmount` double default NULL,
 `ChargeBackInvoiceId` int(10) default NULL,
 `IsM` tinyint(1) NOT NULL default '0',
 `CBVType` smallint(6) NOT NULL default '0',
 PRIMARY KEY  (`Id`),
 KEY `PurchaseInvoiceId` (`PurchaseInvoiceId`),
 KEY `ChargeCodeId` (`ChargeCodeId`),
 KEY `ChargeBackInvoiceId` (`ChargeBackInvoiceId`),
 KEY `CBVType` (`CBVType`),
 KEY `ShipmentId` (`ShipmentId`),
 KEY `MasterId` (`MasterId`),
 CONSTRAINT `tblPurchaseInvoiceChargeCode_fk` FOREIGN KEY (`PurchaseInvoiceId`) REFERENCES `tblPurchaseInvoice` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblPurchaseInvoiceChargeCode_fk1` FOREIGN KEY (`ChargeCodeId`) REFERENCES `tblChargeCode` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblPurchaseInvoiceChargeCode_fk2` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblPurchaseInvoiceChargeCode_fk3` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblPurchaseInvoiceChargeCode_ibfk_1` FOREIGN KEY (`ChargeBackInvoiceId`) REFERENCES `tblChargeBackInvoice` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=398 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblRegion

-- ----------------------------

DROP TABLE IF EXISTS `tblRegion`;

CREATE TABLE `tblRegion` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(30) NOT NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 `OfficeId` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `OfficeId` (`OfficeId`),
 CONSTRAINT `tblRegion_fk` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=427 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblReport

-- ----------------------------

DROP TABLE IF EXISTS `tblReport`;

CREATE TABLE `tblReport` (

 `Id` int(10) NOT NULL auto_increment,
 `Description` varchar(100) NOT NULL default ,
 `ClientCompanyId` int(10) default NULL,
 `CreatedOnDate` datetime NOT NULL,
 `ModifiedOnDate` datetime default NULL,
 `LastModifiedUserId` int(10) NOT NULL,
 `FormObject` blob,
 `ScheduleObject` blob NOT NULL,
 `LastGenerationDataObject` blob,
 PRIMARY KEY  (`Id`),
 KEY `ClientCompanyId` (`ClientCompanyId`),
 KEY `LastModifiedUserId` (`LastModifiedUserId`),
 CONSTRAINT `tblReport_fk` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblReport_fk1` FOREIGN KEY (`LastModifiedUserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblReportRecipient

-- ----------------------------

DROP TABLE IF EXISTS `tblReportRecipient`;

CREATE TABLE `tblReportRecipient` (

 `Id` int(10) NOT NULL auto_increment,
 `ReportId` int(10) NOT NULL,
 `GenericUserId` int(10) default NULL,
 `Type` int(11) NOT NULL default '0',
 `Email` varchar(50) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `GenericUserId` (`GenericUserId`),
 KEY `ReportId` (`ReportId`),
 CONSTRAINT `tblReportRecipient_fk` FOREIGN KEY (`GenericUserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblReportRecipient_fk1` FOREIGN KEY (`ReportId`) REFERENCES `tblReport` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblRSSNewsFeeds

-- ----------------------------

DROP TABLE IF EXISTS `tblRSSNewsFeeds`;

CREATE TABLE `tblRSSNewsFeeds` (

 `Id` int(11) NOT NULL auto_increment,
 `URL` varchar(255) NOT NULL,
 `Name` varchar(128) default NULL,
 `RegionId` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `RegionId` (`RegionId`),
 CONSTRAINT `tblRSSNewsFeeds_fk` FOREIGN KEY (`RegionId`) REFERENCES `tblRegion` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblSeaPort

-- ----------------------------

DROP TABLE IF EXISTS `tblSeaPort`;

CREATE TABLE `tblSeaPort` (

 `Id` int(10) NOT NULL auto_increment,
 `City` varchar(50) NOT NULL,
 `CountryId` int(10) NOT NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`),
 KEY `CountryId` (`CountryId`),
 CONSTRAINT `tblSeaPort_ibfk_1` FOREIGN KEY (`CountryId`) REFERENCES `tblCountry` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblSecuredUrl

-- ----------------------------

DROP TABLE IF EXISTS `tblSecuredUrl`;

CREATE TABLE `tblSecuredUrl` (

 `Id` int(10) NOT NULL auto_increment,
 `Url` varchar(100) NOT NULL default ,
 `Description` varchar(30) NOT NULL,
 `BasicOpsAccess` bit(1) NOT NULL,
 `SuperOpsAccess` bit(1) NOT NULL,
 `BasicAcc` bit(1) NOT NULL,
 `SuperAcc` bit(1) NOT NULL,
 `Management` bit(1) NOT NULL,
 `Sales` bit(1) NOT NULL,
 `Blank1` bit(1) NOT NULL,
 `Blank2` bit(1) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipline

-- ----------------------------

DROP TABLE IF EXISTS `tblShipline`;

CREATE TABLE `tblShipline` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(50) NOT NULL,
 `SCAC` varchar(4) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipment2ClientCompanyGroupRelation

-- ----------------------------

DROP TABLE IF EXISTS `tblShipment2ClientCompanyGroupRelation`;

CREATE TABLE `tblShipment2ClientCompanyGroupRelation` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientType` int(10) NOT NULL,
 `ShipmentId` int(10) NOT NULL,
 `ClientCompanyGroupId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `ClientType` (`ClientType`,`ShipmentId`),
 KEY `GenericShipmentId_FK` (`ShipmentId`),
 KEY `ClientCompanyGroupId` (`ClientCompanyGroupId`),
 CONSTRAINT `tblShipment2ClientCompanyGroupRelation_fk` FOREIGN KEY (`ClientCompanyGroupId`) REFERENCES `tblClientCompanyGroup` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `GenericShipmentId_FK` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=217 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipment2ClientCompanyRelation

-- ----------------------------

DROP TABLE IF EXISTS `tblShipment2ClientCompanyRelation`;

CREATE TABLE `tblShipment2ClientCompanyRelation` (

 `Id` int(10) NOT NULL auto_increment,
 `ClientType` int(10) NOT NULL,
 `ShipmentId` int(10) NOT NULL,
 `ClientCompanyId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `ClientType` (`ClientType`,`ShipmentId`),
 KEY `GenericShipmentId1_FK` (`ShipmentId`),
 KEY `ClientCompanyId` (`ClientCompanyId`),
 CONSTRAINT `tblShipment2ClientCompanyRelation_fk` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `GenericShipmentId1_FK` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=3261 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipmentChargeCodes

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentChargeCodes`;

CREATE TABLE `tblShipmentChargeCodes` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentId` int(10) NOT NULL,
 `ChargeCodeNumber` int(10) NOT NULL,
 `Description` varchar(150) default NULL,
 `CalculationType` int(10) default NULL,
 `QuantityUnitTypeId` int(10) default NULL,
 `RateUnitTypeId` int(10) default NULL,
 `CurrencyId` int(10) default NULL,
 `Quantity` double default NULL,
 `Rate` double default NULL,
 `Amount` double default NULL,
 `ChargeCodeId` int(10) default NULL,
 `EnableVAT` tinyint(1) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `ShipmentId` (`ShipmentId`),
 KEY `QuantityUnitTypeId` (`QuantityUnitTypeId`),
 KEY `RateUnitTypeId` (`RateUnitTypeId`),
 KEY `CurrencyId` (`CurrencyId`),
 KEY `ChargeCodeId` (`ChargeCodeId`),
 CONSTRAINT `tblShipmentChargeCodes_fk4` FOREIGN KEY (`ChargeCodeId`) REFERENCES `tblChargeCode` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentChargeCodes_fk1` FOREIGN KEY (`QuantityUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentChargeCodes_fk2` FOREIGN KEY (`RateUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentChargeCodes_fk3` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentChargeCodes_fk` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1947 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 6144 kB; (`ShipmentId`) REFER `cybertrax/tblgen';

-- ----------------------------

-- Table structure for tblShipmentComments

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentComments`;

CREATE TABLE `tblShipmentComments` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentId` int(10) NOT NULL,
 `ReplyDateTime` datetime NOT NULL,
 `UserId` int(10) NOT NULL,
 `Body` varchar(1500) NOT NULL default ,
 `Internal` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`),
 KEY `ShipmentId` (`ShipmentId`),
 KEY `UserId` (`UserId`),
 CONSTRAINT `tblShipmentComments_ibfk_1` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE,
 CONSTRAINT `tblShipmentComments_ibfk_2` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=307 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipmentDateHistory

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentDateHistory`;

CREATE TABLE `tblShipmentDateHistory` (

 `Id` int(10) NOT NULL auto_increment,
 `DateTypeId` int(10) NOT NULL,
 `ShipmentId` int(10) NOT NULL,
 `DateValue` datetime NOT NULL,
 `ChangeDate` datetime NOT NULL,
 `OperatorId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `tblShipmentDateHistory_ibfk_1` (`ShipmentId`),
 KEY `tblShipmentDateHistory_ibfk_2` (`OperatorId`),
 CONSTRAINT `tblShipmentDateHistory_ibfk_1` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE,
 CONSTRAINT `tblShipmentDateHistory_ibfk_2` FOREIGN KEY (`OperatorId`) REFERENCES `tblGenericUser` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=3478 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipmentGroup

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentGroup`;

CREATE TABLE `tblShipmentGroup` (

 `Id` int(10) NOT NULL auto_increment,
 `TransportModeId` int(10) NOT NULL,
 `ClientCompanyId` int(10) NOT NULL,
 `MasterId` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `TransportModeId` (`TransportModeId`),
 KEY `ClientCompanyId` (`ClientCompanyId`),
 KEY `tblShipmentGroup_fk2` (`MasterId`),
 CONSTRAINT `tblShipmentGroup_fk` FOREIGN KEY (`TransportModeId`) REFERENCES `tblTransportMode` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentGroup_fk1` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentGroup_fk2` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipmentInvoice2ContainersRelation

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentInvoice2ContainersRelation`;

CREATE TABLE `tblShipmentInvoice2ContainersRelation` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentInvoiceId` int(10) NOT NULL,
 `ContainerId` int(10) NOT NULL,
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `Id` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=388 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipmentInvoiceChargeCodes

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentInvoiceChargeCodes`;

CREATE TABLE `tblShipmentInvoiceChargeCodes` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentInvoiceId` int(10) default NULL,
 `ChargeCodeNumber` int(10) NOT NULL,
 `Description` varchar(150) default NULL,
 `CalculationType` int(10) default NULL,
 `QuantityUnitTypeId` int(10) default NULL,
 `RateUnitTypeId` int(10) default NULL,
 `CurrencyId` int(10) default NULL,
 `Quantity` double default NULL,
 `Rate` double default NULL,
 `Amount` double default NULL,
 `ChargeCodeId` int(10) default NULL,
 `EnableVAT` tinyint(1) default NULL,
 `TableOfChargesId` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `tblShipmentInvoiceChargeCodes_InvoiceId` (`ShipmentInvoiceId`),
 KEY `QuantityUnitTypeId` (`QuantityUnitTypeId`),
 KEY `CurrencyId` (`CurrencyId`),
 KEY `ChargeCodeId` (`ChargeCodeId`),
 KEY `TableOfChargesId` (`TableOfChargesId`),
 CONSTRAINT `tblShipmentInvoiceChargeCodes_fk3` FOREIGN KEY (`TableOfChargesId`) REFERENCES `tblTableOfCharges` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoiceChargeCodes_fk` FOREIGN KEY (`QuantityUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoiceChargeCodes_fk1` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoiceChargeCodes_fk2` FOREIGN KEY (`ChargeCodeId`) REFERENCES `tblChargeCode` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoiceChargeCodes_InvoiceId` FOREIGN KEY (`ShipmentInvoiceId`) REFERENCES `tblShipmentInvoices` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=2533 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 7168 kB; (`ShipmentInvoiceId`) REFER `cybertrax';

-- ----------------------------

-- Table structure for tblShipmentInvoiceContainers

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentInvoiceContainers`;

CREATE TABLE `tblShipmentInvoiceContainers` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentInvoiceChargeCodeId` int(10) default NULL,
 `SizeId` int(10) default NULL,
 `TypeId` int(10) default NULL,
 `Count` int(10) default NULL,
 `Rate` double default NULL,
 `Amount` double default NULL,
 `CurrencyId` int(10) default NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipmentInvoices

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentInvoices`;

CREATE TABLE `tblShipmentInvoices` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentId` int(10) NOT NULL,
 `IssueDate` datetime NOT NULL,
 `UserId` int(10) NOT NULL,
 `Amount` double NOT NULL default '0',
 `BillToCompany` varchar(255) default NULL,
 `BillToAddress` varchar(512) default NULL,
 `CurrencyId` int(10) NOT NULL,
 `OfficeId` int(10) NOT NULL,
 `DateOfDeparture` date default NULL,
 `DateOfArrival` date default NULL,
 `PortOfDeparture` varchar(128) default NULL,
 `PortOfArrival` varchar(128) default NULL,
 `Vesselname` varchar(128) default NULL,
 `Shipper` varchar(512) default NULL,
 `Consignee` varchar(512) default NULL,
 `NotifyParty` varchar(512) default NULL,
 `FileRef` varchar(512) default NULL,
 `InvoiceType` int(10) NOT NULL default '0',
 `CreditNoteOnInvoiceId` int(10) default NULL,
 `VoyageNumber` varchar(20) default NULL,
 `TotalOfPallets` int(10) default NULL,
 `TotalPkgsOnPlts` int(10) default NULL,
 `TotalLoosePkgsOnPlts` int(10) default NULL,
 `BillToOfficeId` int(10) default NULL,
 `BillToCompanyId` int(10) default NULL,
 `WeightInKg` double default NULL,
 `VolumeInCbm` double default NULL,
 `Comments` varchar(1024) default NULL,
 `PONumbers` varchar(1024) default NULL,
 `CTRef` varchar(512) default NULL,
 `VATAmount` double default NULL,
 `TotalWithVAT` double default NULL,
 `TotalNonVAT` double default NULL,
 `CustomerRef` varchar(512) default NULL,
 `ChargeableWeight` double default NULL,
 `TableOfChargesId` int(10) default NULL,
 `ShowToClient` tinyint(1) NOT NULL default '0',
 `InvoiceNumber` int(10) NOT NULL default '0',
 `MainOfficeId` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `ShipmentId` (`ShipmentId`),
 KEY `InvoiceType` (`InvoiceType`),
 KEY `UserId` (`UserId`),
 KEY `OfficeId` (`OfficeId`),
 KEY `CurrencyId` (`CurrencyId`),
 KEY `BillToOfficeId` (`BillToOfficeId`),
 KEY `BillToCompanyId` (`BillToCompanyId`),
 KEY `CreditNoteOnInvoiceId` (`CreditNoteOnInvoiceId`),
 KEY `TableOfChargesId` (`TableOfChargesId`),
 KEY `InvoiceNumber` (`InvoiceNumber`),
 KEY `MainOfficeId` (`MainOfficeId`),
 CONSTRAINT `tblShipmentInvoices_fk8` FOREIGN KEY (`MainOfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoices_fk` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoices_fk1` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoices_fk2` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoices_fk3` FOREIGN KEY (`BillToOfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoices_fk4` FOREIGN KEY (`BillToCompanyId`) REFERENCES `tblAddressBillToParty` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoices_fk5` FOREIGN KEY (`UserId`) REFERENCES `tblJaguarUser` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoices_fk6` FOREIGN KEY (`CreditNoteOnInvoiceId`) REFERENCES `tblShipmentInvoices` (`Id`) ON UPDATE CASCADE,
 CONSTRAINT `tblShipmentInvoices_fk7` FOREIGN KEY (`TableOfChargesId`) REFERENCES `tblTableOfCharges` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=427 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 7168 kB';

-- ----------------------------

-- Table structure for tblShipmentLog

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentLog`;

CREATE TABLE `tblShipmentLog` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentId` int(10) NOT NULL,
 `UserId` int(10) NOT NULL,
 `FieldName` varchar(500) NOT NULL default ,
 `OldValue` varchar(1500) default NULL,
 `Newvalue` varchar(1500) default NULL,
 `ChangeTime` datetime NOT NULL,
 PRIMARY KEY  (`Id`),
 KEY `ShipmentId` (`ShipmentId`),
 KEY `UserId` (`UserId`),
 CONSTRAINT `tblShipmentLog_ibfk_1` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE,
 CONSTRAINT `tblShipmentLog_ibfk_2` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=11617 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShipmentTemplate

-- ----------------------------

DROP TABLE IF EXISTS `tblShipmentTemplate`;

CREATE TABLE `tblShipmentTemplate` (

 `Id` int(10) NOT NULL auto_increment,
 `TemplateTypeCode` int(10) NOT NULL,
 `OfficeId` int(10) NOT NULL,
 `ShipmentId` int(10) NOT NULL,
 `TransportModeId` int(10) NOT NULL,
 `ClientCompanyId` int(10) NOT NULL,
 `ShipperAddressId` int(10) default NULL,
 `ShipperAddressDescription` varchar(1500) default NULL,
 `ConsigneeAddressId` int(10) default NULL,
 `ConsigneeAddressDescription` varchar(1500) default NULL,
 `NotifyAddressId` int(10) default NULL,
 `NotifyAddressDescription` varchar(1500) default NULL,
 `PickupAddressId` int(10) default NULL,
 `PickupAddressDescription` varchar(1500) default NULL,
 `PickupTruckerId` int(10) default NULL,
 `OriginPointCountryId` int(10) default NULL,
 `OriginPoint` varchar(30) default NULL,
 `DestinationPointCountryId` int(10) default NULL,
 `DestinationPoint` varchar(30) default NULL,
 `CommodityDescription` varchar(1500) default NULL,
 `Comment` varchar(1500) default NULL,
 `CustomsBrokerId` int(10) default NULL,
 `OriginalTerminalId` int(10) default NULL,
 `PortOfLoadingId` int(10) default NULL,
 `PortOfTransShipmentId` int(10) default NULL,
 `PortOfDischargeId` int(10) default NULL,
 `DestinationalTerminalId` int(10) default NULL,
 `DestinationReceivingAgentId` int(10) default NULL,
 `AirportOfDepartureId` int(10) default NULL,
 `TransShipmentAirportId` int(10) default NULL,
 `AirportOfDestinationId` int(10) default NULL,
 `AdditionalClient1Id` int(10) default NULL,
 `AdditionalClient1IsCompany` tinyint(1) default NULL,
 `AdditionalClient2Id` int(10) default NULL,
 `AdditionalClient2IsCompany` tinyint(1) default NULL,
 `AdditionalClient3Id` int(10) default NULL,
 `AdditionalClient3IsCompany` tinyint(1) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `tblOceanShipmentTemplate_ibfk_1` (`TransportModeId`),
 KEY `tblOceanShipmentTemplate_ibfk_2` (`ClientCompanyId`),
 KEY `tblOceanShipmentTemplate_ibfk_3` (`ShipperAddressId`),
 KEY `tblOceanShipmentTemplate_ibfk_4` (`ConsigneeAddressId`),
 KEY `tblOceanShipmentTemplate_ibfk_5` (`NotifyAddressId`),
 KEY `tblOceanShipmentTemplate_ibfk_6` (`PickupAddressId`),
 KEY `tblOceanShipmentTemplate_ibfk_7` (`OriginalTerminalId`),
 KEY `tblOceanShipmentTemplate_ibfk_8` (`PortOfLoadingId`),
 KEY `tblOceanShipmentTemplate_ibfk_9` (`PortOfTransShipmentId`),
 KEY `tblOceanShipmentTemplate_ibfk_10` (`PortOfDischargeId`),
 KEY `tblOceanShipmentTemplate_ibfk_11` (`DestinationalTerminalId`),
 KEY `tblOceanShipmentTemplate_ibfk_12` (`DestinationReceivingAgentId`),
 KEY `tblOceanShipmentTemplate_ibfk_13` (`PickupTruckerId`),
 KEY `tblOceanShipmentTemplate_ibfk_14` (`CustomsBrokerId`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_1` FOREIGN KEY (`TransportModeId`) REFERENCES `tblTransportMode` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_10` FOREIGN KEY (`PortOfDischargeId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_11` FOREIGN KEY (`DestinationalTerminalId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_12` FOREIGN KEY (`DestinationReceivingAgentId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_13` FOREIGN KEY (`PickupTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_14` FOREIGN KEY (`CustomsBrokerId`) REFERENCES `tblAddressJaguarVendor` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_2` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_3` FOREIGN KEY (`ShipperAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_4` FOREIGN KEY (`ConsigneeAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_5` FOREIGN KEY (`NotifyAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_6` FOREIGN KEY (`PickupAddressId`) REFERENCES `tblAddressTransportation` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_7` FOREIGN KEY (`OriginalTerminalId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_8` FOREIGN KEY (`PortOfLoadingId`) REFERENCES `tblSeaPort` (`Id`),
 CONSTRAINT `tblOceanShipmentTemplate_ibfk_9` FOREIGN KEY (`PortOfTransShipmentId`) REFERENCES `tblSeaPort` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblShippingTerm

-- ----------------------------

DROP TABLE IF EXISTS `tblShippingTerm`;

CREATE TABLE `tblShippingTerm` (

 `Id` int(10) NOT NULL auto_increment,
 `TransportModeId` int(11) NOT NULL,
 `Term` varchar(20) default NULL,
 `IsArchived` bit(1) NOT NULL default '\0',
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `Term` (`Term`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblStandardComments

-- ----------------------------

DROP TABLE IF EXISTS `tblStandardComments`;

CREATE TABLE `tblStandardComments` (

 `Id` int(10) NOT NULL auto_increment,
 `Header` varchar(40) NOT NULL,
 `Body` varchar(200) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblSteamshipline

-- ----------------------------

DROP TABLE IF EXISTS `tblSteamshipline`;

CREATE TABLE `tblSteamshipline` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(30) NOT NULL,
 `SCACCode` varchar(4) NOT NULL,
 `TrackingCarrierCode` varchar(30) default NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblTableOfCharges

-- ----------------------------

DROP TABLE IF EXISTS `tblTableOfCharges`;

CREATE TABLE `tblTableOfCharges` (

 `Id` int(10) NOT NULL auto_increment,
 `Amount` double default NULL,
 `AmountVAT` double default NULL,
 `TotalWithVAT` double default NULL,
 `TotalNonVAT` double default NULL,
 `CurrencyId` int(10) default NULL,
 PRIMARY KEY  (`Id`),
 KEY `CurrencyId` (`CurrencyId`),
 CONSTRAINT `tblTableOfCharges_fk` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1363 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblTestimonials

-- ----------------------------

DROP TABLE IF EXISTS `tblTestimonials`;

CREATE TABLE `tblTestimonials` (

 `Id` int(11) NOT NULL auto_increment,
 `Text` varchar(2048) default NULL,
 `Name` varchar(128) default NULL,
 `Company` varchar(128) default NULL,
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `Id` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblTransportMode

-- ----------------------------

DROP TABLE IF EXISTS `tblTransportMode`;

CREATE TABLE `tblTransportMode` (

 `Id` int(10) NOT NULL auto_increment,
 `Mode` varchar(50) NOT NULL,
 PRIMARY KEY  (`Id`),
 UNIQUE KEY `Mode` (`Mode`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblTruckingShipment

-- ----------------------------

DROP TABLE IF EXISTS `tblTruckingShipment`;

CREATE TABLE `tblTruckingShipment` (

 `TruckingShipmentId` int(10) NOT NULL auto_increment,
 `GenericShipmentId` int(10) NOT NULL,
 `EstimatedDeliveryDate` date default NULL,
 `ActualDeliveryDate` date default NULL,
 `LastFreeDay` date default NULL,
 `HouseAirwayBill` varchar(30) default NULL,
 `AirlineId` int(10) default NULL,
 `MasterBillOfLoading` varchar(30) default NULL,
 `HouseBillOfLoading` varchar(30) default NULL,
 `SteamshiplineId` int(10) default NULL,
 `MasterBillA` varchar(3) NOT NULL default ,
 `MasterBillB` varchar(4) NOT NULL default ,
 `MasterBillC` varchar(4) NOT NULL default ,
 PRIMARY KEY  (`TruckingShipmentId`),
 KEY `TruckingShipment_GenericShipmentId` (`GenericShipmentId`),
 KEY `TruckingShipment_AirlineId` (`AirlineId`),
 KEY `TruckingShipment_SteamshiplineId` (`SteamshiplineId`),
 CONSTRAINT `tblTruckingShipment_ibfk_1` FOREIGN KEY (`GenericShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE,
 CONSTRAINT `tblTruckingShipment_ibfk_2` FOREIGN KEY (`AirlineId`) REFERENCES `tblAirline` (`Id`),
 CONSTRAINT `tblTruckingShipment_ibfk_3` FOREIGN KEY (`SteamshiplineId`) REFERENCES `tblSteamshipline` (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=198 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblUndefinedShipment

-- ----------------------------

DROP TABLE IF EXISTS `tblUndefinedShipment`;

CREATE TABLE `tblUndefinedShipment` (

 `UndefinedShipmentId` int(10) NOT NULL auto_increment,
 `GenericShipmentId` int(10) NOT NULL,
 PRIMARY KEY  (`UndefinedShipmentId`),
 KEY `UndefinedShipment_GenericShipmentId` (`GenericShipmentId`),
 CONSTRAINT `tblUndefinedShipment_ibfk_11` FOREIGN KEY (`GenericShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=997 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblUnitType

-- ----------------------------

DROP TABLE IF EXISTS `tblUnitType`;

CREATE TABLE `tblUnitType` (

 `Id` int(10) NOT NULL auto_increment,
 `Description` varchar(25) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblUpdateLogRecord

-- ----------------------------

DROP TABLE IF EXISTS `tblUpdateLogRecord`;

CREATE TABLE `tblUpdateLogRecord` (

 `Id` int(10) NOT NULL auto_increment,
 `UserID` int(10) NOT NULL,
 `UpdateTime` datetime NOT NULL,
 `GroupObjectID` int(10) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblUploadedFiles

-- ----------------------------

DROP TABLE IF EXISTS `tblUploadedFiles`;

CREATE TABLE `tblUploadedFiles` (

 `Id` int(10) NOT NULL auto_increment,
 `ShipmentId` int(10) NOT NULL,
 `FileName` varchar(150) NOT NULL default ,
 `Summary` varchar(150) default NULL,
 `ShowToClient` tinyint(1) NOT NULL default '0',
 `UploadDate` datetime default NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblUSAstate

-- ----------------------------

DROP TABLE IF EXISTS `tblUSAstate`;

CREATE TABLE `tblUSAstate` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(40) NOT NULL,
 `Abbr` varchar(2) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblUserGroup

-- ----------------------------

DROP TABLE IF EXISTS `tblUserGroup`;

CREATE TABLE `tblUserGroup` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(30) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblUserGroupObject

-- ----------------------------

DROP TABLE IF EXISTS `tblUserGroupObject`;

CREATE TABLE `tblUserGroupObject` (

 `Id` int(10) NOT NULL auto_increment,
 `UserGroupID` int(10) NOT NULL,
 `Name` varchar(30) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Table structure for tblUserRole

-- ----------------------------

DROP TABLE IF EXISTS `tblUserRole`;

CREATE TABLE `tblUserRole` (

 `Id` int(10) NOT NULL auto_increment,
 `Name` varchar(30) NOT NULL,
 `Description` varchar(150) NOT NULL,
 PRIMARY KEY  (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Trigger structure for tblCurrency_after_ins_tr

-- ----------------------------

DELIMITER ;;

CREATE TRIGGER `tblCurrency_after_ins_tr` AFTER INSERT ON `tblCurrency` FOR EACH ROW BEGIN

   INSERT INTO `tblCurrencyExchangeRate` (FromCurrencyId, ToCurrencyId, OfficeId)
   SELECT NEW.Id, `tblCurrency`.Id, `tblOffice`.Id
       FROM tblCurrency, tblOffice
       WHERE NOT `tblCurrency`.Id=NEW.Id;
   INSERT INTO `tblCurrencyExchangeRate` (FromCurrencyId, ToCurrencyId, OfficeId)
   SELECT `tblCurrency`.Id, NEW.Id, `tblOffice`.Id
       FROM tblCurrency, tblOffice
       WHERE NOT `tblCurrency`.Id=NEW.Id;

END;;

DELIMITER ;

-- ----------------------------

-- Trigger structure for tblOffice_after_ins_tr

-- ----------------------------

DELIMITER ;;

CREATE TRIGGER `tblOffice_after_ins_tr` AFTER INSERT ON `tblOffice` FOR EACH ROW BEGIN INSERT INTO `tblCurrencyExchangeRate` (FromCurrencyId, ToCurrencyId, OfficeId)

   SELECT c1.Id, c2.Id, NEW.Id
       FROM tblCurrency c1, tblCurrency c2
       WHERE NOT c1.Id=c2.Id;

END;;

DELIMITER ;

-- ----------------------------

-- Trigger structure for tblShipmentInvoices_after_del_tr

-- ----------------------------

DELIMITER ;;

CREATE TRIGGER `tblShipmentInvoices_after_del_tr` AFTER DELETE ON `tblShipmentInvoices` FOR EACH ROW BEGIN

delete from `tblTableOfCharges` where `tblTableOfCharges`.Id = OLD.`TableOfChargesId`;

END;;

DELIMITER ;

</pre>