Db
From UG
/*
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 ;
