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 nvl(LV.ATTRIBUTE4,2) = 2
  AND LV.LOOKUP_TYPE           = 'MSC_X_SETUP_ENTITY_CODE';

Line: 71


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

Line: 73


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

Line: 77


                   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: 79


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

Line: 118


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

Line: 132


  END delete_records;

Line: 148


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

Line: 215


              UPDATE msc_apps_instances
              SET st_status=G_ST_PURGING
              WHERE INSTANCE_CODE= pINSTANCE_CODE;

Line: 228


  | 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: 255


  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: 265


  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: 271


  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: 327


    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: 334


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

Line: 335


       SELECT count(*) INTO lv_inst_flag  FROM msc_apps_instances;

Line: 346


       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: 366


           UPDATE msc_apps_instances
           SET st_status= G_ST_PURGING;

Line: 409


           UPDATE msc_apps_instances
           SET st_status= G_ST_EMPTY;

Line: 435


               delete_records( lv_p_instance_code,lv_p_del_rej_rec);

Line: 446


                      delete_records(lv_p_instance_code,lv_p_del_rej_rec);

Line: 454


                         UPDATE msc_apps_instances
                         SET st_status=G_ST_EMPTY
                         WHERE instance_code=lv_p_instance_code;

Line: 464


                         UPDATE msc_apps_instances
                         SET st_status=lv_st_status
                         WHERE instance_code=lv_p_instance_code;

Line: 723


      /* delete FROM MSC_ST_SR_ASSIGNMENTS

      // WHERE SR_ASSIGNMENT_INSTANCE_ID= p_instance_id;

Line: 1057


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

Line: 1071


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

Line: 1079


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

Line: 1089


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

Line: 1097


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

Line: 1105


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

Line: 1113


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

Line: 1121


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

Line: 1129


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

Line: 1133


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

Line: 1141


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

Line: 1149


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

Line: 1161


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

Line: 1169


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

Line: 1181


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

Line: 1189


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

Line: 1197


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

Line: 1205


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

Line: 1213


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

Line: 1221


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

Line: 1229


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

Line: 1237


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

Line: 1245


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

Line: 1253


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

Line: 1261


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

Line: 1271


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

Line: 1279


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

Line: 1289


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

Line: 1299


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

Line: 1309


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

Line: 1319


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

Line: 1327


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

Line: 1339


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

Line: 1349


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

Line: 1357


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

Line: 1365


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

Line: 1375


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

Line: 1385


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

Line: 1395


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

Line: 1403


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

Line: 1411


      DELETE FROM MSC_ST_SR_ASSIGNMENTS

       WHERE SR_ASSIGNMENT_INSTANCE_ID= p_instance_id;

Line: 1421


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

Line: 1429


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

Line: 1437


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

Line: 1445


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

Line: 1453


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

Line: 1461


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

Line: 1469


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

Line: 1477


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

Line: 1485


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

Line: 1495


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

Line: 1503


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

Line: 1511


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

Line: 1521


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

Line: 1531


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

Line: 1541


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

Line: 1549


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

Line: 1557


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

Line: 1567


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

Line: 1575


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

Line: 1583


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

Line: 1593


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

Line: 1603


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

Line: 1611


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

Line: 1619


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

Line: 1629


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

Line: 1637


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

Line: 1645


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

Line: 1653


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

Line: 1661


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

Line: 1671


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

Line: 1681


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

Line: 1691


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

Line: 1701


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

Line: 1711


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

Line: 1719


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

Line: 1727


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

Line: 1735


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

Line: 1743


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

Line: 1751


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

Line: 1761


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

Line: 1769


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

Line: 1783


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

Line: 1791


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

Line: 1799


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

Line: 1807


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

Line: 1819


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

Line: 1827


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

Line: 1839


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

Line: 1851


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

Line: 1855


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

Line: 1859


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

Line: 1863


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

Line: 1867


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

Line: 1871


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

Line: 1875


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

Line: 1879


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

Line: 1883


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

Line: 1891


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

Line: 1897


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

Line: 1942


   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: 1954


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

Line: 1959


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

Line: 1983


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