xxen_api

clear

procedure clear

Removes the parameter values and runtime options set in this session without a run id. Call it before setting the values of a new run.

create_run_id

function create_run_id(p_report_id in pls_integer) return number

Creates a run of a report and returns its run id, by report id or, with create_run_id_, by report name in the session language. Values set in the session without a run id are copied into the run; create_run_id_ discards those of the report first. Parameter default values are not evaluated: set every parameter the report SQL depends on, hidden parameters included.

set_parameter_value

procedure set_parameter_value(p_parameter_id in pls_integer, p_value in clob, p_run_id in number default null)

Sets the value of a parameter for a run (p_run_id), or for the session when p_run_id is null. A second call for the same parameter adds a value, which makes it a multiple value selection. Pass the stored value: the id for a list of values with an id column, else the displayed value, and dates as DD-MON-YYYY. set_parameter_value_ identifies the parameter by its name in p_language (default the session language, then US). With a run id the value is committed in an autonomous transaction.

set_runtime_option

procedure set_runtime_option(p_runtime_option_name in varchar2, p_value in varchar2, p_run_id in number default null)

Sets a runtime option by name: TEMPLATE_NAME, OUTPUT_FORMAT, OUTPUT_FILE_NAME, EMAIL, SERVER_OUTPUT_DIRECTORY or SERVER_FILE_NAME. For a concurrent submission set the options without run id and call report_submit_concurrent with p_run_id null.

default_template

function default_template(p_report_id in pls_integer) return varchar2

Returns the name of the current user's default template of a report.

run_report

procedure run_report(p_run_id in number, x_row_count out nocopy pls_integer, x_output_file out nocopy blob, x_filename out nocopy varchar2, x_return_status out nocopy varchar2, x_msg_data out nocopy varchar2)

Runs a report in the current database session for a run prepared with create_run_id and set_parameter_value, and returns the output file, its file name and the row count. x_return_status is S or E, x_msg_data the completion message. Initialize the session for an EBS user and responsibility first (fnd_global.apps_initialize); the report runs with that responsibility's security.

report_file_id

function report_file_id(p_run_id in number) return varchar2

Runs a report like run_report and stores the output through the EBS generic file manager (fnd_gfm), returning the file id to download it in the browser, for example from a button of a custom form. Returns the completion message instead when the run fails or produces no output.

report_submit_concurrent

function report_submit_concurrent(p_report_id in pls_integer, p_run_id in out nocopy number, x_request_id out nocopy number) return varchar2

Submits a report as a Blitz Report concurrent request and returns null, or the error message. With p_run_id null the request takes the parameter values and runtime options set in the session; report_submit_concurrent_ submits a report by name with its saved defaults only. Commit to release the request to the concurrent managers.

multiple_values_allowed

function multiple_values_allowed(p_parameter_id in pls_integer) return varchar2 result_cache

Returns 'Y' when a parameter accepts multiple values.

report_id

function report_id(p_report_name in varchar2) return pls_integer

Returns the report id of a report or upload name in the session language.

display_request_param_value

function display_request_param_value(p_application_id in number, p_concurrent_program_name in varchar2, p_parameter_number in number, p_id in varchar2) return varchar2 result_cache

Translate a concurrent request's argument into the visible value

display_request_param_values

function display_request_param_values(
p_application_id in pls_integer,
p_concurrent_program_name in varchar2,
p_delimiter in varchar2,
p_argument1 in varchar2,
p_argument2 in varchar2,
p_argument3 in varchar2,
p_argument4 in varchar2,
p_argument5 in varchar2,
p_argument6 in varchar2,
p_argument7 in varchar2,
p_argument8 in varchar2,
p_argument9 in varchar2,
p_argument10 in varchar2,
p_argument11 in varchar2,
p_argument12 in varchar2,
p_argument13 in varchar2,
p_argument14 in varchar2,
p_argument15 in varchar2,
p_argument16 in varchar2,
p_argument17 in varchar2,
p_argument18 in varchar2,
p_argument19 in varchar2,
p_argument20 in varchar2,
p_argument21 in varchar2,
p_argument22 in varchar2,
p_argument23 in varchar2,
p_argument24 in varchar2,
p_argument25 in varchar2
) return clob

Translate a concurrent request's argument into the visible value

function display_request_param_values(p_row_id in rowid, p_delimiter in varchar2) return clob

Translate a concurrent request's arguments into a visible value text

import_concurrent_program

procedure import_concurrent_program(
p_application_short_name in varchar2,
p_concurrent_program_name in varchar2,
p_category in varchar2 default null,
x_report_id out nocopy pls_integer,
x_message out nocopy varchar2
)

Import API for BI Publisher, Excel4apps Reports Wand and other Concurrent Programs

import_discoverer_folders

procedure import_discoverer_folders(
p_eul in varchar2,
p_accessed_after in date,
p_object_use_key in varchar2,
p_expand_views in varchar2 default null,
p_include_columns in varchar2 default null,
p_category in varchar2 default null,
x_report_id out nocopy pls_integer,
x_message out nocopy varchar2
)

Import API for discoverer folder combinations from execution history eul_us.eul5_qpp_stats

import_reporting_workbench

procedure import_reporting_workbench(
p_report_id in pls_integer,
p_expand_view_sqls in varchar2,
p_category in varchar2 default null,
x_report_id out nocopy pls_integer,
x_message out nocopy varchar2
)

Import API for Polaris Reporting Workbench reports

import_ecc_dataset

procedure import_ecc_dataset(
p_dataset_key in varchar2,
p_category in varchar2 default null,
x_status out nocopy varchar2,
x_message out nocopy varchar2)

Import API for Enterprise Command Center SQLs

export_file_data

function export_file_data(
p_type in varchar2,
p_ids in fnd_table_of_number default null,
p_include_lovs in varchar2 default null,
p_include_categories in varchar2 default null,
p_include_assignments in varchar2 default null,
p_include_templates in varchar2 default null,
p_include_columns in varchar2 default null,
p_creation_date in date default null,
p_language in varchar2 default null,
p_sql_mode in varchar default null
) return clob

Export API for reports or other object data as xml or sql scripts

export_report

function export_report(
p_report_id in pls_integer,
p_include_lovs in varchar2 default null,
p_include_categories in varchar2 default null,
p_include_assignments in varchar2 default null,
p_include_templates in varchar2 default null,
p_include_columns in varchar2 default null,
p_language in varchar2 default null
) return clob

Export API for individual reports

export_report_

function export_report_(
p_report_id in pls_integer,
p_include_lovs in varchar2 default null,
p_include_categories in varchar2 default null,
p_include_assignments in varchar2 default null,
p_include_templates in varchar2 default null,
p_include_columns in varchar2 default null,
p_language in varchar2 default null
) return clob

Export API for individual reports, slower version which can be used in SQL statements

export_template

function export_template(p_template_id in pls_integer) return clob

Export API for individual Templates

export_lov

function export_lov(p_lov_id in number) return clob

Export API for individual LOVs

import_xml

procedure import_xml(p_xml in clob)

Imports reports, uploads, templates, lists of values and categories from an XML document in the format the export functions and the setup form produce. Commits.

delete_parameter

procedure delete_parameter(p_parameter_id in pls_integer)

Deletes a report parameter. Does not commit.

delete_report

procedure delete_report(p_report_id in pls_integer, p_delete_history in varchar2 default null)

Deletes a report or upload with its translations, parameters, templates, assignments and category assignments. p_delete_history='Y' also deletes its SQL version history and its runs. Does not commit.

delete_lov

procedure delete_lov(p_lov_id in pls_integer, p_delete_history in varchar2 default null)

Deletes a list of values. p_delete_history='Y' also deletes its query version history. Does not commit.

mass_change

