1 PACKAGE BODY AHL_PRD_SERN_CHANGE_PVT AS
2 /* $Header: AHLVSNCB.pls 120.5.12020000.3 2012/12/07 14:42:40 sareepar ship $ */
3 --
4 -----------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME VARCHAR2(30) := 'AHL_PRD_SERN_CHANGE_PVT';
8 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
9
10 -------------------------------------------------
11 -- Declare Local Procedures --
12 -------------------------------------------------
13
14 PROCEDURE GetCSI_Transaction_ID(p_txn_code IN VARCHAR2,
15 x_txn_type_id OUT NOCOPY NUMBER,
16 x_return_val OUT NOCOPY BOOLEAN) IS
17
18 -- For transaction code.
19 CURSOR csi_txn_types_csr(p_txn_code IN VARCHAR2) IS
20 SELECT ctxn.transaction_type_id
21 FROM csi_txn_types ctxn, fnd_application app
22 WHERE ctxn.source_application_id = app.application_id
23 AND app.APPLICATION_SHORT_NAME = 'AHL'
24 AND ctxn.source_transaction_type = p_txn_code;
25
26 l_txn_type_id NUMBER;
27 l_return_val BOOLEAN DEFAULT TRUE;
28
29 BEGIN
30
31 -- get transaction_type_id .
32 OPEN csi_txn_types_csr(p_txn_code);
33 FETCH csi_txn_types_csr INTO l_txn_type_id;
34 IF (csi_txn_types_csr%NOTFOUND) THEN
35 FND_MESSAGE.Set_Name('AHL','AHL__TXNCODE_INVALID');
36 FND_MESSAGE.Set_Token('CODE',p_txn_code);
37 FND_MSG_PUB.ADD;
38 --dbms_output.put_line('Transaction code not found');
39 l_return_val := FALSE;
40 END IF;
41 CLOSE csi_txn_types_csr;
42
43 -- assign out parameters.
44 x_return_val := l_return_val;
45 x_txn_type_id := l_txn_type_id;
46
47
48 END GetCSI_Transaction_ID;
49 --
50 PROCEDURE GetCSI_Attribute_ID (p_attribute_code IN VARCHAR2,
51 x_attribute_id OUT NOCOPY NUMBER,
52 x_return_val OUT NOCOPY BOOLEAN) IS
53
54
55 CURSOR csi_i_ext_attrib_csr(p_attribute_code IN VARCHAR2) IS
56 SELECT attribute_id
57 FROM csi_i_extended_attribs
58 WHERE attribute_level = 'GLOBAL'
59 AND attribute_code = p_attribute_code;
60
61 l_return_val BOOLEAN DEFAULT TRUE;
62 l_attribute_id NUMBER;
63
64 BEGIN
65
66 OPEN csi_i_ext_attrib_csr(p_attribute_code);
67 FETCH csi_i_ext_attrib_csr INTO l_attribute_id;
68 IF (csi_i_ext_attrib_csr%NOTFOUND) THEN
69 l_return_val := FALSE;
70 l_attribute_id := null;
71 END IF;
72 CLOSE csi_i_ext_attrib_csr;
73 x_attribute_id := l_attribute_id;
74 x_return_val := l_return_val;
75
76 END GetCSI_Attribute_ID;
77
78 ---------------------------------------------------------------------
79 -- Procedure to get extended attribute value given the attribute code --
80 ---------------------------------------------------------------------
81 PROCEDURE GetCSI_Attribute_Value (p_csi_instance_id IN NUMBER,
82 p_attribute_code IN VARCHAR2,
83 x_attribute_value OUT NOCOPY VARCHAR2,
84 x_attribute_value_id OUT NOCOPY NUMBER,
85 x_object_version_number OUT NOCOPY NUMBER,
86 x_return_val OUT NOCOPY BOOLEAN) IS
87
88
89 CURSOR csi_i_iea_csr(p_attribute_code IN VARCHAR2,
90 p_csi_instance_id IN NUMBER) IS
91
92 SELECT iea.attribute_value, iea.attribute_value_id, iea.object_version_number
93 FROM csi_i_extended_attribs attb, csi_iea_values iea
94 WHERE attb.attribute_id = iea.attribute_id
95 AND attb.attribute_code = p_attribute_code
96 AND iea.instance_id = p_csi_instance_id
97 AND trunc(sysdate) >= trunc(nvl(iea.active_start_date, sysdate))
98 AND trunc(sysdate) < trunc(nvl(iea.active_end_date, sysdate+1));
99
100 l_return_val BOOLEAN DEFAULT TRUE;
101 l_attribute_value csi_iea_values.attribute_value%TYPE;
102 l_attribute_value_id NUMBER;
103 l_object_version_number NUMBER;
104
105 BEGIN
106
107 OPEN csi_i_iea_csr(p_attribute_code, p_csi_instance_id);
111 l_return_val := FALSE;
108 FETCH csi_i_iea_csr INTO l_attribute_value, l_attribute_value_id,
109 l_object_version_number;
110 IF (csi_i_iea_csr%NOTFOUND) THEN
112 l_attribute_value := null;
113 l_attribute_value_id := null;
114 l_object_version_number := null;
115 END IF;
116
117 CLOSE csi_i_iea_csr;
118 x_attribute_value := l_attribute_value;
119 x_return_val := l_return_val;
120 x_attribute_value_id := l_attribute_value_id;
121 x_object_version_number := l_object_version_number;
122
123 END GetCSI_Attribute_Value;
124
125 --------------------------------------------------------
126 -- Procedure to return lookup code given the meaning --
127 --------------------------------------------------------
128 PROCEDURE Convert_To_LookupCode (p_lookup_type IN VARCHAR2,
129 p_lookup_meaning IN VARCHAR2,
130 x_lookup_code OUT NOCOPY VARCHAR2,
131 x_return_val OUT NOCOPY BOOLEAN) IS
132
133 CURSOR fnd_lookup_csr (p_lookup_type IN VARCHAR2,
134 p_lookup_meaning IN VARCHAR2) IS
135 SELECT lookup_code
136 FROM fnd_lookup_values_vl
137 WHERE lookup_type = p_lookup_type
138 AND meaning = p_lookup_meaning
139 AND TRUNC(SYSDATE) >= TRUNC(NVL(start_date_active, SYSDATE))
140 AND TRUNC(SYSDATE) < TRUNC(NVL(end_date_active, SYSDATE+1));
141
142 l_lookup_code fnd_lookups.lookup_code%TYPE DEFAULT NULL;
143 l_return_val BOOLEAN DEFAULT TRUE;
144
145 BEGIN
146
147 OPEN fnd_lookup_csr(p_lookup_type, p_lookup_meaning);
148 FETCH fnd_lookup_csr INTO l_lookup_code;
149 IF (fnd_lookup_csr%NOTFOUND) THEN
150 l_return_val := FALSE;
151 l_lookup_code := NULL;
152 END IF;
153
154 CLOSE fnd_lookup_csr;
155
156 x_lookup_code := l_lookup_code;
157 x_return_val := l_return_val;
158
159 END Convert_To_LookupCode;
160 --
161 PROCEDURE Validate_SerialNumber(p_Inventory_id IN NUMBER,
162 p_Serial_Number IN VARCHAR2,
163 p_serial_number_control IN NUMBER,
164 p_serialnum_tag_code IN VARCHAR2,
165 p_concatenated_segments IN VARCHAR2) IS
166
167 CURSOR mtl_serial_numbers_csr(c_Inventory_id IN NUMBER,
168 c_Serial_Number IN VARCHAR2) IS
169 SELECT 1
170 FROM mtl_serial_numbers
171 WHERE inventory_item_id = c_Inventory_id
172 AND Serial_Number = c_Serial_Number;
173
174
175 l_junk VARCHAR2(1);
176
177 BEGIN
178
179 -- Validate serial number.(1 = No serial number control; 2 = Pre-defined;
180 -- 3 = Dynamic Entry at inventory receipt.)
181 IF (nvl(p_serial_number_control,0) IN (2,5,6)) THEN
182 -- serial number is mandatory.
183 IF (p_Serial_Number IS NULL) OR (p_Serial_Number = FND_API.G_MISS_CHAR) THEN
184 FND_MESSAGE.Set_Name('AHL','AHL_PRD_SERIAL_NULL');
185 FND_MESSAGE.Set_Token('INV_ITEM',p_concatenated_segments);
186 FND_MSG_PUB.ADD;
187 --dbms_output.put_line('Serial Number is null');
188 ELSE
189 -- If serial tag code = INVENTORY then validate serial number against inventory.
190 IF (p_serialnum_tag_code = 'INVENTORY') THEN
191 OPEN mtl_serial_numbers_csr(p_Inventory_id,p_Serial_Number);
192 FETCH mtl_serial_numbers_csr INTO l_junk;
193 IF (mtl_serial_numbers_csr%NOTFOUND) THEN
194 FND_MESSAGE.Set_Name('AHL','AHL_PRD_SERIAL_INVALID');
195 FND_MESSAGE.Set_Token('SERIAL',p_Serial_Number);
196 FND_MESSAGE.Set_Token('INV_ITEM',p_concatenated_segments);
197 FND_MSG_PUB.ADD;
198 --dbms_output.put_line('Serial Number does not exist in master ');
199 END IF;
200 CLOSE mtl_serial_numbers_csr;
201 END IF;
202
203
204 END IF;
205 ELSE
206 -- if not serialized item, then serial number must be null.
207 IF (p_Serial_Number <> FND_API.G_MISS_CHAR) AND (p_Serial_Number IS NOT NULL) THEN
208 FND_MESSAGE.Set_Name('AHL','AHL_PRD_SERIAL_NOTNULL');
209 FND_MESSAGE.Set_Token('SERIAL',p_Serial_Number);
210 FND_MESSAGE.Set_Token('INV_ITEM',p_concatenated_segments);
211 FND_MSG_PUB.ADD;
212 --dbms_output.put_line('Serial Number is not null');
213 END IF;
214
215 END IF; /* for serial number control */
216 END Validate_SerialNumber;
217 --Function is used mainly in ahl workorders view to get serial tag code
218 FUNCTION get_serialtag_code
219 (
220 p_instance_id IN NUMBER
221 ) RETURN VARCHAR2
222
223 IS
224 -- Changes for ER # 5676360 start
225 Cursor Csi_Iea_Value_Cur(c_instance_id IN NUMBER)
226 IS
227 SELECT cii.attribute_value attribute_value
228 FROM csi_item_instances csi,csi_iea_values cii
232 where instance_id = csi.instance_id);
229 WHERE csi.instance_id = cii.instance_id
230 AND csi.instance_id = c_instance_id
231 AND cii.rowid in ( select max(rowid) from csi_iea_values
233
234 Cursor Csi_Item_Inst_Cur(c_instance_id IN NUMBER)
235 IS
236 SELECT cii.attribute_value attribute_value
237 FROM csi_item_instances csi,csi_iea_values cii
238 WHERE csi.instance_id = cii.instance_id(+)
239 AND csi.instance_id = c_instance_id;
240 -- Changes for ER # 5676360 end
241
242 l_attribute_code VARCHAR2(30);
243 l_api_name CONSTANT VARCHAR2(30) := 'GET_SERIALTAG_CODE';
244 l_api_version CONSTANT NUMBER := 1.0;
245
246 BEGIN
247
248 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
249 fnd_log.string
250 (
251 fnd_log.level_statement,
252 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
253 'Get_Serialtag_code Instance ID : '|| p_instance_id
254 );
255 END IF;
256
257 IF (p_instance_id IS NULL OR
258 p_instance_id = FND_API.G_MISS_NUM) THEN
259 RETURN NULL;
260 END IF;
261 --
262 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
263 fnd_log.string
264 (
265 fnd_log.level_statement,
266 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
267 'Get_Serialtag_code Instance ID : '|| p_instance_id
268 );
269 END IF;
270
271 OPEN Csi_Iea_Value_Cur(p_instance_id);
272 FETCH Csi_Iea_Value_Cur INTO l_attribute_code;
273 CLOSE Csi_Iea_Value_Cur;
274 -- Check if record doesn't exist in csi iea values
275 IF l_attribute_code IS NULL THEN
276 OPEN Csi_Item_Inst_Cur(p_instance_id);
277 FETCH Csi_Item_Inst_Cur INTO l_attribute_code;
278 CLOSE Csi_Item_Inst_Cur;
279 RETURN l_attribute_code;
280 ELSE
281 RETURN l_attribute_code;
282 END IF;
283
284 END get_serialtag_code;
285
286 --Function is used mainly in ahl workorders view to get serial tag code
287 FUNCTION get_serialtag_meaning
288 (
289 p_instance_id IN NUMBER
290 ) RETURN VARCHAR2
291
292 IS
293
294 Cursor Csi_Iea_Value_Cur(c_instance_id IN NUMBER)
295 IS
296 SELECT decode(mfg_Serial_number_flag, 'N',cii.attribute_value,NULL,cii.attribute_value,'INVENTORY') attribute_value
297 FROM csi_item_instances csi,csi_iea_values cii
298 WHERE csi.instance_id = cii.instance_id
299 AND csi.instance_id = c_instance_id
300 AND cii.rowid in ( select max(rowid) from csi_iea_values
301 where instance_id = csi.instance_id);
302
303 Cursor Csi_Item_Inst_Cur(c_instance_id IN NUMBER)
304 IS
305 SELECT decode(mfg_Serial_number_flag, 'N',cii.attribute_value,NULL,cii.attribute_value,'INVENTORY') attribute_value
306 FROM csi_item_instances csi,csi_iea_values cii
307 WHERE csi.instance_id = cii.instance_id(+)
308 AND csi.instance_id = c_instance_id;
309
310 Cursor Serial_Tag_Mean_Cur(c_lookup_code IN VARCHAR2)
311 IS
312 SELECT meaning
313 FROM fnd_lookup_values_vl
314 WHERE lookup_type = 'AHL_SERIALNUMBER_TAG'
315 AND lookup_code = c_lookup_code;
316
317 l_attribute_code VARCHAR2(30);
318 l_attribute_mean VARCHAR2(80);
319
320 BEGIN
321
322 IF p_instance_id IS NOT NULL AND
323 p_instance_id = FND_API.G_MISS_NUM THEN
324 RETURN NULL;
325 END IF;
326 --
327 OPEN Csi_Iea_Value_Cur(p_instance_id);
328 FETCH Csi_Iea_Value_Cur INTO l_attribute_code;
329 CLOSE Csi_Iea_Value_Cur;
330 -- Check if record doesn't exist in csi iea values
331 IF l_attribute_code IS NULL THEN
332 OPEN Csi_Item_Inst_Cur(p_instance_id);
333 FETCH Csi_Item_Inst_Cur INTO l_attribute_code;
334 CLOSE Csi_Item_Inst_Cur;
335 --Get Mening
336 OPEN Serial_Tag_Mean_Cur(l_attribute_code);
337 FETCH Serial_Tag_Mean_Cur INTO l_attribute_mean;
338 CLOSE Serial_Tag_Mean_Cur;
339 RETURN l_attribute_mean;
340 ELSE
341 OPEN Serial_Tag_Mean_Cur(l_attribute_code);
342 FETCH Serial_Tag_Mean_Cur INTO l_attribute_mean;
343 CLOSE Serial_Tag_Mean_Cur;
344 RETURN l_attribute_mean;
345 END IF;
346
347 END get_serialtag_meaning;
348
349 -- Start of Comments --
350 -- Procedure name : Process_Serialnum_Change
351 -- Type : Private
352 -- Function :
353 -- Pre-reqs :
354 -- Parameters :
355 --
356 -- Standard IN Parameters :
357 -- p_api_version IN NUMBER Required
358 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
359 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
360 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
361 -- Based on this flag, the API will set the default attributes.
362 -- p_module_type In VARCHAR2 Default NULL
363 -- This will be null.
364 -- Standard OUT Parameters :
365 -- x_return_status OUT VARCHAR2 Required
366 -- x_msg_count OUT NUMBER Required
367 -- x_msg_data OUT VARCHAR2 Required
368 --
369 -- Process_Serialnum_Change Parameters :
370 -- p_serialnum_change_rec IN Serialnum_Change_Rec_Type, Required
371 -- Adithya added the x_warning_msg_tbl parameter: Bug# 6683990
372 -- x_warning_msg_tbl OUT ahl_uc_validation_pub.error_tbl_type
373 -- List of Serial number change attributes
377 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
374
375 PROCEDURE Process_Serialnum_Change (
376 p_api_version IN NUMBER,
378 p_commit IN VARCHAR2 := FND_API.G_FALSE,
379 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
380 p_serialnum_change_rec IN Sernum_Change_Rec_Type,
381 x_return_status OUT NOCOPY VARCHAR2,
382 x_msg_count OUT NOCOPY NUMBER,
383 x_msg_data OUT NOCOPY VARCHAR2,
384 --Adithya added the x_warning_msg_tbl parameter: Bug# 6683990
385 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type)
386
387 IS
388 --
389 -- Balaji modified the cursor for Item/Serial Change ER -- Begin
390 CURSOR get_workorder_csr (c_workorder_id IN NUMBER,
391 c_job_number IN VARCHAR2)
392 IS
393 SELECT
394 workorder_id,
395 wip_entity_id,
396 job_number,
397 item_instance_id,
398 item_instance_number,
399 organization_id,
400 inventory_item_id
401 FROM
402 ahl_workorders_v
403 WHERE
404 (workorder_id = c_workorder_id
405 OR job_number = c_job_number)
406 AND job_status_code not in (1,4,5,7,12,14,17);
407 -- Balaji modified the cursor for Item/Serial Change ER -- End
408
409 --
410 CURSOR l_uc_exists_cur (c_item_instance_id IN NUMBER)
411 IS
412 SELECT csi_item_instance_id
413 FROM ahl_unit_config_headers uc
414 WHERE csi_item_instance_id in ( SELECT object_id
415 FROM csi_ii_relationships
416 START WITH object_id = c_item_instance_id
417 AND relationship_type_code = 'COMPONENT-OF'
418 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
419 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
420 CONNECT BY PRIOR subject_id = object_id
421 AND relationship_type_code = 'COMPONENT-OF'
422 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
423 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
424 )
425 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
426 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
427
428 -- Get the current record from csi item instances
429 --Adithya added location_type_code Bug# 6683990
430 CURSOR c_instance_details (c_instance_id IN NUMBER)
431 IS
432 SELECT
433 instance_number,
434 instance_id,
435 object_version_number,
436 inventory_item_id,
437 serial_number,
438 wip_job_id,
439 location_type_code
440 FROM
441 csi_item_instances
442 WHERE
443 instance_id = c_instance_id;
444
445 CURSOR mtl_system_items_csr(c_Inventory_id IN NUMBER,
446 c_Organization_id IN NUMBER)
447 IS
448 SELECT
449 serial_number_control_code,
450 lot_control_code,
451 concatenated_segments
452 FROM
453 mtl_system_items_vl
454 WHERE
455 inventory_item_id = c_Inventory_id
456 AND organization_id = c_Organization_id;
457
458 --
459 CURSOR c_get_inv_item_id(c_item_number VARCHAR2)
460 IS
461 SELECT
462 inventory_item_id
463 FROM
464 MTL_SYSTEM_ITEMS_KFV
465 WHERE
466 CONCATENATED_SEGMENTS = c_item_number;
467
468 -- Balaji added cursor for checking if destination item is valid.- Begin
469 CURSOR c_is_item_valid(c_item_number VARCHAR2, c_organization_id NUMBER)
470 IS
471 SELECT
472 inventory_item_id
473 FROM
474 MTL_SYSTEM_ITEMS_KFV
475 WHERE
476 CONCATENATED_SEGMENTS = c_item_number
477 AND organization_id = c_organization_id
481 l_inventory_item_id NUMBER;
478 AND SERIAL_NUMBER_CONTROL_CODE in (2,5,6);
479
480 l_new_inventory_item_id NUMBER;
482 l_organization_id NUMBER;
483 l_junk NUMBER;
484 l_instance_id NUMBER;
485 -- Balaji added cursor for checking if destination item is valid.- End
486
487 -- Arunjk added to cursor check if the data has been modified of not BUG NO : 12658658
488 CURSOR C_VALUES_MODIFIED(C_INSTANCE_ID NUMBER)
489 IS
490 SELECT CSI.SERIAL_NUMBER OLD_SER_NUM,
491 MTL.CONCATENATED_SEGMENTS OLD_ITEM_NUM,
492 AHL_PRD_SERN_CHANGE_PVT.GET_SERIALTAG_CODE (CSI.INSTANCE_ID) OLD_SER_TAG_CODE,
493 CSI.INVENTORY_REVISION OLD_INV_REV,
494 CSI.LOT_NUMBER OLD_LOT_NUM
495 FROM CSI_ITEM_INSTANCES CSI,
496 MTL_SYSTEM_ITEMS_KFV MTL
497 WHERE CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
498 AND CSI.LAST_VLD_ORGANIZATION_ID = MTL.ORGANIZATION_ID
499 AND CSI.INSTANCE_ID = C_INSTANCE_ID;
500
501 L_MODIFIED_FLAG BOOLEAN;
502 -- Arunjk added to cursor check if the data has been modified of not; - Ends
503
504 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SERIALNUM_CHANGE';
505 l_api_version CONSTANT NUMBER := 1.0;
506 l_return_status VARCHAR2(1);
507 l_msg_count NUMBER;
508 l_msg_data VARCHAR2(2000);
509 --
510 -- variables needed for csi api call.
511 l_serialnum_change_rec Sernum_Change_Rec_Type := p_serialnum_change_rec;
512 l_instance_dtls_rec c_instance_details%ROWTYPE;
513 l_get_workorder_rec get_workorder_csr%ROWTYPE;
514 l_mtl_system_items_rec mtl_system_items_csr%ROWTYPE;
515 l_lookup_code fnd_lookups.lookup_code%TYPE;
516 l_item_instance_id NUMBER;
517 l_return_val BOOLEAN;
518 l_attribute_value_id NUMBER;
519 l_object_version_number NUMBER;
520 l_attribute_value csi_iea_values.attribute_value%TYPE;
521 l_attribute_id NUMBER;
522 l_idx NUMBER := 0;
523 l_serial_tag_code csi_iea_values.attribute_value%TYPE;
524 l_serial_tag_rec_found VARCHAR2(1) DEFAULT 'Y';
525 l_transaction_type_id NUMBER;
526 --Adithya added variables as part of fix for Bug# 6683990
527 l_matches_flag VARCHAR2(1);
528 l_root_uc_header_id NUMBER;
529 --
530 l_csi_instance_id_lst CSI_DATASTRUCTURES_PUB.Id_Tbl;
531 --
532 l_csi_instance_rec csi_datastructures_pub.instance_rec;
533 l_csi_party_rec csi_datastructures_pub.party_rec;
534 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
535 l_csi_extend_attrib_rec csi_datastructures_pub.extend_attrib_values_rec;
536 l_csi_relationship_rec csi_datastructures_pub.ii_relationship_rec;
537
538 l_csi_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
539 l_csi_party_tbl csi_datastructures_pub.party_tbl;
540 l_csi_account_tbl csi_datastructures_pub.party_account_tbl;
541 l_csi_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
542 l_csi_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
543 l_csi_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
544 l_csi_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
545 l_csi_extend_attrib_rec1 csi_datastructures_pub.extend_attrib_values_rec;
546 l_csi_ext_attrib_values_tbl1 csi_datastructures_pub.extend_attrib_values_tbl;
547 l_idx1 NUMBER := 0;
548 l_osp_serialnum_change_rec AHL_OSP_SHIPMENT_PUB.Sernum_Change_Rec_Type;
549
550 -- ARUNJK included cursor for cancel reservation for WO-WO supply WO Cancel Marshalling2
551 CURSOR GET_SRC_RES_ID_CSR(P_WORKORDER_ID NUMBER)
552 IS
553 SELECT mtl.reservation_id RESERVATION_ID
554 FROM mtl_reservations mtl ,
555 ahl_workorders awo
556 WHERE mtl.supply_source_header_id = awo.wip_entity_id
557 AND awo.workorder_id = P_WORKORDER_ID;
558
559 l_cancellation_flag boolean;
560 -- ARUNJK included cursor for cancel reservation for WO-WO supply WO Cancel Marshalling2 Ends
561
562 BEGIN
563 ----------------------------------
564 -- Standard Start of API savepoint
565 ----------------------------------
566 SAVEPOINT Process_Serialnum_Change;
567
568 -------------------------------------------------------------
569 -- Check if API is called in debug mode. If yes, enable debug
570 -------------------------------------------------------------.
571 IF G_DEBUG='Y' THEN
572 AHL_DEBUG_PUB.enable_debug;
573 END IF;
574
575 --------------
576 -- Debug info.
577 --------------
578 IF G_DEBUG='Y' THEN
579 AHL_DEBUG_PUB.debug( 'enter ahl_prd_sern_change_pvt. Process Serialnum Change','+PRDSRN+');
580 END IF;
581
582 ------------------------------------------------
583 -- Standard call to check for call compatibility.
584 ------------------------------------------------
585 IF FND_API.to_boolean(p_init_msg_list)
586 THEN
587 FND_MSG_PUB.initialize;
588 END IF;
589
590 -------------------------------------------
591 -- Initialize API return status to success
592 -------------------------------------------
593 x_return_status := FND_API.G_RET_STS_SUCCESS;
594
595 ------------------------------------------------------------
596 -- Initialize message list if p_init_msg_list is set to TRUE.
597 ------------------------------------------------------------
598 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
599 p_api_version,
600 l_api_name,G_PKG_NAME)
601 THEN
605 --------------------Start of API Body-----------------------------------
602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
603 END IF;
604
606
607 --------------------------------------------
608 -- Dump API Inputs.
609 --------------------------------------------
610 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
611 fnd_log.string
612 (
613 fnd_log.level_statement,
614 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
615 'p_serialnum_change_rec.workorder_id -> '||p_serialnum_change_rec.workorder_id
616 );
617 fnd_log.string
618 (
619 fnd_log.level_statement,
620 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
621 'p_serialnum_change_rec.job_number -> '||p_serialnum_change_rec.job_number
622 );
623 fnd_log.string
624 (
625 fnd_log.level_statement,
626 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
627 'p_serialnum_change_rec.osp_line_id -> '||p_serialnum_change_rec.osp_line_id
628 );
629 fnd_log.string
630 (
631 fnd_log.level_statement,
632 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
633 'p_serialnum_change_rec.instance_id -> '||p_serialnum_change_rec.instance_id
634 );
635 fnd_log.string
636 (
637 fnd_log.level_statement,
638 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
639 'p_serialnum_change_rec.new_item_number -> '||p_serialnum_change_rec.new_item_number
640 );
641 fnd_log.string
642 (
643 fnd_log.level_statement,
644 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
645 'p_serialnum_change_rec.new_serial_number -> '||p_serialnum_change_rec.new_serial_number
646 );
647 fnd_log.string
648 (
649 fnd_log.level_statement,
650 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
651 'p_serialnum_change_rec.new_serial_tag_code -> '||p_serialnum_change_rec.new_serial_tag_code
652 );
653 END IF;
654
655 --------------------------------------------
656 -- Validate if required parameters are passed
657 -- to this API. Abort otherwise
658 --------------------------------------------
659 IF
660 (
661 (
662 l_serialnum_change_rec.WORKORDER_ID IS NULL
663 AND
664 l_serialnum_change_rec.JOB_NUMBER IS NULL
665 )
666 AND
667 l_serialnum_change_rec.OSP_LINE_ID IS NULL
668 )
669 THEN
670 FND_MESSAGE.Set_Name('AHL','AHL_COM_REQD_PARAM_MISSING');
671 FND_MSG_PUB.ADD;
672 RAISE FND_API.G_EXC_ERROR;
673 END IF;
674
675 -- Arunjk added to check if the data has been modified of not BUG NO : 12658658
676 L_MODIFIED_FLAG := FALSE;
677 l_cancellation_flag := false;
678 FOR VALUES_MODIFIED_CHK IN C_VALUES_MODIFIED(L_SERIALNUM_CHANGE_REC.INSTANCE_ID) LOOP
679 IF(VALUES_MODIFIED_CHK.OLD_SER_NUM = L_SERIALNUM_CHANGE_REC.NEW_SERIAL_NUMBER AND VALUES_MODIFIED_CHK.OLD_ITEM_NUM = L_SERIALNUM_CHANGE_REC.NEW_ITEM_NUMBER) THEN
680 IF(VALUES_MODIFIED_CHK.OLD_SER_TAG_CODE = L_SERIALNUM_CHANGE_REC.NEW_SERIAL_TAG_CODE OR (VALUES_MODIFIED_CHK.OLD_SER_TAG_CODE IS NULL AND L_SERIALNUM_CHANGE_REC.NEW_SERIAL_TAG_CODE IS NULL)) THEN
681 IF(VALUES_MODIFIED_CHK.OLD_INV_REV = L_SERIALNUM_CHANGE_REC.NEW_ITEM_REV_NUMBER OR (VALUES_MODIFIED_CHK.OLD_INV_REV IS NULL AND L_SERIALNUM_CHANGE_REC.NEW_ITEM_REV_NUMBER IS NULL)) THEN
682 L_MODIFIED_FLAG := FALSE;
683 ELSE
684 L_MODIFIED_FLAG := TRUE;
685 END IF;
686 IF(L_MODIFIED_FLAG = FALSE) THEN
687 IF(VALUES_MODIFIED_CHK.OLD_LOT_NUM = L_SERIALNUM_CHANGE_REC.NEW_LOT_NUMBER OR (VALUES_MODIFIED_CHK.OLD_LOT_NUM IS NULL AND L_SERIALNUM_CHANGE_REC.NEW_LOT_NUMBER IS NULL)) THEN
688 FND_MESSAGE.Set_Name('AHL','AHL_COM_NO_CHANGES_TO_SAVE');
689 FND_MSG_PUB.ADD;
690 RAISE FND_API.G_EXC_ERROR;
691 END IF;
692 END IF;
693 END IF;
694 END IF;
695 -- Arunjk added for Marshalling for WO- WO Cancellation
696 if(VALUES_MODIFIED_CHK.OLD_ITEM_NUM <> L_SERIALNUM_CHANGE_REC.NEW_ITEM_NUMBER) then
697 l_cancellation_flag := true;
698 end if;
699 -- Arunjk added for Marshalling for WO- WO Cancellation End
700 END LOOP;
701 -- Arunjk added to check if the data has been modified of not BUG NO : 12658658 End
702
703 IF(l_serialnum_change_rec.OSP_LINE_ID IS NOT NULL) THEN
704
705 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
706
707 fnd_log.string
708 (
709 fnd_log.level_statement,
710 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
711 'Copying the serial num change attributes '
712 );
713
714 END IF;
715
716 l_osp_serialnum_change_rec.OSP_LINE_ID := l_serialnum_change_rec.OSP_LINE_ID;
717 l_osp_serialnum_change_rec.INSTANCE_ID := l_serialnum_change_rec.INSTANCE_ID;
718 l_osp_serialnum_change_rec.ITEM_NUMBER := l_serialnum_change_rec.ITEM_NUMBER;
719 l_osp_serialnum_change_rec.NEW_ITEM_NUMBER := l_serialnum_change_rec.NEW_ITEM_NUMBER;
720 l_osp_serialnum_change_rec.CURRENT_SERIAL_NUMBER := l_serialnum_change_rec.CURRENT_SERIAL_NUMBER;
721 l_osp_serialnum_change_rec.CURRENT_SERAIL_TAG := l_serialnum_change_rec.CURRENT_SERAIL_TAG;
722 l_osp_serialnum_change_rec.NEW_SERIAL_NUMBER := l_serialnum_change_rec.NEW_SERIAL_NUMBER;
723 l_osp_serialnum_change_rec.NEW_SERIAL_TAG_CODE := l_serialnum_change_rec.NEW_SERIAL_TAG_CODE;
724 l_osp_serialnum_change_rec.NEW_SERIAL_TAG_MEAN := l_serialnum_change_rec.NEW_SERIAL_TAG_MEAN;
725
726 l_osp_serialnum_change_rec.new_item_rev_number := l_serialnum_change_rec.new_item_rev_number;
730
727 l_osp_serialnum_change_rec.new_lot_number := l_serialnum_change_rec.new_lot_number;
728
729 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
731 fnd_log.string
732 (
733 fnd_log.level_statement,
734 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
735 'Before calling AHL_OSP_SHIPMENT_PUB.Process_Osp_SerialNum_Change '
736 );
737
738 END IF;
739
740 AHL_OSP_SHIPMENT_PUB.Process_Osp_SerialNum_Change
741 (
742 p_api_version => 1.0,
743 p_init_msg_list => FND_API.G_FALSE,
744 p_commit => FND_API.G_FALSE,
745 p_serialnum_change_rec => l_osp_serialnum_change_rec,
746 x_return_status => l_return_status,
747 x_msg_count => l_msg_count,
748 x_msg_data => l_msg_data
749 );
750
751 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
752
753 fnd_log.string
754 (
755 fnd_log.level_statement,
756 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
757 'After calling AHL_OSP_SHIPMENT_PUB.Process_Osp_SerialNum_Change: l_return_status => ' ||l_return_status
758 );
759
760 END IF;
761
762 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
763 RAISE FND_API.G_EXC_ERROR;
764 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767
768 ELSE --If the osp_line_id is null, then the the change is being performed from a workorder
769
770 ----------------------------------------------------------------------------------------
771 -- If the Item / Serial change is WO context.This block of code puts all validations
772 -- relevant only for a Work Order context.Also it includes validations specific to a
773 -- Work Orders.
774 ----------------------------------------------------------------------------------------
775 IF (
776 l_serialnum_change_rec.workorder_id IS NOT NULL
777 OR
778 l_serialnum_change_rec.job_number IS NOT NULL
779 )
780 THEN
781 -------------------------------------------------------
782 -- Validate that the WO is in valid status and retrieve
783 -- required attributes.
784 -------------------------------------------------------
785 OPEN get_workorder_csr(l_serialnum_change_rec.workorder_id,
786 l_serialnum_change_rec.job_number);
787 FETCH get_workorder_csr INTO l_get_workorder_rec;
788 IF l_get_workorder_rec.workorder_id IS NULL
789 THEN
790 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WO_MISSING');
791 FND_MESSAGE.Set_Token('JOBNUMBER',l_serialnum_change_rec.job_number);
792 FND_MSG_PUB.ADD;
793 CLOSE get_workorder_csr;
794 RAISE FND_API.G_EXC_ERROR;
795 END IF;
796 CLOSE get_workorder_csr;
797
798 -- rroy
799 -- ACL Changes
800 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => l_get_workorder_rec.workorder_id,
801 p_ue_id => NULL,
802 p_visit_id => NULL,
803 p_item_instance_id => NULL);
804 IF l_return_status = FND_API.G_TRUE THEN
805 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_SNC_UNTLCKD');
806 FND_MSG_PUB.ADD;
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809
810 -- rroy
811 -- ACL Changes
812
813 l_organization_id := l_get_workorder_rec.organization_id;
814
815 IF l_serialnum_change_rec.instance_id IS NULL
816 THEN
817 l_serialnum_change_rec.instance_id := l_get_workorder_rec.item_instance_id;
818 END IF;
819
820 END IF;
821
822 -- convert change item number to change item id.
823 IF l_serialnum_change_rec.new_item_number IS NOT NULL
824 THEN
825 -- Retrieve inventory_item_id from item_number
826 OPEN c_get_inv_item_id(l_serialnum_change_rec.new_item_number);
827 FETCH c_get_inv_item_id INTO l_new_inventory_item_id;
828 CLOSE c_get_inv_item_id;
829 --l_inventory_item_id := l_new_inventory_item_id;
830 ELSE
831 FND_MESSAGE.Set_Name('AHL','AHL_PP_INV_ID_REQUIRED');
832 FND_MSG_PUB.ADD;
833 RAISE FND_API.G_EXC_ERROR;
834 END IF;
835
836 -------------------------------------------------------------------------------------
837 -- retrieve all instance related details and performe related validations
838 -------------------------------------------------------------------------------------
839 -- retrieve old instance details
840 OPEN c_instance_details(l_serialnum_change_rec.instance_id);
841 FETCH c_instance_details INTO l_instance_dtls_rec;
842 CLOSE c_instance_details;
843
844 IF l_instance_dtls_rec.instance_id IS NULL
845 THEN
846 FND_MESSAGE.Set_Name('AHL','AHL_INVALID_INSTANCE');
847 FND_MSG_PUB.ADD;
848 RAISE FND_API.G_EXC_ERROR;
849 END IF;
850
851 --Adithya added the location_type_code check to allow PN/SN change for the top node
852 -- Bug# 6683990
856 l_instance_dtls_rec.wip_job_id <> l_get_workorder_rec.wip_entity_id)
853 IF l_serialnum_change_rec.WORKORDER_ID IS NOT NULL
854 AND
855 (l_instance_dtls_rec.wip_job_id IS NULL OR
857
858 THEN
859 -- new message that will be seeded.
860 IF (l_instance_dtls_rec.location_type_code IN ('PO','IN-TRANSIT','PROJECT','INVENTORY'))
861 THEN
862 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INST_LOC_INVALID');
863 FND_MSG_PUB.ADD;
864 RAISE FND_API.G_EXC_ERROR;
865 END IF;
866 END IF;
867
868 --l_inventory_item_id := NVL(l_new_inventory_item_id,l_instance_dtls_rec.inventory_item_id);
869 l_inventory_item_id := l_new_inventory_item_id;
870
871 -------------------------------------------------------------------------------------
872 -- Perform validations on the new item
873 -------------------------------------------------------------------------------------
874 -- Verify that the destination item is valid
875
876 IF l_serialnum_change_rec.NEW_ITEM_NUMBER IS NOT NULL
877 THEN
878 OPEN c_is_item_valid(l_serialnum_change_rec.NEW_ITEM_NUMBER, l_organization_id);
879 FETCH c_is_item_valid INTO l_junk;
880 CLOSE c_is_item_valid;
881 IF l_junk IS NULL
882 THEN
883 FND_MESSAGE.Set_Name('AHL','AHL_PRD_CHG_ITEM_INVALID');
884 -- Source or Destination item should be serial controlled.
885 FND_MSG_PUB.ADD;
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888 END IF;
889
890 -- Adithya added the following validation to check whether the new item is valid for the position
891 -- Bug# 6683990
892 -- get root uc header id.
893 l_root_uc_header_id := AHL_UTIL_UC_PKG.get_uc_header_id(l_serialnum_change_rec.instance_id);
894
895 IF (l_root_uc_header_id IS NOT NULL) THEN
896 AHL_UTIL_UC_PKG.Item_Matches_Instance_Pos(p_inventory_item_id => l_inventory_item_id,
897 p_item_revision => l_serialnum_change_rec.new_item_rev_number,
898 p_instance_id => l_serialnum_change_rec.instance_id,
899 x_matches_flag => l_matches_flag);
900 IF l_matches_flag = FND_API.G_FALSE
901 THEN
902 FND_MESSAGE.Set_Name('AHL','AHL_PRD_ITEM_POS_MISMATCH');
903 FND_MESSAGE.Set_Token('ITEM',l_serialnum_change_rec.NEW_ITEM_NUMBER);
904 FND_MSG_PUB.ADD;
905 RAISE FND_API.G_EXC_ERROR;
906 END IF;
907 END IF;
908 --Adithya changes end
909 --------------------------------------------
910 -- Convert meaning to lookup code
911 -- For Serialnum_tag_code.
912 --------------------------------------------
913 IF
914 (l_serialnum_change_rec.New_Serial_Tag_Code IS NULL)
915 OR
916 (l_serialnum_change_rec.New_Serial_Tag_Code = FND_API.G_MISS_CHAR)
917 THEN
918 -- Check if meaning exists.
919 IF (l_serialnum_change_rec.New_Serial_Tag_Mean IS NOT NULL)
920 AND
921 (l_serialnum_change_rec.New_Serial_Tag_Mean <> FND_API.G_MISS_CHAR)
922 THEN
923 Convert_To_LookupCode('AHL_SERIALNUMBER_TAG',
924 l_serialnum_change_rec.New_Serial_Tag_Mean,
925 l_lookup_code,
926 l_return_val);
927 IF NOT(l_return_val) THEN
928 FND_MESSAGE.Set_Name('AHL','AHL_PRD_TAGMEANING_INVALID');
929 FND_MESSAGE.Set_Token('TAG',l_serialnum_change_rec.New_Serial_Tag_Mean);
930 FND_MSG_PUB.ADD;
931 RAISE FND_API.G_EXC_ERROR;
932 ELSE
933 l_serialnum_change_rec.New_Serial_Tag_Code := l_lookup_code;
934 END IF;
935 END IF;
936 END IF;
937
938 --------------------------------------------
939 -- Validate for serial number control code
940 --------------------------------------------
941 OPEN mtl_system_items_csr(l_inventory_item_id,
942 l_organization_id);
943 FETCH mtl_system_items_csr INTO l_mtl_system_items_rec;
944 CLOSE mtl_system_items_csr;
945
946 ------------------------
947 -- Call local procedure
948 ------------------------
949 Validate_SerialNumber(l_inventory_item_id,
950 l_serialnum_change_rec.new_serial_number,
951 l_mtl_system_items_rec.serial_number_control_code,
952 l_serialnum_change_rec.New_Serial_Tag_Code,
953 l_mtl_system_items_rec.concatenated_segments);
954
955 l_msg_count := Fnd_Msg_Pub.count_msg;
956
957 IF l_msg_count > 0 THEN
958 X_msg_count := l_msg_count;
959 X_return_status := Fnd_Api.G_RET_STS_ERROR;
960 RAISE Fnd_Api.G_EXC_ERROR;
961 END IF;
962
963 ----------------------
964 -- Check for UC Exists
965 ----------------------
966 /* This check is not needed anymore?
967 OPEN l_uc_exists_cur(l_instance_dtls_rec.instance_id);
968 FETCH l_uc_exists_cur into l_item_instance_id;
969 CLOSE l_uc_exists_cur;
970 */
971 ------------------------------------------------------------
972 -- Retrieve existing value of serialNum_Tag_Code if present.
976 l_attribute_value,
973 ------------------------------------------------------------
974 GetCSI_Attribute_Value (l_serialnum_change_rec.instance_id,
975 'AHL_TEMP_SERIAL_NUM',
977 l_attribute_value_id,
978 l_object_version_number,
979 l_return_val);
980 IF NOT(l_return_val) THEN
981 l_serial_tag_code := null;
982 l_serial_tag_rec_found := 'N';
983 ELSE
984 l_serial_tag_code := l_attribute_value;
985 END IF;
986
987 ------------------------------------------------------------
988 -- Build extended attribute record for serialnum_tag_code.
989 ------------------------------------------------------------
990 IF (l_serial_tag_rec_found = 'Y' ) THEN
991 IF (l_serialnum_change_rec.New_Serial_Tag_Code IS NULL AND l_serial_tag_code IS NOT NULL) OR
992 (l_serial_tag_code IS NULL AND l_serialnum_change_rec.New_Serial_Tag_Code IS NOT NULL) OR
993 (l_serialnum_change_rec.New_Serial_Tag_Code IS NOT NULL AND l_Serial_tag_code IS NOT NULL AND
994 l_serialnum_change_rec.New_Serial_Tag_Code <> FND_API.G_MISS_CHAR AND
995 l_serialnum_change_rec.New_Serial_Tag_Code <> l_Serial_tag_code) THEN
996
997 -- changed value. update attribute record.
998 l_csi_extend_attrib_rec.attribute_value_id := l_attribute_value_id;
999 l_csi_extend_attrib_rec.attribute_value := l_serialnum_change_rec.New_Serial_Tag_Code;
1000 l_csi_extend_attrib_rec.object_version_number := l_object_version_number;
1001 l_idx := l_idx + 1;
1002 l_csi_ext_attrib_values_tbl(l_idx) := l_csi_extend_attrib_rec;
1003 END IF;
1004 ELSIF (l_serial_tag_rec_found = 'N' ) THEN
1005 IF (l_serialnum_change_rec.New_Serial_Tag_Code IS NOT NULL) THEN
1006 -- create extended attributes.
1007 GetCSI_Attribute_ID('AHL_TEMP_SERIAL_NUM',l_attribute_id, l_return_val);
1008 IF NOT(l_return_val) THEN
1009 FND_MESSAGE.Set_Name('AHL','AHL_ATTRIB_CODE_MISSING');
1010 FND_MESSAGE.Set_Token('CODE', 'AHL_TEMP_SERIAL_NUM');
1011 FND_MSG_PUB.ADD;
1012 ELSE
1013 l_csi_extend_attrib_rec1.attribute_id := l_attribute_id;
1014 l_csi_extend_attrib_rec1.attribute_value := l_serialnum_change_rec.New_Serial_Tag_Code;
1015 l_csi_extend_attrib_rec1.instance_id := l_serialnum_change_rec.instance_id;
1016 l_idx1 := l_idx1 + 1;
1017 l_csi_ext_attrib_values_tbl1(l_idx1) := l_csi_extend_attrib_rec1;
1018 END IF;
1019 END IF;
1020 END IF;
1021
1022 ------------------------------------------------------------
1023 -- Populate rest of the attributes needed.
1024 ------------------------------------------------------------
1025 -- Update item.
1026 l_csi_instance_rec.instance_id := l_serialnum_change_rec.instance_id;
1027 l_csi_instance_rec.object_version_number := l_instance_dtls_rec.object_version_number;
1028 l_csi_instance_rec.serial_number := l_serialnum_change_rec.new_serial_number;
1029 l_csi_instance_rec.inventory_item_id := l_inventory_item_id;
1030
1031 l_csi_instance_rec.inventory_revision := l_serialnum_change_rec.new_item_rev_number;
1032 l_csi_instance_rec.lot_number := l_serialnum_change_rec.new_lot_number;
1033
1034 -- IF (l_serialnum_change_rec.New_Serial_Tag_Code = 'INVENTORY') THEN
1035 --l_csi_instance_rec.mfg_serial_number_flag := 'Y';
1036 --END IF;
1037
1038 -- Per IB team, this flag should always to 'N'.
1039 l_csi_instance_rec.mfg_serial_number_flag := 'N';
1040
1041 -- csi transaction record.
1042 l_csi_transaction_rec.source_transaction_date := sysdate;
1043
1044 -- get transaction_type_id .
1045 -- GetCSI_Transaction_ID('UC_UPDATE',l_transaction_type_id, l_return_val);
1046 -- Balaji modified the transaction id type to 205--ITEM_SERIAL_CHANGE
1047 GetCSI_Transaction_ID('ITEM_SERIAL_CHANGE',l_transaction_type_id, l_return_val);
1048 IF NOT(l_return_val) THEN
1049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1050 END IF;
1051
1052 -- ??use the transaction id from the header record.
1053
1054 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
1055 --l_csi_transaction_rec.transaction_status_code :=
1056
1057 IF l_serialnum_change_rec.workorder_id IS NOT NULL
1058 OR
1059 l_serialnum_change_rec.job_number IS NOT NULL
1060 THEN
1061 l_csi_transaction_rec.source_line_ref := 'AHL_PRD_WO';
1062 l_csi_transaction_rec.source_line_ref_id := l_get_workorder_rec.workorder_id;
1063 ELSIF l_serialnum_change_rec.osp_line_id IS NOT NULL
1064 THEN
1065 l_csi_transaction_rec.source_line_ref := 'AHL_OSP_LINE';
1066 l_csi_transaction_rec.source_line_ref_id := l_serialnum_change_rec.osp_line_id;
1067 END IF;
1068
1069 -------------------------------------------------------------
1070 -- Call IB API for making item/serial change for the instance.
1071 -------------------------------------------------------------
1072 CSI_Item_Instance_PUB.Update_Item_Instance(
1073 p_api_version => 1.0,
1074 p_instance_rec => l_csi_instance_rec,
1075 p_txn_rec => l_csi_transaction_rec,
1076 p_ext_attrib_values_tbl => l_csi_ext_attrib_values_tbl,
1077 p_party_tbl => l_csi_party_tbl,
1078 p_account_tbl => l_csi_account_tbl,
1079 p_pricing_attrib_tbl => l_csi_pricing_attrib_tbl,
1083 x_return_status => l_return_status,
1080 p_org_assignments_tbl => l_csi_org_assignments_tbl,
1081 p_asset_assignment_tbl => l_csi_asset_assignment_tbl,
1082 x_instance_id_lst => l_csi_instance_id_lst,
1084 x_msg_count => l_msg_count,
1085 x_msg_data => l_msg_data );
1086
1087 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1088 RAISE FND_API.G_EXC_ERROR;
1089 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1090 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1091 END IF;
1092
1093 -------------------------------------------------------------
1094 -- for extended attributes.
1095 -------------------------------------------------------------
1096 IF (l_idx1 > 0) THEN
1097 -- Call API to create extended attributes.
1098 CSI_Item_Instance_PUB.Create_Extended_attrib_values(
1099 p_api_version => 1.0,
1100 p_txn_rec => l_csi_transaction_rec,
1101 p_ext_attrib_tbl => l_csi_ext_attrib_values_tbl1,
1102 x_return_status => l_return_status,
1103 x_msg_count => l_msg_count,
1104 x_msg_data => l_msg_data );
1105
1106
1107 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1108 RAISE FND_API.G_EXC_ERROR;
1109 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1111 END IF;
1112 END IF;
1113
1114 --Adithya added the following validation to verify that the UC rules are not broken
1115 --after partnumber/serial number has been changed.
1116 --Bug# 6683990
1117 IF (l_root_uc_header_id IS NOT NULL) THEN
1118 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1119 fnd_log.string ( fnd_log.level_statement, 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1120 'Entering UC rules validation api: root_uc_header_id => ' || l_root_uc_header_id );
1121 END IF;
1122
1123 ahl_uc_validation_pub.Validate_Completeness(
1124 p_api_version => 1.0,
1125 p_init_msg_list => FND_API.G_FALSE,
1126 p_commit => FND_API.G_FALSE,
1127 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1128 x_return_status => l_return_status,
1129 x_msg_count => l_msg_count,
1130 x_msg_data => l_msg_data,
1131 p_unit_header_id => l_root_uc_header_id,
1132 x_error_tbl => x_warning_msg_tbl);
1133
1134 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1135 RAISE FND_API.G_EXC_ERROR;
1136 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1138 END IF;
1139 END IF;
1140 --Adithya changes end
1141
1142 -- END IF; -- WO id or WO # is not null.
1143
1144 END IF; -- END IF(l_serialnum_change_rec.OSP_LINE_ID IS NOT NULL)
1145
1146 -- ARUNJK added for Marshalling WO to WO
1147 if(l_cancellation_flag = true) then
1148 FOR GET_SRC_RES_ID_REC IN GET_SRC_RES_ID_CSR(l_serialnum_change_rec.workorder_id)
1149 LOOP
1150 AHL_MM_RESERVATIONS_PVT.DELETE_RESERVATION (
1151 p_api_version => 1.0,
1152 p_init_msg_list => FND_API.G_TRUE ,
1153 p_commit => FND_API.G_FALSE ,
1154 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
1155 p_module_type => NULL,
1156 x_return_status => l_return_status ,
1157 x_msg_count => l_msg_count ,
1158 x_msg_data => l_msg_data ,
1159 p_reservation_id => GET_SRC_RES_ID_REC.RESERVATION_ID);
1160 -- Check return status.
1161 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1162 IF ( G_DEBUG = 'Y' ) THEN
1163 AHL_DEBUG_PUB.debug('Delete_Reservation failed for Reservation ID: '
1164 || GET_SRC_RES_ID_REC.RESERVATION_ID);
1165 END IF; -- G_DEBUG.
1166
1167 EXIT;
1168 END IF; -- l_return_status
1169 END LOOP;
1170 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1172 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1173 RAISE FND_API.G_EXC_ERROR;
1174 END IF;
1175 end if; -- ARUNJK Changes End
1176 ------------------------End of Body---------------------------------------
1177
1178 --Standard check to count messages
1179 x_msg_count := Fnd_Msg_Pub.count_msg;
1180
1181 /*
1182 IF l_msg_count > 0 THEN
1183 X_msg_count := l_msg_count;
1184 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1185 RAISE Fnd_Api.G_EXC_ERROR;
1186 END IF;
1187 */
1188
1189 --Standard check for commit
1190 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1191 COMMIT;
1192 END IF;
1193
1194 -- Debug info
1195 IF G_DEBUG='Y' THEN
1196 Ahl_Debug_Pub.debug( 'End of private api Process Serialnum Change','+PRDSRN+');
1197 -- Check if API is called in debug mode. If yes, disable debug.
1198 Ahl_Debug_Pub.disable_debug;
1199 END IF;
1200
1201 EXCEPTION
1202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1203 ROLLBACK TO Process_Serialnum_Change;
1204 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1205 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1206 p_count => x_msg_count,
1207 p_data => x_msg_data);
1208 IF G_DEBUG='Y' THEN
1209 AHL_DEBUG_PUB.log_app_messages (
1210 x_msg_count, x_msg_data, 'ERROR' );
1211 AHL_DEBUG_PUB.debug( 'ahl_prd_sern_change_pvt. Process Serialnum Change','+PRDSRN+');
1212 -- Check if API is called in debug mode. If yes, disable debug.
1213 AHL_DEBUG_PUB.disable_debug;
1214 END IF;
1215
1216 WHEN FND_API.G_EXC_ERROR THEN
1217 ROLLBACK TO Process_Serialnum_Change;
1218 X_return_status := FND_API.G_RET_STS_ERROR;
1219 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1220 p_count => x_msg_count,
1221 p_data => X_msg_data);
1222 IF G_DEBUG='Y' THEN
1223 -- Debug info.
1224 AHL_DEBUG_PUB.log_app_messages (
1225 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1226 AHL_DEBUG_PUB.debug( 'ahl_prd_sern_change_pvt. Process Serialnum Change','+PRDSRN+');
1227 -- Check if API is called in debug mode. If yes, disable debug.
1228 AHL_DEBUG_PUB.disable_debug;
1229 END IF;
1230 WHEN OTHERS THEN
1231 ROLLBACK TO Process_Serialnum_Change;
1232 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1233 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1234 THEN
1235 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PRD_SERN_CHANGE_PVT',
1236 p_procedure_name => 'PROCESS_SERIALNUM_CHANGE',
1237 p_error_text => SUBSTR(SQLERRM,1,240));
1238 END IF;
1239 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1240 p_count => x_msg_count,
1241 p_data => X_msg_data);
1242 IF G_DEBUG='Y' THEN
1243 -- Debug info.
1244 AHL_DEBUG_PUB.log_app_messages (
1245 x_msg_count, x_msg_data, 'SQL ERROR' );
1246 AHL_DEBUG_PUB.debug( 'ahl_prd_sern_change_pvt. Process Serialnum Change','+PRDSRN+');
1247 -- Check if API is called in debug mode. If yes, disable debug.
1248 AHL_DEBUG_PUB.disable_debug;
1249 END IF;
1250
1251 END Process_Serialnum_Change;
1252
1253 END AHL_PRD_SERN_CHANGE_PVT;
[Home] [Help]