Limiting lines per page and fixed length table in XML/BI Publisher reports

In most master detail report there is a need to display the master/header lines followed by the child/detail lines. For example, Invoice report, Dunning Letter, Customer Statement, etc. In all these reports the number of detail lines that can be displayed on a single page needs to be restricted so that the report look is consistent across all pages.

There are solutions available at various forums but I wish to share the solution with my own logic.

The requirement:

Say if your report has 36 lines of data and you requirement is to display only 30 lines per page, then the report should display only 30 lines in the first page and the remaining 6 lines must appear in the next page. Also, the second page must have the table size fixed, meaning the table must end at the end of the page and not just after displaying the remaining lines.

Look at the screenshots shown below to get a glimpse. The first screenshot shows that only 30 lines have been printed. The second screenshot shows that the second page has printed the rest of the lines but the table does not end immediately. The table extends till the end of the page or in other words till it can fill 30 lines.

Let us divide this into stages.

In stage 1, we shall discuss about the declaration of variables that are required to put this logic together.

In stage 2, we shall discuss about limiting lines per page which means page will be broken once the line limit is reached in that particular page.

In stage 3, we shall discuss about extending the table based on the lines printed in the last page so that table goes till the end of the last page.


Stage 1: Variable declaration

In this stage, we shall look at the variables required. Let us simplify this logic. All we need is a few variables for calculation of number of lines and lines per page.

lines_page” variable is needed to store the number of lines that we are supposed to display per page. This will be the line limit.

Counter” variable is needed to store the position of the current line that is being printed.

tot_lines” is the count of all the lines in the xml data for that particular header. The count function on “.//G_LINE” gives the count of the lines in that particular header.

Remainder” variable is needed to store the count of lines for the last page to extend the table.

The below commands are used to create a few variable.

<?xdoxslt:set_variable($_XDOCTX, ‘counter’, 0)?>

<?xdoxslt:set_variable($_XDOCTX, ‘lines_page’, 31)?>

