eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OKL_AM_CREATE_QUOTE_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 34


	--Updated the sql statement for performance issue #5484903
	-- by excluding UPPER function to do Index scan
        CURSOR asset_chk_curs1 (p_asset_number IN VARCHAR2) IS
	SELECT 'Y' a
        FROM   okx_assets_v okx
        WHERE  UPPER(okx.asset_number) = p_asset_number
        and
	    ( okx.asset_number like Initcap(substr(p_asset_number,1,2))||'%'
               or
               okx.asset_number like lower(substr(p_asset_number,1))||Upper(substr(p_asset_number,2,1))||'%'
               or
               okx.asset_number like Upper(substr(p_asset_number,1,2))||'%'
               or
               okx.asset_number like lower(substr(p_asset_number,1,2))||'%'
             );

Line: 53


	--Updated the sql statement for performance issue #5484903
	-- by excluding UPPER function to do Index scan
        CURSOR asset_chk_curs2 (p_asset_number IN VARCHAR2) IS
        SELECT 'Y' a
        FROM   okc_k_lines_v kle,
               okc_line_styles_b  lse
        WHERE  kle.lse_id = lse.id
        AND    lse.lty_code = 'FREE_FORM1'
        AND  UPPER(kle.NAME) = p_asset_number  -- RMUNJULU 3241502
        AND ( kle.NAME like Initcap(substr(p_asset_number,1,2))||'%'
               or
               kle.NAME like lower(substr(p_asset_number,1))||Upper(substr(p_asset_number,2,1))||'%'
               or
               kle.NAME like Upper(substr(p_asset_number,1,2))||'%'
              or
               kle.NAME like lower(substr(p_asset_number,1,2))||'%'
             ) ;

Line: 75


        SELECT 'Y' a
        FROM   okl_txd_assets_b txd
        WHERE  NVL(UPPER(txd.asset_number),'-999999999999999') = UPPER(p_asset_number) -- RMUNJULU 3241502
        AND    EXISTS (SELECT NULL
                       FROM   okl_trx_Assets   trx,
                              okl_trx_types_tl ttyp,
                              okl_txl_assets_b txl
                       WHERE  trx.id        = txl.tas_id
                       AND    trx.try_id    = ttyp.id
                       AND    ttyp.name     = 'Split Asset'
                       AND    ttyp.language = 'US'
                       AND    txl.id        = txd.tal_id);

Line: 203


  SELECT chr.id chr_id,
         chr.contract_number contract_number,
         chr.start_date from_start_date,
         chr.end_date from_end_date,
         stl.code sts_code,
         stl.meaning sts_meaning,
         chr.authoring_org_id org_id,
         hp.party_name party_name
  FROM okc_statuses_tl stl,
       hz_parties hp,
       okc_k_party_roles_b cpl,
       okc_k_headers_b chr
-- BAKUCHIB 2781134 start
  WHERE upper(chr.contract_number) LIKE upper(nvl(p_achr_rec.contract_number,chr.contract_number))
-- BAKUCHIB 2807201 start
  AND nvl(chr.start_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_start_date,nvl(chr.start_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_start_date,nvl(chr.start_date,to_date('1111','yyyy')))
  AND nvl(chr.end_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_end_date,nvl(chr.end_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_end_date,nvl(chr.end_date,to_date('1111','yyyy')))
-- BAKUCHIB 2807201 end
-- BAKUCHIB 2781134 end
-- BAKUCHIB 2748110 Start
  AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
  AND chr.scs_code IN ('LEASE', 'LOAN')
  AND chr.authoring_org_id = p_achr_rec.org_id
-- BAKUCHIB 2748110 end
  AND chr.id = cpl.dnz_chr_id
  AND cpl.chr_id = cpl.dnz_chr_id
  AND cpl.object1_id1 = hp.party_id
  AND cpl.object1_id2 = '#'
  AND cpl.jtot_object1_code = 'OKX_PARTY'
  AND cpl.rle_code = 'LESSEE'
  AND cpl.cle_id IS NULL
-- BAKUCHIB 2781134 start
  AND upper(hp.party_name) LIKE upper(nvl(p_achr_rec.party_name,hp.party_name))
-- BAKUCHIB 2781134 end
  AND hp.party_type IN ( 'PERSON','ORGANIZATION')
  AND chr.sts_code = stl.code
  AND stl.LANGUAGE = userenv('LANG')
  ORDER BY contract_number ASC;

Line: 245


  SELECT chr.id chr_id,
         chr.contract_number contract_number,
         chr.start_date from_start_date,
         chr.end_date from_end_date,
         stl.code sts_code,
         stl.meaning sts_meaning,
         chr.authoring_org_id org_id,
         hp.party_name party_name
  FROM okc_statuses_tl stl,
       hz_parties hp,
       okc_k_party_roles_b cpl,
       okc_k_headers_b chr
-- BAKUCHIB 2781134 start
  WHERE upper(chr.contract_number) LIKE upper(nvl(p_achr_rec.contract_number,chr.contract_number))
-- BAKUCHIB 2781134 end
-- BAKUCHIB 2748110 Start
  AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
  AND chr.scs_code IN ('LEASE', 'LOAN')
  AND chr.authoring_org_id = p_achr_rec.org_id
-- BAKUCHIB 2748110 End
-- BAKUCHIB 2781134 start
-- BAKUCHIB 2807201 start
  AND nvl(chr.start_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_start_date,nvl(chr.start_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_start_date,nvl(chr.start_date,to_date('1111','yyyy')))
  AND nvl(chr.end_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_end_date,nvl(chr.end_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_end_date,nvl(chr.end_date,to_date('1111','yyyy')))
-- BAKUCHIB 2807201 end
-- BAKUCHIB 2781134 end
  AND chr.id = cpl.dnz_chr_id
  AND cpl.chr_id = cpl.dnz_chr_id
  AND cpl.object1_id1 = hp.party_id
  AND cpl.object1_id2 = '#'
  AND cpl.jtot_object1_code = 'OKX_PARTY'
  AND cpl.rle_code = 'LESSEE'
  AND cpl.cle_id IS NULL
-- BAKUCHIB 2781134 start
  AND upper(hp.party_name) LIKE upper(nvl(p_achr_rec.party_name,hp.party_name))
-- BAKUCHIB 2781134 end
  AND hp.party_type IN ( 'PERSON','ORGANIZATION')
  AND chr.sts_code = stl.code
  AND stl.LANGUAGE = userenv('LANG')
  AND chr.id IN (SELECT DISTINCT cle_fin.dnz_chr_id chr_id
                 FROM okc_line_styles_b lse_fin,
                      okc_k_lines_tl clet_fin,
                      okc_k_lines_b cle_fin,
                      okc_k_headers_b chr
                 WHERE cle_fin.cle_id IS NULL
                 AND cle_fin.chr_id = cle_fin.dnz_chr_id
                 AND cle_fin.dnz_chr_id = chr.id
                 AND cle_fin.id = clet_fin.id
                 AND clet_fin.LANGUAGE = userenv('LANG')
                 AND lse_fin.id = cle_fin.lse_id
                 AND lse_fin.lty_code = 'FREE_FORM1'
-- BAKUCHIB 2748110 Start
                 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
                 AND chr.scs_code IN ('LEASE', 'LOAN')
                 AND chr.authoring_org_id = p_achr_rec.org_id
-- BAKUCHIB 2748110 End
-- BAKUCHIB 2781134 start
                 AND upper(nvl(clet_fin.name,'x')) LIKE upper(nvl(p_achr_rec.asset_number,nvl(clet_fin.name,'x'))))
-- BAKUCHIB 2781134 end
  ORDER BY contract_number ASC;

Line: 309


  SELECT chr.id chr_id,
         chr.contract_number contract_number,
         chr.start_date from_start_date,
         chr.end_date from_end_date,
         stl.code sts_code,
         stl.meaning sts_meaning,
         chr.authoring_org_id org_id,
         hp.party_name party_name
  FROM okc_statuses_tl stl,
       hz_parties hp,
       okc_k_party_roles_b cpl,
       okc_k_headers_b chr
-- BAKUCHIB 2781134 start
  WHERE upper(chr.contract_number) LIKE upper(nvl(p_achr_rec.contract_number,chr.contract_number))
-- BAKUCHIB 2781134 end
-- BAKUCHIB 2748110 Start
  AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
  AND chr.scs_code IN ('LEASE', 'LOAN')
  AND chr.authoring_org_id = p_achr_rec.org_id
-- BAKUCHIB 2748110 End
-- BAKUCHIB 2781134 start
-- BAKUCHIB 2807201 start
  AND nvl(chr.start_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_start_date,nvl(chr.start_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_start_date,nvl(chr.start_date,to_date('1111','yyyy')))
  AND nvl(chr.end_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_end_date,nvl(chr.end_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_end_date,nvl(chr.end_date,to_date('1111','yyyy')))
-- BAKUCHIB 2807201 end
-- BAKUCHIB 2781134 end
  AND chr.id = cpl.dnz_chr_id
  AND cpl.chr_id = cpl.dnz_chr_id
  AND cpl.object1_id1 = hp.party_id
  AND cpl.object1_id2 = '#'
  AND cpl.jtot_object1_code = 'OKX_PARTY'
  AND cpl.rle_code = 'LESSEE'
  AND cpl.cle_id IS NULL
-- BAKUCHIB 2781134 start
  AND upper(hp.party_name) LIKE upper(nvl(p_achr_rec.party_name,hp.party_name))
-- BAKUCHIB 2781134 end
  AND hp.party_type IN ( 'PERSON','ORGANIZATION')
  AND chr.sts_code = stl.code
  AND stl.LANGUAGE = userenv('LANG')
  AND chr.id IN (SELECT DISTINCT cim_ib.dnz_chr_id chr_id
                 FROM csi_item_instances csi,
                      okc_k_items cim_ib,
                      okc_line_styles_b lse_ib,
                      okc_k_lines_b cle_ib,
                      okc_k_headers_b chr
                 WHERE 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 cle_ib.dnz_chr_id = chr.id
-- BAKUCHIB 2748110 Start
                 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
                 AND chr.scs_code IN ('LEASE', 'LOAN')
                 AND chr.authoring_org_id = p_achr_rec.org_id
-- BAKUCHIB 2748110 End
                 AND cim_ib.object1_id1 = csi.instance_id
                 AND cim_ib.object1_id2 = '#'
                 AND cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
-- BAKUCHIB 2781134 start
                 AND upper(nvl(csi.serial_number,'x')) LIKE upper(nvl(p_achr_rec.serial_number,nvl(csi.serial_number,'x'))))
-- BAKUCHIB 2781134 end
  ORDER BY contract_number ASC;

Line: 375


  SELECT chr.id chr_id,
         chr.contract_number contract_number,
         chr.start_date from_start_date,
         chr.end_date from_end_date,
         stl.code sts_code,
         stl.meaning sts_meaning,
         chr.authoring_org_id org_id,
         hp.party_name party_name
  FROM okc_statuses_tl stl,
       hz_parties hp,
       okc_k_party_roles_b cpl,
       okc_k_headers_b chr
-- BAKUCHIB 2781134 start
  WHERE upper(chr.contract_number) LIKE upper(nvl(p_achr_rec.contract_number,chr.contract_number))
-- BAKUCHIB 2781134 end
-- BAKUCHIB 2748110 Start
  AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
  AND chr.scs_code IN ('LEASE', 'LOAN')
  AND chr.authoring_org_id = p_achr_rec.org_id
-- BAKUCHIB 2748110 End
-- BAKUCHIB 2781134 start
-- BAKUCHIB 2807201 start
  AND nvl(chr.start_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_start_date,nvl(chr.start_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_start_date,nvl(chr.start_date,to_date('1111','yyyy')))
  AND nvl(chr.end_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_end_date,nvl(chr.end_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_end_date,nvl(chr.end_date,to_date('1111','yyyy')))
-- BAKUCHIB 2807201 end
-- BAKUCHIB 2781134 end
  AND chr.id = cpl.dnz_chr_id
  AND cpl.chr_id = cpl.dnz_chr_id
  AND cpl.object1_id1 = hp.party_id
  AND cpl.object1_id2 = '#'
  AND cpl.jtot_object1_code = 'OKX_PARTY'
  AND cpl.rle_code = 'LESSEE'
  AND cpl.cle_id IS NULL
-- BAKUCHIB 2781134 start
  AND upper(hp.party_name)LIKE upper(nvl(p_achr_rec.party_name,hp.party_name))
-- BAKUCHIB 2781134 end
  AND hp.party_type IN ( 'PERSON','ORGANIZATION')
  AND chr.sts_code = stl.code
  AND stl.LANGUAGE = userenv('LANG')
  AND chr.id IN (SELECT DISTINCT cle_fin.dnz_chr_id chr_id
                 FROM 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_tl clet_fin,
                      okc_k_lines_b cle_fin,
                      okc_k_headers_b chr
                 WHERE cle_fin.cle_id IS NULL
                 AND cle_fin.chr_id = cle_fin.dnz_chr_id
                 AND cle_fin.dnz_chr_id = chr.id
-- BAKUCHIB 2748110 Start
                 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
                 AND chr.scs_code IN ('LEASE', 'LOAN')
                 AND chr.authoring_org_id = p_achr_rec.org_id
-- BAKUCHIB 2748110 End
                 AND cle_fin.id = clet_fin.id
                 AND clet_fin.LANGUAGE = userenv('LANG')
                 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_fin.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'
-- BAKUCHIB 2781134 start
                 AND upper(nvl(csi.serial_number,'x')) LIKE upper(nvl(p_achr_rec.serial_number,nvl(csi.serial_number,'x')))
                 AND upper(nvl(clet_fin.name,'x')) LIKE upper(nvl(p_achr_rec.asset_number,nvl(clet_fin.name,'x'))))
-- BAKUCHIB 2781134 end
  ORDER BY contract_number ASC;

Line: 1520


       SELECT K.contract_number, K.sts_code, K.start_date, K.end_date -- rmunjulu EDAT
       FROM   OKC_K_HEADERS_B  K
       WHERE  K.id     = p_khr_id;

Line: 1528


       SELECT Q.quote_number, Q.qtp_code
       FROM   OKL_TRX_QUOTES_B Q
       WHERE  Q.khr_id  = p_khr_id
       AND    Q.accepted_yn = 'Y';

Line: 1537


      SELECT  KLE.chr_id, KLE.start_date -- rmunjulu EDAT
      FROM    OKC_K_LINES_B   KLE,
              OKC_K_HEADERS_B KHR
      WHERE   KLE.id = p_kle_id
      AND     KLE.chr_id = KHR.id
      AND     KLE.sts_code = KHR.sts_code;

Line: 1547


    SELECT name
    FROM   okl_k_lines_full_v
    WHERE  id = p_id;

Line: 1556


    SELECT '1'
    FROM dual WHERE EXISTS (
               SELECT '1'
               FROM   okc_k_headers_b   oks_chrb,
                      okc_line_styles_b oks_cov_pd_lse,
                      okc_k_lines_b     oks_cov_pd_cleb,
                      okc_k_rel_objs    krel,
                      okc_line_styles_b lnk_srv_lse,
                      okc_statuses_b    lnk_srv_sts,
                      okc_k_lines_b     lnk_srv_cleb,
                      okc_k_items       lnk_srv_cim
               WHERE  oks_chrb.scs_code            = 'SERVICE'
               AND    oks_chrb.id                  = oks_cov_pd_cleb.dnz_chr_id
               AND    oks_cov_pd_cleb.lse_id       = oks_cov_pd_lse.id
               AND    oks_cov_pd_lse.lty_code      = 'COVER_PROD'
               AND    '#'                          = krel.object1_id2
               AND    oks_cov_pd_cleb.id           = krel.object1_id1
               AND    krel.rty_code                = 'OKLSRV'
               AND    krel.chr_id                  = lnk_srv_cleb.dnz_chr_id
               AND    krel.cle_id                  = lnk_srv_cleb.id
               AND    lnk_srv_cleb.lse_id          = lnk_srv_lse.id
               AND    lnk_srv_lse.lty_code         = 'LINK_SERV_ASSET'
               AND    lnk_srv_cleb.sts_code        = lnk_srv_sts.code
               AND    lnk_srv_sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
               AND    lnk_srv_cleb.dnz_chr_id       = lnk_srv_cim.dnz_chr_id
               AND    lnk_srv_cleb.id               = lnk_srv_cim.cle_id
               AND    lnk_srv_cim.jtot_object1_code = 'OKX_COVASST'
               AND    lnk_srv_cim.object1_id2       = '#'
               AND    lnk_srv_cim.object1_id1       = TO_CHAR(p_kle_id));

Line: 1589


    SELECT INITIAL_ASSET_ID
    FROM   FA_SYSTEM_CONTROLS;

Line: 3069


      SELECT  contract_number, end_date
      FROM    OKC_K_HEADERS_B
      WHERE   id = p_chr_id;

Line: 3078


      SELECT COUNT(OKLV.id )
      FROM   OKC_K_LINES_V       OKLV,
             OKC_LINE_STYLES_V   OLSV,
             OKC_K_HEADERS_V     KHR
      WHERE  OKLV.lse_id = OLSV.id
      AND    OLSV.lty_code = 'FREE_FORM1'
      AND    OKLV.chr_id = p_chr_id
      AND    OKLV.sts_code = KHR.sts_code
      AND    OKLV.chr_id = KHR.id;

Line: 3092


    SELECT display_name
    FROM   wf_roles
    WHERE  name = p_name;

Line: 3237


    SELECT SYSDATE INTO l_sys_date FROM DUAL;

Line: 3528


          'before call to OKL_TRX_QUOTES_PUB.insert_trx_quotes :'||l_return_status);

Line: 3532


    OKL_TRX_QUOTES_PUB.insert_trx_quotes (
         p_api_version      =>   p_api_version,
         p_init_msg_list    =>   OKL_API.G_FALSE,
         x_msg_count        =>   x_msg_count,
         x_msg_data         =>   x_msg_data,
         p_qtev_rec         =>   lp_quot_rec,
         x_qtev_rec         =>   lx_quot_rec,
         x_return_status    =>   l_return_status);

Line: 3543


          'after call to OKL_TRX_QUOTES_PUB.insert_trx_quotes :'||l_return_status);

Line: 3632


          'before call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);

Line: 3635


    OKL_TRX_QUOTES_PUB.update_trx_quotes (
         p_api_version      =>   p_api_version,
         p_init_msg_list    =>   OKL_API.G_FALSE,
         x_msg_count        =>   x_msg_count,
         x_msg_data         =>   x_msg_data,
         p_qtev_rec         =>   lp_quot_rec,
         x_qtev_rec         =>   lx_quot_rec,
         x_return_status    =>   l_return_status);

Line: 3646


          'After call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);

Line: 3719


          'Before call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);

Line: 3723


    OKL_TRX_QUOTES_PUB.update_trx_quotes (
         p_api_version      =>   p_api_version,
         p_init_msg_list    =>   OKL_API.G_FALSE,
         x_msg_count        =>   x_msg_count,
         x_msg_data         =>   x_msg_data,
         p_qtev_rec         =>   lp_quot_rec,
         x_qtev_rec         =>   lx_quot_rec,
         x_return_status    =>   l_return_status);

Line: 3734


         'After call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);

Line: 3941


                'Before call to OKL_TRX_QUOTES_PUB.update_trx_quotes'||l_return_status);

Line: 3945


       OKL_TRX_QUOTES_PUB.update_trx_quotes (
         p_api_version      =>   p_api_version,
         p_init_msg_list    =>   OKL_API.G_FALSE,
         x_msg_count        =>   x_msg_count,
         x_msg_data         =>   x_msg_data,
         p_qtev_rec         =>   lp_quot_rec,
         x_qtev_rec         =>   lx_quot_rec,
         x_return_status    =>   l_return_status);

Line: 3956


                'After call to OKL_TRX_QUOTES_PUB.update_trx_quotes'||l_return_status);

Line: 4079


   SELECT NVL(repo_quote_indicator_yn,'N')
   FROM OKL_TRX_QUOTES_B
   WHERE id = p_quote_id;