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

Source

View Text - Preformatted


SELECT ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , TEMP.PROJECT_ID , TEMP.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , ORG.ORGANIZATION_ID , SUB.NETTING_TYPE , NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE,1)), 2, 1, 0)) , 0) , NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE,1)), 2, 0, 1)) , 0) , NULL FROM MRP_SUB_INVENTORIES SUB, MTL_MATERIAL_TRANSACTIONS_TEMP TEMP, MRP_SYSTEM_ITEMS ITEMS , MRP_PLAN_ORGANIZATIONS_V ORG, MTL_ITEM_LOCATIONS MIL WHERE SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR AND SUB.SUB_INVENTORY_CODE = TEMP.SUBINVENTORY_CODE AND TEMP.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND TEMP.POSTING_FLAG = 'Y' AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR AND NVL(ITEMS.EFFECTIVITY_CONTROL, 1) = 1 AND MIL.ORGANIZATION_ID(+)= TEMP.ORGANIZATION_ID AND MIL.INVENTORY_LOCATION_ID(+)= TEMP.LOCATOR_ID AND NVL(TEMP.TRANSACTION_STATUS,0) <> 2 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status( temp.organization_id, temp.inventory_item_id, temp.subinventory_code, temp.locator_id, temp.lot_number, temp.lpn_id, temp.transaction_action_id), mms.status_id) AND mms.availability_type =1) GROUP BY ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, TEMP.PROJECT_ID, TEMP.TASK_ID, SUB.SUB_INVENTORY_CODE, ITEMS.COMPILE_DESIGNATOR, ORG.ORGANIZATION_ID, SUB.NETTING_TYPE UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , TEMP.PROJECT_ID , TEMP.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , ORG.ORGANIZATION_ID , SUB.NETTING_TYPE , NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE,1)), 2, 1, 0)) , 0) , NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE,1)), 2, 0, 1)) , 0) , DECODE(TEMP.TRANSACTION_SOURCE_TYPE_ID, 1, PJM_UNIT_EFF.RCV_UNIT_NUMBER(TEMP.RCV_TRANSACTION_ID), 5, PJM_UNIT_EFF.WIP_UNIT_NUMBER(TEMP.TRANSACTION_SOURCE_ID, TEMP.ORGANIZATION_ID)) FROM MRP_SUB_INVENTORIES SUB , MTL_MATERIAL_TRANSACTIONS_TEMP TEMP , MRP_SYSTEM_ITEMS ITEMS , MRP_PLAN_ORGANIZATIONS_V ORG, MTL_ITEM_LOCATIONS MIL WHERE SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR AND SUB.SUB_INVENTORY_CODE = TEMP.SUBINVENTORY_CODE AND TEMP.POSTING_FLAG = 'Y' AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR AND NVL(TEMP.TRANSACTION_STATUS,0) <> 2 AND MIL.ORGANIZATION_ID(+)= TEMP.ORGANIZATION_ID AND MIL.INVENTORY_LOCATION_ID(+)= TEMP.LOCATOR_ID AND NVL(ITEMS.EFFECTIVITY_CONTROL, 1) = 2 AND (TEMP.TRANSACTION_ACTION_ID = 1 AND TEMP.TRANSACTION_SOURCE_TYPE_ID IN (1, 5)) AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status( temp.organization_id, temp.inventory_item_id, temp.subinventory_code, temp.locator_id, temp.lot_number, temp.lpn_id, temp.transaction_action_id), mms.status_id) AND mms.availability_type =1) GROUP BY ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , TEMP.PROJECT_ID , TEMP.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , ORG.ORGANIZATION_ID , SUB.NETTING_TYPE , DECODE(TEMP.TRANSACTION_SOURCE_TYPE_ID, 1, PJM_UNIT_EFF.RCV_UNIT_NUMBER(TEMP.RCV_TRANSACTION_ID), 5, PJM_UNIT_EFF.WIP_UNIT_NUMBER(TEMP.TRANSACTION_SOURCE_ID, TEMP.ORGANIZATION_ID)) UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , TEMP.PROJECT_ID , TEMP.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , ORG.ORGANIZATION_ID , SUB.NETTING_TYPE , NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE,1)), 2, 1, 0)) , 0) , NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE,1)), 2, 0, 1)) , 0) , MSN.END_ITEM_UNIT_NUMBER FROM MRP_SUB_INVENTORIES SUB , MTL_MATERIAL_TRANSACTIONS_TEMP TEMP , MTL_SERIAL_NUMBERS_TEMP SERTEMP , MTL_SERIAL_NUMBERS MSN , MRP_SYSTEM_ITEMS ITEMS , MRP_PLAN_ORGANIZATIONS_V ORG, MTL_ITEM_LOCATIONS MIL WHERE SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR AND SUB.SUB_INVENTORY_CODE = TEMP.SUBINVENTORY_CODE AND TEMP.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND TEMP.POSTING_FLAG = 'Y' AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR AND NVL(TEMP.TRANSACTION_STATUS,0) <> 2 AND NVL(ITEMS.EFFECTIVITY_CONTROL, 1) = 2 AND SERTEMP.TRANSACTION_TEMP_ID = TEMP.TRANSACTION_TEMP_ID AND MSN.SERIAL_NUMBER = SERTEMP.FM_SERIAL_NUMBER AND MSN.INVENTORY_ITEM_ID = TEMP.INVENTORY_ITEM_ID AND MIL.ORGANIZATION_ID(+)= TEMP.ORGANIZATION_ID AND MIL.INVENTORY_LOCATION_ID(+)= TEMP.LOCATOR_ID AND NOT(TEMP.TRANSACTION_ACTION_ID = 1 AND TEMP.TRANSACTION_SOURCE_TYPE_ID IN (1, 5)) AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status( temp.organization_id, temp.inventory_item_id, temp.subinventory_code, temp.locator_id, temp.lot_number, temp.lpn_id, temp.transaction_action_id), mms.status_id) AND mms.availability_type =1) GROUP BY ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , TEMP.PROJECT_ID , TEMP.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , ORG.ORGANIZATION_ID , SUB.NETTING_TYPE , MSN.END_ITEM_UNIT_NUMBER UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , MTRL.PROJECT_ID , MTRL.TASK_ID , 'DUMMY', ITEMS.COMPILE_DESIGNATOR , ORG.ORGANIZATION_ID , 1, SUM(QUANTITY - NVL(QUANTITY_DELIVERED,0)), 0, PJM_UNIT_EFF.WIP_UNIT_NUMBER(MTRL.TXN_SOURCE_ID,MTRL.ORGANIZATION_ID) FROM MTL_TXN_REQUEST_LINES MTRL, MTL_TXN_REQUEST_HEADERS MTRH, MTL_TRANSACTION_TYPES MTT, MRP_SYSTEM_ITEMS ITEMS , MRP_PLAN_ORGANIZATIONS_V ORG WHERE ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR AND MTRL.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND MTRL.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND MTRH.MOVE_ORDER_TYPE = 6 AND MTT.TRANSACTION_ACTION_ID = 31 AND MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID AND MTRL.TRANSACTION_SOURCE_TYPE_ID = 5 AND MTRL.LINE_STATUS = 7 AND MTRL.LPN_ID IS NOT NULL AND MTRH.HEADER_ID = MTRL.HEADER_ID GROUP BY ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, MTRL.PROJECT_ID, MTRL.TASK_ID, ITEMS.COMPILE_DESIGNATOR, ORG.ORGANIZATION_ID, PJM_UNIT_EFF.WIP_UNIT_NUMBER(MTRL.TXN_SOURCE_ID,MTRL.ORGANIZATION_ID)

