APPS.OKL_CURE_CALC_PVT SQL Statements

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

Line: 14

select  RULE_INFORMATION3
from okc_rules_b rul,
      okl_k_headers khr
where rul.dnz_chr_id =khr.khr_id
and khr.id =p_contract_id
and  RULE_INFORMATION_CATEGORY ='CORPUR';

Line: 68

  SELECT kle.id kle_id, kle.name asset_number
  FROM okc_k_lines_v kle, okc_k_headers_v khr,
       OKC_LINE_STYLES_V LSE
  WHERE kle.chr_id = khr.id
  AND kle.lse_id = LSE.id
  AND lse.lty_code = 'FREE_FORM1' --This is the TOP LINE for Financial Assets
  AND khr.sts_code = kle.sts_code
  AND khr.id = p_contract_id;

Line: 115

 Procedure Update_cure_amounts(
                p_contract_id     IN NUMBER
               ,x_return_status  OUT NOCOPY VARCHAR2
               ,x_msg_count      OUT NOCOPY NUMBER
               ,x_msg_data       OUT NOCOPY VARCHAR2 ) IS

Cursor c_get_cure_amts (p_contract_id IN NUMBER) IS
Select cure_amount_id,object_version_number,negotiated_amount
from okl_cure_amounts
where chr_id =p_contract_id
and SHOW_ON_REQUEST ='Y';

Line: 146

 select sum(ara.amount_applied)
 from ar_payment_schedules ps1,
      okl_cnsld_ar_strms_b st1
     ,ar_receivable_applications ara
     ,okl_xtl_sell_invs_v  xls
     ,okl_txl_ar_inv_lns_v til
     ,okl_trx_ar_invoices_v tai
where st1.receivables_invoice_id = ps1.customer_trx_id
     and ara.applied_payment_schedule_id = ps1.payment_schedule_id
     and st1.id =xls.lsm_id
     and tai.id = til.tai_id
     and til.id = xls.til_id
     and tai.cpy_id =p_cure_amount_id
     and st1.khr_id =tai.khr_id;*/

Line: 162

 select sum(ara.amount_applied)
 from ar_payment_schedules ps1,
      okl_bpd_tld_ar_lines_v st1
     ,ar_receivable_applications ara
     --,okl_xtl_sell_invs_v  xls
     ,okl_txl_ar_inv_lns_v til
     ,okl_trx_ar_invoices_v tai
where st1.customer_trx_id = ps1.customer_trx_id
     and ara.applied_payment_schedule_id = ps1.payment_schedule_id
     --and st1.id =xls.lsm_id
     and tai.id = til.tai_id
     --and til.id = xls.til_id
     and tai.cpy_id =p_cure_amount_id
     and st1.khr_id =tai.khr_id
     and st1.til_id_details = til.id
     and til.tai_id = tai.id;

Line: 186

         SAVEPOINT UPDATE_CURE_AMOUNTS;

Line: 189

         write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Calling Cure Amount Update Api');

Line: 191

         okl_debug_pub.logmessage('Update_cure_amounts : START ');

Line: 207

               okl_debug_pub.logmessage('Update_cure_amounts : i.cure_amount_id '|| i.cure_amount_id);

Line: 208

               okl_debug_pub.logmessage('Update_cure_amounts : l_camv_tbl(next_row).received_amount '|| l_camv_tbl(next_row).received_amount);

Line: 220

                  'no of records to be updated in Cure amounts '||
                   l_camv_tbl.COUNT);

Line: 223

           OKL_cure_amounts_pub.update_cure_amounts
                         (  p_api_version    => 1
                           ,p_init_msg_list  => 'T'
                           ,x_return_status  => l_return_status
                           ,x_msg_count      => l_msg_count
                           ,x_msg_data       => l_msg_data
                           ,p_camv_tbl       => l_camv_tbl
                           ,x_camv_tbl       => x_camv_tbl
                         );

Line: 233

         okl_debug_pub.logmessage('Update_cure_amounts : OKL_cure_amounts_pub.update_cure_amounts : '||l_return_status);

