ZCA_S_ACCT_CLASSIFICATIONS_V

Details

• Schema: FUSION

• Object owner: ZCA

• Object type: VIEW

Columns

Name

SALES_ACCOUNT_ID 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_CA_AUX_DIM_CLASS_CAT_1', 'MOT_CA_AUX_DIM_CLASS_CAT_2', 'MOT_CA_AUX_DIM_CLASS_CAT_3', 'MOT_CA_AUX_DIM_CLASS_CAT_4', 'MOT_CA_AUX_DIM_CLASS_CAT_5', 'MOT_CA_AUX_DIM_CLASS_CAT_6', 'MOT_CA_AUX_DIM_CLASS_CAT_7', 'MOT_CA_AUX_DIM_CLASS_CAT_8', 'MOT_CA_AUX_DIM_CLASS_CAT_9', 'MOT_CA_AUX_DIM_CLASS_CAT_10') AND pov.profile_option_value IS NOT NULL ) SELECT hzca.sales_account_id sales_account_id, (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_1' ) AS CLASS_CATEGORY_1, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_2' ) AS CLASS_CATEGORY_2, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_3' ) AS CLASS_CATEGORY_3, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_4' ) AS CLASS_CATEGORY_4, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_5' ) AS CLASS_CATEGORY_5, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_6' ) AS CLASS_CATEGORY_6, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_7' ) AS CLASS_CATEGORY_7, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_8' ) AS CLASS_CATEGORY_8, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_9' ) AS CLASS_CATEGORY_9, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_10' ) AS CLASS_CATEGORY_10, listagg( CASE WHEN hzca.class_category = (SELECT class_category FROM AUX_DIM_CLASS_CAT_MAP WHERE dim_code = 'MOT_CA_AUX_DIM_CLASS_CAT_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_CA_AUX_DIM_CLASS_CAT_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 zca_sa_code_assignments hzca, AUX_DIM_CLASS_CAT_MAP adccm WHERE 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.sales_account_id