VIEW: APPS.BOM_BILL_OF_MATERIALS_ERV

Source

View Text - Preformatted

SELECT BOM.ROWID ROW_ID , BOM.ASSEMBLY_ITEM_ID , MSI.PRIMARY_UOM_CODE UOM , MSI.PICK_COMPONENTS_FLAG , MSI.REPLENISH_TO_ORDER_FLAG , MSI.BOM_ITEM_TYPE, MSI.BASE_ITEM_ID , MSI.CONCATENATED_SEGMENTS ASSEMBLY_ITEM_NO , MSI.DESCRIPTION ASSEMBLY_ITEM_DESCRIPTION , MIR.REVISION ASSEMBLY_ITEM_REVISION , MSI.ITEM_CATALOG_GROUP_ID /*, MICG.CONCATENATED_SEGMENTS ITEM_CATALOG_GROUP*/ , FND_FLEX_SERVER.get_kfv_concat_segs_by_ccid('COMPACT',401,'MICG',101,micg.item_catalog_group_id ,NULL) ITEM_CATALOG_GROUP , MSI.ATP_COMPONENTS_FLAG , MSI.WIP_SUPPLY_TYPE , MSI.ENG_ITEM_FLAG , BOM.ORGANIZATION_ID , MP1.ORGANIZATION_CODE, HOU1.NAME ORGANIZATION_NAME , BOM.ALTERNATE_BOM_DESIGNATOR , BOM.LAST_UPDATE_DATE , BOM.LAST_UPDATED_BY , BOM.CREATION_DATE , BOM.CREATED_BY , BOM.LAST_UPDATE_LOGIN , BOM.COMMON_ASSEMBLY_ITEM_ID , MSI2.CONCATENATED_SEGMENTS COMMON_ASSEMBLY_ITEM_NO , MSI2.DESCRIPTION COMMON_ASSEMBLY_DESCRIPTION , BOM.SPECIFIC_ASSEMBLY_COMMENT , BOM.PENDING_FROM_ECN , BOM.ATTRIBUTE_CATEGORY , BOM.ATTRIBUTE1 , BOM.ATTRIBUTE2 , BOM.ATTRIBUTE3 , BOM.ATTRIBUTE4 , BOM.ATTRIBUTE5 , BOM.ATTRIBUTE6 , BOM.ATTRIBUTE7 , BOM.ATTRIBUTE8 , BOM.ATTRIBUTE9 , BOM.ATTRIBUTE10 , BOM.ATTRIBUTE11 , BOM.ATTRIBUTE12 , BOM.ATTRIBUTE13 , BOM.ATTRIBUTE14 , BOM.ATTRIBUTE15 , BOM.ASSEMBLY_TYPE , BOM.COMMON_BILL_SEQUENCE_ID , BOM.BILL_SEQUENCE_ID , BOM.REQUEST_ID , BOM.PROGRAM_APPLICATION_ID , BOM.PROGRAM_ID , BOM.PROGRAM_UPDATE_DATE , BOM.COMMON_ORGANIZATION_ID , MP2.ORGANIZATION_CODE COMMON_ORGANIZATION_CODE , HOU2.NAME COMMON_ORGANIZATION_NAME , BOM.NEXT_EXPLODE_DATE , FCL.MEANING ITEM_TYPE , MSI.BOM_ENABLED_FLAG , MSI.EAM_ITEM_TYPE EAM_ITEM_TYPE , MGL.MEANING EAM_ITEM_TYPE_VALUE FROM FND_COMMON_LOOKUPS FCL , MTL_SYSTEM_ITEMS_VL MSI2 , MTL_SYSTEM_ITEMS_VL MSI , BOM_BILL_OF_MATERIALS BOM , MFG_LOOKUPS MGL , HR_ALL_ORGANIZATION_UNITS_TL HOU1 , MTL_PARAMETERS MP1 , HR_ALL_ORGANIZATION_UNITS_TL HOU2 , MTL_PARAMETERS MP2 , MTL_ITEM_CATALOG_GROUPS MICG , MTL_ITEM_REVISIONS MIR WHERE MICG.ITEM_CATALOG_GROUP_ID(+) = MSI.ITEM_CATALOG_GROUP_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 BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND HOU1.LANGUAGE = USERENV('LANG') AND HOU1.ORGANIZATION_ID = BOM.ORGANIZATION_ID AND MP1.ORGANIZATION_ID = BOM.ORGANIZATION_ID AND HOU2.LANGUAGE(+) = USERENV('LANG') AND HOU2.ORGANIZATION_ID(+) = BOM.COMMON_ORGANIZATION_ID AND MP2.ORGANIZATION_ID(+) = BOM.COMMON_ORGANIZATION_ID AND MSI2.INVENTORY_ITEM_ID(+) = BOM.COMMON_ASSEMBLY_ITEM_ID AND MSI2.ORGANIZATION_ID(+) = BOM.COMMON_ORGANIZATION_ID AND MSI.ITEM_TYPE = FCL.LOOKUP_CODE(+) AND FCL.LOOKUP_TYPE(+) = 'ITEM_TYPE' AND FCL.APPLICATION_ID(+) = 401 AND MGL.LOOKUP_TYPE(+) = 'MTL_EAM_ITEM_TYPE' AND MGL.LOOKUP_CODE(+) = MSI.EAM_ITEM_TYPE

