How to check Versions of Apps, DB, Web server, Forms & Reports, Jinitiator, Java, pld, OA Framwork, Workflow, Discoverer etc.

Find component Version in Apps 11i/R12/12i
Q. How to find Apps Version (11i/R12/12i).
A.  Connect to database as user apps
SQL> select release_name from apps.fnd_product_groups;
Output like 12.0.4 or 11.5.10.2

Q. Web Server/Apache or Application Server in Apps 11i/R12
A. Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
Output for 11i should be like
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built:   Jan 26 2005 11:06:44 (iAS 1.0.2.2.2 rollup 5)
Output for R12 should be like
Server version: Oracle-Application-Server-10g/10.1.3.0.0Oracle-HTTP-Server
Server built:   Dec  4 2006 14:44:38
Q. Forms & Report version (aka developer 6i) in 11i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/f60run | grep Version | grep Forms
output like
Forms 6.0 (Forms Runtime) Version 6.0.8.25.2 (Production)
Check fourth character in version 25 which means Forms 6i patchset 16 (25-9)
.
Q. Forms & Report version in R12/12i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release
Output should be like
Report Builder: Release 10.1.2.2.0
You can safely ignore warnings
Q. Database Version in 11i/R12/12i
A. Go to database section below.
Q. Oracle Jinitiator in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE

(Default is Java Plug-In for R12/12i )
Q. Oracle Java Plug-in in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE
Q. File Version on file system
adident Header <filename>
or
strings <file_name> | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility
Q. Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx.  *.pll is in $AU_TOP/resource and to find its version check
adident Header $AU_TOP/resource/<filename>.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/<filename>.pll | grep -i header
FDRCSID(‘$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);
Q. OA Framework Version
A.http:// hostname.domainName:port/OA_HTML/OAInfo.jsp (Only for 11i)
A.  Log in as Application user, set environment variable and run below query
adident Header $FND_TOP/html/OA.jsp
adident Header $OA_HTML/OA.jsp
output for both should look like
$Header OA.jsp 115.60 2006/03/31 00:47:28 atgops1 noship $
120.21 means OA Framework Version (coming soon..)
115.60 means OA Framework Version (coming soon..)
115.56 means OA Framework Version (coming soon..)
115.36 means OA Framework Version 5.7
115.27 means OA Framework Version 5.6E
115.26 means OA Framework Version 5.5.2E
Q. Discoverer Version for 11i (3i or 4i)
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/disc4ws | grep -i Version
Q. Discoverer Version for 11i or R12 (10g AS)
Check under Application Server Section as 10g AS Discoverer is on standalone
Q. Workflow Version with Apps
A. Connect to Database as apps user
SQL> select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;
Output like 2.6.0 means workflow version 2.6.0
.
Version for Fusion Middleware Component

Identity Management component Version/Release Number
A. Oracle Single Sign On
Connect to database which holds SSO repository
SQL>select version from orasso.wwc_version$;
B. Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)
>>> To find software/binary version
$ORACLE_HOME/bin/oidldapd -version
output should look like
oidldapd: Release 10.1.4.0.1 – Production on mon jul 14 14:14:21 2008
Copyright (c) 1982, 2006 Oracle.  All rights reserved.
>>> To find Schema Version/ database use
ldapsearch -h <hostname> -p <port> -D “cn=orcladmin” -w “<password>” -b “” \
-s base “objectclass=*” orcldirectoryversion
and output should be like
version: 1
dn:
orcldirectoryversion: OID 10.1.4.0.1
or run following query in database
SQL> select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;
Output should be like OID 10.1.4.0.1
C. Application Server
1. Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version

Version=10.1.3.0.0
2. For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
SQL> select * from ias_versions;
or
SQL>select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;
.
D. AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version
.
E. Oracle Portal
SQL> select version from portal.wwc_version$;
.

Database Component
I) Oracle Database
To find database version
SQL> select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail
.
Oracle Enterprise Manager
Metalink Note 605398.1  How to to find the version of the main EM components
.
Unix Operating System
Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release
 .

Login to any user in R12. It is not showing any error on the screen, after login screen.

http://www.conacent.com


Problem -

Check the application.log is $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1
javax.servlet.ServletException: Application: FND, Message Name: FND_NO_DATABASE_CONNECTION

Solution :- 

Login Fails Intermittently With: Application: Fnd, Message Name: oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_NO_DATABASE_CONNECTION [ID 1298103.1]

12.1.3 CustomersApply and test patch 11832737


12.1.2 and 12.1.1 Customers
Apply and test patch 9908921

Flashbacks Time Base Recovery

http://www.conacent.com

Accidentally, the user executes a wrong query, probably with & without a WHERE clause, and COMMITS the data. Finally, when he queries the table to check the record count, he notices that all or some of the rows from the table are deleted. The Administrator uses the Flashback Query feature to restore the data to prior point of time, the data as existed some time minutes back.


1.  Suppose we have created one table named test
    SQL> create table test (name varchar2(10));
 



2. Insert few records into the table
    SQL> insert into test (name) values ('aa');



3. Check the date and time of the system
    SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

4. Delete all records or some of it.
    SQL> delete from test;
or
    SQL> delete from test where name='cc';






5. Commit after you have deleted the record
    SQL> commit;

6. Check the date and time of the system
    SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

7. Place the value  of <Date & Time> taken from step 3.
    If all the records are deleted then use the below query
    SQL> insert into test select * from test as of timestamp to_timestamp('<Date & Time>','dd-mm-yyyy hh24:mi:ss');

   If specific record is deleted then use the below query
   SQL> insert into test select * from test as of timestamp to_timestamp('<Date & Time>','dd-mm-yyyy hh24:mi:ss') where name='cc' ;

Direct Delivery Receipts (Lot enabled items) using Interface tables

Steps to upload Direct Delivery Receipts (Lot enabled items) using Interface tables:

1. Set the Profile option "RCV: Processing Mode" to "Batch" using System Administrator responsibility:
    System Administrator -> Profile ->RCV: Processing Mode.

