APPS.MSD_DEM_COLLECT_LEVEL_TYPES SQL Statements

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

Line: 37

	    x_sql := ' SELECT NVL(COUNT(1),0) FROM ' || x_dem_schema || '.DM_WIZ_DM_DEF '
				|| ' WHERE DATA_MODEL_TREE_ID = 15'
				|| ' AND IS_ACTIVE = 1' ;

Line: 45

			x_sql := ' SELECT NVL(COUNT(1),0) FROM ' || x_dem_schema || '.E_PLAN_TREE '
					|| ' WHERE UPPER(TABLE_NAME) LIKE  ''T_SRC_ITEM_TMPL'' '
					|| ' AND UPPER(FIELD_NAME) LIKE ''DM_ITEM_CODE'' '
					|| ' AND UPPER(E_PLAN_NAME) = ''I_ATT_1'' '
					|| ' AND UPPER(E_PLAN_TABLE_NAME) = ''T_EP_I_ATT_1'' '
					|| ' AND MODEL_VERSION = 15';

Line: 54

			x_sql := ' SELECT NVL(COUNT(1),0) FROM ' || x_dem_schema || '.E_PLAN_TREE '
					|| ' WHERE UPPER(TABLE_NAME) = ''T_SRC_ITEM_TMPL'' '
					|| ' AND UPPER(FIELD_NAME) = ''DM_ITEM_DESC'' '
					|| ' AND UPPER(E_PLAN_NAME) = ''I_ATT_1_DESC'' '
					|| ' AND MODEL_VERSION = 15';

Line: 62

			x_sql := ' SELECT NVL(COUNT(1),0) FROM ' || x_dem_schema || '.E_PLAN_TREE '
					|| ' WHERE UPPER(TABLE_NAME) LIKE  ''T_SRC_ITEM_TMPL'' '
					|| ' AND UPPER(FIELD_NAME) LIKE ''T_EP_I_ATT_1'' '
					|| ' AND MODEL_VERSION = 15';

Line: 89

procedure delete_duplicates(retcode out nocopy number)
as

cursor get_instances is
select instance_id
from msc_apps_instances
order by instance_id;

Line: 103

				l_stmt := 'delete from msd_dem_items_gtt ' ||
									' where (inventory_item_id, sr_demand_class_pk) in ' ||
									' (select sr_inventory_item_id, sr_demand_class_pk ' ||
									' from msd_dem_items_gtt ' ||
									' group by sr_inventory_item_id, sr_demand_class_pk ' ||
									' having count(*) > 1) ' ||
									' and sr_instance_id > :1';

Line: 136

select plan_type
from msc_plans
where plan_id = p_plan_id;

Line: 190

       		delete_duplicates(retcode);

Line: 193

       msd_dem_common_utilities.log_debug('Insert statement for populating temporary table for ' ||
                                                                            v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10) ||
                                                                            'Instance: ' || p_instance_id || fnd_global.local_chr(10));

Line: 231

select instance_id
from msc_apps_instances;

Line: 282

     msd_dem_common_utilities.log_debug('Insert statement for populating Demantra staging table for ' || v_collect_level_type || fnd_global.local_chr(10) || l_stmt || fnd_global.local_chr(10));

Line: 358

            SELECT 1
               FROM dual
               WHERE EXISTS (SELECT 1
                                FROM msd_dem_new_items
                                WHERE  sr_instance_id = p_sr_instance_id
                                   AND process_flag = 2);

Line: 438

        UPDATE msd_dem_new_items
           SET process_flag = 1
           WHERE  sr_instance_id = p_sr_instance_id
              AND process_flag = 2;

Line: 535

      SELECT count(*)
         INTO var_count
         FROM dba_tab_columns
         WHERE  owner = var_schema
            AND table_name = 'T_SRC_LOC_TMPL'
            AND column_name in ('ORG_TYPE_CODE', 'ORG_TYPE_DESC');

Line: 551

      SELECT count(1)
         INTO var_count
         FROM dba_tables
         WHERE  owner = var_schema
            AND table_name = 'T_EP_ORG_TYPE';

Line: 566

         EXECUTE IMMEDIATE 'SELECT org_type_code, org_type_desc FROM ' || var_schema || '.t_ep_org_type '
                           || ' WHERE t_ep_org_type_ep_id = decode(' || to_char(p_for_spf) || ' , 1, 1, 0) '
            INTO  var_org_type_code, var_org_type_desc;

Line: 589

      /* Update org type and code in t_src_loc_tmpl */
      msd_dem_common_utilities.log_message ('Updating org type and code in location staging...');

Line: 591

      EXECUTE IMMEDIATE 'UPDATE ' || var_schema || '.T_SRC_LOC_TMPL '
                        || ' SET org_type_code = ''' || var_org_type_code || ''', '
                        || '     org_type_desc = ''' || var_org_type_desc || ''' ';

Line: 610

    * This procedure updates the column t_src_item_tmpl.t_ep_i_att_10
    * For items which are already available in demantra
    *    with (members of the item_type level) from t_ep_item & t_ep_i_att_10 tables.
    * For items which are new to demantra
    *    not updated.
    *
    * This is done so that an item cannot have 2 item_type levels as parents
    *
    */
    PROCEDURE populate_item_type(errbuf        OUT NOCOPY VARCHAR2,
                           retcode             OUT NOCOPY NUMBER )
    AS

    x_dem_schema			VARCHAR2(100)		DEFAULT 	fnd_profile.value('MSD_DEM_SCHEMA');

Line: 632

      x_sql := ' select count(nvl(1,0)) from all_tables where table_name = ''T_EP_I_ATT_10'' and owner = ''' || x_dem_schema || '''' ;

Line: 643

      x_sql := ' UPDATE ' || x_dem_schema || '.t_src_item_tmpl tmpl ' ||
               ' SET t_ep_i_att_10 = NVL((SELECT it.i_att_10 ' ||
                                        'FROM ' ||
                                        x_dem_schema || '.t_ep_item tei, ' || x_dem_schema || '.t_ep_i_att_10 it ' ||
                                        'WHERE
                                        tmpl.dm_item_code = tei.item
                                        AND tei.t_ep_i_att_10_ep_id = it.t_ep_i_att_10_ep_id)
                                    ,t_ep_i_att_10) ';

Line: 655

       msd_dem_common_utilities.log_debug('Update sql for item_type level - ' || x_sql);

Line: 690

       select decode(p_collect_level_type, 1 , 'Locations', 2, 'Items') into v_collect_level_type from dual;