Home/Oracle Fusion ERP/Oracle Fusion Queries
  1. Asked: May 18, 2025In:

    Query to Get Oracle Fusion Users and their Attached Roles

    Hassan AbdElrahman
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

    Here's a sample SQL query that you can use to retrieve a list of users and their attached roles in Oracle Fusion: /* For Oraask.com */ 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_stRead more

    Here’s a sample SQL query that you can use to retrieve a list of users and their attached roles in Oracle Fusion:

    /* For Oraask.com */
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 = :p_username
ORDER BY pu.username, prdt.role_name

    You can adjust the query based on your specific requirements. If you need further customization or have any questions, let me know!

    See less
  2. Asked: July 24, 2024In:

    Query to Get Business Units in Oracle Fusion

    Saly
    Saly Explorer

    Find below a query to get business unit information's like (Name, Effective Date, Is it enabled for HR, etc..) /* - Query to get Business Units informations in oracle Fusion - Prepared by: Hassan for Oraask.com */ SELECT haotl.NAME BU_NAME, hao.LOCATION_ID LOCATION_ID, haotl.BUSINESS_GROUP_ID BUSINERead more

    Find below a query to get business unit information’s like (Name, Effective Date, Is it enabled for HR, etc..)

    /* - Query to get Business Units informations in oracle Fusion
   - Prepared by: Hassan for Oraask.com
 */

SELECT
haotl.NAME BU_NAME,
hao.LOCATION_ID LOCATION_ID,
haotl.BUSINESS_GROUP_ID BUSINESS_GROUP_ID,
haotl.EFFECTIVE_START_DATE DATE_FROM,
haotl.EFFECTIVE_END_DATE DATE_TO,
hoi.ORG_INFORMATION1 MANAGER_ID,
hoi.ORG_INFORMATION2 LEGAL_ENTITY_ID,
hoi.ORG_INFORMATION3 PRIMARY_LEDGER_ID,
hoi.ORG_INFORMATION4 DEFAULT_SET_ID,
haotl.NAME SHORT_CODE,
hoi.ORG_INFORMATION6 ENABLED_FOR_HR_FLAG,
hoi.ORG_INFORMATION7 FIN_BUSINESS_UNIT_ID,
hoi.ORG_INFORMATION8 DEFAULT_CURRENCY_CODE,
hoi.ORG_INFORMATION9 PROFIT_CENTER_FLAG
FROM
HR_ALL_ORGANIZATION_UNITS_F hao,
HR_ORGANIZATION_UNITS_F_TL haotl,
HR_ORG_UNIT_CLASSIFICATIONS_F houc,
hr_organization_information_f hoi
WHERE
hao.ORGANIZATION_ID = haotl.ORGANIZATION_ID
AND houc.ORGANIZATION_ID = haotl.ORGANIZATION_ID
AND houc.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
AND hoi.ORGANIZATION_ID = haotl.ORGANIZATION_ID
AND hoi.ORG_INFORMATION_CONTEXT = houc.CLASSIFICATION_CODE
AND TRUNC(SYSDATE) BETWEEN haotl.EFFECTIVE_START_DATE AND haotl.EFFECTIVE_END_DATE
AND haotl.LANGUAGE = USERENV('LANG')
AND haotl.EFFECTIVE_START_DATE = hao.EFFECTIVE_START_DATE
AND haotl.EFFECTIVE_END_DATE = hao.EFFECTIVE_END_DATE
ORDER BY
BU_NAME

    hope this helpful

    See less
  3. Asked: June 3, 2023In: ,

    What is Inventory Item Table in Oracle Fusion

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    The table containing inventory item details in Oracle Fusion is "EGP_SYSTEM_ITEMS_B". It has one composite primary key (INVENTORY_ITEM_ID, ORGANIZATION_ID). and here you can find a sample query to get the most used information about items in Oracle Fusion: Query Inventory Item in Oracle Fusion SELECRead more

    The table containing inventory item details in Oracle Fusion is “EGP_SYSTEM_ITEMS_B“. It has one composite primary key (INVENTORY_ITEM_ID, ORGANIZATION_ID). and here you can find a sample query to get the most used information about items in Oracle Fusion:

    Query Inventory Item in Oracle Fusion

    SELECT ITEM_NUMBER
,INVENTORY_ITEM_ID
,FULL_LEAD_TIME
,PLANNER_CODE
,DECODE (MRP_PLANNING_CODE, 3, 'MRP Planned', 4, 'MPS Planned', 6, 'Not Planned', 7, 'MRP/MPP Planned', 8, 'MPS/MPP Planned', 9, 'MPP Planned', NULL)
MRP_PLANNING_CODE
,INVENTORY_ITEM_STATUS_CODE
,MINIMUM_ORDER_QUANTITY
,MAXIMUM_ORDER_QUANTITY
,FIXED_ORDER_QUANTITY
,FIXED_LOT_MULTIPLIER
,DECODE (INVENTORY_PLANNING_CODE, 6, 'Not Planned', 2, 'Min-Max', 1, 'Reorder Point', 7, 'Vendor Managed') INVENTORY_PLANNING_CODE
,MIN_MINMAX_QUANTITY
,MAX_MINMAX_QUANTITY
,MTL_TRANSACTIONS_ENABLED_FLAG
,CUSTOMER_ORDER_ENABLED_FLAG
,SO_TRANSACTIONS_FLAG
,PRIMARY_UOM_CODE
,UNIT_LENGTH
,UNIT_WIDTH
,UNIT_HEIGHT
,UNIT_WEIGHT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
FROM EGP_SYSTEM_ITEMS_B
WHERE ORGANIZATION_ID = NVL(:INV_ORG_ID,ORGANIZATION_ID);

    In the above query, we have one parameter, “:P_INV_ORG_ID” to get the item information in a specific inventory organization.

    You can refer to the Oracle doc to know all columns in this table.

    Hope this help.

    See less