Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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';
- 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';
- 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';
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement