APPS.ARP_COLL_IND SQL Statements

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

Line: 32

      SELECT sob.currency_code,
             c.precision,
             c.minimum_accountable_unit
      INTO   curr_rec.base_currency,
             curr_rec.base_precision,
             curr_rec.base_min_acc_unit
      FROM   ar_system_parameters 	sysp,
             gl_sets_of_books 		sob,
             fnd_currencies 		c
      WHERE  sob.set_of_books_id = sysp.set_of_books_id
      AND    sob.currency_code   = c.currency_code;

Line: 46

      SELECT sob.currency_code,
             c.precision,
             c.minimum_accountable_unit
      INTO   curr_rec.base_currency,
             curr_rec.base_precision,
             curr_rec.base_min_acc_unit
      FROM   gl_sets_of_books 		sob,
             fnd_currencies 		c
      WHERE  sob.set_of_books_id = p_sob_id
      AND    sob.currency_code   = c.currency_code;

Line: 139

       SELECT  SUM(arpcurr.functional_amount(
			ps.amount_due_original,
			curr_rec.base_currency,
			nvl(ps.exchange_rate,1),
			curr_rec.base_precision,
			curr_rec.base_min_acc_unit) +
		   Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pend_date))
       INTO    tot_rec
       FROM    ar_payment_schedules 	ps
       WHERE   ps.class in ('INV', 'DM', 'CB', 'DEP' )
       AND     ps.payment_schedule_id <> -1
       AND     ps.gl_date BETWEEN temp_start AND pend_date
       AND     (pcust_id = -1 OR
                (pcust_id <> -1 AND ps.customer_id = pcust_id))
       AND     (psite_id = -1 OR
                (psite_id <> -1 AND ps.customer_site_use_id = psite_id));

Line: 162

       SELECT  SUM( gl_currency_api.convert_closest_amount_sql(
			 ps.invoice_currency_code
			,curr_rec.base_currency
			,to_date(null)
			,'User'
			,nvl(ps.exchange_rate,1)
			,ps.amount_due_original
			,to_number(null)
			)
	            + Get_Adj_For_Tot_Rec_GL(ps.payment_schedule_id,pend_date)
		  )
       INTO    tot_rec
       FROM    ar_payment_schedules_all 	ps,
               ra_customer_trx_all 		trx
       WHERE   ps.class in ('INV', 'DM', 'CB', 'DEP' )
       AND     ps.payment_schedule_id <> -1
       AND     ps.customer_trx_id = trx.customer_trx_id
       AND     trx.set_of_books_id = psob_id
       AND     ps.gl_date BETWEEN temp_start AND pend_date
       AND     (pcust_id = -1 OR
                (pcust_id <> -1 AND ps.customer_id = pcust_id))
       AND     (psite_id = -1 OR
                (psite_id <> -1 AND ps.customer_site_use_id = psite_id));

Line: 242

    SELECT sum(Get_Apps_Total(ps.payment_schedule_id,pend_date) -
           Get_Adj_Total(ps.payment_schedule_id,pend_date) +
           nvl(ps.acctd_amount_due_remaining,0))
    INTO   rem_sales
    FROM   ar_payment_schedules         ps
    WHERE  ps.gl_date between pstart_date and pend_date
    AND    ps.class in ('INV','DEP','DM','CB')
    AND    ps.gl_date_closed > pend_date
    AND    (pcust_id = -1 OR
            (pcust_id <> -1 AND ps.customer_id = pcust_id));

Line: 257

    SELECT sum(Get_Apps_Total_GL(ps.payment_schedule_id,pend_date) -
           Get_Adj_Total_GL(ps.payment_schedule_id,pend_date) +
           nvl(ps.acctd_amount_due_remaining,0))
    INTO   rem_sales
    FROM   ar_payment_schedules_all  	ps,
	   ra_customer_trx_all		trx
    WHERE  ps.gl_date between pstart_date and pend_date
    AND    ps.class in ('INV','DEP','DM','CB')
    AND    ps.customer_trx_id = trx.customer_trx_id
    AND    trx.set_of_books_id = psob_id
    AND    ps.gl_date_closed > pend_date
    AND    (pcust_id = -1 OR
            (pcust_id <> -1 AND ps.customer_id = pcust_id));

