VIEW: APPS.MTL_MFG_COMPONENTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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