1 PACKAGE BODY csi_Item_Instance_Vld_pvt AS
2 /* $Header: csiviivb.pls 120.38.12010000.2 2008/10/03 19:47:59 rsinn ship $ */
3
4 -- ------------------------------------------------------------
5 -- Define global variables
6 -- ------------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_ITEM_INSTANCE_VLD_PVT';
9 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csiviivb.pls';
10
11 /*-----------------------------------------------------------*/
12 /* Procedure name: Check_Reqd_Param */
13 /* Description : To Check if the reqd parameter is passed */
14 /*-----------------------------------------------------------*/
15
16 PROCEDURE Check_Reqd_Param_num
17 (
18 p_number IN NUMBER,
19 p_param_name IN VARCHAR2,
20 p_api_name IN VARCHAR2
21 ) IS
22 BEGIN
23 IF (NVL(p_number,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) THEN
24 FND_MESSAGE.SET_NAME('CSI','CSI_API_REQD_PARAM_MISSING');
25 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
26 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
27 FND_MSG_PUB.Add;
28 RAISE FND_API.G_EXC_ERROR;
29 END IF;
30 END Check_Reqd_Param_num;
31
32 /*-----------------------------------------------------------*/
33 /* Procedure name: Check_Reqd_Param */
34 /* Description : To Check if the reqd parameter is passed */
35 /*-----------------------------------------------------------*/
36
37 PROCEDURE Check_Reqd_Param_char
38 (
39 p_variable IN VARCHAR2,
40 p_param_name IN VARCHAR2,
41 p_api_name IN VARCHAR2
42 ) IS
43 BEGIN
44 IF (NVL(p_variable,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR) THEN
45 FND_MESSAGE.SET_NAME('CSI','CSI_API_REQD_PARAM_MISSING');
46 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
47 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
48 FND_MSG_PUB.Add;
49 RAISE FND_API.G_EXC_ERROR;
50 END IF;
51 END Check_Reqd_Param_char;
52
53 /*-----------------------------------------------------------*/
54 /* Procedure name: Check_Reqd_Param */
55 /* Description : To Check if the reqd parameter is passed */
56 /*-----------------------------------------------------------*/
57
58 PROCEDURE Check_Reqd_Param_date
59 (
60 p_date IN DATE,
61 p_param_name IN VARCHAR2,
62 p_api_name IN VARCHAR2
63 ) IS
64 BEGIN
65 IF (NVL(p_date,FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) THEN
66 FND_MESSAGE.SET_NAME('CSI','CSI_API_REQD_PARAM_MISSING');
67 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
68 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
69 FND_MSG_PUB.Add;
70 RAISE FND_API.G_EXC_ERROR;
71 END IF;
72 END Check_Reqd_Param_date;
73
74 /*-----------------------------------------------------*/
75 /* Validates the item instance id */
76 /*-----------------------------------------------------*/
77
78 FUNCTION InstanceExists
79 ( p_item_instance_id IN NUMBER,
80 p_stack_err_msg IN BOOLEAN
81 ) RETURN BOOLEAN IS
82
83 l_dummy VARCHAR2(2);
84 l_return_value BOOLEAN := TRUE;
85 l_instance_number VARCHAR2(30):= substr(to_char(p_item_instance_id),1,30);
86 BEGIN
87 SELECT 'x'
88 INTO l_dummy
89 FROM csi_item_instances
90 WHERE instance_id = p_item_instance_id
91 OR instance_number = l_instance_number;
92 l_return_value := TRUE;
93 IF ( p_stack_err_msg = TRUE ) THEN
94 FND_MESSAGE.SET_NAME('CSI','CSI_API_INSTANCE_EXISTS');
95 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_item_instance_id);
96 FND_MESSAGE.SET_TOKEN('INSTANCE_NUMBER',p_item_instance_id);
97 FND_MSG_PUB.Add;
98 END IF;
99 -- RETURN l_return_value;
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 l_return_value := FALSE;
103 RETURN l_return_value;
104 END InstanceExists;
105 --
106 /*-----------------------------------------------------*/
107 /* Validates the termination status */
108 /* Modified this routine to look at terminated_flag */
109 /* instead of service_order_allowed_flag since OKS call*/
110 /* with TRM transaction_type should depend on this. */
111 /*-----------------------------------------------------*/
112
113 FUNCTION termination_status
114 ( p_instance_status_id IN NUMBER
115 ) RETURN BOOLEAN IS
116 l_flag VARCHAR2(1);
117 l_return_value BOOLEAN := FALSE;
118 BEGIN
119 BEGIN
120 IF p_instance_status_id IS NOT NULL
121 THEN
122 SELECT terminated_flag -- service_order_allowed_flag Bug # 3945813 srramakr
123 INTO l_flag
124 FROM csi_instance_statuses
125 WHERE instance_status_id = p_instance_status_id;
126 -- IF upper(l_flag)='N' -- check for N while selecting service_order_allowed_flag
127 IF upper(l_flag)='Y' -- check for Y while selecting terminated_flag
128 THEN
129 l_return_value := TRUE;
130 ELSE
131 l_return_value := FALSE;
132 END IF;
133 ELSE
134 l_return_value := FALSE;
135 END IF;
136 EXCEPTION
137 WHEN OTHERS THEN
138 l_return_value := FALSE;
139 END;
140 RETURN l_return_value;
141 END termination_status;
142
143 /*-----------------------------------------------------*/
144 /* Validates the item instance number */
145 /*-----------------------------------------------------*/
146
147 FUNCTION Is_InstanceNum_Valid
148 ( p_item_instance_id IN NUMBER,
149 p_instance_number IN VARCHAR2,
150 p_mode IN VARCHAR2,
151 p_stack_err_msg IN BOOLEAN
152 ) RETURN BOOLEAN IS
153 l_instance_id NUMBER;
154 l_return_value BOOLEAN := TRUE;
155 l_instance_number VARCHAR2(30);
156 BEGIN
157 IF p_mode='CREATE'
158 THEN
159 IF ((p_item_instance_id IS NULL) OR
160 (p_item_instance_id = FND_API.G_MISS_NUM)) THEN
161 l_return_value := FALSE;
162 ELSE
163 IF ((p_instance_number IS NULL) OR
164 (p_instance_number = FND_API.G_MISS_CHAR)) THEN
165 l_return_value := TRUE;
166 ELSE
167 -- Added for eam integration
168 BEGIN
169 SELECT instance_number
170 INTO l_instance_number
171 FROM csi_item_instances
172 WHERE instance_number=p_instance_number;
173 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_NUM');
174 FND_MESSAGE.SET_TOKEN('INSTANCE_NUMBER',p_instance_number);
175 FND_MSG_PUB.Add;
176 l_return_value := FALSE;
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 l_return_value := TRUE;
180 WHEN TOO_MANY_ROWS THEN
181 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_NUM');
182 FND_MESSAGE.SET_TOKEN('INSTANCE_NUMBER',p_instance_number);
183 FND_MSG_PUB.Add;
184 l_return_value := FALSE;
185 END;
186 -- End addition for eam integration
187 -- Start commentation for eam integration
188 /*
189 IF (to_char(p_item_instance_id) <> p_instance_number) THEN
190 IF ( p_stack_err_msg = TRUE ) THEN
191 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_NUM');
192 FND_MESSAGE.SET_TOKEN('INSTANCE_NUMBER',p_instance_number);
193 FND_MSG_PUB.Add;
194 l_return_value := FALSE;
195 END IF;
196 END IF;
197 */
198 -- End commentation for eam integration
199 END IF;
200 END IF;
201 ELSIF p_mode='UPDATE'
202 THEN
203 -- Added for eam integration
204 BEGIN
205 SELECT instance_number
206 INTO l_instance_number
207 FROM csi_item_instances
208 WHERE instance_number = p_instance_number
209 AND instance_id <> p_item_instance_id;
210 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_NUM');
211 FND_MESSAGE.SET_TOKEN('INSTANCE_NUMBER',p_instance_number);
212 FND_MSG_PUB.Add;
213 l_return_value := FALSE;
214 EXCEPTION
215 WHEN NO_DATA_FOUND THEN
216 l_return_value := TRUE;
217 WHEN TOO_MANY_ROWS THEN
218 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_NUM');
219 FND_MESSAGE.SET_TOKEN('INSTANCE_NUMBER',p_instance_number);
220 FND_MSG_PUB.Add;
221 l_return_value := FALSE;
222 END;
223 -- End addition for eam integration
224 END IF;
225 RETURN l_return_value;
226 END Is_InstanceNum_Valid;
227
228 --instance_id and instance_number are same at this point of time.
229
230 /*------------------------------------------------------------*/
231 /* This function verifies that the item is a valid inventory */
232 /* item and is marked as 'Trackable' */
233 /*------------------------------------------------------------*/
234
235 FUNCTION Is_Trackable
236 (
237 p_inv_item_id IN NUMBER,
238 p_org_id IN NUMBER,
239 p_trackable_flag IN VARCHAR2,
240 p_stack_err_msg IN BOOLEAN
241 )
242 RETURN BOOLEAN IS
243
244 l_temp_string VARCHAR2(1);
245 l_return_value BOOLEAN := TRUE;
246 l_description VARCHAR2(240);
247
248
249 --changed for bug 6327810 to return description -somitra
250
251 BEGIN
252
253 SELECT NVL(comms_nl_trackable_flag,'N') ,NVL(description,' ')
254 INTO l_temp_string,l_description
255 FROM mtl_system_items
256 WHERE inventory_item_id = p_inv_item_id
257 AND organization_id = p_org_id
258 AND enabled_flag = 'Y'
259 AND nvl (start_date_active, sysdate) <= sysdate
260 AND nvl (end_date_active, sysdate+1) > sysdate;
261
262
263
264 If p_trackable_flag <> FND_API.G_MISS_CHAR then
265 if nvl(p_trackable_flag,'N') <> 'Y' then
266 l_return_value := FALSE;
267 IF (p_stack_err_msg = TRUE) THEN
268 FND_MESSAGE.SET_NAME('CSI','CSI_API_NOT_TRACKABLE');
269 FND_MESSAGE.SET_TOKEN('ITEM_DESCRIPTION',l_description) ;
270 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_inv_item_id);
271 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',p_org_id);
272
273 FND_MSG_PUB.Add;
274 END IF;
275 else
276 l_return_value := TRUE;
277 end if;
278 RETURN l_return_value;
279 End if;
280 --
281 -- Check for inventory item id and Org id values
282 IF ((p_inv_item_id IS NOT NULL) AND (p_inv_item_id <> FND_API.G_MISS_NUM)) AND
283 ((p_org_id IS NOT NULL) AND (p_org_id <> FND_API.G_MISS_NUM)) THEN
284
285
286 IF UPPER(l_temp_string) = 'Y' THEN
287 l_return_value := TRUE;
288 ELSE
289 l_return_value := FALSE;
290 IF (p_stack_err_msg = TRUE) THEN
291 FND_MESSAGE.SET_NAME('CSI','CSI_API_NOT_TRACKABLE');
292 FND_MESSAGE.SET_TOKEN('ITEM_DESCRIPTION',l_description) ;
293 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_inv_item_id);
294 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',p_org_id);
295 FND_MSG_PUB.Add;
296 END IF;
297 END IF;
298
299
300 ELSE
301 l_return_value := FALSE;
302 IF (p_stack_err_msg = TRUE) THEN
303 FND_MESSAGE.SET_NAME('CSI','CSI_API_NOT_TRACKABLE');
304 FND_MESSAGE.SET_TOKEN('ITEM_DESCRIPTION',l_description) ;
305 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_inv_item_id);
306 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',p_org_id);
307 FND_MSG_PUB.Add;
308 End IF;
309 END IF;
310
311
312 RETURN l_return_value;
313 END Is_Trackable;
314
315
316 /*----------------------------------------------------*/
317 /* This Procedure verifies validity of serial number */
318 /* ,lot number and revision when vld_organization_id */
319 /* is changing. */
320 /*----------------------------------------------------*/
321
322 PROCEDURE Validate_org_dependent_params
323 (
324 p_instance_rec IN OUT NOCOPY csi_datastructures_pub.instance_rec,
325 p_txn_rec IN csi_datastructures_pub.transaction_rec,
326 l_return_value IN OUT NOCOPY BOOLEAN
327 ) IS
328
329 BEGIN
330 IF p_instance_rec.serial_number <> fnd_api.g_miss_char
331 AND p_instance_rec.serial_number IS NOT NULL
332 THEN
333 csi_item_instance_vld_pvt.Validate_Serial_Number
334 (
335 p_inv_org_id => p_instance_rec.vld_organization_id,
336 p_inv_item_id => p_instance_rec.inventory_item_id ,
337 p_serial_number => p_instance_rec.serial_number,
338 p_mfg_serial_number_flag => p_instance_rec.mfg_serial_number_flag,
339 p_txn_rec => p_txn_rec,
340 p_creation_complete_flag => p_instance_rec.creation_complete_flag,
341 p_location_type_code => p_instance_rec.location_type_code,
342 p_instance_id => p_instance_rec.instance_id, -- Bug 2342885
343 p_instance_usage_code => p_instance_rec.instance_usage_code,
344 l_return_value => l_return_value
345 );
346 END IF;
347
348 IF p_instance_rec.lot_number <> fnd_api.g_miss_char
349 AND p_instance_rec.lot_number IS NOT NULL
350 AND l_return_value
351 THEN
352 csi_item_instance_vld_pvt.Validate_Lot_Number
353 (
354 p_inv_org_id => p_instance_rec.vld_organization_id,
355 p_inv_item_id => p_instance_rec.inventory_item_id ,
356 p_lot_number => p_instance_rec.lot_number,
357 p_mfg_serial_number_flag => p_instance_rec.mfg_serial_number_flag,
358 p_txn_rec => p_txn_rec,
359 p_creation_complete_flag => p_instance_rec.creation_complete_flag,
360 l_return_value => l_return_value
361 );
362 END IF;
363
364 IF p_instance_rec.inventory_revision <> fnd_api.g_miss_char
365 AND p_instance_rec.inventory_revision IS NOT NULL
366 AND l_return_value
367 THEN
368 csi_item_instance_vld_pvt.Validate_Revision
369 (
370 p_inv_item_id => p_instance_rec.inventory_item_id ,
371 p_inv_org_id => p_instance_rec.vld_organization_id,
372 p_creation_complete_flag => p_instance_rec.creation_complete_flag,
373 p_revision => p_instance_rec.inventory_revision,
374 l_return_value => l_return_value
375 );
376 END IF;
377
378 END Validate_org_dependent_params;
379
380
381
382 /*------------------------------------------------------------*/
383 /* This Procedure verifies that the item revision is valid */
384 /* by looking into the mtl revision table */
385 /*------------------------------------------------------------*/
386
387 PROCEDURE Validate_Revision
388 (
389 p_inv_item_id IN NUMBER,
390 p_inv_org_id IN NUMBER,
391 p_revision IN VARCHAR2,
392 p_creation_complete_flag IN OUT NOCOPY VARCHAR2,
393 l_return_value IN OUT NOCOPY BOOLEAN,
394 p_rev_control_code IN NUMBER
395 ) IS
396 l_dummy number; --varchar2(1);
397 l_stack_err_msg BOOLEAN DEFAULT TRUE;
398
399 CURSOR c1 is
400 SELECT revision_qty_control_code
401 FROM mtl_system_items
402 WHERE inventory_item_id = p_inv_item_id
403 AND organization_id = p_inv_org_id
404 AND enabled_flag = 'Y'
405 AND nvl (start_date_active, sysdate) <= sysdate
406 AND nvl (end_date_active, sysdate+1) > sysdate;
407
408 BEGIN
409 If p_rev_control_code <> FND_API.G_MISS_NUM Then
410 l_dummy := p_rev_control_code;
411 else
412 OPEN c1;
413 FETCH c1 into l_dummy;
414 CLOSE c1;
415 end if;
416 -- If Revision controlled
417 IF l_dummy is not null THEN
418 -- Item is under revision control but revision_number is NULL
419 -- '1' stands for - No revision control
420 -- '2' stands for - Full revision control
421 IF NVL(l_dummy,0) = 2 THEN
422 IF ((p_revision IS NULL) OR
423 (p_revision = FND_API.G_MISS_CHAR)) THEN
424 IF (p_creation_complete_flag = 'Y') THEN
425 l_return_value := FALSE;
426 IF ( l_stack_err_msg = TRUE ) THEN
427 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REVISION');
428 FND_MESSAGE.SET_TOKEN('INVENTORY_REVISION',p_revision);
429 FND_MSG_PUB.Add;
430 END IF;
431 ELSE
432 p_creation_complete_flag := 'N';
433 l_return_value := TRUE;
434 END IF;
435
436 ELSE
437 BEGIN
438 SELECT 1
439 INTO l_dummy
440 FROM mtl_item_revisions
441 WHERE inventory_item_id = p_inv_item_id
442 AND organization_id = p_inv_org_id
443 AND revision = p_revision;
444 l_return_value := TRUE;
445 EXCEPTION
446 WHEN NO_DATA_FOUND THEN
447 l_return_value := FALSE;
448 IF ( l_stack_err_msg = TRUE ) THEN
449 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REVISION');
450 FND_MESSAGE.SET_TOKEN('INVENTORY_REVISION',p_revision);
451 FND_MSG_PUB.Add;
452 END IF;
453 END;
454 END IF;
455 ELSE
456 -- Item is not under revision control but inventory_revision is not NULL
457 IF ((p_revision IS NOT NULL) AND (p_revision <> FND_API.G_MISS_CHAR)) THEN
458 BEGIN
459 SELECT 1
460 INTO l_dummy
461 FROM mtl_item_revisions
462 WHERE inventory_item_id = p_inv_item_id
463 AND organization_id = p_inv_org_id
464 AND revision = p_revision;
465 l_return_value := TRUE;
466 EXCEPTION
467 WHEN NO_DATA_FOUND THEN
468 l_return_value := FALSE;
469 IF ( l_stack_err_msg = TRUE ) THEN
470 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REVISION');
471 FND_MESSAGE.SET_TOKEN('INVENTORY_REVISION',p_revision);
472 FND_MSG_PUB.Add;
473 END IF;
474 END;
475 ELSE
476 l_return_value := TRUE;
477 END IF;
478
479 END IF;
480 ELSE
481 l_return_value := FALSE;
482 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ITEM'); -- Item does not exist in the inventory organization provided
483 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_inv_item_id);
484 FND_MESSAGE.SET_TOKEN('INVENTORY_ORGANIZATION_ID',p_inv_org_id);
485 FND_MSG_PUB.Add;
486 END IF;
487 END Validate_Revision;
488
489 /*------------------------------------------------------------*/
490 /* This Procedure verifies that the item revision is valid */
491 /* by looking into the mtl revision table */
492 /*------------------------------------------------------------*/
493
494 PROCEDURE Update_Revision
495 (
496 p_inv_item_id IN NUMBER,
497 p_inv_org_id IN NUMBER,
498 p_revision IN VARCHAR2,
499 l_return_value IN OUT NOCOPY BOOLEAN,
500 p_rev_control_code IN NUMBER
501 ) IS
502 l_dummy number; --varchar2(1);
503 l_stack_err_msg BOOLEAN DEFAULT TRUE;
504
505 CURSOR c1 is
506 SELECT revision_qty_control_code
507 FROM mtl_system_items
508 WHERE inventory_item_id = p_inv_item_id
509 AND organization_id = p_inv_org_id
510 AND enabled_flag = 'Y'
511 AND nvl (start_date_active, sysdate) <= sysdate
512 AND nvl (end_date_active, sysdate+1) > sysdate;
513
514 BEGIN
515 l_return_value := TRUE;
516 If p_rev_control_code <> FND_API.G_MISS_NUM Then
517 l_dummy := p_rev_control_code;
518 else
519 OPEN c1;
520 FETCH c1 into l_dummy;
521 CLOSE c1;
522 end if;
523 -- If Revision controlled
524 IF l_dummy is not null THEN
525 -- Item is under revision control but revision_number is NULL
526 -- '1' stands for - No revision control
527 -- '2' stands for - Full revision control
528 IF NVL(l_dummy,0) = 2 THEN
529 IF ((p_revision IS NULL) OR
530 (p_revision = FND_API.G_MISS_CHAR)) THEN
531 l_return_value := FALSE;
532 IF ( l_stack_err_msg = TRUE ) THEN
533 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REVISION');
534 FND_MESSAGE.SET_TOKEN('INVENTORY_REVISION',p_revision);
535 FND_MSG_PUB.Add;
536 END IF;
537 ELSE
538 BEGIN
539 SELECT 1
540 INTO l_dummy
541 FROM mtl_item_revisions
542 WHERE inventory_item_id = p_inv_item_id
543 AND organization_id = p_inv_org_id
544 AND revision = p_revision;
545 l_return_value := TRUE;
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 l_return_value := FALSE;
549 IF ( l_stack_err_msg = TRUE ) THEN
550 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REVISION');
551 FND_MESSAGE.SET_TOKEN('INVENTORY_REVISION',p_revision);
552 FND_MSG_PUB.Add;
553 END IF;
554 END;
555 END IF;
556 ELSE
557 -- Item is not under revision control but inventory_revision is not NULL
558 IF ((p_revision IS NOT NULL) AND (p_revision <> FND_API.G_MISS_CHAR)) THEN
559 BEGIN
560 SELECT 1
561 INTO l_dummy
562 FROM mtl_item_revisions
563 WHERE inventory_item_id = p_inv_item_id
564 AND organization_id = p_inv_org_id
565 AND revision = p_revision;
566 l_return_value := TRUE;
567 EXCEPTION
568 WHEN NO_DATA_FOUND THEN
569 l_return_value := FALSE;
570 IF ( l_stack_err_msg = TRUE ) THEN
571 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REVISION');
572 FND_MESSAGE.SET_TOKEN('INVENTORY_REVISION',p_revision);
573 FND_MSG_PUB.Add;
574 END IF;
575 END;
576 ELSE
577 l_return_value := TRUE;
578 END IF;
579 END IF;
580 ELSE
581 l_return_value := FALSE;
582 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ITEM'); -- Item does not exist in the inventory organization provided
583 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_inv_item_id);
584 FND_MESSAGE.SET_TOKEN('INVENTORY_ORGANIZATION_ID',p_inv_org_id);
585 FND_MSG_PUB.Add;
586 END IF;
587 END Update_Revision;
588
589 /*----------------------------------------------------*/
590 /* This function verifies that the item */
591 /* is under serial control or not */
592 /*----------------------------------------------------*/
593
594 FUNCTION Is_treated_serialized
595 ( p_serial_control_code IN NUMBER ,
596 p_location_type_code IN VARCHAR2,
597 p_transaction_type_id IN NUMBER -- Added parameter for bug 5374068
598 ) RETURN BOOLEAN IS
599 l_return_value BOOLEAN := FALSE;
600 BEGIN
601 -- Item is under serial control but serial_number is NULL
602 -- '1' stands for - No serial number control
603 -- '2' stands for - Predefined serial numbers
604 -- '5' stands for - Dynamic entry at inventory receipt
605 -- '6' stands for - Dynamic entry at sales order issue
606
607 IF p_serial_control_code IN (2,5)
608 THEN
609 l_return_value := TRUE;
610 ELSIF p_serial_control_code = 1
611 THEN
612 l_return_value := FALSE;
613 ELSIF p_serial_control_code = 6
614 THEN
615 IF (p_location_type_code IN ('INTERNAL_SITE','WIP','PROJECT','INVENTORY') )
616 THEN
617 -- Added the following code for bug 5374068
618 IF p_location_type_code='INTERNAL_SITE' AND
619 (p_transaction_type_id IS NOT NULL AND
620 p_transaction_type_id=130)
621 THEN
622 l_return_value := TRUE ;
623 ELSE
624 l_return_value := FALSE ;
625 END IF;
626 ELSE
627 l_return_value := TRUE ;
628 END IF;
629 END IF;
630 RETURN l_return_value;
631 END Is_treated_serialized;
632
633
634 /*----------------------------------------------------------------*/
635 /* This Procedure is specifically used for the IB instances that */
636 /* are created manually. When called this procedure creates a */
637 /* serial number in INVENTORY for manually created CP's */
638 /*----------------------------------------------------------------*/
639
640 PROCEDURE Create_Serial
641 (
642 p_inv_org_id IN NUMBER,
643 p_inv_item_id IN NUMBER,
644 p_serial_number IN VARCHAR2,
645 p_mfg_srl_num_flag IN OUT NOCOPY VARCHAR2,
646 p_location_type_code IN VARCHAR2,
647 p_ins_flag OUT NOCOPY VARCHAR2,
648 p_lot_number IN VARCHAR2,
649 p_gen_object_id OUT NOCOPY NUMBER,
650 l_return_value IN OUT NOCOPY BOOLEAN
651 ) IS
652
653 l_exists NUMBER;
654 l_return_status VARCHAR2(1);
655 l_msg_count NUMBER;
656 l_msg_data VARCHAR2(2000);
657 l_object_id NUMBER;
658 l_current_status NUMBER;
659 l_serial_type NUMBER;
660 l_temp VARCHAR2(1);
661 l_item_id NUMBER;
662 l_base_item_id NUMBER;
663 l_lot_number VARCHAR2(80);
664 l_cst_grp_id NUMBER;
665 --
666 CURSOR CTO_CUR(p_base_model IN NUMBER) IS
667 select distinct inventory_item_id
668 from MTL_SYSTEM_ITEMS_B
669 where base_item_id = p_base_model
670 and inventory_item_id <> p_inv_item_id;
671 --
672 process_next EXCEPTION;
673 comp_error EXCEPTION;
674
675 CURSOR ser_upd_csr (p_inv_id IN NUMBER
676 ,p_ser_number IN VARCHAR2) IS
677 SELECT *
678 FROM mtl_serial_numbers
679 WHERE inventory_item_id = p_inv_id
680 AND serial_number = p_ser_number;
681 l_ser_upd_csr ser_upd_csr%ROWTYPE;
682 l_status NUMBER;
683 BEGIN
684 --
685 -- Serial control codes:
686 -----------------------
687 -- No Serial control (status = 1)
688 -- Pre-defined (status = 2)
689 -- Serialized at SO Issue (status = 5)
690 -- Serialized at Receipt (status = 6)
691
692 -- serial statuses:
693 ------------------
694 -- Defined but not used (status = 1)
695 -- Resides in stores (status = 3)
696 -- Issued out of stores (status = 4)
697 -- Resides in Intransit (status = 5)
698
699 -- Serial uniqueness codes:
700 --------------------------
701 -- within inventory items (status = 1)
702 -- within organization (status = 2)
703 -- Across organizations (status = 3)
704 --
705 --
706 l_return_value := TRUE;
707 p_ins_flag := FND_API.G_FALSE;
708 --
709
710 IF nvl(p_lot_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
711 l_lot_number := NULL;
712 ELSE
713 l_lot_number := p_lot_number;
714 END IF;
715 --
716 BEGIN
717 SELECT serial_number_type, -- serial number uniqueness control
718 default_cost_group_id
719 INTO l_serial_type,
720 l_cst_grp_id
721 FROM mtl_parameters
722 WHERE organization_id = p_inv_org_id;
723 EXCEPTION
724 WHEN no_data_found THEN
725 l_return_value := FALSE;
726 fnd_message.set_name('CSI','CSI_NO_ORG_SET');
727 fnd_message.set_token('ORGANIZATION_ID',p_inv_org_id);
728 fnd_msg_pub.add;
729 RAISE comp_error;
730 END;
731 --
732 -- srramakr IB should just check for serial number existence in MSN and if it does not exist
733 -- the same should get created with current_status = 4 (out of stores). All other statuses should be
734 -- driven by Inventory.
735 --
736 /****** COMMENTED
737 IF ((p_location_type_code IS NOT NULL) AND (p_location_type_code <> FND_API.G_MISS_CHAR))
738 THEN
739 IF p_location_type_code = 'IN_TRANSIT'
740 THEN
741 l_current_status := 5;
742 ELSIF p_location_type_code = 'INVENTORY'
743 THEN
744 l_current_status := 3;
745 ELSE
746 l_current_status := 4;
747 END IF;
748 END IF;
749 ******* END OF COMMENT ******/
750 l_current_status := 4;
751 l_temp := NULL;
752 l_status := NULL;
753 --
754 IF l_serial_type IS NOT NULL
755 THEN
756 -- Check for the fundamental combination
757 BEGIN
758 SELECT 'x'
759 ,current_status
760 ,gen_object_id
761 INTO l_temp
762 ,l_status
763 ,p_gen_object_id
764 FROM mtl_serial_numbers
765 WHERE inventory_item_id = p_inv_item_id
766 AND serial_number = p_serial_number;
767 -- AND ROWNUM = 1; -- Commenting as inv_id and serial_number is unique in MSN
768 EXCEPTION
769 WHEN NO_DATA_FOUND THEN
770 l_temp := null;
771 l_status := null;
772 END;
773 --
774 IF l_status IS NOT NULL
775 THEN
776 l_exists := 0;
777 IF l_status = 1 --serial number defined but not used
778 THEN
779 -- If current status is 1 then check for transactions
780 select count(*)
781 into l_exists
782 from mtl_unit_transactions
783 where inventory_item_id = p_inv_item_id
784 and serial_number = p_serial_number
785 and ROWNUM = 1;
786 --
787 IF l_exists > 0 THEN
788 -- Since transactions were found so we will not update the current status
789 RAISE process_next;
790 ELSE
791 l_current_status := 4;
792 END IF;
793 ELSE
794 -- For all other current status values we will not update the status
795 RAISE process_next;
796 END IF;
797 --
798 IF l_exists = 0
799 THEN
800 -- Since transactions were not found so we will update the current status
801 OPEN ser_upd_csr (p_inv_id => p_inv_item_id
802 ,p_ser_number => p_serial_number);
803 FETCH ser_upd_csr INTO l_ser_upd_csr;
804 CLOSE ser_upd_csr;
805 csi_gen_utility_pvt.put_line('Calling INV API to update Serial Number...');
806 inv_serial_number_pub.updateserial(
807 p_api_version => 1.0
808 ,p_init_msg_list => fnd_api.g_false
809 ,p_commit => fnd_api.g_false
810 ,p_validation_level => fnd_api.g_valid_level_full
811 ,p_inventory_item_id => p_inv_item_id
812 ,p_organization_id => p_inv_org_id
813 ,p_serial_number => p_serial_number
814 ,p_initialization_date => l_ser_upd_csr.initialization_date
815 ,p_completion_date => l_ser_upd_csr.completion_date
816 ,p_ship_date => l_ser_upd_csr.ship_date
817 ,p_revision => l_ser_upd_csr.revision
818 ,p_lot_number => l_ser_upd_csr.lot_number
819 ,p_current_locator_id => l_ser_upd_csr.current_locator_id
820 ,p_subinventory_code => l_ser_upd_csr.current_subinventory_code
821 ,p_trx_src_id => l_ser_upd_csr.original_wip_entity_id
822 ,p_unit_vendor_id => l_ser_upd_csr.original_unit_vendor_id
823 ,p_vendor_lot_number => l_ser_upd_csr.vendor_lot_number
824 ,p_vendor_serial_number => l_ser_upd_csr.vendor_serial_number
825 ,p_receipt_issue_type => l_ser_upd_csr.last_receipt_issue_type
826 ,p_txn_src_id => l_ser_upd_csr.last_txn_source_id
827 ,p_txn_src_name => l_ser_upd_csr.last_txn_source_name
828 ,p_txn_src_type_id => l_ser_upd_csr.last_txn_source_type_id
829 ,p_current_status => l_current_status
830 ,p_parent_item_id => l_ser_upd_csr.parent_item_id
831 ,p_parent_serial_number => l_ser_upd_csr.parent_serial_number
832 ,p_serial_temp_id => NULL
833 ,p_last_status => l_status
834 ,p_status_id => NULL
835 ,x_object_id => l_object_id
836 ,x_return_status => l_return_status
837 ,x_msg_count => l_msg_count
838 ,x_msg_data => l_msg_data
839 ,p_organization_type => NULL
840 ,p_owning_org_id => NULL
841 ,p_owning_tp_type => NULL
842 ,p_planning_org_id => NULL
843 ,p_planning_tp_type => NULL
844 ,p_transaction_action_id => NULL
845 );
846 p_gen_object_id := l_object_id;
847 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
848 l_return_value := FALSE;
849 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_UPD_SERIAL');
850 FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from inv_serial_number_pub.updateserial');
851 FND_MSG_PUB.ADD;
852 RAISE comp_error;
853 ELSE
854 l_return_value := TRUE;
855 csi_gen_utility_pvt.put_line('Serial Number updated successfully in MTL_SERIAL_NUMBERS..');
856 RAISE comp_error;
857 END IF;
858 END IF;
859 ELSIF l_temp IS NULL -- Record is not found in MSN so we will validate and create a record in MSN
860 THEN
861 --
862 -- Case 1 unique serial number within Models and inventory items
863 l_temp := null;
864 IF l_serial_type = 1
865 THEN
866 l_base_item_id := NULL;
867 Begin
868 select base_item_id
869 into l_base_item_id
870 from MTL_SYSTEM_ITEMS_B
871 where inventory_item_id = p_inv_item_id
872 and organization_id = p_inv_org_id;
873 Exception
874 when others then
875 l_base_item_id := null;
876 End;
877 --
878 IF l_base_item_id IS NOT NULL THEN
879 l_temp := NULL;
880 FOR base_rec in CTO_CUR(l_base_item_id) LOOP
881 Begin
882 SELECT 'x'
883 INTO l_temp
884 FROM mtl_serial_numbers
885 WHERE inventory_item_id = base_rec.inventory_item_id
886 AND serial_number = p_serial_number;
887 -- AND ROWNUM = 1; -- Commenting as inv_id and serial_number is unique in MSN
888 exit;
889 Exception
890 WHEN NO_DATA_FOUND THEN
891 l_temp := null;
892 WHEN OTHERS THEN
893 l_return_value := TRUE;
894 End;
895 END LOOP;
896 -- If l_temp has a value then the uniqueness within Models and Inventory Items is violated.
897 -- Hence error out.
898 IF l_temp IS NOT NULL THEN
899 l_return_value := FALSE;
900 fnd_message.set_name('CSI','CSI_SER_CASE4');
901 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
902 fnd_msg_pub.add;
903 RAISE comp_error;
904 END IF;
905 END IF; -- base model exists
906 END IF; -- case 1
907 -- case4 unique serial number within inventory items
908 -- Since the fundamental uniqueness is checked upfront, no need to check for serial type 4
909 --
910 -- case2 unique serial number within particular organization
911 l_item_id := null;
912 IF l_serial_type = 2
913 THEN
914 BEGIN
915 SELECT inventory_item_id
916 INTO l_item_id
917 FROM mtl_serial_numbers
918 WHERE serial_number = p_serial_number
919 AND current_organization_id = p_inv_org_id;
920 --
921 EXCEPTION
922 WHEN NO_DATA_FOUND THEN
923 l_temp := null;
924 l_item_id := null;
925 WHEN TOO_MANY_ROWS THEN
926 l_temp := 'x';
927 l_return_value := FALSE;
928 fnd_message.set_name('CSI','CSI_SER_CASE2');
929 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
930 fnd_msg_pub.add;
931 RAISE comp_error;
932 END;
933 --
934 IF l_item_id IS NOT NULL THEN
935 l_temp := 'x';
936 IF l_item_id <> p_inv_item_id THEN
937 l_return_value := FALSE;
938 fnd_message.set_name('CSI','CSI_SER_CASE2');
939 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
940 fnd_msg_pub.add;
941 RAISE comp_error;
942 ELSE
943 RAISE Process_next;
944 END IF;
945 END IF;
946 -- Also check if it has been already defined as
947 -- unique serial number accross organizations i.e entire system
948 IF l_return_value
949 THEN
950 BEGIN
951 SELECT 'x'
952 INTO l_temp
953 FROM mtl_serial_numbers s,
954 mtl_parameters p
955 WHERE s.current_organization_id = p.organization_id
956 AND s.serial_number = p_serial_number
957 AND p.serial_number_type = 3
958 AND ROWNUM = 1;
959 EXCEPTION
960 WHEN NO_DATA_FOUND THEN
961 l_temp := null;
962 END;
963 --
964 IF l_temp IS NOT NULL THEN
965 l_return_value := FALSE;
966 fnd_message.set_name('CSI','CSI_SER_CASE3');
967 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
968 fnd_msg_pub.add;
969 RAISE comp_error;
970 END IF;
971 END IF; --l_return_value
972 END IF; -- l_serial_type = 2
973 -- case3 unique serial number accross organizations i.e entire system
974 IF l_serial_type = 3
975 THEN
976 BEGIN
977 SELECT inventory_item_id
978 INTO l_item_id
979 FROM mtl_serial_numbers
980 WHERE serial_number = p_serial_number;
981 EXCEPTION
982 WHEN NO_DATA_FOUND THEN
983 l_temp := null;
984 l_item_id := null;
985 WHEN TOO_MANY_ROWS THEN
986 l_temp := 'x';
987 l_return_value := FALSE;
988 fnd_message.set_name('CSI','CSI_SER_CASE3');
989 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
990 fnd_msg_pub.add;
991 RAISE comp_error;
992 END;
993 --
994 IF l_item_id IS NOT NULL THEN
995 l_temp := 'x';
996 IF l_item_id <> p_inv_item_id THEN
997 l_return_value := FALSE;
998 fnd_message.set_name('CSI','CSI_SER_CASE3');
999 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1000 fnd_msg_pub.add;
1001 RAISE comp_error;
1002 ELSE
1003 RAISE Process_next;
1004 END IF;
1005 END IF;
1006 END IF; -- l_serial_type = 3
1007 END IF;
1008 --
1009 -- If there is no corresonding serial# in INV,
1010 IF l_temp IS NULL
1011 THEN
1012 -- call the Inventory API to insert serial no. into the MSN table
1013 csi_gen_utility_pvt.put_line('Calling INV API to create Serial Number...');
1014 inv_serial_number_pub.insertSerial(
1015 p_api_version => 1.0,
1016 p_init_msg_list => fnd_api.g_false,
1017 p_commit => fnd_api.g_false,
1018 p_validation_level => fnd_api.g_valid_level_full,
1019 p_inventory_item_id => p_inv_item_id,
1020 p_organization_id => p_inv_org_id,
1021 p_serial_number => p_serial_number,
1022 p_initialization_date => SYSDATE,
1023 p_completion_date => SYSDATE, --NULL,
1024 p_ship_date => NULL,
1025 p_revision => NULL, --'A',
1026 p_lot_number => l_lot_number, --NULL,
1027 p_current_locator_id => NULL,
1028 p_subinventory_code => NULL,
1029 p_trx_src_id => NULL,
1030 p_unit_vendor_id => NULL,
1031 p_vendor_lot_number => NULL,
1032 p_vendor_serial_number => NULL,
1033 p_receipt_issue_type => NULL,
1034 p_txn_src_id => NULL,
1035 p_txn_src_name => NULL,
1036 p_txn_src_type_id => NULL,
1037 p_transaction_id => NULL,
1038 p_current_status => l_current_status,
1039 p_parent_item_id => NULL,
1040 p_parent_serial_number => NULL,
1041 p_cost_group_id => l_cst_grp_id,
1042 p_transaction_action_id => NULL,
1043 p_transaction_temp_id => NULL,
1044 p_status_id => NULL,
1045 x_object_id => l_object_id,
1046 x_return_status => l_return_status,
1047 x_msg_count => l_msg_count,
1048 x_msg_data => l_msg_data,
1049 p_organization_type => NULL,
1050 p_owning_org_id => NULL,
1051 p_owning_tp_type => NULL,
1052 p_planning_org_id => NULL,
1053 p_planning_tp_type => NULL,
1054 p_wip_entity_id => NULL,
1055 p_operation_seq_num => NULL,
1056 p_intraoperation_step_type => NULL );
1057
1058 p_gen_object_id := l_object_id;
1059 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1060 l_return_value := FALSE;
1061 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_INV_SERIAL');
1062 FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from inv_serial_number_pub.insertserial');
1063 FND_MSG_PUB.ADD;
1064 ELSE
1065 l_return_value := TRUE;
1066 p_ins_flag := FND_API.G_TRUE;
1067 csi_gen_utility_pvt.put_line('Serial Number created successfully in MTL_SERIAL_NUMBERS..');
1068 END IF;
1069 END IF; -- (l_temp is null)
1070 END IF;
1071
1072 EXCEPTION
1073 WHEN process_next THEN
1074 l_return_value := TRUE;
1075 WHEN comp_error THEN
1076 null;
1077 END Create_Serial;
1078 --
1079
1080 /*------------------------------------------------------------*/
1081 /* This procedure verifies that the item serial number is */
1082 /* valid by looking into the mtl serial #s table */
1083 /*------------------------------------------------------------*/
1084
1085 PROCEDURE Validate_Serial_Number
1086 (
1087 p_inv_org_id IN NUMBER,
1088 p_inv_item_id IN NUMBER,
1089 p_serial_number IN VARCHAR2,
1090 p_mfg_serial_number_flag IN VARCHAR2,
1091 p_txn_rec IN csi_datastructures_pub.transaction_rec,
1092 p_creation_complete_flag IN OUT NOCOPY VARCHAR2,
1093 p_location_type_code IN VARCHAR2, -- Added by sk on 09/13/01
1094 p_srl_control_code IN NUMBER,
1095 p_instance_id IN NUMBER, -- Bug # 2342885
1096 p_instance_usage_code IN VARCHAR2,
1097 l_return_value IN OUT NOCOPY BOOLEAN
1098 ) IS
1099 l_dummy varchar2(30);
1100 l_temp varchar2(30);
1101 p_stack_err_msg BOOLEAN DEFAULT TRUE;
1102
1103 -- If item is under serial control, then serial number MUST be a non-NULL
1104 -- value. If it is not under serial_control, then serial number MUST be
1105 -- NULL.
1106
1107 CURSOR C1 is
1108 SELECT serial_number_control_code
1109 FROM mtl_system_items
1110 WHERE inventory_item_id = p_inv_item_id
1111 AND organization_id = p_inv_org_id
1112 AND enabled_flag = 'Y'
1113 AND nvl (start_date_active, sysdate) <= sysdate
1114 AND nvl (end_date_active, sysdate+1) > sysdate;
1115
1116 Serialized NUMBER;
1117 l_found VARCHAR2(1);
1118 BEGIN
1119 l_return_value := TRUE;
1120 If p_srl_control_code <> FND_API.G_MISS_NUM then
1121 serialized := p_srl_control_code;
1122 else
1123 OPEN c1;
1124 FETCH c1 into serialized;
1125 CLOSE c1;
1126 end if;
1127 IF serialized is not null THEN
1128 -- Item is under serial control but serial_number is NULL
1129 -- '1' stands for - No serial number control
1130 -- '2' stands for - Predefined serial numbers
1131 -- '5' stands for - Dynamic entry at inventory receipt
1132 -- '6' stands for - Dynamic entry at sales order issue
1133 --IF NVL(serialized,0) IN (2,5,6) THEN
1134 IF Is_treated_serialized( p_serial_control_code => serialized
1135 ,p_location_type_code => p_location_type_code
1136 ,p_transaction_type_id => p_txn_rec.transaction_type_id
1137 )
1138 THEN
1139 IF ((p_serial_number IS NULL) OR
1140 (p_serial_number = FND_API.G_MISS_CHAR)) THEN
1141 IF (p_creation_complete_flag = 'Y') THEN
1142 l_return_value := FALSE;
1143 IF (p_stack_err_msg = TRUE) THEN
1144 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_SERIAL_NUM');
1145 FND_MESSAGE.SET_TOKEN('SERIAL_NUMBER',p_serial_number);
1146 FND_MSG_PUB.Add;
1147 END IF;
1148 ELSE
1149 p_creation_complete_flag := 'N';
1150 l_return_value := TRUE;
1151 END IF;
1152 ELSE
1153 l_return_value := TRUE; --added for bug 2143008
1154 END IF;
1155 ELSE
1156 -- Item is not under serial control but serial_number is not NULL
1157 -- IF NVL(serialized,0) NOT IN (2,5,6) THEN
1158 IF NOT Is_treated_serialized( p_serial_control_code => serialized
1159 ,p_location_type_code => p_location_type_code
1160 ,p_transaction_type_id => p_txn_rec.transaction_type_id
1161 )
1162 THEN
1163 IF ((p_serial_number IS NOT NULL) AND (p_serial_number <> FND_API.G_MISS_CHAR))
1164 THEN
1165 l_found := NULL;
1166 IF serialized IS NOT NULL
1167 AND serialized=6
1168 AND p_instance_usage_code='RETURNED'
1169 AND p_location_type_code='INVENTORY'
1170 THEN
1171 BEGIN
1172 SELECT 'x'
1173 INTO l_found
1174 FROM mtl_serial_numbers
1175 WHERE inventory_item_id = p_inv_item_id
1176 AND serial_number = p_serial_number;
1177 l_return_value := TRUE;
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 NULL;
1181 END;
1182 END IF;
1183 IF l_found IS NULL
1184 THEN
1185 l_return_value := FALSE;
1186 FND_MESSAGE.SET_NAME('CSI','CSI_API_NOT_SER_CONTROLLED');
1187 FND_MESSAGE.SET_TOKEN('SERIAL_NUMBER',p_serial_number);
1188 FND_MSG_PUB.Add;
1189 END IF;
1190 ELSE
1191 l_return_value := TRUE;
1192 END IF;
1193 END IF;
1194 END IF;
1195 ELSE
1196 l_return_value := FALSE;
1197 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ITEM'); -- Item does not exist in the inventory organization provided
1198 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_inv_item_id);
1199 FND_MESSAGE.SET_TOKEN('INVENTORY_ORGANIZATION_ID',p_inv_org_id);
1200 FND_MSG_PUB.Add;
1201 END IF;
1202
1203 IF l_return_value = TRUE AND
1204 p_serial_number IS NOT NULL AND
1205 p_serial_number <> fnd_api.g_miss_char
1206 THEN
1207 Validate_ser_uniqueness
1208 ( p_inv_org_id => p_inv_org_id
1209 ,p_inv_item_id => p_inv_item_id
1210 ,p_serial_number => p_serial_number
1211 ,l_return_value => l_return_value
1212 ,p_instance_id => p_instance_id
1213 );
1214 IF l_return_value = FALSE THEN
1215 fnd_message.set_name('CSI','CSI_FAIL_UNIQUENESS');
1216 fnd_msg_pub.add;
1217 END IF;
1218 END IF;
1219
1220 END Validate_Serial_Number;
1221
1222
1223 /*----------------------------------------------------*/
1224 /* This procedure verifies that the serial number */
1225 /* uniqueness */
1226 /*----------------------------------------------------*/
1227 PROCEDURE Validate_ser_uniqueness
1228 (
1229 p_inv_org_id IN NUMBER,
1230 p_inv_item_id IN NUMBER,
1231 p_serial_number IN VARCHAR2,
1232 p_instance_id IN NUMBER, -- Bug # 2342885
1233 l_return_value IN OUT NOCOPY BOOLEAN
1234 ) IS
1235 l_serial_type NUMBER;
1236 l_temp VARCHAR2(1);
1237 l_instance_id NUMBER;
1238 l_base_item_id NUMBER;
1239 l_item_id NUMBER;
1240 --
1241 CURSOR CTO_CUR(p_base_model IN NUMBER) IS
1242 select distinct inventory_item_id
1243 from MTL_SYSTEM_ITEMS_B
1244 where base_item_id = p_base_model
1245 and inventory_item_id <> p_inv_item_id;
1246 --
1247 comp_error EXCEPTION;
1248 BEGIN
1249 -- srramakr Bug # 2342885. p_instance_id will be passed only from Validate_Org_Dependent_params
1250 -- API. This is basically to validate the serial number when the vld_organization_id changes.
1251 -- In this, the uniqueness check should ignore the current instance.
1252 --
1253 IF p_instance_id IS NULL OR
1254 p_instance_id = FND_API.G_MISS_NUM THEN
1255 l_instance_id := -99999;
1256 ELSE
1257 l_instance_id := p_instance_id;
1258 END IF;
1259 --
1260 l_return_value := TRUE;
1261 BEGIN
1262 SELECT serial_number_type -- serial number uniqueness control
1263 INTO l_serial_type
1264 FROM mtl_parameters
1265 WHERE organization_id = p_inv_org_id;
1266 EXCEPTION
1267 WHEN no_data_found THEN
1268 l_return_value := FALSE;
1269 fnd_message.set_name('CSI','CSI_NO_ORG_SET');
1270 fnd_message.set_token('ORGANIZATION_ID',p_inv_org_id);
1271 fnd_msg_pub.add;
1272 RAISE comp_error;
1273 END;
1274 --
1275 IF l_serial_type IS NOT NULL
1276 THEN
1277 -- Check for fundamental uniqueness
1278 BEGIN
1279 SELECT 'x'
1280 INTO l_temp
1281 FROM csi_item_instances
1282 WHERE serial_number = p_serial_number
1283 AND inventory_item_id = p_inv_item_id
1284 AND instance_id <> l_instance_id
1285 AND ROWNUM = 1;
1286 EXCEPTION
1287 WHEN NO_DATA_FOUND THEN
1288 l_temp := null;
1289 END;
1290 IF l_temp IS NOT NULL THEN
1291 l_return_value := FALSE;
1292 fnd_message.set_name('CSI','CSI_SER_CASE1');
1293 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1294 fnd_msg_pub.add;
1295 RAISE comp_error;
1296 END IF;
1297 --
1298 -- case1 Unique Serial Number within Models and Inventory Items
1299 IF l_serial_type = 1
1300 THEN
1301 select base_item_id
1302 into l_base_item_id
1303 from MTL_SYSTEM_ITEMS_B
1304 where inventory_item_id = p_inv_item_id
1305 and organization_id = p_inv_org_id;
1306 --
1307 IF l_base_item_id IS NOT NULL THEN
1308 l_temp := null;
1309 FOR base_rec in CTO_CUR(l_base_item_id) LOOP
1310 Begin
1311 select 'x'
1312 into l_temp
1313 from CSI_ITEM_INSTANCES
1314 where serial_number = p_serial_number
1315 and inventory_item_id = base_rec.inventory_item_id
1316 and rownum = 1;
1317 exit;
1318 Exception
1319 when no_data_found then
1320 l_temp := null;
1321 End;
1322 END LOOP;
1323 --
1324 IF l_temp IS NOT NULL THEN
1325 l_return_value := FALSE;
1326 fnd_message.set_name('CSI','CSI_SER_CASE4');
1327 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1328 fnd_msg_pub.add;
1329 RAISE comp_error;
1330 END IF;
1331 END IF; -- Base Model check
1332 END IF; -- Case1
1333 -- case4 unique serial number with in inventory items
1334 -- No need to check for serial type 4 as the fundamental uniqueness is checked upfront
1335 --
1336 -- case2 unique serial number with in particular organization
1337 l_item_id := null;
1338 IF l_serial_type = 2
1339 THEN
1340 BEGIN
1341 SELECT 'x'
1342 INTO l_temp
1343 FROM csi_item_instances
1344 WHERE serial_number = p_serial_number
1345 AND last_vld_organization_id = p_inv_org_id
1346 AND instance_id <> l_instance_id
1347 AND ROWNUM = 1;
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 l_return_value := TRUE;
1351 END;
1352 --
1353 IF l_temp IS NOT NULL THEN
1354 l_return_value := FALSE;
1355 fnd_message.set_name('CSI','CSI_SER_CASE2');
1356 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1357 fnd_msg_pub.add;
1358 RAISE comp_error;
1359 END IF;
1360 --
1361 IF l_return_value THEN
1362 BEGIN
1363 SELECT inventory_item_id
1364 INTO l_item_id
1365 FROM mtl_serial_numbers
1366 WHERE serial_number = p_serial_number
1367 AND current_organization_id = p_inv_org_id;
1368 --
1369 EXCEPTION
1370 WHEN NO_DATA_FOUND THEN
1371 l_item_id := null;
1372 WHEN TOO_MANY_ROWS THEN
1373 l_return_value := FALSE;
1374 fnd_message.set_name('CSI','CSI_SER_CASE2');
1375 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1376 fnd_msg_pub.add;
1377 RAISE comp_error;
1378 END;
1379 --
1380 IF l_item_id IS NOT NULL THEN
1381 IF l_item_id <> p_inv_item_id THEN
1382 l_return_value := FALSE;
1383 fnd_message.set_name('CSI','CSI_SER_CASE2');
1384 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1385 fnd_msg_pub.add;
1386 RAISE comp_error;
1387 END IF;
1388 END IF;
1389 END IF;
1390 --
1391 -- Also check if it has been already defined as
1392 -- unique serial number accross organizations i.e entire system
1393 IF l_return_value
1394 THEN
1395 BEGIN
1396 SELECT 'x'
1397 INTO l_temp
1398 FROM mtl_serial_numbers s,
1399 mtl_parameters p
1400 WHERE s.current_organization_id = p.organization_id
1401 AND s.serial_number = p_serial_number
1402 AND p.serial_number_type = 3
1403 AND ROWNUM = 1;
1404 EXCEPTION
1405 WHEN NO_DATA_FOUND THEN
1406 l_temp := null;
1407 END;
1408 --
1409 IF l_temp IS NOT NULL THEN
1410 l_return_value := FALSE;
1411 fnd_message.set_name('CSI','CSI_SER_CASE3');
1412 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1413 fnd_msg_pub.add;
1414 RAISE comp_error;
1415 END IF;
1416 END IF; --l_return_value
1417 END IF; -- l_serial_type = 2
1418 --
1419 -- case3 unique serial number accross organizations i.e entire system
1420 IF l_serial_type = 3
1421 THEN
1422 BEGIN
1423 SELECT 'x'
1424 INTO l_temp
1425 FROM csi_item_instances
1426 WHERE serial_number = p_serial_number
1427 AND instance_id <> l_instance_id
1428 AND ROWNUM=1;
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 l_return_value := TRUE;
1432 END;
1433 --
1434 IF l_temp IS NOT NULL THEN
1435 l_return_value := FALSE;
1436 fnd_message.set_name('CSI','CSI_SER_CASE3');
1437 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1438 fnd_msg_pub.add;
1439 RAISE comp_error;
1440 END IF;
1441 --
1442 IF l_return_value THEN
1443 BEGIN
1444 SELECT inventory_item_id
1445 INTO l_item_id
1446 FROM mtl_serial_numbers
1447 WHERE serial_number = p_serial_number;
1448 EXCEPTION
1449 WHEN NO_DATA_FOUND THEN
1450 l_item_id := null;
1451 WHEN TOO_MANY_ROWS THEN
1452 l_return_value := FALSE;
1453 fnd_message.set_name('CSI','CSI_SER_CASE3');
1454 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1455 fnd_msg_pub.add;
1456 RAISE comp_error;
1457 END;
1458 --
1459 IF l_item_id IS NOT NULL THEN
1460 IF l_item_id <> p_inv_item_id THEN
1461 l_return_value := FALSE;
1462 fnd_message.set_name('CSI','CSI_SER_CASE3');
1463 fnd_message.set_token('SERIAL_NUMBER',p_serial_number);
1464 fnd_msg_pub.add;
1465 RAISE comp_error;
1466 END IF;
1467 END IF;
1468 END IF;
1469 END IF;
1470 END IF;
1471 EXCEPTION
1472 when comp_error then
1473 l_return_value := FALSE;
1474 END Validate_ser_uniqueness;
1475
1476 /*----------------------------------------------------------------*/
1477 /* This Procedure is used to check the lot uniqueness in IB and */
1478 /* INV. Also this procedure is used to create lot numbers in */
1479 /* Inventory for manually created IB instances which are lot */
1480 /* controlled. */
1481 /*----------------------------------------------------------------*/
1482
1483 PROCEDURE Create_Lot
1484 (
1485 p_inv_org_id IN NUMBER,
1486 p_inv_item_id IN NUMBER,
1487 p_lot_number IN VARCHAR2,
1488 p_shelf_life_code IN NUMBER,
1489 p_instance_id IN NUMBER,
1490 l_return_value IN OUT NOCOPY BOOLEAN
1491 )IS
1492 --
1493 l_lot_type NUMBER;
1494 l_temp VARCHAR2(1);
1495 l_instance_id NUMBER;
1496 l_return_status VARCHAR2(1);
1497 l_msg_count NUMBER;
1498 l_msg_data VARCHAR2(2000);
1499 l_object_id NUMBER;
1500 l_expiration_date DATE := null;
1501 --
1502 comp_error EXCEPTION;
1503 --
1504 BEGIN
1505 --
1506 IF ((p_instance_id IS NULL) OR
1507 (p_instance_id = FND_API.G_MISS_NUM)) THEN
1508 l_instance_id := -99999;
1509 ELSE
1510 l_instance_id := p_instance_id;
1511 END IF;
1512 --
1513 l_return_value := TRUE;
1514 --
1515 -- Get the lot uniqueness type for the transacting organization
1516 BEGIN
1517 SELECT lot_number_uniqueness -- lot number uniqueness control
1518 INTO l_lot_type
1519 FROM mtl_parameters
1520 WHERE organization_id = p_inv_org_id;
1521 EXCEPTION
1522 WHEN NO_DATA_FOUND THEN
1523 l_return_value := FALSE;
1524 fnd_message.set_name('CSI','CSI_NO_LOT_ORG_SET');
1525 fnd_message.set_token('ORGANIZATION_ID',p_inv_org_id);
1526 fnd_msg_pub.add;
1527 RAISE comp_error;
1528 END;
1529 --
1530 -- Lot Number uniqueness
1531 ------------------------
1532 -- 1 - Across Items
1533 -- 2 - None
1534 --
1535 l_temp := NULL; -- 1st initial..
1536 --
1537 IF l_lot_type = 1
1538 THEN
1539 -- Call the validate_lot_unique routine to check the uniqueness in Inventory
1540 IF NOT Inv_Lot_Api_PUB.validate_unique_lot
1541 ( p_org_id => p_inv_org_id,
1542 p_inventory_item_id => p_inv_item_id,
1543 p_lot_uniqueness => l_lot_type,
1544 p_auto_lot_number => p_lot_number )
1545 THEN
1546 fnd_message.set_name('CSI','CSI_LOT_CASE1');
1547 fnd_message.set_token('LOT_NUMBER',p_lot_number);
1548 fnd_msg_pub.add;
1549 RAISE comp_error;
1550 ELSE
1551 -- Check for the fundamental uniqueness in Install Base
1552 BEGIN
1553 SELECT 'x'
1554 INTO l_temp
1555 FROM CSI_ITEM_INSTANCES
1556 WHERE inventory_item_id <> p_inv_item_id
1557 AND lot_number = p_lot_number
1558 AND instance_id <> p_instance_id;
1559 EXCEPTION
1560 WHEN NO_DATA_FOUND THEN
1561 l_temp := NULL;
1562 WHEN TOO_MANY_ROWS THEN
1563 l_temp := 'x';
1564 END;
1565 --
1566 IF l_temp IS NOT NULL
1567 THEN
1568 fnd_message.set_name('CSI','CSI_LOT_CASE2');
1569 fnd_message.set_token('LOT_NUMBER',p_lot_number);
1570 fnd_msg_pub.add;
1571 RAISE comp_error;
1572 END IF;
1573 END IF; -- validate_unique_lot
1574 END IF; -- l_lot_type = 1
1575 -- Check for the existance of lot number in Inventory for the current item
1576 BEGIN
1577 SELECT 'x'
1578 INTO l_temp
1579 FROM MTL_LOT_NUMBERS
1580 WHERE inventory_item_id = p_inv_item_id
1581 AND organization_id = p_inv_org_id
1582 AND lot_number = p_lot_number;
1583 EXCEPTION
1584 WHEN NO_DATA_FOUND THEN
1585 l_temp := NULL;
1586 WHEN TOO_MANY_ROWS THEN
1587 l_temp := 'x';
1588 END;
1589 --
1590 --
1591 IF l_temp IS NULL
1592 THEN
1593 -- For user defined shelf-life we default the lot expiration as 50 Years from creation date.
1594 -- This can be updated from the Inventory -> Onhand -> Lots UI.
1595 --
1596 IF p_shelf_life_code = 4 THEN
1597 l_expiration_date := sysdate + 18250;
1598 END IF;
1599 --
1600 -- Call the Inventory API to insert the lot number
1601 inv_lot_api_pub.insertlot (
1602 p_api_version => 1.0,
1603 p_init_msg_list => fnd_api.g_false,
1604 p_commit => fnd_api.g_false,
1605 p_validation_level => fnd_api.g_valid_level_full,
1606 p_inventory_item_id => p_inv_item_id,
1607 p_organization_id => p_inv_org_id,
1608 p_lot_number => p_lot_number,
1609 p_expiration_date => l_expiration_date,
1610 p_transaction_temp_id => NULL,
1611 p_transaction_action_id => NULL,
1612 p_transfer_organization_id => NULL,
1613 x_object_id => l_object_id,
1614 x_return_status => l_return_status,
1615 x_msg_count => l_msg_count,
1616 x_msg_data => l_msg_data);
1617
1618 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1619 FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_INV_LOT_NUM');
1620 FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from inv_lot_api_pub.InsertLot Procedure');
1621 FND_MSG_PUB.ADD;
1622 RAISE comp_error;
1623 ELSE
1624 l_return_value := TRUE;
1625 csi_gen_utility_pvt.put_line('Lot Number created successfully in MTL_LOT_NUMBERS..');
1626 END IF;
1627 END IF; -- l_temp is null
1628 --
1629 EXCEPTION
1630 WHEN comp_error THEN
1631 l_return_value := FALSE;
1632 END Create_Lot;
1633
1634 /*------------------------------------------------------------*/
1635 /* This procedure verifies that the item lot number is */
1636 /* valid */
1637 /*------------------------------------------------------------*/
1638
1639 PROCEDURE Validate_Lot_Number
1640 (
1641 p_inv_org_id IN NUMBER,
1642 p_inv_item_id IN NUMBER,
1643 p_lot_number IN VARCHAR2,
1644 p_mfg_serial_number_flag IN VARCHAR2,
1645 p_txn_rec IN csi_datastructures_pub.transaction_rec,
1646 p_creation_complete_flag IN OUT NOCOPY VARCHAR2,
1647 p_lot_control_code IN NUMBER,
1648 l_return_value IN OUT NOCOPY BOOLEAN
1649 ) IS
1650 --
1651 l_dummy number; -- varchar2(1);
1652 l_stack_err_msg BOOLEAN DEFAULT TRUE;
1653 --
1654 CURSOR c1 is
1655 SELECT lot_control_code
1656 FROM mtl_system_items
1657 WHERE inventory_item_id = p_inv_item_id
1658 AND organization_id = p_inv_org_id
1659 AND enabled_flag = 'Y'
1660 AND nvl (start_date_active, sysdate) <= sysdate
1661 AND nvl (end_date_active, sysdate+1) > sysdate;
1662
1663 BEGIN
1664 IF p_lot_control_code <> FND_API.G_MISS_NUM
1665 THEN
1666 l_dummy := p_lot_control_code;
1667 ELSE
1668 OPEN c1;
1669 FETCH c1 INTO l_dummy;
1670 CLOSE c1;
1671 END IF;
1672 --
1673 IF l_dummy IS NOT NULL
1674 THEN
1675 -- Lot Control Code
1676 -- '1' stands for - No lot control
1677 -- '2' stands for - Full lot control
1678 IF l_dummy = 2 -- Lot Controlled
1679 THEN
1680 --
1681 IF ((p_lot_number IS NULL) OR
1682 (p_lot_number = FND_API.G_MISS_CHAR))
1683 THEN
1684 IF (p_creation_complete_flag = 'Y')
1685 THEN
1686 l_return_value := FALSE;
1687 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOT_NUM');
1688 FND_MESSAGE.SET_TOKEN('LOT_NUMBER',p_lot_number);
1689 FND_MSG_PUB.Add;
1690 ELSE
1691 p_creation_complete_flag := 'N';
1692 l_return_value := TRUE;
1693 END IF;
1694 ELSE
1695 l_return_value := TRUE;
1696 END IF;
1697 --
1698 ELSE -- Item is not under lot control but lot_number is NOT NULL
1699 --
1700 IF ((p_lot_number IS NOT NULL) AND
1701 (p_lot_number <> FND_API.G_MISS_CHAR))
1702 THEN
1703 l_return_value := FALSE;
1704 FND_MESSAGE.SET_NAME('CSI','CSI_API_NOT_LOT_CONTROLLED');
1705 FND_MESSAGE.SET_TOKEN('LOT_NUMBER',p_lot_number);
1706 FND_MSG_PUB.Add;
1707 ELSE
1708 l_return_value := TRUE;
1709 END IF;
1710 --
1711 END IF;
1712 --
1713 ELSE
1714 l_return_value := FALSE;
1715 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ITEM'); -- Item does not exist in the inventory organization provided
1716 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_inv_item_id);
1717 FND_MESSAGE.SET_TOKEN('INVENTORY_ORGANIZATION_ID',p_inv_org_id);
1718 FND_MSG_PUB.Add;
1719 END IF;
1720 --
1721 END Validate_Lot_Number;
1722 --
1723 /*------------------------------------------------------------*/
1724 /* This function validates the quantity and also check for */
1725 /* serialized items, quantity =1 */
1726 /*------------------------------------------------------------*/
1727
1728 FUNCTION Is_Quantity_Valid
1729 ( p_instance_id IN NUMBER ,
1730 p_inv_organization_id IN NUMBER ,
1731 p_quantity IN NUMBER ,
1732 p_serial_control_code IN NUMBER ,
1733 p_location_type_code IN VARCHAR2,
1734 p_flag IN VARCHAR2,
1735 p_csi_txn_type_id IN NUMBER,
1736 p_current_qty IN NUMBER,
1737 p_stack_err_msg IN BOOLEAN
1738 )
1739 RETURN BOOLEAN IS
1740
1741 l_quantity NUMBER;
1742 l_dummy NUMBER;
1743 l_override_neg_for_backflush NUMBER;
1744 l_return_value BOOLEAN := TRUE;
1745 l_drive_qty NUMBER := 0;
1746
1747 Cursor c1 is
1748 SELECT negative_inv_receipt_code
1749 FROM mtl_parameters
1750 WHERE organization_id = p_inv_organization_id;
1751
1752 BEGIN
1753
1754 -- IF ((p_serial_number IS NOT NULL) AND (p_serial_number <> FND_API.G_MISS_CHAR)) THEN
1755 IF (csi_Item_Instance_Vld_pvt.Is_treated_serialized
1756 ( p_serial_control_code => p_serial_control_code
1757 ,p_location_type_code => p_location_type_code
1758 ,p_transaction_type_id => p_csi_txn_type_id
1759 ))
1760 THEN
1761 IF p_quantity <> 1 THEN
1762 l_return_value := FALSE;
1763 IF (p_stack_err_msg = TRUE) THEN
1764 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_QUANTITY');
1765 FND_MESSAGE.SET_TOKEN('QUANTITY',p_quantity);
1766 FND_MSG_PUB.Add;
1767 END IF;
1768 END IF;
1769 ELSE
1770 IF p_quantity < 0 THEN
1771 OPEN C1;
1772 FETCH C1 INTO l_dummy;
1773 IF C1%found THEN
1774 IF nvl(l_dummy,0) = 1 THEN
1775 l_return_value := TRUE;
1776 ELSE
1777 -- srramakr Bug # 4137476. Even if Allow (-)ve balance is set to No at org level
1778 -- if the following profile is set to Yes then WIP backflushes are allowed.
1779 -- This is true for both Operation Pull and Assembly Pull.
1780 -- Since they are registered as WIP issues, we use CSI txn Type 71 to identify them.
1781 -- Profile returns 1 for Yes; 2 for No
1782 -- We also need to find where the qty is driven towards. This is because after backflush
1783 -- an INV instance gets created with a (-)ve qty. If a misc receipt or any other receipt
1784 -- tries to update this instance, the txn type will not be 71. This will result in an error.
1785 -- So, we need to find whether the qty is driven towards (+)ve or (-)ve side.
1786 --
1787 l_drive_qty := p_quantity - nvl(p_current_qty,0);
1788 IF l_drive_qty < 0 THEN -- Qty is driven (-)ve
1789 IF nvl(p_csi_txn_type_id,-999) = 71 THEN
1790 l_override_neg_for_backflush := FND_PROFILE.VALUE('INV_OVERRIDE_NEG_FOR_BACKFLUSH');
1791 IF nvl(l_override_neg_for_backflush,2) = 1 THEN
1792 l_return_value := TRUE;
1793 ELSE
1794 l_return_value := FALSE;
1795 IF (p_stack_err_msg = TRUE) THEN
1796 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_NEGATIVE_QTY');
1797 FND_MESSAGE.SET_TOKEN('QUANTITY',p_quantity);
1798 FND_MSG_PUB.Add;
1799 END IF;
1800 END IF;
1801 ELSE -- Non-WIP Issue transactions
1802 l_return_value := FALSE;
1803 IF (p_stack_err_msg = TRUE) THEN
1804 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_NEGATIVE_QTY');
1805 FND_MESSAGE.SET_TOKEN('QUANTITY',p_quantity);
1806 FND_MSG_PUB.Add;
1807 END IF;
1808 END IF;
1809 ELSE -- Qty is driven (+)ve
1810 l_return_value := TRUE;
1811 END IF; -- l_drive_qty check
1812 END IF;
1813 END IF;
1814 CLOSE C1;
1815 ELSIF p_quantity > 1 AND
1816 (p_instance_id IS NOT NULL AND
1817 p_instance_id <> fnd_api.g_miss_num)
1818 THEN
1819 BEGIN
1820 SELECT subject_id
1821 INTO l_dummy
1822 FROM csi_ii_relationships
1823 WHERE object_id = p_instance_id
1824 AND nvl(active_end_date,(sysdate+1)) > sysdate -- rajeevk Bug#5686753
1825 and rownum < 2; -- srramakr Bug # 3647609
1826 --
1827 IF SQL%FOUND THEN
1828 l_return_value := FALSE;
1829 IF (p_stack_err_msg = TRUE) THEN
1830 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REL_QTY');
1831 FND_MESSAGE.SET_TOKEN('QUANTITY',p_quantity);
1832 FND_MSG_PUB.Add;
1833 END IF;
1834 END IF;
1835 EXCEPTION
1836 WHEN NO_DATA_FOUND THEN
1837 l_return_value := TRUE;
1838 END;
1839 ELSIF p_quantity = 0 AND
1840 p_flag ='CREATE'
1841 THEN
1842 l_return_value := FALSE;
1843 IF (p_stack_err_msg = TRUE) THEN
1844 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ZERO_QTY');
1845 FND_MESSAGE.SET_TOKEN('QUANTITY',p_quantity);
1846 FND_MSG_PUB.Add;
1847 END IF;
1848 END IF;
1849 END IF;
1850 RETURN l_return_value;
1851 END Is_Quantity_Valid;
1852
1853 -- Added by sguthiva for att enhancements
1854
1855 /*------------------------------------------------------------*/
1856 /* This function validates the uniqueness of config key */
1857 /*------------------------------------------------------------*/
1858 FUNCTION Is_unique_config_key
1859 ( p_config_inst_hdr_id IN NUMBER ,
1860 p_config_inst_item_id IN NUMBER ,
1861 p_instance_id IN NUMBER ,
1862 p_validation_mode IN VARCHAR2
1863 )
1864 RETURN BOOLEAN IS
1865
1866 l_config_found VARCHAR2(1);
1867 l_return_value BOOLEAN := TRUE;
1868
1869 BEGIN
1870 /*
1871 IF p_validation_mode='CREATE'
1872 THEN
1873 l_config_found:=NULL;
1874 BEGIN
1875 SELECT 'x'
1876 INTO l_config_found
1877 FROM csi_item_instances
1878 WHERE config_inst_hdr_id = p_config_inst_hdr_id
1879 AND config_inst_item_id = p_config_inst_item_id
1880 AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
1881
1882 l_return_value := FALSE;
1883
1884 EXCEPTION
1885 WHEN NO_DATA_FOUND THEN
1886 NULL;
1887 WHEN OTHERS THEN
1888 l_return_value := FALSE;
1889 END;
1890 ELSIF p_validation_mode='UPDATE'
1891 THEN
1892 */
1893 l_config_found:=NULL;
1894 BEGIN
1895 SELECT 'x'
1896 INTO l_config_found
1897 FROM csi_item_instances
1898 WHERE config_inst_hdr_id = p_config_inst_hdr_id
1899 AND config_inst_item_id = p_config_inst_item_id
1900 AND instance_id <> p_instance_id
1901 AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
1902
1903 l_return_value := FALSE;
1904
1905 EXCEPTION
1906 WHEN NO_DATA_FOUND THEN
1907 NULL;
1908 WHEN OTHERS THEN
1909 l_return_value := FALSE;
1910 END;
1911 -- END IF;
1912 RETURN l_return_value;
1913 END Is_unique_config_key;
1914
1915 -- End addition by sguthiva for att enhancements
1916
1917
1918 /*-------------------------------------------------*/
1919 /* This function verifies that the UOM code is */
1920 /* valid by looking into the mtl table */
1921 /*-------------------------------------------------*/
1922
1923 PROCEDURE Is_Valid_Uom
1924 (
1925 p_inv_org_id IN NUMBER,
1926 p_inv_item_id IN NUMBER,
1927 p_uom_code IN OUT NOCOPY VARCHAR2,
1928 p_quantity IN OUT NOCOPY NUMBER,
1929 p_creation_complete_flag IN OUT NOCOPY VARCHAR2,
1930 l_return_value IN OUT NOCOPY BOOLEAN)
1931 IS
1932 l_quantity NUMBER;
1933 to_unit VARCHAR2(3);
1934 BEGIN
1935 -- check whether the uom class exists for the unit of measure code passed
1936 IF ((p_uom_code IS NULL) OR
1937 (p_uom_code = FND_API.G_MISS_CHAR)) THEN
1938 IF (p_creation_complete_flag = 'Y') THEN
1939 l_return_value := FALSE;
1940 ELSE
1941 p_creation_complete_flag := 'N';
1942 l_return_value := TRUE;
1943 END IF;
1944 ELSE
1945
1946 IF (inv_convert.validate_item_uom
1947 (p_uom_code => p_uom_code ,
1948 p_item_id => p_inv_item_id ,
1949 p_organization_id => p_inv_org_id) ) THEN
1950
1951 -- check for the existance of primary uom code in mtl_system_items for the unit of measure code passed
1952 BEGIN
1953 SELECT primary_uom_code
1954 INTO to_unit
1955 FROM mtl_system_items
1956 WHERE inventory_item_id = p_inv_item_id
1957 AND organization_id = p_inv_org_id
1958 AND enabled_flag = 'Y'
1959 AND nvl (start_date_active, sysdate) <= sysdate
1960 AND nvl (end_date_active, sysdate+1) > sysdate;
1961
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 FND_MESSAGE.SET_NAME('CSI','CSI_API_NO_PRIMARY_UOM_CODE');
1965 FND_MESSAGE.SET_TOKEN('UNIT_OF_MEASURE',p_uom_code);
1966 FND_MSG_PUB.Add;
1967 l_return_value := FALSE;
1968 END;
1969 -- if primary uom code exists, then check whether it is same as the uom code passed
1970 IF ((to_unit IS NOT NULL) AND (to_unit <> FND_API.G_MISS_CHAR)) THEN
1971 IF to_unit = p_uom_code THEN
1972 p_quantity := p_quantity;
1973 p_uom_code := to_unit;
1974 l_return_value := TRUE;
1975 ELSE
1976 -- getting the conversion rate for the unit of measure, quantity passed
1977 l_quantity := inv_convert.inv_um_convert
1978 (item_id => p_inv_item_id,
1979 precision => 6,
1980 from_quantity => p_quantity,
1981 from_unit => p_uom_code,
1982 to_unit => to_unit,
1983 from_name => NULL,
1984 to_name => NULL );
1985 p_quantity := l_quantity;
1986 p_uom_code := to_unit;
1987 l_return_value := TRUE;
1988 END IF;
1989 END IF;
1990 ELSE
1991 -- raise exception if uom class is invalid
1992 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_UOM_CLASS');
1993 FND_MESSAGE.SET_TOKEN('UNIT_OF_MEASURE',p_uom_code);
1994 FND_MSG_PUB.Add;
1995 l_return_value := FALSE;
1996 END IF;
1997 END IF;
1998 END Is_Valid_Uom;
1999
2000 /*---------------------------------------------------------*/
2001 /* This Procedure validates the item condition by looking */
2002 /* through the mtl material statuses */
2003 /*---------------------------------------------------------*/
2004
2005 PROCEDURE Is_Valid_Condition
2006 (
2007 p_instance_condition_id IN NUMBER,
2008 p_creation_complete_flag IN OUT NOCOPY VARCHAR2,
2009 l_return_value IN OUT NOCOPY BOOLEAN
2010 ) IS
2011 l_dummy VARCHAR2(2);
2012 l_stack_err_msg BOOLEAN DEFAULT TRUE;
2013 BEGIN
2014 -- Verify that the Instance Condition is valid i.e.
2015 -- it exists in inventory material status codes (MTL_MATERIAL_STATUSES_B)
2016
2017 IF ((p_instance_condition_id IS NULL) OR
2018 (p_instance_condition_id = FND_API.G_MISS_NUM)) THEN
2019 l_return_value := TRUE;
2020 ELSE
2021 BEGIN
2022 SELECT '1'
2023 INTO l_dummy
2024 FROM mtl_material_statuses
2025 WHERE status_id = p_instance_condition_id;
2026 l_return_value := TRUE;
2027
2028 EXCEPTION
2029 WHEN NO_DATA_FOUND THEN
2030 l_return_value := FALSE;
2031 IF ( l_stack_err_msg = TRUE ) THEN
2032 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ITEM_CONDITION');
2033 FND_MESSAGE.SET_TOKEN('INSTANCE_CONDITION_ID',p_instance_condition_id);
2034 FND_MSG_PUB.Add;
2035 END IF;
2036 END;
2037 END IF;
2038 END Is_Valid_Condition;
2039
2040 /*--------------------------------------------------------*/
2041 /* This function validates the instance status by */
2042 /* looking into the IB status tables */
2043 /*--------------------------------------------------------*/
2044
2045 PROCEDURE Is_Valid_Status
2046 (
2047 p_instance_status_id IN NUMBER,
2048 p_creation_complete_flag IN OUT NOCOPY VARCHAR2,
2049 l_return_value IN OUT NOCOPY BOOLEAN
2050 )
2051 IS
2052 l_dummy VARCHAR2(30);
2053 l_stack_err_msg BOOLEAN DEFAULT TRUE;
2054
2055 -- Verify the Instance Status is valid (CSI_INSTANCE_STATUSES) . If not
2056 -- raise the CSI_API_INVALID_INST_STATUS exception.
2057 BEGIN
2058
2059 IF ((p_instance_status_id IS NULL) OR
2060 (p_instance_status_id = FND_API.G_MISS_NUM))THEN
2061 IF (p_creation_complete_flag = 'Y') THEN
2062 l_return_value := FALSE;
2063 IF ( l_stack_err_msg = TRUE ) THEN
2064 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INST_STATUS');
2065 FND_MESSAGE.SET_TOKEN('INSTANCE_STATUS_ID',p_instance_status_id);
2066 FND_MSG_PUB.Add;
2067 END IF;
2068 ELSE
2069 p_creation_complete_flag := 'N';
2070 l_return_value := TRUE;
2071 END IF;
2072 ELSE
2073 BEGIN
2074 SELECT '1'
2075 INTO l_dummy
2076 FROM csi_instance_statuses
2077 WHERE instance_status_id = p_instance_status_id;
2078
2079 l_return_value := TRUE;
2080 EXCEPTION
2081 WHEN NO_DATA_FOUND THEN
2082 l_return_value := FALSE;
2083 IF ( l_stack_err_msg = TRUE ) THEN
2084 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INST_STATUS');
2085 FND_MESSAGE.SET_TOKEN('INSTANCE_STATUS_ID',p_instance_status_id);
2086 FND_MSG_PUB.Add;
2087 END IF;
2088
2089 END;
2090 END IF;
2091 END Is_Valid_Status;
2092
2093 /*----------------------------------------------------------*/
2094 /* Function Name : Is_StartDate_Valid */
2095 /* */
2096 /* Description : This function checks if start date */
2097 /* is valid */
2098 /*----------------------------------------------------------*/
2099
2100 FUNCTION Is_StartDate_Valid
2101 ( p_start_date IN DATE,
2102 p_end_date IN DATE,
2103 p_stack_err_msg IN BOOLEAN
2104 ) RETURN BOOLEAN IS
2105
2106 l_return_value BOOLEAN := TRUE;
2107
2108 BEGIN
2109 IF ((p_end_date IS NOT NULL) AND (p_end_date = FND_API.G_MISS_DATE)) THEN
2110
2111 IF p_start_date > p_end_date THEN
2112 l_return_value := FALSE;
2113 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_START_DATE');
2114 FND_MESSAGE.SET_TOKEN('START_DATE_ACTIVE',p_start_date);
2115 FND_MSG_PUB.Add;
2116
2117 ELSIF p_end_date < SYSDATE THEN
2118 l_return_value := FALSE;
2119 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_START_DATE');
2120 FND_MESSAGE.SET_TOKEN('START_DATE_ACTIVE',p_start_date);
2121 FND_MSG_PUB.Add;
2122 ELSE l_return_value := TRUE;
2123 END IF;
2124
2125 ELSE
2126 l_return_value := TRUE;
2127
2128 END IF;
2129 RETURN l_return_value;
2130 END Is_StartDate_Valid;
2131
2132 /*----------------------------------------------------------*/
2133 /* Function Name : Is_EndDate_Valid */
2134 /* */
2135 /* Description : This function checks if end date */
2136 /* is valid */
2137 /*----------------------------------------------------------*/
2138 FUNCTION Is_EndDate_Valid
2139 (
2140 p_start_date IN DATE,
2141 p_end_date IN DATE,
2142 p_stack_err_msg IN BOOLEAN
2143 ) RETURN BOOLEAN IS
2144
2145 l_return_value BOOLEAN := TRUE;
2146
2147 BEGIN
2148
2149 IF ((p_end_date IS NOT NULL) AND (p_end_date <> FND_API.G_MISS_DATE))THEN
2150
2151 IF p_start_date > p_end_date THEN
2152 l_return_value := FALSE;
2153 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_END_DATE');
2154 FND_MESSAGE.SET_TOKEN('END_DATE_ACTIVE',to_char(p_end_date,'dd-mm-yy hh24:mm:ss'));
2155 FND_MSG_PUB.Add;
2156
2157 ELSIF p_end_date < SYSDATE THEN
2158 l_return_value := FALSE;
2159 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_END_DATE');
2160 FND_MESSAGE.SET_TOKEN('END_DATE_ACTIVE',to_char(p_end_date,'dd-mm-yy hh24:mm:ss'));
2161 FND_MSG_PUB.Add;
2162
2163 ELSE l_return_value := TRUE;
2164 End IF;
2165
2166 ELSE
2167 l_return_value := TRUE;
2168
2169 END IF;
2170 RETURN l_return_value;
2171 END Is_EndDate_Valid;
2172
2173 /*-----------------------------------------------------*/
2174 /* This function validates the system id by looking */
2175 /* into the CSI systems table */
2176 /*-----------------------------------------------------*/
2177
2178 FUNCTION Is_Valid_System_Id
2179 (
2180 p_system_id IN NUMBER,
2181 p_stack_err_msg IN BOOLEAN
2182 )
2183 RETURN BOOLEAN IS
2184
2185 l_dummy NUMBER;
2186 l_return_value BOOLEAN := TRUE;
2187
2188 -- Validate the System ID against CSI_SYSTEMS_VL table .
2189 BEGIN
2190
2191 IF ((p_system_id IS NULL) OR
2192 (p_system_id = FND_API.G_MISS_NUM)) THEN
2193 l_return_value := TRUE;
2194 ELSE
2195 BEGIN
2196 SELECT '1'
2197 INTO l_dummy
2198 FROM csi_systems_vl
2199 WHERE system_id = p_system_id
2200 AND ( (end_date_active is null) OR -- Fix for bug # 2783027
2201 (end_date_active > sysdate) );
2202
2203 l_return_value := TRUE;
2204 EXCEPTION
2205 WHEN NO_DATA_FOUND THEN
2206 IF ( p_stack_err_msg = TRUE ) THEN
2207 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_SYSTEM_ID');
2208 FND_MESSAGE.SET_TOKEN('SYSTEM_ID',p_system_id);
2209 FND_MSG_PUB.Add;
2210 END IF;
2211 l_return_value := FALSE;
2212 END;
2213 END IF;
2214 RETURN l_return_value;
2215 END Is_Valid_System_Id;
2216
2217 /*-----------------------------------------------------*/
2218 /* This function checks for the instance type code */
2219 /* by looking through the CSI lookups */
2220 /*-----------------------------------------------------*/
2221
2222 FUNCTION Is_Valid_Instance_Type
2223 (
2224 p_instance_type_code IN VARCHAR2,
2225 p_stack_err_msg IN BOOLEAN
2226 )
2227 RETURN BOOLEAN IS
2228
2229 l_dummy VARCHAR2(30);
2230 l_return_value BOOLEAN := TRUE;
2231 l_inst_lookup_type VARCHAR2(30) := 'CSI_INST_TYPE_CODE';
2232
2233 -- Validate the Instance Type Code against CSI_LOOKUPS table .
2234 BEGIN
2235
2236 IF ((p_instance_type_code IS NULL) OR
2237 (p_instance_type_code = FND_API.G_MISS_CHAR)) THEN
2238 l_return_value := TRUE;
2239 ELSE
2240 BEGIN
2241 SELECT '1'
2242 INTO l_dummy
2243 FROM csi_lookups
2244 WHERE lookup_code = UPPER(p_instance_type_code)
2245 AND lookup_type = l_inst_lookup_type;
2246 l_return_value := TRUE;
2247
2248 EXCEPTION
2249 WHEN NO_DATA_FOUND THEN
2250 IF ( p_stack_err_msg = TRUE ) THEN
2251 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_TYPE');
2252 FND_MESSAGE.SET_TOKEN('INSTANCE_TYPE_CODE',p_instance_type_code);
2253 FND_MSG_PUB.Add;
2254 END IF;
2255 l_return_value := FALSE;
2256 END;
2257 END IF;
2258 RETURN l_return_value;
2259 END Is_Valid_Instance_Type;
2260
2261 /*-----------------------------------------------------*/
2262 /* This function checks for the instance usage code */
2263 /* by looking through the CSI lookups */
2264 /*-----------------------------------------------------*/
2265
2266 FUNCTION Valid_Inst_Usage_Code
2267 (
2268 p_inst_usage_code IN VARCHAR2,
2269 p_stack_err_msg IN BOOLEAN
2270 )
2271 RETURN BOOLEAN IS
2272
2273 l_dummy VARCHAR2(30);
2274 l_return_value BOOLEAN := TRUE;
2275 l_usage_lookup_type VARCHAR2(30) := 'CSI_INSTANCE_USAGE_CODE';
2276
2277 -- Validate the System ID against CSI_LOOKUPS table .
2278 BEGIN
2279
2280 IF ((p_inst_usage_code IS NULL) OR
2281 (p_inst_usage_code = FND_API.G_MISS_CHAR)) THEN
2282 l_return_value := TRUE;
2283 ELSE
2284 BEGIN
2285 SELECT '1'
2286 INTO l_dummy
2287 FROM csi_lookups
2288 WHERE lookup_code = UPPER(p_inst_usage_code)
2289 AND lookup_type = l_usage_lookup_type;
2290 l_return_value := TRUE;
2291
2292 EXCEPTION
2293 WHEN NO_DATA_FOUND THEN
2294 IF ( p_stack_err_msg = TRUE ) THEN
2295 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_USAGE_CODE');
2296 FND_MESSAGE.SET_TOKEN('INSTANCE_USAGE_CODE',p_inst_usage_code);
2297 FND_MSG_PUB.Add;
2298 END IF;
2299 l_return_value := FALSE;
2300 END;
2301 END IF;
2302 RETURN l_return_value;
2303 END Valid_Inst_Usage_Code;
2304
2305 /*---------------------------------------------------------*/
2306 /* This function checks for the operational status code */
2307 /* by looking through the CSI lookups */
2308 /*---------------------------------------------------------*/
2309
2310 FUNCTION Valid_operational_status
2311 (
2312 p_operational_status IN VARCHAR2
2313 )
2314 RETURN BOOLEAN IS
2315
2316 l_dummy VARCHAR2(30);
2317 l_return_value BOOLEAN := TRUE;
2318 l_operational_lookup_type VARCHAR2(30) := 'CSI_OPERATIONAL_STATUS_CODE';
2319
2320 -- Validate the System ID against CSI_LOOKUPS table .
2321 BEGIN
2322 IF ((p_operational_status IS NULL) OR
2323 (p_operational_status = FND_API.G_MISS_CHAR))
2324 THEN
2325 l_return_value := TRUE;
2326 ELSE
2327 BEGIN
2328 SELECT '1'
2329 INTO l_dummy
2330 FROM csi_lookups
2331 WHERE lookup_code = UPPER(p_operational_status)
2332 AND lookup_type = l_operational_lookup_type;
2333 l_return_value := TRUE;
2334
2335 EXCEPTION
2336 WHEN NO_DATA_FOUND THEN
2337 FND_MESSAGE.SET_NAME('CSI','CSI_OPERATIONAL_STATUS_CODE');
2338 FND_MESSAGE.SET_TOKEN('OPERATIONAL_STATUS_CODE',p_operational_status);
2339 FND_MSG_PUB.Add;
2340 l_return_value := FALSE;
2341 END;
2342 END IF;
2343 RETURN l_return_value;
2344 END Valid_operational_status;
2345
2346 /*---------------------------------------------------------*/
2347 /* This function checks for the currency code */
2348 /* by looking through the fnd_currencies */
2349 /*---------------------------------------------------------*/
2350
2351 FUNCTION Valid_currency_code
2352 (
2353 p_currency_code IN VARCHAR2
2354 )
2355 RETURN BOOLEAN IS
2356
2357 l_dummy VARCHAR2(30);
2358 l_return_value BOOLEAN := TRUE;
2359 BEGIN
2360 IF ((p_currency_code IS NULL) OR
2361 (p_currency_code = FND_API.G_MISS_CHAR))
2362 THEN
2363 l_return_value := TRUE;
2364 ELSE
2365 BEGIN
2366 SELECT '1'
2367 INTO l_dummy
2368 FROM fnd_currencies
2369 WHERE currency_code = UPPER(p_currency_code);
2370 l_return_value := TRUE;
2371
2372 EXCEPTION
2373 WHEN NO_DATA_FOUND THEN
2374 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_CURRENCY_CODE');
2375 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',p_currency_code);
2376 FND_MSG_PUB.Add;
2377 l_return_value := FALSE;
2378 END;
2379 END IF;
2380 RETURN l_return_value;
2381 END Valid_currency_code;
2382
2383 /*---------------------------------------------------------*/
2384 /* This function checks if status is updateable */
2385 /* by looking through the csi_instance_statuses */
2386 /*---------------------------------------------------------*/
2387 FUNCTION is_status_updateable
2388 (
2389 p_instance_status IN NUMBER,
2390 p_current_status IN NUMBER
2391 )
2392 RETURN BOOLEAN IS
2393
2394 l_change_allowed VARCHAR2(1);
2395 l_return_value BOOLEAN := TRUE;
2396 BEGIN
2397 BEGIN
2398 SELECT status_change_allowed_flag
2399 INTO l_change_allowed
2400 FROM csi_instance_statuses
2401 WHERE instance_status_id = p_current_status;
2402 IF NVL(l_change_allowed,'Y')='Y'
2403 THEN
2404 l_return_value := TRUE;
2405 ELSE
2406 FND_MESSAGE.SET_NAME('CSI','CSI_NO_STATUS_CHANGE');
2407 FND_MESSAGE.SET_TOKEN('CURRENT_STATUS',p_current_status);
2408 FND_MESSAGE.SET_TOKEN('INSTANCE_STATUS_ID',p_instance_status);
2409 FND_MSG_PUB.Add;
2410 l_return_value := FALSE;
2411 END IF;
2412 EXCEPTION
2413 WHEN NO_DATA_FOUND THEN
2414 FND_MESSAGE.SET_NAME('CSI','CSI_NO_STATUS_CHANGE');
2415 FND_MESSAGE.SET_TOKEN('CURRENT_STATUS',p_current_status);
2416 FND_MESSAGE.SET_TOKEN('INSTANCE_STATUS_ID',p_instance_status);
2417 FND_MSG_PUB.Add;
2418 l_return_value := FALSE;
2419 END;
2420 RETURN l_return_value;
2421 END is_status_updateable;
2422
2423 /*-----------------------------------------------------*/
2424 /* This function checks for the uniqueness of the */
2425 /* party owner */
2426 /*-----------------------------------------------------*/
2427
2428 FUNCTION validate_uniqueness(p_instance_rec csi_datastructures_pub.instance_rec,
2429 p_party_rec csi_datastructures_pub.party_rec,
2430 p_srl_control_code NUMBER,
2431 p_csi_txn_type_id NUMBER )
2432 RETURN BOOLEAN IS
2433
2434 l_serial_code NUMBER;
2435 l_return_value BOOLEAN;
2436 l_count NUMBER;
2437
2438 BEGIN
2439 If p_srl_control_code is not null AND
2440 p_srl_control_code <> FND_API.G_MISS_NUM then
2441 l_serial_code := p_srl_control_code;
2442 l_return_value := TRUE;
2443 Else
2444 select serial_number_control_code
2445 into l_serial_code
2446 from mtl_system_items
2447 where inventory_item_id = p_instance_rec.inventory_item_id
2448 and organization_id = p_instance_rec.vld_organization_id;
2449 l_return_value := TRUE;
2450 End if;
2451 -- added by rtalluri for bugfix 2324745 on 04/23/02
2452 IF NOT Is_treated_serialized(
2453 p_serial_control_code => l_serial_code, --serialized
2454 p_location_type_code => p_instance_rec.location_type_code,
2455 p_transaction_type_id => p_csi_txn_type_id
2456 )
2457 THEN
2458 -- end of addition by rtalluri for bugfix 2324745 on 04/23/02
2459 IF p_instance_rec.location_type_code = 'INVENTORY' AND
2460 p_instance_rec.instance_usage_code NOT IN ('IN_RELATIONSHIP','RETURNED')
2461 THEN
2462 BEGIN
2463 -- srramakr Removed the reference to CSI_I_PARTIES since we have the denormalized
2464 -- columns owner_party_id and owner_party_source_table in CSI_ITEM_INSTANCES
2465 SELECT '1'
2466 INTO l_count
2467 FROM csi_item_instances a
2468 -- ,csi_i_parties b -- Not required as we have the denormalized column in CII
2469 -- WHERE a.instance_id = b.instance_id
2470 WHERE a.inventory_item_id = p_instance_rec.inventory_item_id
2471 AND a.inv_organization_id = p_instance_rec.inv_organization_id
2472 AND a.inv_subinventory_name = p_instance_rec.inv_subinventory_name
2473 --Added location_type_code for bug 5514442--
2474 AND a.location_type_code = p_instance_rec.location_type_code
2475 AND a.instance_id <> p_instance_rec.instance_id
2476 AND a.serial_number IS NULL
2477 AND a.instance_usage_code NOT IN ('IN_RELATIONSHIP','RETURNED')
2478 AND a.active_end_date IS NULL --code added for bug 5702911 --
2479 AND (
2480 (a.inventory_revision IS NULL AND p_instance_rec.inventory_revision IS NULL) OR
2481 (a.inventory_revision IS NULL AND p_instance_rec.inventory_revision = FND_API.G_MISS_CHAR) OR
2482 (a.inventory_revision = p_instance_rec.inventory_revision)
2483 )
2484 AND (
2485 (a.lot_number IS NULL AND p_instance_rec.lot_number IS NULL) OR
2486 (a.lot_number IS NULL AND p_instance_rec.lot_number = FND_API.G_MISS_CHAR) OR
2487 (a.lot_number = p_instance_rec.lot_number)
2488 )
2489 AND (
2490 (a.inv_locator_id IS NULL AND p_instance_rec.inv_locator_id IS NULL) OR
2491 (a.inv_locator_id IS NULL AND p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM) OR
2492 (a.inv_locator_id = p_instance_rec.inv_locator_id)
2493 )
2494 AND a.owner_party_id = p_party_rec.party_id
2495 AND a.owner_party_source_table = p_party_rec.party_source_table;
2496 -- AND b.party_id = p_party_rec.party_id
2497 -- AND b.party_source_table = p_party_rec.party_source_table
2498 -- AND b.relationship_type_code = 'OWNER';
2499
2500 l_return_value := FALSE;
2501 FND_MESSAGE.SET_NAME('CSI','CSI_API_OWNER_NOT_UNIQUE');
2502 FND_MSG_PUB.ADD;
2503
2504 EXCEPTION
2505 WHEN NO_DATA_FOUND THEN
2506 l_return_value := TRUE;
2507 WHEN TOO_MANY_ROWS THEN
2508 FND_MESSAGE.SET_NAME('CSI','CSI_API_OWNER_NOT_UNIQUE');
2509 FND_MSG_PUB.ADD;
2510 l_return_value := FALSE;
2511 WHEN OTHERS THEN
2512 FND_MESSAGE.SET_NAME('CSI','CSI_API_OWNER_OTHERS_EXCEPTION');
2513 FND_MSG_PUB.ADD;
2514 l_return_value := FALSE;
2515 END;
2516 END IF; -- end if for inventory check
2517 END IF; --end if for serial check
2518 RETURN l_Return_Value;
2519 EXCEPTION
2520 WHEN OTHERS THEN
2521 l_Return_Value := TRUE;
2522 RETURN l_Return_Value;
2523
2524 END validate_uniqueness;
2525
2526 /*-----------------------------------------------------*/
2527 /* This function checks for the location type code */
2528 /* by looking through the CSI lookups */
2529 /*-----------------------------------------------------*/
2530
2531
2532 FUNCTION Is_Valid_Location_Source
2533 (
2534 p_loc_source_table IN VARCHAR2,
2535 p_stack_err_msg IN BOOLEAN
2536 )
2537 RETURN BOOLEAN IS
2538
2539 l_dummy VARCHAR2(30);
2540 l_return_value BOOLEAN := TRUE;
2541 l_loc_lookup_type VARCHAR2(30) := 'CSI_INST_LOCATION_SOURCE_CODE';
2542
2543 BEGIN
2544
2545 IF ((p_loc_source_table IS NULL) OR
2546 (p_loc_source_table = FND_API.G_MISS_CHAR)) THEN
2547 l_return_value := TRUE;
2548 ELSE
2549 BEGIN
2550 SELECT '1'
2551 INTO l_dummy
2552 FROM csi_lookups
2553 WHERE lookup_code = UPPER(p_loc_source_table)
2554 AND lookup_type = l_loc_lookup_type;
2555
2556 l_return_value:= TRUE;
2557 EXCEPTION
2558 WHEN NO_DATA_FOUND THEN
2559 IF ( p_stack_err_msg = TRUE ) THEN
2560 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_SOURCE');
2561 FND_MESSAGE.SET_TOKEN('LOCATION_SOURCE_TABLE',p_loc_source_table);
2562 FND_MSG_PUB.Add;
2563 END IF;
2564 l_return_value := FALSE;
2565 END;
2566 END IF;
2567 RETURN l_return_value;
2568 END Is_Valid_Location_Source;
2569
2570 /*-----------------------------------------------------*/
2571 /* This procedure is used to validate the values */
2572 /* passed to the update_item_instance */
2573 /*-----------------------------------------------------*/
2574
2575 PROCEDURE get_merge_rec (p_instance_rec IN OUT NOCOPY csi_datastructures_pub.instance_rec,
2576 l_curr_instance_rec IN csi_datastructures_pub.instance_rec,
2577 l_get_instance_rec OUT NOCOPY csi_datastructures_pub.instance_rec
2578 )
2579 IS
2580 BEGIN
2581
2582 --
2583 IF ( p_instance_rec.instance_id = fnd_api.g_miss_num )
2584 THEN l_get_instance_rec.instance_id := l_curr_instance_rec.instance_id;
2585 ELSE l_get_instance_rec.instance_id := p_instance_rec.instance_id;
2586 END IF;
2587
2588 IF ( p_instance_rec.instance_number = fnd_api.g_miss_char )
2589 THEN l_get_instance_rec.instance_number := l_curr_instance_rec.instance_number;
2590 ELSE l_get_instance_rec.instance_number := p_instance_rec.instance_number;
2591 END IF;
2592
2593 IF ( p_instance_rec.external_reference = fnd_api.g_miss_char )
2594 THEN l_get_instance_rec.external_reference := l_curr_instance_rec.external_reference;
2595 ELSE l_get_instance_rec.external_reference := p_instance_rec.external_reference;
2596 END IF;
2597 --
2598 IF ( p_instance_rec.inventory_item_id = fnd_api.g_miss_num )
2599 THEN l_get_instance_rec.inventory_item_id := l_curr_instance_rec.inventory_item_id;
2600 ELSE l_get_instance_rec.inventory_item_id := p_instance_rec.inventory_item_id;
2601 END IF;
2602 --
2603 IF ( p_instance_rec.inventory_revision = fnd_api.g_miss_char )
2604 THEN l_get_instance_rec.inventory_revision := l_curr_instance_rec.inventory_revision;
2605 ELSE l_get_instance_rec.inventory_revision := p_instance_rec.inventory_revision;
2606 END IF;
2607 --
2608 IF ( p_instance_rec.inv_master_organization_id = fnd_api.g_miss_num )
2609 THEN l_get_instance_rec.inv_master_organization_id := l_curr_instance_rec.inv_master_organization_id;
2610 ELSE l_get_instance_rec.inv_master_organization_id := p_instance_rec.inv_master_organization_id;
2611 END IF;
2612 --
2613 IF ( p_instance_rec.serial_number = fnd_api.g_miss_char )
2614 THEN l_get_instance_rec.serial_number := l_curr_instance_rec.serial_number;
2615 ELSE l_get_instance_rec.serial_number := p_instance_rec.serial_number;
2616 END IF;
2617 --
2618 IF ( p_instance_rec.mfg_serial_number_flag = fnd_api.g_miss_char )
2619 THEN l_get_instance_rec.mfg_serial_number_flag := l_curr_instance_rec.mfg_serial_number_flag;
2620 ELSE l_get_instance_rec.mfg_serial_number_flag := p_instance_rec.mfg_serial_number_flag;
2621 END IF;
2622 --
2623 IF ( p_instance_rec.lot_number = fnd_api.g_miss_char )
2624 THEN l_get_instance_rec.lot_number := l_curr_instance_rec.lot_number;
2625 ELSE l_get_instance_rec.lot_number := p_instance_rec.lot_number;
2626 END IF;
2627 --
2628 IF ( p_instance_rec.quantity = fnd_api.g_miss_num )
2629 THEN l_get_instance_rec.quantity := l_curr_instance_rec.quantity;
2630 ELSE l_get_instance_rec.quantity := p_instance_rec.quantity;
2631 END IF;
2632 --
2633 IF ( p_instance_rec.unit_of_measure = fnd_api.g_miss_char )
2634 THEN l_get_instance_rec.unit_of_measure := l_curr_instance_rec.unit_of_measure;
2635 ELSE l_get_instance_rec.unit_of_measure := p_instance_rec.unit_of_measure;
2636 END IF;
2637 --
2638 IF ( p_instance_rec.accounting_class_code = fnd_api.g_miss_char )
2639 THEN l_get_instance_rec.accounting_class_code := l_curr_instance_rec.accounting_class_code;
2640 ELSE l_get_instance_rec.accounting_class_code := p_instance_rec.accounting_class_code;
2641 END IF;
2642 --
2643 IF ( p_instance_rec.instance_condition_id = fnd_api.g_miss_num )
2644 THEN l_get_instance_rec.instance_condition_id := l_curr_instance_rec.instance_condition_id;
2645 ELSE l_get_instance_rec.instance_condition_id := p_instance_rec.instance_condition_id;
2646 END IF;
2647 --
2648 IF ( p_instance_rec.instance_status_id = fnd_api.g_miss_num )
2649 THEN l_get_instance_rec.instance_status_id := l_curr_instance_rec.instance_status_id;
2650 ELSE l_get_instance_rec.instance_status_id := p_instance_rec.instance_status_id;
2651 END IF;
2652 --
2653 IF ( p_instance_rec.customer_view_flag = fnd_api.g_miss_char )
2654 THEN l_get_instance_rec.customer_view_flag := l_curr_instance_rec.customer_view_flag;
2655 ELSE l_get_instance_rec.customer_view_flag := p_instance_rec.customer_view_flag;
2656 END IF;
2657 --
2658 IF ( p_instance_rec.merchant_view_flag = fnd_api.g_miss_char )
2659 THEN l_get_instance_rec.merchant_view_flag := l_curr_instance_rec.merchant_view_flag;
2660 ELSE l_get_instance_rec.merchant_view_flag := p_instance_rec.merchant_view_flag;
2661 END IF;
2662 --
2663 IF ( p_instance_rec.sellable_flag = fnd_api.g_miss_char )
2664 THEN l_get_instance_rec.sellable_flag := l_curr_instance_rec.sellable_flag;
2665 ELSE l_get_instance_rec.sellable_flag := p_instance_rec.sellable_flag;
2666 END IF;
2667 --
2668 IF ( p_instance_rec.system_id = fnd_api.g_miss_num )
2669 THEN l_get_instance_rec.system_id := l_curr_instance_rec.system_id;
2670 ELSE l_get_instance_rec.system_id := p_instance_rec.system_id;
2671 END IF;
2672 --
2673 IF ( p_instance_rec.instance_type_code = fnd_api.g_miss_char )
2674 THEN l_get_instance_rec.instance_type_code := l_curr_instance_rec.instance_type_code;
2675 ELSE l_get_instance_rec.instance_type_code := p_instance_rec.instance_type_code;
2676 END IF;
2677 --
2678 IF ( p_instance_rec.active_start_date = fnd_api.g_miss_date )
2679 THEN l_get_instance_rec.active_start_date := l_curr_instance_rec.active_start_date;
2680 ELSE l_get_instance_rec.active_start_date := p_instance_rec.active_start_date;
2681 END IF;
2682 --
2683 IF ( p_instance_rec.active_end_date = fnd_api.g_miss_date )
2684 THEN l_get_instance_rec.active_end_date := l_curr_instance_rec.active_end_date;
2685 ELSE l_get_instance_rec.active_end_date := p_instance_rec.active_end_date;
2686 END IF;
2687 --
2688 IF ( p_instance_rec.location_type_code = fnd_api.g_miss_char )
2689 THEN l_get_instance_rec.location_type_code := l_curr_instance_rec.location_type_code;
2690 ELSE l_get_instance_rec.location_type_code := p_instance_rec.location_type_code;
2691 END IF;
2692 --
2693 IF ( p_instance_rec.location_id = fnd_api.g_miss_num )
2694 THEN l_get_instance_rec.location_id := l_curr_instance_rec.location_id;
2695 ELSE l_get_instance_rec.location_id := p_instance_rec.location_id;
2696 END IF;
2697 --
2698 IF ( p_instance_rec.inv_organization_id = fnd_api.g_miss_num )
2699 THEN l_get_instance_rec.inv_organization_id := l_curr_instance_rec.inv_organization_id;
2700 ELSE l_get_instance_rec.inv_organization_id := p_instance_rec.inv_organization_id;
2701 END IF;
2702 --
2703 IF ( p_instance_rec.inv_subinventory_name = fnd_api.g_miss_char )
2704 THEN l_get_instance_rec.inv_subinventory_name := l_curr_instance_rec.inv_subinventory_name;
2705 ELSE l_get_instance_rec.inv_subinventory_name := p_instance_rec.inv_subinventory_name;
2706 END IF;
2707 --
2708 IF ( p_instance_rec.inv_locator_id = fnd_api.g_miss_num )
2709 THEN l_get_instance_rec.inv_locator_id := l_curr_instance_rec.inv_locator_id;
2710 ELSE l_get_instance_rec.inv_locator_id := p_instance_rec.inv_locator_id;
2711 END IF;
2712 --
2713 IF ( p_instance_rec.pa_project_id = fnd_api.g_miss_num )
2714 THEN l_get_instance_rec.pa_project_id := l_curr_instance_rec.pa_project_id;
2715 ELSE l_get_instance_rec.pa_project_id := p_instance_rec.pa_project_id;
2716 END IF;
2717 --
2718 IF ( p_instance_rec.pa_project_task_id = fnd_api.g_miss_num )
2719 THEN l_get_instance_rec.pa_project_task_id := l_curr_instance_rec.pa_project_task_id;
2720 ELSE l_get_instance_rec.pa_project_task_id := p_instance_rec.pa_project_task_id;
2721 END IF;
2722 --
2723 IF ( p_instance_rec.in_transit_order_line_id = fnd_api.g_miss_num )
2724 THEN l_get_instance_rec.in_transit_order_line_id := l_curr_instance_rec.in_transit_order_line_id;
2725 ELSE l_get_instance_rec.in_transit_order_line_id := p_instance_rec.in_transit_order_line_id;
2726 END IF;
2727 --
2728 IF ( p_instance_rec.wip_job_id = fnd_api.g_miss_num )
2729 THEN l_get_instance_rec.wip_job_id := l_curr_instance_rec.wip_job_id;
2730 ELSE l_get_instance_rec.wip_job_id := p_instance_rec.wip_job_id;
2731 END IF;
2732 --
2733 IF ( p_instance_rec.po_order_line_id = fnd_api.g_miss_num )
2734 THEN l_get_instance_rec.po_order_line_id := l_curr_instance_rec.po_order_line_id;
2735 ELSE l_get_instance_rec.po_order_line_id := p_instance_rec.po_order_line_id;
2736 END IF;
2737 --
2738 IF ( p_instance_rec.last_oe_order_line_id = fnd_api.g_miss_num )
2739 THEN l_get_instance_rec.last_oe_order_line_id := l_curr_instance_rec.last_oe_order_line_id;
2740 ELSE l_get_instance_rec.last_oe_order_line_id := p_instance_rec.last_oe_order_line_id;
2741 END IF;
2742 --
2743 IF ( p_instance_rec.last_oe_rma_line_id = fnd_api.g_miss_num )
2744 THEN l_get_instance_rec.last_oe_rma_line_id := l_curr_instance_rec.last_oe_rma_line_id;
2745 ELSE l_get_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
2746 END IF;
2747 --
2748 IF ( p_instance_rec.last_po_po_line_id = fnd_api.g_miss_num )
2749 THEN l_get_instance_rec.last_po_po_line_id := l_curr_instance_rec.last_po_po_line_id;
2750 ELSE l_get_instance_rec.last_po_po_line_id := p_instance_rec.last_po_po_line_id;
2751 END IF;
2752 --
2753 IF ( p_instance_rec.last_oe_po_number = fnd_api.g_miss_char )
2754 THEN l_get_instance_rec.last_oe_po_number := l_curr_instance_rec.last_oe_po_number;
2755 ELSE l_get_instance_rec.last_oe_po_number := p_instance_rec.last_oe_po_number;
2756 END IF;
2757 --
2758 IF ( p_instance_rec.last_wip_job_id = fnd_api.g_miss_num )
2759 THEN l_get_instance_rec.last_wip_job_id := l_curr_instance_rec.last_wip_job_id;
2760 ELSE l_get_instance_rec.last_wip_job_id := p_instance_rec.last_wip_job_id;
2761 END IF;
2762 --
2763 IF ( p_instance_rec.last_pa_project_id = fnd_api.g_miss_num )
2764 THEN l_get_instance_rec.last_pa_project_id := l_curr_instance_rec.last_pa_project_id;
2765 ELSE l_get_instance_rec.last_pa_project_id := p_instance_rec.last_pa_project_id;
2766 END IF;
2767 --
2768 IF ( p_instance_rec.last_pa_task_id = fnd_api.g_miss_num )
2769 THEN l_get_instance_rec.last_pa_task_id := l_curr_instance_rec.last_pa_task_id;
2770 ELSE l_get_instance_rec.last_pa_task_id := p_instance_rec.last_pa_task_id;
2771 END IF;
2772 --
2773 IF ( p_instance_rec.last_oe_agreement_id = fnd_api.g_miss_num )
2774 THEN l_get_instance_rec.last_oe_agreement_id := l_curr_instance_rec.last_oe_agreement_id;
2775 ELSE l_get_instance_rec.last_oe_agreement_id := p_instance_rec.last_oe_agreement_id;
2776 END IF;
2777 --
2778 IF ( p_instance_rec.install_date = fnd_api.g_miss_date )
2779 THEN l_get_instance_rec.install_date := l_curr_instance_rec.install_date;
2780 ELSE l_get_instance_rec.install_date := p_instance_rec.install_date;
2781 END IF;
2782 --
2783 IF ( p_instance_rec.manually_created_flag = fnd_api.g_miss_char )
2784 THEN l_get_instance_rec.manually_created_flag := l_curr_instance_rec.manually_created_flag;
2785 ELSE l_get_instance_rec.manually_created_flag := p_instance_rec.manually_created_flag;
2786 END IF;
2787 --
2788 IF ( p_instance_rec.return_by_date = fnd_api.g_miss_date )
2789 THEN l_get_instance_rec.return_by_date := l_curr_instance_rec.return_by_date;
2790 ELSE l_get_instance_rec.return_by_date := p_instance_rec.return_by_date;
2791 END IF;
2792 --
2793 IF ( p_instance_rec.actual_return_date = fnd_api.g_miss_date )
2794 THEN l_get_instance_rec.actual_return_date := l_curr_instance_rec.actual_return_date;
2795 ELSE l_get_instance_rec.actual_return_date := p_instance_rec.actual_return_date;
2796 END IF;
2797 --
2798 IF ( p_instance_rec.creation_complete_flag = fnd_api.g_miss_char )
2799 THEN l_get_instance_rec.creation_complete_flag := l_curr_instance_rec.creation_complete_flag;
2800 ELSE l_get_instance_rec.creation_complete_flag := p_instance_rec.creation_complete_flag;
2801 END IF;
2802 --
2803 IF ( p_instance_rec.completeness_flag = fnd_api.g_miss_char )
2804 THEN l_get_instance_rec.completeness_flag := l_curr_instance_rec.completeness_flag;
2805 ELSE l_get_instance_rec.completeness_flag := p_instance_rec.completeness_flag;
2806 END IF;
2807 --
2808 IF ( p_instance_rec.context = fnd_api.g_miss_char )
2809 THEN l_get_instance_rec.context := l_curr_instance_rec.context;
2810 ELSE l_get_instance_rec.context := p_instance_rec.context;
2811 END IF;
2812 --
2813 IF ( p_instance_rec.attribute1 = fnd_api.g_miss_char )
2814 THEN l_get_instance_rec.attribute1 := l_curr_instance_rec.attribute1;
2815 ELSE l_get_instance_rec.attribute1 := p_instance_rec.attribute1;
2816 END IF;
2817 --
2818 IF ( p_instance_rec.attribute2 = fnd_api.g_miss_char )
2819 THEN l_get_instance_rec.attribute2 := l_curr_instance_rec.attribute2;
2820 ELSE l_get_instance_rec.attribute2 := p_instance_rec.attribute2;
2821 END IF;
2822 --
2823 IF ( p_instance_rec.attribute3 = fnd_api.g_miss_char )
2824 THEN l_get_instance_rec.attribute3 := l_curr_instance_rec.attribute3;
2825 ELSE l_get_instance_rec.attribute3 := p_instance_rec.attribute3;
2826 END IF;
2827 --
2828 IF ( p_instance_rec.attribute4 = fnd_api.g_miss_char )
2829 THEN l_get_instance_rec.attribute4 := l_curr_instance_rec.attribute4;
2830 ELSE l_get_instance_rec.attribute4 := p_instance_rec.attribute4;
2831 END IF;
2832 --
2833 IF ( p_instance_rec.attribute5 = fnd_api.g_miss_char )
2834 THEN l_get_instance_rec.attribute5 := l_curr_instance_rec.attribute5;
2835 ELSE l_get_instance_rec.attribute5 := p_instance_rec.attribute5;
2836 END IF;
2837 --
2838 IF ( p_instance_rec.attribute6 = fnd_api.g_miss_char )
2839 THEN l_get_instance_rec.attribute6 := l_curr_instance_rec.attribute6;
2840 ELSE l_get_instance_rec.attribute6 := p_instance_rec.attribute6;
2841 END IF;
2842 --
2843 IF ( p_instance_rec.attribute7 = fnd_api.g_miss_char )
2844 THEN l_get_instance_rec.attribute7 := l_curr_instance_rec.attribute7;
2845 ELSE l_get_instance_rec.attribute7 := p_instance_rec.attribute7;
2846 END IF;
2847 --
2848 IF ( p_instance_rec.attribute8 = fnd_api.g_miss_char )
2849 THEN l_get_instance_rec.attribute8 := l_curr_instance_rec.attribute8;
2850 ELSE l_get_instance_rec.attribute8 := p_instance_rec.attribute8;
2851 END IF;
2852 --
2853 IF ( p_instance_rec.attribute9 = fnd_api.g_miss_char )
2854 THEN l_get_instance_rec.attribute9 := l_curr_instance_rec.attribute9;
2855 ELSE l_get_instance_rec.attribute9 := p_instance_rec.attribute9;
2856 END IF;
2857 --
2858 IF ( p_instance_rec.attribute10 = fnd_api.g_miss_char )
2859 THEN l_get_instance_rec.attribute10 := l_curr_instance_rec.attribute10;
2860 ELSE l_get_instance_rec.attribute10 := p_instance_rec.attribute10;
2861 END IF;
2862 --
2863 IF ( p_instance_rec.attribute11 = fnd_api.g_miss_char )
2864 THEN l_get_instance_rec.attribute11 := l_curr_instance_rec.attribute11;
2865 ELSE l_get_instance_rec.attribute11 := p_instance_rec.attribute11;
2866 END IF;
2867 --
2868 IF ( p_instance_rec.attribute12 = fnd_api.g_miss_char )
2869 THEN l_get_instance_rec.attribute12 := l_curr_instance_rec.attribute12;
2870 ELSE l_get_instance_rec.attribute12 := p_instance_rec.attribute12;
2871 END IF;
2872 --
2873 IF ( p_instance_rec.attribute13 = fnd_api.g_miss_char )
2874 THEN l_get_instance_rec.attribute13 := l_curr_instance_rec.attribute13;
2875 ELSE l_get_instance_rec.attribute13 := p_instance_rec.attribute13;
2876 END IF;
2877 --
2878 IF ( p_instance_rec.attribute14 = fnd_api.g_miss_char )
2879 THEN l_get_instance_rec.attribute14 := l_curr_instance_rec.attribute14;
2880 ELSE l_get_instance_rec.attribute14 := p_instance_rec.attribute14;
2881 END IF;
2882 --
2883 IF ( p_instance_rec.attribute15 = fnd_api.g_miss_char )
2884 THEN l_get_instance_rec.attribute15 := l_curr_instance_rec.attribute15;
2885 ELSE l_get_instance_rec.attribute15 := p_instance_rec.attribute15;
2886 END IF;
2887 --
2888 IF ( p_instance_rec.object_version_number = fnd_api.g_miss_num )
2889 THEN l_get_instance_rec.object_version_number := l_curr_instance_rec.object_version_number;
2890 ELSE l_get_instance_rec.object_version_number := p_instance_rec.object_version_number;
2891 END IF;
2892 --
2893 IF ( p_instance_rec.last_txn_line_detail_id = fnd_api.g_miss_num )
2894 THEN l_get_instance_rec.last_txn_line_detail_id := l_curr_instance_rec.last_txn_line_detail_id;
2895 ELSE l_get_instance_rec.last_txn_line_detail_id := p_instance_rec.last_txn_line_detail_id;
2896 END IF;
2897 --
2898 IF ( p_instance_rec.install_location_type_code = fnd_api.g_miss_char )
2899 THEN l_get_instance_rec.install_location_type_code := l_curr_instance_rec.install_location_type_code;
2900 ELSE l_get_instance_rec.install_location_type_code := p_instance_rec.install_location_type_code;
2901 END IF;
2902 --
2903 IF ( p_instance_rec.install_location_id = fnd_api.g_miss_num )
2904 THEN l_get_instance_rec.install_location_id := l_curr_instance_rec.install_location_id;
2905 ELSE l_get_instance_rec.install_location_id := p_instance_rec.install_location_id;
2906 END IF;
2907 --
2908 IF ( p_instance_rec.instance_usage_code = fnd_api.g_miss_char )
2909 THEN l_get_instance_rec.instance_usage_code := l_curr_instance_rec.instance_usage_code;
2910 ELSE l_get_instance_rec.instance_usage_code := p_instance_rec.instance_usage_code;
2911 END IF;
2912 --
2913
2914 /* IF ( p_instance_rec.vld_organization_id = fnd_api.g_miss_num )
2915 THEN l_get_instance_rec.vld_organization_id := l_curr_instance_rec.last_vld_organization_id;
2916 ELSE l_get_instance_rec.vld_organization_id := p_instance_rec.vld_organization_id;
2917 END IF;
2918 --
2919 */
2920
2921 IF ( (p_instance_rec.location_type_code <> FND_API.G_MISS_CHAR) OR
2922 (p_instance_rec.location_id <> FND_API.G_MISS_NUM) OR
2923 (p_instance_rec.inv_organization_id <> FND_API.G_MISS_NUM) OR
2924 (p_instance_rec.inv_subinventory_name <> FND_API.G_MISS_CHAR) OR
2925 (p_instance_rec.inv_locator_id <> FND_API.G_MISS_NUM) OR
2926 (p_instance_rec.pa_project_id <> FND_API.G_MISS_NUM) OR
2927 (p_instance_rec.pa_project_task_id <> FND_API.G_MISS_NUM) OR
2928 (p_instance_rec.in_transit_order_line_id <> FND_API.G_MISS_NUM) OR
2929 (p_instance_rec.wip_job_id <> FND_API.G_MISS_NUM) OR
2930 (p_instance_rec.po_order_line_id <> FND_API.G_MISS_NUM)
2931 ) THEN
2932
2933 IF l_get_instance_rec.location_type_code = 'INVENTORY' THEN
2934
2935 IF p_instance_rec.pa_project_id = FND_API.G_MISS_NUM THEN
2936 p_instance_rec.pa_project_id := NULL;
2937 l_get_instance_rec.pa_project_id := NULL;
2938 END IF;
2939 IF p_instance_rec.pa_project_task_id = FND_API.G_MISS_NUM THEN
2940 p_instance_rec.pa_project_task_id := NULL;
2941 l_get_instance_rec.pa_project_task_id := NULL;
2942 END IF;
2943 IF p_instance_rec.wip_job_id = FND_API.G_MISS_NUM THEN
2944 p_instance_rec.wip_job_id := NULL;
2945 l_get_instance_rec.wip_job_id := NULL;
2946 END IF;
2947 IF p_instance_rec.in_transit_order_line_id = FND_API.G_MISS_NUM THEN
2948 p_instance_rec.in_transit_order_line_id := NULL;
2949 l_get_instance_rec.in_transit_order_line_id := NULL;
2950 END IF;
2951 IF p_instance_rec.po_order_line_id = FND_API.G_MISS_NUM THEN
2952 p_instance_rec.po_order_line_id := NULL;
2953 l_get_instance_rec.po_order_line_id := NULL;
2954 END IF;
2955
2956 ELSIF
2957
2958 l_get_instance_rec.location_type_code = 'PROJECT' THEN
2959
2960 IF p_instance_rec.inv_organization_id = FND_API.G_MISS_NUM THEN
2961 p_instance_rec.inv_organization_id := NULL;
2962 l_get_instance_rec.inv_organization_id := NULL;
2963 END IF;
2964 IF p_instance_rec.inv_subinventory_name = FND_API.G_MISS_CHAR THEN
2965 p_instance_rec.inv_subinventory_name := NULL;
2966 l_get_instance_rec.inv_subinventory_name := NULL;
2967 END IF;
2968 IF p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM THEN
2969 p_instance_rec.inv_locator_id := NULL;
2970 l_get_instance_rec.inv_locator_id := NULL;
2971 END IF;
2972 IF p_instance_rec.wip_job_id = FND_API.G_MISS_NUM THEN
2973 p_instance_rec.wip_job_id := NULL;
2974 l_get_instance_rec.wip_job_id := NULL;
2975 END IF;
2976 IF p_instance_rec.in_transit_order_line_id = FND_API.G_MISS_NUM THEN
2977 p_instance_rec.in_transit_order_line_id := NULL;
2978 l_get_instance_rec.in_transit_order_line_id := NULL;
2979 END IF;
2980 IF p_instance_rec.po_order_line_id = FND_API.G_MISS_NUM THEN
2981 p_instance_rec.po_order_line_id := NULL;
2982 l_get_instance_rec.po_order_line_id := NULL;
2983 END IF;
2984
2985 ELSIF
2986
2987 l_get_instance_rec.location_type_code = 'WIP' THEN
2988
2989 IF p_instance_rec.inv_organization_id = FND_API.G_MISS_NUM THEN
2990 p_instance_rec.inv_organization_id := NULL;
2991 l_get_instance_rec.inv_organization_id := NULL;
2992 END IF;
2993 IF p_instance_rec.inv_subinventory_name = FND_API.G_MISS_CHAR THEN
2994 p_instance_rec.inv_subinventory_name := NULL;
2995 l_get_instance_rec.inv_subinventory_name := NULL;
2996 END IF;
2997 IF p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM THEN
2998 p_instance_rec.inv_locator_id := NULL;
2999 l_get_instance_rec.inv_locator_id := NULL;
3000 END IF;
3001 IF p_instance_rec.pa_project_id = FND_API.G_MISS_NUM THEN
3002 p_instance_rec.pa_project_id := NULL;
3003 l_get_instance_rec.pa_project_id := NULL;
3004 END IF;
3005 IF p_instance_rec.pa_project_task_id = FND_API.G_MISS_NUM THEN
3006 p_instance_rec.pa_project_task_id := NULL;
3007 l_get_instance_rec.pa_project_task_id := NULL;
3008 END IF;
3009 IF p_instance_rec.in_transit_order_line_id = FND_API.G_MISS_NUM THEN
3010 p_instance_rec.in_transit_order_line_id := NULL;
3011 l_get_instance_rec.in_transit_order_line_id := NULL;
3012 END IF;
3013 IF p_instance_rec.po_order_line_id = FND_API.G_MISS_NUM THEN
3014 p_instance_rec.po_order_line_id := NULL;
3015 l_get_instance_rec.po_order_line_id := NULL;
3016 END IF;
3017
3018 ELSIF
3019
3020 l_get_instance_rec.location_type_code = 'IN_TRANSIT' THEN
3021
3022 IF p_instance_rec.inv_organization_id = FND_API.G_MISS_NUM THEN
3023 p_instance_rec.inv_organization_id := NULL;
3024 l_get_instance_rec.inv_organization_id := NULL;
3025 END IF;
3026 IF p_instance_rec.inv_subinventory_name = FND_API.G_MISS_CHAR THEN
3027 p_instance_rec.inv_subinventory_name := NULL;
3028 l_get_instance_rec.inv_subinventory_name := NULL;
3029 END IF;
3030 IF p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM THEN
3031 p_instance_rec.inv_locator_id := NULL;
3032 l_get_instance_rec.inv_locator_id := NULL;
3033 END IF;
3034 IF p_instance_rec.pa_project_id = FND_API.G_MISS_NUM THEN
3035 p_instance_rec.pa_project_id := NULL;
3036 l_get_instance_rec.pa_project_id := NULL;
3037 END IF;
3038 IF p_instance_rec.pa_project_task_id = FND_API.G_MISS_NUM THEN
3039 p_instance_rec.pa_project_task_id := NULL;
3040 l_get_instance_rec.pa_project_task_id := NULL;
3041 END IF;
3042 IF p_instance_rec.wip_job_id = FND_API.G_MISS_NUM THEN
3043 p_instance_rec.wip_job_id := NULL;
3044 l_get_instance_rec.wip_job_id := NULL;
3045 END IF;
3046 IF p_instance_rec.po_order_line_id = FND_API.G_MISS_NUM THEN
3047 p_instance_rec.po_order_line_id := NULL;
3048 l_get_instance_rec.po_order_line_id := NULL;
3049 END IF;
3050
3051 ELSIF
3052
3053 l_get_instance_rec.location_type_code = 'PO' THEN
3054
3055 IF p_instance_rec.inv_organization_id = FND_API.G_MISS_NUM THEN
3056 p_instance_rec.inv_organization_id := NULL;
3057 l_get_instance_rec.inv_organization_id := NULL;
3058 END IF;
3059 IF p_instance_rec.inv_subinventory_name = FND_API.G_MISS_CHAR THEN
3060 p_instance_rec.inv_subinventory_name := NULL;
3061 l_get_instance_rec.inv_subinventory_name := NULL;
3062 END IF;
3063 IF p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM THEN
3064 p_instance_rec.inv_locator_id := NULL;
3065 l_get_instance_rec.inv_locator_id := NULL;
3066 END IF;
3067 IF p_instance_rec.pa_project_id = FND_API.G_MISS_NUM THEN
3068 p_instance_rec.pa_project_id := NULL;
3069 l_get_instance_rec.pa_project_id := NULL;
3070 END IF;
3071 IF p_instance_rec.pa_project_task_id = FND_API.G_MISS_NUM THEN
3072 p_instance_rec.pa_project_task_id := NULL;
3073 l_get_instance_rec.pa_project_task_id := NULL;
3074 END IF;
3075 IF p_instance_rec.wip_job_id = FND_API.G_MISS_NUM THEN
3076 p_instance_rec.wip_job_id := NULL;
3077 l_get_instance_rec.wip_job_id := NULL;
3078 END IF;
3079 IF p_instance_rec.in_transit_order_line_id = FND_API.G_MISS_NUM THEN
3080 p_instance_rec.in_transit_order_line_id := NULL;
3081 l_get_instance_rec.in_transit_order_line_id := NULL;
3082 END IF;
3083
3084 ELSIF
3085
3086 l_get_instance_rec.location_type_code = 'HZ_LOCATIONS' THEN
3087
3088 IF p_instance_rec.inv_organization_id = FND_API.G_MISS_NUM THEN
3089 p_instance_rec.inv_organization_id := NULL;
3090 l_get_instance_rec.inv_organization_id := NULL;
3091 END IF;
3092 IF p_instance_rec.inv_subinventory_name = FND_API.G_MISS_CHAR THEN
3093 p_instance_rec.inv_subinventory_name := NULL;
3094 l_get_instance_rec.inv_subinventory_name := NULL;
3095 END IF;
3096 IF p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM THEN
3097 p_instance_rec.inv_locator_id := NULL;
3098 l_get_instance_rec.inv_locator_id := NULL;
3099 END IF;
3100 IF p_instance_rec.wip_job_id = FND_API.G_MISS_NUM THEN
3101 p_instance_rec.wip_job_id := NULL;
3102 l_get_instance_rec.wip_job_id := NULL;
3103 END IF;
3104 IF p_instance_rec.in_transit_order_line_id = FND_API.G_MISS_NUM THEN
3105 p_instance_rec.in_transit_order_line_id := NULL;
3106 l_get_instance_rec.in_transit_order_line_id := NULL;
3107 END IF;
3108 IF p_instance_rec.po_order_line_id = FND_API.G_MISS_NUM THEN
3109 p_instance_rec.po_order_line_id := NULL;
3110 l_get_instance_rec.po_order_line_id := NULL;
3111 END IF;
3112 /*
3113 IF p_instance_rec.pa_project_id = FND_API.G_MISS_NUM THEN
3114 p_instance_rec.pa_project_id := NULL;
3115 l_get_instance_rec.pa_project_id := NULL;
3116 END IF;
3117 IF p_instance_rec.pa_project_task_id = FND_API.G_MISS_NUM THEN
3118 p_instance_rec.pa_project_task_id := NULL;
3119 l_get_instance_rec.pa_project_task_id := NULL;
3120 END IF;
3121 */
3122 ELSIF
3123
3124 l_get_instance_rec.location_type_code = 'HZ_PARTY_SITES' THEN
3125
3126 IF p_instance_rec.inv_organization_id = FND_API.G_MISS_NUM THEN
3127 p_instance_rec.inv_organization_id := NULL;
3128 l_get_instance_rec.inv_organization_id := NULL;
3129 END IF;
3130 IF p_instance_rec.inv_subinventory_name = FND_API.G_MISS_CHAR THEN
3131 p_instance_rec.inv_subinventory_name := NULL;
3132 l_get_instance_rec.inv_subinventory_name := NULL;
3133 END IF;
3134 IF p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM THEN
3135 p_instance_rec.inv_locator_id := NULL;
3136 l_get_instance_rec.inv_locator_id := NULL;
3137 END IF;
3138 IF p_instance_rec.wip_job_id = FND_API.G_MISS_NUM THEN
3139 p_instance_rec.wip_job_id := NULL;
3140 l_get_instance_rec.wip_job_id := NULL;
3141 END IF;
3142 IF p_instance_rec.in_transit_order_line_id = FND_API.G_MISS_NUM THEN
3143 p_instance_rec.in_transit_order_line_id := NULL;
3144 l_get_instance_rec.in_transit_order_line_id := NULL;
3145 END IF;
3146 IF p_instance_rec.po_order_line_id = FND_API.G_MISS_NUM THEN
3147 p_instance_rec.po_order_line_id := NULL;
3148 l_get_instance_rec.po_order_line_id := NULL;
3149 END IF;
3150 IF p_instance_rec.pa_project_id = FND_API.G_MISS_NUM THEN
3151 p_instance_rec.pa_project_id := NULL;
3152 l_get_instance_rec.pa_project_id := NULL;
3153 END IF;
3154 IF p_instance_rec.pa_project_task_id = FND_API.G_MISS_NUM THEN
3155 p_instance_rec.pa_project_task_id := NULL;
3156 l_get_instance_rec.pa_project_task_id := NULL;
3157 END IF;
3158
3159 ELSIF
3160
3161 l_get_instance_rec.location_type_code = 'VENDOR_SITE' THEN
3162
3163 IF p_instance_rec.inv_organization_id = FND_API.G_MISS_NUM THEN
3164 p_instance_rec.inv_organization_id := NULL;
3165 l_get_instance_rec.inv_organization_id := NULL;
3166 END IF;
3167 IF p_instance_rec.inv_subinventory_name = FND_API.G_MISS_CHAR THEN
3168 p_instance_rec.inv_subinventory_name := NULL;
3169 l_get_instance_rec.inv_subinventory_name := NULL;
3170 END IF;
3171 IF p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM THEN
3172 p_instance_rec.inv_locator_id := NULL;
3173 l_get_instance_rec.inv_locator_id := NULL;
3174 END IF;
3175 IF p_instance_rec.wip_job_id = FND_API.G_MISS_NUM THEN
3176 p_instance_rec.wip_job_id := NULL;
3177 l_get_instance_rec.wip_job_id := NULL;
3178 END IF;
3179 IF p_instance_rec.in_transit_order_line_id = FND_API.G_MISS_NUM THEN
3180 p_instance_rec.in_transit_order_line_id := NULL;
3181 l_get_instance_rec.in_transit_order_line_id := NULL;
3182 END IF;
3183 IF p_instance_rec.po_order_line_id = FND_API.G_MISS_NUM THEN
3184 p_instance_rec.po_order_line_id := NULL;
3185 l_get_instance_rec.po_order_line_id := NULL;
3186 END IF;
3187 IF p_instance_rec.pa_project_id = FND_API.G_MISS_NUM THEN
3188 p_instance_rec.pa_project_id := NULL;
3189 l_get_instance_rec.pa_project_id := NULL;
3190 END IF;
3191 IF p_instance_rec.pa_project_task_id = FND_API.G_MISS_NUM THEN
3192 p_instance_rec.pa_project_task_id := NULL;
3193 l_get_instance_rec.pa_project_task_id := NULL;
3194 END IF;
3195
3196 ELSIF
3197
3198 l_get_instance_rec.location_type_code = 'INTERNAL_SITE' THEN
3199
3200 IF p_instance_rec.inv_organization_id = FND_API.G_MISS_NUM THEN
3201 p_instance_rec.inv_organization_id := NULL;
3202 l_get_instance_rec.inv_organization_id := NULL;
3203 END IF;
3204 IF p_instance_rec.inv_subinventory_name = FND_API.G_MISS_CHAR THEN
3205 p_instance_rec.inv_subinventory_name := NULL;
3206 l_get_instance_rec.inv_subinventory_name := NULL;
3207 END IF;
3208 IF p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM THEN
3209 p_instance_rec.inv_locator_id := NULL;
3210 l_get_instance_rec.inv_locator_id := NULL;
3211 END IF;
3212 IF p_instance_rec.wip_job_id = FND_API.G_MISS_NUM THEN
3213 p_instance_rec.wip_job_id := NULL;
3214 l_get_instance_rec.wip_job_id := NULL;
3215 END IF;
3216 IF p_instance_rec.in_transit_order_line_id = FND_API.G_MISS_NUM THEN
3217 p_instance_rec.in_transit_order_line_id := NULL;
3218 l_get_instance_rec.in_transit_order_line_id := NULL;
3219 END IF;
3220 IF p_instance_rec.po_order_line_id = FND_API.G_MISS_NUM THEN
3221 p_instance_rec.po_order_line_id := NULL;
3222 l_get_instance_rec.po_order_line_id := NULL;
3223 END IF;
3224 IF p_instance_rec.pa_project_id = FND_API.G_MISS_NUM THEN
3225 p_instance_rec.pa_project_id := NULL;
3226 l_get_instance_rec.pa_project_id := NULL;
3227 END IF;
3228 IF p_instance_rec.pa_project_task_id = FND_API.G_MISS_NUM THEN
3229 p_instance_rec.pa_project_task_id := NULL;
3230 l_get_instance_rec.pa_project_task_id := NULL;
3231 END IF;
3232
3233 END IF;
3234 END IF; -- Any location attribute changing
3235
3236 END get_merge_rec;
3237
3238 /*----------------------------------------------------------*/
3239 /* Function Name : Get_instance_id */
3240 /* */
3241 /* Description : This function generates */
3242 /* instance_ids using a sequence */
3243 /*----------------------------------------------------------*/
3244 FUNCTION Get_instance_id
3245 ( p_stack_err_msg IN BOOLEAN
3246 )
3247 RETURN NUMBER
3248 IS
3249 l_instance_id NUMBER;
3250 BEGIN
3251 SELECT csi_item_instances_s.NEXTVAL
3252 INTO l_instance_id
3253 FROM sys.dual;
3254 RETURN l_instance_id;
3255 END Get_instance_id;
3256
3257 /*----------------------------------------------------------*/
3258 /* Function Name : get_cis_item_instance_h_id */
3259 /* */
3260 /* Description : This function generates */
3261 /* cis_item_instance_h_id using a sequence */
3262 /*----------------------------------------------------------*/
3263
3264 FUNCTION get_csi_item_instance_h_id
3265 ( p_stack_err_msg IN BOOLEAN
3266 )
3267 RETURN NUMBER
3268 IS
3269 l_csi_item_instance_h_id NUMBER;
3270
3271 BEGIN
3272 SELECT csi_item_instances_h_s.NEXTVAL
3273 INTO l_csi_item_instance_h_id
3274 FROM dual;
3275 RETURN l_csi_item_instance_h_id;
3276 EXCEPTION
3277 WHEN OTHERS THEN
3278 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ITEM_INST_H_ID');
3279 FND_MESSAGE.SET_TOKEN('INSTANCE_HISTORY_ID',l_csi_item_instance_h_id);
3280 FND_MSG_PUB.Add;
3281
3282 END get_csi_item_instance_h_id;
3283
3284 /*-----------------------------------------------------------*/
3285 /* Procedure name: Is_Instance_creation_complete */
3286 /* Description : Check if the instance creation is */
3287 /* complete */
3288 /*-----------------------------------------------------------*/
3289
3290 FUNCTION Is_Inst_creation_complete
3291 ( p_instance_id IN NUMBER,
3292 p_stack_err_msg IN BOOLEAN
3293 ) RETURN BOOLEAN IS
3294
3295 l_dummy VARCHAR2(1);
3296 l_return_value BOOLEAN := TRUE;
3297 BEGIN
3298 SELECT 'x'
3299 INTO l_dummy
3300 FROM csi_item_instances
3301 WHERE instance_id = p_Instance_id
3302 AND creation_complete_flag = 'Y';
3303 RETURN l_return_value;
3304 EXCEPTION
3305 WHEN NO_DATA_FOUND THEN
3306 l_return_value := FALSE;
3307 RETURN l_return_value;
3308 END Is_Inst_creation_complete;
3309
3310 /*-----------------------------------------------------------*/
3311 /* Procedure name: Instance_has_Parent */
3312 /* Description : Check for the parent in csi relationships */
3313 /* */
3314 /*-----------------------------------------------------------*/
3315
3316 FUNCTION Instance_has_Parent
3317 ( p_instance_id IN NUMBER,
3318 p_stack_err_msg IN BOOLEAN
3319 ) RETURN BOOLEAN IS
3320
3321 l_dummy NUMBER;
3322 l_return_value BOOLEAN := TRUE;
3323
3324 BEGIN
3325
3326 BEGIN
3327 SELECT object_id
3328 INTO l_dummy
3329 FROM csi_ii_relationships
3330 WHERE subject_id = p_instance_id
3331 AND relationship_type_code = 'COMPONENT-OF'
3332 AND nvl(active_end_date,(sysdate+1)) > sysdate;
3333
3334 l_return_value := TRUE;
3335
3336 EXCEPTION
3337 WHEN NO_DATA_FOUND THEN
3338 l_return_value := FALSE;
3339 END;
3340 RETURN l_return_value;
3341
3342 END Instance_has_Parent;
3343
3344 /*------------------------------------------------------------*/
3345 /* This procedure verifies that the item serial number is */
3346 /* valid by looking into the mtl serial #s table */
3347 /*------------------------------------------------------------*/
3348
3349 PROCEDURE Validate_srl_num_for_Inst_Upd
3350 (
3351 p_inv_org_id IN NUMBER,
3352 p_inv_item_id IN NUMBER,
3353 p_serial_number IN VARCHAR2,
3354 p_mfg_serial_number_flag IN VARCHAR2,
3355 p_txn_rec IN csi_datastructures_pub.transaction_rec,
3356 p_location_type_code IN VARCHAR2, -- Added by sk on 09/13/01
3357 p_srl_control_code IN NUMBER,
3358 p_instance_usage_code IN VARCHAR2,
3359 p_instance_id IN NUMBER,
3360 l_return_value IN OUT NOCOPY BOOLEAN
3361 ) IS
3362 l_dummy varchar2(30);
3363 l_temp varchar2(30);
3364 p_stack_err_msg BOOLEAN DEFAULT TRUE;
3365
3366 -- If item is under serial control, then serial number MUST be a non-NULL
3367 -- value. If it is not under serial_control, then serial number MUST be NULL
3368 --
3369 CURSOR c1 is
3370 SELECT serial_number_control_code
3371 FROM mtl_system_items
3372 WHERE inventory_item_id = p_inv_item_id
3373 AND organization_id = p_inv_org_id
3374 AND enabled_flag = 'Y'
3375 AND nvl (start_date_active, sysdate) <= sysdate
3376 AND nvl (end_date_active, sysdate+1) > sysdate;
3377
3378 Serialized NUMBER;
3379 l_found VARCHAR2(1);
3380 BEGIN
3381 l_return_value := TRUE;
3382 --
3383 IF p_srl_control_code is not NULL AND
3384 p_srl_control_code <> FND_API.G_MISS_NUM THEN
3385 Serialized := p_srl_control_code;
3386 ELSE
3387 OPEN c1;
3388 FETCH c1 into serialized;
3389 CLOSE c1;
3390 END IF;
3391 --
3392 IF Serialized is not null THEN
3393 -- Item is under serial control but serial_number is NULL
3394 -- '1' stands for - No serial number control
3395 -- '2' stands for - Predefined serial numbers
3396 -- '5' stands for - Dynamic entry at inventory receipt
3397 -- '6' stands for - Dynamic entry at sales order issue
3398 --IF NVL(serialized,0) IN (2,5,6) THEN
3399 IF Is_treated_serialized( p_serial_control_code => serialized
3400 ,p_location_type_code => p_location_type_code
3401 ,p_transaction_type_id => p_txn_rec.transaction_type_id
3402 )
3403 THEN
3404 IF ((p_serial_number IS NULL) OR
3405 (p_serial_number = FND_API.G_MISS_CHAR)) THEN
3406 l_return_value := FALSE;
3407 IF (p_stack_err_msg = TRUE) THEN
3408 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_SERIAL_NUM');
3409 FND_MESSAGE.SET_TOKEN('SERIAL_NUMBER',p_serial_number);
3410 FND_MSG_PUB.Add;
3411 END IF;
3412 ELSE
3413 l_return_value := TRUE;
3414 END IF;
3415 ELSE
3416 -- Item is not under serial control but serial_number is not NULL
3417 --IF NVL(serialized,0) NOT IN (2,5,6) THEN
3418 IF NOT Is_treated_serialized( p_serial_control_code => serialized
3419 ,p_location_type_code => p_location_type_code
3420 ,p_transaction_type_id => p_txn_rec.transaction_type_id
3421 )
3422 THEN
3423 IF ((p_serial_number IS NOT NULL) AND (p_serial_number <> FND_API.G_MISS_CHAR)) THEN
3424 l_found := NULL;
3425 IF serialized IS NOT NULL
3426 AND serialized=6
3427 AND p_instance_usage_code='RETURNED'
3428 AND p_location_type_code='INVENTORY'
3429 THEN
3430 BEGIN
3431 SELECT 'x'
3432 INTO l_found
3433 FROM mtl_serial_numbers
3434 WHERE inventory_item_id = p_inv_item_id
3435 AND serial_number = p_serial_number;
3436 l_return_value := TRUE;
3437 EXCEPTION
3438 WHEN OTHERS THEN
3439 NULL;
3440 END;
3441 -- Need to by-pass validation if the instance is in a configuration
3442 ELSIF serialized IS NOT NULL -- Fix for Bug # 3431641
3443 AND serialized=6
3444 AND p_instance_usage_code='IN_RELATIONSHIP'
3445 THEN
3446 l_found := 'x';
3447 l_return_value := TRUE;
3448 END IF;
3449 IF l_found IS NULL
3450 THEN
3451 l_return_value := FALSE;
3452 FND_MESSAGE.SET_NAME('CSI','CSI_API_NOT_SER_CONTROLLED');
3453 FND_MESSAGE.SET_TOKEN('SERIAL_NUMBER',p_serial_number);
3454 FND_MSG_PUB.Add;
3455 END IF;
3456 ELSE
3457 l_return_value := TRUE;
3458 END IF;
3459 END IF;
3460 END IF;
3461 ELSE
3462 l_return_value := FALSE;
3463 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ITEM'); -- Item does not exist in the inventory organization provided
3464 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_inv_item_id);
3465 FND_MESSAGE.SET_TOKEN('INVENTORY_ORGANIZATION_ID',p_inv_org_id);
3466 FND_MSG_PUB.Add;
3467 END IF;
3468 --
3469 IF l_return_value = TRUE
3470 THEN
3471 Validate_ser_uniqueness
3472 ( p_inv_org_id => p_inv_org_id
3473 ,p_inv_item_id => p_inv_item_id
3474 ,p_serial_number => p_serial_number
3475 ,p_instance_id => p_instance_id
3476 ,l_return_value => l_return_value
3477 );
3478 IF l_return_value = FALSE THEN
3479 fnd_message.set_name('CSI','CSI_FAIL_UNIQUENESS');
3480 fnd_msg_pub.add;
3481 END IF;
3482 END IF;
3483 END Validate_srl_num_for_Inst_Upd;
3484
3485 /*------------------------------------------------------------*/
3486 /* This function validates the quantity and also check for */
3487 /* serialized items, quantity =1 */
3488 /*------------------------------------------------------------*/
3489 /*
3490 FUNCTION Update_Quantity
3491 (
3492 p_instance_id IN NUMBER ,
3493 p_inv_organization_id IN NUMBER ,
3494 p_quantity IN NUMBER ,
3495 --p_serial_number IN VARCHAR2,
3496 p_serial_control_code IN NUMBER ,
3497 p_location_type_code IN VARCHAR2,
3498 p_stack_err_msg IN BOOLEAN
3499 )
3500 RETURN BOOLEAN IS
3501
3502 l_quantity NUMBER;
3503 l_dummy NUMBER;
3504 l_return_value BOOLEAN := TRUE;
3505
3506 Cursor c1 is
3507 SELECT negative_inv_receipt_code
3508 FROM mtl_parameters
3509 WHERE organization_id = p_inv_organization_id;
3510 BEGIN
3511 -- IF ((p_serial_number IS NOT NULL) AND (p_serial_number <> FND_API.G_MISS_CHAR)) THEN
3512 IF (csi_Item_Instance_Vld_pvt.Is_treated_serialized
3513 ( p_serial_control_code => p_serial_control_code
3514 ,p_location_type_code => p_location_type_code
3515 )) --Added by sk on 09/14/01
3516 THEN
3517 IF p_quantity <> 1 THEN
3518 l_return_value := FALSE;
3519 IF (p_stack_err_msg = TRUE) THEN
3520 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_QUANTITY');
3521 FND_MESSAGE.SET_TOKEN('QUANTITY',p_quantity);
3522 FND_MSG_PUB.Add;
3523 END IF;
3524 END IF;
3525 ELSE
3526 IF p_quantity < 0 THEN
3527 OPEN C1;
3528 FETCH C1 INTO l_dummy;
3529 IF C1%found THEN
3530 IF nvl(l_dummy,0) = 1 THEN
3531 l_return_value := TRUE;
3532 ELSE
3533 l_return_value := FALSE;
3534 IF (p_stack_err_msg = TRUE) THEN
3535 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_NEGATIVE_QTY');
3536 FND_MESSAGE.SET_TOKEN('QUANTITY',p_quantity);
3537 FND_MSG_PUB.Add;
3538 END IF;
3539 END IF;
3540 END IF;
3541 CLOSE C1;
3542 ELSIF p_quantity > 1 THEN
3543 BEGIN
3544 SELECT subject_id
3545 INTO l_dummy
3546 FROM csi_ii_relationships
3547 WHERE object_id = p_instance_id;
3548 IF SQL%FOUND THEN
3549 l_return_value := FALSE;
3550 IF (p_stack_err_msg = TRUE) THEN
3551 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REL_QTY');
3552 FND_MESSAGE.SET_TOKEN('QUANTITY',p_quantity);
3553 FND_MSG_PUB.Add;
3554 END IF;
3555 END IF;
3556 EXCEPTION
3557 WHEN NO_DATA_FOUND THEN
3558 l_return_value := TRUE;
3559 WHEN TOO_MANY_ROWS THEN
3560 l_return_value := FALSE;
3561 IF (p_stack_err_msg = TRUE) THEN
3562 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_QUANTITY');--check with faisal
3563 FND_MESSAGE.SET_TOKEN('QUANTITY',p_quantity);
3564 FND_MSG_PUB.Add;
3565 END IF;
3566 END;
3567 END IF;
3568 END IF;
3569 RETURN l_return_value;
3570 END Update_Quantity;
3571 */
3572 /*----------------------------------------------------*/
3573 /* This Procedure validates the accounting class code*/
3574 /* */
3575 /*----------------------------------------------------*/
3576
3577 PROCEDURE get_valid_acct_class
3578 ( p_instance_id IN NUMBER
3579 ,p_curr_acct_class_code IN VARCHAR2
3580 ,p_loc_type_code IN VARCHAR2
3581 ,x_acct_class_code OUT NOCOPY VARCHAR2
3582 )
3583 IS
3584 l_int_party_id NUMBER ;
3585 l_owner NUMBER := -1 ;
3586 l_count NUMBER := 0 ;
3587
3588 BEGIN
3589 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
3590 csi_gen_utility_pvt.populate_install_param_rec;
3591 END IF;
3592 --
3593 l_int_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
3594 --
3595 BEGIN
3596 SELECT party_id
3597 INTO l_owner
3598 FROM csi_i_parties
3599 WHERE instance_id = p_instance_id
3600 AND relationship_type_code ='OWNER'
3601 AND (active_end_date >SYSDATE OR active_end_date IS NULL );
3602 EXCEPTION
3603 WHEN OTHERS THEN
3604 NULL;
3605 END;
3606
3607 BEGIN
3608 SELECT count(*)
3609 INTO l_count
3610 FROM csi_i_assets
3611 WHERE instance_id = p_instance_id
3612 AND (active_end_date >SYSDATE OR active_end_date IS NULL );
3613 EXCEPTION
3614 WHEN OTHERS THEN
3615 NULL;
3616 END;
3617
3618 IF l_owner = l_int_party_id THEN
3619
3620 IF l_count > 0 THEN
3621 x_acct_class_code := 'ASSET';
3622 ELSIF p_loc_type_code = 'WIP' THEN
3623 x_acct_class_code := 'WIP';
3624 ELSIF p_loc_type_code = 'PROJECT' THEN
3625 x_acct_class_code := 'PROJECT';
3626 ELSE
3627 x_acct_class_code := 'INV';
3628 END IF;
3629 ELSE
3630 IF ((p_curr_acct_class_code IS NULL) OR
3631 (p_curr_acct_class_code = FND_API.G_MISS_CHAR)) THEN
3632 x_acct_class_code := 'CUST_PROD';
3633 ELSE
3634 IF (p_curr_acct_class_code = 'WIP') OR
3635 (p_curr_acct_class_code = 'PROJECT') THEN
3636 x_acct_class_code := 'CUST_PROD';
3637 ELSE
3638 x_acct_class_code := p_curr_acct_class_code;
3639 END IF;
3640 END IF;
3641
3642 END IF;
3643 END;
3644
3645 /*-----------------------------------------------------------*/
3646 /* Procedure name: Is_InstanceID_Valid */
3647 /* Description : Check if the instance_id */
3648 /* exists in csi_item_instances */
3649 /*-----------------------------------------------------------*/
3650
3651 FUNCTION Is_InstanceID_Valid
3652 (
3653 p_instance_id IN NUMBER,
3654 p_stack_err_msg IN BOOLEAN
3655 ) RETURN BOOLEAN IS
3656
3657 l_instance_id NUMBER;
3658 l_return_value BOOLEAN := TRUE;
3659
3660 BEGIN
3661
3662 BEGIN
3663 SELECT instance_id
3664 INTO l_instance_id
3665 FROM csi_item_instances
3666 WHERE instance_id = p_instance_id;
3667 l_return_value := TRUE;
3668 EXCEPTION
3669 WHEN NO_DATA_FOUND THEN
3670 l_return_value := FALSE;
3671 IF ( p_stack_err_msg = TRUE ) THEN
3672 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_ID');
3673 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
3674 FND_MSG_PUB.Add;
3675 END IF;
3676 END;
3677 RETURN l_return_value;
3678 END Is_InstanceID_Valid;
3679
3680 /*-----------------------------------------------------------*/
3681 /* Function name: EndDate_Valid */
3682 /* Description : Check if item instance active end */
3683 /* date is valid */
3684 /*-----------------------------------------------------------*/
3685
3686 FUNCTION EndDate_Valid
3687 (
3688 p_start_date IN DATE,
3689 p_end_date IN DATE,
3690 p_instance_id IN NUMBER,
3691 p_stack_err_msg IN BOOLEAN
3692 ) RETURN BOOLEAN IS
3693
3694 l_instance_end_date DATE;
3695 l_instance_start_date DATE;
3696 l_return_value BOOLEAN := TRUE;
3697 l_temp VARCHAR2(1);
3698 l_txn_date DATE;
3699
3700 CURSOR c1 IS
3701 SELECT active_end_date,
3702 active_start_date
3703 FROM csi_item_instances
3704 WHERE instance_id = p_instance_id;
3705
3706 BEGIN
3707 IF ((p_end_date is NOT NULL) AND (p_end_date <> FND_API.G_MISS_DATE)) THEN
3708 OPEN c1;
3709 FETCH c1 INTO l_instance_end_date ,l_instance_start_date;
3710 IF trunc(p_end_date) < trunc(l_instance_start_date) THEN
3711 l_return_value := FALSE;
3712 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_END_DATE');
3713 FND_MESSAGE.SET_TOKEN('END_DATE_ACTIVE',to_char(p_end_date,'DD-MON-YYYY HH24:MI:SS'));
3714 FND_MSG_PUB.Add;
3715 RETURN l_return_value;
3716 csi_gen_utility_pvt.put_line('value of end date in ITEM_VLD_PVT before check for p_end_date < sysdate:'||to_char(p_end_date, 'DD-MON-YYYY HH24:MI:SS'));
3717 csi_gen_utility_pvt.put_line('value of sysdate:'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3718 ELSIF p_end_date < sysdate THEN -- srramakr
3719 BEGIN
3720 SELECT MAX(source_transaction_date)
3721 INTO l_txn_date
3722 FROM csi_inst_transactions_v
3723 WHERE instance_id=p_instance_id
3724 AND source_transaction_date>p_end_date;
3725 csi_gen_utility_pvt.put_line('value of end date in ITEM_VLD_PVT after check for p_end_date < sysdate:'||to_char(p_end_date, 'DD-MON-YYYY HH24:MI:SS'));
3726 csi_gen_utility_pvt.put_line('value of MAX(transaction_date) in ITEM_VLD_PVT:'||to_char(l_txn_date, 'DD-MON-YYYY HH24:MI:SS'));
3727 csi_gen_utility_pvt.put_line('value of instance_id in ITEM_VLD_PVT:'||p_instance_id);
3728 EXCEPTION
3729 WHEN NO_DATA_FOUND THEN
3730 l_return_value := TRUE;
3731 RETURN l_return_value;
3732 END;
3733
3734 IF l_txn_date IS NOT NULL
3735 THEN
3736 csi_gen_utility_pvt.put_line('value of end date in ITEM_VLD_PVT if there are any txns:'||to_char(p_end_date, 'DD-MON-YYYY HH24:MI:SS'));
3737 csi_gen_utility_pvt.put_line('value of MAX(source_transaction_date) in ITEM_VLD_PVT if there are any txns:'||to_char(l_txn_date, 'DD-MON-YYYY HH24:MI:SS'));
3738 csi_gen_utility_pvt.put_line('value of instance_id in ITEM_VLD_PVT, if there are any txns:'||p_instance_id);
3739 l_return_value := FALSE;
3740 FND_MESSAGE.Set_Name('CSI', 'CSI_PARENT_HAS_TXN');
3741 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id );
3742 FND_MESSAGE.SET_TOKEN('TXN_DATE',to_char(l_txn_date, 'DD-MON-YYYY HH24:MI:SS'));
3743 FND_MSG_PUB.ADD;
3744 RETURN l_return_value;
3745 END IF;
3746 END IF;
3747 CLOSE c1;
3748 END IF;
3749 RETURN l_return_value;
3750 END EndDate_Valid;
3751
3752 /*-----------------------------------------------------*/
3753 /* Validates the item instance ID */
3754 /* Used exclusively by copy item instance */
3755 /*-----------------------------------------------------*/
3756
3757 FUNCTION Val_and_get_inst_rec
3758 ( p_item_instance_id IN NUMBER,
3759 p_instance_rec OUT NOCOPY csi_datastructures_pub.instance_rec,
3760 p_stack_err_msg IN BOOLEAN
3761 ) RETURN BOOLEAN IS
3762 BEGIN
3763 SELECT
3764 INSTANCE_ID ,
3765 INSTANCE_NUMBER ,
3766 EXTERNAL_REFERENCE ,
3767 LAST_VLD_ORGANIZATION_ID ,
3768 INVENTORY_ITEM_ID ,
3769 INVENTORY_REVISION ,
3770 INV_MASTER_ORGANIZATION_ID ,
3771 SERIAL_NUMBER ,
3772 MFG_SERIAL_NUMBER_FLAG ,
3773 LOT_NUMBER ,
3774 QUANTITY ,
3775 UNIT_OF_MEASURE ,
3776 ACCOUNTING_CLASS_CODE ,
3777 INSTANCE_CONDITION_ID ,
3778 INSTANCE_STATUS_ID ,
3779 CUSTOMER_VIEW_FLAG ,
3780 MERCHANT_VIEW_FLAG ,
3781 SELLABLE_FLAG ,
3782 SYSTEM_ID ,
3783 INSTANCE_TYPE_CODE ,
3784 ACTIVE_START_DATE ,
3785 ACTIVE_END_DATE ,
3786 LOCATION_TYPE_CODE ,
3787 LOCATION_ID ,
3788 INV_ORGANIZATION_ID ,
3789 INV_SUBINVENTORY_NAME ,
3790 INV_LOCATOR_ID ,
3791 PA_PROJECT_ID ,
3792 PA_PROJECT_TASK_ID ,
3793 IN_TRANSIT_ORDER_LINE_ID ,
3794 WIP_JOB_ID ,
3795 PO_ORDER_LINE_ID ,
3796 LAST_OE_ORDER_LINE_ID ,
3797 LAST_OE_RMA_LINE_ID ,
3798 LAST_PO_PO_LINE_ID ,
3799 LAST_OE_PO_NUMBER ,
3800 LAST_WIP_JOB_ID ,
3801 LAST_PA_PROJECT_ID ,
3802 LAST_PA_TASK_ID ,
3803 LAST_OE_AGREEMENT_ID ,
3804 INSTALL_DATE ,
3805 MANUALLY_CREATED_FLAG ,
3806 RETURN_BY_DATE ,
3807 ACTUAL_RETURN_DATE ,
3808 CREATION_COMPLETE_FLAG ,
3809 COMPLETENESS_FLAG ,
3810 CONTEXT ,
3811 ATTRIBUTE1 ,
3812 ATTRIBUTE2 ,
3813 ATTRIBUTE3 ,
3814 ATTRIBUTE4 ,
3815 ATTRIBUTE5 ,
3816 ATTRIBUTE6 ,
3817 ATTRIBUTE7 ,
3818 ATTRIBUTE8 ,
3819 ATTRIBUTE9 ,
3820 ATTRIBUTE10 ,
3821 ATTRIBUTE11 ,
3822 ATTRIBUTE12 ,
3823 ATTRIBUTE13 ,
3824 ATTRIBUTE14 ,
3825 ATTRIBUTE15 ,
3826 OBJECT_VERSION_NUMBER ,
3827 instance_usage_code , --Added for bug 2163942
3828 install_location_type_code ,
3829 install_location_id
3830 INTO
3831 p_instance_rec.INSTANCE_ID ,
3832 p_instance_rec.INSTANCE_NUMBER ,
3833 p_instance_rec.EXTERNAL_REFERENCE ,
3834 p_instance_rec.VLD_ORGANIZATION_ID ,
3835 p_instance_rec.INVENTORY_ITEM_ID ,
3836 p_instance_rec.INVENTORY_REVISION ,
3837 p_instance_rec.INV_MASTER_ORGANIZATION_ID ,
3838 p_instance_rec.SERIAL_NUMBER ,
3839 p_instance_rec.MFG_SERIAL_NUMBER_FLAG ,
3840 p_instance_rec.LOT_NUMBER ,
3841 p_instance_rec.QUANTITY ,
3842 p_instance_rec.UNIT_OF_MEASURE ,
3843 p_instance_rec.ACCOUNTING_CLASS_CODE ,
3844 p_instance_rec.INSTANCE_CONDITION_ID ,
3845 p_instance_rec.INSTANCE_STATUS_ID ,
3846 p_instance_rec.CUSTOMER_VIEW_FLAG ,
3847 p_instance_rec.MERCHANT_VIEW_FLAG ,
3848 p_instance_rec.SELLABLE_FLAG ,
3849 p_instance_rec.SYSTEM_ID ,
3850 p_instance_rec.INSTANCE_TYPE_CODE ,
3851 p_instance_rec.ACTIVE_START_DATE ,
3852 p_instance_rec.ACTIVE_END_DATE ,
3853 p_instance_rec.LOCATION_TYPE_CODE ,
3854 p_instance_rec.LOCATION_ID ,
3855 p_instance_rec.INV_ORGANIZATION_ID ,
3856 p_instance_rec.INV_SUBINVENTORY_NAME ,
3857 p_instance_rec.INV_LOCATOR_ID ,
3858 p_instance_rec.PA_PROJECT_ID ,
3859 p_instance_rec.PA_PROJECT_TASK_ID ,
3860 p_instance_rec.IN_TRANSIT_ORDER_LINE_ID ,
3861 p_instance_rec.WIP_JOB_ID ,
3862 p_instance_rec.PO_ORDER_LINE_ID ,
3863 p_instance_rec.LAST_OE_ORDER_LINE_ID ,
3864 p_instance_rec.LAST_OE_RMA_LINE_ID ,
3865 p_instance_rec.LAST_PO_PO_LINE_ID ,
3866 p_instance_rec.LAST_OE_PO_NUMBER ,
3867 p_instance_rec.LAST_WIP_JOB_ID ,
3868 p_instance_rec.LAST_PA_PROJECT_ID ,
3869 p_instance_rec.LAST_PA_TASK_ID ,
3870 p_instance_rec.LAST_OE_AGREEMENT_ID ,
3871 p_instance_rec.INSTALL_DATE ,
3872 p_instance_rec.MANUALLY_CREATED_FLAG ,
3873 p_instance_rec.RETURN_BY_DATE ,
3874 p_instance_rec.ACTUAL_RETURN_DATE ,
3875 p_instance_rec.CREATION_COMPLETE_FLAG ,
3876 p_instance_rec.COMPLETENESS_FLAG ,
3877 p_instance_rec.CONTEXT ,
3878 p_instance_rec.ATTRIBUTE1 ,
3879 p_instance_rec.ATTRIBUTE2 ,
3880 p_instance_rec.ATTRIBUTE3 ,
3881 p_instance_rec.ATTRIBUTE4 ,
3882 p_instance_rec.ATTRIBUTE5 ,
3883 p_instance_rec.ATTRIBUTE6 ,
3884 p_instance_rec.ATTRIBUTE7 ,
3885 p_instance_rec.ATTRIBUTE8 ,
3886 p_instance_rec.ATTRIBUTE9 ,
3887 p_instance_rec.ATTRIBUTE10 ,
3888 p_instance_rec.ATTRIBUTE11 ,
3889 p_instance_rec.ATTRIBUTE12 ,
3890 p_instance_rec.ATTRIBUTE13 ,
3891 p_instance_rec.ATTRIBUTE14 ,
3892 p_instance_rec.ATTRIBUTE15 ,
3893 p_instance_rec.OBJECT_VERSION_NUMBER ,
3894 p_instance_rec.instance_usage_code ,--Added for bug 2163942
3895 p_instance_rec.install_location_type_code ,
3896 p_instance_rec.install_location_id
3897 FROM csi_item_instances
3898 WHERE instance_id = p_item_instance_id;
3899
3900 RETURN TRUE;
3901
3902
3903 EXCEPTION
3904 WHEN NO_DATA_FOUND THEN
3905 IF ( p_stack_err_msg = TRUE ) THEN
3906 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE');
3907 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_item_instance_id);
3908 FND_MSG_PUB.Add;
3909 END IF;
3910 RETURN FALSE;
3911
3912 END Val_and_get_inst_rec;
3913
3914 /*-----------------------------------------------------*/
3915 /* Function : To get extended attrib level */
3916 /* Used exclusively by copy item instance */
3917 /*-----------------------------------------------------*/
3918
3919 FUNCTION get_ext_attrib_level
3920 ( p_ATTRIBUTE_ID IN NUMBER,
3921 p_ATTRIBUTE_LEVEL OUT NOCOPY VARCHAR2,
3922 p_stack_err_msg IN BOOLEAN
3923 ) RETURN BOOLEAN IS
3924
3925 BEGIN
3926
3927 SELECT attribute_level
3928 INTO p_ATTRIBUTE_LEVEL
3929 FROM csi_i_extended_attribs
3930 WHERE attribute_id = p_ATTRIBUTE_ID;
3931 RETURN TRUE;
3932 EXCEPTION
3933 WHEN NO_DATA_FOUND THEN
3934 IF ( p_stack_err_msg = TRUE ) THEN
3935 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATTRIBUTE_ID');
3936 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_ID',p_ATTRIBUTE_ID);
3937 FND_MSG_PUB.Add;
3938 END IF;
3939 RETURN FALSE;
3940
3941
3942 END get_ext_attrib_level;
3943
3944 /*-----------------------------------------------------*/
3945 /* Function : val_item_org */
3946 /* To validate item and org */
3947 /*-----------------------------------------------------*/
3948
3949 FUNCTION val_item_org
3950 ( p_INVENTORY_ITEM_ID IN NUMBER,
3951 p_ORGANIZATION_ID IN VARCHAR2,
3952 p_stack_err_msg IN BOOLEAN
3953 ) RETURN BOOLEAN IS
3954
3955 l_dummy VARCHAR2(2);
3956
3957 BEGIN
3958 IF ((p_ORGANIZATION_ID = FND_API.G_MISS_NUM)
3959 OR (p_ORGANIZATION_ID IS NULL)
3960 OR (p_INVENTORY_ITEM_ID = FND_API.G_MISS_NUM)
3961 OR (p_INVENTORY_ITEM_ID IS NULL))
3962 THEN
3963 IF ( p_stack_err_msg = TRUE ) THEN
3964 FND_MESSAGE.SET_NAME('CSI','CSI_API_NULL_ITEM_ORG');
3965 FND_MESSAGE.SET_TOKEN('ITEM_ORG_ID',p_INVENTORY_ITEM_ID||' ' ||p_ORGANIZATION_ID);
3966 FND_MSG_PUB.Add;
3967 END IF;
3968 RETURN FALSE;
3969 END IF;
3970
3971
3972 SELECT '1'
3973 INTO l_dummy
3974 FROM mtl_system_items
3975 WHERE inventory_item_id = p_INVENTORY_ITEM_ID
3976 AND organization_id = p_ORGANIZATION_ID;
3977 RETURN TRUE;
3978 EXCEPTION
3979 WHEN NO_DATA_FOUND THEN
3980 IF ( p_stack_err_msg = TRUE ) THEN
3981 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ITEM_ORG');
3982 FND_MESSAGE.SET_TOKEN('ITEM_ORG_ID',p_INVENTORY_ITEM_ID||' ' ||p_ORGANIZATION_ID);
3983 FND_MSG_PUB.Add;
3984 END IF;
3985 RETURN FALSE;
3986
3987 END val_item_org;
3988
3989 /*-----------------------------------------------------*/
3990 /* Function : val_bom_org */
3991 /* To validate bom and org */
3992 /*-----------------------------------------------------*/
3993
3994 FUNCTION val_bom_org
3995 ( p_INVENTORY_ITEM_ID IN NUMBER,
3996 p_ORGANIZATION_ID IN VARCHAR2,
3997 p_stack_err_msg IN BOOLEAN
3998 ) RETURN BOOLEAN IS
3999
4000 l_dummy VARCHAR2(2);
4001
4002 BEGIN
4003 IF ((p_ORGANIZATION_ID = FND_API.G_MISS_NUM)
4004 OR (p_ORGANIZATION_ID IS NULL)
4005 OR (p_INVENTORY_ITEM_ID = FND_API.G_MISS_NUM)
4006 OR (p_INVENTORY_ITEM_ID IS NULL))
4007 THEN
4008 IF ( p_stack_err_msg = TRUE ) THEN
4009 FND_MESSAGE.SET_NAME('CSI','CSI_API_NULL_ITEM_ORG');
4010 FND_MESSAGE.SET_TOKEN('ITEM_ORG_ID',p_INVENTORY_ITEM_ID||' ' ||p_ORGANIZATION_ID);
4011 FND_MSG_PUB.Add;
4012 END IF;
4013 RETURN FALSE;
4014 END IF;
4015
4016
4017 SELECT '1'
4018 INTO l_dummy
4019 FROM bom_bill_of_materials
4020 WHERE assembly_item_id = p_INVENTORY_ITEM_ID
4021 AND organization_id = p_ORGANIZATION_ID
4022 AND alternate_bom_designator IS NULL; -- srramakr
4023 RETURN TRUE;
4024 EXCEPTION
4025 WHEN NO_DATA_FOUND THEN
4026 IF ( p_stack_err_msg = TRUE ) THEN
4027 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_BOM_ORG');
4028 FND_MESSAGE.SET_TOKEN('BOM_ORG_ID',p_INVENTORY_ITEM_ID||' ' ||p_ORGANIZATION_ID);
4029 FND_MSG_PUB.Add;
4030 END IF;
4031 RETURN FALSE;
4032
4033 END val_bom_org;
4034
4035 /*-----------------------------------------------------*/
4036 /* Function : val_inst_ter_flag */
4037 /* To validate instances with statuses */
4038 /* having termination_flag set to 'Y' */
4039 /* has a end_date */
4040 /*-----------------------------------------------------*/
4041
4042 FUNCTION val_inst_ter_flag
4043 ( p_status_id IN NUMBER,
4044 p_stack_err_msg IN BOOLEAN
4045 ) RETURN BOOLEAN IS
4046
4047 l_dummy VARCHAR2(2);
4048
4049 BEGIN
4050
4051 SELECT '1'
4052 INTO l_dummy
4053 FROM csi_instance_statuses
4054 WHERE instance_status_id = p_status_id
4055 AND terminated_flag = 'Y';
4056 RETURN TRUE;
4057
4058 EXCEPTION
4059 WHEN NO_DATA_FOUND THEN
4060 RETURN FALSE;
4061
4062 END val_inst_ter_flag;
4063
4064 /*-----------------------------------------------------*/
4065 /* Function : Is_config_exploded */
4066 /* To check if the configuration for */
4067 /* the item has been exploded ever */
4068 /* before in Istalled Base */
4069 /*-----------------------------------------------------*/
4070
4071 FUNCTION Is_config_exploded
4072 ( p_instance_id IN NUMBER,
4073 p_stack_err_msg IN BOOLEAN
4074 ) RETURN BOOLEAN
4075 IS
4076
4077 l_dummy VARCHAR2(2);
4078
4079 BEGIN
4080
4081 SELECT '1'
4082 INTO l_dummy
4083 FROM csi_ii_relationships
4084 WHERE object_id = p_instance_id
4085 and ((active_end_date is null) or (active_end_date > sysdate))
4086 and relationship_type_code = 'COMPONENT-OF'
4087 and rownum < 2;
4088 IF ( p_stack_err_msg = TRUE ) THEN
4089 FND_MESSAGE.SET_NAME('CSI','CSI_CONFIG_EXPLODED');
4090 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
4091 FND_MSG_PUB.Add;
4092 END IF;
4093 RETURN TRUE;
4094
4095 EXCEPTION
4096 WHEN NO_DATA_FOUND THEN
4097 RETURN FALSE;
4098
4099 END Is_config_exploded;
4100
4101 /*-----------------------------------------------------------*/
4102 /* Procedure name: Is_Ver_StartDate_Valid */
4103 /* Description : Check if Version Label's active start */
4104 /* date is valid */
4105 /*-----------------------------------------------------------*/
4106
4107 FUNCTION Is_Ver_StartDate_Valid
4108 ( p_start_date IN DATE,
4109 p_end_date IN DATE,
4110 p_instance_id IN NUMBER,
4111 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
4112 ) RETURN BOOLEAN IS
4113
4114 l_instance_start_date DATE;
4115 l_instance_end_date DATE;
4116 l_return_value BOOLEAN := TRUE;
4117
4118 CURSOR c1 IS
4119 SELECT active_start_date,
4120 active_end_date
4121 FROM csi_item_instances
4122 WHERE instance_id = p_instance_id
4123 and ((active_end_date is null) OR (active_end_date >= sysdate));
4124 BEGIN
4125 IF ((p_end_date is NOT NULL) AND (p_end_date <> FND_API.G_MISS_DATE))THEN
4126 IF p_start_date > p_end_date THEN
4127 l_return_value := FALSE;
4128 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_VER_START_DATE');
4129 FND_MESSAGE.SET_TOKEN('ACTIVE_START_DATE',p_start_date);
4130 FND_MSG_PUB.Add;
4131 RETURN l_return_value;
4132 END IF;
4133 END IF;
4134
4135 OPEN c1;
4136 FETCH c1 INTO l_instance_start_date,l_instance_end_date;
4137 IF c1%NOTFOUND THEN
4138 l_return_value := FALSE;
4139 IF ( p_stack_err_msg = TRUE ) THEN
4140 FND_MESSAGE.SET_NAME('CSI','CSI_API_INST_START_DATE');
4141 FND_MESSAGE.SET_NAME('ENTITY','VERSION LABEL');
4142 FND_MSG_PUB.Add;
4143 END IF;
4144 CLOSE c1;
4145 RETURN l_return_value;
4146 END IF;
4147
4148 IF ((p_start_date < l_instance_start_date)
4149 OR ((l_instance_end_date IS NOT NULL) AND (p_start_date > l_instance_end_date))
4150 OR (p_start_date > SYSDATE)) THEN
4151 l_return_value := FALSE;
4152 IF ( p_stack_err_msg = TRUE ) THEN
4153 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_VER_START_DATE');
4154 FND_MESSAGE.SET_TOKEN('ACTIVE_START_DATE',p_start_date);
4155 FND_MSG_PUB.Add;
4156 END IF;
4157 END IF;
4158 RETURN l_return_value;
4159 END Is_Ver_StartDate_Valid;
4160
4161
4162 /*-----------------------------------------------------------*/
4163 /* Procedure name: Is_Ver_EndDate_Valid */
4164 /* Description : Check if version labels active end date */
4165 /* is valid */
4166 /*-----------------------------------------------------------*/
4167
4168 FUNCTION Is_Ver_EndDate_Valid
4169 (
4170 p_start_date IN DATE,
4171 p_end_date IN DATE,
4172 p_instance_id IN NUMBER,
4173 p_stack_err_msg IN BOOLEAN
4174 ) RETURN BOOLEAN IS
4175
4176 l_instance_end_date DATE;
4177 l_instance_start_date DATE;
4178 l_return_value BOOLEAN := TRUE;
4179
4180 CURSOR c1 IS
4181 SELECT active_end_date,
4182 active_start_date
4183 FROM csi_item_instances
4184 WHERE instance_id = p_instance_id
4185 and ((active_end_date is null) OR (active_end_date >= sysdate));
4186 BEGIN
4187 IF p_end_date is NOT NULL THEN
4188 IF p_end_date < sysdate THEN
4189 l_return_value := FALSE;
4190 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_VER_END_DATE');
4191 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
4192 FND_MSG_PUB.Add;
4193 RETURN l_return_value;
4194 END IF;
4195 END IF;
4196
4197 OPEN c1;
4198 FETCH c1 INTO l_instance_end_date ,l_instance_start_date;
4199
4200 IF l_instance_end_date is NOT NULL THEN
4201 IF ((p_end_date > l_instance_end_date) OR
4202 (p_end_date < l_instance_start_date))THEN
4203 l_return_value := FALSE;
4204 IF ( p_stack_err_msg = TRUE ) THEN
4205 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_VER_END_DATE');
4206 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
4207 FND_MSG_PUB.Add;
4208 END IF;
4209 END IF;
4210 END IF;
4211 CLOSE c1;
4212 RETURN l_return_value;
4213
4214 END Is_Ver_EndDate_Valid;
4215 --
4216 FUNCTION Is_Valid_Location_ID
4217 (
4218 p_location_source_table IN VARCHAR2,
4219 p_location_id IN NUMBER
4220 )
4221 RETURN BOOLEAN IS
4222 l_dummy NUMBER;
4223 l_return_value BOOLEAN;
4224 p_stack_err_msg BOOLEAN;
4225 l_location_lookup_type VARCHAR2(30) := 'CSI_INST_LOCATION_SOURCE_CODE';
4226 l_location_source_table VARCHAR2(30);
4227 BEGIN
4228 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
4229 csi_gen_utility_pvt.populate_install_param_rec;
4230 END IF;
4231 --
4232 IF csi_datastructures_pub.g_install_param_rec.fetch_flag = 'N' THEN
4233 FND_MESSAGE.SET_NAME('CSI','CSI_API_UNINSTALLED_PARAMETER');
4234 FND_MSG_PUB.ADD;
4235 l_return_value := FALSE;
4236 RETURN l_return_value;
4237 END IF;
4238 --
4239 -- srramakr Removed the references to Location IDs in CSI_INSTALL_PARAMETERS as they are derived
4240 -- from HR_ALL_ORGANIZATION_UNITS upfront.
4241 --
4242 l_return_value := TRUE;
4243 --
4244 -- Validate the Location Type and check if exists in csi_lookups table with type
4245 -- CSI_INST_LOCATION_SOURCE_CODE. If not raise the CSI_API_INVALID_LOCATION_TYPE exception
4246 -- Added the following code for R12
4247 IF ((p_location_source_table IS NULL) OR (p_location_source_table = FND_API.G_MISS_CHAR) OR
4248 (p_location_id IS NULL) OR (p_location_id = FND_API.G_MISS_NUM) )
4249 THEN
4250 FND_MESSAGE.SET_NAME('CSI','CSI_API_LOCATION_NOT_VALID');
4251 FND_MSG_PUB.ADD;
4252 l_return_value := FALSE;
4253 RETURN l_return_value;
4254 END IF;
4255
4256 -- Validate location type code
4257 BEGIN
4258 SELECT lookup_code
4259 INTO l_location_source_table
4260 FROM csi_lookups
4261 WHERE lookup_code = upper(p_location_source_table)
4262 AND lookup_type = l_location_lookup_type;
4263 l_return_value := TRUE;
4264
4265 EXCEPTION
4266 WHEN NO_DATA_FOUND THEN
4267 l_return_value := FALSE;
4268 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_SOURCE');
4269 FND_MESSAGE.SET_TOKEN('LOCATION_SOURCE_TABLE',p_location_source_table);
4270 FND_MSG_PUB.Add;
4271 RETURN l_return_value;
4272 END;
4273 -- End addition of code for R12
4274
4275 IF (p_location_source_table = 'HZ_PARTY_SITES') THEN
4276 BEGIN
4277 SELECT party_site_id
4278 INTO l_dummy
4279 FROM HZ_PARTY_SITES
4280 WHERE party_site_id = p_location_id;
4281 l_return_value := TRUE;
4282 EXCEPTION
4283 WHEN NO_DATA_FOUND THEN
4284 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PARTY_LOC_ID');
4285 FND_MESSAGE.SET_TOKEN('LOCATION_ID',p_location_id);
4286 FND_MSG_PUB.Add;
4287 l_return_value := FALSE;
4288 RETURN l_return_value;
4289 END;
4290 ELSIF (p_location_source_table = 'HZ_LOCATIONS') THEN
4291 BEGIN
4292 SELECT location_id
4293 INTO l_dummy
4294 FROM hz_locations
4295 WHERE location_id = p_location_id;
4296 l_return_value :=TRUE;
4297 EXCEPTION
4298 WHEN NO_DATA_FOUND THEN
4299 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_HZ_LOC_ID');
4300 FND_MESSAGE.SET_TOKEN('LOCATION_ID',p_location_id);
4301 FND_MSG_PUB.Add;
4302 l_return_value :=FALSE;
4303 RETURN l_return_value;
4304 END;
4305 ELSIF (p_location_source_table = 'VENDOR_SITE') THEN
4306 BEGIN
4307 SELECT vendor_site_id
4308 INTO l_dummy
4309 FROM po_vendor_sites_all
4310 WHERE vendor_site_id = p_location_id;
4311 l_return_value := TRUE;
4312 EXCEPTION
4313 WHEN NO_DATA_FOUND THEN
4314 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_VEND_LOC_ID');
4315 FND_MESSAGE.SET_TOKEN('LOCATION_ID',p_location_id);
4316 FND_MSG_PUB.Add;
4317 l_return_value := FALSE;
4318 RETURN l_return_value;
4319 END;
4320 ELSIF (p_location_source_table = 'INVENTORY') THEN
4321 BEGIN
4322 SELECT location_id
4323 INTO l_dummy
4324 FROM hr_locations_all
4325 WHERE location_id = p_location_id;
4326 l_return_value := TRUE;
4327 EXCEPTION
4328 WHEN NO_DATA_FOUND THEN
4329 l_return_value := FALSE;
4330 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INV_LOCATION');
4331 FND_MESSAGE.SET_TOKEN('LOCATION_ID',p_location_id);
4332 FND_MSG_PUB.Add;
4333 RETURN l_return_value;
4334 END;
4335 ELSIF (p_location_source_table = 'PROJECT') THEN
4336 -- srramakr PROJECT location could from HR or HZ. Since they share the same sequence,
4337 -- we first check against HR, if not found then HZ.
4338 BEGIN
4339 SELECT location_id
4340 INTO l_dummy
4341 FROM hr_locations_all
4342 WHERE location_id = p_location_id;
4343 l_return_value := TRUE;
4344 EXCEPTION
4345 WHEN NO_DATA_FOUND THEN
4346 BEGIN
4347 SELECT location_id
4348 INTO l_dummy
4349 FROM hz_locations
4350 WHERE location_id = p_location_id;
4351 l_return_value :=TRUE;
4352 EXCEPTION
4353 WHEN NO_DATA_FOUND THEN
4354 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INT_LOC_ID');
4355 FND_MESSAGE.SET_TOKEN('LOCATION_ID',p_location_id);
4356 FND_MSG_PUB.Add;
4357 l_return_value := FALSE;
4358 RETURN l_return_value;
4359 END;
4360 END;
4361 ELSIF (p_location_source_table IN ('INTERNAL_SITE','WIP','IN_TRANSIT','PO')) THEN
4362 BEGIN
4363 SELECT location_id
4364 INTO l_dummy
4365 FROM hr_locations_all
4366 WHERE location_id = p_location_id;
4367 l_return_value := TRUE;
4368 EXCEPTION
4369 WHEN NO_DATA_FOUND THEN
4370 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INT_LOC_ID');
4371 FND_MESSAGE.SET_TOKEN('LOCATION_ID',p_location_id);
4372 FND_MSG_PUB.Add;
4373 l_return_value := FALSE;
4374 RETURN l_return_value;
4375 END;
4376 ELSIF p_location_source_table IN ( 'HR_LOCATIONS') THEN
4377 BEGIN
4378 SELECT location_id
4379 INTO l_dummy
4380 FROM hr_locations_all
4381 WHERE location_id = p_location_id;
4382 l_return_value := TRUE;
4383 EXCEPTION
4384 WHEN NO_DATA_FOUND THEN
4385 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INT_LOC_ID');
4386 FND_MESSAGE.SET_TOKEN('LOCATION_ID',p_location_id);
4387 FND_MSG_PUB.Add;
4388 l_return_value := FALSE;
4389 RETURN l_return_value;
4390 END;
4391 ELSE
4392 l_return_value := FALSE;
4393 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_SOURCE');
4394 FND_MESSAGE.SET_TOKEN('LOCATION_SOURCE_TABLE',p_location_source_table);
4395 FND_MSG_PUB.Add;
4396 END IF;
4397 RETURN l_return_value;
4398 END Is_Valid_Location_ID;
4399 --
4400 FUNCTION Validate_Related_Loc_Params
4401 (
4402 p_location_source_table IN VARCHAR2,
4403 p_location_id IN NUMBER,
4404 p_organization_id IN NUMBER,
4405 p_subinventory IN VARCHAR2,
4406 p_locator_id IN NUMBER,
4407 p_project_id IN NUMBER,
4408 p_task_id IN NUMBER,
4409 p_sales_ord_line_id IN NUMBER,
4410 p_wip_job_id IN NUMBER,
4411 p_po_line_id IN NUMBER,
4412 p_inst_usage_code IN VARCHAR2
4413 )
4414 RETURN BOOLEAN IS
4415
4416 l_location_source_table VARCHAR2(30);
4417 l_temp_id NUMBER;
4418 l_return_value BOOLEAN;
4419 p_stack_err_msg BOOLEAN;
4420 BEGIN
4421
4422 -- Get the values of installation parameters
4423 IF (p_location_source_table = 'HZ_PARTY_SITES') THEN
4424 IF ((p_project_id IS NULL) OR (p_project_id = FND_API.G_MISS_NUM)) AND
4425 ((p_task_id IS NULL) OR (p_task_id = FND_API.G_MISS_NUM)) AND
4426 ((p_sales_ord_line_id IS NULL) OR (p_sales_ord_line_id = FND_API.G_MISS_NUM)) AND
4427 ((p_po_line_id IS NULL) OR (p_po_line_id = FND_API.G_MISS_NUM)) AND
4428 ((p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)) AND
4429 ((p_subinventory IS NULL) OR (p_subinventory = FND_API.G_MISS_CHAR)) AND
4430 ((p_locator_id IS NULL) OR (p_locator_id = FND_API.G_MISS_NUM)) AND
4431 ((p_wip_job_id IS NULL) OR (p_wip_job_id = FND_API.G_MISS_NUM)) THEN
4432 l_return_value:= TRUE;
4433 ELSE
4434 l_return_value:= FALSE;
4435 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4436 FND_MSG_PUB.Add;
4437 RETURN l_return_value;
4438 END IF;
4439 END IF;
4440 --
4441 IF (p_location_source_table = 'HZ_LOCATIONS') THEN
4442
4443 IF --((p_project_id IS NULL) OR (p_project_id = FND_API.G_MISS_NUM)) AND
4444 --((p_task_id IS NULL) OR (p_task_id = FND_API.G_MISS_NUM)) AND
4445 ((p_sales_ord_line_id IS NULL) OR (p_sales_ord_line_id = FND_API.G_MISS_NUM)) AND
4446 ((p_po_line_id IS NULL) OR (p_po_line_id = FND_API.G_MISS_NUM)) AND
4447 ((p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)) AND
4448 ((p_subinventory IS NULL) OR (p_subinventory = FND_API.G_MISS_CHAR)) AND
4449 ((p_locator_id IS NULL) OR (p_locator_id = FND_API.G_MISS_NUM)) AND
4450 ((p_wip_job_id IS NULL) OR (p_wip_job_id = FND_API.G_MISS_NUM)) THEN
4451 l_return_value:= TRUE;
4452 ELSE
4453 l_return_value:= FALSE;
4454 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4455 FND_MSG_PUB.Add;
4456 RETURN l_return_value;
4457 END IF;
4458 -----------
4459 IF ( (p_project_id IS NOT NULL AND p_project_id <> FND_API.G_MISS_NUM) OR
4460 (p_task_id IS NOT NULL AND p_task_id <> FND_API.G_MISS_NUM) ) THEN
4461 -- Modified for tracking FP bug 7276773 from base bug 6330298
4462 IF (p_inst_usage_code = 'INSTALLED' OR p_inst_usage_code = 'IN_PROCESS') THEN
4463 BEGIN
4464 SELECT '1'
4465 INTO l_temp_id
4466 FROM pa_tasks
4467 WHERE project_id = p_project_id
4468 AND task_id = p_task_id;
4469 EXCEPTION
4470 WHEN NO_DATA_FOUND THEN
4471 l_return_value:= FALSE;
4472 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_PROJ_LOC_ID');
4473 FND_MESSAGE.SET_TOKEN('PROJECT_ID',p_project_id||'-'||p_task_id);
4474 FND_MSG_PUB.Add;
4475 RETURN l_return_value;
4476 END;
4477 ELSE
4478 l_return_value:= FALSE;
4479 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4480 FND_MSG_PUB.Add;
4481 RETURN l_return_value;
4482 END IF; -- usage code = 'INSTALLED'
4483 END IF; -- Project id , task id provided
4484
4485 END IF; -- location type is HZ_LOCATIONS
4486 --
4487 IF (p_location_source_table = 'VENDOR_SITE') THEN
4488
4489 IF ((p_project_id IS NULL) OR (p_project_id = FND_API.G_MISS_NUM)) AND
4490 ((p_task_id IS NULL) OR (p_task_id = FND_API.G_MISS_NUM)) AND
4491 ((p_sales_ord_line_id IS NULL) OR (p_sales_ord_line_id = FND_API.G_MISS_NUM)) AND
4492 ((p_po_line_id IS NULL) OR (p_po_line_id = FND_API.G_MISS_NUM)) AND
4493 ((p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)) AND
4494 ((p_subinventory IS NULL) OR (p_subinventory = FND_API.G_MISS_CHAR)) AND
4495 ((p_locator_id IS NULL) OR (p_locator_id = FND_API.G_MISS_NUM)) AND
4496 ((p_wip_job_id IS NULL) OR (p_wip_job_id = FND_API.G_MISS_NUM)) THEN
4497 l_return_value:= TRUE;
4498 ELSE
4499 l_return_value:= FALSE;
4500 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4501 FND_MSG_PUB.Add;
4502 RETURN l_return_value;
4503 END IF;
4504 END IF;
4505 --
4506 IF (p_location_source_table = 'INVENTORY') THEN
4507
4508 IF (p_location_id IS NOT NULL) THEN
4509 IF p_inst_usage_code <> 'IN_TRANSIT' THEN
4510 IF ((p_organization_id IS NOT NULL) AND (p_organization_id <> FND_API.G_MISS_NUM)) AND
4511 ((p_subinventory IS NOT NULL) AND (p_subinventory <> FND_API.G_MISS_CHAR)) THEN
4512 -- l_return_value := TRUE;
4513 BEGIN
4514 SELECT '1'
4515 INTO l_temp_id
4516 FROM mtl_secondary_inventories
4517 WHERE secondary_inventory_name = p_subinventory AND
4518 organization_id = p_organization_id;
4519 l_return_value := TRUE;
4520
4521 EXCEPTION
4522 WHEN NO_DATA_FOUND THEN
4523 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INV_LOC_ID');
4524 FND_MESSAGE.SET_TOKEN('SUBINVENTORY',p_subinventory);
4525 FND_MSG_PUB.Add;
4526 l_return_value := FALSE;
4527 RETURN l_return_value;
4528 END;
4529 -- Validate Locator_ID srramakr
4530 IF ((p_locator_id IS NOT NULL) AND (p_locator_id <> FND_API.G_MISS_NUM)) THEN
4531 BEGIN
4532 SELECT '1'
4533 INTO l_temp_id
4534 FROM MTL_ITEM_LOCATIONS
4535 WHERE inventory_location_id = p_locator_id
4536 AND organization_id = p_organization_id
4537 AND subinventory_code = p_subinventory;
4538 --
4539 l_return_value := TRUE;
4540 EXCEPTION
4541 WHEN NO_DATA_FOUND THEN
4542 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INV_LOC_ID');
4543 FND_MESSAGE.SET_TOKEN('LOCATOR_ID',p_locator_id);
4544 FND_MSG_PUB.Add;
4545 l_return_value := FALSE;
4546 RETURN l_return_value;
4547 END;
4548 END IF;
4549 ELSE
4550 l_return_value := FALSE;
4551 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INVENTORY_LOC');
4552 FND_MSG_PUB.Add;
4553 RETURN l_return_value;
4554 END IF;
4555 END IF;
4556 END IF;
4557
4558 IF ((p_project_id IS NULL) OR (p_project_id = FND_API.G_MISS_NUM)) AND
4559 ((p_task_id IS NULL) OR (p_task_id = FND_API.G_MISS_NUM)) AND
4560 ((p_sales_ord_line_id IS NULL) OR (p_sales_ord_line_id = FND_API.G_MISS_NUM)) AND
4561 ((p_po_line_id IS NULL) OR (p_po_line_id = FND_API.G_MISS_NUM)) AND
4562 ((p_wip_job_id IS NULL) OR (p_wip_job_id = FND_API.G_MISS_NUM)) THEN
4563 l_return_value:= TRUE;
4564 ELSE
4565 l_return_value:= FALSE;
4566 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4567 FND_MSG_PUB.Add;
4568 RETURN l_return_value;
4569 END IF;
4570 END IF; -- INVENTORY
4571 --
4572 IF (p_location_source_table = 'INTERNAL_SITE') THEN
4573 l_return_value := TRUE;
4574 IF ((p_project_id IS NULL) OR (p_project_id = FND_API.G_MISS_NUM)) AND
4575 ((p_task_id IS NULL) OR (p_task_id = FND_API.G_MISS_NUM)) AND
4576 ((p_sales_ord_line_id IS NULL) OR (p_sales_ord_line_id = FND_API.G_MISS_NUM)) AND
4577 ((p_po_line_id IS NULL) OR (p_po_line_id = FND_API.G_MISS_NUM)) AND
4578 ((p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)) AND
4579 ((p_subinventory IS NULL) OR (p_subinventory = FND_API.G_MISS_CHAR)) AND
4580 ((p_locator_id IS NULL) OR (p_locator_id = FND_API.G_MISS_NUM)) AND
4581 ((p_wip_job_id IS NULL) OR (p_wip_job_id = FND_API.G_MISS_NUM)) THEN
4582 l_return_value:= TRUE;
4583 ELSE
4584 l_return_value:= FALSE;
4585 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4586 FND_MSG_PUB.Add;
4587 RETURN l_return_value;
4588 END IF;
4589 END IF;
4590 --
4591 IF (p_location_source_table = 'WIP') THEN
4592 BEGIN
4593 SELECT '1'
4594 INTO l_temp_id
4595 FROM wip_entities
4596 WHERE wip_entity_id = p_wip_job_id;
4597
4598 IF ((p_project_id IS NULL) OR (p_project_id = FND_API.G_MISS_NUM)) AND
4599 ((p_task_id IS NULL) OR (p_task_id = FND_API.G_MISS_NUM)) AND
4600 ((p_sales_ord_line_id IS NULL) OR (p_sales_ord_line_id = FND_API.G_MISS_NUM)) AND
4601 ((p_po_line_id IS NULL) OR (p_po_line_id = FND_API.G_MISS_NUM)) AND
4602 ((p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)) AND
4603 ((p_subinventory IS NULL) OR (p_subinventory = FND_API.G_MISS_CHAR)) AND
4604 ((p_locator_id IS NULL) OR (p_locator_id = FND_API.G_MISS_NUM)) THEN
4605 l_return_value:= TRUE;
4606 ELSE
4607 l_return_value:= FALSE;
4608 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4609 FND_MSG_PUB.Add;
4610 RETURN l_return_value;
4611 END IF;
4612
4613 EXCEPTION
4614 WHEN NO_DATA_FOUND THEN
4615 l_return_value:= FALSE;
4616 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_WIP_JOB_ID');
4617 FND_MESSAGE.SET_TOKEN('WIP_JOB_ID',p_wip_job_id);
4618 FND_MSG_PUB.Add;
4619 RETURN l_return_value;
4620 END;
4621 --
4622 ELSIF (p_location_source_table = 'PROJECT') THEN
4623
4624 BEGIN
4625 SELECT '1'
4626 INTO l_temp_id
4627 FROM pa_tasks
4628 WHERE project_id = p_project_id
4629 AND task_id = p_task_id;
4630
4631 IF ((p_sales_ord_line_id IS NULL) OR (p_sales_ord_line_id = FND_API.G_MISS_NUM)) AND
4632 ((p_po_line_id IS NULL) OR (p_po_line_id = FND_API.G_MISS_NUM)) AND
4633 ((p_wip_job_id IS NULL) OR (p_wip_job_id = FND_API.G_MISS_NUM)) AND
4634 ((p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)) AND
4635 ((p_subinventory IS NULL) OR (p_subinventory = FND_API.G_MISS_CHAR)) AND
4636 ((p_locator_id IS NULL) OR (p_locator_id = FND_API.G_MISS_NUM)) THEN
4637 l_return_value:= TRUE;
4638 ELSE
4639 l_return_value:= FALSE;
4640 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4641 FND_MSG_PUB.Add;
4642 RETURN l_return_value;
4643 END IF;
4644
4645 EXCEPTION
4646 WHEN NO_DATA_FOUND THEN
4647 l_return_value:= FALSE;
4648 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PROJ_LOC_ID');
4649 FND_MESSAGE.SET_TOKEN('PROJECT_ID',p_project_id||'-'||p_task_id);
4650 FND_MSG_PUB.Add;
4651 RETURN l_return_value;
4652 END;
4653
4654 ELSIF (p_location_source_table = 'IN_TRANSIT') THEN
4655 BEGIN
4656 SELECT '1'
4657 INTO l_temp_id
4658 FROM oe_order_lines_all
4659 WHERE line_id = p_sales_ord_line_id;
4660
4661 IF ((p_project_id IS NULL) OR (p_project_id = FND_API.G_MISS_NUM)) AND
4662 ((p_task_id IS NULL) OR (p_task_id = FND_API.G_MISS_NUM)) AND
4663 ((p_po_line_id IS NULL) OR (p_po_line_id = FND_API.G_MISS_NUM)) AND
4664 ((p_wip_job_id IS NULL) OR (p_wip_job_id = FND_API.G_MISS_NUM)) AND
4665 ((p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)) AND
4666 ((p_subinventory IS NULL) OR (p_subinventory = FND_API.G_MISS_CHAR)) AND
4667 ((p_locator_id IS NULL) OR (p_locator_id = FND_API.G_MISS_NUM)) THEN
4668 l_return_value:= TRUE;
4669 ELSE
4670 l_return_value:= FALSE;
4671 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4672 FND_MSG_PUB.Add;
4673 RETURN l_return_value;
4674 END IF;
4675
4676 EXCEPTION
4677 WHEN NO_DATA_FOUND THEN
4678 l_return_value:= FALSE;
4679 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INT_ORDER_ID');
4680 FND_MESSAGE.SET_TOKEN('IN_TRANSIT_ID',p_sales_ord_line_id);
4681 FND_MSG_PUB.Add;
4682 RETURN l_return_value;
4683 END;
4684 --
4685 ELSIF (p_location_source_table = 'PO') THEN
4686 BEGIN
4687 SELECT '1'
4688 INTO l_temp_id
4689 FROM po_lines_all
4690 WHERE po_line_id = p_po_line_id;
4691
4692 IF ((p_sales_ord_line_id IS NULL) OR (p_sales_ord_line_id = FND_API.G_MISS_NUM)) AND
4693 ((p_project_id IS NULL) OR (p_project_id = FND_API.G_MISS_NUM)) AND
4694 ((p_task_id IS NULL) OR (p_task_id = FND_API.G_MISS_NUM)) AND
4695 ((p_wip_job_id IS NULL) OR (p_wip_job_id = FND_API.G_MISS_NUM)) AND
4696 ((p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)) AND
4697 ((p_subinventory IS NULL) OR (p_subinventory = FND_API.G_MISS_CHAR)) AND
4698 ((p_locator_id IS NULL) OR (p_locator_id = FND_API.G_MISS_NUM)) THEN
4699 l_return_value:= TRUE;
4700 ELSE
4701 l_return_value:= FALSE;
4702 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_LOC_PARAMS');
4703 FND_MSG_PUB.Add;
4704 FND_MSG_PUB.Add;
4705 RETURN l_return_value;
4706 END IF;
4707 EXCEPTION
4708 WHEN NO_DATA_FOUND THEN
4709 l_return_value:= FALSE;
4710 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PO_LOC_ID');
4711 FND_MESSAGE.SET_TOKEN('PO_LINE_ID',p_po_line_id);
4712 FND_MSG_PUB.Add;
4713 RETURN l_return_value;
4714 END;
4715 END IF;
4716 RETURN l_return_value;
4717 END Validate_Related_Loc_Params;
4718
4719 -- Added by sguthiva for att enhancements
4720
4721 /*-----------------------------------------------------------*/
4722 /* Procedure name: get_link_locations */
4723 /* Description : Retreive the Location Parameters from */
4724 /* associated instances of an instance of */
4725 /* instance item class link */
4726 /*-----------------------------------------------------------*/
4727 PROCEDURE get_link_locations
4728 (p_instance_header_tbl IN OUT NOCOPY csi_datastructures_pub.instance_header_tbl,
4729 x_return_status OUT NOCOPY VARCHAR2
4730 ) IS
4731 l_object_id NUMBER;
4732 l_subject_id NUMBER;
4733 l_header_tbl csi_datastructures_pub.instance_header_tbl ;
4734 l_temp_header_tbl csi_datastructures_pub.instance_header_tbl ;
4735 l_link_type VARCHAR2(30);
4736 i NUMBER;
4737
4738 CURSOR link_csr(p_instance_id NUMBER) IS
4739 SELECT object_id,subject_id
4740 FROM csi_ii_relationships
4741 WHERE ( subject_id=p_instance_id
4742 OR object_id=p_instance_id)
4743 AND relationship_type_code ='CONNECTED-TO'
4744 AND SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE);
4745 BEGIN
4746 x_return_status := FND_API.G_RET_STS_SUCCESS;
4747 -- Need to get the schema name using FND API. Refer Bug # 3431768
4748 --
4749 FOR l_link IN p_instance_header_tbl.FIRST..p_instance_header_tbl.LAST
4750 LOOP
4751 SELECT nvl(ib_item_instance_class,'X')
4752 INTO l_link_type
4753 FROM mtl_system_items_b
4754 WHERE inventory_item_id=p_instance_header_tbl(l_link).inventory_item_id
4755 AND organization_id=p_instance_header_tbl(l_link).vld_organization_id;
4756
4757 IF l_link_type='LINK'
4758 THEN
4759 i:=1;
4760 l_header_tbl:=l_temp_header_tbl;
4761 FOR l_link_csr IN link_csr(p_instance_header_tbl(l_link).instance_id)
4762 LOOP
4763 IF l_link_csr.object_id= p_instance_header_tbl(l_link).instance_id
4764 THEN
4765 l_header_tbl(i).instance_id := l_link_csr.subject_id;
4766 END IF;
4767
4768 IF l_link_csr.subject_id= p_instance_header_tbl(l_link).instance_id
4769 THEN
4770 l_header_tbl(i).instance_id := l_link_csr.object_id;
4771 END IF;
4772 i:=i+1;
4773 END LOOP;
4774
4775 IF l_header_tbl.COUNT>0
4776 THEN
4777 IF l_header_tbl(1).instance_id IS NOT NULL AND
4778 l_header_tbl(1).instance_id <> FND_API.G_MISS_NUM
4779 THEN
4780 BEGIN
4781 SELECT location_id,
4782 location_type_code
4783 INTO l_header_tbl(1).location_id,
4784 l_header_tbl(1).location_type_code
4785 FROM csi_item_instances
4786 WHERE instance_id=l_header_tbl(1).instance_id;
4787 EXCEPTION
4788 WHEN OTHERS THEN
4789 NULL;
4790 END;
4791 --
4792 IF l_header_tbl.COUNT>1
4793 THEN
4794 IF l_header_tbl(2).instance_id IS NOT NULL AND
4795 l_header_tbl(2).instance_id <> FND_API.G_MISS_NUM
4796 THEN
4797
4798 BEGIN
4799 SELECT location_id,
4800 location_type_code
4801 INTO l_header_tbl(2).location_id,
4802 l_header_tbl(2).location_type_code
4803 FROM csi_item_instances
4804 WHERE instance_id=l_header_tbl(2).instance_id;
4805 EXCEPTION
4806 WHEN OTHERS THEN
4807 NULL;
4808 END;
4809 END IF;
4810 END IF;
4811
4812 csi_item_instance_pvt.resolve_id_columns
4813 (p_instance_header_tbl => l_header_tbl);
4814
4815 p_instance_header_tbl(l_link).start_loc_address1 := l_header_tbl(1).current_loc_address1;
4816 p_instance_header_tbl(l_link).start_loc_address2 := l_header_tbl(1).current_loc_address2;
4817 p_instance_header_tbl(l_link).start_loc_address3 := l_header_tbl(1).current_loc_address3;
4818 p_instance_header_tbl(l_link).start_loc_address4 := l_header_tbl(1).current_loc_address4;
4819 p_instance_header_tbl(l_link).start_loc_city := l_header_tbl(1).current_loc_city;
4820 p_instance_header_tbl(l_link).start_loc_state := l_header_tbl(1).current_loc_state;
4821 p_instance_header_tbl(l_link).start_loc_postal_code := l_header_tbl(1).current_loc_postal_code;
4822 p_instance_header_tbl(l_link).start_loc_country := l_header_tbl(1).current_loc_country;
4823 IF l_header_tbl.COUNT>1
4824 THEN
4825 p_instance_header_tbl(l_link).end_loc_address1 := l_header_tbl(2).current_loc_address1;
4826 p_instance_header_tbl(l_link).end_loc_address2 := l_header_tbl(2).current_loc_address2;
4827 p_instance_header_tbl(l_link).end_loc_address3 := l_header_tbl(2).current_loc_address3;
4828 p_instance_header_tbl(l_link).end_loc_address4 := l_header_tbl(2).current_loc_address4;
4829 p_instance_header_tbl(l_link).end_loc_city := l_header_tbl(2).current_loc_city;
4830 p_instance_header_tbl(l_link).end_loc_state := l_header_tbl(2).current_loc_state;
4831 p_instance_header_tbl(l_link).end_loc_postal_code := l_header_tbl(2).current_loc_postal_code;
4832 p_instance_header_tbl(l_link).end_loc_country := l_header_tbl(2).current_loc_country;
4833 END IF;
4834
4835 END IF;
4836 END IF;
4837 END IF;
4838 END LOOP;
4839 END get_link_locations;
4840
4841 PROCEDURE Call_batch_validate
4842 ( p_instance_rec IN csi_datastructures_pub.instance_rec
4843 ,p_config_hdr_id IN NUMBER
4844 ,p_config_rev_nbr IN NUMBER
4845 ,x_config_hdr_id OUT NOCOPY NUMBER
4846 ,x_config_rev_nbr OUT NOCOPY NUMBER
4847 ,x_return_status OUT NOCOPY VARCHAR2)
4848 IS
4849 l_xml_hdr VARCHAR2(2000);
4850 l_xml_message LONG := NULL;
4851
4852 BEGIN
4853 x_return_status:= FND_API.G_RET_STS_SUCCESS;
4854
4855 Create_hdr_xml
4856 ( p_config_hdr_id => p_config_hdr_id,
4857 p_config_rev_nbr => p_config_rev_nbr,
4858 p_config_inst_hdr_id => p_instance_rec.config_inst_hdr_id ,
4859 x_xml_hdr => l_xml_hdr,
4860 x_return_status => x_return_status);
4861
4862 csi_gen_utility_pvt.put_line('Status after calling Create_hdr_xml is '||x_return_status);
4863
4864 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4865 RAISE FND_API.G_EXC_ERROR;
4866 END IF;
4867
4868 Send_Input_xml
4869 ( p_xml_hdr => l_xml_hdr,
4870 x_out_xml_msg => l_xml_message,
4871 x_return_status => x_return_status);
4872
4873 csi_gen_utility_pvt.put_line('Status after calling Send_Input_xml is '||x_return_status);
4874
4875 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4876 RAISE FND_API.G_EXC_ERROR;
4877 END IF;
4878
4879 Parse_output_xml
4880 ( p_xml => l_xml_message,
4881 x_config_hdr_id => x_config_hdr_id,
4882 x_config_rev_nbr => x_config_rev_nbr,
4883 x_return_status => x_return_status );
4884
4885
4886 csi_gen_utility_pvt.put_line('Status after calling Parse_output_xml is '||x_return_status);
4887
4888 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4889 RAISE FND_API.G_EXC_ERROR;
4890 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4892 END IF;
4893
4894 EXCEPTION
4895 WHEN FND_API.G_EXC_ERROR THEN
4896 x_return_status := FND_API.G_RET_STS_ERROR;
4897 csi_gen_utility_pvt.put_line('An exp error raised');
4898
4899 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4901 csi_gen_utility_pvt.put_line('An unexp error raised');
4902
4903 WHEN OTHERS THEN
4904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4905 csi_gen_utility_pvt.put_line( 'Send_input_xml error: ' ||substr(sqlerrm,1,100));
4906
4907 END Call_batch_validate;
4908 --
4909 PROCEDURE decode_queue(p_pending_txn_tbl OUT NOCOPY csi_item_instance_pvt.T_NUM
4910 ,p_freeze_date IN DATE) is
4911 CURSOR msg_cur is
4912 SELECT msg_id,
4913 msg_code,
4914 msg_status,
4915 body_text,
4916 creation_date,
4917 description
4918 FROM xnp_msgs
4919 WHERE (msg_code like 'CSI%' OR msg_code like 'CSE%')
4920 -- AND nvl(msg_status, 'READY') <> 'PROCESSED' -- COmmented for Bug 3987286
4921 AND msg_status in ('READY','FAILED')
4922 AND msg_creation_date > p_freeze_date
4923 AND recipient_name is null;
4924
4925 l_amount integer;
4926 l_msg_text varchar2(32767);
4927 l_source_id varchar2(200);
4928 l_ctr number;
4929 BEGIN
4930 p_pending_txn_tbl.DELETE;
4931 l_ctr := 0;
4932 FOR msg_rec in msg_cur
4933 LOOP
4934
4935 l_amount := null;
4936 l_amount := dbms_lob.getlength(msg_rec.body_text);
4937 l_msg_text := null;
4938
4939 dbms_lob.read(
4940 lob_loc => msg_rec.body_text,
4941 amount => l_amount,
4942 offset => 1,
4943 buffer => l_msg_text );
4944
4945 l_source_id := null;
4946
4947 IF msg_rec.msg_code in ('CSISOFUL', 'CSIRMAFL') THEN
4948 null;
4949 ELSE
4950 xnp_xml_utils.decode(l_msg_text, 'MTL_TRANSACTION_ID', l_source_id);
4951 END IF;
4952 --
4953 IF l_source_id IS NOT NULL THEN
4954 l_ctr := l_ctr + 1;
4955 p_pending_txn_tbl(l_ctr) := to_number(l_source_id);
4956 END IF;
4957 END LOOP;
4958 END decode_queue;
4959 --
4960 /*-----------------------------------------------------------*/
4961 /* Procedure name: Check_Prior_Txn */
4962 /* Description : Check if there is any transactions pending */
4963 /* this Item Instance prior to the current Txn */
4964 /* */
4965 /* If p_mode is CREATE then we need to get the pending txns */
4966 /* from the xnp_msgs by decoding the message. Each valid txn*/
4967 /* will be checked against this list for further processing */
4968 /*-----------------------------------------------------------*/
4969
4970 PROCEDURE Check_Prior_Txn
4971 ( p_instance_rec IN csi_datastructures_pub.instance_rec
4972 ,p_txn_rec IN csi_datastructures_pub.transaction_rec
4973 ,p_prior_txn_id OUT NOCOPY NUMBER
4974 ,p_mode IN VARCHAR2
4975 ,x_return_status OUT NOCOPY VARCHAR2
4976 ) AS
4977
4978 -- Added cursor for bug 6755879, FP of bug 6680634
4979 CURSOR err_txn_cur(
4980 p_transaction_id NUMBER,
4981 p_transfer_transaction_id NUMBER) IS
4982 SELECT inv_material_transaction_id
4983 FROM csi_txn_errors
4984 WHERE inv_material_transaction_id IS NOT NULL
4985 AND inv_material_transaction_id IN (p_transaction_id, p_transfer_transaction_id)
4986 AND processed_flag IN ('R','E');
4987
4988 l_pending_txn_tbl csi_item_instance_pvt.T_NUM;
4989 --
4990 l_txn_seq_start_date DATE;
4991 l_max_csi_txn_id NUMBER;
4992 --
4993 l_src_txn_id NUMBER;
4994 l_src_txn_type_id NUMBER;
4995 l_min_txn_id NUMBER;
4996 l_xfer_mtl_txn_id NUMBER;
4997 l_src_txn_date DATE;
4998 l_cur_mtl_txn_id number;
4999 l_cur_mtl_txn_date DATE;
5000 l_mtl_txn_tbl csi_datastructures_pub.mtl_txn_tbl;
5001 l_txn_line_detail_id NUMBER;
5002 l_order_number NUMBER;
5003 l_line_number NUMBER;
5004 l_error_found varchar2(1);
5005 l_txn_found varchar2(1);
5006
5007 -- Added variables for bug 6755879, FP of bug 6680634
5008 l_err_mtl_txn_id NUMBER;
5009 l_err_mtl_txn_date DATE;
5010 BEGIN
5011 x_return_status := FND_API.G_RET_STS_SUCCESS;
5012 --
5013 csi_gen_utility_pvt.put_line('inside check_prior_txn');
5014
5015 IF p_instance_rec.inventory_item_id is NOT NULL AND p_instance_rec.inventory_item_id <> FND_API.G_MISS_NUM
5016 AND p_instance_rec.serial_number is NOT NULL AND p_instance_rec.serial_number <> FND_API.G_MISS_CHAR
5017 THEN
5018 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
5019 csi_gen_utility_pvt.populate_install_param_rec;
5020 END IF;
5021
5022 l_txn_seq_start_date := nvl(csi_datastructures_pub.g_install_param_rec.txn_seq_start_date,
5023 csi_datastructures_pub.g_install_param_rec.freeze_date);
5024
5025 IF l_txn_seq_start_date IS NULL THEN
5026 FND_MESSAGE.SET_NAME('CSI','CSI_API_UNINSTALLED_PARAMETER');
5027 FND_MSG_PUB.ADD;
5028 raise fnd_api.g_exc_error;
5029 END IF;
5030
5031 IF p_instance_rec.last_txn_line_detail_id IS NULL OR
5032 p_instance_rec.last_txn_line_detail_id = FND_API.G_MISS_NUM THEN
5033 l_txn_line_detail_id := -9999;
5034 ELSE
5035 l_txn_line_detail_id := p_instance_rec.last_txn_line_detail_id;
5036 END IF;
5037 --
5038 l_cur_mtl_txn_id := NVL(p_txn_rec.inv_material_transaction_id,FND_API.G_MISS_NUM);
5039 IF l_cur_mtl_txn_id <> FND_API.G_MISS_NUM THEN
5040 Begin
5041 select creation_date
5042 into l_cur_mtl_txn_date
5043 from MTL_MATERIAL_TRANSACTIONS
5044 where transaction_id = l_cur_mtl_txn_id;
5045 Exception
5046 when no_data_found then
5047 l_cur_mtl_txn_date := sysdate;
5048 End;
5049 ELSE
5050 l_cur_mtl_txn_date := sysdate;
5051 END IF;
5052 -- Added debug lines for bug 6755879, FP of bug 6680634
5053 csi_gen_utility_pvt.put_line(' l_cur_mtl_txn_id : '||l_cur_mtl_txn_id);
5054 csi_gen_utility_pvt.put_line(' l_cur_mtl_txn_date : '||l_cur_mtl_txn_date);
5055
5056 IF p_mode = 'CREATE' THEN
5057 decode_queue(p_pending_txn_tbl => l_pending_txn_tbl
5058 ,p_freeze_date => csi_datastructures_pub.g_install_param_rec.freeze_date);
5059 -- For 'UPDATE' mode get the transactions starting from the src txn date used for instance creation.
5060 -- Bug # 4018629
5061 ELSE
5062 l_min_txn_id := NULL;
5063 l_src_txn_date := NULL;
5064 select min(transaction_id)
5065 into l_min_txn_id
5066 from csi_item_instances_h
5067 where instance_id = p_instance_rec.instance_id
5068 and creation_date = (select min(creation_date) from csi_item_instances_h
5069 where instance_id = p_instance_rec.instance_id
5070 );
5071 --
5072 IF l_min_txn_id IS NOT NULL THEN
5073 Begin
5074 select source_transaction_date
5075 into l_src_txn_date
5076 from csi_transactions
5077 where transaction_id = l_min_txn_id;
5078 Exception
5079 when no_data_found then
5080 null;
5081 End;
5082 END IF;
5083 --
5084 IF l_src_txn_date IS NOT NULL AND
5085 l_src_txn_date > l_txn_seq_start_date THEN
5086 l_txn_seq_start_date := l_src_txn_date;
5087 END IF;
5088 END IF;
5089 --
5090
5091 get_mtl_txn_for_srl(
5092 p_inventory_item_id => p_instance_rec.inventory_item_id,
5093 p_serial_number => p_instance_rec.serial_number,
5094 x_mtl_txn_tbl => l_mtl_txn_tbl
5095 );
5096 csi_gen_utility_pvt.put_line('l_mtl_txn_tbl.count : '||l_mtl_txn_tbl.count);
5097
5098 IF l_mtl_txn_tbl.count > 0 THEN
5099 FOR l_ind IN l_mtl_txn_tbl.FIRST .. l_mtl_txn_tbl.LAST
5100 LOOP
5101 -- Added debug lines for bug 6755879, FP of bug 6680634
5102 csi_gen_utility_pvt.put_line('l_mtl_txn_tbl('||l_ind||').transaction_id : '
5103 ||l_mtl_txn_tbl(l_ind).transaction_id);
5104 csi_gen_utility_pvt.put_line('l_mtl_txn_tbl('||l_ind||').transfer_transaction_id : '
5105 ||l_mtl_txn_tbl(l_ind).transfer_transaction_id);
5106 IF l_mtl_txn_tbl(l_ind).creation_date > l_txn_seq_start_date
5107 AND l_mtl_txn_tbl(l_ind).creation_date < l_cur_mtl_txn_date
5108 AND l_mtl_txn_tbl(l_ind).transaction_id <> l_cur_mtl_txn_id
5109 THEN
5110 IF p_mode = 'CREATE' THEN
5111 IF l_pending_txn_tbl.count > 0 THEN
5112 IF l_pending_txn_tbl.exists(l_mtl_txn_tbl(l_ind).transaction_id) THEN
5113 fnd_message.set_name('CSI','CSI_PENDING_PRIOR_TXN');
5114 fnd_message.set_token('MAT_TXN_ID',l_mtl_txn_tbl(l_ind).transaction_id);
5115 fnd_msg_pub.add;
5116 p_prior_txn_id := l_mtl_txn_tbl(l_ind).transaction_id;
5117 raise fnd_api.g_exc_error;
5118 END IF;
5119 IF l_mtl_txn_tbl(l_ind).transfer_transaction_id is not null THEN
5120 IF l_pending_txn_tbl.exists(l_mtl_txn_tbl(l_ind).transfer_transaction_id) THEN
5121 fnd_message.set_name('CSI','CSI_PENDING_PRIOR_TXN');
5122 fnd_message.set_token('MAT_TXN_ID',l_mtl_txn_tbl(l_ind).transfer_transaction_id);
5123 fnd_msg_pub.add;
5124 p_prior_txn_id := l_mtl_txn_tbl(l_ind).transfer_transaction_id;
5125 RAISE fnd_api.g_exc_error;
5126 END IF;
5127 END IF;
5128 END IF;
5129 END IF;
5130 IF l_mtl_txn_tbl(l_ind).transfer_transaction_id is not null THEN
5131 l_xfer_mtl_txn_id := l_mtl_txn_tbl(l_ind).transfer_transaction_id;
5132 ELSE
5133 l_xfer_mtl_txn_id := -999999;
5134 END IF;
5135
5136 -- check against csi_txn_errors
5137 -- Begin modification for bug 6755879, FP of bug 6680634
5138 FOR err_txn_rec IN err_txn_cur(
5139 p_transaction_id => l_mtl_txn_tbl(l_ind).transaction_id,
5140 p_transfer_transaction_id => l_xfer_mtl_txn_id)
5141 LOOP
5142 l_err_mtl_txn_id := err_txn_rec.inv_material_transaction_id;
5143
5144 IF l_err_mtl_txn_id <> FND_API.G_MISS_NUM
5145 AND l_err_mtl_txn_id <> l_cur_mtl_txn_id THEN -- The errorred transaction is not the transaction currently being reprocessed
5146 BEGIN
5147 SELECT creation_date
5148 INTO l_err_mtl_txn_date
5149 FROM mtl_material_transactions
5150 WHERE transaction_id = l_err_mtl_txn_id;
5151 EXCEPTION
5152 WHEN NO_DATA_FOUND THEN
5153 NULL;
5154 END;
5155
5156 csi_gen_utility_pvt.put_line(' l_err_mtl_txn_id : '||l_err_mtl_txn_id);
5157 csi_gen_utility_pvt.put_line(' l_err_mtl_txn_date : '||l_err_mtl_txn_date);
5158
5159 IF (l_err_mtl_txn_date <> FND_API.G_MISS_DATE
5160 AND l_err_mtl_txn_date <= l_cur_mtl_txn_date)
5161 OR (l_err_mtl_txn_date = FND_API.G_MISS_DATE) THEN
5162 fnd_message.set_name('CSI','CSI_ERROR_PRIOR_TXN');
5163 fnd_message.set_token('MAT_TXN_ID',l_mtl_txn_tbl(l_ind).transaction_id);
5164 fnd_msg_pub.add;
5165 p_prior_txn_id := l_mtl_txn_tbl(l_ind).transaction_id;
5166 RAISE fnd_api.g_exc_error;
5167 END IF;
5168 END IF;
5169 END LOOP;
5170
5171 -- Add condition for bug 6755879, FP of bug 6680634
5172 IF p_mode <> 'CREATE' THEN
5173 BEGIN
5174 SELECT 'Y'
5175 INTO l_txn_found
5176 FROM csi_transactions
5177 WHERE inv_material_transaction_id in (l_mtl_txn_tbl(l_ind).transaction_id, l_xfer_mtl_txn_id)
5178 AND rownum = 1;
5179 EXCEPTION
5180 WHEN no_data_found THEN
5181 fnd_message.set_name('CSI','CSI_PENDING_PRIOR_TXN');
5182 fnd_message.set_token('MAT_TXN_ID',l_mtl_txn_tbl(l_ind).transaction_id);
5183 fnd_msg_pub.add;
5184 p_prior_txn_id := l_mtl_txn_tbl(l_ind).transaction_id;
5185 RAISE fnd_api.g_exc_error;
5186 END;
5187 END IF;
5188 END IF;
5189 END LOOP;
5190 -- End modification for bug 6755879, FP of bug 6680634
5191 END IF;
5192 -- Check whether the instance_id resides in CSI_T_TXN_LINE_DETAILS in 'ERROR' status.
5193 l_src_txn_id := NULL;
5194 l_src_txn_type_id := NULL;
5195 Begin
5196 select line.source_transaction_id,line.source_transaction_type_id
5197 into l_src_txn_id,l_src_txn_type_id
5198 from CSI_T_TXN_LINE_DETAILS det,
5199 CSI_T_TRANSACTION_LINES line
5200 where ( (det.instance_id = p_instance_rec.instance_id) OR
5201 (det.inventory_item_id = p_instance_rec.inventory_item_id AND
5202 det.serial_number = p_instance_rec.serial_number) )
5203 and nvl(det.processing_status,'SUBMIT') = 'ERROR'
5204 and det.creation_date < p_txn_rec.source_transaction_date
5205 and det.txn_line_detail_id <> l_txn_line_detail_id
5206 and line.transaction_line_id = det.transaction_line_id
5207 and rownum = 1;
5208 Exception
5209 when no_data_found then
5210 null;
5211 End;
5212 --
5213 IF l_src_txn_id IS NOT NULL AND
5214 l_src_txn_type_id in (51,53,54) THEN
5215 Begin
5216 select hdr.order_number,line.line_number
5217 into l_order_number,l_line_number
5218 from oe_order_headers_all hdr
5219 ,oe_order_lines_all line
5220 where line.line_id = l_src_txn_id
5221 and hdr.header_id = line.header_id;
5222 Exception
5223 when no_data_found then
5224 null;
5225 End;
5226 FND_MESSAGE.SET_NAME('CSI','CSI_ERROR_INST_DETAILS');
5227 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER',l_order_number);
5228 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
5229 FND_MSG_PUB.Add;
5230 RAISE fnd_api.g_exc_error;
5231 END IF;
5232 END IF; -- Item-srl# not null check
5233 EXCEPTION
5234 WHEN fnd_api.g_exc_error THEN
5235 x_return_status := FND_API.G_RET_STS_ERROR;
5236 END Check_Prior_Txn;
5237 --
5238 FUNCTION Is_Forward_Synch
5239 ( p_instance_id IN NUMBER,
5240 p_stop_all_txn IN VARCHAR2,
5241 p_mtl_txn_id IN NUMBER)
5242 RETURN BOOLEAN IS
5243 --
5244 l_return_value BOOLEAN;
5245 l_recount NUMBER;
5246 l_process_flag VARCHAR2(1) := 'P';
5247 l_mtl_txn_id NUMBER := NVL(p_mtl_txn_id,fnd_api.g_miss_num);
5248 l_def_cr_date DATE := sysdate;
5249 l_mtl_txn_cr_date DATE;
5250 BEGIN
5251 l_return_value := TRUE;
5252 --
5253 IF p_instance_id IS NOT NULL AND
5254 p_instance_id <> FND_API.G_MISS_NUM THEN
5255 l_recount := 0;
5256 IF nvl(p_stop_all_txn,FND_API.G_TRUE) = FND_API.G_TRUE THEN
5257 csi_gen_utility_pvt.put_line('Stop All Txns..');
5258 BEGIN
5259 select count(*)
5260 into l_recount
5261 from CSI_II_FORWARD_SYNC_TEMP
5262 where instance_id = p_instance_id
5263 and nvl(process_flag,'N') <> l_process_flag
5264 and ROWNUM = 1;
5265 EXCEPTION
5266 WHEN OTHERS THEN
5267 l_recount := 0;
5268 END;
5269 --
5270 ELSE
5271 csi_gen_utility_pvt.put_line('Stop Later Txns..');
5272 IF l_mtl_txn_id <> fnd_api.g_miss_num THEN
5273 Begin
5274 select creation_date
5275 into l_mtl_txn_cr_date
5276 from MTL_MATERIAL_TRANSACTIONS
5277 where transaction_id = l_mtl_txn_id;
5278 Exception
5279 when no_data_found then
5280 l_mtl_txn_cr_date := sysdate;
5281 End;
5282 ELSE
5283 l_mtl_txn_cr_date := sysdate;
5284 END IF;
5285 --
5286 BEGIN
5287 select count(*)
5288 into l_recount
5289 from CSI_II_FORWARD_SYNC_TEMP
5290 where instance_id = p_instance_id
5291 and nvl(process_flag,'N') <> l_process_flag
5292 and nvl(mtl_txn_creation_date,l_def_cr_date) < l_mtl_txn_cr_date
5293 and ROWNUM = 1;
5294 EXCEPTION
5295 WHEN OTHERS THEN
5296 l_recount := 0;
5297 END;
5298 END IF;
5299 --
5300 IF nvl(l_recount,0) > 0 THEN -- Forward Synch not performed
5301 l_return_value := FALSE;
5302 ELSE
5303 l_return_value := TRUE;
5304 END IF;
5305 --
5306 RETURN l_return_value;
5307 END IF;
5308 RETURN l_return_value;
5309 END Is_Forward_Synch;
5310 --
5311 FUNCTION Is_Valid_Master_Org
5312 ( p_master_org_id IN NUMBER )
5313 RETURN BOOLEAN IS
5314 --
5315 l_return_value BOOLEAN;
5316 l_exists VARCHAR2(1);
5317 BEGIN
5318 l_return_value := TRUE;
5319 IF p_master_org_id IS NULL OR
5320 p_master_org_id = FND_API.G_MISS_NUM THEN
5321 l_return_value := FALSE;
5322 RETURN l_return_value;
5323 END IF;
5324 --
5325 Begin
5326 select 'x'
5327 into l_exists
5328 from MTL_PARAMETERS
5329 where organization_id = p_master_org_id
5330 and master_organization_id = p_master_org_id;
5331 l_return_value := TRUE;
5332 Exception
5333 when no_data_found then
5334 l_return_value := FALSE;
5335 End;
5336 --
5337 RETURN l_return_value;
5338 END Is_Valid_Master_Org;
5339 --
5340 /*-------------------------------------------------------------------------
5341 Procedure Name : Create_hdr_xml
5342 Description : creates a batch validation header message.
5343 --------------------------------------------------------------------------*/
5344
5345 PROCEDURE Create_hdr_xml
5346 ( p_config_hdr_id IN NUMBER
5347 , p_config_rev_nbr IN NUMBER
5348 , p_config_inst_hdr_id IN NUMBER
5349 , x_xml_hdr OUT NOCOPY VARCHAR2 -- this needs to be passed to Send_input_xml
5350 , x_return_status OUT NOCOPY VARCHAR2 )
5351 IS
5352 /*TYPE param_name_type IS TABLE OF VARCHAR2(30)
5353 INDEX BY BINARY_INTEGER;
5354 TYPE param_value_type IS TABLE OF VARCHAR2(200)
5355 INDEX BY BINARY_INTEGER;*/
5356 param_name csi_datastructures_pub.parameter_name;
5357 param_value csi_datastructures_pub.parameter_value;
5358 l_rec_index BINARY_INTEGER;
5359 -- SPC specific params
5360 l_database_id VARCHAR2(100);
5361 l_save_config_behavior VARCHAR2(30):= 'new_config';
5362 l_ui_type VARCHAR2(30):= NULL;
5363 l_msg_behavior VARCHAR2(30):= 'brief';
5364 l_config_header_id VARCHAR2(80);
5365 l_config_rev_nbr VARCHAR2(80);
5366 l_count NUMBER;
5367 -- message related
5368 l_xml_hdr VARCHAR2(2000):= '<initialize>';
5369 l_dummy VARCHAR2(500) := NULL;
5370 l_debug_level NUMBER;
5371 l_icx_session_ticket VARCHAR2(200);
5372 BEGIN
5373 x_return_status:= FND_API.G_RET_STS_SUCCESS;
5374
5375
5376
5377 -- Now set the values from model_rec and org_id
5378 l_config_header_id := to_char(p_config_hdr_id);
5379 l_config_rev_nbr := to_char(p_config_rev_nbr);
5380
5381
5382 csi_gen_utility_pvt.put_line('Queried from oe_lines: ' ||
5383 ' config-hdr: ' || l_config_header_id ||
5384 ' config-rev: ' || l_config_rev_nbr );
5385
5386 -- profiles and env. variables.
5387 l_database_id := fnd_web_config.database_id;
5388 l_icx_session_ticket := cz_cf_api.icx_session_ticket;
5389
5390 csi_gen_utility_pvt.put_line('database_id: '||l_database_id);--,2);
5391
5392 -- Set param_names
5393 -- Always required
5394 param_name(1) := 'ui_type'; -- we are passing null
5395 -- DB connection parameters
5396 param_name(2) := 'database_id'; -- passing fnd_web_config.database_id
5397 -- Dates related parameters
5398 -- param_name(3) := 'config_creation_date'; -- passing oe_order_lines.creation_date
5399 -- Applicability parameters i.e model identification parameters
5400 -- 1 set is required
5401 -- Set 2 is not required . according to spec this set is used when the
5402 -- previously saved configuration is restored. I think this can be used in
5403 -- update_item_instance instead of create_item_instance.
5404 param_name(3) := 'config_header_id'; -- value is l_config_header_id
5405 param_name(4) := 'config_rev_nbr'; -- value is l_config_rev_nbr
5406
5407 -- Other applicability parameters
5408 param_name(5) := 'calling_application_id'; -- Required parameter
5409 param_name(6) := 'sbm_flag'; -- TRUE is passed
5410 param_name(7) := 'responsibility_id';
5411 param_name(8) := 'save_config_behavior';
5412 param_name(9) := 'terminate_msg_behavior';
5413 param_name(10) := 'validation_context';
5414 param_name(11) := 'suppress_baseline_errors';
5415 param_name(12) := 'icx_session_ticket';
5416
5417 -- In spec the following 2 parameters were newly added.
5418
5419
5420
5421
5422 l_count := 12;
5423
5424 param_value(1) := l_ui_type;
5425 param_value(2) := l_database_id;
5426 -- param_value(3) := to_char(l_line_rec.creation_date,'MM-DD-YYYY-HH24-MI-SS');
5427 param_value(3) := l_config_header_id;
5428 param_value(4) := l_config_rev_nbr;
5429 param_value(5) := fnd_profile.value('RESP_APPL_ID');
5430 param_value(6) := 'TRUE';
5431 param_value(7) := fnd_profile.value('RESP_ID');
5432 param_value(8) := l_save_config_behavior;
5433 param_value(9) := l_msg_behavior;
5434 param_value(10) := 'INSTALLED';
5435 param_value(11) := 'TRUE';
5436 param_value(12) := l_icx_session_ticket;
5437
5438
5439 csi_gen_utility_pvt.put_line('Inside Create_hdr_xml, parameters are set');
5440 l_rec_index := 1;
5441 LOOP
5442 IF (param_value(l_rec_index) IS NOT NULL) THEN
5443 l_dummy := '<param name=' ||
5444 '"' || param_name(l_rec_index) || '"'
5445 ||'>'|| param_value(l_rec_index) ||
5446 '</param>';
5447 l_xml_hdr := l_xml_hdr || l_dummy;
5448 END IF;
5449 l_dummy := NULL;
5450 l_rec_index := l_rec_index + 1;
5451 EXIT WHEN l_rec_index > l_count;
5452 END LOOP;
5453
5454 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
5455
5456 IF (l_debug_level > 0) THEN
5457 csi_gen_utility_pvt.put_line( 'Call to batch validation ');
5458 END IF;
5459
5460 IF (l_debug_level > 1) THEN
5461 csi_gen_utility_pvt.dump_call_batch_val
5462 ( p_api_version => 1.0
5463 ,p_init_msg_list => fnd_api.g_false
5464 ,p_parameter_name => param_name
5465 ,p_parameter_value => param_value
5466 );
5467 END IF;
5468
5469
5470
5471
5472 l_xml_hdr := l_xml_hdr ||'<instance header_id='||'"'||p_config_inst_hdr_id||'"'||'/>';
5473 -- add termination tags
5474 l_xml_hdr := l_xml_hdr || '</initialize>';
5475 l_xml_hdr := REPLACE(l_xml_hdr, ' ' , '+');
5476
5477 csi_gen_utility_pvt.put_line(' ');
5478 csi_gen_utility_pvt.put_line
5479 ('1st Part of Create_hdr_xml is : '||SUBSTR(l_xml_hdr, 1, 200) );
5480 csi_gen_utility_pvt.put_line(' ');
5481 csi_gen_utility_pvt.put_line
5482 ('2nd Part of Create_hdr_xml is : '||SUBSTR(l_xml_hdr, 201, 200) );
5483 csi_gen_utility_pvt.put_line(' ');
5484 csi_gen_utility_pvt.put_line
5485 ('3rd Part of Create_hdr_xml is : '||SUBSTR(l_xml_hdr, 401, 200) );
5486 csi_gen_utility_pvt.put_line(' ');
5487 csi_gen_utility_pvt.put_line
5488 ('4th Part of Create_hdr_xml is : '||SUBSTR(l_xml_hdr, 601, 200) );
5489
5490 x_xml_hdr := l_xml_hdr;
5491 csi_gen_utility_pvt.put_line(' ');
5492 csi_gen_utility_pvt.put_line('length of ini msg:' || length(l_xml_hdr));
5493 csi_gen_utility_pvt.put_line('Leaving Create_hdr_xml');
5494 csi_gen_utility_pvt.put_line('------------------------------------- ');
5495 EXCEPTION
5496 WHEN OTHERS THEN
5497 csi_gen_utility_pvt.put_line('exception in create_hdr_xml '|| sqlerrm);
5498 x_return_status := FND_API.G_RET_STS_ERROR;
5499 END Create_hdr_xml;
5500
5501
5502
5503 -- create xml message, send it to ui manager
5504 -- get back pieces of xml message
5505 -- process them and generate a long output xml message
5506 -- hardcoded :url,user, passwd, gwyuid,fndnam,two_task
5507
5508 /*-------------------------------------------------------------------
5509 Procedure Name : Send_input_xml
5510 Description : sends the xml batch validation message
5511 ---------------------------------------------------------------------*/
5512
5513 PROCEDURE Send_input_xml
5514 ( p_xml_hdr IN VARCHAR2,-- Value passed from Create_hdr_xml
5515 x_out_xml_msg OUT NOCOPY LONG,
5516 x_return_status OUT NOCOPY VARCHAR2 )
5517 IS
5518 l_html_pieces CZ_CF_API.CFG_OUTPUT_PIECES;
5519 l_option CZ_CF_API.INPUT_SELECTION;
5520 l_batch_val_tbl CZ_CF_API.CFG_INPUT_LIST;
5521
5522 --variable to fetch from cursor Get_Options
5523 l_component_code VARCHAR2(1000);
5524 l_configuration_id NUMBER;
5525 -- message related
5526 l_validation_status NUMBER;
5527 l_sequence NUMBER := 0;
5528 l_url VARCHAR2(500):= FND_PROFILE.Value('CZ_UIMGR_URL');
5529 l_rec_index BINARY_INTEGER;
5530 l_xml_hdr VARCHAR2(2000);
5531 l_long_xml LONG := NULL;
5532 l_return_status VARCHAR2(1);
5533
5534 BEGIN
5535 l_return_status := FND_API.G_RET_STS_SUCCESS;
5536
5537 csi_gen_utility_pvt.put_line('Entering Send_input_xml');
5538 csi_gen_utility_pvt.put_line('UImanager url: ' || l_url );
5539
5540 l_xml_hdr := p_xml_hdr;
5541 csi_gen_utility_pvt.put_line('length of ini msg: ' || length(l_xml_hdr));
5542
5543
5544 -- delete previous data.
5545 IF (l_html_pieces.COUNT <> 0) THEN
5546 l_html_pieces.DELETE;
5547 END IF;
5548
5549 cz_network_api_pub.Validate( config_input_list => l_batch_val_tbl ,
5550 init_message => l_xml_hdr ,
5551 config_messages => l_html_pieces ,
5552 validation_status => l_validation_status ,
5553 URL => l_url ,
5554 p_validation_type => cz_api_pub.validate_fulfillment
5555 );
5556
5557 csi_gen_utility_pvt.put_line('After call to batch validation the status is : '||l_validation_status );
5558
5559
5560 IF l_validation_status <> 0 THEN
5561 l_return_status := FND_API.G_RET_STS_ERROR;
5562 FND_MESSAGE.Set_Name('CSI', 'CSI_BATCH_VALIDATE');
5563 FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from cz_network_api_pub.Validate, validation_status is: '||l_validation_status);
5564 FND_MSG_PUB.ADD;
5565 END IF;
5566
5567 IF (l_html_pieces.COUNT <= 0) THEN
5568 l_return_status := FND_API.G_RET_STS_ERROR;
5569 FND_MESSAGE.Set_Name('CSI', 'CSI_BATCH_VALIDATE');
5570 FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from cz_network_api_pub.Validate, html_pieces count is <= 0' );
5571 FND_MSG_PUB.ADD;
5572 END IF;
5573
5574
5575 IF l_html_pieces.COUNT >0
5576 THEN
5577 l_rec_index := l_html_pieces.FIRST;
5578 LOOP
5579 csi_gen_utility_pvt.put_line(l_rec_index ||': Part of output_message: ' ||
5580 SUBSTR(l_html_pieces(l_rec_index), 1, 100) );
5581
5582 l_long_xml := l_long_xml || l_html_pieces(l_rec_index);
5583
5584 EXIT WHEN l_rec_index = l_html_pieces.LAST;
5585 l_rec_index := l_html_pieces.NEXT(l_rec_index);
5586
5587 END LOOP;
5588 END IF;
5589
5590 -- if everything ok, set out values
5591 x_out_xml_msg := l_long_xml;
5592 x_return_status := l_return_status;
5593 csi_gen_utility_pvt.put_line('Exiting csi_config_util.Send_input_xml');
5594 EXCEPTION
5595 WHEN OTHERS THEN
5596 x_return_status := FND_API.G_RET_STS_ERROR;
5597 csi_gen_utility_pvt.put_line( 'Inside Send_input_xml when others exception: ' ||substr(sqlerrm,1,100));
5598 END Send_input_xml;
5599
5600 PROCEDURE Parse_output_xml
5601 ( p_xml IN LONG,
5602 x_config_hdr_id OUT NOCOPY NUMBER,
5603 x_config_rev_nbr OUT NOCOPY NUMBER,
5604 x_return_status OUT NOCOPY VARCHAR2 )
5605 IS
5606
5607 CURSOR messages(p_config_hdr_id NUMBER, p_config_rev_nbr NUMBER) is
5608 SELECT constraint_type , message
5609 FROM cz_config_messages
5610 WHERE config_hdr_id = p_config_hdr_id
5611 AND config_rev_nbr = p_config_rev_nbr;
5612
5613
5614 l_exit_start_tag VARCHAR2(20) := '<exit>';
5615 l_exit_end_tag VARCHAR2(20) := '</exit>';
5616 l_exit_start_pos NUMBER;
5617 l_exit_end_pos NUMBER;
5618
5619 l_valid_config_start_tag VARCHAR2(30) := '<valid_configuration>';
5620 l_valid_config_end_tag VARCHAR2(30) := '</valid_configuration>';
5621 l_valid_config_start_pos NUMBER;
5622 l_valid_config_end_pos NUMBER;
5623
5624 l_complete_config_start_tag VARCHAR2(30) := '<complete_configuration>';
5625 l_complete_config_end_tag VARCHAR2(30) := '</complete_configuration>';
5626 l_complete_config_start_pos NUMBER;
5627 l_complete_config_end_pos NUMBER;
5628
5629 l_config_header_id_start_tag VARCHAR2(20) := '<config_header_id>';
5630 l_config_header_id_end_tag VARCHAR2(20) := '</config_header_id>';
5631 l_config_header_id_start_pos NUMBER;
5632 l_config_header_id_end_pos NUMBER;
5633
5634 l_config_rev_nbr_start_tag VARCHAR2(20) := '<config_rev_nbr>';
5635 l_config_rev_nbr_end_tag VARCHAR2(20) := '</config_rev_nbr>';
5636 l_config_rev_nbr_start_pos NUMBER;
5637 l_config_rev_nbr_end_pos NUMBER;
5638
5639 l_message_text_start_tag VARCHAR2(20) := '<message_text>';
5640 l_message_text_end_tag VARCHAR2(20) := '</message_text>';
5641 l_message_text_start_pos NUMBER;
5642 l_message_text_end_pos NUMBER;
5643
5644 l_message_type_start_tag VARCHAR2(20) := '<message_type>';
5645 l_message_type_end_tag VARCHAR2(20) := '</message_type>';
5646 l_message_type_start_pos NUMBER;
5647 l_message_type_end_pos NUMBER;
5648
5649 l_exit VARCHAR(20);
5650 l_config_header_id NUMBER;
5651 l_config_rev_nbr NUMBER;
5652 l_message_text VARCHAR2(2000);
5653 l_message_type VARCHAR2(200);
5654 l_list_price NUMBER;
5655 l_selection_line_id NUMBER;
5656 l_valid_config VARCHAR2(10);
5657 l_complete_config VARCHAR2(10);
5658 l_header_id NUMBER;
5659 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
5660 l_return_status_del VARCHAR2(1);
5661 l_msg VARCHAR2(2000);
5662 l_constraint VARCHAR2(16);
5663 l_flag VARCHAR2(1) := 'N';
5664
5665 BEGIN
5666
5667 csi_gen_utility_pvt.put_line('Entering Parse_output_xml');
5668
5669 l_exit_start_pos :=
5670 INSTR(p_xml, l_exit_start_tag,1, 1) +
5671 length(l_exit_start_tag);
5672
5673 l_exit_end_pos :=
5674 INSTR(p_xml, l_exit_end_tag,1, 1) - 1;
5675
5676 l_exit := SUBSTR (p_xml, l_exit_start_pos,
5677 l_exit_end_pos - l_exit_start_pos + 1);
5678
5679 csi_gen_utility_pvt.put_line('l_exit: '||l_exit);
5680
5681 -- if error go to msg etc.
5682 IF nvl(l_exit,'error') <> 'error' THEN
5683
5684 l_valid_config_start_pos :=
5685 INSTR(p_xml, l_valid_config_start_tag,1, 1) +length(l_valid_config_start_tag);
5686
5687 l_valid_config_end_pos :=
5688 INSTR(p_xml, l_valid_config_end_tag,1, 1) - 1;
5689
5690 l_valid_config := SUBSTR( p_xml, l_valid_config_start_pos,
5691 l_valid_config_end_pos -
5692 l_valid_config_start_pos + 1);
5693
5694 csi_gen_utility_pvt.put_line('l_valid_config: '|| l_valid_config);
5695
5696 l_complete_config_start_pos :=
5697 INSTR(p_xml, l_complete_config_start_tag,1, 1)+length(l_complete_config_start_tag);
5698 l_complete_config_end_pos :=
5699 INSTR(p_xml, l_complete_config_end_tag,1, 1) - 1;
5700
5701 l_complete_config := SUBSTR( p_xml, l_complete_config_start_pos,
5702 l_complete_config_end_pos -
5703 l_complete_config_start_pos + 1);
5704
5705 csi_gen_utility_pvt.put_line('l_complete_config '|| l_complete_config);
5706
5707
5708 IF (nvl(l_valid_config, 'N') <> 'true') THEN
5709 csi_gen_utility_pvt.put_line(' Returned valid_flag as null/false');
5710 l_flag := 'Y';
5711 END IF ;
5712
5713
5714 IF (nvl(l_complete_config, 'N') <> 'true' ) THEN
5715 csi_gen_utility_pvt.put_line('Returned complete_flag as null/false');
5716 l_flag := 'Y';
5717 END IF;
5718
5719
5720 END IF; /* if not error */
5721
5722
5723
5724 l_message_text_start_pos :=
5725 INSTR(p_xml, l_message_text_start_tag,1, 1)+length(l_message_text_start_tag);
5726 l_message_text_end_pos :=
5727 INSTR(p_xml, l_message_text_end_tag,1, 1) - 1;
5728
5729 l_message_text := SUBSTR( p_xml, l_message_text_start_pos,
5730 l_message_text_end_pos -
5731 l_message_text_start_pos + 1);
5732
5733 csi_gen_utility_pvt.put_line('l_message_text is: '||l_message_text);
5734
5735 l_message_type_start_pos :=
5736 INSTR(p_xml, l_message_type_start_tag,1, 1)+length(l_message_type_start_tag);
5737 l_message_type_end_pos :=
5738 INSTR(p_xml, l_message_type_end_tag,1, 1) - 1;
5739
5740 l_message_type := SUBSTR( p_xml, l_message_type_start_pos,
5741 l_message_type_end_pos -
5742 l_message_type_start_pos + 1);
5743
5744
5745 -- get the latest config_header_id, and rev_nbr to get
5746 -- messages if any.
5747
5748
5749 csi_gen_utility_pvt.put_line('l_message_type is : '|| l_message_type);
5750
5751
5752 l_config_header_id_start_pos :=
5753 INSTR(p_xml, l_config_header_id_start_tag, 1, 1)+length(l_config_header_id_start_tag);
5754
5755 l_config_header_id_end_pos :=
5756 INSTR(p_xml, l_config_header_id_end_tag, 1, 1) - 1;
5757
5758 l_config_header_id :=
5759 to_number(SUBSTR( p_xml,l_config_header_id_start_pos,
5760 l_config_header_id_end_pos -
5761 l_config_header_id_start_pos + 1));
5762
5763
5764 l_config_rev_nbr_start_pos :=
5765 INSTR(p_xml, l_config_rev_nbr_start_tag, 1, 1)+length(l_config_rev_nbr_start_tag);
5766
5767 l_config_rev_nbr_end_pos :=
5768 INSTR(p_xml, l_config_rev_nbr_end_tag, 1, 1) - 1;
5769
5770 l_config_rev_nbr :=
5771 to_number(SUBSTR( p_xml,l_config_rev_nbr_start_pos,
5772 l_config_rev_nbr_end_pos -
5773 l_config_rev_nbr_start_pos + 1));
5774
5775 csi_gen_utility_pvt.put_line('Returned config_header_id as:' ||to_char(l_config_header_id));
5776 csi_gen_utility_pvt.put_line('Returned config_rev_nbr as:' ||to_char(l_config_rev_nbr));
5777
5778
5779
5780
5781 IF (l_flag = 'Y' ) OR
5782 l_exit is NULL OR
5783 l_exit = 'error' THEN
5784
5785 csi_gen_utility_pvt.put_line('Getting messages from cz_config_messages');
5786
5787 OPEN messages(l_config_header_id, l_config_rev_nbr);
5788
5789 LOOP
5790 FETCH messages INTO l_constraint,l_msg;
5791 EXIT WHEN messages%notfound;
5792
5793 csi_gen_utility_pvt.put_line('msg : '|| substr(l_msg, 1, 250));
5794 END LOOP;
5795 /*
5796 IF nvl(l_valid_config, 'false') = 'false'
5797 OR l_exit = 'error'
5798 THEN
5799 l_return_status:=FND_API.G_RET_STS_ERROR;
5800 FND_MESSAGE.Set_Name('CSI', 'CSI_BATCH_VALIDATE');
5801 FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from cz_network_api_pub.Validate, from Parse_output_xml ' );
5802 FND_MSG_PUB.ADD;
5803 csi_gen_utility_pvt.put_line('Configuration is invalid/incomplete');
5804 END IF;
5805 */
5806
5807
5808
5809
5810 END IF;
5811 x_config_hdr_id := l_config_header_id;
5812 x_config_rev_nbr := l_config_rev_nbr;
5813
5814 -- if everything ok, set return values
5815 x_return_status := l_return_status;
5816
5817
5818 csi_gen_utility_pvt.put_line('Exiting parse_output_xml');
5819
5820 EXCEPTION
5821 WHEN OTHERS THEN
5822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5823 csi_gen_utility_pvt.put_line( 'Parse_Output_xml error: ' || substr(sqlerrm,1,100));
5824
5825 END Parse_output_xml;
5826
5827 -- End addition by sguthiva for att enhancements
5828
5829 FUNCTION Check_for_eam_item
5830 (p_inventory_item_id IN NUMBER,
5831 p_organization_id IN NUMBER,
5832 p_eam_item_type IN NUMBER)
5833 RETURN BOOLEAN
5834 IS
5835 l_eam NUMBER;
5836 BEGIN
5837 IF nvl(p_eam_item_type,-99) <> FND_API.G_MISS_NUM
5838 THEN
5839 IF p_eam_item_type in (1,3) THEN
5840 RETURN TRUE;
5841 ELSE
5842 RETURN FALSE;
5843 END IF;
5844 ELSE
5845 SELECT eam_item_type
5846 INTO l_eam
5847 FROM mtl_system_items_b
5848 WHERE inventory_item_id = p_inventory_item_id
5849 AND organization_id = p_organization_id;
5850
5851 IF l_eam IN (1,3)
5852 THEN
5853 RETURN TRUE;
5854 ELSE
5855 RETURN FALSE;
5856 END IF;
5857 END IF;
5858 EXCEPTION
5859 WHEN NO_DATA_FOUND THEN
5860 RETURN FALSE;
5861 END Check_for_eam_item;
5862
5863 FUNCTION pending_in_oi_or_tld(
5864 p_inventory_item_id IN number,
5865 p_serial_number IN varchar2)
5866 RETURN boolean IS
5867
5868 CURSOR tld_cur IS
5869 SELECT ctl.source_transaction_table,
5870 ctl.source_transaction_id
5871 FROM csi_t_txn_line_details ctld,
5872 csi_t_transaction_lines ctl
5873 WHERE ctld.inventory_item_id = p_inventory_item_id
5874 AND ctld.serial_number = p_serial_number
5875 AND nvl(ctld.processing_status, 'SUBMIT') <> 'PROCESSED'
5876 AND ctl.transaction_line_id = ctld.transaction_line_id;
5877
5878 CURSOR oi_cur IS
5879 SELECT '1'
5880 FROM csi_instance_interface
5881 WHERE inventory_item_id = p_inventory_item_id
5882 AND serial_number = p_serial_number
5883 AND process_status <> 'P';
5884
5885 BEGIN
5886
5887 FOR tld_rec IN tld_cur
5888 LOOP
5889 fnd_message.set_name('CSI','CSI_SRL_PENDING_IN_TLD');
5890 fnd_message.set_token('SRC_TBL', tld_rec.source_transaction_table);
5891 fnd_message.set_token('SRC_ID', tld_rec.source_transaction_id);
5892 fnd_msg_pub.add;
5893 RAISE fnd_api.g_exc_error;
5894 END LOOP;
5895
5896 FOR oi_rec IN oi_cur
5897 LOOP
5898 fnd_message.set_name('CSI','CSI_SRL_PENDING_IN_OI');
5899 fnd_msg_pub.add;
5900 RAISE fnd_api.g_exc_error;
5901 END LOOP;
5902
5903 RETURN FALSE;
5904
5905 EXCEPTION
5906 WHEN fnd_api.g_exc_error THEN
5907 RETURN TRUE;
5908 END pending_in_oi_or_tld;
5909
5910 PROCEDURE validate_serial_for_upd(
5911 p_instance_rec IN csi_datastructures_pub.instance_rec,
5912 p_txn_rec IN csi_datastructures_pub.transaction_rec,
5913 p_old_serial_number IN varchar2,
5914 x_return_status OUT nocopy varchar2)
5915 IS
5916
5917 l_gen_object_id number;
5918 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
5919 l_current_txn_id NUMBER; --uncommented code for 6965008
5920 l_rec_count NUMBER;
5921
5922 l_current_status NUMBER; -- added for 6176621
5923
5924 CURSOR mog_cur(p_gen_object_id IN number) IS
5925 SELECT 'Y'
5926 FROM mtl_object_genealogy mog
5927 WHERE mog.parent_object_type = 2
5928 AND (mog.object_id = p_gen_object_id OR mog.parent_object_id = p_gen_object_id)
5929 AND mog.object_type = 2
5930 AND sysdate BETWEEN nvl(mog.start_date_active, sysdate-1)
5931 AND nvl(mog.end_date_active, sysdate+1)
5932 AND ROWNUM = 1;
5933 --
5934 CURSOR ALL_TXN_CUR(p_item_id IN NUMBER,p_srl_num IN VARCHAR2,p_curr_txn_id IN NUMBER) IS
5935 select '1'
5936 FROM mtl_unit_transactions mut,
5937 mtl_material_transactions mmt
5938 WHERE mut.inventory_item_id = p_item_id
5939 AND mut.serial_number = p_srl_num
5940 AND mmt.transaction_id = mut.transaction_id
5941 AND mmt.transaction_id <> p_curr_txn_id
5942 AND ROWNUM = 1
5943 UNION --uncommented code for 6965008
5944 select '1'
5945 FROM mtl_unit_transactions mut,
5946 mtl_transaction_lot_numbers mtln,
5947 mtl_material_transactions mmt
5948 WHERE mut.inventory_item_id = p_item_id
5949 AND mut.serial_number = p_srl_num
5950 AND mtln.organization_id = mut.organization_id
5951 AND mtln.serial_transaction_id = mut.transaction_id
5952 AND mmt.transaction_id = mtln.transaction_id
5953 AND mmt.transaction_id <> p_curr_txn_id
5954 AND ROWNUM = 1;
5955 --
5956 BEGIN
5957
5958 x_return_status := fnd_api.g_ret_sts_success;
5959
5960 IF p_txn_rec.transaction_type_id <> 205 THEN --Added condition and uncommented the code for bug 6965008
5961 IF p_txn_rec.inv_material_transaction_id IS NULL OR
5962 p_txn_rec.inv_material_transaction_id = FND_API.G_MISS_NUM THEN
5963 l_current_txn_id := -99999;
5964 ELSE
5965 l_current_txn_id := p_txn_rec.inv_material_transaction_id;
5966 END IF;
5967 --
5968 IF p_instance_rec.location_type_code IN
5969 ('INVENTORY', 'IN_TRANSIT', 'PROJECT', 'PO') --Removed INTERNAL_SITE for bug 5168249
5970 THEN
5971 fnd_message.set_name('CSI', 'CSI_SRL_IN_INT_CANNOT_UPD');
5972 fnd_message.set_token('INST_NUM', p_instance_rec.instance_number);
5973 fnd_message.set_token('LOC_TYPE_CODE', p_instance_rec.location_type_code);
5974 fnd_msg_pub.add;
5975 RAISE fnd_api.g_exc_error;
5976 END IF;
5977 END IF;
5978
5979 -- Added the following IF to handle NULL to NOT NULL serial update
5980 IF p_old_serial_number IS NOT NULL AND
5981 p_old_serial_number <> fnd_api.g_miss_char THEN
5982
5983 IF p_txn_rec.transaction_type_id <> 205 THEN --Added condition and uncommented the code for bug 6965008
5984 -- check for existence in mut and error
5985 FOR all_txn_rec IN all_txn_cur(p_instance_rec.inventory_item_id,p_old_serial_number,-9999)
5986 LOOP
5987 fnd_message.set_name('CSI', 'CSI_OLD_SRL_HAS_TXN_CANNOT_UPD');
5988 fnd_message.set_token('SERIAL_NUM', p_old_serial_number);
5989 fnd_msg_pub.add;
5990 RAISE fnd_api.g_exc_error;
5991 END LOOP;
5992
5993 --uncommented the code and brought the code up for bug 6965008
5994
5995 -- Check whether EAM Work Order Exists for this Serial Number.
5996 -- From R12 release, EAM work Order will always have the item instance reference.
5997 -- Indirectly, the validation is done for the old serial number.
5998 -- This need not have to be performed for the new serial number because if an item instance
5999 -- exists for the new one, updating the current instance with that serial number would
6000 -- lead to serial uniqueness violation. Since our uniqueness validation catches that, we are
6001 -- performing this only for the old serial number.
6002 --
6003 l_rec_count := 0;
6004 --
6005 select count(*)
6006 into l_rec_count
6007 from EAM_WORK_ORDER_DETAILS ewod,
6008 WIP_DISCRETE_JOBS wdj
6009 where wdj.wip_entity_id = ewod.wip_entity_id
6010 and wdj.organization_id = ewod.organization_id
6011 and wdj.maintenance_object_type = 3
6012 and wdj.maintenance_object_id = p_instance_rec.instance_id
6013 and wdj.maintenance_object_source = 1
6014 and ROWNUM = 1;
6015 --
6016 IF l_rec_count > 0 THEN
6017 fnd_message.set_name('CSI', 'CSI_OLD_SRL_HAS_EAM_CANNOT_UPD');
6018 fnd_message.set_token('SERIAL_NUM', p_old_serial_number);
6019 fnd_msg_pub.add;
6020 RAISE fnd_api.g_exc_error;
6021 END IF;
6022 END IF;
6023
6024 BEGIN
6025 SELECT gen_object_id,current_status --changed for 6176621
6026 INTO l_gen_object_id,
6027 l_current_status
6028 FROM mtl_serial_numbers
6029 WHERE inventory_item_id = p_instance_rec.inventory_item_id
6030 AND serial_number = p_old_serial_number;
6031
6032 --start of code fix for 6176621
6033 IF l_current_status <> 4 THEN
6034 fnd_message.set_name('CSI', 'CSI_SRL_IN_USE_CANNOT_UPD');
6035 fnd_message.set_token('SERIAL_NUM',p_old_serial_number);
6036 fnd_msg_pub.add;
6037 RAISE fnd_api.g_exc_error;
6038 END IF;
6039 --code fix end for 6176621
6040 --
6041 FOR mog_rec IN mog_cur(l_gen_object_id)
6042 LOOP
6043 fnd_message.set_name('CSI', 'CSI_SRL_IN_MOG_CANNOT_UPD');
6044 fnd_message.set_token('SERIAL_NUM',p_old_serial_number);
6045 fnd_msg_pub.add;
6046 RAISE fnd_api.g_exc_error;
6047 END LOOP;
6048
6049 -- check pending transaction in open interface and installation detail references
6050 IF pending_in_oi_or_tld(p_instance_rec.inventory_item_id, p_old_serial_number) THEN
6051 RAISE fnd_api.g_exc_error;
6052 END IF;
6053 --
6054 EXCEPTION
6055 WHEN no_data_found THEN
6056 null;
6057 END;
6058 --
6059 END IF;-- Check Old serial Number not null
6060 --
6061 -- New Serial specific validations
6062 --
6063 -- srramakr When Serialized at SO Issue items are shipped, the staging instance is splitted and then
6064 -- the new instance is updated with the shipped serial# and external location.
6065 -- Under such scenario, when the serial update validation happens for the new serial number,
6066 -- we should ignore the current material transaction as the current txn is one that is updating it.
6067 -- Whenever we filter the serial records based on transaction_id, we cannot use MUT always.
6068 -- If the item is lot-serial controlled then it needs to be joined with MTLN.
6069 --
6070 IF nvl(p_old_serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
6071 AND p_txn_rec.transaction_type_id <> 205 --Added condition and uncommented the code for bug 6965008
6072 THEN
6073 FOR all_txn_rec IN all_txn_cur(p_instance_rec.inventory_item_id,
6074 p_instance_rec.serial_number,
6075 l_current_txn_id)
6076 LOOP
6077 fnd_message.set_name('CSI', 'CSI_SRL_HAS_TXN_CANNOT_UPD');
6078 fnd_message.set_token('SERIAL_NUM', p_instance_rec.serial_number);
6079 fnd_msg_pub.add;
6080 RAISE fnd_api.g_exc_error;
6081 END LOOP;
6082 END IF;
6083 ----uncommented code for 6965008
6084 fnd_message.set_name('CSI', 'CSI_SERIAL_UPD_WARNING');
6085 fnd_message.set_token('CURRENT_SERIAL', p_old_serial_number);
6086 fnd_message.set_token('NEW_SERIAL', p_instance_rec.serial_number);
6087 fnd_msg_pub.add;
6088 --
6089 -- Warning Status should be handled by Public API based ont Serial Number Update Event
6090 -- x_return_status := 'W';
6091 EXCEPTION
6092 WHEN fnd_api.g_exc_error THEN
6093 x_return_status := fnd_api.g_ret_sts_error;
6094 END validate_serial_for_upd;
6095 --
6096 /*-----------------------------------------------------------*/
6097 /* This function gets the version label of an item instance */
6098 /* based on the time stamp passed. */
6099 /*---------------------------------------------------------*/
6100 FUNCTION Get_Version_Label
6101 (
6102 p_instance_id IN NUMBER,
6103 p_time_stamp IN DATE
6104 ) RETURN VARCHAR2 IS
6105 --
6106 l_time_stamp DATE;
6107 l_ver_label VARCHAR2(30);
6108 --
6109 CURSOR VER_LABEL_CUR IS
6110 select version_label
6111 from CSI_I_VERSION_LABELS
6112 where instance_id = p_instance_id
6113 and date_time_stamp <= l_time_stamp
6114 order by date_time_stamp desc;
6115 BEGIN
6116 IF p_time_stamp IS NULL OR
6117 p_time_stamp = FND_API.G_MISS_DATE THEN
6118 l_time_stamp := sysdate + 1;
6119 ELSE
6120 l_time_stamp := p_time_stamp;
6121 END IF;
6122 --
6123 l_ver_label := NULL;
6124 --
6125 OPEN VER_LABEL_CUR;
6126 FETCH VER_LABEL_CUR INTO l_ver_label;
6127 CLOSE VER_LABEL_CUR;
6128 --
6129 RETURN l_ver_label;
6130 END Get_Version_Label;
6131 --
6132
6133
6134 PROCEDURE get_mtl_txn_for_srl(
6135 p_inventory_item_id IN number,
6136 p_serial_number IN varchar2,
6137 x_mtl_txn_tbl OUT nocopy csi_datastructures_pub.mtl_txn_tbl)
6138 IS
6139
6140 l_mtl_txn_id number;
6141 l_lot_number varchar2(30);
6142 l_mtl_txn_tbl csi_datastructures_pub.mtl_txn_tbl;
6143 l_ind binary_integer := 0;
6144 x_ind binary_integer := 0;
6145 l_txn_seq_start_date date;
6146
6147 CURSOR unit_txn_cur IS
6148 SELECT mut.transaction_id,
6149 mut.creation_date,
6150 msi.lot_control_code,
6151 msi.serial_number_control_code,
6152 msi.primary_uom_code
6153 FROM mtl_unit_transactions mut,
6154 mtl_system_items msi
6155 WHERE mut.serial_number = p_serial_number
6156 AND mut.inventory_item_id = p_inventory_item_id
6157 AND msi.organization_id = mut.organization_id
6158 AND msi.inventory_item_id = mut.inventory_item_id
6159 -- need to add this because in a diff ou it this item may not be ib tracked
6160 AND msi.comms_nl_trackable_flag = 'Y'
6161 ORDER BY mut.creation_date desc, mut.transaction_id desc;
6162
6163 BEGIN
6164
6165 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
6166 csi_gen_utility_pvt.populate_install_param_rec;
6167 END IF;
6168
6169 l_txn_seq_start_date := csi_datastructures_pub.g_install_param_rec.txn_seq_start_date;
6170 IF l_txn_seq_start_date is null THEN
6171 l_txn_seq_start_date := csi_datastructures_pub.g_install_param_rec.freeze_date;
6172 END IF;
6173
6174 IF l_txn_seq_start_date is null THEN
6175 fnd_message.set_name('CSI','CSI_API_UNINSTALLED_PARAMETER');
6176 fnd_msg_pub.add;
6177 raise fnd_api.g_exc_error;
6178 END IF;
6179
6180 FOR unit_txn_rec IN unit_txn_cur
6181 LOOP
6182 l_mtl_txn_id := unit_txn_rec.transaction_id;
6183
6184 IF unit_txn_rec.lot_control_code = 2 THEN -- serial is lot controlled in the transacting org
6185 BEGIN
6186 SELECT transaction_id,
6187 lot_number
6188 INTO l_mtl_txn_id,
6189 l_lot_number
6190 FROM mtl_transaction_lot_numbers
6191 WHERE serial_transaction_id = unit_txn_rec.transaction_id;
6192 EXCEPTION
6193 WHEN no_data_found THEN
6194 l_mtl_txn_id := unit_txn_rec.transaction_id;
6195 l_lot_number := null;
6196 END;
6197 END IF;
6198
6199 l_ind := l_ind + 1;
6200
6201 l_mtl_txn_tbl(l_ind).transaction_id := l_mtl_txn_id;
6202
6203 SELECT inventory_item_id,
6204 organization_id,
6205 transaction_date,
6206 creation_date,
6207 transfer_transaction_id,
6208 transaction_type_id,
6209 transaction_action_id,
6210 transaction_source_type_id,
6211 transaction_quantity,
6212 transaction_uom,
6213 primary_quantity,
6214 transaction_source_id,
6215 trx_source_line_id
6216 INTO l_mtl_txn_tbl(l_ind).inventory_item_id,
6217 l_mtl_txn_tbl(l_ind).organization_id,
6218 l_mtl_txn_tbl(l_ind).transaction_date,
6219 l_mtl_txn_tbl(l_ind).creation_date,
6220 l_mtl_txn_tbl(l_ind).transfer_transaction_id,
6221 l_mtl_txn_tbl(l_ind).transaction_type_id,
6222 l_mtl_txn_tbl(l_ind).transaction_action_id,
6223 l_mtl_txn_tbl(l_ind).transaction_source_type_id,
6224 l_mtl_txn_tbl(l_ind).transaction_quantity,
6225 l_mtl_txn_tbl(l_ind).transaction_uom,
6226 l_mtl_txn_tbl(l_ind).primary_quantity,
6227 l_mtl_txn_tbl(l_ind).transaction_source_id,
6228 l_mtl_txn_tbl(l_ind).trx_source_line_id
6229 FROM mtl_material_transactions
6230 WHERE transaction_id = l_mtl_txn_id;
6231
6232 l_mtl_txn_tbl(l_ind).serial_control_code := unit_txn_rec.serial_number_control_code;
6233 l_mtl_txn_tbl(l_ind).lot_control_code := unit_txn_rec.lot_control_code;
6234 l_mtl_txn_tbl(l_ind).primary_uom := unit_txn_rec.primary_uom_code;
6235 l_mtl_txn_tbl(l_ind).lot_number := l_lot_number;
6236
6237 END LOOP;
6238
6239 IF l_mtl_txn_tbl.count <> 0 -- 6164506
6240 THEN
6241 FOR l_ind IN l_mtl_txn_tbl.FIRST .. l_mtl_txn_tbl.LAST
6242 LOOP
6243 IF l_mtl_txn_tbl(l_ind).creation_date > l_txn_seq_start_date
6244 AND
6245 csi_inv_trxs_pkg.valid_ib_txn(l_mtl_txn_tbl(l_ind).transaction_id)
6246 THEN
6247 x_ind := x_ind + 1;
6248 x_mtl_txn_tbl(x_ind) := l_mtl_txn_tbl(l_ind);
6249 END IF;
6250 END LOOP;
6251 END IF;
6252 END get_mtl_txn_for_srl;
6253 END csi_Item_Instance_Vld_Pvt;
[Home] [Help]