Query to find the Project and Task details.

SELECT ppa.project_id                                    ,
  ppa.name project_name                                   ,
  ppa.segment1 project_number                             ,
  ppa.project_type                                        ,
  ppa.carrying_out_organization_id                        ,
  hou.name   Organization              ,
  ppa.project_status_code                                 ,
  ppa.description                                         ,
  ppa.start_date                                          ,
  ppa.completion_date                                     ,
  ppa.closed_date                                         ,
  ppa.summary_flag                                        ,
  ppa.enabled_flag                                        ,
  ppa.org_id                                              ,
  hou1.name Operating_Unit                                      ,
  ppa.project_currency_code                               ,
  ppa.allow_cross_charge_flag                             ,
  ppa.project_rate_type                                   ,
  ppa.projfunc_currency_code                              ,
  ppa.long_name                                           ,
  pt.task_number                                          ,
  pt.task_id                                              ,
  pt.task_name                                            ,
  pt.top_task_id                                          ,
  pt.wbs_level                                            ,
  pt.chargeable_flag                                      ,
  pt.billable_flag                                        ,
  pt.limit_to_txn_controls_flag                           ,
  pt.start_date Task_start_date                           ,
  pt.completion_date task_completion_date                 ,
  pt.allow_cross_charge_flag tasks_allow_cross_charge_flag,
  pt.long_task_name,
  hou.attribute6,
  ppa.pm_project_reference
   FROM apps.pa_projects_all ppa    ,
  apps.pa_tasks pt                  ,
  apps.hr_all_organization_units hou,
  apps.hr_all_organization_units hou1
  WHERE 1               =1
AND hou.organization_id =ppa.carrying_out_organization_id
AND hou1.organization_id=ppa.org_id
AND ppa.project_id      =pt.project_id
AND ppa.segment1        =  &project_number
ORDER BY task_number

Query to find Active user Responsibilities.

SELECT
       DISTINCT fu.user_name USER_NAME,
       fu.description  Description,
       pf.Employee_Number,
       fr.responsibility_name, furg.start_date,
       to_char (furg.end_date, 'DD-MON-YYYY HH:MI:SS'),
       fu.email_address,
      fa.application_name,
      hou.NAME
FROM
      APPS.fnd_profile_option_values fpov,
       APPS.fnd_profile_options fpo,
       APPS.hr_operating_units hou,
       APPS.fnd_responsibility_tl fr,
       APPS.fnd_application_tl fa,
       APPS.fnd_user_resp_groups_direct furg,
       APPS.fnd_user fu,
       APPS.per_all_people_f pf
 WHERE
   fpov.profile_option_id = fpo.profile_option_id
   AND fpov.application_id = fpo.application_id
   AND fpo.profile_option_name = 'ORG_ID'
   AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
   AND fpov.level_value = fr.responsibility_id
   AND fa.application_id = fr.application_id
   AND fpov.level_value = fr.responsibility_id
   AND furg.user_id = fu.user_id
   and fu.employee_id=pf.person_id
   AND furg.responsibility_id = fr.responsibility_id
  and ((furg.end_date is null) or (furg.end_date >=sysdate))
  and( fu.end_date >= sysdate or fu.end_date is null)
    order by fu.user_name asc

Oracle AIM Document Templates

Oracle AIM Document Templates

1. Business Process Architecture (BP)
BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure

2. Business Requirements Definition (RD)
RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements

3. Business Requirements Mapping
BR.010 Analyze High-Level Gaps
BR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles

4. Application and Technical Architecture (TA)
TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Architecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures

5. Module Design and Build (MD)
MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines

6. Data Conversion (CV)
CV.010 Define data conversion requirements and strategy
CV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data

7. Documentation (DO)
DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide

8. Business System Testing (TE)
TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test

9. PERFORMACE TESTING(PT)
PT.010 - Define Performance Testing Strategy
PT.020 - Identify Performance Test Scenarios
PT.030 - Identify Performance Test Transaction
PT.040 - Create Performance Test Scripts
PT.050 - Design Performance Test Transaction Programs
PT.060 - Design Performance Test Data
PT.070 - Design Test Database Load Programs
PT.080 - Create Performance Test TransactionPrograms
PT.090 - Create Test Database Load Programs
PT.100 - Construct Performance Test Database
PT.110 - Prepare Performance Test Environment
PT.120 - Execute Performance Test

10. Adoption and Learning (AP)
AP.010 - Define Executive Project Strategy
AP.020 - Conduct Initial Project Team Orientation
AP.030 - Develop Project Team Learning Plan
AP.040 - Prepare Project Team Learning Environment
AP.050 - Conduct Project Team Learning Events
AP.060 - Develop Business Unit Managers’Readiness Plan
AP.070 - Develop Project Readiness Roadmap
AP.080 - Develop and Execute CommunicationCampaign
AP.090 - Develop Managers’ Readiness Plan
AP.100 - Identify Business Process Impact onOrganization
AP.110 - Align Human Performance SupportSystems
AP.120 - Align Information Technology Groups
AP.130 - Conduct User Learning Needs Analysis
AP.140 - Develop User Learning Plan
AP.150 - Develop User Learningware
AP.160 - Prepare User Learning Environment
AP.170 - Conduct User Learning Events
AP.180 - Conduct Effectiveness Assessment

11. Production Migration (PM)
PM.010 - Define Transition Strategy
PM.020 - Design Production Support Infrastructure
PM.030 - Develop Transition and Contingency Plan
PM.040 - Prepare Production Environment
PM.050 - Set Up Applications
PM.060 - Implement Production Support Infrastructure
PM.070 - Verify Production Readiness
PM.080 - Begin Production
PM.090 - Measure System Performance
PM.100 - Maintain System
PM.110 - Refine Production System
PM.120 - Decommission Former Systems
PM.130 - Propose Future Business Direction
PM.140 - Propose Future Technical Direction

Query To Find the Felxfield Segment details

SELECT   fa.application_name, fifst.id_flex_structure_name,
         segment_num "Number", segment_name "Name",
         form_left_prompt "Window Prompt",
         fifs.application_column_name "Column",
         ffvs.flex_value_set_name "Value Set Name",
         ffvs.description "Value Set Description",
         default_type "Default Type", DEFAULT_VALUE "Default Value",
         DECODE (fifs.required_flag,
                 'Y', 'Checked',
                 'N', 'Unchacked'
                ) "Required",
         DECODE (fifs.security_enabled_flag,
                 'Y', 'Checked',
                 'N', 'Unchacked'
                ) "Security Enabled",
         range_code "Range",
         DECODE (display_flag, 'Y', 'Checked', 'N', 'Unchacked') "Display",
         DECODE (enabled_flag, 'Y', 'Checked', 'N', 'Unchacked') "Enabled",
         DECODE (application_column_index_flag,
                 'Y', 'Checked',
                 'N', 'Unchacked'
                ) "Indexed",
         display_size "Display Size",
         maximum_description_len "Descirption Size",
         concatenation_description_len "Concatenated Description Size",
         form_above_prompt "List Of Value Prompt"
    FROM fnd_id_flex_segments fifs,
         fnd_id_flex_segments_tl fifsgt,
         fnd_id_flex_structures_tl fifst,
         fnd_flex_value_sets ffvs,
         fnd_application_tl fa
   WHERE fa.application_id = fifs.application_id
     AND fifs.id_flex_num = fifst.id_flex_num
     AND fifsgt.id_flex_num = fifs.id_flex_num
     AND fifsgt.id_flex_code = fifs.id_flex_code
     AND fifsgt.application_column_name = fifs.application_column_name
     AND fifst.id_flex_code = fifs.id_flex_code
     AND ffvs.flex_value_set_id = fifs.flex_value_set_id
   --AND fifst.id_flex_structure_name LIKE :flex_name
   --and fifst.id_flex_num = 53558
   --AND fifs.id_flex_code = 'GL#'
ORDER BY application_name,id_flex_structure_name;

Query To Find the Key Flexfield Details

