eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIS_PMV_QUERY_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 687


l_viewby_select VARCHAR2(32000);

Line: 688


l_column_select VARCHAR2(32000);

Line: 689


l_target_select VARCHAR2(32000);

Line: 690


l_select   VARCHAR2(32000) := ' SELECT ';

Line: 706


SELECT attribute1 disable_viewby,
       attribute6 user_groupby,
       attribute7 user_orderby,
       database_object_name source_view,
       region_object_type report_type,
       attribute8 plsql_function,
       attribute10 data_source,
       attribute11 where_clause
FROM   AK_REGIONS
WHERE  region_code = cpRegionCode;

Line: 729


SELECT attribute2,
       attribute3 base_column,
       attribute4 where_clause,
       attribute14 data_type
FROM   AK_REGION_ITEMS
WHERE  region_code = cpRegionCode
AND nested_region_code is null
AND    (nvl(attribute2, attribute_code) = cpParameterName
        or attribute2||'_FROM' = cpParameterName
        or attribute2||'_TO' = cpParameterName)
ORDER BY display_sequence;

Line: 745


SELECT attribute1 attribute_type,
       attribute_code,
       attribute2,
       attribute3 base_column,
       attribute4 where_clause,
       attribute15 lov_table,
       attribute9 aggregate_function,
       attribute14 data_type,
       attribute7 data_format,
       order_sequence,
       order_direction,
       node_query_flag
       ,node_display_flag    -- 2371922
FROM   AK_REGION_ITEMS
WHERE  region_code = cpRegionCode
AND nested_region_code is null
ORDER BY display_sequence;

Line: 785


SELECT attribute16 extra_groupby
FROM   BIS_AK_REGION_ITEM_EXTENSION
WHERE  region_code = cpRegionCode
AND    attribute_code = cpAttributeCode;

Line: 795


SELECT distinct attribute3 base_column, attribute9 aggregation_function
FROM AK_REGION_ITEMS
WHERE region_code = p_region_code
AND attribute9 is not null
AND nested_region_code is null
AND substr(attribute3,1,1) not in ('''','"');

Line: 809


l_select_string VARCHAR2(2000);

Line: 1084


           BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING
           (p_DimLevelShortName => l_time_dimension_level,
            p_bis_source => l_report_type,
            x_select_string => l_select_string,
            x_table_name => l_time_table,
            x_id_name => l_time_id_name,
            x_value_name => l_time_value_name,
            x_return_status => l_return_status,
            x_msg_count => l_msg_count,
            x_msg_data => l_msg_data);

Line: 1112


        BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING
        (p_DimLevelShortName => l_viewby_dimension_level,
         p_bis_source => l_report_type,
         x_select_string => l_select_string,
         x_table_name => l_viewby_table,
         x_id_name => l_viewby_id_name,
         x_value_name => l_viewby_value_name,
         x_return_status => l_return_status,
         x_msg_count => l_msg_count,
         x_msg_data => l_msg_data);

Line: 1145


            l_viewby_select := 'to_char';

Line: 1147


	  l_viewby_select := l_viewby_select || '(VBT.' || l_viewby_value_name || ') "VIEWBY", ';

Line: 1149


           l_viewby_select := l_viewby_select || ' (VBT.' || l_viewby_id_name ||') "VIEWBYID", ';

Line: 1163


           l_viewby_select := l_viewby_select || 'SV.' || l_extra_groupby_name || ' EXTRAVIEWBY, ';

Line: 1221


           l_column_select := l_column_select || BIS_PMV_QUERY_PVT.GET_CALCULATE_SELECT(l_ak_region_item_rec, l_parameter_tbl, l_base_column_tbl,l_aggregation_tbl);

Line: 1224


        l_column_select := l_column_select || BIS_PMV_QUERY_PVT.GET_NORMAL_SELECT(l_ak_region_item_rec);

Line: 1239


        BIS_PMV_QUERY_PVT.GET_TARGET_SELECT
                          (p_user_session_rec => l_user_session_rec,
                           p_ak_region_item_rec => l_ak_region_item_rec,
                           p_parameter_tbl => l_parameter_tbl,
                           p_report_type => l_report_type,
                           p_plan_id => l_plan_id,
                           p_viewby_dimension => l_viewby_dimension,
                           p_viewby_attribute2 => l_viewby_attribute2,
                           p_viewby_id_name => l_viewby_id_name,
                           p_time_from_description => l_time_from_description,
                           p_time_to_description => l_time_to_description,
                           x_target_select => l_target_select,
                           x_no_target => l_no_target,
                           x_bind_variables => x_bind_variables,
                           --x_bind_indexes => x_bind_indexes,
                           x_bind_count => l_bind_count);

Line: 1256


        if l_target_select is not null and length(l_target_select) > 0 then
           if length(l_ak_region_item_rec.attribute_code) > 23 then
              l_target_alias_name := 'target'||l_target_count;

Line: 1268


           l_target_select := l_target_select || ' "'|| l_target_alias_name || '", ';

Line: 1271


        l_column_select := l_column_select || l_target_select;

Line: 1302


  l_select := l_select || l_viewby_select || l_column_select;

Line: 1304


  if substr(l_select, length(l_select)-1) = ', ' then
     l_select := substr(l_select, 1, length(l_select)-2);

Line: 1309


     l_select := l_select || ', ' || l_user_groupby || ' ';

Line: 1313


     l_select := l_select || ', ' || l_user_orderby || ' ';

Line: 1532


    x_sql := l_select || l_from || l_where || l_group_by || l_main_order_by;

Line: 1534


    x_sql := l_select || l_from || l_where || l_group_by || l_order_by;

Line: 1601


function GET_NORMAL_SELECT(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC)
return varchar2 is
  l_select_string varchar2(2000);

Line: 1605


  l_select_string := p_ak_region_item_rec.aggregate_function || '('
  || BIS_PMV_QUERY_PVT.APPLY_DATA_FORMAT(p_ak_region_item_rec) || ') "'
  || p_ak_region_item_rec.attribute_code || '", ';

Line: 1608


  return l_select_string;

Line: 1609


end GET_NORMAL_SELECT;

Line: 1633


function GET_CALCULATE_SELECT(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC
,p_parameter_tbl  in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE
,p_base_column_tbl in out NOCOPY BISVIEWER.t_char
,p_aggregation_tbl in out NOCOPY BISVIEWER.t_char)
return varchar2 is
  l_calculate_select varchar2(2000);

Line: 1641


     l_calculate_select := substr(p_ak_region_item_rec.base_column,2,length(p_ak_region_item_rec.base_column)-2)
     || ' "'||p_ak_region_item_rec.attribute_code||'", ';

Line: 1646


     l_calculate_select := BIS_PMV_QUERY_PVT.REPLACE_FORMULA(p_ak_region_item_rec,
				p_parameter_tbl,
				p_base_column_tbl,
				p_aggregation_Tbl)
     || ' "'||p_ak_region_item_rec.attribute_code||'", ';

Line: 1652


  return l_calculate_select;

Line: 1653


end GET_CALCULATE_SELECT;

Line: 1742


 x_Ordered_Dimension_Select OUT NOCOPY VARCHAR2,
 x_target_level_id OUT NOCOPY NUMBER,
 x_no_target OUT NOCOPY BOOLEAN,
 x_bind_variables In OUT NOCOPY VARCHAR2,
 --x_bind_indexes In OUT NOCOPY VARCHAR2,
 x_bind_count in out NOCOPY NUMBER
)
IS

  l_parameter_rec BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;

Line: 1759


  l_Ordered_Dimension_Select VARCHAR2(2000) := '';

Line: 1808


 SELECT level_id
 FROM bis_levels_vl
 WHERE short_name=p_dim_level_short_name;

Line: 1844


               			/* l_dimension_select := l_dimension_select || '''' || l_parameter_rec.dimension || ''','''
						|| l_dimension_level || ''',';*/

Line: 1853


                        	--l_Dimension_Select := l_dimension_select || l_parameter_Rec.parameter_value;

Line: 1856


	                       -- l_dimension_select := l_dimension_select || ''''||l_parameter_rec.parameter_value||'''';

Line: 1860


	                --l_dimension_select := l_dimension_select || ',';

Line: 2521


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select||' :'||x_bind_count;

Line: 2526


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2534


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2541


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2543


  	x_Ordered_Dimension_Select  :=  x_Ordered_Dimension_Select  ||',' ||lDIm1LevelValue;

Line: 2550


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2555


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2563


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2570


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2572


  	x_Ordered_Dimension_Select  :=  x_Ordered_Dimension_Select  ||',' ||lDIm2LevelValue;

Line: 2579


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2584


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2592


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2599


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2601


  	x_Ordered_Dimension_Select  :=  x_Ordered_Dimension_Select  ||',' ||lDIm3LevelValue;

Line: 2608


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2613


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2620


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2627


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2629


  	x_Ordered_Dimension_Select  :=  x_Ordered_Dimension_Select  ||',' ||lDIm4LevelValue;

Line: 2636


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2641


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2648


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2655


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2657


  	x_Ordered_Dimension_Select  :=  x_Ordered_Dimension_Select  ||',' ||lDIm5LevelValue;

Line: 2665


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2670


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2677


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2684


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2686


  	x_Ordered_Dimension_Select  :=  x_Ordered_Dimension_Select  ||',' ||lDIm6LevelValue;

Line: 2693


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2698


x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2705


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2713


    x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2716


  	    x_Ordered_Dimension_Select  :=  x_Ordered_Dimension_Select  ||',' ||lDIm7LevelValue;

Line: 2721


      x_Ordered_Dimension_Select := x_Ordered_Dimension_Select ||', :'||x_bind_count;

Line: 2735


procedure GET_TARGET_SELECT(p_user_session_rec in BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
                            p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC,
                            p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE,
                            p_report_type in VARCHAR2,
                            p_plan_id in VARCHAR2,
                            p_viewby_dimension in VARCHAR2,
                            p_viewby_attribute2 in VARCHAR2,
                            p_viewby_id_name in VARCHAR2,
                            p_time_from_description in VARCHAR2,
                            p_time_to_description in VARCHAR2,
                            x_target_select out NOCOPY VARCHAR2,
                            x_no_target out NOCOPY boolean,
                            x_bind_variables IN OUT NOCOPY VARCHAR2,
                            --x_bind_indexes IN OUT NOCOPY VARCHAR2,
                            x_bind_count IN OUT NOCOPY NUMBER) is
  l_target_select varchar2(2000);

Line: 2752


  l_dimension_select varchar2(2000);

Line: 2776


	l_dimension_select,
	l_target_level_id,
	l_no_target,
        l_bind_variables,
        --l_bind_indexes,
        x_bind_count
  );

