204 ER
From UG
(Difference between revisions)
(→SF Address) |
(→Data) |
||
| Line 42: | 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 | ||
| - | |||
| - | |||
=== SF State=== | === SF State=== | ||
Revision as of 03:55, 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
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)
