How to delete Business Event and Subscription

Declare
   Cursor c_event_cur
   Is
     Select we.guid event_guid
           ,wes.guid subscription_guid
     From wf_events we
           ,wf_event_subscriptions wes
     Where we.name = 'sat.oracle.apps.xxpm.generateProposal'  -- your custom event name
     And wes.event_filter_guid = we.guid;  
Begin
   For i in c_event_cur
   Loop
      wf_events_pkg.delete_row(i.event_guid);
      wf_event_subscriptions_pkg.delete_row(i.subscription_guid);
   End Loop;
   commit;
Exception  
   When Others Then
      dbms_output.put_line(sqlerrm);
End;

After Sucessful Cloning: FRM 92101 issues on cloned instance while starting forms

I setup my clone instance successfully, but when login into forms application getting following error message:

Form starting with error

oracle.forms.net.ConnectionException: Forms session <1> aborted: unable to communicate with runtime process.
  at oracle.forms.net.ConnectionException.createConnectionException(Unknown Source)

I checked the following forms log files:
formsstd.err and formsstd.out

formsstd.out:

16/06/10 09:31:36 Oracle Containers for J2EE 10g (10.1.3.5.0)  initialized
16/06/10 09:35:01 ListenerServlet init()
16/06/10 09:35:02 Forms session <1> aborted: unable to communicate with runtime process.
16/06/10 09:54:12 Shutting down...
16/06/10 09:55:14 FormsServlet init():
    configFileName:     /u02/app/oracle/PROD/inst/apps/CLONE_XXXXX/ora/10.1.2/forms/server/appsweb.cfg
    testMode:           false

Solution:

Compile all menus and application forms using adadmin tool

R12.1.3: adcfgclone.pl failed with afcpctx.sh INSTE8_PRF 136

