Error Details_jsp.txt

1518888

Ajith-Oracle

Hi,

Please explain the issue more in detail as .txt attached does not show the issue

Regards

Ajith

Erik Broeders-Oracle

The actual error seems to be:

JBO-27122: SQL error during statement preparation.  Statement: SELECT * FROM (SELECT prl.requisition_header_id,

       prl.requisition_line_id,

       prl.line_num AS LINE,

       msi.concatenated_segments AS ITEM_NUMBER,

       prl.item_revision,

       prl.item_description AS DESCRIPTION,

       por_apprv_wf_util_grp.get_cost_center(prl.requisition_line_id) AS COST_CENTER,

       nvl(muom.unit_of_measure_tl, prl.unit_meas_lookup_code) AS UOM,

       prl.quantity AS QUANTITY,

       prl.unit_price AS UNIT_PRICE,

       decode(nvl(order_type_lookup_code, 'QUANTITY'),

              'FIXED PRICE',

              prl.amount,

              'RATE',

              prl.amount,

              prl.quantity * prl.unit_price) AS LINE_AMOUNT,

       lkup.meaning as contractor_status,

       PO_POAPPROVAL_INIT1.Get_Formatted_Full_Name(prl.candidate_first_name,

                                                   prl.candidate_last_name) AS CONTRACTOR_NAME,

       prl.assignment_start_date AS START_DATE,

       prl.assignment_end_date AS END_DATE,

       prl.need_by_date AS NEED_BY,

       hrt.location_code AS LOCATION,

       per.full_name AS REQUESTOR,

       decode(nvl(prl.contractor_status, 'NULL'),

              'PENDING',

              fnd_message.get_string('ICX', 'ICX_POR_SEE_DETAILS'),

              decode(prl.source_type_code,

                     'VENDOR',

                     prl.suggested_vendor_name,

                     org.organization_code || ' - ' || org.organization_name)) AS SUGG_SUPPLIER,

       decode(nvl(prl.contractor_status, 'NULL'),

              'PENDING',

              fnd_message.get_string('ICX', 'ICX_POR_SEE_DETAILS'),

              decode(prl.source_type_code,

                     'VENDOR',

                     prl.suggested_vendor_location,

                     '')) AS SUGG_SITE,

       nvl(prl.currency_code, :1) AS TXN_CURRENCY_CODE,

       nvl(prl.currency_unit_price, prl.unit_price) AS CURRENCY_UNIT_PRICE,

       decode(nvl(order_type_lookup_code, 'QUANTITY'),

              'FIXED PRICE',

              nvl(prl.currency_amount, prl.amount),

              'RATE',

              nvl(prl.currency_amount, prl.amount),

              prl.quantity * nvl(prl.currency_unit_price, prl.unit_price)) AS CURRENCY_LINE_AMOUNT,

       decode(nvl(prl.order_type_lookup_code, 'QUANTITY'),

              'FIXED PRICE',

              0,

              decode(prl.currency_unit_price, null, 0, 1)) AS CURR_UNIT_PRICE_DISPLAY_FLAG,

       decode(nvl(prl.order_type_lookup_code, 'QUANTITY'),

              'FIXED PRICE',

              decode(prl.currency_amount, null, 0, 1),

              decode(prl.currency_unit_price, null, 0, 1)) AS CURR_AMOUNT_DISPLAY_FLAG             

  ,(select mp.name from HR_ORGANIZATION_UNITS mp where mp.organization_id = prl.destination_organization_id ) Destination_organization

  ,prl.destination_subinventory

  ,(select mil.concatenated_segments from mtl_item_locations_kfv mil where mil.inventory_location_id= prl.attribute7)   Locator

  ,case when prl.document_type_code='QUOTATION' then (select t.segment1 ||'.' || prl.blanket_po_line_num from PO_HEADERS_all t where t.po_header_id= prl.blanket_po_header_id )  else null end Quotation

  ,(select a.Last_PO from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPO

  ,(select a.Last_PO_Vendor from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPOVendor

  ,(select a.last_PO_QTY from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) lastPOQTY

  ,(select a.Last_PO_Price_USD from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPOPriceUSD                

  FROM po_requisition_lines_all     prl,

       mtl_system_items_kfv         msi,

       hr_locations_all             hrt,

       per_all_people_f             per,

       mtl_units_of_measure         muom,

       org_organization_definitions org,

       fnd_lookup_values            lkup

WHERE prl.requisition_header_id = :2

   AND NVL(prl.cancel_flag, 'N') = 'N'

   AND NVL(prl.modified_by_agent_flag, 'N') = 'N'

   AND nvl(closed_code, 'OPEN') != 'FINALLY CLOSED'

   AND hrt.location_id(+) = prl.deliver_to_location_id

   AND prl.item_id = msi.inventory_item_id(+)

   AND nvl(msi.organization_id, prl.destination_organization_id) =

       prl.destination_organization_id

   AND prl.to_person_id = per.person_id(+)

   AND per.effective_start_date(+) <= trunc(sysdate)

   AND per.effective_end_date(+) >= trunc(sysdate)

   AND prl.source_organization_id = org.organization_id(+)

   AND muom.unit_of_measure(+) = prl.unit_meas_lookup_code

   AND lkup.lookup_type(+) = 'ICX_POR_CONTRACTOR_STATUS'

   AND lkup.lookup_code(+) = prl.contractor_status

   AND lkup.language(+) = ) QRSLT  ORDER BY LINE

Erik Broeders-Oracle

Dear customer,

This issue might be due to your customizations:

  ,(select a.Last_PO from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPO

  ,(select a.Last_PO_Vendor from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPOVendor

  ,(select a.last_PO_QTY from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) lastPOQTY

  ,(select a.Last_PO_Price_USD from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPOPriceUSD     

Please remove the customizations and retest the behavior.

Thank You,
Erik
Global Customer Services

1518888

Dear customer,

This issue might be due to your customizations:

  ,(select a.Last_PO from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPO

  ,(select a.Last_PO_Vendor from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPOVendor

  ,(select a.last_PO_QTY from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) lastPOQTY

  ,(select a.Last_PO_Price_USD from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPOPriceUSD     

Please remove the customizations and retest the behavior.

Thank You,
Erik
Global Customer Services

Hi Erik,

The custom views which are using in the query are in valid status and running fine.

Thanks in advance

Erik Broeders-Oracle

Dear customer,

Often the JBO-27122 error, occurring when opening a requisition notification, is related to customizations. So to rule this out please remove the customizations and restore the seeded code. After doing so, test whether you are able to open the requisition notification.

Thank You,

Erik

Global Customer Services

1518888

Hi Ajith,

The issue is when the user tries to open a notification at that we are facing this issue.

Thanks

jjerzak-Oracle

Hi

Looking at the error stack you uploaded - you should always go to the Detail 0 section of the error stack

java.sql.SQLException: Invalid column index

    at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:6363)

-- So check your indexes here

Statement: SELECT * FROM (SELECT prl.requisition_header_id,

       prl.requisition_line_id,

       prl.line_num AS LINE,

       msi.concatenated_segments AS ITEM_NUMBER,

       prl.item_revision,

       prl.item_description AS DESCRIPTION,

       por_apprv_wf_util_grp.get_cost_center(prl.requisition_line_id) AS COST_CENTER,

       nvl(muom.unit_of_measure_tl, prl.unit_meas_lookup_code) AS UOM,

       prl.quantity AS QUANTITY,

       prl.unit_price AS UNIT_PRICE,

       decode(nvl(order_type_lookup_code, 'QUANTITY'),

              'FIXED PRICE',

              prl.amount,

              'RATE',

              prl.amount,

              prl.quantity * prl.unit_price) AS LINE_AMOUNT,

       lkup.meaning as contractor_status,

       PO_POAPPROVAL_INIT1.Get_Formatted_Full_Name(prl.candidate_first_name,

                                                   prl.candidate_last_name) AS CONTRACTOR_NAME,

       prl.assignment_start_date AS START_DATE,

       prl.assignment_end_date AS END_DATE,

       prl.need_by_date AS NEED_BY,

       hrt.location_code AS LOCATION,

       per.full_name AS REQUESTOR,

       decode(nvl(prl.contractor_status, 'NULL'),

              'PENDING',

              fnd_message.get_string('ICX', 'ICX_POR_SEE_DETAILS'),

              decode(prl.source_type_code,

                     'VENDOR',

                     prl.suggested_vendor_name,

                     org.organization_code || ' - ' || org.organization_name)) AS SUGG_SUPPLIER,

       decode(nvl(prl.contractor_status, 'NULL'),

              'PENDING',

              fnd_message.get_string('ICX', 'ICX_POR_SEE_DETAILS'),

              decode(prl.source_type_code,

                     'VENDOR',

                     prl.suggested_vendor_location,

                     '')) AS SUGG_SITE,

       nvl(prl.currency_code, :1) AS TXN_CURRENCY_CODE,

       nvl(prl.currency_unit_price, prl.unit_price) AS CURRENCY_UNIT_PRICE,

       decode(nvl(order_type_lookup_code, 'QUANTITY'),

              'FIXED PRICE',

              nvl(prl.currency_amount, prl.amount),

              'RATE',

              nvl(prl.currency_amount, prl.amount),

              prl.quantity * nvl(prl.currency_unit_price, prl.unit_price)) AS CURRENCY_LINE_AMOUNT,

       decode(nvl(prl.order_type_lookup_code, 'QUANTITY'),

              'FIXED PRICE',

              0,

              decode(prl.currency_unit_price, null, 0, 1)) AS CURR_UNIT_PRICE_DISPLAY_FLAG,

       decode(nvl(prl.order_type_lookup_code, 'QUANTITY'),

              'FIXED PRICE',

              decode(prl.currency_amount, null, 0, 1),

              decode(prl.currency_unit_price, null, 0, 1)) AS CURR_AMOUNT_DISPLAY_FLAG             

  ,(select mp.name from HR_ORGANIZATION_UNITS mp where mp.organization_id = prl.destination_organization_id ) Destination_organization

  ,prl.destination_subinventory

  ,(select mil.concatenated_segments from mtl_item_locations_kfv mil where mil.inventory_location_id= prl.attribute7)   Locator

  ,case when prl.document_type_code='QUOTATION' then (select t.segment1 ||'.' || prl.blanket_po_line_num from PO_HEADERS_all t where t.po_header_id= prl.blanket_po_header_id )  else null end Quotation

  ,(select a.Last_PO from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPO

  ,(select a.Last_PO_Vendor from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPOVendor

  ,(select a.last_PO_QTY from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) lastPOQTY

  ,(select a.Last_PO_Price_USD from xxrad_il_indx_v a where a.item_id   = prl.item_id and rownum=1) LastPOPriceUSD                

  FROM po_requisition_lines_all     prl,

       mtl_system_items_kfv         msi,

       hr_locations_all             hrt,

       per_all_people_f             per,

       mtl_units_of_measure         muom,

       org_organization_definitions org,

       fnd_lookup_values            lkup

WHERE prl.requisition_header_id = :2

   AND NVL(prl.cancel_flag, 'N') = 'N'

   AND NVL(prl.modified_by_agent_flag, 'N') = 'N'

   AND nvl(closed_code, 'OPEN') != 'FINALLY CLOSED'

   AND hrt.location_id(+) = prl.deliver_to_location_id

   AND prl.item_id = msi.inventory_item_id(+)

   AND nvl(msi.organization_id, prl.destination_organization_id) =

       prl.destination_organization_id

   AND prl.to_person_id = per.person_id(+)

   AND per.effective_start_date(+) <= trunc(sysdate)

   AND per.effective_end_date(+) >= trunc(sysdate)

   AND prl.source_organization_id = org.organization_id(+)

   AND muom.unit_of_measure(+) = prl.unit_meas_lookup_code

   AND lkup.lookup_type(+) = 'ICX_POR_CONTRACTOR_STATUS'

   AND lkup.lookup_code(+) = prl.contractor_status

   AND lkup.language(+) = ) QRSLT  ORDER BY LINE