CA Service Management

I'm trying to list out the forms linked to a particular service offerings, Can i do it using an SQL query?

Hello Dharma

The information you need is in this knowledge document:

https://comm.support.ca.com/kb/how-can-i-use-a-sql-query-to-find-the-details-of-the-forms-which-are-associated-with-a-particular-request/kb000010278

Steve

Shown below is the inline code we use in PAM to get the raw data.

DECLARE @REQUESTID nvarchar(15) = ?;

SELECT s1.request_id AS RequestID
, s1.id AS RequestItemID
, s1.group_id AS groupID
, s1.sd_row AS sdRowID
, s1.offering_id AS offeringID
, s1.item_id AS RateItemID
, s1.status AS soe_status
, rd.item_text AS soe_name
, rd.code AS soe_code
, rd.external_id AS externalID
, rd.category
, rd.category_class
, rd.category_subclass
, s2.numeric_1 AS itemqty
, CAST(s2.numeric_2 AS decimal(10, 2)) AS itemcost
, CAST(s2.numeric_1 * s2.numeric_2 AS decimal(10, 2)) AS linecost
, s3.id AS formID
FROM dbo.usm_subscription_detail AS s1 INNER JOIN
dbo.usm_rate_definition AS rd ON s1.item_id = rd.item_id AND s1.item_id = rd.item_id LEFT OUTER JOIN
dbo.usm_subscription_detail AS s3 ON s1.sd_row = s3.sd_row AND s1.group_id = s3.group_id AND s1.request_id = s3.request_id AND
s3.subscription_type = 5 LEFT OUTER JOIN
dbo.usm_subscription_detail AS s2 ON s1.request_id = s2.request_id AND s1.group_id = s2.group_id AND s1.sd_row = s2.sd_row AND s2.numeric_1 > 0
WHERE (s1.request_id = @REQUESTID) AND (rd.rate_col = 0)
ORDER BY s1.group_id,s1.sd_row