APPS Blogs

Oracle Transparent Gateway for MS SQL Server - Part II

Aviad Elbaz - Fri, 2008-08-22 08:28

Following my last post about Oracle Transparent Gateways which was theoretical post, this post will be more practical.

In this post I'll show an example of configuring Oracle Transparent Gateway for MS SQL Server.
The goal is to make it possible to retrieve data from MS SQL server database by querying from Oracle database.

The steps are:

  1. install Oracle Transparent Gateway for MS SQL Server (not explained here)
  2. Configure the gateway initialization parameter file
  3. Configure the listener
  4. Configure the Oracle database tnsnames.ora
  5. Creating DB link from Oracle database to MS SQL Server

In my environment there are 3 servers involved:

  1. Oracle database server - name: orclsrv01, with Oracle database installed name: orcldb
  2. Microsoft SQL server - name: msqlsrv01, with MS SQL server installed name: msqldb
  3. Oracle Gateway server - name: oragwsrv01, with Oracle Transparent Gateway installed.


Configure the gateway initialization parameter file

After Oracle Transparent Gateway for MS SQL server installed on ORAGWSRV01 server, a new default initialization file is created at: $ORACLE_HOME\tg4msql\admin\inittg4msql.ora.

$ORACLE_HOME - is the directory where Transparent Gateway installed.
inittg4msql.ora - is the default initialization parameter file, as tg4msql represents the Gateway SID.
If you choose to not use the default SID (or when you have more than one MS Sql server to connect to), the initialization parameter file should be named as follow: init[GW_SID].ora

Edit the inittg4msql.ora file and specify the MS Sql Server connection details as follow:
HS_FDS_CONNECT_INFO=server_name.database_name

in our case:
HS_FDS_CONNECT_INFO=msqlsrv01.msqldb

Alternatively you can set it as follow:
HS_FDS_CONNECT_INFO="SERVER=msqlsrv01; DATABASE=msqldb"

Configure the listener

In order to make the listener work with the gateway we should configure it.
The listener have to be on the same server like the Gateway.
We should edit the listener.ora within the Oracle home on which the Gateway installed.

The listener.ora must have at least 2 entries:

  • The address to listen on
  • the Gateway to start

On ORAGWSRV01 server at $ORACLE_HOME\network\admin edit the listener.ora as follow:

LISTENER=
               (ADDRESS=
                   (PROTOCOL=TCP)
                   (HOST=host_name)
                   (PORT=port_number))

SID_LIST_LISTENER=
      (SID_LIST=
           (SID_DESC=
                 (SID_NAME=gateway_sid)
                 (ORACLE_HOME=oracle_home)
                 (PROGRAM=tg4msql)
           ) 
      )

Where:
host_name - is the server name on which the gateway is installed
port_number - is the listener port
gateway_sid - is the gateway SID (default is tg4msql)
oracle_home - is the Oracle directory on which the gateway is installed

In our case:

LISTENER=
              (ADDRESS= 
              (PROTOCOL=TCP)
              (HOST=oragwsrv01)
              (PORT=1521))

SID_LIST_LISTENER=
      (SID_LIST=
          (SID_DESC= 
               (SID_NAME=tg4msql)
               (ORACLE_HOME=C:\oracle\product\10.2.0)
               (PROGRAM=tg4msql)
          )
      )

After finishing the Gateway configuration, restart the listener (lsnrctl stop; lsnrctl start).

Configure the Oracle database tnsnames.ora

Now, when the Gateway configured, we need to configure the Oracle database server to communicate with the Gateway via Oracle Net services.
On ORCLSRV01 server at $TNS_ADMIN ($ORACLE_HOME/network/admin) edit the tnsnames.ora file and add the Gateway's connect descriptor as follow:

connect_descriptor=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=host_name)
         (PORT=port_number)
      )
      (CONNECT_DATA=
         (SID=gateway_sid))
      (HS=OK))

Where:

connect_descriptor - you can specify a name which will be used later when we create a db link
host_name - is the server on which the Gateway installed
port_number - is the port used by the listener that listening for the Gateway
gateway_sid - is the gateway SID (default tg4msql)
HS=OK - indicates this connect descriptor is using heterogeneous Services

