View: MTL_MFG_COMPONENTS_V

Product:	INV - Inventory
Description:
Implementation/DBA Data:	APPS.MTL_MFG_COMPONENTS_V

View Text

SELECT WE.GEN_OBJECT_ID
, WE.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE
, WE.PRIMARY_ITEM_ID PRODUCT_ITEM_ID
, HOU.NAME ORGANIZATION_NAME
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, WRO.INVENTORY_ITEM_ID
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, 'COMPONENT' ITEM_TYPE
, WRO.REQUIRED_QUANTITY JOB_QUANTITY
, MTLN.LOT_NUMBER
, DECODE(MSNT.SERIAL_NUMBER
, NULL
, DECODE( MTLN.TRANSACTION_QUANTITY
, NULL
, MMT.TRANSACTION_QUANTITY
, MTLN.TRANSACTION_QUANTITY)
, 1) TRANSACTION_QUANTITY
, DECODE(MSNT.SERIAL_NUMBER
, NULL
, DECODE( MTLN.PRIMARY_QUANTITY
, NULL
, MMT.PRIMARY_QUANTITY
, MTLN.PRIMARY_QUANTITY)
, 1) PRIMARY_QUANTITY
, DECODE(MSNT.SERIAL_NUMBER
, NULL
, DECODE(MTLN.SECONDARY_TRANSACTION_QUANTITY
, NULL
, MMT.SECONDARY_TRANSACTION_QUANTITY
, MTLN.SECONDARY_TRANSACTION_QUANTITY)
, NULL) SECONDARY_QUANTITY
, MTLN.GRADE_CODE
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_TYPE_ID
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MSNT.SERIAL_NUMBER SERIAL_NUMBER
, P_MTLN.LOT_NUMBER PRODUCT_LOT_NUMBER
, P_MSNT.SERIAL_NUMBER PRODUCT_SERIAL_NUMBER
FROM MTL_PARAMETERS ORG
, HR_ORGANIZATION_UNITS HOU
, WIP_REQUIREMENT_OPERATIONS WRO
, WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_LOT_NUMBERS MTLN
, MTL_UNIT_TRANSACTIONS MSNT
, MTL_MATERIAL_TRANSACTIONS P_MMT
, MTL_TRANSACTION_LOT_NUMBERS P_MTLN
, MTL_UNIT_TRANSACTIONS P_MSNT
WHERE WE.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WE.ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND WE.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID
AND MMT.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID (+)
AND MTLN.SERIAL_TRANSACTION_ID = MSNT.TRANSACTION_ID (+)
AND ((MSI.LOT_CONTROL_CODE <> 1
AND MSI.SERIAL_NUMBER_CONTROL_CODE = 1) OR (MSI.LOT_CONTROL_CODE = 1
AND MSI.SERIAL_NUMBER_CONTROL_CODE = 1) OR (MSI.LOT_CONTROL_CODE <> 1
AND MSI.SERIAL_NUMBER_CONTROL_CODE<>1) )
AND P_MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID
AND P_MMT.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND P_MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND P_MMT.TRANSACTION_ID = P_MTLN.TRANSACTION_ID (+)
AND P_MTLN.SERIAL_TRANSACTION_ID = P_MSNT.TRANSACTION_ID (+) UNION ALL SELECT WE.GEN_OBJECT_ID
, WE.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE
, WE.PRIMARY_ITEM_ID PRODUCT_ITEM_ID
, HOU.NAME ORGANIZATION_NAME
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, WRO.INVENTORY_ITEM_ID
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, 'COMPONENT' ITEM_TYPE
, WRO.REQUIRED_QUANTITY JOB_QUANTITY
, NULL LOT_NUMBER
, 1 TRANSACTION_QUANTITY
, 1 PRIMARY_QUANTITY
, NULL SECONDARY_QUANTITY
, NULL GRADE_CODE
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_TYPE_ID
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MSNT.SERIAL_NUMBER SERIAL_NUMBER
, P_MTLN.LOT_NUMBER PRODUCT_LOT_NUMBER
, P_MSNT.SERIAL_NUMBER PRODUCT_SERIAL_NUMBER
FROM MTL_PARAMETERS ORG
, HR_ORGANIZATION_UNITS HOU
, WIP_REQUIREMENT_OPERATIONS WRO
, WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_UNIT_TRANSACTIONS MSNT
, MTL_MATERIAL_TRANSACTIONS P_MMT
, MTL_TRANSACTION_LOT_NUMBERS P_MTLN
, MTL_UNIT_TRANSACTIONS P_MSNT
WHERE WE.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WE.ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND WE.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID
AND MMT.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.TRANSACTION_ID = MSNT.TRANSACTION_ID (+)
AND MMT.INVENTORY_ITEM_ID = MSNT.INVENTORY_ITEM_ID
AND P_MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID
AND P_MMT.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND P_MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND P_MMT.TRANSACTION_ID = P_MTLN.TRANSACTION_ID (+)
AND P_MTLN.SERIAL_TRANSACTION_ID = P_MSNT.TRANSACTION_ID (+) UNION ALL SELECT WE.GEN_OBJECT_ID
, WE.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE
, GMDTL_PROD.INVENTORY_ITEM_ID PRODUCT_ITEM_ID
, HOU.NAME ORGANIZATION_NAME
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.INVENTORY_ITEM_ID
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, 'INGREDIENT' ITEM_TYPE
, GMDTL_INGD.PLAN_QTY JOB_QUANTITY
, MTLN.LOT_NUMBER
, DECODE(MTLN.TRANSACTION_QUANTITY
, NULL
, MMT.TRANSACTION_QUANTITY
, MTLN.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY
, DECODE(MTLN.PRIMARY_QUANTITY
, NULL
, MMT.PRIMARY_QUANTITY
, MTLN.PRIMARY_QUANTITY) PRIMARY_QUANTITY
, DECODE(MTLN.SECONDARY_TRANSACTION_QUANTITY
, NULL
, MMT.SECONDARY_TRANSACTION_QUANTITY
, MTLN.SECONDARY_TRANSACTION_QUANTITY) SECONDARY_QUANTITY
, MTLN.GRADE_CODE
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_TYPE_ID
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, NULL SERIAL_NUMBER
, P_MTLN.LOT_NUMBER PRODUCT_LOT_NUMBER
, NULL PRODUCT_SERIAL_NUMBER
FROM MTL_PARAMETERS ORG
, HR_ORGANIZATION_UNITS HOU
, GME_MATERIAL_DETAILS GMDTL_PROD
, GME_MATERIAL_DETAILS GMDTL_INGD
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_LOT_NUMBERS MTLN
, WIP_ENTITIES WE
, MTL_MATERIAL_TRANSACTIONS P_MMT
, MTL_TRANSACTION_LOT_NUMBERS P_MTLN
WHERE GMDTL_PROD.BATCH_ID = WE.WIP_ENTITY_ID
AND GMDTL_INGD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WE.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND WE.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND WE.ENTITY_TYPE IN (9
, 10)
AND GMDTL_PROD.LINE_TYPE IN (1
, 2)
AND GMDTL_INGD.BATCH_ID = GMDTL_PROD.BATCH_ID
AND GMDTL_INGD.LINE_TYPE = -1
AND ORG.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND MMT.TRX_SOURCE_LINE_ID = GMDTL_INGD.MATERIAL_DETAIL_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID (+)
AND P_MMT.TRX_SOURCE_LINE_ID = GMDTL_PROD.MATERIAL_DETAIL_ID
AND P_MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND P_MMT.TRANSACTION_ID = P_MTLN.TRANSACTION_ID (+)
AND MMT.TRANSACTION_SOURCE_ID = GMDTL_INGD.BATCH_ID
AND MMT.INVENTORY_ITEM_ID = GMDTL_INGD.INVENTORY_ITEM_ID
AND P_MMT.TRANSACTION_SOURCE_ID = GMDTL_PROD.BATCH_ID
AND P_MMT.INVENTORY_ITEM_ID = GMDTL_PROD.INVENTORY_ITEM_ID GROUP BY WE.GEN_OBJECT_ID
, WE.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE
, GMDTL_PROD.INVENTORY_ITEM_ID
, HOU.NAME
, MSI.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MSI.INVENTORY_ITEM_ID
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, 'INGREDIENT'
, GMDTL_INGD.PLAN_QTY
, MTLN.LOT_NUMBER
, DECODE(MTLN.TRANSACTION_QUANTITY
, NULL
, MMT.TRANSACTION_QUANTITY
, MTLN.TRANSACTION_QUANTITY)
, DECODE(MTLN.PRIMARY_QUANTITY
, NULL
, MMT.PRIMARY_QUANTITY
, MTLN.PRIMARY_QUANTITY)
, DECODE(MTLN.SECONDARY_TRANSACTION_QUANTITY
, NULL
, MMT.SECONDARY_TRANSACTION_QUANTITY
, MTLN.SECONDARY_TRANSACTION_QUANTITY)
, MTLN.GRADE_CODE
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_TYPE_ID
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, P_MTLN.LOT_NUMBER

Columns