This discussion is archived
2 Replies Latest reply: Jan 30, 2013 2:16 PM by 787534 RSS

OAF not recognising package.function

Aravinda Newbie
Currently Being Moderated
Dear friends ,

I have used a function in my query , in OAF VO EXTENSION.
It is giving the following error .................java.sql.SQLException: ORA-00904: "XXSFIPACKAGEVOEXT"."SFI_EAM_TRANQTY": invalid identifier.
how to resolve it .plz help.let me know for any clarification.plz help.


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
, lot_control_code
, serial_number_control_code
, revision_qty_control_code
, eam_item_type
, msi.inventory_item_id
, msi.padded_concatenated_segments item_code_segment1
, msi.description item_description
, mOH.LOCATOR_ID
, cst.item_cost
, moh.subinventory_code sub_inventory_name
, mil.segment1 LOCATOR
, mcr.cross_reference
, xxsfipackagevoext.SFI_EAM_tranqty(moh.organization_id,moh.subinventory_code ,moh.LOCATOR_ID,msi.inventory_item_id) Available_to_transact
, sum(moh.primary_transaction_quantity) total_qoh
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
--and moh.organization_id =3159
-- and moh.INVENTORY_ITEM_ID = 8073
group by moh.subinventory_code ,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 ,
msi.padded_concatenated_segments ,
msi.description ,
MOH.LOCATOR_ID,
mil.segment1 , mcr.cross_reference, cst.item_cost,
xxsfipackagevoext.SFI_EAM_tranqty(moh.organization_id,moh.subinventory_code ,moh.LOCATOR_ID,msi.inventory_item_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-00904: "XXSFIPACKAGEVOEXT"."SFI_EAM_TRANQTY": invalid identifier

     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-00904: "XXSFIPACKAGEVOEXT"."SFI_EAM_TRANQTY": invalid identifier

     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)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points