In our case:

mytg4msql=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=oragwsrv01)
         (PORT=1521)
      )
      (CONNECT_DATA=
         (SID=tg4msql))
      (HS=OK))

Creating DB link from Oracle database to MS SQL Server

Now, when all configurations were done, we only need to create a Database Link from Oracle to the Gateway.
Connect to orcldb database and create a db link as follow:

create database link ORCLDB2MSQLDB
  connect to [MSQLUSER] identified by [MSQLPWD]
  using 'mytg4msql';

Where MSQLUSER & MSQLPWD are the user name and password to connect to the MS SQL server.

Test it...

At this stage we can get data from MS SQL Server via Oracle Database as if we query from Oracle database.

For example, if we have a RESERVATIONS table in MSQLDB, we can retrieve RESERVATIONS rows from ORCLDB by issuing the following SQL statement:

select *
from reservations@ORCLDB2MSQLDB;

For more information about Transparent Gateway for Microsoft SQL Server:
Oracle® Transparent Gateway for Microsoft SQL Server Administrator's Guide 10g Release 2 (10.2) for Microsoft Windows (32-bit)

For more information about other Transparent Gateways for other Database systems:
Oracle Database Documentation Library 10g release 2 (10.2) - Information Integration

You are welcome to leave a comment.

Aviad

Categories: APPS Blogs

Adding Cost Center value to the OTL Timecard

RameshKumar Shanmugam - Sun, 2008-08-17 21:35
The common requirement in any timecard configuration is to have a cost center field to charge the time against. By default in OTL Oracle provides the cost center field but for displaying the values in the Timecard you need to configure the ltd file and create a View to hold the cost center value

Following code can be used to create the view
CREATE OR REPLACE FORCE VIEW HXC_CUI_CUSTOM9_V ( display_value ,value ) AS select fv.flex_value'-'fv.DESCRIPTION display_value, to_char(flex_value_id) value from fnd_flex_values_vl fv, fnd_flex_value_sets fvs where fv.flex_value_set_id = fvs.flex_value_set_id and fvs.flex_value_set_name = 'GL_COA_COST_CENTER'

Following are the three layout needed to be modified

Payroll layout - hxczzhxclayt0000.ldt
Payroll Review page - hxczzhxclayt0005.ldt
Payroll Confirmation page - hxczzhxclayt0001.ldt

Replace the cost center segment with the below code

Modify the Payroll layout - hxczzhxclayt0000.ldt

BEGIN HXC_LAYOUT_COMPONENTS "Payroll Timecard Layout - Cost Center"OWNER = "CUSTOM"COMPONENT_VALUE = "COSTCENTER"REGION_CODE = "HXC_CUI_TIMECARD"REGION_CODE_APP_SHORT_NAME = "HXC"ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER"ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC"SEQUENCE = "190"COMPONENT_DEFINITION = "CHOICE_LIST"RENDER_TYPE = "WEB"PARENT_COMPONENT ="NEC Payroll Timecard Layout - Day Scope Building blocks for worker timecard matrix"LAST_UPDATE_DATE = "2004/05/24"
BEGIN HXC_LAYOUT_COMP_QUALIFIERS "NEC Payroll Timecard Layout - Cost Center"OWNER = "CUSTOM"QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST"QUALIFIER_ATTRIBUTE1 = "Custom9VO" ########Changed to Custm9VO ##############QUALIFIER_ATTRIBUTE4 = "N"QUALIFIER_ATTRIBUTE8 = "DisplayValue"QUALIFIER_ATTRIBUTE9 = "Value#NUMBER"
QUALIFIER_ATTRIBUTE10 ="oracle.apps.hxc.selfservice.timecard.server.Custom9VO" ########Changed to Custm9VO ##############
QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATETIMECARD_BIND_END_DATE"QUALIFIER_ATTRIBUTE17 = "OraTableCellText"QUALIFIER_ATTRIBUTE20 = "N"QUALIFIER_ATTRIBUTE21 = "Y"QUALIFIER_ATTRIBUTE22 = "L"QUALIFIER_ATTRIBUTE25 = "FLEX"QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context"QUALIFIER_ATTRIBUTE27 = "Attribute2"LAST_UPDATE_DATE = "2004/05/24"END HXC_LAYOUT_COMP_QUALIFIERS
END HXC_LAYOUT_COMPONENTS

