eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSC_PLANNED_RESOURCES_V

Source

View Text - Preformatted


SELECT RES.ROWID , RES.ORGANIZATION_ID , msc_get_name.org_code(res.organization_id, res.sr_instance_id) , RES.plan_id , RES.OWNING_DEPARTMENT_ID , decode(res.resource_id,-1, null, msc_get_name.department_code(2, res.owning_department_id, res.organization_id,res.plan_id,res.sr_instance_id)) , RES.DEPARTMENT_ID , res.DEPARTMENT_CODE , res.department_DESCRIPTION , res.DEPARTMENT_CLASS , RES.RESOURCE_ID , res.RESOURCE_CODE , res.RESOURCE_TYPE , msc_get_name.lookup_meaning('BOM_RESOURCE_TYPE',res.resource_type) , res.RESOURCE_GROUP_NAME , res.max_rate , res.min_rate , RES.RESOURCE_SHORTAGE_TYPE , RES.RESOURCE_EXCESS_TYPE , RES.OVERUTILIZED_PERCENT , RES.UNDERUTILIZED_PERCENT , RES.UTILIZATION , RES.EFFICIENCY , RES.LAST_UPDATE_DATE , RES.LAST_UPDATED_BY , RES.CREATION_DATE , RES.CREATED_BY , RES.LAST_UPDATE_LOGIN , RES.ATTRIBUTE_CATEGORY , RES.ATTRIBUTE1 , RES.ATTRIBUTE2 , RES.ATTRIBUTE3 , RES.ATTRIBUTE4 , RES.ATTRIBUTE5 , RES.ATTRIBUTE6 , RES.ATTRIBUTE7 , RES.ATTRIBUTE8 , RES.ATTRIBUTE9 , RES.ATTRIBUTE10 , RES.ATTRIBUTE11 , RES.ATTRIBUTE12 , RES.ATTRIBUTE13 , RES.ATTRIBUTE14 , RES.ATTRIBUTE15 , res.disable_date ,res.sr_instance_id ,res.resource_description ,to_number(null) ,null ,null ,to_number(null) ,null ,null ,to_number(null) ,null ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,res.aggregate_resource_flag ,res.available_24_hours_flag ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,null ,res.planning_exception_set ,res.resource_cost ,to_number(null) ,msc_get_name.lookup_meaning('SYS_YES_NO',decode(nvl(res.batchable_flag,2),0,2,res.batchable_flag)) ,res.batching_window ,res.min_capacity ,res.max_capacity ,res.unit_of_measure ,msc_get_name.batchable_uom(res.organization_id, res.department_id, res.resource_id) ,res.bottleneck_flag ,to_number(null) ,null ,res.chargeable_flag ,res.capacity_tolerance ,to_number(null) ,res.schedule_to_instance ,null ,null ,to_number(null) ,to_number(null) ,res.sds_scheduling_window from msc_department_resources RES union all SELECT sm.ROWID , sm.to_ORGANIZATION_ID , msc_get_name.org_code(sm.to_organization_id, sm.sr_instance_id) , sm.plan_id , to_number(null) , null , sm.from_organization_id , msc_get_name.org_code(sm.from_organization_id, sm.sr_instance_id) , null , null , sm.transaction_id , sm.ship_method , 100 , null , null , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , sm.LAST_UPDATE_DATE , sm.LAST_UPDATED_BY , sm.CREATION_DATE , sm.CREATED_BY , sm.LAST_UPDATE_LOGIN , sm.ATTRIBUTE_CATEGORY , sm.ATTRIBUTE1 , sm.ATTRIBUTE2 , sm.ATTRIBUTE3 , sm.ATTRIBUTE4 , sm.ATTRIBUTE5 , sm.ATTRIBUTE6 , sm.ATTRIBUTE7 , sm.ATTRIBUTE8 , sm.ATTRIBUTE9 , sm.ATTRIBUTE10 , sm.ATTRIBUTE11 , sm.ATTRIBUTE12 , sm.ATTRIBUTE13 , sm.ATTRIBUTE14 , sm.ATTRIBUTE15 , to_date(null) ,sm.sr_instance_id ,null ,sm.from_location_id ,msc_get_name.location_code( sm.from_organization_id, sm.from_location_id, sm.sr_instance_id) ,msc_get_name.location_code( sm.from_organization_id, sm.from_location_id, sm.sr_instance_id) ,sm.to_location_id ,msc_get_name.location_code( sm.to_organization_id, sm.to_location_id, sm.sr_instance_id) ,msc_get_name.location_code( sm.to_organization_id, sm.to_location_id, sm.sr_instance_id) ,sm.intransit_time ,sm.time_uom_code ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,null ,null ,cost_per_weight_unit ,to_number(null) ,null ,to_number(null) ,to_number(null) ,to_number(null) ,null ,null ,to_number(null) ,to_number(null) ,null ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,sm.weight_uom ,sm.volume_uom ,sm.weight_capacity ,sm.volume_capacity ,to_number(null) sds_scheduling_window from msc_interorg_ship_methods sm UNION SELECT RES.ROWID , RES.ORGANIZATION_ID , msc_get_name.org_code(res.organization_id, res.sr_instance_id) , RES.plan_id , RES.OWNING_DEPARTMENT_ID , decode(res.resource_id,-1, null, msc_get_name.department_code(2, res.owning_department_id, res.organization_id,res.plan_id,res.sr_instance_id)) , RES.DEPARTMENT_ID , res.DEPARTMENT_CODE , res.department_DESCRIPTION , res.DEPARTMENT_CLASS , RES.RESOURCE_ID , res.RESOURCE_CODE , res.RESOURCE_TYPE , msc_get_name.lookup_meaning('BOM_RESOURCE_TYPE',res.resource_type) , res.RESOURCE_GROUP_NAME , res.max_rate , res.min_rate , RES.RESOURCE_SHORTAGE_TYPE , RES.RESOURCE_EXCESS_TYPE , RES.OVERUTILIZED_PERCENT , RES.UNDERUTILIZED_PERCENT , RES.UTILIZATION , RES.EFFICIENCY , RES.LAST_UPDATE_DATE , RES.LAST_UPDATED_BY , RES.CREATION_DATE , RES.CREATED_BY , RES.LAST_UPDATE_LOGIN , RES.ATTRIBUTE_CATEGORY , RES.ATTRIBUTE1 , RES.ATTRIBUTE2 , RES.ATTRIBUTE3 , RES.ATTRIBUTE4 , RES.ATTRIBUTE5 , RES.ATTRIBUTE6 , RES.ATTRIBUTE7 , RES.ATTRIBUTE8 , RES.ATTRIBUTE9 , RES.ATTRIBUTE10 , RES.ATTRIBUTE11 , RES.ATTRIBUTE12 , RES.ATTRIBUTE13 , RES.ATTRIBUTE14 , RES.ATTRIBUTE15 , res.disable_date ,res.sr_instance_id ,res.resource_description ,to_number(null) ,null ,null ,to_number(null) ,null ,null ,to_number(null) ,null ,mors.routing_sequence_id ,mors.operation_sequence_id ,mors.resource_seq_num ,mors.resource_offset_percent ,mors.schedule_flag ,mor.resource_usage ,res.aggregate_resource_flag ,res.available_24_hours_flag ,mor.alternate_number ,mor.principal_flag ,mor.basis_type ,res.capacity_units ,mor.resource_units ,mor.uom_code ,res.planning_exception_set ,res.resource_cost ,mors.activity_group_id ,msc_get_name.lookup_meaning('SYS_YES_NO',decode(nvl(res.batchable_flag,2),0,2,res.batchable_flag)) ,res.batching_window ,res.min_capacity ,res.max_capacity ,res.unit_of_measure ,msc_get_name.batchable_uom(res.organization_id, res.department_id, res.resource_id) ,res.bottleneck_flag ,mor.setup_id ,mrs.setup_code ,res.chargeable_flag ,res.capacity_tolerance ,mor.breakable_activity_flag ,res.schedule_to_instance ,null ,null ,to_number(null) ,to_number(null) ,res.sds_scheduling_window from msc_operation_resources mor, msc_operation_resource_seqs mors, msc_department_resources RES, msc_resource_setups mrs WHERE res.plan_id = mors.plan_id and res.sr_instance_id = mors.sr_instance_id and res.department_id = mors.department_id and res.resource_id = mor.resource_id and mor.plan_id = mors.plan_id and mor.routing_sequence_id = mors.routing_sequence_id and mor.operation_sequence_id = mors.operation_sequence_id and mor.resource_seq_num = mors.resource_seq_num and mor.sr_instance_id = mors.sr_instance_id and mrs.plan_id (+) = mor.plan_id and mrs.sr_instance_id (+) = mor.sr_instance_id and mrs.organization_id (+) = mor.organization_id and mrs.resource_id (+) = mor.resource_id and mrs.setup_id (+) = mor.setup_id

