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