APPS.OKL_RECEIPTS_PVT SQL Statements

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

Line: 33

SELECT cr.amount,
       cr.currency_code,
       cr.receipt_number,
       cr.receipt_date,
       crh.gl_date
FROM ar_Cash_receipts_all cr, ar_cash_receipt_history_all crh
where cr.cash_Receipt_id = cp_cash_receipt_id
  and cr.cash_receipt_id = crh.cash_receipt_id
  and crh.current_record_flag = 'Y';

Line: 59

     CURSOR c_get_gl_date(cp_date IN DATE) IS SELECT * from (
     SELECT end_date gl_date, 1 Counter
     FROM gl_period_statuses
     WHERE application_id = 222
     AND ledger_id = okl_accounting_util.get_set_of_books_id
     AND closing_status IN ('F','O')
     AND trunc(cp_date) between start_date and end_date
     AND adjustment_period_flag = 'N'
     UNION
     SELECT MAX(end_date) gl_date, 2 Counter
     FROM gl_period_statuses
     WHERE application_id = 222
     AND ledger_id = okl_accounting_util.get_set_of_books_id
     AND closing_status IN ('F','O')
     AND end_date <= trunc(cp_date)
     AND adjustment_period_flag = 'N'
     UNION
     SELECT MIN(start_date) gl_date, 3 Counter
     FROM gl_period_statuses
     WHERE application_id = 222
     AND ledger_id = okl_accounting_util.get_set_of_books_id
     AND closing_status IN ('F','O')
     AND start_date >= trunc(cp_date)
     AND adjustment_period_flag = 'N'
     )
     where gl_date is not null
     order by counter;

Line: 169

        SELECT  '1'
        FROM    AR_CASH_RECEIPTS_ALL
        WHERE   cash_receipt_id = l_cash_rcpt_id;

Line: 544

        select receipt_date
        from ar_cash_receipts_all
        where cash_receipt_id=p_cash_rcpt_id;

Line: 549

        select b.INVOICE_CURRENCY_CODE
        from ra_customer_trx_lines_all a,
            ra_customer_trx_all b
        where a.customer_trx_line_id=p_customer_trx_line_id
        and a.customer_trx_id = b.customer_trx_id;

Line: 556

        select currency_code
        from ar_cash_receipts_all
        where cash_receipt_id=p_cash_rcpt_id;

Line: 563

        SELECT Nvl(SUM(app_Line.Amount_Applied),0)
        FROM   ar_Receivable_Applications_All app_Line
        WHERE  app_Line.Cash_Receipt_Id = p_cash_receipt_id
        AND app_Line.Status = 'APP'
        AND app_Line.Application_Type = 'CASH';

Line: 570

        SELECT Nvl(SUM(onAcc_Line.Amount_Applied),0)
        FROM   ar_Receivable_Applications_All onAcc_Line
        WHERE  onAcc_Line.Cash_Receipt_Id = p_cash_receipt_id
        AND onAcc_Line.Status = 'ONACC'
        AND onAcc_Line.Application_Type = 'CASH';

Line: 577

        SELECT Nvl(SUM(rcpt_Line.Amount),0)
        FROM   ar_Cash_Receipts_All rcpt_Line
        WHERE  rcpt_Line.Cash_Receipt_Id = p_cash_receipt_id;

Line: 582

        SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL),0)
        FROM   RA_CUSTOMER_TRX_LINES_ALL
        WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
        AND    LINE_TYPE = 'FREIGHT';

Line: 588

        SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL),0)
        FROM   RA_CUSTOMER_TRX_LINES_ALL
        WHERE  CUSTOMER_TRX_ID = p_customer_trx_id
        AND    LINE_TYPE = 'CHARGES';

Line: 1050

  SELECT   NVL(RECEIVABLE_APPLICATION_ID,0)
  FROM     AR_RECEIVABLE_APPLICATIONS_ALL
  WHERE    APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
  AND      CASH_RECEIPT_ID = cp_cash_receipt_id
  AND      STATUS = 'APP'
  AND      APPLICATION_TYPE ='CASH'
  AND      DISPLAY ='Y'
  ORDER BY CREATION_DATE desc;

Line: 1062

  SELECT   NVL(SUM(AMOUNT_APPLIED),0)
  FROM     AR_RECEIVABLE_APPLICATIONS_ALL
  WHERE    STATUS = 'APP'
  AND      CASH_RECEIPT_ID = cp_csh_rcpt_id;

Line: 1198

        select receipt_date
        from ar_cash_receipts_all
        where cash_receipt_id=p_cash_rcpt_id;

Line: 1203

        select a.due_date
        from ar_payment_schedules_all a
        where a.customer_trx_id=p_customer_trx_id;

Line: 1211

        SELECT   NVL(RECEIVABLE_APPLICATION_ID,0)
        FROM     AR_RECEIVABLE_APPLICATIONS_ALL
        WHERE    CASH_RECEIPT_ID = p_cash_rcpt_id
        AND      STATUS = 'ACC'
        AND      APPLICATION_TYPE ='CASH'
        AND      DISPLAY ='Y'
        ORDER BY CREATION_DATE desc ;

Line: 1221

        SELECT TRANS_TO_RECEIPT_RATE
        FROM   AR_RECEIVABLE_APPLICATIONS_ALL
        WHERE  CASH_RECEIPT_ID = p_cash_rcpt_id
        AND    APPLIED_CUSTOMER_TRX_ID = p_customer_trx_id
        AND    NVL(DISPLAY,'N') = 'Y';

Line: 1228

        SELECT CUSTOMER_TRX_ID
        FROM   RA_CUSTOMER_TRX_LINES_ALL
        WHERE  CUSTOMER_TRX_LINE_ID = p_line_id;

