Oracle APPS (Oracle Application E-BUSINESS SUITE) 11i - R12

Thursday, 2 July 2015

We are not able to create DFF in RCV_SHIPMENT_LINES in R-12.1.3 or How to create DFF in RCV_SHIPMENT_LINES in R-12.1.3

We are not able to create DFF in RCV_SHIPMENT_LINES in R-12.1.3

How to solved the issue:

Please find the Navigation

Navigation:
Application Developer responsibility
Flexfield > Descriptive > Register
Query on Title field for Shipment Destination Details
Select Find Name of PO_PDF_SHIP_LINES_DESTINATION autopopulates.

Select Find
Descriptive Flexfields form is displayed
Select Columns button
Attributes are listed in column.

The Attributes will all be enabled, as they are defaulted to be located within
PO_PDF_SHIP_LINES_DESTINATION.
Uncheck (disable) each Attribute you wish to use in RCV_SHIPMENT_LINES.
Then check the attributes you want to use in RCV_SHIPMENT_LINES, for those fields to be available in the Descriptive Flexfield Segment definition.
Access RCV_SHIPMENT_LINES; the Attributes should now be listed there for use.

Create Running Total Or Create Variables Values In Oracle XML Report

Running Total OR Variables Values
The variable functionality can be used to add a running total to your invoice listing report.

<?xml version="1.0" encoding="WINDOWS-1252"?>
<INVOICES>
<INVOICE>
<INVNUM>10001-BRIJESH</INVNUM>
<INVDATE>1-Jan-2005</INVDATE>
<INVOICEAMT>100</INVOICEAMT>
</INVOICE>
<INVOICE>
<INVNUM>10001-TESTING</INVNUM>
<INVDATE>10-Jan-2005</INVDATE>
<INVOICEAMT>200</INVOICEAMT>
</INVOICE>
<INVOICE>
<INVNUM>10001-REPORT</INVNUM>
<INVDATE>11-Jan-2005</INVDATE>
<INVOICEAMT>150</INVOICEAMT>
</INVOICE>
</INVOICES>

And Create rtf layout and use Variables function.

To create the Running Total field, define a variable to track the total and initialize it to 0.

Output.

DB UPGRADE 9206 TO 10203

System Overview
Version 11.5.10.2
Oracle Applications Vision Instance
OS: Redhat Linux 5 update 5
Type: Single Node instance
Applications OS User: appl11i
Database 9i OS User : db11i (same user for 9i and 10g)
9i Database Home = /d011/db11i/db/oaproddb/9.2.0
10g Database Home = /d011/db11i/db/oaproddb/10.2.0

Instance SID: DB11i
Host: demo
url:demo.conacent.com:8000
listener port:1521

Task – To Upgrade database from 9.2.0.6 to 10.2.0.3 for Oracle
Applications 11.5.10.2.

Pre Upgrade Steps

   a> Apply the patches - 5478710,4676589, 4653225
   b> Create a new home for Oracle 1
   c> Install Oracle Database 10g.
   d> Install the companion CD.
   e> Install 10.2.0.3 patch set
   f> Create nls/data/9idata directo
   g> Install 10g Listener

Upgrade Steps

   a> Use manual steps or dbua to upgrade the db from 9.2.0.6 to
      10.2.0.3. Manual upgrade is preferred in the industry

Post Upgrade Steps

   a> On the application tier generate appsutil.zip file
   b> Copy the appsutil.zip file generated on application tier to the
      database tier inside 10g Oracle Home
   c> create a context file on database tier
   d> run autoconfig

Pre Upgrade Steps

a> Patches Required – 5478710, 4676589, 4653225
5478710 - TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O (December 2006)
4676589 - ATG Roll up 4
4653225 - 11.5.10 INTEROP PATCH FOR 10GR2

Note: After apply patch 5478710(before run autoconfig) change LD_ASSUME_KERNEL TO XD_ASSUME_KERNEL BOTH db and appl
      than runs autocfg.

###########################################################################################################

