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