APPS.ARP_PROCESS_WRITEOFF SQL Statements

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

Line: 49

      select org_id
      into l_org_id
      from fnd_concurrent_requests
      where request_id = l_request_id;

Line: 318

      SELECT acr.cash_receipt_id cash_receipt_id,
             acr.receipt_number receipt_number,
             acr.amount,
             NVL(acr.exchange_rate,1) exchange_rate,
             SUM(DECODE(app.status,'UNAPP',NVL(app.amount_applied_from,app.amount_applied),0)) unapplied_amount,
             SUM(DECODE(app.status,'ACTIVITY',DECODE(applied_payment_schedule_id,-3,
                 DECODE(app.created_by,c_user_id,
                 NVL(app.amount_applied_from,app.amount_applied),0),0),0)) written_off_amount,
		crh.batch_id batch_id
      FROM   ar_payment_schedules ps,
             ar_cash_receipts acr,
	     ar_cash_receipt_history crh,
             ar_receivable_applications app
      WHERE  ps.invoice_currency_code   =   c_receipt_currency_code
      AND    acr.cash_receipt_id =   ps.cash_receipt_id
      AND    acr.cash_receipt_id =   crh.cash_receipt_id
      AND    crh.current_record_flag = 'Y'
      AND    acr.cash_receipt_id =   app.cash_receipt_id
      AND    ps.status           =   'OP'
      AND    ps.class            =   'PMT'
      AND    ps.trx_date BETWEEN NVL(c_receipt_date_from,ps.trx_date)
                              AND     NVL(c_receipt_date_to,ps.trx_date)
      AND    ps.gl_date       BETWEEN NVL(c_receipt_gl_date_from,ps.gl_date)
                              AND NVL(c_receipt_gl_date_to,ps.gl_date)
      AND    ps.trx_date <= c_apply_date
      AND    ps.gl_date       <= c_gl_date
      AND    acr.receipt_method_id = NVL(c_receipt_method_id,acr.receipt_method_id)
      AND    acr.pay_from_customer = NVL(c_customer_id,acr.pay_from_customer)
      AND    acr.receipt_number    = NVL(c_receipt_number,acr.receipt_number)
      group  by acr.cash_receipt_id,acr.receipt_number,acr.amount,
	acr.exchange_rate,crh.batch_id;

Line: 394

          SELECT NVL(amount_from,0),
                 NVL(amount_to,0)
          INTO   l_amount_from,
                 l_amount_to
          FROM   ar_approval_user_limits
          WHERE  currency_code = l_receipt_currency_code
          AND    user_id = arp_global.user_id
          AND    document_type ='WRTOFF';

Line: 615

	arp_rw_batches_check_pkg.update_batch_status(l_batch_id);

Line: 703

    SELECT SUM(DECODE(ra.status,'UNAPP',NVL(ra.amount_applied, 0),0)) unapplied_amount,
	   SUM(decode(ra.status,'ACTIVITY',decode(ra.applied_payment_schedule_id,-3,
    decode(ra.created_by,p_user_id,NVL(ra.amount_applied,0),0),0),0)) written_off_amount
    INTO   l_unapp_amount_balance,l_written_off_amount
    FROM   ar_receivable_applications ra
    WHERE  ra.cash_receipt_id = p_cash_receipt_id
    AND    ra.status          in ('UNAPP','ACTIVITY');

Line: 714

    /*SELECT NVL(SUM(NVL(ra.amount_applied, 0)),0) written_off_amount
    INTO   l_written_off_amount
    FROM   ar_receivable_applications ra
    WHERE  ra.cash_receipt_id = p_cash_receipt_id
    AND    ra.status = 'ACTIVITY'
    AND    ra.applied_payment_schedule_id + 0 = -3
    AND    ra.created_by = p_user_id;*/

Line: 727

          SELECT NVL(amount_from,0),
                 NVL(amount_to,0)
          INTO   l_amount_from,
                 l_amount_to
          FROM   ar_approval_user_limits
          WHERE  currency_code = p_currency_code
          AND    user_id = p_user_id
          AND    document_type ='WRTOFF';

Line: 756

       SELECT nvl(exchange_rate,1)
       INTO   l_exchange_rate
       FROM   ar_cash_receipts
       WHERE  cash_receipt_id = p_cash_receipt_id;

Line: 791

    SELECT NVL(SUM(NVL(ra.amount_applied,0)),10) l_unapplied_amount
    INTO   l_unapp_amount_balance
    FROM   ar_receivable_applications ra
    WHERE  ra.cash_receipt_id = p_cash_receipt_id
    AND    status = 'ACTIVITY'
    AND    ra.request_id = p_request_id;

Line: 850

     SELECT
        SUM(DECODE(ra.status,
                   'APP',DECODE(ra.confirmed_flag,
                        'N', 0,
                         NVL(nvl(ra.amount_applied_from, ra.amount_applied),0)),
                   'ACTIVITY',DECODE(ra.applied_payment_schedule_id,
                       -2,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
                       -3,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
                       -5,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
                                     0)
                   ,0)) applied_amount

    INTO l_applied_amount
    FROM
        ar_receivable_applications ra
    WHERE ra.cash_receipt_id = p_cash_receipt_id
    AND   ra.request_id <> p_request_id;

Line: 869

     SELECT
        SUM(DECODE(ra.status,
                   'APP',DECODE(ra.confirmed_flag,
                        'N', 0,
                        NVL(nvl(ra.amount_applied_from, ra.amount_applied),0)),
                   'ACTIVITY',DECODE(ra.applied_payment_schedule_id,
                      -2,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
                      -3,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
                      -5,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
                        0)
                   ,0)) applied_amount

    INTO l_applied_amount
    FROM
        ar_receivable_applications ra
    WHERE ra.cash_receipt_id = p_cash_receipt_id;

Line: 930

    SELECT
        SUM(DECODE(ra.status,'ACC', NVL(ra.amount_applied, 0),
         0)) on_account_amount
    INTO l_on_account_amount
    FROM
        ar_receivable_applications ra
    WHERE ra.cash_receipt_id = p_cash_receipt_id;

Line: 984

   SELECT fa_rx_flex_pkg.flex_sql(101,'GL#',chart_of_accounts_id,NULL,
          'SELECT','GL_BALANCING')
   INTO   l_balancing_segment
   FROM   gl_code_combinations
   WHERE  code_combination_id= p_code_combination_id;

Line: 990

   l_str := 'SELECT '||l_balancing_segment||' INTO :bnd_segment_value
             FROM gl_code_combinations where code_combination_id=:bind_ccid';