APPS.MSC_CL_EXCHANGE_PARTTBL SQL Statements

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

Line: 97

   SELECT a.oracle_username
     FROM FND_ORACLE_USERID a,
          FND_PRODUCT_INSTALLATIONS b
    WHERE a.oracle_id = b.oracle_id
      AND b.application_id= 724;

Line: 208

   SELECT 1
     FROM ALL_TABLES
    WHERE table_name= upper(p_tblname)
    AND owner = v_msc_schema;

Line: 247

   select value
   into  lv_block_size
   from v$parameter
   where name = 'db_block_size' ;

Line: 274

   SELECT atp.tablespace_name,
          decode(atp.initial_extent, NULL, '', ' INITIAL ' || atp.initial_extent),
          decode(atp.next_extent, NULL, '', ' NEXT ' || atp.next_extent),
          decode(atp.pct_increase, NULL, '', ' PCTINCREASE ' || atp.pct_increase),
          decode(atp.pct_free, NULL, '', ' PCTFREE ' || atp.pct_free),
          decode(atp.ini_trans, NULL, '', ' INITRANS ' || atp.ini_trans),
          decode(atp.max_trans, NULL, '', ' MAXTRANS ' || atp.max_trans),
          dt.EXTENT_MANAGEMENT,
          dt.ALLOCATION_TYPE
     FROM ALL_TAB_PARTITIONS atp ,
          dba_tablespaces dt
    WHERE atp.table_name = p_tbl
      AND atp.table_owner = p_owner
      AND atp.partition_name=p_partname
      AND atp.TABLESPACE_Name = dt.TABLESPACE_NAME;

Line: 319

      select count(*)
        into lv_part_exists
        from ALL_TAB_PARTITIONS
       where table_name = pTableName
         AND table_owner = v_msc_schema
         AND partition_name=lv_partition_name;

Line: 376

        			||' AS SELECT *'
              ||' FROM '||pTableName
        			||' WHERE NULL=NULL';

Line: 495

          EXECUTE IMMEDIATE ' update msc_coll_parameters set '
                             || v_swapTblList(i).column_name || ' = ' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_2
                             || ' where instance_id = ' || v_instance_id;

Line: 782

          EXECUTE IMMEDIATE ' update msc_coll_parameters set '
                             || v_swapTblList(j).column_name || ' = ' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_2
                             || ' where instance_id = ' || v_instance_id;

Line: 845

        EXECUTE IMMEDIATE ' select nvl(' ||v_swapTblList(i).column_name || ',' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_0 || ') from msc_coll_parameters '
                       || ' where instance_id = ' || v_instance_id
                INTO lv_swap_status;

Line: 868

                           EXECUTE IMMEDIATE ' select ' ||v_swapTblList(j).column_name || 'from msc_coll_parameters '
                                           || ' where instance_id = ' || v_instance_id
                              INTO lv_swap_status;

Line: 890

                           EXECUTE IMMEDIATE ' select ' ||v_swapTblList(j).column_name || 'from msc_coll_parameters '
                                           || ' where instance_id = ' || v_instance_id
                              INTO lv_swap_status;

Line: 903

        EXECUTE IMMEDIATE ' update msc_coll_parameters set '
                           || v_swapTblList(i).column_name || ' = ' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_0
                           || ' where instance_id = ' || v_instance_id;

Line: 1132

        EXECUTE IMMEDIATE ' select nvl(' ||v_swapTblList(i).column_name || ',' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_0 || ') from msc_coll_parameters '
                       || ' where instance_id = ' || v_instance_id
                INTO lv_swap_status;

Line: 1190

    SELECT application_short_name
    INTO   lv_msc_schema
    FROM   fnd_application
    WHERE  application_id = 724;

Line: 1210

      SELECT decode(t1.tablespace_name, NULL, '', ' TABLESPACE ' || t1.tablespace_name),
             decode(t1.pct_free, NULL, '', ' PCTFREE ' || t1.pct_free),
             decode(t1.pct_used, NULL, '', ' PCTUSED ' || t1.pct_used),
             decode(t1.initial_extent, NULL, '', ' INITIAL ' || t1.initial_extent),
             decode(t1.next_extent, NULL, '', ' NEXT ' || t1.next_extent),
             decode(t1.pct_increase, NULL, '', ' PCTINCREASE ' || t1.pct_increase)
      INTO   lv_tblspace_name, lv_pct_free, lv_pct_used, lv_init_ext, lv_nxt_ext, lv_pct_inc
      FROM   all_tab_partitions t1
      WHERE  t1.table_name = p_table_name
             AND t1.partition_name = lv_base_part
             AND t1.table_owner = lv_msc_schema
             AND NOT EXISTS (SELECT 1 FROM all_tab_partitions t2
                             WHERE t2.table_name = t1.table_name
                                   AND t2.partition_name = p_part_name
                                   AND t2.table_owner = t1.table_owner);

Line: 1285

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

Line: 1331

Description	: This procedure drops the partition for a dropped/deleted ERP
		  instance.

Parameters	: p_instance_id (IN NUMBER)
		  Instance Id of ERP instance
