eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_CL_PURGE_STAGING SQL Statements

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

Line: 12


  PROCEDURE delete_records (   p_instance_code IN VARCHAR2 , p_del_rej_rec IN NUMBER );

Line: 20


  | DESCRIPTION  : This procedure is called to delete the records for a particular          |
  |                instance from all the tables from lookup type MSC_X_SETUP_ENTITY_CODE    |
  +========================================================================================*/

  PROCEDURE delete_records ( p_instance_code IN VARCHAR2 , p_del_rej_rec IN NUMBER )
  AS

  lv_instance_code VARCHAR2(5);

Line: 34


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

Line: 37


  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)  = 'MSC'
  AND LV.LOOKUP_TYPE           = 'MSC_X_SETUP_ENTITY_CODE';

Line: 70


                   lv_sql_stmt := 'DELETE FROM '||lv_tablename||' WHERE ROWNUM <= '||v_batch_size||'  AND PROCESS_FLAG IN ( '||G_ERROR_FLG||','||G_PROPAGATION||')';

Line: 72


                   lv_sql_stmt := 'DELETE FROM '||lv_tablename||' WHERE ROWNUM <= '||v_batch_size||'';

Line: 76


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

Line: 78


                   lv_sql_stmt := 'DELETE FROM '||lv_tablename||' WHERE SR_INSTANCE_CODE= '''||lv_instance_code||''' AND ROWNUM <= '||v_batch_size||'';

Line: 117


                   MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No. of rows deleted from '|| lv_tablename ||' - '||lv_total);

Line: 131


  END delete_records;

Line: 147


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

Line: 214


              UPDATE msc_apps_instances
              SET st_status=G_ST_PURGING
              WHERE INSTANCE_CODE= pINSTANCE_CODE;

Line: 227


  | DESCRIPTION  : This is the main program that deletes the records from the MSC 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_purge (  ERRBUF  OUT NOCOPY VARCHAR2,
                          RETCODE  OUT NOCOPY NUMBER,
                          p_instance_id IN NUMBER,
                          p_del_rej_rec IN NUMBER )

  AS

  CURSOR table_names IS
  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)  = 'MSC'
  AND LV.LOOKUP_TYPE           = 'MSC_X_SETUP_ENTITY_CODE';

Line: 254


  SELECT instance_code,instance_type,st_status
  FROM msc_apps_instances
  WHERE instance_id=cp_instance_id
  UNION ALL
  SELECT instance_code,instance_type,st_status
  FROM msc_apps_instances
  WHERE cp_instance_id =-1;

Line: 264


  SELECT instance_code
  FROM msc_apps_instances
  WHERE st_status NOT IN (G_ST_PULLING,G_ST_COLLECTING,G_ST_PRE_PROCESSING)
  FOR UPDATE;

Line: 270


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

Line: 326


    SELECT DECODE(nvl(FND_PROFILE.VALUE('MRP_DEBUG'),'N'),'Y',1,2),
        DECODE(nvl(FND_PROFILE.VALUE('MSC_PURGE_ST_CONTROL'),'No'),'Yes',1,2)
        INTO v_debug,lv_trunc_profile
    FROM dual;

Line: 333


       SELECT count(*) INTO lv_leg_inst_flag  FROM msc_apps_instances WHERE instance_type = G_INS_OTHER ;

Line: 334


       SELECT count(*) INTO lv_inst_flag  FROM msc_apps_instances;

Line: 345


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

Line: 365


           UPDATE msc_apps_instances
           SET st_status= G_ST_PURGING;

Line: 408


           UPDATE msc_apps_instances
           SET st_status= G_ST_EMPTY;

Line: 434


               delete_records( lv_p_instance_code,lv_p_del_rej_rec);

Line: 445


                      delete_records(lv_p_instance_code,lv_p_del_rej_rec);

Line: 453


                         UPDATE msc_apps_instances
                         SET st_status=G_ST_EMPTY
                         WHERE instance_code=lv_p_instance_code;

Line: 463


                         UPDATE msc_apps_instances
                         SET st_status=lv_st_status
                         WHERE instance_code=lv_p_instance_code;

Line: 722


      /* delete FROM MSC_ST_SR_ASSIGNMENTS

      // WHERE SR_ASSIGNMENT_INSTANCE_ID= p_instance_id;

Line: 1056


      MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_CALENDAR_ASSIGNMENTS', p_instance_id, NULL);

Line: 1070


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BOM_COMPONENTS', p_instance_id, NULL);

Line: 1078


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BOMS', p_instance_id, NULL);

Line: 1088


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CO_PRODUCTS', p_instance_id, NULL);

Line: 1096


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_COMPONENT_SUBSTITUTES', p_instance_id, NULL);

Line: 1104


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ROUTINGS', p_instance_id, NULL);

Line: 1112


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ROUTING_OPERATIONS', p_instance_id, NULL);

Line: 1120


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_OPERATION_RESOURCES', p_instance_id, NULL);

Line: 1128


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_OPERATION_RESOURCE_SEQS', p_instance_id, NULL);

Line: 1132


      MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_OPERATION_NETWORKS',p_instance_id,NULL);

Line: 1140


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PROCESS_EFFECTIVITY', p_instance_id, NULL);

Line: 1148


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_OPERATION_COMPONENTS', p_instance_id, NULL);

Line: 1160


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BILL_OF_RESOURCES', p_instance_id, NULL);

Line: 1168


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BOR_REQUIREMENTS', p_instance_id, NULL);

Line: 1180


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CALENDAR_DATES', p_instance_id, NULL);

Line: 1188


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PERIOD_START_DATES', p_instance_id, NULL);

Line: 1196


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CAL_YEAR_START_DATES', p_instance_id, NULL);

Line: 1204


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CAL_WEEK_START_DATES', p_instance_id, NULL);

Line: 1212


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_SHIFTS', p_instance_id, NULL);

Line: 1220


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CALENDAR_SHIFTS', p_instance_id, NULL);

Line: 1228


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SHIFT_DATES', p_instance_id, NULL);

Line: 1236


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_CHANGES', p_instance_id, NULL);

Line: 1244


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SHIFT_TIMES', p_instance_id, NULL);

Line: 1252


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SHIFT_EXCEPTIONS', p_instance_id, NULL);

Line: 1260


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_NET_RESOURCE_AVAIL', p_instance_id, NULL);

Line: 1270


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ITEM_CATEGORIES', p_instance_id, NULL);

Line: 1278


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CATEGORY_SETS', p_instance_id, NULL);

Line: 1288


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DEMANDS', p_instance_id, NULL);

Line: 1298


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SALES_ORDERS', p_instance_id, NULL);

Line: 1308


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESERVATIONS', p_instance_id, NULL);

Line: 1318


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SYSTEM_ITEMS', p_instance_id, NULL);

Line: 1326


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SYSTEM_ITEMS', p_instance_id, NULL);

Line: 1338


      MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_ITEM_SUBSTITUTES',p_instance_id, NULL);

Line: 1348


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DEPARTMENT_RESOURCES', p_instance_id, NULL);

Line: 1356


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SIMULATION_SETS', p_instance_id, NULL);

Line: 1364


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_GROUPS', p_instance_id, NULL);

Line: 1374


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SAFETY_STOCKS', p_instance_id, NULL);

Line: 1384


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DESIGNATORS', p_instance_id, NULL);

Line: 1394


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ASSIGNMENT_SETS', p_instance_id, NULL);

Line: 1402


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SOURCING_RULES', p_instance_id, NULL);

Line: 1410


      DELETE FROM MSC_ST_SR_ASSIGNMENTS

       WHERE SR_ASSIGNMENT_INSTANCE_ID= p_instance_id;

Line: 1420


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SR_RECEIPT_ORG', p_instance_id, NULL);

Line: 1428


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SR_SOURCE_ORG', p_instance_id, NULL);

Line: 1436


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_INTERORG_SHIP_METHODS', p_instance_id, NULL);

Line: 1444


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_REGIONS', p_instance_id, NULL);

Line: 1452


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ZONE_REGIONS', p_instance_id, NULL);

Line: 1460


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_REGION_LOCATIONS', p_instance_id, NULL);

Line: 1468


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_REGION_SITES', p_instance_id, NULL);

Line: 1476


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CARRIER_SERVICES', p_instance_id, NULL);

Line: 1484


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SUB_INVENTORIES', p_instance_id, NULL);

Line: 1494


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ITEM_SUPPLIERS', p_instance_id, NULL);

Line: 1502


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SUPPLIER_CAPACITIES', p_instance_id, NULL);

Line: 1510


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SUPPLIER_FLEX_FENCES', p_instance_id, NULL);

Line: 1520


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SUPPLIES', p_instance_id, NULL);

Line: 1530


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_REQUIREMENTS', p_instance_id, NULL);

Line: 1540


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_TRADING_PARTNERS', p_instance_id, NULL);

Line: 1548


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_TRADING_PARTNER_SITES', p_instance_id, NULL);

Line: 1556


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_LOCATION_ASSOCIATIONS', p_instance_id, NULL);

Line: 1566


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_UNIT_NUMBERS', p_instance_id, NULL);

Line: 1574


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PROJECTS', p_instance_id, NULL);

Line: 1582


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PROJECT_TASKS', p_instance_id, NULL);

Line: 1592


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PARAMETERS', p_instance_id, NULL);

Line: 1602


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_UNITS_OF_MEASURE', p_instance_id, NULL);

Line: 1610


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_UOM_CLASS_CONVERSIONS', p_instance_id, NULL);

Line: 1618


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_UOM_CONVERSIONS', p_instance_id, NULL);

Line: 1628


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_PFMC_MEASURES', p_instance_id, NULL);

Line: 1636


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_TARGET_LEVELS', p_instance_id, NULL);

Line: 1644


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_TARGETS', p_instance_id, NULL);

Line: 1652


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_BUSINESS_PLANS', p_instance_id, NULL);

Line: 1660


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_BIS_PERIODS', p_instance_id, NULL);

Line: 1670


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_ATP_RULES', p_instance_id, NULL);

Line: 1680


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PLANNERS', p_instance_id, NULL);

Line: 1690


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DEMAND_CLASSES', p_instance_id, NULL);

Line: 1700


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_PARTNER_CONTACTS', p_instance_id, NULL);

Line: 1710


        MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_ITEM_SOURCING',p_instance_id, NULL);

Line: 1718


        MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_CALENDARS',p_instance_id, NULL);

Line: 1726


        MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_WORKDAY_PATTERNS',p_instance_id, NULL);

Line: 1734


        MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_CALENDAR_EXCEPTIONS',p_instance_id, NULL);

Line: 1742


        MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_GROUPS',p_instance_id, NULL);

Line: 1750


        MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_GROUP_COMPANIES',p_instance_id, NULL);

Line: 1760


        MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_COMPANY_USERS', p_instance_id, NULL);

Line: 1768


        MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_ST_ITEM_CUSTOMERS', p_instance_id, NULL);

Line: 1782


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_OPERATION_NETWORKS',p_instance_id, NULL);

Line: 1790


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_OPERATIONS',p_instance_id, NULL);

Line: 1798


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_REQUIREMENT_OPS',p_instance_id, NULL);

Line: 1806


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_OP_RESOURCES',p_instance_id, NULL);

Line: 1818


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_TRIPS',p_instance_id, NULL);

Line: 1826


      MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_TRIP_STOPS',p_instance_id, NULL);

Line: 1838


     MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_APPS_INSTANCES',p_instance_id, NULL);

Line: 1850


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_DEPT_RES_INSTANCES',p_instance_id, NULL);

Line: 1854


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_NET_RES_INST_AVAIL',p_instance_id, NULL);

Line: 1858


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_JOB_OP_RES_INSTANCES',p_instance_id, NULL);

Line: 1862


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_INSTANCE_REQS',p_instance_id, NULL);

Line: 1866


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_SETUPS',p_instance_id, NULL);

Line: 1870


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SETUP_TRANSITIONS',p_instance_id, NULL);

Line: 1874


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_STD_OP_RESOURCES',p_instance_id, NULL);

Line: 1878


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RES_INSTANCE_CHANGES',p_instance_id, NULL);

Line: 1882


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_RESOURCE_CHARGES',p_instance_id, NULL);

Line: 1890


    MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_SR_LOOKUPS', p_instance_id, NULL);

Line: 1896


	MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ST_CALENDAR_MONTHS',p_instance_id, NULL);

Line: 1941


   SELECT attribute1 application_id, attribute2 table_name, attribute5 part_type
   FROM   fnd_lookup_values
   WHERE  lookup_type = 'MSC_STAGING_TABLE' AND
          enabled_flag = 'Y' AND
          view_application_id = 700 AND
          language = userenv('lang');

Line: 1953


   SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
   INTO   lv_control_flag
   FROM   dual;

Line: 1958


     SELECT instance_type, lrtype
     INTO   lv_instance_type, lv_last_refresh_type
     FROM   msc_apps_instances
     WHERE  instance_id= p_instance_id;

Line: 1982


                 lv_sql_stmt:= ' DELETE ' || lv_schema || '.' || c_rec.table_name
                           || ' WHERE sr_instance_id = ' || p_instance_id
                           || ' AND rownum < ' || lv_pbs;