How To Reset a Purchase Order or Requisition From In Process or Pre-Approved To Incomplete/Requires Reapproval For Isolated Cases

How To Reset a Purchase Order or Requisition From In Process or Pre-Approved To Incomplete/Requires Reapproval For Isolated Cases

These scripts allow the user to resubmit the document for approval by resetting the authorization status when documents are stuck in 'In-Process' or 'Pre-Approved' statuses.
The scripts are:

poxrespo.sql - to reset Standard, Blanket, Planned and Contract purchase orders
poresrel.sql - to reset Blanket and scheduled releases
poresreq.sql - to reset Internal and purchase requisitions

In order to obtain the scripts apply the proper patch. The scripts will be available in the $PO_TOP/sql directory.
It is strongly encouraged to use these reset scripts where there are single (or few) occurrences of documents getting stuck. It is not necessary to submit a Service Request for authorized use of these scripts and if any issues arise after proper use of one of the reset scripts the issues will be fully supported by Oracle Support. Often where there are single occurrences that cannot be replicated, root
cause investigations cannot be performed. But if there are continued repetitive occurrence of such issues, then these should be reported to Oracle Support.

Doc ID 390023.1

How to find the versions of Technology stack components like Forms, iAS, Framework, JDK, OJSP, Database, etc.?

How to find the versions of Technology stack components like Forms, iAS, Framework, JDK, OJSP, Database, etc.?

Applies to:

Oracle E-Business Suite Technology Stack - Version 11.5.10.2 to 12.2 [Release 11.5.10 to 12.2] on any platform.

On Application Tier

1. Establish the needed environment parameters by sourcing the Applications environment file as the owner of the application tier file system.
2. Ensure "APPLRGF" variable is set in environment. If not, set it to the same value as "APPLTMP" (Note: If you set the output file directory using -outfile parameter, skip this step and the file will be generated under $APPLRGF/TXK directory).
3. Navigate to <FND_TOP>/patch/115/bin. Run the utility/command  as follows :

Operation System	Command Line
Unix or Linux	$ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl \ -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP \ -contextfile=$CONTEXT_FILE \ -appspass=apps \ -outfile=$APPLTMP/Report_App_Inventory.html  Note: - Run the above command in single line - Remove the '\' after each parameter while you run this command in a single line - Supply the apps user password as is applicable for parameter "-appspass"
Windows	%ADPERLPRG% %FND_TOP%\patch\115\bin\TXKScript.pl -script=%FND_TOP%\patch\115\bin\txkInventory.pl -txktop=%APPLTMP% -contextfile=%CONTEXT_FILE% -appspass=apps -outfile=%APPLTMP%\Report_App_Inventory.html Note: - Run the above command in single line - Remove the '\' after each parameter while you  run this command in a single line - Supply the apps user password as is applicable for parameter "-appspass"

Where:

txktop	Temporary working directory use by Perl Modules. Should not be an empty string.
contextfile	Location of the context file. If not passed, default is picked from the environment.
appspass	APPS schema password. If not passed, default password is used.
outfile	Location for the report being generated. If not passed, the default location is <APPLTMP>/TXK

To generate the report in text format, the parameter "-reporttype=text" needs to be passed to the above commands. For example:

outfile=$APPLTMP/Report_App_Inventory.html -reporttype=text

4. Once the command executes successfully, it should generate the report file in the location specified for "outfile" parameter in above script
5. In case you have more than one application tier's (multi-node architecture), follow the steps 1-4 on each of the Application tiers
6. Upload the report output file (default: $APPLTMP/Report_App_Inventory.html )to Oracle Support for review

On Database Tier

1. Establish the needed environment parameters by sourcing the Database environment file as the owner of the Database tier file system. |
2. Ensure "ORACLE_HOME" variable is set in environment and correctly pointing to your database ORACLE_HOME.
3. Navigate to <ORACLE_HOME>/appsutil/bin. Run the utility/command as below (all on a single line): 

Operation System	Command Line
Unix or Linux	$ADPERLPRG $ORACLE_HOME/appsutil/bin/TXKScript.pl -script=$ORACLE_HOME/appsutil/bin/txkInventory.pl -txktop=$ORACLE_HOME/appsutil/temp -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$ORACLE_HOME/appsutil/temp/Report_DB_Inventory.html Note: - Run the above command in single line - Supply the apps user password as is applicable for parameter "-appspass"
Windows	%ADPERLPRG% %ORACLE_HOME%/appsutil/bin/TXKScript.pl -script=%ORACLE_HOME%/appsutil/bin/txkInventory.pl -txktop=%ORACLE_HOME%/appsutil/temp -contextfile=%CONTEXT_FILE% -appspass=apps -outfile=%ORACLE_HOME%/appsutil/temp/Report_DB_Inventory.html Note:  - Run the above command in single line - Supply the apps user password as is applicable for parameter "-appspass"