R12.1.3 application tier cloning script was failed with following error:

 [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /u02/app/oracle/PROD/inst/apps/CLONE_<CONTEXT_NAME>/admin/install
      afcpctx.sh              INSTE8_PRF         136


AutoConfig is exiting with status 1

RC-50014: Fatal: Execution of AutoConfig was failed
Raised by oracle.apps.ad.clone.ApplyApplTop
ERROR: AutoConfig completed with errors. Check logfile at /u02/app/oracle/PROD/inst/apps/CLONE_<CONTEXT_NAME>/admin/log/ApplyAppsTier_06100752.log for details.
ApplyApplTop Completed Successfully.

When I execute afcpctx.sh independently: 

Floating point exception| FNDCPUCF $USERNAME 0 Y /u02/app/oracle/PROD/inst/apps/CLONE_<CONTEXT_NAME>/appl/admin/CLONE_<CONTEXT_NAME>.xml

Solution:

source the application environment file
run relink as follows  
    adrelink.sh force=y ranlib=y "AD all"  
    adrelink.sh force=y ranlib=y "fnd FNDCPUCF"

try again

$perl adcfgclone.pl appsTier

now cloning will be completed successfully.

Recursively check ownership of all files

find files in a folder and their user permission other than oracle

find <<Starting search location>> -not -user oracle -printf "%p %u\n"

find files in a folder and thier group permission other than oinstall

find <<Starting search location>>  -not -user oinstall -printf "%p %u\n" 

Display Sender Name in oracle apex mail

You can display send name using oracle apex_email

 apex_mail.send(
                p_from => "Sender Display Name"<fromemailaddress>

don't enter space after the sender display name double quotes(") and from email address less than symbol (<)

List users with DBA privilege

Which users having dba provilege assigned, query dba_role_privs in the SYS schema

SQL>  select * from dba_role_privs where granted_role='DBA';

Rebuild UNUSABLE Partition Indexes

Validate and generate rebuild script for partition indexes

Select 'ALTER INDEX '||INDEX_OWNER||'.'||index_name ||' rebuild partition ' || PARTITION_NAME ||';'  from DBA_IND_PARTITIONS Where  status = 'UNUSABLE'

How to disable CC Direct Manager functionality in Expenses Workflow

1. Open the wft file and double click on 'IS_MANAGER_IN_THE_APPR_PROCESS'.
2. Click on the Node Attributes and here we have an attribute named 'CC Direct Manager Notification'.
3. Please update the value to No, save the workflow file to the database.
4. Bounce the workflow listeners as well as apache.

R12.ATG_PF.C.Delta.5 Release Update Pack Features

This release introduces an updated Look and Feel for Oracle Application Framework-based pages, the Oracle Alta UI. Ensure that you either set the Oracle Applications Look and Feel profile option to "Alta Look and Feel" or to a Look and Feel that extends the Alta Look and Feel, or leave this profile option blank to use the standard Alta Look and Feel by default. Oracle supports only this standard Look and Feel, or one that extends it, for R12.ATG_PF.C.Delta.5. Oracle no longer supports the Look and Feel options of previous releases.

ADOP phase abort failed with ORA-20001: Error while calling ad_zd_adop.abortORA-01555: snapshot too old

ADOP pahse=abort is failed

When I was running adop phase=abort, stop with the below error:




Log: /u02/app/oracle/PROD/fs_ne/EBSapps/log/adop/53/adop_20160305_072738.log
[START 2016/03/05 07:27:40] Arguments passed to adop
    phase=abort
[END   2016/03/05 07:27:40] Arguments passed to adop
[STATEMENT] Verifying parameters
[STATEMENT] Sourcing Run edition environment /u02/app/oracle/PROD/fs2/EBSapps/appl/APPSPROD_XXXXX15.env

[EVENT]     Validating system setup.
    [STATEMENT] Checking for required environment
    [PROCEDURE] Running: adpawc -nodisp
    [STATEMENT]  Using 8 workers (Default: 8, Recommended maximum limit: 324)
    [PROCEDURE] [START 2016/03/05 07:28:17] Identifying Admin server node
[EVENT]     Node registry is valid.
    [PROCEDURE] [START 2016/03/05 07:28:17] Performing database sanity checks

[PROCEDURE] [END   2016/03/05 07:28:17] Performing database sanity checkss

[PROCEDURE] [START 2016/03/05 07:28:18] Acquiring lock on sessions table

[STATEMENT] Locking ad_adop_sessions table for XXXXX with wait interval of 60 seconds and number of tries 2

[PROCEDURE] [END   2016/03/05 07:28:18] Acquiring lock on sessions table

[EVENT]     Checking for existing adop sessions.

    [PROCEDURE] [START 2016/03/05 07:28:18] Checking for any pending sessions

    [EVENT]     Checking for pending hotpatch session.

        [PROCEDURE] [START 2016/03/05 07:28:18] Checking active hotpatch session

 [STATEMENT] value of incomplete_session: "0"

        [PROCEDURE] [END   2016/03/05 07:28:18] Checking active hotpatch session

    [EVENT]     Checking for pending cleanup session.

        [PROCEDURE] [START 2016/03/05 07:28:18] Checking for active cleanup session

        [PROCEDURE] [END   2016/03/05 07:28:18] Checking for active cleanup session

    [STATEMENT] Master Session ID: "53"

    Continuing with existing session [Session ID: 53].

    [STATEMENT] Master Node: "XXXXXX"

    [STATEMENT] Check for incomplete patching cycle with session ID 53 on hostname kwshqapp15

    [STATEMENT] SQL output: "0"

    [STATEMENT] Open ADOP Session ID: "53"

    [STATEMENT] Using session ID from existing incomplete patching cycle

    [PROCEDURE] [END   2016/03/05 07:28:18] Checking for any pending sessions

[PROCEDURE] [START 2016/03/05 07:28:18] Unlocking sessions table

[STATEMENT] Unlocking ad_adop_sessions table for XXXXXX with wait interval of 60 seconds and number of tries 2

[PROCEDURE] [END   2016/03/05 07:28:18] Unlocking sessions table

[PROCEDURE] [START 2016/03/05 07:28:18] Running adzdoptl.pl

===========================================================================

ADOP (C.Delta.7)

Session ID: 53

Node: kwshqapp15

Phase: abort

===========================================================================

[STATEMENT] Inside createPatchCtxFile().

[STATEMENT] Patch file system context file exists

[PROCEDURE] [START 2016/03/05 07:28:19] Running abort phase

[PROCEDURE] [START 2016/03/05 07:28:19] Determining abort status

[STATEMENT] abort_status for session ID - 53 : N.

[PROCEDURE] [END   2016/03/05 07:28:19] Determining abort status

[STATEMENT] Status of abort phase: N

[PROCEDURE] [START 2016/03/05 07:28:19] Updating adop session status

[PROCEDURE] [START 2016/03/05 07:28:19] Updating session process ID

[PROCEDURE] [END   2016/03/05 07:28:19] Updating session process ID

[STATEMENT] SQL statement : "  update ad_adop_sessions

  set status='R'

   where adop_session_id = 53 and appltop_id = 195 and node_name='XXXXXX'

"

[PROCEDURE] [END   2016/03/05 07:28:19] Updating adop session status

[PROCEDURE] [START 2016/03/05 07:28:19] Updating session timestamps

[STATEMENT] ABORT Phase START TIME: 05-03-2016 07:28:20

[PROCEDURE] [END   2016/03/05 07:28:20] Updating session timestamps

[PROCEDURE] [START 2016/03/05 07:28:20] Updating session status

[STATEMENT] Updating abort_status=R for session ID : 53

[PROCEDURE] [END   2016/03/05 07:28:20] Updating session status

[PROCEDURE] [START 2016/03/05 07:28:20] Performing abort phase

[STATEMENT] Inside uploadCtxFile().

[STATEMENT] Running getJavaCmd procedure.

[STATEMENT] Running getClasspath procedure.

[STATEMENT] Upload of context file successful

[STATEMENT] LOG FILE: /u02/app/oracle/PROD/fs_ne/EBSapps/log/adop/53/abort_20160305_072738/PROD_kwshqapp15/CtxSynchronizer_uploadCtxFile.log.

[EVENT]     Running abort phase.

    [EVENT]     Log: @ADZDSHOWLOG.sql "2016/03/05 07:28:24"

    [ERROR]     Failed to execute sql statement:

        alter session set events='10624 trace name context forever, level 1';

        declare

        begin

          ad_zd_adop.abort(x_mode=>'ADOP',x_session_id=>53);

        exception

          when others then

            update ad_adop_sessions set status='F'

            where adop_session_id=53;

            commit;

            raise_application_error(-20001,'Error while calling ad_zd_adop.abort' || sqlerrm);

        end;

    [ERROR]     SQLPLUS error: buffer=

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Mar 5 07:28:26 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> SQL> Connected.

SQL>    declare

*

ERROR at line 1:

ORA-20001: Error while calling ad_zd_adop.abortORA-01555: snapshot too old:

rollback segment number 11 with name "_SYSSMU11_1381604294$" too small

ORA-06512: at line 9

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    [UNEXPECTED]Error occurred performing abort phase database actions

    [UNEXPECTED]Abort phase has failed.

    [PROCEDURE] [START 2016/03/05 07:57:04] Updating session timestamps

    [STATEMENT] ABORT Phase END TIME: 05-03-2016 07:57:04

    [PROCEDURE] [END   2016/03/05 07:57:04] Updating session timestamps

    [PROCEDURE] [START 2016/03/05 07:57:04] Unlocking sessions table

    [STATEMENT] Unlocking ad_adop_sessions table for XXXXX with wait interval of 60 seconds and number of tries 2

    [PROCEDURE] [END   2016/03/05 07:57:04] Unlocking sessions table

    [PROCEDURE] [START 2016/03/05 07:57:04] Updating adop session status

    [STATEMENT] SQL statement : "  update ad_adop_sessions

  set status='F'

   where adop_session_id = 53 and appltop_id = 195 and node_name='XXXXX'

adop exiting with status = 1 (Fail)

Too overcome this issue.I have added additional datafile to the undotablespace using below:

ALTER TABLESPACE "APPS_UNDOTS2" ADD DATAFILE '/u01/app/oracle/PROD/data/apps_undo05.dbf' SIZE 2000M REUSE AUTOEXTEND OFF

How to list discoverer documents by user access log

List user access log on discoverer documents and sheets

SELECT b.user_name Doc_Owner
               ,a.doc_name
               ,a.doc_developer_key
               ,c.QS_DOC_NAME
               ,c.QS_DOC_DETAILS
               ,d.user_name accessed_by
               ,QS_CREATED_DATE
FROM <schema name>.EUL5_DOCUMENTS a
            ,fnd_user b ,
           ,<schema name>.EUL5_QPP_STATS c ,
           ,fnd_user d
WHERE a.DOC_CREATED_BY <> 'ORACLE_APPS'
AND '#'||TO_CHAR(b.user_id) = a.DOC_CREATED_BY
AND a.doc_name = c.QS_DOC_NAME
AND '#'||TO_CHAR(d.user_id) = c.QS_CREATED_BY

ORDER BY QS_CREATED_DATE DESC 

How to list All User Created Discoverer Worksheets Using SQL statements

Below query can display, all discoverer documents created by any user with dates.

SELECT b.user_name Doc_Owner
             , a.doc_name
             , a.doc_developer_key
             , a.DOC_CREATED_DATE
             , a.doc_updated_date
FROM <schema name>.EUL5_DOCUMENTS a
           , fnd_user b
Where a.DOC_CREATED_BY <> 'ORACLE_APPS'
AND '#'||TO_CHAR(b.user_id) = a.DOC_CREATED_BY

FNDCPASS Utility ALLORACLE

A large set of schemas provided by the individual products of Oracle Applications [for example, ABM,AHL,GL...,ZSA,ZX]

 The default password for these schemas is same as the schema name. 

You should change these passwords after the installation.

To change all ORACLE  schema passwords:

FNDCPASS <logon> 0 Y <system/password> ALLORACLE  <new_password> 

  

e.g. FNDCPASS apps/apps 0 Y system/manager ALLORACLE WELCOME

API to delete value set values

Use the following api to delete unwanted/unusable value set values

 fnd_flex_values_pkg.delete_row (<<flex_Value_id>>);

Flex value id can be select using the following select statement:

Select a.flex_value_id
From FND_FLEX_VALUES_VL a
  ,fnd_flex_value_sets b
Where a.FLEX_VALUE_SET_ID = b.FLEX_VALUE_SET_ID
And b.FLEX_VALUE_SET_NAME = 'XXPM_VALUE_SETNAME'


Don't use this for deleting application seeded value set values. Above solution can only be used for custom value sets.

PLS-00302: component 'PURGE_BUSINESS_EXCEPTIONS' must be declared

When I run concurrent program "Purge Diagnostic and Log Messages", following error occurs:
Purge Diagnostic and Log Messages

ORACLE error 6550 in FDPSTP
Cause: FDPSTP failed due to ORA-06550: line 1, column 20: 
PLS-00302: component 'PURGE_BUSINESS_EXCEPTIONS' must be declared 
ORA-06550: line 1, column 7: 
PL/SQL: Statement ignored 

To overcome the above error "Purge Logs and Closed System Alerts " should be run instead of "Purge Debug Log and System Alerts" in Release 12.2

You can disable concurrent request program  "Purge Debug Log and System Alerts"