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
Procedure
- Open WebSphere Commerce Developer and switch to the Enterprise Explorer view.
-
Create a custom Get query template file to extract category-level SKUs:
-
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.