eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.BOM_IMPL_INQUIRY_V

Source

View Text - Preformatted


SELECT /*+ leading(T) use_nl(T R F M) index(R MTL_ITEM_REVISIONS_N1) no_expand */ T.ROWID , MP.ORGANIZATION_CODE , T.SEQUENCE_ID , T.LOWEST_ITEM_ID , T.CURRENT_ITEM_ID , T.SORT_CODE , T.CURRENT_LEVEL , T.PARENT_ITEM_ID , T.ORGANIZATION_ID , BOM_GLOBALS.Get_Concat_Segs(F.INVENTORY_ITEM_ID,F.ORGANIZATION_ID) , INV_MEANING_SEL.C_ITEM_DESCRIPTION(F.INVENTORY_ITEM_ID,F.ORGANIZATION_ID) , M.PRIMARY_UOM_CODE , LU.MEANING ITEM_TYPE , T.ALTERNATE_DESIGNATOR , T.OPERATION_SEQ_NUM , MIR.REVISION , LU2.MEANING BASIS_TYPE , T.COMPONENT_QUANTITY , T.CURRENT_ASSEMBLY_TYPE , T.CHANGE_NOTICE , T.REVISED_ITEM_SEQUENCE_ID , T.IMPLEMENTED_FLAG , F.INVENTORY_ITEM_STATUS_CODE , T.FROM_END_ITEM_UNIT_NUMBER , T.TO_END_ITEM_UNIT_NUMBER , ML2.MEANING , DECODE(T.FROM_END_ITEM_UNIT_NUMBER, NULL, 2, DECODE(T.DISABLE_DATE, NULL, 2, 1)) DISABLED_FLAG , HOU.NAME ORG_NAME , BOM_GLOBALS.Get_Concat_Segs(M.INVENTORY_ITEM_ID,M.ORGANIZATION_ID) , MIR.REVISION_LABEL , T.PARENT_SORT_CODE , MIR.LIFECYCLE_ID , MIR.CURRENT_PHASE_ID , DECODE(T.FROM_END_ITEM_UNIT_NUMBER,NULL,T.EFFECTIVITY_DATE,NULL) COMPONENT_EFFECTIVE_FROM_DATE , DECODE(T.FROM_END_ITEM_UNIT_NUMBER,NULL,T.DISABLE_DATE,NULL) COMPONENT_EFFECTIVE_TO_DATE FROM MTL_SYSTEM_ITEMS_B_KFV F , MTL_SYSTEM_ITEMS_B_KFV M , MFG_LOOKUPS ML2 , FND_LOOKUP_VALUES LU , BOM_SMALL_IMPL_TEMP T , MTL_PARAMETERS MP ,HR_ORGANIZATION_UNITS HOU ,MTL_ITEM_REVISIONS_B MIR , MFG_LOOKUPS LU2 WHERE T.PARENT_ITEM_ID = F.INVENTORY_ITEM_ID AND T.ORGANIZATION_ID = F.ORGANIZATION_ID AND T.ORGANIZATION_ID = MP.ORGANIZATION_ID AND ((T.FROM_END_ITEM_UNIT_NUMBER IS NULL AND ML2.LOOKUP_CODE = 1) OR (T.FROM_END_ITEM_UNIT_NUMBER IS NOT NULL AND ML2.LOOKUP_CODE = 2)) AND ML2.LOOKUP_TYPE (+) = 'MTL_EFFECTIVITY_CONTROL' AND M.INVENTORY_ITEM_ID = T.CURRENT_ITEM_ID AND M.ORGANIZATION_ID = T.ORGANIZATION_ID AND HOU.ORGANIZATION_ID = T.ORGANIZATION_ID AND F.ITEM_TYPE = LU.LOOKUP_CODE(+) AND LU.LOOKUP_TYPE(+) = 'ITEM_TYPE' AND LU.LANGUAGE(+) = USERENV('LANG') AND LU.VIEW_APPLICATION_ID(+) = 3 AND ( LU.LOOKUP_CODE IS NULL OR LU.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(LU.LOOKUP_TYPE,LU.VIEW_APPLICATION_ID)) AND MIR.INVENTORY_ITEM_ID = T.PARENT_ITEM_ID AND MIR.ORGANIZATION_ID = T.ORGANIZATION_ID AND MIR.effectivity_date =(select max(mir1.effectivity_date) from mtl_item_revisions_b mir1 where mir1.inventory_item_id =T.parent_item_id and mir1.organization_id = T.organization_id and mir1.effectivity_date <=T.implosion_date and ((T.implemented_flag =1 and MIR1.implementation_date is not null) or(T.implemented_flag=2))) and LU2.LOOKUP_TYPE (+) = 'BOM_BASIS_TYPE' AND LU2.LOOKUP_CODE = nvl (T.BASIS_TYPE,1)

View Text - HTML Formatted

