Intro into CT2 DB

From UG

Revision as of 21:46, 18 December 2009 (edit) Alex (Talk | contribs) ← Older edit		Current revision as of 08:38, 31 January 2010 (edit) (undo) Alex (Talk | contribs)
(16 intermediate revisions not shown)
Line 1:		Line 1:
-	[[Category:DB]]	+	[[Category:Data_Model]]
-	== DB Tables related to Acc component ==	+
		+
		+
		+
		+	== CT profile tables ==
		+
		+	* [[tbl Undefined Shipment]]
		+	* [[Tbl Generic Shipment]]
		+	* [[Tbl Ocean Shipment]]
		+	* [[Tbl Air Shipment]]
		+	* [[Tbl Trucking Shipment]]
		+
		+
		+	== Acc tables ==
	* Curr and Exch Rates:		* Curr and Exch Rates:
Line 13:		Line 26:
	* Purchase Inv:		* Purchase Inv:
-	** tblPurchaseInvoice	+	** [[tbl Purchase Invoice]]
-	** tblPurchaseInvoiceChargeCode	+	** [[tbl Purchase Invoice Charge Code]]
	* Sales and Internal Inv:		* Sales and Internal Inv:
-	** tblShipmentInvoices	+	** [[tbl Shipment Invoices]]
-	** tblShipmentInvoice2ContainersRelation	+	** [[tbl Shipment Invoice 2 Containers Relation]]
-	** tblShipmentInvoiceChargeCodes	+	** [[tbl Shipment Invoice Charge Codes]]
-	** tblShipmentInvoiceContainers	+	** [[tbl Shipment Invoice Containers]]
	* TOC table (Air, Ocean; Imp Tab)		* TOC table (Air, Ocean; Imp Tab)
-	** tblShipmentChargeCodes	+	** [[tbl Shipment Charge Codes]]
	* Client accounting properties:		* Client accounting properties:
-	** tblCreditLimit	+	** [[tbl Credit Limit]]
-	** tblCreditStatus	+	** [[tbl Credit Status]]
-	** tblCreditTerm	+	** [[tbl Credit Term]]
	* tblChargeBackInvoice - discontinued?		* tblChargeBackInvoice - discontinued?
		+	== OpsAdmin ==
