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