Steps to create a XML Publisher Report

1] Add the “Xml Publisher Administrator” Responsibility to the user through the front end.

2] Create the Report(Data Model or we can say the .Rdf file) using Oracle Report Builder.

3] Set the user parameter as p_conc_request_id.

4] Add the default values to the Before Report and After Report triggers(not mandatory)

5] Ftp the Report to the Cust_Top/Report/Us.

6] Open the Oracle E-Business Suite then go to Sysadmin>Concurrent>Program>Executables, Here we have to create one executable file for that Rdf.

7] Then go to Sysadmin>Concurrent>Program>Define, Here we have to make a Concurrent Program for that Executable. Make sure that the output format must be XML.

8] Goto the Sysadmin>Security>Responsibility>Define. Query for the Xml Publisher Administrator. See the Request Group attached to this. Attach the Concurrent Program to this Request Group.

9] Design the template in Ms Word(Using the .Rtf file).

10] Goto responsibility XML PUBLISHER ADMINISTRATOR.

Then Goto HOME>DATA DEFINITION>CREATE DATA DEFINITION and 

create a new data definition. Make sure that your Data Definition’s Code should be same as 

Concurrent Program’s Short Name used by you to create the Rdf file.

11] Now go to Xml publisher administrator>Home>Template.

Create a new template with template type=’Rtf’. Then upload the RTF File by browsing the path.

12] Now go to the Responsibility and run the request.

Challa.

Advantages of using the XML Publisher Report

Choose Your Design Tool:

Xml Publisher Report formats can be designed using Microsoft Word or Adobe Acrobat – tools most users are already familiar with. There is no proprietary design studio component required, meaning no extra cost and no extra learning curve.

Data from Anywhere:
Xml Publisher can accept and format any well-formed Xml data, as well as generate the Data. It can therefore be integrated with any database. Xml Publisher also allows you to bring data In from Multiple Data Sources into a Single Output Document.

Deliver To Anywhere:
Deliver your content via Printer, E-Mail, Fax, Webdav, or publish your report to a portal. The Delivery Manager’s open architecture allows for easy implementation of custom delivery channels, as well.

Communicate With The World:

Report Layouts can be created for any combination of 185 languages and 244 territories. These Layouts are converted to Industry-Standard files for easy In-House translation or delivery to third party translation providers. Because each translation is a separate file, modifications can still be made to the Layout without impacting translations. Xml Publisher also offers Advanced Bidirectional and Font Handling Support.

Unsurpassed Performance:
Xml Publisher is based on the W3c Xsl-Fo Standard and it is the Fastest, Most Scalable Implementation in the world today. It can handle very large data inputs and generate output in less time, using very low levels of CPU time and memory.

Open Standards. Easy Integration:
Xml Publisher is built on Open Standard technologies. It is a J2ee application that can be deployed to any J2ee container. Data is handled as Xml and the Layout Templates created in the desktop applications are internally converted to Xsl-Fo, another W3c standard. Outputs generated by the application are also industry standards such as Pdf, Rtf and Html. The delivery protocols are Internet Printing Protocol (Ipp), Webdav, Ftp, As2.

Refer : http://www.w3schools.com/ for more details on XML.

Challa.

How to use If Else Conditions in XML Publisher RTF Template

XML Publisher supports the common programming construct "if","if-then-else". This is extremely useful when you need to test a condition and conditionally show a result.

We can use if condition directly by writing <?IF?> condition <?END IF?>

Example:
<?if:P_PM_YN='N'?> Yes <?end if?>

But for If-else we need to use extended function.

Method 1:

<?xdofx:if element_condition then result1 else result2 end if?>

Example:
<?xdofx:if INVOICE_AMOUNT > 5000 then ’Higher’
else
if INVOICE_AMOUNT <3000 then ’Lower’
else
’Equal’
end if?>

Method 2:
Syntax:
<?xdoxslt:ifelse(condition,true,false)?>

Example:
<?xdoxslt:ifelse(20=21,’yes 20 and 21 are equal’,’No 20 and 21 are not equal’)?>

Ans: No 20 and 21 are not equal

Using OR Condition in XML RTF Template:

Syntax:
<?if:XMLfield=value1 or XMLfield=value2?> display value <?end if?>

