The
supported functions are shown in the following table:
SQL Statement or XSL
Expression
|
Usage
|
Description
|
2+3
|
<?xdofx:2+3?>
|
Addition
|
2-3
|
<?xdofx:2-3?>
|
Subtraction
|
2*3
|
<?xdofx:2*3?>
|
Multiplication
|
2/3
|
<?xdofx:2/3?>
|
Division
|
2**3
|
<?xdofx:2**3?>
|
Exponential
|
3||2
|
<?xdofx:3||2?>
|
Concatenation
|
lpad('aaa',10,'.')
|
<?xdofx:lpad('aaa',10,'.')?>
|
The lpad function pads
the left side of a string with a specific set of characters. The syntax for
the lpad function is:
lpad(string1,padded_length,[pad_string]) string1 is the string to pad characters to (the left-hand side). padded_length is the number of characters to return. pad_string is the string that will be padded to the left-hand side of string1 . |
rpad('aaa',10,'.')
|
<?xdofx:rpad('aaa',10,'.')?>
|
The rpad function pads
the right side of a string with a specific set of characters.
The syntax for the rpad function is: rpad(string1,padded_length,[pad_string]). string1 is the string to pad characters to (the right-hand side). padded_length is the number of characters to return. pad_string is the string that will be padded to the right-hand side of string1 |
trim()
|
<?xdoxslt:trim(‘ a
‘)?>
|
Removes spaces in a
string. Enter the text to be trimmed, the function returns the trimmed text.
|
ltrim()
|
<?xdoxslt:ltrim(‘ a
‘)?>
|
Removes the leading
white spaces in a string.
|
rtrim()
|
<?xdoxslt:rtrim(‘ a
‘)?>
|
Removes the trailing
white spaces in a string.
|
truncate
|
<?xdoxslt:truncate
( number [, integer ] )?>
|
The truncate function
returns number truncated to integer places right
of the decimal point. Ifinteger is omitted, then number is
truncated to the whole integer value. integer can be
negative to truncate values left of the decimal point. integer must
be an integer.
Example: <?xdoxslt:truncate(-2.3333)?> returns -2 Example: <?xdoxslt:truncate(2.7777, 2)?> returns 2.77 Example: <?xdoxslt:truncate(27.7777, -1)?> returns 20 |
replicate
|
<?xdoxslt:replicate(‘string’, integer)?>
|
The replicate function
will replicate the specified string the specified number of times.
Example: <?xdoxslt:replicate(‘oracle’, 3)?> returns oracleoracleoracle |
decode('xxx','bbb','ccc','xxx','ddd')
|
<?xdofx:decode('xxx','bbb','ccc','xxx','ddd')?>
|
The decode function
has the functionality of an IF-THEN-ELSE statement. The syntax for the decode
function is:
decode(expression, search, result [,search, result]...[, default]) expression is the value to compare. search is the value that is compared against expression. result is the value returned, if expression is equal to search. default is returned if no matches are found. |
Instr('abcabcabc','a',2)
|
<?xdofx:Instr('abcabcabc','a',2)?>
|
The instr function
returns the location of a substring in a string. The syntax for the instr
function is:
instr(string1,string2,[start_position],[nth_appearance]) string1 is the string to search. string2 is the substring to search for in string1. start_position is the position in string1 where the search will start. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1. nth appearance is the nth appearance of string2. |
substr('abcdefg',2,3)
|
<?xdofx:substr('abcdefg',2,3)?>
|
The substr function
allows you to extract a substring from a string. The syntax for the substr
function is:
substr(string, start_position, length) string is the source string. start_position is the position for extraction. The first position in the string is always 1. length is the number of characters to extract. |
left
|
<?xdoxslt:left(‘abcdefg’,
3)?>
|
Enables you to extract
the specified number of characters from a string, starting from the left. The
syntax is left(string, Numchars)
For example, <?xdoxslt:left(‘abcdefg’, 3)?> returns abc |
right
|
<?xdoxslt:right(‘abcdefg’,
3)?>
|
Enables you to extract
the specified number of characters from a string, starting from the right.
The syntax is right(string, Numchars)
For example, <?xdoxslt:right(‘abcdefg’, 3)?> returns efg |
replace(name,'John','Jon')
|
<?xdofx:replace(name,'John','Jon')?>
|
The replace function
replaces a sequence of characters in a string with another set of characters.
The syntax for the replace function is:
replace(string1,string_to_replace,[replacement_string]) string1 is the string to replace a sequence of characters with another set of characters. string_to_replace is the string that will be searched for in string1. replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. |
to_number('12345')
|
<?xdofx:to_number('12345')?>
|
Function to_number
converts char, a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype
containing a number in the format specified by the optional format model fmt,
to a value of NUMBER datatype.
|
format_number
|
<?xdoxslt:format_number(12345, n,
$_XDOLOCALE)?>
|
Converts a number to a
string and formats the number according to the locale specified in$_XDOLOCALE and
to the number of decimal positions specified in n using
Java's default symbols. For example:
<?xdoxslt:format_number(-12345, 2, ‘fr-FR’)?> returns -12 345,00 |
format_number
|
<?xdoxslt:format_number(12345, n, s1,s2,$_XDOLOCALE)?>
|
Converts a number to a
string and uses the specified separators: s1 for the thousand separator and
s2 for the decimal separator. For example:
<?xdoxslt:format_number(12345, 2, 'g', 'd', $_XDOLOCALE)?> returns 12g345d00 |
pat_format_number
|
<?xdoxslt:pat_format_number(12345,
‘##,##0.00’, $_XDOLOCALE)?>
|
Returns a number
formatted with the specified pattern.
For example: <?xdoxslt:pat_format_number(12345, ‘##,##0.00’, $_XDOLOCALE)?> returns 12,345.00 |
to_char(12345)
|
<?xdofx:to_char('12345')?>
|
Use the TO_CHAR
function to translate a value of NUMBER datatype to VARCHAR2 datatype.
|
to_date
|
<?xdofx:to_date (
char [, fmt [, 'nlsparam']] )
|
TO_DATE converts char
of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype.
The fmt is a date format specifying the format of char.
If you omitfmt, then char must be in the default date
format. If fmt is 'J', for Julian, then char must
be an integer.
|
sysdate()
|
<?xdofx:sysdate()?>
|
SYSDATE returns the
current date and time. The datatype of the returned value is DATE. The
function requires no arguments.
|
current_date()
|
<?xdoxslt:current_date($_XDOLOCALE,
$_XDOTIMEZONE)?>
Example: <?xdoxslt:current_date('ja-JP', 'Asia/Tokyo')?> |
Returns the current
date in "yyyy-MM-dd" format in the given locale and timezone. This
function supports only the Gregorian calendar.
|
current_time()
|
<?xdoxslt:current_time($_XDOLOCALE,
$_XDOTIMEZONE)?>
Example: <?xdoxslt:current_time('ja-JP', 'Asia/Tokyo')?> |
Returns the current
time in the given locale and timezone. This function supports only the
Gregorian calendar.
|
minimum
|
<?xdoxslt:minimum(ELEMENT_NAME)?>
|
Returns the minimum
value of the element in the set.
|
date_diff
|
<?xdoxslt:date_diff(‘y',
‘YYYY-MM-DD’, ‘YYYY-MM-DD’, $_XDOLOCALE, $_XDOTIMEZONE)?>
|
This function provides
a method to get the difference between two dates in the given locale. The
dates need to be in "yyyy-MM-dd" format. This function supports only
the Gregorian calendar. The syntax is as follows:
<?xdoxslt:date_diff(‘format’, ‘YYYY-MM-DD’, ‘YYYY-MM-DD’, $_XDOLOCALE, $_XDOTIMEZONE)?> where format is the time value for which the difference is to be calculated. Valid values are :
Example: <?xdoxslt:date_diff(‘y’, ‘2000-04-08’, ‘2001-05-01’, $_XDOLOCALE, $_XDOTIMEZONE)?> returns 1 Example: <?xdoxslt:date_diff(‘m’, ‘2001-04-08’, ‘2000-02-01’, $_XDOLOCALE, $_XDOTIMEZONE)?> returns -14 Example: <?xdoxslt:date_diff(‘d’, ‘2006-04-08’, ‘2006-04-01’, $_XDOLOCALE, ‘America/Los_Angeles’)?> returns -7 |
sec_diff
|
<?xdoxslt:sec_diff(‘2000-04-08T20:00:00’,
‘2000-04-08T21:00:00’, $_XDOLOCALE, $_XDOTIMEZONE?>
|
This function provides
a method to get the difference between two dates in seconds in the given
locale. The dates need to be in "yyyy-MM-dd'T'HH:mm:ss". This
function supports only Gregorian calendar.
Example: <?xdoxslt:sec_diff(‘2000-04-08T20:00:00’, ‘2000-04-08T21:00:00’, $_XDOLOCALE, $_XDOTIMEZONE?> returns 3600 |
get_day
|
<?xdoxslt:get_day(‘2000-04-08’,
$_XDOLOCALE)?>
|
This function provides
a method to get the day value of a date in "yyyy-MM-dd" format in
the given locale. This function supports only the Gregorian calendar.
Example: <?xdoxslt:get_day(‘2000-04-08’, $_XDOLOCALE)?> returns 8 |
get_month
|
<?xdoxslt:get_month(‘2000-04-08’,
$_XDOLOCALE)?>
|
This function provides
a method to get the month value of a date in "yyyy-MM-dd" format in
the given locale. This function supports only the Gregorian calendar.
Example: <?xdoxslt:get_month(‘2000-04-08’, $_XDOLOCALE)?> returns 4 |
get_year
|
<?xdoxslt:get_year(‘2000-04-08’,
$_XDOLOCALE)?>
|
This function provides
a method to get the year value of a date in "yyyy-MM-dd" format in
the given locale. This function supports only the Gregorian calendar.
Example: <?xdoxslt:get_year(‘2000-04-08’, $_XDOLOCALE)?> returns 2000 |
month_name
|
<?xdoxslt:month_name(1,
0, $_XDOLOCALE)?>
|
This function provides
a method to get the name of the month in the given locale. This function
supports only the Gregorian calendar.
The syntax for this function is: <?xdoxslt:month_name(month, [abbreviate?], $_XDOLOCALE)?> where month is the numeric value of the month (Januany = 1) and [abbreviate?] is the value 0 for do not abbreviate or 1 for abbreviate. Example: <?xdoxslt:month_name(12, 1, ‘fr-FR’)?> returns dec. Example" <?xdoxslt:month_name(1, 0, $_XDOLOCALE)?> returns January |
maximum
|
<?xdoxslt:maximum(ELEMENT_NAME)?>
|
Returns the maximum
value of the element in the set.
|
abs
|
<?xdoxslt:abs(-123.45)?>
|
Returns the absolute
value of the number entered.
Example: <?xdoxslt:abs(-123.45)?> Returns: 123.45 |
chr
|
<?xdofx:chr(n)?>
|
CHR returns the
character having the binary equivalent to n in either the
database character set or the national character set.
|
ceil
|
<?xdofx:ceil(n)?>
|
CEIL returns smallest
integer greater than or equal to n.
|
floor
|
<?xdofx:floor(n)?>
|
FLOOR returns largest
integer equal to or less than n.
|
round
(SQL function) |
<?xdofx:round
( number [, integer ] )?>
|
ROUND returns number rounded
to integer places right of the decimal point. If integer is
omitted, then number is rounded to 0 places. integer can
be negative to round off digits left of the decimal point. integer must
be an integer.
Example: <?xdofx:round (2.777)?> returns 3 Example: <?xdofx:round (2.777, 2)?> returns 2.78 |
round
(XSLT function) |
<?xdoxslt:round
( number [, integer ] )?>
|
ROUND returns number rounded
to integer places right of the decimal point. If integer is
omitted, then number is rounded to 0 places. integer can
be negative to round off digits left of the decimal point. integer must
be an integer.
Example: <?xdoxslt:round (2.777)?> returns 3 Example: <?xdoxslt:round (2.777, 2)?> returns 2.78 |
lower
|
<?xdofx:lower
(char)?>
|
LOWER returns char,
with all letters lowercase. char can be any of the datatypes
CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the
same datatype as char.
|
upper
|
<?xdofx:upper(char)?>
|
UPPER returns char,
with all letters uppercase. char can be any of the datatypes
CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the
same datatype as char.
|
length
|
<?xdofx:length(char)?>
|
The "length"
function returns the length of char. LENGTH calculates length
using characters as defined by the input character set.
|
greatest
|
<?xdofx:greatest (
expr [, expr]... )?>
|
GREATEST returns the
greatest of the list of exprs. All exprs after
the first are implicitly converted to the datatype of the first expr before
the comparison.
|
least
|
<?xdofx:least (
expr [, expr]... )?>
|
LEAST returns the
least of the list of exprs. All exprs after the
first are implicitly converted to the datatype of the first expr before
the comparison.
|
next_element
|
<?xdoxslt:next_element(current-group(),.,'<element-name>')?>
|
Method to get the next
element in the current group. Will return the element that occurs after the
element named. For example:
<?xdoxslt:next_element(current-group(),.,'employee')?> will return the element that occurs in the current group after "employee". |
prev_element
|
<?xdoxslt:prev_element(current-group(),..,'<element-name')?>
|
Method to get the
previous element in the current group. Will return the element that occurs
before the element named. For example:
<?xdoxslt:prev_element(current-group(),.,'employee')?> will return the element that occurs in the current group before "employee". |
set_array
|
<?xdoxslt:set_array($_XDOCTX,
‘<name of hash table>’, n, ‘<value>’)?>
|
Sets a value in a hash
table. Syntax is <?xdoxslt:set_array($_XDOCTX, ‘<name of hash
table>’, n, ‘<value>’)?>
where $_XDOCTX is required to set the context, <name of hash table> is the name you supply for your table n is the index of the hash table <value> is the value to set in the hash table. For example: <?xdoxslt:set_array($_XDOCTX, ‘Employee’, 2, ‘Jones’)?> See get_array below. |
get_array
|
<?xdoxslt:get_array($_XDOCTX,
‘<name of hash table>’, n)?>
|
Returns the value at
the specified index of the hash table.
Syntax is <?xdoxslt:get_array($_XDOCTX, ‘<name of hash table>’, n)?> where $_XDOCTX is required to set the context, <name of hash table> is the name you supplied for your table in set_array n is the index value of the element you want returned. For example, used in conjunction with the set_array example above, <?xdoxslt:get_array($_XDOCTX, ‘Employee’, 2)?> returns Jones |
The
following table shows supported combination functions:
SQL Statement
|
Usage
|
(2+3/4-6*7)/8
|
<?xdofx:(2+3/4-6*7)/8?>
|
lpad(substr('1234567890',5,3),10,'^')
|
<?xdofx:lpad(substr('1234567890',5,3),10,'^')?>
|
decode('a','b','c','d','e','1')||instr('321',1,1)
|
<?xdofx:decode('a','b','c','d','e','1')||instr('321',1,1)?>
|
Supported XSL
Elements
|
Description
|
BI Publisher Syntax
|
<xsl:value-of
select= "name">
|
Placeholder syntax
|
<?name?>
|
<xsl:apply-templates
select="name">
|
Applies a template
rule to the current element's child nodes.
|
<?apply:name?>
|
<xsl:copy-of
select="name">
|
Creates a copy of the
current node.
|
<?copy-of:name?>
|
<xsl:call-template
name="name">
|
Calls a named template
to be inserted into/applied to the current template.
|
<?call:name?>
|
<xsl:sort
select="name">
|
Sorts a group of data
based on an element in the dataset.
|
<?sort:name?>
|
<xsl:for-each select="name">
|
Loops through the rows
of data of a group, used to generate tabular output.
|
<?for-each:name?>
|
<xsl:choose>
|
Used in conjunction
with when and otherwise to express multiple conditional tests.
|
<?choose?>
|
<xsl:when
test="exp">
|
Used in conjunction with
choose and otherwise to express multiple conditional tests
|
<?when:expression?>
|
<xsl:otherwise>
|
Used in conjunction
with choose and when to express multiple conditional tests
|
<?otherwise?>
|
<xsl:if
test="exp">
|
Used for conditional
formatting.
|
<?if:expression?>
|
<xsl:template
name="name">
|
Template declaration
|
<?template:name?>
|
<xsl:variable
name="name">
|
Local or global
variable declaration
|
<?variable:name?>
|
<xsl:import
href="url">
|
Import the contents of
one stylesheet into another
|
<?import:url?>
|
<xsl:include
href="url">
|
Include one stylesheet
in another
|
<?include:url?>
|
<xsl:stylesheet
xmlns:x="url">
|
Define the root
element of a stylesheet
|
<?namespace:x=url?>
|
You can use most FO elements in an RTF template
inside the Microsoft Word form fields. The following FO elements have been
extended for use with BI Publisher RTF templates. The BI Publisher syntax can
be used with either RTF template method.
The
full list of FO elements supported by BI Publisher can be found in the
Appendix: Supported XSL-FO Elements.
FO Element
|
BI Publisher Syntax
|
<fo:page-number-citation
ref-id="id">
|
<?fo:page-number-citation:id?>
|
<fo:page-number>
|
<?fo:page-number?>
|
<fo:ANY NAME
WITHOUT ATTRIBUTE>
|
<?fo:ANY NAME
WITHOUT ATTRIBUTE?>
|
Query:
select b.concurrent_program_name,
c.user_concurrent_program_name,
b.output_file_type,
e.executable_name,
e.execution_file_name,
e.execution_method_code,
a.application_short_name ,
a.data_source_name "DATA DEFINITION
NAME",
a.data_source_code "DATA DEFINITION
CODE",
d.template_name "TEMPLATE NAME",
d.template_code "TEMPLATE CODE",
d.template_type_code,
d.default_language,
d.default_territory,
f.file_name
from xdo_ds_definitions_vl a,
fnd_concurrent_programs b,
fnd_concurrent_programs_tl c,
xdo_templates_vl d,
fnd_executables e,
xdo_lobs f
where a.data_source_code
= b.concurrent_program_name
and b.concurrent_program_id
= c.concurrent_program_id
and a.data_source_code
= d.data_source_code
and e.executable_id
= b.executable_id
and f.lob_code = d.template_code
and a.application_short_name
= 'FND'
and b.concurrent_program_name
like 'FNDSCURS'
and e.EXECUTION_METHOD_CODE
like 'P'
and f.file_name like '%.rtf'
ORDER BY EXECUTION_FILE_NAME
No comments:
Post a Comment