MSS_FS_OPERATIONS

Details

Schema: FUSION
Object owner: MSS
Object type: VIEW

Columns

Name
PLAN_ID WO_OPERATION_ID WORK_ORDER_ID RESOURCE_SEQ_NUMBER OPERATION_SEQ_NUMBER OPERATION_CODE OPERATION_NAME FS_OPERATION_TYPE FS_WD_HEADER_ID WORK_DEFINITION_ID FS_COMPLETION_DATE WD_OPERATION_ID LINE_OP_SEQ_NUMBER STATUS WORK_CENTER_ID DURATION_RES_ID LOT_MULTIPLE_RES_ID LINE_RATE

Name

PLAN_ID

WO_OPERATION_ID

WORK_ORDER_ID

RESOURCE_SEQ_NUMBER

OPERATION_SEQ_NUMBER

OPERATION_CODE

OPERATION_NAME

FS_OPERATION_TYPE

FS_WD_HEADER_ID

WORK_DEFINITION_ID

FS_COMPLETION_DATE

WD_OPERATION_ID

LINE_OP_SEQ_NUMBER

STATUS

WORK_CENTER_ID

DURATION_RES_ID

LOT_MULTIPLE_RES_ID

LINE_RATE

Query

SQL_Statement
with fsOp as ( Select distinct wo.PLAN_ID, wo.WORK_ORDER_ID, 10 RESOURCE_SEQ_NUMBER, fsWdOpMat.OPERATION_SEQ_NUMBER, item.ITEM_CODE \|\| '::' \|\| fsWdOpMat.OPERATION_SEQ_NUMBER OPERATION_CODE, 'OP' \|\| fsWdOpMat.OPERATION_SEQ_NUMBER OPERATION_NAME, 'Line Operation' FS_OPERATION_TYPE, fsWdHdr.FS_WD_HEADER_ID, fsWdHdr.WORK_DEFINITION_ID, fsWdHdr.FS_COMPLETION_DATE, fsWdOpMat.WD_OPERATION_ID, fsWdOpMat.LINE_OP_SEQ_NUMBER, 'Open' STATUS, r.WORK_CENTER_ID, r.RESOURCE_ID DURATION_RES_ID, r.RESOURCE_ID LOT_MULTIPLE_RES_ID, pLine.LINE_RATE from MSS_WORK_ORDERS wo join MSS_PRODUCTION_LINES pLine on wo.PLAN_ID = pLine.PLAN_ID and wo.PRODUCTION_LINE_ID = pLine.PRODUCTION_LINE_ID join MSS_FS_RESOURCES_V r on pLine.PLAN_ID = r.PLAN_ID and pLine.PRODUCTION_LINE_ID = r.PRODUCTION_LINE_ID join MSS_ITEMS item on wo.PLAN_ID = item.PLAN_ID and wo.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID join MSS_FS_WD_HEADERS fsWdHdr on wo.PLAN_ID = fsWdHdr.PLAN_ID and wo.FS_WD_HEADER_ID = fsWdHdr.FS_WD_HEADER_ID join MSS_FS_WD_OP_MATERIALS fsWdOpMat on fsWdHdr.PLAN_ID = fsWdOpMat.PLAN_ID and fsWdHdr.FS_WD_HEADER_ID = fsWdOpMat.FS_WD_HEADER_ID union Select wo.PLAN_ID, wo.WORK_ORDER_ID, 10 RESOURCE_SEQ_NUMBER, null OPERATION_SEQ_NUMBER, item.ITEM_CODE \|\| '::Takt' OPERATION_CODE, 'Takt Operation' OPERATION_NAME, 'Takt Operation' FS_OPERATION_TYPE, fsWdHdr.FS_WD_HEADER_ID, fsWdHdr.WORK_DEFINITION_ID, fsWdHdr.FS_COMPLETION_DATE, null WD_OPERATION_ID, null LINE_OP_SEQ_NUMBER, 'Open' STATUS, -2 WORK_CENTER_ID, pLine.PRODUCTION_LINE_ID DURATION_RES_ID, pLine.PRODUCTION_LINE_ID LOT_MULTIPLE_RES_ID, pLine.LINE_RATE from MSS_WORK_ORDERS wo join MSS_PRODUCTION_LINES pLine on wo.PLAN_ID = pLine.PLAN_ID and wo.PRODUCTION_LINE_ID = pLine.PRODUCTION_LINE_ID join MSS_ITEMS item on wo.PLAN_ID = item.PLAN_ID and wo.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID left outer join MSS_FS_WD_HEADERS fsWdHdr on wo.PLAN_ID = fsWdHdr.PLAN_ID and wo.FS_WD_HEADER_ID = fsWdHdr.FS_WD_HEADER_ID ) select PLAN_ID, (-99 - row_number() over (partition by PLAN_ID order by WORK_ORDER_ID, OPERATION_SEQ_NUMBER, RESOURCE_SEQ_NUMBER)) WO_OPERATION_ID, WORK_ORDER_ID, RESOURCE_SEQ_NUMBER, OPERATION_SEQ_NUMBER, OPERATION_CODE, OPERATION_NAME, FS_OPERATION_TYPE, FS_WD_HEADER_ID, WORK_DEFINITION_ID, FS_COMPLETION_DATE, WD_OPERATION_ID, LINE_OP_SEQ_NUMBER, STATUS, WORK_CENTER_ID, DURATION_RES_ID, LOT_MULTIPLE_RES_ID, LINE_RATE from fsOp

