VIEW: APPS.MSC_RES_AND_INST_V

Source

View Text - Preformatted

SELECT mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, mdr.owning_department_id, msc_get_name.org_code(mdr.organization_id, mdr.sr_instance_id) org_code, mdr.department_class dept_class, decode(mdr.resource_id,-1, null, msc_get_name.department_code(2, mdr.owning_department_id, mdr.organization_id,mdr.plan_id,mdr.sr_instance_id)) owning_dept_code, mdr.department_code dept_code, mdr.department_description dept_desc, mdr.resource_group_name res_group_name, mdr.resource_code res_code, mdr.resource_description res_desc, mdr.resource_type, msc_get_name.lookup_meaning('BOM_RESOURCE_TYPE',mdr.resource_type) resource_type_text, to_number(null) res_instance_id, null res_instance_code, null equipment_item_name, null serial_number, mdr.resource_shortage_type, mdr.resource_excess_type, mdr.overutilized_percent, mdr.underutilized_percent, mdr.utilization, mdr.efficiency, mdr.aggregate_resource_flag, mdr.available_24_hours_flag, mdr.batchable_flag, mdr.unit_of_measure, mdr.bottleneck_flag, mdr.chargeable_flag, mdr.capacity_tolerance, msc_gantt_utils.isExcpExists(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, 21) RES_OVERLOAD_EXCP, msc_gantt_utils.isExcpExists(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, 36) RES_CONST_EXCP, msc_gantt_utils.isExcpExists(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, 90) RES_EXC_SETUPS_EXCP, msc_gantt_utils.isExcpExists(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, 91) RES_UTILIZATION_EXCP from msc_department_resources mdr union all select mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, mdr.owning_department_id, msc_get_name.org_code(mdr.organization_id, mdr.sr_instance_id) org_code, mdr.department_class dept_class, decode(mdr.resource_id,-1, null, msc_get_name.department_code(2, mdr.owning_department_id, mdr.organization_id,mdr.plan_id,mdr.sr_instance_id)) owning_dept_code, mdr.department_code dept_code, mdr.department_description dept_desc, mdr.resource_group_name res_group_name, mdr.resource_code res_code, mdr.resource_description res_desc, mdr.resource_type, msc_get_name.lookup_meaning('BOM_RESOURCE_TYPE',mdr.resource_type) resource_type_text, mdri.res_instance_id, msc_get_name.item_name(mdri.equipment_item_id, null, null, null) ||decode(mdri.serial_number, null, '', ':'||mdri.serial_number) res_instance_code, msc_get_name.item_name(mdri.equipment_item_id, null, null, null) equipment_item_name, mdri.serial_number, mdr.resource_shortage_type, mdr.resource_excess_type, mdr.overutilized_percent, mdr.underutilized_percent, mdr.utilization, mdr.efficiency, mdr.aggregate_resource_flag, mdr.available_24_hours_flag, mdr.batchable_flag, mdr.unit_of_measure, mdr.bottleneck_flag, mdr.chargeable_flag, mdr.capacity_tolerance, msc_gantt_utils.isExcpExists(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, 21) RES_OVERLOAD_EXCP, msc_gantt_utils.isExcpExists(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, 36) RES_CONST_EXCP, msc_gantt_utils.isExcpExists(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, 90) RES_EXC_SETUPS_EXCP, msc_gantt_utils.isExcpExists(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id, 91) RES_UTILIZATION_EXCP from msc_department_resources mdr, msc_dept_res_instances mdri where mdr.plan_id = mdri.plan_id and mdr.sr_instance_id = mdri.sr_instance_id and mdr.organization_id = mdri.organization_id and mdr.department_id = mdri.department_id and mdr.resource_id = mdri.resource_id

View Text - HTML Formatted

