MSC_AP_WD_OP_DEPENDENCIES

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ASSEMBLY_NAME ORGANIZATION_CODE ALTERNATE_ROUTING_DESIGNATOR VERSION_EFFECTIVITY_DATE VERSION_DISABLE_DATE PREVIOUS_OP_SEQ_NUM NEXT_OP_SEQ_NUM PREV_OP_EFFECTIVITY_DATE NEXT_OP_EFFECTIVITY_DATE DEPENDENCY_TYPE TRANSFER_PERCENT STATUS_CODE WD_OP_DEPENDENCY_ID SR_WD_ID ASSEMBLY_ITEM_ID ORGANIZATION_ID

Name

ASSEMBLY_NAME

ORGANIZATION_CODE

ALTERNATE_ROUTING_DESIGNATOR

VERSION_EFFECTIVITY_DATE

VERSION_DISABLE_DATE

PREVIOUS_OP_SEQ_NUM

NEXT_OP_SEQ_NUM

PREV_OP_EFFECTIVITY_DATE

NEXT_OP_EFFECTIVITY_DATE

DEPENDENCY_TYPE

TRANSFER_PERCENT

STATUS_CODE

WD_OP_DEPENDENCY_ID

SR_WD_ID

ASSEMBLY_ITEM_ID

ORGANIZATION_ID

Query

SQL_Statement
WITH FIRST_VERSION AS ( SELECT WV.WORK_DEFINITION_ID, MIN(WV.EFFECTIVE_FROM_DATE) EFFECTIVE_FROM_DATE FROM WIS_WD_VERSIONS_V WV GROUP BY WV.WORK_DEFINITION_ID ) SELECT ESI.ITEM_NUMBER ASSEMBLY_NAME, ESI.ORGANIZATION_CODE ORGANIZATION_CODE, WDN.WORK_DEFINITION_CODE ALTERNATE_ROUTING_DESIGNATOR, WV.EFFECTIVE_FROM_DATE VERSION_EFFECTIVITY_DATE, WV.EFFECTIVE_TO_DATE VERSION_DISABLE_DATE, WOP.OPERATION_SEQ_NUMBER PREVIOUS_OP_SEQ_NUM, WOP1.OPERATION_SEQ_NUMBER NEXT_OP_SEQ_NUM, GREATEST(WOP.EFFECTIVE_FROM_DATE, FIRST_VERSION.EFFECTIVE_FROM_DATE) PREV_OP_EFFECTIVITY_DATE, GREATEST(WOP1.EFFECTIVE_FROM_DATE, FIRST_VERSION.EFFECTIVE_FROM_DATE) NEXT_OP_EFFECTIVITY_DATE, DECODE(WOPD.DEPENDENCY_TYPE,'ORA_FINISH_TO_START',1,'ORA_START_TO_START',2,NULL) DEPENDENCY_TYPE, WOPD.TRANSFER_PERCENT TRANSFER_PERCENT, WOPD.STATUS_CODE STATUS_CODE, WOPD.WD_OP_DEPENDENCY_ID WD_OP_DEPENDENCY_ID, WOPD.WORK_DEFINITION_ID SR_WD_ID, ESI.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID, WV.ORGANIZATION_ID ORGANIZATION_ID FROM WIS_WORK_DEFINITIONS WD, WIS_WORK_METHODS_B WM, WIS_WORK_DEFINITION_NAMES_B WDN, MSC_PLANNED_EGP_SYSTEM_ITEMS ESI, WIS_WD_VERSIONS WV, WIS_WD_OPERATIONS_B WOP, WIS_WD_OPERATIONS_B WOP1, WIS_WD_OP_DEPENDENCIES WOPD, FIRST_VERSION WHERE WD.STATUS_CODE IN ('ACTIVE', 'PENDING_INACTIVE') AND WD.WORK_DEFINITION_ID = FIRST_VERSION.WORK_DEFINITION_ID AND WM.WORK_METHOD_ID = WD.WORK_METHOD_ID AND WM.WORK_METHOD_CODE IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING') AND WDN.WORK_DEFINITION_NAME_ID = WD.WORK_DEFINITION_NAME_ID AND WDN.USED_IN_PLANNING_FLAG = 'Y' AND WD.ORGANIZATION_ID = ESI.ORGANIZATION_ID AND WD.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID AND WV.WORK_DEFINITION_ID = WD.WORK_DEFINITION_ID AND WV.PARALLEL_OPS_STATUS_CODE = 'VALID' AND WV.WORK_DEFINITION_ID = WOPD.WORK_DEFINITION_ID AND WV.WORK_DEFINITION_VERSION_ID = WOPD.WORK_DEFINITION_VERSION_ID AND WOP.WD_OPERATION_ID(+) = WOPD.PREVIOUS_WD_OPERATION_ID AND WOP1.WD_OPERATION_ID(+) = WOPD.NEXT_WD_OPERATION_ID