Modify the Payroll Review page - hxczzhxclayt0005.ldt

BEGIN HXC_LAYOUT_COMPONENTS "Payroll Review Layout - Cost Center" OWNER = "CUSTOM" COMPONENT_VALUE = "COSTCENTER" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "160" COMPONENT_DEFINITION = "CHOICE_LIST" RENDER_TYPE = "WEB" PARENT_COMPONENT = "NEC Payroll Review Layout - Day building blocks - matrix layout" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "NEC NEC Payroll Review Layout - Cost Center" OWNER = "CUSTOM" QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST" QUALIFIER_ATTRIBUTE1 = "Custom9VO" #QUALIFIER_ATTRIBUTE8 = "DisplayValue" #QUALIFIER_ATTRIBUTE9 = "Value#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.Custom9VO" #QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATETIMECARD_BIND_END_DATE" #QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context" QUALIFIER_ATTRIBUTE27 = "Attribute2" QUALIFIER_ATTRIBUTE30 = "Y" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS

Modify Payroll Confirmation page - hxczzhxclayt0001.ldt

BEGIN HXC_LAYOUT_COMPONENTS "Payroll Confirmation Layout - Cost Center" OWNER = "CUSTOM" COMPONENT_VALUE = "COSTCENTER" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "210" COMPONENT_DEFINITION = "CHOICE_LIST" RENDER_TYPE = "WEB" PARENT_COMPONENT = "NEC Payroll Confirmation Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "NEC NEC Payroll Confirmation Layout - Cost Center" OWNER = "CUSTOM" QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST" QUALIFIER_ATTRIBUTE1 = "Custom9VO" #QUALIFIER_ATTRIBUTE8 = "DisplayValue" #QUALIFIER_ATTRIBUTE9 = "Value#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.Custom9VO" #QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATETIMECARD_BIND_END_DATE" #QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context" QUALIFIER_ATTRIBUTE27 = "Attribute2" QUALIFIER_ATTRIBUTE30 = "Y" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS
Categories: APPS Blogs

How to Configure OEM 10g Database Control after Clone

Aviad Elbaz - Thu, 2008-08-07 05:32

After cloning an EBS environment I had tried to start the OEM 10g Database Control (DBConsole) and I got this message:

[oratest@testsrv1 ~]$ emctl start dbconsole
TZ set to Israel
OC4J Configuration issue.
/oracle/test/db/testdb/10.2/ oc4j/j2ee/OC4J_DBConsole_testsrv1.domain_TEST not found.

However, it was an acceptable message since DB Console wasn't configured to this instance yet.

Using emca I've tried to recreate the DB Control configuration files as follow:

[oratest@testsrv1 ~]$ emca -config dbcontrol db

...
INFO: Database Control started successfully
...
INFO: >>>>>>>>>>> The Database Control URL is http://testsrv1.domain:5500/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully

Well, I thought it looks fine, but when I opened the DB Console I saw this message at the top of the page: 

error:
java.lang.Exception: Exception in sending Request :: null

After some research, logs digging and Metalink searches, I found that the Agent component should be relinked manually.
But before there are two issues in env_sysman.mk file that should be fixed before executing the relink command.

I will show my actions step by step:

  1. Stop DB Console:
    emctl stop dbconsole
     
  2. cd $ORACLE_HOME/sysman/lib
     
  3. Edit env_sysman.mk and look for "JRE_LIB_DIR"
    In my instance JRE_LIB_DIR was populated with the old environment name..
    I changed it from:
    JRE_LIB_DIR=/oracle/prod/db/proddb/10.2/jdk/jre/lib/i386
    To:
    JRE_LIB_DIR=/oracle/test/db/testdb/10.2/jdk/jre/lib/i386
     
  4. One more issue for env_sysman.mk, look for "$(LIBNMEM)  $(LIBNMER)"
    And change from:
    $(LIBNMEM)  $(LIBNMER) \
    To:
    $(LIBNMEM)  $(LIBNMER) $(LIBNMO) \
     
  5. Now relink the Agent component by issuing this command:
    make -f ins_sysman.mk agent
     
  6. Recreate the DB Control configuration files (Again, even if you did it before)
    emca -config dbcontrol db

