eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSD_SR_SALES_FCST_V

Source

View Text - Preformatted


SELECT  28,
   msd_sr_util.get_all_prd_pk,
   15,
   msd_sr_util.get_customer_id(owl.customer_id),
   29,
   msd_sr_util.get_all_org_pk,
   33,
   msd_sr_util.get_all_scs_pk,
   32,
   msd_sr_util.get_all_rep_pk,
   owl.best_forecast_amount * msd_sr_util.convert_global_amt(owl.currency_code,glp.end_date) best_forecast_amount,
   owl.forecast_amount * msd_sr_util.convert_global_amt(owl.currency_code,glp.end_date) forecast_amount,
   owl.worst_forecast_amount * msd_sr_util.convert_global_amt(owl.currency_code,glp.end_date) worst_forecast_amount,
   owl.amount * msd_sr_util.convert_global_amt(owl.currency_code,glp.end_date) pipeline_amount,
   owl.weighted_amount * msd_sr_util.convert_global_amt(owl.currency_code,glp.end_date) weighted_pipeline_amount,
   glp.start_date,
   glp.end_date,
   afc.forecast_category_name,
   decode(glp.period_type, 'Month', 3, 'Quarter', 4, 'Year', 5),
   1,
   tsg.lvl
FROM as_opp_worksheet_lines owl,
   as_forecast_categories afc,
   gl_periods glp,
   msd_setup_parameters cal,
   (select group_id, lvl from
    ((select group_id, 1 Lvl from jtf_rs_groups_b
      minus
      select group_id, 1 from jtf_rs_grp_relations
      where relation_type = 'PARENT_GROUP'
      and (trunc(sysdate) between trunc(start_date_active)
      and nvl(trunc(end_date_active), trunc(sysdate+1)))
      and delete_flag <> 'Y'
     )
     union all
     (select group_id, 2 Lvl from jtf_rs_grp_relations
      where RELATED_GROUP_ID in (
      select group_id from jtf_rs_groups_b
      minus
      select group_id from jtf_rs_grp_relations
      where relation_type = 'PARENT_GROUP'
      and (trunc(sysdate) between trunc(start_date_active)
      and nvl(trunc(end_date_active), trunc(sysdate+1)))
      and delete_flag <> 'Y' )
   ))) tsg
WHERE owl.period_name = glp.period_name
and glp.period_set_name =cal.parameter_value
and cal.parameter_name = 'AS_FORECAST_CALENDAR'
and owl.sales_group_id = tsg.group_id
and nvl(owl.product_category_id, -1) = -1
and owl.status_code = 'SUBMITTED'
and owl.end_date_active is NULL
and owl.salesforce_id is NULL
and owl.forecast_category_id = afc.forecast_category_id
UNION ALL
SELECT distinct 4,
   nvl(edh.parent_id, msd_sr_util.get_null_pk),
   30,
   msd_sr_util.get_all_geo_pk,
   29,
   msd_sr_util.get_all_org_pk,
   33,
   msd_sr_util.get_all_scs_pk,
   32,
   msd_sr_util.get_all_rep_pk,
   pwl.best_forecast_amount * msd_sr_util.convert_global_amt(pwl.currency_code,glp.end_date) best_forecast_amount,
   pwl.forecast_amount * msd_sr_util.convert_global_amt(pwl.currency_code,glp.end_date) forecast_amount,
   pwl.worst_forecast_amount * msd_sr_util.convert_global_amt(pwl.currency_code,glp.end_date) worst_forecast_amount,
   pwl.pipeline_amount * msd_sr_util.convert_global_amt(pwl.currency_code,glp.end_date) pipeline_amount,
   pwl.weighted_pipeline_amount * msd_sr_util.convert_global_amt(pwl.currency_code,glp.end_date) weighted_pipeline_amount,
   glp.start_date,
   glp.end_date,
   afc.forecast_category_name,
   decode(glp.period_type, 'Month', 3, 'Quarter', 4, 'Year', 5),
   2,
   tsg.lvl
FROM mtl_default_category_sets dcs,
    eni_denorm_hierarchies edh,
    as_prod_worksheet_lines pwl,
    as_forecast_categories afc,
    gl_periods glp,
    msd_setup_parameters cal,
   (select group_id, lvl from
    ((select group_id, 1 Lvl from jtf_rs_groups_b
      minus
      select group_id, 1 from jtf_rs_grp_relations
      where relation_type = 'PARENT_GROUP'
      and (trunc(sysdate) between trunc(start_date_active)
      and nvl(trunc(end_date_active), trunc(sysdate+1)))
      and delete_flag <> 'Y'
     )
     union all
     (select group_id, 2 Lvl from jtf_rs_grp_relations
      where RELATED_GROUP_ID in (
      select group_id from jtf_rs_groups_b
      minus
      select group_id from jtf_rs_grp_relations
      where relation_type = 'PARENT_GROUP'
      and (trunc(sysdate) between trunc(start_date_active)
      and nvl(trunc(end_date_active), trunc(sysdate+1)))
      and delete_flag <> 'Y' )
   ))) tsg
