1. Tables and Views for Sales and Fusion Service
  2. QSC_PROD_GRP_TREE_V

QSC_PROD_GRP_TREE_V

Details

  • Schema: FUSION

  • Object owner: QSC

  • Object type: VIEW

Columns

Name

PROD_GRP_DENORM_ID

FIRST_PROD_GRP_ID

LAST_PROD_GRP_ID

SECOND_LAST_PROD_GRP_ID

DENORM_DISTANCE_NUM

PATH_ID

CONCAT_PATH

DENM_ACTIVE_FLAG

DENM_START_DATE

DENM_END_DATE

DENM_CREATED_BY

DENM_CREATION_DATE

DENM_LAST_UPDATED_BY

DENM_LAST_UPDATE_DATE

DENM_LAST_UPDATE_LOGIN

DENM_USER_LAST_UPDATE_DATE

RUNTIME_STATUS_NUM

ADMIN_STATUS_NUM

BATCH_KEY

REL_ACTIVE_FLAG

REL_START_DATE

REL_END_DATE

PGLEVEL0TOTOP

PGLEVEL1TOTOP

PGLEVEL2TOTOP

PGLEVEL3TOTOP

PGLEVEL4TOTOP

PGLEVEL5TOTOP

PGLEVEL6TOTOP

PGLEVEL7TOTOP

PGLEVEL8TOTOP

PGLEVEL9TOTOP

PGLEVEL10TOTOP

PGLEVEL11TOTOP

PGLEVEL12TOTOP

PGLEVEL13TOTOP

PGLEVEL14TOTOP

USAGE_CODE

ROOT_PROD_GROUP_ID

ROOT_START_DATE

ROOT_END_DATE

DISPLAY_ORDER_NUM

ROOT_LAST_UPDATE_DATE

AGGREG_LAST_UPDATE_DATE

AGGREG_START_DATE

AGGREG_END_DATE

AGGREG_REL_START_DATE

AGGREG_REL_END_DATE

CURR_DATE_SNAPSHOT_FLAG

AGGREG_CHANGE_DATE

Query

SQL_Statement

SELECT

QSC_PROD_GRP_DENORM.PROD_GRP_DENORM_ID,

QSC_PROD_GRP_DENORM.FIRST_PROD_GRP_ID,

QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID,

QSC_PROD_GRP_DENORM.SECOND_LAST_PROD_GRP_ID,

QSC_PROD_GRP_DENORM.DENORM_DISTANCE_NUM,

QSC_PROD_GRP_DENORM.PATH_ID,

QSC_PROD_GRP_DENORM.CONCAT_PATH,

QSC_PROD_GRP_DENORM.ACTIVE_FLAG AS DENM_ACTIVE_FLAG,

QSC_PROD_GRP_DENORM.START_DATE AS DENM_START_DATE,

QSC_PROD_GRP_DENORM.END_DATE AS DENM_END_DATE,

QSC_PROD_GRP_DENORM.CREATED_BY AS DENM_CREATED_BY,

QSC_PROD_GRP_DENORM.CREATION_DATE AS DENM_CREATION_DATE,

QSC_PROD_GRP_DENORM.LAST_UPDATED_BY AS DENM_LAST_UPDATED_BY,

QSC_PROD_GRP_DENORM.LAST_UPDATE_DATE AS DENM_LAST_UPDATE_DATE,

QSC_PROD_GRP_DENORM.LAST_UPDATE_LOGIN AS DENM_LAST_UPDATE_LOGIN,

QSC_PROD_GRP_DENORM.USER_LAST_UPDATE_DATE AS DENM_USER_LAST_UPDATE_DATE,

QSC_PROD_GRP_DENORM.RUNTIME_STATUS_NUM,

QSC_PROD_GRP_DENORM.ADMIN_STATUS_NUM,

QSC_PROD_GRP_DENORM.BATCH_KEY,

QSC_PROD_GRP_DENORM.REL_ACTIVE_FLAG,

QSC_PROD_GRP_DENORM.REL_START_DATE,

QSC_PROD_GRP_DENORM.REL_END_DATE, NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,1,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) AS PGLEVEL0TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,1)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,2) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,1)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL1TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,2)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,3) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,2)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL2TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,3)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,4) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,3)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL3TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,4)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,5) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,4)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL4TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,5)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,6) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,5)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL5TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,6)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,7) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,6)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL6TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,7)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,8) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,7)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL7TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,8)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,9) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,8)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL8TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,9)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,10) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,9)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL9TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,10)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,11) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,10)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL10TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,11)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,12) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,11)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL11TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,12)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,13) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,12)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL12TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,13)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,14) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,13)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL13TOTOP,

NVL(SUBSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH,',',1,14)+1, INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,15) - INSTR(QSC_PROD_GRP_DENORM.CONCAT_PATH, ',',1,14)-1), QSC_PROD_GRP_DENORM.LAST_PROD_GRP_ID) as PGLEVEL14TOTOP,

QSC_PROD_GRP_ROOTS.USAGE_CODE,

QSC_PROD_GRP_ROOTS.ROOT_PROD_GROUP_ID,

QSC_PROD_GRP_ROOTS.START_DATE AS ROOT_START_DATE,

QSC_PROD_GRP_ROOTS.END_DATE AS ROOT_END_DATE,

QSC_PROD_GRP_ROOTS.DISPLAY_ORDER_NUM,

QSC_PROD_GRP_ROOTS.LAST_UPDATE_DATE AS ROOT_LAST_UPDATE_DATE,

GREATEST (QSC_PROD_GRP_DENORM.LAST_UPDATE_DATE,QSC_PROD_GRP_ROOTS.LAST_UPDATE_DATE) AS AGGREG_LAST_UPDATE_DATE,

GREATEST (QSC_PROD_GRP_DENORM.START_DATE,QSC_PROD_GRP_ROOTS.START_DATE) AS AGGREG_START_DATE,

LEAST (QSC_PROD_GRP_DENORM.END_DATE,QSC_PROD_GRP_ROOTS.END_DATE) AS AGGREG_END_DATE,

GREATEST (QSC_PROD_GRP_DENORM.REL_START_DATE, QSC_PROD_GRP_ROOTS.START_DATE) AS AGGREG_REL_START_DATE,

LEAST (QSC_PROD_GRP_DENORM.REL_END_DATE, QSC_PROD_GRP_ROOTS.END_DATE) AS AGGREG_REL_END_DATE,

CASE WHEN SYSTIMESTAMP BETWEEN GREATEST (QSC_PROD_GRP_DENORM.REL_START_DATE,QSC_PROD_GRP_ROOTS.START_DATE) AND LEAST (QSC_PROD_GRP_DENORM.REL_END_DATE,QSC_PROD_GRP_ROOTS.END_DATE)

THEN 'Y' ELSE 'N' END AS CURR_DATE_SNAPSHOT_FLAG,

GREATEST (QSC_PROD_GRP_DENORM.LAST_UPDATE_DATE, QSC_PROD_GRP_ROOTS.LAST_UPDATE_DATE, QSC_PROD_GRP_DENORM.START_DATE) AGGREG_CHANGE_DATE

FROM

QSC_PROD_GRP_ROOTS,

QSC_PROD_GRP_DENORM

WHERE

QSC_PROD_GRP_ROOTS.ROOT_PROD_GROUP_ID = QSC_PROD_GRP_DENORM.FIRST_PROD_GRP_ID

AND QSC_PROD_GRP_DENORM.RUNTIME_STATUS_NUM = 1