Exchange Rate Handling in Oracle Cloud

 Please find below a summary regarding exchange rate handling in Oracle Cloud:

• The exchange rate values in both RA_CUSTOMER_TRX_ALL and GL_DAILY_RATES are identical.

• However, the Fusion UI typically displays the exchange rate rounded to 6 decimal places. To ensure consistency, it is recommended to use ROUND(exchange_rate, 6) in custom reports or queries.

• The AutoInvoice interface (RA_INTERFACE_LINES_ALL) does not apply rounding by default, which can result in high-precision values being inserted—particularly when CONVERSION_TYPE is set to 'User' or a custom type.

Recommendations to Avoid High-Precision Exchange Rates:

• Round the exchange rate in FBDI templates, APIs, or integration sources before loading data.

• Ensure values in GL_DAILY_RATES are stored with a consistent precision (ideally up to 6 decimal places).

• Use ROUND(exchange_rate, 6) in all custom queries and reports to match how values are displayed in the Fusion UI.

Oracle Fusion + Sales Order Audit Report Query + Sales Order Fulfillment Lines EFF Query

 SELECT * FROM 

(SELECT --dla.line_id,

       --dha.header_id,

   dha.order_number "Order Number",

       hp.party_name "customer",

       hp.country "country",

   (NVL(dla.ordered_qty,0) * NVL(dla.unit_selling_price,0)) amount,

  (SELECT attribute_char3

   FROM doo_headers_eff_b dheb

   WHERE dheb.header_id = dha.header_id

     AND CONTEXT_CODE='Subscription')  region,

       Revenue_Management.attribute_char8 "workday contract name",

       Subscription.attribute_char1 "billing_offset",

       ACCOUNTS_RECEIVABLES.attribute_char1 "invoice group by number",

       ACCOUNTS_RECEIVABLES.attribute_char2 "invoice group by description",

       ACCOUNTS_RECEIVABLES.attribute_char3 "management group",

        SF_SUBSCRIPTION.ATTRIBUTE_CHAR10 "align billing from",

            SF_SUBSCRIPTION.attribute_char11 "custom billing",

                 Conversion.ATTRIBUTE_CHAR4 "Misc info",

                 Conversion.attribute_char2 "PS billed amount",

                 Conversion.attribute_char3 "PS unbilled amount",

                 Conversion.attribute_char5 "supplier purchase order",

                 TO_CHAR(SNOW.attribute_timestamp1,'MM/DD/YY HH:MI') "cloud provision date",

                 TO_CHAR(SNOW.attribute_timestamp2,'MM/DD/YY HH:MI') "on-perm provision date",

                 IB.attribute_char2 "asset line identifer",

                 IB.attribute_char3 "life cycle pid ",

                 SFDC.attribute_char1 "PO requied",

 SFDC.attribute_char2 "PO Number",

 SFDC.attribute_char3 "PO Line Number",

                 SFDC.attribute_char4 "invoice trigger event",

                 SFDC.attribute_char5 "site id",

                 SFDC.attribute_char6 "bundle id",

                 SFDC.attribute_char7 "advanced billing flag",

                 SFDC.attribute_char10 "used inventory flag",

                 SFDC.attribute_char11 "GSS validation flag",

                 SFDC.attribute_char16 "suppress fulfillment",

                 Procurement.attribute_char1 "cabinet position",

                 Procurement.attribute_char2 "PO vendor cost/transfer price",

                 Procurement.attribute_char3 "related manual order",

                 Tax.attribute_char2 "0% tax  rate flag",

                 BRAZIL.attribute_char1 "CFOP-line order",

                 CSP.attribute_char1 "CSP flag",

                 CSP.attribute_char3 "private order flag",

     esib.item_number,

 esib.description item_description,

 bill_to_party.party_name bill_to_customer,

 ship_to_party.party_name ship_to_customer,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR5 sf_subscription_number,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR2  "SF Line Identifier (Model)",

 revenue_management.attribute_char6 "Rev Contract Grouping" ,

 (SELECT header_curr_duration_ext_amt

 FROM doo_order_pricing_details_v dopdv

 WHERE dopdv.fulfill_line_id = dfla.fulfill_line_id

                 AND dopdv.PRICE_ELEMENT_CODE='QP_NET_PRICE'

                 AND dopdv.ROLLUP_FLAG ='Y' ) Total_Amount_for_Duration

FROM doo_headers_all dha,

     doo_lines_all dla,

     doo_fulfill_lines_all dfla,

     doo_fulfill_lines_eff_b ACCOUNTS_RECEIVABLES,

     doo_fulfill_lines_eff_b Revenue_Management,

     doo_fulfill_lines_eff_b Subscription,

     doo_fulfill_lines_eff_b CONVERSION,

     doo_fulfill_lines_eff_b SFDC,

 doo_fulfill_lines_eff_b SNOW,

     doo_fulfill_lines_eff_b IB,

     doo_fulfill_lines_eff_b Procurement,

     doo_fulfill_lines_eff_b Tax,

     doo_fulfill_lines_eff_b BRAZIL,

     doo_fulfill_lines_eff_b CSP,

 doo_fulfill_lines_eff_b SF_SUBSCRIPTION,

     hz_parties hp,

 egp_system_items esib,

     doo_order_addresses bill_to,

 doo_order_addresses ship_to,

 hz_cust_accounts bill_to_cust,

 hz_parties bill_to_party,

 hz_parties ship_to_party

WHERE 1=1

  ---AND dha.order_number='00026264'

  AND dha.header_id=dla.header_id

  AND dla.line_id= dfla.line_id

  AND dfla.fulfill_line_id = accounts_receivables.fulfill_line_id (+)

  AND accounts_receivables.context_code (+)='ACCOUNTS RECEIVABLES'

  AND dfla.fulfill_line_id = Revenue_Management.fulfill_line_id (+)

  AND Revenue_Management.context_code (+)='Revenue_Management_Information_Line'

  AND dfla.fulfill_line_id = Subscription.fulfill_line_id (+)

  AND Subscription.context_code (+)='Subscription'

  AND dfla.fulfill_line_id = Conversion.fulfill_line_id (+)

  AND Conversion.context_code (+)='Conversion'

  AND dfla.fulfill_line_id = SFDC.fulfill_line_id (+)

  AND SFDC.context_code (+)='SFDC'

  AND dfla.fulfill_line_id = SNOW.fulfill_line_id (+)

  AND SNOW.context_code (+)='SNOW'

  AND dfla.fulfill_line_id = IB.fulfill_line_id (+)

  AND IB.context_code (+)='IB - Asset Line Identifier Model'

  AND dfla.fulfill_line_id = Procurement.fulfill_line_id (+)

  AND Procurement.context_code (+)='Procurement'

  AND dfla.fulfill_line_id = Tax.fulfill_line_id (+)

  AND Tax.context_code (+)='Tax'

  AND dfla.fulfill_line_id = BRAZIL.fulfill_line_id (+)

  AND BRAZIL.context_code (+)='BRAZIL-Localization'

  AND dfla.fulfill_line_id = CSP.fulfill_line_id (+)

  AND CSP.context_code (+)='CSP Details'

  AND dfla.fulfill_line_id = SF_SUBSCRIPTION.fulfill_line_id (+)

  AND SF_SUBSCRIPTION.context_code (+)='SF Subscription - SF Line Identifier Model'

  AND dha.sold_to_party_id = hp.party_id 

  AND dla.inventory_organization_id =esib.organization_id

  and dla.inventory_item_id = esib.inventory_item_id

  AND dha.header_id = bill_to.header_id (+)

  AND bill_to.address_use_type (+)= 'BILL_TO'

  AND bill_to.cust_acct_id = bill_to_cust.cust_account_id (+)

  AND bill_to_cust.party_id = bill_to_party.party_id

  AND dha.header_id = ship_to.header_id (+)

  AND ship_to.address_use_type (+) = 'SHIP_TO'

  AND ship_to.party_id = ship_to_party.party_id (+)

 ) 

  WHERE region = NVL(:p_region,region);