2. Populate the given fields of the two interface tables i.e. RCV_HEADERS_INTERFACE and   RCV_TRANSACTIONS_INTERFACE .

3. In order to insert Lot Numbers the MTL_TRANSACTION_LOTS_INTERFACE table needs to be populated.
 For multiple lot insertion the above table needs to be populated with different lot numbers.

4. Run the Receiving Transaction Processor request from the front end Apps Screen.

/************************* Interface Tables Population ******************************/

/********** Population of RCV_HEADERS_INTERFACE table**********/
INSERT INTO RCV_HEADERS_INTERFACE
(HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
AUTO_TRANSACT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
VENDOR_ID,
SHIP_TO_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE,
VALIDATION_FLAG
)
VALUES
(rcv_headers_interface_s.nextval , --Header_Interface_Id
rcv_interface_groups_s.nextval, --Group_Id
'PENDING', --Processing_Status_Code
'VENDOR', --Receipt_Source_Code
'NEW', --Transaction_Type
'DELIVER', --Auto_Transact_Code
SYSDATE, --Last_Update_Date
0, --Last_Updated_By
0, --Last_Update_Login
SYSDATE, --Creation_Date
0, --Created_By
341, --Vendor_Id
87, --Ship_To_Organization_Id,
SYSDATE, --Expected_Receipt_Date
'Y' --Validation_Flag
);

/*********Population of RCV_TRANSACTIONS_INTERFACE table**********/
 INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
EMPLOYEE_ID,
AUTO_TRANSACT_CODE,
SHIP_TO_LOCATION_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
SOURCE_DOCUMENT_CODE,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
DESTINATION_TYPE_CODE,
-- DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
SUBINVENTORY,
HEADER_INTERFACE_ID,
DOCUMENT_NUM,
TO_ORGANIZATION_ID,
VALIDATION_FLAG
)
SELECT
rcv_transactions_interface_s.nextval, --Interface_Transaction_id
rcv_interface_groups_s.currval, --Group_id
SYSDATE, --Last_update_date
0, --Last_updated_by
SYSDATE, --Creation_date
0, --Created_by
0, --Last_update_login
'RECEIVE', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
2, --QUANTITY
'Kilogram', --UNIT_OF_MEASURE
9142 , --ITEM_ID
61, --EMPLOYEE_ID
'DELIVER', --AUTO_TRANSACT_CODE
147, --SHIP_TO_LOCATION_ID
'VENDOR', --RECEIPT_SOURCE_CODE
341, --VENDOR_ID
'PO', --SOURCE_DOCUMENT_CODE
7439, --PO_HEADER_ID
8054, --PO_LINE_ID
9200, --PO_LINE_LOCATION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
-- 13706, --DELIVER_TO_PERSON_ID
147, --LOCATION_ID
147, --DELIVER_TO_LOCATION_ID
'EEBFGWIP', --SUBINVENTORY
rcv_headers_interface_s.currval, --Header_interface_id
'40031000160', --PO NUMBER
87, --TO_ORGANIZATION_ID
'Y' --VALIDATION_FLAG
FROM DUAL;
 
/*********** For Lot Insertion *************/
INSERT INTO mtl_transaction_lots_interface

(

TRANSACTION_interface_ID      

,LAST_UPDATE_DATE          

,LAST_UPDATED_BY          

,CREATION_DATE            

,CREATED_BY                

,LAST_UPDATE_LOGIN        

,TRANSACTION_QUANTITY      

,PRIMARY_QUANTITY          

,LOT_NUMBER                

,LOT_EXPIRATION_DATE      

,SERIAL_TRANSACTION_TEMP_ID

,PRODUCT_CODE              

,PRODUCT_TRANSACTION_ID    

)

values

(

MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL --TRANSACTION_interface_ID      

,SYSDATE --LAST_UPDATE_DATE          

,1 --LAST_UPDATED_BY          

,SYSDATE --CREATION_DATE            

,1 --CREATED_BY                

,1 --LAST_UPDATE_LOGIN        

,60 --TRANSACTION_QUANTITY      

,60 --PRIMARY_QUANTITY          

,'B/L100' --LOT_NUMBER                

,NULL --LOT_EXPIRATION_DATE      

,NULL --SERIAL_TRANSACTION_TEMP_ID

,'RCV' --PRODUCT_CODE              

,RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID    

);

RMAN Restoration on different Server as well as same Mount Point for Oracle APPS

Please Visit http://www.conacent.com/?page_id=218

RMAN Restoration on different Server as well as same Mount Point for Oracle APPS

1. if mount point is not same in target server change the /etc/fstab and restart the server.

2. Copy db backup file system from source server to target server as well as rman backup files.

2. Clone the database technology Stack.
    $su - oracln
    $cd $ORACLE_HOME/appsutil/clone/bin
    $perl adcfgclone.pl dbTechStack

3. Connect to RMAN utility.
    $RMAN target /

4. Startup Database to nomount state.
    RMAN>startup nomount;

5. Restore controlfile from the rman backup file system
    RMAN> restore controlfile from '/data2/rman_archive/autobackup_control_files';  [Latest control file]

6. Database need to be startup to mount state.
    RMAN> alter database mount;

7. Restore database from rman backup file.
    RMAN> restore database;  /  restore database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
    RMAN> recover database;  /  recover database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
    RMAN> alter database open resetlogs;



Please give your feedback if any thing is not correct. Your feedback is valuable.

RMAN Restore on another machine with different file system - same database name In Oracle Apps

Please Visit http://www.conacent.com/?page_id=218

RMAN Restoration on different Server as well as different Mount Point for Oracle APPS

1. Copy db backup file system from source server to target server as well as rman backup files.

2. Clone the database technology Stack.
    $su - oracln
    $cd $ORACLE_HOME/appsutil/clone/bin
    $perl adcfgclone.pl dbTechStack

