eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ARP_AUTOMATIC_CLEARING_PKG SQL Statements

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

Line: 17


procedure main_select_risk
(
        statement 	IN OUT NOCOPY VARCHAR2,
        p_total_workers IN NUMBER DEFAULT 0,
        p_request_id 	IN NUMBER DEFAULT -1      );

Line: 23


procedure main_select_factored
(
        statement 	IN OUT NOCOPY VARCHAR2,
        p_total_workers IN NUMBER DEFAULT 0,
        p_request_id 	IN NUMBER DEFAULT -1     );

Line: 29


procedure main_select_remitted
(
        statement 	IN OUT NOCOPY VARCHAR2,
        p_total_workers IN NUMBER DEFAULT 0,
        p_request_id 	IN NUMBER DEFAULT -1      );

Line: 74


procedure main_select_remitted_parallel
(
	statement IN OUT NOCOPY VARCHAR2	);

Line: 78


procedure main_select_factored_parallel
(
        statement IN OUT NOCOPY VARCHAR2       );

Line: 82


procedure main_select_risk_parallel
(
        statement IN OUT NOCOPY VARCHAR2       );

Line: 173


procedure main_select_risk
(
        statement IN OUT NOCOPY VARCHAR2,
        p_total_workers IN NUMBER DEFAULT 0,
        p_request_id IN NUMBER DEFAULT -1       ) IS
--
BEGIN
--
/* 07-JUL-2000 J Rautiainen BR Implementation
 * Receipts created by the Bills Receivable transaction remittance process
 * having BR_REMIT receipt class cannot be risk eliminated */
/*5444413 Index hint added*/
statement := statement ||
            'select ' ||
            'cr.cash_receipt_id, ' ||
            ':b_clear_date, ' ||
            'greatest ( crh.gl_date, :b_gl_date ), ' ||
            ' ''AR_AUTOMATIC_CLEARING'', ' ||
            ' ''10.6'', ' ||
            'crh.cash_receipt_history_id  ';

Line: 240


     arp_standard.debug('Inside main_select_risk');

Line: 251


procedure main_select_factored
(
        statement IN OUT NOCOPY VARCHAR2,
        p_total_workers IN NUMBER DEFAULT 0,
        p_request_id IN NUMBER DEFAULT -1       ) IS
--
BEGIN
--
/* Bug 2484984 Modified the call gl_currency_api.get_rate so that
   there is an NVL for the exchange_rate_type parameter rather
   than an NVL for the function call itself . */

/* Bug 3820774 Replaced the get_rate with get_rate_sql. */
/*Bug5444413 Added hint */
statement := statement ||
            'select /*+ INDEX (CRH AR_CASH_RECEIPT_HISTORY_N6) USE_NL(crh,cr,rm,rc,rma,c,party,ba) */ ' ||
                'cr.cash_receipt_id, '||
                ':b_clear_date, ' ||
                'greatest ( crh.gl_date, :b_gl_date ), ' ||
                'nvl(:b_clear_date,crh.exchange_date), ' ||
                'nvl(:b_exchange_rate_type,cr.exchange_rate_type), ' ||
                'decode(nvl(:b_exchange_rate_type,cr.exchange_rate_type), ' ||
		'	''User'',cr.exchange_rate, ' ||
                '       gl_currency_api.get_rate_sql(:b_set_of_bks_id,cr.currency_code,:b_clear_date, ' ||
                '          nvl(:b_exchange_rate_type,cr.exchange_rate_type))), ' ||
                'cba.currency_code, ' ||
                'decode(cr.currency_code,
			cba.currency_code,crh.amount,crh.acctd_amount), ' ||
                'decode(cr.currency_code,
			cba.currency_code,crh.factor_discount_amount,crh.acctd_factor_discount_amount), ' ||
		' ''AR_AUTOMATIC_CLEARING'', ' ||
		' ''10.6'', ' ||
		'crh.cash_receipt_history_id, ' ||
                'crh.amount, ' ||
                'crh.factor_discount_amount, ' ||
                'cr.currency_code, ' ||
                'crh.exchange_rate ' ;

Line: 325


		'select ''debit memo reversal'' ' ||
		'from   ar_payment_schedules ps1 ' ||
		'where  ps1.reversed_cash_receipt_id = cr.cash_receipt_id ' ||
		'and    ps1.class = ''DM'') ';

Line: 330


     arp_standard.debug('Inside main_select_factored');

Line: 340


procedure main_select_remitted
(
	statement IN OUT NOCOPY VARCHAR2,
    p_total_workers IN NUMBER DEFAULT 0,
    p_request_id IN NUMBER DEFAULT -1	) IS
--
BEGIN
--
/* Bug 2484984 Modified the call gl_currency_api.get_rate so that
   there is an NVL for the exchange_rate_type parameter rather
   than an NVL for the function call itself . */
