Learn with Rego | Query Links CMN_CUSTOM_SCRIPTS Table to Process Name; Find a Calendar with SQL; Different Project and Team Actual Hours; Apply a Negative Amount to Task Actuals; and Display Lookup Values in MSP

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

16_07_LWR_02_Community_header.jpg

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

 

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.

 

 

Feel free to share your feedback and thanks for participating.

 

Your guide,

The Rego Team

1 person found this helpful

Attachments

    Outcomes