View: ENG_REVISED_COMPONENTS_V

Product:	ENG - Engineering
Description:	Pending, implemented, and cancelled revised components
Implementation/DBA Data:	APPS.ENG_REVISED_COMPONENTS_V

View Text

SELECT A.ROWID
, A.COMPONENT_SEQUENCE_ID
, A.COMPONENT_ITEM_ID
, A.BILL_SEQUENCE_ID
, A.CHANGE_NOTICE
, A.EFFECTIVITY_DATE
, A.COMPONENT_QUANTITY
, A.COMPONENT_YIELD_FACTOR
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.CREATION_DATE
, A.CREATED_BY
, A.LAST_UPDATE_LOGIN
, A.OLD_COMPONENT_SEQUENCE_ID
, A.ITEM_NUM
, A.WIP_SUPPLY_TYPE
, ML.MEANING SUPPLY_TYPE
, A.COMPONENT_REMARKS
, A.IMPLEMENTATION_DATE
, 2 IMPL_CB
, A.DISABLE_DATE
, A.ACD_TYPE
, A.PLANNING_FACTOR
, A.QUANTITY_RELATED
, A.SO_BASIS
, A.OPTIONAL
, A.MUTUALLY_EXCLUSIVE_OPTIONS
, A.INCLUDE_IN_COST_ROLLUP
, A.CHECK_ATP
, A.SHIPPING_ALLOWED
, A.REQUIRED_TO_SHIP
, A.REQUIRED_FOR_REVENUE
, A.INCLUDE_ON_SHIP_DOCS
, A.INCLUDE_ON_BILL_DOCS
, A.LOW_QUANTITY
, A.HIGH_QUANTITY
, A.ATTRIBUTE_CATEGORY
, A.ATTRIBUTE1
, A.ATTRIBUTE2
, A.ATTRIBUTE3
, A.ATTRIBUTE4
, A.ATTRIBUTE5
, A.ATTRIBUTE6
, A.ATTRIBUTE7
, A.ATTRIBUTE8
, A.ATTRIBUTE9
, A.ATTRIBUTE10
, A.ATTRIBUTE11
, A.ATTRIBUTE12
, A.ATTRIBUTE13
, A.ATTRIBUTE14
, A.ATTRIBUTE15
, A.REQUEST_ID
, A.PROGRAM_APPLICATION_ID
, A.PROGRAM_ID
, A.PROGRAM_UPDATE_DATE
, A.PICK_COMPONENTS
, A.OPERATION_SEQUENCE_NUM
, A.SUPPLY_SUBINVENTORY
, A.SUPPLY_LOCATOR_ID
, A.REVISED_ITEM_SEQUENCE_ID
, A.COST_FACTOR
, A.CANCELLATION_DATE
, A.CANCEL_COMMENTS
, MSI.BOM_ITEM_TYPE
, MSI.PRIMARY_UOM_CODE
, MSI.DESCRIPTION
, MSI.ENG_ITEM_FLAG
, MSI.INVENTORY_ITEM_STATUS_CODE
, MSI.SHIPPABLE_ITEM_FLAG
, MSI.LOCATION_CONTROL_CODE ITEM_LOCATOR_CONTROL
, MSI.RESTRICT_LOCATORS_CODE RESTRICT_LOCATORS_FLAG
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N') RESTRICT_SUBINVENTORIES_FLAG
, MSI.BASE_ITEM_ID
, FCL.MEANING ITEM_TYPE
, MSI.REPLENISH_TO_ORDER_FLAG
, MSI.ATP_COMPONENTS_FLAG
, MSI.PICK_COMPONENTS_FLAG
, MSI.INVENTORY_ASSET_FLAG
, MSI.ATP_FLAG
, MSI.ATO_FORECAST_CONTROL FORECAST_CONTROL
, A.FROM_END_ITEM_UNIT_NUMBER
, A.TO_END_ITEM_UNIT_NUMBER
, NVL( A.ECO_FOR_PRODUCTION
, 2)
, 0 ENFORCE_INT_REQUIREMENTS
, NULL ENFORCE_INT_REQUIREMENTS_DESC
, ERI.ORIGINAL_SYSTEM_REFERENCE
, A.FROM_END_ITEM_REV_ID
, A.TO_END_ITEM_REV_ID
, A.FROM_END_ITEM_MINOR_REV_ID
, A.TO_END_ITEM_MINOR_REV_ID
, A.MODEL_COMP_SEQ_ID
, A.PARENT_BILL_SEQ_ID
, A.OPTIONAL_ON_MODEL
, A.PLAN_LEVEL
, A.AUTO_REQUEST_MATERIAL
, A.FROM_OBJECT_REVISION_ID
, A.TO_OBJECT_REVISION_ID
, A.COMPONENT_ITEM_REVISION_ID
, A.VENDOR_ID
, A.SUGGESTED_VENDOR_NAME
, A.UNIT_PRICE
, NVL(A.BASIS_TYPE
, 1)
, A.COMMON_COMPONENT_SEQUENCE_ID
, BASIS.MEANING AS BASIS_TYPE_DESC
FROM MFG_LOOKUPS ML
, FND_COMMON_LOOKUPS FCL
, MTL_SYSTEM_ITEMS MSI
, ENG_REVISED_ITEMS ERI
, ENG_REVISED_COMPONENTS A
, MFG_LOOKUPS BASIS
WHERE A.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID
AND A.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ERI.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ML.LOOKUP_CODE(+) = A.WIP_SUPPLY_TYPE
AND ML.LOOKUP_TYPE(+) = 'WIP_SUPPLY'
AND FCL.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND FCL.LOOKUP_CODE(+) = MSI.ITEM_TYPE
AND FCL.APPLICATION_ID(+) = 401
AND BASIS.LOOKUP_TYPE = 'BOM_BASIS_TYPE'
AND BASIS.LOOKUP_CODE(+) = NVL(A.BASIS_TYPE
, 1) UNION (SELECT /* ORDERED */B.ROWID
, B.COMPONENT_SEQUENCE_ID
, B.COMPONENT_ITEM_ID
, B.BILL_SEQUENCE_ID
, B.CHANGE_NOTICE
, B.EFFECTIVITY_DATE
, B.COMPONENT_QUANTITY
, B.COMPONENT_YIELD_FACTOR
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.OLD_COMPONENT_SEQUENCE_ID
, B.ITEM_NUM
, B.WIP_SUPPLY_TYPE
, ML.MEANING SUPPLY_TYPE
, B.COMPONENT_REMARKS
, B.IMPLEMENTATION_DATE
, DECODE(B.IMPLEMENTATION_DATE
, NULL
, 2
, 1) IMPL_CB
, B.DISABLE_DATE
, B.ACD_TYPE
, B.PLANNING_FACTOR
, B.QUANTITY_RELATED
, B.SO_BASIS
, B.OPTIONAL
, B.MUTUALLY_EXCLUSIVE_OPTIONS
, B.INCLUDE_IN_COST_ROLLUP
, B.CHECK_ATP
, B.SHIPPING_ALLOWED
, B.REQUIRED_TO_SHIP
, B.REQUIRED_FOR_REVENUE
, B.INCLUDE_ON_SHIP_DOCS
, B.INCLUDE_ON_BILL_DOCS
, B.LOW_QUANTITY
, B.HIGH_QUANTITY
, B.ATTRIBUTE_CATEGORY
, B.ATTRIBUTE1
, B.ATTRIBUTE2
, B.ATTRIBUTE3
, B.ATTRIBUTE4
, B.ATTRIBUTE5
, B.ATTRIBUTE6
, B.ATTRIBUTE7
, B.ATTRIBUTE8
, B.ATTRIBUTE9
, B.ATTRIBUTE10
, B.ATTRIBUTE11
, B.ATTRIBUTE12
, B.ATTRIBUTE13
, B.ATTRIBUTE14
, B.ATTRIBUTE15
, B.REQUEST_ID
, B.PROGRAM_APPLICATION_ID
, B.PROGRAM_ID
, B.PROGRAM_UPDATE_DATE
, B.PICK_COMPONENTS
, B.OPERATION_SEQ_NUM
, B.SUPPLY_SUBINVENTORY
, B.SUPPLY_LOCATOR_ID
, B.REVISED_ITEM_SEQUENCE_ID
, B.COST_FACTOR
, TO_DATE(NULL) CANCELLATION_DATE
, NULL CANCEL_COMMENTS
, B.BOM_ITEM_TYPE
, MSI.PRIMARY_UOM_CODE
, MSI.DESCRIPTION
, MSI.ENG_ITEM_FLAG
, MSI.INVENTORY_ITEM_STATUS_CODE
, MSI.SHIPPABLE_ITEM_FLAG
, MSI.LOCATION_CONTROL_CODE ITEM_LOCATOR_CONTROL
, MSI.RESTRICT_LOCATORS_CODE RESTRICT_LOCATORS_FLAG
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N') RESTRICT_SUBINVENTORIES_FLAG
, MSI.BASE_ITEM_ID
, FCL.MEANING ITEM_TYPE
, MSI.REPLENISH_TO_ORDER_FLAG
, MSI.ATP_COMPONENTS_FLAG
, MSI.PICK_COMPONENTS_FLAG
, MSI.INVENTORY_ASSET_FLAG
, MSI.ATP_FLAG
, MSI.ATO_FORECAST_CONTROL FORECAST_CONTROL
, B.FROM_END_ITEM_UNIT_NUMBER
, B.TO_END_ITEM_UNIT_NUMBER
, NVL( B.ECO_FOR_PRODUCTION
, 2)
, B.ENFORCE_INT_REQUIREMENTS
, ML1.MEANING ENFORCE_INT_REQUIREMENTS_DESC
, B.ORIGINAL_SYSTEM_REFERENCE
, B.FROM_END_ITEM_REV_ID
, B.TO_END_ITEM_REV_ID
, B.FROM_END_ITEM_MINOR_REV_ID
, B.TO_END_ITEM_MINOR_REV_ID
, B.MODEL_COMP_SEQ_ID
, B.PARENT_BILL_SEQ_ID
, B.OPTIONAL_ON_MODEL
, B.PLAN_LEVEL
, B.AUTO_REQUEST_MATERIAL
, B.FROM_OBJECT_REVISION_ID
, B.TO_OBJECT_REVISION_ID
, B.COMPONENT_ITEM_REVISION_ID
, B.VENDOR_ID
, B.SUGGESTED_VENDOR_NAME
, B.UNIT_PRICE
, NVL(B.BASIS_TYPE
, 1)
, B.COMMON_COMPONENT_SEQUENCE_ID
, BASIS.MEANING AS BASIS_TYPE_DESC
FROM BOM_INVENTORY_COMPONENTS B
, BOM_BILL_OF_MATERIALS BOM
, MTL_SYSTEM_ITEMS MSI
, MFG_LOOKUPS ML
, FND_COMMON_LOOKUPS FCL
, MFG_LOOKUPS ML1
, MFG_LOOKUPS BASIS
WHERE B.IMPLEMENTATION_DATE IS NULL
AND B.CHANGE_NOTICE IS NOT NULL
AND B.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
AND B.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ML.LOOKUP_CODE(+) = B.WIP_SUPPLY_TYPE
AND ML.LOOKUP_TYPE(+) = 'WIP_SUPPLY'
AND ML1.LOOKUP_TYPE(+) = 'BOM_ENFORCE_INT_REQUIREMENTS'
AND ML1.LOOKUP_CODE (+) = B.ENFORCE_INT_REQUIREMENTS
AND FCL.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND FCL.LOOKUP_CODE(+) = MSI.ITEM_TYPE
AND FCL.APPLICATION_ID(+) = 401
AND BASIS.LOOKUP_TYPE = 'BOM_BASIS_TYPE'
AND BASIS.LOOKUP_CODE(+) = NVL(B.BASIS_TYPE
, 1) )

Columns