PL/SQL: Disable formatting by PLSQL Developer Beautifier for a block

This post is just to highlight the syntax to supress formatting for the PLSQL Beautifier.

You can use the NoFormat Start and NoFormat End directives to suppress the formatting by the beautifier.

 -- NoFormat Start  
      <<Code goes here>>  
 -- NoFormat End       

From the PLSQL Developer Manual.

OAF: oracle.apps.fnd.framework.OAException: oracle.jbo.AttributeLoadException: JBO-27021: Failed to load CustomDatum value at index

In one of my project, the client was getting the below error when opening an OA Page after applying some patchset. Since I have seen this several time and most of the time the reason was the same, I thought of adding this to my blogpost.

 Exception Details.   
  oracle.apps.fnd.framework.OAException: oracle.jbo.AttributeLoadException: JBO-27021: Failed to load CustomDatum value at index 45 with java
 object of type oracle.jbo.domain.Number due to java.sql.SQLException.  
      at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)  
      at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)  
      at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)  
      at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)  
      at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)  
      at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:783)  
      at oracle.apps.icx.por.wf.webui.ReqLinesNotificationsCO.processRequest(ReqLinesNotificationsCO.java:164)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:631)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1386)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)  
      at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2883)  
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:2060)  
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:582)  
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:470)  
      at _pages.__oa._jspService(__oa.java:233)  
      at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)  
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)  
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)  
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)  
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at weblogic.servlet.internal.RequestDispatcherImpl.invokeServlet(RequestDispatcherImpl.java:527)  
      at weblogic.servlet.internal.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:253)  
      at weblogic.servlet.jsp.PageContextImpl.forward(PageContextImpl.java:159)  
      at _pages.__oa._jspService(__oa.java:246)  
      at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)  
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)  
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)  
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)  
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at weblogic.servlet.internal.RequestDispatcherImpl.invokeServlet(RequestDispatcherImpl.java:527)  
      at weblogic.servlet.internal.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:253)  
      at weblogic.servlet.jsp.PageContextImpl.forward(PageContextImpl.java:159)  
      at _pages.__oa._jspService(__oa.java:246)  
      at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)  
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)  
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)  
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)  
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.fnd.security.WLFilter.doFilter(WLFilter.java:213)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(Unknown Source)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:432)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119)  
      at java.security.AccessController.doPrivileged(Native Method)  
      at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:315)  
      at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:442)  
      at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103)  
      at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171)  
      at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:139)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3748)  
      at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3714)  
      at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)  
      at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)  
      at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2283)  
      at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2182)  
      at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1499)  
      at weblogic.work.ExecuteThread.execute(ExecuteThread.java:263)  
      at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)  
 ## Detail 0 ##  
 java.sql.SQLException: Invalid column index  
      at oracle.jdbc.driver.OracleResultSetImpl.getBytes(OracleResultSetImpl.java:1494)  
      at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl.getBytes(Unknown Source)  
      at oracle.jbo.domain.Number$1facClass.createDatum(Number.java:103)  
      at oracle.jbo.server.OracleSQLBuilderImpl.doLoadFromResultSet(OracleSQLBuilderImpl.java:1196)  
      at oracle.jbo.server.AttributeDefImpl.loadFromResultSet(AttributeDefImpl.java:1634)  
      at oracle.jbo.server.ViewRowImpl.populate(ViewRowImpl.java:2221)  
      at oracle.jbo.server.ViewDefImpl.createInstanceFromResultSet(ViewDefImpl.java:1073)  
      at oracle.jbo.server.ViewObjectImpl.createRowFromResultSet(ViewObjectImpl.java:3045)  
      at oracle.jbo.server.ViewObjectImpl.createInstanceFromResultSet(ViewObjectImpl.java:2917)  
      at oracle.jbo.server.QueryCollection.populateRow(QueryCollection.java:2398)  
      at oracle.jbo.server.QueryCollection.fetch(QueryCollection.java:2256)  
      at oracle.jbo.server.QueryCollection.get(QueryCollection.java:1504)  
      at oracle.jbo.server.ViewRowSetImpl.getRow(ViewRowSetImpl.java:3705)  
      at oracle.jbo.server.ViewRowSetIteratorImpl.doFetch(ViewRowSetIteratorImpl.java:2815)  
      at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(ViewRowSetIteratorImpl.java:2671)  
      at oracle.jbo.server.ViewRowSetIteratorImpl.refresh(ViewRowSetIteratorImpl.java:2912)  
      at oracle.jbo.server.ViewRowSetImpl.notifyRefresh(ViewRowSetImpl.java:2094)  
      at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:782)  
      at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:900)  
      at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:814)  
      at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:808)  
      at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3674)  
      at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:441)  
      at xxxx.oracle.apps.icx.por.wf.server.ReqLinesNotificationsVOExtImpl.executeQuery(ReqLinesNotificationsVOExtImpl.java:17)  
      at oracle.apps.icx.por.wf.server.ReqLinesNotificationsVOImpl.initQuery(ReqLinesNotificationsVOImpl.java:111)  
      at oracle.apps.icx.por.wf.server.ReqApprovalNotificationsAMImpl.initReqLinesNotfnVOQry(ReqApprovalNotificationsAMImpl.java:85)  
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)  
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
      at java.lang.reflect.Method.invoke(Method.java:606)  
      at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)  
      at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)  
      at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:783)  
      at oracle.apps.icx.por.wf.webui.ReqLinesNotificationsCO.processRequest(ReqLinesNotificationsCO.java:164)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:631)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1386)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)  
      at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2883)  
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:2060)  
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:582)  
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:470)  
      at _pages.__oa._jspService(__oa.java:233)  
      at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)  
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)  
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)  
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)  
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at weblogic.servlet.internal.RequestDispatcherImpl.invokeServlet(RequestDispatcherImpl.java:527)  
      at weblogic.servlet.internal.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:253)  
      at weblogic.servlet.jsp.PageContextImpl.forward(PageContextImpl.java:159)  
      at _pages.__oa._jspService(__oa.java:246)  
      at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)  
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)  
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)  
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)  
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at weblogic.servlet.internal.RequestDispatcherImpl.invokeServlet(RequestDispatcherImpl.java:527)  
      at weblogic.servlet.internal.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:253)  
      at weblogic.servlet.jsp.PageContextImpl.forward(PageContextImpl.java:159)  
      at _pages.__oa._jspService(__oa.java:246)  
      at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)  
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)  
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)  
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)  
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.fnd.security.WLFilter.doFilter(WLFilter.java:213)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(Unknown Source)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:432)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119)  
      at java.security.AccessController.doPrivileged(Native Method)  
      at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:315)  
      at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:442)  
      at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103)  
      at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171)  
      at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:139)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3748)  
      at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3714)  
      at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)  
      at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)  
      at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2283)  
      at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2182)  
      at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1499)  
      at weblogic.work.ExecuteThread.execute(ExecuteThread.java:263)  
      at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)  
 java.sql.SQLException: Invalid column index  
      at oracle.jdbc.driver.OracleResultSetImpl.getBytes(OracleResultSetImpl.java:1494)  
      at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl.getBytes(Unknown Source)  
      at oracle.jbo.domain.Number$1facClass.createDatum(Number.java:103)  
      at oracle.jbo.server.OracleSQLBuilderImpl.doLoadFromResultSet(OracleSQLBuilderImpl.java:1196)  
      at oracle.jbo.server.AttributeDefImpl.loadFromResultSet(AttributeDefImpl.java:1634)  
      at oracle.jbo.server.ViewRowImpl.populate(ViewRowImpl.java:2221)  
      at oracle.jbo.server.ViewDefImpl.createInstanceFromResultSet(ViewDefImpl.java:1073)  
      at oracle.jbo.server.ViewObjectImpl.createRowFromResultSet(ViewObjectImpl.java:3045)  
      at oracle.jbo.server.ViewObjectImpl.createInstanceFromResultSet(ViewObjectImpl.java:2917)  
      at oracle.jbo.server.QueryCollection.populateRow(QueryCollection.java:2398)  
      at oracle.jbo.server.QueryCollection.fetch(QueryCollection.java:2256)  
      at oracle.jbo.server.QueryCollection.get(QueryCollection.java:1504)  
      at oracle.jbo.server.ViewRowSetImpl.getRow(ViewRowSetImpl.java:3705)  
      at oracle.jbo.server.ViewRowSetIteratorImpl.doFetch(ViewRowSetIteratorImpl.java:2815)  
      at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(ViewRowSetIteratorImpl.java:2671)  
      at oracle.jbo.server.ViewRowSetIteratorImpl.refresh(ViewRowSetIteratorImpl.java:2912)  
      at oracle.jbo.server.ViewRowSetImpl.notifyRefresh(ViewRowSetImpl.java:2094)  
      at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:782)  
      at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:900)  
      at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:814)  
      at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:808)  
      at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3674)  
      at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:441)  
      at xxxx.oracle.apps.icx.por.wf.server.ReqLinesNotificationsVOExtImpl.executeQuery(ReqLinesNotificationsVOExtImpl.java:17)  
      at oracle.apps.icx.por.wf.server.ReqLinesNotificationsVOImpl.initQuery(ReqLinesNotificationsVOImpl.java:111)  
      at oracle.apps.icx.por.wf.server.ReqApprovalNotificationsAMImpl.initReqLinesNotfnVOQry(ReqApprovalNotificationsAMImpl.java:85)  
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)  
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
      at java.lang.reflect.Method.invoke(Method.java:606)  
      at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)  
      at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)  
      at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:783)  
      at oracle.apps.icx.por.wf.webui.ReqLinesNotificationsCO.processRequest(ReqLinesNotificationsCO.java:164)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:631)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(OAPageLayoutHelper.java:1386)  
      at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(OAPageLayoutBean.java:1569)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(Unknown Source)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1003)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:970)  
      at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:690)  
      at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)  
      at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)  
      at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2883)  
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:2060)  
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:582)  
      at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:470)  
      at _pages.__oa._jspService(__oa.java:233)  
      at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)  
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)  
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)  
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)  
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at weblogic.servlet.internal.RequestDispatcherImpl.invokeServlet(RequestDispatcherImpl.java:527)  
      at weblogic.servlet.internal.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:253)  
      at weblogic.servlet.jsp.PageContextImpl.forward(PageContextImpl.java:159)  
      at _pages.__oa._jspService(__oa.java:246)  
      at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)  
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)  
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)  
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)  
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at weblogic.servlet.internal.RequestDispatcherImpl.invokeServlet(RequestDispatcherImpl.java:527)  
      at weblogic.servlet.internal.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:253)  
      at weblogic.servlet.jsp.PageContextImpl.forward(PageContextImpl.java:159)  
      at _pages.__oa._jspService(__oa.java:246)  
      at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)  
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)  
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)  
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)  
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.fnd.security.WLFilter.doFilter(WLFilter.java:213)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(Unknown Source)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:432)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119)  
      at java.security.AccessController.doPrivileged(Native Method)  
      at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:315)  
      at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:442)  
      at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103)  
      at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171)  
      at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:139)  
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)  
      at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3748)  
      at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3714)  
      at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)  
      at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)  
      at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2283)  
      at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2182)  
      at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1499)  
      at weblogic.work.ExecuteThread.execute(ExecuteThread.java:263)  
      at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)  

