Query For an Item on the Price List Maintenance Form Errors - oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
(Doc ID 2974345.1)
Last updated on SEPTEMBER 15, 2023
Applies to:
Oracle Advanced Pricing - Version 12.2.10 and laterInformation in this document applies to any platform.
Symptoms
When attempting to query an item on the price list maintenance form,
the following error occurs and is shown:
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation
java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
Error Page, Exception Details, show:
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT qpll.list_header_id list_header_id, qpll.list_line_id list_line_id, QP_PRICE_LIST_LINE_UTIL.Get_Product_Value('QP_ATTR_DEFNS_PRICING', qppa.Product_Attribute_Context, qppa.Product_Attribute ,qppa.Product_Attr_Value) Name, qp_mass_maintain_util.Get_Product_Desc(qppa.product_attribute_context, qppa.product_attribute,qppa.product_attr_value) Description, qppa.product_uom_code uom, qpll.operand operand, QP_QP_Form_Pricing_Attr.Get_Formula(qpll.GENERATE_USING_FORMULA_ID) Formula, qpll.start_date_active || ' - ' || qpll.end_date_active start_end_date, 'LINES' type, decode(qpll.list_line_type_code, 'PBH', 'Y', qp_mass_maintain_util.Check_SS_PTE_Codes_Match(qpll.list_header_id)) Disable, QP_QP_Form_Pricing_Attr.Get_Formula(qpll.price_by_formula_id) Dynamic_Formula, 'Y' has_child_element FROM qp_list_lines qpll, qp_pricing_attributes qppa WHERE qpll.list_header_id = qppa.list_header_id AND qpll.list_line_id = qppa.list_line_id AND qpll.qualification_ind IN (4, 6, 20, 22) AND qppa.pricing_attribute_context IS NULL and not exists ( select 1 from qp_fass_v map, qp_item_categories_v qic , qp_list_headers qlh where qic.category_set_id = map.category_set_id and qic.functional_area_id = map.functional_area_id and qlh.list_header_id = qpll.list_header_id and qppa.product_attribute = 'PRICING_ATTRIBUTE2' and qic.category_id =qppa.product_attr_value and map.enabled_flag = 'N' and qlh.pte_code = map.pte_code and qlh.source_system_code = map.application_short_name ) and qppa.product_attribute_context = :1 and qppa.product_attribute = :2 and qppa.product_attr_value = :3 and qpll.list_header_id = :4
at oracle.jbo.server.BaseSQLBuilderImpl.processException(BaseSQLBuilderImpl.java:3383)
at oracle.jbo.server.OracleSQLBuilderImpl.processException(OracleSQLBuilderImpl.java:4022)
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:961)
at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)
at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:5072)
at oracle.apps.qp.pricelist.massmaint.server.PriceListLinesHgridVOImpl.executeQueryForCollection(PriceListLinesHgridVOImpl.java:139)
at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:751)
at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:696)
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT qpll.list_header_id list_header_id, qpll.list_line_id list_line_id, QP_PRICE_LIST_LINE_UTIL.Get_Product_Value('QP_ATTR_DEFNS_PRICING', qppa.Product_Attribute_Context, qppa.Product_Attribute ,qppa.Product_Attr_Value) Name, qp_mass_maintain_util.Get_Product_Desc(qppa.product_attribute_context, qppa.product_attribute,qppa.product_attr_value) Description, qppa.product_uom_code uom, qpll.operand operand, QP_QP_Form_Pricing_Attr.Get_Formula(qpll.GENERATE_USING_FORMULA_ID) Formula, qpll.start_date_active || ' - ' || qpll.end_date_active start_end_date, 'LINES' type, decode(qpll.list_line_type_code, 'PBH', 'Y', qp_mass_maintain_util.Check_SS_PTE_Codes_Match(qpll.list_header_id)) Disable, QP_QP_Form_Pricing_Attr.Get_Formula(qpll.price_by_formula_id) Dynamic_Formula, 'Y' has_child_element FROM qp_list_lines qpll, qp_pricing_attributes qppa WHERE qpll.list_header_id = qppa.list_header_id AND qpll.list_line_id = qppa.list_line_id AND qpll.qualification_ind IN (4, 6, 20, 22) AND qppa.pricing_attribute_context IS NULL and not exists ( select 1 from qp_fass_v map, qp_item_categories_v qic , qp_list_headers qlh where qic.category_set_id = map.category_set_id and qic.functional_area_id = map.functional_area_id and qlh.list_header_id = qpll.list_header_id and qppa.product_attribute = 'PRICING_ATTRIBUTE2' and qic.category_id =qppa.product_attr_value and map.enabled_flag = 'N' and qlh.pte_code = map.pte_code and qlh.source_system_code = map.application_short_name ) and qppa.product_attribute_context = :1 and qppa.product_attribute = :2 and qppa.product_attr_value = :3 and qpll.list_header_id = :4
at oracle.jbo.server.BaseSQLBuilderImpl.processException(BaseSQLBuilderImpl.java:3383)
at oracle.jbo.server.OracleSQLBuilderImpl.processException(OracleSQLBuilderImpl.java:4022)
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:961)
at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)
at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:5072)
at oracle.apps.qp.pricelist.massmaint.server.PriceListLinesHgridVOImpl.executeQueryForCollection(PriceListLinesHgridVOImpl.java:139)
at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:751)
at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:696)
The issue can be reproduced at will with the following steps:
1. Responsibility: Pricing Administrator (or equivalent)
2. Navigate to: Price List Maintenance.
3. Search by item and the above errors / exception is shown
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |