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 &<PageNumber> of &<TotalPages>
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" %>
<!--
<rw:report id="report">
<rw:objects id="objects">
</rw:objects>
-->
<html>
<head>
<meta name="GENERATOR" content="Oracle 9i Reports Developer"/>
<title> Your Title </title>
</head>
<body>
<!-- Data Area Generated by Reports Developer -->
<rw:dataArea id="MGTITLEGRPFR9">
<rw:foreach id="RGTITLE91" src="G_TITLE">
<!-- Start GetGroupHeader/n --> <table>
<caption> <br>Title <rw:field id="F_strategy" src="strategy" breakLevel="RGTITLE91" breakValue=" "> F_strategy </rw:field><br>
Business Number <rw:field id="F_BUSINESS_NUMBER" src="BUSINESS_NUMBER" breakLevel="RGTITLE91" breakValue=" "> F_BUSINESS_NUMBER </rw:field><br>
Description <rw:field id="F_strat_desc" src="strat_desc" breakLevel="RGTITLE91" breakValue=" "> F_strat_desc </rw:field><br>
Title <rw:field id="F_title" src="title" breakLevel="RGTITLE91" breakValue=" "> F_title </rw:field><br>
</caption>
<!-- End GetGroupHeader/n --> <tr>
<td valign="top">
<table summary="">
<!-- Header -->
<thead>
<tr>
<th <rw:id id="HBACRONYM9" asArray="no"/>> Acronym </th>
<th <rw:id id="HBunitname9" asArray="no"/>> Dn </th>
</tr>
</thead>
<!-- Body -->
<tbody>
<rw:foreach id="RGUNITID91" src="G_UNIT_ID">
<tr>
<td <rw:headers id="HFACRONYM9" src="HBACRONYM9"/>><rw:field id="FACRONYM9" src="ACRONYM" nullValue=" "> F_ACRONYM </rw:field></td>
<td <rw:headers id="HFunitname9" src="HBunitname9"/>><rw:field id="Funitname9" src="unit_name" nullValue=" "> F_unit_name </rw:field></td>
</tr>
</rw:foreach>
</tbody>
<tr>
</tr>
</table>
<table summary="">
<!-- Header -->
<thead>
<tr>
<th <rw:id id="HBstobjective9" asArray="no"/>> St Objective </th>
</tr>
</thead>
<!-- Body -->
<tbody>
<rw:foreach id="RGID191" src="G_ID1">
<tr>
<td <rw:headers id="HFstobjective9" src="HBstobjective9"/>><rw:field id="Fstobjective9" src="st_objective" nullValue=" "> F_st_objective </rw:field></td>
</tr>
</rw:foreach>
</tbody>
<tr>
</tr>
</table>
</td>
</tr>
</table>
</rw:foreach>
<table summary="">
</table>
</rw:dataArea> <!-- id="MGTITLEGRPFR9" -->
<!-- End of Data Area Generated by Reports Developer -->
</body>
</html>
<!--
</rw:report>
-->