VIEW: APPS.PA_PROGRESS_REPORTS_MAIN_V

Source

View Text - Preformatted

SELECT PPDVer.Object_Type, PPDVer.Object_Id, PPDVer.Page_Type_Code, PPDVer.Page_Id, PPDVer.Report_Type_Id, RTyp.Name, POPL.Next_Reporting_Date, PPDVer.Version_Id, PPDVer.Report_Status_Code, PPS1.Project_Status_Name Page_Status_Meaning, PPDVer.Progress_Status_Code, PPS2.Project_Status_Name Progress_Status_Meaning, PPS2.Status_icon_ind, PPS2.Status_icon_active_ind, PPDVer.Report_Start_Date, PPDVer.Report_End_Date, PPDVer.Reported_By, pa_resource_utils.get_resource_name(PPDVer.Reported_By), PPDVER.Current_Flag, PPDVer.Overview, PPDVer.Comments, PPDVer.Published_Date, PPDVer.Canceled_Date, PPDVer.Record_Version_Number, pa_progress_report_utils.Check_Security_For_ProgRep(ppdver.object_type,ppdver.object_id,ppdver.report_type_id) report_access From Pa_progress_report_vers PPDVer, Pa_Project_Statuses PPS1, Pa_Project_Statuses PPS2, Pa_Report_Types RTyp, Pa_Object_Page_Layouts POPL WHERE PPDVER.object_Id = POPL.object_id and PPDVER.object_Type = POPL.object_type and PPDVER.report_Type_id = POPL.report_Type_id and PPDVer.Page_Type_Code = 'PPR' and PPDVer.Report_Type_Id = RTyp.Report_Type_Id and PPDVer.Report_Status_Code = PPS1.Project_Status_Code and PPDVer.Progress_Status_Code = PPS2.Project_Status_Code(+) and ( generation_method is null or generation_method = 'MANUAL') UNION Select POPL.Object_Type, POPL.Object_Id, POPL.Page_Type_Code, POPL.Page_Id, POPL.Report_Type_Id, RTyp.Name, POPL.Next_Reporting_Date, to_number(null) version_id, null Report_Status_Code, null Page_Status_Meaning, null Progress_Status_Code, null Progress_Status_Meaning, null Progress_Status_Icon, null Progress_Status_Active_Icon, to_date(null) Report_Start_Date, to_date(null) Report_End_Date, to_number(null) Reported_By, null Reported_By_Name, 'Y' current_flag, null Overview, null Comments, to_date(null) Published_Date, to_date(null) Canceled_Date, to_number(null) Record_Version_Number, pa_progress_report_utils.Check_Security_For_ProgRep(popl.object_type,popl.object_id,popl.report_type_id) Report_Access From Pa_Report_Types RTyp, Pa_Object_Page_Layouts POPL Where POPL.page_type_code = 'PPR' and POPL.Report_Type_Id = RTyp.Report_Type_Id and (generation_method is null or generation_method = 'MANUAL') and not exists (select 'Y' from pa_progress_report_vers pprv where pprv.object_id = POPL.object_Id and pprv.object_type = POPL.object_type and pprv.Report_Type_Id = POPL.Report_Type_Id and pprv.report_status_code = 'PROGRESS_REPORT_PUBLISHED')

View Text - HTML Formatted

