1 PACKAGE BODY FA_CUA_HIERARCHY_PKG AS
2 /* $Header: FACHRAHMB.pls 120.4 2009/08/20 14:17:46 bridgway ship $ */
3
4 -- Creating Package Body for FA_CUA_HIERARCHY_PKG
5 -- Private APIs
6
7 Function Validate_hierarchy_purpose(x_purpose_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
8 return Boolean
9 is
10 Cursor C_1 is select asset_hierarchy_purpose_id
11 from fa_asset_hierarchy_purpose
12 where asset_hierarchy_purpose_id = x_purpose_id;
13 l_purpose_id FA_ASSET_HIERARCHY_PURPOSE.asset_hierarchy_purpose_id%TYPE;
14 Begin
15 open C_1;
16 fetch C_1 into l_purpose_id;
17 if C_1%NOTFOUND then
18 close C_1;
19 return FALSE;
20 end if;
21 close C_1;
22 return TRUE;
23 End Validate_hierarchy_purpose;
24
25 --Procedure to validate Asset CAtegory
26 Procedure Validate_asset_category (x_book_type_code in varchar2
27 ,x_asset_category_id in number
28 ,x_lease_id in NUMBER
29 ,x_err_code in out nocopy varchar2
30 ,x_err_stage in out nocopy varchar2
31 ,x_err_stack in out nocopy varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
32 is
33 Cursor C_1 is select category_type
34 from fa_categories
35 where category_id = x_asset_category_id;
36
37 Cursor C_2 is select 1
38 from fa_category_books
39 where category_id = x_asset_category_id
40 and book_type_code = x_book_type_code;
41
42 l_category_type FA_CATEGORIES.category_type%TYPE;
43 dummy C_2%ROWTYPE;
44 l_old_err_stack varchar2(640);
45 Begin
46 l_old_err_stack := x_err_stack;
47 x_err_stack := x_err_stack||'->'||'Validating_Asset_CAtegory';
48 x_err_stage := 'Validating existance of Category';
49 open C_1;
50 fetch C_1 into l_category_type;
51 if C_1%NOTFOUND then
52 close C_1;
53 x_err_code := 'CUA_INVALID_CATEGORY';
54 return ;
55 else
56 close C_1;
57 x_err_stage := 'Validating Category with Lease';
58 if(x_lease_id is not null AND l_category_type = 'NON-LEASE') then
59 x_err_code := 'CUA_INVALID_CATEGORY_LEASE';
60 return;
61 end if;
62 end if;
63 x_err_stage := 'Validating Category defined for the Book';
64 Open C_2;
65 fetch C_2 into dummy;
66 if(C_2%NOTFOUND) then
67 x_err_code := 'CUA_INVALID_CATG_BOOK';
68 close C_2;
69 return;
70 end if;
71 close C_2;
72 x_err_stack := l_old_err_stack;
73 End Validate_asset_category;
74
75 --Function to Validate the Lease ID
76 Function Validate_lease(x_lease_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
77 return Boolean
78 is
79 Cursor C_1 is select lease_id
80 from fa_leases
81 where lease_id = x_lease_id;
82 l_lease_id number;
83 Begin
84 open C_1;
85 fetch C_1 into l_lease_id;
86 if C_1%NOTFOUND then
87 close C_1;
88 return FALSE;
89 end if;
90 close C_1;
91 return TRUE;
92 End Validate_lease;
93
94 --Fnction to validate Asset Key words
95 Function Validate_asset_key(x_asset_key_ccid in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
96 return Boolean
97 is
98 Cursor C_1 is select code_combination_id
99 from fa_asset_keywords
100 where code_combination_id = x_asset_key_ccid;
101 l_asset_key_ccid number;
102 Begin
103 open C_1;
104 fetch C_1 into l_asset_key_ccid;
105 if C_1%NOTFOUND then
106 close C_1;
107 return FALSE;
108 end if;
109 close C_1;
110 return TRUE;
111 End Validate_asset_key;
112
113 --Fnction to validate Location
114 Function Validate_location(x_location_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
115 return Boolean
116 is
117 Cursor C_1 is select location_id
118 from fa_locations
119 where location_id = x_location_id;
120 l_location_id number;
121 Begin
122 open C_1;
123 fetch C_1 into l_location_id;
124 if C_1%NOTFOUND then
125 close C_1;
126 return FALSE;
127 end if;
128 close C_1;
129 return TRUE;
130 End Validate_location;
131
132 --Fnction to validate Location
133 Function Validate_gl_ccid(x_gl_ccid in number,x_CofA_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
134 return Boolean
135 is
136 Cursor C_1 is select code_combination_id
137 from gl_code_combinations
138 where code_combination_id = x_gl_ccid
139 and chart_of_accounts_id = x_CofA_id
140 and account_type = 'E'
141 and enabled_flag = 'Y'
142 and summary_flag = 'N'
143 and template_id is null
144 and detail_posting_allowed_flag = 'Y';
145 l_gl_ccid number;
146 Begin
147 open C_1;
148 fetch C_1 into l_gl_ccid;
149 if C_1%NOTFOUND then
150 close C_1;
151 return FALSE;
152 end if;
153 close C_1;
154 return TRUE;
155 End Validate_gl_ccid;
156
157 --Fnction to validate employee
158 Function Validate_employee(x_employee_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
159 return Boolean
160 is
161 Cursor C_1 is select employee_id
162 from fa_employees
163 where employee_id = x_employee_id;
164 l_employee_id number;
165 Begin
166 open C_1;
167 fetch C_1 into l_employee_id;
168 if C_1%NOTFOUND then
169 close C_1;
170 return FALSE;
171 end if;
172 close C_1;
173 return TRUE;
174 End Validate_employee;
175
176 --Function to validate the distribution set
177 Function valid_dist_set(x_dist_set_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
178 return Boolean
179 is
180 Cursor C is select dist_set_id
181 from FA_HIERARCHY_DISTRIBUTIONS
182 where dist_set_id = x_dist_set_id;
183 l_dist_set_id number;
184 Begin
185 open C;
186 fetch C into l_dist_set_id;
187 if C%NOTFOUND then
188 close C ;
189 return FALSE;
190 end if;
191 close C;
192 return TRUE;
193 End valid_dist_set;
194 Function validate_level_number(p_level_number in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
195 return boolean
196 is
197 begin
198 if (p_level_number < 0 OR
199 (p_level_number - trunc(p_level_number)) > 0)
200 then
201 return FALSE;
202 else
203 return TRUE;
204
205 end if;
206 end validate_level_number;
207
208 Function set_global_level_number(p_level_number in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
209 return boolean
210 is
211 Begin
212 FA_CUA_HIERARCHY_PKG.global_level_number := p_level_number;
213 return TRUE;
214 End;
215
216 Function get_global_level_number
217 return number
218 is
219 BEGIN
220 return (nvl(FA_CUA_HIERARCHY_PKG.global_level_number,-1));
221 END;
222 --Function to check an hierararchy node exists
223 Function check_node_exists ( x_name in varchar2
224 ,x_node_type in Varchar2
225 ,x_purpose_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
226 return Boolean
227 is
228 Cursor C_NAME is select asset_hierarchy_id
229 from fa_asset_hierarchy
230 where name = x_name
231 and asset_hierarchy_purpose_id = x_purpose_id
232 and nvl(asset_id,0) = decode(x_node_type,'N',0,asset_id);
233 dummy number;
234 Begin
235 open C_NAME;
236 fetch C_NAME into dummy;
237 if (C_NAME%NOTFOUND ) then
238 close C_NAME;
239 return FALSE;
240 end if;
241 close C_NAME;
242 return TRUE;
243 END check_node_exists;
244
245 Procedure Insert_row ( x_rowid in out nocopy varchar2
246 , x_asset_hierarchy_purpose_id in number
247 , x_asset_hierarchy_id in out nocopy number
248 , x_name in varchar2 default null
249 , x_level_number in number
250 , x_hierarchy_rule_set_id in number
251 , X_CREATION_DATE in date
252 , X_CREATED_BY in number
253 , X_LAST_UPDATE_DATE in date
254 , X_LAST_UPDATED_BY in number
255 , X_LAST_UPDATE_LOGIN in number
256 , x_description in varchar2
257 , x_parent_hierarchy_id in number
258 , x_lowest_level_flag in number
259 , x_depreciation_start_date in date
260 , x_asset_id in number
261 , X_ATTRIBUTE_CATEGORY in varchar2
262 , X_ATTRIBUTE1 in varchar2
263 , X_ATTRIBUTE2 in varchar2
264 , X_ATTRIBUTE3 in varchar2
265 , X_ATTRIBUTE4 in varchar2
266 , X_ATTRIBUTE5 in varchar2
267 , X_ATTRIBUTE6 in varchar2
268 , X_ATTRIBUTE7 in varchar2
269 , X_ATTRIBUTE8 in varchar2
270 , X_ATTRIBUTE9 in varchar2
271 , X_ATTRIBUTE10 in varchar2
272 , X_ATTRIBUTE11 in varchar2
273 , X_ATTRIBUTE12 in varchar2
274 , X_ATTRIBUTE13 in varchar2
275 , X_ATTRIBUTE14 in varchar2
276 , X_ATTRIBUTE15 in varchar2
277 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
278 is
279 cursor C is select ROWID from FA_ASSET_HIERARCHY
280 where asset_hierarchy_id = X_asset_hierarchy_id ;
281
282 CURSOR C1 is Select FA_ASSET_HIERARCHY_S.nextval from sys.dual;
283 l_name FA_ASSET_HIERARCHY.name%TYPE;
284 begin
285 if X_asset_hierarchy_id is null then
286 open C1;
287 fetch C1 into X_asset_hierarchy_id ;
288 close C1;
289 end if;
290 --Set the Node name same as the node id if the node name is null for the non asset node
291 if( nvl(x_asset_id,0) = 0
292 AND x_name is null
293 ) then
294 l_name := to_char(x_asset_hierarchy_id);
295 end if;
296 insert into FA_ASSET_HIERARCHY
297 ( asset_hierarchy_purpose_id
298 , asset_hierarchy_id
299 , name
300 , level_number
301 , hierarchy_rule_set_id
302 , CREATION_DATE
303 , CREATED_BY
304 , LAST_UPDATE_DATE
305 , LAST_UPDATED_BY
306 , LAST_UPDATE_LOGIN
307 , description
308 , parent_hierarchy_id
309 , lowest_level_flag
310 , depreciation_start_date
311 , asset_id
312 , ATTRIBUTE_CATEGORY
313 , ATTRIBUTE1
314 , ATTRIBUTE2
315 , ATTRIBUTE3
316 , ATTRIBUTE4
317 , ATTRIBUTE5
318 , ATTRIBUTE6
319 , ATTRIBUTE7
320 , ATTRIBUTE8
321 , ATTRIBUTE9
322 , ATTRIBUTE10
323 , ATTRIBUTE11
324 , ATTRIBUTE12
325 , ATTRIBUTE13
326 , ATTRIBUTE14
327 , ATTRIBUTE15 )
328 Values
329 (x_asset_hierarchy_purpose_id
330 , x_asset_hierarchy_id
331 , nvl(x_name,l_name)
332 , x_level_number
333 , x_hierarchy_rule_set_id
334 , X_CREATION_DATE
335 , X_CREATED_BY
336 , X_LAST_UPDATE_DATE
337 , X_LAST_UPDATED_BY
338 , X_LAST_UPDATE_LOGIN
339 , x_description
340 , x_parent_hierarchy_id
341 , x_lowest_level_flag
342 , x_depreciation_start_date
343 , x_asset_id
344 , X_ATTRIBUTE_CATEGORY
345 , X_ATTRIBUTE1
346 , X_ATTRIBUTE2
347 , X_ATTRIBUTE3
348 , X_ATTRIBUTE4
349 , X_ATTRIBUTE5
350 , X_ATTRIBUTE6
351 , X_ATTRIBUTE7
352 , X_ATTRIBUTE8
353 , X_ATTRIBUTE9
354 , X_ATTRIBUTE10
355 , X_ATTRIBUTE11
356 , X_ATTRIBUTE12
357 , X_ATTRIBUTE13
358 , X_ATTRIBUTE14
359 , X_ATTRIBUTE15 );
360
361 open c;
362 fetch c into X_ROWID;
363 if (c%notfound) then
364 close c;
365 raise no_data_found;
366 end if;
367 close c;
368 end INSERT_ROW;
369
370 /* Procedure to validate the distribution table and create the distribution
371 set and the distribution lines in FA_HIERARCHY_DISTRIBUTIONS table */
372 Procedure create_distribution_set
373 ( x_dist_set_id out nocopy number
374 ,x_book_type_code in varchar2
375 ,x_distribution_tab in FA_CUA_HIERARCHY_PKG.distribution_tabtype
376 ,x_err_code in out nocopy varchar2
377 ,x_err_stage in out nocopy varchar2
378 ,x_err_stack in out nocopy varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
379 is
380 Cursor C is select FA_HIERARCHY_DIST_SET_S.nextval from dual;
381 Cursor C_CofA_id is select accounting_flex_structure
382 from fa_book_controls
383 where book_type_code = x_book_type_code;
384 l_CofA_id number;
385 l_old_err_stack varchar2(640);
386 I BINARY_INTEGER;
387 l_percent_total number;
388 l_rowid varchar2(240);
389 l_distribution_id number;
390 l_CREATION_DATE DATE default trunc(sysdate);
391 l_CREATED_BY NUMBER := FND_GLOBAL.USER_ID;
392
393 Begin
394 l_old_err_stack := x_err_stack;
395 x_err_stack := x_err_stack||'->'||'CREATE_DIST_SET';
396 --Getting Chart of Account ID for the Book
397 x_err_stage := 'Getting Chart of Account ID for the Book';
398 open C_CofA_id;
399 fetch C_CofA_id into l_CofA_id;
400 if(C_CofA_id%NOTFOUND ) then
401 x_err_code:= 'CUA_NO_FLEX_STRUCTURE';
402 close C_CofA_id;
403 return;
404 end if;
405 close C_CofA_id;
406 --Validate the distribution Table
407 x_err_stage := 'Validating Distributions';
408 FOR I in 1..x_distribution_tab.count LOOP
409 --Validating Expense Account
410 if(x_distribution_tab(I).code_combination_id is not null
411 AND NOT validate_gl_ccid( x_distribution_tab(I).code_combination_id,l_CofA_id, p_log_level_rec) )then
412 x_err_code := 'CUA_INVALID_EXPENSE_ACCOUNT';
413 return;
414 elsif(x_distribution_tab(I).code_combination_id is null ) then
415 x_err_code := 'CUA_EXPENSE_ACCOUNT_MANDATORY';
416 return;
417 end if;
418 --Validate Location
419 if(x_distribution_tab(I).location_id is not null
420 AND NOT validate_location(x_distribution_tab(I).location_id ,
421 p_log_level_rec) )then
422 x_err_code := 'CUA_INVALID_LOCATION';
423 return;
424 elsif(x_distribution_tab(I).location_id is null) then
425 x_err_code := 'CUA_LOCATION_MANDATORY';
426 return;
427 end if;
428 --Validate Employee
429 if(x_distribution_tab(I).assigned_to is not null
430 AND NOT validate_employee( x_distribution_tab(I).assigned_to,
431 p_log_level_rec ) )then
432 x_err_code := 'CUA_INVALID_EMPLOYEE';
433 return;
434 end if;
435 END LOOP;
436 --Validate the Sum of Disrtribution % is 100
437 x_err_stage := 'Validating Distribution percentage sum is 100';
438 FOR I in 1..x_distribution_tab.count LOOP
439 l_percent_total := nvl(l_percent_total,0) + nvl(x_distribution_tab(I).distribution_line_percentage,0);
440 END LOOP;
441 if(l_percent_total <> 100) then
442 x_err_code := 'CUA_INVALID_LINE_PERCENT_SUM';
443 return;
444 end if;
445
446 -- If Valid create the distribution set with details and return
447 -- the distribution set id
448 x_err_stage := 'Fetch the next Dist Set ID';
449 open C;
450 fetch C into x_dist_set_id;
451 close c;
452 x_err_stage := 'Inserting into FA_HIERARCHY_DISTRIBUTIONS table';
453 l_rowid := null;
454 --dbms_output.put_line('Before Inserting Distributions');
455 --dbms_output.put_line('count:'||to_char(x_distribution_tab.count));
456 FOR I in 1..x_distribution_tab.count LOOP
457 l_distribution_id := null;
458 FA_CUA_HR_DISTRIBUTION_PKG.Insert_row (
459 l_rowid
460 , l_distribution_id
461 , x_dist_set_id
462 --, x_asset_hierarchy_purpose_id
463 --, x_asset_hierarchy_id
464 , x_book_type_code
465 , x_distribution_tab(I).distribution_line_percentage
466 , x_distribution_tab(I).code_combination_id
467 , x_distribution_tab(I).location_id
468 , x_distribution_tab(I).assigned_to
469 , l_CREATION_DATE
470 , l_CREATED_BY
471 , l_CREATION_DATE
472 , l_CREATED_BY
473 , l_CREATED_BY
474 , p_log_level_rec );
475 --dbms_output.put_line('Distribution ID:'||to_char(l_distribution_id));
476 END LOOP;
477 x_err_stack := l_old_err_stack;
478 End create_distribution_set;
479
480 --FUnction to check Ctegory is of lease type
481 Function is_catg_nonlease_type(x_catg_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
482 return boolean
483 is
484 dummy number;
485 begin
486 select 1 into dummy from dual
487 where exists(select 1 from fa_categories
488 where category_id = x_catg_id
489 and category_type = 'NON-LEASE');
490 return(TRUE);
491 exception
492 when no_data_found then
493 return(FALSE);
494 end is_catg_nonlease_type;
495
496 --Procedure to Validate Node Attribute Values
497 --Call this procedure only for Non Asset Nodes
498 --and attribute values are required
499 /* validates for a. Called only for Non Asset Nodes
500 b. Checks for the mandatory parameters with the controls
501 c. Attribute Book is associate of the purpose Book.
502 d. Asset Node level must be zero and Non Asset node level must be Non Zero
503 e. Check all the parameters passed are valid ones.
504 f. If catrgory is given then valid for the Attribute Book.
505 g. If category and lease are given, then category is of NON_LEASE type.
506 h. If Distribution tab is given then the sum of distribution line % is 100.
507 */
508
509 Procedure validate_node_attributes
510 (x_asset_hierarchy_purpose_id in number
511 ,x_asset_hierarchy_id in number
512 ,x_level_number in number
513 ,x_book_type_code in varchar2
514 ,x_asset_category_id in number default null
515 ,x_lease_id in NUMBER default null
516 ,x_asset_key_ccid in number default null
517 ,x_serial_number in varchar2 default null
518 ,x_life_end_date in date default null
519 ,x_dist_set_id in number default null
520 --,x_distribution_tab in FA_CUA_HIERARCHY_PKG.distribution_tabtype
521 ,x_err_code in out nocopy varchar2
522 ,x_err_stage in out nocopy varchar2
523 ,x_err_stack in out nocopy varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
524 is
525 Cursor C_MANDT_CONTROLS is
526 select ASSET_HIERARCHY_PURPOSE_ID,
527 LEVEL_NUMBER,
528 CATEGORY_MANDATORY_FLAG,
529 LEASE_MANDATORY_FLAG,
530 ASSET_KEY_MANDATORY_FLAG,
531 SERIAL_NUMBER_MANDATORY_FLAG,
532 DISTRIBUTION_MANDATORY_FLAG,
533 LIFE_END_DATE_MANDATORY_FLAG,
534 DPIS_MANDATORY_FLAG,
535 CREATED_BY,
536 CREATION_DATE,
537 LAST_UPDATED_BY,
538 LAST_UPDATE_DATE,
539 LAST_UPDATE_LOGIN
540 from FA_HIERARCHY_CONTROLS
541 where asset_hierarchy_purpose_id = x_asset_hierarchy_purpose_id
542 and level_number = x_level_number;
543 mand_controlrec C_MANDT_CONTROLS%ROWTYPE;
544 l_old_err_stack varchar2(640);
545
546 Begin
547 --x_err_code := '0';
548 l_old_err_stack := x_err_stack;
549 --dbms_output.put_line('Before setting Stack');
550 x_err_stack := x_err_stack ||'->'||'VALIDATING_NODE_ATTRIBUTES';
551 --dbms_output.put_line('After setting Stack');
552
553 --Validating the existance of parameters with the mandatory flags
554 x_err_stage := 'Validating Parameters existance with Mandatory requirements';
555 open C_MANDT_CONTROLS;
556 fetch C_MANDT_CONTROLS into mand_controlrec;
557 --If Controls exists then validate for mandatory parameters
558 if (C_MANDT_CONTROLS%FOUND) then
559 if (mand_controlrec.life_end_date_mandatory_flag = 'Y'
560 and x_life_end_date is null ) then
561 x_err_code := 'CUA_LIFE_END_DATE_MANDATORY';
562 close C_MANDT_CONTROLS;
563 return;
564 end if;
565
566 if (mand_controlrec.category_mandatory_flag = 'Y'
567 and x_asset_category_id is null ) then
568 x_err_code := 'CUA_CATEGORY_MANDATORY';
569 close C_MANDT_CONTROLS;
570 return;
571 end if;
572 --Do the Mandatory check for lease only if the given category is null
573 --or the category is lease type
574 if(x_asset_category_id is null
575 OR (x_asset_category_id is not null
576 AND NOT is_catg_nonlease_type(x_asset_category_id,
577 p_log_level_rec)
578 )
579 ) then
580 if (mand_controlrec.lease_mandatory_flag = 'Y'
581 and x_lease_id is null ) then
582 x_err_code := 'CUA_LEASE_MANDATORY';
583 close C_MANDT_CONTROLS;
584 return;
585 end if;
586 end if;
587 if (mand_controlrec.asset_key_mandatory_flag = 'Y'
588 and x_asset_key_ccid is null ) then
589 x_err_code := 'CUA_ASSET_KEY_MANDATORY';
590 close C_MANDT_CONTROLS;
591 return;
592 end if;
593 if (mand_controlrec.serial_number_mandatory_flag = 'Y'
594 and x_serial_number is null ) then
595 x_err_code := 'CUA_SERIAL_NUMBER_MANDATORY';
596 close C_MANDT_CONTROLS;
597 return;
598 end if;
599
600 if (mand_controlrec.distribution_mandatory_flag = 'Y'
601 AND x_dist_set_id is null ) then
602 x_err_code := 'CUA_DISTRIBUTION_MANDATORY';
603 close C_MANDT_CONTROLS;
604 return;
605 end if;
606 end if;
607 --dbms_output.put_line('After Mandatory check');
608 --Validating the parameters
609 x_err_stage := 'Validating the Parameters';
610
611 --valodating Hierachy purpose
612 if(NOT validate_hierarchy_purpose(x_asset_hierarchy_purpose_id,
613 p_log_level_rec) )then
614 x_err_code := 'CUA_INVALID_PURPOSE';
615 return;
616 end if;
617
618 --Vaidating Asset CAtegory
619 x_err_stage := 'Validating asset Category';
620 if(x_asset_category_id is not null ) then
621 -- dbms_output.put_line('Validating Category');
622 validate_asset_category (x_book_type_code
623 ,x_asset_category_id
624 ,x_lease_id
625 ,x_err_code
626 ,x_err_stage
627 ,x_err_stack
628 ,p_log_level_rec);
629 --dbms_output.put_line('After Category Validation');
630 if(x_err_code <> '0' ) then
631 return;
632 end if;
633 end if;
634 --Validating Lease
635 x_err_stage := 'Validating Lease';
636 if(x_lease_id is not null AND NOT validate_lease(x_lease_id,p_log_level_rec) )then
637 x_err_code := 'CUA_INVALID_LEASE';
638 return;
639 end if;
640
641 --Validating Asset Key
642 x_err_stage := 'Validating Asset Key';
643 if(x_asset_key_ccid is not null AND NOT validate_asset_key(x_asset_key_ccid,p_log_level_rec)) then
644 x_err_code := 'CUA_INVALID_ASSET_KEY';
645 return;
646 end if;
647
648 --Validate Distribution Set
649 x_err_stage := 'Validating Distribution Set';
650 if(x_dist_set_id is not null AND NOT valid_dist_set(x_dist_set_id,
651 p_log_level_rec) ) then
652 x_err_code := 'CUA_INVALID_DIST_SET';
653 return;
654 end if;
655 /** Moved to procedure create_distribution_set **
656 --Validating Distributions
657 if(x_distribution_tab.count > 0 ) then
658 x_err_stage := 'Validating Distributions';
659 FOR I in 1..x_distribution_tab.count LOOP
660 --Validating Expense Account
661 if(x_distribution_tab(I).code_combination_id is not null
662 AND NOT validate_gl_ccid( x_distribution_tab(I).code_combination_id,l_CofA_id ) )then
663 x_err_code := 'CUA_INVALID_EXPENSE_ACCOUNT';
664 return;
665 end if;
666 --Validate Location
667 if(x_distribution_tab(I).location_id is not null
668 AND NOT validate_location(x_distribution_tab(I).location_id ) )then
669 x_err_code := 'CUA_INVALID_LOCATION';
670 return;
671 end if;
672 --Validate Employee
673 if(x_distribution_tab(I).assigned_to is not null
674 AND NOT validate_employee( x_distribution_tab(I).assigned_to ) )then
675 x_err_code := 'CUA_INVALID_EMPLOYEE';
676 return;
677 end if;
678 END LOOP;
679 --Validate the Sum of Disrtribution % is 100
680 x_err_stage := 'Validating Distribution percentage sum is 100';
681 FOR I in 1..x_distribution_tab.count LOOP
682 l_percent_total := nvl(l_percent_total,0) + nvl(x_distribution_tab(I).distribution_line_percentage,0);
683 END LOOP;
684 if(l_percent_total <> 100) then
685 x_err_code := 'CUA_INVALID_LINE_PERCENT_SUM';
686 end if;
687 end if;
688 **/
689
690 End validate_node_attributes;
691
692 --Function to check name is unique
693 Function check_name_unique( x_event in varchar2
694 ,x_asset_hierarchy_id in number default null
695 ,x_name in varchar2
696 ,x_asset_id in number
697 ,x_purpose_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
698 return Boolean
699 is
700 Cursor C_name_insert is Select name
701 from FA_ASSET_HIERARCHY
702 where name = x_name
703 and decode(nvl(asset_id,0),0,'N','A') = decode(nvl(x_asset_id,0),0,'N','A')
704 and asset_hierarchy_purpose_id = x_purpose_id;
705 Cursor C_name_update is Select name
706 from FA_ASSET_HIERARCHY
707 where name = x_name
708 and decode(nvl(asset_id,0),0,'N','A') = decode(nvl(x_asset_id,0),0,'N','A')
709 and asset_hierarchy_purpose_id = x_purpose_id
710 and asset_hierarchy_id <> nvl(x_asset_hierarchy_id,0);
711 l_name fa_asset_hierarchy.name%TYPE;
712 Begin
713 if(x_event = 'INSERT') then
714 -- validate for name uniqueness if passed
715 -- x_err_stage := 'Validating name uniqueness';
716 -- if (x_name is not null ) then
717 open C_NAME_insert;
718 fetch c_name_insert into l_name;
719 if(C_NAME_insert%FOUND) then
720 close C_NAME_insert;
721 --x_err_code := 'CUA_NAME_NOT_UNIQUE';
722 return FALSE;
723 end if;
724 close C_NAME_insert;
725 return TRUE;
726 elsif(x_name = 'UPDATE') then
727 open C_NAME_update;
728 fetch c_name_update into l_name;
729 if(C_NAME_update%FOUND) then
730 close C_NAME_update;
731 return FALSE;
732 end if;
733 close C_NAME_update;
734 return TRUE;
735 end if;
736 end check_name_unique;
737
738
739 Procedure validate_node( x_calling_module in varchar2 default 'A'
740 ,x_asset_hierarchy_purpose_id in out nocopy number
741 ,x_book_type_code in varchar2
742 ,x_name in varchar2 default null
743 ,x_level_number in number default 0
744 ,x_parent_hierarchy_id in number
745 ,x_hierarchy_rule_set_id in number default null
746 ,x_err_code in out nocopy varchar2
747 ,x_err_stage in out nocopy varchar2
748 ,x_err_stack in out nocopy varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
749 is
750 /* validates for a. Book is of Corporate class
751 b. parent Node exists
752 d. Book type code matches with the parent book type
753 e. Rule set book matches with the Node book
754 f. check purpose id exists if passed else return purpose ID
755 g. Check purpose book matches with book if both passed
756 h. Check either the purpose or book is passed
757 i. Level Number is 0 for asset node
758 j. Level number is a valid positive integer.
759 k. Level number is within the permissible limit
760 l. Parent level is one level higher than the current level.
761 m. Node name cannot be null for Asset node
762 */
763 l_old_err_stack varchar2(640);
764 v_book_class FA_BOOK_CONTROLS.book_class%TYPE;
765 v_book_type_code FA_BOOK_CONTROLS.book_type_code%TYPE;
766 v_name FA_ASSET_HIERARCHY.name%TYPE;
767 dummy number;
768 l_book_type_code varchar2(30);
769 v_mandatory_asset_flag varchar2(1);
770
771
772 INVALID_PURPOSE EXCEPTION;
773 PARENT_MANDATORY EXCEPTION;
774 BOOK_TYPE_NOT_EXISTS EXCEPTION;
775 INVALID_BOOK_TYPE EXCEPTION;
776 INVALID_PARENT_NODE EXCEPTION;
777 INVALID_PARENT_BOOK_TYPE EXCEPTION;
778 INVALID_RULE_SET EXCEPTION;
779 INVALID_RULE_BOOK EXCEPTION;
780 INVALID_PURPOSE_ID EXCEPTION;
781 PURPOSE_NOT_EXISTS EXCEPTION;
782
783 Cursor C_BOOK is
784 Select book_class from FA_BOOK_CONTROLS
785 where book_type_code = x_book_type_code;
786 Cursor C_PARENT is
787 select book_type_code from FA_ASSET_HIERARCHY_PURPOSE
788 where asset_hierarchy_purpose_id = (Select asset_hierarchy_purpose_id
789 from fa_asset_hierarchy
790 where asset_hierarchy_id = x_parent_hierarchy_id);
791 Cursor C_RULE is
792 Select book_type_code from FA_HIERARCHY_RULE_SET
793 where hierarchy_rule_set_id = x_hierarchy_rule_set_id;
794 Cursor C_PURPOSE is select asset_hierarchy_purpose_id,mandatory_asset_flag
795 from FA_ASSET_HIERARCHY_PURPOSE
796 where book_type_code = x_book_type_code;
797
798 Cursor C_PERMIT_LEVELS is
799 select nvl(permissible_levels,0)
800 from FA_ASSET_HIERARCHY_PURPOSE
801 where asset_hierarchy_purpose_id = x_asset_hierarchy_purpose_id;
802 v_permit_levels FA_ASSET_HIERARCHY_PURPOSE.permissible_levels%TYPE;
803 purpose_rec C_PURPOSE%ROWTYPE;
804
805 Cursor C_PARENT_LEVEL is select level_number
806 from FA_ASSET_HIERARCHY
807 where asset_hierarchy_id = x_parent_hierarchy_id;
808 v_parent_level number;
809 Begin
810 x_err_code := '0';
811 l_old_err_stack := x_err_stack;
812 x_err_stack := x_err_stack ||'->'|| 'VALIDATING_NODE';
813 --check for validity of purpose if not null
814 x_err_stage := 'Validating Purpose ID';
815 If (nvl(x_asset_hierarchy_purpose_id,0) <> 0) then
816 Begin
817 select 1 into dummy
818 from fa_asset_hierarchy_purpose
819 where asset_hierarchy_purpose_id = x_asset_hierarchy_purpose_id;
820 Exception
821 when no_data_found then
822 raise INVALID_PURPOSE_ID;
823 -- x_err_code := 'CUA_INVALID_PURPOSE';
824 End;
825 else
826 open C_PURPOSE;
827 fetch C_PURPOSE into purpose_rec;
828 if(C_PURPOSE%FOUND) then
829 x_asset_hierarchy_purpose_id := purpose_rec.asset_hierarchy_purpose_id;
830 if(x_calling_module = 'A'
831 AND purpose_rec.mandatory_asset_flag = 'Y'
832 AND nvl(x_parent_hierarchy_id,0) = 0) then
833 close C_PURPOSE;
834 raise PARENT_MANDATORY;
835 end if;
836 end if;
837 close C_PURPOSE;
838 end if;
839
840 --Validate Parent node
841 if (nvl(x_parent_hierarchy_id,0) <> 0 ) then
842 x_err_stage := 'Validating Parent Node';
843 open C_PARENT;
844 fetch C_PARENT into v_book_type_code;
845 if C_PARENT%NOTFOUND then
846 raise INVALID_PARENT_NODE;
847 else
848 if v_book_type_code <> x_book_type_code then
849 raise INVALID_PARENT_BOOK_TYPE;
850 end if;
851 end if;
852 close C_PARENT;
853 end if;
854 --Validating Level number
855 --check level number is a valid positive integer
856 x_err_stage := 'Validating level is a positive integer';
857 if( NOT validate_level_number(x_level_number, p_log_level_rec) ) then
858 x_err_code := 'CUA_INVALID_LEVEL_NUMBER';
859 return;
860 end if;
861 --Check level number is less than the permissible levels in purpose if purpose is given
862 x_err_stage := 'Validating level with permissible levels';
863 if(x_asset_hierarchy_purpose_id is not null ) then
864 open C_PERMIT_LEVELS;
865 fetch C_PERMIT_LEVELS into v_permit_levels;
866 if(C_PERMIT_LEVELS%NOTFOUND) then
867 x_err_code := 'CUA_INVALID_PURPOSE';
868 close C_PERMIT_LEVELS;
869 return;
870 end if;
871 close C_PERMIT_LEVELS;
872 if(v_permit_levels <> 0 ) then
873 if( x_level_number > v_permit_levels) then
874 x_err_code := 'CUA_LEVEL_EXCEEDS_PERMIT';
875 return;
876 end if;
877 end if;
878 end if;
879 --check level number is 0 for asset node
880 x_err_stage := 'Validating level number for the node';
881 if((x_calling_module = 'A' AND x_level_number <> 0 )
882 OR(x_calling_module = 'N' AND x_level_number = 0 ) ) then
883 x_err_code := 'CUA_INVALID_ASSET_LEVEL';
884 return;
885 end if;
886 -- check parent level is 1 greater than the node level
887 x_err_stage := 'Validating parent is one level higher to current node';
888 if(nvl(x_parent_hierarchy_id,0) <> 0 ) then
889 open C_PARENT_LEVEL;
890 fetch C_PARENT_LEVEL into v_parent_level;
891 if(C_PARENT_LEVEL%NOTFOUND) then
892 x_err_code := 'CUA_INVALID_PARENT';
893 close C_PARENT_LEVEL;
894 return;
895 end if;
896 close C_PARENT_LEVEL;
897 if (x_level_number <> v_parent_level - 1 ) then
898 x_err_code := 'CUA_INVALID_NODE_PARENT_LEVEL';
899 return;
900 end if;
901 end if;
902
903 -- Validate Book Type
904 x_err_stage := 'Validating Book Class';
905 open C_BOOK;
906 fetch C_BOOK into v_book_class;
907 if C_BOOK%NOTFOUND then
908 raise BOOK_TYPE_NOT_EXISTS;
909 else
910 if v_book_class <> 'CORPORATE' then
911 raise INVALID_BOOK_TYPE;
912 end if;
913 end if;
914 close C_BOOK;
915
916 --Validate Rule Set
917 if (nvl(x_hierarchy_rule_set_id,0) <> 0) then
918 x_err_stage := 'Validating Rule Set';
919 open C_RULE;
920 fetch C_RULE into v_book_type_code;
921 if C_RULE%NOTFOUND then
922 raise INVALID_RULE_SET;
923 else
924 if (v_book_type_code <> x_book_type_code) then
925 raise INVALID_RULE_BOOK;
926 end if;
927 end if;
928 close C_RULE;
929 end if;
930 x_err_stack := l_old_err_stack;
931 Exception
932 when PARENT_MANDATORY then
933 x_err_code := 'CUA_PARENT_MANDATORY';
934 when INVALID_PURPOSE_ID then
935 x_err_code := 'CUA_INVALID_PURPOSE';
936 when PURPOSE_NOT_EXISTS then
937 x_err_code := 'CUA_PURPOSE_NOT_EXISTS';
938 when BOOK_TYPE_NOT_EXISTS then
939 x_err_code := 'CUA_BOOK_TYPE_NOT_EXISTS';
940 close C_BOOK;
941 when INVALID_BOOK_TYPE then
942 x_err_code := 'CUA_INVALID_BOOK_TYPE';
943 close C_BOOK;
944 when INVALID_PARENT_NODE then
945 x_err_code := 'CUA_INVALID_PARENT_NODE';
946 close C_PARENT;
947 when INVALID_PARENT_BOOK_TYPE then
948 x_err_code := 'CUA_INVALID_PARENT_BOOK_TYPE';
949 close C_PARENT;
950 when INVALID_RULE_SET then
951 x_err_code := 'CUA_INVALID_RULE_SET';
952 close C_RULE;
953 when INVALID_RULE_BOOK then
954 x_err_code := 'CUA_INVALID_PARENT_BOOK_TYPE';
955 close C_RULE;
956 when others then
957 x_err_code := SQLCODE;
958 if C_BOOK%ISOPEN then
959 close C_BOOK;
960 end if;
961 if C_PARENT%ISOPEN then
962 close C_PARENT;
963 end if;
964 if C_PURPOSE%ISOPEN then
965 close C_PURPOSE;
966 end if;
967 if C_RULE%ISOPEN then
968 close C_RULE;
969 end if;
970
971 End Validate_node;
972
973 --Procedure to create node along with the attributes
974 procedure create_node_with_attributes(
975 -- Arguments required for Public APIs
976 x_err_code in out nocopy varchar2
977 , x_err_stage in out nocopy Varchar2
978 , x_err_stack in out nocopy varchar2
979 -- Arguments for Node Creation
980 , x_asset_hierarchy_purpose_id in NUMBER
981 , x_asset_hierarchy_id in out nocopy NUMBER
982 , x_name in VARCHAR2 default null
983 , x_level_number in NUMBER
984 , x_hierarchy_rule_set_id in NUMBER default null
985 , X_CREATION_DATE in DATE default trunc(sysdate)
986 , X_CREATED_BY in NUMBER := FND_GLOBAL.USER_ID
987 , X_LAST_UPDATE_DATE in DATE default trunc(sysdate)
988 , X_LAST_UPDATED_BY in NUMBER := FND_GLOBAL.USER_ID
989 , X_LAST_UPDATE_LOGIN in NUMBER := FND_GLOBAL.USER_ID
990 , x_description in VARCHAR2 default null
991 , x_parent_hierarchy_id in NUMBER default null
992 , x_lowest_level_flag in NUMBER default null
993 , x_depreciation_start_date in date default null
994 , x_asset_id in number default null
995 , X_ATTRIBUTE_CATEGORY in VARCHAR2 default null
996 , X_ATTRIBUTE1 in VARCHAR2 default null
997 , X_ATTRIBUTE2 in VARCHAR2 default null
998 , X_ATTRIBUTE3 in VARCHAR2 default null
999 , X_ATTRIBUTE4 in VARCHAR2 default null
1000 , X_ATTRIBUTE5 in VARCHAR2 default null
1001 , X_ATTRIBUTE6 in VARCHAR2 default null
1002 , X_ATTRIBUTE7 in VARCHAR2 default null
1003 , X_ATTRIBUTE8 in VARCHAR2 default null
1004 , X_ATTRIBUTE9 in VARCHAR2 default null
1005 , X_ATTRIBUTE10 in VARCHAR2 default null
1006 , X_ATTRIBUTE11 in VARCHAR2 default null
1007 , X_ATTRIBUTE12 in VARCHAR2 default null
1008 , X_ATTRIBUTE13 in VARCHAR2 default null
1009 , X_ATTRIBUTE14 in VARCHAR2 default null
1010 , X_ATTRIBUTE15 in VARCHAR2 default null
1011 --Parameters for Node Attributes
1012 ,x_attribute_book_type_code in varchar2 default null
1013 ,x_asset_category_id in number default null
1014 ,x_lease_id in NUMBER default null
1015 ,x_asset_key_ccid in number default null
1016 ,x_serial_number in varchar2 default null
1017 ,x_life_end_date in date default null
1018 ,x_distribution_tab in FA_CUA_HIERARCHY_PKG.distribution_tabtype default FA_CUA_HIERARCHY_PKG.distribution_tab
1019 ,p_log_level_rec IN fa_api_types.log_level_rec_type)
1020 is
1021 l_old_err_stack varchar2(640);
1022 l_rowid varchar2(240) default null;
1023 l_distribution_id number default null;
1024 I BINARY_INTEGER;
1025 l_dist_set_id number default null;
1026 Begin
1027 --Call the crete_node API to create the Node first
1028 l_old_err_stack := x_err_stack;
1029 x_err_code := '0';
1030 x_err_stage := 'Calling CREATE_NODE';
1031 FA_CUA_HIERARCHY_PKG.create_node(
1032 x_err_code
1033 , x_err_stage
1034 , x_err_stack
1035 , x_asset_hierarchy_purpose_id
1036 , x_asset_hierarchy_id
1037 , x_name
1038 , x_level_number
1039 , x_hierarchy_rule_set_id
1040 , X_CREATION_DATE
1041 , X_CREATED_BY
1042 , X_LAST_UPDATE_DATE
1043 , X_LAST_UPDATED_BY
1044 , X_LAST_UPDATE_LOGIN
1045 , x_description
1046 , x_parent_hierarchy_id
1047 , x_lowest_level_flag
1048 , x_depreciation_start_date
1049 , x_asset_id
1050 , X_ATTRIBUTE_CATEGORY
1051 , X_ATTRIBUTE1
1052 , X_ATTRIBUTE2
1053 , X_ATTRIBUTE3
1054 , X_ATTRIBUTE4
1055 , X_ATTRIBUTE5
1056 , X_ATTRIBUTE6
1057 , X_ATTRIBUTE7
1058 , X_ATTRIBUTE8
1059 , X_ATTRIBUTE9
1060 , X_ATTRIBUTE10
1061 , X_ATTRIBUTE11
1062 , X_ATTRIBUTE12
1063 , X_ATTRIBUTE13
1064 , X_ATTRIBUTE14
1065 , X_ATTRIBUTE15
1066 , p_log_level_rec => p_log_level_rec);
1067
1068 if(x_err_code <> '0' ) then
1069 rollback work;
1070 return;
1071 end if;
1072 /** Validate and create the distribution set if distribution
1073 table is passed **/
1074 if(nvl(x_asset_id,0) = 0
1075 AND x_attribute_book_type_code is not null
1076 AND x_distribution_tab.count > 0 ) then
1077 FA_CUA_HIERARCHY_PKG.create_distribution_set
1078 ( l_dist_set_id
1079 ,x_attribute_book_type_code
1080 ,x_distribution_tab
1081 ,x_err_code
1082 ,x_err_stage
1083 ,x_err_stack , p_log_level_rec => p_log_level_rec);
1084 end if;
1085 if(x_err_code <> '0' ) then
1086 rollback work;
1087 return;
1088 end if;
1089 /** Call the Validate Attributes and create attributes process
1090 only if the Node is a Non Asset Node and the Attribute Book
1091 and one of the other attribute is given **/
1092 if (nvl(x_asset_id,0) = 0
1093 AND x_attribute_book_type_code is not null
1094 AND ( x_asset_category_id is not null
1095 OR x_lease_id is not null
1096 OR x_asset_key_ccid is not null
1097 OR x_serial_number is not null
1098 OR x_life_end_date is not null
1099 OR l_dist_set_id is not null
1100 )
1101 ) then
1102 x_err_stage := 'Calling Validate_node_attributes';
1103 FA_CUA_HIERARCHY_PKG.validate_node_attributes
1104 (x_asset_hierarchy_purpose_id
1105 ,x_asset_hierarchy_id
1106 ,x_level_number
1107 ,x_attribute_book_type_code
1108 ,x_asset_category_id
1109 ,x_lease_id
1110 ,x_asset_key_ccid
1111 ,x_serial_number
1112 ,x_life_end_date
1113 ,l_dist_set_id
1114 ,x_err_code
1115 ,x_err_stage
1116 ,x_err_stack , p_log_level_rec => p_log_level_rec);
1117 end if;
1118 if(x_err_code <> '0') then
1119 rollback work;
1120 return;
1121 else
1122 x_err_stage := 'Inserting into FA_ASSET_HIERARCHY_VALES table';
1123 if (x_attribute_book_type_code is not null
1124 AND ( x_asset_category_id is not null
1125 OR x_lease_id is not null
1126 OR x_asset_key_ccid is not null
1127 OR x_serial_number is not null
1128 OR x_life_end_date is not null
1129 OR l_dist_set_id is not null)
1130 ) then
1131 --If corporate Book insert all values else insert only life end date
1132 FA_CUA_HIERARCHY_VALUES_PKG.Insert_row (
1133 l_rowid
1134 , x_asset_hierarchy_id
1135 , x_attribute_book_type_code
1136 , x_asset_category_id
1137 , x_lease_id
1138 , x_asset_key_ccid
1139 , x_serial_number
1140 , x_life_end_date
1141 , l_dist_set_id
1142 , X_CREATION_DATE
1143 , X_CREATED_BY
1144 , X_LAST_UPDATE_DATE
1145 , X_LAST_UPDATED_BY
1146 , X_LAST_UPDATE_LOGIN , p_log_level_rec => p_log_level_rec);
1147 end if;
1148 /** Moved to Create_dist_set procedure **
1149 x_err_stage := 'Inserting into FA_HIERARCHY_DISTRIBUTIONS table';
1150 l_rowid := null;
1151 --dbms_output.put_line('Before Inserting Distributions');
1152 --dbms_output.put_line('count:'||to_char(x_distribution_tab.count));
1153 if( x_distribution_tab.count > 0 ) then
1154 FOR I in 1..x_distribution_tab.count LOOP
1155 l_distribution_id := null;
1156 FA_CUA_HR_DISTRIBUTION_PKG.Insert_row (
1157 l_rowid
1158 , l_distribution_id
1159 , x_asset_hierarchy_purpose_id
1160 , x_asset_hierarchy_id
1161 , x_attribute_book_type_code
1162 , x_distribution_tab(I).distribution_line_percentage
1163 , x_distribution_tab(I).code_combination_id
1164 , x_distribution_tab(I).location_id
1165 , x_distribution_tab(I).assigned_to
1166 , X_CREATION_DATE
1167 , X_CREATED_BY
1168 , X_LAST_UPDATE_DATE
1169 , X_LAST_UPDATED_BY
1170 , X_LAST_UPDATE_LOGIN );
1171 --dbms_output.put_line('Distribution ID:'||to_char(l_distribution_id));
1172 END LOOP;
1173 end if;
1174 ****/
1175 end if;
1176 x_err_stack := l_old_err_stack;
1177 Exception
1178 when others then
1179 x_err_code := sqlerrm;
1180 End create_node_with_attributes;
1181
1182 procedure create_node(
1183 -- Arguments required for Public APIs
1184 x_err_code in out nocopy varchar2
1185 , x_err_stage in out nocopy Varchar2
1186 , x_err_stack in out nocopy varchar2
1187 -- Arguments for Node Creation
1188 , x_asset_hierarchy_purpose_id in NUMBER
1189 , x_asset_hierarchy_id in out nocopy NUMBER
1190 , x_name in VARCHAR2 default null
1191 , x_level_number in NUMBER
1192 , x_hierarchy_rule_set_id in NUMBER default null
1193 , X_CREATION_DATE in DATE default trunc(sysdate)
1194 , X_CREATED_BY in NUMBER := FND_GLOBAL.USER_ID
1195 , X_LAST_UPDATE_DATE in DATE default trunc(sysdate)
1196 , X_LAST_UPDATED_BY in NUMBER := FND_GLOBAL.USER_ID
1197 , X_LAST_UPDATE_LOGIN in NUMBER := FND_GLOBAL.USER_ID
1198 , x_description in VARCHAR2 default null
1199 , x_parent_hierarchy_id in NUMBER default null
1200 , x_lowest_level_flag in NUMBER default null
1201 , x_depreciation_start_date in date default null
1202 , x_asset_id in number default null
1203 , X_ATTRIBUTE_CATEGORY in VARCHAR2 default null
1204 , X_ATTRIBUTE1 in VARCHAR2 default null
1205 , X_ATTRIBUTE2 in VARCHAR2 default null
1206 , X_ATTRIBUTE3 in VARCHAR2 default null
1207 , X_ATTRIBUTE4 in VARCHAR2 default null
1208 , X_ATTRIBUTE5 in VARCHAR2 default null
1209 , X_ATTRIBUTE6 in VARCHAR2 default null
1210 , X_ATTRIBUTE7 in VARCHAR2 default null
1211 , X_ATTRIBUTE8 in VARCHAR2 default null
1212 , X_ATTRIBUTE9 in VARCHAR2 default null
1213 , X_ATTRIBUTE10 in VARCHAR2 default null
1214 , X_ATTRIBUTE11 in VARCHAR2 default null
1215 , X_ATTRIBUTE12 in VARCHAR2 default null
1216 , X_ATTRIBUTE13 in VARCHAR2 default null
1217 , X_ATTRIBUTE14 in VARCHAR2 default null
1218 , X_ATTRIBUTE15 in VARCHAR2 default null
1219 ,p_log_level_rec IN fa_api_types.log_level_rec_type)
1220 is
1221 Cursor C_PURPOSE_BOOK is select book_type_code
1222 from FA_ASSET_HIERARCHY_PURPOSE
1223 where asset_hierarchy_purpose_id = x_asset_hierarchy_purpose_id;
1224
1225 CURSOR C_NAME1 is SELECT ASSET_HIERARCHY_ID
1226 FROM FA_ASSET_HIERARCHY
1227 WHERE NAME = x_name
1228 AND NVL (ASSET_ID, 0) = 0
1229 AND ASSET_HIERARCHY_PURPOSE_ID = x_asset_hierarchy_purpose_id;
1230
1231 CURSOR C_NAME2 is SELECT ASSET_HIERARCHY_ID
1232 FROM FA_ASSET_HIERARCHY
1233 WHERE NAME = x_name
1234 AND ASSET_ID = x_asset_id
1235 AND ASSET_HIERARCHY_PURPOSE_ID = x_asset_hierarchy_purpose_id;
1236
1237 Cursor C_PERMIT_LEVELS is select nvl(permissible_levels,0)
1238 from FA_ASSET_HIERARCHY_PURPOSE
1239 where asset_hierarchy_purpose_id = x_asset_hierarchy_purpose_id;
1240 v_permit_levels FA_ASSET_HIERARCHY_PURPOSE.permissible_levels%TYPE;
1241 l_rowid varchar2(30);
1242 l_old_err_stack varchar2(240);
1243 l_book_type_code varchar2(30);
1244 l_asset_hierarchy_purpose_id number;
1245 l_calling_module varchar2(1);
1246 INVALID_PURPOSE EXCEPTION;
1247 Begin
1248 x_err_code := '0';
1249 l_old_err_stack := x_err_stack;
1250 x_err_stack := x_err_stack||'->'||'CREATE_NODE';
1251 --Validating the node already exists with this name and if so returns the ID
1252 x_err_stage := 'Check for the existance of the node';
1253 if x_name is not null then
1254 -- Fix for Bug #1064659. Check x_asset_id for performance considerations.
1255 if (x_asset_id is null) or (x_asset_id = 0) then
1256 open C_NAME1;
1257 fetch C_NAME1 into x_asset_hierarchy_id;
1258 if C_NAME1%FOUND then
1259 close C_NAME1;
1260 return;
1261 end if;
1262 close C_NAME1;
1263 else
1264 open C_NAME2;
1265 fetch C_NAME2 into x_asset_hierarchy_id;
1266 if C_NAME2%FOUND then
1267 close C_NAME2;
1268 return;
1269 end if;
1270 close C_NAME2;
1271 end if;
1272 end if;
1273 --Check whether batch for this batch is un applied for asset nodes,if so return
1274 x_err_stage := 'Checking for Pending Parent Batches';
1275 if(nvl(x_asset_id,0) <> 0 ) then
1276 open C_PURPOSE_BOOK;
1277 fetch C_PURPOSE_BOOK into l_book_type_code;
1278 close C_PURPOSE_BOOK;
1279
1280 -- msiddiqu bugfix 1613852
1281 if fa_cua_hr_retirements_pkg.check_pending_batch
1282 ( x_calling_function => 'MASS_ADDITION',
1283 x_book_type_code => l_book_type_code,
1284 x_event_code => 'ADDITION',
1285 x_asset_id => x_asset_id,
1286 x_node_id => x_parent_hierarchy_id,
1287 x_category_id => null,
1288 x_attribute => null,
1289 x_conc_request_id => null,
1290 x_status => x_err_code , p_log_level_rec => p_log_level_rec) then
1291 /** commented by msiddiqu for bugfix 1613852
1292 if(fa_cua_hr_retirements_pkg.check_pending_batch('ADDITION'
1293 ,l_book_type_code
1294 ,x_asset_id
1295 ,x_parent_hierarchy_id)
1296 ) then **/
1297 x_err_code := 'CUA_PENDING_PARENT_BATCH';
1298 return;
1299 end if;
1300 end if;
1301 /** Validate the level number is less than the permissible levels.
1302 Though the check is performed in validate node, it is repeated here because
1303 this check may not be performed at Validate node level if the
1304 purpose ID is not known. **/
1305 x_err_stage := 'Validating Level Number with permit level - Create Node';
1306 open C_PERMIT_LEVELS;
1307 fetch C_PERMIT_LEVELS into v_permit_levels;
1308 if(C_PERMIT_LEVELS%NOTFOUND) then
1309 x_err_code := 'CUA_INVALID_PURPOSE';
1310 close C_PERMIT_LEVELS;
1311 return;
1312 end if;
1313 close C_PERMIT_LEVELS;
1314 if(v_permit_levels <> 0 ) then
1315 if( x_level_number > v_permit_levels) then
1316 x_err_code := 'CUA_LEVEL_EXCEEDS_PERMIT';
1317 return;
1318 end if;
1319 end if;
1320 --Validating depreciation start date is given if DPIS is Mandatory
1321 x_err_stage := 'Validating Date Placed in Service is Mandatory';
1322 if ( x_level_number <> 0
1323 AND x_depreciation_start_date is null
1324 AND is_attribute_mandatory(x_asset_hierarchy_purpose_id
1325 ,x_level_number
1326 ,'DPIS'
1327 ,p_log_level_rec)
1328 ) then
1329 x_err_code := 'CUA_DPIS_MANDATORY';
1330 return;
1331 end if;
1332 -- Check Node name is not null for Asset Node
1333 x_err_stage := 'Checking for Mandatory name for Asset Node';
1334 if (x_level_number = 0 AND x_name is null) then
1335 x_err_code := 'CUA_NAME_MANDATORY_ASSET';
1336 return;
1337 end if;
1338 x_err_stage := 'Getting the book from purpose';
1339 open C_PURPOSE_BOOK;
1340 fetch C_PURPOSE_BOOK into l_book_type_code;
1341 if C_PURPOSE_BOOK%NOTFOUND then
1342 close C_PURPOSE_BOOK;
1343 raise INVALID_PURPOSE;
1344 end if;
1345 close C_PURPOSE_BOOK;
1346 if(nvl(x_asset_id,0) = 0) then
1347 l_calling_module := 'N';
1348 else
1349 l_calling_module := 'A';
1350 end if;
1351 x_err_stage := 'Validating Node';
1352 FA_CUA_HIERARCHY_PKG.Validate_node(
1353 l_calling_module
1354 , l_asset_hierarchy_purpose_id
1355 , l_book_type_code
1356 , x_name
1357 , x_level_number
1358 , x_parent_hierarchy_id
1359 , x_hierarchy_rule_set_id
1360 , x_err_code
1361 , x_err_stage
1362 , x_err_stack, p_log_level_rec => p_log_level_rec);
1363 if (x_err_code = '0') then
1364 x_err_stage := 'Inserting Node';
1365 -- x_err_stack := x_err_stack ||'->'||'INASERT_NODE';
1366 FA_CUA_HIERARCHY_PKG.Insert_row (
1367 l_rowid
1368 , x_asset_hierarchy_purpose_id
1369 , x_asset_hierarchy_id
1370 , x_name
1371 , x_level_number
1372 , x_hierarchy_rule_set_id
1373 , X_CREATION_DATE
1374 , X_CREATED_BY
1375 , X_LAST_UPDATE_DATE
1376 , X_LAST_UPDATED_BY
1377 , X_LAST_UPDATE_LOGIN
1378 , x_description
1379 , x_parent_hierarchy_id
1380 , x_lowest_level_flag
1381 , x_depreciation_start_date
1382 , x_asset_id
1383 , X_ATTRIBUTE_CATEGORY
1384 , X_ATTRIBUTE1
1385 , X_ATTRIBUTE2
1386 , X_ATTRIBUTE3
1387 , X_ATTRIBUTE4
1388 , X_ATTRIBUTE5
1389 , X_ATTRIBUTE6
1390 , X_ATTRIBUTE7
1391 , X_ATTRIBUTE8
1392 , X_ATTRIBUTE9
1393 , X_ATTRIBUTE10
1394 , X_ATTRIBUTE11
1395 , X_ATTRIBUTE12
1396 , X_ATTRIBUTE13
1397 , X_ATTRIBUTE14
1398 , X_ATTRIBUTE15 , p_log_level_rec => p_log_level_rec);
1399 x_err_stack := l_old_err_stack;
1400 end if;
1401
1402 exception
1403 when INVALID_PURPOSE then
1404 x_err_stage :='Unable to get Purpose Book';
1405 x_err_code := 'CUA_INVALID_PURPOSE';
1406 when no_data_found then
1407 x_err_stage := 'CREATE_NODE_NO_DATA';
1408 x_err_code := SQLCODE;
1409 when others then
1410 x_err_stage := 'CREATE_NODE_WHEN_OTHERS';
1411 x_err_code := SQLCODE;
1412 end create_node;
1413
1414
1415 procedure LOCK_ROW (
1416 x_asset_hierarchy_purpose_id in NUMBER
1417 , x_asset_hierarchy_id in NUMBER
1418 , x_name in VARCHAR2
1419 , x_level_number in number
1420 , x_hierarchy_rule_set_id in NUMBER
1421 , x_description in VARCHAR2
1422 , x_parent_hierarchy_id in NUMBER
1423 , x_lowest_level_flag in NUMBER
1424 , x_depreciation_start_date in date
1425 , x_asset_id in number
1426 , X_ATTRIBUTE_CATEGORY in VARCHAR2
1427 , X_ATTRIBUTE1 in VARCHAR2
1428 , X_ATTRIBUTE2 in VARCHAR2
1429 , X_ATTRIBUTE3 in VARCHAR2
1430 , X_ATTRIBUTE4 in VARCHAR2
1431 , X_ATTRIBUTE5 in VARCHAR2
1432 , X_ATTRIBUTE6 in VARCHAR2
1433 , X_ATTRIBUTE7 in VARCHAR2
1434 , X_ATTRIBUTE8 in VARCHAR2
1435 , X_ATTRIBUTE9 in VARCHAR2
1436 , X_ATTRIBUTE10 in VARCHAR2
1437 , X_ATTRIBUTE11 in VARCHAR2
1438 , X_ATTRIBUTE12 in VARCHAR2
1439 , X_ATTRIBUTE13 in VARCHAR2
1440 , X_ATTRIBUTE14 in VARCHAR2
1441 , X_ATTRIBUTE15 in VARCHAR2
1442 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
1443 cursor c1 is select
1444 name
1445 , level_number
1446 , hierarchy_rule_set_id
1447 , description
1448 , parent_hierarchy_id
1449 , lowest_level_flag
1450 , depreciation_start_date
1451 , asset_id
1452 , ATTRIBUTE_CATEGORY
1453 , ATTRIBUTE1
1454 , ATTRIBUTE2
1455 , ATTRIBUTE3
1456 , ATTRIBUTE4
1457 , ATTRIBUTE5
1458 , ATTRIBUTE6
1459 , ATTRIBUTE7
1460 , ATTRIBUTE8
1461 , ATTRIBUTE9
1462 , ATTRIBUTE10
1463 , ATTRIBUTE11
1464 , ATTRIBUTE12
1465 , ATTRIBUTE13
1466 , ATTRIBUTE14
1467 , ATTRIBUTE15
1468 from FA_ASSET_HIERARCHY
1469 where asset_hierarchy_id = x_asset_hierarchy_id
1470 and nvl(asset_hierarchy_purpose_id,1) = nvl(x_asset_hierarchy_purpose_id,1)
1471 for update of asset_hierarchy_id nowait;
1472 tlinfo c1%rowtype;
1473
1474 begin
1475 open c1;
1476 fetch c1 into tlinfo;
1477 if (c1%notfound) then
1478 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1479 app_exception.raise_exception;
1480 close c1;
1481 return;
1482 end if;
1483 close c1;
1484 if( nvl(x_asset_id,0) = 0 ) then
1485 if ( (tlinfo.NAME = X_NAME)
1486 AND (tlinfo.level_number = x_level_number)
1487 AND ((tlinfo.hierarchy_rule_set_id = x_hierarchy_rule_set_id)
1488 OR ((tlinfo.hierarchy_rule_set_id is null)
1489 AND (x_hierarchy_rule_set_id is null)))
1490 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
1491 OR ((tlinfo.DESCRIPTION is null)
1492 AND (X_DESCRIPTION is null)))
1493 AND ((tlinfo.PARENT_HIERARCHY_ID = X_PARENT_HIERARCHY_ID)
1494 OR ((tlinfo.PARENT_HIERARCHY_ID is null)
1495 AND (X_PARENT_HIERARCHY_ID is null)))
1496 AND ((tlinfo.LOWEST_LEVEL_FLAG = X_LOWEST_LEVEL_FLAG)
1497 OR ((tlinfo.LOWEST_LEVEL_FLAG is null)
1498 AND (X_LOWEST_LEVEL_FLAG is null)))
1499 AND ((tlinfo.DEPRECIATION_START_DATE = X_DEPRECIATION_START_DATE)
1500 OR ((tlinfo.DEPRECIATION_START_DATE is null)
1501 AND (X_DEPRECIATION_START_DATE is null)))
1502 AND ((tlinfo.ASSET_ID = X_ASSET_ID)
1503 OR ((tlinfo.ASSET_ID is null)
1504 AND (X_ASSET_ID is null)))
1505 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1506 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
1507 AND (X_ATTRIBUTE_CATEGORY is null)))
1508 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
1509 OR ((tlinfo.ATTRIBUTE1 is null)
1510 AND (X_ATTRIBUTE1 is null)))
1511 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
1512 OR ((tlinfo.ATTRIBUTE2 is null)
1513 AND (X_ATTRIBUTE2 is null)))
1514 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
1515 OR ((tlinfo.ATTRIBUTE3 is null)
1516 AND (X_ATTRIBUTE3 is null)))
1517 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
1518 OR ((tlinfo.ATTRIBUTE4 is null)
1519 AND (X_ATTRIBUTE4 is null)))
1520 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
1521 OR ((tlinfo.ATTRIBUTE5 is null)
1522 AND (X_ATTRIBUTE5 is null)))
1523 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
1524 OR ((tlinfo.ATTRIBUTE6 is null)
1525 AND (X_ATTRIBUTE6 is null)))
1526 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
1527 OR ((tlinfo.ATTRIBUTE7 is null)
1528 AND (X_ATTRIBUTE7 is null)))
1529 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
1530 OR ((tlinfo.ATTRIBUTE8 is null)
1531 AND (X_ATTRIBUTE8 is null)))
1532 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
1533 OR ((tlinfo.ATTRIBUTE9 is null)
1534 AND (X_ATTRIBUTE9 is null)))
1535 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
1536 OR ((tlinfo.ATTRIBUTE10 is null)
1537 AND (X_ATTRIBUTE10 is null)))
1538 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
1539 OR ((tlinfo.ATTRIBUTE11 is null)
1540 AND (X_ATTRIBUTE11 is null)))
1541 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1542 OR ((tlinfo.ATTRIBUTE12 is null)
1543 AND (X_ATTRIBUTE12 is null)))
1544 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1545 OR ((tlinfo.ATTRIBUTE13 is null)
1546 AND (X_ATTRIBUTE13 is null)))
1547 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1548 OR ((tlinfo.ATTRIBUTE14 is null)
1549 AND (X_ATTRIBUTE14 is null)))
1550 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1551 OR ((tlinfo.ATTRIBUTE15 is null)
1552 AND (X_ATTRIBUTE15 is null)))
1553 ) then
1554 null;
1555 else
1556 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1557 app_exception.raise_exception;
1558 end if;
1559 else
1560 if ( (tlinfo.NAME = X_NAME)
1561 AND (tlinfo.level_number = x_level_number)
1562 AND ((tlinfo.hierarchy_rule_set_id = x_hierarchy_rule_set_id)
1563 OR ((tlinfo.hierarchy_rule_set_id is null)
1564 AND (x_hierarchy_rule_set_id is null)))
1565 --AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
1566 -- OR ((tlinfo.DESCRIPTION is null)
1567 -- AND (X_DESCRIPTION is null)))
1568 AND ((tlinfo.PARENT_HIERARCHY_ID = X_PARENT_HIERARCHY_ID)
1569 OR ((tlinfo.PARENT_HIERARCHY_ID is null)
1570 AND (X_PARENT_HIERARCHY_ID is null)))
1571 AND ((tlinfo.LOWEST_LEVEL_FLAG = X_LOWEST_LEVEL_FLAG)
1572 OR ((tlinfo.LOWEST_LEVEL_FLAG is null)
1573 AND (X_LOWEST_LEVEL_FLAG is null)))
1574 -- AND ((tlinfo.DEPRECIATION_START_DATE = X_DEPRECIATION_START_DATE)
1575 -- OR ((tlinfo.DEPRECIATION_START_DATE is null)
1576 -- AND (X_DEPRECIATION_START_DATE is null)))
1577 AND ((tlinfo.ASSET_ID = X_ASSET_ID)
1578 OR ((tlinfo.ASSET_ID is null)
1579 AND (X_ASSET_ID is null)))
1580 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1581 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
1582 AND (X_ATTRIBUTE_CATEGORY is null)))
1583 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
1584 OR ((tlinfo.ATTRIBUTE1 is null)
1585 AND (X_ATTRIBUTE1 is null)))
1586 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
1587 OR ((tlinfo.ATTRIBUTE2 is null)
1588 AND (X_ATTRIBUTE2 is null)))
1589 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
1590 OR ((tlinfo.ATTRIBUTE3 is null)
1591 AND (X_ATTRIBUTE3 is null)))
1592 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
1593 OR ((tlinfo.ATTRIBUTE4 is null)
1594 AND (X_ATTRIBUTE4 is null)))
1595 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
1596 OR ((tlinfo.ATTRIBUTE5 is null)
1597 AND (X_ATTRIBUTE5 is null)))
1598 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
1599 OR ((tlinfo.ATTRIBUTE6 is null)
1600 AND (X_ATTRIBUTE6 is null)))
1601 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
1602 OR ((tlinfo.ATTRIBUTE7 is null)
1603 AND (X_ATTRIBUTE7 is null)))
1604 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
1605 OR ((tlinfo.ATTRIBUTE8 is null)
1606 AND (X_ATTRIBUTE8 is null)))
1607 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
1608 OR ((tlinfo.ATTRIBUTE9 is null)
1609 AND (X_ATTRIBUTE9 is null)))
1610 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
1611 OR ((tlinfo.ATTRIBUTE10 is null)
1612 AND (X_ATTRIBUTE10 is null)))
1613 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
1614 OR ((tlinfo.ATTRIBUTE11 is null)
1615 AND (X_ATTRIBUTE11 is null)))
1616 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1617 OR ((tlinfo.ATTRIBUTE12 is null)
1618 AND (X_ATTRIBUTE12 is null)))
1619 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1620 OR ((tlinfo.ATTRIBUTE13 is null)
1621 AND (X_ATTRIBUTE13 is null)))
1622 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1623 OR ((tlinfo.ATTRIBUTE14 is null)
1624 AND (X_ATTRIBUTE14 is null)))
1625 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1626 OR ((tlinfo.ATTRIBUTE15 is null)
1627 AND (X_ATTRIBUTE15 is null)))
1628 ) then
1629 null;
1630 else
1631 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1632 app_exception.raise_exception;
1633 end if;
1634 end if;
1635 return;
1636 end LOCK_ROW;
1637
1638 procedure UPDATE_ROW (
1639 x_asset_hierarchy_purpose_id in NUMBER
1640 , x_asset_hierarchy_id in NUMBER
1641 , x_name in VARCHAR2
1642 , x_level_number in NUMBER
1643 , x_hierarchy_rule_set_id in NUMBER
1644 , X_LAST_UPDATE_DATE in DATE
1645 , X_LAST_UPDATED_BY in NUMBER
1646 , X_LAST_UPDATE_LOGIN in NUMBER
1647 , x_description in VARCHAR2
1648 , x_parent_hierarchy_id in NUMBER
1649 , x_lowest_level_flag in NUMBER
1650 , X_DEPRECIATION_START_DATE in DATE
1651 , x_asset_id in number
1652 , X_ATTRIBUTE_CATEGORY in VARCHAR2
1653 , X_ATTRIBUTE1 in VARCHAR2
1654 , X_ATTRIBUTE2 in VARCHAR2
1655 , X_ATTRIBUTE3 in VARCHAR2
1656 , X_ATTRIBUTE4 in VARCHAR2
1657 , X_ATTRIBUTE5 in VARCHAR2
1658 , X_ATTRIBUTE6 in VARCHAR2
1659 , X_ATTRIBUTE7 in VARCHAR2
1660 , X_ATTRIBUTE8 in VARCHAR2
1661 , X_ATTRIBUTE9 in VARCHAR2
1662 , X_ATTRIBUTE10 in VARCHAR2
1663 , X_ATTRIBUTE11 in VARCHAR2
1664 , X_ATTRIBUTE12 in VARCHAR2
1665 , X_ATTRIBUTE13 in VARCHAR2
1666 , X_ATTRIBUTE14 in VARCHAR2
1667 , X_ATTRIBUTE15 in VARCHAR2
1668 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)is
1669 begin
1670 update FA_ASSET_HIERARCHY set
1671 name = x_name,
1672 --level_number = x_level_number, --Level number is Non Updateable
1673 hierarchy_rule_set_id = x_hierarchy_rule_set_id,
1674 description = x_description,
1675 parent_hierarchy_id = x_parent_hierarchy_id,
1676 lowest_level_flag = x_lowest_level_flag,
1677 depreciation_start_date = x_depreciation_start_date,
1678 asset_id = x_asset_id,
1679 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
1680 ATTRIBUTE1 = X_ATTRIBUTE1,
1681 ATTRIBUTE2 = X_ATTRIBUTE2,
1682 ATTRIBUTE3 = X_ATTRIBUTE3,
1683 ATTRIBUTE4 = X_ATTRIBUTE4,
1684 ATTRIBUTE5 = X_ATTRIBUTE5,
1685 ATTRIBUTE6 = X_ATTRIBUTE6,
1686 ATTRIBUTE7 = X_ATTRIBUTE7,
1687 ATTRIBUTE8 = X_ATTRIBUTE8,
1688 ATTRIBUTE9 = X_ATTRIBUTE9,
1689 ATTRIBUTE10 = X_ATTRIBUTE10,
1690 ATTRIBUTE11 = X_ATTRIBUTE11,
1691 ATTRIBUTE12 = X_ATTRIBUTE12,
1692 ATTRIBUTE13 = X_ATTRIBUTE13,
1693 ATTRIBUTE14 = X_ATTRIBUTE14,
1694 ATTRIBUTE15 = X_ATTRIBUTE15,
1695 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1696 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1697 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1698 where asset_hierarchy_id= X_asset_hierarchy_id
1699 and nvl(asset_hierarchy_purpose_id,1) = nvl(x_asset_hierarchy_purpose_id,1);
1700 if (sql%notfound) then
1701 raise no_data_found;
1702 end if;
1703 end UPDATE_ROW;
1704
1705 procedure DELETE_ROW (
1706 x_asset_hierarchy_purpose_id in number
1707 , X_asset_hierarchy_id in NUMBER
1708 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
1709 begin
1710 delete from FA_ASSET_HIERARCHY
1711 where asset_hierarchy_id = X_asset_hierarchy_id
1712 and nvl(asset_hierarchy_purpose_id,1) = nvl(x_asset_hierarchy_purpose_id,1);
1713 if (sql%notfound) then
1714 raise no_data_found;
1715 end if;
1716 end DELETE_ROW;
1717
1718 function is_non_asset_node(x_asset_hierarchy_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1719 return boolean
1720 is
1721 v_asset_id number;
1722 cursor C is
1723 select nvl(asset_id,0) from FA_ASSET_HIERARCHY
1724 where asset_hierarchy_id = x_asset_hierarchy_id;
1725 Begin
1726 open C;
1727 fetch C into v_asset_id;
1728 close C;
1729 if (v_asset_id = 0) then
1730 return (TRUE);
1731 else
1732 return (FALSE);
1733 end if;
1734 end is_non_asset_node;
1735
1736 --Function to check the particular attribute is mandatory
1737 Function is_attribute_mandatory(x_hierarchy_purpose_id in number
1738 ,x_level_number in number
1739 ,x_attribute_name in varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1740 return Boolean
1741 is
1742 Cursor C is select 1
1743 from fa_hierarchy_controls
1744 where asset_hierarchy_purpose_id = x_hierarchy_purpose_id
1745 and level_number = x_level_number
1746 and nvl(decode(x_attribute_name,'CATEGORY',category_mandatory_flag
1747 ,'LEASE',lease_mandatory_flag
1748 ,'ASSET_KEY',asset_key_mandatory_flag
1749 ,'SERIAL_NUMBER',serial_number_mandatory_flag
1750 ,'DISTRIBUTION',distribution_mandatory_flag
1751 ,'LED',life_end_date_mandatory_flag
1752 ,'DPIS',dpis_mandatory_flag
1753 ),'N') = 'Y';
1754 dummy number;
1755 Begin
1756 open C;
1757 fetch C into dummy;
1758 if C%NOTFOUND then
1759 close C;
1760 return FALSE;
1761 end if;
1762 close C;
1763 return TRUE;
1764 End is_attribute_mandatory;
1765
1766 /* Check for the lowest level non asset node with no Non asset nodes attached */
1767 Function check_lowest_level_node(x_asset_hierarchy_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1768 return Boolean
1769 is
1770 dummy number;
1771 Begin
1772 select 1 into dummy from fa_asset_hierarchy
1773 where asset_hierarchy_id = x_asset_hierarchy_id
1774 and nvl(asset_id,0) = 0
1775 and not exists (Select 1
1776 from fa_asset_hierarchy a
1777 where nvl(a.asset_id,0) = 0
1778 and a.parent_hierarchy_id = x_asset_hierarchy_id);
1779 return(TRUE);
1780 exception
1781 when no_data_found then
1782 return(FALSE);
1783 end check_lowest_level_node;
1784
1785 Function check_asset_node(x_asset_hierarchy_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1786 return Boolean
1787 is
1788 cursor C is select nvl(asset_id,0)
1789 from fa_asset_hierarchy
1790 where asset_hierarchy_id = x_asset_hierarchy_id;
1791 v_asset_id number;
1792 dummy number;
1793 Begin
1794 open C;
1795 fetch C into v_asset_id;
1796 close C;
1797 if (v_asset_id <> 0) then
1798 return(TRUE);
1799 else
1800 return (FALSE);
1801 end if;
1802 end check_asset_node;
1803
1804 Function Check_asset_tied_node(x_asset_hierarchy_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1805 return BOOLEAN
1806 is
1807 dummy number;
1808 Begin
1809 select 1 into dummy
1810 from dual
1811 where exists (Select 1 from fa_asset_hierarchy
1812 where parent_hierarchy_id = x_asset_hierarchy_id
1813 and nvl(asset_id,0) <> 0);
1814 return (TRUE);
1815 Exception
1816 when no_data_found then
1817 return (FALSE);
1818 End check_asset_tied_node;
1819
1820 Function is_child_exists(x_asset_hierarchy_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1821 return BOOLEAN
1822 is
1823 dummy number;
1824 begin
1825 select 1 into dummy from dual
1826 where exists(select 1 from fa_asset_hierarchy
1827 where parent_hierarchy_id = x_asset_hierarchy_id);
1828 return(TRUE);
1829 Exception
1830 when no_data_found then
1831 return(FALSE);
1832 end is_child_exists;
1833
1834 /* Function to check assets are attached to the tree branch */
1835 Function is_assets_attached_node(x_node_id in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean
1836 is
1837 dummy number;
1838 Begin
1839 select 1 into dummy from dual
1840 where exists ( select asset_hierarchy_id
1841 from fa_asset_hierarchy
1842 where nvl(asset_id,0) <> 0
1843 start with asset_hierarchy_id = x_node_id
1844 connect by prior asset_hierarchy_id = parent_hierarchy_id);
1845 return(TRUE);
1846 Exception
1847 when no_data_found then
1848 return(FALSE);
1849 End is_assets_attached_node;
1850
1851 Function is_valid_line_percent(x_line_percent in number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean
1852 is
1853 Begin
1854 if(nvl(x_line_percent,0) >=0 and nvl(x_line_percent,0) <=100) then
1855 return TRUE;
1856 else
1857 return FALSE;
1858 end if;
1859 End is_valid_line_percent;
1860
1861 Procedure wrapper_validate_node (p_log_level_rec IN
1862 fa_api_types.log_level_rec_type ) is
1863 begin
1864 validate_node(x_asset_hierarchy_purpose_id => FA_CUA_HIERARCHY_PKG.g_asset_hierarchy_purpose_id,
1865 x_book_type_code => FA_CUA_HIERARCHY_PKG.g_book_type_code,
1866 x_name => FA_CUA_HIERARCHY_PKG.g_name,
1867 x_level_number => 0,
1868 x_parent_hierarchy_id => FA_CUA_HIERARCHY_PKG.g_parent_hierarchy_id,
1869 x_err_code => FA_CUA_HIERARCHY_PKG.g_err_code,
1870 x_err_stage => FA_CUA_HIERARCHY_PKG.g_err_stage,
1871 x_err_stack => FA_CUA_HIERARCHY_PKG.g_err_stack, p_log_level_rec => p_log_level_rec);
1872 end wrapper_validate_node;
1873
1874
1875 end FA_CUA_HIERARCHY_PKG ;
[Home] [Help]