Today’s Q/A explores five topics.
1. Can a Query link the CMN_CUSTOM_SCRIPTS table to theprocess/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.
——————————
1
Can a Query link the CMN_CUSTOM_SCRIPTS table to the process/step name?
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
——————————
2
How can I use SQL to find the calendar where a resource’s non-working day is defined?
Answer
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’)
——————————
3
Why is there a difference in Actual Hours between Project and Team?
Answer
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?
Answer
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?
Answer- 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.
——————————
Please feel free to comment on our answers and any alternative answers you’ve found within the complete article here, in the CA Community. Rego celebrates Fridays by sharing Questions & Answers about CA PPM with our ever-expanding knowledge-community, so we can all learn as much as possible.
We love your input (always).
And a special thanks to the brilliant Navdeep Joshi and the Rego Team for this great material.
About the Author: Rego Consulting
