WCS Payments Query

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

-- Get list of payments associated by SUBMITTED order id
SELECT C.PPCPAYINST_ID,A.RESPONSECODE,A.TRACKINGID,A.REFERENCENUMBER,E.EDPPAYINST_ID
FROM PPCPAYTRAN A,PPCPAYMENT B,PPCPAYINST C,EDPORDER D,EDPPAYINST E
WHERE B.PPCPAYINST_ID = C.PPCPAYINST_ID and A.PPCPAYTRAN_ID = B.PPCPAYTRAN_ID
AND C.STATE = 1 AND C.ORDER_ID = '<ORDER_ID>' AND D.ORDER_ID = '<ORDER_ID>'
AND E.MARKFORDELETE = 0  AND D.EDPORDER_ID = E.EDPORDER_ID;

-- Get a list of payments data associated with the current pending cart by login id
SELECT * FROM PPCEXTDATA a, PPCPAYINST b, ORDERS c
  WHERE c.orders_id = b.order_id
  AND a.PPCPAYINST_id = b.PPCPAYINST_id
  AND c.orders_id in 
(select orders_id from orders where member_id in (select users_id from userreg where logonid like '%<LOGIN_ID>%') and status='P');

-- Get a list of payments data associated with the current pending cart by login id
SELECT * FROM PPCEXTDATA a, PPCPAYINST b, ORDERS c
  WHERE c.orders_id = b.order_id
  AND a.PPCPAYINST_id = b.PPCPAYINST_id
  AND c.orders_id in 
(select orders_id from orders where member_id in (select users_id from userreg where logonid like '%<LOGIN_ID>%') and status='P')

-- Get a list of payments data by order id
SELECT * FROM PPCEXTDATA a, PPCPAYINST b, ORDERS c
  WHERE c.orders_id = b.order_id
  AND a.PPCPAYINST_id = b.PPCPAYINST_id
  AND c.orders_id in ('<ORDER_ID>');