BDNA Data Platform 5.4 July 2017 (SQL Server) CAT_INDUSTRYCODES - A table containing the list of industry categorization by major analyst or industrial governing body. It contains the lists from North American Industry Classification System (NAICS), Standard Industrial Classification (SIC), and United Nations Standard Products and Services Code (UNSPSC)
|
View Definition:
CREATE VIEW CAT_INDUSTRYCODES AS SELECT f_tcat.CAT_INDUSTRYCODES_ID,f_tcat.CREATE_DATE,f_tcat.LAST_MODIFIED_DATE,f_tcat.CODESOURCE,f_tcat.CODENUMBER,f_tcat.INDUSTRYTITLE,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_INDUSTRYCODES f_tcat where not exists (select 1 from TPC_INDUSTRYCODES tpc_tab where tpc_tab.CAT_INDUSTRYCODES_ID=f_tcat.CAT_INDUSTRYCODES_ID ) union all SELECT * FROM ( SELECT COALESCE(tpc.CAT_INDUSTRYCODES_ID,tcat.CAT_INDUSTRYCODES_ID) CAT_INDUSTRYCODES_ID,COALESCE(tpc.CREATE_DATE,tcat.CREATE_DATE) CREATE_DATE,CASE WHEN ISNULL(tpc.LAST_MODIFIED_DATE,'31-DEC-1900') > tcat.LAST_MODIFIED_DATE THEN tpc.LAST_MODIFIED_DATE
WHEN ISNULL(tpc.LAST_MODIFIED_DATE,'31-DEC-1900') < tcat.LAST_MODIFIED_DATE THEN tcat.LAST_MODIFIED_DATE
ELSE tcat.LAST_MODIFIED_DATE
END LAST_MODIFIED_DATE, COALESCE(tpc.CODESOURCE,tcat.CODESOURCE) CODESOURCE,COALESCE(tpc.CODENUMBER,tcat.CODENUMBER) CODENUMBER,COALESCE(tpc.INDUSTRYTITLE,tcat.INDUSTRYTITLE) INDUSTRYTITLE,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_INDUSTRYCODES tpc LEFT JOIN TCAT_INDUSTRYCODES tcat ON tcat.CAT_INDUSTRYCODES_ID = tpc.CAT_INDUSTRYCODES_ID INNER JOIN TPC_UUID uuid ON tpc.CAT_INDUSTRYCODES_ID = uuid.CAT_UUID_ID AND SIGN(tpc.CAT_INDUSTRYCODES_ID )!= -1 ) cat_tab UNION ALL SELECT tpc.CAT_INDUSTRYCODES_ID,tpc.CREATE_DATE,tpc.LAST_MODIFIED_DATE,tpc.CODESOURCE,tpc.CODENUMBER,tpc.INDUSTRYTITLE,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_INDUSTRYCODES tpc WHERE SIGN(tpc.CAT_INDUSTRYCODES_ID )= -1
Possibly Referenced Tables/Views: