VIEW: APPS.PA_FIN_PERCENT_COMPLETES_V

Source

View Text - Preformatted

SELECT decode( ppe.object_type, 'PA_TASKS', ppe.element_number, 'PA_STRUCTURES', to_char( ppvs.version_number ) ) ,decode( ppe.object_type, 'PA_TASKS', ppe.name, 'PA_STRUCTURES', ppvs.name ) ,ppe.object_type ,ppv.element_version_id ,ppe.proj_element_id ,ppe.project_id ,ppv.display_sequence ,por.object_id_from1 ,ppe.manager_person_id ,papf.FULL_NAME ,ppv.parent_structure_version_id ,ppv.wbs_level ,ppv.wbs_number ,ppe.record_version_number ,ppv.record_version_number ,ppe.carrying_out_organization_id ,hou.name ,pt.start_date ,pt.completion_date ,ppv.financial_task_flag ,ppc.percent_complete_id ,ppr.progress_rollup_id ,ppr.as_of_date ,nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage ,ppc.current_flag ,ppc.published_flag ,ppc.description ,ppc.progress_comment ,PA_PROJ_ELEMENTS_UTILS.check_child_element_exist(ppv.element_version_id) ,LPAD(' ',2 * (ppv.wbs_level - 1))||decode( ppe.object_type, 'PA_TASKS', ppe.element_number, 'PA_STRUCTURES', to_char( ppvs.version_number ) ) , ppr.LAST_UPDATE_DATE , ppr.LAST_UPDATED_BY , ppr.CREATION_DATE , ppr.CREATED_BY , ppr.LAST_UPDATE_LOGIN , ppc.pm_product_code , PA_TASK_UTILS.SORT_ORDER_TREE_WALK(pt.PARENT_TASK_ID, pt.TASK_NUMBER) , ppr.rowid FROM pa_proj_elem_ver_structure ppvs ,per_all_people_f papf ,hr_all_organization_units_tl hou ,pa_proj_structure_types ppst ,pa_structure_types pst ,pa_tasks pt ,pa_proj_elements ppe ,pa_proj_element_versions ppv ,pa_object_relationships por ,pa_percent_completes ppc ,pa_progress_rollup ppr WHERE ppe.proj_element_id = ppv.proj_element_id AND ppe.project_id = ppv.project_id AND ppe.object_type IN ('PA_STRUCTURES', 'PA_TASKS') AND ppv.project_id = ppvs.project_id AND ppv.parent_structure_version_id = ppvs.element_version_id AND ( (ppvs.status_code = 'STRUCTURE_PUBLISHED' AND ppvs.latest_eff_published_flag = 'Y' ) OR ( ppvs.status_code = 'STRUCTURE_WORKING' AND ppvs.current_working_flag = 'Y' AND not exists (select 1 from pa_proj_elem_ver_structure str2 where str2.project_id = ppvs.project_id and str2.proj_element_id = ppvs.proj_element_id and str2.status_code = 'STRUCTURE_PUBLISHED' ) ) ) AND ppvs.proj_element_id = ppst.proj_element_id AND pst.structure_type_id = ppst.structure_type_id AND pst.structure_type_id = 6 AND ppv.element_version_id = por.object_id_to1(+) AND por.relationship_type (+) = 'S' AND ppe.manager_person_id = papf.person_id(+) AND SYSDATE BETWEEN papf.effective_start_date(+) AND papf.effective_end_date (+) AND ppe.carrying_out_organization_id = hou.organization_id (+) AND userenv('LANG') = hou.language (+) AND ppe.link_task_flag <> 'Y' AND ppv.proj_element_id = pt.task_id (+) AND ((ppv.financial_task_flag='Y') OR (ppv.proj_element_id=ppvs.proj_element_id)) AND ppe.project_id = ppr.project_id(+) AND ppe.proj_element_id = ppr.object_id(+) AND ppe.object_type = ppr.object_type(+) AND ppr.current_flag(+) = 'Y' AND ppr.project_id = ppc.project_id(+) AND ppr.object_id = ppc.object_id(+) AND ppr.object_type = ppc.object_type(+) AND trunc(ppr.as_of_date) = trunc(ppc.date_computed(+)) AND ppc.current_flag(+) = 'Y' AND ppr.structure_type(+) = 'FINANCIAL' AND ppr.structure_version_id is null AND ppc.structure_type(+) = 'FINANCIAL' AND ppe.project_id <> 0

View Text - HTML Formatted

