Dear Community Members,
In this week's Learn PPM with Rego, we'll explore five CA PPM questions and answers.
1. How do you return the next auto number sequence ID of an object attribute? [GEL]
2. Is there a way to get rid of extra transactions posted to financials?
3. Why am I getting an Invalid Relational Operator Error with my query? [SQL inside GEL]
4. Can we use Jaspersoft with other non-CA PPM databases?
5. What happens to Timesheets when a Project is on a Financial Hold?
Please feel free to comment on any alternative answers you've found.
We love your input (always).
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)
2. 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?
Delete the transactions from the following tables:
<sql:query var="results"><![CDATA[SELECT prstartFROM prtimeperiod WHERE prestart < (ADD_MONTHS((LAST_DAY(SYSDATE)+1),-1) - ?)]]><sql:param>${p_close_nbr}</sql:param></sql:query>
4. Is it possible for the Jaspersoft running in CA PPM to create reports or pull data from other Databases? If it is possible then what would the difficulty/ease to do this and any other implications be (e.g. licensing etc.)?
5. We had resources post timesheets to a project on financial hold, so the transactions weren't created. If we flip the status to open, will the system retroactively look at all posted timesheets, or do we need to do something special?
*A special thanks to David Zywiec, Darren Greer, Chris Shaffer, Jenn Rinella, and the Rego Team for this great material.