WCS Order Query

-- IBM documentation on WCS Order data model
-- http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.data.doc/refs/rdmorder.htm

-- Get user cart by the login id, replace USER_LOGIN_ID with the actual user login id
select * from orders where member_id in
(select USERS_ID from userreg where lower(logonid) = lower('USER_LOGIN_ID'))
and status='P';

-- Modified version of the previous query for LDAP enabled WCS instance
select * from orders where member_id in
(select USERS_ID from userreg where lower(logonid) like lower('%USER_LOGIN_ID%'))
and status='P';

-- Delete pending cart by user id, replace USER_LOGIN_ID with the login id
-- Delete on Orders does not delete any of the EDPORDER related tables and hence we need to delete from both ORDERS and EDPORDER.
delete from orders o
where member_id in (select users_id from userreg where logonid like '%USER_LOGIN_ID%')
and o.hstatus='P';

-- Check user cart with atleast one or more items
select * from (select count(1) as "Order line count", orders_id  as 
"Order ID" from orderitems where orders_id in (select orders_id from orders a
where member_id in (select users_id from userreg where logonid like '%<USER_ID>%' )
and a.status = 'P') 
group by orders_id
having count (1)> =1
order by count(1) desc);


-- Orders locked by sales center agent
select * from orders where editor_id is not null;