eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIV_HS_PROB_AVOID_REPORT_PKG dependencies on BIV_CORE_PKG

Line 24: g_v_sp VARCHAR2(5) := biv_core_pkg.g_value_sep;

20: g_query VARCHAR2(2300);
21: g_select VARCHAR2(1000);
22: g_table VARCHAR2(300);
23: g_where VARCHAR2(1000);
24: g_v_sp VARCHAR2(5) := biv_core_pkg.g_value_sep;
25: g_p_sp VARCHAR2(5) := biv_core_pkg.g_param_sep;
26: g_session_id NUMBER ;
27: g_debug_flag VARCHAR2(1) ;
28:

Line 25: g_p_sp VARCHAR2(5) := biv_core_pkg.g_param_sep;

21: g_select VARCHAR2(1000);
22: g_table VARCHAR2(300);
23: g_where VARCHAR2(1000);
24: g_v_sp VARCHAR2(5) := biv_core_pkg.g_value_sep;
25: g_p_sp VARCHAR2(5) := biv_core_pkg.g_param_sep;
26: g_session_id NUMBER ;
27: g_debug_flag VARCHAR2(1) ;
28:
29: /*** 1/30/03 this procedure is not used anymore **/

Line 33: g_prod_id := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_ID');

29: /*** 1/30/03 this procedure is not used anymore **/
30: PROCEDURE get_params (p_param_str in VARCHAR2) IS
31: BEGIN
32: /****
33: g_prod_id := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_ID');
34: g_product_version := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_VER');
35: g_component := biv_core_pkg.get_parameter_value(p_param_str,'P_COMP_ID');
36: g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');
37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');

Line 34: g_product_version := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_VER');

30: PROCEDURE get_params (p_param_str in VARCHAR2) IS
31: BEGIN
32: /****
33: g_prod_id := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_ID');
34: g_product_version := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_VER');
35: g_component := biv_core_pkg.get_parameter_value(p_param_str,'P_COMP_ID');
36: g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');
37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');
38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');

Line 35: g_component := biv_core_pkg.get_parameter_value(p_param_str,'P_COMP_ID');

31: BEGIN
32: /****
33: g_prod_id := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_ID');
34: g_product_version := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_VER');
35: g_component := biv_core_pkg.get_parameter_value(p_param_str,'P_COMP_ID');
36: g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');
37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');
38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');

Line 36: g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');

32: /****
33: g_prod_id := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_ID');
34: g_product_version := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_VER');
35: g_component := biv_core_pkg.get_parameter_value(p_param_str,'P_COMP_ID');
36: g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');
37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');
38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');
40: g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');

Line 37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');

33: g_prod_id := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_ID');
34: g_product_version := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_VER');
35: g_component := biv_core_pkg.get_parameter_value(p_param_str,'P_COMP_ID');
36: g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');
37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');
38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');
40: g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');
41: g_closed_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');

Line 38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');

34: g_product_version := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_VER');
35: g_component := biv_core_pkg.get_parameter_value(p_param_str,'P_COMP_ID');
36: g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');
37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');
38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');
40: g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');
41: g_closed_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');
42: g_closed_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_ST');

Line 39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');

35: g_component := biv_core_pkg.get_parameter_value(p_param_str,'P_COMP_ID');
36: g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');
37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');
38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');
40: g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');
41: g_closed_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');
42: g_closed_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_ST');
43: g_closed_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_END');

Line 40: g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');

36: g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');
37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');
38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');
40: g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');
41: g_closed_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');
42: g_closed_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_ST');
43: g_closed_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_END');
44: ****/

Line 41: g_closed_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');

37: g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');
38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');
40: g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');
41: g_closed_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');
42: g_closed_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_ST');
43: g_closed_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_END');
44: ****/
45: null;

Line 42: g_closed_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_ST');

38: g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');
40: g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');
41: g_closed_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');
42: g_closed_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_ST');
43: g_closed_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_END');
44: ****/
45: null;
46: END get_params;

