Change Short Name of Concurrent Program in Oracle Applications

It is a very common requirement for All Oracle Apps Consultant, to change the Short Name of a Concurrent Program.

The hard way of doing it is delete the entire concurrent program and re-create the new one.

However, there are a simpler ways of doing this :

LDT Method :

1) Download the LDT for existing concurrent program (incorrect short code ) and manually make changes in the downloaded LDT File after making changes in (User Concurrent Program Name , you cannot have the same concurrent program user name ) and the desired short code correct one.

Copy To (Option - Applications Screen)
 

2) One very simpler method of doing the same is using the Copy Option in Oracle Applications Screen.
    It allows you the feasibility of creating a concurrent program with different username and short-code either within the same or in different application.

1. Change Short Name of Concurrent Program

It is a common problem that at times we make a concurrent program with an incorrect Short Name and then we struggle to correct the same. There are various ways of correcting the same:

1)

Copy to:

We can use the functionality

Copy to

Provided in the Oracle Applications Screen as shown below: You can copy the Program within the same or different application with a different Program Name, Short Name. After Creating the New Program , you can delete the old program from the screen using then delete option

Please have a look and do give your feedback.

Oracle FNDLOAD Scripts

Download Script

Upload Script

1. Lookups
-- -------------
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"
 
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
 
2. Concurrent Program
-- -----------------------------
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"
 
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
 
3. Profile
-- ---------
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"
 
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
 
4. Request Set and Link
-- ------------------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'
 
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
 
5. FND Message
-- ---------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"
 
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
 
6. D2K FORMS
-- ------------------
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME"
      
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
 
7. Form Function
-- ---------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"
 
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
 
8. Alerts
-- ---------
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"
 
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE
 
9. Value Set
-- --------------
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"
 
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
 
10. Data Definition and Associated Template
--- ----------------------------------------------------------
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'
 
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt
 
11. DATA_TEMPLATE (Data Source .xml file)
--- ----------------------------------------------------------
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME
 
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME
 
12. RTF TEMPLATE (Report Layout .rtf file)
--- -------------------------------------------------------
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME
 
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

Executable Delete Script

/***************************************************

*PURPOSE: DELETE  EXECUTABLE FROM BACK-END  

*AUTHOR: Shravan Purohit 

*SYNTAX:

 DELETE_EXECUTABLE (PROGRAM_SHORT_NAME, APPLICATION_SHORT_NAME)               

***************************************************/

DECLARE

   L_EXE_SHORT_NAME    VARCHAR2 (240);

   L_APPL_SHORT_NAME   VARCHAR2 (240);

BEGIN

   -- SET THE VARIABLES FIRST

   L_EXE_SHORT_NAME := 'XX_MOTRAS_SHORT_EXE'; -- CONCURRENT EXECUTABLE SHORT NAME

   L_APPL_SHORT_NAME := 'PO';                        -- APPLICATION SHORT NAME

   -- SEE IF THE EXECUTABLE EXISTS. IF FOUND, DELETE THE PROGRAM

   IF FND_PROGRAM.EXECUTABLE_EXISTS (LV_EXE_SHORT_NAME, L_APPL_SHORT_NAME)

   THEN

      FND_PROGRAM.DELETE_EXECUTABLE (LV_EXE_SHORT_NAME, L_APPL_SHORT_NAME);

      COMMIT;

      DBMS_OUTPUT.PUT_LINE (L_EXE_SHORT_NAME || ' Deleted Successfully');

   -- IF THE PROGRAM EXECUTABLE DOES NOT EXIST IN THE SYSTEM

   ELSE

      DBMS_OUTPUT.PUT_LINE (L_EXE_SHORT_NAME || ' Not Found');

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);

END;

What is $FLEX$ and $PROFILES$?

