**************************************************************************************************** Location : Local or Network Folders Host : FINDITEZ-T460P Path : C:\Users\ken\Documents\Test Cases\Oracle-Reports\MeasureSummary.xml **************************************************************************************************** -------------------------------------------------- -------------------------------------------------- -- Command(s) Q_MEASUREMENT -- ******************************************************************************************************************************* -- Jaroslav Kolar, Aug 13 2008 re-coded a bit REPORTING status and note, e.g added mega nested table -- ******************************************************************************************************************************* SELECT mber.object_id -- parent (file, submission or strategy) entity id, to be linked to Q_PARENT query , mber.sub_domain_id domain , m.title meas_title --, m.measure_unit , m.data_source , m.id as measurement_id , mtype.title measure_type , freq.title rep_freq , m.business_id measure_number --- not type, measure UNIT , NVL(mu_type.title, '???') measure_unit_type , m.measure_unit , reporting_status_data.overall_comment as status_note , mlvl.title lvl_of_measure , mcls.title measure_class , mcls.mclass_prompt , reporting_status_data.status as rs_status , reporting_to.title as reporting_to_name , reports_pkg.getentireunitpath( m.unit_id, reports_pkg.getunitlastname(m.unit_id)) as Measure_Org_Unit , :starting_point_id as starting_point , act_type.value_id as Actuals_Handling_Id , DECODE( measure_unit_type.title, 'Financial Data', '$', 'Percentage', '%', '') as m_unit_type FROM -- this nested table equals to 'm_measure' table for all but 'MeasureByActivityNotes' (report_type ==7) and MetricsByOrgUnit (report_type ==3) -- and in that special case, the result set is a function of a parameter ('starting_point_id') all the way up from -- Q_DRIVER query. This was needed to satisfy the requirement that states that the same measure should not -- be listed for both child and parent note. And as a rule, if the note is linked to child note, it's automatically listed to the parent note too -- the neater approach would be to implement this filtering when building 'CP_Q_MEASUREMENT_MEASUR_FILTER' but it was not -- possible ( error: 'wrong frequency' ) as that is taking place in BEFOREREPORT trigger ( select * from m_measure where :P_Report_Type_ID not in (3,7) UNION ALL select * from m_measure where :P_Report_Type_ID = 3 and unit_id = :starting_point_id UNION ALL select m2.* from m_metric_activity_note man2, activity_note an2, m_measure m2 where man2.activity_note_id = an2.id and m2.id = man2.metric_id and not exists ( select 1 from m_metric_activity_note inner_man2 , activity_note inner_an2 , ( -- JK 30 Jan 2009: the same subquery is implemented in 'Q_DRIVER' so any changes here have to be copied there ( and reasoning is described there) select pv.value_numeric from context_prompt cp, report_context rc, report_prompt rp, prompt_group pg, prompt_value_set pvs, prompt_value pv, activity_note where cp.report_prompt_id = rp.id and rp.prompt_group_id = pg.id and rp.prompt_number = 6 and rc.id = cp.report_context_id and report_id = 38 and pvs.OWNER_TYPE = 'USER' and pvs.report_context_id = rc.id and pv.prompt_value_set_id = pvs.id and pv.report_prompt_id = rp.id and pv.prompt_value_set_id = :P_Prompt_Value_Set_ID and :CP_Activity_Notes_Selected = 1 -- join child notes and activity_note.parent_id is not null and activity_note.id = pv.value_numeric UNION ALL select id as value_numeric from activity_note where :CP_Activity_Notes_Selected = 0 and parent_id is not null ) eligible_child_notes where inner_man2.activity_note_id = inner_an2.id and inner_man2.metric_id = man2.metric_id and inner_an2.parent_id = an2.id --- join and eligible_child_notes.value_numeric = inner_an2.id ) -- JK 2-MAR-2009 let's try with activity note Id, not metric-activity-note Id -- and man2.id = :starting_point_id and man2.activity_note_id = :starting_point_id and :P_Report_Type_ID = 7 ) m , M_BUSINESS_E_REF mber , VALUE_LIST mtype , VALUE_LIST freq , VALUE_LIST mu_type , VALUE_LIST mlvl , (SELECT id, title, 'Measure Class:' mclass_prompt FROM VALUE_LIST mclass WHERE mclass.TYPE = 'MEASURE_CLASS') mcls , (SELECT id, title FROM VALUE_LIST WHERE TYPE = 'MEASURE_REPORTING_LEVEL') reporting_to -- **************************************************************************** -- nested (simplified) table to get reporting statuses and note - S T A R T -- *************************************************************************** ,( SELECT a.object_id , a.status_id , reporting_status_values.title as status , notes.description as overall_comment FROM report_status a , (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values , ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id) notes WHERE a.sub_domain_id = 2 and ( a.ATTRIBUTE is null ) and a.ACTIVE = 1 -- join softly VALUE_LIST table(s) ONCE for EACH attribute and reporting_status_values.id(+) = a.status_id -- join softly NOTE table(s) and notes.object_class_id(+) = a.id ) reporting_status_data -- **************************************************************************** -- nested (simplified) table to get reporting statuses and note - E N D -- *************************************************************************** -- add value list to show 'actuals handling' , value_list act_type , value_list measure_unit_type WHERE m.ber_id = mber.id AND mtype.type = 'MEASURE_TYPE' AND m.type_id = mtype.id AND m.recording_frequency_id = freq.id AND m.measure_unit_type_id = mu_type.id(+) -- Oct 22 this should be NOT outer join AND m.level_id = mlvl.id -- JK Jan-2009 class is not mandatory so change 'm.class_id(+) = mcls.id' to 'm.class_id = mcls.id(+) ' AND m.class_id = mcls.id(+) AND reporting_status_data.object_id(+) = m.id AND reporting_to.id(+) = m.reporting_level_id AND m.actuals_handling_id = act_type.id AND m.measure_unit_type_id = measure_unit_type.id(+) &CF_Q_MEASUREMENT_MEASUR_FILTER ORDER BY 1,2 Q_PARENT_ENTITY SELECT DISTINCT ref.object_id as parent_id , ref.sub_domain_id as domain_id FROM m_business_e_ref ref -- nesting union of 3 tables is actually needed here because in some circumstances, -- all three types (domains) of parent entities will be listed , ( select bf.id, bf.business_number from ba_file bf &CP_Q_PARENT_FILES_FILTER union all select s.id, s.business_number from submission s &CP_Q_PARENT_SUBMISSIONS_FILTER union all select bs.id, bs.business_number from ba_strategy bs &CP_Q_PARENT_STRATEGIES_FILTER ) parent_entity WHERE parent_entity.id = ref.object_id and ( ( :P_Report_Type_ID in (2,1) and ref.id = (select ber_id from m_measure where id = :p_object_id) AND ref.sub_domain_id in (1,3,4)) OR ( :P_Report_Type_ID=4 and ref.object_id = :p_object_id AND ref.sub_domain_id = 4 ) OR ( :P_Report_Type_ID=1 and ref.object_id = :p_object_id AND ref.sub_domain_id = 1 ) OR ( :P_Report_Type_ID=3 and ref.sub_domain_id = :driver_domain and ref.object_id = :driver_id ) OR ( :P_Report_Type_ID=7 and ref.sub_domain_id in (1,3,4) and ref.sub_domain_id = :driver_domain and ref.object_id = :driver_id ) OR ( :P_Report_Type_ID=5 and ref.sub_domain_id in (4) ) -- there is NO object_id in this clause OR ( :P_Report_Type_ID=6 and ref.sub_domain_id = :driver_domain and ref.object_id = :driver_id) ) ORDER BY 1 Q_GRAPHING_VALUES -- Jaroslav, Aug 12 2008 added M_TARGET_PORTFOLIO to the query, -- which probably makes M_GRAPHING_VALUE and nested 'a' table obsolete -- that was meant for VARIABLE number of target sets. -- Anyway, I will leave it for now. -- NOTE: watch out when introducing column aliases as &CP_..... -- that column might not show up AT ALL so query-rebuild might be the only solution -- JK: 25-Jan-2009 - recode SELECT gv.graphing_id , gv.label as x_axis_value , gv.measure_value as ACTUAL_VALUE1 , gv.target_value as TARGET_VALUE1 , gv.TARGET_HIGH_VALUE , g.measure_id as graphing_measure_id , gv.id as graphing_value_id FROM m_graphing g , m_graphing_value gv WHERE gv.GRAPHING_ID = g.id and g.measure_id = :measurement_id -- value from the parent table -- this is taking the value from parent query and basically killing the result set if graph is marked as 'User Provided' and :system_or_user_generated = 'System Generated' ORDER BY gv.graphing_id -- this is to try to solve the problm when x-axis labls were NOT in order , gv.id Q_GRAPH_DATA_TABULAR_VIEW /* JK Jan 8 2009 add 'm_graphing' table to second query in UNION ALL clause. It's NOT to get any data for a result set but to use it as a filter to make sure that only data from a single target set is displayed It used to be 'm_target_portfolio.is_master' column used for that, and now is m_graphing.target_portfolio_id' */ SELECT a.record_date as gr_record_date -- Jan 29 2009 - using "min()" here because "sum()" would not take non-digits. Should be the same result, as there is , MIN( to_char( a.target_value ) || DECODE( a.high_value, 0, '', ' to ' || to_char(a.high_value) ) ) as gr_target_value , sum(a.period_value) as gr_actual_value , sum(a.total_value) as gr_total_to_date , :measurement_id as raw_data_measure_id FROM ( -- first union provides only actual values, and always null as 'Target Value' select a.record_date , Null as target_value , a.period_value , a.total_value , a.measure_id , 0 high_value from m_actual_record a where a.measure_id = :measurement_id union all -- second union provides only 'Target Value'' and nulls instead of actual values select t.RECORD_DATE -- JK Sep 24, 2008 do note that in m_target_record, the values are in VALUE column, unlike in m_actual_record where it's in PERIOD_VALUE , t.value as PERIOD_VALUE , Null , Null , tp.MEASURE_ID , t.high_value from m_target_record t , m_target_portfolio tp , m_graphing g where tp.MEASURE_ID = :measurement_id and t.TARGET_PORTFOLIO_ID = tp.ID -- JK 16-DEC-2008 obsolete check: and tp.IS_MASTER = 1 and g.target_portfolio_id = tp.id and g.measure_id = tp.measure_id ) a GROUP BY a.RECORD_DATE ORDER BY a.RECORD_DATE Q_LEGENDS ---------------------------------------------------------- ------------ build legends query -------------- ---------------------------------------------------------- -- as of Rel 3.0, there will be only one record (per measurement) in 'm_graphing' table SELECT a.title as measurements_set_name, rownum, a.id as graphing_id, measure_id as legend_measure_id FROM ( select distinct 'Targets (' || tp3.TITLE || ')' as title , g3.id , tp3.MEASURE_ID from m_target_portfolio tp3, m_graphing g3 where tp3.MEASURE_ID = :measure_id and g3.TARGET_PORTFOLIO_ID = tp3.id -- JK 16-DEC-2008 obsolete check: and tp3.is_master = 1 UNION ALL -- retrieval from 'dual' that will ensure that acutals record is always there select 'Actuals', 0, :measure_id as measure_id from dual UNION ALL -- third part of the union, will return a result set only id targets are declaed as ranges select distinct 'Targets High Values' as title , g3.id , tp3.MEASURE_ID from m_target_portfolio tp3 , m_graphing g3, value_list vl where tp3.MEASURE_ID = :measure_id and g3.TARGET_PORTFOLIO_ID = tp3.id and vl.type = 'MEASURE_TARGET_DEFINITION' and tp3.definition_id = vl.id and vl.title = 'Range' ) a -- JK Oct 23, can I assume :CP_SERIES_COUNT is 1 all the time ???? -- WHERE rownum <= :CP_SERIES_COUNT + 1 -- adding one is because actuals are handled differently WHERE rownum <= 3 -- this is taking the value from parent query and basically killing the result set if graph is marked as 'User Provided' and :system_or_user_generated = 'System Generated' ORDER BY 3 Q_GRAPH_OF_MASTER_TARGET --------------------------------------------------------------------------------------------------------------------------- ------------------- NOTE: this will retun a SINGLE graphing id, but only because target portfolio ------------------- selected is MASTER target portfolio ------------------- otherwise, it one-to_many relationsio between measure and graphs -------------------------------------------------------------------------------------------------------------------------- SELECT a.MEASURE_ID , a.id as graphing_id , vl.title as system_or_user_generated , TP.STATEMENT_OF_RESULT -- add image for measure. -- there can be many graphs for a single measurement, but as we will -- consider only MASTER portfolio, effectively there will be one image per measurement ,image_source.co_docid ,image_source.co_versioncomment ,image_source.co_filecontent -- similar rational as for images - read m_graphing data of master target portfolio only. in order to figure out graph type , graph_type_nt.Line_or_Bar FROM m_graphing a , value_list vl , m_target_portfolio tp -- TODO explain nested 'image_siurce' table , (select doc_versions.co_docid, doc_versions.co_versioncomment, doc_versions.co_filecontent, :measurement_id as measure_id from (select b.co_menutext, b.co_menuid, level, sys_connect_by_path( co_menutext, '\' ) path from co_menus b connect by prior b.co_menuid = b.co_menuparent start with b.co_menuid in ( select a.co_menuid from co_menus a where a.co_menuparent = 0 and a.co_menuref = -- s t a r t give me graphing id that corresponds to master portfolio, for known measure id (select '22_' || to_char(nvl(max(g2.id),0)) from m_graphing g2, m_target_portfolio tp2 where g2.measure_id = :measurement_id and tp2.measure_id = g2.measure_id and tp2.id = g2.target_portfolio_id -- JK 16-DEC-2008 obsolete check: and tp2.is_master = 1 ) -- e n d give me graphing id that corresponds to master portfolio, for known measure id ) ) cm , co_document doc , document_type dt , ( select ver.co_docid, ver.co_versioncomment, ver.co_filecontent from co_versions ver where ver.co_version = ( select max(ver2.co_version) from co_versions ver2 where ver2.co_docid = ver.co_docid) -- skip all but .bmp, .jpg and .gif files (JK 5-Mar-2009) and upper(substr(ver.co_filename, instr(ver.co_filename, '.', -1, 1) + 1)) in ('JPG', 'BMP', 'GIF') ) doc_versions where doc.co_menuid = cm.co_menuid and doc.co_doctype = dt.id(+) -- join ('vrsions' ) table that contains the image and doc.co_docid = doc_versions.co_docid ) image_source -- create table that'll give graph type for master target set Line or Bar, based on 'm_graphing.format_id' value -- 'decode' is to translate to Report Builder dictionary , ( select vl2.id , DECODE(vl2.title, 'Line', 'LINE_VERT_ABS', 'Bar', 'BAR_VERT_CLUST', 'LINE_VERT_ABS' ) as Line_or_Bar from value_list vl2 where vl2.type = 'MEASURE_GRAPH_PRESENTATION' ) graph_type_nt where a.MEASURE_ID = :measurement_id and vl.type = 'MEASURE_GRAPH_TYPE' and vl.id = a.TYPE_ID and a.TARGET_PORTFOLIO_ID = tp.id -- JK 16-DEC-2008 obsolete check: and tp.IS_MASTER = 1 -- image part might not have anything and a.MEASURE_ID = image_source.measure_id(+) -- join nested table to give us graph type and graph_type_nt.id = a.format_id Q_FILE -- For simple Measure summary, parent label is 'Belongs To:', and title is prefixed by 'File:'. -- Otherwise, parent label is 'File:'. SELECT DECODE(:P_Report_Type_ID, 2, 'Belongs To:', 7, 'Belongs To:', 'File:') parent_file_label, DECODE(:P_Report_Type_ID, 2, 'File: ', 7, 'File: ', '') || f.title || ' (' || f.BUSINESS_NUMBER || ')' as file_name , f.id as file_id , vl.title as file_status , rs.title as file_reporting_status , rs.status_id as file_reporting_status_id FROM ba_file f , value_list vl , ( select a.object_id , a.status_id , reporting_status_values.title from report_status a , (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values where a.sub_domain_id = 4 and ( a.ATTRIBUTE is null ) and a.ACTIVE = 1 -- join softly VALUE_LIST table(s) ONCE for EACH attribute and reporting_status_values.id(+) = a.status_id ) rs WHERE f.id = :parent_id and :domain_id = 4 and vl.type = 'EASY_STATUS' and vl.id = f.status_id and f.id = rs.object_id(+) Q_FILE_UNITS select fu.FILE_ID as unit_file_id , reports_pkg.getEntireUnitPath( fu.unit_id, reports_pkg.GETUNITLASTNAME(u.id )) as file_division from file_unit fu, unit u where u.ID = fu.UNIT_ID and fu.file_id = :file_id Q_MNGMNT_CATEGORIES -- Jaroslav Feb 24 2009 -- I guess this (sorting) fix should work - forst category scope ('sort_by_scope'), then cateory name ('category_title') and then level ('sorting_sub_categories') -- also, I don't see why we need 'category_scope' any more. Commenting it out. -- ****************************************************************************************************************** select --DECODE(SUBSTR( upper(category_scope.title),1,3), 'OPS', 'OPS Wide Categories', 'MIN', 'Ministry Wide Categories', 'Division Wide Categories') as category_scope mc.TITLE as category_title , LPAD(' ', (LEVEL - 1)*7) || lvel.TITLE || ': ' || value.title as sub_categories_tree , DECODE(SUBSTR( upper(category_scope.title),1,3), 'OPS', 1, 'MIN', 2, 3 ) as sort_by_scope , LEVEL as sorting_sub_categories from MGMT_CATEGORY_VALUE value , MGMT_CATEGORY_LEVEL lvel , MANAGEMENT_CATEGORY mc , (select title, id from value_list where type = 'MANAGEMENT_CATEGORY_SCOPE') category_scope where value.MGMT_CATEGORY_LEVEL_ID = lvel.ID and mc.ID = lvel.MANAGEMENT_CATEGORY_ID and category_scope.id = mc.SCOPE_ID and mc.ENABLED = 1 and value.id in ( -- nested_2: expanded a list from 'nested_1' with parent IDs select distinct id from MGMT_CATEGORY_VALUE START WITH id in ( -- nested_1 : list only values linked to to given file ID select value2.id from MGMT_CATEGORY_VALUE value2 ,FILE_MANAGEMENT_CATEGORY fmc2 where fmc2.FILE_ID = :FILE_ID and value2.id = fmc2.MGMT_CATEGORY_VALUE_ID ) CONNECT BY PRIOR PARENT_ID = id ) START WITH value.PARENT_ID is null CONNECT BY PRIOR value.id = value.PARENT_ID order by 3, 1, 4 Q_FILE_STRATEGY select distinct so.file_id as strategy_file_id -- , so.STRATEGY_ID -- , so.OBJECTIVE_ID , s.TITLE || ' (' || s.BUSINESS_NUMBER || ')' as file_strategy_name -- , o.TITLE as objective_name from FILE_STRATEGY_OBJECTIVE so , ba_strategy s , ba_strategy_objective o where s.ID = so.STRATEGY_ID and o.ID(+) = so.OBJECTIVE_ID and so.FILE_ID = :file_id Q_SUBMISSION select DECODE(:P_Report_Type_ID, 2, 'Belongs To:', 7, 'Belongs To:', 'Submission:') parent_submission_label, DECODE(:P_Report_Type_ID, 2, 'Submission: ', 7, 'Submission: ', '') || DECODE(s.PREV_ADMIN_FLAG, 0, s.title || ' (' || s.BUSINESS_NUMBER || ')', 1, DECODE(NVL(r.PREV_ADMIN_ACCESS, 0), 1, s.title || ' (' || s.BUSINESS_NUMBER || ')', s.BUSINESS_NUMBER)) as submission_name , vl.title || ' / ' || st.title as submission_type , s.id as submission_id , vl2.title as sub_status from submission s INNER JOIN submission_type st ON st.id=s.submission_type_id INNER JOIN value_list vl ON vl.id = st.submission_category_id INNER JOIN value_list vl2 ON vl2.id = s.status_id LEFT JOIN ( SELECT DISTINCT 1 PREV_ADMIN_ACCESS FROM REPORT_VALID_SET rvs INNER JOIN EASY_USER eu ON eu.ID=rvs.CREATED_BY_USER_ID INNER JOIN ROLE_PORTFOLIO rp ON rp.EASY_USER_ID=eu.ID INNER JOIN EASY_ROLE er ON er.ID=rp.EASY_ROLE_ID INNER JOIN UNIT u ON u.ID=er.UNIT_ID INNER JOIN MER_PORTFOLIO mp ON mp.EASY_ROLE_ID=rp.EASY_ROLE_ID INNER JOIN MER_PORTFOLIO_FUNCTION_GROUP mpfg ON mpfg.MER_PORTFOLIO_ID=mp.ID INNER JOIN FUNCTION_GROUP fg ON fg.ID=mpfg.FUNCTION_GROUP_ID INNER JOIN RESOURCE_ACCESS_PRIVILEGE rap ON rap.FUNCTION_GROUP_ID=fg.ID INNER JOIN RESOURCE_1 r ON r.ID = rap.RESOURCE_ID WHERE rvs.ID=:P_Report_Valid_Set_ID AND u.MINISTRY_ID=:P_MIN_ID AND r.NAME='previous.government' AND r.ATTRIBUTE='submission' AND rap.PRIVILEGE!=0 ) r ON 1=1 where s.id = :parent_id and :domain_id = 1 and vl2.type = 'EASY_STATUS' Q_files_to_sub_item select distinct ref.object_id as sub_submission_id , f.file_id as sub_file_id , ba_file.title || ' (' || ba_file.business_number || ')' as sub_file_name from ba_ber_file f -- join table that shows what type of the referenced object , ba_business_entity_ref ref , ba_file where ref.id = f.ber_id -- keep only submissions and ref.sub_domain_id = 1 -- join to submission table and ref.object_id = :submission_id -- join file and ba_file.id = f.file_id Q_strategies_to_sub_item select distinct ref.object_id as strat_to_sub_sub_id -- ,f.file_id , s.TITLE || ' (' || s.BUSINESS_NUMBER || ')' as sub_strategy_name -- , s.id as sub_strategy_id from ba_ber_file f -- join table that shows what type of the referenced object , ba_business_entity_ref ref , ba_file , FILE_STRATEGY_OBJECTIVE so , ba_strategy s , ba_strategy_objective o where ref.id = f.ber_id -- keep only submissions and ref.sub_domain_id = 1 -- join to sun table and ref.object_id = :sub_submission_id -- join file and ba_file.id = f.file_id -- join strategies to file and so.FILE_ID = f.file_id and s.ID = so.STRATEGY_ID and o.ID(+) = so.OBJECTIVE_ID Q_STRATEGY -- Select all Strategies that are related to the current measure, plus -- any Strategic Objectives (but only for Measure Summary version of report) -- For simple Measure summary, parent label is 'Belongs To:', and title is prefixed by 'Strategy:'. -- Otherwise, parent label is 'Strategy:'. select DECODE(:P_Report_Type_ID, 2, 'Belongs To:', 7, 'Belongs To:', 'Strategy:') parent_strategy_label, DECODE(:P_Report_Type_ID, 2, 'Strategy: ', 7, 'Strategy: ', '') || str.title || ' (' || str.BUSINESS_NUMBER || ')' as strategy_name , str.id as strategy_id , so.title as strategy_objective , vl.title as str_status , rs.title as str_reporting_status , rs.status_id as str_reporting_status_id from BA_STRATEGY str, (select * from M_METRIC_STRATEGY_OBJECTIVE where metric_id = :P_Object_ID and :P_Report_Type_ID = 2) mso, BA_STRATEGY_OBJECTIVE so , value_list vl , ( select a.object_id , a.status_id , reporting_status_values.title from report_status a , (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values where a.sub_domain_id = 3 and ( a.ATTRIBUTE is null ) and a.ACTIVE = 1 -- join softly VALUE_LIST table(s) ONCE for EACH attribute and reporting_status_values.id(+) = a.status_id ) rs where str.id = :parent_id and :domain_id = 3 and str.id = mso.strategy_id(+) and mso.objective_id = so.ID(+) and vl.type = 'EASY_STATUS' and vl.id = str.status_id and str.id = rs.object_id(+) Q_RELATED_MEASUREMENTS /* SELECT DISTINCT DECODE(m_rel.relation_type, 'LEVEL', DECODE(:lvl_of_measure, 'Output', 'Outcome', 'Outcome', 'Output'), 'HISTORY', DECODE(m_rel.reltype, 'parent', 'History - Source', 'History - New') ) as m_rel_relation_type, m.business_id as m_related_number, m.title as m_related_title, :measurement_id as m_related_id FROM m_measure m , (select relation_type, child_measure_id as related_measure_id, 'child' as reltype from m_measure_measure mm where parent_measure_id = :measurement_id union all select relation_type, parent_measure_id, 'parent' as reltype from m_measure_measure mm where child_measure_id = :measurement_id ) m_rel WHERE m.id = m_rel.related_measure_id */ SELECT DISTINCT DECODE(m_rel.relation_type, 'LEVEL', DECODE(:lvl_of_measure, 'Output', 'Outcome', 'Outcome', 'Output'), -- JK 7-MAY-09 Add Rollup. Don't knmow why it wasn't there 'ROLLUP', 'Rollup', 'HISTORY', DECODE(m_rel.reltype, 'parent', 'History - Source', 'History - New') ) as m_rel_relation_type, m.business_id as m_related_number, m.title as m_related_title, :measurement_id as m_related_id FROM m_measure m , (select relation_type, child_measure_id as related_measure_id, 'child' as reltype from m_measure_measure mm where parent_measure_id = :measurement_id -- JK 7-MAY-09 do not show Rollup if base neasure is a source of a rollup measure. The app has a tab for it and relation_type != 'ROLLUP' union all select relation_type, parent_measure_id, 'parent' as reltype from m_measure_measure mm where child_measure_id = :measurement_id ) m_rel WHERE m.id = m_rel.related_measure_id Q_REL_ACTIVITY_NOTES SELECT man.metric_id as activity_measurement_id , man.activity_note_id , an.business_number as activity_note_number , an.title as activity_note_title FROM m_metric_activity_note man, activity_note an WHERE an.id = man.activity_note_id and man.metric_id = :measurement_id Q_DRIVER SELECT * FROM TABLE( R_MEASURESUMMARY.GETDRIVERS( :p_min_id, :p_prompt_value_set_id, :CP_Prompt_Runtime_Value_Set_ID, :p_report_valid_set_id, :p_report_type_id, :p_object_id, :CP_ACTIVITY_NOTES_SELECTED, :CP_ORG_UNITS_SELECTED, :CP_ORG_UNITS_SELECTED_SR, :CP_MC_LEVEL_ID ) ); Q_strategy_units select su.Strategy_ID as unit_strategy_id , reports_pkg.getEntireUnitPath( su.unit_id, reports_pkg.GETUNITLASTNAME(u.id )) as strategy_division from ba_strategy_unit su, unit u where u.ID = su.UNIT_ID and su.strategy_id = :strategy_id Q_Submission_Lead_Units -- all lead units select su.submission_ID as unit_lead_submission_id , reports_pkg.getEntireUnitPath( su.unit_id, reports_pkg.GETUNITLASTNAME(u.id )) as submission_lead_division from submission_unit su, unit u, value_list vl where u.ID = su.UNIT_ID and su.submission_id = :submission_id and su.type_id = vl.id and vl.type = 'CONTACT_MINISTRY_TYPE' and vl.value_id = 1 Q_Submission_Affected_Units -- all 'affected' units select su.submission_ID as unit_aff_submission_id , reports_pkg.getEntireUnitPath( su.unit_id, reports_pkg.GETUNITLASTNAME(u.id )) as submission_aff_division from submission_unit su, unit u, value_list vl where u.ID = su.UNIT_ID and su.submission_id = :submission_id and su.type_id = vl.id and vl.type = 'CONTACT_MINISTRY_TYPE' and vl.value_id = 2 Q_CHART_ATTRIBUTES SELECT c.id as basic_chart_id , c.title as chart_title , c.header as chart_header , c.footer as chart_footer , c.period_start as chart_start_dt , c.period_end as chart_end_dt , freq.freq_title , c.description as chart_description , c.data_source as chart_data_source , rs.status as chart_reporting_status , rs.overall_comment as chart_rep_status_comment , parent.chart_parent_id , parent.sub_domain_id -- JK Apr 9 2009 , reports_pkg.getentireunitpath( c.unit_id, reports_pkg.getunitlastname(c.unit_id)) as Chart_Org_Unit , lower( c.title ) as Sort_1 FROM m_chart c , ( select id as freq_id, title as freq_title from value_list where type = 'MEASURE_RECORDING_FREQUENCY' ) freq -- nested table that gived ber_id for given parent S T A R T , ( select ber.id as chart_ber_id, f.id as chart_parent_id, ber.sub_domain_id from m_business_e_ref ber, ba_file f where ber.sub_domain_id = 4 and f.id = ber.object_id and f.id = :driver_id and ber.sub_domain_id = :driver_domain union all select ber.id, str.id, ber.sub_domain_id from m_business_e_ref ber, ba_strategy str where ber.sub_domain_id = 3 and str.id = ber.object_id and str.id = :driver_id and ber.sub_domain_id = :driver_domain union all select ber.id , s.id , ber.sub_domain_id from m_business_e_ref ber, submission s where ber.sub_domain_id = 1 and s.id = ber.object_id and s.id = :driver_id and ber.sub_domain_id = :driver_domain ) parent -- nested table that gived ber_id for given parent E N D -- nested table with reporting status and comment S T A R T , ( select a.object_id , a.status_id , reporting_status_values.title as status -- comments from NOTE table , notes.description as overall_comment from report_status a , (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values , ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id) notes where a.sub_domain_id = 19 and ( a.attribute is null ) and a.active = 1 -- join softly VALUE_LIST table(s) ONCE for EACH attribute and reporting_status_values.id(+) = a.status_id -- join softly NOTE table(s) and notes.object_class_id(+) = a.id ) rs -- nested table with reporting status and comment E N D , v_report_valid_data vld_charts WHERE parent.chart_ber_id = c.ber_id and c.recording_frequency_id = freq_id(+) and c.id = rs.object_id(+) -- limit only to charts that we are alowed to see and c.id = vld_charts.object_id and vld_charts.sub_domain_id = 19 and vld_charts.report_valid_set_id = :P_Report_Valid_Set_ID ORDER BY Sort_1 Q_CHART SELECT c.id as chart_id, ch_rows.row_number, ch_rows.row_id, ch_cols.*, cl.data as data, cl.style_id as cell_style FROM m_chart c, ( select r.chart_data_id, r.row_number, r.id as row_id from chart_row r ) ch_rows, ( select col.chart_data_id, col.column_number, col.id as column_id from chart_column col where col.column_number < 8 ) ch_cols, ( select c1.chart_data_id, c1.chart_row_id, c1.chart_column_id, c1.data, c1.style_id from chart_cell c1 union all select ro.chart_data_id, ro.id as chart_row_id, co.id as chart_column_id, null as data, to_number(null) from chart_row ro, chart_column co, m_chart chart where chart.id = :basic_chart_id AND ro.chart_data_id = chart.chart_data_id AND co.chart_data_id = chart.chart_data_id and NOT EXISTS ( select null from chart_cell ce where ce.chart_data_id = ro.chart_data_id and ce.chart_row_id = ro.id and ce.chart_column_id = co.id ) ) cl WHERE c.id = :basic_chart_id and ch_rows.chart_data_id = c.chart_data_id and ch_cols.chart_data_id = c.chart_data_id and cl.chart_data_id = c.chart_data_id and cl.chart_row_id = ch_rows.row_id and cl.chart_column_id = ch_cols.column_id AND c.id IN ( SELECT rvd.object_id FROM REPORT_VALID_SET rvs, REPORT_VALID_DATA rvd WHERE rvs.id = rvd.report_valid_set_id AND rvd.sub_domain_id = 19 AND rvd.report_valid_set_id = :P_Report_Valid_Set_ID AND rvs.session_id = :P_Session_ID ) order by c.id, ch_rows.row_number, ch_cols.column_number;