SELECT fifs.id_flex_code,fat.application_name "Application", fif.id_flex_name "Flexfield Title",
       fifs.id_flex_structure_code "Structure Code",
       fifst.id_flex_structure_name "Structure Title",
       fifst.description "Structure Description",
       fifs.STRUCTURE_VIEW_NAME "Structure View Name"
       ,decode(fifs.FREEZE_FLEX_DEFINITION_FLAG,'Y','Checked','N','Unchacked') "Freeze Felxfield Definition",
       decode(ENABLED_FLAG,'Y','Checked','N','Unchacked') "Enabled",
       CONCATENATED_SEGMENT_DELIMITER "Segment Separator",
       decode(CROSS_SEGMENT_VALIDATION_FLAG,'Y','Checked','N','Unchacked') "Cross-Validation Segment",
       decode(FREEZE_STRUCTURED_HIER_FLAG,'Y','Checked','N','Unchacked') "Freeze Rollup Group" ,
       decode(DYNAMIC_INSERTS_ALLOWED_FLAG,'Y','Checked','N','Unchacked') "Allow Dyanamic Insert" ,
       fuc.User_name "Created By",
       ful.user_name "Last Updated By"      
  FROM fnd_id_flex_structures fifs,
       fnd_id_flexs fif,
       fnd_application_tl fat,
       fnd_id_flex_structures_tl fifst,
       fnd_user fuc,
       fnd_user ful
 WHERE fif.application_id = fat.application_id
   AND fifs.id_flex_code = fif.id_flex_code
   AND fifst.id_flex_num = fifs.id_flex_num
   AND fifst.id_flex_code = fifs.id_flex_code
   and fuc.user_id(+)=fifs.created_by
   and ful.user_id(+)=fifs.last_updated_by
   AND fifs.id_flex_code = 'GL#'
--   and fat.application_name  like 'G%'
--   AND id_flex_structure_name LIKE :Flex_name
order by application_name ; 

Query to Find The Structure/Defination of Value Set

SELECT ffvs.flex_value_set_name "Value Set Name", ffvs.description,
       flv.meaning "Validation Type", fls.meaning "Security Type",
              --security_enabled_flag,
       fll.meaning "List Type",
                               --longlist_flag,
                               flf.meaning "Format Type",
                                                         --format_type,
                                                         maximum_size,
       number_precision "Precision",
       DECODE (alphanumeric_allowed_flag,
               'Y', 'Unchacked',
               'N', 'Checked'
              ) "Number Only",
       DECODE (uppercase_only_flag,
               'Y', 'Checked',
               'N', 'Unchecked'
              ) "Upper Case Only",
       DECODE (numeric_mode_enabled_flag,
               'Y', 'Checked',
               'N', 'Unchacked'
              ) "Zero Fill number",
       minimum_value, maximum_value,
       fuc.user_name "Created By User",
       ful.user_name "Last Updated By User"
  FROM fnd_flex_value_sets ffvs,
       fnd_lookups flv,
       fnd_lookups fls,
       fnd_lookups fll,
       fnd_lookups flf,
       fnd_user fuc,
       Fnd_user ful
 WHERE flv.lookup_code = ffvs.validation_type
   AND fls.lookup_code = ffvs.security_enabled_flag
   AND fll.lookup_code = ffvs.longlist_flag
   AND flf.lookup_code = ffvs.format_type
   and fuc.user_id(+)=ffvs.created_by
   and ful.user_id(+)=ffvs.last_updated_by
   AND flf.lookup_type = 'FIELD_TYPE'
   AND fll.lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG'
   AND fls.lookup_type = 'FLEX_VST_SECURITY_ENABLED_FLAG'
   AND flv.lookup_type = 'SEG_VAL_TYPES'
   --AND flex_value_set_name LIKE :values_name
   order by ffvs.last_update_date;

Query to find the Securing attributes for User

SELECT fu.user_name "User", atr.attribute_code "Attribute",
       fa.application_name "Application", number_value "Value",
       fuc.user_name "Added By", ful.user_name "Updated By"
  FROM ak_web_user_sec_attr_values atr,
       fnd_user fu,
       fnd_application_tl fa,
       fnd_user fuc,
       fnd_user ful
 WHERE fu.user_id = atr.web_user_id
   AND atr.attribute_application_id = fa.application_id
   AND atr.created_by = fuc.user_id
   AND atr.last_updated_by = ful.user_id
   AND fu.user_name = UPPER (:user_name);

Query To find Indirect Responsibilities assign to User

