Commands(s)
Q_STRATEGY
SELECT
bs.id,
bs.title strategy_title,
bs.business_number,
bs.description strat_desc,
bs.status_id,
bs.target_id,
bs.owner_unit_id,
bs.start_date,
bs.end_date,
bs.create_by_id,
bs.create_date,
bs.update_by_id,
bs.update_date,
tr.title target,
st.title strat_status,
rs.status strat_rs,
rs.overall_comment strat_rs_comments
FROM
BA_STRATEGY bs,
VALUE_LIST tr,
VALUE_LIST st
, (
SELECT
e.object_id
-- IDs (just in case) and status descriptions
, e.status_id, reporting_status_values.title as status
, e.quality_status_id, quality_status_values.title as quality_status
, e.budget_status_id, budget_status_values.title as budget_status
, e.time_status_id, time_status_values.title as time_status
-- comments from NOTE table
, notes.description as overall_comment
, quality_att_notes.description as comment_on_quality
, time_att_notes.description as comment_on_time
, budget_att_notes.description as comment_on_budget
FROM
-- ******* START nested table 'e'
-- ******* all status IDs and corresponding descriptions are flattened to a single row per object_id
-- ******* ( in a level above, use it to joinn to NOTE table to retrieve comments )
(
select
d.object_id
, SUM(status_id) as status_id
, SUM(time_status_id) as time_status_id
, SUM(quality_status_id) as quality_status_id
, SUM(budget_status_id) as budget_status_id
, SUM(report_id ) as report_id
, SUM(time_status_report_id) as time_status_report_id
, SUM(quality_status_report_id ) as quality_status_report_id
, SUM(budget_status_report_id ) as budget_status_report_id
from
-- ******* START nested table 'd'
-- ******* prepare reporting data for grouping by object_id
-- ******* it basically ADDs columns to 'c' data, so the reaul set will have OBJECT_ID plus two fields for each 4 attributes
-- ******* note that attributes themself are being omittedm but from the column names is obvious what are the refereing to
(
select
c.object_id
, DECODE(c.attribute, null, status_id) as status_id
, DECODE(c.attribute, 'Time', status_id) as time_status_id
, DECODE(c.attribute, 'Quality', status_id) as quality_status_id
, DECODE(c.attribute, 'Budget', status_id) as budget_status_id
, DECODE(c.attribute, null, id) as report_id
, DECODE(c.attribute, 'Time', id) as time_status_report_id
, DECODE(c.attribute, 'Quality', id) as quality_status_report_id
, DECODE(c.attribute, 'Budget', id) as budget_status_report_id
from
-- ******* START nested table 'c''
-- ******* list RELEVANT / LATEST reporting status IDs for eaach attribute
-- ******* flag ACTIVE saves the day here, as there is always ONLY ONE record with ACTIVE == 1 for each of the
-- ******* attributes { null, Time, Quality, Budget }
(
select a.OBJECT_ID, a.ATTRIBUTE, a.STATUS_ID, a.id
from
report_status a
, easy_domain -- join to filter out ALL but Files
where a.sub_domain_id = easy_domain.ID
and easy_domain.ID = 3 -- values: 1-SUBMISSION, 3-STRATEGY, 4-FILE, 6- BILL 2 - measurement
and ( a.ATTRIBUTE in ('Time', 'Quality', 'Budget') or a.ATTRIBUTE is null )
and a.ACTIVE = 1
) c
-- ******* END nested table 'c'
-- ******************************
) d
-- ******* END nested table 'd'
-- ******************************
group by d.object_id
) e
-- ******* END nested table 'e'
-- ******************************
-- used for (vanila) status' description
, (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values
-- used for ('Time') status' description
, (select id, title from value_list where type = 'REPORTING_STATUS' ) time_status_values
-- used for ('Quality') status' description
, (select id, title from value_list where type = 'REPORTING_STATUS' ) quality_status_values
-- used for ('Budget') status' description
, (select id, title from value_list where type = 'REPORTING_STATUS' ) budget_status_values
-- value_id = 13 is 'Reporting Status' sub domain ID
-- Aug 13, 2008 ok, it seems that value_id has silently disapeared so we'll use expression ' easy_domain.Title = 'Reporting_Status''instead
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id) notes
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) quality_att_notes
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) time_att_notes
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) budget_att_notes
WHERE
-- join softly VALUE_LIST table(s) ONCE for EACH attribute
reporting_status_values.id(+) = e.status_id
and time_status_values.id(+) = e.time_status_id
and quality_status_values.id(+) = e.quality_status_id
and budget_status_values.id(+) = e.budget_status_id
-- join softly NOTE table(s) ONCE for EACH attribute
and notes.object_class_id(+) = e.report_id
and quality_att_notes.object_class_id(+) = e.quality_status_report_id
and time_att_notes.object_class_id(+) = e.time_status_report_id
and budget_att_notes.object_class_id(+) = e.budget_status_report_id
) rs
WHERE
bs.target_id = tr.id AND
tr.TYPE= 'MINISTRY_TARGET' AND
bs.status_id = st.id AND
st.TYPE = 'EASY_STATUS'
and bs.id = :cp_strategy_id AND
-- OUTER join
bs.id = rs.object_id(+)
AND bs.id IN
(
SELECT rvd.object_id
FROM REPORT_VALID_SET rvs
, REPORT_VALID_DATA rvd
WHERE rvs.id = rvd.report_valid_set_id
AND rvd.sub_domain_id = 3
AND rvd.report_valid_set_id = :P_Report_Valid_Set_ID
AND rvs.session_id = :P_Session_ID)
Q_OBJECTIVES
SELECT
--'Objectives:' obj_hdr1,
BA_STRATEGY_OBJECTIVE.TITLE st_objective, BA_STRATEGY_OBJECTIVE.STRATEGY_ID
FROM BA_STRATEGY_OBJECTIVE
Q_UNITS_IT_BELONGS_TO
select bsu.unit_id,
u.acronym
, reports_pkg.GETENTIREUNITPATH( U.ID, reports_pkg.getUnitLastName(u.id)) AS unit_name
, bsu.strategy_id u_strat
from ba_strategy_unit bsu
, unit u
where bsu.unit_id = u.id
/*
select bsu.unit_id,
u.acronym
, reports_pkg.getUnitLastName(bsu.unit_id) unit_name
, bsu.strategy_id u_strat
from ba_strategy_unit bsu, unit u
where bsu.unit_id = u.id
*/
Q_METRICS
select mber.object_id meas_strategy
, mm.title meas_title
, m_utyp.title meas_type
, mm.measure_unit
, m_freq.title meas_freq
, m_stat.title meas_status
, decode(m_type.value_id, 2, 'Rollup Measures', 3, 'Rollup Measures', 'Measures') measure_type
, mm.id as measurement_id
, mm.business_id measure_number
from
m_business_e_ref mber
, m_measure mm
, value_list m_utyp
, value_list m_freq
, value_list m_stat
, value_list m_type
where mber.id = mm.ber_id
and mm.measure_unit_type_id = m_utyp.id
and m_utyp.type = 'MEASURE_UNIT_TYPE'
and mm.recording_frequency_id = m_freq.id
and m_freq.type = 'MEASURE_RECORDING_FREQUENCY'
and mm.status_id = m_stat.id
and m_stat.type = 'EASY_STATUS'
and mm.type_id = m_type.id
and m_type.type = 'MEASURE_TYPE'
and mm.id in
(
SELECT rvd.object_id
FROM REPORT_VALID_SET rvs
, REPORT_VALID_DATA rvd
WHERE rvs.id = rvd.report_valid_set_id
AND rvd.sub_domain_id = 2
AND rvd.report_valid_set_id = :P_Report_Valid_Set_ID
AND rvs.session_id = :P_Session_ID)
Q_RELATED_FILES
-- Aug 15, 2008 rewrote related files. to simpler, one hopefully
SELECT f.title as file_name
, f.business_number as file_number
, rec_stat.title as file_status
, reporting_status.status as file_reporting_status
, reporting_status.overall_comment as file_reporting_status_comment
, units.file_division
, f.id file_id
FROM file_strategy_objective fso
, ba_file f
, (select id, title from value_list where type = 'EASY_STATUS') rec_stat
-- ************ n e s t e d r e p o r t i n g s t a t u s - S T A R T ********************
, (
SELECT
e.object_id
-- IDs (just in case) and status descriptions
, e.status_id, reporting_status_values.title as status
, e.quality_status_id, quality_status_values.title as quality_status
, e.budget_status_id, budget_status_values.title as budget_status
, e.time_status_id, time_status_values.title as time_status
-- comments from NOTE table
, notes.description as overall_comment
, quality_att_notes.description as comment_on_quality
, time_att_notes.description as comment_on_time
, budget_att_notes.description as comment_on_budget
FROM
-- ******* START nested table 'e'
-- ******* all status IDs and corresponding descriptions are flattened to a single row per object_id
-- ******* ( in a level above, use it to joinn to NOTE table to retrieve comments )
(
select
d.object_id
, SUM(status_id) as status_id
, SUM(time_status_id) as time_status_id
, SUM(quality_status_id) as quality_status_id
, SUM(budget_status_id) as budget_status_id
, SUM(report_id ) as report_id
, SUM(time_status_report_id) as time_status_report_id
, SUM(quality_status_report_id ) as quality_status_report_id
, SUM(budget_status_report_id ) as budget_status_report_id
from
-- ******* START nested table 'd'
-- ******* prepare reporting data for grouping by object_id
-- ******* it basically ADDs columns to 'c' data, so the reaul set will have OBJECT_ID plus two fields for each 4 attributes
-- ******* note that attributes themself are being omittedm but from the column names is obvious what are the refereing to
(
select
c.object_id
, DECODE(c.attribute, null, status_id) as status_id
, DECODE(c.attribute, 'Time', status_id) as time_status_id
, DECODE(c.attribute, 'Quality', status_id) as quality_status_id
, DECODE(c.attribute, 'Budget', status_id) as budget_status_id
, DECODE(c.attribute, null, id) as report_id
, DECODE(c.attribute, 'Time', id) as time_status_report_id
, DECODE(c.attribute, 'Quality', id) as quality_status_report_id
, DECODE(c.attribute, 'Budget', id) as budget_status_report_id
from
-- ******* START nested table 'c''
-- ******* list RELEVANT / LATEST reporting status IDs for eaach attribute
-- ******* flag ACTIVE saves the day here, as there is always ONLY ONE record with ACTIVE == 1 for each of the
-- ******* attributes { null, Time, Quality, Budget }
(
select a.OBJECT_ID, a.ATTRIBUTE, a.STATUS_ID, a.id
from
report_status a
, easy_domain -- join to filter out ALL but Files
where a.sub_domain_id = easy_domain.ID
and easy_domain.ID = 4 -- values: 1-SUBMISSION, 3-STRATEGY, 4-FILE, 6- BILL 2 - measurement
and ( a.ATTRIBUTE in ('Time', 'Quality', 'Budget') or a.ATTRIBUTE is null )
and a.ACTIVE = 1
) c
-- ******* END nested table 'c'
-- ******************************
) d
-- ******* END nested table 'd'
-- ******************************
group by d.object_id
) e
-- ******* END nested table 'e'
-- ******************************
-- used for (vanila) status' description
, (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values
-- used for ('Time') status' description
, (select id, title from value_list where type = 'REPORTING_STATUS' ) time_status_values
-- used for ('Quality') status' description
, (select id, title from value_list where type = 'REPORTING_STATUS' ) quality_status_values
-- used for ('Budget') status' description
, (select id, title from value_list where type = 'REPORTING_STATUS' ) budget_status_values
-- value_id = 13 is 'Reporting Status' sub domain ID
-- Aug 13, 2008 ok, it seems that value_id has silently disapeared so we'll use expression ' easy_domain.Title = 'Reporting_Status'' instead
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id) notes
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) quality_att_notes
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) time_att_notes
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id ) budget_att_notes
WHERE
-- join softly VALUE_LIST table(s) ONCE for EACH attribute
reporting_status_values.id(+) = e.status_id
and time_status_values.id(+) = e.time_status_id
and quality_status_values.id(+) = e.quality_status_id
and budget_status_values.id(+) = e.budget_status_id
-- join softly NOTE table(s) ONCE for EACH attribute
and notes.object_class_id(+) = e.report_id
and quality_att_notes.object_class_id(+) = e.quality_status_report_id
and time_att_notes.object_class_id(+) = e.time_status_report_id
and budget_att_notes.object_class_id(+) = e.budget_status_report_id
) reporting_status
-- ************ n e s t e d r e p o r t i n g s t a t u s - S T A R T ********************
-- add units related to associated files
,(
select fu.FILE_ID, reports_pkg.getEntireUnitPath( fu.unit_id, reports_pkg.GETUNITLASTNAME(u.id )) as file_division
from file_unit fu, unit u
where u.ID = fu.UNIT_ID
) units
WHERE strategy_id = :P_OBJECT_ID
and f.id = fso.file_id
and rec_stat.id = f.status_id
-- join 'divisions'
and units.file_id = f.id
-- join reporting status data
and reporting_status.object_id(+) = f.id
AND f.id IN
(
SELECT rvd.object_id
FROM REPORT_VALID_SET rvs
, REPORT_VALID_DATA rvd
WHERE rvs.id = rvd.report_valid_set_id
AND rvd.sub_domain_id = 4
AND rvd.report_valid_set_id = :P_Report_Valid_Set_ID
AND rvs.session_id = :P_Session_ID)
Q_DOCUMENTS
-- Sep 3, added the "latest version" nested table
SELECT
cm.co_menutext doc_pkg_templ_name
, dt.title doc_type_title
, doc.co_description doc_desc
, doc.co_docname
, :id doc_sub_id
, co_sourceauthor doc_creator
, vrsn.co_filename
, to_date(co_docdate, 'MM/DD/YY HH:MI AM') doc_date
, vrsn.co_version
, doc.co_docid as doc_id
, 'Documents:' as dummy_field
FROM (
select b.co_menutext, b.co_menuid, level
from co_menus b
connect by prior b.CO_MENUID = b.CO_MENUPARENT
start with b.CO_MENUID in (
select a.co_menuid from co_menus a where a.CO_MENUPARENT = 0 and TRIM(a.co_menuref) = '3_' || TO_CHAR(:id)
)
) cm
, co_document doc
, document_type dt
, (
select ver.co_version, ver.co_versioncomment , ver.co_docid, ver.co_filename
from co_versions ver
where ver.co_version = ( select max(ver2.co_version) from co_versions ver2 where ver2.co_docid = ver.co_docid)
) vrsn
WHERE doc.co_menuid = cm.co_menuid
and doc.co_doctype = dt.id(+)
and vrsn.co_docid = doc.co_docid
Q_CONTACTS
-- query for Summary Reports
-- provides data to list contacts as tha application does, i.e. ministry, contact team, roles , users
-- first sort criteria is contact potfolio's ministry, and current ministry is listed first ( 'sort_id )
select
DECODE(u.ministry_id, :P_MIN_ID, -999, ABS(u.ministry_id)) as sort_id
, reports_pkg.GETUNITLASTNAME( u.ministry_id ) as Contact_Portf_Ministry
, cp.object_id as cont_object_id
--, u.acronym as cont_unit
, ct.title as cont_team_title
, cr.title as cont_role_title
, i.first_name || ' ' || i.last_name cont_name
from easy_contact_portfolio cp
, CONTACT_GROUP cg, CONTACT c
, contact_role cr, contact_team ct
, individual i
, unit u
, easy_domain d
where cp.id = cg.EASY_CONTACT_PORTFOLIO_ID
and cg.id = c.CONTACT_GROUP_ID
and c.contact_role_id = cr.id
and ct.id = cg.contact_team_id
and ct.id = cr.contact_team_id
and c.status = 1
-- join individual
and c.individual_id = i.id
-- join unit table
and cp.unit_id = u.id
-- jOion domain table
and d.id = cp.SUB_DOMAIN_ID
-- filter all but our domain records
and d.id = 3 -- :P_SUB_DOMAIN_ID -- 1 - SUBMISSION, 3 - STRATEGY, 4 - FILE, - BILL
--and cp.object_id = :P_OBJECT_ID
--order by 1, ct.title, cr.title, cont_name
Q_GRAPH_DATA_TABULAR_VIEW
/*
JK Jan 8 2009 add 'm_graphing' table to second query in UNION ALL clause.
It's NOT to get any data for a result set but to use it as a filter to make sure that only data from a single target set is displayed
It used to be 'm_target_portfolio.is_master' column used for that, and now is m_graphing.target_portfolio_id'
The moment when 'm_graphing' table has more then one record - we are screwed
SELECT a.record_date as gr_record_date
, sum(a.target_value ) as gr_target_value
, sum(a.period_value) as gr_actual_value
, sum(a.total_value) as gr_total_to_date
, :measurement_id as raw_data_measure_id
FROM
(
-- first union provides only actual values, and always zero as 'Target Value'
select a.record_date
, 0 as target_value
, a.period_value
, a.total_value
from m_actual_record a
where a.measure_id = :MEASUREMENT_ID
union all
-- second union provides only 'Target Value'' and zeros instead of actual values
select
t.RECORD_DATE
-- , t.PERIOD_VALUE
, t.VALUE
, 0
, 0
from
m_target_record t
, m_target_portfolio tp
, m_graphing g
where tp.MEASURE_ID = :MEASUREMENT_ID
and t.TARGET_PORTFOLIO_ID = tp.ID
--and tp.IS_MASTER = 1
and g.target_portfolio_id = tp.id and g.measure_id = tp.measure_id
) a
GROUP BY a.RECORD_DATE
*/
SELECT a.record_date as gr_record_date
-- Jan 29 2009 - using "min()" here because "sum()" would not take non-digits. Should be the same result, as there is
, MIN( to_char( a.target_value ) || DECODE( a.high_value, 0, '', ' - ' || to_char(a.high_value) ) ) as gr_target_value
, sum(a.period_value) as gr_actual_value
, sum(a.total_value) as gr_total_to_date
, :measurement_id as raw_data_measure_id
FROM
(
-- first union provides only actual values, and always null as 'Target Value'
select a.record_date
, Null as target_value
, a.period_value
, a.total_value
, a.measure_id
, 0 high_value
from m_actual_record a
where a.measure_id = :measurement_id
union all
-- second union provides only 'Target Value'' and nulls instead of actual values
select
t.RECORD_DATE
-- JK Sep 24, 2008 do note that in m_target_record, the values are in VALUE column, unlike in m_actual_record where it's in PERIOD_VALUE
, t.value as PERIOD_VALUE
, Null
, Null
, tp.MEASURE_ID
, t.high_value
from
m_target_record t
, m_target_portfolio tp
, m_graphing g
where tp.MEASURE_ID = :measurement_id
and t.TARGET_PORTFOLIO_ID = tp.ID
-- JK 16-DEC-2008 obsolete check: and tp.IS_MASTER = 1
and g.target_portfolio_id = tp.id and g.measure_id = tp.measure_id
) a
GROUP BY a.RECORD_DATE
Q_Graph_Of_Master_Target
---------------------------------------------------------------------------------------------------------------------------
------------------- NOTE: this will retun a SINGLE graphing id, but only because target portfolio
------------------- selected is MASTER target portfolio
------------------- otherwise, it one-to_many relationsio between measure and graphs
--------------------------------------------------------------------------------------------------------------------------
SELECT
a.MEASURE_ID graph_measure_id
, a.id as graphing_id
, vl.title as system_or_user_generated
, TP.STATEMENT_OF_RESULT
-- add image for measure.
-- there can be many graphs for a single measurement, but as we will
-- consider only MASTER portfolio, effectively there will be one image per measurement
,image_source.co_docid
,image_source.co_versioncomment
,image_source.co_filecontent
-- similar rational as for images - read m_graphing data of master target portfolio only. in order to figure out graph type
, graph_type_nt.Line_or_Bar
FROM
m_graphing a
, value_list vl
, m_target_portfolio tp
-- TODO explain nested 'image_siurce' table
, (select doc_versions.co_docid, doc_versions.co_versioncomment, doc_versions.co_filecontent, :measurement_id as measure_id
from (select b.co_menutext, b.co_menuid, level, sys_connect_by_path( co_menutext, '\' ) path
from co_menus b
connect by prior b.co_menuid = b.co_menuparent
start with b.co_menuid in (
select a.co_menuid from co_menus a where a.co_menuparent = 0 and a.co_menuref =
-- s t a r t give me graphing id that corresponds to master portfolio, for known measure id
(select '22_' || to_char(nvl(max(g2.id),0))
from m_graphing g2, m_target_portfolio tp2
where g2.measure_id = :measurement_id
and tp2.measure_id = g2.measure_id
and tp2.id = g2.target_portfolio_id
-- JK 22-Jan-2009 OBSOLETE and tp2.is_master = 1
)
-- e n d give me graphing id that corresponds to master portfolio, for known measure id
)
) cm
, co_document doc
, document_type dt
, (
select ver.co_docid, ver.co_versioncomment, ver.co_filecontent
from co_versions ver
where ver.co_version = ( select max(ver2.co_version) from co_versions ver2 where ver2.co_docid = ver.co_docid)
-- skip all but .bmp, .jpg and .gif files (JK 11-Mar-2009)
and upper(substr(ver.co_filename, instr(ver.co_filename, '.', -1, 1) + 1)) in ('JPG', 'BMP', 'GIF')
) doc_versions
where doc.co_menuid = cm.co_menuid
and doc.co_doctype = dt.id(+)
-- join ('vrsions' ) table that contains the image
and doc.co_docid = doc_versions.co_docid
) image_source
-- create table that'll give graph type for master target set Line or Bar, based on 'm_graphing.format_id' value
-- 'decode' is to translate to Report Builder dictionary
, ( select vl2.id
, DECODE(vl2.title, 'Line', 'LINE_VERT_ABS', 'Bar', 'BAR_VERT_CLUST', 'LINE_VERT_ABS' ) as Line_or_Bar
from value_list vl2
where vl2.type = 'MEASURE_GRAPH_PRESENTATION'
) graph_type_nt
where a.MEASURE_ID = :measurement_id
and vl.type = 'MEASURE_GRAPH_TYPE'
and vl.id = a.TYPE_ID
and a.TARGET_PORTFOLIO_ID = tp.id
-- JK 22-Jan-2009 OBSOLETE and tp.IS_MASTER = 1
-- image part might not have anything
and a.MEASURE_ID = image_source.measure_id(+)
-- join nested table to give us graph type
and graph_type_nt.id = a.format_id
Q_Legends
/*
----------------------------------------------------------
------------ build legends query --------------
----------------------------------------------------------
SELECT a.title as measurements_set_name, rownum, a.id as graphing_id, measure_id as legend_measure_id
FROM (
select distinct 'Targets (' || tp3.TITLE || ')' as title
, g3.id
, tp3.MEASURE_ID
from m_target_portfolio tp3 , M_GRAPHING_VALUE gv3, m_graphing g3
where tp3.MEASURE_ID = :measurement_id
and g3.TARGET_PORTFOLIO_ID = tp3.id
and gv3.GRAPHING_ID = g3.id
-- JK 21-JAN-2009 obsolete check: and tp3.is_master = 1
UNION ALL
-- select distinct 'Actuals', 0, measure_id from m_actual_record where measure_id = :measure_id
-- Jaroslav Kolar, Sep 25, 2008 retieving from actualls is wating CPU cycles and more importantly, it does not return anything in case when actuals are NOT existing
-- as a consequence, legend will not have a red box denoting actual values ...
-- and at the same time, 'm_graphing_value' table willl contain (zero) values so the flat line at the bottom will be drawn
-- That might be confusing ( line without legend explaining it ) and therefore this retrieval from 'dual' that will ensure that acutals record is always there
select 'Actuals', 0, :measurement_id as measure_id from dual
) a
-- JK Oct 23, can I assume :CP_SERIES_COUNT is 1 all the time ????
-- WHERE rownum <= :CP_SERIES_COUNT + 1 -- adding one is because actuals are handled differently
WHERE rownum <= 2
-- this is taking the value from parent query and basically killing the result set if graph is marked as 'User Provided'
and :system_or_user_generated = 'System Generated'
ORDER BY 3
*/
----------------------------------------------------------
------------ build legends query --------------
----------------------------------------------------------
-- as of Rel 3.0, there will be only one record (per measurement) in 'm_graphing' table
SELECT a.title as measurements_set_name, rownum, a.id as graphing_id, measure_id as legend_measure_id
FROM (
select distinct 'Targets (' || tp3.TITLE || ')' as title
, g3.id
, tp3.MEASURE_ID
from m_target_portfolio tp3, m_graphing g3
where tp3.MEASURE_ID = :measurement_id
and g3.TARGET_PORTFOLIO_ID = tp3.id
-- JK 16-DEC-2008 obsolete check: and tp3.is_master = 1
UNION ALL
-- retrieval from 'dual' that will ensure that acutals record is always there
select 'Actuals', 0, :measurement_id as measure_id from dual
UNION ALL
-- third part of the union, will return a result set only id targets are declaed as ranges
select distinct 'Targets High Values' as title
, g3.id
, tp3.MEASURE_ID
from m_target_portfolio tp3 , m_graphing g3, value_list vl
where tp3.MEASURE_ID = :measurement_id
and g3.TARGET_PORTFOLIO_ID = tp3.id
and vl.type = 'MEASURE_TARGET_DEFINITION'
and tp3.definition_id = vl.id
and vl.title = 'Range'
) a
-- JK Oct 23, can I assume :CP_SERIES_COUNT is 1 all the time ????
-- WHERE rownum <= :CP_SERIES_COUNT + 1 -- adding one is because actuals are handled differently
WHERE rownum <= 3
-- this is taking the value from parent query and basically killing the result set if graph is marked as 'User Provided'
and :system_or_user_generated = 'System Generated'
ORDER BY 3
Q_Graphing_Values
-- Jaroslav, Aug 12 2008 added M_TARGET_PORTFOLIO to the query,
-- which probably makes M_GRAPHING_VALUE and nested 'a' table obsolete
-- that was meant for VARIABLE number of target sets.
-- Anyway, I will leave it for now.
-- NOTE: watch out when introducing column aliases as &CP_.....
-- that column might not show up AT ALL so query-rebuild might be the only solution
-- JK: 25-Jan-2009 - recode
SELECT
gv.graphing_id
, gv.label as x_axis_value
, gv.measure_value as ACTUAL_VALUE1
, gv.target_value as TARGET_VALUE1
, gv.TARGET_HIGH_VALUE
, g.measure_id as graphing_measure_id
FROM m_graphing g
, m_graphing_value gv
WHERE gv.GRAPHING_ID = g.id
and g.measure_id = :measurement_id -- value from the parent table
-- this is taking the value from parent query and basically killing the result set if graph is marked as 'User Provided'
and :system_or_user_generated = 'System Generated'
ORDER BY gv.graphing_id
/*
SELECT
gv.GRAPHING_ID
, gv.label as x_axis_value
, gv.MEASURE_VALUE as actual_value
, gv.TARGET_VALUE
, b.row_number
-- , b.graphing_id
, DECODE( b.row_number, 1, gv.MEASURE_VALUE) as ACTUAL_VALUE1
, DECODE( b.row_number, 1, gv.TARGET_VALUE) as TARGET_VALUE1
, DECODE( b.row_number, 2, gv.TARGET_VALUE) as TARGET_VALUE2
, DECODE( b.row_number, 3, gv.TARGET_VALUE) as TARGET_VALUE3
, DECODE( b.row_number, 4, gv.TARGET_VALUE) as TARGET_VALUE4
, DECODE( b.row_number, 5, gv.TARGET_VALUE) as TARGET_VALUE5
, DECODE( b.row_number, 6, gv.TARGET_VALUE) as TARGET_VALUE6
, DECODE( b.row_number, 7, gv.TARGET_VALUE) as TARGET_VALUE7
, DECODE( b.row_number, 8, gv.TARGET_VALUE) as TARGET_VALUE8
, DECODE( b.row_number, 9, gv.TARGET_VALUE) as TARGET_VALUE9
-- add measure id to be linked to 'measurement' table
, g.measure_id as graphing_measure_id
FROM m_graphing g
, M_GRAPHING_VALUE gv
, (
SELECT rownum as row_number, a.graphing_id
FROM (
select distinct graphing_id
from M_GRAPHING_VALUE gv2
, m_graphing g2
where gv2.GRAPHING_ID = g2.id
and g2.measure_id in :measurement_id -- value from the parent table
) a
) b
, m_target_portfolio tp
WHERE gv.GRAPHING_ID = g.id
and gv.GRAPHING_ID = b.GRAPHING_ID
and tp.id = g.target_portfolio_id
-- select only DOMINANT one
-- JK 16-DEC-2008 obsolete check: and tp.is_master = 1
-- this is taking the value from parent query and basically killing the result set if graph is marked as 'User Provided'
and :system_or_user_generated = 'System Generated'
ORDER BY gv.GRAPHING_ID
*/
Q_Chart_Attributes
SELECT
c.id as basic_chart_id,
c.chart_data_id as chart_data_id
, c.title as chart_title
, c.header as chart_header
, c.footer as chart_footer
, c.period_start as chart_start_dt
, c.period_end as chart_end_dt
-- , c.style_id as chart_style
, freq.freq_title
, c.description as chart_description
, c.data_source as chart_data_source
, rs.status as chart_reporting_status
, rs.overall_comment as chart_rep_status_comment
, parent.chart_parent_id
, parent.sub_domain_id
FROM
m_chart c
, ( select id as freq_id, title as freq_title from value_list where type = 'MEASURE_RECORDING_FREQUENCY' ) freq
-- nested table that gived ber_id for given parent S T A R T
, (
select ber.id as chart_ber_id, str.id as chart_parent_id, ber.sub_domain_id
from m_business_e_ref ber, ba_strategy str
where ber.sub_domain_id = 3
and str.id = ber.object_id
and str.id = :id
) parent
-- nested table that gived ber_id for given parent E N D
-- nested table with reporting status and comment S T A R T
, (
select
a.object_id
, a.status_id
, reporting_status_values.title as status
-- comments from NOTE table
, notes.description as overall_comment
from report_status a
, (select id, title from value_list where type = 'REPORTING_STATUS' ) reporting_status_values
, ( select note.object_class_id, note.description from note, easy_domain where easy_domain.Title = 'Reporting_Status' and sub_domain_id = easy_domain.id) notes
where
a.sub_domain_id = 19
and ( a.attribute is null )
and a.active = 1
-- join softly VALUE_LIST table(s) ONCE for EACH attribute
and reporting_status_values.id(+) = a.status_id
-- join softly NOTE table(s)
and notes.object_class_id(+) = a.id
) rs
-- nested table with reporting status and comment E N D
WHERE
parent.chart_ber_id = c.ber_id
and c.recording_frequency_id = freq_id(+)
and c.id = rs.object_id(+)
Q_1
SELECT DECODE(SUBSTR( upper(category_scope.title),1,3), 'OPS', '1OPS-Wide Management Categories', 'MIN', '2'||:CP_Ministry_Acronym || '-Specific Management Categories', '3Division-Specific Management Categories') || ':' as category_scope
, mc.TITLE AS category_title
--, LPAD(' ', (LEVEL - 1)*7) || lpad(lvel.TITLE, 20) || ': ' || value.title AS sub_categories_tree
--, lvel.title cat_hierarchy_title, value.title AS sub_categories_tree
, LPAD(' ', (LEVEL - 1)*7) || lpad(lvel.TITLE, 20) cat_hierarchy_title, value.title AS sub_categories_tree
, DECODE(SUBSTR( UPPER(category_scope.title),1,3), 'OPS', 1, 'MIN', 2, 3 ) AS sort_by_scope
, value.id value_id
FROM MGMT_CATEGORY_VALUE value
, MGMT_CATEGORY_LEVEL lvel
, MANAGEMENT_CATEGORY mc
, (SELECT title, id FROM VALUE_LIST WHERE TYPE = 'MANAGEMENT_CATEGORY_SCOPE') category_scope
WHERE value.MGMT_CATEGORY_LEVEL_ID = lvel.ID
AND mc.ID = lvel.MANAGEMENT_CATEGORY_ID
AND category_scope.id = mc.SCOPE_ID
AND mc.ENABLED = 1
AND value.id IN (
-- nested_2: expanded a list from 'nested_1' with parent IDs
SELECT DISTINCT id
FROM MGMT_CATEGORY_VALUE
START WITH id IN (
-- nested_1 : list only values linked to to given file ID
SELECT value2.id FROM
MGMT_CATEGORY_VALUE value2
,FILE_MANAGEMENT_CATEGORY fmc2
WHERE value2.id = fmc2.MGMT_CATEGORY_VALUE_ID
AND fmc2.FILE_ID = :FILE_ID
)
CONNECT BY PRIOR PARENT_ID = id
)
START WITH value.PARENT_ID IS NULL
CONNECT BY PRIOR value.id = value.PARENT_ID
order by 3 desc
Q_2
--SELECT lvel.hierarchy_level , lvel.title hierarchy_title, value.title AS hierarchy_tree
--SELECT rpad(lvel.TITLE, 20) || ': ' || value.title AS hierarchy_tree
SELECT lvel.hierarchy_level , lvel.title hierarchy_title, LPAD(' ', (LEVEL - 1)*7) || lpad(value.TITLE, 20) AS hierarchy_tree
FROM MGMT_CATEGORY_VALUE value
, MGMT_CATEGORY_LEVEL lvel
, MANAGEMENT_CATEGORY mc
, (SELECT title, id FROM VALUE_LIST WHERE TYPE = 'MANAGEMENT_CATEGORY_SCOPE') category_scope
WHERE value.MGMT_CATEGORY_LEVEL_ID = lvel.ID
AND mc.ID = lvel.MANAGEMENT_CATEGORY_ID
AND category_scope.id = mc.SCOPE_ID
AND mc.ENABLED = 1
AND value.id != :id
AND value.id IN (
-- nested_2: expanded a list from 'nested_1' with parent IDs
SELECT DISTINCT id
FROM MGMT_CATEGORY_VALUE
START WITH id IN (
-- nested_1 : list only values linked to to given file ID
SELECT value2.id FROM
MGMT_CATEGORY_VALUE value2
,FILE_MANAGEMENT_CATEGORY fmc2
WHERE value2.id = fmc2.MGMT_CATEGORY_VALUE_ID
)
CONNECT BY PRIOR PARENT_ID = id
)
START WITH value.id = :id --value.PARENT_ID IS NULL
CONNECT BY PRIOR value.parent_id = value.id
ORDER BY lvel.hierarchy_level
Q_BUILT_CHART
SELECT
:chart_data_id as data_chart_id,
r.row_number as chart_row_number,
min( decode(ac.column_number, 0, ac.data, null)) col1,
min( decode(ac.column_number, 1, ac.data, null)) col2,
min( decode(ac.column_number, 2, ac.data, null)) col3,
min( decode(ac.column_number, 3, ac.data, null)) col4,
min( decode(ac.column_number, 4, ac.data, null)) col5,
min( decode(ac.column_number, 5, ac.data, null)) col6,
min( decode(ac.column_number, 6, ac.data, null)) col7,
min( decode(ac.column_number, 7, ac.data, null)) col8
FROM
(
SELECT
cell.data as data,
col.column_number as column_number,
cell.chart_data_id as chart_data_id,
cell.chart_row_id as chart_row_id
FROM
chart_column col,
chart_cell cell
WHERE
col.chart_data_id = :chart_data_id
AND cell.chart_column_id(+) = col.id
) ac,
chart_row r
WHERE
r.chart_data_id = :chart_data_id
and ac.chart_row_id(+) = r.id
GROUP BY
r.row_number
ORDER BY
1,2;