[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_SERN_CHANGE_PVT
Source
1 PACKAGE BODY AHL_PRD_SERN_CHANGE_PVT AS
2 /* $Header: AHLVSNCB.pls 120.5 2008/04/03 13:23:49 adivenka 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);
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
111 l_return_val := FALSE;
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
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
232 where instance_id = csi.instance_id);
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
374
375 PROCEDURE Process_Serialnum_Change (
376 p_api_version IN NUMBER,
377 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
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
478 AND SERIAL_NUMBER_CONTROL_CODE in (2,5,6);
479
480 l_new_inventory_item_id NUMBER;
481 l_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 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SERIALNUM_CHANGE';
488 l_api_version CONSTANT NUMBER := 1.0;
489 l_return_status VARCHAR2(1);
490 l_msg_count NUMBER;
491 l_msg_data VARCHAR2(2000);
492 --
493 -- variables needed for csi api call.
494 l_serialnum_change_rec Sernum_Change_Rec_Type := p_serialnum_change_rec;
495 l_instance_dtls_rec c_instance_details%ROWTYPE;
496 l_get_workorder_rec get_workorder_csr%ROWTYPE;
497 l_mtl_system_items_rec mtl_system_items_csr%ROWTYPE;
498 l_lookup_code fnd_lookups.lookup_code%TYPE;
499 l_item_instance_id NUMBER;
500 l_return_val BOOLEAN;
501 l_attribute_value_id NUMBER;
502 l_object_version_number NUMBER;
503 l_attribute_value csi_iea_values.attribute_value%TYPE;
504 l_attribute_id NUMBER;
505 l_idx NUMBER := 0;
506 l_serial_tag_code csi_iea_values.attribute_value%TYPE;
507 l_serial_tag_rec_found VARCHAR2(1) DEFAULT 'Y';
508 l_transaction_type_id NUMBER;
509 --Adithya added variables as part of fix for Bug# 6683990
510 l_matches_flag VARCHAR2(1);
511 l_root_uc_header_id NUMBER;
512 --
513 l_csi_instance_id_lst CSI_DATASTRUCTURES_PUB.Id_Tbl;
514 --
515 l_csi_instance_rec csi_datastructures_pub.instance_rec;
516 l_csi_party_rec csi_datastructures_pub.party_rec;
517 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
518 l_csi_extend_attrib_rec csi_datastructures_pub.extend_attrib_values_rec;
519 l_csi_relationship_rec csi_datastructures_pub.ii_relationship_rec;
520
521 l_csi_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
522 l_csi_party_tbl csi_datastructures_pub.party_tbl;
523 l_csi_account_tbl csi_datastructures_pub.party_account_tbl;
524 l_csi_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
525 l_csi_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
526 l_csi_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
527 l_csi_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
528 l_csi_extend_attrib_rec1 csi_datastructures_pub.extend_attrib_values_rec;
529 l_csi_ext_attrib_values_tbl1 csi_datastructures_pub.extend_attrib_values_tbl;
530 l_idx1 NUMBER := 0;
531 l_osp_serialnum_change_rec AHL_OSP_SHIPMENT_PUB.Sernum_Change_Rec_Type;
532
533 BEGIN
534 ----------------------------------
535 -- Standard Start of API savepoint
536 ----------------------------------
537 SAVEPOINT Process_Serialnum_Change;
538
539 -------------------------------------------------------------
540 -- Check if API is called in debug mode. If yes, enable debug
541 -------------------------------------------------------------.
542 IF G_DEBUG='Y' THEN
543 AHL_DEBUG_PUB.enable_debug;
544 END IF;
545
546 --------------
547 -- Debug info.
548 --------------
549 IF G_DEBUG='Y' THEN
550 AHL_DEBUG_PUB.debug( 'enter ahl_prd_sern_change_pvt. Process Serialnum Change','+PRDSRN+');
551 END IF;
552
553 ------------------------------------------------
554 -- Standard call to check for call compatibility.
555 ------------------------------------------------
556 IF FND_API.to_boolean(p_init_msg_list)
557 THEN
558 FND_MSG_PUB.initialize;
559 END IF;
560
561 -------------------------------------------
562 -- Initialize API return status to success
563 -------------------------------------------
564 x_return_status := FND_API.G_RET_STS_SUCCESS;
565
566 ------------------------------------------------------------
567 -- Initialize message list if p_init_msg_list is set to TRUE.
568 ------------------------------------------------------------
569 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
570 p_api_version,
571 l_api_name,G_PKG_NAME)
572 THEN
573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
574 END IF;
575
576 --------------------Start of API Body-----------------------------------
577
578 --------------------------------------------
579 -- Dump API Inputs.
580 --------------------------------------------
581 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
582 fnd_log.string
583 (
584 fnd_log.level_statement,
585 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
586 'p_serialnum_change_rec.workorder_id -> '||p_serialnum_change_rec.workorder_id
587 );
588 fnd_log.string
589 (
590 fnd_log.level_statement,
591 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
592 'p_serialnum_change_rec.job_number -> '||p_serialnum_change_rec.job_number
593 );
594 fnd_log.string
595 (
596 fnd_log.level_statement,
597 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
598 'p_serialnum_change_rec.osp_line_id -> '||p_serialnum_change_rec.osp_line_id
599 );
600 fnd_log.string
601 (
602 fnd_log.level_statement,
603 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
604 'p_serialnum_change_rec.instance_id -> '||p_serialnum_change_rec.instance_id
605 );
606 fnd_log.string
607 (
608 fnd_log.level_statement,
609 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
610 'p_serialnum_change_rec.new_item_number -> '||p_serialnum_change_rec.new_item_number
611 );
612 fnd_log.string
613 (
614 fnd_log.level_statement,
615 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
616 'p_serialnum_change_rec.new_serial_number -> '||p_serialnum_change_rec.new_serial_number
617 );
618 fnd_log.string
619 (
620 fnd_log.level_statement,
621 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
622 'p_serialnum_change_rec.new_serial_tag_code -> '||p_serialnum_change_rec.new_serial_tag_code
623 );
624 END IF;
625
626 --------------------------------------------
627 -- Validate if required parameters are passed
628 -- to this API. Abort otherwise
629 --------------------------------------------
630 IF
631 (
632 (
633 l_serialnum_change_rec.WORKORDER_ID IS NULL
634 AND
635 l_serialnum_change_rec.JOB_NUMBER IS NULL
636 )
637 AND
638 l_serialnum_change_rec.OSP_LINE_ID IS NULL
639 )
640 THEN
641 FND_MESSAGE.Set_Name('AHL','AHL_COM_REQD_PARAM_MISSING');
642 FND_MSG_PUB.ADD;
643 RAISE FND_API.G_EXC_ERROR;
644 END IF;
645
646 IF(l_serialnum_change_rec.OSP_LINE_ID IS NOT NULL) THEN
647
648 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
649
650 fnd_log.string
651 (
652 fnd_log.level_statement,
653 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
654 'Copying the serial num change attributes '
655 );
656
657 END IF;
658
659 l_osp_serialnum_change_rec.OSP_LINE_ID := l_serialnum_change_rec.OSP_LINE_ID;
660 l_osp_serialnum_change_rec.INSTANCE_ID := l_serialnum_change_rec.INSTANCE_ID;
661 l_osp_serialnum_change_rec.ITEM_NUMBER := l_serialnum_change_rec.ITEM_NUMBER;
662 l_osp_serialnum_change_rec.NEW_ITEM_NUMBER := l_serialnum_change_rec.NEW_ITEM_NUMBER;
663 l_osp_serialnum_change_rec.CURRENT_SERIAL_NUMBER := l_serialnum_change_rec.CURRENT_SERIAL_NUMBER;
664 l_osp_serialnum_change_rec.CURRENT_SERAIL_TAG := l_serialnum_change_rec.CURRENT_SERAIL_TAG;
665 l_osp_serialnum_change_rec.NEW_SERIAL_NUMBER := l_serialnum_change_rec.NEW_SERIAL_NUMBER;
666 l_osp_serialnum_change_rec.NEW_SERIAL_TAG_CODE := l_serialnum_change_rec.NEW_SERIAL_TAG_CODE;
667 l_osp_serialnum_change_rec.NEW_SERIAL_TAG_MEAN := l_serialnum_change_rec.NEW_SERIAL_TAG_MEAN;
668
669 l_osp_serialnum_change_rec.new_item_rev_number := l_serialnum_change_rec.new_item_rev_number;
670 l_osp_serialnum_change_rec.new_lot_number := l_serialnum_change_rec.new_lot_number;
671
672 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
673
674 fnd_log.string
675 (
676 fnd_log.level_statement,
677 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
678 'Before calling AHL_OSP_SHIPMENT_PUB.Process_Osp_SerialNum_Change '
679 );
680
681 END IF;
682
683 AHL_OSP_SHIPMENT_PUB.Process_Osp_SerialNum_Change
684 (
685 p_api_version => 1.0,
686 p_init_msg_list => FND_API.G_FALSE,
687 p_commit => FND_API.G_FALSE,
688 p_serialnum_change_rec => l_osp_serialnum_change_rec,
689 x_return_status => l_return_status,
690 x_msg_count => l_msg_count,
691 x_msg_data => l_msg_data
692 );
693
694 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
695
696 fnd_log.string
697 (
698 fnd_log.level_statement,
699 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
700 'After calling AHL_OSP_SHIPMENT_PUB.Process_Osp_SerialNum_Change: l_return_status => ' ||l_return_status
701 );
702
703 END IF;
704
705 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
706 RAISE FND_API.G_EXC_ERROR;
707 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
709 END IF;
710
711 ELSE --If the osp_line_id is null, then the the change is being performed from a workorder
712
713 ----------------------------------------------------------------------------------------
714 -- If the Item / Serial change is WO context.This block of code puts all validations
715 -- relevant only for a Work Order context.Also it includes validations specific to a
716 -- Work Orders.
717 ----------------------------------------------------------------------------------------
718 IF (
719 l_serialnum_change_rec.workorder_id IS NOT NULL
720 OR
721 l_serialnum_change_rec.job_number IS NOT NULL
722 )
723 THEN
724 -------------------------------------------------------
725 -- Validate that the WO is in valid status and retrieve
726 -- required attributes.
727 -------------------------------------------------------
728 OPEN get_workorder_csr(l_serialnum_change_rec.workorder_id,
729 l_serialnum_change_rec.job_number);
730 FETCH get_workorder_csr INTO l_get_workorder_rec;
731 IF l_get_workorder_rec.workorder_id IS NULL
732 THEN
733 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WO_MISSING');
734 FND_MESSAGE.Set_Token('JOBNUMBER',l_serialnum_change_rec.job_number);
735 FND_MSG_PUB.ADD;
736 CLOSE get_workorder_csr;
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739 CLOSE get_workorder_csr;
740
741 -- rroy
742 -- ACL Changes
743 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => l_get_workorder_rec.workorder_id,
744 p_ue_id => NULL,
745 p_visit_id => NULL,
746 p_item_instance_id => NULL);
747 IF l_return_status = FND_API.G_TRUE THEN
748 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_SNC_UNTLCKD');
749 FND_MSG_PUB.ADD;
750 RAISE FND_API.G_EXC_ERROR;
751 END IF;
752
753 -- rroy
754 -- ACL Changes
755
756 l_organization_id := l_get_workorder_rec.organization_id;
757
758 IF l_serialnum_change_rec.instance_id IS NULL
759 THEN
760 l_serialnum_change_rec.instance_id := l_get_workorder_rec.item_instance_id;
761 END IF;
762
763 END IF;
764
765 -- convert change item number to change item id.
766 IF l_serialnum_change_rec.new_item_number IS NOT NULL
767 THEN
768 -- Retrieve inventory_item_id from item_number
769 OPEN c_get_inv_item_id(l_serialnum_change_rec.new_item_number);
770 FETCH c_get_inv_item_id INTO l_new_inventory_item_id;
771 CLOSE c_get_inv_item_id;
772 --l_inventory_item_id := l_new_inventory_item_id;
773 ELSE
774 FND_MESSAGE.Set_Name('AHL','AHL_PP_INV_ID_REQUIRED');
775 FND_MSG_PUB.ADD;
776 RAISE FND_API.G_EXC_ERROR;
777 END IF;
778
779 -------------------------------------------------------------------------------------
780 -- retrieve all instance related details and performe related validations
781 -------------------------------------------------------------------------------------
782 -- retrieve old instance details
783 OPEN c_instance_details(l_serialnum_change_rec.instance_id);
784 FETCH c_instance_details INTO l_instance_dtls_rec;
785 CLOSE c_instance_details;
786
787 IF l_instance_dtls_rec.instance_id IS NULL
788 THEN
789 FND_MESSAGE.Set_Name('AHL','AHL_INVALID_INSTANCE');
790 FND_MSG_PUB.ADD;
791 RAISE FND_API.G_EXC_ERROR;
792 END IF;
793
794 --Adithya added the location_type_code check to allow PN/SN change for the top node
795 -- Bug# 6683990
796 IF l_serialnum_change_rec.WORKORDER_ID IS NOT NULL
797 AND
798 (l_instance_dtls_rec.wip_job_id IS NULL OR
799 l_instance_dtls_rec.wip_job_id <> l_get_workorder_rec.wip_entity_id)
800
801 THEN
802 -- new message that will be seeded.
803 IF (l_instance_dtls_rec.location_type_code IN ('PO','IN-TRANSIT','PROJECT','INVENTORY'))
804 THEN
805 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INST_LOC_INVALID');
806 FND_MSG_PUB.ADD;
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809 END IF;
810
811 --l_inventory_item_id := NVL(l_new_inventory_item_id,l_instance_dtls_rec.inventory_item_id);
812 l_inventory_item_id := l_new_inventory_item_id;
813
814 -------------------------------------------------------------------------------------
815 -- Perform validations on the new item
816 -------------------------------------------------------------------------------------
817 -- Verify that the destination item is valid
818
819 IF l_serialnum_change_rec.NEW_ITEM_NUMBER IS NOT NULL
820 THEN
821 OPEN c_is_item_valid(l_serialnum_change_rec.NEW_ITEM_NUMBER, l_organization_id);
822 FETCH c_is_item_valid INTO l_junk;
823 CLOSE c_is_item_valid;
824 IF l_junk IS NULL
825 THEN
826 FND_MESSAGE.Set_Name('AHL','AHL_PRD_CHG_ITEM_INVALID');
827 -- Source or Destination item should be serial controlled.
828 FND_MSG_PUB.ADD;
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831 END IF;
832
833 -- Adithya added the following validation to check whether the new item is valid for the position
834 -- Bug# 6683990
835 -- get root uc header id.
836 l_root_uc_header_id := AHL_UTIL_UC_PKG.get_uc_header_id(l_serialnum_change_rec.instance_id);
837
838 IF (l_root_uc_header_id IS NOT NULL) THEN
839 AHL_UTIL_UC_PKG.Item_Matches_Instance_Pos(p_inventory_item_id => l_inventory_item_id,
840 p_item_revision => l_serialnum_change_rec.new_item_rev_number,
841 p_instance_id => l_serialnum_change_rec.instance_id,
842 x_matches_flag => l_matches_flag);
843 IF l_matches_flag = FND_API.G_FALSE
844 THEN
845 FND_MESSAGE.Set_Name('AHL','AHL_PRD_ITEM_POS_MISMATCH');
846 FND_MESSAGE.Set_Token('ITEM',l_serialnum_change_rec.NEW_ITEM_NUMBER);
847 FND_MSG_PUB.ADD;
848 RAISE FND_API.G_EXC_ERROR;
849 END IF;
850 END IF;
851 --Adithya changes end
852 --------------------------------------------
853 -- Convert meaning to lookup code
854 -- For Serialnum_tag_code.
855 --------------------------------------------
856 IF
857 (l_serialnum_change_rec.New_Serial_Tag_Code IS NULL)
858 OR
859 (l_serialnum_change_rec.New_Serial_Tag_Code = FND_API.G_MISS_CHAR)
860 THEN
861 -- Check if meaning exists.
862 IF (l_serialnum_change_rec.New_Serial_Tag_Mean IS NOT NULL)
863 AND
864 (l_serialnum_change_rec.New_Serial_Tag_Mean <> FND_API.G_MISS_CHAR)
865 THEN
866 Convert_To_LookupCode('AHL_SERIALNUMBER_TAG',
867 l_serialnum_change_rec.New_Serial_Tag_Mean,
868 l_lookup_code,
869 l_return_val);
870 IF NOT(l_return_val) THEN
871 FND_MESSAGE.Set_Name('AHL','AHL_PRD_TAGMEANING_INVALID');
872 FND_MESSAGE.Set_Token('TAG',l_serialnum_change_rec.New_Serial_Tag_Mean);
873 FND_MSG_PUB.ADD;
874 RAISE FND_API.G_EXC_ERROR;
875 ELSE
876 l_serialnum_change_rec.New_Serial_Tag_Code := l_lookup_code;
877 END IF;
878 END IF;
879 END IF;
880
881 --------------------------------------------
882 -- Validate for serial number control code
883 --------------------------------------------
884 OPEN mtl_system_items_csr(l_inventory_item_id,
885 l_organization_id);
886 FETCH mtl_system_items_csr INTO l_mtl_system_items_rec;
887 CLOSE mtl_system_items_csr;
888
889 ------------------------
890 -- Call local procedure
891 ------------------------
892 Validate_SerialNumber(l_inventory_item_id,
893 l_serialnum_change_rec.new_serial_number,
894 l_mtl_system_items_rec.serial_number_control_code,
895 l_serialnum_change_rec.New_Serial_Tag_Code,
896 l_mtl_system_items_rec.concatenated_segments);
897
898 l_msg_count := Fnd_Msg_Pub.count_msg;
899
900 IF l_msg_count > 0 THEN
901 X_msg_count := l_msg_count;
902 X_return_status := Fnd_Api.G_RET_STS_ERROR;
903 RAISE Fnd_Api.G_EXC_ERROR;
904 END IF;
905
906 ----------------------
907 -- Check for UC Exists
908 ----------------------
909 /* This check is not needed anymore?
910 OPEN l_uc_exists_cur(l_instance_dtls_rec.instance_id);
911 FETCH l_uc_exists_cur into l_item_instance_id;
912 CLOSE l_uc_exists_cur;
913 */
914 ------------------------------------------------------------
915 -- Retrieve existing value of serialNum_Tag_Code if present.
916 ------------------------------------------------------------
917 GetCSI_Attribute_Value (l_serialnum_change_rec.instance_id,
918 'AHL_TEMP_SERIAL_NUM',
919 l_attribute_value,
920 l_attribute_value_id,
921 l_object_version_number,
922 l_return_val);
923 IF NOT(l_return_val) THEN
924 l_serial_tag_code := null;
925 l_serial_tag_rec_found := 'N';
926 ELSE
927 l_serial_tag_code := l_attribute_value;
928 END IF;
929
930 ------------------------------------------------------------
931 -- Build extended attribute record for serialnum_tag_code.
932 ------------------------------------------------------------
933 IF (l_serial_tag_rec_found = 'Y' ) THEN
934 IF (l_serialnum_change_rec.New_Serial_Tag_Code IS NULL AND l_serial_tag_code IS NOT NULL) OR
935 (l_serial_tag_code IS NULL AND l_serialnum_change_rec.New_Serial_Tag_Code IS NOT NULL) OR
936 (l_serialnum_change_rec.New_Serial_Tag_Code IS NOT NULL AND l_Serial_tag_code IS NOT NULL AND
937 l_serialnum_change_rec.New_Serial_Tag_Code <> FND_API.G_MISS_CHAR AND
938 l_serialnum_change_rec.New_Serial_Tag_Code <> l_Serial_tag_code) THEN
939
940 -- changed value. update attribute record.
941 l_csi_extend_attrib_rec.attribute_value_id := l_attribute_value_id;
942 l_csi_extend_attrib_rec.attribute_value := l_serialnum_change_rec.New_Serial_Tag_Code;
943 l_csi_extend_attrib_rec.object_version_number := l_object_version_number;
944 l_idx := l_idx + 1;
945 l_csi_ext_attrib_values_tbl(l_idx) := l_csi_extend_attrib_rec;
946 END IF;
947 ELSIF (l_serial_tag_rec_found = 'N' ) THEN
948 IF (l_serialnum_change_rec.New_Serial_Tag_Code IS NOT NULL) THEN
949 -- create extended attributes.
950 GetCSI_Attribute_ID('AHL_TEMP_SERIAL_NUM',l_attribute_id, l_return_val);
951 IF NOT(l_return_val) THEN
952 FND_MESSAGE.Set_Name('AHL','AHL_ATTRIB_CODE_MISSING');
953 FND_MESSAGE.Set_Token('CODE', 'AHL_TEMP_SERIAL_NUM');
954 FND_MSG_PUB.ADD;
955 ELSE
956 l_csi_extend_attrib_rec1.attribute_id := l_attribute_id;
957 l_csi_extend_attrib_rec1.attribute_value := l_serialnum_change_rec.New_Serial_Tag_Code;
958 l_csi_extend_attrib_rec1.instance_id := l_serialnum_change_rec.instance_id;
959 l_idx1 := l_idx1 + 1;
960 l_csi_ext_attrib_values_tbl1(l_idx1) := l_csi_extend_attrib_rec1;
961 END IF;
962 END IF;
963 END IF;
964
965 ------------------------------------------------------------
966 -- Populate rest of the attributes needed.
967 ------------------------------------------------------------
968 -- Update item.
969 l_csi_instance_rec.instance_id := l_serialnum_change_rec.instance_id;
970 l_csi_instance_rec.object_version_number := l_instance_dtls_rec.object_version_number;
971 l_csi_instance_rec.serial_number := l_serialnum_change_rec.new_serial_number;
972 l_csi_instance_rec.inventory_item_id := l_inventory_item_id;
973
974 l_csi_instance_rec.inventory_revision := l_serialnum_change_rec.new_item_rev_number;
975 l_csi_instance_rec.lot_number := l_serialnum_change_rec.new_lot_number;
976
977 -- IF (l_serialnum_change_rec.New_Serial_Tag_Code = 'INVENTORY') THEN
978 --l_csi_instance_rec.mfg_serial_number_flag := 'Y';
979 --END IF;
980
981 -- Per IB team, this flag should always to 'N'.
982 l_csi_instance_rec.mfg_serial_number_flag := 'N';
983
984 -- csi transaction record.
985 l_csi_transaction_rec.source_transaction_date := sysdate;
986
987 -- get transaction_type_id .
988 -- GetCSI_Transaction_ID('UC_UPDATE',l_transaction_type_id, l_return_val);
989 -- Balaji modified the transaction id type to 205--ITEM_SERIAL_CHANGE
990 GetCSI_Transaction_ID('ITEM_SERIAL_CHANGE',l_transaction_type_id, l_return_val);
991 IF NOT(l_return_val) THEN
992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
993 END IF;
994
995 -- ??use the transaction id from the header record.
996
997 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
998 --l_csi_transaction_rec.transaction_status_code :=
999
1000 IF l_serialnum_change_rec.workorder_id IS NOT NULL
1001 OR
1002 l_serialnum_change_rec.job_number IS NOT NULL
1003 THEN
1004 l_csi_transaction_rec.source_line_ref := 'AHL_PRD_WO';
1005 l_csi_transaction_rec.source_line_ref_id := l_get_workorder_rec.workorder_id;
1006 ELSIF l_serialnum_change_rec.osp_line_id IS NOT NULL
1007 THEN
1008 l_csi_transaction_rec.source_line_ref := 'AHL_OSP_LINE';
1009 l_csi_transaction_rec.source_line_ref_id := l_serialnum_change_rec.osp_line_id;
1010 END IF;
1011
1012 -------------------------------------------------------------
1013 -- Call IB API for making item/serial change for the instance.
1014 -------------------------------------------------------------
1015 CSI_Item_Instance_PUB.Update_Item_Instance(
1016 p_api_version => 1.0,
1017 p_instance_rec => l_csi_instance_rec,
1018 p_txn_rec => l_csi_transaction_rec,
1019 p_ext_attrib_values_tbl => l_csi_ext_attrib_values_tbl,
1020 p_party_tbl => l_csi_party_tbl,
1021 p_account_tbl => l_csi_account_tbl,
1022 p_pricing_attrib_tbl => l_csi_pricing_attrib_tbl,
1023 p_org_assignments_tbl => l_csi_org_assignments_tbl,
1024 p_asset_assignment_tbl => l_csi_asset_assignment_tbl,
1025 x_instance_id_lst => l_csi_instance_id_lst,
1026 x_return_status => l_return_status,
1027 x_msg_count => l_msg_count,
1028 x_msg_data => l_msg_data );
1029
1030 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1031 RAISE FND_API.G_EXC_ERROR;
1032 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1034 END IF;
1035
1036 -------------------------------------------------------------
1037 -- for extended attributes.
1038 -------------------------------------------------------------
1039 IF (l_idx1 > 0) THEN
1040 -- Call API to create extended attributes.
1041 CSI_Item_Instance_PUB.Create_Extended_attrib_values(
1042 p_api_version => 1.0,
1043 p_txn_rec => l_csi_transaction_rec,
1044 p_ext_attrib_tbl => l_csi_ext_attrib_values_tbl1,
1045 x_return_status => l_return_status,
1046 x_msg_count => l_msg_count,
1047 x_msg_data => l_msg_data );
1048
1049
1050 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1051 RAISE FND_API.G_EXC_ERROR;
1052 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054 END IF;
1055 END IF;
1056
1057 --Adithya added the following validation to verify that the UC rules are not broken
1058 --after partnumber/serial number has been changed.
1059 --Bug# 6683990
1060 IF (l_root_uc_header_id IS NOT NULL) THEN
1061 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1062 fnd_log.string ( fnd_log.level_statement, 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1063 'Entering UC rules validation api: root_uc_header_id => ' || l_root_uc_header_id );
1064 END IF;
1065
1066 ahl_uc_validation_pub.Validate_Completeness(
1067 p_api_version => 1.0,
1068 p_init_msg_list => FND_API.G_FALSE,
1069 p_commit => FND_API.G_FALSE,
1070 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1071 x_return_status => l_return_status,
1072 x_msg_count => l_msg_count,
1073 x_msg_data => l_msg_data,
1074 p_unit_header_id => l_root_uc_header_id,
1075 x_error_tbl => x_warning_msg_tbl);
1076
1077 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1078 RAISE FND_API.G_EXC_ERROR;
1079 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1080 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1081 END IF;
1082 END IF;
1083 --Adithya changes end
1084
1085 -- END IF; -- WO id or WO # is not null.
1086 END IF; -- END IF(l_serialnum_change_rec.OSP_LINE_ID IS NOT NULL)
1087 ------------------------End of Body---------------------------------------
1088
1089 --Standard check to count messages
1090 x_msg_count := Fnd_Msg_Pub.count_msg;
1091
1092 /*
1093 IF l_msg_count > 0 THEN
1094 X_msg_count := l_msg_count;
1095 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1096 RAISE Fnd_Api.G_EXC_ERROR;
1097 END IF;
1098 */
1099
1100 --Standard check for commit
1101 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1102 COMMIT;
1103 END IF;
1104
1105 -- Debug info
1106 IF G_DEBUG='Y' THEN
1107 Ahl_Debug_Pub.debug( 'End of private api Process Serialnum Change','+PRDSRN+');
1108 -- Check if API is called in debug mode. If yes, disable debug.
1109 Ahl_Debug_Pub.disable_debug;
1110 END IF;
1111
1112 EXCEPTION
1113 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1114 ROLLBACK TO Process_Serialnum_Change;
1115 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1116 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1117 p_count => x_msg_count,
1118 p_data => x_msg_data);
1119 IF G_DEBUG='Y' THEN
1120 AHL_DEBUG_PUB.log_app_messages (
1121 x_msg_count, x_msg_data, 'ERROR' );
1122 AHL_DEBUG_PUB.debug( 'ahl_prd_sern_change_pvt. Process Serialnum Change','+PRDSRN+');
1123 -- Check if API is called in debug mode. If yes, disable debug.
1124 AHL_DEBUG_PUB.disable_debug;
1125 END IF;
1126
1127 WHEN FND_API.G_EXC_ERROR THEN
1128 ROLLBACK TO Process_Serialnum_Change;
1129 X_return_status := FND_API.G_RET_STS_ERROR;
1130 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1131 p_count => x_msg_count,
1132 p_data => X_msg_data);
1133 IF G_DEBUG='Y' THEN
1134 -- Debug info.
1135 AHL_DEBUG_PUB.log_app_messages (
1136 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1137 AHL_DEBUG_PUB.debug( 'ahl_prd_sern_change_pvt. Process Serialnum Change','+PRDSRN+');
1138 -- Check if API is called in debug mode. If yes, disable debug.
1139 AHL_DEBUG_PUB.disable_debug;
1140 END IF;
1141 WHEN OTHERS THEN
1142 ROLLBACK TO Process_Serialnum_Change;
1143 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1144 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1145 THEN
1146 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PRD_SERN_CHANGE_PVT',
1147 p_procedure_name => 'PROCESS_SERIALNUM_CHANGE',
1148 p_error_text => SUBSTR(SQLERRM,1,240));
1149 END IF;
1150 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1151 p_count => x_msg_count,
1152 p_data => X_msg_data);
1153 IF G_DEBUG='Y' THEN
1154 -- Debug info.
1155 AHL_DEBUG_PUB.log_app_messages (
1156 x_msg_count, x_msg_data, 'SQL ERROR' );
1157 AHL_DEBUG_PUB.debug( 'ahl_prd_sern_change_pvt. Process Serialnum Change','+PRDSRN+');
1158 -- Check if API is called in debug mode. If yes, disable debug.
1159 AHL_DEBUG_PUB.disable_debug;
1160 END IF;
1161
1162 END Process_Serialnum_Change;
1163
1164 END AHL_PRD_SERN_CHANGE_PVT;