View Text - HTML Formatted

SELECT BOM.ROWID ROW_ID
, BOM.ASSEMBLY_ITEM_ID
, MSI.PRIMARY_UOM_CODE UOM
, MSI.PICK_COMPONENTS_FLAG
, MSI.REPLENISH_TO_ORDER_FLAG
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ASSEMBLY_ITEM_NO
, MSI.DESCRIPTION ASSEMBLY_ITEM_DESCRIPTION
, MIR.REVISION ASSEMBLY_ITEM_REVISION
, MSI.ITEM_CATALOG_GROUP_ID /*
, MICG.CONCATENATED_SEGMENTS ITEM_CATALOG_GROUP*/
, FND_FLEX_SERVER.GET_KFV_CONCAT_SEGS_BY_CCID('COMPACT'
, 401
, 'MICG'
, 101
, MICG.ITEM_CATALOG_GROUP_ID
, NULL) ITEM_CATALOG_GROUP
, MSI.ATP_COMPONENTS_FLAG
, MSI.WIP_SUPPLY_TYPE
, MSI.ENG_ITEM_FLAG
, BOM.ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOU1.NAME ORGANIZATION_NAME
, BOM.ALTERNATE_BOM_DESIGNATOR
, BOM.LAST_UPDATE_DATE
, BOM.LAST_UPDATED_BY
, BOM.CREATION_DATE
, BOM.CREATED_BY
, BOM.LAST_UPDATE_LOGIN
, BOM.COMMON_ASSEMBLY_ITEM_ID
, MSI2.CONCATENATED_SEGMENTS COMMON_ASSEMBLY_ITEM_NO
, MSI2.DESCRIPTION COMMON_ASSEMBLY_DESCRIPTION
, BOM.SPECIFIC_ASSEMBLY_COMMENT
, BOM.PENDING_FROM_ECN
, BOM.ATTRIBUTE_CATEGORY
, BOM.ATTRIBUTE1
, BOM.ATTRIBUTE2
, BOM.ATTRIBUTE3
, BOM.ATTRIBUTE4
, BOM.ATTRIBUTE5
, BOM.ATTRIBUTE6
, BOM.ATTRIBUTE7
, BOM.ATTRIBUTE8
, BOM.ATTRIBUTE9
, BOM.ATTRIBUTE10
, BOM.ATTRIBUTE11
, BOM.ATTRIBUTE12
, BOM.ATTRIBUTE13
, BOM.ATTRIBUTE14
, BOM.ATTRIBUTE15
, BOM.ASSEMBLY_TYPE
, BOM.COMMON_BILL_SEQUENCE_ID
, BOM.BILL_SEQUENCE_ID
, BOM.REQUEST_ID
, BOM.PROGRAM_APPLICATION_ID
, BOM.PROGRAM_ID
, BOM.PROGRAM_UPDATE_DATE
, BOM.COMMON_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE COMMON_ORGANIZATION_CODE
, HOU2.NAME COMMON_ORGANIZATION_NAME
, BOM.NEXT_EXPLODE_DATE
, FCL.MEANING ITEM_TYPE
, MSI.BOM_ENABLED_FLAG
, MSI.EAM_ITEM_TYPE EAM_ITEM_TYPE
, MGL.MEANING EAM_ITEM_TYPE_VALUE
FROM FND_COMMON_LOOKUPS FCL
, MTL_SYSTEM_ITEMS_VL MSI2
, MTL_SYSTEM_ITEMS_VL MSI
, BOM_BILL_OF_MATERIALS BOM
, MFG_LOOKUPS MGL
, HR_ALL_ORGANIZATION_UNITS_TL HOU1
, MTL_PARAMETERS MP1
, HR_ALL_ORGANIZATION_UNITS_TL HOU2
, MTL_PARAMETERS MP2
, MTL_ITEM_CATALOG_GROUPS MICG
, MTL_ITEM_REVISIONS MIR
WHERE MICG.ITEM_CATALOG_GROUP_ID(+) = MSI.ITEM_CATALOG_GROUP_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 BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND HOU1.LANGUAGE = USERENV('LANG')
AND HOU1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND HOU2.LANGUAGE(+) = USERENV('LANG')
AND HOU2.ORGANIZATION_ID(+) = BOM.COMMON_ORGANIZATION_ID
AND MP2.ORGANIZATION_ID(+) = BOM.COMMON_ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID(+) = BOM.COMMON_ASSEMBLY_ITEM_ID
AND MSI2.ORGANIZATION_ID(+) = BOM.COMMON_ORGANIZATION_ID
AND MSI.ITEM_TYPE = FCL.LOOKUP_CODE(+)
AND FCL.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND FCL.APPLICATION_ID(+) = 401
AND MGL.LOOKUP_TYPE(+) = 'MTL_EAM_ITEM_TYPE'
AND MGL.LOOKUP_CODE(+) = MSI.EAM_ITEM_TYPE