oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT item_number, item_description, inventory_item_id, billing_type, mtl_transactions_enabled_flag, recovered_part_disp_code, primary_uom_code, recovered_part_disp, transaction_type_id, qrslt.organization_id, restrict_locators_code, update_ib_flag, ib_trackable, location_control_code, lot_control_code, revision_qty_control_code, serial_number_control_code, restrict_subinventories_code, onhand_qty, csp_inv_loc_assignment_id, subinventory_code, resource_type, resource_id, revision, available_qty FROM (SELECT msibk.concatenated_segments item_number, msibk.description item_description, msibk.inventory_item_id, msibk.material_billable_flag billing_type, msibk.mtl_transactions_enabled_flag, msibk.recovered_part_disp_code, msibk.primary_uom_code, fl.meaning recovered_part_disp, DECODE (:1, 'NO_CTBT', -1, ctbt.transaction_type_id) transaction_type_id, msibk.organization_id, msibk.restrict_locators_code, NVL (msibk.comms_nl_trackable_flag, 'N' ) update_ib_flag, NVL (msibk.comms_nl_trackable_flag, 'N') ib_trackable, msibk.location_control_code, msibk.lot_control_code, msibk.revision_qty_control_code, csf_debrief_pvt.serial_number_control_code (msibk.organization_id,msibk.inventory_item_id, 93 ) serial_number_control_code, msibk.restrict_subinventories_code, mosv.total_qoh onhand_qty, cila.csp_inv_loc_assignment_id, cila.subinventory_code, cila.resource_type, cila.resource_id, TO_CHAR (NULL) revision, csp_validate_pub.get_avail_qty (mosv.organization_id,mosv.subinventory_code,NULL,mosv.inventory_item_id, mosv.revision ) available_qty FROM mtl_onhand_sub_v mosv, csp_inv_loc_assignments cila, csp_sec_inventories csi, mtl_parameters mp, mtl_system_items_b_kfv msibk, fnd_lookups fl, (SELECT transaction_type_id, billing_type FROM cs_txn_billing_types WHERE ROWNUM = DECODE (:2, 'NO_CTBT', 1, ROWNUM)) ctbt, cs_billing_type_categories cbtc WHERE mosv.organization_id = cila.organization_id AND mosv.subinventory_code = cila.subinventory_code AND msibk.organization_id = mosv.organization_id AND msibk.inventory_item_id = mosv.inventory_item_id AND mp.organization_id = cila.organization_id AND csi.organization_id = cila.organization_id AND csi.secondary_inventory_name = cila.subinventory_code AND csi.condition_type = 'G' AND msibk.mtl_transactions_enabled_flag = 'Y' AND msibk.recovered_part_disp_code = fl.lookup_code(+) AND fl.lookup_type(+) = 'CSP_RECOVERED_PART_DISP_CODE' AND DECODE (:3, 'NO_CTBT', cbtc.billing_type, ctbt.billing_type ) = cbtc.billing_type AND cbtc.billing_category = 'M' AND cbtc.billing_type = msibk.material_billable_flag AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (msibk.start_date_active, SYSDATE ) ) AND TRUNC (NVL (msibk.end_date_active, SYSDATE)) AND ( ( mp.negative_inv_receipt_code 1 AND mosv.total_qoh > 0 ) OR mp.negative_inv_receipt_code = 1) UNION ALL SELECT msibk.concatenated_segments item_number, msibk.description item_description, msibk.inventory_item_id, msibk.material_billable_flag billing_type, msibk.mtl_transactions_enabled_flag, msibk.recovered_part_disp_code, msibk.primary_uom_code, fl.meaning recovered_part_disp, DECODE (:4,'NO_CTBT', -1, ctbt.transaction_type_id) transaction_type_id, msibk.organization_id, msibk.restrict_locators_code, NVL (msibk.comms_nl_trackable_flag, 'N' ) update_ib_flag, NVL (msibk.comms_nl_trackable_flag, 'N') ib_trackable, msibk.location_control_code, msibk.lot_control_code, msibk.revision_qty_control_code, csf_debrief_pvt.serial_number_control_code (msibk.organization_id, msibk.inventory_item_id, 93 ) serial_number_control_code, msibk.restrict_subinventories_code, 0 onhand_qty, cila.csp_inv_loc_assignment_id, cila.subinventory_code, cila.resource_type, cila.resource_id, TO_CHAR (NULL) revision, 0 available_qty FROM mtl_system_items_b_kfv msibk, csp_inv_loc_assignments cila, csp_sec_inventories csi, mtl_parameters mp, fnd_lookups fl, (SELECT transaction_type_id, billing_type FROM cs_txn_billing_types WHERE ROWNUM = DECODE (:5, 'NO_CTBT', 1, ROWNUM)) ctbt, cs_billing_type_categories cbtc WHERE mp.organization_id = cila.organization_id AND mp.negative_inv_receipt_code = 1 AND msibk.organization_id = cila.organization_id AND csi.organization_id = cila.organization_id AND csi.secondary_inventory_name = cila.subinventory_code AND csi.condition_type = 'G' AND msibk.mtl_transactions_enabled_flag = 'Y' AND msibk.recovered_part_disp_code = fl.lookup_code(+) AND fl.lookup_type(+) = 'CSP_RECOVERED_PART_DISP_CODE' AND DECODE (:6, 'NO_CTBT', cbtc.billing_type, ctbt.billing_type ) = cbtc.billing_type AND cbtc.billing_category = 'M' AND cbtc.billing_type = msibk.material_billable_flag AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (msibk.start_date_active, SYSDATE ) ) AND TRUNC (NVL (msibk.end_date_active, SYSDATE) ) AND NOT EXISTS ( SELECT 'x' FROM mtl_onhand_quantities_detail moqd WHERE moqd.organization_id = msibk.organization_id AND moqd.inventory_item_id = msibk.inventory_item_id AND moqd.subinventory_code = cila.subinventory_code) ORDER BY item_number) qrslt, c_csd_global_temp_sbom_items sbom WHERE qrslt.resource_type = :7 AND qrslt.resource_id = :8 AND qrslt.organization_id IN (418) AND qrslt.csp_inv_loc_assignment_id IN (10060) AND qrslt.transaction_type_id = :9 AND qrslt.update_ib_flag = DECODE (:10, 'N', 'N', qrslt.update_ib_flag ) AND qrslt.organization_id = sbom.organization_id AND qrslt.inventory_item_id = sbom.component_item_id) qrslt WHERE ((UPPER (item_number) LIKE UPPER (:11)))