3. Connect to RMAN utility.
    $RMAN target /


     Recovery Manager: Release 11.1.0.7.0 - Production on Fri Oct 5 14:03:49 2012
     Copyright (c) 1982, 2007, Oracle.  All rights reserved.
     connected to target database (not started)


4. Startup Database to nomount state.
    RMAN>startup nomount;


    Oracle instance started
    Total System Global Area    1068937216 bytes
    Fixed Size                                 2166536 bytes
    Variable Size                         427819256 bytes
    Database Buffers                   624951296 bytes
    Redo Buffers                           14000128 bytes


5. Restore controlfile from the rman backup file system. Before changing the path of the RMAN backup change the owner of the directory.

    RMAN> restore controlfile from '/data2/rman_archive/autobackup_control_filec-147839628-20120930-00';  [Latest control file]


                  Starting restore at 05-OCT-12
                  using channel ORA_DISK_1
                  channel ORA_DISK_1: restoring control file
                  channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
                  output file name=/d01/CLONE/db/apps_st/data/cntrl01.dbf
                  output file name=/d01/CLONE/db/apps_st/data/cntrl02.dbf
                  output file name=/d01/CLONE/db/apps_st/data/cntrl03.dbf
                  Finished restore at 05-OCT-12


6. Database need to be startup to mount state.
    RMAN> alter database mount;

                using target database control file instead of recovery catalog
               database mounted

7. if mount point is different then start with catalog command
    RMAN> catalog start with '/d01/CLONE/db';

                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config.ouibak.2
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config.ouibak.3
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config.ouibak.5
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/lib/ons.jar
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/lib/onc.jar
                   File Name: /d01/CLONE/db/tech_st/11.1.0/root.sh.old.1
                   File Name: /d01/CLONE/db/tech_st/11.1.0/root.sh.old

                   Do you really want to catalog the above files (enter YES or NO)? YES
                   cataloging files...


8. if redo logfile is in different mount point
    $ sqlplus '/as sysdba'
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log03a.dbf' to '/d01/CLONE/db/apps_st/data/log03a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log03b.dbf' to '/d01/CLONE/db/apps_st/data/log03b.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log04a.dbf' to '/d01/CLONE/db/apps_st/data/log04a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log04b.dbf' to '/d01/CLONE/db/apps_st/data/log04b.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log05a.dbf' to '/d01/CLONE/db/apps_st/data/log05a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log05b.dbf' to '/d01/CLONE/db/apps_st/data/log05b.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log06a.dbf' to '/d01/CLONE/db/apps_st/data/log06a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log06b.dbf' to '/d01/CLONE/db/apps_st/data/log06b.dbf';


9. if tempfile is in different mount point
    RMAN> run {
    2> set newname for tempfile '/oracle/OBA/obaora/db/apps_st/data/temp01.dbf' to '/d01/CLONE /db/apps_st/data/temp01.dbf';
     3> set newname for tempfile '/oracle/OBA/obaora/db/apps_st/data/temp02.dbf' to '/d01/CLONE/db/apps_st/data/temp02.dbf';
     4> switch tempfile all;
     5> }

      executing command: SET NEWNAME
      using target database control file instead of recovery catalog

       executing command: SET NEWNAME

        renamed tempfile 1 to /d01/CLONE/db/apps_st/data/temp01.dbf in control file
        renamed tempfile 2 to /d01/CLONE/db/apps_st/data/temp02.dbf in control file

  

10. if datafile is in different mount point

    RMAN> run {
          set newname for datafile 1 to '/d01/CLONE/db/apps_st/data/system01.dbf';
          set newname for datafile 2 to '/d01/CLONE/db/apps_st/data/system02.dbf';
          set newname for datafile 3 to '/d01/CLONE/db/apps_st/data/system03.dbf';
          set newname for datafile 4 to '/d01/CLONE/db/apps_st/data/system04.dbf';
          set newname for datafile 5 to '/d01/CLONE/db/apps_st/data/system05.dbf';
          set newname for datafile 6 to '/d01/CLONE/db/apps_st/data/ctxd01.dbf';
          set newname for datafile 7 to '/d01/CLONE/db/apps_st/data/owad01.dbf';
          set newname for datafile 8 to '/d01/CLONE/db/apps_st/data/a_queue02.dbf';
          set newname for datafile 9 to '/d01/CLONE/db/apps_st/data/odm.dbf';
          set newname for datafile 10 to '/d01/CLONE/db/apps_st/data/olap.dbf';
          set newname for datafile 11 to '/d01/CLONE/db/apps_st/data/sysaux01.dbf';
          set newname for datafile 12 to '/d01/CLONE/db/apps_st/data/apps_ts_tools01.dbf';
          set newname for datafile 13 to '/d01/CLONE/db/apps_st/data/system12.dbf';
          set newname for datafile 14 to '/d01/CLONE/db/apps_st/data/a_txn_data04.dbf';
          set newname for datafile 15 to '/d01/CLONE/db/apps_st/data/a_txn_ind06.dbf';
          set newname for datafile 16 to '/d01/CLONE/db/apps_st/data/a_ref03.dbf';
          set newname for datafile 17 to '/d01/CLONE/db/apps_st/data/a_int02.dbf';
          set newname for datafile 18 to '/d01/CLONE/db/apps_st/data/sysaux02.dbf';
          set newname for datafile 19 to '/d01/CLONE/db/apps_st/data/sysaux03.dbf';
          set newname for datafile 20 to '/d01/CLONE/db/apps_st/data/a_txn_data05.dbf';
          set newname for datafile 21 to '/d01/CLONE/db/apps_st/data/a_txn_data06.dbf';
          set newname for datafile 22 to '/d01/CLONE/db/apps_st/data/a_txn_ind07.dbf';
          set newname for datafile 23 to '/d01/CLONE/db/apps_st/data/XXILL01.dbf';
          set newname for datafile 288 to '/d01/CLONE/db/apps_st/data/system10.dbf';
          set newname for datafile 295 to '/d01/CLONE/db/apps_st/data/system06.dbf';
          set newname for datafile 314 to '/d01/CLONE/db/apps_st/data/portal01.dbf';
          set newname for datafile 351 to '/d01/CLONE/db/apps_st/data/system07.dbf';
          set newname for datafile 352 to '/d01/CLONE/db/apps_st/data/system09.dbf';
          set newname for datafile 353 to '/d01/CLONE/db/apps_st/data/system08.dbf';
          set newname for datafile 354 to '/d01/CLONE/db/apps_st/data/system11.dbf';
          set newname for datafile 379 to '/d01/CLONE/db/apps_st/data/undo01.dbf';
          set newname for datafile 392 to '/d01/CLONE/db/apps_st/data/a_txn_data01.dbf';
          set newname for datafile 393 to '/d01/CLONE/db/apps_st/data/a_txn_ind01.dbf';
          set newname for datafile 394 to '/d01/CLONE/db/apps_st/data/a_ref01.dbf';
          set newname for datafile 395 to '/d01/CLONE/db/apps_st/data/a_int01.dbf';
          set newname for datafile 396 to '/d01/CLONE/db/apps_st/data/a_summ01.dbf';
          set newname for datafile 397 to '/d01/CLONE/db/apps_st/data/a_nolog01.dbf';
          set newname for datafile 398 to '/d01/CLONE/db/apps_st/data/a_archive01.dbf';
          set newname for datafile 399 to '/d01/CLONE/db/apps_st/data/a_queue01.dbf';
          set newname for datafile 400 to '/d01/CLONE/db/apps_st/data/a_media01.dbf';
          set newname for datafile 401 to '/d01/CLONE/db/apps_st/data/a_txn_data02.dbf';
          set newname for datafile 402 to '/d01/CLONE/db/apps_st/data/a_txn_data03.dbf';
          set newname for datafile 403 to '/d01/CLONE/db/apps_st/data/a_txn_ind02.dbf';
          set newname for datafile 404 to '/d01/CLONE/db/apps_st/data/a_txn_ind03.dbf';
          set newname for datafile 405 to '/d01/CLONE/db/apps_st/data/a_txn_ind04.dbf';
          set newname for datafile 406 to '/d01/CLONE/db/apps_st/data/a_txn_ind05.dbf';
          set newname for datafile 407 to '/d01/CLONE/db/apps_st/data/a_ref02.dbf';
          switch datafile all;
}

