HCL Commerce Enterprise

Registering your existing offer and list price lists in the STORETPC table

The Catalog Filter and Pricing tool retrieves the offer and list price lists of a store from a new STORETPC table, instead of the existing TRADEPOSCN table. By registering your existing price lists in the STORETPC table, business users can manage those price lists in the Catalog Filter and Pricing tool.

Note: You must have an entry in the STORETPC table for both the offer price list and the list price list, even if your store does not use list prices.

About this task

This task applies to:
  • Single stores (stores that are not extended site stores)
  • Extended sites – complete this task on the following stores:
    • Catalog asset store
    • Each extended site store

Procedure

  1. Run the following SQL statement to find the ID (tradeposcn_id) of your existing offer price list for the master catalog of the store in the CATGRPTPC table:
    select tradeposcn_id from catgrptpc where store_id = storeId;

    Where storeId is the store entity ID as defined in the STORE_ID column of the STORE table.

  2. Do one of the following things:
    • If you have an existing list price list, you can run the following SQL statement to find all the price lists registered for the store. Then find the ID (tradeposcn_id) of your existing list price list:
      select * from tradeposcn where member_id =(select MEMBER_ID from storeent 
      where STOREENT_ID=storeId);

      Where storeId is the store entity ID as defined in the STORE_ID column of the STORE table.

    • If you do not have an existing list price list, you can create an empty one in the STORETPC table. You must ensure that there is a list price list registered in the STORETPC table even if your store does not use it.
  3. Register your existing offer price list in the STORETPC table. Use the following SQL statement:
    insert into storetpc (STOREENT_ID, TRADEPOSCN_ID, STTPCUSG_ID) 
    values (storeId, tradeposcnId, 2);

    where:

    storeId
    The store entity ID as defined in the STORE_ID column of the STORE table.
    tradeposcnId
    The ID of the existing offer price list that you looked up in a previous step.
    2
    Indicates the price list is for the master offer price.
  4. Register your existing list price list in the STORETPC table. If you do not have an existing list price list, you must register an empty (dummy) list price list. Use the following SQL statement:
    insert into storetpc (STOREENT_ID, TRADEPOSCN_ID, STTPCUSG_ID) 
    values (storeId, tradeposcnId, 1);

    where:

    storeId
    The store entity ID as defined in the STORE_ID column of the STORE table.
    tradeposcnId
    The ID of the existing list price list that you looked up in a previous step. If you do not have an existing list price list, you can make up an ID, for example, 99999.
    1
    Indicates the price list is for the list price.