APPS.MSD_PURGE_LEG SQL Statements

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

Line: 13

  | DESCRIPTION  : This procedure is called to delete or truncate all the tables            |
  |                under lookup type MSC_X_SETUP_ENTITY_CODE                                |
  +========================================================================================*/

  PROCEDURE delete_records ( p_instance_code IN VARCHAR2 DEFAULT NULL, p_instance_id IN NUMBER ,p_del_rej_rec IN NUMBER ,p_trunc_flag IN NUMBER )
  AS

  lv_instance_code VARCHAR2(5);

Line: 33

  lv_total number :=0; -- total number of rows deleted

Line: 36

  SELECT DISTINCT (LV.ATTRIBUTE1) TABLE_NAME
  FROM   FND_LOOKUP_VALUES LV
  WHERE LV.ENABLED_FLAG          = 'Y'
  AND LV.VIEW_APPLICATION_ID   = 700
  AND   SUBSTR  (LV.ATTRIBUTE1, 1, 3)  = 'MSD'
  AND LV.LOOKUP_TYPE           = 'MSC_X_SETUP_ENTITY_CODE';

Line: 75

           UPDATE msc_apps_instances
           SET st_status= G_ST_PURGING;

Line: 120

           UPDATE msc_apps_instances
           SET st_status= G_ST_EMPTY;

Line: 147

                    lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''' ) OR ( ATTRIBUTE_1 = '''||lv_instance_id||''' ))  AND ROWNUM <= '||v_batch_size
                    ||' AND PROCESS_FLAG IN ( '||G_ERROR_FLG||' ,'|| G_PROPAGATION||' )';

Line: 150

                    lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''' ) OR ( ATTRIBUTE_1 = '''||lv_instance_id||''' )) AND ROWNUM <= '||v_batch_size ;

Line: 160

                   lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''') OR ( INSTANCE_ID= '||lv_instance_id||'))  AND ROWNUM <= '||v_batch_size
                   ||'  AND PROCESS_FLAG IN ( '||G_ERROR_FLG||','||G_PROPAGATION||')';

Line: 163

                   lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''') OR ( INSTANCE_ID= '||lv_instance_id||')) AND ROWNUM <= '||v_batch_size||'';

Line: 174

                   lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''') OR ( INSTANCE= '||lv_instance_id||'))  AND ROWNUM <= '||v_batch_size
                   ||'  AND PROCESS_FLAG IN ( '||G_ERROR_FLG||','||G_PROPAGATION||')';

Line: 177

                   lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''') OR ( INSTANCE= '||lv_instance_id||')) AND ROWNUM <= '||v_batch_size||'';

Line: 221

                       msc_st_util.log_message ('No. of rows deleted from '|| lv_table_name ||' - '||lv_total);

Line: 240

  END delete_records;

Line: 256

     SELECT ST_STATUS INTO lv_staging_table_status
     FROM msc_apps_instances
     WHERE INSTANCE_CODE= pINSTANCE_CODE
     FOR UPDATE;

Line: 327

              UPDATE msc_apps_instances
              SET st_status=G_ST_PURGING
              WHERE INSTANCE_CODE= pINSTANCE_CODE;

Line: 340

  | DESCRIPTION  : This is the main program that deletes the records from the MSD staging        |
  |                tables.It takes instance_code as a parameter and deletes records for the      |
  |                instance only when st_status for this instance is not in G_ST_PULLING,        |
  |                G_ST_COLLECTING  and G_ST_PRE_PROCESSING .If the instance_code is null        |
  |                then it will delete records from all instances after checking the st_status.  |
  |                It also takes a parameter , whether to delete only errored out records or     |
  |                all legacy data (st_status check before deletion will only take place         |
  |                when 'delete only rejected records' parameter is set to NO).                  |
  +=============================================================================================*/


  PROCEDURE LAUNCH_PROCEDURE (  ERRBUF  OUT NOCOPY VARCHAR2,
                          RETCODE  OUT  NOCOPY NUMBER,
                          p_instance_id IN NUMBER,
                          p_del_rej_rec IN NUMBER)



  AS


  CURSOR instance_codes ( cp_instance_id NUMBER ) IS
  SELECT instance_code,instance_type,instance_id,st_status
  FROM msc_apps_instances
  WHERE ( cp_instance_id = -1 or instance_id=cp_instance_id );

Line: 370

  SELECT instance_code
  FROM msc_apps_instances
  WHERE st_status NOT IN (G_ST_PULLING,G_ST_COLLECTING,G_ST_PRE_PROCESSING)
  AND ( cp_instance_id= -1 or instance_id=cp_instance_id )
  FOR UPDATE;

Line: 377

  SELECT count(*)
  FROM msc_apps_instances;

Line: 381

  SELECT count(*)
  FROM msc_apps_instances
  WHERE st_status IN ( G_ST_PULLING,G_ST_COLLECTING,G_ST_PRE_PROCESSING )
  AND ((instance_id=cp_instance_id) OR (cp_instance_id=-1));

Line: 458

         delete_records (    p_instance_id   =>    lv_p_instance_id,
                             p_del_rej_rec   =>    lv_p_del_rej_rec,
                             p_trunc_flag    =>    lv_trunc_flag );

Line: 489

               delete_records( lv_instance_code,lv_instance_id,lv_p_del_rej_rec,lv_trunc_flag);

Line: 501

                      delete_records(lv_instance_code,lv_instance_id,lv_p_del_rej_rec,lv_trunc_flag);

Line: 508

                         UPDATE msc_apps_instances
                         SET st_status=G_ST_EMPTY
                         WHERE instance_code=lv_instance_code;