   FROM ASSET_ASSOC
  WHERE UPPER(ENABLED) = 'Y'
 ;
-
-
-/* FMDR.ASSET_ASSOC_V_ALL (Enabled AND Disabled Associations) */
-CREATE OR REPLACE VIEW FMDR.ASSET_ASSOC_V_ALL 
+COMMENT ON  TABLE FMDR.ASSET_ASSOC_V
+               IS 'View for Enabled Asset Associations ONLY';
+
+
+/* FMDR.ASSET_ASSOC_ALL_V (Enabled AND Disabled Associations) */
+CREATE OR REPLACE VIEW FMDR.ASSET_ASSOC_ALL_V
 AS 
 SELECT ASSET_ASSOC_ID,
        GET_ASSET_TYPE_ID(LS_ASSET_ID) LS_TYPE_ASSET_ID,

          ASSET_TYPE_ASSET_ID,
          ASSET_ID
;


/* FMDR.ASSET_ASSOC_V (Enabled Associations ONLY) */
CREATE OR REPLACE VIEW FMDR.ASSET_ASSOC_V 
AS 
SELECT ASSET_ASSOC_ID,
       GET_ASSET_TYPE_ID(LS_ASSET_ID) LS_TYPE_ASSET_ID,

          AV.ASSET_ID,
          AP.ASSET_PROP_ID
;
COMMENT ON  TABLE FMDR.ASSET_PROP_V
               IS 'View for Active Assets with its Properties';


/* List Assets that are MDR Namespaces */
CREATE OR REPLACE VIEW FMDR.NAMESPACE_V
AS
SELECT ASSET_ID,
       ASSET_LABEL,
       ASSET_ACTIVATE_DT,
       ASSET_DEACTIVATE_DT,
       ASSET_DSC
  FROM ASSET
 WHERE ASSET_TYPE_ASSET_ID = 10001 -- NEW NAMESPACE ASSET ID
    OR ASSET_TYPE_ASSET_ID = 2 -- OLD NAMESPACE ASSET ID
 ORDER BY ASSET_ID
;
COMMENT ON  TABLE FMDR.NAMESPACE_V
               IS 'View for Assets that are MDR Namespaces';


/* Tree View WITHIN Asset */
/* Relationships BETWEEN Assets are in the Asset_Assoc_v */
/* Level 1 is already represented in the later levels,
   therefore, i'm using level > 1 */
/* nocycle prevents endless loop */
/* The attribute immediately after the prior keyword is the parent id */
CREATE OR REPLACE VIEW FMDR.ASSET_TREE_V
AS
 SELECT ASSET_ID, 
        ASSET_LABEL, 
        ASSET_NAMESPACE_ASSET_ID,
        ASSET_NAMESPACE_LABEL,
        ASSET_TYPE_ASSET_ID,
        ASSET_TYPE_LABEL,
        LEVEL AS TREE_LEVEL,
        SYS_CONNECT_BY_PATH(ASSET_LABEL, '^') AS PATH
   FROM ASSET_V 
  WHERE LEVEL > 1 
CONNECT BY NOCYCLE PRIOR ASSET_ID = ASSET_TYPE_ASSET_ID
  ORDER BY ASSET_NAMESPACE_LABEL, PATH
;
COMMENT ON  TABLE FMDR.ASSET_TREE_V
               IS 'View for Hierarchical Structure of Assets';


