Adding a query template file to extract custom catalog entry data

The IBM Product Recommendations, formerly known as Coremetrics Intelligent Offer data extraction utility invokes the Catalog services to retrieve service data objects for the CatalogEntry noun. When making a service call, you must specify an access profile to define the amount of data that you want the server to return. The data extraction utility uses a default access profile, IBM_Admin_DataExtract. To return your custom data, you must create access profile in a 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

Procedure

Open WebSphere Commerce Developer and switch to the Enterprise Explorer view.
Create a custom Get query template file for the Catalog service module:
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-DataExtract-ABCcompany-CatalogEntry-admin-get.tpl
```
5. Click Finish.
Create the query template file contents.
Your query template file must define an access profile to retrieve your custom catalog entry data. The best practice is to extend the existing IBM_Admin_DataExtract access profile, rather than creating your own stand-alone access profile. This way, if IBM enhances the existing access profile in future software updates, the utility will pick up these enhancements.
For examples of query template files, see Adding query templates to include custom information.
Save and close the new query template file.
Deploy the query template file to the WebSphere Application Server.

Example

The following is an example query template file that retrieves custom warranty data for catalog entries. The query template file defines an associated SQL statement named MyCompanyWarrantyCatalogEntry that the access profile can use to retrieve warranty data. Then, the query template file defines a new MyCompany_Admin_DataExtract access profile that extends the default IBM_Admin_DataExtract access profile that is provided with the data extraction utility.

BEGIN_SYMBOL_DEFINITIONS
<!-- MyCompany extension tables -->
COLS:XWARRANTY=XWARRANTY:*
COLS:CATENTRY_ID=CATENTRY:CATENTRY_ID

END_SYMBOL_DEFINITIONS

BEGIN_ASSOCIATION_SQL_STATEMENT
  name=MyCompanyWarrantyCatalogEntry
  base_table=CATENTRY
    sql=
      SELECT
      	CATENTRY.$COLS:CATENTRY_ID$,
        XWARRANTY.$COLS:XWARRANTY$       
      FROM
        CATENTRY INNER JOIN XWARRANTY ON (CATENTRY.CATENTRY_ID = XWARRANTY.CATENTRY_ID)
      WHERE
        XWARRANTY.CATENTRY_ID IN ($ENTITY_PKS$) 
END_ASSOCIATION_SQL_STATEMENT

BEGIN_PROFILE
	name=MyCompany_Admin_DataExtract
	extends = IBM_Admin_DataExtract
	BEGIN_ENTITY
		associated_sql_statement=MyCompanyWarrantyCatalogEntry
	END_ENTITY
END_PROFILE