Product: | AS - Sales Foundation |
---|---|
Description: | Sales quotas for forecast view |
Implementation/DBA Data: | Not implemented in this database |
SELECT FORECASTS.FORECAST_ID
, FORECASTS.LAST_UPDATE_DATE
, FORECASTS.LAST_UPDATED_BY
, FORECASTS.CREATION_DATE
, FORECASTS.CREATED_BY
, FORECASTS.LAST_UPDATE_LOGIN
, FORECASTS.FORECAST_AMOUNT
, FORECASTS.UPSIDE_AMOUNT
, FORECASTS.UOM_CODE
, FORECASTS.FORECAST_QUANTITY
, FORECASTS.UPSIDE_QUANTITY
, FORECASTS.MANAGER_REVIEW_FLAG
, FORECASTS.MANAGER_REVIEW_DATE
, FORECASTS.ORIGINAL_FORECAST_ID
, FORECASTS.ATTRIBUTE_CATEGORY
, FORECASTS.ATTRIBUTE1
, FORECASTS.ATTRIBUTE2
, FORECASTS.ATTRIBUTE3
, FORECASTS.ATTRIBUTE4
, FORECASTS.ATTRIBUTE5
, FORECASTS.ATTRIBUTE6
, FORECASTS.ATTRIBUTE7
, FORECASTS.ATTRIBUTE8
, FORECASTS.ATTRIBUTE9
, FORECASTS.ATTRIBUTE10
, FORECASTS.ATTRIBUTE11
, FORECASTS.ATTRIBUTE12
, FORECASTS.ATTRIBUTE13
, FORECASTS.ATTRIBUTE14
, FORECASTS.ATTRIBUTE15
, FORECASTS.ROWID
, PEOPLE.LAST_NAME || '
, ' || PEOPLE.FIRST_NAME
, PEOPLE.FIRST_NAME
, PEOPLE.LAST_NAME
, SGRP.NAME
, PARTNER.CUSTOMER_NAME
, INTTYPE.INTEREST_TYPE || '/' || INTCODE1.CODE || '/' || INTCODE2.CODE
, INTTYPE.INTEREST_TYPE
, INTCODE1.CODE
, INTCODE2.CODE
, QUOTA.PERIOD_NAME
, FORECASTS.FREEZE_PERIOD
, QUOTA.QUOTA_AMOUNT
, QUOTA.REVENUE_FLAG
, QUOTA.SALES_QUOTA_ID
, AS_LEADS_PKG.GET_PIPELINE_AMOUNT ( QUOTA.INTEREST_TYPE_ID
, QUOTA.PRIMARY_INTEREST_CODE_ID
, QUOTA.SECONDARY_INTEREST_CODE_ID
, QUOTA.ITEM_ID
, QUOTA.SALESFORCE_ID
, QUOTA.SALES_GROUP_ID
, QUOTA.PERIOD_NAME
, NULL
, NULL
, QUOTA.REVENUE_FLAG
, 'WON'
, NULL
, 'FALSE') WON_AMOUNT
, AS_LEADS_PKG.GET_PIPELINE_AMOUNT ( QUOTA.INTEREST_TYPE_ID
, QUOTA.PRIMARY_INTEREST_CODE_ID
, QUOTA.SECONDARY_INTEREST_CODE_ID
, QUOTA.ITEM_ID
, QUOTA.SALESFORCE_ID
, QUOTA.SALES_GROUP_ID
, QUOTA.PERIOD_NAME
, NULL
, NULL
, QUOTA.REVENUE_FLAG
, 'FORECASTED'
, NULL
, 'FALSE') FORECAST_AMOUNT
, AS_LEADS_PKG.GET_PIPELINE_AMOUNT ( QUOTA.INTEREST_TYPE_ID
, QUOTA.PRIMARY_INTEREST_CODE_ID
, QUOTA.SECONDARY_INTEREST_CODE_ID
, QUOTA.ITEM_ID
, QUOTA.SALESFORCE_ID
, QUOTA.SALES_GROUP_ID
, QUOTA.PERIOD_NAME
, NULL
, NULL
, QUOTA.REVENUE_FLAG
, 'UPSIDE'
, NULL
, 'FALSE') UPSIDE_AMOUNT
, AS_LEADS_PKG.GET_PIPELINE_QUANTITY ( QUOTA.INTEREST_TYPE_ID
, QUOTA.PRIMARY_INTEREST_CODE_ID
, QUOTA.SECONDARY_INTEREST_CODE_ID
, QUOTA.ITEM_ID
, QUOTA.SALESFORCE_ID
, QUOTA.SALES_GROUP_ID
, QUOTA.PERIOD_NAME
, NULL
, NULL
, 'WON'
, 'FALSE') WON_QUANTITY
, AS_LEADS_PKG.GET_PIPELINE_QUANTITY ( QUOTA.INTEREST_TYPE_ID
, QUOTA.PRIMARY_INTEREST_CODE_ID
, QUOTA.SECONDARY_INTEREST_CODE_ID
, QUOTA.ITEM_ID
, QUOTA.SALESFORCE_ID
, QUOTA.SALES_GROUP_ID
, QUOTA.PERIOD_NAME
, NULL
, NULL
, 'FORECASTED'
, 'FALSE') FORECAST_QUANTITY
, AS_LEADS_PKG.GET_PIPELINE_QUANTITY ( QUOTA.INTEREST_TYPE_ID
, QUOTA.PRIMARY_INTEREST_CODE_ID
, QUOTA.SECONDARY_INTEREST_CODE_ID
, QUOTA.ITEM_ID
, QUOTA.SALESFORCE_ID
, QUOTA.SALES_GROUP_ID
, QUOTA.PERIOD_NAME
, NULL
, NULL
, 'UPSIDE'
, 'FALSE') UPSIDE_QUANTITY
, QUOTA.SALESFORCE_ID
, QUOTA.EMPLOYEE_PERSON_ID
, QUOTA.PARTNER_CUSTOMER_ID
, QUOTA.PARTNER_ADDRESS_ID
, QUOTA.SALES_GROUP_ID
, QUOTA.INTEREST_TYPE_ID
, QUOTA.PRIMARY_INTEREST_CODE_ID
, QUOTA.SECONDARY_INTEREST_CODE_ID
, QUOTA.ITEM_ID
, QUOTA.ORGANIZATION_ID
FROM AS_FORECASTS FORECASTS
, AS_INTEREST_CODES_VL INTCODE1
, AS_INTEREST_CODES_VL INTCODE2
, PER_ALL_PEOPLE_F PEOPLE
, AS_SALES_GROUPS SGRP
, RA_CUSTOMERS PARTNER
, AS_INTEREST_TYPES_VL INTTYPE
, AS_SALES_QUOTAS QUOTA
WHERE QUOTA.INTEREST_TYPE_ID = INTTYPE.INTEREST_TYPE_ID (+)
AND QUOTA.PRIMARY_INTEREST_CODE_ID = INTCODE1.INTEREST_CODE_ID (+)
AND QUOTA.SECONDARY_INTEREST_CODE_ID = INTCODE2.INTEREST_CODE_ID (+)
AND QUOTA.EMPLOYEE_PERSON_ID = PEOPLE.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN PEOPLE.EFFECTIVE_START_DATE(+)
AND PEOPLE.EFFECTIVE_END_DATE(+)
AND QUOTA.SALES_GROUP_ID = SGRP.SALES_GROUP_ID (+)
AND QUOTA.PARTNER_CUSTOMER_ID = PARTNER.CUSTOMER_ID (+)
AND QUOTA.SALES_QUOTA_ID = FORECASTS.SALES_QUOTA_ID (+)