Object Overview and Relationships

Description:

The Product Forecast object is designed to hold forecasted financial, volumetric, and probability-weighted metrics for specific products associated with opportunities. This object facilitates forecasting and revenue prediction by allowing users to aggregate data at the product-opportunity level. 

Related Objects:

• Opportunity: The Product Forecast object is associated with the Opportunity object via a Master-Detail relationship. This enables consolidated reporting and tracking of forecasted metrics across different opportunities.

• Product: There is a Lookup relationship with the Product object, tying each forecast to a product. This supports detailed product-level forecasting and analysis.

• Opportunity Realized Sales Year: While not directly referenced, historical data from Opportunity Realized Sales Year can inform or validate forecasts, providing a comparison of past actuals versus forecasted metrics.

Automation

Flow Name: Create Product Forecasts

Purpose:
This flow creates a forecast record for each Opportunity Line Item added to an opportunity, ensuring that forecasts are generated and updated based on specific business rules.

Trigger

• Type: Record-Triggered Flow
• Event: Triggered each time an Opportunity Product record is created.
• Entry Conditions: None – the flow runs for all Opportunity Products and executes immediately.

Logic and Flow Steps

1. Retrieve Existing Forecasts

   • The flow first checks if any forecast records already exist for the Opportunity.
   • It retrieves records from Product Forecast where:
     • SLV_Product__c equals $Record > Product ID
     • SLV_Opportunity__c equals $Record > Opportunity ID

2. Check for Existing Forecasts

   • Decision: If forecast records exist, the flow ends.
   • If No Forecasts Exist: The flow proceeds to a counter-based decision to manage forecast creation.

3. Counter Logic and Forecast Creation

   • Counter Check (Counter < 5):

     • If fewer than 5 forecasts exist, the flow:
       • Assigns values to all relevant fields in the forecast record.
       • Sets the Year field to: 

TEXT(YEAR(DATEVALUE(DATETIMEVALUE({!SLV7_OPP_Targeted_first_delivery_date}))) + {!counter})

1. • Counter Check (Counter ≥ 5):

     • When the counter reaches 5, it stops assigning values and creates the forecast records with the previously assigned field values.

2. Field Assignments (Set Values)

   • The following fields are populated from the Opportunity Line Item to the Forecast record:
     • Currency ISO Code
     • Opportunity
     • Unit of Measure
     • Opportunity Product ID
     • Product
     • CM Calculation Method
     • Volume
     • Price
     • Contribution Margin (Amount) per UoM
     • Contribution Margin
     • Year

FX_Price_EUR__c

• Event: Before Insert, Before Update
• Action: Update FX price in EUR
• Purpose: Converts SLV_Price__c to EUR based on the currency exchange rate (CurrencyIsoCode to EUR) for accurate FX pricing.

2. FX_Contribution_Margin_Amount_UoM_EUR__c

• Event: Before Insert, Before Update
• Action: Update FX contribution margin in EUR
• Purpose: Converts SLV7_PF_Contribution_Margin_Amount_UoM__c to EUR using CurrencyIsoCode to ensure accurate FX calculations for contribution margin.

3. SLV_Product_Forcast_Rollup__c

• Event: N/A (commented out)
• Action: Update rollup flag on Opportunity (currently inactive)
• Purpose: Intended to update a rollup flag (SLV_Product_Forcast_Rollup__c) on the associated Opportunity record to indicate the existence or total of related forecasts, but this feature is currently commented out.

4. Opportunity Realized Sales Year (ORSY) Related Fields

The following fields are used to manage ORSY records in synchronization with forecast records:

• Product_Forecast__c

  • Event: After Insert, Before Delete
  • Action: Link or delete ORSY with forecast
  • Purpose: Links ORSY records to the forecast record on insert, or deletes them if the forecast is removed, ensuring data integrity.

• Opportunity_Product_Id__c

  • Event: After Insert
  • Action: Link ORSY with Opportunity Product
  • Purpose: Links ORSY records to Opportunity Products using SLV_Opportunity_Product_Id__c, allowing forecast data alignment with specific products.

• Opportunity__c

  • Event: After Insert
  • Action: Link ORSY with Opportunity
  • Purpose: Links ORSY records to the Opportunity associated with the forecast, consolidating forecast data at the Opportunity level.

• Year__c

  • Event: After Insert
  • Action: Set ORSY year based on forecast year
  • Purpose: Sets the Year__c field in ORSY records based on SLV_Year__c from the forecast, aligning each ORSY record with the correct forecasted period.

• Product__c

  • Event: After Insert
  • Action: Link ORSY with product
  • Purpose: Links ORSY records to the product in the forecast (SLV_Product__c), ensuring forecast data is product-specific.

• CurrencyIsoCode

  • Event: After Insert
  • Action: Set currency for ORSY
  • Purpose: Transfers the CurrencyIsoCode from the forecast to ORSY records, ensuring currency consistency across forecasted sales data.

Enhancements 

Oil & Gas Forecast Year Creation - 

• For O&G Opportunity rollout project, change was made in a way Forecast records are generated. 

New opportunity products created for O&G Opportunities will generate Forecast records but these records will not be pre-filled for all years. Only first year will be pre-filled with values from Opportunity Product. The rest will be set to null. 

This was done due to nature of O&G business and inability to know in advance. Hence these values stay blank for later years.

• This change applies only for O&G Opportunities. 

Automated UOM-to-Metric Tons Conversion

• Purpose: Standardize volume representation across all records.
• Logic: Converts values from supported UOMs to metric tons using a formula based on the UOM and associated conversion factors.
• Key Formula Logic:

CASE(SLV5_PF_Unit_of_Measure__c,
    'T', SLV_Volume__c / 1,
    'KG', SLV_Volume__c / 1000,
    'Sh. T', SLV_Volume__c / 1.1023,
    'LB', SLV_Volume__c / 2204.6,
    'L', SLV_Volume__c * OGR_Conversion_Factor__c,
    'GAL', SLV_Volume__c * OGR_Conversion_Factor__c,
    0
)

Flow Name: Opportunity Products - Convert UOMs

Type: Triggered Flow

Key Features

 1. Integration Across Objects

• Opportunity Line Items (OLI):
  • Field Name: SLV5_Volume_in_T__c
  • Stores the converted volume for each line item based on the selected UOM.
• Product Forecasts:
  • Field Name: SLV5_Pivot_volume_in_T__c
  • Extends the conversion logic from OLI, ensuring compatibility and consistency.

2. Key Data Dependencies

• Conversion Factors:
  • Stored in OGR_Conversion_Factor__c (Opportunity Product).
  • Dynamically updated when alternative UOMs or density values are modified.
• Lookup Field:
  • New lookup field from Product Forecast to Opportunity Line Item ensures consistent reuse of existing conversion factors.

3. Reporting and Analytics

• Field Accessibility:
  • Not displayed on record page layouts by default.
  • Included in custom and standard report types for visibility in reporting.
• Use Cases:
  • Analyze product volumes in metric tons for forecasting and decision-making.
  • Ensure consistent reporting across regions and product lines.

4. Handling Missing Data

• Conversion logic accounts for scenarios where:
  • No valid conversion factor is available (sets value to 0).
  • Selected UOM is unsupported (marks records for review or logs errors for follow-up).