Modifying
region queries/source dynamically can be done easily, whereas building regions
dynamically can be done only using APEX API.
Using
the below code, a static region and a chart region shall be created. Modify the
code based on the requirement of the region type.
This
can also be used as a plugin by uploading the below SQL script in the
application and calling the plugin in the required page with required inputs.
Steps:
Create
a procedure with input parameters as given in the below code.
Code:
CREATE OR REPLACE PROCEDURE test_api_chart_region (
iv_n_template_id IN NUMBER,
iv_n_item_template_id IN NUMBER,
iv_n_page_no IN NUMBER
)
AS
l_static_region_id NUMBER := wwv_flow_id.next_val;
l_region_id NUMBER := wwv_flow_id.next_val;
l_template_id NUMBER := iv_n_template_id; --77632827835402642885;
--give your ID from APEX API tables
l_item_template_id NUMBER := iv_n_item_template_id;
--77632848475788642917;
--give your ID from APEX API tables
l_page_id NUMBER := iv_n_page_no;
--give your page ID
l_chart_id NUMBER := wwv_flow_id.next_val;
l_series_id NUMBER := wwv_flow_id.next_val;
l_xaxis_id NUMBER := wwv_flow_id.next_val;
l_yaxis_id NUMBER := wwv_flow_id.next_val;
l_item_id NUMBER := wwv_flow_id.next_val;
BEGIN
wwv_flow_api.create_page_plug
(p_id => l_static_region_id,
p_flow_id => v ('APP_ID'),
p_page_id => l_page_id,
p_plug_name => 'TestRegion',
p_region_template_options => '#DEFAULT#:t-Region--scrollBody',
p_plug_template => l_template_id,
p_plug_display_sequence => 10,
p_include_in_reg_disp_sel_yn => 'Y',
p_plug_display_point => 'BODY',
p_plug_query_options => 'DERIVED_REPORT_COLUMNS',
p_attribute_01 => 'N',
p_attribute_02 => 'HTML'
);
wwv_flow_api.create_page_plug
(p_id => l_region_id,
p_flow_id => v ('APP_ID'),
p_page_id => l_page_id,
p_plug_name => 'Chart1',
p_region_template_options => '#DEFAULT#:t-Region--scrollBody',
p_escape_on_http_output => 'Y',
p_plug_template => l_template_id,
p_plug_display_sequence => 20,
p_include_in_reg_disp_sel_yn => 'Y',
p_plug_display_point => 'BODY',
p_plug_source => wwv_flow_string.JOIN
(wwv_flow_t_varchar2
('SELECT NULL LINK,',
' deptno LABEL,',
' SAL VALUE',
'',
'FROM EMP',
'where SAL between 1000 and
3000'
/*,
'and MGR = :P2000_X'*/
)
),
p_plug_source_type => 'NATIVE_JET_CHART',
p_plug_query_num_rows => 15,
p_plug_query_options => 'DERIVED_REPORT_COLUMNS'
);
wwv_flow_api.create_jet_chart (p_id => l_chart_id,
p_region_id => l_region_id,
p_chart_type => 'bar',
p_animation_on_display => 'none',
p_animation_on_data_change => 'none',
p_orientation => 'vertical',
p_data_cursor => 'auto',
p_data_cursor_behavior => 'auto',
p_hide_and_show_behavior => 'none',
p_hover_behavior => 'none',
p_stack => 'off',
p_zoom_and_scroll => 'off',
p_tooltip_rendered => 'Y',
p_show_series_name => TRUE,
p_show_group_name => TRUE,
p_show_value => TRUE,
p_show_label => TRUE,
p_legend_rendered => 'on',
p_legend_position => 'auto'
);
wwv_flow_api.create_jet_chart_series
(p_id => l_series_id,
p_chart_id => l_chart_id,
p_seq => 10,
p_name => 'New',
p_data_source_type => 'REGION_SOURCE',
p_items_value_column_name => 'VALUE',
p_items_label_column_name => 'LABEL',
p_assigned_to_y2 => 'off',
p_items_label_rendered => FALSE
);
wwv_flow_api.create_jet_chart_axis
(p_id => l_xaxis_id,
p_chart_id => l_chart_id,
p_axis => 'x',
p_is_rendered => 'on',
p_format_scaling => 'auto',
p_scaling => 'linear',
p_baseline_scaling => 'zero',
p_major_tick_rendered => 'on',
p_minor_tick_rendered => 'off',
p_tick_label_rendered => 'on',
p_tick_label_rotation => 'auto',
p_tick_label_position => 'outside'
);
wwv_flow_api.create_jet_chart_axis
(p_id => l_yaxis_id,
p_chart_id => l_chart_id,
p_axis => 'y',
p_is_rendered => 'on',
p_format_scaling => 'auto',
p_scaling => 'linear',
p_baseline_scaling => 'zero',
p_position => 'auto',
p_major_tick_rendered => 'on',
p_minor_tick_rendered => 'off',
p_tick_label_rendered => 'on'
);
END;
Call the procedure by passing the
required parameters in the page on required event.
Output:
Hello,
ReplyDeleteGreat post! I am see the programming coding and step by step execute the outputs.I am gather this coding more information. It's helpful for me. Also great blog here with all of the valuable information you have. Oracle EBS Training
I just do not really know how to get l_item_template_id and the other parm needed, could you help me please
ReplyDeleteAre you sure we can use wwv_* packages and tables? As per oracle they are undocumented and can be changed any time. Do you have any better way of doing it using apex_* packages?
ReplyDelete