Where

txktop	Temporary working directory use by Perl Modules. Should not be an empty string.
contextfile	Location of the context file.
appspass	APPS schema password.
outfile	Location for the report being generated.

To generate the report in text format, the parameter "-reporttype=text" needs to be passed to the above commands. For example: 

outfile=$ORACLE_HOME/appsutil/temp/Report_DB_Inventory.html -reporttype=text

4. Once the command executes successfully, it should generate the report file in the location specified for "outfile" parameter in above script
5. Upload the report output file (default: $ORACLE_HOME/appsutil/temp/Report_DB_Inventory.html ) to Oracle Support for review

 Specific Component

E-Business Suite Version.

Connect to the database as user apps and run the following sql.

SQL> select release_name from apps.fnd_product_groups;

Workflow Version.

Connect to the database as user apps and run the following sql.

SQL> select distinct TEXT Version from WF_RESOURCES where NAME = ‘WF_VERSION’;

Database version. 

Connect to the database with sqlplus as sysdba and run next query:

SQL> select * from v$version;

Web Server or Application Server version.

Oracle EBS 11i 

Logon the Applications Middle-tier as the Application owner, using ssh terminal.
Set the correct environment variables and run next command:

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd –version

Forms version.

Logon the Applications Middle-tier as the Application owner, using ssh terminal..
Set the correct environment variables and run next command:

Oracle EBS 11i 

$ORACLE_HOME/bin/f60run | grep Version | grep Forms

Oracle EBS 12.2

$ORACLE_HOME/bin/frmcmp_batch | grep Version | grep Forms

Reports version.

Oracle EBS 12.2

Logon the Applications Middle-tier as the Application owner, using ssh terminal.
Set the correct environment variables and run next command:

$ORACLE_HOME/bin/rwrun | grep Release

  

Oracle Java Plug-in version. 

Logon the Applications Middle-tier as the Application owner, using ssh terminal.
Set the correct environment variables and run next command:

grep plugin $CONTEXT_FILE

OA Framework Version.

Oracle EBS 11i 

Logon the Applications Middle-tier as the Application owner, using ssh terminal.
Set the correct environment variables and run next command:

adident Header $FND_TOP/html/OA.jsp
adident Header $OA_HTML/OA.jsp

Weblogic server version.

Oracle EBS 12.2

Logon the Applications Middle-tier as the Application owner, using ssh terminal.
Set the correct environment variables and run next command:

cat $FMW_HOME/wlserver_10.3/.product.properties | grep WLS_PRODUCT_VERSION

Fusion Middle Ware (FMW) version.

Oracle EBS 12.2

Logon the Applications Middle-tier as the Application owner, using ssh terminal.

Set the correct environment variables.
Set the correct ORACLE_HOME variable:
E.g. export ORACLE_HOME=/u001/oracle/R122/FMW_Home/Oracle_EBS-app1

Run next command:
$ORACLE_HOME/OPatch/opatch lsinventory

Jinitiator version.

Logon the Applications Middle-tier as the Application owner, using ssh terminal.
Set the correct environment variables and run next command:

grep jinit_ver_comma $CONTEXT_FILE

  

End Dating Seeded Responsibilities

End dating seeded responsibilities that are not assigned to user, meaning no users are assigned to the responsibility

--Get List of responsibilities

SELECT fr.responsibility_id,
               fr.application_id,
               fr.data_group_application_id,
               fr.data_group_id,
               fr.menu_id,
               fr.web_host_name,
               fr.web_agent_name,
               fr.group_application_id,
               fr.request_group_id,
               fr.responsibility_key,
               frt.responsibility_name,
               frt.description,
               fr.start_date,
               fr.version
          FROM fnd_responsibility_tl frt, fnd_responsibility fr
         WHERE frt.responsibility_name in ('India Local Order Management')
               AND frt.language = 'US'
               AND fr.responsibility_id = frt.responsibility_id;
             
             
--Run Below API to end date responsibility

