View: MRP_AP_ONHAND_TRX_SUPPLIES_V

View Text

SELECT X.ORGANIZATION_ID
, X.INVENTORY_ITEM_ID
, X.SUB_INV_CODE
, X.LOCATOR_ID
, Y.CONCATENATED_SEGMENTS LOCATOR_NAME
, X.SERIAL_NUMBER
, X.PROJECT_ID
, X.TASK_ID
, X.PLANNING_GROUP
, X.END_ITEM_UNIT_NUMBER
, X.QUANTITY
, X.SOURCE_TYPE
, X.RN4 RN4
, X.RN3 RN3
, X.RN2 RN2
, X.RN1 RN1
FROM MTL_ITEM_LOCATIONS_KFV Y
, ( SELECT MMTT.ORGANIZATION_ID
, MMTT.INVENTORY_ITEM_ID
, MMTT.SUBINVENTORY_CODE SUB_INV_CODE
, MMTT.LOCATOR_ID
, MMTT.SERIAL_NUMBER
, MMTT.PROJECT_ID
, MMTT.TASK_ID
, MPP.PLANNING_GROUP
, NULL END_ITEM_UNIT_NUMBER
, SUM(MMTT.PRIMARY_QUANTITY) QUANTITY
, 3 SOURCE_TYPE
, MAX( NVL(MPP.RN
, 0)) RN4
, MAX( MMTT.RN) RN3
, MAX( MASIS.RN) RN2
, MAX( MP.RN) RN1
FROM MRP_AP_PRJ_PARAS_SN MPP
, MRP_AP_SYS_ITEMS_SN MASIS
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_MTRX_TMP_SN MMTT
WHERE MP.ORGANIZATION_ID= MMTT.ORGANIZATION_ID
AND MPP.ORGANIZATION_ID(+)= MMTT.ORGANIZATION_ID
AND MPP.PROJECT_ID(+)= MMTT.PROJECT_ID
AND MASIS.ORGANIZATION_ID= MMTT.ORGANIZATION_ID
AND MASIS.INVENTORY_ITEM_ID= MMTT.INVENTORY_ITEM_ID
AND MASIS.EFFECTIVITY_CONTROL= 1 GROUP BY MMTT.INVENTORY_ITEM_ID
, MMTT.ORGANIZATION_ID
, MMTT.PROJECT_ID
, MMTT.TASK_ID
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MMTT.SERIAL_NUMBER
, NULL
, MPP.PLANNING_GROUP UNION ALL SELECT MMTT.ORGANIZATION_ID
, MMTT.INVENTORY_ITEM_ID
, MMTT.SUBINVENTORY_CODE SUB_INV_CODE
, MMTT.LOCATOR_ID
, MMTT.SERIAL_NUMBER
, MMTT.PROJECT_ID
, MMTT.TASK_ID
, MPP.PLANNING_GROUP
, DECODE( MMTT.TRANSACTION_SOURCE_TYPE_ID
, 1
, PJM_UNIT_EFF.RCV_UNIT_NUMBER( MMTT.RCV_TRANSACTION_ID)
, PJM_UNIT_EFF.WIP_UNIT_NUMBER( MMTT.TRANSACTION_SOURCE_ID
, MMTT.ORGANIZATION_ID)) END_ITEM_UNIT_NUMBER
, SUM(MMTT.PRIMARY_QUANTITY) QUANTITY
, 4 SOURCE_TYPE
, MAX( NVL(MPP.RN
, 0)) RN4
, MAX( MMTT.RN) RN3
, MAX( MASIS.RN) RN2
, MAX( MP.RN) RN1
FROM MRP_AP_PRJ_PARAS_SN MPP
, MRP_AP_SYS_ITEMS_SN MASIS
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_MTRX_TMP_SN MMTT
WHERE MP.ORGANIZATION_ID= MMTT.ORGANIZATION_ID
AND MPP.ORGANIZATION_ID(+)= MMTT.ORGANIZATION_ID
AND MPP.PROJECT_ID(+)= MMTT.PROJECT_ID
AND MMTT.TRANSACTION_ACTION_ID= 1
AND MMTT.TRANSACTION_SOURCE_TYPE_ID IN (1
, 5)
AND MASIS.ORGANIZATION_ID= MMTT.ORGANIZATION_ID
AND MASIS.INVENTORY_ITEM_ID= MMTT.INVENTORY_ITEM_ID
AND MASIS.EFFECTIVITY_CONTROL= 2 GROUP BY MMTT.INVENTORY_ITEM_ID
, MMTT.ORGANIZATION_ID
, MMTT.PROJECT_ID
, MMTT.TASK_ID
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MMTT.SERIAL_NUMBER
, DECODE( MMTT.TRANSACTION_SOURCE_TYPE_ID
, 1
, PJM_UNIT_EFF.RCV_UNIT_NUMBER( MMTT.RCV_TRANSACTION_ID)
, PJM_UNIT_EFF.WIP_UNIT_NUMBER( MMTT.TRANSACTION_SOURCE_ID
, MMTT.ORGANIZATION_ID))
, MPP.PLANNING_GROUP UNION ALL SELECT MMTT.ORGANIZATION_ID
, MMTT.INVENTORY_ITEM_ID
, MMTT.SUBINVENTORY_CODE SUB_INV_CODE
, MMTT.LOCATOR_ID
, MMTT.SERIAL_NUMBER
, MMTT.PROJECT_ID
, MMTT.TASK_ID
, MPP.PLANNING_GROUP
, MSN.END_ITEM_UNIT_NUMBER
, SUM(MMTT.PRIMARY_QUANTITY) QUANTITY
, 5 SOURCE_TYPE
, MAX( NVL(MPP.RN
, 0)) RN4
, MAX( MMTT.RN) RN3
, MAX( MASIS.RN) RN2
, MAX( MP.RN) RN1
FROM MRP_AP_PRJ_PARAS_SN MPP
, MTL_SERIAL_NUMBERS_TEMP MSNT
, MRP_AP_SRL_NUMS_SN MSN
, MRP_AP_SYS_ITEMS_SN MASIS
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_MTRX_TMP_SN MMTT
WHERE MP.ORGANIZATION_ID= MMTT.ORGANIZATION_ID
AND MPP.ORGANIZATION_ID(+)= MMTT.ORGANIZATION_ID
AND MPP.PROJECT_ID(+)= MMTT.PROJECT_ID
AND MSNT.TRANSACTION_TEMP_ID= MMTT.TRANSACTION_TEMP_ID
AND MSNT.FM_SERIAL_NUMBER= MSN.SERIAL_NUMBER
AND MSN.INVENTORY_ITEM_ID= MMTT.INVENTORY_ITEM_ID
AND MMTT.POSTING_FLAG= 'Y'
AND NOT( MMTT.TRANSACTION_ACTION_ID= 1
AND MMTT.TRANSACTION_SOURCE_TYPE_ID IN (1
, 5))
AND MASIS.ORGANIZATION_ID= MMTT.ORGANIZATION_ID
AND MASIS.INVENTORY_ITEM_ID= MMTT.INVENTORY_ITEM_ID
AND MASIS.EFFECTIVITY_CONTROL= 2 GROUP BY MMTT.INVENTORY_ITEM_ID
, MMTT.ORGANIZATION_ID
, MMTT.PROJECT_ID
, MMTT.TASK_ID
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MMTT.SERIAL_NUMBER
, MSN.END_ITEM_UNIT_NUMBER
, MPP.PLANNING_GROUP ) X
WHERE Y.INVENTORY_LOCATION_ID(+)= X.LOCATOR_ID
AND Y.ORGANIZATION_ID(+)= X.ORGANIZATION_ID

Columns