CREATE OR REPLACE VIEW FMDR.ASSET_RESOURCE_V
AS
SELECT ASSET_RESOURCE_ID,
       ASSET_RESOURCE_TYPE_ID,
       ASSET_VERSION_SEQ_ID,
       ASSET_ID,
       ASSET_VERSION,
       RESOURCE_NAME,
       RESOURCE_DSC,
       RESOURCE_FILE_NAME,
       STORAGE_CD,
       MIME_TYPE,
       RESOURCE_TEXT,
       AR.RESOURCE_XML.GETCLOBVAL() AS RESOURCE_XML,
       RESOURCE_CLOB,
       RESOURCE_BLOB,
       RESOURCE_URL,
       RELATIVE_RESOURCE_URL,
       RESOURCE_ACTIVATE_DT,
       RESOURCE_DEACTIVATE_DT
  FROM ASSET_RESOURCE AR
 WHERE RESOURCE_DEACTIVATE_DT > SYSDATE
    OR RESOURCE_DEACTIVATE_DT IS NULL
 ORDER BY ASSET_ID
;
COMMENT ON  TABLE FMDR.ASSET_RESOURCE_V
               IS 'View for Active Resources and Displays XML as Clob';


CREATE OR REPLACE VIEW FMDR.ASSET_SUMMARY_V
AS
SELECT AST.ASSET_ID,
       AST.ASSET_NAMESPACE_ASSET_ID NAMESPACE_ID,
       GET_ASSET_LABEL(AST.ASSET_NAMESPACE_ASSET_ID) NAMESPACE_LABEL,
       AST.ASSET_TYPE_ASSET_ID ASSET_TYPE_ID,
       GET_ASSET_LABEL(AST.ASSET_TYPE_ASSET_ID) ASSET_TYPE_LABEL,
       AST.ASSET_LABEL,
       AV.ASSET_VERSION,
       AV.ASSET_STATUS_ID,
       GET_LOOKUP_LABEL( AV.ASSET_STATUS_ID ) STATUS_LABEL,
       AR.ASSET_RESOURCE_ID RESOURCE_ID,
       AR.ASSET_RESOURCE_TYPE_ID RESOURCE_TYPE_ID,
       ATRT.ASSET_RESOURCE_CARDINALITY RESOURCE_CARDINALITY,
       ATRT.ASSET_RESOURCE_PARAM_NM RESOURCE_PARAM_NM,
       GET_RESOURCE_TYPE_LABEL( AR.ASSET_RESOURCE_TYPE_ID ) RESOURCE_TYPE_LABEL,
       AR.RESOURCE_NAME RESOURCE_LABEL,
       AR.STORAGE_CD,
       AR.MIME_TYPE,
       AR.RESOURCE_BLOB,
       AR.RESOURCE_CLOB,
       AR.RESOURCE_XML,
       AR.RESOURCE_XML.GETCLOBVAL() RESOURCE_XML_CLOB,
       AR.RESOURCE_TEXT,
       AR.RESOURCE_URL,
       AR.RELATIVE_RESOURCE_URL,
       AR.RESOURCE_INDEX,
       AR.UPDATE_USER_ID
  -- ANSI Style Join
  FROM ASSET AST
  JOIN ASSET_VERSION AV 
    ON AST.ASSET_ID = AV.ASSET_ID
  JOIN ASSET_RESOURCE AR
    ON AST.ASSET_ID = AR.ASSET_ID 
   AND AV.ASSET_VERSION = AR.ASSET_VERSION
  JOIN ASSET_TYPE_RESOURCE_TYPE ATRT
    ON AST.ASSET_TYPE_ASSET_ID = ATRT.ASSET_TYPE_ID
   AND AR.ASSET_RESOURCE_TYPE_ID = ATRT.ASSET_RESOURCE_TYPE_ID
;
COMMENT ON  TABLE FMDR.ASSET_SUMMARY_V
               IS 'View for Asset Summary';
/*
  -- ORACLE Style Join
  FROM asset ast,
    asset_version av,
    asset_resource ar,
    asset_type_resource_type atrt
  WHERE ast.asset_id   = av.asset_id
  AND ast.asset_id     = ar.asset_id
  AND av.asset_version = ar.asset_version
  AND ar.asset_resource_type_id = atrt.asset_resource_type_id
  AND atrt.asset_type_id = ast.asset_type_asset_id;
*/


/* ***** END Views ***** */
