[Home] [Help]
PACKAGE BODY: APPS.FA_MAINTENANCE_PKG
Source
1 PACKAGE BODY FA_MAINTENANCE_PKG as
2 /* $Header: FAXMTSCB.pls 120.3 2004/06/23 20:45:31 lson ship $ */
3
4 h_progname varchar2(30) := 'Asset Maintenance Scheduling';
5
6 procedure do_schedule(
7 errbuf out nocopy varchar2,
8 retcode out nocopy varchar2,
9 argument1 in varchar2, -- schedule_id
10 argument2 in varchar2 default null,
11 argument3 in varchar2 default null,
12 argument4 in varchar2 default null,
13 argument5 in varchar2 default null,
14 argument6 in varchar2 default null,
15 argument7 in varchar2 default null,
16 argument8 in varchar2 default null,
17 argument9 in varchar2 default null,
18 argument10 in varchar2 default null,
19 argument11 in varchar2 default null,
20 argument12 in varchar2 default null,
21 argument13 in varchar2 default null,
22 argument14 in varchar2 default null,
23 argument15 in varchar2 default null,
24 argument16 in varchar2 default null,
25 argument17 in varchar2 default null,
26 argument18 in varchar2 default null,
27 argument19 in varchar2 default null,
28 argument20 in varchar2 default null,
29 argument21 in varchar2 default null,
30 argument22 in varchar2 default null,
31 argument23 in varchar2 default null,
32 argument24 in varchar2 default null,
33 argument25 in varchar2 default null,
34 argument26 in varchar2 default null,
35 argument27 in varchar2 default null,
36 argument28 in varchar2 default null,
37 argument29 in varchar2 default null,
38 argument30 in varchar2 default null,
39 argument31 in varchar2 default null,
40 argument32 in varchar2 default null,
41 argument33 in varchar2 default null,
42 argument34 in varchar2 default null,
43 argument35 in varchar2 default null,
44 argument36 in varchar2 default null,
45 argument37 in varchar2 default null,
46 argument38 in varchar2 default null,
47 argument39 in varchar2 default null,
48 argument40 in varchar2 default null,
49 argument41 in varchar2 default null,
50 argument42 in varchar2 default null,
51 argument43 in varchar2 default null,
52 argument44 in varchar2 default null,
53 argument45 in varchar2 default null,
54 argument46 in varchar2 default null,
55 argument47 in varchar2 default null,
56 argument48 in varchar2 default null,
57 argument49 in varchar2 default null,
58 argument50 in varchar2 default null,
59 argument51 in varchar2 default null,
60 argument52 in varchar2 default null,
61 argument53 in varchar2 default null,
62 argument54 in varchar2 default null,
63 argument55 in varchar2 default null,
64 argument56 in varchar2 default null,
65 argument57 in varchar2 default null,
66 argument58 in varchar2 default null,
67 argument59 in varchar2 default null,
68 argument60 in varchar2 default null,
69 argument61 in varchar2 default null,
70 argument62 in varchar2 default null,
71 argument63 in varchar2 default null,
72 argument64 in varchar2 default null,
73 argument65 in varchar2 default null,
74 argument66 in varchar2 default null,
75 argument67 in varchar2 default null,
76 argument68 in varchar2 default null,
77 argument69 in varchar2 default null,
78 argument70 in varchar2 default null,
79 argument71 in varchar2 default null,
80 argument72 in varchar2 default null,
81 argument73 in varchar2 default null,
82 argument74 in varchar2 default null,
83 argument75 in varchar2 default null,
84 argument76 in varchar2 default null,
85 argument77 in varchar2 default null,
86 argument78 in varchar2 default null,
87 argument79 in varchar2 default null,
88 argument80 in varchar2 default null,
89 argument81 in varchar2 default null,
90 argument82 in varchar2 default null,
91 argument83 in varchar2 default null,
92 argument84 in varchar2 default null,
93 argument85 in varchar2 default null,
94 argument86 in varchar2 default null,
95 argument87 in varchar2 default null,
96 argument88 in varchar2 default null,
97 argument89 in varchar2 default null,
98 argument90 in varchar2 default null,
99 argument91 in varchar2 default null,
100 argument92 in varchar2 default null,
101 argument93 in varchar2 default null,
102 argument94 in varchar2 default null,
103 argument95 in varchar2 default null,
104 argument96 in varchar2 default null,
105 argument97 in varchar2 default null,
106 argument98 in varchar2 default null,
107 argument99 in varchar2 default null,
108 argument100 in varchar2 default null) is
109
110
111 events_table events_tbl_type;
112 h_num_events number;
113 h_num_assets number:= 0;
114 h_mesg_str varchar2(2000);
115 h_mesg_name varchar2(30);
116 prog_failed exception;
117 h_schedule_id number;
118 h_start_date date;
119 h_end_date date;
120 h_maint_date date;
121 h_asset_id number;
122 h_book_type_code varchar2(15);
123 h_succeed boolean:= TRUE;
124
125 cursor assets_to_schedule is
126 select ad.asset_id
127 from fa_distribution_history dh,
128 gl_code_combinations gc,
129 fa_additions ad,
130 fa_books bk,
131 fa_book_controls bc,
132 fa_maint_schedule_hdr msh
133 where msh.schedule_id = h_schedule_id
134 and bc.book_type_code = msh.book_type_code
135 and bc.book_class = 'CORPORATE'
136 and bk.book_type_code = msh.book_type_code
137 and bk.date_ineffective is null
138 and bk.period_counter_fully_retired is null
139 and bk.date_placed_in_service >=
140 nvl(msh.from_date_placed_in_service,bk.date_placed_in_service)
141 and bk.date_placed_in_service <=
142 nvl(msh.to_date_placed_in_service,bk.date_placed_in_service)
143 and bk.asset_id = ad.asset_id
144 and ad.asset_number >=
145 nvl(msh.from_asset_number, ad.asset_number)
146 and ad.asset_number <=
147 nvl(msh.to_asset_number, ad.asset_number)
148 and ad.asset_category_id =
149 nvl(msh.category_id, ad.asset_category_id)
150 and nvl(ad.asset_key_ccid,-9999) =
151 nvl(msh.asset_key_id, nvl(ad.asset_key_ccid,-9999))
152 and ad.asset_id = dh.asset_id
153 and dh.location_id = nvl(msh.location_id, dh.location_id)
154 and dh.date_ineffective is null
155 and dh.code_combination_id = gc.code_combination_id
156 group by ad.asset_id;
157
158
159 cursor date_cursor is
160 SELECT start_date,end_date,book_type_code
161
162 FROM fa_maint_schedule_hdr
163 WHERE schedule_id = h_schedule_id;
164
165 BEGIN
166
167 retcode := 0;
168
169 savepoint asset_maint;
170
171 -- print out arguments
172
173 fnd_message.set_name('OFA','FA_ASSET_MAINT_SCHARG_LIST');
174 fnd_message.set_token('SCHID', argument1, FALSE);
175 h_mesg_str := fnd_message.get;
176 fa_rx_conc_mesg_pkg.log(h_mesg_str);
177 fa_rx_conc_mesg_pkg.log('');
178 fa_rx_conc_mesg_pkg.out(h_mesg_str);
179 fa_rx_conc_mesg_pkg.out('');
180
181 -- check if valid argument is passed in
182
183 if (argument1 is NULL) then
184
185 h_mesg_name := 'FA_ASSET_MAINT_WRONG_PARAM';
186 upd_status(NULL,h_mesg_name);
187 raise prog_failed;
188 end if;
189
190 h_schedule_id := to_number(argument1);
191
192
193 open date_cursor;
194 fetch date_cursor into
195 h_start_date,h_end_date,h_book_type_code;
196
197 if (date_cursor%NOTFOUND) then
198 close date_cursor;
199 h_mesg_name := 'FA_ASSET_MAINT_WRONG_PARAM';
200 upd_status(h_schedule_id,h_mesg_name);
201 raise prog_failed;
202 end if;
203 close date_cursor;
204
205 if (h_start_date is NULL or h_end_date is NULL) then
206 h_mesg_name := 'FA_ASSET_MAINT_WRONG_PARAM';
207 upd_status(h_schedule_id,h_mesg_name);
208 raise prog_failed;
209 end if;
210
211
212 load_events_records(h_schedule_id,
213 events_table,
214 h_succeed);
215 if (NOT h_succeed) then
216 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
217 upd_status(h_schedule_id,h_mesg_name);
218 raise prog_failed;
219 end if;
220
221 open assets_to_schedule;
222
223 LOOP
224 fetch assets_to_schedule
225 into h_asset_id;
226
227 exit when assets_to_schedule%NOTFOUND;
228
229 h_num_assets := h_num_assets + 1;
230 FOR i in events_table.first ..
231 events_table.last LOOP
232
233 if (events_table(i).maintenance_date is not null) then
234
235 h_maint_date := events_table(i).maintenance_date;
236 insert_to_fa_maint_events(h_asset_id,
237 h_book_type_code,
238 events_table(i),
239 h_maint_date,
240 h_succeed);
241
242 if (NOT h_succeed) then
243 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
244 upd_status(h_schedule_id,h_mesg_name);
245 raise prog_failed;
246 end if;
247
248 elsif (events_table(i).frequency_in_days is not null) then
249
250 h_maint_date := h_start_date;
251 WHILE (h_maint_date <= h_end_date) LOOP
252
253 insert_to_fa_maint_events(h_asset_id,
254 h_book_type_code,
255 events_table(i),
256 h_maint_date,
257 h_succeed);
258 if (NOT h_succeed) then
259 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
260 upd_status(h_schedule_id,h_mesg_name);
261 raise prog_failed;
262 end if;
263
264 h_maint_date := h_maint_date +
265 events_table(i).frequency_in_days;
266 END LOOP;
267 else
268 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
269 upd_status(h_schedule_id,h_mesg_name);
270 raise prog_failed;
271 end if;
272 END LOOP;
273 END LOOP;
274
275 close assets_to_schedule;
276
277 update fa_maint_schedule_hdr
278 set status = 'COMPLETED'
279 where schedule_id = h_schedule_id;
280 commit;
281
282 fa_rx_conc_mesg_pkg.log('');
283 fa_rx_conc_mesg_pkg.log('');
284 fnd_message.set_name('OFA','FA_MASSRCL_NUM_PROC');
285 fnd_message.set_token('NUM', to_char(h_num_assets), FALSE);
286 h_mesg_str := fnd_message.get;
287 fa_rx_conc_mesg_pkg.out(h_mesg_str);
288 fa_rx_conc_mesg_pkg.out('');
289 fa_rx_conc_mesg_pkg.out('');
290
291 fnd_message.set_name('OFA', 'FA_SHARED_END_SUCCESS');
292 fnd_message.set_token('PROGRAM',h_progname,FALSE);
293 h_mesg_str := fnd_message.get;
294 fa_rx_conc_mesg_pkg.log(h_mesg_str);
295 fa_rx_conc_mesg_pkg.log('');
296 fa_rx_conc_mesg_pkg.out(h_mesg_str);
297
298 retcode := 0;
299
300 EXCEPTION
301 when prog_failed then
302 if (assets_to_schedule%ISOPEN) then
303 close assets_to_schedule;
304 end if;
305 retcode := 1;
306
307 when others then
308 if (assets_to_schedule%ISOPEN) then
309 close assets_to_schedule;
310 end if;
311 fnd_message.set_name('OFA',h_mesg_name);
312 h_mesg_str := fnd_message.get;
313 fa_rx_conc_mesg_pkg.log(h_mesg_str);
314 fa_rx_conc_mesg_pkg.log('');
315
316 fnd_message.set_name('OFA', 'FA_SHARED_END_WITH_ERROR');
317 fnd_message.set_token('PROGRAM',h_progname,FALSE);
318 h_mesg_str := fnd_message.get;
319 fa_rx_conc_mesg_pkg.log(h_mesg_str);
320 fa_rx_conc_mesg_pkg.out(h_mesg_str);
321 retcode := 2;
322
323 END do_schedule;
324
325
326 procedure upd_status(
327 p_sch_id in number,
328 p_msg_name in varchar2) is
329
330 h_mesg_str varchar2(2000);
331
332 begin
333 fnd_message.set_name('OFA', p_msg_name);
334 if (p_msg_name = 'FA_SHARED_INSERT_FAILED') then
335 fnd_message.set_token('TABLE','FA_MAINT_EVENTS',FALSE);
336 end if;
337 h_mesg_str := fnd_message.get;
338 fa_rx_conc_mesg_pkg.log(h_mesg_str);
339 fa_rx_conc_mesg_pkg.log('');
340
341 fnd_message.set_name('OFA', 'FA_SHARED_END_WITH_ERROR');
342 fnd_message.set_token('PROGRAM',h_progname,FALSE);
343 h_mesg_str := fnd_message.get;
344 fa_rx_conc_mesg_pkg.log(h_mesg_str);
345 fa_rx_conc_mesg_pkg.out(h_mesg_str);
346
347 rollback to savepoint asset_maint;
348 if (p_sch_id is not NULL) then
349 update fa_maint_schedule_hdr
350 set status = 'FAILED_RUN'
351 where schedule_id = p_sch_id;
352 commit;
353 end if;
354
355 exception
356 when others then
357 raise;
358 end;
359
360 procedure load_events_records(
361 p_schedule_id in number,
362 p_events_tbl in out nocopy events_tbl_type,
363 p_succeed out nocopy boolean) is
364
365 h_count number := 0;
366 h_detail_rec event_rec_type;
367 h_mesg_str varchar2(2000);
368
369 cursor events_cursor is
370 select schedule_id,event_name,description,frequency_in_days,
371 maintenance_date,cost,employee_id,vendor_id,created_by,
372 creation_date,last_updated_by,last_update_login,last_update_date,
373 attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,
374 attribute7,attribute8,attribute9,attribute10,attribute11,attribute12,
375 attribute13,attribute14,attribute15,attribute_category
376 from fa_maint_schedule_dtl
377 where schedule_id = p_schedule_id;
378
379 begin
380 p_succeed := TRUE;
381 p_events_tbl.delete;
382
383 open events_cursor;
384 loop
385
386 fetch events_cursor
387 into h_detail_rec;
388 exit when events_cursor%NOTFOUND;
389
390 h_count := h_count + 1;
391 p_events_tbl(h_count) := h_detail_rec;
392
393 end loop;
394
395 close events_cursor;
396
397
398 exception
399 when others then
400 if (events_cursor%ISOPEN) then
401 close events_cursor;
402 end if;
403 p_succeed := FALSE;
404
405 end load_events_records;
406
407
408 procedure insert_to_fa_maint_events(
409 p_asset_id in number,
410 p_book_type_code in varchar2,
411 p_event_rec in event_rec_type,
412 p_maint_date in date,
413 p_succeed out nocopy boolean) is
414
415 h_status varchar2(10) := 'DUE';
416 h_mesg_str varchar2(2000);
417
418 begin
419
420 p_succeed := TRUE;
421
422 insert into fa_maint_events
423 (asset_event_id,
424 asset_id,
425 event_name,
426 description,
427 frequency_in_days,
428 maintenance_date,
429 vendor_id,
430 employee_id,
431 cost,
432 book_type_code,
433 status,
434 schedule_id,
435 created_by,
436 creation_date,
437 last_updated_by,
438 last_update_login,
439 last_update_date,
440 attribute1,
441 attribute2,
442 attribute3,
443 attribute4,
444 attribute5,
445 attribute6,
446 attribute7,
447 attribute8,
448 attribute9,
449 attribute10,
450 attribute11,
451 attribute12,
452 attribute13,
453 attribute14,
454 attribute15,
455 attribute_category)
456 values
457 (fa_maint_events_s.nextval,
458 p_asset_id,
459 p_event_rec.event_name,
460 p_event_rec.description,
461 p_event_rec.frequency_in_days,
462 p_maint_date,
463 p_event_rec.vendor_id,
464 p_event_rec.employee_id,
465 p_event_rec.cost,
466 p_book_type_code,
467 h_status,
468 p_event_rec.schedule_id,
469 p_event_rec.created_by,
470 p_event_rec.creation_date,
471 p_event_rec.last_updated_by,
472 p_event_rec.last_update_login,
473 p_event_rec.last_update_date,
474 p_event_rec.attribute1,
475 p_event_rec.attribute2,
476 p_event_rec.attribute3,
477 p_event_rec.attribute4,
478 p_event_rec.attribute5,
479 p_event_rec.attribute6,
480 p_event_rec.attribute7,
481 p_event_rec.attribute8,
482 p_event_rec.attribute9,
483 p_event_rec.attribute10,
484 p_event_rec.attribute11,
485 p_event_rec.attribute12,
486 p_event_rec.attribute13,
487 p_event_rec.attribute14,
488 p_event_rec.attribute15,
489 p_event_rec.attribute_category);
490
491 exception
492 when others then
493 p_succeed := FALSE;
494
495 end insert_to_fa_maint_events;
496
497 END FA_MAINTENANCE_PKG;