Example:

<?if:sum(AVALUE)=0 or sum(BVALUE)=0?>0<?end if?>

You can use whichever is applicable to your requirement.

Challa.

Valuesets for beginner's

What is value set in Oracle application AOL?

• Value set is primarily a container for your values, you define your value set such that it can control the types of values that are allowed into the value set (either predefined or nonvalidated). You can specify the format of your values.
• Oracle Application Object Library uses value sets as important components of key flexfields, descriptive flexfields, and Standard Request Submission (value sets for report parameters for your reports that use the Standard Request Submission feature).

When to defining Values for Value Sets?

• After you register your Flexfields & report parameters, if you are using independent or dependent value sets, you can enter values into each corresponding value set using the Segment Values form.
• Values for the Value Sets, we are defining will be kept in the Oracle Application Object Library tables.

How many Format Types the value set have?

1. Char
2. Date
3. DateTime
4. Number
5. Standard Date
6. Standard Date Time
7. Time

You should take a note that Date and Date Time value set formats will be obsolete now and are provided for backward compatibility only. For new value sets, use the format types Standard Date and Standard Date Time.

What is Security type in value set?

• By Security Rules window, we can define value security rules for ranges of flexfield and report parameter values.

There are two levels where you must activate Security, the one at value set level and other atindividual segment or parameter level. You make Flex field Value Security available for your value set by choosing Hierarchical Security or Non-Hierarchical Security for the Security Type. When you make security available for a value set, all segments and report parameters that use that value set can use security. You then enable security for a particular segment or parameter.

• Choose Hierarchical Security, If you want Security on a parent value to Cascade down to its child value or else you can choose Non-Hierarchical Security.

How many Character Formatting Options have for value set? 

• Numbers Only (0 – 9)We cannot prevent users from entering a value that contains the radix character.

Cannot be used in Translatable Independent and Translatable Dependent value sets.

• Uppercase Only(A-Z)

Here also we cannot use in Translatable Independent and Translatable Dependent value sets.

• Right justify and Zero fill Numbers(001)

1. If you have selected Numbers Only (0-9) flag, then it wont allow you to affect this flag.
2. We are recommended to use this in Accounting Flex fields.

• Minimum and Maximum Value Range

1. Your Minimum/maximum value may not be longer than the maximum size you specify for this value set.
2. Once you specify a range of values, you cannot define a new valid value that falls outside this range.
3. The Minimum Value and Maximum Value fields can therefore allow you to create a value set with a validation type of None

How many validation Type does value set have?

There are several validation types that affect the way users enter and use segment or parameter values:

• None (not validated at all)

1. Allow users to enter any value.
2. Only Format Validations will be done.

• Independent

1. Provides a predefined list of values.
2. Independent values are stored in an Oracle Application Object Library table.

• Dependent

1. Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
2. Must define your independent value set before you define the dependent value set that depends on it.
3. Advisable to create your independent values first.
4. Must create at least one dependent value for each independent value, or else it wont allow you to enter into that segment or field.

• Table

1. It use your own application tables as value sets for flex field segments and report parameters instead of the special values tables which Oracle Applications provides.
2. You can also use validation tables with other special arguments to make your segments depend on profile options or field values.
3. You can use any existing application table, view, or synonym as a validation table.
4. If we are using non registered table for your value set, then we have to Create the necessary grants and synonyms to APPS Schema.
5. The value column and the defined ID column in the table must return a unique row for a given value or ID.
6. If the Hidden Id column is provided the value passed to the report will be Hidden and not the Value column.
7. Similarly, when you specify :$FLEX$.Value_Set_Name, your flex field segment or report parameter defaults to always use the hidden ID column to compare with your WHERE clause .
8. We can use Special BIND variable such as :$PROFILES$.Option_name, :$FLEX$.Value_set_name, :block.field in the WHERE clause.

• Special

1. Special validation value sets allow you to call key flex field user exits to validate a flex field segment or report parameter using a flex field within a flex field mechanism. You can call flex field routines and use a complete flex field as the value passed by this value set.

• Pair

1. Pair validation value set allows user to pass a range of concatenated Flex field segments as parameters to a report.

• Translatable Independent & Translatable Dependent

