Oracle Apps: Reset FND User password from backend

Sample code to reset FND_USER password from backend

 declare   
      v_user_name varchar2(30)  := upper('AJ_TEST');   
      v_password  varchar2(30)  := 'johnytips'; -- new password  
      v_status    boolean;   
 begin   
      v_status := fnd_user_pkg.changepassword (   
                                    username    => v_user_name   
                                   ,newpassword => v_password );   
      if v_status = true then   
           dbms_output.put_line ('Password for '||v_user_name || ' got changed successfully !!!'); 
           commit;  
      else   
           dbms_output.put_line ('Unable to reset password  for '|| v_user_name ||' : '||SUBSTR(SQLERRM, 1, 1000));   
      end if;  
 end;  

Related blog: Oracle Apps: Create User and Add Responsibility from backend


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

PL/SQL: Extract XML Data using SQL

Few examples to extract xml data in a SQL query.

#1. Extract Master Child xml using xmltable
XMLTABLE

 select header_row.department_id
       ,header_row.department_name  
       ,child_row.employee_id  
       ,child_row.first_name
       ,child_row.last_name  
 from XMLTABLE(
               XMLNAMESPACES(default 'http://johnytips.blogspot.com.au/ns/department')  
              ,'/department'  
          PASSING xmltype(  
              '<department xmlns="http://johnytips.blogspot.com.au/ns/department" id="1">  
                    <department_name>Research</department_name>  
                    <employees>  
                         <employee id="1">  
                              <first_name>ANOOP</first_name>  
                              <last_name>JOHNY</last_name>  
                         </employee>  
                         <employee id="2">  
                              <first_name>ANISH</first_name>  
                              <last_name>JOHNY</last_name>  
                         </employee>  
                    </employees>  
               </department>')  
          COLUMNS   
               department_id   VARCHAR2(30) PATH '@id',  
               department_name VARCHAR2(10) PATH 'department_name',  
               child_rows XMLTYPE PATH 'employees'  
              ) header_row  
     ,XMLTABLE(
               XMLNAMESPACES(default 'http://johnytips.blogspot.com.au/ns/department')  
              ,'/employees/employee'  
          PASSING header_row.child_rows  
          COLUMNS  
               employee_id NUMBER PATH '@id',   
               first_name  VARCHAR2(30) PATH 'first_name',  
               last_name   VARCHAR2(30) PATH 'last_name'
              ) child_row;  

#2. EXTRACTVALUE

 with t as  
  (select   
          xmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 select extractvalue(str,'/employee/employee_id') EMPLOYEE_ID  
       ,extractvalue(str,'/employee/first_name')  FIRST_NAME  
       ,extractvalue(str,'/employee/last_name')   LAST_NAME  
 from t;  

#3.EXTRACT

 with t as  
  (select   
          xmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 select extract(str,'/employee/employee_id/text()') EMPLOYEE_ID  
       ,extract(str,'/employee/first_name/text()')  FIRST_NAME  
       ,extract(str,'/employee/last_name/text()')   LAST_NAME  
 from t;  

#4. To extract with the xml tag

 with t as  
  (select   
          xmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 select extract(str,'/employee/employee_id') EMPLOYEE_ID  
       ,extract(str,'/employee/first_name')  FIRST_NAME  
       ,extract(str,'/employee/last_name')   LAST_NAME  
 from t;  

#5. Prior to Oracle Database 10g Release 2 using xmlsequence
XMLSEQUENCE

 select extractvalue(column_value, '/employee/first_name') "FIRST_NAME"  
       ,extractvalue(column_value, '/employee/last_name')  "LAST_NAME"  
 from table(xmlsequence(xmltype('<employees>  
                                      <employee>  
                                           <employee_id>1</employee_id>  
                                           <first_name>ANOOP</first_name>  
                                           <last_name>JOHNY</last_name>  
                                      </employee>  
                                      <employee>   
                                           <employee_id>2</employee_id>  
                                           <first_name>ANISH</first_name>  
                                           <last_name>JOHNY</last_name>  
                                      </employee>  
                                 </employees>').extract('/employees/employee')));  

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

OAF: java.lang.NoClassDefFoundError: Could not initialize class oracle.apps.fnd.common.WebAppsContext

Error while trying to run a custom OA Page from Jdeveloper:

 500 Internal Server Error  
 java.lang.NoClassDefFoundError: Could not initialize class oracle.apps.fnd.common.WebAppsContext  
      at oracle.apps.fnd.framework.server.OAUtility.getWebAppsContext(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAServerDelegate.getWebAppsContext(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAJSPHelper.handleErrorStackDisplay(Unknown Source)  
      at _OAErrorPage._jspService(_OAErrorPage.java:135)  
      [/OAErrorPage.jsp]  
      at com.orionserver[Oracle Containers for J2EE 10g (10.1.3.5.0) ].http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)  
      at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:473)  
      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[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:734)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:280)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:68)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:214)  
      at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:219)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.EvermindPageContext.handlePageThrowable(EvermindPageContext.java:871)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.EvermindPageContext.handlePageException(EvermindPageContext.java:816)  
      at _runregion._jspService(_runregion.java:193)  
      [/runregion.jsp]  
      at com.orionserver[Oracle Containers for J2EE 10g (10.1.3.5.0) ].http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)  
      at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:473)  
      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[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:734)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:908)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:226)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.HttpRequestHandler.run(HttpRequestHandler.java:127)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.HttpRequestHandler.run(HttpRequestHandler.java:116)  
      at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)  
      at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)  
      at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)  
      at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)  
      at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:298)  
      at java.lang.Thread.run(Thread.java:662)  

