BEGIN
  FOR REC IN (SELECT * FROM HARRELATYPE) LOOP 
    SP_ADD_CODE('Orion.Attachment', 
                'NatureOfRelation', 
                RTRIM(REC.RELATYPECODE), 
                RTRIM(REC.RELATYPENAME)
               );
  END LOOP;
  COMMIT;
END;



DECLARE
BEGIN
       FOR REC IN (SELECT * FROM HARRELATYPE) LOOP
             SP_ADD_CODE('Orion.Attachment','NatureOfRelation', RTRIM (REC.RELATYPECODE) ,RTRIM (REC.RELATYPENAME));
       END LOOP;
       COMMIT;
 
UPDATE HARATTACHMENT A SET
            (NATURE_OF_RELATION,
            SP_ANALYS1,
            SP_ANALYS2,
            SP_ANALYS3,
            SP_ANALYS4,
            SP_ANALYS5,
            SP_ANALYS6,
            SP_ANALYS7,
            SP_ANALYS8,
            SP_ANALYS9,
            SP_ANALYS10,
            MISC_NUM1,
            MISC_NUM2,
            MISC_NUM3,
            MISC_NUM4,
            MISC_NUM5,
            MISC_NUM6,
            MISC_NUM7,
            MISC_NUM8,
            MISC_NUM9,
            MISC_NUM10,
            MISC_DATE1,
            MISC_DATE2,
            MISC_DATE3,
            MISC_DATE4,
            MISC_DATE5,
            MISC_DATE6,
            MISC_DATE7,
            MISC_DATE8,
            MISC_DATE9,
            MISC_DATE10,
            MISC_TEXT1,
            MISC_TEXT2,
            MISC_TEXT3,
            MISC_TEXT4,
            MISC_TEXT5,
            MISC_TEXT6,
            MISC_TEXT7,
            MISC_TEXT8,
            MISC_TEXT9,
            MISC_TEXT10) = 
            (SELECT DISTINCT
                  RTRIM(R.RELATYPECODE),
                  RTRIM(R.SPECIAL1),
                  RTRIM(R.SPECIAL2),
                  RTRIM(R.SPECIAL3),
                  RTRIM(R.SPECIAL4),
                  RTRIM(R.SPECIAL5),
                  RTRIM(R.SPECIAL6),
                  RTRIM(R.SPECIAL7),
                  RTRIM(R.SPECIAL8),
                  RTRIM(R.SPECIAL9),
                  RTRIM(R.SPECIAL10),
                  R.MISCNUM1,
                  R.MISCNUM2,
                  R.MISCNUM3,
                  R.MISCNUM4,
                  R.MISCNUM5,
                  R.MISCNUM6,
                  R.MISCNUM7,
                  R.MISCNUM8,
                  R.MISCNUM9,
                  R.MISCNUM10,
                  R.MISCDT1,
                  R.MISCDT2,
                  R.MISCDT3,
                  R.MISCDT4,
                  R.MISCDT5,
                  R.MISCDT6,
                  R.MISCDT7,
                  R.MISCDT8,
                  R.MISCDT9,
                  R.MISCDT10,
                  RTRIM(R.MISCDESC1),
                  RTRIM(R.MISCDESC2),
                  RTRIM(R.MISCDESC3),
                  RTRIM(R.MISCDESC4),
                  RTRIM(R.MISCDESC5),
                  RTRIM(R.MISCDESC6),
                  RTRIM(R.MISCDESC7),
                  RTRIM(R.MISCDESC8),
                  RTRIM(R.MISCDESC9),
                  RTRIM(R.MISCDESC10)
            FROM HARRELA R, HARCLAIM C 
                  WHERE DOCTYPCOD IN (SELECT DOCTYPE FROM DOCTYPEMAP WHERE ENTITY_NAM IN ('STARS.Claim','STARS.Incident'))
                    AND R.ROLOCOD = A.ATTACHMENTID
                    AND C.CLAIM = RTRIM(R.DOC_NUM)
                    AND NVL(C.SETID,-1) = NVL(R.SETID,-1)
                    AND C.CLAIM_ID = A.PARENTID
                    AND (
                     (R.DOCTYPCOD = 1 AND C.STATUS <> 'I')
                     OR 
                     (R.DOCTYPCOD = 26 AND C.STATUS = 'I')
                     )
                     AND ROWNUM = 1
                     )
        WHERE A.ATTACHMENTTYPE = 'Orion.ContactRole' AND PARENTTYPE IN ('STARS.Incident','STARS.Claim')
              AND EXISTS (
              SELECT 1 
                  FROM HARRELA R, HARCLAIM C 
                  WHERE DOCTYPCOD IN (SELECT DOCTYPE FROM DOCTYPEMAP WHERE ENTITY_NAM IN ('STARS.Claim','STARS.Incident'))
                    AND R.ROLOCOD = A.ATTACHMENTID
                    AND C.CLAIM = RTRIM(R.DOC_NUM)
                    AND NVL(C.SETID,-1) = NVL(R.SETID,-1)
                    AND C.CLAIM_ID = A.PARENTID
                    AND (
                     (R.DOCTYPCOD = 1 AND C.STATUS <> 'I')
                     OR 
                     (R.DOCTYPCOD = 26 AND C.STATUS = 'I')
                     )
                    );
            COMMIT;
END;