SELECT fu.user_name "User", responsibility_name "Responsibility",
       application_name "Application", furgd.description "Description",
       security_group_key "SECURITY GROUP",
       TO_CHAR (furgd.start_date, 'DD-Mon-YYYY') "Start Date",
       TO_CHAR (furgd.end_date, 'DD-Mon-YYYY') "End Date",
       fuc.user_name "Added By", ful.user_name "Updated By"
  FROM fnd_user fu,
       fnd_responsibility_tl fr,
       fnd_user_resp_groups_indirect furgd,
       fnd_application_tl fa,
       fnd_security_groups fsg,
       fnd_user fuc,
       fnd_user ful
 WHERE fr.responsibility_id = furgd.responsibility_id
   AND fu.user_id = furgd.user_id
   AND fa.application_id = furgd.responsibility_application_id
   AND fsg.security_group_id = furgd.security_group_id
   AND furgd.created_by = fuc.user_id
   AND furgd.created_by = ful.user_id
   AND fu.user_name = UPPER (:user_name);

Query to Find the Direct Responsibilities assign to User

SELECT fu.user_name "User", responsibility_name "Responsibility",
       application_name "Application", furgd.description "Description",
       security_group_key "SECURITY GROUP",
       TO_CHAR (furgd.start_date, 'DD-Mon-YYYY') "Start Date",
       TO_CHAR (furgd.end_date, 'DD-Mon-YYYY') "End Date",
       fuc.user_name "Added By", ful.user_name "Updated By"
  FROM fnd_user fu,
       fnd_responsibility_tl fr,
       fnd_user_resp_groups_direct furgd,
       fnd_application_tl fa,
       fnd_security_groups fsg,
       fnd_user fuc,
       fnd_user ful
 WHERE fr.responsibility_id = furgd.responsibility_id
   AND fu.user_id = furgd.user_id
   AND fa.application_id = furgd.responsibility_application_id
   AND fsg.security_group_id = furgd.security_group_id
   AND furgd.created_by = fuc.user_id
   AND furgd.created_by = ful.user_id
   AND fu.user_name = UPPER (:user_name);

Query to find the Concurrent Request Scheduling Details

