VIEW: APPS.PA_TASK_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT ra.rowid row_id, ra.pm_res_assignment_reference, ra.resource_assignment_id, proj.pm_project_reference, ra.pm_product_code, ra.project_id, pev.parent_structure_version_id, pevs.latest_eff_published_flag, pe.pm_source_reference, ra.wbs_element_version_id, pe.proj_element_id, pe.name, pe.element_number, rlm.alias, ra.resource_list_member_id, ra.resource_class_code, rctl.name, ra.resource_class_flag, ra.rbs_element_id, ra.person_id, ra.unit_of_measure, uom.meaning, ra.assignment_description, ra.rate_based_flag, ra.unplanned_flag, PA_PLANNING_RESOURCE_UTILS.Ret_Resource_Name(ra.res_type_code, ra.person_id, ra.bom_resource_id, ra.job_id, ra.person_type_code, ra.non_labor_resource, ra.inventory_item_id, rcb.resource_class_id, ra.item_category_id, ra.resource_assignment_id), ra.res_type_code, ra.bom_resource_id, ra.job_id, ra.person_type_code, ra.non_labor_resource, ra.inventory_item_id, rcb.resource_class_id, ra.item_category_id, PA_PLANNING_RESOURCE_UTILS.Ret_Fin_Category_Name(ra.fc_res_type_code, ra.expenditure_type, ra.expenditure_category, ra.event_type, ra.revenue_category_code, ra.resource_assignment_id), ra.fc_res_type_code, ra.expenditure_type, ra.expenditure_category, ra.event_type, ra.revenue_category_code, pa_expenditures_utils.GetOrgTlName(ra.organization_id), ra.organization_id, pa_planning_element_utils.get_supplier_name(ra.supplier_id), ra.supplier_id, ra.named_role, PA_PLANNING_RESOURCE_UTILS.Ret_Incur_By_Res_Name(ra.person_id, ra.job_id, ra.incur_by_role_id, ra.person_type_code, ra.incur_by_res_class_code, ra.resource_assignment_id), ra.incur_by_role_id, ra.incur_by_res_class_code, ra.incurred_by_res_flag, ra.procure_resource_flag, pa_planning_element_utils.get_project_role_name(ra.project_role_id), ra.project_role_id, ra.schedule_start_date, ra.schedule_end_date, ra.planning_start_date, ra.planning_end_date, ra.use_task_schedule_flag, ra.schedule_start_date - pevsch.scheduled_start_date as delay, ra.schedule_end_date - ra.schedule_start_date as duration, ra.spread_curve_id, pa_planning_element_utils.get_spread_curve_name(ra.spread_curve_id), PA_TASK_ASSIGNMENT_UTILS.get_planned_currency_info( ra.resource_assignment_id, ra.project_id, 'txn_currency_code') as txn_currency_code, pa_planning_element_utils.get_mfc_cost_type_name(ra.mfc_cost_type_id), ra.mfc_cost_type_id, pa_fin_plan_utils.get_lookup_value('PA_FP_ETC_METHOD', ra.etc_method_code), ra.etc_method_code, ra.billable_percent, prac.total_quantity as planned_quantity, prac.total_txn_burdened_cost as planned_bur_cost_txn_cur, ra.total_plan_burdened_cost as planned_bur_cost_projfunc, ra.total_project_burdened_cost as planned_bur_cost_proj_cur, prac.total_txn_raw_cost as planned_raw_cost_txn_cur, ra.total_project_raw_cost as planned_raw_cost_proj_cur, ra.total_plan_raw_cost as planned_raw_cost_projfunc, prac.total_init_quantity as actual_quantity, prac.total_txn_init_burdened_cost as act_bur_cost_txn_cur, prac.total_project_init_bd_cost as act_bur_cost_proj_cur, prac.total_projfunc_init_bd_cost as act_bur_cost_projfunc, prac.total_txn_init_raw_cost as act_raw_cost_txn_cur, prac.total_project_init_raw_cost as act_raw_cost_proj_cur, prac.total_projfunc_init_raw_cost as act_raw_cost_projfunc, prac.txn_average_raw_cost_rate as avg_raw_cost_rate, prac.txn_average_burden_cost_rate as avg_bur_cost_rate, PA_TASK_ASSIGNMENT_UTILS.get_baselined_asgmt_dates(ra.project_id, ra.wbs_element_version_id, ra.resource_assignment_id, null, proj.project_currency_code, proj.projfunc_currency_code, 'baselined_asgmt_start_date'), PA_TASK_ASSIGNMENT_UTILS.get_baselined_asgmt_dates(ra.project_id, ra.wbs_element_version_id, ra.resource_assignment_id, null, proj.project_currency_code, proj.projfunc_currency_code, 'baselined_asgmt_end_date'), PA_TASK_ASSIGNMENT_UTILS.get_baselined_asgmt_amounts(ra.project_id, ra.wbs_element_version_id, ra.resource_assignment_id, null, proj.project_currency_code, proj.projfunc_currency_code, 'baselined_planned_qty'), PA_TASK_ASSIGNMENT_UTILS.get_baselined_asgmt_amounts(ra.project_id, ra.wbs_element_version_id, ra.resource_assignment_id, null, proj.project_currency_code, proj.projfunc_currency_code, 'bl_planned_bur_cost_txn_cur'), PA_TASK_ASSIGNMENT_UTILS.get_baselined_asgmt_amounts(ra.project_id, ra.wbs_element_version_id, ra.resource_assignment_id, null, proj.project_currency_code, proj.projfunc_currency_code, 'bl_bur_cost_proj_cur'), PA_TASK_ASSIGNMENT_UTILS.get_baselined_asgmt_amounts(ra.project_id, ra.wbs_element_version_id, ra.resource_assignment_id, null, proj.project_currency_code, proj.projfunc_currency_code, 'bl_bur_cost_projfunc_cur'), PA_TASK_ASSIGNMENT_UTILS.get_baselined_asgmt_amounts(ra.project_id, ra.wbs_element_version_id, ra.resource_assignment_id, null, proj.project_currency_code, proj.projfunc_currency_code, 'bl_raw_cost_txn_cur'), PA_TASK_ASSIGNMENT_UTILS.get_baselined_asgmt_amounts(ra.project_id, ra.wbs_element_version_id, ra.resource_assignment_id, null, proj.project_currency_code, proj.projfunc_currency_code, 'bl_raw_cost_proj_cur'), PA_TASK_ASSIGNMENT_UTILS.get_baselined_asgmt_amounts(ra.project_id, ra.wbs_element_version_id, ra.resource_assignment_id, null, proj.project_currency_code, proj.projfunc_currency_code, 'bl_raw_cost_projfunc_cur'), ra.ta_display_flag, ra.budget_version_id, ra.rate_expenditure_type, ra.rate_exp_func_curr_code, ra.rate_expenditure_org_id, DECODE(ra.incurred_by_res_flag, 'Y', ra.res_type_code, NULL), ra.scheduled_delay , ra.cbs_element_id from pa_resource_assignments ra, pa_resource_list_members rlm, pa_resource_classes_tl rctl, pa_resource_classes_b rcb, pa_proj_element_versions pev, pa_proj_elements pe, pa_lookups uom, pa_project_role_types prt, pa_proj_elem_ver_structure pevs, pa_projects_all proj, PA_PROJ_ELEM_VER_SCHEDULE pevsch, pa_budget_versions bv, PA_RESOURCE_ASGN_CURR prac WHERE ra.resource_list_member_id = rlm.resource_list_member_id and ra.resource_class_code = rcb.resource_class_code and rcb.resource_class_id = rctl.resource_class_id and rctl.language = userenv('LANG') and ra.wbs_element_version_id = pev.element_version_id and pev.proj_element_id = pe.proj_element_id and pevsch.element_version_id = ra.wbs_element_version_id and uom.lookup_type = 'UNIT' and uom.lookup_code = ra.unit_of_measure and ra.ta_display_flag is not null and pev.parent_structure_version_id = pevs.element_version_id and ra.project_id = proj.project_id and pev.project_id = pevs.project_id and ra.project_role_id = prt.project_role_id(+) and ra.budget_version_id = bv.budget_version_id and bv.project_structure_version_id = pevs.element_version_id and ra.resource_assignment_id = prac.resource_assignment_id

