POR_SHOPPING_LISTS

Details

Schema: FUSION
Object owner: POR
Object type: VIEW

Columns

Name
PUBLIC_LIST_HEADER_ID PUBLIC_LIST_NAME LIST_NAME DESCRIPTION SHOPPING_LIST_TYPE IMAGE_URL CREATION_DATE CREATED_BY LAST_UPDATED_BY LAST_UPDATE_DATE REQ_BU_ID PERSON_ID

Name

PUBLIC_LIST_HEADER_ID

PUBLIC_LIST_NAME

LIST_NAME

DESCRIPTION

SHOPPING_LIST_TYPE

IMAGE_URL

CREATION_DATE

CREATED_BY

LAST_UPDATED_BY

LAST_UPDATE_DATE

REQ_BU_ID

PERSON_ID

Query

SQL_Statement
SELECT DISTINCT PUBLICLISTHEADEREO.PUBLIC_LIST_HEADER_ID, PUBLICLISTHEADEREO.PUBLIC_LIST_NAME, UPPER(PUBLICLISTHEADEREO.PUBLIC_LIST_NAME) LIST_NAME, PUBLICLISTHEADEREO.DESCRIPTION, 'PUBLIC' SHOPPING_LIST_TYPE, PUBLICLISTHEADEREO.IMAGE_URL, PUBLICLISTHEADEREO.CREATION_DATE, PUBLICLISTHEADEREO.CREATED_BY, PUBLICLISTHEADEREO.LAST_UPDATED_BY, PUBLICLISTHEADEREO.LAST_UPDATE_DATE, IQ.REQ_BU_ID, IQ.PERSON_ID FROM POR_PUBLIC_LIST_HEADERS_ALL_VL PUBLICLISTHEADEREO, ( SELECT CZASSIGNMENTEO.ASSIGNMENT_ID, USERPREF.REQ_BU_ID, USERPREF.PERSON_ID FROM POR_CONTENT_ZONES_ALL_B CONTENTZONEEO, POR_CZ_SECURITY_ASSIGNMENTS CZSECURITYASSIGNMENTEO, POR_CZ_ASSIGNMENTS CZASSIGNMENTEO, POR_USER_PREFERENCES_ALL USERPREF, FUN_BU_SERVICE_PROVIDERS BUSERVICEPROVIDERPEO WHERE BUSERVICEPROVIDERPEO.UPSTREAM_FUNCTION_ID = 120 AND BUSERVICEPROVIDERPEO.DOWNSTREAM_FUNCTION_ID = 110 AND BUSERVICEPROVIDERPEO.ACTIVE_FLAG = 'Y' AND BUSERVICEPROVIDERPEO.CLIENT_BU_ORG_ID = USERPREF.REQ_BU_ID AND CONTENTZONEEO.PRC_BU_ID = BUSERVICEPROVIDERPEO.PROVIDER_BU_ORG_ID AND CONTENTZONEEO.USAGE = 'REQUISITIONING' AND CONTENTZONEEO.CONTENT_ZONE_ID = CZSECURITYASSIGNMENTEO.CONTENT_ZONE_ID(+) AND CONTENTZONEEO.CONTENT_ZONE_ID = CZASSIGNMENTEO.CONTENT_ZONE_ID AND CZASSIGNMENTEO.ASSIGNMENT_TYPE = 'PUBLIC_LIST' AND (USERPREF.TYPE = 'SSP' OR USERPREF.TYPE IS NULL) AND ((( (CONTENTZONEEO.SECURITY_DIMENSION = 'SECURED_BY_BU') AND (CZSECURITYASSIGNMENTEO.REQ_BU_ID = USERPREF.REQ_BU_ID ) ) OR ((CONTENTZONEEO.SECURITY_DIMENSION = 'SECURED_BY_WORKER') AND (CZSECURITYASSIGNMENTEO.WORKER_ID = USERPREF.PERSON_ID ) ) OR ((CONTENTZONEEO.SECURITY_DIMENSION = 'SECURED_BY_DEL_LOC') AND (CZSECURITYASSIGNMENTEO.DELIVER_TO_LOCATION_ID = USERPREF.DELIVER_TO_LOCATION_ID ) )) OR (CONTENTZONEEO.SECURITY_DIMENSION = 'AVAIL_ALL_REQ_BUS') ) ) IQ WHERE PUBLICLISTHEADEREO.PUBLIC_LIST_HEADER_ID = IQ.ASSIGNMENT_ID AND SYSDATE BETWEEN NVL(PUBLICLISTHEADEREO.START_DATE, SYSDATE) AND NVL(PUBLICLISTHEADEREO.END_DATE, SYSDATE) UNION ALL SELECT DISTINCT PERSONALLISTHEADEREO.PERSONAL_LIST_HEADER_ID, PERSONALLISTHEADEREO.PERSONAL_LIST_NAME, UPPER(PERSONALLISTHEADEREO.PERSONAL_LIST_NAME) LIST_NAME, NULL AS DESCRIPTION, 'PERSONAL' AS SHOPPING_LIST_TYPE, PERSONALLISTHEADEREO.IMAGE_URL, PERSONALLISTHEADEREO.CREATION_DATE, PERSONALLISTHEADEREO.CREATED_BY, PERSONALLISTHEADEREO.LAST_UPDATED_BY, PERSONALLISTHEADEREO.LAST_UPDATE_DATE, NULL REQ_BU_ID, PERSONALLISTHEADEREO.PERSON_ID PERSON_ID FROM POR_PERSONAL_LIST_HEADERS PERSONALLISTHEADEREO

