VIEW: APPS.OKE_K_ALL_ACCESS_BASIC_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)