APPS.MSC_PURGE_LID SQL Statements

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

Line: 36

    SELECT mai.enable_flag,
          mai.st_status
          INTO lv_instance_enabled, lv_staging_table_status
          FROM MSC_APPS_INSTANCES mai
          WHERE mai.INSTANCE_ID= v_instance_id
          AND   mai.instance_type IN (G_INS_OTHER,G_INS_EXCH) ;

Line: 105

      SELECT tab.partitioned
        FROM dba_tables tab,
             FND_ORACLE_USERID a,
             FND_PRODUCT_INSTALLATIONS b
       WHERE a.oracle_id = b.oracle_id
         AND b.application_id= 724
         AND tab.owner= a.oracle_username
         AND tab.table_name= p_table_name;

Line: 128

  This function deletes record from the MSC tables.
=========================================================================*/

  PROCEDURE DELETE_MSC_TABLE( p_table_name            IN VARCHAR2,
                              p_instance_id           IN NUMBER,
                              p_plan_id               IN NUMBER:= NULL,
                              p_sub_str               IN VARCHAR2:= NULL) IS

    lv_cnt          NUMBER;

Line: 154

    FND_MESSAGE.SET_TOKEN('PROCEDURE', 'DELETE_MSC_TABLE:'||p_table_name);

Line: 201

          lv_sql_stmt:= 'SELECT COUNT(*)'
                   ||' FROM '|| p_table_name
                   ||' WHERE SR_INSTANCE_ID= :p_instance_id '
                   ||  p_sub_str;

Line: 221

             lv_sql_stmt:= 'DELETE  '||p_table_name
                      ||' WHERE SR_INSTANCE_ID= :p_instance_id '
                      ||  p_sub_str;

Line: 237

             lv_sql_stmt:=   'DELETE  '||p_table_name
                         ||'  WHERE SR_INSTANCE_ID= :p_instance_id '
                         ||     p_sub_str
                         ||'    AND ROWNUM < :lv_pbs';

Line: 259

          lv_sql_stmt:= 'SELECT COUNT(*)'
                   ||' FROM '||p_table_name
                   ||' WHERE SR_INSTANCE_ID= :p_instance_id'
                   ||'   AND PLAN_ID= -1 '
                   ||    p_sub_str;

Line: 278

             lv_sql_stmt:=  'DELETE '||p_table_name
                         ||'  WHERE SR_INSTANCE_ID= :lv_instance_id'
                         ||'    AND PLAN_ID= -1 '
                         ||   p_sub_str;

Line: 293

             lv_sql_stmt:=   'DELETE '||p_table_name
                         ||'  WHERE SR_INSTANCE_ID= :p_instance_id '
                         ||'    AND PLAN_ID= -1 '
                         ||     p_sub_str
                         ||'    AND ROWNUM < :lv_pbs';

Line: 324

	lv_errtext := substr('DELETE_MSC_TABLE'||'('
                        ||v_sql_stmt||')'|| SQLERRM, 1, 240);

Line: 329

  END DELETE_MSC_TABLE;

Line: 334

  This function deletes record from MSC_LOCAL_ID_XXX  table
=========================================================================*/

  PROCEDURE DELETE_LID_TABLE(p_entity_name         IN VARCHAR2,
                             p_lid_table           IN VARCHAR2,
                             p_instance_id         IN NUMBER,
                             p_where_str           IN VARCHAR2) IS


    lv_cnt          NUMBER;

Line: 360

    FND_MESSAGE.SET_TOKEN('PROCEDURE', 'DELETE_LID_TABLE:'||p_lid_table);

Line: 367

    lv_sql_stmt :=  ' SELECT COUNT(*) '
                   ||' FROM  '||p_lid_table||' lid'
                   ||' WHERE lid.instance_id = :p_instance_id'
                   ||' AND lid.entity_name = :p_entity_name'
                   ||  lv_where_str  ;

Line: 385

               lv_sql_stmt :=  'DELETE FROM  '||p_lid_table||' lid'
                              ||' WHERE lid.instance_id = :p_instance_id'
                              ||' AND lid.entity_name = :p_entity_name'
                              ||  lv_where_str  ;

Line: 401

              lv_sql_stmt :=  'DELETE FROM  '||p_lid_table||' lid'
                              ||' WHERE lid.instance_id = :p_instance_id'
                              ||' AND lid.entity_name = :p_entity_name'
                              ||  lv_where_str
                              ||' AND ROWNUM < :lv_pbs';

Line: 426

            MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 441

 	lv_errtext := substr('DELETE_LID_TABLE'||'('
                        ||v_sql_stmt||')'|| SQLERRM, 1, 240);

Line: 446

  END DELETE_LID_TABLE ;

Line: 450

 This is the main program that deletes the record from ths MSC_LOCAL_ID_XXX
 table . This accepts parameter, date upto which the record has to be deleted
 and different Business Object for which this program should delete LID .
 If it is complete refresh then it deletes record from the ODS and LID both.
+==========================================================================*/

  PROCEDURE PURGE_LID_TABLES(ERRBUF              OUT NOCOPY VARCHAR2,
                             RETCODE             OUT NOCOPY NUMBER,
                             p_instance_id       IN  NUMBER,
                             p_complete_refresh  IN  NUMBER     DEFAULT SYS_NO,
                             p_date              IN  VARCHAR2,
                             p_supply_flag       IN  NUMBER     DEFAULT SYS_NO,
                             p_demand_flag       IN  NUMBER     DEFAULT SYS_NO)
  IS

  lv_errtext        VARCHAR2(5000);

Line: 482

           SELECT rowid
           FROM msc_local_id_demand lid
           WHERE lid.entity_name ='DISPOSITION_ID_FCT'
           AND lid.instance_id = p_instance_id
           MINUS
           SELECT distinct lid.rowid
           FROM msc_local_id_demand lid, msc_demands ms
           WHERE lid.instance_id = p_instance_id
           AND lid.entity_name = 'DISPOSITION_ID_FCT'
           AND ms.sales_order_line_id = lid.local_id
           AND ms.plan_id = -1
           AND ms.origination_type = 29
           AND ms.sr_instance_id = lid.instance_id;

Line: 497

          SELECT rowid
          FROM msc_local_id_demand lid
          WHERE lid.entity_name ='DISPOSITION_ID_MDS'
          AND lid.instance_id = p_instance_id
          MINUS
          SELECT distinct lid.rowid
          FROM msc_local_id_demand lid, msc_demands ms
          WHERE lid.instance_id = p_instance_id
          AND lid.entity_name = 'DISPOSITION_ID_MDS'
          AND ms.disposition_id = lid.local_id
          AND ms.origination_type = 8
          AND ms.plan_id = -1
          AND ms.sr_instance_id = lid.instance_id;

Line: 512

          SELECT rowid
          FROM msc_local_id_demand lid
          WHERE lid.entity_name ='SALES_ORDER_ID'
          AND lid.instance_id = p_instance_id
          MINUS
          SELECT distinct lid.rowid
          FROM msc_local_id_demand lid, msc_sales_orders ms
          WHERE lid.instance_id = p_instance_id
          AND lid.entity_name = 'SALES_ORDER_ID'
          AND ms.demand_source_header_id = lid.local_id
          AND ms.sr_instance_id = lid.instance_id;

Line: 525

          SELECT rowid
          FROM msc_local_id_demand lid
          WHERE lid.entity_name ='DEMAND_ID'
          AND lid.instance_id = p_instance_id
          MINUS
          SELECT distinct lid.rowid
          FROM msc_local_id_demand lid, msc_sales_orders ms
          WHERE lid.instance_id = p_instance_id
          AND lid.entity_name = 'DEMAND_ID'
          AND ms.demand_id = lid.local_id
          AND ms.sr_instance_id = lid.instance_id;

Line: 538

             SELECT rowid
             FROM msc_local_id_supply lid
             WHERE lid.entity_name ='WIP_ENTITY_ID'
             AND lid.instance_id = p_instance_id
             MINUS
             SELECT distinct lid.rowid
             FROM msc_local_id_supply lid, msc_supplies ms
             WHERE lid.instance_id = p_instance_id
             AND lid.entity_name ='WIP_ENTITY_ID'
             AND ms.disposition_id = lid.local_id
             AND ms.order_type IN (3,7,27)
             AND ms.plan_id = -1
             AND ms.sr_instance_id = lid.instance_id;

Line: 553

	     SELECT rowid
             FROM msc_local_id_supply lid
             WHERE lid.entity_name ='SR_MTL_SUPPLY_ID'
             AND lid.instance_id = p_instance_id
             MINUS
             SELECT distinct lid.rowid
             FROM msc_local_id_supply lid, msc_supplies ms
             WHERE lid.instance_id = p_instance_id
             AND lid.entity_name = 'SR_MTL_SUPPLY_ID'
             AND ms.sr_mtl_supply_id = lid.local_id
             AND ms.order_type IN (1,2,8,11,12)
             AND ms.plan_id = -1
             AND ms.sr_instance_id = lid.instance_id;

Line: 568

             SELECT rowid
             FROM msc_local_id_supply lid
             WHERE lid.entity_name ='PO_LINE_ID'
             AND lid.instance_id = p_instance_id
             MINUS
             SELECT distinct lid.rowid
             FROM msc_local_id_supply lid, msc_supplies ms
             WHERE lid.instance_id = p_instance_id
             AND lid.entity_name = 'PO_LINE_ID'
             AND ms.po_line_id = lid.local_id
             AND ms.order_type IN (1,2,8,11,12)
             AND ms.plan_id = -1
             AND ms.sr_instance_id = lid.instance_id;

Line: 583

             SELECT rowid
             FROM msc_local_id_supply lid
             WHERE lid.entity_name ='DISPOSITION_ID'
             AND lid.instance_id = p_instance_id
             MINUS
             SELECT distinct lid.rowid
             FROM msc_local_id_supply lid, msc_supplies ms
             WHERE lid.instance_id = p_instance_id
             AND lid.entity_name = 'DISPOSITION_ID'
             AND ms.disposition_id = lid.local_id
             AND ms.order_type IN (1,2,8,11,12)
             AND ms.plan_id = -1
             AND ms.sr_instance_id = lid.instance_id;

Line: 598

            SELECT rowid
            FROM msc_local_id_supply lid
            WHERE lid.entity_name ='DISPOSITION_ID_MPS'
            AND lid.instance_id = p_instance_id
            MINUS
            SELECT distinct lid.rowid
            FROM msc_local_id_supply lid, msc_supplies ms
            WHERE lid.instance_id = p_instance_id
            AND lid.entity_name = 'DISPOSITION_ID_MPS'
            AND ms.disposition_id = lid.local_id
            AND ms.order_type = 5
            AND ms.plan_id = -1
            AND ms.sr_instance_id = lid.instance_id;

Line: 613

            SELECT rowid
            FROM msc_local_id_supply lid
            WHERE lid.entity_name ='SCHEDULE_GROUP_ID'
            AND lid.instance_id = p_instance_id
            MINUS
            SELECT distinct lid. rowid
            FROM msc_local_id_supply lid, msc_supplies ms
            WHERE lid.instance_id = p_instance_id
            AND lid.entity_name = 'SCHEDULE_GROUP_ID'
            AND ms.schedule_group_id = lid.local_id
            AND ms.plan_id = -1
            AND ms.sr_instance_id = lid.instance_id;

Line: 666

   DELETE MSC_ITEM_ID_LID    WHERE SR_INSTANCE_ID= v_instance_id;

Line: 670

   DELETE MSC_ITEM_ID_LID    WHERE SR_INSTANCE_ID= -1;

Line: 679

       DELETE_MSC_TABLE('MSC_SYSTEM_ITEMS', v_instance_id, -1);

Line: 681

       DELETE_LID_TABLE( p_entity_name     => 'SR_INVENTORY_ITEM_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_ITEM',
                         p_instance_id     => v_instance_id ,
                         p_where_str       => NULL );

Line: 688

      DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', v_instance_id, NULL);

Line: 690

       DELETE_LID_TABLE( p_entity_name     => 'ABC_CLASS_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id ,
                         p_where_str       => NULL );

Line: 696

       DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', v_instance_id, -1);

Line: 698

       DELETE_MSC_TABLE( 'MSC_BOMS', v_instance_id, -1);

