VIEW: APPS.BOM_INVENTORY_COMPONENTS_V

Source

View Text - Preformatted

SELECT BIC.ROWID ROW_ID , BIC.OPERATION_SEQ_NUM , BIC.COMPONENT_ITEM_ID , BIC.LAST_UPDATE_DATE , BIC.LAST_UPDATED_BY , BIC.CREATION_DATE , BIC.CREATED_BY , BIC.LAST_UPDATE_LOGIN , BIC.ITEM_NUM , nvl(BIC.BASIS_TYPE,1) , BIC.COMPONENT_QUANTITY , BIC.COMPONENT_YIELD_FACTOR , BIC.COMPONENT_REMARKS , BIC.EFFECTIVITY_DATE , BIC.FROM_END_ITEM_UNIT_NUMBER , BIC.TO_END_ITEM_UNIT_NUMBER , BIC.CHANGE_NOTICE , BIC.IMPLEMENTATION_DATE , DECODE(BIC.IMPLEMENTATION_DATE,NULL,2,1) IMPL_CB , BIC.DISABLE_DATE , BIC.ATTRIBUTE_CATEGORY , BIC.ATTRIBUTE1 , BIC.ATTRIBUTE2 , BIC.ATTRIBUTE3 , BIC.ATTRIBUTE4 , BIC.ATTRIBUTE5 , BIC.ATTRIBUTE6 , BIC.ATTRIBUTE7 , BIC.ATTRIBUTE8 , BIC.ATTRIBUTE9 , BIC.ATTRIBUTE10 , BIC.ATTRIBUTE11 , BIC.ATTRIBUTE12 , BIC.ATTRIBUTE13 , BIC.ATTRIBUTE14 , BIC.ATTRIBUTE15 , BIC.PLANNING_FACTOR , BIC.QUANTITY_RELATED , BIC.SO_BASIS , BIC.OPTIONAL , BIC.MUTUALLY_EXCLUSIVE_OPTIONS , BIC.INCLUDE_IN_COST_ROLLUP , BIC.CHECK_ATP , BIC.SHIPPING_ALLOWED , BIC.REQUIRED_TO_SHIP , BIC.REQUIRED_FOR_REVENUE , BIC.INCLUDE_ON_SHIP_DOCS , BIC.INCLUDE_ON_BILL_DOCS , BIC.LOW_QUANTITY , BIC.HIGH_QUANTITY , BIC.ACD_TYPE , BIC.OLD_COMPONENT_SEQUENCE_ID , BIC.COMPONENT_SEQUENCE_ID , BIC.BILL_SEQUENCE_ID , BIC.REQUEST_ID , BIC.PROGRAM_APPLICATION_ID , BIC.PROGRAM_ID , BIC.PROGRAM_UPDATE_DATE , BIC.WIP_SUPPLY_TYPE , ML.MEANING SUPPLY_TYPE , BIC.PICK_COMPONENTS , BIC.SUPPLY_SUBINVENTORY , BIC.SUPPLY_LOCATOR_ID , BIC.OPERATION_LEAD_TIME_PERCENT , BIC.REVISED_ITEM_SEQUENCE_ID , BIC.COST_FACTOR , BIC.BOM_ITEM_TYPE , MSI.PRIMARY_UOM_CODE , MSIT.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 , 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 , DECODE(BIC.FROM_END_ITEM_UNIT_NUMBER, NULL, 2, DECODE(BIC.DISABLE_DATE,NULL,2,1)) DISABLE_CB , NVL(BIC.ECO_FOR_PRODUCTION,2) , BIC.ENFORCE_INT_REQUIREMENTS ENFORCE_INT_REQUIREMENTS , ML1.MEANING ENFORCE_INT_REQUIREMENTS_DESC , BIC.FROM_BILL_REVISION_ID , BIC.TO_BILL_REVISION_ID , BIC.AUTO_REQUEST_MATERIAL , BIC.SUGGESTED_VENDOR_NAME , BIC.VENDOR_ID , BIC.UNIT_PRICE ,BIC.COMMON_COMPONENT_SEQUENCE_ID FROM BOM_BILL_OF_MATERIALS BOM /* NEEDED TO GET ORG_ID */ , BOM_INVENTORY_COMPONENTS BIC , MTL_SYSTEM_ITEMS_B_KFV MSI , MTL_SYSTEM_ITEMS_TL MSIT , FND_COMMON_LOOKUPS FCL , MFG_LOOKUPS ML , MFG_LOOKUPS ML1 WHERE BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID AND BIC.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = MSIT.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = MSIT.ORGANIZATION_ID AND MSIT.LANGUAGE = USERENV('LANG') AND ML.LOOKUP_CODE(+) = BIC.WIP_SUPPLY_TYPE AND ML.LOOKUP_TYPE(+) = 'WIP_SUPPLY' AND ML1.LOOKUP_TYPE(+) = 'BOM_ENFORCE_INT_REQUIREMENTS' AND ML1.LOOKUP_CODE(+) = BIC.ENFORCE_INT_REQUIREMENTS AND FCL.LOOKUP_TYPE(+) = 'ITEM_TYPE' AND FCL.LOOKUP_CODE(+) = MSI.ITEM_TYPE AND FCL.APPLICATION_ID(+) = 401 AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2

View Text - HTML Formatted

SELECT BIC.ROWID ROW_ID
, BIC.OPERATION_SEQ_NUM
, BIC.COMPONENT_ITEM_ID
, BIC.LAST_UPDATE_DATE
, BIC.LAST_UPDATED_BY
, BIC.CREATION_DATE
, BIC.CREATED_BY
, BIC.LAST_UPDATE_LOGIN
, BIC.ITEM_NUM
, NVL(BIC.BASIS_TYPE
, 1)
, BIC.COMPONENT_QUANTITY
, BIC.COMPONENT_YIELD_FACTOR
, BIC.COMPONENT_REMARKS
, BIC.EFFECTIVITY_DATE
, BIC.FROM_END_ITEM_UNIT_NUMBER
, BIC.TO_END_ITEM_UNIT_NUMBER
, BIC.CHANGE_NOTICE
, BIC.IMPLEMENTATION_DATE
, DECODE(BIC.IMPLEMENTATION_DATE
, NULL
, 2
, 1) IMPL_CB
, BIC.DISABLE_DATE
, BIC.ATTRIBUTE_CATEGORY
, BIC.ATTRIBUTE1
, BIC.ATTRIBUTE2
, BIC.ATTRIBUTE3
, BIC.ATTRIBUTE4
, BIC.ATTRIBUTE5
, BIC.ATTRIBUTE6
, BIC.ATTRIBUTE7
, BIC.ATTRIBUTE8
, BIC.ATTRIBUTE9
, BIC.ATTRIBUTE10
, BIC.ATTRIBUTE11
, BIC.ATTRIBUTE12
, BIC.ATTRIBUTE13
, BIC.ATTRIBUTE14
, BIC.ATTRIBUTE15
, BIC.PLANNING_FACTOR
, BIC.QUANTITY_RELATED
, BIC.SO_BASIS
, BIC.OPTIONAL
, BIC.MUTUALLY_EXCLUSIVE_OPTIONS
, BIC.INCLUDE_IN_COST_ROLLUP
, BIC.CHECK_ATP
, BIC.SHIPPING_ALLOWED
, BIC.REQUIRED_TO_SHIP
, BIC.REQUIRED_FOR_REVENUE
, BIC.INCLUDE_ON_SHIP_DOCS
, BIC.INCLUDE_ON_BILL_DOCS
, BIC.LOW_QUANTITY
, BIC.HIGH_QUANTITY
, BIC.ACD_TYPE
, BIC.OLD_COMPONENT_SEQUENCE_ID
, BIC.COMPONENT_SEQUENCE_ID
, BIC.BILL_SEQUENCE_ID
, BIC.REQUEST_ID
, BIC.PROGRAM_APPLICATION_ID
, BIC.PROGRAM_ID
, BIC.PROGRAM_UPDATE_DATE
, BIC.WIP_SUPPLY_TYPE
, ML.MEANING SUPPLY_TYPE
, BIC.PICK_COMPONENTS
, BIC.SUPPLY_SUBINVENTORY
, BIC.SUPPLY_LOCATOR_ID
, BIC.OPERATION_LEAD_TIME_PERCENT
, BIC.REVISED_ITEM_SEQUENCE_ID
, BIC.COST_FACTOR
, BIC.BOM_ITEM_TYPE
, MSI.PRIMARY_UOM_CODE
, MSIT.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
, 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
, DECODE(BIC.FROM_END_ITEM_UNIT_NUMBER
, NULL
, 2
, DECODE(BIC.DISABLE_DATE
, NULL
, 2
, 1)) DISABLE_CB
, NVL(BIC.ECO_FOR_PRODUCTION
, 2)
, BIC.ENFORCE_INT_REQUIREMENTS ENFORCE_INT_REQUIREMENTS
, ML1.MEANING ENFORCE_INT_REQUIREMENTS_DESC
, BIC.FROM_BILL_REVISION_ID
, BIC.TO_BILL_REVISION_ID
, BIC.AUTO_REQUEST_MATERIAL
, BIC.SUGGESTED_VENDOR_NAME
, BIC.VENDOR_ID
, BIC.UNIT_PRICE
, BIC.COMMON_COMPONENT_SEQUENCE_ID
FROM BOM_BILL_OF_MATERIALS BOM /* NEEDED TO GET ORG_ID */
, BOM_INVENTORY_COMPONENTS BIC
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SYSTEM_ITEMS_TL MSIT
, FND_COMMON_LOOKUPS FCL
, MFG_LOOKUPS ML
, MFG_LOOKUPS ML1
WHERE BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
AND BIC.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MSIT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MSIT.ORGANIZATION_ID
AND MSIT.LANGUAGE = USERENV('LANG')
AND ML.LOOKUP_CODE(+) = BIC.WIP_SUPPLY_TYPE
AND ML.LOOKUP_TYPE(+) = 'WIP_SUPPLY'
AND ML1.LOOKUP_TYPE(+) = 'BOM_ENFORCE_INT_REQUIREMENTS'
AND ML1.LOOKUP_CODE(+) = BIC.ENFORCE_INT_REQUIREMENTS
AND FCL.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND FCL.LOOKUP_CODE(+) = MSI.ITEM_TYPE
AND FCL.APPLICATION_ID(+) = 401
AND NVL(BIC.ECO_FOR_PRODUCTION
, 2) = 2