Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Equivalent table in cloud fusion for the EBS standard table fnd_responsibility

Received Response
221
Views
7
Comments
User_GMRN8
User_GMRN8 Rank 1 - Community Starter
edited April 10 in Oracle Transactional Business Intelligence

Dear Experts,

I am trying create a OTBI report using a simple query as shown below using standard EBS table 'fnd_responsibility'. But it throws an error 'ORA-00942: table or view does not exist'. Kindly advise what is the equivalent table in cloud fusion for fnd_responsibility.

This is the navigation we are following to create a simple report.

Home->OD common->Reports and analytics->Browse catalog->Create->Data model

image.png

Thanks,

Sara

Tagged:

Answers

  • Fazal Subhan111
    Fazal Subhan111 Rank 1 - Community Starter

                  PER_USER_ROLES PUR,

                   PER_ROLES_DN PRD,

                   PER_ROLES_DN_TL PRDT,

                   ASE_ROLE_VL ARV

           

  • Fazal Subhan111
    Fazal Subhan111 Rank 1 - Community Starter

    Below query should get you started

    SELECT *

      FROM (SELECT DISTINCT PU.PERSON_ID,

                   PU.USER_ID,

                   PU.USERNAME,

                   TO_CHAR (PU.START_DATE, 'DD-MM-YYYY') USER_START_DATE,

                   TO_CHAR (PU.END_DATE, 'DD-MM-YYYY') USER_END_DATE,

                   DECODE (PU.ACTIVE_FLAG,  'N', 'Inactive',  'Y', 'Active')

                      IS_USER_ACCOUNT_ACTIVE,

                   DECODE (PU.SUSPENDED,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ACCOUNT_SUSPENDED,

                   PPNF.FULL_NAME,

                   PRDT.ROLE_NAME,

                   replace(replace(PRDT.DESCRIPTION,CHR(10),''),CHR(13),'')  ROLEDESCRIPTION,

                   DECODE (PUR.METHOD_CODE,

                           'A', 'Automatic',

                           'M', 'Manually',

                           'E', 'Externally Provisioned')

                      METHOD_CODE,

                   DECODE (PUR.ACTIVE_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_ACTIVE,

                   DECODE (PUR.TERMINATED_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_TERMINATED,

                   TO_CHAR (PUR.START_DATE, 'DD-MM-YYYY') ROLE_START_DATE,

                   TO_CHAR (PUR.END_DATE, 'DD-MM-YYYY') ROLE_END_DATE,

                   PRD.ROLE_COMMON_NAME,

                   CASE

                      WHEN (    PRD.ABSTRACT_ROLE = 'Y'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Abstract Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'Y'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Job Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'Y')

                      THEN

                         'Data Role'

                      WHEN (    PRD.ABSTRACT_ROLE IS NULL

                            AND PRD.JOB_ROLE IS NULL

                            AND PRD.DATA_ROLE IS NULL)

                      THEN

                         '--NA--'

                   END

                      AS USERROLETYPE,

                   PUR.CREATION_DATE ROLE_ASSIGNMENT_DATE,

                   PUR.CREATED_BY ROLE_ASSIGNED_BY,

                   NULL ROLE_REMOVED_BY,

                   NULL ROLE_REMOVAL_DATE,

                   DECODE (ARV.IS_SEEDED,  'N', 'Custom',  'Y', 'Seeded')

                      CUSTOM_SEEDED

              FROM PER_USERS PU,

                   PER_PERSON_NAMES_F PPNF,

                   PER_USER_ROLES PUR,

                   PER_ROLES_DN PRD,

                   PER_ROLES_DN_TL PRDT,

                   ASE_ROLE_VL ARV

             WHERE TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE(+)

         AND PPNF.EFFECTIVE_END_DATE(+)

                   AND PPNF.NAME_TYPE(+) = 'GLOBAL'

                   AND PPNF.PERSON_ID(+) = PU.PERSON_ID

                   AND PU.USERNAME NOT LIKE 'FUSION_APPS_%'

                   AND PUR.ACTIVE_FLAG = 'Y'

                   AND PU.USER_ID = PUR.USER_ID

                   AND PUR.ROLE_ID = PRD.ROLE_ID

                   AND PUR.ROLE_GUID = PRD.ROLE_GUID

                   AND PUR.ROLE_ID = PRDT.ROLE_ID

                   AND PRDT.SOURCE_LANG = 'US'

                   AND PRDT.ROLE_NAME = ARV.ROLE_NAME

                   AND ARV.LANGUAGE = USERENV ('LANG')

  • Fazal Subhan111
    Fazal Subhan111 Rank 1 - Community Starter

    Below query should get you started

    SELECT *

      FROM (SELECT DISTINCT PU.PERSON_ID,

                   PU.USER_ID,

                   PU.USERNAME,

                   TO_CHAR (PU.START_DATE, 'DD-MM-YYYY') USER_START_DATE,

                   TO_CHAR (PU.END_DATE, 'DD-MM-YYYY') USER_END_DATE,

                   DECODE (PU.ACTIVE_FLAG,  'N', 'Inactive',  'Y', 'Active')

                      IS_USER_ACCOUNT_ACTIVE,

                   DECODE (PU.SUSPENDED,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ACCOUNT_SUSPENDED,

                   PPNF.FULL_NAME,

                   PRDT.ROLE_NAME,

                   replace(replace(PRDT.DESCRIPTION,CHR(10),''),CHR(13),'')  ROLEDESCRIPTION,

                   DECODE (PUR.METHOD_CODE,

                           'A', 'Automatic',

                           'M', 'Manually',

                           'E', 'Externally Provisioned')

                      METHOD_CODE,

                   DECODE (PUR.ACTIVE_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_ACTIVE,

                   DECODE (PUR.TERMINATED_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_TERMINATED,

                   TO_CHAR (PUR.START_DATE, 'DD-MM-YYYY') ROLE_START_DATE,

                   TO_CHAR (PUR.END_DATE, 'DD-MM-YYYY') ROLE_END_DATE,

                   PRD.ROLE_COMMON_NAME,

                   CASE

                      WHEN (    PRD.ABSTRACT_ROLE = 'Y'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Abstract Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'Y'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Job Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'Y')

                      THEN

                         'Data Role'

                      WHEN (    PRD.ABSTRACT_ROLE IS NULL

                            AND PRD.JOB_ROLE IS NULL

                            AND PRD.DATA_ROLE IS NULL)

                      THEN

                         '--NA--'

                   END

                      AS USERROLETYPE,

                   PUR.CREATION_DATE ROLE_ASSIGNMENT_DATE,

                   PUR.CREATED_BY ROLE_ASSIGNED_BY,

                   NULL ROLE_REMOVED_BY,

                   NULL ROLE_REMOVAL_DATE,

                   DECODE (ARV.IS_SEEDED,  'N', 'Custom',  'Y', 'Seeded')

                      CUSTOM_SEEDED

              FROM PER_USERS PU,

                   PER_PERSON_NAMES_F PPNF,

                   PER_USER_ROLES PUR,

                   PER_ROLES_DN PRD,

                   PER_ROLES_DN_TL PRDT,

                   ASE_ROLE_VL ARV

             WHERE TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE(+)

         AND PPNF.EFFECTIVE_END_DATE(+)

                   AND PPNF.NAME_TYPE(+) = 'GLOBAL'

                   AND PPNF.PERSON_ID(+) = PU.PERSON_ID

                   AND PU.USERNAME NOT LIKE 'FUSION_APPS_%'

                   AND PUR.ACTIVE_FLAG = 'Y'

                   AND PU.USER_ID = PUR.USER_ID

                   AND PUR.ROLE_ID = PRD.ROLE_ID

                   AND PUR.ROLE_GUID = PRD.ROLE_GUID

                   AND PUR.ROLE_ID = PRDT.ROLE_ID

                   AND PRDT.SOURCE_LANG = 'US'

                   AND PRDT.ROLE_NAME = ARV.ROLE_NAME

                   AND ARV.LANGUAGE = USERENV ('LANG')

  • Fazal Subhan111
    Fazal Subhan111 Rank 1 - Community Starter

    Below query should get you started

    SELECT *

      FROM (SELECT DISTINCT PU.PERSON_ID,

                   PU.USER_ID,

                   PU.USERNAME,

                   TO_CHAR (PU.START_DATE, 'DD-MM-YYYY') USER_START_DATE,

                   TO_CHAR (PU.END_DATE, 'DD-MM-YYYY') USER_END_DATE,

                   DECODE (PU.ACTIVE_FLAG,  'N', 'Inactive',  'Y', 'Active')

                      IS_USER_ACCOUNT_ACTIVE,

                   DECODE (PU.SUSPENDED,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ACCOUNT_SUSPENDED,

                   PPNF.FULL_NAME,

                   PRDT.ROLE_NAME,

                   replace(replace(PRDT.DESCRIPTION,CHR(10),''),CHR(13),'')  ROLEDESCRIPTION,

                   DECODE (PUR.METHOD_CODE,

                           'A', 'Automatic',

                           'M', 'Manually',

                           'E', 'Externally Provisioned')

                      METHOD_CODE,

                   DECODE (PUR.ACTIVE_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_ACTIVE,

                   DECODE (PUR.TERMINATED_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_TERMINATED,

                   TO_CHAR (PUR.START_DATE, 'DD-MM-YYYY') ROLE_START_DATE,

                   TO_CHAR (PUR.END_DATE, 'DD-MM-YYYY') ROLE_END_DATE,

                   PRD.ROLE_COMMON_NAME,

                   CASE

                      WHEN (    PRD.ABSTRACT_ROLE = 'Y'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Abstract Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'Y'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Job Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'Y')

                      THEN

                         'Data Role'

                      WHEN (    PRD.ABSTRACT_ROLE IS NULL

                            AND PRD.JOB_ROLE IS NULL

                            AND PRD.DATA_ROLE IS NULL)

                      THEN

                         '--NA--'

                   END

                      AS USERROLETYPE,

                   PUR.CREATION_DATE ROLE_ASSIGNMENT_DATE,

                   PUR.CREATED_BY ROLE_ASSIGNED_BY,

                   NULL ROLE_REMOVED_BY,

                   NULL ROLE_REMOVAL_DATE,

                   DECODE (ARV.IS_SEEDED,  'N', 'Custom',  'Y', 'Seeded')

                      CUSTOM_SEEDED

              FROM PER_USERS PU,

                   PER_PERSON_NAMES_F PPNF,

                   PER_USER_ROLES PUR,

                   PER_ROLES_DN PRD,

                   PER_ROLES_DN_TL PRDT,

                   ASE_ROLE_VL ARV

             WHERE TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE(+)

         AND PPNF.EFFECTIVE_END_DATE(+)

                   AND PPNF.NAME_TYPE(+) = 'GLOBAL'

                   AND PPNF.PERSON_ID(+) = PU.PERSON_ID

                   AND PU.USERNAME NOT LIKE 'FUSION_APPS_%'

                   AND PUR.ACTIVE_FLAG = 'Y'

                   AND PU.USER_ID = PUR.USER_ID

                   AND PUR.ROLE_ID = PRD.ROLE_ID

                   AND PUR.ROLE_GUID = PRD.ROLE_GUID

                   AND PUR.ROLE_ID = PRDT.ROLE_ID

                   AND PRDT.SOURCE_LANG = 'US'

                   AND PRDT.ROLE_NAME = ARV.ROLE_NAME

                   AND ARV.LANGUAGE = USERENV ('LANG')

  • Fazal Subhan111
    Fazal Subhan111 Rank 1 - Community Starter

    Below query should get you started

    SELECT *

      FROM (SELECT DISTINCT PU.PERSON_ID,

                   PU.USER_ID,

                   PU.USERNAME,

                   TO_CHAR (PU.START_DATE, 'DD-MM-YYYY') USER_START_DATE,

                   TO_CHAR (PU.END_DATE, 'DD-MM-YYYY') USER_END_DATE,

                   DECODE (PU.ACTIVE_FLAG,  'N', 'Inactive',  'Y', 'Active')

                      IS_USER_ACCOUNT_ACTIVE,

                   DECODE (PU.SUSPENDED,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ACCOUNT_SUSPENDED,

                   PPNF.FULL_NAME,

                   PRDT.ROLE_NAME,

                   replace(replace(PRDT.DESCRIPTION,CHR(10),''),CHR(13),'')  ROLEDESCRIPTION,

                   DECODE (PUR.METHOD_CODE,

                           'A', 'Automatic',

                           'M', 'Manually',

                           'E', 'Externally Provisioned')

                      METHOD_CODE,

                   DECODE (PUR.ACTIVE_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_ACTIVE,

                   DECODE (PUR.TERMINATED_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_TERMINATED,

                   TO_CHAR (PUR.START_DATE, 'DD-MM-YYYY') ROLE_START_DATE,

                   TO_CHAR (PUR.END_DATE, 'DD-MM-YYYY') ROLE_END_DATE,

                   PRD.ROLE_COMMON_NAME,

                   CASE

                      WHEN (    PRD.ABSTRACT_ROLE = 'Y'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Abstract Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'Y'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Job Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'Y')

                      THEN

                         'Data Role'

                      WHEN (    PRD.ABSTRACT_ROLE IS NULL

                            AND PRD.JOB_ROLE IS NULL

                            AND PRD.DATA_ROLE IS NULL)

                      THEN

                         '--NA--'

                   END

                      AS USERROLETYPE,

                   PUR.CREATION_DATE ROLE_ASSIGNMENT_DATE,

                   PUR.CREATED_BY ROLE_ASSIGNED_BY,

                   NULL ROLE_REMOVED_BY,

                   NULL ROLE_REMOVAL_DATE,

                   DECODE (ARV.IS_SEEDED,  'N', 'Custom',  'Y', 'Seeded')

                      CUSTOM_SEEDED

              FROM PER_USERS PU,

                   PER_PERSON_NAMES_F PPNF,

                   PER_USER_ROLES PUR,

                   PER_ROLES_DN PRD,

                   PER_ROLES_DN_TL PRDT,

                   ASE_ROLE_VL ARV

             WHERE TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE(+)

         AND PPNF.EFFECTIVE_END_DATE(+)

                   AND PPNF.NAME_TYPE(+) = 'GLOBAL'

                   AND PPNF.PERSON_ID(+) = PU.PERSON_ID

                   AND PU.USERNAME NOT LIKE 'FUSION_APPS_%'

                   AND PUR.ACTIVE_FLAG = 'Y'

                   AND PU.USER_ID = PUR.USER_ID

                   AND PUR.ROLE_ID = PRD.ROLE_ID

                   AND PUR.ROLE_GUID = PRD.ROLE_GUID

                   AND PUR.ROLE_ID = PRDT.ROLE_ID

                   AND PRDT.SOURCE_LANG = 'US'

                   AND PRDT.ROLE_NAME = ARV.ROLE_NAME

                   AND ARV.LANGUAGE = USERENV ('LANG')

  • Fazal Subhan111
    Fazal Subhan111 Rank 1 - Community Starter

    SELECT *

      FROM (SELECT DISTINCT PU.PERSON_ID,

                   PU.USER_ID,

                   PU.USERNAME,

                   TO_CHAR (PU.START_DATE, 'DD-MM-YYYY') USER_START_DATE,

                   TO_CHAR (PU.END_DATE, 'DD-MM-YYYY') USER_END_DATE,

                   DECODE (PU.ACTIVE_FLAG,  'N', 'Inactive',  'Y', 'Active')

                      IS_USER_ACCOUNT_ACTIVE,

                   DECODE (PU.SUSPENDED,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ACCOUNT_SUSPENDED,

                   PPNF.FULL_NAME,

                   PRDT.ROLE_NAME,

                   replace(replace(PRDT.DESCRIPTION,CHR(10),''),CHR(13),'')  ROLEDESCRIPTION,

                   DECODE (PUR.METHOD_CODE,

                           'A', 'Automatic',

                           'M', 'Manually',

                           'E', 'Externally Provisioned')

                      METHOD_CODE,

                   DECODE (PUR.ACTIVE_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_ACTIVE,

                   DECODE (PUR.TERMINATED_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_TERMINATED,

                   TO_CHAR (PUR.START_DATE, 'DD-MM-YYYY') ROLE_START_DATE,

                   TO_CHAR (PUR.END_DATE, 'DD-MM-YYYY') ROLE_END_DATE,

                   PRD.ROLE_COMMON_NAME,

                   CASE

                      WHEN (    PRD.ABSTRACT_ROLE = 'Y'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Abstract Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'Y'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Job Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'Y')

                      THEN

                         'Data Role'

                      WHEN (    PRD.ABSTRACT_ROLE IS NULL

                            AND PRD.JOB_ROLE IS NULL

                            AND PRD.DATA_ROLE IS NULL)

                      THEN

                         '--NA--'

                   END

                      AS USERROLETYPE,

                   PUR.CREATION_DATE ROLE_ASSIGNMENT_DATE,

                   PUR.CREATED_BY ROLE_ASSIGNED_BY,

                   NULL ROLE_REMOVED_BY,

                   NULL ROLE_REMOVAL_DATE,

                   DECODE (ARV.IS_SEEDED,  'N', 'Custom',  'Y', 'Seeded')

                      CUSTOM_SEEDED

              FROM PER_USERS PU,

                   PER_PERSON_NAMES_F PPNF,

                   PER_USER_ROLES PUR,

                   PER_ROLES_DN PRD,

                   PER_ROLES_DN_TL PRDT,

                   ASE_ROLE_VL ARV

             WHERE TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE(+)

         AND PPNF.EFFECTIVE_END_DATE(+)

                   AND PPNF.NAME_TYPE(+) = 'GLOBAL'

                   AND PPNF.PERSON_ID(+) = PU.PERSON_ID

                   AND PU.USERNAME NOT LIKE 'FUSION_APPS_%'

                   AND PUR.ACTIVE_FLAG = 'Y'

                   AND PU.USER_ID = PUR.USER_ID

                   AND PUR.ROLE_ID = PRD.ROLE_ID

                   AND PUR.ROLE_GUID = PRD.ROLE_GUID

                   AND PUR.ROLE_ID = PRDT.ROLE_ID

                   AND PRDT.SOURCE_LANG = 'US'

                   AND PRDT.ROLE_NAME = ARV.ROLE_NAME

                   AND ARV.LANGUAGE = USERENV ('LANG')

  • Fazal Subhan111
    Fazal Subhan111 Rank 1 - Community Starter

    SELECT *

      FROM (SELECT DISTINCT PU.PERSON_ID,

                   PU.USER_ID,

                   PU.USERNAME,

                   TO_CHAR (PU.START_DATE, 'DD-MM-YYYY') USER_START_DATE,

                   TO_CHAR (PU.END_DATE, 'DD-MM-YYYY') USER_END_DATE,

                   DECODE (PU.ACTIVE_FLAG,  'N', 'Inactive',  'Y', 'Active')

                      IS_USER_ACCOUNT_ACTIVE,

                   DECODE (PU.SUSPENDED,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ACCOUNT_SUSPENDED,

                   PPNF.FULL_NAME,

                   PRDT.ROLE_NAME,

                   replace(replace(PRDT.DESCRIPTION,CHR(10),''),CHR(13),'')  ROLEDESCRIPTION,

                   DECODE (PUR.METHOD_CODE,

                           'A', 'Automatic',

                           'M', 'Manually',

                           'E', 'Externally Provisioned')

                      METHOD_CODE,

                   DECODE (PUR.ACTIVE_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_ACTIVE,

                   DECODE (PUR.TERMINATED_FLAG,  'N', 'No',  'Y', 'Yes')

                      IS_USER_ROLE_TERMINATED,

                   TO_CHAR (PUR.START_DATE, 'DD-MM-YYYY') ROLE_START_DATE,

                   TO_CHAR (PUR.END_DATE, 'DD-MM-YYYY') ROLE_END_DATE,

                   PRD.ROLE_COMMON_NAME,

                   CASE

                      WHEN (    PRD.ABSTRACT_ROLE = 'Y'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Abstract Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'Y'

                            AND PRD.DATA_ROLE = 'N')

                      THEN

                         'Job Role'

                      WHEN (    PRD.ABSTRACT_ROLE = 'N'

                            AND PRD.JOB_ROLE = 'N'

                            AND PRD.DATA_ROLE = 'Y')

                      THEN

                         'Data Role'

                      WHEN (    PRD.ABSTRACT_ROLE IS NULL

                            AND PRD.JOB_ROLE IS NULL

                            AND PRD.DATA_ROLE IS NULL)

                      THEN

                         '--NA--'

                   END

                      AS USERROLETYPE,

                   PUR.CREATION_DATE ROLE_ASSIGNMENT_DATE,

                   PUR.CREATED_BY ROLE_ASSIGNED_BY,

                   NULL ROLE_REMOVED_BY,

                   NULL ROLE_REMOVAL_DATE,

                   DECODE (ARV.IS_SEEDED,  'N', 'Custom',  'Y', 'Seeded')

                      CUSTOM_SEEDED

              FROM PER_USERS PU,

                   PER_PERSON_NAMES_F PPNF,

                   PER_USER_ROLES PUR,

                   PER_ROLES_DN PRD,

                   PER_ROLES_DN_TL PRDT,

                   ASE_ROLE_VL ARV

             WHERE TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE(+)

         AND PPNF.EFFECTIVE_END_DATE(+)

                   AND PPNF.NAME_TYPE(+) = 'GLOBAL'

                   AND PPNF.PERSON_ID(+) = PU.PERSON_ID

                   AND PU.USERNAME NOT LIKE 'FUSION_APPS_%'

                   AND PUR.ACTIVE_FLAG = 'Y'

                   AND PU.USER_ID = PUR.USER_ID

                   AND PUR.ROLE_ID = PRD.ROLE_ID

                   AND PUR.ROLE_GUID = PRD.ROLE_GUID

                   AND PUR.ROLE_ID = PRDT.ROLE_ID

                   AND PRDT.SOURCE_LANG = 'US'

                   AND PRDT.ROLE_NAME = ARV.ROLE_NAME

                   AND ARV.LANGUAGE = USERENV ('LANG')