lakse01

TIPS: How cost type value populates in transaction Table (PPA_TRANSCONTROL, PPA_WIP)

Discussion created by lakse01 Employee on Jul 22, 2017

The logic in determining what value to use for a transaction reads from the bottom up in the project hierarchy:

If the Cost Type on the Task is set to a value (Capital or Operating), that value is used. If the Cost Type is blank, the logic looks to the next level up in the WBS.

If that level is, for instance, a summary task, and its Cost Type has a value, then that value is used for the sub-task with a blank Cost Type.  If that level has a blank Cost Type, the logic looks to the next level up.

The logic will continue to search each level up for a value until it reaches the Project level.  Since Cost Type is a required field at the Project level, there will always be a Cost Type value for every transaction.

So - if you have a simple project WBS structure with no indentures (no summary tasks or phases) on the project, and the task that you select for charging the non-labor to has no Cost Type value, and if the Project is set with a Cost Type of Capital, the non-labor charges will be recorded and reported as Capital.

 

Example:

Project 1

Cost Type: Capital

Level

WBS

Cost Type

 

Level 1

Phase

Capital

From Template

Level 2

Work Package

Capital

From Template

Level 3

Task 1

 <blank>

Created By PM

 

PM adds a timesheet Task 1 @ WBS level 3 under the Work Package a cost type value will only display if you select a value at the Level 3 for Task 1.

If Cost type is not defined for Task 1 it will inherit the Cost type from parent in the backend in this case from Work Package.

If level 2 does not have a cost type value Task 1 will inherit the Cost Type from Level 1 in the backend.

If none of the WBS levels have cost type defined Task 1 will inherit the value defined at Project level.

 

Query to get cost type information at database:

SELECT C.PRNAME, C.COST_TYPE FROM

   ( SELECT NVL(PT1.COST_TYPE, NVL(PT2.COST_TYPE, P.COST_TYPE)) AS COST_TYPE, PT1.PRID PRID, PT1.PRNAME,

     ROW_NUMBER() OVER (ORDER BY PT2.PRWBSSEQUENCE DESC) ROW_NUM

                         FROM PAC_MNT_PROJECTS P, PRTASK PT1

                              LEFT OUTER JOIN PRTASK PT2 ON

                                   PT2.COST_TYPE IS NOT NULL

                                   AND PT2.PRPROJECTID = PT1.PRPROJECTID

                                   AND PT2.PRWBSSEQUENCE <= PT1.PRWBSSEQUENCE

                                   AND PT2.WBS_NNBSEQ > PT1.PRWBSSEQUENCE

                                   AND PT2.PRID <> PT1.PRID

                         WHERE PT1.PRPROJECTID = 5004001 /* PASS PROJECT ID */

                               AND PT1.PRID = 5005006   /* PASS TASK PRID */

                        ) C

                  WHERE C.ROW_NUM = 1

 

 

Function:

 

 

CREATE OR REPLACE FUNCTION NIKU.Z_TRANS_TASK_COST_TYPE

  (P_PROJECT_ID INT, P_TASK_ID INT)

  RETURN  VARCHAR IS

 V_COST_TYPE   PRTASK.COST_TYPE%TYPE;

BEGIN

SELECT  C.COST_TYPE INTO V_COST_TYPE

                  FROM ( SELECT NVL(PT1.COST_TYPE, NVL(PT2.COST_TYPE, P.COST_TYPE)) AS COST_TYPE, PT1.PRID PRID, PT1.PRNAME,

                                ROW_NUMBER() OVER (ORDER BY PT2.PRWBSSEQUENCE DESC) ROW_NUM

                         FROM PAC_MNT_PROJECTS P, PRTASK PT1

                              LEFT OUTER JOIN PRTASK PT2 ON

                                   PT2.COST_TYPE IS NOT NULL

                                   AND PT2.PRPROJECTID = PT1.PRPROJECTID

                                   AND PT2.PRWBSSEQUENCE <= PT1.PRWBSSEQUENCE

                                   AND PT2.WBS_NNBSEQ > PT1.PRWBSSEQUENCE

                                   AND PT2.PRID <> PT1.PRID

                         WHERE PT1.PRPROJECTID = P_PROJECT_ID

                               AND PT1.PRID = P_TASK_ID

                        ) C

                  WHERE C.ROW_NUM = 1;

 

    RETURN TO_CHAR(V_COST_TYPE);

END;

GO

 

SELECT I.CODE PROJECT_CODE, I.NAME PROJECT_NAME, T.PREXTERNALID TASK_ID, T.PRNAME TASK_NAME, P.COST_TYPE PROJECT_COST_TYPE, T.COST_TYPE TASK_COST_TYPE,

Z_TRANS_TASK_COST_TYPE(I.ID, T.PRID) TRANS_COST_TYPE

FROM INV_INVESTMENTS I JOIN PRTASK T ON I.ID = T.PRPROJECTID AND I.CODE = '00000003'

JOIN PAC_MNT_PROJECTS P ON P.ID = I.ID

ORDER BY PRWBSSEQUENCE

 

Output:

Attachments

Outcomes