-	== All CT2 tables ==	+	=== Users and Offices (component) ===
		+
		+	| tblGenericUser                        |
		+
		+	| tblJaguarUser                          |
		+
		+
		+	| tblJaguarUserBCCList                  |
		+
		+	| tblJaguarUserGroup                    |
		+
		+	| tblJaguarUserType                      |
		+
		+
		+	| tblOffice                              |
		+
		+	| tblUserGroup                          |
		+
		+	| tblUserGroupObject                    |
		+
		+	| tblUserRole                            |
		+
		+	| tblJaguarOpsGroup                      |
		+
		+	=== Client Companies (component) ===
		+
		+
		+	| tblClient                              |
		+
		+	| tblClientCompany                      |
		+
		+	| tblClientCompanyGroup                  |
		+
		+	| tblClientCompanyGroupRelation          |
		+
		+
		+
		+	| tblClientMode                          |
		+
		+	| tblClientUser                          |
		+
		+	| tblClientUser2AddressTransportation    |
		+
		+	| tblClientUser2Region                  |
		+
		+	| tblClientUserWatchList                |
		+
		+	| tblClientsGroup                        |
		+
		+	| tblClientsGroupRelation                |
		+
		+
		+	=== Addressbook (component) ===
		+
		+	| tblAddressBillToParty                  |
		+
		+	| tblAddressCompanyCity                  |
		+
		+	| tblAddressJaguarVendor                |
		+
		+	| tblAddressTransportation              |
		+
		+
		+
		+	=== Carriers (component) ===
		+
		+
		+	* [[tbl Airline]]                            |
		+
		+	* [[tbl Steamshipline]]                      |
		+
		+	* [[tbl Shipline]]                            |
		+
		+
		+
		+	== Remaining tables TO BE SORTED OUT ==
		+
		+
		+	| mymfavorites                          |
		+
		+	| tblAirShipment                        |
		+
		+
		+
		+	| tblAirport                            |
		+
		+	| tblAlwaysBCCEmails                    |
		+
		+	| tblAuthorizationMethod                |
		+
		+	| tblAuthorizationMethodSettings        |
		+
		+	| tblBusiestSeaPort                      |
		+
		+
		+
		+
		+	| tblCommentsMailedTo                    |
		+
		+	| tblCommodity                          |
		+
		+	| tblCommodityDisplaySettings            |
		+
		+	| tblCommodityItem                      |
		+
		+	| tblContainer                          |
		+
		+	| tblContainer2Master                    |
		+
		+	| tblContainer2Shipment                  |
		+
		+	| tblContainerSize                      |
		+
		+	| tblContainerType                      |
		+
		+	| tblCountry                            |
		+
		+
		+
		+
		+	| tblCustomer                            |
		+
		+	| tblCustomsStatus                      |
		+
		+	| tblDailyQuotes                        |
		+
		+	| tblDefaults                            |
		+
		+	| tblDeliveryDateTypes                  |
		+
		+	| tblDeliveryLocation                    |
		+
		+	| tblDropClientTops                      |
		+
		+	| tblDropCountryTops                    |
		+
		+	| tblEmailQueryHistory                  |
		+
		+	| tblEmailQueryHistoryCCAddresses        |
		+
		+	| tblEstimatedTransitTime                |
		+
		+
		+
		+	| tblFDAStatus                          |
		+
		+	| tblGenericShipment                    |
		+
		+
		+
		+	| tblGroupObjectPermission              |
		+
		+	| tblHandlingRemark                      |
		+
		+	| tblHandlingUnit                        |
		+
		+
		+
		+	| tblJaguarOpsGroupClientRelation        |
		+
		+	| tblJaguarOpsGroupCompanyRelation      |
		+
		+
		+
		+
		+	| tblLoose                              |
		+
		+	| tblMailTemplates                      |
		+
		+	| tblMaster                              |
		+
		+	| tblMasterComments                      |
		+
		+	| tblMasterCommentsMailedTo              |
		+
		+	| tblMasterOcean                        |
		+
		+	| tblMasterPDFReports                    |
		+
		+	| tblMasterTransportMode                |
		+
		+	| tblMoveType                            |
		+
		+	| tblOceanModeType                      |
		+
		+	| tblOceanShipment                      |
		+
		+
		+
		+	| tblOnPallets                          |
		+
		+	| tblPDFReports                          |
		+
		+	| tblPackagingCommodityType              |
		+
		+	| tblPackagingPalletType                |
		+
		+	| tblPackagingType                      |
		+
		+
		+	| tblRSSNewsFeeds                        |
		+
		+	| tblRegion                              |
		+
		+	| tblReport                              |
		+
		+	| tblReportRecipient                    |
		+
		+	| tblSeaPort                            |
		+
		+	| tblSecuredUrl                          |
		+
		+
		+
		+	| tblShipment2ClientCompanyGroupRelation |
		+
		+	| tblShipment2ClientCompanyRelation      |
		+
		+
		+
		+	| tblShipmentComments                    |
		+
		+	| tblShipmentDateHistory                |
		+
		+	| tblShipmentGroup                      |
		+
		+
		+	| tblShipmentLog                        |
		+
		+	| tblShipmentTemplate                    |
		+
		+	| tblShippingTerm                        |
		+
		+	| tblStandardComments                    |
		+
		+
		+
		+	| tblTableOfCharges                      |
		+
		+	| tblTestimonials                        |
		+
		+	| tblTransportMode                      |
		+
		+	| tblTruckingShipment                    |
		+
		+	| tblUSAstate                            |
		+
		+	| |
		+
		+	| tblUnitType                            |
		+
		+	| tblUpdateLogRecord                    |
		+
		+	| tblUploadedFiles                      |
		+
		+	== Views ==
	<pre>		<pre>
		+	| viewArchivedShipmentId                |
		+	| viewClientGenericShipment              |
		+	| viewClientGenericShipmentId            |
		+	| viewClientOceanShipmentDetails        |
		+	| viewClientShipmentCommodities          |
		+	| viewClientShipmentCommodityItems      |
		+	| viewClientShipmentContainers          |
		+	| viewClientTruckingShipmentDetails      |
		+	| viewClientUndefinedShipmentDetails    |
		+	| viewClientUserConsignees              |
		+	| viewClientUserDeliveryLocations        |
		+	| viewClientUserDestinationRegions      |
		+	| viewClientUserOriginRegions            |
		+	| viewClientUserPickUpLocations          |
		+	| viewClientUserShippers                |
		+	| viewClientUserShippingTerms            |
		+	| viewClientWatchList                    |
		+	| viewGenericShipment                    |
		+	| viewGenericShipment_new                |
		+	| viewPurchaseInvoice                    |
		+	| viewPurchaseInvoiceChargeCode          |
		+	| viewPurchaseInvoiceExtended            |
		+	| viewPurchaseInvoiceStatus              |
		+	| viewPurchaseInvoiceWithStatus          |
		+	| viewPurchaseInvoiceWithStatusExtended  |
		+	| viewReport                            |
		+	| viewShipment                          |
		+	| viewShipmentContainersInfo            |
		+	| viewShipmentInvoice                    |
		+	| viewShipmentTemplateInfo              |
		+	| viewTransportationCompanyCity          |
		+	| viewVendorCompanyCity                  |
		+	| viewVisibilityClientCompany            |
		+	| viewVisibilityClientCompanyGroup      |
		+	+----------------------------------------+
		+	154 rows in set
		+
		+	</pre>
		+
		+	== All tables and Views ==
		+
		+
		+	<pre>
	mysql> show tables;		mysql> show tables;
	+----------------------------------------+		+----------------------------------------+