Oracle Community | 961553 | 4 years ago
  1. 0

    Urgent: JBO-27122: Invalid Column Type error during statement preparation.

    Oracle Community | 4 years ago | 961553
    oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT item_number, item_description, inventory_item_id, billing_type, mtl_transactions_enabled_flag, recovered_part_disp_code, primary_uom_code, recovered_part_disp, transaction_type_id, qrslt.organization_id, restrict_locators_code, update_ib_flag, ib_trackable, location_control_code, lot_control_code, revision_qty_control_code, serial_number_control_code, restrict_subinventories_code, onhand_qty, csp_inv_loc_assignment_id, subinventory_code, resource_type, resource_id, revision, available_qty FROM (SELECT msibk.concatenated_segments item_number, msibk.description item_description, msibk.inventory_item_id, msibk.material_billable_flag billing_type, msibk.mtl_transactions_enabled_flag, msibk.recovered_part_disp_code, msibk.primary_uom_code, fl.meaning recovered_part_disp, DECODE (:1, 'NO_CTBT', -1, ctbt.transaction_type_id) transaction_type_id, msibk.organization_id, msibk.restrict_locators_code, NVL (msibk.comms_nl_trackable_flag, 'N' ) update_ib_flag, NVL (msibk.comms_nl_trackable_flag, 'N') ib_trackable, msibk.location_control_code, msibk.lot_control_code, msibk.revision_qty_control_code, csf_debrief_pvt.serial_number_control_code (msibk.organization_id,msibk.inventory_item_id, 93 ) serial_number_control_code, msibk.restrict_subinventories_code, mosv.total_qoh onhand_qty, cila.csp_inv_loc_assignment_id, cila.subinventory_code, cila.resource_type, cila.resource_id, TO_CHAR (NULL) revision, csp_validate_pub.get_avail_qty (mosv.organization_id,mosv.subinventory_code,NULL,mosv.inventory_item_id, mosv.revision ) available_qty FROM mtl_onhand_sub_v mosv, csp_inv_loc_assignments cila, csp_sec_inventories csi, mtl_parameters mp, mtl_system_items_b_kfv msibk, fnd_lookups fl, (SELECT transaction_type_id, billing_type FROM cs_txn_billing_types WHERE ROWNUM = DECODE (:2, 'NO_CTBT', 1, ROWNUM)) ctbt, cs_billing_type_categories cbtc WHERE mosv.organization_id = cila.organization_id AND mosv.subinventory_code = cila.subinventory_code AND msibk.organization_id = mosv.organization_id AND msibk.inventory_item_id = mosv.inventory_item_id AND mp.organization_id = cila.organization_id AND csi.organization_id = cila.organization_id AND csi.secondary_inventory_name = cila.subinventory_code AND csi.condition_type = 'G' AND msibk.mtl_transactions_enabled_flag = 'Y' AND msibk.recovered_part_disp_code = fl.lookup_code(+) AND fl.lookup_type(+) = 'CSP_RECOVERED_PART_DISP_CODE' AND DECODE (:3, 'NO_CTBT', cbtc.billing_type, ctbt.billing_type ) = cbtc.billing_type AND cbtc.billing_category = 'M' AND cbtc.billing_type = msibk.material_billable_flag AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (msibk.start_date_active, SYSDATE ) ) AND TRUNC (NVL (msibk.end_date_active, SYSDATE)) AND ( ( mp.negative_inv_receipt_code 1 AND mosv.total_qoh > 0 ) OR mp.negative_inv_receipt_code = 1) UNION ALL SELECT msibk.concatenated_segments item_number, msibk.description item_description, msibk.inventory_item_id, msibk.material_billable_flag billing_type, msibk.mtl_transactions_enabled_flag, msibk.recovered_part_disp_code, msibk.primary_uom_code, fl.meaning recovered_part_disp, DECODE (:4,'NO_CTBT', -1, ctbt.transaction_type_id) transaction_type_id, msibk.organization_id, msibk.restrict_locators_code, NVL (msibk.comms_nl_trackable_flag, 'N' ) update_ib_flag, NVL (msibk.comms_nl_trackable_flag, 'N') ib_trackable, msibk.location_control_code, msibk.lot_control_code, msibk.revision_qty_control_code, csf_debrief_pvt.serial_number_control_code (msibk.organization_id, msibk.inventory_item_id, 93 ) serial_number_control_code, msibk.restrict_subinventories_code, 0 onhand_qty, cila.csp_inv_loc_assignment_id, cila.subinventory_code, cila.resource_type, cila.resource_id, TO_CHAR (NULL) revision, 0 available_qty FROM mtl_system_items_b_kfv msibk, csp_inv_loc_assignments cila, csp_sec_inventories csi, mtl_parameters mp, fnd_lookups fl, (SELECT transaction_type_id, billing_type FROM cs_txn_billing_types WHERE ROWNUM = DECODE (:5, 'NO_CTBT', 1, ROWNUM)) ctbt, cs_billing_type_categories cbtc WHERE mp.organization_id = cila.organization_id AND mp.negative_inv_receipt_code = 1 AND msibk.organization_id = cila.organization_id AND csi.organization_id = cila.organization_id AND csi.secondary_inventory_name = cila.subinventory_code AND csi.condition_type = 'G' AND msibk.mtl_transactions_enabled_flag = 'Y' AND msibk.recovered_part_disp_code = fl.lookup_code(+) AND fl.lookup_type(+) = 'CSP_RECOVERED_PART_DISP_CODE' AND DECODE (:6, 'NO_CTBT', cbtc.billing_type, ctbt.billing_type ) = cbtc.billing_type AND cbtc.billing_category = 'M' AND cbtc.billing_type = msibk.material_billable_flag AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (msibk.start_date_active, SYSDATE ) ) AND TRUNC (NVL (msibk.end_date_active, SYSDATE) ) AND NOT EXISTS ( SELECT 'x' FROM mtl_onhand_quantities_detail moqd WHERE moqd.organization_id = msibk.organization_id AND moqd.inventory_item_id = msibk.inventory_item_id AND moqd.subinventory_code = cila.subinventory_code) ORDER BY item_number) qrslt, c_csd_global_temp_sbom_items sbom WHERE qrslt.resource_type = :7 AND qrslt.resource_id = :8 AND qrslt.organization_id IN (418) AND qrslt.csp_inv_loc_assignment_id IN (10060) AND qrslt.transaction_type_id = :9 AND qrslt.update_ib_flag = DECODE (:10, 'N', 'N', qrslt.update_ib_flag ) AND qrslt.organization_id = sbom.organization_id AND qrslt.inventory_item_id = sbom.component_item_id) qrslt WHERE ((UPPER (item_number) LIKE UPPER (:11)))
  2. Speed up your debug routine!

    Automated exception search integrated into your IDE

  3. 0

    how to get a value standad page to custom page

    Oracle Community | 6 years ago | 725826
    oracle.apps.fnd.framework.OAException: java.lang.NullPointerException
  4. 0

    Migration issue in OAF-urgent

    Oracle Community | 5 years ago | user5425941
    oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select 'Select_flag', poh.po_num, poh.comments, poh.creation_date, poh.po_release_id, to_char(null) agent_name, poh.currency_code, to_char(0) as amount, poh.ACCEPTANCE_DUE_DATE, poh.vendor_id, poh.po_header_id, poh.agent_id, poh.type_lookup_code, poh.type_name, poh.vendor_contact_id, poh.vendor_site_id, poh.acceptance_required_flag, poh.approved_date, poh.revision_num, to_char(null) PO_STATUS_CODE , to_char(null) PO_STATUS_DISP , poh.org_name, poh.attribute1, poh.attribute2, poh.attribute3, poh.attribute4, poh.attribute5, poh.attribute6, poh.attribute7, poh.attribute8, poh.attribute9, poh.attribute10, poh.attribute11, poh.attribute12, poh.attribute13, poh.attribute14, poh.attribute15, poh.attribute_category, decode(poh.po_release_id,null,'PosHdrAttach','PosRelAttach') as SwitchAttach, 'N' as retrieve_flag, poh.CANCEL_FLAG, poh.CLOSED_CODE, poh.FROZEN_FLAG, poh.USER_HOLD_FLAG, poh.global_agreement_flag, to_char(null) consigned_consumption_flag, poh.vendor_name, poh.vendor_site_code, poh.po_release_id po_release_id2, poh.vendor_number , poh.vendor_id vendor_id2, org_id, poh.segment1, poh.release_num, to_char(null) business_document_type, to_char(null) business_document_id, to_char(null) business_document_version, 0 as ship_to_location_id, security_level_code, access_level_code from spos_view_po_summary_v poh) QRSLT WHERE (VENDOR_ID in (:1) AND VENDOR_CONTACT_ID in (:2) AND ((VENDOR_SITE_ID in (:3) AND NVL(global_agreement_flag, 'N') = 'N') OR (PO_RELEASE_ID is null AND PO_HEADER_ID in ( select PGA.po_header_id from PO_GA_ORG_ASSIGNMENTS PGA, PO_HEADERS_ALL POH where PGA.po_header_id = POH.po_header_id and PGA.enabled_flag = 'Y' and POH.global_agreement_flag = 'Y' and PGA.vendor_site_id in (:4))))) ORDER BY APPROVED_DATE DESC

    11 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. oracle.apps.fnd.framework.OAException

      oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT item_number, item_description, inventory_item_id, billing_type, mtl_transactions_enabled_flag, recovered_part_disp_code, primary_uom_code, recovered_part_disp, transaction_type_id, qrslt.organization_id, restrict_locators_code, update_ib_flag, ib_trackable, location_control_code, lot_control_code, revision_qty_control_code, serial_number_control_code, restrict_subinventories_code, onhand_qty, csp_inv_loc_assignment_id, subinventory_code, resource_type, resource_id, revision, available_qty FROM (SELECT msibk.concatenated_segments item_number, msibk.description item_description, msibk.inventory_item_id, msibk.material_billable_flag billing_type, msibk.mtl_transactions_enabled_flag, msibk.recovered_part_disp_code, msibk.primary_uom_code, fl.meaning recovered_part_disp, DECODE (:1, 'NO_CTBT', -1, ctbt.transaction_type_id) transaction_type_id, msibk.organization_id, msibk.restrict_locators_code, NVL (msibk.comms_nl_trackable_flag, 'N' ) update_ib_flag, NVL (msibk.comms_nl_trackable_flag, 'N') ib_trackable, msibk.location_control_code, msibk.lot_control_code, msibk.revision_qty_control_code, csf_debrief_pvt.serial_number_control_code (msibk.organization_id,msibk.inventory_item_id, 93 ) serial_number_control_code, msibk.restrict_subinventories_code, mosv.total_qoh onhand_qty, cila.csp_inv_loc_assignment_id, cila.subinventory_code, cila.resource_type, cila.resource_id, TO_CHAR (NULL) revision, csp_validate_pub.get_avail_qty (mosv.organization_id,mosv.subinventory_code,NULL,mosv.inventory_item_id, mosv.revision ) available_qty FROM mtl_onhand_sub_v mosv, csp_inv_loc_assignments cila, csp_sec_inventories csi, mtl_parameters mp, mtl_system_items_b_kfv msibk, fnd_lookups fl, (SELECT transaction_type_id, billing_type FROM cs_txn_billing_types WHERE ROWNUM = DECODE (:2, 'NO_CTBT', 1, ROWNUM)) ctbt, cs_billing_type_categories cbtc WHERE mosv.organization_id = cila.organization_id AND mosv.subinventory_code = cila.subinventory_code AND msibk.organization_id = mosv.organization_id AND msibk.inventory_item_id = mosv.inventory_item_id AND mp.organization_id = cila.organization_id AND csi.organization_id = cila.organization_id AND csi.secondary_inventory_name = cila.subinventory_code AND csi.condition_type = 'G' AND msibk.mtl_transactions_enabled_flag = 'Y' AND msibk.recovered_part_disp_code = fl.lookup_code(+) AND fl.lookup_type(+) = 'CSP_RECOVERED_PART_DISP_CODE' AND DECODE (:3, 'NO_CTBT', cbtc.billing_type, ctbt.billing_type ) = cbtc.billing_type AND cbtc.billing_category = 'M' AND cbtc.billing_type = msibk.material_billable_flag AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (msibk.start_date_active, SYSDATE ) ) AND TRUNC (NVL (msibk.end_date_active, SYSDATE)) AND ( ( mp.negative_inv_receipt_code 1 AND mosv.total_qoh > 0 ) OR mp.negative_inv_receipt_code = 1) UNION ALL SELECT msibk.concatenated_segments item_number, msibk.description item_description, msibk.inventory_item_id, msibk.material_billable_flag billing_type, msibk.mtl_transactions_enabled_flag, msibk.recovered_part_disp_code, msibk.primary_uom_code, fl.meaning recovered_part_disp, DECODE (:4,'NO_CTBT', -1, ctbt.transaction_type_id) transaction_type_id, msibk.organization_id, msibk.restrict_locators_code, NVL (msibk.comms_nl_trackable_flag, 'N' ) update_ib_flag, NVL (msibk.comms_nl_trackable_flag, 'N') ib_trackable, msibk.location_control_code, msibk.lot_control_code, msibk.revision_qty_control_code, csf_debrief_pvt.serial_number_control_code (msibk.organization_id, msibk.inventory_item_id, 93 ) serial_number_control_code, msibk.restrict_subinventories_code, 0 onhand_qty, cila.csp_inv_loc_assignment_id, cila.subinventory_code, cila.resource_type, cila.resource_id, TO_CHAR (NULL) revision, 0 available_qty FROM mtl_system_items_b_kfv msibk, csp_inv_loc_assignments cila, csp_sec_inventories csi, mtl_parameters mp, fnd_lookups fl, (SELECT transaction_type_id, billing_type FROM cs_txn_billing_types WHERE ROWNUM = DECODE (:5, 'NO_CTBT', 1, ROWNUM)) ctbt, cs_billing_type_categories cbtc WHERE mp.organization_id = cila.organization_id AND mp.negative_inv_receipt_code = 1 AND msibk.organization_id = cila.organization_id AND csi.organization_id = cila.organization_id AND csi.secondary_inventory_name = cila.subinventory_code AND csi.condition_type = 'G' AND msibk.mtl_transactions_enabled_flag = 'Y' AND msibk.recovered_part_disp_code = fl.lookup_code(+) AND fl.lookup_type(+) = 'CSP_RECOVERED_PART_DISP_CODE' AND DECODE (:6, 'NO_CTBT', cbtc.billing_type, ctbt.billing_type ) = cbtc.billing_type AND cbtc.billing_category = 'M' AND cbtc.billing_type = msibk.material_billable_flag AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (msibk.start_date_active, SYSDATE ) ) AND TRUNC (NVL (msibk.end_date_active, SYSDATE) ) AND NOT EXISTS ( SELECT 'x' FROM mtl_onhand_quantities_detail moqd WHERE moqd.organization_id = msibk.organization_id AND moqd.inventory_item_id = msibk.inventory_item_id AND moqd.subinventory_code = cila.subinventory_code) ORDER BY item_number) qrslt, c_csd_global_temp_sbom_items sbom WHERE qrslt.resource_type = :7 AND qrslt.resource_id = :8 AND qrslt.organization_id IN (418) AND qrslt.csp_inv_loc_assignment_id IN (10060) AND qrslt.transaction_type_id = :9 AND qrslt.update_ib_flag = DECODE (:10, 'N', 'N', qrslt.update_ib_flag ) AND qrslt.organization_id = sbom.organization_id AND qrslt.inventory_item_id = sbom.component_item_id) qrslt WHERE ((UPPER (item_number) LIKE UPPER (:11)))

      at oracle.apps.fnd.framework.OAException.wrapperException()
    2. oracle.apps.fnd
      OAPageBean.preparePage
      1. oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
      2. oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1169)
      3. oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435)
      4. oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2978)
      5. oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1875)
      6. oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543)
      7. oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)
      7 frames