Following this procedure will make the DBConsole configured and work for the new cloned instance.

Categories: APPS Blogs

How to enable FND_HELP search by creating an index on FND_LOBS efficiently

Aviad Elbaz - Tue, 2008-07-29 03:57

Have you ever tried to use the search option of Online Help in Oracle Applications?
Our users did... and they got "The page cannot be found" message...

I checked it on firefox, hope to get more accurate message, and I got this:
"Not Found. The requested URL /pls/DEV/fnd_help.search was not found on this server".
 

 

I checked fnd_help package and it's compiled and looks fine.

These errors appeared in error_log_pls:

[Mon Jul 28 10:34:54 2008] [warn] mod_plsql: Stale Connection due to Oracle error 20000
[Mon Jul 28 10:34:54 2008] [error] mod_plsql: /pls/DEV/fnd_help.search ORA-20000
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1209
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "APPS.FND_HELP", line 1043
ORA-06512: at "APPS.FND_HELP", line 873
ORA-06512: at line 20

I found note 306239.1 - "Cannot Search Online Help After Fresh Install of 11.5.10" which suggest that Applications interMedia Text indexes are corrupt. In my instance it even wasn't exist...

I followed this note which instructs to:

  1. drop index FND_LOBS_CTX;
  2. Rebuild the index using aflobbld.sql

But aflobbld.sql had been running for more than 10 hours and the size of DR$FND_LOBS_CTX$I table has reached to 35 GB !
I had been wondering how it can be that fnd_lobs table is less than 1GB and the index on it is 35 GB and counting.... ?!

Note 396803.1 - "FND_LOBS_CTX is having huge size, how to reduce the sizeof the index?" suggests it's a bug, indexing all documents in FND_LOB table, also the binary files, while using wrong filter.

So how can we make aflobbld.sql to index only FND_HELP documents?

For each row in FND_LOBS table the file_format column is populated with one of the following values: IGNORE, BINARY, TEXT.
aflobbld.sql will index only rows that have this column set to BINARY or TEXT.
If we set all rows to IGNORE except FND_HELP rows, we could index them only.

Note 397757.1 - "How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data" suggests the steps to do it.

These are the steps:

  1. Backup the fnd_lobs table before updating it, we will use it later:
     
    create table fnd_lobs_bk as select * from fnd_lobs;
     
    ** you can create a backup of this table and omit the file_date column to make this backup faster
     
  2. Drop index FND_LOBS_CTX if exists:
     
    drop index applsys.FND_LOBS_CTX;
     
  3. Update all rows to IGNORE except FND_HELP rows:
     
    update fnd_lobs
    set file_format = 'IGNORE'
    where nvl(program_name,'@') <> 'FND_HELP' ;
     
  4. Execute aflobbld.sql from OS terminal:
     
    sqlplus apps/sppas @$FND_TOP/sql/aflobbld.sql applsys apps;
     
  5. Since I'm not sure about the impact of leaving the FND_LOBS rows as IGNORE, I updated them back to the previous state:
     
    create unique index fnd_lobs_bk_u1 on fnd_lobs_bk (file_id);
     
    update (select fl.file_format ffo,flb.file_format ffb
            from fnd_lobs fl
                ,fnd_lobs_bk flb
            where fl.file_id = flb.file_id)
    set ffo=ffb;
     
    drop table fnd_lobs_bk;
     
  6. Check the search option.... it should work now.

You are welcome to leave a comment .

Aviad

Categories: APPS Blogs

Line Manager Time Entry funtionality

RameshKumar Shanmugam - Sat, 2008-07-26 10:38
This post in the continuation of the Previous posts Various way of entering time into OTL


Oracle Time & Labor Line Manager functionality is available with the product out of Box, The User Function name is Timecard Mgr (Function -HXC_TIMECARDS_MGR).

This function is included in the Seeded Manager Self Service menu HR_LINE_MANAGER_ACCESS_V4.0


IMPORTANT CATCH

