Clarity

Hi,

I'm trying to design the code to get the pending actual for each resource with respect to task /investment. I was able to acheive in getting the ETC & Actual on Monthly (period) basis using the table DWH_INV_ASSIGN_PERIOD FACTS.  But to get the pending actual which is available in DWH_INV_ASSIGN_SUMMARY_FACTS but no period_key is avaiable.

So if any one have achieved in getting the report(Table) query to get Resoure name, Investment Name, Task Name, Monthly period, Actuals, ETC and pending Actual, please do share.

Thanks

Sreeram

Hi Sreeram,

You can query both tables joined, DWH_INV_ASSIGN_PERIOD FACTS and DWH_INV_ASSIGN_SUMMARY_FACTS or you can join DWH_INV_ASSIGN_SUMMARY_FACTS to DWH_CMN_PERIOD_M_V.

IMO the easiest way to see this and how exactly it will look, is to create an Ad Hoc view and then select View SQL query button (depending on Server options this may not always enabled).

Here is the query I got for this sample Ad-Hoc View based on Project Management domain with the fields you wanted:

select * from ( select "DWH_CMN_PERIOD_M_V"."MONTH_START_DATE" as "DWH_CMN_PERIOD_M_V_MONTH_START",

"DWH_CMN_PERIOD_M_V"."MONTH_START_DATE" as "DWH_CMN_PERIOD_M_V_cal_calc_mo",

"DWH_INV_ASSIGNMENT"."RESOURCE_NAME" as "DWH_INV_ASSIGNMENT_RESOURCE_NA",

"DWH_INV_ASSIGN_SUMMARY_FACTS"."ACTUAL_HOURS" as "DWH_INV_ASSIGN_SUMMARY_FACTS_A",

"DWH_INV_ASSIGN_SUMMARY_FACTS"."ETC_HOURS" as "DWH_INV_ASSIGN_SUMMARY_FACTS_E",

"DWH_INV_ASSIGN_SUMMARY_FACTS"."PENDING_ACTUAL_HOURS" as "DWH_INV_ASSIGN_SUMMARY_FACTS_P",

"DWH_INV_INVESTMENT"."INVESTMENT_NAME" as "DWH_INV_INVESTMENT_INVESTMENT_",

"DWH_INV_TASK"."TASK_NAME" as "DWH_INV_TASK_TASK_NAME"

from "PPM_DWH"."DWH_INV_INVESTMENT" "DWH_INV_INVESTMENT"

inner join "PPM_DWH"."DWH_INV_PER_FACTS_M_V" "DWH_INV_PER_FACTS_M_V" on ("DWH_INV_INVESTMENT"."INVESTMENT_KEY" = "DWH_INV_PER_FACTS_M_V"."INVESTMENT_KEY")

inner join "PPM_DWH"."DWH_INV_TASK" "DWH_INV_TASK" on ("DWH_INV_INVESTMENT"."INVESTMENT_KEY" = "DWH_INV_TASK"."INVESTMENT_KEY")

inner join "PPM_DWH"."DWH_INV_ASSIGNMENT" "DWH_INV_ASSIGNMENT" on ("DWH_INV_TASK"."TASK_KEY" = "DWH_INV_ASSIGNMENT"."TASK_KEY")

inner join "PPM_DWH"."DWH_INV_ASSIGN_SUMMARY_FACTS" "DWH_INV_ASSIGN_SUMMARY_FACTS" on ("DWH_INV_ASSIGNMENT"."ASSIGNMENT_KEY" = "DWH_INV_ASSIGN_SUMMARY_FACTS"."ASSIGNMENT_KEY")

inner join "PPM_DWH"."DWH_CMN_PERIOD_M_V" "DWH_CMN_PERIOD_M_V" on ("DWH_INV_PER_FACTS_M_V"."PERIOD_KEY" = "DWH_CMN_PERIOD_M_V"."PERIOD_KEY" and "DWH_CMN_PERIOD_M_V"."LANGUAGE_CODE" = 'en')

inner join "PPM_DWH"."DWH_INV_SECURITY_V" "DWH_INV_SECURITY_V" on ("DWH_INV_INVESTMENT"."INVESTMENT_KEY" = "DWH_INV_SECURITY_V"."INVESTMENT_KEY")

where ("DWH_INV_SECURITY_V"."USER_UID" = 'admin') and "DWH_INV_INVESTMENT"."INVESTMENT_TYPE_KEY" = 'project' and "DWH_INV_ASSIGN_SUMMARY_FACTS"."PENDING_ACTUAL_HOURS" > 0 and "DWH_INV_INVESTMENT"."INVESTMENT_TYPE_KEY" = 'project'

order by "DWH_INV_ASSIGNMENT_RESOURCE_NA", "DWH_CMN_PERIOD_M_V_cal_calc_mo"

) where ROWNUM <= 200001

This feature can help you see how the joins are done within the domains and help you get an idea on how to further query to build your reports.

Hope this helps -Nika