SQL_Statement

with fsOp as (

Select distinct

wo.PLAN_ID,

wo.WORK_ORDER_ID,

10 RESOURCE_SEQ_NUMBER,

fsWdOpMat.OPERATION_SEQ_NUMBER,

item.ITEM_CODE || '::' || fsWdOpMat.OPERATION_SEQ_NUMBER OPERATION_CODE,

'OP' || fsWdOpMat.OPERATION_SEQ_NUMBER OPERATION_NAME,

'Line Operation' FS_OPERATION_TYPE,

fsWdHdr.FS_WD_HEADER_ID,

fsWdHdr.WORK_DEFINITION_ID,

fsWdHdr.FS_COMPLETION_DATE,

fsWdOpMat.WD_OPERATION_ID,

fsWdOpMat.LINE_OP_SEQ_NUMBER,

'Open' STATUS,

r.WORK_CENTER_ID,

r.RESOURCE_ID DURATION_RES_ID,

r.RESOURCE_ID LOT_MULTIPLE_RES_ID,

pLine.LINE_RATE

from MSS_WORK_ORDERS wo

join MSS_PRODUCTION_LINES pLine

on wo.PLAN_ID = pLine.PLAN_ID and wo.PRODUCTION_LINE_ID = pLine.PRODUCTION_LINE_ID

join MSS_FS_RESOURCES_V r

on pLine.PLAN_ID = r.PLAN_ID and pLine.PRODUCTION_LINE_ID = r.PRODUCTION_LINE_ID

join MSS_ITEMS item

on wo.PLAN_ID = item.PLAN_ID and wo.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID

join MSS_FS_WD_HEADERS fsWdHdr

on wo.PLAN_ID = fsWdHdr.PLAN_ID and wo.FS_WD_HEADER_ID = fsWdHdr.FS_WD_HEADER_ID

join MSS_FS_WD_OP_MATERIALS fsWdOpMat

on fsWdHdr.PLAN_ID = fsWdOpMat.PLAN_ID and fsWdHdr.FS_WD_HEADER_ID = fsWdOpMat.FS_WD_HEADER_ID

union

Select

wo.PLAN_ID,

wo.WORK_ORDER_ID,

10 RESOURCE_SEQ_NUMBER,

null OPERATION_SEQ_NUMBER,

item.ITEM_CODE || '::Takt' OPERATION_CODE,

'Takt Operation' OPERATION_NAME,

'Takt Operation' FS_OPERATION_TYPE,

fsWdHdr.FS_WD_HEADER_ID,

fsWdHdr.WORK_DEFINITION_ID,

fsWdHdr.FS_COMPLETION_DATE,

null WD_OPERATION_ID,

null LINE_OP_SEQ_NUMBER,

'Open' STATUS,

-2 WORK_CENTER_ID,

pLine.PRODUCTION_LINE_ID DURATION_RES_ID,

pLine.PRODUCTION_LINE_ID LOT_MULTIPLE_RES_ID,

pLine.LINE_RATE

from MSS_WORK_ORDERS wo

join MSS_PRODUCTION_LINES pLine

on wo.PLAN_ID = pLine.PLAN_ID and wo.PRODUCTION_LINE_ID = pLine.PRODUCTION_LINE_ID

join MSS_ITEMS item

on wo.PLAN_ID = item.PLAN_ID and wo.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID

left outer join MSS_FS_WD_HEADERS fsWdHdr

on wo.PLAN_ID = fsWdHdr.PLAN_ID and wo.FS_WD_HEADER_ID = fsWdHdr.FS_WD_HEADER_ID

)

select

PLAN_ID,

(-99 - row_number() over (partition by PLAN_ID order by WORK_ORDER_ID, OPERATION_SEQ_NUMBER, RESOURCE_SEQ_NUMBER)) WO_OPERATION_ID,

WORK_ORDER_ID,

RESOURCE_SEQ_NUMBER,

OPERATION_SEQ_NUMBER,

OPERATION_CODE,

OPERATION_NAME,

FS_OPERATION_TYPE,

FS_WD_HEADER_ID,

WORK_DEFINITION_ID,

FS_COMPLETION_DATE,

WD_OPERATION_ID,

LINE_OP_SEQ_NUMBER,

STATUS,

WORK_CENTER_ID,

DURATION_RES_ID,

LOT_MULTIPLE_RES_ID,

LINE_RATE

from fsOp