CREATE TABLE TEMPTABLE
AS
SELECT ROW_NUMBER() OVER (ORDER BY L.IPCONTACTTYPEID) AS ID, R.RECORDID AS RECORDID, L.IPCONTACTTYPEID AS IPCONTACTTYPEID, I.LONGNAME AS NAME, GM.GLOBALMEDIAKEY AS GMKEY
FROM SRC_RECORD R
JOIN SRC_CONFLICT C ON R.RECORDID = C.CONFLICTID
JOIN SRC_LNKIPCONTACT L ON R.RECORDID = L.RECORDID
JOIN SRC_USERINFORMATION I ON L.USERID = I.USERID
JOIN GLOBALMEDIA GM ON GM.PARENTKEY = R.CASEKEY
JOIN TABLEMEDIATYPE TMT ON TMT.MEDIATYPEKEY = GM.MEDIATYPEKEY
WHERE TMT.MEDIATYPEDESCRIPTION = 'Additional Notes'
AND L.IPCONTACTTYPEID IN ( 65, 66, 57, 107 )
ORDER BY 1;
DECLARE IFINAL NUMBER;
BEGIN
SELECT MAX(ID) INTO IFINAL FROM TEMPTABLE;
FOR ICOUNT IN 1..IFINAL
LOOP
UPDATE GLOBALMEDIALOB R
SET R.LONGVALUE = ( SELECT C2B( BLOB_TO_CLOB( R.LONGVALUE ) ||
TO_CLOB ( CHR(13)||CHR(10)||
CASE TT.IPCONTACTTYPEID
WHEN 65 THEN 'Searcher - ' || TT.NAME
WHEN 66 THEN 'Third Party Contact - ' || TT.NAME
WHEN 57 THEN 'Law Firm Contact - ' || TT.NAME
WHEN 107 THEN 'Patent Lead - ' || TT.NAME
END ))
FROM TEMPTABLE TT WHERE R.GLOBALMEDIAKEY = TT.GMKEY AND TT.ID = ICOUNT)
WHERE R.GLOBALMEDIAKEY = ( SELECT GMKEY FROM TEMPTABLE WHERE ID = ICOUNT );
END LOOP;
END;
COMMIT;
DROP TABLE TEMPTABLE;
