Oracle Apps

Oracle Apps Blogs

Notes on Extracting the Day of the Week

leave a comment »

sarunraj:

Good article for Oracle Developers.

Originally posted on Oracle Apps Notes:

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:

1

2

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.

3

With the ‘Day’ format, Oracle returns the “Name of day, padded with blanks to…

View original 84 more words

Written by sarunraj

08/05/2014 at 9:32 AM

Posted in Oracle Apps

Oracle BI Publisher Desktop Tool

leave a comment »

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

PastFlow

 

Process Flow using BI Publisher Desktop Tool

PresentFlow

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:

bipubtemplate_viewer

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.

bipubtemplate_viewer001

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.

bipubtemplate_viewer002

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.

XML Report from XML Data Template – Part 3

with 2 comments

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.

  1. Group under a Group in the XML Output
  2. 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:

  1. Supplier and Supplier’s invoices
  2. Customer and Customer’s AR Invoices
  3. Supplier and Supplier Sites
  4. Department and Employees belonging to the Department

So in our example let us look at the below business requirement:

  1. List all the Invoices belonging to a Supplier
  2. At Supplier level we need the SUM of the invoice amounts

So basically we are looking at the below structure.

requiredXMLStructure

 

Let us look at the XML Data Template that we need to create. We have two parameters – Org Id and Vendor Id

xmlTemplate

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.

Parent Group:
parentGroup

Child Group:
childGroup

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.

sumAmount

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:

xmlOutput

You can use this XML output to design the RTF Template. Hope this helps. Check out the Part 1 and Part 2 of this blog to get more details.

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.

 

Interesting SQL / PLSQL Tips and Tricks

leave a comment »

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.

XML Report from XML Data Template – Part 2

with one comment

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:

  1. Populate a temporary table with the data based on the parameters passed to the Concurrent Program
  2. Fetch the data for the report from the Temporary table.
  3. 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.

xml_template_definition_01

Lets talk about how to define the trigger.

  1. Define a pl/sql Package xxraj_xml_triggers_pkg
  2. Define functions beforeReport and afterReport in the Package. This is very important. You have to define it as functions and not as procedures
  3. 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
  4. 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.

You may download the Package Spec ( xxraj_xml_triggers_pkg_ps ) and Package Body ( xxraj_xml_triggers_pkg_pb) for your reference.

Now looking at the changes in the XML Data Template, you will see that there are following changes:

Default Package
The package that you are using for the Trigger should be defined as the “default Package” in the XML Data Template.

SQL Statement
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 :-D

Check out the next blog on xml Templates – XML Template Part 3

External Tables in Oracle

with 3 comments

Hey friends, let us look at “External Tables” concept in Oracle. This is a very useful tool for Oracle / Oracle Apps Developers.

External Tables
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
(

Batch_Name                           VARCHAR2(240),
Header_Name                        VARCHAR2(240),
JE_Category                           VARCHAR2(30),
JE_Source                               VARCHAR2(30),
Period_Name                         VARCHAR2(10),
Accounting_Date                  DATE,
Concatenated_Segments      VARCHAR2(120),
Segment1                              VARCHAR2(30),
Segment2                              VARCHAR2(30),
Segment3                              VARCHAR2(30),
Segment4                              VARCHAR2(30),
segment5                              VARCHAR2(30),
Segment6                              VARCHAR2(30),
Accounted_DR                      NUMBER,
Accounted_CR                      NUMBER,
Batch_Description                VARCHAR2(240),
Header_Description             VARCHAR2(240),
Line_Description                  VARCHAR2(240),
Currency_Code                     VARCHAR2(3),
Conversion_Rate                 NUMBER,
Conversion_Type                 VARCHAR2(30),
Actual_Flag                          VARCHAR2(1),
Creation_Date                      DATE,
Line_Number                       NUMBER

)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY gl_in_files
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS
TERMINATED BY ‘|’
OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL

(
Batch_Name ,
Header_Name ,
JE_Category ,
JE_Source ,
Period_Name ,
Accounting_Date ,
Concatenated_Segments ,
Segment1 ,
Segment2 ,
Segment3 ,
Segment4 ,
Segment5 ,
Segment6 ,
Accounted_DR ,
Accounted_CR ,
Batch_Description ,
Header_Description ,
Line_Description ,
Currency_Code ,
Conversion_Rate ,
Conversion_Type ,
Actual_Flag ,
Creation_Date ,
Line_Number
)

)
LOCATION (‘GL_INTERFACE.txt’)
);

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

