RA_MC_ANCESTOR1

(SQL View)
 Index Back

Marketing Center Ancestors

This view returns not only the immediate marketing center parent but all ancestor marketing centers in the tree heirarchy.

SELECT RA_MKT_CENTER , RA_PARENT_MKTCTR FROM PS_RA_UD_MKTCENTER WHERE ra_mc_level_num = 4 UNION SELECT RA_MKT_CENTER , 'ROOT' FROM PS_RA_UD_MKTCENTER WHERE ra_mc_level_num = 4 UNION SELECT DISTINCT a.RA_MKT_CENTER , b.RA_PARENT_MKTCTR FROM PS_RA_UD_MKTCENTER A LEFT OUTER JOIN PS_RA_UD_MKTCENTER B ON a.cs_institution=b.cs_institution WHERE a.ra_mc_level_num = 4 AND b.ra_mc_level_num=3 UNION SELECT RA_MKT_CENTER , RA_PARENT_MKTCTR FROM PS_RA_UD_MKTCENTER WHERE ra_mc_level_num = 3 UNION SELECT RA_MKT_CENTER , 'ROOT' FROM PS_RA_UD_MKTCENTER WHERE ra_mc_level_num = 3 UNION SELECT RA_MKT_CENTER , RA_PARENT_MKTCTR FROM PS_RA_UD_MKTCENTER WHERE ra_mc_level_num = 2 UNION SELECT RA_MKT_CENTER , RA_PARENT_MKTCTR FROM PS_RA_UD_MKTCENTER WHERE ra_mc_level_num = 1

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 RA_MKT_CENTER Character(10) VARCHAR2(10) NOT NULL Marketing Center code
2 RA_PARENT_MKTCTR Character(10) VARCHAR2(10) NOT NULL Parent Marketing Center