Line: 246

      okl_debug_pub.logmessage('Update_cure_amounts : END ');

Line: 251

      ROLLBACK TO UPDATE_CURE_AMOUNTS;

Line: 259

      ROLLBACK TO UPDATE_CURE_AMOUNTS;

Line: 267

      ROLLBACK TO UPDATE_CURE_AMOUNTS;

Line: 271

      Fnd_Msg_Pub.ADD_EXC_MSG('OKL_CURE_CALC_PVT','UPDATE_CURE_AMOUNTS');

Line: 277

End update_cure_amounts;

Line: 322

    SELECT  party.id
      FROM okl_am_k_party_roles_uv party,
           okl_k_headers khr
      WHERE party.dnz_chr_id =khr.khr_id
      and khr.id=p_contract_id
      AND party.rle_code = 'OKL_VENDOR';

Line: 335

   select CPLB.ID id
   FROM OKC_K_PARTY_ROLES_B CPLB
   where CPLB.DNZ_CHR_ID=p_contract_id
   and CPLB.RLE_CODE= 'OKL_VENDOR';

Line: 497

 select refund_amount_due
 from okl_cure_refunds_dtls_uv
 where contract_id =p_contract_id;

Line: 509

    SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
    FROM   okl_cnsld_ar_strms_b ocas
           ,ar_payment_schedules aps
    WHERE  ocas.khr_id = p_contract_id
    AND    ocas.receivables_invoice_id = aps.customer_trx_id
    AND    aps.class ='INV'
    AND    (aps.due_date + p_grace_days) < sysdate
    AND    NVL(aps.amount_due_remaining, 0) > 0
    and not exists
          (select xls1.lsm_id from
              okl_xtl_sell_invs_v xls1
              ,okl_txl_ar_inv_lns_v til1
              ,okl_trx_ar_invoices_v tai1 where
              tai1.id = til1.tai_id and
              til1.id = xls1.til_id and
              tai1.cpy_id IS NOT NULL and
              xls1.lsm_id =ocas.id
           ); */

Line: 530

    SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
    FROM   okl_bpd_tld_ar_lines_v ocas
           ,ar_payment_schedules aps
    WHERE  ocas.khr_id = p_contract_id
    AND    ocas.customer_trx_id = aps.customer_trx_id
    AND    aps.class ='INV'
    AND    (aps.due_date + p_grace_days) < sysdate
    AND    NVL(aps.amount_due_remaining, 0) > 0
    and not exists
          (select tld.id from
              --okl_xtl_sell_invs_v xls1
               okl_txd_ar_ln_dtls_b tld
              ,okl_txl_ar_inv_lns_v til1
              ,okl_trx_ar_invoices_v tai1 where
              tai1.id = til1.tai_id and
              --til1.id = xls1.til_id and
              til1.id = tld.til_id_details and
              tai1.cpy_id IS NOT NULL and
              --xls1.lsm_id =ocas.id
              tld.id =ocas.tld_id
           );

Line: 629

  select refund_amount_due
  from okl_cure_refunds_dtls_uv
  where contract_id =p_contract_id;

Line: 639

    SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
    FROM   okl_cnsld_ar_strms_b ocas
           ,ar_payment_schedules aps
    WHERE  ocas.khr_id = p_contract_id
    AND    ocas.receivables_invoice_id = aps.customer_trx_id
    AND    aps.class ='INV'
    AND    (aps.due_date + p_grace_days) < sysdate
    AND    NVL(aps.amount_due_remaining, 0) > 0
    and not exists
          (select xls1.lsm_id from
              okl_xtl_sell_invs_v xls1
              ,okl_txl_ar_inv_lns_v til1
              ,okl_trx_ar_invoices_v tai1 where
              tai1.id = til1.tai_id and
              til1.id = xls1.til_id and
              tai1.cpy_id IS NOT NULL and
              xls1.lsm_id =ocas.id
           ); */

