View: ENG_REVISED_COMPONENTS_ERV

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

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
, MIL.CONCATENATED_SEGMENTS SUPPLY_LOCATOR
, A.REVISED_ITEM_SEQUENCE_ID
, A.COST_FACTOR
, A.CANCELLATION_DATE
, A.CANCEL_COMMENTS
, MSI.BOM_ITEM_TYPE
, MSI.PRIMARY_UOM_CODE
, MSI.CONCATENATED_SEGMENTS COMPONENT_ITEM_NO
, MSI.DESCRIPTION COMPONENT_ITEM_DESC
, 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
, BIC.OPERATION_SEQ_NUM OLD_OPERATION_SEQ_NUM
, BIC.COMPONENT_QUANTITY OLD_COMPONENT_QUANTITY
, MIR.REVISION COMPONENT_ITEM_REVISION
FROM MFG_LOOKUPS ML
, FND_COMMON_LOOKUPS FCL
, MTL_SYSTEM_ITEMS_VL MSI
, ENG_REVISED_ITEMS ERI
, ENG_REVISED_COMPONENTS A
, BOM_INVENTORY_COMPONENTS BIC
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_ITEM_REVISIONS MIR
WHERE A.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID
AND MIR.ORGANIZATION_ID(+) = MSI.ORGANIZATION_ID
AND MIR.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND (MIR.EFFECTIVITY_DATE IS NULL OR MIR.EFFECTIVITY_DATE = (SELECT MAX(EFFECTIVITY_DATE)
FROM MTL_ITEM_REVISIONS
WHERE INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EFFECTIVITY_DATE < SYSDATE) )
AND A.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ERI.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+) = A.SUPPLY_LOCATOR_ID
AND BIC.COMPONENT_SEQUENCE_ID(+) = A.COMPONENT_SEQUENCE_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 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
, MIL.CONCATENATED_SEGMENTS SUPPLY_LOCATOR
, 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.CONCATENATED_SEGMENTS COMPONENT_ITEM_NO
, MSI.DESCRIPTION COMPONENT_ITEM_DESC
, 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
, BIC.OPERATION_SEQ_NUM OLD_OPERATION_SEQ_NUM
, BIC.COMPONENT_QUANTITY OLD_COMPONENT_QUANTITY
, MIR.REVISION COMPONENT_ITEM_REVISION
FROM BOM_INVENTORY_COMPONENTS B
, BOM_BILL_OF_MATERIALS BOM
, MTL_SYSTEM_ITEMS_VL MSI
, MFG_LOOKUPS ML
, FND_COMMON_LOOKUPS FCL
, MFG_LOOKUPS ML1
, BOM_INVENTORY_COMPONENTS BIC
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_ITEM_REVISIONS MIR
WHERE B.IMPLEMENTATION_DATE IS NULL
AND B.CHANGE_NOTICE IS NOT NULL
AND B.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
AND MIR.ORGANIZATION_ID(+) = MSI.ORGANIZATION_ID
AND MIR.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND (MIR.EFFECTIVITY_DATE IS NULL OR MIR.EFFECTIVITY_DATE = (SELECT MAX(EFFECTIVITY_DATE)
FROM MTL_ITEM_REVISIONS
WHERE INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EFFECTIVITY_DATE < SYSDATE) )
AND B.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+) = B.SUPPLY_LOCATOR_ID
AND BIC.COMPONENT_SEQUENCE_ID(+) = B.COMPONENT_SEQUENCE_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 )

Columns