Learn with Rego | Bad Transactions Posted to Financials, Jaspersoft with Other Databases, + More

Document created by navzjoshi00 Champion on Sep 15, 2016Last modified by navzjoshi00 Champion on Feb 3, 2017
Version 2Show Document
  • View in full screen mode

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).

 

1. 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)

 

 

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?

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. 

 

 

3. 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”

 

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.)?

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

 

 

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?

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.

 

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

Attachments

    Outcomes