Renewal Quote Java Exception Error Page when Add Assets Used on Renewal Quote (Doc ID 2045700.1)

Last updated on JANUARY 20, 2017

Applies to:

Oracle Lease and Finance Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Asset Management

When attempting to use the Add Assets Button to select assets on Renewal Quote,
the following error occurs.

ERROR
-----------------------
You have encountered an unexpected error. Please contact the system admin for assistance.
Click here for exception details

Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT
  CLEB_TOP.ID ID ,
  CLET.NAME NAME ,
  CLET.ITEM_DESCRIPTION ITEM_DESCRIPTION ,
  CLEB_TOP.CHR_ID CHR_ID ,
  OKHB.CONTRACT_NUMBER CONTRACT_NUMBER ,
  OKHB.STS_CODE CONTRACT_STATUS,
  CLEB_TOP.START_DATE START_DATE,
  CLEB_TOP.END_DATE END_DATE,
  CLEB_TOP.STS_CODE LINE_STATUS,
  KLE.OEC ORIGINAL_COST,
  FA.SERIAL_NUMBER SERIAL_NUMBER,
  FA.IN_SERVICE_DATE IN_SERVICE_DATE,
  FA.MANUFACTURER_NAME MANUFACTURER_NAME,
  FA.MODEL_NUMBER MODEL_NUMBER,
  FA.ASSET_TYPE ASSET_TYPE,
  fa_lookups.meaning asset_type_meaning,
  FA.LIFE_IN_MONTHS LIFE_IN_MONTHS,
  FA.NEW_USED NEW_USED,
  FA.SALVAGE_VALUE SALVAGE_VALUE,
  FA.DEPRECIATION_CATEGORY DEPRECIATION_CATEGORY,
  FAC.CATEGORY_ID,FVC.CONCATENATED_SEGMENTS ASSET_CATEGORY_CODE,
  FA.DEPRN_START_DATE DEPRN_START_DATE,
  FAC.DESCRIPTION DEPRN_CATEGORY_DESC,
  CIM.NUMBER_OF_ITEMS ASSET_UNITS,
  CIM.NUMBER_OF_ITEMS TRMNT_UNITS,
  OKHB.CURRENCY_CODE,
  CSTS.MEANING CONTRACT_STATUS_DESC,
  LSTS.MEANING LINE_STATUS_DESC,
  FND_LOOKUPS.MEANING ASSET_TYPE1 ,
  KLE.residual_value,
  mtl.description,
  okl_lease_quote_pvt.get_asset_location(cleb_top.dnz_chr_id,cleb_top.id) party_site_name,
  /*(SELECT SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
  hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
  null, null,null,null,null,null,null,'n','n',80,1,1),1,80)
  FROM hz_locations hl,
  hz_party_sites hps,
  hz_party_site_uses hpu
  WHERE hpu.party_site_use_id = ITI.OBJECT_ID1_NEW
  AND hps.party_site_id = hpu.party_site_id
  AND hl.location_id = hps.location_id ) party_site_name,*/

CASE
  WHEN eot.eot_type_code IN ('PERCENT' , 'RESIDUAL_PERCENT')
  THEN nvl(eotv.eot_value,0)
  WHEN eot.eot_type_code IN ('AMOUNT' , 'RESIDUAL_AMOUNT')
  THEN nvl((eotv.eot_value * CIM.NUMBER_OF_ITEMS),0)
  ELSE 0 END EOT_VALUE1,
  EOT_VER.END_OF_TERM_VER_ID EOT_OPTION_ID
 FROM
  OKC_K_LINES_B CLEB_TOP,
  OKC_K_LINES_B CLEB,
  OKC_K_LINES_TL CLET,
  OKX_ASSETS_V FA,
  OKC_K_ITEMS CIM,
  okc_k_items CIM_MTL,
  OKC_K_HEADERS_B OKHB,
  OKC_LINE_STYLES_B LSEB,
  OKC_LINE_STYLES_B LSEB_TOP,
  FA_CATEGORIES_VL FAC,FA_CATEGORIES_KFV FVC,
  OKL_K_LINES KLE,
  OKC_STATUSES_V CSTS,
  OKC_STATUSES_V LSTS,
  FND_LOOKUPS,FA_LOOKUPS,
  OKL_FE_EO_TERM_VERS EOT_VER,
  OKL_FE_EO_TERMS_ALL_B EOT,
  OKL_FE_EO_TERM_VALUES EOTV,
