Oracle Apps: How to get Oracle Apps (FND user) password from backend

You can run the below script from backend to retrieve the FND User password from backend.

Create the below packge:

 create or replace package xxaj_get_pwd  
 as  
   function decrypt (key in varchar2, value in varchar2)  
   return varchar2;  
 end xxaj_get_pwd;  
 /  
 create or replace package body xxaj_get_pwd  
 as  
   function decrypt (key in varchar2, value in varchar2)  
   return varchar2  
   as  
    language java  
    NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';  
 end xxaj_get_pwd;  
 /  

Execute the below script with the username to get the password.

 select fu.user_name  
       ,xxaj_get_pwd.decrypt ((select xxaj_get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd
                                                           ,usertable.encrypted_foundation_password )  
                               from fnd_user usertable  
                               where usertable.user_name = regexp_substr(fnd_web_sec.get_guest_username_pwd,'(.*)/(.*)',1,1,NULL,1))  
                             ,fu.encrypted_user_password ) password  
 from fnd_user fu  
 where upper(fu.user_name) = upper('<USER_NAME>');  
 /  

Related links :
Oracle Apps: Reset FND User password from backend
Oracle Apps: Create User and Add Responsibility from backend


Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps: Helpful Queries on FND Users / Roles / Menus / Responsibilities

Some queries which will be helpful when querying on User / Responsibility / Menu in Oracle Apps (eBusiness Suite).

#1. Query to get the Menu/Submenu/Function list (prompts) for a responsibility as you see when you login.

 SELECT (lpad(' ', (LEVEL-1) * 5, ' ') || LEVEL)                        lvl  
       ,(lpad(' ', (LEVEL-1) * 5, ' ') || x.entry_sequence)             entry_sequence  
       ,(lpad(' ', (LEVEL-1) * 5, ' ') || x.prompt)                     prompt  
       ,(SELECT (a.user_menu_name || '[' || a.menu_name || ']')  
         FROM   fnd_menus_vl a  
         WHERE  a.menu_id = x.sub_menu_id)                              menu_name  
       ,(SELECT (a.user_function_name || '[' || a.function_name || ']')  
         FROM   fnd_form_functions_vl a  
         WHERE  a.function_id = x.function_id)                          function_name  
       ,x.sub_menu_id  
       ,x.function_id       
       ,x.grant_flag  
 FROM apps.fnd_menu_entries_vl x  
     ,(SELECT a.menu_id  
             ,a.responsibility_id  
       FROM  apps.fnd_responsibility_vl a  
       WHERE UPPER(a.responsibility_name) = UPPER('System Administrator')) y     -- Replace the responsibility based on your requirement  
 START WITH x.menu_id = y.menu_id  
       AND x.prompt IS NOT NULL -- Comment this if you want all the Functions/Menus  
       --Menu/Function Exclusions  
       AND NVL(x.menu_id,-1)     NOT IN (SELECT b.action_id 
                                         FROM   apps.fnd_resp_functions b   
                                         WHERE  b.responsibility_id = y.responsibility_id 
                                         AND    b.rule_type         = 'M')  
       AND NVL(x.sub_menu_id,-1) NOT IN (SELECT b.action_id 
                                         FROM   apps.fnd_resp_functions b   
                                         WHERE  b.responsibility_id = y.responsibility_id 
                                         AND    b.rule_type         = 'M')  
       AND NVL(x.function_id,-1) NOT IN (SELECT b.action_id 
                                         FROM   apps.fnd_resp_functions b   
                                         WHERE  b.responsibility_id = y.responsibility_id 
                                         AND    b.rule_type         = 'F')  
 CONNECT BY PRIOR x.sub_menu_id = x.menu_id  
            AND  x.prompt IS NOT NULL -- Comment this if you want all the Functions/Menus  
            --Menu/Function Exclusions  
            AND  NVL(x.sub_menu_id,-1) NOT IN (SELECT b.action_id 
                                               FROM   apps.fnd_resp_functions b   
                                               WHERE  b.responsibility_id = y.responsibility_id 
                                               AND    b.rule_type            = 'M')  
            AND  NVL(x.function_id,-1) NOT IN (SELECT b.action_id 
                                               FROM   apps.fnd_resp_functions b   
                                               WHERE  b.responsibility_id = y.responsibility_id 
                                               AND    b.rule_type         = 'F')  
 ORDER SIBLINGS BY x.entry_sequence;

#2. Query to get all the Responsibilities associated with a specific User / all the users which have a specific Responsibility 

 SELECT resp_type  
       ,user_id  
       ,user_name  
       ,responsibility_id  
       ,responsibility_name  
       ,start_date  
       ,end_date  
       ,application_short_name  
       ,application_name  
 FROM  (SELECT 'DIRECT' resp_type  
              ,fu.user_id  
              ,fu.user_name  
              ,resp.responsibility_id  
              ,resp.responsibility_name  
              ,frd.start_date  
              ,frd.end_date  
              ,app.application_short_name  
              ,app.application_name  
        FROM  fnd_user                    fu  
             ,fnd_user_resp_groups_direct frd  
             ,fnd_responsibility_vl       resp  
             ,fnd_application_vl          app  
        WHERE fu.user_id            = frd.user_id  
        AND   frd.responsibility_id = resp.responsibility_id  
        AND   resp.application_id   = app.application_id  
        UNION ALL  
        SELECT 'INDIRECT' resp_type  
              ,fu.user_id  
              ,fu.user_name  
              ,resp.responsibility_id  
              ,resp.responsibility_name  
              ,fri.start_date  
              ,fri.end_date  
              ,app.application_short_name  
              ,app.application_name  
        FROM  fnd_user                      fu  
             ,fnd_user_resp_groups_indirect fri  
             ,fnd_responsibility_vl         resp  
             ,fnd_application_vl            app  
        WHERE fu.user_id           = fri.user_id  
        AND  fri.responsibility_id = resp.responsibility_id  
        AND  resp.application_id   = app.application_id)  
 WHERE 1=1  
 AND   user_name           = 'AJTEST'                -- Comment this if you need all user of a responsibility  
 AND   responsibility_name = 'System Administrator'; -- Comment this if you need all responsibilities of a user  

