Your request doesnt quite make sense, your looking at CMC_BLCO_COMM_ITEM.BLEI_CK to see if its NULL, but your create table statement declares "NOT NULL" So it cannot be null, why check for Null?

Seems simple to me. Left Join to your lookup table, If its found, the BILL_ENTY.BILL_ENTY_SK will not be null and use it.

Otherwise use -1

SELECT ISNULL(BILL_ENTY.BILL_ENTY_SK, -1)

FROM dbo.CMC_BLCO_COMM_ITEM

LEFT OUTER JOIN [dbo].[BILL_ENTY]

ON WPS_IDS.BILL_ENTY.SRC_UNQ_BILL_ENTY_ID = WPS_STG.CMC_BLCO_COMM_ITEM.BLEI_CK

Am I missing something?