DBA Data[Home] [Help]

VIEW: APPS.ENG_REVISED_ITEMS_V

Source

View Text - Preformatted

SELECT ERI.ROWID ROW_ID , ERI.CHANGE_NOTICE , ERI.ORGANIZATION_ID , ERI.REVISED_ITEM_ID , MSITL.DESCRIPTION REVISED_ITEM_DESCRIPTION , FVL.MEANING ITEM_TYPE , MSI.BOM_ITEM_TYPE , MSI.REPLENISH_TO_ORDER_FLAG , MSI.ATP_COMPONENTS_FLAG , MSI.PICK_COMPONENTS_FLAG , MSI.BASE_ITEM_ID , MSI.BOM_ENABLED_FLAG , MSI.BUILD_IN_WIP_FLAG , ERI.LAST_UPDATE_DATE , ERI.LAST_UPDATED_BY , ERI.CREATION_DATE , ERI.CREATED_BY , ERI.LAST_UPDATE_LOGIN , DECODE(BOM.BILL_SEQUENCE_ID, NULL, DECODE(BOR.ROUTING_SEQUENCE_ID,NULL, NULL,BOR.ALTERNATE_ROUTING_DESIGNATOR) , BOM.ALTERNATE_BOM_DESIGNATOR) ALTERNATE_BOM_DESIGNATOR , BOM.ASSEMBLY_TYPE ASSEMBLY_TYPE , ERI.IMPLEMENTATION_DATE , ERI.DESCRIPTIVE_TEXT , ERI.CANCELLATION_DATE , ERI.CANCEL_COMMENTS , ERI.DISPOSITION_TYPE , ERI.NEW_ITEM_REVISION , ERI.AUTO_IMPLEMENT_DATE , DECODE(ERI.AUTO_IMPLEMENT_DATE, NULL, 2, 1) AUTO_IMPLEMENTED_FLAG , ERI.EARLY_SCHEDULE_DATE , ERI.STATUS_TYPE , MLU.MEANING REVISED_ITEM_STATUS , ERI.SCHEDULED_DATE , ERI.BILL_SEQUENCE_ID , ERI.MRP_ACTIVE , ERI.UPDATE_WIP , ERI.USE_UP , ERI.USE_UP_ITEM_ID , ERI.REVISED_ITEM_SEQUENCE_ID , ERI.USE_UP_PLAN_NAME , ERI.ATTRIBUTE_CATEGORY , ERI.ATTRIBUTE1 , ERI.ATTRIBUTE2 , ERI.ATTRIBUTE3 , ERI.ATTRIBUTE4 , ERI.ATTRIBUTE5 , ERI.ATTRIBUTE6 , ERI.ATTRIBUTE7 , ERI.ATTRIBUTE8 , ERI.ATTRIBUTE9 , ERI.ATTRIBUTE10 , ERI.ATTRIBUTE11 , ERI.ATTRIBUTE12 , ERI.ATTRIBUTE13 , ERI.ATTRIBUTE14 , ERI.ATTRIBUTE15 , ERI.REQUEST_ID , ERI.PROGRAM_APPLICATION_ID , ERI.PROGRAM_ID , ERI.PROGRAM_UPDATE_DATE , ERI.FROM_END_ITEM_UNIT_NUMBER , ml2.meaning disposition , ERI.FROM_WIP_ENTITY_ID , WI1.WIP_ENTITY_NAME FROM_WORK_ORDER , ERI.TO_WIP_ENTITY_ID , WI2.WIP_ENTITY_NAME TO_WORK_ORDER , ERI.FROM_CUM_QTY , ERI.LOT_NUMBER , ERI.CFM_ROUTING_FLAG , ERI.COMPLETION_SUBINVENTORY , ERI.COMPLETION_LOCATOR_ID , ERI.MIXED_MODEL_MAP_FLAG , ERI.PRIORITY , DECODE(ERI.CTP_FLAG,NULL,2,ERI.CTP_FLAG) CTP_FLAG , ERI.ROUTING_SEQUENCE_ID , ERI.NEW_ROUTING_REVISION , decode(BOR.ROUTING_TYPE,null,2,BOR.ROUTING_TYPE) ROUTING_TYPE , ERI.ROUTING_COMMENT , ERI.ORIGINAL_SYSTEM_REFERENCE , DECODE( ERI.ECO_FOR_PRODUCTION,NULL,2,ERI.ECO_FOR_PRODUCTION) ECO_FOR_PRODUCTION , MRITL.DESCRIPTION NEW_ITEM_REVISION_DESC , ERI.DESIGNATOR_SELECTION_TYPE DESIGNATOR_SELECTION_TYPE , ERI.ALTERNATE_BOM_DESIGNATOR DESIGNATOR_NAME , ERI.TRANSFER_OR_COPY TRANSFER_OR_COPY , ERI.TRANSFER_OR_COPY_ITEM TRANSFER_OR_COPY_ITEM , ERI.TRANSFER_OR_COPY_BILL TRANSFER_OR_COPY_BILL , ERI.TRANSFER_OR_COPY_ROUTING TRANSFER_OR_COPY_ROUTING , ERI.COPY_TO_ITEM COPY_TO_ITEM , ERI.COPY_TO_ITEM_DESC COPY_TO_ITEM_DESC , ERI.IMPLEMENTED_ONLY IMPLEMENTED_ONLY , ERI.SELECTION_OPTION SELECTION_OPTION , ERI.SELECTION_DATE SELECTION_DATE , ERI.SELECTION_UNIT_NUMBER SELECTION_UNIT_NUMBER , ERI.CONCATENATED_COPY_SEGMENTS CONCATENATED_COPY_SEGMENTS , ERI.CHANGE_ID , ERI.NEW_ITEM_REVISION_ID , ERI.CURRENT_ITEM_REVISION_ID , ERI.CURRENT_LIFECYCLE_STATE_ID , ERI.NEW_LIFECYCLE_STATE_ID , ERI.STATUS_CODE , ERI.FROM_END_ITEM_REV_ID , ERI.FROM_END_ITEM_STRC_REV_ID , ERI.ENABLE_ITEM_IN_LOCAL_ORG , ERI.CREATE_BOM_IN_LOCAL_ORG , ERI.PARENT_REVISED_ITEM_SEQ_ID , ERI.PLAN_LEVEL , ERI.CURRENT_STRUCTURE_REV_ID , ERI.NEW_STRUCTURE_REVISION , ERI.FROM_END_ITEM_ID ,BOM.STRUCTURE_TYPE_ID ,BOM.EFFECTIVITY_CONTROL ,ERI.IMPLEMENTATION_REQ_ID FROM ENG_REVISED_ITEMS ERI, BOM_BILL_OF_MATERIALS BOM, MTL_SYSTEM_ITEMS MSI, MFG_LOOKUPS MLU, MFG_LOOKUPS ml2, FND_LOOKUP_VALUES FVL, WIP_ENTITIES WI1, WIP_ENTITIES WI2, BOM_OPERATIONAL_ROUTINGS BOR, MTL_ITEM_REVISIONS_TL MRITL , MTL_ITEM_REVISIONS_B MRI, MTL_SYSTEM_ITEMS_TL MSITL WHERE ERI.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID(+) AND ERI.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID(+) AND ERI.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND ERI.REVISED_ITEM_ID = MSI.INVENTORY_ITEM_ID AND ERI.ORGANIZATION_ID = MRI.ORGANIZATION_ID(+) AND ERI.REVISED_ITEM_ID = MRI.INVENTORY_ITEM_ID(+) AND ERI.NEW_ITEM_REVISION = MRI.REVISION(+) AND MRI.INVENTORY_ITEM_ID =MRITL.INVENTORY_ITEM_ID(+) AND MRI.ORGANIZATION_ID =MRITL.ORGANIZATION_ID(+) AND MRI.REVISION_ID =MRITL.REVISION_ID(+) AND MRITL.LANGUAGE(+) = USERENV('LANG') AND ERI.STATUS_TYPE = MLU.LOOKUP_CODE AND MLU.LOOKUP_TYPE ='ECG_ECN_STATUS' AND eri.disposition_type = ml2.lookup_code AND ml2.lookup_type = 'ECG_MATERIAL_DISPOSITION' AND WI1.WIP_ENTITY_ID(+) = ERI.FROM_WIP_ENTITY_ID AND WI1.ORGANIZATION_ID(+) = ERI.ORGANIZATION_ID AND WI2.WIP_ENTITY_ID(+) = ERI.TO_WIP_ENTITY_ID AND WI2.ORGANIZATION_ID(+) = ERI.ORGANIZATION_ID AND MSI.ITEM_TYPE = FVL.LOOKUP_CODE(+) AND FVL.LOOKUP_TYPE(+) = 'ITEM_TYPE' AND FVL.VIEW_APPLICATION_ID(+) = 3 AND FVL.LANGUAGE(+) = userenv('LANG') AND FVL.SECURITY_GROUP_ID(+) = fnd_global.lookup_security_group('ITEM_TYPE',3) AND MSITL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSITL.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MSITL.LANGUAGE = USERENV('LANG')
View Text - HTML Formatted