-	| Tables_in_cybertraxStaging24          |	+	| Tables_in_cybertrax                    |
	+----------------------------------------+		+----------------------------------------+
	| mymfavorites                          |		| mymfavorites                          |
Line 86:		Line 398:
	| tblCustomsStatus                      |		| tblCustomsStatus                      |
	| tblDailyQuotes                        |		| tblDailyQuotes                        |
-	| tblDefaults                            |
	| tblDeliveryDateTypes                  |		| tblDeliveryDateTypes                  |
	| tblDeliveryLocation                    |		| tblDeliveryLocation                    |
Line 161:		Line 472:
	| tblUserGroupObject                    |		| tblUserGroupObject                    |
	| tblUserRole                            |		| tblUserRole                            |
-
-
	| viewArchivedShipmentId                |		| viewArchivedShipmentId                |
		+	| viewClientAirShipmentDetails          |
	| viewClientGenericShipment              |		| viewClientGenericShipment              |
	| viewClientGenericShipmentId            |		| viewClientGenericShipmentId            |
Line 197:		Line 507:
	| viewVisibilityClientCompany            |		| viewVisibilityClientCompany            |
	| viewVisibilityClientCompanyGroup      |		| viewVisibilityClientCompanyGroup      |
		+	| viewVisibilityClientCopmany            |
	+----------------------------------------+		+----------------------------------------+
-	154 rows in set	+	155 rows in set
-		+
	</pre>		</pre>

---

Current revision as of 08:38, 31 January 2010

Contents 1 CT profile tables 2 Acc tables 3 OpsAdmin 3.1 Users and Offices (component) 3.2 Client Companies (component) 3.3 Addressbook (component) 3.4 Carriers (component) 4 Remaining tables TO BE SORTED OUT 5 Views 6 All tables and Views

