Websphere Commerce - Data Model - Trading Subsystem - Contract

Click and Set the zoom level.

Websphere Commerce - Data Model - Trading Subsystem - Auction

Click and set the zoom level.

Websphere Commerce - Data Model - Payment

Websphere Commerce - Management Center - Marketing Data Models

Click and set the zoom level.

Email Activity Data Model

Websphere Commerce - Data Model - Access Control

Click and set the zoom level.

 

Websphere Commerce - Data Model - Authentication

Click and set the zoom level

Websphere Commerce - Catalog Subsystem - Data Model - Big Picture

Click the picture and set the zoom level.

Websphere Commerce - Order Subsystem - Data Model - Big Picture

Click Picture to set the zoom level.

Subscribe to WebSphere Commerce News and Posts.
Get latest updates and posts on IBM WCS from Buggybread.com
Enter your email address:

Websphere Commerce - DB Schema - Promotions

Websphere Commerce - DB Schema - Attribute

Websphere Commerce - DB Schema - USER

Websphere Commerce - MEMBER / ROLE Relationship

IBM WCS ( Websphere Commerce ) - SQLs to retrieve Attributes

Problem

Retrieve color of all kitchen appliances along with their name.

Assumption

1. There is catentry type for Kitchen Appliances and all kitchen appliances are mapped with this type.
2. Color has been stored as an attribute. 

Tables Referenced 

catenttype -> catentry -> attr -> attrdesc -> catentryattr -> attrval -> attrvaldesc

1. catenttype -> Catalog Entry Type Master table
2. catentry -> Catalog Entry Table
3. attr -> Attribute
4. attrdesc -> Attribute Description
5. catentryattr -> Relationship between catentry, attribute and attribute value.
6. attrval -> Attribute Value
7. attrvaldesc -> Attribute Value Desciption

Solution 

1. Get catentry type id for "Kitchen Appliances"

select catenttype_id from catenttype where description = 'Kitchen Applicances';

2. Get catentries belonging to this catentry type

select catentry_id from catentry where catenttype_id = <Retrieved above>

3. Get Attr id for attribute color

select attr_id from attrdesc where Name='Color'

4. Get attr value ids for color attribute for these catentries Ids

select attrval_id from catentryattr where catentry_id in ( <Retrieved  in 2 >) and attr_id=<Retrieved in 3>

5. Get attr value name for these attrval ids

Select value from attrvaldesc where attrval_id in < Retrieved in 5 >

IBM WCS - Promotions - SQL to check the promotion xmls

select * from PX_PROMOTION where rownum < 2 order by PX_PROMOTION_ID Desc;

will return the complete promotion record for last created promotion - 

select XMLPARAM from PX_PROMOTION where rownum < 2 order by PX_PROMOTION_ID Desc;

will return the xml only for the last created promotion


select * from PX_PROMOTION where PX_PROMOTION_ID=1234;

will return the promotion record for a respective promotion Id. 


 

IBM WCS - DB Schema - PPC

IBM WCS - DB Schema - Orders

IBM WCS - DB Schema - EDP

IBM WCS - DB Schema - Calculation

IBM WCS ( Websphere Commerce Suite ) - DB Views and their definition

View AUCTBIDVW

SELECT t1.auct_id, t1.refcode, t1.autype, t1.austatus, t1.auquant, t1.quantscale, t1.resrvprice, t1.openprice, t1.currprice, t1.currquant, t1.closepr, t1.currency, t1.paymethods, t1.closetype, t1.rulepage, t1.itempage, t1.starttime, t1.endtime, t1.realendtime, t1.duration, t1.deposit, t1.bestbid_id, t1.highbid_id, t1.lockflag, t1.updatetime, t1.lastbktime, t1.precedence, t1.supplier_id, t2.bid_id, t2.refcode, t2.bidquant, t2.bidprice, t2.bidtime, t2.winopt, t2.bidstatus, t2.paytype, t2.encrypt, t2.device, t2.expdate, t2.autobid_id, t2.bidroot, t2.bkname, t2.shipto_id, t2.shipmode, t2.winprice, t2.winquant, t2.bidmsg, t2.admin_id, t2.owner_id, t2.store_id, t2.field2, t2.field1, t2.field3 FROM auctview t1, bid t2 WHERE T1.AUCT_ID = T2.AUCT_ID AND T1.STORE_ID = T2.STORE_ID

