[Home] [Help]
PACKAGE BODY: APPS.AHL_CMP_PVT
Source
1 PACKAGE BODY AHL_CMP_PVT AS
2 /* $Header: AHLVCMPB.pls 120.0.12020000.2 2012/12/14 07:45:06 shnatu noship $ */
3
4 -- Package constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_CMP_PVT';
6 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
7 G_DEBUG CONSTANT VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
8
9 --ITEM Params
10 C_NO_SERIAL_CONTROL CONSTANT NUMBER := 1;
11 C_NO_LOT_CONTROL CONSTANT NUMBER := 1;
12
13 --Declare any types used by the procedure
14 TYPE TXN_INTF_ID_TBL IS TABLE OF NUMBER index by BINARY_INTEGER;
15
16 ------------------------------------------------------------------------------------
17 -- Common variables
18 ------------------------------------------------------------------------------------
19 l_log_current_level NUMBER := FND_LOG.g_current_runtime_level;
20 l_log_statement NUMBER := FND_LOG.level_statement;
21 l_log_procedure NUMBER := FND_LOG.level_procedure;
22 l_log_error NUMBER := FND_LOG.level_error;
23 l_log_exception NUMBER := FND_LOG.level_exception;
24 l_log_unexpected NUMBER := FND_LOG.level_unexpected;
25 ------------------------------------------------------------------------------------
26
27 ------------------------------------------------------------------------------------
28 -- Private APIs
29 ------------------------------------------------------------------------------------
30 -- Start of Comments
31 -- Procedure name : Validate_Rec
32 -- Type : Private
33 -- Function : This procedure will validate Material Transfer Record
34 -- Pre-reqs :
35 -- Parameters :
36 --
37 -- Validate_Rec Parameters
38 -- p_x_ahl_mtlxfr_rec IN OUT Material Transfer Record Required
39 --
40 -- End of Comments
41 -------------------------------------------------------------------------------------------------------
42 PROCEDURE Validate_Rec
43 (
44 p_x_ahl_mtlxfr_rec IN OUT NOCOPY Ahl_Mtlxfr_Rec_Type,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2
48 );
49
50 ------------------------------------------------------------------------------------------------------
51 -- Start of Comments
52 -- Procedure name : Insert_Mtl_Txn_Intf
53 -- Type : Private
54 -- Function : This procedure will insert data into interface table MTL_TRANSACTIONS_INTERFACE
55 -- Pre-reqs :
56 -- Parameters :
57 --
58 -- Insert_Mtl_Txn_Intf Parameters
59 -- p_x_ahl_mtlxfr_rec IN OUT Material Transfer Record Required
60 -- p_x_txn_hdr_id IN OUT Transaction Header ID
61 -- p_x_txn_intf_id IN OUT Transaction Interface ID
62 --
63 -- End of Comments
64 -------------------------------------------------------------------------------------------------------
65 PROCEDURE Insert_Mtl_Txn_Intf
66 (
67 p_x_ahl_mtlxfr_rec IN OUT NOCOPY Ahl_Mtlxfr_Rec_Type,
68 p_x_txn_hdr_id IN OUT NOCOPY NUMBER,
69 p_x_txn_intf_id IN OUT NOCOPY NUMBER,
70 x_return_status OUT NOCOPY VARCHAR2
71 );
72 -- Kasridha: Changes for move item instances from planning to in-repair locator begins
73 ---------------------------------------------------------------------------------------------
74 -- Kasridha: Procedure to create a dummy instance in WIP
75 ------------------------------------------------------------------------------------------------------
76 -- Start of Comments
77 -- Procedure name : Create_Instance_in_WIP
78 -- Type : Public
79 -- Function : This procedure is used to create a dummy instance in WIP.
80 -- Pre-reqs :
81 -- Parameters :
82 --
83 -- Create_Instance_in_WIP
84 -- p_wip_entity_id IN WIP Entity ID of the first RTS workorder Required
85 -- p_instance_id IN Instance ID Required
86 --
87 -- End of Comments
88 -------------------------------------------------------------------------------------------------------
89 PROCEDURE Create_Instance_in_WIP
90 (
91 p_api_version IN NUMBER := 1.0,
92 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
93 p_commit IN VARCHAR2 := FND_API.G_FALSE,
94 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
95 p_wip_entity_id IN NUMBER,
96 p_instance_id IN NUMBER,
97 x_new_instance_id OUT NOCOPY NUMBER,
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_msg_count OUT NOCOPY NUMBER,
100 x_msg_data OUT NOCOPY VARCHAR2
101 );
102 ---------------------------------------------------------------------------------------------
103 -- Kasridha: Procedure to update all EAM workorders with an instance
104 ------------------------------------------------------------------------------------------------------
105 -- Start of Comments
106 -- Procedure name : Update_EAM_Workorders
107 -- Type : Public
108 -- Function : This procedure is used to update all EAM workorders with an instance.
109 -- Pre-reqs :
110 -- Parameters :
111 --
112 -- Update_EAM_Workorders
113 -- p_wip_entity_id IN WIP Entity ID of the first RTS workorder Required
114 -- p_instance_id IN Instance ID Required
115 --
116 -- End of Comments
117 -------------------------------------------------------------------------------------------------------
118 PROCEDURE Update_EAM_Workorders
119 (
120 p_api_version IN NUMBER := 1.0,
121 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
122 p_commit IN VARCHAR2 := FND_API.G_FALSE,
123 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
124 p_wip_entity_id IN NUMBER,
125 p_instance_id IN NUMBER,
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2
129 );
130 -- Kasridha: Changes for move item instances from planning to in-repair locator Ends
131 ------------------------------------------------------------------------------------------------------
132
133 -------------------------------------------------------------------------------------------------------
134 -- Start of Comments
135 -- Procedure name : Validate_Rtn_Rec
136 -- Type : Private
137 -- Function : This procedure will validate Material Return Record
138 -- Pre-reqs :
139 -- Parameters :
140 --
141 -- Validate_Rtn_Rec Parameters
142 -- p_x_ahl_mtlrtn_rec IN OUT Material Return Record Required
143 --
144 -- End of Comments
145 ---------------------------------------------------------------------------------------------------------
146 PROCEDURE Validate_Rtn_Rec
147 (
148 p_x_ahl_mtlrtn_rec IN OUT NOCOPY Ahl_MtlRtn_Rec_Type,
149 x_return_status OUT NOCOPY VARCHAR2,
150 x_msg_count OUT NOCOPY NUMBER,
151 x_msg_data OUT NOCOPY VARCHAR2
152
153 );
154 ----------------------------------------------------------------------------------------------------------
155
156 ------------------------------------------------------------------------------------------------------
157 -- Start of Comments
158 -- Procedure name : Perform_Mtl_Xfr
159 -- Type : Public
160 -- Function : This procedure will transfer materials between different sub-inventory locators
161 -- Pre-reqs :
162 -- Parameters :
163 --
164 -- Perform_Mtl_Xfr Parameters
165 -- p_x_ahl_mtlxfr_tbl IN Table of Material Transfer Records Required
166 --
167 -- End of Comments
168 -------------------------------------------------------------------------------------------------------
169 PROCEDURE PERFORM_MTL_XFR
170 (
171 p_api_version IN NUMBER := 1.0,
172 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
173 p_commit IN VARCHAR2 := FND_API.G_FALSE,
174 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
175 p_x_ahl_mtlxfr_tbl IN OUT NOCOPY Ahl_Mtlxfr_Tbl_Type,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_msg_count OUT NOCOPY NUMBER,
178 x_msg_data OUT NOCOPY VARCHAR2
179 )
180 IS
181 l_api_name CONSTANT VARCHAR2(30) := 'PERFORM_MTL_XFR';
182 l_api_version CONSTANT NUMBER := 1.0;
183
184 l_Txn_Header_Id NUMBER;
185 l_Txn_tmp_Id NUMBER;
186 l_Txn_Id_Tbl TXN_INTF_ID_TBL;
187
188 l_error_msg varchar2(240);
189 l_error_code varchar2(240);
190 l_error BOOLEAN;
191 l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
192
193
194 --Query to get the error messages
195 CURSOR Txn_Error_Cur(p_txn_Id NUMBER) IS
196 SELECT intf.ERROR_EXPLANATION ,intf.ERROR_CODE, kfv.concatenated_segments
197 FROM MTL_TRANSACTIONS_INTERFACE INTF, mtl_system_items_kfv kfv
198 WHERE TRANSACTION_INTERFACE_ID = p_txn_Id
199 AND intf.inventory_item_id = kfv.inventory_item_id
200 AND intf.organization_id = kfv.organization_id ;
201
202
203 l_junk VARCHAR2(1);
204 L_VALID_FLAG BOOLEAN;
205 L_RESULT NUMBER;
206 l_trans_count NUMBER;
207
208 BEGIN
209 -- Standard start of API savepoint
210 SAVEPOINT PERFORM_MTL_XFR_PVT;
211
212 -- Standard call to check for call compatibility
213 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
214 G_PKG_NAME) THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217 -- Initialize message list if p_init_msg_list is set to TRUE
218 IF FND_API.To_Boolean(p_init_msg_list) THEN
219 FND_MSG_PUB.Initialize;
220 END IF;
221
222 -- Initialize API return status to success
223 x_return_status := FND_API.G_RET_STS_SUCCESS;
224
225
226 IF G_DEBUG='Y' THEN
227 AHL_DEBUG_PUB.enable_debug;
228 AHL_DEBUG_PUB.debug('Entered Mtl Transfer API');
229 END IF;
230
231 IF (p_x_ahl_mtlxfr_tbl.COUNT > 0) THEN
232
233 FOR i IN p_x_ahl_mtlxfr_tbl.FIRST..p_x_ahl_mtlxfr_tbl.LAST LOOP
234
235 IF G_DEBUG='Y' THEN
236 AHL_DEBUG_PUB.debug('calling Validate_Rec for i=['||i||']');
237 END IF;
238
239 --Call Validate_rec to validate the material transfer record.
240 VALIDATE_REC
241 (
242 p_x_ahl_mtlxfr_rec => p_x_ahl_mtlxfr_tbl(i),
243 x_return_status => x_return_status,
244 x_msg_count => x_msg_count,
245 x_msg_data => x_msg_data
246 );
247
248 IF G_DEBUG='Y' THEN
249 AHL_DEBUG_PUB.debug('After calling Validate_rec');
250 END IF;
251
252 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
253 l_error := true;
254
255 IF G_DEBUG='Y' THEN
256 AHL_DEBUG_PUB.debug('Error in Validate_rec');
257 END IF;
258 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260
261 IF G_DEBUG='Y' THEN
262 AHL_DEBUG_PUB.debug('Unexpected error in Validate_rec');
263 END IF;
264 END IF;
265
266 END LOOP;
267
268 IF (l_Error) THEN
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271
272 -- Interface table insert.
273 l_txn_Header_Id := NULL;
274 l_Error := false;
275 FOR i IN p_x_ahl_mtlxfr_tbl.FIRST..p_x_ahl_mtlxfr_tbl.LAST LOOP
276
277 IF G_DEBUG='Y' THEN
278 AHL_DEBUG_PUB.debug('Calling Insert_Txn_Intf...');
279 END IF;
280
281 INSERT_MTL_TXN_INTF
282 (
283 p_x_ahl_mtlxfr_rec => p_x_ahl_mtlxfr_tbl(i) ,
284 p_x_txn_hdr_id => l_txn_Header_Id,
285 p_x_txn_intf_id => l_txn_tmp_Id,
286 x_return_status => x_return_status
287 );
288
289 l_txn_Id_Tbl(i) := l_Txn_tmp_Id;
290 IF G_DEBUG='Y' THEN
291 AHL_DEBUG_PUB.debug('After Calling Insert_Txn_Intf...ret_status['||x_return_status||']');
292 END IF;
293
294
295 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
296 l_error := true;
297 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
298 l_error := true;
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 END IF;
301 END LOOP; -- End of loop for Interface table inserts
302
303 IF(l_error ) THEN
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
306
307 -- Now process the interface records
308 IF G_DEBUG='Y' THEN
309 AHL_DEBUG_PUB.debug('Before calling INV_TXN_MANAGER_PUB.process_Transactions....');
310 END IF;
311
312 l_result := INV_TXN_MANAGER_PUB.process_Transactions(
313 p_api_version => null ,
314 p_init_msg_list => fnd_api.g_false ,
315 p_commit => fnd_api.g_false ,
316 p_validation_level => fnd_api.g_valid_level_full ,
317 x_return_status => x_return_status,
318 x_msg_count => x_msg_count,
319 x_msg_data => x_msg_data,
320 x_trans_count => l_trans_count,
321 p_table => 1 , -- Source of transaction records with value 1 for material transaction interface table
322 p_header_id => l_Txn_Header_Id ); -- Transaction header id
323
324 mo_global.init('AHL');
325
326 IF( l_result = -1 OR x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
327 IF G_DEBUG='Y' THEN
328 AHL_DEBUG_PUB.debug('INV_TXN_MANAGER_PUB.process_Transactions....errored');
329 AHL_DEBUG_PUB.debug('count of error msgs: ' || FND_MSG_PUB.COUNT_MSG);
330 END IF;
331
332 END IF;
333 END IF;
334
335
336 -- Now Loop thru the transaction Id table, Check Errors
337 IF (l_txn_Id_Tbl.COUNT > 0) THEN
338
339 -- This loop checks for errors. No interface record implies
340 -- tha there is no error.
341 l_error := false;
342 FOR i IN l_txn_Id_Tbl.FIRST..l_txn_Id_Tbl.LAST LOOP
343
344 OPEN Txn_Error_cur(l_txn_Id_Tbl(i));
345 FETCH Txn_Error_cur INTO l_error_msg,l_error_code,
346 l_concatenated_segments;
347 IF(Txn_Error_cur%FOUND AND (l_error_code IS NOT NULL
348 OR trim(l_error_code) = '')) THEN
349
350 IF G_DEBUG='Y' THEN
351 AHL_DEBUG_PUB.debug('Error in transaction['||l_error_msg||']');
352 END IF;
353
354 FND_MESSAGE.Set_Name('AHL','AHL_CMP_MTLXFR_ERROR');
355 FND_MESSAGE.Set_Token('MSG',l_error_msg);
356 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
357 FND_MSG_PUB.ADD;
358 l_error := true;
359 END IF;
360 CLOSE Txn_Error_cur;
361 END LOOP;
362
363 IF(l_error ) THEN
364 RAISE FND_API.G_EXC_ERROR;
365 END IF;
366 END IF;
367 -- END of if which checks the count of transactions created
368
369 -- Standard check of p_commit
370 IF FND_API.To_Boolean(p_commit) THEN
371 COMMIT WORK;
372 END IF;
373
374 EXCEPTION
375 WHEN FND_API.G_EXC_ERROR THEN
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 Rollback to PERFORM_MTL_XFR_PVT;
378 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
379 p_data => x_msg_data,
380 p_encoded => fnd_api.g_false);
381
382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
384 Rollback to PERFORM_MTL_XFR_PVT;
385 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
386 p_data => x_msg_data,
387 p_encoded => fnd_api.g_false);
388
389 WHEN OTHERS THEN
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391 Rollback to PERFORM_MTL_XFR_PVT;
392 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
393 p_procedure_name => 'Perform_Mtl_Xfr',
394 p_error_text => SQLERRM);
395 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
396 p_data => x_msg_data,
397 p_encoded => fnd_api.g_false);
398
399 END PERFORM_MTL_XFR;
400
401 PROCEDURE Validate_Rec
402 (
403 p_x_ahl_mtlxfr_rec IN OUT NOCOPY Ahl_Mtlxfr_Rec_Type,
404 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2
407
408 )
409 IS
410 l_Count NUMBER;
411
412 l_serial_control NUMBER;
413 l_revision_control NUMBER;
414 l_lot_control NUMBER;
415 l_location_control NUMBER;
416
417 l_return_status VARCHAR2(10);
418 l_msg_count NUMBER;
419 l_msg_data VARCHAR2(240);
420
421 l_lot_flag BOOLEAN := FALSE;
422 l_revision_flag BOOLEAN := FALSE;
423
424
425 -- Item id validation and selecting serial control code,lot control code values
426 CURSOR AHL_ITEM_ID_CUR (p_org_id NUMBER, p_item NUMBER) IS
427 SELECT SERIAL_NUMBER_CONTROL_CODE, LOT_CONTROL_CODE, REVISION_QTY_CONTROL_CODE,
428 LOCATION_CONTROL_CODE,primary_uom_code, concatenated_segments
429 FROM MTL_SYSTEM_ITEMS_KFV
430 WHERE ORGANIZATION_ID = p_org_id
431 AND INVENTORY_ITEM_ID = p_item
432 AND ENABLED_FLAG = 'Y'
433 AND ((START_DATE_ACTIVE IS NULL) OR (START_DATE_ACTIVE <= SYSDATE))
434 AND ((END_DATE_ACTIVE IS NULL) OR (END_DATE_ACTIVE >= SYSDATE));
435
436 --Sub Inventory Query
437 CURSOR AHL_SUBINV_CUR (p_org_id NUMBER, p_subinv VARCHAR2) IS
438 SELECT 1
439 FROM MTL_SECONDARY_INVENTORIES
440 WHERE ORGANIZATION_ID = p_org_id
441 AND SECONDARY_INVENTORY_NAME = p_subinv;
442
443 -- Locator Query
444 CURSOR AHL_LOCATOR_CUR (p_org_id NUMBER, p_locator_id NUMBER) IS
445 SELECT 1
446 FROM MTL_ITEM_LOCATIONS
447 WHERE ORGANIZATION_ID = p_org_id
448 AND INVENTORY_LOCATION_ID = p_locator_id;
449
450 -- Revision Query
451 CURSOR AHL_REVISION_CUR (p_org_id NUMBER, p_item NUMBER, p_revision VARCHAR2) IS
452 SELECT 1
453 FROM MTL_ITEM_REVISIONS
454 WHERE ORGANIZATION_ID = p_org_id
455 AND INVENTORY_ITEM_ID = p_item
456 AND REVISION = p_revision;
457
458
459 CURSOR mtl_srl_num_csr(p_org_id In NUMBER,
460 p_inv_id IN NUMBER,
461 p_serial_number IN VARCHAR2) IS
462 SELECT current_subinventory_code, current_locator_id
463 FROM mtl_serial_numbers
464 WHERE serial_number = p_serial_number
465 AND current_organization_id = p_org_id
466 AND inventory_item_id = p_inv_id
467 AND current_status = 3;
468
469
470 -- Lot Number
471 CURSOR mtl_lot_num_csr (p_org_id In NUMBER,
472 p_inventory_item_id IN NUMBER,
473 p_lot_number IN VARCHAR2) IS
474 SELECT 'x'
475 FROM mtl_lot_numbers
476 WHERE organization_id = p_org_id
477 AND inventory_item_id = p_inventory_item_id
478 AND lot_number = p_lot_number
479 AND nvl(disable_flag,2) = 2;
480
481 -- Transaction Types
482 CURSOR TRX_ACTION_CUR(p_type_Id NUMBER) IS
483 SELECT TRANSACTION_TYPE_NAME
484 from MTL_TRANSACTION_TYPES
485 where TRANSACTION_TYPE_ID = p_type_Id;
486
487
488 l_txn_type VARCHAR2(80);
489 l_curr_subinventory_code mtl_serial_numbers.current_subinventory_code%TYPE;
490 l_curr_locator_id NUMBER;
491 l_primary_uom_code mtl_system_items.primary_uom_code%TYPE;
492 l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
493 l_junk VARCHAR2(1);
494
495
496 BEGIN
497
498 x_return_status := FND_API.G_RET_STS_SUCCESS;
499
500 IF G_DEBUG='Y' THEN
501 AHL_DEBUG_PUB.enable_debug;
502 AHL_DEBUG_PUB.debug('Ahl Validating TxnType...['||to_Char(p_x_ahl_mtlxfr_rec.Transaction_Type_Id)||']');
503 END IF;
504
505 -- Validate transaction type
506 IF p_x_ahl_mtlxfr_rec.Transaction_Type_Id IS NULL THEN
507 FND_MESSAGE.Set_Name('AHL','AHL_CMP_TXNTYPE_NULL');
508 FND_MSG_PUB.ADD;
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 ELSE
511 OPEN TRX_ACTION_CUR(P_X_AHL_MTLXFR_REC.TRANSACTION_TYPE_ID);
512 FETCH TRX_ACTION_CUR INTO l_txn_type;
513 IF(TRX_ACTION_CUR%NOTFOUND) THEN
514 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_TXNTYPE');
515 FND_MSG_PUB.ADD;
516 x_return_status := FND_API.G_RET_STS_ERROR;
517 END IF;
518 CLOSE TRX_ACTION_CUR;
519 END IF;
520
521 -- transaction quantity should be +ve always.
522 IF G_DEBUG='Y' THEN
523 AHL_DEBUG_PUB.debug('Validating quantity...['||to_Char(p_x_ahl_mtlxfr_rec.Quantity)||']');
524 END IF;
525
526 IF(nvl(p_x_ahl_mtlxfr_rec.Quantity,0) <= 0) THEN
527 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_QTY');
528 FND_MESSAGE.Set_Token('QUANTITY',p_x_ahl_mtlxfr_rec.Quantity);
529 FND_MSG_PUB.ADD;
530 x_return_status := FND_API.G_RET_STS_ERROR;
531 END IF;
532
533 IF G_DEBUG='Y' THEN
534 AHL_DEBUG_PUB.debug('Validating item_id['||to_Char(p_x_ahl_mtlxfr_rec.Organization_ID)||','||to_Char(p_x_ahl_mtlxfr_rec.Inventory_Item_Id)||']');
535 END IF;
536
537
538 -- Validate Item id
539 IF (p_x_ahl_mtlxfr_rec.Organization_ID IS NOT NULL AND p_x_ahl_mtlxfr_rec.Inventory_Item_Id IS NOT NULL) THEN
540
541 OPEN AHL_ITEM_ID_CUR(p_x_ahl_mtlxfr_rec.Organization_ID,
542 p_x_ahl_mtlxfr_rec.Inventory_Item_Id);
543 FETCH AHL_ITEM_ID_CUR INTO l_serial_Control, l_lot_control, l_revision_control, l_location_control, l_primary_uom_code, l_concatenated_segments;
544 IF(AHL_ITEM_ID_CUR%NOTFOUND) THEN
545 FND_MESSAGE.Set_Name('AHL','AHL_COM_INVALID_ITEM');
546 FND_MESSAGE.Set_Token('FIELD',p_x_ahl_mtlxfr_rec.Inventory_Item_Id);
547 FND_MSG_PUB.ADD;
548 x_return_status := FND_API.G_RET_STS_ERROR;
549 END IF;
550 CLOSE AHL_ITEM_ID_CUR;
551 ELSE
552 IF p_x_ahl_mtlxfr_rec.Organization_ID IS NULL THEN
553 FND_MESSAGE.Set_Name('AHL','AHL_CMP_ORG_NULL');
554 FND_MSG_PUB.ADD;
555 x_return_status := FND_API.G_RET_STS_ERROR;
556 END IF;
557 IF p_x_ahl_mtlxfr_rec.Inventory_Item_Id IS NULL THEN
558 FND_MESSAGE.Set_Name('AHL','AHL_CMP_ITEM_NULL');
559 FND_MSG_PUB.ADD;
560 x_return_status := FND_API.G_RET_STS_ERROR;
561 END IF;
562
563 END IF;
564
565
566 IF G_DEBUG='Y' THEN
567 AHL_DEBUG_PUB.debug('Validating serial num(control, srl_num)['||to_Char(l_serial_Control)||','||p_x_ahl_mtlxfr_rec.Serial_Number||']');
568 END IF;
569
570 -- If the item is of serial controlled check if the serial numebr is null
571 IF(p_x_ahl_mtlxfr_rec.Serial_Number IS NULL
572 AND (nvl(l_serial_Control,0) <> nvl(C_NO_SERIAL_CONTROL,0)) ) THEN
573 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_SRLNUM');
574 FND_MESSAGE.Set_Token('SER',p_x_ahl_mtlxfr_rec.Serial_Number);
575 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
576 FND_MSG_PUB.ADD;
577 x_return_status := FND_API.G_RET_STS_ERROR;
578 END IF;
579
580 IF (p_x_ahl_mtlxfr_rec.Serial_Number IS NOT NULL AND
581 nvl(l_serial_Control,1) = 1) THEN
582 FND_MESSAGE.Set_Name('AHL','AHL_PRD_SRLNUM_NOTMAND');
583 FND_MESSAGE.Set_Token('SER',p_x_ahl_mtlxfr_rec.Serial_Number);
584 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
585 FND_MSG_PUB.ADD;
586 x_return_status := FND_API.G_RET_STS_ERROR;
587 END IF;
588
589
590 IF G_DEBUG='Y' THEN
591 AHL_DEBUG_PUB.debug('Validating lot num(control, lot_num)['||to_Char(l_lot_Control)||','||p_x_ahl_mtlxfr_rec.Lot_Number||']');
592 END IF;
593
594 -- If the item is of lot controlled check if the lot number is null
595 IF((p_x_ahl_mtlxfr_rec.Lot_Number IS NULL
596 OR p_x_ahl_mtlxfr_rec.Lot_Number = FND_API.G_MISS_CHAR)
597 AND (l_lot_Control <> C_NO_LOT_CONTROL) ) THEN
598 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_LOT');
599 FND_MESSAGE.Set_Token('LOT',p_x_ahl_mtlxfr_rec.Lot_Number);
600 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
601 FND_MSG_PUB.ADD;
602 x_return_status := FND_API.G_RET_STS_ERROR;
603 END IF;
604 -- validate lot number.
605 IF (p_x_ahl_mtlxfr_rec.Lot_Number IS NOT NULL AND (l_lot_Control <> C_NO_LOT_CONTROL) ) THEN
606 OPEN mtl_lot_num_csr(p_x_ahl_mtlxfr_rec.organization_id,
607 p_x_ahl_mtlxfr_rec.inventory_item_id,
608 p_x_ahl_mtlxfr_rec.Lot_Number);
609 FETCH mtl_lot_num_csr INTO l_junk;
610 IF (mtl_lot_num_csr%NOTFOUND) THEN
611 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_LOT');
612 FND_MESSAGE.Set_Token('LOT',p_x_ahl_mtlxfr_rec.Lot_Number);
613 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
614 FND_MSG_PUB.ADD;
615 x_return_status := FND_API.G_RET_STS_ERROR;
616 END IF;
617 CLOSE mtl_lot_num_csr;
618 END IF;
619
620 -- raise error if item is not lot controlled.
621 IF (p_x_ahl_mtlxfr_rec.Lot_Number IS NOT NULL AND (l_lot_Control = C_NO_LOT_CONTROL) ) THEN
622 FND_MESSAGE.Set_Name('AHL','AHL_PRD_LOT_NOTNULL');
623 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
624 FND_MSG_PUB.ADD;
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 END IF;
627
628
629 -- validate source sub inventory
630 OPEN AHL_SUBINV_CUR(p_x_ahl_mtlxfr_rec.Organization_ID,
631 p_x_ahl_mtlxfr_rec.Src_Subinv_Name);
632 FETCH AHL_SUBINV_CUR INTO l_Count;
633 IF(AHL_SUBINV_CUR%NOTFOUND) THEN
634 FND_MESSAGE.Set_Name('AHL','AHL_CMP_INVL_SRC_SUBINV');
635 FND_MESSAGE.Set_Token('INV',p_x_ahl_mtlxfr_rec.Src_Subinv_Name);
636 FND_MSG_PUB.ADD;
637 x_return_status := FND_API.G_RET_STS_ERROR;
638 END IF;
639 CLOSE AHL_SUBINV_CUR;
640
641 -- validate destination sub inventory
642 OPEN AHL_SUBINV_CUR(p_x_ahl_mtlxfr_rec.Organization_ID,
643 p_x_ahl_mtlxfr_rec.Dst_SubInv_Name);
644 FETCH AHL_SUBINV_CUR INTO l_Count;
645 IF(AHL_SUBINV_CUR%NOTFOUND) THEN
646 FND_MESSAGE.Set_Name('AHL','AHL_CMP_INVL_DST_SUBINV');
647 FND_MESSAGE.Set_Token('INV',p_x_ahl_mtlxfr_rec.Dst_SubInv_Name);
648 FND_MSG_PUB.ADD;
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 END IF;
651 CLOSE AHL_SUBINV_CUR;
652
653 -- validate source locator
654 OPEN AHL_LOCATOR_CUR(p_x_ahl_mtlxfr_rec.Organization_ID,
655 p_x_ahl_mtlxfr_rec.Src_Locator_Id);
656 FETCH AHL_LOCATOR_CUR INTO l_Count;
657 IF(AHL_LOCATOR_CUR%NOTFOUND) THEN
658 FND_MESSAGE.Set_Name('AHL','AHL_CMP_INVL_SRC_LOCATOR');
659 FND_MESSAGE.Set_Token('LOC',p_x_ahl_mtlxfr_rec.Src_Locator_Id);
660 FND_MSG_PUB.ADD;
661 x_return_status := FND_API.G_RET_STS_ERROR;
662 END IF;
663 CLOSE AHL_LOCATOR_CUR;
664
665
666 -- validate destination locator
667 IF p_x_ahl_mtlxfr_rec.Dst_Locator_Id IS NOT NULL THEN
668 OPEN AHL_LOCATOR_CUR(p_x_ahl_mtlxfr_rec.Organization_ID,
669 p_x_ahl_mtlxfr_rec.Dst_Locator_Id);
670 FETCH AHL_LOCATOR_CUR INTO l_Count;
671 IF(AHL_LOCATOR_CUR%NOTFOUND) THEN
672 FND_MESSAGE.Set_Name('AHL','AHL_CMP_INVL_DST_LOCATOR');
673 FND_MESSAGE.Set_Token('LOC',p_x_ahl_mtlxfr_rec.Dst_Locator_Id);
674 FND_MSG_PUB.ADD;
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 END IF;
677 CLOSE AHL_LOCATOR_CUR;
678 END IF;
679
680
681 IF G_DEBUG='Y' THEN
682 AHL_DEBUG_PUB.debug('Validating revision['||p_x_ahl_mtlxfr_rec.Revision||']');
683 END IF;
684
685
686 -- If item is revision-controlled, then verify revision is NOT NULL and is valid
687 -- If item is not revision-controlled, then verify revision is NULL
688 IF (nvl(l_revision_control, -1) = 2)
689 THEN
690 IF p_x_ahl_mtlxfr_rec.Revision IS NULL
691 THEN
692 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_REVISION');
693 FND_MESSAGE.Set_Token('REVISION',p_x_ahl_mtlxfr_rec.Revision);
694 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
695 FND_MSG_PUB.ADD;
696 x_return_status := FND_API.G_RET_STS_ERROR;
697 ELSE
698 OPEN AHL_REVISION_CUR
699 (
700 p_x_ahl_mtlxfr_rec.Organization_ID,
701 p_x_ahl_mtlxfr_rec.Inventory_Item_Id,
702 p_x_ahl_mtlxfr_rec.Revision
703 );
704 FETCH AHL_REVISION_CUR INTO l_Count;
705 IF (AHL_REVISION_CUR%NOTFOUND)
706 THEN
707 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_REVISION');
708 FND_MESSAGE.Set_Token('REVISION',p_x_ahl_mtlxfr_rec.Revision);
709 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
710 FND_MSG_PUB.ADD;
711 x_return_status := FND_API.G_RET_STS_ERROR;
712
713 END IF;
714 CLOSE AHL_REVISION_CUR;
715 END IF;
716 ELSE
717 IF p_x_ahl_mtlxfr_rec.Revision IS NOT NULL
718 THEN
719 FND_MESSAGE.Set_Name('AHL','AHL_PRD_REVNUM_NOTMAND');
720 FND_MESSAGE.Set_Token('REV',p_x_ahl_mtlxfr_rec.Revision);
721 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
722 FND_MSG_PUB.ADD;
723 x_return_status := FND_API.G_RET_STS_ERROR;
724 END IF;
725 END IF;
726
727
728 --If the serial number is not null and the quantity is <>1 return the
729 --Error AHL_INVALID_SRL_QTY
730
731 IF G_DEBUG='Y' THEN
732 AHL_DEBUG_PUB.debug('Validating serial num/qty['||p_x_ahl_mtlxfr_rec.quantity||']');
733 END IF;
734
735 IF ((p_x_ahl_mtlxfr_rec.Serial_Number IS NOT NULL) AND nvl(l_serial_Control,1) <> 1) THEN
736 IF (p_x_ahl_mtlxfr_rec.Quantity <> 1) THEN
737 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_ITEM_QTY');
738 FND_MESSAGE.Set_Token('PRIM_UOM',l_primary_uom_code);
739 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
740 FND_MSG_PUB.ADD;
741 x_return_status := FND_API.G_RET_STS_ERROR;
742 END IF;
743 END IF;
744
745 IF G_DEBUG='Y' THEN
746 AHL_DEBUG_PUB.debug('Validating serial subinv, loc['||p_x_ahl_mtlxfr_rec.Serial_Number||']');
747 END IF;
748
749 -- For issue txn with serial number, validate if subinventory and locator match
750 -- that from mtl_serial_numbers.
751 IF (p_x_ahl_mtlxfr_rec.Serial_Number IS NOT NULL) AND (nvl(l_serial_Control,0) <> 1) THEN
752 OPEN mtl_srl_num_csr(p_x_ahl_mtlxfr_rec.Organization_ID,
753 p_x_ahl_mtlxfr_rec.Inventory_Item_id,
754 p_x_ahl_mtlxfr_rec.Serial_Number);
755 FETCH mtl_srl_num_csr INTO l_curr_subinventory_code, l_curr_locator_id;
756 IF (mtl_srl_num_csr%FOUND) THEN
757
758 IF G_DEBUG='Y' THEN
759 AHL_DEBUG_PUB.debug('Validating serial subinv['||p_x_ahl_mtlxfr_rec.Src_Subinv_Name||']');
760 END IF;
761
762 IF (p_x_ahl_mtlxfr_rec.Src_Subinv_Name IS NOT NULL AND
763 p_x_ahl_mtlxfr_rec.Src_Subinv_Name <> l_curr_subinventory_code) THEN
764 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_SUB_CODE');
765 FND_MESSAGE.Set_Token('CODE',l_curr_subinventory_code);
766 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
767 FND_MESSAGE.Set_Token('SER',p_x_ahl_mtlxfr_rec.Serial_Number);
768 FND_MSG_PUB.ADD;
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 END IF;
771
772 IF G_DEBUG='Y' THEN
773 AHL_DEBUG_PUB.debug('Validating serial subinv['||p_x_ahl_mtlxfr_rec.Src_Subinv_Name||']');
774 END IF;
775
776 IF (p_x_ahl_mtlxfr_rec.Src_Locator_Id IS NOT NULL AND l_curr_locator_id IS NOT NULL AND
777 p_x_ahl_mtlxfr_rec.Src_Locator_Id <> l_curr_locator_id) THEN
778 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_LOC_CODE');
779 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
780 FND_MESSAGE.Set_Token('SER',p_x_ahl_mtlxfr_rec.Serial_Number);
781 FND_MSG_PUB.ADD;
782 x_return_status := FND_API.G_RET_STS_ERROR;
783 END IF;
784 ELSE
785 -- serial number not found.
786 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_SRLNUM');
787 FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
788 FND_MESSAGE.Set_Token('SER',p_x_ahl_mtlxfr_rec.Serial_Number);
789 FND_MSG_PUB.ADD;
790 x_return_status := FND_API.G_RET_STS_ERROR;
791 END IF;
792
793 CLOSE mtl_srl_num_csr;
794 END IF;
795 END Validate_Rec;
796
797 PROCEDURE INSERT_MTL_TXN_INTF
798 (
799 p_x_ahl_mtlxfr_rec IN OUT NOCOPY Ahl_Mtlxfr_Rec_Type,
800 p_x_txn_hdr_id IN OUT NOCOPY NUMBER,
801 p_x_txn_intf_id IN OUT NOCOPY NUMBER,
802 x_return_status OUT NOCOPY VARCHAR2
803 )
804 IS
805 l_Process_Flag VARCHAR2(1);
806 l_Validation_required VARCHAR2(1);
807 l_transaction_Mode NUMBER;
808 l_source_code VARCHAR2(240);
809 l_source_line_id NUMBER;
810 l_txn_tmp_id NUMBER;
811 l_Source_Header_Id NUMBER;
812 l_lot_expiration_Date DATE;
813 l_txn_action NUMBER;
814 l_txn_source_type NUMBER;
815 --l_qty NUMBER;
816 l_reservation_flag VARCHAR2(1);
817
818 -- added to support dynamic locator creation.
819 l_mti_seglist fnd_flex_ext.SegmentArray;
820
821 -- l_loop_count NUMBER;
822
823 CURSOR TRX_ACTION_CUR(p_type_Id NUMBER) IS
824 SELECT TRANSACTION_ACTION_ID,TRANSACTION_SOURCE_TYPE_ID
825 from MTL_TRANSACTION_TYPES
826 where TRANSACTION_TYPE_ID = p_type_Id;
827
828 CURSOR get_lot_dff_attrib(p_lot_number IN VARCHAR2) IS
829 SELECT ATTRIBUTE_CATEGORY,
830 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
831 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
832 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
833 C_ATTRIBUTE1, C_ATTRIBUTE2, C_ATTRIBUTE3, C_ATTRIBUTE4, C_ATTRIBUTE5,
834 C_ATTRIBUTE6, C_ATTRIBUTE7, C_ATTRIBUTE8, C_ATTRIBUTE9, C_ATTRIBUTE10,
835 C_ATTRIBUTE11, C_ATTRIBUTE12, C_ATTRIBUTE13, C_ATTRIBUTE14, C_ATTRIBUTE15,
836 C_ATTRIBUTE16, C_ATTRIBUTE17, C_ATTRIBUTE18, C_ATTRIBUTE19, C_ATTRIBUTE20,
837 D_ATTRIBUTE1, D_ATTRIBUTE2, D_ATTRIBUTE3, D_ATTRIBUTE4, D_ATTRIBUTE5,
838 D_ATTRIBUTE6, D_ATTRIBUTE7, D_ATTRIBUTE8, D_ATTRIBUTE9, D_ATTRIBUTE10,
839 N_ATTRIBUTE1, N_ATTRIBUTE2, N_ATTRIBUTE3, N_ATTRIBUTE4, N_ATTRIBUTE5,
840 N_ATTRIBUTE6, N_ATTRIBUTE7, N_ATTRIBUTE8, N_ATTRIBUTE9, N_ATTRIBUTE10
841 FROM mtl_lot_numbers
842 where lot_number = p_lot_number;
843
844 l_lot_dff_rec get_lot_dff_attrib%ROWTYPE;
845
846
847 CURSOR get_serial_dff_attrib(p_inv_item_id IN NUMBER,
848 p_serial_number IN VARCHAR2) IS
849 SELECT ATTRIBUTE_CATEGORY,
850 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
851 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
852 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
853 C_ATTRIBUTE1, C_ATTRIBUTE2, C_ATTRIBUTE3, C_ATTRIBUTE4, C_ATTRIBUTE5,
854 C_ATTRIBUTE6, C_ATTRIBUTE7, C_ATTRIBUTE8, C_ATTRIBUTE9, C_ATTRIBUTE10,
855 C_ATTRIBUTE11, C_ATTRIBUTE12, C_ATTRIBUTE13, C_ATTRIBUTE14, C_ATTRIBUTE15,
856 C_ATTRIBUTE16, C_ATTRIBUTE17, C_ATTRIBUTE18, C_ATTRIBUTE19, C_ATTRIBUTE20,
857 D_ATTRIBUTE1, D_ATTRIBUTE2, D_ATTRIBUTE3, D_ATTRIBUTE4, D_ATTRIBUTE5,
858 D_ATTRIBUTE6, D_ATTRIBUTE7, D_ATTRIBUTE8, D_ATTRIBUTE9, D_ATTRIBUTE10,
859 N_ATTRIBUTE1, N_ATTRIBUTE2, N_ATTRIBUTE3, N_ATTRIBUTE4, N_ATTRIBUTE5,
860 N_ATTRIBUTE6, N_ATTRIBUTE7, N_ATTRIBUTE8, N_ATTRIBUTE9, N_ATTRIBUTE10
861 FROM mtl_serial_numbers
862 where inventory_item_id = p_inv_item_id
863 and serial_number = p_serial_number;
864
865 l_serial_dff_rec get_serial_dff_attrib%ROWTYPE;
866
867
868 BEGIN
869
870 l_Process_Flag := '1'; --transaction ready
871 l_Validation_required := '1'; --yes
872 l_transaction_Mode := 2; --concurrent processing mode
873 l_source_code := 'AHL';
874 l_source_line_id := 1;
875 l_Source_Header_Id := 1;
876
877
878 x_return_status := FND_API.G_RET_STS_SUCCESS;
879
880 OPEN TRX_ACTION_CUR(p_x_ahl_mtlxfr_rec.Transaction_Type_Id);
881 FETCH TRX_ACTION_CUR INTO l_txn_action, l_txn_source_type;
882 IF(TRX_ACTION_CUR%NOTFOUND) THEN
883 IF G_DEBUG='Y' THEN
884 AHL_DEBUG_PUB.debug('Did not find the Txn Type');
885 END IF;
886 CLOSE TRX_ACTION_CUR;
887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
888 END IF;
889 CLOSE TRX_ACTION_CUR;
890
891
892 IF(p_x_txn_hdr_id IS NULL) THEN
893 SELECT Mtl_Material_Transactions_S.nextval
894 INTO p_x_txn_hdr_id
895 FROM DUAL;
896 IF G_DEBUG='Y' THEN
897 AHL_DEBUG_PUB.debug('Header id created..['||p_x_txn_hdr_id||']');
898 END IF;
899 END IF;
900
901 --l_loop_count := 0;
902
903 IF ( p_x_ahl_mtlxfr_rec.Quantity > 0) THEN
904
905 SELECT Mtl_Material_Transactions_S.nextval
906 INTO p_x_txn_intf_id
907 FROM DUAL;
908
909 IF G_DEBUG='Y' THEN
910 AHL_DEBUG_PUB.debug('Interface id created..['||p_x_txn_intf_id||']');
911 END IF;
912
913 --l_qty := 1;
914 --l_loop_count := l_loop_count + 1;
915
916 IF G_DEBUG='Y' THEN
917 AHL_DEBUG_PUB.debug('Before dynamic chk:loc segs:ID' || p_x_ahl_mtlxfr_rec.Dst_Locator_Segments || ':' || p_x_ahl_mtlxfr_rec.Dst_Locator_Id);
918 END IF;
919
920
921 -- support dynamic locator creation if allowed.
922 -- inv will validate. We will just split and pass the locator segments.
923
924 -- initialze mti locator segment values.
925 FOR i IN 1..20 LOOP
926 l_mti_seglist(i) := null;
927 END LOOP;
928
929 IF (p_x_ahl_mtlxfr_rec.Dst_Locator_Id IS NULL AND
930 p_x_ahl_mtlxfr_rec.Dst_Locator_Segments IS NOT NULL) THEN
931 AHL_PRD_MTLTXN_PVT.Get_MTL_LocatorSegs (p_concat_segs => p_x_ahl_mtlxfr_rec.Dst_Locator_Segments,
932 p_organization_id => p_x_ahl_mtlxfr_rec.organization_id,
933 p_x_mti_seglist => l_mti_seglist,
934 x_return_status => x_return_status);
935
936 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
937 RAISE FND_API.G_EXC_ERROR;
938 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
939 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
940 END IF;
941 END IF;
942
943 --If the lot controlled inventory record is not null insert reocrds
944 -- into transaction lots interface table.
945
946 IF (p_x_ahl_mtlxfr_rec.Lot_Number IS NOT NULL) THEN
947
948 OPEN get_lot_dff_attrib(p_x_ahl_mtlxfr_rec.Lot_Number);
949 FETCH get_lot_dff_attrib INTO l_lot_dff_rec;
950 CLOSE get_lot_dff_attrib;
951
952 IF(p_x_ahl_mtlxfr_rec.Serial_Number IS NOT NULL) THEN
953 SELECT Mtl_Material_Transactions_S.nextval
954 INTO l_txn_tmp_id
955 FROM DUAL;
956 ELSE
957 l_txn_tmp_id := p_x_txn_intf_id;
958 END IF;
959
960 IF G_DEBUG='Y' THEN
961 AHL_DEBUG_PUB.debug('insertng the lot record,interface id,tempid['
962 ||to_char(p_x_txn_intf_id)||','
963 ||to_char(l_txn_tmp_id)||']');
964 END IF;
965
966 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
967 ( TRANSACTION_INTERFACE_ID ,
968 SOURCE_CODE ,
969 SOURCE_LINE_ID ,
970 LAST_UPDATE_DATE ,
971 LAST_UPDATED_BY ,
972 CREATION_DATE ,
973 CREATED_BY ,
974 LAST_UPDATE_LOGIN ,
975 LOT_NUMBER ,
976 LOT_EXPIRATION_DATE ,
977 TRANSACTION_QUANTITY ,
978 PRIMARY_QUANTITY,
979 SERIAL_TRANSACTION_TEMP_ID,
980 ATTRIBUTE_CATEGORY,
981 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
982 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
983 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
984 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, C_ATTRIBUTE1,
985 C_ATTRIBUTE2, C_ATTRIBUTE3, C_ATTRIBUTE4, C_ATTRIBUTE5,
986 C_ATTRIBUTE6, C_ATTRIBUTE7, C_ATTRIBUTE8, C_ATTRIBUTE9,
987 C_ATTRIBUTE10, C_ATTRIBUTE11, C_ATTRIBUTE12, C_ATTRIBUTE13,
988 C_ATTRIBUTE14, C_ATTRIBUTE15, C_ATTRIBUTE16, C_ATTRIBUTE17,
989 C_ATTRIBUTE18, C_ATTRIBUTE19, C_ATTRIBUTE20, D_ATTRIBUTE1,
990 D_ATTRIBUTE2, D_ATTRIBUTE3, D_ATTRIBUTE4, D_ATTRIBUTE5,
991 D_ATTRIBUTE6, D_ATTRIBUTE7, D_ATTRIBUTE8, D_ATTRIBUTE9,
992 D_ATTRIBUTE10, N_ATTRIBUTE1, N_ATTRIBUTE2, N_ATTRIBUTE3,
993 N_ATTRIBUTE4, N_ATTRIBUTE5, N_ATTRIBUTE6, N_ATTRIBUTE7,
994 N_ATTRIBUTE8, N_ATTRIBUTE9, N_ATTRIBUTE10
995 )
996 VALUES(p_x_txn_intf_id,
997 l_Source_Code,
998 l_Source_Line_Id,
999 sysdate,
1000 FND_GLOBAL.USER_ID,
1001 sysdate,
1002 FND_GLOBAL.USER_ID,
1003 FND_GLOBAL.LOGIN_ID,
1004 p_x_ahl_mtlxfr_rec.Lot_Number,
1005 l_lot_expiration_Date,
1006 p_x_ahl_mtlxfr_rec.Quantity,
1007 p_x_ahl_mtlxfr_rec.Quantity,
1008 l_txn_tmp_id,
1009 l_lot_dff_rec.ATTRIBUTE_CATEGORY,
1010 l_lot_dff_rec.ATTRIBUTE1, l_lot_dff_rec.ATTRIBUTE2, l_lot_dff_rec.ATTRIBUTE3, l_lot_dff_rec.ATTRIBUTE4,
1011 l_lot_dff_rec.ATTRIBUTE5, l_lot_dff_rec.ATTRIBUTE6, l_lot_dff_rec.ATTRIBUTE7, l_lot_dff_rec.ATTRIBUTE8,
1012 l_lot_dff_rec.ATTRIBUTE9, l_lot_dff_rec.ATTRIBUTE10, l_lot_dff_rec.ATTRIBUTE11, l_lot_dff_rec.ATTRIBUTE12,
1013 l_lot_dff_rec.ATTRIBUTE13, l_lot_dff_rec.ATTRIBUTE14, l_lot_dff_rec.ATTRIBUTE15,
1014 l_lot_dff_rec.C_ATTRIBUTE1, l_lot_dff_rec.C_ATTRIBUTE2, l_lot_dff_rec.C_ATTRIBUTE3, l_lot_dff_rec.C_ATTRIBUTE4,
1015 l_lot_dff_rec.C_ATTRIBUTE5, l_lot_dff_rec.C_ATTRIBUTE6, l_lot_dff_rec.C_ATTRIBUTE7, l_lot_dff_rec.C_ATTRIBUTE8,
1016 l_lot_dff_rec.C_ATTRIBUTE9, l_lot_dff_rec.C_ATTRIBUTE10, l_lot_dff_rec.C_ATTRIBUTE11, l_lot_dff_rec.C_ATTRIBUTE12,
1017 l_lot_dff_rec.C_ATTRIBUTE13, l_lot_dff_rec.C_ATTRIBUTE14, l_lot_dff_rec.C_ATTRIBUTE15, l_lot_dff_rec.C_ATTRIBUTE16,
1018 l_lot_dff_rec.C_ATTRIBUTE17, l_lot_dff_rec.C_ATTRIBUTE18, l_lot_dff_rec.C_ATTRIBUTE19, l_lot_dff_rec.C_ATTRIBUTE20,
1019 l_lot_dff_rec.D_ATTRIBUTE1, l_lot_dff_rec.D_ATTRIBUTE2, l_lot_dff_rec.D_ATTRIBUTE3, l_lot_dff_rec.D_ATTRIBUTE4,
1020 l_lot_dff_rec.D_ATTRIBUTE5, l_lot_dff_rec.D_ATTRIBUTE6, l_lot_dff_rec.D_ATTRIBUTE7, l_lot_dff_rec.D_ATTRIBUTE8,
1021 l_lot_dff_rec.D_ATTRIBUTE9, l_lot_dff_rec.D_ATTRIBUTE10,
1022 l_lot_dff_rec.N_ATTRIBUTE1, l_lot_dff_rec.N_ATTRIBUTE2, l_lot_dff_rec.N_ATTRIBUTE3, l_lot_dff_rec.N_ATTRIBUTE4,
1023 l_lot_dff_rec.N_ATTRIBUTE5, l_lot_dff_rec.N_ATTRIBUTE6, l_lot_dff_rec.N_ATTRIBUTE7, l_lot_dff_rec.N_ATTRIBUTE8,
1024 l_lot_dff_rec.N_ATTRIBUTE9, l_lot_dff_rec.N_ATTRIBUTE10
1025 );
1026
1027 END IF;
1028
1029 --If the serial controlled rec is not null then insert records
1030 -- into the serial numbers interface table
1031 IF (p_x_ahl_mtlxfr_rec.Serial_Number IS NOT NULL) THEN
1032
1033
1034 OPEN get_serial_dff_attrib(p_x_ahl_mtlxfr_rec.Inventory_Item_Id, p_x_ahl_mtlxfr_rec.Serial_Number);
1035 FETCH get_serial_dff_attrib INTO l_serial_dff_rec;
1036 CLOSE get_serial_dff_attrib;
1037
1038 IF (p_x_ahl_mtlxfr_rec.Lot_Number IS NULL) THEN
1039 l_txn_tmp_id := p_x_txn_intf_id;
1040 END IF;
1041
1042 IF G_DEBUG='Y' THEN
1043 AHL_DEBUG_PUB.debug('insertng the serial record,interface id['
1044 ||to_char(l_txn_tmp_id)||']');
1045 END IF;
1046
1047
1048 INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
1049 TRANSACTION_INTERFACE_ID, SOURCE_CODE, SOURCE_LINE_ID,
1050 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
1051 CREATED_BY, LAST_UPDATE_LOGIN, FM_SERIAL_NUMBER,
1052 TO_SERIAL_NUMBER, PROCESS_FLAG,
1053 ATTRIBUTE_CATEGORY,
1054 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
1055 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
1056 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1057 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, C_ATTRIBUTE1,
1058 C_ATTRIBUTE2, C_ATTRIBUTE3, C_ATTRIBUTE4, C_ATTRIBUTE5,
1059 C_ATTRIBUTE6, C_ATTRIBUTE7, C_ATTRIBUTE8, C_ATTRIBUTE9,
1060 C_ATTRIBUTE10, C_ATTRIBUTE11, C_ATTRIBUTE12, C_ATTRIBUTE13,
1061 C_ATTRIBUTE14, C_ATTRIBUTE15, C_ATTRIBUTE16, C_ATTRIBUTE17,
1062 C_ATTRIBUTE18, C_ATTRIBUTE19, C_ATTRIBUTE20, D_ATTRIBUTE1,
1063 D_ATTRIBUTE2, D_ATTRIBUTE3, D_ATTRIBUTE4, D_ATTRIBUTE5,
1064 D_ATTRIBUTE6, D_ATTRIBUTE7, D_ATTRIBUTE8, D_ATTRIBUTE9,
1065 D_ATTRIBUTE10, N_ATTRIBUTE1, N_ATTRIBUTE2, N_ATTRIBUTE3,
1066 N_ATTRIBUTE4, N_ATTRIBUTE5, N_ATTRIBUTE6, N_ATTRIBUTE7,
1067 N_ATTRIBUTE8, N_ATTRIBUTE9, N_ATTRIBUTE10
1068 )
1069 VALUES ( l_txn_tmp_id,
1070 l_source_code,
1071 l_source_line_id,
1072 SYSDATE,
1073 FND_GLOBAL.USER_ID,
1074 SYSDATE,
1075 FND_GLOBAL.USER_ID,
1076 FND_GLOBAL.LOGIN_ID,
1077 p_x_ahl_mtlxfr_rec.Serial_Number,
1078 p_x_ahl_mtlxfr_rec.Serial_Number,
1079 l_Process_Flag,
1080 l_serial_dff_rec.ATTRIBUTE_CATEGORY,
1081 l_serial_dff_rec.ATTRIBUTE1, l_serial_dff_rec.ATTRIBUTE2, l_serial_dff_rec.ATTRIBUTE3, l_serial_dff_rec.ATTRIBUTE4,
1082 l_serial_dff_rec.ATTRIBUTE5, l_serial_dff_rec.ATTRIBUTE6, l_serial_dff_rec.ATTRIBUTE7, l_serial_dff_rec.ATTRIBUTE8,
1083 l_serial_dff_rec.ATTRIBUTE9, l_serial_dff_rec.ATTRIBUTE10, l_serial_dff_rec.ATTRIBUTE11, l_serial_dff_rec.ATTRIBUTE12,
1084 l_serial_dff_rec.ATTRIBUTE13, l_serial_dff_rec.ATTRIBUTE14, l_serial_dff_rec.ATTRIBUTE15,
1085 l_serial_dff_rec.C_ATTRIBUTE1, l_serial_dff_rec.C_ATTRIBUTE2, l_serial_dff_rec.C_ATTRIBUTE3,
1086 l_serial_dff_rec.C_ATTRIBUTE4, l_serial_dff_rec.C_ATTRIBUTE5,
1087 l_serial_dff_rec.C_ATTRIBUTE6, l_serial_dff_rec.C_ATTRIBUTE7,
1088 l_serial_dff_rec.C_ATTRIBUTE8, l_serial_dff_rec.C_ATTRIBUTE9,
1089 l_serial_dff_rec.C_ATTRIBUTE10, l_serial_dff_rec.C_ATTRIBUTE11,
1090 l_serial_dff_rec.C_ATTRIBUTE12, l_serial_dff_rec.C_ATTRIBUTE13,
1091 l_serial_dff_rec.C_ATTRIBUTE14, l_serial_dff_rec.C_ATTRIBUTE15,
1092 l_serial_dff_rec.C_ATTRIBUTE16, l_serial_dff_rec.C_ATTRIBUTE17,
1093 l_serial_dff_rec.C_ATTRIBUTE18, l_serial_dff_rec.C_ATTRIBUTE19,
1094 l_serial_dff_rec.C_ATTRIBUTE20, l_serial_dff_rec.D_ATTRIBUTE1,
1095 l_serial_dff_rec.D_ATTRIBUTE2, l_serial_dff_rec.D_ATTRIBUTE3,
1096 l_serial_dff_rec.D_ATTRIBUTE4, l_serial_dff_rec.D_ATTRIBUTE5,
1097 l_serial_dff_rec.D_ATTRIBUTE6, l_serial_dff_rec.D_ATTRIBUTE7,
1098 l_serial_dff_rec.D_ATTRIBUTE8, l_serial_dff_rec.D_ATTRIBUTE9,
1099 l_serial_dff_rec.D_ATTRIBUTE10, l_serial_dff_rec.N_ATTRIBUTE1,
1100 l_serial_dff_rec.N_ATTRIBUTE2, l_serial_dff_rec.N_ATTRIBUTE3,
1101 l_serial_dff_rec.N_ATTRIBUTE4, l_serial_dff_rec.N_ATTRIBUTE5,
1102 l_serial_dff_rec.N_ATTRIBUTE6, l_serial_dff_rec.N_ATTRIBUTE7,
1103 l_serial_dff_rec.N_ATTRIBUTE8, l_serial_dff_rec.N_ATTRIBUTE9,
1104 l_serial_dff_rec.N_ATTRIBUTE10
1105 );
1106
1107 END IF;
1108
1109 IF G_DEBUG='Y' THEN
1110 AHL_DEBUG_PUB.debug('insertng the txn record,header id,interface id['
1111 ||to_char(p_x_txn_hdr_id)||','
1112 ||to_char(p_x_txn_intf_id)||']');
1113 END IF;
1114
1115
1116 INSERT INTO MTL_TRANSACTIONS_INTERFACE
1117
1118 ( TRANSACTION_INTERFACE_ID , TRANSACTION_HEADER_ID ,
1119 SOURCE_CODE , SOURCE_LINE_ID ,
1120 SOURCE_HEADER_ID, PROCESS_FLAG ,
1121 VALIDATION_REQUIRED , TRANSACTION_MODE ,
1122 LAST_UPDATE_DATE , LAST_UPDATED_BY ,
1123 CREATION_DATE , CREATED_BY ,
1124 LAST_UPDATE_LOGIN , INVENTORY_ITEM_ID ,
1125 ORGANIZATION_ID , TRANSACTION_QUANTITY ,
1126 PRIMARY_QUANTITY , TRANSACTION_UOM ,
1127 TRANSACTION_DATE , SUBINVENTORY_CODE ,
1128 LOCATOR_ID , TRANSACTION_TYPE_ID ,
1129 REVISION , TRANSACTION_REFERENCE ,
1130 TRANSFER_SUBINVENTORY, TRANSFER_LOCATOR,
1131 XFER_LOC_SEGMENT1, XFER_LOC_SEGMENT2,
1132 XFER_LOC_SEGMENT3, XFER_LOC_SEGMENT4,
1133 XFER_LOC_SEGMENT5, XFER_LOC_SEGMENT6,
1134 XFER_LOC_SEGMENT7, XFER_LOC_SEGMENT8,
1135 XFER_LOC_SEGMENT9, XFER_LOC_SEGMENT10,
1136 XFER_LOC_SEGMENT11, XFER_LOC_SEGMENT12,
1137 XFER_LOC_SEGMENT13, XFER_LOC_SEGMENT14,
1138 XFER_LOC_SEGMENT15, XFER_LOC_SEGMENT16,
1139 XFER_LOC_SEGMENT17, XFER_LOC_SEGMENT18,
1140 XFER_LOC_SEGMENT19, XFER_LOC_SEGMENT20 )
1141 values (p_x_txn_intf_id, p_x_txn_hdr_id,
1142 l_Source_Code, l_Source_Line_Id,
1143 l_Source_Header_Id, l_Process_Flag,
1144 l_Validation_required , l_transaction_Mode,
1145 sysdate, FND_GLOBAL.USER_ID,
1146 sysdate, FND_GLOBAL.USER_ID,
1147 FND_GLOBAL.LOGIN_ID, p_x_ahl_mtlxfr_rec.Inventory_Item_Id,
1148 p_x_ahl_mtlxfr_rec.Organization_Id, p_x_ahl_mtlxfr_rec.Quantity,
1149 p_x_ahl_mtlxfr_rec.Quantity, p_x_ahl_mtlxfr_rec.UOM,
1150 sysdate , p_x_ahl_mtlxfr_rec.Src_Subinv_Name,
1151 p_x_ahl_mtlxfr_rec.Src_Locator_Id, p_x_ahl_mtlxfr_rec.Transaction_Type_Id,
1152 p_x_ahl_mtlxfr_rec.Revision, p_x_ahl_mtlxfr_rec.Transaction_Reference,
1153 p_x_ahl_mtlxfr_rec.Dst_SubInv_Name, p_x_ahl_mtlxfr_rec.Dst_Locator_Id,
1154 l_mti_seglist(1), l_mti_seglist(2),
1155 l_mti_seglist(3), l_mti_seglist(4),
1156 l_mti_seglist(5), l_mti_seglist(6),
1157 l_mti_seglist(7), l_mti_seglist(8),
1158 l_mti_seglist(9), l_mti_seglist(10),
1159 l_mti_seglist(11), l_mti_seglist(12),
1160 l_mti_seglist(13), l_mti_seglist(14),
1161 l_mti_seglist(15), l_mti_seglist(16),
1162 l_mti_seglist(17), l_mti_seglist(18),
1163 l_mti_seglist(19), l_mti_seglist(20)
1164 ) ;
1165 END IF; -- p_x_ahl_mtlxfr_rec.Quantity > 0
1166
1167 EXCEPTION
1168 WHEN OTHERS THEN
1169 x_return_status := FND_API.G_RET_STS_ERROR;
1170 IF G_DEBUG='Y' THEN
1171 AHL_DEBUG_PUB.debug('Exception inserting into mtl_txn interface' || SQLCODE);
1172 AHL_DEBUG_PUB.debug('SQLERRM:' || SQLERRM);
1173 END IF;
1174 FND_MESSAGE.Set_Name('AHL','AHL_CMP_MTLXFR_ERROR');
1175 FND_MESSAGE.Set_Token('MSG',SQLERRM);
1176 FND_MESSAGE.Set_Token('ITEM',p_x_ahl_mtlxfr_rec.Inventory_Item_Id);
1177 FND_MSG_PUB.ADD;
1178 RAISE FND_API.G_EXC_ERROR;
1179
1180 END INSERT_MTL_TXN_INTF;
1181 ---------------------------------------------------------------------------------------------------------
1182
1183 ---------------------------------------------------------------------------------------------------------
1184 -- Start of Comments
1185 -- Procedure name : Process_Repair_Item
1186 -- Type : Public
1187 -- Function : Procedure to process repair item by moving it from a sub-inventory / repair batch
1188 -- to a new / existing repair batch.
1189 --
1190 -- Pre-reqs :
1191 -- Parameters :
1192 --
1193 -- Process_Repair_Item Parameters:
1194 -- p_inv_item_id IN Repair item id. Required
1195 -- p_item_mstr_org_id IN Repair item's master org id. Required
1196 -- p_item_inv_org_id IN Repair item's inventory org id. Required
1197 -- p_instance_id IN Repair item's instance id. Required
1198 -- p_repair_qty IN Repair item quantity. Required
1199 -- p_opr_flag IN To indicate whether to create a destination Required
1200 -- repair batch or update an existing one.
1201 -- p_src_subinv_name IN Source sub-inventory code if the repair item Required
1202 -- item resides in it.
1203 -- p_src_locator_id IN Source sub-inventory locator id if the repair Required
1204 -- item resides in it.
1205 -- p_src_rpr_batch_name IN Source repair batch name if the repair item Required
1206 -- resides in it.
1207 -- p_x_dst_rpr_batch_name IN OUT Destination repair batch name. Required
1208 --
1209 -- End of Comments
1210
1211 PROCEDURE Process_Repair_Item (
1212 p_api_version IN NUMBER := 1.0,
1213 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1214 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1215 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1216 p_inv_item_id IN NUMBER,
1217 p_item_mstr_org_id IN NUMBER,
1218 p_item_inv_org_id IN NUMBER,
1219 p_instance_id IN NUMBER,
1220 p_repair_qty IN NUMBER,
1221 p_opr_flag IN VARCHAR2,
1222 p_src_subinv_name IN VARCHAR2,
1223 p_src_locator_id IN NUMBER,
1224 p_src_rpr_batch_name IN VARCHAR2,
1225 p_x_dst_rpr_batch_name IN OUT NOCOPY VARCHAR2,
1226 x_return_status OUT NOCOPY VARCHAR2,
1227 x_msg_count OUT NOCOPY NUMBER,
1228 x_msg_data OUT NOCOPY VARCHAR2
1229 ) IS
1230
1231 -- cursor to get the item and instance details
1232 CURSOR get_item_inst_det_csr (c_inv_item_id NUMBER,
1233 c_item_mstr_org_id NUMBER,
1234 c_item_inv_org_id NUMBER,
1235 c_instance_id NUMBER) IS
1236 SELECT CSI.serial_number,
1237 CSI.lot_number,
1238 CSI.quantity,
1239 CSI.unit_of_measure,
1240 MTL.concatenated_segments
1241 FROM CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL
1242 WHERE CSI.inv_master_organization_id = c_item_mstr_org_id
1243 AND CSI.inv_organization_id = c_item_inv_org_id
1244 AND CSI.inventory_item_id = c_inv_item_id
1245 AND CSI.instance_id = c_instance_id
1246 AND MTL.inventory_item_id = CSI.inventory_item_id
1247 AND MTL.organization_id = CSI.inv_master_organization_id
1248 AND NOT EXISTS (
1249 SELECT 'X'
1250 FROM CSI_II_RELATIONSHIPS
1251 WHERE relationship_type_code = 'COMPONENT-OF'
1252 AND (
1253 object_id = CSI.instance_id
1254 OR
1255 subject_id = CSI.instance_id
1256 )
1257 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
1258 )
1259 AND TRUNC(NVL(CSI.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
1260 AND TRUNC(NVL(CSI.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
1261
1262 -- cursor to check the sub-inventory locator
1263 CURSOR chk_locator_csr (c_org_id NUMBER,
1264 c_subinv_name VARCHAR2,
1265 c_locator_id NUMBER) IS
1266 SELECT 'X'
1267 FROM MTL_ITEM_LOCATIONS
1268 WHERE organization_id = c_org_id
1269 AND inventory_location_id = c_locator_id
1270 AND subinventory_code = c_subinv_name;
1271
1272 -- cursor to check the repair batch
1273 CURSOR chk_rpr_batch_csr (c_rpr_batch_name VARCHAR2,
1274 c_inv_item_id NUMBER) IS
1275 SELECT 'X'
1276 FROM AHL_VISIT_TASKS_B TSK, CSI_ITEM_INSTANCES CSI
1277 WHERE TSK.repair_batch_name = c_rpr_batch_name
1278 AND TSK.instance_id = CSI.instance_id
1279 AND CSI.inventory_item_id = c_inv_item_id
1280 AND TSK.status_code NOT IN ('CANCELLED', 'CLOSED', 'DELETED');
1281
1282 -- cursor to get the org name
1283 CURSOR get_org_name_csr (c_org_id NUMBER) IS
1284 SELECT name
1285 FROM HR_ALL_ORGANIZATION_UNITS
1286 WHERE organization_id = c_org_id;
1287
1288 -- pdoki modified for Bug 14068468
1289 -- cursor to get visit and task details
1290 CURSOR get_visit_task_det_csr (c_visit_task_id NUMBER) IS
1291 SELECT VST.visit_number,
1292 TSK.visit_task_number,
1293 VST.project_id,
1294 PRJ.name project_number,
1295 PRT.task_id project_task_id,
1296 PRT.task_number project_task_number,
1297 VST.comp_planning_loc_id,
1298 VST.comp_inrepair_loc_id
1299 FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
1300 PA_PROJECTS_ALL PRJ, PA_TASKS PRT
1301 WHERE TSK.visit_task_id = c_visit_task_id
1302 AND VST.visit_id = TSK.visit_id
1303 AND PRJ.project_id = VST.project_id
1304 AND PRT.project_id = PRJ.project_id
1305 AND PRT.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
1306
1307 -- cursor to get the concatenated segments for visit's physical locator
1308 CURSOR get_locator_segments_csr (c_inv_locator_id NUMBER) IS
1309 SELECT MTK.concatenated_segments,
1310 MTI.subinventory_code
1311 FROM MTL_ITEM_LOCATIONS MTI, MTL_ITEM_LOCATIONS_KFV MTK
1312 WHERE MTK.inventory_location_id = MTI.inventory_location_id
1313 AND MTI.inventory_location_id = c_inv_locator_id;
1314
1315 -- pdoki modified for Bug 14068468
1316 -- cursor to get the planning and in-repair locator ids for a repair batch
1317 CURSOR get_locator_id_csr (c_rpr_batch_name VARCHAR2) IS
1318 SELECT MTLP.inventory_location_id planning_loc_id,
1319 MTLP.subinventory_code plannning_subinv,
1320 (SELECT inventory_location_id
1321 FROM MTL_ITEM_LOCATIONS
1322 WHERE physical_location_id = VST.comp_inrepair_loc_id
1323 AND project_id = VST.project_id
1324 AND task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)) inrepair_loc_id,
1325 (SELECT subinventory_code
1326 FROM MTL_ITEM_LOCATIONS
1327 WHERE physical_location_id = VST.comp_inrepair_loc_id
1328 AND project_id = VST.project_id
1329 AND task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)) inrepair_subinv
1330 FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
1331 MTL_ITEM_LOCATIONS MTLP
1332 WHERE TSK.repair_batch_name = c_rpr_batch_name
1333 AND VST.visit_id = TSK.visit_id
1334 AND MTLP.physical_location_id = VST.comp_planning_loc_id
1335 AND MTLP.project_id = VST.project_id
1336 AND MTLP.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
1337
1338 -- cursor to get the instance id from a locator
1339 -- NOTE: will be used for non-serialized non-lot-controlled item
1340 CURSOR get_locator_inst_id_csr (c_inv_locator_id NUMBER,
1341 c_inv_item_id NUMBER) IS
1342 SELECT instance_id
1343 FROM CSI_ITEM_INSTANCES
1344 WHERE inv_locator_id = c_inv_locator_id
1345 AND inventory_item_id = c_inv_item_id
1346 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
1347 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
1348
1349 -- cursor to get the instance qty from a locator
1350 -- NOTE: will be used for non-serialized non-lot-controlled item
1351 CURSOR get_locator_inst_qty_csr (c_inv_locator_id NUMBER,
1352 c_inv_item_id NUMBER) IS
1353 SELECT quantity
1354 FROM CSI_ITEM_INSTANCES
1355 WHERE inv_locator_id = c_inv_locator_id
1356 AND inventory_item_id = c_inv_item_id
1357 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
1358 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
1359
1360 -- pdoki modified for Bug 13722071
1361 -- cursor to get all the applicable tasks for update in a given repair batch's scope
1362 CURSOR get_applcbl_tasks_csr (c_rpr_batch_name VARCHAR2) IS
1363 SELECT visit_task_id
1364 FROM AHL_VISIT_TASKS_B
1365 WHERE repair_batch_name = c_rpr_batch_name
1366 UNION ALL
1367 SELECT vts.visit_task_id
1368 FROM AHL_VISIT_TASKS_B vts,
1369 AHL_WORKORDERS wo
1370 WHERE vts.status_code NOT IN ('CANCELLED', 'CLOSED', 'DELETED')
1371 AND wo.visit_task_id = vts.visit_task_id
1372 AND wo.status_code NOT IN ('7','12','22' ) -- Cancelled,Closed,Deleted
1373 START WITH vts.cost_parent_id = ( SELECT visit_task_id
1374 FROM AHL_VISIT_TASKS_B
1375 WHERE repair_batch_name = c_rpr_batch_name)
1376 CONNECT BY vts.cost_parent_id = PRIOR vts.visit_task_id;
1377
1378 -- cursor to check if the repair batch belongs to a visit that is past dated.
1379 CURSOR Check_Past_Dated_Visit(c_rpr_batch_name VARCHAR2) IS
1380 SELECT 'x'
1381 FROM AHL_VISIT_TASKS_B tsk,
1382 AHL_VISITS_B vst
1383 WHERE vst.visit_id = tsk.visit_id
1384 AND tsk.repair_batch_name = c_rpr_batch_name
1385 AND NVL(vst.close_date_time, SYSDATE+1) < SYSDATE;
1386
1387
1388 --
1389 l_api_version CONSTANT NUMBER := 1.0;
1390 l_api_name CONSTANT VARCHAR2(30) := 'Process_Repair_Item';
1391 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1392
1393 l_msg_count NUMBER;
1394 l_visit_id NUMBER;
1395 l_org_name VARCHAR2(240);
1396 l_src_locator_id NUMBER;
1397 l_dst_subinv_name VARCHAR2(10);
1398 l_dst_locator_id NUMBER;
1399 l_dst_locator_segments VARCHAR2(240);
1400 l_task_rec AHL_VWP_RULES_PVT.Task_Rec_Type;
1401 l_locator_inst_id NUMBER;
1402 l_locator_inst_qty NUMBER;
1403 l_dummy VARCHAR2(1);
1404
1405 l_item_inst_det_csr get_item_inst_det_csr%ROWTYPE;
1406 l_visit_task_det_csr get_visit_task_det_csr%ROWTYPE;
1407 l_locator_id_csr get_locator_id_csr%ROWTYPE;
1408
1409 TYPE t_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1410 l_task_id_tbl t_id_tbl;
1411
1412 -- variables for locator flexfield handling
1413 l_fftype FND_FLEX_KEY_API.flexfield_type;
1414 l_ffstrctr FND_FLEX_KEY_API.structure_type;
1415 l_sprtr VARCHAR2(1);
1416
1417 -- variable for material transfer
1418 l_ahl_mtlxfr_tbl Ahl_Mtlxfr_Tbl_Type;
1419 --
1420
1421 BEGIN
1422 IF (l_log_procedure >= l_log_current_level) THEN
1423 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
1424 END IF;
1425
1426 -- Standard start of API savepoint
1427 SAVEPOINT Process_Repair_Item_Pvt;
1428
1429 -- Standard call to check for call compatibility
1430 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1431 l_api_name, G_PKG_NAME) THEN
1432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1433 END IF;
1434
1435 -- Initialize message list if p_init_msg_list is set to TRUE
1436 IF FND_API.To_Boolean( p_init_msg_list) THEN
1437 FND_MSG_PUB.Initialize;
1438 END IF;
1439
1440 -- Initialize Procedure return status to success
1441 x_return_status := FND_API.G_RET_STS_SUCCESS;
1442
1443 IF (l_log_statement >= l_log_current_level) THEN
1444 FND_LOG.string(l_log_statement, l_full_name,
1445 ' p_inv_item_id = '||p_inv_item_id||
1446 ', p_item_mstr_org_id = '||p_item_mstr_org_id||
1447 ', p_item_inv_org_id = '||p_item_inv_org_id||
1448 ', p_instance_id = '||p_instance_id||
1449 ', p_repair_qty = '||p_repair_qty||
1450 ', p_opr_flag = '||p_opr_flag||
1451 ', p_src_subinv_name = '||p_src_subinv_name||
1452 ', p_src_locator_id = '||p_src_locator_id||
1453 ', p_src_rpr_batch_name = '||p_src_rpr_batch_name||
1454 ', p_x_dst_rpr_batch_name = '||p_x_dst_rpr_batch_name);
1455 END IF;
1456
1457 -- ************************** Ist Validation 1::Start ************************** --
1458 -- If any of the user non-enterbale input parameter is invalid, throw the following error:
1459 -- AHL_UC_API_PARAMETER_INVALID The provided parameter NAME : VALUE is invalid.
1460
1461 -- Get the error message count till this point
1462 l_msg_count := FND_MSG_PUB.count_msg;
1463
1464 -- 1) Validate item and instance, and get the required details
1465 OPEN get_item_inst_det_csr (p_inv_item_id, p_item_mstr_org_id, p_item_inv_org_id, p_instance_id);
1466 FETCH get_item_inst_det_csr INTO l_item_inst_det_csr;
1467 IF (get_item_inst_det_csr%NOTFOUND) THEN
1468 -- item and instance are invalid
1469 IF (l_log_statement >= l_log_current_level) THEN
1470 FND_LOG.string(l_log_statement, l_full_name, 'item and instance are invalid');
1471 END IF;
1472 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_API_PARAMETER_INVALID');
1473 FND_MESSAGE.Set_Token('NAME', 'p_inv_item_id');
1474 FND_MESSAGE.Set_Token('VALUE', p_inv_item_id);
1475 FND_MSG_PUB.ADD;
1476 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_API_PARAMETER_INVALID');
1477 FND_MESSAGE.Set_Token('NAME', 'p_item_mstr_org_id');
1478 FND_MESSAGE.Set_Token('VALUE', p_item_mstr_org_id);
1479 FND_MSG_PUB.ADD;
1480 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_API_PARAMETER_INVALID');
1481 FND_MESSAGE.Set_Token('NAME', 'p_item_inv_org_id');
1482 FND_MESSAGE.Set_Token('VALUE', p_item_inv_org_id);
1483 FND_MSG_PUB.ADD;
1484 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_API_PARAMETER_INVALID');
1485 FND_MESSAGE.Set_Token('NAME', 'p_instance_id');
1486 FND_MESSAGE.Set_Token('VALUE', p_instance_id);
1487 FND_MSG_PUB.ADD;
1488 END IF;
1489 CLOSE get_item_inst_det_csr;
1490
1491 -- 2) Validate the operation flag
1492 IF (NVL(p_opr_flag, '-') <> G_OP_CREATE AND NVL(p_opr_flag, '-') <> G_OP_UPDATE) THEN
1493 -- operation flag is invalid
1494 IF (l_log_statement >= l_log_current_level) THEN
1495 FND_LOG.string(l_log_statement, l_full_name, 'operation flag is invalid');
1496 END IF;
1497 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_API_PARAMETER_INVALID');
1498 FND_MESSAGE.Set_Token('NAME', 'p_opr_flag');
1499 FND_MESSAGE.Set_Token('VALUE', p_opr_flag);
1500 FND_MSG_PUB.ADD;
1501 END IF;
1502
1503 -- 3) Validate the source sub-inventory locator
1504 OPEN chk_locator_csr (p_item_inv_org_id, p_src_subinv_name, p_src_locator_id);
1505 FETCH chk_locator_csr INTO l_dummy;
1506 IF (chk_locator_csr%NOTFOUND) THEN
1507 -- source sub-inventory locator is invalid
1508 IF (l_log_statement >= l_log_current_level) THEN
1509 FND_LOG.string(l_log_statement, l_full_name, 'source sub-inventory locator is invalid');
1510 END IF;
1511 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_API_PARAMETER_INVALID');
1512 FND_MESSAGE.Set_Token('NAME', 'p_src_subinv_name');
1513 FND_MESSAGE.Set_Token('VALUE', p_src_subinv_name);
1514 FND_MSG_PUB.ADD;
1515 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_API_PARAMETER_INVALID');
1516 FND_MESSAGE.Set_Token('NAME', 'p_src_locator_id');
1517 FND_MESSAGE.Set_Token('VALUE', p_src_locator_id);
1518 FND_MSG_PUB.ADD;
1519 END IF;
1520 CLOSE chk_locator_csr;
1521
1522 -- 4) Validate the source repair batch name
1523 IF (p_src_rpr_batch_name IS NOT NULL) THEN
1524 OPEN chk_rpr_batch_csr (p_src_rpr_batch_name, p_inv_item_id);
1525 FETCH chk_rpr_batch_csr INTO l_dummy;
1526 IF (chk_rpr_batch_csr%NOTFOUND) THEN
1527 -- source repair batch name is invalid
1528 IF (l_log_statement >= l_log_current_level) THEN
1529 FND_LOG.string(l_log_statement, l_full_name, 'source repair batch name is invalid');
1530 END IF;
1531 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_UC_API_PARAMETER_INVALID');
1532 FND_MESSAGE.Set_Token('NAME', 'p_src_rpr_batch_name');
1533 FND_MESSAGE.Set_Token('VALUE', p_src_rpr_batch_name);
1534 FND_MSG_PUB.ADD;
1535 END IF;
1536 CLOSE chk_rpr_batch_csr;
1537 END IF;
1538
1539 -- If any errors occured, then raise them
1540 IF (l_msg_count < FND_MSG_PUB.count_msg) THEN
1541 IF (l_log_statement >= l_log_current_level) THEN
1542 FND_LOG.string(l_log_statement, l_full_name, 'input parameters are invalid');
1543 END IF;
1544 RAISE FND_API.G_EXC_ERROR;
1545 END IF;
1546 -- ************************** Ist Validation 1::End ************************** --
1547
1548 -- ************************** IInd Validation 2::Start ************************** --
1549 -- If any of the user enterbale input parameter is invalid, throw respective error
1550
1551 -- 1) Validate the repair quantity
1552 IF (p_repair_qty IS NULL OR p_repair_qty = 0 OR p_repair_qty > l_item_inst_det_csr.quantity) THEN
1553 -- repair quantity is NULL or greater than instance quantity
1554 IF (l_log_statement >= l_log_current_level) THEN
1555 FND_LOG.string(l_log_statement, l_full_name, 'repair quantity is NULL or greater than instance quantity');
1556 END IF;
1557 -- Please enter a positive number for the repair quantity, less than or equal to the instance quantity.
1558 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_CMP_RPR_QTY_INVLD');
1559 FND_MSG_PUB.ADD;
1560 RAISE FND_API.G_EXC_ERROR;
1561 ELSIF (p_repair_qty IS NOT NULL AND p_repair_qty = l_item_inst_det_csr.quantity) THEN
1562 -- if the repair status is 'PLANNING' or 'PRODUCTION', then check the workorders for the instance
1563 -- - if all are complete, then complete quantity is allowed
1564 -- - else, all but 1 quantity is allowed
1565 IF (AHL_CMP_UTIL_PKG.Get_Rpr_Status_For_Inst(p_instance_id, p_item_inv_org_id) IN ('PLANNING', 'PRODUCTION')) THEN
1566 l_dummy := AHL_CMP_UTIL_PKG.Are_All_Workorders_Complete(NULL, p_instance_id, p_item_inv_org_id);
1567 IF (l_dummy = 'N') THEN
1568 -- repair quantity can't be complete
1569 IF (l_log_statement >= l_log_current_level) THEN
1570 FND_LOG.string(l_log_statement, l_full_name, 'repair quantity cant be complete');
1571 END IF;
1572 -- Please complete the open workorders for the instance before moving complete quantity.
1573 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_CMP_COMP_QTY_INVLD');
1574 FND_MSG_PUB.ADD;
1575 RAISE FND_API.G_EXC_ERROR;
1576 END IF;
1577 l_dummy := NULL;
1578 END IF;
1579 END IF;
1580
1581 -- 2) For update flow:
1582 -- a) validate the destination repair batch name
1583 -- b) don't allow update for lot-controlled items, as only one instance is allowed in the locator for them
1584 IF (p_opr_flag = G_OP_UPDATE) THEN
1585 OPEN chk_rpr_batch_csr (p_x_dst_rpr_batch_name, p_inv_item_id);
1586 FETCH chk_rpr_batch_csr INTO l_dummy;
1587 IF (chk_rpr_batch_csr%NOTFOUND) THEN
1588 -- destination repair batch name is invalid
1589 CLOSE chk_rpr_batch_csr;
1590 IF (l_log_statement >= l_log_current_level) THEN
1591 FND_LOG.string(l_log_statement, l_full_name, 'destination repair batch name is invalid');
1592 END IF;
1593 -- The repair batch REPAIR_BATCH is invalid for the item ITEM.
1594 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_CMP_RPR_BATCH_INVLD');
1595 FND_MESSAGE.Set_Token('REPAIR_BATCH', p_x_dst_rpr_batch_name);
1596 FND_MESSAGE.Set_Token('ITEM', l_item_inst_det_csr.concatenated_segments);
1597 FND_MSG_PUB.ADD;
1598 RAISE FND_API.G_EXC_ERROR;
1599 END IF;
1600 CLOSE chk_rpr_batch_csr;
1601
1602 IF (l_item_inst_det_csr.lot_number IS NOT NULL) THEN
1603 -- repair batch update not allowed for lot controlled items
1604 IF (l_log_statement >= l_log_current_level) THEN
1605 FND_LOG.string(l_log_statement, l_full_name, 'repair batch update not allowed for lot controlled items');
1606 END IF;
1607 -- The repair batch REPAIR_BATCH can contain only one lot-controlled item instance.
1608 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_CMP_LOT_UPDATE_INVLD');
1609 FND_MESSAGE.Set_Token('REPAIR_BATCH', p_x_dst_rpr_batch_name);
1610 FND_MSG_PUB.ADD;
1611 RAISE FND_API.G_EXC_ERROR;
1612 END IF;
1613
1614 OPEN Check_Past_Dated_Visit(p_x_dst_rpr_batch_name);
1615 FETCH Check_Past_Dated_Visit INTO l_dummy;
1616 IF (Check_Past_Dated_Visit%FOUND) THEN
1617 -- destination repair batch belongs to a past dated visit.
1618 CLOSE Check_Past_Dated_Visit;
1619 IF (l_log_statement >= l_log_current_level) THEN
1620 FND_LOG.string(l_log_statement, l_full_name, 'destination repair batch belongs to a past dated visit.');
1621 END IF;
1622 -- Cannot add instances to the repair batch REPAIR_BATCH as it belongs to a past dated visit.
1623 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_CMP_CANNOT_ADD_INSTANCES');
1624 FND_MESSAGE.Set_Token('REPAIR_BATCH', p_x_dst_rpr_batch_name);
1625 FND_MSG_PUB.ADD;
1626 RAISE FND_API.G_EXC_ERROR;
1627 END IF;
1628 CLOSE Check_Past_Dated_Visit;
1629 END IF;
1630 -- ************************** IInd Validation 2::End ************************** --
1631
1632 -- ************************** IIIrd Destination Repair Batch Creation::Start ************************** --
1633 -- If p_opr_flag is G_OP_CREATE ('C'), then create the destination repair batch / summary task,
1634 -- and formulate the concatenated segments for the planning locator to be created during item transfer.
1635 -- Else, get the locator id for the existing destination repair batch.
1636
1637 IF (p_opr_flag = G_OP_CREATE) THEN
1638 -- 1) Get the Component Visit for the org
1639 l_visit_id := AHL_CMP_UTIL_PKG.Get_Comp_Visit_For_Org (p_item_inv_org_id);
1640 IF (l_visit_id IS NULL) THEN
1641 -- no component visit exists for the org
1642 IF (l_log_statement >= l_log_current_level) THEN
1643 FND_LOG.string(l_log_statement, l_full_name, 'no component visit exists for the org');
1644 END IF;
1645 OPEN get_org_name_csr (p_item_inv_org_id);
1646 FETCH get_org_name_csr INTO l_org_name;
1647 CLOSE get_org_name_csr;
1648 -- No valid Component Visit exists for the organization 'ORG_NAME'.
1649 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_CMP_COMP_VISIT_NULL');
1650 FND_MESSAGE.Set_Token('ORG_NAME', l_org_name);
1651 FND_MSG_PUB.ADD;
1652 RAISE FND_API.G_EXC_ERROR;
1653 END IF;
1654
1655 -- 2) Create the summary task
1656 l_task_rec.visit_id := l_visit_id;
1657 l_task_rec.inventory_item_id := p_inv_item_id;
1658 l_Task_rec.item_organization_id := p_item_mstr_org_id;
1659 l_task_rec.instance_id := p_instance_id;
1660 l_task_rec.task_type_code := 'SUMMARY';
1661 l_task_rec.visit_task_name := 'Dummy Task Name';
1662 -- pdoki added for Bug 14068468
1663 l_task_rec.repair_batch_name := 'Dummy Repair Batch';
1664
1665 -- call the VWP API, and let the defaults for input parameters kick in
1666 IF (l_log_statement >= l_log_current_level) THEN
1667 FND_LOG.string(l_log_statement, l_full_name, 'calling AHL_VWP_TASKS_PVT.Create_Task'||
1668 ' l_visit_id = '||l_visit_id);
1669 END IF;
1670 AHL_VWP_TASKS_PVT.Create_Task (
1671 p_api_version => 1.0,
1672 p_module_type => 'API',
1673 p_x_task_rec => l_task_rec,
1674 x_return_status => x_return_status,
1675 x_msg_count => x_msg_count,
1676 x_msg_data => x_msg_data
1677 );
1678 IF (l_log_statement >= l_log_current_level) THEN
1679 FND_LOG.string(l_log_statement, l_full_name, 'called AHL_VWP_TASKS_PVT.Create_Task'||
1680 ' l_task_rec.visit_task_id = '||l_task_rec.visit_task_id);
1681 END IF;
1682
1683 -- if the call resulted in error, then raise it
1684 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1685 IF (l_log_statement >= l_log_current_level) THEN
1686 FND_LOG.string(l_log_statement, l_full_name, 'call to AHL_VWP_TASKS_PVT.Create_Task errored out');
1687 END IF;
1688 RAISE FND_API.G_EXC_ERROR;
1689 END IF;
1690
1691 -- get the visit and task details
1692 OPEN get_visit_task_det_csr (l_task_rec.visit_task_id);
1693 FETCH get_visit_task_det_csr INTO l_visit_task_det_csr;
1694 CLOSE get_visit_task_det_csr;
1695
1696 p_x_dst_rpr_batch_name := l_visit_task_det_csr.visit_number||'.'||
1697 l_item_inst_det_csr.concatenated_segments||'.'||
1698 l_visit_task_det_csr.visit_task_number;
1699
1700 -- 3) Update the task quantity, repair batch name, target_qty, ret_serviceable_qty and ret_unserviceable_qty
1701 -- and visit task name in the TL table (it will be replaced in the UI later, if needed)
1702 UPDATE AHL_VISIT_TASKS_B SET
1703 target_qty = p_repair_qty,
1704 quantity = p_repair_qty,
1705 repair_batch_name = p_x_dst_rpr_batch_name,
1706 ret_serviceable_qty = NULL,
1707 ret_unserviceable_qty = NULL
1708 WHERE visit_task_id = l_task_rec.visit_task_id;
1709
1710 UPDATE AHL_VISIT_TASKS_TL SET
1711 visit_task_name = p_x_dst_rpr_batch_name
1712 WHERE visit_task_id = l_task_rec.visit_task_id;
1713
1714 -- 4) Create the concatenated segments for the task locator
1715 -- get the concatenated segments for visit's planning locator
1716 OPEN get_locator_segments_csr (l_visit_task_det_csr.comp_planning_loc_id);
1717 FETCH get_locator_segments_csr INTO l_dst_locator_segments, l_dst_subinv_name;
1718 CLOSE get_locator_segments_csr;
1719 IF (l_log_statement >= l_log_current_level) THEN
1720 FND_LOG.string(l_log_statement, l_full_name,' planning l_dst_locator_segments = '||l_dst_locator_segments);
1721 END IF;
1722
1723 -- get the separator defined for the locator flexfield
1724 FND_PROFILE.put('MFG_ORGANIZATION_ID', p_item_inv_org_id); -- set mfg_organization_id profile - bug# 6010795
1725 FND_FLEX_KEY_API.set_session_mode('seed_data');
1726 l_fftype := FND_FLEX_KEY_API.find_flexfield('INV', 'MTLL'); -- get flexfield type
1727 l_ffstrctr := FND_FLEX_KEY_API.find_structure(l_fftype, 101); -- get flexfield structure type
1728 l_sprtr := l_ffstrctr.segment_separator; -- get flexfield segment separator
1729 IF (l_log_statement >= l_log_current_level) THEN
1730 FND_LOG.string(l_log_statement, l_full_name,' l_sprtr = '||l_sprtr);
1731 END IF;
1732
1733 -- form the logical locator segments for the newly created task
1734 l_dst_locator_segments := SUBSTR(l_dst_locator_segments, 1, (LENGTH(l_dst_locator_segments)-1));
1735 IF (l_log_statement >= l_log_current_level) THEN
1736 FND_LOG.string(l_log_statement, l_full_name,' substring l_dst_locator_segments = '||l_dst_locator_segments);
1737 END IF;
1738 l_dst_locator_segments := l_dst_locator_segments||
1739 l_visit_task_det_csr.project_number||l_sprtr||
1740 l_visit_task_det_csr.project_task_number;
1741 IF (l_log_statement >= l_log_current_level) THEN
1742 FND_LOG.string(l_log_statement, l_full_name,' logical l_dst_locator_segments = '||l_dst_locator_segments);
1743 END IF;
1744 ELSE
1745 -- 1) Get the locator id for the destination repair batch
1746 -- - if it is non-serialized item and in-repair locator exists for the repair batch, then the destination locator
1747 -- is the in-repair locator
1748 -- - else, it is the planning locator
1749 OPEN get_locator_id_csr (p_x_dst_rpr_batch_name);
1750 FETCH get_locator_id_csr INTO l_locator_id_csr;
1751 CLOSE get_locator_id_csr;
1752
1753 IF (l_item_inst_det_csr.serial_number IS NULL AND l_locator_id_csr.inrepair_loc_id IS NOT NULL) THEN
1754 l_dst_locator_id := l_locator_id_csr.inrepair_loc_id;
1755 l_dst_subinv_name := l_locator_id_csr.inrepair_subinv;
1756 ELSE
1757 l_dst_locator_id := l_locator_id_csr.planning_loc_id;
1758 l_dst_subinv_name := l_locator_id_csr.plannning_subinv;
1759 END IF;
1760 END IF;
1761 -- ************************** IIIrd Destination Repair Batch Creation::End ************************** --
1762
1763 -- ************************** IVth Material Transfer::Start ************************** --
1764 -- Populate the required parameters in the material transfer API record and make the call
1765
1766 -- 1) Get the source locator id
1767 l_src_locator_id := p_src_locator_id;
1768
1769 -- 2) Populate the material transfer record and call the API
1770 l_ahl_mtlxfr_tbl(0).inventory_item_id := p_inv_item_id;
1771 l_ahl_mtlxfr_tbl(0).organization_id := p_item_inv_org_id;
1772 l_ahl_mtlxfr_tbl(0).src_subinv_name := p_src_subinv_name;
1773 l_ahl_mtlxfr_tbl(0).src_locator_id := l_src_locator_id;
1774 l_ahl_mtlxfr_tbl(0).quantity := p_repair_qty;
1775 l_ahl_mtlxfr_tbl(0).uom := l_item_inst_det_csr.unit_of_measure;
1776 l_ahl_mtlxfr_tbl(0).serial_number := l_item_inst_det_csr.serial_number;
1777 l_ahl_mtlxfr_tbl(0).lot_number := l_item_inst_det_csr.lot_number;
1778 l_ahl_mtlxfr_tbl(0).transaction_type_id := 67; -- for project tasks transfer
1779 l_ahl_mtlxfr_tbl(0).transaction_reference := 'AHL - '||p_x_dst_rpr_batch_name;
1780 l_ahl_mtlxfr_tbl(0).dst_subinv_name := l_dst_subinv_name;
1781
1782 IF (p_opr_flag = G_OP_CREATE) THEN
1783 l_ahl_mtlxfr_tbl(0).dst_locator_segments := l_dst_locator_segments;
1784 ELSE
1785 l_ahl_mtlxfr_tbl(0).dst_locator_id := l_dst_locator_id;
1786 END IF;
1787
1788 -- call the material transfer API, and let the defaults for input parameters kick in
1789 IF (l_log_statement >= l_log_current_level) THEN
1790 FND_LOG.string(l_log_statement, l_full_name, 'calling Perform_Mtl_Xfr');
1791 END IF;
1792 Perform_Mtl_Xfr (
1793 p_x_ahl_mtlxfr_tbl => l_ahl_mtlxfr_tbl,
1794 x_return_status => x_return_status,
1795 x_msg_count => x_msg_count,
1796 x_msg_data => x_msg_data
1797 );
1798 IF (l_log_statement >= l_log_current_level) THEN
1799 FND_LOG.string(l_log_statement, l_full_name, 'called Perform_Mtl_Xfr');
1800 END IF;
1801
1802 IF (p_opr_flag = G_OP_CREATE) THEN
1803 -- get the locator id for the created repair batch
1804 OPEN get_locator_id_csr (p_x_dst_rpr_batch_name);
1805 FETCH get_locator_id_csr INTO l_locator_id_csr;
1806 CLOSE get_locator_id_csr;
1807 l_dst_locator_id := l_locator_id_csr.planning_loc_id;
1808 l_dst_subinv_name := l_locator_id_csr.plannning_subinv;
1809 IF (l_log_statement >= l_log_current_level) THEN
1810 FND_LOG.string(l_log_statement, l_full_name, 'locator created = '||l_dst_locator_id);
1811 END IF;
1812 END IF;
1813
1814 -- if the call resulted in error, then raise it
1815 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1816 IF (l_log_statement >= l_log_current_level) THEN
1817 FND_LOG.string(l_log_statement, l_full_name, 'call to Perform_Mtl_Xfr errored out');
1818 END IF;
1819 RAISE FND_API.G_EXC_ERROR;
1820 END IF;
1821 -- ************************** IVth Material Transfer::End ************************** --
1822
1823 -- ************************** Vth Repair Batch And Scope Update::Start ************************** --
1824 -- - In create flow for non-serialized non-lot-controlled items, update created batch's (task's) instance
1825 -- - In update flow for non-serialized non-lot-controlled items, update updated batch's workscope's task qty
1826 -- - In both the flows, if the source was a repair batch, then :
1827 -- - for non-serialized non-lot-controlled items, update its workscope's task qty
1828 -- NOTE: Updated repair batch will have any work scope only if the updated locator was its in-repair locator.
1829
1830 IF (p_opr_flag = G_OP_CREATE) THEN
1831 IF (l_item_inst_det_csr.serial_number IS NULL AND l_item_inst_det_csr.lot_number IS NULL) THEN
1832 -- 1) Get the instance id from the created locator
1833 OPEN get_locator_inst_id_csr (l_dst_locator_id, p_inv_item_id);
1834 FETCH get_locator_inst_id_csr INTO l_locator_inst_id;
1835 CLOSE get_locator_inst_id_csr;
1836
1837 -- 2) Update repair batch's (task's) instance
1838 UPDATE AHL_VISIT_TASKS_B SET
1839 instance_id = l_locator_inst_id
1840 WHERE repair_batch_name = p_x_dst_rpr_batch_name;
1841 END IF;
1842 ELSE
1843 -- For non-serialized non-lot-controlled items, update its scope's task qty
1844 IF (l_item_inst_det_csr.serial_number IS NULL AND l_item_inst_det_csr.lot_number IS NULL) THEN
1845 -- get the instance qty from the destination locator
1846 OPEN get_locator_inst_qty_csr (l_dst_locator_id, p_inv_item_id);
1847 FETCH get_locator_inst_qty_csr INTO l_locator_inst_qty;
1848 CLOSE get_locator_inst_qty_csr;
1849
1850 -- bulk collect all the applicable tasks
1851 OPEN get_applcbl_tasks_csr (p_x_dst_rpr_batch_name);
1852 FETCH get_applcbl_tasks_csr BULK COLLECT INTO l_task_id_tbl;
1853 CLOSE get_applcbl_tasks_csr;
1854 IF (l_log_statement >= l_log_current_level) THEN
1855 FND_LOG.string(l_log_statement, l_full_name,' l_task_id_tbl.COUNT = '||l_task_id_tbl.COUNT);
1856 END IF;
1857
1858 -- update the tasks
1859 IF (l_task_id_tbl.COUNT > 0) THEN
1860 FOR i IN l_task_id_tbl.FIRST..l_task_id_tbl.LAST LOOP
1861 UPDATE AHL_VISIT_TASKS_B SET
1862 quantity = l_locator_inst_qty
1863 WHERE visit_task_id = l_task_id_tbl(i);
1864 END LOOP;
1865 END IF;
1866 END IF;
1867 END IF; -- p_opr_flag = G_OP_CREATE
1868
1869 IF (p_src_rpr_batch_name IS NOT NULL) THEN
1870 -- For non-serialized non-lot-controlled items, update its scope's task qty
1871 IF (l_item_inst_det_csr.serial_number IS NULL AND l_item_inst_det_csr.lot_number IS NULL) THEN
1872 -- get the instance qty from the source locator
1873 OPEN get_locator_inst_qty_csr (l_src_locator_id, p_inv_item_id);
1874 FETCH get_locator_inst_qty_csr INTO l_locator_inst_qty;
1875 CLOSE get_locator_inst_qty_csr;
1876
1877 -- bulk collect all the applicable tasks
1878 OPEN get_applcbl_tasks_csr (p_src_rpr_batch_name);
1879 FETCH get_applcbl_tasks_csr BULK COLLECT INTO l_task_id_tbl;
1880 CLOSE get_applcbl_tasks_csr;
1881 IF (l_log_statement >= l_log_current_level) THEN
1882 FND_LOG.string(l_log_statement, l_full_name,' l_task_id_tbl.COUNT = '||l_task_id_tbl.COUNT);
1883 END IF;
1884
1885 -- update the tasks
1886 IF (l_task_id_tbl.COUNT > 0) THEN
1887 FOR i IN l_task_id_tbl.FIRST..l_task_id_tbl.LAST LOOP
1888 UPDATE AHL_VISIT_TASKS_B SET
1889 quantity = l_locator_inst_qty
1890 WHERE visit_task_id = l_task_id_tbl(i);
1891 END LOOP;
1892 END IF;
1893 END IF;
1894 END IF;
1895 -- ************************** Vth Repair Batch And Scope Update::End ************************** --
1896
1897 IF (l_log_statement >= l_log_current_level) THEN
1898 FND_LOG.string(l_log_statement, l_full_name,
1899 ' p_x_dst_rpr_batch_name = '||p_x_dst_rpr_batch_name);
1900 END IF;
1901
1902 -- Standard check of p_commit
1903 IF FND_API.TO_BOOLEAN(p_commit) THEN
1904 COMMIT WORK;
1905 END IF;
1906
1907 IF (l_log_procedure >= l_log_current_level) THEN
1908 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
1909 END IF;
1910
1911 EXCEPTION
1912 WHEN FND_API.G_EXC_ERROR THEN
1913 ROLLBACK TO Process_Repair_Item_Pvt;
1914 x_return_status := FND_API.G_RET_STS_ERROR;
1915 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1916 p_data => x_msg_data,
1917 p_encoded => FND_API.G_FALSE);
1918 IF (l_log_exception >= l_log_current_level) THEN
1919 FND_LOG.string(l_log_exception, l_full_name, 'Execution Error: ' || x_msg_data);
1920 END IF;
1921
1922 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1923 ROLLBACK TO Process_Repair_Item_Pvt;
1924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1925 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1926 p_data => x_msg_data,
1927 p_encoded => FND_API.G_FALSE);
1928 IF (l_log_unexpected >= l_log_current_level) THEN
1929 FND_LOG.string(l_log_unexpected, l_full_name, 'Unexpected Exception: ' || x_msg_data);
1930 END IF;
1931
1932 WHEN OTHERS THEN
1933 ROLLBACK TO Process_Repair_Item_Pvt;
1934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1935 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1936 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
1937 p_procedure_name => l_api_name,
1938 p_error_text => SUBSTR(SQLERRM,1,500));
1939 END IF;
1940 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1941 p_data => x_msg_data,
1942 p_encoded => fnd_api.G_FALSE);
1943 IF (l_log_unexpected >= l_log_current_level) THEN
1944 FND_LOG.string(l_log_unexpected, l_full_name, 'Other Exception: ' || x_msg_data);
1945 END IF;
1946
1947 END Process_Repair_Item;
1948 ------------------------------------------------------------------------------------
1949
1950 ------------------------------------------------------------------------------------------------------
1951 -- Start of Comments
1952 -- Procedure name : Close_Repair_Batch
1953 -- Type : Public
1954 -- Function : This procedure will Close a given Repair Batch
1955 -- Pre-reqs :
1956 -- Parameters :
1957 --
1958 -- Close_Repair_Batch
1959 -- p_repair_batch_id IN Repair Batch Id Required
1960 -- p_x_cost_session_id IN OUT Cost Session Param Required
1961 -- p_x_mr_session_id IN OUT Mr Session Param Required
1962 --
1963 -- End of Comments
1964 -------------------------------------------------------------------------------------------------------
1965 PROCEDURE Close_Repair_Batch
1966 (
1967 P_API_VERSION IN NUMBER := 1.0,
1968 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1969 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
1970 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1971 P_MODULE_TYPE IN VARCHAR2 := NULL,
1972 P_REPAIR_BATCH_ID IN NUMBER,
1973 P_X_COST_SESSION_ID IN OUT NOCOPY NUMBER,
1974 P_X_MR_SESSION_ID IN OUT NOCOPY NUMBER,
1975 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1976 X_MSG_COUNT OUT NOCOPY NUMBER,
1977 X_MSG_DATA OUT NOCOPY VARCHAR2
1978 )
1979 IS
1980 l_api_name CONSTANT VARCHAR2(30) := 'Close_Repair_Batch';
1981 L_API_VERSION CONSTANT NUMBER := 1.0;
1982 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1983 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1984
1985 l_close_wo_flag boolean := TRUE;
1986 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.Cost_price_rec_type;
1987
1988 l_actual_cost NUMBER;
1989 l_estimated_cost NUMBER;
1990 l_return_status VARCHAR2(1);
1991 l_msg_count NUMBER;
1992
1993
1994 -- Cursor to get all the workorders associated to the given repair batch.
1995 CURSOR Get_Workorders(c_repair_batch_id NUMBER)
1996 IS
1997 SELECT wo.visit_task_id,wo.workorder_id,wo.workorder_name,wo.status_code
1998 FROM ahl_workorders wo
1999 WHERE wo.visit_task_id in
2000 ( SELECT vt.visit_task_id
2001 FROM AHL_VISIT_TASKS_B vt
2002 START WITH vt.visit_task_id = c_repair_batch_id
2003 CONNECT BY PRIOR VT.VISIT_TASK_ID = VT.COST_PARENT_ID );
2004
2005 CURSOR get_wip_entity(c_repair_batch_id NUMBER)
2006 IS
2007 SELECT wo.visit_task_id,wo.workorder_id,wo.wip_entity_id
2008 FROM ahl_workorders wo
2009 WHERE wo.STATUS_CODE <> '7'
2010 AND wo.visit_task_id IN
2011 ( SELECT vt.visit_task_id
2012 FROM AHL_VISIT_TASKS_B vt
2013 START WITH vt.visit_task_id = c_repair_batch_id
2014 CONNECT BY PRIOR VT.VISIT_TASK_ID = VT.COST_PARENT_ID );
2015
2016 l_wip_entity_rec get_wip_entity%ROWTYPE;
2017 l_get_wo_rec get_workorders%ROWTYPE;
2018
2019 -- To find visit Id
2020 CURSOR get_batch_details (c_repair_batch_id NUMBER)
2021 IS
2022 SELECT *
2023 FROM AHL_VISIT_TASKS_B
2024 WHERE visit_task_id = c_repair_batch_id;
2025
2026 l_batch_dtl_rec get_batch_details%ROWTYPE;
2027 l_batch_qty NUMBER;
2028 l_batch_wo_qty NUMBER;
2029
2030
2031 BEGIN
2032
2033 -- Standard start of API savepoint
2034 SAVEPOINT Close_Repair_Batch_Pvt;
2035
2036 -- Standard call to check for call compatibility
2037 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2038 G_PKG_NAME) THEN
2039 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2040 END IF;
2041 -- Initialize message list if p_init_msg_list is set to TRUE
2042 IF FND_API.To_Boolean(p_init_msg_list) THEN
2043 FND_MSG_PUB.Initialize;
2044 END IF;
2045
2046 -- Initialize API return status to success
2047 x_return_status := FND_API.G_RET_STS_SUCCESS;
2048
2049 IF G_DEBUG='Y' THEN
2050 AHL_DEBUG_PUB.enable_debug;
2051 AHL_DEBUG_PUB.debug('Entered Close Repair Batch API');
2052 END IF;
2053
2054 OPEN get_batch_details (p_repair_batch_id);
2055 FETCH get_batch_details INTO l_batch_dtl_rec;
2056 CLOSE get_batch_details;
2057
2058 IF l_batch_dtl_rec.status_code IS NOT NULL AND l_batch_dtl_rec.status_code IN ('CLOSED','CANCELLED','DELETED') THEN
2059 FND_MESSAGE.SET_NAME('AHL','AHL_CMP_CANNOT_CLOSE');
2060 FND_MESSAGE.Set_Token('BATCH', l_batch_dtl_rec.repair_batch_name);
2061 FND_MSG_PUB.ADD;
2062 RAISE FND_API.G_EXC_ERROR;
2063 END IF;
2064
2065 OPEN Get_Workorders(p_repair_batch_id);
2066 LOOP
2067 FETCH Get_Workorders INTO l_get_wo_rec ;
2068 EXIT WHEN Get_Workorders%NOTFOUND;
2069
2070 IF l_get_wo_rec.status_code NOT IN ('4','12','7','5','17' ) THEN -- Complete,Closed,Cancelled,Complete-No-Charge and Draft
2071 l_close_wo_flag := FALSE;
2072 Exit;
2073 END IF;
2074 END LOOP;
2075 CLOSE GET_WORKORDERS;
2076
2077 IF l_close_wo_flag THEN -- Either all the associated Workorders have come to an end state(Complete,Closed,Cancelled,Complete-No-Charge)/no workorders have been associated.
2078
2079 l_batch_qty := AHL_CMP_UTIL_PKG.Get_Batch_Qty(l_batch_dtl_rec.repair_batch_name);
2080 IF l_batch_qty <> 0 THEN
2081 FND_MESSAGE.SET_NAME('AHL','AHL_CMP_BATCH_QTY_NOT_ZERO');
2082 FND_MESSAGE.Set_Token('BATCH', l_batch_dtl_rec.repair_batch_name);
2083 FND_MSG_PUB.ADD;
2084 RAISE FND_API.G_EXC_ERROR;
2085 END IF;
2086
2087 l_batch_wo_qty := AHL_CMP_UTIL_PKG.Get_Workorder_Qty(l_batch_dtl_rec.repair_batch_name);
2088 IF l_batch_wo_qty <> 0 THEN
2089 FND_MESSAGE.SET_NAME('AHL','AHL_CMP_WO_QTY_NOT_ZERO');
2090 FND_MESSAGE.Set_Token('BATCH', l_batch_dtl_rec.repair_batch_name);
2091 FND_MSG_PUB.ADD;
2092 RAISE FND_API.G_EXC_ERROR;
2093 END IF;
2094
2095 l_cost_price_rec.visit_id := l_batch_dtl_rec.visit_id;
2096 l_cost_price_rec.visit_task_id := p_repair_batch_id;
2097 l_cost_price_rec.cost_session_id := p_x_cost_session_id;
2098 l_cost_price_rec.mr_session_id := p_x_mr_session_id;
2099
2100 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2101 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost visit ID:' || l_cost_price_rec.VISIT_ID);
2102 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost task ID:' || l_cost_price_rec.visit_task_id);
2103 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost Session ID: ' || l_cost_price_rec.COST_SESSION_ID);
2104 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Mr Session ID:' || l_cost_price_rec.MR_SESSION_ID);
2105 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before Calling AHL_VWP_COST_PVT.Calculate_WO_Cost');
2106 END IF;
2107
2108 AHL_VWP_COST_PVT.Calculate_WO_Cost(
2109 p_api_version => p_api_version,
2110 p_init_msg_list => p_init_msg_list,
2111 p_commit => Fnd_Api.g_false,
2112 p_validation_level => p_validation_level,
2113 p_x_cost_price_rec => l_cost_price_rec,
2114 x_return_status => l_return_status);
2115
2116 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2117 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After Calling AHL_VWP_COST_PVT.Calculate_WO_Cost : l_return_status - '||l_return_status);
2118 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost Session ID: ' || l_cost_price_rec.COST_SESSION_ID);
2119 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Mr Session ID:' || l_cost_price_rec.MR_SESSION_ID);
2120 END IF;
2121
2122 -- Check Error Message stack.
2123 l_msg_count := FND_MSG_PUB.count_msg;
2124 IF l_msg_count > 0 THEN
2125 x_return_status := FND_API.G_RET_STS_ERROR;
2126 RAISE FND_API.G_EXC_ERROR;
2127 END IF;
2128
2129 --Assign to out paramenter
2130 p_x_cost_session_id := l_cost_price_rec.cost_session_id;
2131 p_x_mr_session_id := l_cost_price_rec.mr_session_id;
2132
2133 OPEN get_wip_entity(p_repair_batch_id);
2134 LOOP
2135 FETCH get_wip_entity INTO l_wip_entity_rec;
2136 EXIT WHEN get_wip_entity%NOTFOUND;
2137
2138
2139 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2140 fnd_log.string(fnd_log.level_statement,L_DEBUG,'wip_entity_id - '||l_wip_entity_rec.wip_entity_id||' - '||'Visit task Id - '||l_wip_entity_rec.visit_task_id);
2141 END IF;
2142
2143 IF l_wip_entity_rec.wip_entity_id IS NOT NULL THEN
2144
2145 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2146 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before Calling AHL_VWP_COST_PVT.Get_WO_Cost for all workorers');
2147 END IF;
2148
2149 AHL_VWP_COST_PVT.Get_WO_Cost(
2150 p_Session_Id => l_cost_price_rec.mr_session_id,
2151 p_Id => l_wip_entity_rec.wip_entity_id,
2152 p_program_id => fnd_global.PROG_APPL_ID,
2153 x_actual_cost => l_actual_cost,
2154 x_estimated_cost => l_estimated_cost,
2155 x_return_status => l_return_status);
2156
2157 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2158 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After Calling AHL_VWP_COST_PVT.Get_WO_Cost : l_return_status - '||l_return_status);
2159 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Updating the actual cost of WO to - '||l_actual_cost);
2160 END IF;
2161
2162 -- Update with actual cost
2163 UPDATE ahl_visit_tasks_b
2164 SET actual_cost = l_actual_cost
2165 WHERE visit_task_id = l_wip_entity_rec.visit_task_id;
2166
2167 END IF;
2168 END LOOP;
2169 CLOSE get_wip_entity;
2170
2171 -- Check Error Message stack.
2172 l_msg_count := FND_MSG_PUB.count_msg;
2173 IF l_msg_count > 0 THEN
2174 x_return_status := FND_API.G_RET_STS_ERROR;
2175 RAISE FND_API.G_EXC_ERROR;
2176 END IF;
2177
2178 -- Update Repair Batch and its child task Statuses to Closed.
2179 /* UPDATE ahl_visit_tasks_b
2180 SET status_code = 'CLOSED'
2181 WHERE visit_task_id IN ( SELECT vt.visit_task_id
2182 FROM AHL_VISIT_TASKS_B vt
2183 START WITH vt.visit_task_id = p_repair_batch_id
2184 CONNECT BY PRIOR VT.VISIT_TASK_ID = VT.COST_PARENT_ID ); */
2185
2186 -- Update Repair Batch Summary task status to Closed
2187 UPDATE ahl_visit_tasks_b
2188 SET status_code = 'CLOSED'
2189 WHERE visit_task_id = p_repair_batch_id ;
2190
2191 ELSE
2192
2193 FND_MESSAGE.SET_NAME('AHL','AHL_CMP_OPEN_WO_EXISTS'); --Repair Batch cannot be Closed as it has Open workorders.
2194 FND_MESSAGE.Set_Token('REPAIR_BATCH', l_batch_dtl_rec.repair_batch_name);
2195 FND_MSG_PUB.ADD;
2196 RAISE FND_API.G_EXC_ERROR;
2197 END IF;
2198
2199 IF G_DEBUG='Y' THEN
2200 AHL_DEBUG_PUB.enable_debug;
2201 AHL_DEBUG_PUB.debug('End of Close Repair Batch API');
2202 END IF;
2203
2204 -- Standard check of p_commit
2205 IF FND_API.To_Boolean(p_commit) THEN
2206 COMMIT;
2207 END IF;
2208
2209 EXCEPTION
2210 WHEN FND_API.G_EXC_ERROR THEN
2211 x_return_status := FND_API.G_RET_STS_ERROR;
2212 Rollback to Close_Repair_Batch_Pvt;
2213 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2214 p_data => x_msg_data,
2215 P_ENCODED => FND_API.G_FALSE);
2216
2217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2218 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2219 Rollback to Close_Repair_Batch_Pvt;
2220 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2221 P_DATA => X_MSG_DATA,
2222 p_encoded => fnd_api.g_false);
2223
2224 WHEN OTHERS THEN
2225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2226 Rollback to Close_Repair_Batch_Pvt;
2227 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2228 p_procedure_name => 'Close_Repair_Batch',
2229 p_error_text => SQLERRM);
2230 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2231 p_data => x_msg_data,
2232 p_encoded => fnd_api.g_false);
2233
2234 END Close_Repair_Batch;
2235 ----------------------------------------------------------------------------------------------------
2236
2237 ---------------------------------------------------------------------------------------------
2238 -- Kasridha: Procedure to move the instance to the in-repair locator
2239 ------------------------------------------------------------------------------------------------------
2240 -- Start of Comments
2241 -- Procedure name : Move_To_InRepair_Locator
2242 -- Type : Public
2243 -- Function : This procedure move the selected instance from planning to in-repair locator
2244 -- when a RTS workorder is added to the repair batch through addition of MRs.
2245 -- Pre-reqs :
2246 -- Parameters :
2247 --
2248 -- Move_To_InRepair_Locator
2249 -- p_wip_entity_id IN WIP Entity ID of the first RTS workorder Required
2250 -- p_repair_batch_name IN Repair batch name Required
2251 -- p_instance_id IN Instance ID Required
2252 --
2253 -- End of Comments
2254 -------------------------------------------------------------------------------------------------------
2255 PROCEDURE Move_To_InRepair_Locator
2256 (
2257 p_api_version IN NUMBER := 1.0,
2258 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2259 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2260 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2261 p_wip_entity_id IN NUMBER,
2262 p_repair_batch_name IN VARCHAR2,
2263 p_instance_id IN NUMBER,
2264 x_return_status OUT NOCOPY VARCHAR2,
2265 x_msg_count OUT NOCOPY NUMBER,
2266 x_msg_data OUT NOCOPY VARCHAR2
2267 )
2268
2269 IS
2270 l_api_name CONSTANT VARCHAR2(30) := 'Move_To_InRepair_Locator';
2271 l_api_version CONSTANT NUMBER := 1.0;
2272 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
2273 l_serial_number NUMBER;
2274 l_lot_number VARCHAR2(80);
2275 l_new_instance_id NUMBER;
2276 l_comp_inrepair_loc_id NUMBER;
2277 l_comp_planning_loc_id NUMBER;
2278 l_src_locator_id NUMBER;
2279 l_src_subinv_name VARCHAR2(10);
2280 l_dst_subinv_name VARCHAR2(10);
2281 l_dst_locator_id NUMBER;
2282 l_dst_locator_segments VARCHAR2(240);
2283 l_task_rec AHL_VWP_RULES_PVT.Task_Rec_Type;
2284 l_locator_inst_id NUMBER;
2285 l_locator_inst_qty NUMBER;
2286 l_dummy VARCHAR2(1);
2287 l_return_value BOOLEAN;
2288 l_open_wo_count NUMBER;
2289 -- variables/records needed to call CSI public API to expire instance
2290 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
2291 l_csi_upd_transaction_rec csi_datastructures_pub.transaction_rec;
2292 l_csi_instance_rec csi_datastructures_pub.instance_rec;
2293 l_csi_transaction_type_id NUMBER;
2294 l_csi_instance_id_lst csi_datastructures_pub.id_tbl;
2295
2296 TYPE t_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2297 l_task_id_tbl t_id_tbl;
2298
2299 -- variables for locator flexfield handling
2300 l_fftype FND_FLEX_KEY_API.flexfield_type;
2301 l_ffstrctr FND_FLEX_KEY_API.structure_type;
2302 l_sprtr VARCHAR2(1);
2303
2304 -- variable for material transfer
2305 l_ahl_mtlxfr_tbl Ahl_Mtlxfr_Tbl_Type;
2306
2307 -- Get the instance details for a given instance
2308
2309 CURSOR get_instance_dtls_csr(p_instance_id_csr NUMBER) IS
2310 SELECT CSI.serial_number,
2311 CSI.lot_number,
2312 CSI.quantity,
2313 CSI.unit_of_measure,
2314 MTL.concatenated_segments,
2315 CSI.inv_master_organization_id,
2316 CSI.inv_organization_id,
2317 CSI.inventory_item_id
2318 FROM CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL
2319 WHERE CSI.instance_id = p_instance_id_csr
2320 AND MTL.inventory_item_id = CSI.inventory_item_id
2321 AND MTL.organization_id = CSI.inv_master_organization_id
2322 AND TRUNC(NVL(CSI.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
2323 AND TRUNC(NVL(CSI.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
2324
2325
2326 -- pdoki modified for Bug 14068468
2327 -- Get the required visit and task details for a given repair batch
2328 CURSOR get_visit_task_det_csr (p_rpr_batch_name_csr VARCHAR2) IS
2329 SELECT VST.visit_number,
2330 TSK.visit_task_number,
2331 VST.project_id,
2332 PRJ.name project_number,
2333 PRT.task_id project_task_id,
2334 PRT.task_number project_task_number,
2335 VST.comp_inrepair_loc_id,
2336 VST.comp_planning_loc_id
2337 FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
2338 PA_PROJECTS_ALL PRJ, PA_TASKS PRT
2339 WHERE TSK.repair_batch_name = p_rpr_batch_name_csr
2340 AND VST.visit_id = TSK.visit_id
2341 AND PRJ.project_id = VST.project_id
2342 AND PRT.project_id = PRJ.project_id
2343 AND PRT.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
2344
2345
2346 -- pdoki modified for Bug 14068468
2347 -- Get the locator id and sub inventory name of the planning locator
2348 -- for a given repair batch
2349 CURSOR get_src_locator_id_csr (p_rpr_batch_name_csr VARCHAR2) IS
2350 SELECT MTL.inventory_location_id,
2351 MTL.subinventory_code
2352 FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
2353 MTL_ITEM_LOCATIONS MTL
2354 WHERE TSK.repair_batch_name = p_rpr_batch_name_csr
2355 AND VST.visit_id = TSK.visit_id
2356 AND MTL.physical_location_id = VST.comp_planning_loc_id
2357 AND MTL.project_id = VST.project_id
2358 AND MTL.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
2359
2360 -- pdoki modified for Bug 14068468
2361 -- Get the locator id and sub inventory name of the in-repair locator
2362 -- for a given repair batch
2363 CURSOR get_inrepair_locator_id_csr (p_rpr_batch_name_csr VARCHAR2) IS
2364 SELECT MTL.inventory_location_id,
2365 MTL.subinventory_code
2366 FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
2367 MTL_ITEM_LOCATIONS MTL
2368 WHERE TSK.repair_batch_name = p_rpr_batch_name_csr
2369 AND VST.visit_id = TSK.visit_id
2370 AND MTL.physical_location_id = VST.comp_inrepair_loc_id
2371 AND MTL.project_id = VST.project_id
2372 AND MTL.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
2373
2374
2375 -- Get the new instance from the in-repair locator for a given repair batch
2376 CURSOR get_in_repair_instance_csr(p_inrepair_locator_id NUMBER) IS
2377 SELECT instance_id FROM csi_item_instances WHERE
2378 inv_locator_id = p_inrepair_locator_id;
2379 -- cursor to get the concatenated segments for visit's physical locator
2380 CURSOR get_locator_segments_csr (p_inv_locator_id_csr NUMBER) IS
2381 SELECT MTK.concatenated_segments,
2382 MTI.subinventory_code
2383 FROM MTL_ITEM_LOCATIONS MTI, MTL_ITEM_LOCATIONS_KFV MTK
2384 WHERE MTK.inventory_location_id = MTI.inventory_location_id
2385 AND MTI.inventory_location_id = p_inv_locator_id_csr;
2386
2387 -- get the object version number for instance updation
2388 CURSOR get_csi_obj_ver_num(c_instance_id NUMBER) IS
2389 SELECT object_version_number
2390 FROM csi_item_instances
2391 WHERE instance_id = c_instance_id
2392 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2393 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2394
2395 -- Get all the WIP Entity IDs pertaining to the instance and repair batch name
2396 CURSOR get_wip_entity_ids_csr(p_instance_id_csr NUMBER, p_repair_batch_csr VARCHAR2) IS
2397 SELECT wip_entity_id
2398 FROM ahl_workorders
2399 WHERE status_code IN ('1', '3', '6', '19', '20', '17')
2400 --unreleased, released, on-hold, parts hold and pending QA approval(open workorders)
2401 AND visit_task_id IN
2402 (SELECT vt.visit_task_id
2403 FROM AHL_VISIT_TASKS_B vt
2404 WHERE instance_id = p_instance_id_csr
2405 START WITH vt.visit_task_id =
2406 (SELECT visit_task_id
2407 FROM ahl_visit_tasks_b
2408 WHERE repair_batch_name = p_repair_batch_csr
2409 )
2410 CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id
2411 );
2412
2413 -- pdoki modified for Bug 14068468
2414 -- Check if the instance is already in in-repair locator
2415 CURSOR is_in_repair_locator_csr(p_rprbatch_name_csr VARCHAR2, p_instance_id_csr NUMBER) IS
2416 SELECT 'Y'
2417 FROM (SELECT VST.comp_inrepair_loc_id, VST.project_id,
2418 VST.visit_id,
2419 TSK.project_task_id,
2420 TSK.inventory_item_id
2421 FROM AHL_VISIT_TASKS_VL TSK,
2422 AHL_VISITS_B VST
2423 WHERE TSK.repair_batch_name = p_rprbatch_name_csr
2424 AND VST.visit_id = TSK.visit_id) VISIT_TASK,
2425 MTL_ITEM_LOCATIONS_KFV INRPR_LOC, CSI_ITEM_INSTANCES csi
2426 WHERE csi.inv_locator_id = INRPR_LOC.inventory_location_id
2427 AND INRPR_LOC.physical_location_id = VISIT_TASK.comp_inrepair_loc_id
2428 AND INRPR_LOC.project_id = VISIT_TASK.project_id
2429 AND INRPR_LOC.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VISIT_TASK.project_id,VISIT_TASK.project_task_id,VISIT_TASK.visit_id)
2430 AND csi.inventory_item_id = VISIT_TASK.inventory_item_id
2431 AND csi.instance_id = p_instance_id_csr
2432 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
2433 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
2434
2435
2436
2437 l_item_inst_det_csr get_instance_dtls_csr%ROWTYPE;
2438 l_visit_task_det_csr get_visit_task_det_csr%ROWTYPE;
2439 l_wip_entity_rec get_wip_entity_ids_csr%ROWTYPE;
2440 l_wipEntityIds NUMBER;
2441 l_wip_entity_tbl t_id_tbl;
2442 i NUMBER := 0;
2443 l_is_in_repair_csr VARCHAR2(1) := 'N';
2444
2445 BEGIN
2446 -- Standard start of API savepoint
2447 SAVEPOINT Move_To_InRepair_Locator_Pvt;
2448 -- Standard call to check for call compatibility
2449 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2450 G_PKG_NAME) THEN
2451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2452 END IF;
2453 -- Initialize message list if p_init_msg_list is set to TRUE
2454 IF FND_API.To_Boolean(p_init_msg_list) THEN
2455 FND_MSG_PUB.Initialize;
2456 END IF;
2457
2458 -- Initialize API return status to success
2459 x_return_status := FND_API.G_RET_STS_SUCCESS;
2460
2461 IF G_DEBUG='Y' THEN
2462 AHL_DEBUG_PUB.enable_debug;
2463 AHL_DEBUG_PUB.debug('Entering Move_To_InRepair_Locator API with WIP Entity ID: '
2464 || p_wip_entity_id ||
2465 ' RepairBatch Name: ' || p_repair_batch_name);
2466 END IF;
2467
2468 --Check if the instance is already in in-repair locator, if yes then exit
2469 -- This is required if there is a manual intervention of rts flag
2470 -- i.e. user adds RTS flag to task(instance will be moved to in-repair), then removes it
2471 OPEN is_in_repair_locator_csr(p_repair_batch_name, p_instance_id);
2472 FETCH is_in_repair_locator_csr INTO l_is_in_repair_csr;
2473 CLOSE is_in_repair_locator_csr;
2474
2475 IF l_is_in_repair_csr = 'Y' THEN
2476 IF (l_log_statement >= l_log_current_level) THEN
2477 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Instance already in In-Repair Locator');
2478 END IF;
2479 RETURN;
2480 END IF;
2481
2482 OPEN get_instance_dtls_csr(p_instance_id);
2483 FETCH get_instance_dtls_csr INTO l_item_inst_det_csr;
2484 CLOSE get_instance_dtls_csr;
2485
2486 --For Non serialized items
2487 -- Create a dummy instance and issued to the workorder
2488 -- Update all the tasks in the repair batch with the new instance id
2489 -- Update EAM workorders with the new instance ID
2490
2491 IF l_item_inst_det_csr.serial_number IS NULL THEN
2492 -- 1) Create a dummy instance and issue it to the workorder
2493 IF (l_log_statement >= l_log_current_level) THEN
2494 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Calling AHL_CMP_PVT.Create_Instance_in_WIP');
2495 END IF;
2496 AHL_CMP_PVT.Create_Instance_in_WIP(
2497 p_api_version => l_api_version,
2498 p_init_msg_list => Fnd_Api.g_false,
2499 p_commit => Fnd_Api.g_false,
2500 p_validation_level => p_validation_level,
2501 p_wip_entity_id => p_wip_entity_id,
2502 p_instance_id => p_instance_id,
2503 x_new_instance_id => l_new_instance_id,
2504 x_return_status => x_return_status,
2505 x_msg_count => x_msg_count,
2506 x_msg_data => x_msg_data);
2507
2508 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2509 IF (l_log_statement >= l_log_current_level) THEN
2510 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'call to Create_Instance_in_WIP errored out');
2511 END IF;
2512 RAISE FND_API.G_EXC_ERROR;
2513 END IF;
2514 /********************End of Dummy Instance Creation *********************************************/
2515
2516 IF (l_log_statement >= l_log_current_level) THEN
2517 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Updating tasks in repair batch to new instance');
2518 END IF;
2519 IF (l_log_statement >= l_log_current_level) THEN
2520 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Before updating WOs');
2521 SELECT COUNT(wo.visit_task_id) INTO l_open_wo_count FROM ahl_workorders wo, ahl_visit_tasks_b task
2522 WHERE
2523 task.visit_task_id = wo.visit_task_id
2524 and task.instance_id = p_instance_id;
2525 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Openworkorders for the instance::' || l_open_wo_count);
2526 END IF;
2527
2528 -- get the list of wip entity ids
2529 OPEN get_wip_entity_ids_csr (p_instance_id, p_repair_batch_name);
2530 FETCH get_wip_entity_ids_csr BULK COLLECT INTO l_wip_entity_tbl;
2531 CLOSE get_wip_entity_ids_csr;
2532
2533 -- 2) Update all the tasks in the repair batch with the new instance id
2534 UPDATE ahl_visit_tasks_b SET instance_id = l_new_instance_id WHERE
2535 visit_id = (SELECT visit_id FROM ahl_visit_tasks_b
2536 WHERE repair_batch_name = p_repair_batch_name)
2537 AND instance_id = p_instance_id;
2538
2539 IF (l_log_statement >= l_log_current_level) THEN
2540 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'After updating WOs');
2541 SELECT COUNT(wo.visit_task_id) INTO l_open_wo_count FROM ahl_workorders wo, ahl_visit_tasks_b task
2542 WHERE
2543 task.visit_task_id = wo.visit_task_id
2544 and task.instance_id = p_instance_id;
2545 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Openworkorders for the instance::' || l_open_wo_count);
2546 END IF;
2547 IF (l_log_statement >= l_log_current_level) THEN
2548 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Before calling EAM_PROCESS_WO_PUB.'
2549 || 'process_wo');
2550 SELECT COUNT(wip_entity_id) INTO l_wipEntityIds FROM wip_discrete_jobs
2551 where maintenance_object_id = p_instance_id;
2552 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'EAMWOcount Before exec'
2553 || l_wipEntityIds);
2554 END IF;
2555 IF (l_log_statement >= l_log_current_level) THEN
2556 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Calling AHL_CMP_PVT.Update_EAM_Workorders');
2557 END IF;
2558
2559 FOR i IN l_wip_entity_tbl.FIRST .. l_wip_entity_tbl.LAST
2560 LOOP
2561 -- 3) Update EAM workorders with the new instance ID
2562 AHL_CMP_PVT.Update_EAM_Workorders(
2563 p_api_version => l_api_version,
2564 p_init_msg_list => Fnd_Api.g_false,
2565 p_commit => Fnd_Api.g_false,
2566 p_validation_level => p_validation_level,
2567 p_wip_entity_id => l_wip_entity_tbl(i),
2568 p_instance_id => l_new_instance_id,
2569 x_return_status => x_return_status,
2570 x_msg_count => x_msg_count,
2571 x_msg_data => x_msg_data);
2572 END LOOP;
2573
2574 /********************End of Update of EAM Workorders**************************************/
2575 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2576 IF (l_log_statement >= l_log_current_level) THEN
2577 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'call to Update_EAM_Workorders errored out');
2578 END IF;
2579 RAISE FND_API.G_EXC_ERROR;
2580 END IF;
2581 END IF;
2582 IF (l_log_statement >= l_log_current_level) THEN
2583 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'After calling EAM_PROCESS_WO_PUB.'
2584 || 'process_wo');
2585 SELECT COUNT(wip_entity_id) INTO l_wipEntityIds FROM wip_discrete_jobs
2586 where maintenance_object_id = p_instance_id;
2587 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'EAMWOcount after exec'
2588 || l_wipEntityIds);
2589 END IF;
2590 OPEN get_visit_task_det_csr (p_repair_batch_name);
2591 FETCH get_visit_task_det_csr INTO l_visit_task_det_csr;
2592 CLOSE get_visit_task_det_csr;
2593
2594 -- 4) Move the instances from the planning to in-repair locator
2595 -- Create the concatenated segments for the task locator
2596 -- get the concatenated segments for visit's physical locator
2597
2598 -- source - Planning locator and subinventory name
2599 OPEN get_src_locator_id_csr (p_repair_batch_name);
2600 FETCH get_src_locator_id_csr INTO l_src_locator_id, l_src_subinv_name;
2601 CLOSE get_src_locator_id_csr;
2602
2603 -- destination locator segments
2604 OPEN get_locator_segments_csr (l_visit_task_det_csr.comp_inrepair_loc_id);
2605 FETCH get_locator_segments_csr INTO l_dst_locator_segments, l_dst_subinv_name;
2606 CLOSE get_locator_segments_csr;
2607
2608 IF (l_log_statement >= l_log_current_level) THEN
2609 FND_LOG.string(l_log_statement, L_DEBUG_KEY,' physical l_dst_locator_segments = '||l_dst_locator_segments);
2610 END IF;
2611
2612 -- get the separator defined for the locator flexfield
2613 FND_PROFILE.put('MFG_ORGANIZATION_ID', l_item_inst_det_csr.inv_organization_id); -- set mfg_organization_id profile - bug# 6010795
2614 FND_FLEX_KEY_API.set_session_mode('seed_data');
2615 l_fftype := FND_FLEX_KEY_API.find_flexfield('INV', 'MTLL'); -- get flexfield type
2616 l_ffstrctr := FND_FLEX_KEY_API.find_structure(l_fftype, 101); -- get flexfield structure type
2617 l_sprtr := l_ffstrctr.segment_separator; -- get flexfield segment separator
2618
2619 IF (l_log_statement >= l_log_current_level) THEN
2620 FND_LOG.string(l_log_statement, L_DEBUG_KEY,' l_sprtr = '||l_sprtr);
2621 END IF;
2622
2623 -- form the logical locator segments for the newly created task
2624 l_dst_locator_segments := SUBSTR(l_dst_locator_segments, 1, (LENGTH(l_dst_locator_segments)-1));
2625
2626 IF (l_log_statement >= l_log_current_level) THEN
2627 FND_LOG.string(l_log_statement, L_DEBUG_KEY,' substring l_dst_locator_segments = '||l_dst_locator_segments);
2628 END IF;
2629
2630 l_dst_locator_segments := l_dst_locator_segments||
2631 l_visit_task_det_csr.project_number||l_sprtr||
2632 l_visit_task_det_csr.project_task_number;
2633
2634 IF (l_log_statement >= l_log_current_level) THEN
2635 FND_LOG.string(l_log_statement, L_DEBUG_KEY,' logical l_dst_locator_segments = '||l_dst_locator_segments);
2636 FND_LOG.string(l_log_statement, L_DEBUG_KEY,' src_locator_id = '|| l_src_locator_id);
2637 FND_LOG.string(l_log_statement, L_DEBUG_KEY,' src_subinv_name = '|| l_src_subinv_name);
2638 END IF;
2639
2640 -- 2) Populate the material transfer record and call the API
2641 l_ahl_mtlxfr_tbl(0).inventory_item_id := l_item_inst_det_csr.inventory_item_id;
2642 l_ahl_mtlxfr_tbl(0).organization_id := l_item_inst_det_csr.inv_organization_id;
2643 l_ahl_mtlxfr_tbl(0).src_subinv_name := l_src_subinv_name;
2644 l_ahl_mtlxfr_tbl(0).src_locator_id := l_src_locator_id;
2645 l_ahl_mtlxfr_tbl(0).quantity := l_item_inst_det_csr.quantity;
2646 l_ahl_mtlxfr_tbl(0).uom := l_item_inst_det_csr.unit_of_measure;
2647 l_ahl_mtlxfr_tbl(0).serial_number := l_item_inst_det_csr.serial_number;
2648 l_ahl_mtlxfr_tbl(0).lot_number := l_item_inst_det_csr.lot_number;
2649 l_ahl_mtlxfr_tbl(0).transaction_type_id := 67; -- for project tasks transfer
2650 l_ahl_mtlxfr_tbl(0).transaction_reference := 'AHL - '|| p_repair_batch_name;
2651 l_ahl_mtlxfr_tbl(0).dst_subinv_name := l_dst_subinv_name;
2652 l_ahl_mtlxfr_tbl(0).dst_locator_segments := l_dst_locator_segments;
2653
2654 -- call the material transfer API, and let the defaults for input parameters kick in
2655 IF (l_log_statement >= l_log_current_level) THEN
2656 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'calling Perform_Mtl_Xfr');
2657 END IF;
2658 Perform_Mtl_Xfr (
2659 p_x_ahl_mtlxfr_tbl => l_ahl_mtlxfr_tbl,
2660 x_return_status => x_return_status,
2661 x_msg_count => x_msg_count,
2662 x_msg_data => x_msg_data
2663 );
2664 IF (l_log_statement >= l_log_current_level) THEN
2665 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'called Perform_Mtl_Xfr');
2666 END IF;
2667
2668 -- if the call resulted in error, then raise it
2669 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2670 IF (l_log_statement >= l_log_current_level) THEN
2671 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'call to Perform_Mtl_Xfr errored out');
2672 END IF;
2673 RAISE FND_API.G_EXC_ERROR;
2674 END IF;
2675 /********************End of Material Transfer ***************************************************/
2676 -- 5) For non-serialized items - update back all the open workorders / tasks (non released)
2677 -- of the repair batch with the new In-Repair locator instance.
2678
2679 IF l_item_inst_det_csr.serial_number IS NULL THEN
2680
2681 IF (l_log_statement >= l_log_current_level) THEN
2682 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Update tasks with the instance');
2683 END IF;
2684
2685 -- get the in-repair locator id
2686 OPEN get_inrepair_locator_id_csr (p_repair_batch_name);
2687 FETCH get_inrepair_locator_id_csr INTO l_comp_inrepair_loc_id, l_dst_subinv_name;
2688 CLOSE get_inrepair_locator_id_csr;
2689
2690 -- get the new instance id from the in-repair locator
2691 OPEN get_in_repair_instance_csr (l_comp_inrepair_loc_id);
2692 FETCH get_in_repair_instance_csr INTO l_locator_inst_id;
2693 CLOSE get_in_repair_instance_csr;
2694
2695 IF (l_log_statement >= l_log_current_level) THEN
2696 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'Instance ID from inrepair locator:'
2697 || l_locator_inst_id);
2698 END IF;
2699
2700 -- Update all the tasks in the repair batch with the new locator instance id
2701 UPDATE ahl_visit_tasks_b SET instance_id = l_locator_inst_id WHERE
2702 visit_id = (SELECT visit_id FROM ahl_visit_tasks_b
2703 WHERE repair_batch_name = p_repair_batch_name)
2704 AND instance_id = l_new_instance_id;
2705
2706 IF (l_log_statement >= l_log_current_level) THEN
2707 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Calling AHL_CMP_PVT.Update_EAM_Workorders');
2708 END IF;
2709 -- Update EAM workorders with the new locator instance ID
2710 FOR i IN l_wip_entity_tbl.FIRST .. l_wip_entity_tbl.LAST
2711 LOOP
2712 AHL_CMP_PVT.Update_EAM_Workorders(
2713 p_api_version => l_api_version,
2714 p_init_msg_list => Fnd_Api.g_false,
2715 p_commit => Fnd_Api.g_false,
2716 p_validation_level => p_validation_level,
2717 p_wip_entity_id => l_wip_entity_tbl(i),
2718 p_instance_id => l_locator_inst_id,
2719 x_return_status => x_return_status,
2720 x_msg_count => x_msg_count,
2721 x_msg_data => x_msg_data);
2722 END LOOP;
2723
2724 /********************End of Update of EAM Workorders**************************************/
2725 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2726 IF (l_log_statement >= l_log_current_level) THEN
2727 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'call to Update_EAM_Workorders errored out');
2728 END IF;
2729 RAISE FND_API.G_EXC_ERROR;
2730 END IF;
2731
2732 -- 6) Expire the dummy instance
2733 --UPDATE CSI_ITEM_INSTANCES set ACTIVE_END_DATE = TRUNC(SYSDATE) WHERE
2734 --instance_id = l_new_instance_id;
2735 -- Call CSI PUB API to expire the instance
2736 -- get the transaction type
2737 AHL_UTIL_UC_PKG.getcsi_transaction_id('CMRO_MAINTENANCE_WORK_ORDER',l_csi_transaction_type_id, l_return_value);
2738 IF NOT l_return_value THEN
2739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2740 END IF;
2741
2742 --Set the CSI transaction record
2743 l_csi_transaction_rec.source_transaction_date := SYSDATE;
2744 l_csi_transaction_rec.transaction_type_id := l_csi_transaction_type_id;
2745 l_csi_upd_transaction_rec := l_csi_transaction_rec;
2746 --Call CSI API to expire the instance and all its descendants if exist
2747 l_csi_instance_rec.instance_id := l_new_instance_id;
2748 OPEN get_csi_obj_ver_num(l_new_instance_id);
2749 FETCH get_csi_obj_ver_num INTO l_dummy;
2750
2751 IF (get_csi_obj_ver_num%NOTFOUND) THEN
2752 CLOSE get_csi_obj_ver_num;
2753 FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_DELETED');
2754 FND_MSG_PUB.add;
2755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2756 ELSE
2757 CLOSE get_csi_obj_ver_num;
2758 END IF;
2759
2760 l_csi_instance_rec.object_version_number := l_dummy;
2761
2762 IF (l_log_statement >= l_log_current_level) THEN
2763 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'calling expire_item_instance');
2764 END IF;
2765
2766 CSI_ITEM_INSTANCE_PUB.expire_item_instance(
2767 p_api_version => 1.0,
2768 p_instance_rec => l_csi_instance_rec,
2769 p_expire_children => FND_API.G_TRUE,
2770 p_txn_rec => l_csi_upd_transaction_rec,
2771 x_instance_id_lst => l_csi_instance_id_lst,
2772 x_return_status => x_return_status,
2773 x_msg_count => x_msg_count,
2774 x_msg_data => x_msg_data);
2775
2776 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2777 IF (l_log_statement >= l_log_current_level) THEN
2778 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'Error in CSI_ITEM_INSTANCE_PUB.expire_item_instance');
2779 END IF;
2780 RAISE FND_API.G_EXC_ERROR;
2781 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2782 IF (l_log_statement >= l_log_current_level) THEN
2783 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'Error in CSI_ITEM_INSTANCE_PUB.expire_item_instance');
2784 END IF;
2785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2786 END IF;
2787
2788 IF (l_log_statement >= l_log_current_level) THEN
2789 FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'called expire_item_instance');
2790 END IF;
2791
2792 END IF;
2793
2794
2795
2796 -- Standard check of p_commit
2797 IF FND_API.To_Boolean(p_commit) THEN
2798 COMMIT;
2799 END IF;
2800 EXCEPTION
2801 WHEN FND_API.G_EXC_ERROR THEN
2802 x_return_status := FND_API.G_RET_STS_ERROR;
2803 Rollback to Move_To_InRepair_Locator_Pvt;
2804 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2805 p_data => x_msg_data,
2806 P_ENCODED => FND_API.G_FALSE);
2807
2808 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2809 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2810 Rollback to Move_To_InRepair_Locator_Pvt;
2811 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2812 P_DATA => X_MSG_DATA,
2813 p_encoded => fnd_api.g_false);
2814
2815 WHEN OTHERS THEN
2816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2817 Rollback to Move_To_InRepair_Locator_Pvt;
2818 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2819 p_procedure_name => 'Move_To_InRepair_Locator',
2820 p_error_text => SQLERRM);
2821 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2822 p_data => x_msg_data,
2823 p_encoded => fnd_api.g_false);
2824
2825 END Move_To_InRepair_Locator;
2826 ---------------------------------------------------------------------------------------------
2827 -- Kasridha: Procedure to create a dummy instance in WIP
2828 ------------------------------------------------------------------------------------------------------
2829 -- Start of Comments
2830 -- Procedure name : Create_Instance_in_WIP
2831 -- Type : Public
2832 -- Function : This procedure is used to create a dummy instance in WIP.
2833 -- Pre-reqs :
2834 -- Parameters :
2835 --
2836 -- Create_Instance_in_WIP
2837 -- p_wip_entity_id IN WIP Entity ID of the first RTS workorder Required
2838 -- p_instance_id IN Instance ID Required
2839 --
2840 -- End of Comments
2841 -------------------------------------------------------------------------------------------------------
2842 PROCEDURE Create_Instance_in_WIP
2843 (
2844 p_api_version IN NUMBER := 1.0,
2845 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2846 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2847 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2848 p_wip_entity_id IN NUMBER,
2849 p_instance_id IN NUMBER,
2850 x_new_instance_id OUT NOCOPY NUMBER,
2851 x_return_status OUT NOCOPY VARCHAR2,
2852 x_msg_count OUT NOCOPY NUMBER,
2853 x_msg_data OUT NOCOPY VARCHAR2
2854 )
2855 IS
2856 -- cursor to get the details from the existing instance in repair batch locator
2857 CURSOR get_csi_item_instance_det_csr(c_csi_instance_id NUMBER) IS
2858 SELECT c.location_id,
2859 c.inventory_item_id,
2860 c.inv_master_organization_id,
2861 c.inv_organization_id,
2862 c.quantity,
2863 c.unit_of_measure,
2864 c.lot_number,
2865 c.install_date,
2866 c.inventory_revision,
2867 p.party_id,
2868 p.party_source_table,
2869 p.instance_party_id
2870 FROM csi_item_instances c, csi_i_parties p
2871 WHERE c.instance_id = c_csi_instance_id
2872 AND p.instance_id = c.instance_id
2873 AND p.relationship_type_code = 'OWNER'
2874 AND trunc(nvl(c.active_start_date, SYSDATE)) <= trunc(SYSDATE)
2875 AND trunc(nvl(c.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2876
2877 -- cursor to get instance accounts details
2878 CURSOR get_csi_ip_accounts_det_csr(c_instance_party_id NUMBER) IS
2879 SELECT party_account_id
2880 FROM csi_ip_accounts
2881 WHERE relationship_type_code = 'OWNER'
2882 AND instance_party_id = c_instance_party_id
2883 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2884 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2885
2886 -- local variables
2887 l_rpr_batch_instance NUMBER := p_instance_id;
2888 l_draft_wo_wip_entity_id NUMBER := p_wip_entity_id;
2889 l_msg_count NUMBER;
2890 l_csi_item_instance_det get_csi_item_instance_det_csr%ROWTYPE;
2891 l_return_val BOOLEAN;
2892 l_attribute_id NUMBER;
2893 l_index NUMBER := 0;
2894 l_transaction_type_id NUMBER;
2895
2896 -- variables needed for CSI API call
2897 l_csi_instance_rec csi_datastructures_pub.instance_rec;
2898 l_csi_party_rec csi_datastructures_pub.party_rec;
2899 l_csi_party_tbl csi_datastructures_pub.party_tbl;
2900 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
2901 l_party_account_rec csi_datastructures_pub.party_account_rec;
2902 l_csi_account_tbl csi_datastructures_pub.party_account_tbl;
2903 l_csi_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
2904 l_csi_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
2905 l_csi_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
2906 l_csi_extend_attrib_rec csi_datastructures_pub.extend_attrib_values_rec;
2907 l_csi_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2908
2909 l_msg_index_out NUMBER;
2910
2911 l_api_name CONSTANT VARCHAR2(30) := 'Create_Instance_in_WIP';
2912 l_api_version CONSTANT NUMBER := 1.0;
2913 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
2914 BEGIN
2915
2916 -- Standard call to check for call compatibility
2917 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2918 G_PKG_NAME) THEN
2919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2920 END IF;
2921 -- Initialize message list if p_init_msg_list is set to TRUE
2922 IF FND_API.To_Boolean(p_init_msg_list) THEN
2923 FND_MSG_PUB.Initialize;
2924 END IF;
2925
2926 -- Initialize API return status to success
2927 x_return_status := FND_API.G_RET_STS_SUCCESS;
2928
2929 IF G_DEBUG='Y' THEN
2930 AHL_DEBUG_PUB.enable_debug;
2931 AHL_DEBUG_PUB.debug('Entering Create_Instance_in_WIP API with WIP Entity ID: '|| p_wip_entity_id ||
2932 ' Instance ID: ' || p_instance_id);
2933 END IF;
2934
2935 -- Get the details of the existing instance
2936 OPEN get_csi_item_instance_det_csr (l_rpr_batch_instance);
2937 FETCH get_csi_item_instance_det_csr INTO l_csi_item_instance_det;
2938 CLOSE get_csi_item_instance_det_csr;
2939
2940 -- Get the error message count
2941 l_msg_count := FND_MSG_PUB.count_msg;
2942
2943 -- Set csi instance record
2944 l_csi_instance_rec.inventory_item_id := l_csi_item_instance_det.inventory_item_id;
2945 l_csi_instance_rec.vld_organization_id := l_csi_item_instance_det.inv_organization_id;
2946 l_csi_instance_rec.quantity := l_csi_item_instance_det.quantity;
2947 l_csi_instance_rec.unit_of_measure := l_csi_item_instance_det.unit_of_measure;
2948 l_csi_instance_rec.install_date := l_csi_item_instance_det.install_date;
2949 -- l_csi_instance_rec.location_id := l_csi_item_instance_det.location_id;
2950 l_csi_instance_rec.location_type_code := 'WIP';
2951 l_csi_instance_rec.wip_job_id := l_draft_wo_wip_entity_id;
2952 l_csi_instance_rec.mfg_serial_number_flag := 'N'; -- must always be N
2953 l_csi_instance_rec.instance_usage_code := NULL;
2954
2955 IF (l_csi_item_instance_det.lot_number IS NOT NULL) THEN
2956 l_csi_instance_rec.lot_number := l_csi_item_instance_det.lot_number;
2957 END IF;
2958
2959 IF (l_csi_item_instance_det.inventory_revision IS NOT NULL) THEN
2960 l_csi_instance_rec.inventory_revision := l_csi_item_instance_det.inventory_revision;
2961 END IF;
2962
2963 AHL_UTIL_UC_PKG.getcsi_attribute_id('AHL_MFG_DATE', l_attribute_id, l_return_val);
2964
2965 IF NOT(l_return_val) THEN
2966 FND_MESSAGE.set_name('AHL', 'AHL_UC_ATTRIB_CODE_MISSING');
2967 FND_MESSAGE.set_token('CODE', 'AHL_MFG_DATE');
2968 FND_MSG_PUB.ADD;
2969 RAISE FND_API.G_EXC_ERROR;
2970 ELSE
2971 l_csi_extend_attrib_rec.attribute_id := l_attribute_id;
2972 l_csi_extend_attrib_rec.attribute_value := TO_CHAR(SYSDATE, 'DD/MM/YYYY');
2973 l_csi_ext_attrib_values_tbl(1) := l_csi_extend_attrib_rec;
2974 END IF;
2975
2976 -- Build CSI party record
2977 l_csi_party_rec.party_id := l_csi_item_instance_det.party_id;
2978 l_csi_party_rec.relationship_type_code := 'OWNER';
2979 l_csi_party_rec.party_source_table := l_csi_item_instance_det.party_source_table;
2980 l_csi_party_rec.contact_flag := 'N';
2981 l_csi_party_tbl(1) := l_csi_party_rec;
2982
2983 -- Build CSI accounts table
2984 FOR l_csi_ip_accounts_det IN get_csi_ip_accounts_det_csr(l_csi_item_instance_det.instance_party_id)
2985 LOOP
2986 l_party_account_rec.party_account_id := l_csi_ip_accounts_det.party_account_id;
2987 l_party_account_rec.relationship_type_code := 'OWNER';
2988 l_party_account_rec.parent_tbl_index := 1;
2989 l_index := l_index + 1;
2990 l_csi_account_tbl(l_index) := l_party_account_rec;
2991 END LOOP;
2992
2993 -- Build CSI transaction record, first get transaction_type_id
2994 AHL_UTIL_UC_PKG.getcsi_transaction_id('CMRO_MAINTENANCE_WORK_ORDER', l_transaction_type_id, l_return_val);
2995
2996 IF NOT(l_return_val) THEN
2997 FND_MESSAGE.Set_Name('AHL', 'AHL_UC_TXNCODE_INVALID');
2998 FND_MESSAGE.Set_Token('CODE', 'CMRO_MAINTENANCE_WORK_ORDER');
2999 FND_MSG_PUB.ADD;
3000 RAISE FND_API.G_EXC_ERROR;
3001 END IF;
3002
3003 IF (l_log_statement >= l_log_current_level) THEN
3004 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Before calling CSI_ITEM_INSTANCE_PUB.'
3005 || 'create_item_instance');
3006 END IF;
3007
3008 l_csi_transaction_rec.source_transaction_date := SYSDATE;
3009 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
3010
3011 CSI_ITEM_INSTANCE_PUB.create_item_instance(
3012 p_api_version => 1.0,
3013 p_instance_rec => l_csi_instance_rec,
3014 p_txn_rec => l_csi_transaction_rec,
3015 p_ext_attrib_values_tbl => l_csi_ext_attrib_values_tbl,
3016 p_party_tbl => l_csi_party_tbl,
3017 p_account_tbl => l_csi_account_tbl,
3018 p_pricing_attrib_tbl => l_csi_pricing_attrib_tbl,
3019 p_org_assignments_tbl => l_csi_org_assignments_tbl,
3020 p_asset_assignment_tbl => l_csi_asset_assignment_tbl,
3021 x_return_status => x_return_status,
3022 x_msg_count => x_msg_count,
3023 x_msg_data => x_msg_data);
3024
3025 IF (l_log_statement >= l_log_current_level) THEN
3026 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'After calling CSI_ITEM_INSTANCE_PUB.'
3027 || 'create_item_instance');
3028 END IF;
3029
3030 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3032 END IF;
3033
3034 x_new_instance_id := l_csi_instance_rec.instance_id;
3035
3036 IF (l_log_statement >= l_log_current_level) THEN
3037 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'New Instance ID: ' || x_new_instance_id);
3038 END IF;
3039
3040 -- Standard check of p_commit
3041 IF FND_API.To_Boolean(p_commit) THEN
3042 COMMIT;
3043 END IF;
3044
3045 -- Error Handling
3046 EXCEPTION
3047 WHEN FND_API.G_EXC_ERROR THEN
3048 x_return_status := FND_API.G_RET_STS_ERROR;
3049 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3050 p_data => x_msg_data,
3051 P_ENCODED => FND_API.G_FALSE);
3052
3053 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3054 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3055 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3056 P_DATA => X_MSG_DATA,
3057 p_encoded => fnd_api.g_false);
3058
3059 WHEN OTHERS THEN
3060 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3061 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3062 p_procedure_name => 'Create_Instance_in_WIP',
3063 p_error_text => SQLERRM);
3064 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3065 p_data => x_msg_data,
3066 p_encoded => fnd_api.g_false);
3067 END Create_Instance_in_WIP;
3068
3069 ---------------------------------------------------------------------------------------------
3070 -- Kasridha: Procedure to update all EAM workorders with an instance
3071 ------------------------------------------------------------------------------------------------------
3072 -- Start of Comments
3073 -- Procedure name : Update_EAM_Workorders
3074 -- Type : Public
3075 -- Function : This procedure is used to update all EAM workorders with an instance.
3076 -- Pre-reqs :
3077 -- Parameters :
3078 --
3079 -- Update_EAM_Workorders
3080 -- p_wip_entity_id IN WIP Entity ID of the first RTS workorder Required
3081 -- p_instance_id IN Instance ID Required
3082 --
3083 -- End of Comments
3084 -------------------------------------------------------------------------------------------------------
3085 PROCEDURE Update_EAM_Workorders
3086 (
3087 p_api_version IN NUMBER := 1.0,
3088 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3089 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3090 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3091 p_wip_entity_id IN NUMBER,
3092 p_instance_id IN NUMBER,
3093 x_return_status OUT NOCOPY VARCHAR2,
3094 x_msg_count OUT NOCOPY NUMBER,
3095 x_msg_data OUT NOCOPY VARCHAR2
3096 )
3097
3098 IS
3099 -- Cursor to get the workorder details for a given WIP entity ID
3100 CURSOR get_workorder_dtls_csr(p_wip_entity_id_csr NUMBER) IS
3101 SELECT wo.workorder_id,
3102 task.inventory_item_id, visit.organization_id,
3103 wo.status_code
3104 FROM
3105 ahl_workorders wo, ahl_visit_tasks_b task,
3106 ahl_visits_b visit
3107 WHERE
3108 wo.wip_entity_id = p_wip_entity_id_csr
3109 AND task.visit_task_id = wo.visit_task_id
3110 AND visit.visit_id = task.visit_id;
3111
3112 --Local variables
3113
3114 l_bo_identifier VARCHAR2(10) := 'AHL';
3115 l_output_dir VARCHAR2(80);
3116 l_debug_filename VARCHAR2(80);
3117 l_debug_file_mode VARCHAR2(1);
3118 l_return_status VARCHAR2(1);
3119 l_workorder_id NUMBER;
3120 l_item_id NUMBER;
3121 l_organization_id NUMBER;
3122 l_wo_status_code NUMBER;
3123 l_msg_count NUMBER;
3124 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3125 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3126 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3127 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3128 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3129 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3130 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3131 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3132 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3133 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3134 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3135 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3136 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3137 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3138 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3139 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3140 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3141 l_out_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3142
3143 l_msg_index_out NUMBER;
3144
3145 l_api_name CONSTANT VARCHAR2(30) := 'Update_EAM_Workorders';
3146 l_api_version CONSTANT NUMBER := 1.0;
3147 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
3148 l_init_msg_list BOOLEAN := TRUE;
3149 l_debug VARCHAR2(1) := 'N';
3150 l_wip_entityids NUMBER;
3151 BEGIN
3152
3153 -- Standard call to check for call compatibility
3154 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
3155 G_PKG_NAME) THEN
3156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3157 END IF;
3158 -- Initialize message list if p_init_msg_list is set to TRUE
3159 IF FND_API.To_Boolean(p_init_msg_list) THEN
3160 FND_MSG_PUB.Initialize;
3161 END IF;
3162
3163 -- Initialize API return status to success
3164 x_return_status := FND_API.G_RET_STS_SUCCESS;
3165
3166 IF G_DEBUG='Y' THEN
3167 AHL_DEBUG_PUB.enable_debug;
3168 AHL_DEBUG_PUB.debug('Entering Update_EAM_Workorders API with WIP Entity ID: '|| p_wip_entity_id ||
3169 ' Instance ID: ' || p_instance_id);
3170 END IF;
3171
3172 OPEN get_workorder_dtls_csr(p_wip_entity_id);
3173 FETCH get_workorder_dtls_csr INTO l_workorder_id,
3174 l_item_id,
3175 l_organization_id,
3176 l_wo_status_code ;
3177 CLOSE get_workorder_dtls_csr;
3178 IF G_DEBUG='Y' THEN
3179 AHL_DEBUG_PUB.enable_debug;
3180 AHL_DEBUG_PUB.debug('l_organization_id ::' || l_organization_id ||
3181 'l_item_id :: ' || l_item_id || 'l_wo_status_code :: ' || l_wo_status_code);
3182 END IF;
3183
3184 l_eam_wo_rec.wip_entity_id := p_wip_entity_id;
3185 l_eam_wo_rec.organization_id := l_organization_id;
3186 l_eam_wo_rec.rebuild_item_id := l_item_id;
3187 l_eam_wo_rec.maintenance_object_id := p_instance_id;
3188 l_eam_wo_rec.status_type := TO_NUMBER(l_wo_status_code);
3189 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
3190
3191 -- Invoke EAM BO API for Updating the Job
3192 EAM_PROCESS_WO_PUB.process_wo
3193 (
3194 p_bo_identifier => l_bo_identifier,
3195 p_api_version_number => 1.0,
3196 p_init_msg_list => l_init_msg_list,
3197 p_commit => FND_API.G_FALSE,
3198 p_eam_wo_rec => l_eam_wo_rec,
3199 p_eam_op_tbl => l_eam_op_tbl,
3200 p_eam_op_network_tbl => l_eam_op_network_tbl,
3201 p_eam_res_tbl => l_eam_res_tbl,
3202 p_eam_res_inst_tbl => l_eam_res_inst_tbl,
3203 p_eam_sub_res_tbl => l_eam_sub_res_tbl,
3204 p_eam_res_usage_tbl => l_eam_res_usage_tbl,
3205 p_eam_mat_req_tbl => l_eam_mat_req_tbl,
3206 p_eam_direct_items_tbl => l_eam_direct_items_tbl,
3207 x_eam_wo_rec => l_out_eam_wo_rec,
3208 x_eam_op_tbl => l_out_eam_op_tbl,
3209 x_eam_op_network_tbl => l_out_eam_op_network_tbl,
3210 x_eam_res_tbl => l_out_eam_res_tbl,
3211 x_eam_res_inst_tbl => l_out_eam_res_inst_tbl,
3212 x_eam_sub_res_tbl => l_out_eam_sub_res_tbl,
3213 x_eam_res_usage_tbl => l_out_eam_res_usage_tbl,
3214 x_eam_mat_req_tbl => l_out_eam_mat_req_tbl,
3215 x_eam_direct_items_tbl => l_out_eam_direct_items_tbl,
3216 x_return_status => l_return_status,
3217 x_msg_count => l_msg_count,
3218 p_debug => l_debug,
3219 p_output_dir => l_output_dir,
3220 p_debug_filename => l_debug_filename,
3221 p_debug_file_mode => l_debug_file_mode
3222 );
3223
3224
3225 IF (l_log_statement >= l_log_current_level) THEN
3226 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'After calling EAM_PROCESS_WO_PUB.'
3227 || 'process_wo');
3228 END IF;
3229
3230 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3231 IF (l_log_statement >= l_log_current_level) THEN
3232 FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Error occured during setting instance '
3233 || 'for EAM workorders');
3234 END IF;
3235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3236 END IF;
3237
3238 -- Standard check of p_commit
3239 IF FND_API.To_Boolean(p_commit) THEN
3240 COMMIT;
3241 END IF;
3242
3243 -- Error Handling
3244 EXCEPTION
3245 WHEN FND_API.G_EXC_ERROR THEN
3246 x_return_status := FND_API.G_RET_STS_ERROR;
3247 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3248 p_data => x_msg_data,
3249 P_ENCODED => FND_API.G_FALSE);
3250
3251 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3252 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3253 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3254 P_DATA => X_MSG_DATA,
3255 p_encoded => fnd_api.g_false);
3256
3257 WHEN OTHERS THEN
3258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3259 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3260 p_procedure_name => 'Update_EAM_Workorders',
3261 p_error_text => SQLERRM);
3262 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3263 p_data => x_msg_data,
3264 p_encoded => fnd_api.g_false);
3265 END Update_EAM_Workorders;
3266 --------------------------------------------------------------------------------------------------------------
3267
3268 -------------------------------------------------------------------------------------------------------
3269 -- Start of Comments
3270 -- Procedure name : Validate_Rtn_Rec
3271 -- Type : Private
3272 -- Function : This procedure will validate Material Return Record
3273 -- Pre-reqs :
3274 -- Parameters :
3275 --
3276 -- Validate_Rec Parameters
3277 -- p_x_ahl_mtlrtn_rec IN OUT Material Return Record Required
3278 --
3279 -- End of Comments
3280 -------------------------------------------------------------------------------------------------------
3281 PROCEDURE Validate_Rtn_Rec
3282 (
3283 p_x_ahl_mtlrtn_rec IN OUT NOCOPY Ahl_MtlRtn_Rec_Type,
3284 x_return_status OUT NOCOPY VARCHAR2,
3285 x_msg_count OUT NOCOPY NUMBER,
3286 x_msg_data OUT NOCOPY VARCHAR2
3287
3288 )
3289 IS
3290
3291 CURSOR Get_Srl_Ctrl_Code(p_org_id NUMBER, p_item_id NUMBER)
3292 IS
3293 SELECT serial_number_control_code
3294 FROM MTL_SYSTEM_ITEMS_KFV
3295 WHERE organization_id = p_org_id
3296 AND inventory_item_id = p_item_id ;
3297
3298 l_return_status VARCHAR2(10);
3299 l_msg_count NUMBER;
3300 l_msg_data VARCHAR2(240);
3301
3302 l_result VARCHAR2(1);
3303 l_srl_ctrl_code NUMBER;
3304
3305
3306 BEGIN
3307
3308 x_return_status := FND_API.G_RET_STS_SUCCESS;
3309
3310 IF G_DEBUG='Y' THEN
3311 AHL_DEBUG_PUB.enable_debug;
3312 AHL_DEBUG_PUB.debug('Entering Validate_Rtn_Rec Procedure');
3313 END IF;
3314
3315 -- Return Quantity should be +ve always.
3316 IF G_DEBUG='Y' THEN
3317 AHL_DEBUG_PUB.debug('Validating quantity...['||to_Char(p_x_ahl_mtlrtn_rec.Return_Qty)||']');
3318 END IF;
3319
3320 IF p_x_ahl_mtlrtn_rec.Return_Qty IS NULL THEN
3321 FND_MESSAGE.Set_Name('AHL','AHL_CMP_RTN_QTY_NULL'); -- Return quantity cannot be null.
3322 FND_MSG_PUB.ADD;
3323 x_return_status := FND_API.G_RET_STS_ERROR;
3324 ELSIF p_x_ahl_mtlrtn_rec.Return_Qty <= 0 THEN
3325 FND_MESSAGE.Set_Name('AHL','AHL_CMP_INVALID_RTN_QTY'); -- Invalid Return quantity specified.
3326 FND_MESSAGE.Set_Token('QUANTITY',p_x_ahl_mtlrtn_rec.Return_Qty);
3327 FND_MSG_PUB.ADD;
3328 x_return_status := FND_API.G_RET_STS_ERROR;
3329 END IF;
3330
3331 IF p_x_ahl_mtlrtn_rec.Instance_Qty IS NOT NULL THEN
3332 IF p_x_ahl_mtlrtn_rec.Return_Qty > p_x_ahl_mtlrtn_rec.Instance_Qty THEN
3333 FND_MESSAGE.Set_Name('AHL','AHL_CMP_RTNQTY_GRTR_INSTQTY'); -- Return quantity cannot be greater than instance quantity.
3334 FND_MSG_PUB.ADD;
3335 x_return_status := FND_API.G_RET_STS_ERROR;
3336 END IF;
3337 END IF;
3338
3339 IF p_x_ahl_mtlrtn_rec.Return_Condition IS NULL THEN
3340 FND_MESSAGE.Set_Name('AHL','AHL_CMP_RET_COND_NULL'); -- Condition cannot be null.
3341 FND_MSG_PUB.ADD;
3342 x_return_status := FND_API.G_RET_STS_ERROR;
3343 END IF;
3344
3345 IF p_x_ahl_mtlrtn_rec.Dst_SubInv_Name IS NULL THEN
3346 FND_MESSAGE.Set_Name('AHL','AHL_CMP_SUBINV_NULL'); -- Sub Inventory cannot be null.
3347 FND_MSG_PUB.ADD;
3348 x_return_status := FND_API.G_RET_STS_ERROR;
3349 END IF;
3350
3351 IF (p_x_ahl_mtlrtn_rec.Dst_Locator_Id IS NULL AND p_x_ahl_mtlrtn_rec.Dst_Locator_Segments IS NULL) THEN
3352 FND_MESSAGE.Set_Name('AHL','AHL_CMP_LOCATOR_NULL'); -- Locator cannot be null.
3353 FND_MSG_PUB.ADD;
3354 x_return_status := FND_API.G_RET_STS_ERROR;
3355 END IF;
3356
3357
3358 l_result := AHL_CMP_UTIL_PKG.Are_All_Workorders_Complete(p_x_ahl_mtlrtn_rec.repair_batch_name, p_x_ahl_mtlrtn_rec.Instance_Id , p_x_ahl_mtlrtn_rec.Organization_Id);
3359
3360 -- If all the workorders completed is false and Return Qty equals Instance Qty.
3361 IF l_result <> 'Y' AND p_x_ahl_mtlrtn_rec.Return_Qty = p_x_ahl_mtlrtn_rec.Instance_Qty THEN
3362 OPEN Get_Srl_Ctrl_Code(p_x_ahl_mtlrtn_rec.Organization_Id,p_x_ahl_mtlrtn_rec.Inventory_Item_Id);
3363 FETCH Get_Srl_Ctrl_Code INTO l_srl_ctrl_code;
3364 CLOSE Get_Srl_Ctrl_Code;
3365
3366 IF (nvl(l_srl_ctrl_code,0) <> nvl(C_NO_SERIAL_CONTROL,0) ) THEN --Serialized Item
3367 FND_MESSAGE.Set_Name('AHL','AHL_CMP_CANNOT_RETURN'); -- Cannot perform return as there exists open workorders associated to the instance
3368 FND_MESSAGE.Set_Token('INSTANCE',p_x_ahl_mtlrtn_rec.Instance_Id);
3369 FND_MSG_PUB.ADD;
3370 x_return_status := FND_API.G_RET_STS_ERROR;
3371 ELSE
3372 FND_MESSAGE.Set_Name('AHL','AHL_CMP_CANNOT_RTN_FULL_QTY'); -- Cannot return complete quantity as there exists open workorders associated to the instance
3373 FND_MESSAGE.Set_Token('INSTANCE',p_x_ahl_mtlrtn_rec.Instance_Id);
3374 FND_MSG_PUB.ADD;
3375 x_return_status := FND_API.G_RET_STS_ERROR;
3376 END IF;
3377 END IF;
3378
3379 IF G_DEBUG='Y' THEN
3380 AHL_DEBUG_PUB.debug('after all the validations, ret_status['|| x_return_status || ']');
3381 END IF;
3382
3383
3384 END Validate_Rtn_Rec;
3385 -----------------------------------------------------------------------------------------------------------------
3386
3387 ---------------------------------------------------------------------------------------------------------
3388 -- Start of Comments
3389 -- Procedure name : Perform_Return_Txn
3390 -- Type : Public
3391 -- Function : This procedure will transfer materials from Planning/In-Repair Locators of a Repair Batch
3392 -- to a Serviceable/Un-Serviceable Sub Inventory.
3393 -- Pre-reqs :
3394 -- Parameters :
3395 --
3396 -- Perform_Return_Txn Parameters
3397 -- p_x_ahl_mtlrtn_tbl IN Table of Material Return Records Required
3398 --
3399 -- End of Comments
3400 ----------------------------------------------------------------------------------------------------------
3401 PROCEDURE PERFORM_RETURN_TXN
3402 (
3403 p_api_version IN NUMBER := 1.0,
3404 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3405 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3406 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3407 p_x_ahl_mtlrtn_tbl IN OUT NOCOPY Ahl_MtlRtn_Tbl_Type,
3408 x_return_status OUT NOCOPY VARCHAR2,
3409 x_msg_count OUT NOCOPY NUMBER,
3410 x_msg_data OUT NOCOPY VARCHAR2
3411 )
3412 IS
3413 l_api_name CONSTANT VARCHAR2(30) := 'PERFORM_RETURN_TXN';
3414 l_api_version CONSTANT NUMBER := 1.0;
3415 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3416 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||l_full_name;
3417
3418 l_error BOOLEAN;
3419 l_ahl_mtlxfr_tbl Ahl_Mtlxfr_Tbl_Type;
3420 l_remaining_qty NUMBER;
3421
3422 l_workorder_name AHL_WORKORDERS.WORKORDER_NAME%TYPE;
3423 l_visit_task_id NUMBER;
3424
3425 -- Cursor to retrieve all the open workorders associated to a given instance under a Repair Batch.
3426 CURSOR Get_Open_Workorders(c_repair_batch_id NUMBER, c_ins_id NUMBER)
3427 IS
3428 SELECT workorder_name, visit_task_id
3429 FROM AHL_WORKORDERS
3430 WHERE status_code IN ('1', '3', '6', '17', '19', '20' ) -- unreleased, released, on-hold, draft, parts hold, pending QA (Open WO Statuses)
3431 AND visit_task_id IN
3432 (SELECT visit_task_id
3433 FROM AHL_VISIT_TASKS_B vt
3434 WHERE vt.cost_parent_id IS NOT NULL
3435 AND vt.instance_id = c_ins_id
3436 START WITH vt.visit_task_id = c_repair_batch_id
3437 CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id );
3438
3439 -- pdoki added for CMP-Marshaling Integration, Start.
3440 CURSOR Get_RTS_WO_Details( c_repair_batch VARCHAR2, c_ins_id NUMBER)
3441 IS
3442 SELECT wo.workorder_id,wo.workorder_name,wo.wip_entity_id,wdj.REBUILD_ITEM_ID,wdj.MAINTENANCE_OBJECT_ID
3443 FROM AHL_WORKORDERS WO,
3444 WIP_DISCRETE_JOBS WDJ
3445 WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
3446 and wo.status_code NOT IN ('7','12','17','22' ) -- Cancelled,Closed,Draft and Deleted
3447 AND VISIT_TASK_ID IN
3448 ( SELECT vt.visit_task_id
3449 FROM AHL_VISIT_TASKS_B vt
3450 WHERE vt.cost_parent_id IS NOT NULL
3451 AND nvl(vt.return_to_supply_flag,'N') = 'Y'
3452 AND vt.instance_id = nvl(c_ins_id,vt.instance_id)
3453 START WITH vt.visit_task_id = (SELECT visit_task_id
3454 FROM AHL_VISIT_TASKS_B
3455 WHERE REPAIR_BATCH_NAME = C_REPAIR_BATCH)
3456 CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id );
3457
3458 l_rts_wo_dtls_rec Get_RTS_WO_Details%ROWTYPE;
3459
3460 CURSOR Check_WO_WO_Resv_Exists( c_wip_entity_id NUMBER)
3461 IS
3462 SELECT RESERVATION_ID,
3463 DEMAND_SOURCE_LINE_DETAIL,
3464 DEMAND_SOURCE_HEADER_ID,
3465 SUPPLY_SOURCE_HEADER_ID,
3466 PRIMARY_RESERVATION_QUANTITY
3467 FROM MTL_RESERVATIONS
3468 WHERE SUPPLY_SOURCE_TYPE_ID = 5
3469 AND DEMAND_SOURCE_TYPE_ID = 5
3470 AND SUPPLY_SOURCE_HEADER_ID = c_wip_entity_id ;
3471
3472 l_resv_rec Check_WO_WO_Resv_Exists%ROWTYPE;
3473 l_reservable NUMBER;
3474 l_wo_wo_resv_flag BOOLEAN;
3475 l_del_flag BOOLEAN;
3476 l_create_flag BOOLEAN;
3477
3478 l_rsv_rec AHL_MM_RESERVATIONS_PVT.mtl_reservation_rec_type;
3479 l_serial_number_tbl AHL_MM_RESERVATIONS_PVT.serial_number_tbl_type;
3480 l_x_serial_number_tbl AHL_MM_RESERVATIONS_PVT.number_table_type;
3481 -- pdoki added for CMP-Marshaling Integration, End.
3482
3483 -- pdoki added for Bug 13721975, Start.
3484 CURSOR Get_Child_Summary_Tasks( c_repair_batch VARCHAR2, c_ins_id NUMBER)
3485 IS
3486 SELECT vt.visit_task_id
3487 FROM AHL_VISIT_TASKS_B vt
3488 WHERE vt.cost_parent_id IS NOT NULL
3489 AND vt.originating_task_id IS NULL
3490 AND vt.task_type_code = 'SUMMARY'
3491 AND vt.status_code = 'PLANNING'
3492 AND vt.instance_id = c_ins_id
3493 START WITH vt.visit_task_id = ( SELECT visit_task_id
3494 FROM AHL_VISIT_TASKS_B
3495 WHERE repair_batch_name = c_repair_batch )
3496 CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id;
3497 -- pdoki added for Bug 13721975, End.
3498
3499
3500
3501 BEGIN
3502 -- Standard start of API savepoint
3503 SAVEPOINT PERFORM_RETURN_TXN_PVT;
3504
3505 -- Standard call to check for call compatibility
3506 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
3507 G_PKG_NAME) THEN
3508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3509 END IF;
3510 -- Initialize message list if p_init_msg_list is set to TRUE
3511 IF FND_API.To_Boolean(p_init_msg_list) THEN
3512 FND_MSG_PUB.Initialize;
3513 END IF;
3514
3515 -- Initialize API return status to success
3516 x_return_status := FND_API.G_RET_STS_SUCCESS;
3517
3518
3519 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3520 fnd_log.string(l_log_statement,l_debug,'Entering Perform Return Txn API');
3521 END IF;
3522
3523
3524 IF (p_x_ahl_mtlrtn_tbl.COUNT > 0) THEN
3525
3526 FOR i IN p_x_ahl_mtlrtn_tbl.FIRST..p_x_ahl_mtlrtn_tbl.LAST LOOP
3527
3528 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3529 fnd_log.string(l_log_statement,l_debug,'Inputs to this API:: For Rec[' || i || ']');
3530 fnd_log.string(l_log_statement,l_debug,'Repair_Batch_Id - ' || p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Id);
3531 fnd_log.string(l_log_statement,l_debug,'Repair_Batch_Name - ' || p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Name);
3532 fnd_log.string(l_log_statement,l_debug,'Inventory_Item_Id - ' || p_x_ahl_mtlrtn_tbl(i).Inventory_Item_Id);
3533 fnd_log.string(l_log_statement,l_debug,'Revision - ' || p_x_ahl_mtlrtn_tbl(i).Revision);
3534 fnd_log.string(l_log_statement,l_debug,'Organization_Id - ' || p_x_ahl_mtlrtn_tbl(i).Organization_Id);
3535 fnd_log.string(l_log_statement,l_debug,'Src_Subinv_Name - ' || p_x_ahl_mtlrtn_tbl(i).Src_Subinv_Name);
3536 fnd_log.string(l_log_statement,l_debug,'Src_Locator_Id - ' || p_x_ahl_mtlrtn_tbl(i).Src_Locator_Id);
3537 fnd_log.string(l_log_statement,l_debug,'Dst_SubInv_Name - ' || p_x_ahl_mtlrtn_tbl(i).Dst_SubInv_Name);
3538 fnd_log.string(l_log_statement,l_debug,'Dst_Locator_Id - ' || p_x_ahl_mtlrtn_tbl(i).Dst_Locator_Id);
3539 fnd_log.string(l_log_statement,l_debug,'Dst_Locator_Segments - ' || p_x_ahl_mtlrtn_tbl(i).Dst_Locator_Segments);
3540 fnd_log.string(l_log_statement,l_debug,'Instance_Qty - ' || p_x_ahl_mtlrtn_tbl(i).Instance_Qty);
3541 fnd_log.string(l_log_statement,l_debug,'Return_Qty - ' || p_x_ahl_mtlrtn_tbl(i).Return_Qty);
3542 fnd_log.string(l_log_statement,l_debug,'Uom - ' || p_x_ahl_mtlrtn_tbl(i).Uom);
3543 fnd_log.string(l_log_statement,l_debug,'Serial_Number - ' || p_x_ahl_mtlrtn_tbl(i).Serial_Number);
3544 fnd_log.string(l_log_statement,l_debug,'Lot_Number - ' || p_x_ahl_mtlrtn_tbl(i).Lot_Number);
3545 fnd_log.string(l_log_statement,l_debug,'Instance_Id - ' || p_x_ahl_mtlrtn_tbl(i).Instance_Id);
3546 fnd_log.string(l_log_statement,l_debug,'Return_Condition - ' || p_x_ahl_mtlrtn_tbl(i).Return_Condition);
3547 END IF;
3548
3549
3550 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3551 fnd_log.string(l_log_statement,l_debug,'calling Validate_Rtn_Rec for i=['||i||']');
3552 END IF;
3553
3554 --Call Validate_Rtn_Rec to validate the material return record.
3555 Validate_Rtn_Rec
3556 (
3557 p_x_ahl_mtlrtn_rec => p_x_ahl_mtlrtn_tbl(i),
3558 x_return_status => x_return_status,
3559 x_msg_count => x_msg_count,
3560 x_msg_data => x_msg_data
3561 );
3562
3563 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3564 fnd_log.string(l_log_statement,l_debug,'After calling Validate_Rtn_Rec');
3565 END IF;
3566
3567
3568 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3569 l_error := true;
3570
3571 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3572 fnd_log.string(l_log_statement,l_debug,'Error in Validate_Rtn_Rec');
3573 END IF;
3574 END IF;
3575 END LOOP;
3576
3577 IF (l_Error) THEN
3578 RAISE FND_API.G_EXC_ERROR;
3579 END IF;
3580
3581 -- Calling Perform_Mtl_Xfr api to transfer material from Planning/In-Repair Locator to a Serviceable/Un-Serviceable Sub Inventory.
3582 FOR i IN p_x_ahl_mtlrtn_tbl.FIRST..p_x_ahl_mtlrtn_tbl.LAST LOOP
3583
3584 -- Populate material transfer table
3585 l_ahl_mtlxfr_tbl(0).Inventory_Item_Id := p_x_ahl_mtlrtn_tbl(i).Inventory_Item_Id;
3586 l_ahl_mtlxfr_tbl(0).Revision := p_x_ahl_mtlrtn_tbl(i).Revision;
3587 l_ahl_mtlxfr_tbl(0).Organization_Id := p_x_ahl_mtlrtn_tbl(i).Organization_Id;
3588 l_ahl_mtlxfr_tbl(0).Src_Subinv_Name := p_x_ahl_mtlrtn_tbl(i).Src_Subinv_Name;
3589 l_ahl_mtlxfr_tbl(0).Src_Locator_Id := p_x_ahl_mtlrtn_tbl(i).Src_Locator_Id;
3590 l_ahl_mtlxfr_tbl(0).Dst_SubInv_Name := p_x_ahl_mtlrtn_tbl(i).Dst_SubInv_Name;
3591 l_ahl_mtlxfr_tbl(0).Dst_Locator_Id := p_x_ahl_mtlrtn_tbl(i).Dst_Locator_Id;
3592 l_ahl_mtlxfr_tbl(0).Dst_Locator_Segments := p_x_ahl_mtlrtn_tbl(i).Dst_Locator_Segments;
3593 l_ahl_mtlxfr_tbl(0).Quantity := p_x_ahl_mtlrtn_tbl(i).Return_Qty;
3594 l_ahl_mtlxfr_tbl(0).Uom := p_x_ahl_mtlrtn_tbl(i).Uom;
3595 L_AHL_MTLXFR_TBL(0).TRANSACTION_TYPE_ID := 67 ; -- For Project Transfers
3596 l_ahl_mtlxfr_tbl(0).Transaction_Reference := 'AHL - ' || p_x_ahl_mtlrtn_tbl(i).Dst_SubInv_Name ;
3597 l_ahl_mtlxfr_tbl(0).Serial_Number := p_x_ahl_mtlrtn_tbl(i).Serial_Number;
3598 l_ahl_mtlxfr_tbl(0).Lot_Number := p_x_ahl_mtlrtn_tbl(i).Lot_Number;
3599
3600 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3601 fnd_log.string(l_log_statement,l_debug,'Calling Perform_Mtl_Xfr API...');
3602 END IF;
3603
3604 Perform_Mtl_Xfr
3605 (
3606 p_x_ahl_mtlxfr_tbl => l_ahl_mtlxfr_tbl,
3607 x_return_status => x_return_status,
3608 x_msg_count => x_msg_count,
3609 x_msg_data => x_msg_data
3610 );
3611
3612 IF (l_log_statement >= l_log_current_level) THEN
3613 FND_LOG.string(l_log_statement, l_debug, 'After calling Perform_Mtl_Xfr API...ret_status['||x_return_status||']');
3614 END IF;
3615
3616 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3617 RAISE FND_API.G_EXC_ERROR;
3618 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3620 END IF;
3621
3622
3623 -- For Partial Qty Returns, all the Open Workorders associated to the instance are updated with the remaining instance qty.
3624 IF p_x_ahl_mtlrtn_tbl(i).Return_Qty < p_x_ahl_mtlrtn_tbl(i).Instance_Qty THEN
3625 l_remaining_qty := p_x_ahl_mtlrtn_tbl(i).Instance_Qty - p_x_ahl_mtlrtn_tbl(i).Return_Qty;
3626
3627 OPEN Get_Open_Workorders(p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Id, p_x_ahl_mtlrtn_tbl(i).Instance_Id);
3628 LOOP
3629 FETCH Get_Open_Workorders INTO l_workorder_name, l_visit_task_id;
3630 EXIT WHEN Get_Open_Workorders%NOTFOUND;
3631
3632 UPDATE AHL_VISIT_TASKS_B
3633 SET quantity = l_remaining_qty
3634 WHERE visit_task_id = l_visit_task_id ;
3635
3636 IF (l_log_statement >= l_log_current_level) THEN
3637 FND_LOG.string(l_log_statement, l_debug, 'Workorder - ' || l_workorder_name || ' is updated with Quantity ::' || l_remaining_qty );
3638 END IF;
3639 END LOOP;
3640 END IF;
3641
3642 -- Based on the Return Condition, update Return Serviceable/Return Un-Serviceable Qty fields of the Repair Batch summary task.
3643 IF ( p_x_ahl_mtlrtn_tbl(i).Return_Condition = G_AHL_UNSERVICEABLE_CONDITION OR p_x_ahl_mtlrtn_tbl(i).Return_Condition = G_AHL_MRB_CONDITION ) THEN
3644 UPDATE AHL_VISIT_TASKS_B
3645 SET RET_UNSERVICEABLE_QTY = NVL(RET_UNSERVICEABLE_QTY,0) + P_X_AHL_MTLRTN_TBL(I).RETURN_QTY
3646 WHERE visit_task_id = p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Id;
3647
3648 ELSE
3649 UPDATE AHL_VISIT_TASKS_B
3650 SET RET_SERVICEABLE_QTY = NVL(RET_SERVICEABLE_QTY,0) + P_X_AHL_MTLRTN_TBL(I).RETURN_QTY
3651 WHERE visit_task_id = p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Id;
3652
3653 END IF;
3654
3655 -- for serialized instances, update instance condition.
3656 IF (p_x_ahl_mtlrtn_tbl(i).Serial_Number) IS NOT NULL THEN
3657 AHL_PRD_PARTS_CHANGE_PVT.Update_Part_Condition
3658 (
3659 p_init_msg_list => FND_API.G_FALSE,
3660 p_commit => FND_API.G_FALSE,
3661 p_instance_id => p_x_ahl_mtlrtn_tbl(i).Instance_Id,
3662 p_instance_condition_id => p_x_ahl_mtlrtn_tbl(i).Return_Condition,
3663 x_return_status => x_return_status,
3664 x_msg_data => x_msg_data,
3665 x_msg_count => x_msg_count
3666 );
3667
3668 IF (l_log_statement >= l_log_current_level) THEN
3669 FND_LOG.string(l_log_statement, l_debug, 'after PartsChange Part-Condition-Update api:ret status=['||x_return_status||']');
3670 END IF;
3671
3672 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3673 RAISE FND_API.G_EXC_ERROR;
3674 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3676 END IF;
3677
3678 END IF; -- serial number
3679
3680 -- pdoki added for CMP-Marshaling Integration, Start.
3681 l_wo_wo_resv_flag := false;
3682 l_del_flag := false;
3683 l_create_flag := false;
3684
3685 -- For Serialized instances, if WO-WO reservation exists then transfer it to INV-WO reservation
3686 IF (p_x_ahl_mtlrtn_tbl(i).Serial_Number IS NOT NULL ) THEN
3687 IF ( AHL_CMP_UTIL_PKG.Check_Rts_Workorder_Exists(p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Name, p_x_ahl_mtlrtn_tbl(i).Instance_Id) = 'Y' ) THEN
3688
3689 OPEN Get_RTS_WO_Details(p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Name, p_x_ahl_mtlrtn_tbl(i).Instance_Id);
3690 FETCH Get_RTS_WO_Details INTO l_rts_wo_dtls_rec;
3691 CLOSE Get_RTS_WO_Details;
3692
3693 IF (l_log_statement >= l_log_current_level) THEN
3694 FND_LOG.string(l_log_statement, l_debug, 'RTS Workorder Details - ' || 'Workorder Name: ' || l_rts_wo_dtls_rec.workorder_name || ' Wip Entity ID: ' || l_rts_wo_dtls_rec.wip_entity_id );
3695 END IF;
3696
3697 IF ( p_x_ahl_mtlrtn_tbl(i).Inventory_Item_Id = l_rts_wo_dtls_rec.REBUILD_ITEM_ID AND p_x_ahl_mtlrtn_tbl(i).Instance_Id = l_rts_wo_dtls_rec.MAINTENANCE_OBJECT_ID ) THEN
3698
3699 IF (l_log_statement >= l_log_current_level) THEN
3700 FND_LOG.string(l_log_statement, l_debug, 'Return Item is same as Workorder Header Item');
3701 END IF;
3702
3703 OPEN Check_WO_WO_Resv_Exists(l_rts_wo_dtls_rec.wip_entity_id);
3704 FETCH Check_WO_WO_Resv_Exists into l_resv_rec;
3705 IF Check_WO_WO_Resv_Exists%FOUND THEN
3706 IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3707 FND_LOG.string(l_log_statement, l_debug, 'WO-WO Resv Exists,Resv ID = ' || l_resv_rec.reservation_id || ' ,SupplySource = ' || l_resv_rec.SUPPLY_SOURCE_HEADER_ID || ' ,DemandSource = ' || l_resv_rec.DEMAND_SOURCE_HEADER_ID);
3708 END IF;
3709 l_wo_wo_resv_flag := true;
3710 END IF;
3711 Close Check_WO_WO_Resv_Exists;
3712
3713 IF (L_WO_WO_RESV_FLAG = TRUE) THEN
3714 IF (p_x_ahl_mtlrtn_tbl(i).Return_Condition = G_AHL_UNSERVICEABLE_CONDITION) THEN
3715 IF (l_log_statement >= l_log_current_level) THEN
3716 FND_LOG.string(l_log_statement, l_debug, 'Return Condition is Unserviceable');
3717 END IF;
3718 l_del_flag := true;
3719
3720 ELSE
3721
3722 SELECT RESERVABLE_TYPE
3723 INTO l_reservable
3724 FROM MTL_SECONDARY_INVENTORIES
3725 WHERE ORGANIZATION_ID = p_x_ahl_mtlrtn_tbl(i).Organization_Id
3726 AND SECONDARY_INVENTORY_NAME = p_x_ahl_mtlrtn_tbl(i).Dst_SubInv_Name ;
3727
3728 IF (l_reservable <> 1) THEN
3729 IF (l_log_statement >= l_log_current_level) THEN
3730 FND_LOG.string(l_log_statement, l_debug, 'Destination SubInventory is not Reservable');
3731 END IF;
3732 l_del_flag := true;
3733 ELSE
3734 l_del_flag := true;
3735 l_create_flag := true;
3736 END IF;
3737 END IF; -- Return Condition
3738
3739 END IF; -- WO-WO Resv exists
3740
3741 IF (l_del_flag = true) THEN
3742 mo_global.init('AHL');
3743
3744 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3745 fnd_log.string(l_log_statement, l_debug,'Calling Delete_Reservation API...');
3746 END IF;
3747 AHL_MM_RESERVATIONS_PVT.DELETE_RESERVATION
3748 (
3749 p_api_version => 1.0,
3750 p_init_msg_list => FND_API.G_FALSE,
3751 p_commit => FND_API.G_FALSE,
3752 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3753 p_module_type => null,
3754 x_return_status => x_return_status,
3755 x_msg_count => x_msg_count,
3756 x_msg_data => x_msg_data,
3757 p_reservation_id => l_resv_rec.reservation_id
3758 );
3759
3760 IF (l_log_statement >= l_log_current_level) THEN
3761 FND_LOG.string(l_log_statement, l_debug, 'After Delete_Reservation API...ret_status['||x_return_status||']');
3762 END IF;
3763
3764 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3765 RAISE FND_API.G_EXC_ERROR;
3766 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3767 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3768 END IF;
3769
3770 END IF;
3771
3772 IF (l_create_flag = true) THEN
3773
3774 -- Demand Source -----------------------------
3775 l_rsv_rec.demand_source_line_detail := l_resv_rec.demand_source_line_detail; -- asm id
3776
3777 -- Supply Source -----------------------------
3778 l_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
3779 l_rsv_rec.primary_reservation_quantity := l_resv_rec.primary_reservation_quantity;
3780 l_rsv_rec.reservation_id := NULL;
3781
3782 l_serial_number_tbl(1).inventory_item_id := p_x_ahl_mtlrtn_tbl(i).Inventory_Item_Id ;
3783 l_serial_number_tbl(1).serial_number := p_x_ahl_mtlrtn_tbl(i).Serial_Number;
3784
3785 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3786 fnd_log.string(l_log_statement, l_debug,'Calling Process_Reservation API...');
3787 END IF;
3788
3789 AHL_MM_RESERVATIONS_PVT.PROCESS_RESERVATION
3790 (
3791 p_api_version => 1.0,
3792 p_init_msg_list => FND_API.G_FALSE,
3793 p_commit => FND_API.G_FALSE,
3794 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3795 p_module_type => null,
3796 x_return_status => x_return_status,
3797 x_msg_count => x_msg_count,
3798 x_msg_data => x_msg_data,
3799 p_rsv_rec => l_rsv_rec,
3800 p_serial_number_tbl => l_serial_number_tbl,
3801 x_reservation_id_tbl => l_x_serial_number_tbl
3802 );
3803
3804 IF (l_log_statement >= l_log_current_level) THEN
3805 FND_LOG.string(l_log_statement, l_debug, 'After Process_Reservation API...ret_status['||x_return_status||']');
3806 END IF;
3807
3808 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3809 RAISE FND_API.G_EXC_ERROR;
3810 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3812 END IF;
3813
3814 END IF;
3815
3816 END IF;
3817
3818 END IF; -- RTS WO Exists
3819 END IF; -- Serial Number
3820 -- pdoki added for CMP-Marshaling Integration, End.
3821
3822 -- pdoki added for Bug 13721975, Start.
3823 -- For Serialized and Non-Serialized (only in case of full qty returns) instances, Cancel all the tasks that are in planning status
3824 IF p_x_ahl_mtlrtn_tbl(i).Return_Qty = p_x_ahl_mtlrtn_tbl(i).Instance_Qty THEN
3825 OPEN Get_Child_Summary_Tasks(p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Name, p_x_ahl_mtlrtn_tbl(i).Instance_Id);
3826 LOOP
3827 FETCH Get_Child_Summary_Tasks INTO l_visit_task_id;
3828 EXIT WHEN Get_Child_Summary_Tasks%NOTFOUND;
3829
3830 IF (l_log_statement >= l_log_current_level) THEN
3831 FND_LOG.string(l_log_statement, l_debug, 'Calling AHL_VWP_TASKS_PVT.Delete_Task - ' || 'Summary Task ID: ' || l_visit_task_id );
3832 END IF;
3833
3834 AHL_VWP_TASKS_PVT.Delete_Task
3835 (
3836 p_api_version => 1.0,
3837 p_init_msg_list => FND_API.G_FALSE,
3838 p_commit => FND_API.G_FALSE,
3839 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3840 p_module_type => NULL,
3841 p_Visit_Task_Id => l_visit_task_id,
3842 x_return_status => x_return_status,
3843 x_msg_count => x_msg_count,
3844 x_msg_data => x_msg_data
3845 );
3846
3847 IF (l_log_statement >= l_log_current_level) THEN
3848 FND_LOG.string(l_log_statement, l_debug, 'After Delete_Task API...ret_status['||x_return_status||']');
3849 END IF;
3850
3851 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3852 RAISE FND_API.G_EXC_ERROR;
3853 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3855 END IF;
3856
3857 END LOOP;
3858 CLOSE Get_Child_Summary_Tasks;
3859 END IF;
3860 -- pdoki added for Bug 13721975, End.
3861
3862 END LOOP;
3863 END IF;
3864
3865 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3866 fnd_log.string(l_log_statement, l_debug,'End of Perform Return Txn API');
3867 END IF;
3868
3869 -- Standard check of p_commit
3870 IF FND_API.To_Boolean(p_commit) THEN
3871 COMMIT WORK;
3872 END IF;
3873
3874
3875 EXCEPTION
3876 WHEN FND_API.G_EXC_ERROR THEN
3877 x_return_status := FND_API.G_RET_STS_ERROR;
3878 Rollback to PERFORM_RETURN_TXN_PVT;
3879 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3880 p_data => x_msg_data,
3881 p_encoded => fnd_api.g_false);
3882
3883 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3885 Rollback to PERFORM_RETURN_TXN_PVT;
3886 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3887 p_data => x_msg_data,
3888 p_encoded => fnd_api.g_false);
3889
3890 WHEN OTHERS THEN
3891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3892 Rollback to PERFORM_RETURN_TXN_PVT;
3893 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3894 p_procedure_name => 'Perform_Return_Txn',
3895 p_error_text => SQLERRM);
3896 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3897 p_data => x_msg_data,
3898 p_encoded => fnd_api.g_false);
3899
3900 END PERFORM_RETURN_TXN;
3901 ---------------------------------------------------------------------------------------------------------
3902
3903 END AHL_CMP_PVT;