View: BOM_EXPL_INQUIRY_VIEW

Product:	BOM - Bills of Material
Description:	Indented bill of material for window
Implementation/DBA Data:	APPS.BOM_EXPL_INQUIRY_VIEW

View Text

SELECT /* ORDERED FIRST_ROWS INDEX(MSI MTL_SYSTEM_ITEMS_U1) INDEX(BOM BOM_BILL_OF_MATERIALS_U2) INDEX(LU10 MFG_LOOKUPS_U1) INDEX(FCL FND_COMMON_LOOKUPS_U1) */ ROWIDTOCHAR(BET.ROWID) ROW_ID
, BET.TOP_BILL_SEQUENCE_ID
, BET.BILL_SEQUENCE_ID
, BET.COMPONENT_SEQUENCE_ID
, BET.COMPONENT_ITEM_ID
, BET.ORGANIZATION_ID
, BET.OPERATION_SEQ_NUM
, BET.PLAN_LEVEL
, BET.EXTENDED_QUANTITY
, BET.ITEM_NUM
, BET.SORT_ORDER
, BET.GROUP_ID
, BET.ITEM_COST
, NVL(MSI.RESERVABLE_TYPE
, 2) RESERVABLE_TYPE
, BET.EXTEND_COST_FLAG
, BET.TOP_ALTERNATE_DESIGNATOR
, BET.TOP_ITEM_ID
, BET.IMPLEMENTATION_DATE
, DECODE(BET.IMPLEMENTATION_DATE
, NULL
, 2
, 1) IMPLEMENTED_FLAG
, BET.OPTIONAL
, BET.PLANNING_FACTOR
, BET.COMPONENT_YIELD_FACTOR
, BET.INCLUDE_IN_ROLLUP_FLAG
, BET.WIP_SUPPLY_TYPE WIP_SUPPLY_TYPE
, ML2.MEANING SUPPLY_TYPE
, BET.SUPPLY_SUBINVENTORY
, BET.SUPPLY_LOCATOR_ID
, BET.LOCATOR
, BET.COMPONENT_REMARKS
, BET.CHANGE_NOTICE
, LU11.MEANING BASIS_TYPE
, BET.COMPONENT_QUANTITY
, BET.OPERATION_LEAD_TIME_PERCENT
, BET.OPERATION_OFFSET
, BET.MUTUALLY_EXCLUSIVE_OPTIONS
, BET.CHECK_ATP
, BET.REQUIRED_TO_SHIP
, LU10.MEANING SO_BASIS
, BET.REQUIRED_FOR_REVENUE
, BET.INCLUDE_ON_SHIP_DOCS
, BET.LOW_QUANTITY MINIMUM_QUANTITY
, BET.HIGH_QUANTITY MAXIMUM_QUANTITY
, MSI.PRIMARY_UOM_CODE UNIT_OF_MEASURE
, MSI.ITEM_TYPE ITEM_TYPE
, FCL.MEANING ITEM_TYPE_DESCRIPTION
, MSI.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS
, INV_MEANING_SEL.C_ITEM_DESCRIPTION(MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID) DESCRIPTION
, MSI.ENG_ITEM_FLAG
, MSI.CUM_MANUFACTURING_LEAD_TIME
, MSI.CUMULATIVE_TOTAL_LEAD_TIME
, MSI.FULL_LEAD_TIME MANUFACTURING_LEAD_TIME
, BOM.ASSEMBLY_ITEM_ID PARENT_ITEM_ID
, BOM.ALTERNATE_BOM_DESIGNATOR PARENT_ALTERNATE
, DECODE(BOM.ALTERNATE_BOM_DESIGNATOR
, NULL
, 2
, 1) PARENT_ALTERNATE_FLAG
, NVL(BOM1.ASSEMBLY_TYPE
, 1) ENG_BILL
, LPAD(PLAN_LEVEL
, LEAST(PLAN_LEVEL
, 8)
, '.') DISPLAY_PLAN_LEVEL
, TO_CHAR(BET.EFFECTIVITY_DATE
, 'HH24:MI') EFFECTIVITY_TIME
, TO_CHAR(BET.DISABLE_DATE
, 'HH24:MI') DISABLE_TIME
, BET.EXTENDED_QUANTITY
, DECODE(BET.EXTEND_COST_FLAG
, 1
, BET.ITEM_COST * BET.EXTENDED_QUANTITY
, 2
, 0
, NULL) EXTENDED_COST
, BET.OPERATION_SEQ_NUM
, MSI.BOM_ITEM_TYPE BOM_ITEM_TYPE
, MSI.REPLENISH_TO_ORDER_FLAG ATO_FLAG
, MSI.PICK_COMPONENTS_FLAG
, MSI.ATP_COMPONENTS_FLAG
, MSI.ATP_FLAG
, MSI.SHIPPABLE_ITEM_FLAG
, MSI.MRP_PLANNING_CODE
, MSI.SO_TRANSACTIONS_FLAG
, MSI.CUSTOMER_ORDER_ENABLED_FLAG
, MSI.INVOICE_ENABLED_FLAG
, MSI.INVENTORY_ASSET_FLAG
, BOM_GLOBALS.GET_CONCAT_SEGS(MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID)
, MSI.PADDED_CONCATENATED_SEGMENTS
, BET.CURRENT_REVISION
, BET.FROM_END_ITEM_UNIT_NUMBER
, BET.TO_END_ITEM_UNIT_NUMBER
, ML3.MEANING
, DECODE(BET.FROM_END_ITEM_UNIT_NUMBER
, NULL
, 2
, DECODE(BET.DISABLE_DATE
, NULL
, 2
, 1)) DISABLED_FLAG
, DECODE(BET.FROM_END_ITEM_UNIT_NUMBER
, NULL
, BET.EFFECTIVITY_DATE
, NULL) FROM_EFFECTIVITY_DATE
, DECODE(BET.FROM_END_ITEM_UNIT_NUMBER
, NULL
, BET.DISABLE_DATE
, NULL) TO_EFFECTIVITY_DATE
FROM MFG_LOOKUPS ML2
, FND_COMMON_LOOKUPS FCL
, MFG_LOOKUPS ML3
, MFG_LOOKUPS LU10
, MFG_LOOKUPS LU11
, BOM_BILL_OF_MATERIALS BOM
, BOM_BILL_OF_MATERIALS BOM1
, MTL_SYSTEM_ITEMS_KFV MSI
, BOM_SMALL_EXPL_TEMP BET
WHERE BET.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BET.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
AND BET.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND LU10.LOOKUP_CODE (+) = BET.SO_BASIS
AND LU10.LOOKUP_TYPE (+) = 'BOM_SO_BASIS'
AND LU11.LOOKUP_CODE = NVL(BET.BASIS_TYPE
, 1)
AND LU11.LOOKUP_TYPE (+) = 'BOM_BASIS_TYPE'
AND ML3.LOOKUP_CODE = DECODE(BET.FROM_END_ITEM_UNIT_NUMBER
, NULL
, 1
, 2)
AND ML3.LOOKUP_TYPE (+) = 'MTL_EFFECTIVITY_CONTROL'
AND FCL.LOOKUP_CODE (+) = MSI.ITEM_TYPE
AND FCL.LOOKUP_TYPE (+) = 'ITEM_TYPE'
AND FCL.APPLICATION_ID (+) = 401
AND ML2.LOOKUP_CODE (+) = BET.WIP_SUPPLY_TYPE
AND ML2.LOOKUP_TYPE (+) = 'WIP_SUPPLY'
AND BET.COMPONENT_ITEM_ID = BOM1.ASSEMBLY_ITEM_ID(+)
AND BET.ORGANIZATION_ID = BOM1.ORGANIZATION_ID(+)
AND ( (BET.TOP_ALTERNATE_DESIGNATOR IS NULL
AND BOM1.ALTERNATE_BOM_DESIGNATOR IS NULL ) OR (BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
AND BOM1.ALTERNATE_BOM_DESIGNATOR = BET.TOP_ALTERNATE_DESIGNATOR ) OR ( BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
AND BOM1.ALTERNATE_BOM_DESIGNATOR IS NULL
AND NOT EXISTS ( SELECT 'X'
FROM BOM_BILL_OF_MATERIALS BOM2
WHERE BOM2.ORGANIZATION_ID = BET.ORGANIZATION_ID
AND BOM2.ASSEMBLY_ITEM_ID = BET.COMPONENT_ITEM_ID
AND BOM2.ALTERNATE_BOM_DESIGNATOR = BET.TOP_ALTERNATE_DESIGNATOR ) ) )

Columns