VIEW: APPS.PA_PROJECT_LISTS_PORTLET_V

Source

View Text - Preformatted

SELECT PPL.PROJECT_ID , PPL.NAME , PPL.SEGMENT1 , PPL.PROJECT_TYPE , PPL.CARRYING_OUT_ORGANIZATION_ID , PPL.NAME , PPL.PUBLIC_SECTOR_FLAG , PPL.PROJECT_STATUS_CODE , PPL.PROJECT_STATUS_NAME , PPL.DESCRIPTION , PPL.START_DATE , PPL.COMPLETION_DATE , PPL.CLOSED_DATE , PPL.DISTRIBUTION_RULE , PPL.TEMPLATE_FLAG , PPL.CREATED_FROM_PROJECT_ID , PPL.TEMPLATE_START_DATE_ACTIVE , PPL.TEMPLATE_END_DATE_ACTIVE , PPL.SUMMARY_FLAG , PPL.ENABLED_FLAG , PPL.ORG_ID , PPL.PROJECT_CURRENCY_CODE , PPL.PERSON_ID , PPL.PERSON_NAME , PPL.CUSTOMER_ID , PPL.CUSTOMER_NAME , PPL.resource_id , PPL.pm_product_code , PPL.pm_project_reference , PPL.actual_start_date , PPL.actual_finish_date , PPL.early_start_date , PPL.early_finish_date , PPL.late_start_date , PPL.late_finish_date , PPL.scheduled_start_date , PPL.scheduled_finish_date , PPL.location_id , PPL.country_code , PPL.country_name , PPL.region , PPL.city , PPL.probability_member_id , PPL.probability_percentage , PPL.expected_approval_date , PPL.project_value , PPR.progress_status_code , PPR.meaning , PPR.progress_asof_date , PPL.record_version_number FROM PA_PROJECT_LISTS_USER_V PPL , (SELECT PPR2.PROJECT_ID, PPR2.PROGRESS_STATUS_CODE, PL.MEANING, PPR2.PROGRESS_ASOF_DATE FROM PA_PROJ_PROGRESS_REPORTS PPR2, PA_LOOKUPS PL WHERE PPR2.TASK_ID = 0 AND PPR2.REPORT_STATUS = 'PUBLISHED' AND PPR2.PROGRESS_REPORT_ID = (SELECT MAX(PPR3.PROGRESS_REPORT_ID) FROM PA_PROJ_PROGRESS_REPORTS PPR3 WHERE PPR3.PROJECT_ID = PPR2.PROJECT_ID AND PPR3.TASK_ID = 0 AND PPR3.REPORT_STATUS = 'PUBLISHED') AND PL.LOOKUP_TYPE = 'PA_XC_PROGRESS_STATUS' AND PL.LOOKUP_CODE = PPR2.PROGRESS_STATUS_CODE) PPR, PA_PROJECT_STATUSES PPS WHERE PPL.PROJECT_ID = PPR.PROJECT_ID (+) AND PPL.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE AND PPS.PROJECT_SYSTEM_STATUS_CODE IN ('APPROVED', 'UNAPPROVED', 'SUBMITTED', 'UPGRADED')

View Text - HTML Formatted

SELECT PPL.PROJECT_ID
, PPL.NAME
, PPL.SEGMENT1
, PPL.PROJECT_TYPE
, PPL.CARRYING_OUT_ORGANIZATION_ID
, PPL.NAME
, PPL.PUBLIC_SECTOR_FLAG
, PPL.PROJECT_STATUS_CODE
, PPL.PROJECT_STATUS_NAME
, PPL.DESCRIPTION
, PPL.START_DATE
, PPL.COMPLETION_DATE
, PPL.CLOSED_DATE
, PPL.DISTRIBUTION_RULE
, PPL.TEMPLATE_FLAG
, PPL.CREATED_FROM_PROJECT_ID
, PPL.TEMPLATE_START_DATE_ACTIVE
, PPL.TEMPLATE_END_DATE_ACTIVE
, PPL.SUMMARY_FLAG
, PPL.ENABLED_FLAG
, PPL.ORG_ID
, PPL.PROJECT_CURRENCY_CODE
, PPL.PERSON_ID
, PPL.PERSON_NAME
, PPL.CUSTOMER_ID
, PPL.CUSTOMER_NAME
, PPL.RESOURCE_ID
, PPL.PM_PRODUCT_CODE
, PPL.PM_PROJECT_REFERENCE
, PPL.ACTUAL_START_DATE
, PPL.ACTUAL_FINISH_DATE
, PPL.EARLY_START_DATE
, PPL.EARLY_FINISH_DATE
, PPL.LATE_START_DATE
, PPL.LATE_FINISH_DATE
, PPL.SCHEDULED_START_DATE
, PPL.SCHEDULED_FINISH_DATE
, PPL.LOCATION_ID
, PPL.COUNTRY_CODE
, PPL.COUNTRY_NAME
, PPL.REGION
, PPL.CITY
, PPL.PROBABILITY_MEMBER_ID
, PPL.PROBABILITY_PERCENTAGE
, PPL.EXPECTED_APPROVAL_DATE
, PPL.PROJECT_VALUE
, PPR.PROGRESS_STATUS_CODE
, PPR.MEANING
, PPR.PROGRESS_ASOF_DATE
, PPL.RECORD_VERSION_NUMBER
FROM PA_PROJECT_LISTS_USER_V PPL
, (SELECT PPR2.PROJECT_ID
, PPR2.PROGRESS_STATUS_CODE
, PL.MEANING
, PPR2.PROGRESS_ASOF_DATE
FROM PA_PROJ_PROGRESS_REPORTS PPR2
, PA_LOOKUPS PL
WHERE PPR2.TASK_ID = 0
AND PPR2.REPORT_STATUS = 'PUBLISHED'
AND PPR2.PROGRESS_REPORT_ID = (SELECT MAX(PPR3.PROGRESS_REPORT_ID)
FROM PA_PROJ_PROGRESS_REPORTS PPR3
WHERE PPR3.PROJECT_ID = PPR2.PROJECT_ID
AND PPR3.TASK_ID = 0
AND PPR3.REPORT_STATUS = 'PUBLISHED')
AND PL.LOOKUP_TYPE = 'PA_XC_PROGRESS_STATUS'
AND PL.LOOKUP_CODE = PPR2.PROGRESS_STATUS_CODE) PPR
, PA_PROJECT_STATUSES PPS
WHERE PPL.PROJECT_ID = PPR.PROJECT_ID (+)
AND PPL.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE
AND PPS.PROJECT_SYSTEM_STATUS_CODE IN ('APPROVED'
, 'UNAPPROVED'
, 'SUBMITTED'
, 'UPGRADED')