Line 43: g_closed_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_END');

39: g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');
40: g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');
41: g_closed_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');
42: g_closed_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_ST');
43: g_closed_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_END');
44: ****/
45: null;
46: END get_params;
47:

Line 57: l_session_id number := biv_core_pkg.get_session_id;

53: l_return_num PLS_INTEGER := 0;
54: l_start_date DATE;
55: l_end_date DATE;
56: l_ttl_recs number;
57: l_session_id number := biv_core_pkg.get_session_id;
58: l_ttl_meaning fnd_lookups.meaning % type :=
59: biv_core_pkg.get_lookup_meaning('TOTAL');
60: l_param_for_drill varchar2(600);
61: BEGIN

Line 59: biv_core_pkg.get_lookup_meaning('TOTAL');

55: l_end_date DATE;
56: l_ttl_recs number;
57: l_session_id number := biv_core_pkg.get_session_id;
58: l_ttl_meaning fnd_lookups.meaning % type :=
59: biv_core_pkg.get_lookup_meaning('TOTAL');
60: l_param_for_drill varchar2(600);
61: BEGIN
62: g_debug_flag := nvl(fnd_profile.value('BIV:DEBUG'),'N');
63: g_session_id := biv_core_pkg.get_session_id;

Line 63: g_session_id := biv_core_pkg.get_session_id;

59: biv_core_pkg.get_lookup_meaning('TOTAL');
60: l_param_for_drill varchar2(600);
61: BEGIN
62: g_debug_flag := nvl(fnd_profile.value('BIV:DEBUG'),'N');
63: g_session_id := biv_core_pkg.get_session_id;
64: biv_core_pkg.g_report_type := 'HS';
65: biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');
66: -- report code
67: g_report_code := 'BIV_HS_PROB_AVOID';

Line 64: biv_core_pkg.g_report_type := 'HS';

60: l_param_for_drill varchar2(600);
61: BEGIN
62: g_debug_flag := nvl(fnd_profile.value('BIV:DEBUG'),'N');
63: g_session_id := biv_core_pkg.get_session_id;
64: biv_core_pkg.g_report_type := 'HS';
65: biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');
66: -- report code
67: g_report_code := 'BIV_HS_PROB_AVOID';
68: -- get all param

Line 65: biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');

61: BEGIN
62: g_debug_flag := nvl(fnd_profile.value('BIV:DEBUG'),'N');
63: g_session_id := biv_core_pkg.get_session_id;
64: biv_core_pkg.g_report_type := 'HS';
65: biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');
66: -- report code
67: g_report_code := 'BIV_HS_PROB_AVOID';
68: -- get all param
69: --get_params(p_param_str);

Line 70: biv_core_pkg.get_report_parameters(p_param_str);

66: -- report code
67: g_report_code := 'BIV_HS_PROB_AVOID';
68: -- get all param
69: --get_params(p_param_str);
70: biv_core_pkg.get_report_parameters(p_param_str);
71:
72: g_select := get_prob_avoid_rpt_select;
73: g_table := get_prob_avoid_rpt_table;
74: biv_core_pkg.get_where_clause(g_table,g_where);

Line 74: biv_core_pkg.get_where_clause(g_table,g_where);

70: biv_core_pkg.get_report_parameters(p_param_str);
71:
72: g_select := get_prob_avoid_rpt_select;
73: g_table := get_prob_avoid_rpt_table;
74: biv_core_pkg.get_where_clause(g_table,g_where);
75: g_where := g_where || get_prob_avoid_rpt_where;
76:
77: g_query := g_select || g_table || g_where;
78:

