Oracle Apps: Sample Concurrent Program Shell script to call multiple SQL scripts

In one of my project, the client to wanted to have a Host Concurrent Program, which calls multiple sql statements. It was easy to create one shell script which calls multiple sql statements. But the client wanted the concurrent program to fail (complete in ERROR status), if any of the sql statements fail (not just the last one).

A sample code which we used in this scenario:

 #!/bin/ksh  
 
 #Do whatever you want here  
 echo Connecting to apps...  
 
 sql_output=`sqlplus -s /nolog <<EOF  
 
 connect apps/$PASSWORD  
 
 prompt getting sysdate value into a variable...  
 COLUMN today NOPRINT NEW_VALUE g_today  
 SELECT to_char(sysdate,'DD-MON-YYYY') today  
 FROM dual;  
 
 set serveroutput on size 1000000  
 
 prompt get the count from debug table...  
 select count(*) from xxaj_debug;  
 
 prompt truncate debug table...  
 truncate table xxaj_debug1;  
 
 prompt populate temporary table...  
 insert into xxaj_debug values (xxaj_seq.nextval, 'Anoop', sysdate);   
 
 update xxaj_debug  
 set msg = 'Anoop Johny';  
 
 COLUMN old_count NOPRINT NEW_VALUE g_old_count  
 select count(*) old_count from xxaj_debug;  
 delete from xxaj_debug;  
 
 EOF`  
 
 if [ `echo "$sql_output" | egrep 'ORA-[0-9]{5}:' | wc -l` -ne 0 ]   
 then   
 echo "$sql_output"   
 exit 1   
 else   
 echo "$sql_output"   
 exit 0  
 fi   

The above script with exit with status 0 if every sql statement is success.

Now if any of the statement fails, it will return with status 1.

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

Oracle Apps: Organization Hierarchy Query to get the organizations in a specific Hierarchy

The below query can be used to get the Organizations which belongs to a hierarchy which is defined in the Organization Hierarchy.

Thanks to Sajeer Omar.

Replace the below hardcoded values before executing :

Business group ID

Organization Hierarchy Name

Top Organization Name

If you want to get only the organization as a specific level, uncomment the commented whereclause.

 select (lpad(' ', (lvl - 1) * 5, ' ') || haou_c.name) org_name  
        ,org_hier.*  
 from  hr_all_organization_units haou_c  
      ,(select hier.organization_id_parent  
              ,hier.organization_id_child  
              ,level lvl  
              ,sys_connect_by_path(hier.organization_id_child, '/') org_level  
              ,sys_connect_by_path(level, '/')                      hier_level  
        from  per_org_structure_elements hier  
             ,(select vers.org_structure_version_id  
               from  per_organization_structures_v struct  
                    ,per_org_structure_versions_v  vers  
               where struct.business_group_id = 0 -- Business group ID  
               and  struct.name = 'AJ Hierarchy' -- Organization Hierarchy Name  
               and  struct.organization_structure_id = vers.organization_structure_id  
               and  struct.business_group_id = vers.business_group_id  
               and  SYSDATE BETWEEN vers.date_from and nvl(vers.date_to, sysdate)  
               and  rownum = 1) orgstr  
        where hier.org_structure_version_id = orgstr.org_structure_version_id  
        and  hier.business_group_id = 0 -- Business group ID  
        start with hier.organization_id_parent =  
                                                (select a.organization_id  
                                                 from  hr_all_organization_units a  
                                                 where a.name = 'AJ Pty Ltd (Australia)') -- Top Organization Name  
              and  hier.org_structure_version_id = orgstr.org_structure_version_id  
              and  hier.business_group_id = 0 -- Business group ID  
        connect by prior organization_id_child = organization_id_parent) org_hier  
 where haou_c.organization_id = org_hier.organization_id_child  
 --and  org_hier.lvl = 2 -- If you want to get only a specific level values  
 order by org_hier.org_level;  

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

OAF: oracle.apps.fnd.framework.OAException: oracle.jbo.NameClashException: JBO-25001: Name LOV_oracle_apps_xx_xxx_server_XXXXXXXAM of object type ApplicationModule already exists

In one of my project, the client was getting the below error when opening an OA Page. Since the issue was not a straight forward case, I thought of adding the issue to the blogpost.

