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