Forum Stats

  • 3,728,423 Users
  • 2,245,612 Discussions
  • 7,853,521 Comments

Discussions

Trace API execution

rootsman
rootsman Member Posts: 233 Bronze Badge

Hi guys,

I have made cursor which updates table values by using provided Oracle API fnd_user_resp_groups_api.update_assignment. My question is how can I see which tables are affected? I would like to revert change in case that I need to so I would like to preserve those data first. Our Oracle database is version 12.1.0.2.

If you suggest tracing please explain me how to make trace file more readable, or any other ideas?

Thank you

Answers

  • rootsman
    rootsman Member Posts: 233 Bronze Badge

    Hi team,

    For better understanding i have used this code block which execution i would like to trace somehow, in order to see affected tables:

    declare
        --cursor to get all inactive users
    cursor cur_inactive_user
    is 
        select  fu.user_id,
            fd.responsibility_id,
            fd.responsibility_application_id,
            fd.security_group_id,
            fd.start_date,
            fu.end_date
        from fnd_user fu, fnd_user_resp_groups_direct fd
        where fu.user_id = fd.user_id
        and (fu.end_date < sysdate or fu.end_date is not null)
        and fd.end_date is null;
        --and fu.user_name='HFORD';
    begin
        for rec_inactive_user in cur_inactive_user
        loop 
          -- checking if the responsibillity is assigned to the user 
          if (fnd_user_resp_groups_api.assignment_exists (
           rec_inactive_user.user_id,
           rec_inactive_user.responsibility_id,
           rec_inactive_user.responsibility_application_id,
            rec_inactive_user.security_group_id))
        then
            -- Call API to End date the responsibillity
            fnd_user_resp_groups_api.update_assignment (
            user_id                          => rec_inactive_user.user_id,
            responsibility_id                => rec_inactive_user.responsibility_id,
            responsibility_application_id    => rec_inactive_user.responsibility_application_id,
            security_group_id                => rec_inactive_user.security_group_id,
            start_date                       => rec_inactive_user.start_date,
            end_date                         => rec_inactive_user.end_date,
            description                      => null );
            --commit;
        end if;
        end loop;
    end;
    

    Thanks

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,180 Red Diamond

    This question is best asked in an Oracle Apps forum. This forum deals with general database issues, problems, config, setup, and so on.

Sign In or Register to comment.