1 PACKAGE CN_CALC_SQL_EXPS_PVT AUTHID CURRENT_USER AS
2 /*$Header: cnvcexps.pls 120.6 2007/03/14 12:56:42 kjayapau ship $*/
3
4 TYPE parent_expression_tbl_type IS TABLE OF VARCHAR2(30)
5 INDEX BY BINARY_INTEGER;
6
7 TYPE calc_expression_rec_type IS RECORD
8 (CALC_SQL_EXP_ID CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
9 NAME CN_CALC_SQL_EXPS.NAME%TYPE,
10 DESCRIPTION CN_CALC_SQL_EXPS.DESCRIPTION%TYPE,
11 STATUS CN_CALC_SQL_EXPS.STATUS%TYPE,
12 EXP_TYPE_CODE CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE);
13
14 TYPE calc_expression_tbl_type IS TABLE OF calc_expression_rec_type
15 INDEX BY BINARY_INTEGER;
16
17 TYPE expr_type_rec_type IS RECORD
18 (node_value VARCHAR2(80), -- not based off any table
19 node_label VARCHAR2(80),
20 parent_node_value VARCHAR2(80),
21 element VARCHAR2(4000));
22 -- 500 should be long enough... some elements are CLOBs
23
24 TYPE expr_type_tbl_type IS TABLE OF expr_type_rec_type
25 INDEX BY BINARY_INTEGER;
26
27 TYPE num_tbl_type IS TABLE OF number
28 INDEX BY BINARY_INTEGER;
29
30 -- Start of comments
31 -- API name : Create_Expression
32 -- Type : Private.
33 -- Function :
34 -- Pre-reqs : None.
35 -- Parameters :
36 -- IN : p_api_version IN NUMBER Required
37 -- p_init_msg_list IN VARCHAR2 Optional
38 -- Default = FND_API.G_FALSE
39 -- p_commit IN VARCHAR2 Optional
40 -- Default = FND_API.G_FALSE
41 -- p_validation_level IN NUMBER Optional
42 -- Default = FND_API.G_VALID_LEVEL_FULL
43 -- p_name IN VARCHAR2 Required
44 -- p_description IN VARCHAR2 Optional
45 -- Default = null
46 -- p_expression_disp IN VARCHAR2 Optional
47 -- Default = null
48 -- p_sql_select IN VARCHAR2 Optional
49 -- Default = null
50 -- p_sql_from IN VARCHAR2 Optional
51 -- Default = null
52 -- p_piped_expression_disp IN VARCHAR2 Optional
53 -- Default = null
54 -- p_piped_sql_select IN VARCHAR2 Optional
55 -- Default = null
56 -- p_piped_sql_from IN VARCHAR2 Optional
57 -- Default = null
58 -- OUT : x_calc_sql_exp_id OUT NUMBER
59 -- x_exp_type_code OUT VARCHAR2(30)
60 -- x_status OUT VARCHAR2(30)
61 -- x_return_status OUT VARCHAR2(1)
62 -- x_msg_count OUT NUMBER
63 -- x_msg_data OUT VARCHAR2(2000)
64 -- Version : Current version 1.0
65 -- Initial version 1.0
66 --
67 -- Notes : Create SQL expressions that will be used in
68 -- calculation.
69 -- 1) Validate the expression and return the result in
70 -- x_status (Valid or Invalid)
71 -- 2) Classify expressions into sub types for formula
72 -- validation and dynamic rate table validation
73 -- 3) If there are embedded expressions, record the
74 -- embedding relations in cn_calc_edges
75 --
76 -- End of comments
77
78 PROCEDURE Create_Expression
79 (p_api_version IN NUMBER ,
80 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
81 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
82 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
83 p_org_id IN CN_CALC_SQL_EXPS.ORG_ID%TYPE,
84 p_name IN CN_CALC_SQL_EXPS.NAME%TYPE,
85 p_description IN CN_CALC_SQL_EXPS.DESCRIPTION%TYPE := NULL,
86 p_expression_disp IN VARCHAR2 := NULL, -- CLOBs
87 p_sql_select IN VARCHAR2 := NULL,
88 p_sql_from IN VARCHAR2 := NULL,
89 p_piped_expression_disp IN VARCHAR2 := NULL,
90 p_piped_sql_select IN VARCHAR2 := NULL,
91 p_piped_sql_from IN VARCHAR2 := NULL,
92 x_calc_sql_exp_id IN OUT NOCOPY CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
93 x_exp_type_code OUT NOCOPY CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE,
94 x_status OUT NOCOPY CN_CALC_SQL_EXPS.STATUS%TYPE,
95 x_return_status OUT NOCOPY VARCHAR2 ,
96 x_msg_count OUT NOCOPY NUMBER ,
97 x_msg_data OUT NOCOPY VARCHAR2 ,
98 x_object_version_number OUT NOCOPY CN_CALC_SQL_EXPS.OBJECT_VERSION_NUMBER%TYPE);
99
100 -- Start of comments
101 -- API name : Update_Expressions
102 -- Type : Private.
103 -- Function :
104 -- Pre-reqs : None.
105 -- Parameters :
106 -- IN : p_api_version IN NUMBER Required
107 -- p_init_msg_list IN VARCHAR2 Optional
108 -- Default = FND_API.G_FALSE
109 -- p_commit IN VARCHAR2 Optional
110 -- Default = FND_API.G_FALSE
111 -- p_validation_level IN NUMBER Optional
112 -- Default = FND_API.G_VALID_LEVEL_FULL
113 -- p_update_parent_also IN VARCHAR2 Optional
114 -- Default = FND_API.G_FALSE
115 -- p_calc_sql_exp_id IN NUMBER Required
116 -- p_name IN VARCHAR2 Required
117 -- p_description IN VARCHAR2 Optional
118 -- Default = null
119 -- p_expression_disp IN VARCHAR2 Optional
120 -- Default = null
121 -- p_sql_select IN VARCHAR2 Optional
122 -- Default = null
123 -- p_sql_from IN VARCHAR2 Optional
124 -- Default = null
125 -- p_piped_expression_disp IN VARCHAR2 Optional
126 -- Default = null
127 -- p_piped_sql_select IN VARCHAR2 Optional
128 -- Default = null
129 -- p_piped_sql_from IN VARCHAR2 Optional
130 -- Default = null
131 -- p_ovn IN NUMBER Required
132 -- OUT : x_exp_type_code OUT VARCHAR2(30)
133 -- x_status OUT VARCHAR2(30)
134 -- x_return_status OUT VARCHAR2(1)
135 -- x_msg_count OUT NUMBER
136 -- x_msg_data OUT VARCHAR2(2000)
137 -- Version : Current version 1.0
138 -- Initial version 1.0
139 --
140 -- Notes : Update SQL expressions that will be used in
141 -- calculation.
142 -- 1) validate the expression and return the result in
143 -- x_status (Valid or Invalid)
144 -- 2) re-classify expressions into sub types for formula
145 -- validation and dynamic rate table validation
146 -- 3) adjust the corresponding embedding relations in
147 -- cn_calc_edges
148 -- 4) if the expression is used, update the parent
149 -- expressions, formulas accordingly
150 --
151 -- End of comments
152
153 PROCEDURE Update_Expression
154 (p_api_version IN NUMBER ,
155 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
156 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
157 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
158 p_update_parent_also IN VARCHAR2 := fnd_api.g_false ,
159 p_org_id IN CN_CALC_SQL_EXPS.ORG_ID%TYPE,
160 p_calc_sql_exp_id IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
161 p_name IN CN_CALC_SQL_EXPS.NAME%TYPE,
162 p_description IN CN_CALC_SQL_EXPS.DESCRIPTION%TYPE := NULL,
163 p_expression_disp IN VARCHAR2 := NULL, -- CLOBs
164 p_sql_select IN VARCHAR2 := NULL,
165 p_sql_from IN VARCHAR2 := NULL,
166 p_piped_expression_disp IN VARCHAR2 := NULL,
167 p_piped_sql_select IN VARCHAR2 := NULL,
168 p_piped_sql_from IN VARCHAR2 := NULL,
169 p_ovn IN OUT NOCOPY CN_CALC_SQL_EXPS.OBJECT_VERSION_NUMBER%TYPE,
170 x_exp_type_code OUT NOCOPY CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE,
171 x_status OUT NOCOPY CN_CALC_SQL_EXPS.STATUS%TYPE,
172 x_return_status OUT NOCOPY VARCHAR2 ,
173 x_msg_count OUT NOCOPY NUMBER ,
174 x_msg_data OUT NOCOPY VARCHAR2 );
175
176 -- Start of comments
177 -- API name : Delete_Expression
178 -- Type : Private.
179 -- Function :
180 -- Pre-reqs : None.
181 -- Parameters :
182 -- IN : p_api_version IN NUMBER Required
183 -- p_init_msg_list IN VARCHAR2 Optional
184 -- Default = FND_API.G_FALSE
185 -- p_commit IN VARCHAR2 Optional
186 -- Default = FND_API.G_FALSE
187 -- p_validation_level IN NUMBER Optional
188 -- Default = FND_API.G_VALID_LEVEL_FULL
189 -- p_calc_sql_exp_id IN NUMBER
190 -- OUT : x_return_status OUT VARCHAR2(1)
191 -- x_msg_count OUT NUMBER
192 -- x_msg_data OUT VARCHAR2(2000)
193 -- Version : Current version 1.0
194 -- Initial version 1.0
195 --
196 -- Notes : Delete an expression
197 -- 1) if it is used, it can not be deleted
198 -- 2) delete the embedding relations in cn_calc_edges
199 -- if there is any
200 --
201 -- End of comments
202
203 PROCEDURE Delete_Expression
204 (p_api_version IN NUMBER ,
205 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
206 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
207 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
208 p_calc_sql_exp_id IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
209 x_return_status OUT NOCOPY VARCHAR2 ,
210 x_msg_count OUT NOCOPY NUMBER ,
211 x_msg_data OUT NOCOPY VARCHAR2 );
212
213 -- Start of comments
214 -- API name : Get_Parent_Expressions
215 -- Type : Private.
216 -- Function :
217 -- Pre-reqs : None.
218 -- Parameters :
219 -- IN : p_api_version IN NUMBER Required
220 -- p_init_msg_list IN VARCHAR2 Optional
221 -- Default = FND_API.G_FALSE
222 -- p_commit IN VARCHAR2 Optional
223 -- Default = FND_API.G_FALSE
224 -- p_validation_level IN NUMBER Optional
225 -- Default = FND_API.G_VALID_LEVEL_FULL
226 -- p_calc_sql_exp_id IN NUMBER
227 -- OUT : x_parents_tbl OUT expression_tbl_type
228 -- x_return_status OUT VARCHAR2(1)
229 -- x_msg_count OUT NUMBER
230 -- x_msg_data OUT VARCHAR2(2000)
231 -- Version : Current version 1.0
232 -- Initial version 1.0
233 --
234 -- Notes : Get parent expressions if there is any
235 --
236 -- End of comments
237 /*PROCEDURE Get_Parent_Expressions
238 (p_api_version IN NUMBER ,
239 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
240 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
241 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
242 p_calc_sql_exp_id IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
243 x_parents_tbl OUT NOCOPY parent_expression_tbl_type ,
244 x_return_status OUT NOCOPY VARCHAR2 ,
245 x_msg_count OUT NOCOPY NUMBER ,
246 x_msg_data OUT NOCOPY VARCHAR2 );*/
247
248 -- determine the expression type given its SQL statement.
249 PROCEDURE classify_expression
250 (p_org_id IN CN_CALC_SQL_EXPS.ORG_ID%TYPE,
251 p_sql_select IN VARCHAR2, -- CLOBs
255 x_status IN OUT NOCOPY CN_CALC_SQL_EXPS.STATUS%TYPE,
252 p_sql_from IN VARCHAR2,
253 p_piped_sql_select IN VARCHAR2,
254 p_piped_sql_from IN VARCHAR2,
256 x_exp_type_code IN OUT NOCOPY CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE,
257 x_msg_count OUT NOCOPY NUMBER,
258 x_msg_data OUT NOCOPY VARCHAR2);
259
260 -- translate the usage code of an expression to get its meaning
261 PROCEDURE get_usage_info
262 (p_exp_type_code IN CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE,
263 x_usage_info OUT NOCOPY VARCHAR2);
264
265 -- populate the summary screen
266 /*PROCEDURE get_expr_summary
267 (p_first IN NUMBER,
268 p_last IN NUMBER,
269 p_srch_name IN VARCHAR2 := '%',
270 x_total_rows OUT NOCOPY NUMBER,
271 x_result_tbl OUT NOCOPY calc_expression_tbl_type);*/
272
273 -- populate the details for an expression
274 /*PROCEDURE get_expr_detail
275 (p_calc_sql_exp_id IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
276 x_name OUT NOCOPY CN_CALC_SQL_EXPS.NAME%TYPE,
277 x_description OUT NOCOPY CN_CALC_SQL_EXPS.DESCRIPTION%TYPE,
278 x_status OUT NOCOPY CN_CALC_SQL_EXPS.STATUS%TYPE,
279 x_exp_type_code OUT NOCOPY CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE,
280 x_expression_disp OUT NOCOPY VARCHAR2, -- CLOBs
281 x_sql_select OUT NOCOPY VARCHAR2,
282 x_sql_from OUT NOCOPY VARCHAR2,
283 x_piped_sql_select OUT NOCOPY VARCHAR2,
284 x_piped_sql_from OUT NOCOPY VARCHAR2,
285 x_piped_expression_disp OUT NOCOPY VARCHAR2,
286 x_ovn OUT NOCOPY CN_CALC_SQL_EXPS.OBJECT_VERSION_NUMBER%TYPE);*/
287
288 -- get all the types of elements that can be included in expressions. the
289 -- types are arranged in a tree structure. each type has a value, a label
290 -- and a parent value, as well as the element that gets included in the
291 -- expression value and SQL statement.
292 /*PROCEDURE get_type_tree
293 (x_types OUT NOCOPY expr_type_tbl_type);*/
294
295 -- parse a sql select statement looking for included plan elements
296 -- of the form (1234PE.COLUMN_NAME). if any are found, include them in
297 -- the x_plan_elt_tbl and provide a parsed version of the sql select.
298 PROCEDURE parse_plan_elements
299 (p_sql_select IN VARCHAR2,
300 x_plan_elt_tbl OUT NOCOPY num_tbl_type,
301 x_parsed_sql_select OUT NOCOPY VARCHAR2);
302
303 -- given a plan element, formula, or expression, determine all the plan
304 -- elements referenced directly or indirectly
305 -- pass in a node type (formula=F, plan element=P, expression=E), and the ID
306 PROCEDURE get_dependent_plan_elts
307 (p_api_version IN NUMBER ,
308 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
309 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
310 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
311 p_node_type IN VARCHAR2,
312 p_node_id IN NUMBER,
313 x_plan_elt_id_tbl OUT NOCOPY num_tbl_type,
314 x_return_status OUT NOCOPY VARCHAR2 ,
315 x_msg_count OUT NOCOPY NUMBER ,
316 x_msg_data OUT NOCOPY VARCHAR2 );
317
318 -- given a plan element, formula, or expression, determine all the plan
319 -- elements that reference it directly or indirectly
320 -- pass in a node type (formula=F, plan element=P, expression=E), and the ID
321 PROCEDURE get_parent_plan_elts
322 (p_api_version IN NUMBER ,
323 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
324 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
325 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
326 p_node_type IN VARCHAR2,
327 p_node_id IN NUMBER,
328 x_plan_elt_id_tbl OUT NOCOPY num_tbl_type,
329 x_return_status OUT NOCOPY VARCHAR2 ,
330 x_msg_count OUT NOCOPY NUMBER ,
331 x_msg_data OUT NOCOPY VARCHAR2 );
332
333
334 -- import expressions
335 PROCEDURE import
336 (errbuf OUT NOCOPY VARCHAR2,
337 retcode OUT NOCOPY VARCHAR2,
338 p_imp_header_id IN NUMBER,
339 p_org_id IN NUMBER);
340
341 -- export
342 PROCEDURE Export
343 (errbuf OUT NOCOPY VARCHAR2,
344 retcode OUT NOCOPY VARCHAR2,
345 p_imp_header_id IN NUMBER,
346 p_org_id IN NUMBER);
347
348 PROCEDURE duplicate_expression
349 (p_api_version IN NUMBER ,
350 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
351 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
355 x_new_expr_name OUT NOCOPY CN_CALC_SQL_EXPS.NAME%TYPE,
352 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
353 p_old_expr_id IN NUMBER,
354 x_new_expr_id OUT NOCOPY NUMBER,
356 x_return_status OUT NOCOPY VARCHAR2,
357 x_msg_count OUT NOCOPY NUMBER,
358 x_msg_data OUT NOCOPY VARCHAR2);
359
360 END CN_CALC_SQL_EXPS_PVT;
[Home] [Help]