SQL_Statement

SELECT

DISTINCT PUBLICLISTHEADEREO.PUBLIC_LIST_HEADER_ID,

PUBLICLISTHEADEREO.PUBLIC_LIST_NAME,

UPPER(PUBLICLISTHEADEREO.PUBLIC_LIST_NAME) LIST_NAME,

PUBLICLISTHEADEREO.DESCRIPTION,

'PUBLIC' SHOPPING_LIST_TYPE,

PUBLICLISTHEADEREO.IMAGE_URL,

PUBLICLISTHEADEREO.CREATION_DATE,

PUBLICLISTHEADEREO.CREATED_BY,

PUBLICLISTHEADEREO.LAST_UPDATED_BY,

PUBLICLISTHEADEREO.LAST_UPDATE_DATE,

IQ.REQ_BU_ID,

IQ.PERSON_ID

FROM

POR_PUBLIC_LIST_HEADERS_ALL_VL PUBLICLISTHEADEREO,

(

SELECT CZASSIGNMENTEO.ASSIGNMENT_ID,

USERPREF.REQ_BU_ID,

USERPREF.PERSON_ID

FROM POR_CONTENT_ZONES_ALL_B CONTENTZONEEO,

POR_CZ_SECURITY_ASSIGNMENTS CZSECURITYASSIGNMENTEO,

POR_CZ_ASSIGNMENTS CZASSIGNMENTEO,

POR_USER_PREFERENCES_ALL USERPREF,

FUN_BU_SERVICE_PROVIDERS BUSERVICEPROVIDERPEO

WHERE

BUSERVICEPROVIDERPEO.UPSTREAM_FUNCTION_ID = 120

AND BUSERVICEPROVIDERPEO.DOWNSTREAM_FUNCTION_ID = 110

AND BUSERVICEPROVIDERPEO.ACTIVE_FLAG = 'Y'

AND BUSERVICEPROVIDERPEO.CLIENT_BU_ORG_ID = USERPREF.REQ_BU_ID

AND CONTENTZONEEO.PRC_BU_ID = BUSERVICEPROVIDERPEO.PROVIDER_BU_ORG_ID

AND CONTENTZONEEO.USAGE = 'REQUISITIONING'

AND CONTENTZONEEO.CONTENT_ZONE_ID = CZSECURITYASSIGNMENTEO.CONTENT_ZONE_ID(+)

AND CONTENTZONEEO.CONTENT_ZONE_ID = CZASSIGNMENTEO.CONTENT_ZONE_ID

AND CZASSIGNMENTEO.ASSIGNMENT_TYPE = 'PUBLIC_LIST'

AND (USERPREF.TYPE = 'SSP' OR USERPREF.TYPE IS NULL)

AND ((( (CONTENTZONEEO.SECURITY_DIMENSION = 'SECURED_BY_BU') AND (CZSECURITYASSIGNMENTEO.REQ_BU_ID = USERPREF.REQ_BU_ID ) )

OR ((CONTENTZONEEO.SECURITY_DIMENSION = 'SECURED_BY_WORKER') AND (CZSECURITYASSIGNMENTEO.WORKER_ID = USERPREF.PERSON_ID ) )

OR ((CONTENTZONEEO.SECURITY_DIMENSION = 'SECURED_BY_DEL_LOC') AND (CZSECURITYASSIGNMENTEO.DELIVER_TO_LOCATION_ID = USERPREF.DELIVER_TO_LOCATION_ID ) ))

OR (CONTENTZONEEO.SECURITY_DIMENSION = 'AVAIL_ALL_REQ_BUS') )

) IQ

WHERE

PUBLICLISTHEADEREO.PUBLIC_LIST_HEADER_ID = IQ.ASSIGNMENT_ID

AND SYSDATE BETWEEN NVL(PUBLICLISTHEADEREO.START_DATE, SYSDATE) AND NVL(PUBLICLISTHEADEREO.END_DATE, SYSDATE)

UNION ALL

SELECT

DISTINCT PERSONALLISTHEADEREO.PERSONAL_LIST_HEADER_ID,

PERSONALLISTHEADEREO.PERSONAL_LIST_NAME,

UPPER(PERSONALLISTHEADEREO.PERSONAL_LIST_NAME) LIST_NAME,

NULL AS DESCRIPTION,

'PERSONAL' AS SHOPPING_LIST_TYPE,

PERSONALLISTHEADEREO.IMAGE_URL,

PERSONALLISTHEADEREO.CREATION_DATE,

PERSONALLISTHEADEREO.CREATED_BY,

PERSONALLISTHEADEREO.LAST_UPDATED_BY,

PERSONALLISTHEADEREO.LAST_UPDATE_DATE,

NULL REQ_BU_ID,

PERSONALLISTHEADEREO.PERSON_ID PERSON_ID

FROM POR_PERSONAL_LIST_HEADERS PERSONALLISTHEADEREO