Learn PPM with Rego: Bad Transactions Posted, Jaspersoft with Other Databases, & What Happens to Timesheets When My Project is On Hold?

In this week’s Learn PPM with Rego, we take on five new Clarity PPM questions and answers.

1. What happens to Timesheets when my Project is on a Financial Hold?
2. Can we use Jaspersoft with other non-Clarity PPM databases?
3. Is there a way to get rid of extra transactions posted to financials?
4. Why am I getting an Invalid Relational Operator Error with my query?
   [SQL inside GEL]
5. How do you return the next auto number sequence ID of an object attribute? [GEL]

Please feel free to comment on any alternative answers you’ve found within the CA Community here. 

We love your input (always).

1——————————

Can we use Jaspersoft with other non-Clarity PPM databases?

Answer

With Jaspersoft you have the ability to create “Data Sources” which allow you to connect to any database that is available to you. If you’re On Demand please make sure to check that connection goes through your firewall. We have seen a client try to connect PeopleSoft data to Jaspersoft inside of Clarity, and it was denied by CA’s firewall.

See data source info: http://community.jaspersoft.com/wiki/datasources

2——————————

What happens to Timesheets when a Project is on a Financial Hold?

Answer

If the resources were enabled, there should have been entries created in the Invalid Transactions log when the timesheets were posted. If those entries are present, then set the project to open (assuming that the other minimum attributes for enablement have been set), and the transactions will post the next time you run Post Transactions to Financials and Post to WIP.

3——————————

Is there a way to get rid of extra transactions posted to financials?

We mistakenly entered several duplicate transactions. Then we ran the Post Transactions to Financials Job, so we can’t remove the extra transactions from the “invalid transactions” screen.

Posting and reversing is not a good option since there are thousands of transactions already, and identifying the ones we need to remove won’t be easy.

Does anyone has a script or good method to get rid of several transactions from the “Ready to Post to WIP” step?

Answer

Delete the transactions from the following tables:

PPA_TRANSCONTROL

PPA_TRANSCONTROL_VALUES

The structure is pretty much the same as the PPA_WIP tables.

4——————————

I’m getting an “invalid relational operator” error in GEL when trying to do a simple query using a less-than sign. It’s inside CDATA tags. Any thoughts? Version 14.3  

<sql:query var=”results”><![CDATA[

SELECT prstart

FROM prtimeperiod

WHERE prestart < (ADD_MONTHS((LAST_DAY(SYSDATE)+1),-1) – ?)

]]>

<sql:param>${p_close_nbr}</sql:param>

</sql:query>

Answer

Remember the escapeText=”false”

5——————————

How do you return the next auto number sequence ID of an object attribute? [GEL]

Answer

Try this GEL Script Code:

<core:invokeStatic className=”com.niku.odf.object.autonumbering.AutoNumberGenerator” method=”getNextNumber” var=”nextCode” >

             <core:arg type=”java.lang.String” value=”het_prj_gl_actuals”/>

             <core:arg type=”java.lang.String” value=”code”/>

             <core:arg type=”java.lang.String” value=”NIKU.ROOT”/>

             <core:arg type=”java.lang.Number” value=”${null}”/>

             <core:arg type=”com.niku.union.security.SecurityIdentifier” value=”${null}”/>

             <core:arg type=”java.sql.Connection” value=”${null}”/>

           </core:invokeStatic>

           <core:invokeStatic className=”com.niku.odf.object.autonumbering.AutoNumberGenerator” method=”getNextNumber” var=”nextName” >

             <core:arg type=”java.lang.String” value=”het_prj_gl_actuals”/>

             <core:arg type=”java.lang.String” value=”name”/>

             <core:arg type=”java.lang.String” value=”NIKU.ROOT”/>

             <core:arg type=”java.lang.Number” value=”${null}”/>

             <core:arg type=”com.niku.union.security.SecurityIdentifier” value=”${null}”/>

             <core:arg type=”java.sql.Connection” value=”${null}”/>

           </core:invokeStatic>

The parameters are as follows:

-Object ID

-Attribute ID

-Partition ID

-Parent PKID (can be null)

-Security Identifier (can be null)

-Connection (can be null)

THANK YOU——————————

A special thanks to David Zywiec, Darren Greer, Chris Shaffer, Jenn Rinella, and the Rego Team for this great material.