xxen_util

base_language

function base_language return varchar2 deterministic

Language code of the EBS base language, for example US.

nls_language_iso_code

function nls_language_iso_code return varchar2

ISO code of the database instance's nls_language parameter, for example en-US. It reads the instance parameter, not the session language.

trunc_sysdate

function trunc_sysdate return date

Returns trunc(sysdate), for SQL with a result_cache hint, which is not cached when it references sysdate directly.

fnd_message

function fnd_message(p_message_name in varchar2, p_application_short_name in varchar2 default null) return varchar2

Text of an FND message in the session language, falling back to the base language and then to the message name. p_application_short_name defaults to the Blitz Report application. Message tokens are not substituted.

meaning

function meaning(p_lookup_code in varchar2, p_lookup_type in varchar2, p_application_id in varchar2, p_code_if_null in varchar2 default null) return varchar2

Translated meaning of a lookup code in the session language, from fnd_lookup_values, or null when there is none. p_application_id is the lookup's view application id, for example 0 for FND, 222 for AR and 660 for OM lookups. p_code_if_null 'Y' returns the code itself when no meaning exists. Results are cached, so it is cheap per row. Fixed Assets lookups are kept in fa_lookups and are not found.

description

function description(p_lookup_code in varchar2, p_lookup_type in varchar2, p_application_id in varchar2) return varchar2

Description of a lookup code in the session language, falling back to the base language.

lookup_code

function lookup_code(p_meaning in varchar2, p_lookup_type in varchar2, p_application_id in varchar2, p_meaning_if_null in varchar2 default null) return varchar2

Lookup code for a translated meaning in the session language, the reverse of meaning. p_meaning_if_null 'Y' returns the meaning itself when no code is found.

yes

function yes(p_lookup_code in varchar2) return varchar2

Translated Yes for 'Y' and null for any other value, to show a flag column as Yes or blank.

regexp_escape

function regexp_escape(p_text in varchar2) return varchar2

