REM +======================================================================+
REM | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM
REM NAME
REM     afsvcv.sql  -  Script to create Generic Services Components Views
REM +======================================================================+

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

SET VERIFY OFF
DEFINE hdr = "$Header: afsvcv.sql 26.2 2003/03/28 21:30:03 ankung ship $"

create or replace force view FND_SVC_COMP_PARAMS_VL ( 
    ROW_ID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER,
    PARAMETER_ID,
    PARAMETER_NAME,
    COMPONENT_TYPE,
    DEFAULT_PARAMETER_VALUE,
    REQUIRED_FLAG,
    ALLOW_RELOAD_FLAG,
    ENCRYPTED_FLAG,
    CUSTOMIZATION_LEVEL,
    DISPLAY_NAME,
    DESCRIPTION
  ) as select
    B.ROWID ROW_ID,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.OBJECT_VERSION_NUMBER,
    B.PARAMETER_ID,
    B.PARAMETER_NAME,
    B.COMPONENT_TYPE,
    B.DEFAULT_PARAMETER_VALUE,
    B.REQUIRED_FLAG,
    B.ALLOW_RELOAD_FLAG,
    B.ENCRYPTED_FLAG,
    B.CUSTOMIZATION_LEVEL,
    T.DISPLAY_NAME,
    T.DESCRIPTION
  from FND_SVC_COMP_PARAMS_TL T, FND_SVC_COMP_PARAMS_B B
  where B.PARAMETER_ID = T.PARAMETER_ID
  and T.LANGUAGE = userenv('LANG')
/

create or replace force view FND_SVC_COMP_TYPES_VL ( 
    ROW_ID,
    COMPONENT_TYPE,
    COMPONENT_CLASS_NAME,
    CONFIG_UI_REGION,
    CUSTOMIZATION_LEVEL,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER,
    DISPLAY_NAME,
    DESCRIPTION
  ) as select
    B.ROWID ROW_ID,
    B.COMPONENT_TYPE,
    B.COMPONENT_CLASS_NAME,
    B.CONFIG_UI_REGION,
    B.CUSTOMIZATION_LEVEL,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.OBJECT_VERSION_NUMBER,
    T.DISPLAY_NAME,
    T.DESCRIPTION
  from FND_SVC_COMP_TYPES_TL T, FND_SVC_COMP_TYPES_B B
  where B.COMPONENT_TYPE = T.COMPONENT_TYPE
  and T.LANGUAGE = userenv('LANG')
/

PROMPT Creating View 'FND_SVC_COMP_TYPE_EVENTS_V'
CREATE OR REPLACE FORCE VIEW FND_SVC_COMP_TYPE_EVENTS_V
 (NAME
 ,DISPLAY_NAME
 ,DESCRIPTION
 ,COMPONENT_TYPE)
 AS SELECT EV.NAME,
 EV.DISPLAY_NAME,
 EV.DESCRIPTION,
 CTB.COMPONENT_TYPE
FROM
 WF_EVENTS_VL EV,
 FND_SVC_COMP_TYPES_B CTB
  WHERE EV.NAME LIKE CTB.COMPONENT_CLASS_NAME || '.%'
/

PROMPT Creating View 'FND_SVC_COMP_REQUESTS_H_V'
CREATE OR REPLACE FORCE VIEW FND_SVC_COMP_REQUESTS_H_V
 (COMPLETION_DATE
 ,COMPONENT_ID
 ,COMPONENT_NAME
 ,COMPONENT_STATUS
 ,COMPONENT_TYPE
 ,CONTAINER_NAME
 ,CONTAINER_TYPE
 ,CREATED_BY
 ,EVENT_NAME
 ,EVENT_PARAMS
 ,OBJECT_VERSION_NUMBER
 ,REQUEST_HISTORY_ID
 ,REQUEST_STATUS
 ,REQUESTED_BY_USER)
 AS SELECT CRH.COMPLETION_DATE,
CRH.COMPONENT_ID,
CRH.COMPONENT_NAME,
CRH.COMPONENT_STATUS,
CRH.COMPONENT_TYPE,
CRH.CONTAINER_NAME,
CRH.CONTAINER_TYPE,
CRH.CREATED_BY,
CRH.EVENT_NAME,
CRH.EVENT_PARAMS,
CRH.OBJECT_VERSION_NUMBER,
CRH.REQUEST_HISTORY_ID,
CRH.REQUEST_STATUS,
CRH.REQUESTED_BY_USER
FROM
FND_SVC_COMP_REQUESTS_H CRH
/

