WCS Catalog Queries

-- 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;