Query to find Responsibility and Request Group from Concurrent Program

-

 In this blog, we’ll break down a query that helps you trace a specific Concurrent Program through its Application, Request Group, and associated Responsibilities in Oracle EBS R12

Objective

The goal of this query is to find:

  • The User-Friendly Name of a Concurrent Program

  • The Technical Concurrent Program Name

  • The Application to which it belongs

  • The Request Group under which it is registered

  • The Responsibility that grants access to it

This is especially useful for ensuring correct program registration or for debugging access issues.


SQL Query:

    SELECT DISTINCT 

    fcpl.user_concurrent_program_name,

    fcp.concurrent_program_name,

    fapp.application_name,

    frg.request_group_name,

    fnrtl.responsibility_name

FROM 

    apps.fnd_request_groups         frg,

    apps.fnd_application_tl         fapp,

    apps.fnd_request_group_units    frgu,

    apps.fnd_concurrent_programs    fcp,

    apps.fnd_concurrent_programs_tl fcpl,

    apps.fnd_responsibility         fnr,

    apps.fnd_responsibility_tl      fnrtl

WHERE     

    frg.application_id = fapp.application_id

    AND frg.application_id = frgu.application_id

    AND frg.request_group_id = frgu.request_group_id

    AND frg.request_group_id = fnr.request_group_id

    AND fnr.responsibility_id = fnrtl.responsibility_id

    AND frgu.request_unit_id = fcp.concurrent_program_id

    AND frgu.unit_application_id = fcp.application_id

    AND fcp.concurrent_program_id = fcpl.concurrent_program_id

    AND fcp.concurrent_program_name = 'GIVE YOUR CONCURRENT NAME'

    AND fnrtl.LANGUAGE = 'US'

    AND fapp.LANGUAGE = 'US'

Tables Involved


TablePurpose
fnd_concurrent_programsStores technical details about concurrent programs
fnd_concurrent_programs_tlStores translated (user-friendly) names of concurrent programs
fnd_application_tlHolds application names
fnd_request_groupsDefines request groups that group multiple concurrent programs
fnd_request_group_unitsMaps concurrent programs to request groups
fnd_responsibilityStores responsibility metadata
fnd_responsibility_tlProvides language-specific responsibility names


Use Cases

This query is especially helpful in these scenarios:

  • Access Verification: Confirm if a concurrent program is available under a specific responsibility.

  • Deployment QA: Validate that newly migrated programs are registered in the correct request group and visible to users.

  • Audit & Documentation: Generate reports showing where each custom program (XX%) is available across the EBS ecosystem.

Conclusion

This SQL query is a practical tool in every Oracle Apps developer’s toolkit. It provides a complete picture of how a concurrent program is tied to the EBS security model — from the application to the final responsibility level. Knowing this can save hours during issue resolution and deployment validation.

Location: Hyderabad, Telangana, India

Comments

Post a Comment

Popular posts from this blog

How to Extract User Role Details with SQL in Oracle Fusion

-
When working with Oracle HCM / Fusion Applications , administrators and developers often need to fetch details about users, their assigned roles, and their effective dates. This helps in audits, access reviews, and troubleshooting authorization issues. In this post, we’ll walk through a SQL query that retrieves user-role information from Oracle HCM tables. SQL Query:                          SELECT     pus.user_id             "User ID",     pus.username            "Username",     ppnaf.full_name         "Full Name",     prdn.role_common_name   "Role Common Name",     prdtl.role_name         "Role Name",     to_char(purs.start_date, 'DD-MON-YYYY') "Role Start Date",     to_char(purs.end_date, 'DD-MON-YYYY')   "...
Read more »»»»

3 Way PO Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation

-
3 Way Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation In any procurement system, ensuring the accuracy of supplier payments is critical. One of the most robust methods for validating supplier invoices is the 3-way matching process. This blog breaks down the 3-way matching concept in Oracle E-Business Suite (EBS) or Oracle Fusion, explains its benefits, and presents a detailed SQL query that helps extract and reconcile PO, Receipt, and Invoice data. What is 3-Way Matching ? 3-Way Matching is a process that compares the following three documents before payment is released: Purchase Order (PO) – What you ordered Receipt – What you received Invoice – What the supplier billed This comparison ensures that you only pay for goods that were ordered and received , avoiding overpayments, duplicates, and fraud. Matching Criteria   Element              PO      Receipt  ...
Read more »»»»