VIEW: APPS.PA_PROJECTS_CUST_360_V

Source

View Text - Preformatted

SELECT PPA.PROJECT_ID , PPA.NAME , PPA.SEGMENT1 , PPA.PROJECT_TYPE , PPA.CARRYING_OUT_ORGANIZATION_ID , HOU.NAME , PPA.PUBLIC_SECTOR_FLAG , PPA.PROJECT_STATUS_CODE , PPS.PROJECT_STATUS_NAME , PPA.DESCRIPTION , PPA.START_DATE , PPA.COMPLETION_DATE , PPA.CLOSED_DATE , PPA.DISTRIBUTION_RULE , PPA.TEMPLATE_FLAG , PPA.CREATED_FROM_PROJECT_ID , PPA.TEMPLATE_START_DATE_ACTIVE , PPA.TEMPLATE_END_DATE_ACTIVE , PPA.SUMMARY_FLAG , PPA.ENABLED_FLAG , PPA.ORG_ID , PPA.PROJECT_CURRENCY_CODE , PARTIES.RESOURCE_SOURCE_ID , PEOPLE.FULL_NAME , PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER(PPA.PROJECT_ID) , PPA.pm_product_code , PPA.pm_project_reference , PPA.actual_start_date , PPA.actual_finish_date , PPA.early_start_date , PPA.early_finish_date , PPA.late_start_date , PPA.late_finish_date , PPA.scheduled_start_date , PPA.scheduled_finish_date , PPA.probability_member_id , PPA.expected_approval_date , PPA.project_value , PPA.record_version_number FROM PA_PROJECTS_ALL PPA, HR_ALL_ORGANIZATION_UNITS HOU , PA_PROJECT_STATUSES PPS , PER_ALL_PEOPLE_F PEOPLE, PA_PROJECT_PARTIES PARTIES, (select PA_SECURITY_PVT.get_grantee_key grantee_key, decode(employee_id,null,PA_UTILS.GET_PARTY_ID(user_id),employee_id) resource_source_id, decode(employee_id,null,112,101) resource_type_id from fnd_user WHERE user_id = FND_GLOBAL.user_id) login_user, (select FND_PROFILE.VALUE_SPECIFIC('PA_SUPER_PROJECT', FND_GLOBAL.user_id) super_user_edit from dual), (select FND_PROFILE.VALUE_SPECIFIC('PA_SUPER_PROJECT_VIEW', FND_GLOBAL.user_id) super_user_view from dual) WHERE PPA.TEMPLATE_FLAG = 'N' AND PPA.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID AND PPA.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE AND PARTIES.PROJECT_ROLE_ID (+) = 1 AND PARTIES.PROJECT_ID (+) = PPA.PROJECT_ID AND sysdate between PARTIES.start_date_active(+) AND NVL(PARTIES.end_date_active(+),sysdate) AND PARTIES.RESOURCE_SOURCE_ID = PEOPLE.PERSON_ID (+) AND SYSDATE BETWEEN PEOPLE.EFFECTIVE_START_DATE (+) AND PEOPLE.EFFECTIVE_END_DATE (+) AND ((PPA.security_level = 1 and login_user.resource_type_id= 101) OR (super_user_edit = 'Y' or super_user_view = 'Y') OR exists( select '1' from pa_project_parties where project_id = PPA.project_id and object_type = 'PA_PROJECTS' and object_id = PPA.project_id and resource_source_id = login_user.resource_source_id and resource_type_id = login_user.resource_type_id) OR exists( select '1' from fnd_grants fg, fnd_objects fo where FG.GRANTEE_KEY = login_user.grantee_key AND FG.GRANTEE_TYPE = 'USER' AND FG.INSTANCE_TYPE = 'INSTANCE' AND sysdate between fg.start_date and nvl( fg.end_date, sysdate + 1 ) AND FG.OBJECT_ID = FO.OBJECT_ID AND FO.OBJ_NAME = 'ORGANIZATION' AND PPA.CARRYING_OUT_ORGANIZATION_ID = to_number(FG.INSTANCE_PK1_VALUE) ))

View Text - HTML Formatted

