Clarity

Is there a way to find out the next value in the autonum sequence for a sub-object? I'd like to call that from my custom procedure in Oracle.

I tried using this command CMN_ID_SP('APRP', V_ID), but it is returning the value for the PRNOTE autonum sequence instead of the sequence for my sub-object.

Thanks in advance for any helpful tips!

Check these -

99545304

10045115

99772328

NJ

Try

SELECT OBJECT_CODE
,ATTRIBUTE_CODE
,NEXT_VALUE
FROM niku.CMN_AUTONUM_SCHEMES.
WHERE OBJEC_CODE = '<the code of your custom object>'


Martti K.

EDIT so it should be
WHERE OBJECT_CODE = '<the code of your custom object>'

^ slight typo in that SQL.

--

The stored procedure cmn_autonum_get_next_sp is how the app gets many of its sequences (and this procedure updates that table that Martti referenced).

Some discussion here ; XOG-in and auto-numbering attributes.

HOWEVER I would be very cautious about using that SP ; the only "legitimate" usage I can think of is calling that SP to get an ID that you then use later in a XOG write call (and the links NJ posted are discussions around how to get XOG to do this for you automatically) - for example, if you are asking this question because you are intending to create sub-object instances directly on the database then please DON'T do this as it is unsupported and will likely "break" Clarity. :what:

Agree with the risks. Custom procedure does not sound good.
If there are risks using a Clarity SP which updates this and that, wouldn't the risk be lesser with a custom SP based on the query?

Martti K.

Hi NJ, Martti, and Dave,

Thank you very much for your replies!

Just to give you some background info on my question....
* We are tracking applications in Clarity and some applications are parents of other applications.
* We require all of our applications to review their recovery plans on an annual basis....this is done in the recovery plan sub-object.
* Each application needs to have a recovery plan review record.
* Currently, when the parent application reviews the recovery plan, they are manually creating a recovery plan review record for themself and for each of their children applications.
* To save time we are trying to create a process in Clarity that would create a record for the children applications right after the parent application creates their recovery plan review record.
* In order to insert a record into the sub-object I'm thinking I need to figure out how to get the next auto-number in the sub-object's sequence.....................should I be going down a different path?

Sorry...not sure if this is too much info...

thanks,
christine

Would need some time to digest that.
The concerns were about how you would create the new records in a supported manner.
If the is going to be recovery, it'll be the new recovery plan review record you'll creating not the applications right?
Why would you need predefined ID's? If autonumbering with XOGin does not work why can't you just generate the ID's with a formula. Is there a problem if you skip one ID?.

Martti K.

Hi Martti,

Thanks for your reply.

A new recovery plan record would be created on a child's recovery plan sub-object. I forgot to mention that the child's record would use the same values as the parent's recovery plan review record.

It's not a problem to skip IDs....I just thought it might be good to use the next available number.

Sorry for the dumb question, but Is it possible to have a step in a process XOG in a record?

thanks,
christine

christine.takeuchi wrote:

* In order to insert a record into the sub-object I'm thinking I need to figure out how to get the next auto-number in the sub-object's sequence...........

Its the word "insert" that worries us.... this sounds like you are complemplating a direct database insert here, and that is what we are saying is a "bad thing". :what:

christine.takeuchi wrote:

..........should I be going down a different path?

Slightly, yes! :-)

christine.takeuchi wrote:

....but Is it possible to have a step in a process XOG in a record?

Yes - you have a 'custom action' as the step in the process, and the custom action is a GEL script ; from which you can (within reason) do most things (for example XOG data into Clarity).

However you don't need* to embed XOG in a GEL script / process - you can call XOG from any client language that supports calls to web-services; for example an Oracle PL/SQL stored procedure (or the SQL*Server equivalent).

* - But since you want the action to be intiated by a user-initiated event "in" Clarity then a process is the right thing.

--

Just to be clear, the only thing that I am saying to be cautious about is the implied direct-database insert of the sub-object records ; I am really just saying that that sub-object record creation should be done through a XOG call.

Hi Dave,

Thank you for your feedback. As you can see....I have a lot to learn......thank you for your patience with my questions.

