ORA-20002: 3825: Error '-24033 - ORA-24033: no recipients for message' encountered during execution of Rule function 'WF_RULE.ERROR_RULE' for event 'oracle.apps.fnd.cp.gsc.SvcComponent.stop' with key 'SVC:29-APR-2015'. FUNCTION_NAME=WF_EVENT_OJMSTEXT_QH.enqueue()

Error:
When we try to start/stop the Worflow mailer it shows this error,

ORA-20002: 3825: Error '-24033 - ORA-24033: no recipients for message' encountered during execution of Rule function 'WF_RULE.ERROR_RULE' for event 'oracle.apps.fnd.cp.gsc.SvcComponent.stop' with key 'SVC:29-APR-2015'. FUNCTION_NAME=WF_EVENT_OJMSTEXT_QH.enqueue()


Solution:

To resolve the issue try restarting the components

1. Workflow Mailer Service
2. Workflow Document Web Services
3. Workflow Agent Listener Service

Navigation to restart the components

• Login as SYSADMIN --> System Administrator --> Oracle Application Manager --> Workflow 
• Click on "Notification Mailer" --> Click "Workflow Mailer Services" Under Container Column --> Select "Workflow Mailer Services" --> Select "Start" --> "Go" button --> Ok
• Similary do the same for "Workflow Document Web Services" and "Workflow Agent Listener Service"

Now the workflow mailer will be Started/Stopped

EBS : Transparent Data Encryption (TDE) implementation

1. Add the below entry in the sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
  (SOURCE= (METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=/oradb/oracle/11.2.0.3/admin/<CONTEXT_NAME>/encryption_wallet/)
))


2. Create the directory to contain the encrypted key:

mkdir -p /oradb/oracle/11.2.0.3/admin/<CONTEXT_NAME>/encryption_wallet/


3. Creating the wallet with password "g00g1e": login as sysdba

Syntax:
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY <password>;

Eg:
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "g00g1e";

4. Encrypting the columns of the table: login as sysdba

Syntax:
ALTER TABLE owner.table_name MODIFY(column_name ENCRYPT NO SALT);
ALTER TABLE owner.table_name MODIFY(column_name ENCRYPT);

Eg:
ALTER TABLE HR.PER_ALL_PEOPLE_F MODIFY(NATIONAL_IDENTIFIER encrypt no salt);
ALTER TABLE HR.PQP_EXT_CROSS_PERSON_RECORDS MODIFY(NATIONAL_IDENTIFIER encrypt no salt);
ALTER TABLE BEN.BEN_PL_F MODIFY(NAME  encrypt);

5. Once you added the columns to be encrypted close the wallet using below command: login as sysdba

Syntax:
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY <password>;

Eg: 
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "g00g1e";

6. Opening the wallet: login as sysdba
Syntax: 
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY <password>;

Eg: 
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "g00g1e";

7. How to check whether the wallet is open / closed : login as sysdba

SQL> COL WRL_PARAMETER FOR A65
SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                                     STATUS
-------------------- ----------------------------------------------------------------- ------------------
file                 /oradb/oracle/11.2.0.3/admin/TEST_drebsdb01/encryption_wallet/    OPEN


8. SQL to check what are the table colums that are encrypted : login as sysdba

SQL> COL OWNER FOR A5
SQL> COL COLUMN_NAME FOR A20
SQL> COL ENCRYPTION_ALG FOR A16
SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME                     COLUMN_NAME          ENCRYPTION_ALG   SAL
----- ------------------------------ -------------------- ---------------- ---
HR    PER_ALL_PEOPLE_F               NATIONAL_IDENTIFIER  AES 192 bits key NO
HR    PQP_EXT_CROSS_PERSON_RECORDS   NATIONAL_IDENTIFIER  AES 192 bits key NO
BEN   BEN_PL_F                       NAME                 AES 192 bits key NO
BEN   BEN_OPT_F                      NAME                 AES 192 bits key NO
BEN   BEN_REPORTING                  NATIONAL_IDENTIFIER  AES 192 bits key YES
HR    GHR_MASS_ACTIONS_PREVIEW       NATIONAL_IDENTIFIER  AES 192 bits key YES
HR    GHR_RIF_REGISTERS              NATIONAL_IDENTIFIER  AES 192 bits key YES
HRI   EDW_HR_PERM_ASSIGN_LSTG        NATIONAL_IDENTIFIER  AES 192 bits key YES
HRI   EDW_HR_PERM_ASSIGN_LTC         NATIONAL_IDENTIFIER  AES 192 bits key YES
HR    HR_H2PI_EMPLOYEES              NATIONAL_IDENTIFIER  AES 192 bits key YES
BEN   BEN_PRTT_ENRT_RSLT_F           BNFT_AMT             AES 192 bits key YES

