APPS.MSD_SRP_PROCESS_STREAM_DATA SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 27

  SELECT owner
  FROM dba_objects
  WHERE owner = owner
   AND object_type = 'TABLE'
   AND object_name = 'MDP_MATRIX'
  ORDER BY created DESC;

Line: 35

  SELECT demand_plan_id
  FROM msd_demand_plans
  WHERE demand_plan_id = 5555555;

Line: 40

  SELECT scenario_id
  FROM msd_dp_scenarios
  WHERE scenario_name LIKE
    (SELECT DISTINCT(scenario_name)
     FROM msd_dp_scenario_entries
	 WHERE scenario_id = -23453)
  ;

Line: 49

  SELECT DISTINCT(scenario_name)
  FROM msd_dp_scenario_entries
  WHERE scenario_id = -23453;

Line: 54

  SELECT COUNT(*)
  FROM msd_dp_scenario_entries
  WHERE demand_plan_id = 5555555
   AND scenario_id = l_scenario_id;

Line: 60

  SELECT * from MSC_ITEM_FAILURE_RATES
  where process_flag = 'E';

Line: 115

      lv_sql_stmt := ' UPDATE  ' || msd_schema_name || '.MSD_DEM_INS_BASE_HISTORY t1'
      || ' SET t1.level1_sr_pk = ( SELECT t2.sr_inventory_item_id '
      || '    FROM msc_system_items t2 '
      || '    WHERE t2.sr_instance_id  =  :p_instance_id '
      || '                           AND   t2.item_name       = t1.level1 '
      || '                           AND   rownum = 1) ';

Line: 128

      lv_sql_stmt := ' UPDATE  ' || msd_schema_name
      || '.MSD_DEM_FLD_SER_USG_HISTORY t1'
      || ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
       || '            FROM msc_system_items t2 '
       || '          WHERE t2.sr_instance_id  =  :p_instance_id '
       || '          AND   t2.item_name       = t1.level1 '
       || '          AND   rownum = 1) ' ;

Line: 136

      lv_sql_stmt1 := ' UPDATE ' || msd_schema_name
      || '.MSD_DEM_FLD_SER_USG_HISTORY t1'
      || ' SET t1.level2_sr_pk = ( SELECT t2.region_id '
      || '                       FROM msc_regions t2 '
      || '              where t2.sr_instance_id = :p_instance_id '
       || '               AND t2.zone = t1.level2 '
       || '               AND t2.zone_usage = 1)'  ;

Line: 150

      lv_sql_stmt := ' UPDATE  '
       || msd_schema_name
        || '.MSD_DEM_DPT_REP_USG_HISTORY t1'
	|| ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
	|| '                           FROM msc_system_items t2 '
	|| '                  WHERE t2.sr_instance_id  =  :p_instance_id '
	|| '                    AND   t2.item_name       = t1.level1) ';

Line: 158

      lv_sql_stmt1 := ' UPDATE '
      || msd_schema_name
      || '.MSD_DEM_DPT_REP_USG_HISTORY t1'
      || ' SET t1.level2_sr_pk = ( SELECT t2.sr_tp_id '
      || '                       FROM msc_trading_partners t2 '
      || '              where t2.sr_instance_id = :p_instance_id '
      || '              AND t2.organization_code = t1.level2) ';

Line: 172

      lv_sql_stmt := ' UPDATE  '
      || msd_schema_name
      || '.MSD_DEM_SRP_RETURN_HISTORY t1'
      || ' SET t1.level1_sr_pk = ( SELECT distinct(t2.sr_inventory_item_id) '
      || '                           FROM msc_system_items t2 '
      || '                WHERE t2.sr_instance_id  =  :p_instance_id '
      || '                           AND   t2.item_name       = t1.level1 '
      || '                           AND   rownum = 1) ';

Line: 181

      lv_sql_stmt1 := ' UPDATE '
      || msd_schema_name || '.MSD_DEM_SRP_RETURN_HISTORY t1'
      || ' SET t1.level2_sr_pk = ( SELECT t2.region_id '
      || '                       FROM msc_regions t2 '
      || '             where t2.sr_instance_id = :p_instance_id '
      || '                       AND t2.zone = t1.level2 '
      || '                       AND t2.zone_usage = 1)';

Line: 196

      lv_sql_stmt := 'UPDATE ' || msc_schema_name
      || '.MSC_ITEM_FAILURE_RATES t1'
      || ' SET t1.using_assembly_id = nvl((select distinct(t2.inventory_item_id)'
      || '                             FROM msc_system_items t2 '
      || '                  where t2.sr_instance_id = :p_instance_id '
      || '                     and t2.item_name = t1.using_assembly_name),-55555) ';

Line: 203

      lv_sql_stmt1 := 'UPDATE ' || msc_schema_name
      || '.MSC_ITEM_FAILURE_RATES t1'
      || ' SET t1.inventory_item_id = nvl((select distinct(t2.inventory_item_id) '
      || '                             FROM msc_system_items t2 '
      || '                      where t2.sr_instance_id = :p_instance_id '
      || '                             and t2.item_name = t1.item_name),-55555) ';

Line: 210

      lv_sql_stmt2 := ' UPDATE ' || msc_schema_name
      || '.MSC_ITEM_FAILURE_RATES '
      || ' SET FAILURE_RATE = 1'
      || ' where failure_rate > 1'
      || ' and process_flag = ''N'' ';

