HZ_PARTY_CLASSIFICATIONS

Details

Schema: FUSION
Object owner: HZ
Object type: VIEW

Columns

Name
PARTY_ID INDUSTRY_CLASS_CATEGORY INDUSTRY_CODE_ASSIGNMENT_ID INDUSTRY_CLASS_CODE ORG_TYPE_CLASS_CATEGORY ORG_TYPE_CODE_ASSIGNMENT_ID ORG_TYPE_CLASS_CODE CLASS_CATEGORY_1 CODE_ASSIGNMENT_ID_1 CLASS_CODE_1 CLASS_CATEGORY_2 CODE_ASSIGNMENT_ID_2 CLASS_CODE_2 CLASS_CATEGORY_3 CODE_ASSIGNMENT_ID_3 CLASS_CODE_3 CLASS_CATEGORY_4 CODE_ASSIGNMENT_ID_4 CLASS_CODE_4 CLASS_CATEGORY_5 CODE_ASSIGNMENT_ID_5 CLASS_CODE_5 CLASS_CATEGORY_6 CODE_ASSIGNMENT_ID_6 CLASS_CODE_6 CLASS_CATEGORY_7 CODE_ASSIGNMENT_ID_7 CLASS_CODE_7 CLASS_CATEGORY_8 CODE_ASSIGNMENT_ID_8 CLASS_CODE_8 CLASS_CATEGORY_9 CODE_ASSIGNMENT_ID_9 CLASS_CODE_9 CLASS_CATEGORY_10 CODE_ASSIGNMENT_ID_10 CLASS_CODE_10 LAST_UPDATE_DATE

Name

PARTY_ID

INDUSTRY_CLASS_CATEGORY

INDUSTRY_CODE_ASSIGNMENT_ID

INDUSTRY_CLASS_CODE

ORG_TYPE_CLASS_CATEGORY

ORG_TYPE_CODE_ASSIGNMENT_ID

ORG_TYPE_CLASS_CODE

CLASS_CATEGORY_1

CODE_ASSIGNMENT_ID_1

CLASS_CODE_1

CLASS_CATEGORY_2

CODE_ASSIGNMENT_ID_2

CLASS_CODE_2

CLASS_CATEGORY_3

CODE_ASSIGNMENT_ID_3

CLASS_CODE_3

CLASS_CATEGORY_4

CODE_ASSIGNMENT_ID_4

CLASS_CODE_4

CLASS_CATEGORY_5

CODE_ASSIGNMENT_ID_5

CLASS_CODE_5

CLASS_CATEGORY_6

CODE_ASSIGNMENT_ID_6

CLASS_CODE_6

CLASS_CATEGORY_7

CODE_ASSIGNMENT_ID_7

CLASS_CODE_7

CLASS_CATEGORY_8

CODE_ASSIGNMENT_ID_8

CLASS_CODE_8

CLASS_CATEGORY_9

CODE_ASSIGNMENT_ID_9

CLASS_CODE_9

CLASS_CATEGORY_10

CODE_ASSIGNMENT_ID_10

CLASS_CODE_10

LAST_UPDATE_DATE

Query

SQL_Statement
WITH AUX_DIM_CLASS_CAT_MAP AS (SELECT po.profile_option_name dim_code, pov.profile_option_value class_category FROM fnd_profile_options po, fnd_profile_option_values pov WHERE po.profile_option_id = pov.profile_option_id AND po.application_id = pov.application_id AND pov.level_name = 'SITE' AND po.profile_option_name IN ('MOT_INDUSTRY_CLASS_CATEGORY', 'MOT_AUX_DIM_CLASS_CATEGORY_1', 'MOT_AUX_DIM_CLASS_CATEGORY_2', 'MOT_AUX_DIM_CLASS_CATEGORY_3', 'MOT_AUX_DIM_CLASS_CATEGORY_4', 'MOT_AUX_DIM_CLASS_CATEGORY_5', 'MOT_AUX_DIM_CLASS_CATEGORY_6', 'MOT_AUX_DIM_CLASS_CATEGORY_7', 'MOT_AUX_DIM_CLASS_CATEGORY_8', 'MOT_AUX_DIM_CLASS_CATEGORY_9', 'MOT_AUX_DIM_CLASS_CATEGORY_10') AND pov.profile_option_value IS NOT NULL UNION ALL SELECT 'MOT_ORG_TYPE_CLASS_CATEGORY', 'ORGANIZATION_TYPE' class_category FROM (select 1 from hz_code_assignments where rownum = 1) ) SELECT hzca.owner_table_id party_id, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_INDUSTRY_CLASS_CATEGORY' ) AS INDUSTRY_CLASS_CATEGORY, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_INDUSTRY_CLASS_CATEGORY' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS INDUSTRY_CODE_ASSIGNMENT_ID, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_INDUSTRY_CLASS_CATEGORY' ) THEN hzca.class_category END , ',') within GROUP ( ORDER BY class_code ) AS INDUSTRY_CLASS_CODE, 'ORGANIZATION_TYPE' AS ORG_TYPE_CLASS_CATEGORY, listagg( CASE WHEN hzca.class_category = 'ORGANIZATION_TYPE' THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS ORG_TYPE_CODE_ASSIGNMENT_ID, listagg( CASE WHEN hzca.class_category = 'ORGANIZATION_TYPE' THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS ORG_TYPE_CLASS_CODE, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_1' ) AS CLASS_CATEGORY_1, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_1' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_1, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_1' ) THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS CLASS_CODE_1, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_2' ) AS CLASS_CATEGORY_2, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_2' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_2, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_2' ) THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS CLASS_CODE_2, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_3' ) AS CLASS_CATEGORY_3, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_3' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_3, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_3' ) THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS CLASS_CODE_3, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_4' ) AS CLASS_CATEGORY_4, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_4' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_4, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_4' ) THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS CLASS_CODE_4, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_5' ) AS CLASS_CATEGORY_5, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_5' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_5, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_5' ) THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS CLASS_CODE_5, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_6' ) AS CLASS_CATEGORY_6, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_6' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_6, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_6' ) THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS CLASS_CODE_6, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_7' ) AS CLASS_CATEGORY_7, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_7' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_7, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_7' ) THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS CLASS_CODE_7, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_8' ) AS CLASS_CATEGORY_8, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_8' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_8, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_8' ) THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS CLASS_CODE_8, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_9' ) AS CLASS_CATEGORY_9, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_9' ) THEN hzca.code_assignment_id END , ',') within GROUP ( ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_9, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_9' ) THEN hzca.class_code END , ',') within GROUP ( ORDER BY class_code ) AS CLASS_CODE_9, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_10' ) AS CLASS_CATEGORY_10, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_10' ) THEN hzca.code_assignment_id END , ',') within GROUP (ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_10, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_10' ) THEN hzca.class_code END , ',') within GROUP (ORDER BY class_code ) AS CLASS_CODE_10, MAX( hzca.last_update_date ) AS LAST_UPDATE_DATE FROM hz_code_assignments hzca, AUX_DIM_CLASS_CAT_MAP adccm WHERE hzca.owner_table_name = 'HZ_PARTIES' AND hzca.primary_flag = 'Y' AND hzca.status = 'A' AND hzca.class_category = adccm.class_category AND (( hzca.start_date_active <= TRUNC(sysdate) OR hzca.start_date_active IS NULL) AND ( hzca.end_date_active >= TRUNC(sysdate) OR hzca.end_date_active IS NULL) ) GROUP BY hzca.owner_table_id

SQL_Statement

WITH AUX_DIM_CLASS_CAT_MAP AS

(SELECT po.profile_option_name dim_code,

pov.profile_option_value class_category

FROM fnd_profile_options po,

fnd_profile_option_values pov

WHERE po.profile_option_id = pov.profile_option_id

AND po.application_id = pov.application_id

AND pov.level_name = 'SITE'

AND po.profile_option_name IN

('MOT_INDUSTRY_CLASS_CATEGORY',

'MOT_AUX_DIM_CLASS_CATEGORY_1',

'MOT_AUX_DIM_CLASS_CATEGORY_2',

'MOT_AUX_DIM_CLASS_CATEGORY_3',

'MOT_AUX_DIM_CLASS_CATEGORY_4',

'MOT_AUX_DIM_CLASS_CATEGORY_5',

'MOT_AUX_DIM_CLASS_CATEGORY_6',

'MOT_AUX_DIM_CLASS_CATEGORY_7',

'MOT_AUX_DIM_CLASS_CATEGORY_8',

'MOT_AUX_DIM_CLASS_CATEGORY_9',

'MOT_AUX_DIM_CLASS_CATEGORY_10')

AND pov.profile_option_value IS NOT NULL

UNION ALL

SELECT 'MOT_ORG_TYPE_CLASS_CATEGORY',

'ORGANIZATION_TYPE' class_category

FROM (select 1 from hz_code_assignments where rownum = 1)

)

SELECT hzca.owner_table_id party_id,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_INDUSTRY_CLASS_CATEGORY'

) AS INDUSTRY_CLASS_CATEGORY,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_INDUSTRY_CLASS_CATEGORY'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS INDUSTRY_CODE_ASSIGNMENT_ID,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_INDUSTRY_CLASS_CATEGORY'

)

