VIEW: APPS.INV_BIS_SUPPLY_CHAIN_INVENTORY

Source

View Text - Preformatted

SELECT MOH.inventory_item_id INVENTORY_ITEM_ID , MSI.concatenated_segments INVENTORY_ITEM_NAME , MOH.organization_id ORGANIZATION_ID , HAO.name ORGANIZATION_NAME , MOH.subinventory_code SUBINVENTORY_NAME , MOH.transaction_quantity ONHAND_QUANTITY , 0 INTRANSIT_QUANTITY , MOH.item_cost ITEM_COST from MTL_SYSTEM_ITEMS_B_KFV MSI , MTL_ONHAND_QTY_COST_V MOH , HR_ALL_ORGANIZATION_UNITS HAO WHERE MSI.organization_id = MOH.organization_id AND MSI.inventory_item_id = MOH.inventory_item_id AND MSI.organization_id = HAO.organization_id UNION ALL SELECT CIVV.inventory_item_id INVENTORY_ITEM_ID , MSIBK.concatenated_segments INVENTORY_ITEM_NAME , CIVV.intransit_owning_org_id ORGANIZATION_ID , HAO.name ORGANIZATION_NAME , DECODE(CIVV.intransit_owning_org_id, CIVV.from_organization_id, CIVV.from_subinventory, CIVV.to_subinventory) SUBINVENTORY_NAME , 0 ONHAND_QUANTITY , CIVV.quantity INTRANSIT_QUANTITY , CCICV.item_cost ITEM_COST from MTL_SYSTEM_ITEMS_B_KFV MSIBK , CST_INTRANSIT_VALUE_VIEW CIVV , HR_ALL_ORGANIZATION_UNITS HAO , (SELECT mp.default_cost_group_id default_cost_group_id ,decode(cql.inventory_item_id, null, nvl(mp.default_cost_group_id,1),cql.COST_GROUP_ID) COST_GROUP_ID ,NVL( DECODE(CQL.INVENTORY_ITEM_ID, NULL, CIC.ITEM_COST, CQL.ITEM_COST),0) ITEM_COST ,CIC.INVENTORY_ITEM_ID ,MP.ORGANIZATION_ID FROM CST_QUANTITY_LAYERS CQL ,MTL_PARAMETERS MP ,CST_ITEM_COSTS CIC WHERE CQL.INVENTORY_ITEM_ID(+) = CIC.INVENTORY_ITEM_ID AND CQL.ORGANIZATION_ID(+) = CIC.ORGANIZATION_ID AND MP.COST_ORGANIZATION_ID = CIC.ORGANIZATION_ID AND MP.PRIMARY_COST_METHOD = CIC.COST_TYPE_ID) CCICV WHERE CIVV.intransit_owning_org_id = HAO.organization_id AND CIVV.inventory_item_id = MSIBK.inventory_item_id AND CIVV.intransit_owning_org_id = MSIBK.organization_id AND CIVV.intransit_owning_org_id = CCICV.organization_id AND CIVV.inventory_item_id = CCICV.inventory_item_id AND nvl(CIVV.cost_group_id, CCICV.default_cost_group_id) = CCICV.cost_group_id

View Text - HTML Formatted

SELECT MOH.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS INVENTORY_ITEM_NAME
, MOH.ORGANIZATION_ID ORGANIZATION_ID
, HAO.NAME ORGANIZATION_NAME
, MOH.SUBINVENTORY_CODE SUBINVENTORY_NAME
, MOH.TRANSACTION_QUANTITY ONHAND_QUANTITY
, 0 INTRANSIT_QUANTITY
, MOH.ITEM_COST ITEM_COST
FROM MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_ONHAND_QTY_COST_V MOH
, HR_ALL_ORGANIZATION_UNITS HAO
WHERE MSI.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MOH.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = HAO.ORGANIZATION_ID UNION ALL SELECT CIVV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS INVENTORY_ITEM_NAME
, CIVV.INTRANSIT_OWNING_ORG_ID ORGANIZATION_ID
, HAO.NAME ORGANIZATION_NAME
, DECODE(CIVV.INTRANSIT_OWNING_ORG_ID
, CIVV.FROM_ORGANIZATION_ID
, CIVV.FROM_SUBINVENTORY
, CIVV.TO_SUBINVENTORY) SUBINVENTORY_NAME
, 0 ONHAND_QUANTITY
, CIVV.QUANTITY INTRANSIT_QUANTITY
, CCICV.ITEM_COST ITEM_COST
FROM MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CST_INTRANSIT_VALUE_VIEW CIVV
, HR_ALL_ORGANIZATION_UNITS HAO
, (SELECT MP.DEFAULT_COST_GROUP_ID DEFAULT_COST_GROUP_ID
, DECODE(CQL.INVENTORY_ITEM_ID
, NULL
, NVL(MP.DEFAULT_COST_GROUP_ID
, 1)
, CQL.COST_GROUP_ID) COST_GROUP_ID
, NVL( DECODE(CQL.INVENTORY_ITEM_ID
, NULL
, CIC.ITEM_COST
, CQL.ITEM_COST)
, 0) ITEM_COST
, CIC.INVENTORY_ITEM_ID
, MP.ORGANIZATION_ID
FROM CST_QUANTITY_LAYERS CQL
, MTL_PARAMETERS MP
, CST_ITEM_COSTS CIC
WHERE CQL.INVENTORY_ITEM_ID(+) = CIC.INVENTORY_ITEM_ID
AND CQL.ORGANIZATION_ID(+) = CIC.ORGANIZATION_ID
AND MP.COST_ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND MP.PRIMARY_COST_METHOD = CIC.COST_TYPE_ID) CCICV
WHERE CIVV.INTRANSIT_OWNING_ORG_ID = HAO.ORGANIZATION_ID
AND CIVV.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND CIVV.INTRANSIT_OWNING_ORG_ID = MSIBK.ORGANIZATION_ID
AND CIVV.INTRANSIT_OWNING_ORG_ID = CCICV.ORGANIZATION_ID
AND CIVV.INVENTORY_ITEM_ID = CCICV.INVENTORY_ITEM_ID
AND NVL(CIVV.COST_GROUP_ID
, CCICV.DEFAULT_COST_GROUP_ID) = CCICV.COST_GROUP_ID