VIEW: APPS.BOM_INV_COMPS_INTERFACE_ERV

Source

View Text - Preformatted

SELECT BICI.ROWID ROW_ID , BICI.OPERATION_SEQ_NUM , BICI.COMPONENT_ITEM_ID , MSI.CONCATENATED_SEGMENTS , BICI.LAST_UPDATE_DATE , BICI.LAST_UPDATED_BY , BICI.CREATION_DATE , BICI.CREATED_BY , BICI.LAST_UPDATE_LOGIN , BICI.ITEM_NUM , BICI.COMPONENT_QUANTITY , BICI.COMPONENT_YIELD_FACTOR , BICI.DISABLE_DATE , BICI.ATTRIBUTE_CATEGORY , BICI.ATTRIBUTE1 , BICI.ATTRIBUTE2 , BICI.ATTRIBUTE3 , BICI.ATTRIBUTE4 , BICI.ATTRIBUTE5 , BICI.ATTRIBUTE6 , BICI.ATTRIBUTE7 , BICI.ATTRIBUTE8 , BICI.ATTRIBUTE9 , BICI.ATTRIBUTE10 , BICI.ATTRIBUTE11 , BICI.ATTRIBUTE12 , BICI.ATTRIBUTE13 , BICI.ATTRIBUTE14 , BICI.ATTRIBUTE15 , BICI.PLANNING_FACTOR , BICI.QUANTITY_RELATED , BICI.SO_BASIS , BICI.OPTIONAL , BICI.MUTUALLY_EXCLUSIVE_OPTIONS , BICI.INCLUDE_IN_COST_ROLLUP , BICI.CHECK_ATP , BICI.SHIPPING_ALLOWED , BICI.REQUIRED_TO_SHIP , BICI.REQUIRED_FOR_REVENUE , BICI.INCLUDE_ON_SHIP_DOCS , BICI.INCLUDE_ON_BILL_DOCS , BICI.LOW_QUANTITY , BICI.HIGH_QUANTITY , BICI.ACD_TYPE , BICI.OLD_COMPONENT_SEQUENCE_ID , BICI.COMPONENT_SEQUENCE_ID , BICI.REQUEST_ID , BICI.PROGRAM_APPLICATION_ID , BICI.PROGRAM_ID , BICI.PROGRAM_UPDATE_DATE , BICI.WIP_SUPPLY_TYPE , BICI.SUPPLY_SUBINVENTORY , BICI.SUPPLY_LOCATOR_ID , BICI.REVISED_ITEM_SEQUENCE_ID , BICI.COST_FACTOR , BICI.DDF_CONTEXT1 , BICI.DDF_CONTEXT2 , MSI.DESCRIPTION , MSI.PRIMARY_UOM_CODE , MSI.BOM_ITEM_TYPE , MSI.ATP_COMPONENTS_FLAG , MSI.REPLENISH_TO_ORDER_FLAG , DECODE(MSI.SHIPPABLE_ITEM_FLAG,'Y',1,2) DEFAULT_SHIPPABLE , DECODE(MSI.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,'Y',1,'N',2,1) DEFAULT_COST_ROLLUP , DECODE(MSI.BOM_ITEM_TYPE, 1, 6, 2, 6, NVL(DECODE(MSI.WIP_SUPPLY_TYPE, 7,1,MSI.WIP_SUPPLY_TYPE),1)) DEFAULT_WIP_SUPPLY_TYPE , MSI.WIP_SUPPLY_LOCATOR_ID DEFAULT_SUPPLY_LOCATOR_ID , MSI.WIP_SUPPLY_SUBINVENTORY DEFAULT_SUPPLY_SUBINVENTORY , DECODE(MSI.ATP_FLAG, 'Y', 1, 2) DEFAULT_CHECK_ATP , MSI.LOCATION_CONTROL_CODE ITEM_LOCATOR_CONTROL , DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y','N') RESTRICT_LOCATORS_FLAG , DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_FLAG , MSI.INVENTORY_ASSET_FLAG , MSI.SHIPPABLE_ITEM_FLAG , BCRV.CURRENT_REVISION REVISION , FCL.MEANING ITEM_TYPE_NAME , ML1.MEANING ACD_TYPE_NAME , ML2.MEANING SUPPLY_TYPE , BICI.TO_END_ITEM_UNIT_NUMBER , MIL.CONCATENATED_SEGMENTS LOCATOR FROM BOM_INVENTORY_COMPS_INTERFACE BICI , MTL_SYSTEM_ITEMS_KFV MSI , ENG_REVISED_ITEMS_INTERFACE ERII , BOM_ITEM_CURRENT_REV_VIEW BCRV , FND_COMMON_LOOKUPS FCL , MFG_LOOKUPS ML1 , MFG_LOOKUPS ML2 , MTL_ITEM_LOCATIONS_KFV MIL WHERE BICI.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID AND BICI.REVISED_ITEM_SEQUENCE_ID = ERII.REVISED_ITEM_SEQUENCE_ID AND ERII.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND BICI.COMPONENT_ITEM_ID = BCRV.INVENTORY_ITEM_ID AND ERII.ORGANIZATION_ID = BCRV.ORGANIZATION_ID AND MSI.ITEM_TYPE = FCL.LOOKUP_CODE(+) AND FCL.LOOKUP_TYPE(+) = 'ITEM_TYPE' AND FCL.APPLICATION_ID(+) = 401 AND BICI.ACD_TYPE = ML1.LOOKUP_CODE AND ML1.LOOKUP_TYPE = 'BOM_CO_ACTION' AND BICI.WIP_SUPPLY_TYPE = ML2.LOOKUP_CODE(+) AND ML2.LOOKUP_TYPE(+) = 'WIP_SUPPLY' AND MIL.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID AND MIL.ORGANIZATION_ID(+) = MSI.ORGANIZATION_ID

