VIEW: APPS.PA_PROJ_MEMBERS_VIEW

Source

View Text - Preformatted

SELECT proj.org_id, proj.project_id, proj.name, proj.segment1, proj.start_date, proj.completion_date, ps.project_status_name, proj.project_status_code, fnd.meaning, proj.project_type, proj.description, org.organization_id, org.name, player.person_id, people.full_name, proj.distribution_rule, roles.project_role_type, roles.meaning FROM pa_projects proj, hr_organization_units org, per_people_f people, pa_project_players player, pa_project_role_types roles, pa_project_statuses ps, fnd_lookups fnd WHERE proj.carrying_out_organization_id = org.organization_id and proj.project_id = player.project_id and player.person_id = people.person_id and trunc(sysdate) <= people.effective_end_date and ((people.employee_number is not null and people.current_employee_flag = 'Y') or (people.npw_number is not null and people.current_npw_flag = 'Y')) and player.project_role_type = roles.project_role_type and sysdate <= nvl(player.end_date_active,sysdate+1) and ps.project_status_code = proj.project_status_code and fnd.lookup_type = 'YES_NO' and fnd.lookup_code = proj.public_sector_flag and ps.status_type = 'PROJECT' and trunc(sysdate) between trunc(people.effective_start_date) and trunc(people.effective_end_date)

View Text - HTML Formatted

SELECT PROJ.ORG_ID
, PROJ.PROJECT_ID
, PROJ.NAME
, PROJ.SEGMENT1
, PROJ.START_DATE
, PROJ.COMPLETION_DATE
, PS.PROJECT_STATUS_NAME
, PROJ.PROJECT_STATUS_CODE
, FND.MEANING
, PROJ.PROJECT_TYPE
, PROJ.DESCRIPTION
, ORG.ORGANIZATION_ID
, ORG.NAME
, PLAYER.PERSON_ID
, PEOPLE.FULL_NAME
, PROJ.DISTRIBUTION_RULE
, ROLES.PROJECT_ROLE_TYPE
, ROLES.MEANING
FROM PA_PROJECTS PROJ
, HR_ORGANIZATION_UNITS ORG
, PER_PEOPLE_F PEOPLE
, PA_PROJECT_PLAYERS PLAYER
, PA_PROJECT_ROLE_TYPES ROLES
, PA_PROJECT_STATUSES PS
, FND_LOOKUPS FND
WHERE PROJ.CARRYING_OUT_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PROJ.PROJECT_ID = PLAYER.PROJECT_ID
AND PLAYER.PERSON_ID = PEOPLE.PERSON_ID
AND TRUNC(SYSDATE) <= PEOPLE.EFFECTIVE_END_DATE
AND ((PEOPLE.EMPLOYEE_NUMBER IS NOT NULL
AND PEOPLE.CURRENT_EMPLOYEE_FLAG = 'Y') OR (PEOPLE.NPW_NUMBER IS NOT NULL
AND PEOPLE.CURRENT_NPW_FLAG = 'Y'))
AND PLAYER.PROJECT_ROLE_TYPE = ROLES.PROJECT_ROLE_TYPE
AND SYSDATE <= NVL(PLAYER.END_DATE_ACTIVE
, SYSDATE+1)
AND PS.PROJECT_STATUS_CODE = PROJ.PROJECT_STATUS_CODE
AND FND.LOOKUP_TYPE = 'YES_NO'
AND FND.LOOKUP_CODE = PROJ.PUBLIC_SECTOR_FLAG
AND PS.STATUS_TYPE = 'PROJECT'
AND TRUNC(SYSDATE) BETWEEN TRUNC(PEOPLE.EFFECTIVE_START_DATE)
AND TRUNC(PEOPLE.EFFECTIVE_END_DATE)