PJF_PROJECTS_WF_V

Details

• Schema: FUSION

• Object owner: PJF

• Object type: VIEW

Columns

Name

PROJECT_ID PROJECT_NUMBER PROJECT_BUSINESS_UNIT_ID CARRYING_OUT_ORG_ID PROJECT_UNIT_ID LEGAL_ENTITY_ID WORK_TYPE_ID PROJECT_TYPE_ID PROJECT_CURRENCY_CODE PROJFUNC_CURRENCY_CODE ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE31 ATTRIBUTE32 ATTRIBUTE33 ATTRIBUTE34 ATTRIBUTE35 ATTRIBUTE36 ATTRIBUTE37 ATTRIBUTE38 ATTRIBUTE39 ATTRIBUTE40 ATTRIBUTE41 ATTRIBUTE42 ATTRIBUTE43 ATTRIBUTE44 ATTRIBUTE45 ATTRIBUTE46 ATTRIBUTE47 ATTRIBUTE48 ATTRIBUTE49 ATTRIBUTE50 ATTRIBUTE1_NUMBER ATTRIBUTE2_NUMBER ATTRIBUTE3_NUMBER ATTRIBUTE4_NUMBER ATTRIBUTE5_NUMBER ATTRIBUTE6_NUMBER ATTRIBUTE7_NUMBER ATTRIBUTE8_NUMBER ATTRIBUTE9_NUMBER ATTRIBUTE10_NUMBER ATTRIBUTE11_NUMBER ATTRIBUTE12_NUMBER ATTRIBUTE13_NUMBER ATTRIBUTE14_NUMBER ATTRIBUTE15_NUMBER ATTRIBUTE1_DATE ATTRIBUTE2_DATE ATTRIBUTE3_DATE ATTRIBUTE4_DATE ATTRIBUTE5_DATE ATTRIBUTE6_DATE ATTRIBUTE7_DATE ATTRIBUTE8_DATE ATTRIBUTE9_DATE ATTRIBUTE10_DATE ATTRIBUTE11_DATE ATTRIBUTE12_DATE ATTRIBUTE13_DATE ATTRIBUTE14_DATE ATTRIBUTE15_DATE TASK_ID TASK_NUMBER TASK_MANAGER_PERSON_ID TASK_ORGANIZATION_ID PROJECT_ONLY HIERARCHY_TASK_MGR_PER_ID TASK_ATTRIBUTE_CATEGORY TASK_ATTRIBUTE1 TASK_ATTRIBUTE2 TASK_ATTRIBUTE3 TASK_ATTRIBUTE4 TASK_ATTRIBUTE5 TASK_ATTRIBUTE6 TASK_ATTRIBUTE7 TASK_ATTRIBUTE8 TASK_ATTRIBUTE9 TASK_ATTRIBUTE10 TASK_ATTRIBUTE11 TASK_ATTRIBUTE12 TASK_ATTRIBUTE13 TASK_ATTRIBUTE14 TASK_ATTRIBUTE15 TASK_ATTRIBUTE16 TASK_ATTRIBUTE17 TASK_ATTRIBUTE18 TASK_ATTRIBUTE19 TASK_ATTRIBUTE20 TASK_ATTRIBUTE21 TASK_ATTRIBUTE22 TASK_ATTRIBUTE23 TASK_ATTRIBUTE24 TASK_ATTRIBUTE25 TASK_ATTRIBUTE26 TASK_ATTRIBUTE27 TASK_ATTRIBUTE28 TASK_ATTRIBUTE29 TASK_ATTRIBUTE30 TASK_ATTRIBUTE31 TASK_ATTRIBUTE32 TASK_ATTRIBUTE33 TASK_ATTRIBUTE34 TASK_ATTRIBUTE35 TASK_ATTRIBUTE36 TASK_ATTRIBUTE37 TASK_ATTRIBUTE38 TASK_ATTRIBUTE39 TASK_ATTRIBUTE40 TASK_ATTRIBUTE41 TASK_ATTRIBUTE42 TASK_ATTRIBUTE43 TASK_ATTRIBUTE44 TASK_ATTRIBUTE45 TASK_ATTRIBUTE46 TASK_ATTRIBUTE47 TASK_ATTRIBUTE48 TASK_ATTRIBUTE49 TASK_ATTRIBUTE50 TASK_ATTRIBUTE1_NUM TASK_ATTRIBUTE2_NUM TASK_ATTRIBUTE3_NUM TASK_ATTRIBUTE4_NUM TASK_ATTRIBUTE5_NUM TASK_ATTRIBUTE6_NUM TASK_ATTRIBUTE7_NUM TASK_ATTRIBUTE8_NUM TASK_ATTRIBUTE9_NUM TASK_ATTRIBUTE10_NUM TASK_ATTRIBUTE11_NUM TASK_ATTRIBUTE12_NUM TASK_ATTRIBUTE13_NUM TASK_ATTRIBUTE14_NUM TASK_ATTRIBUTE15_NUM TASK_ATTRIBUTE1_DATE TASK_ATTRIBUTE2_DATE TASK_ATTRIBUTE3_DATE TASK_ATTRIBUTE4_DATE TASK_ATTRIBUTE5_DATE TASK_ATTRIBUTE6_DATE TASK_ATTRIBUTE7_DATE TASK_ATTRIBUTE8_DATE TASK_ATTRIBUTE9_DATE TASK_ATTRIBUTE10_DATE TASK_ATTRIBUTE11_DATE TASK_ATTRIBUTE12_DATE TASK_ATTRIBUTE13_DATE TASK_ATTRIBUTE14_DATE TASK_ATTRIBUTE15_DATE TEAM_MEMBER1_ROLE_ID TEAM_MEMBER1_PERSON_ID TEAM_MEMBER1_ROLE_NAME TEAM_MEMBER2_ROLE_ID TEAM_MEMBER2_PERSON_ID TEAM_MEMBER2_ROLE_NAME TEAM_MEMBER3_ROLE_ID TEAM_MEMBER3_PERSON_ID TEAM_MEMBER3_ROLE_NAME TEAM_MEMBER4_ROLE_ID TEAM_MEMBER4_PERSON_ID TEAM_MEMBER4_ROLE_NAME TEAM_MEMBER5_ROLE_ID TEAM_MEMBER5_PERSON_ID TEAM_MEMBER5_ROLE_NAME CC1_CLASS_CATEGORY CC1_CLASS_CATEGORY_ID CC1_CLASS_CODE CC1_CLASS_CODE_ID CC1_CODE_PERCENTAGE CC2_CLASS_CATEGORY CC2_CLASS_CATEGORY_ID CC2_CLASS_CODE CC2_CLASS_CODE_ID CC2_CODE_PERCENTAGE CC3_CLASS_CATEGORY CC3_CLASS_CATEGORY_ID CC3_CLASS_CODE CC3_CLASS_CODE_ID CC3_CODE_PERCENTAGE