SELECT DECODE( PPE.OBJECT_TYPE
, 'PA_TASKS'
, PPE.ELEMENT_NUMBER
, 'PA_STRUCTURES'
, TO_CHAR( PPVS.VERSION_NUMBER ) )
, DECODE( PPE.OBJECT_TYPE
, 'PA_TASKS'
, PPE.NAME
, 'PA_STRUCTURES'
, PPVS.NAME )
, PPE.OBJECT_TYPE
, PPV.ELEMENT_VERSION_ID
, PPE.PROJ_ELEMENT_ID
, PPE.PROJECT_ID
, PPV.DISPLAY_SEQUENCE
, POR.OBJECT_ID_FROM1
, PPE.MANAGER_PERSON_ID
, PAPF.FULL_NAME
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPV.WBS_LEVEL
, PPV.WBS_NUMBER
, PPE.RECORD_VERSION_NUMBER
, PPV.RECORD_VERSION_NUMBER
, PPE.CARRYING_OUT_ORGANIZATION_ID
, HOU.NAME
, PT.START_DATE
, PT.COMPLETION_DATE
, PPV.FINANCIAL_TASK_FLAG
, PPC.PERCENT_COMPLETE_ID
, PPR.PROGRESS_ROLLUP_ID
, PPR.AS_OF_DATE
, NVL(PPR.COMPLETED_PERCENTAGE
, PPR.EFF_ROLLUP_PERCENT_COMP) COMPLETED_PERCENTAGE
, PPC.CURRENT_FLAG
, PPC.PUBLISHED_FLAG
, PPC.DESCRIPTION
, PPC.PROGRESS_COMMENT
, PA_PROJ_ELEMENTS_UTILS.CHECK_CHILD_ELEMENT_EXIST(PPV.ELEMENT_VERSION_ID)
, LPAD(' '
, 2 * (PPV.WBS_LEVEL - 1))||DECODE( PPE.OBJECT_TYPE
, 'PA_TASKS'
, PPE.ELEMENT_NUMBER
, 'PA_STRUCTURES'
, TO_CHAR( PPVS.VERSION_NUMBER ) )
, PPR.LAST_UPDATE_DATE
, PPR.LAST_UPDATED_BY
, PPR.CREATION_DATE
, PPR.CREATED_BY
, PPR.LAST_UPDATE_LOGIN
, PPC.PM_PRODUCT_CODE
, PA_TASK_UTILS.SORT_ORDER_TREE_WALK(PT.PARENT_TASK_ID
, PT.TASK_NUMBER)
, PPR.ROWID
FROM PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PER_ALL_PEOPLE_F PAPF
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
, PA_TASKS PT
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPV
, PA_OBJECT_RELATIONSHIPS POR
, PA_PERCENT_COMPLETES PPC
, PA_PROGRESS_ROLLUP PPR
WHERE PPE.PROJ_ELEMENT_ID = PPV.PROJ_ELEMENT_ID
AND PPE.PROJECT_ID = PPV.PROJECT_ID
AND PPE.OBJECT_TYPE IN ('PA_STRUCTURES'
, 'PA_TASKS')
AND PPV.PROJECT_ID = PPVS.PROJECT_ID
AND PPV.PARENT_STRUCTURE_VERSION_ID = PPVS.ELEMENT_VERSION_ID
AND ( (PPVS.STATUS_CODE = 'STRUCTURE_PUBLISHED'
AND PPVS.LATEST_EFF_PUBLISHED_FLAG = 'Y' ) OR ( PPVS.STATUS_CODE = 'STRUCTURE_WORKING'
AND PPVS.CURRENT_WORKING_FLAG = 'Y'
AND NOT EXISTS (SELECT 1
FROM PA_PROJ_ELEM_VER_STRUCTURE STR2
WHERE STR2.PROJECT_ID = PPVS.PROJECT_ID
AND STR2.PROJ_ELEMENT_ID = PPVS.PROJ_ELEMENT_ID
AND STR2.STATUS_CODE = 'STRUCTURE_PUBLISHED' ) ) )
AND PPVS.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID
AND PST.STRUCTURE_TYPE_ID = PPST.STRUCTURE_TYPE_ID
AND PST.STRUCTURE_TYPE_ID = 6
AND PPV.ELEMENT_VERSION_ID = POR.OBJECT_ID_TO1(+)
AND POR.RELATIONSHIP_TYPE (+) = 'S'
AND PPE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND PPE.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID (+)
AND USERENV('LANG') = HOU.LANGUAGE (+)
AND PPE.LINK_TASK_FLAG <> 'Y'
AND PPV.PROJ_ELEMENT_ID = PT.TASK_ID (+)
AND ((PPV.FINANCIAL_TASK_FLAG='Y') OR (PPV.PROJ_ELEMENT_ID=PPVS.PROJ_ELEMENT_ID))
AND PPE.PROJECT_ID = PPR.PROJECT_ID(+)
AND PPE.PROJ_ELEMENT_ID = PPR.OBJECT_ID(+)
AND PPE.OBJECT_TYPE = PPR.OBJECT_TYPE(+)
AND PPR.CURRENT_FLAG(+) = 'Y'
AND PPR.PROJECT_ID = PPC.PROJECT_ID(+)
AND PPR.OBJECT_ID = PPC.OBJECT_ID(+)
AND PPR.OBJECT_TYPE = PPC.OBJECT_TYPE(+)
AND TRUNC(PPR.AS_OF_DATE) = TRUNC(PPC.DATE_COMPUTED(+))
AND PPC.CURRENT_FLAG(+) = 'Y'
AND PPR.STRUCTURE_TYPE(+) = 'FINANCIAL'
AND PPR.STRUCTURE_VERSION_ID IS NULL
AND PPC.STRUCTURE_TYPE(+) = 'FINANCIAL'
AND PPE.PROJECT_ID <> 0