SQL Query to get User Names of the Responsibility Attached

SELECT UNIQUE
    papf.full_name,
    fu.user_name,
    frt.responsibility_id,
    frt.responsibility_name,
    frv.responsibility_key,
    frv.application_id,
    furg.end_date,
    frv.end_date,
    fu.end_date
FROM
    apps.fnd_user                fu,
    apps.fnd_user_resp_groups    furg,
    apps.fnd_responsibility_tl   frt,
    apps.fnd_responsibility_vl   frv,
    apps.per_all_people_f        papf
WHERE
    fu.user_id = furg.user_id
    AND furg.responsibility_id = frt.responsibility_id
    AND frt.responsibility_id = frv.responsibility_id
    AND papf.person_id = fu.employee_id
    AND frt.responsibility_name = 'RESPONSIBILITY_NAME'
    AND fu.user_name = 'USER_NAME';

Standard PO's for last 12 months

SELECT
    pha.segment1                po_no,
    trunc(pha.creation_date) po_date,
    pha.currency_code           po_currency,
    nvl(pha.rate, 1) po_rate,
    nvl2(pla.item_id, msib.segment1, NULL) item,
    nvl2(pla.item_id, msib.description, pla.item_description) item_desc,
    msib.primary_uom_code       item_uom,
    mck.concatenated_segments   item_category,
    pll.price_override          item_po_price,
    aps.vendor_name             supplier_name,
    nvl((nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0)), 0) ordered_qty,
    ( nvl((nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0)), 0) * pll.price_override ) ordered_value_po_curr,
    (
        CASE pha.currency_code
            WHEN 'AED' THEN
                ( nvl((nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0)), 0) * pll.price_override )
            ELSE
                ( ( nvl((nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0)), 0) * pll.price_override ) * nvl(pha.rate, 1) )
        END
    ) ordered_value_aed
FROM
    apps.po_headers_all              pha,
    apps.po_lines_all                pla,
    apps.po_distributions_all        pda,
    apps.gl_code_combinations        gcc,
    apps.po_line_locations_all       pll,
    apps.hr_all_organization_units   hao,
    apps.mtl_system_items_b          msib,
    apps.mtl_categories_kfv          mck,
    apps.po_agents_v                 pov,
    apps.ap_suppliers                aps,
    apps.ap_supplier_sites_all       ass
WHERE
    pha.po_header_id = pla.po_header_id
    AND pla.po_line_id = pll.po_line_id
    AND pll.ship_to_organization_id = hao.organization_id
    AND pll.po_release_id IS NULL
    AND pla.item_id = msib.inventory_item_id (+)
    AND pha.po_header_id = pda.po_header_id
    AND pla.po_line_id = pda.po_line_id
    AND pda.code_combination_id = gcc.code_combination_id
    AND msib.organization_id (+) = 63
    AND pla.category_id = mck.category_id
    AND pha.agent_id = pov.agent_id
    AND pha.vendor_id = aps.vendor_id
    AND pha.vendor_site_id = ass.vendor_site_id
    AND aps.vendor_id = ass.vendor_id
    AND pha.type_lookup_code = 'STANDARD'
    AND trunc(pha.creation_date) BETWEEN trunc(add_months(sysdate, - 12), 'MONTH') AND trunc(sysdate, 'MONTH')
ORDER BY
    trunc(pha.creation_date) ASC

SQL Query to find Responsibility for given Request group / Find Request group attached responsibilities

SELECT responsibility_name ,
  request_group_name        ,
  frg.description
   FROM fnd_request_groups frg,
  fnd_responsibility_vl frv
  WHERE frv.request_group_id = frg.request_group_id
AND request_group_name    LIKE 'Request group name'
ORDER BY responsibility_name;

SQL Query to find Concurrent program details with Parameters, status, submitted date and responsibility

SELECT
    cpt.user_concurrent_program_name   "Concurrent Program Name",
    decode(rgu.request_unit_type, 'P', 'Program', 'S', 'Set',
           rgu.request_unit_type) "Unit Type",
    fcr.status_code                    status,
    fcr.request_date                   prog_submitted_date,
    fnr.responsibility_name,
    fcr.argument1
    || '-'
    || fcr.argument2
    || '-'
    || fcr.argument3
    || '-'
    || fcr.argument4
    || '-'
    || fcr.argument5
    || '-'
    || fcr.argument6
    || '-'
    || fcr.argument7
    || '-'
    || fcr.argument8
    || '-'
    || fcr.argument9
    || '-'
    || fcr.argument10
    || '-'
    || fcr.argument11
    || '-'
    || fcr.argument12
    || '-'
    || fcr.argument13
    || '-'
    || fcr.argument14
    || '-'
    || fcr.argument15 parameters,
    cp.concurrent_program_name         "Concurrent Program Short Name",
    rg.application_id                  "Application ID",
    rg.request_group_name              "Request Group Name",
    fat.application_name               "Application Name",
    fa.application_short_name          "Application Short Name",
    fa.basepath                        "Basepath",
    cpt.concurrent_program_id
