Sunday, January 13, 2019

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;

No comments:

Post a Comment

SQL Query to find Customer, Customer Account and Customer Sites Information

/****************************************************************************** *PURPOSE: Query to Customer, Customer Account and Customer...