SELECT ERI.ROWID ROW_ID
, ERI.CHANGE_NOTICE
, ERI.ORGANIZATION_ID
, ERI.REVISED_ITEM_ID
, MSITL.DESCRIPTION REVISED_ITEM_DESCRIPTION
, FVL.MEANING ITEM_TYPE
, MSI.BOM_ITEM_TYPE
, MSI.REPLENISH_TO_ORDER_FLAG
, MSI.ATP_COMPONENTS_FLAG
, MSI.PICK_COMPONENTS_FLAG
, MSI.BASE_ITEM_ID
, MSI.BOM_ENABLED_FLAG
, MSI.BUILD_IN_WIP_FLAG
, ERI.LAST_UPDATE_DATE
, ERI.LAST_UPDATED_BY
, ERI.CREATION_DATE
, ERI.CREATED_BY
, ERI.LAST_UPDATE_LOGIN
, DECODE(BOM.BILL_SEQUENCE_ID
, NULL
, DECODE(BOR.ROUTING_SEQUENCE_ID
, NULL
, NULL
, BOR.ALTERNATE_ROUTING_DESIGNATOR)
, BOM.ALTERNATE_BOM_DESIGNATOR) ALTERNATE_BOM_DESIGNATOR
, BOM.ASSEMBLY_TYPE ASSEMBLY_TYPE
, ERI.IMPLEMENTATION_DATE
, ERI.DESCRIPTIVE_TEXT
, ERI.CANCELLATION_DATE
, ERI.CANCEL_COMMENTS
, ERI.DISPOSITION_TYPE
, ERI.NEW_ITEM_REVISION
, ERI.AUTO_IMPLEMENT_DATE
, DECODE(ERI.AUTO_IMPLEMENT_DATE
, NULL
, 2
, 1) AUTO_IMPLEMENTED_FLAG
, ERI.EARLY_SCHEDULE_DATE
, ERI.STATUS_TYPE
, MLU.MEANING REVISED_ITEM_STATUS
, ERI.SCHEDULED_DATE
, ERI.BILL_SEQUENCE_ID
, ERI.MRP_ACTIVE
, ERI.UPDATE_WIP
, ERI.USE_UP
, ERI.USE_UP_ITEM_ID
, ERI.REVISED_ITEM_SEQUENCE_ID
, ERI.USE_UP_PLAN_NAME
, ERI.ATTRIBUTE_CATEGORY
, ERI.ATTRIBUTE1
, ERI.ATTRIBUTE2
, ERI.ATTRIBUTE3
, ERI.ATTRIBUTE4
, ERI.ATTRIBUTE5
, ERI.ATTRIBUTE6
, ERI.ATTRIBUTE7
, ERI.ATTRIBUTE8
, ERI.ATTRIBUTE9
, ERI.ATTRIBUTE10
, ERI.ATTRIBUTE11
, ERI.ATTRIBUTE12
, ERI.ATTRIBUTE13
, ERI.ATTRIBUTE14
, ERI.ATTRIBUTE15
, ERI.REQUEST_ID
, ERI.PROGRAM_APPLICATION_ID
, ERI.PROGRAM_ID
, ERI.PROGRAM_UPDATE_DATE
, ERI.FROM_END_ITEM_UNIT_NUMBER
, ML2.MEANING DISPOSITION
, ERI.FROM_WIP_ENTITY_ID
, WI1.WIP_ENTITY_NAME FROM_WORK_ORDER
, ERI.TO_WIP_ENTITY_ID
, WI2.WIP_ENTITY_NAME TO_WORK_ORDER
, ERI.FROM_CUM_QTY
, ERI.LOT_NUMBER
, ERI.CFM_ROUTING_FLAG
, ERI.COMPLETION_SUBINVENTORY
, ERI.COMPLETION_LOCATOR_ID
, ERI.MIXED_MODEL_MAP_FLAG
, ERI.PRIORITY
, DECODE(ERI.CTP_FLAG
, NULL
, 2
, ERI.CTP_FLAG) CTP_FLAG
, ERI.ROUTING_SEQUENCE_ID
, ERI.NEW_ROUTING_REVISION
, DECODE(BOR.ROUTING_TYPE
, NULL
, 2
, BOR.ROUTING_TYPE) ROUTING_TYPE
, ERI.ROUTING_COMMENT
, ERI.ORIGINAL_SYSTEM_REFERENCE
, DECODE( ERI.ECO_FOR_PRODUCTION
, NULL
, 2
, ERI.ECO_FOR_PRODUCTION) ECO_FOR_PRODUCTION
, MRITL.DESCRIPTION NEW_ITEM_REVISION_DESC
, ERI.DESIGNATOR_SELECTION_TYPE DESIGNATOR_SELECTION_TYPE
, ERI.ALTERNATE_BOM_DESIGNATOR DESIGNATOR_NAME
, ERI.TRANSFER_OR_COPY TRANSFER_OR_COPY
, ERI.TRANSFER_OR_COPY_ITEM TRANSFER_OR_COPY_ITEM
, ERI.TRANSFER_OR_COPY_BILL TRANSFER_OR_COPY_BILL
, ERI.TRANSFER_OR_COPY_ROUTING TRANSFER_OR_COPY_ROUTING
, ERI.COPY_TO_ITEM COPY_TO_ITEM
, ERI.COPY_TO_ITEM_DESC COPY_TO_ITEM_DESC
, ERI.IMPLEMENTED_ONLY IMPLEMENTED_ONLY
, ERI.SELECTION_OPTION SELECTION_OPTION
, ERI.SELECTION_DATE SELECTION_DATE
, ERI.SELECTION_UNIT_NUMBER SELECTION_UNIT_NUMBER
, ERI.CONCATENATED_COPY_SEGMENTS CONCATENATED_COPY_SEGMENTS
, ERI.CHANGE_ID
, ERI.NEW_ITEM_REVISION_ID
, ERI.CURRENT_ITEM_REVISION_ID
, ERI.CURRENT_LIFECYCLE_STATE_ID
, ERI.NEW_LIFECYCLE_STATE_ID
, ERI.STATUS_CODE
, ERI.FROM_END_ITEM_REV_ID
, ERI.FROM_END_ITEM_STRC_REV_ID
, ERI.ENABLE_ITEM_IN_LOCAL_ORG
, ERI.CREATE_BOM_IN_LOCAL_ORG
, ERI.PARENT_REVISED_ITEM_SEQ_ID
, ERI.PLAN_LEVEL
, ERI.CURRENT_STRUCTURE_REV_ID
, ERI.NEW_STRUCTURE_REVISION
, ERI.FROM_END_ITEM_ID
, BOM.STRUCTURE_TYPE_ID
, BOM.EFFECTIVITY_CONTROL
, ERI.IMPLEMENTATION_REQ_ID
FROM ENG_REVISED_ITEMS ERI
, BOM_BILL_OF_MATERIALS BOM
, MTL_SYSTEM_ITEMS MSI
, MFG_LOOKUPS MLU
, MFG_LOOKUPS ML2
, FND_LOOKUP_VALUES FVL
, WIP_ENTITIES WI1
, WIP_ENTITIES WI2
, BOM_OPERATIONAL_ROUTINGS BOR
, MTL_ITEM_REVISIONS_TL MRITL
, MTL_ITEM_REVISIONS_B MRI
, MTL_SYSTEM_ITEMS_TL MSITL
WHERE ERI.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID(+)
AND ERI.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID(+)
AND ERI.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ERI.REVISED_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ERI.ORGANIZATION_ID = MRI.ORGANIZATION_ID(+)
AND ERI.REVISED_ITEM_ID = MRI.INVENTORY_ITEM_ID(+)
AND ERI.NEW_ITEM_REVISION = MRI.REVISION(+)
AND MRI.INVENTORY_ITEM_ID =MRITL.INVENTORY_ITEM_ID(+)
AND MRI.ORGANIZATION_ID =MRITL.ORGANIZATION_ID(+)
AND MRI.REVISION_ID =MRITL.REVISION_ID(+)
AND MRITL.LANGUAGE(+) = USERENV('LANG')
AND ERI.STATUS_TYPE = MLU.LOOKUP_CODE
AND MLU.LOOKUP_TYPE ='ECG_ECN_STATUS'
AND ERI.DISPOSITION_TYPE = ML2.LOOKUP_CODE
AND ML2.LOOKUP_TYPE = 'ECG_MATERIAL_DISPOSITION'
AND WI1.WIP_ENTITY_ID(+) = ERI.FROM_WIP_ENTITY_ID
AND WI1.ORGANIZATION_ID(+) = ERI.ORGANIZATION_ID
AND WI2.WIP_ENTITY_ID(+) = ERI.TO_WIP_ENTITY_ID
AND WI2.ORGANIZATION_ID(+) = ERI.ORGANIZATION_ID
AND MSI.ITEM_TYPE = FVL.LOOKUP_CODE(+)
AND FVL.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND FVL.VIEW_APPLICATION_ID(+) = 3
AND FVL.LANGUAGE(+) = USERENV('LANG')
AND FVL.SECURITY_GROUP_ID(+) = FND_GLOBAL.LOOKUP_SECURITY_GROUP('ITEM_TYPE'
, 3)
AND MSITL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSITL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSITL.LANGUAGE = USERENV('LANG')