问复制oracle ebs中的责任

EN

DECLARE
  --
  resp_count NUMBER := 0;
  --
  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key ,
      fsg.security_group_key
    FROM fnd_application fa ,
      fnd_responsibility fr ,
      fnd_user fu ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg
    WHERE 1                                 = 1
    AND fu.user_name                        = 'XX_ORACLE_APPS_DNA_1'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
      -- AND furga.end_date IS NULL OR trunc(furga.end_date) > trunc(SYSDATE)
    AND FURGA.END_DATE              IS NULL
    AND furga.responsibility_id NOT IN --Exclude those resposibilites which are already there in User 2. Only those resp whoch are present in 1 but not in 2
      (SELECT RESPONSIBILITY_ID
      FROM FND_APPLICATION FA2 ,
        FND_RESPONSIBILITY FR2 ,
        FND_USER FU2 ,
        FND_USER_RESP_GROUPS_ALL FURGA2,
        fnd_security_groups fsg2
      WHERE 1                                  = 1
      AND FU2.USER_NAME                        = 'XX_ORACLE_APPS_DNA_2'
      AND FU2.USER_ID                          = FURGA2.USER_ID
      AND FA2.APPLICATION_ID                   = FR2.APPLICATION_ID
      AND FURGA2.RESPONSIBILITY_ID             = FR2.RESPONSIBILITY_ID
      AND FURGA2.RESPONSIBILITY_APPLICATION_ID = FA2.APPLICATION_ID
      AND fsg2.security_group_id               = furga2.security_group_id
        -- AND furga.end_date IS NULL OR trunc(furga.end_date) > trunc(SYSDATE)
      AND FURGA2.END_DATE IS NULL
      );
  --
  --
BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
      --

      FND_USER_PKG.ADDRESP (USERNAME => 'XX_ORACLE_APPS_DNA_2', 
                            RESP_APP => USER_RESP_DETAILS_REC.APPLICATION_SHORT_NAME, 
                            RESP_KEY => USER_RESP_DETAILS_REC.RESPONSIBILITY_KEY, 
                            SECURITY_GROUP => USER_RESP_DETAILS_REC.SECURITY_GROUP_KEY, 
                            DESCRIPTION => null, 
                            start_date => SYSDATE,
                            end_date => NULL );
      --
   resp_count := resp_count + 1;
--
EXCEPTION
WHEN OTHERS THEN
  --
  DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
  DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
  DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
  --
END;
END LOOP;
--
DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );
--
COMMIT;
END;