BDNA Data Platform 5.4 July 2017 (Oracle) CAT_HW_LIFECYCLE_CALC - Hardware lifecylce table
|
View Definition:
SELECT CAT_HW_LIFECYCLE.CAT_HW_LIFECYCLE_ID,
CAT_HW_LIFECYCLE.CAT_HW_MODEL_ID,
CAT_HW_LIFECYCLE.INTRODUCTION,
CAT_HW_LIFECYCLE.INRODUCTION_EXCEPTION,
CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_START,
CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_END,
COALESCE(CAT_HW_LIFECYCLE.GENERAL_AVAILABILITY,CAT_HW_LIFECYCLE.INTRODUCTION) as GA,
COALESCE(CAT_HW_LIFECYCLE.GA_EXCEPTION,CAT_HW_LIFECYCLE.INRODUCTION_EXCEPTION) AS GA_EXCEPTION,
COALESCE(CAT_HW_LIFECYCLE.GA_RANGE_START, CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_START ) AS GA_RANGE_START ,
COALESCE(CAT_HW_LIFECYCLE.GA_RANGE_END,CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_END ) AS GA_RANGE_END,
CAT_HW_LIFECYCLE.LAST_AVAILABILITY,
CAT_HW_LIFECYCLE.LAST_AVAILABILITY_EXCEPTION,
CAT_HW_LIFECYCLE.LAST_AVAIL_RANGE_START,
CAT_HW_LIFECYCLE.LAST_AVAIL_RANGE_END,
CAT_HW_LIFECYCLE.OBSOLETE ,
CAT_HW_LIFECYCLE.OBSOLETE_EXCEPTION ,
CAT_HW_LIFECYCLE.OBSOLETE_RANGE_START ,
CAT_HW_LIFECYCLE.OBSOLETE_RANGE_END,
CAT_HW_LIFECYCLE.OBSOLETE as CALC_EOL,
CAT_HW_LIFECYCLE.OBSOLETE_EXCEPTION AS CALC_EOL_EXCEPTION,
CAT_HW_LIFECYCLE.OBSOLETE_RANGE_START AS CALC_EOL_RANGE_START ,
CAT_HW_LIFECYCLE.OBSOLETE_RANGE_END AS CALC_EOL_RANGE_END ,
CASE WHEN TRUNC(CAT_HW_LIFECYCLE.OBSOLETE - SYSDATE) > 0 THEN 'Technopedia EOL date not met yet'
WHEN TRUNC(CAT_HW_LIFECYCLE.OBSOLETE - SYSDATE) < 0 THEN 'Technopedia EOL Date'
END AS REASON_IS_SUPPORTED,
CASE WHEN TRUNC(CAT_HW_LIFECYCLE.OBSOLETE - SYSDATE) > 0 THEN 'yes' ELSE 'no' END as IS_SUPPORTED,
CAT_HW_LIFECYCLE.CREATE_DATE,
CAT_HW_LIFECYCLE.LAST_MODIFIED_DATE
FROM CAT_HW_LIFECYCLE
INNER JOIN CAT_HW_MODEL ON CAT_HW_MODEL.CAT_HW_MODEL_ID=CAT_HW_LIFECYCLE.CAT_HW_MODEL_ID
INNER JOIN CAT_MANUFACTURER ON CAT_MANUFACTURER.CAT_MANUFACTURER_ID=CAT_HW_MODEL.CAT_MANUFACTURER_ID
WHERE CAT_HW_LIFECYCLE.OBSOLETE IS NOT NULL
UNION ALL
SELECT
CAT_HW_LIFECYCLE.CAT_HW_LIFECYCLE_ID,
CAT_HW_LIFECYCLE.CAT_HW_MODEL_ID,
CAT_HW_LIFECYCLE.INTRODUCTION,
CAT_HW_LIFECYCLE.INRODUCTION_EXCEPTION,
CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_START,
CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_END,
COALESCE(CAT_HW_LIFECYCLE.GENERAL_AVAILABILITY,CAT_HW_LIFECYCLE.INTRODUCTION) as GA,
COALESCE(CAT_HW_LIFECYCLE.GA_EXCEPTION,CAT_HW_LIFECYCLE.INRODUCTION_EXCEPTION) AS GA_EXCEPTION,
COALESCE(CAT_HW_LIFECYCLE.GA_RANGE_START, CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_START ) AS GA_RANGE_START ,
COALESCE(CAT_HW_LIFECYCLE.GA_RANGE_END,CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_END ) AS GA_RANGE_END,
CAT_HW_LIFECYCLE.LAST_AVAILABILITY,
CAT_HW_LIFECYCLE.LAST_AVAILABILITY_EXCEPTION,
CAT_HW_LIFECYCLE.LAST_AVAIL_RANGE_START,
CAT_HW_LIFECYCLE.LAST_AVAIL_RANGE_END,
CAT_HW_LIFECYCLE.OBSOLETE ,
CAT_HW_LIFECYCLE.OBSOLETE_EXCEPTION ,
CAT_HW_LIFECYCLE.OBSOLETE_RANGE_START ,
CAT_HW_LIFECYCLE.OBSOLETE_RANGE_START,
CASE WHEN 99 =9999 THEN NULL
WHEN 99!=9999 THEN
ADD_MONTHS(COALESCE(CAT_HW_LIFECYCLE.GENERAL_AVAILABILITY,CAT_HW_LIFECYCLE.INTRODUCTION),99)
END as CALC_EOL,
COALESCE(CAT_HW_LIFECYCLE.GA_EXCEPTION,CAT_HW_LIFECYCLE.INRODUCTION_EXCEPTION) as CALC_EOL_EXCEPTION,
CASE WHEN 99 =9999 THEN NULL
WHEN 99!=9999 THEN
ADD_MONTHS(COALESCE(CAT_HW_LIFECYCLE.GA_RANGE_START, CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_START ),99)
END as CALC_EOL_RANGE_START,
CASE WHEN 99 =9999 THEN NULL
WHEN 99!=9999 THEN
ADD_MONTHS(COALESCE(CAT_HW_LIFECYCLE.GA_RANGE_END,CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_END ),99)
END as CALC_EOL_RANGE_END,
CASE WHEN 99 =9999 THEN NULL
WHEN TRUNC(ADD_MONTHS(COALESCE(CAT_HW_LIFECYCLE.GENERAL_AVAILABILITY,CAT_HW_LIFECYCLE.INTRODUCTION),99) - SYSDATE ) > 0 THEN
'Product is less than GA + 99 months'
WHEN TRUNC(ADD_MONTHS(COALESCE(CAT_HW_LIFECYCLE.GENERAL_AVAILABILITY,CAT_HW_LIFECYCLE.INTRODUCTION),99) - SYSDATE ) < 0 THEN
'Product is GA + 99 months'
WHEN TRUNC(ADD_MONTHS(COALESCE(CAT_HW_LIFECYCLE.GENERAL_AVAILABILITY,CAT_HW_LIFECYCLE.INTRODUCTION),99) - SYSDATE ) > 0 THEN NULL
ELSE NULL
END as REASON_IS_SUPPORTED,
CASE WHEN 99 =9999 THEN NULL
WHEN TRUNC(ADD_MONTHS(COALESCE(CAT_HW_LIFECYCLE.GENERAL_AVAILABILITY,CAT_HW_LIFECYCLE.INTRODUCTION),99) - SYSDATE ) > 0 THEN 'yes' ELSE 'no' END as IS_SUPPORTED,
CAT_HW_LIFECYCLE.CREATE_DATE,
CASE WHEN TO_DATE('01-JAN-1900', 'DD-MON-YYYY') = TO_DATE('01-JAN-1900','DD-MON-YYYY') THEN
CAT_HW_LIFECYCLE.LAST_MODIFIED_DATE
WHEN TO_DATE('01-JAN-1900', 'DD-MON-YYYY') >= CAT_HW_LIFECYCLE.LAST_MODIFIED_DATE THEN
TO_DATE('01-JAN-1900', 'DD-MON-YYYY')
ELSE CAT_HW_LIFECYCLE.LAST_MODIFIED_DATE
END LAST_MODIFIED_DATE
FROM CAT_HW_LIFECYCLE
INNER JOIN CAT_HW_MODEL ON CAT_HW_MODEL.CAT_HW_MODEL_ID=CAT_HW_LIFECYCLE.CAT_HW_MODEL_ID
INNER JOIN CAT_MANUFACTURER ON CAT_MANUFACTURER.CAT_MANUFACTURER_ID=CAT_HW_MODEL.CAT_MANUFACTURER_ID
WHERE COALESCE(NULLIF(TO_CHAR(GENERAL_AVAILABILITY,'YYYY-MM-DD'),'1900-01-01'),NULLIF(TO_CHAR(INTRODUCTION,'YYYY-MM-DD'),'1900-01-01')) IS NOT NULL AND CAT_HW_LIFECYCLE.OBSOLETE IS NULL
UNION ALL
SELECT
CAT_HW_LIFECYCLE.CAT_HW_LIFECYCLE_ID,
CAT_HW_LIFECYCLE.CAT_HW_MODEL_ID,
CAT_HW_LIFECYCLE.INTRODUCTION,
CAT_HW_LIFECYCLE.INRODUCTION_EXCEPTION,
CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_START,
CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_END,
COALESCE(CAT_HW_LIFECYCLE.GENERAL_AVAILABILITY,CAT_HW_LIFECYCLE.INTRODUCTION) as GA,
COALESCE(CAT_HW_LIFECYCLE.GA_EXCEPTION,CAT_HW_LIFECYCLE.INRODUCTION_EXCEPTION) AS GA_EXCEPTION,
COALESCE(CAT_HW_LIFECYCLE.GA_RANGE_START, CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_START ) AS GA_RANGE_START ,
COALESCE(CAT_HW_LIFECYCLE.GA_RANGE_END,CAT_HW_LIFECYCLE.INTRODUCTION_RANGE_END ) AS GA_RANGE_END,
CAT_HW_LIFECYCLE.LAST_AVAILABILITY,
CAT_HW_LIFECYCLE.LAST_AVAILABILITY_EXCEPTION,
CAT_HW_LIFECYCLE.LAST_AVAIL_RANGE_START,
CAT_HW_LIFECYCLE.LAST_AVAIL_RANGE_END,
CAT_HW_LIFECYCLE.OBSOLETE ,
CAT_HW_LIFECYCLE.OBSOLETE_EXCEPTION ,
CAT_HW_LIFECYCLE.OBSOLETE_RANGE_START ,
CAT_HW_LIFECYCLE.OBSOLETE_RANGE_START,
NULL AS CALC_EOL,
NULL AS CALC_EOL_EXCEPTION,
NULL AS CALC_EOL_RANGE_START,
NULL AS CALC_EOL_RANGE_END,
'No GA or EOL Date' as REASON_IS_SUPPORTED,
'Unknown' as IS_SUPPORTED,
CAT_HW_LIFECYCLE.CREATE_DATE,
CAT_HW_LIFECYCLE.LAST_MODIFIED_DATE
FROM CAT_HW_LIFECYCLE
INNER JOIN CAT_HW_MODEL ON CAT_HW_MODEL.CAT_HW_MODEL_ID=CAT_HW_LIFECYCLE.CAT_HW_MODEL_ID
INNER JOIN CAT_MANUFACTURER ON CAT_MANUFACTURER.CAT_MANUFACTURER_ID=CAT_HW_MODEL.CAT_MANUFACTURER_ID
WHERE COALESCE(NULLIF(TO_CHAR(GENERAL_AVAILABILITY,'YYYY-MM-DD'),'1900-01-01'),NULLIF(TO_CHAR(INTRODUCTION,'YYYY-MM-DD'),'1900-01-01')) IS NULL AND CAT_HW_LIFECYCLE.OBSOLETE IS NULL
Possibly Referenced Tables/Views: