SQL to get List of apps related objects created by a user including timestamp

By Zafar
CREATE OR REPLACE FORCE VIEW apps.XX_timesheet
AS
     SELECT a."OBJECTTYPE",
            a."USER_DEFINE_NAME",
            a."SHORT_NAME_OR_STATUS",
            a."DESCRIPTION",
            a."APPLICATION_OBJECT_ID",
            a."OWNER",
            (SELECT user_name
               FROM fnd_user fu
              WHERE fu.user_id = a.created_by)
               created_by,
            a."CREATION_DATE"
       FROM (SELECT 'Concurrent Programs' objecttype,
                    fct.user_concurrent_program_name user_define_name,
                    concurrent_program_name short_name_or_status,
                    fct.description description,
                    fcp.application_id application_object_id,
                    fa.application_short_name owner,
                    TO_CHAR (fcp.created_by) created_by,
                    TRUNC (fcp.creation_date) creation_date
               FROM fnd_application fa,
                    fnd_concurrent_programs fcp,
                    fnd_concurrent_programs_tl fct
              WHERE fcp.concurrent_program_id = fct.concurrent_program_id
                    AND fa.application_id = fcp.application_id
             UNION
             SELECT 'Form Function ' objecttype,
                    user_function_name,
                    function_name,
                    fft.description,
                    fff.application_id,
                    fa.application_short_name,
                    TO_CHAR (fff.created_by) created_by,
                    TRUNC (fff.creation_date) creation_date
               FROM fnd_application fa,
                    fnd_form_functions_tl fft,
                    fnd_form_functions fff
              WHERE fff.function_id = fft.function_id
                    AND fa.application_id = fff.application_id
             UNION
             SELECT 'Responsibility' objecttype,
                    responsibility_name,
                    responsibility_key,
                    frt.description,
                    frt.application_id,
                    fa.application_short_name,
                    TO_CHAR (fr.created_by) created_by,
                    TRUNC (fr.creation_date) creation_date
               FROM fnd_application fa,
                    fnd_responsibility fr,
                    fnd_responsibility_tl frt
              WHERE frt.responsibility_id = fr.responsibility_id
                    AND fa.application_id = frt.application_id
             UNION
             SELECT DISTINCT 'MenuName' objecttype,
                             user_menu_name,
                             menu_name,
                             fmt.description,
                             fr.application_id,
                             fa.application_short_name,
                             TO_CHAR (fm.created_by) created_by,
                             TRUNC (fm.creation_date) creation_date
               FROM fnd_application fa,
                    fnd_menus fm,
                    fnd_menus_tl fmt,
                    fnd_responsibility fr
              WHERE     fmt.menu_id = fm.menu_id
                    AND fr.menu_id = fm.menu_id
                    AND fa.application_id = fr.application_id
             UNION
             SELECT DISTINCT 'RequestGroup' objecttype,
                             request_group_name,
                             request_group_name,
                             frg.description,
                             fr.application_id,
                             fa.application_short_name,
                             TO_CHAR (frg.created_by) created_by,
                             TRUNC (frg.creation_date) creation_date
               FROM fnd_application fa,
                    fnd_request_groups frg,
                    fnd_responsibility fr
              WHERE frg.application_id = fr.application_id
                    AND fa.application_id = fr.application_id
             UNION
             SELECT DISTINCT 'RequestSet' objecttype,
                             frst.user_request_set_name,
                             frs.request_set_name,
                             frst.description,
                             frst.application_id,
                             fa.application_short_name,
                             TO_CHAR (frs.created_by) created_by,
                             TRUNC (frs.creation_date) creation_date
               FROM fnd_application fa,
                    fnd_request_sets_tl frst,
                    fnd_request_sets frs
              WHERE     frs.application_id = frst.application_id
                    AND frs.request_set_id = frst.request_set_id
                    AND fa.application_id = frst.application_id
             UNION
             SELECT 'ValueSets' objecttype,
                    flex_value_set_name,
                    flex_value_set_name,
                    frg.description,
                    NULL,
                    NULL,
                    TO_CHAR (frg.created_by) created_by,
                    TRUNC (frg.creation_date) creation_date
               FROM fnd_flex_value_sets frg
              WHERE 1 = 1
             UNION
             SELECT 'FormsPersonalization' objecttype,
                    function_name,
                    form_name,
                    fcr.description,
                    sequence,
                    rule_key,
                    TO_CHAR (fcr.created_by) created_by,
                    TRUNC (fcr.creation_date) creation_date
               FROM fnd_form_custom_rules fcr
             UNION
             SELECT 'DataDefinitions' objecttype,
                    xdt.data_source_name user_define_name,
                    xdd.data_source_code short_name_or_status,
                    xdt.description description,
                    (SELECT application_id
                       FROM fnd_application
                      WHERE application_short_name = xdd.application_short_name)
                       application_object_id,
                    xdd.application_short_name owner,
                    TO_CHAR (xdd.created_by) created_by,
                    TRUNC (xdd.creation_date) creation_date
               FROM xdo_ds_definitions_b xdd, xdo_ds_definitions_tl xdt
              WHERE xdt.data_source_code = xdd.data_source_code
             UNION
             SELECT 'XMLTemplates' objecttype,
                    xdt.template_name user_define_name,
                    xdd.template_code short_name_or_status,
                    xdt.description description,
                    (SELECT application_id
                       FROM fnd_application
                      WHERE application_short_name = xdd.application_short_name)
                       application_object_id,
                    xdd.application_short_name owner,
                    TO_CHAR (xdd.created_by) created_by,
                    TRUNC (xdd.creation_date) creation_date
               FROM xdo_templates_b xdd, xdo_templates_tl xdt
              WHERE xdt.template_code = xdd.template_code
             UNION
             SELECT 'AME Approval Groups' objecttype,
                    xdt.user_approval_group_name user_define_name,
                    xdd.name short_name_or_status,
                    xdt.description description,
                    (SELECT application_id
                       FROM fnd_application
                      WHERE application_short_name = NULL)
                       application_object_id,
                    NULL owner,
                    TO_CHAR (xdd.created_by) created_by,
                    TRUNC (xdd.creation_date) creation_date
               FROM ame_approval_groups xdd, ame_approval_groups_tl xdt
              WHERE xdt.approval_group_id = xdd.approval_group_id
             UNION
             SELECT 'AME Rules' objecttype,
                    xdt.description user_define_name,
                    xdd.rule_key short_name_or_status,
                    xdt.description description,
                    (SELECT application_id
                       FROM fnd_application
                      WHERE application_short_name = NULL)
                       application_object_id,
                    NULL owner,
                    TO_CHAR (xdd.created_by) created_by,
                    TRUNC (xdd.creation_date) creation_date
               FROM ame_rules xdd, ame_rules_tl xdt
              WHERE xdt.rule_id = xdd.rule_id
             UNION
             SELECT 'Workflow Messages' objecttype,
                    display_name user_define_name,
                    name short_name_or_status,
                    description description,
                    NULL application_object_id,
                    SUBSTR (name, 1, 3) owner,
                    NULL created_by,
                    (SELECT TRUNC (MIN (begin_date))
                       FROM wf_items wi
                      WHERE wi.item_type = tmp.TYPE)
                       creation_date
               FROM wf_messages_vl tmp
             UNION
             SELECT 'Workflow Attributes' objecttype,
                    display_name user_define_name,
                    name short_name_or_status,
                    description description,
                    NULL application_object_id,
                    SUBSTR (name, 1, 3) owner,
                    NULL created_by,
                    (SELECT TRUNC (MIN (begin_date))
                       FROM wf_items wi
                      WHERE wi.item_type = tmp.TYPE)
                       creation_date
               FROM wf_item_attributes_vl tmp
             UNION
             SELECT 'Workflows' objecttype,
                    display_name user_define_name,
                    name short_name_or_status,
                    description description,
                    NULL application_object_id,
                    SUBSTR (name, 1, 3) owner,
                    NULL created_by,
                    (SELECT TRUNC (MIN (begin_date))
                       FROM wf_items wi
                      WHERE wi.item_type = name)
                       creation_date
               FROM wf_item_types_vl
             UNION
             SELECT object_type objecttype,
                    object_name user_define_name,
                    status short_name_or_status,
                    NVL (subobject_name, object_name) description,
                    (SELECT application_id
                       FROM fnd_application
                      WHERE application_short_name = NULL)
                       application_object_id,
                    owner,
                    NULL created_by,
                    TRUNC (created) creation_date
               FROM dba_objects
              WHERE SUBSTR (UPPER (object_name), 1, 2) IN ('XX')
                    AND TO_CHAR (NVL (last_ddl_time, created), 'RRRR') =
                           TO_CHAR (SYSDATE, 'RRRR')
             UNION
             SELECT 'Web Integrator(ADI)' objecttype,
                    user_name user_function_name,
                    integrator_code function_name,
                    user_name description,
                    bni.application_id,
                    fa.application_short_name,
                    TO_CHAR (bni.created_by) created_by,
                    TRUNC (bni.creation_date) creation_date
               FROM bne_integrators_vl bni, fnd_application fa
              WHERE fa.application_id = bni.application_id) a
   ORDER BY a.owner, a.objecttype;


