VIEW: APPS.AS_FRCST_MGR_OPP_LINES_V

Source

View Text - Preformatted

SELECT OP.opp_description ,OP.opp_number ,OP.product_name ,OP.product_category_name ,OP.customer_name ,STT.meaning status ,STG.name sales_stage ,OP.win_probability ,OP.forecast_date ,DECODE(OP.subord_type, 'G', RES2.source_name, RES.source_name) direct_report ,RES.source_name ,SGTL.group_name ,OP.opp_worst_forecast_amount ,OP.opp_forecast_amount ,OP.opp_best_forecast_amount ,OP.pipeline_amount ,OP.Weighted_amount ,OP.Won_amount ,OP.forecast_category_id ,OP.credit_type_id ,OP.to_currency ,OP.period_name ,OP.period_set_name ,OP.parent_group_id sales_group_id FROM jtf_rs_resource_extns RES ,jtf_rs_resource_extns RES2 ,jtf_rs_group_members GRPM ,jtf_rs_roles_b ROL ,jtf_rs_role_relations REL ,as_sales_stages_all_tl STG ,as_statuses_tl STT ,jtf_rs_groups_tl SGTL ,( SELECT 'G' subord_type ,SCD.salesforce_id salesforce_id ,SCD.sales_group_id salesgroup_id ,SGD.group_id drct_salesgroup_id ,SCD.opp_description ,SCD.lead_number opp_number ,SCD.customer_name ,NVL(MTL.description, MCT.description) product_name ,MCT1.description product_category_name ,SCD.win_probability ,SCD.forecast_date ,NVL(SCD.opp_worst_forecast_amount * R.conversion_rate, 0) opp_worst_forecast_amount ,NVL(SCD.opp_forecast_amount * R.conversion_rate, 0) opp_forecast_amount ,NVL(SCD.opp_best_forecast_amount * R.conversion_rate, 0) opp_best_forecast_amount ,SCD.sales_credit_amount * R.conversion_rate pipeline_amount ,DECODE(SCD.win_loss_indicator, 'W', SCD.sales_credit_amount, SCD.weighted_amount) * R.conversion_rate weighted_amount ,SCD.won_amount * R.conversion_rate won_amount ,SCD.status_code ,SCD.sales_stage_id ,AFS.forecast_category_id ,SCD.credit_type_id ,R.to_currency ,R.period_name ,R.period_set_name ,SGD.parent_group_id FROM as_fst_sales_categories AFS ,eni_prod_denorm_hrchy_v EPD ,as_sales_credits_denorm SCD ,as_period_rates R ,mtl_categories_tl MCT ,mtl_system_items_tl MTL ,mtl_categories_tl MCT1 ,jtf_rs_groups_denorm SGD ,jtf_rs_groups_denorm SGD2 WHERE TRUNC(sysdate) BETWEEN AFS.start_date_active AND NVL(AFS.end_date_active,TRUNC(sysdate)) AND AFS.product_category_id = EPD.parent_id AND EPD.child_id = SCD.product_category_id AND SCD.forecast_rollup_flag = 'Y' AND R.from_currency = SCD.currency_code AND SCD.product_category_id = MCT.category_id AND SCD.item_id = MTL.inventory_item_id(+) AND SCD.organization_id = MTL.organization_id(+) AND MCT.language=USERENV('LANG') AND MTL.language(+)=USERENV('LANG') AND SGD.immediate_parent_flag = 'Y' AND TRUNC(sysdate) between SGD.start_date_active AND NVL(SGD.end_date_active, TRUNC(sysdate)) AND SGD2.parent_group_id = SGD.group_id AND SCD.sales_group_id = SGD2.group_id AND TRUNC(sysdate) between SGD2.start_date_active AND NVL(SGD2.end_date_active, TRUNC(sysdate)) AND AFS.product_category_id = MCT1.category_id AND MCT1.language = USERENV('LANG') UNION ALL SELECT 'R' subord_type ,SCD.salesforce_id salesforce_id ,SCD.sales_group_id salesgroup_id ,SCD.sales_group_id drct_sales_group_id ,SCD.opp_description ,SCD.lead_number opp_number ,SCD.customer_name ,NVL(MTL.description, MCT.description) product_name ,MCT1.description product_category_name ,SCD.win_probability ,SCD.forecast_date ,NVL(SCD.opp_worst_forecast_amount * R.conversion_rate, 0) opp_worst_forecast_amount ,NVL(SCD.opp_forecast_amount * R.conversion_rate, 0) opp_forecast_amount ,NVL(SCD.opp_best_forecast_amount * R.conversion_rate, 0) opp_best_forecast_amount ,SCD.sales_credit_amount * R.conversion_rate pipeline_amount ,DECODE(SCD.win_loss_indicator, 'W', SCD.sales_credit_amount, SCD.weighted_amount) * R.conversion_rate weighted_amount ,SCD.won_amount * R.conversion_rate won_amount ,SCD.status_code ,SCD.sales_stage_id ,AFS.forecast_category_id ,SCD.credit_type_id ,R.to_currency ,R.period_name ,R.period_set_name ,SCD.sales_group_id parent_group_id FROM as_fst_sales_categories AFS ,eni_prod_denorm_hrchy_v EPD ,as_sales_credits_denorm SCD ,as_period_rates R ,mtl_categories_tl MCT ,mtl_categories_tl MCT1 ,mtl_system_items_tl MTL WHERE TRUNC(sysdate) BETWEEN AFS.start_date_active AND NVL(AFS.end_date_active,TRUNC(sysdate)) AND AFS.product_category_id = EPD.parent_id AND EPD.child_id = SCD.product_category_id AND SCD.forecast_rollup_flag = 'Y' AND R.from_currency = SCD.currency_code AND SCD.product_category_id = MCT.category_id AND SCD.item_id = MTL.inventory_item_id(+) AND SCD.organization_id = MTL.organization_id(+) AND MCT.language=USERENV('LANG') AND MTL.language(+)=USERENV('LANG') AND AFS.product_category_id = MCT1.category_id AND MCT1.language = USERENV('LANG') ) OP WHERE OP.drct_salesgroup_id = GRPM.group_id AND GRPM.group_member_id = REL.role_resource_id AND NVL(GRPM.delete_flag,'N') = 'N' AND REL.role_resource_type = 'RS_GROUP_MEMBER' AND NVL(REL.delete_flag,'N') = 'N' AND trunc(sysdate) between REL.start_date_active and NVL(REL.end_date_active, trunc(sysdate)) AND REL.role_id = ROL.role_id AND ROL.manager_flag = 'Y' AND ROL.active_flag = 'Y' AND ROL.role_type_code = 'SALES' AND RES2.resource_id = GRPM.resource_id AND OP.salesforce_id = RES.resource_id AND STG.language(+) = USERENV('LANG') AND OP.sales_stage_id = STG.sales_stage_id(+) AND STT.language = USERENV('LANG') AND OP.status_code = STT.status_code AND OP.salesgroup_id = SGTL.group_id AND SGTL.language = USERENV('LANG')

