Problem: We opened an SR with Oracle and they want us to run a diagnostics test for a payment process request. How do I run a diagnostics test in E-Business Suite?
Solution: Diagnostic tests in EBS are run from the Application Diagnostics responsibility.
Problem: Some purchase orders are stuck in the “In Process” status. How do I reset these purchase orders?
Solution: Refer to How To Reset a Purchase Order or Requisition From In Process or Pre-Approved To Incomplete/Requires Reapproval For Isolated Cases (Doc ID 390023.1)
Problem: After cloning the production EBS instance, user submits the Subledger Period Close Exceptions Report, but it does not complete.
Solution: Post-processing of the request is points to the production instance (for some reason). Run AutoConfig to point it back to the clone.
Formatting Data File
There are eleven columns in the data file, in the following order:
The Excel worksheet must be formatted before exporting to Comma-Separated Values (CSV). There must be NO commas in any cell before exporting the data to CSV. The SQL*Plus script parses the data by commas, so any of the fields with commas will cause the program to fail
Remember to format the Amount Applied column without commas.
Preparing Data File
The file is usually saved with a .csv extension. It is renamed to remove spaces from the filename (8-character file name and 3-character extension). The data file can be named such that the data it contains can be identified from the name. For example, creatcs1.dat.
The first row of the data file contains the column headings of the Excel worksheet. Remember to delete the first row in the data file before uploading the file to the server.
Uploading Data File
Once prepared, the data file is uploaded to the server via FTP. From the Command Prompt, use ftp to log on to the server (as oraprod user) and change to the directory to upload the data file to:
| Server Name | Upload Directory |
| test | /raid01/oraclone/custload |
| ora | /raid01/oraprod/custload |
After uploading the data file, log on to the server using Telnet or PuTTY. Navigate to the upload directory (cd /raid01/oraclone). Copy and paste the data file to ‘creatcsh.dat’. To obtain the number of rows in the file, execute the command:
$ cat creatcsh.dat | wc
Preparing SQL*Plus Environment
Load SQL*Plus on the client machine (not server) and log on to the instance as user oraprod. The scripts automatically sets the ORG ID and serveroutput settings in SQL*Plus, so it is not necessary to set it manually.
Since data from a text file is accessed from the SQL procedure, we must tell the instance the directory to search for the data file. To set this up, execute the ‘CREATE DIRECTORY’ SQL command and specify the directory on the server that the data file is located in:
SQL> CREATE DIRECTORY CLONEDATA3 as ‘/raid01/oraclone’;
To verify that the directory has been set up, query the DBA_DIRECTORIES view:
SQL> select * from DBA_DIRECTORIES;
Running the Create Cash API script
The SQL code for loading the data is in the creatcsh.sql file, located in C:SCRIPTS. The code can be run by either copying/pasting the SQL code, or by running the script:
SQL> @ C:Scriptscreatcsh.sql
Note that the changes are not committed, you must manually commit the changes once you are satisfied:
SQL> commit;
This documentation shows how to set up the TT Receipts printer in Oracle Applications.
Step 1: Set up a new Printer Style. Switch to the System Administrator responsibility. Navigate to Install > Printer > Style.
Style Name: TT_RECEIPT
Seq: 53
User Style: TT_RECEIPT
SRW Driver: ghlrecpt
Description: TRINIDAD RECEIPT STYLE
Columns: 132
Rows: 33
Step 2: Set up a new Printer Driver. Navigate to Install > Printer > Driver.
Driver Name: TT_RECEIPT
User Driver: TT_RECEIPT
Description: TRINIDAD RECEIPT DRIVER
SRW Driver: ghlrecpt
Driver Method: Command
Arguments: lp -o nobanner -c -d$PROFILES$.PRINTER -n$PROFILES$.CONC_COPIES -t”$PROFILES$.TITLE” $PROFILES$.FILENAME
Initialization: /eE
Reset: /eE
Step 3: Add the new Printer Style and Driver to a Printer Type. Navigate to Install > Printer > Type.
Type: EPSON
Description: PCL
Style: TT_RECEIPT
Driver: TT_RECEIPT
Step 4: Add the new Printer. Navigate to Install > Printer > Register.
Printer: DotMatrix
Type: EPSON
Description: Cashier Dot Matrix
Step 5: Associate the Printer Style and Name with the Receipt concurrent program. Navigate to Concurrent > Program > Define.
Program: TT_RECEIPT
Short Name: TT_RECEIPT
Application: Custom Application
Description: AR Receipt Printing Program
Executable Name: TT_RECEIPT
Method: Oracle Reports
Use in SRS: Checked
Restart on System Failure: Checked
NLS Compliant: Checked
Style: TT_RECEIPT
Style Required: Checked
Printer: DotMatrix
The following is the contents of the recpt.prt file (located in /raid01/oraprod/oracle/prodappl/fnd/11.5.0/reports directory on the oradata server):
printer “dec LN03 Portrait”
height 33
width 80
between pages control(L)
return “”
linefeed control(M) control(J)
code “bold on” esc “[1m”
code “bold off” esc “[0m”
code “underline on” esc “[4m”
code “underline off” esc “[0m”
Obtaining Data File
User passes the Microsoft Excel spreadsheet for the agent-customer relationships from analyst. Analyst extracts the customer number and agent number onto a new worksheet in Excel and saves the worksheet as type “Formatted Text (Space delimited)”. A sample of the data file is shown below:
01AAS00101PGCMM 999999
01ARE00101PGCMM 999999
01BLI00101PGCMM 999999
01BMA00101PGCMM 999999
01CPA00101PGCMM 999999
01CTR00101PGCMM 999999
01DEQ00101PGCMM 999999
The customer number is usually about 15 characters wide, right padded with spaces to 22 characters. The agent number is 6 characters wide.
This data file is passed to the DBA for loading.
Preparing Data File
The exported file is saved with a .prn extension by default. Rename the file (in Command Prompt) to an 8.3 format (8-character file name and 3-character extension). The data file can be named such that the data it contains can be identified from the name. For example:
| Type of Listing | File name |
| Provisor Customer Relationships | provcust.dat |
| Quantum Customer Relationships | quancust.dat |
The first row of the data file contains the column headings of the Excel worksheet. This header row must be deleted, so that there is only data in the file.
Uploading Data File
Once prepared, the data file is uploaded to the server via FTP. From the Command Prompt, use ftp to log on to the server (as oraprod user) and change to the directory to upload the data file to:
| Server Name | Upload Directory |
| testdata | /raid01/oraclone/custload |
| oradata | /raid01/oraprod/custload |
After uploading the data file, log on to the server using Telnet or PuTTY. Navigate to the upload directory (cd /raid01/oraclone). Rename (copy and paste) the data file to ‘datafile.dat’. To obtain the number of rows in the file, execute the command (shown in bold):
$ cat datafile.dat | wc
This command returns three figures. The first figure is the number of lines in the file. Verify that the data file has the correct number of rows to upload.
Preparing SQL*Plus Environment
Load SQL*Plus on the client machine (not server) and log on to the instance as user oraprod. Set the ORG ID and serveroutput settings in SQL*Plus before running the SQL procedure:
SQL> exec dbms_application_info.set_client_info(’21’);
SQL> set serveroutput on
Recall that ‘21’ refers to the ID of the Trinidad Operating Unit. Since the customer data is tied to the ORG ID, you must ensure that the correct ORG ID is set before running the code.
Since data from the text file is being accessed from the SQL procedure, the instance must have access to the file system. We must tell the Oracle instance the directory to search for the data file. To set this up, execute the ‘CREATE DIRECTORY’ SQL command and specify the directory on the server that the data file is located in:
SQL> CREATE DIRECTORY CLONEDATA3 as ‘/raid01/oraclone’;
To verify that the directory has been set up, query the DBA_DIRECTORIES view:
SQL> select * from DBA_DIRECTORIES;
Note that you only need to run the ‘CREATE DIRECTORY’ command once.
Running the TCA API
The SQL code for loading the data is in the tcaapi.sql file, located in C:SCRIPTS. The code can be run by either copying/pasting the SQL code, or by running the script:
SQL> @ C:Scriptstcaapi.sql
Note that the changes are not committed, you must manually commit the changes once you are satisfied the script completed without errors:
SQL> commit;
Errors Encountered
Problem: The API code is run with several dozen records in the data file and the following error is thrown:
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at “SYS.DBMS_OUTPUT”, line 32
ORA-06512: at “SYS.DBMS_OUTPUT”, line 97
ORA-06512: at “SYS.DBMS_OUTPUT”, line 112
ORA-06512: at line 85
Solution: This error is caused by the put_line statements. To overcome this, disable printing output from the procedure (set the value of c_print_data to ‘N’), or remove all put_line statements. Alternatively, set serveroutput to a large buffer:
SQL> set serveroutput on size 1000000
Problem: The code runs and completes successfully, but does not read or populate any records.
Solution: The record header from Excel still exists in the data file. Remove the headers and rerun.
SQL> @ utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 08-30-2007 18:57:10
.
**********************************************************************
Database:
**********************************************************************
–> name: PROD
–> version: 9.2.0.8.0
–> compatible: 9.2.0
–> blocksize: 8192
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
–> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 7139 MB
–> CTXD tablespace is adequate for the upgrade.
…. minimum required size: 17 MB
–> APPLSYSD tablespace is adequate for the upgrade.
…. minimum required size: 18597 MB
–> CCTD tablespace is adequate for the upgrade.
…. minimum required size: 7 MB
–> ARD tablespace is adequate for the upgrade.
…. minimum required size: 268 MB
–> OKCD tablespace is adequate for the upgrade.
…. minimum required size: 33 MB
–> AMVD tablespace is adequate for the upgrade.
…. minimum required size: 7 MB
–> XNPD tablespace is adequate for the upgrade.
…. minimum required size: 9 MB
–> XDPD tablespace is adequate for the upgrade.
…. minimum required size: 8 MB
–> IEOD tablespace is adequate for the upgrade.
…. minimum required size: 4 MB
–> IBUD tablespace is adequate for the upgrade.
…. minimum required size: 1 MB
–> IEMD tablespace is adequate for the upgrade.
…. minimum required size: 8 MB
–> JTFD tablespace is adequate for the upgrade.
…. minimum required size: 63 MB
–> ASOD tablespace is adequate for the upgrade.
…. minimum required size: 7 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 58 MB
–> APPS_UNDOTS1 tablespace is adequate for the upgrade.
…. minimum required size: 286 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: –> “streams_pool_size” is not currently defined and needs a value of
at least 50331648
WARNING: –> “session_max_open_files” needs to be increased to at least 20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
— No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
–> “optimizer_max_permutations”
–> “row_locking”
–> “undo_suppress_errors”
–> “log_archive_start”
–> “max_enabled_roles”
–> “enqueue_resources”
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] VALID
–> JServer JAVA Virtual Machine [upgrade] VALID
…The ‘JServer JAVA Virtual Machine’ JAccelerator (NCOMP)
…is required to be installed from the 10g Companion CD.
–> Oracle XDK for Java [upgrade] VALID
–> Oracle Java Packages [upgrade] VALID
–> Oracle Text [upgrade] VALID
–> Oracle XML Database [install]
–> Real Application Clusters [upgrade] INVALID
–> Oracle interMedia [upgrade] VALID
…The ‘Oracle interMedia Image Accelerator’ is
…required to be installed from the 10g Companion CD.
–> Spatial [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: –> Passwords exist in some database links.
…. Passwords will be encrypted during the upgrade.
…. Downgrade of database links with passwords is not supported.
WARNING: –> Deprecated CONNECT role granted to some user/roles.
…. CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 10g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. CTXSYS
…. ORDSYS
…. MDSYS
WARNING: –> Database contains INVALID objects prior to upgrade.
…. USER APPS has 4 INVALID objects.
…. USER DISCOVERER has 1 INVALID objects.
…. USER PERFSTAT has 1 INVALID objects.
…. USER SPOTLIGHT has 2 INVALID objects.
…. USER SYS has 5 INVALID objects.
…. USER VTAS_I3_ORCL has 40 INVALID objects.
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
–> New “SYSAUX” tablespace
…. minimum required size for database upgrade: 500 MB
.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> spool off
| Sno | Patch Number | Description | Remarks |
| 1 | 4238286 | Tums | |
| 2 | 4712852 | AD.i.4 Patch | Got An issue while applying on |
| Apps Node while Relinking Refer | |||
| Log Notes | |||
| 3 | 5107107 | AutoConfig Update | |
| Patch | |||
| 4 | 4318672 | Technology Stack | |
| Utility Patch | |||
| 5 | 9iAS Installation | This Patch will Install | This Step includes set of |
| iAS and Developer | Patches which is same in Main | ||
| Project Plan | |||
| 6 | 9i Upgrade | Database Upgrade | This steps includes set of |
| Patches which is same in Main | |||
| Project Plan | |||
| Note : After Database Upgrade Process | |||
| Got an issue while Starting up the Instance | |||
| Refer log Notes | |||
| 7 | 3180164 | New Product and New TBS | |
| 8 | 3480000 | Maintenance Patch | Got an Issue while applying on Admin Node |
| refer to Log Notes and also it took 3 days on | |||
| admin node | |||
| 9 | 3275245 | Online Help Patch | This Patch Will be Applied after Go-Live |
| 10 | 5117525 | Jinitiator Upgrade Patch | This patch should apply as a Part of Jinitiator |
| 11 | JDK Upgrade | ||
| 12 | 5759055 | TXK and ADX patch | This Patch is should be applied as part of 10g |
| Upgrade as Pre-Reqs | |||
| 13 | 4653225 | 10g Upgrade | This Patch should as 10g pre-req Upgrade |
| 14 | 10g Upgrade | Actual 10g upgrade process | |
| 15 | 5753621 | Receivables Patch | |
| 16 | 3636980 | Diagnostic Patch | |
| 17 | 5529008 | Diag Support Patch | |
1. Check the Invalid Objects in the existing Production Instance and Spool the output
2. Check the Size of existing SGA in Production
3. Check the Number of Tablespaces along with free space
4. Check the Number of Datafiles/Log/Control files in Production
5. Run Autoconfig on both Both Nodes just to make sure that autoconfig run successful on both nodes…Note: This must be done when users logout from application
6. Relink any Executable eg.FNDLIBR using adadmin or adrelink command on both nodes just to make sure that there is no relink issue for any module
7. Check the Installed or Shared Modules on existing Production Instance along with it Patch Level
8. Turn Off Archive Log mode
Note: Except step 5,6,8 spool the output and store it for further reference
Additional steps:
a. Obtain URL, port of instance
b. Obtain username and password of essential Oracle users (apps, system, sys) and OS users (applmgr, root)
c. Obtain username, password for user with System Administrator responsibility, to test concurrent programs (e.g. Active Users)
d. Determine and note location of database files, log files, control files, archive log files
e. Note location of top directories (APPL_TOP, COMMON_TOP, ORA_TOP, DB_TOP, DATA_TOP)
f. Determine status of archivelog mode (and turn off if necessary)
g. Record PERL location and version
h. Rerun AutoConfig on database, application tiers to ensure it is working
I. Run adrelink on database, application to ensure it is working
j. Source environment and ensure environment variables correct (e.g. APPLCSF)
k. Ensure UNIX commands available (cc, ld, make/gnumake, ar)
Recent Comments