CREATE or replace View V_USER_BUT_DET as select distinct USR.USER_ID, BUT.BUS_UT_ID, BUT.BUS_UT_NM, BUT.BUS_UT_KY, BUT.FEED_TYPE_ID from INT_USER_DETAILS USR, INT_USER_ROLE_DETAILS URL, BUSINESS_UNIT BUT where USR.USER_ID = URL.USER_ID AND URL.USER_ROLE_ID = 'AA' AND URL.STATUS_IN = 'A' AND BUT.BUS_UT_ID NOT IN('INVALID', 'N/A' ) UNION select distinct RLS.USER_ID, BUT.BUS_UT_ID, BUT.BUS_UT_NM, BUT.BUS_UT_KY, BUT.FEED_TYPE_ID from INT_USER_ROLES_BUS_UT_DET RLS, BUSINESS_UNIT BUT where RLS.BUSINESS_UNIT_ID = BUT.BUS_UT_ID -- AND RLS.USER_ROLE_ID <> 'AA' AND RLS.USER_ROLE_ID NOT IN('AA', 'PM') AND RLS.CURRENT_REC_IN = 'Y' AND RLS.STATUS_IN = 'A' UNION select distinct RLS.USER_ID, BUT.BUS_UT_ID, BUT.BUS_UT_NM, BUT.BUS_UT_KY, BUT.FEED_TYPE_ID from INT_USER_ROLES_BUS_UT_DET RLS, BUSINESS_UNIT BUT where RLS.USER_ROLE_ID = 'PM' AND RLS.CURRENT_REC_IN = 'Y' AND RLS.STATUS_IN = 'A' -- AND RLS.USER_ID = 'alstimpson' AND BUT.BUS_UT_ID in( SELECT business_unit_id buid FROM INT_BUSINESS_UT_HIERARCHY WHERE ROWID IN( SELECT MAX(ROWID) FROM INT_BUSINESS_UT_HIERARCHY GROUP BY LEVEL, business_unit_id, parent_business_unit_id, current_rec_in ) CONNECT BY PRIOR business_unit_id = parent_business_unit_id START WITH business_unit_id = RLS.BUSINESS_UNIT_ID ) ----------------- PLAN OUTPUT ------------------- SELECT STATEMENT, GOAL = CHOOSE Cost=210575 Cardinality=11447 Bytes=610710 SORT UNIQUE Cost=210575 Cardinality=11447 Bytes=610710 UNION-ALL MERGE JOIN CARTESIAN Cost=90 Cardinality=9590 Bytes=517860 NESTED LOOPS Cost=2 Cardinality=44 Bytes=968 TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_USER_ROLE_DETAILS Cost=2 Cardinality=44 Bytes=572 INDEX UNIQUE SCAN Object owner=PM_DBA Object name=INT_USER_DETAILS_PK Cardinality=400 Bytes=3600 BUFFER SORT Cost=90 Cardinality=216 Bytes=6912 TABLE ACCESS FULL Object owner=PM_DBA Object name=BUSINESS_UNIT Cost=2 Cardinality=216 Bytes=6912 HASH JOIN Cost=6 Cardinality=203 Bytes=10150 TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_USER_ROLES_BUS_UT_DET Cost=3 Cardinality=202 Bytes=3636 TABLE ACCESS FULL Object owner=PM_DBA Object name=BUSINESS_UNIT Cost=2 Cardinality=218 Bytes=6976 FILTER NESTED LOOPS Cost=307 Cardinality=1654 Bytes=82700 TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_USER_ROLES_BUS_UT_DET Cost=3 Cardinality=152 Bytes=2736 TABLE ACCESS FULL Object owner=PM_DBA Object name=BUSINESS_UNIT Cost=2 Cardinality=11 Bytes=352 FILTER CONNECT BY WITH FILTERING NESTED LOOPS INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_BUS_UT_HIERARCHY_PK Cost=1 Cardinality=1 Bytes=6 TABLE ACCESS BY USER ROWID Object owner=PM_DBA Object name=INT_BUSINESS_UT_HIERARCHY NESTED LOOPS BUFFER SORT Cardinality=11 Bytes=187 CONNECT BY PUMP INDEX FULL SCAN Object owner=PM_DBA Object name=INT_BUS_UT_HIERARCHY_PK Cost=127 Cardinality=11 Bytes=187 FILTER SORT GROUP BY Cost=8 Cardinality=3 Bytes=54 TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_BUSINESS_UT_HIERARCHY Cost=2 Cardinality=212 Bytes=3816 -------------------------- REPORT SELECT STATEMENT --------- select /*+ FIRST_ROWS */ "Portfolio_Level_Type"."PORTFOLIO_LEVEL_DE" "Portfolio_Level_Description", "Return_Object_Type"."RETURN_OBJECT_DE" "Return_Object_Description", "Sector_Industries"."INDUSTRY_HIERARCHY_LEVEL1_NM" "INDUSTRY_HIERARCHY_LEVEL1_NM", "Business_Unit"."BUS_UT_NM" "BUS_UT_NM", "Account"."ACCOUNT_SHORT_NM" "Account_Name", "Monthly_Combined_Returns"."RATE_OF_RETURN_PT" "Return1", "Monthly_Combined_Returns"."END_MARKET_VALUE_AM" "Market_Value", case when "Monthly_Combined_Returns"."INDUSTRY_HIERARCHY_LEVEL_KY"<>0 then 1 else 2 end "Ranking", "Sector_Industries"."INDUSTRY_HIERARCHY_LEVEL_KY" "INDUSTRY_HIERARCHY_LEVEL_KY", "Sector_Industries"."INDUSTRY_HIERARCHY_LEVEL1_ID" "INDUSTRY_HIERARCHY_LEVEL1_ID", ("Monthly_Combined_Returns"."BEGIN_MARKET_VALUE_AM"/nullif(sum(case when (case when ("Monthly_Combined_Returns"."INDUSTRY_HIERARCHY_LEVEL_KY"<>0) then 1 else 2 end =2) then "Monthly_Combined_Returns"."BEGIN_MARKET_VALUE_AM" else 0 end ) over (partition by "Account"."ACCOUNT_SHORT_NM"), 0))*"Monthly_Combined_Returns"."RATE_OF_RETURN_PT" "Contribution", sum(case when case when ("Monthly_Combined_Returns"."INDUSTRY_HIERARCHY_LEVEL_KY"<>0) then 1 else 2 end =2 then "Monthly_Combined_Returns"."BEGIN_MARKET_VALUE_AM" else 0 end ) over (partition by "Account"."ACCOUNT_SHORT_NM") "Beg_MV_Total", "Monthly_Combined_Returns"."BEGIN_MARKET_VALUE_AM"/nullif(sum(case when (case when ("Monthly_Combined_Returns"."INDUSTRY_HIERARCHY_LEVEL_KY"<>0) then 1 else 2 end =2) then "Monthly_Combined_Returns"."BEGIN_MARKET_VALUE_AM" else 0 end ) over (partition by "Account"."ACCOUNT_SHORT_NM"), 0) "Weight_for_Contrib", "Monthly_Combined_Returns"."ACCOUNT_KY" "AcctKy", sum(case when case when ("Monthly_Combined_Returns"."INDUSTRY_HIERARCHY_LEVEL_KY"<>0) then 1 else 2 end =2 then "Monthly_Combined_Returns"."END_MARKET_VALUE_AM" else 0 end ) over (partition by "Account"."ACCOUNT_SHORT_NM") "End_MV_Total", "Monthly_Combined_Returns"."END_MARKET_VALUE_AM"/nullif(sum(case when (case when ("Monthly_Combined_Returns"."INDUSTRY_HIERARCHY_LEVEL_KY"<>0) then 1 else 2 end =2) then "Monthly_Combined_Returns"."END_MARKET_VALUE_AM" else 0 end ) over (partition by "Account"."ACCOUNT_SHORT_NM"), 0) "Weight", "Monthly_Combined_Returns"."MONTH_KY" "MonKy", "Business_Unit"."BUS_UT_KY" "BUKy", "Calendar_Month"."Month_Year" "Month_and_Year" from "PM_DBA"."PORTFOLIO_LEVEL_TYPE" "Portfolio_Level_Type", "PM_DBA"."RETURN_OBJECT_TYPE" "Return_Object_Type", "PM_DBA"."INDUSTRY_HIERARCHY" "Sector_Industries", ( select /*+ FIRST_ROWS */ "Business_Unit"."BUS_UT_NM" "BUS_UT_NM", "Business_Unit"."BUS_UT_KY" "BUS_UT_KY" from ( select /*+ FIRST_ROWS */ "BUSINESS_UNIT"."BUS_UT_KY", "BUSINESS_UNIT"."BUS_UT_ID", "BUSINESS_UNIT"."BUS_UT_NM", "BUSINESS_UNIT"."BUS_UT_LONG_NM", "BUSINESS_UNIT"."PM_SYS_REGION_ID", "BUSINESS_UNIT"."CURRENT_REC_IN", "BUSINESS_UNIT"."BUS_UT_CONTACT_EMAIL_AD", "BUSINESS_UNIT"."BUS_UT_CONTACT_CELL_NO", "BUSINESS_UNIT"."BUS_UT_MTHLY_PERF_STR_DAY_NO", "BUSINESS_UNIT"."BUS_UT_CONTACT_FAX_NO", "BUSINESS_UNIT"."BUS_UT_SPONSOR_COUNTRY_ID", "BUSINESS_UNIT"."INVEST_MGR_BUS_UT_IN", "BUSINESS_UNIT"."BUS_UT_CONTACT_FIRST_NM", "BUSINESS_UNIT"."FISCAL_RECORD_IN", "BUSINESS_UNIT"."BUS_UT_CONTACT_TTL_TX", "BUSINESS_UNIT"."INTERMEDIATE_BUS_UT_IN", "BUSINESS_UNIT"."BUS_UT_CONTACT_LINE2_AD", "BUSINESS_UNIT"."BUS_UT_STATUS_IN", "BUSINESS_UNIT"."BUS_UT_CONTACT_CITY_NM", "BUSINESS_UNIT"."FEED_TYPE_ID", "BUSINESS_UNIT"."BUS_UT_CONTACT_STATE_NM", "BUSINESS_UNIT"."FEED_TYPE_NM", "BUSINESS_UNIT"."PM_SYS_REGION_NM", "BUSINESS_UNIT"."EFFECTIVE_START_DT", "BUSINESS_UNIT"."BUS_UT_CONTACT_EXTN_NO", "BUSINESS_UNIT"."EFFECTIVE_END_DT", "BUSINESS_UNIT"."BUS_UT_SPONSOR_COUNTRY_NM", "BUSINESS_UNIT"."CREATE_UI", "BUSINESS_UNIT"."BUS_UT_CONTACT_LINE1_AD", "BUSINESS_UNIT"."CREATE_TS", "BUSINESS_UNIT"."BUS_UT_CONTACT_ZIP_CD", "BUSINESS_UNIT"."UPDATE_UI", "BUSINESS_UNIT"."BUS_UT_CONTACT_LAST_NM", "BUSINESS_UNIT"."BUS_UT_CONTACT_LINE3_AD", "BUSINESS_UNIT"."BUS_UT_CONTACT_COUNTRY_NM", "BUSINESS_UNIT"."BUS_UT_MTHLY_PERF_END_DAY_NO", "BUSINESS_UNIT"."UPDATE_TS", "BUSINESS_UNIT"."OVERIDE_UI", "BUSINESS_UNIT"."OVERRIDE_REASON_ID", "BUSINESS_UNIT"."BUS_UT_CONTACT_WORK_PHONE_NO", "BUSINESS_UNIT"."USES_COMB_RTNS_IN", GET_BUS_UT_FACT_TYPE("BUSINESS_UNIT"."BUS_UT_KY") "PriFcTyKy" from "PM_DBA"."BUSINESS_UNIT" INNER JOIN "PM_DBA"."V_USER_BUT_DET" on "BUSINESS_UNIT"."BUS_UT_ID"="V_USER_BUT_DET"."BUS_UT_ID" where upper ("V_USER_BUT_DET"."USER_ID")=upper ('alstimpson')) "Business_Unit") "Business_Unit", "PM_DBA"."ACCOUNT" "Account", ( select /*+ FIRST_ROWS */ "Monthly_Combined_Returns"."END_MARKET_VALUE_AM" "END_MARKET_VALUE_AM", "Monthly_Combined_Returns"."RATE_OF_RETURN_PT" "RATE_OF_RETURN_PT", "Monthly_Combined_Returns"."BEGIN_MARKET_VALUE_AM" "BEGIN_MARKET_VALUE_AM", "Monthly_Combined_Returns"."BUS_UT_KY" "BUS_UT_KY", "Monthly_Combined_Returns"."ACCOUNT_KY" "ACCOUNT_KY", "Monthly_Combined_Returns"."MONTH_KY" "MONTH_KY", "Monthly_Combined_Returns"."REGION_KY" "REGION_KY", "Monthly_Combined_Returns"."COUNTRY_KY" "COUNTRY_KY", "Monthly_Combined_Returns"."INST_TYPE_HIERARCHY_LEVEL_KY" "INST_TYPE_HIERARCHY_LEVEL_KY", "Monthly_Combined_Returns"."INDUSTRY_HIERARCHY_LEVEL_KY" "INDUSTRY_HIERARCHY_LEVEL_KY", "Monthly_Combined_Returns"."CURRENCY_CODE_TYPE_KY" "CURRENCY_CODE_TYPE_KY", "Monthly_Combined_Returns"."CAPITAL_SIZE_KY" "CAPITAL_SIZE_KY", "Monthly_Combined_Returns"."PORTFOLIO_LEVEL_KY" "PORTFOLIO_LEVEL_KY", "Monthly_Combined_Returns"."RETURN_OBJECT_KY" "RETURN_OBJECT_KY" from ( select /*+ FIRST_ROWS */ * from "PM_DBA"."MONTHLY_COMBINED_RETURNS" where not ("MONTHLY_COMBINED_RETURNS"."END_MARKET_VALUE_AM"=0 and "MONTHLY_COMBINED_RETURNS"."RETURN_OBJECT_KY"=2 and ("MONTHLY_COMBINED_RETURNS"."RATE_OF_RETURN_PT" is null or "MONTHLY_COMBINED_RETURNS"."RATE_OF_RETURN_PT"=0)) or "MONTHLY_COMBINED_RETURNS"."RETURN_OBJECT_KY"<>2) "Monthly_Combined_Returns", ( select /*+ FIRST_ROWS */ "Calendar_Month"."MONTH_KY" "MONTH_KY", "Calendar_Month"."MONTH_ID" "MONTH_ID" from ( select /*+ FIRST_ROWS */ "CALENDAR_MONTH"."MONTH_KY", "CALENDAR_MONTH"."MONTH_ID", "CALENDAR_MONTH"."MONTH_NO", "CALENDAR_MONTH"."MONTH_NM", "CALENDAR_MONTH"."QUARTER_NO", "CALENDAR_MONTH"."QUARTER_NM", to_char("CALENDAR_MONTH"."YEAR_NO") "Year", "CALENDAR_MONTH"."YEAR_NO", Concat(Concat(Rtrim("CALENDAR_MONTH"."MONTH_NM"), ' '), Rtrim("CALENDAR_MONTH"."YEAR_NO")) "Month Year", "CALENDAR_MONTH"."CREATE_UI", "CALENDAR_MONTH"."CREATE_TS", "CALENDAR_MONTH"."UPDATE_UI", "CALENDAR_MONTH"."UPDATE_TS", "CALENDAR_MONTH"."OVERIDE_UI", "CALENDAR_MONTH"."OVERIDE_REASON_ID" from "PM_DBA"."CALENDAR_MONTH") "Calendar_Month") "Calendar_Month" where "Calendar_Month"."MONTH_KY"="Monthly_Combined_Returns"."MONTH_KY") "Monthly_Combined_Returns", ( select /*+ FIRST_ROWS */ "Calendar_Month"."Month Year" "Month_Year", "Calendar_Month"."MONTH_KY" "MONTH_KY" from ( select /*+ FIRST_ROWS */ "CALENDAR_MONTH"."MONTH_KY", "CALENDAR_MONTH"."MONTH_ID", "CALENDAR_MONTH"."MONTH_NO", "CALENDAR_MONTH"."MONTH_NM", "CALENDAR_MONTH"."QUARTER_NO", "CALENDAR_MONTH"."QUARTER_NM", to_char("CALENDAR_MONTH"."YEAR_NO") "Year", "CALENDAR_MONTH"."YEAR_NO", Concat(Concat(Rtrim("CALENDAR_MONTH"."MONTH_NM"), ' '), Rtrim("CALENDAR_MONTH"."YEAR_NO")) "Month Year", "CALENDAR_MONTH"."CREATE_UI", "CALENDAR_MONTH"."CREATE_TS", "CALENDAR_MONTH"."UPDATE_UI", "CALENDAR_MONTH"."UPDATE_TS", "CALENDAR_MONTH"."OVERIDE_UI", "CALENDAR_MONTH"."OVERIDE_REASON_ID" from "PM_DBA"."CALENDAR_MONTH") "Calendar_Month") "Calendar_Month", "PM_DBA"."REGION" "Region", "PM_DBA"."COUNTRY" "Country", "PM_DBA"."INSTRUMENT_TYPE_HIERARCHY" "Asset_Classes", "PM_DBA"."CODE_TYPE" "Fund_Currency_Code_Type" where "Monthly_Combined_Returns"."ACCOUNT_KY" in (0) and ("Region"."REGION_KY"=0 and "Country"."COUNTRY_KY"=0 and "Asset_Classes"."INMT_TYPE_HIERARCHY_LEVEL_KY"<>0 and "Monthly_Combined_Returns"."CAPITAL_SIZE_KY"=0 and "Asset_Classes"."INMT_TYPE_HIERARCHY_SCHEME_ID"='WILSHIRE' and "Asset_Classes"."INMT_TYPE_HIERARCHY_LEVEL_NO"=1 and "Asset_Classes"."INMT_TYPE_HIERARCHY_LEVEL1_NM"='Equities' and "Sector_Industries"."INDUSTRY_HIERARCHY_SCHEME_ID"='WIL-MSCI' and "Sector_Industries"."INDUSTRY_HIERARCHY_LEVEL_NO"='1' or "Region"."REGION_KY"=0 and "Country"."COUNTRY_KY"=0 and "Asset_Classes"."INMT_TYPE_HIERARCHY_LEVEL_KY"<>0 and "Sector_Industries"."INDUSTRY_HIERARCHY_LEVEL_KY"=0 and "Monthly_Combined_Returns"."CAPITAL_SIZE_KY"=0 and "Asset_Classes"."INMT_TYPE_HIERARCHY_SCHEME_ID"='WILSHIRE' and "Asset_Classes"."INMT_TYPE_HIERARCHY_LEVEL_NO"=1 and "Asset_Classes"."INMT_TYPE_HIERARCHY_LEVEL1_NM"='Equities') and "Business_Unit"."BUS_UT_KY"=0 and ("Monthly_Combined_Returns"."MONTH_KY"=0 and 0<>0 or "Monthly_Combined_Returns"."MONTH_KY"=GET_CUR_MONTH_BY_BUKY_OTHER(0) and 0=0) and "Fund_Currency_Code_Type"."CODE_TYPE_ID"='PRIMRY' and "Portfolio_Level_Type"."PORTFOLIO_LEVEL_DE"='Account' and "Return_Object_Type"."RETURN_OBJECT_DE"='Portfolio' and "Account"."ACCOUNT_KY"="Monthly_Combined_Returns"."ACCOUNT_KY" and "Business_Unit"."BUS_UT_KY"="Monthly_Combined_Returns"."BUS_UT_KY" and "Calendar_Month"."MONTH_KY"="Monthly_Combined_Returns"."MONTH_KY" and "Region"."REGION_KY"="Monthly_Combined_Returns"."REGION_KY" and "Country"."COUNTRY_KY"="Monthly_Combined_Returns"."COUNTRY_KY" and "Asset_Classes"."INMT_TYPE_HIERARCHY_LEVEL_KY"="Monthly_Combined_Returns"."INST_TYPE_HIERARCHY_LEVEL_KY" and "Sector_Industries"."INDUSTRY_HIERARCHY_LEVEL_KY"="Monthly_Combined_Returns"."INDUSTRY_HIERARCHY_LEVEL_KY" and "Monthly_Combined_Returns"."CURRENCY_CODE_TYPE_KY"="Fund_Currency_Code_Type"."CODE_TYPE_KY" and "Portfolio_Level_Type"."PORTFOLIO_LEVEL_KY"="Monthly_Combined_Returns"."PORTFOLIO_LEVEL_KY" and "Return_Object_Type"."RETURN_OBJECT_KY"="Monthly_Combined_Returns"."RETURN_OBJECT_KY"