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.

[Home] [Help]

View: MRP_AP_ONHAND_SUPPLIES_V

Product:	MRP - Master Scheduling/MRP
Description:
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT /*+ ORDERED */ X.ORGANIZATION_ID
, X.INVENTORY_ITEM_ID
, X.SUBINVENTORY_CODE
, X.LOCATOR_ID
, X.LOT_NUMBER
, X.LOT_QUANTITY
, NULL SERIAL_NUMBER
, MLN.EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, MPP.PLANNING_GROUP
, NULL END_ITEM_UNIT_NUMBER
, Y.CONCATENATED_SEGMENTS LOCATOR_NAME
, 1 SOURCE_TYPE
, NVL( MPP.RN
, 0) RN6
, NVL( MIL.RN
, 0) RN5
, NVL( MLN.RN
, 0) RN4
, X.RN RN3
, MASIS.RN RN2
, MP.RN RN1
FROM ( SELECT MOQ.ORGANIZATION_ID
, MOQ.INVENTORY_ITEM_ID
, MOQ.SUBINVENTORY_CODE
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER
, SUM(MOQ.TRANSACTION_QUANTITY) LOT_QUANTITY
, MAX(MOQ.RN) RN
FROM MRP_AP_OH_QTYS_SN MOQ GROUP BY MOQ.ORGANIZATION_ID
, MOQ.INVENTORY_ITEM_ID
, MOQ.SUBINVENTORY_CODE
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER ) X
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_SYS_ITEMS_SN MASIS
, MRP_AP_LOT_NUMS_SN MLN
, MRP_AP_ITEM_LOCS_SN MIL
, MRP_AP_PRJ_PARAS_SN MPP
, MTL_ITEM_LOCATIONS_KFV Y
WHERE MP.ORGANIZATION_ID= X.ORGANIZATION_ID
AND MLN.ORGANIZATION_ID(+)= X.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID(+)= X.INVENTORY_ITEM_ID
AND MLN.LOT_NUMBER(+)= X.LOT_NUMBER
AND MASIS.ORGANIZATION_ID= X.ORGANIZATION_ID
AND MASIS.INVENTORY_ITEM_ID= X.INVENTORY_ITEM_ID
AND MASIS.EFFECTIVITY_CONTROL= 1
AND MIL.ORGANIZATION_ID(+)= X.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= X.LOCATOR_ID
AND MPP.PROJECT_ID(+)= MIL.PROJECT_ID
AND MPP.ORGANIZATION_ID(+)= MIL.ORGANIZATION_ID
AND Y.INVENTORY_LOCATION_ID(+)= X.LOCATOR_ID
AND Y.ORGANIZATION_ID(+)= X.ORGANIZATION_ID UNION ALL SELECT /*+ ORDERED */ X.ORGANIZATION_ID
, X.INVENTORY_ITEM_ID
, X.SUBINVENTORY_CODE
, X.LOCATOR_ID
, X.LOT_NUMBER
, X.LOT_QUANTITY
, X.SERIAL_NUMBER
, MLN.EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, MPP.PLANNING_GROUP
, X.END_ITEM_UNIT_NUMBER
, Y.CONCATENATED_SEGMENTS LOCATOR_NAME
, 2 SOURCE_TYPE
, NVL( MPP.RN
, 0) RN6
, NVL( MIL.RN
, 0) RN5
, NVL( MLN.RN
, 0) RN4
, X.RN RN3
, MASIS.RN RN2
, MP.RN RN1
FROM ( SELECT MSN.CURRENT_ORGANIZATION_ID ORGANIZATION_ID
, MSN.INVENTORY_ITEM_ID
, MSN.CURRENT_SUBINVENTORY_CODE SUBINVENTORY_CODE
, MSN.CURRENT_LOCATOR_ID LOCATOR_ID
, MSN.LOT_NUMBER
, MSN.SERIAL_NUMBER
, SUM(1) LOT_QUANTITY
, MSN.END_ITEM_UNIT_NUMBER
, MAX(RN) RN
FROM MRP_AP_SRL_NUMS_SN MSN
WHERE MSN.CURRENT_STATUS IN ( 3
, 5) GROUP BY MSN.CURRENT_ORGANIZATION_ID
, MSN.INVENTORY_ITEM_ID
, MSN.CURRENT_SUBINVENTORY_CODE
, MSN.CURRENT_LOCATOR_ID
, MSN.SERIAL_NUMBER
, MSN.LOT_NUMBER
, MSN.END_ITEM_UNIT_NUMBER ) X
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_SYS_ITEMS_SN MASIS
, MRP_AP_LOT_NUMS_SN MLN
, MRP_AP_ITEM_LOCS_SN MIL
, MRP_AP_PRJ_PARAS_SN MPP
, MTL_ITEM_LOCATIONS_KFV Y
WHERE MP.ORGANIZATION_ID= X.ORGANIZATION_ID
AND MLN.ORGANIZATION_ID(+)= X.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID(+)= X.INVENTORY_ITEM_ID
AND MLN.LOT_NUMBER(+)= X.LOT_NUMBER
AND MASIS.ORGANIZATION_ID= X.ORGANIZATION_ID
AND MASIS.INVENTORY_ITEM_ID= X.INVENTORY_ITEM_ID
AND MASIS.EFFECTIVITY_CONTROL= 2
AND MIL.ORGANIZATION_ID(+)= X.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= X.LOCATOR_ID
AND MPP.PROJECT_ID(+)= MIL.PROJECT_ID
AND MPP.ORGANIZATION_ID(+)= MIL.ORGANIZATION_ID
AND Y.INVENTORY_LOCATION_ID(+)= X.LOCATOR_ID
AND Y.ORGANIZATION_ID(+)= X.ORGANIZATION_ID

Columns

Name
ORGANIZATION_ID
INVENTORY_ITEM_ID
SUBINVENTORY_CODE
LOCATOR_ID
LOT_NUMBER
LOT_QUANTITY
SERIAL_NUMBER
EXPIRATION_DATE
PROJECT_ID
TASK_ID
PLANNING_GROUP
END_ITEM_UNIT_NUMBER
LOCATOR_NAME
SOURCE_TYPE
RN6
RN5
RN4
RN3
RN2
RN1