FROM
    fnd_request_groups           rg,
    fnd_request_group_units      rgu,
    fnd_concurrent_programs      cp,
    fnd_concurrent_programs_tl   cpt,
    fnd_application              fa,
    fnd_application_tl           fat--,
    ,
    fnd_concurrent_requests      fcr,
    fnd_responsibility_tl        fnr
WHERE
    rg.request_group_id = rgu.request_group_id
    AND rgu.request_unit_id = cp.concurrent_program_id
    AND cp.concurrent_program_id = cpt.concurrent_program_id
    AND rg.application_id = fat.application_id
    AND fa.application_id = fat.application_id
    AND cpt.language = userenv('LANG')
    AND fat.language = userenv('LANG')
    AND cpt.concurrent_program_id = fcr.concurrent_program_id
    AND fcr.responsibility_id = fnr.responsibility_id
    AND cpt.user_concurrent_program_name = 'Concurrent program name'
ORDER BY
    fcr.request_date DESC

How to find patches applied/installed on Oracle database

SELECT
e.patch_name,
c.end_date,
a.bug_number,
b.applied_flag
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
AND a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id

AND d.applied_patch_id = e.applied_patch_id

For Oracle 11g Release:

1. using Opatch utility

cd $ORACLE_HOME/OPatch

opatch lsinventory

2.  select * from sys.registry$history;



For Oracle 12c Release:

1. Opatch utility

cd $ORACLE_HOME/OPatch

opatch lsinventory

2. using dba_registry_sqlpatch view:

SQL> select * from dba_registry_sqlpatch;

3. using package dbms_qopatch

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Finding Nth highest salary in a table

Finding Nth highest salary in a table, Here is a way to do this task using dense_rank() function.


select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from Employee)
where r=&n;


DENSE_RANK :

1. DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.

2. This function accepts arguments as any numeric data type and returns NUMBER.

3. As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
 
4. In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows.

SQL Query to find duplicate values in a table

SQL Query to find duplicate values in a table


SELECT
col,
COUNT(col)
FROM.
table_name.
GROUP BY col.
HAVING COUNT(col) > 1;

Query to find Request Group for concurrent program

Query to find Request Group for concurrent program






SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"
  FROM fnd_request_groups          rg,
       fnd_request_group_units     rgu,
       fnd_concurrent_programs     cp,
       fnd_concurrent_programs_tl  cpt,
       fnd_application             fa,
       fnd_application_tl          fat
 WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND cpt.user_concurrent_program_name = 'xx concurrent program name';

FND_GLOBAL and FND_PROFILE: Import List of System Global values

FND_GLOBAL and FND_PROFILE: Import List of System Global values


FND_PROFILE values:


1  fnd_profile.value('PROFILEOPTION')
2  fnd_profile.value('MFG_ORGANIZATION_ID')
3  fnd_profile.value('ORG_ID')
4  fnd_profile.value('LOGIN_ID')
5  fnd_profile.value('USER_ID')
6  fnd_profile.value('USERNAME')
7  fnd_profile.value('CONCURRENT_REQUEST_ID')
8  fnd_profile.value('GL_SET_OF_BKS_ID')
9  fnd_profile.value('SO_ORGANIZATION_ID')
10 fnd_profile.value('APPL_SHRT_NAME')
11 fnd_profile.value('RESP_NAME')
12 fnd_profile.value('RESP_ID')
13 fnd_profile.value('PER_BUSINESS_GROUP_ID')
14 fnd_profile.value('GL_SET_OF_BKS_ID')
15 fnd_profile.value('CURRENT_ORG_CONTEXT')


FND_GLOBAL values:


1  fnd_global.USER_ID
2  fnd_global.USER_NAME
3  fnd_global.RESP_ID
4  fnd_global.RESP_NAME
5  fnd_global.APPLICATION_NAME
6  fnd_global.APPLICATION_SHORT_NAME
7  fnd_global.RESP_APPL_ID
8  fnd_global.BASE_LANGUAGE
9  fnd_global.CONC_LOGIN_ID
10 fnd_global.CONC_PRIORITY_REQUEST
11 fnd_global.CONC_PROCESS_ID
12 fnd_global.CONC_PROGRAM_ID
13 fnd_global.CONC_QUEUE_ID
14 fnd_global.CONC_REQUEST_ID
15 fnd_global.CURRENT_LANGUAGE
16 fnd_global.CUSTOMER_ID
17 fnd_global.EMPLOYEE_ID
18 fnd_global.FORM_APPL_ID
19 fnd_global.FORM_ID
20 fnd_global.GET_SESSION_CONTEXT
21 fnd_global.LANGUAGE_COUNT
22 fnd_global.LOGIN_ID
23 fnd_global.NEWLINE
24 fnd_global.NLS_DATE_FORMAT
25 fnd_global.NLS_DATE_LANGUAGE
26 fnd_global.NLS_LANGUAGE
27 fnd_global.NLS_NUMERIC_CHARACTERS
28 fnd_global.NLS_SORT
29 fnd_global.NLS_TERRITORY
30 fnd_global.ORG_ID
31 fnd_global.ORG_NAME
32 fnd_global.PARTY_ID
33 fnd_global.PER_BUSINESS_GROUP_ID
34 fnd_global.PER_SECURITY_PROFILE_ID
35 fnd_global.PROG_APPL_ID
36 fnd_global.QUEUE_APPL_ID
37 fnd_global.RT_TEST_ID
38 fnd_global.SECURITY_GROUP_ID
39 fnd_global.SERVER_ID
40 fnd_global.SESSION_ID
41 fnd_global.SUPPLIER_ID
42 fnd_global.TAB


Examples to initialize the application environment:


v_user_id      PLS_INTEGER  :=  fnd_global.user_id;
v_login_id     PLS_INTEGER  :=  fnd_global.login_id;
v_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;
v_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');
v_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');


v_resp_appl_id  := fnd_global.resp_appl_id;
v_resp_id       := fnd_global.resp_id;
v_user_id       := fnd_global.user_id;
    
FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

SQL Query to find KEY FLEX FIELDS (KFF) Definitions:

SQL Query to find KEY FLEX FIELDS (KFF) Definitions:


 select  fif.application_id  ,
         fif.id_flex_code    ,
         fif.id_flex_name    ,
         fif.application_table_name ,
         fif.description     ,
         fifs.id_flex_num    ,
         fifs.id_flex_structure_code  ,
         fifse.segment_name,
         fifse.segment_num,
         fifse.flex_value_set_id
 from    fnd_id_flexs fif    ,
         fnd_id_flex_structures fifs ,
         fnd_id_flex_segments fifse
 where   fif.application_id   = fifs.application_id
 and     fif.id_flex_code     = fifs.id_flex_code
 and     fifse.application_id = fif.application_id
 and     fifse.id_flex_code   = fif.id_flex_code
 and     fifse.id_flex_num    = fifs.id_flex_num
 and     fif.id_flex_code     like 'GL#'
 and     fif.id_flex_name     like 'Accounting Flexfield';

SQL Query to find the Concurrent Programs attached Responsibility details / SQL Query to find Concurrent Programs with Responsibilities

SQL Query to find the Concurrent Programs Responsibility details
SELECT frt.responsibility_name
    ,frg.request_group_name
       ,frgu.request_unit_type
    ,frgu.request_unit_id
       ,fcpt.user_concurrent_program_name
FROM   fnd_Responsibility fr, fnd_responsibility_tl frt,
       fnd_request_groups frg, fnd_request_group_units frgu,
       fnd_concurrent_programs_tl fcpt
WHERE  1                                 = 1
AND    frt.responsibility_id             = fr.responsibility_id
AND    frg.request_group_id              = fr.request_group_id
AND    frgu.request_group_id             = frg.request_group_id
AND    fcpt.concurrent_program_id        = frgu.request_unit_id
AND    frt.LANGUAGE                      = USERENV('LANG')
AND    fcpt.LANGUAGE                     = USERENV('LANG')
AND    fcpt.user_concurrent_program_name = :Concurrent_Program_Name ;

SQL Query to check Site Level Profile Options enabled or not / Check Site Level enabled Profiles / Check Profiles

