MSC_DIM_BU

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ID NAME DESCRIPTION PARENT_ID REFRESH_NUMBER SR_INSTANCE_ID

Name

ID

NAME

DESCRIPTION

PARENT_ID

REFRESH_NUMBER

SR_INSTANCE_ID

Query

SQL_Statement
SELECT ID, NAME, DESCRIPTION, max(PARENT_ID) PARENT_ID, -1 REFRESH_NUMBER, SR_INSTANCE_ID from (SELECT P.BUSINESS_UNIT_ID ID , P.BUSINESS_UNIT_NAME NAME , P.BUSINESS_UNIT_ID\|\|'-'\|\|P.BUSINESS_UNIT_NAME DESCRIPTION, P.SR_LEGAL_ENTITY PARENT_ID , P.REFRESH_NUMBER REFRESH_NUMBER, IO.SR_INSTANCE_ID SR_INSTANCE_ID FROM MSC_PARAMETERS P, MSC_APPS_INSTANCES AI, MSC_INSTANCE_ORGS IO WHERE AI.INSTANCE_ID = IO.SR_INSTANCE_ID AND P.ORGANIZATION_ID = IO.ORGANIZATION_ID AND P.BUSINESS_UNIT_ID IS NOT NULL AND P.SR_LEGAL_ENTITY IS NOT NULL AND AI.APPS_VER = 2 UNION ALL SELECT P.OPERATING_UNIT_ID ID , P.OPERATING_UNIT_NAME NAME , P.OPERATING_UNIT_ID\|\|'-'\|\|P.OPERATING_UNIT_NAME DESCRIPTION, P.SR_LEGAL_ENTITY PARENT_ID , P.REFRESH_NUMBER REFRESH_NUMBER, IO.SR_INSTANCE_ID SR_INSTANCE_ID FROM MSC_PARAMETERS P, MSC_APPS_INSTANCES AI, MSC_INSTANCE_ORGS IO WHERE AI.INSTANCE_ID = IO.SR_INSTANCE_ID AND P.ORGANIZATION_ID = IO.ORGANIZATION_ID AND P.OPERATING_UNIT_ID IS NOT NULL AND P.SR_LEGAL_ENTITY IS NOT NULL AND AI.APPS_VER != 2 UNION ALL SELECT -1 ID,'-1' NAME, '-1' DESCRIPTION,-1 PARENT_ID,-1,-1 REFRESH_NUMBER from dual ) group by ID, NAME, DESCRIPTION, SR_INSTANCE_ID

SQL_Statement

SELECT

ID,

NAME,

DESCRIPTION,

max(PARENT_ID) PARENT_ID,

-1 REFRESH_NUMBER,

SR_INSTANCE_ID

from

(SELECT

P.BUSINESS_UNIT_ID ID ,

P.BUSINESS_UNIT_NAME NAME ,

P.BUSINESS_UNIT_ID||'-'||P.BUSINESS_UNIT_NAME DESCRIPTION,

P.SR_LEGAL_ENTITY PARENT_ID ,

P.REFRESH_NUMBER REFRESH_NUMBER,

IO.SR_INSTANCE_ID SR_INSTANCE_ID

FROM MSC_PARAMETERS P, MSC_APPS_INSTANCES AI, MSC_INSTANCE_ORGS IO

WHERE AI.INSTANCE_ID = IO.SR_INSTANCE_ID

AND P.ORGANIZATION_ID = IO.ORGANIZATION_ID

AND P.BUSINESS_UNIT_ID IS NOT NULL

AND P.SR_LEGAL_ENTITY IS NOT NULL

AND AI.APPS_VER = 2

UNION ALL

SELECT

P.OPERATING_UNIT_ID ID ,

P.OPERATING_UNIT_NAME NAME ,

P.OPERATING_UNIT_ID||'-'||P.OPERATING_UNIT_NAME DESCRIPTION,

P.SR_LEGAL_ENTITY PARENT_ID ,

P.REFRESH_NUMBER REFRESH_NUMBER,

IO.SR_INSTANCE_ID SR_INSTANCE_ID

FROM MSC_PARAMETERS P, MSC_APPS_INSTANCES AI, MSC_INSTANCE_ORGS IO

WHERE AI.INSTANCE_ID = IO.SR_INSTANCE_ID

AND P.ORGANIZATION_ID = IO.ORGANIZATION_ID

AND P.OPERATING_UNIT_ID IS NOT NULL

AND P.SR_LEGAL_ENTITY IS NOT NULL

AND AI.APPS_VER != 2

UNION ALL

SELECT -1 ID,'-1' NAME, '-1' DESCRIPTION,-1 PARENT_ID,-1,-1 REFRESH_NUMBER from dual

)

group by ID, NAME, DESCRIPTION, SR_INSTANCE_ID