View: MSD_SR_SALES_FCST_V

Product:	MSD - Demand Planning
Description:	This is the Source View for Sales Forecast. This view would be applied to an Oracle Applications 11i instance from which the Sales Forecast information is to be retrieved.
Implementation/DBA Data:	APPS.MSD_SR_SALES_FCST_V

View Text

SELECT DISTINCT 28
, MSD_SR_UTIL.GET_ALL_PRD_PK
, 15
, NVL(OWL.CUSTOMER_ID
, MSD_SR_UTIL.GET_NULL_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
, 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
FROM AS_OPP_WORKSHEET_LINES OWL
, AS_FORECAST_CATEGORIES AFC
, GL_PERIODS GLP
, MSD_SETUP_PARAMETERS CAL
, (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 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(PWL.INTEREST_TYPE_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
FROM AS_PROD_WORKSHEET_LINES PWL
, AS_FORECAST_CATEGORIES AFC
, GL_PERIODS GLP
, MSD_SETUP_PARAMETERS CAL
, (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 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
FROM AS_INTERNAL_FORECASTS AIF
, AS_FORECAST_CATEGORIES AFC
, GL_PERIODS GLP
, MSD_SETUP_PARAMETERS CAL
, (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

Columns