VIEW: APPS.PA_PROJECT_LISTS_USER_V

Source

View Text - Preformatted

SELECT PPA.PROJECT_ID , PPA.NAME , PPA.SEGMENT1 , PPA.PROJECT_TYPE , PPA.CARRYING_OUT_ORGANIZATION_ID , HOU.NAME , PUBLIC_SECTOR_FLAG , FL.MEANING , 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 , to_number(NULL) , PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID) , to_number(NULL) , 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.location_id , PL.country_code , FTV.territory_short_name , PL.region , PL.city , PPA.probability_member_id , PRO.probability_percentage , PPA.expected_approval_date , PPA.project_value , PPT.service_type_code , PR.PROGRESS_STATUS_CODE , PR.PROGRESS_STATUS_NAME , PR.STATUS_ICON_IND , PR.STATUS_ICON_ACTIVE_IND , PR.REPORT_END_DATE , PPA.record_version_number , decode( (pa_security_pvt.check_user_privilege( 'PA_MY_PROJ_MAIN_TAB_PSI_COL', 'PA_PROJECTS',ppa.project_id)), 'T','Y', 'F', 'N', 'N') , PSI.PROJFUNC_CURRENCY_CODE , PSI.COLUMN4 , PSI.COLUMN5 , PSI.COLUMN6 , PSI.COLUMN7 , PSI.COLUMN8 , PSI.COLUMN9 , PSI.COLUMN10 , PSI.COLUMN11 , PSI.COLUMN12 , PSI.COLUMN13 , PSI.COLUMN14 , PSI.COLUMN15 , PSI.COLUMN16 , PSI.COLUMN17 , PSI.COLUMN18 , PSI.COLUMN19 , PSI.COLUMN20 , PSI.COLUMN21 , PSI.COLUMN22 , PSI.COLUMN23 , PSI.COLUMN24 , PSI.COLUMN25 , PSI.COLUMN26 , PSI.COLUMN27 , PSI.COLUMN28 , PSI.COLUMN29 , PSI.COLUMN30 , PSI.COLUMN31 , PSI.COLUMN32 , PSI.COLUMN33 , ppoa.opportunity_value , ppoa.opp_value_currency_code , ppoa.projfunc_opp_value , ppa.projfunc_currency_code , ppoa.project_opp_value , pr.report_type_name , pr.report_type_id , ppa.security_level , pal.meaning , ppa.long_name , PA_PROJECT_DATES_UTILS.get_project_start_date(PPA.PROJECT_ID) , PA_PROJECT_DATES_UTILS.get_project_finish_date(PPA.PROJECT_ID) , ppa.baseline_start_date , ppa.baseline_finish_date , ppa.target_start_date , ppa.target_finish_date , priority.meaning FROM PA_PROJECTS_ALL PPA, PA_STATUS_PROJ_LIST_V PSI, HR_ALL_ORGANIZATION_UNITS HOU , PA_PROJECT_STATUSES PPS , PA_PROBABILITY_MEMBERS PRO , PER_ALL_PEOPLE_F PEOPLE, PA_PROJECT_PARTIES PARTIES, PA_LOCATIONS PL , FND_TERRITORIES_VL FTV , PA_PROJECT_TYPES_ALL PPT , FND_LOOKUPS FL , PA_PROJ_CURR_STATUS_REP_V PR , pa_project_opp_attrs ppoa , pa_lookups PAL , (select PA_SECURITY_PVT.get_grantee_key grantee_key, decode(employee_id, null,'Y','N') external_user from fnd_user WHERE user_id = FND_GLOBAL.user_id), pa_lookups priority WHERE PPA.TEMPLATE_FLAG = 'N' AND PPA.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID AND PPA.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE AND PPA.PROBABILITY_MEMBER_ID = PRO.PROBABILITY_MEMBER_ID (+) AND PPA.LOCATION_ID = PL.LOCATION_ID (+) AND PL.COUNTRY_CODE = FTV.TERRITORY_CODE (+) AND PPA.PROJECT_TYPE = PPT.project_type AND PPA.ORG_ID = PPT.ORG_ID AND PPA.PUBLIC_SECTOR_FLAG = FL.LOOKUP_CODE AND FL.LOOKUP_TYPE = 'YES_NO' 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 + 1) AND PARTIES.RESOURCE_SOURCE_ID = PEOPLE.PERSON_ID (+) AND PPA.PROJECT_ID = PR.PROJECT_ID (+) AND SYSDATE BETWEEN PEOPLE.EFFECTIVE_START_DATE (+) AND PEOPLE.EFFECTIVE_END_DATE (+) AND PPA.PROJECT_ID = PSI.PROJECT_ID AND PAL.lookup_type = 'PA_PROJECT_ACCESS_LEVEL' AND PAL.lookup_code = to_char(ppa.security_level) AND ((ppa.security_level = 1 and external_user = 'N') OR exists( select '1' from pa_project_parties where project_id = ppa.project_id and ( (resource_source_id=fnd_global.employee_id and resource_type_id = 101) OR ( resource_type_id = 112 and resource_source_id = (select person_party_id from fnd_user where user_id = fnd_global.user_id) ) )) OR exists( select '1' from fnd_grants fg, fnd_objects fo where FG.GRANTEE_KEY = 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) )) and ppa.project_id = ppoa.project_id AND priority.lookup_type (+) = 'PA_PROJECT_PRIORITY_CODE' AND priority.lookup_code (+) = ppa.priority_code