$FLEX$ and $PROFILES$ are Special Variables in oracle Apps environment and are used to hold values at runtime.
Every Oracle Apps technical consultant will be familiar with the term $FLEX$ and $PROFILES$. Whenever we are working with value sets, we will be using both of these for modifying the data fetched, for basing the values of a parameter on the other parameter and also fetching the Profile Option values.
To segregate this based on the functionality
$FLEX$: Used for basing the value of a parameter on another parameter.
$PROFILES$: used for fetching the data stored in the specified profile option value which is currently active.

Where is it used?

Both these variables are used in the Parameter form of a Concurrent Program and are used at the Where Clause window in the value set of Table type.

Syntax:

:$FLEX$.previous_value_set_name
Important:

• $FLEX$ must always be in capitals.
• A ‘:’ must precede the declaration of $FLEX$.
• The previous value set name must have already been assigned and saved on a different parameter.

:$PROFILES$.Profile_option_name

Important:

• $PROFILES$ must be always in capitals.
• ‘:’ must always precede the declaration.
• Profile option name is the Profile Name and not to be confused with the User profile Name.

Some use of the Special Variables are as below:
Pre-Requisites:
Created an Executable and a concurrent program which is registered in the Application Object Library. The Query for the report is as below:

SELECT e.ename, e.empno, e.sal, e.hiredate, e.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE d.deptno = :x_deptno;
The name of the concurrent program is taken as “XX_Checking_flex_and_profile_use”

Scenario 1:

Changing the value of Parameter B based on the Parameter A:
In this, we created two value sets and they are: XX_SCENARIO_1 and XX_Sub1

Steps:
Create the value set XX_SCENARIO_1 using the validation type as ‘Table’.
Create the second value set XX_Sub1 using the validation type as ‘Table’ and in the where clause field specify the following code:
where deptno <= :$FLEX$.XX_SCENARIO_1
Working:
To check the working of this concurrent program, lets submit this
In the picture here, the First parameter contains no value, so the second parameter is disabled as the WHERE clause in value set is equated to NULL, making the field disabled.


When a value is selected in the first parameter, the second parameter gets enabled and when the LOV is clicked, it shows the departments which are in department number 20 and below it, as we have specified <= in the where clause.

Scenario 2:Use of :$FLEX$ in the default type option of a Parameter form
Steps:

The query used in the Default Value test field is as below: Select dname from dept where deptno =:$FLEX$.XX_SCENARIO_1
Ensure that the default value for the field Department Number is given as SQL Statement.
Select deptno from dept where deptno=20

Working:

Lets submit the concurrent program and check.

Since the default value was given as a SQL statement, the result of that is shown in the first parameter and the second parameter has the value based on the first parameter as specified in the SQL statement.


Scenario 3:

Use of $PROFILES$ in where clause and Default Value. It is done in the same way as is used in $FLEX$.



Modifying the where clause using $PROFILES$ returns Successful upon validation.





Scenario 4:

Use of Parameter name instead of a value set name in the $FLEX$ Special Variable.
Where Clause of the value set of table type using the parameter name.

Reason:
When we provide the name of the value set in $FLEX$ then we will be able to change the value of the parameter which is dependent on it. If the value set is used by more than one parameter then it becomes difficult to get the exact value in the dependent parameter as it will fetch the values based on the last used value of the value set. It could have been last used by the first parameter or any other parameter in the form. To avoid this kind of confusion, the alternative is to provide the name of the Parameter itself in the $FLEX$ variable. No matter how many times the value set is used, if we provide the parameter name, we will get the dependent parameter value based on its parent parameter only. Let me explain this using the pictures below:

Where Clause of the value set of table type using the parameter
name.



The first and third parameter are based on value set names and the second and fourth parameter are based on parameter name. Since the second parameter is not initialized, the third parameter (value set based )is also not initialized.




Since the latest value is from Second parameter, hence the value in third is based on the second parameter. The third value is based on the value of the second parameter and is irrespective of the value set. Shown in the next picture.





Here the third parameter takes the latest value of the value set, hence shows the department name corresponding to that and the fourth parameter values are dependent on the value of the second parameter.







These were some of the basic uses of $FLEX$ and $PROFILES$. Feel free to post your queries and comments on this topic.