1 PACKAGE OPI_DBI_RPT_UTIL_PKG AS
2 /*$Header: OPIDRMFGUTS.pls 120.6 2006/02/24 03:23:00 achandak noship $ */
3
4 /*++++++++++++++++++++++++++++++++++++++++*/
5 /* Dimension bitmap values for OPI
6 /*++++++++++++++++++++++++++++++++++++++++*/
7 ORG_BMAP CONSTANT INTEGER := 1;
8 RES_BMAP CONSTANT INTEGER := 4;
9 RES_GRP_BMAP CONSTANT INTEGER := 8;
10 RES_DEPT_BMAP CONSTANT INTEGER := 16;
11 CATEGORY_BMAP CONSTANT INTEGER := 32;
12 CYCLE_COUNT_HEADER_BMAP CONSTANT INTEGER := 64;
13 CYCLE_COUNT_CLASS_BMAP CONSTANT INTEGER := 128;
14 ITEM_BMAP CONSTANT INTEGER := 256;
15 ORG_SUB_BMAP CONSTANT INTEGER := 512;
16 REASON_CODE_BMAP CONSTANT INTEGER := 1024;
17 OP_PLAN_BMAP CONSTANT INTEGER := 2048;
18 LOCATOR_BMAP CONSTANT INTEGER := 4096;
19 ITEM_GRADE_BMAP CONSTANT INTEGER := 8192;
20 ITEM_LOT_BMAP CONSTANT INTEGER := 16384;
21 TRX_REASON_BMAP CONSTANT INTEGER := 32768;
22
23 -- Identifiers for the various viewby's
24 C_VIEWBY_ORG CONSTANT VARCHAR2(50) := 'ORGANIZATION+ORGANIZATION';
25 C_VIEWBY_SUB CONSTANT VARCHAR2(50) := 'ORGANIZATION+ORGANIZATION_SUBINVENTORY';
26 C_VIEWBY_ITEM CONSTANT VARCHAR2(50) := 'ITEM+ENI_ITEM_ORG';
27 C_VIEWBY_INV_CAT CONSTANT VARCHAR2(50) := 'ITEM+ENI_ITEM_INV_CAT';
28 C_VIEWBY_ORG_LOC CONSTANT VARCHAR2(50) := 'ORGANIZATION+OPI_SUB_LOCATOR_LVL';
29 C_VIEWBY_ITEM_GRADE CONSTANT VARCHAR2(50) :=
30 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL';
31 C_VIEWBY_ITEM_LOT CONSTANT VARCHAR2(50) :=
32 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL';
33
34 -- Identifiers for the various dimensions
35 C_DIMNAME_ORG CONSTANT VARCHAR2(50) := 'ORGANIZATION+ORGANIZATION';
36 C_DIMNAME_SUB CONSTANT VARCHAR2(50) := 'ORGANIZATION+ORGANIZATION_SUBINVENTORY';
37 C_DIMNAME_ITEM CONSTANT VARCHAR2(50) := 'ITEM+ENI_ITEM_ORG';
38 C_DIMNAME_INV_CAT CONSTANT VARCHAR2(50) := 'ITEM+ENI_ITEM_INV_CAT';
39 C_DIMNAME_ORG_LOC CONSTANT VARCHAR2(50) := 'ORGANIZATION+OPI_SUB_LOCATOR_LVL';
40 C_DIMNAME_ITEM_GRADE CONSTANT VARCHAR2(50) :=
41 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL';
42 C_DIMNAME_ITEM_LOT CONSTANT VARCHAR2(50) :=
43 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL';
44
45
46 -- Identifier for value 'All' for any dimension
47 C_ALL CONSTANT VARCHAR2 (10) := 'All';
48
49 -- Associated Array for the dimension bitmaps.
50 TYPE opi_dbi_mv_bmap_rec is RECORD (mv_name VARCHAR2(32),
51 mv_bmap NUMBER);
52 TYPE opi_dbi_mv_bmap_tbl is TABLE of opi_dbi_mv_bmap_rec;
53
54 -- Array for the MV aggregation level values
55 TYPE mv_agg_lvl_rec IS RECORD(VALUE NUMBER);
56 TYPE mv_agg_lvl_tbl IS TABLE OF mv_agg_lvl_rec;
57
58 /* Generic Process Parameter function.
59 */
60 PROCEDURE process_parameters (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
61 p_view_by OUT NOCOPY VARCHAR2,
62 p_view_by_col_name OUT NOCOPY VARCHAR2,
63 p_comparison_type OUT NOCOPY VARCHAR2,
64 p_xtd OUT NOCOPY VARCHAR2,
65 p_cur_suffix OUT NOCOPY VARCHAR2,
66 p_where_clause OUT NOCOPY VARCHAR2,
67 p_mv OUT NOCOPY VARCHAR2,
68 p_join_tbl OUT NOCOPY
69 poa_dbi_util_pkg.poa_dbi_join_tbl,
70 p_mv_level_flag OUT NOCOPY VARCHAR2,
71 p_trend IN VARCHAR2,
72 p_func_area IN VaRCHAR2,
73 p_version IN VARCHAR2,
74 p_role IN VARCHAR2,
75 p_mv_set IN VARCHAR2,
76 p_mv_flag_type IN VARCHAR2 := 'NONE');
77
78
79 /*
80 For the status_sql, get the name of the viewby column.
81 */
82 FUNCTION get_view_by_col_name (p_dim_name VARCHAR2)
83 RETURN VARCHAR2;
84
85 /* Get the VIEWBY and VIEWBYID columns
86 */
87 FUNCTION get_viewby_select_clause (p_viewby IN VARCHAR2)
88 RETURN VARCHAR2;
89
90 /* Build the fact view by columns string using the join table
91 for queries using windowing.
92 */
93 FUNCTION get_fact_select_columns (p_join_tbl IN
94 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
95 RETURN VARCHAR2;
96
97 /* Return the viewby item columns, description and UOM
98 */
99 PROCEDURE get_viewby_item_columns (p_dim_name VARCHAR2,
100 p_description OUT NOCOPY VARCHAR2,
101 p_uom OUT NOCOPY VARCHAR2);
102
103 /* API called by core team during migration to set R12 migration date
104 into our log table
105 */
106 PROCEDURE set_inv_convergence_date (x_return_status OUT NOCOPY VARCHAR2);
107
108 /* Procedure called by DBI ETL's to get the R12 migration date
109 */
110 PROCEDURE get_inv_convergence_date (p_inv_migration_date OUT NOCOPY DATE);
111
112 /* Procedure to merge the convergence date into the DBI log table
113 */
114 PROCEDURE merge_inv_convergence_date (p_migration_date IN DATE);
115
116 /* percent_str
117
118 Gets the string for percentage change of two specified strings.
119 Better than copying CASE statements everywhere
120 */
121 FUNCTION percent_str (p_numerator IN VARCHAR2,
122 p_denominator IN VARCHAR2,
123 p_measure_name IN VARCHAR2)
124 RETURN VARCHAR2;
125
126 /* percent_str_basic
127
128 Gets the string for percentage change of two specified strings.
129 Better than copying CASE statements everywhere.
130 No NVLs on numerator.
131 */
132 FUNCTION percent_str_basic (p_numerator IN VARCHAR2,
133 p_denominator IN VARCHAR2,
134 p_measure_name IN VARCHAR2)
135 RETURN VARCHAR2;
136
137 /* pos_denom_percent_str
138
139 Gets the string for percentage change of two specified strings if
140 the denominator is positive and greater than 0.
141 Better than copying CASE statements everywhere.
142 */
143 FUNCTION pos_denom_percent_str (p_numerator IN VARCHAR2,
144 p_denominator IN VARCHAR2,
145 p_measure_name IN VARCHAR2)
146 RETURN VARCHAR2;
147
148 /* pos_denom_percent_str_basic
149
150 Gets the string for percentage change of two specified strings if
151 the denominator is positive and greater than 0.
152 Better than copying CASE statements everywhere.
153 No NVLs on numerator.
154 */
155 FUNCTION pos_denom_percent_str_basic (p_numerator IN VARCHAR2,
156 p_denominator IN VARCHAR2,
157 p_measure_name IN VARCHAR2)
158 RETURN VARCHAR2;
159
160 /* change_str
161 Get the percentage change string. Better than writing out all the case
162 statements
163 */
164 FUNCTION change_str (p_new_numerator IN VARCHAR2,
165 p_old_numerator IN VARCHAR2,
166 p_denominator IN VARCHAR2,
167 p_measure_name IN VARCHAR2)
168 RETURN VARCHAR2;
169
170 /* change_str_basic
171 Get the percentage change string. Better than writing out all the case
172 statements. No NVLs on numerator.
173 */
174 FUNCTION change_str_basic (p_new_numerator IN VARCHAR2,
175 p_old_numerator IN VARCHAR2,
176 p_denominator IN VARCHAR2,
177 p_measure_name IN VARCHAR2)
178 RETURN VARCHAR2;
179
180 /* change_pct_str
181 Get the change in percentage string. Better than writing out all the case
182 statements
183 */
184 FUNCTION change_pct_str (p_new_numerator IN VARCHAR2,
185 p_new_denominator IN VARCHAR2,
186 p_old_numerator IN VARCHAR2,
187 p_old_denominator IN VARCHAR2,
188 p_measure_name IN VARCHAR2)
189 RETURN VARCHAR2;
190
191 /* change_pct_str_basic
192 Get the change in percentage string. Better than writing out all the case
193 statements.
194 No NVLs on numerator.
195 */
196 FUNCTION change_pct_str_basic (p_new_numerator IN VARCHAR2,
197 p_new_denominator IN VARCHAR2,
198 p_old_numerator IN VARCHAR2,
199 p_old_denominator IN VARCHAR2,
200 p_measure_name IN VARCHAR2)
201 RETURN VARCHAR2;
202
203 /* nvl_str
204 Convert a string into its NVL (str, val)
205 The default NVL value is 0
206 */
207 FUNCTION nvl_str (p_str IN VARCHAR2,
208 p_default_val IN NUMBER := 0)
209 RETURN VARCHAR2;
210
211 /* raw_str
212 If the string is NULL, return NULL.
213 Else return itself.
214 */
215 FUNCTION raw_str (p_str IN VARCHAR2)
216 RETURN VARCHAR2;
217
218 /*
219 If the value of the string is NEGATIVE, return NULL.
220 Else return itself.
221 */
222 FUNCTION neg_str(p_str IN VARCHAR2)
223 RETURN VARCHAR2;
224
225 /* rate_str
226 Calculates a rate given a numerator and denominator;
227 p_rate_type = 'P' indicates percentage inputs
228 p_rate_type = 'NP' indicates absolute inputs
229 */
230 FUNCTION rate_str (p_numerator IN VARCHAR2,
231 p_denominator IN VARCHAR2,
232 p_rate_type IN VARCHAR2 := 'P')
233 RETURN VARCHAR2;
234
235 /* Replace a substring only once */
236 FUNCTION replace_n (p_orig_str IN VARCHAR2,
237 p_match_str IN VARCHAR2,
238 p_replace_str IN VARCHAR2 := NULL,
239 p_start_pos IN NUMBER := 1,
240 p_num_times IN NUMBER := 1)
241 RETURN VARCHAR2;
242
243
244 /* Security where clauses function
245 Making public for use in specific detail reports
246 */
247 FUNCTION get_security_where_clauses(p_org_value IN VARCHAR2, p_trend IN VARCHAR2 := 'N')
248 RETURN VARCHAR2;
249
250 -- OPI_UM_CONVERT will use in place INV_CONVERT.inv_um_convert
251 -- In inventory Intransit load
252 FUNCTION OPI_UM_CONVERT (
253 p_item_id number,
254 p_item_qty number,
255 p_from_unit varchar2,
256 p_to_unit varchar2 ) RETURN number ;
257
258 -- Variable will use in above package an will used in callin procedure too
259 g_pk_uom_conversion number;
260
261
262 END OPI_DBI_RPT_UTIL_PKG;
263
[Home] [Help]