Financial Cost Plan Details - Clarity PPM v12.0.6

costplans_clarity_ppm_12.0.6.sql

SELECT INVESTMENTS.ID INVESTMENT_PRID,
       INVESTMENTS.CODE INVESTMENT_ID,
       INVESTMENTS.ENTITY_CODE,
       INVESTMENTS.NAME INVESTMENT_NAME,
       INVESTMENTS.ODF_OBJECT_CODE INVESTMENT_TYPE,
       INVESTMENTS.IS_ACTIVE,
       INVESTMENTS.MANAGER_ID,
       INVESTMENTS.IS_OPEN_FOR_TE,
       PLANS.CODE PLAN_ID,
       PLANS.NAME PLAN_NAME,
       PLANS.DESCRIPTION PLAN_DESCRIPTION,
       PLANS.REVISION PLAN_REVISION,
       PLANS.PLAN_TYPE_CODE PLAN_TYPE,
       PLANS.PERIOD_TYPE_CODE PLAN_PERIOD,
       PLANS.IS_PLAN_OF_RECORD,
       PLAN_GROUP.NAME PLAN_BY_GROUP,
       PLAN_SUBGROUP.NAME PLAN_BY_SUBGROUP,
       CHARGE_CODES.PRNAME CHARGECODE_NAME,
       TRANSACTION_CLASSES.DESCRIPTION TRANCLASS_NAME,
       ROLES.NAME ROLE_NAME,
       PLAN_VALUES.START_DATE,
       PLAN_VALUES.FINISH_DATE,
       PLAN_VALUES.UNITS,
       PLAN_VALUES.COSTS
  FROM INV_INVESTMENTS INVESTMENTS,
       FIN_PLANS PLANS,
       FIN_COST_PLAN_DETAILS DETAILS,
       TRANSCLASS TRANSACTION_CLASSES,
       PRCHARGECODE CHARGE_CODES,
       ( SELECT PRJ_OBJECT_ID,
                 START_DATE,
                 FINISH_DATE,
                 SUM (UNITS) UNITS,
                 SUM (COSTS) COSTS
            FROM ( SELECT PRJ_OBJECT_ID,
                           START_DATE,
                           FINISH_DATE,
                           ROUND ( (FINISH_DATE - START_DATE) * SUM (SLICE), 2)
                              UNITS,
                           0 COSTS,
                           0 ACTUALS
                      FROM ODF_SSL_CST_DTL_UNITS
                  GROUP BY PRJ_OBJECT_ID, START_DATE, FINISH_DATE
                  UNION
                    SELECT PRJ_OBJECT_ID,
                           START_DATE,
                           FINISH_DATE,
                           0 UNITS,
                           ROUND ( (FINISH_DATE - START_DATE) * SUM (SLICE), 2)
                              COSTS,
                           0 ACTUALS
                      FROM ODF_SSL_CST_DTL_COST
                  GROUP BY PRJ_OBJECT_ID, START_DATE, FINISH_DATE)
        GROUP BY PRJ_OBJECT_ID, START_DATE, FINISH_DATE) PLAN_VALUES,
       (SELECT LOOKUP_CODE ID, NAME
          FROM CMN_LOOKUPS_V
         WHERE LOOKUP_TYPE = 'FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE = 'en') PLAN_GROUP,
       (SELECT LOOKUP_CODE ID, NAME
          FROM CMN_LOOKUPS_V
         WHERE LOOKUP_TYPE = 'FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE = 'en') PLAN_SUBGROUP,
       (SELECT A.PRID ID, B.FULL_NAME NAME
          FROM PRJ_RESOURCES A, SRM_RESOURCES B
         WHERE A.PRID = B.ID AND PRISROLE = 1) ROLES
 WHERE INVESTMENTS.ID = PLANS.OBJECT_ID
       AND PLANS.ID = DETAILS.PLAN_ID
       AND DETAILS.ID = PLAN_VALUES.PRJ_OBJECT_ID
       AND DETAILS.PRROLE_ID = ROLES.ID(+)
       AND PLANS.PLAN_BY_1_CODE = PLAN_GROUP.ID(+)
       AND PLANS.PLAN_BY_2_CODE = PLAN_SUBGROUP.ID(+)
       AND DETAILS.TRANSCLASS_ID = TRANSACTION_CLASSES.ID(+)
       AND DETAILS.PRCHARGECODE_ID = CHARGE_CODES.PRID(+)