RMAN> catalog start with '/s01/rman_archive';


RMAN>run {
          restore database  /  restore database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
           recover database  /  recover database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
          }

RMAN> alter database open resetlogs;



Please give your feedback if any thing is not correct. Your feedback is valuable.

"RVTTH-192: Subroutine ORA-20001: APP—20110:Encountered the error in trigger JAI_RCVRT_BRIUD_TI ORA-06508: PL/SQL: could not find program unit being called"

While Performing Receipt/Delviery Error:-
 "RVTTH-192: Subroutine ORA-20001: APP—20110:Encountered the error in trigger JAI_RCVRT_BRIUD_TI ORA-06508: PL/SQL: could not find program unit being called"

Solution: If India Localization is used then it is mandatory set the profile option RCV:Processing Mode to Immediate at site level.

Query For Account Hiererchy:

Step:1

create table XX_test_acc1
as select xx.parent_flex_value,xx.flex_value,xx.DESCRIPTION
from
(select DISTINCT ffvc.parent_flex_value,ffvc.flex_value,FFVC.DESCRIPTION
from
fnd_flex_value_children_v ffvc
where ffvc.summary_flag='N'
AND ffvc.flex_value_set_id=1014870
START WITH ffvc.parent_flex_value IS NOT NULL
CONNECT BY PRIOR ffvc.flex_value=ffvc.parent_flex_value
ORDER BY FFVC.PARENT_FLEX_VALUE) xx;

Step:2

select a.PARENT_FLEX_VALUE,a.DESCRIPTION,xx.parent_flex_value_low,xx.flex_value,xx.flex_value_meaning,xx.description
from
(SELECT   parent_flex_value_low,flex_value, flex_value_meaning, description
    FROM fnd_flex_values_vl
   WHERE (   ('' IS NULL)
          OR (structured_hierarchy_level IN (
                 SELECT hierarchy_id
                   FROM fnd_flex_hierarchies_vl h
                  WHERE h.flex_value_set_id = 1014871
                    AND h.hierarchy_name LIKE '')
             )
         )
     AND (flex_value_set_id = 1014871)
     AND enabled_flag='Y'
          AND (parent_flex_value_low in(select a.flex_value from cil_test_acc a))
ORDER BY parent_flex_value_low) xx ,XX_test_acc1 a
where xx.parent_flex_value_low=to_number(a.FLEX_VALUE);

Query to find responsibilty attached to user

SELECT fu.user_name,
       frv.responsibility_name,
       TO_CHAR (furgd.start_date, 'DD-MON-YYYY') "START_DATE",
       TO_CHAR (furgd.end_date, 'DD-MON-YYYY') "END_DATE"
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id                     = furgd.user_id
AND furgd.responsibility_id          = frv.responsibility_id
AND furgd.end_date                  IS NULL
AND furgd.start_date                <= sysdate
AND fu.start_date                   <= sysdate
AND frv.start_date                  <= sysdate;

AR 7 Bucket Ageing Query

