Performance issue with APXINREV - Open Items Revaluation Report (XML) (Doc ID 1548497.1)

Last updated on MARCH 27, 2017

Applies to:

Oracle Payables - Version 12.1.1 and later
Information in this document applies to any platform.
Performance issue with APXINREV - Open Items Revaluation Report (XML)

Symptoms

APXINREV module: Open Items Revaluation Report ran for 13 + hours and failed.

  The following SQL statement too long:
  
  SELECT  GLCC.segment1 C_BALANCING ,  GLCC.segment3 C_ACCOUNT ,
  GLCC.SEGMENT1||'
  '||GLCC.SEGMENT2||'
  '||GLCC.SEGMENT3||'
  '||GLCC.SEGMENT4||'
  '||GLCC.SEGMENT5||'
  '||GLCC.SEGMENT6||'
  '||GLCC.SEGMENT7||'
  '||GLCC.SEGMENT8||'
  '||GLCC.SEGMENT9 C_FLEXDATA , i . invoice_type_lookup_code C_INVOICE_TYPE ,
  alc . displayed_field C_INVOICE_TYPE_LOOKUP , i . invoice_num C_INVOICE_NUM
  , i . invoice_currency_code C_CURRENCY_CODE , i . invoice_date
  C_INVOICE_DATE , hp . party_name C_VENDOR_NAME , pv . segment1
  C_VENDOR_NUMBER , pvs . vendor_site_code C_VENDOR_SITE , i .
  payment_cross_rate C_PAYMENT_CROSS_RATE , i . payment_currency_code
  C_PAYMENT_CURRENCY_CODE , sum ( nvl ( aid . amount , 0 ) )
  C_OP_ORIGINAL_AMOUNT , ( nvl ( i . exchange_rate , 0 ) ) C_EXCHANGE_RATE ,
  sum ( nvl ( aid . amount , 0 ) ) C_OP_FUNCTIONAL_AMOUNT , sum ( nvl ( aid .
  amount , 0 ) ) C_OP_REVAL_AMOUNT , i . invoice_id C_INVOICE_ID FROM
  ap_invoice_distributions aid , ap_invoices i ,  xla_ae_headers
  AEH,xla_ae_lines xal,xla_distribution_links xdl, gl_code_combinations glcc ,
  ap_lookup_codes alc , hz_parties hp , ap_suppliers pv , ap_supplier_sites
  pvs   WHERE aid.invoice_id = i.invoice_id and aid.accounting_date <=
  '31-AUG-11' AND i.invoice_type_lookup_code = alc.lookup_code AND
  alc.lookup_type = 'INVOICE TYPE' and glcc.SEGMENT1 || '' >= '10' and
  glcc.SEGMENT1 || '' <= '78' and AEH.event_id = aid.accounting_event_id and
  AEH.ledger_id = '2021' and AEH.gl_transfer_status_code = 'Y' and
  AEH.ae_header_id = xdl.ae_header_id and AEH.ae_header_id = xal.ae_header_id
  and xal.code_combination_id = glcc.code_combination_id and AEH.event_id =
  xdl.event_id and xal.ae_line_num = xdl.ae_line_num and
  xdl.source_distribution_type = 'AP_INV_DIST' and xdl.accounting_line_code
  like '%AP%LIAB%' and xdl.source_distribution_id_num_1 =
  aid.invoice_distribution_id and AEH.application_id = 200 and
  xal.application_id = 200 and xdl.application_id = 200 AND i.party_id =
  hp.party_id AND hp.party_id = pv.party_id (+) AND i.vendor_site_id =
  pvs.vendor_site_id (+) AND : c_daily_rate_lookup_error = 'N' AND
  aid.match_status_flag = 'A' GROUP BY GLCC.segment1 , GLCC.segment3 ,
  GLCC.SEGMENT1 || '
  ' || GLCC.SEGMENT2 || '
  ' || GLCC.SEGMENT3 || '
  ' || GLCC.SEGMENT4 || '
  ' || GLCC.SEGMENT5 || '
  ' || GLCC.SEGMENT6 || '
  ' || GLCC.SEGMENT7 || '
  ' || GLCC.SEGMENT8 || '
  ' || GLCC.SEGMENT9 , alc.displayed_field , i.invoice_num ,
  i.invoice_currency_code , i.invoice_date , i.freight_amount ,
  i.exchange_rate , i.invoice_id , hp.party_name , pv.segment1 ,
  pvs.vendor_site_code , i.invoice_type_lookup_code , i.payment_currency_code
  , i.payment_cross_rate UNION ALL SELECT  GLCC.segment1 C_BALANCING ,
  GLCC.segment3 C_ACCOUNT ,  GLCC.SEGMENT1||'
  '||GLCC.SEGMENT2||'
  '||GLCC.SEGMENT3||'
  '||GLCC.SEGMENT4||'
  '||GLCC.SEGMENT5||'
  '||GLCC.SEGMENT6||'
  '||GLCC.SEGMENT7||'
  '||GLCC.SEGMENT8||'
  '||GLCC.SEGMENT9 C_FLEXDATA , 'Payment' C_INVOICE_TYPE , NULL
  C_INVOICE_TYPE_LOOKUP , to_char ( ac . check_number ) C_INVOICE_NUM , ac .
  currency_code C_CURRENCY_CODE , ac . check_date C_INVOICE_DATE , hp .
  party_name C_VENDOR_NAME , pv . segment1 C_VENDOR_NUMBER , pvs .
  vendor_site_code C_VENDOR_SITE , i . payment_cross_rate C_PAYMENT_CROSS_RATE
  , i . payment_currency_code C_PAYMENT_CURRENCY_CODE , Sum ( nvl ( aip .
  amount , 0 ) * - 1 ) C_OP_ORIGINAL_AMOUNT , ( nvl ( i . exchange_rate , 0 )
  ) C_EXCHANGE_RATE , Sum ( nvl ( aip . amount , 0 ) * - 1 )
  C_OP_FUNCTIONAL_AMOUNT , Sum ( nvl ( aip . amount , 0 ) * - 1 )
  C_OP_REVAL_AMOUNT , ac . check_id C_INVOICE_ID FROM ap_checks ac ,
  ap_invoices i , ap_invoice_distributions aid , ap_invoice_payments aip ,
  xla_ae_headers AEH,xla_ae_lines xal,xla_distribution_links xdl,
  gl_code_combglcc , hz_parties hp , ap_suppliers pv , ap_supplier_sites pvs  
  WHERE aip.check_id = ac.check_id AND aip.invoice_id = i.invoice_id AND
  aid.invoice_id = i.invoice_id AND aip.accounting_date <= :
  C_ACC_PERIOD_END_DATE AND i.gl_date > : C_ACC_PERIOD_END_DATE and
  glcc.SEGMENT1 || '' >= '10' and glcc.SEGMENT1 || '' <= '78' and AEH.event_id
  = aip.accounting_event_id and AEH.ledger_id = '2021' and
  AEH.gl_transfer_status_code = 'Y' and xdl.ae_header_id = aeh.ae_header_id
  and aeh.ae_header_id = xal.ae_header_id and xal.code_combination_id =
  glcc.code_combination_id and aeh.event_id = xdl.event_id and xal.ae_line_num
  = xdl.ae_line_num and xdl.applied_to_distribution_type = 'AP_INV_DIST' and
  xdl.accounting_line_code like '%AP%LIAB%' and xdl.applied_to_dist_id_num_1 =
  aid.invoice_distribution_id and aeh.application_id = 200 and
  xal.application_id = 200 and xdl.application_id = 200 AND i.party_id =
  hp.party_id AND hp.party_id = pv.party_id (+) AND i.vendor_site_id =
  pvs.vendor_site_id (+) AND : c_daily_rate_lookup_error = 'N' GROUP BY
  GLCC.segment1 , GLCC.segment3 , GLCC.SEGMENT1 || '
  ' || GLCC.SEGMENT2 || '
  ' || GLCC.SEGMENT3 || '
  ' || GLCC.SEGMENT4 || '
  ' || GLCC.SEGMENT5 || '
  ' || GLCC.SEGMENT6 || '
  ' || GLCC.SEGMENT7 || '
  ' || GLCC.SEGMENT8 || '
  ' || GLCC.SEGMENT9 , ac.check_number , ac.currency_code , ac.check_date ,
  i.exchange_rate , ac.check_id , hp.party_name , pv.segment1 ,
  pvs.vendor_site_code , i.payment_currency_code , i.payment_cross_rate ORDER
  BY 1 ASC,2 ASC,3 ASC,9 ASC,10 ASC,11 ASC,6 ASC;

Changes

 NONE

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