8 Replies Latest reply on Mar 16, 2016 11:29 AM by CarlosDLG

    end date multiple responsibilities for a user

    connie.b

      I found the below script to update and end date a responsibility for a user, using responsibility name.  I would like to end date multiple responsibilities (not all) for a user, using responsibility_id instead of responsibility name.  I'm only used to writing simple scripts, and have tried to hack this a bit to meet my needs, but am having no luck.  How do I modify this script/create a new one that will give me the outcome I am after?  Thanks in advance

      DECLARE

         CURSOR c1

         IS

            SELECT fu.user_name,

                   fa.application_short_name,

                   frt.responsibility_name,

                   fr.responsibility_key,

                   fsg.security_group_key

              FROM fnd_user_resp_groups_all ful,

                   fnd_user fu,

                   fnd_responsibility_tl frt,

                   fnd_responsibility fr,

                   fnd_security_groups fsg,

                   fnd_application fa

             WHERE     fu.user_id = ful.user_id

                   AND frt.responsibility_id = ful.responsibility_id

                   AND fr.responsibility_id = frt.responsibility_id

                   AND fsg.security_group_id = ful.security_group_id

                   AND fa.application_id = ful.responsibility_application_id

                   AND frt.language = 'US'

                   AND fu.user_name = 'BIJOYJ'

                   AND frt.responsibility_name = 'Learning Instructor Self-Service';

      BEGIN

         FOR i IN c1

         LOOP

            BEGIN

               fnd_user_pkg.delresp (username         => i.user_name,

                                     resp_app         => i.application_short_name,

                                     resp_key         => i.responsibility_key,

                                     security_group   => i.security_group_key);

               COMMIT;

               DBMS_OUTPUT.

                put_line (

                  i.responsibility_name || ' has been End Dated Successfully !!!');

            EXCEPTION

               WHEN OTHERS

               THEN

                  DBMS_OUTPUT.

                   put_line (

                        'Inner Exception: '

                     || ' - '

                     || i.responsibility_key

                     || ' - '

                     || SQLERRM);

            END;

         END LOOP;

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

      END;

        • 1. Re: end date multiple responsibilities for a user
          Paulzip

          I won't comment on the approaches used in this procedure (they aren't great), but simply done,  change...


          AND frt.responsibility_name = 'Learning Instructor Self-Service';


          for


          AND frt.responsibility_id in (1, 5, 9, 10); -- List of responsibilities in here

          • 2. Re: end date multiple responsibilities for a user
            Dairy Land

            AND frt.responsibility_id  IN (<the_id_you_want_here>)

            • 3. Re: end date multiple responsibilities for a user
              CarlosDLG

              1232238 wrote:

               

              I found the below script to update and end date a responsibility for a user, using responsibility name.  I would like to end date multiple responsibilities (not all) for a user, using responsibility_id instead of responsibility name.  I'm only used to writing simple scripts, and have tried to hack this a bit to meet my needs, but am having no luck.  How do I modify this script/create a new one that will give me the outcome I am after?  Thanks in advance

              DECLARE

                 CURSOR c1

                 IS

                    SELECT fu.user_name,

                           fa.application_short_name,

                           frt.responsibility_name,

                           fr.responsibility_key,

                           fsg.security_group_key

                      FROM fnd_user_resp_groups_all ful,

                           fnd_user fu,

                           fnd_responsibility_tl frt,

                           fnd_responsibility fr,

                           fnd_security_groups fsg,

                           fnd_application fa

                     WHERE     fu.user_id = ful.user_id

                           AND frt.responsibility_id = ful.responsibility_id

                           AND fr.responsibility_id = frt.responsibility_id

                           AND fsg.security_group_id = ful.security_group_id

                           AND fa.application_id = ful.responsibility_application_id

                           AND frt.language = 'US'

                           AND fu.user_name = 'BIJOYJ'

                           AND frt.responsibility_name = 'Learning Instructor Self-Service';

              BEGIN

                 FOR i IN c1

                 LOOP

                    BEGIN

                       fnd_user_pkg.delresp (username         => i.user_name,

                                             resp_app         => i.application_short_name,

                                             resp_key         => i.responsibility_key,

                                             security_group   => i.security_group_key);

                       COMMIT;

                       DBMS_OUTPUT.

                        put_line (

                          i.responsibility_name || ' has been End Dated Successfully !!!');

                    EXCEPTION

                       WHEN OTHERS

                       THEN

                          DBMS_OUTPUT.

                           put_line (

                                'Inner Exception: '

                             || ' - '

                             || i.responsibility_key

                             || ' - '

                             || SQLERRM);

                    END;

                 END LOOP;

              EXCEPTION

                 WHEN OTHERS

                 THEN

                    DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

              END;

              I'm not sure to understand what you want, but you probably want to change this line:

              AND frt.responsibility_name='Learning Instructor Self-Service';

              To something like this:

              AND frt.responsibility_id in (1,2,3);

              Where 1,2,3 is a comma-separated list of the responsability_ids you want to process.  If the column responsability_id is not numeric, but varchar2, you would need something like this:

              AND frt.responsibility_id in ('ID1','ID2','ID3');

              • 4. Re: end date multiple responsibilities for a user

                EXCEPTION handlers are hard coded bug waiting to do nasty things since nobody will ever see any DBMS_OUTPUT

                EXCEPTION handlers should be removed, deleted & otherwise avoided.

                • 5. Re: end date multiple responsibilities for a user
                  Tubby

                  1) remove BOTH the exception blocks completely. You aren't expecting any specific errors (as you used WHEN OTHERS) so how can you presume to to know to do with them? Let the code fail if it needs to fail and go from there.

                   

                  2) remove the COMMIT. What happens when you process 47% of the data and COMMIT and then hit an error? Now your data is in an unknown state because your transaction was committing all along the way. COMMIT inside a loop will also penalize you from a performance standpoint but that's much less a concern than the breaking of the transactional boundaries.

                   

                  Cheers,

                  • 6. Re: end date multiple responsibilities for a user
                    PamNL

                    It would be better to use a list of responsibility keys (ids may not be the same acrosss environments, where the keys usually are ...).

                    • 7. Re: end date multiple responsibilities for a user
                      Jagadekara

                      Hi,

                       

                      but am having no luck.

                      So, what happened?

                       

                      I tried by using your code, it's working.

                       

                      But you want to end date multiple resps. So as suggest by others you need to use

                      AND frt.responsibility_id in (1,2,3);


                      Also check exceptions part.

                      • 8. Re: end date multiple responsibilities for a user
                        CarlosDLG

                        PamNL wrote:

                         

                        It would be better to use a list of responsibility keys (ids may not be the same acrosss environments, where the keys usually are ...).

                        This is what the OP said:

                         

                        I would like to end date multiple responsibilities (not all) for a user, using responsibility_id instead of responsibility name.