Line: 2789


    l_target_select := l_target_select || 'BIS_PMV_PMF_PVT.get_target_new(';

Line: 2795


    l_target_select := l_target_select ||' :'||x_bind_count;

Line: 2800


    l_target_select := l_target_select ||', :'||x_bind_count;

Line: 2805


    l_target_select := l_target_select ||', :'||x_bind_count;

Line: 2810


    l_target_select := l_target_select ||', :'||x_bind_count;

Line: 2815


    l_target_select := l_target_select ||', :'||x_bind_count;

Line: 2820


    l_target_select := l_target_select ||', :'||x_bind_count;

Line: 2825


    l_target_select := l_target_select ||', :'||x_bind_count ;

Line: 2827


    if l_dimension_select is not null and length(l_dimension_select) > 0 then
        l_target_select := l_target_select || ',' || l_dimension_select;

Line: 2833


    l_target_select := l_target_select || ')';

Line: 2837


  x_target_select := l_target_select;

Line: 2840


end GET_TARGET_SELECT;

Line: 3304


     l_user_function := 'SELECT ' || substr(p_user_string, 2, length(p_user_string)-2) || ' FROM DUAL';

Line: 3342


    select property_varchar2_value,property_name,attribute_code
    from ak_custom_region_items_vl
    where property_name in (cp_property1,cp_property2)
    and region_code =  p_region_code
    and customization_code = cp_cust_code
    and attribute_code = cp_attribute_code
    order by property_name;