Errors:

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.

Additional Options
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.

External Table Definition

GL Data File –> Please change the extension to “.txt”. Word press does not allow me to upload files with “.txt” extension.

 

Written by sarunraj

06/03/2014 at 7:55 PM

GL Journal Import for Multiple Journal Sources

leave a comment »

Many of you might have read my blog on how to Submit GL Import Program from a PL/SQL Procedure. The example given in that blog was for importing Journals pertaining to one particular Journal Source.

  • What if you had multiple Journal Source like ‘Receivables‘,’Payables‘ etc?
  • How will you submit the gl import and make sure that the journals pertaining to all the sources are imported?
  • Do we have to submit GL Import for each of the sources separately?

If you have the same questions as above, then please read on. Also have a look at my previous blog on GL Import.

You can use FND_REQUEST.SUBMIT_REQUEST to call the GL Import program from a PL/SQL Procedure.

Before we call the GL Import program, we need to make sure that control records are inserted into GL_INTERFACE_CONTROL Table for each of the sources that we are trying to import.
For example if you have journals from Receivables and Payables, you need to insert two separate records into GL_INTERFACE_CONTROL Table.
One for Receivables Journal source and one for Payables Journal Source. However, make sure that the interface_run_id value is the same for both the records. In the same way you need to insert separate records into GL_INTERFACE_CONTROL Table for each of the sources and keeping the same interface_run_id value for all the records.

The below code snippet is for importing Journals pertaining to Receivables and Payables. You can also download the PDF Version by clicking on  Multiple GL Sources link.
Please leave your comments / feedback / questions.

 

DECLARE

l_conc_id                NUMBER;
l_int_run_id           NUMBER;
l_access_set_id    NUMBER;
l_org_id                   NUMBER := 101;
l_sob_id                   NUMBER := 101;
l_user_id                 NUMBER := FND_GLOBAL.USER_ID;
l_resp_id                 NUMBER := FND_GLOBAL.RESP_ID;
l_resp_app_id       NUMBER := FND_GLOBAL.RESP_APPL_ID;

BEGIN

fnd_global.apps_initialize ( user_id             => l_user_id              –User Id
,resp_id             => l_resp_id              –Responsibility Id
,resp_appl_id => l_resp_app_id)–Responsibility Application Id

mo_global.set_policy_context(‘S’,l_org_id);

SELECT   gl_journal_import_s.NEXTVAL
INTO    l_int_run_id
FROM   dual;

SELECT   access_set_id
INTO    l_access_set_id
FROM   gl_access_sets
WHERE   name = ‘VISION OPERATIONS SET’ ;

INSERT INTO gl_interface_control (je_source_name,interface_run_id,status,set_of_books_id)
VALUES (‘Receivables’,l_int_run_id,‘S’,l_sob_id);

INSERT INTO gl_interface_control (je_source_name,interface_run_id,status,set_of_books_id)
VALUES (‘Payables’,l_int_run_id,‘S’,l_sob_id);  –same interface_run_id for both the records.

l_conc_id := fnd_request.submit_request(application     => ‘SQLGL’
,program          => ‘GLLEZL’
,description    => NULL
,start_time      => SYSDATE
,sub_request  => FALSE
,argument1     => l_int_run_id        –interface run id
,argument2     => l_access_set_id –data access set_id
,argument3     => ‘N’                              –post to suspense
,argument4     => NULL                      –from date
,argument5     => NULL                      –to date
,argument6     => ‘N’                             –summary mode
,argument7     => ‘N’                             –import DFF
,argument8     => ‘Y’                             –backward mode
);

COMMIT;

DBMS_OUTPUT.PUT_LINE(‘GL Import Submitted. Request Id : ‘||l_conc_id);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(‘Error while submitting the GL Import Program.’);
DBMS_OUTPUT.PUT_LINE(‘Error : ‘||SQLCODE||‘-’||SUBSTR(SQLERRM,1,200));

END;

Hope this helps. Looking forward to your comments /feedback / questions.

Follow

Get every new post delivered to your Inbox.

Join 593 other followers