[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_RET_PUB
Source
1 PACKAGE BODY FA_MASS_RET_PUB as
2 /* $Header: FAPMRLDB.pls 120.8 2009/03/27 08:25:51 bridgway ship $ */
3
4
5
6 PROCEDURE CREATE_CRITERIA
7 (p_api_version in NUMBER
8 ,p_init_msg_list in VARCHAR2 := FND_API.G_FALSE
9 ,p_commit in VARCHAR2 := FND_API.G_FALSE
10 ,p_validation_level in NUMBER := FND_API.G_VALID_LEVEL_FULL
11 ,p_calling_fn in VARCHAR2
12 ,x_return_status out NOCOPY VARCHAR2
13 ,x_msg_count out NOCOPY NUMBER
14 ,x_msg_data out NOCOPY VARCHAR2
15 ,px_mass_ret_rec in out
16 NOCOPY FA_CUSTOM_RET_VAL_PKG.mass_ret_rec_tbl_type) IS
17
18
19
20 l_mass_ret_rec fa_custom_ret_val_pkg.mass_ret_rec_tbl_type;
21 mr_count NUMBER;
22 validation_error Exception;
23 error_code varchar2(30);
24
25 -- validation cursors
26
27 l_found varchar2(30);
28
29 l_fiscal_year_name fa_book_controls.fiscal_year_name%TYPE;
30 l_fiscal_year fa_deprn_periods.fiscal_year%TYPE;
31 l_book_class fa_book_controls.book_class%TYPE;
32 l_per_close_date fa_deprn_periods.calendar_period_close_date%TYPE;
33 l_book fa_book_controls.book_type_code%TYPE;
34
35 l_category_id fa_categories.category_id%TYPE;
36 l_retirement_type_code fa_mass_retirements.retirement_type_code%TYPE;
37 l_retirement_date date;
38 l_status fa_lookups_b.lookup_code%TYPE;
39 l_location_id fa_locations.location_id%TYPE;
40 l_employee_id number;
41 l_asset_key_id fa_mass_retirements.asset_key_id%TYPE;
42 l_from_asset_no fa_additions_b.asset_number%TYPE;
43 l_to_asset_no fa_additions_b.asset_number%TYPE;
44
45 l_msg_data varchar2(200);
46 l_loop_no number;
47 l_yesno varchar2(10);
48
49 cursor c_bc is
50 select bc.fiscal_year_name,
51 bc.current_fiscal_year,
52 bc.book_class,
53 dp.calendar_period_close_date
54 from fa_book_controls bc,
55 fa_deprn_periods dp
56 where bc.book_type_code = l_book
57 and bc.last_period_counter = dp.period_counter
58 and dp.book_type_code = bc.book_type_code
59 and bc.book_class <> 'BUDGET'
60 and nvl(bc.date_ineffective, sysdate + 1) > sysdate;
61
62 cursor c_cb is
63 Select 'Validated'
64 From fa_category_books cb
65 Where book_type_code = l_book
66 And category_id = l_category_id;
67
68 cursor c_lu is
69 Select 'Validated'
70 From fa_lookups
71 Where lookup_type = 'RETIREMENT'
72 And lookup_code = l_retirement_type_code;
73
74 cursor c_rd is
75 Select 'Validated'
76 FROM fa_deprn_periods fadp
77 WHERE fadp.book_type_code = l_book
78 AND fadp.period_close_date IS NULL
79 AND l_retirement_date
80 <= fadp.calendar_period_close_date;
81
82 cursor c_rd2 is
83 SELECT 'Validated'
84 FROM fa_fiscal_year
85 WHERE fiscal_year = l_fiscal_year
86 AND fiscal_year_name = l_fiscal_year_name
87 AND l_retirement_date
88 BETWEEN start_date AND end_date;
89
90 cursor c_status is
91 SELECT 'Validated'
92 FROM FA_LOOKUPS
93 WHERE LOOKUP_TYPE = 'MASS_TRX_STATUS'
94 AND LOOKUP_CODE = l_status
95 AND LOOKUP_CODE in ('NEW','PENDING','ON_HOLD');
96
97
98 cursor c_loc is
99 SELECT 'Validated'
100 FROM FA_LOCATIONS
101 WHERE location_id = l_location_id
102 AND sysdate between nvl(start_date_active,sysdate -1)
103 and nvl(end_date_active, sysdate +1)
104 AND enabled_flag = 'Y';
105
106 cursor c_emp is
107 SELECT 'Validated'
108 FROM FA_EMPLOYEES
109 WHERE employee_id = l_employee_id;
110
111 cursor c_key is
112 SELECT 'Validated'
113 FROM FA_ASSET_KEYWORDS
114 WHERE code_combination_id = l_asset_key_id;
115
116 cursor c_fromasset is
117 Select 'Validated'
118 From fa_books bk,
119 fa_additions ad
120 Where ad.asset_number = l_from_asset_no
121 And ad.asset_id = bk.asset_id
122 And bk.book_type_code = l_book
123 And bk.date_ineffective is null;
124
125 cursor c_toasset is
126 Select 'Validated'
127 From fa_books bk,
128 fa_additions ad
129 Where ad.asset_number = l_to_asset_no
130 And ad.asset_id = bk.asset_id
131 And bk.book_type_code = l_book
132 And bk.date_ineffective is null;
133
134 -- end validation cursors
135
136 Begin
137
138 l_mass_ret_rec := px_mass_ret_rec;
139
140
141 FOR mr_count in 1..l_mass_ret_rec.count LOOP
142
143 l_loop_no := mr_count;
144 /* Use same validation as in Create Mass Retirements form */
145
146 l_book := l_mass_ret_rec(mr_count).book_type_code;
147 l_category_id := l_mass_ret_rec(mr_count).category_id;
148 l_retirement_type_code := l_mass_ret_rec(mr_count).retirement_type_code;
149 l_retirement_date := l_mass_ret_rec(mr_count).retirement_date;
153 l_asset_key_id := l_mass_ret_rec(mr_count).asset_key_id;
150 l_status := l_mass_ret_rec(mr_count).status;
151 l_location_id := l_mass_ret_rec(mr_count).location_id;
152 l_employee_id := l_mass_ret_rec(mr_count).employee_id;
154 l_from_asset_no := l_mass_ret_rec(mr_count).from_asset_number;
155 l_to_asset_no := l_mass_ret_rec(mr_count).to_asset_number;
156
157 open c_bc;
158 fetch c_bc into l_fiscal_year_name,
159 l_fiscal_year,
160 l_book_class,
161 l_per_close_date;
162
163 if c_bc%NOTFOUND then
164 error_code := 'BOOK_TYPE_CODE';
165 raise validation_error;
166 end if;
167 close c_bc;
168
169 IF l_mass_ret_rec(mr_count).category_id is not null then
170
171 open c_cb;
172 fetch c_cb into l_found;
173 if c_cb%NOTFOUND then
174 error_code := 'CATEGORY';
175 raise validation_error;
176 end if;
177 close c_cb;
178 End if;
179
180 If l_mass_ret_rec(mr_count).retirement_type_code is not null then
181 open c_lu;
182 fetch c_lu into l_found;
183 if c_lu%NOTFOUND then
184 error_code := 'RETIREMENT_TYPE_CODE';
185 raise validation_error;
186 end if;
187 close c_lu;
188 End if;
189
190 If l_mass_ret_rec(mr_count).retirement_date is not null then
191
192 open c_rd;
193 fetch c_rd into l_found;
194 if c_rd%NOTFOUND then
195 error_code := 'RETIREMENT_DATE';
196 raise validation_error;
197 end if;
198 close c_rd;
199
200 open c_rd2;
201 fetch c_rd2 into l_found;
202 if c_rd2%NOTFOUND then
203 error_code := 'RETIREMENT_DATE2';
204 raise validation_error;
205 end if;
206 close c_rd2;
207 End if;
208
209 If l_mass_ret_rec(mr_count).status is not null then
210 open c_status;
211 fetch c_status into l_found;
212 if c_status%NOTFOUND then
213 error_code := 'STATUS';
214 raise validation_error;
215 end if;
216 close c_status;
217 End if;
218
219 If (nvl(l_mass_ret_rec(mr_count).units_to_retire,0) > 0)
220 and l_book_class = 'TAX' then
221 error_code := 'TAX';
222 raise validation_error;
223 end if;
224
225 If nvl(l_mass_ret_rec(mr_count).units_to_retire,0) < 0 then
226 error_code := 'UNITS';
227 raise validation_error;
228 end if;
229
230 if nvl(l_mass_ret_rec(mr_count).retire_subcomponents_flag,'NO')
231 not in ('NO','YES') then
232 l_yesno := l_mass_ret_rec(mr_count).retire_subcomponents_flag;
233 error_code := 'SUBCOMPONENTS';
234 raise validation_error;
235 end if;
236
237 -- PROJECT AND TASK VALIDATION
238
239 -- End project and task
240
241 if nvl(l_mass_ret_rec(mr_count).asset_type,'CIP') not in ('EXPENSED',
242 'CAPITALIZED','CIP') then
243 error_code := 'ASSET TYPE';
244 raise validation_error;
245 end if;
246
247 if nvl(l_mass_ret_rec(mr_count).INCLUDE_FULLY_RSVD_FLAG,'NO')
248 not in ('YES','NO') then
249 error_code := 'FULLY_RSVD';
250 raise validation_error;
251 end if;
252
253 if l_mass_ret_rec(mr_count).location_id is not null then
254
255 open c_loc;
256 fetch c_loc into l_found;
257 if c_loc%NOTFOUND then
258 error_code := 'LOCATION';
259 raise validation_error;
260 end if;
261 close c_loc;
262
263 end if;
264
265 if l_mass_ret_rec(mr_count).employee_id is not null then
266
267 open c_emp;
268 fetch c_emp into l_found;
269 if c_emp%NOTFOUND then
270 error_code := 'EMPLOYEE';
271 raise validation_error;
272 end if;
273 close c_emp;
274
275 end if;
276
277 if l_mass_ret_rec(mr_count).asset_key_id is not null then
278
279 open c_key;
280 fetch c_key into l_found;
281 if c_key%NOTFOUND then
282 error_code := 'KEY';
283 raise validation_error;
284 end if;
285 close c_key;
286
287 end if;
288
289 if l_mass_ret_rec(mr_count).from_cost is not null then
290
291 if l_mass_ret_rec(mr_count).from_cost >
292 nvl(l_mass_ret_rec(mr_count).to_cost,0) then
293 error_code := 'COST';
294 raise validation_error;
295
296 end if;
297
298 end if;
299
300 if l_mass_ret_rec(mr_count).from_asset_number is not null then
301 if l_mass_ret_rec(mr_count).from_asset_number <=
302 l_mass_ret_rec(mr_count).to_asset_number then
303
304 open c_fromasset;
305 fetch c_fromasset into l_found;
306 if c_fromasset%NOTFOUND then
307 error_code := 'FROM ASSET';
308 raise validation_error;
309 end if;
310 close c_fromasset;
311
312 open c_toasset;
313 fetch c_toasset into l_found;
314 if c_toasset%NOTFOUND then
315 error_code := 'TO ASSET';
316 raise validation_error;
317 end if;
318 close c_toasset;
319
320 else
321 error_code := 'FROM ASSET';
322 raise validation_error;
323
324 end if;
325
326
327 end if;
328
329 if l_mass_ret_rec(mr_count).from_date_placed_in_service is not null then
330
331
332 if (l_mass_ret_rec(mr_count).from_date_placed_in_service > l_per_close_date)
333 or
334 (l_mass_ret_rec(mr_count).to_date_placed_in_service > l_per_close_date)
335 then
336 error_code := 'DPIS';
337 raise validation_error;
338 end if;
339 end if;
340
341
342 Insert into fa_mass_retirements (
343 MASS_RETIREMENT_ID,
344 BOOK_TYPE_CODE,
345 RETIRE_SUBCOMPONENTS_FLAG,
346 STATUS,
347 RETIRE_REQUEST_ID,
348 REINSTATE_REQUEST_ID,
349 RETIREMENT_DATE,
350 PROCEEDS_OF_SALE,
351 COST_OF_REMOVAL,
352 DESCRIPTION,
353 RETIREMENT_TYPE_CODE,
354 ASSET_TYPE,
355 LOCATION_ID,
356 EMPLOYEE_ID,
357 CATEGORY_ID,
358 ASSET_KEY_ID,
359 FROM_ASSET_NUMBER,
360 TO_ASSET_NUMBER,
361 FROM_DATE_PLACED_IN_SERVICE,
362 TO_DATE_PLACED_IN_SERVICE,
363 FROM_COST,
364 MODEL_NUMBER,
365 TAG_NUMBER,
366 MANUFACTURER_NAME,
367 SERIAL_NUMBER,
368 CREATE_REQUEST_ID,
369 UNITS_TO_RETIRE,
370 INCLUDE_FULLY_RSVD_FLAG,
371 TO_COST,
372 GROUP_ASSET_ID,
373 FROM_THRESHOLD_AMOUNT,
374 TO_THRESHOLD_AMOUNT,
375 PROJECT_ID,
376 TASK_ID,
377 ATTRIBUTE1,
378 ATTRIBUTE2,
379 ATTRIBUTE3,
380 ATTRIBUTE4,
381 ATTRIBUTE5,
382 ATTRIBUTE6,
383 ATTRIBUTE7,
384 ATTRIBUTE8,
385 ATTRIBUTE9,
386 ATTRIBUTE10,
387 ATTRIBUTE11,
388 ATTRIBUTE12,
389 ATTRIBUTE13,
390 ATTRIBUTE14,
391 ATTRIBUTE15,
392 ATTRIBUTE_CATEGORY_CODE,
393 SEGMENT1_LOW,
394 SEGMENT2_LOW,
395 SEGMENT3_LOW,
396 SEGMENT4_LOW,
397 SEGMENT5_LOW,
398 SEGMENT6_LOW,
399 SEGMENT7_LOW,
400 SEGMENT8_LOW,
401 SEGMENT9_LOW,
402 SEGMENT10_LOW,
403 SEGMENT11_LOW,
404 SEGMENT12_LOW,
405 SEGMENT13_LOW,
406 SEGMENT14_LOW,
407 SEGMENT15_LOW,
408 SEGMENT16_LOW,
409 SEGMENT17_LOW,
410 SEGMENT18_LOW,
411 SEGMENT19_LOW,
412 SEGMENT20_LOW,
413 SEGMENT21_LOW,
414 SEGMENT22_LOW,
415 SEGMENT23_LOW,
416 SEGMENT24_LOW,
417 SEGMENT25_LOW,
418 SEGMENT26_LOW,
419 SEGMENT27_LOW,
420 SEGMENT28_LOW,
421 SEGMENT29_LOW,
422 SEGMENT30_LOW,
423 SEGMENT1_HIGH,
424 SEGMENT2_HIGH,
425 SEGMENT3_HIGH,
426 SEGMENT4_HIGH,
427 SEGMENT5_HIGH,
428 SEGMENT6_HIGH,
429 SEGMENT7_HIGH,
430 SEGMENT8_HIGH,
431 SEGMENT9_HIGH,
432 SEGMENT10_HIGH,
433 SEGMENT11_HIGH,
434 SEGMENT12_HIGH,
435 SEGMENT13_HIGH,
436 SEGMENT14_HIGH,
437 SEGMENT15_HIGH,
438 SEGMENT16_HIGH,
439 SEGMENT17_HIGH,
440 SEGMENT18_HIGH,
441 SEGMENT19_HIGH,
442 SEGMENT20_HIGH,
443 SEGMENT21_HIGH,
444 SEGMENT22_HIGH,
445 SEGMENT23_HIGH,
446 SEGMENT24_HIGH,
447 SEGMENT25_HIGH,
448 SEGMENT26_HIGH,
449 SEGMENT27_HIGH,
450 SEGMENT28_HIGH,
451 SEGMENT29_HIGH,
452 SEGMENT30_HIGH,
453 LAST_UPDATE_DATE,
454 LAST_UPDATED_BY,
455 CREATION_DATE,
456 CREATED_BY,
457 LAST_UPDATE_LOGIN
458 )
459 VALUES
460 (
461 FA_MASS_TRANSACTIONS_S.NEXTVAL,
462 L_MASS_RET_REC(mr_count).BOOK_TYPE_CODE,
463 L_MASS_RET_REC(mr_count).RETIRE_SUBCOMPONENTS_FLAG,
464 L_MASS_RET_REC(mr_count).STATUS,
465 L_MASS_RET_REC(mr_count).RETIRE_REQUEST_ID,
466 L_MASS_RET_REC(mr_count).REINSTATE_REQUEST_ID,
467 L_MASS_RET_REC(mr_count).RETIREMENT_DATE,
468 L_MASS_RET_REC(mr_count).PROCEEDS_OF_SALE,
469 L_MASS_RET_REC(mr_count).COST_OF_REMOVAL,
470 L_MASS_RET_REC(mr_count).DESCRIPTION,
471 L_MASS_RET_REC(mr_count).RETIREMENT_TYPE_CODE,
472 L_MASS_RET_REC(mr_count).ASSET_TYPE,
473 L_MASS_RET_REC(mr_count).LOCATION_ID,
474 L_MASS_RET_REC(mr_count).EMPLOYEE_ID,
475 L_MASS_RET_REC(mr_count).CATEGORY_ID,
476 L_MASS_RET_REC(mr_count).ASSET_KEY_ID,
477 L_MASS_RET_REC(mr_count).FROM_ASSET_NUMBER,
478 L_MASS_RET_REC(mr_count).TO_ASSET_NUMBER,
479 L_MASS_RET_REC(mr_count).FROM_DATE_PLACED_IN_SERVICE,
480 L_MASS_RET_REC(mr_count).TO_DATE_PLACED_IN_SERVICE,
481 L_MASS_RET_REC(mr_count).FROM_COST,
482 L_MASS_RET_REC(mr_count).MODEL_NUMBER,
483 L_MASS_RET_REC(mr_count).TAG_NUMBER,
484 L_MASS_RET_REC(mr_count).MANUFACTURER_NAME,
485 L_MASS_RET_REC(mr_count).SERIAL_NUMBER,
486 L_MASS_RET_REC(mr_count).CREATE_REQUEST_ID,
487 L_MASS_RET_REC(mr_count).UNITS_TO_RETIRE,
488 L_MASS_RET_REC(mr_count).INCLUDE_FULLY_RSVD_FLAG,
489 L_MASS_RET_REC(mr_count).TO_COST,
490 L_MASS_RET_REC(mr_count).GROUP_ASSET_ID,
491 L_MASS_RET_REC(mr_count).FROM_THRESHOLD_AMOUNT,
492 L_MASS_RET_REC(mr_count).TO_THRESHOLD_AMOUNT,
493 L_MASS_RET_REC(mr_count).PROJECT_ID,
494 L_MASS_RET_REC(mr_count).TASK_ID,
495 L_MASS_RET_REC(mr_count).ATTRIBUTE1,
496 L_MASS_RET_REC(mr_count).ATTRIBUTE2,
497 L_MASS_RET_REC(mr_count).ATTRIBUTE3,
498 L_MASS_RET_REC(mr_count).ATTRIBUTE4,
499 L_MASS_RET_REC(mr_count).ATTRIBUTE5,
500 L_MASS_RET_REC(mr_count).ATTRIBUTE6,
501 L_MASS_RET_REC(mr_count).ATTRIBUTE7,
502 L_MASS_RET_REC(mr_count).ATTRIBUTE8,
503 L_MASS_RET_REC(mr_count).ATTRIBUTE9,
504 L_MASS_RET_REC(mr_count).ATTRIBUTE10,
505 L_MASS_RET_REC(mr_count).ATTRIBUTE11,
506 L_MASS_RET_REC(mr_count).ATTRIBUTE12,
507 L_MASS_RET_REC(mr_count).ATTRIBUTE13,
508 L_MASS_RET_REC(mr_count).ATTRIBUTE14,
509 L_MASS_RET_REC(mr_count).ATTRIBUTE15,
510 L_MASS_RET_REC(mr_count).ATTRIBUTE_CATEGORY_CODE,
511 L_MASS_RET_REC(mr_count).SEGMENT1_LOW,
512 L_MASS_RET_REC(mr_count).SEGMENT2_LOW,
513 L_MASS_RET_REC(mr_count).SEGMENT3_LOW,
514 L_MASS_RET_REC(mr_count).SEGMENT4_LOW,
515 L_MASS_RET_REC(mr_count).SEGMENT5_LOW,
516 L_MASS_RET_REC(mr_count).SEGMENT6_LOW,
517 L_MASS_RET_REC(mr_count).SEGMENT7_LOW,
518 L_MASS_RET_REC(mr_count).SEGMENT8_LOW,
519 L_MASS_RET_REC(mr_count).SEGMENT9_LOW,
520 L_MASS_RET_REC(mr_count).SEGMENT10_LOW,
521 L_MASS_RET_REC(mr_count).SEGMENT11_LOW,
522 L_MASS_RET_REC(mr_count).SEGMENT12_LOW,
523 L_MASS_RET_REC(mr_count).SEGMENT13_LOW,
524 L_MASS_RET_REC(mr_count).SEGMENT14_LOW,
525 L_MASS_RET_REC(mr_count).SEGMENT15_LOW,
526 L_MASS_RET_REC(mr_count).SEGMENT16_LOW,
527 L_MASS_RET_REC(mr_count).SEGMENT17_LOW,
528 L_MASS_RET_REC(mr_count).SEGMENT18_LOW,
529 L_MASS_RET_REC(mr_count).SEGMENT19_LOW,
530 L_MASS_RET_REC(mr_count).SEGMENT20_LOW,
531 L_MASS_RET_REC(mr_count).SEGMENT21_LOW,
532 L_MASS_RET_REC(mr_count).SEGMENT22_LOW,
533 L_MASS_RET_REC(mr_count).SEGMENT23_LOW,
534 L_MASS_RET_REC(mr_count).SEGMENT24_LOW,
535 L_MASS_RET_REC(mr_count).SEGMENT25_LOW,
536 L_MASS_RET_REC(mr_count).SEGMENT26_LOW,
537 L_MASS_RET_REC(mr_count).SEGMENT27_LOW,
538 L_MASS_RET_REC(mr_count).SEGMENT28_LOW,
539 L_MASS_RET_REC(mr_count).SEGMENT29_LOW,
540 L_MASS_RET_REC(mr_count).SEGMENT30_LOW,
541 L_MASS_RET_REC(mr_count).SEGMENT1_HIGH,
542 L_MASS_RET_REC(mr_count).SEGMENT2_HIGH,
543 L_MASS_RET_REC(mr_count).SEGMENT3_HIGH,
544 L_MASS_RET_REC(mr_count).SEGMENT4_HIGH,
545 L_MASS_RET_REC(mr_count).SEGMENT5_HIGH,
546 L_MASS_RET_REC(mr_count).SEGMENT6_HIGH,
547 L_MASS_RET_REC(mr_count).SEGMENT7_HIGH,
548 L_MASS_RET_REC(mr_count).SEGMENT8_HIGH,
549 L_MASS_RET_REC(mr_count).SEGMENT9_HIGH,
550 L_MASS_RET_REC(mr_count).SEGMENT10_HIGH,
551 L_MASS_RET_REC(mr_count).SEGMENT11_HIGH,
552 L_MASS_RET_REC(mr_count).SEGMENT12_HIGH,
553 L_MASS_RET_REC(mr_count).SEGMENT13_HIGH,
554 L_MASS_RET_REC(mr_count).SEGMENT14_HIGH,
555 L_MASS_RET_REC(mr_count).SEGMENT15_HIGH,
556 L_MASS_RET_REC(mr_count).SEGMENT16_HIGH,
557 L_MASS_RET_REC(mr_count).SEGMENT17_HIGH,
558 L_MASS_RET_REC(mr_count).SEGMENT18_HIGH,
559 L_MASS_RET_REC(mr_count).SEGMENT19_HIGH,
560 L_MASS_RET_REC(mr_count).SEGMENT20_HIGH,
561 L_MASS_RET_REC(mr_count).SEGMENT21_HIGH,
562 L_MASS_RET_REC(mr_count).SEGMENT22_HIGH,
563 L_MASS_RET_REC(mr_count).SEGMENT23_HIGH,
564 L_MASS_RET_REC(mr_count).SEGMENT24_HIGH,
565 L_MASS_RET_REC(mr_count).SEGMENT25_HIGH,
566 L_MASS_RET_REC(mr_count).SEGMENT26_HIGH,
567 L_MASS_RET_REC(mr_count).SEGMENT27_HIGH,
568 L_MASS_RET_REC(mr_count).SEGMENT28_HIGH,
569 L_MASS_RET_REC(mr_count).SEGMENT29_HIGH,
570 L_MASS_RET_REC(mr_count).SEGMENT30_HIGH,
571 L_MASS_RET_REC(mr_count).LAST_UPDATE_DATE,
572 L_MASS_RET_REC(mr_count).LAST_UPDATED_BY,
573 L_MASS_RET_REC(mr_count).CREATION_DATE,
574 L_MASS_RET_REC(mr_count).CREATED_BY,
575 L_MASS_RET_REC(mr_count).LAST_UPDATE_LOGIN
576 );
577
578 END LOOP;
579
580
581 IF FND_API.To_Boolean(p_commit) THEN
582 COMMIT WORK;
583 END IF;
584
585 x_return_status := 0;
586
587 Exception
588 When validation_error then
589
590 if error_code = 'BOOK_TYPE_CODE' then
591 FND_MESSAGE.SET_NAME('OFA', 'FA_BOOK_INEFFECTIVE_BOOK');
592 x_msg_data := fnd_message.get;
593
594 elsif error_code = 'CATEGORY' then
595 FND_MESSAGE.SET_NAME('OFA', 'FA_BOOK_CATBOOK_NOT_DEFINED');
596 x_msg_data := fnd_message.get;
597
598 elsif error_code = 'RETIREMENT_TYPE_CODE' then
599 x_msg_data := 'Retirement Type is incorrect';
600 --
601 elsif error_code = 'RETIREMENT_DATE' then
602 FND_MESSAGE.SET_NAME('OFA', 'FA_SHARED_CANNOT_FUTURE');
603 x_msg_data := fnd_message.get;
604
605 elsif error_code = 'RETIREMENT_DATE2' then
606 FND_MESSAGE.SET_NAME('OFA', 'FA_RET_DATE_MUSTBE_IN_CUR_FY');
607 x_msg_data := fnd_message.get;
608
609 elsif error_code = 'STATUS' then
610 FND_MESSAGE.SET_NAME('OFA', 'FA_SHARED_UNKNOWN_STATUS');
611 FND_MESSAGE.SET_TOKEN('STATUS',l_status, false);
612 x_msg_data := fnd_message.get;
613
614 elsif error_code = 'TAX' then
615 FND_MESSAGE.SET_NAME('OFA', 'FA_RET_COST_ONLY');
616 x_msg_data := fnd_message.get;
617
618 elsif error_code = 'UNITS' then
619 FND_MESSAGE.SET_NAME('OFA', 'FA_UNT_ADJ_VAL_CUR_UNTS');
620 x_msg_data := fnd_message.get;
621
622 elsif error_code = 'SUBCOMPONENTS' then
623 FND_MESSAGE.SET_NAME('OFA', 'FA_API_SHARED_INVALID_YESNO');
624 FND_MESSAGE.SET_TOKEN('VALUE', l_yesno, false);
625 FND_MESSAGE.SET_TOKEN('XMLTAG', 'SUBCOMPONENTS', false);
626 x_msg_data := fnd_message.get;
627
628 elsif error_code = 'ASSET TYPE' then
629 FND_MESSAGE.SET_NAME('OFA', 'FA_DPR_BAD_ASSET_TYPE');
630 x_msg_data := fnd_message.get;
631
632 elsif error_code = 'FULLY_RSVD' then
633 FND_MESSAGE.SET_NAME('OFA', 'FA_API_SHARED_INVALID_YESNO');
634 x_msg_data := fnd_message.get;
635
636 elsif error_code = 'LOCATION' then
637 FND_MESSAGE.SET_NAME('OFA', 'FA_INCORRECT_LOCATION');
638 x_msg_data := fnd_message.get;
639
640 elsif error_code = 'EMPLOYEE' then
641 FND_MESSAGE.SET_NAME('OFA', 'FA_INCORRECT_ASSIGNED_TO');
642 x_msg_data := fnd_message.get;
643
644 elsif error_code = 'KEY' then
645 FND_MESSAGE.SET_NAME('OFA', 'FA_INCORRECT_ASSET_KEY');
646 FND_MESSAGE.SET_TOKEN('ASSET_KEY_CCID', l_asset_key_id, false);
647 x_msg_data := fnd_message.get;
648
649 elsif error_code = 'COST' then
650 FND_MESSAGE.SET_NAME('OFA', 'FA_FE_CANT_CALC_COST_RET');
651 x_msg_data := fnd_message.get;
652
653 elsif error_code = 'FROM ASSET' then
654 FND_MESSAGE.SET_NAME('OFA', 'FA_REV_FAILED');
655 FND_MESSAGE.SET_TOKEN('ASSET_NUMBER',
656 l_from_asset_no , false);
657 x_msg_data := fnd_message.get;
658
659 elsif error_code = 'TO ASSET' then
660 FND_MESSAGE.SET_NAME('OFA', 'FA_REV_FAILED');
661 FND_MESSAGE.SET_TOKEN('ASSET_NUMBER',
662 l_to_asset_no, false);
663 x_msg_data := fnd_message.get;
664
665 elsif error_code = 'DPIS' then
666 FND_MESSAGE.SET_NAME('OFA', 'FA_SHARED_CANNOT_FUTURE');
667 x_msg_data := fnd_message.get;
668 end if;
669
670 x_return_status := 2;
671 Rollback;
672 When others then
673 x_msg_data := sqlerrm;
674 x_return_status := 2;
675 Rollback;
676
677 END CREATE_CRITERIA;
678 END FA_MASS_RET_PUB;