[Home] [Help]
PACKAGE BODY: APPS.FARX_INV_MISS_PKG
Source
1 PACKAGE BODY FARX_INV_MISS_PKG as
2 /* $Header: farximb.pls 120.5.12010000.2 2008/10/24 13:14:37 pmadas ship $ */
3
4 procedure miss_asset(
5 inventory_name in varchar2,
6 request_id in number,
7 user_id in number,
8 retcode out nocopy number,
9 errbuf out nocopy varchar2) is
10
11 h_login_id number;
12 h_asset_number varchar2(25);
13 h_description varchar2(80);
14
15 h_concat_key varchar2(500);
16 h_key_segs fa_rx_shared_pkg.Seg_Array;
17 h_key_structure number;
18
19 h_cat_structure number;
20 h_concat_cat varchar2(500);
21 h_cat_segs fa_rx_shared_pkg.Seg_Array;
22
23 h_concat_loc varchar2(500);
24 h_loc_segs fa_rx_shared_pkg.Seg_Array;
25
26 h_loc_structure number;
27
28 h_inventory_name varchar2(80);
29 h_request_id number;
30 h_segment_num number;
31
32 h_mesg_name varchar2(50);
33 h_mesg_str varchar2(2000);
34 h_flex_error varchar2(5);
35 h_ccid_error number;
36
37
38 Cursor c_main is
39 SELECT inv.inventory_name,
40 ad.asset_number,
41 ad.description,
42 dh.units_assigned,
43 dh.location_id,
44 ad.tag_number,
45 bk.date_placed_in_service,
46 ad.serial_number,
47 ad.manufacturer_name,
48 ad.model_number,
49 ad.asset_category_id,
50 ad.asset_key_ccid
51 FROM fa_books bk,
52 fa_distribution_history dh,
53 fa_inventory inv,
54 fa_additions ad,
55 fa_book_controls_sec bc --Bug#3503643
56 WHERE ad.inventorial = 'YES'
57 AND ad.asset_id = bk.asset_id
58 AND bk.date_ineffective is null
59 AND bk.period_counter_fully_retired is null
60 AND bk.book_type_code = bc.book_type_code
61 AND inv.inventory_name = h_inventory_name
62 AND inv.start_date > bk.date_placed_in_service
63 AND bc.book_class = 'CORPORATE'
64 AND bc.book_type_code = dh.book_type_code
65 AND dh.asset_id = ad.asset_id
66 AND dh.date_ineffective is null
67 AND ad.asset_id not in
68 (
69 select itf.asset_id
70 from fa_inv_interface itf
71 where itf.inventory_name = inv.inventory_name
72 and itf.asset_id = ad.asset_id
73 );
74
75 c_mainrec c_main%rowtype;
76
77 begin
78 --raise no_data_found;
79 h_inventory_name := inventory_name;
80 h_request_id := request_id;
81
82 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
83
84 select location_flex_structure,
85 asset_key_flex_structure,
86 category_flex_structure
87 into h_loc_structure,
88 h_key_structure,
89 h_cat_structure
90 from fa_system_controls;
91
92 select fcr.last_update_login
93 into h_login_id
94 from fnd_concurrent_requests fcr
95 where fcr.request_id = h_request_id;
96
97 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
98
99 open c_main;
100 loop
101
102 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
103
104 fetch c_main into c_mainrec;
105
106 if (c_main%NOTFOUND) then exit; end if;
107
108 h_mesg_name := 'FA_RX_CONCAT_SEGS';
109 h_flex_error := 'LOC#';
110 h_ccid_error := c_mainrec.location_id;
111
112 fa_rx_shared_pkg.concat_location (
113 struct_id => h_loc_structure,
114 ccid => c_mainrec.location_id,
115 concat_string => h_concat_loc,
116 segarray => h_loc_segs);
117
118 h_flex_error := 'CAT#';
119 h_ccid_error := c_mainrec.asset_category_id;
120
121 fa_rx_shared_pkg.concat_category (
122 struct_id => h_cat_structure,
123 ccid => c_mainrec.asset_category_id,
124 concat_string => h_concat_cat,
125 segarray => h_cat_segs);
126
127 if (c_mainrec.asset_key_ccid is not null) then
128
129 h_flex_error := 'KEY#';
130 h_ccid_error := c_mainrec.asset_key_ccid;
131
132 fa_rx_shared_pkg.concat_asset_key (
133 struct_id => h_key_structure,
134 ccid => c_mainrec.asset_key_ccid,
135 concat_string => h_concat_key,
136 segarray => h_key_segs);
137
138 end if;
139
140 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
141
142 insert into fa_invmiss_rep_itf (
143 request_id,
144 location,
145 category,
146 asset_key,
147 inventory_name,
148 asset_number,
149 description,
150 units_assigned,
151 serial_number,
152 tag_number,
153 manufacturer_name,
154 model_number,
155 created_by, creation_date, last_updated_by, last_update_date,
156 last_update_login)
157 values (
158 h_request_id,
159 h_concat_loc,
160 h_concat_cat,
161 h_concat_key,
162 c_mainrec.inventory_name,
163 c_mainrec.asset_number,
164 c_mainrec.description,
165 c_mainrec.units_assigned,
166 c_mainrec.serial_number,
167 c_mainrec.tag_number,
168 c_mainrec.manufacturer_name,
169 c_mainrec.model_number,
170 user_id, sysdate, user_id , sysdate, h_login_id);
171
172 end loop;
173
174 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
175
176 close c_main;
177
178 exception when others then
179 if SQLCODE <> 0 then
180 fa_Rx_conc_mesg_pkg.log(SQLERRM);
181 end if;
182 fnd_message.set_name('OFA',h_mesg_name);
183 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
184 fnd_message.set_token('TABLE','FA_INVMISS_REP_ITF',FALSE);
185 end if;
186 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
187 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
188 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
189 end if;
190
191 h_mesg_str := fnd_message.get;
192 fa_rx_conc_mesg_pkg.log(h_mesg_str);
193 retcode := 2;
194
195 end miss_asset;
196
197
198 procedure comparison (
199 inventory_name in varchar2,
200 location in varchar2,
201 category in varchar2,
202 request_id in number,
203 user_id in number,
204 retcode out nocopy number,
205 errbuf out nocopy varchar2) is
206
207 h_login_id number;
208 h_asset_number varchar2(25);
209 h_description varchar2(80);
210
211 h_concat_key varchar2(500);
212 h_key_segs fa_rx_shared_pkg.Seg_Array;
213 h_key_structure number;
214 h_key_ccid number;
215
216 h_cat_structure number;
217 h_concat_cat varchar2(500);
218 h_cat_segs fa_rx_shared_pkg.Seg_Array;
219 h_cat_ccid number;
220
221 h_concat_loc varchar2(500);
222 h_loc_segs fa_rx_shared_pkg.Seg_Array;
223 h_loc_structure number;
224 h_loc_ccid number;
225
226 h_inventory_name varchar2(80);
227 h_request_id number;
228 h_segment_num number;
229
230 h_mesg_name varchar2(50);
231 h_mesg_str varchar2(2000);
232 h_flex_error varchar2(5);
233 h_ccid_error number;
234 h_value_error varchar2(240);
235 h_param_error varchar2(240);
236
237 cursor c_compare is
238 select inv.inventory_name, inv.asset_number, inv.asset_key_ccid,
239 inv.tag_number, inv.description, inv.model_number, inv.serial_number,
240 inv.manufacturer_name, inv.asset_category_id, inv.units,
241 inv.location_id, lu1.meaning status,
242 lu2.meaning unit_reconcile_mth_mean,
243 lu3.meaning loc_reconcile_mth_mean
244 from fa_inv_interface inv, fa_lookups lu1, fa_lookups lu2, fa_lookups lu3
245 where nvl(inv.inventory_name,'X') = nvl(h_inventory_name,
246 nvl(inv.inventory_name,'X'))
247 and nvl(inv.asset_category_id,-9999) = nvl(h_cat_ccid,
248 nvl(inv.asset_category_id,-9999))
249 and nvl(inv.location_id,-9999) = nvl(h_loc_ccid,
250 nvl(inv.location_id,-9999))
251 and inv.status = lu1.lookup_code(+)
252 and lu1.lookup_type(+) = 'INVENTORY STATUS'
253 and inv.unit_reconcile_mth = lu2.lookup_code(+)
254 and lu2.lookup_type(+) like 'INVENTORY UNIT METHOD%'
255 and inv.loc_reconcile_mth = lu3.lookup_code(+)
256 and lu3.lookup_type(+) like 'INVENTORY LOCATION METHOD%'
257 -- Bug# 7377673
258 and nvl(nvl(inv.asset_number, inv.tag_number), inv.serial_number) in
259 (select nvl(nvl(inv1.asset_number, inv1.tag_number), inv1.serial_number)
260 -- End Bug# 7377673
261 from fa_inv_interface inv1,
262 fa_additions_b ad,
263 fa_books bks,
264 fa_book_controls_sec bc
265 -- Bug# 7377673
266 where ( (inv1.asset_number = ad.asset_number)
267 or ( inv1.asset_number is null
268 and inv1.tag_number = ad.tag_number)
269 or ( inv1.asset_number is null
270 and inv1.tag_number is null
271 and inv1.serial_number = ad.serial_number)
272 )
273 -- End Bug# 7377673
274 and ad.asset_id = bks.asset_id
275 and bks.transaction_header_id_out is null
276 and bks.book_type_code = bc.book_type_code
277 union
278 -- Bug# 7377673
279 select nvl(nvl(inv2.asset_number, inv2.tag_number), inv2.serial_number)
280 -- End Bug# 7377673
281 from fa_inv_interface inv2
282 where not exists
283 (select 'x'
284 from fa_additions_b ad
285 -- Bug# 7377673
286 where ( ad.asset_number = inv2.asset_number
287 or ad.tag_number = inv2.tag_number
288 or ad.serial_number = inv2.serial_number)
289 -- End Bug# 7377673
290 )); --Bug#3503643
291
292 c_comparerec c_compare%rowtype;
293 begin
294
295 h_loc_ccid := null;
296 h_cat_ccid := null;
297 h_inventory_name := inventory_name;
298 h_request_id := request_id;
299 h_concat_cat := category;
300 h_concat_loc := location;
301
302 h_mesg_name := 'FA_FE_LOOKUP_IN_SYSTEM_CTLS';
303
304 select location_flex_structure,
305 asset_key_flex_structure,
306 category_flex_structure
307 into h_loc_structure,
308 h_key_structure,
309 h_cat_structure
310 from fa_system_controls;
311
312 select fcr.last_update_login
313 into h_login_id
314 from fnd_concurrent_requests fcr
315 where fcr.request_id = h_request_id;
316
317
318 /* Commenting out validation for cathory and location.
319 When submitted from SRS location and category already have
320 location_ccid and category_ccid
321
322 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
323
324 if category is not null then
325 h_value_error := category;
326 h_param_error := 'CATEGORY';
327 if fnd_flex_keyval.validate_segs (
328 operation => 'CHECK_COMBINATION',
329 appl_short_name => 'OFA',
330 key_flex_code => 'CAT#',
331 structure_number => h_cat_structure,
332 concat_segments => category,
333 values_or_ids => 'V',
334 validation_date =>SYSDATE,
335 displayable => 'ALL',
336 data_set => NULL,
337 vrule => NULL,
338 where_clause => NULL,
339 get_columns => NULL,
340 allow_nulls => FALSE,
341 allow_orphans => FALSE,
342 resp_appl_id => NULL,
343 resp_id => NULL,
344 user_id => NULL) = FALSE then
345
346 fnd_message.set_name('OFA','FA_WHATIF_NO_CAT');
347 fnd_message.set_token('CAT',category,FALSE);
348 h_mesg_str := fnd_message.get;
349 fa_rx_conc_mesg_pkg.log(h_mesg_str);
350
351 retcode := 2;
352 return;
353 end if;
354 h_cat_ccid := fnd_flex_keyval.combination_id;
355 end if;
356
357 if location is not null then
358 h_value_error := location;
359 h_param_error := 'LOCATION';
360
361 if fnd_flex_keyval.validate_segs (
362 operation => 'CHECK_COMBINATION',
363 appl_short_name => 'OFA',
364 key_flex_code => 'LOC#',
365 structure_number => h_loc_structure,
366 concat_segments => location,
367 values_or_ids => 'V',
368 validation_date =>SYSDATE,
369 displayable => 'ALL',
370 data_set => NULL,
371 vrule => NULL,
372 where_clause => NULL,
373 get_columns => NULL,
374 allow_nulls => FALSE,
375 allow_orphans => FALSE,
376 resp_appl_id => NULL,
377 resp_id => NULL,
378 user_id => NULL) = FALSE then
379
380 fnd_message.set_name('OFA','FA_PI_NO_LOCATION');
381 fnd_message.set_token('LOC',location,FALSE);
382 h_mesg_str := fnd_message.get;
383 fa_rx_conc_mesg_pkg.log(h_mesg_str);
384
385 retcode := 2;
386 return;
387 end if;
388 h_loc_ccid := fnd_flex_keyval.combination_id;
389 end if;
390 */
391
392 h_cat_ccid := to_number(category);
393 h_loc_ccid := to_number(location);
394
395 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
396
397 open c_compare;
398 loop
399
400 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
401
402 fetch c_compare into c_comparerec;
403
404 if (c_compare%NOTFOUND) then exit; end if;
405
406 h_mesg_name := 'FA_RX_CONCAT_SEGS';
407 if (c_comparerec.asset_category_id is not null
408 and category is null) then
409
410 h_flex_error := 'CAT#';
411 h_ccid_error := c_comparerec.asset_category_id;
412
413 fa_rx_shared_pkg.concat_category (
414 struct_id => h_cat_structure,
415 ccid => c_comparerec.asset_category_id,
416 concat_string => h_concat_cat,
417 segarray => h_cat_segs);
418
419 elsif (category is not null ) then -- fix bug 3252216.
420
421 fa_rx_shared_pkg.concat_category (
422 struct_id => h_cat_structure,
423 ccid => h_cat_ccid,
424 concat_string => h_concat_cat,
425 segarray => h_cat_segs);
426 else
430 if (c_comparerec.location_id is not null
427 h_concat_cat := '';
428 end if;
429
431 and location is null) then
432
433 h_flex_error := 'LOC#';
434 h_ccid_error := c_comparerec.location_id;
435
436 fa_rx_shared_pkg.concat_location (
437 struct_id => h_loc_structure,
438 ccid => c_comparerec.location_id,
439 concat_string => h_concat_loc,
440 segarray => h_loc_segs);
441
442 elsif (location is not null) then -- fix bug 3252216.
443
444 fa_rx_shared_pkg.concat_location (
445 struct_id => h_loc_structure,
446 ccid => h_loc_ccid,
447 concat_string => h_concat_loc,
448 segarray => h_loc_segs);
449
450 else
451 h_concat_loc := '';
452 end if;
453
454 if (c_comparerec.asset_key_ccid is not null) then
455
456 h_flex_error := 'KEY#';
457 h_ccid_error := c_comparerec.asset_key_ccid;
458
459 fa_rx_shared_pkg.concat_asset_key (
460 struct_id => h_key_structure,
461 ccid => c_comparerec.asset_key_ccid,
462 concat_string => h_concat_key,
463 segarray => h_key_segs);
464 else
465 h_concat_key := '';
466 end if;
467
468
469 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
470
471 insert into fa_inv_compare_rep_itf (
472 REQUEST_ID, INVENTORY_NAME, ASSET_NUMBER, ASSET_KEY, TAG_NUMBER,
473 DESCRIPTION, MODEL_NUMBER, SERIAL_NUMBER, MANUFACTURER_NAME,
474 ASSET_CATEGORY, UNITS, LOCATION, STATUS, UNIT_RECONCILE_MTH_MEAN,
475 LOC_RECONCILE_MTH_MEAN, LAST_UPDATE_DATE, LAST_UPDATED_BY,
476 LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE ) values (
477 h_request_id, c_comparerec.inventory_name,
478 c_comparerec.asset_number, h_concat_key, c_comparerec.tag_number,
479 c_comparerec.description, c_comparerec.model_number,
480 c_comparerec.serial_number, c_comparerec.manufacturer_name,
481 h_concat_cat, c_comparerec.units, h_concat_loc, c_comparerec.status,
482 c_comparerec.unit_reconcile_mth_mean,
483 c_comparerec.loc_reconcile_mth_mean, sysdate, user_id, h_login_id,
484 user_id, sysdate);
485
486
487
488
489 end loop;
490
491 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
492
493 close c_compare;
494
495 exception when others then
496 if SQLCODE <> 0 then
497 fa_Rx_conc_mesg_pkg.log(SQLERRM);
498 end if;
499 fnd_message.set_name('OFA',h_mesg_name);
500 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
501 fnd_message.set_token('TABLE','FA_INV_COMPARE_REP_ITF',FALSE);
502 end if;
503 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
504 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
505 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
506 end if;
507
508 h_mesg_str := fnd_message.get;
509 fa_rx_conc_mesg_pkg.log(h_mesg_str);
510 retcode := 2;
511
512
513 end comparison;
514
515 END FARX_INV_MISS_PKG;