UPDATE %Table(PS_STAGE_TMP)TMP
SET TMP.DEPTID = (
SELECT BI.DEPTID
FROM PS_BUS_UNIT_BI BI
WHERE BI.BUSINESS_UNIT = TMP.BUSINESS_UNIT_BI
AND BI.PLACEMENT_TYPE=TMP.PLACEMENT_TYPE
AND BI.LOCAL_FLAG=TMP.LOCAL_FLAG
AND BI.EFFDT=(
SELECT MAX(A.EFFDT)
FROM PS_BUS_UNIT_BI A
WHERE A.SETID=BI.SETID
AND A.ACCT_TYPE=BI.ACCT_TYPE
AND A.BUSINESS_UNIT=BI.BUSINESS_UNIT
AND A.PLACEMENT_TYPE=BI.PLACEMENT_TYPE
AND A.LOCAL_FLAG=BI.LOCAL_FLAG
AND A.EFFDT<=SYSDATE)
AND TMP.BUSINESS_UNIT_BI <> ' '
AND TRIM(BI.DEPTID) <> ' '
AND BI.DEPTID IS NOT NULL )
WHERE TMP.PROCESS_INSTANCE = %ProcessInstance
AND TMP.BATCH_STATUS = 'N' AND
EXISTS (SELECT BI.DEPTID
FROM PS_BUS_UNIT_BI BI
WHERE BI.BUSINESS_UNIT = TMP.BUSINESS_UNIT_BI
AND BI.PLACEMENT_TYPE=TMP.PLACEMENT_TYPE
AND BI.LOCAL_FLAG=TMP.LOCAL_FLAG
AND BI.EFFDT=(
SELECT MAX(A.EFFDT)
FROM PS_BUS_UNIT_BI A
WHERE A.SETID=BI.SETID
AND A.ACCT_TYPE=BI.ACCT_TYPE
AND A.BUSINESS_UNIT=BI.BUSINESS_UNIT
AND A.PLACEMENT_TYPE=BI.PLACEMENT_TYPE
AND A.LOCAL_FLAG=BI.LOCAL_FLAG
AND A.EFFDT<=SYSDATE)
AND TMP.BUSINESS_UNIT_BI <> ' '
AND TRIM(BI.DEPTID) <> ' '
AND BI.DEPTID IS NOT NULL )

your where clause below, is incorrect.
and trim(bi.deptid) <> ' '

If you are going to use the trim function, then check for null
ex. and trim(bi.deptid) is not null

If you are going to check for a blank character value, then check for a single blank space, as character fields are not allowed to be null in a peoplesoft system.
ex. and bi.deptid <> ' '