Cause:

In this specific case, the ReqLinesNotificationsVO was extended and the Patch has overridden the standard VO and added additional columns.

If we have a VO Substitution and the Standard VO gets modified  and new columns gets added or the datatype of some attribute gets changed, or the order or attributes gets changed, then we will get this error as there is a mismatch with the Standard VO Attribute and the Extended VO Attribute.

Solution:

Re-create the extended VO based on the new StandardVO and re-deploy to the server.

Related links :JBO-27021 Failed to load CustomDatum value at index 51, When Creating A Receipt in Oracle iProcurement (Doc ID 2127497.1)




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

JTF: Enabling Logging on Screen in CRM Technology Foundation Pages JTF Framework

Similar to seeing the log messages on screen for OAF (OAF: How to add logging / debug messages in Oracle Application Framework),  you can see the log messages for JTF  framework also.

Refer : Logging Features in Oracle E-Business Suite

For security reasons, this feature is only accessible if the "FND: Diagnostics" Profile is set to "Yes".

To enable logging to screen in pages based on the CRM Technology Foundation, append the following to the page's URL:

jtfdebug
Specify the logging level that should be displayed on the current screen.

jtfdebugfilter
(Optional) If desired, this parameter can be used as a filter to display messages based on a Java package name.

For example: <current_url>&jtfdebug=STATEMENT&jtfdebugfilter=jtf*

