VIEW: APPS.MSC_RESOURCE_BATCHES_V

Source

View Text - Preformatted

SELECT distinct mrb.rowid row_id ,res.department_code , res.resource_code , mrb.batch_number , mrb.start_date , mrb.end_date , mrb.resource_hours , res.min_capacity , res.max_capacity , mrb.batch_capacity_used , trunc(decode(nvl(res.max_capacity,0) , 0 , null , mrb.batch_capacity_used / (nvl(res.max_capacity,0) * nvl(msc_get_name.get_res_units(mrb.plan_id,mrb.sr_instance_id, mrb.organization_id,mrb.department_id, mrb.resource_id, mrb.start_date,mrb.end_date),1)) * 100) , 3) , msc_get_name.batchable_uom(res.organization_id, res.department_id, res.resource_id) , msc_get_name.org_code(mrb.organization_id, mrb.sr_instance_id) , mrb.plan_id , mrb.sr_instance_id , mrb.organization_id , mrb.department_id , mrb.resource_id , res.department_description , res.department_class , res.resource_type , res.resource_group_name , 'MSC_DEPARTMENT_RESOURCES' from msc_resource_batches mrb, msc_department_resources res WHERE mrb.plan_id = res.plan_id and mrb.sr_instance_id = res.sr_instance_id and mrb.department_id = res.department_id and mrb.resource_id = res.resource_id and mrb.organization_id = res.organization_id and nvl(res.batchable_flag ,2) = 1 and res.resource_id <> -1

View Text - HTML Formatted

SELECT DISTINCT MRB.ROWID ROW_ID
, RES.DEPARTMENT_CODE
, RES.RESOURCE_CODE
, MRB.BATCH_NUMBER
, MRB.START_DATE
, MRB.END_DATE
, MRB.RESOURCE_HOURS
, RES.MIN_CAPACITY
, RES.MAX_CAPACITY
, MRB.BATCH_CAPACITY_USED
, TRUNC(DECODE(NVL(RES.MAX_CAPACITY
, 0)
, 0
, NULL
, MRB.BATCH_CAPACITY_USED / (NVL(RES.MAX_CAPACITY
, 0) * NVL(MSC_GET_NAME.GET_RES_UNITS(MRB.PLAN_ID
, MRB.SR_INSTANCE_ID
, MRB.ORGANIZATION_ID
, MRB.DEPARTMENT_ID
, MRB.RESOURCE_ID
, MRB.START_DATE
, MRB.END_DATE)
, 1)) * 100)
, 3)
, MSC_GET_NAME.BATCHABLE_UOM(RES.ORGANIZATION_ID
, RES.DEPARTMENT_ID
, RES.RESOURCE_ID)
, MSC_GET_NAME.ORG_CODE(MRB.ORGANIZATION_ID
, MRB.SR_INSTANCE_ID)
, MRB.PLAN_ID
, MRB.SR_INSTANCE_ID
, MRB.ORGANIZATION_ID
, MRB.DEPARTMENT_ID
, MRB.RESOURCE_ID
, RES.DEPARTMENT_DESCRIPTION
, RES.DEPARTMENT_CLASS
, RES.RESOURCE_TYPE
, RES.RESOURCE_GROUP_NAME
, 'MSC_DEPARTMENT_RESOURCES'
FROM MSC_RESOURCE_BATCHES MRB
, MSC_DEPARTMENT_RESOURCES RES
WHERE MRB.PLAN_ID = RES.PLAN_ID
AND MRB.SR_INSTANCE_ID = RES.SR_INSTANCE_ID
AND MRB.DEPARTMENT_ID = RES.DEPARTMENT_ID
AND MRB.RESOURCE_ID = RES.RESOURCE_ID
AND MRB.ORGANIZATION_ID = RES.ORGANIZATION_ID
AND NVL(RES.BATCHABLE_FLAG
, 2) = 1
AND RES.RESOURCE_ID <> -1