ATG Database Tables

Most Important Tables that is required when analysing ATG related things are been listed below with all the description.

The below list of tables is been divided based on the schemas in which they reside. The schemas that have been classified is based in Switching Database Architecture. For nonSwitching it a bit different which you will get to know when you reach the end.

Schemas are :

  • Production Schema
  • Catalog Schema(SwitchA/CatA/SwitchB/CatB)
  • Publishing Schema

Production Schema :

      User:

  1. DPS_USER - Has all the User details, whoever registers their account in ATG will have its entry its here.
  2. DPS_CREDIT_CARD - Contains all the User profile save cards info. This is been linked to DPS_USER table with linking table(DPS_USR_CREDITCARD).
  3. DPS_CONTACT_INFO - All the User saved addresses will be saved here like firstname, lastname, Address1, City, State, Phone Number. This table is linked with DPS_USER table using DPS_USER_ADDRESS.
  4. DCS_USER - This tables Contains User default details and also user specific settings like AllowPartialShip, Default carrier and more. 

      SQL JMS:

  1. DMS_MSG - Its stores all the Sql messages that has to be passed among the ATG systems.  Ever Heard of PatchBay stuff. This table is related to that stuff. Its has the message that has to be passed in SqlObjectMessage form also all the information related to Destination to which it has to reach to. Once the message is successfully been read to, then the message will be removed from the table.
  2. DMS_QUEUE - All the Queue thats is been registered with JMS will be listed in this. The important Column is Queue_id which will be used to point to queues.
  3. DMS_QUEUE_ENTRY - This acts as mapping table for (1) and (2) tables. The entries in this also be removed as soon as the message is being consumed.
  4. DMS_TOPIC and DMS_TOPIC_ENTRY - This is Similar to Queues. Used to save the topics related information.
  5. DMS_TOPIC_SUB - Every JMS topic has subscribers which consumes the message sent to topics. There can be any number of Subscribers to a topic. This table has all the Topic and Subscribers relationShip.
  6. DMS_LIMBO_MESSAGE - Contains the messages that has to be delivered in specific time to specific destination.

       Order

  1. DCSPP_ORDER - All the Order related information is Saved here. Important columns would be submitted_date, Profile_id(Can be looked up in dps_user table), State, Price_info(Can be looked up in dcspp_amount_info table), Site_id.
    • DCSPP_ORDER_ITEM - Order and Items(dcspp_item) joining table.
    • DCSPP_ORDER_ADJ - Order level Adjustments
    • DCSPP_ORDER_PRICE - Order level price
    • DCSPP_ORDER_REL - All the relationships for that order be it be item level or Shipping group level and payment group level will be present here.
  2. DCSPP_PAY_GROUP - Payment related information will be stored here. Imp columns Payment_Method, Amount, Amount_authorized, Amount_debited and Amount_credited. Linked to Order table with order_ref column.
    • DCSPP_PAY_STATUS - Contains the Payment status updates for all the transactions. Linked tables are DCSPP_AUTH_STATUS, DCSPP_DEBIT_STATUS and DCSPP_CREDIT_STATUS.
    • DCSPP_PAYORDER_REL - Payment order relationship.
    • DCSPP_BILL_ADDR - Billing Address informatio is stored here. Payment_group_id is search identifier.
  3. DCSPP_SHIP_GROUP - Store to store Shipping related information.  Important columns Shipping_method, STATE, Submitted_date, Price_info(Can be looked up in dcspp_amount_info table).Linked to Order table with order_ref column.
    • DCSPP_SHIP_ADDR - Shipping Group's address is saved here.  Shipping_group_id is used for finding the shipping_address.
    • DCSPP_SHIP_PRICE - Shipping prices.
  4. DCSPP_ITEM - All the CommerceItems entries will be present here. Important Columns Catalog_ref_id, Product_id, Site_id, Quantity, State and Price_info(Can be looked up in dcspp_amount_info table).
    • DCSPP_ITEM_PRICE - All the Item prices mapped with price_info id from the dcspp_item table.
  5. DCSPP_AMOUNT_INFO - All the amount Info details at all the levels is been maintained here. Item level prices, Order level, Shipping and PayGroup level prices also.
    • DCSPP_AMTINFO_ADJ - All the Adjustments that are been applied to all the levels will be stored here. AMOUNT_INFO_ID can be used here to link this.
    • DCSPP_PRICE_ADJUST - All the Adjustments like Coupons , Promotions , discounts that is been applied are saved here.
      DCSPP_AMOUNT_INFO(AMOUNT_INFO_ID) ---> DCSPP_AMTINFO_ADJ(ADJUSTMENTS) ---> DCSPP_PRICE_ADJUST(ADJUSTMENT_ID.
       Other Important tables in Core schema include
  • DCSPP_COUPON - All the Coupon related information is maintained in Prod Schema not in Cat schema.
  • DCS_INVENTORY - This will maintain all the items stock levels. This takes SKUID as reference.

Comments

Post a Comment

Popular posts from this blog

ATG BCC Users Roles

Each and Every person Who ever Login's to the ATG System Should have Users created in ProfileRepositoy. Their accesses to perform different Actions/Activities depends on the kind of Roles assigned to that User. Below are the Different roles that User Should Possess for Accessing BCC. User Role Description EPub-User Business User Who Creates and manages Assets. He Cannot Review them or Deploy them. EPub-Manager User Who Reviews and Approves the Assets created by ‘EPub-User’ and can deploy the same on Staging and Production. He can also create New Assets and Move them between the Targets. EPub-Admin Users Can Create new Users and grant Access to different users. Can create new users. Can assign new Responsibilities/remove responsibilities  to/from existing User. He is Same as Epub-user in case of Assests creation. He can just create/manage but cannot move them between targets. EPub-Super-Admin Has all the permissions. of the Above three
Read more

Resetting the Dynamo Admin password

Facing difficulty in Logging in to dynamo admin or Forgot Password for the same. NO PROBLEM. Please Follow below Steps to reset it. 1) Delete all the rows from table das_account . 2) Restart the server instance. This Creates all the rows in das_account with default Values. 3) LOGIN to dyn/admin with default credentials admin/admin . 4) After Login DYN ADMIN ask to reset the password. Reset to the desired Value. Hope this Helps.
Read more