1   Before install 10g software to change
     vi /etc/redhat-release
    change:Red Hat Enterprise Linux ES release 4 (Nahant Update 2)

2.change inventory location

     #inventory_loc=/etc/oraInventory
     inventory_loc=/d011/db11i/db/oaproddb/10.2.0/inventory

3.change oratab location:
     cat oratab

       DB11i:/d011/db11i/db/oaproddb/10.2.0:N

4.Install 10 g software only

5. before companios cd installion
set variable in bash_profile

export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'
#. /d011/db11i/db/oaproddb/10.2.0/DB11i_demo.env
export PATH

ORACLE_BASE=/d011/db11i/db/oaproddb
export ORACLE_BASE

ORACLE_HOME=/d011/db11i/db/oaproddb/10.2.0/
export ORACLE_HOME

6.Install Companions cd.

Note: In a Product to install
      Oracle Database 10g Producst 10.2.0


7. Install 10g patchset3

   p5337014_10203_LINUX.zip

#####################################################################################################
1.Copy dbs,appsutil,admin,.env,network/admin in 9i to 10g.

2. Replace all the file with 9.2.0 to 10.2.0.

3. set .bash_profile to new oracle_home

4. Runs create perl cr9idata.pl

5.   Set ORA_NLS10

   vi .env($oracle_home)10g
if test "db920" = "db101" -o "db920" != "db102"; then

6.Apply opatch in 10g (before upgrade)
   p5648872_10203_LINUX.zip

7.Make the below changes in initTEST.ora (10g)

#optimizer_max_permutations
#row_locking
#undo_suppress_errors
#max_enabled_roles
#enqueue_resources
#sql_trace
streams_pool_size=50331648
large_pool_size = 8388608
session_max_open_files=20
optimizer_features_enable=10.2.0.3
compatible=10.2.0.

8.shutdown 9i db and 9ilistener

9.set vi .bash_profile in new home(10g)

10.oratab set old home(9.2.0)

11. upgrade database

   a.Manual

    cd $ORACLE_HOME/bin

    sqlplus “/as sysdba”

     startup upgrade

12. Create sysaux tablespace

CREATE TABLESPACE sysaux DATAFILE '/d01/oracle/singlenode/visdata/sysaux01.dbfSIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO
   ONLINE;

13.    Run catupgrade.sql
   cd $ORACLE_HOME/rdbms/admin
   @catupgrade.sql

b. For dbua
     cd $ORACLE_HOME/bin
    ./dbua
############################################################################################################

Post Upgrade Steps:

Some more fixes

i) Fix Korean lexers
a. Connect to sqlplus as sysdba
b. execute $ORACLE_HOME/ctx/sample/script/drkorean.sql

ii) Run adgrants.sql
a. Connect to sqlplus as sysdba
b. Execute @$APPL_TOP/admin/adgrants.sql APPLSYS

iii) Grant create procedure privilege on CTXSYS
a. Connect to sqlplus with apps
b. Execute @$AD_TOP/patch/115/sql/adctxprv manager CTXSYS
manager is the SYSTEM password

Added few steps

i) Gather Statistics for SYS schema
a. sqlplus “/as sysdba”
b. shutdown immediate
c. startup restrict
d. @/oracle/tst10appl/admin/adstats.sql
e. shutdown
f. startup

ii) Recreate grants and synonym for apps
a. Log in to server with applmgr user
b. Execute adadmin
c. Choose -> Maintain Applications Database Entities menu
d. Choose -> Re-create grants and synonyms for APPS schema

iii) On the application tier run the below command to generate
appsutil.zip file

perl /a01/appl11i/appl/oaprodappl/ad/11.5.0/bin/admkappsutil.pl

iv) Next step is to create a context file on database tier. Run the
below commands with appropriate values. This will generate context file
on database tier

$ORACLE_HOME/appsutil/bin
perl adbldxml.pl tier=db appsuser=apps appspasswd=apps

Note:copy jre folder in $ORACLE_HOME/appsutil to 10 oracle_home/appsutil/ than cretae context file

