2 Replies Latest reply on Dec 22, 2008 2:12 PM by 567343

    java.sql.SQLException: Attempt to set a parameter name that does not occur

    567343
      Hi,
      my query
      select lines.description,
      ph.segment1,
      lines.quantity_invoiced,
      lines.unit_meas_lookup_code,
      lines.unit_price,
      lines.amount,
      alc.displayed_field approval_status,
      to_char(lines.unit_price,FND_CURRENCY.SAFE_GET_FORMAT_MASK(ai.INVOICE_CURRENCY_CODE,25)) unit_price_disp,
      to_char(lines.amount,FND_CURRENCY.SAFE_GET_FORMAT_MASK(ai.INVOICE_CURRENCY_CODE,25)) amount_disp
           ,ppa.segment1 project
           ,pt.task_number task
           ,lines.expenditure_type
      from ap_invoice_lines_all lines,
      ap_invoices_all ai,
      po_headers_all ph,
      ap_lookup_codes alc,
      ap_holds_all holds,
      pa_projects_all ppa,
      pa_tasks pt
      where lines.po_header_id = ph.po_header_id(+)
      and lines.invoice_id = ai.invoice_id
      and alc.lookup_type = 'AP_WFAPPROVAL_STATUS'
      and alc.lookup_code = lines.wfapproval_status
      and nvl(holds.line_location_id,-99) = nvl(lines.po_line_location_id,-99)
      and holds.invoice_id = lines.invoice_id
      and ppa.project_id = pt.task_id
      and lines.project_id = ppa.project_id(+)
      and holds.hold_id = :0
      and 1 = :1
      union
      select lines.description,
      ph.segment1,
      lines.quantity_invoiced,
      lines.unit_meas_lookup_code,
      lines.unit_price,
      lines.amount,
      alc.displayed_field approval_status,
      to_char(lines.unit_price,FND_CURRENCY.SAFE_GET_FORMAT_MASK(ai.INVOICE_CURRENCY_CODE,25)) unit_price_disp,
      to_char(lines.amount,FND_CURRENCY.SAFE_GET_FORMAT_MASK(ai.INVOICE_CURRENCY_CODE,25)) amount_disp
           ,ppa.segment1 project
           ,pt.task_number task
           ,lines.expenditure_type
      from ap_invoice_lines_all lines,
      ap_invoices_all ai,
      po_headers_all ph,
      ap_lookup_codes alc,
      ap_apinv_approvers aprv,
           pa_projects_all ppa,
      pa_tasks pt
      where lines.po_header_id = ph.po_header_id(+)
      and lines.invoice_id = ai.invoice_id
      and alc.lookup_type = 'AP_WFAPPROVAL_STATUS'
      and alc.lookup_code = lines.wfapproval_status
      and ppa.project_id = pt.task_id
      and lines.project_id = ppa.project_id(+)
      and lines.invoice_id = :2
      and aprv.child_process_item_type = :3
      and aprv.child_process_item_key = :4
      and aprv.invoice_id = lines.invoice_id
      and aprv.line_number = lines.line_number
      and 1 = :5
      union
      select lines.description,
      ph.segment1,
      lines.quantity_invoiced,
      lines.unit_meas_lookup_code,
      lines.unit_price,
      lines.amount,
      alc.displayed_field approval_status,
      to_char(lines.unit_price,FND_CURRENCY.SAFE_GET_FORMAT_MASK(ai.INVOICE_CURRENCY_CODE,25)) unit_price_disp,
      to_char(lines.amount,FND_CURRENCY.SAFE_GET_FORMAT_MASK(ai.INVOICE_CURRENCY_CODE,25)) amount_disp
           ,ppa.segment1 project
           ,pt.task_number task
           ,lines.expenditure_type
      from ap_invoice_lines_all lines,
      ap_invoices_all ai,
      po_headers_all ph,
      ap_lookup_codes alc,
           pa_projects_all ppa,
      pa_tasks pt
      where lines.po_header_id = ph.po_header_id(+)
      and lines.invoice_id = ai.invoice_id
      and alc.lookup_type = 'AP_WFAPPROVAL_STATUS'
      and alc.lookup_code(+) = lines.wfapproval_status
      and ppa.project_id = pt.task_id
      and lines.project_id = ppa.project_id(+)
      and lines.invoice_id = :6
      and 1 = :7

      and the error is

      ## Detail 0 ##
      java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: 8

      Please suggest .

      query is valid

      Rgds,
      Shahnawaz