How to Migrate Reports from 11i to R12?

We had a big challenge when to migrate the oracle rdf reports from version 11i to R12. Most of our consultants used RA_CUSTOMERS, RA_ADDRESSES, RA_CUST_SITES views to get the customer related information in the report. As you know, these tables/views are no longer used in R12.

Here, This is the trick i have used. I have created 3 views, which is based on the HZ_% tables like XX_RA_CUSTOMERS, XX_RA_ADDRESSES and XX_RA_CUST_SITES. Now, in all old rdf reports i haved included XX_ infront of RA_ and solved the issue.

There is one more easy way to solve this issue. But I have one question in this? Tell me, shall we go in this way.

Common Question is... Instead of making XX_RA_CUSTOMERS, why cannot we create a view in the same name as RA_CUSTOMERS and drop the RA_CUSTOMERS table? I know we should not touch the oracle seeded objects. But, As per oracle R12 technical design this tables are no more...!

Hope this will help you. For more updates keep watching................

What is the Difference Between Lexical Parameter and Bind Parameter?

Bind references/Bind Parameters are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY, and START WITH clauses of queries. Binds may not be referenced in the FROM clause.
An example is:
SELECT Col1,Col2
FROM XX_table
WHERE Col1 = :P_col1



Lexical references/Parameters are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY , ORDER BY , HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value.
An example is:
SELECT Col1,Col2
FROM &TABLE

How to install Oracle Applications 11i?

Through this post I will cover things you need to install Oracle Apps 11i (11.5.10.2) on Linux.
Installing Oracle Applications , Become Apps DBA II
Install Oracle Apps
Install Oracle 11i (11.5.10.2) on Linux


Software
1. Software for Oracle 11i can be downloaded from edelivery.oracle.com(46 zip files) or you can order disks via metalink account.
2. Unzip these files and this should create five directory startCD, oraAppDB, oraApps, oraDB, oraiAS

O. S. Requirement
1. Linux Package : make sure you have ar, gcc, g++, ld, ksh, make, X Display Server
2. Disk Space : 26 GB for Application Tier & 65 GB for Vision Database (31 GB for fresh database). Hence total 57 GB for fresh DB & 91 GB for Vision Instance.
3. If you are installing apps 11.5.10.2 from Staging area you need another 24GB (No need if you are doing install from inserting CD’s on prompt)
4. O.S. User : Create two operating system user, avis11i (Application Tier) & ovis11i (Database Tier) both belonging to group DBA. You can use single user as well for both Application Tier & Database Tier
5. JDK : No need to install JDK 1.4.2 on solaris, Linux or windows as 11i Installer will install it. For other operating system you need to install JDK 1.4.2
6. For full list of RPM’s required on Red Hat Enterprise Linux Visit Metalink Note # 303859.1

Things you must do/know before installation
A) set environment variable LD_ASSUME_KERNEL=2.4.19 for root user
B) gcc -v (Should be of version 3.2.x) For any other version
mv /usr/bin/gcc /usr/bin/gcc.orig
mv /usr/bin/g++ /usr/bin/g++.orig
ln -s /usr/bin/i386-redhat-linux-gcc32 /usr/bin/gcc
ln -s /usr/bin/i386-redhat-linux-g++32 /usr/bin/g++

C) If your installation fail some time restart installation by option restart like
“rapidwiz -restart”
D) If you try to access apps from linux machine from browser other than Mozilla (like KDE)you will get error message that “You have insufficient privileges for current operation”
E) Installer should be started from root user

Starting Installation
1. Go to Staging directory /stage_location/startCD/Disk1/rapidwiz/
2. ./rapidwiz
3. Create base directory under which you wish to install database & apps tier with proper permission (owner) (Five main directory - [SID]appl,[SID]comn,[SID]ora owned by application user (avis11i) & [SID]db, [SID]data owned by database o.s. user (ovis11i) )

Post Install
1. Set environment variable in O.S. user profile
/install_base/[sid]appl/APPSORA.env (For Application Tier User, avis11i)
/install_base/[sid]db/SID_hostname.env (For Application Tier User, ovis11i)

2. For start/stop services of Oracle Apps 11i visit
11i Startup/Shutdown Scripts

Error/Issues during 11i Install
Issue : RW-50004: Error code received when running external process. Check log file for details. Running Database Install Drivers for VIS Database

Check Logsat : $install_base/[sid]db/9.2.0/appsutil/log/[SID]_[hostname]/MMDDHHMM.log
Error while running adlnkoh.sh, log for adlnkoh.sh at $install_base/[sid]db /9.2.0/install/make.log
In make.log file libgcc_s.so : undefined reference to `dl_iterate_phdr@GLIBC_2.2.4`

