[Home] [Help]
PACKAGE BODY: APPS.EAM_REBUILD_GENEALOGY
Source
1 PACKAGE BODY eam_rebuild_genealogy AS
2 /* $Header: EAMRBGNB.pls 115.4 2002/11/20 19:28:48 aan noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_REBUILD_GENEALOGY';
5
6 PROCEDURE create_rebuild_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 , x_msg_count OUT NOCOPY NUMBER
24 , x_msg_data OUT NOCOPY VARCHAR2
25 , x_return_status OUT NOCOPY VARCHAR2) is
26
27 l_api_name CONSTANT VARCHAR(30) := 'CREATE_REBUILD_GENEALOGY';
28 l_errCode number;
29 l_msg_count number;
30 l_msg_data varchar2(100);
31 l_return_status varchar2(5);
32 l_statement number;
33 l_txn_id number;
34 l_current_status number;
35 l_dist_acct_id number;
36 l_serial_number varchar2(30);
37 l_inventory_item_id number;
38 l_subinventory varchar2(30);
39 l_locator_id number;
40 l_organization_id number;
41 l_api_version number := 1.0;
42 l_parent_object_id number;
43 l_charge_object_id number;
44
45 begin
46
47 savepoint eam_rebuild_genealogy;
48
49 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
50 p_api_version,
51 l_api_name,
52 g_pkg_name)
53 THEN
54 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
55 END IF;
56
57 IF FND_API.TO_BOOLEAN(p_init_msg_list)
58 THEN
59 FND_MSG_PUB.initialize;
60 END IF;
61
62 -- Initialize API return status to success
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64
65 -- obtain rebuildable information
66 if p_object_id is null then
67 if p_serial_number is null
68 or p_inventory_item_id is null
69 or p_organization_id is null then
70 FND_MESSAGE.SET_NAME('INV', 'INV_FIELD_INVALID');
71 FND_MESSAGE.SET_TOKEN('ENTITY1', 'p_serial_number');
72 FND_MSG_PUB.ADD;
73 l_return_status := 'E';
74 RAISE FND_API.G_EXC_ERROR;
75 else
76 l_serial_number := p_serial_number;
77 l_inventory_item_id := p_inventory_item_id;
78 l_organization_id := p_organization_id;
79 end if;
80 else
81 if (p_serial_number is null)
82 or (p_inventory_item_id is null)
83 or (p_organization_id is null) then
84
85 select serial_number, inventory_item_id, current_organization_id
86 into l_serial_number, l_inventory_item_id, l_organization_id
87 from mtl_serial_numbers where
88 gen_object_id = p_object_id;
89 else
90
91 l_serial_number := p_serial_number;
92 l_inventory_item_id := p_inventory_item_id;
93 l_organization_id := p_organization_id;
94 end if;
95 end if;
96
97 -- obtain parent object id information
98 if p_parent_object_id is null then
99 if p_parent_serial_number is null
100 or p_parent_inventory_item_id is null
101 or p_parent_organization_id is null then
102 FND_MESSAGE.SET_NAME('INV', 'INV_FIELD_INVALID');
103 FND_MESSAGE.SET_TOKEN('ENTITY1', 'p_serial_number');
104 FND_MSG_PUB.ADD;
105 l_return_status := 'E';
106 RAISE FND_API.G_EXC_ERROR;
107 else
108 select gen_object_id into l_parent_object_id
109 from mtl_serial_numbers where
110 serial_number = p_parent_serial_number
111 and inventory_item_id = p_parent_inventory_item_id
112 and current_organization_id = p_parent_organization_id;
113 end if;
114 else
115 l_parent_object_id := p_parent_object_id;
116 end if;
117
118 -- 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.
119
120 begin
121 select msn.gen_object_id into l_charge_object_id
122 from wip_discrete_jobs wdj, mtl_serial_numbers msn
123 where wdj.rebuild_serial_number = l_serial_number
124 and wdj.rebuild_item_id = l_inventory_item_id
125 and wdj.organization_id = l_organization_id
126 and msn.serial_number = wdj.asset_number
127 and msn.inventory_item_id = wdj.asset_group_id
128 and msn.current_organization_id = wdj.organization_id
129 and wdj.manual_rebuild_flag = 'N'
130 and wdj.status_type in (1,3,6);
131
132 if l_charge_object_id <> l_parent_object_id then
133 FND_MESSAGE.SET_NAME('EAM', 'EAM_CURRENT_WO_WITH_CHARGE_ASSET');
134 FND_MSG_PUB.ADD;
135 l_return_status := 'E';
136 RAISE FND_API.G_EXC_ERROR;
137 end if;
138 exception
139 when others then
140 null;
141 end;
142
143 -- determine the current status of the rebuild component
144 begin
145 select current_status into l_current_status from mtl_serial_numbers
146 where serial_number = l_serial_number
147 and inventory_item_id = l_inventory_item_id
148 and current_organization_id = l_organization_id;
149
150 exception
151 when no_data_found then
152 FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_CURRENT_STATUS');
153 FND_MSG_PUB.ADD;
154 l_return_status := 'E';
155 RAISE FND_API.G_EXC_ERROR;
156 end;
157
158 -- if current_status = 3 then carry out transaction
159
160 if l_current_status = 3 then
161
162 if p_subinventory is null then
163 select current_subinventory_code into l_subinventory
164 from mtl_serial_numbers
165 where serial_number = l_serial_number and
166 inventory_item_id = l_inventory_item_id and
167 current_organization_id = l_organization_id;
168 else
169 l_subinventory := p_subinventory;
170 end if;
171
172 if p_locator_id is null then
173 begin
174 select current_locator_id into l_locator_id
175 from mtl_serial_numbers
176 where serial_number = l_serial_number and
177 inventory_item_id = l_inventory_item_id and
178 current_organization_id = l_organization_id;
179
180 exception
181 when others then
182 null;
183 end;
184 else
185 l_locator_id := p_locator_id;
186 end if;
187
188 -- Genealogy entry in the future is not allowed.
189 -- This restriction has been put in place because eventhough the date
190 -- of transaction is future, the status of the item is changed immediately
191
192 if p_start_date_active > sysdate OR (p_end_date_active is not null
193 and p_end_date_active > sysdate) then
194 l_return_status := 'E';
195 FND_MESSAGE.SET_NAME('EAM', 'EAM_REBUILD_FUTURE_TXN');
196 FND_MSG_PUB.ADD;
197 RAISE FND_API.G_EXC_ERROR;
198 elsif (p_start_date_active < sysdate and
199 (p_end_date_active is not null and p_end_date_active < sysdate)) then
200 l_txn_id := null;
201 l_return_status := 'S';
202 else
203
204 -- obtain the offset acct id from user defined EAM parameters
205 begin
206 select maintenance_offset_account into l_dist_acct_id
207 from wip_eam_parameters
208 where organization_id = l_organization_id;
209
210 exception
211 when others then
212 FND_MESSAGE.SET_NAME('EAM', 'EAM_NO_OFFSET_ACCOUNT');
213 FND_MSG_PUB.ADD;
214 RAISE FND_API.G_EXC_ERROR;
215 end;
216
217 -- Transaction processing API is called
218 -- If the transaction is processed successfully then
219 -- the inventory genealogy API is called
220
221 eam_transactions_pvt.process_eam_txn(
222 p_subinventory => l_subinventory,
223 p_locator_id => l_locator_id,
224 p_serial_number => l_serial_number,
225 p_organization_id => l_organization_id,
226 p_inventory_item_id => l_inventory_item_id,
227 p_dist_acct_id => l_dist_acct_id,
228 p_transaction_type_id => 32,
229 p_transaction_quantity => 1,
230 p_transaction_action_id => 1,
231 p_transaction_source_type_id => 13,
232 x_errCode => l_errCode,
233 x_msg_count => l_msg_count,
234 x_msg_data => l_msg_data,
235 x_return_status => l_return_status,
236 x_statement => l_statement);
237
238 begin
239 select last_transaction_id
240 into l_txn_id
241 from mtl_serial_numbers
242 where serial_number = l_serial_number;
243 exception
244 when others then
245 RAISE FND_API.G_EXC_ERROR;
246 end;
247 end if;
248
249 elsif l_current_status = 4 then
250 l_txn_id := null;
251 l_return_status := 'S';
252 else
253 FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_CURRENT_STATUS');
254 FND_MSG_PUB.ADD;
255 l_return_status := 'E';
256 RAISE FND_API.G_EXC_ERROR;
257 end if;
258
259 if l_return_status = 'S' then
260
261 FND_MSG_PUB.initialize;
262 inv_genealogy_pub.insert_genealogy(
263 p_api_version => l_api_version
264 , p_object_type => 2
265 , p_parent_object_type => 2
266 , p_object_number => l_serial_number
267 , p_inventory_item_id => l_inventory_item_id
268 , p_org_id => l_organization_id
269 , p_parent_object_id => p_parent_object_id
270 , p_parent_object_number => p_parent_serial_number
271 , p_parent_inventory_item_id => p_parent_inventory_item_id
272 , p_parent_org_id => p_parent_organization_id
273 , p_genealogy_origin => 3
274 , p_genealogy_type => 5
275 , p_start_date_active => p_start_date_active
276 , p_end_date_active => p_end_date_active
277 , p_origin_txn_id => l_txn_id
278 , x_return_status => l_return_status
279 , x_msg_count => l_msg_count
280 , x_msg_data => l_msg_data);
281
282 if (l_return_status = 'E') or (l_return_status = 'U') then
283 raise FND_API.G_EXC_ERROR;
284 end if;
285
286 else
287 raise FND_API.G_EXC_ERROR;
288 end if;
289
290 if p_commit = FND_API.G_TRUE then
291 commit;
292 end if;
293
294 exception
295 WHEN FND_API.G_EXC_ERROR THEN
296 ROLLBACK TO eam_rebuild_genealogy;
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 FND_MSG_PUB.Count_And_Get
299 ( p_encoded => FND_API.G_FALSE,
300 p_count => x_msg_count ,
301 p_data => x_msg_data
302 );
303
304 WHEN OTHERS THEN
305 ROLLBACK TO eam_rebuild_genealogy;
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 FND_MSG_PUB.Count_And_Get
308 ( p_encoded => FND_API.G_FALSE,
309 p_count => x_msg_count ,
310 p_data => x_msg_data
311 );
312
313 end create_rebuild_genealogy;
314
315
316 PROCEDURE update_rebuild_genealogy(
317 p_api_version IN NUMBER
318 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
319 , p_commit IN VARCHAR2 := FND_API.G_FALSE
320 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
321 , p_object_type IN NUMBER
322 , p_object_id IN NUMBER := NULL
323 , p_serial_number IN VARCHAR2 := NULL
324 , p_inventory_item_id IN NUMBER := NULL
325 , p_organization_id IN NUMBER := NULL
326 , p_subinventory IN VARCHAR2 := NULL
327 , p_locator_id IN NUMBER := NULL
328 , p_genealogy_origin IN NUMBER := NULL
329 , p_genealogy_type IN NUMBER := NULL
330 , p_end_date_active IN DATE := NULL
331 , x_return_status OUT NOCOPY VARCHAR2
332 , x_msg_count OUT NOCOPY NUMBER
333 , x_msg_data OUT NOCOPY VARCHAR2) is
334
335 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_REBUILD_GENEALOGY';
336 l_errCode number;
337 l_msg_count number;
338 l_msg_data varchar2(100);
339 l_return_status varchar2(5);
340 l_statement number;
341 l_txn_id number;
342 l_dist_acct_id number;
343 l_api_version number := 1.0;
344 l_serial_number varchar2(30);
345 l_subinventory varchar2(30);
346 l_locator_id number;
347 l_inventory_item_id number;
348 l_object_id number;
349 l_organization_id number;
350 l_txn_type number;
351 l_txn_action_id number;
352 l_txn_source_type number;
353 l_wip_entity_id number;
354 l_work_status number;
355 l_original_txn_id number;
356
357 begin
358
359 savepoint eam_rebuild_genealogy;
360
361 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
362 p_api_version,
363 l_api_name,
364 g_pkg_name)
365 THEN
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
367 END IF;
368
369 IF FND_API.TO_BOOLEAN(p_init_msg_list)
370 THEN
371 FND_MSG_PUB.initialize;
372 END IF;
376
373
374 -- Initialize API return status to success
375 x_return_status := FND_API.G_RET_STS_SUCCESS;
377
378 -- obtain rebuildable information
379 if p_object_id is null then
380 if p_serial_number is null
381 or p_inventory_item_id is null
382 or p_organization_id is null then
383 FND_MESSAGE.SET_NAME('INV', 'INV_FIELD_INVALID');
384 FND_MESSAGE.SET_TOKEN('ENTITY1', 'p_serial_number');
385 FND_MSG_PUB.ADD;
386 l_return_status := 'E';
387 RAISE FND_API.G_EXC_ERROR;
388 else
389 l_serial_number := p_serial_number;
390 l_inventory_item_id := p_inventory_item_id;
391 l_organization_id := p_organization_id;
392
393 begin
394 select gen_object_id into l_object_id
395 from mtl_serial_numbers
396 where serial_number = p_serial_number
397 and inventory_item_id = p_inventory_item_id
398 and current_organization_id = p_organization_id;
399 exception
400 when others then
401 FND_MESSAGE.SET_NAME('INV', 'INV_EAM_GEN_INVALID_OBJECT');
402 FND_MSG_PUB.ADD;
403 l_return_status := 'E';
404 RAISE FND_API.G_EXC_ERROR;
405 end;
406 end if;
407 else
408
409 if (p_serial_number is null)
410 or (p_inventory_item_id is null)
411 or (p_organization_id is null) then
412
413 begin
414 select serial_number, inventory_item_id, current_organization_id
415 into l_serial_number, l_inventory_item_id, l_organization_id
416 from mtl_serial_numbers where
417 gen_object_id = p_object_id;
418
419 exception
420 when others then
421 FND_MESSAGE.SET_NAME('INV', 'INV_EAM_GEN_INVALID_OBJECT');
422 FND_MSG_PUB.ADD;
423 l_return_status := 'E';
424 RAISE FND_API.G_EXC_ERROR;
425 end;
426 else
427 l_serial_number := p_serial_number;
428 l_inventory_item_id := p_inventory_item_id;
429 l_organization_id := p_organization_id;
430 end if;
431
432 l_object_id := p_object_id;
433 end if;
434
435
436
437 if p_subinventory is null then
438 begin
439 select current_subinventory_code into l_subinventory
440 from mtl_serial_numbers
441 where serial_number = l_serial_number and
442 inventory_item_id = l_inventory_item_id and
443 current_organization_id = l_organization_id;
444 exception
445 when others then
446 l_return_status := 'E';
447 FND_MESSAGE.SET_NAME('EAM', 'EAM_NO_SUBINV_SPECIFIED');
448 FND_MSG_PUB.ADD;
449 RAISE FND_API.G_EXC_ERROR;
450 end;
451 else
452 l_subinventory := p_subinventory;
453 end if;
454
455 if p_locator_id is null then
456 begin
457 select current_locator_id into l_locator_id
458 from mtl_serial_numbers
459 where serial_number = l_serial_number and
460 inventory_item_id = l_inventory_item_id and
461 current_organization_id = l_organization_id;
462
463 exception
464 when others then
465 null;
466 end;
467 else
468 l_locator_id := p_locator_id;
469 end if;
470
471
472 -- genealogy update in future is not allowed
473 -- This restriction has been put in place because eventhough the date
474 -- of transaction is future, the status of the item is changed immediately
475
476 if p_end_date_active is not null and p_end_date_active > sysdate then
477 l_return_status := 'E';
478 FND_MESSAGE.SET_NAME('EAM', 'EAM_REBUILD_FUTURE_TXN');
479 FND_MSG_PUB.ADD;
480 RAISE FND_API.G_EXC_ERROR;
481 else
482
483 -- obtain the offset account id from user-defined EAM parameters
484 begin
485 select maintenance_offset_account into l_dist_acct_id
486 from wip_eam_parameters
487 where organization_id = l_organization_id;
488
489 exception
490 when others then
491 FND_MESSAGE.SET_NAME('EAM', 'EAM_NO_OFFSET_ACCOUNT');
492 FND_MSG_PUB.ADD;
493 RAISE FND_API.G_EXC_ERROR;
494 end;
495
496 -- call the Transaction processing API
497 -- if API returns success, call the inventory genealogy API
498 eam_transactions_pvt.process_eam_txn(
499 p_subinventory => l_subinventory,
500 p_serial_number => l_serial_number,
501 p_locator_id => l_locator_id,
502 p_organization_id => l_organization_id,
503 p_inventory_item_id => l_inventory_item_id,
504 p_dist_acct_id => l_dist_acct_id,
505 p_transaction_type_id => 42,
506 p_transaction_quantity => 1,
507 p_transaction_action_id => 27,
508 p_transaction_source_type_id => 13,
509 x_errCode => l_errCode,
510 x_msg_count => l_msg_count,
511 x_msg_data => l_msg_data,
512 x_return_status => l_return_status,
513 x_statement => l_statement);
514 begin
515 select last_transaction_id
516 into l_txn_id
517 from mtl_serial_numbers
518 where serial_number = l_serial_number;
519 -- dbms_output.put_line('got here 3');
520 exception
521 when others then
522 RAISE FND_API.G_EXC_ERROR;
523 end;
524 end if;
525
526 if l_return_status = 'S' then
527 --dbms_output.put_line('got here 4');
528 FND_MSG_PUB.initialize;
529 inv_genealogy_pub.update_genealogy(
530 p_api_version => l_api_version
531 , p_object_type => 2
532 , p_object_number => l_serial_number
533 , p_inventory_item_id => l_inventory_item_id
534 , p_org_id => l_organization_id
535 , p_genealogy_origin => 3
536 , p_genealogy_type => 5
537 , p_end_date_active => p_end_date_active
538 , p_update_txn_id => l_txn_id
539 , x_return_status => l_return_status
540 , x_msg_count => l_msg_count
541 , x_msg_data => l_msg_data);
542
543 if (l_return_status = 'E') or (l_return_status = 'U') then
544 raise FND_API.G_EXC_ERROR;
545 end if;
546
547 else
548 raise FND_API.G_EXC_ERROR;
549 end if;
550
551 if p_commit = FND_API.G_TRUE then
552 commit;
553 end if;
554
555 exception
556 WHEN FND_API.G_EXC_ERROR THEN
557 ROLLBACK TO eam_rebuild_genealogy;
558 x_return_status := l_RETURN_STATUS;
559 FND_MSG_PUB.Count_And_Get
560 ( p_encoded => FND_API.G_FALSE,
561 p_count => x_msg_count ,
562 p_data => x_msg_data
563 );
564 WHEN OTHERS THEN
565 ROLLBACK TO eam_rebuild_genealogy;
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567 FND_MSG_PUB.Count_And_Get
568 ( p_encoded => FND_API.G_FALSE,
569 p_count => x_msg_count ,
570 p_data => x_msg_data
571 );
572
573 end update_rebuild_genealogy;
574
575 end eam_rebuild_genealogy;