APPS.MSD_PURGE SQL Statements

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

Line: 10

 *     which information is deleted from the msd_cs_data table.
 *     Therfore, each header needs to be checked if a row
 *     exists in the msd_cs_data table that refers to it.
 *
 *  2. When no date is specified the data from msd_cs_data_headers
 *     can immediately be deleted.
 */

/* If given the name of a stream this provides the definition id.
 * This is useful when on the UI, there is no option to select
 * the custom stream, but represented in a hard-coded label.
 */

CURSOR get_cs_definition_id(p_cs_name in VARCHAR2) IS
SELECT cs_definition_id
  FROM msd_cs_definitions
 WHERE name = p_cs_name;

Line: 29

SELECT system_flag
  FROM msd_cs_definitions
 WHERE cs_definition_id = p_cs_iden;

Line: 40

procedure delete_cs_data ( p_instance_id      IN varchar2,
                           p_cs_definition_id IN number,
                           p_cs_designator    IN varchar2,
                           p_from_date        IN date,
                           p_to_date          IN date ) IS

 TYPE cs_data_id_tab is table of msd_cs_data.cs_data_id%TYPE;

Line: 52

 select cs_data_id
   from msd_cs_data
  where cs_definition_id in (select cs_definition_id
                               from msd_cs_definitions
                              where cs_definition_id =  nvl(p_cs_definition_id , cs_definition_id)
                                and ((p_cs_definition_id is not null) or
                                     (system_flag = 'C')))
    and nvl(cs_name, '#$#$^&&&!!!!!!$%$%$%$%090@@') = nvl(p_cs_designator, nvl(cs_name, '#$#$^&&&!!!!!!$%$%$%$%090@@'))
    and nvl(attribute_43, '0001/01/01') between nvl(to_char(p_from_date, 'YYYY/MM/DD'), '0001/01/01')
    and nvl(to_char(p_to_date, 'YYYY/MM/DD'), '4317/12/31')
    and nvl(attribute_1, '-999') = nvl(p_instance_id, nvl(attribute_1, '-999'));

Line: 64

C_NUM_DELETE_ROWS number := 1000;

Line: 72

    fetch get_cs_data bulk collect into t_cs_data_id LIMIT C_NUM_DELETE_ROWS;

Line: 77

        DELETE FROM msd_cs_data
        WHERE cs_data_id = t_cs_data_id(i);

Line: 81

        DELETE FROM msd_cs_data_ds
        WHERE cs_data_id = t_cs_data_id(i);

Line: 95

end delete_cs_data;

Line: 98

procedure delete_cs_headers    (p_instance_id IN NUMBER,
                                p_cs_def_id   IN NUMBER,
                                p_cs_name     IN VARCHAR2) IS

begin

    delete from msd_cs_data_headers
    where instance = nvl(p_instance_id, instance)
    and cs_definition_id = nvl(p_cs_def_id, cs_definition_id)
    and cs_name = nvl(p_cs_name, cs_name);

Line: 111

end delete_cs_headers;

Line: 124

select 1
from msd_cs_data
where attribute_1 = p_instance_id
and cs_definition_id = p_cs_id
and cs_name = p_cs_name
and rownum = x_num_rows;

Line: 132

select *
from msd_cs_data_headers
where instance = nvl(p_instance_id, instance)
and cs_definition_id = nvl(p_cs_def_id, cs_definition_id)
and cs_name = nvl(p_cs_name, cs_name)
and exists ( select 1
                   from msd_cs_definitions csd
                  where csd.cs_definition_id = msd_cs_data_headers.cs_definition_id
                    and csd.system_flag = 'C' );

Line: 153

          delete_cs_headers(cs_rec.instance, cs_rec.cs_definition_id, cs_rec.cs_name);

Line: 164

/* This procedure is called after data is deleted from msd_cs_data.
 * It will determine whether dates are defined and determine
 * whether headers need to check for children in msd_cs_data
 */

procedure purge_cs_data_headers(p_instance_id IN NUMBER,
				p_from_date   IN VARCHAR2,
				p_to_date     IN VARCHAR2,
                                p_cs_def_id   IN NUMBER,
                                p_cs_name     IN VARCHAR2) IS

begin

  /* Date is included so the msd_cs_data needs to be checked for rows
   * that are deleted.
   */

  if (p_from_date is not null) or (p_to_date is not null) then

    /* Deletes data in headers checking date-filtered deletes in msd_cs_data */
    check_cs_headers (p_instance_id, p_cs_def_id, p_cs_name);

