MSC_DIM_LE

Details

Name
ID NAME PARENT_ID REFRESH_NUMBER SR_INSTANCE_ID

Name

ID

NAME

PARENT_ID

REFRESH_NUMBER

SR_INSTANCE_ID

SQL_Statement
SELECT ID, NAME, PARENT_ID, MAX(REFRESH_NUMBER) REFRESH_NUMBER, SR_INSTANCE_ID FROM (SELECT DISTINCT A.ID, A.NAME, NVL(B.PARENT_ID, A.PARENT_ID) PARENT_ID, A.REFRESH_NUMBER, A.SR_INSTANCE_ID FROM (SELECT P.SR_LEGAL_ENTITY ID , P.LEGAL_ENTITY_NAME NAME , P.SR_LEGAL_ENTITY\|\|'-'\|\| P.LEGAL_ENTITY_NAME DESCRIPTION, -1 PARENT_ID, P.REFRESH_NUMBER REFRESH_NUMBER, IO.SR_INSTANCE_ID SR_INSTANCE_ID FROM MSC_PARAMETERS P, MSC_INSTANCE_ORGS IO WHERE P.SR_LEGAL_ENTITY IS NOT NULL AND P.ORGANIZATION_ID = IO.ORGANIZATION_ID) A, (SELECT SHTL.ID ID,SHTL.NAME NAME, LLMD.ID PARENT_ID FROM MSC_DIM_CSTMHRCHY_LLMD LLMD, MSC_DIM_CSTMHRCHY_SHTL SHTL where LLMD.hierarchy_id = SHTL.hierarchy_id and LLMD.LEVEL_ID = SHTL.PARENT_LEVEL_ID and LLMD.ID = SHTL.PARENT_ID) B WHERE A.ID = B.ID (+) AND A.NAME = B.NAME (+) union all select -1 ID,'-1' NAME,-1 parent_id,-1 refresh_number, -1 from dual) GROUP BY ID, NAME, PARENT_ID, SR_INSTANCE_ID

SQL_Statement

SELECT ID, NAME, PARENT_ID, MAX(REFRESH_NUMBER) REFRESH_NUMBER, SR_INSTANCE_ID

FROM

(SELECT DISTINCT A.ID, A.NAME, NVL(B.PARENT_ID, A.PARENT_ID) PARENT_ID, A.REFRESH_NUMBER, A.SR_INSTANCE_ID

FROM

(SELECT

P.SR_LEGAL_ENTITY ID ,

P.LEGAL_ENTITY_NAME NAME ,

P.SR_LEGAL_ENTITY||'-'|| P.LEGAL_ENTITY_NAME DESCRIPTION,

-1 PARENT_ID,

P.REFRESH_NUMBER REFRESH_NUMBER,

IO.SR_INSTANCE_ID SR_INSTANCE_ID

FROM MSC_PARAMETERS P, MSC_INSTANCE_ORGS IO

WHERE P.SR_LEGAL_ENTITY IS NOT NULL

AND P.ORGANIZATION_ID = IO.ORGANIZATION_ID) A,

(SELECT SHTL.ID ID,SHTL.NAME NAME, LLMD.ID PARENT_ID FROM MSC_DIM_CSTMHRCHY_LLMD LLMD, MSC_DIM_CSTMHRCHY_SHTL SHTL

where

LLMD.hierarchy_id = SHTL.hierarchy_id

and LLMD.LEVEL_ID = SHTL.PARENT_LEVEL_ID

and LLMD.ID = SHTL.PARENT_ID) B

WHERE A.ID = B.ID (+)

AND A.NAME = B.NAME (+)

union all select -1 ID,'-1' NAME,-1 parent_id,-1 refresh_number, -1 from dual)

GROUP BY ID, NAME, PARENT_ID, SR_INSTANCE_ID