Hi Fellow readers and bloggers.
I have a very exciting news to announce. I have created my own website for the blogs and I plan to post more content. I also plan to post Tutorials and Videos as well. Please find below the details of the website:
I hope to see you all at the new website. I have included a “Contact US” page where you can ask me questions on Oracle Apps or if you want me to write on a specific topic you can do that as well.
Good article for Oracle Developers.
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…
View original post 84 more words
Today I wanted to talk about Oracle BI Publisher Desktop Tool. This is one tool that I love.
If you are working on XML Publisher Reports, this is a must have tool. This tool allows you to test your the RTF Template on your local system (desktop / laptop) and make any changes necessary before uploading the template to the server. You do not have to upload the RTF template to the server and run the concurrent program every time you want to test a change.
I have given below two different process flows and I am sure it explains itself.
Process Flow without using BI Publisher Desktop Tool
Process Flow using BI Publisher Desktop Tool
If you look at the second process flow; the changes are tested on your local system itself, rather than testing the changes in the server. This saves a lot of testing and development time for the Developers 🙂
Check the below images to see how you can use it:
Browse: Click Browse to select the directory where the XML Data file and RTF Template are stored.
Data Region: The Data Region will show you the available XML Data files in the directory
Template Region: The Template region will show you the available RTF Templates.
Select the Data File and the Template that you want to use to create the output.
Output Format: You can select the output format that you want. The formats include PDF / Excel / RTF / HTML etc.
Click on Start Processing and if there are no errors, the output file will be generated.
I had selected the output format as “PDF”, so the output was generated as PDF.
If you are not happy with the output layout, you can make changes in the RTF and test it as many times as you want in the local desktop. Once you are happy with the layout, you can upload the same to the Data Template.
Hope this helps. As always your comments, suggestions and feedback are highly appreciated.
Hi everyone. Hope you all are doing great and enjoying the FIFA world cup.!!
So this is the 3rd in a series of blogs on XML Reports from XML Data Template. Today we are going to look at a few nifty tricks that we can achieve using the XML Data Template. Also check out the Part 1 and Part 2 of this blog to get more details.
- Group under a Group in the XML Output
- Using the SUM Function to calculate sum of a field
Group under a Group in the XML Output
So we can generate a Parent Group and a Sub Group using the XML Data Template. This feature is very useful and mostly used in Parent – Child relationship scenarios.
The following examples fall into this category:
- Supplier and Supplier’s invoices
- Customer and Customer’s AR Invoices
- Supplier and Supplier Sites
- Department and Employees belonging to the Department
So in our example let us look at the below business requirement:
- List all the Invoices belonging to a Supplier
- At Supplier level we need the SUM of the invoice amounts
So basically we are looking at the below structure.
Let us look at the XML Data Template that we need to create. We have two parameters – Org Id and Vendor Id
Parameters Section has the parameters that will be used in the SQL Query.
Data Query Section has the SQL Statement. The SQL Statement selects the Supplier and Invoice Details based on the parameters.
Group: I have defined two groups. G_SUPPLIER is the parent group and G_INV is the child group.
Using the SUM Function to calculate sum of a field
If you notice the G_SUPPLIER group, the TOTAL_INV_AMOUNT field is the sum of all the child invoice amounts belonging to a particular supplier.
The value is picked from the Amount field in the Child Group, i.e., G_INV.AMOUNT. The child values are added together using the function SUM.
Save the XML Data template as an XML File and create the Data Definition. Follow the steps described in the Part 1 of the blog.
When you run the concurrent program the XML output will be similar to what is shown below:
Hope this helps. Let me know your feedback. I am also working on video blogs / video tutorials on few of the topics. I will keep you all updated with the progress.
As always your feedback and suggestions are most welcome. Feel free to ask any questions that you might have.
This blog covers some interesting SQL / PLSQL Tips and Tricks. I will try to include some interesting functions and plsql solutions to some interesting requirements.
Hope this helps.
Find Last Day of the Month
SQL Function :- LAST_DAY()
SELECT LAST_DAY(SYSDATE) FROM DUAL
Find First Day of the Current Month
SQL Function:- Combination of LAST_DAY () and ADD_MONTHS ()
SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1 FROM DUAL
Find First Day of the Previous Month
SQL Function:- Combination of LAST_DAY () and ADD_MONTHS ()
SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 FROM DUAL
Find First Day of the Next Month
SQL Function:- Combination of LAST_DAY () and ADD_MONTHS ()
SELECT LAST_DAY(SYSDATE)+1 FROM DUAL
Count number of times a character appears in a string
SQL Function(s):- Combination of LENGTH() and REPLACE ()
SELECT LENGTH(‘THIS IS SO MUCH FUN’) – LENGTH(REPLACE(‘THIS IS SO MUCH FUN’,’ ‘)) FROM DUAL
The above query will return 4, which is the number of “spaces” in the string “THIS IS SO MUCH FUN”.
SELECT LENGTH(‘THIS IS SO MUCH FUN’) – LENGTH(REPLACE(‘THIS IS SO MUCH FUN’,’S’)) FROM DUAL
The above query will return 3, which is the number of times the character “S” appears in the string.
I will add more in the coming days. Feel free to ask any questions that you may have.
Hello everyone!! Hope you all are enjoying the FIFA 2014 matches. I am a big fan of Football or Soccer as it is called in the US.
Today I wanted to show you how to call triggers from the XML Data Template. This is in continuation to my blog on generating XML Report from XML Data Template. Check the same here.
So here is my requirement:
- Populate a temporary table with the data based on the parameters passed to the Concurrent Program
- Fetch the data for the report from the Temporary table.
- Truncate the Table after the XML Report is generated
Temporary Table Name – XXRAJ_TEMP_TABLE
Here is how the XML Data Template will look like. I have also marked the changes to this new file.
Lets talk about how to define the trigger.
- Define a pl/sql Package xxraj_xml_triggers_pkg
- Define functions beforeReport and afterReport in the Package. This is very important. You have to define it as functions and not as procedures
- Define the parameters as variables in the Package Specification. In our case p_orgid and p_vendorid are the parameters. So you need to define these as variables in the Package Spec
- Create the package body and define the functions. The beforeReport function will insert the invoive data into the temp table and the afterReport function will truncate the data.
Now looking at the changes in the XML Data Template, you will see that there are following changes:
The package that you are using for the Trigger should be defined as the “default Package” in the XML Data Template.
The SQL Statement has been changed to fetch the data from the temporary table rather than from the AP_INVOICES_ALL Table
Before Report Trigger
Just above the <dataStructure> element, I have defined a new trigger. Since the trigger is defined before the <dataStructure>, this will act as the before Report Trigger. You will notice that I have given the name and source of the trigger.
<dataTrigger name=”beforeReport” source=”xxraj_xml_triggers_pkg.beforeReport(:p_OrgId,:p_VendorId)”/>
name: This can be any value. However to make it easier to understand it will be a good practice to give the name as “beforeReport” to indicate that the trigger fires before the report.
source: Here you give the “package.function” that is to be called for execution. The parameters can be passed to this function.
After Report Trigger
Just below the <dataStructure> element, I have defined the after report Trigger. Since the trigger is defined after the <dataStructure>, this will act as the after Report Trigger.
<dataTrigger name=”afterReport” source=”xxraj_xml_triggers_pkg.afterReport(:p_OrgId)”/>
Upload the new XML file to the Data Definition. Everything else remains the same.
This was a very simple example to show how to call Triggers from XML Data Template. Hope this helps. Feel free to ask any questions you may have. As always your feedback is highly appreciated.
Have a great day ahead!! I have to watch Portugal Vs USA Match now 😀
Check out the next blog on xml Templates – XML Template Part 3
Hey friends, let us look at “External Tables” concept in Oracle. This is a very useful tool for Oracle / Oracle Apps Developers.
External Tables enables you to access data in external sources as if the data were in a table. To simplify, you can have a data file in the Unix directory and you can have a select statement to select data from the data file.
Oh yeah, you heard it right!!! This is very useful when you have to process data files as part of interfaces.
I, myself, have used it extensively to read AP, AR and GL Daily Interface files. It is similar to SQL Loader functionality.
Defining an External Table
In my note, we will looking at creating an external table to read GL Journal Data. I will also include the sample data file so that you can try it out. Before you can start defining the external table, you need to know the data file format. In our example the data fields are separated by “|”. We don’t have any header or trailer records as well.
CREATE TABLE xxraj_gl_interface_load_tbl
DEFAULT DIRECTORY gl_in_files
(RECORDS DELIMITED BY NEWLINE
TERMINATED BY ‘|’
OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL
ORGANIZATION EXTERNAL –> This identifies the table as an External Table
DEFAULT DIRECTORY gl_in_files –> You need to define the directory in Oracle. In our example, we will be placing the file @ /data/prod/interfaces/in/GL directory.
And in Oracle Database, we will be creating a directory called “gl_in_files” as follows:
CREATE OR REPLACE DIRECTORY gl_in_files AS ‘/data/prod/interfaces/in/GL’
FIELDS TERMINATED BY ‘|’ –> the different data fields in the data file are separated by “|”.
LOCATION (‘GL_INTERFACE.txt’) –> this specifies the file name is “GL_INTERFACE.txt”. The location is identified by the Directory name given in the script. In our case it is “gl_in_files”.
Access the Data File using External Table
Once the External Table is successfully created, please follow the below steps:
- Place the data file in the directory that you have defined, In our example, the data file will be placed @ /data/prod/interfaces/in/GL
- Make sure the file has read privileges. I usually give “775” privileges on the data file.
- Run the select query from Toad / Oracle SQL Developer / SQL Plus. You may use the below sample query:
SELECT * FROM xxraj_gl_interface_load_tbl
There are chances that you run into errors while running the SQL Query to select the data using External Table. You will have to debug the issues yourself.
The good news, you will see a log file and a bad file in the directory where the data file is placed. You can check the files to see the errors and make changes to the External Table definition or data file, based on the errors.
You can include lot of different options in the External Table Definition. Few of them include:
BADFILE <file_name> –> Provide the name of the Bad File that will be created
DISCARDFILE <file_name>–> Provide the name of the Discard File that will be created
LOGFILE <file_name> –> Provide the name of the Log File that will be created
SKIP <number_of_rows> –> This options allows you to SKIP rows
Download the sample Data file and External Table Definition using below links. I hope this helps you guys. Feel free to post your questions / comments /feedback.
GL Data File –> Please change the extension to “.txt”. Word press does not allow me to upload files with “.txt” extension.