/* Responsibility End Date */
DECLARE
    CURSOR c1
    IS
        SELECT fr.responsibility_id,
               fr.application_id,
               fr.data_group_application_id,
               fr.data_group_id,
               fr.menu_id,
               fr.web_host_name,
               fr.web_agent_name,
               fr.group_application_id,
               fr.request_group_id,
               fr.responsibility_key,
               frt.responsibility_name,
               frt.description,
               fr.start_date,
               fr.version
          FROM fnd_responsibility_tl frt, fnd_responsibility fr
         WHERE     frt.responsibility_name IN
                       ('India Local Order Management')
               AND frt.language = 'US'
               AND fr.responsibility_id = frt.responsibility_id;
BEGIN
    FOR i IN c1
    LOOP
        BEGIN
            FND_RESPONSIBILITY_PKG.UPDATE_ROW (
                X_RESPONSIBILITY_ID           => i.responsibility_id,
                X_APPLICATION_ID              => i.application_id,
                X_WEB_HOST_NAME               => i.web_host_name,
                X_WEB_AGENT_NAME              => i.web_agent_name,
                X_DATA_GROUP_APPLICATION_ID   => i.data_group_application_id,
                X_DATA_GROUP_ID               => i.data_group_id,
                X_MENU_ID                     => i.menu_id,
                X_START_DATE                  => i.start_date,
                X_END_DATE                    => SYSDATE - 1,
                X_GROUP_APPLICATION_ID        => i.group_application_id,
                X_REQUEST_GROUP_ID            => i.request_group_id,
                X_VERSION                     => i.version,
                X_RESPONSIBILITY_KEY          => i.responsibility_key,
                X_RESPONSIBILITY_NAME         => i.responsibility_name,
                X_DESCRIPTION                 => i.description,
                X_LAST_UPDATE_DATE            => SYSDATE,
                X_LAST_UPDATED_BY             => -1,
                X_LAST_UPDATE_LOGIN           => 0);

            COMMIT;

            DBMS_OUTPUT.put_line (
                i.responsibility_name || ' has been updated !!!');
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
        END;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
/

Verify Below Documents and take decision based on usage of applications/modules in your Organization.
What Is The Impact Of Disabling Oracle Seeded Users? (Doc ID 418767.1)
Is it Safe To End Date Some Applications Users like GUEST (Doc ID 783428.1)
End Dating Seeded Responsibilities (Doc ID 390448.1)

End Date all users in EBS 11i after cloning

End Date all users in EBS 11i after cloning

select count(*) from fnd_user;

create table fnd_user_bkp as select * from fnd_user;


DECLARE
CURSOR cur1
IS
SELECT user_name
FROM fnd_user
WHERE user_name NOT IN ('SYSADMIN', 'GUEST')
AND (end_date IS NULL OR TRUNC(end_date) > TRUNC (SYSDATE));
BEGIN
FOR all_user IN cur1
LOOP
fnd_user_pkg.updateuser (x_user_name => all_user.user_name,
x_owner => 'SYSADMIN',
x_end_date => SYSDATE
);
COMMIT;
END LOOP;
END;


How to End-Date/Delete an Applications User (Doc ID 1064798.1)

Tips & Tricks for Navigating Oracle Applications

Tips & Tricks for Navigating Oracle Applications

Help : Keyboard Help or – Lists Hot Keys

Tools : Close Other Forms – check box to auto close forms when a new form is opened

Query Tips and Tricks
to enter query by example
to recall the last query parameters
to Execute Query
Query : Count Matching Records (after putting in the query parameters)
Exit Query

Query Wild Card Characters
% one or more characters
_ exactly one character

Query Operators
#between and
#is null, #is not null
> Greater Than
>= Greater Than or Equal
< Less Than <= Less Than or Equal = Equal != Not Equal View : Record : First Record or Last Record – shows first or last record returned by query

What SQL Statement did the Oracle Applications run to fill the form?
Help : Diagnostics : Examine (requires apps password)
Block = SYSTEM
Field = last_query

Other ways to determine the underlying table structure:
1) Help : Record History
2) Trace the form (requires apps password)

XML Publisher
The XML Publisher concurrent program joins the XML concurrent report data with the registered templates to generate PDF output. The following template types are supported: Portable Document Format (PDF), Rich Text Format (RTF) and XSL-FO.
- www.norcaloaug.org, Training Day 2004, Paper by Brent Mosher,
‘Oracle Tech update part 1: XML Publisher/Oracle Applications Framework’
- Metalink Note:269605.1, ‘About Oracle XML Publisher Release 4.5’
- Metalink Note:276691.1, ‘XML Publisher and Concurrent Manager Integration’
- Metalink Note:258332.1, ‘About Oracle XML Publisher Minipack XDO.H’
- Metalink B13817-01, ‘Oracle® XML Publisher User’s Guide Release 11i