1 PACKAGE BODY inv_genealogy_pub AS
2 /* $Header: INVPVCGB.pls 120.11.12000000.4 2007/01/26 20:12:46 mrana ship $ */
3
4 /*-------------------------------------------------------------------------------+
5 | This file contains the Genealogy API body. These APIs will be used |
6 | exclusively to Create Genealogy records |
7 | History: |
8 | May 10, 2000. sthamman Created package body. |
9 | Aug 10, 2000. mrana Value for 4th column in insert |
10 | object_genealogy should be l_parent_id |
11 +-------------------------------------------------------------------------------*/
12 --
13 -- FILENAME
14 --
15 -- INVPVCGB.pls
16 --
17 -- DESCRIPTION
18 -- Body of package INV_genealogy_PUB
19 --
20 -- NOTES
21 --
22 -- HISTORY
23 -- 10-MAY-00 Created sthamman
24 -- 23-May-00 Modified sthamman
25 -- Introduced the following parameters
26 -- 1. p_object_id
27 -- 2. p_inventory_item_id
28 -- 3. p_org_id
29 -- 4. p_parent_object_id
30 -- 5. p_parent_inventory_item_id
31 -- 6. p_parent_org_id
32 --
33
34 /* Genealogy_object_types :
35 * ----------------
36 * 1 Lot
37 * 2 Serial
38 * 3 External
39 * 4 Container
40 * 5 Job
41 *
42 * Genealogy Type
43 * ----------------
44 * 1 Assembly
45 * 2 Lot Split
46 * 3 Lot merge
47 * 4 Sublot
48 * 5 Assets (used by EAM)
49 *
50 * Genealogy Origin
51 * ----------------
52 * 1 WIP
53 * 2 Transaction
54 * 3 Manual
55 * */
56
57 -- Global constant holding the package name
58 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_genealogy_PUB';
59 g_mod_name VARCHAR2(30) := NULL;
60 --lg_fnd_validate_none CONSTANT NUMBER := 0;
61 lg_fnd_g_false VARCHAR2(1) := FND_API.G_FALSE;
62 lg_fnd_valid_level_full NUMBER := FND_API.G_VALID_LEVEL_FULL;
63
64 lg_ret_sts_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_error;
65 lg_ret_sts_unexp_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_unexp_error;
66 lg_ret_sts_success CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_success;
67
68 lg_exc_error EXCEPTION ; --fnd_api.g_exc_error;
69 lg_exc_unexpected_error EXCEPTION ; --fnd_api.g_exc_unexpected_error;
70
71
72 -- R12 GEnealogy Enhancements :
73 -- Local prtocedure to validate input parameters passed to Insert-genealogy or
74 -- Update_genealogy. The reason for creating this is because both the procedures
75 -- have the same set of parameters and same validations
76
77 PROCEDURE parameter_validations(
78 p_validation_level IN NUMBER := gen_fnd_valid_level_full
79 , p_object_type IN NUMBER
80 , p_parent_object_type IN NUMBER
81 , p_object_id IN OUT NOCOPY NUMBER
82 , p_object_number IN VARCHAR2
83 , p_inventory_item_id IN NUMBER
84 , p_org_id IN NUMBER
85 , p_parent_object_id IN OUT NOCOPY NUMBER
86 , p_parent_object_number IN VARCHAR2
87 , p_parent_inventory_item_id IN NUMBER
88 , p_parent_org_id IN NUMBER
89 , p_genealogy_origin IN NUMBER
90 , p_genealogy_type IN NUMBER
91 , p_start_date_active IN DATE
92 , p_end_date_active IN DATE
93 , p_origin_txn_id IN NUMBER
94 , p_update_txn_id IN NUMBER
95 , p_object_type2 IN OUT NOCOPY NUMBER
96 , p_object_id2 IN OUT NOCOPY NUMBER
97 , p_object_number2 IN VARCHAR2
98 , p_parent_object_type2 IN OUT NOCOPY NUMBER
99 , p_parent_object_id2 IN OUT NOCOPY NUMBER
100 , p_parent_object_number2 IN VARCHAR2
101 , p_child_lot_control_code IN NUMBER
102 , p_parent_lot_control_code IN NUMBER
103 , p_action IN VARCHAR2
104 , p_debug IN NUMBER
105 , x_return_status OUT NOCOPY VARCHAR2
106 , x_msg_count OUT NOCOPY NUMBER
107 , x_msg_data OUT NOCOPY VARCHAR2) ;
108
109
110 PROCEDURE mydebug( p_msg IN VARCHAR2)
111 IS
112 BEGIN
113 inv_log_util.trace( p_message => p_msg,
114 p_module => g_pkg_name ,
115 p_level => 9);
116
117 --dbms_output.put_line( p_msg );
118 END mydebug;
119
120 /* The function recursively checks whether the parent is among
121 the children of the given asset within the given dates.
122 To do so, it exhaustively traverses down the tree with the
123 given asset as its root node. If it finds the parent, it
124 returns a value of 1 and exits; otherwise it traverses
125 until it reaches the leaf node. This function assumes that
126 the existing data in the MOG table is valid and does not
127 contain any loop. Hence it does not check any asset that
128 it has already traversed. This is the main difference
129 between this function and the previous select statement
130 using a connect by clause. This is a fix for bug # 2287872
131 */
132
133 FUNCTION genealogy_loop(
134 object_id IN NUMBER
135 , parent_object_id IN NUMBER
136 , start_date IN DATE
137 , end_date IN DATE
138 , object_table IN OUT NOCOPY object_id_tbl_t
139 )
140 RETURN NUMBER AS
141 l_dummy NUMBER := 0;
142 i NUMBER;
143 l_dummy_char VARCHAR2(10) := NULL;
144 l_object_id NUMBER := object_id;
145 counter NUMBER;
146 l_start_date DATE;
147 l_end_date DATE;
148 l_count NUMBER;
149 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
150 BEGIN
151 -- check if the parent asset is an immediate child of the given asset
152
153 IF end_date IS NULL THEN
154 BEGIN
155 SELECT 1
156 INTO l_dummy
157 FROM DUAL
158 WHERE parent_object_id IN (SELECT mog.object_id
159 FROM mtl_object_genealogy mog
160 WHERE (end_date_active IS NULL OR end_date_active >= start_date)
161 AND parent_object_id = l_object_id
162 AND genealogy_type = 5);
163 EXCEPTION
164 WHEN OTHERS THEN
165 NULL;
166 END;
167 ELSE
168 BEGIN
169 SELECT 1
170 INTO l_dummy
171 FROM DUAL
172 WHERE parent_object_id IN (SELECT mog.object_id
173 FROM mtl_object_genealogy mog
174 WHERE (((start_date_active <= start_date)
175 AND (end_date_active IS NULL
176 OR (end_date_active >= start_date)))
177 OR ((start_date_active >= start_date)
178 AND (start_date_active <= end_date)))
179 AND parent_object_id = l_object_id
180 AND genealogy_type = 5);
181 EXCEPTION
182 WHEN OTHERS THEN
183 NULL;
184 END;
185 END IF;
186
187 -- if the parent asset is an immediate child of the given asset, return 1 and exit
188
189 IF l_dummy = 1 THEN
190 RETURN 1;
191 ELSE
192 -- otherwise recursively traverse along each of the child
193
194 IF end_date IS NULL THEN
195 FOR object_id_rec IN (SELECT object_id
196 , start_date_active
197 , end_date_active
198 FROM mtl_object_genealogy
199 WHERE genealogy_type = 5
200 AND parent_object_id = l_object_id
201 AND (end_date_active IS NULL OR end_date_active >= start_date))
202 LOOP
203 l_dummy_char := 'N';
204 i := 1;
205
206 WHILE (i <= object_table.COUNT
207 AND (object_id_rec.object_id <> object_table(i).object_id
208 OR object_id_rec.start_date_active <> object_table(i).start_date_active
209 OR object_id_rec.end_date_active <> object_table(i).end_date_active))
210 LOOP
211 i := i + 1;
212 END LOOP;
213
214 IF i <= object_table.COUNT THEN
215 l_dummy_char := 'Y';
216 END IF;
217
218 IF l_dummy_char <> 'Y' THEN
219 l_count := object_table.COUNT + 1;
220
221 SELECT object_id_rec.object_id
222 , object_id_rec.start_date_active
223 , object_id_rec.end_date_active
224 INTO object_table(l_count).object_id
225 , object_table(l_count).start_date_active
226 , object_table(l_count).end_date_active
227 FROM DUAL;
228
229 IF (object_id_rec.start_date_active > end_date)
230 OR (object_id_rec.end_date_active < start_date) THEN
231 NULL;
232 ELSE
233 IF start_date > object_id_rec.start_date_active THEN
234 l_start_date := start_date;
235 ELSE
236 l_start_date := object_id_rec.start_date_active;
237 END IF;
238
239 IF end_date > object_id_rec.end_date_active THEN
240 l_end_date := object_id_rec.end_date_active;
241 ELSE
242 l_end_date := end_date;
243 END IF;
244
245 l_dummy := genealogy_loop(object_id_rec.object_id, parent_object_id, l_start_date, l_end_date, object_table);
246
247 IF l_dummy = 1 THEN
248 RETURN 1;
249 END IF;
250 END IF;
251 END IF;
252 END LOOP;
253 ELSE
254 FOR object_id_rec IN (SELECT object_id
255 , start_date_active
256 , end_date_active
257 FROM mtl_object_genealogy
258 WHERE genealogy_type = 5
259 AND parent_object_id = l_object_id
260 AND (((start_date_active <= start_date)
261 AND (end_date_active IS NULL
262 OR (end_date_active >= start_date)))
263 OR ((start_date_active >= start_date)
264 AND (start_date_active <= end_date))))
265 LOOP
266 l_dummy_char := 'N';
267 i := 1;
268 l_count := object_table.COUNT;
269
270 WHILE (i <= l_count
271 AND (object_id_rec.object_id <> object_table(i).object_id
272 OR object_id_rec.start_date_active <> object_table(i).start_date_active
273 OR object_id_rec.end_date_active <> object_table(i).end_date_active))
274 LOOP
275 i := i + 1;
276 END LOOP;
277
278 IF i <= l_count THEN
279 l_dummy_char := 'Y';
280 END IF;
281
282 IF l_dummy_char <> 'Y' THEN
283 SELECT object_id_rec.object_id
284 , object_id_rec.start_date_active
285 , object_id_rec.end_date_active
286 INTO object_table(l_count + 1).object_id
287 , object_table(l_count + 1).start_date_active
288 , object_table(l_count + 1).end_date_active
289 FROM DUAL;
290
291 IF (object_id_rec.start_date_active > end_date)
292 OR (object_id_rec.end_date_active < start_date) THEN
293 NULL;
294 ELSE
295 IF start_date > object_id_rec.start_date_active THEN
296 l_start_date := start_date;
297 ELSE
298 l_start_date := object_id_rec.start_date_active;
299 END IF;
300
301 IF end_date > object_id_rec.end_date_active THEN
302 l_end_date := object_id_rec.end_date_active;
303 ELSE
304 l_end_date := end_date;
305 END IF;
306
307 l_dummy := genealogy_loop(object_id_rec.object_id, parent_object_id, l_start_date, l_end_date, object_table);
308
309 IF l_dummy = 1 THEN
310 RETURN 1;
311 END IF;
312 END IF;
313 END IF;
314 END LOOP;
315 END IF;
316 END IF;
317
318 RETURN 0;
319 END;
320
321 PROCEDURE insert_genealogy(
322 p_api_version IN NUMBER
323 , p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
324 , p_commit IN VARCHAR2 := gen_fnd_g_false
325 , p_validation_level IN NUMBER := gen_fnd_valid_level_full
326 , p_object_type IN NUMBER
327 , p_parent_object_type IN NUMBER := NULL
328 , p_object_id IN NUMBER := NULL
329 , p_object_number IN VARCHAR2 := NULL
330 , p_inventory_item_id IN NUMBER := NULL
331 , p_org_id IN NUMBER := NULL
332 , p_parent_object_id IN NUMBER := NULL
333 , p_parent_object_number IN VARCHAR2 := NULL
334 , p_parent_inventory_item_id IN NUMBER := NULL
335 , p_parent_org_id IN NUMBER := NULL
336 , p_genealogy_origin IN NUMBER := NULL
337 , p_genealogy_type IN NUMBER := NULL
338 , p_start_date_active IN DATE := SYSDATE
339 , p_end_date_active IN DATE := NULL
340 , p_origin_txn_id IN NUMBER := NULL
341 , p_update_txn_id IN NUMBER := NULL
342 , x_return_status OUT NOCOPY VARCHAR2
343 , x_msg_count OUT NOCOPY NUMBER
344 , x_msg_data OUT NOCOPY VARCHAR2
345 , p_object_type2 IN NUMBER := NULL -- R12 Genealogy Enhancements
346 , p_object_id2 IN NUMBER := NULL -- R12 Genealogy Enhancements
347 , p_object_number2 IN VARCHAR2 := NULL -- R12 Genealogy Enhancements
348 , p_parent_object_type2 IN NUMBER := NULL -- R12 Genealogy Enhancements
349 , p_parent_object_id2 IN NUMBER := NULL -- R12 Genealogy Enhancements
350 , p_parent_object_number2 IN VARCHAR2 := NULL -- R12 Genealogy Enhancements
351 , p_child_lot_control_code IN NUMBER := NULL -- R12 Genealogy Enhancements
352 , p_parent_lot_control_code IN NUMBER := NULL -- R12 Genealogy Enhancements
353 ) IS
354 l_api_version CONSTANT NUMBER := 1.0;
355 l_api_name CONSTANT VARCHAR2(30) := 'insert_genealogy';
356 l_dummy NUMBER := 0;
360 l_dummy_date2 DATE;
357 l_dummy_char VARCHAR2(30);
358 l_dummy_num NUMBER := 0;
359 l_dummy_date DATE;
361 l_org_id NUMBER;
362 retval NUMBER;
363 l_parent_org_id NUMBER;
364 l_object_id NUMBER;
365 l_parent_object_id NUMBER;
366 l_object_id2 NUMBER;
367 l_parent_object_id2 NUMBER;
368 l_object_type2 NUMBER;
369 l_parent_object_type2 NUMBER;
370 l_parent_object_type NUMBER;
371 l_parent_inventory_item_id NUMBER;
372 l_inventory_item_id NUMBER;
373 l_child_item_type NUMBER;
374 l_parent_item_type NUMBER;
375 l_object_table object_id_tbl_t;
376 l_serial_number VARCHAR2(30);
377 l_instance_number VARCHAR2(30);
378 l_parent_instance_number VARCHAR2(30);
379 l_debug NUMBER :=1 ; -- := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
380 l_invalid_field_msg VARCHAR2(50);
381 l_invalid_comb_msg VARCHAR2(150);
382 l_child_lot_control_code NUMBER; -- R12
383 l_parent_lot_control_code NUMBER; -- R12
384 l_action VARCHAR2(10);
385 l_end_date_active DATE;
386 l_return_status VARCHAR2(10);
387 l_msg_count NUMBER;
388 l_msg_data VARCHAR2(4000);
389 BEGIN
390 -- Standard Start of API savepoint
391 x_return_status := lg_ret_sts_success;
392 SAVEPOINT save_insert_genealogy;
393 g_mod_name := 'Insert_Genealogy';
394
395 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
396 RAISE lg_exc_unexpected_error;
397 END IF;
398
399 IF (l_debug = 1) THEN
400 mydebug('in procedure ...: ' || g_mod_name );
401 mydebug('p_api_version: ' || p_api_version );
402 mydebug('p_init_msg_list: ' || p_init_msg_list );
403 mydebug('p_commit: ' || p_commit );
404 mydebug('p_validation_level: ' || p_validation_level );
405 mydebug('p_object_type: ' || p_object_type );
406 mydebug('p_parent_object_type: ' || p_parent_object_type );
407 mydebug('p_object_id: ' || p_object_id );
408 mydebug('p_object_number: ' || p_object_number );
409 mydebug('p_inventory_item_id: ' || p_inventory_item_id );
410 mydebug('p_org_id: ' || p_org_id );
411 mydebug('p_parent_object_id: ' || p_parent_object_id );
412 mydebug('p_parent_object_number: ' || p_parent_object_number );
413 mydebug('p_parent_inventory_item_id: ' || p_parent_inventory_item_id );
414 mydebug('p_parent_org_id: ' || p_parent_org_id );
415 mydebug('p_genealogy_origin: ' || p_genealogy_origin );
416 mydebug('p_genealogy_type: ' || p_genealogy_type );
417 mydebug('p_start_date_active: ' || p_start_date_active );
418 mydebug('p_end_date_active: ' || p_end_date_active );
419 mydebug('p_origin_txn_id: ' || p_origin_txn_id );
420 mydebug('p_update_txn_id: ' || p_update_txn_id );
421 mydebug('p_object_type2: ' || p_object_type2 );
422 mydebug('p_object_id2: ' || p_object_id2 );
423 mydebug('p_object_number2: ' || p_object_number2 );
424 mydebug('p_parent_object_type2: ' || p_parent_object_type2 );
425 mydebug('p_parent_object_id2: ' || p_parent_object_id2 );
426 mydebug('p_parent_object_number2: ' || p_parent_object_number2 );
427 mydebug('p_child_lot_control_code: ' || p_child_lot_control_code );
428 mydebug('p_parent_lot_control_code: ' || p_parent_lot_control_code );
429 END IF;
430
431 IF fnd_api.to_boolean(p_init_msg_list) THEN
432 fnd_msg_pub.initialize;
433 END IF;
434
435 -- Initialize API return status to success
436 IF p_parent_object_type IS NULL THEN
437 l_parent_object_type := p_object_type;
438 ELSE
439 l_parent_object_type := p_parent_object_type;
440 END IF;
441
442 IF p_parent_org_id IS NULL THEN
443 l_parent_org_id := p_org_id;
444 ELSE
445 l_parent_org_id := p_parent_org_id;
446 END IF;
447
448 l_action := 'INSERT';
449
450 l_object_id2 := p_object_id2;
451 l_parent_object_id2 := p_parent_object_id2;
452 l_object_type2 := p_object_type2;
453 l_parent_object_type2 := p_parent_object_type2;
454 l_object_id := p_object_id;
455 l_parent_object_id := p_parent_object_id;
456
457 IF (l_debug = 1) THEN
458 mydebug('l_parent_object_type: ' || l_parent_object_type );
459 mydebug('l_parent_org_id: ' || l_parent_org_id );
460 mydebug('l_object_id2: ' || l_object_id2 );
461 mydebug('l_parent_object_id2: ' || l_parent_object_id2 );
462 mydebug('l_object_type2w: ' || l_object_type2 );
463 mydebug('l_parent_object_type2: ' || l_parent_object_type2 );
464 mydebug('l_object_id: ' || l_object_id );
465 mydebug('jel_parent_object_id: ' || l_parent_object_id );
466 END IF;
467
468
469 -- R12 Genealogy Enhancements : Moved all the validations to the new procedure parameter_validations
470 -- Check for the parameters
471 parameter_validations(
472 p_validation_level => p_validation_level
473 , p_object_type => p_object_type
474 , p_parent_object_type => l_parent_object_type
478 , p_org_id => p_org_id
475 , p_object_id => l_object_id
476 , p_object_number => p_object_number
477 , p_inventory_item_id => p_inventory_item_id
479 , p_parent_object_id => l_parent_object_id
480 , p_parent_object_number => p_parent_object_number
481 , p_parent_inventory_item_id => p_parent_inventory_item_id
482 , p_parent_org_id => l_parent_org_id
483 , p_genealogy_origin => p_genealogy_origin
484 , p_genealogy_type => p_genealogy_type
485 , p_start_date_active => p_start_date_active
486 , p_end_date_active => p_end_date_active
487 , p_origin_txn_id => p_origin_txn_id
488 , p_update_txn_id => p_update_txn_id
489 , p_object_type2 => l_object_type2
490 , p_object_id2 => l_object_id2
491 , p_object_number2 => p_object_number2
492 , p_parent_object_type2 => l_parent_object_type2
493 , p_parent_object_id2 => l_parent_object_id2
494 , p_parent_object_number2 => p_parent_object_number2
495 , p_child_lot_control_code => p_child_lot_control_code
496 , p_parent_lot_control_code => p_parent_lot_control_code
497 , p_action => l_action
498 , p_debug => l_debug
499 , x_return_status => x_return_status
500 , x_msg_count => x_msg_count
501 , x_msg_data => x_msg_data);
502
503 g_mod_name := 'Insert_Genealogy';
504
505 IF (l_debug = 1) THEN mydebug('x_return_status from parameter_validations API: ' || x_return_status); END IF;
506
507 IF x_return_status = lg_ret_sts_error
508 THEN
509 IF (l_debug = 1) THEN
510 mydebug('{{ If any of the parameter validations failed, then raise ' ||
511 'an expected exception now..before inserting into genealogy }}' );
512 END IF;
513 RAISE lg_exc_error;
514 END IF;
515 IF x_return_status = lg_ret_sts_unexp_error
516 THEN
517 IF (l_debug = 1) THEN mydebug('{{ If any of the parameter validations failed, then raise ' ||
518 'an unexpected exception now..before inserting into genealogy }}');
519 END IF;
520 RAISE lg_exc_unexpected_error;
521 END IF;
522
523
524 IF (l_debug = 1) THEN
525 mydebug('After calling parameter validations, check the value of IN OUT parameters ' );
526 mydebug('l_object_id := ' || l_object_id );
527 mydebug('l_parent_object_id := ' || l_parent_object_id);
528 mydebug('l_object_type2 := ' || l_object_type2);
529 mydebug('l_object_id2 := ' || l_object_id2);
530 mydebug('l_parent_object_type2 := ' || l_parent_object_type2) ;
531 mydebug('l_parent_object_id2 := ' || l_parent_object_id2) ;
532 END IF;
533
534 -- Eam Validations Starts here
535 -- if EAM data, do EAM genealogy validations
536 IF (p_genealogy_type = 5) THEN
537 IF (l_debug = 1) THEN mydebug(' Start of EAM Validations'); END IF;
538 -- validate that the parent and child are different objects
539 IF l_object_id = l_parent_object_id THEN
540 fnd_message.set_name('INV', 'INV_EAM_GENEALOGY_SAME_CH_PAR');
541 fnd_msg_pub.ADD;
542 RAISE lg_exc_error;
543 END IF;
544
545 -- determine the child org
546 IF p_org_id IS NOT NULL THEN
547 l_org_id := p_org_id;
548 ELSE
549 SELECT msn.current_organization_id
550 INTO l_org_id
551 FROM mtl_serial_numbers msn
552 WHERE msn.gen_object_id = l_object_id;
553 END IF;
554
555 -- determine the parent org
556 IF p_parent_org_id IS NOT NULL THEN
557 l_parent_org_id := p_parent_org_id;
558 ELSE
559 SELECT msn.current_organization_id
560 INTO l_parent_org_id
561 FROM mtl_serial_numbers msn
562 WHERE msn.gen_object_id = l_parent_object_id;
563 END IF;
564
565 -- validate that the start date is not null
566 IF p_start_date_active IS NULL THEN
567 fnd_message.set_name('INV', 'INV_EAM_GEN_NULL_START_DATE');
568 fnd_msg_pub.ADD;
569 RAISE lg_exc_error;
570 END IF;
571
572 -- validate that the end date, if not null, is greater than the start date
573 IF p_end_date_active IS NOT NULL THEN
574 IF p_start_date_active > p_end_date_active THEN
575 fnd_message.set_name('INV', 'INV_EAM_START_END_DATE_INVALID');
576 fnd_message.set_token('ENTITY1', TO_CHAR(p_start_date_active, 'DD-MON-YYYY HH24:MI:SS'));
577 fnd_message.set_token('ENTITY2', TO_CHAR(p_end_date_active, 'DD-MON-YYYY HH24:MI:SS'));
578 fnd_msg_pub.ADD;
579 RAISE lg_exc_error;
580 END IF;
581 END IF;
582
583 -- ***EAM change to allow rebuildables to be parents***
584 -- check to see if the parent object is a rebuildable.
585 -- If so it cannot have an asset child
586
587
588 IF p_parent_inventory_item_id IS NOT NULL THEN
589 l_parent_inventory_item_id := p_parent_inventory_item_id;
590 ELSE
594 WHERE msn.gen_object_id = l_parent_object_id;
591 SELECT msn.inventory_item_id
592 INTO l_parent_inventory_item_id
593 FROM mtl_serial_numbers msn
595 END IF;
596
597 SELECT msi.eam_item_type
598 INTO l_parent_item_type
599 FROM mtl_system_items msi
600 WHERE msi.inventory_item_id = l_parent_inventory_item_id
601 AND msi.organization_id = l_parent_org_id;
602
603 IF l_parent_item_type IS NULL THEN
604 fnd_message.set_name('INV', 'INV_EAM_PARENT_ITEM_TYPE');
605 fnd_msg_pub.ADD;
606 RAISE lg_exc_error;
607 END IF;
608
609 IF p_inventory_item_id IS NOT NULL THEN
610 l_inventory_item_id := p_inventory_item_id;
611 ELSE
612 SELECT msn.inventory_item_id
613 INTO l_inventory_item_id
614 FROM mtl_serial_numbers msn
615 WHERE msn.gen_object_id = l_object_id;
616 END IF;
617
618 SELECT msi.eam_item_type
619 INTO l_child_item_type
620 FROM mtl_system_items msi
621 WHERE msi.inventory_item_id = l_inventory_item_id
622 AND msi.organization_id = l_org_id;
623
624 IF l_child_item_type IS NULL THEN
625 fnd_message.set_name('INV', 'INV_EAM_CHILD_ITEM_TYPE');
626 fnd_msg_pub.ADD;
627 RAISE lg_exc_error;
628 END IF;
629
630
631 /* Start R12 changes made by Himal -- EAM group
632 -- rebuildables cannot be parents of assets
633 IF ((l_parent_item_type = 3)
634 AND (l_child_item_type = 1)
635 ) THEN
636 fnd_message.set_name('INV', 'INV_EAM_ASSET_REBUILD_PARENT');
637 fnd_msg_pub.ADD;
638 RAISE lg_exc_error;
639 END IF;
640
641 -- *** End of eam change to allow parent rebuildables ***
642
643 END --R12 changes made by Himal -- EAM group */
644
645 -- validate origin transaction id with the genealogy_origin
646 -- If origin transaction id is null then genealogy origin
647 -- should show that it was a manual entry
648 IF p_origin_txn_id IS NULL THEN
649 IF p_genealogy_origin <> 3 THEN
650 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
651 fnd_message.set_token('ENTITY1', 'p_genealogy_origin');
652 fnd_msg_pub.ADD;
653 RAISE lg_exc_error;
654 END IF;
655 END IF;
656
657 SELECT serial_number
658 INTO l_serial_number
659 FROM mtl_serial_numbers
660 WHERE gen_object_id = l_object_id;
661
662 DECLARE
663 CURSOR genealogy_entry_cur IS
664 SELECT mog.start_date_active start_date_active
665 , mog.end_date_active end_date_active
666 , msn.serial_number parent_serial_number
667 , msn.inventory_item_id parent_inventory_item_id
668 FROM mtl_object_genealogy mog, mtl_serial_numbers msn
669 WHERE mog.object_id = l_object_id
670 AND msn.gen_object_id = mog.parent_object_id
671 AND mog.genealogy_type = 5;
672 BEGIN
673 FOR i IN genealogy_entry_cur LOOP
674 IF i.end_date_active IS NOT NULL THEN
675 IF p_end_date_active IS NOT NULL THEN
676 IF ((p_start_date_active <= i.start_date_active)
677 AND (p_end_date_active >= i.start_date_active)
678 )
679 OR ((p_start_date_active >= i.start_date_active)
680 AND (p_end_date_active <= i.end_date_active)
681 )
682 OR ((p_start_date_active <= i.start_date_active)
683 AND (p_end_date_active >= i.end_date_active)
684 )
685 OR ((p_start_date_active <= i.end_date_active)
686 AND (p_end_date_active >= i.end_date_active)
687 ) THEN
688
689
690 begin
691 select instance_number into l_instance_number
692 from csi_item_instances
693 where serial_number = l_serial_number
694 and inventory_item_id = l_inventory_item_id
695 and last_vld_organization_id = l_org_id;
696
697 select instance_number into l_parent_instance_number
698 from csi_item_instances
699 where serial_number = i.parent_serial_number
700 and inventory_item_id = i.parent_inventory_item_id
701 and last_vld_organization_id = l_parent_org_id;
702
703 end;
704
705 fnd_message.set_name('INV', 'INV_EAM_DATE_OVERLAP');
706 fnd_message.set_token('ENTITY1', l_instance_number);
707 fnd_message.set_token('ENTITY2', l_parent_instance_number);
708 fnd_message.set_token('ENTITY3', TO_CHAR(i.start_date_active, 'DD-MON-YYYY HH24:MI:SS'));
709 fnd_message.set_token('ENTITY4', TO_CHAR(i.end_date_active, 'DD-MON-YYYY HH24:MI:SS'));
710 fnd_message.set_token('ENTITY5', TO_CHAR(p_start_date_active, 'DD-MON-YYYY HH24:MI:SS'));
711 fnd_message.set_token('ENTITY6', TO_CHAR(p_end_date_active, 'DD-MON-YYYY HH24:MI:SS'));
712 fnd_msg_pub.ADD;
713 RAISE lg_exc_error;
714 END IF;
715 ELSE
716 IF (p_start_date_active <= i.end_date_active) THEN
717
718 begin
719 select instance_number into l_instance_number
720 from csi_item_instances
724
721 where serial_number = l_serial_number
722 and inventory_item_id = l_inventory_item_id
723 and last_vld_organization_id = l_org_id;
725 select instance_number into l_parent_instance_number
726 from csi_item_instances
727 where serial_number = i.parent_serial_number
728 and inventory_item_id = i.parent_inventory_item_id
729 and last_vld_organization_id = l_parent_org_id;
730
731 end;
732
733 fnd_message.set_name('INV', 'INV_EAM_DATE_OVERLAP2');
734 fnd_message.set_token('ENTITY1', l_instance_number);
735 fnd_message.set_token('ENTITY2', l_parent_instance_number);
736 fnd_message.set_token('ENTITY3', TO_CHAR(i.start_date_active, 'DD-MON-YYYY HH24:MI:SS'));
737 fnd_message.set_token('ENTITY4', TO_CHAR(i.end_date_active, 'DD-MON-YYYY HH24:MI:SS'));
738 fnd_message.set_token('ENTITY5', TO_CHAR(p_start_date_active, 'DD-MON-YYYY HH24:MI:SS'));
739 fnd_msg_pub.ADD;
740 RAISE lg_exc_error;
741 END IF;
742 END IF;
743 ELSE
744 IF p_end_date_active IS NULL THEN
745
746 begin
747 select instance_number into l_instance_number
748 from csi_item_instances
749 where serial_number = l_serial_number
750 and inventory_item_id = l_inventory_item_id
751 and last_vld_organization_id = l_org_id;
752
753 select instance_number into l_parent_instance_number
754 from csi_item_instances
755 where serial_number = i.parent_serial_number
756 and inventory_item_id = i.parent_inventory_item_id
757 and last_vld_organization_id = l_parent_org_id;
758
759 end;
760
761 fnd_message.set_name('INV', 'INV_EAM_DATE_OVERLAP3');
762 fnd_message.set_token('ENTITY1', l_instance_number);
763 fnd_message.set_token('ENTITY2', l_parent_instance_number);
764 fnd_message.set_token('ENTITY3', TO_CHAR(i.start_date_active, 'DD-MON-YYYY HH24:MI:SS'));
765 fnd_message.set_token('ENTITY4', TO_CHAR(p_start_date_active, 'DD-MON-YYYY HH24:MI:SS'));
766 fnd_msg_pub.ADD;
767 RAISE lg_exc_error;
768 ELSIF (p_start_date_active >= i.start_date_active)
769 OR (p_end_date_active >= i.start_date_active) THEN
770
771 begin
772 select instance_number into l_instance_number
773 from csi_item_instances
774 where serial_number = l_serial_number
775 and inventory_item_id = l_inventory_item_id
776 and last_vld_organization_id = l_org_id;
777
778 select instance_number into l_parent_instance_number
779 from csi_item_instances
780 where serial_number = i.parent_serial_number
781 and inventory_item_id = i.parent_inventory_item_id
782 and last_vld_organization_id = l_parent_org_id;
783
784 end;
785
786 fnd_message.set_name('INV', 'INV_EAM_DATE_OVERLAP1');
787 fnd_message.set_token('ENTITY1', l_instance_number);
788 fnd_message.set_token('ENTITY2', l_parent_instance_number);
789 fnd_message.set_token('ENTITY3', TO_CHAR(i.start_date_active, 'DD-MON-YYYY HH24:MI:SS'));
790 fnd_message.set_token('ENTITY4', TO_CHAR(p_start_date_active, 'DD-MON-YYYY HH24:MI:SS'));
791 fnd_message.set_token('ENTITY5', TO_CHAR(p_end_date_active, 'DD-MON-YYYY HH24:MI:SS'));
792 fnd_msg_pub.ADD;
793 RAISE lg_exc_error;
794 END IF;
795 END IF;
796 END LOOP;
797 END;
798
799 -- check for genealogy loops i.e. the parent object is not a child of the object
800 DECLARE
801 l_parent_serial_number VARCHAR2(30);
802 BEGIN
803 SELECT l_object_id
804 INTO l_object_table(1).object_id
805 FROM DUAL;
806
807 -- call to the function that checks for the genealogy loop
808 retval := genealogy_loop(l_object_id, l_parent_object_id,
809 p_start_date_active, p_end_date_active, l_object_table);
810
811 IF retval = 1 THEN
812 SELECT serial_number, inventory_item_id
813 INTO l_parent_serial_number, l_parent_inventory_item_id
814 FROM mtl_serial_numbers msn
815 WHERE gen_object_id = l_parent_object_id;
816
817 begin
818 select instance_number into l_instance_number
819 from csi_item_instances
820 where serial_number = l_serial_number
821 and inventory_item_id = l_inventory_item_id
822 and last_vld_organization_id = l_org_id;
823
824 select instance_number into l_parent_instance_number
825 from csi_item_instances
826 where serial_number = l_parent_serial_number
827 and inventory_item_id = l_parent_inventory_item_id
828 and last_vld_organization_id = l_parent_org_id;
829
830 end;
831
832 fnd_message.set_name('INV', 'INV_EAM_GENEALOGY_LOOP');
833 fnd_message.set_token('ENTITY1', l_parent_instance_number);
834 fnd_message.set_token('ENTITY2', l_instance_number);
835 fnd_msg_pub.ADD;
836 RAISE lg_exc_error;
837 END IF;
838 EXCEPTION
839 WHEN NO_DATA_FOUND THEN
840 NULL;
841 END;
842 IF (l_debug = 1) THEN mydebug(' After EAM Validations'); END IF;
846
843 ELSE
844 IF (l_debug = 1) THEN mydebug(' Not EAM Type'); END IF;
845 END IF;
847 -- EAM Validations Ends here
848
849
850 /* Fix bug 2138294, in EAM, object is not inserted into genealogy table
851 Reason: l_dummy = 0 because there is existing parent/child relationship
852 Fix: For EAM, it is allowed to have existing parent/child relationship,
853 Added 'AND GENEALOGY_TYPE <> 5' to not include genealogy_type of 5 (Assets) */
854
855 /* bug 2712800 The genealogy was not getting built when the serial number
856 is completed the second time. Add the condition 'end_date_active is null'
857 so that the genealogy is built if the end_date_active is already marked.
858 For serials, end_date_active is marked with the sysdate once the serial
859 number is returned (Serial-Tracking in WIP)*/
860
861 l_dummy := -999;
862
863 -- R12 Genealogy Enhancements:
864 -- If second set of object details exist then we should check the existence
865 -- of relationship using first set + second set
866 IF l_object_id2 IS NULL AND l_parent_object_id2 IS NULL THEN
867 IF (l_debug = 1) THEN
868 mydebug('{{- Genealogy is not between lot+serial controlled items }}');
869 END IF;
870 SELECT COUNT(*)
871 INTO l_dummy
872 FROM mtl_object_genealogy
873 WHERE object_id = l_object_id
874 AND object_id2 IS NULL -- added this for lot+serial controlled items
875 AND parent_object_id = l_parent_object_id
876 AND parent_object_id2 IS NULL
877 AND end_date_active IS NULL
878 AND genealogy_type <> 5;
879 ELSIF l_object_id2 IS NULL AND l_parent_object_id2 IS NOT NULL THEN
880 IF (l_debug = 1) THEN
881 mydebug('{{- Genealogy is between non lot+serial child and lot+serial parent}}');
882 END IF;
883 SELECT COUNT(*)
884 INTO l_dummy
885 FROM mtl_object_genealogy
886 WHERE object_id = l_object_id
887 AND object_id2 IS NULL -- added this for lot+serial controlled items
888 AND parent_object_id = l_parent_object_id
889 AND parent_object_id2 = l_parent_object_id2
890 AND end_date_active IS NULL
891 AND genealogy_type <> 5;
892 ELSIF l_object_id2 IS NOT NULL AND l_parent_object_id2 IS NULL THEN
893 IF (l_debug = 1) THEN
894 mydebug(' {{- Genealogy is between lot+serial child and non lot+serial parent }}');
895 END IF;
896 SELECT COUNT(*)
897 INTO l_dummy
898 FROM mtl_object_genealogy
899 WHERE object_id = l_object_id
900 AND object_id2 = l_object_id2 -- added this for lot+serial controlled items
901 AND parent_object_id = l_parent_object_id
902 AND parent_object_id2 IS NULL
903 AND end_date_active IS NULL
904 AND genealogy_type <> 5;
905 ELSIF l_object_id2 IS NOT NULL AND l_parent_object_id2 IS NOT NULL THEN
906 IF (l_debug = 1) THEN
907 mydebug(' {{- Genealogy is between lot+serial child and lot+serial parent }}');
908 END IF;
909 SELECT COUNT(*)
910 INTO l_dummy
911 FROM mtl_object_genealogy
912 WHERE object_id = l_object_id
913 AND object_id2 = l_object_id2 -- added this for lot+serial controlled items
914 AND parent_object_id = l_parent_object_id
915 AND parent_object_id2 = l_parent_object_id2
916 AND end_date_active IS NULL
917 AND genealogy_type <> 5;
918 END IF ;
919
920 IF (l_debug = 1) THEN
921 mydebug('l_dummy(count of relatioships): ' || l_dummy );
922 END IF ;
923
924 IF (l_dummy = 0) THEN
925 -- No need of this condition AND (l_object_id <> l_parent_object_id)
926 IF (l_debug = 1) THEN
927 mydebug('{{- Only if the relationship does not exist that a new record is }}' ||
928 '{{ inserted in mtl_object_genealogy for the given l_object_id, l_object_id2 }} ' ||
929 '{{ and l_parent_object_id, l_parent_object_id2 combination }}' );
930 mydebug('{{- If the relationship is between lot+serial controlled item, make sure }}' ||
931 '{{ that columns with 2 as suffix are also populated in the table }}'
932 );
933 END IF ;
934 INSERT INTO mtl_object_genealogy
935 (
936 object_id
937 , object_type
938 , object_id2 -- R12 Genealogy Enhancements
939 , object_type2 -- R12 Genealogy Enhancements
940 , parent_object_type
941 , parent_object_id
942 , parent_object_type2 -- R12 Genealogy Enhancements
943 , parent_object_id2 -- R12 Genealogy Enhancements
944 , last_update_date
945 , last_updated_by
946 , creation_date
947 , created_by
948 , start_date_active
949 , end_date_active
950 , genealogy_origin
951 , origin_txn_id
952 , update_txn_id
953 , genealogy_type
954 , last_update_login
958 , attribute3
955 , attribute_category
956 , attribute1
957 , attribute2
959 , attribute4
960 , attribute5
961 , attribute6
962 , attribute7
963 , attribute8
964 , attribute9
965 , attribute10
966 , attribute11
967 , attribute12
968 , attribute13
969 , attribute14
970 , attribute15
971 , request_id
972 , program_application_id
973 , program_id
974 , program_update_date
975 )
976 VALUES (
977 l_object_id
978 , p_object_type
979 , l_object_id2 -- R12Genealogy Enhancements
980 , l_object_type2 -- R12Genealogy Enhancements
981 , p_parent_object_type
982 , l_parent_object_id
983 , l_parent_object_type2 -- R12Genealogy Enhancements
984 , l_parent_object_id2 -- R12Genealogy Enhancements
985 , SYSDATE
986 , -1
987 , SYSDATE + 10
988 , fnd_global.user_id
989 , p_start_date_active
990 , p_end_date_active
991 , p_genealogy_origin
992 , p_origin_txn_id
993 , p_update_txn_id
994 , p_genealogy_type
995 , -1
996 , NULL
997 , NULL
998 , NULL
999 , NULL
1000 , NULL
1001 , NULL
1002 , NULL
1003 , NULL
1004 , NULL
1005 , NULL
1006 , NULL
1007 , NULL
1008 , NULL
1009 , NULL
1010 , NULL
1011 , NULL
1012 , fnd_global.conc_request_id
1013 , fnd_global.prog_appl_id
1014 , fnd_global.conc_program_id
1015 , SYSDATE
1016 );
1017
1018 IF (l_debug = 1) THEN mydebug( 'Inserted a New Record ' ); END IF;
1019
1020 --End of API body.
1021 -- Standard check of p_commit.
1022 IF fnd_api.to_boolean(p_commit) THEN
1023 COMMIT WORK;
1024 IF (l_debug = 1) THEN mydebug( 'Commit work' ); END IF;
1025 END IF;
1026
1027 -- Standard call to get message count and if count is 1, get message info.
1028 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1029 ELSE
1030 IF (l_debug = 1) THEN mydebug( 'Record Already present ' ); END IF;
1031 END IF;
1032 mydebug('Out of procedure ...: ' || g_mod_name );
1033 EXCEPTION
1034 WHEN lg_exc_error THEN
1035 IF (l_debug = 1) THEN
1036 mydebug('exception G_EXC_ERROR'|| x_msg_data);
1037 END IF;
1038
1039 ROLLBACK TO apiinsert_genealogy_apipub;
1040 x_return_status := lg_ret_sts_error;
1041 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
1042 WHEN lg_exc_unexpected_error THEN
1043 IF (l_debug = 1) THEN
1044 mydebug('exception G_UNEXC_ERROR'|| x_msg_data);
1045 END IF;
1046
1047 ROLLBACK TO apiinsert_genealogy_apipub;
1048 x_return_status := lg_ret_sts_unexp_error;
1049 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
1050 WHEN OTHERS THEN
1051 IF (l_debug = 1) THEN
1052 mydebug('exception WHEN OTHERS'|| x_msg_data);
1053 END IF;
1054
1055 ROLLBACK TO apiinsert_genealogy_apipub;
1056 x_return_status := lg_ret_sts_unexp_error;
1057
1058 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1059 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1060 END IF;
1061
1062 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
1063 END insert_genealogy;
1064
1065 PROCEDURE update_genealogy(
1066 p_api_version IN NUMBER
1067 , p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
1068 , p_commit IN VARCHAR2 := gen_fnd_g_false
1069 , p_validation_level IN NUMBER := gen_fnd_valid_level_full
1070 , p_object_type IN NUMBER
1071 , p_object_id IN NUMBER := NULL
1072 , p_object_number IN VARCHAR2 := NULL
1073 , p_inventory_item_id IN NUMBER := NULL
1074 , p_org_id IN NUMBER := NULL
1075 , p_genealogy_origin IN NUMBER := NULL
1076 , p_genealogy_type IN NUMBER := NULL
1077 , p_end_date_active IN DATE := NULL
1078 , p_update_txn_id IN NUMBER := NULL
1079 , x_return_status OUT NOCOPY VARCHAR2
1080 , x_msg_count OUT NOCOPY NUMBER
1081 , x_msg_data OUT NOCOPY VARCHAR2
1082 ) IS
1083 l_api_version CONSTANT NUMBER := 1.0;
1084 l_api_name CONSTANT VARCHAR2(30) := 'update_genealogy';
1085 l_dummy NUMBER;
1086 l_dummy_char VARCHAR2(30);
1087 l_dummy_date DATE;
1088 l_object_id NUMBER;
1092 BEGIN
1089 l_parent_object_id NUMBER;
1090 l_parent_object_type NUMBER;
1091 l_debug NUMBER :=1 ; -- := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1093 -- Standard Start of API savepoint
1094 SAVEPOINT update_genealogy_pub;
1095
1096 g_mod_name := 'Update Genealogy';
1097
1098 -- Standard call to check for call compatibility.
1099 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1100 RAISE lg_exc_unexpected_error;
1101 END IF;
1102
1103 -- Initialize message list if p_init_msg_list is set to TRUE.
1104 IF fnd_api.to_boolean(p_init_msg_list) THEN
1105 fnd_msg_pub.initialize;
1106 END IF;
1107
1108 -- Initialize API return status to success
1109 x_return_status := lg_ret_sts_success;
1110
1111 -- API body
1112 -- Check for the mandatory parameters
1113 IF p_object_type IS NULL THEN
1114 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1115 fnd_message.set_token('ATTRIBUTE', 'p_object_type');
1116 fnd_msg_pub.ADD;
1117 RAISE lg_exc_error;
1118 END IF;
1119
1120 IF p_object_id IS NULL THEN
1121 IF p_object_number IS NULL
1122 OR p_inventory_item_id IS NULL
1123 OR p_org_id IS NULL THEN
1124 IF p_object_number IS NULL THEN
1125 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1126 fnd_message.set_token('ATTRIBUTE', 'p_object_number');
1127 fnd_msg_pub.ADD;
1128 END IF;
1129
1130 IF p_inventory_item_id IS NULL THEN
1131 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1132 fnd_message.set_token('ATTRIBUTE', 'p_inventory_item_id');
1133 fnd_msg_pub.ADD;
1134 END IF;
1135
1136 IF p_org_id IS NULL THEN
1137 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1138 fnd_message.set_token('ATTRIBUTE', 'p_org_id');
1139 fnd_msg_pub.ADD;
1140 END IF;
1141
1142 RAISE lg_exc_error;
1143 END IF;
1144 END IF;
1145
1146 -- Object type can be either 1 (lot number) or 2 (serial Number).
1147 -- If invalid return an error.
1148 IF p_object_type < 1
1149 OR p_object_type > 2 THEN
1150 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1151 fnd_message.set_token('ENTITY1', 'p_object_type');
1152 fnd_msg_pub.ADD;
1153 RAISE lg_exc_error;
1154 END IF;
1155
1156 -- Validate the existence of object number in MTL_SERIAL_NUMBERS
1157 -- or MTL_LOT_NUMBERS depending on the object type.
1158 -- If object number is not found return an error.
1159 IF p_object_type = 1 THEN
1160 IF p_object_id IS NOT NULL THEN
1161 SELECT COUNT(*)
1162 INTO l_dummy
1163 FROM mtl_lot_numbers
1164 WHERE gen_object_id = p_object_id;
1165
1166 IF l_dummy = 0 THEN
1167 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1168 fnd_message.set_token('ENTITY1', 'p_object_id');
1169 fnd_msg_pub.ADD;
1170 RAISE lg_exc_error;
1171 END IF;
1172
1173 l_object_id := p_object_id;
1174 ELSE
1175 BEGIN
1176 SELECT gen_object_id
1177 INTO l_object_id
1178 FROM mtl_lot_numbers
1179 WHERE lot_number = p_object_number
1180 AND inventory_item_id = p_inventory_item_id
1181 AND organization_id = p_org_id;
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1185 fnd_message.set_token('ENTITY1', 'p_object_number, p_inventory_item_id and p_org_id combination');
1186 fnd_msg_pub.ADD;
1187 RAISE lg_exc_error;
1188 END;
1189 END IF;
1190 ELSIF p_object_type = 2 THEN
1191 IF p_object_id IS NOT NULL THEN
1192 SELECT COUNT(*)
1193 INTO l_dummy
1194 FROM mtl_serial_numbers
1195 WHERE gen_object_id = p_object_id;
1196
1197 IF l_dummy = 0 THEN
1198 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1199 fnd_message.set_token('ENTITY1', 'p_object_id');
1200 fnd_msg_pub.ADD;
1201 RAISE lg_exc_error;
1202 END IF;
1203
1204 l_object_id := p_object_id;
1205 ELSE
1206 BEGIN
1207 SELECT gen_object_id
1208 INTO l_object_id
1209 FROM mtl_serial_numbers
1210 WHERE serial_number = p_object_number
1211 AND inventory_item_id = p_inventory_item_id
1212 AND current_organization_id = p_org_id;
1213 EXCEPTION
1214 WHEN NO_DATA_FOUND THEN
1215 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1216 fnd_message.set_token('ENTITY1', 'p_object_number, p_inventory_item_id and p_org_id combination');
1217 fnd_msg_pub.ADD;
1218 RAISE lg_exc_error;
1219 END;
1220 END IF;
1221 END IF;
1222
1223 IF p_genealogy_origin IS NOT NULL THEN
1224 SELECT COUNT(*)
1225 INTO l_dummy
1226 FROM mfg_lookups
1227 WHERE lookup_type = 'INV_GENEALOGY_ORIGIN'
1228 AND lookup_code = p_genealogy_origin;
1229
1230 IF l_dummy = 0 THEN
1231 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1235 END IF;
1232 fnd_message.set_token('ENTITY1', 'p_genealogy_origin');
1233 fnd_msg_pub.ADD;
1234 RAISE lg_exc_error;
1236 END IF;
1237
1238 -- Validate values are :
1239 -- 1- Assembly component
1240 -- 2- Lot split
1241 -- 3- lot merge
1242 -- 5- Asset
1243 -- if p_object_type = 2 then p_genealogy_type of 1 is valid
1244 -- otherwise all of the above are valid
1245 IF p_genealogy_type NOT IN (1, 2, 3, 5)
1246 AND p_genealogy_type IS NOT NULL THEN
1247 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1248 fnd_message.set_token('ENTITY1', 'P_genealogy_type');
1249 fnd_msg_pub.ADD;
1250 RAISE lg_exc_error;
1251 END IF;
1252
1253 IF p_object_type = 2 THEN
1254 IF p_genealogy_type NOT IN (1, 5) THEN
1255 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1256 fnd_message.set_token('ENTITY1', 'P_genealogy_type');
1257 fnd_msg_pub.ADD;
1258 RAISE lg_exc_error;
1259 END IF;
1260 END IF;
1261
1262 -- If EAM object validate whether the object exists in the table
1263 -- If it does, check whether there exists a NULL end date for the last entry of the object
1264 IF p_genealogy_type = 5 THEN
1265 IF p_end_date_active IS NULL THEN
1266 fnd_message.set_name('INV', 'INV_EAM_NULL_END_DATE');
1267 fnd_msg_pub.ADD;
1268 RAISE lg_exc_error;
1269 END IF;
1270
1271 BEGIN
1272 SELECT 'Y'
1273 INTO l_dummy_char
1274 FROM DUAL
1275 WHERE EXISTS( SELECT *
1276 FROM mtl_object_genealogy
1277 WHERE genealogy_type = 5
1278 AND object_id = l_object_id
1279 AND end_date_active IS NULL);
1280
1281 SELECT start_date_active
1282 INTO l_dummy_date
1283 FROM mtl_object_genealogy
1284 WHERE genealogy_type = 5
1285 AND object_id = l_object_id
1286 AND end_date_active IS NULL;
1287
1288 IF (p_end_date_active < l_dummy_date) THEN
1289 fnd_message.set_name('INV', 'INV_EAM_END_START_DATE_INVALID');
1290 fnd_message.set_token('ENTITY1', TO_CHAR(p_end_date_active, 'DD-MON-YYYY HH24:MI:SS'));
1291 fnd_message.set_token('ENTITY2', TO_CHAR(l_dummy_date, 'DD-MON-YYYY HH24:MI:SS'));
1292 fnd_msg_pub.ADD;
1293 RAISE lg_exc_error;
1294 END IF;
1295 EXCEPTION
1296 WHEN NO_DATA_FOUND THEN
1297 fnd_message.set_name('INV', 'INV_EAM_GEN_NOEXIST');
1298 fnd_msg_pub.ADD;
1299 RAISE lg_exc_error;
1300 END;
1301 END IF;
1302
1303 UPDATE mtl_object_genealogy
1304 SET last_update_date = SYSDATE
1305 , last_updated_by = -1
1306 , end_date_active = p_end_date_active
1307 , update_txn_id = p_update_txn_id
1308 , last_update_login = -1
1309 , request_id = fnd_global.conc_request_id
1310 , program_application_id = fnd_global.prog_appl_id
1311 , program_id = fnd_global.conc_program_id
1312 , program_update_date = SYSDATE
1313 WHERE object_id = l_object_id
1314 AND end_date_active IS NULL;
1315
1316 -- End of API body.
1317 -- Standard check of p_commit.
1318 IF fnd_api.to_boolean(p_commit) THEN
1319 COMMIT WORK;
1320 END IF;
1321
1322 -- Standard call to get message count and if count is 1, get message info.
1323 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1324 EXCEPTION
1325 WHEN lg_exc_error THEN
1326 ROLLBACK TO update_genealogy_pub;
1327 x_return_status := lg_ret_sts_error;
1328 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
1329 WHEN lg_exc_unexpected_error THEN
1330 ROLLBACK TO update_genealogy_pub;
1331 x_return_status := lg_ret_sts_unexp_error;
1332 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
1333 WHEN OTHERS THEN
1334 ROLLBACK TO update_genealogy_pub;
1335 x_return_status := lg_ret_sts_unexp_error;
1336
1337 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1338 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1339 END IF;
1340
1341 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
1342 END update_genealogy;
1343
1344 PROCEDURE insert_flow_genealogy(
1345 p_api_version IN NUMBER
1346 , p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
1347 , p_commit IN VARCHAR2 := gen_fnd_g_false
1348 , p_validation_level IN NUMBER := gen_fnd_valid_level_full
1349 , p_transaction_source_id IN NUMBER
1350 , p_completion_transaction_id IN NUMBER
1351 , p_parent_object_id IN NUMBER := NULL
1352 , p_parent_object_number IN VARCHAR2 := NULL
1353 , p_parent_inventory_item_id IN NUMBER := NULL
1354 , p_parent_org_id IN NUMBER := NULL
1355 , p_genealogy_origin IN NUMBER := NULL
1356 , p_genealogy_type IN NUMBER := NULL
1357 , p_start_date_active IN DATE := SYSDATE
1358 , p_end_date_active IN DATE := NULL
1359 , p_origin_txn_id IN NUMBER := NULL
1363 , x_msg_data OUT NOCOPY VARCHAR2
1360 , p_update_txn_id IN NUMBER := NULL
1361 , x_return_status OUT NOCOPY VARCHAR2
1362 , x_msg_count OUT NOCOPY NUMBER
1364 --, debug_count OUT NUMBER
1365 ) IS
1366 l_transaction_action_id NUMBER := 1;
1367 l_transaction_source_type_id NUMBER := 5;
1368 l_api_version CONSTANT NUMBER := 1.0;
1369 l_api_name CONSTANT VARCHAR2(30) := 'insert_flow_genealogy';
1370
1371 CURSOR childlotmmtt(p_transaction_source_id NUMBER, p_completion_transaction_id NUMBER) IS
1372 SELECT mtlt.lot_number
1373 , mmtt.organization_id
1374 , mmtt.inventory_item_id
1375 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1376 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id
1377 AND mmtt.transaction_source_id = p_transaction_source_id
1378 AND mmtt.completion_transaction_id = p_completion_transaction_id
1379 AND mmtt.transaction_action_id = l_transaction_action_id
1380 AND mmtt.transaction_source_type_id = l_transaction_source_type_id;
1381
1382 CURSOR childlotmmt(p_transaction_source_id NUMBER, p_completion_transaction_id NUMBER) IS
1383 SELECT mtlt.lot_number
1384 , mmtt.organization_id
1385 , mmtt.inventory_item_id
1386 , mmtt.transaction_id
1387 FROM mtl_material_transactions mmtt, mtl_transaction_lot_numbers mtlt
1388 WHERE mmtt.transaction_id = mtlt.transaction_id
1389 AND mmtt.transaction_source_id = p_transaction_source_id
1390 AND mmtt.completion_transaction_id = p_completion_transaction_id
1391 AND mmtt.transaction_action_id = l_transaction_action_id
1392 AND mmtt.transaction_source_type_id = l_transaction_source_type_id;
1393
1394 l_object_number VARCHAR2(80); /* 5209767: Made the l_object_number size 80 from 31*/
1395 l_inventory_item_id NUMBER;
1396 l_organization_id NUMBER;
1397 l_object_type NUMBER := 1;
1398 l_parent_object_type NUMBER := 1;
1399 l_return_status VARCHAR2(10);
1400 l_count NUMBER;
1401 l_msg_count NUMBER;
1402 l_msg_data VARCHAR2(2000);
1403 l_origin_txn_id NUMBER;
1404 l_debug NUMBER :=1 ; -- := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1405 BEGIN
1406 -- {{- RT: Flow Genealogy. No direct change has been made to this }}
1407 IF (l_debug = 1) THEN
1408 mydebug('Inside Insert_Flow_Genealogy');
1409 mydebug('p_completion_transaction_id is '|| p_completion_transaction_id);
1410 mydebug('p_transaction_source_id is '|| p_transaction_source_id);
1411 mydebug('p_parent_object_number = '|| p_parent_object_number
1412 || ' p_parent_inventory_item_id = ' || p_parent_inventory_item_id );
1413 mydebug( 'p_parent_org_id = '|| p_parent_org_id || ' p_genealogy_origin = '
1414 || p_genealogy_origin || ' p_genealogy_type = ' || p_genealogy_type );
1415 mydebug( 'p_origin_txn_id = ' || p_origin_txn_id
1416 || ' p_update_txn_id = ' || p_update_txn_id
1417 || ' p_start_date_active = ' || TO_CHAR(p_start_date_active, 'DD-MON-RRRR')
1418 || ' p_end_date_active = ' || TO_CHAR(p_end_date_active, 'DD-MON-RRRR') );
1419 END IF;
1420
1421 -- Standard Start of API savepoint
1422 SAVEPOINT apiinsert_genealogy_apipub;
1423
1424 -- Standard call to check for call compatibility.
1425 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1426 RAISE lg_exc_unexpected_error;
1427 END IF;
1428
1429 -- Initialize message list if p_init_msg_list is set to TRUE.
1430 IF fnd_api.to_boolean(p_init_msg_list) THEN
1431 fnd_msg_pub.initialize;
1432 END IF;
1433
1434 x_msg_count := 0;
1435 x_return_status := lg_ret_sts_success;
1436
1437 IF p_transaction_source_id IS NULL THEN
1438 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
1439 fnd_msg_pub.ADD;
1440 RAISE lg_exc_error;
1441 END IF;
1442
1443 IF p_completion_transaction_id IS NULL THEN
1444 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
1445 fnd_msg_pub.ADD;
1446 RAISE lg_exc_error;
1447 END IF;
1448
1449 OPEN childlotmmtt(p_transaction_source_id, p_completion_transaction_id);
1450
1451 LOOP
1452 IF (l_debug = 1) THEN
1453 mydebug('inside Loop to retrieve child lot');
1454 END IF;
1455
1456 FETCH childlotmmtt INTO l_object_number, l_organization_id, l_inventory_item_id;
1457 EXIT WHEN childlotmmtt%NOTFOUND;
1458
1459 -- call insert_genealogy for each component lot.
1460 IF (l_debug = 1) THEN
1461 mydebug(l_object_number || ' ' || l_organization_id || ' ' || l_inventory_item_id);
1462 mydebug('call insert_genealogy');
1463 END IF;
1464
1465 inv_genealogy_pub.insert_genealogy(
1466 p_api_version => 1.0
1467 , p_init_msg_list => lg_fnd_g_false
1468 , p_commit => lg_fnd_g_false
1469 , p_validation_level => lg_fnd_valid_level_full
1470 , p_object_type => l_object_type
1471 , p_parent_object_type => l_parent_object_type
1475 , p_org_id => l_organization_id
1472 , p_object_id => NULL
1473 , p_object_number => l_object_number
1474 , p_inventory_item_id => l_inventory_item_id
1476 , p_parent_object_id => NULL
1477 , p_parent_object_number => p_parent_object_number
1478 , p_parent_inventory_item_id => p_parent_inventory_item_id
1479 , p_parent_org_id => p_parent_org_id
1480 , p_genealogy_origin => p_genealogy_origin
1481 , p_genealogy_type => p_genealogy_type
1482 , p_start_date_active => SYSDATE
1483 , p_end_date_active => NULL
1484 , p_origin_txn_id => p_origin_txn_id
1485 , p_update_txn_id => NULL
1486 , x_return_status => l_return_status
1487 , x_msg_count => l_msg_count
1488 , x_msg_data => l_msg_data);
1489
1490 IF l_return_status <> lg_ret_sts_success THEN
1491 RAISE lg_exc_error;
1492 END IF;
1493 END LOOP;
1494
1495 CLOSE childlotmmtt;
1496
1497 SELECT COUNT(*)
1498 INTO l_count
1499 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1500 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id
1501 AND mmtt.transaction_source_id = p_transaction_source_id
1502 AND mmtt.transaction_action_id = l_transaction_action_id
1503 AND mmtt.transaction_source_type_id = l_transaction_source_type_id
1504 AND mmtt.completion_transaction_id = p_completion_transaction_id;
1505
1506 IF (l_count = 0) THEN
1507 OPEN childlotmmt(p_transaction_source_id, p_completion_transaction_id);
1508
1509 LOOP
1510 IF (l_debug = 1) THEN
1511 mydebug('Inside retreive child lot from mmt');
1512 END IF;
1513
1514 FETCH childlotmmt INTO l_object_number, l_organization_id, l_inventory_item_id, l_origin_txn_id;
1515 EXIT WHEN childlotmmt%NOTFOUND;
1516
1517 -- call insert_genealogy for each component lot.
1518 IF (l_debug = 1) THEN
1519 mydebug(l_object_number || ' ' || l_organization_id || ' ' || l_inventory_item_id);
1520 mydebug('call insert_genealogy');
1521 END IF;
1522
1523 inv_genealogy_pub.insert_genealogy(
1524 p_api_version => 1.0
1525 , p_init_msg_list => lg_fnd_g_false
1526 , p_commit => lg_fnd_g_false
1527 , p_validation_level => lg_fnd_valid_level_full
1528 , p_object_type => l_object_type
1529 , p_parent_object_type => l_parent_object_type
1530 , p_object_id => NULL
1531 , p_object_number => l_object_number
1532 , p_inventory_item_id => l_inventory_item_id
1533 , p_org_id => l_organization_id
1534 , p_parent_object_id => NULL
1535 , p_parent_object_number => p_parent_object_number
1536 , p_parent_inventory_item_id => p_parent_inventory_item_id
1537 , p_parent_org_id => p_parent_org_id
1538 , p_genealogy_origin => p_genealogy_origin
1539 , p_genealogy_type => p_genealogy_type
1540 , p_start_date_active => SYSDATE
1541 , p_end_date_active => NULL
1542 , p_origin_txn_id => l_origin_txn_id
1543 , p_update_txn_id => NULL
1544 , x_return_status => l_return_status
1545 , x_msg_count => l_msg_count
1546 , x_msg_data => l_msg_data);
1547
1548 IF l_return_status <> lg_ret_sts_success THEN
1549 RAISE lg_exc_error;
1550 END IF;
1551 END LOOP;
1552
1553 CLOSE childlotmmt;
1554 END IF;
1555
1556 -- Standard check of p_commit.
1557 IF fnd_api.to_boolean(p_commit) THEN
1558 COMMIT WORK;
1559 END IF;
1560
1561 -- Standard call to get message count and if count is 1, get message info.
1562 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1563 EXCEPTION
1564 WHEN lg_exc_error THEN
1565 IF (l_debug = 1) THEN
1566 mydebug('exception G_EXC_ERROR'|| x_msg_data);
1567 END IF;
1568
1569 ROLLBACK TO save_insert_genealogy;
1570 x_return_status := lg_ret_sts_error;
1571 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
1572 WHEN lg_exc_unexpected_error THEN
1573 IF (l_debug = 1) THEN
1574 mydebug('exception G_UNEXC_ERROR'|| x_msg_data);
1575 END IF;
1576
1577 ROLLBACK TO save_insert_genealogy;
1578 x_return_status := lg_ret_sts_unexp_error;
1579 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
1580 WHEN OTHERS THEN
1581 IF (l_debug = 1) THEN
1582 mydebug('exception WHEN OTHERS'|| x_msg_data);
1583 END IF;
1584
1585 ROLLBACK TO save_insert_genealogy;
1586 x_return_status := lg_ret_sts_unexp_error;
1587
1588 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1589 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1590 END IF;
1591
1592 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
1593 END insert_flow_genealogy;
1594
1595
1599 P_INIT_MSG_LIST IN VARCHAR2 := gen_fnd_g_false,
1596
1597 PROCEDURE DELETE_EAM_ROW(
1598 P_API_VERSION IN NUMBER,
1600 P_COMMIT IN VARCHAR2 := gen_fnd_g_false,
1601 P_VALIDATION_LEVEL IN NUMBER := gen_fnd_valid_level_full,
1602 P_OBJECT_ID IN NUMBER,
1603 P_START_DATE_ACTIVE IN DATE,
1604 P_END_DATE_ACTIVE IN DATE,
1605 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1606 X_MSG_COUNT OUT NOCOPY NUMBER,
1607 X_MSG_DATA OUT NOCOPY VARCHAR2
1608 ) IS
1609 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
1610 l_api_version CONSTANT NUMBER := 1.0;
1611 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1612 BEGIN
1613 SAVEPOINT inv_eam_genealogy;
1614
1615 -- Standard call to check for call compatibility.
1616 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1617 RAISE lg_exc_unexpected_error;
1618 END IF;
1619
1620 -- Initialize message list if p_init_msg_list is set to TRUE.
1621 IF fnd_api.to_boolean(p_init_msg_list) THEN
1622 fnd_msg_pub.initialize;
1623 END IF;
1624
1625 -- Initialize API return status to success
1626 x_return_status := lg_ret_sts_success;
1627
1628 -- API body
1629
1630 Delete from mtl_object_genealogy
1631 where object_id = p_object_id
1632 and start_date_active = p_start_date_active
1633 and end_date_active = p_end_date_active
1634 and genealogy_type = 5;
1635
1636 -- End of API body.
1637 -- Standard check of p_commit.
1638 IF fnd_api.to_boolean(p_commit) THEN
1639 COMMIT WORK;
1640 END IF;
1641
1642 -- Standard call to get message count and if count is 1, get message info.
1643 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1644 EXCEPTION
1645 WHEN lg_exc_error THEN
1646 ROLLBACK TO inv_eam_genealogy;
1647 x_return_status := lg_ret_sts_error;
1648 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1649 WHEN lg_exc_unexpected_error THEN
1650 ROLLBACK TO inv_eam_genealogy;
1651 x_return_status := lg_ret_sts_unexp_error;
1652 fnd_msg_pub.count_and_get(
1653 p_count => x_msg_count
1654 ,p_data => x_msg_data);
1655 WHEN OTHERS THEN
1656 ROLLBACK TO inv_eam_genealogy;
1657 x_return_status := lg_ret_sts_unexp_error;
1658
1659 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1660 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1661 END IF;
1662
1663 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1664
1665 END Delete_EAM_Row;
1666
1667 PROCEDURE parameter_validations(
1668 p_validation_level IN NUMBER := gen_fnd_valid_level_full
1669 , p_object_type IN NUMBER
1670 , p_parent_object_type IN NUMBER
1671 , p_object_id IN OUT NOCOPY NUMBER
1672 , p_object_number IN VARCHAR2
1673 , p_inventory_item_id IN NUMBER
1674 , p_org_id IN NUMBER
1675 , p_parent_object_id IN OUT NOCOPY NUMBER
1676 , p_parent_object_number IN VARCHAR2
1677 , p_parent_inventory_item_id IN NUMBER
1678 , p_parent_org_id IN NUMBER
1679 , p_genealogy_origin IN NUMBER
1680 , p_genealogy_type IN NUMBER
1681 , p_start_date_active IN DATE
1682 , p_end_date_active IN DATE
1683 , p_origin_txn_id IN NUMBER
1684 , p_update_txn_id IN NUMBER
1685 , p_object_type2 IN OUT NOCOPY NUMBER
1686 , p_object_id2 IN OUT NOCOPY NUMBER
1687 , p_object_number2 IN VARCHAR2
1688 , p_parent_object_type2 IN OUT NOCOPY NUMBER
1689 , p_parent_object_id2 IN OUT NOCOPY NUMBER
1690 , p_parent_object_number2 IN VARCHAR2
1691 , p_child_lot_control_code IN NUMBER
1692 , p_parent_lot_control_code IN NUMBER
1693 , p_action IN VARCHAR2
1694 , p_debug IN NUMBER
1695 , x_return_status OUT NOCOPY VARCHAR2
1696 , x_msg_count OUT NOCOPY NUMBER
1697 , x_msg_data OUT NOCOPY VARCHAR2)
1698 IS
1699 l_api_name CONSTANT VARCHAR2(30) := 'parameter_validations';
1700 l_dummy NUMBER;
1701 l_org_id NUMBER;
1702 retval NUMBER;
1703 l_parent_org_id NUMBER;
1704 l_object_id NUMBER;
1705 l_parent_object_id NUMBER;
1706 l_object_id2 NUMBER;
1707 l_parent_object_id2 NUMBER;
1708 l_object_type2 NUMBER;
1709 l_parent_object_type2 NUMBER;
1710 l_parent_object_type NUMBER;
1711 l_parent_inventory_item_id NUMBER;
1712 l_inventory_item_id NUMBER;
1713 l_child_item_type NUMBER;
1714 l_parent_item_type NUMBER;
1715 l_object_table object_id_tbl_t;
1716 l_serial_number VARCHAR2(30);
1717 l_invalid_field_msg VARCHAR2(50);
1718 l_invalid_comb_msg VARCHAR2(150);
1719 l_child_lot_control_code NUMBER;
1720 l_parent_lot_control_code NUMBER;
1724 -- Standard Start of API savepoint
1721 l_action VARCHAR2(10);
1722 BEGIN
1723 x_return_status := lg_ret_sts_success;
1725 SAVEPOINT save_parameter_validations;
1726
1727 g_mod_name := 'parameter_validations';
1728
1729 IF (p_debug = 1) THEN
1730 mydebug('Entered parameter_validations ...');
1731 mydebug('p_validation_level: ' || p_validation_level );
1732 mydebug('p_object_type: ' || p_object_type );
1733 mydebug('p_parent_object_type: ' || p_parent_object_type );
1734 mydebug('p_object_id: ' || p_object_id );
1735 mydebug('p_object_number: ' || p_object_number );
1736 mydebug('p_inventory_item_id: ' || p_inventory_item_id );
1737 mydebug('p_org_id: ' || p_org_id );
1738 mydebug('p_parent_object_id: ' || p_parent_object_id );
1739 mydebug('p_parent_object_number: ' || p_parent_object_number );
1740 mydebug('p_parent_inventory_item_id: ' || p_parent_inventory_item_id );
1741 mydebug('p_parent_org_id: ' || p_parent_org_id );
1742 mydebug('p_genealogy_origin: ' || p_genealogy_origin );
1743 mydebug('p_genealogy_type: ' || p_genealogy_type );
1744 mydebug('p_start_date_active: ' || p_start_date_active );
1745 mydebug('p_end_date_active: ' || p_end_date_active );
1746 mydebug('p_origin_txn_id: ' || p_origin_txn_id );
1747 mydebug('p_update_txn_id: ' || p_update_txn_id );
1748 mydebug('p_object_type2: ' || p_object_type2 );
1749 mydebug('p_object_id2: ' || p_object_id2 );
1750 mydebug('p_object_number2: ' || p_object_number2 );
1751 mydebug('p_parent_object_type2: ' || p_parent_object_type2 );
1752 mydebug('p_parent_object_id2: ' || p_parent_object_id2 );
1753 mydebug('p_parent_object_number2: ' || p_parent_object_number2 );
1754 mydebug('p_child_lot_control_code: ' || p_child_lot_control_code );
1755 mydebug('p_parent_lot_control_code: ' || p_parent_lot_control_code );
1756 mydebug('p_action: ' || p_action );
1757 mydebug('p_debug: ' || p_debug );
1758 END IF;
1759
1760 l_object_type2 := p_object_type2;
1761 l_parent_object_type2 := p_parent_object_type2;
1762 l_child_lot_control_code := p_child_lot_control_code ;
1763 l_parent_lot_control_code := p_parent_lot_control_code ;
1764
1765 IF (p_debug = 1) THEN
1766 mydebug('l_object_type2 : ' || l_object_type2);
1767 mydebug('l_parent_object_type2 : ' || l_parent_object_type2);
1768 mydebug('l_child_lot_control_code : ' || l_child_lot_control_code);
1769 mydebug('l_parent_lot_control_code : ' || l_parent_lot_control_code);
1770 END IF;
1771
1772 -- R12 Genealogy Project: Added the check for p_validation_level and only if
1773 -- it is full that we should do certain validations otherwise not
1774
1775 IF p_validation_level = lg_fnd_valid_level_full THEN
1776 IF (p_debug = 1) THEN
1777 mydebug('{{- Only if p_validation_level is FULL that the input parameters will be validated }}');
1778 END IF;
1779
1780 IF p_object_type IS NULL THEN
1781 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: P_object_type'); END IF;
1782
1783 x_return_status := lg_ret_sts_error; -- R12
1784 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1785 fnd_message.set_token('ATTRIBUTE', 'p_object_type');
1786 fnd_msg_pub.ADD;
1787 END IF;
1788
1789 IF p_object_id IS NULL THEN
1790 IF (p_debug = 1) THEN mydebug('p_object_id is null'); END IF;
1791
1792 IF p_object_number IS NULL OR p_inventory_item_id IS NULL OR p_org_id IS NULL THEN
1793 IF p_object_number IS NULL THEN
1794 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_object_number'); END IF;
1795
1796 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1797 fnd_message.set_token('ATTRIBUTE', 'p_object_number');
1798 fnd_msg_pub.ADD;
1799 END IF;
1800
1801 --R12 IF p_inventory_item_id IS NULL THEN
1802 IF p_object_type <> 5 AND p_inventory_item_id IS NULL THEN
1803 /* {{ - If object_id is not passed then inventory_item_id is necessary along with
1804 object_number and org_id only if this object is not of type JOB = 5 }} */
1805 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_inventory_item_id'); END IF;
1806
1807 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1808 fnd_message.set_token('ATTRIBUTE', 'p_inventory_item_id');
1809 fnd_msg_pub.ADD;
1810 END IF;
1811
1812 IF p_org_id IS NULL THEN
1813 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_org_id'); END IF;
1814
1815 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1816 fnd_message.set_token('ATTRIBUTE', 'p_org_id');
1817 fnd_msg_pub.ADD;
1818 END IF;
1819
1820 x_return_status := lg_ret_sts_error; -- R12
1821 END IF;
1822 END IF;
1823
1824 IF (p_debug = 1) THEN mydebug('After validating Child details 1 ' || x_return_status); END IF;
1825
1826 IF p_parent_object_id IS NULL THEN
1827 IF (p_debug = 1) THEN mydebug('p_parent_object_id is null'); END IF;
1828
1829 IF p_parent_object_number IS NULL
1830 OR p_parent_inventory_item_id IS NULL
1834 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_parent_object_number'); END IF;
1831 OR p_parent_org_id IS NULL THEN
1832
1833 IF p_parent_object_number IS NULL THEN
1835
1836 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1837 fnd_message.set_token('ATTRIBUTE', 'p_parent_object_number');
1838 fnd_msg_pub.ADD;
1839 END IF;
1840
1841 IF p_parent_object_type <> 5 AND p_parent_inventory_item_id IS NULL THEN
1842 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_parent_inventory_item_id'); END IF;
1843
1844 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1845 fnd_message.set_token('ATTRIBUTE', 'p_parent_inventory_item_id');
1846 fnd_msg_pub.ADD;
1847 END IF;
1848
1849 IF p_parent_org_id IS NULL THEN
1850 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_parent_org_id'); END IF;
1851
1852 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1853 fnd_message.set_token('ATTRIBUTE', 'p_parent_org_id');
1854 fnd_msg_pub.ADD;
1855 END IF;
1856
1857 x_return_status := lg_ret_sts_error; -- R12
1858 END IF;
1859 END IF;
1860
1861 IF (p_debug = 1) THEN mydebug('After validating Parent Object details 1: ' || x_return_status); END IF;
1862
1863 -- Object type can be between 1 and 5 .
1864 -- If invalid return an error.
1865 IF p_object_type < 1 OR p_object_type > 5 THEN
1866 x_return_status := lg_ret_sts_error; -- R12
1867 IF (p_debug = 1) THEN mydebug('INV_FIELD_INVALID - p_object_type'); END IF;
1868 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1869 fnd_message.set_token('ENTITY1', 'p_object_type');
1870 fnd_msg_pub.ADD;
1871 END IF;
1872
1873 IF (p_parent_object_type < 1 OR p_parent_object_type > 5) THEN
1874 x_return_status := lg_ret_sts_error; -- R12
1875 IF (p_debug = 1) THEN mydebug('INV_FIELD_INVALID - p_parent_object_type'); END IF;
1876 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1877 fnd_message.set_token('ENTITY1', 'p_parent_object_type');
1878 fnd_msg_pub.ADD;
1879 END IF;
1880
1881 -- R12 Genealogy Enhancements : Validate second set of object and parent object details
1882
1883 IF (p_debug = 1) THEN
1884 mydebug('{{- Validate the second set of object_details in columns with suffix 2 }}'
1885 || '{{ It applies to Lot+Serial Controlled items }}');
1886 mydebug('x_return_status: ' || x_return_status);
1887 END IF;
1888
1889 IF (p_object_type = 2 AND (p_object_id2 IS NOT NULL OR p_object_number2 IS NOT NULL) )
1890 AND l_child_lot_control_code IS NULL AND p_genealogy_type <> 5 THEN
1891 -- EAM genealogy is never for Lot+serial controlled items, therefore
1892 -- it is not necessary to derive lot_control_code of the item and therefore
1893 -- inventory_item_id is not necessary
1894 IF (p_debug = 1) THEN
1895 mydebug('{{- If p_object_type is 2 (serial) then check the lot control code }}' ||
1896 '{{ of the child item, only if it is 2 that the second set of child object }}'||
1897 '{{ details are necessary }} ');
1898 END IF;
1899
1900 l_inventory_item_id := p_inventory_item_id ;
1901 IF l_inventory_item_id is NULL THEN
1902 BEGIN
1903 SELECT INVENTORY_ITEM_ID
1904 INTO l_inventory_item_id
1905 FROM mtl_serial_numbers
1906 WHERE gen_object_id = p_object_id
1907 AND current_Organization_id = p_org_id;
1908 EXCEPTION WHEN NO_DATA_FOUND THEN
1909
1910 IF (p_debug = 1) THEN mydebug('no data found ...child mtl_serial_numbers '); END IF;
1911 x_return_status := lg_ret_sts_error; -- R12
1912 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1913 fnd_message.set_token('ATTRIBUTE', 'l_inventory_item_id');
1914 fnd_msg_pub.ADD;
1915 END ;
1916
1917 IF (p_debug = 1) THEN mydebug('l_inventory_item_id :' || l_inventory_item_id); END IF;
1918 END IF;
1919 IF l_inventory_item_id is NOT NULL THEN
1920 IF (p_debug = 1) THEN mydebug('l_inventory_item_id is not null ' ); END IF;
1921
1922 SELECT lot_control_code
1923 INTO l_child_lot_control_code
1924 FROM mtl_system_items_b
1925 WHERE inventory_item_id = l_inventory_item_id
1926 AND Organization_id = p_org_id;
1927
1928 END IF;
1929 END IF;
1930
1931 IF (p_debug = 1) THEN mydebug('l_child_lot_control_code: x_return_status: ' || l_child_lot_control_code
1932 || ':' || x_return_status); END IF;
1933
1934 IF (p_parent_object_type = 2 AND (p_parent_object_id2 IS NOT NULL OR p_parent_object_number2 IS NOT NULL))
1935 and l_parent_lot_control_code is NULL AND p_genealogy_type <> 5 THEN
1936 IF (p_debug = 1) THEN
1937 mydebug('{{- If p_parent_object_type is 2 (serial) then check the lot control code }}' ||
1938 '{{ for parent item, only if it is 2 that the second set of parent object details}}' ||
1939 '{{ are necessary }} ');
1943 IF l_parent_inventory_item_id is NULL OR l_parent_inventory_item_id = 0 THEN
1940 END IF;
1941
1942 l_parent_inventory_item_id := p_parent_inventory_item_id ;
1944 BEGIN
1945 SELECT INVENTORY_ITEM_ID
1946 INTO l_parent_inventory_item_id
1947 FROM mtl_serial_numbers
1948 WHERE gen_object_id = p_parent_object_id
1949 AND current_Organization_id = p_parent_org_id;
1950 EXCEPTION WHEN NO_DATA_FOUND THEN
1951 x_return_status := lg_ret_sts_error; -- R12
1952 IF (p_debug = 1) THEN mydebug('no data found ...parent mtl_serial_numbers '); END IF;
1953 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
1954 fnd_message.set_token('ATTRIBUTE', 'l_parent_inventory_item_id');
1955 fnd_msg_pub.ADD;
1956 END ;
1957 IF (p_debug = 1) THEN mydebug('l_parent_inventory_item_id '|| l_parent_inventory_item_id ); END IF;
1958 END IF;
1959 IF l_parent_inventory_item_id is NOT NULL THEN
1960 SELECT lot_control_code
1961 INTO l_parent_lot_control_code
1962 FROM mtl_system_items_b
1963 WHERE inventory_item_id = l_parent_inventory_item_id
1964 AND Organization_id = p_parent_org_id;
1965
1966 END IF;
1967 END IF;
1968 IF (p_debug = 1) THEN mydebug('l_parent_lot_control_code: x_return_status: ' || l_parent_lot_control_code
1969 || ':' || x_return_status); END IF;
1970
1971 IF l_child_lot_control_code = 2 THEN
1972 IF (p_debug = 1) THEN
1973 mydebug('{{Only if child is lot controlled that we need to validate second set of object details }}' );
1974 END IF;
1975
1976 IF (l_object_type2 IS NOT NULL AND l_object_type2 <> 1) THEN
1977 IF (p_debug = 1) THEN mydebug('{{- p_object_type2 can only be = 1 (lot) }} '); END IF;
1978 x_return_status := lg_ret_sts_error; -- R12
1979 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
1980 fnd_message.set_token('ENTITY1', 'l_object_type2');
1981 fnd_msg_pub.ADD;
1982 ELSE
1983 IF (l_object_type2 IS NULL )
1984 THEN
1985 IF (p_debug = 1) THEN mydebug('If P_object_type2 is null assign 1 (lot)'); END IF;
1986 l_object_type2 := 1; --Lot
1987 END IF;
1988 END IF;
1989
1990
1991 /*Mrana : 11/02/05: Do not bother validating or derivig id2 ..
1992 * : for backward compatibility where lot+serial assemblies
1993 * : might input only serial number and not both lotand serial
1994 * IF p_object_id2 IS NULL THEN
1995 IF (p_debug = 1) THEN mydebug('p_object_id2 is NULL'); END IF;
1996 IF p_object_number2 IS NULL OR p_inventory_item_id IS NULL OR p_org_id IS NULL THEN
1997 IF (p_debug = 1) THEN mydebug('p_object_number2 or p_inventory_item_id is NULL' ||
1998 ' or p_org_id is NULL'); END IF;
1999 IF p_object_number2 IS NULL THEN
2000 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_object_number2'); END IF;
2001
2002 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
2003 fnd_message.set_token('ATTRIBUTE', 'p_object_number2');
2004 fnd_msg_pub.ADD;
2005 END IF;
2006 IF p_object_type <> 5 AND p_inventory_item_id IS NULL THEN
2007 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_inventory_item_id'); END IF;
2008
2009 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
2010 fnd_message.set_token('ATTRIBUTE', 'p_inventory_item_id');
2011 fnd_msg_pub.ADD;
2012 END IF;
2013
2014
2015 IF p_org_id IS NULL THEN
2016 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_org_id'); END IF;
2017
2018 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
2019 fnd_message.set_token('ATTRIBUTE', 'p_org_id');
2020 fnd_msg_pub.ADD;
2021 END IF;
2022
2023 x_return_status := lg_ret_sts_error; -- R12
2024 --RAISE lg_exc_error;
2025 END IF;
2026 END IF; */
2027 END IF;
2028
2029 IF (p_debug = 1) THEN
2030 mydebug('After validating Child Object 2 details');
2031 mydebug('x_return_status: ' || x_return_status);
2032 END IF;
2033
2034 IF l_parent_lot_control_code = 2 THEN
2035 IF (l_parent_object_type2 IS NOT NULL AND l_parent_object_type2 <> 1) THEN
2036 IF (p_debug = 1) THEN mydebug('{{- p_parent_object_type2 can only be = 1 (lot) }} '); END IF;
2037 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2038 fnd_message.set_token('ENTITY1', 'l_parent_object_type2');
2039 fnd_msg_pub.ADD;
2040 RAISE lg_exc_error;
2041 ELSE
2042 IF (l_parent_object_type2 IS NULL )
2043 THEN
2044 IF (p_debug = 1) THEN mydebug('If P_object_type2 is null assign 1 (lot)'); END IF;
2045 l_parent_object_type2 := 1; --Lot
2046 END IF;
2047 END IF;
2048
2052 IF p_parent_object_id2 IS NULL THEN
2049 /*Mrana : 11/02/05: Do not bother validating or derivig id2 ..
2050 * : for backward compatibility where lot+serial assemblies
2051 * : might input only serial number and not both lotand serial
2053 IF (p_debug = 1) THEN mydebug('p_parent_object_id2 is NULL'); END IF;
2054 IF p_parent_object_number2 IS NULL
2055 OR p_parent_inventory_item_id IS NULL
2056 OR p_parent_org_id IS NULL THEN
2057 IF (p_debug = 1) THEN mydebug('p_parent_object_number2 or -p_parent_object_type2 ' ||
2058 '<> 5 and p_inventory_item_id is NULL- or p_org_id is NUll'); END IF;
2059 IF p_parent_object_number2 IS NULL THEN
2060 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_parent_object_number2'); END IF;
2061
2062 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
2063 fnd_message.set_token('ATTRIBUTE', 'p_parent_object_number2');
2064 fnd_msg_pub.ADD;
2065 END IF;
2066
2067 IF l_parent_object_type2 <> 5 AND p_parent_inventory_item_id IS NULL THEN
2068 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_parent_inventory_item_id'); END IF;
2069
2070 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
2071 fnd_message.set_token('ATTRIBUTE', 'p_parent_inventory_item_id');
2072 fnd_msg_pub.ADD;
2073 END IF;
2074
2075 IF p_parent_org_id IS NULL THEN
2076 IF (p_debug = 1) THEN mydebug('INV_ATTRIBUTE_REQUIRED: p_parent_org_id'); END IF;
2077
2078 fnd_message.set_name('INV', 'INV_ATTRIBUTE_REQUIRED');
2079 fnd_message.set_token('ATTRIBUTE', 'p_parent_org_id');
2080 fnd_msg_pub.ADD;
2081 END IF;
2082
2083 x_return_status := lg_ret_sts_error; -- R12
2084 --RAISE lg_exc_error;
2085 END IF;
2086 END IF; */
2087 END IF;
2088
2089 IF (p_debug = 1) THEN mydebug('After validating Parent Object 2 details');
2090 mydebug('x_return_status: ' || x_return_status);
2091 END IF;
2092 END IF; -- Validation level is full
2093
2094 IF x_return_status = lg_ret_sts_error
2095 THEN
2096 IF (p_debug = 1) THEN mydebug('{{ If any of the parameter validations failed, then raise ' ||
2097 'an exception now..before validating the object ids }}');
2098 END IF;
2099 RAISE lg_exc_error;
2100 END IF;
2101
2102 IF x_return_status = lg_ret_sts_unexp_error
2103 THEN
2104 IF (p_debug = 1) THEN mydebug('{{ If any of the parameter validations failed, then raise ' ||
2105 'an exception now..before validating the object ids }}');
2106 END IF;
2107 RAISE lg_exc_unexpected_error;
2108 END IF;
2109
2110 -- Validate the existence of object number in MTL_SERIAL_NUMBERS
2111 -- or MTL_LOT_NUMBERS depending on the object type.
2112 -- If object number is not found return an error.
2113
2114 IF (p_debug = 1) THEN mydebug('Before deriving Object ID');
2115 mydebug('LTRIM(RTRIM(p_object_number)) : ' ||
2116 LTRIM(RTRIM(p_object_number)));
2117 mydebug('LTRIM(RTRIM(p_parent_object_number)) : ' ||
2118 LTRIM(RTRIM(p_parent_object_number))); END IF;
2119
2120
2121 l_invalid_field_msg := 'Object ID';
2122 l_invalid_comb_msg := 'Org, Item and Object Number Combination';
2123
2124 l_dummy := 0;
2125 IF p_object_type = 1 THEN
2126 IF p_object_id IS NOT NULL THEN
2127 IF p_validation_level = lg_fnd_valid_level_full THEN
2128 IF (p_debug = 1) THEN
2129 mydebug('{{- Only if p_validation_level is FULL that parameters- object id will be validated }}');
2130 END IF;
2131 SELECT COUNT(*)
2132 INTO l_dummy
2133 FROM mtl_lot_numbers
2134 WHERE gen_object_id = p_object_id;
2135
2136 IF l_dummy = 0 THEN
2137 x_return_status := lg_ret_sts_error; -- R12
2138 IF (p_debug = 1) THEN mydebug('lot not found using - p_object_id'); END IF;
2139 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2140 fnd_message.set_token('ENTITY1', l_invalid_field_msg);
2141 fnd_msg_pub.ADD;
2142 --RAISE lg_exc_error;
2143 END IF;
2144 END IF;
2145
2146 l_object_id := p_object_id;
2147 IF (p_debug = 1) THEN mydebug('1: l_object_id : ' || l_object_id);
2148 mydebug('1: p_object_id : ' || p_object_id); END IF;
2149
2150 ELSE
2151 BEGIN
2152 SELECT gen_object_id
2153 INTO l_object_id
2154 FROM mtl_lot_numbers
2155 WHERE lot_number = LTRIM(RTRIM(p_object_number))
2156 AND inventory_item_id = p_inventory_item_id
2157 AND organization_id = p_org_id;
2158 IF (p_debug = 1) THEN mydebug('1: l_object_id: ' || l_object_id); END IF;
2159
2160 EXCEPTION
2161 WHEN NO_DATA_FOUND THEN
2162 x_return_status := lg_ret_sts_error; -- R12
2163 IF (p_debug = 1) THEN mydebug('lot not found using - p_object_number,'); END IF;
2164 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2168 END;
2165 fnd_message.set_token('ENTITY1', l_invalid_comb_msg);
2166 fnd_msg_pub.ADD;
2167 --RAISE lg_exc_error;
2169 END IF;
2170 ELSIF p_object_type = 2 THEN
2171 IF p_object_id IS NOT NULL THEN
2172 IF p_validation_level = lg_fnd_valid_level_full THEN
2173 SELECT COUNT(*)
2174 INTO l_dummy
2175 FROM mtl_serial_numbers
2176 WHERE gen_object_id = p_object_id;
2177
2178 IF l_dummy = 0 THEN
2179 x_return_status := lg_ret_sts_error; -- R12
2180 IF (p_debug = 1) THEN mydebug('Serial not found using - p_object_id,'); END IF;
2181 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2182 fnd_message.set_token('ENTITY1', l_invalid_field_msg);
2183 fnd_msg_pub.ADD;
2184 -- RAISE lg_exc_error;
2185 END IF;
2186 END IF;
2187
2188 l_object_id := p_object_id;
2189 IF (p_debug = 1) THEN mydebug('2: l_object_id : ' || l_object_id);
2190 mydebug('2: p_object_id : ' || p_object_id); END IF;
2191
2192 ELSE
2193 BEGIN
2194 SELECT gen_object_id
2195 INTO l_object_id
2196 FROM mtl_serial_numbers
2197 WHERE serial_number = LTRIM(RTRIM(p_object_number))
2198 AND inventory_item_id = p_inventory_item_id
2199 AND current_organization_id = p_org_id;
2200 IF (p_debug = 1) THEN mydebug('2: l_object_id: ' || l_object_id); END IF;
2201
2202 EXCEPTION
2203 WHEN NO_DATA_FOUND THEN
2204 x_return_status := lg_ret_sts_error; -- R12
2205 IF (p_debug = 1) THEN mydebug('Serial not found using - p_object_number,'); END IF;
2206 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2207 fnd_message.set_token('ENTITY1', l_invalid_comb_msg);
2208 fnd_msg_pub.ADD;
2209 --RAISE lg_exc_error;
2210 END;
2211 END IF;
2212 ELSIF p_object_type = 5 THEN
2213 IF p_object_id IS NOT NULL THEN
2214 SELECT wip_entity_id
2215 INTO l_dummy
2216 FROM wip_entities
2217 WHERE gen_object_id = p_object_id;
2218
2219 IF l_dummy = 0 THEN
2220 x_return_status := lg_ret_sts_error; -- R12
2221 IF (p_debug = 1) THEN mydebug('Job not found using - p_object_id,'); END IF;
2222 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2223 fnd_message.set_token('ENTITY1', l_invalid_field_msg);
2224 fnd_msg_pub.ADD;
2225 --RAISE lg_exc_error;
2226 END IF;
2227
2228 l_object_id := p_object_id;
2229 IF (p_debug = 1) THEN mydebug('5: l_object_id : ' || l_object_id);
2230 mydebug('5: p_object_id : ' || p_object_id); END IF;
2231
2232 ELSE
2233 BEGIN
2234 SELECT gen_object_id
2235 INTO l_object_id
2236 FROM wip_entities
2237 WHERE wip_entity_name = LTRIM(RTRIM(p_object_number))
2238 AND organization_id = p_org_id;
2239 IF (p_debug = 1) THEN mydebug('5: l_object_id: ' || l_object_id); END IF;
2240
2241 EXCEPTION
2242 WHEN NO_DATA_FOUND THEN
2243 x_return_status := lg_ret_sts_error; -- R12
2244 IF (p_debug = 1) THEN mydebug('Job not found using - p_object_number,'); END IF;
2245 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2246 fnd_message.set_token('ENTITY1', l_invalid_comb_msg);
2247 fnd_msg_pub.ADD;
2248 --RAISE lg_exc_error;
2249 END;
2250 END IF;
2251 END IF;
2252
2253 IF (p_debug = 1) THEN mydebug(' After deriving Object Id');
2254 mydebug('x_return_status: ' || x_return_status); END IF;
2255
2256 -- Validate the existence of parent object number in MTL_SERIAL_NUMBERS
2257 -- or MTL_LOT_NUMBERS depending on the object type.
2258 -- If parent object number is not found return an error.
2259 l_invalid_field_msg := 'Parent Object ID';
2260 l_invalid_comb_msg := 'Parent Org, Item and Object Number Combination';
2261 IF p_parent_object_type = 1 THEN
2262 IF p_parent_object_id IS NOT NULL THEN
2263 IF p_validation_level = lg_fnd_valid_level_full THEN
2264 IF (p_debug = 1) THEN
2265 mydebug('{{- Only if p_validation_level is FULL that parameters- parent object_id will be validated }}');
2266 END IF;
2267 SELECT COUNT(*)
2268 INTO l_dummy
2269 FROM mtl_lot_numbers
2270 WHERE gen_object_id = p_parent_object_id;
2271
2272 IF l_dummy = 0 THEN
2273 x_return_status := lg_ret_sts_error; -- R12
2274 IF (p_debug = 1) THEN mydebug('Lot not found using - p_parent_object_id,'); END IF;
2275 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2276 fnd_message.set_token('ENTITY1', l_invalid_field_msg);
2277 fnd_msg_pub.ADD;
2278 --RAISE lg_exc_error;
2279 END IF;
2280 END IF;
2281
2282 l_parent_object_id := p_parent_object_id;
2283 IF (p_debug = 1) THEN mydebug('1: l_parent_object_id : ' || l_parent_object_id);
2284 mydebug('1: p_parent_object_id : ' || p_parent_object_id); END IF;
2285
2286 ELSE
2287 BEGIN
2288 SELECT gen_object_id
2289 INTO l_parent_object_id
2290 FROM mtl_lot_numbers
2294 IF (p_debug = 1) THEN mydebug('1: l_parent_object_id: ' || l_parent_object_id); END IF;
2291 WHERE lot_number = LTRIM(RTRIM(p_parent_object_number))
2292 AND inventory_item_id = p_parent_inventory_item_id
2293 AND organization_id = p_parent_org_id;
2295
2296 EXCEPTION
2297 WHEN NO_DATA_FOUND THEN
2298 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2299 x_return_status := lg_ret_sts_error; -- R12
2300 IF (p_debug = 1) THEN mydebug('Lot not found using - p_parent_object_number,'); END IF;
2301 fnd_message.set_token('ENTITY1', l_invalid_comb_msg);
2302 fnd_msg_pub.ADD;
2303 --RAISE lg_exc_error;
2304 END;
2305 END IF;
2306 ELSIF p_parent_object_type = 2 THEN
2307 IF p_parent_object_id IS NOT NULL THEN
2308 IF p_validation_level = lg_fnd_valid_level_full THEN
2309 IF (p_debug = 1) THEN
2310 mydebug('{{- Only if p_validation_level is FULL that parameters- parent object details will be validated }}');
2311 END IF;
2312 SELECT COUNT(*)
2313 INTO l_dummy
2314 FROM mtl_serial_numbers
2315 WHERE gen_object_id = p_parent_object_id;
2316
2317 IF l_dummy = 0 THEN
2318 x_return_status := lg_ret_sts_error; -- R12
2319 IF (p_debug = 1) THEN mydebug('Serial not found using - p_parent_object_id,'); END IF;
2320 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2321 fnd_message.set_token('ENTITY1', l_invalid_field_msg);
2322 fnd_msg_pub.ADD;
2323 --RAISE lg_exc_error;
2324 END IF;
2325 END IF;
2326
2327 l_parent_object_id := p_parent_object_id;
2328 IF (p_debug = 1) THEN mydebug('2: l_parent_object_id : ' || l_parent_object_id);
2329 mydebug('2: p_parent_object_id : ' || p_parent_object_id); END IF;
2330
2331 ELSE
2332 BEGIN
2333 SELECT gen_object_id
2334 INTO l_parent_object_id
2335 FROM mtl_serial_numbers
2336 WHERE serial_number = LTRIM(RTRIM(p_parent_object_number))
2337 AND inventory_item_id = p_parent_inventory_item_id
2338 AND current_organization_id = p_parent_org_id;
2339 IF (p_debug = 1) THEN mydebug('2: l_parent_object_id: ' || l_parent_object_id); END IF;
2340
2341 EXCEPTION
2342 WHEN NO_DATA_FOUND THEN
2343 x_return_status := lg_ret_sts_error; -- R12
2344 IF (p_debug = 1) THEN mydebug('Serial not found using - p_parent_object_number,'); END IF;
2345 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2346 fnd_message.set_token('ENTITY1', l_invalid_comb_msg);
2347 fnd_msg_pub.ADD;
2348 --RAISE lg_exc_error;
2349 END;
2350 END IF;
2351 ELSIF p_parent_object_type = 5 THEN
2352 -- Not putting p_validation around this since it belongs to EAM and will leave them to decide
2353 IF p_parent_object_id IS NOT NULL THEN
2354 SELECT wip_entity_id
2355 INTO l_dummy
2356 FROM wip_entities
2357 WHERE gen_object_id = p_parent_object_id;
2358
2359 IF l_dummy = 0 THEN
2360 x_return_status := lg_ret_sts_error; -- R12
2361 IF (p_debug = 1) THEN mydebug('Job not found using - p_parent_object_id,'); END IF;
2362 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2363 fnd_message.set_token('ENTITY1', l_invalid_field_msg);
2364 fnd_msg_pub.ADD;
2365 -- RAISE lg_exc_error;
2366 END IF;
2367
2368 l_parent_object_id := p_parent_object_id;
2369 IF (p_debug = 1) THEN mydebug('5: l_parent_object_id : ' || l_parent_object_id);
2370 mydebug('5: p_parent_object_id : ' || p_parent_object_id); END IF;
2371
2372 ELSE
2373 BEGIN
2374 SELECT gen_object_id
2375 INTO l_parent_object_id
2376 FROM wip_entities
2377 WHERE wip_entity_name = LTRIM(RTRIM(p_parent_object_number))
2378 AND organization_id = p_parent_org_id;
2379 IF (p_debug = 1) THEN mydebug('5: l_parent_object_id: ' || l_parent_object_id); END IF;
2380
2381 EXCEPTION
2382 WHEN NO_DATA_FOUND THEN
2383 x_return_status := lg_ret_sts_error; -- R12
2384 IF (p_debug = 1) THEN mydebug('Job not found using - p_parent_object_number,'); END IF;
2385 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2386 fnd_message.set_token('ENTITY1', l_invalid_comb_msg);
2387 fnd_msg_pub.ADD;
2388 --RAISE lg_exc_error;
2389 END;
2390 END IF;
2391 END IF;
2392
2393 IF (p_debug = 1) THEN mydebug(' After deriving Parent Object Id');
2394 mydebug('x_return_status: ' || x_return_status);
2395 END IF;
2396
2397 l_invalid_field_msg := 'Object ID2';
2398 l_invalid_comb_msg := 'Org, Item and Object Number2 Combination';
2399
2400 -- R12 Genealogy Enhancements: Start
2401 -- Validate/Derive the second set of object details
2402 IF l_object_type2 = 1 THEN
2403 IF p_object_id2 IS NOT NULL THEN
2404 IF p_validation_level = lg_fnd_valid_level_full THEN
2405 IF (p_debug = 1) THEN
2406 mydebug('{{- Only if p_validation_level is FULL that parameters- object id2 will be validated }}');
2407 END IF;
2411 WHERE gen_object_id = p_object_id2;
2408 SELECT COUNT(*)
2409 INTO l_dummy
2410 FROM mtl_lot_numbers
2412
2413 IF l_dummy = 0 THEN
2414 x_return_status := lg_ret_sts_error; -- R12
2415 IF (p_debug = 1) THEN mydebug('Lot not found using - p_object_id2,'); END IF;
2416 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2417 fnd_message.set_token('ENTITY1', l_invalid_field_msg);
2418 fnd_msg_pub.ADD;
2419 --RAISE lg_exc_error;
2420 END IF;
2421 END IF;
2422
2423 l_object_id2 := p_object_id2;
2424 ELSE
2425 IF LTRIM(RTRIM(p_object_number2)) IS NOT NULL THEN
2426 BEGIN
2427 SELECT gen_object_id
2428 INTO l_object_id2
2429 FROM mtl_lot_numbers
2430 WHERE lot_number = LTRIM(RTRIM(p_object_number2))
2431 AND inventory_item_id = p_inventory_item_id
2432 AND organization_id = p_org_id;
2433 EXCEPTION
2434 WHEN NO_DATA_FOUND THEN
2435 x_return_status := lg_ret_sts_error; -- R12
2436 IF (p_debug = 1) THEN mydebug('Lot not found using - p_object_number2,'); END IF;
2437 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2438 fnd_message.set_token('ENTITY1', l_invalid_comb_msg);
2439 fnd_msg_pub.ADD;
2440 --RAISE lg_exc_error;
2441 END;
2442 ELSE
2443 l_object_id2 := NULL;
2444 END IF;
2445 END IF;
2446 END IF;
2447
2448 IF (p_debug = 1) THEN
2449 mydebug(' After validating / deriving l_Object Id2: ' || l_object_id2);
2450 mydebug('x_return_status: ' || x_return_status);
2451 END IF;
2452
2453 l_invalid_field_msg := 'Parent Object ID2';
2454 l_invalid_comb_msg := 'Parent Org, Item and Object Number2 Combination';
2455 IF l_parent_object_type2 = 1 THEN
2456 IF p_parent_object_id2 IS NOT NULL THEN
2457 IF p_validation_level = lg_fnd_valid_level_full THEN
2458 IF (p_debug = 1) THEN
2459 mydebug('{{- Only if p_validation_level is FULL that parameters- parent object_id2 will be validated }}');
2460 END IF;
2461 SELECT COUNT(*)
2462 INTO l_dummy
2463 FROM mtl_lot_numbers
2464 WHERE gen_object_id = p_parent_object_id2;
2465
2466 IF l_dummy = 0 THEN
2467 x_return_status := lg_ret_sts_error; -- R12
2468 IF (p_debug = 1) THEN mydebug('Lot not found using - p_parent_object_id2,'); END IF;
2469 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2470 fnd_message.set_token('ENTITY1', l_invalid_field_msg);
2471 fnd_msg_pub.ADD;
2472 --RAISE lg_exc_error;
2473 END IF;
2474 END IF;
2475
2476 l_parent_object_id2 := p_parent_object_id2;
2477 ELSE
2478 IF LTRIM(RTRIM(p_parent_object_number2)) IS NOT NULL THEN
2479 BEGIN
2480 SELECT gen_object_id
2481 INTO l_parent_object_id2
2482 FROM mtl_lot_numbers
2483 WHERE lot_number = LTRIM(RTRIM(p_parent_object_number2))
2484 AND inventory_item_id = p_parent_inventory_item_id
2485 AND organization_id = p_parent_org_id;
2486 EXCEPTION
2487 WHEN NO_DATA_FOUND THEN
2488 x_return_status := lg_ret_sts_error; -- R12
2489 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2490 fnd_message.set_token('ENTITY1', l_invalid_comb_msg);
2491 fnd_msg_pub.ADD;
2492 IF (p_debug = 1) THEN mydebug('Lot not found using - p_parent_object_number2,'); END IF;
2493 --RAISE lg_exc_error;
2494 END;
2495 ELSE
2496 l_parent_object_id2 := NULL;
2497 END IF;
2498 END IF;
2499 END IF;
2500
2501 IF (p_debug = 1) THEN mydebug(' After validating/deriving Parent Object Id2: ' || l_parent_object_id2);
2502 mydebug('x_return_status: ' || x_return_status);
2503 END IF;
2504 -- R12 Genealogy Enhancements: End
2505 -- Validate/Derive the second set of object details
2506
2507
2508 IF p_genealogy_origin IS NOT NULL THEN
2509 IF p_validation_level = lg_fnd_valid_level_full THEN
2510 -- {{- only if p_validation_level is FULL that the input parameters p_genealogy_origin will be validated }}
2511 SELECT COUNT(*)
2512 INTO l_dummy
2513 FROM mfg_lookups
2514 WHERE lookup_type = 'INV_GENEALOGY_ORIGIN'
2515 AND lookup_code = p_genealogy_origin;
2516
2517 IF l_dummy = 0 THEN
2518 x_return_status := lg_ret_sts_error; -- R12
2519 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2520 fnd_message.set_token('ENTITY1', 'p_genealogy_origin:' || TO_CHAR(p_genealogy_origin));
2521 fnd_msg_pub.ADD;
2522 IF (p_debug = 1) THEN mydebug('Gene. Origin not found in mfg_lookups,'); END IF;
2523 --RAISE lg_exc_error;
2524 END IF;
2525 END IF;
2526 END IF;
2527
2528 -- Valid values are :
2529 -- 1- Assembly component
2530 -- 2- Lot split
2531 -- 3- lot merge
2532 -- 5- Asset
2536 IF p_validation_level = lg_fnd_valid_level_full THEN
2533 -- if p_object_type = 2 then p_genealogy_type of 1 is valid otherwise all of the above are valid
2534 -- (Removed this condition in R12 )
2535
2537 -- {{- only if p_validation_level is FULL that the input parameters p_genealogy_origin will be validated }}
2538 IF p_genealogy_type NOT IN (1, 2, 3, 4, 5)
2539 AND p_genealogy_type IS NOT NULL THEN
2540 x_return_status := lg_ret_sts_error; -- R12
2541 fnd_message.set_name('INV', 'INV_FIELD_INVALID');
2542 fnd_message.set_token('ENTITY1', 'P_genealogy_type');
2543 fnd_msg_pub.ADD;
2544 IF (p_debug = 1) THEN mydebug('Gene. Type is invalid ,'); END IF;
2545 --RAISE lg_exc_error;
2546 END IF;
2547 END IF;
2548
2549 /* {{- Removed this condition for R12 since we will have more genealogy types for object_type = 2(serials) }}
2550
2551 {{ IF p_object_type = 2 THEN }}
2552 {{ IF p_genealogy_type NOT IN (1, 5) THEN }}
2553 fnd_message.set_name('INV', {{' INV_FIELD_INVALID'}});
2554 fnd_message.set_token('ENTITY1', 'P_genealogy_type');
2555 fnd_msg_pub.ADD;
2556 RAISE lg_exc_error;
2557 END IF;
2558 END IF; */
2559
2560
2561 -- R12 Genealogy Enhancements: Start
2562 -- set Return parameters or raise an exception if any of the above
2563 -- validations failed
2564 IF (p_debug = 1) THEN
2565 mydebug('l_object_id: ' || l_object_id );
2566 mydebug('l_parent_object_id: ' || l_parent_object_id );
2567 mydebug('l_object_id2: ' || l_object_id2 );
2568 mydebug('l_parent_object_id2: ' || l_parent_object_id2 );
2569 mydebug('x_return_status: ' || x_return_status );
2570 END IF;
2571
2572 IF l_object_id IS NULL OR l_parent_object_id IS NULL THEN
2573 x_return_status := lg_ret_sts_error;
2574 fnd_message.set_name('INV', 'INV_NULLOBJECTID'); -- mrana addmsg
2575 IF l_object_id is NULL THEN
2576 fnd_message.set_token('ENTITY1', 'p_object_number');
2577 ELSIF l_parent_object_id IS NULL THEN
2578 fnd_message.set_token('ENTITY1', 'p_parent_object_number');
2579 END IF;
2580 fnd_msg_pub.ADD;
2581 IF (p_debug = 1) THEN mydebug('Gene. Type is invalid ,'); END IF;
2582 END IF;
2583
2584 IF x_return_status = lg_ret_sts_error
2585 THEN
2586 IF (p_debug = 1) THEN mydebug('{{ If any of the parameter validations failed, then raise ' ||
2587 'an exception now..before validating the object ids }}');
2588 END IF;
2589 RAISE lg_exc_error;
2590 END IF;
2591 IF x_return_status = lg_ret_sts_unexp_error
2592 THEN
2593 IF (p_debug = 1) THEN mydebug('{{ If any of the parameter validations failed, then raise ' ||
2594 'an exception now..before validating the object ids }}');
2595 END IF;
2596 RAISE lg_exc_unexpected_error;
2597 END IF;
2598
2599 p_object_id := l_object_id ;
2600 p_parent_object_id := l_parent_object_id;
2601 p_object_type2 := l_object_type2;
2602 p_object_id2 := l_object_id2;
2603 p_parent_object_type2 := l_parent_object_type2 ;
2604 p_parent_object_id2 := l_parent_object_id2 ;
2605
2606 IF (p_debug = 1) THEN
2607 mydebug('After setting value for IO parameters..before returning to caller ');
2608 mydebug('l_object_id2: ' || l_object_id2 );
2609 mydebug('l_parent_object_id2: ' || l_parent_object_id2 );
2610 mydebug('l_object_type2: ' || l_object_type2 );
2611 mydebug('l_parent_object_type2: ' || l_parent_object_type2 );
2612 mydebug('l_object_id: ' || l_object_id );
2613 mydebug('l_parent_object_id: ' || l_parent_object_id );
2614 END IF;
2615
2616 -- R12 Genealogy Enhancements: End
2617 -- Return parameters
2618 EXCEPTION
2619 WHEN lg_exc_error THEN
2620 IF (p_debug = 1) THEN mydebug('exception G_EXC_ERROR'|| x_msg_data); END IF;
2621 x_return_status := lg_ret_sts_error;
2622 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
2623 WHEN lg_exc_unexpected_error THEN
2624 IF (p_debug = 1) THEN mydebug('exception G_UNEXC_ERROR'|| x_msg_data); END IF;
2625 x_return_status := lg_ret_sts_unexp_error;
2626 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
2627 WHEN OTHERS THEN
2628 IF (p_debug = 1) THEN mydebug('exception WHEN OTHERS'|| x_msg_data); END IF;
2629 x_return_status := lg_ret_sts_unexp_error;
2630 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2631 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2632 END IF;
2633 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
2634
2635 END parameter_validations;
2636
2637 -- R12 genealogy Enhancements : Added a new procedure to update genealogy
2638 --- Primarily used to disable an existing relationship in case of serial
2639 -- component or assembly returns in this release
2640
2641 PROCEDURE update_genealogy(
2642 p_api_version IN NUMBER
2643 , p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
2644 , p_commit IN VARCHAR2 := gen_fnd_g_false
2645 , p_validation_level IN NUMBER := gen_fnd_valid_level_full
2649 , p_object_number IN VARCHAR2 := NULL
2646 , p_object_type IN NUMBER
2647 , p_parent_object_type IN NUMBER := NULL
2648 , p_object_id IN NUMBER := NULL
2650 , p_inventory_item_id IN NUMBER := NULL
2651 , p_organization_id IN NUMBER := NULL
2652 , p_parent_object_id IN NUMBER := NULL
2653 , p_parent_object_number IN VARCHAR2 := NULL
2654 , p_parent_inventory_item_id IN NUMBER := NULL
2655 , p_parent_org_id IN NUMBER := NULL
2656 , p_genealogy_origin IN NUMBER := NULL
2657 , p_genealogy_type IN NUMBER := NULL
2658 , p_start_date_active IN DATE := SYSDATE
2659 , p_end_date_active IN DATE := NULL
2660 , p_origin_txn_id IN NUMBER := NULL
2661 , p_update_txn_id IN NUMBER := NULL
2662 , p_object_type2 IN NUMBER := NULL
2663 , p_object_id2 IN NUMBER := NULL
2664 , p_object_number2 IN VARCHAR2 := NULL
2665 , p_parent_object_type2 IN NUMBER := NULL
2666 , p_parent_object_id2 IN NUMBER := NULL
2667 , p_parent_object_number2 IN VARCHAR2 := NULL
2668 , p_child_lot_control_code IN NUMBER := NULL
2669 , p_parent_lot_control_code IN NUMBER := NULL
2670 , p_transaction_type IN VARCHAR2 := NULL -- ASSEMBLY_RETURN, COMP_RETURN, NULL
2671 , x_return_status OUT NOCOPY VARCHAR2
2672 , x_msg_count OUT NOCOPY NUMBER
2673 , x_msg_data OUT NOCOPY VARCHAR2
2674 ) IS
2675 -- 2/2/06: Bug: 4997221 : Added new parameter p_transaction_type
2676 l_api_version CONSTANT NUMBER := 1.0;
2677 l_api_name CONSTANT VARCHAR2(30) := 'update_genealogy';
2678 l_org_id NUMBER;
2679 l_parent_org_id NUMBER;
2680 l_object_id NUMBER;
2681 l_parent_object_id NUMBER;
2682 l_object_id2 NUMBER;
2683 l_parent_object_id2 NUMBER;
2684 l_object_type2 NUMBER;
2685 l_parent_object_type2 NUMBER;
2686 l_parent_object_type NUMBER;
2687 l_parent_inventory_item_id NUMBER;
2688 l_inventory_item_id NUMBER;
2689 l_child_item_type NUMBER;
2690 l_parent_item_type NUMBER;
2691 l_serial_number VARCHAR2(30);
2692 l_debug NUMBER :=1 ; -- := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2693 l_invalid_field_msg VARCHAR2(50);
2694 l_invalid_comb_msg VARCHAR2(150);
2695 l_child_lot_control_code NUMBER;
2696 l_parent_lot_control_code NUMBER;
2697 l_action VARCHAR2(10);
2698 l_end_date_active DATE;
2699 l_return_status VARCHAR2(10);
2700 l_msg_count NUMBER;
2701 l_msg_data VARCHAR2(240);
2702 BEGIN
2703 -- Standard Start of API savepoint
2704 x_return_status := lg_ret_sts_success;
2705 SAVEPOINT save_update_genealogy;
2706 g_mod_name := 'update_Genealogy';
2707
2708 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2709 RAISE lg_exc_unexpected_error;
2710 END IF;
2711
2712 IF (l_debug = 1) THEN
2713 mydebug('In procedure: ' || g_mod_name );
2714 mydebug('p_api_version: ' || p_api_version );
2715 mydebug('p_init_msg_list: ' || p_init_msg_list );
2716 mydebug('p_commit: ' || p_commit );
2717 mydebug('p_validation_level: ' || p_validation_level );
2718 mydebug('p_object_type: ' || p_object_type );
2719 mydebug('p_parent_object_type: ' || p_parent_object_type );
2720 mydebug('p_object_id: ' || p_object_id );
2721 mydebug('p_object_number: ' || p_object_number );
2722 mydebug('p_inventory_item_id: ' || p_inventory_item_id );
2723 mydebug('p_organization_id: ' || p_organization_id );
2724 mydebug('p_parent_object_id: ' || p_parent_object_id );
2725 mydebug('p_parent_object_number: ' || p_parent_object_number );
2726 mydebug('p_parent_inventory_item_id: ' || p_parent_inventory_item_id );
2727 mydebug('p_parent_org_id: ' || p_parent_org_id );
2728 mydebug('p_genealogy_origin: ' || p_genealogy_origin );
2729 mydebug('p_genealogy_type: ' || p_genealogy_type );
2730 mydebug('p_start_date_active: ' || p_start_date_active );
2731 mydebug('p_end_date_active: ' || p_end_date_active );
2732 mydebug('p_origin_txn_id: ' || p_origin_txn_id );
2733 mydebug('p_update_txn_id: ' || p_update_txn_id );
2734 mydebug('p_object_type2: ' || p_object_type2 );
2735 mydebug('p_object_id2: ' || p_object_id2 );
2736 mydebug('p_object_number2: ' || p_object_number2 );
2737 mydebug('p_parent_object_type2: ' || p_parent_object_type2 );
2738 mydebug('p_parent_object_id2: ' || p_parent_object_id2 );
2739 mydebug('p_parent_object_number2: ' || p_parent_object_number2 );
2740 mydebug('p_child_lot_control_code: ' || p_child_lot_control_code );
2741 mydebug('p_parent_lot_control_code: ' || p_parent_lot_control_code );
2742 mydebug('p_transaction_type: ' || p_transaction_type );
2743 END IF;
2744
2745 IF fnd_api.to_boolean(p_init_msg_list) THEN
2749 IF p_parent_object_type IS NULL THEN
2746 fnd_msg_pub.initialize;
2747 END IF;
2748
2750 l_parent_object_type := p_object_type;
2751 ELSE
2752 l_parent_object_type := p_parent_object_type;
2753 END IF;
2754
2755 IF p_parent_org_id IS NULL THEN
2756 l_parent_org_id := p_organization_id;
2757 ELSE
2758 l_parent_org_id := p_parent_org_id;
2759 END IF;
2760
2761 l_action := 'UPDATE';
2762
2763 l_object_id2 := p_object_id2;
2764 l_parent_object_id2 := p_parent_object_id2;
2765 l_object_type2 := p_object_type2;
2766 l_parent_object_type2 := p_parent_object_type2;
2767 l_child_lot_control_code := p_child_lot_control_code ;
2768 l_parent_lot_control_code := p_parent_lot_control_code ;
2769 l_object_id := p_object_id;
2770 l_parent_object_id := p_parent_object_id;
2771
2772
2773 parameter_validations (
2774 p_validation_level => p_validation_level
2775 , p_object_type => p_object_type
2776 , p_parent_object_type => l_parent_object_type
2777 , p_object_id => l_object_id -- IN OUT
2778 , p_object_number => p_object_number
2779 , p_inventory_item_id => p_inventory_item_id
2780 , p_org_id => p_organization_id
2781 , p_parent_object_id => l_parent_object_id -- IN OUT
2782 , p_parent_object_number => p_parent_object_number
2783 , p_parent_inventory_item_id => p_parent_inventory_item_id
2784 , p_parent_org_id => l_parent_org_id
2785 , p_genealogy_origin => p_genealogy_origin
2786 , p_genealogy_type => p_genealogy_type
2787 , p_start_date_active => p_start_date_active
2788 , p_end_date_active => p_end_date_active
2789 , p_origin_txn_id => p_origin_txn_id
2790 , p_update_txn_id => p_update_txn_id
2791 , p_object_type2 => l_object_type2 -- IN OUT
2792 , p_object_id2 => l_object_id2 -- IN OUT
2793 , p_object_number2 => p_object_number2
2794 , p_parent_object_type2 => l_parent_object_type2 -- IN OUT
2795 , p_parent_object_id2 => l_parent_object_id2 -- IN OUT
2796 , p_parent_object_number2 => p_parent_object_number2
2797 , p_child_lot_control_code => p_child_lot_control_code
2798 , p_parent_lot_control_code => p_parent_lot_control_code
2799 , p_action => l_action
2800 , p_debug => l_debug
2801 , x_return_status => l_return_status
2802 , x_msg_count => l_msg_count
2803 , x_msg_data => l_msg_data);
2804 g_mod_name := 'update_Genealogy';
2805
2806 IF (l_debug = 1) THEN
2807 mydebug('x_return_status from parameter_validations API: ' || x_return_status); END IF;
2808
2809 IF x_return_status = lg_ret_sts_error
2810 THEN
2811 IF (l_debug = 1) THEN
2812 mydebug('{{ If any of the parameter validations failed, then raise ' ||
2813 'an expected exception now..before inserting into genealogy }}' );
2814 END IF;
2815 RAISE lg_exc_error;
2816 END IF;
2817 IF x_return_status = lg_ret_sts_unexp_error
2818 THEN
2819 IF (l_debug = 1) THEN mydebug('{{ If any of the parameter validations failed, then raise ' ||
2820 'an unexpected exception now..before inserting into genealogy }}');
2821 END IF;
2822 RAISE lg_exc_unexpected_error;
2823 END IF;
2824
2825
2826 IF (l_debug = 1) THEN
2827 mydebug('After calling parameter validations, check the value of IN OUT parameters ' );
2828 mydebug('l_object_id := ' || l_object_id );
2829 mydebug('l_parent_object_id := ' || l_parent_object_id);
2830 mydebug('l_object_type2 := ' || l_object_type2);
2831 mydebug('l_object_id2 := ' || l_object_id2);
2832 mydebug('l_parent_object_type2 := ' || l_parent_object_type2) ;
2833 mydebug('l_parent_object_id2 := ' || l_parent_object_id2) ;
2834 END IF;
2835
2836
2837 IF p_end_date_active IS NULL THEN
2838 l_end_date_active := SYSDATE;
2839 ELSE
2840 l_end_date_active := p_end_date_active;
2841 END IF;
2842
2843 IF (l_debug = 1) THEN
2844 mydebug('{{- Only if the relationship exists that it can be updated }}' );
2845 mydebug('{{- Check the end_date_active in the genealogy record is populated }} ');
2846 END IF ;
2847 /* 4997221: 02/02/2006: Added following logic for R12 assembly to job
2848 * relationship */
2852 BEGIN
2849 IF p_transaction_type = 'ASSEMBLY_RETURN' THEN
2850 IF l_object_id2 IS NULL THEN
2851 IF (l_debug = 1) THEN mydebug('{{- AR: Genealogy is not between lot+serial controlled items }}'); END IF;
2853 UPDATE mtl_object_genealogy
2854 SET last_update_date = SYSDATE
2855 , last_updated_by = -1
2856 , end_date_active = l_end_date_active
2857 , update_txn_id = p_update_txn_id
2858 , last_update_login = -1
2859 , request_id = -1
2860 , program_application_id = fnd_global.prog_appl_id
2861 , program_id = fnd_global.conc_program_id
2862 , program_update_date = SYSDATE
2863 WHERE end_date_active IS NULL
2864 AND parent_object_id = l_object_id
2865 AND parent_object_id2 IS NULL
2866 AND object_type = 5 -- Job : 5368998
2867 AND object_id = l_parent_object_id -- Job's gen object id : 5368998
2868 AND genealogy_type <> 5;
2869 /* 5368998: Added the above consition to make sure that only one
2870 * relation ship gets disabled. When assembly is completed, genealogy is created
2871 * between assembly and Job , so during assembly return only this genealogy
2872 * should be disabled */
2873 IF SQL%NOTFOUND THEN
2874 IF (l_debug = 1) THEN
2875 mydebug(' {{- AR: NO relationship between object_id and any other object :' || l_object_id || '}}');
2876 END IF;
2877 ELSE
2878 IF (l_debug = 1) THEN
2879 mydebug(' {{- AR: Number of relationships deleted for parent_object_id :'
2880 || l_object_id || ' - IS:' || sql%rowcount || '}}');
2881 END IF;
2882 END IF;
2883
2884 END ;
2885 ELSE
2886 IF (l_debug = 1) THEN
2887 mydebug(' {{- AR: Genealogy is between lot+serial child and non lot+serial parent }}');
2888 END IF;
2889 BEGIN
2890 UPDATE mtl_object_genealogy
2891 SET last_update_date = SYSDATE
2892 , last_updated_by = -1
2893 , end_date_active = l_end_date_active
2894 , update_txn_id = p_update_txn_id
2895 , last_update_login = -1
2896 , request_id = fnd_global.conc_request_id
2897 , program_application_id = fnd_global.prog_appl_id
2898 , program_id = fnd_global.conc_program_id
2899 , program_update_date = SYSDATE
2900 WHERE end_date_active IS NULL
2901 AND parent_object_id = l_object_id
2902 AND parent_object_id2 = l_object_id2
2903 AND object_type = 5 -- Job : 5368998
2904 AND object_id = l_parent_object_id -- Job's gen object id : 5368998
2905 AND genealogy_type <> 5;
2906 IF SQL%NOTFOUND THEN
2907 IF (l_debug = 1) THEN
2908 mydebug(' {{- AR: NO relationship between object_id,object_id2 and any other object :' || l_object_id
2909 || ':' || l_object_id2 || '}}');
2910 END IF;
2911 ELSE
2912 IF (l_debug = 1) THEN
2913 mydebug(' {{- AR: Number of relationships deleted for object_id ,object_id2 and any other object :'
2914 || l_object_id || ':' || l_object_id2 || ' - IS:' || sql%rowcount || '}}');
2915 END IF;
2916 END IF;
2917
2918 END ;
2919 END IF ;
2920 ELSE -- IF p_transaction_type = 'COMP_RETURN' OR NULL
2921 IF l_object_id2 IS NULL THEN
2922 IF (l_debug = 1) THEN mydebug('{{- CR: Genealogy is not between lot+serial controlled items }}'); END IF;
2926 , last_updated_by = -1
2923 BEGIN
2924 UPDATE mtl_object_genealogy
2925 SET last_update_date = SYSDATE
2927 , end_date_active = l_end_date_active
2928 , update_txn_id = p_update_txn_id
2929 , last_update_login = -1
2930 , request_id = -1
2931 , program_application_id = fnd_global.prog_appl_id
2932 , program_id = fnd_global.conc_program_id
2933 , program_update_date = SYSDATE
2934 WHERE end_date_active IS NULL
2935 AND object_id = l_object_id
2936 AND object_id2 IS NULL
2937 AND ( (parent_object_type = 5 -- Job : 5368998
2938 AND parent_object_id = l_parent_object_id) -- Job's gen object id : 5368998
2939 OR (parent_object_type <> 5))
2940 AND genealogy_type = 1;
2941 /* Bug: 5368998: Changed it from <>5 to =1, to make sure that only the genealogy
2942 created by comp issue/assembly completion gets updated */
2943 IF SQL%NOTFOUND THEN
2944 IF (l_debug = 1) THEN
2945 mydebug(' {{- CR: NO relationship between object_id and any other object :' || l_object_id || '}}');
2946 END IF;
2947 ELSE
2948 IF (l_debug = 1) THEN
2949 mydebug(' {{- CR: Number of relationships deleted for parent_object_id :'
2950 || l_object_id || ' - IS:' || sql%rowcount || '}}');
2951 END IF;
2952 END IF;
2953
2954 END ;
2955 ELSE
2956 IF (l_debug = 1) THEN
2957 mydebug(' {{- CR: Genealogy is between lot+serial child and non lot+serial parent }}');
2958 END IF;
2959 BEGIN
2960 UPDATE mtl_object_genealogy
2961 SET last_update_date = SYSDATE
2962 , last_updated_by = -1
2963 , end_date_active = l_end_date_active
2964 , update_txn_id = p_update_txn_id
2965 , last_update_login = -1
2966 , request_id = fnd_global.conc_request_id
2967 , program_application_id = fnd_global.prog_appl_id
2968 , program_id = fnd_global.conc_program_id
2969 , program_update_date = SYSDATE
2970 WHERE end_date_active IS NULL
2971 AND object_id = l_object_id
2972 AND object_id2 = l_object_id2
2973 AND ( (parent_object_type = 5 -- Job : 5368998
2974 AND parent_object_id = l_parent_object_id) -- Job's gen object id : 5368998
2975 OR (parent_object_type <> 5))
2976 AND genealogy_type = 1;
2977 /* Bug: 5368998: Changed it from <>5 to =1, to make sure that only the genealogy
2978 created by comp issue/assembly completion gets updated */
2979
2980 IF SQL%NOTFOUND THEN
2981 IF (l_debug = 1) THEN
2982 mydebug(' {{- CR: NO relationship between object_id,object_id2 and any other object :' || l_object_id
2983 || ':' || l_object_id2 || '}}');
2984 END IF;
2985 ELSE
2986 IF (l_debug = 1) THEN
2987 mydebug(' {{- CR: Number of relationships deleted for object_id ,object_id2 and any other object :'
2988 || l_object_id || ':' || l_object_id2 || ' - IS:' || sql%rowcount || '}}');
2989 END IF;
2990 END IF;
2991
2992 END ;
2993 END IF ;
2994 UPDATE mtl_serial_numbers
2995 SET parent_serial_number = NULL
2996 ,last_update_date = SYSDATE
2997 ,last_updated_by = -1
2998 ,last_update_login = -1
2999 WHERE gen_object_id = l_object_id;
3000
3001 END IF;
3002
3003 IF (l_debug = 1) THEN mydebug('End of '|| g_mod_name); END IF;
3004 EXCEPTION
3005 WHEN lg_exc_error THEN
3006 IF (l_debug = 1) THEN mydebug('exception G_EXC_ERROR'|| x_msg_data); END IF;
3007 ROLLBACK TO save_update_genealogy;
3008 x_return_status := lg_ret_sts_error;
3009 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
3010
3011 WHEN lg_exc_unexpected_error THEN
3012 IF (l_debug = 1) THEN mydebug('exception G_UNEXC_ERROR'|| x_msg_data); END IF;
3013 ROLLBACK TO save_update_genealogy;
3014 x_return_status := lg_ret_sts_unexp_error;
3015 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
3016 WHEN OTHERS THEN
3017 IF (l_debug = 1) THEN mydebug('exception WHEN OTHERS'|| x_msg_data); END IF;
3018 ROLLBACK TO save_update_genealogy;
3019 x_return_status := lg_ret_sts_unexp_error;
3020 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3021 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3022 END IF;
3023 fnd_msg_pub.count_and_get(p_encoded => lg_fnd_g_false, p_count => x_msg_count, p_data => x_msg_data);
3024 END update_genealogy;
3025 END inv_genealogy_pub;
[Home] [Help]