Line: 188

    /* Deletes directly from headers since no date filtering done. */

    delete from msd_cs_data_headers
    where instance = nvl(p_instance_id, instance)
    and cs_definition_id = nvl(p_cs_def_id, cs_definition_id)
    and cs_name = nvl(p_cs_name, cs_name)
    and exists ( select 1
                   from msd_cs_definitions csd
                  where csd.cs_definition_id = msd_cs_data_headers.cs_definition_id
                    and csd.system_flag = 'C' );

Line: 213

select 1
from msd_mfg_forecast
where instance = p_instance_id
and forecast_designator = p_cs_name
and rownum = x_num_rows;

Line: 220

select *
from msd_cs_data_headers
where instance = nvl(p_instance_id, instance)
and cs_definition_id = nvl(p_cs_def_id, cs_definition_id)
and cs_name = nvl(p_cs_name, cs_name);

Line: 237

          delete_cs_headers(cs_rec.instance, cs_rec.cs_definition_id, cs_rec.cs_name);

Line: 248

/* This procedure is called after data is deleted from msd_mfg_forecast.
 * It will determine whether dates are defined and determine
 * whether headers need to check for children in msd_mfg_forecast
 */

procedure purge_mfg_data_headers(p_instance_id IN NUMBER,
                                 p_cs_def_id   IN NUMBER,
				 p_l_date      IN NUMBER,
                                 p_cs_name     IN VARCHAR2) IS

begin

  /* Date is included so the msd_cs_data needs to be checked for rows
   * that are deleted.
   */

  if (p_l_date <> 0) then

    /* Deletes data in headers checking date-filtered deletes in msd_cs_data */
    check_mfg_headers (p_instance_id, p_cs_def_id, p_cs_name);

Line: 271

    /* Deletes directly from headers since no date filtering done. */
    delete_cs_headers(p_instance_id, p_cs_def_id, p_cs_name);

Line: 288

select 1
from msd_cs_data
where attribute_1 = p_instance_id
and cs_definition_id = p_cs_id
and cs_name = p_cs_name
and rownum = x_num_rows;

Line: 296

select *
from msd_cs_data_headers
where instance = nvl(p_instance_id, instance)
and cs_definition_id = p_cs_def_id
and cs_name = nvl(p_cs_name, cs_name);

Line: 313

          delete_cs_headers(cs_rec.instance, cs_rec.cs_definition_id, cs_rec.cs_name);

Line: 324

/* This procedure is called after data is deleted from msd_cs_data.
 * It will determine whether dates are defined and determine
 * whether headers need to check for children in msd_cs_data
 */

procedure purge_int_data_headers(p_instance_id IN NUMBER,
				p_from_date   IN VARCHAR2,
				p_to_date     IN VARCHAR2,
                                p_cs_def_id   IN NUMBER,
                                p_cs_name     IN VARCHAR2) IS

begin

  /* Date is included so the msd_cs_data needs to be checked for rows
   * that are deleted.
   */

  if (p_from_date is not null) or (p_to_date is not null) then

    /* Deletes data in headers checking date-filtered deletes in msd_cs_data */
    check_int_headers (p_instance_id, p_cs_def_id, p_cs_name);

Line: 348

    /* Deletes directly from headers since no date filtering done. */

    delete from msd_cs_data_headers
    where instance = nvl(p_instance_id, instance)
    and cs_definition_id = p_cs_def_id
    and cs_name = nvl(p_cs_name, cs_name);

Line: 394

l_delete_from varchar2(500);

Line: 403

b_delete_denorm   boolean := false;

Line: 432

      	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.SHIPMENT_FACT_TABLE;

Line: 437

      	l_final_str := l_delete_from || l_where;

Line: 448

      	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.BOOKING_FACT_TABLE;

Line: 453

      	l_final_str := l_delete_from || l_where;

Line: 466

      	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE;

Line: 472

      	l_final_str := l_delete_from || l_where;

Line: 502

    delete_cs_data (   p_instance_id,
                       x_cs_id,
                       null,
                           x_from_date,
                           x_to_date );

Line: 525

    delete_cs_data (   p_instance_id,
                           x_cs_id,
                           null,
                           x_from_date,
                           x_to_date );

Line: 548

    delete_cs_data (   p_instance_id,
                           x_cs_id,
                           null,
                           x_from_date,
                           x_to_date );

Line: 562

/** Delete Custom Data **/
if p_cs_data_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG then

    if (p_cs_definition_id is not null) then
      open get_cs_system_flag(p_cs_definition_id);

Line: 573

      /* Seeded custom stream can only be deleted one at a time.
       * Therefore the user must have specified the id of the
       * stream.
       */

      if(p_cs_definition_id is not null) then

        delete_cs_data (   p_instance_id,
                           p_cs_definition_id,
                           p_cs_designator,
                           x_from_date,
                           x_to_date );

