建立 WIP_OPERATIONS_V 重構連結方式
FROM WIP_OPERATIONS WO
INNER JOIN BOM_DEPARTMENTS BD ON WO.DEPARTMENT_ID = BD.DEPARTMENT_ID
LEFT
OUTER JOIN BOM_STANDARD_OPERATIONS BSO ON WO.STANDARD_OPERATION_ID =
BSO.STANDARD_OPERATION_ID
AND BSO.LINE_ID IS NULL
AND NVL
(BSO.OPERATION_TYPE, 1) = 1
LEFT OUTER JOIN MFG_LOOKUPS
LU1 ON WO.SHUTDOWN_TYPE = LU1.LOOKUP_CODE
AND
'BOM_EAM_SHUTDOWN_TYPE' = LU1.LOOKUP_TYPE
LEFT OUTER JOIN PER_ALL_PEOPLE_F PAP ON WO.EMPLOYEE_ID = PAP.PERSON_ID
以下寫法跟胡說八道幾乎相同
還是Oracle 原廠的瘋子寫的方式
原廠就是自大與偷懶...
不好意思說你智商低...
FROM BOM_DEPARTMENTS BD,
BOM_STANDARD_OPERATIONS BSO,
WIP_OPERATIONS WO,
MFG_LOOKUPS LU1,
PER_ALL_PEOPLE_F PAP
WHERE BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND BSO.STANDARD_OPERATION_ID(+) = WO.STANDARD_OPERATION_ID
AND NVL (BSO.OPERATION_TYPE, 1) = 1
AND BSO.LINE_ID IS NULL
AND LU1.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND LU1.LOOKUP_CODE(+) = WO.SHUTDOWN_TYPE
AND WO.EMPLOYEE_ID = PAP.PERSON_ID(+)
CEATE OR REPLACE FORCE VIEW APPS.WIP_OPERATIONS_V
( ROW_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID,
REPETITIVE_SCHEDULE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OPERATION_SEQUENCE_ID,
STANDARD_OPERATION_ID,
OPERATION_CODE,
DEPARTMENT_ID,
DEPARTMENT_CODE,
LOCATION_ID,
DESCRIPTION,
SCHEDULED_QUANTITY,
QUANTITY_IN_QUEUE,
QUANTITY_RUNNING,
QUANTITY_WAITING_TO_MOVE,
QUANTITY_REJECTED,
QUANTITY_SCRAPPED,
QUANTITY_COMPLETED,
FIRST_UNIT_START_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
PREVIOUS_OPERATION_SEQ_NUM,
NEXT_OPERATION_SEQ_NUM,
COUNT_POINT_TYPE,
COUNT_POINT_FLAG,
AUTOCHARGE_FLAG,
BACKFLUSH_FLAG,
MINIMUM_TRANSFER_QUANTITY,
DATE_LAST_MOVED,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OPERATION_YIELD,
CUMULATIVE_SCRAP_QUANTITY,
OPERATION_YIELD_ENABLED,
OPERATION_COMPLETED,
SHUTDOWN_TYPE,
SHUTDOWN_TYPE_DISP,
X_POS,
Y_POS,
LONG_DESCRIPTION,
DISABLE_DATE,
RECOMMENDED,
PROGRESS_PERCENTAGE,
WSM_BONUS_QUANTITY,
ACTUAL_START_DATE,
ACTUAL_COMPLETION_DATE,
EMPLOYEE_ID,
EMPLOYEE_NAME,
LOWEST_ACCEPTABLE_YIELD,
CHECK_SKILL
)
AS
SELECT WO.ROWID ROW_ID,
WO.WIP_ENTITY_ID,
WO.OPERATION_SEQ_NUM,
WO.ORGANIZATION_ID,
WO.REPETITIVE_SCHEDULE_ID,
WO.LAST_UPDATE_DATE,
WO.LAST_UPDATED_BY,
WO.CREATION_DATE,
WO.CREATED_BY,
WO.LAST_UPDATE_LOGIN,
WO.REQUEST_ID,
WO.PROGRAM_APPLICATION_ID,
WO.PROGRAM_ID,
WO.PROGRAM_UPDATE_DATE,
WO.OPERATION_SEQUENCE_ID,
WO.STANDARD_OPERATION_ID,
BSO.OPERATION_CODE,
WO.DEPARTMENT_ID,
BD.DEPARTMENT_CODE,
BD.LOCATION_ID,
WO.DESCRIPTION,
WO.SCHEDULED_QUANTITY,
DECODE (WO.QUANTITY_IN_QUEUE, 0, NULL, WO.QUANTITY_IN_QUEUE),
DECODE (WO.QUANTITY_RUNNING, 0, NULL, WO.QUANTITY_RUNNING),
DECODE (WO.QUANTITY_WAITING_TO_MOVE,
0, NULL,
WO.QUANTITY_WAITING_TO_MOVE),
DECODE (WO.QUANTITY_REJECTED, 0, NULL, WO.QUANTITY_REJECTED),
DECODE (WO.QUANTITY_SCRAPPED, 0, NULL, WO.QUANTITY_SCRAPPED),
DECODE (WO.QUANTITY_COMPLETED, 0, NULL, WO.QUANTITY_COMPLETED),
WO.FIRST_UNIT_START_DATE,
WO.FIRST_UNIT_COMPLETION_DATE,
WO.LAST_UNIT_START_DATE,
WO.LAST_UNIT_COMPLETION_DATE,
WO.PREVIOUS_OPERATION_SEQ_NUM,
WO.NEXT_OPERATION_SEQ_NUM,
WO.COUNT_POINT_TYPE,
DECODE (WO.COUNT_POINT_TYPE, 1, 1, 2) "COUNT_POINT_FLAG",
DECODE (WO.COUNT_POINT_TYPE, 3, 2, 1) "AUTOCHARGE_FLAG",
WO.BACKFLUSH_FLAG,
WO.MINIMUM_TRANSFER_QUANTITY,
WO.DATE_LAST_MOVED,
WO.ATTRIBUTE_CATEGORY,
WO.ATTRIBUTE1,
WO.ATTRIBUTE2,
WO.ATTRIBUTE3,
WO.ATTRIBUTE4,
WO.ATTRIBUTE5,
WO.ATTRIBUTE6,
WO.ATTRIBUTE7,
WO.ATTRIBUTE8,
WO.ATTRIBUTE9,
WO.ATTRIBUTE10,
WO.ATTRIBUTE11,
WO.ATTRIBUTE12,
WO.ATTRIBUTE13,
WO.ATTRIBUTE14,
WO.ATTRIBUTE15,
WO.OPERATION_YIELD,
WO.CUMULATIVE_SCRAP_QUANTITY,
WO.OPERATION_YIELD_ENABLED,
NVL (WO.OPERATION_COMPLETED, 'N'),
WO.SHUTDOWN_TYPE,
LU1.MEANING,
WO.X_POS,
WO.Y_POS,
WO.LONG_DESCRIPTION,
WO.DISABLE_DATE,
WO.RECOMMENDED,
WO.PROGRESS_PERCENTAGE,
WO.WSM_BONUS_QUANTITY,
WO.ACTUAL_START_DATE,
WO.ACTUAL_COMPLETION_DATE,
WO.EMPLOYEE_ID,
PAP.FULL_NAME,
WO.LOWEST_ACCEPTABLE_YIELD,
NVL (wo.CHECK_SKILL, 2) CHECK_SKILL
FROM WIP_OPERATIONS WO
INNER JOIN BOM_DEPARTMENTS BD ON WO.DEPARTMENT_ID = BD.DEPARTMENT_ID
LEFT OUTER JOIN BOM_STANDARD_OPERATIONS BSO ON WO.STANDARD_OPERATION_ID= BSO.STANDARD_OPERATION_ID AND BSO.LINE_ID IS NULL AND NVL (BSO.OPERATION_TYPE, 1) = 1
LEFT OUTER JOIN MFG_LOOKUPS LU1 ON WO.SHUTDOWN_TYPE = LU1.LOOKUP_CODE AND 'BOM_EAM_SHUTDOWN_TYPE'= LU1.LOOKUP_TYPE
LEFT OUTER JOIN PER_ALL_PEOPLE_F PAP ON WO.EMPLOYEE_ID = PAP.PERSON_ID
WHERE BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND BSO.STANDARD_OPERATION_ID(+) = WO.STANDARD_OPERATION_ID
AND NVL (BSO.OPERATION_TYPE, 1) = 1
AND BSO.LINE_ID IS NULL
AND LU1.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND LU1.LOOKUP_CODE(+) = WO.SHUTDOWN_TYPE
AND WO.EMPLOYEE_ID = PAP.PERSON_ID(+)
ORDER BY WO.OPERATION_SEQ_NUM;
沒有留言:
張貼留言