APPS.MSC_PERS_QUERIES SQL Statements

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

Line: 12

  procedure delete_from_results_table(p_query_id in number,
		p_plan_id in number) is
  BEGIN
    --KSA_DEBUG(SYSDATE,' p_query_id <> '||p_query_id,'delete_from_results_table');

Line: 16

    delete msc_pq_results
	where query_id = p_query_id
	  and plan_id = p_plan_id ;

Line: 19

  end delete_from_results_table;

Line: 21

  PROCEDURE delete_from_results_table(p_query_id IN NUMBER,
                                      p_plan_id IN NUMBER,
                                      p_detail_query_id IN NUMBER) IS
  BEGIN
    DELETE msc_pq_results
	WHERE query_id = p_query_id
	AND plan_id = p_plan_id
	AND detail_query_id =  p_detail_query_id;

Line: 29

  END delete_from_results_table;

Line: 40

   l_insert_begin varchar2(1000) := 'INSERT INTO MSC_PQ_RESULTS ('
	|| ' QUERY_ID, PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID, SUMMARY_DATA,';

Line: 42

   l_insert_end varchar2(1000) := 'created_by, creation_date, last_update_date, '||
        ' last_updated_by, last_update_login) ';

Line: 45

   l_insert_end_new  varchar2(1000) := 'sequence_id, created_by, creation_date, last_update_date, '||
        ' last_updated_by, last_update_login )  ';

Line: 59

  l_select_begin varchar2(1000) :=' select distinct '||to_char(p_query_id)
        ||', nvl(plan_id,-1), nvl(organization_id,-1), nvl(sr_instance_id,-1), ';

Line: 71

   SELECT NVL(priority,999)
   FROM msc_pq_types
   WHERE query_id= p_query_id
   AND NVL(detail_query_id,query_id) = p_detail_query_id;

Line: 78

   select group_by, sequence
   from msc_selection_criteria_v
   where folder_id = l_query_id
   and nvl(source_type, -1) = nvl(l_source_type, -1)
   and nvl(object_type, -1) = nvl(l_object_type, -1)
   and nvl(count_by, 2) = 1
   and nvl(active_flag,2) = 1
   order by sequence;

Line: 88

   SELECT distinct ATTRIBUTE_NAME group_by
   FROM MSC_WORKLIST_GROUPBY
   WHERE QUERY_ID = l_query;

Line: 93

   select source_type, object_type, sequence_id,NVL(priority,999), frequency
   from msc_pq_types
   where query_id = l_query_id;

Line: 98

   SELECT frequency
   FROM msc_pq_types
   WHERE query_id = p_query_id
   AND   detail_query_id = p_detail_query_id;

Line: 131

    l_select_begin  :=' select distinct '||to_char(p_MASTER_QUERY_ID)
        ||', nvl(plan_id,-1), nvl(organization_id,-1), nvl(sr_instance_id,-1), ';

Line: 138

    l_insert_end  := 'created_by, creation_date, last_update_date, '||
                     ' last_updated_by, last_update_login , detail_query_id,PRIORITY ) ';

Line: 148

    delete_from_results_table(p_query_id, p_plan_id);

Line: 154

       l_sql_stmt := l_insert_begin||l_item_cols||l_insert_end||
		l_select_begin||l_summary_col||l_item_cols||l_who_cols||
		' FROM '||p_item_view||
                ' where plan_id = '||p_plan_id||' and  '||p_where_clause;

Line: 162

       l_sql_stmt := l_insert_begin||l_res_cols||l_insert_end||
		l_select_begin||l_summary_col||l_res_cols||l_who_cols||
		' FROM '||p_res_view||
                ' where plan_id = '||p_plan_id||' and  '||p_where_clause;

Line: 169

       l_sql_stmt := l_insert_begin||l_supp_cols||l_insert_end||
		l_select_begin||l_summary_col||l_supp_cols||l_who_cols||
		' FROM '||p_supp_view||
                ' where plan_id = '||p_plan_id||' and  '||p_where_clause;

Line: 176

        l_sql_stmt := l_insert_begin||l_order_cols||l_insert_end||
		              l_select_begin||l_summary_col||l_Order_cols1||l_who_cols||
		              ' FROM '||p_order_view||
                      ' WHERE plan_id = '||p_plan_id||' AND  '||p_where_clause;

Line: 268

            select plan_type
                into v_plan_type
            from msc_plans
            where plan_id = p_plan_id;

Line: 276

                l_sql_stmt := l_insert_begin||'source_type ,'|| l_excp_cols || l_insert_end||
		        l_select_begin||l_detail_col||l_source_type||','||l_excp_cols || l_who_cols||
		        ' FROM '||l_view||
                ' where plan_id = '||p_plan_id||l_cp_context||' and  '||p_where_clause
		        ||' and ( source_type = 2 and exception_type = '||l_object||')';

Line: 292

                l_sql_stmt := l_insert_begin||'source_type , sequence_id, '||
                              l_excp_cols ||l_new_group_by_cols1|| --l_worklist_cols|| --l_new_group_by_cols1||
                              l_insert_end||
                              l_select_begin||l_detail_col||
                              l_source_type||','||l_seq_id||','||
                              l_excp_cols ||l_new_group_by_cols2|| --l_worklist_cols|| --l_new_group_by_cols2||
                              l_who_cols||
                              ' FROM '||l_view||' where plan_id = '||p_plan_id||
                              ' and  '||p_where_clause||
                              ' and ( source_type = 1 and exception_type = '||
                              l_object||')';

Line: 315

	    INSERT INTO MSC_PQ_RESULTS(query_id,plan_id,sr_instance_id,
	                               organization_id,	exception_type,
	                               source_type, summary_data, sequence_id,
	                               exception_count, groupby_org,
	                               INVENTORY_ITEM_ID, --groupby_supply_item,
	                               CATEGORY_ID, PLANNER_CODE,
	                               CUSTOMER_iD, SUPPLIER_ID,
	                               CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
	                               created_by,creation_date,
	                               last_updated_by, last_update_date,
	                               detail_query_id,Priority)
	    SELECT l_temp_query_id, NVL(p_plan_id,-1), sr_instance_id,
	           organization_id, exception_type,
	           source_type,	1, sequence_id,
	           COUNT(*), groupby_org,
	           INVENTORY_ITEM_ID, --groupby_supply_item,
	           CATEGORY_ID, PLANNER_CODE,
	           CUSTOMER_iD, SUPPLIER_ID,
	           CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
	           fnd_global.user_id, sysdate,
	           fnd_global.user_id, SYSDATE,
	           detail_query_id,PRIORITY
	    FROM MSC_PQ_RESULTS
	    WHERE query_id = l_temp_query_id
	    AND plan_id = p_plan_id
	    AND ((detail_query_id <> query_id
	         AND detail_query_id = l_detail_query_id)
	        OR detail_query_id = query_id)
	    GROUP BY query_id, plan_id, sr_instance_id,
	             organization_id, exception_type,
	             source_type, sequence_id,
	             groupby_org, INVENTORY_ITEM_ID,
	             CATEGORY_ID, PLANNER_CODE,
	             CUSTOMER_iD, SUPPLIER_ID,
	             CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
	             detail_query_id,PRIORITY;

Line: 428

      select plan_type
        into v_plan_type
        from msc_plans
       where plan_id = p_plan_id;

Line: 436

         l_sql_stmt := l_insert_begin||'source_type , sequence_id, '|| l_excp_cols || l_group_by_cols1 ||l_insert_end||
		l_select_begin||l_detail_col||l_source_type||','||l_seq_id||','||l_excp_cols || l_group_by_cols2 ||l_who_cols||
		' FROM '||l_view||
                ' where plan_id = '||p_plan_id||l_cp_context||' and  '||p_where_clause
		||' and ( source_type = 2 and exception_type = '||l_object||')';

Line: 449

         l_sql_stmt := l_insert_begin   || 'source_type , sequence_id, '||
                       l_excp_cols      || l_new_group_by_cols1||
                       l_group_by_cols1 || l_insert_end||
                       l_select_begin   || l_detail_col||
                       l_source_type    || ','||l_seq_id||','||
                       l_excp_cols      || l_new_group_by_cols2||
                       l_group_by_cols2 || l_who_cols||
                       ' FROM '||l_view|| ' where plan_id = '||
                       p_plan_id||' and  '||p_where_clause||
                       ' and ( source_type = 1 and exception_type = '||l_object||')';

