AOL : Query to fetch responsibilities attached to a User

 Query to fetch the responsibilities attached to a FND_USER login.

SELECT t.responsibility_id,

       t.responsibility_name,

       v.RESPONSIBILITY_KEY,

       (select (fa.APPLICATION_SHORT_NAME ||' - '|| fa.APPLICATION_NAME) application_name

          from fnd_application_vl fa

         where fa.APPLICATION_ID = t.application_id) application_name

  FROM FND_RESPONSIBILITY_TL t, fnd_responsibility_vl v

 WHERE t.RESPONSIBILITY_ID IN

       (SELECT RESPONSIBILITY_ID

          FROM FND_USER_RESP_GROUPS

         WHERE USER_ID IN (SELECT USER_ID

                             FROM FND_USER

                            WHERE USER_NAME = 'FNDUSERNAME'))

   and v.RESPONSIBILITY_ID = t.responsibility_id;

AOL: Apps Initialize Script

 Apps Initialization Script  : 

l_resp_id number; 

l_user_id number; 

l_app_id   number;

BEGIN

select t.RESPONSIBILITY_ID 

into g_resp_id

 from fnd_responsibility_tl t where t.RESPONSIBILITY_NAME like 'Purchasing Administrator';

SELECT f.USER_ID

into l_user_id

 FROM fnd_user f WHERE f.user_name =  'USERNAME';

 select t.application_id 

into l_app_id  

from fnd_application_vl t where application_short_name = 'PO';

  fnd_global.apps_initialize(user_id           => l_user_id

                            ,resp_id           => g_resp_id

                            ,resp_appl_id      => l_app_id  

                            ,security_group_id => 0

                            ,server_id         => NULL);

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('SQLERRM ' || SQLERRM);

  

END;

AOL: Script to reset fnd user login password from backend in Oracle

AOL: Script to reset password from backend in Oracle 

The below script is a ready to use script, you can set the new password according to your company password policy. 

/************************************************************

*PURPOSE: To change the   password of a user from backend(DB)   *

*AUTHOR: Arokia Antony Jayaraj                              *

*************************************************************/

SET serveroutput ON;

DECLARE

  v_user_name    VARCHAR2(30):= UPPER('SYSADMIN');

  v_new_password VARCHAR2(30):= 'oracle123';

  v_status       BOOLEAN;

BEGIN

  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, 

                                              newpassword => v_new_password 

                                            );

  IF v_status  THEN

    dbms_output.put_line ('The password reset successfully for the entered User:'||v_user_name);

    COMMIT;

  ELSE

    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    ROLLBACK;

  END IF;

END;

 

HR: Organization Classifications Query

 This query help you to find the different Organization Classification information of an Org.

1.  select distinct t.ORG_INFORMATION_CONTEXT from hr_organization_information t

2.     SELECT o.organization_id,

               o.organization_id,

               otl.name,

               o.date_from,

               o.date_to,

               o.internal_address_line,

               o.location_id,

               o.comments,

               o4.org_information1

          FROM hr_all_organization_units    o,

               hr_all_organization_units_tl otl,

               hr_organization_information  o2,

               hr_organization_information  o3,

               hr_organization_information  o4

         WHERE o.organization_id = otl.organization_id

           AND o.organization_id = o2.organization_id(+)

           AND o.organization_id = o3.organization_id

           AND o.organization_id = o4.organization_id

           AND o3.org_information_context = 'Business Group Information' 

           AND o2.org_information_context(+) = 'Work Day Information' -- Get a distinct of this from the above query 

           AND o4.org_information_context = 'CLASS' -- This means the Classification

           AND o4.org_information1 = 'HR_BG' -- BG Business group 

           AND o4.org_information2 = 'Y';

AOL: Query to Check DB NLS Language settings and parameters

 

Query to Check DB NLS Language settings and parameters:

 SELECT    (SELECT VALUE

             FROM nls_database_parameters

            WHERE parameter = 'NLS_LANGUAGE')

       || '_'

       || (SELECT VALUE

             FROM nls_database_parameters

            WHERE parameter = 'NLS_TERRITORY')

       || '.'

       || (SELECT VALUE

             FROM nls_database_parameters

            WHERE parameter = 'NLS_CHARACTERSET') LANGUAGE

  FROM DUAL;

HR: Queries related to Absence (PENDING,APPROVED-HR_API_TRANSACTIONS)

 The below list of queries fetch the list of absences 