Regards,

Amar Alam

Oracle Fusion + Query to fetch Inventory On-hand Available to Reserve quantity

 select 

  NVL(TR_QTY, 0) - (

    NVL(TR_QTY_B, 0) + NVL(TR_QTY_C, 0)

  ) res_qty 

from 

  (

    SELECT 

      (

        SELECT 

          SUM(transaction_quantity) 

        FROM 

          INV_ONHAND_QUANTITIES_DETAIL 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348 

          AND subinventory_code IN (

            SELECT 

              SECONDARY_INVENTORY_NAME 

            FROM 

              INV_SECONDARY_INVENTORIES 

            WHERE 

              organization_id = 300000002138348 

              AND reservable_type = '2'

          )

      ) AS TR_QTY_C, 

      (

        SELECT 

          SUM(RESERVATION_QUANTITY) 

        FROM 

          INV_RESERVATIONS 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348

      ) AS TR_QTY_B, 

      (

        SELECT 

          SUM(transaction_quantity) 

        FROM 

          INV_ONHAND_QUANTITIES_DETAIL 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348

      ) AS TR_QTY 

    from 

      egp_system_items_b MB1 

    where 

      1 = 1 

      AND (

        mb1.organization_id = 300000002138348

      ) 

      AND (

        mb1.inventory_item_id = 100000000615408

      )

  );

Oracle Cloud + Query to get AR Receipts Recommendations Details

 SELECT 

    acra.receipt_number            receipt_number

   ,rcta.trx_number                recommendation

   ,acr.match_score_value          reference_score

   ,DECODE(acr.match_reason_code ,'AR_AA_BELOW_TRX_TSLD','Below transaction threshold','AR_AM_INV_THRESHOLD','Above transaction threshold') reason

   ,apsa.amount_due_remaining      balance

   ,DECODE(tta.type, 'INV', 'Invoice', 'CM', 'Credit Memo') document_type

   ,hp.party_name                  customer

   ,acr.recommendation_status      accepted

   ,acr.amount_applied

   ,SUBSTR(apsa.trx_number,'-0',7)||' '||acr.amount_applied Reference_number

FROM

    ar_cash_receipts_all            acra

   ,ar_receipt_methods              arm

   ,ar_cash_remit_refs_all          acrfa

   ,ar_cash_recos_all               acr

   ,ar_cash_reco_lines_all          acrla

   ,ra_customer_trx_all             rcta

   ,ar_payment_schedules_all        apsa

   ,ra_cust_trx_types_all           tta

   ,hz_cust_accounts                hca

   ,hz_parties                      hp

   ,hz_party_sites                  hps

   ,hz_cust_site_uses_all           hcsu

   ,hz_cust_acct_sites_all          hcsa

WHERE 1 = 1

    AND acra.receipt_number          = '111300952062031'

AND acra.receipt_method_id       = arm.receipt_method_id   

AND acra.cash_receipt_id         = acrfa.cash_receipt_id

AND acrfa.remit_reference_id     = acr.remit_reference_id

AND acr.recommendation_id        = acrla.recommendation_id

AND acr.resolved_matching_number = rcta.trx_number

AND rcta.customer_trx_id         = apsa.customer_trx_id

AND rcta.cust_trx_type_seq_id    = tta.cust_trx_type_seq_id

AND rcta.bill_to_customer_id     = hca.cust_account_id

AND acr.customer_id              = hca.cust_account_id

AND hca.party_id                 = hp.party_id

AND hp.party_id                  = hps.party_id

AND acr.customer_site_use_id     = hcsu.site_use_id

AND hcsu.cust_acct_site_id       = hcsa.cust_acct_site_id

AND hcsa.cust_account_id         = hca.cust_account_id

AND hcsa.party_site_id           = hps.party_site_id

Thanks

Amar Alam

Oracle Cloud + Query to get The Data Access for an User

