1. Tables and Views for SCM
  2. MSC_AP_CATALOG_CATEGORIES_V

MSC_AP_CATALOG_CATEGORIES_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

CATEGORY_SET_ID

CATEGORY_ID

PARENT_CATEGORY_ID

CATEGORY_CODE

CATEGORY_NAME

PARENT_CATEGORY_NAME

CATEGORY_SET_NAME

START_DATE_ACTIVE

DESCRIPTION

END_DATE_ACTIVE

CATEGORY_ACTIVE_STATUS

Query

SQL_Statement

SELECT

ecvs.CATEGORY_SET_ID CATEGORY_SET_ID,

ecvs.CATEGORY_ID CATEGORY_ID,

ecvs.PARENT_CATEGORY_ID PARENT_CATEGORY_ID,

ECS1.CATEGORY_CODE,

ECS1.CATEGORY_NAME,

ECS2.CATEGORY_NAME PARENT_CATEGORY_NAME,

ECS3.CATEGORY_SET_NAME,

TRUNC(ecs1.START_DATE_ACTIVE) START_DATE_ACTIVE,

ecs1.description,

TRUNC(ecs1.END_DATE_ACTIVE) END_DATE_ACTIVE,

(

CASE

WHEN TRUNC(SYSDATE) >= TRUNC(NVL(COALESCE(GREATEST(ecs1.start_date_active,ecs3.start_date),ecs1.start_date_active,ecs3.start_date),SYSDATE))

AND

TRUNC(SYSDATE) <= TRUNC(NVL(COALESCE(LEAST(ecs1.end_date_active,ecs3.end_date),ecs1.end_date_active,ecs3.end_date),SYSDATE))

THEN 1

ELSE 2

END

) category_active_status

FROM

EGP_CATEGORIES_VL ecs1,

EGP_CATEGORIES_VL ecs2,

MSC_AP_CATEGORY_SETS_V ecs3,

EGP_CATEGORY_SET_VALID_CATS ECVS

WHERE ECS1.CATEGORY_ID = ECVS.CATEGORY_ID

AND ECS2.CATEGORY_ID(+) = ECVS.PARENT_CATEGORY_ID

AND ECS3.SR_CATEGORY_SET_ID = ECVS.CATEGORY_SET_ID