0 Replies Latest reply: Jan 25, 2013 7:55 AM by Aravinda RSS

    AJVOAF:Issue :exception     variable not in select list

    Aravinda
      Dear Friends ,


      I did a vo extension (using a API ---------------- inv_quantity_tree_pub.query_quantities to get transactable quantity......... like SFI_EAM_tranqty(moh.organization_id,msi.inventory_item_id) in select query )
      Iam getting a error like variable not in select list what does it mean exactly
      i have given error details below
      let me know for any clarification..plz help


      Error Page


      Exception Details.

      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT concatenated_segments
      ,msi.description
      ,msi.organization_id
      ,primary_uom_code
      ,location_control_code
      ,restrict_locators_code
      ,restrict_subinventories_code
      ,msi.inventory_item_id
      ,lot_control_code, serial_number_control_code
      ,revision_qty_control_code
      ,eam_item_type
      ,msi.inventory_item_id as InventoryItem
      ,msi.padded_concatenated_segments item_code_segment1
      ,msi.description item_description
      ,moh.subinventory_code sub_inventory_name
      ,moh.primary_transaction_quantity total_qoh
      ,SFI_EAM_tranqty(moh.organization_id,msi.inventory_item_id) Available_to_transact
      ,mil.segment1 LOCATOR
      ,mcr.cross_reference
      ,cst.item_cost
      FROM mtl_system_items_kfv msi,
      mtl_onhand_quantities_detail moh,
      apps.mtl_item_locations mil,
      apps.mtl_cross_references mcr,
      apps.cst_item_costs cst
      WHERE stock_enabled_flag = 'Y'
      AND bom_item_type = 4
      AND (eam_item_type IS NULL OR eam_item_type = 3)
      AND moh.inventory_item_id(+) = msi.inventory_item_id
      AND moh.organization_id(+) = msi.organization_id
      AND moh.locator_id = mil.inventory_location_id(+)
      AND moh.organization_id = mcr.organization_id(+)
      AND mcr.cross_reference_type(+) = 'SFI_MRO_OLD_PART_NUM'
      AND moh.inventory_item_id = mcr.inventory_item_id(+)
      AND msi.inventory_item_status_code != 'Inactive'
      AND msi.item_type != 'AG'
      AND msi.item_type != 'AA'
      AND msi.item_type != 'OP'
      AND moh.inventory_item_id = cst.inventory_item_id
      AND moh.organization_id = cst.organization_id
      order by total_qoh desc) QRSLT WHERE (organization_id = (select distinct organization_id from wip_discrete_jobs where wip_entity_id = :1))
           at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
           at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1408)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2704)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1707)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:511)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:432)
           at oa_html._OA._jspService(_OA.java:84)
           at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
           at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
           at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
           at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
           at oracle.jsp.JspServlet.service(JspServlet.java:156)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
           at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
           at org.apache.jserv.JServConnection.run(JServConnection.java:294)
           at java.lang.Thread.run(Thread.java:534)
      ## Detail 0 ##
      java.sql.SQLException: ORA-01007: variable not in select list

           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
           at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
           at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
           at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
           at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
           at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2566)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584)
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:631)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:518)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3375)
           at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(OAJboViewObjectImpl.java:828)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4525)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:574)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:544)
           at oracle.jbo.server.ViewRowSetImpl.executeDetailQuery(ViewRowSetImpl.java:619)
           at oracle.jbo.server.ViewObjectImpl.executeDetailQuery(ViewObjectImpl.java:3339)
           at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3326)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:441)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(OAViewObjectImpl.java:716)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(OAWebBeanHelper.java:2314)
           at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequestAfterController(OAListOfValuesHelper.java:1493)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:842)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequest(OAListOfValuesHelper.java:1399)
           at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processFormRequest(OAListOfValuesBean.java:423)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1022)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:988)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:843)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2700)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1707)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:511)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:432)
           at oa_html._OA._jspService(_OA.java:84)
           at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
           at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
           at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
           at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
           at oracle.jsp.JspServlet.service(JspServlet.java:156)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
           at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
           at org.apache.jserv.JServConnection.run(JServConnection.java:294)
           at java.lang.Thread.run(Thread.java:534)
      java.sql.SQLException: ORA-01007: variable not in select list

           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
           at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
           at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
           at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
           at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
           at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2566)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584)
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:631)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:518)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3375)
           at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(OAJboViewObjectImpl.java:828)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4525)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:574)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:544)
           at oracle.jbo.server.ViewRowSetImpl.executeDetailQuery(ViewRowSetImpl.java:619)
           at oracle.jbo.server.ViewObjectImpl.executeDetailQuery(ViewObjectImpl.java:3339)
           at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3326)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:441)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(OAViewObjectImpl.java:716)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(OAWebBeanHelper.java:2314)
           at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequestAfterController(OAListOfValuesHelper.java:1493)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:842)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequest(OAListOfValuesHelper.java:1399)
           at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processFormRequest(OAListOfValuesBean.java:423)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1022)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:988)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:843)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
           at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
           at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2700)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1707)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:511)
           at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:432)
           at oa_html._OA._jspService(_OA.java:84)
           at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
           at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
           at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
           at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
           at oracle.jsp.JspServlet.service(JspServlet.java:156)
           at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
           at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
           at org.apache.jserv.JServConnection.run(JServConnection.java:294)
           at java.lang.Thread.run(Thread.java:534)