SELECT /*+ LEADING(T) USE_NL(T R F M) INDEX(R MTL_ITEM_REVISIONS_N1) NO_EXPAND */ T.ROWID
, MP.ORGANIZATION_CODE
, T.SEQUENCE_ID
, T.LOWEST_ITEM_ID
, T.CURRENT_ITEM_ID
, T.SORT_CODE
, T.CURRENT_LEVEL
, T.PARENT_ITEM_ID
, T.ORGANIZATION_ID
, BOM_GLOBALS.GET_CONCAT_SEGS(F.INVENTORY_ITEM_ID
, F.ORGANIZATION_ID)
, INV_MEANING_SEL.C_ITEM_DESCRIPTION(F.INVENTORY_ITEM_ID
, F.ORGANIZATION_ID)
, M.PRIMARY_UOM_CODE
, LU.MEANING ITEM_TYPE
, T.ALTERNATE_DESIGNATOR
, T.OPERATION_SEQ_NUM
, MIR.REVISION
, LU2.MEANING BASIS_TYPE
, T.COMPONENT_QUANTITY
, T.CURRENT_ASSEMBLY_TYPE
, T.CHANGE_NOTICE
, T.REVISED_ITEM_SEQUENCE_ID
, T.IMPLEMENTED_FLAG
, F.INVENTORY_ITEM_STATUS_CODE
, T.FROM_END_ITEM_UNIT_NUMBER
, T.TO_END_ITEM_UNIT_NUMBER
, ML2.MEANING
, DECODE(T.FROM_END_ITEM_UNIT_NUMBER
, NULL
, 2
, DECODE(T.DISABLE_DATE
, NULL
, 2
, 1)) DISABLED_FLAG
, HOU.NAME ORG_NAME
, BOM_GLOBALS.GET_CONCAT_SEGS(M.INVENTORY_ITEM_ID
, M.ORGANIZATION_ID)
, MIR.REVISION_LABEL
, T.PARENT_SORT_CODE
, MIR.LIFECYCLE_ID
, MIR.CURRENT_PHASE_ID
, DECODE(T.FROM_END_ITEM_UNIT_NUMBER
, NULL
, T.EFFECTIVITY_DATE
, NULL) COMPONENT_EFFECTIVE_FROM_DATE
, DECODE(T.FROM_END_ITEM_UNIT_NUMBER
, NULL
, T.DISABLE_DATE
, NULL) COMPONENT_EFFECTIVE_TO_DATE
FROM MTL_SYSTEM_ITEMS_B_KFV F
, MTL_SYSTEM_ITEMS_B_KFV M
, MFG_LOOKUPS ML2
, FND_LOOKUP_VALUES LU
, BOM_SMALL_IMPL_TEMP T
, MTL_PARAMETERS MP
, HR_ORGANIZATION_UNITS HOU
, MTL_ITEM_REVISIONS_B MIR
, MFG_LOOKUPS LU2
WHERE T.PARENT_ITEM_ID = F.INVENTORY_ITEM_ID
AND T.ORGANIZATION_ID = F.ORGANIZATION_ID
AND T.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND ((T.FROM_END_ITEM_UNIT_NUMBER IS NULL
AND ML2.LOOKUP_CODE = 1) OR (T.FROM_END_ITEM_UNIT_NUMBER IS NOT NULL
AND ML2.LOOKUP_CODE = 2))
AND ML2.LOOKUP_TYPE (+) = 'MTL_EFFECTIVITY_CONTROL'
AND M.INVENTORY_ITEM_ID = T.CURRENT_ITEM_ID
AND M.ORGANIZATION_ID = T.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = T.ORGANIZATION_ID
AND F.ITEM_TYPE = LU.LOOKUP_CODE(+)
AND LU.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND LU.LANGUAGE(+) = USERENV('LANG')
AND LU.VIEW_APPLICATION_ID(+) = 3
AND ( LU.LOOKUP_CODE IS NULL OR LU.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(LU.LOOKUP_TYPE
, LU.VIEW_APPLICATION_ID))
AND MIR.INVENTORY_ITEM_ID = T.PARENT_ITEM_ID
AND MIR.ORGANIZATION_ID = T.ORGANIZATION_ID
AND MIR.EFFECTIVITY_DATE =(SELECT MAX(MIR1.EFFECTIVITY_DATE)
FROM MTL_ITEM_REVISIONS_B MIR1
WHERE MIR1.INVENTORY_ITEM_ID =T.PARENT_ITEM_ID
AND MIR1.ORGANIZATION_ID = T.ORGANIZATION_ID
AND MIR1.EFFECTIVITY_DATE <=T.IMPLOSION_DATE
AND ((T.IMPLEMENTED_FLAG =1
AND MIR1.IMPLEMENTATION_DATE IS NOT NULL) OR(T.IMPLEMENTED_FLAG=2)))
AND LU2.LOOKUP_TYPE (+) = 'BOM_BASIS_TYPE'
AND LU2.LOOKUP_CODE = NVL (T.BASIS_TYPE
, 1)