Select MAIN_TAB.*
from
(select AR_TAB.account_number ,
AR_TAB.party_name,
AR_TAB.customer_trx_id,
AR_TAB.Trans_Type,
AR_TAB.trans_type_name,
AR_TAB.cust_trx_type_id,
AR_TAB.invoice_currency_code,
AR_TAB.location_code,
AR_TAB.trx_num,
AR_TAB.Trans_Date,
AR_TAB.gl_date,
AR_TAB.Due_Date,
AR_TAB.Fcy_Amt,
AR_TAB.Inr_Amt,
AR_TAB.exchange_rate,
AR_TAB.amount_due_remaining_FCY,
AR_TAB.Amount_Remaining_INR,
nvl(AR_TAB.ar_acctd_amt_due,0) ar_acctd_amt_due,
(nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0)) outstanding_amount_inr,
(nvl(AR_TAB.Fcy_Amt,0)-nvl(AR_TAB.ar_fcy_amt_due,0)) Outstanding_amount_fcy,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) <=0 then (AR_TAB.INR_AMT - AR_TAB.ar_acctd_amt_due)
else 0 end ) Current_bal
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 1 and 30 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 31 and 60 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 61 and 90 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_90_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 91 and 120 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_120_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 121 and 150 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_150_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 151 and 180 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_180_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) > 180 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) more_than_180_Days_Past_Due
from
(SELECT
hca.account_number account_number,
hp.PARTY_NAME party_name,
cta.CUSTOMER_TRX_ID customer_trx_id
--,acl.name collector_name
,decode(ps.class,'INV', 'Invoice', 'DM', 'Debit Memo','CM','Credit Memo') Trans_Type
,rctt.name trans_type_name,
rctt.CUST_TRX_TYPE_ID cust_trx_type_id
,cta.INVOICE_CURRENCY_CODE invoice_currency_code,
hou.LOCATION_CODE location_code,
ps.trx_number trx_num
,ps.trx_date Trans_Date,
ps.GL_DATE gl_date,
ps.due_date Due_Date,
ps.AMOUNT_DUE_ORIGINAL Fcy_Amt,
round((ps.AMOUNT_DUE_ORIGINAL * nvl(ps.EXCHANGE_RATE,1)),2) Inr_Amt ,
ps.EXCHANGE_RATE exchange_rate,
ps.AMOUNT_DUE_REMAINING amount_due_remaining_FCY
,ps.acctd_amount_due_remaining Amount_Remaining_INR,
(select round(sum(nvl(a.AMOUNT_APPLIED* nvl(ps.EXCHANGE_RATE,1) ,0)),2)
from ar_receivable_applications_all a
where a.APPLIED_PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.APPLIED_PAYMENT_SCHEDULE_ID) ar_acctd_amt_due,
(select round(sum(nvl(a.AMOUNT_APPLIED,0)),2) from ar_receivable_applications_all a where a.APPLIED_PAYMENT_SCHEDULE_ID=ps.PAYMENT_SCHEDULE_ID
and a.DISPLAY='Y' and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.APPLIED_PAYMENT_SCHEDULE_ID) ar_fcy_amt_due
/*,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) <=0 then ps.acctd_amount_due_remaining
else 0 end ) Current_bal
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 1 and 30 then ps.acctd_amount_due_remaining
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 31 and 60 then ps.acctd_amount_due_remaining
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 61 and 90 then ps.acctd_amount_due_remaining
else 0 end ) One_90_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 91 and 120 then ps.acctd_amount_due_remaining
else 0 end ) One_120_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 121 and 150 then ps.acctd_amount_due_remaining
else 0 end ) One_150_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 151 and 180 then ps.acctd_amount_due_remaining
else 0 end ) One_180_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) > 180 then ps.acctd_amount_due_remaining
else 0 end ) more_than_180_Days_Past_Due*/
FROM apps.ar_payment_schedules_all ps
,apps.hz_cust_accounts hca
,apps.hz_parties hp
--,apps.ar_collectors acl
,apps.ra_customer_trx_all cta
,apps.ra_cust_trx_types_all rctt
,hr_organization_units_v hou
--ar_receivable_applications_all ara
WHERE hca.cust_account_id = ps.CUSTOMER_ID
AND   ps.CUSTOMER_TRX_ID=cta.CUSTOMER_TRX_ID
--AND   ara.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
--AND ara.DISPLAY='Y'
AND cta.cust_trx_type_id = rctt.cust_trx_type_id
--AND (ps.status = 'OP' or(ps.STATUS='CL' and ps.GL_DATE_CLOSED < :p_to_date))
AND ps.class <> 'PMT'
AND ps.CLASS = nvl(:p_class,ps.CLASS)
AND ps.CLASS in('INV','DM')
--AND ps.customer_id > 0
--AND ps.AMOUNT_DUE_REMAINING <> 0
--AND trunc(ps.GL_DATE) < trunc(sysdate)
AND cta.INVOICE_CURRENCY_CODE <> 'INR'
AND hou.ORGANIZATION_ID=cta.ORG_ID
AND cta.ORG_ID = nvl(:p_org_id,cta.ORG_ID)
AND hp.PARTY_ID=hca.PARTY_ID
--AND hp.PARTY_NAME='Tea Promotors Export Pvt. Ltd.'
AND ps.INVOICE_CURRENCY_CODE<>'INR'
AND rctt.ORG_ID=cta.ORG_ID
AND ps.AMOUNT_ADJUSTED is null -- added on 10jul2012
--AND cta.TRX_NUMBER='511120003'
--AND trunc(ps.DUE_DATE) between  nvl(:p_from_date,trunc(ps.DUE_DATE))  and nvl(:p_to_date,trunc(ps.DUE_DATE))
--AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE)
AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE))) AR_TAB
--AND AR_TAB.Amount_Remaining_INR <>0
where (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0)) <> 0
union all /*For credit Memo*/
select AR_TAB.account_number ,
AR_TAB.party_name,
AR_TAB.customer_trx_id,
AR_TAB.Trans_Type,
AR_TAB.trans_type_name,
AR_TAB.cust_trx_type_id,
AR_TAB.invoice_currency_code,
AR_TAB.location_code,
AR_TAB.trx_num,
AR_TAB.Trans_Date,
AR_TAB.gl_date,
AR_TAB.Due_Date,
AR_TAB.Fcy_Amt,
AR_TAB.Inr_Amt,
AR_TAB.exchange_rate,
AR_TAB.amount_due_remaining_FCY,
AR_TAB.Amount_Remaining_INR,
nvl(AR_TAB.ar_acctd_amt_due,0) ar_acctd_amt_due,
-(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0)) outstanding_amount_inr,
-(nvl(abs(AR_TAB.Fcy_Amt),0)-nvl(AR_TAB.ar_fcy_amt_due,0)) Outstanding_amount_fcy,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) <=0 then -(abs(AR_TAB.INR_AMT) - abs(AR_TAB.ar_acctd_amt_due))
else 0 end ) Current_bal
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 1 and 30 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 31 and 60 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 61 and 90 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_90_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 91 and 120 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_120_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 121 and 150 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_150_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 151 and 180 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_180_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) > 180 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) more_than_180_Days_Past_Due
from
(SELECT
hca.account_number account_number,
hp.PARTY_NAME party_name,
cta.CUSTOMER_TRX_ID customer_trx_id
--,acl.name collector_name
,decode(ps.class,'INV', 'Invoice', 'DM', 'Debit Memo','CM','Credit Memo') Trans_Type
,rctt.name trans_type_name,
rctt.CUST_TRX_TYPE_ID cust_trx_type_id
,cta.INVOICE_CURRENCY_CODE invoice_currency_code,
hou.LOCATION_CODE location_code,
ps.trx_number trx_num
,ps.trx_date Trans_Date,
ps.GL_DATE gl_date,
ps.due_date Due_Date,
ps.AMOUNT_DUE_ORIGINAL Fcy_Amt,
round((ps.AMOUNT_DUE_ORIGINAL * nvl(ps.EXCHANGE_RATE,1)),2) Inr_Amt ,
ps.EXCHANGE_RATE exchange_rate,
ps.AMOUNT_DUE_REMAINING amount_due_remaining_FCY
,ps.acctd_amount_due_remaining Amount_Remaining_INR,
(select round(sum(nvl(a.AMOUNT_APPLIED*nvl(ps.EXCHANGE_RATE,1),0)),2)
from ar_receivable_applications_all a
where a.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.PAYMENT_SCHEDULE_ID) ar_acctd_amt_due,
(select round(sum(nvl(abs(a.AMOUNT_APPLIED),0)),2)
from ar_receivable_applications_all a
where a.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.PAYMENT_SCHEDULE_ID) ar_fcy_amt_due
/*,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) <=0 then ps.acctd_amount_due_remaining
else 0 end ) Current_bal
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 1 and 30 then ps.acctd_amount_due_remaining
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 31 and 60 then ps.acctd_amount_due_remaining
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 61 and 90 then ps.acctd_amount_due_remaining
else 0 end ) One_90_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 91 and 120 then ps.acctd_amount_due_remaining
else 0 end ) One_120_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 121 and 150 then ps.acctd_amount_due_remaining
else 0 end ) One_150_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 151 and 180 then ps.acctd_amount_due_remaining
else 0 end ) One_180_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) > 180 then ps.acctd_amount_due_remaining
else 0 end ) more_than_180_Days_Past_Due*/
FROM apps.ar_payment_schedules_all ps
,apps.hz_cust_accounts hca
,apps.hz_parties hp
--,apps.ar_collectors acl
,apps.ra_customer_trx_all cta
,apps.ra_cust_trx_types_all rctt
,hr_organization_units_v hou
--ar_receivable_applications_all ara
WHERE hca.cust_account_id = ps.CUSTOMER_ID
AND   ps.CUSTOMER_TRX_ID=cta.CUSTOMER_TRX_ID
--AND   ara.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
--AND ara.DISPLAY='Y'
AND cta.cust_trx_type_id = rctt.cust_trx_type_id
--AND (ps.status = 'OP' or(ps.STATUS='CL' and ps.GL_DATE_CLOSED < :p_to_date))
AND ps.class <> 'PMT'
AND ps.CLASS = nvl(:p_class,ps.CLASS)
AND ps.CLASS in('CM')
--AND ps.customer_id > 0
--AND ps.AMOUNT_DUE_REMAINING <> 0
--AND trunc(ps.GL_DATE) < trunc(sysdate)
AND cta.INVOICE_CURRENCY_CODE <> 'INR'
AND hou.ORGANIZATION_ID=cta.ORG_ID
AND cta.ORG_ID = nvl(:p_org_id,cta.ORG_ID)
AND hp.PARTY_ID=hca.PARTY_ID
--AND hp.PARTY_NAME='Tea Promotors Export Pvt. Ltd.'
AND ps.INVOICE_CURRENCY_CODE<>'INR'
AND rctt.ORG_ID=cta.ORG_ID
AND ps.AMOUNT_ADJUSTED is null -- added on 10jul2012
--AND cta.TRX_NUMBER='511120003'
--AND trunc(ps.DUE_DATE) between  nvl(:p_from_date,trunc(ps.DUE_DATE))  and nvl(:p_to_date,trunc(ps.DUE_DATE))
--AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE)
AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE))) AR_TAB
--AND AR_TAB.Amount_Remaining_INR <>0
where (nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0)) <> 0) MAIN_TAB
order by MAIN_TAB.PARTY_NAME asc;

Call a procedure through personalization

SQL TO FIND MANUAL AP INVOICES WITH OUT PO AND RECEIPT MATCH

SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.BASE_AMOUNT     AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS          RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NULL
AND   AID.BASE_AMOUNT IS NOT NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID (+)
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID (+)
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
UNION ALL
SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.AMOUNT          AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS           RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NULL
AND   AID.BASE_AMOUNT IS NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID (+)
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID (+)
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
ORDER BY INVOICE_NUM, GL_DATE

SQL TO FIND PO MATCH INVOICES

SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.BASE_AMOUNT     AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS          RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NOT NULL
AND   AID.BASE_AMOUNT IS NOT NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
UNION ALL
SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.AMOUNT          AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS           RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NOT NULL
AND   AID.BASE_AMOUNT IS NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
ORDER BY INVOICE_NUM, GL_DATE

SQL to find Inventory Misc. Transactions with Dist Account and Value

SELECT  CID.ORGANIZATION_CODE
,       HRO.NAME
,       MMT.TRANSACTION_ID
,       MSI.SEGMENT1
,       MSI.DESCRIPTION
,       MTY.TRANSACTION_TYPE_NAME
,       TRUNC(MMT.TRANSACTION_DATE) AS TRANSACTION_DATE
,       MMT.TRANSACTION_QUANTITY
,       MMT.TRANSACTION_UOM
,       MMT.TRANSACTION_SOURCE_NAME
,       CID.UNIT_COST
,       NVL(MMT.ACTUAL_COST,0) AS ACTUAL_COST
,       GCC.CONCATENATED_SEGMENTS   AS ACCOUNT_CODE_COMBINATION
,       CID.LINE_TYPE_NAME
,       CID.BASE_TRANSACTION_VALUE
FROM MTL_MATERIAL_TRANSACTIONS      MMT
,    MTL_SYSTEM_ITEMS_B             MSI
,    HR_ALL_ORGANIZATION_UNITS      HRO
,    MTL_TRANSACTION_TYPES          MTY
,    CST_INV_DISTRIBUTION_V         CID
,    GL_CODE_COMBINATIONS_KFV       GCC
WHERE MMT.TRANSACTION_TYPE_ID IN (32, 42, 100001, 100002)
AND   MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND   MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND   MMT.ORGANIZATION_ID = HRO.ORGANIZATION_ID
AND   MMT.TRANSACTION_TYPE_ID = MTY.TRANSACTION_TYPE_ID
AND   TRUNC(MMT.TRANSACTION_DATE) BETWEEN :FROM_DATE AND :TO_DATE
AND   MMT.TRANSACTION_ID =  CID.TRANSACTION_ID
AND   MMT.ORGANIZATION_ID = CID.ORGANIZATION_ID
AND   CID.REFERENCE_ACCOUNT = GCC.CODE_COMBINATION_ID
ORDER BY MMT.TRANSACTION_ID, BASE_TRANSACTION_VALUE DESC