By default this function will allow the manager to enter his own time, which most of the customer wont prefer the Manager to put in his time in the same place where the Manager can enter time for his direct reports.

there is an very simple way to restrict Manager for entering his time using the Timecard Mgr function

we will be able to limit by setting an additional parameter on the Timecard Mgr function (Function - HXC_TIMECARDS_MGR). To limit the manager from entering their own data, the parameter - pManagerEnabled should be entered at the end of other parameters (connector is &) with an N being 'Not Enabled', and a Y equaling 'Enabled'.


Example - to disable Line Manager entry of their own time using the Line Manager functionality, the parameter is added as: &pManagerEnabled=N



Try this out
Categories: APPS Blogs

Identifying Descriptive Flexfield in the Form

RameshKumar Shanmugam - Sun, 2008-07-20 22:08
Descriptive Flexfield are used to gather additional information about the business entity beyond the information required by the Oracle application

Or we can also define the descriptive flexfield as the configurable fields which we can used to capture the additional information

Descriptive flexfield appears on a form as a field enclosed within Brackets

Many time I get the question from my functional users how they will be able to identify the descriptive flexfield name in the form.

There is an easy way to find this

Navigate the the Form where you want to find the Descriotive flexfield
The select the following Menu

Help > Examine

In the Block Select ‘$DESCRIPTIVE_FLEXFIELD$
In the field select the Form in which you are looking for example “ PERSON.DF’. The Value field will display the Descriptive flexfield that you are looking for.

Categories: APPS Blogs

OTL - Authorized Delegate

RameshKumar Shanmugam - Sun, 2008-07-06 00:55

Authorized Delegate: This functionality is similar to the timekeeper functionality but has few difference

  • Timekeeper uses the PUI where as Authorized Delegate uses Self Service
  • Timekeeper can access the people who are assigned to the Timekeeper group where authorized Delegate is controlled by the HR:Security Profile
  • Timekeeper has seeded responsibility where the authorized delegate the user must build the custom responsibility using the Seeded Menu 'Authorized Delegate Timecard Entry’

This functionality is good to use when a single person need to enter time for fewer number of employees, if there are more number of employees it is always advisable to use timekeeper

Try it out

Categories: APPS Blogs

How to execute TKPROF on trace files larger than 2GB ? --> Use pipe

Aviad Elbaz - Tue, 2008-06-24 05:54

Here is a nice trick to work with files larger than 2GB on Unix/Linux using pipe.

First case - TKPROF

When trying to execute TKPROF on a trace file larger than 2 GB I got this error:

[udump]$ ll test_ora_21769.trc

-rw-r-----  1 oratest dba 2736108204 Jun 23 11:04 test_ora_21769.trc

[udump]$ tkprof test_ora_21769.trc test_ora_21769.out

TKPROF: Release 9.2.0.6.0 - Production on Thu Jun 23 21:05:10 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

could not open trace file test_ora_21769.trc

In order to successfully execute TKPROF on this trace file you can use the mkfifo command to create named pipe as follow:

  • Open a new unix/linux session (1st), change directory where the trace file exists and execute:

[udump]$ mkfifo mytracepipe
[udump]$ tkprof mytracepipe test_ora_21769.out

TKPROF: Release 9.2.0.6.0 - Production on Thu Jun 23 21:07:35 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

  • Open another session (2nd), change directory where the trace file exists and execute:

[udump]$ cat test_ora_21769.trc > mytracepipe

This way you'll successfully get the output file.

 

Second case - spool

Similar issue with spool to file larger than 2GB can be treat similarly.

$ mkfifo myspoolpipe.out

--> Create new named pipe called 'myspoolpipe.out'

$ dd if=myspoolpipe.out of=aviad.out &

--> What you read from 'myspoolpipe.out' write to 'aviad.out'

$ sqlplus user/pwd@dbname

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jun 24 12:05:37 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> spool myspoolpipe.out

--> Spool to the pipe

SQL> select .....

SQL> spool off
SQL> 5225309+294082 records in
5367174+1 records out

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

[1]+  Done                    dd if=myspoolpipe.out of=aviad.out

$ ls -ltr

prw-r--r--  1 oratest dba          0 Jun 24 12:22 myspoolpipe.out
-rw-r--r--  1 oratest dba 2747993487 Jun 24 12:22 aviad.out