[edit] CT profile tables

• tbl Undefined Shipment
• Tbl Generic Shipment
• Tbl Ocean Shipment
• Tbl Air Shipment
• Tbl Trucking Shipment

[edit] Acc tables

• Curr and Exch Rates:
  • tbl Currency
  • tbl Currency Exchange Rate
  • tbl Currency Exchange Rate Change
  • tbl Client Exchange Rate - discontinued?

• CC:
  • tblChargeCode

• Purchase Inv:
  • tbl Purchase Invoice
  • tbl Purchase Invoice Charge Code

• Sales and Internal Inv:
  • tbl Shipment Invoices
  • tbl Shipment Invoice 2 Containers Relation
  • tbl Shipment Invoice Charge Codes
  • tbl Shipment Invoice Containers

• TOC table (Air, Ocean; Imp Tab)
  • tbl Shipment Charge Codes

• Client accounting properties:
  • tbl Credit Limit
  • tbl Credit Status
  • tbl Credit Term

• tblChargeBackInvoice - discontinued?

[edit] OpsAdmin

[edit] Users and Offices (component)

| tblGenericUser |

| tblJaguarUser |

| tblJaguarUserBCCList |

| tblJaguarUserGroup |

| tblJaguarUserType |

| tblOffice |

| tblUserGroup |

| tblUserGroupObject |

| tblUserRole |

| tblJaguarOpsGroup |

[edit] Client Companies (component)

| tblClient |

| tblClientCompany |

| tblClientCompanyGroup |

| tblClientCompanyGroupRelation |

| tblClientMode |

| tblClientUser |

| tblClientUser2AddressTransportation |

| tblClientUser2Region |

| tblClientUserWatchList |

| tblClientsGroup |

| tblClientsGroupRelation |

[edit] Addressbook (component)

| tblAddressBillToParty |

| tblAddressCompanyCity |

| tblAddressJaguarVendor |

| tblAddressTransportation |

[edit] Carriers (component)

• tbl Airline |

• tbl Steamshipline |

• tbl Shipline |

[edit] Remaining tables TO BE SORTED OUT

| mymfavorites |

| tblAirShipment |

| tblAirport |

| tblAlwaysBCCEmails |

| tblAuthorizationMethod |

| tblAuthorizationMethodSettings |

| tblBusiestSeaPort |

| tblCommentsMailedTo |

| tblCommodity |

| tblCommodityDisplaySettings |

| tblCommodityItem |

| tblContainer |

| tblContainer2Master |

| tblContainer2Shipment |

| tblContainerSize |

| tblContainerType |

| tblCountry |

| tblCustomer |

| tblCustomsStatus |

| tblDailyQuotes |

| tblDefaults |

| tblDeliveryDateTypes |

| tblDeliveryLocation |

| tblDropClientTops |

| tblDropCountryTops |

| tblEmailQueryHistory |

| tblEmailQueryHistoryCCAddresses |

| tblEstimatedTransitTime |

| tblFDAStatus |

| tblGenericShipment |

| tblGroupObjectPermission |

| tblHandlingRemark |

| tblHandlingUnit |

| tblJaguarOpsGroupClientRelation |

| tblJaguarOpsGroupCompanyRelation |

| tblLoose |

| tblMailTemplates |

| tblMaster |

| tblMasterComments |

| tblMasterCommentsMailedTo |

| tblMasterOcean |

| tblMasterPDFReports |

| tblMasterTransportMode |

| tblMoveType |

| tblOceanModeType |

| tblOceanShipment |

| tblOnPallets |

| tblPDFReports |

| tblPackagingCommodityType |

| tblPackagingPalletType |

| tblPackagingType |

| tblRSSNewsFeeds |

| tblRegion |

| tblReport |

| tblReportRecipient |

| tblSeaPort |

| tblSecuredUrl |

| tblShipment2ClientCompanyGroupRelation |

| tblShipment2ClientCompanyRelation |

