Running below query in Fusion BIP: can someone help fine tune the query. running in production and its quite taking a long time and already exceeds the timeout.
purpose of the query is to retrieve invoice distribution details for those that have overridden account lines in SLA.
SELECT
aia.invoice_num,
aia.invoice_id,
aila.line_number,
aida.distribution_line_number,
aida.amount,
aida.LINE_TYPE_LOOKUP_CODE,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7 DISTRIBUTION_CC
FROM
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
gl_code_combinations gcc
WHERE 1=1
AND aia.invoice_id=aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aia.invoice_id = aila.invoice_id
--AND aia.invoice_num = '59049308'
and gcc.code_combination_id = aida.DIST_CODE_COMBINATION_ID
AND aida.line_type_lookup_code IN ('ITEM', 'IPV')
AND EXISTS
(SELECT 1 FROM ap_invoices_all aia1,
xla_transaction_entities xte,
xla_ae_headers xah ,
xla_ae_lines xal ,
gl_code_combinations gcc2
where 1=1
AND xte.entity_code = 'AP_INVOICES'
AND xte.SOURCE_ID_INT_1 =aia1.invoice_id
AND xah.entity_id = xte.entity_id
AND xal.ae_header_id = xah.ae_header_id
AND XTE.APPLICATION_ID =200
AND xah.application_id = 200
AND xal.application_id = 200
and gcc2.code_combination_id = xal.OVERRIDDEN_CODE_COMBINATION_ID
AND aia1.invoice_id = aia.invoice_id
and xal.SUBLEDGER_XCC_COMPLETE_STATUS = 'Y')
ORDER BY aia.invoice_num, aila.line_number, distribution_line_number