Intro into CT2 DB

From UG

(Difference between revisions)
Jump to: navigation, search
(DB Tables related to Acc component)
 
(17 intermediate revisions not shown)
Line 1: Line 1:
-
[[Category:DB]]
+
[[Category:Data_Model]]
-
== All CT2 tables ==
+
 
 +
 
 +
 
 +
 
 +
== CT profile tables ==
 +
 
 +
* [[tbl Undefined Shipment]]
 +
* [[Tbl Generic Shipment]]
 +
* [[Tbl Ocean Shipment]]
 +
* [[Tbl Air Shipment]]
 +
* [[Tbl Trucking Shipment]]
 +
 
 +
 
 +
== 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?
 +
 
 +
== OpsAdmin ==
 +
 
 +
=== 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 54: Line 398:
| tblCustomsStatus                      |
| tblCustomsStatus                      |
| tblDailyQuotes                        |
| tblDailyQuotes                        |
-
| tblDefaults                            |
 
| tblDeliveryDateTypes                  |
| tblDeliveryDateTypes                  |
| tblDeliveryLocation                    |
| tblDeliveryLocation                    |
Line 129: Line 472:
| tblUserGroupObject                    |
| tblUserGroupObject                    |
| tblUserRole                            |
| tblUserRole                            |
-
 
-
 
| viewArchivedShipmentId                |
| viewArchivedShipmentId                |
 +
| viewClientAirShipmentDetails          |
| viewClientGenericShipment              |
| viewClientGenericShipment              |
| viewClientGenericShipmentId            |
| viewClientGenericShipmentId            |
Line 165: Line 507:
| viewVisibilityClientCompany            |
| viewVisibilityClientCompany            |
| viewVisibilityClientCompanyGroup      |
| viewVisibilityClientCompanyGroup      |
 +
| viewVisibilityClientCopmany            |
+----------------------------------------+
+----------------------------------------+
-
154 rows in set
+
155 rows in set
-
 
+
</pre>
</pre>
-
 
-
== DB Tables related to Acc component ==
 
-
 
-
* Curr and Exch Rates:
 
-
** [[tbl Currency]]                           
 
-
** [[tbl Currency Exchange Rate]]       
 
-
** [[tbl Currency Exchange Rate Change]] 
 
-
** [[tbl Client Exchange Rate]]    - discontinued? 
 
-
 
-
* CC:
 
-
** tblChargeCode
 
-
 
-
* Purchase Inv:
 
-
** tblPurchaseInvoice                     
 
-
** tblPurchaseInvoiceChargeCode           
 
-
 
-
* Sales and Internal Inv:
 
-
** tblShipmentInvoices   
 
-
** tblShipmentInvoice2ContainersRelation 
 
-
** tblShipmentInvoiceChargeCodes         
 
-
** tblShipmentInvoiceContainers           
 
-
 
-
* TOC table (Air, Ocean; Imp Tab)
 
-
** tblShipmentChargeCodes
 
-
 
-
* Client accounting properties:
 
-
** tblCreditLimit                       
 
-
** tblCreditStatus                       
 
-
** tblCreditTerm
 
-
 
-
* tblChargeBackInvoice - discontinued?
 

Current revision as of 08:38, 31 January 2010




Contents

[edit] CT profile tables


[edit] Acc tables

  • CC:
    • tblChargeCode
  • 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)


[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
Personal tools