WHERE dcs.functional_area_id = 11
and dcs.category_set_id = edh.object_id
and edh.object_type = 'CATEGORY_SET'
and edh.OLTP_flag = 'Y'
and edh.top_node_flag = 'Y'
and edh.child_id = pwl.product_category_id
and pwl.period_name = glp.period_name
and glp.period_set_name =cal.parameter_value
and cal.parameter_name = 'AS_FORECAST_CALENDAR'
and pwl.sales_group_id = tsg.group_id
and pwl.status_code = 'SUBMITTED'
and pwl.end_date_active is NULL
and pwl.salesforce_id is NULL
and pwl.forecast_category_id = afc.forecast_category_id
UNION ALL
SELECT distinct 28,
   msd_sr_util.get_all_prd_pk,
   30,
   msd_sr_util.get_all_geo_pk,
   29,
   msd_sr_util.get_all_org_pk,
   33,
   msd_sr_util.get_all_scs_pk,
   32,
   msd_sr_util.get_all_rep_pk,
   aif.best_forecast_amount * msd_sr_util.convert_global_amt(aif.currency_code,glp.end_date) best_forecast_amount,
   aif.forecast_amount * msd_sr_util.convert_global_amt(aif.currency_code,glp.end_date) forecast_amount,
   aif.worst_forecast_amount * msd_sr_util.convert_global_amt(aif.currency_code,glp.end_date) worst_forecast_amount,
   aif.pipeline_amount * msd_sr_util.convert_global_amt(aif.currency_code,glp.end_date) pipeline_amount,
   aif.weighted_pipeline_amount * msd_sr_util.convert_global_amt(aif.currency_code,glp.end_date) weighted_pipeline_amount,
   glp.start_date,
   glp.end_date,
   afc.forecast_category_name,
   decode(glp.period_type, 'Month', 3, 'Quarter', 4, 'Year', 5),
   3,
   tsg.lvl
FROM as_internal_forecasts aif,
    as_forecast_categories afc,
    gl_periods glp,
    msd_setup_parameters cal,
   (select group_id, lvl from
    ((select group_id, 1 Lvl from jtf_rs_groups_b
      minus
      select group_id, 1 from jtf_rs_grp_relations
      where relation_type = 'PARENT_GROUP'
      and (trunc(sysdate) between trunc(start_date_active)
      and nvl(trunc(end_date_active), trunc(sysdate+1)))
      and delete_flag <> 'Y'
     )
     union all
     (select group_id, 2 Lvl from jtf_rs_grp_relations
      where RELATED_GROUP_ID in (
      select group_id from jtf_rs_groups_b
      minus
      select group_id from jtf_rs_grp_relations
      where relation_type = 'PARENT_GROUP'
      and (trunc(sysdate) between trunc(start_date_active)
      and nvl(trunc(end_date_active), trunc(sysdate+1)))
      and delete_flag <> 'Y' )
   ))) tsg
WHERE aif.period_name = glp.period_name
and glp.period_set_name =cal.parameter_value
and cal.parameter_name = 'AS_FORECAST_CALENDAR'
and aif.sales_group_id = tsg.group_id
and aif.status_code = 'SUBMITTED'
and aif.end_date_active is NULL
and aif.salesforce_id is NULL
and aif.forecast_category_id = afc.forecast_category_id

View Text - HTML Formatted

