204 ER
From UG
Contents |
Intro
Data
Defines ER (DB Model) changes related to project.
SCAC
New field
Need to add SCAC code as a property of a Trucking company in CT2.
Format: AN, min/max=2/4
Currently Trucking Company is defined through Address book, Vendors, Trucking Companies(V3)
Add SCAC field to DB:
- varchar(4)
- to tblAddressJaguarVendor
- not required for all recs
- ALTER TABLE `tblAddressJaguarVendor` ADD `SCAC` varchar(4) NULL
The Standard Carrier Alpha Code (SCAC) is a unique code used to identify transportation companies.
http://en.wikipedia.org/wiki/Standard_Carrier_Alpha_Code
SF Company
This is ship from "Company Name" for specific LD number.
select ...
SF City
This is ship from "City" for specific LD number.
select ....
SF Address
This is ship from "Address" for specific LD number. Pull and concatenate Address 1, Address 2, Address 3
SF State
SF Zip
SF Date
Estimated Pick Up date for specific LD number.
tblLoad refactoring
It would be nice to add fields to this table that come with Load Plan so we do not have to look at related CT tables. Such as:
- PU location
- Deliv location
- etc
DB
mysql> describe tblLoad; +-----------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+----------------+ | Id | int(10) | NO | PRI | NULL | auto_increment | | LoadNumber | char(15) | NO | UNI | NULL | | | DateOfReceipt | date | NO | | NULL | | | TotalCharge | double(15,3) | YES | | NULL | | | TotalWeight | double(15,3) | YES | | NULL | | | BaseRate | double(15,3) | YES | | NULL | | | OtherCharges | double(15,3) | YES | | NULL | | | CarrierId | int(10) | YES | MUL | NULL | | | FreightTrendset | double | NO | | 0 | | | FuelTrendset | double | NO | | 0 | | | TransmittedToTrendset | bit(1) | NO | | b'0' | | | FTL | bit(1) | NO | | b'0' | | +-----------------------+--------------+------+-----+---------+----------------+ mysql> describe tblTruckingShipment; +-----------------------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------------+---------------+------+-----+---------+----------------+ | TruckingShipmentId | int(10) | NO | PRI | NULL | auto_increment | | GenericShipmentId | int(10) | NO | MUL | NULL | | | EstimatedDeliveryDate | date | YES | | NULL | | | ActualDeliveryDate | date | YES | | NULL | | | CargoAvailableDate | date | YES | | NULL | | | CargoDueDate | date | YES | | NULL | | | LastFreeDay | date | YES | | NULL | | | MasterAirwayBill | varchar(30) | YES | | NULL | | | HouseAirwayBill | varchar(30) | YES | | NULL | | | AirlineId | int(10) | YES | MUL | NULL | | | MasterBillOfLoading | varchar(30) | YES | | NULL | | | HouseBillOfLoading | varchar(30) | YES | | NULL | | | SteamshiplineId | int(10) | YES | MUL | NULL | | | MasterBillA | varchar(3) | NO | | | | | MasterBillB | varchar(4) | NO | | | | | MasterBillC | varchar(4) | NO | | | | | PickupTrailerId | int(10) | YES | MUL | NULL | | | DeliveryTrailerId | int(10) | YES | MUL | NULL | | | DeliveryTruckerId | int(10) | YES | MUL | NULL | | | Hazardous | tinyint(1) | NO | | 0 | | | HazardousNote | varchar(1500) | YES | | NULL | | | IsCargoDueDateEditedBySuperPlaner | tinyint(1) | NO | | 0 | | +-----------------------------------+---------------+------+-----+---------+----------------+ 22 rows in set (0.00 sec) mysql> describe tblGenericShipment; +-------------------------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------------+---------------+------+-----+---------+----------------+ | Id | int(10) | NO | PRI | NULL | auto_increment | | ClientCompanyId | int(10) | NO | MUL | NULL | | | ShipperAddressId | int(10) | YES | MUL | NULL | | | ShipperAddressDescription | varchar(1500) | YES | | NULL | | | ConsigneeAddressId | int(10) | YES | MUL | NULL | | | ConsigneeAddressDescription | varchar(1500) | YES | | NULL | | | NotifyAddressId | int(10) | YES | MUL | NULL | | | NotifyAddressDescription | varchar(1500) | YES | | NULL | | | PickupAddressId | int(10) | YES | MUL | NULL | | | PickupAddressDescription | varchar(1500) | YES | | NULL | | | GeneralDeliveryToAddressId | int(10) | YES | MUL | NULL | | | GeneralDeliveryToAddressDescription | varchar(1500) | YES | | NULL | | | PickupComments | varchar(1500) | YES | | NULL | | | PickupTruckerId | int(10) | YES | MUL | NULL | | | PickupTruckerDate | date | YES | | NULL | | | EstimatedPickupDate | date | YES | | NULL | | | ActualPickupDate | date | YES | | NULL | | | OriginPoint | varchar(30) | YES | | NULL | | | DestinationPoint | varchar(30) | YES | | NULL | | | TransportModeId | int(10) | YES | MUL | NULL | | | ClientModeId | int(10) | YES | MUL | NULL | | | ShippingTermId | int(10) | YES | MUL | NULL | | | ShippingTermLocation | varchar(30) | YES | | NULL | | | CommodityDescription | varchar(1500) | YES | | NULL | | | MarksOrNumber | varchar(1500) | YES | | NULL | | | AuthorizedBy | varchar(30) | YES | | NULL | | | AuthorizedDate | date | YES | | NULL | | | EstimatedDeliveryDate | date | YES | | NULL | | | AuthorizationMethodId | int(10) | YES | MUL | NULL | | | ExportRef | varchar(30) | YES | MUL | NULL | | | ExportRefId | int(10) | YES | MUL | NULL | | | ImportRef | varchar(30) | YES | MUL | NULL | | | ImportRefId | int(10) | YES | MUL | NULL | | | CustomerRef | varchar(30) | YES | MUL | NULL | | | Jaguar3rdRef | varchar(30) | YES | MUL | NULL | | | JaguarRefId | int(10) | YES | MUL | NULL | | | MasterId | int(10) | YES | MUL | NULL | | | IsDeleted | bit(1) | NO | MUL | b'0' | | | LastModifiedUserId | int(10) | YES | MUL | NULL | | | CreatedOnDate | datetime | YES | MUL | NULL | | | IsDimsMode | tinyint(1) | NO | | 0 | | | TotalHUInCubicMeters | double | YES | | 0 | | | TotalHUInCubicFeets | double | YES | | 0 | | | OriginPointCountryId | int(10) | YES | MUL | NULL | | | DestinationPointCountryId | int(10) | YES | MUL | NULL | | | ShipmentGroupId | int(10) | YES | MUL | NULL | | | NotificationDate | date | YES | | NULL | | | EnabledTransShipment | tinyint(1) | YES | | 0 | | | EnabledOriginTerminal | tinyint(1) | YES | | 0 | | | EnabledDestinationTerminal | tinyint(1) | YES | | 0 | | | EnabledPickupAddress | tinyint(1) | YES | | 0 | | | EnabledGeneralDeliveryToAddress | tinyint(1) | YES | | 0 | | | ShowPickupAddress | tinyint(1) | YES | | 0 | | | ShowDeliveryToAddress | tinyint(1) | YES | | 0 | | | ShowOriginPointCountry | tinyint(1) | YES | | 0 | | | ShowDestinationPointCountry | tinyint(1) | YES | | 0 | | | AuthorizedMOT | int(10) | YES | | NULL | | | TableOfChargesId | int(10) | YES | MUL | NULL | | | SalesPersonId | int(10) | YES | MUL | NULL | | | CreatedByOfficeId | int(10) | NO | MUL | 1 | | | IsOnCustomerHold | bit(1) | NO | | b'0' | | | IsPendingApprovalMode | bit(1) | NO | | b'0' | | | ConsolWarehouse | int(10) | NO | | 0 | | | POIssuedById | int(10) | YES | MUL | NULL | | | CreatedByShipperClientUserId | int(10) | YES | MUL | NULL | | | AuthorizationStatusHistoryId | int(10) | YES | MUL | NULL | | | SkuPlannerId | int(10) | YES | MUL | NULL | | | IsApprovedByPlanner | bit(1) | YES | | NULL | | | TmsStatus | char(20) | YES | MUL | yet | | | AuthorizedMOTComment | varchar(500) | YES | | NULL | | | EstimatedCost | double | YES | | NULL | | | EstimatedSale | double | YES | | NULL | | | EstimatedProfitLoss | double | YES | | NULL | | | EstimatedCurrencyId | int(10) | YES | MUL | NULL | | | Cancelled | bit(1) | NO | | b'0' | | | LoadId | int(10) | YES | MUL | NULL | | | ProNumber | varchar(25) | YES | | NULL | | | TmsUpdatesPending | bit(1) | NO | | b'0' | | | IsLossAuthorized | bit(1) | NO | MUL | b'0' | | | AuthorizationStatusId | int(10) | YES | MUL | NULL | | | AuthorizationApprovedForDate | datetime | YES | MUL | NULL | | | AuthorizationPlannerType | int(10) | YES | MUL | NULL | | | AuthorizedByPlannerId | int(10) | YES | MUL | NULL | | | GmsStatus | int(10) | NO | MUL | 0 | | | GMSRuleCase | int(10) | YES | MUL | NULL | | | OCStatus | int(10) | NO | MUL | 0 | | | GMSDescription | varchar(128) | YES | | NULL | | | OcStatusManuallyChangeDate | date | YES | | NULL | | | OcStatusManuallyChangeUserId | int(10) | YES | MUL | NULL | | | LastClosedDate | datetime | YES | | NULL | | +-------------------------------------+---------------+------+-----+---------+----------------+ 90 rows in set (0.01 sec) mysql> describe tblAddressJaguarVendor; +-----------------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------+--------------+------+-----+---------+----------------+ | Id | int(10) | NO | PRI | NULL | auto_increment | | CompanyCityId | int(10) | NO | MUL | NULL | | | Attn | varchar(25) | NO | | NULL | | | AddressLine1 | varchar(100) | YES | | NULL | | | AddressLine2 | varchar(100) | YES | | NULL | | | AddressLine3 | varchar(100) | YES | | NULL | | | Street | varchar(30) | NO | | NULL | | | Building | varchar(5) | NO | | NULL | | | Postcode | varchar(10) | NO | | NULL | | | ContactPerson | varchar(90) | NO | | NULL | | | Remark | varchar(40) | NO | | NULL | | | IsAirline | bit(1) | NO | | b'0' | | | IsSteamshipline | bit(1) | NO | | b'0' | | | IsTruckingCompany | bit(1) | NO | | b'0' | | | IsCustomsBroker | bit(1) | NO | | b'0' | | | IsCoLoaderOcean | bit(1) | NO | | b'0' | | | IsCoLoaderAir | bit(1) | NO | | b'0' | | | IsCargoLocation | bit(1) | NO | | b'0' | | | IsConsolidationWarehouse | bit(1) | NO | | b'0' | | | IsCustomsInspectionFacility | bit(1) | NO | | b'0' | | | IsJaguarOffice | bit(1) | NO | | b'0' | | | IsOverseasAgent | bit(1) | NO | | b'0' | | | CountryId | int(10) | YES | MUL | NULL | | | USstateId | int(10) | YES | MUL | NULL | | | NonUS_state | varchar(40) | YES | | NULL | | | PhoneCountryCode | varchar(4) | NO | | NULL | | | PhoneCityCode | varchar(4) | NO | | NULL | | | PhoneNumber | varchar(8) | NO | | NULL | | | PhoneExt | varchar(4) | NO | | NULL | | | FaxCountryCode | varchar(4) | NO | | NULL | | | FaxCityCode | varchar(4) | NO | | NULL | | | FaxNumber | varchar(8) | NO | | NULL | | | FaxExt | varchar(4) | NO | | NULL | | | IsArchived | bit(1) | YES | | NULL | | | SendToTrendset | bit(1) | NO | | b'0' | | +-----------------------------+--------------+------+-----+---------+----------------+ 35 rows in set (0.01 sec) mysql> describe tblAddressCompanyCity; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | Id | int(10) | NO | PRI | NULL | auto_increment | | CompanyName | varchar(70) | NO | MUL | | | | CityName | varchar(30) | NO | MUL | NULL | | | IsArchived | bit(1) | NO | | b'0' | | +-------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