View AUCTVIEW

SELECT t1.refcode, t1.autype, t1.austatus, t1.auquant, t1.quantscale, t1.resrvprice, t1.openprice, t1.currprice, t1.currquant, t1.closepr, t1.currency, t1.paymethods, t1.closetype, t1.rulepage, t1.starttime, t1.endtime, t1.realendtime, t1.duration, t1.deposit, t1.bestbid_id, t1.highbid_id, t1.lockflag, t1.updatetime, t1.lastbktime, t1.precedence, t1.supplier_id, t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6, t2.itempage, t1.auct_id, t1.retract_bid, t1.admin_id, t1.owner_id, t1.ffmcenter_id, t1.catentry_id, t1.bidrule_id, t1.mbrgrp_id, t2.store_id, t1.durdays, t1.refprice, t1.state, t1.invrsrvid, t1.aucpayinfo_id, t1.aucmode, t1.pricelimit, t1.quantlimit, t1.durlength FROM auction t1, auctstrrel t2 WHERE T1.AUCT_ID = T2.AUCT_ID

View BIDPAYSUM
SELECT bidpayment.type, SUM(AMOUNT) FROM bidpayment GROUP BY BID_ID, TYPE

View CATPRDREL
select C1.CATGROUP_ID, C1.CATALOG_ID, C1.CATENTRY_ID, C1.RULE, C1.SEQUENCE, C1.LASTUPDATE, c2.member_id, c2.partnumber, c3.identifier from catgpenrel c1, catentry c2, catgroup c3 where c1.catentry_id = c2.catentry_id and c1.catgroup_id = c3.catgroup_id and c2.member_id = c3.member_id

View INVITMVW
SELECT itemffmctr.itemspc_id, rcptitmvw.receiptquantity - SUM(qtyreserved + qtyallocbackorder) as qtyavailable FROM rcptitmvw, itemffmctr WHERE rcptitmvw.itemspc_id = itemffmctr.itemspc_id GROUP BY itemffmctr.itemspc_id, rcptitmvw.receiptquantity

View INVSTFFMVW
SELECT itemffmctr.store_id, itemffmctr.ffmcenter_id, itemffmctr.itemspc_id, rcptstffvw.receiptquantity - (qtyreserved + qtyallocbackorder) as qtyavailable FROM rcptstffvw, itemffmctr WHERE rcptstffvw.itemspc_id = itemffmctr.itemspc_id AND rcptstffvw.store_id = itemffmctr.store_id AND rcptstffvw.ffmcenter_id = itemffmctr.ffmcenter_id

View INVSTVW

SELECT itemffmctr.store_id, itemffmctr.itemspc_id, rcptstvw.receiptquantity - sum(qtyreserved + qtyallocbackorder) as qtyavailable, baseitem.quantitymeasure FROM rcptstvw, itemspc, itemffmctr, baseitem WHERE rcptstvw.itemspc_id = itemspc.itemspc_id AND itemspc.baseitem_id = baseitem.baseitem_id AND itemffmctr.itemspc_id = itemspc.itemspc_id AND itemffmctr.store_id = rcptstvw.store_id GROUP BY itemffmctr.store_id, itemffmctr.itemspc_id, rcptstvw.receiptquantity, baseitem.quantitymeasure

View MSALESVW

SELECT storeent.storeent_id as storeent_id, nvl( sum( totalproduct + totalshipping + totaltax + totaltaxshipping + totaladjustment),0) as grosssales, count( orders.orders_id) as totalorders, currency as currency FROM storeent, orders where orders.storeent_id(+) = storeent.storeent_id and orders.status in ('M','A','B','C','F','G','R','S','D') and orders.lastupdate >= add_months(current_timestamp,-1) group by storeent.storeent_id,currency