[In URLs, use the asterisk symbol (*) as a wildcard character, not the percent sign (%).]

Example screenshot:

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

OAF: The program can't start because MSVCR71.dll is missing from your computer.

In one of my project, in one machine we were not able to open the Jdeveloper  (Version 10.1.3.5 - Patch 19170592 ) which we downloaded from the Metalink Note. This post is to help those who face this issue and don't know what to do to fix this.

OA Framework - How to Find the Correct Version of JDeveloper for OA Extensions to Use with E-Business Suite 11i or Release 12.x (Doc ID 416708.1)

After downloading p19170592_R12_GENERIC.zip, unzipped the contents into C:/oracle/Jdev10.1.3.5. After that when we tried to open the Jdeveloper using C:/oracle/Jdev10.1.3.5/jdevbin/jdev/bin/jdevW.exe, we were getting the below error.

"The program can't start because MSVCR71.dll is missing from your computer. Try reinstalling the program to fix this problem."

Fix:  You just need to copy the msvcr71.dll file from C:/oracle/Jdev10.1.3.5/jdevbin/jdk/bin to C:/oracle/Jdev10.1.3.5/jdevbin/jdev/bin.

After copying the file, Jdeveloper opened successfully.

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

Oracle Apps: Query to get the details of a Descriptive Flexfield