PROMPT Creating View 'FND_SVC_COMP_PARAM_VALS_V'
CREATE OR REPLACE FORCE VIEW FND_SVC_COMP_PARAM_VALS_V
 (ALLOW_RELOAD_FLAG
 ,COMPONENT_ID
 ,COMPONENT_PARAMETER_ID
 ,CREATED_BY
 ,CUSTOMIZATION_LEVEL
 ,DEFAULT_PARAMETER_VALUE
 ,ENCRYPTED_FLAG
 ,OBJECT_VERSION_NUMBER
 ,PARAMETER_DESCRIPTION
 ,PARAMETER_DISPLAY_NAME
 ,PARAMETER_NAME
 ,PARAMETER_ID
 ,PARAMETER_VALUE
 ,REQUIRED_FLAG)
 AS SELECT CPB.ALLOW_RELOAD_FLAG,
 CPV.COMPONENT_ID,
 CPV.COMPONENT_PARAMETER_ID,
 CPV.CREATED_BY,
 CPV.CUSTOMIZATION_LEVEL,
 CPB.DEFAULT_PARAMETER_VALUE,
 CPB.ENCRYPTED_FLAG,
 CPV.OBJECT_VERSION_NUMBER,
 CPVL.DESCRIPTION PARAMETER_DESCRIPTION,
 CPVL.DISPLAY_NAME PARAMETER_DISPLAY_NAME,
 CPB.PARAMETER_NAME,
 CPV.PARAMETER_ID,
 CPV.PARAMETER_VALUE,
 CPB.REQUIRED_FLAG
FROM
 FND_SVC_COMP_PARAM_VALS CPV,
 FND_SVC_COMP_PARAMS_VL CPVL,
 FND_SVC_COMP_PARAMS_B CPB
  WHERE CPV.PARAMETER_ID = CPVL.PARAMETER_ID
AND CPV.PARAMETER_ID = CPB.PARAMETER_ID
/

PROMPT Creating View 'FND_SVC_COMPONENTS_V'
CREATE OR REPLACE FORCE VIEW FND_SVC_COMPONENTS_V
 (APPLICATION_ID
 ,COMPONENT_CLASS_NAME
 ,COMPONENT_ID
 ,COMPONENT_NAME
 ,COMPONENT_STATUS
 ,COMPONENT_STATUS_DISPLAY_NAME
 ,COMPONENT_STATUS_INFO
 ,COMPONENT_TYPE
 ,COMPONENT_TYPE_DISPLAY_NAME
 ,CONCURRENT_QUEUE_DISPLAY_NAME
 ,CONCURRENT_QUEUE_ID
 ,CONCURRENT_QUEUE_NAME
 ,CONTAINER_TYPE
 ,CONTAINER_TYPE_DISPLAY_NAME
 ,CORRELATION_ID
 ,CREATED_BY
 ,CUSTOMIZATION_LEVEL
 ,INBOUND_AGENT_DISPLAY_NAME
 ,INBOUND_AGENT_NAME
 ,MAX_IDLE_TIME
 ,OUTBOUND_AGENT_DISPLAY_NAME
 ,OBJECT_VERSION_NUMBER
 ,OUTBOUND_AGENT_NAME
 ,STANDALONE_CONTAINER_NAME
 ,STARTUP_MODE
 ,STARTUP_MODE_DISPLAY_NAME)
 AS SELECT CQV.APPLICATION_ID,
 CTV.COMPONENT_CLASS_NAME,
 C.COMPONENT_ID,
 C.COMPONENT_NAME,
 C.COMPONENT_STATUS, 
 (SELECT MEANING 
   FROM WF_LOOKUPS 
   WHERE LOOKUP_CODE = C.COMPONENT_STATUS 
   AND LOOKUP_TYPE='WF_SVC_COMP_STATUS') COMPONENT_STATUS_DISPLAY_NAME,
 C.COMPONENT_STATUS_INFO,
 C.COMPONENT_TYPE,
 CTV.DISPLAY_NAME COMPONENT_TYPE_DISPLAY_NAME,
 CQV.USER_CONCURRENT_QUEUE_NAME  CONCURRENT_QUEUE_DISPLAY_NAME,
 C.CONCURRENT_QUEUE_ID,
 CQV.CONCURRENT_QUEUE_NAME,
 C.CONTAINER_TYPE,
 (SELECT MEANING 
   FROM WF_LOOKUPS 
   WHERE LOOKUP_CODE = C.CONTAINER_TYPE 
   AND LOOKUP_TYPE='WF_SVC_CONTAINER_TYPE') CONTAINER_TYPE_DISPLAY_NAME,
 C.CORRELATION_ID,
 C.CREATED_BY,
 C.CUSTOMIZATION_LEVEL,
 (SELECT DISPLAY_NAME 
   FROM WF_AGENTS 
   WHERE NAME = C.INBOUND_AGENT_NAME AND SYSTEM_GUID = WF_CORE.TRANSLATE('WF_SYSTEM_GUID')) INBOUND_AGENT_DISPLAY_NAME,
 C.INBOUND_AGENT_NAME,
 C.MAX_IDLE_TIME,
 (SELECT DISPLAY_NAME 
   FROM WF_AGENTS 
   WHERE NAME = C.OUTBOUND_AGENT_NAME AND SYSTEM_GUID = WF_CORE.TRANSLATE('WF_SYSTEM_GUID')) OUTBOUND_AGENT_DISPLAY_NAME,
 C.OBJECT_VERSION_NUMBER,
 C.OUTBOUND_AGENT_NAME,
 C.STANDALONE_CONTAINER_NAME,
 C.STARTUP_MODE,
 (SELECT MEANING 
   FROM WF_LOOKUPS 
   WHERE LOOKUP_CODE = C.STARTUP_MODE 
   AND LOOKUP_TYPE='WF_SVC_COMP_STARTUP_MODE') STARTUP_MODE_DISPLAY_NAME
