When you create a temp table, SQL server hashes the name so that there's no name collision. TempDB has the same rules as all other databases - no 2 objects have the same name.

If 2 connections both create a temp table with the name #tmpTable, they'll appear in the tempDB system tables looking something like this

#tmpTable_____________________________________73921

#tmpTable_____________________________________05482

When you do al alter table add constraint and explicitly set the name, SQL doesn't do that kind of hashing. Hence if two connections are running that proc, the first will run fine, the second will try to create a constrain with the same name as an existing one, and will fail.

What I would recommend you do is create a unique index, rather than a primary key. Index names are only unique at the table level.

CREATE UNIQUE INDEX idx_pkCursor ON #ACCOMPLISHMENTS

(PROGRAM_YEAR, ACTIVITY_CODE)