Line: 700

       DELETE_LID_TABLE( p_entity_name     => 'BILL_SEQUENCE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id ,
                         p_where_str       => NULL );

Line: 705

       DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', v_instance_id, -1);

Line: 707

       DELETE_LID_TABLE( p_entity_name     => 'COMPONENT_SEQUENCE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 712

       DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', v_instance_id, -1);

Line: 715

       DELETE_LID_TABLE( p_entity_name     => 'CO_PRODUCT_GROUP_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 723

       DELETE_MSC_TABLE( 'MSC_ROUTINGS', v_instance_id, -1);

Line: 725

       DELETE_LID_TABLE( p_entity_name     => 'ROUTING_SEQUENCE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 731

       DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', v_instance_id, -1);

Line: 733

       DELETE_LID_TABLE( p_entity_name     => 'OPERATION_SEQUENCE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 738

       DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', v_instance_id, -1);

Line: 740

       DELETE_LID_TABLE( p_entity_name     => 'RESOURCE_SEQ_NUM',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 745

       DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', v_instance_id, -1);

Line: 746

       DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', v_instance_id, -1);

Line: 747

       DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', v_instance_id, -1);

Line: 750

       DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', v_instance_id, -1);

Line: 753

       DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', v_instance_id, -1);

Line: 754

       DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', v_instance_id, -1);

Line: 755

       DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', v_instance_id, -1);

Line: 759

DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', v_instance_id, NULL);

Line: 761

       DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', v_instance_id, -1);

Line: 763

       DELETE_LID_TABLE( p_entity_name     => 'DEPARTMENT_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 768

       DELETE_LID_TABLE( p_entity_name     => 'LINE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 773

       DELETE_LID_TABLE( p_entity_name     => 'RESOURCE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 778

       DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', v_instance_id, NULL);

Line: 779

       DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', v_instance_id, NULL);

Line: 780

       DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', v_instance_id, NULL);

Line: 781

       DELETE_MSC_TABLE( 'MSC_RESOURCE_REQUIREMENTS', v_instance_id, -1);

Line: 785

       DELETE_MSC_TABLE( 'MSC_PROJECTS', v_instance_id, -1);

Line: 787

       DELETE_LID_TABLE( p_entity_name     => 'PROJECT_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 792

       DELETE_MSC_TABLE( 'MSC_PROJECT_TASKS', v_instance_id, -1);

Line: 794

       DELETE_LID_TABLE( p_entity_name     => 'TASK_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 799

       DELETE_LID_TABLE( p_entity_name     => 'COSTING_GROUP_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 806

DELETE_MSC_TABLE( 'MSC_DEMAND_CLASSES', v_instance_id, NULL);

Line: 815

/*      DELETE MSC_TRADING_PARTNERS
          WHERE sr_instance_id= v_instance_id
           AND partner_type=3;

Line: 819

       DELETE_LID_TABLE( p_entity_name     => 'SR_TP_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 825

        DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', v_instance_id, NULL,
                          'AND PARTNER_TYPE=3');

Line: 828

        DELETE_LID_TABLE( p_entity_name     => 'SR_TP_SITE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 833

       DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', v_instance_id, NULL);

Line: 837

       DELETE MSC_LOCATION_ASSOCIATIONS
          WHERE SR_INSTANCE_ID= v_instance_id;

Line: 840

       DELETE_LID_TABLE(p_entity_name     => 'LOCATION_ID',
                        p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                        p_instance_id     => v_instance_id,
                        p_where_str       => NULL );      */ --- for legacyno deletion

Line: 847

        DELETE_MSC_TABLE( 'MSC_PLANNERS', v_instance_id, NULL);

Line: 850

        DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', v_instance_id, NULL);

Line: 852

        DELETE_LID_TABLE( p_entity_name    => 'SR_CATEGORY_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 857

   /*    DELETE_LID_TABLE( p_entity_name    => 'SR_CATEGORY_SET_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL ); */ -- as we do not purge ODS

Line: 865

/*   DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', v_instance_id, NULL);

Line: 867

      DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', v_instance_id, NULL);

Line: 869

      DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', v_instance_id, NULL);

Line: 871

      DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', v_instance_id, NULL);

Line: 873

      DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', v_instance_id, NULL);

Line: 875

      DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', v_instance_id, NULL);

Line: 877

      DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', v_instance_id, NULL);

Line: 879

      DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', v_instance_id, NULL);

Line: 881

      DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', v_instance_id, NULL);

Line: 883

      DELETE_LID_TABLE( p_entity_name     => 'SHIFT_NUM',
                       p_lid_table       => 'MSC_LOCAL_ID_SETUP',
                       p_instance_id     => v_instance_id,
                       p_where_str       => NULL );                    */

Line: 889

DELETE_MSC_TABLE( 'MSC_INTERORG_SHIP_METHODS', v_instance_id,-1);

Line: 893

       DELETE_LID_TABLE( p_entity_name     => 'SOURCING_RULE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id ,
                         p_where_str       => NULL );

Line: 898

       DELETE_LID_TABLE( p_entity_name     => 'ASSIGNMENT_SET_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 904

       DELETE_LID_TABLE( p_entity_name     => 'SR_RECEIPT_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 909

       DELETE_LID_TABLE( p_entity_name     => 'SR_SOURCE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 914

       DELETE_LID_TABLE( p_entity_name     => 'ASSIGNMENT_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_MISC',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );      */

Line: 925

   UPDATE MSC_DESIGNATORS
   SET DISABLE_DATE= v_current_date,
       LAST_UPDATE_DATE= v_current_date,
       LAST_UPDATED_BY= v_current_user
   WHERE SR_INSTANCE_ID= v_instance_id
   AND COLLECTED_FLAG= SYS_YES;

Line: 933

DELETE_MSC_TABLE( 'MSC_SAFETY_STOCKS', v_instance_id, -1);

Line: 936

DELETE_MSC_TABLE( 'MSC_RESERVATIONS', v_instance_id, -1);

Line: 939

       DELETE_MSC_TABLE( 'MSC_DEMANDS', v_instance_id, -1 );

Line: 940

       DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', v_instance_id, NULL);

Line: 943

       DELETE_LID_TABLE( p_entity_name     => 'DISPOSITION_ID_FCT',
                         p_lid_table       => 'MSC_LOCAL_ID_DEMAND',
                         p_instance_id     => v_instance_id ,
                         p_where_str       => NULL );

Line: 948

       DELETE_LID_TABLE( p_entity_name     => 'DISPOSITION_ID_MDS',
                         p_lid_table       => 'MSC_LOCAL_ID_DEMAND',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 953

       DELETE_LID_TABLE( p_entity_name     => 'DISPOSITION_ID_FCT',
                         p_lid_table       => 'MSC_LOCAL_ID_DEMAND',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 958

       DELETE_LID_TABLE( p_entity_name     => 'SALES_ORDER_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_DEMAND',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 963

       DELETE_LID_TABLE( p_entity_name     => 'DEMAND_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_DEMAND',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 970

       DELETE_MSC_TABLE( 'MSC_SUPPLIES', v_instance_id, -1);

Line: 972

       DELETE_LID_TABLE( p_entity_name     => 'DISPOSITION_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SUPPLY',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 977

       DELETE_LID_TABLE( p_entity_name     => 'PO_LINE_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SUPPLY',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 982

       DELETE_LID_TABLE( p_entity_name     => 'SCHEDULE_GROUP_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SUPPLY',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 987

       DELETE_LID_TABLE( p_entity_name     => 'DISPOSTION_ID_MPS',
                         p_lid_table       => 'MSC_LOCAL_ID_SUPPLY',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 992

       DELETE_LID_TABLE( p_entity_name     => 'SR_MTL_SUPPLY_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SUPPLY',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 997

       DELETE_LID_TABLE( p_entity_name     => 'WIP_ENTITY_ID',
                         p_lid_table       => 'MSC_LOCAL_ID_SUPPLY',
                         p_instance_id     => v_instance_id,
                         p_where_str       => NULL );

Line: 1004

   DELETE_MSC_TABLE('MSC_JOB_OPERATION_NETWORKS', v_instance_id, -1);

Line: 1005

   DELETE_MSC_TABLE('MSC_JOB_OPERATIONS', v_instance_id, -1);

Line: 1006

   DELETE_MSC_TABLE('MSC_JOB_REQUIREMENT_OPS', v_instance_id, -1);

Line: 1007

   DELETE_MSC_TABLE('MSC_JOB_OP_RESOURCES', v_instance_id, -1);

Line: 1024

         DELETE_MSC_TABLE( p_table_name  =>'MSC_DEMANDS',
                           p_instance_id => v_instance_id,
                           p_plan_id     => -1,
                           p_sub_str     => '  and trunc(USING_ASSEMBLY_DEMAND_DATE) <= '||'trunc(to_date('||''''||p_date||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||'))');

Line: 1030

        DELETE_MSC_TABLE( p_table_name  =>'MSC_SALES_ORDERS',
                           p_instance_id => v_instance_id,
                           p_plan_id     => NULL,
                           p_sub_str     => '  and trunc(REQUIREMENT_DATE) <= '||'trunc(to_date('||''''||p_date||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||'))');

Line: 1043

            MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND  for entity_name  DISPOSITION_ID_FCT' );

Line: 1054

           DELETE FROM  MSC_LOCAL_ID_DEMAND
           WHERE ROWID = lb_rowid(j);

Line: 1065

            MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 1081

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND  for entity_name  DISPOSITION_ID_MDS');

Line: 1092

         DELETE FROM  MSC_LOCAL_ID_DEMAND
         WHERE ROWID = lb_rowid(j);

Line: 1104

            MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 1120

            MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND  for entity_name  SALES_ORDER_ID');

Line: 1131

         DELETE FROM  MSC_LOCAL_ID_DEMAND
         WHERE ROWID = lb_rowid(j);

Line: 1143

            MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 1158

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_DEMAND  for entity_name  DEMAND_ID');

Line: 1169

         DELETE FROM  MSC_LOCAL_ID_DEMAND
         WHERE ROWID = lb_rowid(j);

Line: 1180

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 1193

         DELETE_MSC_TABLE( p_table_name  =>'MSC_SUPPLIES',
                           p_instance_id => v_instance_id,
                           p_plan_id     => -1,
                           p_sub_str     => '  and trunc(NEW_SCHEDULE_DATE) <= '||'trunc(to_date('||''''||p_date||''''||','||''''||'YYYY/MM/DD HH24:MI:SS'||''''||'))');

Line: 1205

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name  WIP_ENTITY_ID');

Line: 1214

         DELETE FROM  MSC_LOCAL_ID_SUPPLY
         WHERE ROWID = lb_rowid(j);

Line: 1225

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 1241

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name  SR_MTL_SUPPLY_ID');

Line: 1252

          DELETE FROM  MSC_LOCAL_ID_SUPPLY
          WHERE ROWID = lb_rowid(j);

Line: 1263

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 1279

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name   PO_LINE_ID');

Line: 1289

          DELETE FROM  MSC_LOCAL_ID_SUPPLY
          WHERE ROWID = lb_rowid(j);

Line: 1300

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 1315

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name  DISPOSITION_ID');

Line: 1326

          DELETE FROM  MSC_LOCAL_ID_SUPPLY
          WHERE ROWID = lb_rowid(j);

Line: 1337

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 1353

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name  DISPOSITION_ID_MPS');

Line: 1364

          DELETE FROM  MSC_LOCAL_ID_SUPPLY
          WHERE ROWID = lb_rowid(j);

Line: 1375

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'No of Rows deleted : '||lv_total);

Line: 1391

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Delete from MSC_LOCAL_ID_SUPPLY for entity_name  SCHEDULE_GROUP_ID');

Line: 1402

          DELETE FROM  MSC_LOCAL_ID_SUPPLY
          WHERE ROWID = lb_rowid(j);

Line: 1413

MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,' No of Rows deleted : '||lv_total);

Line: 1452

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

Line: 1454

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

Line: 1457

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

Line: 1459

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

Line: 1462

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

Line: 1464

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

Line: 1466

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

Line: 1467

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

Line: 1470

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

Line: 1473

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

Line: 1476

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

Line: 1478

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

Line: 1481

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

Line: 1483

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

Line: 1485

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

Line: 1487

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

Line: 1489

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

Line: 1492

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

Line: 1494

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

Line: 1496

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

Line: 1498

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

Line: 1500

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

Line: 1503

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

Line: 1505

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

Line: 1508

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

Line: 1510

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

Line: 1513

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

Line: 1516

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

Line: 1519

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

Line: 1522

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

Line: 1525

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

Line: 1527

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

Line: 1529

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

Line: 1532

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

Line: 1535

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

Line: 1538

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

Line: 1540

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

Line: 1542

      DELETE FROM MSC_SR_ASSIGNMENTS
       WHERE SR_ASSIGNMENT_INSTANCE_ID= p_instance_id;

Line: 1545

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

Line: 1547

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

Line: 1549

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

Line: 1551

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

Line: 1554

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

Line: 1557

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

Line: 1559

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

Line: 1561

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

Line: 1564

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

Line: 1567

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

Line: 1570

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

Line: 1572

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

Line: 1574

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

Line: 1577

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

Line: 1580

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

Line: 1582

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

Line: 1585

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

Line: 1588

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

Line: 1590

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

Line: 1592

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

Line: 1595

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

Line: 1597

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

Line: 1599

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

Line: 1601

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

Line: 1603

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

Line: 1606

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

Line: 1609

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

Line: 1612

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

Line: 1615

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

Line: 1618

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

Line: 1620

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

Line: 1622

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

Line: 1624

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

Line: 1626

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

Line: 1628

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

Line: 1631

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

Line: 1633

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

Line: 1637

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

Line: 1639

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

Line: 1641

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

Line: 1643

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

Line: 1645

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

Line: 1647

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

Line: 1649

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

Line: 1651

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

Line: 1653

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

Line: 1656

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

Line: 1658

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

Line: 1672

  SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK)

    INTO lv_dblink

    FROM MSC_APPS_INSTANCES

   WHERE INSTANCE_ID= pInstId;

Line: 1741

      SELECT application_short_name

        INTO lv_schema_short_nm

        FROM fnd_application

       WHERE application_id=724;

Line: 1797

  lv_qry_str :=    'SELECT table_name,partition_name '

                  ||' FROM fnd_lookup_values a,DBA_TAB_PARTITIONS b'

                  ||' WHERE a.attribute2 = b.table_name' -- (Not in MSC_%)see that meaning is there in upper case

                  ||' AND b.table_owner = :B1'

                  ||' AND a.lookup_type IN (''MSC_ODS_TABLE'',''MSC_OTHER_TABLE'')'-- see that staging table can be included here

                  ||' AND a.ATTRIBUTE11 = ''Y''' -- Column SR_INSTANCE_ID Present

                  ||' AND a.enabled_flag = ''Y'''

                  ||' AND a.view_application_id = 700'

                  ||' AND a.language = userenv(''lang'')'

                  ||' AND a.attribute5 != ''U'''   -- Table is Partitioned

                  ||' AND NVL(a.attribute13,''-1'')!=''G'''

                  ||' AND b.partition_name like substr( a.attribute2,5)||''%'''

                  --AND  INSTR(partition_name,'__') > 0

                  ||' AND SUBSTR(b.partition_name,INSTR(partition_name,''__'')+2) IN '||lv_inst_str;

Line: 1877

    SELECT REPLACE(lv_inst_str,'''','') INTO lv_inst_str  FROM DUAL;

Line: 1881

    MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'*******  Delete Rows for  Instance  *******');

Line: 1883

    FOR tab IN ( SELECT attribute2 tname,ATTRIBUTE12 Plan_Id

                   FROM fnd_lookup_values a, dba_tables b

                  WHERE a.attribute2 = b.table_name

                    AND b.owner = lv_schema

                    AND lookup_type IN ('MSC_ODS_TABLE','MSC_OTHER_TABLE')

                    AND enabled_flag = 'Y'

                    AND view_application_id = 700

                    AND language = userenv('lang')

                    AND attribute5='U'      -- Unpartitioned table

                    AND a.ATTRIBUTE11 = 'Y' -- Column SR_INSTANCE_ID Present

                    AND NVL(attribute13,'-1')<>'G') -- to check

    LOOP

      IF tab.Plan_Id = 'Y' THEN

        lv_qry_str := 'DELETE FROM '||lv_schema||'.'||tab.tname||' WHERE plan_id= -1 AND  sr_instance_id IN '||lv_inst_str||' AND ROWNUM <= '||row_limit;

Line: 1915

        lv_qry_str := 'DELETE FROM '||lv_schema||'.'||tab.tname||' WHERE sr_instance_id IN '||lv_inst_str||' AND ROWNUM <= '||row_limit;

Line: 1947

          EXIT WHEN (SQL%ROWCOUNT < row_limit) OR  (lv_err_flag = TRUE); -- Exit when Not enough rows to be deleted;

Line: 1971

        SELECT application_short_name

          INTO lv_schema_short_nm

          FROM fnd_application

         WHERE application_id=704;

Line: 2005

              lv_qry_str := 'DELETE FROM '||lv_source_schema ||'.MRP_AP_APPS_INSTANCES_ALL'||GET_M2A_DBLINK(pInstList(indx) )

                                                             ||' WHERE instance_id IN '||lv_inst_str;

Line: 2037

    BEGIN -- delete  from MSC_INST_PARTITIONS --

      lv_qry_str := 'DELETE FROM '||lv_schema||'.MSC_INST_PARTITIONS WHERE instance_id IN '||lv_inst_str;

Line: 2057

    BEGIN -- delete from MSC_APPS_INSTANCES --

      lv_qry_str := 'DELETE FROM '||lv_schema||'.MSC_APPS_INSTANCES WHERE instance_id IN '||lv_inst_str;

Line: 2147

      SELECT application_short_name

        INTO lv_schema_short_nm

        FROM fnd_application

       WHERE application_id=724;

Line: 2207

  lv_qry_str := 'SELECT table_name,partition_name '

                  ||' FROM fnd_lookup_values a,DBA_TAB_PARTITIONS b'

                  ||' WHERE a.attribute2 = b.table_name' --

                  ||' AND b.table_owner = :B1'

                  ||' AND a.lookup_type IN (''MSC_ODS_TABLE'',''MSC_PDSONLY_TABLE'',''MSC_OTHER_TABLE'')'-- see that staging table can be included here

                  ||' AND a.ATTRIBUTE12 = ''Y''' -- Column PLAN_ID Present

                  ||' AND a.enabled_flag = ''Y'''

                  ||' AND a.view_application_id = 700'

                  ||' AND a.language = userenv(''lang'')'

                  ||' AND a.attribute5 != ''U'''   -- Table is Partitioned

                  ||' AND b.partition_name like substr( a.attribute2,5)||''%'''

                  ||' AND NVL(a.attribute13,''-1'')!=''G'''

                  ||' AND LTRIM(''MSC_''||partition_name, table_name||''_'') IN '||lv_plan_str;

Line: 2277

   SELECT REPLACE(lv_plan_str,'''','') INTO lv_plan_str FROM DUAL;

Line: 2281

    MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'*******  Delete Rows for plan***********');

Line: 2285

    FOR tab IN (SELECT a.attribute2 tname

                  FROM fnd_lookup_values a,dba_tables b

                  WHERE a.attribute2 = b.table_name

                    AND b.owner = lv_schema

                    AND lookup_type IN ('MSC_ODS_TABLE','MSC_PDSONLY_TABLE','MSC_OTHER_TABLE')

                    AND enabled_flag = 'Y'

                    AND view_application_id = 700

                    AND language = userenv('lang')

                    AND attribute5 = DECODE(fnd_profile.value('MSC_SHARE_PARTITIONS'),'Y',attribute5,'U')      -- Unpartitioned table

                    AND a.ATTRIBUTE12 = 'Y' -- Column PLAN_ID Present

                    AND NVL(attribute13,'-1')<>'G')

    LOOP

     lv_qry_str  := 'DELETE FROM '||lv_schema||'.'||tab.tname||' WHERE plan_id  IN '||lv_plan_str||' AND ROWNUM <= '||row_limit;

Line: 2333

          EXIT WHEN (SQL%ROWCOUNT < row_limit) OR  (lv_err_flag = TRUE); -- Exit when Not enough rows to be deleted;