Query to find the list of Parameters of a Concurrent Program

select column_seq_num
      ,END_USER_COLUMN_NAME      
      ,description
      ,enabled_flag
      ,required_flag
      ,display_flag
      ,flex_value_set_id
from FND_DESCR_FLEX_COL_USAGE_VL a
where DESCRIPTIVE_FLEXFIELD_NAME='$SRS$.FNDMDGEN' --FNDMDGEN is short name of CP

Oracle Apps - Messages

This is one of several “Oracle Applications Basics” articles that are aimed at oracle applications program developers and cover a number of commonly used development elements when interacting with the E-Business Suite.

This article covers the use of application messages and focuses on the use of the pl/sql package FND_MESSAGE as well as the use of the generic loader to download and upload messages between your environments.
During the development of custom programs it is common to use customised messages in our code to implement error messages, confirmation messages, log entries etc…
These messages will be seen and interpreted by your end users and it is important to ensure that these messages can easily be changed in the event that your customer wishes to do so. The reason for changing them maybe simple such as a spelling mistake or more complex like assigning a generic error code to all error messages used by the system to aid in support activities.
The Oracle E-Business suite provides a message library that allows us to implement our messages and at the same time provides the facility to easily change these messages via the E-Business suite standard user interface.
This article will cover the creation of messages via the E-Business suite user interface and the implementation of message retrieval via the pl/sql API package provided with Oracle Applications.
Creating an Oracle E-Business Suite Message
To create a message in the E-Business suite message library you will need the “Application Developer” responsibility. Once logged in navigate to Application Developer > Application > Messages. This will launch a form and it will look like figure 1:

 

Figure 1

 

Enter a unique name for your message, for custom messages this is often in the form XX_NN_MM where
XX is the custom schema prefix you are using (Usually just XX)
NN is the module code or short name for the extension that the message belongs
MM the message description
I would advise that you always use a unique NN portion to the message name to make it easy for you to identify your messages i.e. you can query back all messages for XX_MYMOD%, this will help you later when you are compiling your messages for your installation scripts.
Select the language that your message is written in and the application that the message belongs, this will usually be the custom application setup by your development team lead. If you don’t yet have a custom application and you are developing custom application modules then you will need one.
Enter the message text in the “Current Message Text” box.
Click the save icon.
For message naming conventions you should always consult your build standards documentation for the customer you are implementing for to ensure you have observed any standard development approach that may have been implemented at your site.
Example:

Figure 2

Retrieving a message using PL/SQL
Once we have saved the message in E-Business suite via the standard message form we need to interact with the message library via our custom code to enable us to implement the message in the way it was intended to be used.
In order to retrieve the message from the database we need to use a few different standard foundation API’s in the FND_MESSAGE package. An E-Business suite message should be retrieved as follows:
1. Clear the current session of any message variables that may already be set
2. Tell E-Business suite which message you wish to retrieve
3. Retrieve the actual message string
4. Clear the session (Not required but good practice)
This process will look like this in your PL/SQL
For more details regarding fnd_global.apps_initialise see my other blog entry “Oracle Apps Basics – Session Context”

set serveroutput on   DECLARE   my_message VARCHAR2(100); BEGIN   --Initialise Apps Session    fnd_global.apps_initialize( user_id      => 1290                           ,  resp_id      => 50257                           ,  resp_appl_id => 10003                            );    --Clear the existing session   FND_MESSAGE.CLEAR;    --Tell ebusiness suite which message you want (custom application short name/message name)      FND_MESSAGE.SET_NAME('XX','XX_MYMOD_MESSAGE1');    --Retrieve the message   my_message := FND_MESSAGE.GET;       --Output the message   DBMS_OUTPUT.PUT_LINE(my_message); END;   anonymous block completed Test Message 1

Using Tokens
The Oracle E-Business suite allows the substitution of tokens within a message string to enable the programmer to add dynamic content to the message at run time. This is useful for adding things like timestamps and user names to the existing message.
We will expand the example used in the previous section to demonstrate the use of tokens in our messages.
Open the E-Business Suite message creation form and query back your previous message.
In order to insert a token into a message it is necessary to prefix the token with a ampersand e.g. modify your message text from “Test Message 1″ to “Test Message Retrieved at &TIMESTAMP by user &USERNAME”
Tokens can be called what ever you want but they must be prefixed with an ampersand and they must be in uppercase.
To demonstrate the use of our new tokens we will add an additional 2 API calls to our previous code which will set the tokens to the desired values and then resolve the message using the substituted values, our code will now look like this:

 
 
 
set serveroutput on
 
DECLARE
 my_message VARCHAR2(100);
BEGIN
 --Initialise Apps Session
 fnd_global.apps_initialize( user_id      => 1290
                          ,  resp_id      => 50257
                          ,  resp_appl_id => 10003
                           );
 
 --Clear the existing session
 FND_MESSAGE.CLEAR;
 
 --Tell ebusiness suite which message you want (custom application short name/message name)
 FND_MESSAGE.SET_NAME('XXC','XX_MYMOD_MESSAGE1');
 
 --Set the timestamp message token with the current database timestamp 
 FND_MESSAGE.SET_TOKEN('TIMESTAMP',to_char(SYSDATE,'DD-MM-YYYY HH:MI:SS'));
 
 --Set the username message token with the current applications user
 FND_MESSAGE.SET_TOKEN('USERNAME',FND_GLOBAL.USER_NAME);
 
 --Retrieve the message
 my_message := FND_MESSAGE.GET;
 
 --Output the message
 DBMS_OUTPUT.PUT_LINE(my_message);
END;
 
anonymous block completed
Test Message Retrieved at 02-04-2009 03:16:59 by user TURLEYK


 
 

Downloading and Uploading Messages using the Generic Loader
The generic loader can be used to download/upload existing E-Business Suite messages.
To download our example message we would use the following command at the Unix prompt on the mid-tier:

 
  
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct MSG_XX_MYMOD_MESSAGE1.ldt 
FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XX' MESSAGE_NAME="XX_MYMOD_MESSAGE1"
 
 
 

To Upload our example message we would use the following command at the Unix prompt on the mid-tier:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct MSG_XX_MYMOD_MESSAGE1.ldt

What are the difference between DDL, DML and DCL commands?

1)Query-select
2)DDL - Data Definition Language: statements used to define the database structure or schema. Some examples:

* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object

3)DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples:

* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency

4)DCL - Data Control Language. Some examples:

* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command

5)TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

6)System Control Statements

These statements change the properties of the Oracle database instance. The only system control statement is ALTER SYSTEM. It lets users change settings, such as the minimum number of shared servers, kill a session, and perform other tasks.

7)Embedded SQL Statements
These statements used in a procedural language program, such as those used with the Oracle precompilers. Examples include OPEN, CLOSE, FETCH, and EXECUTE.

To disable the Concurrent Program from Backend

To disable the Concurrent Program from Backend
whenever sqlerror continue
SET ECHO OFF
SET SERVEROUTPUT ON
SET LINESIZE 200

BEGIN
  fnd_program.enable_program('XXCFIRPORINV',     --CP Short Name
                             'Cisco Financials', -- Application Name
                             'N');               --Enable Flag 'Y' or 'N' 
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Main Exception : '||sqlerrm);
END;
/