Supply Chain Planning and Collaboration

Get Involved. Join the Conversation.

Topic

    Venkatesh Periketi
    How to validate if combination exists
    Topic posted May 13, 2019 by Venkatesh PeriketiGold Medal: 3,500+ Points, tagged Demand Management Cloud, How-to, Sales & Operations Planning Cloud 
    119 Views, 4 Comments
    Title:
    How to validate if combination exists
    Content:

    Hi,

    We have defined measure to load external forecast in Demand Management. For this Oracle confirmed that we should have combination exists for which we are loading the external forecast. In order to validate if combination exists or not, I am trying to find out what is the best approach to find the combination using SQL query.

    Your help is appreciated.

    Thanks,

    Venkatesh

    Version:
    R13-19A update

    Comment

     

    • Ramesh Choudhary

      Hi Venkatesh,

      Can you please let us know for which measure you are uploading the forecast?

      Are you talking about the Data combinations?

      You can use the below query to find the combinations of data uploaded.

      select * from MSC_MEASURE_DATA order by creation_date desc

      Thanks,

      Ramesh

       

      • Venkatesh Periketi
        Hi Ramesh, I am talking about matrix data combinations. MSC_MEASURE_DATA Table stores the data leader in particular measure. I am loading the data in Custom measure "XX Extrem al Forever". Thanks
    • Venkatesh Periketi

      Able to figure it out... Please use below queries to verify the combination exists.

      /* Step1: Find Plan ID */

      SELECT plan_id,
             compile_designator
      FROM   fusion.msc_plan_definitions
      WHERE  compile_designator = <PLAN name> --- Note Down Plan_id 300000005582973

      /* Step2: Find Dynamic Table */


      SELECT pds_table_name,
             entity_id
      FROM   fusion.msc_plan_tables
      WHERE  plan_id = <PLAN ID> --Plan_id from Step1
             AND entity_id IN (SELECT granularity_id
                               FROM   msc_measures_vl
                               WHERE  name = '&Measure_name') -- External Forecast1
             AND entity_type IN ('MATRIX');  
             

      /* Step3: Check Granularity Details - Lowest Levels */

      SELECT granularity_id,
             granularity_code
      FROM   msc_granularity
      WHERE  granularity_id IN (SELECT granularity_id
                                FROM   msc_measures_vl
                                WHERE  name = '&Measure_name') --External Forecast1
                                

      /* Step4: Query Dynamic Table - To validate combinations*/

      SELECT DISTINCT MSI.item_name,
                      MP.organization_code,
                      MCS.partner_name,
                      MCS.tp_site_code,
                      MTX.*
      FROM   msc_matrix_102158_dyd MTX, --Use PDS_TABLE_NAME from Step2 and join other tables based on granularity_code
             msc_system_items MSI,
             msc_parameters MP,
             msc_customer_sites MCS
      WHERE  MTX.prd_lvl_member_id = MSI.inventory_item_id
             AND MTX.org_lvl_member_id = MP.organization_id
             AND MTX.cus_lvl_member_id = MCS.partner_site_id;  

       

    • Prashul Agrawal

      Thanks Venkatesh, this helps.

      Is it possible to delete the existing combinations in cloud. By loading the measure data in Target mode with just one record, though it delete the existing data but combinations still remain and show in table.

      As for some customers and items combinations which are no more sold and no forecast is coming, then they should not appear in table.

      Even using advanced filter options, not able to remove them, as if we apply condition on Product level -> Replace with - 'Sales Forecast' is not null.

      This condition applies filter only at item level, so if this item has forecast for any one customer, table displays all combinations of this item against every customer for which it was sold previously.

      Even we apply same condition to customer and product dimension, the condition at combination of these two does not apply.

      Please suggest if any suitable way?