SELECT
    p.profile_option_name short_name,
    n.user_profile_option_name name,
    DECODE(
        v.level_id,
        10001,
        'Site',
        10002,
        'Application',
        10003,
        'Responsibility',
        10004,
        'User',
        10005,
        'Server',
        10006,
        'Org',
        10007,
        DECODE(
            TO_CHAR(v.level_value2),
            '-1',
            'Responsibility',
            DECODE(TO_CHAR(v.level_value),'-1','Server','Server+Resp')
        ),
        'UnDef'
    ) level_set,
    DECODE(
        TO_CHAR(v.level_id),
        '10001',
        '',
        '10002',
        app.application_short_name,
        '10003',
        rsp.responsibility_key,
        '10004',
        usr.user_name,
        '10005',
        svr.node_name,
        '10006',
        org.name,
        '10007',
        DECODE(
            TO_CHAR(v.level_value2),
            '-1',
            rsp.responsibility_key,
            DECODE(
                TO_CHAR(v.level_value),
                '-1',
                (
                    SELECT
                        node_name
                    FROM
                        fnd_nodes
                    WHERE
                        node_id = v.level_value2
                ),
                (
                    SELECT
                        node_name
                    FROM
                        fnd_nodes
                    WHERE
                        node_id = v.level_value2
                )
                 ||  '-'
                 ||  rsp.responsibility_key
            )
        ),
        'UnDef'
    ) "CONTEXT",
    v.profile_option_value value
FROM
    fnd_profile_options p,
    fnd_profile_option_values v,
    fnd_profile_options_tl n,
    fnd_user usr,
    fnd_application app,
    fnd_responsibility rsp,
    fnd_nodes svr,
    hr_operating_units org
WHERE
    p.profile_option_id = v.profile_option_id (+)
AND
    p.profile_option_name = n.profile_option_name
AND
    upper(p.profile_option_name) IN (
        SELECT
            profile_option_name
        FROM
            fnd_profile_options_tl
        WHERE
            upper(user_profile_option_name) LIKE upper('MO: Operating Unit')
    )
AND
    usr.user_id (+) = v.level_value
AND
    rsp.application_id (+) = v.level_value_application_id
AND
    rsp.responsibility_id (+) = v.level_value
AND
    app.application_id (+) = v.level_value
AND
    svr.node_id (+) = v.level_value
AND
    org.organization_id (+) = v.level_value
AND
    DECODE(
        v.level_id,
        10001,
        'Site',
        10002,
        'Application',
        10003,
        'Responsibility',
        10004,
        'User',
        10005,
        'Server',
        10006,
        'Org',
        10007,
        DECODE(
            TO_CHAR(v.level_value2),
            '-1',
            'Responsibility',
            DECODE(TO_CHAR(v.level_value),'-1','Server','Server+Resp')
        ),
        'UnDef'
    ) = 'Site'
ORDER BY
    short_name,
    user_profile_option_name,
    level_id,
    level_set;

Optimizer costs don't match SQL execution performance

Question:  I am tuning an Oracle SQL query and I've noted that the costs don't match the execution speed and SQL where the optimizer shows a lower cost actually take more time to execute.  I want to know why in second case the cost is less but time taken to execute the query is more?
Answer:  It's important to note that the cost figures that are displayed in an execution plan are not described in any Oracle documentation, and it's been demonstrated that the plan with the lowest ?cost? number is not always the plan chosen by the optimizer.  Further, the ?cost? figures do not always indicate the ?best:? execution plan for a query, given the divergent optimizer goals of first_rows (optimizer for response time) and all_rows (optimize for minimizing computing resources).

In general, the lower the CBO-calculated cost, the faster the query will run, but we have to remember that the costs used by the CBO are only estimates, and the numbers are based on metadata statistics collected by dbms_stats.  The cost based optimizer (CBO) creates these estimates based on many variables:


you can refer below link for more details:


1) http://www.dba-oracle.com/t_cbo_cost_does_not_match_performance.htm


2) http://www.dba-oracle.com/t_display_sql_optimizer_features.htm
3) http://www.dba-oracle.com/t_system_session_fix_control.htm

SQL Query to find Application Use Credentials from Backend / SQL Query to Get the User Passwords from back end based on the username

SELECT usr.user_name,
       get_pwd.decrypt (
          (SELECT (SELECT get_pwd.decrypt (
                             fnd_web_sec.get_guest_username_pwd,
                             usertable.encrypted_foundation_password)
                     FROM DUAL)
                     AS apps_password
             FROM fnd_user usertable
            WHERE usertable.user_name =
                     (SELECT SUBSTR (
                                fnd_web_sec.get_guest_username_pwd,
                                1,
                                  INSTR (fnd_web_sec.get_guest_username_pwd,
                                         '/')
                                - 1)
                        FROM DUAL)),
          usr.encrypted_user_password)
          PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = 'USERNAME';