-- ,OKC_K_LINES_B CLEB_INST,
-- OKC_K_LINES_B CLEB_IB,
  OKC_K_LINES_B CLEB_MTL,
  OKC_LINE_STYLES_B LSE_INST,
  OKC_LINE_STYLES_B LSE_IB,
  OKC_LINE_STYLES_B LSE_MTL,
  mtl_system_items_tl MTL
 -- OKL_TXL_ITM_INSTS ITI
 WHERE CLEB_TOP.ID = CLET.ID
 AND CLET.LANGUAGE = USERENV('LANG')
 AND CLEB_TOP.ID = CLEB.CLE_ID(+)
 AND CIM.DNZ_CHR_ID = CLEB.DNZ_CHR_ID
 AND CIM.CLE_ID = CLEB.ID
 AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
 AND FA.ID1(+) = CIM.OBJECT1_ID1
 AND FA.ID2(+) = CIM.OBJECT1_ID2
 AND OKHB.id = CLEB_TOP.CHR_ID
 AND CLEB.LSE_ID = LSEB.ID
 AND CLEB_TOP.LSE_ID = LSEB_TOP.ID
 AND LSEB_TOP.LTY_CODE = 'FREE_FORM1'
 AND LSEB.LTY_CODE = 'FIXED_ASSET'
 AND FAC.CATEGORY_ID (+) = FA.DEPRECIATION_CATEGORY
 AND FVC.CATEGORY_ID (+) = FA.DEPRECIATION_CATEGORY
 AND CLEB_TOP.STS_CODE <> 'ABANDONED'
 AND CLEB.STS_CODE <> 'ABANDONED'
 AND CLEB_TOP.ID = KLE.ID
 AND CSTS.CODE = OKHB.STS_CODE
 AND LSTS.CODE = CLEB_TOP.STS_CODE
 And FND_LOOKUPS.LOOKUP_CODE = DECODE(KLE.RE_LEASE_YN,'Y','RELEASE','NEW')
 AND FND_LOOKUPS.LOOKUP_TYPE='OKL_ASSET_TYPE'
 AND FA_LOOKUPS.LOOKUP_CODE =FA.ASSET_TYPE
 AND FA_LOOKUPS.LOOKUP_TYPE ='ASSET TYPE'
 AND EOT_VER.END_OF_TERM_ID = EOT.END_OF_TERM_ID
AND EOTV.END_OF_TERM_VER_ID(+) = EOT_VER.END_OF_TERM_VER_ID
 --and CLEB_INST.CLE_ID = CLEB_TOP.ID
 --and CLEB_INST.DNZ_CHR_ID = CLEB_TOP.CHR_ID
 --and CLEB_INST.LSE_ID = LSE_INST.ID
 and LSE_INST.LTY_CODE = 'FREE_FORM2'
 --and CLEB_IB.CLE_ID = CLEB_INST.ID
 --and CLEB_IB.DNZ_CHR_ID = CLEB_INST.DNZ_CHR_ID
 --and CLEB_IB.LSE_ID = LSE_IB.ID
 and LSE_IB.LTY_CODE = 'INST_ITEM'
 --and ITI.KLE_ID = CLEB_IB.ID
 and CLEB_MTL.CLE_ID = CLEB_TOP.ID
 and CLEB_MTL.DNZ_CHR_ID = CLEB_TOP.CHR_ID
 and CLEB_MTL.LSE_ID = LSE_MTL.ID
 and LSE_MTL.LTY_CODE = 'ITEM'
 and CIM_MTL.cle_id = CLEB_MTL.id
 and MTL.inventory_item_id=CIM_MTL.OBJECT1_ID1
 AND MTL.LANGUAGE=USERENV('LANG')
 --ANd MTL.organization_id=mo_global.get_current_org_id()
 AND MTL.organization_id = OKHB.inv_organization_id
 AND EOT.ORG_ID=OKHB.authoring_org_id
and CLEB_TOP.CHR_ID=:1
and EOT_VER.END_OF_TERM_VER_ID=:2


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Pre-requisite OLFM Lease Superuser Responsibility :
2. Create and Book contracts that are Evergreen Eligible (that may be renewed)
3. Create EOL Option for financial product to be used on Renewal Quote
4. Run Billing and Accruals as deemed appropriate
5. OLFM Lease Superuser Responsibility > Query Contract and select Quote for Modifications in the Actions Field
6. Click Renewal Quotes Tab and Create Renewal Quote and Enter required data in the header section of the form
7. Click Add Assets Button, Error Page is displayed

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