FND Design Data [Home] [Help]

View: MTL_MFG_PRODUCTS_V

Product: INV - Inventory
Description:
Implementation/DBA Data: ViewAPPS.MTL_MFG_PRODUCTS_V
View Text

SELECT WRO.INVENTORY_ITEM_ID COMP_ITEM_ID
, DSJ.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, DSJ.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE
, HOU.NAME ORGANIZATION_NAME
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.INVENTORY_ITEM_ID PRODUCT_ITEM_ID
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, 'ASSEMBLY' ITEM_TYPE
, DSJ.START_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
, C_MTLN.LOT_NUMBER COMP_LOT_NUMBER
, C_MSNT.SERIAL_NUMBER COMP_SERIAL_NUMBER
FROM WIP_ENTITIES WE
, MTL_PARAMETERS ORG
, HR_ORGANIZATION_UNITS HOU
, WIP_DISCRETE_JOBS DSJ
, WIP_REQUIREMENT_OPERATIONS WRO
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_LOT_NUMBERS MTLN
, MTL_UNIT_TRANSACTIONS MSNT
, MTL_MATERIAL_TRANSACTIONS C_MMT
, MTL_TRANSACTION_LOT_NUMBERS C_MTLN
, MTL_UNIT_TRANSACTIONS C_MSNT
WHERE WE.WIP_ENTITY_ID = DSJ.WIP_ENTITY_ID
AND DSJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND DSJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND DSJ.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND DSJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND ORG.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_ID=WE.WIP_ENTITY_ID
AND MMT.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.INVENTORY_ITEM_ID = DSJ.PRIMARY_ITEM_ID
AND MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID (+)
AND MTLN.SERIAL_TRANSACTION_ID = MSNT.TRANSACTION_ID (+)
AND C_MMT.TRANSACTION_SOURCE_ID=WE.WIP_ENTITY_ID
AND C_MMT.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND C_MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND C_MMT.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND C_MMT.TRANSACTION_ID = C_MTLN.TRANSACTION_ID (+)
AND C_MTLN.SERIAL_TRANSACTION_ID = C_MSNT.TRANSACTION_ID (+) UNION ALL ( SELECT GMDTL_INGD.INVENTORY_ITEM_ID COMP_ITEM_ID
, WE.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE
, HOU.NAME ORGANIZATION_NAME
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.INVENTORY_ITEM_ID PRODUCT_ITEM_ID
, MSI.PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE
, DECODE(GMDTL_PROD.LINE_TYPE
, 1
, 'PRODUCT'
, 2
, 'BY PRODUCT' ) ITEM_TYPE
, GMDTL_PROD.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
, MSNT.SERIAL_NUMBER SERIAL_NUMBER
, C_MTLN.LOT_NUMBER COMP_LOT_NUMBER
, C_MSNT.SERIAL_NUMBER COMP_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_UNIT_TRANSACTIONS MSNT
, MTL_MATERIAL_TRANSACTIONS C_MMT
, MTL_TRANSACTION_LOT_NUMBERS C_MTLN
, MTL_UNIT_TRANSACTIONS C_MSNT
WHERE GMDTL_PROD.BATCH_ID = WE.WIP_ENTITY_ID
AND GMDTL_PROD.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_PROD.MATERIAL_DETAIL_ID
AND MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID (+)
AND MTLN.SERIAL_TRANSACTION_ID = MSNT.TRANSACTION_ID (+)
AND C_MMT.TRX_SOURCE_LINE_ID = GMDTL_INGD.MATERIAL_DETAIL_ID
AND C_MMT.TRANSACTION_SOURCE_ID=WE.WIP_ENTITY_ID
AND C_MMT.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND C_MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND C_MMT.TRANSACTION_ID = C_MTLN.TRANSACTION_ID (+)
AND C_MTLN.SERIAL_TRANSACTION_ID = C_MSNT.TRANSACTION_ID (+) )

Columns

Name
COMP_ITEM_ID
WIP_ENTITY_ID
WIP_ENTITY_NAME
ORGANIZATION_ID
ORGANIZATION_CODE
ORGANIZATION_NAME
ITEM_NUMBER
ITEM_DESCRIPTION
PRODUCT_ITEM_ID
PRIMARY_UOM_CODE
SECONDARY_UOM_CODE
ITEM_TYPE
JOB_QUANTITY
LOT_NUMBER
TRANSACTION_QUANTITY
PRIMARY_QUANTITY
SECONDARY_QUANTITY
GRADE_CODE
TRANSACTION_DATE
TRANSACTION_TYPE_ID
SUBINVENTORY_CODE
LOCATOR_ID
SERIAL_NUMBER
COMP_LOT_NUMBER
COMP_SERIAL_NUMBER