Escapes the regular expression metacharacters of a text with a backslash, for example ( to \(, so that regexp functions match it literally.

json_translate

function json_translate(p_text in varchar2) return varchar2

Prepares a text for the Blitz Report webservice row format: removes control characters other than tab, line feed and carriage return, and replaces / with ~^, | with `^ and \ with @^.

json_escape

function json_escape(p_text in varchar2) return varchar2

Escapes a text for use inside a JSON string (backslash, double quote and control characters), without adding the quotes. chr(0) is removed.

function json_escape(p_clob in clob) return clob

Escapes a clob of any length for use inside a JSON string, as the varchar2 version does.

contains

function contains(p_parameter_value in varchar2, p_column_value in varchar2) return varchar2

Returns 'Y' when p_column_value is one of the values in the semicolon separated list p_parameter_value, otherwise null. Use it in a dependent LOV query, xxen_util.contains(:$flex$.parameter,column)='Y', so that a multiple values parent parameter filters the list. The list may carry the <multiple_values> prefix. When it contains %, its values are matched with like.

init_cap

function init_cap(p_text in varchar2) return varchar2

Initcap of an upper case name of more than three characters without blanks, for example PROJECT to Project, to turn flexfield prompts into column headings. Any other text is returned unchanged.

profile_option_value_sql

function profile_option_value_sql(
p_application_id in number,
p_profile_option_id in number,
p_translated_only in varchar2 default null
) return varchar2 result_cache

Select statement with the columns profile_option_value and visible_option_value, derived from a profile option's SQL validation. p_translated_only 'Y' returns null when the validation shows the stored value unchanged.

display_profile_option_value

function display_profile_option_value(
p_application_id in number,
p_profile_option_id in number,
p_profile_option_value in varchar2
) return varchar2

User visible value of a stored profile option value, for example the responsibility name for a responsibility id. Returns the stored value when the option has no list or the value is not in it.

profile_option_value_lov

function profile_option_value_lov(
p_application_id in number,
p_profile_option_id in number,
p_lov_delimter in varchar2 default '~'
) return fnd_table_of_varchar2_4000 pipelined

Pipelined list of a profile option's valid values, from its SQL validation. Each row is the stored value and the visible value, separated by p_lov_delimter. A validation that cannot run outside the profile form returns no rows.

display_flexfield_context

function display_flexfield_context(
p_application_id in number,
p_descriptive_flexfield_name in varchar2,
p_context_code in varchar2
) return varchar2

Name of a descriptive flexfield context, or the code when the flexfield has no context of that code.

display_flexfield_value

function display_flexfield_value(
p_application_id in number,
p_descriptive_flexfield_name in varchar2,
p_context_code in varchar2,
p_column_name in varchar2,
p_rowid in rowid default null,
p_value in varchar2 default chr(0),
p_business_group_id in number default null,
p_organization_id in number default null,
p_org_information_id in number default null,
p_display_mode in varchar2 default null
) return varchar2

Display value of a descriptive flexfield segment, translating the stored value of a validated segment, for example an id, into what the user sees. Pass the stored value in p_value, or the row's rowid in p_rowid to read it from the flexfield's table. The rowid also supplies the parent and other segment values that dependent value sets need. p_display_mode null returns the value, 'D' the description and 'C' the value followed by ': ' and the description. p_business_group_id, p_organization_id and p_org_information_id feed value sets that reference them. On an error the stored value is returned.

dff_attribute_value_to_id

function dff_attribute_value_to_id(
p_application_id in number,
p_descriptive_flexfield_name in varchar2,
p_context_code in varchar2,
p_column_name in varchar2,
p_attribute1 in varchar2 default null,
p_attribute2 in varchar2 default null,
p_attribute3 in varchar2 default null,
p_attribute4 in varchar2 default null,
p_attribute5 in varchar2 default null,
p_attribute6 in varchar2 default null,
p_attribute7 in varchar2 default null,
p_attribute8 in varchar2 default null,
p_attribute9 in varchar2 default null,
p_attribute10 in varchar2 default null,
p_attribute11 in varchar2 default null,
p_attribute12 in varchar2 default null,
p_attribute13 in varchar2 default null,
p_attribute14 in varchar2 default null,
p_attribute15 in varchar2 default null,
p_attribute16 in varchar2 default null,
p_attribute17 in varchar2 default null,
p_attribute18 in varchar2 default null,
p_attribute19 in varchar2 default null,
p_attribute20 in varchar2 default null,
p_attribute21 in varchar2 default null,
p_attribute22 in varchar2 default null,
p_attribute23 in varchar2 default null,
p_attribute24 in varchar2 default null,
p_attribute25 in varchar2 default null,
p_attribute26 in varchar2 default null,
p_attribute27 in varchar2 default null,
p_attribute28 in varchar2 default null,
p_attribute29 in varchar2 default null,
p_attribute30 in varchar2 default null,
p_recursion_path in varchar2 default null
) return varchar2

Stored value of a descriptive flexfield segment for its display value, the reverse of display_flexfield_value, for example to pass an uploaded value to an API. p_column_name is the segment column, for example ATTRIBUTE5. p_attribute1 to p_attribute30 carry the display values of all segments, which dependent value sets need. A date is returned in canonical format, a segment without a list returns its display value. p_recursion_path is for internal use.

dff_attribute_lov

function dff_attribute_lov(
p_application_id in number,
p_descriptive_flexfield_name in varchar2,
p_context_name in varchar2,
p_column_name in varchar2,
p_lov_delimter in varchar2 default '~',
p_attribute1 in varchar2 default null,
p_attribute2 in varchar2 default null,
p_attribute3 in varchar2 default null,
p_attribute4 in varchar2 default null,
p_attribute5 in varchar2 default null,
p_attribute6 in varchar2 default null,
p_attribute7 in varchar2 default null,
p_attribute8 in varchar2 default null,
p_attribute9 in varchar2 default null,
p_attribute10 in varchar2 default null,
p_attribute11 in varchar2 default null,
p_attribute12 in varchar2 default null,
p_attribute13 in varchar2 default null,
p_attribute14 in varchar2 default null,
p_attribute15 in varchar2 default null,
p_attribute16 in varchar2 default null,
p_attribute17 in varchar2 default null,
p_attribute18 in varchar2 default null,
p_attribute19 in varchar2 default null,
p_attribute20 in varchar2 default null,
p_attribute21 in varchar2 default null,
p_attribute22 in varchar2 default null,
p_attribute23 in varchar2 default null,
p_attribute24 in varchar2 default null,
p_attribute25 in varchar2 default null,
p_attribute26 in varchar2 default null,
p_attribute27 in varchar2 default null,
p_attribute28 in varchar2 default null,
p_attribute29 in varchar2 default null,
p_attribute30 in varchar2 default null
) return fnd_table_of_varchar2_4000 pipelined

Pipelined list of values of a descriptive flexfield segment, for upload column LOVs. Each row is the value and the description, separated by p_lov_delimter. p_context_name takes the context name or code. p_attribute1 to p_attribute30 carry the display values of the segments, which dependent value sets need. A value in the segment's own attribute restricts the list to values starting with its first two characters.

kff_segment_lov

function kff_segment_lov(
p_application_id in number,
p_id_flex_code in varchar2,
p_id_flex_num in number,
p_application_column_name in varchar2,
p_lov_delimter in varchar2 default '~',
p_enabled_only in varchar2 default 'Y',
p_segment1 in varchar2 default null,
p_segment2 in varchar2 default null,
p_segment3 in varchar2 default null,
p_segment4 in varchar2 default null,
p_segment5 in varchar2 default null,
p_segment6 in varchar2 default null,
p_segment7 in varchar2 default null,
p_segment8 in varchar2 default null,
p_segment9 in varchar2 default null,
p_segment10 in varchar2 default null,
p_segment11 in varchar2 default null,
p_segment12 in varchar2 default null,
p_segment13 in varchar2 default null,
p_segment14 in varchar2 default null,
p_segment15 in varchar2 default null,
p_segment16 in varchar2 default null,
p_segment17 in varchar2 default null,
p_segment18 in varchar2 default null,
p_segment19 in varchar2 default null,
p_segment20 in varchar2 default null,
p_segment21 in varchar2 default null,
p_segment22 in varchar2 default null,
p_segment23 in varchar2 default null,
p_segment24 in varchar2 default null,
p_segment25 in varchar2 default null,
p_segment26 in varchar2 default null,
p_segment27 in varchar2 default null,
p_segment28 in varchar2 default null,
p_segment29 in varchar2 default null,
p_segment30 in varchar2 default null
) return fnd_table_of_varchar2_4000 pipelined

Pipelined list of values of a key flexfield segment, for example an accounting flexfield segment, for report and upload LOVs. It covers every value set validation type, so a table validated segment returns its values from the validation table. Each row is id, value and description, separated by p_lov_delimter. p_enabled_only 'Y' lists enabled values only. Rows are not sorted. p_segment1 to p_segment30 carry the other segments' values, which dependent value sets need. A value in the requested segment's own position restricts the list to values starting with its first two characters. Values the current responsibility's flexfield value security excludes are left out.

concatenated_segments

function concatenated_segments(p_code_combination_id in number) return varchar2

Concatenated segments of a GL code combination. Cached per session; in a report over many code combinations joining gl_code_combinations_kfv is cheaper.

segment_description

function segment_description(
p_segment_value in varchar2,
p_column_name in varchar2,
p_id_flex_num in pls_integer,
p_parent_segment_value in varchar2 default null,
p_application_id in pls_integer default 101,
p_id_flex_code in varchar2 default 'GL#'
) return varchar2

Description of a key flexfield segment value, by default of the accounting flexfield, in full length. p_column_name is the segment column, for example SEGMENT3, and p_id_flex_num the structure, for the accounting flexfield the chart of accounts id. A value of a dependent value set is found only with p_parent_segment_value. Returns null when the value does not exist.

segments_description

function segments_description(
p_id in number,
p_id_flex_num in pls_integer,
p_application_id in pls_integer default 101,
p_id_flex_code in varchar2 default 'GL#'
) return varchar2

Descriptions of all segments of a key flexfield combination, one line per segment in the form prompt: description. p_id is the combination id, for example code_combination_id, and p_id_flex_num the structure, for the accounting flexfield the chart of accounts id.

function segments_description(p_code_combination_id in number) return varchar2

Segment descriptions of a GL code combination, as the other version, with the chart of accounts looked up from the combination. Slower than passing it.

reverse

function reverse(p_text in varchar2, p_delimiter in varchar2) return varchar2

Reverses the element order of a delimited path, for example /gordon/supervisor/project to /project/supervisor/gordon. Useful to show sys_connect_by_path in reverse order without changing the start of the connect by query. The text must start with the delimiter, as sys_connect_by_path returns it. Otherwise the first element is lost.

clob_to_blob

function clob_to_blob(p_clob in clob, p_charset_id in number default null) return blob

Converts a clob to a blob in AL32UTF8, or in the character set p_charset_id (see nls_charset_id). Returns null for an empty clob.

blob_to_clob

function blob_to_clob(p_blob in blob, p_charset_id in number default null) return clob

Converts a blob encoded in AL32UTF8, or in the character set p_charset_id, to a clob. A UTF-8 byte order mark is removed. Useful for example to read text files stored in the database, such as the SQL of BI Publisher data templates.

blob_is_utf8

function blob_is_utf8(p_blob in blob, p_charset_id in number default null) return varchar2

Returns 'Y' when a blob converts to the database character set without inconvertible characters, otherwise null. Use it to check that an uploaded file has the expected encoding, as blob_to_clob replaces such characters silently.

long_to_clob

function long_to_clob(p_table_name in varchar2, p_column_name in varchar2, p_row_id in rowid) return clob

Value of a LONG column as a clob, for the row p_row_id of table p_table_name.

blob_to_base64

function blob_to_base64(p_blob in blob) return clob

Base64 encoding of a blob. On a Unix database server a line break follows about every 32767 characters, so each line fits a SQL*Plus line.

base64_to_blob

function base64_to_blob(p_clob in clob) return blob

Decodes base64 text to a blob. Line breaks in the text are ignored.

clob_lengthb

function clob_lengthb(p_clob in clob) return number

Length of a clob in bytes, counted in AL32UTF8 encoding.

clob_substrb

function clob_substrb(p_clob in clob, p_byte_length in pls_integer, p_char_position in pls_integer default 1) return clob

Part of a clob of at most p_byte_length bytes, starting at character position p_char_position, up to 32767 bytes. Use dbms_lob.substr(xxen_util.clob_substrb(clob,4000,1)) to read a clob as a varchar2 of up to 4000 bytes in SQL without ORA-06502 on multibyte data.

replace_first_occurrence

function replace_first_occurrence(p_source in clob, p_search in clob, p_replacement in clob) return clob

Replaces the first occurrence of p_search in a clob. Search and replacement may be clobs of any length.

clob_replace

function clob_replace(p_source in clob, p_search in clob, p_replacement in clob) return clob

Replaces all occurrences of p_search in a clob. It repeats until the search text no longer occurs, so it never ends when the replacement contains the search text: use replace_first_occurrence for that.

instring

function instring(p_text in clob, p_separator in varchar2, p_occurrence in pls_integer) return varchar2 deterministic

Element number p_occurrence of a text delimited by p_separator, trimmed, or null when there are fewer elements. Faster than regexp_substr on long multiple value lists, especially on clobs.

rowgen

function rowgen(p_rows in number) return fnd_table_of_number deterministic

Generates p_rows rows in SQL, numbered from 1 in column_value, for example table(xxen_util.rowgen(n)) rowgen to split a delimited list.

data_type_class

function data_type_class(p_data_type in pls_integer) return varchar2

Class of a dbms_sql column data type code: varchar2, number, date, clob or rowid.

sql_columns

function sql_columns(p_sql in clob, p_skip_binding in varchar2 default null) return xxen_sql_desc_tab

Columns of a SQL statement, which is parsed and described but not executed, for example select x.* from table(xxen_util.sql_columns('select * from hz_parties')) x. A statement that does not parse returns one row with column_name '---- error ----' and the error in data_type. By default the Number and Date binds of the current Blitz Report run are bound with dummy values; p_skip_binding 'Y' skips that.

remove_empty_lines

function remove_empty_lines(p_clob in clob) return clob

Removes empty and blank lines from a text, for example a SQL statement.

html_to_text

function html_to_text(p_html in clob, p_remove_empty_lines in varchar2 default null) return clob

Converts HTML formatted text to plain text: line and block element ends become line breaks, all other tags are removed and entities decoded. p_remove_empty_lines 'Y' removes all empty lines, otherwise only leading and trailing ones.

zero_to_null

function zero_to_null(p_number in number) return number

Returns null for 0 and the number otherwise, the same as nullif(p_number,0), for example to avoid ORA-01476 divisor is equal to zero.

time

function time(p_seconds in number) return varchar2 deterministic

Duration text for a number of seconds, for example 90061 to 1d 1h 1m 1s. Fractions of a second are truncated.

module_type

function module_type(p_module in varchar2, p_action in varchar2 default null) return varchar2 result_cache

Type of an EBS database session, from v$session module and action: Concurrent Request, Concurrent Manager, Form, Oracle Application Framework, Workflow, Business Event, Blitz Report or Discoverer, for example Concurrent Request for the module e:per:cp:pay/pyuslv.

module_name

function module_name(p_module in varchar2, p_program in varchar2 default null) return varchar2

Display name of what an EBS database session runs, from its v$session module, for example Payroll Worker Process for e:per:cp:pay/pyuslv. Covers concurrent programs and managers, forms, workflow item types, business events, Blitz Reports and Discoverer users. p_program is the v$session program, used as fallback.

responsibility

function responsibility(p_module in varchar2, p_action in varchar2) return varchar2

Responsibility name of an EBS database session, from its v$session action, for example System Administrator for SYSADMIN/SYSTEM_ADMINISTRATOR.

user_name

function user_name(p_user_name in varchar2) return varchar2

User name followed by the user's description in brackets, for example CLAUDIA (Claudia Renner). The description is the employee name, else the user description, else the email address, and is shown unless profile Blitz Report Show User Description is No.

function user_name(p_user_id in pls_integer, p_show_description in varchar2 default null) return varchar2

User name for a user id, with its description as user_name(p_user_name) shows it. p_show_description 'N' returns the user name only.

user_id

function user_id(p_user_name in varchar2) return pls_integer result_cache

User id for a user name, or null when the user does not exist.

function user_name(p_module in varchar2, p_action in varchar2, p_client_id in varchar2) return varchar2 result_cache

User name of an EBS database session, from its v$session client_identifier. For a concurrent request or business event session whose client identifier is a request id, it returns the user who submitted the request.

is_xml

function is_xml(p_clob in clob) return varchar2

Returns 'Y' when a clob is well formed XML, otherwise the parser error stack.

ap_invoice_status

function ap_invoice_status(
p_invoice_id in number,
p_invoice_amount in number,
p_payment_status_flag in varchar2,
p_invoice_type_lookup_code in varchar2,
p_validation_request_id in number
) return varchar2

Translated invoice status as the Payables Invoice Workbench shows it, for example Validated, Selected for Payment or Needs Revalidation. Pass the columns of ap_invoices_all. Faster than the version with the invoice id only.

function ap_invoice_status(p_invoice_id in number) return varchar2

Translated invoice status as the Payables Invoice Workbench shows it, read from ap_invoices_all for the invoice id.

wsh_next_step

function wsh_next_step(
p_container_flag in wsh_delivery_details.container_flag%type,
p_released_status in wsh_delivery_details.released_status%type,
p_source_code in wsh_delivery_details.source_code%type,
p_inv_interfaced_flag in wsh_delivery_details.inv_interfaced_flag%type,
p_oe_interfaced_flag in wsh_delivery_details.oe_interfaced_flag%type,
p_client_id in number,
p_replenishment_status in varchar2,
p_move_order_line_id in wsh_delivery_details.move_order_line_id%type
) return varchar2

Translated next step of a delivery detail, as the Shipping Transactions form shows it, for example Pick Release or Ship Confirm/Close Trip Stop. Pass the columns of wsh_delivery_details (client_id and replenishment_status are null on EBS 11i). Faster than the version with the delivery detail id only.

function wsh_next_step(p_delivery_detail_id in number) return varchar2

Translated next step of a delivery detail, as the Shipping Transactions form shows it, read from wsh_delivery_details for the delivery detail id.

client_time

function client_time(p_date in date) return date deterministic

Converts a server date to the user's client time zone, when profile Enable Timezone Conversions is Yes and the two time zones differ.

server_time

function server_time(p_date in date) return date deterministic

Converts a date in the user's client time zone to the server time zone, when profile Enable Timezone Conversions is Yes and the two time zones differ.

canonical_to_displaydate

function canonical_to_displaydate(p_canonical_value in varchar2) return varchar2

Converts a canonical date text (YYYY/MM/DD [HH24:MI:SS]) to the user's display date format. Text that is not a canonical date, such as the expression sysdate-30, is returned unchanged.

canonical_to_displaydt

function canonical_to_displaydt(p_canonical_value in varchar2) return varchar2

Converts a canonical date text (YYYY/MM/DD [HH24:MI:SS]) to the user's display date and time format, for DateTime parameters. Text that is not a canonical date is returned unchanged.

application_name

function application_name(p_application_short_name in varchar2) return varchar2 result_cache

Application name for an application short name. AP, GL and FA are accepted for SQLAP, SQLGL and OFA.

application_short_name_trans

function application_short_name_trans(p_application_short_name in varchar2) return varchar2 deterministic

Short name Blitz Report shows for an application: AP, GL and FA for SQLAP, SQLGL and OFA, null for DUMMY. Used for report short names.

application_short_name

function application_short_name(p_application_name in varchar2) return varchar2 result_cache

Application short name for an application name, with AP, GL and FA for SQLAP, SQLGL and OFA, and Blitz for Blitz Report.

dis_eul_usage_count

function dis_eul_usage_count(p_eul varchar2) return number

Discoverer migration: number of worksheet executions logged in an EUL's eul5_qpp_stats within the import history period.

dis_business_area

function dis_business_area(p_obj_id in number, p_eul in varchar2) return varchar2

Discoverer migration: business areas of a folder, comma separated.

dis_folder_type

function dis_folder_type(p_obj_type in varchar2) return varchar2 deterministic

Discoverer migration: display name of a folder type, Standard, Complex view or Custom SQL.

dis_item_type

function dis_item_type(p_exp_type in varchar2) return varchar2 deterministic

Discoverer migration: display name of an expression type, Condition, Calculated Item, Item or Join Predicate.

dis_default_eul

function dis_default_eul return varchar2

Discoverer migration: the default EUL, from profile Blitz Report Discoverer Default EUL, else the EUL with the most executions.

dis_user

function dis_user(p_user_id in number, p_eul in varchar2) return varchar2 result_cache

Discoverer migration: EUL user name for an EUL user id.

dis_user_name

function dis_user_name(p_username in varchar2, p_show_description in varchar2 default null, p_eul in varchar2 default null) return varchar2

Discoverer migration: the EBS user name or responsibility name for a Discoverer user name.

function dis_user_name(p_user_id in number, p_eul in varchar2, p_show_description in varchar2 default null) return varchar2 result_cache

Discoverer migration: the EBS user name or responsibility name for an EUL user id.

dis_user_type

function dis_user_type(p_username in varchar2) return varchar2 result_cache

Discoverer migration: type of a Discoverer user name, User, Responsibility or Database.

function dis_user_type(p_user_id in number, p_eul in varchar2) return varchar2 result_cache

Discoverer migration: type of an EUL user id, User, Responsibility or Database.

dis_folder_sql

function dis_folder_sql(p_object_id in number, p_eul in varchar2) return clob

Discoverer migration: SQL text of a custom SQL or complex folder.

dis_folder_sql2

function dis_folder_sql2(p_object_id in number, p_eul in varchar2) return clob

Discoverer migration: SQL text of a custom SQL or complex folder, as an inline view without the outer select and apps. prefixes.

dis_folder_sql3

function dis_folder_sql3(p_object_id in number, p_eul in varchar2) return clob

Discoverer migration: SQL text of a custom SQL or complex folder, as select * from the folder's inline view.

dis_lov_query

function dis_lov_query(p_exp_id in pls_integer, p_eul in varchar2) return clob

Discoverer migration: LOV query for a Discoverer item, to create a Blitz Report LOV.

dis_worksheet_sql

function dis_worksheet_sql(p_workbook_owner_name in varchar2, p_workbook_name in varchar2, p_worksheet_name in varchar2) return clob

Discoverer migration: SQL of a worksheet, from the logging table ams_discoverer_sql.

dis_folder_exists

function dis_folder_exists(p_eul in varchar2) return varchar2

Discoverer migration: 'Y' when an EUL has execution statistics in eul5_qpp_stats from which folders can be imported.

dis_qs_exp_ids

function dis_qs_exp_ids(p_qs_id in number, p_eul in varchar2, p_type in varchar2 default null) return clob

Discoverer migration: ids of the items an eul5_qpp_stats record used, comma separated.

dis_obj_type

function dis_obj_type(p_obj_id in varchar2, p_eul in varchar2) return varchar2 result_cache

Discoverer migration: object type of a folder.

dis_obj_id

function dis_obj_id(p_object_key in varchar2, p_eul in varchar2) return number result_cache

Discoverer migration: object id of a folder for its identifier.

dis_exp_id

function dis_exp_id(p_object_key in varchar2, p_item_name in varchar2, p_eul in varchar2) return pls_integer result_cache

Discoverer migration: expression id of a folder item.

dis_exp_data_type

function dis_exp_data_type(p_object_key in varchar2, p_item_name in varchar2, p_eul in varchar2) return varchar2 result_cache

Discoverer migration: data type of a folder item, Number, Date or Char.

compile_designator

function compile_designator(p_org_id in pls_integer) return varchar2

Default MRP plan of an organization: the plan named in profile MRP_DEFAULT_PLAN when it plans the organization, otherwise the first by name. Only completed MRP plans that are not disabled are considered.

calendar_date_offset

function calendar_date_offset(p_date in date, p_calendar_code in varchar2, p_offset_days in number default 0, p_calendar_exception_set_id in number default -1) return date

Date p_offset_days working days from p_date in manufacturing calendar p_calendar_code (bom_calendar_dates). A start date that is not a working day counts from the next working day for a positive offset and from the previous one for a negative offset. With offset 0 it returns the date itself, or the next working day.

sequence_nextval

function sequence_nextval(p_sequence_name in varchar2) return number

Next value of a sequence. Use it in the insert branch of a merge, where seq.nextval would use up a value for every updated row too. The name is concatenated into dynamic PL/SQL, so pass a sequence name only, never user input.

bcp47_language

function bcp47_language(p_language_code in varchar2) return varchar result_cache

BCP 47 code for an Oracle language code, for example en-GB for GB or zh-Hans for ZHS.

geolocation

function geolocation(p_service_url in varchar2, p_ip_address in varchar2, p_path in varchar2, p_wallet_path in varchar2 default null) return varchar2 result_cache

Location information, such as country or city, of an IP address from a geolocation web service, cached per address. The service is called with p_service_url followed by the IP address. For an XML response p_path is the XPath of the value, for JSON the name of a top level field. p_wallet_path is the Oracle wallet with the service's root certificate for an https URL. The database needs a network ACL for the service.

parameter_value

function parameter_value(p_parameter_name in varchar2 default null, p_parameter_bind in varchar2 default null) return varchar2

Value of a parameter of the Blitz Report running in the current concurrent request, for PL/SQL in a before report trigger, for example xxen_util.parameter_value('Operating Unit'), with the English parameter name, or by bind name with p_parameter_bind. Multiple values are returned as <multiple_values>A;B. Returns null outside a concurrent request.

orig_order_line_id

function orig_order_line_id(p_split_from_line_id in number) return number

Id of the original order line a split line derives from, following split_from_line_id up to the first line. A line that was never split returns its own id. A function is needed because the optimizer does not execute this connect by correctly inside a larger query.

default_ledger

function default_ledger return varchar2

Default ledger name for the current user: the single primary ledger of the responsibility's data access set, or with several, the one of the default operating unit. Returns null when no single ledger qualifies. On EBS 11i it is the set of books of profile GL Set of Books ID.

default_operating_unit

function default_operating_unit return varchar2

Default operating unit name for the current user: the only operating unit the session's MOAC access (restricted to profile MO: Default Operating Unit when set) contains. Returns null when there are several or none, or MOAC is not initialized.

previous_parameter_value

function previous_parameter_value(p_parameter_id in pls_integer) return varchar2

Value of a parameter in the user's latest run of its report in the current responsibility within the last 60 days, for parameter defaults.

dff_columns

function dff_columns(
p_table_name in varchar2,
p_table_alias in varchar2 default null,
p_descr_flex_context_code in varchar2 default null,
p_column_name_prefix in varchar2 default null,
p_prefix in varchar2 default null,
p_hide_column_name in varchar2 default null,
p_row_id in varchar2 default null,
p_regional_localization in varchar2 default null,
p_org_id in number default null,
p_display_mode in varchar2 default null,
p_scalar_subquery in varchar2 default 'Y'
) return clob

Select list text with one column per descriptive flexfield segment of a table, for a lexical parameter in report SQL, preceded by the context column. A segment prompt used in several contexts becomes one column, headed by the prompt. p_table_name is the flexfield's application table and p_table_alias its alias in the SQL. p_descr_flex_context_code restricts it to one context, p_column_name_prefix prefixes the headings, p_hide_column_name 'Y' omits the aliases (for a group by) and p_prefix replaces each expression. p_display_mode null shows values, 'C' value and description, 'S' an additional description column. p_row_id is the rowid column if not rowid. p_regional_localization 'Y' returns the global descriptive flexfield of the default operating unit's country (or p_org_id's) instead. Every column ends with a comma, so place the lexical before another column of the outermost select.

item_category_columns

function item_category_columns(p_category_set_name in varchar2, p_table_alias in varchar2 default 'msiv', p_item_id_column in varchar2 default 'inventory_item_id', p_org_id_column in varchar2 default 'organization_id', p_column_name_prefix in varchar2 default null) return clob

Select list text with one column per category segment of category set p_category_set_name, followed by the category description, for a lexical parameter in report SQL. The arguments name the item table alias and its item and organization id columns. An item with several categories in the set shows its first. Every column ends with a comma, so place the lexical before another column of the outermost select.

wait_for_request

function wait_for_request(p_conc_request_id in number, x_status out varchar2, x_message out varchar2) return varchar2

Waits for a concurrent request to complete and returns Success when it completed Normal or Warning, otherwise Error. x_status returns the request status and x_message its completion message. It waits without a time limit.

functional_currency_code

function functional_currency_code(p_ledger_name in varchar2) return varchar2

Functional currency of a ledger.

latest_open_period

function latest_open_period(p_ledger_name in varchar2) return varchar2

Latest period of a ledger that has been opened, whether it is still open or closed since.

segment_name

function segment_name(p_ledger_name in varchar2, p_segment_prop_value in varchar2, p_segment_num in number) return varchar2

Blitz FSG: display name 'prompt (number)' of an accounting segment of a ledger's chart of accounts, by display name, else by segment number.

ledger_id

function ledger_id(p_ledger_name in varchar2) return number result_cache

Ledger id for a ledger name. On EBS 11i the set of books id.

coa_id

function coa_id(p_ledger_name in varchar2) return number result_cache

Chart of accounts id of a ledger.

has_flex_value_security

function has_flex_value_security return varchar2

Returns 'Y' when the current responsibility has flexfield value security rules, otherwise null. Profile Blitz Report Disable GL Flex Value Security set to Yes always returns null.

flex_value_accessible

function flex_value_accessible(p_resp_appl_id in number, p_resp_id in number, p_flex_value_set_id in number, p_flex_value in varchar2) return varchar2

Flexfield value security: returns 'Y' when the responsibility may access a value of a value set, null when its security rules exclude it. It applies the rules as the EBS flexfield engine does, hierarchical security included, on EBS 11i and R12. Results are cached per session. Call it only for segments with Security Enabled: a value set shared with an unsecured segment is not secured there.

flex_ccid_accessible

function flex_ccid_accessible(p_resp_appl_id in number, p_resp_id in number, p_code_combination_id in number) return varchar2

Flexfield value security for a whole GL code combination: returns 'Y' when every security enabled segment value is accessible, otherwise null. Use it in place of gl_security_pkg. Results are cached per session.

xml_escape

function xml_escape(p_text in varchar2) return varchar2

Escapes a text for XML: replaces ampersand, less than, greater than and double quote by entities and control characters by blanks, and cuts the result to 4000 bytes. A value written as ="text" is unwrapped first.

xml_unescape

function xml_unescape(p_text in varchar2) return varchar2

Replaces the XML entities for ampersand, less than, greater than and double quote by the characters.

xml_unescape_clob

function xml_unescape_clob(p_text in clob) return clob

Replaces the XML entities for ampersand, less than, greater than and double quote by the characters, in a clob.

xml_attribute_value

function xml_attribute_value(p_vchr in varchar2) return varchar2

Escapes a text for XML element content: replaces ampersand, less than and greater than by entities and control characters other than tab, line feed and carriage return by blanks. A value written as ="text" is unwrapped first.

remove_order_by

function remove_order_by(p_text in clob) return clob

Removes the order by clause from the end of a SQL statement. Only the first order by of the text is considered, so a statement with an earlier one, such as in an analytic function, is returned unchanged.

is_ebs_database

function is_ebs_database return boolean

True when the database holds Oracle EBS (the APPLSYS schema exists).

jai_installed

function jai_installed return varchar2 result_cache

Returns 'Y' when India Localization (JAI) is installed and its party registration API is available, otherwise null.

concurrent_request_file_url

function concurrent_request_file_url(p_file_type in varchar2, p_request_id in number) return varchar2

Temporary URL to view a concurrent request's output (p_file_type 'O') or log file ('L') without logging in.

colname

function colname(p_col in pls_integer) return varchar2

Excel column letters for a column number, for example 3 to C.

colnum

function colnum(p_column_name in varchar2) return pls_integer

Excel column number for upper case column letters, for example C to 3.

date_to_excel_date

function date_to_excel_date(p_date in date) return varchar2 deterministic

Excel serial date number of a date, as text with a period as decimal separator, for example 45658 for 01-Jan-2025.

excel_date_to_date

function excel_date_to_date(p_excel_date in varchar2) return date deterministic

Date for an Excel serial date number, passed as text with a period as decimal separator.

file_content_type

function file_content_type(p_file_name in varchar2) return varchar2

MIME type for a file name by its extension, application/octet-stream when the extension is not known.

object_ddl

function object_ddl(p_object_type in varchar2, p_object_name in varchar2, p_owner in varchar2) return clob

DDL of a database object, rebuilt from the dba_ data dictionary views: packages and other source, views, tables, sequences, synonyms, indexes, materialized views and database links. Returns null for other object types and on error.