AP-SLA-GL QUERY with project and Purchase Order data

This query is very similar to my last. This query will show all the distributions that post from project invoices to GL. It also includes some PO data and project data as well.

Select aia.invoice_num as "Invoice Number", replace(replace(aida.description,chr(10),''),chr(13),'') as "Distribution Description",
NVL(psv.vendor_name, (SELECT PERSON_FIRST_NAME||' '||person_last_name
    from hz_parties hp
    where aia.party_id = hp.party_id)
    ) as Supplier,
gcc.segment1 "Company",
gcc.segment2 "Division",
gcc.segment3 "Department",
gcc.segment4 "Account",
gcc.segment5 "Fut1",
gcc.segment6 "Fut2",
aida.PJC_EXPENDITURE_ITEM_DATE,
aia.SOURCE as "Invoice Source",
pett.EXPENDITURE_TYPE_NAME,
prj.segment1 as "Project Number",
prj.name as "Project Name",
tsk.task_number as "Task",
poev.name as "Project Organization",
aida.amount as "Distribution Amount",
xah.period_name,
aida.accounting_date as "Accounting Date",
nvl(XDL.UNROUNDED_ACCOUNTED_DR,0) as "Debit",
nvl(XDL.UNROUNDED_ACCOUNTED_CR,0) as "Credit",
xdl.ae_header_id,
xdl.ae_line_num,
poh.segment1 as "Identifying PO",
aia.invoice_date,
v1.segment1 as "Invoice Line Purchase Order",
gl_flexfields_pkg.get_description_sql(41,4,gcc.segment4) NATURAL_ACCOUNT_DESC,
aida.ATTRIBUTE1 as "Budget ID",
gjb.name as "JE Batch Name",
gjh.name as "JE Name",
gjl.je_line_num as "JE Line",
XDL.accounting_line_code
FROM AP_INVOICES_ALL aia,
ap_invoice_lines_all al,
AP_INVOICE_DISTRIBUTIONS_ALL aida,
XLA_DISTRIBUTION_LINKS XDL,
xla_ae_lines xal,
xla_ae_headers xah,
gl_import_references gir,
GL_JE_LINES gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations gcc,
poz_suppliers_v psv,
PJF_EXP_TYPES_TL pett,
PJF_PROJECTS_ALL_VL prj,
PJC_XLA_TASK_REF_V tsk,
PJF_ORGANIZATIONS_EXPEND_V poev,
po_headers_all poh,
(select poh1.segment1, pda.PO_DISTRIBUTION_ID
FROM PO_DISTRIBUTIONS_ALL pda, po_headers_all poh1
WHERE 1=1
AND pda.po_header_id  = poh1.po_header_id) v1
WHERE 1=1
AND al.invoice_id = aia.invoice_id
and al.line_number = aida.invoice_line_number
and al.invoice_id = aida.invoice_id
and aida.invoice_distribution_id = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
and aida.accounting_event_id = xdl.event_id
AND xal.ae_header_id = xdl.ae_header_id       AND xal.ae_line_num = xdl.ae_line_num
AND XDL.ae_header_id = xah.ae_header_id
and xal.GL_SL_LINK_ID = gir.GL_SL_LINK_ID
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 gjh.je_batch_id = gjb.je_batch_id
and gjl.code_combination_id = gcc.code_combination_id
AND aia.vendor_id = psv.vendor_id (+)
and aida.pjc_EXPENDITURE_TYPE_ID = pett.EXPENDITURE_TYPE_ID
AND aida.pjc_project_id = prj.project_id
AND aida.pjc_task_id = tsk.task_id
AND aida.pjc_organization_id = poev.organization_id
AND aia.po_header_id  = poh.po_header_id (+)
and aida.po_distribution_id = v1.po_distribution_id (+)
and poev.effective_end_date > aida.PJC_EXPENDITURE_ITEM_DATE
and poev.effective_start_date < aida.PJC_EXPENDITURE_ITEM_DATE
and xdl.APPLIED_TO_APPLICATION_ID = 200
AND pett.LANGUAGE = 'US'
AND xal.accounting_class_code != 'INTRA'
AND aia.invoice_num = nvl(:Invoice, aia.invoice_num) -- 'B06676970'AND UPPER(prj.name) like '%'||UPPER(:PrjName)||'%'
AND (UPPER(psv.vendor_name) like '%'||UPPER(:SupplierName)||'%' or 'All' in (:SupplierName||'All'))
AND xah.period_name = NVL(:Period,xah.period_name)
AND (prj.segment1 in (:Project_num) or 'All' IN (:Project_num||'All'))
and gcc.segment4 = NVL(:Account,gcc.segment4)
UNION ALL
Select aia.invoice_num as "Invoice Number", replace(replace(st.description,chr(10),''),chr(13),'') as "Distribution Description",
psv.vendor_name as Supplier,
gcc.segment1 "Company",
gcc.segment2 "Division",
gcc.segment3 "Department",
gcc.segment4 "Account",
gcc.segment5 "Fut1",
gcc.segment6 "Fut2",
st.PJC_EXPENDITURE_ITEM_DATE,
aia.SOURCE as "Invoice Source",
pett.EXPENDITURE_TYPE_NAME,
prj.segment1 as "Project Number",
prj.name as "Project Name",
tsk.task_number as "Task",
poev.name as "Project Organization",
st.amount as "Distribution Amount",
xah.period_name,
st.accounting_date as "Accounting Date",
nvl(XDL.UNROUNDED_ACCOUNTED_DR,0) as "Debit",
nvl(XDL.UNROUNDED_ACCOUNTED_CR,0) as "Credit",
xdl.ae_header_id,
xdl.ae_line_num,
poh.segment1 as "Identifying PO",
aia.invoice_date,
v1.segment1 as "Invoice Line Purchase Order",
gl_flexfields_pkg.get_description_sql(41,4,gcc.segment4) NATURAL_ACCOUNT_DESC,
st.ATTRIBUTE1 as "Budget ID",
gjb.name as "JE Batch Name",
gjh.name as "JE Name",
gjl.je_line_num as "JE Line",
XDL.accounting_line_code
FROM AP_INVOICES_ALL aia,
ap_invoice_lines_all al,
AP_SELF_ASSESSED_TAX_DIST_ALL st,
XLA_DISTRIBUTION_LINKS XDL,
xla_ae_lines xal,
xla_ae_headers xah,
gl_import_references gir,
GL_JE_LINES gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations gcc,
poz_suppliers_v psv,
PJF_EXP_TYPES_TL pett,
PJF_PROJECTS_ALL_VL prj,
PJC_XLA_TASK_REF_V tsk,
PJF_ORGANIZATIONS_EXPEND_V poev,
po_headers_all poh,
(select poh1.segment1, pda.PO_DISTRIBUTION_ID
    FROM PO_DISTRIBUTIONS_ALL pda, po_headers_all poh1
    WHERE 1=1
    AND pda.po_header_id  = poh1.po_header_id
) v1
WHERE 1=1
AND al.invoice_id = aia.invoice_id
and al.line_number = st.invoice_line_number
and al.invoice_id = st.invoice_id
and st.invoice_distribution_id = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
and st.accounting_event_id = xdl.event_id
AND xal.ae_header_id = xdl.ae_header_id       AND xal.ae_line_num = xdl.ae_line_num
AND XDL.ae_header_id = xah.ae_header_id
and xal.GL_SL_LINK_ID = gir.GL_SL_LINK_ID
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 gjh.je_batch_id = gjb.je_batch_id
and gjl.code_combination_id = gcc.code_combination_id
AND aia.vendor_id = psv.vendor_id
and st.pjc_EXPENDITURE_TYPE_ID = pett.EXPENDITURE_TYPE_ID
AND st.pjc_project_id = prj.project_id
AND st.pjc_task_id = tsk.task_id
AND st.pjc_organization_id = poev.organization_id
AND aia.po_header_id  = poh.po_header_id (+)
and st.po_distribution_id = v1.po_distribution_id (+)
and poev.effective_end_date > st.PJC_EXPENDITURE_ITEM_DATE
and poev.effective_start_date < st.PJC_EXPENDITURE_ITEM_DATE
and xdl.APPLIED_TO_APPLICATION_ID = 200
AND pett.LANGUAGE = 'US'
AND xal.accounting_class_code != 'INTRA'
AND aia.invoice_num = nvl(:Invoice, aia.invoice_num) -- 'B06676970'AND UPPER(prj.name) like '%'||UPPER(:PrjName)||'%'
AND UPPER(psv.vendor_name) like '%'||UPPER(:SupplierName)||'%'
AND xah.period_name = NVL(:Period,xah.period_name)
AND (prj.segment1 in (:Project_num) or 'All' IN (:Project_num||'All'))
and gcc.segment4 = NVL(:Account,gcc.segment4)