Learn PPM with Rego: Override Project Status, Blank Instead of Null, Strange Date, Vendor Code Tracking, and Delimiters

Document created by navzjoshi00 Champion on Aug 4, 2016Last modified by navzjoshi00 Champion on Aug 5, 2016
Version 3Show 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 override the Project Overall Status Report values?

2. Gel Process Writing | When writing a CSV file, how can we make it blank instead of displaying null?

3. Where is this strange date coming from in our Calendar Table?

4. Is the vendor code tracked when we post a contractor's timesheets?

5. Can you help us with the delimiters in our Gel Script?


Please feel free to comment on any alternative answers you've found.

We love your input (always).


1. Is there a way to override the value that shows the Overall status as "Red/Yellow/Green," so that when the Schedule is "RED" the Overall Status will show as "RED," without considering the values in Scope and Cost?



We want this override anywhere the “Triple Constraint” (Schedule/Cost/Scope) is used, so whenever Schedule is RED, Overall STATUS is RED.



Change the weighting factor in the calculation so that the Schedule Status has a higher weight and forces a project to show as red if it's behind schedule.



2. When writing a CSV file, how can we make it blank instead of displaying null?



In the query, we used NVL(TO_CHAR(INVID),'null') INVID to make sure all numbers were first characters. Then we made them 'null' as a true string (instead of GEL adding it later). Finally, the column write looked like this: 

<file:column value="${RESULT[4].replace('null', '')}"/>



3. We have a portlet that queries against the nbi_dim_calendar_time table. Why does a 1950's date show up occasionally in the data?



Odds are good that there is something in the system extending back to 1950, whether accidentally or intentionally. The NBI_DIM_CALENDAR_TIME table is populated by the stored procedure NBI_POPULATE_CAL_DIM_SP which is used by the Datamart Extraction job.


When that job runs for the time within the NBI_DIM_CALENDAR table (two-years-worth of calendar structures: weeks, month, quarter, etc), subsequent runs of the job will ensure the creation of enough calendar periods to cover the extension of CA PPM's data curves. These data curves include information such as allocation, availability, and actuals.


If any object exists in the system with 1950 curve data attached, you'll probably see the date.


4. Is the vendor code tracked when a contractor's timesheet posts to the financial tables? The contractor is associated with the vendor.


Note: We realize the vendor is in the APINFO table, which holds the voucher header info for manual and (usually) non-labor transactions, but what's the expected behavior for labor transactions via timesheets?



The vendor code is not tracked in the WIP tables along with the transaction details. You'll have to pull the current value from the resource record.



5. Is there a limit on what delimiters can be used when reading a file using GEL? We're trying to specify pipe as a delimiter (i.e., delimiter="|"), and it's not working. We don't want to use comma (,) because there are commas in the data.



You can escape the pipe character with a "\". As below:


<files:readFile fileName="${vFileName}" delimiter="\|" var="vActualsData" embedded="false"/>


Feel free to share your feedback and thanks for participating.


Your guide,

The Rego Team

1 person found this helpful