select * from XX_timesheet;

Comments

Post a Comment

Popular posts from this blog

### Understanding fnd_request.submit_request and fnd_request.wait_for_request in Oracle E-Business Suite

By Zafar
In the realm of Oracle E-Business Suite (EBS), managing concurrent requests efficiently is crucial for maintaining smooth operations. Two key functions involved in this process are `fnd_request.submit_request` and `fnd_request.wait_for_request`. Let’s delve into these functions and understand their detailed usage. #### fnd_request.submit_request: This function is utilized to submit concurrent requests programmatically within Oracle EBS. It's particularly useful for automation purposes, allowing users to initiate various concurrent processes without manual intervention. **Usage:** ```sql l_request_id := fnd_request.submit_request(     application => 'APPLICATION_NAME',     program => 'PROGRAM_NAME',     description => 'REQUEST_DESCRIPTION',     start_time => NULL,     sub_request => FALSE,     argument1 => 'ARGUMENT_1_VALUE',     argument2 => 'ARGUMENT_2_VALUE',     ...     argu...
Read more

Understanding Oracle R12 Encumbrance Accounting: Functionality, Standard Profiles, and Reports

By Zafar
Introduction: Oracle R12 Encumbrance Accounting is a powerful tool within the Oracle E-Business Suite that helps organizations effectively manage and monitor their financial commitments. In this detailed blog, we'll explore the concept of encumbrance accounting, its functionality within Oracle R12, standard profiles used in encumbrance, standard reports available, and the benefits it offers to businesses. What is Encumbrance Accounting? Encumbrance accounting is a method used by organizations to track and manage financial commitments or obligations before they are actually incurred. It allows businesses to reserve funds for specific purposes, such as purchase orders, contracts, or projects, ensuring that budgetary controls are maintained throughout the procurement process. Functionality of Oracle R12 Encumbrance Accounting: 1. Budgetary Control: Oracle R12 Encumbrance Accounting enables organizations to establish budgetary controls by setting up budgets for different cost centers, ...
Read more

Understanding the "Run Alone" Flag in Oracle E-Business Suite Concurrent Programs

By Zafar
Image
In Oracle E-Business Suite (EBS), managing the performance and scheduling of concurrent programs is crucial to ensuring efficient processing and optimal resource usage. One of the features available to help manage this is the "Run Alone" flag. This setting allows administrators to control how a specific concurrent program is executed, especially when running alongside other programs. In this blog, we’ll explore what the "Run Alone" flag does, why it’s important, and best practices for its use. What is the "Run Alone" Flag? The "Run Alone" flag is a setting available in the Concurrent Program definition in Oracle EBS. When this flag is enabled, it restricts the concurrent program from running alongside other concurrent programs on the same concurrent manager. In essence, it tells Oracle EBS that this program must run exclusively, with no other concurrent requests processed until it completes. How to Set the "Run Alone" Flag You can enabl...
Read more