The below gives the details of Descriptive Flex Field defined in Oracle eBusiness Suite. This query can be used to find out all the DFF Segments defined for a specific table.

 select dffv.application_table_name          table_name  
       ,dffv.title  
       ,dcont.descriptive_flex_context_code context_code  
       ,dfusg.application_column_name       segment_column  
       ,dfusg.form_left_prompt              segment_window_prompt  
       ,dffv.concatenated_segs_view_name    view_name  
       ,(select x.application_name  
         from fnd_application_vl x  
         where x.application_id = dffv.application_id) application  
       ,dffv.description  
       ,dffv.descriptive_flexfield_name    name  
       ,dffv.context_column_name           structure_column  
       ,dffv.form_context_prompt           prompt  
       ,dffv.default_context_value         default_value  
       ,dffv.default_context_field_name    reference_field  
       ,dffv.context_required_flag         required  
       ,dffv.context_user_override_flag    displayed  
       ,dffv.context_synchronization_flag  synchronize_with_reference  
       ,dcont.descriptive_flex_context_code context_code  
       ,dcont.descriptive_flex_context_name context_name  
       ,dcont.description  
       ,dcont.enabled_flag  
       ,dfusg.column_seq_num           sequence_number  
       ,dfusg.end_user_column_name     segment_name  
       ,dfusg.form_left_prompt         segment_window_prompt  
       ,dfusg.application_column_name  segment_column  
       ,dfusg.enabled_flag  
       ,dfusg.display_flag  
       ,vset.flex_value_set_name      value_set  
       ,vset.*  
 from  apps.fnd_descriptive_flexs_vl     dffv  
      ,apps.fnd_descr_flex_contexts_vl   dcont  
      ,apps.fnd_descr_flex_col_usage_vl  dfusg  
      ,apps.fnd_flex_value_sets          vset  
 where dffv.descriptive_flexfield_name     = dcont.descriptive_flexfield_name  
 and   dcont.descriptive_flexfield_name    = dfusg.descriptive_flexfield_name(+)  
 and   dfusg.flex_value_set_id             = vset.flex_value_set_id(+)  
 and   dcont.descriptive_flex_context_code = dfusg.descriptive_flex_context_code(+)  
 --and  dffv.title LIKE 'Common Lookups'                -- Uncomment and add your DFF Title  
 --and  dffv.application_table_name LIKE 'GL_TAX_CODES' -- Uncomment and add your Table Name  
 order by dffv.application_id  
         ,dffv.title  
         ,dcont.descriptive_flex_context_code  
         ,decode(dfusg.enabled_flag, 'Y', 1, 'N', 2)  
         ,dfusg.column_seq_num  

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

Oracle Apps: Query to get the details of a Table Type ValueSet

