VIEW: APPS.GMP_NETTABLE_ONHANDS_V

Source

View Text - Preformatted

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 ) ) )

View Text - HTML Formatted

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 ) ) )