204 ER
From UG
(Difference between revisions)
(→Changes) |
|||
| Line 26: | Line 26: | ||
http://en.wikipedia.org/wiki/Standard_Carrier_Alpha_Code | http://en.wikipedia.org/wiki/Standard_Carrier_Alpha_Code | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
=== SF Company === | === SF Company === | ||
| Line 49: | Line 42: | ||
This is ship from "Address" for specific LD number. Pull and concatenate Address 1, Address 2, Address 3 | This is ship from "Address" for specific LD number. Pull and concatenate Address 1, Address 2, Address 3 | ||
| + | |||
| + | == 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 == | == DB == | ||
Revision as of 02:48, 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
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
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)
