MSC_OPERATION_COMPONENTS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
PLAN_ID ORGANIZATION_ID OPERATION_SEQUENCE_ID OPERATION_SEQ_NUM COMPONENT_SEQUENCE_ID BILL_SEQUENCE_ID ROUTING_SEQUENCE_ID LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY

Name

PLAN_ID

ORGANIZATION_ID

OPERATION_SEQUENCE_ID

OPERATION_SEQ_NUM

COMPONENT_SEQUENCE_ID

BILL_SEQUENCE_ID

ROUTING_SEQUENCE_ID

LAST_UPDATE_DATE

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

Query

SQL_Statement
SELECT DECODE(FROM_LEG,1,LEG.PLAN_ID,BC.PLAN_ID) PLAN_ID, DECODE(FROM_LEG,1,LEG.ORGANIZATION_ID,BC.ORGANIZATION_ID) ORGANIZATION_ID, NVL(BC.OPERATION_SEQUENCE_ID, LEG.OPERATION_SEQUENCE_ID ) OPERATION_SEQUENCE_ID, NVL(BC.OPERATION_SEQ_NUM, LEG.OPERATION_SEQ_NUM ) OPERATION_SEQ_NUM, BC.COMPONENT_SEQUENCE_ID, BC.BILL_SEQUENCE_ID, LEG.ROUTING_SEQUENCE_ID, DECODE(FROM_LEG,1,LEG.LAST_UPDATE_DATE, BC.LAST_UPDATE_DATE) LAST_UPDATE_DATE, DECODE(FROM_LEG,1,LEG.LAST_UPDATED_BY, BC.LAST_UPDATED_BY) LAST_UPDATED_BY, DECODE(FROM_LEG,1,LEG.CREATION_DATE, BC.CREATION_DATE) CREATION_DATE, DECODE(FROM_LEG,1,LEG.CREATED_BY, BC.CREATED_BY) CREATED_BY FROM ( SELECT B.COMPONENT_SEQUENCE_ID, B.BILL_SEQUENCE_ID, B.ORGANIZATION_ID, B.USING_ASSEMBLY_ID, B.PLAN_ID, EP.OPERATION_SEQUENCE_ID, EP.OPERATION_SEQ_NUM, EP.LAST_UPDATE_DATE, EP.LAST_UPDATED_BY, EP.CREATION_DATE, EP.CREATED_BY FROM MSC_BOM_COMPONENTS_V B, EGP_COMPONENT_OPERATIONS EP WHERE B.COMMON_BILL_SEQUENCE_ID = EP.BILL_SEQUENCE_ID ) BC FULL OUTER JOIN ( SELECT RTG.ROUTING_SEQUENCE_ID, RTOP.OPERATION_SEQUENCE_ID, OPERATION_SEQ_NUM, RTG.PLAN_ID, RTG.ASSEMBLY_ITEM_ID, RTG.ORGANIZATION_ID , 1 FROM_LEG, RTOP.LAST_UPDATE_DATE, RTOP.LAST_UPDATED_BY, RTOP.CREATION_DATE, RTOP.CREATED_BY FROM MSC_ROUTINGS RTG, MSC_ROUTING_OPERATIONS RTOP WHERE RTOP.ORGANIZATION_ID (+) = RTG.ORGANIZATION_ID AND RTOP.ROUTING_SEQUENCE_ID (+) = RTG.ROUTING_SEQUENCE_ID ) LEG ON BC.USING_ASSEMBLY_ID = LEG.ASSEMBLY_ITEM_ID AND BC.ORGANIZATION_ID = LEG.ORGANIZATION_ID AND BC.OPERATION_SEQ_NUM = LEG.OPERATION_SEQ_NUM

SQL_Statement

SELECT

DECODE(FROM_LEG,1,LEG.PLAN_ID,BC.PLAN_ID) PLAN_ID,

DECODE(FROM_LEG,1,LEG.ORGANIZATION_ID,BC.ORGANIZATION_ID) ORGANIZATION_ID,

NVL(BC.OPERATION_SEQUENCE_ID, LEG.OPERATION_SEQUENCE_ID ) OPERATION_SEQUENCE_ID,

NVL(BC.OPERATION_SEQ_NUM, LEG.OPERATION_SEQ_NUM ) OPERATION_SEQ_NUM,

BC.COMPONENT_SEQUENCE_ID,

BC.BILL_SEQUENCE_ID,

LEG.ROUTING_SEQUENCE_ID,

DECODE(FROM_LEG,1,LEG.LAST_UPDATE_DATE, BC.LAST_UPDATE_DATE) LAST_UPDATE_DATE,

DECODE(FROM_LEG,1,LEG.LAST_UPDATED_BY, BC.LAST_UPDATED_BY) LAST_UPDATED_BY,

DECODE(FROM_LEG,1,LEG.CREATION_DATE, BC.CREATION_DATE) CREATION_DATE,

DECODE(FROM_LEG,1,LEG.CREATED_BY, BC.CREATED_BY) CREATED_BY

FROM

(

SELECT B.COMPONENT_SEQUENCE_ID,

B.BILL_SEQUENCE_ID,

B.ORGANIZATION_ID, B.USING_ASSEMBLY_ID,

B.PLAN_ID,

EP.OPERATION_SEQUENCE_ID,

EP.OPERATION_SEQ_NUM,

EP.LAST_UPDATE_DATE,

EP.LAST_UPDATED_BY,

EP.CREATION_DATE,

EP.CREATED_BY

FROM MSC_BOM_COMPONENTS_V B,

EGP_COMPONENT_OPERATIONS EP

WHERE B.COMMON_BILL_SEQUENCE_ID = EP.BILL_SEQUENCE_ID ) BC

FULL OUTER JOIN

(

SELECT

RTG.ROUTING_SEQUENCE_ID,

RTOP.OPERATION_SEQUENCE_ID,

OPERATION_SEQ_NUM,

RTG.PLAN_ID,

RTG.ASSEMBLY_ITEM_ID,

RTG.ORGANIZATION_ID ,

1 FROM_LEG,

RTOP.LAST_UPDATE_DATE,

RTOP.LAST_UPDATED_BY,

RTOP.CREATION_DATE,

RTOP.CREATED_BY

FROM

MSC_ROUTINGS RTG,

MSC_ROUTING_OPERATIONS RTOP

WHERE

RTOP.ORGANIZATION_ID (+) = RTG.ORGANIZATION_ID

AND RTOP.ROUTING_SEQUENCE_ID (+) = RTG.ROUTING_SEQUENCE_ID ) LEG

ON BC.USING_ASSEMBLY_ID = LEG.ASSEMBLY_ITEM_ID

AND BC.ORGANIZATION_ID = LEG.ORGANIZATION_ID

AND BC.OPERATION_SEQ_NUM = LEG.OPERATION_SEQ_NUM