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

MOT_TERR_COVERAGES_V

Details

  • Schema: FUSION

  • Object owner: MOT

  • Object type: VIEW

Columns

Name

COVERAGE_ID

TERR_DIMENSION_CODE

DIMENSION_NAME

DISPLAY_FLAG

ACTIVE_FLAG

TERR_DIM_PARTITION_ID

TERR_DIM_INTG_ID

SOURCE_LOOKUP_TYPE

SYNCHEDDIMENSIONCODE

Query

SQL_Statement

SELECT mtc.coverage_id, mtc.terr_dimension_code, mtd.name AS DIMENSION_NAME,

mtd.display_flag, mtd.active_flag, mtp.terr_dim_partition_id,

CASE

WHEN mtc.terr_dimension_code = 'CSize' THEN CUSTOMER_SIZE_CODE

WHEN (mtc.terr_dimension_code = 'Prod' and INVENTORY_ITEM_ID IS NOT NULL) THEN to_char(INVENTORY_ITEM_ID)

WHEN (mtc.terr_dimension_code = 'Prod' and PRODUCT_GROUP_ID IS NOT NULL) THEN to_char(PRODUCT_GROUP_ID)

WHEN mtc.terr_dimension_code = 'AcTyp' THEN ACCOUNT_TYPE_CODE

WHEN mtc.terr_dimension_code = 'BUnit' THEN to_char(BUSINESS_UNIT_ID)

WHEN mtc.terr_dimension_code = 'Prtnr' THEN to_char(PARTNER_ID)

WHEN mtc.terr_dimension_code = 'SChnl' THEN to_char(SALES_CHANNEL_CODE || '~' || NVL(SUB_SALES_CHANNEL_CODE, ''))

WHEN mtc.terr_dimension_code = 'Aux1' THEN (fnd_profile.value('MOT_AUX_DIM_CLASS_CATEGORY_1') || '~' || AUXILIARY1_CODE)

WHEN mtc.terr_dimension_code = 'Aux2' THEN (fnd_profile.value('MOT_AUX_DIM_CLASS_CATEGORY_2') || '~' || AUXILIARY2_CODE)

WHEN mtc.terr_dimension_code = 'Aux3' THEN (fnd_profile.value('MOT_AUX_DIM_CLASS_CATEGORY_3') || '~' || AUXILIARY3_CODE)

WHEN mtc.terr_dimension_code = 'Indst' THEN (fnd_profile.value('MOT_INDUSTRY_CLASS_CATEGORY') || '~' || INDUSTRY_CODE)

WHEN mtc.terr_dimension_code = 'OrgTp' THEN to_char('ORGANIZATION_TYPE' || '~' || ORGANIZATION_TYPE_CODE)

WHEN mtc.terr_dimension_code = 'Ext1' THEN EXTENSION1_CODE

WHEN mtc.terr_dimension_code = 'Ext2' THEN EXTENSION2_CODE

WHEN mtc.terr_dimension_code = 'Ext3' THEN EXTENSION3_CODE

WHEN mtc.terr_dimension_code = 'Ext4' THEN EXTENSION4_CODE

WHEN mtc.terr_dimension_code = 'Ext5' THEN EXTENSION5_CODE

WHEN mtc.terr_dimension_code = 'Ext6' THEN EXTENSION6_CODE

WHEN mtc.terr_dimension_code = 'Ext7' THEN EXTENSION7_CODE

WHEN mtc.terr_dimension_code = 'Ext8' THEN EXTENSION8_CODE

WHEN mtc.terr_dimension_code = 'Ext9' THEN EXTENSION9_CODE

WHEN mtc.terr_dimension_code = 'Ext10' THEN EXTENSION10_CODE

END AS Terr_dim_intg_id,

mdp.value_code Source_lookup_type,

CASE WHEN (mtc.terr_dimension_code in ( 'Acct','Prtnr','Geo') OR mtc.terr_dimension_code LIKE 'Ext%') THEN null ELSE mtc.terr_dimension_code END AS SynchedDimensionCode

FROM mot_terr_coverages mtc, mot_terr_dimensions_vl mtd, mot_terr_dim_partitions mtp, mot_terr_dim_parameters mdp

WHERE mtc.terr_dimension_code = mtd.dimension_code (+)

and mtd.partition_id (+) = mtp.terr_dim_partition_id

and mtp.type_code = 'PRODUCTION'

and mtd.terr_dimension_id = mdp.terr_dimension_id (+)

and mdp.internal_code (+)= 'SOURCE_LOOKUP_TYPE'