FROM 
 FND_SVC_COMPONENTS C,
 FND_SVC_COMP_TYPES_VL CTV,
 FND_CONCURRENT_QUEUES_VL CQV
  WHERE C.COMPONENT_TYPE  = CTV.COMPONENT_TYPE
AND C.CONCURRENT_QUEUE_ID = CQV.CONCURRENT_QUEUE_ID (+)
AND C.APPLICATION_ID  = CQV.APPLICATION_ID (+)
/

PROMPT Creating View 'FND_SVC_COMP_REQUESTS_V'
CREATE OR REPLACE FORCE VIEW FND_SVC_COMP_REQUESTS_V
 (COMPONENT_ID
 ,COMPONENT_REQUEST_ID
 ,CREATED_BY
 ,EVENT_DATE
 ,EVENT_DISPLAY_NAME
 ,EVENT_FREQUENCY
 ,EVENT_NAME
 ,EVENT_PARAMS
 ,FAILURES
 ,INTERVAL
 ,JOB_ID
 ,LAST_DATE
 ,NEXT_DATE
 ,OBJECT_VERSION_NUMBER
 ,REQUESTED_BY_USER
 ,THIS_DATE
 ,WHAT)
 AS SELECT CR.COMPONENT_ID,
 CR.COMPONENT_REQUEST_ID,
 CR.CREATED_BY,
 CR.EVENT_DATE,
 EV.DISPLAY_NAME EVENT_DISPLAY_NAME,
 CR.EVENT_FREQUENCY,
 CR.EVENT_NAME,
 CR.EVENT_PARAMS,
 WAJ.FAILURES,
 WAJ.INTERVAL,
 CR.JOB_ID,
 WAJ.LAST_DATE,
 WAJ.NEXT_DATE,
 CR.OBJECT_VERSION_NUMBER,
 CR.REQUESTED_BY_USER,
 WAJ.THIS_DATE,
 WAJ.WHAT
FROM
 FND_SVC_COMP_REQUESTS CR,
 WF_EVENTS_VL EV,
 WF_ALL_JOBS WAJ
  WHERE CR.EVENT_NAME = EV.NAME 
AND CR.JOB_ID = WAJ.JOB
/


COMMENT ON TABLE FND_SVC_COMP_TYPE_EVENTS_V IS 'FND_SVC_COMP_TYPE_EVENTS_V'
/

COMMENT ON COLUMN FND_SVC_COMP_TYPE_EVENTS_V.NAME IS 'NAME'
/

COMMENT ON COLUMN FND_SVC_COMP_TYPE_EVENTS_V.DESCRIPTION IS 'DESCRIPTION'
/

COMMENT ON COLUMN FND_SVC_COMP_TYPE_EVENTS_V.COMPONENT_TYPE IS 'COMPONENT_TYPE'
/

COMMENT ON TABLE FND_SVC_COMP_REQUESTS_H_V IS 'FND_SVC_COMP_REQUESTS_H_V'
/

COMMENT ON TABLE FND_SVC_COMP_PARAM_VALS_V IS 'FND_SVC_COMP_PARAM_VALS_V'
/