View Text - HTML Formatted

SELECT RES.ROWID
, RES.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(RES.ORGANIZATION_ID
, RES.SR_INSTANCE_ID)
, RES.PLAN_ID
, RES.OWNING_DEPARTMENT_ID
, DECODE(RES.RESOURCE_ID
, -1
, NULL
, MSC_GET_NAME.DEPARTMENT_CODE(2
, RES.OWNING_DEPARTMENT_ID
, RES.ORGANIZATION_ID
, RES.PLAN_ID
, RES.SR_INSTANCE_ID))
, RES.DEPARTMENT_ID
, RES.DEPARTMENT_CODE
, RES.DEPARTMENT_DESCRIPTION
, RES.DEPARTMENT_CLASS
, RES.RESOURCE_ID
, RES.RESOURCE_CODE
, RES.RESOURCE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE'
, RES.RESOURCE_TYPE)
, RES.RESOURCE_GROUP_NAME
, RES.MAX_RATE
, RES.MIN_RATE
, RES.RESOURCE_SHORTAGE_TYPE
, RES.RESOURCE_EXCESS_TYPE
, RES.OVERUTILIZED_PERCENT
, RES.UNDERUTILIZED_PERCENT
, RES.UTILIZATION
, RES.EFFICIENCY
, RES.LAST_UPDATE_DATE
, RES.LAST_UPDATED_BY
, RES.CREATION_DATE
, RES.CREATED_BY
, RES.LAST_UPDATE_LOGIN
, RES.ATTRIBUTE_CATEGORY
, RES.ATTRIBUTE1
, RES.ATTRIBUTE2
, RES.ATTRIBUTE3
, RES.ATTRIBUTE4
, RES.ATTRIBUTE5
, RES.ATTRIBUTE6
, RES.ATTRIBUTE7
, RES.ATTRIBUTE8
, RES.ATTRIBUTE9
, RES.ATTRIBUTE10
, RES.ATTRIBUTE11
, RES.ATTRIBUTE12
, RES.ATTRIBUTE13
, RES.ATTRIBUTE14
, RES.ATTRIBUTE15
, RES.DISABLE_DATE
, RES.SR_INSTANCE_ID
, RES.RESOURCE_DESCRIPTION
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RES.AGGREGATE_RESOURCE_FLAG
, RES.AVAILABLE_24_HOURS_FLAG
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, RES.PLANNING_EXCEPTION_SET
, RES.RESOURCE_COST
, TO_NUMBER(NULL)
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, DECODE(NVL(RES.BATCHABLE_FLAG
, 2)
, 0
, 2
, RES.BATCHABLE_FLAG))
, RES.BATCHING_WINDOW
, RES.MIN_CAPACITY
, RES.MAX_CAPACITY
, RES.UNIT_OF_MEASURE
, MSC_GET_NAME.BATCHABLE_UOM(RES.ORGANIZATION_ID
, RES.DEPARTMENT_ID
, RES.RESOURCE_ID)
, RES.BOTTLENECK_FLAG
, TO_NUMBER(NULL)
, NULL
, RES.CHARGEABLE_FLAG
, RES.CAPACITY_TOLERANCE
, TO_NUMBER(NULL)
, RES.SCHEDULE_TO_INSTANCE
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RES.SDS_SCHEDULING_WINDOW
FROM MSC_DEPARTMENT_RESOURCES RES UNION ALL SELECT SM.ROWID
, SM.TO_ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(SM.TO_ORGANIZATION_ID
, SM.SR_INSTANCE_ID)
, SM.PLAN_ID
, TO_NUMBER(NULL)
, NULL
, SM.FROM_ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(SM.FROM_ORGANIZATION_ID
, SM.SR_INSTANCE_ID)
, NULL
, NULL
, SM.TRANSACTION_ID
, SM.SHIP_METHOD
, 100
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SM.LAST_UPDATE_DATE
, SM.LAST_UPDATED_BY
, SM.CREATION_DATE
, SM.CREATED_BY
, SM.LAST_UPDATE_LOGIN
, SM.ATTRIBUTE_CATEGORY
, SM.ATTRIBUTE1
, SM.ATTRIBUTE2
, SM.ATTRIBUTE3
, SM.ATTRIBUTE4
, SM.ATTRIBUTE5
, SM.ATTRIBUTE6
, SM.ATTRIBUTE7
, SM.ATTRIBUTE8
, SM.ATTRIBUTE9
, SM.ATTRIBUTE10
, SM.ATTRIBUTE11
, SM.ATTRIBUTE12
, SM.ATTRIBUTE13
, SM.ATTRIBUTE14
, SM.ATTRIBUTE15
, TO_DATE(NULL)
, SM.SR_INSTANCE_ID
, NULL
, SM.FROM_LOCATION_ID
, MSC_GET_NAME.LOCATION_CODE( SM.FROM_ORGANIZATION_ID
, SM.FROM_LOCATION_ID
, SM.SR_INSTANCE_ID)
, MSC_GET_NAME.LOCATION_CODE( SM.FROM_ORGANIZATION_ID
, SM.FROM_LOCATION_ID
, SM.SR_INSTANCE_ID)
, SM.TO_LOCATION_ID
, MSC_GET_NAME.LOCATION_CODE( SM.TO_ORGANIZATION_ID
, SM.TO_LOCATION_ID
, SM.SR_INSTANCE_ID)
, MSC_GET_NAME.LOCATION_CODE( SM.TO_ORGANIZATION_ID
, SM.TO_LOCATION_ID
, SM.SR_INSTANCE_ID)
, SM.INTRANSIT_TIME
, SM.TIME_UOM_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, COST_PER_WEIGHT_UNIT
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SM.WEIGHT_UOM
, SM.VOLUME_UOM
, SM.WEIGHT_CAPACITY
, SM.VOLUME_CAPACITY
, TO_NUMBER(NULL) SDS_SCHEDULING_WINDOW
FROM MSC_INTERORG_SHIP_METHODS SM UNION SELECT RES.ROWID
, RES.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(RES.ORGANIZATION_ID
, RES.SR_INSTANCE_ID)
, RES.PLAN_ID
, RES.OWNING_DEPARTMENT_ID
, DECODE(RES.RESOURCE_ID
, -1
, NULL
, MSC_GET_NAME.DEPARTMENT_CODE(2
, RES.OWNING_DEPARTMENT_ID
, RES.ORGANIZATION_ID
, RES.PLAN_ID
, RES.SR_INSTANCE_ID))
, RES.DEPARTMENT_ID
, RES.DEPARTMENT_CODE
, RES.DEPARTMENT_DESCRIPTION
, RES.DEPARTMENT_CLASS
, RES.RESOURCE_ID
, RES.RESOURCE_CODE
, RES.RESOURCE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE'
, RES.RESOURCE_TYPE)
, RES.RESOURCE_GROUP_NAME
, RES.MAX_RATE
, RES.MIN_RATE
, RES.RESOURCE_SHORTAGE_TYPE
, RES.RESOURCE_EXCESS_TYPE
, RES.OVERUTILIZED_PERCENT
, RES.UNDERUTILIZED_PERCENT
, RES.UTILIZATION
, RES.EFFICIENCY
, RES.LAST_UPDATE_DATE
, RES.LAST_UPDATED_BY
, RES.CREATION_DATE
, RES.CREATED_BY
, RES.LAST_UPDATE_LOGIN
, RES.ATTRIBUTE_CATEGORY
, RES.ATTRIBUTE1
, RES.ATTRIBUTE2
, RES.ATTRIBUTE3
, RES.ATTRIBUTE4
, RES.ATTRIBUTE5
, RES.ATTRIBUTE6
, RES.ATTRIBUTE7
, RES.ATTRIBUTE8
, RES.ATTRIBUTE9
, RES.ATTRIBUTE10
, RES.ATTRIBUTE11
, RES.ATTRIBUTE12
, RES.ATTRIBUTE13
, RES.ATTRIBUTE14
, RES.ATTRIBUTE15
, RES.DISABLE_DATE
, RES.SR_INSTANCE_ID
, RES.RESOURCE_DESCRIPTION
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, MORS.ROUTING_SEQUENCE_ID
, MORS.OPERATION_SEQUENCE_ID
, MORS.RESOURCE_SEQ_NUM
, MORS.RESOURCE_OFFSET_PERCENT
, MORS.SCHEDULE_FLAG
, MOR.RESOURCE_USAGE
, RES.AGGREGATE_RESOURCE_FLAG
, RES.AVAILABLE_24_HOURS_FLAG
, MOR.ALTERNATE_NUMBER
, MOR.PRINCIPAL_FLAG
, MOR.BASIS_TYPE
, RES.CAPACITY_UNITS
, MOR.RESOURCE_UNITS
, MOR.UOM_CODE
, RES.PLANNING_EXCEPTION_SET
, RES.RESOURCE_COST
, MORS.ACTIVITY_GROUP_ID
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, DECODE(NVL(RES.BATCHABLE_FLAG
, 2)
, 0
, 2
, RES.BATCHABLE_FLAG))
, RES.BATCHING_WINDOW
, RES.MIN_CAPACITY
, RES.MAX_CAPACITY
, RES.UNIT_OF_MEASURE
, MSC_GET_NAME.BATCHABLE_UOM(RES.ORGANIZATION_ID
, RES.DEPARTMENT_ID
, RES.RESOURCE_ID)
, RES.BOTTLENECK_FLAG
, MOR.SETUP_ID
, MRS.SETUP_CODE
, RES.CHARGEABLE_FLAG
, RES.CAPACITY_TOLERANCE
, MOR.BREAKABLE_ACTIVITY_FLAG
, RES.SCHEDULE_TO_INSTANCE
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RES.SDS_SCHEDULING_WINDOW
FROM MSC_OPERATION_RESOURCES MOR
, MSC_OPERATION_RESOURCE_SEQS MORS
, MSC_DEPARTMENT_RESOURCES RES
, MSC_RESOURCE_SETUPS MRS
WHERE RES.PLAN_ID = MORS.PLAN_ID
AND RES.SR_INSTANCE_ID = MORS.SR_INSTANCE_ID
AND RES.DEPARTMENT_ID = MORS.DEPARTMENT_ID
AND RES.RESOURCE_ID = MOR.RESOURCE_ID
AND MOR.PLAN_ID = MORS.PLAN_ID
AND MOR.ROUTING_SEQUENCE_ID = MORS.ROUTING_SEQUENCE_ID
AND MOR.OPERATION_SEQUENCE_ID = MORS.OPERATION_SEQUENCE_ID
AND MOR.RESOURCE_SEQ_NUM = MORS.RESOURCE_SEQ_NUM
AND MOR.SR_INSTANCE_ID = MORS.SR_INSTANCE_ID
AND MRS.PLAN_ID (+) = MOR.PLAN_ID
AND MRS.SR_INSTANCE_ID (+) = MOR.SR_INSTANCE_ID
AND MRS.ORGANIZATION_ID (+) = MOR.ORGANIZATION_ID
AND MRS.RESOURCE_ID (+) = MOR.RESOURCE_ID
AND MRS.SETUP_ID (+) = MOR.SETUP_ID