| tblShipmentComments |

| tblShipmentDateHistory |

| tblShipmentGroup |

| tblShipmentLog |

| tblShipmentTemplate |

| tblShippingTerm |

| tblStandardComments |

| tblTableOfCharges |

| tblTestimonials |

| tblTransportMode |

| tblTruckingShipment |

| tblUSAstate |

| |

| tblUnitType |

| tblUpdateLogRecord |

| tblUploadedFiles |

[edit] Views

| viewArchivedShipmentId                 |
| viewClientGenericShipment              |
| viewClientGenericShipmentId            |
| viewClientOceanShipmentDetails         |
| viewClientShipmentCommodities          |
| viewClientShipmentCommodityItems       |
| viewClientShipmentContainers           |
| viewClientTruckingShipmentDetails      |
| viewClientUndefinedShipmentDetails     |
| viewClientUserConsignees               |
| viewClientUserDeliveryLocations        |
| viewClientUserDestinationRegions       |
| viewClientUserOriginRegions            |
| viewClientUserPickUpLocations          |
| viewClientUserShippers                 |
| viewClientUserShippingTerms            |
| viewClientWatchList                    |
| viewGenericShipment                    |
| viewGenericShipment_new                |
| viewPurchaseInvoice                    |
| viewPurchaseInvoiceChargeCode          |
| viewPurchaseInvoiceExtended            |
| viewPurchaseInvoiceStatus              |
| viewPurchaseInvoiceWithStatus          |
| viewPurchaseInvoiceWithStatusExtended  |
| viewReport                             |
| viewShipment                           |
| viewShipmentContainersInfo             |
| viewShipmentInvoice                    |
| viewShipmentTemplateInfo               |
| viewTransportationCompanyCity          |
| viewVendorCompanyCity                  |
| viewVisibilityClientCompany            |
| viewVisibilityClientCompanyGroup       |
+----------------------------------------+
154 rows in set

[edit] All tables and Views