Feel free to point out if anything is missing/wrong in this blog.

MWA/MSCA: How to enable DFF on Reciept Information Page in WMS Mobile

In one of my project, the client wanted to enable the DFF (descriptive flexfield) on Receipt Info Page (last page during the Receiving Transaction). In WMS Mobile (MWA Framework) we could enable the DFF for LPN uisng MWA Personalization Architecture. 

Refer Oracle WMS Mobile Personalization

But the Receipt Information Page don't have a DFF field to personalize.

Navigation :

WMA Server Manager --> WMA Server Manager --> MWA Personalization Framework.

Solution:

We can enable the RCV_SHIPMENT_HEADERS DFF on the Receipt Information page by following the below steps.

Steps:

1. Define the DFF Segments for the DFF 'RCV_SHIPMENT_HEADERS'

2. Modify the Form Function (eg: INV_MOB_PO_RCPT [Mobile PO Receive]) and add ' SHOW_HEADER_DFF=YES' at the end of the Parameter. 

Now you can see the DFF field in the Receipt Information Page (last page on PO Receive transaction)

Related Links : MWA/MSCA Personalizations in Warehouse Management System (WMS)

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps: Setting NULL value for DFF segment on FND_LOOKUP

This blog is about setting NULL value for DFF segment on FND_LOOKUP from lookup form or using API.

Assume we have a DFF enabled on FND Lookup and the segment already have a value. Now we need to clear the value of that segment. What we normally do is open the Lookup and open the DFF values and delete the value from the Form for that segment and save the record. This will not clear the value, if you open the lookup again, you can still see the old value.

The reason for this is because, the underlying API uses a decode statement in the update procedure, which overides the NULL value with old value.

code snippet from package spec FND_LOOKUP_VALUES_PKG

 null_char varchar2(8) := '*NULL*';  

code snippet from package body FND_LOOKUP_VALUES_PKG

 l_null varchar2(20) := fnd_lookup_values_pkg.null_char;  

 update FND_LOOKUP_VALUES A  
   set  
   A.TAG = X_TAG,  
   A.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,  
   A.ATTRIBUTE1 = decode(x_attribute1,l_null,null, null,A.attribute1, x_attribute1) ,  
   A.ATTRIBUTE2 = decode(x_attribute2,l_null,null, null,A.attribute2, x_attribute2) ,  
   A.ATTRIBUTE3 = decode(x_attribute3,l_null,null, null,A.attribute3, x_attribute3) ,  
   A.ATTRIBUTE4 = decode(x_attribute4,l_null,null, null,A.attribute4, x_attribute4) ,  
   A.ATTRIBUTE5 = decode(x_attribute5,l_null,null, null,A.attribute5, x_attribute5) ,  
   A.ATTRIBUTE6 = decode(x_attribute6,l_null,null, null,A.attribute6, x_attribute6) ,  
   A.ATTRIBUTE7 = decode(x_attribute7,l_null,null, null,A.attribute7, x_attribute7) ,  
   A.ATTRIBUTE8 = decode(x_attribute8,l_null,null, null,A.attribute8, x_attribute8) ,  
   A.ATTRIBUTE9 = decode(x_attribute9,l_null,null, null,A.attribute9, x_attribute9) ,  
   A.ATTRIBUTE10 = decode(x_attribute10,l_null,null, null,A.attribute10, x_attribute10) ,  
   A.ATTRIBUTE11 = decode(x_attribute11,l_null,null, null,A.attribute11, x_attribute11) ,  
   A.ATTRIBUTE12 = decode(x_attribute12,l_null,null, null,A.attribute12, x_attribute12) ,  
   A.ATTRIBUTE13 = decode(x_attribute13,l_null,null, null,A.attribute13, x_attribute13) ,  
   A.ATTRIBUTE14 = decode(x_attribute14,l_null,null, null,A.attribute14, x_attribute14) ,  
   A.ATTRIBUTE15 = decode(x_attribute15,l_null,null, null,A.attribute15, x_attribute15) ,  
   A.ENABLED_FLAG = X_ENABLED_FLAG,  
   A.START_DATE_ACTIVE = X_START_DATE_ACTIVE,  
   A.END_DATE_ACTIVE = X_END_DATE_ACTIVE,  
   A.TERRITORY_CODE = X_TERRITORY_CODE,  
   A.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,  
   A.LAST_UPDATED_BY = X_LAST_UPDATED_BY,  
   A.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN  
  where A.LOOKUP_TYPE = X_LOOKUP_TYPE  
  and A.SECURITY_GROUP_ID = sgid  
  and A.VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID  
  and A.LOOKUP_CODE = X_LOOKUP_CODE;  

Explanation:

A.ATTRIBUTE1 = decode(x_attribute1,l_null,null, null,A.attribute1, x_attribute1)

If the new value is *NULL*, then set the value to null
If the new value is null, then set the value to old value
Else set the new value

Solution :

So to clear the DFF segment value on FND Lookup from the form, use the string *NULL*, instead of deleting the value.

Feel free to point out if anything is missing/wrong in this blog.