procedure mass_change(
p_text_old in varchar2,
p_text_new in varchar2,
p_category in varchar2 default null,
p_report_id in pls_integer default null,
p_parameter_id in pls_integer default null,
p_lov_id in pls_integer default null,
p_change_report_sql in varchar2 default 'Y',
p_change_parameter_sql_text in varchar2 default 'Y',
p_change_param_cust_lov_query in varchar2 default 'Y',
p_change_parameter_default_val in varchar2 default 'Y',
p_change_upld_col_cust_lov_qry in varchar2 default 'Y',
p_change_upld_col_val_to_id_qr in varchar2 default 'Y',
p_change_upld_col_default_val in varchar2 default 'Y',
p_change_upld_column_comments in varchar2 default 'Y',
p_change_upld_excel_validation in varchar2 default 'Y',
p_change_lov_query in varchar2 default 'Y',
p_change_lov_value_to_id_query in varchar2 default 'Y',
p_change_lov_name in varchar2 default null,
p_match_case in varchar2 default 'Y',
p_regex_mode in varchar2 default null,
p_comments in varchar2 default null
)

Replaces a text in the SQL of reports, parameters, upload columns and lists of values, optionally restricted to a category, report, parameter or list of values; the p_change_ flags select which SQL texts are changed. p_regex_mode='Y' takes p_text_old and p_text_new as a regular expression and its replacement. Every changed report and list of values gets a new version with comment p_comments.

create_parameter

procedure create_parameter(
p_report_id in pls_integer,
p_display_sequence in pls_integer default null,
p_parameter_name in varchar2,
p_description in varchar2 default null,
p_source_lang in varchar2 default xxen_util.base_language,
p_anchor in varchar2,
p_sql_text in clob default null,
p_parameter_type in varchar2 default null,
p_lov_id in pls_integer default null,
p_free_text in varchar2 default null,
p_filter_before_display in varchar2 default null,
p_lov_query in clob default null,
p_matching_value in varchar2 default null,
p_default_value in varchar2 default null,
p_required in varchar2 default null,
x_parameter_id out pls_integer
)

Adds a parameter to a report in all installed languages and returns its id. p_anchor is the SQL anchor (a 1=1 style predicate anchor, a lexical or a bind), p_sql_text the text the anchor is replaced with, p_parameter_type the stored type code, p_lov_id a shared list of values. Dependencies on other parameters are not derived; to create complete definitions use import_xml. Does not commit.

parameter_value

function parameter_value(p_run_id in number, p_parameter_id in pls_integer, p_id_or_value in varchar2 default 'value') return clob

Returns the value of a parameter of a run: p_id_or_value 'value' gives the displayed value, 'id' the stored value. Multiple values are separated by semicolons.

parameters

function parameters(p_run_id in number) return clob

Returns all parameter values of a run as text, one line per parameter in display order, as 'name: value'.

save_user_profile

procedure save_user_profile(p_profile_option_name in varchar2, p_value in varchar2, p_default_value in varchar2 default null)

Sets a profile option at user level for the current user. A value equal to what the user gets without a user level value (responsibility, application or site, else p_default_value) removes the user level value instead. Commits in an autonomous transaction.

user_profile_value

function user_profile_value(p_profile_option_name in varchar2) return varchar2

Returns a profile option value for the current user and responsibility.

save_user_preference

procedure save_user_preference(p_preference_name in varchar2, p_value in varchar2, p_default_value in varchar2 default null, p_module_name in varchar2 default 'XXEN_REPORT')

Saves a user preference of the current user (fnd_user_preferences, module XXEN_REPORT by default). A value equal to p_default_value removes the preference. save_user_preference_num stores a number in canonical format. Commits in an autonomous transaction.

user_preference

function user_preference(p_preference_name in varchar2, p_module_name in varchar2 default 'XXEN_REPORT') return varchar2

Returns a user preference of the current user, cached per session; a change made in another session is visible after ten seconds. user_preference_num returns it as a number.