Set application message and token and retrieve it / Oracle application messages / FND Messages

Create a new application message as shown below.

You can set the message ,it's tokens, retrieve the value and then you can use it user hooks,form personalizations and other scripts.

DECLARE

   lv_msgdata   VARCHAR2 (32000);

BEGIN

   apps.fnd_message.set_name ('PER', 'XX_MSG_1');

   apps.fnd_message.set_token ('MSG_NAME', 'XXXX', TRANSLATE => TRUE);

   apps.fnd_message.set_token ('MSG_START_DATE',

                               '01-Jan-2014',

                               TRANSLATE      => TRUE

                              );

   lv_msgdata := apps.fnd_message.get ();

END;

Running the pre-processor after registering user-hook in oracle apps / Alternative of pre-processor

To run the pre-processor run one of the following commands:

cd $PER_TOP/admin/sql
Log into SQLPLUS as the APPS user
SQL> @hrahkall.sql

or

SQL> @hrahkone.sql


The first script will create all hook package bodies, whilst the second will
create hook package bodies for one API module only, and prompt for that
api_module_id.

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

Sometimes if you don't have server credentials then you can use below alternative to run the pre-processor through sqlplus

Log in to database through apps

SQL> @hrahkall.sql

instead of running above script you can execute below statement

exec hr_api_user_hooks_utility.create_hooks_all_modules;

SQL> @hrahkone.sql

instead of running above script you can execute below statement

exec hr_api_user_hooks_utility.create_hooks_one_module(p_module_id);

Important Tables in HRMS

Important Tables in HRMS

person details

• per_all_people_f

Assignment details

• per_all_assignments_f

person type details 

• per_person_types
• per_person_type_usages_f

Termination details

• per_periods_of_service

Person SIT

• per_person_analyses
• per_analysis_criteria

Person EIT

• per_people_extra_info

HR Locations

• hr_locations_all

HR Organizations

• hr_all_organization_units

HR Jobs

• per_jobs
• per_job_definitions

HR Grades

• per_grades
• per_grade_definitions

HR Positions

• hr_all_positions_f
• per_position_definitions

People Group

• pay_people_groups

Employee address

• per_addresses

Employee Phones

• per_phones

Employee Qualifications

• per_qualifications

Employee Contracts

• per_contracts_f

Organization additional information / organization classification/ organization eit details

• hr_organization_information

All self service transaction details are stored in the below tables.

For all hr transactions

- hr_api_transactions
- hr_api_transaction_steps
- hr_api_transaction_values

for payroll related transactions, e.g. salary change

- per_pay_transactions

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

History tables for SSHR transactions

PQH_SS_TRANSACTION_HISTORY
PQH_SS_STEP_HISTORY
PQH_SS_VALUE_HISTORY
PQH_SS_APPROVAL_HISTORY        

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


You can use above all tables in AME for tracking the self service transactions.

you can use transaction_id of hr_api_transactions and per_pay_transactions in  AME test workbench to see the attributes values, approvals , rules.

Join these tables with wf_notifications to identify status of the transaction.

How to Add custom organization classification and add custom organization EIT

How to Add  custom organization classification and add custom organization EIT

Add a new value to ‘ORG_CLASS’ lookup.

Search any organization, now you can see new custom classification in classification LOV and you can attach the new classification to any new organization.

Now create an organization EIT.

Go to Application developer è flex field è Descriptive è Segments 

Add a new structure to ‘Org Developer DF’

Add few segments to the structure.

Freeze and compile the new structure and DFF.

Then run Register Extra information types program with parameters as shown below.

Search any organization, go to classification and click on others button, you can see the newly structure

AUTONOMOUS Debug procedure -- IMP for debugging purpose in Oracle apps

- Create table XX_DEBUG_T

CREATE TABLE XX_DEBUG_T
(
  SEQ        NUMBER,
  DATA_TEXT  VARCHAR2(4000 BYTE)
);

- Create sequence XX_DEBUG_S

CREATE SEQUENCE APPS.XX_DEBUG_S
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;


- Create debug procedure as pragma procedure

CREATE OR REPLACE PROCEDURE APPS.xx_debug_p (p_data VARCHAR2)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
   a   NUMBER;
BEGIN
   a := xx_debug_s.NEXTVAL;

   INSERT INTO xx_debug_t
               (seq, data_text
               )
        VALUES (a, p_data
               );

   COMMIT;
END xx_debug_p;
/

You can call the above debug procedure in any other procedure,function, fast formulas, packages for debugging purpose.

You can see the latest messages in the log table .

select * from xx_debug_t
order by seq desc;

How to get apps password and application user password in R12/11i - With Clear details

Step -1 

Create get_pwd package specification as below.

CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;


Step -2 

Create get_pwd package body as below.

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;


Step-1 and step-2 are common in both R12 and 11i
For executing get_pwd package u need access on

'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

class file.Generally apps user will have this access , so you need to create the above package in APPS package.

Now For 11i

In 11i oracle has defined one seeded profile option GUEST_USER_PWD (Guest User Password)
This profile option holds the value 'GUEST/ORACLE' in almost all the 11i instances.

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

ALTER SESSION SET current_schema = apps;

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

For both step-3 and step-4 execute the above or log in as apps 

Step-3 

Query to get apps password

SELECT (SELECT get_pwd.decrypt
                  (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
                             FROM DUAL)
                         ),
                   usertable.encrypted_foundation_password
                  )
          FROM DUAL) AS apps_password
  FROM fnd_user usertable
 WHERE usertable.user_name LIKE
          UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                 1,
                                   INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                          '/'
                                         )
                                 - 1
                                )
                    FROM DUAL)
                );


