1 package FA_CUA_HIERARCHY_PKG AS
2 /* $Header: FACHRAHMS.pls 120.1 2002/11/12 07:55:01 glchen ship $ */
3 /****
4 REM===========================================================================
5 REM $Header: FACHRAHMS.pls 120.1 2002/11/12 07:55:01 glchen ship $
6 REM +========================================================================+
7 REM | Copyright (c) 1993 Oracle Corporation Redwood Shores, CA, USA |
8 REM | All Rights Reserved |
9 REM +========================================================================+
10 REM |FILENAME |
11 REM | FACHRAHS.pls |
12 REM | |
13 REM |DESCRIPTION |
14 REM | PL/SQL Body for package: FA_CUA_HIERARCHY_PKG |
15 REM | |
16 REM |USAGE |
17 REM | sqlplus apps/apps @FACHRAHS.pls |
18 REM | |
19 REM |EXAMPLE |
20 REM | sqlplus apps/apps @FACHRAHS.pls |
21 REM | |
22 REM |HISTORY |
23 REM | 21-Jan-99 S Murali Added Fn is_assets_attched_node |
24 REM | 18-NOV-98 S Murali Created |
25 REM | |
26 REM | |
27 REM +========================================================================+
28 REM
29
30 ****/
31
32 g_asset_hierarchy_purpose_id number;
33 g_book_type_code varchar2(15);
34 g_name varchar2(30);
35 g_parent_hierarchy_id number;
36 g_err_code varchar2(240);
37 g_err_stage varchar2(240);
38 g_err_stack varchar2(240);
39 global_level_number Number;
40
41 -- Declarations for Distribution Record type
42 TYPE distribution_rec IS RECORD (
43 distribution_line_percentage NUMBER
44 , code_combination_id NUMBER
45 , location_id NUMBER
46 , assigned_to NUMBER
47 );
48
49 TYPE distribution_tabtype IS TABLE OF distribution_rec
50 INDEX BY BINARY_INTEGER ;
51
52 distribution_tab distribution_tabtype;
53
54 Function set_global_level_number(p_level_number in number)
55 return boolean;
56
57 Function get_global_level_number
58 return number;
59 pragma restrict_references (get_global_level_number,WNPS,WNDS);
60
61 Function validate_level_number(p_level_number in number)
62 return boolean;
63
64 Procedure Insert_row ( x_rowid in out nocopy varchar2
65 , x_asset_hierarchy_purpose_id in number
66 , x_asset_hierarchy_id in out nocopy number
67 , x_name in varchar2 default null
68 , x_level_number in NUMBER
69 , x_hierarchy_rule_set_id in number
70 , X_CREATION_DATE in date
71 , X_CREATED_BY in number
72 , X_LAST_UPDATE_DATE in date
73 , X_LAST_UPDATED_BY in number
74 , X_LAST_UPDATE_LOGIN in number
75 , x_description in varchar2
76 , x_parent_hierarchy_id in number
77 , x_lowest_level_flag in number
78 , x_depreciation_start_date in date
79 , x_asset_id in number
80 , X_ATTRIBUTE_CATEGORY in varchar2
81 , X_ATTRIBUTE1 in varchar2
82 , X_ATTRIBUTE2 in varchar2
83 , X_ATTRIBUTE3 in varchar2
84 , X_ATTRIBUTE4 in varchar2
85 , X_ATTRIBUTE5 in varchar2
86 , X_ATTRIBUTE6 in varchar2
87 , X_ATTRIBUTE7 in varchar2
88 , X_ATTRIBUTE8 in varchar2
89 , X_ATTRIBUTE9 in varchar2
90 , X_ATTRIBUTE10 in varchar2
91 , X_ATTRIBUTE11 in varchar2
92 , X_ATTRIBUTE12 in varchar2
93 , X_ATTRIBUTE13 in varchar2
94 , X_ATTRIBUTE14 in varchar2
95 , X_ATTRIBUTE15 in varchar2
96 );
97 --Function to check an hierararchy node exists
98 Function check_node_exists ( x_name in varchar2
99 ,x_node_type in Varchar2
100 ,x_purpose_id in number)
101 return Boolean;
102 --Function to check name is unique
103 Function check_name_unique( x_event in varchar2
104 ,x_asset_hierarchy_id in number default null
105 ,x_name in varchar2
106 ,x_asset_id in number
107 ,x_purpose_id in number)
108 return Boolean;
109
110 --Procedure to create the distribution set
111 Procedure create_distribution_set
112 ( x_dist_set_id out nocopy number
113 ,x_book_type_code in varchar2
114 ,x_distribution_tab in FA_CUA_HIERARCHY_PKG.distribution_tabtype
115 ,x_err_code in out nocopy varchar2
116 ,x_err_stage in out nocopy varchar2
117 ,x_err_stack in out nocopy varchar2);
118
119 --Procedure to Validate Node Attribute Values
120 Procedure validate_node_attributes
121 (x_asset_hierarchy_purpose_id in number
122 ,x_asset_hierarchy_id in number
123 ,x_level_number in number
124 ,x_book_type_code in varchar2
125 ,x_asset_category_id in number default null
126 ,x_lease_id in NUMBER default null
127 ,x_asset_key_ccid in number default null
128 ,x_serial_number in varchar2 default null
129 ,x_life_end_date in date default null
130 ,x_dist_set_id in number default null
131 --,x_distribution_tab in FA_CUA_HIERARCHY_PKG.distribution_tabtype
132 ,x_err_code in out nocopy varchar2
133 ,x_err_stage in out nocopy varchar2
134 ,x_err_stack in out nocopy varchar2);
135
136 --Procedure To validate Node parameters
137 Procedure validate_node( x_calling_module in varchar2 default 'A'
138 ,x_asset_hierarchy_purpose_id in out nocopy number
139 ,x_book_type_code in varchar2
140 ,x_name in varchar2 default null
141 ,x_level_number in NUMBER default 0
142 ,x_parent_hierarchy_id in number
143 ,x_hierarchy_rule_set_id in number default null
144 ,x_err_code in out nocopy varchar2
145 ,x_err_stage in out nocopy varchar2
146 ,x_err_stack in out nocopy varchar2);
147
148 procedure create_node(
149 -- Arguments required for Public APIs
150 x_err_code in out nocopy varchar2
151 , x_err_stage in out nocopy Varchar2
152 , x_err_stack in out nocopy varchar2
153 -- Arguments for Node Creation
154 , x_asset_hierarchy_purpose_id in NUMBER
155 , x_asset_hierarchy_id in out nocopy NUMBER
156 , x_name in VARCHAR2 default null
157 , x_level_number in NUMBER
158 , x_hierarchy_rule_set_id in NUMBER default null
159 , X_CREATION_DATE in DATE default trunc(sysdate)
160 , X_CREATED_BY in NUMBER := FND_GLOBAL.USER_ID
161 , X_LAST_UPDATE_DATE in DATE default trunc(sysdate)
162 , X_LAST_UPDATED_BY in NUMBER := FND_GLOBAL.USER_ID
163 , X_LAST_UPDATE_LOGIN in NUMBER := FND_GLOBAL.USER_ID
164 , x_description in VARCHAR2 default null
165 , x_parent_hierarchy_id in NUMBER default null
166 , x_lowest_level_flag in NUMBER default null
167 , x_depreciation_start_date in date default null
168 , x_asset_id in number default null
169 , X_ATTRIBUTE_CATEGORY in VARCHAR2 default null
170 , X_ATTRIBUTE1 in VARCHAR2 default null
171 , X_ATTRIBUTE2 in VARCHAR2 default null
172 , X_ATTRIBUTE3 in VARCHAR2 default null
173 , X_ATTRIBUTE4 in VARCHAR2 default null
174 , X_ATTRIBUTE5 in VARCHAR2 default null
175 , X_ATTRIBUTE6 in VARCHAR2 default null
176 , X_ATTRIBUTE7 in VARCHAR2 default null
177 , X_ATTRIBUTE8 in VARCHAR2 default null
178 , X_ATTRIBUTE9 in VARCHAR2 default null
179 , X_ATTRIBUTE10 in VARCHAR2 default null
180 , X_ATTRIBUTE11 in VARCHAR2 default null
181 , X_ATTRIBUTE12 in VARCHAR2 default null
182 , X_ATTRIBUTE13 in VARCHAR2 default null
183 , X_ATTRIBUTE14 in VARCHAR2 default null
184 , X_ATTRIBUTE15 in VARCHAR2 default null
185 );
186 --Procedure to create node along with the attributes
187 procedure create_node_with_attributes(
188 -- Arguments required for Public APIs
189 x_err_code in out nocopy varchar2
190 , x_err_stage in out nocopy Varchar2
191 , x_err_stack in out nocopy varchar2
192 -- Arguments for Node Creation
193 , x_asset_hierarchy_purpose_id in NUMBER
194 , x_asset_hierarchy_id in out nocopy NUMBER
195 , x_name in VARCHAR2 default null
196 , x_level_number in NUMBER
197 , x_hierarchy_rule_set_id in NUMBER default null
198 , X_CREATION_DATE in DATE default trunc(sysdate)
199 , X_CREATED_BY in NUMBER := FND_GLOBAL.USER_ID
200 , X_LAST_UPDATE_DATE in DATE default trunc(sysdate)
201 , X_LAST_UPDATED_BY in NUMBER := FND_GLOBAL.USER_ID
202 , X_LAST_UPDATE_LOGIN in NUMBER := FND_GLOBAL.USER_ID
203 , x_description in VARCHAR2 default null
204 , x_parent_hierarchy_id in NUMBER default null
205 , x_lowest_level_flag in NUMBER default null
206 , x_depreciation_start_date in date default null
207 , x_asset_id in number default null
208 , X_ATTRIBUTE_CATEGORY in VARCHAR2 default null
209 , X_ATTRIBUTE1 in VARCHAR2 default null
210 , X_ATTRIBUTE2 in VARCHAR2 default null
211 , X_ATTRIBUTE3 in VARCHAR2 default null
212 , X_ATTRIBUTE4 in VARCHAR2 default null
213 , X_ATTRIBUTE5 in VARCHAR2 default null
214 , X_ATTRIBUTE6 in VARCHAR2 default null
215 , X_ATTRIBUTE7 in VARCHAR2 default null
216 , X_ATTRIBUTE8 in VARCHAR2 default null
217 , X_ATTRIBUTE9 in VARCHAR2 default null
218 , X_ATTRIBUTE10 in VARCHAR2 default null
219 , X_ATTRIBUTE11 in VARCHAR2 default null
220 , X_ATTRIBUTE12 in VARCHAR2 default null
221 , X_ATTRIBUTE13 in VARCHAR2 default null
222 , X_ATTRIBUTE14 in VARCHAR2 default null
223 , X_ATTRIBUTE15 in VARCHAR2 default null
224 --Parameters for Node Attributes
225 ,x_attribute_book_type_code in varchar2 default null
226 ,x_asset_category_id in number default null
227 ,x_lease_id in NUMBER default null
228 ,x_asset_key_ccid in number default null
229 ,x_serial_number in varchar2 default null
230 ,x_life_end_date in date default null
231 ,x_distribution_tab in FA_CUA_HIERARCHY_PKG.distribution_tabtype default FA_CUA_HIERARCHY_PKG.distribution_tab
232 );
233
234
235 procedure LOCK_ROW (
236 x_asset_hierarchy_purpose_id in NUMBER
237 , x_asset_hierarchy_id in NUMBER
238 --, x_book_type_code in varchar2
239 , x_name in VARCHAR2
240 , x_level_number in Number
241 , x_hierarchy_rule_set_id in NUMBER
242 , x_description in VARCHAR2
243 , x_parent_hierarchy_id in NUMBER
244 , x_lowest_level_flag in NUMBER
245 , x_depreciation_start_date in date
246 , x_asset_id in number
247 , X_ATTRIBUTE_CATEGORY in VARCHAR2
248 , X_ATTRIBUTE1 in VARCHAR2
249 , X_ATTRIBUTE2 in VARCHAR2
250 , X_ATTRIBUTE3 in VARCHAR2
251 , X_ATTRIBUTE4 in VARCHAR2
252 , X_ATTRIBUTE5 in VARCHAR2
253 , X_ATTRIBUTE6 in VARCHAR2
254 , X_ATTRIBUTE7 in VARCHAR2
255 , X_ATTRIBUTE8 in VARCHAR2
256 , X_ATTRIBUTE9 in VARCHAR2
257 , X_ATTRIBUTE10 in VARCHAR2
258 , X_ATTRIBUTE11 in VARCHAR2
259 , X_ATTRIBUTE12 in VARCHAR2
260 , X_ATTRIBUTE13 in VARCHAR2
261 , X_ATTRIBUTE14 in VARCHAR2
262 , X_ATTRIBUTE15 in VARCHAR2
263 );
264
265
266
267 procedure UPDATE_ROW (
268 x_asset_hierarchy_purpose_id in NUMBER
269 , x_asset_hierarchy_id in NUMBER
270 --, x_book_type_code in varchar2
271 , x_name in VARCHAR2
272 , x_level_number in NUMBER
273 , x_hierarchy_rule_set_id in NUMBER
274 , X_LAST_UPDATE_DATE in DATE
275 , X_LAST_UPDATED_BY in NUMBER
276 , X_LAST_UPDATE_LOGIN in NUMBER
277 , x_description in VARCHAR2
278 , x_parent_hierarchy_id in NUMBER
279 , x_lowest_level_flag in NUMBER
280 , X_DEPRECIATION_START_DATE in DATE
281 , x_asset_id in number
282 , X_ATTRIBUTE_CATEGORY in VARCHAR2
283 , X_ATTRIBUTE1 in VARCHAR2
284 , X_ATTRIBUTE2 in VARCHAR2
285 , X_ATTRIBUTE3 in VARCHAR2
286 , X_ATTRIBUTE4 in VARCHAR2
287 , X_ATTRIBUTE5 in VARCHAR2
288 , X_ATTRIBUTE6 in VARCHAR2
289 , X_ATTRIBUTE7 in VARCHAR2
290 , X_ATTRIBUTE8 in VARCHAR2
291 , X_ATTRIBUTE9 in VARCHAR2
292 , X_ATTRIBUTE10 in VARCHAR2
293 , X_ATTRIBUTE11 in VARCHAR2
294 , X_ATTRIBUTE12 in VARCHAR2
295 , X_ATTRIBUTE13 in VARCHAR2
296 , X_ATTRIBUTE14 in VARCHAR2
297 , X_ATTRIBUTE15 in VARCHAR2
298 );
299
300
301 procedure DELETE_ROW (
302 x_asset_hierarchy_purpose_id in number
303 , X_asset_hierarchy_id in NUMBER
304 );
305
306 Function check_lowest_level_node(x_asset_hierarchy_id in number)
307 return Boolean;
308
309 Function check_asset_node(x_asset_hierarchy_id in number)
310 return Boolean;
311
312 Function Check_asset_tied_node(x_asset_hierarchy_id in number)
313 return BOOLEAN;
314
315 Function is_attribute_mandatory(x_hierarchy_purpose_id in number
316 ,x_level_number in number
317 ,x_attribute_name in varchar2)
318 return Boolean;
319
320 Function is_child_exists(x_asset_hierarchy_id in number)
321 return BOOLEAN;
322
323 Function is_assets_attached_node(x_node_id in number) return boolean;
324
325 Function is_valid_line_percent(x_line_percent in number) return boolean;
326
327 Procedure wrapper_validate_node;
328
329 end FA_CUA_HIERARCHY_PKG ;