1. Tables and Views for SCM
  2. MSC_AP_ONHAND_SUPPLIES_V

MSC_AP_ONHAND_SUPPLIES_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

ROW_ID

ORGANIZATION_ID

INVENTORY_ITEM_ID

ORGANIZATION_CODE

ITEM_NUMBER

ESI_ORG_ID

ESI_ITEM_ID

SUBINVENTORY_CODE

LOT_NUMBER

EXPIRATION_DATE

HOLD_DATE

MATURITY_DATE

QUANTITY

GLOBAL_ATTRIBUTE_NUMBER11

GLOBAL_ATTRIBUTE_NUMBER12

ISN_ROWID

ILN_ROWID

MOQ_ROWID

MSI_ROWID

Query

SQL_Statement

SELECT

sup.ROW_ID,

sup.Organization_ID,

sup.Inventory_Item_ID,

esi.organization_code,

esi.item_number,

esi.Organization_ID esi_org_id,

esi.Inventory_Item_ID esi_item_id,

sup.Subinventory_Code,

sup.Lot_Number,

mln.Expiration_Date,

mln.hold_date,

mln.maturity_date,

sup.Quantity,

sup.PROJECT_ID GLOBAL_ATTRIBUTE_NUMBER11,

sup.TASK_ID GLOBAL_ATTRIBUTE_NUMBER12,

sup.isn_rowid,

mln.rowid iln_rowid,

sup.moq_rowid,

msi.rowid msi_rowid

FROM (

SELECT

'IOQD_'||moq.ONHAND_QUANTITIES_ID ROW_ID,

moq.Organization_ID,

moq.Inventory_Item_ID,

moq.Subinventory_Code,

moq.Lot_Number,

moq.Locator_ID,

moq.Primary_Transaction_Quantity Quantity,

moq.PROJECT_ID,

moq.TASK_ID,

1 Effectivity_Control,

NULL isn_rowid,

moq.rowid moq_rowid

FROM INV_ONHAND_QUANTITIES_DETAIL moq

UNION ALL

SELECT

'IMTT_'||TRANSACTION_TEMP_ID ROW_ID,

Organization_ID,

Inventory_Item_ID,

Subinventory_Code,

TO_CHAR(NULL) Lot_Number,

Locator_ID,

(DECODE(transaction_action_id, 1,-1, 2,-1, 28,-1, 3,-1, 67,-1, Sign(primary_quantity)) * Abs(primary_quantity)) Quantity,

PROJECT_ID,

TASK_ID,

1 Effectivity_Control,

NULL isn_rowid,

NULL moq_rowid

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

'IMTT_'||TRANSACTION_TEMP_ID ROW_ID,

Decode(transaction_action_id, 3, transfer_organization, Organization_ID) Organization_ID,

Inventory_Item_ID,

transfer_subinventory Subinventory_Code,

TO_CHAR(NULL) Lot_Number,

transfer_to_location Locator_ID,

abs(Primary_Quantity) Quantity,

decode(transaction_action_id, 67, TRANSFER_PROJECT_ID, PROJECT_ID) PROJECT_ID,

decode(transaction_action_id, 67, TRANSFER_TASK_ID, TASK_ID) TASK_ID,

1 Effectivity_Control,

NULL isn_rowid,

NULL moq_rowid

FROM INV_MATERIAL_TXNS_TEMP

WHERE Posting_Flag = 'Y'

AND nvl(transaction_status, 0) <> 2

AND transaction_action_id IN (2,28,3,67)

UNION ALL

SELECT

'ITRL_'||mtrl.line_id ROW_ID,

mtrl.Organization_ID ,

mtrl.Inventory_Item_ID ,

TO_CHAR(NULL) Subinventory_Code ,

TO_CHAR(NULL) Lot_Number ,

NULL Locator_ID ,

mtrl.Primary_Quantity Quantity ,

mtrl.PJC_PROJECT_ID PROJECT_ID,

mtrl.PJC_TASK_ID TASK_ID,

NULL Effectivity_Control ,

NULL isn_rowid,

NULL moq_rowid

FROM

INV_TXN_REQUEST_LINES mtrl,

INV_TXN_REQUEST_HEADERS mtrh,

INV_TRANSACTION_TYPES mtt

WHERE mtrh.HEADER_ID = mtrl.HEADER_ID

AND mtrh.MOVE_ORDER_TYPE = 6

AND mtrl.TRANSACTION_TYPE_ID = mtt.TRANSACTION_TYPE_ID

AND mtt.TRANSACTION_ACTION_ID = 31

UNION ALL

SELECT

'ISN_'||isn.serial_number ROW_ID,

isn.current_organization_id organization_id,

isn.inventory_item_id,

isn.current_subinventory_code subinventory_code,

isn.lot_number,

isn.current_locator_id locator_id,

1 quantity,

isn.PROJECT_ID,

isn.TASK_ID,

2 Effectivity_Control,

isn.rowid isn_rowid,

NULL moq_rowid

FROM INV_SERIAL_NUMBERS isn,

INV_LOT_NUMBERS iln

WHERE isn.current_status IN (3, 5)

AND iln.organization_id(+) = isn.current_organization_id

AND iln.inventory_item_id(+) = isn.inventory_item_id

AND iln.lot_number(+) = isn.lot_number

AND NVL(TRUNC(iln.hold_date), SYSDATE-1) <= TRUNC(SYSDATE)

AND (( isn.availability_type is null) OR

( isn.status_ID is not null

AND EXISTS (

SELECT 1

FROM INV_MATERIAL_STATUSES_B sts

WHERE sts.status_id = isn.status_ID

AND sts.availability_type = 1

)

)

)

UNION ALL

SELECT

'ISNT_'||isnt.transaction_temp_id ROW_ID,

imtt.organization_id,

imtt.inventory_item_id,

imtt.subinventory_code,

NULL lot_number,

imtt.locator_id,

imtt.primary_quantity quantity,

isn.PROJECT_ID,

isn.TASK_ID,

2 Effectivity_Control,

isn.rowid isn_rowid,

NULL moq_rowid

FROM

inv_serial_numbers_temp isnt,

inv_serial_numbers isn,

inv_material_txns_temp imtt

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 NVL(imtt.transaction_status, 0) <> 2

) sup,

MSC_PLANNED_EGP_SYSTEM_ITEMS esi,

INV_LOT_NUMBERS mln,

inv_item_locations mil,

inv_secondary_inventories msi

WHERE esi.Organization_ID = sup.Organization_ID

AND esi.Inventory_Item_ID = sup.Inventory_Item_ID

AND (sup.Effectivity_Control IS NULL

OR (sup.Effectivity_Control IS NOT NULL AND sup.Effectivity_Control = esi.Effectivity_Control)

)

AND mln.Organization_ID(+) = sup.Organization_ID

AND mln.Inventory_Item_ID(+)= sup.Inventory_Item_ID

AND mln.Lot_Number(+) = sup.Lot_Number

AND (

(sup.Lot_Number IS NULL)

OR

(sup.Lot_Number IS NOT NULL AND NVL(mln.availability_type,1) = 1)

)

AND mil.organization_id(+) = sup.Organization_ID

AND mil.Inventory_Location_ID(+) = sup.Locator_ID

AND (

(sup.Locator_ID IS NULL)

OR

(sup.Locator_ID IS NOT NULL AND NVL(mil.availability_type,1) = 1)

)

AND msi.organization_id = sup.Organization_ID

AND msi.secondary_inventory_name = sup.Subinventory_Code

AND TRUNC(NVL(msi.disable_date,SYSDATE)) >= TRUNC(SYSDATE)