OTL Restrict Cost Centre list

The following SQL will restrict the list of GL cost centre segment values according to the security rules assigned to a specific responsibility. The original query was written for Virgin Money, and also includes PO Controls:

SELECT /*
Program: VM_ACTIVITY_LOV_V.sql
Author: David Taylor
Description: Custom view to select valid cost centres.
Version: 1.0
Date: 19-AUG-2006
History: 1.0 Original version */
vsv.flex_value cc
, vsv.description cc_desc
FROM gl_sets_of_books sofb
, fnd_id_flex_structures st
, fnd_id_flex_segments sg
, fnd_flex_values_vl vsv
WHERE sofb.chart_of_accounts_id = st.id_flex_num
AND st.id_flex_code = sg.id_flex_code
AND st.id_flex_num = sg.id_flex_num
AND st.application_id = sg.application_id
AND sg.flex_value_set_id = vsv.flex_value_set_id
AND sofb.set_of_books_id = fnd_profile.value(‘GL_SET_OF_BKS_ID’)
AND st.id_flex_code= ‘GL#’
AND sg.segment_name = ‘Cost Centre’
AND nvl(vsv.enabled_flag, ‘Y’) = ‘Y’
AND nvl(vsv.summary_flag, ‘N’) = ‘N’
AND trunc(sysdate) BETWEEN trunc(nvl(vsv.start_date_active, sysdate))
AND trunc(nvl(vsv.end_date_active, sysdate))
AND EXISTS
(
SELECT null
FROM po_control_functions f
, po_position_controls_all p
, po_control_groups c
, po_control_rules r
, per_assignments_x a
, per_people_x pe
, fnd_user u
WHERE f.control_function_id = p.control_function_id
AND p.control_group_id = c.control_group_id
AND c.control_group_id = r.control_group_id
AND p.position_id = a.position_id
AND a.person_id = pe.person_id
AND pe.person_id = u.employee_id
AND f.control_function_name = ‘Approve Purchase Requisitions’
AND f.enabled_flag = ‘Y’
AND trunc(sysdate) BETWEEN trunc(p.start_date)
AND trunc(nvl(p.end_date,sysdate))
AND c.enabled_flag = ‘Y’
AND u.user_id = fnd_profile.value(‘USER_ID’)
AND vsv.flex_value BETWEEN r.segment2_low
AND r.segment2_high
)
AND (
NOT EXISTS
(
SELECT ‘x’
FROM fnd_flex_value_rule_usages usg
, fnd_flex_value_rules rul
WHERE usg.application_id = fnd_profile.value(‘RESP_APPL_ID’)
AND usg.responsibility_id = fnd_profile.value(‘RESP_ID’)
AND usg.flex_value_rule_id = rul.flex_value_rule_id
AND rul.flex_value_set_id = vsv.flex_value_set_id
)
OR
(
EXISTS
(
SELECT ‘x’
FROM fnd_flex_value_rule_usages usg
, fnd_flex_value_rules rul
, fnd_flex_value_rule_lines line
WHERE usg.application_id = fnd_profile.value(‘RESP_APPL_ID’)
AND usg.responsibility_id = fnd_profile.value(‘RESP_ID’)
AND usg.flex_value_rule_id = rul.flex_value_rule_id
AND line.flex_value_rule_id = rul.flex_value_rule_id
AND line.include_exclude_indicator = ‘I’
AND vsv.flex_value BETWEEN line.flex_value_low
AND line.flex_value_high
AND rul.flex_value_set_id = vsv.flex_value_set_id
)
AND NOT EXISTS
(
SELECT ‘x’
FROM fnd_flex_value_rule_usages usg
, fnd_flex_value_rules rul
, fnd_flex_value_rule_lines line
WHERE usg.application_id = fnd_profile.value(‘RESP_APPL_ID’)
AND usg.responsibility_id = fnd_profile.value(‘RESP_ID’)
AND usg.flex_value_rule_id = rul.flex_value_rule_id
AND line.flex_value_rule_id = rul.flex_value_rule_id
AND line.include_exclude_indicator = ‘E’
AND vsv.flex_value BETWEEN line.flex_value_low
AND line.flex_value_high
AND rul.flex_value_set_id = vsv.flex_value_set_id
)
)
)
ORDER BY vsv.flex_value

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s