VIEW: APPS.PA_FIN_STRUCTURE_VERSIONS_V

Source

View Text - Preformatted

SELECT ele.proj_element_id, evr.element_version_id, stc.pev_structure_id, evr.project_id, ele.element_number, ele.name, stc.version_number, stc.name, stc.description, stc.effective_date, stc.status_code, st1.project_status_name, stc.lock_status_code, st2.meaning, stc.locked_date, stc.locked_by_person_id, pp1.full_name, stc.published_date, stc.published_by_person_id, pp2.full_name, stc.original_flag, lk1.meaning, stc.current_flag, lk2.meaning, stc.latest_eff_published_flag, lk3.meaning, pst.STRUCTURE_TYPE, ele.record_version_number, evr.record_version_number, stc.record_version_number, stc.wbs_record_version_number, ele.manager_person_id, papf.FULL_NAME, ele.baseline_start_date, ele.baseline_finish_date, stc.change_reason_code, lu.meaning, ppa.BASELINE_AS_OF_DATE, PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Structure(ele.proj_element_id,'WORKPLAN'), PA_PROJ_ELEMENTS_UTILS.check_child_element_exist(evr.element_version_id) from pa_project_statuses st1, pa_lookups st2, pa_lookups lu, per_all_people_f pp1, per_all_people_f pp2, fnd_lookups lk1, fnd_lookups lk2, fnd_lookups lk3, pa_proj_structure_types ppst, pa_structure_types pst, per_all_people_f papf, pa_projects_all ppa, pa_proj_elem_ver_structure stc, pa_proj_elements ele, pa_proj_element_versions evr WHERE evr.proj_element_id = ele.proj_element_id and stc.element_version_id = evr.element_version_id and stc.project_id = evr.project_id and st1.project_status_code = stc.status_code and st2.lookup_code (+) = stc.lock_status_code and st2.lookup_type (+) = 'PA_PEV_LOCK_STATUS' and ele.proj_element_id = ppst.proj_element_id and ppst.STRUCTURE_TYPE_ID = pst.STRUCTURE_TYPE_ID and lk1.lookup_type (+) = 'YES_NO' and lk1.lookup_code (+) = stc.original_flag and lk2.lookup_type (+) = 'YES_NO' and lk2.lookup_code (+) = stc.current_flag and lk3.lookup_type (+) = 'YES_NO' and lk3.lookup_code (+) = stc.latest_eff_published_flag and pp1.person_id (+) = stc.locked_by_person_id and sysdate between pp1.effective_start_date(+) and pp1.effective_end_date(+) and pp2.person_id (+) = stc.published_by_person_id and sysdate between pp2.effective_start_date(+) and pp2.effective_end_date(+) and ele.manager_person_id = papf.person_id(+) and SYSDATE BETWEEN papf.effective_start_date(+) AND papf.effective_end_date (+) and stc.change_reason_code = lu.lookup_code(+) and lu.lookup_type(+) = 'PA_WORKPLAN_CHANGE_REASON' and ele.project_id = ppa.project_id AND stc.project_id <> 0

View Text - HTML Formatted

SELECT ELE.PROJ_ELEMENT_ID
, EVR.ELEMENT_VERSION_ID
, STC.PEV_STRUCTURE_ID
, EVR.PROJECT_ID
, ELE.ELEMENT_NUMBER
, ELE.NAME
, STC.VERSION_NUMBER
, STC.NAME
, STC.DESCRIPTION
, STC.EFFECTIVE_DATE
, STC.STATUS_CODE
, ST1.PROJECT_STATUS_NAME
, STC.LOCK_STATUS_CODE
, ST2.MEANING
, STC.LOCKED_DATE
, STC.LOCKED_BY_PERSON_ID
, PP1.FULL_NAME
, STC.PUBLISHED_DATE
, STC.PUBLISHED_BY_PERSON_ID
, PP2.FULL_NAME
, STC.ORIGINAL_FLAG
, LK1.MEANING
, STC.CURRENT_FLAG
, LK2.MEANING
, STC.LATEST_EFF_PUBLISHED_FLAG
, LK3.MEANING
, PST.STRUCTURE_TYPE
, ELE.RECORD_VERSION_NUMBER
, EVR.RECORD_VERSION_NUMBER
, STC.RECORD_VERSION_NUMBER
, STC.WBS_RECORD_VERSION_NUMBER
, ELE.MANAGER_PERSON_ID
, PAPF.FULL_NAME
, ELE.BASELINE_START_DATE
, ELE.BASELINE_FINISH_DATE
, STC.CHANGE_REASON_CODE
, LU.MEANING
, PPA.BASELINE_AS_OF_DATE
, PA_PROJECT_STRUCTURE_UTILS.GET_STRUC_TYPE_FOR_STRUCTURE(ELE.PROJ_ELEMENT_ID
, 'WORKPLAN')
, PA_PROJ_ELEMENTS_UTILS.CHECK_CHILD_ELEMENT_EXIST(EVR.ELEMENT_VERSION_ID)
FROM PA_PROJECT_STATUSES ST1
, PA_LOOKUPS ST2
, PA_LOOKUPS LU
, PER_ALL_PEOPLE_F PP1
, PER_ALL_PEOPLE_F PP2
, FND_LOOKUPS LK1
, FND_LOOKUPS LK2
, FND_LOOKUPS LK3
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
, PER_ALL_PEOPLE_F PAPF
, PA_PROJECTS_ALL PPA
, PA_PROJ_ELEM_VER_STRUCTURE STC
, PA_PROJ_ELEMENTS ELE
, PA_PROJ_ELEMENT_VERSIONS EVR
WHERE EVR.PROJ_ELEMENT_ID = ELE.PROJ_ELEMENT_ID
AND STC.ELEMENT_VERSION_ID = EVR.ELEMENT_VERSION_ID
AND STC.PROJECT_ID = EVR.PROJECT_ID
AND ST1.PROJECT_STATUS_CODE = STC.STATUS_CODE
AND ST2.LOOKUP_CODE (+) = STC.LOCK_STATUS_CODE
AND ST2.LOOKUP_TYPE (+) = 'PA_PEV_LOCK_STATUS'
AND ELE.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID
AND PPST.STRUCTURE_TYPE_ID = PST.STRUCTURE_TYPE_ID
AND LK1.LOOKUP_TYPE (+) = 'YES_NO'
AND LK1.LOOKUP_CODE (+) = STC.ORIGINAL_FLAG
AND LK2.LOOKUP_TYPE (+) = 'YES_NO'
AND LK2.LOOKUP_CODE (+) = STC.CURRENT_FLAG
AND LK3.LOOKUP_TYPE (+) = 'YES_NO'
AND LK3.LOOKUP_CODE (+) = STC.LATEST_EFF_PUBLISHED_FLAG
AND PP1.PERSON_ID (+) = STC.LOCKED_BY_PERSON_ID
AND SYSDATE BETWEEN PP1.EFFECTIVE_START_DATE(+)
AND PP1.EFFECTIVE_END_DATE(+)
AND PP2.PERSON_ID (+) = STC.PUBLISHED_BY_PERSON_ID
AND SYSDATE BETWEEN PP2.EFFECTIVE_START_DATE(+)
AND PP2.EFFECTIVE_END_DATE(+)
AND ELE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND STC.CHANGE_REASON_CODE = LU.LOOKUP_CODE(+)
AND LU.LOOKUP_TYPE(+) = 'PA_WORKPLAN_CHANGE_REASON'
AND ELE.PROJECT_ID = PPA.PROJECT_ID
AND STC.PROJECT_ID <> 0