Line 80: biv_core_pkg.biv_debug('Parameter: '||p_param_str,

76:
77: g_query := g_select || g_table || g_where;
78:
79: if g_debug_flag = 'Y' then
80: biv_core_pkg.biv_debug('Parameter: '||p_param_str,
81: g_report_code);
82: biv_core_pkg.biv_debug(g_query,g_report_code);
83: commit;
84: end if;

Line 82: biv_core_pkg.biv_debug(g_query,g_report_code);

78:
79: if g_debug_flag = 'Y' then
80: biv_core_pkg.biv_debug('Parameter: '||p_param_str,
81: g_report_code);
82: biv_core_pkg.biv_debug(g_query,g_report_code);
83: commit;
84: end if;
85:
86: l_cur_id := DBMS_SQL.OPEN_CURSOR;

Line 92: biv_core_pkg.biv_debug('SessionId: '||g_session_id,

88: DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_report_code',g_report_code);
89:
90: -- sessionid also written to biv_debug
91: if (g_debug_flag = 'Y') then
92: biv_core_pkg.biv_debug('SessionId: '||g_session_id,
93: g_report_code);
94: end if;
95:
96: DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',g_session_id);

Line 97: biv_core_pkg.bind_all_variables(l_cur_id);

93: g_report_code);
94: end if;
95:
96: DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',g_session_id);
97: biv_core_pkg.bind_all_variables(l_cur_id);
98: dbms_sql.bind_variable(l_cur_id, ':session_id', l_session_id);
99: l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
100:
101: -- update the total percentage column

Line 108: biv_core_pkg.get_session_id

104: round((100*col12/(select sum(col10)
105: from biv_tmp_hs1 b
106: where b.report_code = ''BIV_HS_PROB_AVOID''
107: and b.session_id =
108: biv_core_pkg.get_session_id
109: )),2)
110: WHERE report_code = ''BIV_HS_PROB_AVOID'' and
111: session_id = :session_id ';
112: DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);

Line 113: DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',biv_core_pkg.get_session_id);

109: )),2)
110: WHERE report_code = ''BIV_HS_PROB_AVOID'' and
111: session_id = :session_id ';
112: DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);
113: DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',biv_core_pkg.get_session_id);
114: l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
115: DBMS_SQL.CLOSE_CURSOR(l_cur_id);
116:
117: -- UPDATE COMP SUBCOMP DESC

Line 124: col6 is not null ' using biv_core_pkg.get_session_id;

120: set col6 = (select description from mtl_system_items_vl
121: where inventory_item_id = to_number(col6) and rownum = 1)
122: WHERE report_code = ''BIV_HS_PROB_AVOID'' and
123: session_id = :session_id and
124: col6 is not null ' using biv_core_pkg.get_session_id;
125:
126: execute immediate '
127: UPDATE biv_tmp_hs1
128: set col8 = (select description from mtl_system_items_vl

Line 133: col8 is not null ' using biv_core_pkg.get_session_id;

129: where inventory_item_id = to_number(col8) and rownum = 1),
130: col14 = 1
131: WHERE report_code = ''BIV_HS_PROB_AVOID'' and
132: session_id = :session_id and
133: col8 is not null ' using biv_core_pkg.get_session_id;
134:
135: -- update drilldown link
136: l_param_for_drill := 'BIV_HS_PROB_AVOID_RES' ||
137: biv_core_pkg.g_param_sep ||

Line 137: biv_core_pkg.g_param_sep ||

133: col8 is not null ' using biv_core_pkg.get_session_id;
134:
135: -- update drilldown link
136: l_param_for_drill := 'BIV_HS_PROB_AVOID_RES' ||
137: biv_core_pkg.g_param_sep ||
138: biv_core_pkg.reconstruct_param_str;
139: update biv_tmp_hs1
140: set col9 = l_param_for_drill || 'P_PRD_ID' ||
141: biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||

Line 138: biv_core_pkg.reconstruct_param_str;

134:
135: -- update drilldown link
136: l_param_for_drill := 'BIV_HS_PROB_AVOID_RES' ||
137: biv_core_pkg.g_param_sep ||
138: biv_core_pkg.reconstruct_param_str;
139: update biv_tmp_hs1
140: set col9 = l_param_for_drill || 'P_PRD_ID' ||
141: biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||
142: biv_core_pkg.g_param_sep || 'P_COMP_ID' ||

Line 141: biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||

