CA Service Management

I am working on creating a service catalog request using SOAP web Service.

Here is the design of the request definition, it has handful of form elements and four tables on the form. 

1. what are the underneath sql tables which stores the information of the request.
2. what is the preferred web service method is used to create a the service request. if required to make more that one web service call what are all the methods to use in sequence.
3. any example of would help me in my implementation.

- Venkat

Good Afternoon Venkat.

Could this be of help?

==============================================
select o.offering_id,o.offering_name,
i.parent_id,i.rate_plan_id,
rd.item_id,rd.rate_plan_id,rd.item_text,rd.item_type,rd.text_1
from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
where o.offering_name like '%key%'
and i.parent_id = o.offering_id
and rd.rate_plan_id = i.rate_plan_id
and rd.item_type = 14
==============================================
select distinct
o.offering_id,o.parent_id,
rd.text_1,
fe1.form_entity_id,fe1.form_entity_name,fe1.form_entity_type,
fca.attr_value

FROM
usm_offering o,
usm_rate_definition rd,
usm_offering_ratedef_inclusion i,
usm_form_entities fe1,
usm_form_entities fe2,
usm_form_component_attributes fca

where
o.offering_name = '6-TextAreaNewLine' and
i.parent_id = o.offering_id and
rd.rate_plan_id = i.rate_plan_id and
rd.item_type = 14 and
fe1.form_entity_parent_id = rd.text_1 and
fe1.form_entity_parent_id = fe2.form_entity_id and
fe1.form_entity_id = fca.form_comp_id and
fca.attr_name = '_id'

order by fe1.form_entity_type
==============================================
<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://services.soap.usm.ca.com">
<soapenv:Header/>
<soapenv:Body>
<ser:addRequestWithForm soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<sessionID xsi:type="xsd:string">f69439893c0138520336e847e1b92454697d2d1e</sessionID>
<requestData xsi:type="xsd:string">name:keySupport!req_by_user_id:spadmin!req_for_user_id:amelo01</requestData>
<requestValuesData xsi:type="xsd:string">project:ProjectCase802132</requestValuesData>
<requestValuesDataType xsi:type="xsd:string"></requestValuesDataType>
<addedOfferings xsi:type="xsd:string"></addedOfferings>
<addedItems xsi:type="xsd:string">offering_id:10138|rate_plan_id:10120|item_id:12033</addedItems>
<formValuesData xsi:type="xsd:string">offering_id:10138|rate_plan_id:10120|item_id:12033|eSamPublisher:Scooter Software|eSamProduct:Beyond Compare Standard|eSamVersion:4 (4.1)|eSamRPCID:3PC0000134695-PR0000028499|eSamMFPNumber:99|eSamMetric:oo|eSamGSPCID:163660199|eSamQty:1|eSamSID:V024866|eSamMachine:W63ZDXQ1|eSameRequestSys:DevXChange|eSamTickAssoc:121|eSamWorkflow:2996648</formValuesData>
</ser:addRequestWithForm>
</soapenv:Body>
</soapenv:Envelope>
========================

Kind regards, Louis.

Good Afternoon Venkat.

And this example perhaps:

SOAPUI: http://HOST:PORT/usm/services/RequestService?wsdl
Login_ID: 8dc7b70b-d514-4fe8-aa8c-cd316ac8e1b6

1) Create a empty request
<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://services.soap.usm.ca.com">
<soapenv:Header/>
<soapenv:Body>
<ser:saveRequest soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<sessionID xsi:type="xsd:string">8dc7b70b-d514-4fe8-aa8c-cd316ac8e1b6</sessionID>
<requestData xsi:type="xsd:string">name: ABAKUS: REQ0030198 - RITM0042146|created_date:2017-07-31 04:25:56|desired_date:|priority:4|req_by_user_id:spadmin|req_for_user_id:amelo01|comments:ServiceNow SYS_ID:CAT001371</requestData>
<requestValuesData xsi:type="xsd:string">authorization_number:RITM0042146|project:CAT001371|gl_code:88888|cost_center:(set on the form)|ship_to_address1:N/A|ship_to_city:N/A|ship_to_state:N/A|ship_to_postal:N/A|ship_to_country:N/A</requestValuesData>
<requestValuesDataType xsi:type="xsd:string"></requestValuesDataType>
<addedOfferings xsi:type="xsd:string"></addedOfferings>
<removedOfferings xsi:type="xsd:string"></removedOfferings>
<addedItems xsi:type="xsd:string"></addedItems>
</ser:saveRequest>
</soapenv:Body>
</soapenv:Envelope>
Result: <requestID xsi:type="xsd:int">10167</requestID>

2) Save the service into the request
SELECT o.offering_id, o.offering_name,
rp.rate_plan_id, rp.status,rp.date_available, rp.is_inherited, rp.total_rows, rp.total_cols,rp.domain,rp.sort_order_no,rp.rate_plan_name,rp.description,
ori.id as iid, ori.layout as layout
FROM usm_offering o, usm_rate_plan rp,usm_offering_rplan_inclusion ori
WHERE o.offering_name = 'SOAP-Offering'
AND ori.parent_id=o.offering_id
AND rp.rate_plan_id=ori.child_id
AND rp.status in (1,6)
Result: 10248 SOAP-Offering 10171 ~ Bestellung von Services

