:$FLEX$.VALUE_SET_NAME to set up value sets where one segment depends on a prior segment that itself depends on a prior segment (“cascading dependencies”)

FLEX Syntax

WHERE CLAUSE

WHERE EXISTS (SELECT 1
FROM PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
MTL_SYSTEM_ITEMS_B MSI
WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND PRHA.AUTHORIZATION_STATUS = ‘APPROVED’
AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
AND PRLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PRLA.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND (PRLA.LINE_LOCATION_ID IS NULL
OR PRLA.AUCTION_HEADER_ID IS NULL)
AND PRDA.PROJECT_ID = :$FLEX$.XX_SECURED_PROJECTS_ID
AND SUBSTR(MSI.SEGMENT1,1,4) = ITM.ITEM_GROUP
AND NVL(PRLA.SUGGESTED_BUYER_ID,0) = NVL(:$FLEX$.PO_SRS_BUYER_ID:NULL, NVL(PRLA.SUGGESTED_BUYER_ID,0))
AND PRHA.SEGMENT1 BETWEEN NVL(:$FLEX$.P_PR_FROM:NULL, PRHA.SEGMENT1) AND NVL(:$FLEX$.P_PR_TO:NULL, PRHA.SEGMENT1)
AND TRUNC(APPROVED_DATE) BETWEEN fnd_date.canonical_to_date(:$FLEX$.P_FROM_DATE)
AND fnd_date.canonical_to_date(:$FLEX$.P_TO_DATE))

How to use Oracle Message Dictionary in Oracle Apps – FND MESSAGES?

Oracle Message Dictionary provides flexibility to store pre-formatted text as part of its catalog to display them as error/warning/note messages in Forms, Log Files, OAF Pages, Reports, etc.,

These messages mainly provide information about business rule errors, such as missing or incorrect data, and how to resolve them, warn about the consequences of intended actions, inform about the status of an application, pages, or business objects, and indicate that processes and actions are performing or are completed.

Advantages of Message Dictionary:

  1. Change or translate the message text to different languages without regenerating or recompiling the code.
  2. It provides consistent look and feel of the message text, since it is pre-formatted.

Steps to Create a Message from Oracle Forms:

Navigate to Application Developer responsibility –> Application –> Messages

Create a Message from Oracle Forms

Steps to Create a Message from OAF Page:

Navigate to Functional Administrator responsibility –> Core Services –> Messages

Create a Message from OAF Page

Purpose of different fields on Messages form

COMPONENT NAME DESCRIPTION
Name Every message must have a unique name. You should include a unique prefix that makes it easier to find your custom messages and that helps to avoid name conflicts with non-custom messages.
Language Select the language that your message is written in.
Application Select the application that the message belongs, this will usually be the custom application.
Current Message Text Message text is required. This is a brief statement of the operation attempted and the problem that occurred as a result, or information that the user needs to know. The maximum field size for messages stored in the Message Dictionary is 240 characters.
Number A unique and persistent message number can be included with each message. When displayed, the number takes the format of (Application ShortnameNumber). If the message does not have a message number, the formatted number is not displayed.
Type The message type indicates which message components are applicable, determines whether implicit logging and incident creation occurs, and determines the logging level if the message is logged.
Maximum Length Maximum number of display characters the translators can use to translate the message.
Description Description of the Message.
Alert Category This will allow user interfaces and other programs to filter exception messages based on category. The types are Product, System, Security and User.
Alert Severity This will allow user interfaces and other programs to filter exception messages based on severity. The types can be: Critical, Error or Warning.
Log Severity This group indicates the Log severity levels like: Unexpected, Error, Exception, Event, Procedure, Statement or Off.

How to use Message Dictionary in PL/SQL Procedures

Oracle has provided FND_MESSAGE API to Set, Retrieve, Clear the messages in Message Stack.

Below is a small example to get the message text:

DECLARE
  msg VARCHAR2(2000);
BEGIN
  fnd_message.set_name ('FND', 'XX_TEST_ORACLE_ERP_APPS_GUIDE');
  msg := fnd_message.get;
  dbms_output.put_line(msg);
END;
/

