Adding a custom query template file to extract category-level SKUs

To extract category-level SKUs, you must create a custom query template file that defines a new SQL query. This SQL query returns the catalog entry IDs of all the products and category-level SKUs belonging to as specific store. The custom query template file uses the default SQL composer that is used to inject database-level paging indexes to the SQL statement.

The data extraction utility uses the IBM_Admin_DataExtract access profile to return the catalog entry data from the WebSphere Commerce database. This access profile is defined in the wc-query-CatalogEntry-admin-get-fep.tpl query template file.

Before you begin

Review the following topics to learn more about query template files and access profiles:

You can also review the default query template file that the data extraction utility uses at the following path:

workspace_dir\WC\xml\config\com.ibm.commerce.catalog-fep\wc-query-CatalogEntry-admin-get-fep.tpl

About this task

The code for the custom query template file is provided for you in the steps of the procedure.

Procedure

Open WebSphere Commerce Developer and switch to the Enterprise Explorer view.
Create a custom Get query template file to extract category-level SKUs:
1. Create a workspace_dir\WC\xml\config\com.ibm.commerce.catalog-ext folder, if one does not exist.
2. Right-click the com.ibm.commerce.catalog-ext folder.
3. Click New > File.
4. Name the custom query template file by using this syntax:
```
wc-query-name_of_your_custom_template.tpl
```
  For example:
```
wc-query-MyCompanyCatalogEntry-get.tpl
```
5. Click Finish.

Paste the following query template file contents into the empty custom query template you created in the previous step:

BEGIN_SYMBOL_DEFINITIONS
        <!-- CATENTRY table -->        
        COLS:CATENTRY_ID=CATENTRY:CATENTRY_ID
        
END_SYMBOL_DEFINITIONS
<!-- ============================================================= -->
<!-- This SQL will return the elements of products and                    -->
<!-- Category level SKUs belonging to the store                                       -->
<!-- The access profiles that apply to this SQL are:               -->
<!-- IBM_Admin_DataExtract                                                                                      -->
<!-- @param Context:StoreID - The store for which to retrieve the  -->
<!--        catalog entry. This parameter is retrieved from within -->
<!--            the business context.                                      -->
<!-- ============================================================= -->
BEGIN_XPATH_TO_SQL_STATEMENT
  name=/CatalogEntry[CatalogEntryIdentifier[ExternalIdentifier[StoreIdentifier[(UniqueID=)]]] and CategoryLevelSKU]
  base_table=CATENTRY
  className=com.ibm.commerce.catalogentry.facade.server.services.dataaccess.db.jdbc.CatalogEntryDataExtractSQLComposer
  sql=
    SELECT 
      CATENTRY.$COLS:CATENTRY_ID$
    FROM
      CATENTRY 
    JOIN
      STORECENT ON (CATENTRY.CATENTRY_ID = STORECENT.CATENTRY_ID AND STORECENT.STOREENT_ID IN (?UniqueID?))
                                       
    WHERE NOT EXISTS 
      (SELECT 1 FROM CATENTREL WHERE CATENTREL.CATENTRY_ID_CHILD = CATENTRY.CATENTRY_ID AND CATENTREL.CATRELTYPE_ID = 'PRODUCT_ITEM') AND 
        CATENTRY.CATENTTYPE_ID != 'BundleBean' AND 
        CATENTRY.BUYABLE=1 AND 
        CATENTRY.MARKFORDELETE=0                
END_XPATH_TO_SQL_STATEMENT

Save and close the file.
Deploy the query template file to the WebSphere Application Server.