Data Sources
Group: G_measure
Data Item: meas_title
Data Type: vchar2
Label: PM:
Expression: m.title
Data Item: object_id
Data Type: vchar2
Label: Object Id
Expression: mber.object_id
Data Item: measurement_id
Label: Measurement Id
Expression: m.id
Data Item: measure_unit
Data Type: vchar2
Label: Measure Unit
Expression: m.measure_unit
Data Item: domain
Label: Sub Domain Id
Expression: mber.sub_domain_id
Data Item: measure_type
Data Type: vchar2
Label: Measure Type:
Expression: mtype.title
Data Item: rep_freq
Data Type: vchar2
Label: Reporting Frequency:
Expression: freq.title
Data Item: data_source
Data Type: vchar2
Label: Data Source:
Expression: m.data_source
Data Item: measure_number
Data Type: vchar2
Label: Measure Number
Expression: m.business_id
Data Item: status_note
Data Type: clob
Label: Status Note:
Expression: reporting_status_data.overall_comment
Data Item: lvl_of_measure
Data Type: vchar2
Label: Lvl Of Measure
Expression: mlvl.title
Data Item: measure_class
Data Type: vchar2
Label: Measure Class
Expression: mcls.title
Data Item: mclass_prompt
Data Type: vchar2
Label: Mclass Prompt
Expression: mcls.mclass_prompt
Data Item: rs_status
Data Type: vchar2
Label: Rs Status
Expression: reporting_status_data.status
Data Item: reporting_to_name
Data Type: vchar2
Label: Reporting To Name
Expression: reporting_to.title
Data Item: Measure_Org_Unit
Data Type: vchar2
Label: Measure Org Unit
Expression: reports_pkg.getentireunitpath ( m.unit_id , reports_pkg.getunitlastname ( m.unit_id ) )
Data Item: starting_point
Label: Starting Point
Expression: : starting_point_id
Data Item: Actuals_Handling_Id
Data Type: vchar2
Label: Actuals Handling Id
Expression: act_type.value_id
Data Item: m_unit_type
Data Type: vchar2
Label: M Unit Type
Expression: DECODE ( measure_unit_type.title , 'Financial Data' , '$' , 'Percentage' , '%' , '' )
Formula: CF_GRAPH_TITLE_TEXT
Data-Type: character
Label: Cf Graph Title Text
Source: cf_graph_title_textformula
Formula: CF_GRAPH_SUBTITLE_TEXT
Data-Type: character
Label: Cf Graph Subtitle Text
Source: cf_graph_subtitle_textformula
Formula: CF_DATA_VALUES
Data-Type: character
Label: Cf Data Values
Source: cf_data_valuesformula
Formula: CF_Actuals_Handling_Label
Data-Type: character
Label: Cf Actuals Handling Label
Source: cf_actuals_handling_labelformu
Group: G_entity
Data Item: parent_id
Data Type: vchar2
Label: Hierarchy Level
Expression: ref.object_id
Data Item: domain_id
Label: Domain Id
Expression: ref.sub_domain_id
Group: G_GRAPHING_ID
Data Item: GRAPHING_ID
Label: Graphing Id
Expression: gv.graphing_id
Data Item: graphing_value_id
Label: Graphing Value Id
Expression: gv.id
Data Item: TARGET_HIGH_VALUE
Label: Target High Value
Expression: gv.TARGET_HIGH_VALUE
Data Item: graphing_measure_id
Label: Graphing Measure Id
Expression: g.measure_id
Data Item: x_axis_value
Data Type: vchar2
Label: X Axis Value
Expression: gv.label
Data Item: ACTUAL_VALUE1
Label: Actual Value1
Expression: gv.measure_value
Data Item: TARGET_VALUE1
Label: Target Value1
Expression: gv.target_value
Group: G_gr_record_date
Data Item: gr_record_date
Data Type: date
Label: Gr Record Date
Expression: a.record_date
Data Item: gr_target_value
Data Type: vchar2
Label: Gr Target Value
Expression: MIN ( to_char ( a.target_value ) || DECODE ( a.high_value , 0 , '' , ' to ' || to_char ( a.high_value ) ) )
Data Item: gr_actual_value
Label: Gr Actual Value
Expression: sum ( a.period_value )
Data Item: gr_total_to_date
Label: Gr Total To Date
Expression: sum ( a.total_value )
Data Item: raw_data_measure_id
Label: Raw Data Measure Id
Expression: : measurement_id
Formula: CF_First_Actuals_Column
Data-Type: number
Label: Cf First Actuals Column
Source: cf_first_actuals_columnformula
Group: G_measurements_set_name
Data Item: measurements_set_name
Data Type: vchar2
Label: Measurements Set Name
Expression: a.title
Data Item: rownum
Label: Rownum
Expression: rownum
Data Item: graphing_id1
Label: Graphing Id1
Expression: a.id
Data Item: legend_measure_id
Label: Legend Measure Id
Expression: measure_id
Group: G_MEASURE_ID
Data Item: MEASURE_ID
Label: Measure Id
Expression: a.MEASURE_ID
Data Item: graphing_id2
Label: Graphing Id2
Expression: a.id
Data Item: system_or_user_generated
Data Type: vchar2
Label: System Or User Generated
Expression: vl.title
Data Item: co_docid
Label: Co Docid
Expression: image_source.co_docid
Data Item: co_versioncomment
Data Type: vchar2
Label: Co Versioncomment
Expression: image_source.co_versioncomment
Data Item: co_filecontent
Data Type: blob
Label: Co Filecontent
Expression: image_source.co_filecontent
Data Item: STATEMENT_OF_RESULT
Data Type: vchar2
Label: Statement Of Result
Expression: TP.STATEMENT_OF_RESULT
Data Item: Line_or_Bar
Data Type: vchar2
Label: Line Or Bar
Expression: graph_type_nt.Line_or_Bar
Group: G_FILE
Data Item: file_name
Data Type: vchar2
Label: File Name
Expression: DECODE ( : P_Report_Type_ID , 2 , 'File: ' , 7 , 'File: ' , '' ) || f.title || ' (' || f.BUSINESS_NUMBER || ')'
Data Item: file_id
Label: File Id
Expression: f.id
Data Item: parent_file_label
Data Type: vchar2
Label: Parent File Label
Expression: DECODE ( : P_Report_Type_ID , 2 , 'Belongs To:' , 7 , 'Belongs To:' , 'File:' )
Data Item: file_status
Data Type: vchar2
Label: File Status
Expression: vl.title
Data Item: file_reporting_status_id
Label: File Reporting Status Id
Expression: rs.status_id
Data Item: file_reporting_status
Data Type: vchar2
Label: File Reporting Status
Expression: rs.title
Group: G_FILE_UNITS
Data Item: unit_file_id
Label: Unit File Id
Expression: fu.FILE_ID
Data Item: file_division
Data Type: vchar2
Label: File Division
Expression: reports_pkg.getEntireUnitPath ( fu.unit_id , reports_pkg.GETUNITLASTNAME ( u.id ) )
Group: G_category
Data Item: sort_by_scope
Label: Sort By Scope
Expression: DECODE ( SUBSTR ( upper ( category_scope.title ) , 1 , 3 ) , 'OPS' , 1 , 'MIN' , 2 , 3 )
Data Item: category_title
Data Type: vchar2
Label: Category Title
Expression: mc.TITLE
Group: G_sub_categories
Data Item: sorting_sub_categories
Label: Sorting Sub Categories
Expression: LEVEL
Data Item: sub_categories_tree
Data Type: vchar2
Label: Sub Categories Tree
Expression: LPAD ( ' ' , ( LEVEL - 1 ) * 7 ) || lvel.TITLE || ': ' || value.title
Group: G_file_strategy
Data Item: strategy_file_id
Label: Strategy File Id
Expression: so.file_id
Data Item: file_strategy_name
Data Type: vchar2
Label: File Strategy Name
Expression: s.TITLE || ' (' || s.BUSINESS_NUMBER || ')'
Group: G_SUBMISSION
Data Item: submission_id
Label: Submission Id
Expression: SUBMISSION_ID
Data Item: submission_name
Data Type: vchar2
Label: Submission Name
Expression: SUBMISSION_NAME
Data Item: submission_type
Data Type: vchar2
Label: Submission Type
Expression: SUBMISSION_TYPE
Data Item: parent_submission_label
Data Type: vchar2
Label: Parent Submission Label
Expression: PARENT_SUBMISSION_LABEL
Data Item: sub_status
Data Type: vchar2
Label: Sub Status
Expression: SUB_STATUS
Group: G_file_to_sub
Data Item: sub_submission_id
Label: Sub Submission Id
Expression: ref.object_id
Data Item: sub_file_id
Label: Sub File Id
Expression: f.file_id
Data Item: sub_file_name
Data Type: vchar2
Label: Sub File Name
Expression: ba_file.title || ' (' || ba_file.business_number || ')'
Group: G_strategies_to_sub_item
Data Item: strat_to_sub_sub_id
Label: Strat To Sub Sub Id
Expression: ref.object_id
Data Item: sub_strategy_name
Data Type: vchar2
Label: Sub Strategy Name
Expression: s.TITLE || ' (' || s.BUSINESS_NUMBER || ')'
Group: G_STRATEGY
Data Item: strategy_name
Data Type: vchar2
Label: Strategy Name
Expression: DECODE ( : P_Report_Type_ID , 2 , 'Strategy: ' , 7 , 'Strategy: ' , '' ) || str.title || ' (' || str.BUSINESS_NUMBER || ')'
Data Item: strategy_id
Label: Id
Expression: str.id
Data Item: parent_strategy_label
Data Type: vchar2
Label: Parent Strategy Label
Expression: DECODE ( : P_Report_Type_ID , 2 , 'Belongs To:' , 7 , 'Belongs To:' , 'Strategy:' )
Data Item: str_reporting_status
Data Type: vchar2
Label: Str Reporting Status
Expression: rs.title
Data Item: str_status
Data Type: vchar2
Label: Str Status
Expression: vl.title
Data Item: str_reporting_status_id
Label: Str Reporting Status Id
Expression: rs.status_id
Group: G_strategy_objective
Data Item: strategy_objective
Data Type: vchar2
Label: Strategy Objective
Expression: so.title
Group: G_RELATED_MEASUREMENTS
Data Item: m_rel_relation_type
Data Type: vchar2
Label: M Rel Relation Type
Expression: DECODE ( m_rel.relation_type , 'LEVEL' , DECODE ( : lvl_of_measure , 'Output' , 'Outcome' , 'Outcome' , 'Output' ) , 'ROLLUP' , 'Rollup' , 'HISTORY' , DECODE ( m_rel.reltype , 'parent' , 'History - Source' , 'History - New' ) )
Data Item: m_related_title
Data Type: vchar2
Label: M Related Title
Expression: m.title
Data Item: m_related_id
Label: M Related Id
Expression: : measurement_id
Data Item: m_related_number
Data Type: vchar2
Label: M Related Number
Expression: m.business_id
Group: G_REL_ACTIVITY_NOTES
Data Item: activity_measurement_id
Label: Activity Measurement Id
Expression: man.metric_id
Data Item: activity_note_id
Label: Activity Note Id
Expression: man.activity_note_id
Data Item: activity_note_number
Data Type: vchar2
Label: Activity Note Number
Expression: an.business_number
Data Item: activity_note_title
Data Type: vchar2
Label: Activity Note Title
Expression: an.title
Group: G_DRIVER
Data Item: SORTING_ORDER
Data Type: vchar2
Label: Sorting Order
Expression: SORTING_ORDER
Data Item: DRIVER_TYPE
Data Type: vchar2
Label: Driver Type
Expression: DRIVER_TYPE
Data Item: STARTING_POINT_ID
Label: Starting Point Id
Expression: STARTING_POINT_ID
Data Item: DRIVER_DOMAIN
Label: Driver Domain
Expression: DRIVER_DOMAIN
Data Item: DRIVER_ID
Data Type: vchar2
Label: Driver Id
Expression: DRIVER_ID
Data Item: DRIVER_NAME
Data Type: vchar2
Label: Driver Name
Expression: DRIVER_NAME
Group: G_strategy_units
Data Item: unit_strategy_id
Label: Unit Strategy Id
Expression: su.Strategy_ID
Data Item: strategy_division
Data Type: vchar2
Label: Strategy Division
Expression: reports_pkg.getEntireUnitPath ( su.unit_id , reports_pkg.GETUNITLASTNAME ( u.id ) )
Group: G_submission_lead_units
Data Item: unit_lead_submission_id
Label: Unit Lead Submission Id
Expression: su.submission_ID
Data Item: submission_lead_division
Data Type: vchar2
Label: Submission Lead Division
Expression: reports_pkg.getEntireUnitPath ( su.unit_id , reports_pkg.GETUNITLASTNAME ( u.id ) )
Group: G_submission_aff_units
Data Item: unit_aff_submission_id
Label: Unit Lead Submission Id1
Expression: su.submission_ID
Data Item: submission_aff_division
Data Type: vchar2
Label: Submission Lead Division1
Expression: reports_pkg.getEntireUnitPath ( su.unit_id , reports_pkg.GETUNITLASTNAME ( u.id ) )
Group: G_ATTRIBUTE_DATA
Data Item: Sort_1
Data Type: vchar2
Label: Sort 1
Expression: lower ( c.title )
Data Item: chart_title
Data Type: vchar2
Label: Chart Title
Expression: c.title
Data Item: basic_chart_id
Label: Basic Chart Id
Expression: c.id
Data Item: chart_header
Data Type: vchar2
Label: Chart Header
Expression: c.header
Data Item: chart_footer
Data Type: vchar2
Label: Chart Footer
Expression: c.footer
Data Item: chart_start_dt
Data Type: date
Label: Period Start
Expression: c.period_start
Data Item: chart_end_dt
Data Type: date
Label: Period End
Expression: c.period_end
Data Item: freq_title
Data Type: vchar2
Label: Freq Title
Expression: freq.freq_title
Data Item: chart_description
Data Type: vchar2
Label: Description
Expression: c.description
Data Item: chart_data_source
Data Type: vchar2
Label: Chart Data Source
Expression: c.data_source
Data Item: chart_reporting_status
Data Type: vchar2
Label: Reporting Status
Expression: rs.status
Data Item: chart_rep_status_comment
Data Type: clob
Label: Reporting Status Comment
Expression: rs.overall_comment
Data Item: sub_domain_id
Label: Sub Domain Id
Expression: parent.sub_domain_id
Data Item: chart_parent_id
Label: Chart Parent Id
Expression: parent.chart_parent_id
Data Item: Chart_Org_Unit
Data Type: vchar2
Label: Chart Org Unit
Expression: reports_pkg.getentireunitpath ( c.unit_id , reports_pkg.getunitlastname ( c.unit_id ) )
Group: G_row_number
Data Item: row_number
Label: Row Number1
Expression: ch_rows.row_number
Group: G_COLUMN_NUMBER
Data Item: COLUMN_NUMBER
Label: Column Number
Expression: ch_cols.COLUMN_NUMBER
Group: G_data
Data Item: CHART_DATA_ID
Label: Chart Data Id
Expression: ch_cols.CHART_DATA_ID
Data Item: row_id
Label: Row Id
Expression: ch_rows.row_id
Data Item: CHART_ID1
Label: Chart Id1
Expression: c.id
Data Item: COLUMN_ID
Label: Column Id
Expression: ch_cols.COLUMN_ID
Data Item: data
Data Type: vchar2
Label: Data
Expression: cl.data
Data Item: cell_style
Label: Cell Style
Expression: cl.style_id
Report Parameters
Parameter: P_MIN_ID
Data Type: number
Initial Values: 47
Parameter: P_OBJECT_ID
Data Type: number
Initial Values: -1
Parameter: P_SESSION_ID
Data Type: character
Initial Values: 1284745256872
Parameter: P_GRAPH_TYPE
Data Type: character
Initial Values: BAR_VERT_CLUST
Parameter: P_Report_Type_ID
Data Type: number
Initial Values: 3
Parameter: SP_Prompt_Value_Set_ID
Data Type: character
Initial Values: 1072,937
Parameter: P_Report_Valid_Set_ID
Data Type: number
Initial Values: 26744
Parameter: P_STATUS_REQUEST_ID
Data Type: number
Tables
Table: Q_MEASUREMENT
SQL Command: -- *******************************************************************************************************************************
-- Jaroslav Kolar, Aug 13 2008 re-coded a bit REPORTING status and note, e.g added mega nested table
-- *******************************************************************************************************************************
SELECT
mber.object_id -- parent (file, submission or strategy) entity id, to be linked to Q_PARENT query
, mber.sub_domain_id domain
, m.title meas_title
--, m.measure_unit
, m.data_source
, m.id as measurement_id
, mtype.title measure_type
, freq.title rep_freq
, m.business_id measure_number
--- not type, measure UNIT , NVL(mu_type.title, '???') measure_unit_type
, m.measure_unit
, reporting_status_data.overall_comment as status_note
, mlvl.title lvl_of_measure
, mcls.title measure_class
, mcls.mclass_prompt
, reporting_status_data.status as rs_status
, reporting_to.title as reporting_to_name
, reports_pkg.getentireunitpath( m.unit_id, reports_pkg.getunitlastname(m.unit_id)) as Measure_Org_Unit
, :starting_point_id as starting_point
, act_type.value_id as Actuals_Handling_Id
, DECODE( measure_unit_type.title, 'Financial Data', '$', 'Percentage', '%', '') as m_unit_type
FROM
-- this nested table equals to 'm_measure' table for all but 'MeasureByActivityNotes' (report_type ==7) and MetricsByOrgUnit (report_type ==3)
-- and in that special case, the result set is a function of a parameter ('starting_point_id') all the way up from
-- Q_DRIVER query. This was needed to satisfy the requirement that states that the same measure should not
-- be listed for both child and parent note. And as a rule, if the note is linked to child note, it's automatically listed to the parent note too
-- the neater approach would be to implement this filtering when building 'CP_Q_MEASUREMENT_MEASUR_FILTER' but it was not
-- possible ( error: 'wrong frequency' ) as that is taking place in BEFOREREPORT trigger
( select * from m_measure where :P_Report_Type_ID not in (3,7)
UNION ALL
select * from m_measure where :P_Report_Type_ID = 3 and unit_id = :starting_point_id
UNION ALL
select m2.*
from m_metric_activity_note man2, activity_note an2, m_measure m2
where man2.activity_note_id = an2.id
and m2.id = man2.metric_id
and not exists ( select 1
from m_metric_activity_note inner_man2
, activity_note inner_an2
, (
-- JK 30 Jan 2009: the same subquery is implemented in 'Q_DRIVER' so any changes here have to be copied there ( and reasoning is described there)
select pv.value_numeric
from context_prompt cp, report_context rc, report_prompt rp, prompt_group pg, prompt_value_set pvs, prompt_value pv, activity_note
where cp.report_prompt_id = rp.id
and rp.prompt_group_id = pg.id
and rp.prompt_number = 6
and rc.id = cp.report_context_id
and report_id = 38
and pvs.OWNER_TYPE = 'USER'
and pvs.report_context_id = rc.id
and pv.prompt_value_set_id = pvs.id
and pv.report_prompt_id = rp.id
and pv.prompt_value_set_id = :P_Prompt_Value_Set_ID
and :CP_Activity_Notes_Selected = 1
-- join child notes
and activity_note.parent_id is not null
and activity_note.id = pv.value_numeric
UNION ALL
select id as value_numeric from activity_note where :CP_Activity_Notes_Selected = 0 and parent_id is not null
) eligible_child_notes
where inner_man2.activity_note_id = inner_an2.id
and inner_man2.metric_id = man2.metric_id
and inner_an2.parent_id = an2.id
--- join
and eligible_child_notes.value_numeric = inner_an2.id
)
-- JK 2-MAR-2009 let's try with activity note Id, not metric-activity-note Id
-- and man2.id = :starting_point_id
and man2.activity_note_id = :starting_point_id
and :P_Report_Type_ID = 7
) m
, M_BUSINESS_E_REF mber
, VALUE_LIST mtype
, VALUE_LIST freq
, VALUE_LIST mu_type
, VALUE_LIST mlvl
, (SELECT id, title, 'Measure Class:' mclass_prompt FROM VALUE_LIST mclass WHERE mclass.TYPE = 'MEASURE_CLASS') mcls
, (SELECT id, title FROM VALUE_LIST WHERE TYPE = 'MEASURE_REPORTING_LEVEL') reporting_to
-- ****************************************************************************
-- nested (simplified) table to get reporting statuses and note - S T A R T
-- ***************************************************************************
,(
SELECT
a.object_id
, a.status_id
, reporting_status_values.title as status
, notes.description as overall_comment
FROM report_status a
, (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id) notes
WHERE
a.sub_domain_id = 2
and ( a.ATTRIBUTE is null )
and a.ACTIVE = 1
-- join softly VALUE_LIST table(s) ONCE for EACH attribute
and reporting_status_values.id(+) = a.status_id
-- join softly NOTE table(s)
and notes.object_class_id(+) = a.id
) reporting_status_data
-- ****************************************************************************
-- nested (simplified) table to get reporting statuses and note - E N D
-- ***************************************************************************
-- add value list to show 'actuals handling'
, value_list act_type
, value_list measure_unit_type
WHERE m.ber_id = mber.id
AND mtype.type = 'MEASURE_TYPE'
AND m.type_id = mtype.id
AND m.recording_frequency_id = freq.id
AND m.measure_unit_type_id = mu_type.id(+) -- Oct 22 this should be NOT outer join
AND m.level_id = mlvl.id
-- JK Jan-2009 class is not mandatory so change 'm.class_id(+) = mcls.id' to 'm.class_id = mcls.id(+) '
AND m.class_id = mcls.id(+)
AND reporting_status_data.object_id(+) = m.id
AND reporting_to.id(+) = m.reporting_level_id
AND m.actuals_handling_id = act_type.id
AND m.measure_unit_type_id = measure_unit_type.id(+)
&CF_Q_MEASUREMENT_MEASUR_FILTER
ORDER BY 1,2
Table: Q_PARENT_ENTITY
SQL Command: SELECT DISTINCT ref.object_id as parent_id
, ref.sub_domain_id as domain_id
FROM m_business_e_ref ref
-- nesting union of 3 tables is actually needed here because in some circumstances,
-- all three types (domains) of parent entities will be listed
, (
select bf.id, bf.business_number
from ba_file bf
&CP_Q_PARENT_FILES_FILTER
union all
select s.id, s.business_number
from submission s
&CP_Q_PARENT_SUBMISSIONS_FILTER
union all
select bs.id, bs.business_number
from ba_strategy bs
&CP_Q_PARENT_STRATEGIES_FILTER
) parent_entity
WHERE
parent_entity.id = ref.object_id
and
(
( :P_Report_Type_ID in (2,1) and ref.id = (select ber_id from m_measure where id = :p_object_id) AND ref.sub_domain_id in (1,3,4))
OR
( :P_Report_Type_ID=4 and ref.object_id = :p_object_id AND ref.sub_domain_id = 4 )
OR
( :P_Report_Type_ID=1 and ref.object_id = :p_object_id AND ref.sub_domain_id = 1 )
OR
( :P_Report_Type_ID=3 and ref.sub_domain_id = :driver_domain and ref.object_id = :driver_id )
OR
( :P_Report_Type_ID=7 and ref.sub_domain_id in (1,3,4) and ref.sub_domain_id = :driver_domain and ref.object_id = :driver_id )
OR
( :P_Report_Type_ID=5 and ref.sub_domain_id in (4) ) -- there is NO object_id in this clause
OR
( :P_Report_Type_ID=6 and ref.sub_domain_id = :driver_domain and ref.object_id = :driver_id)
)
ORDER BY 1
Table: Q_GRAPHING_VALUES
SQL Command: -- Jaroslav, Aug 12 2008 added M_TARGET_PORTFOLIO to the query,
-- which probably makes M_GRAPHING_VALUE and nested 'a' table obsolete
-- that was meant for VARIABLE number of target sets.
-- Anyway, I will leave it for now.
-- NOTE: watch out when introducing column aliases as &CP_.....
-- that column might not show up AT ALL so query-rebuild might be the only solution
-- JK: 25-Jan-2009 - recode
SELECT
gv.graphing_id
, gv.label as x_axis_value
, gv.measure_value as ACTUAL_VALUE1
, gv.target_value as TARGET_VALUE1
, gv.TARGET_HIGH_VALUE
, g.measure_id as graphing_measure_id
, gv.id as graphing_value_id
FROM m_graphing g
, m_graphing_value gv
WHERE gv.GRAPHING_ID = g.id
and g.measure_id = :measurement_id -- value from the parent table
-- this is taking the value from parent query and basically killing the result set if graph is marked as 'User Provided'
and :system_or_user_generated = 'System Generated'
ORDER BY gv.graphing_id
-- this is to try to solve the problm when x-axis labls were NOT in order
, gv.id
Table: Q_GRAPH_DATA_TABULAR_VIEW
SQL Command:
/*
JK Jan 8 2009 add 'm_graphing' table to second query in UNION ALL clause.
It's NOT to get any data for a result set but to use it as a filter to make sure that only data from a single target set is displayed
It used to be 'm_target_portfolio.is_master' column used for that, and now is m_graphing.target_portfolio_id'
*/
SELECT a.record_date as gr_record_date
-- Jan 29 2009 - using "min()" here because "sum()" would not take non-digits. Should be the same result, as there is
, MIN( to_char( a.target_value ) || DECODE( a.high_value, 0, '', ' to ' || to_char(a.high_value) ) ) as gr_target_value
, sum(a.period_value) as gr_actual_value
, sum(a.total_value) as gr_total_to_date
, :measurement_id as raw_data_measure_id
FROM
(
-- first union provides only actual values, and always null as 'Target Value'
select a.record_date
, Null as target_value
, a.period_value
, a.total_value
, a.measure_id
, 0 high_value
from m_actual_record a
where a.measure_id = :measurement_id
union all
-- second union provides only 'Target Value'' and nulls instead of actual values
select
t.RECORD_DATE
-- JK Sep 24, 2008 do note that in m_target_record, the values are in VALUE column, unlike in m_actual_record where it's in PERIOD_VALUE
, t.value as PERIOD_VALUE
, Null
, Null
, tp.MEASURE_ID
, t.high_value
from
m_target_record t
, m_target_portfolio tp
, m_graphing g
where tp.MEASURE_ID = :measurement_id
and t.TARGET_PORTFOLIO_ID = tp.ID
-- JK 16-DEC-2008 obsolete check: and tp.IS_MASTER = 1
and g.target_portfolio_id = tp.id and g.measure_id = tp.measure_id
) a
GROUP BY a.RECORD_DATE
ORDER BY a.RECORD_DATE
Table: Q_LEGENDS
SQL Command: ----------------------------------------------------------
------------ build legends query --------------
----------------------------------------------------------
-- as of Rel 3.0, there will be only one record (per measurement) in 'm_graphing' table
SELECT a.title as measurements_set_name, rownum, a.id as graphing_id, measure_id as legend_measure_id
FROM (
select distinct 'Targets (' || tp3.TITLE || ')' as title
, g3.id
, tp3.MEASURE_ID
from m_target_portfolio tp3, m_graphing g3
where tp3.MEASURE_ID = :measure_id
and g3.TARGET_PORTFOLIO_ID = tp3.id
-- JK 16-DEC-2008 obsolete check: and tp3.is_master = 1
UNION ALL
-- retrieval from 'dual' that will ensure that acutals record is always there
select 'Actuals', 0, :measure_id as measure_id from dual
UNION ALL
-- third part of the union, will return a result set only id targets are declaed as ranges
select distinct 'Targets High Values' as title
, g3.id
, tp3.MEASURE_ID
from m_target_portfolio tp3 , m_graphing g3, value_list vl
where tp3.MEASURE_ID = :measure_id
and g3.TARGET_PORTFOLIO_ID = tp3.id
and vl.type = 'MEASURE_TARGET_DEFINITION'
and tp3.definition_id = vl.id
and vl.title = 'Range'
) a
-- JK Oct 23, can I assume :CP_SERIES_COUNT is 1 all the time ????
-- WHERE rownum <= :CP_SERIES_COUNT + 1 -- adding one is because actuals are handled differently
WHERE rownum <= 3
-- this is taking the value from parent query and basically killing the result set if graph is marked as 'User Provided'
and :system_or_user_generated = 'System Generated'
ORDER BY 3
Table: Q_GRAPH_OF_MASTER_TARGET
SQL Command: ---------------------------------------------------------------------------------------------------------------------------
------------------- NOTE: this will retun a SINGLE graphing id, but only because target portfolio
------------------- selected is MASTER target portfolio
------------------- otherwise, it one-to_many relationsio between measure and graphs
--------------------------------------------------------------------------------------------------------------------------
SELECT
a.MEASURE_ID
, a.id as graphing_id
, vl.title as system_or_user_generated
, TP.STATEMENT_OF_RESULT
-- add image for measure.
-- there can be many graphs for a single measurement, but as we will
-- consider only MASTER portfolio, effectively there will be one image per measurement
,image_source.co_docid
,image_source.co_versioncomment
,image_source.co_filecontent
-- similar rational as for images - read m_graphing data of master target portfolio only. in order to figure out graph type
, graph_type_nt.Line_or_Bar
FROM
m_graphing a
, value_list vl
, m_target_portfolio tp
-- TODO explain nested 'image_siurce' table
, (select doc_versions.co_docid, doc_versions.co_versioncomment, doc_versions.co_filecontent, :measurement_id as measure_id
from (select b.co_menutext, b.co_menuid, level, sys_connect_by_path( co_menutext, '\' ) path
from co_menus b
connect by prior b.co_menuid = b.co_menuparent
start with b.co_menuid in (
select a.co_menuid from co_menus a where a.co_menuparent = 0 and a.co_menuref =
-- s t a r t give me graphing id that corresponds to master portfolio, for known measure id
(select '22_' || to_char(nvl(max(g2.id),0))
from m_graphing g2, m_target_portfolio tp2
where g2.measure_id = :measurement_id
and tp2.measure_id = g2.measure_id
and tp2.id = g2.target_portfolio_id
-- JK 16-DEC-2008 obsolete check: and tp2.is_master = 1
)
-- e n d give me graphing id that corresponds to master portfolio, for known measure id
)
) cm
, co_document doc
, document_type dt
, (
select ver.co_docid, ver.co_versioncomment, ver.co_filecontent
from co_versions ver
where ver.co_version = ( select max(ver2.co_version) from co_versions ver2 where ver2.co_docid = ver.co_docid)
-- skip all but .bmp, .jpg and .gif files (JK 5-Mar-2009)
and upper(substr(ver.co_filename, instr(ver.co_filename, '.', -1, 1) + 1)) in ('JPG', 'BMP', 'GIF')
) doc_versions
where doc.co_menuid = cm.co_menuid
and doc.co_doctype = dt.id(+)
-- join ('vrsions' ) table that contains the image
and doc.co_docid = doc_versions.co_docid
) image_source
-- create table that'll give graph type for master target set Line or Bar, based on 'm_graphing.format_id' value
-- 'decode' is to translate to Report Builder dictionary
, ( select vl2.id
, DECODE(vl2.title, 'Line', 'LINE_VERT_ABS', 'Bar', 'BAR_VERT_CLUST', 'LINE_VERT_ABS' ) as Line_or_Bar
from value_list vl2
where vl2.type = 'MEASURE_GRAPH_PRESENTATION'
) graph_type_nt
where a.MEASURE_ID = :measurement_id
and vl.type = 'MEASURE_GRAPH_TYPE'
and vl.id = a.TYPE_ID
and a.TARGET_PORTFOLIO_ID = tp.id
-- JK 16-DEC-2008 obsolete check: and tp.IS_MASTER = 1
-- image part might not have anything
and a.MEASURE_ID = image_source.measure_id(+)
-- join nested table to give us graph type
and graph_type_nt.id = a.format_id
Table: Q_FILE
SQL Command: -- For simple Measure summary, parent label is 'Belongs To:', and title is prefixed by 'File:'.
-- Otherwise, parent label is 'File:'.
SELECT
DECODE(:P_Report_Type_ID, 2, 'Belongs To:', 7, 'Belongs To:', 'File:') parent_file_label,
DECODE(:P_Report_Type_ID, 2, 'File: ', 7, 'File: ', '') || f.title || ' (' || f.BUSINESS_NUMBER || ')' as file_name
, f.id as file_id
, vl.title as file_status
, rs.title as file_reporting_status
, rs.status_id as file_reporting_status_id
FROM ba_file f
, value_list vl
, (
select
a.object_id
, a.status_id
, reporting_status_values.title
from report_status a
, (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values
where
a.sub_domain_id = 4
and ( a.ATTRIBUTE is null )
and a.ACTIVE = 1
-- join softly VALUE_LIST table(s) ONCE for EACH attribute
and reporting_status_values.id(+) = a.status_id
) rs
WHERE f.id = :parent_id
and :domain_id = 4
and vl.type = 'EASY_STATUS'
and vl.id = f.status_id
and f.id = rs.object_id(+)
Table: Q_FILE_UNITS
SQL Command: select fu.FILE_ID as unit_file_id
, reports_pkg.getEntireUnitPath( fu.unit_id, reports_pkg.GETUNITLASTNAME(u.id )) as file_division
from file_unit fu, unit u
where u.ID = fu.UNIT_ID
and fu.file_id = :file_id
Table: Q_MNGMNT_CATEGORIES
SQL Command: -- Jaroslav Feb 24 2009
-- I guess this (sorting) fix should work - forst category scope ('sort_by_scope'), then cateory name ('category_title') and then level ('sorting_sub_categories')
-- also, I don't see why we need 'category_scope' any more. Commenting it out.
-- ******************************************************************************************************************
select --DECODE(SUBSTR( upper(category_scope.title),1,3), 'OPS', 'OPS Wide Categories', 'MIN', 'Ministry Wide Categories', 'Division Wide Categories') as category_scope
mc.TITLE as category_title
, LPAD(' ', (LEVEL - 1)*7) || lvel.TITLE || ': ' || value.title as sub_categories_tree
, DECODE(SUBSTR( upper(category_scope.title),1,3), 'OPS', 1, 'MIN', 2, 3 ) as sort_by_scope
, LEVEL as sorting_sub_categories
from MGMT_CATEGORY_VALUE value
, MGMT_CATEGORY_LEVEL lvel
, MANAGEMENT_CATEGORY mc
, (select title, id from value_list where type = 'MANAGEMENT_CATEGORY_SCOPE') category_scope
where value.MGMT_CATEGORY_LEVEL_ID = lvel.ID
and mc.ID = lvel.MANAGEMENT_CATEGORY_ID
and category_scope.id = mc.SCOPE_ID
and mc.ENABLED = 1
and value.id in (
-- nested_2: expanded a list from 'nested_1' with parent IDs
select distinct id
from MGMT_CATEGORY_VALUE
START WITH id in (
-- nested_1 : list only values linked to to given file ID
select value2.id from
MGMT_CATEGORY_VALUE value2
,FILE_MANAGEMENT_CATEGORY fmc2
where fmc2.FILE_ID = :FILE_ID
and value2.id = fmc2.MGMT_CATEGORY_VALUE_ID
)
CONNECT BY PRIOR PARENT_ID = id
)
START WITH value.PARENT_ID is null
CONNECT BY PRIOR value.id = value.PARENT_ID
order by 3, 1, 4
Table: Q_FILE_STRATEGY
SQL Command: select distinct
so.file_id as strategy_file_id
-- , so.STRATEGY_ID
-- , so.OBJECTIVE_ID
, s.TITLE || ' (' || s.BUSINESS_NUMBER || ')' as file_strategy_name
-- , o.TITLE as objective_name
from
FILE_STRATEGY_OBJECTIVE so
, ba_strategy s
, ba_strategy_objective o
where s.ID = so.STRATEGY_ID
and o.ID(+) = so.OBJECTIVE_ID
and so.FILE_ID = :file_id
Table: Q_SUBMISSION
SQL Command: select
DECODE(:P_Report_Type_ID, 2, 'Belongs To:', 7, 'Belongs To:', 'Submission:') parent_submission_label,
DECODE(:P_Report_Type_ID, 2, 'Submission: ', 7, 'Submission: ', '') || DECODE(s.PREV_ADMIN_FLAG,
0, s.title || ' (' || s.BUSINESS_NUMBER || ')',
1, DECODE(NVL(r.PREV_ADMIN_ACCESS, 0), 1, s.title || ' (' || s.BUSINESS_NUMBER || ')', s.BUSINESS_NUMBER))
as submission_name
, vl.title || ' / ' || st.title as submission_type
, s.id as submission_id
, vl2.title as sub_status
from submission s
INNER JOIN submission_type st ON st.id=s.submission_type_id
INNER JOIN value_list vl ON vl.id = st.submission_category_id
INNER JOIN value_list vl2 ON vl2.id = s.status_id
LEFT JOIN (
SELECT DISTINCT 1 PREV_ADMIN_ACCESS
FROM REPORT_VALID_SET rvs
INNER JOIN EASY_USER eu ON eu.ID=rvs.CREATED_BY_USER_ID
INNER JOIN ROLE_PORTFOLIO rp ON rp.EASY_USER_ID=eu.ID
INNER JOIN EASY_ROLE er ON er.ID=rp.EASY_ROLE_ID
INNER JOIN UNIT u ON u.ID=er.UNIT_ID
INNER JOIN MER_PORTFOLIO mp ON mp.EASY_ROLE_ID=rp.EASY_ROLE_ID
INNER JOIN MER_PORTFOLIO_FUNCTION_GROUP mpfg ON mpfg.MER_PORTFOLIO_ID=mp.ID
INNER JOIN FUNCTION_GROUP fg ON fg.ID=mpfg.FUNCTION_GROUP_ID
INNER JOIN RESOURCE_ACCESS_PRIVILEGE rap ON rap.FUNCTION_GROUP_ID=fg.ID
INNER JOIN RESOURCE_1 r ON r.ID = rap.RESOURCE_ID
WHERE rvs.ID=:P_Report_Valid_Set_ID
AND u.MINISTRY_ID=:P_MIN_ID
AND r.NAME='previous.government'
AND r.ATTRIBUTE='submission'
AND rap.PRIVILEGE!=0
) r ON 1=1
where s.id = :parent_id
and :domain_id = 1
and vl2.type = 'EASY_STATUS'
Table: Q_files_to_sub_item
SQL Command: select distinct
ref.object_id as sub_submission_id
, f.file_id as sub_file_id
, ba_file.title || ' (' || ba_file.business_number || ')' as sub_file_name
from ba_ber_file f
-- join table that shows what type of the referenced object
, ba_business_entity_ref ref
, ba_file
where ref.id = f.ber_id
-- keep only submissions
and ref.sub_domain_id = 1
-- join to submission table
and ref.object_id = :submission_id
-- join file
and ba_file.id = f.file_id
Table: Q_strategies_to_sub_item
SQL Command: select distinct
ref.object_id as strat_to_sub_sub_id
-- ,f.file_id
, s.TITLE || ' (' || s.BUSINESS_NUMBER || ')' as sub_strategy_name
-- , s.id as sub_strategy_id
from ba_ber_file f
-- join table that shows what type of the referenced object
, ba_business_entity_ref ref
, ba_file
, FILE_STRATEGY_OBJECTIVE so
, ba_strategy s
, ba_strategy_objective o
where ref.id = f.ber_id
-- keep only submissions
and ref.sub_domain_id = 1
-- join to sun table
and ref.object_id = :sub_submission_id
-- join file
and ba_file.id = f.file_id
-- join strategies to file
and so.FILE_ID = f.file_id
and s.ID = so.STRATEGY_ID
and o.ID(+) = so.OBJECTIVE_ID
Table: Q_STRATEGY
SQL Command: -- Select all Strategies that are related to the current measure, plus
-- any Strategic Objectives (but only for Measure Summary version of report)
-- For simple Measure summary, parent label is 'Belongs To:', and title is prefixed by 'Strategy:'.
-- Otherwise, parent label is 'Strategy:'.
select
DECODE(:P_Report_Type_ID, 2, 'Belongs To:', 7, 'Belongs To:', 'Strategy:') parent_strategy_label,
DECODE(:P_Report_Type_ID, 2, 'Strategy: ', 7, 'Strategy: ', '') || str.title || ' (' || str.BUSINESS_NUMBER || ')' as strategy_name
, str.id as strategy_id
, so.title as strategy_objective
, vl.title as str_status
, rs.title as str_reporting_status
, rs.status_id as str_reporting_status_id
from BA_STRATEGY str,
(select * from M_METRIC_STRATEGY_OBJECTIVE
where metric_id = :P_Object_ID
and :P_Report_Type_ID = 2) mso,
BA_STRATEGY_OBJECTIVE so
, value_list vl
, (
select
a.object_id
, a.status_id
, reporting_status_values.title
from report_status a
, (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values
where
a.sub_domain_id = 3
and ( a.ATTRIBUTE is null )
and a.ACTIVE = 1
-- join softly VALUE_LIST table(s) ONCE for EACH attribute
and reporting_status_values.id(+) = a.status_id
) rs
where str.id = :parent_id
and :domain_id = 3
and str.id = mso.strategy_id(+)
and mso.objective_id = so.ID(+)
and vl.type = 'EASY_STATUS'
and vl.id = str.status_id
and str.id = rs.object_id(+)
Table: Q_RELATED_MEASUREMENTS
SQL Command: /*
SELECT DISTINCT
DECODE(m_rel.relation_type, 'LEVEL', DECODE(:lvl_of_measure, 'Output', 'Outcome', 'Outcome', 'Output'),
'HISTORY', DECODE(m_rel.reltype, 'parent', 'History - Source', 'History - New') ) as m_rel_relation_type,
m.business_id as m_related_number,
m.title as m_related_title,
:measurement_id as m_related_id
FROM
m_measure m
, (select relation_type, child_measure_id as related_measure_id, 'child' as reltype
from m_measure_measure mm
where parent_measure_id = :measurement_id
union all
select relation_type, parent_measure_id, 'parent' as reltype
from m_measure_measure mm
where child_measure_id = :measurement_id
) m_rel
WHERE m.id = m_rel.related_measure_id
*/
SELECT DISTINCT
DECODE(m_rel.relation_type,
'LEVEL', DECODE(:lvl_of_measure, 'Output', 'Outcome', 'Outcome', 'Output'),
-- JK 7-MAY-09 Add Rollup. Don't knmow why it wasn't there
'ROLLUP', 'Rollup',
'HISTORY', DECODE(m_rel.reltype, 'parent', 'History - Source', 'History - New')
) as m_rel_relation_type,
m.business_id as m_related_number,
m.title as m_related_title,
:measurement_id as m_related_id
FROM
m_measure m
, (select relation_type, child_measure_id as related_measure_id, 'child' as reltype
from m_measure_measure mm
where parent_measure_id = :measurement_id
-- JK 7-MAY-09 do not show Rollup if base neasure is a source of a rollup measure. The app has a tab for it
and relation_type != 'ROLLUP'
union all
select relation_type, parent_measure_id, 'parent' as reltype
from m_measure_measure mm
where child_measure_id = :measurement_id
) m_rel
WHERE m.id = m_rel.related_measure_id
Table: Q_REL_ACTIVITY_NOTES
SQL Command: SELECT man.metric_id as activity_measurement_id
, man.activity_note_id
, an.business_number as activity_note_number
, an.title as activity_note_title
FROM m_metric_activity_note man, activity_note an
WHERE an.id = man.activity_note_id
and man.metric_id = :measurement_id
Table: Q_DRIVER
SQL Command: SELECT
*
FROM
TABLE(
R_MEASURESUMMARY.GETDRIVERS(
:p_min_id,
:p_prompt_value_set_id,
:CP_Prompt_Runtime_Value_Set_ID,
:p_report_valid_set_id,
:p_report_type_id,
:p_object_id,
:CP_ACTIVITY_NOTES_SELECTED,
:CP_ORG_UNITS_SELECTED,
:CP_ORG_UNITS_SELECTED_SR,
:CP_MC_LEVEL_ID
)
);
Table: Q_strategy_units
SQL Command: select su.Strategy_ID as unit_strategy_id
, reports_pkg.getEntireUnitPath( su.unit_id, reports_pkg.GETUNITLASTNAME(u.id )) as strategy_division
from ba_strategy_unit su, unit u
where u.ID = su.UNIT_ID
and su.strategy_id = :strategy_id
Table: Q_Submission_Lead_Units
SQL Command: -- all lead units
select su.submission_ID as unit_lead_submission_id
, reports_pkg.getEntireUnitPath( su.unit_id, reports_pkg.GETUNITLASTNAME(u.id )) as submission_lead_division
from submission_unit su, unit u, value_list vl
where u.ID = su.UNIT_ID
and su.submission_id = :submission_id
and su.type_id = vl.id
and vl.type = 'CONTACT_MINISTRY_TYPE'
and vl.value_id = 1
Table: Q_Submission_Affected_Units
SQL Command: -- all 'affected' units
select su.submission_ID as unit_aff_submission_id
, reports_pkg.getEntireUnitPath( su.unit_id, reports_pkg.GETUNITLASTNAME(u.id )) as submission_aff_division
from submission_unit su, unit u, value_list vl
where u.ID = su.UNIT_ID
and su.submission_id = :submission_id
and su.type_id = vl.id
and vl.type = 'CONTACT_MINISTRY_TYPE'
and vl.value_id = 2
Table: Q_CHART_ATTRIBUTES
SQL Command: SELECT
c.id as basic_chart_id
, c.title as chart_title
, c.header as chart_header
, c.footer as chart_footer
, c.period_start as chart_start_dt
, c.period_end as chart_end_dt
, freq.freq_title
, c.description as chart_description
, c.data_source as chart_data_source
, rs.status as chart_reporting_status
, rs.overall_comment as chart_rep_status_comment
, parent.chart_parent_id
, parent.sub_domain_id
-- JK Apr 9 2009
, reports_pkg.getentireunitpath( c.unit_id, reports_pkg.getunitlastname(c.unit_id)) as Chart_Org_Unit
, lower( c.title ) as Sort_1
FROM
m_chart c
, ( select id as freq_id, title as freq_title from value_list where type = 'MEASURE_RECORDING_FREQUENCY' ) freq
-- nested table that gived ber_id for given parent S T A R T
, (
select ber.id as chart_ber_id, f.id as chart_parent_id, ber.sub_domain_id
from m_business_e_ref ber, ba_file f
where ber.sub_domain_id = 4
and f.id = ber.object_id
and f.id = :driver_id
and ber.sub_domain_id = :driver_domain
union all
select ber.id, str.id, ber.sub_domain_id
from m_business_e_ref ber, ba_strategy str
where ber.sub_domain_id = 3
and str.id = ber.object_id
and str.id = :driver_id
and ber.sub_domain_id = :driver_domain
union all
select ber.id , s.id , ber.sub_domain_id
from m_business_e_ref ber, submission s
where ber.sub_domain_id = 1
and s.id = ber.object_id
and s.id = :driver_id
and ber.sub_domain_id = :driver_domain
) parent
-- nested table that gived ber_id for given parent E N D
-- nested table with reporting status and comment S T A R T
, (
select
a.object_id
, a.status_id
, reporting_status_values.title as status
-- comments from NOTE table
, notes.description as overall_comment
from report_status a
, (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id) notes
where
a.sub_domain_id = 19
and ( a.attribute is null )
and a.active = 1
-- join softly VALUE_LIST table(s) ONCE for EACH attribute
and reporting_status_values.id(+) = a.status_id
-- join softly NOTE table(s)
and notes.object_class_id(+) = a.id
) rs
-- nested table with reporting status and comment E N D
, v_report_valid_data vld_charts
WHERE
parent.chart_ber_id = c.ber_id
and c.recording_frequency_id = freq_id(+)
and c.id = rs.object_id(+)
-- limit only to charts that we are alowed to see
and c.id = vld_charts.object_id
and vld_charts.sub_domain_id = 19
and vld_charts.report_valid_set_id = :P_Report_Valid_Set_ID
ORDER BY Sort_1
Table: Q_CHART
SQL Command: SELECT
c.id as chart_id,
ch_rows.row_number,
ch_rows.row_id,
ch_cols.*,
cl.data as data,
cl.style_id as cell_style
FROM
m_chart c,
(
select
r.chart_data_id,
r.row_number,
r.id as row_id
from
chart_row r
) ch_rows,
(
select
col.chart_data_id,
col.column_number,
col.id as column_id
from
chart_column col
where
col.column_number < 8
) ch_cols,
(
select
c1.chart_data_id,
c1.chart_row_id,
c1.chart_column_id,
c1.data,
c1.style_id
from
chart_cell c1
union all
select
ro.chart_data_id,
ro.id as chart_row_id,
co.id as chart_column_id,
null as data,
to_number(null)
from
chart_row ro,
chart_column co,
m_chart chart
where
chart.id = :basic_chart_id
AND ro.chart_data_id = chart.chart_data_id
AND co.chart_data_id = chart.chart_data_id
and NOT EXISTS
(
select
null
from
chart_cell ce
where
ce.chart_data_id = ro.chart_data_id
and ce.chart_row_id = ro.id
and ce.chart_column_id = co.id
)
) cl
WHERE
c.id = :basic_chart_id
and ch_rows.chart_data_id = c.chart_data_id
and ch_cols.chart_data_id = c.chart_data_id
and cl.chart_data_id = c.chart_data_id
and cl.chart_row_id = ch_rows.row_id
and cl.chart_column_id = ch_cols.column_id
AND c.id IN
(
SELECT
rvd.object_id
FROM
REPORT_VALID_SET rvs,
REPORT_VALID_DATA rvd
WHERE
rvs.id = rvd.report_valid_set_id
AND rvd.sub_domain_id = 19
AND rvd.report_valid_set_id = :P_Report_Valid_Set_ID
AND rvs.session_id = :P_Session_ID
)
order by
c.id,
ch_rows.row_number,
ch_cols.column_number;
Table Links
Joins: where [G_measure].[measurement_id] = [Q_GRAPH_DATA_TABULAR_VIEW].[raw_data_measure_id]
where [G_measure].[measurement_id] = [Q_GRAPH_OF_MASTER_TARGET].[MEASURE_ID]
where [G_MEASURE_ID].[graphing_id2] = [Q_GRAPHING_VALUES].[GRAPHING_ID]
where [G_measure].[measurement_id] = [Q_LEGENDS].[legend_measure_id]
where [G_entity].[parent_id] = [Q_FILE].[file_id]
where [G_FILE].[file_id] = [Q_FILE_UNITS].[unit_file_id]
where [G_FILE] = [Q_MNGMNT_CATEGORIES]
where [G_FILE].[file_id] = [Q_FILE_STRATEGY].[strategy_file_id]
where [G_entity].[parent_id] = [Q_SUBMISSION].[submission_id]
where [G_SUBMISSION].[submission_id] = [Q_files_to_sub_item].[sub_submission_id]
where [G_SUBMISSION].[submission_id] = [Q_strategies_to_sub_item].[strat_to_sub_sub_id]
where [G_entity].[parent_id] = [Q_STRATEGY].[strategy_id]
where [G_measure].[measurement_id] = [Q_RELATED_MEASUREMENTS].[m_related_id]
where [G_measure].[measurement_id] = [Q_REL_ACTIVITY_NOTES].[activity_measurement_id]
where [G_entity].[parent_id] = [Q_MEASUREMENT].[object_id]
where [G_DRIVER].[DRIVER_ID] = [Q_PARENT_ENTITY].[parent_id]
where [G_DRIVER].[DRIVER_DOMAIN] = [Q_PARENT_ENTITY].[domain_id]
where [G_STRATEGY].[strategy_id] = [Q_strategy_units].[unit_strategy_id]
where [G_SUBMISSION].[submission_id] = [Q_Submission_Lead_Units].[unit_lead_submission_id]
where [G_SUBMISSION] = [Q_Submission_Affected_Units]
where [G_entity].[parent_id] = [Q_CHART_ATTRIBUTES].[chart_parent_id]
where [G_entity].[domain_id] = [Q_CHART_ATTRIBUTES].[sub_domain_id]
where [G_ATTRIBUTE_DATA].[basic_chart_id] = [Q_CHART].[CHART_ID1]
Placeholder Columns
Column: CP_Ministry_ID
Data Type: number
Label: Cp Ministry Id
Column: CP_Measure_ID
Data Type: number
Label: Cp Measure Id
Column: CP_Version
Data Type: character
Column: CP_data_series
Data Type: character
Column: CP_SERIES_COUNT
Data Type: number
Column: CP_REPORT_TITLE
Data Type: character
Column: CP_PARENT_ENTITY_ID
Data Type: number
Column: CP_DOMAIN_ID
Data Type: number
Column: CP_ACTUALS_COLOR
Data Type: character
Column: CP_MASTER_TARGET_COLOR
Data Type: character
Column: MEASURE_SUMMARY_NEW
Data Type: character
Column: CP_Q_PARENT_FILES_FILTER
Data Type: character
Column: CP_Q_PARENT_SUBMISSIONS_FILTER
Data Type: character
Column: CP_Q_PARENT_STRATEGIES_FILTER
Data Type: character
Column: CP_Q_MEASUREMENT_MEASUR_FILTER
Data Type: character
Column: CP_ORG_UNITS_SELECTED
Data Type: number
Column: CP_Is_Chart_Visible
Data Type: number
Column: CP_Activity_Notes_Selected
Data Type: number
Column: CP_MC_Level_Id
Data Type: number
Column: CP_Prompt_Runtime_Value_Set_ID
Data Type: number
Column: P_PROMPT_VALUE_SET_ID
Data Type: number
Column: CP_ORG_UNITS_SELECTED_SR
Data Type: number
Summary Columns
Column: CS_PARENT_COUNT
Source: parent_id
Function: count
Column: CS_MEASURE_COUNT2
Source: object_id
Function: count
Column: CS_GRAPHING_ID
Source: GRAPHING_ID
Function: count
Column: CS_1
Source: legend_measure_id
Function: sum
Column: CS_Chart_Count2
Source: basic_chart_id
Function: count
Column: CS_Driver_Count
Source: DRIVER_ID
Function: count
Formula Fields
Field: beforereport
Type: Function Body
Formula: function BeforeReport return boolean is
parent_entity_type varchar2(200);
parent_entity_ID numeric(10);
domain_ID integer ;
begin
--SRW.MESSAGE(2008, 'Before trigger ');
:cp_ministry_id := reports_pkg.gettoplevelancestorunitid(:p_min_id);
:cp_measure_id := :p_object_id;
-- ************************************************
-- the following block handles report title, and measurement's parent entity ID, parent domain id when ...
-- ************************************************
:CP_REPORT_TITLE := '? ? ?';
-- JK, Apr 15, 2009. Read titles from the table.
--select title
--into :CP_REPORT_TITLE
--from report
-- translation from report type (input param ) to Report.Id
--where id = decode(:P_Report_Type_ID, 1,50, 2,37, 3,40, 4,30, 5,41, 6,39, 7,38, 0);
--:CP_REPORT_TITLE := ' ' || :CP_REPORT_TITLE;
if (:P_Report_Type_ID = 2) then
:CP_REPORT_TITLE := ' Performance Measure Summary';
end if;
if (:P_Report_Type_ID = 3) then
:CP_REPORT_TITLE := ' Metrics by Organization Unit';
end if;
if (:P_Report_Type_ID = 4) then
:CP_REPORT_TITLE := ' File Measure Summary';
:CP_PARENT_ENTITY_ID := :p_object_id;
:CP_DOMAIN_ID := 4;
end if;
if (:P_Report_Type_ID = 5) then
:CP_REPORT_TITLE := ' Metrics by Management Category';
end if;
if (:P_Report_Type_ID = 7) then
:CP_REPORT_TITLE := ' Measures By Activity Notes';
end if;
if (:P_Report_Type_ID = 6) then
:CP_REPORT_TITLE := ' Metrics By Business Domain';
end if;
if (:P_Report_Type_ID = 1) then
:CP_REPORT_TITLE := ' Graph Summary';
end if;
INIT_GRAPH_VALUES.init();
RETURN (TRUE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
SRW.MESSAGE(2008, 'Data integrity error. Some data was not found !');
return (TRUE);
end;
Field: b_devstatus_cur1formattrigger
Type: Function Body
Formula: function B_DevStatus_Cur1FormatTrigger return boolean is
begin
if upper(:rs_status) = 'ON TRACK' then -- 'GREEN' then -- {{ONTrack}}
srw.set_foreground_fill_color('r50g88b50');
srw.set_text_color('r50g88b50');
srw.set_fill_pattern('solid');
elsif upper(:rs_status) = 'CONCERNS' then -- 'YELLOW' then --{{Concerns}}
srw.set_foreground_fill_color('r100g100b0');
srw.set_text_color('r100g100b0');
srw.set_fill_pattern('solid');
elsif upper(:rs_status) = 'ATTENTION' then --'RED' then --{{Immediate Attention}}
srw.set_foreground_fill_color('r100g0b0');
srw.set_text_color('r100g0b0');
srw.set_fill_pattern('solid');
elsif upper(:rs_status) = 'COMPLETED' then --'BLACK' then --{{Complete}}
srw.set_foreground_fill_color('black');
srw.set_fill_pattern('solid');
elsif upper(:rs_status) = 'CANCELLED' then --'GRAY' then --{{Cancelled}}
srw.set_foreground_fill_color('gray24');
srw.set_fill_pattern('solid');
elsif upper(:rs_status) = 'DEFERRED' then --'WHITE' then
srw.set_foreground_fill_color('white');
srw.set_fill_pattern('solid');
else
return (FALSE);
end if;
return (TRUE);
end;
Field: init_graph_values
Type: packageBody
Formula: PACKAGE BODY INIT_GRAPH_VALUES IS
PROCEDURE init IS
DBG VARCHAR2(200);
BEGIN
R_MEASURESUMMARY.INITREPORT(
P_REPORT_TYPE_ID => :P_REPORT_TYPE_ID,
P_OBJECT_ID => :P_OBJECT_ID,
P_REPORT_VALID_SET_ID => :P_REPORT_VALID_SET_ID,
SP_PROMPT_VALUE_SET_ID => :SP_PROMPT_VALUE_SET_ID,
P_STATUS_REQUEST_ID => :P_STATUS_REQUEST_ID,
P_PROMPT_VALUE_SET_ID => :P_PROMPT_VALUE_SET_ID,
CP_Q_PARENT_FILES_FILTER => :CP_Q_PARENT_FILES_FILTER,
CP_Q_PARENT_SUBMISSIONS_FILTER => :CP_Q_PARENT_SUBMISSIONS_FILTER,
CP_Q_PARENT_STRATEGIES_FILTER => :CP_Q_PARENT_STRATEGIES_FILTER,
CP_IS_CHART_VISIBLE => :CP_IS_CHART_VISIBLE,
CP_ACTIVITY_NOTES_SELECTED => :CP_ACTIVITY_NOTES_SELECTED,
CP_MC_LEVEL_ID => :CP_MC_LEVEL_ID,
CP_ORG_UNITS_SELECTED => :CP_ORG_UNITS_SELECTED,
CP_PROMPT_RUNTIME_VALUE_SET_ID => :CP_PROMPT_RUNTIME_VALUE_SET_ID,
CP_ORG_UNITS_SELECTED_SR => :CP_ORG_UNITS_SELECTED_SR,
DBG => DBG
);
end init;
END INIT_GRAPH_VALUES;
Field: init_graph_values
Type: packageSpec
Formula: PACKAGE INIT_GRAPH_VALUES IS
procedure init;
END;
Field: m_graph_data_tabular_viewforma
Type: Function Body
Formula: function M_GRAPH_DATA_TABULAR_VIEWForma return boolean is
begin
return (TRUE);
end;
Field: b_legend_colour_boxformattrigg
Type: Function Body
Formula: function B_LEGEND_COLOUR_BOXFormatTrigg return boolean is
s_color varchar2(12) := 'r25g50b100';
begin
-- this is the order - make sure it's on sync with graph definiton (XML )
--red #ff0000
--blue #0000ff
--black #000000
--green #00ff00
--yellow #ffff00
--magenta #ff00ff
--cyan #00ffff
--gray #808080
--darkred #800000
--darkblue #000080
--darkgreen
--darkyellow
--darkmagenta
--darkcyan
--darkgray
-- used to be red & blue
if (:measurements_set_name = 'Actuals') then
s_color := 'r100g0b0';
end if;
if (:measurements_set_name = 'Targets High Values') then
s_color := 'r50g75b100';
end if;
srw.set_foreground_fill_color(s_color);
srw.set_fill_pattern('solid');
return (TRUE);
end;
Field: r_imageformattrigger
Type: Function Body
Formula: function R_IMAGEFormatTrigger return boolean is
begin
if (:CS_TYPE_OF_GRAPH_IMAGE = 'System Generated')
then
return (FALSE);
end if;
return (TRUE);
end;
Field: f_graph_or_image_valueformattr
Type: Function Body
Formula: function F_GRAPH_OR_IMAGE_VALUEFormatTr return boolean is
begin
-- JK - HIDE IT ALWAYS.
return (false);
end;
Field: m_fileformattrigger
Type: Function Body
Formula: function M_FILEFormatTrigger return boolean is
begin
-- Automatically Generated from Report Builder.
if ((:domain_id != '4') or
(:CS_Measure_Counter + :CS_Chart_Counter = 0) or (:P_Report_Type_ID = 1))
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_strategyformattrigger
Type: Function Body
Formula: function M_STRATEGYFormatTrigger return boolean is
begin
if ( (:domain_id != '3') or (:P_Report_Type_ID = 1) or (:CS_Measure_Counter + :CS_Chart_Counter = 0) )
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_submissionformattrigger
Type: Function Body
Formula: function M_SUBMISSIONFormatTrigger return boolean is
begin
if ((:domain_id != '1') or (:P_Report_Type_ID = 1) or (:CS_Measure_Counter + :CS_Chart_Counter = 0) )
then
return (FALSE);
end if;
return (TRUE);
end;
Field: cf_graph_title_textformula
Type: Function Body
Return Type: character
Formula: function CF_GRAPH_TITLE_TEXTFormula return Char is
entity_type varchar(20);
business_num varchar(50);
begin
select title into entity_type from easy_domain where id = :domain;
if :domain = 3 then
select business_number into business_num from BA_STRATEGY where id = :object_id;
end if;
if :domain = 1 then
select business_number into business_num from SUBMISSION where id = :object_id;
end if ;
if :domain = 4 then
select business_number into business_num from BA_FILE where id = :object_id;
end if ;
return entity_type || ' (' || business_num || ')';
end;
Field: cf_graph_subtitle_textformula
Type: Function Body
Return Type: character
Formula: function CF_GRAPH_SUBTITLE_TEXTFormula return Char is
begin
return SUBSTR( ('Measure (' || :meas_title || ')'), 1, 50 );
end;
Field: cf_data_valuesformula
Type: Function Body
Return Type: character
Formula: function CF_DATA_VALUESFormula return Char is
--i_series_cnt integer := 2;
DATA_VALUES VARCHAR2(200) := 'ACTUAL_VALUE1,TARGET_VALUE1';
s_range_or_single varchar2(50) := 'Singular' ;
begin
-- 26-JAN-2009 S T A R T ********************************************************************************************************
-- the commented out query below (select count ...) does not make much sense anymore. As of Rel 3.0 there will be only one graphing data per measure.
-- ( don't know what will happen when two users concurrently execute plotting a graph for the same measure )
-- Anyway, today I will change the value returned from this function. It will be either 'ACTUAL_VALUE1,TARGET_VALUE1'
-- or 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_HIGH_VALUE' for target portfolio 'definition' equals to 'Singular' or 'Range' respectively.
select vl.title
into s_range_or_single
from value_list vl, m_target_portfolio tp, m_graphing g
where vl.type = 'MEASURE_TARGET_DEFINITION'
and tp.definition_id = vl.id
and g.measure_id = tp.measure_id
and tp.measure_id = :MEASUREMENT_ID
and g.target_portfolio_id = tp.id;
if s_range_or_single = 'Range' then
DATA_VALUES := DATA_VALUES || ',TARGET_HIGH_VALUE';
end if;
-- 26-JAN-2009 E N D ************************************************************************************************************
/*
-- value of this variable will be used in graph legend
-- so, first of all, determine the number of the targets, and then heir names
-- do handle the situation when there is NO ACTUAL value, only TARGETS
-- do note, that column names return have to be 'real', found in SQL query
select count(distinct graphing_id) into i_series_cnt
from M_GRAPHING_VALUE gv2
, m_graphing g2
where gv2.GRAPHING_ID = g2.id
and g2.measure_id = :MEASUREMENT_ID
;
-- now the ugly & boring part:
IF i_series_cnt = 1 THEN
DATA_VALUES := 'ACTUAL_VALUE1';
END IF;
IF i_series_cnt = 1 THEN
DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1';
END IF;
--SRW.MESSAGE(999, 'CP_DATA_VALUES = ' || :CP_DATA_VALUES);
IF i_series_cnt = 2 THEN
DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2';
END IF;
IF i_series_cnt = 3 THEN
DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3';
END IF;
IF i_series_cnt = 4 THEN
DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4';
END IF;
IF i_series_cnt = 5 THEN
DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5';
END IF;
IF i_series_cnt = 6 THEN
DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5,TARGET_VALUE6';
END IF;
IF i_series_cnt = 7 THEN
DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5,TARGET_VALUE6,TARGET_VALUE7';
END IF;
IF i_series_cnt = 8 THEN
DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5,TARGET_VALUE6,TARGET_VALUE7,TARGET_VALUE8';
END IF;
-- and for more then 1- targets we'll show only 10
IF i_series_cnt >= 9 THEN
DATA_VALUES := 'ACTUAL_VALUE1,TARGET_VALUE1,TARGET_VALUE2,TARGET_VALUE3,TARGET_VALUE4,TARGET_VALUE5,TARGET_VALUE6,TARGET_VALUE7,TARGET_VALUE8,TARGET_VALUE9';
END IF;
*/
RETURN DATA_VALUES;
end;
Field: cf_ministry_nameformula
Type: Function Body
Return Type: character
Formula: function CF_MINISTRY_NAMEFormula return Char is
l_name varchar2(100);
l_acr varchar2(100);
begin
select reports_pkg.GETUNITLASTNAME(ID) --, acronym
into l_name --, l_acr
from unit
where id = :P_MIN_ID;
--return l_name || ' (' || l_acr || ')' ;
return l_name;
end;
Field: b_13formattrigger
Type: Function Body
Formula: function B_13FormatTrigger return boolean is
begin
return (TRUE);
end;
Field: r_statement_of_resultformattri
Type: Function Body
Formula: function R_STATEMENT_OF_RESULTFormatTri return boolean is
begin
-- hide always; statement of results deprecated
return (FALSE);
end;
Field: m_commitmentsformattrigger
Type: Function Body
Formula: function M_CommitmentsFormatTrigger return boolean is
begin
-- Commitments not implemented yet
return (FALSE);
end;
Field: m_relatedmeasuresformattrigger
Type: Function Body
Formula: function M_RelatedMeasuresFormatTrigger return boolean is
begin
-- Automatically Generated from Report Builder.
if ((:CS_CNT_RELATED_MEASURES = '0') or (:P_Report_Type_ID = 1) )
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_activitynotesformattrigger
Type: Function Body
Formula: function M_ActivityNotesFormatTrigger return boolean is
begin
if ((:CS_CNT_ACTIVITY_NOTES = '0') or (:P_Report_Type_ID = 1) )
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_file_lead3formattrigger
Type: Function Body
Formula: function M_File_Lead3FormatTrigger return boolean is
begin
-- Automatically Generated from Report Builder.
if (:CS_CNT_AFFECTED_SUBMISSIONS = '0')
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_strategicobjectivesformattri
Type: Function Body
Formula: function M_StrategicObjectivesFormatTri return boolean is
begin
-- Automatically Generated from Report Builder.
if (:CS_CNT_STRATEGY_OBJECTIVES = '0')
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_measure_data_frameformattrig
Type: Function Body
Formula: function M_MEASURE_DATA_FRAMEFormatTrig return boolean is
begin
-- Automatically Generated from Report Builder.
if (:CS_CNT_GRAPH_TBL_ROWS = '0')
then
return (FALSE);
end if;
return (TRUE);
end;
Field: b_21formattrigger
Type: Function Body
Formula: function B_21FormatTrigger return boolean is
begin
-- Automatically Generated from Report Builder.
if (:CS_CNT_GRAPH_TBL_ROWS > '0')
then
return (FALSE);
end if;
return (TRUE);
end;
Field: ct_4formattrigger
Type: Function Body
Formula: function CT_4FormatTrigger return boolean is
begin
-- If drawing user-supplied graph image, or if there is
-- no graphing data, hide the graph object.
if ((:CS_TYPE_OF_GRAPH_IMAGE != 'System Generated')
or
(:CS_CNT_GRAPH_TBL_ROWS = 0))
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_legend_title_frameformattrig
Type: Function Body
Formula: function M_LEGEND_TITLE_FRAMEFormatTrig return boolean is
begin
-- Don't show legend if no data exists, or user-supplied
-- graph image is selected.
if ((:CS_CNT_GRAPH_TBL_ROWS = 0) or
(:CS_TYPE_OF_GRAPH_IMAGE != 'System Generated'))
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_driver_frameformattrigger
Type: Function Body
Formula: function M_DRIVER_FRAMEFormatTrigger return boolean is
begin
-- Hide 'driver' heading bar for Measure Summary, Graph Summary File Metrics Summary (2,1,4)
if ( (:P_Report_Type_ID = '2') or (:P_Report_Type_ID = '4') or (:P_Report_Type_ID = '1') ) then
return (FALSE);
else
-- as well as when there are no records inside it (i.e. # of Parent ID records is 0)
if ((:CS_CNT_MEASURES = 0 or :CS_CNT_MEASURES is null)
and
(:CS_CNT_CHARTS = 0 or :CS_CNT_CHARTS is null))
then
return (false);
end if;
end if;
return (TRUE);
end;
Field: m_spacer_graph_heightformattri
Type: Function Body
Formula: function M_SPACER_GRAPH_HEIGHTFormatTri return boolean is
begin
-- If drawing user-supplied graph image, or if there is
-- no graphing data, hiding the graph object, so don't need
-- the spacer.
if ((:CS_TYPE_OF_GRAPH_IMAGE != 'System Generated')
or
(:CS_CNT_GRAPH_TBL_ROWS = 0))
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_chartformattrigger
Type: Function Body
Formula: function M_CHARTFormatTrigger return boolean is
begin
-- first and formost, read the value derived by reading the report prompt and decide to show / display charts
if :CP_Is_Chart_Visible = 0 then
return (false);
end if;
-- Automatically Generated from Report Builder.
if ((:CS_Chart_Counter < 1) or (:CS_Chart_Counter IS NULL) or (:P_Report_Type_ID = 2) or (:P_Report_Type_ID = 1) or (:P_Report_Type_ID = 7))
then
return (FALSE);
end if;
return (TRUE);
end;
Field: b_chart_rep_status_symbolforma
Type: Function Body
Formula: function B_chart_rep_status_symbolForma return boolean is
begin
if upper(:chart_reporting_status) = 'ON TRACK' then -- 'GREEN' then -- {{ONTrack}}
srw.set_foreground_fill_color('r50g88b50');
srw.set_text_color('r50g88b50');
srw.set_fill_pattern('solid');
elsif upper(:chart_reporting_status) = 'CONCERNS' then -- 'YELLOW' then --{{Concerns}}
srw.set_foreground_fill_color('r100g100b0');
srw.set_text_color('r100g100b0');
srw.set_fill_pattern('solid');
elsif upper(:chart_reporting_status) = 'ATTENTION' then --'RED' then --{{Immediate Attention}}
srw.set_foreground_fill_color('r100g0b0');
srw.set_text_color('r100g0b0');
srw.set_fill_pattern('solid');
elsif upper(:chart_reporting_status) = 'COMPLETED' then --'BLACK' then --{{Complete}}
srw.set_foreground_fill_color('black');
srw.set_fill_pattern('solid');
elsif upper(:chart_reporting_status) = 'CANCELLED' then --'GRAY' then --{{Cancelled}}
srw.set_foreground_fill_color('gray24');
srw.set_fill_pattern('solid');
elsif upper(:chart_reporting_status) = 'DEFERRED' then --'WHITE'
srw.set_foreground_fill_color('white');
srw.set_fill_pattern('solid');
end if;
return (TRUE);
end;
Field: f_data1formattrigger
Type: Function Body
Formula: function F_data1FormatTrigger return boolean is
style_num NUMBER(10) := 0;
l_BG_COLOR VARCHAR2(50) := '' ;
l_TEXT_COLOR VARCHAR2(50) := '' ;
l_BOLD NUMBER(1) := 0;
l_ITALIC NUMBER(1) := 0;
l_UNDERLINE NUMBER(1) := 0;
l_ALIGNMENT VARCHAR2(50) := '';
BEGIN
-- order in precedence (form least signficant to most significant) is as following:
-- chart -> column -> row -> cell
-- meaning, id cell has a style id defined, use cell style info
style_num := 0;
IF :cell_style is not null AND :cell_style > 0 THEN
style_num := :cell_style;
END IF;
-- if we have style defined, retrieve the attributes from M_STYLE table
IF style_num is not null AND style_num > 0 THEN
--SRW.MESSAGE(999, 'changing style ' || to_char(style_num) );
-- DEBUG
--SRW.MESSAGE(999, 'style num = ' || style_num);
--SRW.MESSAGE(999, 'a' || :chart_style || 'b' || :column_style || 'c' || :row_style || 'd' || :cell_style || 'e');
--SRW.MESSAGE(999, 'row = ' || :row_number || ' column = ' || :column_number );
SELECT
--NVL(map1.REPORT_COLOR_CODE, 'r100g100b100') as BG_COLOR,
--NVL(map2.REPORT_COLOR_CODE, 'r100g100b100') as TEXT_COLOR,
map1.REPORT_COLOR_CODE as BG_COLOR,
map2.REPORT_COLOR_CODE as TEXT_COLOR,
BOLD ,
ITALIC ,
UNDERLINE ,
ALIGNMENT
INTO
l_BG_COLOR,
l_TEXT_COLOR,
l_BOLD ,
l_ITALIC ,
l_UNDERLINE ,
l_ALIGNMENT
FROM CHART_STYLE
, M_COLORS_MAP map1
, M_COLORS_MAP map2
WHERE id = style_num
and lower(trim(map1.RGB_COLOR_CODE(+))) = lower(trim(BG_COLOR))
and lower(trim(map2.RGB_COLOR_CODE(+))) = lower(trim(TEXT_COLOR)) ;
--SRW.MESSAGE(999, 'values are ' || l_BG_COLOR || l_TEXT_COLOR || l_BOLD || l_ITALIC || l_UNDERLINE || l_ALIGNMENT );
-- take the style retrieved and do SRW.SET calls
IF l_BG_COLOR is not null and LENGTH(l_BG_COLOR) >0 THEN
--SRW.SET_BACKGROUND_FILL_COLOR(l_BG_COLOR);
SRW.SET_FOREGROUND_FILL_COLOR(l_BG_COLOR);
srw.set_fill_pattern('solid');
--SRW.MESSAGE(999, 'row ' || to_char(:row_number) || ' column number ' || to_char(:column_number) || ', changing BG color to ' || l_BG_COLOR );
END IF;
IF l_TEXT_COLOR is not null AND LENGTH(l_TEXT_COLOR) > 0 THEN
SRW.SET_TEXT_COLOR(l_TEXT_COLOR);
--SRW.MESSAGE(999, 'row ' || to_char(:row_number) || ' column number ' || to_char(:column_number) || ' changing color to ' || l_TEXT_COLOR );
END IF;
IF l_BOLD is not null AND l_BOLD = 1 THEN
SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT);
END IF;
IF l_ITALIC is not null AND l_ITALIC = 1 THEN
SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE);
END IF;
IF l_UNDERLINE is not null AND l_UNDERLINE = 1 THEN
SRW.SET_FONT_STYLE(SRW.UNDERLINE_STYLE);
END IF;
IF l_ALIGNMENT is not null AND LENGTH(l_ALIGNMENT) > 0 THEN
SRW.SET_JUSTIFICATION( SRW.CENTER_HJUST );
END IF;
END IF;
-- no style is defined, exit from here and leave everything at its defaults
return (TRUE);
END;
Field: b_divider1formattrigger
Type: Function Body
Formula: function B_Divider1FormatTrigger return boolean is
begin
-- Automatically Generated from Report Builder.
if (:P_Report_Type_ID = '1')
then
return (FALSE);
end if;
return (TRUE);
end;
Field: b_file_rep_status_symbolformat
Type: Function Body
Formula: function B_file_rep_status_symbolFormat return boolean is
begin
if upper(:file_reporting_status) = 'ON TRACK' then -- 'GREEN' then -- {{ONTrack}}
srw.set_foreground_fill_color('r50g88b50');
srw.set_text_color('r50g88b50');
srw.set_fill_pattern('solid');
elsif upper(:file_reporting_status) = 'CONCERNS' then -- 'YELLOW' then --{{Concerns}}
srw.set_foreground_fill_color('r100g100b0');
srw.set_text_color('r100g100b0');
srw.set_fill_pattern('solid');
elsif upper(:file_reporting_status) = 'ATTENTION' then --'RED' then --{{Immediate Attention}}
srw.set_foreground_fill_color('r100g0b0');
srw.set_text_color('r100g0b0');
srw.set_fill_pattern('solid');
elsif upper(:file_reporting_status) = 'COMPLETED' then --'BLACK' then --{{Complete}}
srw.set_foreground_fill_color('black');
srw.set_fill_pattern('solid');
elsif upper(:file_reporting_status) = 'CANCELLED' then --'GRAY' then --{{Cancelled}}
srw.set_foreground_fill_color('gray24');
srw.set_fill_pattern('solid');
elsif upper(:file_reporting_status) = 'DEFERRED' then --'WHITE' then
srw.set_foreground_fill_color('white');
srw.set_fill_pattern('solid');
end if;
return (TRUE);
end;
Field: b_sub_rep_status_symbol1format
Type: Function Body
Formula: function B_sub_rep_status_symbol1Format return boolean is
begin
if upper(:str_reporting_status) = 'ON TRACK' then -- 'GREEN' then -- {{ONTrack}}
srw.set_foreground_fill_color('r50g88b50');
srw.set_text_color('r50g88b50');
srw.set_fill_pattern('solid');
elsif upper(:str_reporting_status) = 'CONCERNS' then -- 'YELLOW' then --{{Concerns}}
srw.set_foreground_fill_color('r100g100b0');
srw.set_text_color('r100g100b0');
srw.set_fill_pattern('solid');
elsif upper(:str_reporting_status) = 'ATTENTION' then --'RED' then --{{Immediate Attention}}
srw.set_foreground_fill_color('r100g0b0');
srw.set_text_color('r100g0b0');
srw.set_fill_pattern('solid');
elsif upper(:str_reporting_status) = 'COMPLETED' then --'BLACK' then --{{Complete}}
srw.set_foreground_fill_color('black');
srw.set_fill_pattern('solid');
elsif upper(:str_reporting_status) = 'CANCELLED' then --'GRAY' then --{{Cancelled}}
srw.set_foreground_fill_color('gray24');
srw.set_fill_pattern('solid');
elsif upper(:str_reporting_status) = 'DEFERRED' then --'WHITE'
srw.set_foreground_fill_color('white');
srw.set_fill_pattern('solid');
end if;
return (TRUE);
end;
Field: f_drivers_countformattrigger
Type: Function Body
Formula: function F_drivers_countFormatTrigger return boolean is
begin
return (FALSE);
end;
Field: f_measure_counterformattrigger
Type: Function Body
Formula: function F_measure_counterFormatTrigger return boolean is
begin
-- Automatically Generated from Report Builder.
if (:P_Report_Type_ID != '5')
then
return (false);
end if;
return (false);
end;
Field: cf_q_measurement_measur_filter
Type: Function Body
Return Type: character
Formula: function CF_Q_MEASUREMENT_MEASUR_FILTER return Char is
l_str varchar(2000) := ' and 1 = 1 ';
begin
-- set the default SQL and change only if needed.
l_str := ' and m.id in (select object_id from v_report_valid_data where sub_domain_id = 2 and report_valid_set_id = ' || :P_Report_Valid_Set_ID || ') ' ;
if :P_Report_Type_ID = 2 or :P_Report_Type_ID = 1 then
l_str := ' and m.id = ' || to_char(:P_OBJECT_ID) || ' and m.id in (select object_id from v_report_valid_data where sub_domain_id = 2 and report_valid_set_id = ' || :P_Report_Valid_Set_ID || ') ';
end if;
if :P_Report_Type_ID = 7 then
-- this is an addition to further filter out mesures to be listed, and it will change further in near future when user gets the ability to
-- select activity notes
l_str := l_str || ' and m.id in (select distinct man.metric_id from m_metric_activity_note man ) ' ;
end if;
return l_str;
end;
Field: f_starting_pointformattrigger
Type: Function Body
Formula: function F_starting_pointFormatTrigger return boolean is
begin
return (false);
end;
Field: b_no_data_msgformattrigger
Type: Function Body
Formula: function B_no_data_msgFormatTrigger return boolean is
begin
-- Automatically Generated from Report Builder.
if (:CS_MEASURE_COUNT2 + :CS_Chart_Count2 = 0)
then
return (TRUE);
end if;
return (FALSE);
end;
Field: f_measure_counter1formattrigge
Type: Function Body
Formula: function F_measure_counter1FormatTrigge return boolean is
begin
-- Automatically Generated from Report Builder.
if (:P_Report_Type_ID != '5')
then
return (false);
end if;
return (false);
end;
Field: cf_actuals_handling_labelformu
Type: Function Body
Return Type: character
Formula: function CF_Actuals_Handling_LabelFormu return Char is
str_value varchar2(30) := '???';
begin
-- JK Apr 13 OBSOLETE
--if :Actuals_Handling_id = 1 then
-- str_value := 'Actual Total';
-- else
--str_value := 'Actual';
--end if;
return str_value;
end;
Field: cf_first_actuals_columnformula
Type: Function Body
Return Type: number
Formula: function CF_First_Actuals_ColumnFormula return Number is
nbr_value number(38,2);
begin
-- Actuals_Handling_id | Title
------------------------------------------------------------------
-- 1 Cumulative (adds to running total)
-- 2 Total to Date (replaces the total)
if :Actuals_Handling_id = 1 then
nbr_value := :gr_actual_value;
else
nbr_value := :gr_total_to_date;
end if;
return nbr_value;
end;
Field: f_total_to_date_valueformattri
Type: Function Body
Formula: function F_TOTAL_TO_DATE_VALUEFormatTri return boolean is
begin
if (:Actuals_Handling_Id = 2)
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_vertical_spacer_2formattrigg
Type: Function Body
Formula: function M_Vertical_Spacer_2FormatTrigg return boolean is
begin
if (:Actuals_Handling_Id = 2)
then
return (FALSE);
end if;
return (TRUE);
end;
Field: b_actual_totalformattrigger
Type: Function Body
Formula: function B_Actual_TotalFormatTrigger return boolean is
begin
if (:Actuals_Handling_Id = 2)
then
return (FALSE);
end if;
return (TRUE);
end;
Field: f_percent_or_dollar_symbolform
Type: Function Body
Formula: function F_percent_or_dollar_symbolForm return boolean is
begin
-- Kevin Davis Oct. 15, 2009. Hiding '%' or '$' when no graphing data is present.
-- Automatically Generated from Report Builder.
if (:CS_MIN_users_image = 'User Provided') or (:CS_CNT_GRAPH_TBL_ROWS < 1)
then
return (FALSE);
end if;
return (TRUE);
end;
Field: m_owning_file_detailsformattri
Type: Function Body
Formula: function M_Owning_File_DetailsFormatTri return boolean is
begin
if :P_Report_Type_ID = 5 then
return (FALSE);
end if;
return (TRUE);
end;
Field: f_gr_actual_valueformattrigger
Type: Function Body
Formula: function F_GR_Actual_ValueFormatTrigger return boolean is
begin
return (TRUE);
end;
Groups
Group: main Section
Body
Section: Frame M_MAIN_FRAME
Text Fields
Text Field: B_no_data_msg
Text: No data to display
Section: Frame M_DRIVER_FRAME
Fields
Field: F_drivers_count
Data Source: CS_CNT_MEASURES
Section: Frame M_PROTECT_DRIVER_TYPE_AND_NAME
Fields
Field: F_DRIVER_TYPE
Data Source: DRIVER_TYPE
Field: F_DRIVER_NAME
Data Source: DRIVER_NAME
Section: Frame M_FILE_NAME_FRAME
Fields
Field: F_NAME_AND_NUMBER
Data Source: file_name
Field: F_File_Label
Data Source: parent_file_label
Section: Frame M_Owning_File_Details
Fields
Field: F_file_reporting_status
Data Source: file_reporting_status
Field: F_file_record_status
Data Source: file_status
Text Fields
Text Field: B_FILE_UNITS
Text: Lead:
Text Field: B_FILE_MCs
Text: Management Categories:
Text Field: B_File_Reporting_Status
Text: Reporting Status:
Text Field: B_File_Record_Status
Text: Record Status:
Section: Frame M_frame_around_stategy
Text Fields
Text Field: B_FILE_STARTEGY
Text: Strategies:
Section: Frame M_SUBMISSION_NAME_FRAME
Fields
Field: F_submission_name
Data Source: submission_name
Field: F_Submission_Label
Data Source: parent_submission_label
Section: Frame M_File_Lead2
Text Fields
Text Field: B_FILE_UNITS2
Text: Lead:
Section: Frame M_File_Lead3
Text Fields
Text Field: B_FILE_UNITS3
Text: Affected:
Section: Frame M_startegies_to_sub_item
Text Fields
Text Field: B_submission_strategy
Text: Strategy:
Section: Frame M_strategy_title
Fields
Field: F_strategy_name
Data Source: strategy_name
Field: F_Strategy_Label
Data Source: parent_strategy_label
Section: Frame M_File_Lead1
Text Fields
Text Field: B_FILE_UNITS1
Text: Lead:
Section: Frame M_StrategicObjectives
Text Fields
Text Field: B_13
Text: Strategic Objective:
Section: Frame M_MeasureDetail
Fields
Field: F_GRAPH_OR_IMAGE_VALUE
Data Source: CS_TYPE_OF_GRAPH_IMAGE
Field: F_starting_point
Data Source: starting_point
Section: Frame M_ReportingTo
Fields
Field: F_reporting_to
Data Source: reporting_to_name
Text Fields
Text Field: B_LABEL_REPORTING_TO
Text: Reporting To:
Section: Frame M_ReportingStatus
Fields
Field: F_measure_status
Data Source: rs_status
Text Fields
Text Field: B_LABEL_REPORTING_STATUS
Text: Reporting Status:
Section: Frame M_StatusComments
Fields
Field: F_status_note
Data Source: status_note
Text Fields
Text Field: B_LABEL_STATUS_NOTE
Text: Status Comments:
Section: Frame M_MEASURE_NAME_FRAME
Fields
Field: F_meas_title
Data Source: meas_title
Text Fields
Text Field: B_1
Text: Measure Title:
Section: Frame M_LOM_Keeptogether1
Fields
Field: F_MeasureNumber
Data Source: measure_number
Text Fields
Text Field: B_20
Text: Number:
Section: Frame M_LOM_Keeptogether
Fields
Field: F_Level_of_Measure
Data Source: lvl_of_measure
Text Fields
Text Field: B_12
Text: Level of Measure:
Section: Frame M_measure_class_keeptogether
Fields
Field: F_Measure_Class_Prompt
Data Source: mclass_prompt
Field: F_Measure_Class
Data Source: measure_class
Section: Frame M_measure_type_keeptogether
Fields
Field: F_measure_type
Data Source: measure_type
Text Fields
Text Field: B_4
Text: Measure Type:
Section: Frame M_RecordingFrequency
Fields
Field: F_rep_freq
Data Source: rep_freq
Text Fields
Text Field: B_5
Text: Recording Frequency:
Section: Frame M_UnitofMeasure
Fields
Field: F_measure_unit_type
Data Source: measure_unit
Text Fields
Text Field: B_6
Text: Unit of Measure:
Section: Frame M_MEASURE_DATA_SOURCE_NAME_FR
Fields
Field: F_data_source
Data Source: data_source
Text Fields
Text Field: B_7
Text: Data Source:
Section: Frame M_Org_Unit_Wrapper
Fields
Field: F_Measure_Org_Unit
Data Source: Measure_Org_Unit
Text Fields
Text Field: B_22
Text: Org Unit:
Section: Frame M_RelatedMeasures
Text Fields
Text Field: B_RELATED_MEASURES
Text: Related Measures:
Text Field: B_RELATION
Text: Relation
Text Field: B_REL_MEASURE_NUMBER
Text: Measure #
Text Field: B_REL_MEASURE_TITLE
Text: Title
Section: Frame M_Commitments
Text Fields
Text Field: B_RELATED_COMMITMENTS
Text: Commitments:
Text Field: B_2
Text: No data.
Section: Frame M_ActivityNotes
Text Fields
Text Field: B_RELATED_COMMITMENTS1
Text: Activity Notes:
Text Field: B_ACT_NOTE_NUMBER
Text: Number
Text Field: B_ACT_NOTE_TITLE
Text: Title
Section: Frame M_GRAPH_OR_IMAGE_FRAME
Fields
Field: F_percent_or_dollar_symbol
Data Source: m_unit_type
Text Fields
Text Field: B_21
Text: No graphing data entered.
Text Field: B_RELATED_MEASURES1
Text: Graphing:
Section: Frame M_GRAPH_DATA_TABULAR_VIEW
Text Fields
Text Field: B_RECORDED_DATE
Text: Entered Data Date
Text Field: B_TARGET_VALUE
Text: Target Value
Text Field: B_Actual
Text: Actual Value
Text Field: B_Actual_Total
Text: Actual Total
Section: Frame M_CHART
Text Fields
Text Field: B_chart_label
Text: Charts
Section: Frame M_chart_description_wrapper
Fields
Field: F_Chart_Description
Data Source: chart_description
Text Fields
Text Field: B_Chart_Description
Text: Description:
Section: Frame M_chart_data_source_wrapper
Fields
Field: F_Chart_Data_Source
Data Source: chart_data_source
Text Fields
Text Field: B_Data_Source
Text: Data Source:
Section: Frame M_Rep_Status_Comment_Wrapper
Fields
Field: F_chart_rep_status_comment
Data Source: chart_rep_status_comment
Text Fields
Text Field: B_chart_reporting_status1
Text: Status Comment:
Margin
Section: Margin
Fields
Field: F_Run_Date1
Data Source: CurrentDate
Field: F_Page
Data Source: PageNumber
Field: F_Total_Pages
Data Source: TotalPages
Field: F_MINISTRY_NAME
Data Source: CF_MINISTRY_NAME
Text Fields
Text Field: B_9
Text: &CP_REPORT_TITLE
Text Field: B_11
Text: EVista Report (v&CP_Version) run:
Text Field: B_10
Text: Page &F_Page of &F_Total_Pages
Text Field: B_3
Text: Confidential
Web Sources
Source: <%@ taglib uri="/WEB-INF/lib/reports_tld.jar" prefix="rw" %>
<%@ page language="java" import="java.io.*" errorPage="/rwerror.jsp" session="false" %>
<%@ page contentType="text/html;charset=ISO-8859-1" %>
<!--
<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="MGmeastitleGRPFR168">
<table>
<caption> </caption>
<!-- Header -->
<thead>
<tr>
<th <rw:id id="HBmeastitle168" asArray="no"/>> PM: </th>
<th <rw:id id="HBmeasureunit168" asArray="no"/>> Measure Units: </th>
<th <rw:id id="HBmeasuretype168" asArray="no"/>> Measure Type: </th>
<th <rw:id id="HBrepfreq168" asArray="no"/>> Reporting Frequency: </th>
<th <rw:id id="HBdatasource168" asArray="no"/>> Data
Source: </th>
<th <rw:id id="HBmeasureunittype168" asArray="no"/>> Unit of
Measure Type: </th>
<th <rw:id id="HBstatusnote168" asArray="no"/>> Status
Note: </th>
</tr>
</thead>
<!-- Body -->
<tbody>
<rw:foreach id="RGmeastitle1681" src="G_meas_title">
<tr>
<td <rw:headers id="HFmeastitle168" src="HBmeastitle168"/>><rw:field id="Fmeastitle168" src="meas_title" nullValue=" "> F_meas_title </rw:field></td>
<td <rw:headers id="HFmeasureunit168" src="HBmeasureunit168"/>><rw:field id="Fmeasureunit168" src="measure_unit" nullValue=" "> F_measure_unit </rw:field></td>
<td <rw:headers id="HFmeasuretype168" src="HBmeasuretype168"/>><rw:field id="Fmeasuretype168" src="measure_type" nullValue=" "> F_measure_type </rw:field></td>
<td <rw:headers id="HFrepfreq168" src="HBrepfreq168"/>><rw:field id="Frepfreq168" src="rep_freq" nullValue=" "> F_rep_freq </rw:field></td>
<td <rw:headers id="HFdatasource168" src="HBdatasource168"/>><rw:field id="Fdatasource168" src="data_source" nullValue=" "> F_data_source </rw:field></td>
<td <rw:headers id="HFmeasureunittype168" src="HBmeasureunittype168"/>><rw:field id="Fmeasureunittype168" src="measure_unit_type" nullValue=" "> F_measure_unit_type </rw:field></td>
<td <rw:headers id="HFstatusnote168" src="HBstatusnote168"/>><rw:field id="Fstatusnote168" src="status_note" nullValue=" "> F_status_note </rw:field></td>
</tr>
</rw:foreach>
</tbody>
</table>
</rw:dataArea> <!-- id="MGmeastitleGRPFR168" -->
<!-- End of Data Area Generated by Reports Developer -->
</body>
</html>
<!--
</rw:report>
-->