Commands(s)
Q_MEASUREMENT
-- *******************************************************************************************************************************
-- 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
Q_PARENT_ENTITY
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
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
, 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
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'
*/
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
Q_LEGENDS
----------------------------------------------------------
------------ 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
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
, 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
Q_FILE
-- 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(+)
Q_FILE_UNITS
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
Q_MNGMNT_CATEGORIES
-- 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
Q_FILE_STRATEGY
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
Q_SUBMISSION
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'
Q_files_to_sub_item
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
Q_strategies_to_sub_item
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
Q_STRATEGY
-- 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(+)
Q_RELATED_MEASUREMENTS
/*
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
Q_REL_ACTIVITY_NOTES
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
Q_DRIVER
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
)
);
Q_strategy_units
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
Q_Submission_Lead_Units
-- 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
Q_Submission_Affected_Units
-- 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
Q_CHART_ATTRIBUTES
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
Q_CHART
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;