1. Tables and Views for SCM
  2. MSC_AP_MOQ_ONHAND_SUP_V

MSC_AP_MOQ_ONHAND_SUP_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

ONHAND_QUANTITIES_ID

ORGANIZATION_ID

ORGANIZATION_CODE

INVENTORY_ITEM_ID

ITEM_NUMBER

SUBINVENTORY_CODE

LOT_NUMBER

QUANTITY

EXPIRATION_DATE

Query

SQL_Statement

SELECT

moq.ONHAND_QUANTITIES_ID,

moq.Organization_ID,

esi.organization_code,

moq.Inventory_Item_ID,

esi.item_number,

moq.Subinventory_Code,

moq.Lot_Number,

moq.Primary_Transaction_Quantity Quantity,

mln.Expiration_Date

FROM INV_ONHAND_QUANTITIES_DETAIL moq,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi ,

INV_LOT_NUMBERS mln,

INV_SECONDARY_INVENTORIES msi

WHERE moq.Inventory_Item_ID= esi.Inventory_Item_ID

AND moq.Organization_ID= esi.Organization_ID

AND esi.Effectivity_Control= 1

AND 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

AND ((moq.Lot_Number is null) OR

( moq.Lot_Number is not null

AND EXISTS (

SELECT 'x' FROM INV_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 nvl(mln2.availability_type,1) = 1

)

)

)

AND (( moq.Locator_ID is null) OR

( moq.Locator_ID is not null

AND EXISTS (

SELECT 'x' FROM INV_ITEM_LOCATIONS mil2

WHERE mil2.organization_id = moq.Organization_ID

AND mil2.Inventory_Location_ID = moq.Locator_ID

AND nvl(mil2.availability_type,1) = 1

)

)

)

AND exists (

select 'x'

from inv_secondary_inventories isi

where moq.subinventory_code = isi.secondary_inventory_name

and moq.organization_id = isi.organization_id

and isi.availability_type = 1

)