[Home] [Help]
PACKAGE BODY: APPS.FARX_C_MT
Source
1 PACKAGE BODY FARX_C_MT as
2 /* $Header: FARXCMTB.pls 120.2 2003/08/27 23:07:50 lson ship $ */
3
4 PROCEDURE insert_to_itf (
5 p_book in varchar2,
6 p_event_name in varchar2,
7 p_maint_date_from in date,
8 p_maint_date_to in date,
9 p_asset_number_from in varchar2,
10 p_asset_number_to in varchar2,
11 p_dpis_from in date,
12 p_dpis_to in date,
13 p_cat_id in number,
14 p_request_id in number,
15 retcode out nocopy varchar2,
16 errbuf out nocopy varchar2) is
17
18
19 h_cat_structure number;
20 h_loc_structure number;
21 h_key_structure number;
22 h_cat_ccid number := NULL;
23
24 h_request_id number;
25 h_login_id number;
26 h_user_id number;
27 h_mesg_str varchar2(2000);
28 h_mesg_name varchar2(50);
29
30 h_concat_cat varchar2(500);
31 h_concat_loc varchar2(500);
32 h_concat_key varchar2(500);
33 h_cat_segs fa_rx_shared_pkg.Seg_Array;
34 h_loc_segs fa_rx_shared_pkg.Seg_Array;
35 h_key_segs fa_rx_shared_pkg.Seg_Array;
36
37
38 cursor c_assets is
39 select distinct me.asset_event_id,ad.asset_id,ad.asset_number,ad.description,ad.serial_number,
40 ad.tag_number, ad.manufacturer_name,ad.model_number,ad.asset_category_id,
41 ad.asset_key_ccid, dh.location_id location_id,
42 bk.cost asset_cost,bk.book_type_code,me.event_name,me.cost maint_cost,
43 me.maintenance_date,emp1.full_name warr_contact_name,emp1.employee_number warr_contact_number,
44 po.vendor_name,po.segment1 vendor_number, war.warranty_number,war.description war_desc,
45 war.start_date,war.end_date,emp2.full_name maint_contact_name,
46 emp2.employee_number maint_contact_number
47
48 from fa_additions ad,
49 fa_distribution_history dh,
50 po_vendors po,
51 per_people_x emp1,
52 per_people_x emp2,
53 fa_warranties war,
54 fa_add_warranties adw,
55 fa_books bk,
56 fa_maint_events me
57 where me.book_type_code = p_book and
58 me.event_name = nvl(p_event_name, me.event_name) and
59 me.maintenance_date between
60 nvl(p_maint_date_from,me.maintenance_date) and
61 nvl(p_maint_date_to,me.maintenance_date) and
62 me.asset_id = ad.asset_id and
63 ad.asset_number >= nvl(p_asset_number_from,ad.asset_number) and
64 ad.asset_number <= nvl(p_asset_number_to,ad.asset_number) and
65 ad.asset_category_id = nvl(p_cat_id, ad.asset_category_id) and
66 dh.asset_id = ad.asset_id and
67 dh.date_ineffective is NULL and
68 bk.asset_id = ad.asset_id and
69 bk.book_type_code = me.book_type_code and
70 bk.date_ineffective is NULL and
71 bk.date_placed_in_service between
72 nvl(p_dpis_from,bk.date_placed_in_service) and
73 nvl(p_dpis_to,bk.date_placed_in_service) and
74 adw.asset_id(+) = me.asset_id and
75 war.warranty_id(+) = adw.warranty_id and
76 emp1.person_id(+) = war.employee_id and
77 emp2.person_id(+) = me.employee_id and
78 po.vendor_id(+) = me.vendor_id;
79
80 c_mainrec c_assets%rowtype;
81
82
83 begin
84
85 h_request_id := p_request_id;
86
87 select category_flex_structure,
88 location_flex_structure,
89 asset_key_flex_structure
90 into h_cat_structure,
91 h_loc_structure,
92 h_key_structure
93 from fa_system_controls;
94
95
96 select fcr.last_update_login,fcr.requested_by
97 into h_login_id,h_user_id
98 from fnd_concurrent_requests fcr
99 where fcr.request_id = h_request_id;
100
101
102 /* if (h_category is not null) then
103 --h_value_error := category;
104 --h_param_error := 'CATEGORY';
105 if fnd_flex_keyval.validate_segs (
106 operation => 'CHECK_COMBINATION',
107 appl_short_name => 'OFA',
108 key_flex_code => 'CAT#',
109 structure_number => h_cat_structure,
110 concat_segments => h_category,
111 values_or_ids => 'V',
112 validation_date =>SYSDATE,
113 displayable => 'ALL',
114 data_set => NULL,
115 vrule => NULL,
116 where_clause => NULL,
117 get_columns => NULL,
118 allow_nulls => FALSE,
119 allow_orphans => FALSE,
120 resp_appl_id => NULL,
121 resp_id => NULL,
122 user_id => NULL) = FALSE then
123
124 fnd_message.set_name('OFA','FA_WHATIF_NO_CAT');
125 fnd_message.set_token('CAT',p_category,FALSE);
126 h_mesg_str := fnd_message.get;
127 fa_rx_conc_mesg_pkg.log(h_mesg_str);
128
129 retcode := 2;
130 return;
131 end if;
132 h_cat_ccid := fnd_flex_keyval.combination_id;
133 end if;
134
135 if (h_location is not null) then
136 --h_value_error := location;
137 --h_param_error := 'LOCATION';
138
139 if fnd_flex_keyval.validate_segs (
140 operation => 'CHECK_COMBINATION',
141 appl_short_name => 'OFA',
142 key_flex_code => 'LOC#',
143 structure_number => h_loc_structure,
144 concat_segments => h_location,
145 values_or_ids => 'V',
146 validation_date =>SYSDATE,
147 displayable => 'ALL',
148 data_set => NULL,
149 vrule => NULL,
150 where_clause => NULL,
151 get_columns => NULL,
152 allow_nulls => FALSE,
153 allow_orphans => FALSE,
154 resp_appl_id => NULL,
155 resp_id => NULL,
156 user_id => NULL) = FALSE then
157
158 fnd_message.set_name('OFA','FA_PI_NO_LOCATION');
159 fnd_message.set_token('LOC',p_location,FALSE);
160 h_mesg_str := fnd_message.get;
161 fa_rx_conc_mesg_pkg.log(h_mesg_str);
162
163 retcode := 2;
164 return;
165 end if;
166 h_loc_ccid := fnd_flex_keyval.combination_id;
167 end if;
168
169 if (h_asset_key is not null) then
170 --h_value_error := location;
171 --h_param_error := 'LOCATION';
172
173 if fnd_flex_keyval.validate_segs (
174 operation => 'CHECK_COMBINATION',
175 appl_short_name => 'OFA',
176 key_flex_code => 'KEY#',
177 structure_number => h_key_structure,
178 concat_segments => h_asset_key,
179 values_or_ids => 'V',
180 validation_date =>SYSDATE,
181 displayable => 'ALL',
182 data_set => NULL,
183 vrule => NULL,
184 where_clause => NULL,
185 get_columns => NULL,
186 allow_nulls => FALSE,
187 allow_orphans => FALSE,
188 resp_appl_id => NULL,
189 resp_id => NULL,
190 user_id => NULL) = FALSE then
191
192 fnd_message.set_name('OFA','FA_ASSET_MAINT_NO_KEY');
193 fnd_message.set_token('LOC',p_asset_key,FALSE);
194 h_mesg_str := fnd_message.get;
195 fa_rx_conc_mesg_pkg.log(h_mesg_str);
196
197 retcode := 2;
198 return;
199 end if;
200 h_key_ccid := fnd_flex_keyval.combination_id;
201 end if; */
202
203 open c_assets;
204 loop
205
206 fetch c_assets into c_mainrec;
207
208 if (c_assets%NOTFOUND) then
209 exit;
210 end if;
211
212
213 -- h_concat_cat := p_category;
214 if (c_mainrec.asset_category_id is not null) then
215
216 fa_rx_shared_pkg.concat_category (
217 struct_id => h_cat_structure,
218 ccid => c_mainrec.asset_category_id,
219 concat_string => h_concat_cat,
220 segarray => h_cat_segs);
221 end if;
222
223 -- h_concat_loc := p_location;
224 if (c_mainrec.location_id is not null) then
225
226 fa_rx_shared_pkg.concat_location (
227 struct_id => h_loc_structure,
228 ccid => c_mainrec.location_id,
229 concat_string => h_concat_loc,
230 segarray => h_loc_segs);
231 end if;
232
233 -- h_concat_key := p_asset_key;
234 if (c_mainrec.asset_key_ccid is not null) then
235
236 fa_rx_shared_pkg.concat_asset_key (
237 struct_id => h_key_structure,
238 ccid => c_mainrec.asset_key_ccid,
239 concat_string => h_concat_key,
240 segarray => h_key_segs);
241 end if;
242
243 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
244 insert into fa_maint_rep_itf (
245 REQUEST_ID,BOOK_TYPE_CODE,ASSET_ID,ASSET_NUMBER,DESCRIPTION,SERIAL_NUMBER,TAG_NUMBER,
246 ASSET_KEY_FF,ASSET_COST,LOCATION_FF,CATEGORY_FF,EVENT_NAME,MAINTENANCE_COST,
247 MAINTENANCE_DATE,VENDOR_NAME,VENDOR_NUMBER,CONTACT_NAME,CONTACT_NUMBER,
248 WARRANTY_NUMBER,WARRANTY_DESC,WARRANTY_START_DATE,WARRANTY_END_DATE,
249 MANUFACTURER_NAME,MODEL_NUMBER,WARRANTY_CONTACT_NAME,WARRANTY_CONTACT_NUMBER,
250 LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN)
251 values (h_request_id,c_mainrec.book_type_code,c_mainrec.asset_id,c_mainrec.asset_number,
252 c_mainrec.description,c_mainrec.serial_number,c_mainrec.tag_number,
253 h_concat_key,c_mainrec.asset_cost,h_concat_loc,h_concat_cat,c_mainrec.event_name,
254 c_mainrec.maint_cost,c_mainrec.maintenance_date,c_mainrec.vendor_name,
255 c_mainrec.vendor_number,c_mainrec.maint_contact_name,c_mainrec.maint_contact_number,
256 c_mainrec.warranty_number,c_mainrec.war_desc,c_mainrec.start_date,
257 c_mainrec.end_date,c_mainrec.manufacturer_name,c_mainrec.model_number,
258 c_mainrec.warr_contact_name,c_mainrec.warr_contact_number,sysdate,h_user_id,
259 h_user_id,sysdate,h_login_id);
260
261 end loop;
262
263 close c_assets;
264
265 retcode := 0;
266
267
268 exception
269 when others then
270 fnd_message.set_name('OFA', h_mesg_name);
271 if (h_mesg_name = 'FA_SHARED_INSERT_FAILED') then
272 fnd_message.set_token('TABLE','FA_MAINT_REP_ITF');
273 end if;
274 h_mesg_str := fnd_message.get;
275 fa_rx_conc_mesg_pkg.log(h_mesg_str);
276 retcode := 2;
277
278 end insert_to_itf;
279
280
281
282 PROCEDURE asset_maintenance (
283 errbuf out nocopy varchar2,
284 retcode out nocopy varchar2,
285 argument1 in varchar2, -- book
286 argument2 in varchar2 default null, -- event_name
287 argument3 in varchar2 default null, -- maint_date_from
288 argument4 in varchar2 default null, -- maint_date_to
289 argument5 in varchar2 default null, -- asset_number_from
290 argument6 in varchar2 default null, -- asset_number_to
291 argument7 in varchar2 default null, -- dpis_from
292 argument8 in varchar2 default null, -- dpis_to
293 argument9 in varchar2 default null, -- category flex struct
294 argument10 in varchar2 default null, -- category_Id
295 argument11 in varchar2 default null,
296 argument12 in varchar2 default null,
297 argument13 in varchar2 default null,
298 argument14 in varchar2 default null,
299 argument15 in varchar2 default null,
300 argument16 in varchar2 default null,
301 argument17 in varchar2 default null,
302 argument18 in varchar2 default null,
303 argument19 in varchar2 default null,
304 argument20 in varchar2 default null,
305 argument21 in varchar2 default null,
306 argument22 in varchar2 default null,
307 argument23 in varchar2 default null,
308 argument24 in varchar2 default null,
309 argument25 in varchar2 default null,
310 argument26 in varchar2 default null,
311 argument27 in varchar2 default null,
312 argument28 in varchar2 default null,
313 argument29 in varchar2 default null,
314 argument30 in varchar2 default null,
315 argument31 in varchar2 default null,
316 argument32 in varchar2 default null,
317 argument33 in varchar2 default null,
318 argument34 in varchar2 default null,
319 argument35 in varchar2 default null,
320 argument36 in varchar2 default null,
321 argument37 in varchar2 default null,
322 argument38 in varchar2 default null,
323 argument39 in varchar2 default null,
324 argument40 in varchar2 default null,
325 argument41 in varchar2 default null,
326 argument42 in varchar2 default null,
327 argument43 in varchar2 default null,
328 argument44 in varchar2 default null,
329 argument45 in varchar2 default null,
330 argument46 in varchar2 default null,
331 argument47 in varchar2 default null,
332 argument48 in varchar2 default null,
333 argument49 in varchar2 default null,
334 argument50 in varchar2 default null,
335 argument51 in varchar2 default null,
336 argument52 in varchar2 default null,
337 argument53 in varchar2 default null,
338 argument54 in varchar2 default null,
339 argument55 in varchar2 default null,
340 argument56 in varchar2 default null,
341 argument57 in varchar2 default null,
342 argument58 in varchar2 default null,
343 argument59 in varchar2 default null,
344 argument60 in varchar2 default null,
345 argument61 in varchar2 default null,
346 argument62 in varchar2 default null,
347 argument63 in varchar2 default null,
348 argument64 in varchar2 default null,
349 argument65 in varchar2 default null,
350 argument66 in varchar2 default null,
351 argument67 in varchar2 default null,
352 argument68 in varchar2 default null,
353 argument69 in varchar2 default null,
354 argument70 in varchar2 default null,
355 argument71 in varchar2 default null,
356 argument72 in varchar2 default null,
357 argument73 in varchar2 default null,
358 argument74 in varchar2 default null,
359 argument75 in varchar2 default null,
363 argument79 in varchar2 default null,
360 argument76 in varchar2 default null,
361 argument77 in varchar2 default null,
362 argument78 in varchar2 default null,
364 argument80 in varchar2 default null,
365 argument81 in varchar2 default null,
366 argument82 in varchar2 default null,
367 argument83 in varchar2 default null,
368 argument84 in varchar2 default null,
369 argument85 in varchar2 default null,
370 argument86 in varchar2 default null,
371 argument87 in varchar2 default null,
372 argument88 in varchar2 default null,
373 argument89 in varchar2 default null,
374 argument90 in varchar2 default null,
375 argument91 in varchar2 default null,
376 argument92 in varchar2 default null,
377 argument93 in varchar2 default null,
378 argument94 in varchar2 default null,
379 argument95 in varchar2 default null,
380 argument96 in varchar2 default null,
381 argument97 in varchar2 default null,
382 argument98 in varchar2 default null,
383 argument99 in varchar2 default null,
384 argument100 in varchar2 default null) is
385
386
387 h_request_id number;
388 h_mesg_str varchar2(2000);
389 h_mesg_name varchar2(50);
390 h_book varchar2(15);
391 h_event_name varchar2(50);
392 h_maint_date_from date;
393 h_maint_date_to date;
394 h_asset_number_from varchar2(15);
395 h_asset_number_to varchar2(15);
396 h_dpis_from date;
397 h_dpis_to date;
398
399 type segs_arr is table of varchar2(30)
400 index by binary_integer;
401 h_catsegs segs_arr;
402 h_concat_segs varchar2(200);
403 h_cat_structure number;
404 h_cat_ccid number;
405 delim varchar2(1);
406
407 prog_failed exception;
408
409 begin
410
411 retcode := 0;
412
413 h_request_id := fnd_global.conc_request_id;
414 -- fnd_profile.get('USER_ID',h_user_id);
415
416 h_book := argument1;
417 h_event_name := argument2;
418 if (argument3 is not null) then
419 h_maint_date_from := to_date(argument3,'YYYY/MM/DD HH24:MI:SS');
420 end if;
421 if (argument4 is not null) then
422 h_maint_date_to := to_date(argument4, 'YYYY/MM/DD HH24:MI:SS');
423 end if;
424 h_asset_number_from := argument5;
425 h_asset_number_to := argument6;
426 if (argument7 is not null) then
427 h_dpis_from := to_date(argument7,'YYYY/MM/DD HH24:MI:SS');
428 end if;
429 if (argument8 is not null) then
430 h_dpis_to := to_date(argument8, 'YYYY/MM/DD HH24:MI:SS');
431 end if;
432
433 /*
434 h_catsegs(1) := argument9;
435 h_catsegs(2) := argument10;
436 h_catsegs(3) := argument11;
437 h_catsegs(4) := argument12;
438 h_catsegs(5) := argument13;
439 */
440
441 if (h_book is NULL) then
442 h_mesg_name := 'FA_ASSET_MAINT_WRONG_PARAM';
443 fnd_message.set_name('OFA',h_mesg_name);
444 h_mesg_str := fnd_message.get;
445 fa_rx_conc_mesg_pkg.log(h_mesg_str);
446 raise prog_failed;
447 end if;
448
449 /*
450 select category_flex_structure
451 into h_cat_structure
452 from fa_system_controls;
453
454 select s.concatenated_segment_delimiter into delim
455 FROM fnd_id_flex_structures s, fnd_application a
456 WHERE s.application_id = a.application_id
457 AND s.id_flex_code = 'CAT#'
458 AND s.id_flex_num = h_cat_structure
459 AND a.application_short_name = 'OFA';
460
461 h_concat_segs := h_catsegs(1);
462 for ctr in 2 .. 5 loop
463 if (h_catsegs(ctr) is not null) then
464 h_concat_segs := h_concat_segs || delim || h_catsegs(ctr);
465 end if;
466 end loop;
467
468
469 if (h_concat_segs is not NULL) then
470 if fnd_flex_keyval.validate_segs (
471 operation => 'CHECK_COMBINATION',
472 appl_short_name => 'OFA',
473 key_flex_code => 'CAT#',
474 structure_number => h_cat_structure,
475 concat_segments => h_concat_segs,
476 values_or_ids => 'V',
477 validation_date =>SYSDATE,
478 displayable => 'ALL',
479 data_set => NULL,
480 vrule => NULL,
481 where_clause => NULL,
482 get_columns => NULL,
483 allow_nulls => FALSE,
484 allow_orphans => FALSE,
485 resp_appl_id => NULL,
486 resp_id => NULL,
487 user_id => NULL) = FALSE then
488
489 fnd_message.set_name('OFA','FA_WHATIF_NO_CAT');
490 fnd_message.set_token('CAT',h_concat_segs,FALSE);
491 h_mesg_str := fnd_message.get;
492 fa_rx_conc_mesg_pkg.log(h_mesg_str);
493 raise prog_failed;
494 end if;
495 h_cat_ccid := fnd_flex_keyval.combination_id;
496 end if;
497 */
498 if (argument10 is not null) then
502 insert_to_itf (
499 h_cat_ccid := to_number(argument10);
500 end if;
501
503 p_book => h_book,
504 p_event_name => h_event_name,
505 p_maint_date_from => h_maint_date_from,
506 p_maint_date_to => h_maint_date_to,
507 p_asset_number_from => h_asset_number_from,
508 p_asset_number_to => h_asset_number_to,
509 p_dpis_from => h_dpis_from,
510 p_dpis_to => h_dpis_to,
511 p_cat_id => h_cat_ccid,
512 p_request_id => h_request_id,
513 retcode => retcode,
514 errbuf => errbuf);
515
516 if (retcode <> 0) then
517 raise prog_failed;
518 end if;
519
520 commit;
521 retcode := 0;
522
523 exception
524 when prog_failed then
525 retcode := 2;
526
527 when others then
528 fnd_message.set_name('OFA', 'FA_SHARED_SERVER_ERROR');
529 h_mesg_str := fnd_message.get;
530 fa_rx_conc_mesg_pkg.log(h_mesg_str);
531 retcode := 2;
532
533
534 end asset_maintenance;
535
536
537 procedure do_insert(
538 p_book in varchar2,
539 p_event_name in varchar2,
540 p_maint_date_from in date,
541 p_maint_date_to in date,
542 p_asset_number_from in varchar2,
543 p_asset_number_to in varchar2,
544 p_dpis_from in date,
545 p_dpis_to in date,
546 p_category_id in varchar2,
547 p_request_id in number,
548 p_retcode out nocopy number) is
549
550
551 h_maint_date_from date;
552 h_maint_date_to date;
553 h_dpis_from date;
554 h_dpis_to date;
555 h_errbuf varchar2(200);
556 h_mesg_name varchar2(50);
557 h_err_msg varchar2(2000);
558 h_cat_id number;
559 prog_failed exception;
560
561 begin
562 p_retcode := 0;
563
564 --h_maint_date_from := fnd_date.canonical_to_date(p_maint_date_from);
565 --h_maint_date_to := fnd_date.canonical_to_date(p_maint_date_to);
566 --h_dpis_from := fnd_date.canonical_to_date(p_dpis_from);
567 --h_dpis_to := fnd_date.canonical_to_date(p_dpis_to);
568 h_maint_date_from := p_maint_date_from;
569 h_maint_date_to := p_maint_date_to;
570 h_dpis_from := p_dpis_from;
571 h_dpis_to := p_dpis_to;
572
573 if (p_book is NULL) then
574 h_mesg_name := 'FA_ASSET_MAINT_WRONG_PARAM';
575 fnd_message.set_name('OFA',h_mesg_name);
576 h_err_msg := fnd_message.get;
577 fa_rx_conc_mesg_pkg.log(h_err_msg);
578 p_retcode := 2;
579 raise prog_failed;
580 end if;
581
582 h_cat_id := to_number(p_category_id);
583
584 insert_to_itf(p_book => p_book,
585 p_event_name => p_event_name,
586 p_maint_date_from => h_maint_date_from,
587 p_maint_date_to => h_maint_date_to,
588 p_asset_number_from => p_asset_number_from,
589 p_asset_number_to => p_asset_number_to,
590 p_dpis_from => h_dpis_from,
591 p_dpis_to => h_dpis_to,
592 p_cat_id => h_cat_id,
593 p_request_id => p_request_id,
594 retcode => p_retcode,
595 errbuf => h_errbuf);
596
597 if (p_retcode <> 0) then
598 raise prog_failed;
599 end if;
600
601 p_retcode := 0;
602 exception
603 when prog_failed then
604 raise;
605
606 when others then
607 raise;
608
609 end do_insert;
610
611 END FARX_C_MT;