How to approve/reject or respond a notification through script

How to approve/reject or respond a notification through script

BEGIN
   wf_notification.setattrtext (nid         => '4754620',
                                aname       => 'RESULT',
                                avalue      => 'REJECTED'
                               );
   wf_notification.respond (nid         => '4754620',
                                     respond_comment =>  'Reject From backend',
                                     responder =>  'XXTEST');
   COMMIT;
END;


nid =  will be notification id

There are multiple ways to get notification id

-  Open the notification in the application through SYSADMIN or any other user
    in the notification below sent and to date, you can see the ID

or

-   you can search the notification in wf_notification table

aname = will be always RESULT

avalue = will be APPROVE,REJECTED or any other action .

Generally for avalue , perform the appropriate action on the notification through front end
and see the value of RESULT attribute in wf_notification_attributes table.

responder coment = any comment

responder => will be recipient role/user name

Batch Balance Adjustment

Batch Balance Adjustment

If your responsibility is not having ‘Batch Balance Adjustment’ function then add the 3 functions to your menu as shown below.

Functions Name:

-          Batch Balance Adjustment Spreadsheet Interface

-          Create or Update Batch Balance Adjustment Lines

-          Desktop Integration - Create Document

      Double click on ‘BBA Spreadsheet Interface’

k      Click on the ‘Create Batch’ button

       

It will create WEB ADI document, enter the details as below and then upload the document.

The Batch will be created.

Search the batch as shown below.

Select the batch, select create lines in the drop down and click on process button.

Select the element that you want to use for balance adjustment.

Then click on go button.

It will create a new WEB ADI document; enter the details shown as below and upload the document.

Then validate and transfer the batch.

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 fundas

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;