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