--To get pending leaves:

 

SELECT hat.transaction_id, hats.transaction_step_id

  FROM hr_api_transactions hat, hr_api_transaction_steps hats

 WHERE hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'

   AND hat.transaction_group = 'ABSENCE_MGMT'

   AND hat.transaction_identifier = 'ABSENCES'

   AND hat.transaction_ref_id IS NOT NULL

   AND hat.status = 'Y'

   AND hat.transaction_id = hats.transaction_id

   AND hat.CREATOR_PERSON_ID = :p_person_id;

   

-- Query to get Approved Leaves :

SELECT pat.NAME absence_type, paa.date_start leave_start_date,

                paa.date_end leave_end_date, paa.absence_days, paa.abs_information_category,

                paa.abs_information2, paa.abs_information3,paa.abs_information4

           FROM per_absence_attendances paa,

                per_absence_attendance_types pat,

                per_all_people_f papf

          WHERE paa.absence_attendance_type_id =

                                                pat.absence_attendance_type_id

            AND papf.person_id = paa.person_id

            AND TRUNC (paa.date_start) BETWEEN TRUNC

                                                    (papf.effective_start_date)

                                           AND TRUNC (papf.effective_end_date)

            AND TRUNC (p_date) BETWEEN TRUNC (paa.date_start)

                                   AND TRUNC (NVL (paa.date_end,

                                                   paa.date_start

                                                  )

                                             )

            AND papf.person_id = :p_person_id;

-- List of Absence types and their additional informations 

 SELECT pat.NAME absence_type, paa.date_start leave_start_date,

                paa.date_end leave_end_date, paa.absence_days, paa.abs_information_category,

                paa.abs_information2, paa.abs_information3,paa.abs_information4

           FROM per_absence_attendances paa,

                per_absence_attendance_types pat,

                per_all_people_f papf

          WHERE paa.absence_attendance_type_id =

                                                pat.absence_attendance_type_id

            AND papf.person_id = paa.person_id

            AND TRUNC (paa.date_start) BETWEEN TRUNC

                                                    (papf.effective_start_date)

                                           AND TRUNC (papf.effective_end_date)

            AND TRUNC (p_date) BETWEEN TRUNC (paa.date_start)

                                   AND TRUNC (NVL (paa.date_end,

                                                   paa.date_start

                                                  )

                                             )

            AND papf.person_id = :p_person_id;

HR: Query to Get Organizational Hierarchy of an Organization

This query lists the Organization structure from the below navigation : 

Global HRMS Manager -> Work Structures -> Hierarchy(Or Global Hierarchy)

 

SELECT *

  FROM (SELECT (lpad(' ', (lvl - 1) * 5, ' ') || haou_c.name) org_name,

               apps.hr_general.decode_lookup('ORG_TYPE', haou_c.type) TYPE,

               org_hier.*

          FROM hr_all_organization_units haou_c,

               (SELECT hier.organization_id_parent,

                       hier.organization_id_child,

                       LEVEL lvl,

                       sys_connect_by_path(hier.organization_id_child, '/') org_level,

                       sys_connect_by_path(LEVEL, '/') hier_level

                  FROM per_org_structure_elements hier,

                       (SELECT vers.org_structure_version_id

                          FROM per_org_structure_versions_v  vers,

                               per_organization_structures_v struct

                         WHERE struct.business_group_id = 81

                           AND struct.name = 'your Hierarchy Structure name'

                           AND vers.organization_structure_id =

                               struct.organization_structure_id

                           AND struct.business_group_id =

                               vers.business_group_id

                           AND SYSDATE BETWEEN vers.date_from AND

                               nvl(vers.date_to, SYSDATE)

                           AND rownum = 1) orgstr

                 WHERE hier.org_structure_version_id =

                       orgstr.org_structure_version_id

                   AND hier.business_group_id = 81

                 START WITH hier.organization_id_parent =

                            (SELECT a.organization_id

                               FROM hr_all_organization_units a

                              WHERE a.organization_id = 81)

                        AND hier.org_structure_version_id =

                            orgstr.org_structure_version_id

                        AND hier.business_group_id = 81

                CONNECT BY PRIOR

                            organization_id_child = organization_id_parent) org_hier

         WHERE haou_c.organization_id = org_hier.organization_id_child

         ORDER BY org_hier.org_level);