SELECT USERNAME,PERSON_NUMBER,person_name,ROLE_NAME,ROLE_CODE,SECURITY_CONTEXT ,SECURITY_CONTEXT_VALUE
FROM(
SELECT
 PU.USERNAME ,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 ROLE.ROLE_NAME ROLE_CODE,
 'DATA ACCESS SET' security_context,
 GL.NAME security_context_value
 FROM
 fusion.fun_user_role_data_asgnmnts role,
 fusion.gl_access_sets gl,
 fusion.per_users pu,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 GL.ACCESS_SET_ID = ROLE.ACCESS_SET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
 AND ROLE.ROLE_NAME=R.CODE
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
 AND papf.person_id=ppnf.person_id
 AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate
 AND nvl(r.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
 PU.USERNAME,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 ROLE.ROLE_NAME ROLE_CODE,
 'BUSINESS UNIT' security_context,
 BU.BU_NAME security_context_value
FROM
 fusion.fun_all_business_units_v bu,
 fusion.fun_user_role_data_asgnmnts role,
 fusion.per_users pu,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 ROLE.ORG_ID = BU.BU_ID
 AND PU.USER_GUID = ROLE.USER_GUID
 AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
 pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'LEDGERS' security_context,
 led.name security_context_value
FROM
 fusion.gl_ledgers led,
 fusion.fun_user_role_data_asgnmnts role,
 fusion.per_users pu,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 role.ledger_id = led.ledger_id
 and pu.user_guid = role.user_guid
 and role.role_name=r.code
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
 pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'ASSET BOOK' security_context,
 book.book_type_name security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FA_BOOK_CONTROLS BOOK,
 FUSION.PER_USERS PU,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 BOOK.BOOK_CONTROL_ID = ROLE.BOOK_ID
 AND PU.USER_GUID = ROLE.USER_GUID
 AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'INTERCOMPANY ORGANIZATION' security_context,
 INTERCO.INTERCO_ORG_NAME security_context_value
FROM
 fusion.fun_user_role_data_asgnmnts role,
 fusion.fun_interco_organizations interco,
 fusion.per_users pu,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
 ase_role_b r,
 ase_role_tl rtl
WHERE
 INTERCO.INTERCO_ORG_ID = ROLE.INTERCO_ORG_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'COST ORGANIZATION' security_context,
 COST.COST_ORG_NAME security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.CST_COST_ORGS_V COST,
 FUSION.PER_USERS PU,
  per_all_people_f papf,
  per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 COST.COST_ORG_ID = ROLE.CST_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'MANUFACTURING PLANT' security_context,
 MFG.DEF_SUPPLY_SUBINV security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.RCS_MFG_PARAMETERS MFG,
 FUSION.PER_USERS PU,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 MFG.ORGANIZATION_ID = ROLE.MFG_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'CONTROL BUDGET' security_context,
 BUDGET.NAME security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.XCC_CONTROL_BUDGETS BUDGET,
 FUSION.PER_USERS PU,
  per_all_people_f papf,
  per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 BUDGET.CONTROL_BUDGET_ID = ROLE.CONTROL_BUDGET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'REFERENCE DATA SET' security_context,
 ST.SET_NAME security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FND_SETID_SETS_VL ST,
 FUSION.PER_USERS PU,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 ST.SET_ID = ROLE.SET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'INVENTORY ORGANIZATION' security_context,
 INV.ORGANIZATION_CODE security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.INV_ORG_PARAMETERS INV,
 FUSION.PER_USERS PU,
 per_all_people_f papf,
 per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 INV.ORGANIZATION_ID = ROLE.INV_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y'
UNION
SELECT
pu.username,
 papf.person_number,
 ppnf.display_name person_name,
 rtl.role_name,
 role.role_name role_code,
 'PROJECT ORGANIZATION CLASSIFICATION' security_context,
 HR.CLASSIFICATION_CODE security_context_value
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.HR_ORG_UNIT_CLASSIFICATIONS_F HR,
 FUSION.PER_USERS PU,
  per_all_people_f papf,
  per_person_names_f_v ppnf,
  ase_role_b r,
 ase_role_tl rtl
WHERE
 HR.ORG_UNIT_CLASSIFICATION_ID = ROLE.ORG_ID
 AND PU.USER_GUID = ROLE.USER_GUID
  AND ROLE.ROLE_NAME=R.CODE
 AND r.role_id = rtl.role_id
 AND rtl.language = 'US'
 AND nvl(r.effective_start_date,sysdate) <= sysdate and
     nvl(r.effective_end_date,sysdate) >= sysdate
 AND nvl(pu.suspended,'N') = 'N'
 AND pu.person_id=papf.person_id
 AND nvl(papf.effective_start_date,sysdate) <= sysdate
 AND nvl(papf.effective_end_date,sysdate) >= sysdate
  AND papf.person_id=ppnf.person_id
  AND nvl(ppnf.effective_start_date,sysdate) <= sysdate
 AND nvl(ppnf.effective_end_date,sysdate) >= sysdate
 AND pu.active_flag = 'Y' 
 )
 WHERE username='aalam'
 ORDER BY USERNAME,ROLE_NAME,SECURITY_CONTEXT

Thanks
Amar Alam

Query to get user manager and department details in Oracle Cloud

SELECT DISTINCT
    pu.username,
    pea.email_address employee_email,
    ppnf.first_name
    || ' '
    || ppnf.last_name employee_name,
    hauft.name department,
    (
        SELECT
            ppnf1.full_name
        FROM
            per_assignment_supervisors_f pasf,
            per_person_names_f ppnf1
        WHERE
            1 = 1
            AND   pasf.manager_type = 'LINE_MANAGER'
            AND   pasf.manager_id = ppnf1.person_id
            AND   SYSDATE BETWEEN ppnf1.effective_start_date AND ppnf1.effective_end_date
            AND   SYSDATE BETWEEN pasf.effective_start_date AND pasf.effective_end_date
            AND   ppnf1.name_type = 'GLOBAL'
            AND   pasf.person_id = pu.person_id
            AND   ROWNUM = 1
    ) supervisorname,
    pea1.email_address supervisor_email
FROM
    ase_user_b u,
    per_users pu,
    per_all_people_f papf,
    per_person_names_f ppnf,
    per_email_addresses pea,
    hr_org_unit_classifications_f houcf,
    hr_all_organization_units_f haouf,
    hr_organization_units_f_tl hauft,
    per_all_assignments_m paam,
    per_assignment_supervisors_f pasf,
    per_email_addresses pea1
WHERE
    u.user_guid = pu.user_guid
    AND   nvl(u.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(u.effective_end_date,SYSDATE) >= SYSDATE
    AND   nvl(pu.active_flag,'Y') = 'Y'
    AND   nvl(pu.suspended,'N') = 'N'
    AND   pu.person_id = papf.person_id
    AND   nvl(papf.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(papf.effective_end_date,SYSDATE) >= SYSDATE
    AND   papf.person_id = ppnf.person_id
    AND   nvl(ppnf.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(ppnf.effective_end_date,SYSDATE) >= SYSDATE
    AND   papf.person_id = pea.person_id
    AND   papf.primary_email_id = pea.email_address_id
    AND   papf.person_id = paam.person_id
    AND   haouf.organization_id = houcf.organization_id
    AND   haouf.organization_id = hauft.organization_id
    AND   nvl(haouf.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(haouf.effective_end_date,SYSDATE) >= SYSDATE
    AND   hauft.language = 'US'
    AND   hauft.effective_start_date = haouf.effective_start_date
    AND   hauft.effective_end_date = haouf.effective_end_date
    AND   houcf.classification_code = 'DEPARTMENT'
    AND   nvl(hauft.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(hauft.effective_end_date,SYSDATE) >= SYSDATE
    AND   hauft.organization_id = paam.organization_id
    AND   paam.primary_assignment_flag = 'Y'
    AND   paam.assignment_type IN (
        'E',
        'C'
    )
    AND   paam.effective_latest_change = 'Y'
    AND   nvl(paam.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(paam.effective_end_date,SYSDATE) >= SYSDATE
    AND   papf.person_id = pasf.person_id
    AND   pasf.manager_type = 'LINE_MANAGER'
    AND   nvl(pasf.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(pasf.effective_end_date,SYSDATE) >= SYSDATE
    /*AND   pasf.manager_id=ppnf1.person_id
    AND   ppnf1.name_type = 'GLOBAL'
    AND   nvl(ppnf1.effective_start_date,SYSDATE) <= SYSDATE
    AND   nvl(ppnf1.effective_end_date,SYSDATE) >= SYSDATE*/
    AND   pasf.manager_id = pea1.person_id
   --AND upper(pu.username) like 'AALAM%'

Thanks
Amar Alam

Oracle Cloud + Query to Find List of Users for The Specific Role

SELECT pu.username,
                 papf.person_number,
                 r.code,
                 r.role_type_code,
                 rtl.role_name,
                 rtl.description
FROM  ase_user_b u,
              per_users pu,
              per_all_people_f papf,
              ase_user_role_mbr ur,
              ase_role_b r,
              ase_role_tl rtl
WHERE 1=1
AND u.user_guid = pu.user_guid AND
NVL(u.effective_start_date,sysdate) <= sysdate AND
NVL(u.effective_end_date,sysdate) >= sysdate AND
NVL(pu.active_flag,'Y') = 'Y' AND
nvl(pu.suspended,'N') = 'N' AND
pu.person_id=papf.person_id AND
nvl(papf.effective_start_date,sysdate) <= sysdate and
nvl(papf.effective_end_date,sysdate) >= sysdate and
r.role_id = rtl.role_id AND
r.role_id = ur.role_id AND
u.user_id = ur.user_id AND
rtl.language = 'US' and
nvl(r.effective_start_date,sysdate) <= sysdate and
nvl(r.effective_end_date,sysdate) >= sysdate and
nvl(ur.effective_start_date,sysdate) <= sysdate and
nvl(ur.effective_end_date,sysdate) >= sysdate and
rtl.role_name = 'ARRIS Employee'

Your's
Amar Alam