'INVALID COLUMN INDEX' WHILE NAVIGATING ON THE EXPENSE REPORT CONFIRMATION PAGE (Doc ID 1666238.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Internet Expenses - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Internet Expenses Audit Manager->(T)Manage Expense Reports, we are facing the
following error when return from Confirmation page for expense reports that
are pending to Manager approval or Pending Payables Approval:
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException:
JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM
(SELECT aerh.REPORT_HEADER_ID, aerh.EMPLOYEE_ID, aerh.WEEK_END_DATE,
aerh.CREATION_DATE, aerh.CREATED_BY, aerh.LAST_UPDATE_DATE,
aerh.LAST_UPDATED_BY, aerh.VOUCHNO, aerh.TOTAL, aerh.VENDOR_ID,
aerh.VENDOR_SITE_ID, aerh.REFERENCE_1, aerh.REFERENCE_2, aerh.INVOICE_NUM,
aerh.EXPENSE_REPORT_ID, aerh.SET_OF_BOOKS_ID, aerh.SOURCE,
aerh.EMPLOYEE_CCID, aerh.DESCRIPTION, aerh.REJECT_CODE,
@ aerh.HOLD_LOOKUP_CODE, aerh.DEFAULT_CURRENCY_CODE, aerh.LAST_UPDATE_LOGIN,
aerh.VOUCHER_NUM, aerh.AWT_GROUP_ID, aerh.ORG_ID,
aerh.WORKFLOW_APPROVED_FLAG, aerh.CORE_WF_STATUS_FLAG,
aerh.BOTHPAY_PARENT_ID, aerh.SHORTPAY_PARENT_ID,
aerh.PAID_ON_BEHALF_EMPLOYEE_ID, aerh.AMT_DUE_CCARD_COMPANY,
aerh.AMT_DUE_EMPLOYEE, aerh.DEFAULT_RECEIPT_CURRENCY_CODE,
aerh.MULTIPLE_CURRENCIES_FLAG, aerh.REPORT_FILING_NUMBER,
aerh.RECEIPTS_RECEIVED_DATE, aerh.AUDIT_CODE, aerh.REPORT_SUBMITTED_DATE,
aerh.LAST_AUDITED_BY, aerh.RETURN_REASON_CODE, aerh.RETURN_INSTRUCTION,
@ wf.full_name employee_name, decode(wf.current_npw_flag, 'Y', wf.npw_number,
wf.employee_number) employee_number, decode(AERH.WORKFLOW_APPROVED_FLAG,
'Y','Y', 'A','Y', 'M','Y', 'N') mgmt_reviewed_flag,
decode(AERH.WORKFLOW_APPROVED_FLAG, 'Y','Y', 'A','Y', 'P','Y',
DECODE(aerh.AUDIT_CODE, 'AUTO_APPROVE', 'Y', 'N')) ap_reviewed_flag,
AP_WEB_AUDIT_UTILS.get_audit_reason(aerh.REPORT_HEADER_ID) AUDIT_REASON,
ou.name ORG_NAME,
ap_web_expense_form.get_num_total_violations(aerh.report_header_id)
num_total_violations,
ap_web_expense_form.get_num_violation_lines(aerh.report_header_id)
num_violation_lines, DECODE(AI.Payment_status_flag,'Y', 'Y', 'N')
status_paid, DECODE(AI.INVOICE_NUM,null, 'N', 'Y') status_invoice_created,
DECODE(AI.Payment_status_flag,'P', 'Y', 'N') status_partially_paid,
AP_WEB_POLICY_UTILS.get_lookup_meaning('EXPENSE REPORT STATUS', decode(
AI.cancelled_date, NULL, aerh.expense_status_code, 'CANCELLED' ))
report_status, decode( AI.cancelled_date, NULL, aerh.expense_status_code,
'CANCELLED' ) report_status_code,
AP_WEB_AUDIT_UTILS.get_flex_structure_code(aerh.org_id)
key_flex_structure_code, DECODE(aerh.AUDIT_CODE,'AUTO_APPROVE', 'Y',
DECODE(aerh.workflow_approved_flag,'A','Y','N')) auto_approved_flag,
wf.email_address email_address, q.auditor_id orig_auditor_id,
decode(q.auditor_id, -1,
fnd_message.GET_STRING('SQLAP','OIE_AUD_FALLBACK_AUDITOR'),
AP_WEB_POLICY_UTILS.get_employee_name(q.auditor_id)) as auditor_name,
q.auditor_id auditor_id,
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_TYPES',NVL(aerh.AUDIT_CODE,'
AUDIT')) audit_type, aerh.RECEIPTS_RECEIVED_DATE ORIG_RECEIPTS_RECEIVED_DATE,
AP_WEB_AUDIT_UTILS.get_receipt_delay_days(aerh.org_id,'AUDIT_LIST')
receipt_delay_days, aerh.REPORT_SUBMITTED_DATE +
AP_WEB_AUDIT_UTILS.get_receipt_delay_days(aerh.org_id,'AUDIT_LIST')
receipt_delay_date, NVL(aerh.AUDIT_CODE,'AUDIT') audit_code_disp,
decode(aerh.AUDIT_CODE, 'PAPERLESS_AUDIT',
DECODE(AERH.WORKFLOW_APPROVED_FLAG, 'N','Y', 'M','Y', NULL,'Y', 'N'),
'RECEIPT_BASED', DECODE(AERH.WORKFLOW_APPROVED_FLAG, 'N','Y', 'M','Y',
NULL,'Y', 'N'), 'N') requires_paperless_audit,
AP_WEB_AUDIT_UTILS.is_employee_active(aerh.employee_id, aerh.org_id)
employee_active, AP_WEB_POLICY_UTILS.get_employee_name(aerh.LAST_AUDITED_BY)
last_audited_by_name,
AP_WEB_POLICY_UTILS.get_employee_name(aerh.LAST_UPDATED_BY)
last_updated_by_name,
ap_web_audit_utils.get_lookup_description('OIE_AUD_RETURN_REASONS',
aerh.return_reason_code) return_reason, ep.note_language_code,
last_auditor.user_name last_auditor_user_name, DECODE(usr.employee_id,
aerh.employee_id, 'Y', 'N') auditor_own_report,
AP_WEB_POLICY_UTILS.get_lookup_meaning('RECEIPT_STATUS',
aerh.RECEIPTS_STATUS) receipt_package_status,
DECODE(aerh.HOLDING_REPORT_HEADER_ID, null, 'N','Y') hold_status,
aerh.RECEIPTS_STATUS, aerh.attribute_category, aerh.attribute1,
aerh.attribute2, aerh.attribute3, aerh.attribute4, aerh.attribute5,
aerh.attribute6, aerh.attribute7, aerh.attribute8, aerh.attribute9,
aerh.attribute10, aerh.attribute11, aerh.attribute12, aerh.attribute13,
aerh.attribute14, aerh.attribute15, aerh.expense_status_code,
aerh.expense_last_status_date, aerh.holding_report_header_id,
decode(aerh.source,'Both Pay',pv.vendor_name, decode(aerh.source,'CREDIT
CARD',pv.vendor_name,null)) CREDIT_CARD_PROVIDER_NAME, aerh.prepay_num,
aerh.advances_justification, aerh.MAXIMUM_AMOUNT_TO_APPLY,
aerh.ADVANCE_INVOICE_TO_APPLY, aerh.PREPAY_GL_DATE, aerh.PREPAY_DIST_NUM,
aerh.PAYMENT_CURRENCY_CODE, aerh.FLEX_CONCATENATED,
aerh.apply_advances_default, aerh.image_receipts_status,
aerh.image_receipts_received_date, aerh.MISSING_IMG_JUST,
DECODE(q.auditor_id, null, 'N', 'Y') auditor_assigned ,
aerh.EXPENSE_CHECK_ADDRESS_FLAG FROM AP_EXPENSE_REPORT_HEADERS aerh,
AP_INVOICES_ALL AI, HR_ORGANIZATION_UNITS ou, AP_AUD_QUEUES q, FND_USER usr,
AP_EXPENSE_PARAMS_ALL ep, PER_PEOPLE_F wf, FND_USER last_auditor, PO_VENDORS
PV WHERE AI.INVOICE_ID(+) = AERH.VOUCHNO and ou.organization_id = aerh.org_id
and q.expense_report_id(+) = aerh.report_header_id and aerh.source in
('NonValidatedWebExpense', 'WebExpense', 'SelfService', 'CREDIT CARD', 'Both
Pay') and (aerh.Source <> 'NonValidatedWebExpense' OR
aerh.Workflow_approved_flag IS NULL OR aerh.Workflow_approved_flag in
('R','W','T')) and usr.user_id(+) = fnd_global.user_id and ep.org_id(+) =
aerh.org_id and wf.person_id = aerh.employee_id and trunc(sysdate) between
wf.effective_start_date and wf.effective_end_date and last_auditor.user_id(+)
= aerh.LAST_AUDITED_BY and aerh.vendor_id = pv.vendor_id(+)) QRSLT WHERE ((
UPPER(REPORT_STATUS_CODE) = UPPER(:6) AND (REPORT_STATUS_CODE like :7 OR
REPORT_STATUS_CODE like :8 OR REPORT_STATUS_CODE like :9 OR
REPORT_STATUS_CODE like :10))) at
oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
...
## Detail 0 ## java.sql.SQLException: Invalid column index
at
oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedSta
tement.java:5168) at 
...

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms