APPS.ARP_STAGED_DUNNING SQL Statements

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

Line: 98

	'SELECT	  ps.payment_schedule_id'				||
		',ps.invoice_currency_code'				||
		',ps.amount_due_remaining'				||
		',fnd_date.canonical_to_date(:b_dun_date) - ps.due_date ' 	||
		',ps.staged_dunning_level'	         		||
		',ps.dunning_level_override_date'          		||
	  ' FROM  ar_payment_schedules ps';

Line: 107

	'SELECT	  ps.invoice_currency_code, '                           ||
                ' sum(ps.amount_due_remaining) '                        ||
        ' FROM   ar_payment_schedules ps';

Line: 228

		'SELECT ''Unapproved Adjustments '''			||
		  ' FROM  ar_adjustments adj'				||
		 ' WHERE  adj.payment_schedule_id = ps.payment_schedule_id'||
		   ' AND  adj.status NOT IN (''A'',''R'',''U''))';

Line: 235

		'SELECT ''Unapproved Adjustments '''			||
		  ' FROM  ar_adjustments adj'				||
		 ' WHERE  adj.payment_schedule_id = ps.payment_schedule_id'||
		   ' AND  adj.status NOT IN (''A'',''R'',''U''))';

Line: 415

	-- is in the range selected by user( parameter )

	if dun_flag = TRUE
	AND
	 st_dunning_level >= parameter.dunning_level_from
	   AND
	   st_dunning_level <= parameter.dunning_level_to then

                -- save distinct dunning level into array
		change_flag := FALSE;

Line: 507

	SELECT	dlsl.dunning_letter_id
	  INTO  id
	  FROM  ar_dunning_letter_set_lines dlsl
	 WHERE  dlsl.dunning_letter_set_id = site.letter_set_id
	   AND  help_level BETWEEN dlsl.range_of_dunning_level_from
				 AND dlsl.range_of_dunning_level_to;

Line: 589

	SELECT	 nvl(site_cpa.min_dunning_amount,
		    nvl(cust_cpa.min_dunning_amount, 0 ))
		,nvl(site_cpa.min_dunning_invoice_amount,
		    nvl(cust_cpa.min_dunning_invoice_amount, 0 ))
	  INTO 	 min_dun_amount
		,min_dun_inv_amount
	  FROM	 hz_customer_profiles		cust_cp
		,hz_cust_profile_amts		cust_cpa
		,hz_customer_profiles		site_cp
		,hz_cust_profile_amts		site_cpa
	 where   CUST_CP.CUST_ACCOUNT_ID	= site.customer_id
	   AND	 cust_cp.site_use_id IS NULL
	   AND 	 cust_cpa.cust_account_profile_id(+)= cust_cp.cust_account_profile_id
	   AND 	 cust_cpa.currency_code(+)	= curr_code
	   AND	 site_cp.cust_account_id(+)	= cust_cp.cust_account_id
	   AND 	 site_cp.site_use_id(+)		= site.site_use_id
	   AND	 site_cpa.cust_account_profile_id(+)= site_cp.cust_account_profile_id
	   AND   site_cpa.currency_code(+)	= curr_code;

Line: 612

	SELECT	 nvl(cust_cpa.min_dunning_amount, 0)
		,nvl(cust_cpa.min_dunning_invoice_amount, 0)
	  INTO 	 min_dun_amount
		,min_dun_inv_amount
	  FROM	 hz_customer_profiles		cust_cp
		,hz_cust_profile_amts		cust_cpa
	 WHERE   cust_cp.cust_account_id	= site.customer_id
	   AND	 cust_cp.site_use_id IS NULL
	   AND 	 cust_cpa.cust_account_profile_id(+)
                               = cust_cp.cust_account_profile_id
	   AND 	 cust_cpa.currency_code(+)	= curr_code;

Line: 624

        SELECT  NVL(min_dunning_amount, 0) ,
                NVL(min_dunning_invoice_amount, 0)
          INTO  min_dun_amount,
                min_dun_inv_amount
          FROM  hz_cust_profile_amts
         WHERE  CUST_ACCOUNT_PROFILE_ID =
                        (SELECT cust_account_profile_id
                           FROM hz_customer_profiles
                          WHERE site_use_id = arpt_sql_func_util.get_bill_id(site.site_use_id))
           AND     currency_code = curr_code
           AND     CUST_ACCOUNT_ID = site.customer_id;

Line: 656

 |    possibility to update the dunning level. Thatfore , dunning level can   |
 |    not be increased by 1 with every printing of a dunning letter	      |
 |									      |
 |									      |
 | RETURNS                                                                    |
 |    TRUE if open payment should be dunned, else return FALSE                |
 |									      |
 | MODIFIES								      |
 |    staged_dunning_level						      |
 |                                                                            |
 |                                                                            |
 | KNOWN BUGS                                                                 |
 |                                                                            |
 |                                                                            |
 | HISTORY                                                                    |
 |  7/31/95  Christine Vogel  Created                                         |
 |  8/25/96  Simon Jou        Modified for staged dunning/credit memo         |
 *----------------------------------------------------------------------------*/


FUNCTION get_new_dunning_level(ps_id	   IN NUMBER
		  ,staged_dunning_level    IN OUT NOCOPY NUMBER
		  ,current_dun_date	   IN DATE
		  ,dunning_level_override_date IN DATE
                  ,days_late IN NUMBER
                  ,o_letter_set_id IN NUMBER ) RETURN BOOLEAN AS

  last_print_date	DATE;

Line: 692

	SELECT  c.correspondence_date
	  FROM  ar_correspondence_pay_sched	cp
	       ,ar_correspondences		c
	       ,ar_dunning_letter_set_lines     dlsl
	 WHERE  cp.payment_schedule_id		= ps_id
	   AND  c.preliminary_flag		= 'N'
	   AND  cp.staged_dunning_level is NOT NULL
	   AND  dlsl.dunning_letter_set_id	= c.reference1
	   AND  dlsl.dunning_letter_id		= c.reference2
	   AND  cp.correspondence_id		= c.correspondence_id
      ORDER BY  c.correspondence_date DESC;

Line: 715

  SELECT min_days_between_dunning
  INTO   min_dunning_days
  FROM   ar_dunning_letter_set_lines
  WHERE  dunning_letter_set_id = o_letter_set_id
    AND  range_of_dunning_level_from <= (NVL(staged_dunning_level, 0)+1)
    AND  range_of_dunning_level_to   >= (NVL(staged_dunning_level, 0)+1);