Handling Date Parameters in Oracle Applications

Everytime we choose 'FND_STANDARD_DATE' in our Parameters we often get a lot of type casting errors.

It is because the 'FND_STANDARD_DATE' is of the following format.
'YYYY/MM/DD HH24:MI:SS'

Below example shows you how to handle a parameter in your queries

Subquery in an Update Statement - Use Table Aliases

Always use Table aliases for inner / sub-queries

NOTE : xx_gl_journal_stg.je_header_id doesnt exist, so the below block should throw a compilation error
BUT IT WONT !!!!

BEGIN


UPDATE gl_je_headers
                   SET attribute6 = nvl(p_batch_id, attribute6)
                 WHERE je_header_id IN ( SELECT DISTINCT je_header_id
                                           FROM xx_gl_journal_stg
                                          WHERE batch_id = nvl(p_batch_id, batch_id) );

EXCEPTION

WHEN e_exception THEN
                xc_err_msg := lc_err_msg;
                xn_status := 2;
        WHEN OTHERS THEN
                xn_status := 2;
                xc_err_msg := 'Unexpected error in the BLOCK- '||sqlerrm;


END;

-----------------------------------------------------------------------------------
Solution : The subquery in an update statement should always contain an ALIAS
-----------------------------------------------------------------------------------

Queries - TCA

Distinguish a SUPPLIER / CUSTOMER : 

SELECT hpab.party_usage_code, hpab.party_usage_type, hp.party_name
  FROM apps.hz_party_usg_assignments hpua,
       apps.hz_party_usages_b hpab,
       apps.hz_parties hp
 WHERE     hp.party_id = hpua.party_id
       AND hpua.party_usage_code = hpab.party_usage_code
       AND hpab.party_usage_code LIKE 'SUPPLIER%'
       OR hpab.party_usage_code LIKE 'CUSTOMER%'

How to draw Flow Charts for free

The below website allows you to create flow charts for free

This is really beneficial for professionals or students who do not have access to expensive flowchart creating softwares.

https://www.lucidchart.com/

Example : Here is what i created

Ref Cursors

Brilliant Article on ref-cursors
- Courtesy
https://forums.oracle.com/forums/thread.jspa?threadID=886365


What is the point of ref cursors?

A good question. Many people learn about ref cursors and then try and use them everywhere.
Although I personally know how to write and use ref cursors,
I've found very little use for them in my production code.
If you are thinking of using a ref cursor, the first thing you should ask yourself is "Why do I need one?"

If your answer is that you need to write your code to dynamically generate SQL,
then perhaps you should look at what you are trying to achieve. Typically dynamic SQL,
in a lot of cases, is unnecessary and a major cause of unmaintainable and unscalable code as well
as possible performance issues and SQL injection.

If you really have a valid need to dynamically create SQL and you fully understand the
implications and risks involved, then a ref cursor is useful for this task.
What you will find however is that you are limited with ref cursors to a fixed result structure,
so it may not be as generic a solution as you had initially planned for.
If you consider the examples from above, you will see that we had to define a
SQL structure of known columns in order to receive the data from the ref cursor.
So, whilst the function that opens the ref cursor could do so for any piece of query text,
the only way of successfully getting the data out of that ref cursor is to know what columns
we are expecting back from it.

There is a way around this however.
Either use the DBMS_SQL package to dynamically generate and process your queries or,
from 11g onwards, take your ref cursor and convert it to a DBMS_SQL cursor using 11g's
new DBMS_SQL.TO_CURSOR_NUMBER feature
(http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sql.htm#CHDJDGDG).

DBMS_SQL is a very powerful way of writing dynamic SQL, but that's for another article.

(Edit to add)
The most common purpose for ref cursors is where we have a front end application
(e.g. a .NET or Java application) that submits parameters to a function or procedure on the
database and is given a ref cursor back so that it can then retrieve that data and display it
appropriately for that front end. This allows the database layer to be kept seperate from the
user interface layer of the application development. The Application itself does not need to
know how to query that particular database, but just simply expects that the results will be
presented back in a pre-determined format (i.e. it knows what columns to expect to be returned
by the ref cursor).
Summary

In summary, the one key thing to remember is..

REF CURSORS ARE NOT DATA CONTAINERS. ONCE OPENED,
THEY ARE SIMPLY A POINTER TO A QUERY THAT HAS YET TO FETCH DATA.