Line: 646

	SELECT 	sum( nvl(ra.acctd_amount_applied_to,0)  +
              	    nvl(ra.acctd_earned_discount_taken,0) +
             	    nvl(ra.acctd_unearned_discount_taken,0))
	INTO   	apps_tot
	FROM   	ar_receivable_applications   ra
	WHERE  	ra.applied_payment_schedule_id = pay_sched_id
	AND	ra.status = 'APP'
	AND	nvl(ra.confirmed_flag,'Y') = 'Y'
	AND     ra.gl_date   > pto_date;

Line: 677

        SELECT  sum( nvl(a.acctd_amount,0))
        INTO    adj_for_tot_rec
        FROM    ar_adjustments   a
        WHERE   a.payment_schedule_id = pay_sched_id
        AND     a.status = 'A'
        AND     a.gl_date <= pto_date;

Line: 705

        SELECT  sum( nvl(a.acctd_amount,0))
        INTO    adj_for_tot_rec_gl
        FROM    ar_adjustments_all      a
        WHERE   a.payment_schedule_id = pay_sched_id
        AND     a.status  = 'A'
        AND     a.gl_date <= pto_date;

Line: 732

        SELECT  sum( nvl(a.acctd_amount,0))
        INTO    adj_tot
        FROM    ar_adjustments   a
        WHERE   a.payment_schedule_id = pay_sched_id
	AND     a.status       = 'A'
        AND     a.gl_date       > pto_date;

Line: 761

	SELECT 	sum( nvl(ra.acctd_amount_applied_to,0)  +
             	    nvl(ra.acctd_earned_discount_taken,0) +
             	    nvl(ra.acctd_unearned_discount_taken,0))
	INTO   	apps_tot_gl
	FROM   	ar_receivable_applications_all   ra
	WHERE  	ra.applied_payment_schedule_id = pay_sched_id
	AND	ra.status = 'APP'
	AND	nvl(ra.confirmed_flag,'Y') = 'Y'
	AND     ra.gl_date   > pto_date;

Line: 792

        SELECT  sum( nvl(a.acctd_amount,0))
        INTO    adj_tot_gl
        FROM    ar_adjustments_all   	   a
        WHERE   a.payment_schedule_id = pay_sched_id
	AND     a.status   = 'A'
        AND     a.gl_date  > pto_date;

Line: 846

  SELECT sum
           (
             (
               Get_Apps_Total(ps.payment_schedule_id,pas_of_date) -
               Get_Adj_Total(ps.payment_schedule_id,pas_of_date) +
               nvl(ps.acctd_amount_due_remaining,0)
             ) *
             (pas_of_date-ps.due_date)
           )  /
           sum (
             Get_Apps_Total(ps.payment_schedule_id,pas_of_date) -
             Get_Adj_Total(ps.payment_schedule_id,pas_of_date) +
             nvl(ps.acctd_amount_due_remaining,0)
           )
    INTO   wtd_days
    FROM   ar_payment_schedules 	ps
    WHERE  ps.gl_date between pstart_date and  pas_of_date
    AND    ps.class in ('INV','DEP','DM','CB')
    AND    ps.gl_date_closed > pas_of_date
    AND    ps.due_date       < pas_of_date
    AND    ps.payment_schedule_id <> -1
    AND    (pcust_id = -1 OR
            (pcust_id <> -1 AND ps.customer_id = pcust_id));

Line: 914

   SELECT sum(
            (
            Get_Apps_Total(ps.payment_schedule_id,pas_of_date) -
            Get_Adj_Total(ps.payment_schedule_id,pas_of_date) +
            nvl(ps.acctd_amount_due_remaining,0)
            ) *
            (pas_of_date-ps.due_date)
          ) /
          sum(
            pas_of_date-ps.due_date
          )
    INTO  wtd_bal
    FROM  ar_payment_schedules         ps
    WHERE ps.gl_date between pstart_date and pas_of_date
    AND   ps.class in ('INV','DEP','DM','CB')
    AND   ps.gl_date_closed > pas_of_date
    AND   ps.due_date       < pas_of_date
    AND   ps.payment_schedule_id <> -1
    AND   (pcust_id = -1 OR
            (pcust_id <> -1 AND ps.customer_id = pcust_id));

Line: 984

 SELECT
   sum(v_above_amount)
 INTO
   above_amount
 FROM (
    SELECT
      SUM(
        arpcurr.functional_amount(
          ps.amount_due_original,
          curr_rec.base_currency,
          nvl(ps.exchange_rate,1),
          curr_rec.base_precision,
          curr_rec.base_min_acc_unit
        ) +
	Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
      ) v_above_amount
    FROM   ar_payment_schedules ps
    WHERE  ps.gl_date BETWEEN pstart_date AND pas_of_date
    AND    ps.payment_schedule_id <> -1
    AND    ps.class IN ('INV', 'DM', 'CB', 'DEP' )
    AND    (pcust_id = -1 OR
             (pcust_id <> -1 AND ps.customer_id = pcust_id))
    GROUP BY ps.customer_trx_id
    HAVING SUM(
             arpcurr.functional_amount(
               ps.amount_due_original,
               curr_rec.base_currency,
               nvl(ps.exchange_rate,1),
               curr_rec.base_precision,
               curr_rec.base_min_acc_unit
             ) +
             Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
           ) >= psplit
 );

Line: 1072

    SELECT
      SUM(trx)
    INTO
      above_count
    FROM (
      SELECT
        1  trx
      FROM
        ar_payment_schedules ps
      WHERE
            ps.gl_date BETWEEN pstart_date AND pas_of_date
        and ps.class IN ('INV', 'DM', 'CB', 'DEP' )
    	and ps.payment_schedule_id <> -1
    	and (pcust_id = -1 OR
                (pcust_id <> -1 AND ps.customer_id = pcust_id))
	GROUP BY ps.customer_trx_id
        HAVING SUM(arpcurr.functional_amount(
                     ps.amount_due_original,
                     curr_rec.base_currency,
                     nvl(ps.exchange_rate,1),
                     curr_rec.base_precision,
                     curr_rec.base_min_acc_unit
                  ) +
                  Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
               ) >= psplit
    );

Line: 1149

  SELECT
    SUM(v_below_amount)
  INTO
    below_amount
  FROM (
    SELECT
      SUM(
        arpcurr.functional_amount(
          ps.amount_due_original,
          curr_rec.base_currency,
          nvl(ps.exchange_rate,1),
          curr_rec.base_precision,
          curr_rec.base_min_acc_unit
        ) +
	Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
      ) v_below_amount
    FROM
      ar_payment_schedules ps
    WHERE
          ps.gl_date BETWEEN pstart_date AND pas_of_date
      AND ps.class in ('INV', 'DM', 'CB', 'DEP' )
      AND ps.payment_schedule_id <> -1
      AND ( pcust_id = -1 OR
             (pcust_id <> -1 AND ps.customer_id = pcust_id))
    GROUP BY ps.customer_trx_id
    HAVING SUM(
             arpcurr.functional_amount(
               ps.amount_due_original,
               curr_rec.base_currency,
               nvl(ps.exchange_rate,1),
               curr_rec.base_precision,
               curr_rec.base_min_acc_unit
             ) +
             Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
           ) < psplit
  );

Line: 1236

 SELECT
   SUM(trx)
 INTO
   below_count
 FROM (
   SELECT
     1  trx
   FROM
     ar_payment_schedules ps
   WHERE
         ps.gl_date BETWEEN pstart_date AND pas_of_date
     and ps.class IN ('INV', 'DM', 'CB', 'DEP' )
     and ps.payment_schedule_id <> -1
     and (pcust_id = -1 OR
            (pcust_id <> -1 AND ps.customer_id = pcust_id))
   GROUP BY ps.customer_trx_id
   HAVING SUM(
            arpcurr.functional_amount(
              ps.amount_due_original,
              curr_rec.base_currency,
              nvl(ps.exchange_rate,1),
              curr_rec.base_precision,
              curr_rec.base_min_acc_unit
            ) +
            Get_Adj_For_Tot_Rec(ps.payment_schedule_id,pas_of_date)
          ) < psplit
  );