-----------------------------------------------------------------------------*/
PROCEDURE drop_st_partition (p_instance_id IN NUMBER) IS

lv_retval 		boolean;

Line: 1346

SELECT flv.attribute1 application_id,
       flv.attribute2 table_name,
       substr(flv.attribute2, 8) || '_' || p_instance_id partition_name
FROM   fnd_lookup_values flv
WHERE  flv.lookup_type = 'MSC_STAGING_TABLE' AND
       flv.enabled_flag = 'Y' AND
       flv.view_application_id = 700 AND
       flv.language = userenv('lang') AND
       flv.attribute5 = 'L' AND
       EXISTS (
               SELECT 1
               FROM   all_tab_partitions atp
               WHERE  atp.table_name = flv.attribute2 AND
                      atp.partition_name = substr(flv.attribute2, 8) || '_' || to_char(p_instance_id)
              );

Line: 1400

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

Line: 1457

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

Line: 1511

   SELECT ipa.index_name,
          DECODE( di.uniqueness,'UNIQUE','UNIQUE',NULL) uniqueness,
          SUBSTRB( ipa.index_name,5)||'_'||cp_inscode,
          ipa.tablespace_name,
          decode(ipa.initial_extent, NULL, '', ' INITIAL ' || ipa.initial_extent),
          decode(ipa.next_extent, NULL, '', ' NEXT ' || ipa.next_extent),
          decode(ipa.pct_increase, NULL, '', ' PCTINCREASE ' || ipa.pct_increase),
          decode(ipa.pct_free, NULL, '', ' PCTFREE ' || ipa.pct_free),
          decode(ipa.ini_trans, NULL, '', ' INITRANS ' || ipa.ini_trans),
          decode(ipa.max_trans, NULL, '', ' MAXTRANS ' || ipa.max_trans),
          di.index_type,
          dt.EXTENT_MANAGEMENT,
          dt.ALLOCATION_TYPE
     FROM ALL_IND_PARTITIONS ipa,
          ALL_INDEXES di,
          dba_tablespaces dt
     WHERE ipa.index_owner= v_msc_schema
      AND di.table_owner= v_msc_schema
      AND ipa.partition_name=cp_partname
      AND di.table_name= cp_tblname
      AND di.uniqueness= cp_uniqueness
      AND ipa.index_name= di.index_name
      AND ipa.index_owner= di.owner
      AND ipa.tablespace_name= dt.tablespace_name;

Line: 1540

   select ai.INDEX_TYPE, aic.column_name, aie.column_expression, aic.column_position
   from all_indexes ai, all_ind_columns aic, all_ind_expressions aie
   where ai.index_name = aic.index_name
   and ai.owner = aic.index_owner
   and ai.table_name = aic.table_name
   and ai.table_owner = aic.table_owner
   and aic.index_name = aie.index_name (+)
   and aic.index_owner = aie.index_owner  (+)
   and aic.table_name = aie.table_name  (+)
   and aic.table_owner = aie.table_owner  (+)
   and aic.column_position = aie.column_position  (+)
   AND aic.index_owner= p_msc_schema
   AND aic.table_owner= p_msc_schema
   AND aic.index_name = p_index_name
   AND aic.table_name = p_table_name
   order by aic.column_position;

Line: 1605

   SELECT to_number(fnd_profile.value('MSC_INDEX_PARALLEL_THREADS'))
   INTO   lv_deg_parallel
   FROM   dual;

Line: 1642

      /* select the index column order by the column position */

      IF (lv_index_type <> 'NORMAL') THEN
         lv_indexColList :=lv_indexColList1;

Line: 1670

         SELECT b.column_name
         BULK COLLECT
         INTO   lv_indColList
         FROM   ALL_IND_COLUMNS b
         WHERE  b.index_owner = v_msc_schema AND
                b.table_owner = v_msc_schema AND
                b.index_name = lv_index_name AND
                b.table_name = p_part_table
         ORDER BY b.COLUMN_POSITION;

Line: 1764

               lv_sql_stmt2 := 'SELECT ';

Line: 1795

	                  || '(SELECT 1 FROM ' || p_temp_table || ' t2 ' || lv_sql_stmt3
	                  || 't1.rowid < t2.rowid)';

Line: 1814

                  lv_sql_stmt4 := 'DELETE FROM ' || p_temp_table || ' t1 WHERE EXISTS '
	                       || '(SELECT 1 FROM ' || p_temp_table || ' t2 ' || lv_sql_stmt3
	                       || 't1.rowid < t2.rowid)';

Line: 1876

 FOR tab in (select table_name
             from MSC_STAGING_TABLE_V mst
             where partition_type <> 'U'
             and not exists (select 1 from all_tab_partitions ATP
                             where ATP.table_owner = lv_schema
                               and atp.table_name=mst.table_name
                               and partition_name like '%_DEF') )
 loop
  MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,tab.table_name||'.'||substr(tab.table_name,5)||'_DEF');