Query

SQL_Statement

select project.project_id,project.segment1 project_number,project.org_id project_business_unit_id,project.carrying_out_organization_id carrying_out_org_id,project.project_unit_id,project.legal_entity_id, project.work_type_id,project.project_type_id,project.project_currency_code,project.projfunc_currency_code, project.attribute_category, project.attribute1,project.attribute2,project.attribute3,project.attribute4,project.attribute5,project.attribute6,project.attribute7,project.attribute8,project.attribute9, project.attribute10,project.attribute11,project.attribute12,project.attribute13,project.attribute14,project.attribute15,project.attribute16,project.attribute17,project.attribute18, project.attribute19,project.attribute20,project.attribute21,project.attribute22,project.attribute23,project.attribute24,project.attribute25,project.attribute26,project.attribute27, project.attribute28,project.attribute29,project.attribute30,project.attribute31,project.attribute32,project.attribute33,project.attribute34,project.attribute35,project.attribute36, project.attribute37,project.attribute38,project.attribute39,project.attribute40,project.attribute41,project.attribute42,project.attribute43,project.attribute44,project.attribute45, project.attribute46,project.attribute47,project.attribute48,project.attribute49,project.attribute50, project.attribute1_number,project.attribute2_number,project.attribute3_number,project.attribute4_number,project.attribute5_number,project.attribute6_number,project.attribute7_number, project.attribute8_number,project.attribute9_number,project.attribute10_number,project.attribute11_number,project.attribute12_number,project.attribute13_number,project.attribute14_number, project.attribute15_number,project.attribute1_date,project.attribute2_date,project.attribute3_date,project.attribute4_date,project.attribute5_date,project.attribute6_date,project.attribute7_date, project.attribute8_date,project.attribute9_date,project.attribute10_date,project.attribute11_date,project.attribute12_date,project.attribute13_date,project.attribute14_date,project.attribute15_date, task_b.proj_element_id task_id,task_b.element_number task_number,task_b.manager_person_id task_manager_person_id,task_b.carrying_out_organization_id task_organization_id, decode(task_b.object_type,'PJF_STRUCTURES','Y','N') project_only, decode(task_b.manager_person_id ,null, (select manager_person_id from (select task.manager_person_id,denorm.sup_level from PJO_XBS_DENORM denorm,PJF_PROJ_ELEMENTS_B task where task.project_id = task_b.project_id and task.DENORM_PARENT_STRUCT_VER_ID = denorm.struct_version_id and task.proj_element_id = denorm.sup_emt_id and denorm.sub_emt_id = task_b.proj_element_id and task.manager_person_id is not null order by sup_level desc) where rownum<2 ),task_b.manager_person_id) hierarchy_task_mgr_per_id, task_b.attribute_category task_attribute_category, task_b.attribute1 task_attribute1,task_b.attribute2 task_attribute2,task_b.attribute3 task_attribute3,task_b.attribute4 task_attribute4,task_b.attribute5 task_attribute5,task_b.attribute6 task_attribute6, task_b.attribute7 task_attribute7,task_b.attribute8 task_attribute8,task_b.attribute9 task_attribute9,task_b.attribute10 task_attribute10,task_b.attribute11 task_attribute11, task_b.attribute12 task_attribute12,task_b.attribute13 task_attribute13,task_b.attribute14 task_attribute14,task_b.attribute15 task_attribute15,task_b.attribute16 task_attribute16, task_b.attribute17 task_attribute17,task_b.attribute18 task_attribute18, task_b.attribute19 task_attribute19,task_b.attribute20 task_attribute20,task_b.attribute21 task_attribute21, task_b.attribute22 task_attribute22,task_b.attribute23 task_attribute23,task_b.attribute24 task_attribute24,task_b.attribute25 task_attribute25,task_b.attribute26 task_attribute26, task_b.attribute27 task_attribute27,task_b.attribute28 task_attribute28,task_b.attribute29 task_attribute29,task_b.attribute30 task_attribute30,task_b.attribute31 task_attribute31, task_b.attribute32 task_attribute32,task_b.attribute33 task_attribute33,task_b.attribute34 task_attribute34,task_b.attribute35 task_attribute35,task_b.attribute36 task_attribute36, task_b.attribute37 task_attribute37,task_b.attribute38 task_attribute38,task_b.attribute39 task_attribute39,task_b.attribute40 task_attribute40,task_b.attribute41 task_attribute41, task_b.attribute42 task_attribute42,task_b.attribute43 task_attribute43,task_b.attribute44 task_attribute44,task_b.attribute45 task_attribute45,task_b.attribute46 task_attribute46, task_b.attribute47 task_attribute47,task_b.attribute48 task_attribute48,task_b.attribute49 task_attribute49,task_b.attribute50 task_attribute50, task_b.attribute1_number task_attribute1_num,task_b.attribute2_number task_attribute2_num,task_b.attribute3_number task_attribute3_num,task_b.attribute4_number task_attribute4_num, task_b.attribute5_number task_attribute5_num,task_b.attribute6_number task_attribute6_num,task_b.attribute7_number task_attribute7_num,task_b.attribute8_number task_attribute8_num, task_b.attribute9_number task_attribute9_num,task_b.attribute10_number task_attribute10_num,task_b.attribute11_number task_attribute11_num,task_b.attribute12_number task_attribute12_num, task_b.attribute13_number task_attribute13_num,task_b.attribute14_number task_attribute14_num,task_b.attribute15_number task_attribute15_num, task_b.attribute1_date task_attribute1_date,task_b.attribute2_date task_attribute2_date,task_b.attribute3_date task_attribute3_date,task_b.attribute4_date task_attribute4_date, task_b.attribute5_date task_attribute5_date,task_b.attribute6_date task_attribute6_date,task_b.attribute7_date task_attribute7_date, task_b.attribute8_date task_attribute8_date, task_b.attribute9_date task_attribute9_date,task_b.attribute10_date task_attribute10_date,task_b.attribute11_date task_attribute11_date,task_b.attribute12_date task_attribute12_date, task_b.attribute13_date task_attribute13_date,task_b.attribute14_date task_attribute14_date,task_b.attribute15_date task_attribute15_date, team_member_data.TEAM_MEMBER1_ROLE_ID , team_member_data.TEAM_MEMBER1_PERSON_ID , team_member_data.TEAM_MEMBER1_ROLE_NAME , team_member_data.TEAM_MEMBER2_ROLE_ID , team_member_data.TEAM_MEMBER2_PERSON_ID , team_member_data.TEAM_MEMBER2_ROLE_NAME , team_member_data.TEAM_MEMBER3_ROLE_ID , team_member_data.TEAM_MEMBER3_PERSON_ID , team_member_data.TEAM_MEMBER3_ROLE_NAME , team_member_data.TEAM_MEMBER4_ROLE_ID , team_member_data.TEAM_MEMBER4_PERSON_ID , team_member_data.TEAM_MEMBER4_ROLE_NAME , team_member_data.TEAM_MEMBER5_ROLE_ID , team_member_data.TEAM_MEMBER5_PERSON_ID , team_member_data.TEAM_MEMBER5_ROLE_NAME , project_class_data.CC1_CLASS_CATEGORY , project_class_data.CC1_CLASS_CATEGORY_ID, project_class_data.CC1_CLASS_CODE , project_class_data.CC1_CLASS_CODE_ID , project_class_data.CC1_CODE_PERCENTAGE , project_class_data.CC2_CLASS_CATEGORY , project_class_data.CC2_CLASS_CATEGORY_ID, project_class_data.CC2_CLASS_CODE , project_class_data.CC2_CLASS_CODE_ID , project_class_data.CC2_CODE_PERCENTAGE , project_class_data.CC3_CLASS_CATEGORY , project_class_data.CC3_CLASS_CATEGORY_ID, project_class_data.CC3_CLASS_CODE , project_class_data.CC3_CLASS_CODE_ID , project_class_data.CC3_CODE_PERCENTAGE from PJF_PROJECTS_ALL_B project, PJF_PROJ_ELEMENTS_B task_b , LATERAL ( select project_id team_member_proj_id, max(decode(party.project_role_id,1,party.project_role_id)) team_member1_role_id ,max(decode(party.project_role_id,1,party.resource_source_id)) team_member1_person_id, max(decode(party.project_role_id,1,projectRoles.project_role_name)) team_member1_role_name, max(decode(party.project_role_id,10,party.project_role_id)) team_member2_role_id ,max(decode(party.project_role_id,10,party.resource_source_id)) team_member2_person_id, max(decode(party.project_role_id,10,projectRoles.project_role_name)) team_member2_role_name, max(decode(party.project_role_id,2,party.project_role_id)) team_member3_role_id ,max(decode(party.project_role_id,2,party.resource_source_id)) team_member3_person_id, max(decode(party.project_role_id,2,projectRoles.project_role_name)) team_member3_role_name, max(decode(party.project_role_id,11,party.project_role_id)) team_member4_role_id ,max(decode(party.project_role_id,11,party.resource_source_id)) team_member4_person_id, max(decode(party.project_role_id,11,projectRoles.project_role_name)) team_member4_role_name, max(decode(party.project_role_id,13,party.project_role_id)) team_member5_role_id ,max(decode(party.project_role_id,13,party.resource_source_id)) team_member5_person_id, max(decode(party.project_role_id,13,projectRoles.project_role_name)) team_member5_role_name from PJF_PROJECT_PARTIES party,PJF_PROJ_ROLE_TYPES_VL projectRoles where project_id = project.project_id and projectRoles.project_role_id=party.project_role_id and party.project_role_id IN (1,2,10,11,13) group by project_id )(+) team_member_data, LATERAL( select project_id project_class_project_id, max(decode(rownum,1,class_category)) cc1_class_category,max(decode(rownum,1,classes.class_category_id)) cc1_class_category_id, max(decode(rownum,1,codes.class_code)) cc1_class_code,max(decode(rownum,1,codes.class_code_id)) cc1_class_code_id,max(decode(rownum,1,code_percentage)) cc1_code_percentage, max(decode(rownum,2,class_category)) cc2_class_category,max(decode(rownum,2,classes.class_category_id)) cc2_class_category_id, max(decode(rownum,2,codes.class_code)) cc2_class_code,max(decode(rownum,2,codes.class_code_id)) cc2_class_code_id,max(decode(rownum,2,code_percentage)) cc2_code_percentage, max(decode(rownum,3,class_category)) cc3_class_category,max(decode(rownum,3,classes.class_category_id)) cc3_class_category_id, max(decode(rownum,3,codes.class_code)) cc3_class_code,max(decode(rownum,3,codes.class_code_id)) cc3_class_code_id,max(decode(rownum,3,code_percentage)) cc3_code_percentage from PJF_PROJECT_CLASSES classes,PJF_CLASS_CATEGORIES_VL cats,PJF_CLASS_CODES_VL codes where classes.project_id = project.project_id and classes.class_category_id = cats.class_category_id and cats.class_category_id = codes.class_category_id and classes.class_code_id = codes.class_code_id and rownum<4 group by classes.project_id)(+) project_class_data where task_b.project_id=project.project_id