run scp as background process

http://www.conacent.com

I had a problem to transfer big file from server A to server B and don't want to wait until transfer process is completed. How to keep scp running even terminal session is closed.

My solution is using scp with nohup.

1	$nohup scp *.tar user@server:/e01/backup/ &

But the problem with the above code is, scp require password to enter before you can use it. (other method, create ssh signed key)

to solve this problem. We can do the following step
1. run nohup

1	$nohup scp  *.tar user@server:/e01/backup/  > nohup.out 2>&1

by default, nohup is not running at background. wait until scp asking for the password.
2. enter your password.
3. press ctrl + z to temporary suspend the command.
4. enter bg command

1

$bg

now your scp is running at background. You can close your terminal 

Applications File Server connection with the node. There may be a network configuration problem, or the TNS listener on node may not be running.

Please Visit http://www.conacent.com/?page_id=218

Problem :-

Applications File Server connection with the node.
There may be a network configuration problem,
or the TNS listener on node may not be running.


Solution :-

In the Profile Option –>system –>

RRA: Enabled is set as No in Site Level. Make it to Yes.
RRA: Service Prefix should be BLANK


Save and Logout and Retest the Issue.

adcrdb.sh INSTE8 AutoConfig is exiting with status 1

Please Visit http://www.conacent.com/?page_id=218


Problem -

[INSTANTIATE PHASE]
  AutoConfig could not successfully instantiate the following files:
    Directory: /devdbhome/CRMTEST/crmproddb/10.2.0/appsutil/install/CRMTEST_test      adcrdb.sh               INSTE8        


AutoConfig is exiting with status 1


Solution -
 
To implement the solution, please execute the following steps :
1. copy the missing 'adcrdb.sh' template from the Source to the Target Instance :
Source Location :

<$ORACLE_HOME>/appsutil/template

Target Location :

<$ORACLE_HOME>/appsutil/template

2. Change ownership and permission of adcrdb.sh file to match with the other templates, which resides in the template directory.
3. Re-start the adcfgclone dbTier on the Target Instance.

java.sql.SQLException: ORA-04098: trigger 'APPS.FNDSM' is invalid and failed re-validation

Please Visit http://www.conacent.com/?page_id=218


Problem -

java.sql.SQLException: ORA-04098: trigger 'APPS.FNDSM' is invalid and failed re-validation

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
    at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1614)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1579)
    at oracle.apps.ad.tools.configuration.NetServiceHandler.updateNodeIdIfNull(NetServiceHandler.java:357)
    at oracle.apps.ad.tools.configuration.NetServiceHandler.main(NetServiceHandler.java:2953)
AC-50480: Internal error occurred: java.sql.SQLException: ORA-04098: trigger 'APPS.FNDSM' is invalid and failed re-validation

Solution -

1. SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
2. SQL> commit;
3. Compile the FNDSM trigger by connecting as apps
    SQL> alter trigger fndsm compile;






   (If compilation error occurs then Run this script)
    CREATE OR REPLACE TRIGGER fndsm
    AFTER INSERT OR UPDATE ON FND_NODES
    FOR EACH ROW
    BEGIN
      if ( :new.NODE_NAME <> 'AUTHENTICATION' ) then
         if ( (:new.SUPPORT_CP='Y')
              or (:new.SUPPORT_FORMS='Y')
              or (:new.SUPPORT_WEB='Y') ) then
             fnd_cp_fndsm.register_fndsm_fcq(:new.NODE_NAME);
         end if;
         if (:new.SUPPORT_CP = 'Y') then
             fnd_cp_fndsm.register_fndim_fcq(:new.NODE_NAME);
        end if;
       end if;
    END;


4. run autoconfig on dbtier and then followed by appstier
5. start the middle tier.

How to Convert Number to Word in Oracle Report Builder

