**************************************************************************************************** Location : Local or Network Folders Host : FINDITEZ-T460P Path : C:\Users\ken\Documents\Test Cases\Oracle-Reports\StrategySummary.xml **************************************************************************************************** -------------------------------------------------- -------------------------------------------------- -- Command(s) Q_STRATEGY SELECT bs.id, bs.title strategy_title, bs.business_number, bs.description strat_desc, bs.status_id, bs.target_id, bs.owner_unit_id, bs.start_date, bs.end_date, bs.create_by_id, bs.create_date, bs.update_by_id, bs.update_date, tr.title target, st.title strat_status, rs.status strat_rs, rs.overall_comment strat_rs_comments FROM BA_STRATEGY bs, VALUE_LIST tr, VALUE_LIST st , ( SELECT e.object_id -- IDs (just in case) and status descriptions , e.status_id, reporting_status_values.title as status , e.quality_status_id, quality_status_values.title as quality_status , e.budget_status_id, budget_status_values.title as budget_status , e.time_status_id, time_status_values.title as time_status -- comments from NOTE table , notes.description as overall_comment , quality_att_notes.description as comment_on_quality , time_att_notes.description as comment_on_time , budget_att_notes.description as comment_on_budget FROM -- ******* START nested table 'e' -- ******* all status IDs and corresponding descriptions are flattened to a single row per object_id -- ******* ( in a level above, use it to joinn to NOTE table to retrieve comments ) ( select d.object_id , SUM(status_id) as status_id , SUM(time_status_id) as time_status_id , SUM(quality_status_id) as quality_status_id , SUM(budget_status_id) as budget_status_id , SUM(report_id ) as report_id , SUM(time_status_report_id) as time_status_report_id , SUM(quality_status_report_id ) as quality_status_report_id , SUM(budget_status_report_id ) as budget_status_report_id from -- ******* START nested table 'd' -- ******* prepare reporting data for grouping by object_id -- ******* it basically ADDs columns to 'c' data, so the reaul set will have OBJECT_ID plus two fields for each 4 attributes -- ******* note that attributes themself are being omittedm but from the column names is obvious what are the refereing to ( select c.object_id , DECODE(c.attribute, null, status_id) as status_id , DECODE(c.attribute, 'Time', status_id) as time_status_id , DECODE(c.attribute, 'Quality', status_id) as quality_status_id , DECODE(c.attribute, 'Budget', status_id) as budget_status_id , DECODE(c.attribute, null, id) as report_id , DECODE(c.attribute, 'Time', id) as time_status_report_id , DECODE(c.attribute, 'Quality', id) as quality_status_report_id , DECODE(c.attribute, 'Budget', id) as budget_status_report_id from -- ******* START nested table 'c'' -- ******* list RELEVANT / LATEST reporting status IDs for eaach attribute -- ******* flag ACTIVE saves the day here, as there is always ONLY ONE record with ACTIVE == 1 for each of the -- ******* attributes { null, Time, Quality, Budget } ( select a.OBJECT_ID, a.ATTRIBUTE, a.STATUS_ID, a.id from report_status a , easy_domain -- join to filter out ALL but Files where a.sub_domain_id = easy_domain.ID and easy_domain.ID = 3 -- values: 1-SUBMISSION, 3-STRATEGY, 4-FILE, 6- BILL 2 - measurement and ( a.ATTRIBUTE in ('Time', 'Quality', 'Budget') or a.ATTRIBUTE is null ) and a.ACTIVE = 1 ) c -- ******* END nested table 'c' -- ****************************** ) d -- ******* END nested table 'd' -- ****************************** group by d.object_id ) e -- ******* END nested table 'e' -- ****************************** -- used for (vanila) status' description , (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values -- used for ('Time') status' description , (select id, title from value_list where type = 'REPORTING_STATUS' ) time_status_values -- used for ('Quality') status' description , (select id, title from value_list where type = 'REPORTING_STATUS' ) quality_status_values -- used for ('Budget') status' description , (select id, title from value_list where type = 'REPORTING_STATUS' ) budget_status_values -- value_id = 13 is 'Reporting Status' sub domain ID -- Aug 13, 2008 ok, it seems that value_id has silently disapeared so we'll use expression ' easy_domain.Title = 'Reporting_Status''instead , ( 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 , ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) quality_att_notes , ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) time_att_notes , ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) budget_att_notes WHERE -- join softly VALUE_LIST table(s) ONCE for EACH attribute reporting_status_values.id(+) = e.status_id and time_status_values.id(+) = e.time_status_id and quality_status_values.id(+) = e.quality_status_id and budget_status_values.id(+) = e.budget_status_id -- join softly NOTE table(s) ONCE for EACH attribute and notes.object_class_id(+) = e.report_id and quality_att_notes.object_class_id(+) = e.quality_status_report_id and time_att_notes.object_class_id(+) = e.time_status_report_id and budget_att_notes.object_class_id(+) = e.budget_status_report_id ) rs WHERE bs.target_id = tr.id AND tr.TYPE= 'MINISTRY_TARGET' AND bs.status_id = st.id AND st.TYPE = 'EASY_STATUS' and bs.id = :cp_strategy_id AND -- OUTER join bs.id = rs.object_id(+) AND bs.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 = 3 AND rvd.report_valid_set_id = :P_Report_Valid_Set_ID AND rvs.session_id = :P_Session_ID) Q_OBJECTIVES SELECT --'Objectives:' obj_hdr1, BA_STRATEGY_OBJECTIVE.TITLE st_objective, BA_STRATEGY_OBJECTIVE.STRATEGY_ID FROM BA_STRATEGY_OBJECTIVE Q_UNITS_IT_BELONGS_TO select bsu.unit_id, u.acronym , reports_pkg.GETENTIREUNITPATH( U.ID, reports_pkg.getUnitLastName(u.id)) AS unit_name , bsu.strategy_id u_strat from ba_strategy_unit bsu , unit u where bsu.unit_id = u.id /* select bsu.unit_id, u.acronym , reports_pkg.getUnitLastName(bsu.unit_id) unit_name , bsu.strategy_id u_strat from ba_strategy_unit bsu, unit u where bsu.unit_id = u.id */ Q_METRICS select mber.object_id meas_strategy , mm.title meas_title , m_utyp.title meas_type , mm.measure_unit , m_freq.title meas_freq , m_stat.title meas_status , decode(m_type.value_id, 2, 'Rollup Measures', 3, 'Rollup Measures', 'Measures') measure_type , mm.id as measurement_id , mm.business_id measure_number from m_business_e_ref mber , m_measure mm , value_list m_utyp , value_list m_freq , value_list m_stat , value_list m_type where mber.id = mm.ber_id and mm.measure_unit_type_id = m_utyp.id and m_utyp.type = 'MEASURE_UNIT_TYPE' and mm.recording_frequency_id = m_freq.id and m_freq.type = 'MEASURE_RECORDING_FREQUENCY' and mm.status_id = m_stat.id and m_stat.type = 'EASY_STATUS' and mm.type_id = m_type.id and m_type.type = 'MEASURE_TYPE' and mm.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 = 2 AND rvd.report_valid_set_id = :P_Report_Valid_Set_ID AND rvs.session_id = :P_Session_ID) Q_RELATED_FILES -- Aug 15, 2008 rewrote related files. to simpler, one hopefully SELECT f.title as file_name , f.business_number as file_number , rec_stat.title as file_status , reporting_status.status as file_reporting_status , reporting_status.overall_comment as file_reporting_status_comment , units.file_division , f.id file_id FROM file_strategy_objective fso , ba_file f , (select id, title from value_list where type = 'EASY_STATUS') rec_stat -- ************ n e s t e d r e p o r t i n g s t a t u s - S T A R T ******************** , ( SELECT e.object_id -- IDs (just in case) and status descriptions , e.status_id, reporting_status_values.title as status , e.quality_status_id, quality_status_values.title as quality_status , e.budget_status_id, budget_status_values.title as budget_status , e.time_status_id, time_status_values.title as time_status -- comments from NOTE table , notes.description as overall_comment , quality_att_notes.description as comment_on_quality , time_att_notes.description as comment_on_time , budget_att_notes.description as comment_on_budget FROM -- ******* START nested table 'e' -- ******* all status IDs and corresponding descriptions are flattened to a single row per object_id -- ******* ( in a level above, use it to joinn to NOTE table to retrieve comments ) ( select d.object_id , SUM(status_id) as status_id , SUM(time_status_id) as time_status_id , SUM(quality_status_id) as quality_status_id , SUM(budget_status_id) as budget_status_id , SUM(report_id ) as report_id , SUM(time_status_report_id) as time_status_report_id , SUM(quality_status_report_id ) as quality_status_report_id , SUM(budget_status_report_id ) as budget_status_report_id from -- ******* START nested table 'd' -- ******* prepare reporting data for grouping by object_id -- ******* it basically ADDs columns to 'c' data, so the reaul set will have OBJECT_ID plus two fields for each 4 attributes -- ******* note that attributes themself are being omittedm but from the column names is obvious what are the refereing to ( select c.object_id , DECODE(c.attribute, null, status_id) as status_id , DECODE(c.attribute, 'Time', status_id) as time_status_id , DECODE(c.attribute, 'Quality', status_id) as quality_status_id , DECODE(c.attribute, 'Budget', status_id) as budget_status_id , DECODE(c.attribute, null, id) as report_id , DECODE(c.attribute, 'Time', id) as time_status_report_id , DECODE(c.attribute, 'Quality', id) as quality_status_report_id , DECODE(c.attribute, 'Budget', id) as budget_status_report_id from -- ******* START nested table 'c'' -- ******* list RELEVANT / LATEST reporting status IDs for eaach attribute -- ******* flag ACTIVE saves the day here, as there is always ONLY ONE record with ACTIVE == 1 for each of the -- ******* attributes { null, Time, Quality, Budget } ( select a.OBJECT_ID, a.ATTRIBUTE, a.STATUS_ID, a.id from report_status a , easy_domain -- join to filter out ALL but Files where a.sub_domain_id = easy_domain.ID and easy_domain.ID = 4 -- values: 1-SUBMISSION, 3-STRATEGY, 4-FILE, 6- BILL 2 - measurement and ( a.ATTRIBUTE in ('Time', 'Quality', 'Budget') or a.ATTRIBUTE is null ) and a.ACTIVE = 1 ) c -- ******* END nested table 'c' -- ****************************** ) d -- ******* END nested table 'd' -- ****************************** group by d.object_id ) e -- ******* END nested table 'e' -- ****************************** -- used for (vanila) status' description , (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values -- used for ('Time') status' description , (select id, title from value_list where type = 'REPORTING_STATUS' ) time_status_values -- used for ('Quality') status' description , (select id, title from value_list where type = 'REPORTING_STATUS' ) quality_status_values -- used for ('Budget') status' description , (select id, title from value_list where type = 'REPORTING_STATUS' ) budget_status_values -- value_id = 13 is 'Reporting Status' sub domain ID -- Aug 13, 2008 ok, it seems that value_id has silently disapeared so we'll use expression ' easy_domain.Title = 'Reporting_Status'' instead , ( 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 , ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) quality_att_notes , ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) time_att_notes , ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) budget_att_notes WHERE -- join softly VALUE_LIST table(s) ONCE for EACH attribute reporting_status_values.id(+) = e.status_id and time_status_values.id(+) = e.time_status_id and quality_status_values.id(+) = e.quality_status_id and budget_status_values.id(+) = e.budget_status_id -- join softly NOTE table(s) ONCE for EACH attribute and notes.object_class_id(+) = e.report_id and quality_att_notes.object_class_id(+) = e.quality_status_report_id and time_att_notes.object_class_id(+) = e.time_status_report_id and budget_att_notes.object_class_id(+) = e.budget_status_report_id ) reporting_status -- ************ n e s t e d r e p o r t i n g s t a t u s - S T A R T ******************** -- add units related to associated files ,( select fu.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 ) units WHERE strategy_id = :P_OBJECT_ID and f.id = fso.file_id and rec_stat.id = f.status_id -- join 'divisions' and units.file_id = f.id -- join reporting status data and reporting_status.object_id(+) = f.id AND f.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 = 4 AND rvd.report_valid_set_id = :P_Report_Valid_Set_ID AND rvs.session_id = :P_Session_ID) Q_DOCUMENTS -- Sep 3, added the "latest version" nested table SELECT cm.co_menutext doc_pkg_templ_name , dt.title doc_type_title , doc.co_description doc_desc , doc.co_docname , :id doc_sub_id , co_sourceauthor doc_creator , vrsn.co_filename , to_date(co_docdate, 'MM/DD/YY HH:MI AM') doc_date , vrsn.co_version , doc.co_docid as doc_id , 'Documents:' as dummy_field FROM ( select b.co_menutext, b.co_menuid, level 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 TRIM(a.co_menuref) = '3_' || TO_CHAR(:id) ) ) cm , co_document doc , document_type dt , ( select ver.co_version, ver.co_versioncomment , ver.co_docid, ver.co_filename from co_versions ver where ver.co_version = ( select max(ver2.co_version) from co_versions ver2 where ver2.co_docid = ver.co_docid) ) vrsn WHERE doc.co_menuid = cm.co_menuid and doc.co_doctype = dt.id(+) and vrsn.co_docid = doc.co_docid Q_CONTACTS -- query for Summary Reports -- provides data to list contacts as tha application does, i.e. ministry, contact team, roles , users -- first sort criteria is contact potfolio's ministry, and current ministry is listed first ( 'sort_id ) select DECODE(u.ministry_id, :P_MIN_ID, -999, ABS(u.ministry_id)) as sort_id , reports_pkg.GETUNITLASTNAME( u.ministry_id ) as Contact_Portf_Ministry , cp.object_id as cont_object_id --, u.acronym as cont_unit , ct.title as cont_team_title , cr.title as cont_role_title , i.first_name || ' ' || i.last_name cont_name from easy_contact_portfolio cp , CONTACT_GROUP cg, CONTACT c , contact_role cr, contact_team ct , individual i , unit u , easy_domain d where cp.id = cg.EASY_CONTACT_PORTFOLIO_ID and cg.id = c.CONTACT_GROUP_ID and c.contact_role_id = cr.id and ct.id = cg.contact_team_id and ct.id = cr.contact_team_id and c.status = 1 -- join individual and c.individual_id = i.id -- join unit table and cp.unit_id = u.id -- jOion domain table and d.id = cp.SUB_DOMAIN_ID -- filter all but our domain records and d.id = 3 -- :P_SUB_DOMAIN_ID -- 1 - SUBMISSION, 3 - STRATEGY, 4 - FILE, - BILL --and cp.object_id = :P_OBJECT_ID --order by 1, ct.title, cr.title, cont_name 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' The moment when 'm_graphing' table has more then one record - we are screwed SELECT a.record_date as gr_record_date , sum(a.target_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 zero as 'Target Value' select a.record_date , 0 as target_value , a.period_value , a.total_value from m_actual_record a where a.measure_id = :MEASUREMENT_ID union all -- second union provides only 'Target Value'' and zeros instead of actual values select t.RECORD_DATE -- , t.PERIOD_VALUE , t.VALUE , 0 , 0 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 --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 */ 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_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 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 graph_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 22-Jan-2009 OBSOLETE 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 11-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 22-Jan-2009 OBSOLETE 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_Legends /* ---------------------------------------------------------- ------------ build legends query -------------- ---------------------------------------------------------- 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_VALUE gv3, m_graphing g3 where tp3.MEASURE_ID = :measurement_id and g3.TARGET_PORTFOLIO_ID = tp3.id and gv3.GRAPHING_ID = g3.id -- JK 21-JAN-2009 obsolete check: and tp3.is_master = 1 UNION ALL -- select distinct 'Actuals', 0, measure_id from m_actual_record where measure_id = :measure_id -- Jaroslav Kolar, Sep 25, 2008 retieving from actualls is wating CPU cycles and more importantly, it does not return anything in case when actuals are NOT existing -- as a consequence, legend will not have a red box denoting actual values ... -- and at the same time, 'm_graphing_value' table willl contain (zero) values so the flat line at the bottom will be drawn -- That might be confusing ( line without legend explaining it ) and therefore this retrieval from 'dual' that will ensure that acutals record is always there select 'Actuals', 0, :measurement_id as measure_id from dual ) 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 <= 2 -- 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 */ ---------------------------------------------------------- ------------ 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 = :measurement_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, :measurement_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 = :measurement_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_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 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 /* SELECT gv.GRAPHING_ID , gv.label as x_axis_value , gv.MEASURE_VALUE as actual_value , gv.TARGET_VALUE , b.row_number -- , b.graphing_id , DECODE( b.row_number, 1, gv.MEASURE_VALUE) as ACTUAL_VALUE1 , DECODE( b.row_number, 1, gv.TARGET_VALUE) as TARGET_VALUE1 , DECODE( b.row_number, 2, gv.TARGET_VALUE) as TARGET_VALUE2 , DECODE( b.row_number, 3, gv.TARGET_VALUE) as TARGET_VALUE3 , DECODE( b.row_number, 4, gv.TARGET_VALUE) as TARGET_VALUE4 , DECODE( b.row_number, 5, gv.TARGET_VALUE) as TARGET_VALUE5 , DECODE( b.row_number, 6, gv.TARGET_VALUE) as TARGET_VALUE6 , DECODE( b.row_number, 7, gv.TARGET_VALUE) as TARGET_VALUE7 , DECODE( b.row_number, 8, gv.TARGET_VALUE) as TARGET_VALUE8 , DECODE( b.row_number, 9, gv.TARGET_VALUE) as TARGET_VALUE9 -- add measure id to be linked to 'measurement' table , g.measure_id as graphing_measure_id FROM m_graphing g , M_GRAPHING_VALUE gv , ( SELECT rownum as row_number, a.graphing_id FROM ( select distinct graphing_id from M_GRAPHING_VALUE gv2 , m_graphing g2 where gv2.GRAPHING_ID = g2.id and g2.measure_id in :measurement_id -- value from the parent table ) a ) b , m_target_portfolio tp WHERE gv.GRAPHING_ID = g.id and gv.GRAPHING_ID = b.GRAPHING_ID and tp.id = g.target_portfolio_id -- select only DOMINANT one -- JK 16-DEC-2008 obsolete check: and tp.is_master = 1 -- 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 */ Q_Chart_Attributes SELECT c.id as basic_chart_id, c.chart_data_id as chart_data_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 -- , c.style_id as chart_style , 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 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, str.id as chart_parent_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 = :id ) 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 WHERE parent.chart_ber_id = c.ber_id and c.recording_frequency_id = freq_id(+) and c.id = rs.object_id(+) Q_1 SELECT DECODE(SUBSTR( upper(category_scope.title),1,3), 'OPS', '1OPS-Wide Management Categories', 'MIN', '2'||:CP_Ministry_Acronym || '-Specific Management Categories', '3Division-Specific Management Categories') || ':' as category_scope , mc.TITLE AS category_title --, LPAD(' ', (LEVEL - 1)*7) || lpad(lvel.TITLE, 20) || ': ' || value.title AS sub_categories_tree --, lvel.title cat_hierarchy_title, value.title AS sub_categories_tree , LPAD(' ', (LEVEL - 1)*7) || lpad(lvel.TITLE, 20) cat_hierarchy_title, value.title AS sub_categories_tree , DECODE(SUBSTR( UPPER(category_scope.title),1,3), 'OPS', 1, 'MIN', 2, 3 ) AS sort_by_scope , value.id value_id 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 value2.id = fmc2.MGMT_CATEGORY_VALUE_ID AND fmc2.FILE_ID = :FILE_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 desc Q_2 --SELECT lvel.hierarchy_level , lvel.title hierarchy_title, value.title AS hierarchy_tree --SELECT rpad(lvel.TITLE, 20) || ': ' || value.title AS hierarchy_tree SELECT lvel.hierarchy_level , lvel.title hierarchy_title, LPAD(' ', (LEVEL - 1)*7) || lpad(value.TITLE, 20) AS hierarchy_tree 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 != :id 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 value2.id = fmc2.MGMT_CATEGORY_VALUE_ID ) CONNECT BY PRIOR PARENT_ID = id ) START WITH value.id = :id --value.PARENT_ID IS NULL CONNECT BY PRIOR value.parent_id = value.id ORDER BY lvel.hierarchy_level Q_BUILT_CHART SELECT :chart_data_id as data_chart_id, r.row_number as chart_row_number, min( decode(ac.column_number, 0, ac.data, null)) col1, min( decode(ac.column_number, 1, ac.data, null)) col2, min( decode(ac.column_number, 2, ac.data, null)) col3, min( decode(ac.column_number, 3, ac.data, null)) col4, min( decode(ac.column_number, 4, ac.data, null)) col5, min( decode(ac.column_number, 5, ac.data, null)) col6, min( decode(ac.column_number, 6, ac.data, null)) col7, min( decode(ac.column_number, 7, ac.data, null)) col8 FROM ( SELECT cell.data as data, col.column_number as column_number, cell.chart_data_id as chart_data_id, cell.chart_row_id as chart_row_id FROM chart_column col, chart_cell cell WHERE col.chart_data_id = :chart_data_id AND cell.chart_column_id(+) = col.id ) ac, chart_row r WHERE r.chart_data_id = :chart_data_id and ac.chart_row_id(+) = r.id GROUP BY r.row_number ORDER BY 1,2;