BDNA Data Platform 5.4 July 2017 (Oracle) CAT_SW_LIC_RELEASE - The list of software product/release with all software license attributes attached to them
|
View Definition:
SELECT vw.CAT_SW_LIC_RELEASE_ID, vw.CREATE_DATE, vw.LAST_MODIFIED_DATE, vw.CAT_MANUFACTURER_ID, vw.CAT_SW_PRODUCT_ID, vw.CAT_SW_EDITION_ID, vw.CAT_SW_VERSION_GROUP_ID, vw.CAT_SW_VERSION_ID, vw.CAT_SW_RELEASE_ID, vw.CAT_PLATFORMS_ID, prod_tax.CAT_TAXONOMY2012_ID, vw.VENDOR_PRODUCT_NAME, vw.VENDOR_UNIT_OF_MEASURE, vw.LICENSE_TYPES, vw.LICENSE_TYPES_ID, vw.LICENSE_TYPES_OTHER, vw.LICENSE_METRICS, vw.LICENSE_METRICS_ID, vw.UNIT_PACK, vw.UNIT_COUNT_MIN, vw.UNIT_COUNT_MAX, vw.LICENSE_TERM, vw.LICENSE_INFORMATION_OTHER, vw.PERIOD_VALUE, vw.PERIOD_UNIT, vw.MAINT_PEROID_VALUE, vw.MAINT_PERIOD_VALUE, vw.MAINT_PEROID_UNIT, vw.MAINT_PERIOD_UNIT, vw.ENVIRONMENTS, vw.ENVIRONMENTS_ID, vw.ENVIRONMENTS_OTHER, vw.DOWNGRADE_VERSION_AUTH, vw.DOWNGRADE_VERSION_INCR, vw.DOWNGRADE_EDITION_AUTH, vw.DOWNGRADE_EDITION_INCR, vw.DOWNGRADE_DATE_MONTHS_MIN, vw.DOWNGRADE_DATE_MONTHS_MAX, vw.UPGRADE_VERSION_AUTH, vw.UPGRADE_VERSION_INCR, vw.UPGRADE_EDITION_AUTH, vw.UPGRADE_EDITION_INCR, vw.UPGRADE_DATE_MONTHS_MIN, vw.UPGRADE_DATE_MONTHS_MAX, vw.DUAL_LICENSE_AUTH, vw.VIRTUAL_INSTALL_AUTH, vw.VIRTUAL_INSTALL_MAX, vw.REASSIGNMENT_AUTH, vw.CROSS_LANGUAGE_AUTH, vw.CROSS_PLATFORMS_AUTH, vw.CORE_FACTOR, vw.LANGUAGES, vw.LANGUAGES_ID, vw.MAINTENANCE_GRADES, vw.MAINTENANCE_GRADES_ID, vw.DISTRIBUTION_MEDIAS, vw.DISTRIBUTION_MEDIAS_ID, vw.PROGRAMS, vw.PROGRAMS_ID, vw.PRICINGS, vw.PRICINGS_ID, vw.PRICE_LEVELS, vw.PRICE_LEVELS_ID, vw.CAT_SW_LIC_PRODUCT_ID, vw.REQ_SPECIFIC_VERSION, vw.LIC_PRODUCT_LONG_DESC, vw.INST_GUIDE_URL, vw.CUTSHEET_URL, vw.PICTURE_URL, vw.TO_BE_DELETED, vw.TO_BE_DELETED_ON, vw.DELETE_REASON, vw.PRIVATE_FLAG, vw.IS_PRIVATE, vw.PRIVATE_TYPE, vw.REPLACEMENT_ID FROM ( SELECT f_tcat.CAT_SW_LIC_RELEASE_ID,f_tcat.CREATE_DATE,f_tcat.LAST_MODIFIED_DATE,f_tcat.CAT_MANUFACTURER_ID,f_tcat.CAT_SW_PRODUCT_ID,f_tcat.CAT_SW_EDITION_ID,f_tcat.CAT_SW_VERSION_GROUP_ID,f_tcat.CAT_SW_VERSION_ID,f_tcat.CAT_SW_RELEASE_ID,f_tcat.CAT_PLATFORMS_ID,f_tcat.CAT_TAXONOMY2012_ID,f_tcat.VENDOR_PRODUCT_NAME,f_tcat.VENDOR_UNIT_OF_MEASURE,f_tcat.LICENSE_TYPES,f_tcat.LICENSE_TYPES_ID,f_tcat.LICENSE_TYPES_OTHER,f_tcat.LICENSE_METRICS,f_tcat.LICENSE_METRICS_ID,f_tcat.UNIT_PACK,f_tcat.UNIT_COUNT_MIN,f_tcat.UNIT_COUNT_MAX,f_tcat.LICENSE_TERM,f_tcat.LICENSE_INFORMATION_OTHER,f_tcat.PERIOD_VALUE,f_tcat.PERIOD_UNIT,f_tcat.MAINT_PEROID_VALUE,f_tcat.MAINT_PERIOD_VALUE,f_tcat.MAINT_PEROID_UNIT,f_tcat.MAINT_PERIOD_UNIT,f_tcat.ENVIRONMENTS,f_tcat.ENVIRONMENTS_ID,f_tcat.ENVIRONMENTS_OTHER,f_tcat.DOWNGRADE_VERSION_AUTH,f_tcat.DOWNGRADE_VERSION_INCR,f_tcat.DOWNGRADE_EDITION_AUTH,f_tcat.DOWNGRADE_EDITION_INCR,f_tcat.DOWNGRADE_DATE_MONTHS_MIN,f_tcat.DOWNGRADE_DATE_MONTHS_MAX,f_tcat.UPGRADE_VERSION_AUTH,f_tcat.UPGRADE_VERSION_INCR,f_tcat.UPGRADE_EDITION_AUTH,f_tcat.UPGRADE_EDITION_INCR,f_tcat.UPGRADE_DATE_MONTHS_MIN,f_tcat.UPGRADE_DATE_MONTHS_MAX,f_tcat.DUAL_LICENSE_AUTH,f_tcat.VIRTUAL_INSTALL_AUTH,f_tcat.VIRTUAL_INSTALL_MAX,f_tcat.REASSIGNMENT_AUTH,f_tcat.CROSS_LANGUAGE_AUTH,f_tcat.CROSS_PLATFORMS_AUTH,f_tcat.CORE_FACTOR,f_tcat.LANGUAGES,f_tcat.LANGUAGES_ID,f_tcat.MAINTENANCE_GRADES,f_tcat.MAINTENANCE_GRADES_ID,f_tcat.DISTRIBUTION_MEDIAS,f_tcat.DISTRIBUTION_MEDIAS_ID,f_tcat.PROGRAMS,f_tcat.PROGRAMS_ID,f_tcat.PRICINGS,f_tcat.PRICINGS_ID,f_tcat.PRICE_LEVELS,f_tcat.PRICE_LEVELS_ID,f_tcat.CAT_SW_LIC_PRODUCT_ID,f_tcat.REQ_SPECIFIC_VERSION,f_tcat.LIC_PRODUCT_LONG_DESC,f_tcat.INST_GUIDE_URL,f_tcat.CUTSHEET_URL,f_tcat.PICTURE_URL,f_tcat.TO_BE_DELETED,f_tcat.TO_BE_DELETED_ON,f_tcat.DELETE_REASON,f_tcat.PRIVATE_FLAG,f_tcat.IS_PRIVATE,f_tcat.PRIVATE_TYPE,f_tcat.REPLACEMENT_ID from TCAT_SW_LIC_RELEASE f_tcat where not exists (select 1 from TPC_SW_LIC_RELEASE tpc_tab where tpc_tab.CAT_SW_LIC_RELEASE_ID=f_tcat.CAT_SW_LIC_RELEASE_ID ) union all SELECT * FROM ( SELECT COALESCE(tpc.CAT_SW_LIC_RELEASE_ID,tcat.CAT_SW_LIC_RELEASE_ID) CAT_SW_LIC_RELEASE_ID,COALESCE(tpc.CREATE_DATE,tcat.CREATE_DATE) CREATE_DATE,GREATEST(NVL(tpc.LAST_MODIFIED_DATE,TO_DATE('31-DEC-1900','DD-MON-YYYY')),tcat.LAST_MODIFIED_DATE) LAST_MODIFIED_DATE,COALESCE(tpc.CAT_MANUFACTURER_ID,tcat.CAT_MANUFACTURER_ID) CAT_MANUFACTURER_ID,COALESCE(tpc.CAT_SW_PRODUCT_ID,tcat.CAT_SW_PRODUCT_ID) CAT_SW_PRODUCT_ID,COALESCE(tpc.CAT_SW_EDITION_ID,tcat.CAT_SW_EDITION_ID) CAT_SW_EDITION_ID,COALESCE(tpc.CAT_SW_VERSION_GROUP_ID,tcat.CAT_SW_VERSION_GROUP_ID) CAT_SW_VERSION_GROUP_ID,COALESCE(tpc.CAT_SW_VERSION_ID,tcat.CAT_SW_VERSION_ID) CAT_SW_VERSION_ID,COALESCE(tpc.CAT_SW_RELEASE_ID,tcat.CAT_SW_RELEASE_ID) CAT_SW_RELEASE_ID,COALESCE(tpc.CAT_PLATFORMS_ID,tcat.CAT_PLATFORMS_ID) CAT_PLATFORMS_ID,COALESCE(tpc.CAT_TAXONOMY2012_ID,tcat.CAT_TAXONOMY2012_ID) CAT_TAXONOMY2012_ID,COALESCE(cast(tpc.VENDOR_PRODUCT_NAME as varchar2(500)),cast(tcat.VENDOR_PRODUCT_NAME as varchar2(500))) VENDOR_PRODUCT_NAME,COALESCE(cast(tpc.VENDOR_UNIT_OF_MEASURE as varchar2(500)),cast(tcat.VENDOR_UNIT_OF_MEASURE as varchar2(500))) VENDOR_UNIT_OF_MEASURE,COALESCE(tpc.LICENSE_TYPES,tcat.LICENSE_TYPES) LICENSE_TYPES,COALESCE(tpc.LICENSE_TYPES_ID,tcat.LICENSE_TYPES_ID) LICENSE_TYPES_ID,COALESCE(tpc.LICENSE_TYPES_OTHER,tcat.LICENSE_TYPES_OTHER) LICENSE_TYPES_OTHER,COALESCE(tpc.LICENSE_METRICS,tcat.LICENSE_METRICS) LICENSE_METRICS,COALESCE(tpc.LICENSE_METRICS_ID,tcat.LICENSE_METRICS_ID) LICENSE_METRICS_ID,COALESCE(tpc.UNIT_PACK,tcat.UNIT_PACK) UNIT_PACK,COALESCE(tpc.UNIT_COUNT_MIN,tcat.UNIT_COUNT_MIN) UNIT_COUNT_MIN,COALESCE(tpc.UNIT_COUNT_MAX,tcat.UNIT_COUNT_MAX) UNIT_COUNT_MAX,COALESCE(tpc.LICENSE_TERM,tcat.LICENSE_TERM) LICENSE_TERM,COALESCE(tpc.LICENSE_INFORMATION_OTHER,tcat.LICENSE_INFORMATION_OTHER) LICENSE_INFORMATION_OTHER,COALESCE(tpc.PERIOD_VALUE,tcat.PERIOD_VALUE) PERIOD_VALUE,COALESCE(tpc.PERIOD_UNIT,tcat.PERIOD_UNIT) PERIOD_UNIT,COALESCE(tpc.MAINT_PEROID_VALUE,tcat.MAINT_PEROID_VALUE) MAINT_PEROID_VALUE,COALESCE(tpc.MAINT_PERIOD_VALUE,tcat.MAINT_PERIOD_VALUE) MAINT_PERIOD_VALUE,COALESCE(tpc.MAINT_PEROID_UNIT,tcat.MAINT_PEROID_UNIT) MAINT_PEROID_UNIT,COALESCE(tpc.MAINT_PERIOD_UNIT,tcat.MAINT_PERIOD_UNIT) MAINT_PERIOD_UNIT,COALESCE(tpc.ENVIRONMENTS,tcat.ENVIRONMENTS) ENVIRONMENTS,COALESCE(tpc.ENVIRONMENTS_ID,tcat.ENVIRONMENTS_ID) ENVIRONMENTS_ID,COALESCE(tpc.ENVIRONMENTS_OTHER,tcat.ENVIRONMENTS_OTHER) ENVIRONMENTS_OTHER,COALESCE(tpc.DOWNGRADE_VERSION_AUTH,tcat.DOWNGRADE_VERSION_AUTH) DOWNGRADE_VERSION_AUTH,COALESCE(tpc.DOWNGRADE_VERSION_INCR,tcat.DOWNGRADE_VERSION_INCR) DOWNGRADE_VERSION_INCR,COALESCE(tpc.DOWNGRADE_EDITION_AUTH,tcat.DOWNGRADE_EDITION_AUTH) DOWNGRADE_EDITION_AUTH,COALESCE(tpc.DOWNGRADE_EDITION_INCR,tcat.DOWNGRADE_EDITION_INCR) DOWNGRADE_EDITION_INCR,COALESCE(tpc.DOWNGRADE_DATE_MONTHS_MIN,tcat.DOWNGRADE_DATE_MONTHS_MIN) DOWNGRADE_DATE_MONTHS_MIN,COALESCE(tpc.DOWNGRADE_DATE_MONTHS_MAX,tcat.DOWNGRADE_DATE_MONTHS_MAX) DOWNGRADE_DATE_MONTHS_MAX,COALESCE(tpc.UPGRADE_VERSION_AUTH,tcat.UPGRADE_VERSION_AUTH) UPGRADE_VERSION_AUTH,COALESCE(tpc.UPGRADE_VERSION_INCR,tcat.UPGRADE_VERSION_INCR) UPGRADE_VERSION_INCR,COALESCE(tpc.UPGRADE_EDITION_AUTH,tcat.UPGRADE_EDITION_AUTH) UPGRADE_EDITION_AUTH,COALESCE(tpc.UPGRADE_EDITION_INCR,tcat.UPGRADE_EDITION_INCR) UPGRADE_EDITION_INCR,COALESCE(tpc.UPGRADE_DATE_MONTHS_MIN,tcat.UPGRADE_DATE_MONTHS_MIN) UPGRADE_DATE_MONTHS_MIN,COALESCE(tpc.UPGRADE_DATE_MONTHS_MAX,tcat.UPGRADE_DATE_MONTHS_MAX) UPGRADE_DATE_MONTHS_MAX,COALESCE(tpc.DUAL_LICENSE_AUTH,tcat.DUAL_LICENSE_AUTH) DUAL_LICENSE_AUTH,COALESCE(tpc.VIRTUAL_INSTALL_AUTH,tcat.VIRTUAL_INSTALL_AUTH) VIRTUAL_INSTALL_AUTH,COALESCE(tpc.VIRTUAL_INSTALL_MAX,tcat.VIRTUAL_INSTALL_MAX) VIRTUAL_INSTALL_MAX,COALESCE(tpc.REASSIGNMENT_AUTH,tcat.REASSIGNMENT_AUTH) REASSIGNMENT_AUTH,COALESCE(tpc.CROSS_LANGUAGE_AUTH,tcat.CROSS_LANGUAGE_AUTH) CROSS_LANGUAGE_AUTH,COALESCE(tpc.CROSS_PLATFORMS_AUTH,tcat.CROSS_PLATFORMS_AUTH) CROSS_PLATFORMS_AUTH,COALESCE(tpc.CORE_FACTOR,tcat.CORE_FACTOR) CORE_FACTOR,COALESCE(tpc.LANGUAGES,tcat.LANGUAGES) LANGUAGES,COALESCE(tpc.LANGUAGES_ID,tcat.LANGUAGES_ID) LANGUAGES_ID,COALESCE(tpc.MAINTENANCE_GRADES,tcat.MAINTENANCE_GRADES) MAINTENANCE_GRADES,COALESCE(tpc.MAINTENANCE_GRADES_ID,tcat.MAINTENANCE_GRADES_ID) MAINTENANCE_GRADES_ID,COALESCE(tpc.DISTRIBUTION_MEDIAS,tcat.DISTRIBUTION_MEDIAS) DISTRIBUTION_MEDIAS,COALESCE(tpc.DISTRIBUTION_MEDIAS_ID,tcat.DISTRIBUTION_MEDIAS_ID) DISTRIBUTION_MEDIAS_ID,COALESCE(tpc.PROGRAMS,tcat.PROGRAMS) PROGRAMS,COALESCE(tpc.PROGRAMS_ID,tcat.PROGRAMS_ID) PROGRAMS_ID,COALESCE(tpc.PRICINGS,tcat.PRICINGS) PRICINGS,COALESCE(tpc.PRICINGS_ID,tcat.PRICINGS_ID) PRICINGS_ID,COALESCE(tpc.PRICE_LEVELS,tcat.PRICE_LEVELS) PRICE_LEVELS,COALESCE(tpc.PRICE_LEVELS_ID,tcat.PRICE_LEVELS_ID) PRICE_LEVELS_ID,COALESCE(tpc.CAT_SW_LIC_PRODUCT_ID,tcat.CAT_SW_LIC_PRODUCT_ID) CAT_SW_LIC_PRODUCT_ID,COALESCE(tpc.REQ_SPECIFIC_VERSION,tcat.REQ_SPECIFIC_VERSION) REQ_SPECIFIC_VERSION,COALESCE(tpc.LIC_PRODUCT_LONG_DESC,tcat.LIC_PRODUCT_LONG_DESC) LIC_PRODUCT_LONG_DESC,COALESCE(tpc.INST_GUIDE_URL,tcat.INST_GUIDE_URL) INST_GUIDE_URL,COALESCE(tpc.CUTSHEET_URL,tcat.CUTSHEET_URL) CUTSHEET_URL,COALESCE(tpc.PICTURE_URL,tcat.PICTURE_URL) PICTURE_URL,COALESCE(tpc.TO_BE_DELETED,tcat.TO_BE_DELETED) TO_BE_DELETED,COALESCE(tpc.TO_BE_DELETED_ON,tcat.TO_BE_DELETED_ON) TO_BE_DELETED_ON,COALESCE(tpc.DELETE_REASON,tcat.DELETE_REASON) DELETE_REASON,COALESCE(uuid.PRIVATE_FLAG,tcat.PRIVATE_FLAG) PRIVATE_FLAG, COALESCE(uuid.IS_PRIVATE,tcat.IS_PRIVATE) IS_PRIVATE, COALESCE(uuid.PRIVATE_TYPE,tcat.PRIVATE_TYPE) PRIVATE_TYPE, COALESCE(tpc.REPLACEMENT_ID,tcat.REPLACEMENT_ID) REPLACEMENT_ID FROM TPC_SW_LIC_RELEASE tpc LEFT JOIN TCAT_SW_LIC_RELEASE tcat ON tcat.CAT_SW_LIC_RELEASE_ID = tpc.CAT_SW_LIC_RELEASE_ID inner JOIN TPC_UUID uuid ON tpc.CAT_SW_LIC_RELEASE_ID = uuid.CAT_UUID_ID AND SIGN(tpc.CAT_SW_LIC_RELEASE_ID )!= -1 ) cat_tab UNION ALL SELECT tpc.CAT_SW_LIC_RELEASE_ID,tpc.CREATE_DATE,tpc.LAST_MODIFIED_DATE,tpc.CAT_MANUFACTURER_ID,tpc.CAT_SW_PRODUCT_ID,tpc.CAT_SW_EDITION_ID,tpc.CAT_SW_VERSION_GROUP_ID,tpc.CAT_SW_VERSION_ID,tpc.CAT_SW_RELEASE_ID,tpc.CAT_PLATFORMS_ID,tpc.CAT_TAXONOMY2012_ID,cast(tpc.VENDOR_PRODUCT_NAME as varchar2(500)) VENDOR_PRODUCT_NAME,cast(tpc.VENDOR_UNIT_OF_MEASURE as varchar2(500)) VENDOR_UNIT_OF_MEASURE,tpc.LICENSE_TYPES,tpc.LICENSE_TYPES_ID,tpc.LICENSE_TYPES_OTHER,tpc.LICENSE_METRICS,tpc.LICENSE_METRICS_ID,tpc.UNIT_PACK,tpc.UNIT_COUNT_MIN,tpc.UNIT_COUNT_MAX,tpc.LICENSE_TERM,tpc.LICENSE_INFORMATION_OTHER,tpc.PERIOD_VALUE,tpc.PERIOD_UNIT,tpc.MAINT_PEROID_VALUE,tpc.MAINT_PERIOD_VALUE,tpc.MAINT_PEROID_UNIT,tpc.MAINT_PERIOD_UNIT,tpc.ENVIRONMENTS,tpc.ENVIRONMENTS_ID,tpc.ENVIRONMENTS_OTHER,tpc.DOWNGRADE_VERSION_AUTH,tpc.DOWNGRADE_VERSION_INCR,tpc.DOWNGRADE_EDITION_AUTH,tpc.DOWNGRADE_EDITION_INCR,tpc.DOWNGRADE_DATE_MONTHS_MIN,tpc.DOWNGRADE_DATE_MONTHS_MAX,tpc.UPGRADE_VERSION_AUTH,tpc.UPGRADE_VERSION_INCR,tpc.UPGRADE_EDITION_AUTH,tpc.UPGRADE_EDITION_INCR,tpc.UPGRADE_DATE_MONTHS_MIN,tpc.UPGRADE_DATE_MONTHS_MAX,tpc.DUAL_LICENSE_AUTH,tpc.VIRTUAL_INSTALL_AUTH,tpc.VIRTUAL_INSTALL_MAX,tpc.REASSIGNMENT_AUTH,tpc.CROSS_LANGUAGE_AUTH,tpc.CROSS_PLATFORMS_AUTH,tpc.CORE_FACTOR,tpc.LANGUAGES,tpc.LANGUAGES_ID,tpc.MAINTENANCE_GRADES,tpc.MAINTENANCE_GRADES_ID,tpc.DISTRIBUTION_MEDIAS,tpc.DISTRIBUTION_MEDIAS_ID,tpc.PROGRAMS,tpc.PROGRAMS_ID,tpc.PRICINGS,tpc.PRICINGS_ID,tpc.PRICE_LEVELS,tpc.PRICE_LEVELS_ID,tpc.CAT_SW_LIC_PRODUCT_ID,tpc.REQ_SPECIFIC_VERSION,tpc.LIC_PRODUCT_LONG_DESC,tpc.INST_GUIDE_URL,tpc.CUTSHEET_URL,tpc.PICTURE_URL,tpc.TO_BE_DELETED,tpc.TO_BE_DELETED_ON,tpc.DELETE_REASON,tpc.PRIVATE_FLAG,tpc.IS_PRIVATE,tpc.PRIVATE_TYPE,tpc.REPLACEMENT_ID FROM TPC_SW_LIC_RELEASE tpc WHERE SIGN(tpc.CAT_SW_LIC_RELEASE_ID )= -1 ) vw INNER JOIN (SELECT rn,CAT_TAG_ID,CAT_SW_PRODUCT_ID,CAT_TAXONOMY2012_ID
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY swtax.CAT_SW_PRODUCT_ID ORDER BY CASE WHEN swtax.TO_BE_DELETED = 1 then -2
WHEN TAG.CAT_TAG_SHORTNAME='IT' then -1
WHEN TAG.CAT_TAG_SHORTNAME='MD' then 0
WHEN TAG.CAT_TAG_SHORTNAME='FX' then 1
WHEN TAG.CAT_TAG_SHORTNAME='IT' then -1
ELSE swtax.TAXONOMY_VERSION END DESC NULLS LAST,swtax.TAXONOMY_DEFAULT DESC NULLS LAST) rn
,swtax.CAT_TAG_ID,swtax.CAT_SW_PRODUCT_ID,swtax.CAT_TAXONOMY2012_ID
FROM CAT_SW_TAXONOMY2012 swtax
INNER JOIN BDNA_TECHNOPEDIA_TAG tag ON swtax.CAT_TAG_ID = TAG.CAT_TAG_ID
WHERE tag.IS_SUBSCRIBED='Y'
) r WHERE r.rn=1
) PROD_TAX ON VW.CAT_SW_PRODUCT_ID = PROD_TAX.CAT_SW_PRODUCT_ID
Possibly Referenced Tables/Views: