How to Remove/End Date a Responsibility assigned to the User in Oracle Apps

DECLARE

     v_user_name                   VARCHAR2 (100) := 'TEST_USER';

     v_responsibility_name   VARCHAR2 (100) := 'System Administrator';

     v_application_name        VARCHAR2 (100) := NULL;

     v_responsibility_key       VARCHAR2 (100) := NULL;

     v_security_group            VARCHAR2 (100) := NULL;

BEGIN

   SELECT fa.application_short_name,

                 fr.responsibility_key,

                 frg.security_group_key,

                 frt.description

       INTO v_application_name,

                 v_responsibility_key,

                 v_security_group,

                 v_description

     FROM fnd_responsibility fr,

                 fnd_application fa,

                 fnd_security_groups frg,

                 fnd_responsibility_tl frt

   WHERE fr.application_id     = fa.application_id

        AND fr.data_group_id        = frg.security_group_id

        AND fr.responsibility_id    = frt.responsibility_id

        AND frt.LANGUAGE            = USERENV ('LANG')

        AND frt.responsibility_name = v_responsibility_name;

 

  fnd_user_pkg.delresp (username => v_user_name,

                                            resp_app => v_application_name,

                                            resp_key => v_responsibility_key,

                                            security_group => v_security_group);

  COMMIT;

  dbms_output.put_line ( 'Responsiblity ' || v_responsibility_name || ' is removed from the user '                                            || v_user_name || ' Successfully' );

EXCEPTION

    WHEN OTHERS THEN

         dbms_output.put_line ( 'Error encountered while deleting responsibilty from the user and                                                    the error is ' || SQLERRM );

END;

Source: Thanks for Sharing

Oracle Data Security and its components

Data Security: Coordinating with Function Security, Data Security provides additional security on the data. It lets the administrators decide what are all the actions that users can perform on the data.

Data Security Policies can reflect access to 
a. All Instances : All instances of an object represents that all rows in the database related to that object. If you consider Purchase Order (PO) as an object and All Instances of PO means all the Purchase Orders in the database. 
b. Instance Set: Instance set of an object represents a set of the related instances of an object. In our Purchase Order as an object example, a set of Blanket Purchase Orders represent a set of Instances of the PO. 
c. A Specific Instance: This generally represents a single row in the database. It is usually identified by a Primary Key value for the object(Ex: PO Header Id)

How to see Account Generator Workflows in Status Monitor of Workflow Administrator

By default, Oracle Account Generator Workflows does not show up in the Workflow Status Monitor screen of Workflow Administrator. To be able to see them in the status monitor, couple of profile options should be enabled to “Yes”.

           1. PO: Set Debug Workflow ON

           2. Account Generator:Run in Debug Mode

REP-0300: ORACLE error occurred REP-0069: Internal error

Following is the error I got while running a report through Concurrent Program in R12.
This is a very generic error and does not give you any details on what is causing this.


Enter Password:
REP-0300: ORACLE error occurred.
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-300: ORACLE error occurred.


I created a report and I was using a Select * from in one of the queries of the report datamodel. When I executed the concurrent program I was getting the above listed error.
I had no clue why this is happenning. I had multiple queries in that report. I tried to debug which query is causing the error by removing one query at a time and I was able to pin down the query causing this error. Then I realized that I am using Select * from.... in this query and I am selecting individual columns in all other queries. When I replaced the * with Individual columns in this query, Error disappeared.
Hope this helps.