View: MSC_PLANNED_RESOURCES_V

Product:	MSC - Advanced Supply Chain Planning
Description:	This view shows all the resources used in a plan
Implementation/DBA Data:	APPS.MSC_PLANNED_RESOURCES_V

View Text

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
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
, SM.FROM_LOCATION_NAME
, SM.FROM_LOCATION_DESCR
, SM.TO_LOCATION_ID
, SM.TO_LOCATION_NAME
, SM.TO_LOCATION_DESCR
, 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)
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
, 100*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
FROM MSC_OPERATION_RESOURCES MOR
, MSC_OPERATION_RESOURCE_SEQS MORS
, MSC_DEPARTMENT_RESOURCES RES
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

Columns