View: OKE_K_ALL_ACCESS_BASIC_V

Product:	OKE - Project Contracts
Description:	Contract Security Assignment basic view
Implementation/DBA Data:	APPS.OKE_K_ALL_ACCESS_BASIC_V

View Text

SELECT DECODE( SUBSTR(A.ASSIGNMENT_KEY
, 1
, 1)
, 3
, 'SITE'
, P.OBJECT_TYPE )
, K.K_HEADER_ID
, P.RESOURCE_SOURCE_ID
, P.PROJECT_ROLE_ID
, P.START_DATE_ACTIVE
, P.END_DATE_ACTIVE
, P.CREATION_DATE
FROM OKE_K_HEADERS K
, ( SELECT PROJECT_PARTY_ID
, OBJECT_TYPE
, OBJECT_ID
, RESOURCE_SOURCE_ID
, PROJECT_ROLE_ID
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, CREATION_DATE
FROM PA_PROJECT_PARTIES
WHERE RESOURCE_TYPE_ID = 101
AND OBJECT_TYPE LIKE 'OKE%' UNION ALL SELECT /*+ INDEX(K OKE_K_HEADERS_U1) */ K.K_HEADER_ID
, 'CREATED_BY'
, K.K_HEADER_ID
, U.EMPLOYEE_ID
, TO_NUMBER(NULL)
, K.CREATION_DATE
, TO_DATE(NULL)
, K.CREATION_DATE
FROM OKE_K_HEADERS K
, FND_USER U
WHERE K.CREATED_BY = U.USER_ID ) P
, ( SELECT K2.K_HEADER_ID
, MIN( DECODE( P2.OBJECT_TYPE
, 'OKE_K_HEADERS'
, 1
, 'OKE_PROGRAMS'
, DECODE( P2.OBJECT_ID
, 0
, 3
, 2 )
, 4 ) || ':' || P2.PROJECT_PARTY_ID ) ASSIGNMENT_KEY
FROM ( SELECT /*+ INDEX(P PA_PROJECT_PARTIES_N3) */ PROJECT_PARTY_ID
, OBJECT_TYPE
, OBJECT_ID
, RESOURCE_SOURCE_ID
, START_DATE_ACTIVE
, END_DATE_ACTIVE
FROM PA_PROJECT_PARTIES P
WHERE RESOURCE_TYPE_ID = 101
AND OBJECT_TYPE LIKE 'OKE%' UNION ALL SELECT /*+ INDEX(K OKE_K_HEADERS_U1) */ K.K_HEADER_ID
, 'CREATED_BY'
, K.K_HEADER_ID
, U.EMPLOYEE_ID
, K.CREATION_DATE
, TO_DATE(NULL)
FROM OKE_K_HEADERS K
, FND_USER U
WHERE K.CREATED_BY = U.USER_ID ) P2
, OKE_K_HEADERS K2
WHERE ( ( P2.OBJECT_TYPE = 'OKE_K_HEADERS'
AND P2.OBJECT_ID = K2.K_HEADER_ID ) OR ( P2.OBJECT_TYPE = 'OKE_PROGRAMS'
AND P2.OBJECT_ID = K2.PROGRAM_ID ) OR ( P2.OBJECT_TYPE = 'OKE_PROGRAMS'
AND P2.OBJECT_ID = 0 ) OR ( P2.OBJECT_TYPE = 'CREATED_BY'
AND P2.OBJECT_ID = K2.K_HEADER_ID ) )
AND TRUNC(OKE_K_SECURITY_PKG.GET_ASSIGNMENT_DATE) >= TRUNC(P2.START_DATE_ACTIVE)
AND ( P2.END_DATE_ACTIVE IS NULL OR TRUNC(OKE_K_SECURITY_PKG.GET_ASSIGNMENT_DATE) <= TRUNC(P2.END_DATE_ACTIVE) ) GROUP BY K2.K_HEADER_ID
, P2.RESOURCE_SOURCE_ID ) A
WHERE K.K_HEADER_ID = A.K_HEADER_ID
AND P.PROJECT_PARTY_ID = SUBSTR(A.ASSIGNMENT_KEY
, 3
, LENGTH(A.ASSIGNMENT_KEY) - 2)

Columns