Copy Assignment Notes to Target Person

I was asked today about the fact that there is a "Add to Requester's Person Notes" option for assignment notes but nothing that copies the note to the target's Person Notes. This might not seem like a big deal at first, but imagine you have an assignment type of "Hospital Visitation" and you as a staff member are requesting a visitation for a congregation member: You're the requester, the member is the Target person (custom field), but you're the one getting all the notes added to your Person Details page! This is something we're going to look at adding, but in the mean time, here's a couple of scripts I developed that copies all non-Private notes from Person Details-workflow-type assignments to the Target person's Person Notes (and also sets security on the notes equal to the security for the assignment type they come from).

Custom view for the data (run this once):

CREATE VIEW dbo.cust_v_assignment_target_notes
AS
SELECT
aah.assignment_history_id
,aat.name
,aa.title
,aafv.selected_value AS target_person_id
,aah.date_created
,aah.person_id AS noter_person_id
,cp.nick_name
,cp.last_name
,aah.note
FROM
asgn_assignment_history AS aah
JOIN asgn_assignment AS aa ON aa.assignment_id=aah.assignment_id
JOIN asgn_assignment_type AS aat ON aat.assignment_type_id=aa.assignment_type_id
JOIN asgn_assignment_field_value AS aafv ON aafv.custom_field_id=aat.workflow_custom_field_id AND aafv.assignment_id=aa.assignment_id
JOIN core_v_person_basic AS cp ON cp.person_id=aah.person_id
WHERE aat.workflow_type_id=1
AND aah.action='Note'
AND aah.private=0

Custom Stored Procedure (schedule this to run regularly, couple times a day or maybe more):

CREATE PROCEDURE dbo.cust_sp_asgn_notes_to_target

AS

INSERT INTO [dbo].[core_person_history]
([date_created]
,[date_modified]
,[created_by]
,[modified_by]
,[person_id]
,[history_type_luid]
,[history_qualifier_id]
,[history]
,[organization_id])
SELECT
date_created AS date_created
,date_created AS date_modified
,'Assignment Notes' AS created_by
,nick_name+' '+last_name AS modified_by
,target_person_id AS person_id
,355 AS history_type_luid
,assignment_history_id AS history_qualifier_id
,name+', '+title+': ('+nick_name+' '+last_name+') '+note AS history
,(SELECT TOP 1 organization_id FROM orgn_organization) AS organization_id
FROM
cust_v_assignment_target_notes
WHERE assignment_history_id NOT IN (SELECT history_qualifier_id FROM core_person_history WHERE person_id=target_person_id AND history_type_luid=355 AND created_by='Assignment Notes')

DECLARE @historyID INT
DECLARE historyCursor CURSOR FAST_FORWARD FOR
SELECT person_history_id FROM core_person_history WHERE created_by='Assignment Notes' AND person_history_id NOT IN (SELECT object_key FROM secu_permission WHERE object_type=11)

OPEN historyCursor

FETCH NEXT FROM historyCursor
INTO @historyID

WHILE @@FETCH_STATUS<>-1
BEGIN
IF @@FETCH_STATUS=0
BEGIN
INSERT INTO secu_permission (object_type,object_key,subject_type,subject_key,operation_type)
SELECT
11 AS object_type
,@historyID
,subject_type
,subject_key
,operation_type
FROM
secu_permission
WHERE object_type=13
AND object_key=(SELECT TOP 1 assignment_type_id FROM asgn_assignment WHERE assignment_id=(SELECT assignment_id FROM asgn_assignment_history WHERE assignment_history_id=(SELECT history_qualifier_id FROM core_person_history WHERE person_history_id=@historyID)))
END
FETCH NEXT FROM historyCursor
INTO @historyID
END
CLOSE historyCursor
DEALLOCATE historyCursor