VIEW: APPS.POA_ENI_OLTP_ITEM_STAR_V

Source

View Text - Preformatted

SELECT enii.id, enii.value, enii.organization_code, enii.inventory_item_id, enii.organization_id, enii.po_category_id, enii.po_category_set_id, enii.po_concat_seg, enii.inv_category_id, enii.inv_category_set_id, enii.inv_concat_seg, enii.vbh_category_id, enii.vbh_category_set_id, enii.vbh_concat_seg, enii.master_id, enii.creation_date, enii.last_update_date, enii.item_catalog_group_id, enii.primary_uom_code, enii.unit_weight, enii.unit_volume, enii.weight_uom_code, enii.volume_uom_code, poai.po_item_id, poai.description po_item_description from eni_oltp_item_star enii, poa_items poai WHERE enii.inventory_item_id = poai.item_id(+) and enii.organization_id = poai.organization_id(+) union all select to_char(null) id, value value, null organization_code, to_number(null) inventory_item_id, to_number(null) organization_id, category_id po_category_id, null po_category_set_id, null po_concat_seg, null inv_category_id, null inv_category_set_id, null inv_concat_seg, null vbh_category_id, null vbh_category_set_id, null vbh_concat_seg, null master_id, null creation_date, null last_update_date, null item_catalog_group_id, null primary_uom_code, null unit_weight, null unit_volume, null weight_uom_code, null volume_uom_code, id po_item_id, description po_item_description from ( ( select po_item_id id, vendor_product_num||' ('||vendor_name||')' value, description, to_number(null) inventory_item_id, to_number(null) organization_id, category_id from poa_items_mv ) union all ( select pot.po_item_id id, pot.description || ' (' || cat.concatenated_segments ||')' value, pot.description description, to_number(null) inventory_item_id, to_number(null) organization_id, cat.category_id from poa_items pot, mtl_categories_kfv cat where pot.item_id is null and pot.vendor_product_Num is null and pot.category_id = cat.category_id ) )

View Text - HTML Formatted

SELECT ENII.ID
, ENII.VALUE
, ENII.ORGANIZATION_CODE
, ENII.INVENTORY_ITEM_ID
, ENII.ORGANIZATION_ID
, ENII.PO_CATEGORY_ID
, ENII.PO_CATEGORY_SET_ID
, ENII.PO_CONCAT_SEG
, ENII.INV_CATEGORY_ID
, ENII.INV_CATEGORY_SET_ID
, ENII.INV_CONCAT_SEG
, ENII.VBH_CATEGORY_ID
, ENII.VBH_CATEGORY_SET_ID
, ENII.VBH_CONCAT_SEG
, ENII.MASTER_ID
, ENII.CREATION_DATE
, ENII.LAST_UPDATE_DATE
, ENII.ITEM_CATALOG_GROUP_ID
, ENII.PRIMARY_UOM_CODE
, ENII.UNIT_WEIGHT
, ENII.UNIT_VOLUME
, ENII.WEIGHT_UOM_CODE
, ENII.VOLUME_UOM_CODE
, POAI.PO_ITEM_ID
, POAI.DESCRIPTION PO_ITEM_DESCRIPTION
FROM ENI_OLTP_ITEM_STAR ENII
, POA_ITEMS POAI
WHERE ENII.INVENTORY_ITEM_ID = POAI.ITEM_ID(+)
AND ENII.ORGANIZATION_ID = POAI.ORGANIZATION_ID(+) UNION ALL SELECT TO_CHAR(NULL) ID
, VALUE VALUE
, NULL ORGANIZATION_CODE
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, CATEGORY_ID PO_CATEGORY_ID
, NULL PO_CATEGORY_SET_ID
, NULL PO_CONCAT_SEG
, NULL INV_CATEGORY_ID
, NULL INV_CATEGORY_SET_ID
, NULL INV_CONCAT_SEG
, NULL VBH_CATEGORY_ID
, NULL VBH_CATEGORY_SET_ID
, NULL VBH_CONCAT_SEG
, NULL MASTER_ID
, NULL CREATION_DATE
, NULL LAST_UPDATE_DATE
, NULL ITEM_CATALOG_GROUP_ID
, NULL PRIMARY_UOM_CODE
, NULL UNIT_WEIGHT
, NULL UNIT_VOLUME
, NULL WEIGHT_UOM_CODE
, NULL VOLUME_UOM_CODE
, ID PO_ITEM_ID
, DESCRIPTION PO_ITEM_DESCRIPTION
FROM ( ( SELECT PO_ITEM_ID ID
, VENDOR_PRODUCT_NUM||' ('||VENDOR_NAME||')' VALUE
, DESCRIPTION
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, CATEGORY_ID
FROM POA_ITEMS_MV ) UNION ALL ( SELECT POT.PO_ITEM_ID ID
, POT.DESCRIPTION || ' (' || CAT.CONCATENATED_SEGMENTS ||')' VALUE
, POT.DESCRIPTION DESCRIPTION
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, CAT.CATEGORY_ID
FROM POA_ITEMS POT
, MTL_CATEGORIES_KFV CAT
WHERE POT.ITEM_ID IS NULL
AND POT.VENDOR_PRODUCT_NUM IS NULL
AND POT.CATEGORY_ID = CAT.CATEGORY_ID ) )