View: GMP_NETTABLE_ONHANDS_V

Product:	GMP - Process Manufacturing Process Planning
Description:
Implementation/DBA Data:	APPS.GMP_NETTABLE_ONHANDS_V

View Text

SELECT MOQ.ORGANIZATION_ID
, MOQ.INVENTORY_ITEM_ID
, MOQ.SUBINVENTORY_CODE
, MOQ.LOT_NUMBER
, MOQ.TRANSACTION_QUANTITY QUANTITY
, MOQ.PLANNING_ORGANIZATION_ID
, MOQ.PLANNING_TP_TYPE
, MOQ.OWNING_ORGANIZATION_ID
, MOQ.OWNING_TP_TYPE
, MLN.EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, MPP.PLANNING_GROUP
, NULL END_ITEM_UNIT_NUMBER
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ
, MTL_SYSTEM_ITEMS_B MASIS
, MTL_LOT_NUMBERS MLN
, MTL_ITEM_LOCATIONS MIL
, PJM_PROJECT_PARAMETERS MPP
WHERE MOQ.INVENTORY_ITEM_ID= MASIS.INVENTORY_ITEM_ID
AND MOQ.ORGANIZATION_ID= MASIS.ORGANIZATION_ID
AND MASIS.EFFECTIVITY_CONTROL= 1
AND ( ((MASIS.MRP_PLANNING_CODE IS NOT NULL OR (MASIS.PICK_COMPONENTS_FLAG = 'Y' OR MASIS.BOM_ITEM_TYPE=3))
AND ( MASIS.INVENTORY_ITEM_FLAG = 'Y' OR MASIS.ENG_ITEM_FLAG = 'Y')
AND MASIS.PLANNING_MAKE_BUY_CODE IN (1
, 2)
AND MASIS.PRIMARY_UOM_CODE IS NOT NULL) OR MASIS.ATP_FLAG <> 'N' OR MASIS.ATP_COMPONENTS_FLAG <> 'N' OR MASIS.DRP_PLANNED_FLAG = 1 OR MASIS.EAM_ITEM_TYPE IS NOT NULL) /* INCLUDED CHKS FOR MATERIAL STATUS - SUBINVENTORY */
AND EXISTS ( SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = MOQ.SUBINVENTORY_CODE
AND MSI.AVAILABILITY_TYPE = 1 )
AND MLN.ORGANIZATION_ID(+)= MOQ.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID(+)= MOQ.INVENTORY_ITEM_ID
AND MLN.LOT_NUMBER(+)= MOQ.LOT_NUMBER /* INCLUDED CHKS FOR MATERIAL STATUS - LOT NUMBER*/
AND ((MOQ.LOT_NUMBER IS NULL) OR ( MOQ.LOT_NUMBER IS NOT NULL
AND EXISTS ( SELECT 'X'
FROM MTL_LOT_NUMBERS MLN2
WHERE MLN2.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND MLN2.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND MLN2.LOT_NUMBER = MOQ.LOT_NUMBER
AND MLN2.AVAILABILITY_TYPE = 1 ) ) )
AND MIL.ORGANIZATION_ID(+)= MOQ.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= MOQ.LOCATOR_ID /* INCLUDED CHKS FOR MATERIAL STATUS - LOCATOR*/
AND (( MOQ.LOCATOR_ID IS NULL) OR ( MOQ.LOCATOR_ID IS NOT NULL
AND EXISTS ( SELECT 'X'
FROM MTL_ITEM_LOCATIONS MIL2
WHERE MIL2.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND MIL2.INVENTORY_LOCATION_ID = MOQ.LOCATOR_ID
AND MIL2.AVAILABILITY_TYPE = 1 ) ) )
AND MPP.PROJECT_ID(+)= MIL.PROJECT_ID
AND MPP.ORGANIZATION_ID(+)= MIL.ORGANIZATION_ID /* ================== 2============================= */ UNION ALL SELECT /*+ ORDERED */ X.ORGANIZATION_ID
, X.INVENTORY_ITEM_ID
, X.SUBINVENTORY_CODE
, X.LOT_NUMBER
, X.LOT_QUANTITY QUANTITY
, X.PLANNING_ORGANIZATION_ID
, X.PLANNING_TP_TYPE
, X.OWNING_ORGANIZATION_ID
, X.OWNING_TP_TYPE
, MLN.EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, MPP.PLANNING_GROUP
, X.END_ITEM_UNIT_NUMBER
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
, 1 LOT_QUANTITY
, TO_NUMBER(NULL) PLANNING_ORGANIZATION_ID
, TO_NUMBER(NULL) PLANNING_TP_TYPE
, TO_NUMBER(NULL) OWNING_ORGANIZATION_ID
, TO_NUMBER(NULL) OWNING_TP_TYPE
, MSN.END_ITEM_UNIT_NUMBER
FROM MTL_SERIAL_NUMBERS MSN
, MTL_SYSTEM_ITEMS_B MASIS
WHERE MSN.CURRENT_STATUS IN ( 3
, 5)
AND MASIS.ORGANIZATION_ID= MSN.CURRENT_ORGANIZATION_ID
AND MASIS.INVENTORY_ITEM_ID= MSN.INVENTORY_ITEM_ID
AND ( ((MASIS.MRP_PLANNING_CODE IS NOT NULL OR (MASIS.PICK_COMPONENTS_FLAG = 'Y' OR MASIS.BOM_ITEM_TYPE=3))
AND ( MASIS.INVENTORY_ITEM_FLAG = 'Y' OR MASIS.ENG_ITEM_FLAG = 'Y')
AND MASIS.PLANNING_MAKE_BUY_CODE IN (1
, 2)
AND MASIS.PRIMARY_UOM_CODE IS NOT NULL) OR MASIS.ATP_FLAG <> 'N' OR MASIS.ATP_COMPONENTS_FLAG <> 'N' OR MASIS.DRP_PLANNED_FLAG = 1 OR MASIS.EAM_ITEM_TYPE IS NOT NULL)
AND MASIS.EFFECTIVITY_CONTROL= 2 ) X
, MTL_LOT_NUMBERS MLN
, MTL_ITEM_LOCATIONS MIL
, PJM_PROJECT_PARAMETERS MPP
WHERE MLN.ORGANIZATION_ID(+)= X.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID(+)= X.INVENTORY_ITEM_ID
AND MLN.LOT_NUMBER(+)= X.LOT_NUMBER /* INCLUDED CHKS FOR MATERIAL STATUS - SUBINVENTORY*/
AND EXISTS ( SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = X.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = X.SUBINVENTORY_CODE
AND MSI.AVAILABILITY_TYPE = 1 ) /* INCLUDED CHKS FOR MATERIAL STATUS - LOT NUMBER*/
AND ((X.LOT_NUMBER IS NULL) OR ( X.LOT_NUMBER IS NOT NULL
AND EXISTS ( SELECT 'X'
FROM MTL_LOT_NUMBERS MLN2
WHERE MLN2.ORGANIZATION_ID = X.ORGANIZATION_ID
AND MLN2.INVENTORY_ITEM_ID = X.INVENTORY_ITEM_ID
AND MLN2.LOT_NUMBER = X.LOT_NUMBER
AND MLN2.AVAILABILITY_TYPE = 1 ) ) )
AND MIL.ORGANIZATION_ID(+)= X.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= X.LOCATOR_ID /* INCLUDED CHKS FOR MATERIAL STATUS - LOCATOR*/
AND (( X.LOCATOR_ID IS NULL) OR ( X.LOCATOR_ID IS NOT NULL
AND EXISTS ( SELECT 'X'
FROM MTL_ITEM_LOCATIONS MIL2
WHERE MIL2.ORGANIZATION_ID = X.ORGANIZATION_ID
AND MIL2.INVENTORY_LOCATION_ID = X.LOCATOR_ID
AND MIL2.AVAILABILITY_TYPE = 1 ) ) )
AND MPP.PROJECT_ID(+)= MIL.PROJECT_ID
AND MPP.ORGANIZATION_ID(+)= MIL.ORGANIZATION_ID /* ================== 3 ============================= */ UNION ALL SELECT MMTT.ORGANIZATION_ID
, MMTT.INVENTORY_ITEM_ID
, MMTT.SUBINVENTORY_CODE SUB_INV_CODE
, TO_CHAR(NULL) LOT_NUMBER
, MMTT.PRIMARY_QUANTITY QUANTITY
, MMTT.PLANNING_ORGANIZATION_ID
, MMTT.PLANNING_TP_TYPE
, MMTT.OWNING_ORGANIZATION_ID
, MMTT.OWNING_TP_TYPE
, TO_DATE(NULL) EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, MPP.PLANNING_GROUP
, TO_CHAR(NULL) END_ITEM_UNIT_NUMBER
FROM PJM_PROJECT_PARAMETERS MPP
, MTL_SYSTEM_ITEMS_B MASIS
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_ITEM_LOCATIONS MIL
WHERE MPP.ORGANIZATION_ID(+)= MMTT.ORGANIZATION_ID
AND MPP.PROJECT_ID(+)= MMTT.PROJECT_ID
AND MMTT.POSTING_FLAG= 'Y'
AND MASIS.ORGANIZATION_ID= MMTT.ORGANIZATION_ID
AND MASIS.INVENTORY_ITEM_ID= MMTT.INVENTORY_ITEM_ID
AND MASIS.EFFECTIVITY_CONTROL= 1
AND MIL.ORGANIZATION_ID(+)= MMTT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= MMTT.LOCATOR_ID
AND ( ((MASIS.MRP_PLANNING_CODE IS NOT NULL OR (MASIS.PICK_COMPONENTS_FLAG = 'Y' OR MASIS.BOM_ITEM_TYPE=3))
AND ( MASIS.INVENTORY_ITEM_FLAG = 'Y' OR MASIS.ENG_ITEM_FLAG = 'Y')
AND MASIS.PLANNING_MAKE_BUY_CODE IN (1
, 2)
AND MASIS.PRIMARY_UOM_CODE IS NOT NULL) OR MASIS.ATP_FLAG <> 'N' OR MASIS.ATP_COMPONENTS_FLAG <> 'N' OR MASIS.DRP_PLANNED_FLAG = 1 OR MASIS.EAM_ITEM_TYPE IS NOT NULL) /* INCLUDED CHKS FOR MATERIAL STATUS - SUBINVENTORY*/
AND EXISTS ( SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
AND MSI.AVAILABILITY_TYPE = 1 ) /* INCLUDED CHKS FOR MATERIAL STATUS - LOCATOR*/
AND (( MMTT.LOCATOR_ID IS NULL) OR ( MMTT.LOCATOR_ID IS NOT NULL
AND EXISTS ( SELECT 'X'
FROM MTL_ITEM_LOCATIONS MIL2
WHERE MIL2.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MIL2.INVENTORY_LOCATION_ID = MMTT.LOCATOR_ID
AND MIL2.AVAILABILITY_TYPE = 1 ) ) ) /* ================== 4============================= */ UNION ALL SELECT MMTT.ORGANIZATION_ID
, MMTT.INVENTORY_ITEM_ID
, MMTT.SUBINVENTORY_CODE SUB_INV_CODE
, TO_CHAR(NULL) LOT_NUMBER
, MMTT.PRIMARY_QUANTITY QUANTITY
, MMTT.PLANNING_ORGANIZATION_ID
, MMTT.PLANNING_TP_TYPE
, MMTT.OWNING_ORGANIZATION_ID
, MMTT.OWNING_TP_TYPE
, TO_DATE(NULL) EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.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
FROM PJM_PROJECT_PARAMETERS MPP
, MTL_SYSTEM_ITEMS_B MASIS
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_ITEM_LOCATIONS MIL
WHERE MPP.ORGANIZATION_ID(+)= MMTT.ORGANIZATION_ID
AND MPP.PROJECT_ID(+)= MMTT.PROJECT_ID
AND MMTT.POSTING_FLAG= 'Y'
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
AND MIL.ORGANIZATION_ID(+)= MMTT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= MMTT.LOCATOR_ID
AND ( ((MASIS.MRP_PLANNING_CODE IS NOT NULL OR (MASIS.PICK_COMPONENTS_FLAG = 'Y' OR MASIS.BOM_ITEM_TYPE=3))
AND ( MASIS.INVENTORY_ITEM_FLAG = 'Y' OR MASIS.ENG_ITEM_FLAG = 'Y')
AND MASIS.PLANNING_MAKE_BUY_CODE IN (1
, 2)
AND MASIS.PRIMARY_UOM_CODE IS NOT NULL) OR MASIS.ATP_FLAG <> 'N' OR MASIS.ATP_COMPONENTS_FLAG <> 'N' OR MASIS.DRP_PLANNED_FLAG = 1 OR MASIS.EAM_ITEM_TYPE IS NOT NULL) /* INCLUDED CHKS FOR MATERIAL STATUS - SUBINVENTORY*/
AND EXISTS ( SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
AND MSI.AVAILABILITY_TYPE = 1 ) /* INCLUDED CHKS FOR MATERIAL STATUS - LOCATOR*/
AND (( MMTT.LOCATOR_ID IS NULL) OR ( MMTT.LOCATOR_ID IS NOT NULL
AND EXISTS ( SELECT 'X'
FROM MTL_ITEM_LOCATIONS MIL2
WHERE MIL2.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MIL2.INVENTORY_LOCATION_ID = MMTT.LOCATOR_ID
AND MIL2.AVAILABILITY_TYPE = 1 ) ) ) /* ================== 5============================= */ UNION ALL SELECT MMTT.ORGANIZATION_ID
, MMTT.INVENTORY_ITEM_ID
, MMTT.SUBINVENTORY_CODE SUB_INV_CODE
, TO_CHAR(NULL) LOT_NUMBER
, MMTT.PRIMARY_QUANTITY QUANTITY
, MMTT.PLANNING_ORGANIZATION_ID
, MMTT.PLANNING_TP_TYPE
, MMTT.OWNING_ORGANIZATION_ID
, MMTT.OWNING_TP_TYPE
, TO_DATE(NULL) EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, MPP.PLANNING_GROUP
, MSN.END_ITEM_UNIT_NUMBER
FROM MTL_MATERIAL_STATUSES MS
, PJM_PROJECT_PARAMETERS MPP
, MTL_SERIAL_NUMBERS_TEMP MSNT
, MTL_SERIAL_NUMBERS MSN
, MTL_SYSTEM_ITEMS_B MASIS
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_ITEM_LOCATIONS MIL
WHERE 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 MSN.STATUS_ID = MS.STATUS_ID
AND MS.AVAILABILITY_TYPE = 1
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
AND MIL.ORGANIZATION_ID(+)= MMTT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID(+)= MMTT.LOCATOR_ID
AND ( ((MASIS.MRP_PLANNING_CODE IS NOT NULL OR (MASIS.PICK_COMPONENTS_FLAG = 'Y' OR MASIS.BOM_ITEM_TYPE=3))
AND ( MASIS.INVENTORY_ITEM_FLAG = 'Y' OR MASIS.ENG_ITEM_FLAG = 'Y')
AND MASIS.PLANNING_MAKE_BUY_CODE IN (1
, 2)
AND MASIS.PRIMARY_UOM_CODE IS NOT NULL) OR MASIS.ATP_FLAG <> 'N' OR MASIS.ATP_COMPONENTS_FLAG <> 'N' OR MASIS.DRP_PLANNED_FLAG = 1 OR MASIS.EAM_ITEM_TYPE IS NOT NULL) /* INCLUDED CHKS FOR MATERIAL STATUS - SUBINVENTORY*/
AND EXISTS ( SELECT 'X'
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
AND MSI.AVAILABILITY_TYPE = 1 ) /* INCLUDED CHKS FOR MATERIAL STATUS - LOCATOR*/
AND (( MMTT.LOCATOR_ID IS NULL) OR ( MMTT.LOCATOR_ID IS NOT NULL
AND EXISTS ( SELECT 'X'
FROM MTL_ITEM_LOCATIONS MIL2
WHERE MIL2.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MIL2.INVENTORY_LOCATION_ID = MMTT.LOCATOR_ID
AND MIL2.AVAILABILITY_TYPE = 1 ) ) )

Columns