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.MTL_MFG_PRODUCTS_V

Source

View Text - Preformatted


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 (+) )

View Text - HTML Formatted

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 (+) )