PRJ_PROJECT_MANAGER

Details

Schema: FUSION
Object owner: PJF
Object type: VIEW

Columns

Name
Team Member Id Project Id Person Name Person Email

Name

Team Member Id

Project Id

Person Name

Person Email

Query

SQL_Statement
select prs.proj_resource_id as "Team Member Id", prs.project_id as "Project Id", per.display_name as "Person Name", per.email as "Person Email" from fusion.pjt_project_resource prs, fusion.pjt_prj_enterprise_resource_vl per, PJF_PROJ_ELEMENTS_B prjElem where prs.resource_id = per.resource_id and prs.project_role_id = 1 and prjElem.project_id=prs.project_id and prjElem.object_type='PJF_STRUCTURES' and prjElem.primary_resource_id=prs.resource_id and (prs.START_DATE_ACTIVE is null or prs.START_DATE_ACTIVE <= trunc(sysdate)) and (prs.END_DATE_ACTIVE is null or prs.END_DATE_ACTIVE>= trunc(sysdate)) and not exists( select 1 from fusion.pjt_project_resource prs2 where prs.project_id=prs2.project_id and prs.resource_id=prs2.resource_id and prs2.project_role_id=1 and prs2.proj_resource_id>prs.proj_resource_id) UNION Select ProjectPartyEO.project_party_id as "Team Member Id", ProjectPartyEO.project_id as "Project Id", PersonNameDPEO.display_name as "Person Name", EmailAddressPEO.EMAIL_ADDRESS as "Person Email" FROM PJF_PROJECT_PARTIES ProjectPartyEO, PER_PERSON_NAMES_F_V PersonNameDPEO, PER_ALL_PEOPLE_F PersonDPEO, PER_EMAIL_ADDRESSES EmailAddressPEO where ProjectPartyEO.resource_source_id = PersonNameDPEO.PERSON_ID and ProjectPartyEO.resource_source_id = PersonDPEO.PERSON_ID and ProjectPartyEO.PROJECT_ROLE_ID=1 and PersonDPEO.PRIMARY_EMAIL_ID = EmailAddressPEO.EMAIL_ADDRESS_ID(+) and PersonDPEO.effective_start_date <= trunc(sysdate) and (PersonDPEO.Effective_end_date is null or PersonDPEO.Effective_end_date>= trunc(sysdate)) and PersonNameDPEO.effective_start_date <= trunc(sysdate) and (PersonNameDPEO.Effective_end_date is null or PersonNameDPEO.Effective_end_date>= trunc(sysdate)) and ProjectPartyEO.START_DATE_ACTIVE <= trunc(sysdate) and (ProjectPartyEO.END_DATE_ACTIVE is null or ProjectPartyEO.END_DATE_ACTIVE>= trunc(sysdate)) and (EmailAddressPEO.DATE_FROM(+) is null or EmailAddressPEO.DATE_FROM (+)<= trunc(sysdate)) and (EmailAddressPEO.DATE_TO(+) is null or EmailAddressPEO.DATE_TO(+)>= trunc(sysdate)) and not exists (select 1 from fusion.pjt_project_resource prs, PJF_PROJ_ELEMENTS_B prjElem where ProjectPartyEO.project_id = prjElem.project_id and ProjectPartyEO.project_id = prs.project_id and prs.project_role_id = 1 and prs.project_id=prjElem.project_id and prjElem.object_type='PJF_STRUCTURES' and prjElem.primary_resource_id=prs.resource_id and (prs.START_DATE_ACTIVE is null or prs.START_DATE_ACTIVE <= trunc(sysdate)) and (prs.END_DATE_ACTIVE is null or prs.END_DATE_ACTIVE>= trunc(sysdate)) )

SQL_Statement

select

prs.proj_resource_id as "Team Member Id",

prs.project_id as "Project Id",

per.display_name as "Person Name",

per.email as "Person Email"

from fusion.pjt_project_resource prs, fusion.pjt_prj_enterprise_resource_vl per, PJF_PROJ_ELEMENTS_B prjElem

where prs.resource_id = per.resource_id

and prs.project_role_id = 1

and prjElem.project_id=prs.project_id

and prjElem.object_type='PJF_STRUCTURES'

and prjElem.primary_resource_id=prs.resource_id

and (prs.START_DATE_ACTIVE is null or prs.START_DATE_ACTIVE <= trunc(sysdate)) and (prs.END_DATE_ACTIVE is null or prs.END_DATE_ACTIVE>= trunc(sysdate))

and not exists( select 1 from fusion.pjt_project_resource prs2

where prs.project_id=prs2.project_id

and prs.resource_id=prs2.resource_id

and prs2.project_role_id=1

and prs2.proj_resource_id>prs.proj_resource_id)

UNION

Select

ProjectPartyEO.project_party_id as "Team Member Id",

ProjectPartyEO.project_id as "Project Id",

PersonNameDPEO.display_name as "Person Name",

EmailAddressPEO.EMAIL_ADDRESS as "Person Email"

FROM PJF_PROJECT_PARTIES ProjectPartyEO, PER_PERSON_NAMES_F_V PersonNameDPEO, PER_ALL_PEOPLE_F PersonDPEO, PER_EMAIL_ADDRESSES EmailAddressPEO

where ProjectPartyEO.resource_source_id = PersonNameDPEO.PERSON_ID

and ProjectPartyEO.resource_source_id = PersonDPEO.PERSON_ID

and ProjectPartyEO.PROJECT_ROLE_ID=1

and PersonDPEO.PRIMARY_EMAIL_ID = EmailAddressPEO.EMAIL_ADDRESS_ID(+)

and PersonDPEO.effective_start_date <= trunc(sysdate) and (PersonDPEO.Effective_end_date is null or PersonDPEO.Effective_end_date>= trunc(sysdate))

and PersonNameDPEO.effective_start_date <= trunc(sysdate) and (PersonNameDPEO.Effective_end_date is null or PersonNameDPEO.Effective_end_date>= trunc(sysdate))

and ProjectPartyEO.START_DATE_ACTIVE <= trunc(sysdate) and (ProjectPartyEO.END_DATE_ACTIVE is null or ProjectPartyEO.END_DATE_ACTIVE>= trunc(sysdate))

and (EmailAddressPEO.DATE_FROM(+) is null or EmailAddressPEO.DATE_FROM (+)<= trunc(sysdate)) and (EmailAddressPEO.DATE_TO(+) is null or EmailAddressPEO.DATE_TO(+)>= trunc(sysdate))

and not exists (select 1 from fusion.pjt_project_resource prs, PJF_PROJ_ELEMENTS_B prjElem

where ProjectPartyEO.project_id = prjElem.project_id

and ProjectPartyEO.project_id = prs.project_id

and prs.project_role_id = 1

and prs.project_id=prjElem.project_id

and prjElem.object_type='PJF_STRUCTURES'

and prjElem.primary_resource_id=prs.resource_id

and (prs.START_DATE_ACTIVE is null or prs.START_DATE_ACTIVE <= trunc(sysdate)) and (prs.END_DATE_ACTIVE is null or prs.END_DATE_ACTIVE>= trunc(sysdate))

)