BDNA Data Platform 5.4 July 2017 (Oracle) CAT_SW_REL_LIFECYCLE_CALC - Software release lifecylce table
|
View Definition:
SELECT CAT_SW_REL_LIFECYCLE.CAT_SW_REL_LIFECYCLE_ID,CAT_SW_REL_LIFECYCLE.CAT_SW_RELEASE_ID,GENERAL_AVAILABILITY AS GA,GA_EXCEPTION,GA_RANGE_START,GA_RANGE_END,COALESCE(END_OF_LIFE,OBSOLETE) AS EOL,
END_OF_LIFE_EXCEPTION,END_OF_LIFE_RANGE_START,END_OF_LIFE_RANGE_END,END_OF_LIFE_SUPPORT_LEVEL,OBSOLETE,OBSOLETE_EXCEPTION,OBSOLETE_RANGE_START,
OBSOLETE_RANGE_END,OBSOLETE_SUPPORT_LEVEL,COALESCE(END_OF_LIFE,OBSOLETE) AS CALC_EOL,COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG) AS DISCONDESUPP,
CAT_SW_VERSION.VERSION_ORDER,ORDER1.MAX_VERSION_ORDER,ORDER1.MAX_VERSION_ORDER-CAT_SW_VERSION.VERSION_ORDER AS CALC_DIFF_ORDER,
CASE
WHEN LOWER(COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG))='yes'
THEN 'Technopedia flagged this as discontinued/de-supported'
WHEN TRUNC(COALESCE(END_OF_LIFE,OBSOLETE) -SYSDATE) > 0 THEN 'Technopedia EOL date not met yet'
ELSE 'Technopedia EOL Date'
END AS REASON_IS_SUPPORTED,
CASE
WHEN LOWER(COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG))='yes'
THEN 'no'
WHEN TRUNC(COALESCE(END_OF_LIFE,OBSOLETE) -SYSDATE) > 0
THEN 'yes'
ELSE 'no'
END AS IS_SUPPORTED,
CAT_SW_REL_LIFECYCLE.CREATE_DATE,
CAT_SW_REL_LIFECYCLE.LAST_MODIFIED_DATE
FROM CAT_SW_REL_LIFECYCLE INNER JOIN CAT_SW_RELEASE ON CAT_SW_RELEASE.CAT_SW_RELEASE_ID=CAT_SW_REL_LIFECYCLE.CAT_SW_RELEASE_ID
INNER JOIN CAT_SW_VERSION ON CAT_SW_VERSION.CAT_SW_VERSION_ID=CAT_SW_RELEASE.CAT_SW_VERSION_ID
INNER JOIN
(SELECT CAT_SW_PRODUCT_ID,
MAX(VERSION_ORDER) AS MAX_VERSION_ORDER
FROM CAT_SW_VERSION
GROUP BY CAT_SW_PRODUCT_ID
)ORDER1
ON ORDER1.CAT_SW_PRODUCT_ID=CAT_SW_VERSION.CAT_SW_PRODUCT_ID
WHERE (CAT_SW_REL_LIFECYCLE.END_OF_LIFE IS NOT NULL OR CAT_SW_REL_LIFECYCLE.OBSOLETE IS NOT NULL)
UNION ALL
SELECT CAT_SW_REL_LIFECYCLE.CAT_SW_REL_LIFECYCLE_ID,CAT_SW_REL_LIFECYCLE.CAT_SW_RELEASE_ID,GENERAL_AVAILABILITY AS GA,GA_EXCEPTION,GA_RANGE_START,GA_RANGE_END,COALESCE(END_OF_LIFE,OBSOLETE) AS EOL,
END_OF_LIFE_EXCEPTION,END_OF_LIFE_RANGE_START,END_OF_LIFE_RANGE_END,END_OF_LIFE_SUPPORT_LEVEL,OBSOLETE,OBSOLETE_EXCEPTION,OBSOLETE_RANGE_START,
OBSOLETE_RANGE_END,OBSOLETE_SUPPORT_LEVEL,
CASE WHEN 47 =9999 THEN NULL
WHEN 47 != 9999 THEN ADD_MONTHS(GENERAL_AVAILABILITY,47)
END AS CALC_EOL,
COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG) AS DISCONDESUPP,
CAT_SW_VERSION.VERSION_ORDER,ORDER1.MAX_VERSION_ORDER,ORDER1.MAX_VERSION_ORDER-CAT_SW_VERSION.VERSION_ORDER AS CALC_DIFF_ORDER,
CASE
WHEN LOWER(COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG))='yes' THEN
'Technopedia flagged this as discontinued/de-supported'
WHEN ( 47 != 9999 AND TRUNC(ADD_MONTHS(GENERAL_AVAILABILITY,47) -SYSDATE) > 0) THEN 'Product is less than GA + 47 months'
WHEN ( 47 != 9999 AND TRUNC(ADD_MONTHS(GENERAL_AVAILABILITY,47) -SYSDATE) < 0) THEN 'Product is GA + 47 months'
WHEN 47 =9999 THEN NULL
ELSE NULL
END AS REASON_IS_SUPPORTED,
CASE
WHEN LOWER(COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG))='yes' THEN 'no'
WHEN ( 47 != 9999 AND TRUNC(ADD_MONTHS(GENERAL_AVAILABILITY,47) -SYSDATE) > 0) THEN 'yes'
ELSE 'no'
END AS IS_SUPPORTED,
CAT_SW_REL_LIFECYCLE.CREATE_DATE,
CASE WHEN TO_DATE('01-JAN-1900', 'DD-MON-YYYY') = TO_DATE('01-JAN-1900','DD-MON-YYYY') THEN
CAT_SW_REL_LIFECYCLE.LAST_MODIFIED_DATE
WHEN TO_DATE('01-JAN-1900', 'DD-MON-YYYY') >= CAT_SW_REL_LIFECYCLE.LAST_MODIFIED_DATE THEN
TO_DATE('01-JAN-1900', 'DD-MON-YYYY')
ELSE CAT_SW_REL_LIFECYCLE.LAST_MODIFIED_DATE
END LAST_MODIFIED_DATE
FROM CAT_SW_REL_LIFECYCLE INNER JOIN CAT_SW_RELEASE ON CAT_SW_RELEASE.CAT_SW_RELEASE_ID=CAT_SW_REL_LIFECYCLE.CAT_SW_RELEASE_ID
INNER JOIN CAT_SW_VERSION ON CAT_SW_VERSION.CAT_SW_VERSION_ID=CAT_SW_RELEASE.CAT_SW_VERSION_ID
INNER JOIN
(SELECT CAT_SW_PRODUCT_ID,
MAX(VERSION_ORDER) AS MAX_VERSION_ORDER
FROM CAT_SW_VERSION
GROUP BY CAT_SW_PRODUCT_ID
)ORDER1
ON ORDER1.CAT_SW_PRODUCT_ID=CAT_SW_VERSION.CAT_SW_PRODUCT_ID
WHERE NULLIF(TO_CHAR(CAT_SW_REL_LIFECYCLE.GENERAL_AVAILABILITY,'YYYY-MM-DD'),'1900-01-01') IS NOT NULL
AND CAT_SW_REL_LIFECYCLE.END_OF_LIFE IS NULL
AND CAT_SW_REL_LIFECYCLE.OBSOLETE IS NULL
UNION ALL
SELECT CAT_SW_REL_LIFECYCLE.CAT_SW_REL_LIFECYCLE_ID,CAT_SW_REL_LIFECYCLE.CAT_SW_RELEASE_ID,GENERAL_AVAILABILITY AS GA,GA_EXCEPTION,GA_RANGE_START,GA_RANGE_END,COALESCE(END_OF_LIFE,OBSOLETE) AS EOL,
END_OF_LIFE_EXCEPTION,END_OF_LIFE_RANGE_START,END_OF_LIFE_RANGE_END,END_OF_LIFE_SUPPORT_LEVEL,OBSOLETE,OBSOLETE_EXCEPTION,OBSOLETE_RANGE_START,
OBSOLETE_RANGE_END,OBSOLETE_SUPPORT_LEVEL,NULL AS CALC_EOL,COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG) AS DISCONDESUPP,
CAT_SW_VERSION.VERSION_ORDER,ORDER1.MAX_VERSION_ORDER,ORDER1.MAX_VERSION_ORDER-CAT_SW_VERSION.VERSION_ORDER AS CALC_DIFF_ORDER,
CASE
WHEN LOWER(COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG))='yes' THEN
'Technopedia flagged this as discontinued/de-supported'
WHEN 7 =9999 THEN NULL
WHEN ORDER1.MAX_VERSION_ORDER-CAT_SW_VERSION.VERSION_ORDER <= 7 THEN 'Less than 7 versions apart from most recent version'
WHEN ORDER1.MAX_VERSION_ORDER-CAT_SW_VERSION.VERSION_ORDER >= 7 THEN 'More than 7 versions apart from most recent version'
END AS REASON_IS_SUPPORTED,
CASE
WHEN LOWER(COALESCE(CAT_SW_RELEASE.DISCONTINUEDFLAG,CAT_SW_RELEASE.DESUPFLAG))='yes' THEN 'no'
WHEN 7 =9999 THEN NULL
WHEN (7 !=9999 AND ORDER1.MAX_VERSION_ORDER-CAT_SW_VERSION.VERSION_ORDER <= 7) THEN 'yes'
ELSE 'no'
END IS_SUPPORTED,
CAT_SW_REL_LIFECYCLE.CREATE_DATE,
CASE WHEN TO_DATE('01-JAN-1900', 'DD-MON-YYYY') = TO_DATE('01-JAN-1900','DD-MON-YYYY') THEN
CAT_SW_REL_LIFECYCLE.LAST_MODIFIED_DATE
WHEN TO_DATE('01-JAN-1900', 'DD-MON-YYYY') >= CAT_SW_REL_LIFECYCLE.LAST_MODIFIED_DATE THEN
TO_DATE('01-JAN-1900', 'DD-MON-YYYY')
ELSE CAT_SW_REL_LIFECYCLE.LAST_MODIFIED_DATE
END LAST_MODIFIED_DATE
FROM CAT_SW_REL_LIFECYCLE INNER JOIN CAT_SW_RELEASE ON CAT_SW_RELEASE.CAT_SW_RELEASE_ID=CAT_SW_REL_LIFECYCLE.CAT_SW_RELEASE_ID
INNER JOIN CAT_SW_VERSION ON CAT_SW_VERSION.CAT_SW_VERSION_ID=CAT_SW_RELEASE.CAT_SW_VERSION_ID
INNER JOIN
(SELECT CAT_SW_PRODUCT_ID,
MAX(VERSION_ORDER) AS MAX_VERSION_ORDER
FROM CAT_SW_VERSION
GROUP BY CAT_SW_PRODUCT_ID
)ORDER1
ON ORDER1.CAT_SW_PRODUCT_ID =CAT_SW_VERSION.CAT_SW_PRODUCT_ID
WHERE NULLIF(TO_CHAR(CAT_SW_REL_LIFECYCLE.GENERAL_AVAILABILITY,'YYYY-MM-DD'),'1900-01-01') IS NULL
AND CAT_SW_REL_LIFECYCLE.END_OF_LIFE IS NULL
AND CAT_SW_REL_LIFECYCLE.OBSOLETE IS NULL
Possibly Referenced Tables/Views: