1. Tables and Views for Procurement
  2. PO_COMMODITY_V

PO_COMMODITY_V

Details

  • Schema: FUSION

  • Object owner: PO

  • Object type: VIEW

Columns

Name

CHILD_CATEGORY_ID

PARENT_CATEGORY_ID

PURCHASING_CAT_FLAG

CATEGORY_NAME

CATEGORY_NAME2

CATEGORY_DESCRIPTION

DESCRIPTION

CATEGORY_ID

CATEGORY_ID2

DISPLAY_CATEGORY_NAME

DISPLAY_CATEGORY_DESCRIPTION

IMAGE_URL

SUPPLIER_ENABLED_FLAG

CATEGORY_TYPE

Query

SQL_Statement

(SELECT NVL(BrowseCategoryTreeEO.CHILD_CATEGORY_ID,2) AS CHILD_CATEGORY_ID,

BrowseCategoryTreeEO.PARENT_CATEGORY_ID,

NVL(BrowseCategoryTreeEO.PURCHASING_CAT_FLAG,'N') AS PURCHASING_CAT_FLAG,

BrowseCategoryEO.CATEGORY_NAME,

NULL AS CATEGORY_NAME2,

BrowseCategoryEO.CATEGORY_DESCRIPTION,

NULL DESCRIPTION,

BrowseCategoryEO.CATEGORY_ID,

NULL AS CATEGORY_ID2,

DECODE(BrowseCategoryEO.CATEGORY_NAME,'POR_CATEGORY_HIERARCHY_ROOT',

(SELECT LookupPEO.MEANING

FROM FND_LOOKUPS LookupPEO

WHERE (BrowseCategoryEO.CATEGORY_NAME = LookupPEO.LOOKUP_CODE

AND LookupPEO.LOOKUP_TYPE = 'POR_CATEGORY_HIERARCHY_ROOT')

),BrowseCategoryEO.CATEGORY_NAME) AS DISPLAY_CATEGORY_NAME,

BrowseCategoryEO.CATEGORY_DESCRIPTION AS DISPLAY_CATEGORY_DESCRIPTION,

BrowseCategoryEO.IMAGE_URL,

'Y' AS SUPPLIER_ENABLED_FLAG,

BrowseCategoryEO.TYPE AS CATEGORY_TYPE

FROM POR_BROWSE_TREES BrowseCategoryTreeEO,

POR_BROWSE_CATEGORIES_VL BrowseCategoryEO

WHERE BrowseCategoryEO.TYPE = 'SOURCING'

AND BrowseCategoryTreeEO.PARENT_CATEGORY_ID(+) NOT IN (1,0)

AND (BrowseCategoryTreeEO.CHILD_CATEGORY_ID(+) = BrowseCategoryEO.CATEGORY_ID)

)

UNION

(SELECT NVL(BrowseCategoryTreeEO.CHILD_CATEGORY_ID,CategoryPEO.CATEGORY_ID) CHILD_CATEGORY_ID,

NVL(BrowseCategoryTreeEO.PARENT_CATEGORY_ID,2) PARENT_CATEGORY_ID,

DECODE(CategoryPEO.CATEGORY_ID,NULL,BrowseCategoryTreeEO.PURCHASING_CAT_FLAG, 'Y') PURCHASING_CAT_FLAG,

BrowseCategoryEO.CATEGORY_NAME,

CategoryPEO.CATEGORY_NAME AS CATEGORY_NAME2,

BrowseCategoryEO.CATEGORY_DESCRIPTION,

CategoryPEO.DESCRIPTION,

BrowseCategoryEO.CATEGORY_ID,

CategoryPEO.CATEGORY_ID AS CATEGORY_ID2,

DECODE(DECODE(CategoryPEO.CATEGORY_ID,NULL,BrowseCategoryTreeEO.PURCHASING_CAT_FLAG, 'Y'),'Y', CategoryPEO.CATEGORY_NAME, BrowseCategoryEO.CATEGORY_NAME) AS DISPLAY_CATEGORY_NAME,

DECODE(DECODE(CategoryPEO.CATEGORY_ID,NULL,BrowseCategoryTreeEO.PURCHASING_CAT_FLAG, 'Y'),'Y', CategoryPEO.DESCRIPTION, BrowseCategoryEO.CATEGORY_DESCRIPTION ) AS DISPLAY_CATEGORY_DESCRIPTION,

BrowseCategoryEO.IMAGE_URL,

DECODE(CategoryPEO.CATEGORY_ID,NULL,'Y',CategoryPEO.SUPPLIER_ENABLED_FLAG) AS SUPPLIER_ENABLED_FLAG,

BrowseCategoryEO.TYPE AS CATEGORY_TYPE

FROM POR_BROWSE_TREES BrowseCategoryTreeEO,

POR_BROWSE_CATEGORIES_VL BrowseCategoryEO,

EGP_CATEGORIES_VL CategoryPEO,

EGP_CATEGORY_SET_VALID_CATS ValidCategoryPEO,

EGP_DEFAULT_CATEGORY_SETS DefaultCatalogPEO

WHERE BrowseCategoryEO.TYPE(+) = 'SOURCING'

AND NVL(BrowseCategoryTreeEO.PARENT_CATEGORY_ID,2) NOT IN (SELECT CATEGORY_ID

FROM POR_BROWSE_CATEGORIES_B ParentBrowseCategories

WHERE ParentBrowseCategories.TYPE IN ('SHOPPING','SUPPLIER'))

AND (BrowseCategoryTreeEO.CHILD_CATEGORY_ID = BrowseCategoryEO.CATEGORY_ID(+))

AND (BrowseCategoryTreeEO.CHILD_CATEGORY_ID = CategoryPEO.CATEGORY_ID)

AND CategoryPEO.CATEGORY_ID = ValidCategoryPEO.CATEGORY_ID(+)

AND DefaultCatalogPEO.FUNCTIONAL_AREA_ID = 2

AND ValidCategoryPEO.CATEGORY_SET_ID = DefaultCatalogPEO.CATEGORY_SET_ID

AND ((TRUNC(sysdate) >= TRUNC(NVL(CategoryPEO.START_DATE_ACTIVE, sysdate-1)))

AND (TRUNC(sysdate) <= TRUNC(NVL(CategoryPEO.END_DATE_ACTIVE, sysdate +1))))

)

UNION

(SELECT CategoryPEO.CATEGORY_ID AS CHILD_CATEGORY_ID,

2 AS PARENT_CATEGORY_ID,

'Y' AS PURCHASING_CAT_FLAG,

NULL AS CATEGORY_NAME,

CategoryPEO.CATEGORY_NAME AS CATEGORY_NAME2,

NULL AS CATEGORY_DESCRIPTION,

CategoryPEO.DESCRIPTION,

NULL AS CATEGORY_ID,

CategoryPEO.CATEGORY_ID AS CATEGORY_ID2,

CategoryPEO.CATEGORY_NAME AS DISPLAY_CATEGORY_NAME,

CategoryPEO.DESCRIPTION AS DISPLAY_CATEGORY_DESCRIPTION,

NULL AS IMAGE_URL,

CategoryPEO.SUPPLIER_ENABLED_FLAG AS SUPPLIER_ENABLED_FLAG,

NULL AS CATEGORY_TYPE

FROM EGP_CATEGORIES_VL CategoryPEO,

EGP_CATEGORY_SET_VALID_CATS ValidCategoryPEO,

EGP_DEFAULT_CATEGORY_SETS DefaultCatalogPEO

WHERE (CategoryPEO.CATEGORY_ID NOT IN (SELECT OnlyHierarchyCategories.CHILD_CATEGORY_ID

FROM POR_BROWSE_TREES OnlyHierarchyCategories, POR_BROWSE_CATEGORIES_B ParentBrowseCategories

WHERE OnlyHierarchyCategories.PURCHASING_CAT_FLAG = 'Y'

AND OnlyHierarchyCategories.PARENT_CATEGORY_ID = ParentBrowseCategories.CATEGORY_ID

AND ParentBrowseCategories.TYPE = 'SOURCING'))

AND CategoryPEO.CATEGORY_ID = ValidCategoryPEO.CATEGORY_ID(+)

AND DefaultCatalogPEO.FUNCTIONAL_AREA_ID = 2

AND ValidCategoryPEO.CATEGORY_SET_ID = DefaultCatalogPEO.CATEGORY_SET_ID

AND ((TRUNC(sysdate) >= TRUNC(NVL(CategoryPEO.START_DATE_ACTIVE, sysdate-1)))

AND (TRUNC(sysdate) <= TRUNC(NVL(CategoryPEO.END_DATE_ACTIVE, sysdate +1))))

)

ORDER BY "PURCHASING_CAT_FLAG","DISPLAY_CATEGORY_NAME"