Dear Community Members,
In this week's Learn PPM with Rego, we'll explore five CA PPM questions and answers.
1. Can a Query link the CMN_CUSTOM_SCRIPTS table to the process/step name?
2. How can I use SQL to find the calendar where a resource's non-working day is defined?
3. Why is there a difference in Actual Hours between Project and Team?
4. How can we apply negative amounts to Task Actuals?
5. Can you display Lookup Values in MSP as a pull down?
Please feel free to comment on any alternative answers you've found.
We love your input (always).
Answer
SELECT PN.NAME PROCESS_NAME
, P.PROCESS_CODE
, PV.INTERNAL_STATUS_CODE
, PSPN.NAME PROCESS_STEP
, SAN.NAME STEP_NAME
, SA.SCRIPT_ID
, TO_CHAR(SUBSTR(S.SCRIPT_TEXT, 0, 4000)) XX
FROM BPM_DEF_PROCESSES P
JOIN BPM_DEF_PROCESS_VERSIONS PV ON P.ID = PV.PROCESS_ID
JOIN BPM_DEF_STAGES PS ON PV.ID = PS.PROCESS_VERSION_ID
JOIN BPM_DEF_STEPS PSP ON PS.ID = PSP.STAGE_ID
JOIN BPM_DEF_STEP_ACTIONS SA ON PSP.ID = SA.STEP_ID
JOIN CMN_CAPTIONS_NLS PN ON P.ID = PN.PK_ID AND PN.TABLE_NAME = 'BPM_DEF_PROCESSES' AND PN.LANGUAGE_CODE = 'en'
JOIN CMN_CAPTIONS_NLS PSPN ON PSP.ID = PSPN.PK_ID AND PSPN.TABLE_NAME = 'BPM_DEF_STEPS' AND PSPN.LANGUAGE_CODE = 'en'
JOIN CMN_CAPTIONS_NLS SAN ON SA.ID = SAN.PK_ID AND SAN.TABLE_NAME = 'BPM_DEF_STEP_ACTIONS' AND SAN.LANGUAGE_CODE = 'en'
JOIN CMN_CUSTOM_SCRIPTS S ON SA.SCRIPT_ID = S.ID
WHERE S.SCRIPT_TEXT LIKE '%uslx%'
ORDER BY P.PROCESS_CODE
The only SQL way to determine if a non-working day is a holiday (standard calendar) or vacation (resource calendar) is to compare the resource availability with the availability for an unmodified user (admin). The following SQL appears to do the trick:
SELECT s.prj_object_id
, CASE s.slice WHEN 0 THEN 8 ELSE 0 END hours
, CASE WHEN (s.slice = 0 AND a.slice > 0) THEN 'Vacation' ELSE 'Holiday' END description
FROM prj_blb_slices s
LEFT OUTER JOIN prj_blb_slices a
ON a.slice_request_id = 1
AND a.prj_object_id = 1
AND a.slice_date = s.slice_date
WHERE s.slice_request_id = 1
AND TO_CHAR(s.slice_date, 'DY') NOT IN ('SAT','SUN')
The summed up actuals hours at the project level are dependent on the investment allocation job which aggregates values from the assignment blobs and puts them in blobs on the investment record. If these blobs get out of sync, you could see differences. Also, the Investment Allocation job does not run against inactive projects, so you could see differences with inactive projects.
Our preference is to pull the Actuals from the Assignment level, so you’re not dependent on the Investment Allocation job.
4. How can you apply a negative amount to Task Actuals?
We use manual transactions to track costs for some projects with an expense resource. Recently we entered the wrong cost for a task and pushed a reversal transaction to cancel it. The transaction went through well, and the Financial Plan is coming up with the correct numbers.
However in CA PPM Gantt, the resource still shows the actuals. Apparently negative amounts are not applied to Task Actuals with the Import Financial Actuals job. How can we apply this on the UI end?
If expected actual cost and hours are zero, you can follow the steps below.
But first, some things to consider . . . ETC will also be deleted, and when the Assignment Record is created again it won’t inherit the actual curve or actual cost curve from the WIP tables. If the resource is labor, we need to make sure there is no timesheet. Please test this non-prod and see whether it meets your requirement before doing it in Production.
• Update Prassignment Record as show below:
update prassignment set prextension=null, slice_status=1 , practsum=0, actcost_curve=null , actcost_sum=0 where prid=<assignmentid>
• Run Timeslice Job
• Update Resource ID to another value for those transactions in PPA_WIP:
update ppa_wip set resource_code='<some unique value in the system>’ where project_code='<ProjectID that have issue>' and resource_code='<Resource ID that have issue>' and task_id=<task ID that have issue>
• Delete the assignment via UI
• Recreate the same assignment for the resource
• Revert back the Step 2 changes:
update ppa_wip set resource_code=’<Resource ID that have issue>' where project_code='<ProjectID that have issue>' and resource_code='<some unique value in the system>’ and task_id=<task ID that have issue>
5. Can you display Lookup Values in MSP as a pull-down?
We're required to have a static lookup created in the Task Object and mapped to MSP, so that a Custom Attribute can be managed from MSP.
We created Custom Lookup Attributes and inserted a corresponding row in MSPFIELD (Say Text19). Now in MSP, we only see 0,1,2 etc. values in the field . . . although it is a Lookup Code/Value Static Lookup. We can also save the value back to CA PPM if I type 1 or 2 in Text19 in MSP (it sets the first or second lookup value).
Is there a way we can have lookup codes and/or values in MSP in a pull-down to select? We tried MSP > Text19 > Right Click > Custom Fields... > Selected Lookup > and added lookup value/description, which could be workaround, but every user would need to do that at his/her MSP. Secondly, using this workaround, lookup values show up in a pull-down, but MSP still shows 0,1,2 as display text.
What are we missing?
Key Points
• It has to be mapped to a text field in MSP.
• You cannot get it to show as a lookup in MSP – just text.
• It has to be Dynamic query based lookup (if Static, create a Dynamic lookup that uses a Static one).
• On the CA PPM side, make sure you're using a lookup code vs. enum.
The tricky part is that the lookup display in CA PPM needs to be the ID vs. the name. So we suggest making your lookup codes more representative of the values you want to see in the UI. If you change the lookup in CA PPM to display the values vs. the code, it won't work.
• The MSP value has to be the lookup_code as well. It cannot be the lookup name.
Feel free to share your feedback and thanks for participating.
Your guide,
The Rego Team
Finally got this to show all resources with their respective calendars, and created a jaspersoft report.
Thanks,
Keri
Finally managed to get
however, it's just for US calendar, at this time....
Hello urmas,
I can get the non-working time, i just can't seem to get the pto vs holiday.
what i really need to know is which is holiday and which is pto from my calendar.
Since we have 8 calendars, I also need to pull that in, since each has their own "holidays".
I can get the avail data and use one of my calendar resources i set up for just this purpose, each of them tied to their calendar. ie. US, Costa Rica, Mexico, etc...
then use the code to get the differences to get the "non-working time"
(SELECT * FROM prj_blb_slices WHERE slice_request_id = 1 AND prj_object_id = 5055007 /* <== United States Resource */ AND slice_date BETWEEN :param_start_date AND :param_end_date ) stdcal , (SELECT * FROM prj_blb_slices WHERE slice_request_id = 1 AND slice_date BETWEEN :param_start_date AND :param_end_date AND prj_object_id=5036014 /* <== me as an example , i have US calendar*/ ) rescal
I think its just getting me confused putting all this together. :-(
That was some time ago and I would need some refreshing of my memory. The examples are even further back see
Reading company holidays from base calendars
https://communities.ca.com/message/241847261?commentID=241847261#comment-241847261
See also the links in them. Queries from Atül and Sundar are in
SQL Query to Base Calendars
Dave posts a number of further links
https://communities.ca.com/message/241841205?commentID=241841205#comment-241841205
What exactly the is you need?
urmas, do you have a working example of your suggestions on Holiday vs Non-Working Time? Thanks
Thank you for posting these.
Just wondering about #2
If the question is how to find the calendar, the query result will give something else.
Which might indicate that the query could be fine tuned if the objective is to display the non-working days.
Adding the resource name and slice date to the query results would identify to the user which resource and which date is in question.
The hours seem to be 0 and the description Holiday when it is a non-working day in the standard calendar.
If the availability is zero on any other day then the hours are 8 for Vacation.
I'd rather take the the length of the working day from the calendar in the prsite table than 8 to make the query usable regardless of the length of the working day.
If the non-working days are not Saturday and Sunday they could also be pulled from the admin's slices.
You could also display the status of the user/resource if that is not active.
Then wondering again if the LEFT OUTER JOIN is really needed as the join is to the same table and all records are there.