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:
- Create a workspace_dir\WC\xml\config\com.ibm.commerce.catalog-ext folder,
if one does not exist.
- Right-click the com.ibm.commerce.catalog-ext folder.
- Click .
- 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
- 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.