Line: 596

       delete_cs_data (   p_instance_id,
                          p_cs_definition_id,
                          p_cs_designator,
                          x_from_date,
                          x_to_date );

Line: 612

/**  Delete Currency data **/
if (p_curr_yes_no = MSD_COMMON_UTILITIES.MSD_YES_FLAG) then
      	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.CURRENCY_FACT_TABLE;

Line: 615

      	l_final_str := l_delete_from;

Line: 626

      	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.UOM_FACT_TABLE;

Line: 628

      	l_final_str := l_delete_from || l_where;

Line: 640

     l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.TIME_FACT_TABLE;

Line: 648

	l_final_str := l_delete_from || l_where;

Line: 658

	l_final_str := l_delete_from || l_where;

Line: 672

	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.PRICING_FACT_TABLE;

Line: 675

	l_final_str := l_delete_from || l_where;

Line: 690

           SELECt nvl(max(revision), -999) into l_latest_revision
           from   msd_dp_scenario_revisions
           where  demand_plan_id = p_demand_plan_id and
                  scenario_id  = p_scenario_id;

Line: 696

              b_delete_denorm := TRUE;

Line: 698

              b_delete_denorm := FALSE;

Line: 702

	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.SCENARIO_ENTRIES_TABLE;

Line: 707

	l_final_str := l_delete_from || l_where;

Line: 710

        l_delete_from := 'delete from msd_dp_scenario_revisions';

Line: 714

        l_final_str := l_delete_from || l_where;

Line: 717

        l_delete_from := 'delete from msd_dp_planning_percentages';

Line: 722

        l_final_str := l_delete_from || l_where;

Line: 726

              b_delete_denorm = TRUE ) THEN

    	      l_delete_from := 'delete from msd_dp_scn_entries_denorm ';

Line: 732

	      l_final_str := l_delete_from || l_where;

Line: 735

    	      l_delete_from := 'delete from msd_dp_planning_pct_denorm ';

Line: 739

	      l_final_str := l_delete_from || l_where;

Line: 753

	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE;

Line: 755

	l_final_str := l_delete_from || l_where;

Line: 758

	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.LEVEL_ASSOC_FACT_TABLE;

Line: 760

	l_final_str := l_delete_from || l_where;

Line: 763

	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.ITEM_INFO_FACT_TABLE;

Line: 765

	l_final_str := l_delete_from || l_where;

Line: 768

	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.MSD_LOCAL_ID_SETUP_TABLE;

Line: 770

	l_final_str := l_delete_from || l_where;

Line: 773

	l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.LEVEL_ORG_ASSCNS_FACT_TABLE;

Line: 775

	l_final_str := l_delete_from || l_where;

Line: 778

        l_delete_from := 'delete from ' || MSD_COMMON_UTILITIES.ITEM_RELATIONSHIPS_FACT_TABLE;

Line: 780

	l_final_str := l_delete_from || l_where;

Line: 785

        delete from msd_level_values_ds;

Line: 787

        delete from msd_dp_parameters_ds;

Line: 789

        delete from msd_cs_data_ds;

Line: 791

        update msd_demand_plans
        set build_stripe_level_pk = null,
            build_stripe_stream_name = null,
            build_stripe_stream_desig = null,
            build_stripe_stream_ref_num = null;

Line: 818

       SELECT MIN(mdp.scn_build_refresh_num) ref_num,
         mcd.cs_definition_id csid,
         mcd.name csname
       FROM msd_dp_parameters mdp,
         msd_cs_definitions mcd
       WHERE mcd.name = mdp.parameter_type
       GROUP BY mcd.cs_definition_id,
         mcd.name
       ORDER BY mcd.cs_definition_id;

Line: 831

       SELECT COUNT(*) deleted_rec,
         cs_definition_id csid,
         last_refresh_num l_ref_num,
         action_code
       FROM msd_cs_data
       GROUP BY cs_definition_id,
         last_refresh_num,
         action_code HAVING action_code = 'D'
        AND cs_definition_id = cs_def_id
       ORDER BY last_refresh_num;

Line: 846

       deleted NUMBER;

Line: 860

             deleted := 0;

Line: 869

                 l_sql_stmt := ' DELETE FROM MSD_CS_DATA' || ' WHERE ACTION_CODE = ' || '''D''' || ' and LAST_REFRESH_NUM = ' || set_rec.l_ref_num || ' and cs_definition_id = ' || rec.csid;

Line: 880

                 deleted := deleted + set_rec.deleted_rec;

Line: 890

           IF deleted > 0 THEN
             fnd_file.PUT_LINE(fnd_file.LOG,   to_char(deleted) || ' unused records purged for the custom stream - ' || rec.csname);