**************************************************************************** Location : Local or Network Folders Host : FINDITEZ-T460P Path : C:\Users\ken\Documents\Test Cases\Oracle-Reports\StrategySummary.xml **************************************************************************** Data Sources Group: G_TITLE Data Item: strategy_title Data Type: vchar2 Label: Title Expression: bs.title Data Item: strat_rs Data Type: vchar2 Label: Reporting Status1 Expression: rs.status Data Item: strat_rs_comments Data Type: clob Label: Reporting Status Comments1 Expression: rs.overall_comment Data Item: strat_status Data Type: vchar2 Label: Status Expression: st.title Data Item: BUSINESS_NUMBER Data Type: vchar2 Label: Business Number Expression: bs.business_number Data Item: strat_desc Data Type: vchar2 Label: Description Expression: bs.description Data Item: START_DATE Data Type: character Label: Start Date Expression: bs.start_date Data Item: END_DATE Data Type: character Label: End Date Expression: bs.end_date Data Item: ID Label: Id Expression: bs.id Data Item: STATUS_ID Label: Status Id Expression: bs.status_id Data Item: TARGET_ID Label: Target Id Expression: bs.target_id Data Item: OWNER_UNIT_ID Label: Owner Unit Id Expression: bs.owner_unit_id Data Item: CREATE_BY_ID Label: Create By Id Expression: bs.create_by_id Data Item: CREATE_DATE Data Type: character Label: Create Date Expression: bs.create_date Data Item: UPDATE_BY_ID Label: Update By Id Expression: bs.update_by_id Data Item: UPDATE_DATE Data Type: character Label: Update Date Expression: bs.update_date Data Item: target1 Data Type: vchar2 Label: Target1 Expression: tr.title Group: G_Objectives Data Item: st_objective Data Type: vchar2 Label: St Objective Expression: BA_STRATEGY_OBJECTIVE.TITLE Data Item: STRATEGY_ID Label: Strategy Id Expression: BA_STRATEGY_OBJECTIVE.STRATEGY_ID Group: G_UNIT_ID Data Item: ACRONYM Data Type: vchar2 Label: Acronym Expression: u.acronym Data Item: unit_name Data Type: vchar2 Label: Dn Expression: reports_pkg.GETENTIREUNITPATH ( U.ID , reports_pkg.getUnitLastName ( u.id ) ) Data Item: u_strat Label: Strategy Id1 Expression: bsu.strategy_id Data Item: UNIT_ID Label: Unit Id Expression: bsu.unit_id Group: G_MEASURE_TYPE Data Item: measure_type Data Type: vchar2 Label: Measure Type Expression: decode ( m_type.value_id , 2 , 'Rollup Measures' , 3 , 'Rollup Measures' , 'Measures' ) Group: G_MEASURE_DATA Data Item: measurement_id Label: Id1 Expression: mm.id Data Item: meas_title Data Type: vchar2 Label: Measure Expression: mm.title Data Item: meas_type Data Type: vchar2 Label: Type Expression: m_utyp.title Data Item: measure_unit Data Type: vchar2 Label: Unit Expression: mm.measure_unit Data Item: meas_freq Data Type: vchar2 Label: Frequency Expression: m_freq.title Data Item: meas_status Data Type: vchar2 Label: Status Expression: m_stat.title Data Item: meas_strategy Data Type: vchar2 Label: Meas Strategy Expression: mber.object_id Data Item: measure_number Data Type: vchar2 Label: Measure Number Expression: mm.business_id Formula: CF_DATA_VALUES Data-Type: character Source: cf_data_valuesformula Formula: CF_GRAPH_SUBTITLE Data-Type: character Source: cf_graph_subtitle_textformula Formula: CF_GRAPH_TITLE_TEXT Data-Type: character Source: cf_graph_title_textformula Group: G_RELATED_FILE_DETAILS Data Item: file_number Data Type: vchar2 Label: Business Number1 Expression: f.business_number Data Item: file_name Data Type: vchar2 Label: Title Expression: f.title Data Item: file_status Data Type: vchar2 Label: Rec Status Expression: rec_stat.title Data Item: file_reporting_status Data Type: vchar2 Label: Reporting Status Expression: reporting_status.status Data Item: file_reporting_status_comment Data Type: clob Label: Reporting Status Comment Expression: reporting_status.overall_comment Data Item: file_id Label: File Id Expression: f.id Group: G_file_division Data Item: file_division Data Type: vchar2 Label: File Division Expression: units.file_division Group: G_DOC_dummy Data Item: dummy_field Data Type: character Label: Dummy Field Expression: 'Documents:' Group: G_Doc_Folder Data Item: co_docname Data Type: vchar2 Label: Co Docname Expression: doc.co_docname Data Item: doc_date Data Type: date Label: Doc Date Expression: to_date ( co_docdate , 'MM/DD/YY HH:MI AM' ) Data Item: doc_pkg_templ_name Data Type: vchar2 Label: Doc Pkg Templ Name Expression: cm.co_menutext Data Item: doc_sub_id Label: Doc Sub Id Expression: : id Data Item: doc_type_title Data Type: vchar2 Label: Doc Type Title Expression: dt.title Data Item: co_version Label: Co Version Expression: vrsn.co_version Data Item: doc_id Label: Doc Id Expression: doc.co_docid Data Item: doc_creator Data Type: vchar2 Label: Doc Creator Expression: co_sourceauthor Data Item: co_filename Data Type: vchar2 Label: Co Filename Expression: vrsn.co_filename Data Item: doc_desc Data Type: vchar2 Label: Doc Desc Expression: doc.co_description Formula: CF_doc_desc_decrypt Data-Type: character Source: cf_doc_desc_decryptformula Group: G_cont_units Data Item: sort_id Label: Sort Id Expression: DECODE ( u.ministry_id , : P_MIN_ID , - 999 , ABS ( u.ministry_id ) ) Data Item: Contact_Portf_Ministry Data Type: vchar2 Label: Contact Portf Ministry Expression: reports_pkg.GETUNITLASTNAME ( u.ministry_id ) Data Item: cont_object_id Label: Cont Object Id Expression: cp.object_id Group: G_cont_teams Data Item: cont_team_title Data Type: vchar2 Label: Cont Team Title Expression: ct.title Group: G_cont_roles Data Item: cont_role_title Data Type: vchar2 Label: Cont Role Title Expression: cr.title Group: G_contacts Data Item: cont_name Data Type: vchar2 Label: Cont Name Expression: i.first_name || ' ' || i.last_name 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_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 Group: G_graph_measure_id Data Item: graph_measure_id Label: Graph Measure Id Expression: a.MEASURE_ID Data Item: graphing_id1 Label: Graphing Id Expression: a.id Data Item: system_or_user_generated Data Type: vchar2 Label: System Or User Generated Expression: vl.title Data Item: STATEMENT_OF_RESULT Data Type: vchar2 Label: Statement Of Result Expression: TP.STATEMENT_OF_RESULT 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: Line_or_Bar Data Type: vchar2 Label: Line Or Bar Expression: graph_type_nt.Line_or_Bar 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_id Label: Graphing Id1 Expression: a.id Data Item: legend_measure_id Label: Legend Measure Id Expression: measure_id Group: G_GRAPHING_ID2 Data Item: TARGET_HIGH_VALUE Label: Target High Value Expression: gv.TARGET_HIGH_VALUE Data Item: GRAPHING_ID2 Label: Graphing Id2 Expression: gv.graphing_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 Data Item: graphing_measure_id Label: Graphing Measure Id Expression: g.measure_id Group: G_Attribute Data Item: chart_data_id Label: Chart Data Id Expression: c.chart_data_id Data Item: chart_title Data Type: vchar2 Label: Chart Title Expression: c.title 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: Chart Start Dt Expression: c.period_start Data Item: chart_end_dt Data Type: date Label: Chart End Dt 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: Chart 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: Chart Reporting Status Expression: rs.status Data Item: chart_rep_status_comment Data Type: clob Label: Chart Rep Status Comment Expression: rs.overall_comment Data Item: chart_parent_id Label: Chart Parent Id Expression: parent.chart_parent_id Data Item: sub_domain_id Label: Sub Domain Id Expression: parent.sub_domain_id Data Item: basic_chart_id Label: Basic Chart Id Expression: c.id Group: G_Scope_Header Data Item: category_scope Data Type: vchar2 Label: Category Scope Expression: 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' ) || ':' Formula: CF_Actual_Category_Scope Data-Type: character Label: Cf Actual Category Scope Source: cf_actual_category_scopeformul Group: G_category_title Data Item: category_title Data Type: vchar2 Label: Category Title Expression: mc.TITLE Group: G_category_scope Data Item: cat_hierarchy_title Data Type: vchar2 Label: Cat Hierarchy Title Expression: LPAD ( ' ' , ( LEVEL - 1 ) * 7 ) || lpad ( lvel.TITLE , 20 ) Data Item: sub_categories_tree Data Type: vchar2 Label: Sub Categories Tree Expression: value.title 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: value_id Label: Id1 Expression: value.id Group: G_hierarchy_level Data Item: hierarchy_level Label: Hierarchy Level Expression: lvel.hierarchy_level Data Item: hierarchy_title Data Type: vchar2 Label: Hierarchy Title Expression: lvel.title Data Item: hierarchy_tree Data Type: vchar2 Label: Hierarchy Tree Expression: LPAD ( ' ' , ( LEVEL - 1 ) * 7 ) || lpad ( value.TITLE , 20 ) Group: G_built_chart_id Data Item: data_chart_id Label: Built Chart Id Expression: : chart_data_id Data Item: chart_row_number Label: Built Row Number Expression: r.row_number Data Item: col1 Data Type: vchar2 Label: Col1 Expression: min ( decode ( ac.column_number , 0 , ac.data , null ) ) Data Item: col2 Data Type: vchar2 Label: Col2 Expression: min ( decode ( ac.column_number , 1 , ac.data , null ) ) Data Item: col3 Data Type: vchar2 Label: Col3 Expression: min ( decode ( ac.column_number , 2 , ac.data , null ) ) Data Item: col4 Data Type: vchar2 Label: Col4 Expression: min ( decode ( ac.column_number , 3 , ac.data , null ) ) Data Item: col5 Data Type: vchar2 Label: Col5 Expression: min ( decode ( ac.column_number , 4 , ac.data , null ) ) Data Item: col6 Data Type: vchar2 Label: Col6 Expression: min ( decode ( ac.column_number , 5 , ac.data , null ) ) Data Item: col7 Data Type: vchar2 Label: Col7 Expression: min ( decode ( ac.column_number , 6 , ac.data , null ) ) Data Item: col8 Data Type: vchar2 Label: Col8 Expression: min ( decode ( ac.column_number , 7 , ac.data , null ) ) Report Parameters Parameter: P_MIN_ID Data Type: number Initial Values: 47 Parameter: P_OBJECT_ID Data Type: number Initial Values: 381 Parameter: P_SESSION_ID Data Type: character Initial Values: 1239115823999 Parameter: P_REPORT_VALID_SET_ID Data Type: character Initial Values: 11911 Tables Table: Q_STRATEGY SQL Command: 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) Table: Q_OBJECTIVES SQL Command: SELECT --'Objectives:' obj_hdr1, BA_STRATEGY_OBJECTIVE.TITLE st_objective, BA_STRATEGY_OBJECTIVE.STRATEGY_ID FROM BA_STRATEGY_OBJECTIVE Table: Q_UNITS_IT_BELONGS_TO SQL Command: 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 */ Table: Q_METRICS SQL Command: 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) Table: Q_RELATED_FILES SQL Command: -- 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) Table: Q_DOCUMENTS SQL Command: -- 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 Table: Q_CONTACTS SQL Command: -- 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 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' 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 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 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 Table: Q_Legends SQL Command: /* ---------------------------------------------------------- ------------ 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 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 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 */ Table: Q_Chart_Attributes SQL Command: 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(+) Table: Q_1 SQL Command: 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 Table: Q_2 SQL Command: --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 Table: Q_BUILT_CHART SQL Command: 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; Table Links Joins: where [G_TITLE].[ID] = [Q_OBJECTIVES].[STRATEGY_ID] where [G_TITLE].[ID] = [Q_UNITS_IT_BELONGS_TO].[u_strat] where [G_TITLE] = [Q_DOCUMENTS] where [G_TITLE].[ID] = [Q_CONTACTS].[cont_object_id] where [G_TITLE].[ID] = [Q_METRICS].[meas_strategy] where [G_MEASURE_DATA].[measurement_id] = [Q_Graph_Of_Master_Target].[graph_measure_id] where [G_MEASURE_DATA].[measurement_id] = [Q_Legends].[legend_measure_id] where [G_MEASURE_DATA].[measurement_id] = [Q_GRAPH_DATA_TABULAR_VIEW].[raw_data_measure_id] where [G_graph_measure_id].[graphing_id1] = [Q_Graphing_Values].[GRAPHING_ID2] where [G_category_scope] = [Q_2] where [G_RELATED_FILE_DETAILS] = [Q_1] where [G_TITLE].[ID] = [Q_Chart_Attributes].[chart_parent_id] where [G_Attribute].[chart_data_id] = [Q_BUILT_CHART].[data_chart_id] Placeholder Columns Column: CP_Strategy_ID Data Type: number Label: Cp Strategy Id Column: CP_Version Data Type: character Column: CP_Ministry_Acronym Data Type: character Summary Columns Column: CS_Objectives_Cnt Label: Cs Objectives Cnt Source: st_objective Function: count Column: CS_CNT_RELATED_FILES Source: file_number Function: count Column: CS_CNT_DOCUMENTS Source: co_docname Function: count Column: CS_CNT_CONTACTS Source: cont_role_title Function: count Formula Fields Field: cf_doc_desc_decryptformula Type: Function Body Return Type: character Formula: function CF_doc_desc_decryptFormula return Character is jObj ora_java.jobject; l_Desc submission.description%type; l_vl_id number; begin jObj := EVistaEncryptor.getInstance; l_Desc := rep_util_pkg.truncate_string(EVistaEncryptor.decrypt(jObj, :doc_desc), 500); return (lpad(' ', 12,' ')||l_Desc); end; Field: evistaencryptor Type: packageBody Formula: PACKAGE BODY EVistaEncryptor IS -- -- DO NOT EDIT THIS FILE - it is machine generated! -- args JNI.ARGLIST; -- Method: main ([Ljava/lang/String;)V PROCEDURE main( a0 ORA_JAVA.JARRAY) IS BEGIN args := JNI.CREATE_ARG_LIST(1); JNI.ADD_OBJECT_ARG(args, a0, '[Ljava/lang/String;'); JNI.CALL_VOID_METHOD(TRUE, NULL, 'ca/on/gov/gsdc/evista/common/security/EVistaEncryptor', 'main', '([Ljava/lang/String;)V', args); END; -- Method: getInstance ()Lca/on/gov/gsdc/evista/common/security/EVistaEncryptor; FUNCTION getInstance RETURN ORA_JAVA.JOBJECT IS BEGIN args := NULL; RETURN JNI.CALL_OBJECT_METHOD(TRUE, NULL, 'ca/on/gov/gsdc/evista/common/security/EVistaEncryptor', 'getInstance', '()Lca/on/gov/gsdc/evista/common/security/EVistaEncryptor;', args); END; -- Method: encrypt (Ljava/lang/String;)Ljava/lang/String; FUNCTION encrypt( obj ORA_JAVA.JOBJECT, a0 VARCHAR2) RETURN VARCHAR2 IS BEGIN args := JNI.CREATE_ARG_LIST(1); JNI.ADD_STRING_ARG(args, a0); RETURN JNI.CALL_STRING_METHOD(FALSE, obj, 'ca/on/gov/gsdc/evista/common/security/EVistaEncryptor', 'encrypt', '(Ljava/lang/String;)Ljava/lang/String;', args); END; -- Method: decrypt (Ljava/lang/String;)Ljava/lang/String; FUNCTION decrypt( obj ORA_JAVA.JOBJECT, a0 VARCHAR2) RETURN VARCHAR2 IS BEGIN args := JNI.CREATE_ARG_LIST(1); JNI.ADD_STRING_ARG(args, a0); RETURN JNI.CALL_STRING_METHOD(FALSE, obj, 'ca/on/gov/gsdc/evista/common/security/EVistaEncryptor', 'decrypt', '(Ljava/lang/String;)Ljava/lang/String;', args); END; -- Method: encryptInt (I)Ljava/lang/String; FUNCTION encryptInt( obj ORA_JAVA.JOBJECT, a0 NUMBER) RETURN VARCHAR2 IS BEGIN args := JNI.CREATE_ARG_LIST(1); JNI.ADD_INT_ARG(args, a0); RETURN JNI.CALL_STRING_METHOD(FALSE, obj, 'ca/on/gov/gsdc/evista/common/security/EVistaEncryptor', 'encryptInt', '(I)Ljava/lang/String;', args); END; -- Method: decryptInt (Ljava/lang/String;)I FUNCTION decryptInt( obj ORA_JAVA.JOBJECT, a0 VARCHAR2) RETURN NUMBER IS BEGIN args := JNI.CREATE_ARG_LIST(1); JNI.ADD_STRING_ARG(args, a0); RETURN JNI.CALL_INT_METHOD(FALSE, obj, 'ca/on/gov/gsdc/evista/common/security/EVistaEncryptor', 'decryptInt', '(Ljava/lang/String;)I', args); END; BEGIN NULL; END; Field: evistaencryptor Type: packageSpec Formula: PACKAGE EVistaEncryptor /* ca.on.gov.gsdc.evista.common.security.EVistaEncryptor */ IS -- -- DO NOT EDIT THIS FILE - it is machine generated! -- -- Method: main ([Ljava/lang/String;)V PROCEDURE main( a0 ORA_JAVA.JARRAY); -- Method: getInstance ()Lca/on/gov/gsdc/evista/common/security/EVistaEncryptor; FUNCTION getInstance RETURN ORA_JAVA.JOBJECT; -- Method: encrypt (Ljava/lang/String;)Ljava/lang/String; FUNCTION encrypt( obj ORA_JAVA.JOBJECT, a0 VARCHAR2) RETURN VARCHAR2; -- Method: decrypt (Ljava/lang/String;)Ljava/lang/String; FUNCTION decrypt( obj ORA_JAVA.JOBJECT, a0 VARCHAR2) RETURN VARCHAR2; -- Method: encryptInt (I)Ljava/lang/String; FUNCTION encryptInt( obj ORA_JAVA.JOBJECT, a0 NUMBER) RETURN VARCHAR2; -- Method: decryptInt (Ljava/lang/String;)I FUNCTION decryptInt( obj ORA_JAVA.JOBJECT, a0 VARCHAR2) RETURN NUMBER; END; Field: cf_ministry_nameformula Type: Function Body Return Type: character Formula: function CF_Ministry_NameFormula return Char is l_Ministry varchar2(100); begin if :p_min_id is not null then begin select reports_pkg.getUnitLastName(id) into l_Ministry from unit where id = :p_min_id; return(l_Ministry); exception when no_data_found then return ('Ministry not Found'); end; else return('Awaiting Parameter Form Change for display of ministry name'); end if; end; Field: beforereport Type: Function Body Formula: function BeforeReport return boolean is begin :cp_strategy_id := :p_object_id; begin select acronym into :cp_ministry_acronym from unit where id = :p_min_id; end; return (TRUE); end; Field: b_11formattrigger Type: Function Body Formula: function B_11FormatTrigger 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' srw.set_foreground_fill_color('white'); srw.set_fill_pattern('solid'); else return (FALSE); end if; return (TRUE); end; Field: b_1formattrigger Type: Function Body Formula: function B_1FormatTrigger return boolean is begin if upper(:strat_rs) = 'ON TRACK' then -- 'GREEN' then -- {{ONTrack}} srw.set_foreground_fill_color('r50g88b50'); srw.set_text_color('r50g88b50'); srw.set_fill_pattern('solid'); elsif upper(:strat_rs) = 'CONCERNS' then -- 'YELLOW' then --{{Concerns}} srw.set_foreground_fill_color('r100g100b0'); srw.set_text_color('r100g100b0'); srw.set_fill_pattern('solid'); elsif upper(:strat_rs) = 'ATTENTION' then --'RED' then --{{Immediate Attention}} srw.set_foreground_fill_color('r100g0b0'); srw.set_text_color('r100g0b0'); srw.set_fill_pattern('solid'); elsif upper(:strat_rs) = 'COMPLETED' then --'BLACK' then --{{Complete}} srw.set_foreground_fill_color('black'); srw.set_fill_pattern('solid'); elsif upper(:strat_rs) = 'CANCELLED' then --'GRAY' then --{{Cancelled}} srw.set_foreground_fill_color('gray24'); srw.set_fill_pattern('solid'); elsif upper(:strat_rs) = 'DEFERRED' then --'WHITE' then srw.set_foreground_fill_color('white'); srw.set_fill_pattern('solid'); else return (FALSE); end if; return (TRUE); end; Field: m_related_filesformattrigger Type: Function Body Formula: function M_RELATED_FILESFormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if (:CS_CNT_RELATED_FILES = '0') then return (FALSE); end if; return (TRUE); end; Field: m_g_documents_sectionformattri Type: Function Body Formula: function M_G_DOCUMENTS_SECTIONFormatTri return boolean is begin -- Automatically Generated from Report Builder. if (:CS_CNT_DOCUMENTS = '0') then return (FALSE); end if; return (TRUE); end; Field: m_contactsformattrigger Type: Function Body Formula: function M_CONTACTSFormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if (:CS_CNT_CONTACTS = '0') then return (FALSE); end if; return (TRUE); end; Field: m_graph_data_tabular_viewforma Type: Function Body Formula: function M_GRAPH_DATA_TABULAR_VIEWForma return boolean is begin -- Automatically Generated from Report Builder. if (:CS_ACT_AND_TARGET_DATA_CNT = '0') then return (FALSE); end if; return (TRUE); end; Field: b_no_metrics_yet_msgformattrig Type: Function Body Formula: function B_NO_METRICS_YET_MSGFormatTrig return boolean is begin -- Automatically Generated from Report Builder. if (:CS_ACT_AND_TARGET_DATA_CNT > '0') then return (FALSE); end if; return (TRUE); 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 ************************************************************************************************************ RETURN DATA_VALUES; 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_graph_title_textformula Type: Function Body Return Type: character Formula: function CF_GRAPH_TITLE_TEXTFormula return Char is entity_type varchar(20); begin select title into entity_type from easy_domain where id = 3; return entity_type || ' (' || :business_number || ')'; end; Field: ct_1formattrigger Type: Function Body Formula: function CT_1FormatTrigger return boolean is begin -- If drawing user-supplied graph image, or if there is -- no graphing data, hide the graph object. -- JK TEST fix graph problem if (:CS_TYPE_OF_GRAPH_IMAGE != 'System Generated' or :CS_Count_Graph_Table_Rows = 0) then return (FALSE); end if; return (TRUE); end; Field: r_image1formattrigger Type: Function Body Formula: function R_IMAGE1FormatTrigger return boolean is begin if (:CS_TYPE_OF_GRAPH_IMAGE = 'System Generated') then return (FALSE); end if; return (TRUE); end; Field: b_chart_rep_status_symbol1form Type: Function Body Formula: function B_Chart_Rep_Status_Symbol1Form 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' then --{{Unable to Report}} srw.set_foreground_fill_color('white'); srw.set_fill_pattern('solid'); end if; return (TRUE); end; Field: b_related_measures1formattrigg Type: Function Body Formula: function B_RELATED_MEASURES1FormatTrigg return boolean is begin -- Automatically Generated from Report Builder. if (:CS_Chart_Count = '0') then return (FALSE); end if; return (TRUE); end; Field: cf_actual_category_scopeformul Type: Function Body Return Type: character Formula: function CF_Actual_Category_ScopeFormul return Char is begin return(substr(:category_scope, 2)); end; Field: m_13formattrigger Type: Function Body Formula: function M_13FormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if (:CS_Chart_Count < '1') then return (FALSE); end if; return (TRUE); end; Field: b_contacts_section_title1forma Type: Function Body Formula: function B_CONTACTS_SECTION_TITLE1Forma return boolean is begin -- Automatically Generated from Report Builder. if (:CS_Measure_Count < '1') then return (FALSE); end if; return (TRUE); end; Field: f_chart_col1formattrigger Type: Function Body Formula: function F_chart_col1FormatTrigger 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) := ''; l_BORDER NUMBER(1) := 0; -- this change for every columns l_this_column NUMBER(1) := 0; l_chart_style NUMBER(38) ; l_row_style NUMBER(38) ; l_column_style NUMBER(38) ; l_cell_style NUMBER(38) ; begin -- order in precedence (form least significant to most significant) is as following: -- chart -> column -> row -> cell -- meaning, id cell has a style id defined, use cell style info -- JK Jan 28 2009 9(1): -- This snippet *was* cloned from ChartSummary.rdf file, F_Data field, but it got changed today - big time. -- all styles are being retrieved here -- JK Jan 28 2009 (2): add handler for BORDER attribute style_num := 0; select cl3.style_id into l_cell_style from m_chart c3, chart_column co3, chart_row ro3, chart_cell cl3 where c3.id = :basic_chart_id and co3.chart_data_id = c3.chart_data_id and co3.column_number = l_this_column and ro3.chart_data_id = c3.chart_data_id and ro3.row_number = :chart_row_number and cl3.chart_data_id = c3.chart_data_id and cl3.chart_row_id = ro3.id and cl3.chart_column_id = co3.id; IF l_cell_style is not null AND l_cell_style > 0 THEN style_num := l_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 SELECT map1.REPORT_COLOR_CODE as BG_COLOR, map2.REPORT_COLOR_CODE as TEXT_COLOR, BOLD , ITALIC , UNDERLINE , ALIGNMENT , BORDER INTO l_BG_COLOR, l_TEXT_COLOR, l_BOLD , l_ITALIC , l_UNDERLINE , l_ALIGNMENT , l_BORDER 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_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; IF l_BORDER is not null and l_BORDER = 1 THEN srw.set_foreground_border_color('black'); srw.set_border_pattern('solid'); END IF; END IF; -- no style is defined, exit from here and leave everything at its defaults return (TRUE); exception when no_data_found then return (TRUE); end; Field: f_chart_col2formattrigger Type: Function Body Formula: function F_chart_col2FormatTrigger 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) := ''; l_BORDER NUMBER(1) := 0; -- this change for every columns l_this_column NUMBER(1) := 1; l_chart_style NUMBER(38) ; l_row_style NUMBER(38) ; l_column_style NUMBER(38) ; l_cell_style NUMBER(38) ; begin -- order in precedence (form least significant to most significant) is as following: -- chart -> column -> row -> cell -- meaning, id cell has a style id defined, use cell style info -- JK Jan 28 2009 9(1): -- This snippet *was* cloned from ChartSummary.rdf file, F_Data field, but it got changed today - big time. -- all styles are being retrieved here -- JK Jan 28 2009 (2): add handler for BORDER attribute style_num := 0; select cl3.style_id into l_cell_style from m_chart c3, chart_column co3, chart_row ro3, chart_cell cl3 where c3.id = :basic_chart_id and co3.chart_data_id = c3.chart_data_id and co3.column_number = l_this_column and ro3.chart_data_id = c3.chart_data_id and ro3.row_number = :chart_row_number and cl3.chart_data_id = c3.chart_data_id and cl3.chart_row_id = ro3.id and cl3.chart_column_id = co3.id; IF l_cell_style is not null AND l_cell_style > 0 THEN style_num := l_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 SELECT map1.REPORT_COLOR_CODE as BG_COLOR, map2.REPORT_COLOR_CODE as TEXT_COLOR, BOLD , ITALIC , UNDERLINE , ALIGNMENT , BORDER INTO l_BG_COLOR, l_TEXT_COLOR, l_BOLD , l_ITALIC , l_UNDERLINE , l_ALIGNMENT , l_BORDER 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_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; IF l_BORDER is not null and l_BORDER = 1 THEN srw.set_foreground_border_color('black'); srw.set_border_pattern('solid'); END IF; END IF; -- no style is defined, exit from here and leave everything at its defaults return (TRUE); exception when no_data_found then return (TRUE); end; Field: f_chart_col3formattrigger Type: Function Body Formula: function F_chart_col3FormatTrigger 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) := ''; l_BORDER NUMBER(1) := 0; -- this change for every columns l_this_column NUMBER(1) := 2; l_chart_style NUMBER(38) ; l_row_style NUMBER(38) ; l_column_style NUMBER(38) ; l_cell_style NUMBER(38) ; begin -- order in precedence (form least significant to most significant) is as following: -- chart -> column -> row -> cell -- meaning, id cell has a style id defined, use cell style info -- JK Jan 28 2009 9(1): -- This snippet *was* cloned from ChartSummary.rdf file, F_Data field, but it got changed today - big time. -- all styles are being retrieved here -- JK Jan 28 2009 (2): add handler for BORDER attribute style_num := 0; select cl3.style_id into l_cell_style from m_chart c3, chart_column co3, chart_row ro3, chart_cell cl3 where c3.id = :basic_chart_id and co3.chart_data_id = c3.chart_data_id and co3.column_number = l_this_column and ro3.chart_data_id = c3.chart_data_id and ro3.row_number = :chart_row_number and cl3.chart_data_id = c3.chart_data_id and cl3.chart_row_id = ro3.id and cl3.chart_column_id = co3.id; IF l_cell_style is not null AND l_cell_style > 0 THEN style_num := l_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 SELECT map1.REPORT_COLOR_CODE as BG_COLOR, map2.REPORT_COLOR_CODE as TEXT_COLOR, BOLD , ITALIC , UNDERLINE , ALIGNMENT , BORDER INTO l_BG_COLOR, l_TEXT_COLOR, l_BOLD , l_ITALIC , l_UNDERLINE , l_ALIGNMENT , l_BORDER 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_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; IF l_BORDER is not null and l_BORDER = 1 THEN srw.set_foreground_border_color('black'); srw.set_border_pattern('solid'); END IF; END IF; -- no style is defined, exit from here and leave everything at its defaults return (TRUE); exception when no_data_found then return (TRUE); end; Field: f_chart_col4formattrigger Type: Function Body Formula: function F_chart_col4FormatTrigger 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) := ''; l_BORDER NUMBER(1) := 0; -- this change for every columns l_this_column NUMBER(1) := 3; l_chart_style NUMBER(38) ; l_row_style NUMBER(38) ; l_column_style NUMBER(38) ; l_cell_style NUMBER(38) ; begin -- order in precedence (form least significant to most significant) is as following: -- chart -> column -> row -> cell -- meaning, id cell has a style id defined, use cell style info -- JK Jan 28 2009 9(1): -- This snippet *was* cloned from ChartSummary.rdf file, F_Data field, but it got changed today - big time. -- all styles are being retrieved here -- JK Jan 28 2009 (2): add handler for BORDER attribute style_num := 0; select cl3.style_id into l_cell_style from m_chart c3, chart_column co3, chart_row ro3, chart_cell cl3 where c3.id = :basic_chart_id and co3.chart_data_id = c3.chart_data_id and co3.column_number = l_this_column and ro3.chart_data_id = c3.chart_data_id and ro3.row_number = :chart_row_number and cl3.chart_data_id = c3.chart_data_id and cl3.chart_row_id = ro3.id and cl3.chart_column_id = co3.id; IF l_cell_style is not null AND l_cell_style > 0 THEN style_num := l_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 SELECT map1.REPORT_COLOR_CODE as BG_COLOR, map2.REPORT_COLOR_CODE as TEXT_COLOR, BOLD , ITALIC , UNDERLINE , ALIGNMENT , BORDER INTO l_BG_COLOR, l_TEXT_COLOR, l_BOLD , l_ITALIC , l_UNDERLINE , l_ALIGNMENT , l_BORDER 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_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; IF l_BORDER is not null and l_BORDER = 1 THEN srw.set_foreground_border_color('black'); srw.set_border_pattern('solid'); END IF; END IF; -- no style is defined, exit from here and leave everything at its defaults return (TRUE); exception when no_data_found then return (TRUE); end; Field: f_chart_col5formattrigger Type: Function Body Formula: function F_chart_col5FormatTrigger 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) := ''; l_BORDER NUMBER(1) := 0; -- this change for every columns l_this_column NUMBER(1) := 4; l_chart_style NUMBER(38) ; l_row_style NUMBER(38) ; l_column_style NUMBER(38) ; l_cell_style NUMBER(38) ; begin -- order in precedence (form least significant to most significant) is as following: -- chart -> column -> row -> cell -- meaning, id cell has a style id defined, use cell style info -- JK Jan 28 2009 9(1): -- This snippet *was* cloned from ChartSummary.rdf file, F_Data field, but it got changed today - big time. -- all styles are being retrieved here -- JK Jan 28 2009 (2): add handler for BORDER attribute style_num := 0; select cl3.style_id into l_cell_style from m_chart c3, chart_column co3, chart_row ro3, chart_cell cl3 where c3.id = :basic_chart_id and co3.chart_data_id = c3.chart_data_id and co3.column_number = l_this_column and ro3.chart_data_id = c3.chart_data_id and ro3.row_number = :chart_row_number and cl3.chart_data_id = c3.chart_data_id and cl3.chart_row_id = ro3.id and cl3.chart_column_id = co3.id; IF l_cell_style is not null AND l_cell_style > 0 THEN style_num := l_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 SELECT map1.REPORT_COLOR_CODE as BG_COLOR, map2.REPORT_COLOR_CODE as TEXT_COLOR, BOLD , ITALIC , UNDERLINE , ALIGNMENT , BORDER INTO l_BG_COLOR, l_TEXT_COLOR, l_BOLD , l_ITALIC , l_UNDERLINE , l_ALIGNMENT , l_BORDER 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_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; IF l_BORDER is not null and l_BORDER = 1 THEN srw.set_foreground_border_color('black'); srw.set_border_pattern('solid'); END IF; END IF; -- no style is defined, exit from here and leave everything at its defaults return (TRUE); exception when no_data_found then return (TRUE); end; Field: f_chart_col6formattrigger Type: Function Body Formula: function F_chart_col6FormatTrigger 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) := ''; l_BORDER NUMBER(1) := 0; -- this change for every columns l_this_column NUMBER(1) := 5; l_chart_style NUMBER(38) ; l_row_style NUMBER(38) ; l_column_style NUMBER(38) ; l_cell_style NUMBER(38) ; begin -- order in precedence (form least significant to most significant) is as following: -- chart -> column -> row -> cell -- meaning, id cell has a style id defined, use cell style info -- JK Jan 28 2009 9(1): -- This snippet *was* cloned from ChartSummary.rdf file, F_Data field, but it got changed today - big time. -- all styles are being retrieved here -- JK Jan 28 2009 (2): add handler for BORDER attribute style_num := 0; select cl3.style_id into l_cell_style from m_chart c3, chart_column co3, chart_row ro3, chart_cell cl3 where c3.id = :basic_chart_id and co3.chart_data_id = c3.chart_data_id and co3.column_number = l_this_column and ro3.chart_data_id = c3.chart_data_id and ro3.row_number = :chart_row_number and cl3.chart_data_id = c3.chart_data_id and cl3.chart_row_id = ro3.id and cl3.chart_column_id = co3.id; IF l_cell_style is not null AND l_cell_style > 0 THEN style_num := l_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 SELECT map1.REPORT_COLOR_CODE as BG_COLOR, map2.REPORT_COLOR_CODE as TEXT_COLOR, BOLD , ITALIC , UNDERLINE , ALIGNMENT , BORDER INTO l_BG_COLOR, l_TEXT_COLOR, l_BOLD , l_ITALIC , l_UNDERLINE , l_ALIGNMENT , l_BORDER 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_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; IF l_BORDER is not null and l_BORDER = 1 THEN srw.set_foreground_border_color('black'); srw.set_border_pattern('solid'); END IF; END IF; -- no style is defined, exit from here and leave everything at its defaults return (TRUE); exception when no_data_found then return (TRUE); end; Field: f_chart_col7formattrigger Type: Function Body Formula: function F_chart_col7FormatTrigger 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) := ''; l_BORDER NUMBER(1) := 0; -- this change for every columns l_this_column NUMBER(1) := 6; l_chart_style NUMBER(38) ; l_row_style NUMBER(38) ; l_column_style NUMBER(38) ; l_cell_style NUMBER(38) ; begin -- order in precedence (form least significant to most significant) is as following: -- chart -> column -> row -> cell -- meaning, id cell has a style id defined, use cell style info -- JK Jan 28 2009 9(1): -- This snippet *was* cloned from ChartSummary.rdf file, F_Data field, but it got changed today - big time. -- all styles are being retrieved here -- JK Jan 28 2009 (2): add handler for BORDER attribute style_num := 0; select cl3.style_id into l_cell_style from m_chart c3, chart_column co3, chart_row ro3, chart_cell cl3 where c3.id = :basic_chart_id and co3.chart_data_id = c3.chart_data_id and co3.column_number = l_this_column and ro3.chart_data_id = c3.chart_data_id and ro3.row_number = :chart_row_number and cl3.chart_data_id = c3.chart_data_id and cl3.chart_row_id = ro3.id and cl3.chart_column_id = co3.id; IF l_cell_style is not null AND l_cell_style > 0 THEN style_num := l_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 SELECT map1.REPORT_COLOR_CODE as BG_COLOR, map2.REPORT_COLOR_CODE as TEXT_COLOR, BOLD , ITALIC , UNDERLINE , ALIGNMENT , BORDER INTO l_BG_COLOR, l_TEXT_COLOR, l_BOLD , l_ITALIC , l_UNDERLINE , l_ALIGNMENT , l_BORDER 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_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; IF l_BORDER is not null and l_BORDER = 1 THEN srw.set_foreground_border_color('black'); srw.set_border_pattern('solid'); END IF; END IF; -- no style is defined, exit from here and leave everything at its defaults return (TRUE); exception when no_data_found then return (TRUE); end; Field: f_chart_col8formattrigger Type: Function Body Formula: function F_chart_col8FormatTrigger 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) := ''; l_BORDER NUMBER(1) := 0; -- this change for every columns l_this_column NUMBER(1) := 7; l_chart_style NUMBER(38) ; l_row_style NUMBER(38) ; l_column_style NUMBER(38) ; l_cell_style NUMBER(38) ; begin -- order in precedence (form least significant to most significant) is as following: -- chart -> column -> row -> cell -- meaning, id cell has a style id defined, use cell style info -- JK Jan 28 2009 9(1): -- This snippet *was* cloned from ChartSummary.rdf file, F_Data field, but it got changed today - big time. -- all styles are being retrieved here -- JK Jan 28 2009 (2): add handler for BORDER attribute style_num := 0; select cl3.style_id into l_cell_style from m_chart c3, chart_column co3, chart_row ro3, chart_cell cl3 where c3.id = :basic_chart_id and co3.chart_data_id = c3.chart_data_id and co3.column_number = l_this_column and ro3.chart_data_id = c3.chart_data_id and ro3.row_number = :chart_row_number and cl3.chart_data_id = c3.chart_data_id and cl3.chart_row_id = ro3.id and cl3.chart_column_id = co3.id; IF l_cell_style is not null AND l_cell_style > 0 THEN style_num := l_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 SELECT map1.REPORT_COLOR_CODE as BG_COLOR, map2.REPORT_COLOR_CODE as TEXT_COLOR, BOLD , ITALIC , UNDERLINE , ALIGNMENT , BORDER INTO l_BG_COLOR, l_TEXT_COLOR, l_BOLD , l_ITALIC , l_UNDERLINE , l_ALIGNMENT , l_BORDER 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_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; IF l_BORDER is not null and l_BORDER = 1 THEN srw.set_foreground_border_color('black'); srw.set_border_pattern('solid'); END IF; END IF; -- no style is defined, exit from here and leave everything at its defaults return (TRUE); exception when no_data_found then return (TRUE); end; Field: b_no_chart_data_msg1formattrig Type: Function Body Formula: function B_no_chart_data_msg1FormatTrig return boolean is begin -- Automatically Generated from Report Builder. if (:CS_Chart_Count > '0') then return (FALSE); end if; return (TRUE); end; Field: b_legend_colour_box1formattrig Type: Function Body Formula: function B_LEGEND_COLOUR_BOX1FormatTrig 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_measurements_set_name1format Type: Function Body Formula: function R_MEASUREMENTS_SET_NAME1Format return boolean is begin -- Automatically Generated from Report Builder. if (:CS_TYPE_OF_GRAPH_IMAGE != 'System Generated' or (:CS_Count_Graph_Table_Rows = 0)) then return (FALSE); end if; return (TRUE); end; Field: m_21formattrigger Type: Function Body Formula: function M_21FormatTrigger return boolean is begin if (:CS_Measure_Count < '1') then return (FALSE); end if; return (TRUE); end; Field: m_17formattrigger Type: Function Body Formula: function M_17FormatTrigger return boolean is begin -- Automatically Generated from Report Builder. if (:CS_Chart_Count < 1) then return (FALSE); end if; return (TRUE); end; Field: m_chart_data1formattrigger Type: Function Body Formula: function M_chart_data1FormatTrigger return boolean is begin return (TRUE); end; Field: r_chart_built_manually1formatt Type: Function Body Formula: function R_Chart_Built_Manually1FormatT return boolean is begin return (TRUE); end; Groups Group: main Section Body Section: Frame M_G_TITLE_GRPFR Text Fields Text Field: B_3 Text: * * * e n d o f r e p o r t * * * Section: Frame M_15 Fields Field: F_TARGETS Data Source: target1 Text Fields Text Field: B_OBJECTIVES1 Text: Targets: Section: Frame M_STARTEGY_TITLE Fields Field: F_strategy Data Source: strategy_title Text Fields Text Field: B_strategy Text: Title Section: Frame M_2 Fields Field: F_BUSINESS_NUMBER Data Source: BUSINESS_NUMBER Text Fields Text Field: B_BUSINESS_NUMBER Text: Business Number Section: Frame M_3 Fields Field: F_strat_desc Data Source: strat_desc Text Fields Text Field: B_strat_desc Text: Description Section: Frame M_4 Fields Field: F_status Data Source: strat_status Text Fields Text Field: B_title1 Text: Strategy Status Section: Frame M_5 Fields Field: F_strategy_rs Data Source: strat_rs Text Fields Text Field: B_2 Text: Reporting Status Section: Frame M_8 Fields Field: F_Strategy_RS_Comments Data Source: strat_rs_comments Text Fields Text Field: B_9 Text: Status Comments Section: Frame M_G_Objectives_GRPFR1 Text Fields Text Field: B_OBJECTIVES Text: Objectives Section: Frame M_G_UNIT_ID_GRPFR Text Fields Text Field: B_UNIT Text: Belongs To: Section: Frame M_RELATED_FILES Text Fields Text Field: B_RELATED_FILES Text: Related Files: Text Field: B_10 Text: File Status Text Field: B_12 Text: File Number Text Field: B_13 Text: File Name Text Field: B_19 Text: Organization Unit Section: Frame M_REL_FILES_NAME_NUMBER_STATUS Fields Field: F_record_status Data Source: file_status Field: F_FILE_NUMBER Data Source: file_number Field: F_FILE_NAME Data Source: file_name Section: Frame M_1 Text Fields Text Field: B_18 Text: File Reporting: Section: Frame M_REL_FILES_REPORTING_DATA Fields Field: F_FILE_REPORTING_STATUS Data Source: file_reporting_status Field: F_REP_STATUS_COMMENT Data Source: file_reporting_status_comment Text Fields Text Field: B_36 Text: Reporting Status Text Field: B_37 Text: Reporting Status Comment Section: Frame M_CONTACTS Text Fields Text Field: B_CONTACTS_SECTION_TITLE Text: Contacts: Text Field: B_cont_col3 Text: Contact Team Text Field: B_cont_col2 Text: Contact Role Text Field: B_cont_col1 Text: Contact Name Section: Frame M_CONTACT_TEAMS Fields Field: F_Contact Ministry Data Source: Contact_Portf_Ministry Section: Frame M_6 Fields Field: F_doc_creator Data Source: doc_creator Field: F_co_filename Data Source: co_filename Field: F_doc_date Data Source: doc_date Field: F_doc_pkg_templ_name Data Source: doc_pkg_templ_name Section: Frame M_DOC_TITLE_WRAPPER Fields Field: F_doc_docname Data Source: co_docname Text Fields Text Field: B_DOC_TITLE Text: Title: Section: Frame M_DOC_DESCRIPTION_WRAPPER Fields Field: F_CF_doc_desc_decrypt Data Source: CF_doc_desc_decrypt Text Fields Text Field: B_DOC_DESCRIPTION Text: Description: Section: Frame M_22 Text Fields Text Field: B_METRICS_SECTION_TITLE Text: Metrics: Section: Frame M_21 Text Fields Text Field: B_RELATED_MEASURES Text: Performance Measures: Section: Frame M_16 Fields Field: F_meas_title Data Source: meas_title Field: F_meas_type Data Source: meas_type Field: F_measure_number Data Source: measure_number Section: Frame M_24 Text Fields Text Field: B_NO_METRICS_YET_MSG Text: No measurement data yet. Section: Frame M_GRAPH_DATA_TABULAR_VIEW Text Fields Text Field: B_5 Text: Entered Data Date Text Field: B_14 Text: Target Value Text Field: B_20 Text: Actual Value Text Field: B_21 Text: Total To Date Section: Frame M_MEASURE_HEADERS Fields Field: F_Measure_Title Data Source: measure_type Text Fields Text Field: B_meas_type1 Text: Type Text Field: B_measure_unit1 Text: Measure Number Section: Frame M_13 Text Fields Text Field: B_RELATED_CHARTS Text: Charts: Section: Frame M_12 Fields Field: F_Chart_Description1 Data Source: chart_description Text Fields Text Field: B_32 Text: Description Section: Frame M_10 Fields Field: F_Chart_Data_Source1 Data Source: chart_data_source Text Fields Text Field: B_29 Text: Data Source Section: Frame M_9 Fields Field: F_Chart_Rep_Status_Comment1 Data Source: chart_rep_status_comment Text Fields Text Field: B_27 Text: Status Comment Section: Frame M_17 Fields Field: F_Chart_Header1 Data Source: chart_header Field: F_Chart_Footer1 Data Source: chart_footer Text Fields Text Field: B_EMPTY_FIELD Text: ..... Text Field: B_no_chart_data_msg1 Text: No chart data Margin Section: Margin Fields Field: F_MINISTRY_NAME Data Source: CF_Ministry_Name Field: F_datetime Data Source: CurrentDate Text Fields Text Field: B_OR$BODY_SECTION1 Text: Strategy Summary Report Text Field: B_15 Text: Confidential Text Field: B_16 Text: EVista Report (v&CP_VERSION) Text Field: B_17 Text: Page & of & 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

Title F_strategy
Business Number F_BUSINESS_NUMBER
Description F_strat_desc
Title F_title
> Acronym > Dn
> F_ACRONYM > F_unit_name
> St Objective
> F_st_objective