PROCEDURE update_traxxeo_person_act IS
CURSOR act_sync_cursor IS
SELECT MfcService.MFC_SERVICE_ID,
MfcService.ERP_ID,
MfcService.NAME,
MfcService.CODE,
MfcService.STR_ID MFC_SERVICE_STR_ID,
Activity.ACTIVITY_ID,
Activity.ACTIVITY_CODE,
Activity.ACTIVITY_NAME,
Activity.STR_ID ACTIVITY_STR_ID
FROM mfc_service MfcService, activity Activity
WHERE MfcService.ERP_ID = Activity.ACTIVITY_ID
AND Activity.LM_DATE > MfcService.LM_DATE;--tracking which activities need to be update
BEGIN
    FOR act IN act_sync_cursor LOOP
      IF (act.MFC_SERVICE_STR_ID IS NULL OR NOT EXISTS(SELECT STR_ID FROM mf_owner.STR WHERE STR_ID = act.MFC_SERVICE_STR_ID)) --if there is not id set or there isn't one in the STR table
      --we insert new records with the same ID as the translations from OF_OWNER
      THEN

      DECLARE
      CURSOR translations IS
      SELECT traxxeo_str.str_id,traxxeo_str.str_value,mframe_language.MF_LANGUAGE_ID FROM of_owner.str traxxeo_str, mf_owner.mf_language mframe_language
      WHERE traxxeo_str.str_id = act.ACTIVITY_STR_ID AND traxxeo_str.language <> 'PL' AND traxxeo_str.language <> 'ES'
      AND LOWER(traxxeo_str.language) = LOWER(mframe_language.code);
      BEGIN
      FOR translation IN translations LOOP
      INSERT INTO mf_owner.str (str_id,str_value,mf_language_id) VALUES (translation.str_id, translation.str_value,translation.mf_language_id);
      END LOOP;
      END;
      ELSE --if there are existing translations, we update them with the same IDs as the ones from OF_OWNER
          UPDATE mf_owner.str SET str_id = act.ACTIVITY_STR_ID WHERE str_id = act.MFC_SERVICE_STR_ID;
          UPDATE mf_owner.str
          SET str_value = (SELECT str_value FROM of_owner.str WHERE str_id = act.ACTIVITY_STR_ID AND language = 'EN') WHERE str_id = act.ACTIVITY_STR_ID AND mf_language_id = 1;
          UPDATE mf_owner.str
          SET str_value = (SELECT str_value FROM of_owner.str WHERE str_id = act.ACTIVITY_STR_ID AND language = 'FR') WHERE str_id = act.ACTIVITY_STR_ID AND mf_language_id = 2;
          UPDATE mf_owner.str
          SET str_value = (SELECT str_value FROM of_owner.str WHERE str_id = act.ACTIVITY_STR_ID AND language = 'DU') WHERE str_id = act.ACTIVITY_STR_ID AND mf_language_id = 3;
      END IF;
      UPDATE MFC_SERVICE SET CODE = act.ACTIVITY_CODE, NAME = act.ACTIVITY_NAME, STR_ID = act.ACTIVITY_STR_ID
      WHERE MFC_SERVICE_ID = act.MFC_SERVICE_ID;
    END LOOP;
END;