137: biv_core_pkg.g_param_sep ||
138: biv_core_pkg.reconstruct_param_str;
139: update biv_tmp_hs1
140: set col9 = l_param_for_drill || 'P_PRD_ID' ||
141: biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||
142: biv_core_pkg.g_param_sep || 'P_COMP_ID' ||
143: biv_core_pkg.g_value_sep || nvl(col5,biv_core_pkg.g_null) ||
144: biv_core_pkg.g_param_sep || 'P_SUBCOMP_ID' ||
145: biv_core_pkg.g_value_sep || nvl(col7,biv_core_pkg.g_null) ||

Line 142: biv_core_pkg.g_param_sep || 'P_COMP_ID' ||

138: biv_core_pkg.reconstruct_param_str;
139: update biv_tmp_hs1
140: set col9 = l_param_for_drill || 'P_PRD_ID' ||
141: biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||
142: biv_core_pkg.g_param_sep || 'P_COMP_ID' ||
143: biv_core_pkg.g_value_sep || nvl(col5,biv_core_pkg.g_null) ||
144: biv_core_pkg.g_param_sep || 'P_SUBCOMP_ID' ||
145: biv_core_pkg.g_value_sep || nvl(col7,biv_core_pkg.g_null) ||
146: biv_core_pkg.g_param_sep

Line 143: biv_core_pkg.g_value_sep || nvl(col5,biv_core_pkg.g_null) ||

139: update biv_tmp_hs1
140: set col9 = l_param_for_drill || 'P_PRD_ID' ||
141: biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||
142: biv_core_pkg.g_param_sep || 'P_COMP_ID' ||
143: biv_core_pkg.g_value_sep || nvl(col5,biv_core_pkg.g_null) ||
144: biv_core_pkg.g_param_sep || 'P_SUBCOMP_ID' ||
145: biv_core_pkg.g_value_sep || nvl(col7,biv_core_pkg.g_null) ||
146: biv_core_pkg.g_param_sep
147: where session_id = g_session_id

Line 144: biv_core_pkg.g_param_sep || 'P_SUBCOMP_ID' ||

140: set col9 = l_param_for_drill || 'P_PRD_ID' ||
141: biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||
142: biv_core_pkg.g_param_sep || 'P_COMP_ID' ||
143: biv_core_pkg.g_value_sep || nvl(col5,biv_core_pkg.g_null) ||
144: biv_core_pkg.g_param_sep || 'P_SUBCOMP_ID' ||
145: biv_core_pkg.g_value_sep || nvl(col7,biv_core_pkg.g_null) ||
146: biv_core_pkg.g_param_sep
147: where session_id = g_session_id
148: and report_code = 'BIV_HS_PROB_AVOID';

Line 145: biv_core_pkg.g_value_sep || nvl(col7,biv_core_pkg.g_null) ||

141: biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||
142: biv_core_pkg.g_param_sep || 'P_COMP_ID' ||
143: biv_core_pkg.g_value_sep || nvl(col5,biv_core_pkg.g_null) ||
144: biv_core_pkg.g_param_sep || 'P_SUBCOMP_ID' ||
145: biv_core_pkg.g_value_sep || nvl(col7,biv_core_pkg.g_null) ||
146: biv_core_pkg.g_param_sep
147: where session_id = g_session_id
148: and report_code = 'BIV_HS_PROB_AVOID';
149: -- update drilldown link complete

Line 146: biv_core_pkg.g_param_sep

142: biv_core_pkg.g_param_sep || 'P_COMP_ID' ||
143: biv_core_pkg.g_value_sep || nvl(col5,biv_core_pkg.g_null) ||
144: biv_core_pkg.g_param_sep || 'P_SUBCOMP_ID' ||
145: biv_core_pkg.g_value_sep || nvl(col7,biv_core_pkg.g_null) ||
146: biv_core_pkg.g_param_sep
147: where session_id = g_session_id
148: and report_code = 'BIV_HS_PROB_AVOID';
149: -- update drilldown link complete
150: --- Add total Row

Line 154: session_id = biv_core_pkg.get_session_id;

