[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_PARTS_CHANGE_PVT
Source
1 PACKAGE BODY AHL_PRD_PARTS_CHANGE_PVT AS
2 /* $Header: AHLVPPCB.pls 120.12 2008/07/01 01:33:59 sikumar ship $ */
3
4
5 G_DEBUG varchar2(1) := AHL_DEBUG_PUB.is_log_enabled;
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_PRD_PARTS_CHANGE_PVT';
7 G_CSI_LOCATION_TYPE_CODE CONSTANT VARCHAR2(30) := 'CSI_INST_LOCATION_SOURCE_CODE';
8
9 -----------------------------------
10 -- Declare Local Procedures --
11 -----------------------------------
12 -- Default and validate the parameters
13
14 PROCEDURE validate_part_record (
15 p_x_parts_rec In Out Nocopy Ahl_Parts_Rec_type,
16 p_module_type In Varchar2,
17 X_Return_Status Out Nocopy Varchar2);
18
19 -- Convert value to ids
20 PROCEDURE convert_value_to_id(
21 p_x_parts_rec In Out Nocopy Ahl_Parts_Rec_type,
22 X_Return_Status Out Nocopy Varchar2);
23
24 PROCEDURE create_csi_transaction_rec(
25 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
26 x_return_status Out Nocopy Varchar2);
27
28 --For UC processing
29 PROCEDURE Process_UC(
30 p_x_parts_rec In Out Nocopy Ahl_Parts_Rec_type,
31 p_module_type In varchar2,
32 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
33 x_return_status Out Nocopy Varchar2,
34 x_path_position_id Out NOCOPY Number,
35 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type);
36
37 --For IB processing
38 PROCEDURE Process_IB(
39 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
40 p_x_parts_rec In Out Nocopy Ahl_Parts_Rec_type,
41 x_return_status Out Nocopy Varchar2);
42
43 --For status update of a removed item after UC or IB processing
44 PROCEDURE update_item_location(
45 p_x_parts_rec In Out Nocopy ahl_parts_rec_type,
46 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
47 x_return_status Out Nocopy Varchar2);
48
49 --For Material transaction api
50 PROCEDURE process_material_txn(
51 p_x_parts_rec In Out Nocopy ahl_parts_rec_type,
52 p_module_type In varchar2,
53 x_return_status Out Nocopy varchar2
54 );
55
56 -- Service request processing
57 /*
58 PROCEDURE process_SR(
59 p_x_parts_rec_tbl In Out Nocopy Ahl_parts_tbl_type,
60 p_module_type In Varchar2,
61 x_return_status Out Nocopy Varchar2
62 );
63
64 */
65
66 -- Get Material Issue transaction, is exists, when installing an item.
67 PROCEDURE Get_Issue_Mtl_Txn (p_workorder_id IN NUMBER,
68 p_Item_Instance_Id IN NUMBER,
69 x_issue_mtl_txn_id OUT NOCOPY NUMBER);
70
71
72 /*
73 -- Update Material Return txn if item returned using Material Transactions.
74 PROCEDURE Update_Material_Return (p_return_mtl_txn_id IN NUMBER,
75 p_workorder_id IN NUMBER,
76 p_Item_Instance_Id IN NUMBER,
77 x_return_status OUT NOCOPY VARCHAR2);
78
79 */
80 /*Validate and get the instance to be moved with ids*/
81 PROCEDURE get_dest_instance_rec(
82 p_module_type In Varchar2 Default NULL,
83 p_move_item_instance_rec IN move_item_instance_rec_type,
84 x_instance_rec Out NOCOPY csi_datastructures_pub.instance_rec,
85 x_serialized Out NOCOPY Varchar2,
86 x_Return_Status Out NOCOPY Varchar2
87 );
88 /*Change the location of item instance from one wip job to another */
89 PROCEDURE update_csi_item_instance(
90 p_instance_rec IN csi_datastructures_pub.instance_rec,
91 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
92 X_Return_Status Out NOCOPY Varchar2
93 );
94 /*Change the location of item instance(non serialized) from one wip job to another
95 Does splitting if needed */
96 PROCEDURE move_nonser_instance(
97 p_source_instance_id IN NUMBER,
98 p_move_quantity IN NUMBER,
99 p_dest_wip_job_id IN NUMBER,
100 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
101 X_Return_Status Out NOCOPY Varchar2
102 );
103 /*Create a new instance if needed if a non-serialized item is split
104 and if it does not exist at destination wip job*/
105 PROCEDURE create_similar_instance(
106 p_source_instance_id IN NUMBER,
107 p_dest_quantity IN NUMBER,
108 p_dest_wip_job_id IN NUMBER,
109 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
110 x_dest_instance_id OUT NOCOPY NUMBER,
111 x_return_status OUT NOCOPY VARCHAR2
112 );
113 --------------------------------------
114 -- End Local Procedures Declaration --
115 --------------------------------------
116
117
118 Procedure process_part (
119 P_API_Version In Number:= 1.0,
120 P_Init_Msg_List In Varchar2:= Fnd_API.G_False,
121 P_Commit In Varchar2:= Fnd_API.G_False,
122 P_Validation_Level In Number:= Fnd_API.G_Valid_Level_Full,
123 p_module_type In VarChar2 := NULL,
124 p_default In Varchar2 := FND_API.G_TRUE,
125 p_x_parts_rec_tbl In Out NOCOPY Ahl_Parts_tbl_type,
126 X_Error_Code Out NOCOPY Varchar2,
127 X_Return_Status Out NOCOPY Varchar2,
128 X_Msg_Count Out NOCOPY Number,
129 X_Msg_Data Out NOCOPY Varchar2,
130 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type )
131 IS
132 --
133 l_api_name CONSTANT VARCHAR2(30) := 'Process_Part_Change';
134 l_api_version CONSTANT NUMBER := 1.0;
135 l_msg_count number;
136 l_row_id number;
137 l_ahl_mtltxn_id number;
138 l_csi_transaction_rec CSI_DATASTRUCTURES_PUB.transaction_rec;
139 l_path_position_id NUMBER;
140
141 -- Added for Post 11.5.10 Enh.
142 l_Issue_Mtl_Txn_id NUMBER;
143 l_part_change_type VARCHAR2(1);
144 l_part_change_qty NUMBER;
145
146
147
148 --
149 Begin
150
151 -- Standard start of API savepoint
152 Savepoint perform_part_changes_pvt;
153
154 -- Standard call to check for call compatibility
155 IF NOT FND_API.Compatible_API_Call(l_api_version,
156 p_api_version,
157 l_api_name,
158 G_PKG_NAME) THEN
159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
160 END IF;
161
162 -- Initialize message list if p_init_msg_list is set to TRUE
163 IF FND_API.To_Boolean(p_init_msg_list) THEN
164 FND_MSG_PUB.Initialize;
165 END IF;
166
167 -- Initialize API return status to success
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 -- Enable Debug.
171 IF G_DEBUG = 'Y' THEN
172 AHL_DEBUG_PUB.enable_debug;
173 END IF;
174
175 -- Add debug mesg.
176 IF G_DEBUG = 'Y' THEN
177 AHL_DEBUG_PUB.debug('Begin private API:' || G_PKG_NAME || '.' || l_api_name);
178 END IF;
179
180 --Loop through the table record
181 IF ( p_x_parts_rec_tbl.COUNT > 0) THEN
182 FOR i IN p_x_parts_rec_tbl.FIRST..p_x_parts_rec_tbl.LAST LOOP
183 IF G_DEBUG = 'Y' THEN
184 AHL_DEBUG_PUB.debug('Input parameter dump for record:'|| i);
185 AHL_DEBUG_PUB.debug('Input Workorder_Id:' || p_x_parts_rec_tbl(i).Workorder_Id);
186 AHL_DEBUG_PUB.debug('Input Operation_Sequence_Num:'|| p_x_parts_rec_tbl(i).Operation_Sequence_Num );
187 AHL_DEBUG_PUB.debug('Input workorder_operation_id:'|| p_x_parts_rec_tbl(i).workorder_operation_id);
188 AHL_DEBUG_PUB.debug('Input Unit_Config_Header_Id:'|| p_x_parts_rec_tbl(i).Unit_Config_Header_Id);
189 AHL_DEBUG_PUB.debug('Input Unit_Config_Name:'|| p_x_parts_rec_tbl(i).Unit_Config_Name);
190 AHL_DEBUG_PUB.debug('Input Unit_config_obj_ver_num:' || p_x_parts_rec_tbl(i).Unit_config_obj_ver_num);
191 AHL_DEBUG_PUB.debug('Input Mc_Relationship_Id:' || p_x_parts_rec_tbl(i).Mc_Relationship_Id);
192 AHL_DEBUG_PUB.debug('Input Installed_Instance_Id:' || p_x_parts_rec_tbl(i).Installed_Instance_Id);
193 AHL_DEBUG_PUB.debug('Input Installed_Instance_Num:' || p_x_parts_rec_tbl(i).Installed_Instance_Num);
194 AHL_DEBUG_PUB.debug('Input Installation_date:' || p_x_parts_rec_tbl(i).Installation_date);
195 AHL_DEBUG_PUB.debug('Input Parent_Installed_Instance_Id:' || p_x_parts_rec_tbl(i).Parent_Installed_Instance_Id);
196 AHL_DEBUG_PUB.debug('Input Parent_Installed_Instance_Num:' || p_x_parts_rec_tbl(i).Parent_Installed_Instance_Num);
197 AHL_DEBUG_PUB.debug('Input Removed_Instance_Id:' || p_x_parts_rec_tbl(i).Removed_Instance_Id);
198 AHL_DEBUG_PUB.debug('Input Removed_Instance_Num:' || p_x_parts_rec_tbl(i).Removed_Instance_Num);
199 AHL_DEBUG_PUB.debug('Input Removal_Code:' || p_x_parts_rec_tbl(i).Removal_Code);
200 AHL_DEBUG_PUB.debug('Input Removal_Meaning:'|| p_x_parts_rec_tbl(i).Removal_Meaning);
201 AHL_DEBUG_PUB.debug('Input Removal_Reason_Id:'|| p_x_parts_rec_tbl(i).Removal_Reason_Id);
202 AHL_DEBUG_PUB.debug('Input Removal_Reason_Name:' || p_x_parts_rec_tbl(i).Removal_Reason_Name);
203 AHL_DEBUG_PUB.debug('Input Removal_Date:' || p_x_parts_rec_tbl(i).Removal_Date);
204 --AHL_DEBUG_PUB.debug('Input Condition_Id:' || p_x_parts_rec_tbl(i).Condition_Id);
205 --AHL_DEBUG_PUB.debug('Input Locator_id:'|| p_x_parts_rec_tbl(i).Locator_id);
206 --AHL_DEBUG_PUB.debug('Input Locator_code:'|| p_x_parts_rec_tbl(i).Locator_code);
207 --AHL_DEBUG_PUB.debug('Input Subinventory_code:'|| p_x_parts_rec_tbl(i).Subinventory_code);
208 --AHL_DEBUG_PUB.debug('Input Severity_id:' || p_x_parts_rec_tbl(i).Severity_id);
209 AHL_DEBUG_PUB.debug('Input Csi_II_Relationship_Id:' || p_x_parts_rec_tbl(i).Csi_II_Relationship_Id);
210 AHL_DEBUG_PUB.debug('Input CSI_II_OBJECT_VERSION_NUM:' || p_x_parts_rec_tbl(i).CSI_II_OBJECT_VERSION_NUM);
211 --AHL_DEBUG_PUB.debug('Input Target_Visit_Num:'|| p_x_parts_rec_tbl(i).Target_Visit_Num);
212 --AHL_DEBUG_PUB.debug('Input Target_Visit_Id:' || p_x_parts_rec_tbl(i).Target_Visit_Id);
213 --AHL_DEBUG_PUB.debug('Input Problem_Code:' || p_x_parts_rec_tbl(i).Problem_Code);
214 --AHL_DEBUG_PUB.debug('Input Problem_Meaning:'|| p_x_parts_rec_tbl(i).Problem_Meaning);
215 AHL_DEBUG_PUB.debug('Input Operation_Type:' || p_x_parts_rec_tbl(i).Operation_Type);
216 --AHL_DEBUG_PUB.debug('Input Summary:'|| p_x_parts_rec_tbl(i).Summary);
217 --AHL_DEBUG_PUB.debug('Input estimated_duration:'|| p_x_parts_rec_tbl(i).estimated_duration);
218 AHL_DEBUG_PUB.debug('Input Installed_Instance_Obj_Ver_Num:'|| p_x_parts_rec_tbl(i).Installed_Instance_Obj_Ver_Num);
219 AHL_DEBUG_PUB.debug('Input Removed_INSTANCE_OBJ_VER_NUM:'|| p_x_parts_rec_tbl(i).Removed_INSTANCE_OBJ_VER_NUM);
220
221 END IF;
222
223 -- Set ids to null if the caller is jsp else go the id way
224 IF (p_module_type ='JSP' ) THEN
225 --p_x_parts_rec_tbl(i).removed_instance_id := null;
226 p_x_parts_rec_tbl(i).installed_instance_id := null;
227 --p_x_parts_rec_tbl(i).locator_id := null;
228 --p_x_parts_rec_tbl(i).condition_id := null;
229 p_x_parts_rec_tbl(i).removal_reason_id := null;
230 p_x_parts_rec_tbl(i).removal_code := null;
231 --p_x_parts_rec_tbl(i).problem_code:= null;
232
233 END IF;
234
235 -- convert value to ids first
236 IF G_DEBUG = 'Y' THEN
237 AHL_DEBUG_PUB.debug('Calling convert_value_to_id');
238 END IF;
239 Convert_value_to_id(p_x_parts_rec => p_x_parts_rec_tbl(i),
240 X_Return_Status => x_return_status);
241
242 -- Check Error Message stack.
243 l_msg_count := FND_MSG_PUB.count_msg;
244 IF l_msg_count > 0 THEN
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247
248 -- dbms_output.put_line('After convert_value_to_id');
249 IF G_DEBUG = 'Y' THEN
250 AHL_DEBUG_PUB.debug('After convert_value_to_id');
251 -- Perform Validation Checks
252 AHL_DEBUG_PUB.debug('Calling perform validations ');
253 END IF;
254
255 Validate_part_record(
256 p_x_parts_rec => p_x_parts_rec_tbl(i),
257 p_module_type => p_module_type,
258 X_Return_Status => X_Return_Status);
259
260 -- Check Error Message stack.
261 l_msg_count := FND_MSG_PUB.count_msg;
262
263 IF l_msg_count > 0 THEN
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266
267 ------**************Get csi_transaction_rec.
268 Create_csi_transaction_rec(l_csi_transaction_rec,x_return_status);
269 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
270 RAISE FND_API.G_EXC_ERROR;
271 END IF;
272
273 -- Check if config_header_id was provided. If not check if unit_config_name exists.
274 -- If yes then perform UC processing else do IB processing
275
276 -- *********************** UC Processing ********************************
277 IF (p_x_parts_rec_tbl(i).Unit_Config_Header_Id is not null) then
278 -- dbms_output.put_line('Callung UC config');
279 IF G_DEBUG = 'Y' THEN
280 AHL_DEBUG_PUB.debug('Callung UC config');
281 END IF;
282
283 Process_UC(
284 p_x_parts_rec => p_x_parts_rec_tbl(i),
285 p_module_type => p_module_type,
286 p_x_csi_transaction_rec => l_csi_transaction_rec,
287 x_return_status => x_return_status,
288 x_path_position_id => l_path_position_id,
289 x_warning_msg_tbl => x_warning_msg_tbl);
290 IF G_DEBUG = 'Y' THEN
291 AHL_DEBUG_PUB.debug('x_return_status after uc config '|| x_return_status);
292
293 END IF;
294
295
296 -- *********************** IB Processing ********************************
297 ELSE
298 IF G_DEBUG = 'Y' THEN
299 AHL_DEBUG_PUB.debug('IB Processing');
300 END IF;
301 -- dbms_output.put_line('IB processing');
302
303 Process_IB(
304 p_x_csi_transaction_rec => l_csi_transaction_rec,
305 p_x_parts_rec => p_x_parts_rec_tbl(i),
306 x_return_status => x_return_status);
307
308 END IF; -- if l_uc_processing
309
310 -- error checking after UC or IB processing
311 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
312 RAISE FND_API.G_EXC_ERROR;
313 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315 END IF;
316
317
318 ---*********** Update item location to WIP if operation is remove or swap***********---
319
320 /*IF ( p_x_parts_rec_tbl(i).Unit_Config_Header_Id is null AND p_x_parts_rec_tbl(i).operation_type='D' or p_x_parts_rec_tbl(i).operation_type='M') then
321
322 update_item_location(
323 p_x_parts_rec => p_x_parts_rec_tbl(i),
324 p_x_csi_transaction_rec => l_csi_transaction_rec,
325 x_return_status => x_return_status);
326 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
327 RAISE FND_API.G_EXC_ERROR;
328
329
330
331 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
333 END IF;
334
335 END IF;*/
336
337 /* P11.5.10 Enhancement: Material Transaction Functionality is removed from Parts Change.
338 Per ER#:
339 ---****** Call Material Transaction if inventory is not null and part is returned. ******--
340
341 IF ( (p_x_parts_rec_tbl(i).operation_type='D' or p_x_parts_rec_tbl(i).operation_type='M')
342 AND ( p_x_parts_rec_tbl(i).SubInventory_code is not null)) THEN
343
344 IF G_DEBUG = 'Y' THEN
345 AHL_DEBUG_PUB.debug('inside material txn call');
346 END IF;
347 --Call material transaction api
348 Process_material_txn(
349 p_x_parts_rec => p_x_parts_rec_tbl(i),
350 p_module_type => p_module_type,
351 x_return_status => x_return_status);
352
353 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
354 RAISE FND_API.G_EXC_ERROR;
355 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357 END IF;
358 END IF;
359 */
360
361 ---******Insert into ahl_part_changes table ******---
362 -- Null out removal date and installation dates.
363 IF (p_x_parts_rec_tbl(i).operation_type='D' ) THEN
364 p_x_parts_rec_tbl(i).installation_date := NULL;
365 l_part_change_type := 'R'; -- Remove.
366 l_part_change_qty := NVL(p_x_parts_rec_tbl(i).removed_quantity,1);
367 p_x_parts_rec_tbl(i).Installed_Instance_Id := NULL;
368 END IF;
369
370 IF (p_x_parts_rec_tbl(i).operation_type='C' ) THEN
371 p_x_parts_rec_tbl(i).removal_date := NULL;
372 l_part_change_type := 'I'; -- Install
373
374 -- Find out the Issue Material Txn for this instance.
375 Get_Issue_Mtl_Txn ( p_x_parts_rec_tbl(i).workorder_id,
376 p_x_parts_rec_tbl(i).Installed_Instance_Id,
377 l_Issue_Mtl_Txn_id);
378 l_part_change_qty := NVL(p_x_parts_rec_tbl(i).installed_quantity,1);
379 p_x_parts_rec_tbl(i).Removed_Instance_Id := NULL;
380
381 END IF;
382
383 IF (p_x_parts_rec_tbl(i).operation_type='M' ) THEN
384 l_part_change_type := 'S'; -- Swap.
385
386 -- Find out the Issue Material Txn for this instance.
387 Get_Issue_Mtl_Txn ( p_x_parts_rec_tbl(i).workorder_id,
388 p_x_parts_rec_tbl(i).Installed_Instance_Id,
389 l_Issue_Mtl_Txn_id);
390 l_part_change_qty := NVL(p_x_parts_rec_tbl(i).installed_quantity,1);
391
392 END IF;
393
394 -- set path position ID -- out parameter.
395 p_x_parts_rec_tbl(i).path_position_id := l_path_position_id;
396
397 -- insert into ahl_part_chnages if everything is ok.
398 AHL_PART_CHANGES_PKG.insert_row(
399 X_ROWID => l_row_id,
400 X_PART_CHANGE_ID => p_x_parts_rec_tbl(i).part_change_txn_id,
401 X_UNIT_CONFIG_HEADER_ID =>p_x_parts_rec_tbl(i).unit_config_header_id,
402 X_REMOVED_INSTANCE_ID => p_x_parts_rec_tbl(i).removed_instance_id,
403 --X_MC_RELATIONSHIP_ID => p_x_parts_rec_tbl(i).mc_relationship_id,
404 X_MC_RELATIONSHIP_ID => l_path_position_id,
405 X_REMOVAL_CODE => p_x_parts_rec_tbl(i).removal_code,
406 --X_STATUS_ID => p_x_parts_rec_tbl(i).Condition_id,
407 X_REMOVAL_REASON_ID => p_x_parts_rec_tbl(i).removal_reason_id,
408 X_INSTALLED_INSTANCE_ID => p_x_parts_rec_tbl(i).installed_instance_id,
409 X_WORKORDER_OPERATION_ID => p_x_parts_rec_tbl(i).workorder_operation_id,
410 X_OBJECT_VERSION_NUMBER => 1,
411 --X_COLLECTION_ID => p_x_parts_rec_tbl(i).collection_id,
412 --X_WORKORDER_MTL_TXN_ID => p_x_parts_rec_tbl(i).material_txn_id,
413 --X_NON_ROUTINE_WORKORDER_ID => null,
414 X_REMOVAL_DATE => p_x_parts_rec_tbl(i).removal_date,
415 X_INSTALLATION_DATE => p_x_parts_rec_tbl(i).Installation_Date,
416 X_ISSUE_MTL_TXN_ID => l_issue_mtl_txn_id,
417 X_RETURN_MTL_TXN_ID => null,
418 X_PART_CHANGE_TYPE => l_part_change_type,
419 X_CREATION_DATE => sysdate,
420 X_CREATED_BY => fnd_global.user_id,
421 X_LAST_UPDATE_DATE => sysdate,
422 X_LAST_UPDATED_BY => fnd_global.user_id,
423 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
424 X_ATTRIBUTE_CATEGORY =>null,
425 X_ATTRIBUTE1 => null,
426 X_ATTRIBUTE2 =>null,
427 X_ATTRIBUTE3 =>null,
428 X_ATTRIBUTE4 =>null,
429 X_ATTRIBUTE5 =>null,
430 X_ATTRIBUTE6 =>null,
431 X_ATTRIBUTE7 =>null,
432 X_ATTRIBUTE8 =>null,
433 X_ATTRIBUTE9 =>null,
434 X_ATTRIBUTE10 =>null,
435 X_ATTRIBUTE11 =>null,
436 X_ATTRIBUTE12 =>null,
437 X_ATTRIBUTE13 =>null,
438 X_ATTRIBUTE14 =>null,
439 X_ATTRIBUTE15 =>null,
440 X_QUANTITY => l_part_change_qty);
441
442 END LOOP;
443
444 END IF;
445
446 /* Disposition API creates Service Request. Hence this is no longer needed. */
447 /* ER#
448 -- Create Service Request for the Parts records if operation type = D or M andbased on condition.
449
450 IF G_DEBUG = 'Y' THEN
451 AHL_DEBUG_PUB.debug('Calling SR');
452 END IF;
453 --Call sr api
454 Process_SR(
455 p_x_parts_rec_tbl => p_x_parts_rec_tbl,
456 p_module_type => p_module_type,
457 x_return_status => x_return_status);
458
459 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
460 RAISE FND_API.G_EXC_ERROR;
461 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
463 END IF;
464
465 */
466
467 -- Standard check of p_commit
468 IF FND_API.To_Boolean(p_commit) THEN
469 COMMIT WORK;
470 END IF;
471
472 -- Initialize API return status to success
473 x_return_status := FND_API.G_RET_STS_SUCCESS;
474
475 IF G_DEBUG = 'Y' THEN
476 AHL_DEBUG_PUB.debug('Completed Processing. Checking for errors', '');
477 END IF;
478
479 -- Standard call to get message count and if count is 1, get message
480 FND_MSG_PUB.Count_And_Get
481 ( p_count => x_msg_count,
482 p_data => x_msg_data,
483 p_encoded => fnd_api.g_false);
484
485 -- Disable debug (if enabled)
486 IF G_DEBUG = 'Y' THEN
487 AHL_DEBUG_PUB.disable_debug;
488 END IF;
489
490 EXCEPTION
491 WHEN FND_API.G_EXC_ERROR THEN
492 x_return_status := FND_API.G_RET_STS_ERROR;
493 Rollback to perform_part_changes_pvt;
494 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
495 p_data => x_msg_data,
496 p_encoded => fnd_api.g_false);
497 -- Disable debug
498 IF G_DEBUG = 'Y' THEN
499 AHL_DEBUG_PUB.disable_debug;
500 END IF;
501
502
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
505 Rollback to perform_part_changes_pvt;
506 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
507 p_data => x_msg_data,
508 p_encoded => fnd_api.g_false);
509 -- Disable debug
510 IF G_DEBUG = 'Y' THEN
511 AHL_DEBUG_PUB.disable_debug;
512 END IF;
513 WHEN OTHERS THEN
514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
515 Rollback to perform_part_changes_pvt;
516 --IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
517 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PRD_PARTS_CHANGE_PVT',
518 p_procedure_name => 'process_parts',
519 p_error_text => SQLERRM);
520 --END IF;
521 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
522 p_data => x_msg_data,
523 p_encoded => fnd_api.g_false);
524
525 -- Disable debug
526 IF G_DEBUG = 'Y' THEN
527 AHL_DEBUG_PUB.disable_debug;
528 END IF;
529
530 End Process_Part;
531 ---------------------------------
532
533 Procedure Validate_Part_Record (
534 p_x_parts_rec In Out Nocopy Ahl_Parts_Rec_type,
535 p_module_type In Varchar2,
536 X_Return_Status Out NOCOPY Varchar2)
537 IS
538 --
539 l_junk varchar2(100):= null;
540 l_org_id NUMBER;
541 l_inventory_status number;
542 l_plan_id number;
543 l_wip_entity_id number;
544
545 l_rm_inventory_item_id NUMBER;
546 l_rm_inst_number csi_item_instances.instance_number%TYPE;
547
548 --To check if the unit config header is valid or not.
549 CURSOR ahl_uc_header_csr(p_uc_header_id number) IS
550 select 'x'
551 from ahl_unit_config_headers
552 where unit_config_header_id = p_uc_header_id
553 and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
554
555 --To check that the operation sequence exists for the work order.
556 CURSOR ahl_wo_Oper_csr (p_wo_id in number, p_op_seq_num in number) IS
557 select workorder_operation_id
558 from ahl_workorder_operations
559 where workorder_id= p_wo_id
560 and operation_sequence_num = p_op_seq_num;
561
562 -- Get workorder details.
563 -- and get organization id
564 CURSOR ahl_job_csr(p_wo_id in number) IS
565 select job_status_code, organization_id , wip_entity_id
566 --from ahl_workorders_v
567 from ahl_workorder_tasks_v
568 where workorder_id = p_wo_id;
569
570 /*
571 --To check if user sends a target visit, then check should be made that the visit is open.
572 CURSOR ahl_visit_csr(p_visit_id in NUmber) IS
573 select status_code
574 from ahl_visits_vl
575 where visit_id = p_visit_id;
576
577 -- to get locator_id from locator code
578 CURSOR ahl_locator_csr (p_locator_code in varchar2, p_org_id in number) is
579 select inventory_location_id
580 from mtl_item_locations_kfv
581 where concatenated_segments = p_locator_code
582 and organization_id =p_org_id;
583
584 */
585
586 -- to validate installed item instance
587 CURSOR ahl_location_type_csr(l_item_instance_id in number) IS
588 -- Fix for bug# 6993283
589 --select location_type_code
590 select cii.wip_job_id,
591 (select wip_entity_name
592 from wip_entities
593 where wip_entity_id = cii.wip_job_id) wip_job_name
594 from csi_item_instances cii
595 where cii.instance_id= l_item_instance_id
596 --and location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
597 and cii.location_type_code = 'WIP'
598 and trunc(sysdate) < trunc(nvl(cii.active_end_date, sysdate+1))
599 and cii.quantity > 0;
600
601 -- to validate removal item instance.
602 CURSOR ahl_item_instance_csr(p_item_instance_id in number) IS
603 select inventory_item_id, instance_number
604 from csi_item_instances
605 where instance_id= p_item_instance_id
606 and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
607
608 -- To validate if instance's item defined in job's org.
609 CURSOR mtl_system_kfv_csr(p_inventory_item_id IN NUMBER,
610 p_org_id IN NUMBER) IS
611 Select 'x'
612 From mtl_system_items_b
613 where inventory_item_id = p_inventory_item_id
614 and organization_id = p_org_id
615 and enabled_flag = 'Y';
616
617 --
618 l_msg_data varchar2(2000);
619 l_msg_count number;
620
621 l_return_status VARCHAR2(1);
622
623 CURSOR get_instance_attrib_csr(p_instance_id NUMBER) IS
624 SELECT inventory_item_id,
625 inv_master_organization_id,
626 lot_number,
627 quantity,
628 unit_of_measure,
629 inventory_revision,
630 serial_number
631 FROM csi_item_instances
632 WHERE instance_id = p_instance_id;
633
634 l_config_instance_rec get_instance_attrib_csr%ROWTYPE;
635 l_new_instance_rec get_instance_attrib_csr%ROWTYPE;
636 --
637
638 -- Fix for bug# 6993283
639 l_inst_job_id NUMBER;
640 l_wip_job_name WIP_ENTITIES.wip_entity_name%TYPE;
641
642 BEGIN
643
644 -- Initialize API return status to success
645 x_return_status := FND_API.G_RET_STS_SUCCESS;
646
647
648
649 --********Test that the workorder_id is not null -----
650 IF ( p_x_parts_rec.workorder_id is null or p_x_parts_rec.workorder_id =FND_API.G_MISS_NUM) then
651
652 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_WO_ID_MISSIN');
653 FND_MSG_PUB.ADD;
654 RAISE FND_API.G_EXC_ERROR;
655 END IF;
656
657 --rroy
658 -- ACL Changes
659 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_x_parts_rec.workorder_id,
660 p_ue_id => NULL,
661 p_visit_id => NULL,
662 p_item_instance_id => NULL);
663 IF l_return_status = FND_API.G_TRUE THEN
664 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_PRTCHG_UNTLCKD');
665 FND_MSG_PUB.ADD;
666 RAISE FND_API.G_EXC_ERROR;
667 END IF;
668
669 --rroy
670 -- ACL Changes
671
672 --******** Test that operation type is not null -----
673 IF ( p_x_parts_rec.operation_type is null ) then
674 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_OP_TYPE_MISSIN');
675 FND_MSG_PUB.ADD;
676 RAISE FND_API.G_EXC_ERROR;
677 END IF;
678
679 --*********** Operation Seq ********** -----
680 IF (p_x_parts_rec.operation_sequence_num is null ) then
681 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_INVALID_OPERATION');
682 FND_MESSAGE.Set_Token('OPSEQ', p_x_parts_rec.operation_sequence_num);
683 --FND_MESSAGE.Set_Token('WOID', p_x_parts_rec.workorder_id);
684 FND_MSG_PUB.ADD;
685 RAISE FND_API.G_EXC_ERROR;
686 END IF;
687
688 --*********** Check that the operation sequence exists for the work order. ---
689 OPEN ahl_wo_oper_csr(p_x_parts_rec.workorder_id,p_x_parts_rec.operation_sequence_num );
690
691 FETCH ahl_wo_oper_csr INTO p_x_parts_rec.workorder_operation_id;
692 IF (ahl_wo_oper_csr%NOTFOUND) THEN
693 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_INVALID_OPERATION');
694 FND_MESSAGE.Set_Token('OPSEQ', p_x_parts_rec.operation_sequence_num);
695
696 --FND_MESSAGE.Set_Token('WOID', p_x_parts_rec.workorder_id);
697 FND_MSG_PUB.ADD;
698 CLOSE ahl_wo_oper_csr;
699 RAISE FND_API.G_EXC_ERROR;
700 END IF;
701 CLOSE ahl_wo_oper_csr;
702
703 -- Validate that the job is not closed.
704 OPEN ahl_job_csr(p_x_parts_rec.workorder_id );
705 FETCH ahl_job_csr INTO l_junk, l_org_id, l_wip_entity_id;
706 IF ( ahl_job_csr%NOTFOUND) THEN
707 CLOSE ahl_job_csr;
708 FND_MESSAGE.set_name('AHL', 'AHL_PRD_WO_INVALID');
709 FND_MESSAGE.set_token('WOID', p_x_parts_rec.workorder_id);
710 FND_MSG_PUB.ADD;
711 RAISE FND_API.G_EXC_ERROR;
712 END IF;
713 CLOSE ahl_job_csr;
714
715 IF (l_junk <> '3' and l_junk <> '19') THEN
716 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_JOB_CLOSED');
717 FND_MSG_PUB.ADD;
718 RAISE FND_API.G_EXC_ERROR;
719 END IF;
720
721 -- To test pending transactions in WIP interface.
722 /*IF (AHL_WIP_JOB_PVT.wip_massload_pending(l_wip_entity_id)) THEN
723 RAISE FND_API.G_EXC_ERROR;
724 END IF;*/
725
726 --Check if the UC header id is valid.
727 IF (p_x_parts_rec.Unit_Config_Header_Id is not null) then
728 OPEN ahl_uc_header_csr(p_x_parts_rec.Unit_Config_Header_Id );
729
730 FETCH ahl_uc_header_csr INTO l_junk;
731 IF ( ahl_uc_header_csr%NOTFOUND) THEN
732 CLOSE ahl_uc_header_csr;
733 FND_MESSAGE.set_name('AHL', 'AHL_PRD_UC_HEADER_INVALID');
734 FND_MESSAGE.set_token('UCID', p_x_parts_rec.unit_config_header_id);
735 FND_MSG_PUB.ADD;
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738 END IF;
739
740 --********* Position *************--
741 --Check that the position is not null for a UC part
742 IF (p_x_parts_rec.mc_relationship_id is null
743 and p_x_parts_rec.Unit_Config_Header_Id is not null
744 and p_x_parts_rec.operation_type <> 'D')
745 THEN
746 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_NULL_POSN');
747 FND_MSG_PUB.ADD;
748 END IF;
749
750 --*********Instance number **************--
751 -- It is mandatory.This should not be expired.
752 IF ( p_x_parts_rec.operation_type='D' and
753 p_x_parts_rec.removed_instance_id is null )
754 THEN
755 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_RMV_ITEM_MISSING');
756 FND_MSG_PUB.ADD;
757 END IF;
758
759 IF ( p_x_parts_rec.operation_type='M' and
760 p_x_parts_rec.removed_instance_id is null )
761 THEN
762 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_RMV_ITEM_MISSING');
763 FND_MSG_PUB.ADD;
764 END IF;
765
766 IF ( ( p_x_parts_rec.operation_type='C' or p_x_parts_rec.operation_type='M') and
767 (p_x_parts_rec.installed_instance_id is null) )
768 THEN
769 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_INSTALL_ITEM_MISSIN');
770 FND_MSG_PUB.ADD;
771 END IF;
772
773 IF ( (p_x_parts_rec.operation_type='C') and
774 (p_x_parts_rec.parent_installed_instance_id is null ))
775 THEN
776 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_PARENT_ITEM_MISSING');
777 FND_MSG_PUB.ADD;
778 END IF;
779
780 --** validate item instance***--
781 -- Check whether teh part being installed is valid or not.
782 IF (p_x_parts_rec.operation_type ='C' or p_x_parts_rec.operation_type='M') THEN
783
784 OPEN ahl_location_type_csr(p_x_parts_Rec.Installed_Instance_Id);
785 /* replaced validation to fix bug# 6993283
786 FETCH ahl_location_type_csr into l_junk;
787 CLOSE ahl_location_type_csr;
788 IF l_junk is null then
789 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INST_STATUS_INVALID');
790 FND_MSG_PUB.ADD;
791 --RAISE FND_API.G_EXC_ERROR;
792 END IF;
793 */
794 FETCH ahl_location_type_csr into l_inst_job_id, l_wip_job_name;
795 IF (ahl_location_type_csr%NOTFOUND) THEN
796 FND_MESSAGE.Set_Name('AHL','AHL_PRD_ITEM_NOT_ISSUED');
797 FND_MSG_PUB.ADD;
798 ELSE
799 -- added to fix bug# 6993283
800 -- validate job ID.
801 IF (l_inst_job_id <> l_wip_entity_id) THEN
802 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INST_JOB_INVALID');
803 FND_MESSAGE.Set_Token('WO_NAME',l_wip_job_name);
804 FND_MSG_PUB.ADD;
805 END IF;
806 END IF;
807 CLOSE ahl_location_type_csr;
808 END IF;
809
810 -- Check if the removed instance is defined in the job's organization.
811 IF (p_x_parts_rec.operation_type = 'D' or p_x_parts_rec.operation_type = 'M') THEN
812 OPEN ahl_item_instance_csr(p_x_parts_rec.removed_instance_id);
813 FETCH ahl_item_instance_csr INTO l_rm_inventory_item_id, l_rm_inst_number;
814 IF ahl_item_instance_csr%NOTFOUND THEN
815 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_RMV_ITEM_INVALID');
816 FND_MSG_PUB.ADD;
817 ELSE
818 -- Validate that the item exists in the job's org.
819 OPEN mtl_system_kfv_csr(l_rm_inventory_item_id, l_org_id);
820 FETCH mtl_system_kfv_csr INTO l_junk;
821 IF (mtl_system_kfv_csr%NOTFOUND) THEN
822 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_RMV_ORG_INVALID');
823 FND_MESSAGE.Set_Token('INST_ID', l_rm_inst_number);
824 FND_MSG_PUB.ADD;
825 END IF;
826 CLOSE mtl_system_kfv_csr;
827
828 END IF;
829 CLOSE ahl_item_instance_csr;
830 END IF;
831
832
833 --******** Reason Lov ***--
834 -- should not be null for part removal. Validate if the reason code sent exists in the table
835
836 --mtl_transaction_reasons.
837 IF ( p_x_parts_rec.removal_reason_id is null
838 and (p_x_parts_rec.operation_type='M' or p_x_parts_rec.operation_type='D')
839 )
840 THEN
841 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_REASON_CODE_MISSING');
842 FND_MSG_PUB.ADD;
843 END IF;
844
845 ---*****Validate reason****************
846 IF (p_x_parts_rec.removal_reason_id is not null ) THEN
847 AHL_PRD_UTIL_PKG.validate_reason(
848 p_reason_id => p_x_parts_rec.removal_reason_id,
849 x_return_status => x_return_status,
850 x_msg_data => l_msg_data);
851 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
852 RAISE FND_API.G_EXC_ERROR;
853 END IF;
854
855 END IF;
856
857
858 /* Condition is stored as part of Disposition. Per ER# */
859 /*
860 --*****Condition **********--- Condition should not be null when removing a part.
861 IF (p_x_parts_rec.condition_Id is null
862 and ( p_x_parts_rec.operation_type='M' or p_x_parts_rec.operation_type='D')
863 )
864 THEN
865 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_CONDN_MISSING');
866 FND_MSG_PUB.ADD;
867 END IF;
868
869 --********* Validate Condition ***************__
870 IF (p_x_parts_rec.condition_id is not null ) THEN
871 AHL_PRD_UTIL_PKG.validate_condition(
872 p_condition_id => p_x_parts_rec.condition_id ,
873 x_return_status => x_return_status,
874 x_msg_data => l_msg_data );
875 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
876 RAISE FND_API.G_EXC_ERROR;
877 END IF;
878 END IF;
879
880 */
881
882 --**** Removal Code- This is mandatory in case of part removal.
883 IF (p_x_parts_rec.operation_type='D'
884 OR p_x_parts_rec.operation_type='M') THEN
885 IF (p_x_parts_rec.removal_code is null) THEN
886 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_REMOVAL_CODE_MISSIN');
887 FND_MSG_PUB.ADD;
888 ELSIF NOT(AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_REMOVAL_CODE',p_x_parts_rec.removal_code)) THEN
889 FND_MESSAGE.Set_Name('AHL','AHL_PRD_REMOVAL_CODE_INVALID');
890 FND_MESSAGE.Set_Token('CODE', p_x_parts_rec.removal_code);
891 FND_MSG_PUB.ADD;
892 END IF;
893 END IF;
894
895 /* SR is now created by Disposition API. ER#
896 --********** Perform mrb/unservicebale i.e. SR creation validations
897 IF ((p_x_parts_rec.operation_type='D' or p_x_parts_rec.operation_type='M') AND
898 ((fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE') is not null
899 and p_x_parts_rec.Condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE'))
900 OR
901 (fnd_profile.value('AHL_MTL_MAT_STATUS_MRB') is not null and
902 p_x_parts_rec.Condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'))))
903 THEN
904 --******* Problem Code- This should not be null in case of part removal.
905 -- if p_x_parts_rec.problem_code is null then
906 -- FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_PROBLEM_CODE_MISSIN');
907 -- FND_MSG_PUB.ADD;
908 -- end if;
909
910 --******* Target Visit ***********
911 -- Error out if target visit id is null for unserviceable/mrb type part removal.
912
913 IF p_x_parts_rec.target_visit_id is null THEN
914 -- dbms_output.put_line('Target visit is null for unserviceable mrb');
915 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_VISIT_INVALID');
916 FND_MSG_PUB.ADD;
917 END IF;
918
919 --If user sends a target visit, then check should be made that the visit is open.
920
921 IF (p_x_parts_rec.target_visit_id is not null ) THEN
922 OPEN ahl_visit_csr(p_x_parts_rec.target_visit_id );
923 FETCH ahl_visit_csr INTO l_junk;
924 CLOSE ahl_visit_csr;
925 IF l_junk = 'CLOSED' THEN
926 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_VISIT_INVALID');
927 FND_MSG_PUB.ADD;
928 RAISE FND_API.G_EXC_ERROR;
929 END IF;
930 END IF;
931 -- dbms_output.put_line('target viist id is valid');
932 END IF;--end sr validation
933
934 */
935
936 /* Collection Id is now stored in AHL_PRD_DISPOSITIONS_B table */
937 /*
938 --************* QA plan *************--
939 -- Collection_id should not be null if subinventory status is MRB and a QA plan is attached to the item.
940
941 IF (p_x_parts_rec.subinventory_code is not null ) THEN
942 -- Status_id of the part must match the subinventory status in the case of return.
943
944 IF ( p_x_parts_rec.operation_type='D' OR
945 p_x_parts_rec.operation_type='M') THEN
946
947 AHL_PRD_UTIL_PKG.VALIDATE_MATERIAL_STATUS(p_Organization_Id => l_org_id ,
948 p_Subinventory_Code => p_x_parts_rec.subinventory_code,
949 p_Condition_id => p_x_parts_rec.condition_id,
950 x_return_status => x_return_status);
951
952 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
953 RAISE FND_API.G_EXC_ERROR;
954 END IF;
955
956 -- The following procedure call will decide if a qa plan is atatched.
957 AHL_QA_RESULTS_PVT.get_qa_plan(
958 p_api_version => '1.0',
959 p_init_msg_list => FND_API.G_False,
960 p_commit => FND_API.G_FALSE,
961 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
962 p_default => FND_API.G_FALSE,
963 --p_module_type => p_module_type,
964 p_organization_id => l_org_id,
965 p_transaction_number => 2004,
966 p_col_trigger_value => fnd_profile.value('AHL_MRB_DISP_PLAN_TYPE'),
967 x_return_status => x_return_status,
968 x_msg_count => l_msg_count,
969 x_msg_data => l_msg_data,
970 x_plan_id => l_plan_id);
971
972 IF (fnd_profile.value('AHL_MTL_MAT_STATUS_MRB') is not null and
973 p_x_parts_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')
974 and l_plan_id is not null
975 and p_x_parts_rec.collection_id is null)
976 THEN
977 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_COLLECT_ID_MISSIN');
978 FND_MSG_PUB.ADD;
979 RAISE FND_API.G_EXC_ERROR;
980 END IF;
981
982 END IF; -- operation type.
983 END IF; -- sub-inventory null.
984
985 */
986
987 /* Material Transaction is not allowed from Parts Change. */
988 /* ER#
989 --************* Locator Code ***************--
990 -- get ID if code is provided.
991 IF ( p_x_parts_Rec.locator_code is not null AND p_x_parts_rec.subinventory_code is null) THEN
992
993 --OPEN ahl_locator_csr(p_x_parts_rec.locator_code, l_org_id);
994 --FETCH ahl_locator_csr INTO p_x_parts_rec.locator_id;
995 --IF (ahl_locator_csr%NOTFOUND) then
996 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_SUBINV_MANDATORY');
997 FND_MSG_PUB.ADD;
998 --END IF;
999 --CLOSE ahl_locator_csr;
1000 END IF;
1001
1002 -- Validate ID.
1003 IF (p_x_parts_rec.locator_id is not null ) THEN
1004 AHL_PRD_UTIL_PKG.validate_locators(
1005 p_locator_id => p_x_parts_rec.locator_id,
1006 p_org_id => l_org_id,
1007 p_subinventory_code => p_x_parts_rec.subinventory_code,
1008 X_Return_Status => x_return_status,
1009 x_msg_data => l_msg_data);
1010 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1011 RAISE FND_API.G_EXC_ERROR;
1012 END IF;
1013 END IF;
1014
1015 */
1016
1017 --***********Removal Date *************--
1018 IF (p_x_parts_rec.Removal_date is null or p_x_parts_rec.Removal_date = FND_API.G_MISS_DATE) THEN
1019
1020 p_x_parts_rec.Removal_date := sysdate;
1021 ELSIF (trunc(p_x_parts_rec.Removal_date) > trunc(sysdate)) THEN
1022 FND_MESSAGE.Set_Name('AHL','AHL_PRD_REMOVAL_DATE_INVALID');
1023 FND_MESSAGE.Set_Token('DATE',p_x_parts_rec.Removal_date);
1024 FND_MSG_PUB.ADD;
1025 END IF;
1026
1027
1028
1029 --***********Default Installation Date is null *************--
1030 IF (p_x_parts_rec.Installation_Date is null OR p_x_parts_rec.Installation_Date = FND_API.G_MISS_DATE) THEN
1031
1032 p_x_parts_rec.Installation_Date := sysdate;
1033 END IF;
1034
1035 IF ( p_x_parts_rec.operation_type='D'
1036 AND p_x_parts_rec.removed_instance_id IS NOT NULL)THEN
1037 OPEN get_instance_attrib_csr(p_x_parts_rec.removed_instance_id);
1038 FETCH get_instance_attrib_csr INTO l_config_instance_rec;
1039 CLOSE get_instance_attrib_csr;
1040 IF(NVL(p_x_parts_rec.removed_quantity,1) <= 0 OR NVL(p_x_parts_rec.removed_quantity,1) > l_config_instance_rec.quantity)THEN
1041 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_INRMV_QTY');
1042 FND_MSG_PUB.ADD;
1043 END IF;
1044 END IF;
1045
1046 IF ( p_x_parts_rec.operation_type='C')THEN
1047
1048 OPEN get_instance_attrib_csr(p_x_parts_rec.installed_instance_id);
1049 FETCH get_instance_attrib_csr INTO l_new_instance_rec;
1050 CLOSE get_instance_attrib_csr;
1051
1052 IF(NVL(p_x_parts_rec.installed_quantity,1) <= 0 OR NVL(p_x_parts_rec.installed_quantity,1) > l_new_instance_rec.quantity)THEN
1053 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_ININST_QTY');
1054 FND_MSG_PUB.ADD;
1055 END IF;
1056
1057 IF p_x_parts_rec.removed_instance_id IS NOT NULL THEN
1058 OPEN get_instance_attrib_csr(p_x_parts_rec.removed_instance_id);
1059 FETCH get_instance_attrib_csr INTO l_config_instance_rec;
1060 CLOSE get_instance_attrib_csr;
1061
1062 IF(l_config_instance_rec.inventory_item_id <> l_new_instance_rec.inventory_item_id
1063 OR l_config_instance_rec.inv_master_organization_id <> l_new_instance_rec.inv_master_organization_id
1064 OR NVL(l_config_instance_rec.lot_number,'x') <> NVL(l_new_instance_rec.lot_number,'x')
1065 OR NVL(l_config_instance_rec.unit_of_measure,'x') <> NVL(l_new_instance_rec.unit_of_measure,'x')
1066 OR NVL(l_config_instance_rec.inventory_revision,'x') <> NVL(l_new_instance_rec.inventory_revision,'x')
1067 OR l_config_instance_rec.serial_number IS NOT NULL
1068 OR l_new_instance_rec.serial_number IS NOT NULL)THEN
1069 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_INV_MRG');
1070 FND_MSG_PUB.ADD;
1071
1072 END IF;
1073 END IF;
1074
1075 END IF;
1076
1077 EXCEPTION
1078 WHEN FND_API.G_EXC_ERROR THEN
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080 WHEN NO_DATA_FOUND THEN
1081 x_return_status := FND_API.G_RET_STS_ERROR;
1082 -- dbms_output.put_line('No data found');
1083
1084
1085 END Validate_Part_Record; -- validate_part_record
1086 --------------------------------------
1087
1088 Procedure convert_value_to_id(
1089 p_x_parts_rec In Out Nocopy Ahl_Parts_Rec_type,
1090 X_Return_Status Out NOCOPY Varchar2)
1091 IS
1092
1093 --to get header id if header name is provided
1094 CURSOR ahl_header_id_csr (p_uc_name in varchar2) IS
1095 select unit_config_header_id
1096 from ahl_unit_config_headers
1097 where name = p_uc_name;
1098
1099 --to get instance_id if instance_number is provided
1100 CURSOR ahl_instance_id_csr (p_instance_num in varchar2) IS
1101 select instance_id
1102 from csi_item_instances
1103 where instance_number= p_instance_num;
1104
1105 /*
1106 --to get condition_id if condition meaning is provided
1107 CURSOR ahl_condition_csr ( p_condition in varchar2) is
1108 select status_id
1109 from mtl_material_statuses
1110 where status_code = p_condition;
1111 */
1112
1113 --to get reason id if reason name is provided
1114 CURSOR ahl_reason_csr (p_reason_name in varchar2) IS
1115 select reason_id
1116 from mtl_transaction_reasons
1117 where reason_name = p_Reason_Name;
1118
1119 --To get removal reason code if menaing is provided
1120 CURSOR ahl_removal_lookup_csr(p_meaning in varchar2) IS
1121 select lookup_code
1122 from fnd_lookup_values_vl
1123 where meaning = p_meaning
1124 and lookup_type= 'AHL_REMOVAL_CODE';
1125
1126 /*
1127 --to get problem code if problem meaning is provided
1128 CURSOR ahl_problem_lookup_csr(p_meaning in varchar2) IS
1129 select lookup_code
1130 from fnd_lookup_values_vl
1131 where meaning = p_meaning
1132 and lookup_type= 'REQUEST_PROBLEM_CODE';
1133
1134 -- To get target visit id if number is provided
1135 CURSOR ahl_target_visit_csr (p_visit_number in Number) IS
1136 select visit_id from ahl_visits_vl
1137 where visit_number = p_visit_number;
1138 */
1139
1140 --
1141 BEGIN
1142
1143 -- Get header_id if header name is provided
1144 IF (p_x_parts_Rec.unit_config_name is not null
1145 and p_x_parts_Rec.unit_config_header_id is null ) THEN
1146 OPEN ahl_header_id_csr(p_x_parts_rec.Unit_Config_Name);
1147 FETCH ahl_header_id_csr INTO p_x_parts_rec.Unit_Config_Header_Id;
1148 IF (ahl_header_id_csr%NOTFOUND) THEN
1149 CLOSE ahl_header_id_csr;
1150 FND_MESSAGE.Set_Name('AHL','AHL_UC_NAME_MISSING');
1151 FND_MESSAGE.Set_Token('NAME',p_x_parts_rec.unit_config_name);
1152 FND_MSG_PUB.ADD;
1153 ELSE
1154 CLOSE ahl_header_id_csr;
1155 END IF; END IF;
1156
1157 -- Get Instance Id if instance number is provided
1158 IF (p_x_parts_rec.removed_instance_num is not null
1159 and p_x_parts_rec.removed_instance_id is null ) THEN
1160 OPEN ahl_instance_id_csr(p_x_parts_rec.removed_instance_num);
1161 FETCH ahl_instance_id_csr INTO p_x_parts_rec.removed_instance_id;
1162 IF (ahl_instance_id_csr%NOTFOUND) THEN
1163 CLOSE ahl_instance_id_csr;
1164 FND_MESSAGE.Set_Name('AHL','AHL_PRD_RMV_INST_INVALID');
1165 FND_MESSAGE.Set_Token('INST',p_x_parts_rec.removed_instance_num);
1166 FND_MSG_PUB.ADD;
1167 END IF;
1168 CLOSE ahl_instance_id_csr;
1169 END IF;
1170
1171 IF (p_x_parts_rec.installed_instance_num is not null
1172 and p_x_parts_rec.installed_instance_id is null) THEN
1173 -- dbms_output.put_line('inside ahl_header_id_csr installed');
1174 OPEN ahl_instance_id_csr(p_x_parts_rec.installed_instance_num);
1175 FETCH ahl_instance_id_csr INTO p_x_parts_rec.installed_instance_id;
1176 IF (ahl_instance_id_csr%NOTFOUND) THEN
1177 CLOSE ahl_instance_id_csr;
1178 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INSTAL_INST_INVALID');
1179 FND_MESSAGE.Set_Token('INST',p_x_parts_rec.installed_instance_num);
1180 FND_MSG_PUB.ADD;
1181 ELSE
1182 CLOSE ahl_instance_id_csr;
1183 END IF;
1184 END IF;
1185
1186 --Parent installed instance number
1187 IF (p_x_parts_rec.parent_installed_instance_num is not null
1188 and p_x_parts_rec.parent_installed_instance_id is null) THEN
1189
1190 OPEN ahl_instance_id_csr(p_x_parts_rec.parent_installed_instance_num);
1191 FETCH ahl_instance_id_csr INTO p_x_parts_rec.parent_installed_instance_id;
1192 IF (ahl_instance_id_csr%NOTFOUND) THEN
1193 CLOSE ahl_instance_id_csr;
1194 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_PINSTAL_INVALID');
1195 FND_MESSAGE.Set_Token('INST',p_x_parts_rec.parent_installed_instance_num);
1196 FND_MSG_PUB.ADD;
1197 ELSE
1198 CLOSE ahl_instance_id_csr;
1199 END IF;
1200 END IF;
1201
1202 /* Condition is stored in AHL_PRD_DISPOSITIONS_B
1203 -- Get condition id if condition meaning is provided
1204 IF (p_x_parts_Rec.Condition is not null
1205 and p_x_parts_Rec.Condition_id is null) THEN
1206 -- dbms_output.put_line('inside ahl_condition_csr');
1207 OPEN ahl_condition_csr(p_x_parts_rec.condition);
1208 FETCH ahl_condition_csr INTO p_x_parts_rec.condition_id;
1209 IF (ahl_condition_csr%NOTFOUND) THEN
1210 FND_MESSAGE.Set_Name('AHL','AHL_PRD_COND_INVALID');
1211 FND_MESSAGE.Set_Token('CODE',p_x_parts_rec.condition);
1212 FND_MSG_PUB.ADD;
1213 END IF;
1214 CLOSE ahl_condition_csr;
1215 END IF;
1216 */
1217
1218 -- Get reason id if reasonname is provided
1219 IF ( p_x_parts_Rec.Removal_Reason_Name is not null
1220 and p_x_parts_Rec.Removal_Reason_Id is null) THEN
1221 OPEN ahl_reason_csr(p_x_parts_rec.removal_reason_name);
1222 FETCH ahl_reason_csr INTO p_x_parts_rec.removal_reason_id;
1223 IF (ahl_reason_csr%NOTFOUND) THEN
1224 CLOSE ahl_reason_csr;
1225 FND_MESSAGE.Set_Name('AHL','AHL_PRD_REASON_INVALID');
1226 FND_MSG_PUB.ADD;
1227 ELSE
1228 CLOSE ahl_reason_csr;
1229 END IF;
1230 END IF;
1231
1232 --Get reason_code if reason_meaning is provided
1233 IF (p_x_parts_rec.removal_meaning is not null
1234 AND p_x_parts_rec.removal_code is null) THEN
1235 OPEN ahl_removal_lookup_csr(p_x_parts_rec.removal_meaning);
1236 FETCH ahl_removal_lookup_csr INTO p_x_parts_rec.removal_code;
1237 IF (ahl_removal_lookup_csr%NOTFOUND) THEN
1238 CLOSE ahl_removal_lookup_csr;
1239 FND_MESSAGE.Set_Name('AHL','AHL_PRD_REMOVAL_CODE_INVALID');
1240 FND_MESSAGE.Set_Token('CODE',p_x_parts_rec.removal_meaning);
1241 FND_MSG_PUB.ADD;
1242 ELSE
1243 CLOSE ahl_removal_lookup_csr;
1244 END IF;
1245 END IF;
1246
1247
1248 /* SR is created by Disposition API.
1249 -- To get target visit id if number is provided
1250 IF (p_x_parts_rec.target_visit_num is not null
1251 AND p_x_parts_rec.target_visit_id is null) THEN
1252 OPEN ahl_target_visit_csr(p_x_parts_rec.target_visit_num);
1253 FETCH ahl_target_visit_csr INTO p_x_parts_rec.target_visit_id;
1254 IF (ahl_target_visit_csr%NOTFOUND) THEN
1255 CLOSE ahl_target_visit_csr;
1256 FND_MESSAGE.Set_Name('AHL','AHL_TARGET_VISIT_INVALID');
1257 FND_MESSAGE.Set_Token('CODE',p_x_parts_rec.target_visit_num);
1258 FND_MSG_PUB.ADD;
1259 ELSE
1260 CLOSE ahl_target_visit_csr;
1261 END IF;
1262 END IF;
1263
1264 --Get problem_code if problem_meaning is provided
1265 IF (p_x_parts_rec.problem_meaning is not null
1266 AND p_x_parts_rec.problem_code is null) THEN
1267 OPEN ahl_problem_lookup_csr(p_x_parts_rec.problem_meaning);
1268 FETCH ahl_problem_lookup_csr INTO p_x_parts_rec.problem_code;
1269 IF (ahl_problem_lookup_csr%NOTFOUND) THEN
1270 CLOSE ahl_problem_lookup_csr;
1271 FND_MESSAGE.Set_Name('AHL','AHL_PROBLEM_CODE_INVALID');
1272 FND_MESSAGE.Set_Token('CODE',p_x_parts_rec.problem_meaning);
1273 FND_MSG_PUB.ADD;
1274 else
1275 CLOSE ahl_problem_lookup_csr;
1276 end if;
1277 end if;
1278 */
1279
1280 end;--procedure
1281 -------------------------------------
1282
1283
1284
1285 -- Added workorder_id to fix bug# 5564026.
1286 -- Er# 5660658: Added p_validation_mode parameter. This takes 2 values
1287 -- PARTS_CHG and UPDATE_UC. UC status validation should be done only for Parts
1288 -- Change.
1289 Procedure get_unit_config_information(
1290 p_item_instance_id In Number,
1291 p_workorder_id In Number,
1292 p_validation_mode In varchar2 := 'PARTS_CHG',
1293 x_unit_config_id Out NOCOPY Number,
1294 x_unit_config_name Out NOCOPY Varchar2,
1295 x_return_status Out NOCOPY Varchar2)
1296 IS
1297 --
1298 l_name ahl_unit_config_headers.name%TYPE;
1299 l_location_type_code varchar2(30);
1300 l_location_meaning fnd_lookups.meaning%TYPE;
1301 --
1302 CURSOR ahl_uc_name_csr(p_uc_name in varchar2) IS
1303 select unit_config_header_id, unit_config_status_code,active_uc_status_code
1304 from ahl_unit_config_headers
1305 where name = p_uc_name
1306 and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
1307
1308 CURSOR ahl_location_type_csr(p_item_instance_id In number,
1309 p_csi_location_type In Varchar2) IS
1310 select location_type_code, f.meaning
1311 from csi_item_instances csi, csi_lookups f
1312 where csi.location_type_code = f.lookup_code
1313
1314
1315
1316 and f.lookup_type = p_csi_location_type
1317 and instance_id= p_item_instance_id
1318 and TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1));
1319
1320 -- get instance for the work order.
1321 CURSOR get_vst_instance_csr (p_workorder_id IN NUMBER) IS
1322
1323 SELECT
1324 NVL(VST.ITEM_INSTANCE_ID, VTS.INSTANCE_ID)
1325 FROM
1326 AHL_WORKORDERS AWOS,
1327 AHL_VISITS_B VST,
1328 AHL_VISIT_TASKS_B VTS
1329 WHERE
1330 AWOS.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
1331 VST.VISIT_ID = VTS.VISIT_ID AND
1332 WORKORDER_ID = p_workorder_id;
1333
1334 l_unit_config_status_code ahl_unit_config_headers.unit_config_status_code%TYPE;
1335 l_active_uc_status_code ahl_unit_config_headers.active_uc_status_code%TYPE;
1336
1337 l_item_instance_id NUMBER;
1338 l_uc_header_id NUMBER;
1339
1340 --
1341 BEGIN
1342
1343 -- Initialize API return status to success
1344 FND_MSG_PUB.Initialize;
1345 x_return_status := FND_API.G_RET_STS_SUCCESS;
1346
1347 l_item_instance_id := p_item_instance_id;
1348
1349 IF p_workorder_id IS NOT NULL
1350 THEN
1351 OPEN get_vst_instance_csr(p_workorder_id);
1352 FETCH get_vst_instance_csr INTO l_item_instance_id;
1353 CLOSE get_vst_instance_csr;
1354 END IF;
1355
1356 l_name := AHL_UMP_UTIL_PKG.get_unitName (l_item_instance_id );
1357 x_unit_config_name:= l_name;
1358
1359 IF (l_name is not null ) THEN
1360 OPEN ahl_uc_name_csr(l_name);
1361 FETCH ahl_uc_name_csr INTO x_unit_config_id, l_unit_config_status_code,
1362 l_active_uc_status_code;
1363 IF (ahl_uc_name_csr%NOTFOUND) THEN
1364 x_unit_config_name := NULL;
1365 x_unit_config_id := NULL;
1366 ELSE
1367 IF (l_unit_config_status_code = 'DRAFT') THEN
1368 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_UC_DRAFT');
1369 FND_MSG_PUB.ADD;
1370 CLOSE ahl_uc_name_csr;
1371 RAISE FND_API.G_EXC_ERROR;
1372 ELSIF ((p_validation_mode = 'PARTS_CHG') AND (l_active_uc_status_code <> 'APPROVED')) THEN
1373 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_UC_UNAPPROVED');
1374 FND_MSG_PUB.ADD;
1375 CLOSE ahl_uc_name_csr;
1376 RAISE FND_API.G_EXC_ERROR;
1377 END IF;
1378 END IF;
1379 CLOSE ahl_uc_name_csr;
1380 END IF;
1381
1382 -- Test whether the item instance is valid. If it is in inventory then return an error.
1383 OPEN ahl_location_type_csr(l_item_instance_id, G_CSI_LOCATION_TYPE_CODE);
1384 FETCH ahl_location_type_csr INTO l_location_type_code, l_location_meaning;
1385 IF (ahl_location_type_csr%NOTFOUND) THEN
1386 CLOSE ahl_location_type_csr;
1387 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_PC_INST_EXPIRED');
1388 FND_MSG_PUB.ADD;
1389 RAISE FND_API.G_EXC_ERROR;
1390 ELSE
1391 IF (l_location_type_code IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')) THEN
1392 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INST_STATUS_INVALID');
1393 FND_MESSAGE.Set_Token('STATUS',l_location_meaning);
1394 FND_MSG_PUB.ADD;
1395 RAISE FND_API.G_EXC_ERROR;
1396 END IF;
1397 END IF;
1398
1399
1400 EXCEPTION
1401 WHEN NO_DATA_FOUND THEN
1402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1403
1404 WHEN FND_API.G_EXC_ERROR THEN
1405 x_return_status := FND_API.G_RET_STS_ERROR;
1406
1407 WHEN OTHERS THEN
1408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1409
1410 END; -- procedure get_unit_config_information
1411 ------------------------------------------
1412
1413 Procedure create_csi_transaction_rec(
1414 p_x_csi_transaction_rec in out nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
1415
1416 X_Return_Status Out NOCOPY Varchar2)
1417 IS
1418
1419 l_transaction_type_id number;
1420 l_return_val boolean;
1421
1422 BEGIN
1423 x_return_status := FND_API.G_RET_STS_SUCCESS;
1424
1425 -- csi transaction record.
1426 p_x_csi_transaction_rec.source_transaction_date := sysdate;
1427
1428 -- get transaction_type_id .
1429 AHL_Util_UC_Pkg.GetCSI_Transaction_ID('UC_UPDATE',l_transaction_type_id, l_return_val);
1430
1431 IF NOT(l_return_val) THEN
1432 x_return_status := FND_API.G_RET_STS_ERROR;
1433 END IF;
1434
1435 -- use the transaction id from the header record.
1436 p_x_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
1437
1438 END create_csi_transaction_rec;
1439 ----------------------------------
1440
1441 Procedure Process_UC(
1442 p_x_parts_rec in out nocopy Ahl_Parts_Rec_type,
1443 p_module_type in Varchar2,
1444 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
1445 X_Return_Status Out NOCOPY Varchar2,
1446 x_path_position_id Out NOCOPY Number,
1447 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type)
1448 IS
1449 l_msg_count number;
1450 l_msg_data varchar2(2000);
1451
1452 CURSOR check_inst_nonserial(p_instance_id IN NUMBER) IS
1453 SELECT 'X'
1454 FROM mtl_system_items_b mtl, csi_item_instances csi
1455 WHERE csi.instance_id = p_instance_id
1456 AND csi.inventory_item_id = mtl.inventory_item_id
1457 AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
1458 AND mtl.serial_number_control_code = 1;
1459
1460 l_junk VARCHAR2(1);
1461 l_serialized VARCHAR2(1);
1462 l_move_item_instance_tbl move_item_instance_tbl_type;
1463
1464 CURSOR get_curr_quantity_csr(p_instance_id IN NUMBER) IS
1465 SELECT QUANTITY from csi_item_instances
1466 WHERE INSTANCE_ID = p_instance_id;
1467
1468 l_curr_config_qty NUMBER;
1469 l_curr_job_qty NUMBER;
1470 l_dest_instance_id NUMBER;
1471 l_remaining_qty NUMBER;
1472
1473 l_instance_rec csi_datastructures_pub.instance_rec;
1474
1475 CURSOR csi_item_instance_csr(p_instance_id IN NUMBER) IS
1476 select instance_number,object_Version_number
1477 from csi_item_instances CII
1478 where CII.instance_id = p_instance_id;
1479
1480 CURSOR get_wip_job_csr(p_workorder_id IN NUMBER) IS
1481 select wip_entity_id from ahl_workorders
1482 where workorder_id = p_workorder_id;
1483 l_wip_job_id NUMBER;
1484
1485 CURSOR removal_instance_id(p_instance_id IN NUMBER, p_wip_job_id IN NUMBER) IS
1486 SELECT C1.instance_id FROM CSI_ITEM_INSTANCES C1, CSI_ITEM_INSTANCES C2
1487 WHERE C1.INV_MASTER_ORGANIZATION_ID= C2.INV_MASTER_ORGANIZATION_ID
1488 AND C1.INVENTORY_ITEM_ID = C2.INVENTORY_ITEM_ID
1489 AND NVL(C1.INVENTORY_REVISION,'x') = NVL(C2.INVENTORY_REVISION,'x')
1490 AND NVL(C1.LOT_NUMBER,'x') = NVL(C2.LOT_NUMBER,'x')
1491 AND C1.WIP_JOB_ID= p_wip_job_id
1492 AND C1.unit_of_measure = C2.unit_of_measure
1493 AND C2.instance_id = p_instance_id
1494 AND C1.quantity > 0
1495 AND C1.ACTIVE_START_DATE <= SYSDATE
1496 AND ((C1.ACTIVE_END_DATE IS NULL) OR (C1.ACTIVE_END_DATE > SYSDATE));
1497
1498 l_final_removed_inst_id NUMBER;
1499
1500 BEGIN
1501
1502 -- Initialize.
1503 x_path_position_id := p_x_parts_rec.mc_relationship_id;
1504
1505 OPEN get_wip_job_csr(p_x_parts_rec.workorder_id);
1506 FETCH get_wip_job_csr INTO l_wip_job_id;
1507 CLOSE get_wip_job_csr;
1508
1509 IF ( p_x_parts_rec.operation_type ='D') then
1510 IF G_DEBUG = 'Y' THEN
1511 AHL_DEBUG_PUB.debug('Before making a call to AHL_UC_INSTANCE_PVT.remove_instance.');
1512 END IF;
1513
1514 -- find the path_position_id for the removed instance.
1515
1516 AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID (
1517 p_api_version => 1.0,
1518 x_return_status => x_return_status,
1519 x_msg_count => l_msg_count,
1520 x_msg_data => l_msg_data,
1521 p_csi_item_instance_id => p_x_parts_rec.removed_instance_id,
1522 x_path_position_id => x_path_position_id);
1523
1524
1525 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1526 RAISE FND_API.G_EXC_ERROR;
1527 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1529 END IF;
1530 l_serialized := FND_API.G_FALSE;
1531 OPEN check_inst_nonserial(p_x_parts_rec.Removed_Instance_Id);
1532 FETCH check_inst_nonserial INTO l_junk;
1533 IF(check_inst_nonserial%NOTFOUND)THEN
1534 l_serialized := FND_API.G_TRUE;
1535 END IF;
1536 CLOSE check_inst_nonserial;
1537
1538 IF( l_serialized = FND_API.G_FALSE) THEN
1539 OPEN get_curr_quantity_csr(p_x_parts_rec.Removed_Instance_Id);
1540 FETCH get_curr_quantity_csr INTO l_curr_config_qty;
1541 CLOSE get_curr_quantity_csr;
1542 END IF;
1543 --Call remove_instance
1544 IF( l_serialized = FND_API.G_TRUE OR p_x_parts_rec.Removed_Quantity = l_curr_config_qty)THEN
1545 AHL_UC_INSTANCE_PVT.remove_instance(
1546 p_api_version => 1.0,
1547 p_init_msg_list => FND_API.G_TRUE,
1548 p_commit => FND_API.G_FALSE,
1549 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1550 x_return_status => x_return_status,
1551 x_msg_count => l_msg_count,
1552 x_msg_data => l_msg_data,
1553 p_uc_header_id => p_x_parts_rec.unit_config_header_id,
1554 p_instance_id => p_x_parts_rec.Removed_Instance_Id,
1555 p_csi_ii_ovn => p_x_parts_rec.CSI_II_OBJECT_VERSION_NUM,
1556 p_prod_user_flag => 'Y');
1557
1558
1559 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1560 RAISE FND_API.G_EXC_ERROR;
1561 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1563 END IF;
1564
1565 update_item_location(
1566 p_x_parts_rec => p_x_parts_rec,
1567 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1568 x_return_status => x_return_status);
1569 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1570 RAISE FND_API.G_EXC_ERROR;
1571 END IF;
1572 l_move_item_instance_tbl(1).instance_id := p_x_parts_rec.Removed_Instance_Id;
1573 l_move_item_instance_tbl(1).quantity := p_x_parts_rec.Removed_Quantity;
1574 l_move_item_instance_tbl(1).to_workorder_id := p_x_parts_rec.workorder_id;
1575
1576 move_instance_location(
1577 P_API_Version => 1.0,
1578 P_Init_Msg_List => Fnd_API.G_False,
1579 P_Commit => Fnd_API.G_False,
1580 P_Validation_Level => Fnd_API.G_Valid_Level_Full,
1581 p_module_type => 'API',
1582 p_default => FND_API.G_TRUE,
1583 p_move_item_instance_tbl => l_move_item_instance_tbl,
1584 x_return_status => x_return_status,
1585 x_msg_count => l_msg_count,
1586 x_msg_data => l_msg_data);
1587 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1588 RAISE FND_API.G_EXC_ERROR;
1589 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1591 END IF;
1592 IF( l_serialized = FND_API.G_FALSE)THEN
1593 OPEN removal_instance_id(p_x_parts_rec.Removed_Instance_Id, l_wip_job_id);
1594 FETCH removal_instance_id INTO l_final_removed_inst_id;
1595 IF(removal_instance_id%FOUND)THEN
1596 p_x_parts_rec.Removed_Instance_Id := l_final_removed_inst_id;
1597 END IF;
1598 CLOSE removal_instance_id;
1599 END IF;
1600 IF G_DEBUG = 'Y' THEN
1601 AHL_DEBUG_PUB.debug('1 final p_x_parts_rec.Removed_Instance_Id ' || p_x_parts_rec.Removed_Instance_Id);
1602 AHL_DEBUG_PUB.debug('l_final_removed_inst_id ' || l_final_removed_inst_id);
1603 END IF;
1604
1605 ELSE -- non-serialized incomplete removal
1606
1607 AHL_UC_INSTANCE_PVT.remove_instance(
1608 p_api_version => 1.0,
1609 p_init_msg_list => FND_API.G_TRUE,
1610 p_commit => FND_API.G_FALSE,
1611 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1612 x_return_status => x_return_status,
1613 x_msg_count => l_msg_count,
1614 x_msg_data => l_msg_data,
1615 p_uc_header_id => p_x_parts_rec.unit_config_header_id,
1616 p_instance_id => p_x_parts_rec.Removed_Instance_Id,
1617 p_csi_ii_ovn => p_x_parts_rec.CSI_II_OBJECT_VERSION_NUM,
1618 p_prod_user_flag => 'Y');
1619
1620
1621 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1622 RAISE FND_API.G_EXC_ERROR;
1623 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1625 END IF;
1626
1627 update_item_location(
1628 p_x_parts_rec => p_x_parts_rec,
1629 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1630 x_return_status => x_return_status);
1631
1632 l_remaining_qty := l_curr_config_qty - p_x_parts_rec.Removed_Quantity;
1633 -- update configuration instance with net quantity
1634 l_instance_rec.instance_id := p_x_parts_rec.Removed_Instance_Id;
1635 l_instance_rec.quantity := l_remaining_qty;
1636 OPEN csi_item_instance_csr(p_x_parts_rec.Removed_Instance_Id);
1637 FETCH csi_item_instance_csr INTO p_x_parts_rec.Removed_Instance_Num
1638 ,l_instance_rec.object_version_number;
1639 CLOSE csi_item_instance_csr;
1640
1641 update_csi_item_instance(
1642 p_instance_rec => l_instance_rec,
1643 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1644 x_return_status => x_return_status
1645 );
1646
1647 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1648 RAISE FND_API.G_EXC_ERROR;
1649 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1651 END IF;
1652 --create same instance with removed qty
1653 create_similar_instance(
1654 p_source_instance_id => p_x_parts_rec.Removed_Instance_Id,
1655 p_dest_quantity => p_x_parts_rec.Removed_Quantity,
1656 p_dest_wip_job_id => l_wip_job_id,
1657 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1658 x_dest_instance_id => l_dest_instance_id,
1659 x_return_status => x_return_status
1660 );
1661 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1662 RAISE FND_API.G_EXC_ERROR;
1663 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1665 END IF;
1666
1667 --Call install_existing_instance
1668 AHL_UC_INSTANCE_PVT.install_existing_instance(
1669 p_api_version => 1.0,
1670 p_init_msg_list => FND_API.G_TRUE,
1671 p_commit => FND_API.G_FALSE,
1672 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1673 x_return_status => x_return_status,
1674 x_msg_count => l_msg_count,
1675 x_msg_data => l_msg_data,
1676 p_uc_header_id => p_x_parts_rec.unit_config_header_id,
1677 p_parent_instance_id => p_x_parts_rec.Parent_Installed_Instance_Id,
1678 p_instance_id => p_x_parts_rec.Removed_Instance_Id,
1679 p_instance_number => p_x_parts_rec.Removed_Instance_Num,
1680 p_relationship_id => p_x_parts_rec.mc_relationship_id,
1681 p_csi_ii_ovn => NULL,
1682 p_prod_user_flag => 'Y',
1683 x_warning_msg_tbl => x_warning_msg_tbl);
1684
1685 IF G_DEBUG = 'Y' THEN
1686 AHL_DEBUG_PUB.debug('After making a call to AHL_UC_INSTANCE_PVT.install_existing_instance.');
1687 END IF;
1688
1689 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1690 RAISE FND_API.G_EXC_ERROR;
1691 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1693 END IF;
1694
1695 p_x_parts_rec.Removed_Instance_Id := l_dest_instance_id;
1696 IF G_DEBUG = 'Y' THEN
1697 AHL_DEBUG_PUB.debug('update_item_location :: ' ||l_dest_instance_id);
1698 END IF;
1699 /*update_item_location(
1700 p_x_parts_rec => p_x_parts_rec,
1701 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1702 x_return_status => x_return_status);
1703 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1704 RAISE FND_API.G_EXC_ERROR;
1705 END IF;*/
1706
1707 l_move_item_instance_tbl(1).instance_id := p_x_parts_rec.Removed_Instance_Id;
1708 l_move_item_instance_tbl(1).quantity := p_x_parts_rec.Removed_Quantity;
1709 l_move_item_instance_tbl(1).to_workorder_id := p_x_parts_rec.workorder_id;
1710 IF G_DEBUG = 'Y' THEN
1711 AHL_DEBUG_PUB.debug('moving it');
1712 END IF;
1713 move_instance_location(
1714 P_API_Version => 1.0,
1715 P_Init_Msg_List => Fnd_API.G_False,
1716 P_Commit => Fnd_API.G_False,
1717 P_Validation_Level => Fnd_API.G_Valid_Level_Full,
1718 p_module_type => 'API',
1719 p_default => FND_API.G_TRUE,
1720 p_move_item_instance_tbl => l_move_item_instance_tbl,
1721 x_return_status => x_return_status,
1722 x_msg_count => l_msg_count,
1723 x_msg_data => l_msg_data);
1724 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1725 RAISE FND_API.G_EXC_ERROR;
1726 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1728 END IF;
1729
1730 OPEN removal_instance_id(p_x_parts_rec.Removed_Instance_Id, l_wip_job_id);
1731 FETCH removal_instance_id INTO l_final_removed_inst_id;
1732 IF(removal_instance_id%FOUND)THEN
1733 p_x_parts_rec.Removed_Instance_Id := l_final_removed_inst_id;
1734 END IF;
1735 CLOSE removal_instance_id;
1736 --p_x_parts_rec.Removed_Instance_Id := 274689;
1737 IF G_DEBUG = 'Y' THEN
1738 AHL_DEBUG_PUB.debug('2 final p_x_parts_rec.Removed_Instance_Id ' || p_x_parts_rec.Removed_Instance_Id);
1739 AHL_DEBUG_PUB.debug('l_final_removed_inst_id ' || l_final_removed_inst_id);
1740 END IF;
1741
1742 END IF;
1743
1744 ELSIF ( p_x_parts_rec.operation_type ='C') then
1745
1746 IF G_DEBUG = 'Y' THEN
1747 AHL_DEBUG_PUB.debug('Before making a call to AHL_UC_INSTANCE_PVT.install_existing_instance.');
1748 END IF;
1749
1750 l_serialized := FND_API.G_FALSE;
1751 OPEN check_inst_nonserial(p_x_parts_rec.Installed_Instance_Id);
1752 FETCH check_inst_nonserial INTO l_junk;
1753 IF(check_inst_nonserial%NOTFOUND)THEN
1754 l_serialized := FND_API.G_TRUE;
1755 END IF;
1756 CLOSE check_inst_nonserial;
1757
1758 l_curr_config_qty := 0;
1759 IF( l_serialized = FND_API.G_FALSE AND p_x_parts_rec.Removed_Instance_Id IS NOT NULL) THEN
1760 OPEN get_curr_quantity_csr(p_x_parts_rec.Removed_Instance_Id);
1761 FETCH get_curr_quantity_csr INTO l_curr_config_qty;
1762 CLOSE get_curr_quantity_csr;
1763 END IF;
1764
1765 IF( l_serialized = FND_API.G_FALSE AND p_x_parts_rec.Installed_Instance_Id IS NOT NULL) THEN
1766 OPEN get_curr_quantity_csr(p_x_parts_rec.Installed_Instance_Id);
1767 FETCH get_curr_quantity_csr INTO l_curr_job_qty;
1768 CLOSE get_curr_quantity_csr;
1769 END IF;
1770
1771 IF( l_serialized = FND_API.G_FALSE
1772 AND l_curr_job_qty <> p_x_parts_rec.Installed_Quantity AND l_curr_config_qty = 0)THEN
1773 -- create new instance with remaining qty
1774 l_remaining_qty := l_curr_job_qty - p_x_parts_rec.Installed_Quantity;
1775 create_similar_instance(
1776 p_source_instance_id => p_x_parts_rec.Installed_Instance_Id,
1777 p_dest_quantity => l_remaining_qty,
1778 p_dest_wip_job_id => l_wip_job_id,
1779 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1780 x_dest_instance_id => l_dest_instance_id,
1781 x_return_status => x_return_status
1782 );
1783 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1784 RAISE FND_API.G_EXC_ERROR;
1785 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1787 END IF;
1788 -- update installed instance qty to equal to installed qty
1789 l_instance_rec.instance_id := p_x_parts_rec.Installed_Instance_Id;
1790 l_instance_rec.quantity := p_x_parts_rec.Installed_Quantity;
1791
1792 OPEN csi_item_instance_csr(p_x_parts_rec.Installed_Instance_Id);
1793 FETCH csi_item_instance_csr INTO p_x_parts_rec.Installed_Instance_Num
1794 ,l_instance_rec.object_version_number;
1795 CLOSE csi_item_instance_csr;
1796 update_csi_item_instance(
1797 p_instance_rec => l_instance_rec,
1798 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1799 x_return_status => x_return_status
1800 );
1801 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1802 RAISE FND_API.G_EXC_ERROR;
1803 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1805 END IF;
1806 END IF;
1807
1808
1809 IF( l_serialized = FND_API.G_TRUE OR l_curr_config_qty = 0) THEN
1810 --Call install_existing_instance
1811 AHL_UC_INSTANCE_PVT.install_existing_instance(
1812 p_api_version => 1.0,
1813 p_init_msg_list => FND_API.G_TRUE,
1814 p_commit => FND_API.G_FALSE,
1815 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1816 x_return_status => x_return_status,
1817 x_msg_count => l_msg_count,
1818 x_msg_data => l_msg_data,
1819 p_uc_header_id => p_x_parts_rec.unit_config_header_id,
1820 p_parent_instance_id => p_x_parts_rec.Parent_Installed_Instance_Id,
1821 p_instance_id => p_x_parts_rec.Installed_Instance_Id,
1822 p_instance_number => p_x_parts_rec.Installed_Instance_Num,
1823 p_relationship_id => p_x_parts_rec.mc_relationship_id,
1824 p_csi_ii_ovn => p_x_parts_rec.CSI_II_OBJECT_VERSION_NUM,
1825 p_prod_user_flag => 'Y',
1826 x_warning_msg_tbl => x_warning_msg_tbl);
1827
1828 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1829 RAISE FND_API.G_EXC_ERROR;
1830 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1832 END IF;
1833
1834 IF G_DEBUG = 'Y' THEN
1835 AHL_DEBUG_PUB.debug('After making a call to AHL_UC_INSTANCE_PVT.install_existing_instance.');
1836 END IF;
1837 ELSIF( l_serialized = FND_API.G_FALSE AND l_curr_config_qty <> 0) THEN
1838 AHL_UC_INSTANCE_PVT.remove_instance(
1839 p_api_version => 1.0,
1840 p_init_msg_list => FND_API.G_TRUE,
1841 p_commit => FND_API.G_FALSE,
1842 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1843 x_return_status => x_return_status,
1844 x_msg_count => l_msg_count,
1845 x_msg_data => l_msg_data,
1846 p_uc_header_id => p_x_parts_rec.unit_config_header_id,
1847 p_instance_id => p_x_parts_rec.Removed_Instance_Id,
1848 p_csi_ii_ovn => p_x_parts_rec.CSI_II_OBJECT_VERSION_NUM,
1849 p_prod_user_flag => 'Y');
1850
1851
1852 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1853 RAISE FND_API.G_EXC_ERROR;
1854 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1856 END IF;
1857
1858 update_item_location(
1859 p_x_parts_rec => p_x_parts_rec,
1860 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1861 x_return_status => x_return_status);
1862
1863 l_move_item_instance_tbl(1).instance_id := p_x_parts_rec.Removed_Instance_Id;
1864 l_move_item_instance_tbl(1).quantity := l_curr_config_qty;
1865 l_move_item_instance_tbl(1).to_workorder_id := p_x_parts_rec.workorder_id;
1866 IF G_DEBUG = 'Y' THEN
1867 AHL_DEBUG_PUB.debug('moving it');
1868 END IF;
1869 move_instance_location(
1870 P_API_Version => 1.0,
1871 P_Init_Msg_List => Fnd_API.G_False,
1872 P_Commit => Fnd_API.G_False,
1873 P_Validation_Level => Fnd_API.G_Valid_Level_Full,
1874 p_module_type => 'API',
1875 p_default => FND_API.G_TRUE,
1876 p_move_item_instance_tbl => l_move_item_instance_tbl,
1877 x_return_status => x_return_status,
1878 x_msg_count => l_msg_count,
1879 x_msg_data => l_msg_data);
1880 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1881 RAISE FND_API.G_EXC_ERROR;
1882 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1884 END IF;
1885 -- create new instance with net qty on job
1886 l_remaining_qty := l_curr_job_qty - p_x_parts_rec.Installed_Quantity;
1887
1888 OPEN removal_instance_id(p_x_parts_rec.Removed_Instance_Id, l_wip_job_id);
1889 FETCH removal_instance_id INTO l_final_removed_inst_id;
1890 IF(removal_instance_id%FOUND)THEN
1891 p_x_parts_rec.Removed_Instance_Id := l_final_removed_inst_id;
1892 END IF;
1893 CLOSE removal_instance_id;
1894
1895 IF(l_remaining_qty > 0)THEN
1896
1897 create_similar_instance(
1898 p_source_instance_id => p_x_parts_rec.Removed_Instance_Id,
1899 p_dest_quantity => l_remaining_qty,
1900 p_dest_wip_job_id => l_wip_job_id,
1901 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1902 x_dest_instance_id => l_dest_instance_id,
1903 x_return_status => x_return_status
1904 );
1905 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1906 RAISE FND_API.G_EXC_ERROR;
1907 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1909 END IF;
1910 END IF;
1911 -- now install the previously removed instance back
1912 p_x_parts_rec.Installed_Instance_Id := p_x_parts_rec.Removed_Instance_Id;
1913 -- update installed instance qty to equal to net Config qty
1914 l_instance_rec.instance_id := p_x_parts_rec.Installed_Instance_Id;
1915 l_instance_rec.quantity := p_x_parts_rec.Installed_Quantity + l_curr_config_qty;
1916
1917 OPEN csi_item_instance_csr(p_x_parts_rec.Installed_Instance_Id);
1918 FETCH csi_item_instance_csr INTO p_x_parts_rec.Installed_Instance_Num
1919 ,l_instance_rec.object_version_number;
1920 CLOSE csi_item_instance_csr;
1921 update_csi_item_instance(
1922 p_instance_rec => l_instance_rec,
1923 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1924 x_return_status => x_return_status
1925 );
1926 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1927 RAISE FND_API.G_EXC_ERROR;
1928 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1930 END IF;
1931 -- install the instance back
1932 AHL_UC_INSTANCE_PVT.install_existing_instance(
1933 p_api_version => 1.0,
1934 p_init_msg_list => FND_API.G_TRUE,
1935 p_commit => FND_API.G_FALSE,
1936 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1937 x_return_status => x_return_status,
1938 x_msg_count => l_msg_count,
1939 x_msg_data => l_msg_data,
1940 p_uc_header_id => p_x_parts_rec.unit_config_header_id,
1941 p_parent_instance_id => p_x_parts_rec.Parent_Installed_Instance_Id,
1942 p_instance_id => p_x_parts_rec.Installed_Instance_Id,
1943 p_instance_number => p_x_parts_rec.Installed_Instance_Num,
1944 p_relationship_id => p_x_parts_rec.mc_relationship_id,
1945 p_csi_ii_ovn => p_x_parts_rec.CSI_II_OBJECT_VERSION_NUM,
1946 p_prod_user_flag => 'Y',
1947 x_warning_msg_tbl => x_warning_msg_tbl);
1948
1949 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1950 RAISE FND_API.G_EXC_ERROR;
1951 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1953 END IF;
1954
1955 IF G_DEBUG = 'Y' THEN
1956 AHL_DEBUG_PUB.debug('After making a call to AHL_UC_INSTANCE_PVT.install_existing_instance.');
1957 END IF;
1958 END IF;
1959
1960 ELSIF (p_x_parts_rec.operation_type ='M') then
1961
1962 IF G_DEBUG = 'Y' THEN
1963 AHL_DEBUG_PUB.debug('Before making a call to AHL_UC_INSTANCE_PVT.swap_instance.');
1964 END IF;
1965 --Call swap_instance
1966 AHL_UC_INSTANCE_PVT.swap_instance(
1967 p_api_version => 1.0,
1968 p_init_msg_list => FND_API.G_TRUE,
1969 p_commit => FND_API.G_FALSE,
1970 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1971 x_return_status => x_return_status,
1972 x_msg_count => l_msg_count,
1973 x_msg_data => l_msg_data,
1974 p_uc_header_id => p_x_parts_rec.unit_config_header_id,
1975 p_parent_instance_id => p_x_parts_rec.Parent_Installed_Instance_Id,
1976 p_old_instance_id => p_x_parts_rec.Removed_Instance_Id,
1977 p_new_instance_id => p_x_parts_rec.Installed_Instance_Id,
1978 p_new_instance_number => p_x_parts_rec.Installed_Instance_Num,
1979 p_relationship_id => p_x_parts_rec.mc_relationship_id,
1980 p_csi_ii_ovn => p_x_parts_rec.CSI_II_OBJECT_VERSION_NUM,
1981 p_prod_user_flag => 'Y',
1982 x_warning_msg_tbl => x_warning_msg_tbl);
1983
1984 IF G_DEBUG = 'Y' THEN
1985 AHL_DEBUG_PUB.debug('After making a call to AHL_UC_INSTANCE_PVT.swap_instance.');
1986 END IF;
1987
1988 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
1989 RAISE FND_API.G_EXC_ERROR;
1990 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1992 END IF;
1993
1994 update_item_location(
1995 p_x_parts_rec => p_x_parts_rec,
1996 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
1997 x_return_status => x_return_status);
1998
1999 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2000 RAISE FND_API.G_EXC_ERROR;
2001 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2003 END IF;
2004
2005 l_move_item_instance_tbl(1).instance_id := p_x_parts_rec.Removed_Instance_Id;
2006 l_move_item_instance_tbl(1).quantity := 1;
2007 l_move_item_instance_tbl(1).to_workorder_id := p_x_parts_rec.workorder_id;
2008 -- move removed item to work order's locator
2009 move_instance_location(
2010 P_API_Version => 1.0,
2011 P_Init_Msg_List => Fnd_API.G_False,
2012 P_Commit => Fnd_API.G_False,
2013 P_Validation_Level => Fnd_API.G_Valid_Level_Full,
2014 p_module_type => 'API',
2015 p_default => FND_API.G_TRUE,
2016 p_move_item_instance_tbl => l_move_item_instance_tbl,
2017 x_return_status => x_return_status,
2018 x_msg_count => l_msg_count,
2019 x_msg_data => l_msg_data);
2020 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2021 RAISE FND_API.G_EXC_ERROR;
2022 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2024 END IF;
2025
2026 END IF;
2027
2028 -- Get path position ID for Create and Swap operations.
2029 IF (p_x_parts_rec.operation_type ='C' OR p_x_parts_rec.operation_type = 'M') then
2030
2031 -- find the path_position_id for the installed instance.
2032 AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID (
2033 p_api_version => 1.0,
2034 x_return_status => x_return_status,
2035 x_msg_count => l_msg_count,
2036 x_msg_data => l_msg_data,
2037 p_csi_item_instance_id => p_x_parts_rec.Installed_instance_id,
2038 x_path_position_id => x_path_position_id);
2039
2040 IF G_DEBUG = 'Y' THEN
2041 AHL_DEBUG_PUB.debug('p_x_parts_rec.Installed_Instance_Id ' ||p_x_parts_rec.Installed_Instance_Id);
2042 AHL_DEBUG_PUB.debug('After Install/swap AHL_UC_INSTANCE_PVT' || x_path_position_id);
2043 END IF;
2044
2045 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2046 RAISE FND_API.G_EXC_ERROR;
2047 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2049 END IF;
2050
2051 END IF;
2052
2053
2054 END Process_UC;
2055
2056
2057 Procedure Process_IB(
2058 p_x_csi_transaction_rec in out nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
2059 p_x_parts_rec in out nocopy Ahl_Parts_Rec_type,
2060 X_Return_Status Out NOCOPY Varchar2)
2061 IS
2062 --
2063 l_csi_relationship_tbl CSI_DATASTRUCTURES_PUB.ii_relationship_tbl;
2064 l_csi_transaction_rec CSI_DATASTRUCTURES_PUB.transaction_rec;
2065
2066 l_csi_relationship_rec CSI_DATASTRUCTURES_PUB.ii_relationship_rec;
2067 l_instance_id_lst CSI_DATASTRUCTURES_PUB.id_tbl;
2068 l_unit_config_header_name varchar2(80):= null;
2069 l_msg_count number;
2070 l_msg_data varchar2(2000);
2071
2072 -- Parameters to call Update_Item_Instance.
2073 l_instance_rec csi_datastructures_pub.instance_rec;
2074 l_extend_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2075 l_party_tbl csi_datastructures_pub.party_tbl;
2076 l_account_tbl csi_datastructures_pub.party_account_tbl;
2077
2078 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
2079 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
2080 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
2081
2082 CURSOR check_inst_nonserial(p_instance_id IN NUMBER) IS
2083 SELECT 'X'
2084 FROM mtl_system_items_b mtl, csi_item_instances csi
2085 WHERE csi.instance_id = p_instance_id
2086 AND csi.inventory_item_id = mtl.inventory_item_id
2087 AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
2088 AND mtl.serial_number_control_code = 1;
2089
2090 l_junk VARCHAR2(1);
2091 l_serialized VARCHAR2(1);
2092 l_move_item_instance_tbl move_item_instance_tbl_type;
2093
2094 CURSOR get_curr_quantity_csr(p_instance_id IN NUMBER) IS
2095 SELECT QUANTITY from csi_item_instances
2096 WHERE INSTANCE_ID = p_instance_id;
2097
2098 l_curr_config_qty NUMBER;
2099 l_curr_job_qty NUMBER;
2100 l_dest_instance_id NUMBER;
2101 l_remaining_qty NUMBER;
2102
2103
2104
2105 CURSOR csi_item_instance_csr(p_instance_id IN NUMBER) IS
2106 select instance_number,object_Version_number
2107 from csi_item_instances CII
2108 where CII.instance_id = p_instance_id;
2109
2110 CURSOR get_wip_job_csr(p_workorder_id IN NUMBER) IS
2111 select wip_entity_id from ahl_workorders
2112 where workorder_id = p_workorder_id;
2113 l_wip_job_id NUMBER;
2114
2115 CURSOR removal_instance_id(p_instance_id IN NUMBER, p_wip_job_id IN NUMBER) IS
2116 SELECT C1.instance_id FROM CSI_ITEM_INSTANCES C1, CSI_ITEM_INSTANCES C2
2117 WHERE C1.INV_MASTER_ORGANIZATION_ID= C2.INV_MASTER_ORGANIZATION_ID
2118 AND C1.INVENTORY_ITEM_ID = C2.INVENTORY_ITEM_ID
2119 AND NVL(C1.INVENTORY_REVISION,'x') = NVL(C2.INVENTORY_REVISION,'x')
2120 AND NVL(C1.LOT_NUMBER,'x') = NVL(C2.LOT_NUMBER,'x')
2121 AND C1.WIP_JOB_ID= p_wip_job_id
2122 AND C1.unit_of_measure = C2.unit_of_measure
2123 AND C2.instance_id = p_instance_id
2124 AND C1.quantity > 0
2125 AND C1.ACTIVE_START_DATE <= SYSDATE
2126 AND ((C1.ACTIVE_END_DATE IS NULL) OR (C1.ACTIVE_END_DATE > SYSDATE));
2127
2128 l_final_removed_inst_id NUMBER;
2129
2130
2131 BEGIN
2132
2133 OPEN get_wip_job_csr(p_x_parts_rec.workorder_id);
2134 FETCH get_wip_job_csr INTO l_wip_job_id;
2135 CLOSE get_wip_job_csr;
2136
2137 -- If operation type = M or C then update installation date.
2138 IF (p_x_parts_rec.operation_type = 'M' )--OR p_x_parts_rec.operation_type = 'C')
2139 THEN
2140
2141 l_instance_rec.INSTANCE_ID := p_x_parts_rec.Installed_instance_id;
2142
2143 l_instance_rec.Install_Date := p_x_parts_rec.Installation_Date;
2144 l_instance_rec.OBJECT_VERSION_NUMBER := p_x_parts_rec.Installed_instance_obj_ver_num;
2145
2146 CSI_ITEM_INSTANCE_PUB. update_item_instance (
2147 p_api_version =>1.0
2148 ,p_commit => fnd_api.g_false
2149 ,p_init_msg_list => fnd_api.g_false
2150 ,p_validation_level => fnd_api.g_valid_level_full
2151 ,p_instance_rec => l_instance_rec
2152 ,p_ext_attrib_values_tbl=>l_extend_attrib_values_tbl
2153 ,p_party_tbl =>l_party_tbl
2154 ,p_account_tbl => l_account_tbl
2155 ,p_pricing_attrib_tbl => l_pricing_attrib_tbl
2156 ,p_org_assignments_tbl => l_org_assignments_tbl
2157 ,p_asset_assignment_tbl => l_asset_assignment_tbl
2158 ,p_txn_rec => p_x_csi_transaction_rec
2159 ,x_instance_id_lst => l_instance_id_lst
2160 ,x_return_status => x_return_status
2161 ,x_msg_count => l_msg_count
2162 ,x_msg_data => l_msg_data );
2163 END IF;
2164
2165 -- Build csi relationship rec.
2166 l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
2167 l_csi_relationship_rec.object_id := p_x_parts_rec.Parent_Installed_Instance_Id;
2168 l_csi_relationship_rec.subject_id := p_x_parts_rec.Installed_Instance_Id;
2169 l_csi_relationship_rec.relationship_id := p_x_parts_rec.csi_ii_relationship_id;
2170 l_csi_relationship_rec.object_version_number := p_x_parts_rec.CSI_II_Object_Version_Num;
2171
2172 l_csi_relationship_tbl(1) := l_csi_relationship_rec;
2173
2174 --Installing
2175 IF (p_x_parts_rec.operation_type = 'C' ) then
2176
2177 IF G_DEBUG = 'Y' THEN
2178 AHL_DEBUG_PUB.debug('IB Processing- Create');
2179 END IF;
2180
2181 l_serialized := FND_API.G_FALSE;
2182 OPEN check_inst_nonserial(p_x_parts_rec.Installed_Instance_Id);
2183 FETCH check_inst_nonserial INTO l_junk;
2184 IF(check_inst_nonserial%NOTFOUND)THEN
2185 l_serialized := FND_API.G_TRUE;
2186 END IF;
2187 CLOSE check_inst_nonserial;
2188
2189 IF( l_serialized = FND_API.G_FALSE AND p_x_parts_rec.Installed_Instance_Id IS NOT NULL) THEN
2190 OPEN get_curr_quantity_csr(p_x_parts_rec.Installed_Instance_Id);
2191 FETCH get_curr_quantity_csr INTO l_curr_job_qty;
2192 CLOSE get_curr_quantity_csr;
2193 END IF;
2194
2195 IF( l_serialized = FND_API.G_FALSE
2196 AND l_curr_job_qty <> p_x_parts_rec.Installed_Quantity)THEN
2197 -- create new instance with remaining qty
2198 l_remaining_qty := l_curr_job_qty - p_x_parts_rec.Installed_Quantity;
2199 create_similar_instance(
2200 p_source_instance_id => p_x_parts_rec.Installed_Instance_Id,
2201 p_dest_quantity => l_remaining_qty,
2202 p_dest_wip_job_id => l_wip_job_id,
2203 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2204 x_dest_instance_id => l_dest_instance_id,
2205 x_return_status => x_return_status
2206 );
2207 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2208 RAISE FND_API.G_EXC_ERROR;
2209 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2211 END IF;
2212 IF G_DEBUG = 'Y' THEN
2213 AHL_DEBUG_PUB.debug('Sunil 1 p_x_parts_rec.Installed_Instance_Id : ' || p_x_parts_rec.Installed_Instance_Id);
2214 AHL_DEBUG_PUB.debug('Sunil p_x_parts_rec.Installed_Quantity ' || p_x_parts_rec.Installed_Quantity);
2215 END IF;
2216 -- update installed instance qty to equal to installed qty
2217 l_instance_rec.instance_id := p_x_parts_rec.Installed_Instance_Id;
2218 l_instance_rec.quantity := p_x_parts_rec.Installed_Quantity;
2219 l_instance_rec.Install_Date := p_x_parts_rec.Installation_Date;
2220
2221 OPEN csi_item_instance_csr(p_x_parts_rec.Installed_Instance_Id);
2222 FETCH csi_item_instance_csr INTO p_x_parts_rec.Installed_Instance_Num
2223 ,l_instance_rec.object_version_number;
2224 CLOSE csi_item_instance_csr;
2225 update_csi_item_instance(
2226 p_instance_rec => l_instance_rec,
2227 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2228 x_return_status => x_return_status
2229 );
2230 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2231 RAISE FND_API.G_EXC_ERROR;
2232 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2234 END IF;
2235
2236 l_instance_rec.instance_id := NULL;
2237 l_instance_rec.quantity := NULL;
2238 l_instance_rec.Install_Date := NULL;
2239 l_instance_rec.object_version_number := NULL;
2240
2241 IF G_DEBUG = 'Y' THEN
2242 AHL_DEBUG_PUB.debug('Sunil : Updated installed instance qty to : ' || p_x_parts_rec.Installed_Quantity);
2243
2244 END IF;
2245 END IF;
2246
2247 IF( l_serialized = FND_API.G_TRUE OR p_x_parts_rec.Removed_Instance_Id IS NULL)THEN
2248 csi_ii_relationships_pub.create_relationship(
2249 p_api_version => 1.0
2250 ,p_relationship_tbl => l_csi_relationship_tbl
2251 ,p_txn_rec => p_x_csi_transaction_rec
2252 ,x_return_status => x_return_status
2253 ,x_msg_count => l_msg_count
2254 ,x_msg_data => l_msg_data);
2255 ELSIF ( l_serialized = FND_API.G_FALSE AND p_x_parts_rec.Removed_Instance_Id IS NOT NULL)THEN
2256
2257 -- update qty for wo to 0
2258 l_instance_rec.instance_id := p_x_parts_rec.Installed_Instance_Id;
2259 l_instance_rec.quantity := 0 ;
2260 --l_instance_rec.ACTIVE_END_DATE := SYSDATE;
2261
2262 OPEN csi_item_instance_csr(p_x_parts_rec.Installed_Instance_Id);
2263 FETCH csi_item_instance_csr INTO p_x_parts_rec.Installed_Instance_Num
2264 ,l_instance_rec.object_version_number;
2265 CLOSE csi_item_instance_csr;
2266 update_csi_item_instance(
2267 p_instance_rec => l_instance_rec,
2268 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2269 x_return_status => x_return_status
2270 );
2271
2272
2273 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2274 RAISE FND_API.G_EXC_ERROR;
2275 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2277 END IF;
2278
2279 l_instance_rec.instance_id := NULL;
2280 l_instance_rec.quantity := NULL;
2281 l_instance_rec.ACTIVE_END_DATE := NULL;
2282 l_instance_rec.object_version_number := NULL;
2283
2284
2285 IF G_DEBUG = 'Y' THEN
2286 AHL_DEBUG_PUB.debug('Sunil : Updated fake installed instance qty to : 0 ');
2287 END IF;
2288
2289 OPEN get_curr_quantity_csr(p_x_parts_rec.Removed_Instance_Id);
2290 FETCH get_curr_quantity_csr INTO l_curr_config_qty;
2291 CLOSE get_curr_quantity_csr;
2292 -- up config qty
2293 p_x_parts_rec.Installed_Instance_Id := p_x_parts_rec.Removed_Instance_Id;
2294 l_instance_rec.instance_id := p_x_parts_rec.Installed_Instance_Id;
2295 l_instance_rec.quantity := l_curr_config_qty + p_x_parts_rec.Installed_Quantity;
2296 l_instance_rec.Install_Date := p_x_parts_rec.Installation_Date;
2297
2298
2299 IF G_DEBUG = 'Y' THEN
2300 AHL_DEBUG_PUB.debug('Sunil2 : p_x_parts_rec.Removed_Instance_Id : ' || p_x_parts_rec.Removed_Instance_Id);
2301 AHL_DEBUG_PUB.debug('Sunil2 : net config qty : ' || l_instance_rec.quantity);
2302 END IF;
2303
2304 OPEN csi_item_instance_csr(p_x_parts_rec.Installed_Instance_Id);
2305 FETCH csi_item_instance_csr INTO p_x_parts_rec.Installed_Instance_Num
2306 ,l_instance_rec.object_version_number;
2307 CLOSE csi_item_instance_csr;
2308 update_csi_item_instance(
2309 p_instance_rec => l_instance_rec,
2310 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2311 x_return_status => x_return_status
2312 );
2313
2314 IF G_DEBUG = 'Y' THEN
2315 AHL_DEBUG_PUB.debug('Sunil : Updated qty on config to : ' || l_instance_rec.quantity);
2316 END IF;
2317
2318 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2319 RAISE FND_API.G_EXC_ERROR;
2320 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2322 END IF;
2323
2324 l_instance_rec.instance_id := NULL;
2325 l_instance_rec.quantity := NULL;
2326 l_instance_rec.Install_Date := NULL;
2327 l_instance_rec.object_version_number := NULL;
2328
2329 IF G_DEBUG = 'Y' THEN
2330 AHL_DEBUG_PUB.debug('Sunil : completed partial install : ');
2331 END IF;
2332
2333
2334 END IF;
2335
2336 -- Removing a part
2337 ELSIF (p_x_parts_rec.operation_type = 'D' ) then
2338 -- dbms_output.put_line('IB processing- remove');
2339 IF G_DEBUG = 'Y' THEN
2340 AHL_DEBUG_PUB.debug('IB Processing- Remove');
2341 END IF;
2342 --Test that the part removed is ib config indeed
2343 l_unit_config_header_name:= AHL_UMP_UTIL_PKG.get_UnitName(p_x_parts_rec.removed_instance_id);
2344
2345
2346 IF (l_unit_config_header_name is not null ) then
2347 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_RMV_PRT_INVALID');
2348 FND_MSG_PUB.ADD;
2349 RAISE FND_API.G_EXC_ERROR;
2350 END IF;
2351
2352 l_serialized := FND_API.G_FALSE;
2353 OPEN check_inst_nonserial(p_x_parts_rec.Removed_Instance_Id);
2354 FETCH check_inst_nonserial INTO l_junk;
2355 IF(check_inst_nonserial%NOTFOUND)THEN
2356 l_serialized := FND_API.G_TRUE;
2357 END IF;
2358 CLOSE check_inst_nonserial;
2359
2360 IF( l_serialized = FND_API.G_FALSE) THEN
2361 OPEN get_curr_quantity_csr(p_x_parts_rec.Removed_Instance_Id);
2362 FETCH get_curr_quantity_csr INTO l_curr_config_qty;
2363 CLOSE get_curr_quantity_csr;
2364 END IF;
2365
2366 IF( l_serialized = FND_API.G_TRUE OR p_x_parts_rec.Removed_Quantity = l_curr_config_qty)THEN
2367
2368 csi_ii_relationships_pub.expire_relationship(
2369 p_api_version => 1.0
2370 ,p_relationship_rec => l_csi_relationship_rec
2371 ,p_txn_rec => p_x_csi_transaction_rec
2372 ,x_instance_id_lst=> l_instance_id_lst-- csi_datastructures_pub.id_tbl,
2373 ,x_return_status => x_return_status
2374 ,x_msg_count => l_msg_count
2375 ,x_msg_data => l_msg_data );
2376 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2377 RAISE FND_API.G_EXC_ERROR;
2378 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2380 END IF;
2381
2382 update_item_location(
2383 p_x_parts_rec => p_x_parts_rec,
2384 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2385 x_return_status => x_return_status);
2386 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2387 RAISE FND_API.G_EXC_ERROR;
2388 END IF;
2389 l_move_item_instance_tbl(1).instance_id := p_x_parts_rec.Removed_Instance_Id;
2390 l_move_item_instance_tbl(1).quantity := p_x_parts_rec.Removed_Quantity;
2391 l_move_item_instance_tbl(1).to_workorder_id := p_x_parts_rec.workorder_id;
2392
2393 move_instance_location(
2394 P_API_Version => 1.0,
2395 P_Init_Msg_List => Fnd_API.G_False,
2396 P_Commit => Fnd_API.G_False,
2397 P_Validation_Level => Fnd_API.G_Valid_Level_Full,
2398 p_module_type => 'API',
2399 p_default => FND_API.G_TRUE,
2400 p_move_item_instance_tbl => l_move_item_instance_tbl,
2401 x_return_status => x_return_status,
2402 x_msg_count => l_msg_count,
2403 x_msg_data => l_msg_data);
2404 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2405 RAISE FND_API.G_EXC_ERROR;
2406 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2408 END IF;
2409 IF( l_serialized = FND_API.G_FALSE)THEN
2410 OPEN removal_instance_id(p_x_parts_rec.Removed_Instance_Id, l_wip_job_id);
2411 FETCH removal_instance_id INTO l_final_removed_inst_id;
2412 IF(removal_instance_id%FOUND)THEN
2413 p_x_parts_rec.Removed_Instance_Id := l_final_removed_inst_id;
2414 END IF;
2415 CLOSE removal_instance_id;
2416 END IF;
2417 IF G_DEBUG = 'Y' THEN
2418 AHL_DEBUG_PUB.debug('1 final p_x_parts_rec.Removed_Instance_Id ' || p_x_parts_rec.Removed_Instance_Id);
2419 AHL_DEBUG_PUB.debug('l_final_removed_inst_id ' || l_final_removed_inst_id);
2420 END IF;
2421 ELSE -- non-serialized incomplete removal
2422
2423 csi_ii_relationships_pub.expire_relationship(
2424 p_api_version => 1.0
2425 ,p_relationship_rec => l_csi_relationship_rec
2426 ,p_txn_rec => p_x_csi_transaction_rec
2427 ,x_instance_id_lst=> l_instance_id_lst-- csi_datastructures_pub.id_tbl,
2428 ,x_return_status => x_return_status
2429 ,x_msg_count => l_msg_count
2430 ,x_msg_data => l_msg_data );
2431 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2432 RAISE FND_API.G_EXC_ERROR;
2433 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2435 END IF;
2436
2437 update_item_location(
2438 p_x_parts_rec => p_x_parts_rec,
2439 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2440 x_return_status => x_return_status);
2441
2442 l_remaining_qty := l_curr_config_qty - p_x_parts_rec.Removed_Quantity;
2443 -- update configuration instance with net quantity
2444 l_instance_rec.instance_id := p_x_parts_rec.Removed_Instance_Id;
2445 l_instance_rec.quantity := l_remaining_qty;
2446 OPEN csi_item_instance_csr(p_x_parts_rec.Removed_Instance_Id);
2447 FETCH csi_item_instance_csr INTO p_x_parts_rec.Removed_Instance_Num
2448 ,l_instance_rec.object_version_number;
2449 CLOSE csi_item_instance_csr;
2450
2451 update_csi_item_instance(
2452 p_instance_rec => l_instance_rec,
2453 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2454 x_return_status => x_return_status
2455 );
2456
2457 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2458 RAISE FND_API.G_EXC_ERROR;
2459 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2461 END IF;
2462 l_instance_rec.instance_id := NULL;
2463 l_instance_rec.quantity := NULL;
2464 l_instance_rec.object_version_number := NULL;
2465 --create same instance with removed qty
2466 create_similar_instance(
2467 p_source_instance_id => p_x_parts_rec.Removed_Instance_Id,
2468 p_dest_quantity => p_x_parts_rec.Removed_Quantity,
2469 p_dest_wip_job_id => l_wip_job_id,
2470 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2471 x_dest_instance_id => l_dest_instance_id,
2472 x_return_status => x_return_status
2473 );
2474 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2475 RAISE FND_API.G_EXC_ERROR;
2476 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2478 END IF;
2479
2480
2481 l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
2482 l_csi_relationship_rec.object_id := p_x_parts_rec.Parent_Installed_Instance_Id;
2483 l_csi_relationship_rec.subject_id := p_x_parts_rec.Removed_Instance_Id;
2484 l_csi_relationship_rec.relationship_id := NULL;
2485 l_csi_relationship_rec.object_version_number := NULL;
2486
2487 l_csi_relationship_tbl(1) := l_csi_relationship_rec;
2488
2489 csi_ii_relationships_pub.create_relationship(
2490 p_api_version => 1.0
2491 ,p_relationship_tbl => l_csi_relationship_tbl
2492 ,p_txn_rec => p_x_csi_transaction_rec
2493 ,x_return_status => x_return_status
2494 ,x_msg_count => l_msg_count
2495 ,x_msg_data => l_msg_data);
2496
2497 IF G_DEBUG = 'Y' THEN
2498 AHL_DEBUG_PUB.debug('After making a call to csi_ii_relationships_pub.create_relationship.');
2499 END IF;
2500
2501 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2502 RAISE FND_API.G_EXC_ERROR;
2503 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2505 END IF;
2506
2507 p_x_parts_rec.Removed_Instance_Id := l_dest_instance_id;
2508 IF G_DEBUG = 'Y' THEN
2509 AHL_DEBUG_PUB.debug('update_item_location :: ' ||l_dest_instance_id);
2510 END IF;
2511 /*update_item_location(
2512 p_x_parts_rec => p_x_parts_rec,
2513 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2514 x_return_status => x_return_status);
2515 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2516 RAISE FND_API.G_EXC_ERROR;
2517 END IF;*/
2518
2519 l_move_item_instance_tbl(1).instance_id := p_x_parts_rec.Removed_Instance_Id;
2520 l_move_item_instance_tbl(1).quantity := p_x_parts_rec.Removed_Quantity;
2521 l_move_item_instance_tbl(1).to_workorder_id := p_x_parts_rec.workorder_id;
2522 IF G_DEBUG = 'Y' THEN
2523 AHL_DEBUG_PUB.debug('moving it');
2524 END IF;
2525 move_instance_location(
2526 P_API_Version => 1.0,
2527 P_Init_Msg_List => Fnd_API.G_False,
2528 P_Commit => Fnd_API.G_False,
2529 P_Validation_Level => Fnd_API.G_Valid_Level_Full,
2530 p_module_type => 'API',
2531 p_default => FND_API.G_TRUE,
2532 p_move_item_instance_tbl => l_move_item_instance_tbl,
2533 x_return_status => x_return_status,
2534 x_msg_count => l_msg_count,
2535 x_msg_data => l_msg_data);
2536 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2537 RAISE FND_API.G_EXC_ERROR;
2538 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2540 END IF;
2541
2542 OPEN removal_instance_id(p_x_parts_rec.Removed_Instance_Id, l_wip_job_id);
2543 FETCH removal_instance_id INTO l_final_removed_inst_id;
2544 IF(removal_instance_id%FOUND)THEN
2545 p_x_parts_rec.Removed_Instance_Id := l_final_removed_inst_id;
2546 END IF;
2547 CLOSE removal_instance_id;
2548 --p_x_parts_rec.Removed_Instance_Id := 274689;
2549 IF G_DEBUG = 'Y' THEN
2550 AHL_DEBUG_PUB.debug('2 final p_x_parts_rec.Removed_Instance_Id ' || p_x_parts_rec.Removed_Instance_Id);
2551 AHL_DEBUG_PUB.debug('l_final_removed_inst_id ' || l_final_removed_inst_id);
2552 END IF;
2553
2554 END IF;
2555
2556 --Swapping a part
2557 ELSIF (p_x_parts_rec.operation_type = 'M' ) then
2558 --Test that the part removed is ib config indeed
2559 l_unit_config_header_name:= AHL_UMP_UTIL_PKG.get_UnitName(p_x_parts_rec.removed_instance_id);
2560
2561 IF (l_unit_config_header_name is not null ) then
2562 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_RMV_PRT_INVALID');
2563 FND_MSG_PUB.ADD;
2564 --RAISE FND_API.G_EXC_ERROR;
2565 end if;
2566 IF G_DEBUG = 'Y' THEN
2567 AHL_DEBUG_PUB.debug('IB Processing- Swap');
2568 END IF;
2569 csi_ii_relationships_pub.update_relationship(
2570 p_api_version => 1.0
2571 ,p_relationship_tbl => l_csi_relationship_tbl
2572
2573 ,p_txn_rec => p_x_csi_transaction_rec
2574
2575 ,x_return_status => x_return_status
2576
2577 ,x_msg_count => l_msg_count
2578 ,x_msg_data => l_msg_data);
2579 l_move_item_instance_tbl(1).instance_id := p_x_parts_rec.Removed_Instance_Id;
2580 l_move_item_instance_tbl(1).quantity := p_x_parts_rec.Removed_Quantity;
2581 l_move_item_instance_tbl(1).to_workorder_id := p_x_parts_rec.workorder_id;
2582 IF G_DEBUG = 'Y' THEN
2583 AHL_DEBUG_PUB.debug('moving it');
2584 END IF;
2585
2586 update_item_location(
2587 p_x_parts_rec => p_x_parts_rec,
2588 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
2589 x_return_status => x_return_status);
2590
2591 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2592 RAISE FND_API.G_EXC_ERROR;
2593 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2595 END IF;
2596
2597 move_instance_location(
2598 P_API_Version => 1.0,
2599 P_Init_Msg_List => Fnd_API.G_False,
2600 P_Commit => Fnd_API.G_False,
2601 P_Validation_Level => Fnd_API.G_Valid_Level_Full,
2602 p_module_type => 'API',
2603 p_default => FND_API.G_TRUE,
2604 p_move_item_instance_tbl => l_move_item_instance_tbl,
2605 x_return_status => x_return_status,
2606 x_msg_count => l_msg_count,
2607 x_msg_data => l_msg_data);
2608 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
2609 RAISE FND_API.G_EXC_ERROR;
2610 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2612 END IF;
2613
2614 end if;
2615
2616 END Process_IB;
2617
2618 Procedure Update_item_location(p_x_parts_rec IN out nocopy ahl_parts_rec_type,
2619 p_x_csi_transaction_rec IN out nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
2620
2621 X_Return_Status Out NOCOPY Varchar2)
2622 IS
2623 --
2624 l_wip_entity_type number;
2625 l_wip_entity_id number;
2626 l_instance_rec csi_datastructures_pub.instance_rec;
2627 l_extend_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2628 l_party_tbl csi_datastructures_pub.party_tbl;
2629 l_account_tbl csi_datastructures_pub.party_account_tbl;
2630 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
2631 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
2632 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
2633 l_instance_id_lst csi_datastructures_pub.id_tbl;
2634 l_msg_count number;
2635 l_msg_data varchar2(2000);
2636
2637 -- For wip_entity_id for the workorder
2638 CURSOR ahl_wip_entity_csr(p_wo_id in Number) IS
2639 Select AHL.wip_entity_id, WIP.entity_type
2640 --FROM ahl_workorders_v AHL, wip_entities WIP
2641 FROM ahl_search_workorders_v AHL, wip_entities WIP
2642 WHERE AHL.workorder_id = p_wo_id
2643 and WIP.wip_entity_id = AHL.wip_entity_id;
2644
2645 --For the updated object_version number from csi_item_isntances
2646 CURSOR ahl_obj_ver_csr(p_instance_id in Number) IS
2647 select object_Version_number
2648 from csi_item_instances
2649 where instance_id = p_instance_id;
2650
2651 --to populate csi_transaction record
2652 CURSOR ahl_wip_location_csr IS
2653 select wip_location_id
2654 from csi_install_parameters ;
2655
2656 BEGIN
2657
2658 -- get wip_entity_id, wip_entity_type for the workorder
2659 -- dbms_output.put_line('Calling update_removed item- ahl_wp_entity_csr');
2660 OPEN ahl_wip_entity_csr(p_x_parts_rec.workorder_id);
2661 FETCH ahl_wip_entity_csr INTO l_wip_entity_id,l_wip_entity_type;
2662 IF (ahl_wip_entity_csr%NOTFOUND) THEN
2663 CLOSE ahl_wip_entity_csr;
2664 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WIP_ENTITY_MISSING');
2665 FND_MESSAGE.Set_Token('WOID',p_x_parts_rec.workorder_id);
2666 FND_MSG_PUB.ADD;
2667 else
2668 CLOSE ahl_wip_entity_csr;
2669 END IF;
2670
2671 --get the updated object_version number from csi_item_isntances
2672 OPEN ahl_obj_ver_csr(p_x_parts_rec.removed_instance_id);
2673 FETCH ahl_obj_ver_csr INTO p_x_parts_rec.removed_instance_obj_ver_num;
2674 IF (ahl_obj_ver_csr%NOTFOUND) THEN
2675 FND_MESSAGE.Set_Name('AHL','AHL_PRD_REMOVED_INSTANCE_INVALID');
2676 FND_MESSAGE.Set_Token('INST',p_x_parts_rec.removed_instance_id);
2677 FND_MSG_PUB.ADD;
2678 CLOSE ahl_obj_ver_csr;
2679 else
2680 CLOSE ahl_obj_ver_csr;
2681
2682 END IF;
2683
2684 -- populate l_instance_rec
2685 l_instance_rec.INSTANCE_ID := p_x_parts_rec.removed_instance_id;
2686 l_instance_rec.LOCATION_TYPE_CODE := 'WIP';
2687 l_instance_rec.WIP_JOB_ID := l_wip_entity_id;
2688 l_instance_rec.OBJECT_VERSION_NUMBER := p_x_parts_rec.removed_instance_obj_ver_num;
2689 l_instance_rec.instance_usage_code := 'IN_WIP';
2690
2691
2692 --get location id
2693 OPEN ahl_wip_location_csr();
2694 FETCH ahl_wip_location_csr INTO l_instance_rec.LOCATION_ID ;
2695 CLOSE ahl_wip_location_csr;
2696
2697 CSI_ITEM_INSTANCE_PUB. update_item_instance (
2698 p_api_version =>1.0
2699 ,p_commit => fnd_api.g_false
2700 ,p_init_msg_list => fnd_api.g_false
2701 ,p_validation_level => fnd_api.g_valid_level_full
2702 ,p_instance_rec => l_instance_rec
2703 ,p_ext_attrib_values_tbl=>l_extend_attrib_values_tbl
2704 ,p_party_tbl =>l_party_tbl
2705 ,p_account_tbl => l_account_tbl
2706 ,p_pricing_attrib_tbl => l_pricing_attrib_tbl
2707 ,p_org_assignments_tbl => l_org_assignments_tbl
2708 ,p_asset_assignment_tbl => l_asset_assignment_tbl
2709 ,p_txn_rec => p_x_csi_transaction_rec
2710 ,x_instance_id_lst => l_instance_id_lst
2711 ,x_return_status => x_return_status
2712 ,x_msg_count => l_msg_count
2713 ,x_msg_data => l_msg_data );
2714
2715 END Update_item_location;
2716
2717 --
2718 Procedure Process_material_txn(
2719 p_x_parts_rec in out nocopy Ahl_parts_rec_type,
2720 p_module_type in Varchar2,
2721 X_Return_Status Out NOCOPY Varchar2)
2722 IS
2723 --
2724 l_mtl_txn_tbl AHL_PRD_MTLTXN_PVT.Ahl_Mtltxn_Tbl_Type;
2725 x_ahl_mtl_txn_id_tbl AHL_PRD_MTLTXN_PVT.Ahl_Mtl_Txn_Id_tbl;
2726 l_inventory_item_id number;
2727 l_organization_id number;
2728 L_revision varchar2(3);
2729 l_quantity number;
2730 l_uom varchar2(3);
2731 --L_wip_entity_id number;
2732 l_serial_number varchar2(30);
2733 l_lot_number mtl_lot_numbers.lot_number%TYPE;
2734 l_msg_count number;
2735 l_msg_data varchar2(2000);
2736
2737 --
2738 -- For MTL api
2739 CURSOR ahl_mtl_txn_param_csr (p_wo_id in number, p_instance_id number) is
2740 SELECT CSI.INVENTORY_ITEM_ID, AHL.organization_id
2741 , CSI.inventory_revision revision, CSI.quantity, CSI.unit_of_measure,
2742 CSI.serial_number, CSI.lot_number
2743 --FROM ahl_workorders_v AHL, csi_item_instances CSI
2744 FROM ahl_workorder_tasks_v AHL, csi_item_instances CSI
2745 Where CSI.instance_id = p_instance_id
2746 --and csi.inv_organization_id = ahl.organization_id
2747 And AHL.workorder_id = p_wo_id;
2748 BEGIN
2749 -- dbms_output.put_line('inside material transaction');
2750 --Derive parameters to be passed to the API.
2751 OPEN ahl_mtl_txn_param_csr(p_x_parts_rec.workorder_id, p_x_parts_rec.removed_instance_id);
2752
2753 FETCH ahl_mtl_txn_param_csr INTO l_inventory_item_id, l_organization_id,
2754 L_revision, l_quantity, l_uom,
2755 l_serial_number, l_lot_number;
2756 IF (ahl_mtl_txn_param_csr%NOTFOUND) THEN
2757 CLOSE ahl_mtl_txn_param_csr;
2758 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INST_DATA_MISSING');
2759 FND_MESSAGE.Set_Token('WOID',p_x_parts_rec.workorder_id);
2760 FND_MSG_PUB.ADD;
2761 ELSE
2762 CLOSE ahl_mtl_txn_param_csr;
2763 END IF;
2764
2765 --Get wip_entity_type
2766 l_mtl_txn_tbl(1).Workorder_id := p_x_parts_rec. workorder_id;
2767 -- dbms_output.put_line('workorder '||l_mtl_txn_tbl(1).Workorder_id);
2768
2769 l_mtl_txn_tbl(1).Operation_Seq_Num := p_x_parts_rec.Operation_Sequence_Num;
2770 -- dbms_output.put_line('Operation_Seq_Num '||l_mtl_txn_tbl(1).Operation_Seq_Num);
2771
2772
2773 l_mtl_txn_tbl(1).INVENTORY_ITEM_ID := L_inventory_item_id;
2774 -- dbms_output.put_line('INVENTORY_ITEM_ID '||l_mtl_txn_tbl(1).INVENTORY_ITEM_ID);
2775
2776 l_mtl_txn_tbl(1).REVISION := L_revision;
2777 -- dbms_output.put_line('REVISION '||l_mtl_txn_tbl(1).REVISION);
2778 l_mtl_txn_tbl(1).ORGANIZATION_ID:= L_organization_id;
2779 -- dbms_output.put_line('ORGANIZATION_ID '||l_mtl_txn_tbl(1).ORGANIZATION_ID);
2780 --l_mtl_txn_tbl(1).CONDITION := p_x_parts_rec.condition_id;
2781 -- dbms_output.put_line('CONDITION '||l_mtl_txn_tbl(1).CONDITION);
2782
2783 --l_mtl_txn_tbl(1).SUBINVENTORY_NAME := p_x_parts_rec.subinventory_code;
2784
2785 -- dbms_output.put_line('SUBINVENTORY_NAME '||l_mtl_txn_tbl(1).SUBINVENTORY_NAME);
2786
2787 --l_mtl_txn_tbl(1).LOCATOR_ID :=p_x_parts_rec.locator_id;
2788 -- dbms_output.put_line('LOCATOR_ID '||l_mtl_txn_tbl(1).LOCATOR_ID);
2789
2790 --l_mtl_txn_tbl(1).LOCATOR_segments :=p_x_parts_rec.locator_code;
2791 -- dbms_output.put_line('LOCATOR_SEGMENTS '||l_mtl_txn_tbl(1).LOCATOR_SEGMENTS);
2792
2793 l_mtl_txn_tbl(1).QUANTITY := L_quantity;
2794 -- dbms_output.put_line('QUANTITY '||l_mtl_txn_tbl(1).QUANTITY);
2795
2796 l_mtl_txn_tbl(1).UOM := L_uom;
2797 -- dbms_output.put_line('UOM '||l_mtl_txn_tbl(1).UOM);
2798 l_mtl_txn_tbl(1).TRANSACTION_TYPE_ID:= WIP_CONSTANTS.RETCOMP_TYPE;
2799 -- dbms_output.put_line('TRANSACTION_TYPE_ID '||l_mtl_txn_tbl(1).TRANSACTION_TYPE_ID);
2800
2801 l_mtl_txn_tbl(1).TRANSACTION_REFERENCE := null;
2802 l_mtl_txn_tbl(1).SERIAL_NUMBER := L_serial_number;
2803 -- dbms_output.put_line('SERIAL_NUMBER '||l_mtl_txn_tbl(1).SERIAL_NUMBER);
2804
2805 l_mtl_txn_tbl(1).LOT_NUMBER := L_lot_number;
2806 -- dbms_output.put_line('LOT_NUMBER '||l_mtl_txn_tbl(1).LOT_NUMBER);
2807
2808
2809 --l_mtl_txn_tbl(1).PROBLEM_CODE:= p_x_parts_rec.problem_code;
2810 -- dbms_output.put_line('PROBLEM_CODE '||l_mtl_txn_tbl(1).PROBLEM_CODE);
2811
2812 -- l_mtl_txn_tbl(1).TARGET_VISIT_ID:= p_x_parts_rec.target_visit_id;
2813 -- dbms_output.put_line('TARGET_VISIT_ID '||l_mtl_txn_tbl(1).TARGET_VISIT_ID);
2814
2815 --l_mtl_txn_tbl(1).SR_SUMMARY := p_x_parts_rec.summary;
2816 -- dbms_output.put_line('SR_SUMMARY '||l_mtl_txn_tbl(1).SR_SUMMARY);
2817
2818 --l_mtl_txn_tbl(1).Qa_Collection_Id := p_x_parts_rec.collection_id;
2819 l_mtl_txn_tbl(1).Reason_Id := p_x_parts_rec.removal_reason_id;
2820
2821 AHL_PRD_MTLTXN_PVT.PERFORM_MTL_TXN
2822 (p_api_version => 1.0,
2823 p_module_type => null,--sending null because I am calling it internally
2824 p_create_sr => 'N',
2825 p_x_ahl_mtltxn_tbl=> l_mtl_txn_tbl,
2826 x_return_status => x_return_status,
2827 x_msg_count=> l_msg_count,
2828 x_msg_data => l_msg_data );
2829
2830 --IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2831 -- p_x_parts_rec.Material_txn_id := l_mtl_txn_tbl(1). Ahl_mtltxn_Id;
2832 --END IF;
2833 END Process_material_txn;
2834
2835 /*
2836 -- Service request processing
2837 Procedure Process_SR(
2838 p_x_parts_rec_tbl In Out Nocopy Ahl_parts_tbl_type,
2839 p_module_type In Varchar2,
2840 X_Return_Status Out NOCOPY Varchar2)
2841 IS
2842 l_sr_task_tbl AHL_PRD_NONROUTINE_PVT.sr_task_tbl_type;
2843 l_msg_count number;
2844 l_msg_data varchar2(2000);
2845
2846 BEGIN
2847
2848 -- Loop through the table record and form table list to call SR API.
2849 IF ( p_x_parts_rec_tbl.COUNT > 0) THEN
2850 FOR i IN p_x_parts_rec_tbl.FIRST..p_x_parts_rec_tbl.LAST LOOP
2851
2852 ---********* Call SR if part condition is unserviceable *********** ------
2853 IF ((p_x_parts_rec_tbl(i).operation_type='D' or p_x_parts_rec_tbl(i).operation_type='M') AND
2854 ((fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE') is not null AND
2855 p_x_parts_rec_tbl(i).Condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE'))
2856 OR ( fnd_profile.value('AHL_MTL_MAT_STATUS_MRB') is not null and
2857 p_x_parts_rec_tbl(i).Condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'))))
2858 THEN
2859 --Summary is mandatory for SR API
2860 IF (p_x_parts_rec_tbl(i).summary is null) then
2861 Fnd_Message.SET_NAME('AHL','AHL_PRD_NRJ_SUMMARY_REQ');
2862 FND_MSG_PUB.ADD;
2863 END IF;
2864
2865 -- Populate sr_task_tbl
2866 l_sr_task_tbl(i).Request_date:= sysdate;
2867 l_sr_task_tbl(i).Summary := p_x_parts_rec_tbl(i).summary;
2868 -- dbms_output.put_line('SR- summary '|| p_x_parts_rec_tbl(i).summary);
2869
2870 l_sr_task_tbl(i).Instance_id:= p_x_Parts_rec_tbl(i).removed_instance_id;
2871 -- dbms_output.put_line('SR- Instance_id '|| p_x_Parts_rec_tbl(i).removed_instance_id);
2872
2873 l_sr_task_tbl(i).Instance_number:= p_x_Parts_rec_tbl(i).removed_instance_num;
2874 -- dbms_output.put_line('SR- Instance_num '|| p_x_Parts_rec_tbl(i).removed_instance_num);
2875
2876
2877 l_sr_task_tbl(i).Problem_code := p_x_parts_rec_tbl(i).problem_code;
2878 -- dbms_output.put_line('SR- Problem_code '|| p_x_Parts_rec_tbl(i).Problem_code);
2879
2880 l_sr_task_tbl(i).Problem_meaning := p_x_Parts_rec_tbl(i).problem_meaning;
2881 -- dbms_output.put_line('SR- Problem_meaning '|| p_x_Parts_rec_tbl(i).Problem_meaning);
2882
2883
2884 l_sr_task_tbl(i).Visit_id:= p_x_Parts_rec_tbl(i).Target_Visit_Id;
2885 -- dbms_output.put_line('SR- Visit_id '|| p_x_Parts_rec_tbl(i).target_Visit_id);
2886
2887
2888 l_sr_task_tbl(i).Visit_Number := p_x_parts_rec_tbl(i).Target_Visit_Num ;
2889 -- dbms_output.put_line('SR- Visit_Number '|| p_x_Parts_rec_tbl(i).Target_Visit_Num);
2890 l_sr_task_tbl(i).Originating_wo_id:= p_x_Parts_rec_tbl(i).workorder_id;
2891 -- dbms_output.put_line('SR- Originating_wo_id '|| p_x_Parts_rec_tbl(i).workorder_id);
2892
2893 l_sr_task_tbl(i).Operation_type := 'CREATE' ;
2894
2895 l_sr_task_tbl(i).Severity_id := p_x_Parts_rec_tbl(i).severity_id;
2896 -- dbms_output.put_line('SR- Severity_id '|| p_x_Parts_rec_tbl(i).Severity_id);
2897
2898
2899 l_sr_task_tbl(i).Severity_name := p_x_Parts_rec_tbl(i).severity_name;
2900
2901 -- dbms_output.put_line('SR- Severity_name '|| p_x_Parts_rec_tbl(i).Severity_name);
2902 l_sr_task_tbl(i).duration := p_x_Parts_rec_tbl(i).estimated_duration;
2903
2904 -- dbms_output.put_line('SR- duration '|| p_x_Parts_rec_tbl(i).estimated_duration);
2905 l_sr_task_tbl(i).source_program_code := 'AHL_NONROUTINE';
2906
2907 END IF; -- part condition.
2908 END LOOP;
2909 END IF;
2910
2911 --Calling Service Request API--
2912 AHL_PRD_NONROUTINE_PVT.process_nonroutine_job (
2913 p_api_version => 1.0,
2914 p_commit => Fnd_Api.g_false,
2915 p_module_type => p_module_type,
2916 x_return_status => x_return_status,
2917 x_msg_count => l_msg_count,
2918 x_msg_data => l_msg_data,
2919 p_x_sr_task_tbl => l_sr_task_tbl);
2920
2921
2922 -- dbms_output.put_line('Sangita-x_return_status after SR'|| x_return_status);
2923
2924
2925 IF G_DEBUG = 'Y' THEN
2926 AHL_DEBUG_PUB.debug('Sangita-x_return_status after SR'|| x_return_status);
2927 END IF;
2928
2929
2930 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2931
2932 IF ( l_sr_task_tbl.COUNT > 0) THEN
2933 FOR i IN l_sr_task_tbl.FIRST..l_sr_task_tbl.LAST LOOP
2934
2935 -- dbms_output.put_line('l_sr_task_tbl(1).incident_id='|| l_sr_task_tbl(1).incident_id);
2936 p_x_parts_rec_tbl(i).nonroutine_wo_id := l_sr_task_tbl(i).Nonroutine_wo_id;
2937
2938 --- ********* UPDATE AHL_PARTS_CHANGE TABLE AFTER EVERYTHING IS SUCCESFUL ---------
2939
2940 IF ( p_x_parts_rec_tbl(i).nonroutine_wo_id is not null ) then
2941 -- dbms_output.put_line('inside update part changes call');
2942 IF G_DEBUG = 'Y' THEN
2943 AHL_DEBUG_PUB.debug('inside update part changes call');
2944 END IF;
2945
2946 AHL_PART_CHANGES_PKG.update_row(
2947 X_PART_CHANGE_ID => p_x_parts_rec_tbl(i).part_change_txn_id,
2948 X_UNIT_CONFIG_HEADER_ID=>p_x_parts_rec_tbl(i).unit_config_header_id,
2949 X_REMOVED_INSTANCE_ID => p_x_parts_rec_tbl(i).removed_instance_id,
2950 X_MC_RELATIONSHIP_ID => p_x_parts_rec_tbl(i).mc_relationship_id,
2951 X_REMOVAL_CODE => p_x_parts_rec_tbl(i).removal_code,
2952 X_STATUS_ID => p_x_parts_rec_tbl(i).Condition_id,
2953 X_REMOVAL_REASON_ID => p_x_parts_rec_tbl(i).removal_reason_id,
2954 X_INSTALLED_INSTANCE_ID => p_x_parts_rec_tbl(i).installed_instance_id,
2955 X_WORKORDER_OPERATION_ID => p_x_parts_rec_tbl(i).workorder_operation_id,
2956 X_OBJECT_VERSION_NUMBER => 2,
2957 X_COLLECTION_ID => p_x_parts_rec_tbl(i).collection_id,
2958 X_WORKORDER_MTL_TXN_ID => p_x_parts_rec_tbl(i).material_txn_id,
2959 X_NON_ROUTINE_WORKORDER_ID => p_x_parts_rec_tbl(i).nonroutine_wo_id,
2960 X_REMOVAL_DATE => p_x_parts_rec_tbl(i).removal_date,
2961 X_INSTALLATION_DATE => p_x_parts_rec_tbl(i).INSTALLATION_DATE,
2962 X_LAST_UPDATE_DATE => sysdate,
2963 X_LAST_UPDATED_BY => fnd_global.user_id,
2964 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
2965 X_ATTRIBUTE_CATEGORY => null,
2966 X_ATTRIBUTE1 => null,
2967 X_ATTRIBUTE2 => null,
2968 X_ATTRIBUTE3 => null,
2969 X_ATTRIBUTE4 => null,
2970 X_ATTRIBUTE5 => null,
2971 X_ATTRIBUTE6 => null,
2972 X_ATTRIBUTE7 => null,
2973 X_ATTRIBUTE8 => null,
2974 X_ATTRIBUTE9 => null,
2975 X_ATTRIBUTE10 => null,
2976 X_ATTRIBUTE11 => null,
2977 X_ATTRIBUTE12 => null,
2978 X_ATTRIBUTE13 => null,
2979 X_ATTRIBUTE14 => null,
2980 X_ATTRIBUTE15 => null
2981 );
2982
2983 END IF; -- nonroutine wo exists.
2984 END LOOP;
2985 END IF;
2986 END IF;
2987
2988 END Process_SR;
2989 */
2990
2991 -- Get Material Issue transaction, if exists, when installing an item.
2992 PROCEDURE Get_Issue_Mtl_Txn (p_workorder_id IN NUMBER,
2993 p_Item_Instance_Id IN NUMBER,
2994 x_issue_mtl_txn_id OUT NOCOPY NUMBER)
2995 IS
2996 -- To get latest material issue txn for the item instance.
2997 CURSOR ahl_mtl_txn_csr (p_Item_Instance_Id IN NUMBER,
2998 p_workorder_id IN NUMBER) IS
2999 SELECT workorder_mtl_txn_id
3000 FROM ahl_workorder_mtl_txns mt, ahl_workorder_operations woo
3001 WHERE mt.WORKORDER_OPERATION_ID = woo.WORKORDER_OPERATION_ID
3002 AND TRANSACTION_TYPE_ID = 35 -- issues.
3003 AND woo.workorder_id = p_workorder_id
3004 ORDER by mt.TRANSACTION_DATE DESC, mt.LAST_UPDATE_DATE DESC;
3005
3006
3007 BEGIN
3008 -- get the latest material issue record.
3009 OPEN ahl_mtl_txn_csr(p_Item_Instance_Id, p_workorder_id);
3010 FETCH ahl_mtl_txn_csr INTO x_issue_mtl_txn_id;
3011 IF (ahl_mtl_txn_csr%NOTFOUND) THEN
3012 x_issue_mtl_txn_id := NULL;
3013 END IF;
3014 CLOSE ahl_mtl_txn_csr;
3015
3016 END Get_Issue_Mtl_Txn;
3017
3018 -- Update Material Return txn if item returned using Material Transactions.
3019 PROCEDURE Update_Material_Return (p_return_mtl_txn_id IN NUMBER,
3020 p_workorder_id IN NUMBER,
3021 p_Item_Instance_Id IN NUMBER,
3022 x_return_status OUT NOCOPY VARCHAR2)
3023 IS
3024 -- To get latest removal or swap parts change txn.
3025 CURSOR ahl_part_chg_csr (p_Item_Instance_Id IN NUMBER,
3026 p_workorder_id IN NUMBER) IS
3027 SELECT pc.part_change_id, pc.object_version_number
3028 FROM ahl_part_changes pc, ahl_workorder_operations woo
3029 WHERE pc.WORKORDER_OPERATION_ID = woo.WORKORDER_OPERATION_ID
3030 AND pc.removed_instance_id = p_item_instance_id
3031 AND woo.workorder_id = p_workorder_id
3032 AND pc.return_mtl_txn_id IS NULL
3033 ORDER by pc.LAST_UPDATE_DATE DESC
3034 FOR UPDATE OF return_mtl_txn_id;
3035
3036 l_part_change_id NUMBER;
3037 l_object_version_number NUMBER;
3038 l_found BOOLEAN;
3039
3040 BEGIN
3041
3042 l_found := FALSE;
3043 x_return_status := FND_API.G_RET_STS_SUCCESS;
3044
3045 -- get the latest material issue record.
3046 OPEN ahl_part_chg_csr(p_Item_Instance_Id, p_workorder_id);
3047 FETCH ahl_part_chg_csr INTO l_part_change_id, l_object_version_number;
3048 IF (ahl_part_chg_csr%FOUND) THEN
3049 l_found := TRUE;
3050 END IF;
3051
3052 IF (l_found) THEN
3053 UPDATE ahl_part_changes
3054 SET return_mtl_txn_id = p_return_mtl_txn_id,
3055 object_version_number = l_object_version_number + 1
3056 WHERE CURRENT OF ahl_part_chg_csr;
3057 END IF;
3058
3059 CLOSE ahl_part_chg_csr;
3060
3061 END Update_Material_Return;
3062
3063 -- Added for ER 5854712 - locator for servicable parts.
3064 -- Procedure will return removed instance to Visit-Workorder locator.
3065 PROCEDURE ReturnTo_Workorder_Locator( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3066 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3067 p_part_change_id IN NUMBER,
3068 p_disposition_id IN NUMBER,
3069 x_return_status OUT NOCOPY VARCHAR2,
3070 x_msg_data OUT NOCOPY VARCHAR2,
3071 x_msg_count OUT NOCOPY NUMBER,
3072 x_ahl_mtltxn_rec OUT NOCOPY AHL_PRD_MTLTXN_PVT.Ahl_Mtltxn_Rec_Type)
3073 IS
3074 -- FND Logging Constants
3075 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3076 l_debug_proc NUMBER := FND_LOG.LEVEL_PROCEDURE;
3077 l_debug_stmt NUMBER := FND_LOG.LEVEL_STATEMENT;
3078 l_debug_uexp NUMBER := FND_LOG.LEVEL_UNEXPECTED;
3079
3080 l_debug_module VARCHAR2(80) := 'ahl.plsql.AHL_PRD_PARTS_CHANGE_PVT.ReturnTo_Workorder_Locator';
3081
3082 CURSOR ahl_disp_csr (p_disposition_id IN NUMBER,
3083 p_part_change_id IN NUMBER) IS
3084 SELECT disp.WORKORDER_ID, disp.INSTANCE_ID, disp.CONDITION_ID,
3085 DISP.QUANTITY, DISP.UOM, disp.WO_OPERATION_ID, disp.item_revision revision,
3086 disp.serial_number, disp.lot_number,
3087 csi.inventory_item_id, vst.organization_id, vst.inv_locator_id,
3088 loc.subinventory_code, awo.operation_sequence_num
3089 FROM ahl_prd_dispositions_b disp, csi_item_instances csi,
3090 ahl_workorders wo, ahl_visits_b vst,
3091 mtl_item_locations_kfv loc, ahl_workorder_operations awo
3092 WHERE disp.instance_id = csi.instance_id
3093 AND disp.part_change_id = p_part_change_id
3094 AND disp.workorder_id = wo.workorder_id
3095 AND wo.visit_id = vst.visit_id
3096 AND vst.inv_locator_id = loc.inventory_location_id(+)
3097 AND vst.organization_id = loc.organization_id(+)
3098 AND awo.workorder_operation_id = disp.WO_OPERATION_ID;
3099
3100 CURSOR get_employee_id(p_user_id IN NUMBER) IS
3101 SELECT employee_id
3102 from FND_USER
3103 WHERE user_id = p_user_id;
3104
3105
3106 l_mtl_txn_tbl AHL_PRD_MTLTXN_PVT.Ahl_Mtltxn_Tbl_Type;
3107 l_disp_Mtl_Txn_Tbl AHL_PRD_DISP_MTL_TXN_PVT.Disp_Mtl_Txn_Tbl_Type;
3108
3109 l_disposition_rec ahl_disp_csr%ROWTYPE;
3110 l_employee_id number;
3111
3112 l_msg_count number;
3113 l_msg_count1 number;
3114
3115 BEGIN
3116
3117 -- log debug message.
3118 IF (l_debug_proc >= l_debug_level) THEN
3119 fnd_log.string(l_debug_proc,l_debug_module,
3120 'At Start of procedure AHL_PRD_PARTS_CHANGE_PVT.ReturnTo_Workorder_Locator');
3121 END IF;
3122
3123 -- Standard start of API savepoint
3124 Savepoint ReturnTo_Workorder_Locator_pvt;
3125
3126 -- Initialize message list if p_init_msg_list is set to TRUE
3127 IF FND_API.To_Boolean(p_init_msg_list) THEN
3128 FND_MSG_PUB.Initialize;
3129 END IF;
3130
3131 -- Initialize API return status to success
3132 x_return_status := FND_API.G_RET_STS_SUCCESS;
3133
3134 -- log debug message.
3135 IF (l_debug_stmt >= l_debug_level) THEN
3136 fnd_log.string(l_debug_stmt,l_debug_module,
3137 'Input disposition_id:' || p_disposition_id);
3138 fnd_log.string(l_debug_stmt,l_debug_module,
3139 'Input Parts_Change_id:' || p_part_change_id);
3140 END IF;
3141
3142 -- get count of existing messages.
3143 l_msg_count := FND_MSG_PUB.Count_Msg;
3144
3145 -- get disposition details.
3146 OPEN ahl_disp_csr(p_disposition_id, p_part_change_id);
3147 FETCH ahl_disp_csr INTO l_disposition_rec;
3148 IF (ahl_disp_csr%NOTFOUND) THEN
3149 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_DISP_INVALID');
3150 FND_MESSAGE.Set_Token('DISP_ID',p_disposition_id);
3151 FND_MSG_PUB.ADD;
3152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3153 END IF;
3154 CLOSE ahl_disp_csr;
3155
3156 -- If visit header does not have locator information then return.
3157 IF (l_disposition_rec.inv_locator_id IS NULL) THEN
3158 RETURN;
3159 END IF;
3160
3161 -- Form material txn record structure.
3162
3163 l_mtl_txn_tbl(1).Workorder_id := l_disposition_rec.workorder_id;
3164 -- dbms_output.put_line('workorder '||l_mtl_txn_tbl(1).Workorder_id);
3165
3166 l_mtl_txn_tbl(1).workorder_operation_id := l_disposition_rec.wo_operation_id;
3167 -- dbms_output.put_line('workorder_operation_id '||l_mtl_txn_tbl(1).workorder_operation_id);
3168
3169 l_mtl_txn_tbl(1).operation_seq_num := l_disposition_rec.operation_sequence_num;
3170
3171 l_mtl_txn_tbl(1).INVENTORY_ITEM_ID := l_disposition_rec.inventory_item_id;
3172 -- dbms_output.put_line('INVENTORY_ITEM_ID '||l_mtl_txn_tbl(1).INVENTORY_ITEM_ID);
3173
3174 l_mtl_txn_tbl(1).REVISION := l_disposition_rec.revision;
3175 -- dbms_output.put_line('REVISION '||l_mtl_txn_tbl(1).REVISION);
3176
3177 l_mtl_txn_tbl(1).ORGANIZATION_ID:= l_disposition_rec.organization_id;
3178 -- dbms_output.put_line('ORGANIZATION_ID '||l_mtl_txn_tbl(1).ORGANIZATION_ID);
3179
3180 l_mtl_txn_tbl(1).CONDITION := l_disposition_rec.condition_id;
3181 -- dbms_output.put_line('CONDITION '||l_mtl_txn_tbl(1).CONDITION);
3182
3183 l_mtl_txn_tbl(1).SUBINVENTORY_NAME := l_disposition_rec.subinventory_code;
3184 -- dbms_output.put_line('SUBINVENTORY_NAME '||l_mtl_txn_tbl(1).SUBINVENTORY_NAME);
3185
3186 l_mtl_txn_tbl(1).MOVE_TO_PROJECT_FLAG := 'Y';
3187 -- dbms_output.put_line('MOVE_TO_PROJECT_FLAG '||l_mtl_txn_tbl(1).MOVE_TO_PROJECT_FLAG);
3188
3189 l_mtl_txn_tbl(1).QUANTITY := l_disposition_rec.quantity;
3190 -- dbms_output.put_line('QUANTITY '||l_mtl_txn_tbl(1).QUANTITY);
3191
3192 l_mtl_txn_tbl(1).UOM := l_disposition_rec.uom;
3193 -- dbms_output.put_line('UOM '||l_mtl_txn_tbl(1).UOM);
3194
3195 l_mtl_txn_tbl(1).TRANSACTION_TYPE_ID:= WIP_CONSTANTS.RETCOMP_TYPE;
3196 -- dbms_output.put_line('TRANSACTION_TYPE_ID '||l_mtl_txn_tbl(1).TRANSACTION_TYPE_ID);
3197
3198 l_mtl_txn_tbl(1).TRANSACTION_REFERENCE := null;
3199
3200 l_mtl_txn_tbl(1).SERIAL_NUMBER := l_disposition_rec.serial_number;
3201 -- dbms_output.put_line('SERIAL_NUMBER '||l_mtl_txn_tbl(1).SERIAL_NUMBER);
3202
3203 l_mtl_txn_tbl(1).LOT_NUMBER := l_disposition_rec.lot_number;
3204 -- dbms_output.put_line('LOT_NUMBER '||l_mtl_txn_tbl(1).LOT_NUMBER);
3205
3206 l_mtl_txn_tbl(1).disposition_id := p_disposition_id;
3207 -- dbms_output.put_line('LOT_NUMBER '||l_mtl_txn_tbl(1).LOT_NUMBER);
3208
3209 -- populate receipient.
3210 OPEN get_employee_id(FND_GLOBAL.USER_ID);
3211 FETCH get_employee_id INTO l_employee_id;
3212 CLOSE get_employee_id;
3213
3214 l_mtl_txn_tbl(1).recepient_id := l_employee_id;
3215
3216 AHL_PRD_MTLTXN_PVT.PERFORM_MTL_TXN (p_api_version => 1.0,
3217 p_module_type => null,
3218 p_create_sr => 'N', -- servicable return txn.
3219 p_x_ahl_mtltxn_tbl=> l_mtl_txn_tbl,
3220 x_return_status => x_return_status,
3221 x_msg_count=> x_msg_count,
3222 x_msg_data => x_msg_data );
3223
3224 IF (l_debug_stmt >= l_debug_level)
3225 THEN
3226 fnd_log.string
3227 (
3228 l_debug_stmt,
3229 l_debug_module,
3230 'After call to AHL_PRD_MTLTXN_PVT.PERFORM_MTL_TXN. Return status:' || x_return_status
3231 );
3232 fnd_log.string
3233 (
3234 l_debug_stmt,
3235 l_debug_module,
3236 'After call to AHL_PRD_MTLTXN_PVT.PERFORM_MTL_TXN. Error count:' || x_msg_count
3237 );
3238 END IF;
3239
3240 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3241 RAISE FND_API.G_EXC_ERROR;
3242 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3244 END IF;
3245
3246 -- get count of message after calling Material txn api.
3247 x_msg_count := FND_MSG_PUB.Count_Msg;
3248
3249 -- if return status success.. remove info messages from WIP/INV.
3250 FOR i IN l_msg_count+1..x_msg_count LOOP
3251 FND_MSG_PUB.Delete_Msg(i);
3252 END LOOP;
3253
3254 -- log debug message.
3255 IF (l_debug_proc >= l_debug_level) THEN
3256 fnd_log.string(l_debug_proc,l_debug_module,
3257 '');
3258 END IF;
3259
3260 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3261 p_data => x_msg_data,
3262 p_encoded => fnd_api.g_false);
3263
3264 -- Call disposition api to associate material txn.
3265 l_disp_Mtl_Txn_Tbl(1).wo_mtl_txn_id := l_mtl_txn_tbl(1).Ahl_MtlTxn_Id;
3266 l_disp_Mtl_Txn_Tbl(1).disposition_id := l_mtl_txn_tbl(1).disposition_id;
3267 l_disp_Mtl_Txn_Tbl(1).quantity := l_mtl_txn_tbl(1).Quantity;
3268 l_disp_Mtl_Txn_Tbl(1).uom := l_mtl_txn_tbl(1).uom;
3269
3270 AHL_PRD_DISP_MTL_TXN_PVT.Process_Disp_Mtl_Txn (
3271 p_api_version => 1.0,
3272 p_init_msg_list => FND_API.G_FALSE,
3273 p_commit => FND_API.G_FALSE,
3274 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3275 x_return_status => x_return_status,
3276 x_msg_count => x_msg_count,
3277 x_msg_data => x_msg_data,
3278 p_module => 'JSP',
3279 p_x_disp_mtl_txn_tbl => l_disp_Mtl_Txn_Tbl);
3280
3281 IF (l_debug_stmt >= l_debug_level)
3282 THEN
3283 fnd_log.string
3284 (
3285 l_debug_stmt,
3286 l_debug_module,
3287 'After call to AHL_PRD_DISP_MTL_TXN_PVT.Process_Disp_Mtl_Txn. Return status:' || x_return_status
3288 );
3289 fnd_log.string
3290 (
3291 l_debug_stmt,
3292 l_debug_module,
3293 'After call to AHL_PRD_DISP_MTL_TXN_PVT.Process_Disp_Mtl_Txn. Error count:' || x_msg_count
3294 );
3295 END IF;
3296
3297 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3298 RAISE FND_API.G_EXC_ERROR;
3299 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3301 END IF;
3302
3303 -- log debug message.
3304 IF (l_debug_proc >= l_debug_level) THEN
3305 fnd_log.string(l_debug_proc,l_debug_module,
3306 'At End of procedure AHL_PRD_PARTS_CHANGE_PVT.ReturnTo_Workorder_Locator');
3307 END IF;
3308
3309 EXCEPTION
3310 WHEN FND_API.G_EXC_ERROR THEN
3311 x_return_status := FND_API.G_RET_STS_ERROR;
3312 Rollback to ReturnTo_Workorder_Locator_pvt;
3313 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3314 p_data => x_msg_data,
3315 p_encoded => fnd_api.g_false);
3316 -- Disable debug
3317 IF G_DEBUG = 'Y' THEN
3318 AHL_DEBUG_PUB.disable_debug;
3319 END IF;
3320
3321
3322 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3324 Rollback to ReturnTo_Workorder_Locator_pvt;
3325 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3326 p_data => x_msg_data,
3327 p_encoded => fnd_api.g_false);
3328 -- Disable debug
3329 IF G_DEBUG = 'Y' THEN
3330 AHL_DEBUG_PUB.disable_debug;
3331 END IF;
3332
3333 WHEN OTHERS THEN
3334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3335 Rollback to ReturnTo_Workorder_Locator_pvt;
3336 --IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3337 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PRD_PARTS_CHANGE_PVT',
3338 p_procedure_name => 'process_parts',
3339 p_error_text => SQLERRM);
3340 --END IF;
3341 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3342 p_data => x_msg_data,
3343 p_encoded => fnd_api.g_false);
3344
3345 -- Disable debug
3346 IF G_DEBUG = 'Y' THEN
3347 AHL_DEBUG_PUB.disable_debug;
3348 END IF;
3349
3350 END ReturnTo_Workorder_Locator;
3351
3352
3353 PROCEDURE move_instance_location(
3354 P_API_Version In Number Default 1.0,
3355 P_Init_Msg_List In Varchar2 Default Fnd_API.G_False,
3356 P_Commit In Varchar2 Default Fnd_API.G_False,
3357 P_Validation_Level In Number Default Fnd_API.G_Valid_Level_Full,
3358 p_module_type In Varchar2 Default NULL,
3359 p_default In Varchar2 Default FND_API.G_TRUE,
3360 p_move_item_instance_tbl In AHL_PRD_PARTS_CHANGE_PVT.move_item_instance_tbl_type,
3361 X_Return_Status Out NOCOPY Varchar2,
3362 X_Msg_Count Out NOCOPY Number,
3363 X_Msg_Data Out NOCOPY Varchar2) IS
3364
3365 l_api_name CONSTANT VARCHAR2(30) := 'move_instance_location';
3366 l_api_version CONSTANT NUMBER := 1.0;
3367
3368 l_instance_rec csi_datastructures_pub.instance_rec;
3369 l_csi_transaction_rec CSI_DATASTRUCTURES_PUB.transaction_rec;
3370 l_serialized VARCHAR2(1);
3371
3372
3373
3374
3375
3376 BEGIN
3377 -- Standard start of API savepoint
3378 SAVEPOINT MOVE_INSTANCE_LOCATION;
3379
3380 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3381 fnd_log.string
3382 (
3383 fnd_log.level_procedure,
3384 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
3385 'At the start of PLSQL procedure'
3386 );
3387 END IF;
3388
3389 -- Standard call to check for call compatibility
3390 IF NOT FND_API.Compatible_API_Call(l_api_version,
3391 p_api_version,
3392 l_api_name,
3393 G_PKG_NAME) THEN
3394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3395 END IF;
3396
3397 -- Initialize message list if p_init_msg_list is set to TRUE
3398 IF FND_API.To_Boolean(p_init_msg_list) THEN
3399 FND_MSG_PUB.Initialize;
3400 END IF;
3401
3402 -- Initialize API return status to success
3403 x_return_status := FND_API.G_RET_STS_SUCCESS;
3404
3405 ------**************Get csi_transaction_rec.
3406 Create_csi_transaction_rec(l_csi_transaction_rec,x_return_status);
3407 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3408 RAISE FND_API.G_EXC_ERROR;
3409 END IF;
3410
3411 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3412 fnd_log.string
3413 (
3414 fnd_log.level_statement,
3415 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3416 'Created CSI Transaction'
3417 );
3418 END IF;
3419
3420 --get location id
3421 /*OPEN ahl_wip_location_csr();
3422 FETCH ahl_wip_location_csr INTO l_location_id ;
3423 CLOSE ahl_wip_location_csr;*/
3424
3425 FOR i IN p_move_item_instance_tbl.FIRST..p_move_item_instance_tbl.LAST LOOP
3426 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3427 fnd_log.string
3428 (
3429 fnd_log.level_statement,
3430 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3431 'p_move_item_instance_tbl(i).instance_id : ' || p_move_item_instance_tbl(i).instance_id
3432 );
3433 END IF;
3434 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3435 fnd_log.string
3436 (
3437 fnd_log.level_statement,
3438 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3439 'p_move_item_instance_tbl(i).quantity : ' || p_move_item_instance_tbl(i).quantity
3440 );
3441 END IF;
3442 get_dest_instance_rec(
3443 p_module_type => p_module_type,
3444 p_move_item_instance_rec => p_move_item_instance_tbl(i),
3445 x_instance_rec => l_instance_rec,
3446 x_serialized => l_serialized,
3447 x_Return_Status => X_Return_Status
3448 );
3449 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3450 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3451 fnd_log.string
3452 (
3453 fnd_log.level_error,
3454 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3455 'get_dest_instance_rec returned error'
3456 );
3457 END IF;
3458 RAISE FND_API.G_EXC_ERROR;
3459 END IF;
3460
3461 /*l_instance_rec.location_id := l_location_id;
3462 l_instance_rec.LOCATION_TYPE_CODE := 'WIP';
3463 l_instance_rec.instance_usage_code := 'IN_WIP';*/
3464
3465 IF FND_API.To_Boolean(l_serialized) THEN
3466 update_csi_item_instance(
3467 p_instance_rec => l_instance_rec,
3468 p_x_csi_transaction_rec => l_csi_transaction_rec,
3469 x_Return_Status => X_Return_Status
3470 );
3471 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3472 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3473 fnd_log.string
3474 (
3475 fnd_log.level_error,
3476 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3477 'serialized move: update_csi_item_instance returned error'
3478 );
3479 END IF;
3480 RAISE FND_API.G_EXC_ERROR;
3481 END IF;
3482 ELSE
3483
3484
3485 move_nonser_instance(
3486 p_source_instance_id => l_instance_rec.instance_id,
3487 p_move_quantity => l_instance_rec.quantity,
3488 p_dest_wip_job_id => l_instance_rec.wip_job_id,
3489 p_x_csi_transaction_rec => l_csi_transaction_rec,
3490 x_Return_Status => X_Return_Status
3491 );
3492 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3493 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3494 fnd_log.string
3495 (
3496 fnd_log.level_error,
3497 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3498 'non serialized move: move_nonser_instance returned error'
3499 );
3500 END IF;
3501 RAISE FND_API.G_EXC_ERROR;
3502 END IF;
3503 END IF;
3504 END LOOP;
3505
3506 -- Standard check of p_commit
3507 IF FND_API.To_Boolean(p_commit) THEN
3508 COMMIT WORK;
3509 END IF;
3510
3511
3512
3513 -- Standard call to get message count and if count is 1, get message
3514 FND_MSG_PUB.Count_And_Get
3515 ( p_count => x_msg_count,
3516 p_data => x_msg_data,
3517 p_encoded => fnd_api.g_false);
3518
3519 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3520 fnd_log.string
3521 (
3522 fnd_log.level_procedure,
3523 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.end',
3524 'At the end of PLSQL procedure'
3525 );
3526 END IF;
3527
3528 EXCEPTION
3529 WHEN FND_API.G_EXC_ERROR THEN
3530 x_return_status := FND_API.G_RET_STS_ERROR;
3531 Rollback to MOVE_INSTANCE_LOCATION;
3532 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3533 p_data => x_msg_data,
3534 p_encoded => fnd_api.g_false);
3535
3536
3537 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3539 Rollback to MOVE_INSTANCE_LOCATION;
3540 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3541 p_data => x_msg_data,
3542 p_encoded => fnd_api.g_false);
3543
3544 WHEN OTHERS THEN
3545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3546 Rollback to MOVE_INSTANCE_LOCATION;
3547 --IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3548 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PRD_PARTS_CHANGE_PVT',
3549 p_procedure_name => 'move_instance_location',
3550 p_error_text => SQLERRM);
3551 --END IF;
3552 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3553 p_data => x_msg_data,
3554 p_encoded => fnd_api.g_false);
3555
3556 END move_instance_location;
3557
3558 PROCEDURE get_dest_instance_rec(
3559 p_module_type IN VARCHAR2 Default NULL,
3560 p_move_item_instance_rec IN move_item_instance_rec_type,
3561 x_instance_rec Out NOCOPY csi_datastructures_pub.instance_rec,
3562 x_serialized Out NOCOPY Varchar2,
3563 x_Return_Status Out NOCOPY Varchar2
3564 ) IS
3565 l_api_name CONSTANT VARCHAR2(30) := 'get_dest_instance_rec';
3566 -- For wip_entity_id for the workorder
3567 CURSOR wip_entity_woid_csr(p_wo_id IN NUMBER) IS
3568 Select AHL.wip_entity_id,job_status_code
3569 FROM ahl_search_workorders_v AHL
3570 WHERE AHL.workorder_id = p_wo_id;
3571
3572 CURSOR wip_entity_wonum_csr(p_wo_number IN VARCHAR2) IS
3573 Select AHL.wip_entity_id,job_status_code
3574 FROM ahl_search_workorders_v AHL
3575 WHERE AHL.JOB_NUMBER = p_wo_number;
3576
3577 --For the updated object_version number from csi_item_isntances
3578 CURSOR csi_item_instance_id_csr(p_instance_id IN NUMBER) IS
3579 select instance_id,object_Version_number,serial_number,quantity,wip_job_id
3580 from csi_item_instances CII
3581 where CII.instance_id = p_instance_id
3582 AND CII.ACTIVE_START_DATE <= SYSDATE
3583 AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
3584 AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
3585 WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
3586 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
3587 AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
3588 AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)));
3589
3590 CURSOR csi_item_instance_num_csr(p_instance_number IN VARCHAR2) IS
3591 select instance_id, object_Version_number,serial_number,quantity,wip_job_id
3592 from csi_item_instances CII
3593 where CII.instance_number = p_instance_number
3594 AND CII.ACTIVE_START_DATE <= SYSDATE
3595 AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
3596 AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
3597 WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
3598 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
3599 AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
3600 AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)));
3601
3602 l_instance_rec csi_datastructures_pub.instance_rec;
3603 l_from_wip_entity_id NUMBER;
3604 l_curr_wip_entity_id NUMBER;
3605 l_to_wip_entity_id NUMBER;
3606 l_serial_number VARCHAR2(30);
3607 l_current_quantity NUMBER;
3608 l_status_code VARCHAR2(30);
3609 l_check_qnt_flag BOOLEAN;
3610
3611
3612 CURSOR check_inst_nonserial(p_instance_id IN NUMBER) IS
3613 SELECT 'X'
3614 FROM mtl_system_items_b mtl, csi_item_instances csi
3615 WHERE csi.instance_id = p_instance_id
3616 AND csi.inventory_item_id = mtl.inventory_item_id
3617 AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
3618 AND mtl.serial_number_control_code = 1;
3619
3620 l_junk VARCHAR2(1);
3621
3622 CURSOR check_org_csr(p_from_wip_entity NUMBER,p_to_wip_entity NUMBER)IS
3623 SELECT 'x' FROM ahl_workorders A,ahl_workorders B WHERE
3624 A.wip_entity_id = p_from_wip_entity
3625 AND B.wip_entity_id = p_to_wip_entity
3626 AND A.visit_id = B.visit_id;
3627
3628
3629 BEGIN
3630 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3631 fnd_log.string
3632 (
3633 fnd_log.level_procedure,
3634 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
3635 'At the start of PLSQL procedure'
3636 );
3637 END IF;
3638 x_return_status := FND_API.G_RET_STS_SUCCESS;
3639
3640 IF(p_module_type = 'JSP' AND p_move_item_instance_rec.from_workorder_number IS NOT NULL) THEN
3641 OPEN wip_entity_wonum_csr(p_move_item_instance_rec.from_workorder_number);
3642 FETCH wip_entity_wonum_csr INTO l_from_wip_entity_id,l_status_code;
3643 IF (wip_entity_wonum_csr%NOTFOUND OR l_status_code IN ('1','5','7','12','17','22')) THEN
3644 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3645 fnd_log.string
3646 (
3647 fnd_log.level_error,
3648 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3649 'wip_entity_not_found for from_workorder_number'
3650 );
3651 END IF;
3652 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WO_NUM_MISSING');
3653 FND_MESSAGE.Set_Token('WONUM',p_move_item_instance_rec.from_workorder_number);
3654 FND_MSG_PUB.ADD;
3655 END IF;
3656 CLOSE wip_entity_wonum_csr;
3657 ELSIF p_move_item_instance_rec.from_workorder_id IS NOT NULL THEN
3658 OPEN wip_entity_woid_csr(p_move_item_instance_rec.from_workorder_id);
3659 FETCH wip_entity_woid_csr INTO l_from_wip_entity_id,l_status_code;
3660 IF (wip_entity_woid_csr%NOTFOUND OR l_status_code IN ('1','5','7','12','17','22')) THEN
3661 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3662 fnd_log.string
3663 (
3664 fnd_log.level_error,
3665 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3666 'wip_entity_not_found for from_workorder_id'
3667 );
3668 END IF;
3669 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WO_ID_MISSING');
3670 FND_MESSAGE.Set_Token('WOID',p_move_item_instance_rec.from_workorder_id);
3671 FND_MSG_PUB.ADD;
3672 END IF;
3673 CLOSE wip_entity_woid_csr;
3674 END IF;
3675 --dbms_output.put_line('point1');
3676
3677 IF(p_module_type = 'JSP' AND p_move_item_instance_rec.to_workorder_number IS NOT NULL) THEN
3678 OPEN wip_entity_wonum_csr(p_move_item_instance_rec.to_workorder_number);
3679 FETCH wip_entity_wonum_csr INTO l_to_wip_entity_id, l_status_code;
3680 IF (wip_entity_wonum_csr%NOTFOUND OR l_status_code IN ('1','4','5','6','7','12','17','21','22')) THEN
3681 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3682 fnd_log.string
3683 (
3684 fnd_log.level_error,
3685 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3686 'wip_entity_not_found for to_workorder_number'
3687 );
3688 END IF;
3689 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WO_NUM_MISSING');
3690 FND_MESSAGE.Set_Token('WONUM',p_move_item_instance_rec.to_workorder_number);
3691 FND_MSG_PUB.ADD;
3692 END IF;
3693 CLOSE wip_entity_wonum_csr;
3694 ELSE
3695 OPEN wip_entity_woid_csr(p_move_item_instance_rec.to_workorder_id);
3696 FETCH wip_entity_woid_csr INTO l_to_wip_entity_id, l_status_code;
3697 IF (wip_entity_woid_csr%NOTFOUND OR l_status_code IN ('1','4','5','6','7','12','17','21','22')) THEN
3698 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3699 fnd_log.string
3700 (
3701 fnd_log.level_error,
3702 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3703 'wip_entity_not_found for to_workorder_id'
3704 );
3705 END IF;
3706 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WO_ID_MISSING');
3707 FND_MESSAGE.Set_Token('WOID',p_move_item_instance_rec.to_workorder_id);
3708 FND_MSG_PUB.ADD;
3709 END IF;
3710 CLOSE wip_entity_woid_csr;
3711 END IF;
3712
3713 IF(l_from_wip_entity_id IS NOT NULL) THEN
3714 OPEN check_org_csr(l_from_wip_entity_id,l_to_wip_entity_id);
3715 FETCH check_org_csr INTO l_junk;
3716 IF(check_org_csr%NOTFOUND)THEN
3717 FND_MESSAGE.Set_Name('AHL','AHL_PRD_WO_NUM_MISSING');
3718 FND_MESSAGE.Set_Token('WONUM',p_move_item_instance_rec.to_workorder_number);
3719 FND_MSG_PUB.ADD;
3720 END IF;
3721 CLOSE check_org_csr;
3722 END IF;
3723
3724 l_check_qnt_flag := true; --amsriniv. Introducing this flag to check if quantity /serial checks are required.
3725 --dbms_output.put_line('point2');
3726 IF((p_module_type = 'JSP' AND p_move_item_instance_rec.instance_number IS NOT NULL) OR
3727 p_move_item_instance_rec.instance_id IS NULL) THEN
3728 --get the updated object_version number from csi_item_isntances
3729 --dbms_output.put_line('point2 : ' || p_move_item_instance_rec.instance_number);
3730 --l_check_qnt_flag := true; --amsriniv. Introducing this flag to check if quantity /serial checks are required.
3731 OPEN csi_item_instance_num_csr(p_move_item_instance_rec.instance_number);
3732 FETCH csi_item_instance_num_csr INTO l_instance_rec.instance_id,
3733 l_instance_rec.OBJECT_VERSION_NUMBER,
3734 l_serial_number,
3735 l_current_quantity,
3736 l_curr_wip_entity_id;
3737
3738 IF (csi_item_instance_num_csr%NOTFOUND) THEN
3739 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3740 fnd_log.string
3741 (
3742 fnd_log.level_error,
3743 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3744 'removed instance_num not found ' || p_move_item_instance_rec.instance_number
3745 );
3746 END IF;
3747 l_check_qnt_flag := FALSE; --amsriniv
3748 FND_MESSAGE.Set_Name('AHL','AHL_PRD_REM_INSTNUM_INV');
3749 FND_MESSAGE.Set_Token('INST',p_move_item_instance_rec.instance_number);
3750 FND_MSG_PUB.ADD;
3751 END IF;
3752 CLOSE csi_item_instance_num_csr;
3753 ELSE
3754 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3755 fnd_log.string
3756 (
3757 fnd_log.level_statement,
3758 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
3759 'p_move_item_instance_rec.instance_id : ' || p_move_item_instance_rec.instance_id
3760 );
3761 END IF;
3762 --get the updated object_version number from csi_item_isntances
3763 OPEN csi_item_instance_id_csr(p_move_item_instance_rec.instance_id);
3764 FETCH csi_item_instance_id_csr INTO l_instance_rec.instance_id,
3765 l_instance_rec.OBJECT_VERSION_NUMBER,
3766 l_serial_number,
3767 l_current_quantity,
3768 l_curr_wip_entity_id;
3769 IF (csi_item_instance_id_csr%NOTFOUND) THEN
3770 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3771 fnd_log.string
3772 (
3773 fnd_log.level_error,
3774 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3775 'removed instance_id not found ' || p_move_item_instance_rec.instance_id
3776 );
3777 END IF;
3778 l_check_qnt_flag := FALSE; --amsriniv
3779 FND_MESSAGE.Set_Name('AHL','AHL_PRD_REM_INSTID_INV');
3780 FND_MESSAGE.Set_Token('INST',p_move_item_instance_rec.instance_id);--amsriniv
3781 FND_MSG_PUB.ADD;
3782 END IF;
3783 CLOSE csi_item_instance_id_csr;
3784 END IF;
3785
3786
3787 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3788 fnd_log.string
3789 (
3790 fnd_log.level_statement,
3791 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3792 'checking whether item is serialized'
3793 );
3794 END IF;
3795
3796 --Validate quantity / serial item only if valid instance exists.
3797 x_serialized := FND_API.G_FALSE;
3798 IF (l_check_qnt_flag) THEN
3799 OPEN check_inst_nonserial(l_instance_rec.instance_id);
3800 FETCH check_inst_nonserial INTO l_junk;
3801 IF(check_inst_nonserial%NOTFOUND)THEN
3802 x_serialized := FND_API.G_TRUE;
3803 END IF;
3804 CLOSE check_inst_nonserial;
3805
3806 IF FND_API.To_Boolean(x_serialized) THEN
3807 IF(NVL(p_move_item_instance_rec.quantity,1) <> 1)THEN
3808 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3809 fnd_log.string
3810 (
3811 fnd_log.level_error,
3812 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3813 'invalid qty for serialized item ' || p_move_item_instance_rec.quantity
3814 );
3815 END IF;
3816 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INV_SER_QTY');
3817 FND_MESSAGE.Set_Token('QTY',to_char(p_move_item_instance_rec.quantity));
3818 FND_MSG_PUB.ADD;
3819 END IF;
3820 ELSE
3821 IF ((NVL(p_move_item_instance_rec.quantity,1) < 0) OR
3822 (NVL(p_move_item_instance_rec.quantity,1) > l_current_quantity))THEN
3823 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3824 fnd_log.string
3825 (
3826 fnd_log.level_error,
3827 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3828 'invalid qty for non serialized item ' || p_move_item_instance_rec.quantity
3829 );
3830 END IF;
3831 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INV_NONSER_QTY');
3832 FND_MESSAGE.Set_Token('QTY',to_char(p_move_item_instance_rec.quantity));
3833 FND_MSG_PUB.ADD;
3834 END IF;
3835 END IF;
3836 END IF;
3837
3838 IF(l_curr_wip_entity_id <> l_from_wip_entity_id) THEN
3839 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3840 fnd_log.string
3841 (
3842 fnd_log.level_error,
3843 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3844 'item not in from wip_entity_id location ' || l_from_wip_entity_id
3845 );
3846 END IF;
3847 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INV_CURR_LOC');
3848 FND_MESSAGE.Set_Token('INST',l_instance_rec.instance_id);
3849 FND_MSG_PUB.ADD;
3850 END IF;
3851 --Standard check to count messages
3852 IF Fnd_Msg_Pub.count_msg > 0 THEN
3853 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3854 RAISE Fnd_Api.G_EXC_ERROR;
3855 END IF;
3856
3857 l_instance_rec.wip_job_id := l_to_wip_entity_id;
3858 l_instance_rec.quantity := NVL(p_move_item_instance_rec.quantity,1);
3859 x_instance_rec := l_instance_rec;
3860
3861
3862 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3863 fnd_log.string
3864 (
3865 fnd_log.level_statement,
3866 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3867 'x_instance_rec.instance_id ' || x_instance_rec.instance_id
3868 );
3869 fnd_log.string
3870 (
3871 fnd_log.level_statement,
3872 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3873 'x_instance_rec.wip_job_id ' || x_instance_rec.wip_job_id
3874 );
3875 fnd_log.string
3876 (
3877 fnd_log.level_statement,
3878 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
3879 'x_instance_rec.quantity ' || x_instance_rec.quantity
3880 );
3881 END IF;
3882
3883 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3884 fnd_log.string
3885 (
3886 fnd_log.level_procedure,
3887 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.end',
3888 'At the end of PLSQL procedure'
3889 );
3890 END IF;
3891
3892
3893 END get_dest_instance_rec;
3894
3895 PROCEDURE update_csi_item_instance(
3896 p_instance_rec IN csi_datastructures_pub.instance_rec,
3897 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
3898 X_Return_Status Out NOCOPY Varchar2
3899 ) IS
3900 l_api_name CONSTANT VARCHAR2(30) := 'update_csi_item_instance';
3901 l_extend_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
3902 l_party_tbl csi_datastructures_pub.party_tbl;
3903 l_account_tbl csi_datastructures_pub.party_account_tbl;
3904 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
3905 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
3906 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
3907 l_instance_id_lst csi_datastructures_pub.id_tbl;
3908 l_msg_count number;
3909 l_msg_data varchar2(2000);
3910
3911 --to populate csi_transaction record
3912 /*CURSOR ahl_wip_location_csr IS
3913 select wip_location_id
3914 from csi_install_parameters ;*/
3915
3916 l_instance_rec csi_datastructures_pub.instance_rec;
3917 --to find an expired instance if there
3918 /*CURSOR dest_instance_csr(p_source_instance_id IN NUMBER ) IS
3919 SELECT 'x'
3920 FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
3921 WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
3922 AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
3923 AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
3924 AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
3925 AND NVL(CI1.SERIAL_NUMBER,'x') = NVL(CI2.SERIAL_NUMBER,'x')
3926 AND CI1.WIP_JOB_ID= CI2.WIP_JOB_ID
3927 AND CI1.instance_id <> p_source_instance_id
3928 AND CI2.instance_id = p_source_instance_id
3929 AND CI1.LOCATION_TYPE_CODE='WIP'
3930 AND CI1.INSTANCE_USAGE_CODE = 'IN_WIP'
3931 AND CI1.unit_of_measure = CI2.unit_of_measure
3932 AND CI1.ACTIVE_START_DATE <= SYSDATE
3933 AND (CI1.ACTIVE_END_DATE IS NULL OR CI1.ACTIVE_END_DATE < SYSDATE)
3934 AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
3935 WHERE CIR.SUBJECT_ID = CI1.INSTANCE_ID
3936 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
3937 AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
3938 AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
3939
3940 l_junk VARCHAR2(1);*/
3941
3942
3943 BEGIN
3944 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3945 fnd_log.string
3946 (
3947 fnd_log.level_procedure,
3948 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
3949 'At the start of PLSQL procedure'
3950 );
3951 END IF;
3952
3953 -- populate l_instance_rec
3954 l_instance_rec := p_instance_rec;
3955 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3956 fnd_log.string
3957 (
3958 fnd_log.level_statement,
3959 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
3960 'l_instance_rec.instance_id : ' || l_instance_rec.instance_id
3961 );
3962 fnd_log.string
3963 (
3964 fnd_log.level_statement,
3965 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
3966 'l_instance_rec.quantity : ' || l_instance_rec.quantity
3967 );
3968 fnd_log.string
3969 (
3970 fnd_log.level_statement,
3971 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
3972 'l_instance_rec.active_end_date : ' || to_char(l_instance_rec.active_end_date)
3973 );
3974 END IF;
3975 IF(l_instance_rec.quantity = 0 )THEN
3976 /*OPEN dest_instance_csr(l_instance_rec.instance_id);
3977 FETCH dest_instance_csr INTO l_junk;
3978 IF(dest_instance_csr%FOUND)THEN
3979 --l_instance_rec.instance_usage_code := NULL;
3980 --l_instance_rec.active_end_date := SYSDATE;
3981 l_instance_rec.instance_usage_code := 'UNUSABLE';
3982 END IF;
3983 CLOSE dest_instance_csr;*/
3984 l_instance_rec.active_end_date := SYSDATE;
3985 l_instance_rec.instance_usage_code := 'UNUSABLE';
3986
3987 END IF;
3988
3989
3990
3991 --get location id
3992 /*OPEN ahl_wip_location_csr();
3993 FETCH ahl_wip_location_csr INTO l_instance_rec.LOCATION_ID ;
3994 CLOSE ahl_wip_location_csr;*/
3995
3996
3997 CSI_ITEM_INSTANCE_PUB. update_item_instance (
3998 p_api_version =>1.0
3999 ,p_commit => fnd_api.g_false
4000 ,p_init_msg_list => fnd_api.g_false
4001 ,p_validation_level => fnd_api.g_valid_level_full
4002 ,p_instance_rec => l_instance_rec
4003 ,p_ext_attrib_values_tbl=>l_extend_attrib_values_tbl
4004 ,p_party_tbl =>l_party_tbl
4005 ,p_account_tbl => l_account_tbl
4006 ,p_pricing_attrib_tbl => l_pricing_attrib_tbl
4007 ,p_org_assignments_tbl => l_org_assignments_tbl
4008 ,p_asset_assignment_tbl => l_asset_assignment_tbl
4009 ,p_txn_rec => p_x_csi_transaction_rec
4010 ,x_instance_id_lst => l_instance_id_lst
4011 ,x_return_status => x_return_status
4012 ,x_msg_count => l_msg_count
4013 ,x_msg_data => l_msg_data );
4014 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4015 fnd_log.string
4016 (
4017 fnd_log.level_procedure,
4018 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.end',
4019 'At the end of PLSQL procedure'
4020 );
4021 END IF;
4022 END update_csi_item_instance;
4023
4024 PROCEDURE move_nonser_instance(
4025 p_source_instance_id IN NUMBER,
4026 p_move_quantity IN NUMBER,
4027 p_dest_wip_job_id IN NUMBER,
4028 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
4029 X_Return_Status Out NOCOPY Varchar2
4030 ) IS
4031 l_api_name CONSTANT VARCHAR2(30) := 'move_nonser_instance';
4032
4033 l_instance_rec csi_datastructures_pub.instance_rec;
4034 l_extend_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
4035 l_party_tbl csi_datastructures_pub.party_tbl;
4036 l_account_tbl csi_datastructures_pub.party_account_tbl;
4037 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
4038 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
4039 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
4040 l_instance_id_lst csi_datastructures_pub.id_tbl;
4041 l_msg_count number;
4042 l_msg_data varchar2(2000);
4043
4044
4045 CURSOR get_instance_attrib_csr(p_instance_id NUMBER) IS
4046 SELECT instance_id,
4047 instance_number,
4048 inventory_item_id,
4049 inv_master_organization_id,
4050 lot_number,
4051 quantity,
4052 unit_of_measure,
4053 install_date,
4054 inventory_revision,
4055 object_version_number,
4056 wip_job_id,
4057 location_id
4058 FROM csi_item_instances
4059 WHERE instance_id = p_instance_id;
4060
4061 l_source_inst_rec get_instance_attrib_csr%ROWTYPE;
4062
4063
4064
4065 CURSOR dest_instance_csr(p_inventory_item_id IN NUMBER,
4066 p_inv_master_org_id IN NUMBER,p_wip_job_id IN NUMBER,
4067 --p_location_id IN NUMBER,
4068 p_unit_of_measure IN VARCHAR2,
4069 p_source_instance_id IN NUMBER ) IS
4070 SELECT instance_id,
4071 instance_number,
4072 inventory_item_id,
4073 inv_master_organization_id,
4074 lot_number,
4075 quantity,
4076 unit_of_measure,
4077 install_date,
4078 inventory_revision,
4079 object_version_number,
4080 wip_job_id,
4081 location_id FROM CSI_ITEM_INSTANCES CII
4082 WHERE INV_MASTER_ORGANIZATION_ID= p_inv_master_org_id
4083 AND INVENTORY_ITEM_ID = p_inventory_item_id
4084 AND WIP_JOB_ID= p_wip_job_id
4085 AND instance_id <> p_source_instance_id
4086 AND LOCATION_TYPE_CODE='WIP'
4087 AND INSTANCE_USAGE_CODE='IN_WIP'
4088 --AND LOCATION_ID= p_location_id
4089 AND unit_of_measure = p_unit_of_measure
4090 AND ACTIVE_START_DATE <= SYSDATE
4091 AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
4092 AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
4093 WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
4094 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
4095 AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
4096 AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
4097
4098 l_item_found_in_dest_wo BOOLEAN;
4099 l_dest_instance_rec dest_instance_csr%ROWTYPE ;
4100 l_dest_instance_id NUMBER;
4101
4102 CURSOR dest_inactive_instance_csr(p_source_instance_id IN NUMBER,p_wip_job_id IN NUMBER ) IS
4103 SELECT 'x'
4104 FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
4105 WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
4106 AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
4107 AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
4108 AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
4109 AND CI1.WIP_JOB_ID= p_wip_job_id
4110 AND CI1.instance_id <> p_source_instance_id
4111 AND CI2.instance_id = p_source_instance_id
4112 AND CI1.LOCATION_TYPE_CODE='WIP'
4113 --AND CI1.INSTANCE_USAGE_CODE IS NOT NULL
4114 AND NVL(CI1.INSTANCE_USAGE_CODE,'x') <> 'UNUSABLE'
4115 --AND CI1.quantity = 0
4116 AND CI1.unit_of_measure = CI2.unit_of_measure
4117 AND CI1.ACTIVE_START_DATE <= SYSDATE
4118 AND CI1.ACTIVE_END_DATE IS NOT NULL AND CI1.ACTIVE_END_DATE < SYSDATE
4119 AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
4120 WHERE CIR.SUBJECT_ID = CI1.INSTANCE_ID
4121 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
4122 AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
4123 AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
4124
4125 l_inactive_item_found BOOLEAN;
4126 l_junk VARCHAR2(1);
4127
4128 CURSOR is_open_job_instance_csr(p_instance_id IN NUMBER) IS
4129 SELECT 'x' from ahl_visit_tasks_b VST, ahl_workorders WO where
4130 VST.instance_id = p_instance_id
4131 AND VST.visit_task_id = WO.visit_task_id
4132 AND VST.visit_id = WO.visit_id
4133 AND WO.status_code NOT IN ('4','5','7','12','17','22');
4134
4135 l_open_wip_job_source BOOLEAN;
4136 l_open_wip_job_dest BOOLEAN;
4137
4138
4139 BEGIN
4140 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4141 fnd_log.string
4142 (
4143 fnd_log.level_procedure,
4144 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
4145 'At the start of PLSQL procedure'
4146 );
4147 END IF;
4148
4149 OPEN get_instance_attrib_csr(p_source_instance_id);
4150 FETCH get_instance_attrib_csr INTO l_source_inst_rec;
4151 CLOSE get_instance_attrib_csr;
4152
4153 OPEN is_open_job_instance_csr(p_source_instance_id);
4154 FETCH is_open_job_instance_csr INTO l_junk;
4155 IF(is_open_job_instance_csr%NOTFOUND)THEN
4156 l_open_wip_job_source := FALSE;
4157 ELSE
4158 l_open_wip_job_source := TRUE;
4159 END IF;
4160 CLOSE is_open_job_instance_csr;
4161
4162
4163 l_item_found_in_dest_wo := FALSE;
4164
4165 FOR dest_instance_rec IN dest_instance_csr(l_source_inst_rec.inventory_item_id,
4166 l_source_inst_rec.inv_master_organization_id,
4167 p_dest_wip_job_id,
4168 --l_source_inst_rec.location_id,
4169 l_source_inst_rec.unit_of_measure,
4170 p_source_instance_id
4171 )LOOP
4172 IF( NVL(dest_instance_rec.lot_number,'X') = NVL(l_source_inst_rec.lot_number,'X') AND
4173 NVL(dest_instance_rec.inventory_revision,'X') = NVL(l_source_inst_rec.inventory_revision,'X')) THEN
4174 l_item_found_in_dest_wo := TRUE;
4175 l_dest_instance_rec := dest_instance_rec;
4176
4177 OPEN is_open_job_instance_csr(l_dest_instance_rec.instance_id);
4178 FETCH is_open_job_instance_csr INTO l_junk;
4179 IF(is_open_job_instance_csr%NOTFOUND)THEN
4180 l_open_wip_job_dest := FALSE;
4181 ELSE
4182 l_open_wip_job_dest := TRUE;
4183 END IF;
4184 CLOSE is_open_job_instance_csr;
4185 END IF;
4186 END LOOP;
4187
4188 IF(l_item_found_in_dest_wo AND l_source_inst_rec.quantity <> p_move_quantity) THEN
4189 -- item in destination and not a full move. Should be OK as nothing gets expired
4190 -- add to dest instance quantity
4191 l_instance_rec.instance_id := l_dest_instance_rec.instance_id;
4192 l_instance_rec.quantity := l_dest_instance_rec.quantity + p_move_quantity;
4193 l_instance_rec.object_version_number := l_dest_instance_rec.object_version_number;
4194 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4195 fnd_log.string
4196 (
4197 fnd_log.level_statement,
4198 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4199 'Item Found in destination'
4200 );
4201 fnd_log.string
4202 (
4203 fnd_log.level_statement,
4204 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4205 'l_dest_instance_rec.instance_id ' || l_dest_instance_rec.instance_id
4206 );
4207 END IF;
4208 --l_instance_rec.wip_job_id := l_dest_instance_rec.wip_job_id;
4209 update_csi_item_instance(
4210 p_instance_rec => l_instance_rec,
4211 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
4212 x_Return_Status => X_Return_Status
4213 );
4214 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
4215 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
4216 fnd_log.string
4217 (
4218 fnd_log.level_error,
4219 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4220 'Item in destination: dest Non Serialized update_csi_item_instance returned error '
4221 );
4222 END IF;
4223 RAISE FND_API.G_EXC_ERROR;
4224 END IF;
4225 -- reduce quantity and/or expire source insance
4226 l_instance_rec.instance_id := l_source_inst_rec.instance_id;
4227 l_instance_rec.quantity := l_source_inst_rec.quantity - p_move_quantity;
4228 l_instance_rec.object_version_number := l_source_inst_rec.object_version_number;
4229
4230 update_csi_item_instance(
4231 p_instance_rec => l_instance_rec,
4232 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
4233 x_Return_Status => X_Return_Status
4234 );
4235 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4236 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
4237 fnd_log.string
4238 (
4239 fnd_log.level_error,
4240 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4241 'Item in destination: source Non Serialized update_csi_item_instance returned error '
4242 );
4243 END IF;
4244 RAISE FND_API.G_EXC_ERROR;
4245 END IF;
4246 ELSIF(l_item_found_in_dest_wo AND l_source_inst_rec.quantity = p_move_quantity) THEN
4247 -- item in destination and is a full move. Make sure that item with open job do not get expired
4248 -- add to dest instance quantity
4249
4250
4251 l_instance_rec.instance_id := l_dest_instance_rec.instance_id;
4252 IF( NOT l_open_wip_job_source) THEN
4253 l_instance_rec.quantity := l_dest_instance_rec.quantity + p_move_quantity;
4254 ELSIF (NOT l_open_wip_job_dest) THEN
4255 l_instance_rec.quantity := 0;
4256 ELSE
4257 l_instance_rec.quantity := l_dest_instance_rec.quantity + p_move_quantity;
4258 END IF;
4259 l_instance_rec.object_version_number := l_dest_instance_rec.object_version_number;
4260 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4261 fnd_log.string
4262 (
4263 fnd_log.level_statement,
4264 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4265 'Item Found in destination'
4266 );
4267 fnd_log.string
4268 (
4269 fnd_log.level_statement,
4270 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4271 'l_dest_instance_rec.instance_id ' || l_dest_instance_rec.instance_id
4272 );
4273 END IF;
4274 --l_instance_rec.wip_job_id := l_dest_instance_rec.wip_job_id;
4275 update_csi_item_instance(
4276 p_instance_rec => l_instance_rec,
4277 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
4278 x_Return_Status => X_Return_Status
4279 );
4280 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
4281 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
4282 fnd_log.string
4283 (
4284 fnd_log.level_error,
4285 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4286 'Item in destination: dest Non Serialized update_csi_item_instance returned error '
4287 );
4288 END IF;
4289 RAISE FND_API.G_EXC_ERROR;
4290 END IF;
4291 -- reduce quantity and/or expire source insance
4292 l_instance_rec.instance_id := l_source_inst_rec.instance_id;
4293 IF( NOT l_open_wip_job_source) THEN
4294 l_instance_rec.quantity := l_source_inst_rec.quantity - p_move_quantity;
4295 ELSIF (NOT l_open_wip_job_dest) THEN
4296 l_instance_rec.quantity := l_source_inst_rec.quantity + l_dest_instance_rec.quantity;
4297 l_instance_rec.wip_job_id := p_dest_wip_job_id; --now change the location
4298 ELSE
4299 l_instance_rec.quantity := l_source_inst_rec.quantity - p_move_quantity;
4300 END IF;
4301 l_instance_rec.object_version_number := l_source_inst_rec.object_version_number;
4302
4303 update_csi_item_instance(
4304 p_instance_rec => l_instance_rec,
4305 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
4306 x_Return_Status => X_Return_Status
4307 );
4308 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4309 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
4310 fnd_log.string
4311 (
4312 fnd_log.level_error,
4313 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4314 'Item in destination: source Non Serialized update_csi_item_instance returned error '
4315 );
4316 END IF;
4317 RAISE FND_API.G_EXC_ERROR;
4318 END IF;
4319 ELSE -- no item in destination
4320 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4321 fnd_log.string
4322 (
4323 fnd_log.level_statement,
4324 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4325 'Active Item Not Found in destination'
4326 );
4327 fnd_log.string
4328 (
4329 fnd_log.level_statement,
4330 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4331 'l_source_inst_rec.instance_id ' || l_source_inst_rec.instance_id
4332 );
4333 fnd_log.string
4334 (
4335 fnd_log.level_statement,
4336 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4337 'l_source_inst_rec.quantity ' || l_source_inst_rec.quantity
4338 );
4339 fnd_log.string
4340 (
4341 fnd_log.level_statement,
4342 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4343 'p_dest_wip_job_id ' || p_dest_wip_job_id
4344 );
4345 fnd_log.string
4346 (
4347 fnd_log.level_statement,
4348 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4349 'p_move_quantity ' || p_move_quantity
4350 );
4351 END IF;
4352 l_inactive_item_found := TRUE;
4353 OPEN dest_inactive_instance_csr(p_source_instance_id,p_dest_wip_job_id);
4354 FETCH dest_inactive_instance_csr INTO l_junk;
4355 IF(dest_inactive_instance_csr%NOTFOUND)THEN
4356 l_inactive_item_found := FALSE;
4357 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4358 fnd_log.string
4359 (
4360 fnd_log.level_statement,
4361 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4362 'No inactive item either in destination '
4363 );
4364 END IF;
4365 END IF;
4366 CLOSE dest_inactive_instance_csr;
4367 -- check if full quantity is being moved, if yes, just change location of source
4368 IF(l_source_inst_rec.quantity = p_move_quantity AND NOT(l_inactive_item_found))THEN
4369 l_instance_rec.instance_id := l_source_inst_rec.instance_id;
4370 l_instance_rec.wip_job_id := p_dest_wip_job_id;
4371 l_instance_rec.object_version_number := l_source_inst_rec.object_version_number;
4372 update_csi_item_instance(
4373 p_instance_rec => l_instance_rec,
4374 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
4375 x_Return_Status => X_Return_Status
4376 );
4377 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4378 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
4379 fnd_log.string
4380 (
4381 fnd_log.level_error,
4382 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4383 'Item NOT in destination: Full Move update_csi_item_instance returned error '
4384 );
4385 END IF;
4386 RAISE FND_API.G_EXC_ERROR;
4387 END IF;
4388 ELSE -- else create instance at dest with move qty and reduce source qty
4389 -- or reuse an expired instance create_similar_instance API does that
4390 create_similar_instance(
4391 p_source_instance_id => l_source_inst_rec.instance_id,
4392 p_dest_quantity => p_move_quantity,
4393 p_dest_wip_job_id => p_dest_wip_job_id,
4394 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
4395 x_dest_instance_id => l_dest_instance_id,
4396 x_return_status => X_Return_Status
4397 );
4398 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
4399 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
4400 fnd_log.string
4401 (
4402 fnd_log.level_error,
4403 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4404 'Item NOT in destination: Partial Move create_similar_instance returned error '
4405 );
4406 END IF;
4407 RAISE FND_API.G_EXC_ERROR;
4408 END IF;
4409 -- reduce quantity and/or expire source insance
4410 l_instance_rec.instance_id := l_source_inst_rec.instance_id;
4411 l_instance_rec.quantity := l_source_inst_rec.quantity - p_move_quantity;
4412 l_instance_rec.object_version_number := l_source_inst_rec.object_version_number;
4413 update_csi_item_instance(
4414 p_instance_rec => l_instance_rec,
4415 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
4416 x_Return_Status => X_Return_Status
4417 );
4418 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4419 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
4420 fnd_log.string
4421 (
4422 fnd_log.level_error,
4423 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4424 'Item NOT in destination: Partial Move/Update Source qty update_csi_item_instance returned error '
4425 );
4426 END IF;
4427 RAISE FND_API.G_EXC_ERROR;
4428 END IF;
4429 END IF;
4430 END IF;
4431
4432 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4433 fnd_log.string
4434 (
4435 fnd_log.level_procedure,
4436 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.end',
4437 'At the end of PLSQL procedure'
4438 );
4439 END IF;
4440 END move_nonser_instance;
4441
4442
4443 PROCEDURE create_similar_instance(
4444 p_source_instance_id IN NUMBER,
4445 p_dest_quantity IN NUMBER,
4446 p_dest_wip_job_id IN NUMBER,
4447 p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
4448 x_dest_instance_id OUT NOCOPY NUMBER,
4449 x_return_status OUT NOCOPY VARCHAR2
4450 ) IS
4451 l_api_name CONSTANT VARCHAR2(30) := 'create_similar_instance';
4452 l_source_instance_rec csi_datastructures_pub.instance_rec;
4453 l_dest_instance_rec csi_datastructures_pub.instance_rec;
4454 l_new_instance_tbl csi_datastructures_pub.instance_tbl;
4455
4456 l_msg_count NUMBER;
4457 l_msg_data VARCHAR2(2000);
4458
4459 CURSOR get_instance_attrib_csr(p_instance_id NUMBER) IS
4460 SELECT instance_id,
4461 object_version_number,
4462 INVENTORY_REVISION,
4463 LOT_NUMBER,
4464 LOCATION_ID,
4465 INSTANCE_STATUS_ID
4466 FROM csi_item_instances
4467 WHERE instance_id = p_instance_id;
4468
4469 CURSOR dest_instance_csr(p_wip_job_id IN NUMBER,
4470 p_source_instance_id IN NUMBER ) IS
4471 SELECT CI1.instance_id
4472 FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
4473 WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
4474 AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
4475 AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
4476 AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
4477 AND CI1.WIP_JOB_ID= p_wip_job_id
4478 AND CI1.instance_id <> CI2.instance_id
4479 AND CI2.instance_id = p_source_instance_id
4480 AND CI1.LOCATION_TYPE_CODE='WIP'
4481 AND CI1.INSTANCE_USAGE_CODE = 'IN_WIP'
4482 --AND CI1.quantity = 0
4483 AND CI1.unit_of_measure = CI2.unit_of_measure
4484 AND CI1.ACTIVE_START_DATE <= SYSDATE
4485 AND CI1.ACTIVE_END_DATE IS NOT NULL AND CI1.ACTIVE_END_DATE < SYSDATE
4486 AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
4487 WHERE CIR.SUBJECT_ID = CI1.INSTANCE_ID
4488 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
4489 AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
4490 AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
4491
4492 CURSOR dest_instance_csr1(p_wip_job_id IN NUMBER,
4493 p_source_instance_id IN NUMBER ) IS
4494 SELECT CI1.instance_id
4495 FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
4496 WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
4497 AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
4498 AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
4499 AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
4500 AND CI1.WIP_JOB_ID= p_wip_job_id
4501 AND CI1.instance_id <> CI2.instance_id
4502 AND CI2.instance_id = p_source_instance_id
4503 AND CI1.LOCATION_TYPE_CODE='WIP'
4504 AND NVL(CI1.INSTANCE_USAGE_CODE,'x') <> 'UNUSABLE'
4505 --AND CI1.quantity = 0
4506 AND CI1.unit_of_measure = CI2.unit_of_measure
4507 AND CI1.ACTIVE_START_DATE <= SYSDATE
4508 AND CI1.ACTIVE_END_DATE IS NOT NULL AND CI1.ACTIVE_END_DATE < SYSDATE
4509 AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
4510 WHERE CIR.SUBJECT_ID = CI1.INSTANCE_ID
4511 AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
4512 AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
4513 AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
4514
4515 l_copy_instance BOOLEAN;
4516
4517 BEGIN
4518 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4519 fnd_log.string
4520 (
4521 fnd_log.level_procedure,
4522 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
4523 'At the start of PLSQL procedure'
4524 );
4525 END IF;
4526
4527 l_copy_instance := TRUE;
4528 OPEN dest_instance_csr(p_dest_wip_job_id,p_source_instance_id );
4529 FETCH dest_instance_csr INTO l_dest_instance_rec.instance_id;
4530 IF(dest_instance_csr%FOUND)THEN
4531 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4532 fnd_log.string
4533 (
4534 fnd_log.level_statement,
4535 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4536 'Expired instance found'
4537 );
4538 END IF;
4539 l_copy_instance := FALSE;
4540 END IF;
4541 CLOSE dest_instance_csr;
4542
4543 IF(l_copy_instance)THEN
4544 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4545 fnd_log.string
4546 (
4547 fnd_log.level_statement,
4548 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4549 'Searching for a dummy one'
4550 );
4551 END IF;
4552 OPEN dest_instance_csr1(p_dest_wip_job_id,p_source_instance_id );
4553 FETCH dest_instance_csr1 INTO l_dest_instance_rec.instance_id;
4554 IF(dest_instance_csr1%FOUND)THEN
4555 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4556 fnd_log.string
4557 (
4558 fnd_log.level_statement,
4559 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4560 'Dummy instance found '
4561 );
4562 END IF;
4563 l_copy_instance := FALSE;
4564 END IF;
4565 CLOSE dest_instance_csr1;
4566 END IF;
4567
4568 IF(l_copy_instance)THEN
4569 OPEN get_instance_attrib_csr(p_source_instance_id);
4570 FETCH get_instance_attrib_csr INTO l_source_instance_rec.instance_id,
4571 l_source_instance_rec.object_version_number,
4572 l_source_instance_rec.INVENTORY_REVISION,
4573 l_source_instance_rec.LOT_NUMBER,
4574 l_source_instance_rec.LOCATION_ID,
4575 l_source_instance_rec.INSTANCE_STATUS_ID;
4576 CLOSE get_instance_attrib_csr;
4577
4578 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4579 fnd_log.string
4580 (
4581 fnd_log.level_statement,
4582 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4583 'l_source_instance_rec.instance_id ' || l_source_instance_rec.instance_id
4584 );
4585 fnd_log.string
4586 (
4587 fnd_log.level_statement,
4588 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4589 'l_source_instance_rec.object_version_number ' || l_source_instance_rec.object_version_number
4590 );
4591 fnd_log.string
4592 (
4593 fnd_log.level_statement,
4594 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4595 'p_dest_quantity ' || p_dest_quantity
4596 );
4597 fnd_log.string
4598 (
4599 fnd_log.level_statement,
4600 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4601 'p_dest_wip_job_id ' || p_dest_wip_job_id
4602 );
4603 END IF;
4604
4605 CSI_ITEM_INSTANCE_PUB.copy_item_instance
4606 (
4607 p_api_version => 1.0
4608 ,p_commit => fnd_api.g_false
4609 ,p_init_msg_list => fnd_api.g_true
4610 ,p_validation_level => fnd_api.g_valid_level_full
4611 ,p_source_instance_rec => l_source_instance_rec
4612 ,p_copy_ext_attribs => fnd_api.g_true
4613 ,p_copy_org_assignments => fnd_api.g_true
4614 ,p_copy_parties => fnd_api.g_true
4615 ,p_copy_party_contacts => fnd_api.g_true
4616 ,p_copy_accounts => fnd_api.g_true
4617 ,p_copy_asset_assignments => fnd_api.g_true
4618 ,p_copy_pricing_attribs => fnd_api.g_true
4619 ,p_copy_inst_children => fnd_api.g_true
4620 ,p_txn_rec => p_x_csi_transaction_rec
4621 ,x_new_instance_tbl => l_new_instance_tbl
4622 ,x_return_status => x_return_status
4623 ,x_msg_count => l_msg_count
4624 ,x_msg_data => l_msg_data
4625 );
4626
4627 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4628 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
4629 fnd_log.string
4630 (
4631 fnd_log.level_error,
4632 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4633 'CSI_ITEM_INSTANCE_PUB.copy_item_instance returned error '
4634 );
4635 END IF;
4636 RAISE FND_API.G_EXC_ERROR;
4637 END IF;
4638
4639 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4640 fnd_log.string
4641 (
4642 fnd_log.level_statement,
4643 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4644 'Instance copied successfully'
4645 );
4646 fnd_log.string
4647 (
4648 fnd_log.level_statement,
4649 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4650 'l_new_instance_tbl(1).instance_id : ' ||l_new_instance_tbl(1).instance_id
4651 );
4652
4653 END IF;
4654 l_dest_instance_rec.instance_id := l_new_instance_tbl(1).instance_id;
4655 ELSE
4656 l_dest_instance_rec.active_end_date := NULL;
4657 l_dest_instance_rec.instance_usage_code := 'IN_WIP';
4658
4659 END IF;
4660
4661 OPEN get_instance_attrib_csr(l_dest_instance_rec.instance_id);
4662 FETCH get_instance_attrib_csr INTO l_dest_instance_rec.instance_id,
4663 l_dest_instance_rec.object_version_number,
4664 l_dest_instance_rec.INVENTORY_REVISION,
4665 l_dest_instance_rec.LOT_NUMBER,
4666 l_dest_instance_rec.LOCATION_ID,
4667 l_dest_instance_rec.INSTANCE_STATUS_ID;
4668 CLOSE get_instance_attrib_csr;
4669
4670 -- if opening an expired instance
4671 IF(NOT l_copy_instance) THEN
4672 l_dest_instance_rec.INSTANCE_STATUS_ID := 510; -- as good as created
4673 END IF;
4674
4675 l_dest_instance_rec.quantity := p_dest_quantity;
4676 l_dest_instance_rec.wip_job_id := p_dest_wip_job_id;
4677
4678
4679 update_csi_item_instance
4680 (
4681 p_instance_rec => l_dest_instance_rec,
4682 p_x_csi_transaction_rec => p_x_csi_transaction_rec,
4683 x_Return_Status => X_Return_Status
4684 );
4685 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4686 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
4687 fnd_log.string
4688 (
4689 fnd_log.level_error,
4690 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name,
4691 'Updating copied instance loc: update_csi_item_instance returned error '
4692 );
4693 END IF;
4694 RAISE FND_API.G_EXC_ERROR;
4695 END IF;
4696 x_dest_instance_id := l_dest_instance_rec.instance_id;
4697
4698
4699 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4700 fnd_log.string
4701 (
4702 fnd_log.level_procedure,
4703 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name ||'.begin',
4704 'At the end of PLSQL procedure'
4705 );
4706 END IF;
4707
4708 END create_similar_instance;
4709
4710 -- Function to get unit on visit or visit task.
4711 FUNCTION Get_UnitConfig_ID(p_workorder_id IN NUMBER)
4712 RETURN NUMBER IS
4713
4714 CURSOR get_vst_instance_csr (p_workorder_id IN NUMBER) IS
4715
4716 SELECT
4717 NVL(VST.ITEM_INSTANCE_ID, VTS.INSTANCE_ID)
4718 FROM
4719 AHL_WORKORDERS AWOS,
4720 AHL_VISITS_B VST,
4721 AHL_VISIT_TASKS_B VTS
4722 WHERE
4723 AWOS.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
4724 VST.VISIT_ID = VTS.VISIT_ID AND
4725 WORKORDER_ID = p_workorder_id;
4726
4727 -- declare local variables here
4728 l_item_instance_id NUMBER;
4729 l_uc_header_id NUMBER;
4730
4731 BEGIN
4732 IF p_workorder_id IS NULL
4733 THEN
4734 RETURN -1;
4735 END IF;
4736
4737 OPEN get_vst_instance_csr(p_workorder_id);
4738 FETCH get_vst_instance_csr INTO l_item_instance_id;
4739 CLOSE get_vst_instance_csr;
4740
4741 l_uc_header_id := AHL_UTIL_UC_PKG.get_uc_header_id(l_item_instance_id);
4742
4743 RETURN l_uc_header_id;
4744
4745 END Get_UnitConfig_ID;
4746
4747 END; -- Package Body AHL_PRD_PART_CHANGE