Error when opening the OA Page:

 oracle.apps.fnd.framework.OAException: oracle.jbo.NameClashException: JBO-25001: Name LOV_oracle_apps_xx_xxx_server_XXXXXXXAM of object type ApplicationModule already exists  
         at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1247)  
         at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435)  
         at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2736)  
         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1963)  
         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:557)  
         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:439  
         at _OA._jspService(_OA.java:213)  
         at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)  
         at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390)  
         at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)  
         at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)  
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)  
         at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)  
         at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)  
         at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)  
         at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:429)  
         at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:642)  
         at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)  
         at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:911)  
         at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)  
         at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)  
         at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)  
         at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)  
         at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)  
         at java.lang.Thread.run(Thread.java:745)  
 ## Detail 0 ##  
 oracle.apps.fnd.framework.OAException: oracle.jbo.NameClashException: JBO-25001: Name LOV_oracle_apps_xx_xxx_server_XXXXXXXAM of object type ApplicationModule already exists  
         at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)  
         at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1169)  
         at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435)  
         at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2736)  
         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1963)  
         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:557)  
         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:439)  
         at _OA._jspService(_OA.java:213)  
         at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)  
         at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390)  
         at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)  
         at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)  
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)  
         at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)  
         at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)  
         at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)  
         at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:429)  
         at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:642)  
         at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)  
         at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:911)  
         at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)  
         at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)  
         at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)  
         at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)  
         at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)  
         at java.lang.Thread.run(Thread.java:745)  
 oracle.apps.fnd.framework.OAException: oracle.jbo.NameClashException: JBO-25001: Name LOV_oracle_apps_xx_xxx_server_XXXXXXXAM of object type ApplicationModule already exists  
         at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)  
         at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1169)  
         at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435)  
         at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2736)  
         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1963)  
         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:557)  
         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:439)  
         at _OA._jspService(_OA.java:213)  
         at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)  
         at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:390)  
         at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)  
         at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)  
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)  
         at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)  
         at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)  
         at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)  
         at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:429)  
         at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:642)  
         at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)  
         at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:911)  
         at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)  
         at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)  
         at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)  
         at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)  
         at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)  
         at java.lang.Thread.run(Thread.java:745)  

In this case : 

Problem : The specified AM,  oracle.apps.xx.xxx.server.XXXXXXXAM, ( which was an AM associated with an external LOV region in the page) was substituted in the Application for another page. AM Substitution is like a site level Personalization as it applies to all the pages where the AM is referenced.

Solution : Remove the AM Substitution.

Note: In this specific case, we were able to replace the AM Substitution with just VO Substitution.

I have seen several cases, where AM Substitution is done, things start failing in the page. Especially, if there is a LOV in the page, you might get different types of errors which will be difficult to debug.



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

PL/SQL: Some helpful SQL queries -2

Continuation of PL/SQL: Some helpful SQL queries -1

#7. How to convert Multiple Columns into Rows (Unpivot multiple columns). 
Source Data

Expected output

 with t as  
 (select '1001' emp_no,'Anoop' emp_first_name, 'Johny' emp_last_name
        ,'Developer' job_role, 'Senior Developer' new_job_role  
        ,'150000'    salary  , '175000'           new_salary 
        ,'Australia' country , 'Australia'        new_country  
  from dual  
  union  
  select '1002' emp_no,'Sajeer' emp_first_name, 'Omar' emp_last_name
       ,'Senior Developer' job_role, 'Developer' new_job_role  
       ,'105000'           salary  , '100000'    new_salary 
       ,'Australia'        country , 'Australia' new_country   
  from dual  
 )
 select (case when row_number() over (partition by emp_no order by emp_no ) = 1    
              then emp_no   
         end) emp_no  
       ,(case when row_number() over (partition by emp_no order by emp_no ) = 1    
              then emp_first_name   
         end) emp_first_name  
       ,(case when row_number() over (partition by emp_last_name order by emp_no ) = 1    
              then emp_last_name   
         end) emp_last_name   
       ,col     detail  
       ,val     old_value  
       ,new_val new_value  
 from t    
 unpivot include nulls ((val,new_val) for col in ((job_role,new_job_role) as 'Job Role'  
                                                 ,(salary,new_salary)     as 'Salary'  
                                                 ,(country,new_country)   as 'Country'));  

Refer:
PIVOT and UNPIVOT Operators in Oracle Database 11g Release 
Pivot and Unpivot

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

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.