View Text - HTML Formatted

SELECT OP.OPP_DESCRIPTION
, OP.OPP_NUMBER
, OP.PRODUCT_NAME
, OP.PRODUCT_CATEGORY_NAME
, OP.CUSTOMER_NAME
, STT.MEANING STATUS
, STG.NAME SALES_STAGE
, OP.WIN_PROBABILITY
, OP.FORECAST_DATE
, DECODE(OP.SUBORD_TYPE
, 'G'
, RES2.SOURCE_NAME
, RES.SOURCE_NAME) DIRECT_REPORT
, RES.SOURCE_NAME
, SGTL.GROUP_NAME
, OP.OPP_WORST_FORECAST_AMOUNT
, OP.OPP_FORECAST_AMOUNT
, OP.OPP_BEST_FORECAST_AMOUNT
, OP.PIPELINE_AMOUNT
, OP.WEIGHTED_AMOUNT
, OP.WON_AMOUNT
, OP.FORECAST_CATEGORY_ID
, OP.CREDIT_TYPE_ID
, OP.TO_CURRENCY
, OP.PERIOD_NAME
, OP.PERIOD_SET_NAME
, OP.PARENT_GROUP_ID SALES_GROUP_ID
FROM JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_RESOURCE_EXTNS RES2
, JTF_RS_GROUP_MEMBERS GRPM
, JTF_RS_ROLES_B ROL
, JTF_RS_ROLE_RELATIONS REL
, AS_SALES_STAGES_ALL_TL STG
, AS_STATUSES_TL STT
, JTF_RS_GROUPS_TL SGTL
, ( SELECT 'G' SUBORD_TYPE
, SCD.SALESFORCE_ID SALESFORCE_ID
, SCD.SALES_GROUP_ID SALESGROUP_ID
, SGD.GROUP_ID DRCT_SALESGROUP_ID
, SCD.OPP_DESCRIPTION
, SCD.LEAD_NUMBER OPP_NUMBER
, SCD.CUSTOMER_NAME
, NVL(MTL.DESCRIPTION
, MCT.DESCRIPTION) PRODUCT_NAME
, MCT1.DESCRIPTION PRODUCT_CATEGORY_NAME
, SCD.WIN_PROBABILITY
, SCD.FORECAST_DATE
, NVL(SCD.OPP_WORST_FORECAST_AMOUNT * R.CONVERSION_RATE
, 0) OPP_WORST_FORECAST_AMOUNT
, NVL(SCD.OPP_FORECAST_AMOUNT * R.CONVERSION_RATE
, 0) OPP_FORECAST_AMOUNT
, NVL(SCD.OPP_BEST_FORECAST_AMOUNT * R.CONVERSION_RATE
, 0) OPP_BEST_FORECAST_AMOUNT
, SCD.SALES_CREDIT_AMOUNT * R.CONVERSION_RATE PIPELINE_AMOUNT
, DECODE(SCD.WIN_LOSS_INDICATOR
, 'W'
, SCD.SALES_CREDIT_AMOUNT
, SCD.WEIGHTED_AMOUNT) * R.CONVERSION_RATE WEIGHTED_AMOUNT
, SCD.WON_AMOUNT * R.CONVERSION_RATE WON_AMOUNT
, SCD.STATUS_CODE
, SCD.SALES_STAGE_ID
, AFS.FORECAST_CATEGORY_ID
, SCD.CREDIT_TYPE_ID
, R.TO_CURRENCY
, R.PERIOD_NAME
, R.PERIOD_SET_NAME
, SGD.PARENT_GROUP_ID
FROM AS_FST_SALES_CATEGORIES AFS
, ENI_PROD_DENORM_HRCHY_V EPD
, AS_SALES_CREDITS_DENORM SCD
, AS_PERIOD_RATES R
, MTL_CATEGORIES_TL MCT
, MTL_SYSTEM_ITEMS_TL MTL
, MTL_CATEGORIES_TL MCT1
, JTF_RS_GROUPS_DENORM SGD
, JTF_RS_GROUPS_DENORM SGD2
WHERE TRUNC(SYSDATE) BETWEEN AFS.START_DATE_ACTIVE
AND NVL(AFS.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND AFS.PRODUCT_CATEGORY_ID = EPD.PARENT_ID
AND EPD.CHILD_ID = SCD.PRODUCT_CATEGORY_ID
AND SCD.FORECAST_ROLLUP_FLAG = 'Y'
AND R.FROM_CURRENCY = SCD.CURRENCY_CODE
AND SCD.PRODUCT_CATEGORY_ID = MCT.CATEGORY_ID
AND SCD.ITEM_ID = MTL.INVENTORY_ITEM_ID(+)
AND SCD.ORGANIZATION_ID = MTL.ORGANIZATION_ID(+)
AND MCT.LANGUAGE=USERENV('LANG')
AND MTL.LANGUAGE(+)=USERENV('LANG')
AND SGD.IMMEDIATE_PARENT_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN SGD.START_DATE_ACTIVE
AND NVL(SGD.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND SGD2.PARENT_GROUP_ID = SGD.GROUP_ID
AND SCD.SALES_GROUP_ID = SGD2.GROUP_ID
AND TRUNC(SYSDATE) BETWEEN SGD2.START_DATE_ACTIVE
AND NVL(SGD2.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND AFS.PRODUCT_CATEGORY_ID = MCT1.CATEGORY_ID
AND MCT1.LANGUAGE = USERENV('LANG') UNION ALL SELECT 'R' SUBORD_TYPE
, SCD.SALESFORCE_ID SALESFORCE_ID
, SCD.SALES_GROUP_ID SALESGROUP_ID
, SCD.SALES_GROUP_ID DRCT_SALES_GROUP_ID
, SCD.OPP_DESCRIPTION
, SCD.LEAD_NUMBER OPP_NUMBER
, SCD.CUSTOMER_NAME
, NVL(MTL.DESCRIPTION
, MCT.DESCRIPTION) PRODUCT_NAME
, MCT1.DESCRIPTION PRODUCT_CATEGORY_NAME
, SCD.WIN_PROBABILITY
, SCD.FORECAST_DATE
, NVL(SCD.OPP_WORST_FORECAST_AMOUNT * R.CONVERSION_RATE
, 0) OPP_WORST_FORECAST_AMOUNT
, NVL(SCD.OPP_FORECAST_AMOUNT * R.CONVERSION_RATE
, 0) OPP_FORECAST_AMOUNT
, NVL(SCD.OPP_BEST_FORECAST_AMOUNT * R.CONVERSION_RATE
, 0) OPP_BEST_FORECAST_AMOUNT
, SCD.SALES_CREDIT_AMOUNT * R.CONVERSION_RATE PIPELINE_AMOUNT
, DECODE(SCD.WIN_LOSS_INDICATOR
, 'W'
, SCD.SALES_CREDIT_AMOUNT
, SCD.WEIGHTED_AMOUNT) * R.CONVERSION_RATE WEIGHTED_AMOUNT
, SCD.WON_AMOUNT * R.CONVERSION_RATE WON_AMOUNT
, SCD.STATUS_CODE
, SCD.SALES_STAGE_ID
, AFS.FORECAST_CATEGORY_ID
, SCD.CREDIT_TYPE_ID
, R.TO_CURRENCY
, R.PERIOD_NAME
, R.PERIOD_SET_NAME
, SCD.SALES_GROUP_ID PARENT_GROUP_ID
FROM AS_FST_SALES_CATEGORIES AFS
, ENI_PROD_DENORM_HRCHY_V EPD
, AS_SALES_CREDITS_DENORM SCD
, AS_PERIOD_RATES R
, MTL_CATEGORIES_TL MCT
, MTL_CATEGORIES_TL MCT1
, MTL_SYSTEM_ITEMS_TL MTL
WHERE TRUNC(SYSDATE) BETWEEN AFS.START_DATE_ACTIVE
AND NVL(AFS.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND AFS.PRODUCT_CATEGORY_ID = EPD.PARENT_ID
AND EPD.CHILD_ID = SCD.PRODUCT_CATEGORY_ID
AND SCD.FORECAST_ROLLUP_FLAG = 'Y'
AND R.FROM_CURRENCY = SCD.CURRENCY_CODE
AND SCD.PRODUCT_CATEGORY_ID = MCT.CATEGORY_ID
AND SCD.ITEM_ID = MTL.INVENTORY_ITEM_ID(+)
AND SCD.ORGANIZATION_ID = MTL.ORGANIZATION_ID(+)
AND MCT.LANGUAGE=USERENV('LANG')
AND MTL.LANGUAGE(+)=USERENV('LANG')
AND AFS.PRODUCT_CATEGORY_ID = MCT1.CATEGORY_ID
AND MCT1.LANGUAGE = USERENV('LANG') ) OP
WHERE OP.DRCT_SALESGROUP_ID = GRPM.GROUP_ID
AND GRPM.GROUP_MEMBER_ID = REL.ROLE_RESOURCE_ID
AND NVL(GRPM.DELETE_FLAG
, 'N') = 'N'
AND REL.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER'
AND NVL(REL.DELETE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) BETWEEN REL.START_DATE_ACTIVE
AND NVL(REL.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND REL.ROLE_ID = ROL.ROLE_ID
AND ROL.MANAGER_FLAG = 'Y'
AND ROL.ACTIVE_FLAG = 'Y'
AND ROL.ROLE_TYPE_CODE = 'SALES'
AND RES2.RESOURCE_ID = GRPM.RESOURCE_ID
AND OP.SALESFORCE_ID = RES.RESOURCE_ID
AND STG.LANGUAGE(+) = USERENV('LANG')
AND OP.SALES_STAGE_ID = STG.SALES_STAGE_ID(+)
AND STT.LANGUAGE = USERENV('LANG')
AND OP.STATUS_CODE = STT.STATUS_CODE
AND OP.SALESGROUP_ID = SGTL.GROUP_ID
AND SGTL.LANGUAGE = USERENV('LANG')