BDNA Data Platform 5.4 July 2017 (Oracle) 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:
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 "CAT_INDUSTRYCODES_ID","CREATE_DATE","LAST_MODIFIED_DATE","CODESOURCE","CODENUMBER","INDUSTRYTITLE","TO_BE_DELETED","TO_BE_DELETED_ON","DELETE_REASON","PRIVATE_FLAG","IS_PRIVATE","PRIVATE_TYPE","REPLACEMENT_ID" FROM ( SELECT COALESCE(tpc.CAT_INDUSTRYCODES_ID,tcat.CAT_INDUSTRYCODES_ID) CAT_INDUSTRYCODES_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.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