The below query gives the full details of a Table Type Valueset in Oracle eBusiness. The last column gives the actual query which gets executed by the Valueset.

 select ffvs.flex_value_set_id  
       ,ffvs.flex_value_set_name  
       ,ffvs.description  
       ,decode(ffvs.longlist_flag  
              ,'Y', 'Long List of Values'  
              ,'N', 'List of Values'  
              ,'X', 'Poplist') list_type  
       ,decode(ffvs.security_enabled_flag  
              ,'N' ,'No Security'  
              ,'H' ,'Hierarchical Security'  
              ,'Y' ,'Non-Hierarchical Security') security_type  
       ,decode(ffvs.format_type  
              ,'C' ,'Char'  
              ,'D' ,'Date'  
              ,'T' ,'DateTime'  
              ,'N' ,'Number'  
              ,'X' ,'Standard Date'  
              ,'Y' ,'Standard DateTime'  
              ,'I' ,'Time') format_type  
       ,ffvs.maximum_size  
       ,ffvs.number_precision precision
       ,decode(ffvs.alphanumeric_allowed_flag  
              ,'N', 'Y'  
              ,'Y', 'N') numbers_only  
       ,ffvs.uppercase_only_flag uppercase_only  
       ,ffvs.numeric_mode_enabled_flag right_justify_and_zero_fill  
       ,ffvs.minimum_value min_value  
       ,ffvs.maximum_value max_value  
       ,decode(ffvs.validation_type  
              ,'I' ,'Independent'  
              ,'D' ,'Dependent'  
              ,'N' ,'None'  
              ,'P' ,'Pair'  
              ,'U' ,'Special'  
              ,'F' ,'Table'  
              ,'X' ,'Translatable Independent'  
              ,'Y' ,'Translatable Dependent') validation_type  
       ,(select application_name   
         from fnd_application_vl x   
         where x.application_id = ffvt.table_application_id) table_application  
       ,ffvt.application_table_name table_name  
       ,ffvt.value_column_name  
       ,decode(ffvt.value_column_type  
              ,'V', 'Varchar2'  
              ,'N', 'Number'  
              ,'C', 'Char'  
              ,'D', 'Date') value_column_type  
       ,ffvt.value_column_size  
       ,ffvt.meaning_column_name  
       ,decode(ffvt.meaning_column_type  
              ,'V', 'Varchar2'  
              ,'N', 'Number'  
              ,'C', 'Char'  
              ,'D', 'Date') meaning_column_type  
       ,ffvt.meaning_column_size  
       ,ffvt.id_column_name  
       ,decode(ffvt.id_column_type  
              ,'V', 'Varchar2'  
              ,'N', 'Number'  
              ,'C', 'Char'  
              ,'D', 'Date') id_column_type  
       ,ffvt.id_column_size  
       ,ffvt.additional_where_clause  
       ,ffvt.additional_quickpick_columns  
       ,('select ' ||NVL(value_column_name,NULL)   ||', '  
                   ||NVL(meaning_column_name,NULL) ||', '  
                   ||NVL(id_column_name,NULL)      ||' '||  
         'from '  || ffvt.application_table_name   ||' '||  
         'where ' || decode(upper(substr(ltrim(to_char(ffvt.additional_where_clause)),0,5))  
                           ,'WHERE', substr(to_char(ffvt.additional_where_clause),6)  
                           ,to_char(ffvt.additional_where_clause)) )valueset_query  
 from  apps.fnd_flex_value_sets        ffvs  
      ,apps.fnd_flex_validation_tables ffvt  
 where ffvs.flex_value_set_id = ffvt.flex_value_set_id  
 and   ffvs.flex_value_set_name = 'XXAJ_VALUESET'; -- Replace the valuesetname  

This query is helpful when we need to find out all the value sets which is using a particular profile_option or another valueset or a specific table etc...


Add the below where clause to the above query to find out all the valuesets which uses a specific value in the whereclause.

 and  ffvt.additional_where_clause like '%AJ%'; -- Replace the value with the string you want to search for.  

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

Oracle Apps: PA_TASKS Hierarchy Query to get all the tasks in Project /Template in the correct order

The below query can be used to get the PA Tasks in a hierarchical order which belongs to a Project/Template which is defined in the Project Accounting Module.

Thanks to Sajeer Omar.

Replace the below hardcoded values before executing :

Project/Template ID

 select x.project_id  
       ,x.task_id  
       ,(lpad(' ',(wbs_level-1)*5,' ')||x.task_number) task_number  
       ,x.parent_task_id  
       ,x.top_task_id  
       ,x.wbs_level  
       ,sys_connect_by_path(x.task_id, '/')  
 from  pa_tasks x  
 where x.project_id = 1 -- Project/Template ID  
 connect by prior x.task_id = x.parent_task_id  
 start with x.parent_task_id is null  
 order by sys_connect_by_path(x.task_id, '/');  

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