Thank you Ian...
the initial query I am looking to display would be something like this. (I am still trying to rap my head around what the Service Catalog Tables are as I can't seem to find any documentation regarding the tables/fields). the query below basically list all requests and the associated SDM ticket (if it exist) and to whom it was delegated to (if exist), status and other information... It would be perfect if I could just display this list based on a user (meaning, list only those requests that is/are assigned to a user/group that he/she belongs to)... then, a bonus would be if we can have hyperlinks to the requets/sdm request fields to re-direct them to the actual requests in SC and SDM
select u.request_id, a.ref_num As SDM_Ticket, b.user_id As DelegatedTo, u.name,
CASE
WHEN u.status = '2' THEN 'Completed'
WHEN u.status = '4' THEN 'Cancelled'
WHEN u.status = '200' THEN 'Submitted'
WHEN u.status = '400' THEN 'Pending Approval'
WHEN u.status = '502' THEN 'Pending Allocation'
WHEN u.status = '800' THEN 'Approved'
WHEN u.status = '1000' THEN 'Pending Fullfillment'
WHEN u.status = '1100' THEN 'Processing Request'
ELSE 'Status Unknown'
END AS Status,
u.created_date, u.completion_date, u.modified_date,
c.first_name+' '+c.last_name as RequestedFor,
d.first_name+' '+d.last_name as RequestedBy
from usm_request u
LEFT JOIN usm_link_ticket_request_item a on a.request_id = u.request_id
LEFT JOIN usm_request_pending_action b on b.request_id = u.request_id and b.status=5
LEFT JOIN ca_contact c on c.userid = u.req_for_user_id
LEFT JOIN ca_contact d on d.userid = u.req_by_user_id
where u.status <> 0
order by u.status
Thanks again.
br
ken