Aging Reports Error: Aging - 7 Buckets - By Collector Report Errors with ORA-01652: unable to extend temp segment by 16 in tablespace TEMP (Doc ID 454043.1)

Last updated on AUGUST 24, 2016

Applies to:

Oracle Receivables - Version 11.5.10.2 and later
Information in this document applies to any platform.

Symptoms

When attempting to run 'Aging - 7 Buckets - By Collector Report' with report parameter "Set of books Currency" set to Reporting Currency = USD, it ran for over an hour then failed with error:
ORA-01652: unable to extend temp segment by 16 in tablespace TEMP

When submitted with "Set of books currency" set to Primary Currency = LTL instead, it completes without error.

A trace/tkprof shows the following:

select rpad ( 'a' , 50 , '-' ) short_cust_name , 0 cust_id , rpad ( 'a' , 30 , '-' ) cust_no ,
    rpad ( 'a' , 500 , '-' ) sort_field1 , rpad ( 'a' , 40 , '-' ) sort_field2 , 0 payment_sched_id ,
    rpad ( 'a' , 32 , '-' ) class , sysdate due_date , 0 amt_due_remaining , 0 days_past_due , 0
    amount_adjusted , 0 amount_applied , 0 amount_credited , sysdate gl_date , 'x' data_converted , 0
    ps_exchange_rate , 0 b0 , 0 b1 , 0 b2 , 0 b3 , 0 b4 , 0 b5 , 0 b6 , rpad ( 'a' , 25 , '-' )
    bal_segment_value , rpad ( 'a' , 500 , '-' ) inv_tid , rpad ( 'a' , 32 , '-' ) invoice_type from
    dual   where 1 = 2 UNION ALL select /*+ ORDERED */
             substrb(party.party_name,1,50) short_cust_name,
    ..
    ..
    site_cp.site_use_id (+) = ps.customer_site_use_id and col.collector_id = NVL (
    site_cp.collector_id , cust_cp.collector_id ) AND NVL ( gld.ORG_ID , : p_reporting_entity_id ) = :
    p_reporting_entity_id UNION ALL select /*+ ORDERED INDEX (app AR_RECEIVABLE_APPLICATIONS_N1) */
             substrb(party.party_name,1,50) short_cust_name,
             nvl(cust_acct.cust_account_id, -999) cust_id,
             cust_acct.account_number cust_no, col.name, to_char(col.collector_id),
    ps.payment_schedule_id,
             DECODE(app.applied_payment_schedule_id,-4,'CLAIM',ps.class),
             ps.due_date,
             decode
             ( :convert_flag, 'Y', nvl(-sum(app.acctd_amount_applied_from),0),
               nvl(-sum(app.amount_applied),0)
             ),
             ceil(to_date(:as_of_date) - ps.due_date),
             ps.amount_adjusted,
             ps.amount_applied,
             ps.amount_credited,
             ps.gl_date,
             decode(ps.invoice_currency_code, :functional_currency, NULL,
                    decode(ps.exchange_rate, NULL, '*', NULL)),
             nvl(ps.exchange_rate, 1), arpt_sql_func_util.bucket_function(:bucket_line_type_0,
                    ps.amount_in_dispute,ps.amount_adjusted_pending,
                    :bucket_days_from_0,:bucket_days_to_0,
                     ps.due_date,:bucket_category,to_date(:as_of_date)) b0,
          arpt_sql_func_util.bucket_function(:bucket_line_type_1,
                    ps.amount_in_dispute,ps.amount_adjusted_pending,
                    :bucket_days_from_1,:bucket_days_to_1,
                     ps.due_date,:bucket_category,to_date(:as_of_date)) b1,
          arpt_sql_func_util.bucket_function(:bucket_line_type_2,
                    ps.amount_in_dispute,ps.amount_adjusted_pending,
                    :bucket_days_from_2,:bucket_days_to_2,
                     ps.due_date,:bucket_category,to_date(:as_of_date)) b2,
          arpt_sql_func_util.bucket_function(:bucket_line_type_3,
                    ps.amount_in_dispute,ps.amount_adjusted_pending,
                    :bucket_days_from_3,:bucket_days_to_3,
                     ps.due_date,:bucket_category,to_date(:as_of_date)) b3,
          arpt_sql_func_util.bucket_function(:bucket_line_type_4,
                    ps.amount_in_dispute,ps.amount_adjusted_pending,
                    :bucket_days_from_4,:bucket_days_to_4,
                     ps.due_date,:bucket_category,to_date(:as_of_date)) b4,
          arpt_sql_func_util.bucket_function(:bucket_line_type_5,
                    ps.amount_in_dispute,ps.amount_adjusted_pending,
                    :bucket_days_from_5,:bucket_days_to_5,
                     ps.due_date,:bucket_category,to_date(:as_of_date)) b5,
          arpt_sql_func_util.bucket_function(:bucket_line_type_6,
                    ps.amount_in_dispute,ps.amount_adjusted_pending,
                    :bucket_days_from_6,:bucket_days_to_6,
                     ps.due_date,:bucket_category,to_date(:as_of_date)) b6,c.SEGMENT1,col.name
    inv_tid,initcap(:lp_payment_meaning) from      ar_payment_schedules_all_mrc_v ps,
                       ar_receivable_apps_all_mrc_v app,
                       hz_cust_accounts cust_acct,
                       hz_parties party,
                       gl_code_combinations c ,hz_customer_profiles site_cp  ,hz_customer_profiles
    cust_cp  ,ar_collectors col    where app.gl_date <= to_date ( : as_of_date ) and ps.customer_id
    = cust_acct.cust_account_id (+) and cust_acct.party_id = party.party_id (+) and ps.cash_receipt_id
    = app.cash_receipt_id and app.code_combination_id = c.code_combination_id and app.status in (
    'ACC' , 'UNAPP' , 'UNID' , 'OTHER ACC' ) and nvl ( app.confirmed_flag , 'Y' ) = 'Y' and
    ps.gl_date_closed > to_date ( : as_of_date ) and ( ( app.reversal_gl_date is not null AND
    ps.gl_date <= to_date ( : as_of_date ) ) OR app.reversal_gl_date is null ) and decode ( upper (
    : p_in_currency ) , NULL , ps.invoice_currency_code , upper ( : p_in_currency ) ) =
    ps.invoice_currency_code and nvl ( ps.receipt_confirmed_flag , 'Y' ) = 'Y' and party.party_name
    >= : p_in_customer_name_low and party.party_name <= : p_in_customer_name_high and
    cust_cp.cust_account_id = cust_acct.cust_account_id and cust_cp.site_use_id is null and
    site_cp.site_use_id (+) = ps.customer_site_use_id and col.collector_id = NVL (
    site_cp.collector_id , cust_cp.collector_id ) AND NVL ( ps.ORG_ID , : p_reporting_entity_id ) = :
    p_reporting_entity_id AND NVL ( app.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id
    GROUP BY party.party_name , cust_acct.account_number , cust_acct.cust_account_id , col.name ,
    to_char ( col.collector_id ) , ps.payment_schedule_id , ps.due_date , ps.amount_adjusted ,
    ps.amount_applied , ps.amount_credited , ps.gl_date , ps.amount_in_dispute ,
    ps.amount_adjusted_pending , ps.invoice_currency_code , ps.exchange_rate , DECODE (
    app.applied_payment_schedule_id , - 4 , 'CLAIM' , ps.class ) , c.code_combination_id , c.SEGMENT1
    , decode ( app.status , 'UNID' , 'UNID' , 'OTHER ACC' , 'OTHER ACC' , 'UNAPP' ) , col.name ,
    initcap ( : lp_payment_meaning ) ORDER BY 24 ASC,4 ASC,25 ASC,1 ASC,2 ASC,3 ASC
   
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.20       0.20          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1   7512.30    7998.82      44480      86327         40           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4   7512.50    7999.03      44480      86327         40           0

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms