POZ_SUPP_PROD_SERVICES_ATTR_V

Details

• Schema: FUSION

• Object owner: POZ

• Object type: VIEW

Columns

Name
CATEGORY_ID CATEGORY_NAME CATEGORY_DESCRIPTION ANCESTOR_CATEGORY_ID ANCESTOR_CATEGORY_NAME ANCESTOR_CATEGORY_DESCRIPTION CALCULATED_PATH DISTANCE ISLEAF

Query

SQL_Statement

SELECT connect_by_root t.child_category_id category_id, connect_by_root NVL(c.category_name,e.category_name) category_name, connect_by_root NVL(c.category_description,e.description) category_description, NVL(c.category_id,e.category_id) ancestor_category_id, NVL(c.category_name,e.category_name) ancestor_category_name, NVL(c.category_description, e.description) ancestor_category_description, CONCAT(reverse(LTRIM(SYS_CONNECT_BY_PATH(reverse(NVL(c.CATEGORY_NAME,e.category_name)),'>'), '>')),'>') AS CALCULATED_PATH, LEVEL AS distance, CASE WHEN LEVEL = 1 THEN 1 ELSE 0 END AS IsLeaf FROM POR_BROWSE_CATEGORIES_VL c, POR_BROWSE_TREES t, EGP_CATEGORIES_VL e WHERE t.child_category_id = c.category_id(+) AND t.child_category_id = e.category_id(+) START WITH (t.parent_category_id, t.child_category_id) IN (SELECT parent_category_id, child_category_id FROM fusion.POR_BROWSE_TREES tt, fusion.POR_BROWSE_CATEGORIES_VL cc WHERE tt.parent_category_id=cc.category_id AND cc.TYPE='SUPPLIER' ) CONNECT BY (PRIOR t.PARENT_CATEGORY_ID = t.CHILD_CATEGORY_ID AND c.TYPE='SUPPLIER')