VIEW: APPS.MSC_AVAIL_RESOURCE_SUMMARY_V

Source

View Text - Preformatted

SELECT RES.ROWID , res.transaction_id , RES.ORGANIZATION_ID , msc_get_name.org_code(res.organization_id, res.sr_instance_id) , RES.plan_id , RES.DEPARTMENT_ID , DEP.department_code , RES.RESOURCE_ID , DEP.resource_code , RES.shift_DATE , bucket.bkt_END_DATE , res.capacity_units , decode(res.resource_id, -1, null,FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE')) , decode(res.resource_id, -1, RES.capacity_units, to_number(null)) , RES.UPDATED , RES.STATUS , RES.APPLIED , 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.sr_instance_id , to_number(null) , null , to_number(null) , null ,DEP.LINE_FLAG from msc_plan_buckets bucket, msc_net_resource_avail RES, msc_department_resources DEP WHERE res.parent_id =-1 and res.simulation_set is null and bucket.plan_id = res.plan_id and to_char(bucket.bkt_start_date,'J') = to_char(res.shift_date,'J') and res.capacity_units > 0 and res.plan_id <> -1 and res.plan_id = dep.plan_id and res.organization_id = dep.organization_id and res.sr_instance_id = dep.sr_instance_id and res.department_id = dep.department_id and res.resource_id = dep.resource_id union all select RES.ROWID , res.transaction_id , RES.ORGANIZATION_ID , msc_get_name.org_code(res.organization_id, res.sr_instance_id) , RES.plan_id , RES.DEPARTMENT_ID , DEP.department_code , RES.RESOURCE_ID , DEP.resource_code , RES.shift_DATE , res.shift_date , res.capacity_units*(res.to_time-res.from_time)/3600 , decode(res.resource_id,-1,null,FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE')) , decode(res.resource_id, -1, RES.capacity_units, to_number(null)) , RES.UPDATED , RES.STATUS , RES.APPLIED , 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.sr_instance_id , to_number(null) , null , to_number(null) , null ,DEP.line_flag from msc_net_resource_avail RES, msc_department_resources DEP where res.simulation_set is null and res.plan_id =-1 and res.from_time < res.to_time and res.plan_id = dep.plan_id and res.organization_id = dep.organization_id and res.sr_instance_id = dep.sr_instance_id and res.department_id = dep.department_id and res.resource_id = dep.resource_id union all select RES.ROWID , res.transaction_id , RES.ORGANIZATION_ID , msc_get_name.org_code(res.organization_id, res.sr_instance_id) , RES.plan_id , RES.DEPARTMENT_ID , DEP.department_code , RES.RESOURCE_ID ,DEP.resource_code , RES.shift_DATE , res.shift_date , res.capacity_units*(res.to_time+86400-res.from_time)/3600 , decode(res.resource_id, -1, null,FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE')) , decode(res.resource_id, -1, RES.capacity_units, to_number(null)) , RES.UPDATED , RES.STATUS , RES.APPLIED , 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.sr_instance_id , to_number(null) , null , to_number(null) , null ,DEP.line_flag from msc_net_resource_avail RES, msc_department_resources DEP where res.simulation_set is null and res.plan_id =-1 and res.to_time < res.from_time and res.plan_id = dep.plan_id and res.organization_id = dep.organization_id and res.sr_instance_id = dep.sr_instance_id and res.department_id = dep.department_id and res.resource_id = dep.resource_id UNION ALL SELECT sm.rowid , sm.transaction_id , sm.to_ORGANIZATION_ID , msc_get_name.org_code(sm.to_organization_id, sm.sr_instance_id) , sm.plan_id , sm.from_ORGANIZATION_ID , msc_get_name.org_code(sm.from_organization_id, sm.sr_instance_id) , sm.transaction_id , sm.ship_method , to_date(null) , to_date(null) , to_number(null) , 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 , sm.sr_instance_id , sm.weight_capacity , sm.weight_uom , sm.volume_capacity , sm.volume_uom , 2 from msc_interorg_ship_methods sm

View Text - HTML Formatted

SELECT RES.ROWID
, RES.TRANSACTION_ID
, RES.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(RES.ORGANIZATION_ID
, RES.SR_INSTANCE_ID)
, RES.PLAN_ID
, RES.DEPARTMENT_ID
, DEP.DEPARTMENT_CODE
, RES.RESOURCE_ID
, DEP.RESOURCE_CODE
, RES.SHIFT_DATE
, BUCKET.BKT_END_DATE
, RES.CAPACITY_UNITS
, DECODE(RES.RESOURCE_ID
, -1
, NULL
, FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE'))
, DECODE(RES.RESOURCE_ID
, -1
, RES.CAPACITY_UNITS
, TO_NUMBER(NULL))
, RES.UPDATED
, RES.STATUS
, RES.APPLIED
, 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.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, DEP.LINE_FLAG
FROM MSC_PLAN_BUCKETS BUCKET
, MSC_NET_RESOURCE_AVAIL RES
, MSC_DEPARTMENT_RESOURCES DEP
WHERE RES.PARENT_ID =-1
AND RES.SIMULATION_SET IS NULL
AND BUCKET.PLAN_ID = RES.PLAN_ID
AND TO_CHAR(BUCKET.BKT_START_DATE
, 'J') = TO_CHAR(RES.SHIFT_DATE
, 'J')
AND RES.CAPACITY_UNITS > 0
AND RES.PLAN_ID <> -1
AND RES.PLAN_ID = DEP.PLAN_ID
AND RES.ORGANIZATION_ID = DEP.ORGANIZATION_ID
AND RES.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND RES.DEPARTMENT_ID = DEP.DEPARTMENT_ID
AND RES.RESOURCE_ID = DEP.RESOURCE_ID UNION ALL SELECT RES.ROWID
, RES.TRANSACTION_ID
, RES.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(RES.ORGANIZATION_ID
, RES.SR_INSTANCE_ID)
, RES.PLAN_ID
, RES.DEPARTMENT_ID
, DEP.DEPARTMENT_CODE
, RES.RESOURCE_ID
, DEP.RESOURCE_CODE
, RES.SHIFT_DATE
, RES.SHIFT_DATE
, RES.CAPACITY_UNITS*(RES.TO_TIME-RES.FROM_TIME)/3600
, DECODE(RES.RESOURCE_ID
, -1
, NULL
, FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE'))
, DECODE(RES.RESOURCE_ID
, -1
, RES.CAPACITY_UNITS
, TO_NUMBER(NULL))
, RES.UPDATED
, RES.STATUS
, RES.APPLIED
, 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.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, DEP.LINE_FLAG
FROM MSC_NET_RESOURCE_AVAIL RES
, MSC_DEPARTMENT_RESOURCES DEP
WHERE RES.SIMULATION_SET IS NULL
AND RES.PLAN_ID =-1
AND RES.FROM_TIME < RES.TO_TIME
AND RES.PLAN_ID = DEP.PLAN_ID
AND RES.ORGANIZATION_ID = DEP.ORGANIZATION_ID
AND RES.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND RES.DEPARTMENT_ID = DEP.DEPARTMENT_ID
AND RES.RESOURCE_ID = DEP.RESOURCE_ID UNION ALL SELECT RES.ROWID
, RES.TRANSACTION_ID
, RES.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(RES.ORGANIZATION_ID
, RES.SR_INSTANCE_ID)
, RES.PLAN_ID
, RES.DEPARTMENT_ID
, DEP.DEPARTMENT_CODE
, RES.RESOURCE_ID
, DEP.RESOURCE_CODE
, RES.SHIFT_DATE
, RES.SHIFT_DATE
, RES.CAPACITY_UNITS*(RES.TO_TIME+86400-RES.FROM_TIME)/3600
, DECODE(RES.RESOURCE_ID
, -1
, NULL
, FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE'))
, DECODE(RES.RESOURCE_ID
, -1
, RES.CAPACITY_UNITS
, TO_NUMBER(NULL))
, RES.UPDATED
, RES.STATUS
, RES.APPLIED
, 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.SR_INSTANCE_ID
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, DEP.LINE_FLAG
FROM MSC_NET_RESOURCE_AVAIL RES
, MSC_DEPARTMENT_RESOURCES DEP
WHERE RES.SIMULATION_SET IS NULL
AND RES.PLAN_ID =-1
AND RES.TO_TIME < RES.FROM_TIME
AND RES.PLAN_ID = DEP.PLAN_ID
AND RES.ORGANIZATION_ID = DEP.ORGANIZATION_ID
AND RES.SR_INSTANCE_ID = DEP.SR_INSTANCE_ID
AND RES.DEPARTMENT_ID = DEP.DEPARTMENT_ID
AND RES.RESOURCE_ID = DEP.RESOURCE_ID UNION ALL SELECT SM.ROWID
, SM.TRANSACTION_ID
, SM.TO_ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(SM.TO_ORGANIZATION_ID
, SM.SR_INSTANCE_ID)
, SM.PLAN_ID
, SM.FROM_ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(SM.FROM_ORGANIZATION_ID
, SM.SR_INSTANCE_ID)
, SM.TRANSACTION_ID
, SM.SHIP_METHOD
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, 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
, SM.SR_INSTANCE_ID
, SM.WEIGHT_CAPACITY
, SM.WEIGHT_UOM
, SM.VOLUME_CAPACITY
, SM.VOLUME_UOM
, 2
FROM MSC_INTERORG_SHIP_METHODS SM