**************************************************************************** Location : Local or Network Folders Host : FINDITEZ-T460P Path : C:\Users\ken\Documents\Test Cases\Oracle-Reports\MeasureSummary.xml **************************************************************************** Data Sources Group: G_measure Data Item: meas_title Data Type: vchar2 Label: PM: Expression: m.title Data Item: object_id Data Type: vchar2 Label: Object Id Expression: mber.object_id Data Item: measurement_id Label: Measurement Id Expression: m.id Data Item: measure_unit Data Type: vchar2 Label: Measure Unit Expression: m.measure_unit Data Item: domain Label: Sub Domain Id Expression: mber.sub_domain_id Data Item: measure_type Data Type: vchar2 Label: Measure Type: Expression: mtype.title Data Item: rep_freq Data Type: vchar2 Label: Reporting Frequency: Expression: freq.title Data Item: data_source Data Type: vchar2 Label: Data Source: Expression: m.data_source Data Item: measure_number Data Type: vchar2 Label: Measure Number Expression: m.business_id Data Item: status_note Data Type: clob Label: Status Note: Expression: reporting_status_data.overall_comment Data Item: lvl_of_measure Data Type: vchar2 Label: Lvl Of Measure Expression: mlvl.title Data Item: measure_class Data Type: vchar2 Label: Measure Class Expression: mcls.title Data Item: mclass_prompt Data Type: vchar2 Label: Mclass Prompt Expression: mcls.mclass_prompt Data Item: rs_status Data Type: vchar2 Label: Rs Status Expression: reporting_status_data.status Data Item: reporting_to_name Data Type: vchar2 Label: Reporting To Name Expression: reporting_to.title Data Item: Measure_Org_Unit Data Type: vchar2 Label: Measure Org Unit Expression: reports_pkg.getentireunitpath ( m.unit_id , reports_pkg.getunitlastname ( m.unit_id ) ) Data Item: starting_point Label: Starting Point Expression: : starting_point_id Data Item: Actuals_Handling_Id Data Type: vchar2 Label: Actuals Handling Id Expression: act_type.value_id Data Item: m_unit_type Data Type: vchar2 Label: M Unit Type Expression: DECODE ( measure_unit_type.title , 'Financial Data' , '$' , 'Percentage' , '%' , '' ) Formula: CF_GRAPH_TITLE_TEXT Data-Type: character Label: Cf Graph Title Text Source: cf_graph_title_textformula Formula: CF_GRAPH_SUBTITLE_TEXT Data-Type: character Label: Cf Graph Subtitle Text Source: cf_graph_subtitle_textformula Formula: CF_DATA_VALUES Data-Type: character Label: Cf Data Values Source: cf_data_valuesformula Formula: CF_Actuals_Handling_Label Data-Type: character Label: Cf Actuals Handling Label Source: cf_actuals_handling_labelformu Group: G_entity Data Item: parent_id Data Type: vchar2 Label: Hierarchy Level Expression: ref.object_id Data Item: domain_id Label: Domain Id Expression: ref.sub_domain_id Group: G_GRAPHING_ID Data Item: GRAPHING_ID Label: Graphing Id Expression: gv.graphing_id Data Item: graphing_value_id Label: Graphing Value Id Expression: gv.id Data Item: TARGET_HIGH_VALUE Label: Target High Value Expression: gv.TARGET_HIGH_VALUE Data Item: graphing_measure_id Label: Graphing Measure Id Expression: g.measure_id Data Item: x_axis_value Data Type: vchar2 Label: X Axis Value Expression: gv.label Data Item: ACTUAL_VALUE1 Label: Actual Value1 Expression: gv.measure_value Data Item: TARGET_VALUE1 Label: Target Value1 Expression: gv.target_value Group: G_gr_record_date Data Item: gr_record_date Data Type: date Label: Gr Record Date Expression: a.record_date Data Item: gr_target_value Data Type: vchar2 Label: Gr Target Value Expression: MIN ( to_char ( a.target_value ) || DECODE ( a.high_value , 0 , '' , ' to ' || to_char ( a.high_value ) ) ) Data Item: gr_actual_value Label: Gr Actual Value Expression: sum ( a.period_value ) Data Item: gr_total_to_date Label: Gr Total To Date Expression: sum ( a.total_value ) Data Item: raw_data_measure_id Label: Raw Data Measure Id Expression: : measurement_id Formula: CF_First_Actuals_Column Data-Type: number Label: Cf First Actuals Column Source: cf_first_actuals_columnformula Group: G_measurements_set_name Data Item: measurements_set_name Data Type: vchar2 Label: Measurements Set Name Expression: a.title Data Item: rownum Label: Rownum Expression: rownum Data Item: graphing_id1 Label: Graphing Id1 Expression: a.id Data Item: legend_measure_id Label: Legend Measure Id Expression: measure_id Group: G_MEASURE_ID Data Item: MEASURE_ID Label: Measure Id Expression: a.MEASURE_ID Data Item: graphing_id2 Label: Graphing Id2 Expression: a.id Data Item: system_or_user_generated Data Type: vchar2 Label: System Or User Generated Expression: vl.title Data Item: co_docid Label: Co Docid Expression: image_source.co_docid Data Item: co_versioncomment Data Type: vchar2 Label: Co Versioncomment Expression: image_source.co_versioncomment Data Item: co_filecontent Data Type: blob Label: Co Filecontent Expression: image_source.co_filecontent Data Item: STATEMENT_OF_RESULT Data Type: vchar2 Label: Statement Of Result Expression: TP.STATEMENT_OF_RESULT Data Item: Line_or_Bar Data Type: vchar2 Label: Line Or Bar Expression: graph_type_nt.Line_or_Bar Group: G_FILE Data Item: file_name Data Type: vchar2 Label: File Name Expression: DECODE ( : P_Report_Type_ID , 2 , 'File: ' , 7 , 'File: ' , '' ) || f.title || ' (' || f.BUSINESS_NUMBER || ')' Data Item: file_id Label: File Id Expression: f.id Data Item: parent_file_label Data Type: vchar2 Label: Parent File Label Expression: DECODE ( : P_Report_Type_ID , 2 , 'Belongs To:' , 7 , 'Belongs To:' , 'File:' ) Data Item: file_status Data Type: vchar2 Label: File Status Expression: vl.title Data Item: file_reporting_status_id Label: File Reporting Status Id Expression: rs.status_id Data Item: file_reporting_status Data Type: vchar2 Label: File Reporting Status Expression: rs.title Group: G_FILE_UNITS Data Item: unit_file_id Label: Unit File Id Expression: fu.FILE_ID Data Item: file_division Data Type: vchar2 Label: File Division Expression: reports_pkg.getEntireUnitPath ( fu.unit_id , reports_pkg.GETUNITLASTNAME ( u.id ) ) Group: G_category Data Item: sort_by_scope Label: Sort By Scope Expression: DECODE ( SUBSTR ( upper ( category_scope.title ) , 1 , 3 ) , 'OPS' , 1 , 'MIN' , 2 , 3 ) Data Item: category_title Data Type: vchar2 Label: Category Title Expression: mc.TITLE Group: G_sub_categories Data Item: sorting_sub_categories Label: Sorting Sub Categories Expression: LEVEL Data Item: sub_categories_tree Data Type: vchar2 Label: Sub Categories Tree Expression: LPAD ( ' ' , ( LEVEL - 1 ) * 7 ) || lvel.TITLE || ': ' || value.title Group: G_file_strategy Data Item: strategy_file_id Label: Strategy File Id Expression: so.file_id Data Item: file_strategy_name Data Type: vchar2 Label: File Strategy Name Expression: s.TITLE || ' (' || s.BUSINESS_NUMBER || ')' Group: G_SUBMISSION Data Item: submission_id Label: Submission Id Expression: SUBMISSION_ID Data Item: submission_name Data Type: vchar2 Label: Submission Name Expression: SUBMISSION_NAME Data Item: submission_type Data Type: vchar2 Label: Submission Type Expression: SUBMISSION_TYPE Data Item: parent_submission_label Data Type: vchar2 Label: Parent Submission Label Expression: PARENT_SUBMISSION_LABEL Data Item: sub_status Data Type: vchar2 Label: Sub Status Expression: SUB_STATUS Group: G_file_to_sub Data Item: sub_submission_id Label: Sub Submission Id Expression: ref.object_id Data Item: sub_file_id Label: Sub File Id Expression: f.file_id Data Item: sub_file_name Data Type: vchar2 Label: Sub File Name Expression: ba_file.title || ' (' || ba_file.business_number || ')' Group: G_strategies_to_sub_item Data Item: strat_to_sub_sub_id Label: Strat To Sub Sub Id Expression: ref.object_id Data Item: sub_strategy_name Data Type: vchar2 Label: Sub Strategy Name Expression: s.TITLE || ' (' || s.BUSINESS_NUMBER || ')' Group: G_STRATEGY Data Item: strategy_name Data Type: vchar2 Label: Strategy Name Expression: DECODE ( : P_Report_Type_ID , 2 , 'Strategy: ' , 7 , 'Strategy: ' , '' ) || str.title || ' (' || str.BUSINESS_NUMBER || ')' Data Item: strategy_id Label: Id Expression: str.id Data Item: parent_strategy_label Data Type: vchar2 Label: Parent Strategy Label Expression: DECODE ( : P_Report_Type_ID , 2 , 'Belongs To:' , 7 , 'Belongs To:' , 'Strategy:' ) Data Item: str_reporting_status Data Type: vchar2 Label: Str Reporting Status Expression: rs.title Data Item: str_status Data Type: vchar2 Label: Str Status Expression: vl.title Data Item: str_reporting_status_id Label: Str Reporting Status Id Expression: rs.status_id Group: G_strategy_objective Data Item: strategy_objective Data Type: vchar2 Label: Strategy Objective Expression: so.title Group: G_RELATED_MEASUREMENTS Data Item: m_rel_relation_type Data Type: vchar2 Label: M Rel Relation Type Expression: DECODE ( m_rel.relation_type , 'LEVEL' , DECODE ( : lvl_of_measure , 'Output' , 'Outcome' , 'Outcome' , 'Output' ) , 'ROLLUP' , 'Rollup' , 'HISTORY' , DECODE ( m_rel.reltype , 'parent' , 'History - Source' , 'History - New' ) ) Data Item: m_related_title Data Type: vchar2 Label: M Related Title Expression: m.title Data Item: m_related_id Label: M Related Id Expression: : measurement_id Data Item: m_related_number Data Type: vchar2 Label: M Related Number Expression: m.business_id Group: G_REL_ACTIVITY_NOTES Data Item: activity_measurement_id Label: Activity Measurement Id Expression: man.metric_id Data Item: activity_note_id Label: Activity Note Id Expression: man.activity_note_id Data Item: activity_note_number Data Type: vchar2 Label: Activity Note Number Expression: an.business_number Data Item: activity_note_title Data Type: vchar2 Label: Activity Note Title Expression: an.title Group: G_DRIVER Data Item: SORTING_ORDER Data Type: vchar2 Label: Sorting Order Expression: SORTING_ORDER Data Item: DRIVER_TYPE Data Type: vchar2 Label: Driver Type Expression: DRIVER_TYPE Data Item: STARTING_POINT_ID Label: Starting Point Id Expression: STARTING_POINT_ID Data Item: DRIVER_DOMAIN Label: Driver Domain Expression: DRIVER_DOMAIN Data Item: DRIVER_ID Data Type: vchar2 Label: Driver Id Expression: DRIVER_ID Data Item: DRIVER_NAME Data Type: vchar2 Label: Driver Name Expression: DRIVER_NAME Group: G_strategy_units Data Item: unit_strategy_id Label: Unit Strategy Id Expression: su.Strategy_ID Data Item: strategy_division Data Type: vchar2 Label: Strategy Division Expression: reports_pkg.getEntireUnitPath ( su.unit_id , reports_pkg.GETUNITLASTNAME ( u.id ) ) Group: G_submission_lead_units Data Item: unit_lead_submission_id Label: Unit Lead Submission Id Expression: su.submission_ID Data Item: submission_lead_division Data Type: vchar2 Label: Submission Lead Division Expression: reports_pkg.getEntireUnitPath ( su.unit_id , reports_pkg.GETUNITLASTNAME ( u.id ) ) Group: G_submission_aff_units Data Item: unit_aff_submission_id Label: Unit Lead Submission Id1 Expression: su.submission_ID Data Item: submission_aff_division Data Type: vchar2 Label: Submission Lead Division1 Expression: reports_pkg.getEntireUnitPath ( su.unit_id , reports_pkg.GETUNITLASTNAME ( u.id ) ) Group: G_ATTRIBUTE_DATA Data Item: Sort_1 Data Type: vchar2 Label: Sort 1 Expression: lower ( c.title ) Data Item: chart_title Data Type: vchar2 Label: Chart Title Expression: c.title Data Item: basic_chart_id Label: Basic Chart Id Expression: c.id Data Item: chart_header Data Type: vchar2 Label: Chart Header Expression: c.header Data Item: chart_footer Data Type: vchar2 Label: Chart Footer Expression: c.footer Data Item: chart_start_dt Data Type: date Label: Period Start Expression: c.period_start Data Item: chart_end_dt Data Type: date Label: Period End Expression: c.period_end Data Item: freq_title Data Type: vchar2 Label: Freq Title Expression: freq.freq_title Data Item: chart_description Data Type: vchar2 Label: Description Expression: c.description Data Item: chart_data_source Data Type: vchar2 Label: Chart Data Source Expression: c.data_source Data Item: chart_reporting_status Data Type: vchar2 Label: Reporting Status Expression: rs.status Data Item: chart_rep_status_comment Data Type: clob Label: Reporting Status Comment Expression: rs.overall_comment Data Item: sub_domain_id Label: Sub Domain Id Expression: parent.sub_domain_id Data Item: chart_parent_id Label: Chart Parent Id Expression: parent.chart_parent_id Data Item: Chart_Org_Unit Data Type: vchar2 Label: Chart Org Unit Expression: reports_pkg.getentireunitpath ( c.unit_id , reports_pkg.getunitlastname ( c.unit_id ) ) Group: G_row_number Data Item: row_number Label: Row Number1 Expression: ch_rows.row_number Group: G_COLUMN_NUMBER Data Item: COLUMN_NUMBER Label: Column Number Expression: ch_cols.COLUMN_NUMBER Group: G_data Data Item: CHART_DATA_ID Label: Chart Data Id Expression: ch_cols.CHART_DATA_ID Data Item: row_id Label: Row Id Expression: ch_rows.row_id Data Item: CHART_ID1 Label: Chart Id1 Expression: c.id Data Item: COLUMN_ID Label: Column Id Expression: ch_cols.COLUMN_ID Data Item: data Data Type: vchar2 Label: Data Expression: cl.data Data Item: cell_style Label: Cell Style Expression: cl.style_id Report Parameters Parameter: P_MIN_ID Data Type: number Initial Values: 47 Parameter: P_OBJECT_ID Data Type: number Initial Values: -1 Parameter: P_SESSION_ID Data Type: character Initial Values: 1284745256872 Parameter: P_GRAPH_TYPE Data Type: character Initial Values: BAR_VERT_CLUST Parameter: P_Report_Type_ID Data Type: number Initial Values: 3 Parameter: SP_Prompt_Value_Set_ID Data Type: character Initial Values: 1072,937 Parameter: P_Report_Valid_Set_ID Data Type: number Initial Values: 26744 Parameter: P_STATUS_REQUEST_ID Data Type: number Tables Table: Q_MEASUREMENT SQL Command: -- ******************************************************************************************************************************* -- 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 Table: Q_PARENT_ENTITY SQL Command: 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 Table: Q_GRAPHING_VALUES SQL Command: -- 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 Table: Q_GRAPH_DATA_TABULAR_VIEW SQL Command: /* 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 Table: Q_LEGENDS SQL Command: ---------------------------------------------------------- ------------ 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 Table: Q_GRAPH_OF_MASTER_TARGET SQL Command: --------------------------------------------------------------------------------------------------------------------------- ------------------- 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 Table: Q_FILE SQL Command: -- 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(+) Table: Q_FILE_UNITS SQL Command: 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 Table: Q_MNGMNT_CATEGORIES SQL Command: -- 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 Table: Q_FILE_STRATEGY SQL Command: 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 Table: Q_SUBMISSION SQL Command: 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' Table: Q_files_to_sub_item SQL Command: 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 Table: Q_strategies_to_sub_item SQL Command: 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 Table: Q_STRATEGY SQL Command: -- 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(+) Table: Q_RELATED_MEASUREMENTS SQL Command: /* 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 Table: Q_REL_ACTIVITY_NOTES SQL Command: 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 Table: Q_DRIVER SQL Command: 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 ) ); Table: Q_strategy_units SQL Command: 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 Table: Q_Submission_Lead_Units SQL Command: -- 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 Table: Q_Submission_Affected_Units SQL Command: -- 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 Table: Q_CHART_ATTRIBUTES SQL Command: 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 Table: Q_CHART SQL Command: 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; Table Links Joins: where [G_measure].[measurement_id] = [Q_GRAPH_DATA_TABULAR_VIEW].[raw_data_measure_id] where [G_measure].[measurement_id] = [Q_GRAPH_OF_MASTER_TARGET].[MEASURE_ID] where [G_MEASURE_ID].[graphing_id2] = [Q_GRAPHING_VALUES].[GRAPHING_ID] where [G_measure].[measurement_id] = [Q_LEGENDS].[legend_measure_id] where [G_entity].[parent_id] = [Q_FILE].[file_id] where [G_FILE].[file_id] = [Q_FILE_UNITS].[unit_file_id] where [G_FILE] = [Q_MNGMNT_CATEGORIES] where [G_FILE].[file_id] = [Q_FILE_STRATEGY].[strategy_file_id] where [G_entity].[parent_id] = [Q_SUBMISSION].[submission_id] where [G_SUBMISSION].[submission_id] = [Q_files_to_sub_item].[sub_submission_id] where [G_SUBMISSION].[submission_id] = [Q_strategies_to_sub_item].[strat_to_sub_sub_id] where [G_entity].[parent_id] = [Q_STRATEGY].[strategy_id] where [G_measure].[measurement_id] = [Q_RELATED_MEASUREMENTS].[m_related_id] where [G_measure].[measurement_id] = [Q_REL_ACTIVITY_NOTES].[activity_measurement_id] where [G_entity].[parent_id] = [Q_MEASUREMENT].[object_id] where [G_DRIVER].[DRIVER_ID] = [Q_PARENT_ENTITY].[parent_id] where [G_DRIVER].[DRIVER_DOMAIN] = [Q_PARENT_ENTITY].[domain_id] where [G_STRATEGY].[strategy_id] = [Q_strategy_units].[unit_strategy_id] where [G_SUBMISSION].[submission_id] = [Q_Submission_Lead_Units].[unit_lead_submission_id] where [G_SUBMISSION] = [Q_Submission_Affected_Units] where [G_entity].[parent_id] = [Q_CHART_ATTRIBUTES].[chart_parent_id] where [G_entity].[domain_id] = [Q_CHART_ATTRIBUTES].[sub_domain_id] where [G_ATTRIBUTE_DATA].[basic_chart_id] = [Q_CHART].[CHART_ID1] Placeholder Columns Column: CP_Ministry_ID Data Type: number Label: Cp Ministry Id Column: CP_Measure_ID Data Type: number Label: Cp Measure Id Column: CP_Version Data Type: character Column: CP_data_series Data Type: character Column: CP_SERIES_COUNT Data Type: number Column: CP_REPORT_TITLE Data Type: character Column: CP_PARENT_ENTITY_ID Data Type: number Column: CP_DOMAIN_ID Data Type: number Column: CP_ACTUALS_COLOR Data Type: character Column: CP_MASTER_TARGET_COLOR Data Type: character Column: MEASURE_SUMMARY_NEW Data Type: character Column: CP_Q_PARENT_FILES_FILTER Data Type: character Column: CP_Q_PARENT_SUBMISSIONS_FILTER Data Type: character Column: CP_Q_PARENT_STRATEGIES_FILTER Data Type: character Column: CP_Q_MEASUREMENT_MEASUR_FILTER Data Type: character Column: CP_ORG_UNITS_SELECTED Data Type: number Column: CP_Is_Chart_Visible Data Type: number Column: CP_Activity_Notes_Selected Data Type: number Column: CP_MC_Level_Id Data Type: number Column: CP_Prompt_Runtime_Value_Set_ID Data Type: number Column: P_PROMPT_VALUE_SET_ID Data Type: number Column: CP_ORG_UNITS_SELECTED_SR Data Type: number Summary Columns Column: CS_PARENT_COUNT Source: parent_id Function: count Column: CS_MEASURE_COUNT2 Source: object_id Function: count Column: CS_GRAPHING_ID Source: GRAPHING_ID Function: count Column: CS_1 Source: legend_measure_id Function: sum Column: CS_Chart_Count2 Source: basic_chart_id Function: count Column: CS_Driver_Count Source: DRIVER_ID Function: count Formula Fields Field: beforereport Type: Function Body Formula: function BeforeReport return boolean is parent_entity_type varchar2(200); parent_entity_ID numeric(10); domain_ID integer ; begin --SRW.MESSAGE(2008, 'Before trigger '); :cp_ministry_id := reports_pkg.gettoplevelancestorunitid(:p_min_id); :cp_measure_id := :p_object_id; -- ************************************************ -- the following block handles report title, and measurement's parent entity ID, parent domain id when ... -- ************************************************ :CP_REPORT_TITLE := '? ? ?'; -- JK, Apr 15, 2009. Read titles from the table. --select title --into :CP_REPORT_TITLE --from report -- translation from report type (input param ) to Report.Id --where id = decode(:P_Report_Type_ID, 1,50, 2,37, 3,40, 4,30, 5,41, 6,39, 7,38, 0); --:CP_REPORT_TITLE := ' ' || :CP_REPORT_TITLE; if (:P_Report_Type_ID = 2) then :CP_REPORT_TITLE := ' Performance Measure Summary'; end if; if (:P_Report_Type_ID = 3) then :CP_REPORT_TITLE := ' Metrics by Organization Unit'; end if; if (:P_Report_Type_ID = 4) then :CP_REPORT_TITLE := ' File Measure Summary'; :CP_PARENT_ENTITY_ID := :p_object_id; :CP_DOMAIN_ID := 4; end if; if (:P_Report_Type_ID = 5) then :CP_REPORT_TITLE := ' Metrics by Management Category'; end if; if (:P_Report_Type_ID = 7) then :CP_REPORT_TITLE := ' Measures By Activity Notes'; end if; if (:P_Report_Type_ID = 6) then :CP_REPORT_TITLE := ' Metrics By Business Domain'; end if; if (:P_Report_Type_ID = 1) then :CP_REPORT_TITLE := ' Graph Summary'; end if; INIT_GRAPH_VALUES.init(); RETURN (TRUE); EXCEPTION WHEN NO_DATA_FOUND THEN SRW.MESSAGE(2008, 'Data integrity error. Some data was not found !'); return (TRUE); end; Field: b_devstatus_cur1formattrigger Type: Function Body Formula: function B_DevStatus_Cur1FormatTrigger return boolean is begin if upper(:rs_status) = 'ON TRACK' then -- 'GREEN' then -- {{ONTrack}} srw.set_foreground_fill_color('r50g88b50'); srw.set_text_color('r50g88b50'); srw.set_fill_pattern('solid'); elsif upper(:rs_status) = 'CONCERNS' then -- 'YELLOW' then --{{Concerns}} srw.set_foreground_fill_color('r100g100b0'); srw.set_text_color('r100g100b0'); srw.set_fill_pattern('solid'); elsif upper(:rs_status) = 'ATTENTION' then --'RED' then --{{Immediate Attention}} srw.set_foreground_fill_color('r100g0b0'); srw.set_text_color('r100g0b0'); srw.set_fill_pattern('solid'); elsif upper(:rs_status) = 'COMPLETED' then --'BLACK' then --{{Complete}} srw.set_foreground_fill_color('black'); srw.set_fill_pattern('solid'); elsif upper(:rs_status) = 'CANCELLED' then --'GRAY' then --{{Cancelled}} srw.set_foreground_fill_color('gray24'); srw.set_fill_pattern('solid'); elsif upper(:rs_status) = 'DEFERRED' then --'WHITE' then srw.set_foreground_fill_color('white'); srw.set_fill_pattern('solid'); else return (FALSE); end if; return (TRUE); end; Field: init_graph_values Type: packageBody Formula: PACKAGE BODY INIT_GRAPH_VALUES IS PROCEDURE init IS DBG VARCHAR2(200); BEGIN R_MEASURESUMMARY.INITREPORT( P_REPORT_TYPE_ID => :P_REPORT_TYPE_ID, P_OBJECT_ID => :P_OBJECT_ID, P_REPORT_VALID_SET_ID => :P_REPORT_VALID_SET_ID, SP_PROMPT_VALUE_SET_ID => :SP_PROMPT_VALUE_SET_ID, P_STATUS_REQUEST_ID => :P_STATUS_REQUEST_ID, P_PROMPT_VALUE_SET_ID => :P_PROMPT_VALUE_SET_ID, CP_Q_PARENT_FILES_FILTER => :CP_Q_PARENT_FILES_FILTER, CP_Q_PARENT_SUBMISSIONS_FILTER => :CP_Q_PARENT_SUBMISSIONS_FILTER, CP_Q_PARENT_STRATEGIES_FILTER => :CP_Q_PARENT_STRATEGIES_FILTER, CP_IS_CHART_VISIBLE => :CP_IS_CHART_VISIBLE, CP_ACTIVITY_NOTES_SELECTED => :CP_ACTIVITY_NOTES_SELECTED, CP_MC_LEVEL_ID => :CP_MC_LEVEL_ID, CP_ORG_UNITS_SELECTED => :CP_ORG_UNITS_SELECTED, CP_PROMPT_RUNTIME_VALUE_SET_ID => :CP_PROMPT_RUNTIME_VALUE_SET_ID, CP_ORG_UNITS_SELECTED_SR => :CP_ORG_UNITS_SELECTED_SR, DBG => DBG ); end init; END INIT_GRAPH_VALUES; Field: init_graph_values Type: packageSpec Formula: PACKAGE INIT_GRAPH_VALUES IS procedure init; END; Field: m_graph_data_tabular_viewforma Type: Function Body Formula: function M_GRAPH_DATA_TABULAR_VIEWForma return boolean is begin return (TRUE); end; Field: b_legend_colour_boxformattrigg Type: Function Body Formula: function B_LEGEND_COLOUR_BOXFormatTrigg return boolean is s_color varchar2(12) := 'r25g50b100'; begin -- this is the order - make sure it's on sync with graph definiton (XML ) --red #ff0000 --blue #0000ff --black #000000 --green #00ff00 --yellow #ffff00 --magenta #ff00ff --cyan #00ffff --gray #808080 --darkred #800000 --darkblue #000080 --darkgreen --darkyellow --darkmagenta --darkcyan --darkgray -- used to be red & blue if (:measurements_set_name = 'Actuals') then s_color := 'r100g0b0'; end if; if (:measurements_set_name = 'Targets High Values') then s_color := 'r50g75b100'; end if; srw.set_foreground_fill_color(s_color); srw.set_fill_pattern('solid'); return (TRUE); end; Field: r_imageformattrigger Type: Function Body Formula: function R_IMAGEFormatTrigger return boolean is begin if (:CS_TYPE_OF_GRAPH_IMAGE = 'System Generated') then return (FALSE); end if; return (TRUE); end; Field: f_graph_or_image_valueformattr Type: Function Body Formula: function F_GRAPH_OR_IMAGE_VALUEFormatTr return boolean is begin -- JK - HIDE IT ALWAYS. return (false); end; Field: m_fileformattrigger Type: Function Body Formula: function M_FILEFormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if ((:domain_id != '4') or (:CS_Measure_Counter + :CS_Chart_Counter = 0) or (:P_Report_Type_ID = 1)) then return (FALSE); end if; return (TRUE); end; Field: m_strategyformattrigger Type: Function Body Formula: function M_STRATEGYFormatTrigger return boolean is begin if ( (:domain_id != '3') or (:P_Report_Type_ID = 1) or (:CS_Measure_Counter + :CS_Chart_Counter = 0) ) then return (FALSE); end if; return (TRUE); end; Field: m_submissionformattrigger Type: Function Body Formula: function M_SUBMISSIONFormatTrigger return boolean is begin if ((:domain_id != '1') or (:P_Report_Type_ID = 1) or (:CS_Measure_Counter + :CS_Chart_Counter = 0) ) then return (FALSE); end if; return (TRUE); end; Field: cf_graph_title_textformula Type: Function Body Return Type: character Formula: function CF_GRAPH_TITLE_TEXTFormula return Char is entity_type varchar(20); business_num varchar(50); begin select title into entity_type from easy_domain where id = :domain; if :domain = 3 then select business_number into business_num from BA_STRATEGY where id = :object_id; end if; if :domain = 1 then select business_number into business_num from SUBMISSION where id = :object_id; end if ; if :domain = 4 then select business_number into business_num from BA_FILE where id = :object_id; end if ; return entity_type || ' (' || business_num || ')'; end; Field: cf_graph_subtitle_textformula Type: Function Body Return Type: character Formula: function CF_GRAPH_SUBTITLE_TEXTFormula return Char is begin return SUBSTR( ('Measure (' || :meas_title || ')'), 1, 50 ); end; Field: cf_data_valuesformula Type: Function Body Return Type: character Formula: function CF_DATA_VALUESFormula return Char is --i_series_cnt integer := 2; DATA_VALUES VARCHAR2(200) := 'ACTUAL_VALUE1,TARGET_VALUE1'; s_range_or_single varchar2(50) := 'Singular' ; begin -- 26-JAN-2009 S T A R T ******************************************************************************************************** -- the commented out query below (select count ...) does not make much sense anymore. As of Rel 3.0 there will be only one graphing data per measure. -- ( don't know what will happen when two users concurrently execute plotting a graph for the same measure ) -- Anyway, today I will change the value returned from this function. It will be either 'ACTUAL_VALUE1,TARGET_VALUE1' -- or 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_HIGH_VALUE' for target portfolio 'definition' equals to 'Singular' or 'Range' respectively. select vl.title into s_range_or_single from value_list vl, m_target_portfolio tp, m_graphing g where vl.type = 'MEASURE_TARGET_DEFINITION' and tp.definition_id = vl.id and g.measure_id = tp.measure_id and tp.measure_id = :MEASUREMENT_ID and g.target_portfolio_id = tp.id; if s_range_or_single = 'Range' then DATA_VALUES := DATA_VALUES || ',TARGET_HIGH_VALUE'; end if; -- 26-JAN-2009 E N D ************************************************************************************************************ /* -- value of this variable will be used in graph legend -- so, first of all, determine the number of the targets, and then heir names -- do handle the situation when there is NO ACTUAL value, only TARGETS -- do note, that column names return have to be 'real', found in SQL query select count(distinct graphing_id) into i_series_cnt from M_GRAPHING_VALUE gv2 , m_graphing g2 where gv2.GRAPHING_ID = g2.id and g2.measure_id = :MEASUREMENT_ID ; -- now the ugly & boring part: IF i_series_cnt = 1 THEN DATA_VALUES := 'ACTUAL_VALUE1'; END IF; IF i_series_cnt = 1 THEN DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1'; END IF; --SRW.MESSAGE(999, 'CP_DATA_VALUES = ' || :CP_DATA_VALUES); IF i_series_cnt = 2 THEN DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2'; END IF; IF i_series_cnt = 3 THEN DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3'; END IF; IF i_series_cnt = 4 THEN DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4'; END IF; IF i_series_cnt = 5 THEN DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5'; END IF; IF i_series_cnt = 6 THEN DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5,TARGET_VALUE6'; END IF; IF i_series_cnt = 7 THEN DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5,TARGET_VALUE6,TARGET_VALUE7'; END IF; IF i_series_cnt = 8 THEN DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5,TARGET_VALUE6,TARGET_VALUE7,TARGET_VALUE8'; END IF; -- and for more then 1- targets we'll show only 10 IF i_series_cnt >= 9 THEN DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5,TARGET_VALUE6,TARGET_VALUE7,TARGET_VALUE8,TARGET_VALUE9'; END IF; */ RETURN DATA_VALUES; end; Field: cf_ministry_nameformula Type: Function Body Return Type: character Formula: function CF_MINISTRY_NAMEFormula return Char is l_name varchar2(100); l_acr varchar2(100); begin select reports_pkg.GETUNITLASTNAME(ID) --, acronym into l_name --, l_acr from unit where id = :P_MIN_ID; --return l_name || ' (' || l_acr || ')' ; return l_name; end; Field: b_13formattrigger Type: Function Body Formula: function B_13FormatTrigger return boolean is begin return (TRUE); end; Field: r_statement_of_resultformattri Type: Function Body Formula: function R_STATEMENT_OF_RESULTFormatTri return boolean is begin -- hide always; statement of results deprecated return (FALSE); end; Field: m_commitmentsformattrigger Type: Function Body Formula: function M_CommitmentsFormatTrigger return boolean is begin -- Commitments not implemented yet return (FALSE); end; Field: m_relatedmeasuresformattrigger Type: Function Body Formula: function M_RelatedMeasuresFormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if ((:CS_CNT_RELATED_MEASURES = '0') or (:P_Report_Type_ID = 1) ) then return (FALSE); end if; return (TRUE); end; Field: m_activitynotesformattrigger Type: Function Body Formula: function M_ActivityNotesFormatTrigger return boolean is begin if ((:CS_CNT_ACTIVITY_NOTES = '0') or (:P_Report_Type_ID = 1) ) then return (FALSE); end if; return (TRUE); end; Field: m_file_lead3formattrigger Type: Function Body Formula: function M_File_Lead3FormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if (:CS_CNT_AFFECTED_SUBMISSIONS = '0') then return (FALSE); end if; return (TRUE); end; Field: m_strategicobjectivesformattri Type: Function Body Formula: function M_StrategicObjectivesFormatTri return boolean is begin -- Automatically Generated from Report Builder. if (:CS_CNT_STRATEGY_OBJECTIVES = '0') then return (FALSE); end if; return (TRUE); end; Field: m_measure_data_frameformattrig Type: Function Body Formula: function M_MEASURE_DATA_FRAMEFormatTrig return boolean is begin -- Automatically Generated from Report Builder. if (:CS_CNT_GRAPH_TBL_ROWS = '0') then return (FALSE); end if; return (TRUE); end; Field: b_21formattrigger Type: Function Body Formula: function B_21FormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if (:CS_CNT_GRAPH_TBL_ROWS > '0') then return (FALSE); end if; return (TRUE); end; Field: ct_4formattrigger Type: Function Body Formula: function CT_4FormatTrigger return boolean is begin -- If drawing user-supplied graph image, or if there is -- no graphing data, hide the graph object. if ((:CS_TYPE_OF_GRAPH_IMAGE != 'System Generated') or (:CS_CNT_GRAPH_TBL_ROWS = 0)) then return (FALSE); end if; return (TRUE); end; Field: m_legend_title_frameformattrig Type: Function Body Formula: function M_LEGEND_TITLE_FRAMEFormatTrig return boolean is begin -- Don't show legend if no data exists, or user-supplied -- graph image is selected. if ((:CS_CNT_GRAPH_TBL_ROWS = 0) or (:CS_TYPE_OF_GRAPH_IMAGE != 'System Generated')) then return (FALSE); end if; return (TRUE); end; Field: m_driver_frameformattrigger Type: Function Body Formula: function M_DRIVER_FRAMEFormatTrigger return boolean is begin -- Hide 'driver' heading bar for Measure Summary, Graph Summary File Metrics Summary (2,1,4) if ( (:P_Report_Type_ID = '2') or (:P_Report_Type_ID = '4') or (:P_Report_Type_ID = '1') ) then return (FALSE); else -- as well as when there are no records inside it (i.e. # of Parent ID records is 0) if ((:CS_CNT_MEASURES = 0 or :CS_CNT_MEASURES is null) and (:CS_CNT_CHARTS = 0 or :CS_CNT_CHARTS is null)) then return (false); end if; end if; return (TRUE); end; Field: m_spacer_graph_heightformattri Type: Function Body Formula: function M_SPACER_GRAPH_HEIGHTFormatTri return boolean is begin -- If drawing user-supplied graph image, or if there is -- no graphing data, hiding the graph object, so don't need -- the spacer. if ((:CS_TYPE_OF_GRAPH_IMAGE != 'System Generated') or (:CS_CNT_GRAPH_TBL_ROWS = 0)) then return (FALSE); end if; return (TRUE); end; Field: m_chartformattrigger Type: Function Body Formula: function M_CHARTFormatTrigger return boolean is begin -- first and formost, read the value derived by reading the report prompt and decide to show / display charts if :CP_Is_Chart_Visible = 0 then return (false); end if; -- Automatically Generated from Report Builder. if ((:CS_Chart_Counter < 1) or (:CS_Chart_Counter IS NULL) or (:P_Report_Type_ID = 2) or (:P_Report_Type_ID = 1) or (:P_Report_Type_ID = 7)) then return (FALSE); end if; return (TRUE); end; Field: b_chart_rep_status_symbolforma Type: Function Body Formula: function B_chart_rep_status_symbolForma return boolean is begin if upper(:chart_reporting_status) = 'ON TRACK' then -- 'GREEN' then -- {{ONTrack}} srw.set_foreground_fill_color('r50g88b50'); srw.set_text_color('r50g88b50'); srw.set_fill_pattern('solid'); elsif upper(:chart_reporting_status) = 'CONCERNS' then -- 'YELLOW' then --{{Concerns}} srw.set_foreground_fill_color('r100g100b0'); srw.set_text_color('r100g100b0'); srw.set_fill_pattern('solid'); elsif upper(:chart_reporting_status) = 'ATTENTION' then --'RED' then --{{Immediate Attention}} srw.set_foreground_fill_color('r100g0b0'); srw.set_text_color('r100g0b0'); srw.set_fill_pattern('solid'); elsif upper(:chart_reporting_status) = 'COMPLETED' then --'BLACK' then --{{Complete}} srw.set_foreground_fill_color('black'); srw.set_fill_pattern('solid'); elsif upper(:chart_reporting_status) = 'CANCELLED' then --'GRAY' then --{{Cancelled}} srw.set_foreground_fill_color('gray24'); srw.set_fill_pattern('solid'); elsif upper(:chart_reporting_status) = 'DEFERRED' then --'WHITE' srw.set_foreground_fill_color('white'); srw.set_fill_pattern('solid'); end if; return (TRUE); end; Field: f_data1formattrigger Type: Function Body Formula: function F_data1FormatTrigger return boolean is style_num NUMBER(10) := 0; l_BG_COLOR VARCHAR2(50) := '' ; l_TEXT_COLOR VARCHAR2(50) := '' ; l_BOLD NUMBER(1) := 0; l_ITALIC NUMBER(1) := 0; l_UNDERLINE NUMBER(1) := 0; l_ALIGNMENT VARCHAR2(50) := ''; BEGIN -- order in precedence (form least signficant to most significant) is as following: -- chart -> column -> row -> cell -- meaning, id cell has a style id defined, use cell style info style_num := 0; IF :cell_style is not null AND :cell_style > 0 THEN style_num := :cell_style; END IF; -- if we have style defined, retrieve the attributes from M_STYLE table IF style_num is not null AND style_num > 0 THEN --SRW.MESSAGE(999, 'changing style ' || to_char(style_num) ); -- DEBUG --SRW.MESSAGE(999, 'style num = ' || style_num); --SRW.MESSAGE(999, 'a' || :chart_style || 'b' || :column_style || 'c' || :row_style || 'd' || :cell_style || 'e'); --SRW.MESSAGE(999, 'row = ' || :row_number || ' column = ' || :column_number ); SELECT --NVL(map1.REPORT_COLOR_CODE, 'r100g100b100') as BG_COLOR, --NVL(map2.REPORT_COLOR_CODE, 'r100g100b100') as TEXT_COLOR, map1.REPORT_COLOR_CODE as BG_COLOR, map2.REPORT_COLOR_CODE as TEXT_COLOR, BOLD , ITALIC , UNDERLINE , ALIGNMENT INTO l_BG_COLOR, l_TEXT_COLOR, l_BOLD , l_ITALIC , l_UNDERLINE , l_ALIGNMENT FROM CHART_STYLE , M_COLORS_MAP map1 , M_COLORS_MAP map2 WHERE id = style_num and lower(trim(map1.RGB_COLOR_CODE(+))) = lower(trim(BG_COLOR)) and lower(trim(map2.RGB_COLOR_CODE(+))) = lower(trim(TEXT_COLOR)) ; --SRW.MESSAGE(999, 'values are ' || l_BG_COLOR || l_TEXT_COLOR || l_BOLD || l_ITALIC || l_UNDERLINE || l_ALIGNMENT ); -- take the style retrieved and do SRW.SET calls IF l_BG_COLOR is not null and LENGTH(l_BG_COLOR) >0 THEN --SRW.SET_BACKGROUND_FILL_COLOR(l_BG_COLOR); SRW.SET_FOREGROUND_FILL_COLOR(l_BG_COLOR); srw.set_fill_pattern('solid'); --SRW.MESSAGE(999, 'row ' || to_char(:row_number) || ' column number ' || to_char(:column_number) || ', changing BG color to ' || l_BG_COLOR ); END IF; IF l_TEXT_COLOR is not null AND LENGTH(l_TEXT_COLOR) > 0 THEN SRW.SET_TEXT_COLOR(l_TEXT_COLOR); --SRW.MESSAGE(999, 'row ' || to_char(:row_number) || ' column number ' || to_char(:column_number) || ' changing color to ' || l_TEXT_COLOR ); END IF; IF l_BOLD is not null AND l_BOLD = 1 THEN SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT); END IF; IF l_ITALIC is not null AND l_ITALIC = 1 THEN SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE); END IF; IF l_UNDERLINE is not null AND l_UNDERLINE = 1 THEN SRW.SET_FONT_STYLE(SRW.UNDERLINE_STYLE); END IF; IF l_ALIGNMENT is not null AND LENGTH(l_ALIGNMENT) > 0 THEN SRW.SET_JUSTIFICATION( SRW.CENTER_HJUST ); END IF; END IF; -- no style is defined, exit from here and leave everything at its defaults return (TRUE); END; Field: b_divider1formattrigger Type: Function Body Formula: function B_Divider1FormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if (:P_Report_Type_ID = '1') then return (FALSE); end if; return (TRUE); end; Field: b_file_rep_status_symbolformat Type: Function Body Formula: function B_file_rep_status_symbolFormat return boolean is begin if upper(:file_reporting_status) = 'ON TRACK' then -- 'GREEN' then -- {{ONTrack}} srw.set_foreground_fill_color('r50g88b50'); srw.set_text_color('r50g88b50'); srw.set_fill_pattern('solid'); elsif upper(:file_reporting_status) = 'CONCERNS' then -- 'YELLOW' then --{{Concerns}} srw.set_foreground_fill_color('r100g100b0'); srw.set_text_color('r100g100b0'); srw.set_fill_pattern('solid'); elsif upper(:file_reporting_status) = 'ATTENTION' then --'RED' then --{{Immediate Attention}} srw.set_foreground_fill_color('r100g0b0'); srw.set_text_color('r100g0b0'); srw.set_fill_pattern('solid'); elsif upper(:file_reporting_status) = 'COMPLETED' then --'BLACK' then --{{Complete}} srw.set_foreground_fill_color('black'); srw.set_fill_pattern('solid'); elsif upper(:file_reporting_status) = 'CANCELLED' then --'GRAY' then --{{Cancelled}} srw.set_foreground_fill_color('gray24'); srw.set_fill_pattern('solid'); elsif upper(:file_reporting_status) = 'DEFERRED' then --'WHITE' then srw.set_foreground_fill_color('white'); srw.set_fill_pattern('solid'); end if; return (TRUE); end; Field: b_sub_rep_status_symbol1format Type: Function Body Formula: function B_sub_rep_status_symbol1Format return boolean is begin if upper(:str_reporting_status) = 'ON TRACK' then -- 'GREEN' then -- {{ONTrack}} srw.set_foreground_fill_color('r50g88b50'); srw.set_text_color('r50g88b50'); srw.set_fill_pattern('solid'); elsif upper(:str_reporting_status) = 'CONCERNS' then -- 'YELLOW' then --{{Concerns}} srw.set_foreground_fill_color('r100g100b0'); srw.set_text_color('r100g100b0'); srw.set_fill_pattern('solid'); elsif upper(:str_reporting_status) = 'ATTENTION' then --'RED' then --{{Immediate Attention}} srw.set_foreground_fill_color('r100g0b0'); srw.set_text_color('r100g0b0'); srw.set_fill_pattern('solid'); elsif upper(:str_reporting_status) = 'COMPLETED' then --'BLACK' then --{{Complete}} srw.set_foreground_fill_color('black'); srw.set_fill_pattern('solid'); elsif upper(:str_reporting_status) = 'CANCELLED' then --'GRAY' then --{{Cancelled}} srw.set_foreground_fill_color('gray24'); srw.set_fill_pattern('solid'); elsif upper(:str_reporting_status) = 'DEFERRED' then --'WHITE' srw.set_foreground_fill_color('white'); srw.set_fill_pattern('solid'); end if; return (TRUE); end; Field: f_drivers_countformattrigger Type: Function Body Formula: function F_drivers_countFormatTrigger return boolean is begin return (FALSE); end; Field: f_measure_counterformattrigger Type: Function Body Formula: function F_measure_counterFormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if (:P_Report_Type_ID != '5') then return (false); end if; return (false); end; Field: cf_q_measurement_measur_filter Type: Function Body Return Type: character Formula: function CF_Q_MEASUREMENT_MEASUR_FILTER return Char is l_str varchar(2000) := ' and 1 = 1 '; begin -- set the default SQL and change only if needed. l_str := ' and m.id in (select object_id from v_report_valid_data where sub_domain_id = 2 and report_valid_set_id = ' || :P_Report_Valid_Set_ID || ') ' ; if :P_Report_Type_ID = 2 or :P_Report_Type_ID = 1 then l_str := ' and m.id = ' || to_char(:P_OBJECT_ID) || ' and m.id in (select object_id from v_report_valid_data where sub_domain_id = 2 and report_valid_set_id = ' || :P_Report_Valid_Set_ID || ') '; end if; if :P_Report_Type_ID = 7 then -- this is an addition to further filter out mesures to be listed, and it will change further in near future when user gets the ability to -- select activity notes l_str := l_str || ' and m.id in (select distinct man.metric_id from m_metric_activity_note man ) ' ; end if; return l_str; end; Field: f_starting_pointformattrigger Type: Function Body Formula: function F_starting_pointFormatTrigger return boolean is begin return (false); end; Field: b_no_data_msgformattrigger Type: Function Body Formula: function B_no_data_msgFormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if (:CS_MEASURE_COUNT2 + :CS_Chart_Count2 = 0) then return (TRUE); end if; return (FALSE); end; Field: f_measure_counter1formattrigge Type: Function Body Formula: function F_measure_counter1FormatTrigge return boolean is begin -- Automatically Generated from Report Builder. if (:P_Report_Type_ID != '5') then return (false); end if; return (false); end; Field: cf_actuals_handling_labelformu Type: Function Body Return Type: character Formula: function CF_Actuals_Handling_LabelFormu return Char is str_value varchar2(30) := '???'; begin -- JK Apr 13 OBSOLETE --if :Actuals_Handling_id = 1 then -- str_value := 'Actual Total'; -- else --str_value := 'Actual'; --end if; return str_value; end; Field: cf_first_actuals_columnformula Type: Function Body Return Type: number Formula: function CF_First_Actuals_ColumnFormula return Number is nbr_value number(38,2); begin -- Actuals_Handling_id | Title ------------------------------------------------------------------ -- 1 Cumulative (adds to running total) -- 2 Total to Date (replaces the total) if :Actuals_Handling_id = 1 then nbr_value := :gr_actual_value; else nbr_value := :gr_total_to_date; end if; return nbr_value; end; Field: f_total_to_date_valueformattri Type: Function Body Formula: function F_TOTAL_TO_DATE_VALUEFormatTri return boolean is begin if (:Actuals_Handling_Id = 2) then return (FALSE); end if; return (TRUE); end; Field: m_vertical_spacer_2formattrigg Type: Function Body Formula: function M_Vertical_Spacer_2FormatTrigg return boolean is begin if (:Actuals_Handling_Id = 2) then return (FALSE); end if; return (TRUE); end; Field: b_actual_totalformattrigger Type: Function Body Formula: function B_Actual_TotalFormatTrigger return boolean is begin if (:Actuals_Handling_Id = 2) then return (FALSE); end if; return (TRUE); end; Field: f_percent_or_dollar_symbolform Type: Function Body Formula: function F_percent_or_dollar_symbolForm return boolean is begin -- Kevin Davis Oct. 15, 2009. Hiding '%' or '$' when no graphing data is present. -- Automatically Generated from Report Builder. if (:CS_MIN_users_image = 'User Provided') or (:CS_CNT_GRAPH_TBL_ROWS < 1) then return (FALSE); end if; return (TRUE); end; Field: m_owning_file_detailsformattri Type: Function Body Formula: function M_Owning_File_DetailsFormatTri return boolean is begin if :P_Report_Type_ID = 5 then return (FALSE); end if; return (TRUE); end; Field: f_gr_actual_valueformattrigger Type: Function Body Formula: function F_GR_Actual_ValueFormatTrigger return boolean is begin return (TRUE); end; Groups Group: main Section Body Section: Frame M_MAIN_FRAME Text Fields Text Field: B_no_data_msg Text: No data to display Section: Frame M_DRIVER_FRAME Fields Field: F_drivers_count Data Source: CS_CNT_MEASURES Section: Frame M_PROTECT_DRIVER_TYPE_AND_NAME Fields Field: F_DRIVER_TYPE Data Source: DRIVER_TYPE Field: F_DRIVER_NAME Data Source: DRIVER_NAME Section: Frame M_FILE_NAME_FRAME Fields Field: F_NAME_AND_NUMBER Data Source: file_name Field: F_File_Label Data Source: parent_file_label Section: Frame M_Owning_File_Details Fields Field: F_file_reporting_status Data Source: file_reporting_status Field: F_file_record_status Data Source: file_status Text Fields Text Field: B_FILE_UNITS Text: Lead: Text Field: B_FILE_MCs Text: Management Categories: Text Field: B_File_Reporting_Status Text: Reporting Status: Text Field: B_File_Record_Status Text: Record Status: Section: Frame M_frame_around_stategy Text Fields Text Field: B_FILE_STARTEGY Text: Strategies: Section: Frame M_SUBMISSION_NAME_FRAME Fields Field: F_submission_name Data Source: submission_name Field: F_Submission_Label Data Source: parent_submission_label Section: Frame M_File_Lead2 Text Fields Text Field: B_FILE_UNITS2 Text: Lead: Section: Frame M_File_Lead3 Text Fields Text Field: B_FILE_UNITS3 Text: Affected: Section: Frame M_startegies_to_sub_item Text Fields Text Field: B_submission_strategy Text: Strategy: Section: Frame M_strategy_title Fields Field: F_strategy_name Data Source: strategy_name Field: F_Strategy_Label Data Source: parent_strategy_label Section: Frame M_File_Lead1 Text Fields Text Field: B_FILE_UNITS1 Text: Lead: Section: Frame M_StrategicObjectives Text Fields Text Field: B_13 Text: Strategic Objective: Section: Frame M_MeasureDetail Fields Field: F_GRAPH_OR_IMAGE_VALUE Data Source: CS_TYPE_OF_GRAPH_IMAGE Field: F_starting_point Data Source: starting_point Section: Frame M_ReportingTo Fields Field: F_reporting_to Data Source: reporting_to_name Text Fields Text Field: B_LABEL_REPORTING_TO Text: Reporting To: Section: Frame M_ReportingStatus Fields Field: F_measure_status Data Source: rs_status Text Fields Text Field: B_LABEL_REPORTING_STATUS Text: Reporting Status: Section: Frame M_StatusComments Fields Field: F_status_note Data Source: status_note Text Fields Text Field: B_LABEL_STATUS_NOTE Text: Status Comments: Section: Frame M_MEASURE_NAME_FRAME Fields Field: F_meas_title Data Source: meas_title Text Fields Text Field: B_1 Text: Measure Title: Section: Frame M_LOM_Keeptogether1 Fields Field: F_MeasureNumber Data Source: measure_number Text Fields Text Field: B_20 Text: Number: Section: Frame M_LOM_Keeptogether Fields Field: F_Level_of_Measure Data Source: lvl_of_measure Text Fields Text Field: B_12 Text: Level of Measure: Section: Frame M_measure_class_keeptogether Fields Field: F_Measure_Class_Prompt Data Source: mclass_prompt Field: F_Measure_Class Data Source: measure_class Section: Frame M_measure_type_keeptogether Fields Field: F_measure_type Data Source: measure_type Text Fields Text Field: B_4 Text: Measure Type: Section: Frame M_RecordingFrequency Fields Field: F_rep_freq Data Source: rep_freq Text Fields Text Field: B_5 Text: Recording Frequency: Section: Frame M_UnitofMeasure Fields Field: F_measure_unit_type Data Source: measure_unit Text Fields Text Field: B_6 Text: Unit of Measure: Section: Frame M_MEASURE_DATA_SOURCE_NAME_FR Fields Field: F_data_source Data Source: data_source Text Fields Text Field: B_7 Text: Data Source: Section: Frame M_Org_Unit_Wrapper Fields Field: F_Measure_Org_Unit Data Source: Measure_Org_Unit Text Fields Text Field: B_22 Text: Org Unit: Section: Frame M_RelatedMeasures Text Fields Text Field: B_RELATED_MEASURES Text: Related Measures: Text Field: B_RELATION Text: Relation Text Field: B_REL_MEASURE_NUMBER Text: Measure # Text Field: B_REL_MEASURE_TITLE Text: Title Section: Frame M_Commitments Text Fields Text Field: B_RELATED_COMMITMENTS Text: Commitments: Text Field: B_2 Text: No data. Section: Frame M_ActivityNotes Text Fields Text Field: B_RELATED_COMMITMENTS1 Text: Activity Notes: Text Field: B_ACT_NOTE_NUMBER Text: Number Text Field: B_ACT_NOTE_TITLE Text: Title Section: Frame M_GRAPH_OR_IMAGE_FRAME Fields Field: F_percent_or_dollar_symbol Data Source: m_unit_type Text Fields Text Field: B_21 Text: No graphing data entered. Text Field: B_RELATED_MEASURES1 Text: Graphing: Section: Frame M_GRAPH_DATA_TABULAR_VIEW Text Fields Text Field: B_RECORDED_DATE Text: Entered Data Date Text Field: B_TARGET_VALUE Text: Target Value Text Field: B_Actual Text: Actual Value Text Field: B_Actual_Total Text: Actual Total Section: Frame M_CHART Text Fields Text Field: B_chart_label Text: Charts Section: Frame M_chart_description_wrapper Fields Field: F_Chart_Description Data Source: chart_description Text Fields Text Field: B_Chart_Description Text: Description: Section: Frame M_chart_data_source_wrapper Fields Field: F_Chart_Data_Source Data Source: chart_data_source Text Fields Text Field: B_Data_Source Text: Data Source: Section: Frame M_Rep_Status_Comment_Wrapper Fields Field: F_chart_rep_status_comment Data Source: chart_rep_status_comment Text Fields Text Field: B_chart_reporting_status1 Text: Status Comment: Margin Section: Margin Fields Field: F_Run_Date1 Data Source: CurrentDate Field: F_Page Data Source: PageNumber Field: F_Total_Pages Data Source: TotalPages Field: F_MINISTRY_NAME Data Source: CF_MINISTRY_NAME Text Fields Text Field: B_9 Text: &CP_REPORT_TITLE Text Field: B_11 Text: EVista Report (v&CP_Version) run: Text Field: B_10 Text: Page &F_Page of &F_Total_Pages Text Field: B_3 Text: Confidential Web Sources Source: <%@ taglib uri="/WEB-INF/lib/reports_tld.jar" prefix="rw" %> <%@ page language="java" import="java.io.*" errorPage="/rwerror.jsp" session="false" %> <%@ page contentType="text/html;charset=ISO-8859-1" %> Your Title
> PM: > Measure Units: > Measure Type: > Reporting Frequency: > Data Source: > Unit of Measure Type: > Status Note:
> F_meas_title > F_measure_unit > F_measure_type > F_rep_freq > F_data_source > F_measure_unit_type > F_status_note