1. Tables and Views for SCM
  2. MSC_AP_SERIAL_ONHAND_SUP_V

MSC_AP_SERIAL_ONHAND_SUP_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

SERIAL_NUMBER

ORGANIZATION_ID

ORGANIZATION_CODE

INVENTORY_ITEM_ID

ITEM_NUMBER

SUBINVENTORY_CODE

LOT_NUMBER

QUANTITY

EXPIRATION_DATE

Query

SQL_Statement

SELECT

null serial_number,

x2.organization_ID,

x2.organization_code,

x2.inventory_Item_ID,

x2.item_number,

x2.subinventory_Code,

x2.lot_number,

x2.lot_quantity quantity,

iln.expiration_date

FROM (select isn.serial_number,

isn.current_organization_id organization_id,

esi.organization_code,

isn.inventory_item_id,

esi.item_number,

isn.current_subinventory_code subinventory_code,

isn.current_locator_id locator_id,

isn.lot_number,

1 lot_quantity

from MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi,

INV_SERIAL_NUMBERS isn

where isn.current_status in (3, 5)

and esi.organization_id = isn.current_organization_id

and esi.inventory_item_id = isn.inventory_item_id

and nvl(isn.availability_type, 1) = 1

and esi.effectivity_control = 2

) x2,

INV_LOT_NUMBERS iln,

INV_ITEM_LOCATIONS iil

WHERE iln.organization_id(+) = x2.organization_id

and exists (

select 1

from inv_secondary_inventories isi

where isi.organization_id = x2.organization_id

and isi.secondary_inventory_name = x2.subinventory_code

and isi.availability_type = 1

)

and iln.inventory_item_id(+) = x2.inventory_item_id

and iln.lot_number(+) = x2.lot_number

and nvl(trunc(iln.hold_date), sysdate-1) <= trunc(sysdate)

and ((x2.lot_number is null) OR

(x2.lot_number is not null and exists (

select 1

from inv_lot_numbers iln2

where iln2.organization_id = x2.organization_id

and iln2.inventory_item_id = x2.inventory_item_id

and iln2.lot_number = x2.lot_number

and nvl(iln2.availability_type, 1) = 1

)

)

)

and iil.organization_id(+) = x2.organization_id

and iil.inventory_location_id(+) = x2.locator_id

and ((x2.locator_id is null) or

(x2.locator_id is not null and exists (

select 1

from inv_item_locations iil2

where iil2.organization_id = x2.organization_id

and iil2.inventory_location_id = x2.locator_id

and nvl(iil2.availability_type, 1) = 1

)

)

)

UNION ALL

select imtt.transaction_temp_id,

imtt.organization_id,

esi.organization_code,

imtt.inventory_item_id,

esi.item_number,

imtt.subinventory_code,

null lot_number,

imtt.primary_quantity quantity,

to_date(null) expiration_date

from inv_serial_numbers_temp isnt,

inv_serial_numbers isn,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi,

inv_material_txns_temp imtt,

inv_item_locations iil

where isnt.transaction_temp_id = imtt.transaction_temp_id

and isnt.fm_serial_number = isn.serial_number

and isn.inventory_item_id = imtt.inventory_item_id

and nvl(isn.availability_type, 1) = 1

and imtt.posting_flag = 'Y'

and not (imtt.transaction_action_id = 1

and imtt.transaction_source_type_id in (1))

and esi.organization_id = imtt.organization_id

and esi.inventory_item_id = imtt.inventory_item_id

and esi.effectivity_control = 2

and iil.organization_id(+) = imtt.organization_id

and iil.inventory_location_id(+) = imtt.locator_id

and nvl(imtt.transaction_status, 0) <> 2

and exists (

select 1

from inv_secondary_inventories isi

where isi.organization_id = imtt.organization_id

and isi.secondary_inventory_name = imtt.subinventory_code

and isi.availability_type = 1

)

and ((imtt.locator_id is null) or

(imtt.locator_id is not null

and exists (

select 1

from inv_item_locations iil2

where iil2.organization_id = isn.current_organization_id

and iil2.inventory_location_id = isn.current_locator_id

and nvl(iil2.availability_type, 1) = 1

)

)

)