Function CF_11Formula return Char is

   NumberIN float:=round(:CF_1,2); 

   InvalidNumberFormatModel                     EXCEPTION;

     PRAGMA EXCEPTION_INIT(InvalidNumberFormatModel,-1481);

     InvalidNumber                                   EXCEPTION;

     PRAGMA EXCEPTION_INIT(InvalidNumber,-1722);

     TYPE GroupTableType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

     ConversionType        CHAR(6)                  := '';

     GroupTable                                     GroupTableType;

     GroupIndex                                     NUMBER;

   Words                                           VARCHAR2(2000);

     WholePart                                     NUMBER;

     FractionalPart                                 NUMBER;

     FractionalDigits                             NUMBER;

     Remainder                                     NUMBER;

     Remainder1                                     NUMBER;

     Remainder2                                     NUMBER;

     Suffix                                           VARCHAR2(50);

   BEGIN

      GroupTable(0)    := '';

        GroupTable(1)    := ' ten';

        GroupTable(2)    := ' hundred';

        GroupTable(3)    := ' thousand';

        GroupTable(4)    := ' ten thousand';

        GroupTable(5)    := ' lakh ';

        GroupTable(6)    := ' ten lakh ';

        GroupTable(7)    := ' crore ';

        GroupTable(8)    := ' ten crore ';

        GroupTable(9)    := ' hundred crore ';

        GroupTable(10)    := ' thousand crore ';

        GroupTable(11)    := ' ten thousand crore ';

        GroupTable(12)    := ' lakh crore ';

        GroupTable(13)    := ' ten lakh crore ';

        GroupTable(14)    := ' hundred lakh crore ';

        GroupTable(15)    := ' thousand lakh crore ';

        GroupTable(16)    := ' ten thousand lakh crore ';

        GroupTable(17)    := ' lakh lakh crore ';

        GroupTable(18)    := ' ten lakh lakh crore ';

        GroupTable(19)    := ' crore crore ';

           WholePart    := ABS(TRUNC(NumberIN));  -- Calculate whole and fractional parts

           FractionalPart    := ABS(NumberIN) - WholePart;

           IF FractionalPart = 0 THEN         -- Check if fractional part is 0

              Words    := 'zero paise';

                     Suffix        := ' and ';

           ELSE

              IF ConversionType = 'N' THEN

                   FractionalDigits    := LENGTH(TO_CHAR(FractionalPart)) - 1;

                IF FractionalDigits > 15 THEN

                   RAISE InvalidNumber;

                END IF;

                Suffix            := GroupTable(FractionalDigits) || 'th';

                FractionalPart        := FractionalPart *    POWER(10,FractionalDigits);

           ELSE

                IF LENGTH(TO_CHAR(FractionalPart)) > 3 THEN

                   RAISE InvalidNumber;

                 END IF;

                       FractionalPart        := FractionalPart * 100;

                   IF FractionalPart = 1 THEN

                      Suffix        := ' Paise';

               ELSE

                  Suffix        := ' Paise';

                   END IF;

           END IF;

              IF FractionalPart <= 99999 THEN

                   Words    := TO_CHAR(TO_DATE(FractionalPart,'j'),'Jsp') ||Suffix;

              ELSE

                  GroupIndex    := 0;

                      WHILE FractionalPart != 0

                 LOOP

                     Remainder    := MOD(FractionalPart,1000);

                        IF Remainder != 0 THEN

                           Words    := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||    GroupTable(GroupIndex) || Words;

                        END IF;

                        GroupIndex    := GroupIndex + 3;

                        FractionalPart:= TRUNC(FractionalPart / 1000);

                    END LOOP;

                    Words    := Words || Suffix;

              END IF;

              Suffix        := ' and ';

          END IF;

             IF WholePart = 0  THEN

                  IF ConversionType = '' THEN

                     Words    := 'zero ' || Suffix || Words;

                  ELSE

                  Words    := 'zero' || Suffix || Words;

                  END IF;

             ELSE

                IF WholePart = 1 THEN

                     Suffix    := ' Rupee' || Suffix;

                  ELSE

                     Suffix    := '' || Suffix;

                  END IF;

                IF WholePart <= 99999 THEN

                     Words    := TO_CHAR(TO_DATE(WholePart,'j'),'Jsp') ||Suffix || Words;

                ELSE

                     IF LENGTH(TO_CHAR(WholePart)) > 15 THEN

                        RAISE InvalidNumber;

                     END IF;

                     GroupIndex    := 0;

                     Words        := Suffix || Words;

                     WHILE WholePart != 0

                    LOOP

                           IF WholePart < 10000000 THEN

                       Remainder    := MOD(WholePart,100000);

                              IF Remainder != 0 THEN

                        

                              Words    := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||GroupTable(GroupIndex) || Words;

                                END IF;

                              GroupIndex    := GroupIndex + 5;

                              WholePart    := TRUNC(WholePart / 100000);

                       ELSE

                       Remainder    := MOD(WholePart,10000000);

                              IF Remainder != 0 THEN

                          IF Remainder >= 100000 THEN

                          Remainder2 := MOD(Remainder,100000);

                          Words    :=  TO_CHAR(TO_DATE(Remainder2,'j'),'Jsp') || words;

                          Remainder1 := trunc(Remainder/100000);

                              Words    := TO_CHAR(TO_DATE(Remainder1,'j'),'Jsp') ||' lakh '|| Words;

                          ELSE

                          Words    := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||GroupTable(GroupIndex) || Words;

                          END IF;

                          END IF;

                              GroupIndex    := GroupIndex + 7;

                              WholePart    := TRUNC(WholePart / 10000000);

                       END IF;

                        END LOOP;

                END IF;

             END IF;

             IF Words IS NULL THEN

                Words    := 'zero';

             END IF;

             IF SIGN(NumberIN) = -1 THEN

                Words    := 'minus ' || Words;

             END IF;

             return Words||' Only';

   EXCEPTION

     WHEN OTHERS THEN

       RETURN ' ';                  

   END;

Note : For INR currency of India