Saturday Aug 04, 2012

End Date Responsibility for Oracle FND User

API - fnd_user_pkg.delresp

Example --

Consider FND_USER = ‘PRAJKUMAR’ having responsibility ‘Application Developer’

 

 

Let us try to END_DATE ‘Application Developer’ Responsibility with fnd_user_pkg.delresp API

-- -----------------------------------------------------------------
-- End Date Responsibility from Oracle FND User
-- -----------------------------------------------------------------
DECLARE
   v_user_name                   VARCHAR2 (100) := 'PRAJKUMAR';
   v_responsibility_name   VARCHAR2 (100) := 'Application Developer';
   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
    INTO        v_application_name,
                      v_responsibility_key,
                      v_security_group
    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;
/

After API Run --

 

EO based VO Extension in OAF

In the exercise we have taken “OAF Search Page“can be found @

https://blogs.oracle.com/prajkumar/entry/create_oaf_search_page

 

We are going to extend SearchVO of SearchPG page. Our business requirement is to add Creation Date attribute in the results table region

 

 

1. Analyze the Page

Click on “About this Page” link to check that which ViewObject is associated with the table region [ResultsRN]

 

 

 

 

Here we can see that results table has SearchVO attached to it. Now click on Business Component Reference details and check the path of SearchVO

 

 

Now click on SearchVO which is an EO based [SearchEO] to check whether it has Creation Date attribute or not which we want to add

 

 

Here we can see that our VO does not have CreationDate attribute.

So for adding this attribute we need to perform VO Extension.

FTP this project from application server and open in JDeveloper

 

2. Create a New View Object (VO)

Right click on SearchDemo > New > ADF Business Components > View Object

Package -- prajkumar.oracle.apps.fnd.searchdemo.server

Name – ExtSearchVO

Extends -- prajkumar.oracle.apps.fnd.searchdemo.server.SearchVO

 

 

The next screen allows the addition or deletion of Entity Objects. No change is required here for this extension so simply select Next

 

 

The next pane allows additional attributes to be added or attributes to be removed. In this case add CreationDate attributes to the selected list

 

Note -- Please note the new attribute you would be adding will be coming as Transient

 

Click on Next -> Next

 

 

Modify above SQL statement with below SQL to add new attribute CREATION_DATE

 

SELECT  SearchEO.COLUMN1,
                SearchEO.COLUMN2,
                SearchEO.ROWID,
                SearchEO.CREATION_DATE
FROM     XX_SEARCH_DEMO     SearchEO

 

Click on Next -> Next

Generate ExtSearchVORowImpl 

 

 

Click Finish

 

 

Note -- Now as our attribute created as Transient hence to fix this issue we have two options:

1. We need to correct our XML file generated as it is a known error with Jdeveloper

2. Double click on that attribute i.e CreationDate and in the View Object Attribute window, enter attribute name in 'Expression' column i.e. CreationDate

 

Lets try first method --

Open your ExtSearchVO.xml file in some notepad editor.

Scroll to bottom of that file

Update

 

with

 

 

3. Substitute your New VO with parent VO

Right click on SearchDemo > Project Properties > Business Components > Substitutions

In Available list select SearchVO and in Substitute list select New VO ExtSearchVO and click on Add and then Ok

 

 

4. After substitution import *.jpx

In our case it will modify SearchDemo.jpx at project location i.e. -- D:\xxxx\jdevhome\jdev\myclasses

Open Command Prompt and go to following location of your project

D:\xxxx\jdevbin\oaext\bin

 

Use this Import Command to import jpx

 

5. Personalize the page to create new item

On SearchPG click on Personalize Page link

Select Complete View Radio Button

 

 

Click on Create Item on Table: (ResultTable)

 

 

Set item style as messageStyledText

Set Following Properties

Id – CreationDateId

Data Type -- Date

Prompt – Creation Date

View Instance – ExtSearchVO1

View Attribute – CreationDate

 

 

Click Apply > Return to Application

 

6. Congratulation you have successfully finished. Run Your SearchPG page and Test Your Work

Note -- You can notice page has new Column with name Creation Date

 

 

 

About

Welcome to My Oracle World Puneet Rajkumar

Search

Categories
Archives
« August 2012 »
SunMonTueWedThuFriSat
   
2
3
5
7
8
9
10
11
12
13
14
16
17
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today