9. When the Wallet is close we cannot access the data from backend:
SQL> SELECT NATIONAL_IDENTIFIER FROM HR.PER_ALL_PEOPLE_F WHERE NATIONAL_IDENTIFIER IS NOT NULL;
SELECT NATIONAL_IDENTIFIER FROM HR.PER_ALL_PEOPLE_F WHERE NATIONAL_IDENTIFIER IS NOT NULL
                                   *
ERROR at line 1:
ORA-28365: wallet is not open

Note: We need to open the wallet in order to view the values of the columns table that are encrypted.

DB : pdksh-5.2.14 missing while installing 11g in RHEL-6.5 (64-bit)

Error:

pdksh-5.2.14 missing

Solution:

1. Change directory to <path>/database/stage/cvu/cv/admin
2. Backup cvu_config
$ cp cvu_config backup_cvu_config
3. Edit cvu_config and change the following line:
from:
CV_ASSUME_DISTID=OEL4
to:
CV_ASSUME_DISTID=OEL6
4. Save the updated cvu_config file
5. Install the 11.2.0.3 or 11.2.0.4 software using <path>/database/runInstaller
    $ cd <path>/database
    $ ./runInstaller
OUI should now perform the OEL6 prerequisite checks (which are identical to the RHEL6 prerequisite checks) and no longer report that packages "elfutils-libelf-devel-0.97" and "pdksh-5.2.14" are missing.

Reference : Installing 11.2.0.3 Or 11.2.0.4 (32-bit (x86) or 64-bit (x86-64) ) On RHEL6 Reports That Packages "elfutils-libelf-devel-0.97" And "pdksh-5.2.14" Are Missing (PRVF-7532) (Doc ID 1454982.1)

EBS : Change the FORMS color from backend

How to change the java forms color from backuend?

Solution:

Connect to apps schema
sqlplus apps/apps

set serveroutput on
REM Change Java Scheme Color - Site Level
REM update fnd_profile_option_values set PROFILE_OPTION_VALUE='TEAL' where PROFILE_OPTION_ID = 1002097 and level_id = 10001;
DECLARE
        stat boolean;
BEGIN
        dbms_output.disable;
        dbms_output.enable(100000);
        stat := FND_PROFILE.SAVE('FND_COLOR_SCHEME', 'PURPLE', 'SITE');
        IF stat THEN
                dbms_output.put_line( 'Stat = TRUE - profile updated' );
        ELSE
                dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
        END IF;
        commit;
END;
/

instead of PRURPLE provide any of the below value to change the color

• BLUE
• KHAKI
• OLIVE
• PURPLE
• RED
• SWAN
• TEAL
• TITANIUM

EBS : Arabic characters are appearing as "?" Question mark in the standard output of CCR

Solution:

Check ALBAN*.ttf files exists in the directory $OA_JRE_TOP/lib/fonts/
ls -l $OA_JRE_TOP/lib/fonts/ALBAN*.ttf

  1. If it doesnt exists 

  1.1 Copy it from the $FND_TOP/resource/ directory
 cp $FND_TOP/resource/ALBAN*.ttf $OA_JRE_TOP/lib/fonts/

  1.2 Restart the apache and the oc4j
          cd $ADMIN_SCRIPTS_HOME
         adapcctl.sh stop
          adoacorectl.sh stop
          adoacorectl.sh start
          adapcctl.sh start

  1.3 Retest the issue.

       Reference : PDF Reports Show Arabic Characters As Question Marks (Doc ID 1199013.1)

  2. If exists

     2.1 Login as SYSADMIN
     2.2 System Adminstrator --> Profile --> System
     2.3 Check the profile option "FND: NATIVE CLIENT ENCODING" is set to the database character set
    Check the database character set using the below query
    SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

EBS : List of EBS users with their responsibilities

select fu.user_name,frv.responsibility_name,papf.full_name,papf.previous_last_name,
(select name from hr_organization_units where organization_id = paaf.ORGANIZATION_ID) department_name
from FND_USER_RESP_GROUPS_DIRECT furgd,fnd_user fu,
                         fnd_responsibility_vl frv,per_all_assignments_f paaf,per_all_people_f papf
       where furgd.user_id = fu.user_id
       and furgd.responsibility_id = frv.responsibility_id
       --and fu.user_name like 'H%'
       and paaf.person_id = fu.employee_id
       and paaf.person_id = papf.person_id
       order by 5,1

EBS : List of users who logged in to EBS application in last 14 days

SELECT DISTINCT user_name,  
  TO_CHAR(last_logon_date,'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN_DATE,
  papf.full_name,
  (select name from hr_organization_units where organization_id = paaf.ORGANIZATION_ID) department_name
FROM fnd_user fu, per_all_people_f papf,per_all_assignments_f paaf
WHERE last_logon_date >=sysdate-14
--and fu.user_name like 'H%'
and paaf.person_id = fu.employee_id
and paaf.person_id = papf.person_id
order by 4,2,1 desc