BOM_STANDARD_OPERATIONS_V
ALTER view [dbo].[BOM_STANDARD_OPERATIONS_V]
as
SELECT WL.LINE_CODE, BSO.SEQUENCE_NUM, BSO.LINE_ID, BSO.OPERATION_TYPE,
BSO.STANDARD_OPERATION_ID, BSO.OPERATION_CODE,
BSO.ORGANIZATION_ID, BSO.DEPARTMENT_ID,
BD.DEPARTMENT_CODE DEPARTMENT_CODE,
BD.DESCRIPTION DEPARTMENT_DESCRIPTION, BD.LOCATION_ID,
BSO.LAST_UPDATE_DATE, BSO.LAST_UPDATED_BY, BSO.CREATION_DATE,
BSO.CREATED_BY, BSO.LAST_UPDATE_LOGIN,
BSO.MINIMUM_TRANSFER_QUANTITY, BSO.COUNT_POINT_TYPE,
BSO.OPERATION_DESCRIPTION, BSO.OPTION_DEPENDENT_FLAG,
BSO.ATTRIBUTE_CATEGORY, BSO.ATTRIBUTE1, BSO.ATTRIBUTE2,
BSO.ATTRIBUTE3, BSO.ATTRIBUTE4, BSO.ATTRIBUTE5, BSO.ATTRIBUTE6,
BSO.ATTRIBUTE7, BSO.ATTRIBUTE8, BSO.ATTRIBUTE9, BSO.ATTRIBUTE10,
BSO.ATTRIBUTE11, BSO.ATTRIBUTE12, BSO.ATTRIBUTE13, BSO.ATTRIBUTE14,
BSO.ATTRIBUTE15, BSO.REQUEST_ID, BSO.PROGRAM_APPLICATION_ID,
BSO.PROGRAM_ID, BSO.PROGRAM_UPDATE_DATE, BSO.BACKFLUSH_FLAG,
CASE WHEN BSO.COUNT_POINT_TYPE = 1 THEN 1 ELSE 2 END COUNT_POINT_FLAG,
CASE WHEN BSO.COUNT_POINT_TYPE= 3 THEN 2 ELSE 1 END AUTOCHARGE_FLAG,
BSO.WMS_TASK_TYPE, BSO.OPERATION_YIELD_ENABLED, BSO.YIELD,
BSO.SHUTDOWN_TYPE, ML.MEANING SHUTDOWN_TYPE_VALUE,
BSO.DEFAULT_SUBINVENTORY, BSO.DEFAULT_LOCATOR_ID,
BSO.CRITICAL_TO_QUALITY, BSO.VALUE_ADDED
FROM bom_standard_operations bso
inner join bom_departments bd on bd.department_id = bso.department_id
left join wip..wip_lines wl on wl.line_id = bso.line_id
left join mfg_lookups ml on ml.lookup_code = bso.shutdown_type
AND ml.lookup_type = 'BOM_EAM_SHUTDOWN_TYPE';
as
SELECT WL.LINE_CODE, BSO.SEQUENCE_NUM, BSO.LINE_ID, BSO.OPERATION_TYPE,
BSO.STANDARD_OPERATION_ID, BSO.OPERATION_CODE,
BSO.ORGANIZATION_ID, BSO.DEPARTMENT_ID,
BD.DEPARTMENT_CODE DEPARTMENT_CODE,
BD.DESCRIPTION DEPARTMENT_DESCRIPTION, BD.LOCATION_ID,
BSO.LAST_UPDATE_DATE, BSO.LAST_UPDATED_BY, BSO.CREATION_DATE,
BSO.CREATED_BY, BSO.LAST_UPDATE_LOGIN,
BSO.MINIMUM_TRANSFER_QUANTITY, BSO.COUNT_POINT_TYPE,
BSO.OPERATION_DESCRIPTION, BSO.OPTION_DEPENDENT_FLAG,
BSO.ATTRIBUTE_CATEGORY, BSO.ATTRIBUTE1, BSO.ATTRIBUTE2,
BSO.ATTRIBUTE3, BSO.ATTRIBUTE4, BSO.ATTRIBUTE5, BSO.ATTRIBUTE6,
BSO.ATTRIBUTE7, BSO.ATTRIBUTE8, BSO.ATTRIBUTE9, BSO.ATTRIBUTE10,
BSO.ATTRIBUTE11, BSO.ATTRIBUTE12, BSO.ATTRIBUTE13, BSO.ATTRIBUTE14,
BSO.ATTRIBUTE15, BSO.REQUEST_ID, BSO.PROGRAM_APPLICATION_ID,
BSO.PROGRAM_ID, BSO.PROGRAM_UPDATE_DATE, BSO.BACKFLUSH_FLAG,
CASE WHEN BSO.COUNT_POINT_TYPE = 1 THEN 1 ELSE 2 END COUNT_POINT_FLAG,
CASE WHEN BSO.COUNT_POINT_TYPE= 3 THEN 2 ELSE 1 END AUTOCHARGE_FLAG,
BSO.WMS_TASK_TYPE, BSO.OPERATION_YIELD_ENABLED, BSO.YIELD,
BSO.SHUTDOWN_TYPE, ML.MEANING SHUTDOWN_TYPE_VALUE,
BSO.DEFAULT_SUBINVENTORY, BSO.DEFAULT_LOCATOR_ID,
BSO.CRITICAL_TO_QUALITY, BSO.VALUE_ADDED
FROM bom_standard_operations bso
inner join bom_departments bd on bd.department_id = bso.department_id
left join wip..wip_lines wl on wl.line_id = bso.line_id
left join mfg_lookups ml on ml.lookup_code = bso.shutdown_type
AND ml.lookup_type = 'BOM_EAM_SHUTDOWN_TYPE';