WCS Catalog data model

— IBM documentation on WCS Catalog data model

— Get Master Catalog associated to a Store, Query by Store Name

SELECT * FROM storecat WHERE (storeent_id = (select storeent_id from storeent where identifier='AuroraESite'
) or storeent_id in
(SELECT relatedstore_id FROM storerel WHERE store_id = (select storeent_id from storeent where identifier='AuroraESite'
) AND streltyp_id = -4))
AND mastercatalog = '1';

— Get top categories of a master catalog

select * from catgrpdesc where catgroup_id in
(select catgroup_id from CATTOGRP where catalog_id in
(select catalog_id from catalog where identifier='Extended Sites Catalog Asset Store')
) and language_id=-1;

— Get second level category of top level category

Select * from catgrpdesc where catgroup_id in
(select catgroup_id_child from catgrprel where catgroup_id_parent
in (select catgroup_id from CATTOGRP where catalog_id in
(select catalog_id from catalog where identifier='Extended Sites Catalog Asset Store')))
and language_id=-1;

— Get products associated to a category, query by category name

select * from catentdesc where catentry_id in
(select catentry_id from catentry where catentry_id in
(select catentry_id from CATGPENREL where catgroup_id = (Select catgroup_id from catgrpdesc where name='Dairy'))
and catenttype_id='ProductBean')
and language_id=-1;

— Load inventory into inventory table for non – ATP store model
— replace catentry id and store name

insert into inventory
(catentry_id, quantity, ffmcenter_id, store_id, quantitymesaure, inventoryflags)
values
('XXXX','999',(select ffmcenter_id from store where directory = 'AuroraESite'),
(select store_id from store where directory = 'AuroraESite'),'C62',0);

— Retrieve shipping method associated to a store

select * from SHIPMODE where storeent_id in ( select storeent_id from storeent where identifier='AuroraStorefrontAssetStore');

— Get attribute value for a given attribute name from WCS attribute dictionary

SELECT CA.CATENTRY_ID "Catentry ID",cat.partnumber as "Part Number", A.IDENTIFIER as "Attribute Name", CA.SEQUENCE,av.identifier as "Attribute Value"
FROM catentryattr CA, ATTR A, ATTRVALDESC AD, attrval av , catentry cat
WHERE CA.ATTR_ID = A.ATTR_ID AND CA.ATTRVAL_ID = AD.ATTRVAL_ID AND A.ATTR_ID = AD.ATTR_ID AND CA.ATTRVAL_ID = AD.ATTRVAL_ID
AND cat.catentry_id = CA.CATENTRY_iD
AND ad.attrval_id=av.attrval_id
-- uncomment this section if you want to query for a specific part number
-- AND CA.catentry_Id IN
--(SELECT catentry_id FROM catentry where partnumber IN ('xxxxxxxxxxx'))
AND CA.attr_id IN (SELECT attr_id FROM attr
-- replace swatchSize with the attribute name
WHERE identifier in ('swatchSize'));

— Get ship retail for an item, actual ship retail in the cart will depend on the address jurisdiction and ship method selected
— this query will return ship retail value for all available ship method and jurisdiction based on items category

SELECT CATGPCALCD.CATGROUP_ID,CALCODE.CALCODE_ID, CALRULE.FIELD1 , CALRLOOKUP.Value, CALSCALE.CALMETHOD_ID, SHPJCRULE.FFMCENTER_ID,
SHPJCRULE.JURSTGROUP_ID,
SHPJCRULE.SHIPMODE_ID,
(select code from shipmode shpmode where shpmode.shipmode_id = SHPJCRULE.SHIPMODE_ID) as shipmethod,
(select code from JURSTGROUP jurmode where jurmode.JURSTGROUP_ID = SHPJCRULE.JURSTGROUP_ID) as jurisdiction,
calscale.code FROM CALCODE, CALRULE,
CALRLOOKUP,CRULESCALE, CALRANGE,SHPJCRULE,CALSCALE,CATGPCALCD WHERE
CALCODE.CALCODE_ID = CALRULE.CALCODE_ID AND CALRULE.CALRULE_ID = SHPJCRULE.CALRULE_ID
AND CALRULE.CALRULE_ID = CRULESCALE.CALRULE_ID AND CRULESCALE.CALSCALE_ID = CALSCALE.CALSCALE_ID AND
CALRANGE.CALSCALE_ID =CALSCALE.CALSCALE_ID AND CALRANGE.CALRANGE_ID = CALRLOOKUP.CALRANGE_ID
AND CALCODE.CALCODE_ID = CATGPCALCD.CALCODE_ID
AND CATGPCALCD.CATGROUP_ID IN
-- replace partnumber with the actual value
(SELECT CATGROUP_ID FROM CATGPENREL
WHERE CATENTRY_ID in (select catentry_id from catentry where partnumber='XXXXXXX'))
order by FIELD1,JURSTGROUP_ID,SHIPMODE_ID;

— Get ship retail for an item, actual ship retail in the cart will depend on the address jurisdiction and ship method selected
— this query will return ship retail value for all available ship method and jurisdiction based on the item

SELECT CATGPCALCD.CATGROUP_ID,CALCODE.CALCODE_ID, CALRULE.FIELD1 , CALRLOOKUP.Value, CALSCALE.CALMETHOD_ID, SHPJCRULE.FFMCENTER_ID,
SHPJCRULE.JURSTGROUP_ID, SHPJCRULE.SHIPMODE_ID, calscale.code FROM CALCODE,CALRULE,
CALRLOOKUP,CRULESCALE,CALRANGE,SHPJCRULE,CALSCALE,CATGPCALCD WHERE
CALCODE.CALCODE_ID = CALRULE.CALCODE_ID AND CALRULE.CALRULE_ID = SHPJCRULE.CALRULE_ID
AND CALRULE.CALRULE_ID = CRULESCALE.CALRULE_ID AND CRULESCALE.CALSCALE_ID = CALSCALE.CALSCALE_ID AND
CALRANGE.CALSCALE_ID =CALSCALE.CALSCALE_ID AND CALRANGE.CALRANGE_ID = CALRLOOKUP.CALRANGE_ID
AND CALCODE.CALCODE_ID = CATENCALCD.CALCODE_ID AND CATENCALCD.catentry_id IN
-- replace partnumber with the actual value
(select catentry_id from catentry where partnumber='XXXXXXX')
order by FIELD1,JURSTGROUP_ID,SHIPMODE_ID;

Ref : Info-center

Leave a Reply