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