R12: OIE: When User Clicks Return Button on Audit Page Getting Error "You have encountered an unexpected error"

(Doc ID 1507638.1)

Last updated on AUGUST 31, 2017

Applies to:

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

Symptoms

 


iExpenses Audit Page Navigation given error in R12, it has been changed from R11.

Internet Expenses Audit Manager:

Manage Expense Report> Search for Pending Payables Approval>
Click on Confirmation Page>
Click on View Attachments> Open Attachment
Click on Return to “Return to Expense Report”>
Click on (B) Return

While pressing Return Button showing below error,

You have encountered an unexpected error. Please contact the system Administrator for assistance.

This issue is not inconsistent all the time, some user have this issue and some are not


Oracle Applications : 12.1.3

Exception details


Error Details
  Logout

  Error Page
  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,
  AP_WEB_AUDIT_UTILS.get_auditor_name(q.auditor_id) 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_audit_rule_info(aerh.org_id,'AUDIT_LIST','receipt_delay_days','NUMBER') receipt_delay_days,
  aerh.REPORT_SUBMITTED_DATE + to_number(AP_WEB_AUDIT_UTILS.get_audit_rule_info(aerh.org_id,'AUDIT_LIST','receipt_delay_days','NUMBER')) 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_AUDIT_UTILS.get_user_name(aerh.LAST_AUDITED_BY) last_audited_by_name,
  AP_WEB_AUDIT_UTILS.get_user_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
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)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:616)

...

## Detail 0 ##
java.sql.SQLException: Invalid column index

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