where ‘FND’ is the application short name in which the message is defined and ‘XX_TEST_ORACLE_ERP_APPS_GUIDE’ the name of the message.

FND_MESSAGE.SET_NAME : this Sets a message name in the global area without actually retrieving the message from Message Dictionary.

FND_MESSAGE.GET : Retrieves a translated and token-substituted message from the message stack and then clears that message from the message stack. GET returns up to 2000 bytes of message.

Learn More about FND Message Functions, Click Here

Tokens to change Message content dynamically

Tokens are identified in the message text by their use of ampersand (&) or curly brackets ({}) and all uppercase letters. The token values are supplied at run time by the code that raises the message. For example, the following token &USER is replaced by a user name when the user receives this message on their screen:

New User Creation

DECLARE
  l_user_name VARCHAR2(200);
  msg         VARCHAR2(2000);
BEGIN
  --
  SELECT user_name
  INTO l_user_name
  FROM fnd_user
  WHERE user_id = '84857';
  --
  fnd_message.set_name ('FND', 'XX_TEST_CREATE_USER');
  fnd_message.set_token('USER', l_user_name);
  msg := fnd_message.get;
  dbms_output.put_line(msg);
END;
/

Using Fnd Message on OAF page

Below line can be used to show FND_MESSAGE from oaf page  which is created using Application Developer responsibility.
import oracle.apps.fnd.common.MessageToken;
public class xxcusHelloWorldMainCO extends HelloWorldMainCO {
 
    public void processFormRequest(OAPageContext pageContext,
                                   OAWebBean webBean) {
 
     if (pageContext.getParameter("Go") != null) {
String getUserName = String.valueOf(pageContext.getUserName());
     MessageToken[] msgtoken = {new MessageToken("USER",getUserName) };
throw new OAException("FND", "XX_TEST_CREATE_USER", msgtoken, OAException.ERROR, null);
    }
}
Autoconfig log file:
Apps:
$INST_TOP/appl/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adconfig.log $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/NetServiceHandler.log

Startup/Shutdown Log files:

$INST_TOP/logs/appl/admin/log

Apache, OC4J and OPMN:

$LOG_HOME/ora/10.1.3/Apache 
$LOG_HOME/ora/10.1.3/j2ee 
$LOG_HOME/ora/10.1.3/opmn

Patch log:

$APPL_TOP/admin/$SID/log/
Workflow Mailer log:
$APPLCSF/$APPLLOG/FNDCPGSC*.txt

Concurrent log:

$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log
OAM (Oracle Application Manager) log:
$APPLRGF/oam/

Clone log:

Preclone log files in source instance

Apps:
$INST_TOP/apps/$CONTEXT_NAME/admin/log/ (StageAppsTier_MMDDHHMM.log)

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)

Clone log files in target instance:

Apps :
$INST_TOP/apps/$CONTEXT_NAME/admin/log/ApplyAppsTier_.log

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log

– Alert Log File:
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

Concurrent OUT Directory:
$APPLCSF/$APPLOUT  or $APPLCSF/out
APPL_TOP – this is the top level directory for the Applications
  • $APPLCSF – the top level directory where the concurrent manager stores the log and out files of concurrent requests
  • $APPLCSF/$APPLLOG – concurrent request log files( As describe above)
  • $APPLCSF/$APPLOUT – concurrent request out files( As describe above)
  • $APPLTMP – Applications temporary files
  • $APPLPTMP – PL/SQL temporary files   or /usr/tmp (default tmp directory)
Startup and shutdown messages:
–Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web)
$INST_TOP/logs/ora/ (10.1.2 & 10.1.3)
$INST_TOP/logs/ora/10.1.3/Apache/error_log[timestamp]
$INST_TOP/logs/ora/10.1.3/opmn/ (OC4J~…, oa*, opmn.log)$INST_TOP/apps/$CONTEXT_NAME/logs/ora/10.1.2/network/ (listener log)
$INST_TOP/logs/appl/conc/log (CM log files)
Other log files in R12

1) Database Tier
1.1) Relink Log files :
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME /MMDDHHMM/ make_$MMDDHHMM.log

1.2) Alert Log Files :
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

1.3) Network Logs :
$ORACLE_HOME/network/admin/$SID.log

1.4) OUI Logs :
OUI Inventory Logs :
$ORACLE_HOME/admin/oui/$CONTEXT_NAME/oraInventory/logs

2) Application Tier

$ORACLE_HOME/j2ee/DevSuite/log
$ORACLE_HOME/opmn/logs
$ORACLE_HOME/network/logs

Tech Stack Patch 10.1.3 (Web/HTTP Server)
$IAS_ORACLE_HOME/j2ee/forms/log
$IAS_ORACLE_HOME/j2ee/oafm/log
$IAS_ORACLE_HOME/j2ee/oacore/log
$IAS_ORACLE_HOME/opmn/logs
$IAS_ORACLE_HOME/network/log
$INST_TOP/logs/ora/10.1.2
$INST_TOP/logs/ora/10.1.3
$INST_TOP/logs/appl/conc/log
$INST_TOP/logs/appl/admin/log

Patching related log files in R12 :
Application Tier– adpatch log – $APPL_TOP/admin//log/
Developer (Developer/Forms & Reports 10.1.2) Patch – $ORACLE_HOME/.patch_storage
Web Server (Apache) patch – $IAS_ORACLE_HOME/.patch_storage
Database Tier opatch log – $ORACLE_HOME/.patch_storage
Error:Delete/Purge COMPLETED concurrent requests

Concurrent request history should not exceed 50K rows, or else it will start impacting on performance

SQL> select count(*) from FND_CONCURRENT_PROCESSES;

COUNT(*)
———-
552

SQL> select count(*) from FND_CONCURRENT_REQUESTS;

COUNT(*)
———-
85009 ==>>> Should always be below 50K, Else run “Purge Concurrent Request”
SQL>

SQL> select count(*) from FND_CONCURRENT_REQUESTS

2 where request_date > sysdate-7;

COUNT(*)
———-
19757

SQL> col “Min request|Date” for a20;

SQL> col “Max request|Date” for a20;

SQL> select min(request_date) “Min request|Date”,max(request_date) “Max request|Date” from FND_CONCURRENT_REQUESTS;

Min request|Date Max request|Date
——————– ——————–
18-NOV-09 01-NOV-11

SQL>

To clean old history of “COMPLETED” requests, follow the below note, to purge the queue

Concurrent Processing Tables and Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) [ID 104282.1]

System Administrator -> Requests -> Run

Select “Single Request”

Mode = 14 days – delete all older than 14 days. Don’t make this window to small.

To view the above submitted request, do the following

Enter the request ID and “Find”

While Running:

Status on successful completion is shown below “Completed”, “Normal”


Extract from the Log File:

FNDCPPUR module: Purge Concurrent Request and/or Manager Data
+—————————————————————————+
Current system time is 01-NOV-2011 12:57:03
+—————————————————————————+
Purged 46616 entrie(s) from FND_CONCURRENT_REQUESTS :-01-NOV-2011 13:07:22

Purged 0 entrie(s) from FND_FILE_TEMP :-01-NOV-2011 13:07:22

Purged 7082 entrie(s) from FND_CRM_HISTORY :-01-NOV-2011 13:07:25

Purged 0 entrie(s) from FND_TM_EVENTS :-01-NOV-2011 13:07:25

Purged 346 entrie(s) from FND_TEMP_FILES :-01-NOV-2011 13:07:25

Purged 0 entrie(s) from FND_ENV_CONTEXT :-01-NOV-2011 13:07:46

Purged 1 entrie(s) from FND_DUAL :-01-NOV-2011 13:07:46

Purged 2 entrie(s) from FND_CONFLICTS_DOMAIN :-01-NOV-2011 13:07:46

+—————————————————————————+
Start of log messages from FND_FILE
+—————————————————————————+

