1 PACKAGE FEM_HIER_UTILS_PVT AS
2 /* $Header: FEMVDHUS.pls 120.4 2007/07/18 11:58:33 gdonthir ship $ */
3
4
5 /*TYPE g_children_rec_type is RECORD
6 (
7 childids number,
8 childdepthnums number,
9 childvaluesetids number,
10 displayordernum number,
11 weightingpct number
12 );
13
14 TYPE g_children_tbl_type IS TABLE OF g_children_rec_type
15 INDEX BY BINARY_INTEGER;
16
17 TYPE g_base_rec_type is RECORD
18 (
19 childids number,
20 childvaluesetids number
21 );
22
23 TYPE g_base_tbl_type IS TABLE OF g_base_rec_type
24 INDEX BY BINARY_INTEGER; */
25
26
27
28
29 PROCEDURE Flatten_Focus_Node
30 (
31 p_api_version IN NUMBER ,
32 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
33 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
34 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
35 x_return_status OUT NOCOPY VARCHAR2 ,
36 x_msg_count OUT NOCOPY NUMBER ,
37 x_msg_data OUT NOCOPY VARCHAR2 ,
38 --
39 p_hier_obj_defn_id IN NUMBER,
40 p_hier_table_name IN VARCHAR2,
41 p_focus_node IN NUMBER ,
42 p_focus_value_set_id IN NUMBER
43 );
44
45 PROCEDURE Unflatten_Focus_Node_Tree
46 (
47 p_api_version IN NUMBER ,
48 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
49 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
50 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
51 x_return_status OUT NOCOPY VARCHAR2 ,
52 x_msg_count OUT NOCOPY NUMBER ,
53 x_msg_data OUT NOCOPY VARCHAR2 ,
54 --
55 p_hier_obj_defn_id IN NUMBER,
56 p_hier_table_name IN VARCHAR2,
57 p_focus_node IN NUMBER ,
58 p_focus_value_set_id IN NUMBER,
59 p_parent_id IN NUMBER,
60 p_parent_value_set_id IN NUMBER,
61 p_imm_child_id IN NUMBER,
62 p_imm_child_value_set_id IN NUMBER,
63 p_operation IN VARCHAR2
64 );
65
66 PROCEDURE Flatten_Focus_Node_Tree
67 (
68 p_api_version IN NUMBER ,
69 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
70 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
71 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
72 x_return_status OUT NOCOPY VARCHAR2 ,
73 x_msg_count OUT NOCOPY NUMBER ,
74 x_msg_data OUT NOCOPY VARCHAR2 ,
75 --
76 p_hier_obj_defn_id IN NUMBER,
77 p_hier_table_name IN VARCHAR2,
78 p_focus_node IN NUMBER ,
79 p_focus_value_set_id IN NUMBER
80 );
81
82 PROCEDURE Flatten_Whole_Hier_Version
83 (
84 p_api_version IN NUMBER ,
85 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
86 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
87 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
88 x_return_status OUT NOCOPY VARCHAR2 ,
89 x_msg_count OUT NOCOPY NUMBER ,
90 x_msg_data OUT NOCOPY VARCHAR2 ,
91 --
92 p_hier_obj_defn_id IN NUMBER
93 );
94
95 PROCEDURE Flatten_Whole_Hier_Version_CP
96 (
97 errbuf OUT NOCOPY VARCHAR2 ,
98 retcode OUT NOCOPY VARCHAR2 ,
99 --
100 p_hierarchy_id IN NUMBER ,
101 p_hier_obj_defn_id IN NUMBER
102 );
103
104
105 PROCEDURE Flatten_Focus_Node_CP
106 (
107 errbuf OUT NOCOPY VARCHAR2 ,
108 retcode OUT NOCOPY VARCHAR2 ,
109 --
110 p_hier_obj_defn_id IN NUMBER ,
111 p_hier_table_name IN VARCHAR2 ,
112 p_focus_node IN NUMBER ,
113 p_focus_value_set_id IN NUMBER ,
114 p_parent_id IN NUMBER ,
115 p_parent_value_set_id IN NUMBER ,
116 p_imm_child_id IN NUMBER ,
117 p_imm_child_value_set_id IN NUMBER ,
118 p_operation IN VARCHAR2
119 );
120
121 PROCEDURE insert_root_node (
122 p_api_version IN NUMBER ,
123 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
124 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
125 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
126 p_return_status OUT NOCOPY VARCHAR2 ,
127 p_msg_count OUT NOCOPY NUMBER ,
128 p_msg_data OUT NOCOPY VARCHAR2 ,
129 p_rowid IN OUT NOCOPY VARCHAR2,
130 p_vs_required_flag IN VARCHAR2,
131 p_hier_table_name IN VARCHAR2,
132 p_hier_obj_def_id IN NUMBER,
133 p_parent_depth_num IN NUMBER,
134 p_parent_id IN NUMBER,
135 p_parent_value_set_id IN NUMBER,
136 p_child_depth_num IN NUMBER,
137 p_child_id IN NUMBER,
138 p_child_value_set_id IN NUMBER,
139 p_single_depth_flag IN VARCHAR2,
140 p_display_order_num IN NUMBER,
141 p_weighting_pct IN NUMBER );
142
143 PROCEDURE launch_dup_hier_process(ERRBUFF IN OUT NOCOPY VARCHAR2,
144 RETCODE IN OUT NOCOPY VARCHAR2,
145 p_hier_table_name IN VARCHAR2,
146 p_src_hier_obj_id IN NUMBER,
147 p_dest_hier_name IN VARCHAR2,
148 p_dest_hier_desc in VARCHAR2,
149 p_dest_hier_folder_id IN NUMBER,
150 p_src_hier_version_id IN NUMBER,
151 p_dest_version_name IN VARCHAR2,
152 p_dest_version_desc IN VARCHAR2,
153 p_dest_start_date IN VARCHAR2,
154 p_dest_end_date IN VARCHAR2);
155
156 PROCEDURE duplicate_hierarchy (
157 p_api_version IN NUMBER ,
158 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
159 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
160 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
161 p_return_status OUT NOCOPY VARCHAR2 ,
162 p_msg_count OUT NOCOPY NUMBER ,
163 p_msg_data OUT NOCOPY VARCHAR2 ,
164 p_hier_table_name IN VARCHAR2,
165 p_src_hier_obj_id IN NUMBER,
166 p_dest_hier_name IN VARCHAR2,
167 p_dest_hier_desc IN VARCHAR2,
168 p_dest_hier_folder_id IN NUMBER,
169 p_src_hier_version_id IN NUMBER,
170 p_dest_version_name IN VARCHAR2,
171 p_dest_version_desc IN VARCHAR2,
172 p_dest_start_date IN DATE,
173 p_dest_end_date IN DATE);
174
175
176 PROCEDURE launch_dup_hier_ver_process(ERRBUFF IN OUT NOCOPY VARCHAR2,
177 RETCODE IN OUT NOCOPY VARCHAR2,
178 p_hier_table_name IN VARCHAR2,
179 p_src_hier_obj_id IN NUMBER,
180 p_src_hier_version_id IN NUMBER,
181 p_dest_version_name IN VARCHAR2,
182 p_dest_version_desc IN VARCHAR2,
183 p_dest_start_date IN VARCHAR2,
184 p_dest_end_date IN VARCHAR2);
185
186
187 PROCEDURE duplicate_hier_version(
188 p_api_version IN NUMBER ,
189 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
190 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
191 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
192 p_return_status OUT NOCOPY VARCHAR2 ,
193 p_msg_count OUT NOCOPY NUMBER ,
194 p_msg_data OUT NOCOPY VARCHAR2 ,
195 p_hier_table_name IN VARCHAR2,
196 p_src_hier_obj_id IN NUMBER,
197 p_src_hier_version_id IN NUMBER,
198 p_dest_hier_obj_id IN NUMBER,
199 p_dest_version_name IN VARCHAR2,
200 p_dest_version_desc IN VARCHAR2,
201 p_dest_start_date IN DATE,
202 p_dest_end_date IN DATE);
203
204 PROCEDURE launch_del_hier_process(ERRBUFF IN OUT NOCOPY VARCHAR2,
205 RETCODE IN OUT NOCOPY VARCHAR2,
206 p_hier_table_name IN VARCHAR2,
207 p_hier_obj_id IN NUMBER);
208
209 PROCEDURE delete_hierarchy(
210 p_api_version IN NUMBER ,
211 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
212 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
213 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
214 p_return_status OUT NOCOPY VARCHAR2 ,
215 p_msg_count OUT NOCOPY NUMBER ,
216 p_msg_data OUT NOCOPY VARCHAR2 ,
217 p_hier_table_name IN VARCHAR2,
218 p_hier_obj_id IN NUMBER );
219
220 PROCEDURE launch_del_hier_ver_process(ERRBUFF IN OUT NOCOPY VARCHAR2,
221 RETCODE IN OUT NOCOPY VARCHAR2,
222 p_hier_table_name IN VARCHAR2,
223 p_hier_obj_id IN NUMBER,
224 p_hier_version_id IN NUMBER);
225
226 PROCEDURE delete_hier_version(
227 p_api_version IN NUMBER ,
228 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
229 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
230 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
231 p_return_status OUT NOCOPY VARCHAR2 ,
232 p_msg_count OUT NOCOPY NUMBER ,
233 p_msg_data OUT NOCOPY VARCHAR2 ,
234 p_hier_table_name IN VARCHAR2,
235 p_hier_obj_id IN NUMBER,
236 p_hier_version_id IN NUMBER);
237
238 PROCEDURE Remove_Nodes
239 (
240 p_api_version IN NUMBER,
241 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
242 p_commit IN VARCHAR2 := FND_API.G_FALSE,
243 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 --
248 p_version_id IN NUMBER,
249 p_member_id IN NUMBER,
250 p_value_set_id IN NUMBER,
251 p_hier_table_name IN VARCHAR2,
252 p_value_set_required_flag IN VARCHAR2,
253 p_flatten_rows_flag IN VARCHAR2,
254 p_user_id IN NUMBER
255 );
256
257 PROCEDURE Move_Nodes
258 (
259 p_api_version IN NUMBER,
260 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
261 p_commit IN VARCHAR2 := FND_API.G_FALSE,
262 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2,
266 --
267 p_version_id IN NUMBER,
268 p_source_member_id IN NUMBER,
269 p_source_value_set_id IN NUMBER,
270 p_dest_member_id IN NUMBER,
271 p_dest_value_set_id IN NUMBER,
272 p_hier_table_name IN VARCHAR2,
273 p_value_set_required_flag IN VARCHAR2,
274 p_flatten_rows_flag IN VARCHAR2,
275 p_user_id IN NUMBER
276 );
277
278 PROCEDURE Add_Nodes
279 (
280 p_api_version IN NUMBER,
281 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
282 p_commit IN VARCHAR2 := FND_API.G_FALSE,
283 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_msg_data OUT NOCOPY VARCHAR2,
287 --
288 p_version_id IN NUMBER,
289 p_parent_member_id IN NUMBER,
290 p_parent_value_set_id IN NUMBER,
291 p_child_members IN FEM_DHM_MEMBER_TAB_TYP,
292 p_user_id IN NUMBER,
293 p_hier_table_name IN VARCHAR2,
294 p_value_set_required_flag IN VARCHAR2,
295 p_flatten_rows_flag IN VARCHAR2
296 );
297
298 PROCEDURE Add_Root_Nodes
299 (
300 p_api_version IN NUMBER,
301 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
302 p_commit IN VARCHAR2 := FND_API.G_FALSE,
303 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2,
307 --
308 p_version_id IN NUMBER,
309 p_child_members IN FEM_DHM_MEMBER_TAB_TYP,
310 p_user_id IN NUMBER,
311 p_hier_table_name IN VARCHAR2,
312 p_value_set_required_flag IN VARCHAR2
313 );
314
315 procedure Hier_Member_Sequence_Update (
316 p_api_version IN NUMBER ,
317 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
318 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
319 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
320 p_return_status OUT NOCOPY VARCHAR2 ,
321 p_msg_count OUT NOCOPY NUMBER ,
322 p_msg_data OUT NOCOPY VARCHAR2 ,
323 --
324 p_new_display_order_num IN NUMBER ,
325 p_hierarchy_table_name IN VARCHAR2 ,
326 p_hierarchy_obj_def_id IN NUMBER ,
327 p_parent_id IN NUMBER ,
328 p_parent_value_set_id IN NUMBER ,
329 p_child_id IN NUMBER ,
330 p_child_value_set_id IN NUMBER
331 );
332
333 FUNCTION Is_Lowest_Group
334 (
335 p_hierarchy_obj_id IN NUMBER,
336 p_dimension_group_id IN NUMBER
337 )
338 RETURN VARCHAR2;
339
340 FUNCTION Is_Highest_Group
341 (
342 p_hierarchy_obj_id IN NUMBER,
343 p_dimension_group_id IN NUMBER
344 )
345 RETURN VARCHAR2;
346
347 FUNCTION Is_Reorder_Allowed
348 (
349 p_dimension_id IN NUMBER,
350 p_hierarchy_obj_id IN NUMBER,
351 p_hierarchy_obj_def_id IN NUMBER,
352 p_member_id IN NUMBER := 0,
353 p_value_set_id IN NUMBER,
354 p_user_mode IN VARCHAR2,
355 p_comp_dim_flag IN VARCHAR2 := 'N'
356 )
357 RETURN VARCHAR2;
358
359 PROCEDURE Add_Relation(
360 p_api_version IN NUMBER,
361 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
362 p_commit IN VARCHAR2 := FND_API.G_FALSE,
363 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_count OUT NOCOPY NUMBER,
366 x_msg_data OUT NOCOPY VARCHAR2,
367 --
371 p_parent_qty IN NUMBER,
368 p_hierarchy_id IN NUMBER,
369 p_version_id IN NUMBER,
370 p_parent_id IN NUMBER,
372 p_child_id IN NUMBER,
373 p_child_qty IN NUMBER,
374 p_yield_pct IN NUMBER,
375 p_bom_reference IN VARCHAR2,
376 p_display_order_num IN NUMBER,
377 p_hier_table_name IN VARCHAR2
378 );
379
380 PROCEDURE Add_Relations(
381 p_api_version IN NUMBER,
382 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
383 p_commit IN VARCHAR2 := FND_API.G_FALSE,
384 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
385 x_return_status OUT NOCOPY VARCHAR2,
386 x_msg_count OUT NOCOPY NUMBER,
387 x_msg_data OUT NOCOPY VARCHAR2,
388 --
389 p_hierarchy_id IN NUMBER,
390 p_version_id IN NUMBER,
391 p_hier_table_name IN VARCHAR2,
392 p_relation_details_tbl IN FEM_DHM_HIER_NODE_TAB_TYP
393 );
394
395 PROCEDURE Update_Relation(
396 p_api_version IN NUMBER,
397 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
398 p_commit IN VARCHAR2 := FND_API.G_FALSE,
399 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
400 x_return_status OUT NOCOPY VARCHAR2,
401 x_msg_count OUT NOCOPY NUMBER,
402 x_msg_data OUT NOCOPY VARCHAR2,
403 --
404 p_hierarchy_id IN NUMBER,
405 p_version_id IN NUMBER,
406 p_parent_id IN NUMBER,
407 p_parent_qty IN NUMBER,
408 p_child_id IN NUMBER,
409 p_child_qty IN NUMBER,
410 p_child_sequence_num IN NUMBER,
411 p_yield_pct IN NUMBER,
412 p_bom_reference IN VARCHAR2,
413 p_display_order_num IN NUMBER,
414 p_hier_table_name IN VARCHAR2
415 );
416
417 PROCEDURE Update_Relations(
418 p_api_version IN NUMBER,
419 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
420 p_commit IN VARCHAR2 := FND_API.G_FALSE,
421 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
422 x_return_status OUT NOCOPY VARCHAR2,
423 x_msg_count OUT NOCOPY NUMBER,
424 x_msg_data OUT NOCOPY VARCHAR2,
425 --
426 p_hierarchy_id IN NUMBER,
427 p_version_id IN NUMBER,
428 p_hier_table_name IN VARCHAR2,
429 p_relation_details_tbl IN FEM_DHM_HIER_NODE_TAB_TYP
430 );
431
432 PROCEDURE Remove_Relation(
433 p_api_version IN NUMBER,
434 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
435 p_commit IN VARCHAR2 := FND_API.G_FALSE,
436 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2,
440 --
441 p_hierarchy_id IN NUMBER,
442 p_version_id IN NUMBER,
443 p_parent_id IN NUMBER,
444 p_child_id IN NUMBER,
445 p_child_sequence_num IN NUMBER,
446 p_hier_table_name IN VARCHAR2,
447 p_remove_all_children_flag IN VARCHAR);
448
449
450 PROCEDURE Move_Relation(
451 p_api_version IN NUMBER,
452 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
453 p_commit IN VARCHAR2 := FND_API.G_FALSE,
454 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2,
458 --
459 p_hierarchy_id IN NUMBER,
460 p_version_id IN NUMBER,
461 p_hier_table_name IN VARCHAR2,
462 p_child_id IN NUMBER,
463 p_src_parent_id IN NUMBER,
464 p_dest_parent_id IN NUMBER,
465 p_child_sequence_num IN NUMBER
466 );
467
468 PROCEDURE Flatten_Whole_Hierarchy_CP
469 (
470 errbuf OUT NOCOPY VARCHAR2 ,
471 retcode OUT NOCOPY VARCHAR2 ,
472 p_hierarchy_id IN NUMBER
473 );
474
475 FUNCTION Get_Version_Access_Code
476 (
477 p_version_id IN NUMBER,
478 p_object_access_code IN VARCHAR2
479 )
480 RETURN VARCHAR2;
481
482 FUNCTION can_delete_hierarchy
483 (
484 p_hierarchy_id IN NUMBER,
485 p_folder_id IN NUMBER
486 )
487 RETURN VARCHAR2;
488
489 FUNCTION can_delete_hier_version
490 (
491 p_hier_version_id IN NUMBER,
492 p_folder_id IN NUMBER
493 )
494 RETURN VARCHAR2;
495
496 FUNCTION can_view_ver_detail
497 (
498 p_hier_version_id IN NUMBER
499 )
500 RETURN VARCHAR2;
501
502 FUNCTION can_duplicate_hier_version
503 (
504 p_hier_version_id IN NUMBER
505 )
506
507 RETURN VARCHAR2;
508
509 FUNCTION Can_Update_Hierarchy
510 (
511 p_object_access_code IN VARCHAR2,
512 p_hier_created_by IN VARCHAR2
513 )
514 RETURN VARCHAR2;
515
516 FUNCTION is_hier_deleted
517 (
518 p_hierarchy_id IN NUMBER
519 )
520 RETURN VARCHAR2;
521
522 FUNCTION is_hier_ver_deleted
523 (
524 p_hier_ver_id IN NUMBER
525 )
526 RETURN VARCHAR2;
527
528 FUNCTION is_hier_vs_deleteable
529 (
530 p_hierarchy_id IN NUMBER,
531 p_value_set_id IN NUMBER,
532 p_hier_table IN VARCHAR2
533 )
534 RETURN VARCHAR2;
535
536 end FEM_HIER_UTILS_PVT;
[Home] [Help]