SELECT PPDVER.OBJECT_TYPE
, PPDVER.OBJECT_ID
, PPDVER.PAGE_TYPE_CODE
, PPDVER.PAGE_ID
, PPDVER.REPORT_TYPE_ID
, RTYP.NAME
, POPL.NEXT_REPORTING_DATE
, PPDVER.VERSION_ID
, PPDVER.REPORT_STATUS_CODE
, PPS1.PROJECT_STATUS_NAME PAGE_STATUS_MEANING
, PPDVER.PROGRESS_STATUS_CODE
, PPS2.PROJECT_STATUS_NAME PROGRESS_STATUS_MEANING
, PPS2.STATUS_ICON_IND
, PPS2.STATUS_ICON_ACTIVE_IND
, PPDVER.REPORT_START_DATE
, PPDVER.REPORT_END_DATE
, PPDVER.REPORTED_BY
, PA_RESOURCE_UTILS.GET_RESOURCE_NAME(PPDVER.REPORTED_BY)
, PPDVER.CURRENT_FLAG
, PPDVER.OVERVIEW
, PPDVER.COMMENTS
, PPDVER.PUBLISHED_DATE
, PPDVER.CANCELED_DATE
, PPDVER.RECORD_VERSION_NUMBER
, PA_PROGRESS_REPORT_UTILS.CHECK_SECURITY_FOR_PROGREP(PPDVER.OBJECT_TYPE
, PPDVER.OBJECT_ID
, PPDVER.REPORT_TYPE_ID) REPORT_ACCESS
FROM PA_PROGRESS_REPORT_VERS PPDVER
, PA_PROJECT_STATUSES PPS1
, PA_PROJECT_STATUSES PPS2
, PA_REPORT_TYPES RTYP
, PA_OBJECT_PAGE_LAYOUTS POPL
WHERE PPDVER.OBJECT_ID = POPL.OBJECT_ID
AND PPDVER.OBJECT_TYPE = POPL.OBJECT_TYPE
AND PPDVER.REPORT_TYPE_ID = POPL.REPORT_TYPE_ID
AND PPDVER.PAGE_TYPE_CODE = 'PPR'
AND PPDVER.REPORT_TYPE_ID = RTYP.REPORT_TYPE_ID
AND PPDVER.REPORT_STATUS_CODE = PPS1.PROJECT_STATUS_CODE
AND PPDVER.PROGRESS_STATUS_CODE = PPS2.PROJECT_STATUS_CODE(+)
AND ( GENERATION_METHOD IS NULL OR GENERATION_METHOD = 'MANUAL') UNION SELECT POPL.OBJECT_TYPE
, POPL.OBJECT_ID
, POPL.PAGE_TYPE_CODE
, POPL.PAGE_ID
, POPL.REPORT_TYPE_ID
, RTYP.NAME
, POPL.NEXT_REPORTING_DATE
, TO_NUMBER(NULL) VERSION_ID
, NULL REPORT_STATUS_CODE
, NULL PAGE_STATUS_MEANING
, NULL PROGRESS_STATUS_CODE
, NULL PROGRESS_STATUS_MEANING
, NULL PROGRESS_STATUS_ICON
, NULL PROGRESS_STATUS_ACTIVE_ICON
, TO_DATE(NULL) REPORT_START_DATE
, TO_DATE(NULL) REPORT_END_DATE
, TO_NUMBER(NULL) REPORTED_BY
, NULL REPORTED_BY_NAME
, 'Y' CURRENT_FLAG
, NULL OVERVIEW
, NULL COMMENTS
, TO_DATE(NULL) PUBLISHED_DATE
, TO_DATE(NULL) CANCELED_DATE
, TO_NUMBER(NULL) RECORD_VERSION_NUMBER
, PA_PROGRESS_REPORT_UTILS.CHECK_SECURITY_FOR_PROGREP(POPL.OBJECT_TYPE
, POPL.OBJECT_ID
, POPL.REPORT_TYPE_ID) REPORT_ACCESS
FROM PA_REPORT_TYPES RTYP
, PA_OBJECT_PAGE_LAYOUTS POPL
WHERE POPL.PAGE_TYPE_CODE = 'PPR'
AND POPL.REPORT_TYPE_ID = RTYP.REPORT_TYPE_ID
AND (GENERATION_METHOD IS NULL OR GENERATION_METHOD = 'MANUAL')
AND NOT EXISTS (SELECT 'Y'
FROM PA_PROGRESS_REPORT_VERS PPRV
WHERE PPRV.OBJECT_ID = POPL.OBJECT_ID
AND PPRV.OBJECT_TYPE = POPL.OBJECT_TYPE
AND PPRV.REPORT_TYPE_ID = POPL.REPORT_TYPE_ID
AND PPRV.REPORT_STATUS_CODE = 'PROGRESS_REPORT_PUBLISHED')