1. These value sets are similar to Independent and Dependent value sets except that translated values can be displayed to the user. Translatable Independent and Translatable Dependent value sets allow you to use hidden values and displayed (translated) values in your value sets. In this way your users can see a value in their preferred languages, yet the values will be validated against a hidden value that is not translated.
2. We can convert the Independent value set to a Translatable Independent value set, or a Dependent value set to a Translatable Dependent value set. These are the only types of conversions allowed.

Which Oracle table store Value sets and underline information?

FND_FLEX_VALUE_HIERARCHIES
FND_FLEX_VALUE_SETS
FND_ID_FLEX_SEGMENTS
FND_FLEX_VALUE_NORM_HIERARCHY
FND_FLEX_HIERARCHIES
FND_FLEX_VALUE
FND_FLEX_VALIDATION_EVENTS
FND_FLEX_VALUE_RULE_LINES
FND_FLEX_VALUE_RULE
FND_FLEX_VALUE_RULE_USAGE
FND_RESPONSIBLITY
FND_TABLES
FND_FLEX_VALIDATION_TABLES

Any method to upload flexfield value?

Yes, FNDLOAD is utility which can be used for moving value set across different environment.

Do we have any restriction on value set?
Yes, here are some listed one:

Table Validated Value Sets

1. We cannot use table-validated id value sets for any accounting flexfield or any other key flexfields.
2. We cannot use :$FLEX$, :$PROFILES$ in table name, value and id of table validated value sets.
3. We cannot use DISTINCT clause in any of the column fields or in the WHERE clause of a table validate value set.
4. In an id value set, the value can be non-unique but id should be unique. In a non-id value set, value should be unique.
5. We can only use columns selected for the table-validated value set must be of type NUMBER, DATE or VARCHAR2.
6. Support for SQL expression in columns of Table Validated value sets will be obsolete in future release.

Translatable Independent and Translatable Dependent Valuesets

1. The Numbers Only and Uppercase Only option cannot be used.
2. Must have “Char” format type.

Special/Pair valuesets

1. Special/Pair value sets are user-exit value sets . PL/SQL APIs will not be able to validate them.

Lets now define a simple value set in R12:

Step 1: Go to Application Developer, and select menu /Validation/Set

Create a value set name as COUNTRY_LIST which will contain a list of countries. Make it anindependent value set. Format type is CHAR. Save the work.



Step 2: Go to Application Developer, and select menu /Validation/Values
The below window will appear. Put the Search Name as COUNTRY_LIST and click Find.



Step 3: Enter the country details in this window. Save the work.



Now the value set is ready to be used in any concurrent program.

Challa.

Query to fetch the Parameter List and associated Value Sets of a Concurrent Program.

The following query will fetch the Parameter List and associated Value Sets of a Concurrent Program.

SELECT

fcpl.user_concurrent_program_name "Concurrent Program Name",

fcp.concurrent_program_name "Short Name",

fdfcuv.column_seq_num "Column Seq Number",

fdfcuv.end_user_column_name "Parameter Name",

fdfcuv.form_left_prompt "Prompt",

fdfcuv.enabled_flag " Enabled Flag",

fdfcuv.required_flag "Required Flag",

fdfcuv.display_flag "Display Flag",

fdfcuv.flex_value_set_id "Value Set Id",

ffvs.flex_value_set_name "Value Set Name",

flv.meaning "Default Type",

fdfcuv.DEFAULT_VALUE "Default Value"

FROM

fnd_concurrent_programs fcp,

fnd_concurrent_programs_tl fcpl,

fnd_descr_flex_col_usage_vl fdfcuv,

fnd_flex_value_sets ffvs,

fnd_lookup_values flv

WHERE

fcp.concurrent_program_id = fcpl.concurrent_program_id

--AND fcpl.user_concurrent_program_name = :conc_prg_name

AND fdfcuv.descriptive_flexfield_name = '$SRS$.'|| fcp.concurrent_program_name

AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'

AND flv.lookup_code(+) = fdfcuv.default_type

AND fcpl.LANGUAGE = USERENV ('LANG')

AND flv.LANGUAGE(+) = USERENV ('LANG')

ORDER BY fdfcuv.column_seq_num;