Line: 470

	INSERT INTO MSC_PQ_RESULTS (
		query_id,
		plan_id,
 		sr_instance_id,
		organization_id,
		exception_type,
                source_type,
		summary_data,
                sequence_id,
		exception_count,
	        groupby_org, groupby_item, groupby_supply_item, groupby_category,
                groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
		char1,
		char2,
		char3,
		char4,
		char5,
		created_by,
		creation_date,
		last_updated_by,
		last_update_date)
	SELECT l_temp_query_id, --p_query_id
		nvl(p_plan_id,-1),
		-1,
		-1,
		exception_type,
                source_type,
		1,
                sequence_id,
		count(*),
	        groupby_org, groupby_item, groupby_supply_item, groupby_category,
                groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
		char1,
		char2,
		char3,
		char4,
		char5,
		fnd_global.user_id,
		sysdate,
		fnd_global.user_id,
		sysdate
	FROM MSC_PQ_RESULTS
	WHERE query_id = l_temp_query_id --p_query_id
	  and plan_id = p_plan_id
	GROUP BY query_id, plan_id, source_type,exception_type,sequence_id,
	  groupby_org, groupby_item, groupby_supply_item, groupby_category,
          groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
	  char1, char2, char3, char4, char5;

Line: 524

           l_sql_stmt := l_insert_begin||l_item_cols||l_insert_end_new||
                l_select_begin||l_summary_col||' inventory_item_id , ' || p_master_query_id||' , ' ||
                 'NULL ' ||' , ' ||
                p_sequence_id || ' , ' ||
                l_who_cols||
                ' FROM '||p_item_attributes_view||
                ' where simulation_set_id is NULL and plan_id = '||p_plan_id||' and  '||p_where_clause;

Line: 537

       l_sql_stmt := l_insert_begin|| l_shipment_cols||l_insert_end||
		l_select_begin||l_summary_col||l_shipment_cols||l_who_cols||
		' FROM '||p_shipment_view||
                ' where plan_id = '||p_plan_id||' and  '||p_where_clause ||
		' GROUP BY PLAN_ID, SHIPMENT_ID, FROM_ORG_ID, FROM_ORG_INSTANCE_ID, '||
		' TO_ORG_ID, TO_ORG_INSTANCE_ID, ORGANIZATION_ID, SR_INSTANCE_ID ';

Line: 548

    update msc_personal_queries
	set execute_flag = 1,
	    EXECUTION_DATE = TRUNC(SYSDATE)
	where query_id = l_temp_query_id ;

Line: 555

procedure update_category( ERRBUF     OUT NOCOPY VARCHAR2,
                           RETCODE    OUT NOCOPY NUMBER,
                           p_query_id IN NUMBER) IS

cursor  item_exist(p_item number, p_org number, p_inst number) IS
select 1
from msc_item_attributes
where simulation_set_id = -1
and plan_id = -1
and inventory_item_id = p_item
and organization_id = p_org
and sr_instance_id = p_inst;

Line: 569

select category_id
from msc_pq_results
where query_id = p_query_id
and  inventory_item_id = p_item
and organization_id = p_org
and sr_instance_id = p_inst
and sequence_id = p_seq;

Line: 592

select inventory_item_id, organization_id, sr_instance_id, max(sequence_id)
bulk collect into  v_item_id, v_org_id, v_inst_id, v_seq
from msc_pq_results
where query_id = p_query_id
group by inventory_item_id, organization_id, sr_instance_id;

Line: 610

     update msc_item_attributes
     set criticality_category = p_cat
     where inventory_item_id = v_item_id(j)
     and organization_id = v_org_id(j)
     and sr_instance_id = v_inst_id(j)
     and simulation_set_id = -1
     and plan_id = -1;

Line: 618

     insert into msc_item_attributes(simulation_set_id,
        inventory_item_id, organization_id,
        sr_instance_id, last_update_date,
        last_updated_by, creation_date, created_by,
        criticality_category, plan_id, updated_columns_count
        )  values
        ( -1 ,  v_item_id(j), v_org_id(j),
         v_inst_id(j), sysdate , -1,
         sysdate, -1, p_cat, -1, 1);

Line: 637

end update_category;

Line: 642

    select user_name
    into l_name
    from fnd_user
    where user_id = p_user_id;

Line: 656

    select query_name
    into l_name
    from msc_personal_queries
    where query_id = p_query_id;

