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

OKC_CONTRACT_ACKL_LIST_V

Details

  • Schema: FUSION

  • Object owner: OKC

  • Object type: VIEW

Columns

Name

CONTRACT_ID

MAJOR_VERSION

RESOURCE_ID

ACCESS_LEVEL

Query

SQL_Statement

select c.dnz_chr_id as contract_id, c.major_version, c.object1_id1 as resource_id, NVL(c.access_level, 'FULL') as access_level

from okc_contacts c

where c.jtot_object1_code='OKX_RESOURCE'

UNION ALL

select c.dnz_chr_id as contract_id, c.major_version, mgr.parent_resource_id as resource_id , NVL(c.access_level, 'FULL') as access_level

from jtf_rs_rep_managers mgr, fnd_tree_version vers , okc_contacts c

where mgr.resource_id = c.object1_id1

and c.jtot_object1_code='OKX_RESOURCE'

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))

UNION ALL

select c.dnz_chr_id as contract_id, c.major_version, r.resource_id as resource_id , NVL(c.access_level, 'FULL') as access_level

from jtf_rs_group_members r, okc_contacts c

where c.jtot_object1_code='OKX_RESOURCE_ORG'

and r.group_id = c.object1_id1

UNION ALL

select co.dnz_chr_id as contract_id, co.major_version, r.resource_id as resource_id , NVL(co.access_level, 'FULL') as access_level

from jtf_rs_group_members r , okc_contacts co

where co.jtot_object1_code='OKX_RESOURCE_ORG'

and r.group_id in (

select group_id

from jtf_rs_rep_managers mgr , fnd_tree_version vers , okc_contacts c

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 co.dnz_chr_id = c.dnz_chr_id

and co.major_version = c.major_version

and c.jtot_object1_code='OKX_RESOURCE_ORG'

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))

)

UNION ALL

select c.dnz_chr_id as contract_id, c.major_version,mgr.parent_resource_id as resource_id , NVL(c.access_level, 'FULL') as access_level

from jtf_rs_rep_managers mgr , fnd_tree_version vers , okc_contacts c

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 c.jtot_object1_code='OKX_RESOURCE_ORG'

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