Delete Duplicate Rows from Oracle Table

DELETE FROM EMPLOYEES WHERE ROWID NOT IN
(SELECT MIN(ROWID)
FROM EMPLOYEES
GROUP BY LAST_NAME ) ;

How to compile Forms in 11i and R12

Compile Forms in 11i

$ f60gen module=<formname>.fmb userid=apps/<apps_pwd> output_file=$XX_TOP/forms/US/<formname>.fmx


Compile Forms in R12

$ frmcmp_batch <formname>.fmb userid=apps/<apps_pwd> output_file=$XX_TOP/12.0.0/form/US/<formname>.fmx module_type=form compile_all=special

Oracle will retire several certification tracks effective November 1, 2011

Exams to be retired November 1:

1Z0-109 Oracle WebLogic Server 10g Developer
1Z0-110 Oracle Weblogic Portal 10g Developer
1Z0-200 Oracle 11i E-Business Essentials
1Z0-207 Oracle Comm. Billing & Revenue Mgmt.: Pricing
1Z0-208 Oracle Comm. Billing & Revenue Mgmt.: Server Developer
1Z0-209 Oracle Comm. Billing & Revenue Mgmt.: System Administrator
1Z0-211 Oracle 11i General Ledger Fundamentals
1Z0-212 Oracle 11i Payables Fundamentals
1Z0-213 Oracle Receivables 11i Fundamentals
1Z0-221 Oracle 11i Inventory Management Fundamentals
1Z0-222 Oracle 11i Purchasing Fundamentals
1Z0-223 Oracle 11i Order Management Fundamentals
1Z0-231 Oracle 11i 2.6 Implement Workflow
1Z0-232 Oracle 11i System Administration
1Z0-233 Oracle 11i Install, Patch and Maintain Applications
1Z0-235 Oracle 11i Applications DBA: Fundamentals I
1Z0-236 Oracle 11i Applications DBA Fundamentals II
1Z0-255 Hyperion Essbase 7.1.2 Consultant
1Z0-257 Hyperion Financial Management 4.1 Consultant
1Z0-259 Hyperion Planning 4.1 Consultant
1Z0-262 Hyperion Planning 4.1 Administrator
1Z0-263 Hyperion System 9 BI+ 9.0 Administrator
1Z0-273 Hyperion Financial Management 4.1 Administrator I & II
1Z0-301 Oracle9iAS: Basic Administration
1Z0-615 Siebel 7.7 Consultant Core Exam
1Z0-620 Siebel 7.7 Analytics Application Developer Professional Exam
1Z0-630 Siebel 7.7 Analytics Server Architect Professional Exam
1Z0-640 Siebel 7.7 Analytics Data Warehouse Developer Professional Exam

Just Words

Believe that anything you can imagine you can achieve it real.
Having the devotion,passion and dedication to learn & Work

“Knowledge grows when it is shared“.

How To Prevent the Profile Option MO: Operating Unit being set to NULL at Site Level

steps
1.Login to the application as SYSADMIN
2.Responsibility : System Administrator
3.Function: Profiles --> System
4.Choose from the menu: Help --> Diagnostics --> Custom Code --> Personalize
5.Create a new Rule
oSeq : 10
oDescription : MO: Operating Unit
oLevel : Function
oEnabled : [checked]
6.Specify the following Condition:
oTrigger Event : WHEN-VALIDATE-RECORD
oTrigger Object : PROFILE_VALUES
oCondition : :PROFILE_VALUES.PROFILE_OPTION_NAME = 'ORG_ID' AND :PROFILE_VALUES.SITE_VISIBLE_VALUE IS NULL
oProcessing Mode : Not in Enter-Query Mode
7.Assign the following Actions to the Rule:
oSeq : 10
oType : Message
oDescription : MO: Operating Unit
oLanguage : All
oEnabled : [checked]
oMessage Type : Error
oMessage Text : You must specify a profile option value at Site level for the profile option 'MO: Operating Unit'.
8.Save the changes
9.Close the System Profile Values form

Result
An error message will appear if an end user now tries to remove the profile option value.

Useful Queries

--Finding Invaled Objects
select count(*) from dba_objects where status ='INVALID'

--To Check which node is running what service
select * from fnd_nodes

--Information about the bugs fixed in Installation
select * from ad_bugs

--Information about the applied patches
select * from ad_applied_patches

--Stores values for various profile options
select * from fnd_profile_option_values

--Information about various profile options
select * from fnd_profile_options

--To Find database version
select * from v$version

--To Determine Oracle Apps 11i Version ?
select release_name from fnd_product_groups;

How to list E-Business Suite Profile Option values for all levels using SQLPlus

select p.profile_option_name SHORT_NAME,
       n.user_profile_option_name NAME,
       decode(v.level_id,
               10001, 'Site',
               10002, 'Application',
               10003, 'Responsibility',
               10004, 'User',
               10005, 'Server',
               10006, 'Org',
               10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
                             decode(to_char(v.level_value), '-1', 'Server',
                             'Server+Resp')),
               'UnDef') LEVEL_SET,
       decode(to_char(v.level_id),
               '10001', '',
               '10002', app.application_short_name,
               '10003', rsp.responsibility_key,
               '10004', usr.user_name,
               '10005', svr.node_name,
               '10006', org.name,
               '10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
                          decode(to_char(v.level_value), '-1',
                            (select node_name from fnd_nodes
                             where node_id = v.level_value2),
                        (select node_name from fnd_nodes
                         where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
               'UnDef') "CONTEXT",
       v.profile_option_value VALUE
from fnd_profile_options p,
     fnd_profile_option_values v,
     fnd_profile_options_tl n,
     fnd_user usr,
     fnd_application app,
     fnd_responsibility rsp,
     fnd_nodes svr,
     hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
  and p.profile_option_name = n.profile_option_name
  and upper(p.profile_option_name) in (
            select profile_option_name from fnd_profile_options_tl
            where  upper(profile_option_name) like upper('%&profile_name%')
            and    upper(profile_option_name) in (select profile_option_name
              from   fnd_profile_options_tl
              where  upper(user_profile_option_name) like upper('%&user_profile_name%')))
  and    usr.user_id (+) = v.level_value
  and    rsp.application_id (+) = v.level_value_application_id
  and    rsp.responsibility_id (+) = v.level_value
  and    app.application_id (+) = v.level_value
  and    svr.node_id (+) = v.level_value
  and    org.organization_id (+) = v.level_value
  order by short_name, user_profile_option_name, level_id, level_set;

Bash Script To Remove Files Older Than 7 Days

find /path -name "*" -mtime +7 -exec rm -f {} \ ;

How to control access to the database and understand validnode checking

You can configure the sqlnet.ora file to allow and deny access to the database
via the validnode checking parmeters.


TCP.VALIDNODE_CHECKING
Use to specify whether to screen access to the database.Value is either YES or ON

TCP.EXCLUDED_NODES
Use to specify which clients using the TCP/IP protocol are denied access to the database.
Hostname and ipaddress can be used

TCP.INVITED_NODES
Use to specify which clients using the TCP/IP protocol are allowed access to the database.
Hostname and ipadddress can be used.


Example sqlnet.ora file (set where database is running)

TCP.VALIDNODE_CHECKING = YES
TCP.EXCLUDED_NODES= (138.3.33.33)
TCP.INVITED_NODES=(138.4.44.44, hammer)

Hide Diagnostics Menu Entry In Oracle Applications

Use the system profiles: 'Hide Diagnostics menu entry'
to control the visibility of the Diagnostics menu