Db

From UG

Revision as of 20:28, 18 December 2009 by Alex (Talk | contribs)
(diff) ← Older revision | Current revision (diff) | Newer revision → (diff)
Jump to: navigation, search



/*

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;

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

-- Table structure for 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!';



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

-- Table structure for 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';



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

-- Table structure for 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;



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

-- Table structure for 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;



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

-- Table structure for 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;



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

-- Table structure for 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 ;



Personal tools