Oracle Report Builder 10.1.2.3.0 crash (APPCRASH)

Problem: After upgrading the report builder to 10.1.2.3, it crashes while opening seeded rdf in R12.2+ version.

Solution: Connect to R12 instance and retry opening the rdf, the file opens up properly..

Performance Tip-- How to cache oracle apps valuesets in PLSQL

Problem: In consulting world, most of the business uses value sets to define name value pair values. Usually programmers pass the value set name and value to check or derive the values in loop for each record. How do we avoid re-quering the valueset table again and again.

Solution:  Please see the below example which will allow them to cache it locally in the collection table, which can be used anytime during the processing without going back to db.

set serveroutput on;
declare
      CURSOR c_get_value_set_info IS
        SELECT fv.flex_value,      
               fv.description flex_value_meaning
        FROM fnd_flex_values_vl fv, fnd_flex_value_sets fvs
          WHERE     fv.enabled_flag = 'Y'
            AND fvs.flex_value_set_id = fv.flex_value_set_id
            AND SYSDATE BETWEEN NVL (fv.start_date_active, SYSDATE - 1) AND NVL (fv.end_date_active, SYSDATE + 1)
            AND fvs.flex_value_set_name = 'XX_VALUE_SET_NAME';

   TYPE xx_valuest_rec IS RECORD
   (
     flex_value         VARCHAR2(250),
     flex_meaning       VARCHAR2(2000)
   );
 
   TYPE xx_valuest_rec_tbl IS TABLE OF xx_valuest_rec INDEX BY PLS_INTEGER;
 
     l_value_set_array xx_valuest_rec_tbl;
     l_status_message VARCHAR2(2000);
begin
     OPEN c_get_value_set_info;
     FETCH c_get_value_set_info BULK COLLECT into  l_value_set_array;
     CLOSE c_get_value_set_info;
   
       IF l_value_set_array.count > 0  THEN
         dbms_output.put_line('success:');

          -- to print all the elements
          for i in 1..l_value_set_array.count loop
            dbms_output.put_line('value:'||l_value_set_array(i).flex_value ||'    Meaning:'||l_value_set_array(i).flex_meaning);
          end loop;
       else
         dbms_output.put_line('failure: valueset not found');
        end if;

end;

How to find Components version in R12

How to find Components version in R12

Apache Version

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v

Go to reports path Then type: -

string -a APXAPRVL.rdf|grep Header

Perl Version

$IAS_ORACLE_HOME/perl/bin/perl -v|grep built

Java Version

sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"

Jre version

cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35

Forms Version

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

Plsql Version

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication mode

cat $FORMS_WEB_CONFIG_FILE|grep serverURL=

echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"

How to find Apps Version
select release_name from apps.fnd_product_groups;

Web Server/Apache or Application Server in Apps 11i/R12
Log in as Application user, set environment variable and run below query$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
  
Forms & Report version in R12/12i
Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release

 Oracle Jinitiator in 11i/R12/12i
 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 )

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.

File Version on file system
adident Header
or
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility.

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/.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);

Workflow Version with Apps
select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;
  
Identity Management component Version/Release Number

Oracle Single Sign On
select version from orasso.wwc_version$;

Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)

To find software/binary version
$ORACLE_HOME/bin/oidldapd -version

To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \ -s base “objectclass=*” orcldirectoryversion 

select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;

 Application Server
Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0 

For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database

select * from ias_versions;
or
select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;

AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version 

Oracle Portal
select version from portal.wwc_version$; 

Database Component 

To find database version
select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail

Unix Operating System

Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release

Oracle Ebusiness -- Reserve Document sequence number for external system (portal/websites)

Purpose: How to reserve order numbers/ invoice numbers with external system to use. This will help to keep single number across the system.


SET serveroutput ON;
DECLARE
  x_doc_sequence_value NUMBER;
  X_doc_sequence_id    NUMBER;
  x_reuslt             NUMBER;
BEGIN
  fnd_global.apps_initialize (-1, 213322, 660);
  x_reuslt := fnd_seqnum.get_seq_val( 660, -- application id
  TO_CHAR(10),                          -- category code from FND_DOC_SEQUENCE_ASSIGNMENTS
  100,                                    -- Set of Books
  NULL, sysdate, x_doc_sequence_value, X_doc_sequence_id, 'Y', 'Y');
  DBMS_OUTPUT.PUT_LINE('x_doc_sequence_value:'||x_doc_sequence_value);
  DBMS_OUTPUT.PUT_LINE('X_doc_sequence_id:'||X_doc_sequence_id);
  DBMS_OUTPUT.PUT_LINE('x_result:'||x_reuslt);
END;

Mac host file changes vanished after restart

Please make sure changing both the files /etc/hosts and /etc/hosts.ac

sudo nano /private/etc/hosts

sudo nano /private/etc/hosts.ac