APPS.BIV_HS_PROB_AVOID_REPORT_PKG SQL Statements

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

Line: 21

	g_select VARCHAR2(1000);

Line: 72

		g_select := get_prob_avoid_rpt_select;

Line: 77

		g_query := g_select || g_table || g_where;

Line: 101

		-- update the total percentage column
		g_query := 'UPDATE biv_tmp_hs1
						SET col12 =
						round((100*col12/(select sum(col10)
												from biv_tmp_hs1 b
												where b.report_code = ''BIV_HS_PROB_AVOID''
												and b.session_id =
												biv_core_pkg.get_session_id
												)),2)
						WHERE report_code = ''BIV_HS_PROB_AVOID'' and
						session_id = :session_id ';

Line: 119

      UPDATE biv_tmp_hs1
      set col6 = (select description from mtl_system_items_vl
                  where inventory_item_id = to_number(col6) and rownum = 1)
		WHERE report_code = ''BIV_HS_PROB_AVOID'' and
            session_id = :session_id and
            col6 is not null ' using biv_core_pkg.get_session_id;

Line: 127

      UPDATE biv_tmp_hs1
      set col8 = (select description from mtl_system_items_vl
                  where inventory_item_id = to_number(col8) and rownum = 1),
          col14 = 1
		WHERE report_code = ''BIV_HS_PROB_AVOID'' and
            session_id = :session_id and
            col8 is not null ' using biv_core_pkg.get_session_id;

Line: 139

      update biv_tmp_hs1
         set col9 = l_param_for_drill || 'P_PRD_ID' ||
                    biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||
                    biv_core_pkg.g_param_sep || 'P_COMP_ID' ||
                    biv_core_pkg.g_value_sep || nvl(col5,biv_core_pkg.g_null) ||
                    biv_core_pkg.g_param_sep || 'P_SUBCOMP_ID' ||
                    biv_core_pkg.g_value_sep || nvl(col7,biv_core_pkg.g_null) ||
                    biv_core_pkg.g_param_sep
       where session_id = g_session_id
         and report_code = 'BIV_HS_PROB_AVOID';

Line: 151

      select count(*) into l_ttl_recs
        from biv_tmp_hs1
       WHERE report_code = 'BIV_HS_PROB_AVOID' and
            session_id = biv_core_pkg.get_session_id;

Line: 157

            biv_core_pkg.biv_debug('Inserting Total Row','BIV_HS_PROB_AVOID');

Line: 162

         insert into biv_tmp_hs1(report_code, session_id, rowno, col2, col10,
                                 col14, col9)
           select 'BIV_HS_PROB_AVOID', l_session_id, max(rowno)+1,
                  l_ttl_meaning,sum(col10),2, l_param_for_drill
             from biv_tmp_hs1
            where report_code = 'BIV_HS_PROB_AVOID'
              and session_id = l_session_id;

Line: 180

	-- get select
	FUNCTION  get_prob_avoid_rpt_select RETURN VARCHAR2 IS
		l_select_stmt varchar2(1000);

Line: 186

		l_select_stmt := 'INSERT INTO biv_tmp_hs1 (report_code, session_id,col2, ';

Line: 187

		l_select_stmt := l_select_stmt || 'col4,col6,col8,col10,col12,col5,col7) ';

Line: 188

		l_select_stmt := l_select_stmt || 'SELECT :g_report_code,  :session_id,';

Line: 189

		l_select_stmt := l_select_stmt || 'sr.inventory_item_id prod_id, ';

Line: 190

		l_select_stmt := l_select_stmt || 'substr(nvl(inv1.description,''' || l_na_desc || '''),1,50) product, ';

Line: 191

		l_select_stmt := l_select_stmt || 'sr.inv_component_id component, ';

Line: 192

		l_select_stmt := l_select_stmt || 'sr.inv_subcomponent_id subcomponent, ';

Line: 194

		l_select_stmt := l_select_stmt || '
			count(distinct sr.incident_id),
			count(distinct sr.incident_id),
                        sr.inv_component_id, sr.inv_subcomponent_id ';

Line: 198

		return l_select_stmt;

Line: 199

	END get_prob_avoid_rpt_select;

Line: 263

		g_select := get_prob_avoid_res_rpt_select;

Line: 268

		g_query := g_select || g_table || g_where;

Line: 285

		-- update the total percentage column
		g_query := 'UPDATE biv_tmp_hs1
						SET col4 =
						round((100*col4/(select sum(b.col2)
												from biv_tmp_hs1 b
												where b.report_code = ''BIV_HS_PROB_AVOID_RES''
												and b.session_id =
												biv_core_pkg.get_session_id
												)),2)
						WHERE report_code = ''BIV_HS_PROB_AVOID_RES'' and
						session_id = :session_id ';

Line: 304

                update biv_tmp_hs1
                  set col1 = l_new_param_str || 'P_RESL_CODE' ||
                             biv_core_pkg.g_value_sep ||
                             nvl(col5,biv_core_pkg.g_null) ||
                             biv_core_pkg.g_param_sep
                 where report_code = 'BIV_HS_PROB_AVOID_RES'
                   and session_id = g_session_id;

Line: 321

	-- get select
	FUNCTION  get_prob_avoid_res_rpt_select RETURN VARCHAR2 IS
		l_select_stmt varchar2(1000);

Line: 325

		l_select_stmt := 'INSERT INTO biv_tmp_hs1 (report_code,session_id,col2, ';

Line: 326

		l_select_stmt := l_select_stmt || 'col4,col5,col6) ';

Line: 327

		l_select_stmt := l_select_stmt || 'SELECT :report_code,:session_id, ';

Line: 329

		l_select_stmt := l_select_stmt || '
			count(sr.incident_id),
			count(sr.incident_id),sr.resolution_code,clr.meaning ';

Line: 332

		return l_select_stmt;

Line: 333

	END get_prob_avoid_res_rpt_select;