Line: 1926

    SELECT b.table_name
          ,b.partition_name
          ,b.high_value
          ,b.high_value_length
          --,SUBSTR(b.partition_name,INSTR(partition_name,'_',-1)+1) part_inst_id
    FROM MSC_STAGING_TABLE_V a,DBA_TAB_PARTITIONS b
    WHERE a.table_name = b.table_name
    AND b.table_owner = p_schema
    AND a.PARTITION_TYPE <> 'U'   -- Table is Partitioned
    ORDER BY a.table_name ;

Line: 1946

  FOR inst IN (select instance_id,instance_type from MSC_APPS_INSTANCES )
  LOOP
  if inst.instance_type <> 3 then
    lv_inst_str := lv_inst_str  ||  inst.instance_id   ||  ',' ;

Line: 1971

                for inst in (select instance_id from msc_apps_instances where instance_type = 3 ) loop
                  if instr(', '||substr(tab.high_value,1,tab.high_value_length)||',' , ', '||inst.instance_id||',') < 1  then
                  IS_BAD_PARTITION := TRUE;

Line: 2040

SELECT b.table_name
      ,b.partition_name
      ,b.high_value
      ,b.high_value_length
      ,SUBSTR(b.partition_name,INSTR(b.partition_name,'__',-1)+2) part_inst_id
      ,nvl(instance_id_flag,'N') instance_id_flag
      ,nvl(plan_id_flag,'N')     plan_id_flag
 FROM MSC_ODS_TABLE_V a,DBA_TAB_PARTITIONS b
 WHERE a.table_name = b.table_name
 AND b.table_owner = p_schema
  AND a.instance_id_flag = 'Y'
  AND a.PARTITION_TYPE <> 'U'   -- Table is Partitioned
  AND NVL(a.global_flag,'-1')<>'G'
 AND b.partition_name like substr( a.table_name,5)||'%'
 AND  INSTR(b.partition_name,'__') > 0;

Line: 2063

  FOR inst IN (select instance_id from MSC_INST_PARTITIONS )
  LOOP
    lv_inst_str := lv_inst_str  ||  inst.instance_id   ||  ',' ;

Line: 2124

select b.table_name
      ,a.instance_id
      ,nvl(instance_id_flag,'N') instance_id_flag
      ,nvl(plan_id_flag,'N') plan_id_flag
from msc_inst_partitions a,
     MSC_ODS_TABLE_V b
WHERE b.PARTITION_TYPE='R'
  AND NOT EXISTS (    select 1
                      FROM    all_tab_partitions atp
                      WHERE   atp.table_name = b.table_name AND
                              atp.table_owner = cp_schema AND
                              atp.partition_name = substr(b.table_name, 5) || '__' || to_char(a.instance_id)
                     )
                     ;

Line: 2182

   SELECT mst.application_id,
          mst.table_name,
          mai.instance_id instance_id,
          mai.instance_type instance_type
   FROM   msc_staging_table_v mst,
          msc_apps_instances mai
   WHERE  mst.table_name = cp_table AND
          mst.PARTITION_TYPE = 'L'  AND
          (
          mai.instance_type = 3 OR
          NOT EXISTS (
                      select 1
                      FROM    all_tab_partitions atp
                      WHERE   atp.table_name = mst.table_name AND
                              atp.table_owner = cp_owner    AND
                              atp.partition_name = substr(mst.table_name, 8) || '_' || to_char(mai.instance_id)
                     )
          )
  order by mst.table_name;

Line: 2203

  SELECT 1
  from all_tab_partitions
  where table_owner = cp_owner
  AND   table_name  = cp_table
  AND partition_name = substr(table_name,5)||'_LEG';

Line: 2221

for stg_tab in (select table_name from msc_staging_table_v where PARTITION_TYPE = 'L' ) loop

   open c_leg_part (lv_schema,stg_tab.table_name);

Line: 2297

For i in (select partition_name,high_value,high_value_length
            from all_tab_partitions
            where table_name = p_tab
            AND   table_owner= powner
            order by partition_position
            )
LOOP
    IF COMPARE_PARTITION_BOUND(powner, p_tab, 'TABLE', p_high_val, i.high_value)=2 THEN
      RETURN i.partition_name ;

Line: 2341

      l_sql := '    SELECT pk.column_position, pk.COLUMN_NAME, tc.DATA_TYPE
                    FROM  ALL_PART_KEY_COLUMNS pk, ALL_TAB_COLUMNS tc
                      WHERE pk.OWNER = tc.OWNER
                        AND pk.name = tc.table_name
                        AND pk.column_name = tc.column_name
                        AND pk.owner = '''||powner||''''||
                  '  AND pk.name = '''||pobject_name||''''||
                  ' ORDER BY pk.column_position ASC';

Line: 2350

        l_sql:= 'select c.column_position, a.column_name,a.data_type
                  from ALL_TAB_COLUMNS a,all_part_indexes b,all_part_key_columns c
                  where a.owner = b.owner
                   and a.table_name = b.table_name
                   and b.index_name = c.name
                   and a.column_name = c.column_name
                   and a.owner = '''||powner||''''||
                   ' and b.owner = '''||powner||''''||
                   'and c.owner = '''||powner||''''||
                  ' and b.index_name = '''||pobject_name||''''||
                 ' ORDER BY c.column_position ASC';