View Text - HTML Formatted

SELECT PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PPA.PROJECT_TYPE
, PPA.CARRYING_OUT_ORGANIZATION_ID
, HOU.NAME
, PUBLIC_SECTOR_FLAG
, FL.MEANING
, 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
, TO_NUMBER(NULL)
, PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID)
, TO_NUMBER(NULL)
, 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.LOCATION_ID
, PL.COUNTRY_CODE
, FTV.TERRITORY_SHORT_NAME
, PL.REGION
, PL.CITY
, PPA.PROBABILITY_MEMBER_ID
, PRO.PROBABILITY_PERCENTAGE
, PPA.EXPECTED_APPROVAL_DATE
, PPA.PROJECT_VALUE
, PPT.SERVICE_TYPE_CODE
, PR.PROGRESS_STATUS_CODE
, PR.PROGRESS_STATUS_NAME
, PR.STATUS_ICON_IND
, PR.STATUS_ICON_ACTIVE_IND
, PR.REPORT_END_DATE
, PPA.RECORD_VERSION_NUMBER
, DECODE( (PA_SECURITY_PVT.CHECK_USER_PRIVILEGE( 'PA_MY_PROJ_MAIN_TAB_PSI_COL'
, 'PA_PROJECTS'
, PPA.PROJECT_ID))
, 'T'
, 'Y'
, 'F'
, 'N'
, 'N')
, PSI.PROJFUNC_CURRENCY_CODE
, PSI.COLUMN4
, PSI.COLUMN5
, PSI.COLUMN6
, PSI.COLUMN7
, PSI.COLUMN8
, PSI.COLUMN9
, PSI.COLUMN10
, PSI.COLUMN11
, PSI.COLUMN12
, PSI.COLUMN13
, PSI.COLUMN14
, PSI.COLUMN15
, PSI.COLUMN16
, PSI.COLUMN17
, PSI.COLUMN18
, PSI.COLUMN19
, PSI.COLUMN20
, PSI.COLUMN21
, PSI.COLUMN22
, PSI.COLUMN23
, PSI.COLUMN24
, PSI.COLUMN25
, PSI.COLUMN26
, PSI.COLUMN27
, PSI.COLUMN28
, PSI.COLUMN29
, PSI.COLUMN30
, PSI.COLUMN31
, PSI.COLUMN32
, PSI.COLUMN33
, PPOA.OPPORTUNITY_VALUE
, PPOA.OPP_VALUE_CURRENCY_CODE
, PPOA.PROJFUNC_OPP_VALUE
, PPA.PROJFUNC_CURRENCY_CODE
, PPOA.PROJECT_OPP_VALUE
, PR.REPORT_TYPE_NAME
, PR.REPORT_TYPE_ID
, PPA.SECURITY_LEVEL
, PAL.MEANING
, PPA.LONG_NAME
, PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE(PPA.PROJECT_ID)
, PA_PROJECT_DATES_UTILS.GET_PROJECT_FINISH_DATE(PPA.PROJECT_ID)
, PPA.BASELINE_START_DATE
, PPA.BASELINE_FINISH_DATE
, PPA.TARGET_START_DATE
, PPA.TARGET_FINISH_DATE
, PRIORITY.MEANING
FROM PA_PROJECTS_ALL PPA
, PA_STATUS_PROJ_LIST_V PSI
, HR_ALL_ORGANIZATION_UNITS HOU
, PA_PROJECT_STATUSES PPS
, PA_PROBABILITY_MEMBERS PRO
, PER_ALL_PEOPLE_F PEOPLE
, PA_PROJECT_PARTIES PARTIES
, PA_LOCATIONS PL
, FND_TERRITORIES_VL FTV
, PA_PROJECT_TYPES_ALL PPT
, FND_LOOKUPS FL
, PA_PROJ_CURR_STATUS_REP_V PR
, PA_PROJECT_OPP_ATTRS PPOA
, PA_LOOKUPS PAL
, (SELECT PA_SECURITY_PVT.GET_GRANTEE_KEY GRANTEE_KEY
, DECODE(EMPLOYEE_ID
, NULL
, 'Y'
, 'N') EXTERNAL_USER
FROM FND_USER
WHERE USER_ID = FND_GLOBAL.USER_ID)
, PA_LOOKUPS PRIORITY
WHERE PPA.TEMPLATE_FLAG = 'N'
AND PPA.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND PPA.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE
AND PPA.PROBABILITY_MEMBER_ID = PRO.PROBABILITY_MEMBER_ID (+)
AND PPA.LOCATION_ID = PL.LOCATION_ID (+)
AND PL.COUNTRY_CODE = FTV.TERRITORY_CODE (+)
AND PPA.PROJECT_TYPE = PPT.PROJECT_TYPE
AND PPA.ORG_ID = PPT.ORG_ID
AND PPA.PUBLIC_SECTOR_FLAG = FL.LOOKUP_CODE
AND FL.LOOKUP_TYPE = 'YES_NO'
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 + 1)
AND PARTIES.RESOURCE_SOURCE_ID = PEOPLE.PERSON_ID (+)
AND PPA.PROJECT_ID = PR.PROJECT_ID (+)
AND SYSDATE BETWEEN PEOPLE.EFFECTIVE_START_DATE (+)
AND PEOPLE.EFFECTIVE_END_DATE (+)
AND PPA.PROJECT_ID = PSI.PROJECT_ID
AND PAL.LOOKUP_TYPE = 'PA_PROJECT_ACCESS_LEVEL'
AND PAL.LOOKUP_CODE = TO_CHAR(PPA.SECURITY_LEVEL)
AND ((PPA.SECURITY_LEVEL = 1
AND EXTERNAL_USER = 'N') OR EXISTS( SELECT '1'
FROM PA_PROJECT_PARTIES
WHERE PROJECT_ID = PPA.PROJECT_ID
AND ( (RESOURCE_SOURCE_ID=FND_GLOBAL.EMPLOYEE_ID
AND RESOURCE_TYPE_ID = 101) OR ( RESOURCE_TYPE_ID = 112
AND RESOURCE_SOURCE_ID = (SELECT PERSON_PARTY_ID
FROM FND_USER
WHERE USER_ID = FND_GLOBAL.USER_ID) ) )) OR EXISTS( SELECT '1'
FROM FND_GRANTS FG
, FND_OBJECTS FO
WHERE FG.GRANTEE_KEY = 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) ))
AND PPA.PROJECT_ID = PPOA.PROJECT_ID
AND PRIORITY.LOOKUP_TYPE (+) = 'PA_PROJECT_PRIORITY_CODE'
AND PRIORITY.LOOKUP_CODE (+) = PPA.PRIORITY_CODE