Line: 660

    SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
    FROM   okl_bpd_tld_ar_lines_v ocas
           ,ar_payment_schedules aps
    WHERE  ocas.khr_id = p_contract_id
    AND    ocas.customer_trx_id = aps.customer_trx_id
    AND    aps.class ='INV'
    AND    (aps.due_date + p_grace_days) < sysdate
    AND    NVL(aps.amount_due_remaining, 0) > 0
    and not exists
          (select tld.id from
              okl_txd_ar_ln_dtls_b tld
              ,okl_txl_ar_inv_lns_v til1
              ,okl_trx_ar_invoices_v tai1 where
              tai1.id = til1.tai_id and
              til1.id = tld.til_id_details and
              tai1.cpy_id IS NOT NULL and
              tld.id =ocas.tld_id
           );

Line: 825

  select nvl(sum(negotiated_amount),0)+ nvl(sum(short_fund_amount),0)
  from  okl_cure_amounts
  where chr_id =p_contract_id
  and   status ='CURESINPROGRESS';

Line: 832

  SELECT org_id
  FROM okc_k_headers_b
  WHERE id = p_contract_id;

Line: 857

     SELECT DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, Fnd_Global.CONC_REQUEST_ID),
          DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL, Fnd_Global.PROG_APPL_ID),
          DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL, Fnd_Global.CONC_PROGRAM_ID),
          DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
    INTO l_camv_rec.request_id,
         l_camv_rec.program_application_id,
         l_camv_rec.program_id,
         l_camv_rec.program_update_date
    FROM DUAL;

Line: 951

  l_camv_rec.selected_on_request   := 'Y';

Line: 969

           Update_cure_amounts(
                                 p_contract_id    =>p_contract_id,
                                 x_return_status  =>l_return_status,
                                 x_msg_count      =>l_msg_count,
                                 x_msg_data       =>l_msg_data );

Line: 975

           okl_debug_pub.logmessage('CALC_CURE_REPURCHASE : Update_cure_amounts : '||l_return_status);

Line: 989

            END IF; --  update_cure_amounts

Line: 992

     write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Before Inserting Cure Amounts');

Line: 1003

       OKL_cure_amounts_pub.insert_cure_amounts
                         (
                            p_api_version    => p_api_version
                           ,p_init_msg_list  => p_init_msg_list
                           ,x_return_status  => l_return_status
                           ,x_msg_count      => l_msg_count
                           ,x_msg_data       => l_msg_data
                           ,p_camv_rec       => l_camv_rec
                           ,x_camv_rec       => x_camv_rec
                         );

Line: 1014

       okl_debug_pub.logmessage('CALC_CURE_REPURCHASE : OKL_cure_amounts_pub.insert_cure_amounts : '||l_return_status);

Line: 1152

    SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
    FROM   okl_cnsld_ar_strms_b ocas
          ,ar_payment_schedules aps
    WHERE  ocas.khr_id = p_contract_id
    AND    ocas.receivables_invoice_id = aps.customer_trx_id
    AND    aps.class ='INV'
    AND    (aps.due_date + p_grace_days) < sysdate
    AND    NVL(aps.amount_due_remaining, 0) > 0
    AND  not exists
          (select xls1.lsm_id from
              okl_xtl_sell_invs_v xls1
              ,okl_txl_ar_inv_lns_v til1
              ,okl_trx_ar_invoices_v tai1 where
              tai1.id = til1.tai_id and
              til1.id = xls1.til_id and
              tai1.cpy_id IS NOT NULL and
              xls1.lsm_id =ocas.id);*/