View Text - HTML Formatted

SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE
, 1))
, 2
, 1
, 0))
, 0)
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE
, 1))
, 2
, 0
, 1))
, 0)
, NULL
FROM MRP_SUB_INVENTORIES SUB
, MTL_MATERIAL_TRANSACTIONS_TEMP TEMP
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V ORG
, MTL_ITEM_LOCATIONS MIL
WHERE SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = TEMP.SUBINVENTORY_CODE
AND TEMP.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND TEMP.POSTING_FLAG = 'Y'
AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR
AND NVL(ITEMS.EFFECTIVITY_CONTROL
, 1) = 1
AND MIL.ORGANIZATION_ID(+)= TEMP.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= TEMP.LOCATOR_ID
AND NVL(TEMP.TRANSACTION_STATUS
, 0) <> 2
AND EXISTS (SELECT 'X'
FROM MTL_MATERIAL_STATUSES MMS
WHERE MMS.STATUS_ID= NVL(INV_MATERIAL_STATUS_GRP.GET_DEFAULT_STATUS( TEMP.ORGANIZATION_ID
, TEMP.INVENTORY_ITEM_ID
, TEMP.SUBINVENTORY_CODE
, TEMP.LOCATOR_ID
, TEMP.LOT_NUMBER
, TEMP.LPN_ID
, TEMP.TRANSACTION_ACTION_ID)
, MMS.STATUS_ID)
AND MMS.AVAILABILITY_TYPE =1) GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE
, 1))
, 2
, 1
, 0))
, 0)
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE
, 1))
, 2
, 0
, 1))
, 0)
, DECODE(TEMP.TRANSACTION_SOURCE_TYPE_ID
, 1
, PJM_UNIT_EFF.RCV_UNIT_NUMBER(TEMP.RCV_TRANSACTION_ID)
, 5
, PJM_UNIT_EFF.WIP_UNIT_NUMBER(TEMP.TRANSACTION_SOURCE_ID
, TEMP.ORGANIZATION_ID))
FROM MRP_SUB_INVENTORIES SUB
, MTL_MATERIAL_TRANSACTIONS_TEMP TEMP
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V ORG
, MTL_ITEM_LOCATIONS MIL
WHERE SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = TEMP.SUBINVENTORY_CODE
AND TEMP.POSTING_FLAG = 'Y'
AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR
AND NVL(TEMP.TRANSACTION_STATUS
, 0) <> 2
AND MIL.ORGANIZATION_ID(+)= TEMP.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= TEMP.LOCATOR_ID
AND NVL(ITEMS.EFFECTIVITY_CONTROL
, 1) = 2
AND (TEMP.TRANSACTION_ACTION_ID = 1
AND TEMP.TRANSACTION_SOURCE_TYPE_ID IN (1
, 5))
AND EXISTS (SELECT 'X'
FROM MTL_MATERIAL_STATUSES MMS
WHERE MMS.STATUS_ID= NVL(INV_MATERIAL_STATUS_GRP.GET_DEFAULT_STATUS( TEMP.ORGANIZATION_ID
, TEMP.INVENTORY_ITEM_ID
, TEMP.SUBINVENTORY_CODE
, TEMP.LOCATOR_ID
, TEMP.LOT_NUMBER
, TEMP.LPN_ID
, TEMP.TRANSACTION_ACTION_ID)
, MMS.STATUS_ID)
AND MMS.AVAILABILITY_TYPE =1) GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, DECODE(TEMP.TRANSACTION_SOURCE_TYPE_ID
, 1
, PJM_UNIT_EFF.RCV_UNIT_NUMBER(TEMP.RCV_TRANSACTION_ID)
, 5
, PJM_UNIT_EFF.WIP_UNIT_NUMBER(TEMP.TRANSACTION_SOURCE_ID
, TEMP.ORGANIZATION_ID)) UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE
, 1))
, 2
, 1
, 0))
, 0)
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE
, 1))
, 2
, 0
, 1))
, 0)
, MSN.END_ITEM_UNIT_NUMBER
FROM MRP_SUB_INVENTORIES SUB
, MTL_MATERIAL_TRANSACTIONS_TEMP TEMP
, MTL_SERIAL_NUMBERS_TEMP SERTEMP
, MTL_SERIAL_NUMBERS MSN
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V ORG
, MTL_ITEM_LOCATIONS MIL
WHERE SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = TEMP.SUBINVENTORY_CODE
AND TEMP.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND TEMP.POSTING_FLAG = 'Y'
AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR
AND NVL(TEMP.TRANSACTION_STATUS
, 0) <> 2
AND NVL(ITEMS.EFFECTIVITY_CONTROL
, 1) = 2
AND SERTEMP.TRANSACTION_TEMP_ID = TEMP.TRANSACTION_TEMP_ID
AND MSN.SERIAL_NUMBER = SERTEMP.FM_SERIAL_NUMBER
AND MSN.INVENTORY_ITEM_ID = TEMP.INVENTORY_ITEM_ID
AND MIL.ORGANIZATION_ID(+)= TEMP.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= TEMP.LOCATOR_ID
AND NOT(TEMP.TRANSACTION_ACTION_ID = 1
AND TEMP.TRANSACTION_SOURCE_TYPE_ID IN (1
, 5))
AND EXISTS (SELECT 'X'
FROM MTL_MATERIAL_STATUSES MMS
WHERE MMS.STATUS_ID= NVL(INV_MATERIAL_STATUS_GRP.GET_DEFAULT_STATUS( TEMP.ORGANIZATION_ID
, TEMP.INVENTORY_ITEM_ID
, TEMP.SUBINVENTORY_CODE
, TEMP.LOCATOR_ID
, TEMP.LOT_NUMBER
, TEMP.LPN_ID
, TEMP.TRANSACTION_ACTION_ID)
, MMS.STATUS_ID)
AND MMS.AVAILABILITY_TYPE =1) GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, MSN.END_ITEM_UNIT_NUMBER UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MTRL.PROJECT_ID
, MTRL.TASK_ID
, 'DUMMY'
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, 1
, SUM(QUANTITY - NVL(QUANTITY_DELIVERED
, 0))
, 0
, PJM_UNIT_EFF.WIP_UNIT_NUMBER(MTRL.TXN_SOURCE_ID
, MTRL.ORGANIZATION_ID)
FROM MTL_TXN_REQUEST_LINES MTRL
, MTL_TXN_REQUEST_HEADERS MTRH
, MTL_TRANSACTION_TYPES MTT
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V ORG
WHERE ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR
AND MTRL.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND MTRL.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND MTRH.MOVE_ORDER_TYPE = 6
AND MTT.TRANSACTION_ACTION_ID = 31
AND MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
AND MTRL.TRANSACTION_SOURCE_TYPE_ID = 5
AND MTRL.LINE_STATUS = 7
AND MTRL.LPN_ID IS NOT NULL
AND MTRH.HEADER_ID = MTRL.HEADER_ID GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MTRL.PROJECT_ID
, MTRL.TASK_ID
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, PJM_UNIT_EFF.WIP_UNIT_NUMBER(MTRL.TXN_SOURCE_ID
, MTRL.ORGANIZATION_ID)