/* Bug 3820774 Replaced the get_rate with get_rate_sql. */
/*5444413 hint added here*/
statement := statement || 'select  ' ||
                'cr.cash_receipt_id, ' ||
                ':b_clear_date, ' ||
                'greatest ( crh.gl_date, :b_gl_date ), ' ||
                'nvl(:b_clear_date, crh.exchange_date), ' ||
                'nvl(:b_exchange_rate_type,cr.exchange_rate_type), ' ||
                'decode(nvl(:b_exchange_rate_type,cr.exchange_rate_type), ' ||
		'	 ''User'',cr.exchange_rate, ' ||
                '       gl_currency_api.get_rate_sql(:b_set_of_bks_id,cr.currency_code,:b_clear_date, ' ||
                '          nvl(:b_exchange_rate_type,cr.exchange_rate_type))), ' ||
                'cba.currency_code, ' ||
                'decode(cr.currency_code,
			cba.currency_code,crh.amount,crh.acctd_amount), ' ||
                'decode(cr.currency_code,
			cba.currency_code,crh.factor_discount_amount,crh.acctd_factor_discount_amount), ' ||
                ' ''AR_AUTOMATIC_CLEARING'', ' ||
                ' ''10.6'', ' ||
                'crh.cash_receipt_history_id, ' ||
                'crh.amount, ' ||
                'crh.factor_discount_amount, ' ||
                'cr.currency_code, ' ||
                'crh.exchange_rate ' ;

Line: 423


		'select ''debit memo reversal'' ' ||
		'from   ar_payment_schedules ps1 ' ||
		'where  ps1.reversed_cash_receipt_id = cr.cash_receipt_id ' ||
		'and    ps1.class = ''DM'') ';

Line: 428


     arp_standard.debug('Inside main_select_remitted');

Line: 545


select set_of_books_id INTO v_set_of_bks_id from ar_system_parameters;

Line: 548


	main_select_remitted(
			statement,
                        0,
                        -1 );

Line: 555


    main_select_factored(
                        statement,
                        0,
                        -1 );

Line: 562


    main_select_risk(
                        statement,
                        0,
                        -1 );

Line: 712


                Deleted the condition of 'v_ex_rate IS NULL'. */

                IF ( locked AND p_crh_rec.current_record_flag = 'Y'
                     AND v_ex_rate > 0 ) THEN

			IF (v_currency <> v_cr_currency) THEN
			    -- 785113
			    -- Bank currency not equal to receipt currency. Calculate
			    -- new accounted amounts using the receipt amount and exch.rate.
			    --
			    v_amount := arp_util.functional_amount(
					v_crh_amount,
					ARP_GLOBAL.functional_currency,
					nvl(v_ex_rate,nvl(v_ex_rate_old,1)),
					NULL,NULL );

Line: 1061


    SELECT org_id
    INTO l_org_id
    FROM ar_system_parameters;

Line: 1127


  select count(*)
  into l_count
  from ar_autoclear_interim
  where request_id = l_master_request_id;

Line: 1136


fnd_file.put_line( FND_FILE.LOG, 'No of Receipts Selected :'||l_count);

Line: 1174


    DELETE FROM ar_autoclear_interim
    WHERE request_id = l_master_request_id;

Line: 1243


select set_of_books_id INTO v_set_of_bks_id from ar_system_parameters;

Line: 1246


    statement := 'INSERT INTO ar_autoclear_interim
                  ( cash_receipt_id,
                    trx_date,
                    gl_date,
                    exchange_date,
                    exchange_rate_type,
                    exchange_rate,
                    currency_code,
                    amount,
                    factor_discount_amount,
                    module_name,
                    module_version,
                    cash_receipt_history_id,
                    crh_amount,
                    crh_factor_discount_amount,
                    cr_currency_code,
                    exchange_rate_old,
                    current_worker,
                    request_id,
                    type
                  ) ';

Line: 1267


	main_select_remitted(
			statement,
                        p_total_workers,
                        p_request_id );

Line: 1274


    statement := 'INSERT INTO ar_autoclear_interim
                  ( cash_receipt_id,
                    trx_date,
                    gl_date,
                    exchange_date,
                    exchange_rate_type,
                    exchange_rate,
                    currency_code,
                    amount,
                    factor_discount_amount,
                    module_name,
                    module_version,
                    cash_receipt_history_id,
                    crh_amount,
                    crh_factor_discount_amount,
                    cr_currency_code,
                    exchange_rate_old,
                    current_worker,
                    request_id,
                    type
                  ) ';

Line: 1295


        main_select_factored(
                        statement,
                        p_total_workers,
                        p_request_id  );

Line: 1302


    statement := 'INSERT INTO ar_autoclear_interim
                  ( cash_receipt_id,
                    trx_date,
                    gl_date,
                    module_name,
                    module_version,
                    cash_receipt_history_id,
                    current_worker,
                    request_id,
                    type
                  ) ';

Line: 1313


        main_select_risk(
                        statement,
                        p_total_workers,
                        p_request_id  );

Line: 1368


    fnd_file.put_line(fnd_file.LOG, 'No of Records Selected : '|| ignore);

Line: 1371


