View: AS_VIEW_FORECAST_V

View Text

SELECT ASQ.PERIOD_NAME
, AF.FREEZE_PERIOD
, ASQ.SALES_QUOTA_ID
, ASQ.SALES_GROUP_ID
, ASG.NAME SALES_GROUP
, ASQ.SALESFORCE_ID
, ASQ.EMPLOYEE_PERSON_ID EMPLOYEE_PERSON_ID
, PP.FIRST_NAME EMPLOYEE_FIRST_NAME
, PP.LAST_NAME EMPLOYEE_LAST_NAME
, ASQ.INTEREST_TYPE_ID
, AIT.INTEREST_TYPE
, ASQ.PRIMARY_INTEREST_CODE_ID
, AIC1.CODE PRIMARY_INTEREST_CODE
, ASQ.SECONDARY_INTEREST_CODE_ID
, AIC2.CODE SECONDARY_INTEREST_CODE
, ASQ.ITEM_ID INVENTORY_ITEM_ID
, ASQ.REVENUE_FLAG
, DECODE(AF.FREEZE_PERIOD
, NULL
, ASQ.QUOTA_AMOUNT
, AF.SALES_QUOTA_AMOUNT)
, AF.CURRENCY_CODE
, AF.FORECAST_AMOUNT
, AF.UPSIDE_AMOUNT
, DECODE(AF.FREEZE_PERIOD
, NULL
, AS_LEADS_PKG.GET_PIPELINE_AMOUNT( ASQ.INTEREST_TYPE_ID
, ASQ.PRIMARY_INTEREST_CODE_ID
, ASQ.SECONDARY_INTEREST_CODE_ID
, ASQ.ITEM_ID
, ASQ.SALESFORCE_ID
, ASQ.SALES_GROUP_ID
, ASQ.PERIOD_NAME
, NULL
, NULL
, ASQ.REVENUE_FLAG
, 'WON'
, NULL
, 'FALSE')
, AF.LEAD_WON_AMOUNT) LEAD_WON_AMOUNT
, DECODE(AF.FREEZE_PERIOD
, NULL
, AS_LEADS_PKG.GET_PIPELINE_AMOUNT( ASQ.INTEREST_TYPE_ID
, ASQ.PRIMARY_INTEREST_CODE_ID
, ASQ.SECONDARY_INTEREST_CODE_ID
, ASQ.ITEM_ID
, ASQ.SALESFORCE_ID
, ASQ.SALES_GROUP_ID
, ASQ.PERIOD_NAME
, NULL
, NULL
, ASQ.REVENUE_FLAG
, 'FORECASTED'
, NULL
, 'FALSE')
, AF.LEAD_FORECAST_AMOUNT) LEAD_FORECAST_AMOUNT
, DECODE(AF.FREEZE_PERIOD
, NULL
, AS_LEADS_PKG.GET_PIPELINE_AMOUNT( ASQ.INTEREST_TYPE_ID
, ASQ.PRIMARY_INTEREST_CODE_ID
, ASQ.SECONDARY_INTEREST_CODE_ID
, ASQ.ITEM_ID
, ASQ.SALESFORCE_ID
, ASQ.SALES_GROUP_ID
, ASQ.PERIOD_NAME
, NULL
, NULL
, ASQ.REVENUE_FLAG
, 'UPSIDE'
, NULL
, 'FALSE')
, AF.LEAD_UPSIDE_AMOUNT) LEAD_UPSIDE_AMOUNT
, AF.UOM_CODE
, AF.FORECAST_QUANTITY
, AF.UPSIDE_QUANTITY
, DECODE(AF.FREEZE_PERIOD
, NULL
, AS_LEADS_PKG.GET_PIPELINE_QUANTITY( ASQ.INTEREST_TYPE_ID
, ASQ.PRIMARY_INTEREST_CODE_ID
, ASQ.SECONDARY_INTEREST_CODE_ID
, ASQ.ITEM_ID
, ASQ.SALESFORCE_ID
, ASQ.SALES_GROUP_ID
, ASQ.PERIOD_NAME
, NULL
, NULL
, 'WON'
, 'FALSE')
, AF.LEAD_WON_QUANTITY) LEAD_WON_QUANTITY
, DECODE(AF.FREEZE_PERIOD
, NULL
, AS_LEADS_PKG.GET_PIPELINE_QUANTITY( ASQ.INTEREST_TYPE_ID
, ASQ.PRIMARY_INTEREST_CODE_ID
, ASQ.SECONDARY_INTEREST_CODE_ID
, ASQ.ITEM_ID
, ASQ.SALESFORCE_ID
, ASQ.SALES_GROUP_ID
, ASQ.PERIOD_NAME
, NULL
, NULL
, 'FORECASTED'
, 'FALSE')
, AF.LEAD_FORECAST_QUANTITY) LEAD_FORECAST_QUANTITY
, DECODE(AF.FREEZE_PERIOD
, NULL
, AS_LEADS_PKG.GET_PIPELINE_QUANTITY( ASQ.INTEREST_TYPE_ID
, ASQ.PRIMARY_INTEREST_CODE_ID
, ASQ.SECONDARY_INTEREST_CODE_ID
, ASQ.ITEM_ID
, ASQ.SALESFORCE_ID
, ASQ.SALES_GROUP_ID
, ASQ.PERIOD_NAME
, NULL
, NULL
, 'UPSIDE'
, 'FALSE')
, AF.LEAD_UPSIDE_QUANTITY) LEAD_UPSIDE_QUANTITY
, AF.FORECAST_ID
, AF.CREATION_DATE FORECAST_DATE
, AF.LAST_UPDATED_BY
, AF.CREATED_BY
, AF.ORIGINAL_FORECAST_ID
, AF.MANAGER_REVIEW_FLAG
, AF.MANAGER_REVIEW_DATE
FROM AS_INTEREST_TYPES_VL AIT
, AS_INTEREST_CODES_VL AIC1
, AS_INTEREST_CODES_VL AIC2
, PER_ALL_PEOPLE_F PP
, AS_SALES_QUOTAS ASQ
, AS_FORECASTS AF
, AS_SALES_GROUPS ASG
WHERE AIT.INTEREST_TYPE_ID(+) = ASQ.INTEREST_TYPE_ID
AND AIC1.INTEREST_CODE_ID(+) = ASQ.PRIMARY_INTEREST_CODE_ID
AND AIC2.INTEREST_CODE_ID(+) = ASQ.SECONDARY_INTEREST_CODE_ID
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE(+)
AND NVL(PP.EFFECTIVE_END_DATE(+)
, SYSDATE + 1)
AND PP.PERSON_ID(+) = ASQ.EMPLOYEE_PERSON_ID
AND AF.SALES_QUOTA_ID(+) = ASQ.SALES_QUOTA_ID
AND ASG.SALES_GROUP_ID = ASQ.SALES_GROUP_ID

Columns