[Home] [Help]
PACKAGE BODY: APPS.WIP_EAM_GENEALOGY_PVT
Source
1 PACKAGE BODY WIP_EAM_GENEALOGY_PVT AS
2 /* $Header: WIPVEGNB.pls 120.8.12010000.2 2008/12/23 10:18:23 smrsharm 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,
376 p_inventory_item_id IN NUMBER := NULL,
377 p_organization_id IN NUMBER := NULL,
378 p_subinventory IN VARCHAR2 := NULL,
379 p_locator_id IN NUMBER := 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 x_locator_ctrl NUMBER ;
436 x_error_flag NUMBER ;
437 x_error_mssg VARCHAR2(100);
438
439
440 begin
441
442 savepoint eam_rebuild_genealogy;
443
444 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
445 p_api_version,
446 l_api_name,
447 g_pkg_name)
448 THEN
449
450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
451
452 END IF;
453
454
455 IF FND_API.TO_BOOLEAN(p_init_msg_list)
456 THEN
457
458 FND_MSG_PUB.initialize;
459
460 END IF;
461
462 -- Initialize API return status to success
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464 l_return_status := FND_API.G_RET_STS_SUCCESS;
465
466 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
467 'Start of ' || l_module || '('
468 || 'p_organization_id='|| p_organization_id || ','
469 || 'p_subinventory='|| p_subinventory || ','
470 || 'p_locator_id='|| p_locator_id || ','
471 || 'p_object_type='|| p_object_type || ','
472 || 'p_object_id='|| p_object_id || ','
473 || 'p_serial_number='|| p_serial_number ||','
474 || 'p_inventory_item_id='|| p_inventory_item_id || ','
475 || 'p_end_date_active='|| p_end_date_active ||','
476 || 'p_from_eam='|| p_from_eam || ','
477 || 'p_commit='|| p_commit
478 || ')');
479 end if;
480
481
482 -- return without error if serial number is not specified when
483 -- the genealogy is not originating from eam
484 if (((p_from_eam is null) or (p_from_eam = FND_API.G_FALSE))
485 and ((p_serial_number is null) and (p_object_id is null))) then
486 x_return_status := FND_API.G_RET_STS_SUCCESS;
487 return;
488 end if;
489
490 -- obtain rebuildable information
491 if p_object_id is null then
492
493 if p_serial_number is null
494 or p_inventory_item_id is null
495 or p_organization_id is null then
496
497 -- if serial_number is null, then quit processing
498 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ITEM_DOES_NOT_EXIST');
499 FND_MSG_PUB.ADD;
500 RAISE FND_API.G_EXC_ERROR;
501 else
502 l_serial_number := p_serial_number;
503 l_inventory_item_id := p_inventory_item_id;
504 --l_organization_id := p_organization_id;
505
506
507 begin
508 select gen_object_id,current_organization_id into l_object_id, l_organization_id
509 from mtl_serial_numbers
510 where serial_number = l_serial_number
511 and inventory_item_id = p_inventory_item_id;
512
513 exception
514 when others then
515 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ITEM_DOES_NOT_EXIST');
516 FND_MSG_PUB.ADD;
517 RAISE FND_API.G_EXC_ERROR;
518 end;
519 end if;
520
521 else
522
523 if (p_serial_number is null)
524 or (p_inventory_item_id is null)
525 or (p_organization_id is null) then
526
527 begin
528 select serial_number, inventory_item_id, current_organization_id
529 into l_serial_number, l_inventory_item_id, l_organization_id
530 from mtl_serial_numbers where
531 gen_object_id = p_object_id;
532
533 exception
534 when others then
535 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ITEM_DOES_NOT_EXIST');
536 FND_MSG_PUB.ADD;
537 RAISE FND_API.G_EXC_ERROR;
538 end;
539
540 else
541 l_serial_number := p_serial_number;
542 l_inventory_item_id := p_inventory_item_id;
543 --l_organization_id := p_organization_id;
544 select current_organization_id into l_organization_id
545 from mtl_serial_numbers
546 where gen_object_id = p_object_id;
547
548 end if;
549
550 l_object_id := p_object_id;
551
552 end if;
553
554
555 -- perform misc inventory transaction if p_from_eam is set to TRUE.
556 if FND_API.to_Boolean( p_from_eam ) and (p_subinventory is not null) THEN
557
558
559 l_subinventory := p_subinventory;
560
561 IF inv_install.adv_inv_installed(NULL) THEN
562 l_wms_installed := 'TRUE';
563 ELSE
564 l_wms_installed := 'FALSE';
565 END IF;
566
567 if inv_material_status_grp.is_status_applicable(
568 l_wms_installed,
569 NULL,
570 42,
571 NULL,
572 NULL,
573 l_organization_id,
574 l_inventory_item_id,
575 l_subinventory,
576 NULL,
577 NULL,
578 NULL,
579 'Z') <> 'Y' then
580 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_INVALID_SUBINVENTORY');
581 FND_MSG_PUB.ADD;
582 RAISE FND_API.G_EXC_ERROR;
583 end if;
584
585 select restrict_subinventories_code
586 into l_sub_code
587 from mtl_system_items
588 where inventory_item_id = l_inventory_item_id
589 and organization_id = l_organization_id;
590
591 if l_sub_code = 1 then
592
593 l_dummy := 1;
594
595 begin
596 select 10 into l_dummy from dual
597 where exists
598 (select *
599 from mtl_item_sub_inventories
600 where inventory_item_id = l_inventory_item_id
601 and organization_id = l_organization_id
602 and secondary_inventory = l_subinventory);
603 exception
604 when others then
605 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_INVALID_SUBINVENTORY');
606 FND_MSG_PUB.ADD;
607 RAISE FND_API.G_EXC_ERROR;
608 end;
609
610 if l_dummy <> 10 then
611 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_INVALID_SUBINVENTORY');
612 FND_MSG_PUB.ADD;
613 RAISE FND_API.G_EXC_ERROR;
614 end if;
615
616 end if;
617
618
619 if p_locator_id is null then
620 begin
621 select current_locator_id into l_locator_id
622 from mtl_serial_numbers
623 where serial_number = l_serial_number and
624 inventory_item_id = l_inventory_item_id and
625 current_organization_id = l_organization_id;
626
627 exception
628 when others then
629 null;
630 end;
631 else
632 l_locator_id := p_locator_id;
633 end if;
634
635 Get_LocatorControl_Code(
636 l_organization_id,
637 l_subinventory,
638 l_inventory_item_id,
639 27,
640 x_locator_ctrl,
641 x_error_flag,
642 x_error_mssg);
643
644
645 -- if the locator control is Predefined or Dynamic Entry
646 if(x_locator_ctrl = 2 or x_locator_ctrl = 3) then
647 if(p_locator_id IS NULL) then
648 FND_MESSAGE.SET_NAME('EAM', 'EAM_RET_MAT_LOCATOR_NEEDED');
649 FND_MSG_PUB.ADD;
650 RAISE FND_API.G_EXC_ERROR;
651 end if;
652 elsif(x_locator_ctrl = 1) then -- If the locator control is NOControl
653 if(p_locator_id IS NOT NULL) then
654 FND_MESSAGE.SET_NAME('EAM', 'EAM_RET_MAT_LOCATOR_RESTRICTED');
655 FND_MSG_PUB.ADD;
656 RAISE FND_API.G_EXC_ERROR;
657 end if;
658 end if; -- end of locator_control checkif
659
660
661
662 -- genealogy update in future is not allowed
663 -- This restriction has been put in place because eventhough the date
664 -- of transaction is future, the status of the item is changed immediately
665
666 if p_end_date_active is not null and p_end_date_active > sysdate then
667 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_REBUILD_FUTURE_TXN');
668 FND_MSG_PUB.ADD;
669 RAISE FND_API.G_EXC_ERROR;
670 else
671
672 -- obtain the offset account id from user-defined EAM parameters
673 select maintenance_offset_account into l_dist_acct_id
674 from wip_eam_parameters
675 where organization_id = l_organization_id;
676
677 if l_dist_acct_id is null then
678 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_NO_OFFSET_ACCOUNT');
679 FND_MSG_PUB.ADD;
680 RAISE FND_API.G_EXC_ERROR;
681 end if;
682
683 --obtain the revision of the object from MSN
684 select revision into l_revision
685 from mtl_serial_numbers
686 where serial_number = l_serial_number and
687 inventory_item_id = l_inventory_item_id and
688 current_organization_id = l_organization_id;
689
690 -- call the Transaction processing API
691 -- if API returns success, call the inventory genealogy API
692 wip_eam_transactions_pvt.process_eam_txn(
693 p_subinventory => l_subinventory,
694 p_serial_number => l_serial_number,
695 p_locator_id => l_locator_id,
696 p_organization_id => l_organization_id,
697 p_inventory_item_id => l_inventory_item_id,
698 p_dist_acct_id => l_dist_acct_id,
699 p_transaction_type_id => 42,
700 p_transaction_quantity => 1,
701 p_transaction_action_id => 27,
702 p_transaction_source_type_id => 13,
703 p_revision => l_revision,
704 x_errCode => l_errCode,
705 x_msg_count => x_msg_count,
706 x_msg_data => x_msg_data,
707 x_return_status => l_return_status,
708 x_statement => l_statement);
709
710 select last_transaction_id
711 into l_txn_id
712 from mtl_serial_numbers
713 where serial_number = l_serial_number
714 and current_organization_id = l_organization_id and
715 inventory_item_id = l_inventory_item_id;
716
717 if l_txn_id is null then
718 RAISE FND_API.G_EXC_ERROR;
719 end if;
720
721 end if;
722
723 end if;
724
725
726 -- perform genealogy update
727
728 if l_return_status = 'S' then
729
730 /* R12 Hook for Asset Log #4141712 To get Parent Object Id Begin*/
731 SELECT parent_object_id INTO l_parent_object_id
732 FROM mtl_object_genealogy
733 WHERE genealogy_type = 5
734 AND object_id = l_object_id
735 AND end_date_active IS NULL;
736 /* R12 Hook for Asset Log #4141712 To get Parent Object Id End*/
737
738 FND_MSG_PUB.initialize;
739
740 inv_genealogy_pub.update_genealogy(
741 p_api_version => l_api_version,
742 p_object_type => 2,
743 p_object_number => l_serial_number,
744 p_inventory_item_id => l_inventory_item_id,
745 p_org_id => l_organization_id,
746 p_genealogy_origin => 3,
747 p_genealogy_type => 5,
748 p_end_date_active => p_end_date_active,
749 p_update_txn_id => l_txn_id,
750 x_return_status => l_return_status,
751 x_msg_count => x_msg_count,
752 x_msg_data => x_msg_data);
753
754 if (l_return_status = 'E') then
755 raise FND_API.G_EXC_ERROR;
756 elsif (l_return_status = 'U') then
757 raise FND_API.G_EXC_UNEXPECTED_ERROR;
758 end if;
759
760 /* R12 Hook for Asset Log #4141712 Begin */
761 --Remove Parent Instance from Child Instance - 17
762 begin
763 SELECT cii.instance_id, mp.maint_organization_id
764 INTO l_instance_id , l_maint_orgid
765 FROM csi_item_instances cii, mtl_parameters mp
766 WHERE cii.serial_number = l_serial_number
767 AND cii.inventory_item_id = l_inventory_item_id
768 AND cii.last_vld_organization_id = mp.organization_id
769 AND cii.last_vld_organization_id= l_organization_id ;
770
771 SELECT cii.instance_number ,cii.instance_id
772 INTO l_reference, l_parent_instance_id
773 FROM csi_item_instances cii, mtl_serial_numbers msn
774 WHERE cii.serial_number = msn.serial_number
775 AND cii.inventory_item_id = msn.inventory_item_id
776 AND cii.last_vld_organization_id = msn.current_organization_id
777 AND msn.gen_object_id= l_parent_object_id ;
778
779 eam_asset_log_pvt.insert_row(
780 p_event_date => p_end_date_active,
781 p_event_type => l_event_type,
782 p_event_id => 17,
783 p_organization_id => l_maint_orgid,
784 p_instance_id => l_instance_id,
785 p_reference => l_reference,
786 p_ref_id => l_parent_instance_id,
787 p_instance_number => l_serial_number,
788 x_return_status => l_return_status,
789 x_msg_count => x_msg_count,
790 x_msg_data => x_msg_data
791 );
792
793 exception
794 WHEN NO_DATA_FOUND THEN
795 fnd_message.set_name
796 ( application => 'EAM'
797 , name => 'EAM_INSTANCE_ID_INVALID'
798 );
799
800 fnd_msg_pub.add;
801 x_return_status:= fnd_api.g_ret_sts_error;
802 fnd_msg_pub.Count_And_Get
803 ( p_count => x_msg_count,
804 p_data => x_msg_data
805 );
806 RETURN;
807 end;
808 --Remove Child Event from parent - 16
809 begin
810 SELECT cii.instance_id, cii.instance_number
811 INTO l_parent_instance_id, l_parent_instance_number
812 FROM csi_item_instances cii, mtl_serial_numbers msn
813 WHERE cii.serial_number = msn.serial_number
814 AND cii.inventory_item_id = msn.inventory_item_id
815 AND msn.gen_object_id = l_parent_object_id;
816
817 SELECT cii.instance_number
818 INTO l_reference
819 FROM csi_item_instances cii
820 WHERE cii.instance_id = l_instance_id
821 -- AND cii.inventory_item_id = l_inventory_item_id
822 AND cii.last_vld_organization_id = l_organization_id;
823
824 eam_asset_log_pvt.insert_row(
825 p_event_date => p_end_date_active,
826 p_event_type => l_event_type,
827 p_event_id => 16,
828 p_organization_id => l_maint_orgid,
829 p_instance_id => l_parent_instance_id,
830 p_reference => l_reference,
831 p_ref_id => l_instance_id,
832 p_instance_number => l_parent_instance_number,
833 x_return_status => l_return_status,
834 x_msg_count => x_msg_count,
835 x_msg_data => x_msg_data
836 );
837
838 exception
839 WHEN NO_DATA_FOUND THEN
840 fnd_message.set_name
841 ( application => 'EAM'
842 , name => 'EAM_INSTANCE_ID_INVALID'
843 );
844
845 fnd_msg_pub.add;
846 x_return_status:= fnd_api.g_ret_sts_error;
847 fnd_msg_pub.Count_And_Get
848 ( p_count => x_msg_count,
849 p_data => x_msg_data
850 );
851 RETURN;
852 end;
853 /* R12 Hook for Asset Log #4141712 End */
854
855 else
856 raise FND_API.G_EXC_ERROR;
857 end if;
858
859 if p_commit = FND_API.G_TRUE then
860 commit;
861 end if;
862
863
864 exception
865 WHEN FND_API.G_EXC_ERROR THEN
866
867 ROLLBACK TO eam_rebuild_genealogy;
868
869 x_return_status := FND_API.G_RET_STS_ERROR;
870
871 FND_MSG_PUB.Count_And_Get
872 ( p_encoded => FND_API.G_FALSE,
873 p_count => x_msg_count,
874 p_data => x_msg_data
875 );
876
877 WHEN OTHERS THEN
878
879 ROLLBACK TO eam_rebuild_genealogy;
880
881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
882
883 FND_MSG_PUB.Count_And_Get
884 ( p_encoded => FND_API.G_FALSE,
885 p_count => x_msg_count,
886 p_data => x_msg_data
887 );
888
889 end update_eam_genealogy;
890
891
892 Procedure Get_LocatorControl_Code(
893 p_org IN NUMBER,
894 p_subinv IN VARCHAR2,
895 p_item_id IN NUMBER,
896 p_action IN NUMBER,
897 x_locator_ctrl OUT NOCOPY NUMBER,
898 x_error_flag OUT NOCOPY NUMBER, -- returns 0 if no error ,1 if any error .
899 x_error_mssg OUT NOCOPY VARCHAR2
900 ) IS
901 x_org_ctrl NUMBER;
902 x_sub_ctrl NUMBER;
903 x_item_ctrl NUMBER;
904 x_neg_flag NUMBER;
905 x_restrict_flag NUMBER;
906 BEGIN
907
908 -- initialize the output .
909 x_error_flag := 0;
910 x_error_mssg := '';
911
912 -- retrive organization level control information
913 Begin
914 SELECT
915 negative_inv_receipt_code,stock_locator_control_code into
916 x_neg_flag,x_org_ctrl FROM MTL_PARAMETERS
917 WHERE
918 organization_id = p_org;
919 Exception
920 When no_data_found then
921 x_error_flag := 1;
922 x_error_mssg := 'EAM_INVALID_ORGANIZATION';
923 End;
924
925 -- retrive subinventory level control information
926 Begin
927 SELECT
928 locator_type into x_sub_ctrl
929 FROM MTL_SECONDARY_INVENTORIES
930 WHERE
931 organization_id = p_org and
932 secondary_inventory_name = p_subinv ;
933 Exception
934 When no_data_found then
935 x_error_flag := 1;
936 x_error_mssg := 'EAM_RET_MAT_INVALID_SUBINV1';
937 End;
938
939
940 -- retrive Item level control information
941 Begin
942
943 SELECT
944 location_control_code,restrict_locators_code into
945 x_item_ctrl,x_restrict_flag
946 FROM MTL_SYSTEM_ITEMS
947 WHERE
948 inventory_item_id = p_item_id and
949 organization_id = p_org;
950 Exception
951 When no_data_found then
952 x_error_flag := 1;
953 x_error_mssg := 'EAM_NO_ITEM_FOUND';
954 End;
955
956
957 if(x_org_ctrl = 1) then
958 x_locator_ctrl := 1;
959 elsif(x_org_ctrl = 2) then
960 x_locator_ctrl := 2;
961 elsif(x_org_ctrl = 3) then
962 x_locator_ctrl := 3;
963 if(dynamic_entry_not_allowed(x_restrict_flag,
964 x_neg_flag,p_action)) then
965 x_locator_ctrl := 2;
966 end if;
967 elsif(x_org_ctrl = 4) then
968 if(x_sub_ctrl = 1) then
969 x_locator_ctrl := 1;
970 elsif(x_sub_ctrl = 2) then
971 x_locator_ctrl := 2;
972 elsif(x_sub_ctrl = 3) then
973 x_locator_ctrl := 3;
974 if(dynamic_entry_not_allowed(x_restrict_flag,
975 x_neg_flag,p_action)) then
976 x_locator_ctrl := 2;
977 end if;
978 elsif(x_sub_ctrl = 5) then
979 if(x_item_ctrl = 1) then
980 x_locator_ctrl := 1;
981 elsif(x_item_ctrl = 2) then
982 x_locator_ctrl := 2;
983 elsif(x_item_ctrl = 3) then
984 x_locator_ctrl := 3;
985 if(dynamic_entry_not_allowed(x_restrict_flag,
986 x_neg_flag,p_action)) then
987 x_locator_ctrl := 2;
988 end if;
989 elsif(x_item_ctrl IS NULL) then
990 x_locator_ctrl := x_sub_ctrl;
991 else
992 x_error_flag := 1;
993 x_error_mssg := 'EAM_RET_MAT_INVALID_LOCATOR';
994 return ;
995 end if;
996 else
997 x_error_flag := 1;
998 x_error_mssg := 'EAM_RET_MAT_INVALID_SUBINV';
999 return ;
1000 end if;
1001 else
1002 x_error_flag := 1;
1003 x_error_mssg := 'EAM_RET_MAT_INVALID_ORG';
1004 return ;
1005 end if;
1006
1007 END Get_LocatorControl_Code; -- end of get_locatorcontrol_code procedure
1008
1009 Function Dynamic_Entry_Not_Allowed(
1010 p_restrict_flag IN NUMBER,
1011 p_neg_flag IN NUMBER,
1012 p_action IN NUMBER) return Boolean IS
1013 Begin
1014 if(p_restrict_flag = 2 or p_restrict_flag = null) then
1015 if(p_neg_flag = 2) then
1016 if(p_action = 1 or p_action = 2 or p_action = 3 or
1017 p_action = 21 or p_action = 30 or p_action = 32) then
1018 return TRUE;
1019 end if;
1020 else
1021 return FALSE;
1022 end if; -- end of neg_flag check
1023 elsif(p_restrict_flag = 1) then
1024 return TRUE;
1025 end if;
1026 return TRUE;
1027 End Dynamic_Entry_Not_Allowed ;
1028
1029 end WIP_EAM_GENEALOGY_PVT;
1030