commit; -- Commit the values inserted into the interim table.

Line: 1383


procedure main_select_risk_parallel
(
        statement IN OUT NOCOPY VARCHAR2       ) IS
--
BEGIN
--
/* 07-JUL-2000 J Rautiainen BR Implementation
 * Receipts created by the Bills Receivable transaction remittance process
 * having BR_REMIT receipt class cannot be risk eliminated */
/*5444413 Index hint added*/
fnd_file.put_line(fnd_file.log, 'main_select_risk_parallel()+');

Line: 1394


statement :=    'select ' ||
                        'cash_receipt_id, ' ||
                        'trx_date, ' ||
                        'gl_date , ' ||
                        ' module_name, ' ||
                        ' module_version, ' ||
                        'cash_receipt_history_id  ' ||
                'from ' ||
                        'ar_autoclear_interim ' ||
                'where ' ||
                        'request_id = :b_request_id ' ||
                'and    current_worker = :b_worker_number '||
  'and type = ''RISK'' ';

Line: 1410


fnd_file.put_line(fnd_file.log, 'main_select_risk_parallel()-');

Line: 1419


procedure main_select_factored_parallel
(
        statement IN OUT NOCOPY VARCHAR2       ) IS
--
BEGIN
--
/* Bug 2484984 Modified the call gl_currency_api.get_rate so that
   there is an NVL for the exchange_rate_type parameter rather
   than an NVL for the function call itself . */

/* Bug 3820774 Replaced the get_rate with get_rate_sql. */
/*Bug5444413 Added hint */
fnd_file.put_line(fnd_file.log, 'main_select_factored_parallel()+');

Line: 1432


statement :=    'select ' ||
                        'cash_receipt_id, '||
                        'trx_date, ' ||
                        'gl_date, ' ||
                        'exchange_date, ' ||
                        'exchange_rate_type, ' ||
                        'exchange_rate, ' ||
                        'currency_code, ' ||
                        'amount, ' ||
                        'factor_discount_amount, ' ||
                        ' module_name, ' ||
                        ' module_version, ' ||
                        'cash_receipt_history_id, ' ||
                        'crh_amount, ' ||
                        'crh_factor_discount_amount, ' ||
                        'cr_currency_code, ' ||
                        'exchange_rate_old ' ||
                'from ' ||
                        'ar_autoclear_interim ' ||
                'where ' ||
                        'request_id = :b_request_id ' ||
                'and    current_worker = :b_worker_number '||
                'and    type = ''FACTOR'' ';

Line: 1458


fnd_file.put_line(fnd_file.log, 'main_select_factored_parallel()-');

Line: 1466


procedure main_select_remitted_parallel
(
	statement IN OUT NOCOPY VARCHAR2	) IS
--
BEGIN
--
/* Bug 2484984 Modified the call gl_currency_api.get_rate so that
   there is an NVL for the exchange_rate_type parameter rather
   than an NVL for the function call itself . */
/* Bug 3820774 Replaced the get_rate with get_rate_sql. */
/*5444413 hint added here*/
fnd_file.put_line(fnd_file.log, 'main_select_remitted_parallel()+');

Line: 1478


statement :=    'select ' ||
                        'cash_receipt_id, '||
                        'trx_date, ' ||
                        'gl_date, ' ||
                        'exchange_date, ' ||
                        'exchange_rate_type, ' ||
        		            'exchange_rate, ' ||
                        'currency_code, ' ||
                        'amount, ' ||
                        'factor_discount_amount, ' ||
        		' module_name, ' ||
        		' module_version, ' ||
        		'cash_receipt_history_id, ' ||
                        'crh_amount, ' ||
                        'crh_factor_discount_amount, ' ||
                        'cr_currency_code, ' ||
                        'exchange_rate_old ' ||
                'from ' ||
                        'ar_autoclear_interim ' ||
                'where ' ||
                        'request_id = :b_request_id ' ||
                'and    current_worker = :b_worker_number '||
                'and    type = ''REMIT'' ';

Line: 1504


fnd_file.put_line(fnd_file.log, 'main_select_remitted_parallel()-');

Line: 1573


select set_of_books_id INTO v_set_of_bks_id from ar_system_parameters;

Line: 1576


	main_select_remitted_parallel(
			statement );

Line: 1581


        main_select_factored_parallel(
                        statement );

Line: 1586


        main_select_risk_parallel(
                        statement );

Line: 1704


                Deleted the condition of 'v_ex_rate IS NULL'. */
                IF ( locked AND p_crh_rec.current_record_flag = 'Y'
                     AND v_ex_rate > 0 ) THEN
			IF (v_currency <> v_cr_currency) THEN
			    -- 785113
			    -- Bank currency not equal to receipt currency. Calculate
			    -- new accounted amounts using the receipt amount and exch.rate.
			    --
			    v_amount := arp_util.functional_amount(
					v_crh_amount,
					ARP_GLOBAL.functional_currency,
					nvl(v_ex_rate,nvl(v_ex_rate_old,1)),
					NULL,NULL );