Extend View Objects, "Invalid Column" SQL error

Extending the ReqSupplierVO and ReqSupplierSiteVO in the Supplier and Supplier Site Search Regions in the iProc Non-catalog Request page results in the following Java SQL error:

## Detail 0 ##
java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:240)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7895)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7572)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8183)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectAtName(OraclePreparedStatement


Cause: the extended VO’s were created using JDeveloper 10G, and the SQL tab of the VO Properties must specify a binding type of “Oracle Positional”.

OTL Restrict Cost Centre list

The following SQL will restrict the list of GL cost centre segment values according to the security rules assigned to a specific responsibility. The original query was written for Virgin Money, and also includes PO Controls:

SELECT /*
Program: VM_ACTIVITY_LOV_V.sql
Author: David Taylor
Description: Custom view to select valid cost centres.
Version: 1.0
Date: 19-AUG-2006
History: 1.0 Original version */
vsv.flex_value cc
, vsv.description cc_desc
FROM gl_sets_of_books sofb
, fnd_id_flex_structures st
, fnd_id_flex_segments sg
, fnd_flex_values_vl vsv
WHERE sofb.chart_of_accounts_id = st.id_flex_num
AND st.id_flex_code = sg.id_flex_code
AND st.id_flex_num = sg.id_flex_num
AND st.application_id = sg.application_id
AND sg.flex_value_set_id = vsv.flex_value_set_id
AND sofb.set_of_books_id = fnd_profile.value(‘GL_SET_OF_BKS_ID’)
AND st.id_flex_code= ‘GL#’
AND sg.segment_name = ‘Cost Centre’
AND nvl(vsv.enabled_flag, ‘Y’) = ‘Y’
AND nvl(vsv.summary_flag, ‘N’) = ‘N’
AND trunc(sysdate) BETWEEN trunc(nvl(vsv.start_date_active, sysdate))
AND trunc(nvl(vsv.end_date_active, sysdate))
AND EXISTS
(
SELECT null
FROM po_control_functions f
, po_position_controls_all p
, po_control_groups c
, po_control_rules r
, per_assignments_x a
, per_people_x pe
, fnd_user u
WHERE f.control_function_id = p.control_function_id
AND p.control_group_id = c.control_group_id
AND c.control_group_id = r.control_group_id
AND p.position_id = a.position_id
AND a.person_id = pe.person_id
AND pe.person_id = u.employee_id
AND f.control_function_name = ‘Approve Purchase Requisitions’
AND f.enabled_flag = ‘Y’
AND trunc(sysdate) BETWEEN trunc(p.start_date)
AND trunc(nvl(p.end_date,sysdate))
AND c.enabled_flag = ‘Y’
AND u.user_id = fnd_profile.value(‘USER_ID’)
AND vsv.flex_value BETWEEN r.segment2_low
AND r.segment2_high
)
AND (
NOT EXISTS
(
SELECT ‘x’
FROM fnd_flex_value_rule_usages usg
, fnd_flex_value_rules rul
WHERE usg.application_id = fnd_profile.value(‘RESP_APPL_ID’)
AND usg.responsibility_id = fnd_profile.value(‘RESP_ID’)
AND usg.flex_value_rule_id = rul.flex_value_rule_id
AND rul.flex_value_set_id = vsv.flex_value_set_id
)
OR
(
EXISTS
(
SELECT ‘x’
FROM fnd_flex_value_rule_usages usg
, fnd_flex_value_rules rul
, fnd_flex_value_rule_lines line
WHERE usg.application_id = fnd_profile.value(‘RESP_APPL_ID’)
AND usg.responsibility_id = fnd_profile.value(‘RESP_ID’)
AND usg.flex_value_rule_id = rul.flex_value_rule_id
AND line.flex_value_rule_id = rul.flex_value_rule_id
AND line.include_exclude_indicator = ‘I’
AND vsv.flex_value BETWEEN line.flex_value_low
AND line.flex_value_high
AND rul.flex_value_set_id = vsv.flex_value_set_id
)
AND NOT EXISTS
(
SELECT ‘x’
FROM fnd_flex_value_rule_usages usg
, fnd_flex_value_rules rul
, fnd_flex_value_rule_lines line
WHERE usg.application_id = fnd_profile.value(‘RESP_APPL_ID’)
AND usg.responsibility_id = fnd_profile.value(‘RESP_ID’)
AND usg.flex_value_rule_id = rul.flex_value_rule_id
AND line.flex_value_rule_id = rul.flex_value_rule_id
AND line.include_exclude_indicator = ‘E’
AND vsv.flex_value BETWEEN line.flex_value_low
AND line.flex_value_high
AND rul.flex_value_set_id = vsv.flex_value_set_id
)
)
)
ORDER BY vsv.flex_value