psi_mmobile

Untitled

Oct 19th, 2020
842
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;
  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))
  18.       THEN
  19.           INSERT INTO mf_owner.str (str_id,str_value,mf_language_id) SELECT str_id,str_value,1 FROM of_owner.str WHERE str_id = act.ACTIVITY_STR_ID AND language = 'EN';
  20.           INSERT INTO mf_owner.str (str_id,str_value,mf_language_id) SELECT str_id,str_value,2 FROM of_owner.str WHERE str_id = act.ACTIVITY_STR_ID AND language = 'FR';
  21.           INSERT INTO mf_owner.str (str_id,str_value,mf_language_id) SELECT str_id,str_value,3 FROM of_owner.str WHERE str_id = act.ACTIVITY_STR_ID AND language = 'DU';
  22.       ELSE
  23.           UPDATE mf_owner.str SET str_id = act.ACTIVITY_STR_ID WHERE str_id = act.MFC_SERVICE_STR_ID;
  24.           UPDATE mf_owner.str
  25.           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;
  26.           UPDATE mf_owner.str
  27.           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;
  28.           UPDATE mf_owner.str
  29.           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;
  30.       END IF;
  31.       UPDATE MFC_SERVICE SET CODE = act.ACTIVITY_CODE, NAME = act.ACTIVITY_NAME, STR_ID = act.ACTIVITY_STR_ID
  32.       WHERE MFC_SERVICE_ID = act.MFC_SERVICE_ID;
  33.     END LOOP;
  34. END;
RAW Paste Data