THEN hzca.class_category

END , ',') within GROUP (

ORDER BY class_code ) AS INDUSTRY_CLASS_CODE,

'ORGANIZATION_TYPE' AS ORG_TYPE_CLASS_CATEGORY,

listagg(

CASE

WHEN hzca.class_category = 'ORGANIZATION_TYPE'

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS ORG_TYPE_CODE_ASSIGNMENT_ID,

listagg(

CASE

WHEN hzca.class_category = 'ORGANIZATION_TYPE'

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS ORG_TYPE_CLASS_CODE,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_1'

) AS CLASS_CATEGORY_1,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_1'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_1,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_1'

)

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS CLASS_CODE_1,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_2'

) AS CLASS_CATEGORY_2,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_2'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_2,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_2'

)

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS CLASS_CODE_2,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_3'

) AS CLASS_CATEGORY_3,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_3'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_3,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_3'

)

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS CLASS_CODE_3,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_4'

) AS CLASS_CATEGORY_4,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_4'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_4,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_4'

)

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS CLASS_CODE_4,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_5'

) AS CLASS_CATEGORY_5,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_5'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_5,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_5'

)

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS CLASS_CODE_5,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_6'

) AS CLASS_CATEGORY_6,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_6'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_6,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_6'

)

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS CLASS_CODE_6,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_7'

) AS CLASS_CATEGORY_7,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_7'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_7,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_7'

)

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS CLASS_CODE_7,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_8'

) AS CLASS_CATEGORY_8,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_8'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_8,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_8'

)

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS CLASS_CODE_8,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_9'

) AS CLASS_CATEGORY_9,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_9'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (

ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_9,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_9'

)

THEN hzca.class_code

END , ',') within GROUP (

ORDER BY class_code ) AS CLASS_CODE_9,

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_10'

) AS CLASS_CATEGORY_10,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_10'

)

THEN hzca.code_assignment_id

END , ',') within GROUP (ORDER BY class_code ) AS CODE_ASSIGNMENT_ID_10,

listagg(

CASE

WHEN hzca.class_category =

(SELECT class_category

FROM AUX_DIM_CLASS_CAT_MAP

WHERE dim_code = 'MOT_AUX_DIM_CLASS_CATEGORY_10'

)

THEN hzca.class_code

END , ',') within GROUP (ORDER BY class_code ) AS CLASS_CODE_10,

MAX( hzca.last_update_date ) AS LAST_UPDATE_DATE

FROM hz_code_assignments hzca,

AUX_DIM_CLASS_CAT_MAP adccm

WHERE hzca.owner_table_name = 'HZ_PARTIES'

AND hzca.primary_flag = 'Y'

AND hzca.status = 'A'

AND hzca.class_category = adccm.class_category

AND (( hzca.start_date_active <= TRUNC(sysdate)

OR hzca.start_date_active IS NULL)

AND ( hzca.end_date_active >= TRUNC(sysdate)

OR hzca.end_date_active IS NULL) )

GROUP BY hzca.owner_table_id