ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN F293CFDB55414A56A9538D73CF39E9E3Y select null action_, null status_, null message_, null modified_columns_, haouv.name operating_unit, ppa.segment1 project_number, ppa.name project_name, pfptv.name plan_type, ppevs.name workplan_version, ppe.element_number task_number, ppe.name task_name, prlm.alias planning_resource, prcv.name resource_class, pra.assignment_description, pra.total_plan_quantity planned_quantity, xxen_util.meaning(pra.unit_of_measure,'UNIT',275) unit_of_measure, pra.total_plan_raw_cost planned_raw_cost, pra.total_plan_burdened_cost planned_burdened_cost, xxen_util.yes(pra.use_task_schedule_flag) use_task_schedule, pra.planning_start_date start_date, pra.planning_end_date end_date, null delete_assignment, null publish_workplan, null publish_message_, pra.resource_assignment_id task_assignment_id, to_number(null) upload_row from hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_budget_versions pbv, pa_proj_elem_ver_structure ppevs, pa_fin_plan_types_vl pfptv, pa_resource_assignments pra, pa_proj_elements ppe, pa_resource_list_members prlm, pa_resource_classes_vl prcv where 1=1 and :p_upload_mode like '%'||xxen_upload.action_update and haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and haouv.organization_id=ppa.org_id and nvl(ppa.template_flag,'N')='N' and ppa.project_id=pbv.project_id and pbv.wp_version_flag='Y' and pbv.project_structure_version_id=xxen_pa_upload.workplan_version_id(ppa.project_id) and pbv.project_id=ppevs.project_id and pbv.project_structure_version_id=ppevs.element_version_id and pbv.fin_plan_type_id=pfptv.fin_plan_type_id and pbv.budget_version_id=pra.budget_version_id and pra.ta_display_flag='Y' and pra.task_id=ppe.proj_element_id and pra.resource_list_member_id=prlm.resource_list_member_id and prlm.resource_class_code=prcv.resource_class_code(+) New upload of workplan task resource assignments. U A xxen_pa_upload.upload_workplan_assignment xxen_pa_upload.publish_workplan US PA Workplan Upload Creates, updates and deletes the resource assignments of workplan tasks as on the Projects HTML workplan pages (Workplan > Tasks > Task Assignments), using pa_task_assignments_pub. The assignments are maintained on the current working workplan version, or on the published version when workplan versioning is disabled for the project. Planning resources come from the resource list of the workplan plan type. Planned cost of a rate based resource is calculated from the planned quantity. The resource of an existing assignment cannot be changed: delete the assignment and create a new one. Publish Workplan publishes the workplan version once all rows of the project uploaded successfully. Enginatics R12 only Upload 1=1 :p_upload_mode 1 10 :p_upload_mode LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C,U Y US Upload Mode 2 20 1=1 haouv.name=:p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 3 30 1=1 ppa.segment1=:p_project_number LOV custom select ppa.segment1 value, ppa.name description from hr_all_organization_units_vl haouv, pa_projects_all ppa where haouv.organization_id=ppa.org_id and nvl(ppa.template_flag,'N')='N' and haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and ppa.project_id in (select pbv.project_id from pa_budget_versions pbv where pbv.wp_version_flag='Y') order by value Y US Project Number 4 40 1=1 ppe.element_number=:p_task_number LOV custom select ppe.element_number value, ppe.name description from pa_projects_all ppa, pa_proj_elements ppe where xxen_util.contains(:$flex$.project_number,ppa.segment1)='Y' and ppa.project_id=ppe.project_id and ppe.object_type='PA_TASKS' order by value US Task Number A4FB372615F841E5A878E42C0B8E276B Default Task assignments of the workplan. ENGINATICS 7 ASSIGNMENT_DESCRIPTION 15 DELETE_ASSIGNMENT 14 END_DATE 1 OPERATING_UNIT 11 PLANNED_BURDENED_COST 8 PLANNED_QUANTITY 10 PLANNED_RAW_COST 4 PLANNING_RESOURCE 18 PLAN_TYPE 17 PROJECT_NAME 2 PROJECT_NUMBER 16 PUBLISH_WORKPLAN 6 RESOURCE_CLASS 13 START_DATE 5 TASK_NAME Y 3 TASK_NUMBER 9 UNIT_OF_MEASURE 12 USE_TASK_SCHEDULE 19 WORKPLAN_VERSION Upload Mode C,U S Site 247DDC8B9D7E4694BFA8F781EF6EEF54 New Assignments Empty sheet to create task assignments. ENGINATICS 7 ASSIGNMENT_DESCRIPTION 14 END_DATE 1 OPERATING_UNIT 11 PLANNED_BURDENED_COST 8 PLANNED_QUANTITY 10 PLANNED_RAW_COST 4 PLANNING_RESOURCE 17 PLAN_TYPE 16 PROJECT_NAME 2 PROJECT_NUMBER 15 PUBLISH_WORKPLAN 6 RESOURCE_CLASS 13 START_DATE 5 TASK_NAME Y 3 TASK_NUMBER 9 UNIT_OF_MEASURE 12 USE_TASK_SCHEDULE 18 WORKPLAN_VERSION Upload Mode C Y S Site Task Number A4FB372615F841E5A878E42C0B8E276B 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 OPERATING_UNIT LOV varchar2 240 HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name select hou.organization_id id, hou.name value from hr_operating_units hou :$flex$.operating_unit Y 6 PROJECT_NUMBER LOV custom varchar2 25 select ppa.project_id id, ppa.segment1 value, ppa.name description from hr_all_organization_units_vl haouv, pa_projects_all ppa where haouv.name=:$column$.operating_unit and haouv.organization_id=ppa.org_id and nvl(ppa.template_flag,'N')='N' and ppa.project_id in (select pbv.project_id from pa_budget_versions pbv where pbv.wp_version_flag='Y') order by value :$flex$.project_number Y 7 PROJECT_NAME Char varchar2 30 select ppa.name from hr_all_organization_units_vl haouv, pa_projects_all ppa where haouv.name=:$column$.operating_unit and haouv.organization_id=ppa.org_id and ppa.segment1=:$column$.project_number Y 8 PLAN_TYPE Char varchar2 150 select pfptv.name from hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_budget_versions pbv, pa_fin_plan_types_vl pfptv where haouv.name=:$column$.operating_unit and haouv.organization_id=ppa.org_id and ppa.segment1=:$column$.project_number and ppa.project_id=pbv.project_id and pbv.wp_version_flag='Y' and pbv.project_structure_version_id=xxen_pa_upload.workplan_version_id(ppa.project_id) and pbv.fin_plan_type_id=pfptv.fin_plan_type_id Workplan plan type of the project, which sets the resource list, time phasing and amounts of the workplan. Y 9 WORKPLAN_VERSION Char varchar2 240 select ppevs.name from hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_proj_elem_ver_structure ppevs where haouv.name=:$column$.operating_unit and haouv.organization_id=ppa.org_id and ppa.segment1=:$column$.project_number and ppa.project_id=ppevs.project_id and ppevs.element_version_id=xxen_pa_upload.workplan_version_id(ppa.project_id) Workplan version the assignments are maintained on: the current working version, or the published version when workplan versioning is disabled. Y 10 TASK_NUMBER LOV custom varchar2 100 select ppe.proj_element_id id, ppe.element_number value, ppe.name description from hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_proj_element_versions ppev, pa_proj_elements ppe where haouv.name=:$column$.operating_unit and haouv.organization_id=ppa.org_id and ppa.segment1=:$column$.project_number and ppa.project_id=ppev.project_id and ppev.parent_structure_version_id=xxen_pa_upload.workplan_version_id(ppa.project_id) and ppev.object_type='PA_TASKS' and ppev.proj_element_id=ppe.proj_element_id order by ppev.display_sequence Y 11 TASK_NAME Char varchar2 240 select ppe.name from hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_proj_elements ppe where haouv.name=:$column$.operating_unit and haouv.organization_id=ppa.org_id and ppa.segment1=:$column$.project_number and ppa.project_id=ppe.project_id and ppe.object_type='PA_TASKS' and ppe.element_number=:$column$.task_number Y 12 PLANNING_RESOURCE LOV custom varchar2 80 select prlm.resource_list_member_id id, prlm.alias value, (select prcv.name from pa_resource_classes_vl prcv where prlm.resource_class_code=prcv.resource_class_code) description from hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_budget_versions pbv, pa_resource_lists_all_bg prl, pa_resource_list_members prlm where haouv.name=:$column$.operating_unit and haouv.organization_id=ppa.org_id and ppa.segment1=:$column$.project_number and ppa.project_id=pbv.project_id and pbv.wp_version_flag='Y' and pbv.project_structure_version_id=xxen_pa_upload.workplan_version_id(ppa.project_id) and pbv.resource_list_id=prl.resource_list_id and prl.resource_list_id=prlm.resource_list_id and prlm.enabled_flag='Y' and nvl(prlm.wp_eligible_flag,'Y')='Y' and (prlm.migration_code is not null or prl.migration_code is null) and (prl.control_flag='N' and prlm.object_type='PROJECT' and prlm.object_id=ppa.project_id or nvl(prl.control_flag,'Y')='Y' and nvl(prlm.object_type,'RESOURCE_LIST')<>'PROJECT') order by value Planning resource of the workplan resource list. Y 13 RESOURCE_CLASS Char varchar2 80 select prcv.name from pa_resource_classes_vl prcv, hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_budget_versions pbv, pa_resource_lists_all_bg prl, pa_resource_list_members prlm where haouv.name=:$column$.operating_unit and haouv.organization_id=ppa.org_id and ppa.segment1=:$column$.project_number and ppa.project_id=pbv.project_id and pbv.wp_version_flag='Y' and pbv.project_structure_version_id=xxen_pa_upload.workplan_version_id(ppa.project_id) and pbv.resource_list_id=prl.resource_list_id and prl.resource_list_id=prlm.resource_list_id and prlm.enabled_flag='Y' and nvl(prlm.wp_eligible_flag,'Y')='Y' and (prlm.migration_code is not null or prl.migration_code is null) and (prl.control_flag='N' and prlm.object_type='PROJECT' and prlm.object_id=ppa.project_id or nvl(prl.control_flag,'Y')='Y' and nvl(prlm.object_type,'RESOURCE_LIST')<>'PROJECT') and prlm.alias=:$column$.planning_resource and prlm.resource_class_code=prcv.resource_class_code Y 14 ASSIGNMENT_DESCRIPTION Char varchar2 240 15 PLANNED_QUANTITY Number number 16 UNIT_OF_MEASURE Char varchar2 80 select xxen_util.meaning(prlm.unit_of_measure,'UNIT',275) from hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_budget_versions pbv, pa_resource_lists_all_bg prl, pa_resource_list_members prlm where haouv.name=:$column$.operating_unit and haouv.organization_id=ppa.org_id and ppa.segment1=:$column$.project_number and ppa.project_id=pbv.project_id and pbv.wp_version_flag='Y' and pbv.project_structure_version_id=xxen_pa_upload.workplan_version_id(ppa.project_id) and pbv.resource_list_id=prl.resource_list_id and prl.resource_list_id=prlm.resource_list_id and prlm.enabled_flag='Y' and nvl(prlm.wp_eligible_flag,'Y')='Y' and (prlm.migration_code is not null or prl.migration_code is null) and (prl.control_flag='N' and prlm.object_type='PROJECT' and prlm.object_id=ppa.project_id or nvl(prl.control_flag,'Y')='Y' and nvl(prlm.object_type,'RESOURCE_LIST')<>'PROJECT') and prlm.alias=:$column$.planning_resource Y 17 PLANNED_RAW_COST Number number 18 PLANNED_BURDENED_COST Number number 19 USE_TASK_SCHEDULE LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 20 START_DATE Date date 21 END_DATE Date date 22 DELETE_ASSIGNMENT LOV custom varchar2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.task_assignment_id is not null 23 PUBLISH_WORKPLAN LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Publish the workplan version after the task assignments of the project are uploaded. 24 PUBLISH_MESSAGE_ Char varchar2 Y 25 TASK_ASSIGNMENT_ID Number number Y 26 UPLOAD_ROW Number number Y U 1 action_ in Char ACTION_CODE_ U 2 status_ out Char STATUS_CODE_ U 3 message_ out Char MESSAGE_ U 4 p_org_id in Number OPERATING_UNIT U 5 p_project_id in Number PROJECT_NUMBER U 6 p_task_id in Number TASK_NUMBER U 7 p_resource_list_member_id in Number PLANNING_RESOURCE U 8 p_description in Char Y ASSIGNMENT_DESCRIPTION U 9 p_planned_quantity in Number Y PLANNED_QUANTITY U 10 p_planned_raw_cost in Number Y PLANNED_RAW_COST U 11 p_planned_burdened_cost in Number Y PLANNED_BURDENED_COST U 12 p_use_task_schedule in Char Y USE_TASK_SCHEDULE U 13 p_start_date in Date Y START_DATE U 14 p_end_date in Date Y END_DATE U 15 p_delete_assignment in Char Y DELETE_ASSIGNMENT U 16 p_task_assignment_id in Number Y TASK_ASSIGNMENT_ID U 17 p_task_assignment_id_out out Number TASK_ASSIGNMENT_ID P 1 status_ out Char STATUS_CODE_ P 2 message_ out Char PUBLISH_MESSAGE_ P 3 p_org_id in Number OPERATING_UNIT P 4 p_project_id in Number PROJECT_NUMBER P 5 p_publish_workplan in Char PUBLISH_WORKPLAN Error SQL Order By upload_row, project_number, task_number, planning_resource Success SQL select xu.action_, xu.status_, xu.message_||case when xu.publish_message_ is not null then ' '||xu.publish_message_ end message_, xu.modified_columns_, nvl(haouv.name,xu.operating_unit) operating_unit, nvl(ppa.segment1,xu.project_number) project_number, nvl(ppa.name,xu.project_name) project_name, nvl(pfptv.name,xu.plan_type) plan_type, nvl(ppevs.name,xu.workplan_version) workplan_version, nvl(ppe.element_number,xu.task_number) task_number, nvl(ppe.name,xu.task_name) task_name, nvl(prlm.alias,xu.planning_resource) planning_resource, nvl(prcv.name,xu.resource_class) resource_class, nvl(pra.assignment_description,xu.assignment_description) assignment_description, nvl(pra.total_plan_quantity,xu.planned_quantity) planned_quantity, nvl(xxen_util.meaning(pra.unit_of_measure,'UNIT',275),xu.unit_of_measure) unit_of_measure, nvl(pra.total_plan_raw_cost,xu.planned_raw_cost) planned_raw_cost, nvl(pra.total_plan_burdened_cost,xu.planned_burdened_cost) planned_burdened_cost, decode(pra.resource_assignment_id,null,xu.use_task_schedule,xxen_util.yes(pra.use_task_schedule_flag)) use_task_schedule, nvl(pra.planning_start_date,xu.start_date) start_date, nvl(pra.planning_end_date,xu.end_date) end_date, xu.delete_assignment, xu.publish_workplan, xu.publish_message_, nvl(pra.resource_assignment_id,xu.task_assignment_id) task_assignment_id, xu.upload_row from xxen_pa_workplan_upload_8068_u xu, pa_resource_assignments pra, pa_budget_versions pbv, pa_projects_all ppa, hr_all_organization_units_vl haouv, pa_proj_elem_ver_structure ppevs, pa_fin_plan_types_vl pfptv, pa_proj_elements ppe, pa_resource_list_members prlm, pa_resource_classes_vl prcv where xu.status_code_=xxen_upload.status_success and xu.task_assignment_id=pra.resource_assignment_id(+) and pra.budget_version_id=pbv.budget_version_id(+) and pbv.project_id=ppa.project_id(+) and ppa.org_id=haouv.organization_id(+) and pbv.project_id=ppevs.project_id(+) and pbv.project_structure_version_id=ppevs.element_version_id(+) and pbv.fin_plan_type_id=pfptv.fin_plan_type_id(+) and pra.task_id=ppe.proj_element_id(+) and pra.resource_list_member_id=prlm.resource_list_member_id(+) and prlm.resource_class_code=prcv.resource_class_code(+)