In my case :

Problem : The dbc file was old and not updated.  

Solution : Get the latest dbc file from the server and copy it to your local folder and refer that in the Project properties. [Also verify the JDBC string in the DBC file and compare the database connection in Jdeveloper Database Connections window]

To get the location of dbc file in the server :

1. Navigate to any of the OA Page in the application.

2. Click on the 'About this page' link at the bottom.

3. Select the tab 'Java System Properties'.

 

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

Oracle Apps: Project Number disappear when the OTL Timecard is saved.

In one of the project We came across an issue in OTL Timecard. It took some time to finally figure out the reason, none of the google GOD or metalink notes helped. So I thought of sharing the details here, which might help someone.

The OTL Timecard was customized, but if the employee save the timecard before submitting, the Project Number gets disappeared from the screen. If he/she continue and submit the timecard without saving, it works fine. If he/she tries to update and existing timecard, then also the Project Number does not appear in the LOV field.

The customization done was as follows :

1. Expenditure Type was made hidden by modifying the Timecard Layout ldt file.
2. Customized the ProjectVO and added SYSLink and Expenditure Type to the Project VO and default the SysLinkFunc and ExpType when the Project Number is selected from the Project LOV.

The issue was the Sequence Number of the Expenditure Type field in the TIMECARD layout ldt. In the standard ldt, the sequence Numbers are as follows.

SYSTEMLINKAGEFUNCTION  --> 200
PROJECT                                  --> 210
TASK                                          --> 220
EXPENDITURETYPE                --> 230

Since the timecard is customized and the Expenditure is getting defaulted from the Project LOV the sequence number of component EXPENDITURETYPE should be less than the component PROJECT. Otherwise when the page tries to load the Project, the EXPENDITURETYPE component's value will come as NULL and the Project LOV will not return any value. The value of ExpenditureType field and the SysLink field will be added as a dynamic whereclause when the page tries to query the Project Number. This happens if the EXPTYPE and SYSLINKFUNC is specified in the QUALIFIER_ATTRIBUTE7.

So the solution was to change the sequence of EXPENDITURETYPE to 205.

Sample from the Layout ldt:
PROJECT:

 BEGIN HXC_LAYOUT_COMPONENTS "XXAJ Employee Timecard Layout - Project"  
      OWNER = "ORACLE12.1.3"  
      COMPONENT_VALUE = "XXAJPROJ"  
      REGION_CODE = "HXC_CUI_TIMECARD"  
      REGION_CODE_APP_SHORT_NAME = "HXC"  
      ATTRIBUTE_CODE = "HXC_TIMECARD_PROJECT"  
      ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC"  
      SEQUENCE = "210"  
      COMPONENT_DEFINITION = "LOV"  
      RENDER_TYPE = "WEB"  
      PARENT_COMPONENT = "XXAJ Employee Timecard Layout - Day Scope Building blocks for worker timecard matrix"  
      LAST_UPDATE_DATE = "2004/05/24"  
      BEGIN HXC_LAYOUT_COMP_QUALIFIERS "XXAJ Employee Timecard Layout - Project"  
           OWNER = "ORACLE12.1.3"  
           QUALIFIER_ATTRIBUTE_CATEGORY = "LOV"  
           QUALIFIER_ATTRIBUTE1 = "XXAJEmpProjVO"  
           QUALIFIER_ATTRIBUTE2 = "N"  
           QUALIFIER_ATTRIBUTE3 = "XXAJ_EMP_PROJ_LOV"  
           QUALIFIER_ATTRIBUTE4 = "809"  
           QUALIFIER_ATTRIBUTE5 = "12"  
           QUALIFIER_ATTRIBUTE6 = "HxcCuiProjectNumber|XXAJPROJ-DISPLAY|CRITERIA|N|HxcCuiProjectId|XXAJPROJ|RESULT|N|HxcCuiProjectNumber|XXAJPROJ-DISPLAY|RESULT|N|HxcCuiExptypeExpType|EXPTYPE|RESULT|N|HxcCuiExptypeSysLinkFunc|SYSLINKFUNC|RESULT|N"  
           QUALIFIER_ATTRIBUTE7 = "EXPTYPE|ExpType|SYSLINKFUNC|SysLink"  
           QUALIFIER_ATTRIBUTE8 = "ProjectNumber"  
           QUALIFIER_ATTRIBUTE9 = "ProjectId#NUMBER"  
           QUALIFIER_ATTRIBUTE10 = "xxaj.oracle.apps.hxc.selfservice.timecard.server.XXAJEmpProjVO"  
           QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_START_DATE|TIMECARD_BIND_START_DATE"  
           QUALIFIER_ATTRIBUTE17 = "OraTableCellText"  
           QUALIFIER_ATTRIBUTE20 = "N"  
           QUALIFIER_ATTRIBUTE21 = "Y"  
           QUALIFIER_ATTRIBUTE22 = "L"  
           QUALIFIER_ATTRIBUTE25 = "FLEX"  
           QUALIFIER_ATTRIBUTE26 = "PROJECTS"  
           QUALIFIER_ATTRIBUTE27 = "Attribute1"  
           QUALIFIER_ATTRIBUTE28 = "XXAJPROJ"  
           LAST_UPDATE_DATE = "2004/05/24"  
      END HXC_LAYOUT_COMP_QUALIFIERS  
 END HXC_LAYOUT_COMPONENTS  

EXPENDITURETYPE :

 BEGIN HXC_LAYOUT_COMPONENTS "XXAJ Employee Timecard Layout - Expenditure Type"  
      OWNER = "ORACLE12.1.3"  
      COMPONENT_VALUE = "EXPENDITURETYPE"  
      SEQUENCE = "205"  
      COMPONENT_DEFINITION = "HIDDEN_FIELD"  
      RENDER_TYPE = "WEB"  
      PARENT_COMPONENT = "XXAJ Employee Timecard Layout - Day Scope Building blocks for worker timecard matrix"  
      LAST_UPDATE_DATE = "2004/05/24"  
      BEGIN HXC_LAYOUT_COMP_QUALIFIERS "XXAJ Employee Timecard Layout - Expenditure Type"  
           OWNER = "ORACLE12.1.3"  
           QUALIFIER_ATTRIBUTE_CATEGORY = "HIDDEN_FIELD"  
           QUALIFIER_ATTRIBUTE18 = "EXCLUDE"  
           QUALIFIER_ATTRIBUTE19 = "|CSV|"  
           QUALIFIER_ATTRIBUTE20 = "N"  
           QUALIFIER_ATTRIBUTE21 = "Y"  
           QUALIFIER_ATTRIBUTE22 = "L"  
           QUALIFIER_ATTRIBUTE23 = "FORM"  
           QUALIFIER_ATTRIBUTE25 = "FLEX"  
           QUALIFIER_ATTRIBUTE26 = "PROJECTS"  
           QUALIFIER_ATTRIBUTE27 = "Attribute3"  
           QUALIFIER_ATTRIBUTE28 = "EXPTYPE"  
           LAST_UPDATE_DATE = "2004/05/24"  
      END HXC_LAYOUT_COMP_QUALIFIERS  
 END HXC_LAYOUT_COMPONENTS  

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

Oracle Apps: Create User and Add Responsibility from backend

Sample code to create a user (FND_USER) from backend and add responsibility.

 declare  
   v_user_name varchar2(30) :='AJ_TEST';   -- User Name  
   v_password  varchar2(30) :='johnytips';  -- Password  
   -- List of responsibilities to be added automatically  
   cursor cur_get_responsibilities  
   is  
     select resp.responsibility_key  
           ,resp.responsibility_name  
           ,app.application_short_name        
     from  fnd_responsibility_vl resp  
          ,fnd_application       app  
     where resp.application_id = app.application_id   
     and   resp.responsibility_name in ( 'System Administrator'  
                                        ,'Application Developer'  
                                        ,'Functional Administrator') ;  
 begin  
   fnd_user_pkg.createuser (  
           x_user_name             => upper(v_user_name)  
          ,x_owner                 => null  
          ,x_unencrypted_password  => v_password  
          ,x_session_number        => userenv('sessionid')  
          ,x_start_date            => sysdate  
          ,x_end_date              => null );  
   dbms_output.put_line ('User '||v_user_name||' created !!!!!');  
   for c_get_resp in cur_get_responsibilities   
   loop  
     fnd_user_pkg.addresp ( 
                username        => v_user_name  
               ,resp_app        => c_get_resp.application_short_name  
               ,resp_key        => c_get_resp.responsibility_key  
               ,security_group  => 'STANDARD'  
               ,description     => null  
               ,start_date      => sysdate  
               ,end_date        => null);  
     dbms_output.put_line('Responsibility '||c_get_resp.responsibility_name||' added !!!!!!');    
   end loop;  
   commit;  
 exception  
   when others then  
   dbms_output.put_line ('Exception : '||SUBSTR(SQLERRM, 1, 500));  
   rollback;  
 end;  

Related blog: Oracle Apps: Reset FND User password from backend


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

Oracle Apps: How to set Profile Option Value from backend

In this post, I will just post a sample code to set a profile option value from the backend.

This is the sample screen shot of the Profile Option Definition.
Navigation :- Application Developer --> Profile --> System

To set the profile option value, use the below code.

 declare  
   lb_return boolean;  
 begin  
   -- To set the Profile value at site level  
   lb_return := fnd_profile.SAVE ('XXAJ_TEST', 'S', 'SITE');
  
   --To set the Profile value at user Level -- UserName:-348019  
   lb_return := fnd_profile.SAVE ('XXAJ_TEST', 'U', 'USER',146356);
  
   --To set the Profile value at user Level -- Application Name :-General Ledger  
   lb_return := fnd_profile.SAVE ('XXAJ_TEST', 'A', 'APPL',101);
  
   --To set the Profile value at user Level -- Responsibility Name :-General Ledger  
   lb_return := fnd_profile.SAVE ('XXAJ_TEST', 'R', 'RESP', 20434, 101);
  
   --Commit the changes  
   commit;
  
 end;  

After executing the above code:

SAVE procedure:

   /*  
   ** save  
   **  Saves the value of a profile option permanently to the database, at any  
   **  level using the FND_PROFILE_OPTION_VALUES_PKG. This routine can be used  
   **  at runtime or during patching. This routine will not actually commit  
   **  the changes; the caller must commit.  
   **  
   **  ('SITE', 'APPL', 'RESP', 'USER', 'SERVER', 'ORG', or 'SERVRESP').  
   **  
   **  Examples of use:  
   **    FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');  
   **    FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'APPL', 321532);  
   **    FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'RESP', 321532, 345234);  
   **    FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', 123321);  
   **    FND_PROFILE.SAVE('P_NAME', 'SERVER', 25);  
   **    FND_PROFILE.SAVE('P_NAME', 'ORG', 204);  
   **    FND_PROFILE.SAVE('P_NAME', 'SERVRESP', 321532, 345234, 25);  
   **    FND_PROFILE.SAVE('P_NAME', 'SERVRESP', 321532, 345234, -1);  
   **    FND_PROFILE.SAVE('P_NAME', 'SERVRESP', -1, -1, 25);  
   **  
   **  Arguments/Parameters:  
   **    profileName    - Profile name you are setting  
   **    profileValue   - Profile value you are setting  
   **    levelName     - Level that you're setting at:  
   **              'SITE','APPL','RESP','USER', etc.  
   **    levelValue    - context value used for setting profile option  
   **              value, e.g. user_id for 'USER' level. This does  
   **              not apply to 'SITE' level.  
   **    levelValueAppId  - applies to 'RESP' and 'SERVRESP' levels, i.e.  
   **              the Resp Application_Id.  
   **    levelValue2    - 2nd context value used for setting profile  
   **              option value. This applies to the 'SERVRESP'  
   **              hierarchy.  
   **  
   ** RETURNS: TRUE if successful, FALSE if failure.  
   */  
   FUNCTION SAVE(x_name IN VARCHAR2,  
          /* Profile name you are setting */  
          x_value IN VARCHAR2,  
          /* Profile value you are setting */  
          x_level_name IN VARCHAR2,  
          /* Level that you're setting at: 'SITE','APPL','RESP','USER',  
                   etc. */  
          x_level_value IN VARCHAR2 DEFAULT NULL,  
          /* Level value that you are setting at, e.g. user id for 'USER'  
                   level. X_LEVEL_VALUE is not used at site level. */  
          x_level_value_app_id IN VARCHAR2 DEFAULT NULL,  
          /* Used for 'RESP' and 'SERVRESP' level; Resp Application_Id. */  
          x_level_value2 IN VARCHAR2 DEFAULT NULL  
          /* 2nd Level value that you are setting at. This is for the  
                   'SERVRESP' hierarchy. */)  