150: --- Add total Row
151: select count(*) into l_ttl_recs
152: from biv_tmp_hs1
153: WHERE report_code = 'BIV_HS_PROB_AVOID' and
154: session_id = biv_core_pkg.get_session_id;
155: if (l_ttl_recs > 1 /*and l_ttl_recs < biv_core_pkg.g_disp*/ ) then
156: if (g_debug_flag = 'Y') then
157: biv_core_pkg.biv_debug('Inserting Total Row','BIV_HS_PROB_AVOID');
158: end if;

Line 155: if (l_ttl_recs > 1 /and l_ttl_recs < biv_core_pkg.g_disp/ ) then

151: select count(*) into l_ttl_recs
152: from biv_tmp_hs1
153: WHERE report_code = 'BIV_HS_PROB_AVOID' and
154: session_id = biv_core_pkg.get_session_id;
155: if (l_ttl_recs > 1 /*and l_ttl_recs < biv_core_pkg.g_disp*/ ) then
156: if (g_debug_flag = 'Y') then
157: biv_core_pkg.biv_debug('Inserting Total Row','BIV_HS_PROB_AVOID');
158: end if;
159: l_param_for_drill := 'BIV_HS_PROB_AVOID_RES' ||

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

153: WHERE report_code = 'BIV_HS_PROB_AVOID' and
154: session_id = biv_core_pkg.get_session_id;
155: if (l_ttl_recs > 1 /*and l_ttl_recs < biv_core_pkg.g_disp*/ ) then
156: if (g_debug_flag = 'Y') then
157: biv_core_pkg.biv_debug('Inserting Total Row','BIV_HS_PROB_AVOID');
158: end if;
159: l_param_for_drill := 'BIV_HS_PROB_AVOID_RES' ||
160: biv_core_pkg.g_param_sep ||
161: biv_core_pkg.reconstruct_param_str;

Line 160: biv_core_pkg.g_param_sep ||

156: if (g_debug_flag = 'Y') then
157: biv_core_pkg.biv_debug('Inserting Total Row','BIV_HS_PROB_AVOID');
158: end if;
159: l_param_for_drill := 'BIV_HS_PROB_AVOID_RES' ||
160: biv_core_pkg.g_param_sep ||
161: biv_core_pkg.reconstruct_param_str;
162: insert into biv_tmp_hs1(report_code, session_id, rowno, col2, col10,
163: col14, col9)
164: select 'BIV_HS_PROB_AVOID', l_session_id, max(rowno)+1,

Line 161: biv_core_pkg.reconstruct_param_str;

157: biv_core_pkg.biv_debug('Inserting Total Row','BIV_HS_PROB_AVOID');
158: end if;
159: l_param_for_drill := 'BIV_HS_PROB_AVOID_RES' ||
160: biv_core_pkg.g_param_sep ||
161: biv_core_pkg.reconstruct_param_str;
162: insert into biv_tmp_hs1(report_code, session_id, rowno, col2, col10,
163: col14, col9)
164: select 'BIV_HS_PROB_AVOID', l_session_id, max(rowno)+1,
165: l_ttl_meaning,sum(col10),2, l_param_for_drill

Line 175: biv_core_pkg.biv_debug(g_err,'BIV_HS_PROB_AVOID');

171: WHEN OTHERS THEN
172: if (g_debug_flag = 'Y') then
173: g_err := 'Err in BIV_HS_PROB_AVOID_REPORT_PKG. ' ||
174: 'load_prob_avoid_rpt:' || substr(sqlerrm,1,500);
175: biv_core_pkg.biv_debug(g_err,'BIV_HS_PROB_AVOID');
176: end if;
177:
178: END load_prob_avoid_rpt;
179:

Line 184: biv_core_pkg.get_lookup_meaning('NA');

