OKL Generate Pending Asset Repossession Report for Loans

 SELECT LINESTL.NAME ASSET_NUMBER,
        ARB.ARS_CODE ARS_CODE,
        ARB.DATE_RETURN_DUE DATE_RETURN_DUE,
        to_char(ARB.DATE_RETURNED,'DD-MON-RR') DATE_RETURNED,
        OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE  FUNC_CCY_CODE,
        okl_accounting_util.format_amount(ARB.ASSET_FMV_AMOUNT ,OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE) ASSET_FMV_AMOUNT,
        LOOKUPS.MEANING ARS_CODE_MEANING,
        LINESTL.ITEM_DESCRIPTION ASSET_DESCRIPTION,
        OKX_VEND.NAME REPOSSESSION_AGENT,
        ITEMS.NUMBER_OF_ITEMS NUMBER_OF_ITEMS,
        (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) party_site_name
from hz_locations hl,
  hz_party_sites hps,
  csi_item_instances csi,
  okc_k_items cim_ib,
  okc_line_styles_b lse_ib,
  okc_k_lines_b cle_ib,
  okc_line_styles_b lse_inst,
  okc_k_lines_b cle_inst,
  okc_line_styles_b lse_fin,
  okc_k_lines_b cle_fin
where cle_fin.cle_id is null
and cle_fin.chr_id = cle_fin.dnz_chr_id
and lse_fin.id = cle_fin.lse_id
and lse_fin.lty_code = 'FREE_FORM1'
and cle_inst.cle_id = cle_fin.id
and cle_inst.dnz_chr_id = cle_fin.dnz_chr_id
and cle_inst.lse_id = lse_inst.id
and lse_inst.lty_code = 'FREE_FORM2'
and cle_ib.cle_id = cle_inst.id
and cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
and cle_ib.lse_id = lse_ib.id
and lse_ib.lty_code = 'INST_ITEM'
and cim_ib.cle_id = cle_ib.id
and cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
and cim_ib.object1_id1 = csi.instance_id
and cim_ib.object1_id2 = '#'
and cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
and csi.install_location_id = hps.party_site_id
and csi.install_location_type_code = 'HZ_PARTY_SITES'
and hps.location_id = hl.location_id
and cle_fin.dnz_chr_id = :CONTRACT_ID
and cle_fin.id = ARB.KLE_ID
and rownum = 1
        ) PARTY_SITE_NAME,
        ARB.KLE_ID KLE_ID
 FROM OKL_ASSET_RETURNS_ALL_B ARB,
      OKC_K_LINES_B LINESB,
      OKC_K_LINES_TL LINESTL,
      FND_LOOKUPS LOOKUPS,
      OKX_VENDORS_V OKX_VEND,
      OKC_K_ITEMS ITEMS,
      OKC_LINE_STYLES_B LSE,
      OKC_K_LINES_B LINESB2
WHERE ARB.KLE_ID = LINESB.ID
AND   LINESB.LSE_ID = 33
AND   LINESB.DNZ_CHR_ID = :CONTRACT_ID
AND   LINESB.ID = LINESTL.ID
AND   LINESTL.LANGUAGE = USERENV('LANG')
AND   LOOKUPS.LOOKUP_TYPE (+) = 'OKL_ASSET_RETURN_STATUS'
AND   ARB.ARS_CODE (+) = LOOKUPS.LOOKUP_CODE
AND   ARB.RNA_ID = OKX_VEND.ID1 (+)
AND   ARB.KLE_ID = LINESB2.CLE_ID
AND   LINESB2.LSE_ID = LSE.ID
AND   LSE.LTY_CODE = 'ITEM'
AND   LINESB2.ID = ITEMS.CLE_ID
ORDER BY LINESTL.NAME, ARB.ARS_CODE