mysql> show tables;
+----------------------------------------+
| Tables_in_cybertrax                    |
+----------------------------------------+
| mymfavorites                           |
| tblAddressBillToParty                  |
| tblAddressCompanyCity                  |
| tblAddressJaguarVendor                 |
| tblAddressTransportation               |
| tblAirShipment                         |
| tblAirline                             |
| tblAirport                             |
| tblAlwaysBCCEmails                     |
| tblAuthorizationMethod                 |
| tblAuthorizationMethodSettings         |
| tblBusiestSeaPort                      |
| tblChargeBackInvoice                   |
| tblChargeCode                          |
| tblClient                              |
| tblClientCompany                       |
| tblClientCompanyGroup                  |
| tblClientCompanyGroupRelation          |
| tblClientExchangeRate                  |
| tblClientMode                          |
| tblClientUser                          |
| tblClientUser2AddressTransportation    |
| tblClientUser2Region                   |
| tblClientUserWatchList                 |
| tblClientsGroup                        |
| tblClientsGroupRelation                |
| tblCommentsMailedTo                    |
| tblCommodity                           |
| tblCommodityDisplaySettings            |
| tblCommodityItem                       |
| tblContainer                           |
| tblContainer2Master                    |
| tblContainer2Shipment                  |
| tblContainerSize                       |
| tblContainerType                       |
| tblCountry                             |
| tblCreditLimit                         |
| tblCreditStatus                        |
| tblCreditTerm                          |
| tblCurrency                            |
| tblCurrencyExchangeRate                |
| tblCurrencyExchangeRateChange          |
| tblCustomer                            |
| tblCustomsStatus                       |
| tblDailyQuotes                         |
| tblDeliveryDateTypes                   |
| tblDeliveryLocation                    |
| tblDropClientTops                      |
| tblDropCountryTops                     |
| tblEmailQueryHistory                   |
| tblEmailQueryHistoryCCAddresses        |
| tblEstimatedTransitTime                |
| tblExchangeRate                        |
| tblFDAStatus                           |
| tblGenericShipment                     |
| tblGenericUser                         |
| tblGroupObjectPermission               |
| tblHandlingRemark                      |
| tblHandlingUnit                        |
| tblJaguarOpsGroup                      |
| tblJaguarOpsGroupClientRelation        |
| tblJaguarOpsGroupCompanyRelation       |
| tblJaguarUser                          |
| tblJaguarUserBCCList                   |
| tblJaguarUserGroup                     |
| tblJaguarUserType                      |
| tblLoose                               |
| tblMailTemplates                       |
| tblMaster                              |
| tblMasterComments                      |
| tblMasterCommentsMailedTo              |
| tblMasterOcean                         |
| tblMasterPDFReports                    |
| tblMasterTransportMode                 |
| tblMoveType                            |
| tblOceanModeType                       |
| tblOceanShipment                       |
| tblOffice                              |
| tblOnPallets                           |
| tblPDFReports                          |
| tblPackagingCommodityType              |
| tblPackagingPalletType                 |
| tblPackagingType                       |
| tblPurchaseInvoice                     |
| tblPurchaseInvoiceChargeCode           |
| tblRSSNewsFeeds                        |
| tblRegion                              |
| tblReport                              |
| tblReportRecipient                     |
| tblSeaPort                             |
| tblSecuredUrl                          |
| tblShipline                            |
| tblShipment2ClientCompanyGroupRelation |
| tblShipment2ClientCompanyRelation      |
| tblShipmentChargeCodes                 |
| tblShipmentComments                    |
| tblShipmentDateHistory                 |
| tblShipmentGroup                       |
| tblShipmentInvoice2ContainersRelation  |
| tblShipmentInvoiceChargeCodes          |
| tblShipmentInvoiceContainers           |
| tblShipmentInvoices                    |
| tblShipmentLog                         |
| tblShipmentTemplate                    |
| tblShippingTerm                        |
| tblStandardComments                    |
| tblSteamshipline                       |
| tblTableOfCharges                      |
| tblTestimonials                        |
| tblTransportMode                       |
| tblTruckingShipment                    |
| tblUSAstate                            |
| tblUndefinedShipment                   |
| tblUnitType                            |
| tblUpdateLogRecord                     |
| tblUploadedFiles                       |
| tblUserGroup                           |
| tblUserGroupObject                     |
| tblUserRole                            |
| viewArchivedShipmentId                 |
| viewClientAirShipmentDetails           |
| viewClientGenericShipment              |
| viewClientGenericShipmentId            |
| viewClientOceanShipmentDetails         |
| viewClientShipmentCommodities          |
| viewClientShipmentCommodityItems       |
| viewClientShipmentContainers           |
| viewClientTruckingShipmentDetails      |
| viewClientUndefinedShipmentDetails     |
| viewClientUserConsignees               |
| viewClientUserDeliveryLocations        |
| viewClientUserDestinationRegions       |
| viewClientUserOriginRegions            |
| viewClientUserPickUpLocations          |
| viewClientUserShippers                 |
| viewClientUserShippingTerms            |
| viewClientWatchList                    |
| viewGenericShipment                    |
| viewGenericShipment_new                |
| viewPurchaseInvoice                    |
| viewPurchaseInvoiceChargeCode          |
| viewPurchaseInvoiceExtended            |
| viewPurchaseInvoiceStatus              |
| viewPurchaseInvoiceWithStatus          |
| viewPurchaseInvoiceWithStatusExtended  |
| viewReport                             |
| viewShipment                           |
| viewShipmentContainersInfo             |
| viewShipmentInvoice                    |
| viewShipmentTemplateInfo               |
| viewTransportationCompanyCity          |
| viewVendorCompanyCity                  |
| viewVisibilityClientCompany            |
| viewVisibilityClientCompanyGroup       |
| viewVisibilityClientCopmany            |
+----------------------------------------+
155 rows in set