PRJ_TEAM_MEMBERS_F_V

Details

• Schema: FUSION

• Object owner: PJF

• Object type: VIEW

Columns

Name
Team Member Id Project Id Person Id Person Name Person Email Project Role Project Role Id Start Date Finish Date Track Time Resource Planning Bill Rate Resource Planning Cost Rate Resource Allocation Percentage Resource Assignment Effort Assignment Type Code Billable Percent Billable Percent Reason Code CREATED_BY CREATION_DATE LAST_UPDATE_DATE LAST_UPDATED_BY

Query

SQL_Statement

(Select ProjectPartyEO.project_party_id as "Team Member Id", ProjectPartyEO.project_id as "Project Id", ProjectPartyEO.resource_source_id as "Person Id", PersonNameDPEO.display_name as "Person Name", EmailAddressPEO.EMAIL_ADDRESS as "Person Email", ProjectRoleTypePEO.PROJECT_ROLE_NAME as "Project Role", ProjectRoleTypePEO.PROJECT_ROLE_ID as "Project Role Id", ProjectPartyEO.start_date_active as "Start Date", ProjectPartyEO.end_date_active as "Finish Date", ProjectPartyEO.PJS_TRACK_TIME as "Track Time", PjtProjectResource.LABOR_BILL_RATE as "Resource Planning Bill Rate", PjtProjectResource.LABOR_COST_RATE as "Resource Planning Cost Rate", PjtProjectResource.ALLOCATION as "Resource Allocation Percentage", PjtProjectResource.LABOR_EFFORT as "Resource Assignment Effort", PjtProjectResource.ASSIGNMENT_TYPE as "Assignment Type Code", PjtProjectResource.BILLABLE_PERCENT as "Billable Percent", PjtProjectResource.BILLABLE_PERCENT_REASON_CODE as "Billable Percent Reason Code", ProjectPartyEO.CREATED_BY, ProjectPartyEO.CREATION_DATE, ProjectPartyEO.LAST_UPDATE_DATE, ProjectPartyEO.LAST_UPDATED_BY FROM PJF_PROJECT_PARTIES ProjectPartyEO, PJF_PROJ_ROLE_TYPES_VL ProjectRoleTypePEO, PER_PERSON_NAMES_F_V PersonNameDPEO, PER_ALL_PEOPLE_F PersonDPEO, PER_EMAIL_ADDRESSES EmailAddressPEO, PJT_PROJECT_RESOURCE PjtProjectResource where ProjectPartyEO.PROJECT_ROLE_ID = ProjectRoleTypePEO.PROJECT_ROLE_ID and ProjectPartyEO.resource_source_id = PersonNameDPEO.PERSON_ID and ProjectPartyEO.resource_source_id = PersonDPEO.PERSON_ID 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 (EmailAddressPEO.DATE_FROM(+) is null or EmailAddressPEO.DATE_FROM(+) <= trunc(sysdate)) and (EmailAddressPEO.DATE_TO(+) is null or EmailAddressPEO.DATE_TO(+)>= trunc(sysdate)) AND ProjectPartyEO.resource_source_id = PjtProjectResource.person_id(+) AND ProjectPartyEO.project_role_id = PjtProjectResource.project_role_id(+) AND ProjectPartyEO.start_date_active = PjtProjectResource.start_date_active(+) AND ProjectPartyEO.Project_id = PjtProjectResource.project_id(+) AND 'N' = pjf_proj_utils.get_feature_enabled('ORA_PJF_SINGLE_SOURCE_TM_PRS') ) union (select prs.proj_resource_id as "Team Member Id", prs.project_id as "Project Id", per.person_id as "Person Id", per.display_name as "Person Name", per.email as "Person Email", prole.name as "Project Role", prole.PROJECT_ROLE_ID as "Project Role Id", prs.START_DATE_ACTIVE as "Start Date", prs.END_DATE_ACTIVE as "Finish Date", prs.TRACk_TIME_FLAG as "Track Time", prs.LABOR_BILL_RATE as "Resource Planning Bill Rate", prs.LABOR_COST_RATE as "Resource Planning Cost Rate", prs.ALLOCATION as "Resource Allocation Percentage", prs.LABOR_EFFORT as "Resource Assignment Effort", prs.ASSIGNMENT_TYPE as "Assignment Type Code", prs.BILLABLE_PERCENT as "Billable Percent", prs.BILLABLE_PERCENT_REASON_CODE as "Billable Percent Reason Code", prs.CREATED_BY, prs.CREATION_DATE, prs.LAST_UPDATE_DATE, prs.LAST_UPDATED_BY from fusion.pjt_project_resource prs, fusion.pjt_prj_enterprise_resource_vl per, fusion.pjt_project_roles_vl prole where prs.resource_id = per.resource_id and prole.project_role_id = prs.project_role_id and not exists (SELECT 1 FROM PJF_PROJECT_PARTIES ProjectPartyEO WHERE ProjectPartyEO.resource_source_id = per.person_Id AND ProjectPartyEO.project_role_id = prs.project_role_id AND ProjectPartyEO.start_date_active = prs.start_date_active AND ProjectPartyEO.Project_id = prs.project_id AND 'N' = pjf_proj_utils.get_feature_enabled('ORA_PJF_SINGLE_SOURCE_TM_PRS') ) )