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

MOO_REVN_METRICS_V

Details

  • Schema: FUSION

  • Object owner: MOO

  • Object type: VIEW

Columns

Name

PERIOD_NAME

START_DATE

END_DATE

TERRITORY_ID

SALES_CREDIT_TYPE_CODE

PRODUCT

REVN_METRIC_TYPE

COMMIT_FLAG

REVN_METRIC_VALUE

Query

SQL_Statement

SELECT

periods.period_name,

periods.start_date,

periods.end_date,

terr.territory_id,

r.sales_credit_type_code,

nvl(r.prod_group_id, r.inventory_item_id) product,

r.status_category revn_metric_type,

nvl(r.commit_flag, 'N') commit_flag,

SUM(r.revn_amt * r.crm_conversion_rate) revn_metric_value

FROM

moo_revn r

,

zsf_fcst_header hdr,

zsf_fcst_adjust_period periods

,

mot_territories terr

WHERE

( 1 = 1 )

AND ( r.crm_conversion_rate IS NOT NULL )

AND ( r.split_type_code = 'NONE'

OR r.split_type_code = 'DETAILCHILDSPLIT'

OR r.split_type_code IS NULL )

AND ( r.recur_type_code = 'NONRECUR'

OR r.recur_type_code = 'CHILDRECUR'

OR r.recur_type_code IS NULL )

AND ( r.primary_flag = 'N' )

AND nvl(r.status_category, 'OPEN') IN ( 'OPEN', 'WON' )

AND trunc(r.effective_date) BETWEEN periods.start_date AND periods.end_date

AND hdr.fcst_header_id = periods.fcst_header_id

AND hdr.forecast_type = 'REVENUE'

AND ( hdr.fcst_status_qualifier_code IN ( 'ACTIVE', 'NEXT' )

AND hdr.latest_flag = 'Y' )

AND hdr.delete_flag = 'N'

AND r.pr_territory_version_id = terr.territory_version_id

GROUP BY

periods.period_name,

periods.start_date,

periods.end_date,

terr.territory_id,

r.sales_credit_type_code,

nvl(r.prod_group_id, r.inventory_item_id),

r.status_category,

nvl(r.commit_flag, 'N')