Query to find project, classfication and max trans date happend on exp/Rev/Inv

SELECT DISTINCT hou.NAME operating_unit,
pa.segment1 project_number,
pa.NAME project_name,
pa.creation_date proj_creation_date,
haou.NAME project_organization,
pef.full_name manager,
pa.description project_description,
ps.project_status_name project_status,
pc1.class_code Test,
rc.country,
rac.customer_number,
rac.customer_name,
activity.last_activity_date,
activity.LE LE_account,
rece.segment1 receiver_project,
rece.country receiver_country,
rece.name receiver_OU,
rece.class_code receiver_class,
tem.segment1 created_from_project,
tem.name created_from_project_name
FROM apps.pa_projects_all pa,
apps.hr_operating_units hou,
apps.hr_all_organization_units haou,
apps.pa_project_classes pc1,
apps.pa_project_players pp,
apps.per_all_people_f pef,
apps.pa_project_statuses ps,
apps.pa_project_customers ppc,
apps.ra_addresses_all rc,
apps.ra_customers rac,
apps.pa_projects_all tem,
(
select max(creation_date) last_activity_date,max(segment1) LE,project_id
from
(SELECT invoice_date creation_date,gcc.segment1,pdia.project_id
FROM apps.pa_draft_invoices_all pdia,
apps.RA_CUSTOMER_TRX_ALL r,
apps.ra_cust_trx_line_gl_dist_all CTLGD,
apps.ra_customer_trx_lines_all CTL,
apps.gl_code_combinations gcc
where ra_invoice_number = r.trx_number(+)
and R.customer_trx_id = CTL.customer_trx_id(+)
AND CTLGD.customer_trx_line_id(+) = CTL.customer_trx_line_id
AND gcc.code_combination_id(+)=CTLGD.CODE_COMBINATION_ID
AND ctl.line_type(+) = 'LINE'
--and project_id = 553
and invoice_date = (
SELECT max(invoice_date)
FROM apps.pa_draft_invoices_all
where project_id = pdia.project_id
)
UNION
SELECT creation_date,gcc.segment1,pdra.project_id
FROM apps.pa_draft_revenues_all pdra,
apps.gl_code_combinations gcc
where 1=1
AND gcc.code_combination_id(+)=pdra.UNEARNED_CODE_COMBINATION_ID
--and project_id = 553
and creation_date = (
SELECT max(creation_date)
FROM apps.pa_draft_revenues_all
where project_id = pdra.project_id
)
UNION
select expenditure_item_date creation_date,gcc.segment1,pei.project_id
from apps.pa_expenditure_items_all pei,
apps.PA_COST_DIST_LINES_V pcd,
apps.gl_code_combinations gcc
WHERE pei.expenditure_item_id = pcd.expenditure_item_id(+)
and pcd.DR_CODE_COMBINATION_ID = gcc.code_combination_id(+)
--and pei.project_id = 553
AND expenditure_item_date = (
SELECT max(expenditure_item_date) creation_date
FROM apps.pa_expenditure_items_all
where project_id = pei.project_id
)
) group by project_id
) activity,
(
select ppa.segment1,raa.country,hou.name,ppcc.class_code,pt.task_id
FROM
apps.pa_projects_all ppa,
apps.pa_project_customers ppc,
apps.ra_addresses_all raa,
apps.pa_tasks pt,
apps.pa_project_classes ppcc,
apps.hr_operating_units hou
where 1=1
and ppa.project_id = ppc.project_id
AND ppc.ship_to_address_id = raa.address_id
AND PROJECT_RELATIONSHIP_CODE = 'PRIMARY'
and ppa.project_id = pt.project_id
--AND pt.task_id = 146747
AND ppa.project_id = ppcc.project_id(+)
AND ppcc.class_category(+) = 'Rec Class'
AND ppa.org_id = hou.organization_id
) rece
WHERE pa.org_id=hou.organization_id AND
pa.carrying_out_organization_id=haou.organization_id AND
pa.project_id=ppc.project_id AND
ppc.PROJECT_RELATIONSHIP_CODE='PRIMARY' AND
ppc.ship_to_address_id=rc.address_id AND
pa.project_id=pc1.project_id(+) AND
pc1.class_category(+)= 'Test Class' AND
pp.project_id=pa.project_id AND pp.project_role_type='PROJECT MANAGER' AND
pp.person_id=pef.person_id AND
NVL(pef.effective_end_date,SYSDATE+1)>SYSDATE AND
pa.project_status_code=ps.project_status_code AND
ppc.customer_id=rac.customer_id and
ps.status_type = 'PROJECT' AND
pa.template_flag = 'N' AND
pa.project_id = activity.project_id(+) and
pa.created_from_project_id = tem.project_id AND
ppc.receiver_task_id = rece.task_id(+) AND
--pa.project_id in(1780507,146747) and
--trunc(pa.creation_date) >= to_date(:p_date) AND -- MM/DD/YYYY
ORDER BY 1, 2