View MSGCOUNTVW
SELECT msgview.recipient_id, msgview.status, COUNT(MESSAGE_ID) FROM msgview GROUP BY RECIPIENT_ID, STATUS

View MSGVIEW
SELECT t1.message_id, t1.member_id, t1.msgtype, t1.target_id, t1.subject, t1.posttime, t1.content, t1.delay, t1.base, t1.msgparam1, t1.msgparam2, t1.msgparam3, t2.member_id, t2.status, t2.sendstat FROM message t1, msgmemrel t2 WHERE T1.MESSAGE_ID = T2.MESSAGE_ID

View MUSRTRVW
SELECT storeent.storeent_id as storeent_id, count(usrtraffic.usrtraffic_id) as totalvisits, count(distinct usrtraffic.sessionid) as totalsessions, count(distinct usrtraffic.users_id) as distinctusers from storeent, usrtraffic where usrtraffic.store_id(+) = storeent.storeent_id and usrtraffic.SHLUSTMP >= add_months(current_timestamp,-1) group by storeent.storeent_id

View PRATRSTATR
select c1.catentry_id as attr_product_id, c1.attribute_id as attr_id, c1.language_id as lang_id, c1.name as attr_name, c1.attrtype_id as type_id, c2.partnumber as attr_partnumber from attribute c1, catentry c2 where c1.catentry_id = c2.catentry_id(+)

View PRCEOFFPRD
select c1.offer_id as id, c2.partnumber as partnumber from offer c1 , catentry c2 where c1.catentry_id = c2.catentry_id(+)

View PRCOFFRPRC
select c1.offer_id as id, c2.currency as currency, c2.price as price, c2.compareprice as compareprice from offer c1 , offerprice c2 where c1.offer_id = c2.offer_id(+)

View PRDATRAVAL

select c1.attribute_id as attribute_id, c1.attrtype_id as attrtype_id, c1.attrvalue_id as attrvalue_id, c1.catentry_id as catentry_id, c1.field1 as field1, c1.field2 as field2, c1.field3 as field3, c1.floatvalue as floatvalue, c1.image1 as image1, c1.image2 as image2, c1.integervalue as integervalue, c1.language_id as language_id, c1.name as name, c1.oid as oid, c1.sequence as sequence, c1.stringvalue as stringvalue, c2.partnumber as partnumber from attrvalue c1, catentry c2 where c1.catentry_id = c2.catentry_id(+)

View PRODATR
select c1.attr_name, c1.attr_partnumber, c1.attr_product_id, C2.ATTRIBUTE_ID, C2.ATTRTYPE_ID, C2.ATTRVALUE_ID, C2.CATENTRY_ID, C2.FIELD1, C2.FIELD2, C2.FIELD3, C2.FLOATVALUE, C2.IMAGE1, C2.IMAGE2, C2.INTEGERVALUE, C2.LANGUAGE_ID, C2.NAME, C2.OID, C2.SEQUENCE, C2.STRINGVALUE, C2.PARTNUMBER FROM PRATRstatr c1, PRDATRaval c2 where c1.attr_id = c2.attribute_id AND c1.type_id = c2.attrtype_id

View RCPTITMVW
SELECT itemspc_id, SUM(receipt.qtyonhand-receipt.qtyinprocess) as receiptquantity FROM receipt, versionspc WHERE receipt.versionspc_id = versionspc.versionspc_id GROUP BY versionspc.itemspc_id

View RCPTSTFFVW
SELECT itemspc_id, store_id, ffmcenter_id, SUM(receipt.qtyonhand-receipt.qtyinprocess) as receiptquantity FROM receipt, versionspc WHERE receipt.versionspc_id = versionspc.versionspc_id GROUP BY versionspc.itemspc_id, receipt.store_id, receipt.ffmcenter_id

View RCPTSTVW
SELECT itemspc_id, store_id, SUM(receipt.qtyonhand-receipt.qtyinprocess) as receiptquantity FROM receipt, versionspc WHERE receipt.versionspc_id = versionspc.versionspc_id GROUP BY versionspc.itemspc_id, receipt.store_id

View SETSTATUS

SELECT paystatus.setsbrandid, paystatus.setspreqstmp, paystatus.setstimestmp, paystatus.setsstatcode, paystatus.setsnextstat, paystatus.setstimeexp, paystatus.setsfailtype, paystatus.setsfailcode, paystatus.setscurr, paystatus.setscurrexp, paystatus.setsactamt, paystatus.setsrespreason, paystatus.setsauthcode, paystatus.setsauthcharind, paystatus.setsapprovalcode, paystatus.setsauthretnum, paystatus.setslogrefid, paystatus.setspan, paystatus.setscardexpiry, paystatus.setsauthrevcode, paystatus.setscapcode, paystatus.setscaprevcode, paystatus.setscredcode, paystatus.setscredrevcode, paystatus.setsavsresult, paystatus.setsauthamt, paystatus.setscapamt, paystatus.setssettleamt, paystatus.setscredamt, paystatus.setswakeupmsg, paystatus.setsfailcode2, paystatus.setsornbr, paystatus.pending, paystatus.profilename, paystatus.store_id FROM paystatus

View STOREINV

SELECT STORE_ID, CATENTRY_ID, SUM(QUANTITY) AS STOREQUANTITY, QUANTITYMEASURE, COUNT(*) AS C5 FROM INVENTORY GROUP BY STORE_ID, CATENTRY_ID, QUANTITYMEASURE View WSALESVW SELECT storeent.storeent_id as storeent_id, nvl( sum( totalproduct + totalshipping + totaltax + totaltaxshipping + totaladjustment),0) as grosssales, count( orders.orders_id) as totalorders, currency as currency FROM storeent , orders where orders.storeent_id(+) = storeent.storeent_id and orders.status in ('M','A','B','C','F','G','R','S','D') and orders.lastupdate >= (current_timestamp-7) group by storeent.storeent_id,currency View WUSRTRVW SELECT storeent.storeent_id as storeent_id, count(usrtraffic.usrtraffic_id) as totalvisits, count(distinct usrtraffic.sessionid) as totalsessions, count(distinct usrtraffic.users_id) as distinctusers from storeent, usrtraffic where usrtraffic.store_id(+) = storeent.storeent_id and usrtraffic.SHLUSTMP >= (current_timestamp-7) group by storeent.storeent_id

IBM WCS ( Websphere Commerce Suite ) - DB Views and their referenced Tables / Views

View Name Referenced Tables / Views AUCTBIDVW AUCTVIEW , BID   AUCTVIEW AUCTION , AUCTSTRREL   BIDPAYSUM BIDPAYMENT   CATPRDREL CATENTRY , CATGPENREL , CATGROUP   INVITMVW ITEMFFMCTR , RCPTITMVW   INVSTFFMVW ITEMFFMCTR , RCPTSTFFVW   INVSTVW BASEITEM , ITEMFFMCTR , ITEMSPC  ,RCPTSTVW   MSALESVW ORDERS , STOREENT   MSGCOUNTVW MSGVIEW   MSGVIEW MESSAGE , MSGMEMREL   MUSRTRVW STOREENT , USRTRAFFIC   PRATRSTATR ATTRIBUTE , CATENTRY   PRCEOFFPRD CATENTRY , OFFER   PRCOFFRPRC OFFER , OFFERPRICE   PRDATRAVAL ATTRVALUE , CATENTRY   PRODATR PRATRSTATR , PRDATRAVAL  RCPTITMVW RECEIPT , VERSIONSPC   RCPTSTFFVW RECEIPT , VERSIONSPC   RCPTSTVW RECEIPT , VERSIONSPC   SETSTATUS PAYSTATUS   STOREINV INVENTORY   WSALESVW ORDERS , STOREENT   WUSRTRVW STOREENT , USRTRAFFIC
DB View Definition - http://techsharepoint.blogspot.com/2012/10/ibm-wcs-websphere-commerce-suite-db_5.html