COMMENT ON TABLE FND_SVC_COMPONENTS_V IS 'FND_SVC_COMPONENTS_V'
/

COMMENT ON TABLE FND_SVC_COMP_REQUESTS_V IS 'FND_SVC_COMP_REQUESTS_V'
/

-- M:\sacsharm\fndcpsvc2.vw
--
-- Generated for Oracle 8.1 on Thu Oct 31  19:57:10 2002 by Server Generator 6.5.82.2.0
 

PROMPT Creating View 'FND_CONCURRENT_QUEUES_VL'
CREATE OR REPLACE FORCE VIEW FND_CONCURRENT_QUEUES_VL
 (ROW_ID
 ,RESOURCE_CONSUMER_GROUP
 ,APPLICATION_ID
 ,CONCURRENT_QUEUE_ID
 ,CONCURRENT_QUEUE_NAME
 ,LAST_UPDATE_DATE
 ,LAST_UPDATED_BY
 ,CREATION_DATE
 ,CREATED_BY
 ,LAST_UPDATE_LOGIN
 ,PROCESSOR_APPLICATION_ID
 ,CONCURRENT_PROCESSOR_ID
 ,MAX_PROCESSES
 ,RUNNING_PROCESSES
 ,CACHE_SIZE
 ,MIN_PROCESSES
 ,TARGET_PROCESSES
 ,TARGET_NODE
 ,TARGET_QUEUE
 ,SLEEP_SECONDS
 ,CONTROL_CODE
 ,DIAGNOSTIC_LEVEL
 ,MANAGER_TYPE
 ,NODE_NAME
 ,NODE_NAME2
 ,OS_QUEUE
 ,OS_QUEUE2
 ,DATA_GROUP_ID
 ,RESTART_TYPE
 ,RESTART_INTERVAL
 ,ATTRIBUTE_CATEGORY
 ,ATTRIBUTE
 ,ATTRIBUTE1
 ,ATTRIBUTE2
 ,ATTRIBUTE3
 ,ATTRIBUTE4
 ,ATTRIBUTE5
 ,ATTRIBUTE6
 ,ATTRIBUTE7
 ,ATTRIBUTE8
 ,ATTRIBUTE9
 ,ATTRIBUTE10
 ,ATTRIBUTE11
 ,ATTRIBUTE12
 ,ENABLED_FLAG
 ,DESCRIPTION
 ,USER_CONCURRENT_QUEUE_NAME)
 AS SELECT B.ROWID ROW_ID,
    B.RESOURCE_CONSUMER_GROUP,
    B.APPLICATION_ID,
    B.CONCURRENT_QUEUE_ID,
    B.CONCURRENT_QUEUE_NAME,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.PROCESSOR_APPLICATION_ID,
    B.CONCURRENT_PROCESSOR_ID,
    B.MAX_PROCESSES,
    B.RUNNING_PROCESSES,
    B.CACHE_SIZE,
    B.MIN_PROCESSES,
    B.TARGET_PROCESSES,
    B.TARGET_NODE,
    B.TARGET_QUEUE,
    B.SLEEP_SECONDS,
    B.CONTROL_CODE,
    B.DIAGNOSTIC_LEVEL,
    B.MANAGER_TYPE,
    B.NODE_NAME,
    B.NODE_NAME2,
    B.OS_QUEUE,
    B.OS_QUEUE2,
    B.DATA_GROUP_ID,
    B.RESTART_TYPE,
    B.RESTART_INTERVAL,
    B.ATTRIBUTE_CATEGORY,
    B.ATTRIBUTE,
    B.ATTRIBUTE1,
    B.ATTRIBUTE2,
    B.ATTRIBUTE3,
    B.ATTRIBUTE4,
    B.ATTRIBUTE5,
    B.ATTRIBUTE6,
    B.ATTRIBUTE7,
    B.ATTRIBUTE8,
    B.ATTRIBUTE9,
    B.ATTRIBUTE10,
    B.ATTRIBUTE11,
    B.ATTRIBUTE12,
    B.ENABLED_FLAG,
    T.DESCRIPTION,
    T.USER_CONCURRENT_QUEUE_NAME
  from FND_CONCURRENT_QUEUES_TL T, FND_CONCURRENT_QUEUES B
  WHERE B.APPLICATION_ID = T.APPLICATION_ID
  and B.CONCURRENT_QUEUE_ID = T.CONCURRENT_QUEUE_ID
  and T.LANGUAGE = userenv('LANG')
/

commit;
exit;