SELECT   r.request_id,
            p.user_concurrent_program_name
         || CASE
               WHEN p.user_concurrent_program_name = 'Report Set'
                  THEN (SELECT ' - ' || s.user_request_set_name
                          FROM fnd_request_sets_tl s
                         WHERE s.application_id = r.argument1
                           AND s.request_set_id = r.argument2
                           AND LANGUAGE = 'US')
               WHEN p.user_concurrent_program_name = 'Check Periodic Alert'
                  THEN (SELECT ' - ' || a.alert_name
                          FROM alr_alerts a
                         WHERE a.application_id = r.argument1
                           AND a.alert_id = r.argument2
                           AND LANGUAGE = 'US')
            END concurrent_program_name,
         CASE
            WHEN p.user_concurrent_program_name != 'Report Set'
            AND p.user_concurrent_program_name != 'Check Periodic Alert'
               THEN r.argument_text
         END argument_text,
         r.requested_start_date next_run, r.hold_flag on_hold,
         DECODE (c.class_type,
                 'P', 'Periodic',
                 'S', 'On Specific Days',
                 'X', 'Advanced',
                 c.class_type
                ) schedule_type,
         CASE
            WHEN c.class_type = 'P'
               THEN    'Repeat every '
                    || SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)
                    || DECODE (SUBSTR (c.class_info,
                                       INSTR (c.class_info, ':', 1, 1) + 1,
                                       1
                                      ),
                               'N', ' minutes',
                               'M', ' months',
                               'H', ' hours',
                               'D', ' days'
                              )
                    || DECODE (SUBSTR (c.class_info,
                                       INSTR (c.class_info, ':', 1, 2) + 1,
                                       1
                                      ),
                               'S', ' from the start of the prior run',
                               'C', ' from the completion of the prior run'
                              )
            WHEN c.class_type = 'S'
               THEN    NVL2 (dates.dates,
                             'Dates: ' || dates.dates || '. ',
                             NULL
                            )
                    || DECODE (SUBSTR (c.class_info, 32, 1),
                               '1', 'Last day of month '
                              )
                    || DECODE (SIGN (TO_NUMBER (SUBSTR (c.class_info, 33))),
                               '1', 'Days of week: '
                                || DECODE (SUBSTR (c.class_info, 33, 1),
                                           '1', 'Su '
                                          )
                                || DECODE (SUBSTR (c.class_info, 34, 1),
                                           '1', 'Mo '
                                          )
                                || DECODE (SUBSTR (c.class_info, 35, 1),
                                           '1', 'Tu '
                                          )
                                || DECODE (SUBSTR (c.class_info, 36, 1),
                                           '1', 'We '
                                          )
                                || DECODE (SUBSTR (c.class_info, 37, 1),
                                           '1', 'Th '
                                          )
                                || DECODE (SUBSTR (c.class_info, 38, 1),
                                           '1', 'Fr '
                                          )
                                || DECODE (SUBSTR (c.class_info, 39, 1),
                                           '1', 'Sa '
                                          )
                              )
         END schedule,
         c.date1 start_date, c.date2 end_date, c.class_info
    FROM fnd_concurrent_requests r,
         fnd_conc_release_classes c,
         fnd_concurrent_programs_tl p,
         (SELECT     release_class_id,
                     SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates
                FROM (SELECT release_class_id,
                             RANK () OVER (PARTITION BY release_class_id ORDER BY s)
                                                                            a,
                             s
                        FROM (SELECT c.class_info, l, c.release_class_id,
                                     DECODE (SUBSTR (c.class_info, l, 1),
                                             '1', TO_CHAR (l)
                                            ) s
                                FROM (SELECT     LEVEL l
                                            FROM DUAL
                                      CONNECT BY LEVEL <= 31),
                                     fnd_conc_release_classes c
                               WHERE c.class_type = 'S')
                       WHERE s IS NOT NULL)
          CONNECT BY PRIOR (a || release_class_id) =
                                                   (a - 1) || release_class_id
          START WITH a = 1
            GROUP BY release_class_id) dates
   WHERE r.phase_code = 'P'
     AND c.application_id = r.release_class_app_id
     AND c.release_class_id = r.release_class_id
     AND NVL (c.date2, SYSDATE + 1) > SYSDATE
     AND c.class_type IS NOT NULL
     AND p.concurrent_program_id = r.concurrent_program_id
     AND p.application_id = r.program_application_id
     AND p.LANGUAGE = 'US'
     AND dates.release_class_id(+) = r.release_class_id
ORDER BY on_hold, next_run;

Query to List Profile options details

SELECT   b.user_profile_option_name "Long Name",
         a.profile_option_name "Short Name",
         DECODE (TO_CHAR (c.level_id),
                 '10001', 'Site',
                 '10002', 'Application',
                 '10003', 'Responsibility',
                 '10004', 'User',
                 'Unknown'
                ) "Level",
         DECODE (TO_CHAR (c.level_id),
                 '10001', 'Site',
                 '10002', NVL (h.application_short_name,
                               TO_CHAR (c.level_value)
                              ),
                 '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
                 '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
                 'Unknown'
                ) "Level Value",
         c.profile_option_value "Profile Value",
         c.profile_option_id "Profile ID",
         TO_CHAR (c.last_update_date, 'DD-MON-YYYY HH24:MI') "Updated Date",
         NVL (d.user_name, TO_CHAR (c.last_updated_by)) "Updated By"
    FROM apps_fnd.fnd_profile_options a,
         apps_fnd.fnd_profile_options_vl b,
         apps_fnd.fnd_profile_option_values c,
         apps_fnd.fnd_user d,
         apps_fnd.fnd_user e,
         apps_fnd.fnd_responsibility_vl g,
         apps_fnd.fnd_application h
   WHERE
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
         b.user_profile_option_name LIKE 'EGO%'
     AND a.profile_option_name = b.profile_option_name
     AND a.profile_option_id = c.profile_option_id
     AND a.application_id = c.application_id
     AND c.last_updated_by = d.user_id(+)
     AND c.level_value = e.user_id(+)
     AND c.level_value = g.responsibility_id(+)
     AND c.level_value = h.application_id(+)
ORDER BY b.user_profile_option_name,
         c.level_id,
         DECODE (TO_CHAR (c.level_id),
                 '10001', 'Site',
                 '10002', NVL (h.application_short_name,
                               TO_CHAR (c.level_value)
                              ),
                 '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
                 '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
                 'Unknown'
                );