[Home] [Help]
PACKAGE BODY: APPS.WIP_EAM_GENEALOGY_PVT
Source
1 PACKAGE BODY WIP_EAM_GENEALOGY_PVT AS
2 /* $Header: WIPVEGNB.pls 120.8 2006/06/08 10:12:46 ksiddhar ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WIP_EAM_GENEALOGY_PVT';
5
6 PROCEDURE create_eam_genealogy(
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_subinventory IN VARCHAR2 := NULL,
12 p_locator_id IN NUMBER := NULL,
13 p_object_id IN NUMBER := NULL,
14 p_serial_number IN VARCHAR2 := NULL,
15 p_organization_id IN NUMBER := NULL,
16 p_inventory_item_id IN NUMBER := NULL,
17 p_parent_object_id IN NUMBER := NULL,
18 p_parent_serial_number IN VARCHAR2 := NULL,
19 p_parent_inventory_item_id IN NUMBER := NULL,
20 p_parent_organization_id IN NUMBER := NULL,
21 p_start_date_active IN DATE := SYSDATE,
22 p_end_date_active IN DATE := NULL,
23 p_origin_txn_id IN NUMBER := NULL,
24 p_update_txn_id IN NUMBER := NULL,
25 p_from_eam IN VARCHAR2 := NULL,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 x_return_status OUT NOCOPY VARCHAR2) is
29
30 l_api_name CONSTANT VARCHAR(30) := 'CREATE_EAM_GENEALOGY';
31 l_module constant varchar2(200) := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
32
33 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
34 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND fnd_log.test(fnd_log.level_unexpected, l_module);
35 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
36 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
37
38 /*
39 l_log boolean := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
40 l_plog boolean ; -- := l_log and FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, l_module);
41 l_slog boolean := l_plog and FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, l_module);
42 */
43
44 l_errCode number;
45 l_msg_count number;
46 l_msg_data varchar2(100);
47 l_return_status varchar2(5);
48 l_statement number;
49 l_txn_id number;
50 l_update_txn_id number;
51 l_current_status number;
52 l_revision varchar2(3);
53 l_dist_acct_id number;
54 l_serial_number varchar2(30);
55 l_inventory_item_id number;
56 l_parent_inventory_item_id number;
57 l_subinventory varchar2(30);
58 l_locator_id number;
59 l_organization_id number;
60 l_parent_organization_id number;
61 l_serial_control number;
62 l_api_version number := 1.0;
63 l_parent_object_id number;
64 l_parent_serial_number varchar2(30) := null;
65 l_charge_object_id number;
66 l_wms_installed varchar2(10);
67
68 /* R12 Hook for Asset Log #4141712*/
69 l_maint_orgid number;
70 l_event_type varchar2(30) := 'EAM_SYSTEM_EVENTS';
71 l_parent_instance_id number;
72 l_instance_id number;
73 l_reference varchar2(30);
74 l_parent_instance_number varchar2(30);
75
76
77 begin
78 -- l_plog := ( (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) AND l_log );
79 savepoint eam_rebuild_genealogy;
80
81 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
82 p_api_version,
83 l_api_name,
84 g_pkg_name)
85 THEN
86
87 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
88
89 END IF;
90
91 IF FND_API.TO_BOOLEAN(p_init_msg_list)
92 THEN
93
94 FND_MSG_PUB.initialize;
95
96 END IF;
97
98 -- Initialize API return status to success
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100 l_return_status := FND_API.G_RET_STS_SUCCESS;
101
102
103 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
104 'Start of ' || l_module || '('
105 || 'p_organization_id='|| p_organization_id || ','
106 || 'p_subinventory='|| p_subinventory || ','
107 || 'p_locator_id='|| p_locator_id || ','
108 || 'p_object_id='|| p_object_id || ','
109 || 'p_serial_number='|| p_serial_number ||','
110 || 'p_inventory_item_id='|| p_inventory_item_id || ','
111 || 'p_parent_object_id='|| p_parent_object_id || ','
112 || 'p_parent_serial_number='|| p_parent_serial_number || ','
113 || 'p_parent_inventory_item_id='|| p_parent_inventory_item_id ||','
114 || 'p_parent_organization_id='|| p_parent_organization_id || ','
115 || 'p_start_date_active='|| p_start_date_active || ','
116 || 'p_end_date_active='|| p_end_date_active ||','
117 || 'p_from_eam='|| p_from_eam || ','
118 || 'p_commit='|| p_commit
119 || ')');
120 end if;
121
122 l_txn_id := p_origin_txn_id;
123 l_update_txn_id := p_update_txn_id;
124
125 -- return without error if item is not serial controlled when
126 -- the genealogy is not originating from eam
127 if (((p_from_eam is null) or (p_from_eam = FND_API.G_FALSE))
128 and ((p_object_id is null) and (p_serial_number is null))) then
129 select serial_number_control_code into l_serial_control
130 from mtl_system_items
131 where inventory_item_id = p_inventory_item_id and
132 organization_id = p_organization_id;
133
134 if (l_serial_control = 1) then
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136 return;
137 end if;
138 end if;
139
140
141 -- obtain rebuildable information
142 if p_object_id is null then
143
144 if p_serial_number is null or p_inventory_item_id is null or p_organization_id is null then
145 -- if serial_number is null, then quit processing
146 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ITEM_DOES_NOT_EXIST');
147 FND_MSG_PUB.ADD;
148 RAISE FND_API.G_EXC_ERROR;
149 else
150 l_serial_number := p_serial_number;
151 l_inventory_item_id := p_inventory_item_id;
152 select current_organization_id into l_organization_id
153 from mtl_serial_numbers
154 where serial_number = l_serial_number
155 and inventory_item_id = l_inventory_item_id;
156 -- l_organization_id := p_organization_id;
157
158 end if;
159 else
160 if (p_serial_number is null) or (p_inventory_item_id is null) or (p_organization_id is null) then
161 begin
162 select serial_number, inventory_item_id, current_organization_id
163 into l_serial_number, l_inventory_item_id, l_organization_id
164 from mtl_serial_numbers
165 where gen_object_id = p_object_id;
166 exception
167 when others then
168 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ITEM_DOES_NOT_EXIST');
169 FND_MSG_PUB.ADD;
170 RAISE FND_API.G_EXC_ERROR;
171 end;
172 else
173 l_serial_number := p_serial_number;
174 l_inventory_item_id := p_inventory_item_id;
175 select current_organization_id into l_organization_id
176 from mtl_serial_numbers
177 where gen_object_id = p_object_id;
178 --l_organization_id := p_organization_id;
179 end if;
180
181 end if;
182
183 -- obtain parent object id information
184 if p_parent_object_id is null then
185 if p_parent_serial_number is null or p_parent_inventory_item_id is null or p_parent_organization_id is null then
186 -- if there is no parent information, then quit processing
187 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_INVALID_PARENT_ITEM');
188 FND_MSG_PUB.ADD;
189 RAISE FND_API.G_EXC_ERROR;
190
191 else
192 begin
193
194 l_parent_serial_number := p_parent_serial_number;
195 l_parent_inventory_item_id := p_parent_inventory_item_id;
196
197 select gen_object_id, current_organization_id
198 into l_parent_object_id, l_parent_organization_id
199 from mtl_serial_numbers
200 where serial_number = l_parent_serial_number
201 and inventory_item_id = p_parent_inventory_item_id;
202 exception
203 when others then
204 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_INVALID_PARENT_ITEM');
205 FND_MSG_PUB.ADD;
206 RAISE FND_API.G_EXC_ERROR;
207 end;
208 end if;
209
210 else
211 l_parent_object_id := p_parent_object_id;
212 select current_organization_id,serial_number,inventory_item_id
213 into
214 l_parent_organization_id,
215 l_parent_serial_number,
216 l_parent_inventory_item_id
217 from mtl_serial_numbers
218 where gen_object_id = l_parent_object_id;
219
220 end if;
221
222 -- check if there is an open work order against the rebuild item. If the parent does not correspond to the charge asset in the work order, there is an error. If the charge asset is null, this is not a problem.
223
224 begin
225 select msn.gen_object_id
226 into l_charge_object_id
227 from wip_discrete_jobs wdj, mtl_serial_numbers msn
228 where wdj.rebuild_serial_number = l_serial_number
229 and wdj.rebuild_item_id = l_inventory_item_id
230 and wdj.organization_id = l_organization_id
231 and msn.serial_number = wdj.asset_number
232 and msn.inventory_item_id = wdj.asset_group_id
233 and msn.current_organization_id = wdj.organization_id
234 and wdj.manual_rebuild_flag = 'N'
235 and wdj.status_type in (1,3,6);
236
237 if l_charge_object_id <> l_parent_object_id then
238 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_WO_WITH_CHARGE_ASSET');
239 FND_MSG_PUB.ADD;
240 RAISE FND_API.G_EXC_ERROR;
241 end if;
242
243 exception
244 when others then
245 null;
246 end;
247
248 -- perform genealogy creation
249
250 FND_MSG_PUB.initialize;
251
252 inv_genealogy_pub.insert_genealogy(
253 p_api_version => l_api_version,
254 p_object_type => 2,
255 p_parent_object_type => 2,
256 p_object_number => l_serial_number,
257 p_inventory_item_id => l_inventory_item_id,
258 p_org_id => l_organization_id,
259 p_parent_object_id => l_parent_object_id,
260 p_parent_object_number => l_parent_serial_number,
261 p_parent_inventory_item_id => l_parent_inventory_item_id,
262 p_parent_org_id => l_parent_organization_id,
263 p_genealogy_origin => 3,
264 p_genealogy_type => 5,
265 p_start_date_active => p_start_date_active,
266 p_end_date_active => p_end_date_active,
267 p_origin_txn_id => null,
268 p_update_txn_id => null,
269 x_return_status => l_return_status,
270 x_msg_count => x_msg_count,
271 x_msg_data => x_msg_data);
272
273 if (l_return_status = 'E') then
274 raise FND_API.G_EXC_ERROR;
275 elsif (l_return_status = 'U') then
276 raise FND_API.G_EXC_UNEXPECTED_ERROR;
277 end if;
278
279 /* R12 Hook for Asset Log #4141712 Begin */
280
281 --Add child Event - 15
282
283 begin
284
285 SELECT cii.instance_id, mp.maint_organization_id,cii.instance_number
286 INTO l_parent_instance_id , l_maint_orgid,l_parent_instance_number
287 FROM csi_item_instances cii, mtl_parameters mp
288 WHERE cii.serial_number = l_parent_serial_number
289 AND cii.inventory_item_id = l_parent_inventory_item_id
290 AND cii.last_vld_organization_id = mp.organization_id
291 AND cii.last_vld_organization_id= l_organization_id ;
292
293 SELECT cii.instance_number, cii.instance_id
294 INTO l_reference, l_instance_id
295 FROM csi_item_instances cii
296 WHERE cii.serial_number = l_serial_number
297 AND cii.inventory_item_id = l_inventory_item_id
298 AND cii.last_vld_organization_id = l_organization_id;
299
300 eam_asset_log_pvt.insert_row(
301 p_event_date => p_start_date_active,
302 p_event_type => l_event_type,
303 p_event_id => 15,
304 p_organization_id => l_maint_orgid,
305 p_instance_id => l_parent_instance_id,
306 p_reference => l_reference,
307 p_ref_id => l_instance_id,
308 p_instance_number => l_parent_instance_number,
309 x_return_status => l_return_status,
310 x_msg_count => x_msg_count,
311 x_msg_data => x_msg_data
312 );
313
314 eam_asset_log_pvt.insert_row(
315 p_event_date => p_start_date_active,
316 p_event_type => l_event_type,
317 p_event_id => 14,
318 p_organization_id => l_maint_orgid,
319 p_instance_id => l_instance_id,
320 p_reference => l_parent_instance_number,
321 p_ref_id => l_parent_instance_id,
322 p_instance_number => l_reference,
323 x_return_status => l_return_status,
324 x_msg_count => x_msg_count,
325 x_msg_data => x_msg_data
326 );
327
328
329 exception
330 WHEN NO_DATA_FOUND THEN
331 null;
332 end;
333
334 /* R12 Hook for Asset Log #4141712 End */
335
336
337 if p_commit = FND_API.G_TRUE then
338 commit;
339 end if;
340
341 exception
342 WHEN FND_API.G_EXC_ERROR THEN
343 ROLLBACK TO eam_rebuild_genealogy;
344
345 x_return_status := FND_API.G_RET_STS_ERROR;
346
347 FND_MSG_PUB.Count_And_Get
348 ( p_encoded => FND_API.G_FALSE,
349 p_count => x_msg_count,
350 p_data => x_msg_data
351 );
352
353 WHEN OTHERS THEN
354 ROLLBACK TO eam_rebuild_genealogy;
355
356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
357
358 FND_MSG_PUB.Count_And_Get
359 ( p_encoded => FND_API.G_FALSE,
360 p_count => x_msg_count,
361 p_data => x_msg_data
362 );
363
364 END CREATE_EAM_GENEALOGY;
365
366
367
368 PROCEDURE update_eam_genealogy(
369 p_api_version IN NUMBER,
370 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
371 p_commit IN VARCHAR2 := FND_API.G_FALSE,
372 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
373 p_object_type IN NUMBER,
374 p_object_id IN NUMBER := NULL,
375 p_serial_number IN VARCHAR2 := NULL,
379 p_locator_id IN NUMBER := NULL,
376 p_inventory_item_id IN NUMBER := NULL,
377 p_organization_id IN NUMBER := NULL,
378 p_subinventory IN VARCHAR2 := NULL,
380 p_genealogy_origin IN NUMBER := NULL,
381 p_genealogy_type IN NUMBER := NULL,
382 p_end_date_active IN DATE := NULL,
383 p_from_eam IN VARCHAR2 := NULL,
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_msg_count OUT NOCOPY NUMBER,
386 x_msg_data OUT NOCOPY VARCHAR2) is
387
388 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_EAM_GENEALOGY';
389 l_module constant varchar2(200) := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
390
391 /*
392 l_log boolean := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
393 l_plog boolean := l_log and FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, l_module);
394 l_slog boolean := l_plog and FND_LOG.TEST(FND_LOG.LEVEL_STATEMENT, l_module);
395 */
396
397 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
398 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND fnd_log.test(fnd_log.level_unexpected, l_module);
399 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
400 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
401
402 l_errCode number;
403 l_msg_count number;
404 l_msg_data varchar2(100);
405 l_return_status varchar2(5);
406 l_statement number;
407 l_txn_id number := null;
408 l_dist_acct_id number;
409 l_api_version number := 1.0;
410 l_serial_number varchar2(30);
411 l_subinventory varchar2(30);
412 l_locator_id number;
413 l_inventory_item_id number;
414 l_revision varchar2(3);
415 l_object_id number;
416 l_organization_id number;
417 l_txn_type number;
418 l_txn_action_id number;
419 l_txn_source_type number;
420 l_wip_entity_id number;
421 l_work_status number;
422 l_original_txn_id number;
423 l_wms_installed varchar2(10);
424 l_sub_code number;
425 l_dummy number;
426
427 /* R12 Hook for Asset Log #4141712 To get Parent Object Id Begin*/
428 l_maint_orgid number;
429 l_event_type varchar2(30) := 'EAM_SYSTEM_EVENTS';
430 l_parent_instance_id number;
431 l_instance_id number;
432 l_reference varchar2(30);
433 l_parent_object_id number;
434 l_parent_instance_number varchar2(30);
435
436
437 begin
438
439 savepoint eam_rebuild_genealogy;
440
441 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
442 p_api_version,
443 l_api_name,
444 g_pkg_name)
445 THEN
446
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448
449 END IF;
450
451
452 IF FND_API.TO_BOOLEAN(p_init_msg_list)
453 THEN
454
455 FND_MSG_PUB.initialize;
456
457 END IF;
458
459 -- Initialize API return status to success
460 x_return_status := FND_API.G_RET_STS_SUCCESS;
461 l_return_status := FND_API.G_RET_STS_SUCCESS;
462
463 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
464 'Start of ' || l_module || '('
465 || 'p_organization_id='|| p_organization_id || ','
466 || 'p_subinventory='|| p_subinventory || ','
467 || 'p_locator_id='|| p_locator_id || ','
468 || 'p_object_type='|| p_object_type || ','
469 || 'p_object_id='|| p_object_id || ','
470 || 'p_serial_number='|| p_serial_number ||','
471 || 'p_inventory_item_id='|| p_inventory_item_id || ','
472 || 'p_end_date_active='|| p_end_date_active ||','
473 || 'p_from_eam='|| p_from_eam || ','
474 || 'p_commit='|| p_commit
475 || ')');
476 end if;
477
478
479 -- return without error if serial number is not specified when
480 -- the genealogy is not originating from eam
481 if (((p_from_eam is null) or (p_from_eam = FND_API.G_FALSE))
482 and ((p_serial_number is null) and (p_object_id is null))) then
483 x_return_status := FND_API.G_RET_STS_SUCCESS;
484 return;
485 end if;
486
487 -- obtain rebuildable information
488 if p_object_id is null then
489
490 if p_serial_number is null
491 or p_inventory_item_id is null
492 or p_organization_id is null then
493
494 -- if serial_number is null, then quit processing
495 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ITEM_DOES_NOT_EXIST');
496 FND_MSG_PUB.ADD;
497 RAISE FND_API.G_EXC_ERROR;
498 else
499 l_serial_number := p_serial_number;
500 l_inventory_item_id := p_inventory_item_id;
501 --l_organization_id := p_organization_id;
502
503
504 begin
505 select gen_object_id,current_organization_id into l_object_id, l_organization_id
506 from mtl_serial_numbers
510 exception
507 where serial_number = l_serial_number
508 and inventory_item_id = p_inventory_item_id;
509
511 when others then
512 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ITEM_DOES_NOT_EXIST');
513 FND_MSG_PUB.ADD;
514 RAISE FND_API.G_EXC_ERROR;
515 end;
516 end if;
517
518 else
519
520 if (p_serial_number is null)
521 or (p_inventory_item_id is null)
522 or (p_organization_id is null) then
523
524 begin
525 select serial_number, inventory_item_id, current_organization_id
526 into l_serial_number, l_inventory_item_id, l_organization_id
527 from mtl_serial_numbers where
528 gen_object_id = p_object_id;
529
530 exception
531 when others then
532 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ITEM_DOES_NOT_EXIST');
533 FND_MSG_PUB.ADD;
534 RAISE FND_API.G_EXC_ERROR;
535 end;
536
537 else
538 l_serial_number := p_serial_number;
539 l_inventory_item_id := p_inventory_item_id;
540 --l_organization_id := p_organization_id;
541 select current_organization_id into l_organization_id
542 from mtl_serial_numbers
543 where gen_object_id = p_object_id;
544
545 end if;
546
547 l_object_id := p_object_id;
548
549 end if;
550
551
552 -- perform misc inventory transaction if p_from_eam is set to TRUE.
553 if FND_API.to_Boolean( p_from_eam ) and (p_subinventory is not null) THEN
554
555
556 l_subinventory := p_subinventory;
557
558 IF inv_install.adv_inv_installed(NULL) THEN
559 l_wms_installed := 'TRUE';
560 ELSE
561 l_wms_installed := 'FALSE';
562 END IF;
563
564 if inv_material_status_grp.is_status_applicable(
565 l_wms_installed,
566 NULL,
567 42,
568 NULL,
569 NULL,
570 l_organization_id,
571 l_inventory_item_id,
572 l_subinventory,
573 NULL,
574 NULL,
575 NULL,
576 'Z') <> 'Y' then
577 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_INVALID_SUBINVENTORY');
578 FND_MSG_PUB.ADD;
579 RAISE FND_API.G_EXC_ERROR;
580 end if;
581
582 select restrict_subinventories_code
583 into l_sub_code
584 from mtl_system_items
585 where inventory_item_id = l_inventory_item_id
586 and organization_id = l_organization_id;
587
588 if l_sub_code = 1 then
589
590 l_dummy := 1;
591
592 begin
593 select 10 into l_dummy from dual
594 where exists
595 (select *
596 from mtl_item_sub_inventories
597 where inventory_item_id = l_inventory_item_id
598 and organization_id = l_organization_id
599 and secondary_inventory = l_subinventory);
600 exception
601 when others then
602 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_INVALID_SUBINVENTORY');
603 FND_MSG_PUB.ADD;
604 RAISE FND_API.G_EXC_ERROR;
605 end;
606
607 if l_dummy <> 10 then
608 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_INVALID_SUBINVENTORY');
609 FND_MSG_PUB.ADD;
610 RAISE FND_API.G_EXC_ERROR;
611 end if;
612
613 end if;
614
615
616 if p_locator_id is null then
617 begin
618 select current_locator_id into l_locator_id
619 from mtl_serial_numbers
620 where serial_number = l_serial_number and
621 inventory_item_id = l_inventory_item_id and
622 current_organization_id = l_organization_id;
623
624 exception
625 when others then
626 null;
627 end;
628 else
629 l_locator_id := p_locator_id;
630 end if;
631
632
633 -- genealogy update in future is not allowed
634 -- This restriction has been put in place because eventhough the date
635 -- of transaction is future, the status of the item is changed immediately
636
637 if p_end_date_active is not null and p_end_date_active > sysdate then
638 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_REBUILD_FUTURE_TXN');
639 FND_MSG_PUB.ADD;
640 RAISE FND_API.G_EXC_ERROR;
641 else
642
643 -- obtain the offset account id from user-defined EAM parameters
644 select maintenance_offset_account into l_dist_acct_id
645 from wip_eam_parameters
646 where organization_id = l_organization_id;
647
648 if l_dist_acct_id is null then
649 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_NO_OFFSET_ACCOUNT');
650 FND_MSG_PUB.ADD;
651 RAISE FND_API.G_EXC_ERROR;
652 end if;
653
654 --obtain the revision of the object from MSN
655 select revision into l_revision
656 from mtl_serial_numbers
657 where serial_number = l_serial_number and
658 inventory_item_id = l_inventory_item_id and
659 current_organization_id = l_organization_id;
660
661 -- call the Transaction processing API
662 -- if API returns success, call the inventory genealogy API
663 wip_eam_transactions_pvt.process_eam_txn(
664 p_subinventory => l_subinventory,
665 p_serial_number => l_serial_number,
666 p_locator_id => l_locator_id,
667 p_organization_id => l_organization_id,
668 p_inventory_item_id => l_inventory_item_id,
669 p_dist_acct_id => l_dist_acct_id,
670 p_transaction_type_id => 42,
671 p_transaction_quantity => 1,
672 p_transaction_action_id => 27,
673 p_transaction_source_type_id => 13,
674 p_revision => l_revision,
675 x_errCode => l_errCode,
676 x_msg_count => x_msg_count,
677 x_msg_data => x_msg_data,
678 x_return_status => l_return_status,
679 x_statement => l_statement);
680
681 select last_transaction_id
682 into l_txn_id
683 from mtl_serial_numbers
684 where serial_number = l_serial_number
685 and current_organization_id = l_organization_id and
686 inventory_item_id = l_inventory_item_id;
687
688 if l_txn_id is null then
692 end if;
689 RAISE FND_API.G_EXC_ERROR;
690 end if;
691
693
694 end if;
695
696
697 -- perform genealogy update
698
699 if l_return_status = 'S' then
700
701 /* R12 Hook for Asset Log #4141712 To get Parent Object Id Begin*/
702 SELECT parent_object_id INTO l_parent_object_id
703 FROM mtl_object_genealogy
704 WHERE genealogy_type = 5
705 AND object_id = l_object_id
706 AND end_date_active IS NULL;
707 /* R12 Hook for Asset Log #4141712 To get Parent Object Id End*/
708
709 FND_MSG_PUB.initialize;
710
711 inv_genealogy_pub.update_genealogy(
712 p_api_version => l_api_version,
713 p_object_type => 2,
714 p_object_number => l_serial_number,
715 p_inventory_item_id => l_inventory_item_id,
716 p_org_id => l_organization_id,
717 p_genealogy_origin => 3,
718 p_genealogy_type => 5,
719 p_end_date_active => p_end_date_active,
720 p_update_txn_id => l_txn_id,
721 x_return_status => l_return_status,
722 x_msg_count => x_msg_count,
723 x_msg_data => x_msg_data);
724
725 if (l_return_status = 'E') then
726 raise FND_API.G_EXC_ERROR;
727 elsif (l_return_status = 'U') then
728 raise FND_API.G_EXC_UNEXPECTED_ERROR;
729 end if;
730
731 /* R12 Hook for Asset Log #4141712 Begin */
732 --Remove Parent Instance from Child Instance - 17
733 begin
734 SELECT cii.instance_id, mp.maint_organization_id
735 INTO l_instance_id , l_maint_orgid
736 FROM csi_item_instances cii, mtl_parameters mp
737 WHERE cii.serial_number = l_serial_number
738 AND cii.inventory_item_id = l_inventory_item_id
739 AND cii.last_vld_organization_id = mp.organization_id
740 AND cii.last_vld_organization_id= l_organization_id ;
741
742 SELECT cii.instance_number ,cii.instance_id
743 INTO l_reference, l_parent_instance_id
744 FROM csi_item_instances cii, mtl_serial_numbers msn
745 WHERE cii.serial_number = msn.serial_number
746 AND cii.inventory_item_id = msn.inventory_item_id
747 AND cii.last_vld_organization_id = msn.current_organization_id
748 AND msn.gen_object_id= l_parent_object_id ;
749
750 eam_asset_log_pvt.insert_row(
751 p_event_date => p_end_date_active,
752 p_event_type => l_event_type,
753 p_event_id => 17,
754 p_organization_id => l_maint_orgid,
755 p_instance_id => l_instance_id,
756 p_reference => l_reference,
757 p_ref_id => l_parent_instance_id,
758 p_instance_number => l_serial_number,
759 x_return_status => l_return_status,
760 x_msg_count => x_msg_count,
761 x_msg_data => x_msg_data
762 );
763
764 exception
765 WHEN NO_DATA_FOUND THEN
766 fnd_message.set_name
767 ( application => 'EAM'
768 , name => 'EAM_INSTANCE_ID_INVALID'
769 );
770
771 fnd_msg_pub.add;
772 x_return_status:= fnd_api.g_ret_sts_error;
773 fnd_msg_pub.Count_And_Get
774 ( p_count => x_msg_count,
775 p_data => x_msg_data
776 );
777 RETURN;
778 end;
779 --Remove Child Event from parent - 16
780 begin
781 SELECT cii.instance_id, cii.instance_number
782 INTO l_parent_instance_id, l_parent_instance_number
783 FROM csi_item_instances cii, mtl_serial_numbers msn
784 WHERE cii.serial_number = msn.serial_number
785 AND cii.inventory_item_id = msn.inventory_item_id
786 AND msn.gen_object_id = l_parent_object_id;
787
788 SELECT cii.instance_number
789 INTO l_reference
790 FROM csi_item_instances cii
791 WHERE cii.instance_id = l_instance_id
792 -- AND cii.inventory_item_id = l_inventory_item_id
793 AND cii.last_vld_organization_id = l_organization_id;
794
795 eam_asset_log_pvt.insert_row(
796 p_event_date => p_end_date_active,
797 p_event_type => l_event_type,
798 p_event_id => 16,
799 p_organization_id => l_maint_orgid,
800 p_instance_id => l_parent_instance_id,
801 p_reference => l_reference,
802 p_ref_id => l_instance_id,
803 p_instance_number => l_parent_instance_number,
804 x_return_status => l_return_status,
805 x_msg_count => x_msg_count,
806 x_msg_data => x_msg_data
807 );
808
809 exception
810 WHEN NO_DATA_FOUND THEN
811 fnd_message.set_name
812 ( application => 'EAM'
813 , name => 'EAM_INSTANCE_ID_INVALID'
814 );
815
816 fnd_msg_pub.add;
817 x_return_status:= fnd_api.g_ret_sts_error;
818 fnd_msg_pub.Count_And_Get
819 ( p_count => x_msg_count,
820 p_data => x_msg_data
821 );
822 RETURN;
823 end;
824 /* R12 Hook for Asset Log #4141712 End */
825
826 else
827 raise FND_API.G_EXC_ERROR;
828 end if;
829
830 if p_commit = FND_API.G_TRUE then
831 commit;
832 end if;
833
834
835 exception
836 WHEN FND_API.G_EXC_ERROR THEN
837
838 ROLLBACK TO eam_rebuild_genealogy;
839
840 x_return_status := FND_API.G_RET_STS_ERROR;
841
842 FND_MSG_PUB.Count_And_Get
843 ( p_encoded => FND_API.G_FALSE,
844 p_count => x_msg_count,
845 p_data => x_msg_data
846 );
847
848 WHEN OTHERS THEN
849
850 ROLLBACK TO eam_rebuild_genealogy;
851
852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
853
854 FND_MSG_PUB.Count_And_Get
855 ( p_encoded => FND_API.G_FALSE,
856 p_count => x_msg_count,
857 p_data => x_msg_data
858 );
859
860 end update_eam_genealogy;
861
862 end WIP_EAM_GENEALOGY_PVT;