Line: 216

      lv_sql_stmt3 := ' UPDATE ' || msc_schema_name
      || '.MSC_ITEM_FAILURE_RATES '
      || ' SET FAILURE_RATE = 0'
      || ' where failure_rate < 0'
      || ' and process_flag = ''N'' ';

Line: 222

      lv_sql_stmt4 := ' UPDATE ' || msc_schema_name
      || '.MSC_ITEM_FAILURE_RATES '
      || ' SET PROCESS_FLAG = ''P'''
      || ' where PROCESS_FLAG = ''N''';

Line: 227

      lv_sql_stmt5 := ' UPDATE ' || msc_schema_name
      || '.MSC_ITEM_FAILURE_RATES '
      || ' SET PROCESS_FLAG = ''E'''
      || ' where PROCESS_FLAG = ''N'''
      || ' and (USING_ASSEMBLY_ID = -55555 or INVENTORY_ITEM_ID = -55555) ';

Line: 233

      lv_sql_stmt6 := 'DELETE FROM ' || msc_schema_name
      || '.MSC_ITEM_FAILURE_RATES '
      || 'WHERE PROCESS_FLAG = ''E''';

Line: 269

        INSERT
        INTO msd_demand_plans(demand_plan_id,   organization_id,   demand_plan_name,   last_update_date,   last_updated_by,   creation_date,   created_by,   sr_instance_id,   use_org_specific_bom_flag)
        VALUES(5555555,   -23453,   'SRP DUMMY PLAN',   trunc(sysdate),   fnd_global.user_id,   trunc(sysdate),   fnd_global.user_id,   -23453,   'N');

Line: 282

	lv_sql_stmt := 'DELETE FROM MSD_DP_SCENARIO_ENTRIES WHERE demand_plan_id = 5555555 and scenario_id = :l_scenario_id';

Line: 285

          UPDATE MSD_DP_SCENARIO_ENTRIES SET SCENARIO_ID = l_scenario_id where scenario_name = l_scenario_name
          and demand_plan_id = 5555555 and scenario_id = -23453;

Line: 297

        lv_sql_stmt := 'INSERT INTO MSD_DP_SCENARIOS(demand_plan_id,scenario_id,scenario_name, '
	|| 'last_update_date,last_updated_by,creation_date,created_by)'
	|| 'values(5555555,MSD_DP_SCENARIOS_S.nextval,' || bind_var
	|| 'trunc(sysdate),fnd_global.user_id,trunc(sysdate),fnd_global.user_id)';

Line: 303

        INSERT INTO MSD_DP_SCENARIO_OUTPUT_LEVELS(demand_plan_id,scenario_id,level_id,last_update_date,last_updated_by,creation_date,created_by)
        values(5555555,MSD_DP_SCENARIOS_S.CURRVAL,1,trunc(sysdate),fnd_global.user_id,trunc(sysdate),fnd_global.user_id);

Line: 311

        lv_sql_stmt1 := 'UPDATE MSD_DP_SCENARIO_ENTRIES SET SCENARIO_ID = MSD_DP_SCENARIOS_S.CURRVAL' || ' WHERE SCENARIO_NAME = :l_scenario_name ';

Line: 318

      lv_sql_stmt2 := 'DELETE FROM MSD_DP_SCN_ENTRIES_DENORM ' || 'WHERE SCENARIO_ID = :l_scenario_id ' || 'AND DEMAND_PLAN_ID = 5555555';

Line: 322

      INSERT
      INTO msd_dp_scn_entries_denorm(demand_plan_id,   scenario_id,   demand_id,   sr_inventory_item_id,   sr_organization_id,   start_time,   end_time,   quantity,   creation_date,   created_by)
        (SELECT mdse.demand_plan_id,
           mdse.scenario_id,
           mdse.entry_id,
           msi.sr_inventory_item_id,
           mtp.sr_tp_id,
           mdse.time_lvl_val_from,
           mdse.time_lvl_val_to,
           mdse.total_quantity,
           mdse.creation_date,
           mdse.created_by
         FROM msd_dp_scenario_entries mdse,
           msc_trading_partners mtp,
           msc_system_items msi
         WHERE mdse.demand_plan_id = 5555555
         AND mdse.scenario_id = l_scenario_id
         AND mdse.organization_lvl_val = mtp.organization_code
         AND mdse.product_lvl_val = msi.item_name
         AND mtp.sr_instance_id = p_instance_id
         AND mtp.partner_type = 3
         AND mtp.sr_tp_id = msi.organization_id
         AND msi.sr_instance_id = p_instance_id
         AND msi.plan_id = -1)
      ;

Line: 348

      DELETE FROM msd_dp_scenario_entries mdse
      WHERE mdse.scenario_id = l_scenario_id
       AND mdse.organization_lvl_val IN
        (SELECT organization_code
         FROM msc_trading_partners
         WHERE sr_instance_id = p_instance_id
         AND partner_type = 3
         AND organization_code LIKE mdse.organization_lvl_val
         AND rownum = 1)
      AND mdse.product_lvl_val IN
        (SELECT item_name
         FROM msc_system_items
         WHERE sr_instance_id = p_instance_id
         AND item_name LIKE mdse.product_lvl_val
         AND rownum = 1)
      ;