<?xdoxslt:set_variable($_XDOCTX, ‘tot_lines’, count(.//G_LINE))?>

<?xdoxslt:set_variable($_XDOCTX,’remainder’,0)?>

The above variables in PLSQL would be:

Counter := 0;

Lines_page := 31;

Tot_lines := count(G_LINE);

Remainder:= 0;

We shall see the use of these variables as we go further. The above code can be put in an XML tag as well like this var

Stage 2: Limiting lines per page

In this stage, the task is to allow the lines to be printed till 30 lines as per the “lines_page” value, then go into the next page using page break.

We iterate the variable ‘counter’ for each line, meaning the value of the variable will be incremented by one thus serving as a pointer of the current record.

So when this counter reaches the line limit per page say 30, we need to call a page break.

So the first variable will be ‘counter’ which will be initialized to zero at the beginning of the report.

Then when the FOR loop begins in the template use the below command to increment the ‘counter’ value.

<?for-each:G_LINE?> <?xdoxslt:set_variable($_XDOCTX, ‘counter’, xdoxslt:get_variable($_XDOCTX, ‘counter’)+1)?>

The above command will set value of ‘counter’ as ‘counter+1′. This in PL/SQL would be “counter: = counter + 1;”

Now we need to put a check which shall break the page if 30 lines have been printed. The lines_page variable has value 31 which means it holds the value for “line per page plus 1″.

<?if@inlines:xdoxslt:get_variable($_XDOCTX,’counter’) != xdoxslt:get_variable($_XDOCTX,’tot_lines’)?>
<?if@inlines:position() mod xdoxslt:get_variable($_XDOCTX, ‘lines_page’) = 0?>

The above “IF” condition basically in PLSQL is

IF counter <> tot_lines THEN

IF current_position mod lines_page = 0 THEN

The mod operator returns the remainder divisor and dividend’s division operation.

The first “IF” condition makes sure that we are not breaking the page at the end of the report unnecessarily and the second “IF” condition checks if the report has BI processor has reached the line limit for the page.

Once the limit is reached the above IF condition will return TRUE and we then call the page break.

The usual command for PAGE BREAK is <?split-by-page-break:?> but this cannot be used inside and IF condition hence we have to call the command from another template which can be defined in the same layout but after the FOR loop of all the groups is over.

Below is an example of defining a template and calling it elsewhere.

<?template:breaking?>

<?split-by-page-break:?>

<?end template?>

The above template can be called using CALL command as shown below.

<?call:breaking?>

This command can be used in the above mentioned if condition so that the page can break when the IF condition turn TRUE, in other words when the BI processor reaches the line limit per page.

Now the page breaks and goes into second page and the first page will look as shown below.

You can see that the table does not have the bottom line as the table has not yet closed but just gone into second page. To make this table appear as closed you can use a table cell with all but bottom border as invisible as shown below.

Include this cell in the template “breaking” before the page break command as shown below.

<?template:breaking?>

<?split-by-page-break:?>

<?end template?>

The above template can be used for creating a page break; this will not only break the page but also make the table have a bottom line. The end result of this command is as shown below.

You can see that the table has the bottom line as well even though it has not yet actually closed.

So the code that has been used so far turns out to be

<?xdoxslt:set_variable($_XDOCTX, ‘counter’, xdoxslt:get_variable($_XDOCTX, ‘counter’)+1)?>

<?if@inlines:xdoxslt:get_variable($_XDOCTX,’counter’) != xdoxslt:get_variable($_XDOCTX,’tot_lines’)?>

<?if@inlines:position() mod xdoxslt:get_variable($_XDOCTX, ‘lines_page’) = 0?><?call:breaking?><?end if?><?end if?>

The above code can be put in an XML tag for ease.

The <?end if?> commands are given to close the “IF” conditions opened earlier.

Remember, the above code must be put after the FOR loop has begun for the lines.

Once the all the lines are displayed (say 36 lines which include one page break that occurs at line 30), the table is closed as shown below (after line36).


Stage 3: Extending table to subsequent pages

As you can see from above image, the table ends after the lines are displayed but we are supposed to extend the table till the end of the page as shown below.

So in this stage we shall look at extending the table till the end of the page.

This means we have to make the template extend as if we are still displaying the data inside the table and the table must keep going down.

You can see from above image that the 6 lines are displayed which means another 24 lines of data can be placed till the table reaches the end of the page.

It means all we need to do is put a line break 24 (30 minus 6) times so that the table reaches the end.

But this has to be done only when all the lines have been displayed. The below condition will help evaluate if we have reached end of all the lines.

<?if@inlines:xdoxslt:get_variable($_XDOCTX,’i’)=xdoxslt:get_variable($_XDOCTX,’tot_lines’)?>

Remember we had defined a variable tot_lines that will hold the count of all the lines. Well once the counter matches tot_lines value we can safely assume that the all the lines have been printed.

The above condition in PL/SQL is

IF counter = tot_lines THEN

Now that we are at the end of the line data, we now need to calculate how many line breaks we need to give.

The below command will give us that value. Remember we had defined a variable named “remainder“. This variable will hold the number of line breaks that we need to give to extend the table till the end.

<?xdoxslt:set_variable($_XDOCTX,’remainder’,xdoxslt:get_variable($_XDOCTX,’lines_page’) – (xdoxslt:get_variable($_XDOCTX,’tot_lines’) mod xdoxslt:get_variable($_XDOCTX,’lines_page’)))?>

The above command in PLSQL is

Remainder := lines_page – (tot_lines mod lines_page)

The above command basically returns the count of lines that could have been printed if the lines in the XML had not ended. The second page displayed 6 lines, meaning more 24 lines could have been printed. So you can call 24 line breaks to extend the table.

Now all you need to do is call a line break inside this IF condition but this is feasible only if you call it through a template similar to the PAGE BREAK that we are doing at the end of 30 lines.

Below is the code to call a template with a line break.

<?template:looping?>

<?end template?>

In the above template command, we have nothing but a line break which includes a space right after the <?template:looping?>command. The below example will simplify the “looping” template.

<?template:looping?><<space>><<line break>> <?end template?>

The above command explains the “looping” template defined. Giving the space is mandatory otherwise the line break will keep wrapping up to the previous line and the table will not get extended. When the space is given, it is treated as a character and the wrapping is avoided.

Now the template “looping” is to be called 24 times to extend the table till the page end.

The below command should do the job.

<?for-each:xdoxslt:foreach_number($_XDOCTX,1,xdoxslt:get_variable($_XDOCTX,’remainder’),1)?>

<?call:looping?><?end for-each?>

<?end if?>

The above for-each command in PLSQL is

FOR counter in 1..remainder LOOP

This means there will be a loop for 24 times and in this loop, the “looping” template is called.

The line break happens 24 times and table is extended till the end of the page.

If the second page contains only one line, then the “remainder” variable will have 29 as value and the line break would happen 29 times.

The <?end if?> command obviously closes the IF condition that was opened earlier.

So the code for extending the table would look like this:

<?if@inlines:xdoxslt:get_variable($_XDOCTX,’i’)=xdoxslt:get_variable($_XDOCTX,’tot_lines’)?>

<?xdoxslt:set_variable($_XDOCTX,’remainder’,xdoxslt:get_variable($_XDOCTX,’lines_page’) – (xdoxslt:get_variable($_XDOCTX,’tot_lines’) mod xdoxslt:get_variable($_XDOCTX,’lines_page’)))?>

<?for-each:xdoxslt:foreach_number($_XDOCTX,1,xdoxslt:get_variable($_XDOCTX,’remainder’),1)?>

<?call:looping?><?end for-each?>

<?call:pageline?><?end if?>

The above logic when incorporated in a template should give desired results as shown in the screenshots earlier.

Multi Lingual Report – XML Publisher

We have created 2 rtf templates, 1 for English and 1 for Arabic.

Arabic template (File Name: Arabic template.rtf)

English template (File name: English template.rtf)

Now we will register these templates in Oracle.

Responsibility: XML Publisher Administrator

Navigation: Templates

We created the template with the same name and short name as the concurrent program.

Now we shall add the templates.

Click on Browse to add a template.

We first added the English template and saved the template by clicking on Apply.

Now the English template has been added to this report. We shall now add the Arabic template. Click on Add File button.

The Add file section opens. Add the Arabic template file. Enter Language as Arabic.

Click on Apply button to save the template.

Now you can see that both the templates have been attached to the concurrent program.


Test the report

We shall first test the report in Arabic. To do so we shall log in from the home page by changing the language to Arabic.

You will notice that US English is the base language. This is true for all Oracle Apps installations. Along with English, Arabic (language code AR) in the second line is installed.

You will notice the languages on the Oracle home page.

After logging in we will have to go to the responsibility Order Management – Medical Equipment. Open the SRS form.

 

Enable Trace for a concurrent program

The main use of enabling trace for a concurrent program comes during performance tuning.

By examining a trace file, we come to know which query/queries is/are taking the longest

time to execute, there by letting us to concentrate on tuning them in order to improve the

overall performance of the program.

The following is an illustration of how to Enable and View a trace file for a Concurrent     Program.

Navigation: Application Developer–>Concurrent–>Program

Check the Enable Trace Check box. After that go to that particular Responsibility and run the Concurrent Program.


Check that the Concurrent Program has been completed successfully.

The trace file by default is post fixed with oracle Process_id which helps us to identify which trace file belongs to which concurrent request. The below SQL Query returns the process_id of the concurrent request:

Select oracle_process_id from fnd_concurrent_requests where request_id=’2768335′

(This query displays Process Id)

The path to the trace file can be found by using the below query:

 

 

(This Query displays the path of trace file)

The Trace File generated will not be in the readable format. We have to use TKPROF utility to convert the file into a readable format.

Run the below tkprof command at the command prompt.

TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

A readable file will be generated from the original trace file which can be further

analyzed to improve the performance. This file has the information about the

parsing, execution and fetch times of various queries used in the program.

Dependent parameters in Concurrent Program

This article illustrates the usage of $FLEX$ with an example.

$FLEX$ is a special bind variable that can be used to base a parameter value on the other parameters (dependent parameters)

Syntax –     :$FLEX$.Value_ Set_Name

Value_Set_Name is the name of value set for a prior parameter in the same parameter window that you want your parameter to depend on.

Some scenarios where $FLEX$ can be used:

Example1:

Say you have a concurrent program with the below 2 parameters which are valuesets :

Parameter1 is Deparment

Parameter2 is Employee name

Let’s say there are 100 deparments and each deparment has 200 employees.  Therefore we have 2000 employees altogether.

If we  display all department names in the valueset of parameter1 and all employee names in parameter2  value set  then it might kill lot of performance and also it will be hard for a user to select an employee from the list of 2000 entries.

Better Solution is to let user select the department from the Department Valuset first. Based on the department selected, you can display only the employees in parameter2 that belong to the selected department in parameter1 valueset.

Special Information Types – SIT

SIT is basically a KFF.This we can use to capture additional person information.The KFF which we are using here is Personal Analysis Keyflexfield.We can create and assign N number of SITs to a person.Once you enable the SIT it is available across the Application.

Following steps needs to be done to create a SIT.

1) Define a structure for the Personal Analysis Keyflexfield. For this you have to navigate to

Application Developer > Flexfield Key Segments

Query for Personal Analysis Flexfield.\

1

 

2) Define the segments.

Click on Segments.Create the Segments based on your requirement.

1

 

In the Segments window check Allow Dynamic Inserts check box. Once you complete the segment definition checkFreeze Flexfield Definition and save your work.

3) Enable the SIT.

For this navigate to

US Super HRMS Manager > Other Definitions > Special Information Types

Create a record for above created structure.Check the check box matrix where ever you want to show the SIT.

 

1

 

 

Save your work.

4) Go to any HRMS responsibility.Open Enter and Maintain form.Click on Special Information.Query for above created structure.Enter the information into the segments.

Table

 

The entry will get created in PER_PERSON_ANALYSES and PER_ANALYSIS_CRITERIA table.

API

HR_SIT_API