204 ER

From UG

Jump to: navigation, search


Contents

[edit] Intro

[edit] Data

Defines ER (DB Model) changes related to project.

[edit] 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

[edit] SF Company

This is ship from "Company Name" for specific LD number.

select ...

[edit] SF City

This is ship from "City" for specific LD number.

select ....

[edit] SF Address

This is ship from "Address" for specific LD number. Pull and concatenate Address 1, Address 2, Address 3

[edit] SF State

[edit] SF Zip

[edit] SF Date

Estimated Pick Up date for specific LD number.

[edit] 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

[edit] 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)

Personal tools