MSC_AP_MMTT_ONHAND_SUP_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name
TRANSACTION_TEMP_ID ORGANIZATION_ID ORGANIZATION_CODE INVENTORY_ITEM_ID ITEM_NUMBER SUBINVENTORY_CODE LOT_NUMBER QUANTITY EXPIRATION_DATE

Query

SQL_Statement

SELECT mmtt.TRANSACTION_TEMP_ID, mmtt.Organization_ID, esi.organization_code, mmtt.Inventory_Item_ID, esi.item_number, mmtt.Subinventory_Code Subinventory_Code, TO_CHAR(NULL) Lot_Number, mmtt.Primary_Quantity Quantity, TO_DATE(NULL) Expiration_Date FROM (SELECT TRANSACTION_TEMP_ID, Organization_ID, Inventory_Item_ID, Subinventory_Code Subinventory_Code, (DECODE(transaction_action_id, 1,-1, 2,-1, 28,-1, 3,-1, Sign(primary_quantity)) * Abs(primary_quantity)) Primary_Quantity, Locator_ID FROM INV_MATERIAL_TXNS_TEMP WHERE Posting_Flag= 'Y' AND nvl(transaction_status, 0) <> 2 AND transaction_action_id NOT IN (5,6,24,30) UNION ALL SELECT TRANSACTION_TEMP_ID, Decode(transaction_action_id, 3, transfer_organization, Organization_ID) Organization_ID, Inventory_Item_ID, transfer_subinventory Subinventory_Code, abs(Primary_Quantity), transfer_to_location Locator_ID FROM INV_MATERIAL_TXNS_TEMP WHERE Posting_Flag= 'Y' AND nvl(transaction_status, 0) <> 2 AND transaction_action_id IN (2,28,3) ) mmtt, MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi, INV_ITEM_LOCATIONS mil WHERE esi.Organization_ID= mmtt.Organization_ID AND esi.Inventory_Item_ID= mmtt.Inventory_Item_ID AND esi.Effectivity_Control= 1 AND mil.Organization_ID(+)= mmtt.Organization_ID AND mil.Inventory_Location_ID(+)= mmtt.Locator_ID AND EXISTS ( SELECT 'x' FROM INV_SECONDARY_INVENTORIES msi WHERE msi.organization_id = mmtt.Organization_ID AND msi.secondary_inventory_name = mmtt.Subinventory_Code AND msi.availability_type = 1 ) AND (( mmtt.Locator_ID is null) OR ( mmtt.Locator_ID is not null AND EXISTS ( SELECT 'x' FROM INV_ITEM_LOCATIONS mil2 WHERE mil2.organization_id = mmtt.Organization_ID AND mil2.Inventory_Location_ID = mmtt.Locator_ID AND nvl(mil2.availability_type,1) = 1 ) ) )