VIEW: APPS.PA_FIN_STRUCTURES_TASKS_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.description ,ppe.object_type ,ppv.element_version_id ,ppe.proj_element_id ,ppa.project_id ,ppv.display_sequence ,por.object_id_from1 ,por.object_type_from ,por.relationship_type ,por.relationship_subtype ,decode( ppe.object_type , 'PA_STRUCTURES', 'Y', 'PA_TASKS', PA_PROJ_ELEMENTS_UTILS.is_summary_task_or_structure( ppv.element_version_id ) ) ,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 ,ppv2.record_version_number ,ppe.status_code ,pps2.project_status_name ,ppe.priority_code ,PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_TASK_PRIORITY_CODE' ,ppe.priority_code) ,ppe.carrying_out_organization_id ,hou.name ,por.object_relationship_id ,por.record_version_number ,ppvs.LATEST_EFF_PUBLISHED_FLAG ,ppa.segment1 ,ppa.name ,ppv2.proj_element_id ,pst.structure_type_class_code ,ppvs.published_date ,ppe.link_task_flag ,por.object_id_from1 ,ppe.baseline_start_date ,ppe.baseline_finish_date ,PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PM_PRODUCT_CODE', ppe.pm_source_code) ,ppe.pm_source_code ,ppe.pm_source_reference ,PA_PROJ_ELEMENTS_UTILS.IS_ACTIVE_TASK(ppv.element_version_id, ppv.object_type) ,PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO', PA_PROJ_ELEMENTS_UTILS.IS_ACTIVE_TASK(ppv.element_version_id, ppv.object_type)) ,PA_PROJ_ELEMENTS_UTILS.Get_DAYS_TO_START(ppv.element_version_id, ppv.object_type) ,PA_PROJ_ELEMENTS_UTILS.Get_DAYS_TO_FINISH(ppv.element_version_id, ppv.object_type) ,papf.work_telephone ,PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('SERVICE TYPE',pt.service_type_code) ,pt.service_type_code ,pwt.name ,pt.work_type_id ,PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO',pt.chargeable_flag) ,pt.chargeable_flag ,PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO',pt.billable_flag) ,pt.billable_flag ,PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO',pt.receive_project_invoice_flag) ,pt.receive_project_invoice_flag ,pt.start_date ,pt.completion_date ,to_date(NULL) ,ppa.BASELINE_AS_OF_DATE ,ppv.financial_task_flag financial_task_flag ,ppe.CREATION_DATE ,PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO',PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(ppv.element_version_id)) ,ppe.TYPE_ID ,tt.task_type ,ppe.STATUS_CODE ,pps3.PROJECT_STATUS_NAME ,pt.address_id ,addr.address1 ,addr.address2 ,addr.address3 ,addr.address4|| decode(addr.address4,null,null,', ')|| addr.city||', '||nvl(addr.state,addr.province)||', ' ||addr.county ,PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(ppv.project_id, ppv.parent_structure_version_id) ,pt.attribute_category ,pt.attribute1 ,pt.attribute2 ,pt.attribute3 ,pt.attribute4 ,pt.attribute5 ,pt.attribute6 ,pt.attribute7 ,pt.attribute8 ,pt.attribute9 ,pt.attribute10 ,ppv.TASK_UNPUB_VER_STATUS_CODE ,pa_control_items_utils.get_open_control_items(ppe.project_id,ppe.object_Type,ppe.proj_element_id,'ISSUE') ,to_number(null) ,PA_PROJ_ELEMENTS_UTILS.check_child_element_exist(ppv.element_version_id) ,pa_control_items_utils.get_open_control_items(ppe.project_id,ppe.object_Type,ppe.proj_element_id,'CHANGE_REQUEST') ,pa_control_items_utils.get_open_control_items(ppe.project_id,ppe.object_Type,ppe.proj_element_id,'CHANGE_ORDER') ,(select meaning from pa_lookups where lookup_type='PA_CHG_TASK_STATUSES' and lookup_code=ppe.task_status) task_status ,ppe.task_approver_id FROM pa_proj_elem_ver_structure ppvs ,per_all_people_f papf ,pa_project_statuses pps2 ,hr_all_organization_units_tl hou ,pa_projects_all ppa ,pa_proj_structure_types ppst ,pa_structure_types pst ,pa_work_types_tl pwt ,pa_task_types tt ,pa_project_statuses pps3 ,pa_proj_element_versions ppv2 ,pa_tasks pt ,pa_proj_elements ppe ,pa_proj_element_versions ppv ,pa_object_relationships por ,hz_party_sites party_site ,hz_locations addr ,hz_cust_acct_sites_all acct_site WHERE ppe.proj_element_id = ppv.proj_element_id AND ppv.element_version_id = ppvs.element_version_id (+) AND ppv.project_id = ppvs.project_id (+) AND ppv.element_version_id = por.object_id_to1 AND por.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS') AND ppe.manager_person_id = papf.person_id(+) AND ppe.object_type = 'PA_TASKS' AND SYSDATE BETWEEN papf.effective_start_date(+) AND papf.effective_end_date (+) AND ppe.status_code = pps2.PROJECT_STATUS_CODE(+) AND ppe.carrying_out_organization_id = hou.organization_id (+) AND userenv('LANG') = hou.language (+) AND ppe.project_id = ppa.project_id AND por.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS') AND por.object_id_from1 = ppv2.element_version_id(+) AND ppe.proj_element_id = ppst.proj_element_id(+) AND pst.structure_type_id(+) = ppst.structure_type_id AND por.relationship_type = 'S' AND (ppe.link_task_flag <> 'Y' or (ppe.link_task_flag = 'Y' and ppe.task_status is not null)) AND ppv.proj_element_id = pt.task_id (+) AND pt.work_type_id = pwt.work_type_id (+) AND pwt.language (+) = userenv('lang') AND tt.task_type_id = ppe.type_id AND ppe.status_code = pps3.PROJECT_STATUS_CODE (+) AND pps3.STATUS_TYPE (+) = 'TASK' AND ppe.project_id <> 0 AND pt.address_id = acct_site.cust_acct_site_id (+) AND acct_site.party_site_id = party_site.party_site_id(+) AND party_site.location_id = addr.location_id(+) and ppv.financial_task_flag = 'Y'

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.DESCRIPTION
, PPE.OBJECT_TYPE
, PPV.ELEMENT_VERSION_ID
, PPE.PROJ_ELEMENT_ID
, PPA.PROJECT_ID
, PPV.DISPLAY_SEQUENCE
, POR.OBJECT_ID_FROM1
, POR.OBJECT_TYPE_FROM
, POR.RELATIONSHIP_TYPE
, POR.RELATIONSHIP_SUBTYPE
, DECODE( PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, 'Y'
, 'PA_TASKS'
, PA_PROJ_ELEMENTS_UTILS.IS_SUMMARY_TASK_OR_STRUCTURE( PPV.ELEMENT_VERSION_ID ) )
, 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
, PPV2.RECORD_VERSION_NUMBER
, PPE.STATUS_CODE
, PPS2.PROJECT_STATUS_NAME
, PPE.PRIORITY_CODE
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_TASK_PRIORITY_CODE'
, PPE.PRIORITY_CODE)
, PPE.CARRYING_OUT_ORGANIZATION_ID
, HOU.NAME
, POR.OBJECT_RELATIONSHIP_ID
, POR.RECORD_VERSION_NUMBER
, PPVS.LATEST_EFF_PUBLISHED_FLAG
, PPA.SEGMENT1
, PPA.NAME
, PPV2.PROJ_ELEMENT_ID
, PST.STRUCTURE_TYPE_CLASS_CODE
, PPVS.PUBLISHED_DATE
, PPE.LINK_TASK_FLAG
, POR.OBJECT_ID_FROM1
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PM_PRODUCT_CODE'
, PPE.PM_SOURCE_CODE)
, PPE.PM_SOURCE_CODE
, PPE.PM_SOURCE_REFERENCE
, PA_PROJ_ELEMENTS_UTILS.IS_ACTIVE_TASK(PPV.ELEMENT_VERSION_ID
, PPV.OBJECT_TYPE)
, PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO'
, PA_PROJ_ELEMENTS_UTILS.IS_ACTIVE_TASK(PPV.ELEMENT_VERSION_ID
, PPV.OBJECT_TYPE))
, PA_PROJ_ELEMENTS_UTILS.GET_DAYS_TO_START(PPV.ELEMENT_VERSION_ID
, PPV.OBJECT_TYPE)
, PA_PROJ_ELEMENTS_UTILS.GET_DAYS_TO_FINISH(PPV.ELEMENT_VERSION_ID
, PPV.OBJECT_TYPE)
, PAPF.WORK_TELEPHONE
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('SERVICE TYPE'
, PT.SERVICE_TYPE_CODE)
, PT.SERVICE_TYPE_CODE
, PWT.NAME
, PT.WORK_TYPE_ID
, PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO'
, PT.CHARGEABLE_FLAG)
, PT.CHARGEABLE_FLAG
, PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO'
, PT.BILLABLE_FLAG)
, PT.BILLABLE_FLAG
, PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO'
, PT.RECEIVE_PROJECT_INVOICE_FLAG)
, PT.RECEIVE_PROJECT_INVOICE_FLAG
, PT.START_DATE
, PT.COMPLETION_DATE
, TO_DATE(NULL)
, PPA.BASELINE_AS_OF_DATE
, PPV.FINANCIAL_TASK_FLAG FINANCIAL_TASK_FLAG
, PPE.CREATION_DATE
, PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING('YES_NO'
, PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPV.ELEMENT_VERSION_ID))
, PPE.TYPE_ID
, TT.TASK_TYPE
, PPE.STATUS_CODE
, PPS3.PROJECT_STATUS_NAME
, PT.ADDRESS_ID
, ADDR.ADDRESS1
, ADDR.ADDRESS2
, ADDR.ADDRESS3
, ADDR.ADDRESS4|| DECODE(ADDR.ADDRESS4
, NULL
, NULL
, '
, ')|| ADDR.CITY||'
, '||NVL(ADDR.STATE
, ADDR.PROVINCE)||'
, ' ||ADDR.COUNTY
, PA_PROJECT_STRUCTURE_UTILS.CHECK_STRUC_VER_PUBLISHED(PPV.PROJECT_ID
, PPV.PARENT_STRUCTURE_VERSION_ID)
, PT.ATTRIBUTE_CATEGORY
, PT.ATTRIBUTE1
, PT.ATTRIBUTE2
, PT.ATTRIBUTE3
, PT.ATTRIBUTE4
, PT.ATTRIBUTE5
, PT.ATTRIBUTE6
, PT.ATTRIBUTE7
, PT.ATTRIBUTE8
, PT.ATTRIBUTE9
, PT.ATTRIBUTE10
, PPV.TASK_UNPUB_VER_STATUS_CODE
, PA_CONTROL_ITEMS_UTILS.GET_OPEN_CONTROL_ITEMS(PPE.PROJECT_ID
, PPE.OBJECT_TYPE
, PPE.PROJ_ELEMENT_ID
, 'ISSUE')
, TO_NUMBER(NULL)
, PA_PROJ_ELEMENTS_UTILS.CHECK_CHILD_ELEMENT_EXIST(PPV.ELEMENT_VERSION_ID)
, PA_CONTROL_ITEMS_UTILS.GET_OPEN_CONTROL_ITEMS(PPE.PROJECT_ID
, PPE.OBJECT_TYPE
, PPE.PROJ_ELEMENT_ID
, 'CHANGE_REQUEST')
, PA_CONTROL_ITEMS_UTILS.GET_OPEN_CONTROL_ITEMS(PPE.PROJECT_ID
, PPE.OBJECT_TYPE
, PPE.PROJ_ELEMENT_ID
, 'CHANGE_ORDER')
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='PA_CHG_TASK_STATUSES'
AND LOOKUP_CODE=PPE.TASK_STATUS) TASK_STATUS
, PPE.TASK_APPROVER_ID
FROM PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PER_ALL_PEOPLE_F PAPF
, PA_PROJECT_STATUSES PPS2
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, PA_PROJECTS_ALL PPA
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
, PA_WORK_TYPES_TL PWT
, PA_TASK_TYPES TT
, PA_PROJECT_STATUSES PPS3
, PA_PROJ_ELEMENT_VERSIONS PPV2
, PA_TASKS PT
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPV
, PA_OBJECT_RELATIONSHIPS POR
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS ADDR
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE PPE.PROJ_ELEMENT_ID = PPV.PROJ_ELEMENT_ID
AND PPV.ELEMENT_VERSION_ID = PPVS.ELEMENT_VERSION_ID (+)
AND PPV.PROJECT_ID = PPVS.PROJECT_ID (+)
AND PPV.ELEMENT_VERSION_ID = POR.OBJECT_ID_TO1
AND POR.OBJECT_TYPE_TO IN ('PA_STRUCTURES'
, 'PA_TASKS')
AND PPE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND PPE.OBJECT_TYPE = 'PA_TASKS'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND PPE.STATUS_CODE = PPS2.PROJECT_STATUS_CODE(+)
AND PPE.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID (+)
AND USERENV('LANG') = HOU.LANGUAGE (+)
AND PPE.PROJECT_ID = PPA.PROJECT_ID
AND POR.OBJECT_TYPE_FROM IN ('PA_STRUCTURES'
, 'PA_TASKS')
AND POR.OBJECT_ID_FROM1 = PPV2.ELEMENT_VERSION_ID(+)
AND PPE.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID(+)
AND PST.STRUCTURE_TYPE_ID(+) = PPST.STRUCTURE_TYPE_ID
AND POR.RELATIONSHIP_TYPE = 'S'
AND (PPE.LINK_TASK_FLAG <> 'Y' OR (PPE.LINK_TASK_FLAG = 'Y'
AND PPE.TASK_STATUS IS NOT NULL))
AND PPV.PROJ_ELEMENT_ID = PT.TASK_ID (+)
AND PT.WORK_TYPE_ID = PWT.WORK_TYPE_ID (+)
AND PWT.LANGUAGE (+) = USERENV('LANG')
AND TT.TASK_TYPE_ID = PPE.TYPE_ID
AND PPE.STATUS_CODE = PPS3.PROJECT_STATUS_CODE (+)
AND PPS3.STATUS_TYPE (+) = 'TASK'
AND PPE.PROJECT_ID <> 0
AND PT.ADDRESS_ID = ACCT_SITE.CUST_ACCT_SITE_ID (+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND PARTY_SITE.LOCATION_ID = ADDR.LOCATION_ID(+)
AND PPV.FINANCIAL_TASK_FLAG = 'Y'