Line: 1173

    SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
    FROM   okl_bpd_tld_ar_lines_v ocas
          ,ar_payment_schedules aps
    WHERE  ocas.khr_id = p_contract_id
    AND    ocas.customer_trx_id = aps.customer_trx_id
    AND    aps.class ='INV'
    AND    (aps.due_date + p_grace_days) < sysdate
    AND    NVL(aps.amount_due_remaining, 0) > 0
    AND  not exists
          --(select xls1.lsm_id from
          (select tld.id from
              --okl_xtl_sell_invs_v xls1
              okl_txd_ar_ln_dtls_b tld
              ,okl_txl_ar_inv_lns_v til1
              ,okl_trx_ar_invoices_v tai1 where
              tai1.id = til1.tai_id and
              --til1.id = xls1.til_id and
              til1.id = tld.til_id_details and
              tai1.cpy_id IS NOT NULL and
              --xls1.lsm_id =ocas.id);

Line: 1284

    SELECT   prog.id program_id
            ,prog.contract_number program_number
            ,lease.id contract_id
            ,lease.contract_number contract_number
            ,rgp.rgd_code
    FROM    okc_k_headers_b prog,
            okc_k_headers_b lease,
            okl_k_headers   khr,
            okc_rule_groups_b rgp
    WHERE   khr.id = lease.id
    AND     khr.khr_id = prog.id
    AND     prog.scs_code = 'PROGRAM'
    AND     lease.scs_code in ('LEASE','LOAN')
    AND     rgp.rgd_code = 'COCURP'
    AND     rgp.dnz_chr_id = prog.id
    AND     lease.contract_number =nvl(p_contract_number,lease.contract_number) ;

Line: 1312

 select count( ps.payment_schedule_id)
 from ar_payment_schedules ps
     ,okl_cnsld_ar_strms_b stream
     ,okl_xtl_sell_invs_v  xls
     ,okl_txl_ar_inv_lns_v til
     ,okl_trx_ar_invoices_v tai
 where ps.class ='INV'
      and ps.amount_due_remaining = 0
      and stream.receivables_invoice_id = ps.customer_trx_id
      and stream.id =xls.lsm_id
      and tai.id    = til.tai_id
      and til.id    = xls.til_id
      and tai.cpy_id IS NOT NULL
      and tai.khr_id =p_contract_id;*/

Line: 1329

 select count( ps.payment_schedule_id)
 from   ar_payment_schedules ps
        ,okl_bpd_tld_ar_lines_v stream
        --,okl_xtl_sell_invs_v  xls
        ,okl_txd_ar_ln_dtls_b  tld
        ,okl_txl_ar_inv_lns_v til
        ,okl_trx_ar_invoices_v tai
 where  ps.class ='INV'
 and    ps.amount_due_remaining = 0
 and    stream.customer_trx_id = ps.customer_trx_id
 --and stream.id =xls.lsm_id
 and    stream.tld_id =tld.id
 and    tai.id    = til.tai_id
 --and til.id    = xls.til_id
 and    til.id    = tld.til_id_details
 and    tai.cpy_id IS NOT NULL
 and    tai.khr_id =p_contract_id;

Line: 1358

        SELECT  min(aps.due_date)
        FROM    okl_cnsld_ar_strms_b ocas
               ,ar_payment_schedules aps
               ,okc_k_headers_b chr
               ,OKL_STRM_TYPE_TL SM
        WHERE
               ocas.khr_id = p_contract_id
          AND  ocas.receivables_invoice_id = aps.customer_trx_id
          AND  aps.class = 'INV'
          AND  aps.due_date < sysdate
          AND  NVL(aps.amount_due_remaining, 0) > 0
          AND  ocas.khr_id=chr.id
          AND sm.ID = ocas.STY_ID and sm.name <> 'CURE'    ;*/

Line: 1374

  SELECT  min(aps.due_date)
  FROM    okl_bpd_tld_ar_lines_v ocas
          ,ar_payment_schedules aps
          ,okc_k_headers_b chr
          ,OKL_STRM_TYPE_TL SM
  WHERE   ocas.khr_id = p_contract_id
  AND     ocas.customer_trx_id = aps.customer_trx_id
  AND     aps.class = 'INV'
  AND     aps.due_date < sysdate
  AND     NVL(aps.amount_due_remaining, 0) > 0
  AND     ocas.khr_id=chr.id
  AND     sm.ID = ocas.STY_ID and sm.name <> 'CURE'    ;

Line: 1507

             Update_cure_amounts(
                                 p_contract_id    =>i.contract_id,
                                 x_return_status  =>l_return_status,
                                 x_msg_count      =>l_msg_count,
                                 x_msg_data       =>l_msg_data );

Line: 1525

            END IF; --  update_cure_amounts