Related Notes:

Note 62427.1 - 2Gb or Not 2Gb - File limits in Oracle
Note 94486.1 - How to Create a SQL*Plus Spool File Larger Than 2 GB on UNIX

Aviad

Categories: APPS Blogs

Mix of Old & New style buttons in OA Framework pages

Aviad Elbaz - Fri, 2008-06-06 08:59

After some heavy patches applied on our system we noticed that some buttons in OAF pages looks like the old style gray buttons while the others are fine new style yellow buttons.

For example:

 

(The "Advanced" is the old style and all the others are the new style)

Trying to clear cache ($COMMON_TOP/_pages) and bounce Apache didn't solve the problem.

The solution is hiding within jserv.properties:

  1. Edit $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties
  2. Change the following to TRUE:
    wrapper.bin.parameters=-Djava.awt.headless=true
  3. (optional) Clear all content from $OA_HTML/cabo/images/cache (e.g rm -rf $OA_HTML/cabo/images/cache)
  4. (optional) Clear all content from $COMMON_TOP/_pages
  5. Bounce Apache

And the problem will be resolved...

 


In order to make this change permanent, you should update the Application context file as follow, otherwise next run of AutoConfig will overwrite your change.

  1. Edit $APPL_TOP/admin/$CONTEXT_NAME.xml
  2. Change the following to:
    <java_awt_headless oa_var="s_java_awt_headless">true</java_awt_headless>
  3. Run AutoConfig on Apps Tier.
  4. Bounce Apache

Related Note: 368188.1 - Buttons Are Not Rendering Correctly In Self Service Framework Pages.

Aviad

Categories: APPS Blogs

OTL Time Keeper

RameshKumar Shanmugam - Sun, 2008-06-01 18:13
There are multiple ways you can enter time into OTL

  • Self Service time - employee entering his own time
  • Line Manager time Entry - Manager Entering time for his direct reports
  • Timestore Deposit API - Time import using the interface from the third party system
  • Timekeeper - one person entering time for the group of employees based on the group assigned to them
  • Authorized Delegate - One person entering time for the group of employees based on the security profile attached in the user/responsibility
In this section I am going to explain how to setup Timekeeper module for entering the time for the Group of employees

Before starting the Timekeeper configuration , if you directly go the Responsibility OTL Super timekeeper and click the function timekeeper Entry you will get the following error msg,

The above error message will clearly tell you what are the setup steps you need to do to enable the Timekeeper

The first two steps are to be done in the OTL Application developer Responsibility
1. Timekeeper Misc Setup Items
2. Timekeeper Layout attribute

The third step should be done in the OTL super Timekeeper Responsibility
3. Timekeeper Group is created

The Forth step should be done in the System administrator, this step should be done at the user level and it is only for the super timekeeper
4 Profile OTL: Allow Change Group Timekeeper

I'll be explaining each of the above steps in detail in my next post
Categories: APPS Blogs

FND_GLOBAL affected by New Global Performance Changes

Aviad Elbaz - Thu, 2008-05-29 04:59

After applying ATG Rollup 5 patch (and above) we discovered an issue with some of our custom developments.
For some processes we got the following errors:

ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger
has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 1410
ORA-06512: at "APPS.FND_GLOBAL", line 1655
ORA-06512: at "APPS.FND_GLOBAL", line 2170
ORA-06512: at "APPS.FND_GLOBAL", line 2312
ORA-06512: at "APPS.FND_GLOBAL", line 2250

and this:

ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been
detected in
fnd_global.set_nls.set_paramenters('NLS_LANGUAGE','AMERICAN').

After some debug work we found that this issue happens when executing FND_GLOBAL.apps_initialize more than once within a trigger/via a db link in the same transaction.

According to Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" this issue cause by a new global performance changes.

Oracle Development said: "Very sorry if the new global performance changes have exposed you to this error, but there is no way we can back out these changes. They are not only complex and wide spread but required to maintain functional performance levels. Using fnd_global to change user/resp context from a trigger is not only not supported it is ill advised."

OK, So we had to find a workaround to this issues and we found two...