SELECT PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PPA.PROJECT_TYPE
, PPA.CARRYING_OUT_ORGANIZATION_ID
, HOU.NAME
, PPA.PUBLIC_SECTOR_FLAG
, PPA.PROJECT_STATUS_CODE
, PPS.PROJECT_STATUS_NAME
, PPA.DESCRIPTION
, PPA.START_DATE
, PPA.COMPLETION_DATE
, PPA.CLOSED_DATE
, PPA.DISTRIBUTION_RULE
, PPA.TEMPLATE_FLAG
, PPA.CREATED_FROM_PROJECT_ID
, PPA.TEMPLATE_START_DATE_ACTIVE
, PPA.TEMPLATE_END_DATE_ACTIVE
, PPA.SUMMARY_FLAG
, PPA.ENABLED_FLAG
, PPA.ORG_ID
, PPA.PROJECT_CURRENCY_CODE
, PARTIES.RESOURCE_SOURCE_ID
, PEOPLE.FULL_NAME
, PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER(PPA.PROJECT_ID)
, PPA.PM_PRODUCT_CODE
, PPA.PM_PROJECT_REFERENCE
, PPA.ACTUAL_START_DATE
, PPA.ACTUAL_FINISH_DATE
, PPA.EARLY_START_DATE
, PPA.EARLY_FINISH_DATE
, PPA.LATE_START_DATE
, PPA.LATE_FINISH_DATE
, PPA.SCHEDULED_START_DATE
, PPA.SCHEDULED_FINISH_DATE
, PPA.PROBABILITY_MEMBER_ID
, PPA.EXPECTED_APPROVAL_DATE
, PPA.PROJECT_VALUE
, PPA.RECORD_VERSION_NUMBER
FROM PA_PROJECTS_ALL PPA
, HR_ALL_ORGANIZATION_UNITS HOU
, PA_PROJECT_STATUSES PPS
, PER_ALL_PEOPLE_F PEOPLE
, PA_PROJECT_PARTIES PARTIES
, (SELECT PA_SECURITY_PVT.GET_GRANTEE_KEY GRANTEE_KEY
, DECODE(EMPLOYEE_ID
, NULL
, PA_UTILS.GET_PARTY_ID(USER_ID)
, EMPLOYEE_ID) RESOURCE_SOURCE_ID
, DECODE(EMPLOYEE_ID
, NULL
, 112
, 101) RESOURCE_TYPE_ID
FROM FND_USER
WHERE USER_ID = FND_GLOBAL.USER_ID) LOGIN_USER
, (SELECT FND_PROFILE.VALUE_SPECIFIC('PA_SUPER_PROJECT'
, FND_GLOBAL.USER_ID) SUPER_USER_EDIT
FROM DUAL)
, (SELECT FND_PROFILE.VALUE_SPECIFIC('PA_SUPER_PROJECT_VIEW'
, FND_GLOBAL.USER_ID) SUPER_USER_VIEW
FROM DUAL)
WHERE PPA.TEMPLATE_FLAG = 'N'
AND PPA.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND PPA.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE
AND PARTIES.PROJECT_ROLE_ID (+) = 1
AND PARTIES.PROJECT_ID (+) = PPA.PROJECT_ID
AND SYSDATE BETWEEN PARTIES.START_DATE_ACTIVE(+)
AND NVL(PARTIES.END_DATE_ACTIVE(+)
, SYSDATE)
AND PARTIES.RESOURCE_SOURCE_ID = PEOPLE.PERSON_ID (+)
AND SYSDATE BETWEEN PEOPLE.EFFECTIVE_START_DATE (+)
AND PEOPLE.EFFECTIVE_END_DATE (+)
AND ((PPA.SECURITY_LEVEL = 1
AND LOGIN_USER.RESOURCE_TYPE_ID= 101) OR (SUPER_USER_EDIT = 'Y' OR SUPER_USER_VIEW = 'Y') OR EXISTS( SELECT '1'
FROM PA_PROJECT_PARTIES
WHERE PROJECT_ID = PPA.PROJECT_ID
AND OBJECT_TYPE = 'PA_PROJECTS'
AND OBJECT_ID = PPA.PROJECT_ID
AND RESOURCE_SOURCE_ID = LOGIN_USER.RESOURCE_SOURCE_ID
AND RESOURCE_TYPE_ID = LOGIN_USER.RESOURCE_TYPE_ID) OR EXISTS( SELECT '1'
FROM FND_GRANTS FG
, FND_OBJECTS FO
WHERE FG.GRANTEE_KEY = LOGIN_USER.GRANTEE_KEY
AND FG.GRANTEE_TYPE = 'USER'
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND SYSDATE BETWEEN FG.START_DATE
AND NVL( FG.END_DATE
, SYSDATE + 1 )
AND FG.OBJECT_ID = FO.OBJECT_ID
AND FO.OBJ_NAME = 'ORGANIZATION'
AND PPA.CARRYING_OUT_ORGANIZATION_ID = TO_NUMBER(FG.INSTANCE_PK1_VALUE) ))