View Text - HTML Formatted

SELECT RA.ROWID ROW_ID
, RA.PM_RES_ASSIGNMENT_REFERENCE
, RA.RESOURCE_ASSIGNMENT_ID
, PROJ.PM_PROJECT_REFERENCE
, RA.PM_PRODUCT_CODE
, RA.PROJECT_ID
, PEV.PARENT_STRUCTURE_VERSION_ID
, PEVS.LATEST_EFF_PUBLISHED_FLAG
, PE.PM_SOURCE_REFERENCE
, RA.WBS_ELEMENT_VERSION_ID
, PE.PROJ_ELEMENT_ID
, PE.NAME
, PE.ELEMENT_NUMBER
, RLM.ALIAS
, RA.RESOURCE_LIST_MEMBER_ID
, RA.RESOURCE_CLASS_CODE
, RCTL.NAME
, RA.RESOURCE_CLASS_FLAG
, RA.RBS_ELEMENT_ID
, RA.PERSON_ID
, RA.UNIT_OF_MEASURE
, UOM.MEANING
, RA.ASSIGNMENT_DESCRIPTION
, RA.RATE_BASED_FLAG
, RA.UNPLANNED_FLAG
, PA_PLANNING_RESOURCE_UTILS.RET_RESOURCE_NAME(RA.RES_TYPE_CODE
, RA.PERSON_ID
, RA.BOM_RESOURCE_ID
, RA.JOB_ID
, RA.PERSON_TYPE_CODE
, RA.NON_LABOR_RESOURCE
, RA.INVENTORY_ITEM_ID
, RCB.RESOURCE_CLASS_ID
, RA.ITEM_CATEGORY_ID
, RA.RESOURCE_ASSIGNMENT_ID)
, RA.RES_TYPE_CODE
, RA.BOM_RESOURCE_ID
, RA.JOB_ID
, RA.PERSON_TYPE_CODE
, RA.NON_LABOR_RESOURCE
, RA.INVENTORY_ITEM_ID
, RCB.RESOURCE_CLASS_ID
, RA.ITEM_CATEGORY_ID
, PA_PLANNING_RESOURCE_UTILS.RET_FIN_CATEGORY_NAME(RA.FC_RES_TYPE_CODE
, RA.EXPENDITURE_TYPE
, RA.EXPENDITURE_CATEGORY
, RA.EVENT_TYPE
, RA.REVENUE_CATEGORY_CODE
, RA.RESOURCE_ASSIGNMENT_ID)
, RA.FC_RES_TYPE_CODE
, RA.EXPENDITURE_TYPE
, RA.EXPENDITURE_CATEGORY
, RA.EVENT_TYPE
, RA.REVENUE_CATEGORY_CODE
, PA_EXPENDITURES_UTILS.GETORGTLNAME(RA.ORGANIZATION_ID)
, RA.ORGANIZATION_ID
, PA_PLANNING_ELEMENT_UTILS.GET_SUPPLIER_NAME(RA.SUPPLIER_ID)
, RA.SUPPLIER_ID
, RA.NAMED_ROLE
, PA_PLANNING_RESOURCE_UTILS.RET_INCUR_BY_RES_NAME(RA.PERSON_ID
, RA.JOB_ID
, RA.INCUR_BY_ROLE_ID
, RA.PERSON_TYPE_CODE
, RA.INCUR_BY_RES_CLASS_CODE
, RA.RESOURCE_ASSIGNMENT_ID)
, RA.INCUR_BY_ROLE_ID
, RA.INCUR_BY_RES_CLASS_CODE
, RA.INCURRED_BY_RES_FLAG
, RA.PROCURE_RESOURCE_FLAG
, PA_PLANNING_ELEMENT_UTILS.GET_PROJECT_ROLE_NAME(RA.PROJECT_ROLE_ID)
, RA.PROJECT_ROLE_ID
, RA.SCHEDULE_START_DATE
, RA.SCHEDULE_END_DATE
, RA.PLANNING_START_DATE
, RA.PLANNING_END_DATE
, RA.USE_TASK_SCHEDULE_FLAG
, RA.SCHEDULE_START_DATE - PEVSCH.SCHEDULED_START_DATE AS DELAY
, RA.SCHEDULE_END_DATE - RA.SCHEDULE_START_DATE AS DURATION
, RA.SPREAD_CURVE_ID
, PA_PLANNING_ELEMENT_UTILS.GET_SPREAD_CURVE_NAME(RA.SPREAD_CURVE_ID)
, PA_TASK_ASSIGNMENT_UTILS.GET_PLANNED_CURRENCY_INFO( RA.RESOURCE_ASSIGNMENT_ID
, RA.PROJECT_ID
, 'TXN_CURRENCY_CODE') AS TXN_CURRENCY_CODE
, PA_PLANNING_ELEMENT_UTILS.GET_MFC_COST_TYPE_NAME(RA.MFC_COST_TYPE_ID)
, RA.MFC_COST_TYPE_ID
, PA_FIN_PLAN_UTILS.GET_LOOKUP_VALUE('PA_FP_ETC_METHOD'
, RA.ETC_METHOD_CODE)
, RA.ETC_METHOD_CODE
, RA.BILLABLE_PERCENT
, PRAC.TOTAL_QUANTITY AS PLANNED_QUANTITY
, PRAC.TOTAL_TXN_BURDENED_COST AS PLANNED_BUR_COST_TXN_CUR
, RA.TOTAL_PLAN_BURDENED_COST AS PLANNED_BUR_COST_PROJFUNC
, RA.TOTAL_PROJECT_BURDENED_COST AS PLANNED_BUR_COST_PROJ_CUR
, PRAC.TOTAL_TXN_RAW_COST AS PLANNED_RAW_COST_TXN_CUR
, RA.TOTAL_PROJECT_RAW_COST AS PLANNED_RAW_COST_PROJ_CUR
, RA.TOTAL_PLAN_RAW_COST AS PLANNED_RAW_COST_PROJFUNC
, PRAC.TOTAL_INIT_QUANTITY AS ACTUAL_QUANTITY
, PRAC.TOTAL_TXN_INIT_BURDENED_COST AS ACT_BUR_COST_TXN_CUR
, PRAC.TOTAL_PROJECT_INIT_BD_COST AS ACT_BUR_COST_PROJ_CUR
, PRAC.TOTAL_PROJFUNC_INIT_BD_COST AS ACT_BUR_COST_PROJFUNC
, PRAC.TOTAL_TXN_INIT_RAW_COST AS ACT_RAW_COST_TXN_CUR
, PRAC.TOTAL_PROJECT_INIT_RAW_COST AS ACT_RAW_COST_PROJ_CUR
, PRAC.TOTAL_PROJFUNC_INIT_RAW_COST AS ACT_RAW_COST_PROJFUNC
, PRAC.TXN_AVERAGE_RAW_COST_RATE AS AVG_RAW_COST_RATE
, PRAC.TXN_AVERAGE_BURDEN_COST_RATE AS AVG_BUR_COST_RATE
, PA_TASK_ASSIGNMENT_UTILS.GET_BASELINED_ASGMT_DATES(RA.PROJECT_ID
, RA.WBS_ELEMENT_VERSION_ID
, RA.RESOURCE_ASSIGNMENT_ID
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJFUNC_CURRENCY_CODE
, 'BASELINED_ASGMT_START_DATE')
, PA_TASK_ASSIGNMENT_UTILS.GET_BASELINED_ASGMT_DATES(RA.PROJECT_ID
, RA.WBS_ELEMENT_VERSION_ID
, RA.RESOURCE_ASSIGNMENT_ID
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJFUNC_CURRENCY_CODE
, 'BASELINED_ASGMT_END_DATE')
, PA_TASK_ASSIGNMENT_UTILS.GET_BASELINED_ASGMT_AMOUNTS(RA.PROJECT_ID
, RA.WBS_ELEMENT_VERSION_ID
, RA.RESOURCE_ASSIGNMENT_ID
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJFUNC_CURRENCY_CODE
, 'BASELINED_PLANNED_QTY')
, PA_TASK_ASSIGNMENT_UTILS.GET_BASELINED_ASGMT_AMOUNTS(RA.PROJECT_ID
, RA.WBS_ELEMENT_VERSION_ID
, RA.RESOURCE_ASSIGNMENT_ID
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJFUNC_CURRENCY_CODE
, 'BL_PLANNED_BUR_COST_TXN_CUR')
, PA_TASK_ASSIGNMENT_UTILS.GET_BASELINED_ASGMT_AMOUNTS(RA.PROJECT_ID
, RA.WBS_ELEMENT_VERSION_ID
, RA.RESOURCE_ASSIGNMENT_ID
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJFUNC_CURRENCY_CODE
, 'BL_BUR_COST_PROJ_CUR')
, PA_TASK_ASSIGNMENT_UTILS.GET_BASELINED_ASGMT_AMOUNTS(RA.PROJECT_ID
, RA.WBS_ELEMENT_VERSION_ID
, RA.RESOURCE_ASSIGNMENT_ID
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJFUNC_CURRENCY_CODE
, 'BL_BUR_COST_PROJFUNC_CUR')
, PA_TASK_ASSIGNMENT_UTILS.GET_BASELINED_ASGMT_AMOUNTS(RA.PROJECT_ID
, RA.WBS_ELEMENT_VERSION_ID
, RA.RESOURCE_ASSIGNMENT_ID
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJFUNC_CURRENCY_CODE
, 'BL_RAW_COST_TXN_CUR')
, PA_TASK_ASSIGNMENT_UTILS.GET_BASELINED_ASGMT_AMOUNTS(RA.PROJECT_ID
, RA.WBS_ELEMENT_VERSION_ID
, RA.RESOURCE_ASSIGNMENT_ID
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJFUNC_CURRENCY_CODE
, 'BL_RAW_COST_PROJ_CUR')
, PA_TASK_ASSIGNMENT_UTILS.GET_BASELINED_ASGMT_AMOUNTS(RA.PROJECT_ID
, RA.WBS_ELEMENT_VERSION_ID
, RA.RESOURCE_ASSIGNMENT_ID
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJFUNC_CURRENCY_CODE
, 'BL_RAW_COST_PROJFUNC_CUR')
, RA.TA_DISPLAY_FLAG
, RA.BUDGET_VERSION_ID
, RA.RATE_EXPENDITURE_TYPE
, RA.RATE_EXP_FUNC_CURR_CODE
, RA.RATE_EXPENDITURE_ORG_ID
, DECODE(RA.INCURRED_BY_RES_FLAG
, 'Y'
, RA.RES_TYPE_CODE
, NULL)
, RA.SCHEDULED_DELAY
, RA.CBS_ELEMENT_ID
FROM PA_RESOURCE_ASSIGNMENTS RA
, PA_RESOURCE_LIST_MEMBERS RLM
, PA_RESOURCE_CLASSES_TL RCTL
, PA_RESOURCE_CLASSES_B RCB
, PA_PROJ_ELEMENT_VERSIONS PEV
, PA_PROJ_ELEMENTS PE
, PA_LOOKUPS UOM
, PA_PROJECT_ROLE_TYPES PRT
, PA_PROJ_ELEM_VER_STRUCTURE PEVS
, PA_PROJECTS_ALL PROJ
, PA_PROJ_ELEM_VER_SCHEDULE PEVSCH
, PA_BUDGET_VERSIONS BV
, PA_RESOURCE_ASGN_CURR PRAC
WHERE RA.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID
AND RA.RESOURCE_CLASS_CODE = RCB.RESOURCE_CLASS_CODE
AND RCB.RESOURCE_CLASS_ID = RCTL.RESOURCE_CLASS_ID
AND RCTL.LANGUAGE = USERENV('LANG')
AND RA.WBS_ELEMENT_VERSION_ID = PEV.ELEMENT_VERSION_ID
AND PEV.PROJ_ELEMENT_ID = PE.PROJ_ELEMENT_ID
AND PEVSCH.ELEMENT_VERSION_ID = RA.WBS_ELEMENT_VERSION_ID
AND UOM.LOOKUP_TYPE = 'UNIT'
AND UOM.LOOKUP_CODE = RA.UNIT_OF_MEASURE
AND RA.TA_DISPLAY_FLAG IS NOT NULL
AND PEV.PARENT_STRUCTURE_VERSION_ID = PEVS.ELEMENT_VERSION_ID
AND RA.PROJECT_ID = PROJ.PROJECT_ID
AND PEV.PROJECT_ID = PEVS.PROJECT_ID
AND RA.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID(+)
AND RA.BUDGET_VERSION_ID = BV.BUDGET_VERSION_ID
AND BV.PROJECT_STRUCTURE_VERSION_ID = PEVS.ELEMENT_VERSION_ID
AND RA.RESOURCE_ASSIGNMENT_ID = PRAC.RESOURCE_ASSIGNMENT_ID