180: -- get select
181: FUNCTION get_prob_avoid_rpt_select RETURN VARCHAR2 IS
182: l_select_stmt varchar2(1000);
183: l_na_desc fnd_lookups.meaning%type :=
184: biv_core_pkg.get_lookup_meaning('NA');
185: BEGIN
186: l_select_stmt := 'INSERT INTO biv_tmp_hs1 (report_code, session_id,col2, ';
187: l_select_stmt := l_select_stmt || 'col4,col6,col8,col10,col12,col5,col7) ';
188: l_select_stmt := l_select_stmt || 'SELECT :g_report_code, :session_id,';

Line 254: g_session_id := biv_core_pkg.get_session_id;

250: l_end_date DATE;
251: l_new_param_str varchar2(500);
252: BEGIN
253: g_debug_flag := nvl(fnd_profile.value('BIV:DEBUG'),'N');
254: g_session_id := biv_core_pkg.get_session_id;
255: biv_core_pkg.g_report_type := 'HS';
256: biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');
257: -- report code
258: g_report_code := 'BIV_HS_PROB_AVOID_RES';

Line 255: biv_core_pkg.g_report_type := 'HS';

251: l_new_param_str varchar2(500);
252: BEGIN
253: g_debug_flag := nvl(fnd_profile.value('BIV:DEBUG'),'N');
254: g_session_id := biv_core_pkg.get_session_id;
255: biv_core_pkg.g_report_type := 'HS';
256: biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');
257: -- report code
258: g_report_code := 'BIV_HS_PROB_AVOID_RES';
259: -- get all param

Line 256: biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');

252: BEGIN
253: g_debug_flag := nvl(fnd_profile.value('BIV:DEBUG'),'N');
254: g_session_id := biv_core_pkg.get_session_id;
255: biv_core_pkg.g_report_type := 'HS';
256: biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');
257: -- report code
258: g_report_code := 'BIV_HS_PROB_AVOID_RES';
259: -- get all param
260: --get_params(p_param_str);

Line 261: biv_core_pkg.get_report_parameters(p_param_str);

257: -- report code
258: g_report_code := 'BIV_HS_PROB_AVOID_RES';
259: -- get all param
260: --get_params(p_param_str);
261: biv_core_pkg.get_report_parameters(p_param_str);
262:
263: g_select := get_prob_avoid_res_rpt_select;
264: g_table := get_prob_avoid_res_rpt_table;
265: biv_core_pkg.get_where_clause(g_table,g_where);

Line 265: biv_core_pkg.get_where_clause(g_table,g_where);

261: biv_core_pkg.get_report_parameters(p_param_str);
262:
263: g_select := get_prob_avoid_res_rpt_select;
264: g_table := get_prob_avoid_res_rpt_table;
265: biv_core_pkg.get_where_clause(g_table,g_where);
266: g_where := g_where || get_prob_avoid_res_rpt_where;
267:
268: g_query := g_select || g_table || g_where;
269:

Line 271: biv_core_pkg.biv_debug('Parameter: '||p_param_str,

267:
268: g_query := g_select || g_table || g_where;
269:
270: if g_debug_flag = 'Y' then
271: biv_core_pkg.biv_debug('Parameter: '||p_param_str,
272: g_report_code);
273: biv_core_pkg.biv_debug(g_query,g_report_code);
274: commit;
275: end if;

Line 273: biv_core_pkg.biv_debug(g_query,g_report_code);

269:
270: if g_debug_flag = 'Y' then
271: biv_core_pkg.biv_debug('Parameter: '||p_param_str,
272: g_report_code);
273: biv_core_pkg.biv_debug(g_query,g_report_code);
274: commit;
275: end if;
276:
277: l_cur_id := DBMS_SQL.OPEN_CURSOR;

Line 282: biv_core_pkg.bind_all_variables(l_cur_id);

278: DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);
279: DBMS_SQL.BIND_VARIABLE(l_cur_id,':report_code',g_report_code);
280: DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',g_session_id);
281:
282: biv_core_pkg.bind_all_variables(l_cur_id);
283: l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
284:
285: -- update the total percentage column
286: g_query := 'UPDATE biv_tmp_hs1

Line 292: biv_core_pkg.get_session_id

