Check Provider and receiver projects for a project

SELECT org_name,
segment1 "Project_number",
name,
start_date,
project_status_name,
country,
rece_project,
provider_project,
concatenated_segments "account",
source
FROM
(
select distinct hou.name org_name,ppa.segment1,
ppa.name,
ppa.start_date,
pps.project_status_name,
raa.country,
(select ppa1.segment1
from apps.pa_projects_all ppa1,
apps.pa_tasks pat1
where ppa1.project_id = pat1.project_id
and pat1.task_id = ppc.receiver_task_id
) rece_project,
prov_project.project provider_project,
gcc.concatenated_segments,
'expend' source
from
apps.pa_projects_all ppa,
apps.pa_project_customers ppc,
apps.ra_addresses_all raa,
apps.pa_project_statuses pps,
apps.pa_expenditure_items_all pei,
apps.pa_cost_distribution_lines_all pcd,
apps.gl_code_combinations_kfv gcc,
apps.hr_operating_units hou,
(SELECT
replace(substr(max(sys_connect_by_path(prov_proj,',')),2),' ','') project,project_id,rec_proj
FROM
(select ppa1.segment1 prov_proj,
ppa2.project_id,
ppa2.segment1 rec_proj,
row_number() over (partition by ppa2.project_id order by ppa1.segment1) rn
from apps.pa_projects_all ppa1
,apps.pa_project_customers ppc
,apps.pa_tasks pat1
,apps.pa_projects_all ppa2
where 1=1
and ppc.project_id = ppa1.project_id
and receiver_task_id = pat1.task_id
and to_char(ppa2.creation_date,'YYYY') = '2009'
--and ppa2.project_id = 28490
and pat1.project_id = ppa2.project_id
) start with rn=1 connect by prior rn = rn - 1 and
prior project_id = project_id
group by project_id,rec_proj) prov_project
where ppa.project_id = ppc.project_id
and ppc.ship_to_address_id = raa.address_id
and ppa.project_status_code = pps.project_status_code
and ppa.project_id = pei.project_id
and pei.expenditure_item_id = pcd.expenditure_item_id
and pcd.dr_code_combination_id = gcc.code_combination_id
and hou.organization_id = ppa.org_id
and ppa.project_id = prov_project.project_id(+)
and to_char(ppa.creation_date,'YYYY') = '2009'
--and ppa.project_id = prov_project.project_id
--and ppa.org_id = 192
--and ppa.project_id in(28490,118671)
--order by 1
UNION
select distinct hou.name org_name,ppa.segment1,
ppa.name,
ppa.start_date,
pps.project_status_name,
raa.country,
(select ppa1.segment1
from apps.pa_projects_all ppa1,
apps.pa_tasks pat1
where ppa1.project_id = pat1.project_id
and pat1.task_id = ppc.receiver_task_id
) rece_project,
prov_project.project provider_project,
gcc.concatenated_segments,
'event' source
from
apps.pa_projects_all ppa,
apps.pa_project_customers ppc,
apps.ra_addresses_all raa,
apps.pa_project_statuses pps,
apps.pa_events pe,
apps.pa_cust_event_rdl_all pcd,
apps.gl_code_combinations_kfv gcc,
apps.hr_operating_units hou,
(SELECT
replace(substr(max(sys_connect_by_path(prov_proj,',')),2),' ','') project,project_id,rec_proj
FROM
(select ppa1.segment1 prov_proj,
ppa2.project_id,
ppa2.segment1 rec_proj,
row_number() over (partition by ppa2.project_id order by ppa1.segment1) rn
from apps.pa_projects_all ppa1
,apps.pa_project_customers ppc
,apps.pa_tasks pat1
,apps.pa_projects_all ppa2
where 1=1
and ppc.project_id = ppa1.project_id
and receiver_task_id = pat1.task_id
and to_char(ppa2.creation_date,'YYYY') = '2009'
--and ppa2.project_id = 28490
and pat1.project_id = ppa2.project_id
) start with rn=1 connect by prior rn = rn - 1 and
prior project_id = project_id
group by project_id,rec_proj) prov_project
where ppa.project_id = ppc.project_id
and ppc.ship_to_address_id = raa.address_id
and ppa.project_status_code = pps.project_status_code
and ppa.project_id = pe.project_id
and pe.project_id = pcd.project_id
and pe.event_num = pcd.event_num
and pcd.code_combination_id = gcc.code_combination_id
and hou.organization_id = ppa.org_id
and ppa.project_id = prov_project.project_id(+)
and to_char(ppa.creation_date,'YYYY') = '2009'
--and ppa.project_id = prov_project.project_id
--and ppa.org_id = 192
--and ppa.project_id in(28490,118671)
)
order by 1,2