I will explore having a process step XOG in the data.


thanks for your help!
christine

Hi,

I am pursing the method of trying to have a custom script XOG in the new record for the sub-object. I can get the XOG portion of the script to run fine in the xog.client. However when I try to run the process it fails at the step that calls my custom script to do the XOG. The process just says it's erroring out on that step, but doesn't provide any details on what's causing the error.

Is there any type of alert I could add to the script that would make the system return more details on why the script is abending?

At first I thought it didn't like the format of the date entered for the date_completed field in the XOG portion, but I hard coded 2013-04-19 and it still abends....so I'm not sure what the cause is.

Here's a copy of my custom script.
--------------------------------------------
<gel:script
xmlns:x="jelly:xml"
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql"
xmlns:xog="http://www.niku.com/xog"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<gel:setDataSource dbId="Niku"/>
<gel:parameter default="http://yourserverhere" var="XOGURL"/>
<gel:parameter var="XOGusername" default="yourusernamehere"/>
<gel:parameter var="XOGpassword" default="yourpasswordhere"/>

<!-- ================================================= -->
<!-- Here is my sql grab...-->
<!-- ================================================= -->
<sql:query var="groupmembers">

SELECT APP.ID

, APP.APPLICATION_NAME

, APP.APPLICATION_CODE

, APP.DESCRIPTION

, APP.DR_TEST_GROUP_FOR_FILTER

, APP.DR_TEST_GROUP_DESC

FROM Z_BOH_ALL_ACTIVE_APPL_WF APP

WHERE APP.DR_TEST_GROUP_FOR_FILTER = ${gel_objectInstanceId}
</sql:query>
<sql:query var="parentinfo">

SELECT RP.CREATED_DATE

, RP.LAST_UPDATED_DATE

, RP.NAME RECORD_NAME

, RP.CODE

, RP.ODF_PARENT_ID

, RP.TYPE

, RP.DATE_COMPLETED
, EXTRACT(month from RP.DATE_COMPLETED) date_completed_month
, EXTRACT(day from RP.DATE_COMPLETED) date_completed_day
, EXTRACT(year from RP.DATE_COMPLETED) date_completed_year


FROM ODF_CA_APPL_RECOV_PLAN_FORM RP

,( SELECT MAX(CREATED_DATE) MAX_CREATED_DATE

FROM ODF_CA_APPL_RECOV_PLAN_FORM

WHERE ODF_PARENT_ID = ${gel_objectInstanceId}

AND TYPE = 'arp'

) LAST_REC_CREATED

WHERE RP.ODF_PARENT_ID = ${gel_objectInstanceId}

AND RP.CREATED_DATE = LAST_REC_CREATED.MAX_CREATED_DATE
</sql:query>

<soap:invoke endpoint="${XOGURL}/niku/xog" var="xog_login">
<soap:message>
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soap-env:Body>
<xog:Login xmlns="http://www.niku.com/xog">
<xog:Username>${XOGusername}</xog:Username>
<xog:Password>${XOGpassword}</xog:Password>
</xog:Login>
</soap-env:Body>
</soap-env:Envelope>
</soap:message>
</soap:invoke>

<gel:set var="sessionID" asString="true" select="$xog_login/soap-env:Envelope/soap-env:Body/xog:SessionID/text()"/>
<core:choose>
<core:when test="${sessionID == null}">
<gel:out>Couldn't Log in. Check the username/password.</gel:out>
</core:when>
<core:otherwise>
<gel:out> <gel:expr select="$xog_login"/> </gel:out>

<soap:invoke endpoint="${XOGURL}/niku/xog" var="XOG_result">
<soap:message>
<soap-env:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soap-env:Header>
<xog:Auth><xog:SessionID>${sessionID}</xog:SessionID></xog:Auth>
</soap-env:Header>
<soap-env:Body>