v) Once the context file is created on db tier, run autoconfig.

vi)start Application and Db

----------------------------------------------------------------------------------------------------------------
Login Page

-----------

   checking URL = http://demo.conacent.com:8000/OA_HTML/AppsLogin

   RW-50016: Error: - {0} was not created:
      File = {1}

apply opatch 5890966 for login page issue.

11i TO R12 UPGRADE

Database : 10.2.0.3
Application: 11.5.10.2

Step 1. Apply the below patches in your existing 11.5.10.2 environment

Shut down the entire E-Business Suite environment and perform a full backup
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12

Step 2.   Run rapidwiz to create R12 code tree.

Step 3.    Move the new environment file into .bash_profile(R12)

Step 4.    Now onwards, you are connected to R12 application

-- Apply patch AD.A 4502962

Step 5.   Download the NLS Release 12 patch (4440000) for each active language in the system.

-- Run the American English upgrade patch driver
-- Takes 40 hours

Step 6. Disable maintenance mode

Step 7. Run admkappsutil.pl in application.

Step 8.   copy appsutil.zip to database(10g)

Step 9.   run adautocfg in db.

Step 10. Run rapidwiz to configure and start processes.

How to update uncosted or error costed transactions for reconsideration by Cost Manager for Costing

update mtl_material_transactions
set costed_flag = 'N',
error_code = NULL,
error_explanation = NULL,
transaction_group_id = NULL,
transaction_set_id = NULL
where organization_id = &organization_id
and costed_flag in ('N','E');

How to find uncosted or error costed data in Material Transaction

select *
from mtl_material_transactions
where costed_flag in ( 'E','N');

Tuesday, 30 June 2015

Security Rule to restrict user from choosing / selecting Account code combination values(COA Flex Field)

SECURITY RULE

Security Rule for R12 is defined to restrict the access of the user to certain segment values when used according to its Applications and Responsibility wise . Segment value security rules can work alone or with data access set security that secures data in ledgers, balancing segment values, or management segment values.

Step1: Navigation:
General Ledger Super User --> Financials--> Key-->Segments.

Step 2: Uncheck the check box Freeze Flex field Definition , Go to Segments , Open the Segment Value, Check the check box Security Enabled.

Step 3: Navigate to Define Security Rules window.

General Ledger Super User --> Financials--> Key-->Security-->Define

Choose the Value set Option.

Step 4: In the Security Rule Elements block,select from

Two Security Types in the Security Rule.

Include The range of values that falls under the security rules criteria.

Exclude The range of values that doesn't falls under the security rules criteria.

Enter the low (From) and high (To) ends of this value range.

Step 5: Go to Assign Tab in order to assign to the specific Application and Responsibility.

Friday, 26 June 2015

How to configure LCM(Landed Cost Management) or Setup steps for LCM configuration(Pre-Receiving and LCM as Service or Black Box))

LCM SETUP STEPS

Step 1: Create New Account Code
For Example like as shown below:

Step 2: Define Child Range

Step 3: Set Profile Option as Follows

QP: Blind Discount Option as Yes at Application Level (Oracle Landed Cost Management) .
QP: Licensed for Product as Purchasing at Application Level (Oracle Landed Cost Management) .
QP: Pricing Perspective Request Type as Purchase Order at Application Level (Oracle Landed Cost Management) .
QP: Pricing Transaction Entity as Procurement at User Level.
QP: Source System Code as Oracle Purchasing at User Level.
INL: Default Shipment Type for Open Interface as per your set up.(After Completion of Shipment Type Setup)
INL: Volume UOM Class
INL: Quantity UOM Class
INL: Weight UOM Class
INL: Default Currency Conversion Type for QP Charges Generation
Choose the functional currency or for others currencies, a conversion rate should be recorded in "Daily Rates" form (Setup->Currency->Rates->Daily) using Conversion Type .

Step 4: Define Cost Factors
Oracle Purchasing-->Setup-->Purchasing-->Cost Factors-->Create