MSC_DIM_SUPPLIER

Details

Name
ID NAME PARENT_ID REFRESH_NUMBER

Name

ID

NAME

PARENT_ID

REFRESH_NUMBER

SQL_Statement
SELECT DISTINCT A.ID, A.NAME, NVL(B.PARENT_ID, A.PARENT_ID) PARENT_ID, A.REFRESH_NUMBER FROM (SELECT MGTP.TP_ID ID , MGTP.PARTNER_NAME NAME, -1 PARENT_ID, MGTP.TP_ID\|\|'-'\|\|MGTP.PARTNER_NAME DESCRIPTION, MGTP.REFRESH_NUMBER REFRESH_NUMBER FROM MSC_GLOBAL_TRADING_PARTNERS MGTP WHERE MGTP.PARTNER_TYPE = 1) 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 -1000 ID, FS.MESSAGE_TEXT NAME, -1 PARENT_ID, -1 REFRESH_NUMBER FROM FND_MESSAGES FS WHERE FS.MESSAGE_NAME = 'MSC_SOP_MG_DFLT_SPLR_NAME' AND FS.APPLICATION_ID=724

SQL_Statement

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

FROM

(SELECT MGTP.TP_ID ID ,

MGTP.PARTNER_NAME NAME,

-1 PARENT_ID,

MGTP.TP_ID||'-'||MGTP.PARTNER_NAME DESCRIPTION,

MGTP.REFRESH_NUMBER REFRESH_NUMBER

FROM MSC_GLOBAL_TRADING_PARTNERS MGTP

WHERE MGTP.PARTNER_TYPE = 1) 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 -1000 ID,

FS.MESSAGE_TEXT NAME,

-1 PARENT_ID,

-1 REFRESH_NUMBER

FROM FND_MESSAGES FS WHERE FS.MESSAGE_NAME = 'MSC_SOP_MG_DFLT_SPLR_NAME'

AND FS.APPLICATION_ID=724