Fix : This issue is very well explained in Note : 363600.1 ‘dl_iterate_phdr@GLIBC_2.2.4′Error on Linux Running Rapidwiz

References:
303859.1 Requirements for Installing Oracle 9iR2 on RHEL 4
http://download.oracle.com/docs/cd/B25516_18/current/acrobat/r115102ins.pdf

How to get Customer Address Details in Oracle apps?

The following Query will provide the customer address details in oracle applications.

SELECT  hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
  FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
  WHERE hp.party_id = hps.party_id(+)
       AND hp.party_id = hca.party_id(+)
       AND hcasa1.party_site_id(+) = hps.party_site_id
       AND hcasa2.party_site_id(+) = hps.party_site_id
       AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
       AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
       AND hcsua1.site_use_code(+) = 'bill_to'
       AND hcsua2.site_use_code(+) = 'ship_to'
       AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hps.location_id = hl.location_id
       AND hl.country = ftt.territory_code
       AND ftt.LANGUAGE = USERENV ('lang')
  ORDER BY customer_number;

How to know Which User is Locked the table?

SELECT do.owner

      ,do.object_name

      ,do.object_type

      ,fu.user_name locking_fnd_user_name

      ,fl.start_time locking_fnd_user_login_time

      ,vs.module

      ,vs.machine

      ,vs.osuser

      ,vlo.oracle_username

      ,vs.sid

      ,vp.pid

      ,vp.spid AS os_process

      ,vs.serial#

      ,vs.status

      ,vs.saddr

      ,vs.audsid

      ,vs.process

FROM fnd_logins      fl

    ,fnd_user        fu

    ,v$locked_object vlo

    ,v$process       vp

    ,v$session       vs

    ,dba_objects     do

WHERE vs.sid = vlo.session_id

AND vlo.object_id = do.object_id

AND vs.paddr = vp.addr

AND vp.spid = fl.process_spid(+)

AND vp.pid = fl.pid(+)

AND fl.user_id = fu.user_id(+)

AND do.object_name = :P_TABLE_NAME

AND nvl(vs.status,'XX') != 'KILLED';

  

Above query will give you the details of user who locked the table table.

The input parameter of this query is object name.

How to know the application is multi-org or not?

SELECT multi_org_flag
FROM   fnd_product_groups;

This Query help us to getthe multi org status in oracle applications.

Kindly Let me know If I am wrong...

Thanks,

K Rajashekar Reddy 

How to know the version in oracle apps?

Solution:

select * from v$version;

This query will give you the oracle apps version.

What are the various Types of Table in Oracle Apps?

Different Types of Tables in Oracle Applications 11i and R12

In oracle applications there are some tables that end with some suffixes. I am providing those tables below.

_X	Current information table..there is no date tracking .
_V	View created on base table. Mostly forms are created based on this views
_TL	Tables that support multi language.
_VL	View created on multi language tables. The view generally uses the base table and _tl table
_F	This indicates that these are the date tracking tables. These tables are generally seen for HRMS and contain 2 common columns effective_start_date and effective_end_date
_S	sequence related tables
_DFV /_KFV	The DFF/KFF table created on the base table. This is the best way to get the concatenated value of DFF/KFF. Also using this table the values can be queried based on the DFF/KFF name and not attributes column.
_ALL	Contains multi org data. There will be similar table without _ALL. Before querying this data the environment variable needs to be set. Dbms_application_info.set_client_info('org_id'), or apps_initialize can be used to set the environment variable.common column.
_B	The Main base tables

How to find Onhand Quantity at given date?

The following query displays the onhand quantity information.

The query inputs the Item ID, organization ID and date.

SELECT   SUM (target_qty)
       , item_id
FROM     (SELECT   moqv.subinventory_code subinv
                 , moqv.inventory_item_id item_id
                 , SUM (transaction_quantity) target_qty
          FROM     mtl_onhand_qty_cost_v moqv
          WHERE    moqv.organization_id = :org_id
          AND      moqv.inventory_item_id = :item_id
          GROUP BY moqv.subinventory_code
                 , moqv.inventory_item_id
                 , moqv.item_cost
          UNION
          SELECT   mmt.subinventory_code subinv
                 , mmt.inventory_item_id item_id
                 , -SUM (primary_quantity) target_qty
          FROM     mtl_material_transactions mmt
                 , mtl_txn_source_types mtst
          WHERE    mmt.organization_id = :org_id
          AND      transaction_date >= TO_DATE (:hist_date) + 1
          AND      mmt.transaction_source_type_id =
                                               mtst.transaction_source_type_id
          AND      mmt.inventory_item_id = :item_id
          GROUP BY mmt.subinventory_code
                 , mmt.inventory_item_id) oq
GROUP BY oq.item_id