[Home] [Help]
PACKAGE BODY: APPS.FARX_INV_MISS_PKG
Source
1 PACKAGE BODY FARX_INV_MISS_PKG as
2 /* $Header: farximb.pls 120.8 2010/05/19 15:07:08 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,
126
123 ccid => c_mainrec.asset_category_id,
124 concat_string => h_concat_cat,
125 segarray => h_cat_segs);
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)
272 )
269 or ( inv1.asset_number is null
270 and inv1.tag_number is null
271 and inv1.serial_number = ad.serial_number)
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 /* Bug 9570207 */
289 )
290 -- End Bug# 7377673
291 )); --Bug#3503643
292
293 c_comparerec c_compare%rowtype;
294 begin
295
296 h_loc_ccid := null;
297 h_cat_ccid := null;
298 h_inventory_name := inventory_name;
299 h_request_id := request_id;
300 h_concat_cat := category;
301 h_concat_loc := location;
302
303 h_mesg_name := 'FA_FE_LOOKUP_IN_SYSTEM_CTLS';
304
305 select location_flex_structure,
306 asset_key_flex_structure,
307 category_flex_structure
308 into h_loc_structure,
309 h_key_structure,
310 h_cat_structure
311 from fa_system_controls;
312
313 select fcr.last_update_login
314 into h_login_id
315 from fnd_concurrent_requests fcr
316 where fcr.request_id = h_request_id;
317
318
319 /* Commenting out validation for cathory and location.
320 When submitted from SRS location and category already have
321 location_ccid and category_ccid
322
323 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
324
325 if category is not null then
326 h_value_error := category;
327 h_param_error := 'CATEGORY';
328 if fnd_flex_keyval.validate_segs (
329 operation => 'CHECK_COMBINATION',
330 appl_short_name => 'OFA',
331 key_flex_code => 'CAT#',
332 structure_number => h_cat_structure,
333 concat_segments => category,
334 values_or_ids => 'V',
335 validation_date =>SYSDATE,
336 displayable => 'ALL',
337 data_set => NULL,
338 vrule => NULL,
339 where_clause => NULL,
340 get_columns => NULL,
341 allow_nulls => FALSE,
342 allow_orphans => FALSE,
343 resp_appl_id => NULL,
344 resp_id => NULL,
345 user_id => NULL) = FALSE then
346
347 fnd_message.set_name('OFA','FA_WHATIF_NO_CAT');
348 fnd_message.set_token('CAT',category,FALSE);
349 h_mesg_str := fnd_message.get;
350 fa_rx_conc_mesg_pkg.log(h_mesg_str);
351
352 retcode := 2;
353 return;
354 end if;
355 h_cat_ccid := fnd_flex_keyval.combination_id;
356 end if;
357
358 if location is not null then
362 if fnd_flex_keyval.validate_segs (
359 h_value_error := location;
360 h_param_error := 'LOCATION';
361
363 operation => 'CHECK_COMBINATION',
364 appl_short_name => 'OFA',
365 key_flex_code => 'LOC#',
366 structure_number => h_loc_structure,
367 concat_segments => location,
368 values_or_ids => 'V',
369 validation_date =>SYSDATE,
370 displayable => 'ALL',
371 data_set => NULL,
372 vrule => NULL,
373 where_clause => NULL,
374 get_columns => NULL,
375 allow_nulls => FALSE,
376 allow_orphans => FALSE,
377 resp_appl_id => NULL,
378 resp_id => NULL,
379 user_id => NULL) = FALSE then
380
381 fnd_message.set_name('OFA','FA_PI_NO_LOCATION');
382 fnd_message.set_token('LOC',location,FALSE);
383 h_mesg_str := fnd_message.get;
384 fa_rx_conc_mesg_pkg.log(h_mesg_str);
385
386 retcode := 2;
387 return;
388 end if;
389 h_loc_ccid := fnd_flex_keyval.combination_id;
390 end if;
391 */
392
393 h_cat_ccid := to_number(category);
394 h_loc_ccid := to_number(location);
395
396 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
397
398 open c_compare;
399 loop
400
401 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
402
403 fetch c_compare into c_comparerec;
404
405 if (c_compare%NOTFOUND) then exit; end if;
406
407 h_mesg_name := 'FA_RX_CONCAT_SEGS';
408 if (c_comparerec.asset_category_id is not null
409 and category is null) then
410
411 h_flex_error := 'CAT#';
412 h_ccid_error := c_comparerec.asset_category_id;
413
414 fa_rx_shared_pkg.concat_category (
415 struct_id => h_cat_structure,
416 ccid => c_comparerec.asset_category_id,
417 concat_string => h_concat_cat,
418 segarray => h_cat_segs);
419
420 elsif (category is not null ) then -- fix bug 3252216.
421
422 fa_rx_shared_pkg.concat_category (
423 struct_id => h_cat_structure,
424 ccid => h_cat_ccid,
425 concat_string => h_concat_cat,
426 segarray => h_cat_segs);
427 else
428 h_concat_cat := '';
429 end if;
430
431 if (c_comparerec.location_id is not null
432 and location is null) then
433
434 h_flex_error := 'LOC#';
435 h_ccid_error := c_comparerec.location_id;
436
437 fa_rx_shared_pkg.concat_location (
438 struct_id => h_loc_structure,
439 ccid => c_comparerec.location_id,
440 concat_string => h_concat_loc,
441 segarray => h_loc_segs);
442
443 elsif (location is not null) then -- fix bug 3252216.
444
445 fa_rx_shared_pkg.concat_location (
446 struct_id => h_loc_structure,
447 ccid => h_loc_ccid,
448 concat_string => h_concat_loc,
449 segarray => h_loc_segs);
450
451 else
452 h_concat_loc := '';
453 end if;
454
455 if (c_comparerec.asset_key_ccid is not null) then
456
457 h_flex_error := 'KEY#';
458 h_ccid_error := c_comparerec.asset_key_ccid;
459
460 fa_rx_shared_pkg.concat_asset_key (
461 struct_id => h_key_structure,
462 ccid => c_comparerec.asset_key_ccid,
463 concat_string => h_concat_key,
467 end if;
464 segarray => h_key_segs);
465 else
466 h_concat_key := '';
468
469
470 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
471
472 insert into fa_inv_compare_rep_itf (
473 REQUEST_ID, INVENTORY_NAME, ASSET_NUMBER, ASSET_KEY, TAG_NUMBER,
474 DESCRIPTION, MODEL_NUMBER, SERIAL_NUMBER, MANUFACTURER_NAME,
475 ASSET_CATEGORY, UNITS, LOCATION, STATUS, UNIT_RECONCILE_MTH_MEAN,
476 LOC_RECONCILE_MTH_MEAN, LAST_UPDATE_DATE, LAST_UPDATED_BY,
477 LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE ) values (
478 h_request_id, c_comparerec.inventory_name,
479 c_comparerec.asset_number, h_concat_key, c_comparerec.tag_number,
480 c_comparerec.description, c_comparerec.model_number,
481 c_comparerec.serial_number, c_comparerec.manufacturer_name,
482 h_concat_cat, c_comparerec.units, h_concat_loc, c_comparerec.status,
483 c_comparerec.unit_reconcile_mth_mean,
484 c_comparerec.loc_reconcile_mth_mean, sysdate, user_id, h_login_id,
485 user_id, sysdate);
486
487
488
489
490 end loop;
491
492 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
493
494 close c_compare;
495
496 exception when others then
497 if SQLCODE <> 0 then
498 fa_Rx_conc_mesg_pkg.log(SQLERRM);
499 end if;
500 fnd_message.set_name('OFA',h_mesg_name);
501 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
502 fnd_message.set_token('TABLE','FA_INV_COMPARE_REP_ITF',FALSE);
503 end if;
504 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
505 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
506 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
507 end if;
508
509 h_mesg_str := fnd_message.get;
510 fa_rx_conc_mesg_pkg.log(h_mesg_str);
511 retcode := 2;
512
513
514 end comparison;
515
516 END FARX_INV_MISS_PKG;