<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://services.soap.usm.ca.com">
<soapenv:Header/>
<soapenv:Body>
<ser:saveRequestItems soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<sessionID xsi:type="xsd:string">8dc7b70b-d514-4fe8-aa8c-cd316ac8e1b6</sessionID>
<requestID xsi:type="xsd:int">10167</requestID>
<addedOfferings xsi:type="xsd:string">offering_id:10248</addedOfferings>
<removedOfferings xsi:type="xsd:string"></removedOfferings>
<addedItems xsi:type="xsd:string"></addedItems>
</ser:saveRequestItems>
</soapenv:Body>
</soapenv:Envelope>

3) Populate the attached form
select id from usm_subscription_detail where request_id=10167 and subscription_type=5
Result: 10703

<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://services.soap.usm.ca.com">
<soapenv:Header/>
<soapenv:Body>
<ser:saveRequestForm soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<sessionID xsi:type="xsd:string">8dc7b70b-d514-4fe8-aa8c-cd316ac8e1b6</sessionID>
<subscriptionDetailID xsi:type="xsd:int">10703</subscriptionDetailID>
<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:amelo01@maildomain.com|first_name:Louis|last_name:Amelsfort|userid:amelo01</formValuesData>
</ser:saveRequestForm>
</soapenv:Body>
</soapenv:Envelope>
Result: NO error.

With:
<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:amelo01@maildomain.com|first_name:Louis|last_name:Amelsfort|userid:amelo01</formValuesData>
It works okay.

I then added "|FromDate:" and get the below error:
<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:amelo01@maildomain.com|first_name:Louis|last_name:Amelsfort|userid:amelo01|FromDate:</formValuesData>
Result: Error in view.log:
2017/08/04 18.15.41.640 DEBUG [http-bio-8080-exec-11] [RequestServiceImpl] propsFormData :{userid=H715000, first_name=Firstname, lokation_haus=CT01, befrBisPflicht=, email_address=First.Last@email.com, hiddenFields=;hiddenFields;FromDate;ToDate;ServiceCode;befrBisPflicht;befrVonPflicht;Beschreibung_pflicht;PC;, FromDate=, lokation_lokation=Streetname 13 (enviaM), ServiceCode=SN(CAT001371);acc#, Account=0b4cd065f6cdba4f9254cbe52421aaba|H715000, lokation_raum=302, befrVonPflicht=, befrBis=, ToDate=, last_name=Lastname, pri_phone_number=9932431, Beschreibung= Anfragetyp: Takethisone, befrVon=}
2017/08/04 18.15.41.641 ERROR [http-bio-8080-exec-11] [RequestServiceImpl] SOAPREQ010
java.lang.NullPointerException

<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:amelo01@maildomain.com|first_name:Louis|last_name:Amelsfort|userid:amelo01|FromDate:08/04/2017 0:0:0</formValuesData>
"|FromDate:08/04/2017 0:0:0", all runs okay again.

<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:amelo01@maildomain.com|first_name:Louis|last_name:Amelsfort|userid:amelo01|FromDate:08/04/2017 0:0:0|befrVon:08/04/2017 0:0:0|befrVonPflicht:|ToDate:08/04/2017 0:0:0|befrBis:08/04/2017 0:0:0|befrBisPflicht:|lokation_lokation:Streetname 13 (enviaM)|lokation_raum:302|lokation_haus:CT01|Beschreibung: Anfragetyp: Takethisone|ServiceCode:SN(CAT001371);acc#|Account:0b4cd065f6cdba4f9254cbe52421aaba~H715000|hiddenFields:;hiddenFields;FromDate;ToDate;ServiceCode;befrBisPflicht;befrVonPflicht;Beschreibung_pflicht;PC;</formValuesData>
>>ERROR:
<formValuesData xsi:type="xsd:string">pri_phone_number:9932431|email_address:amelo01@maildomain.com|first_name:Louis|last_name:Amelsfort|userid:amelo01|FromDate:08/04/2017 0:0:0|befrVon:08/04/2017 0:0:0|befrVonPflicht:08/04/2017 0:0:0|ToDate:08/04/2017 0:0:0|befrBis:08/04/2017 0:0:0|befrBisPflicht:08/04/2017 0:0:0|lokation_lokation:Streetname 13 (enviaM)|lokation_raum:302|lokation_haus:CT01|Beschreibung: Anfragetyp: Takethisone|ServiceCode:SN(CAT001371);acc#|Account:0b4cd065f6cdba4f9254cbe52421aaba~H715000|hiddenFields:;hiddenFields;FromDate;ToDate;ServiceCode;befrBisPflicht;befrVonPflicht;Beschreibung_pflicht;PC;</formValuesData>

Date fields may not be empty. All 'date' fields have been given a value. 
The date value must be composed of date & time (08/04/2017 0:0:0)

Kind regards, Louis.