[Home] [Help]
PACKAGE BODY: APPS.FA_RECLASS_UTIL_PVT
Source
1 PACKAGE BODY FA_RECLASS_UTIL_PVT AS
2 /* $Header: FAVRCUTB.pls 120.15 2005/07/25 10:04:10 yyoon noship $ */
3
4 ------------------------------------------------------------------
5 FUNCTION validate_CIP_accounts(
6 p_transaction_type_code IN VARCHAR2,
7 p_book_type_code IN VARCHAR2,
8 p_asset_type IN VARCHAR2,
9 p_category_id IN VARCHAR2,
10 p_calling_fn IN VARCHAR2,
11 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
12
13 v_count number;
14 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.validate_cip_accts';
15
16 BEGIN
17 if p_asset_type = 'CIP' then
18 select count(1)
19 into v_count
20 from fa_category_books
21 where category_id = p_category_id
22 and book_type_code = p_book_type_code
23 and cip_cost_acct is not null
24 and cip_clearing_acct is not null
25 and rownum < 2;
26
27 if v_count = 0 then
28 fa_srvr_msg.add_message(
29 calling_fn => l_calling_fn,
30 name => 'FA_SHARED_NO_CIP_ACCOUNTS',
31 p_log_level_rec => p_log_level_rec);
32 return FALSE;
33 end if;
34 end if;
35
36 return TRUE;
37
38 EXCEPTION
39 WHEN OTHERS THEN
40 FA_SRVR_MSG.ADD_SQL_ERROR(
41 CALLING_FN => l_calling_fn
42 ,p_log_level_rec => p_log_level_rec);
43 RETURN (FALSE);
44 END validate_CIP_accounts;
45
46 -- -------------------------------------------------------
47 -- check to see if category is setup for the book
48 --
49 -- ------------------------------------------------------
50 FUNCTION check_cat_book_setup(
51 p_transaction_type_code IN VARCHAR2,
52 p_new_category_id IN NUMBER,
53 p_asset_id IN NUMBER,
54 p_calling_fn IN VARCHAR2,
55 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
56
57 l_count1 number;
58 l_count2 number;
59 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.check_cat_book_setup';
60 BEGIN
61
62 -- Make sure the new category is defined in all the books the asset belongs to.
63 -- Get the number of books in which the new category is defined for the asset.
64 SELECT count(*) INTO l_count1
65 FROM FA_CATEGORY_BOOKS cb, FA_BOOKS bk, fa_book_controls bc
66 WHERE bk.asset_id = p_asset_id
67 AND bk.date_ineffective IS NULL
68 AND bk.book_type_code = cb.book_type_code
69 AND cb.category_id = p_new_category_id
70 AND bc.book_type_code = bk.book_type_code
71 AND nvl(bc.date_ineffective,sysdate) >= sysdate;
72
73 -- Get the total number of books the asset belongs to.
74 SELECT count(*) INTO l_count2
75 FROM FA_BOOKS bk, FA_BOOK_CONTROLS bc
76 WHERE bk.asset_id = p_asset_id
77 AND bk.date_ineffective IS NULL
78 AND bk.book_type_code = bc.book_type_code
79 AND nvl(bc.date_ineffective,sysdate) >= sysdate;
80
81
82 IF (l_count1 <> l_count2) THEN
83 FA_SRVR_MSG.Add_Message(
84 CALLING_FN => l_calling_fn,
85 NAME => 'FA_REC_CAT_BOOK_NOT_SETUP',
86 p_log_level_rec => p_log_level_rec);
87 /* Message text:
88 'The new category is not defined in at least one of
89 the depreciation books the asset belongs to.' */
90 RETURN (FALSE);
91 END IF;
92
93 return TRUE;
94 EXCEPTION
95 WHEN OTHERS THEN
96 FA_SRVR_MSG.ADD_SQL_ERROR(
97 CALLING_FN => l_calling_fn
98 ,p_log_level_rec => p_log_level_rec);
99 RETURN (FALSE);
100 END check_cat_book_setup;
101
102 -------------------------------------------------------
103 FUNCTION validate_cat_types(
104 p_transaction_type_code IN VARCHAR2,
105 p_old_cat_id IN NUMBER,
106 p_new_cat_id IN NUMBER,
107 p_lease_id IN NUMBER,
108 p_asset_id IN NUMBER,
109 p_calling_fn IN VARCHAR2,
110 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
111 v_new_cap_flag varchar2(3);
112 v_new_cat_type varchar2(30);
113
114 v_old_cap_flag varchar2(3);
115 v_old_cat_type varchar2(30);
116
117 v_count number;
118 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.validate_cat_types';
119 BEGIN
120 -- both categories must be capitalized or expensed types
121 --old values
122 select capitalize_flag, category_type
123 into v_old_cap_flag, v_old_cat_type
124 from fa_categories
125 where category_id = p_old_cat_id;
126
127 -- new values
128 select capitalize_flag, category_type
129 into v_new_cap_flag, v_new_cat_type
130 from fa_categories
131 where category_id = p_new_cat_id;
132 --
133 if v_old_cap_flag = 'YES' then
134 if v_new_cap_flag = 'NO' then
135 fa_srvr_msg.add_message(
136 calling_fn => l_calling_fn,
137 name => 'FA_ADD_RECLS_TO_EXPENSE',
138 p_log_level_rec => p_log_level_rec);
139 return FALSE;
140 end if;
141 elsif v_old_cap_flag = 'NO' then
142 if v_new_cap_flag = 'YES' then
143 fa_srvr_msg.add_message(
144 calling_fn => l_calling_fn,
145 name => 'FA_ADD_RECLS_TO_CAP_ASSET',
146 p_log_level_rec => p_log_level_rec);
147 return FALSE;
148 end if;
149 end if;
150
151 -- validate lease
152 if v_old_cat_type = 'LEASE' and v_new_cat_type <> 'LEASE' then
153 if p_lease_id is not null then -- fix for bug 3507682
154 v_count := 0;
155
156 select 1 into v_count
157 from dual
158 where exists ( select 'x'
159 from fa_additions a,
160 fa_categories b
161 where a.asset_id = p_asset_id
162 and a.lease_id = p_Lease_Id
163 and a.asset_category_id = b.category_id
164 and b.category_type = 'LEASEHOLD IMPROVEMENT');
165
166 --
167 if v_count = 1 then
168 fa_srvr_msg.add_message(
169 calling_fn => l_calling_fn,
170 name => 'FA_ADD_DELETE_LHOLD_BEFORE_RCL',
171 p_log_level_rec => p_log_level_rec);
172 return FALSE;
173 end if;
174
175 --
176 v_count:= 0;
177 select 1 into v_count
178 from dual
179 where exists ( select 'x'
180 from fa_leases
181 where lease_id = p_lease_id );
182 --
183 if v_count = 1 then
184 fa_srvr_msg.add_message(
185 calling_fn => l_calling_fn,
186 name => 'FA_ADD_DELETE_LEASE_BEFORE_RCL',
187 p_log_level_rec => p_log_level_rec);
188 return FALSE;
189 end if;
190 end if;
191 end if; -- fix for bug 3507682
192 return TRUE;
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 FA_SRVR_MSG.ADD_SQL_ERROR(
197 CALLING_FN => l_calling_fn
198 ,p_log_level_rec => p_log_level_rec);
199 RETURN FALSE;
200 END validate_cat_types;
201
202 ------------------------------------------------------
203
204 FUNCTION validate_units(
205 p_transaction_type_code IN VARCHAR2,
206 p_asset_id IN NUMBER,
207 p_calling_fn IN VARCHAR2,
208 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
209 v_ah_units number;
210 v_dh_units number;
211
212 CURSOR get_ah_units IS
213 SELECT units FROM FA_ASSET_HISTORY
214 WHERE asset_id = p_asset_id
215 AND date_ineffective IS NULL;
216
217 CURSOR get_dh_units IS
218 SELECT sum(units_assigned) FROM FA_DISTRIBUTION_HISTORY
219 WHERE asset_id = p_asset_id
220 AND date_ineffective is NULL;
221 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.validate_units';
222 BEGIN
223
224 -- Units in asset history and
225 -- distribution history must match.
226 OPEN get_ah_units;
227 FETCH get_ah_units INTO v_ah_units;
228 CLOSE get_ah_units;
229
230 OPEN get_dh_units;
231 FETCH get_dh_units INTO v_dh_units;
232 CLOSE get_dh_units;
233
234 IF (v_ah_units <> v_dh_units) THEN
235 FA_SRVR_MSG.Add_Message(
236 CALLING_FN => l_calling_fn,
237 NAME => 'FA_SHARED_UNITS_UNBAL',
238 p_log_level_rec => p_log_level_rec);
239 RETURN FALSE;
240 END IF;
241
242 return TRUE;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 FA_SRVR_MSG.ADD_SQL_ERROR(
247 CALLING_FN => l_calling_fn
248 ,p_log_level_rec => p_log_level_rec);
249 RETURN FALSE;
250
251 END validate_units;
252
253 ------------------------------------------------------
254 FUNCTION validate_pending_retire(
255 p_transaction_type_code IN VARCHAR2,
256 p_asset_id IN NUMBER,
257 p_calling_fn IN VARCHAR2,
258 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
259
260 v_count number:= 0;
261 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.validate_pending_ret';
262 BEGIN
263
264 -- no pending retirements
265 select count(1)
266 into v_count
267 from fa_retirements
268 where asset_id = p_Asset_Id
269 and status in ('PENDING', 'REINSTATE', 'PARTIAL')
270 and rownum < 2;
271 --
272 if v_count > 0 then
273 fa_srvr_msg.add_message(
274 calling_fn => l_calling_fn,
275 name => 'FA_RET_PENDING_RETIREMENTS',
276 p_log_level_rec => p_log_level_rec);
277 return FALSE;
278 end if;
279
280 return TRUE;
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 FA_SRVR_MSG.ADD_SQL_ERROR(
285 CALLING_FN => l_calling_fn
286 ,p_log_level_rec => p_log_level_rec);
287 RETURN FALSE;
288
289 END validate_pending_retire;
290
291 -----------------------------------------------------
292 FUNCTION validate_fully_retired(
293 p_transaction_type_code IN VARCHAR2,
294 p_asset_id IN NUMBER,
295 p_calling_fn IN VARCHAR2,
296 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
297
298 v_count NUMBER := 0;
299 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.validate_fully_ret';
300
301 BEGIN
302 SELECT count(1)
303 INTO v_count
304 FROM FA_BOOKS BK,
305 FA_BOOK_CONTROLS BC
306 WHERE BK.ASSET_ID = p_asset_id AND
307 BK.PERIOD_COUNTER_FULLY_RETIRED IS NOT NULL AND
308 BK.DATE_INEFFECTIVE IS NULL AND
309 BK.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE AND
310 BC.DATE_INEFFECTIVE IS NULL AND
311 rownum < 2;
312
313 if v_count = 1 then
314 FA_SRVR_MSG.add_message(
315 CALLING_FN => l_calling_fn,
316 NAME => 'FA_REC_RETIRED',
317 p_log_level_rec => p_log_level_rec);
318 return FALSE;
319 end if;
320
321 return TRUE;
322 EXCEPTION
323 WHEN OTHERS THEN
324 FA_SRVR_MSG.ADD_SQL_ERROR(
325 CALLING_FN => l_calling_fn
326 ,p_log_level_rec => p_log_level_rec);
327 RETURN FALSE;
328
329 END validate_fully_retired;
330
331 --------------------------------------------------------------
332 FUNCTION validate_prior_per_add (
333 p_transaction_type_code IN VARCHAR2,
334 p_asset_id IN NUMBER,
335 p_book IN VARCHAR2,
336 p_calling_fn IN VARCHAR2,
337 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
338
339
340 v_is_prior_period NUMBER :=0;
341 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.val_prior_per_add';
342
343 BEGIN
344
345 /**** donot know whether I need this
346
347 h_mesg_name := 'FA_REC_SQL_PRIOR_PER';
348
349 select count(1)
350 into v_is_prior_period
351 FA_DEPRN_PERIODS DP_NOW,
352 FA_DEPRN_PERIODS DP,
353 FA_BOOK_CONTROLS BC,
354 FA_TRANSACTION_HEADERS TH
355 WHERE
356 TH.ASSET_ID = p_asset_id AND
357 TH.TRANSACTION_TYPE_CODE = DECODE(BC.BOOK_CLASS,'CORPORATE',
358 'TRANSFER IN','ADDITION') AND
359 TH.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE AND
360 BC.BOOK_TYPE_CODE = nvl( p_book,BC.BOOK_TYPE_CODE ) AND
361 TH.DATE_EFFECTIVE BETWEEN
362 DP.PERIOD_OPEN_DATE AND
363 NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
364 AND
365 DP.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE AND
366 DP.PERIOD_COUNTER < DP_NOW.PERIOD_COUNTER AND
367 DP_NOW.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE AND
368 DP_NOW.PERIOD_CLOSE_DATE IS NULL );
369
370
371 if v_is_prior_period = 1 then
372 X_is_prior_period := TRUE ;
373 else
374 X_is_prior_period := FALSE;
375 end if;
376
377 RETURN (TRUE);
378
379 EXCEPTION
380 WHEN NO_DATA_FOUND THEN
381 X_is_prior_period := FALSE;
382 RETURN (TRUE);
383
384 WHEN OTHERS THEN
385 FA_SRVR_MSG.ADD_SQL_ERROR (
386 CALLING_FN => l_calling_fn
387 ,p_log_level_rec => p_log_level_rec);
388
389 RETURN (FALSE);
390 **/
391 return TRUE;
392 END validate_prior_per_add;
393
394 ---------------------------------------------------------------
395 FUNCTION validate_transaction_date(
396 p_trans_rec IN FA_API_TYPES.trans_rec_type,
397 p_asset_id IN NUMBER,
398 p_book IN VARCHAR2,
399 p_calling_fn IN VARCHAR2,
400 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
401
402 l_prior_trans_date DATE;
403 l_prior_date_effective DATE;
404 l_check_another_trans NUMBER := 0;
405
406 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.validate_trx_date';
407
408 CURSOR get_prior_trans_date IS
409 SELECT max(transaction_date_entered)
410 FROM FA_TRANSACTION_HEADERS
411 WHERE asset_id = p_asset_id
412 AND book_type_code = p_book
413 AND transaction_type_code not like '%/VOID';
414
415 CURSOR get_prior_date_effective IS
416 SELECT max(date_effective)
417 FROM FA_TRANSACTION_HEADERS
418 WHERE asset_id = p_asset_id
419 AND book_type_code = p_book;
420 BEGIN
421 /* Logic from FA_BOOKS_VAL5.Amortization_Start_Date from FAXASSET. */
422 -- Check another transaction between transaction date and current period.
423 OPEN get_prior_trans_date;
424 FETCH get_prior_trans_date INTO l_prior_trans_date;
425 CLOSE get_prior_trans_date;
426
427 IF (p_trans_rec.transaction_date_entered < l_prior_trans_date) THEN
428 FA_SRVR_MSG.Add_Message(
429 CALLING_FN => l_calling_fn,
430 NAME => 'FA_SHARED_OTHER_TRX_FOLLOW',
431 p_log_level_rec => p_log_level_rec);
432 RETURN (FALSE);
433 END IF;
434
435 OPEN get_prior_date_effective;
436 FETCH get_prior_date_effective INTO l_prior_date_effective;
437 CLOSE get_prior_date_effective;
438
439 SELECT count(1) INTO l_check_another_trans
440 FROM FA_DEPRN_PERIODS pdp, FA_DEPRN_PERIODS adp
441 WHERE pdp.book_type_code = p_book
442 AND pdp.book_type_code = adp.book_type_code
443 AND pdp.period_counter > adp.period_counter
444 AND l_prior_date_effective between pdp.period_open_date
445 and nvl(pdp.period_close_date, to_date('31-12-4712','DD-MM-YYYY'))
446 AND p_trans_rec.transaction_date_entered between
447 adp.calendar_period_open_date and adp.calendar_period_close_date;
448
449 IF (l_check_another_trans > 0) THEN
450 FA_SRVR_MSG.Add_Message(
451 CALLING_FN => l_calling_fn,
452 NAME => 'FA_SHARED_OTHER_TRX_FOLLOW',
453 p_log_level_rec => p_log_level_rec);
454 RETURN (FALSE);
455 END IF;
456
457 RETURN (TRUE);
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 FA_SRVR_MSG.Add_SQL_Error(
462 CALLING_FN => l_calling_fn
463 ,p_log_level_rec => p_log_level_rec);
464 RETURN (FALSE);
465 END validate_transaction_date;
466
467
468 ---------------------------------------------------------
469 FUNCTION Validate_Adjustment(
470 p_transaction_type_code IN VARCHAR2,
471 p_asset_id IN NUMBER,
472 p_book_type_code IN VARCHAR2,
473 p_amortize_flag IN VARCHAR2,
474 p_mr_req_id IN NUMBER := -1,
475 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
476
477 check_flag VARCHAR2(3);
478 prior_transaction_date DATE;
479 prior_date_effective DATE;
480 check_another_trans NUMBER;
481 l_period_of_addition varchar2(1);
482
483 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.validate_adjustment';
484
485 CURSOR check_mass_change_allowed IS
486 SELECT 'x' FROM FA_BOOK_CONTROLS
487 WHERE book_type_code = p_book_type_code AND allow_mass_changes = 'NO'
488 AND p_mr_req_id <> -1;
489 CURSOR check_prior_amort IS
490 SELECT 'x' FROM FA_BOOKS
491 WHERE book_type_code = p_book_type_code AND asset_id = p_asset_id
492 AND rate_adjustment_factor <> 1;
493 CURSOR check_cip IS
494 SELECT 'x' FROM FA_ADDITIONS
495 WHERE asset_id = p_asset_id AND asset_type = 'CIP';
496 CURSOR check_amort_allowed IS
497 SELECT 'x' FROM FA_BOOK_CONTROLS
498 WHERE book_type_code = p_book_type_code AND amortize_flag = 'NO';
499 CURSOR check_deprn IS
500 SELECT 'x' FROM FA_DEPRN_SUMMARY
501 WHERE book_type_code = p_book_type_code
502 AND asset_id = p_asset_id
503 AND deprn_source_code = 'DEPRN';
504 BEGIN
505 -- Check if mass change(adjustment) is allowed.
506 OPEN check_mass_change_allowed;
507 FETCH check_mass_change_allowed INTO check_flag;
508 IF (check_mass_change_allowed%found) THEN
509 CLOSE check_mass_change_allowed;
510 FA_SRVR_MSG.Add_Message( CALLING_FN => l_calling_fn,
511 NAME => 'FA_REC_MASSCHG_NOT_ALLOWED',
512 p_log_level_rec => p_log_level_rec);
513 /* Message text:
514 'You cannot use mass transaction to redefault depreciation rules
515 for the asset in this book because Allow Mass Changes field in the
516 Book Controls form is set to No. */
517 RETURN (FALSE);
518 END IF;
519 CLOSE check_mass_change_allowed;
520
521
522 ---- Checks for Expensed Adjustment ----
523
524 -- Check if there were prior amortized adjustments, in case of expensed
525 -- adjustment.
526 IF (p_amortize_flag = 'NO') THEN
527 OPEN check_prior_amort;
528 FETCH check_prior_amort INTO check_flag;
529 IF (check_prior_amort%found) THEN
530 CLOSE check_prior_amort;
531 FA_SRVR_MSG.Add_Message( CALLING_FN => l_calling_fn,
532 NAME => 'FA_BOOK_CANT_EXP_AFTER_AMORT',
533 p_log_level_rec => p_log_level_rec);
534 RETURN (FALSE);
535 END IF;
536 CLOSE check_prior_amort;
537
538 ---- Checks for Amortized Adjustment ----
539
540 ELSE
541 -- Check if an asset is a CIP asset. CIP assets cannot be amortized.
542 -- Rule from fa_fin_adj3_pkg.transaction_type().
543 OPEN check_cip;
544 FETCH check_cip INTO check_flag;
545 IF (check_cip%found) THEN
546 CLOSE check_cip;
547 FA_SRVR_MSG.Add_Message( CALLING_FN => l_calling_fn,
548 NAME => 'FA_REC_CIP_CANNOT_AMORT',
549 p_log_level_rec => p_log_level_rec);
550 -- Message text:
551 -- 'You cannot amortize an adjustment for a CIP asset.'
552 RETURN (FALSE);
553 END IF;
554 CLOSE check_cip;
555
556 -- Check if amortization is allowed in this book.
557 OPEN check_amort_allowed;
558 FETCH check_amort_allowed INTO check_flag;
559 IF (check_amort_allowed%found) THEN
560 CLOSE check_amort_allowed;
561 FA_SRVR_MSG.Add_Message( CALLING_FN => l_calling_fn,
562 NAME => 'FA_BOOK_AMORTIZED_NOT_ALLOW',
563 p_log_level_rec => p_log_level_rec);
564 RETURN (FALSE);
565 END IF;
566 CLOSE check_amort_allowed;
567
568 /*
569
570 -- Check if the asset has already been depreciated.
571 OPEN check_deprn;
572 FETCH check_deprn INTO check_flag;
573 IF (check_deprn%notfound) THEN
574 CLOSE check_deprn;
575
576 -- if in period of addition, check if previously amortized
577 OPEN check_prior_amort;
578 FETCH check_prior_amort INTO check_flag;
579 IF (check_prior_amort%notfound) THEN
580 CLOSE check_prior_amort;
581 FA_SRVR_MSG.Add_Message( CALLING_FN => l_calling_fn,
582 NAME => 'FA_BOOK_CANT_AMORT_BEF_DEPRN',
583 p_log_level_rec => p_log_level_rec);
584 RETURN (FALSE);
585 ELSE
586 CLOSE check_prior_amort;
587 END IF;
588 ELSE
589 CLOSE check_deprn;
590 END IF;
591 */
592
593 -- END IF; /* commented for bug 3844678 */
594
595 -- BUG# 3354951
596 -- replacing the above with call to common validation routine
597 if not fa_asset_val_pvt.validate_period_of_addition
598 (p_asset_id => p_asset_id,
599 p_book => p_book_type_code,
600 p_mode => 'ABSOLUTE',
601 px_period_of_addition => l_period_of_addition,
602 p_log_level_rec => p_log_level_rec) then
603
604 FA_SRVR_MSG.Add_Message( CALLING_FN => l_calling_fn,
605 p_log_level_rec => p_log_level_rec);
606 return FALSE;
607 elsif (l_period_of_addition = 'Y') then
608 FA_SRVR_MSG.Add_Message(CALLING_FN => l_calling_fn,
609 NAME => 'FA_BOOK_CANT_AMORT_BEF_DEPRN',
610 p_log_level_rec => p_log_level_rec);
611 return false;
612 end if;
613
614 END IF; /* added for bug 3844678 */
615
616 RETURN (TRUE);
617 EXCEPTION
618 WHEN OTHERS THEN
619 FA_SRVR_MSG.Add_SQL_Error( CALLING_FN => l_calling_fn
620 ,p_log_level_rec => p_log_level_rec);
621 RETURN (FALSE);
622 END Validate_Adjustment;
623
624
625 -------------------------------------------------------------
626 FUNCTION get_new_ccid(
627 p_trans_rec IN FA_API_TYPES.trans_rec_type,
628 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
629 p_asset_cat_rec_new IN FA_API_TYPES.asset_cat_rec_type,
630 p_dist_rec_old IN FA_API_TYPES.asset_dist_rec_type,
631 px_dist_rec_new IN OUT NOCOPY FA_API_TYPES.asset_dist_rec_type,
632 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
633
634 h_mesg_name VARCHAR2(30);
635 h_new_deprn_exp_acct VARCHAR2(26);
636 -- h_new_ccid NUMBER(15) := 0;
637 fardodh_done EXCEPTION;
638 fardodh_error EXCEPTION;
639 h_cost_acct_ccid NUMBER :=0;
640
641 h_chart_of_accounts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
642 h_flex_segment_delimiter varchar2(5);
643 h_flex_segment_number number;
644 h_num_of_segments NUMBER;
645 h_concat_array_segments FND_FLEX_EXT.SEGMENTARRAY;
646
647 h_appl_short_name varchar2(30);
648 h_message_name varchar2(30);
649 h_num number := 0;
650 h_errmsg varchar2(512);
651 h_concat_segs varchar2(2000) := '';
652 h_delimiter varchar2(1);
653
654 l_err_stage varchar2(250);
655 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.get_new_ccid';
656
657 BEGIN
658
659 l_err_stage := 'Get the new category DEPRN_EXPENSE_ACCT';
660 -- dbms_output.put_line(l_err_stage);
661 SELECT deprn_expense_acct,asset_cost_account_ccid
662 INTO h_new_deprn_exp_acct,h_cost_acct_ccid
663 FROM fa_category_books
664 WHERE book_type_code = p_asset_hdr_rec.book_type_code
665 AND category_id = p_asset_cat_rec_new.category_id;
666
667
668 l_err_stage:= 'Get Chart of Accounts ID';
669 -- dbms_output.put_line(l_err_stage);
670 Select sob.chart_of_accounts_id
671 into h_chart_of_accounts_id
672 From fa_book_controls bc,
673 gl_sets_of_books sob
674 Where sob.set_of_books_id = bc.set_of_books_id
675 And bc.book_type_code = p_asset_hdr_rec.book_type_code;
676
677 -- dbms_output.put_line('h_chart_of_accounts_id '||to_char(h_chart_of_accounts_id));
678
679 l_err_stage:= 'Get Account Qualifier Segment Number';
680 -- dbms_output.put_line(l_err_stage);
681 -- h_message_name := 'FND_FLEX_EXT.GET_QUALIFIER_SEGNUM';
682 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
683 101,
684 'GL#',
685 h_chart_of_accounts_id,
686 'GL_ACCOUNT',
687 h_flex_segment_number)) THEN
688 RAISE fardodh_error;
689 END IF;
690
691 l_err_stage:= 'Retrieve distribution segments';
692 -- dbms_output.put_line(l_err_stage);
693 -- h_message_name := 'FND_FLEX_EXT.GET_SEGMENTS';
694 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL',
695 'GL#',
696 h_chart_of_accounts_id,
697 p_dist_rec_old.expense_ccid, --h_old_ccid,
698 h_num_of_segments,
699 h_concat_array_segments)) THEN
700
701 RAISE fardodh_error;
702 END IF;
703 -- -- dbms_output.put_line('old_expense_ccid '||to_char(p_dist_rec_old.expense_ccid));
704 -- -- dbms_output.put_line('h_new_deprn_exp_acct'||h_new_deprn_exp_acct);
705 -- -- dbms_output.put_line('h_flex_number'||to_char(h_flex_segment_number));
706
707 -- Updating array with new account value
708 h_concat_array_segments(h_flex_segment_number) := h_new_deprn_exp_acct;
709
710 l_err_stage:= 'Retrieve new ccid with overlaid account';
711 -- get_combination_id function generates new ccid if rules allows.
712 -- h_message_name := 'FND_FLEX_EXT.GET_COMBINATION_ID';
713 -- dbms_output.put_line(l_err_stage);
714 IF (NOT FND_FLEX_EXT.GET_COMBINATION_ID(
715 'SQLGL',
716 'GL#',
717 h_chart_of_accounts_id,
718 SYSDATE,
719 h_num_of_segments,
720 h_concat_array_segments,
721 px_dist_rec_new.expense_ccid )) THEN
722 -- dbms_output.put_line('error ----- FND_FLEX_EXT.GET_COMBINATION_ID');
723
724 -- -- dbms_output.put_line('FND_FLEX_APIS.get_segment_delimiter');
725 -- build message
726 h_delimiter := FND_FLEX_APIS.get_segment_delimiter(
727 101,
728 'GL#',
729 h_chart_of_accounts_id);
730
731 -- fill the string for messaging with concat segs...
732 while (h_num < h_num_of_segments) loop
733 h_num := h_num + 1;
734
735 if (h_num > 1) then
736 h_concat_segs := h_concat_segs ||
737 h_delimiter;
738 end if;
739
740 h_concat_segs := h_concat_segs ||
741 h_concat_array_segments(h_num);
742
743 end loop;
744
745 h_errmsg:= null;
746 h_errmsg := FND_FLEX_EXT.GET_ENCODED_MESSAGE;
747
748 FA_SRVR_MSG.ADD_MESSAGE
749 (CALLING_FN=>'FAFLEX_PKG_WF.START_PROCESS',
750 NAME=>'FA_FLEXBUILDER_FAIL_CCID',
751 TOKEN1 => 'ACCOUNT_TYPE',
752 VALUE1 => 'DEPRN_EXP',
753 TOKEN2 => 'BOOK_TYPE_CODE',
754 VALUE2 => p_asset_hdr_rec.book_type_code,
755 TOKEN3 => 'DIST_ID',
756 VALUE3 => 'NEW',
757 TOKEN4 => 'CONCAT_SEGS',
758 VALUE4 => h_concat_segs,
759 p_log_level_rec => p_log_level_rec);
760
761 fnd_message.set_encoded(h_errmsg);
762 fnd_msg_pub.add;
763
764 RAISE fardodh_error;
765 END IF;
766
767
768 RETURN (TRUE);
769
770 EXCEPTION
771 WHEN fardodh_error THEN
772 FA_SRVR_MSG.add_message(
773 CALLING_FN => l_calling_fn,
774 NAME => h_mesg_name,
775 p_log_level_rec => p_log_level_rec);
776 RETURN (FALSE);
777 WHEN OTHERS THEN
778 FA_SRVR_MSG.ADD_SQL_ERROR (
779 CALLING_FN => l_calling_fn
780 ,p_log_level_rec => p_log_level_rec);
781
782 RETURN (FALSE);
783
784 END get_new_ccid;
785
786
787 --
788 FUNCTION get_asset_distribution(
789 p_trans_rec IN FA_API_TYPES.trans_rec_type,
790 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
791 p_asset_cat_rec_old IN FA_API_TYPES.asset_cat_rec_type,
792 p_asset_cat_rec_new IN FA_API_TYPES.asset_cat_rec_type,
793 px_asset_dist_tbl IN OUT NOCOPY FA_API_TYPES.asset_dist_tbl_type,
794 p_calling_fn IN VARCHAR2,
795 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
796
797 CURSOR dh_csr IS
798 Select dh.distribution_id,
799 dh.code_combination_id,
800 dh.units_assigned,
801 dh.location_id,
802 dh.assigned_to,
803 ad.asset_number
804 from fa_book_controls bc,
805 fa_distribution_history dh,
806 fa_additions ad
807 where dh.asset_id = p_asset_hdr_rec.asset_id
808 and dh.book_type_code = bc.distribution_source_book
809 and bc.book_type_code = p_asset_hdr_rec.book_type_code
810 and dh.book_type_code = p_asset_hdr_rec.book_type_code
811 and dh.date_ineffective is null
812 and dh.retirement_id is null
813 and dh.asset_id = ad.asset_id;
814
815 CURSOR cat_csr( p_book varchar2, p_cat_id number) IS
816 select deprn_expense_acct
817 from fa_category_books
818 where book_type_code = p_book
819 and category_id = p_cat_id;
820
821 -- fix for bug 3255715
822 CURSOR trx_date_csr( p_book varchar2) IS
823 SELECT greatest(dp.calendar_period_open_date,
824 least(sysdate, dp.calendar_period_close_date))
825 FROM fa_deprn_periods dp
826 WHERE dp.book_type_code = p_book
827 AND dp.period_close_date IS NULL;
828
829 i integer:= 0;
830 j integer:= 0;
831 l_rowcount number;
832 l_dist_rec_old FA_API_TYPES.asset_dist_rec_type;
833 l_dist_rec_new FA_API_TYPES.asset_dist_rec_type;
834
835 l_new_deprn_exp_acct fa_category_books.deprn_expense_acct%type;
836 l_old_deprn_exp_acct fa_category_books.deprn_expense_acct%type;
837 l_err_stage varchar2(250);
838 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.get_asset_dist';
839 l_trx_date_entered date; -- fix for bug 3255715
840
841 BEGIN
842
843 l_err_stage:= 'initialize the table';
844 -- -- dbms_output.put_line(l_err_stage);
845 px_asset_dist_tbl.delete;
846
847 -- check whether distribution change is needed
848 OPEN cat_csr(p_asset_hdr_rec.book_type_code, p_asset_cat_rec_new.category_id );
849 FETCH cat_csr INTO l_new_deprn_exp_acct;
850 CLOSE cat_csr;
851
852 OPEN cat_csr(p_asset_hdr_rec.book_type_code, p_asset_cat_rec_old.category_id );
853 FETCH cat_csr INTO l_old_deprn_exp_acct;
854 CLOSE cat_csr;
855
856 -- fix for bug 3255715
857 if(p_trans_rec.transaction_date_entered is null) then
858 OPEN trx_date_csr(p_asset_hdr_rec.book_type_code);
859 FETCH trx_date_csr into l_trx_date_entered;
860 CLOSE trx_date_csr;
861 else l_trx_date_entered := p_trans_rec.transaction_date_entered;
862 end if;
863
864 l_err_stage:= 'dh_csr';
865 -- -- dbms_output.put_line(l_err_stage);
866 FOR dh_rec in dh_csr LOOP
867 i:= i+1;
868
869 if not FA_ASSET_VAL_PVT.validate_assigned_to (
870 p_transaction_type_code => p_trans_rec.transaction_type_code,
871 p_assigned_to => dh_rec.assigned_to,
872 p_date => l_trx_date_entered, -- fix for bug 3255715
873 p_calling_fn => p_calling_fn,
874 p_log_level_rec => p_log_level_rec) then
875 fa_srvr_msg.add_message(
876 calling_fn => NULL,
877 name => 'FA_INVALID_ASSIGNED_TO',
878 token1 => 'ASSET_NUMBER',
879 value1 => dh_rec.asset_number,
880 token2 => 'ASSIGNED_TO',
881 value2 => dh_rec.assigned_to,
882 p_log_level_rec => p_log_level_rec);
883 dh_rec.assigned_to := NULL; -- set to null if invalid employee
884 end if;
885
886 l_err_stage:= 'pop asset_dist_tbl with old dist row to be obseleted';
887 -- dbms_output.put_line(l_err_stage);
888 px_asset_dist_tbl(i).distribution_id := dh_rec.distribution_id;
889 px_asset_dist_tbl(i).transaction_units := ( dh_rec.units_assigned * -1);
890 px_asset_dist_tbl(i).units_assigned := dh_rec.units_assigned;
891 px_asset_dist_tbl(i).assigned_to := dh_rec.assigned_to;
892 px_asset_dist_tbl(i).expense_ccid := dh_rec.code_combination_id;
893 px_asset_dist_tbl(i).location_ccid := dh_rec.location_id;
894
895 l_err_stage:= 'pop l_dist_rec_old';
896 -- dbms_output.put_line(l_err_stage);
897 l_dist_rec_old.expense_ccid := dh_rec.code_combination_id;
898 l_dist_rec_old.assigned_to := dh_rec.assigned_to;
899 l_dist_rec_old.location_ccid := dh_rec.location_id;
900
901
902 -- dbms_output.put_line('l_old_deprn_exp_acct '||to_char(l_old_deprn_exp_acct));
903 -- dbms_output.put_line('l_new_deprn_exp_acct '||to_char(l_new_deprn_exp_acct));
904 if l_old_deprn_exp_acct <> l_new_deprn_exp_acct then
905 -- pop new dist row to be created
906 -- get new expense_ccid
907 l_err_stage:= 'pop get_new_ccid';
908 -- dbms_output.put_line(l_err_stage);
909 if not get_new_ccid(
910 p_trans_rec => p_trans_rec,
911 p_asset_hdr_rec => p_asset_hdr_rec,
912 p_asset_cat_rec_new => p_asset_cat_rec_new,
913 p_dist_rec_old => l_dist_rec_old,
914 px_dist_rec_new => l_dist_rec_new,
915 p_log_level_rec => p_log_level_rec) then
916
917 FA_SRVR_MSG.add_message( calling_fn => l_calling_fn,
918 p_log_level_rec => p_log_level_rec);
919 return FALSE;
920 end if;
921 else
922 -- no need for change
923 -- same as old
924 l_dist_rec_new.expense_ccid := dh_rec.code_combination_id;
925 end if;
926
927 i:= i+1;
928 l_err_stage:= 'pop asset_dist_tbl with new dist row';
929 -- dbms_output.put_line(l_err_stage);
930 px_asset_dist_tbl(i).distribution_id := null;
931 px_asset_dist_tbl(i).transaction_units := dh_rec.units_assigned;
932 px_asset_dist_tbl(i).units_assigned := dh_rec.units_assigned;
933 px_asset_dist_tbl(i).assigned_to := dh_rec.assigned_to;
934 px_asset_dist_tbl(i).expense_ccid := l_dist_rec_new.expense_ccid;
935 px_asset_dist_tbl(i).location_ccid := dh_rec.location_id;
936
937
938 l_dist_rec_old := null;
939 l_dist_rec_new := null;
940 END LOOP;
941 -- dbms_output.put_line('no of dist populated ' ||to_char(i));
942 -- check whether this asset has any distribution
943 if i = 0 then
944 FA_SRVR_MSG.add_message(
945 CALLING_FN => l_calling_fn,
946 NAME => 'FA_REC_NO_DIST_LINES',
947 p_log_level_rec => p_log_level_rec);
948 return TRUE;
949 end if;
950
951
952 return TRUE;
953
954 EXCEPTION
955 WHEN OTHERS THEN
956 fa_srvr_msg.add_sql_error( calling_fn => l_calling_fn
957 ,p_log_level_rec => p_log_level_rec);
958 return FALSE;
959 END get_asset_distribution;
960
961 -- ------------------------------------------------------
962
963
964 -- ------------------------------------------------------
965
966 FUNCTION get_cat_desc_flex(
967 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
968 px_asset_desc_rec IN OUT NOCOPY FA_API_TYPES.asset_desc_rec_type,
969 p_asset_cat_rec_old IN FA_API_TYPES.asset_cat_rec_type,
970 px_asset_cat_rec_new IN OUT NOCOPY FA_API_TYPES.asset_cat_rec_type,
971 p_recl_opt_rec IN FA_API_TYPES.reclass_options_rec_type,
972 p_calling_fn IN VARCHAR2,
973 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
974
975 l_cat_struct number;
976 l_concat_cat varchar2(210);
977 l_cat_segs fa_rx_shared_pkg.Seg_Array;
978 i integer:= 0;
979 l_seg varchar2(30);
980 l_err_stage varchar2(100);
981
982 l_bal_seg_equal varchar2(1):= null;
983 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.get_cat_desc_flex';
984
985 --
986
987 delim varchar2(1);
988 col_name varchar2(25);
989
990 num_segs integer;
991 seg_ctr integer;
992
993 v_cursorid integer;
994 v_sqlstmt varchar2(500);
995 v_return integer;
996
997 h_mesg_name varchar2(30);
998 h_mesg_str varchar2(2000);
999
1000 --
1001 h_table_id number;
1002 h_table_name varchar2(30) := 'FA_CATEGORIES_B';
1003
1004 h_ccid_col_name varchar2(100) := 'CATEGORY_ID';
1005 h_flex_code varchar2(10) := 'CAT#';
1006 h_appl_id number := 140;
1007 h_appl_short_name varchar2(10) := 'OFA';
1008 concat_string varchar2(100);
1009 segarray fa_rx_shared_pkg.Seg_Array;
1010 cursor segcolumns is
1011 select distinct g.application_column_name, g.segment_num
1012 from fnd_columns c, fnd_id_flex_segments g
1013 WHERE g.application_id = h_appl_id
1014 AND g.id_flex_code = h_flex_code
1015 AND g.id_flex_num = l_cat_struct
1016 AND g.enabled_flag = 'Y'
1017 AND g.display_flag = 'Y'
1018 AND c.application_id = h_appl_id
1019 AND c.table_id = h_table_id
1020 AND c.column_name = g.application_column_name
1021 ORDER BY g.segment_num;
1022
1023 BEGIN
1024
1025 l_err_stage:= 'get concatenated segs for the new category';
1026 -- dbms_output.put_line(l_err_stage);
1027 select category_flex_structure
1028 into l_cat_struct
1029 from fa_system_controls;
1030
1031 l_err_stage:= 'fa_rx_shared_pkg.concat_category';
1032 -- dbms_output.put_line(l_err_stage);
1033
1034 -- bug 3225015
1035 -- Replacing call to fa_rx_shared_pkg, with code underneatch
1036 -- due to specific requirement, not to be shared.
1037 /* fa_rx_shared_pkg.concat_category (
1038 struct_id => l_cat_struct,
1039 ccid => px_asset_cat_rec_new.category_id,
1040 concat_string => l_concat_cat,
1041 segarray => l_cat_segs);
1042 */
1043 select table_id into h_table_id from fnd_tables
1044 where table_name = h_table_name
1045 and application_id = 140;
1046 --
1047 concat_string := '';
1048
1049 select s.concatenated_segment_delimiter into delim
1050 FROM fnd_id_flex_structures s, fnd_application a
1051 WHERE s.application_id = a.application_id
1052 AND s.id_flex_code = h_flex_code
1053 AND s.id_flex_num = l_cat_struct
1054 AND a.application_short_name = h_appl_short_name;
1055
1056 num_segs := 0;
1057 seg_ctr := 0;
1058
1059 v_sqlstmt := 'select ';
1060
1061
1062 h_mesg_name := 'FA_SHARED_FLEX_SEGCOLUMNS';
1063
1064 open segcolumns;
1065 loop
1066
1067 fetch segcolumns into col_name, v_return;
1068
1069 if (segcolumns%NOTFOUND) then exit; end if;
1070
1071 v_sqlstmt := v_sqlstmt || col_name || ', ';
1072 num_segs := num_segs + 1;
1073
1074 segarray(num_segs) := 'seeded';
1075
1076 end loop;
1077 close segcolumns;
1078
1079 h_mesg_name := 'FA_SHARED_FLEX_DYNAMIC_SQL';
1080
1081 v_sqlstmt := rtrim(v_sqlstmt,', ');
1082 v_sqlstmt := v_sqlstmt || ' from ' || h_table_name;
1083 v_sqlstmt := v_sqlstmt || ' where ' || h_ccid_col_name || ' = ';
1084 v_sqlstmt := v_sqlstmt || to_char(px_asset_cat_rec_new.category_id);
1085
1086 v_cursorid := dbms_sql.open_cursor;
1087 dbms_sql.parse(v_cursorid, v_sqlstmt, DBMS_SQL.V7);
1088
1089 for seg_ctr in 1 .. num_segs loop
1090
1091 --bugfix 3128860 msiddiqu
1092 --dbms_sql.define_column(v_cursorid, seg_ctr, segarray(seg_ctr), 25);
1093 dbms_sql.define_column(v_cursorid, seg_ctr, segarray(seg_ctr), 30);
1094
1095 end loop;
1096
1097 v_return := dbms_sql.execute(v_cursorid);
1098 v_return := dbms_sql.fetch_rows(v_cursorid);
1099
1100 for seg_ctr in 1 .. num_segs loop
1101 dbms_sql.column_value(v_cursorid, seg_ctr, segarray(seg_ctr));
1102
1103 end loop;
1104
1105 for seg_ctr in 1 .. num_segs loop
1106 concat_string := concat_string || segarray(seg_ctr) || delim;
1107
1108 end loop;
1109
1110 concat_string := rtrim(concat_string,delim);
1111
1112 dbms_sql.close_cursor(v_cursorid);
1113
1114 -- End replacing fa_rx_shared_pkg. This is what we finally need.
1115 l_concat_cat := concat_string;
1116
1117 --
1118 -- determine whether balancing seg of old and new cat is same
1119 l_err_stage:= 'FA_RECLASS_UTIL_PVT.check_bal_seg_equal';
1120 -- dbms_output.put_line(l_err_stage);
1121 if FA_RECLASS_UTIL_PVT.check_bal_seg_equal(
1122 p_old_category_id => p_asset_cat_rec_old.category_id,
1123 p_new_category_id => px_asset_cat_rec_new.category_id,
1124 p_calling_fn => l_calling_fn,
1125 p_log_level_rec => p_log_level_rec) then
1126 l_bal_seg_equal:= 'Y';
1127 else
1128 l_bal_seg_equal:= 'N';
1129 end if;
1130
1131 -- determine whether to copy cat_desc info
1132
1133 -- If copy_cat_desc_flag option is set to YES and if the old
1134 -- and new major category segment values are the same,
1135 -- old category descriptive flexfield information remains unchanged.
1136 -- Set context = attribute_category_code. Do not update attribute columns.
1137 if ( p_recl_opt_rec.copy_cat_desc_flag = 'YES' and
1138 l_bal_seg_equal = 'Y')then
1139
1140 -- dbms_output.put_line('balancing segment same');
1141
1142 px_asset_cat_rec_new.desc_flex.attribute_category_code := l_concat_cat;
1143 -- Bug 3148518
1144 --px_asset_cat_rec_new.desc_flex.context := l_concat_cat;
1145 px_asset_cat_rec_new.desc_flex.context := p_asset_cat_rec_old.desc_flex.context;
1146
1147 px_asset_cat_rec_new.desc_flex.attribute_category_code := l_concat_cat;
1148 px_asset_cat_rec_new.desc_flex.attribute1:=
1149 p_asset_cat_rec_old.desc_flex.attribute1;
1150 px_asset_cat_rec_new.desc_flex.attribute2:=
1151 p_asset_cat_rec_old.desc_flex.attribute2;
1152 px_asset_cat_rec_new.desc_flex.attribute3:=
1153 p_asset_cat_rec_old.desc_flex.attribute3;
1154 px_asset_cat_rec_new.desc_flex.attribute4:=
1155 p_asset_cat_rec_old.desc_flex.attribute4;
1156 px_asset_cat_rec_new.desc_flex.attribute5:=
1157 p_asset_cat_rec_old.desc_flex.attribute5;
1158 px_asset_cat_rec_new.desc_flex.attribute6:=
1159 p_asset_cat_rec_old.desc_flex.attribute6;
1160 px_asset_cat_rec_new.desc_flex.attribute7:=
1161 p_asset_cat_rec_old.desc_flex.attribute7;
1162 px_asset_cat_rec_new.desc_flex.attribute8:=
1163 p_asset_cat_rec_old.desc_flex.attribute8;
1164 px_asset_cat_rec_new.desc_flex.attribute9:=
1165 p_asset_cat_rec_old.desc_flex.attribute9;
1166 px_asset_cat_rec_new.desc_flex.attribute10:=
1167 p_asset_cat_rec_old.desc_flex.attribute10;
1168 px_asset_cat_rec_new.desc_flex.attribute11:=
1169 p_asset_cat_rec_old.desc_flex.attribute11;
1170 px_asset_cat_rec_new.desc_flex.attribute12:=
1171 p_asset_cat_rec_old.desc_flex.attribute12;
1172 px_asset_cat_rec_new.desc_flex.attribute13:=
1173 p_asset_cat_rec_old.desc_flex.attribute13;
1174 px_asset_cat_rec_new.desc_flex.attribute14:=
1175 p_asset_cat_rec_old.desc_flex.attribute14;
1176 px_asset_cat_rec_new.desc_flex.attribute15:=
1177 p_asset_cat_rec_old.desc_flex.attribute15;
1178 px_asset_cat_rec_new.desc_flex.attribute16:=
1179 p_asset_cat_rec_old.desc_flex.attribute16;
1180 px_asset_cat_rec_new.desc_flex.attribute17:=
1181 p_asset_cat_rec_old.desc_flex.attribute17;
1182 px_asset_cat_rec_new.desc_flex.attribute18:=
1183 p_asset_cat_rec_old.desc_flex.attribute18;
1184 px_asset_cat_rec_new.desc_flex.attribute19:=
1185 p_asset_cat_rec_old.desc_flex.attribute19;
1186 px_asset_cat_rec_new.desc_flex.attribute20:=
1187 p_asset_cat_rec_old.desc_flex.attribute20;
1188 px_asset_cat_rec_new.desc_flex.attribute21:=
1189 p_asset_cat_rec_old.desc_flex.attribute21;
1190 px_asset_cat_rec_new.desc_flex.attribute22:=
1191 p_asset_cat_rec_old.desc_flex.attribute22;
1192 px_asset_cat_rec_new.desc_flex.attribute23:=
1193 p_asset_cat_rec_old.desc_flex.attribute23;
1194 px_asset_cat_rec_new.desc_flex.attribute24:=
1195 p_asset_cat_rec_old.desc_flex.attribute24;
1196 px_asset_cat_rec_new.desc_flex.attribute25:=
1197 p_asset_cat_rec_old.desc_flex.attribute25;
1198 px_asset_cat_rec_new.desc_flex.attribute26:=
1199 p_asset_cat_rec_old.desc_flex.attribute26;
1200 px_asset_cat_rec_new.desc_flex.attribute27:=
1201 p_asset_cat_rec_old.desc_flex.attribute27;
1202 px_asset_cat_rec_new.desc_flex.attribute28:=
1203 p_asset_cat_rec_old.desc_flex.attribute28;
1204 px_asset_cat_rec_new.desc_flex.attribute29:=
1205 p_asset_cat_rec_old.desc_flex.attribute29;
1206 px_asset_cat_rec_new.desc_flex.attribute30:=
1207 p_asset_cat_rec_old.desc_flex.attribute30;
1208
1209 else
1210 -- Set context = attribute_category_code.
1211 px_asset_cat_rec_new.desc_flex.attribute_category_code := l_concat_cat;
1212
1213 -- Bug 3066664
1214 -- px_asset_cat_rec_new.desc_flex.context := l_concat_cat;
1215
1216 -- Fix for Bug #2475293. We should not be nulling the new cat flex
1217 /* px_asset_cat_rec_new.desc_flex.attribute1:= null;
1218 px_asset_cat_rec_new.desc_flex.attribute2:= null;
1219 px_asset_cat_rec_new.desc_flex.attribute3:= null;
1220 px_asset_cat_rec_new.desc_flex.attribute4:= null;
1221 px_asset_cat_rec_new.desc_flex.attribute5:= null;
1222 px_asset_cat_rec_new.desc_flex.attribute6:= null;
1223 px_asset_cat_rec_new.desc_flex.attribute7:= null;
1224 px_asset_cat_rec_new.desc_flex.attribute8:= null;
1225 px_asset_cat_rec_new.desc_flex.attribute9:= null;
1226 px_asset_cat_rec_new.desc_flex.attribute10:= null;
1227 px_asset_cat_rec_new.desc_flex.attribute11:= null;
1228 px_asset_cat_rec_new.desc_flex.attribute12:= null;
1229 px_asset_cat_rec_new.desc_flex.attribute13:= null;
1230 px_asset_cat_rec_new.desc_flex.attribute14:= null;
1231 px_asset_cat_rec_new.desc_flex.attribute15:= null;
1232 px_asset_cat_rec_new.desc_flex.attribute16:= null;
1233 px_asset_cat_rec_new.desc_flex.attribute17:= null;
1234 px_asset_cat_rec_new.desc_flex.attribute18:= null;
1235 px_asset_cat_rec_new.desc_flex.attribute19:= null;
1236 px_asset_cat_rec_new.desc_flex.attribute20:= null;
1237 px_asset_cat_rec_new.desc_flex.attribute21:= null;
1238 px_asset_cat_rec_new.desc_flex.attribute22:= null;
1239 px_asset_cat_rec_new.desc_flex.attribute23:= null;
1240 px_asset_cat_rec_new.desc_flex.attribute24:= null;
1241 px_asset_cat_rec_new.desc_flex.attribute25:= null;
1242 px_asset_cat_rec_new.desc_flex.attribute26:= null;
1243 px_asset_cat_rec_new.desc_flex.attribute27:= null;
1244 px_asset_cat_rec_new.desc_flex.attribute28:= null;
1245 px_asset_cat_rec_new.desc_flex.attribute29:= null;
1246 px_asset_cat_rec_new.desc_flex.attribute30:= null;
1247 */
1248 end if;
1249
1250 return TRUE;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 fa_srvr_msg.add_sql_error(
1255 calling_fn => l_calling_fn
1256 ,p_log_level_rec => p_log_level_rec);
1257 return FALSE;
1258
1259 END get_cat_desc_flex;
1260 -----------------------------------------------------
1261
1262 ----------------------------------------------------
1263 FUNCTION check_bal_seg_equal(
1264 p_old_category_id IN NUMBER,
1265 p_new_category_id IN NUMBER,
1266 p_calling_fn IN VARCHAR2,
1267 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
1268
1269 CURSOR get_cat_flex_struct IS
1270 SELECT category_flex_structure
1271 FROM FA_SYSTEM_CONTROLS;
1272
1273 l_cat_flex_struct NUMBER;
1274 l_gsval BOOLEAN;
1275 l_bal_segnum NUMBER;
1276 l_numof_segs NUMBER;
1277 l_all_segs_old FND_FLEX_EXT.SegmentArray;
1278 l_all_segs_new FND_FLEX_EXT.SegmentArray;
1279 l_old_bal_seg VARCHAR2(30);
1280 l_new_bal_seg VARCHAR2(30);
1281 l_calling_fn varchar2(40) := 'fa_reclass_util_pvt.check_bal_seg_equal';
1282
1283 seg_err EXCEPTION;
1284 BEGIN
1285
1286 -- determine old and new bal segments
1287 -- -- dbms_output.put_line('get_cat_flex_struct');
1288 OPEN get_cat_flex_struct;
1289 FETCH get_cat_flex_struct INTO l_cat_flex_struct;
1290 CLOSE get_cat_flex_struct;
1291
1292 -- Get the segment number for the major category.
1293 -- -- dbms_output.put_line('getqaulifier segnum');
1294 if NOT FND_FLEX_APIS.Get_Qualifier_Segnum (
1295 appl_id => 140 ,
1296 key_flex_code => 'CAT#',
1297 structure_number => l_cat_flex_struct,
1298 flex_qual_name => 'BASED_CATEGORY',
1299 segment_number => l_bal_segnum) then
1300 raise seg_err;
1301 end if;
1302
1303 -- get the segment valuse for the old category
1304 if NOT FND_FLEX_EXT.Get_Segments(
1305 application_short_name => 'OFA',
1306 key_flex_code => 'CAT#',
1307 structure_number => l_cat_flex_struct,
1308 combination_id => p_old_category_id,
1309 n_segments => l_numof_segs,
1310 segments => l_all_segs_old) then
1311 raise seg_err;
1312 end if;
1313
1314 -- Get the old major category segment value.
1315 -- -- dbms_output.put_line('l_bal_segnum '||to_char(l_bal_segnum));
1316 if nvl(l_bal_segnum, 0) <> 0 then
1317 l_old_bal_seg := l_all_segs_old(l_bal_segnum);
1318 end if;
1319
1320 -- Get the segment values for the new category.
1321 -- -- dbms_output.put_line('get_segments');
1322 if NOT FND_FLEX_EXT.Get_Segments(
1323 application_short_name => 'OFA',
1324 key_flex_code => 'CAT#',
1325 structure_number => l_cat_flex_struct,
1326 combination_id => p_new_category_id,
1327 n_segments => l_numof_segs,
1328 segments => l_all_segs_new) then
1329 raise seg_err;
1330 end if;
1331
1332 -- Get the new major category segment value.
1333 -- -- dbms_output.put_line('l_bal_segnum'||to_char(l_bal_segnum));
1334 if nvl(l_bal_segnum, 0) <> 0 then
1335 l_new_bal_seg := l_all_segs_new(l_bal_segnum);
1336 end if;
1337
1338 if ( nvl(l_old_bal_seg, '-999') <> nvl(l_new_bal_seg, '-999')) then
1339 -- dbms_output.put_line('balancing segment not equal - returning FALSE');
1340 return FALSE;
1341 end if;
1342
1343 -- dbms_output.put_line('balancing segment equal - returning TRUE');
1344 return TRUE;
1345
1346 EXCEPTION
1347 when seg_err then
1348 FA_SRVR_MSG.Add_Message(
1349 CALLING_FN => l_calling_fn,
1350 NAME => 'FA_REC_GET_CATSEG_FAILED',
1351 p_log_level_rec => p_log_level_rec);
1352 -- Message: 'Failed to get category segments.'
1353 return FALSE;
1354
1355 when others then
1356 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1357 ,p_log_level_rec => p_log_level_rec);
1358 return FALSE;
1359
1360 END check_bal_seg_equal;
1361
1362 END FA_RECLASS_UTIL_PVT;