Step-4 

Query to get application user password


SELECT usertable.user_name,
       (SELECT get_pwd.decrypt
                  (UPPER
                      ((SELECT (SELECT get_pwd.decrypt
                                          (UPPER
                                              ((SELECT UPPER
                                                          (fnd_profile.VALUE
                                                              ('GUEST_USER_PWD'
                                                              )
                                                          )
                                                  FROM DUAL)
                                              ),
                                           usertable.encrypted_foundation_password
                                          )
                                  FROM DUAL) AS apps_password
                          FROM fnd_user usertable
                         WHERE usertable.user_name LIKE
                                  UPPER
                                     ((SELECT SUBSTR
                                                 (fnd_profile.VALUE
                                                             ('GUEST_USER_PWD'),
                                                  1,
                                                    INSTR
                                                       (fnd_profile.VALUE
                                                             ('GUEST_USER_PWD'),
                                                        '/'
                                                       )
                                                  - 1
                                                 )
                                         FROM DUAL)
                                     ))
                      ),
                   usertable.encrypted_user_password
                  )
          FROM DUAL) AS encrypted_user_password
  FROM fnd_user usertable

 WHERE usertable.user_name LIKE 'SYSADMIN';


Now in R12

In R12 oracle has removed GUEST_USER_PWD (Guest User Password) Profile option.

So the above queries will not work.

oracle has provided new seeded Procedure fnd_web_sec.get_guest_username_pwd which will help us to find out user password.

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

ALTER SESSION SET current_schema = apps;

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

For both step-3 and step-4 execute the above or log in as apps 

Step-3 

Query to get apps password in R12.

SELECT (SELECT get_pwd.decrypt
                    (fnd_web_sec.get_guest_username_pwd,
                     usertable.encrypted_foundation_password
                    )
          FROM DUAL) AS apps_password
  FROM fnd_user usertable
 WHERE usertable.user_name LIKE
          (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd,
                          1,
                          INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1
                         )
             FROM DUAL);


Step-4 

Query to get application user password in R12.


SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name LIKE 'SYSADMIN';

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

Now if you have gone through the step-3 and step-4 queries for both 11i and R12.
we can simplified the queries as below :)
The below will work in both 11i and R12.

Step-3 

Query to get apps password

In the below query you can replace 'GUEST/ORACLE' with any application username and password.

Let say in the below example :

- user name : GUEST
- password   : ORACLE

SELECT (SELECT get_pwd.decrypt
                           ('GUEST/ORACLE',
                            fu.encrypted_foundation_password
                           )
          FROM DUAL) AS apps_password
  FROM fnd_user fu
 WHERE fu.user_name LIKE 'GUEST';

Let say in the below example :

- user name : TEST_USER_NAME
- password   : TEST_USER_pwd

Note : note that password should be case sensitive. 


SELECT (SELECT get_pwd.decrypt
                           ('TEST_USER_NAME/TEST_USER_pwd',
                            fu.encrypted_foundation_password
                           )
          FROM DUAL) AS apps_password
 FROM fnd_user fu

WHERE fu.user_name LIKE 'TEST_USER_NAME';


Step -4

Query to get application password.

TEST_APPS_PWD - is the apps password

TEST_USER_NAME  - is the application user for whom you want to know the password.


SELECT fu.user_name,
       (SELECT get_pwd.decrypt
                       ('TEST_APPS_PWD',
                        fu.encrypted_user_password
                       )
          FROM DUAL) AS encrypted_user_password
  FROM fnd_user fu

 WHERE fu.user_name LIKE 'TEST_USER_NAME';

Create table type LOV in WEB ADI through API

Create table type LOV in WEB ADI through API.

BEGIN

BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV

                  (p_application_id       => 20106,

                   p_interface_code       => 'GENERAL_122_INTF',

                   P_INTERFACE_COL_NAME   => 'P_BUSINESS_GROUP_ID',

                   P_ID_COL               => 'BUSINESS_GROUP_ID',

                   P_MEAN_COL             => 'NAME',

                   P_DESC_COL             => 'NAME',

                   P_TABLE                => 'PER_BUSINESS_GROUPS',

                   P_ADDL_W_C             => null,

                   P_WINDOW_CAPTION       => null,

                   P_WINDOW_WIDTH         => NULL,

                   P_WINDOW_HEIGHT        => NULL,

                   P_TABLE_BLOCK_SIZE     => NULL,

                   P_TABLE_SORT_ORDER     => 'ascending',

                   P_USER_ID              => 0,

                   P_TABLE_COLUMNS        => 'BUSINESS_GROUP_ID,NAME',

                   P_TABLE_SELECT_COLUMNS => 'BUSINESS_GROUP_ID',

                   P_TABLE_COLUMN_ALIAS   => 'BUSINESS_GROUP_ID',

                   P_TABLE_HEADERS        => 'BUSINESS_GROUP_ID,NAME',

                   P_POPLIST_FLAG         =>'N'

);

EXCEPTION

when NO_DATA_FOUND then

dbms_output.put_line('error no data ' ||sqlerrm);

when others then

dbms_output.put_line('error ' ||sqlerrm);

end;

Legal Employer LOV in assignment Screen

Legal Employer LOV is not containing any values. 

check if there is any organization is having classification as Legal Employer enabled for that business group.

While creating an organization ,if you select legal employer as classification and enable that classification then only the organization will come in Legal employer List of values.