Line: 1239

  select CUSTOMER_TRX_ID, error_message
    from ar_llca_trx_errors_gt;

Line: 1481

  PROCEDURE handle_onacc_update( p_api_version       IN  NUMBER
                                ,p_init_msg_list     IN  VARCHAR2 DEFAULT OkL_Api.G_FALSE
                                ,x_return_status     OUT NOCOPY VARCHAR2
                                ,x_msg_count         OUT NOCOPY NUMBER
                                ,x_msg_data          OUT NOCOPY VARCHAR2
                                ,p_cash_receipt_id   IN  AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE
                                ,p_org_id            IN  NUMBER
                                ,p_original_onacc_amount IN NUMBER
                                ,p_apply_onacc_amount IN NUMBER
                                ,p_receipt_date IN DATE
                                ,p_gl_date IN DATE
                         )   IS

---------------------------
-- DECLARE Local Variables
---------------------------

  l_api_version                 NUMBER := 1.0;

Line: 1503

  l_api_name                    CONSTANT VARCHAR2(30) := 'handle_onacc_update';

Line: 1521

        SELECT   NVL(RECEIVABLE_APPLICATION_ID,0)
        FROM     AR_RECEIVABLE_APPLICATIONS_ALL
        WHERE    CASH_RECEIPT_ID = p_cash_rcpt_id
        AND      STATUS = 'ACC'
        AND      APPLICATION_TYPE ='CASH'
        AND      DISPLAY ='Y'
        ORDER BY CREATION_DATE desc ;

Line: 1613

    log_debug('okl_receipts_pvt.handle_onacc_update end');

Line: 1644

  END handle_onacc_update;

Line: 1671

   l_select_yn                  VARCHAR2(1);

Line: 1714

   SELECT   NVL(RECEIVABLE_APPLICATION_ID,0)
   FROM     AR_RECEIVABLE_APPLICATIONS_ALL
   WHERE    CASH_RECEIPT_ID = cp_cash_rcpt_id
   AND      STATUS = 'ACC'
   AND      APPLICATION_TYPE ='CASH'
   AND      DISPLAY ='Y'
   ORDER BY CREATION_DATE desc ;

Line: 1725

   SELECT  contract_number
   FROM    OKC_K_HEADERS_ALL_B
   WHERE   id = cp_contract_id
   AND     org_id = cp_org_id;

Line: 1733

   SELECT  consolidated_invoice_number
   FROM    okl_cnsld_ar_hdrs_all_b
   WHERE   id = cp_con_inv_id
   AND     org_id = cp_org_id;

Line: 1741

   SELECT  trx_number
   FROM    ra_customer_trx_all
   WHERE   customer_trx_id = cp_ar_inv_id
   AND     org_id = cp_org_id;

Line: 1747

   SELECT  CUSTOMER_TRX_LINE_ID
   FROM    RA_CUSTOMER_TRX_LINES_ALL
   WHERE   CUSTOMER_TRX_ID = cp_ar_inv_id
   AND     ORG_ID = cp_org_id
   AND     LINE_TYPE = 'LINE'
   AND     ROWNUM = 1;

Line: 1755

   SELECT  status
   FROM    ar_Cash_receipts_all
   WHERE   cash_receipt_id = cp_cash_receipt_id;

Line: 1760

   SELECT a.invoice_currency_code
   FROM ra_customer_trx_all a
   WHERE a.customer_trx_id = p_customer_trx_id;

Line: 1843

             Ar_receipt_update_api_pub.update_receipt_unid_to_unapp(
                                p_api_version                  => l_api_version
                               ,p_init_msg_list                => l_init_msg_list
                               ,x_return_status                => l_return_status
                               ,x_msg_count                    => l_msg_count
                               ,x_msg_data                     => l_msg_data
                               ,p_cash_receipt_id              => l_rcpt_rec.cash_receipt_id
                               ,p_pay_from_customer            => l_rcpt_rec.customer_id
                               ,x_status                       => x_status
                               ,p_customer_bank_account_id     => l_rcpt_rec.customer_bank_account_id
                               );

Line: 1871

         l_select_yn:='N';

Line: 2104

                  l_select_yn:='Y';

Line: 2109

              IF l_select_yn='Y' THEN
                -- header record already present ,get the lines records in table and add new line
                 l_inv_lns_tbl:=l_final_appl_tbl(l_header_count).inv_lines_tbl;

Line: 2176

                  l_select_yn := 'Y';

Line: 2180

               IF l_select_yn = 'N' THEN
                l_header_count := l_final_appl_tbl.LAST + 1;

Line: 2243

                   l_inv_lns_tbl.delete;

Line: 2325

                   l_inv_lns_tbl.delete;

Line: 2419

log_debug('Before handle_onacc_update l_original_onacc_amount : '||l_original_onacc_amount);

Line: 2420

log_debug('Before handle_onacc_update l_apply_onacc_amount : '||l_apply_onacc_amount);

Line: 2433

        handle_onacc_update(
                                p_api_version    => l_api_version
                                ,p_init_msg_list    => l_init_msg_list
                                ,x_return_status     => l_return_status
                                ,x_msg_count         => l_msg_count
                                ,x_msg_data          => l_msg_data
                                ,p_cash_receipt_id   => l_cash_receipt_id
                                ,p_org_id            => l_org_id
                                ,p_original_onacc_amount => l_original_onacc_amount
                                ,p_apply_onacc_amount =>  l_apply_onacc_amount
                                ,p_receipt_date => l_rcpt_rec.receipt_date
                                ,p_gl_date => l_rcpt_rec.gl_date);

Line: 2508

   SELECT  cross_currency_rate_type
   FROM AR_SYSTEM_PARAMETERS_ALL
   WHERE org_id=p_org_id;