Create User and Create Responsibility Using Script

CREATE TABLE APPS.XX_CREATE_USER
(
  EMP_NO         VARCHAR2(100 BYTE),
  USER_NAME      VARCHAR2(240 BYTE),
  EMAIL          VARCHAR2(240 BYTE),
  STATUS         VARCHAR2(240 BYTE),
  ERROR_MESSAGE  VARCHAR2(2000 BYTE)
)


CREATE OR REPLACE PROCEDURE APPS.XX_CREATE_FNDUSER (errbuf out varchar2, rectcode out varchar2)
AS
   lc_user_name                      VARCHAR2(100);
   lc_user_password                  VARCHAR2(100)   := 'aaa123';
   ld_user_start_date                DATE            := sysdate;
   ld_user_end_date                  VARCHAR2(100)   := NULL;
   ld_password_date                  VARCHAR2(100)   := sysdate; -- TO_DATE('23-JUN-2012');
   ld_password_lifespan_days         NUMBER          := 90;
   ln_person_id                      NUMBER       ;
   lc_email_address                  VARCHAR2(240);
   v_session_id                      integer := userenv('sessionid');

    CURSOR c1
   IS
   
      SELECT a.rowid,a.*
        FROM XX_CREATE_USER a        
        WHERE 1=1
        --AND NVL(STATUS,'E') ='E'
        AND A.EMP_NO not  IN
        --AND A.user_name   IN
        (SELECT PPF.EMPLOYEE_NUMBER
        --(SELECT fu.user_name
            FROM
            per_all_people_f PPF
            ,FND_USER FU
            --,XXDFC_CREATE_USER XXU
            WHERE 1=1
            AND PPF.PERSON_ID=FU.EMPLOYEE_ID
            --AND XXU.EMP_NO(+)=PPF.EMPLOYEE_NUMBER
            AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
          )
          --and a.emp_no in('S00722','S02016','E02148','E01530')
          --and a.emp_no !='C0249'
        ;
       
BEGIN

   FOR REC IN C1
   LOOP
   BEGIN
   select PERSON_ID
   INTO ln_person_id
   from per_all_people_f WHERE 1=1
   AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
   AND upper (ltrim (rtrim (EMPLOYEE_NUMBER))) = upper (ltrim (rtrim (REC.EMP_NO)))
   ;
   END;

--If you Enable  x_password_date  in below code, it is not asking to change password while login first time in application

   fnd_user_pkg.createuser
    (  x_user_name              => REC.USER_NAME,
     x_owner                    => NULL,
     x_unencrypted_password     => lc_user_password,
     x_session_number           => v_session_id,
     x_start_date               => ld_user_start_date,
     x_end_date                 => ld_user_end_date,
     --x_password_date            => ld_password_date,
     x_password_lifespan_days   => ld_password_lifespan_days,
     x_employee_id              => ln_person_id,
     x_email_address            => rec.email
    );
   
    IF ln_person_id IS NOT NULL THEN

    UPDATE XXDFC_CREATE_USER SET STATUS='S' where rowid=rec.rowid;
    ELSE
   
   
        UPDATE XXDFC_CREATE_USER SET STATUS='E' where rowid=rec.rowid;
        END IF;
        END LOOP;
       
    COMMIT;

END;
/

-/**************************Create Responsibility for the exist user for the above code ******************************************************/

CREATE TABLE APPS.XX_CREATE_RESP_STG
(
  EMP_NO               VARCHAR2(100 BYTE),
  USER_NAME            VARCHAR2(240 BYTE),
  RESPONSIBILITY_NAME  VARCHAR2(240 BYTE),
  STATUS               VARCHAR2(240 BYTE),
  ERROR_MESSAGE        VARCHAR2(2000 BYTE)
)


CREATE OR REPLACE PROCEDURE APPS.XX_CREATE_RESP (errbuf out varchar2, rectcode out varchar2)
AS
v_user_name varchar2(240);
v_resp varchar2(240);
v_resp_key varchar2(240);
v_app_short_name varchar2(240);

CURSOR C1 IS

SELECT rownum,a.rowid,UPPER(a.EMP_NO)EMP_NO,UPPER(A.USER_NAME)USER_NAME,A.RESPONSIBILITY_NAME,A.STATUS,A.ERROR_MESSAGE
        FROM XX_CREATE_RESP_STG a        
        WHERE 1=1
        AND NVL(STATUS,'E') ='E'
        --AND EMP_NO IN('S02225','S02224')
        ORDER BY EMP_NO
        ;
       
/*select rownum,a.rowid,UPPER(a.EMP_NO)EMP_NO,UPPER(A.USER_NAME)USER_NAME,A.RESPONSIBILITY_NAME,A.STATUS,A.ERROR_MESSAGE
FROM XX_CREATE_RESP_STG a where upper(a.user_name)  in(
SELECT distinct upper(fu.user_name)
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   )
   --and a.emp_no in('E00011') --,'S02173')
   ---and rownum <10 --and="" nbsp="" p="" rownum=""> 
   ;        */

BEGIN

   FOR REC IN C1
   LOOP
 
   v_resp_key :=null;
   v_app_short_name :=null;
 
   /*BEGIN
 
   SELECT distinct FU.USER_NAME
   INTO v_user_name
            FROM
            per_all_people_f PPF
            ,FND_USER FU
            --,XXDFC_CREATE_USER XXU
            WHERE 1=1
            AND PPF.PERSON_ID=FU.EMPLOYEE_ID
            --AND XXU.EMP_NO(+)=PPF.EMPLOYEE_NUMBER
            AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE  
            --AND PPF.EMPLOYEE_NUMBER IN('S00015','S00024','S00064')
            AND UPPER(PPF.EMPLOYEE_NUMBER)= upper (ltrim (rtrim (REC.EMP_NO)))            
            ;
            END;*/
           
        BEGIN
        select
            r.responsibility_key ,
            a.application_short_name
          into v_resp_key,v_app_short_name
          from fnd_responsibility_vl r,
            fnd_application_vl a
          where
            r.application_id =a.application_id
            and upper(r.responsibility_name) = upper(TRIM(REC.RESPONSIBILITY_NAME));
        END;
       
        DBMS_OUTPUT.put_line ('v_resp_key-'||v_resp_key||' '||'v_app_short_name-'||v_app_short_name||'-REC.RESPONSIBILITY_NAME');
       
        fnd_user_pkg.AddResp (
          username => rec.user_name, --v_user_name,
          resp_app => 'PER', --v_app_short_name,
          resp_key => v_resp_key,
          security_group => 'STANDARD',
          description => null,
          start_date => sysdate,
          end_date => null
          );
          COMMIT;
          UPDATE XX_CREATE_RESP_STG SET STATUS='S' where rowid=rec.rowid;
          END LOOP;
            EXCEPTION
            when others then
          DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
          FND_FILE.put_line(FND_FILE.output,'Error in Calling Receipt API'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
          rollback;
          END;
/