I'll start with a sample of the new behavior of fnd_global to demonstrate the issue and the solutions/workarounds will come right after.

SQL> create table test1 (a number, b number);
Table created

SQL> insert into test1 (a) values (1001);
1 row inserted

SQL> insert into test1 (a) values (1002);
1 row inserted

SQL> commit;
Commit complete

SQL> create or replace trigger test1_trg_bi
  2  after update on test1
  3  for each row
  4  begin
  5       fnd_global.APPS_INITIALIZE(:new.a,1,1);
  6       -- fnd_request.submit_request...
  7       -- ....
  8       -- ....
  9  end;
10  /
Trigger created

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
        -1

SQL> update test1 set b=1101 where a=1001;
1 row updated

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
      1001

SQL> update test1 set b=1102 where a=1002;

update test1 set b=1102 where a=1002

ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger
has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 1410
ORA-06512: at "APPS.FND_GLOBAL", line 1655
ORA-06512: at "APPS.FND_GLOBAL", line 2170
ORA-06512: at "APPS.FND_GLOBAL", line 2312
ORA-06512: at "APPS.FND_GLOBAL", line 2250
ORA-06512: at "APPS.TEST1_TRG_BI", line 2
ORA-04088: error during execution of trigger 'APPS.TEST1_TRG_BI'

As you can see, the second update failed because apps_initialize was executed for the second time in the same transaction.

Now I'll show two ways to workaround this issue:

1) As suggested in Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" a wrapper Concurrent Request which contain a call to the context set (apps_initialize) and afterwards submits the original request, is one possible solution.

instead:

create or replace trigger test1_trg_bi
after update on test1
for each row
declare
     . . .
begin
     fnd_global.APPS_INITIALIZE(:new.a,1,1);
     ret_code := fnd_request.submit_request ('OWNER', 'ORIGINAL_CONC', . . .);
     . . .
     . . .
end;

create the following trigger:

create or replace trigger test1_trg_bi
after update on test1
for each row
declare
     . . .
begin
     ret_code := fnd_request.submit_request ('OWNER', 'WRAPPER_CONC', . . . , :new.a, . . . );
     . . .
     . . .
end;

additionally - create a new plsql concurrent (WRAPPER_CONC) that contains the fnd_global.apps_initialize and submits the ORIGINAL_CONC concurrent request.

This way, the apps_initialize statement executed in a separate transaction with no error.

This is the preferred and recommended solution by Oracle.

2) The second solution is easier to implement, works fine but according to Note: 556391.1 is not supported since it contains calls to fnd_global within a database trigger.

Anyway...

The idea is to call the apps_initialize in an Autonomous Transaction procedure.

Follow this sample:

SQL> create or replace procedure test1_apps_init (p_user_id number) is
  2  pragma autonomous_transaction;
  3  begin
  4       fnd_global.APPS_INITIALIZE(p_user_id,1,1);
  5       commit;
  6  end;
  7  /

Procedure created

SQL> create or replace trigger test1_trg_bi
  2  after update on test1
  3  for each row
  4  begin
  5       test1_apps_init (:new.a);
  6       -- fnd_request.submit_request...
  7       -- ....
  8       -- .....
  9  end;
10  /

Trigger created

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
        -1

SQL> update test1 set b=1101 where a=1001;
1 row updated

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
      1001

SQL> update test1 set b=1102 where a=1002;
1 row updated

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
      1002

As you can see, the update statements were executed successfully this time and the session was updated with the appropriate user context in each update statement.

Those two solutions are working fine, but keep in mind that the second is not supported.

You are welcome to leave a comment.

Aviad

Categories: APPS Blogs

Oracle Reports using BI Publisher

OracleAppsBlog - Mon, 2008-04-14 18:32
Categories: APPS Blogs

Oracle Transparent Gateways - General Description - Part I

Aviad Elbaz - Sun, 2008-04-13 17:15

A lot of companies have several applications based on more than one database system (e.g DB2, SQL Server, Sybase, etc).
Each database system store its own data and naturally there's a need to share data among the various heterogeneous database systems.

