BDNA Data Platform 5.4 July 2017 (Oracle)
CAT_SW_PRODUCT - Software information table

Legend:
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
CAT_SW_PRODUCT_ID number 0  √  null CAT_SW_PRODUCT table's unique identifier
CREATE_DATE date 7  √  null The date when row was first created
LAST_MODIFIED_DATE date 7  √  null The date when last modification on the row was made
CAT_MANUFACTURER_ID number 0  √  null Manufacturer's unique identifier
SOFTWARE varchar2 2000  √  null The name of the software product
FAMILY varchar2 2000  √  null Brand/family in which the software product belongs to (if applicable)
COMPONENT varchar2 2000  √  null The component of the software product (if applicable)
ALIAS varchar2 2000  √  null Former/older name the software product was also known as
IS_SUITE number 0  √  null A flag to indicate whether the software product is a suite or not
PLICSABLE number 0  √  null A flag to indicate whether the software product is licensable or not
NFAMILY number 0  √  null A flag to indicate whether the brand/family name should be included in the full product name
CAT_TAXONOMY2012_ID number 18  √  null BDNA Taxonomy's unique identifier
VENDOR_CATEGORY varchar2 255  √  null Products Vendor Category
DESUPFLAG varchar2 10  √  null A flag that indicates the entry has been de-supported by the vendor. De-support date is in the past (whether the exact date is known or unknown)
DISCONTINUEDFLAG varchar2 10  √  null A flag that indicates the entry has been discontinued by the vendor. Discontinued date is in the past (whether the exact date is known or unknown). The entry might or might not be supported by the vendor
TO_BE_DELETED varchar2 1  √  null Would this record be deleted? eg: Y
TO_BE_DELETED_ON date 7  √  null The date when this record would be deleted
DELETE_REASON varchar2 500  √  null The reason why the entry is marked to be deleted (Values: Duplicate,Inaccurate)
PRIVATE_FLAG number 0  √  null 0 = Not Modified (i.e. original Technopedia entry),1 = Modified Technopedia object, 2 = Proprietary object (those that have negative ids)
IS_PRIVATE varchar2 1  √  null y = a private entry, n = original Technopedia entry
PRIVATE_TYPE varchar2 500  √  null PRIVATE_TYPE = NULL if PRIVATE_FLAG = 0,PRIVATE_TYPE = Modified Technopedia entry if PRIVATE_FLAG = 1,PRIVATE_TYPE = Proprietary entry if PRIVATE_FLAG = 2
REPLACEMENT_ID number 0  √  null Unique identifier of the replacement entry whenever the entry is marked to be deleted for 'Duplicate' reason

Analyzed at Thu Jul 20 12:13 PDT 2017

View Definition:
SELECT vw.CAT_SW_PRODUCT_ID, vw.CREATE_DATE, vw.LAST_MODIFIED_DATE, vw.CAT_MANUFACTURER_ID, vw.SOFTWARE, vw.FAMILY, vw.COMPONENT, vw.ALIAS, vw.IS_SUITE, vw.PLICSABLE, vw.NFAMILY, prod_tax.CAT_TAXONOMY2012_ID, vw.VENDOR_CATEGORY, vw.DESUPFLAG, vw.DISCONTINUEDFLAG, 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_PRODUCT_ID,f_tcat.CREATE_DATE,f_tcat.LAST_MODIFIED_DATE,f_tcat.CAT_MANUFACTURER_ID,f_tcat.SOFTWARE,f_tcat.FAMILY,f_tcat.COMPONENT,f_tcat.ALIAS,f_tcat.IS_SUITE,f_tcat.PLICSABLE,f_tcat.NFAMILY,f_tcat.CAT_TAXONOMY2012_ID,f_tcat.VENDOR_CATEGORY,f_tcat.DESUPFLAG,f_tcat.DISCONTINUEDFLAG,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_PRODUCT f_tcat where not exists (select 1 from TPC_SW_PRODUCT tpc_tab where tpc_tab.CAT_SW_PRODUCT_ID=f_tcat.CAT_SW_PRODUCT_ID ) union all SELECT * FROM ( SELECT COALESCE(tpc.CAT_SW_PRODUCT_ID,tcat.CAT_SW_PRODUCT_ID) CAT_SW_PRODUCT_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(cast(tpc.SOFTWARE as varchar2(255)),cast(tcat.SOFTWARE as varchar2(255))) SOFTWARE,COALESCE(cast(tpc.FAMILY as varchar2(255)),cast(tcat.FAMILY as varchar2(255))) FAMILY,COALESCE(cast(tpc.COMPONENT as varchar2(64)),cast(tcat.COMPONENT as varchar2(64))) COMPONENT,COALESCE(tpc.ALIAS,tcat.ALIAS) ALIAS,COALESCE(tpc.IS_SUITE,tcat.IS_SUITE) IS_SUITE,COALESCE(tpc.PLICSABLE,tcat.PLICSABLE) PLICSABLE,COALESCE(tpc.NFAMILY,tcat.NFAMILY) NFAMILY,COALESCE(tpc.CAT_TAXONOMY2012_ID,tcat.CAT_TAXONOMY2012_ID) CAT_TAXONOMY2012_ID,COALESCE(tpc.VENDOR_CATEGORY,tcat.VENDOR_CATEGORY) VENDOR_CATEGORY,COALESCE(tpc.DESUPFLAG,tcat.DESUPFLAG) DESUPFLAG,COALESCE(tpc.DISCONTINUEDFLAG,tcat.DISCONTINUEDFLAG) DISCONTINUEDFLAG,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_PRODUCT tpc LEFT JOIN TCAT_SW_PRODUCT tcat ON tcat.CAT_SW_PRODUCT_ID = tpc.CAT_SW_PRODUCT_ID inner JOIN TPC_UUID uuid ON tpc.CAT_SW_PRODUCT_ID = uuid.CAT_UUID_ID AND SIGN(tpc.CAT_SW_PRODUCT_ID )!= -1 ) cat_tab UNION ALL SELECT tpc.CAT_SW_PRODUCT_ID,tpc.CREATE_DATE,tpc.LAST_MODIFIED_DATE,tpc.CAT_MANUFACTURER_ID,cast(tpc.SOFTWARE as varchar2(255)) SOFTWARE,cast(tpc.FAMILY as varchar2(255)) FAMILY,cast(tpc.COMPONENT as varchar2(64)) COMPONENT,tpc.ALIAS,tpc.IS_SUITE,tpc.PLICSABLE,tpc.NFAMILY,tpc.CAT_TAXONOMY2012_ID,tpc.VENDOR_CATEGORY,tpc.DESUPFLAG,tpc.DISCONTINUEDFLAG,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_PRODUCT tpc WHERE SIGN(tpc.CAT_SW_PRODUCT_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:

Confidential and Proprietary to BDNA