gl_je_lines l, gl_je_headers h, gl_je_batches b, gl_code_combinations g

 select  b.je_batch_id batch_id ,

        h.je_header_id header_id ,

        l.je_line_num line ,

        l.code_combination_id ccid ,

        g.segment1 || '.' || g.segment2 || '.' || g.segment3 ||

        '.' || g.segment4 || '.' || g.segment5 || '.' || g.segment6 ||

        '.' || g.segment7 || '.' || g.segment8 || '.' || g.segment9 ||

        '.' || g.segment10 combination ,

        l.entered_dr entered_dr,

        l.entered_cr entered_cr,

        l.accounted_dr accounted_dr,

        l.accounted_cr accounted_cr,

        l.status

from    gl_je_lines l,

        gl_je_headers h,

        gl_je_batches b,

        gl_code_combinations g

where   b.je_batch_id = h.je_batch_id

        and h.je_header_id = &je_header_id

        and l.je_header_id = h.je_header_id

        and h.je_batch_id = b.je_batch_id

        and l.code_combination_id = g.code_combination_id

order by h.je_header_id, l.je_line_num;

SQL Query to List Oracle ERP Cloud Users and their Attached Roles

 SQL Query to List Oracle ERP Cloud Users and their Attached Roles 

SELECT pu.user_id,
         pu.username,
         ppnf.full_name,
         prdt.role_id,
         prdt.role_name,
         prd.role_common_name,
         prdt.description,
         TO_CHAR (pur.start_date, 'DD-MON-YYYY') role_start_date,
         TO_CHAR (pur.end_date, 'DD-MON-YYYY') role_end_date,
         prd.abstract_role,
         prd.job_role,
         prd.data_role,
         prd.duty_role,
         prd.active_flag
    FROM per_user_roles    pur,
         per_users         pu,
         per_roles_dn_tl   prdt,
         per_roles_dn      prd,
         per_person_names_f ppnf
   WHERE     1 = 1
         AND pu.user_id = pur.user_id
         AND prdt.role_id = pur.role_id
         AND prdt.language = USERENV ('lang')
         AND prdt.role_id = prd.role_id
         AND NVL (pu.suspended, 'N') = 'N'
         AND ppnf.person_id = pu.person_id
         AND ppnf.name_type = 'GLOBAL'
         AND pu.active_flag = 'Y'
         AND NVL (pu.start_date, SYSDATE) <= SYSDATE
         AND NVL (pu.end_date, SYSDATE) >= SYSDATE
         AND pu.username = &username
ORDER BY pu.username, prdt.role_name;

Fusion User Roles SQL Query & Tables

 

4 Important Tables of Oracle fusion user roles query

1.per_users

2.per_user_roles

3.per_roles_dn

4.per_roles_dn_tl

Detail SQL Query to Extract Oracle Fusion User & Roles

Query 1:-

select a1.USERNAME,

a1.ACTIVE_FLAG,

a1.CREDENTIALS_EMAIL_SENT,

a2.START_DATE,USER_ROLE_ID,

ROLE_ID,

ROLE_GUID,

ABSTRACT_ROLE,

JOB_ROLE,

DATA_ROLE,

ROLE_COMMON_NAME

from  per_users a1, per_user_roles a2,per_roles_dn a3

where a1.user_id=a2.USER_ID

and a2.ROLE_ID=a3.ROLE_ID

and a2.ROLE_GUID=a3.ROLE_GUID

Query 2:-

 

SELECT prdt.role_id, prdt.role_name,

              prdt.description RoleDescription,

              prdt.source_lang

FROM per_roles_dn_tl prdt

Query 3:-

SELECT pu.user_id,
         pu.username,
         ppnf.full_name,
         prdt.role_id,
         prdt.role_name,
         prd.role_common_name,
         prdt.description,
         TO_CHAR (pur.start_date, 'DD-MON-YYYY') role_start_date,
         TO_CHAR (pur.end_date, 'DD-MON-YYYY') role_end_date,
         prd.abstract_role,
         prd.job_role,
         prd.data_role,
         prd.duty_role,
         prd.active_flag
    FROM per_user_roles    pur,
         per_users         pu,
         per_roles_dn_tl   prdt,
         per_roles_dn      prd,
         per_person_names_f ppnf
   WHERE     1 = 1
         AND pu.user_id = pur.user_id
         AND prdt.role_id = pur.role_id
         AND prdt.language = USERENV ('lang')
         AND prdt.role_id = prd.role_id
         AND NVL (pu.suspended, 'N') = 'N'
   AND pu.username =:p_username
         AND ppnf.person_id = pu.person_id
         AND ppnf.name_type = 'GLOBAL'
         AND pu.active_flag = 'Y'
         AND NVL (pu.start_date, SYSDATE) <= SYSDATE
         AND NVL (pu.end_date, SYSDATE) >= SYSDATE
ORDER BY pu.username, prdt.role_name;