<!-- ================================================= -->
<!-- Here is my xml statement with the variable plugged in...-->
<!-- ================================================= -->
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_application.xsd">
<Header action="write" externalSource="NIKU" objectType="application" overrideAutoNumbering="0" version="8.0"/>
<Applications>
<Application active="true"
name="${groupmembers.app_name}"
objectID = "${groupmembers.application_code}"
progress="2" status="1">
<CustomInformation>
<instance instanceCode=""
objectCode="appl_recov_plan_form"
parentInstanceCode="${groupmembers.application_code}" parentObjectCode="application">
<CustomInformation>
<ColumnValue name="name">${parentinfo.record_name}</ColumnValue>
<ColumnValue name="code">1</ColumnValue>
<ColumnValue name="type">arp</ColumnValue>
<ColumnValue name="date_completed">2013-04-19</ColumnValue>
</CustomInformation>
</instance>
</CustomInformation>
</Application>
</Applications>
<XOGOutput>
<Object type = "application"/>
<Status state="SUCCESS"/>
<Statistics failureRecords="0" insertedRecords="0"
totalNumberOfRecords="1" updatedRecords="0"/>
</XOGOutput>
</NikuDataBus>

<!-- ================================================= -->
<!-- Here is the rest of the code from you...-->
<!-- ================================================= -->

</soap-env:Body>
</soap-env:Envelope>
</soap:message>
</soap:invoke>

</core:otherwise>
</core:choose>

<soap:invoke endpoint="${XOGURL}/niku/xog" var="xog_logout">
<soap:message>
<soap-env:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soap-env:Header>
<xog:Auth><xog:SessionID>${sessionID}</xog:SessionID></xog:Auth>
</soap-env:Header>
<soap-env:Body>
<xog:Logout/>
</soap-env:Body>
</soap-env:Envelope>
</soap:message>
</soap:invoke>
<gel:out> <gel:expr select="$xog_logout"/> </gel:out>

</gel:script>

------------------------------------------------

Thanks in advance,
christine

It looks like you are not parsing your XML into a GEL variable first of all, then as you haven't done that, you're not including anything in your XOG call.

Put a line like this before the section where you build your XML

<gel:parse var="object_xml">

Then include it in the call like this

<core:catch var="v_xog_exception">
 <soap:invoke endpoint="${XOGURL}/niku/xog" var="XOG_result">
 <soap:message>
 <soap-env:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
 <soap-env:Header>
 <xog:Auth>
  <xog:SessionID>${sessionID}</xog:SessionID> 
  </xog:Auth>
  </soap-env:Header>
 <soap-env:Body>
  <gel:include select="$object_xml" /> 
  </soap-env:Body>
  </soap-env:Envelope>
  </soap:message>
  </soap:invoke>
 </core:catch>

Hi Owen,

Thank you for your reply! I apologize....I'm a newbie to creating a custom script to call the XOG process.....so please excuse my ignorance.

I actually tried to tweak the code that I found in another forum posting (9744519 to see if I could get it to work for me.....maybe I removed too much of the code that is needed to get it to work?

When you mentioned that I should add the <gel:parse var="object_xml"> statement before the XML, are you referring to the section right before the "<NikuDataBus" tag?

I do see the other code mentioned in your posting in the script....but it's not exactly in the same order that you have it. Is that OK?

thanks for your help,
christine

christine.takeuchi wrote:

I apologize....I'm a newbie to creating a custom script to call the XOG process.....so please excuse my ignorance.

Don't apologise - everyone has to start somewhere and lot of this stuff you only learn by trial and error.

christine.takeuchi wrote:

When you mentioned that I should add the <gel:parse var="object_xml"> statement before the XML, are you referring to the section right before the "<NikuDataBus" tag?

Yes

christine.takeuchi wrote:

I do see the other code mentioned in your posting in the script....but it's not exactly in the same order that you have it. Is that OK?

When I do XOG within GEL I tend to do it in this order
1 - Get my data from SQL
2 - Loop through and put that into variables
3 - Build the XML file using Parse tag and incorporating those variables' values
4 - Log in to XOG
5 - Send the Write call with the XML file
6 - Log out

Sometimes, depending on what version you are on and what you are trying to write back, due to a bug you have to log in and out of XOG for each record you want to write, but that's the only difference. In your script you log in to XOG earlier so that would be the only thing I would change.

Owen