psi_mmobile

Untitled

Oct 20th, 2020
800
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. PROCEDURE update_traxxeo_person_act IS
  2. CURSOR act_sync_cursor IS
  3. SELECT MfcService.MFC_SERVICE_ID,
  4. MfcService.ERP_ID,
  5. MfcService.NAME,
  6. MfcService.CODE,
  7. MfcService.STR_ID MFC_SERVICE_STR_ID,
  8. Activity.ACTIVITY_ID,
  9. Activity.ACTIVITY_CODE,
  10. Activity.ACTIVITY_NAME,
  11. Activity.STR_ID ACTIVITY_STR_ID
  12. FROM mfc_service MfcService, activity Activity
  13. WHERE MfcService.ERP_ID = Activity.ACTIVITY_ID
  14. AND Activity.LM_DATE > MfcService.LM_DATE;--tracking which activities need to be update
  15. BEGIN
  16.     FOR act IN act_sync_cursor LOOP
  17.       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
  18.       --we insert new records with the same ID as the translations from OF_OWNER
  19.       THEN
  20.      
  21.       DECLARE
  22.       CURSOR translations IS
  23.       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
  24.       WHERE traxxeo_str.str_id = act.ACTIVITY_STR_ID AND traxxeo_str.language <> 'PL' AND traxxeo_str.language <> 'ES'
  25.       AND LOWER(traxxeo_str.language) = LOWER(mframe_language.code);
  26.       BEGIN
  27.       FOR translation IN translations LOOP
  28.       INSERT INTO mf_owner.str (str_id,str_value,mf_language_id) VALUES (translation.str_id, translation.str_value,translation.mf_language_id);
  29.       END LOOP;
  30.       END;
  31.       ELSE --if there are existing translations, we update them with the same IDs as the ones from OF_OWNER
  32.           UPDATE mf_owner.str SET str_id = act.ACTIVITY_STR_ID WHERE str_id = act.MFC_SERVICE_STR_ID;
  33.           UPDATE mf_owner.str
  34.           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;
  35.           UPDATE mf_owner.str
  36.           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;
  37.           UPDATE mf_owner.str
  38.           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;
  39.       END IF;
  40.       UPDATE MFC_SERVICE SET CODE = act.ACTIVITY_CODE, NAME = act.ACTIVITY_NAME, STR_ID = act.ACTIVITY_STR_ID
  41.       WHERE MFC_SERVICE_ID = act.MFC_SERVICE_ID;
  42.     END LOOP;
  43. END;
RAW Paste Data