When trying to enable “FND: Debug Log Enabled”, in the below window, it might sometime be that the option to set it to Yes/No, is grayed out, and cannot be changed. Without spending too much time fixing this, you can also enable/disable it from within a sqlplus session, connected as the apps user.
To enable it, do the following
SQL> SELECT PROFILE_OPTION_ID,PROFILE_OPTION_NAME FROM FND_PROFILE_OPTIONS_VL
WHERE START_DATE_ACTIVE <= SYSDATE
and NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
and ( SITE_ENABLED_FLAG = ‘Y’ or APP_ENABLED_FLAG = ‘Y’ or RESP_ENABLED_FLAG = ‘Y’ or USER_ENABLED_FLAG = ‘Y’ or SERVER_ENABLED_FLAG = ‘Y’ or SERVERRESP_ENABLED_FLAG = ‘Y’ or ORG_ENABLED_FLAG = ‘Y’)
and ( UPPER(USER_PROFILE_OPTION_NAME) LIKE ‘%FND%DEBUG%’
and (USER_PROFILE_OPTION_NAME LIKE ‘%f%’ or USER_PROFILE_OPTION_NAME LIKE ‘%F%’))
order by user_profile_option_name;
PROFILE_OPTION_ID PROFILE_OPTION_NAME
—————– ———————————–
             4176 AFLOG_ENABLED
             3098 AFLOG_FILENAME
             3099 AFLOG_LEVEL
             8470 AFLOG_BUFFER_MODE
             3100 AFLOG_MODULE
             8468 DEBUG RULE THRESHOLD
6 rows selected.
SQL>
Description: cid:image003.png@01CD17D0.99AE3620
SQL>
col profile_option_value for A20;
col profile_option_id for 999999999;
select PROFILE_OPTION_ID, PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES
where profile_option_id in (‘4176‘,’3098′,’3099’);
SQL>
Description: cid:image005.png@01CD17D0.99AE3620
SQL> update FND_PROFILE_OPTION_VALUES
set PROFILE_OPTION_VALUE = ‘Y’
where PROFILE_OPTION_VALUE = ‘N’
and PROFILE_OPTION_ID = 4176; 
Description: cid:image006.png@01CD17D0.99AE3620
2 rows updated.
SQL> commit;
Commit complete.
SQL>

col profile_option_value for A20;
col profile_option_id for 999999999;
select PROFILE_OPTION_ID, PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES 
where profile_option_id in (‘4176′,’3098′,’3099’);  => All will show ‘Y’ now

Description: cid:image007.png@01CD17D0.99AE3620
To disable it, just set it to NO again:
SELECT PROFILE_OPTION_ID,PROFILE_OPTION_NAME FROM FND_PROFILE_OPTIONS_VL
WHERE START_DATE_ACTIVE <= SYSDATE
and NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
and ( SITE_ENABLED_FLAG = ‘Y’ or APP_ENABLED_FLAG = ‘Y’ or RESP_ENABLED_FLAG = ‘Y’ or USER_ENABLED_FLAG = ‘Y’ or SERVER_ENABLED_FLAG = ‘Y’ or SERVERRESP_ENABLED_FLAG = ‘Y’ or ORG_ENABLED_FLAG = ‘Y’)
and ( UPPER(USER_PROFILE_OPTION_NAME) LIKE ‘%FND%DEBUG%’
and (USER_PROFILE_OPTION_NAME LIKE ‘%f%’ or USER_PROFILE_OPTION_NAME LIKE ‘%F%’))
order by user_profile_option_name
PROFILE_OPTION_ID    PROFILE_OPTION_NAME
—————–    ——————-
4176                 AFLOG_ENABLED
3098                 AFLOG_FILENAME
3099                 AFLOG_LEVEL
8479                 AFLOG_BUFFER_MODE
3100                 AFLOG_MODULE
8465                 DEBUG RULE THRESHOLD
col profile_option_value for A20;
col profile_option_id for 999999999;
select PROFILE_OPTION_ID, PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES
where profile_option_id in (‘4176‘,’3098′,’3099’);
PROFILE_OPTION_ID PROFILE_OP
—————– ———-
          3099       1
          4176       Y
          3098       NULL
SQL>

update FND_PROFILE_OPTION_VALUES
set PROFILE_OPTION_VALUE = ‘N’
where PROFILE_OPTION_VALUE = ‘Y’
and PROFILE_OPTION_ID = 4176;  => AFLOG_ENABLED will show ‘N’ now