SQL_Statement

WITH FIRST_VERSION AS

( SELECT

WV.WORK_DEFINITION_ID, MIN(WV.EFFECTIVE_FROM_DATE) EFFECTIVE_FROM_DATE

FROM WIS_WD_VERSIONS_V WV

GROUP BY WV.WORK_DEFINITION_ID

)

SELECT

ESI.ITEM_NUMBER ASSEMBLY_NAME,

ESI.ORGANIZATION_CODE ORGANIZATION_CODE,

WDN.WORK_DEFINITION_CODE ALTERNATE_ROUTING_DESIGNATOR,

WV.EFFECTIVE_FROM_DATE VERSION_EFFECTIVITY_DATE,

WV.EFFECTIVE_TO_DATE VERSION_DISABLE_DATE,

WOP.OPERATION_SEQ_NUMBER PREVIOUS_OP_SEQ_NUM,

WOP1.OPERATION_SEQ_NUMBER NEXT_OP_SEQ_NUM,

GREATEST(WOP.EFFECTIVE_FROM_DATE, FIRST_VERSION.EFFECTIVE_FROM_DATE) PREV_OP_EFFECTIVITY_DATE,

GREATEST(WOP1.EFFECTIVE_FROM_DATE, FIRST_VERSION.EFFECTIVE_FROM_DATE) NEXT_OP_EFFECTIVITY_DATE,

DECODE(WOPD.DEPENDENCY_TYPE,'ORA_FINISH_TO_START',1,'ORA_START_TO_START',2,NULL) DEPENDENCY_TYPE,

WOPD.TRANSFER_PERCENT TRANSFER_PERCENT,

WOPD.STATUS_CODE STATUS_CODE,

WOPD.WD_OP_DEPENDENCY_ID WD_OP_DEPENDENCY_ID,

WOPD.WORK_DEFINITION_ID SR_WD_ID,

ESI.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,

WV.ORGANIZATION_ID ORGANIZATION_ID

FROM

WIS_WORK_DEFINITIONS WD,

WIS_WORK_METHODS_B WM,

WIS_WORK_DEFINITION_NAMES_B WDN,

MSC_PLANNED_EGP_SYSTEM_ITEMS ESI,

WIS_WD_VERSIONS WV,

WIS_WD_OPERATIONS_B WOP,

WIS_WD_OPERATIONS_B WOP1,

WIS_WD_OP_DEPENDENCIES WOPD,

FIRST_VERSION

WHERE WD.STATUS_CODE IN ('ACTIVE', 'PENDING_INACTIVE')

AND WD.WORK_DEFINITION_ID = FIRST_VERSION.WORK_DEFINITION_ID

AND WM.WORK_METHOD_ID = WD.WORK_METHOD_ID

AND WM.WORK_METHOD_CODE IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING')

AND WDN.WORK_DEFINITION_NAME_ID = WD.WORK_DEFINITION_NAME_ID

AND WDN.USED_IN_PLANNING_FLAG = 'Y'

AND WD.ORGANIZATION_ID = ESI.ORGANIZATION_ID

AND WD.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID

AND WV.WORK_DEFINITION_ID = WD.WORK_DEFINITION_ID

AND WV.PARALLEL_OPS_STATUS_CODE = 'VALID'

AND WV.WORK_DEFINITION_ID = WOPD.WORK_DEFINITION_ID

AND WV.WORK_DEFINITION_VERSION_ID = WOPD.WORK_DEFINITION_VERSION_ID

AND WOP.WD_OPERATION_ID(+) = WOPD.PREVIOUS_WD_OPERATION_ID

AND WOP1.WD_OPERATION_ID(+) = WOPD.NEXT_WD_OPERATION_ID