VIEW: APPS.PA_PROJ_INFO_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, proj.project_currency_code, proj.project_rate_date, proj.project_rate_type, proj.allow_cross_charge_flag 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 ( people.current_employee_flag='Y' OR people.current_npw_flag='Y') and trunc(sysdate) between people.effective_start_date and people.effective_end_date and player.project_role_type = roles.project_role_type and roles.project_role_type = 'PROJECT MANAGER' and trunc(sysdate) between player.start_date_active and 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' UNION 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, to_number(null), null, proj.distribution_rule, null, null, proj.project_currency_code, proj.project_rate_date, proj.project_rate_type, proj.allow_cross_charge_flag FROM pa_projects proj, hr_organization_units org, pa_project_statuses ps, fnd_lookups fnd where proj.carrying_out_organization_id = org.organization_id and NOT EXISTS ( SELECT NULL FROM pa_project_players pp , per_people_f people WHERE pp.project_id = proj.project_id AND pp.person_id = people.person_id AND (people.current_employee_flag = 'Y' OR people.current_npw_flag = 'Y') AND TRUNC(sysdate) BETWEEN people.effective_start_date AND people.effective_end_date AND pp.project_role_type = 'PROJECT MANAGER' AND trunc(sysdate) BETWEEN pp.start_date_active AND nvl(pp.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'

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
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJECT_RATE_DATE
, PROJ.PROJECT_RATE_TYPE
, PROJ.ALLOW_CROSS_CHARGE_FLAG
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 ( PEOPLE.CURRENT_EMPLOYEE_FLAG='Y' OR PEOPLE.CURRENT_NPW_FLAG='Y')
AND TRUNC(SYSDATE) BETWEEN PEOPLE.EFFECTIVE_START_DATE
AND PEOPLE.EFFECTIVE_END_DATE
AND PLAYER.PROJECT_ROLE_TYPE = ROLES.PROJECT_ROLE_TYPE
AND ROLES.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
AND TRUNC(SYSDATE) BETWEEN PLAYER.START_DATE_ACTIVE
AND 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' UNION 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
, TO_NUMBER(NULL)
, NULL
, PROJ.DISTRIBUTION_RULE
, NULL
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJECT_RATE_DATE
, PROJ.PROJECT_RATE_TYPE
, PROJ.ALLOW_CROSS_CHARGE_FLAG
FROM PA_PROJECTS PROJ
, HR_ORGANIZATION_UNITS ORG
, PA_PROJECT_STATUSES PS
, FND_LOOKUPS FND
WHERE PROJ.CARRYING_OUT_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND NOT EXISTS ( SELECT NULL
FROM PA_PROJECT_PLAYERS PP
, PER_PEOPLE_F PEOPLE
WHERE PP.PROJECT_ID = PROJ.PROJECT_ID
AND PP.PERSON_ID = PEOPLE.PERSON_ID
AND (PEOPLE.CURRENT_EMPLOYEE_FLAG = 'Y' OR PEOPLE.CURRENT_NPW_FLAG = 'Y')
AND TRUNC(SYSDATE) BETWEEN PEOPLE.EFFECTIVE_START_DATE
AND PEOPLE.EFFECTIVE_END_DATE
AND PP.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
AND TRUNC(SYSDATE) BETWEEN PP.START_DATE_ACTIVE
AND NVL(PP.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'