SELECT 28
, MSD_SR_UTIL.GET_ALL_PRD_PK
, 15
, MSD_SR_UTIL.GET_CUSTOMER_ID(OWL.CUSTOMER_ID)
, 29
, MSD_SR_UTIL.GET_ALL_ORG_PK
, 33
, MSD_SR_UTIL.GET_ALL_SCS_PK
, 32
, MSD_SR_UTIL.GET_ALL_REP_PK
, OWL.BEST_FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(OWL.CURRENCY_CODE
, GLP.END_DATE) BEST_FORECAST_AMOUNT
, OWL.FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(OWL.CURRENCY_CODE
, GLP.END_DATE) FORECAST_AMOUNT
, OWL.WORST_FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(OWL.CURRENCY_CODE
, GLP.END_DATE) WORST_FORECAST_AMOUNT
, OWL.AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(OWL.CURRENCY_CODE
, GLP.END_DATE) PIPELINE_AMOUNT
, OWL.WEIGHTED_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(OWL.CURRENCY_CODE
, GLP.END_DATE) WEIGHTED_PIPELINE_AMOUNT
, GLP.START_DATE
, GLP.END_DATE
, AFC.FORECAST_CATEGORY_NAME
, DECODE(GLP.PERIOD_TYPE
, 'MONTH'
, 3
, 'QUARTER'
, 4
, 'YEAR'
, 5)
, 1
, TSG.LVL FROM AS_OPP_WORKSHEET_LINES OWL
, AS_FORECAST_CATEGORIES AFC
, GL_PERIODS GLP
, MSD_SETUP_PARAMETERS CAL
, (SELECT GROUP_ID
, LVL FROM ((SELECT GROUP_ID
, 1 LVL
FROM JTF_RS_GROUPS_B MINUS SELECT GROUP_ID
, 1
FROM JTF_RS_GRP_RELATIONS
WHERE RELATION_TYPE = 'PARENT_GROUP'
AND (TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE_ACTIVE)
AND NVL(TRUNC(END_DATE_ACTIVE)
, TRUNC(SYSDATE+1)))
AND DELETE_FLAG <> 'Y' ) UNION ALL (SELECT GROUP_ID
, 2 LVL
FROM JTF_RS_GRP_RELATIONS
WHERE RELATED_GROUP_ID IN ( SELECT GROUP_ID
FROM JTF_RS_GROUPS_B MINUS SELECT GROUP_ID
FROM JTF_RS_GRP_RELATIONS
WHERE RELATION_TYPE = 'PARENT_GROUP'
AND (TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE_ACTIVE)
AND NVL(TRUNC(END_DATE_ACTIVE)
, TRUNC(SYSDATE+1)))
AND DELETE_FLAG <> 'Y' ) ))) TSG WHERE OWL.PERIOD_NAME = GLP.PERIOD_NAME AND GLP.PERIOD_SET_NAME =CAL.PARAMETER_VALUE AND CAL.PARAMETER_NAME = 'AS_FORECAST_CALENDAR' AND OWL.SALES_GROUP_ID = TSG.GROUP_ID AND NVL(OWL.PRODUCT_CATEGORY_ID
, -1) = -1 AND OWL.STATUS_CODE = 'SUBMITTED' AND OWL.END_DATE_ACTIVE IS NULL AND OWL.SALESFORCE_ID IS NULL AND OWL.FORECAST_CATEGORY_ID = AFC.FORECAST_CATEGORY_ID UNION ALL SELECT DISTINCT 4
, NVL(EDH.PARENT_ID
, MSD_SR_UTIL.GET_NULL_PK)
, 30
, MSD_SR_UTIL.GET_ALL_GEO_PK
, 29
, MSD_SR_UTIL.GET_ALL_ORG_PK
, 33
, MSD_SR_UTIL.GET_ALL_SCS_PK
, 32
, MSD_SR_UTIL.GET_ALL_REP_PK
, PWL.BEST_FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(PWL.CURRENCY_CODE
, GLP.END_DATE) BEST_FORECAST_AMOUNT
, PWL.FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(PWL.CURRENCY_CODE
, GLP.END_DATE) FORECAST_AMOUNT
, PWL.WORST_FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(PWL.CURRENCY_CODE
, GLP.END_DATE) WORST_FORECAST_AMOUNT
, PWL.PIPELINE_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(PWL.CURRENCY_CODE
, GLP.END_DATE) PIPELINE_AMOUNT
, PWL.WEIGHTED_PIPELINE_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(PWL.CURRENCY_CODE
, GLP.END_DATE) WEIGHTED_PIPELINE_AMOUNT
, GLP.START_DATE
, GLP.END_DATE
, AFC.FORECAST_CATEGORY_NAME
, DECODE(GLP.PERIOD_TYPE
, 'MONTH'
, 3
, 'QUARTER'
, 4
, 'YEAR'
, 5)
, 2
, TSG.LVL FROM MTL_DEFAULT_CATEGORY_SETS DCS
, ENI_DENORM_HIERARCHIES EDH
, AS_PROD_WORKSHEET_LINES PWL
, AS_FORECAST_CATEGORIES AFC
, GL_PERIODS GLP
, MSD_SETUP_PARAMETERS CAL
, (SELECT GROUP_ID
, LVL FROM ((SELECT GROUP_ID
, 1 LVL
FROM JTF_RS_GROUPS_B MINUS SELECT GROUP_ID
, 1
FROM JTF_RS_GRP_RELATIONS
WHERE RELATION_TYPE = 'PARENT_GROUP'
AND (TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE_ACTIVE)
AND NVL(TRUNC(END_DATE_ACTIVE)
, TRUNC(SYSDATE+1)))
AND DELETE_FLAG <> 'Y' ) UNION ALL (SELECT GROUP_ID
, 2 LVL
FROM JTF_RS_GRP_RELATIONS
WHERE RELATED_GROUP_ID IN ( SELECT GROUP_ID
FROM JTF_RS_GROUPS_B MINUS SELECT GROUP_ID
FROM JTF_RS_GRP_RELATIONS
WHERE RELATION_TYPE = 'PARENT_GROUP'
AND (TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE_ACTIVE)
AND NVL(TRUNC(END_DATE_ACTIVE)
, TRUNC(SYSDATE+1)))
AND DELETE_FLAG <> 'Y' ) ))) TSG WHERE DCS.FUNCTIONAL_AREA_ID = 11 AND DCS.CATEGORY_SET_ID = EDH.OBJECT_ID AND EDH.OBJECT_TYPE = 'CATEGORY_SET' AND EDH.OLTP_FLAG = 'Y' AND EDH.TOP_NODE_FLAG = 'Y' AND EDH.CHILD_ID = PWL.PRODUCT_CATEGORY_ID AND PWL.PERIOD_NAME = GLP.PERIOD_NAME AND GLP.PERIOD_SET_NAME =CAL.PARAMETER_VALUE AND CAL.PARAMETER_NAME = 'AS_FORECAST_CALENDAR' AND PWL.SALES_GROUP_ID = TSG.GROUP_ID AND PWL.STATUS_CODE = 'SUBMITTED' AND PWL.END_DATE_ACTIVE IS NULL AND PWL.SALESFORCE_ID IS NULL AND PWL.FORECAST_CATEGORY_ID = AFC.FORECAST_CATEGORY_ID UNION ALL SELECT DISTINCT 28
, MSD_SR_UTIL.GET_ALL_PRD_PK
, 30
, MSD_SR_UTIL.GET_ALL_GEO_PK
, 29
, MSD_SR_UTIL.GET_ALL_ORG_PK
, 33
, MSD_SR_UTIL.GET_ALL_SCS_PK
, 32
, MSD_SR_UTIL.GET_ALL_REP_PK
, AIF.BEST_FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(AIF.CURRENCY_CODE
, GLP.END_DATE) BEST_FORECAST_AMOUNT
, AIF.FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(AIF.CURRENCY_CODE
, GLP.END_DATE) FORECAST_AMOUNT
, AIF.WORST_FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(AIF.CURRENCY_CODE
, GLP.END_DATE) WORST_FORECAST_AMOUNT
, AIF.PIPELINE_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(AIF.CURRENCY_CODE
, GLP.END_DATE) PIPELINE_AMOUNT
, AIF.WEIGHTED_PIPELINE_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(AIF.CURRENCY_CODE
, GLP.END_DATE) WEIGHTED_PIPELINE_AMOUNT
, GLP.START_DATE
, GLP.END_DATE
, AFC.FORECAST_CATEGORY_NAME
, DECODE(GLP.PERIOD_TYPE
, 'MONTH'
, 3
, 'QUARTER'
, 4
, 'YEAR'
, 5)
, 3
, TSG.LVL FROM AS_INTERNAL_FORECASTS AIF
, AS_FORECAST_CATEGORIES AFC
, GL_PERIODS GLP
, MSD_SETUP_PARAMETERS CAL
, (SELECT GROUP_ID
, LVL FROM ((SELECT GROUP_ID
, 1 LVL
FROM JTF_RS_GROUPS_B MINUS SELECT GROUP_ID
, 1
FROM JTF_RS_GRP_RELATIONS
WHERE RELATION_TYPE = 'PARENT_GROUP'
AND (TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE_ACTIVE)
AND NVL(TRUNC(END_DATE_ACTIVE)
, TRUNC(SYSDATE+1)))
AND DELETE_FLAG <> 'Y' ) UNION ALL (SELECT GROUP_ID
, 2 LVL
FROM JTF_RS_GRP_RELATIONS
WHERE RELATED_GROUP_ID IN ( SELECT GROUP_ID
FROM JTF_RS_GROUPS_B MINUS SELECT GROUP_ID
FROM JTF_RS_GRP_RELATIONS
WHERE RELATION_TYPE = 'PARENT_GROUP'
AND (TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE_ACTIVE)
AND NVL(TRUNC(END_DATE_ACTIVE)
, TRUNC(SYSDATE+1)))
AND DELETE_FLAG <> 'Y' ) ))) TSG WHERE AIF.PERIOD_NAME = GLP.PERIOD_NAME AND GLP.PERIOD_SET_NAME =CAL.PARAMETER_VALUE AND CAL.PARAMETER_NAME = 'AS_FORECAST_CALENDAR' AND AIF.SALES_GROUP_ID = TSG.GROUP_ID AND AIF.STATUS_CODE = 'SUBMITTED' AND AIF.END_DATE_ACTIVE IS NULL AND AIF.SALESFORCE_ID IS NULL AND AIF.FORECAST_CATEGORY_ID = AFC.FORECAST_CATEGORY_ID