Related links : Oracle Apps: Query to get the profile option values from backend



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

Oracle Apps: Helpful Queries on Concurrent Programs / Concurrent Requests

The intention if this post is to post some of the queries which will be helpful when querying on Concurrent Programs / Request.

#1. Query to find the Run Timing for the Concurrent Request.

select fcr.request_id  
      ,fcp.user_concurrent_program_name  
      ,fcp.concurrent_program_name  
      ,fcr.request_date  
      ,fcr.actual_start_date  
      ,fcr.actual_completion_date  
      ,(  
        (floor(((fcr.actual_completion_date - fcr.actual_start_date)*24))      || 'hr ') ||  
        (floor(((fcr.actual_completion_date - fcr.actual_start_date)*24*60))   || 'min ')||  
        (round(((fcr.actual_completion_date - fcr.actual_start_date)*24*60*60))|| 'sec')  
       ) duration  
      ,fcr.status_code  
      ,flvs.meaning status  
      ,fcr.phase_code  
      ,flvp.meaning phase  
      ,fcr.completion_text  
      ,fcr.argument_text  
      ,fcr.logfile_name  
      ,fcr.outfile_name  
from apps.fnd_concurrent_programs_vl fcp  
    ,apps.fnd_concurrent_requests    fcr  
    ,apps.fnd_lookup_values          flvs  
    ,apps.fnd_lookup_values          flvp  
where fcr.concurrent_program_id = fcp.concurrent_program_id(+)  
and   flvs.lookup_code          = fcr.status_code  
and   flvs.lookup_type          = 'CP_STATUS_CODE'  
and   flvs.language             = 'US'  
and   flvs.view_application_id  = 0  
and   flvp.lookup_code          = fcr.phase_code  
and   flvp.lookup_type          = 'CP_PHASE_CODE'  
and   flvp.language             = 'US'  
and   flvp.view_application_id  = 0;  

#2. Query to find the details of the Scheduled Concurrent Requests & Request Sets including the programs under the Request Set.

select request_id  
      ,conc_prog_name  
      ,params  
      ,prog_schedule_type  
      ,prog_schedule  
      ,user_name  
      ,requested_start_date   
 from (  
    select fcr.request_id  
          ,1 seq  
          ,decode(fcpt.user_concurrent_program_name,  
                  'Report Set','Report Set:' || fcr.description,  
                  fcpt.user_concurrent_program_name) conc_prog_name  
          ,(fcr.argument1||','||fcr.argument2||','||fcr.argument3||','||fcr.argument4||','||fcr.argument5||','||  
            fcr.argument6||','||fcr.argument7||','||fcr.argument8||','||fcr.argument9||','||fcr.argument10) params -- Add more parameters if needed or use column 'argument_text'  
          ,nvl2(fcr.resubmit_interval,'Periodically',nvl2(fcr.release_class_id, 'On Specific Days', 'Once')) prog_schedule_type  
          ,(case nvl2(fcr.resubmit_interval,'PERIODICALLY',nvl2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE'))  
                 when 'PERIODICALLY'  
                     then 'EVERY ' || fcr.resubmit_interval || ' ' || fcr.resubmit_interval_unit_code || ' FROM ' ||fcr.resubmit_interval_type_code || ' OF PREV RUN'  
                 when 'ONCE'  
                     then 'AT :' ||to_char(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI')  
                 else  
                     'EVERY: ' || fcrc.class_info  
            end) prog_schedule  
          ,fu.user_name user_name  
          ,to_char(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI') requested_start_date  
     from apps.fnd_concurrent_programs_tl fcpt  
         ,apps.fnd_concurrent_requests    fcr  
         ,apps.fnd_user                   fu  
         ,apps.fnd_conc_release_classes   fcrc  
     where fcpt.application_id        = fcr.program_application_id  
     and   fcpt.concurrent_program_id = fcr.concurrent_program_id  
     and   fcr.requested_by           = fu.user_id  
     and   fcr.phase_code             = 'P'  
     and   fcr.requested_start_date   > sysdate  
     and   fcpt.language              = 'US'  
     and   fcrc.release_class_id(+)   = fcr.release_class_id  
     and   fcrc.application_id(+)     = fcr.release_class_app_id  
     union  
     select fcr.request_id  
           ,2 seq  
           ,'-->' || fcp.user_concurrent_program_name conc_prog_name  
           ,(frr.argument1||','||frr.argument2||','||frr.argument3||','||frr.argument4||','||frr.argument5||','||  
             frr.argument6||','||frr.argument7||','||frr.argument8||','||frr.argument9||','||frr.argument10) params -- Add more parameters if needed  
           ,null prog_schedule_type  
           ,null prog_schedule  
           ,null user_name  
           ,null requested_start_date  
     from apps.fnd_concurrent_programs_tl fcpt
         ,apps.fnd_concurrent_requests    fcr
         ,apps.fnd_user                   fu
         ,apps.fnd_conc_release_classes   fcrc  
         ,apps.fnd_run_requests           frr
         ,apps.fnd_concurrent_programs_tl fcp  
     where fcpt.application_id               = fcr.program_application_id  
     and   fcpt.concurrent_program_id        = fcr.concurrent_program_id  
     and   fcr.requested_by                  = fu.user_id  
     and   fcr.phase_code                    = 'P'  
     and   fcr.requested_start_date          > sysdate  
     and   fcpt.language                     = 'US'  
     and   fcrc.release_class_id(+)          = fcr.release_class_id  
     and   fcrc.application_id(+)            = fcr.release_class_app_id  
     and   fcpt.user_concurrent_program_name = 'Report Set'  
     and   frr.parent_request_id             = fcr.request_id  
     and   frr.concurrent_program_id         = fcp.concurrent_program_id  
 ) qrslt  
 order by request_id,seq;  

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

Oracle Apps: Helpful Queries on FND Attachments tables and sample code to extract attachments

This post gives some helpful queries when working on FND Attachment tables. I have also given a sample program to extract these attachment into file system.

Attachment Types are stored in the fnd_document_datatypes table.

select * from fnd_document_datatypes;

Query to retrieve the 'Short Text' Attachments:

 select fad.document_id  
       ,fad.entity_name  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,fds.short_text  
       ,(fad.entity_name || '_' || fad.document_id ||'_ST.txt') file_name  
 from fnd_attached_documents fad  
     ,fnd_documents fd  
     ,fnd_documents_short_text fds
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct  
 where fad.document_id = fd.document_id  
 and   fd.media_id     = fds.media_id  
 and   fd.datatype_id  = fdd.datatype_id  
 and   fd.category_id  = fdct.category_id
 and   fdd.user_name   = 'Short Text'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Query to retrieve the 'Long Text' Attachments:

 select fad.entity_name  
       ,fad.document_id  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,fdl.long_text  
       ,(fad.entity_name || '_' || fad.document_id ||'_LT.txt') file_name  
 from fnd_attached_documents fad  
     ,fnd_documents fd  
     ,fnd_documents_long_text fdl
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct    
 where fad.document_id = fd.document_id  
 and   fd.media_id     = fdl.media_id  
 and   fd.datatype_id  = fdd.datatype_id
 and   fd.category_id  = fdct.category_id  
 and   fdd.user_name   = 'Long Text'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Query to retrieve the 'File' Attachments:

 select fad.entity_name   
       ,fad.document_id  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,fd.datatype_id  
       ,(fad.entity_name || '_' || fad.document_id ||'_' || fl.file_name) file_name  
       ,fl.file_data  
 from fnd_attached_documents fad  
     ,fnd_documents fd  
     ,fnd_lobs fl
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct    
 where fad.document_id = fd.document_id  
 and   fd.media_id     = fl.file_id  
 and   fd.datatype_id  = fdd.datatype_id
 and   fd.category_id  = fdct.category_id  
 and   fdd.user_name   = 'File'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Query to retrieve the 'Web Page' Attachments:

 select fad.entity_name  
       ,fad.document_id  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,(fad.entity_name || '_' || fad.document_id ||'_URL.txt') file_name  
       ,fd.url  
 from fnd_attached_documents fad  
     ,fnd_documents fd
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct    
 where fad.document_id = fd.document_id  
 and   fd.datatype_id  = fdd.datatype_id
 and   fd.category_id  = fdct.category_id  
 and   fdd.user_name   = 'Web Page'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Sample program to extract the attachments into file system.

declare  
  v_file     utl_file.file_type;   
  v_line     varchar2(1000);   
  v_blob_len number;  
  v_pos      number;  
  v_buffer   raw(32764);  
  v_amt      binary_integer := 32764;
  
  cursor cur_short_text_files  
  is  
    select fad.entity_name   
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,fds.short_text   
          ,(fad.entity_name || '_' || fad.document_id ||'_ST.txt') file_name   
    from fnd_attached_documents fad   
        ,fnd_documents fd   
        ,fnd_documents_short_text fds  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and   fd.media_id     = fds.media_id   
    and   fd.datatype_id  = fdd.datatype_id   
    and   fdd.user_name   = 'Short Text'   
    and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
  
  cursor cur_long_text_files  
  is  
    select fad.entity_name   
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,fdl.long_text   
          ,(fad.entity_name || '_' || fad.document_id ||'_LT.txt') file_name   
    from fnd_attached_documents fad   
        ,fnd_documents fd   
        ,fnd_documents_long_text fdl  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and  fd.media_id      = fdl.media_id   
    and  fd.datatype_id   = fdd.datatype_id   
    and  fdd.user_name    = 'Long Text'   
    and  fad.entity_name  = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
   
  cursor cur_files  
  is  
    select fad.entity_name    
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,fd.datatype_id   
          ,(fad.entity_name || '_' || fad.document_id ||'_' || fl.file_name) file_name   
          ,fl.file_data   
    from fnd_attached_documents fad   
        ,fnd_documents fd   
        ,fnd_lobs fl  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and   fd.media_id     = fl.file_id   
    and   fd.datatype_id  = fdd.datatype_id   
    and   fdd.user_name   = 'File'   
    and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
  
  cursor cur_url_files  
  is  
    select fad.entity_name   
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,(fad.entity_name || '_' || fad.document_id ||'_URL.txt') file_name   
          ,fd.url   
    from fnd_attached_documents fad   
        ,fnd_documents fd  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and  fd.datatype_id   = fdd.datatype_id   
    and  fdd.user_name    = 'Web Page'   
    and  fad.entity_name  = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
  
begin
  
  -- Short Text Attachments   
  for c_file in cur_short_text_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'W', 32764);  
    utl_file.put(v_file,c_file.short_text);  
    utl_file.fclose(v_file);   
  end loop;
  
  -- Long Text Attachments   
  for c_file in cur_long_text_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'W', 32764);  
    utl_file.put(v_file,c_file.long_text);  
    utl_file.fclose(v_file);   
  end loop;
  
  -- File Attachments  
  for c_file in cur_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'wb', 32764);  
    v_blob_len := dbms_lob.getlength(c_file.file_data);  
    v_pos := 1;  
    while v_pos < v_blob_len  
    loop  
      dbms_lob.read(c_file.file_data,v_amt,v_pos,v_buffer);  
      utl_file.put_raw(v_file,v_buffer,true);  
      v_pos := v_pos + v_amt;  
    end loop;   
    utl_file.fclose(v_file);   
  end loop;
  
  --Web Page Attachments  
  for c_file in cur_url_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'W', 32764);  
    utl_file.put(v_file,c_file.url);  
    utl_file.fclose(v_file);   
  end loop;       
end;  

Note: The oracle directory object 'XX_EXTRACT_DIR' should be created before running the script. Refer the below blog to see how to use utl_file package.
Johny's Oracle Tips: PL/SQL: Sample code for UTL_FILE



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