[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT level_values_view_name view_name
FROM bisbv_dimension_levels lvl
WHERE lvl.dimension_level_short_name = v_lvl_short_name;
p_viewby_select IN OUT NOCOPY VARCHAR2,
p_viewby_col IN OUT NOCOPY VARCHAR2,
p_hierarchy_condition OUT NOCOPY VARCHAR2,
p_order_by_clause OUT NOCOPY VARCHAR2,
p_group_by_clause OUT NOCOPY VARCHAR2) IS
BEGIN
/* Finish the hierarchy join ('AND fact.column = hrchy.') */
/* Get the viewby column and doctor the viewby display column in the select */
/* clause if the top node is selected. Also doctor the column order to make */
/* the top node appear first if selected. Apply the relevant conditions to */
/* the hierarchy view to include top, enable rollup or include subordinates */
IF (p_viewby_level_code = 'HRI_VIEWBY_ORGH+HRI_DRCTS_ROLLUP_INC' OR
p_viewby_level_code = 'HRI_VIEWBY_SUPH+HRI_DRCTS_ROLLUP_INC') THEN
p_hierarchy_join := p_hierarchy_join || g_subro_prefix || p_hierarchy_col;
p_viewby_select :=
' DECODE(hrchy.' || g_sub_prefix || p_hierarchy_level || ',' || g_rtn ||
' 0, viewby.value || ''' || g_temp_no_rollup || ''',' || g_rtn ||
' viewby.value) VIEWBY';
l_viewby_select VARCHAR2(2000); -- doctored viewby display column
l_select_clause VARCHAR2(2000); -- main SELECT
l_outer_select VARCHAR2(2000); -- allows default time periods
l_union_select VARCHAR2(2000); -- allows default time periods
SELECT
itm.attribute1 column_type
,itm.attribute2 level_code
,lower(itm.attribute3) column_name
,itm.attribute_code item_code
,itm.attribute9 aggregation
,itm.node_display_flag display_flag
,itm.attribute15 lov_table
,itm.attribute4 lov_where_clause
,itm.attribute11 hrchy_view_override
,itm.order_sequence order_sequence
,itm.order_direction order_direction
,lower(reg.database_object_name) object_name
,reg.attribute11 region_where_clause
,itm.display_sequence display_sequence
FROM
ak_region_items itm
,ak_regions reg
WHERE reg.region_code = p_region_code
AND reg.region_application_id = 453
AND itm.region_code = reg.region_code
AND reg.region_application_id = itm.region_application_id
/* Must have hidden parameters first so that hierarchy can be extracted */
/* before other parts of the sql statement are built up. */
/* Calculated measures must be returned after view column measures */
/* which is done by the 3rd column
/* Also depending on HRI_PERIOD_TYPE dimension being extracted before */
/* HRI_REPORT_DATE which is done by the last order by column */
ORDER BY DECODE(itm.attribute1,
'HIDE PARAMETER', 1,
'HIDE VIEW BY DIMENSION', 2,
3)
,DECODE(SUBSTR(itm.attribute_code,1,12),
'HRI_P_HIERAR',1,
'HRI_P_VIEWBY',2,
3)
,DECODE(SUBSTR(itm.attribute3,1,1),'"',2,1)
,itm.attribute2;
/* Default the viewby select column so that if */
/* the value is null a "no value" label is displayed */
l_viewby_select :=
' DECODE(viewby.id, ' || g_rtn ||
' ''-1'', ''' || g_temp_no_value || ''',' || g_rtn ||
' ''NA_EDW'',''' || g_temp_no_value || ''',' || g_rtn ||
' viewby.value) VIEWBY';
/* SELECT CLAUSE BUILD - add all non AK calculated measure columns */
l_select_clause := l_select_clause || g_rtn ||
' ,' || measure_rec.aggregation || '(fact.' || measure_rec.column_name
|| ') "' || measure_rec.item_code || '"';
l_union_select := l_union_select || g_rtn || ' ,0';
l_outer_select := l_outer_select || g_rtn || ' ,SUM(' ||
measure_rec.item_code || ') ' || measure_rec.item_code;
p_viewby_select => l_viewby_select,
p_hierarchy_condition => l_hierarchy_condition,
p_order_by_clause => l_order_by_clause,
p_group_by_clause => l_group_by_clause);
/* If the parameter is a dimension level with values selected */
/* then add it to the WHERE clause */
IF (p_params_tbl(i).parameter_name = measure_rec.level_code AND
p_params_tbl(i).parameter_value <> 'All' AND
/* Bug 4633221 - Translation problem with 'All' */
p_params_tbl(i).parameter_value <> g_all_msg AND
p_params_tbl(i).parameter_value IS NOT NULL AND
SUBSTR(p_params_tbl(i).parameter_name,1,11) <> 'HRI_NO_JOIN') THEN
/* WHERE CLAUSE BUILD - Simple Dimension Parameter Value */
IF (INSTR(measure_rec.column_name,'.') > 0) THEN
l_where_clause := l_where_clause ||
'AND ' || measure_rec.column_name ||
' IN (&' || p_params_tbl(i).parameter_name || ')' || g_rtn;
/* The SELECT clause always picks a VIEWBY column and the list of measure */
/* columns already built up */
l_select_clause := 'SELECT' || g_rtn ||
l_viewby_select ||
l_select_clause || g_rtn;
/* Alter above clauses depending on parameters selected */
/* If the query is restricted by the time parameter, and the TIME level is */
/* different to the VIEWBY level */
IF (l_time_level IS NOT NULL AND
l_time_level <> l_viewby_level AND
(l_time_from_date IS NOT NULL OR l_time_to_date IS NOT NULL)) THEN
IF (l_no_viewby_time_condition IS NOT NULL) THEN
l_where_clause := l_where_clause || l_no_viewby_time_condition;
/* Create an outer SELECT statement so that the query results can be */
/* combined with a set of default values for every time period - this */
/* enables trend reporting */
l_outer_select := 'SELECT' || g_rtn ||
' VIEWBY VIEWBY' ||
l_outer_select || g_rtn ||
'FROM (' || g_rtn;
/* Add the ORDERBY attribute to the SELECT clause to order the results */
/* by time period start date */
l_select_clause := l_select_clause ||
' ,viewby.start_date ORDERBY' || g_rtn;
'SELECT' || g_rtn ||
' viewby.value VIEWBY' ||
l_union_select || g_rtn ||
' ,viewby.start_date ORDERBY' || g_rtn ||
'FROM' || g_rtn ||
' ' || l_viewby_view || ' viewby' || g_rtn ||
'WHERE 1=1' || g_rtn ||
REPLACE(l_viewby_time_condition,'tim','viewby') ||
')' || g_rtn ||
'GROUP BY VIEWBY, ORDERBY' || g_rtn;
/* Move the ORDER BY clause to the outer SELECT statement */
l_order_by_clause := 'ORDER BY ORDERBY';
/* Remove the outer select statement */
l_outer_select := NULL;
l_sql_query := l_outer_select ||
l_select_clause ||
l_from_clause ||
l_where_clause ||
l_group_by_clause ||
l_union_clause ||
l_order_by_clause;
l_select_clause VARCHAR2(2000);
SELECT
itm.attribute1 column_type
,itm.attribute2 level_code
,lower(itm.attribute3) column_name
,itm.attribute_code item_code
,itm.attribute9 aggregation
,itm.node_display_flag display_flag
,itm.attribute15 lov_table
,itm.attribute4 lov_where_clause
,itm.attribute11 hrchy_view_override
,itm.order_sequence order_sequence
,itm.order_direction order_direction
,lower(reg.database_object_name) object_name
FROM
ak_region_items itm
,ak_regions reg
WHERE reg.region_code = p_region_code
AND reg.region_application_id = 453
AND itm.region_code = reg.region_code
AND reg.region_application_id = itm.region_application_id
ORDER BY itm.display_sequence;
/* Build up the select clauses containing all measure columns */
IF (l_select_clause IS NULL) THEN
l_select_clause := ' ( fact.' || measure_rec.column_name || ' ) ' || measure_rec.item_code;
l_select_clause := l_select_clause || g_rtn ||
' ,( fact.' || measure_rec.column_name || ' ) ' || measure_rec.item_code;
l_sql_query := 'SELECT' || g_rtn ||
l_select_clause || g_rtn ||
l_from_clause || g_rtn ||
'WHERE 1=1' || g_rtn ||
l_order_by_clause;
l_viewby_select VARCHAR2(2000); -- doctored viewby display column
l_select_clause VARCHAR2(2000); -- main SELECT
SELECT
reg.region_code region_code
,itm.attribute1 column_type
,itm.attribute2 level_code
,lower(itm.attribute3) column_name
,itm.attribute_code item_code
,itm.attribute9 aggregation
,itm.node_display_flag display_flag
,itm.attribute15 lov_table
,itm.attribute4 lov_where_clause
,itm.attribute11 hrchy_view_override
,itm.order_sequence order_sequence
,itm.order_direction order_direction
,itm.attribute14 column_datatype
,lower(reg.database_object_name) object_name
,reg.attribute11 region_where_clause
,itm.display_sequence display_sequence
FROM
ak_region_items itm
,ak_regions reg
WHERE reg.region_code = p_ak_region_code
AND reg.region_application_id = 453
AND itm.region_code = reg.region_code
AND reg.region_application_id = itm.region_application_id
/* Must have hidden parameters first so that hierarchy can be extracted */
/* before other parts of the sql statement are built up. Also depending */
/* on HRI_PERIOD_TYPE dimension being extracted before HRI_REPORT_DATE */
/* which is done by the second order by column */
/* Must have hidden parameters first so that hierarchy can be extracted */
/* before other parts of the sql statement are built up. */
/* Calculated measures must be returned after view column measures */
/* which is done by the 3rd column
/* Also depending on HRI_PERIOD_TYPE dimension being extracted before */
/* HRI_REPORT_DATE which is done by the last order by column */
ORDER BY DECODE(itm.attribute1,
'HIDE PARAMETER', 1,
'HIDE VIEW BY DIMENSION', 2,
3)
,DECODE(SUBSTR(itm.attribute_code,1,12),
'HRI_P_HIERAR',1,
'HRI_P_VIEWBY',2,
3)
,DECODE(SUBSTR(itm.attribute3,1,1),'"',2,1)
,itm.display_sequence;
/* Default the viewby select column so that if */
/* the value is null a "no value" label is displayed */
l_viewby_select :=
' DECODE(viewby.id, ' || g_rtn ||
' ''-1'', ''' || g_temp_no_value || ''',' || g_rtn ||
' ''NA_EDW'',''' || g_temp_no_value || ''',' || g_rtn ||
' viewby.value) VIEWBY';
/* SELECT CLAUSE BUILD - add all non AK calculated measure columns */
IF (measure_rec.column_datatype = 'C') THEN
l_select_clause := l_select_clause || g_rtn ||
' ,' || measure_rec.aggregation || '(fact.' ||
measure_rec.column_name || ') ' || measure_rec.item_code;
l_select_clause := l_select_clause || g_rtn ||
' ,' || measure_rec.aggregation || '( to_char(fact.' ||
measure_rec.column_name || ')) ' || measure_rec.item_code;
p_viewby_select => l_viewby_select,
p_hierarchy_condition => l_hierarchy_condition,
p_order_by_clause => l_order_by_clause,
p_group_by_clause => l_dummy);
/* If the parameter is a dimension level with values selected */
/* then add it to the WHERE clause */
IF (p_params_tbl(i).parameter_name = measure_rec.level_code AND
p_params_tbl(i).parameter_value <> 'All' AND
/* Bug 4633221 */
p_params_tbl(i).parameter_value <> g_all_msg AND
p_params_tbl(i).parameter_value IS NOT NULL AND
measure_rec.column_name IS NOT NULL AND
SUBSTR(p_params_tbl(i).parameter_name,1,11) <> 'HRI_NO_JOIN') THEN
/* WHERE CLAUSE BUILD - Simple Dimension Parameter Value */
/* Bug 2702283 - Put hierarchy related conditions in l_hierarchy_condition */
IF (INSTR(measure_rec.column_name,'.') > 0) THEN
/* If the drill is from the top level directs rollup */
IF ((l_actl_drll_frm_vwby = 'HRI_VIEWBY_ORGH+HRI_DRCTS_ROLLUP_INC' OR
l_actl_drll_frm_vwby = 'HRI_VIEWBY_SUPH+HRI_DRCTS_ROLLUP_INC') AND
p_params_tbl(i).parameter_id = l_hri_viewby_id) THEN
/* Switch the effective mode to "No Rollup Include Subs" */
l_effct_drll_frm_vwby := SUBSTR(l_actl_drll_frm_vwby,1,16) || 'HRI_ALL_INC';
/* The SELECT clause always picks a VIEWBY column and the list of measure */
/* columns already built up */
l_select_clause := 'SELECT' || g_rtn ||
l_viewby_select ||
l_select_clause || g_rtn;
/* Alter above clauses depending on parameters selected */
/* If the query is restricted by the time parameter, and the TIME level is */
/* different to the VIEWBY level */
IF (l_time_condition IS NOT NULL) THEN
l_where_clause := l_where_clause || l_time_condition;
l_sql_query := l_select_clause ||
l_from_clause ||
l_where_clause ||
l_order_by_clause;