1. Tables and Views for Sales and Fusion Service
  2. MKL_GEO_NAME_REFERENCES_V

MKL_GEO_NAME_REFERENCES_V

Details

  • Schema: FUSION

  • Object owner: MKL

  • Object type: VIEW

Columns

Name

GEO_NAME_REFERENCE_ID

LOCATION_ID

GEOGRAPHY_ID

LOCATION_TABLE_NAME

OBJECT_VERSION_NUMBER

GEOGRAPHY_TYPE

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

REQUEST_ID

PROGRAM_APP_NAME

CONFLICT_ID

USER_LAST_UPDATE_DATE

LOC_EFFECTIVE_START_DATE

LOC_EFFECTIVE_END_DATE

LEVEL_NUMBER

MAX_LEV

Query

SQL_Statement

SELECT GEO_NAME_REFERENCE_ID,

LOCATION_ID,

GEOGRAPHY_ID,

LOCATION_TABLE_NAME,

OBJECT_VERSION_NUMBER,

GEOGRAPHY_TYPE,

LAST_UPDATED_BY,

CREATION_DATE,

CREATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATE_LOGIN,

REQUEST_ID,

PROGRAM_APP_NAME,

CONFLICT_ID,

USER_LAST_UPDATE_DATE,

LOC_EFFECTIVE_START_DATE,

LOC_EFFECTIVE_END_DATE,

LEVEL_NUMBER,

MAX_LEV

FROM

(SELECT gr1.geo_name_reference_id,

gr1.location_id,

gr1.geography_id,

gr1.location_table_name,

gr1.object_version_number,

gr1.geography_type,

gr1.last_updated_by,

gr1.creation_date,

gr1.created_by,

gr1.last_update_date,

gr1.last_update_login,

gr1.request_id,

gr1.program_app_name,

gr1.conflict_id,

gr1.user_last_update_date,

gr1.loc_effective_start_date,

gr1.loc_effective_end_date,

gr1.level_number,

(SELECT MAX(gr.level_number)

FROM fusion.hz_geo_name_references gr,

fusion.hz_geo_hierarchy_cf hn

WHERE gr.geography_id = hn.base_geo_id

AND gr.location_id = gr1.location_id

AND hn.hierarchy_type = 'TM'

AND hn.status = 'A'

AND TRUNC(SYSDATE) BETWEEN hn.effective_start_date AND hn.effective_end_date

AND TRUNC(SYSDATE) BETWEEN gr.loc_effective_start_date AND gr.loc_effective_end_date

and gr.location_table_name ='MKL_LM_LEADS'

) max_lev

FROM fusion.hz_geo_name_references gr1

WHERE TRUNC(SYSDATE) BETWEEN gr1.loc_effective_start_date AND gr1.loc_effective_end_date

and gr1.location_table_name ='MKL_LM_LEADS'

)

WHERE max_lev = level_number

and location_table_name ='MKL_LM_LEADS'