SELECT MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, MDR.OWNING_DEPARTMENT_ID
, MSC_GET_NAME.ORG_CODE(MDR.ORGANIZATION_ID
, MDR.SR_INSTANCE_ID) ORG_CODE
, MDR.DEPARTMENT_CLASS DEPT_CLASS
, DECODE(MDR.RESOURCE_ID
, -1
, NULL
, MSC_GET_NAME.DEPARTMENT_CODE(2
, MDR.OWNING_DEPARTMENT_ID
, MDR.ORGANIZATION_ID
, MDR.PLAN_ID
, MDR.SR_INSTANCE_ID)) OWNING_DEPT_CODE
, MDR.DEPARTMENT_CODE DEPT_CODE
, MDR.DEPARTMENT_DESCRIPTION DEPT_DESC
, MDR.RESOURCE_GROUP_NAME RES_GROUP_NAME
, MDR.RESOURCE_CODE RES_CODE
, MDR.RESOURCE_DESCRIPTION RES_DESC
, MDR.RESOURCE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE'
, MDR.RESOURCE_TYPE) RESOURCE_TYPE_TEXT
, TO_NUMBER(NULL) RES_INSTANCE_ID
, NULL RES_INSTANCE_CODE
, NULL EQUIPMENT_ITEM_NAME
, NULL SERIAL_NUMBER
, MDR.RESOURCE_SHORTAGE_TYPE
, MDR.RESOURCE_EXCESS_TYPE
, MDR.OVERUTILIZED_PERCENT
, MDR.UNDERUTILIZED_PERCENT
, MDR.UTILIZATION
, MDR.EFFICIENCY
, MDR.AGGREGATE_RESOURCE_FLAG
, MDR.AVAILABLE_24_HOURS_FLAG
, MDR.BATCHABLE_FLAG
, MDR.UNIT_OF_MEASURE
, MDR.BOTTLENECK_FLAG
, MDR.CHARGEABLE_FLAG
, MDR.CAPACITY_TOLERANCE
, MSC_GANTT_UTILS.ISEXCPEXISTS(MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, 21) RES_OVERLOAD_EXCP
, MSC_GANTT_UTILS.ISEXCPEXISTS(MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, 36) RES_CONST_EXCP
, MSC_GANTT_UTILS.ISEXCPEXISTS(MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, 90) RES_EXC_SETUPS_EXCP
, MSC_GANTT_UTILS.ISEXCPEXISTS(MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, 91) RES_UTILIZATION_EXCP
FROM MSC_DEPARTMENT_RESOURCES MDR UNION ALL SELECT MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, MDR.OWNING_DEPARTMENT_ID
, MSC_GET_NAME.ORG_CODE(MDR.ORGANIZATION_ID
, MDR.SR_INSTANCE_ID) ORG_CODE
, MDR.DEPARTMENT_CLASS DEPT_CLASS
, DECODE(MDR.RESOURCE_ID
, -1
, NULL
, MSC_GET_NAME.DEPARTMENT_CODE(2
, MDR.OWNING_DEPARTMENT_ID
, MDR.ORGANIZATION_ID
, MDR.PLAN_ID
, MDR.SR_INSTANCE_ID)) OWNING_DEPT_CODE
, MDR.DEPARTMENT_CODE DEPT_CODE
, MDR.DEPARTMENT_DESCRIPTION DEPT_DESC
, MDR.RESOURCE_GROUP_NAME RES_GROUP_NAME
, MDR.RESOURCE_CODE RES_CODE
, MDR.RESOURCE_DESCRIPTION RES_DESC
, MDR.RESOURCE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE'
, MDR.RESOURCE_TYPE) RESOURCE_TYPE_TEXT
, MDRI.RES_INSTANCE_ID
, MSC_GET_NAME.ITEM_NAME(MDRI.EQUIPMENT_ITEM_ID
, NULL
, NULL
, NULL) ||DECODE(MDRI.SERIAL_NUMBER
, NULL
, ''
, ':'||MDRI.SERIAL_NUMBER) RES_INSTANCE_CODE
, MSC_GET_NAME.ITEM_NAME(MDRI.EQUIPMENT_ITEM_ID
, NULL
, NULL
, NULL) EQUIPMENT_ITEM_NAME
, MDRI.SERIAL_NUMBER
, MDR.RESOURCE_SHORTAGE_TYPE
, MDR.RESOURCE_EXCESS_TYPE
, MDR.OVERUTILIZED_PERCENT
, MDR.UNDERUTILIZED_PERCENT
, MDR.UTILIZATION
, MDR.EFFICIENCY
, MDR.AGGREGATE_RESOURCE_FLAG
, MDR.AVAILABLE_24_HOURS_FLAG
, MDR.BATCHABLE_FLAG
, MDR.UNIT_OF_MEASURE
, MDR.BOTTLENECK_FLAG
, MDR.CHARGEABLE_FLAG
, MDR.CAPACITY_TOLERANCE
, MSC_GANTT_UTILS.ISEXCPEXISTS(MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, 21) RES_OVERLOAD_EXCP
, MSC_GANTT_UTILS.ISEXCPEXISTS(MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, 36) RES_CONST_EXCP
, MSC_GANTT_UTILS.ISEXCPEXISTS(MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, 90) RES_EXC_SETUPS_EXCP
, MSC_GANTT_UTILS.ISEXCPEXISTS(MDR.PLAN_ID
, MDR.SR_INSTANCE_ID
, MDR.ORGANIZATION_ID
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, 91) RES_UTILIZATION_EXCP
FROM MSC_DEPARTMENT_RESOURCES MDR
, MSC_DEPT_RES_INSTANCES MDRI
WHERE MDR.PLAN_ID = MDRI.PLAN_ID
AND MDR.SR_INSTANCE_ID = MDRI.SR_INSTANCE_ID
AND MDR.ORGANIZATION_ID = MDRI.ORGANIZATION_ID
AND MDR.DEPARTMENT_ID = MDRI.DEPARTMENT_ID
AND MDR.RESOURCE_ID = MDRI.RESOURCE_ID