288: round((100*col4/(select sum(b.col2)
289: from biv_tmp_hs1 b
290: where b.report_code = ''BIV_HS_PROB_AVOID_RES''
291: and b.session_id =
292: biv_core_pkg.get_session_id
293: )),2)
294: WHERE report_code = ''BIV_HS_PROB_AVOID_RES'' and
295: session_id = :session_id ';
296: DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);

Line 297: DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',biv_core_pkg.get_session_id);

293: )),2)
294: WHERE report_code = ''BIV_HS_PROB_AVOID_RES'' and
295: session_id = :session_id ';
296: DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);
297: DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',biv_core_pkg.get_session_id);
298: l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
299: DBMS_SQL.CLOSE_CURSOR(l_cur_id);
300:
301: l_new_param_str := 'BIV_SERVICE_REQUEST' ||

Line 302: biv_core_pkg.g_param_sep ||

298: l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
299: DBMS_SQL.CLOSE_CURSOR(l_cur_id);
300:
301: l_new_param_str := 'BIV_SERVICE_REQUEST' ||
302: biv_core_pkg.g_param_sep ||
303: biv_core_pkg.reconstruct_param_str;
304: update biv_tmp_hs1
305: set col1 = l_new_param_str || 'P_RESL_CODE' ||
306: biv_core_pkg.g_value_sep ||

Line 303: biv_core_pkg.reconstruct_param_str;

299: DBMS_SQL.CLOSE_CURSOR(l_cur_id);
300:
301: l_new_param_str := 'BIV_SERVICE_REQUEST' ||
302: biv_core_pkg.g_param_sep ||
303: biv_core_pkg.reconstruct_param_str;
304: update biv_tmp_hs1
305: set col1 = l_new_param_str || 'P_RESL_CODE' ||
306: biv_core_pkg.g_value_sep ||
307: nvl(col5,biv_core_pkg.g_null) ||

Line 306: biv_core_pkg.g_value_sep ||

302: biv_core_pkg.g_param_sep ||
303: biv_core_pkg.reconstruct_param_str;
304: update biv_tmp_hs1
305: set col1 = l_new_param_str || 'P_RESL_CODE' ||
306: biv_core_pkg.g_value_sep ||
307: nvl(col5,biv_core_pkg.g_null) ||
308: biv_core_pkg.g_param_sep
309: where report_code = 'BIV_HS_PROB_AVOID_RES'
310: and session_id = g_session_id;

Line 307: nvl(col5,biv_core_pkg.g_null) ||

303: biv_core_pkg.reconstruct_param_str;
304: update biv_tmp_hs1
305: set col1 = l_new_param_str || 'P_RESL_CODE' ||
306: biv_core_pkg.g_value_sep ||
307: nvl(col5,biv_core_pkg.g_null) ||
308: biv_core_pkg.g_param_sep
309: where report_code = 'BIV_HS_PROB_AVOID_RES'
310: and session_id = g_session_id;
311: EXCEPTION

Line 308: biv_core_pkg.g_param_sep

304: update biv_tmp_hs1
305: set col1 = l_new_param_str || 'P_RESL_CODE' ||
306: biv_core_pkg.g_value_sep ||
307: nvl(col5,biv_core_pkg.g_null) ||
308: biv_core_pkg.g_param_sep
309: where report_code = 'BIV_HS_PROB_AVOID_RES'
310: and session_id = g_session_id;
311: EXCEPTION
312: WHEN OTHERS THEN

Line 316: biv_core_pkg.biv_debug(g_err,'BIV_HS_PROB_AVOID_RES');

312: WHEN OTHERS THEN
313: if (g_debug_flag = 'Y') then
314: g_err := 'Err in BIV_HS_PROB_AVOID_REPORT_PKG.' ||
315: 'load_prob_avoid_res_rpt:' || substr(sqlerrm,1,500);
316: biv_core_pkg.biv_debug(g_err,'BIV_HS_PROB_AVOID_RES');
317: end if;
318:
319: END load_prob_avoid_res_rpt;
320: