1. Tables and Views for Sales and Fusion Service
  2. OKC_TEAM_SEC_PREDICATE_V

OKC_TEAM_SEC_PREDICATE_V

Details

  • Schema: FUSION

  • Object owner: OKC

  • Object type: VIEW

Columns

Name

CONTRACT_ID

MAJOR_VERSION

ACCESS_LEVEL

Query

SQL_Statement

SELECT c.dnz_chr_id as contract_id,

c.major_version as major_version,

c.access_level as access_level

FROM okc_contacts c

WHERE c.version_type='C'

AND trunc(sysdate) BETWEEN trunc(NVL(c.start_date, sysdate -1)) AND trunc(NVL(c.end_date, sysdate + 1))

AND (

(c.jtot_object1_code='OKX_RESOURCE' AND c.object1_id1 = HZ_SESSION_UTIL.get_user_partyid

) OR

(c.jtot_object1_code='OKX_RESOURCE' AND HZ_SESSION_UTIL.get_user_partyid IN ( select mgr.parent_resource_id

from jtf_rs_rep_managers mgr, fnd_tree_version vers

where mgr.resource_id = c.object1_id1

and vers.tree_code = mgr.tree_code

and vers.tree_structure_code = mgr.tree_structure_code

and vers.tree_version_id = mgr.tree_version_id

and vers.status = 'ACTIVE'

and trunc(sysdate) between trunc(mgr.start_date_active) and NVL(trunc(mgr.end_date_active), trunc(sysdate + 1))

)

) OR

(

c.jtot_object1_code='OKX_RESOURCE_ORG' AND HZ_SESSION_UTIL.get_user_partyid IN (

select resource_id

from jtf_rs_group_members where group_id = c.object1_id1 and delete_flag = 'N'

)

) OR

(c.jtot_object1_code='OKX_RESOURCE_ORG' AND HZ_SESSION_UTIL.get_user_partyid IN (

select resource_id

from jtf_rs_group_members where delete_flag = 'N' and group_id in (

select group_id

from jtf_rs_rep_managers mgr , fnd_tree_version vers

where vers.tree_code = mgr.tree_code

and vers.tree_structure_code = mgr.tree_structure_code

and vers.tree_version_id = mgr.tree_version_id

and vers.status = 'ACTIVE'

and mgr.parent_org_id = c.object1_id1

and trunc(sysdate) between trunc(mgr.start_date_active) and NVL(trunc(mgr.end_date_active), trunc(sysdate + 1))

)

)

) OR

(c.jtot_object1_code='OKX_RESOURCE_ORG' AND HZ_SESSION_UTIL.get_user_partyid IN (

select mgr.parent_resource_id

from jtf_rs_rep_managers mgr , fnd_tree_version vers

where vers.tree_code = mgr.tree_code

and vers.tree_structure_code = mgr.tree_structure_code

and vers.tree_version_id = mgr.tree_version_id

and vers.status = 'ACTIVE'

and mgr.group_id = c.object1_id1

and trunc(sysdate) between trunc(mgr.start_date_active) and NVL(trunc(mgr.end_date_active), trunc(sysdate + 1))

and mgr.denorm_level > 0

)

)

)