Line: 3442


SELECT attribute1 attribute_type,
       attribute_code,
       attribute2,
       attribute3 base_column,
       attribute4 where_clause,
       attribute15 lov_table,
       attribute9 aggregate_function,
       attribute14 data_type,
       attribute7 data_format,
       order_sequence,
       order_direction,
       node_query_flag
       ,node_display_flag --2371922
FROM   AK_REGION_ITEMS
WHERE  region_code = cpRegionCode
AND nested_region_code is null
AND node_query_flag = 'N'
AND ( order_direction IS NOT NULL OR order_sequence IS NOT NULL ) --Bug Fix 2605121
ORDER BY display_sequence;

Line: 3464


SELECT attribute1 attribute_type,
       attribute_code,
       attribute2,
       attribute3 base_column,
       attribute4 where_clause,
       attribute15 lov_table,
       attribute9 aggregate_function,
       attribute14 data_type,
       attribute7 data_format,
       order_sequence,
       order_direction,
       node_query_flag
       ,node_display_flag --2371922
FROM   AK_REGION_ITEMS
WHERE  region_code = cpRegionCode
AND nested_region_code is null
AND node_query_flag = 'N'
ORDER BY display_sequence;

Line: 3714


   SELECt description
   FROM fnd_lookup_values_vl
   WHERE lookup_Type = p_lookup_type and
   (lookup_code||'_FROM' = p_Parameter_name or
   lookup_code||'_TO' = p_Parameter_name);

Line: 3745


    BIS_PMV_PARAMETERS_PVT.DELETE_SCHEDULE_PARAMETER
    (p_parameter_name       => 'VIEW_BY'
    ,p_schedule_id      => l_user_session_rec.schedule_id
    ,x_return_status        => l_return_Status
    ,x_msg_count            => l_msg_count
    ,x_msg_data     => l_msg_Data
    );

Line: 3753


    BIS_PMV_PARAMETERS_PVT.DELETE_PARAMETER( p_user_session_rec =>l_user_session_rec
                                          ,p_parameter_name     => 'VIEW_BY'
                                          ,p_schedule_option  =>'NULL'
                                          ,x_return_status      =>l_return_status
                                          ,x_msg_count => l_msg_count
                                          ,x_msg_data   => l_msg_data );

Line: 3773


     insert into bis_user_attributes  (user_id, function_name,
                                      session_id, schedule_id, attribute_name,
                                      session_value, session_description,
                                      creation_date, created_by,
                                      last_update_Date, last_updated_by)
                              VALUES (l_user_session_rec.user_id, l_user_session_rec.function_name,
                                      l_user_session_rec.session_id, l_user_session_rec.schedule_id,
                                      l_parameter_rec.parameter_name,
                                      pViewByParam, pViewbyParam,
                                      sysdate, -1, sysdate, -1);

Line: 3889


   SELECT attribute2,
       attribute3 base_column,
       attribute4 where_clause,
       attribute14 data_type
   FROM   AK_REGION_ITEMS
   WHERE  region_code = cpRegionCode
   AND    (nvl(attribute2, attribute_code) = cpParameterName
        or attribute2||'_FROM' = cpParameterName
        or attribute2||'_TO' = cpParameterName)
ORDER BY display_sequence;

Line: 3916


    l_page_parameter_tbl.delete;