Line: 670

    select query_type
    into l_name
    from msc_personal_queries
    where query_id = p_query_id;

Line: 684

  insert into msc_query (query_id, number1,
	LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
  select p_query_id, number1 ,
        sysdate, -1, sysdate, -1
  from msc_form_query
  where query_id = p_query_id;

Line: 700

   select  QUERY_ID, QUERY_NAME, DESCRIPTION,
	QUERY_TYPE, PUBLIC_FLAG, AND_OR_FLAG, EXECUTE_FLAG,
	CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
	LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PLAN_TYPE,
	AUTO_RELEASE,GROUP_ID,UPDATED_FLAG
   from msc_personal_queries
   where query_id = p_query;

Line: 709

   select QUERY_ID, SOURCE_TYPE,  OBJECT_TYPE,
	SEQUENCE_ID, AND_OR_FLAG,  CREATION_DATE,
	LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
        CUSTOMIZED_TEXT, OBJECT_TYPE_TEXT, ACTIVE_FLAG,
        DETAIL_QUERY_ID,FREQUENCY,PRIORITY
   from msc_pq_types
   where query_id = p_query;

Line: 718

   select SEQUENCE, FOLDER_ID, OBJECT_SEQUENCE_ID,
          FIELD_NAME, FIELD_TYPE,
	      HIDDEN_FROM_FIELD, CONDITION,
	 FROM_FIELD, TO_FIELD, FOLDER_OBJECT,
	 TREE_NODE, CREATION_DATE, CREATED_BY,
	 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
	 AND_OR, COUNT_BY, SEARCH_QUERY_ID,
	 SEARCH_QUERY_NAME, DEFAULT_FLAG,
	 PUBLIC_FLAG, FROM_FIELD_VALUE, TO_FIELD_VALUE,
	 OBJECT_TYPE, SOURCE_TYPE, ACTIVE_FLAG
   from msc_selection_criteria
   where folder_id = p_query;

Line: 731

   cursor c_query is select msc_personal_queries_s.nextval from dual;

Line: 735

   SELECT distinct ATTRIBUTE_NAME group_by
   FROM MSC_WORKLIST_GROUPBY
   WHERE QUERY_ID = p_query_id;

Line: 759

   INSERT INTO  msc_personal_queries( QUERY_ID, QUERY_NAME, DESCRIPTION,
	QUERY_TYPE, PUBLIC_FLAG, AND_OR_FLAG, EXECUTE_FLAG,
	CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
	LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PLAN_TYPE,
	AUTO_RELEASE,GROUP_ID,UPDATED_FLAG)
   VALUES (l_save_as_query_id, p_query_name, p_query_desc,
	rec_pers_queries.QUERY_TYPE, p_public_flag, rec_pers_queries.AND_OR_FLAG,
	2, rec_pers_queries.CREATED_BY, rec_pers_queries.CREATION_DATE,
	rec_pers_queries.LAST_UPDATE_DATE, rec_pers_queries.LAST_UPDATED_BY,
	rec_pers_queries.LAST_UPDATE_LOGIN, rec_pers_queries.PLAN_TYPE,
	rec_pers_queries.AUTO_RELEASE,rec_pers_queries.GROUP_ID,rec_pers_queries.UPDATED_FLAG);

Line: 773

     INSERT INTO MSC_PQ_TYPES (QUERY_ID, SOURCE_TYPE,  OBJECT_TYPE,
	SEQUENCE_ID, AND_OR_FLAG,  CREATION_DATE,
	LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
        CUSTOMIZED_TEXT, OBJECT_TYPE_TEXT, ACTIVE_FLAG,
        DETAIL_QUERY_ID,FREQUENCY,PRIORITY)
     VALUES (l_save_as_query_id, rec_pq_types.SOURCE_TYPE,  rec_pq_types.OBJECT_TYPE,
	rec_pq_types.SEQUENCE_ID, rec_pq_types.AND_OR_FLAG,
	rec_pq_types.CREATION_DATE, rec_pq_types.LAST_UPDATE_DATE,
	rec_pq_types.LAST_UPDATED_BY, rec_pq_types.LAST_UPDATE_LOGIN,
        rec_pq_types.CUSTOMIZED_TEXT, rec_pq_types.OBJECT_TYPE_TEXT, rec_pq_types.ACTIVE_FLAG,
        rec_pq_types.DETAIL_QUERY_ID,rec_pq_types.FREQUENCY,rec_pq_types.PRIORITY);

Line: 792

     INSERT INTO msc_selection_criteria
     (SEQUENCE, FOLDER_ID, OBJECT_SEQUENCE_ID, FIELD_NAME, FIELD_TYPE,
	HIDDEN_FROM_FIELD, CONDITION,
	FROM_FIELD, TO_FIELD, FOLDER_OBJECT,
	TREE_NODE, CREATION_DATE, CREATED_BY,
	LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
	AND_OR, COUNT_BY, SEARCH_QUERY_ID,
	SEARCH_QUERY_NAME, DEFAULT_FLAG,
	PUBLIC_FLAG, FROM_FIELD_VALUE, TO_FIELD_VALUE,
	OBJECT_TYPE, SOURCE_TYPE, ACTIVE_FLAG)
     VALUES (rec_pers_criteria.SEQUENCE, l_save_as_query_id,
             rec_pers_criteria.OBJECT_SEQUENCE_ID ,
	rec_pers_criteria.FIELD_NAME, rec_pers_criteria.FIELD_TYPE,
	rec_pers_criteria.HIDDEN_FROM_FIELD, rec_pers_criteria.CONDITION,
	rec_pers_criteria.FROM_FIELD, rec_pers_criteria.TO_FIELD,
	rec_pers_criteria.FOLDER_OBJECT, rec_pers_criteria.TREE_NODE,
	rec_pers_criteria.CREATION_DATE, rec_pers_criteria.CREATED_BY,
	rec_pers_criteria.LAST_UPDATE_DATE, rec_pers_criteria.LAST_UPDATED_BY,
	rec_pers_criteria.LAST_UPDATE_LOGIN, rec_pers_criteria.AND_OR,
	rec_pers_criteria.COUNT_BY, rec_pers_criteria.SEARCH_QUERY_ID,
	rec_pers_criteria.SEARCH_QUERY_NAME, rec_pers_criteria.DEFAULT_FLAG,
	rec_pers_criteria.PUBLIC_FLAG, rec_pers_criteria.FROM_FIELD_VALUE,
	rec_pers_criteria.TO_FIELD_VALUE,rec_pers_criteria.OBJECT_TYPE,
	rec_pers_criteria.SOURCE_TYPE, rec_pers_criteria.ACTIVE_FLAG) ;

Line: 818

   INSERT INTO MSC_WORKLIST_GROUPBY(
                                    QUERY_ID                ,
                                    ATTRIBUTE_NAME          ,
                                    CHAR1                   ,
                                    CHAR2                   ,
                                    CHAR3                   ,
                                    CHAR4                   ,
                                    SEQUENCE                ,
                                    LAST_UPDATE_DATE        ,
                                    LAST_UPDATED_BY         ,
                                    CREATION_DATE           ,
                                    CREATED_BY              )
   SELECT               l_save_as_query_id                ,
                        ATTRIBUTE_NAME          ,
                        CHAR1                   ,
                        CHAR2                   ,
                        CHAR3                   ,
                        CHAR4                   ,
                        SEQUENCE                ,
                        LAST_UPDATE_DATE        ,
                        LAST_UPDATED_BY         ,
                        CREATION_DATE           ,
                        CREATED_BY
   FROM MSC_WORKLIST_GROUPBY
   WHERE query_id = p_query_id;

Line: 865

    SELECT  count(*) into l_count
    from ALL_TAB_PARTITIONS
    where TABLE_NAME = 'MSC_PQ_RESULTS' and TABLE_OWNER= l_msc_schema;

Line: 877

      delete from msc_pq_results
       where plan_id = p_plan_id;

Line: 882

  procedure delete_query(p_query_id in number,
        p_query_name in varchar2 default null) is
  PRAGMA AUTONOMOUS_TRANSACTION;

Line: 886

    delete from msc_personal_queries
        where query_id = p_query_id;

Line: 889

    delete from msc_pq_types
        where query_id = p_query_id;

Line: 892

    delete from msc_selection_criteria
        where folder_id = p_query_id;

Line: 895

    delete from msc_among_values
        where folder_id = p_query_id;

Line: 898

    delete from msc_pq_results
        where query_id = p_query_id;

Line: 902

  end delete_query;