Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

EBS BI Publisher tables

I faced some issues recently where I had to check the date when the RTF template, data template and bursting control file of a BI Publisher report in Oracle EBS were last updated. The following tables came in handy and I am jotting them down here lest I forget.

  • XDO_TEMPLATES_B – Stores the report template details
  • XDO_DS_DEFINITIONS_B – Stores the data template details
  • XDO_LOBS – Stores the bursting control file details
--template file
select * from apps.xdo_templates_b where template_code = '<Report Code>';
--data template
select * from apps.xdo_ds_definitions_b where data_source_code = '<Report Code>';
--bursting file
select * from apps.xdo_lobs where LOB_CODE = '<Report Code>';

Custom Commands in WinSCP

The WinSCP tool provides a couple of handy features which allow users to execute custom commands directly on the server without having to open a separate terminal.
One such feature is the Command Line which you can enable from Options>Command Line. This will display the command line at the bottom of the GUI where you can execute arbitrary commands. The restriction is that you cannot enter any commands which require user-inputs or involve data transfer. You can enter commands like ‘ls -ltr’ to list files and the results will be displayed in a console window which pops up to display the output.
The second feature can be enabled from Options>Toolbars>Customer Command Buttons. Once enabled, a set of pre-existing commands such as Execute, Touch, Grep etc will be displayed. So if , for example, you want to execute a shell script on the remote server, you select the appropriate file and then click on the Execute command. The shell script will get executed. This saves you from the trouble of having to open a new terminal and then typing the command to execute the script.
However, the most interesting thing about this feature is the little gear symbol. Click on this gear symbol and you will realise that you can modify the pre-existing commands and add your own ones. This is especially useful when you have to do some repetitive task such as compiling a form or uploading a workflow in Oracle EBS.

Custom commands overview

You can write you own custom command to compile a form or to upload a workflow file. So the next time you make changes to the form or workflow you can just copy it to the server, select the file on the server and click on your custom command. You do not have to perform the additional step of opening a server session on PuTTY to execute the compile or upload command. The screenshot below shows the details of the XXUploadWF custom command which I use to upload workflow files.

Custom command details

Another useful custom command I have defined is XXCompile_Form. The command for this is

frmcmp_batch module="!" userid=apps/apps output_file=`echo "!" | awk '{print substr($0,0,index($0, ".fmb")-1)}'`.fmx module_type=form compile_all=special

It compiles the selected .fmb file(in an EBS R12 environment) and generates an .fmx file of the same name.

More details on writing custom commands for WinSCP is available here.


Notes on Extracting the Day of the Week

There are a couple of date formats which can be used to extract the day of the week.

D – Returns a number from 1 to 7 indicating the day of the week

Day – Returns the name of the day of the week

Here are these two formats in use:



But there is a catch, using the ‘D’ format will return different values depending on the the NLS_TERRITORY that is set. While some cultures consider Sunday to be the first day of the week, others grant that privilege to Monday. Consequently, if the NLS_TERRITORY is set to AMERICA, Saturday will be the seventh day of the week starting from Sunday and a ‘7’ will be returned. Setting the NLS_TERRITORY to ITALY, however, will return ‘6’ indicating that Saturday is the sixth day of the week starting from Monday.


With the ‘Day’ format, Oracle returns the “Name of day, padded with blanks to display width of the widest name of day in the date language used for this element“. In English, Wednesday is the day with the longest name with a length of 9 characters. Hence the ‘Day’ format returns the name of the day padded with blanks till the ninth character.


I wouldn’t have come across the quirks of these two date formats if I hadn’t burnt my fingers recently using one of them. So, if you are reading this, please consider yourself warned :)


Get every new post delivered to your Inbox.

Join 44 other followers