INV_ONHAND_SUP_SUMMARY_V

Details

• Schema: FUSION

• Object owner: INV

• Object type: VIEW

Columns

Name

INVENTORY_ITEM_ID MATERIAL_LOCATION ORGANIZATION_ID SUBINVENTORY_CODE LOCATOR_ID REVISION LOT_NUMBER SERIAL_NUMBER PROJECT_ID TASK_ID COUNTRY_OF_ORIGIN_CODE SHIPMENT_LINE_ID SHIPMENT_HEADER_ID PO_HEADER_ID PO_LINE_ID REQ_HEADER_ID REQ_LINE_ID FROM_ORGANIZATION_ID EXPECTED_DELIVERY_DATE CREATE_TRANSACTION_ID OWNING_ENTITY_ID NODE_TYPE_VIEWBYITEM NODE_ID_VIEWBYITEM NODE_TYPE_VIEWBYSUBINV NODE_ID_VIEWBYSUBINV NODE_TYPE_VIEWBYLOCATOR NODE_ID_VIEWBYLOCATOR

Query

SQL_Statement

WITH INL_ONHAND_UI_INPUT as ( SELECT item_id inventory_item_id, 'Inbound' material_location, to_organization_id organization_id, null subinventory_code, to_number(NULL) locator_id, item_revision revision, to_char(NULL) lot_number, to_char(NULL) serial_number, project_id project_id, task_id task_id, country_of_origin_code country_of_origin_code, shipment_line_id, shipment_header_id, po_header_id, po_line_id, req_header_id, req_line_id, from_organization_id, expected_delivery_date, to_number(NULL) create_transaction_id, to_number(NULL) owning_entity_id FROM INV_SUPPLY_V sup WHERE supply_type_code <> 'RECEIVING' AND item_id IS NOT NULL union all SELECT rs.item_id inventory_item_id, 'Receiving' material_location, rs.to_organization_id organization_id, rs.to_subinventory subinventory_code, rs.to_locator_id locator_id, rs.item_revision revision, null as lot_number, to_char(NULL) serial_number, project_id project_id, task_id task_id, rs.country_of_origin_code, rs.shipment_line_id, rs.shipment_header_id, to_number(NULL) po_header_id, to_number(NULL) po_line_id, to_number(NULL) req_header_id, to_number(NULL) req_line_id, to_number(NULL) from_organization_id, to_date(null) expected_delivery_date, to_number(NULL) create_transaction_id, to_number(NULL) owning_entity_id FROM rcv_supply rs where item_id is not null ) select res.inventory_item_id, material_location, res.organization_id, subinventory_code, locator_id, revision, lot_number, serial_number, project_id, task_id, country_of_origin_code, shipment_line_id, shipment_header_id, po_header_id, po_line_id, req_header_id, req_line_id, from_organization_id, expected_delivery_date, create_transaction_id, owning_entity_id, ( 'Item' || ( Case when REVISION_QTY_CONTROL_CODE = 2 and revision is not null then ' else '' end ) || decode(res.organization_id,null,'',' || ( Case when PROJECT_REFERENCE_ENABLED = 1 then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 and project_id is not null and task_id is not null then ' else '' end ) || decode(subinventory_code,null,'',' || decode(locator_id,null,'',' || ( Case when lot_control_code = 2 and lot_number is not null then ' else '' end ) || ' ) as node_type_viewbyitem, ( res.inventory_item_id || ( Case when REVISION_QTY_CONTROL_CODE = 2 and revision is not null then ' else '' end ) || decode(res.organization_id,null,'',' || ( Case when PROJECT_REFERENCE_ENABLED = 1 then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 and project_id is not null and task_id is not null then ' else '' end ) || decode(subinventory_code,null,'',' || decode(locator_id,null,'',' || ( Case when lot_control_code = 2 and lot_number is not null then ' else '' end ) || ' ) as node_id_viewbyitem, ( 'Organization' || decode(subinventory_code,null,'',' || ' || ( Case when REVISION_QTY_CONTROL_CODE = 2 and revision is not null then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 and project_id is not null and task_id is not null then ' else '' end ) || decode(locator_id,null,'',' || ( Case when lot_control_code = 2 and lot_number is not null then ' else '' end ) || ' ) as node_type_viewbysubinv, ( res.organization_id || decode(res.subinventory_code,null,'',' || ' || ( Case when REVISION_QTY_CONTROL_CODE = 2 and revision is not null then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 and project_id is not null and task_id is not null then ' else '' end ) || decode(locator_id,null,'',' || ( Case when lot_control_code = 2 and lot_number is not null then ' else '' end ) || ' ) as node_id_viewbysubinv, ( 'Organization' || decode(subinventory_code,null,'',' || decode(locator_id,null,'',' || ' || ( Case when REVISION_QTY_CONTROL_CODE = 2 and revision is not null then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 and project_id is not null and task_id is not null then ' else '' end ) || ( Case when lot_control_code = 2 and lot_number is not null then ' else '' end ) || ' ) as node_type_viewbylocator, ( res.organization_id || decode(subinventory_code,null,'',' || decode(locator_id,null,'',' || ' || ( Case when REVISION_QTY_CONTROL_CODE = 2 and revision is not null then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 then ' else '' end ) || ( Case when PROJECT_REFERENCE_ENABLED = 1 and project_id is not null and task_id is not null then ' else '' end ) || ( Case when lot_control_code = 2 and lot_number is not null then ' else '' end ) || ' ) as node_id_viewbylocator from INL_ONHAND_UI_INPUT RES, INV_ORG_PARAMETERS orgs, EGP_SYSTEM_ITEMS_VL items where orgs.organization_id = res.organization_id and items.organization_id = res.organization_id and items.inventory_item_id = res.inventory_item_id