View Text - HTML Formatted

SELECT BICI.ROWID ROW_ID
, BICI.OPERATION_SEQ_NUM
, BICI.COMPONENT_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, BICI.LAST_UPDATE_DATE
, BICI.LAST_UPDATED_BY
, BICI.CREATION_DATE
, BICI.CREATED_BY
, BICI.LAST_UPDATE_LOGIN
, BICI.ITEM_NUM
, BICI.COMPONENT_QUANTITY
, BICI.COMPONENT_YIELD_FACTOR
, BICI.DISABLE_DATE
, BICI.ATTRIBUTE_CATEGORY
, BICI.ATTRIBUTE1
, BICI.ATTRIBUTE2
, BICI.ATTRIBUTE3
, BICI.ATTRIBUTE4
, BICI.ATTRIBUTE5
, BICI.ATTRIBUTE6
, BICI.ATTRIBUTE7
, BICI.ATTRIBUTE8
, BICI.ATTRIBUTE9
, BICI.ATTRIBUTE10
, BICI.ATTRIBUTE11
, BICI.ATTRIBUTE12
, BICI.ATTRIBUTE13
, BICI.ATTRIBUTE14
, BICI.ATTRIBUTE15
, BICI.PLANNING_FACTOR
, BICI.QUANTITY_RELATED
, BICI.SO_BASIS
, BICI.OPTIONAL
, BICI.MUTUALLY_EXCLUSIVE_OPTIONS
, BICI.INCLUDE_IN_COST_ROLLUP
, BICI.CHECK_ATP
, BICI.SHIPPING_ALLOWED
, BICI.REQUIRED_TO_SHIP
, BICI.REQUIRED_FOR_REVENUE
, BICI.INCLUDE_ON_SHIP_DOCS
, BICI.INCLUDE_ON_BILL_DOCS
, BICI.LOW_QUANTITY
, BICI.HIGH_QUANTITY
, BICI.ACD_TYPE
, BICI.OLD_COMPONENT_SEQUENCE_ID
, BICI.COMPONENT_SEQUENCE_ID
, BICI.REQUEST_ID
, BICI.PROGRAM_APPLICATION_ID
, BICI.PROGRAM_ID
, BICI.PROGRAM_UPDATE_DATE
, BICI.WIP_SUPPLY_TYPE
, BICI.SUPPLY_SUBINVENTORY
, BICI.SUPPLY_LOCATOR_ID
, BICI.REVISED_ITEM_SEQUENCE_ID
, BICI.COST_FACTOR
, BICI.DDF_CONTEXT1
, BICI.DDF_CONTEXT2
, MSI.DESCRIPTION
, MSI.PRIMARY_UOM_CODE
, MSI.BOM_ITEM_TYPE
, MSI.ATP_COMPONENTS_FLAG
, MSI.REPLENISH_TO_ORDER_FLAG
, DECODE(MSI.SHIPPABLE_ITEM_FLAG
, 'Y'
, 1
, 2) DEFAULT_SHIPPABLE
, DECODE(MSI.DEFAULT_INCLUDE_IN_ROLLUP_FLAG
, 'Y'
, 1
, 'N'
, 2
, 1) DEFAULT_COST_ROLLUP
, DECODE(MSI.BOM_ITEM_TYPE
, 1
, 6
, 2
, 6
, NVL(DECODE(MSI.WIP_SUPPLY_TYPE
, 7
, 1
, MSI.WIP_SUPPLY_TYPE)
, 1)) DEFAULT_WIP_SUPPLY_TYPE
, MSI.WIP_SUPPLY_LOCATOR_ID DEFAULT_SUPPLY_LOCATOR_ID
, MSI.WIP_SUPPLY_SUBINVENTORY DEFAULT_SUPPLY_SUBINVENTORY
, DECODE(MSI.ATP_FLAG
, 'Y'
, 1
, 2) DEFAULT_CHECK_ATP
, MSI.LOCATION_CONTROL_CODE ITEM_LOCATOR_CONTROL
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'N') RESTRICT_LOCATORS_FLAG
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N') RESTRICT_SUBINVENTORIES_FLAG
, MSI.INVENTORY_ASSET_FLAG
, MSI.SHIPPABLE_ITEM_FLAG
, BCRV.CURRENT_REVISION REVISION
, FCL.MEANING ITEM_TYPE_NAME
, ML1.MEANING ACD_TYPE_NAME
, ML2.MEANING SUPPLY_TYPE
, BICI.TO_END_ITEM_UNIT_NUMBER
, MIL.CONCATENATED_SEGMENTS LOCATOR
FROM BOM_INVENTORY_COMPS_INTERFACE BICI
, MTL_SYSTEM_ITEMS_KFV MSI
, ENG_REVISED_ITEMS_INTERFACE ERII
, BOM_ITEM_CURRENT_REV_VIEW BCRV
, FND_COMMON_LOOKUPS FCL
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MTL_ITEM_LOCATIONS_KFV MIL
WHERE BICI.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BICI.REVISED_ITEM_SEQUENCE_ID = ERII.REVISED_ITEM_SEQUENCE_ID
AND ERII.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND BICI.COMPONENT_ITEM_ID = BCRV.INVENTORY_ITEM_ID
AND ERII.ORGANIZATION_ID = BCRV.ORGANIZATION_ID
AND MSI.ITEM_TYPE = FCL.LOOKUP_CODE(+)
AND FCL.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND FCL.APPLICATION_ID(+) = 401
AND BICI.ACD_TYPE = ML1.LOOKUP_CODE
AND ML1.LOOKUP_TYPE = 'BOM_CO_ACTION'
AND BICI.WIP_SUPPLY_TYPE = ML2.LOOKUP_CODE(+)
AND ML2.LOOKUP_TYPE(+) = 'WIP_SUPPLY'
AND MIL.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND MIL.ORGANIZATION_ID(+) = MSI.ORGANIZATION_ID