204 ER
From UG
(Difference between revisions)
(→Changes) |
(→Changes) |
||
| Line 3: | Line 3: | ||
== Intro == | == Intro == | ||
| - | == | + | == Data == |
Defines ER (DB Model) changes related to project. | Defines ER (DB Model) changes related to project. | ||
=== SCAC === | === SCAC === | ||
| + | |||
| + | New field | ||
Need to add SCAC code as a property of a Trucking company in CT2. | Need to add SCAC code as a property of a Trucking company in CT2. | ||
| Line 31: | Line 33: | ||
* Deliv location | * Deliv location | ||
* etc | * etc | ||
| + | |||
| + | === 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 | ||
== DB == | == DB == | ||
Revision as of 02:46, 20 November 2013
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
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
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
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)
