5 Replies Latest reply on Sep 11, 2018 12:28 PM by stejac

    fnd_user_pkg.UpdateUser remove end_date

    stejac

      Hi community

      i'm working on a script to remove the endate of user i'm in 12.2.4

      the user have an end_date on his account but no end end date present on responsibility

       

      removing the end_date and changing the password work great

      but when i log with the account the user dont have any responsibility

       

      did i forgot something it was working great in 12.1.3

      thanks

       

      script

      declare

      --vld_end_date date := trunc(sysdate);    --deactiv

      vld_end_date date := to_date('2', 'J');   --activ

      ----------------------------------------------------------------

        TYPE rec_usr IS RECORD(usr    varchar2(4000));

        TYPE list_tbl_usr IS TABLE OF rec_usr INDEX BY BINARY_INTEGER;

        tbl_usr list_tbl_usr;

        total number := 0;

        i     number := 0;

        cursor c_user (x_user_name in varchar2) is

        select user_name,user_id,start_date,end_date,last_logon_date,description,

             password_date,password_accesses_left,password_lifespan_accesses,password_lifespan_days,

           employee_id,email_address,fax,customer_id,supplier_id,person_party_id,user_guid

        from fnd_user

        where user_name = upper(x_user_name);

        rc_user c_user%rowtype;

      begin

        --

        i:=i+1; tbl_usr(i).usr := 'USER722'; 

        --

        total := tbl_usr.count;

        i := 0;

        FOR i IN 1 .. total LOOP

          open c_user(tbl_usr(i).usr);

        fetch c_user into rc_user;

        if c_user%notfound then

          dbms_output.put_line('Usager introuvable:' ||tbl_usr(i).usr);

        else

          begin

            fnd_user_pkg.UpdateUser(x_user_name            => rc_user.user_name,

                      x_owner            => 'CUST',

                        x_start_date           => rc_user.start_date,

                        x_end_date             => vld_end_date,

                        x_unencrypted_password  => 'changeitnow', --

                        x_last_logon_date        => rc_user.last_logon_date,

                        x_description          => rc_user.description,

                        x_password_date        => vld_end_date,

                        x_password_accesses_left     => rc_user.password_accesses_left,

                        x_password_lifespan_accesses => rc_user.password_lifespan_accesses,

                        x_password_lifespan_days     => rc_user.password_lifespan_days,

                        x_employee_id                => rc_user.employee_id,

                        x_email_address              => rc_user.email_address,

                        x_fax                        => rc_user.fax,

                        x_customer_id                => rc_user.customer_id,

                        x_supplier_id                => rc_user.supplier_id);

          commit;

          if vld_end_date = to_date('2', 'J') then

                dbms_output.put_line('activ:' ||tbl_usr(i).usr);

            else

              dbms_output.put_line('deactiv:' ||tbl_usr(i).usr);

            end if;

          EXCEPTION

              WHEN OTHERS THEN

                dbms_output.put_line('err fnd_user_pkg.UpdateUser:' ||tbl_usr(i).usr);

          end;

          end if;

        close c_user;

        end loop;

      end;

        • 1. Re: fnd_user_pkg.UpdateUser remove end_date
          stejac

          i have the same problem if i use this

          BEGIN

          FND_USER_PKG.EnableUser(username => 'USER4431');

          commit;

          END;

          the account is unlock but ther is no responsibility

          • 2. Re: fnd_user_pkg.UpdateUser remove end_date
            John_K

            Try clearing the mid-tier cache after running it (Functional Administrator responsibility).

            Here is a script that I use in development environments after a clone to open up my account and any responsibilities I've ever had.

             

             

            Declare
              lusername      fnd_user.user_name%Type := 'MYUSERNAME';
              lnewpassword   Varchar2(20) := 'ChangeThePasswordHere123!';
              loldstartdate  fnd_user.start_date%Type;
            Begin
              Select start_date
                Into loldstartdate
            From fnd_user
               Where user_name=lusername;
               
              fnd_user_pkg.enableuser(username=>lusername,start_date=>loldstartdate);
              
              If Not(fnd_user_pkg.ispasswordchangeable(p_user_name=>lusername) And fnd_user_pkg.changepassword(username=>lusername,newpassword=>lnewpassword)) Then
                Raise_Application_Error(-20000,'Could not change password');
              End If;
              
              For resps In (Select furgd.user_id, furgd.responsibility_id, furgd.responsibility_application_id, furgd.security_group_id, furgd.start_date
                             From fnd_user_resp_groups_direct furgd,
                                  fnd_user fu
                            Where fu.user_id=furgd.user_id
                              And fu.user_name=lusername
                              And Not Exists (Select Null
                                                From fnd_user_resp_groups_indirect furgi
                                               Where furgi.user_id=furgd.user_id
                                                 And furgi.responsibility_id=furgd.responsibility_id
                                                 And furgi.responsibility_application_id=furgd.responsibility_application_id
                                                 And furgi.security_group_id=furgd.security_group_id)) Loop
                fnd_user_resp_groups_api.update_assignment(user_id=>resps.user_id, responsibility_id=>resps.responsibility_id, 
                                                           responsibility_application_id=>resps.responsibility_application_id,
                                                           security_group_id=>resps.security_group_id, start_date=>resps.start_date, end_date=>Null, description=>Null);   
              End Loop;
            End;
            
            1 person found this helpful
            • 3. Re: fnd_user_pkg.UpdateUser remove end_date
              stejac

              i have already try clearing the cache,

              your script had the same result as mine (plus removing all end date to responsibility)

              even shutting down the apps and database didn't solve the problem

              i'm sure both script are good, think we will need to start patching a little

              thanks

              • 4. Re: fnd_user_pkg.UpdateUser remove end_date
                Kanda-Oracle

                Hi,

                 

                Can you explore this document

                 

                 

                Thanks!

                • 5. Re: fnd_user_pkg.UpdateUser remove end_date
                  stejac

                  i was unable to find user_id in fnd_user_resp_groups

                   

                  runned 3 time :     Workflow Directory Services User/Role Validation

                  and one time :     Synchronize WF Local Tables

                   

                  now its OK

                   

                  thanks all