Build contract price as embedded in the CatalogEntry index

We can build contract prices embedded in the WebSphere Commerce Search catalog entry index when we deploy a B2B starter store.


Procedure

  1. Change the wc.search.priceMode.compatiblePriceIndex value to 1.0 in the STORECONF table: 

      insert into storeconf(storeent_id,name,value) values('storeent_id', 'wc.search.priceMode.compatiblePriceIndex', '1.0');

    For more information, see Changing search configuration properties in the STORECONF table.

  2. Call the calculate price RESTFul service. We can call the utility at the following URL on the transaction server. The method is POST, and authentication is basic, with the spiuser and password supplied in the header. 

      https://transactionServerHost:transactionHttpsPort/wcs/resources/admin/index/dataImport/build?indexSubType=Price

    Where:

      masterCatalogId
      Required: The ID of the master catalog (for example, 10101).If you do not know the master catalog ID, run the following SQL: 

        SQL: select * from catalog where IDENTIFIER='STORE_IDENTIFIER'

      To find the master catalog ID for an Extended Site store:

      1. Find the store ID: 

          select * from storeent where IDENTIFIER='STORE_IDENTIFIER'

      2. Use the storeent_id as the store_id in the following SQL to find the catalog asset store ID of this Extended Site store: 

          select * from storerel where store_id=XXXXXX and streltyp_id=-4 and relatedstore_id not in (XXXXXX)

        Where XXXXXX is the storeent_id from the SQL in step 2.a when building the search index.

      3. Get the master catalog ID: 

          select * from storecat where storeent_id=YYYYYY and mastercatalog='1'

        Where YYYYYY is the relatedstore_id from step 2.b when building the search index.

      contractId
      Optional: Used to build prices for the specified contract IDs.
      Specify comma-separated contract IDs to index.
      If no contract IDs are passed in, a full recalculation is performed by default.
      Example: https://localhost/wcs/resources/admin/index/dataImport/build?indexSubType=Price&contractId=-10008,-10005

      catentry
      Optional: Used to build prices for the specified catalog entries.
      Specify comma-separated catalog entry IDs to index.
      If no catalog entry IDs are passed in, a full recalculation is performed by default.
      Example: https://localhost/wcs/resources/admin/index/dataImport/build?indexSubType=Price&catentryId=10006,10005

      storeId
      The eSite store ID.
      We can only pass one store ID in this parameter.
      Example: https://localhost/wcs/resources/admin/index/dataImport/build?indexSubType=Price&storeId=1

      currency
      Optional: Used to build prices for the specified currencies.
      Specify comma-separated currencies to index.

      Note: Ensure that the CURLIST table contains any additional currencies that we want built by the calculate price utility.

      If no currencies are passed in, a full recalculation is performed by default.
      Example: https://localhost/wcs/resources/admin/index/dataImport/build?indexSubType=Price&cy=USD,CAD

      numWorkers
      Optional: This parameter defines the number of calculators. The total number of products assigned to one calculator is arrived by dividing the number of products by the number of calculators. In every calculator, the assigned catentries are calculated against all valid buyer contracts deployed by the sharing stores of the specified master catalog and all valid currencies that are supported by the sharing stores. For large catalog size, IBM recommends to define larger numWorkers to decrease the total execution time of a thread. In addition, the running thread is defined by workManager maximum number, therefore when the numWorkers are larger than work manager number, other numWorkers must wait in queue to be run.
      Default is 1000.
      Example: https://localhost/wcs/resources/admin/index/dataImport/build?indexSubType=Price&numWorkers=100

      flushSize
      Optional: This parameter defines the number of batch statements that flush into database. With large flushSize, the database might roll back. With small flushSize, the database might have to be committed multiple times.
      Default is 1000.
      Example: https://localhost/wcs/resources/admin/index/dataImport/build?indexSubType=Price&flushSize=100

  3. Ensure that the call runs successfully. If the rest runs successfully, a response with body content similar to the following is returned. 

      {
    "jobStatusId": "1003"
}

    We can check the calculate price job status by calling the following API using the GET method and basic authentication in the header, using spiuser and its password. 

      https://transactionServerHost:transactionHttpsPort/wcs/resources/admin/index/dataImport/status?jobStatusId=jobStatusId

    If calculate price was successfully executed, the response to the above REST call will be similar to the following example: 

      {
    "status": {
        "finishTime": "2017-11-02 02:50:27.737085",
        "lastUpdate": "2017-11-02 02:50:27.737085",
        "progress": "100%",
        "jobStatusId": "1002",
        "startTime": "2017-11-02 02:49:55.641083",
        "message": "Price calculation started for masterCatalogId:10,001.\nPrice calculation finished successfully for masterCatalogId:10001.\n",
        "jobType": "SearchIndex",
        "properties": "[indexSubType=Price]",
        "status": "0"
    }
}

    Status=0 means the job has run successfully. -1 means the job is in progress, or its progress is unknown. Other values mean the job has failed. If we get a status that is not equal to 0, an error message will be shown in the message field. To get more logging information, set the logging level to FINEST. 

      com.ibm.commerce.foundation.dataimport.priceindex.*=finest

    After you enable this trace level in the Transaction server, we can re-run the calculate price REST call. Any errors will be recorded in the trace.log file on the Transaction server.

  4. Build the search index to index the price with indexSubType=Structured.


Example

We can verify that the contract price index is used correctly in the Aurora B2B starter store by following these steps:

  1. Set up the organization and contract price index:

    1. Register an organization in the AuroraB2BESite.

    2. Set up the following contracts:

        Default contract copy
        Use the default catalog filter and offer price rule.

        Bolts Only Contract
        Include only the Bolts category only at 50% off.

    3. Change the wc.search.priceMode.compatiblePriceIndex value to 1.0 in the STORECONF table.

    4. Call the calculate price RESTFul service.

  2. Verify the contract prices in the storefront:

    1. Open the AuroraB2BESite in a web browser and sign in to the store.

    2. Under contracts, select the Bolts Only contract and continue shopping.

    3. Go to the Bolts category and add items to your cart.

    4. Check out and confirm that the price rules are correctly applied during the checkout process.


What to do next

After the price index builds, we can optionally replicate and propagate the WebSphere Commerce Search index.