Oracle, starting with Oracle Database 9i, offers the "Oracle Transparent Gateways" (Oracle Database Gateways) to allow integration of Oracle database with non-Oracle databases.
Unlike "Oracle Generic Connectivity" that provide a generic solution to connect any ODBC/OLEDB compliant non-Oracle system using ODBC and OLEDB standards, the "Oracle Transparent Gateways" are solutions specifically tailored for each target non-Oracle database system.
The "Oracle Transparent Gateways" communicates using the target database native interface, it's make it possible to access to non-Oracle systems as if they were Oracle databases.

The Transparent Gateway solution composed of two parts:

  • Heterogeneous Services (HS) - this is a general integrated component that make it possible to connect to non-Oracle systems from Oracle database
  • Oracle Database Gateways (agent) - these are specific tailored agents for non-Oracle systems that make it possible to interacts with the target non-Oracle system.

Heterogeneous Services (HS)

This is a generic component for connecting to non-Oracle systems.
It's an integrated component of the database that "extends the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information" (Oracle Doc').

The following services are provided by the Heterogeneous Services (HS):

  • Transaction service
    Responsible for establishing authenticated connection when the non-Oracle system is accessed and close the connection when session end.
    Also responsible for global data integrity using two phase commit protocol, even for non-Oracle systems that do not support two phase commit natively.
  • SQL Service
    Provide the translation capabilities: SQL & data dictionary translations.
    The SQL services uses an information arrived from the Gateway to translate Oracle SQL to the appropriate SQL dialect of the non-Oracle system. Also, references to data dictionary tables in a query will be rewrite by the SQL Service and result with a result set as from Oracle database.
    ** Data type translation performed by the Gateway.
  • Procedural Service
    An interface for executing stored procedures on non-Oracle system.
  • Pass through SQL
    A mechanism for issuing a SQL statement against the non-Oracle system. It is useful when the statement/function/procedure are not supported by the Gateway.

Oracle Database Gateways (agents)

This component responsible for the interface to the remote non-Oracle system.
It's also responsible for SQL mappings and data type conversions.
The Gateway interacts with Heterogeneous Services to make it possible to transparently connect from an Oracle Database to a non-Oracle System.
In contrast to the HS (Heterogeneous Services) which is a generic component, the Gateways are tailored specifically for each target non-Oracle system.
There are Gateways for many systems such as: DB2, Sybase, Informix, SQL Server, IMS, VSAM, Adabas, Ingres, Teradata, to name a few.
The Gateway can be installed on the same server like the non-Oracle system or on the same server like the Oracle system or on a separate server.

Next post I'll show an example of connecting and retrieving data from a SQL Server database to an Oracle database using Oracle Transparent Gateway for Microsoft SQL Server including all configuration required for Transparent Gateway and the source Oracle System.

Related Documents for more information:

- Oracle® Transparent Gateway for Microsoft SQL Server Administrator's Guide 10g Release 2 (10.2) for Microsoft Windows (32-bit)

- Database Gateways Technical Whitepaper

You are more than welcome to leave a comment.

Aviad

Categories: APPS Blogs

Accrual Balance Display in Self Service HR

RameshKumar Shanmugam - Fri, 2008-04-11 19:18
One of the comon requirement that often times come in the Absence Managment is HR team want their employee to be able to see their available accrued/PTO balance.
As an employee it is always good to know how much vacation balance an employee is left with

Here is a simple setup that can enable your employee to view the available balance through the Self Service absence Management

Step 1: Define the Absence element
Step 2: Link the Element based on the Eligibilty Criteria
Step 3: Define the Absence Type
Step 4: Define the Accrual Plan
Step 5: Attach the Accrual Plan to the Employee
Step 6: Complete the setup for the Self Service HR
Step 7: Make sure employee is able access the absence Management Functionality

Setup steps to enable the Entitlement Balance in the Absence Management

Step 1: Create an Element set with the type as ‘Run Set’
Step 2: Attach the element set to the Profile option HR: Accrual Plan Element Set Displayed to User at the Responsibility level (Employee Self Service)
Step 3: Bounce the Appache

Now Navigate to the Employee Self Service > Absence Management > (T) Entitlement Balance


Try this out
Categories: APPS Blogs

LinkedIn Oracle Contractors Group

OracleAppsBlog - Thu, 2008-04-03 17:46
Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator - APPS Blogs