Query to Find AP Reimbursable Expenses in Oracle Fusion

WITH GET_PERIOD_AVG_RATE
        AS (  SELECT gper.avg_rate,
                     gsob.CURRENCY_CODE AS functional_currency,
                     gper.period_name,
                     gper.to_currency_code
                FROM GL_LOOKUPS lk,
                     GL_TRANSLATION_RATES gper,
                     gl_sets_of_books gsob
               WHERE     lk.lookup_type = ‘TRANSLATION_BAL_TYPE’
                     AND gper.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID
                     AND lk.lookup_code = gper.actual_flag
            GROUP BY gper.avg_rate,
                     gsob.CURRENCY_CODE,
                     gper.period_name,
                     gper.to_currency_code)
SELECT ppat.segment1 project_num,
       ppat.name project_name,
       hou.name project_org,
       hou1.name exp_org,
       TO_CHAR (gjl.effective_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) AS gl_date,
       gjh.period_name,
       aid.amount invoice_amount,
       gjh.currency_code journal_currency,
       TO_CHAR (aid.pjc_expenditure_item_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) AS expenditure_item_date,
       gcc.segment2 ACCT,
       TO_CHAR (gjh.CURRENCY_CONVERSION_DATE, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) AS CURRENCY_CONVERSION_DATE,
       gcc.segment1 Company,
       gcc.segment3 Department,
       gcc.segment4 Region,
       aid.RECEIPT_CURRENCY_CODE,
       gjh.je_category je_category,
       HP.party_name Supplier_name,
       (SELECT PPNF.full_name
          FROM per_person_names_f PPNF
         WHERE PPNF.person_id = EI.incurred_by_person_id
               AND PPNF.name_type = ‘GLOBAL’
               AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND  NVL ( PPNF.EFFECTIVE_END_DATE, TRUNC (SYSDATE) + 1))
          emp_name,
       gjl.description journal_description,
       gjl.attribute1 Journal_line_DFF,
       DECODE (sob.currency_code, ‘USD’, 1, gpavg.avg_rate) usd_conversion_rate,
       ROUND ( xal.accounted_dr * DECODE (sob.currency_code, ‘USD’, 1, gpavg.avg_rate), 2) us_accounted_dr,
       ROUND ( xal.accounted_cr * DECODE (sob.currency_code, ‘USD’, 1, gpavg.avg_rate), 2) us_accounted_cr,
       ei.project_currency_code,
       ei.project_burdened_cost,
  FROM ap_invoice_distributions_all AID,
       ap_invoices_all aia,
       ap_invoice_lines_all ail,
       hz_parties HP,
       PJF_PROJECTS_ALL_vl ppat,
       hr_all_organization_units hou,
       hr_all_organization_units hou1,
       xla_transaction_entities xte,
       xla_events xe,
       xla_ae_headers xah,
       xla_ae_lines xal,
       gl_import_references gir,
       gl_je_headers gjh,
       gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_sets_of_books sob,
       PJC_EXP_ITEMS_ALL ei,
       GET_PERIOD_AVG_RATE gpavg
 WHERE aia.invoice_id = ail.invoice_id
   AND aid.invoice_line_number = ail.line_number
   AND ail.invoice_id = aid.invoice_id
   AND aid.PJC_PROJECT_ID = ppat.project_id
   AND hou.organization_id = ppat.carrying_out_organization_id
   AND hou1.organization_id = aid.PJC_ORGANIZATION_ID
   AND aia.invoice_id = NVL (“SOURCE_ID_INT_1”, (-99)) –19004(invoice_id)
   AND xe.entity_id = xah.entity_id
   AND aid.amount = NVL (xal.accounted_dr, xal.accounted_cr)
   AND xte.entity_code = ‘AP_INVOICES’
   AND xte.application_id = 200
   AND xe.event_id = aid.ACCOUNTING_EVENT_ID
   AND xte.entity_id = xe.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND UPPER (xal.ACCOUNTING_CLASS_CODE) IN (‘ITEM EXPENSE’)
   AND xte.application_id = xah.application_id
    AND gcc.code_combination_id = xal.code_combination_id
   AND aid.LINE_TYPE_LOOKUP_CODE = ‘ITEM’
   AND xal.gl_sl_link_id = gir.gl_sl_link_id
   AND xal.gl_sl_link_table = gir.gl_sl_link_table
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjl.je_header_id = gjh.je_header_id
   AND sob.set_of_books_id = gjh.ledger_id
   AND gjh.currency_code = gpavg.functional_currency(+)
   AND gjh.period_name = gpavg.period_name(+)
   AND gpavg.to_currency_code (+) = ‘USD’
   AND aia.party_id = HP.party_id
   AND ei.vendor_id = aia.vendor_id
   AND ei.project_id = aid.pjc_project_id
   AND ei.task_id = aid.pjc_task_id
   AND aid.org_id = ei.org_id
   AND aid.amount = ei.quantity
   AND aid.invoice_id = aia.invoice_id
   AND aid.LINE_TYPE_LOOKUP_CODE = ‘ITEM’
   AND ei.ORIG_TRANSACTION_REFERENCE = aid.INVOICE_DISTRIBUTION_ID