WIE_WO_OPERATIONS_COMPLETE

Details

Schema: FUSION
Object owner: WIE
Object type: VIEW

Columns

Name
WORK_ORDER_ID WO_OPERATION_ID WORK_ORDER_STATUS_ID PLANNED_COMPLETION_DATE ORGANIZATION_ID WORK_CENTER_ID COMPLETED_QUANTITY WORK_AREA_ID SHIFT_AFFINITY START_DATE_TIME END_DATE_TIME WORK_METHOD_CODE

Name

WORK_ORDER_ID

WO_OPERATION_ID

WORK_ORDER_STATUS_ID

PLANNED_COMPLETION_DATE

ORGANIZATION_ID

WORK_CENTER_ID

COMPLETED_QUANTITY

WORK_AREA_ID

SHIFT_AFFINITY

START_DATE_TIME

END_DATE_TIME

WORK_METHOD_CODE

Query

SQL_Statement
SELECT WO.WORK_ORDER_ID WORK_ORDER_ID, OPER.WO_OPERATION_ID WO_OPERATION_ID, WO.WORK_ORDER_STATUS_ID WORK_ORDER_STATUS_ID, OPER.PLANNED_COMPLETION_DATE PLANNED_COMPLETION_DATE, OPER.ORGANIZATION_ID ORGANIZATION_ID, OPER.WORK_CENTER_ID WORK_CENTER_ID, OPER.COMPLETED_QUANTITY COMPLETED_QUANTITY, WC.WORK_AREA_ID WORK_AREA_ID, SHIF.SHIFT_AFFINITY SHIFT_AFFINITY, SHIF.START_DATE_TIME START_DATE_TIME, SHIF.END_DATE_TIME END_DATE_TIME, MT.WORK_METHOD_CODE WORK_METHOD_CODE FROM WIE_WORK_ORDERS_B WO, WIE_WO_OPERATIONS_B OPER, WIS_WORK_CENTERS_B WC, WIE_CURRENT_SHIFTS_V SHIF, WIS_WORK_METHODS_B MT WHERE OPER.WORK_ORDER_ID = WO.WORK_ORDER_ID AND WO.ORDER_LESS_FLAG = 'N' AND WO.WORK_ORDER_STATUS_ID IN (SELECT WOSTS.WO_STATUS_ID FROM WIE_WO_STATUSES_B WOSTS WHERE WOSTS.WO_SYSTEM_STATUS_CODE IN('RELEASED', 'COMPLETED')) AND WC.WORK_CENTER_ID = OPER.WORK_CENTER_ID AND SHIF.ORGANIZATION_ID = OPER.ORGANIZATION_ID AND OPER.PLANNED_COMPLETION_DATE IS NOT NULL AND OPER.AUTO_TRANSACT_FLAG = 'N' AND OPER.COMPLETED_QUANTITY > 0 AND SHIF.SHIFT_AFFINITY IN ('PREVIOUS_SHIFT','CURRENT_SHIFT','NEXT_SHIFT') AND OPER.PLANNED_COMPLETION_DATE >= FND_TIMEZONE_PUB.ADJUST_DATETIME(SHIF.START_DATE_TIME, FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) AND OPER.PLANNED_COMPLETION_DATE <= FND_TIMEZONE_PUB.ADJUST_DATETIME(SHIF.END_DATE_TIME, FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) AND WO.WORK_METHOD_ID = MT.WORK_METHOD_ID

SQL_Statement

SELECT WO.WORK_ORDER_ID WORK_ORDER_ID,

OPER.WO_OPERATION_ID WO_OPERATION_ID,

WO.WORK_ORDER_STATUS_ID WORK_ORDER_STATUS_ID,

OPER.PLANNED_COMPLETION_DATE PLANNED_COMPLETION_DATE,

OPER.ORGANIZATION_ID ORGANIZATION_ID,

OPER.WORK_CENTER_ID WORK_CENTER_ID,

OPER.COMPLETED_QUANTITY COMPLETED_QUANTITY,

WC.WORK_AREA_ID WORK_AREA_ID,

SHIF.SHIFT_AFFINITY SHIFT_AFFINITY,

SHIF.START_DATE_TIME START_DATE_TIME,

SHIF.END_DATE_TIME END_DATE_TIME,

MT.WORK_METHOD_CODE WORK_METHOD_CODE

FROM WIE_WORK_ORDERS_B WO,

WIE_WO_OPERATIONS_B OPER,

WIS_WORK_CENTERS_B WC,

WIE_CURRENT_SHIFTS_V SHIF,

WIS_WORK_METHODS_B MT

WHERE OPER.WORK_ORDER_ID = WO.WORK_ORDER_ID

AND WO.ORDER_LESS_FLAG = 'N'

AND WO.WORK_ORDER_STATUS_ID IN

(SELECT WOSTS.WO_STATUS_ID

FROM WIE_WO_STATUSES_B WOSTS

WHERE WOSTS.WO_SYSTEM_STATUS_CODE IN('RELEASED', 'COMPLETED'))

AND WC.WORK_CENTER_ID = OPER.WORK_CENTER_ID

AND SHIF.ORGANIZATION_ID = OPER.ORGANIZATION_ID

AND OPER.PLANNED_COMPLETION_DATE IS NOT NULL

AND OPER.AUTO_TRANSACT_FLAG = 'N'

AND OPER.COMPLETED_QUANTITY > 0

AND SHIF.SHIFT_AFFINITY IN ('PREVIOUS_SHIFT','CURRENT_SHIFT','NEXT_SHIFT')

AND OPER.PLANNED_COMPLETION_DATE >= FND_TIMEZONE_PUB.ADJUST_DATETIME(SHIF.START_DATE_TIME, FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE)

AND OPER.PLANNED_COMPLETION_DATE <= FND_TIMEZONE_PUB.ADJUST_DATETIME(SHIF.END_DATE_TIME, FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE)

AND WO.WORK_METHOD_ID = MT.WORK_METHOD_ID