[Home] [Help]
PACKAGE BODY: APPS.CSE_PROJ_ITEM_UNINST_PKG
Source
1 package body CSE_PROJ_ITEM_UNINST_PKG AS
2 -- $Header: CSEITUIB.pls 115.28 2003/09/22 16:06:52 stutika ship $
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('CSE_DEBUG_OPTION'),'N');
5
6
7 PROCEDURE Decode_Message(
8 P_Msg_Header IN XNP_MESSAGE.Msg_Header_Rec_Type,
9 P_Msg_Text IN VARCHAR2,
10 X_Proj_Item_Uninst_Attr_Rec OUT NOCOPY Proj_Item_Uninst_Attr_Rec_Type,
11 X_Return_Status OUT NOCOPY VARCHAR2,
12 X_Error_Message OUT NOCOPY VARCHAR2) IS
13
14 l_Api_Name CONSTANT VARCHAR2(30) := 'CSE_PROJ_ITEM_UNINST_PKG';
15 l_Item_Id NUMBER;
16 l_Revision VARCHAR2(3);
17 l_Lot_Number VARCHAR2(30);
18 l_Serial_Number VARCHAR2(30);
19 l_Quantity NUMBER;
20 l_Netwk_Locn_id NUMBER;
21 l_party_site_id NUMBER;
22 l_Work_Order_Number VARCHAR2(30);
23 l_Project_Id NUMBER;
24 l_Task_Id NUMBER;
25 l_Transaction_Date DATE;
26 l_Transaction_Date_Str VARCHAR2(30);
27 l_Transacted_By NUMBER;
28
29 BEGIN
30
31 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
32 X_Error_Message := Null;
33
34 Xnp_Xml_Utils.Decode(P_Msg_Text,'ITEM_ID',l_Item_Id);
35 Xnp_Xml_Utils.Decode(P_Msg_Text,'REVISION',l_Revision);
36 Xnp_Xml_Utils.Decode(P_Msg_Text,'LOT_NUMBER',l_Lot_Number);
37 Xnp_Xml_Utils.Decode(P_Msg_Text,'SERIAL_NUMBER',l_Serial_Number);
38 Xnp_Xml_Utils.Decode(P_Msg_Text,'QUANTITY',l_Quantity);
39 Xnp_Xml_Utils.Decode(P_Msg_Text,'NETWORK_LOC_ID',l_Netwk_Locn_id);
40 Xnp_Xml_Utils.Decode(P_Msg_Text,'PARTY_SITE_ID',l_party_site_id);
41 Xnp_Xml_Utils.Decode(P_Msg_Text,'WORK_ORDER_NUMBER',l_Work_Order_Number);
42 Xnp_Xml_Utils.Decode(P_Msg_Text,'PROJECT_ID',l_Project_Id);
43 Xnp_Xml_Utils.Decode(P_Msg_Text,'TASK_ID',l_Task_Id);
44 Xnp_Xml_Utils.Decode(P_Msg_Text,'TRANSACTION_DATE',l_Transaction_Date_Str);
45 Xnp_Xml_Utils.Decode(P_Msg_Text,'TRANSACTED_BY',l_Transacted_By);
46
47
48
49 X_Proj_Item_Uninst_Attr_Rec.Item_Id := l_Item_Id;
50 X_Proj_Item_Uninst_Attr_Rec.Revision := l_Revision;
51 X_Proj_Item_Uninst_Attr_Rec.Lot_Number := l_Lot_Number;
52 X_Proj_Item_Uninst_Attr_Rec.Serial_Number := l_Serial_Number;
53 X_Proj_Item_Uninst_Attr_Rec.Quantity := l_Quantity;
54 X_Proj_Item_Uninst_Attr_Rec.Network_Location_Id := l_Netwk_Locn_Id;
55 X_Proj_Item_Uninst_Attr_Rec.Party_Site_Id := l_Party_Site_Id;
56 X_Proj_Item_Uninst_Attr_Rec.Work_Order_Number := l_Work_Order_Number;
57 X_Proj_Item_Uninst_Attr_Rec.Project_Id := l_Project_Id;
58 X_Proj_Item_Uninst_Attr_Rec.Task_Id := l_Task_Id;
59 X_Proj_Item_Uninst_Attr_Rec.Transaction_Date := TO_DATE(l_Transaction_Date_Str, 'YYYY/MM/DD HH24:MI:SS');
60 X_Proj_Item_Uninst_Attr_Rec.Transacted_By := l_Transacted_By;
61 X_Proj_Item_Uninst_Attr_Rec.Message_id := P_Msg_Header.Message_Id;
62
63 EXCEPTION
64
65 WHEN OTHERS THEN
66 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
67 fnd_message.set_token('ERR_MSG',l_Api_Name||'='|| SQLERRM);
68 x_error_message := fnd_message.get;
69 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
70
71 END Decode_Message;
72
73 PROCEDURE Update_Ib_Repository(
74 P_Proj_Item_Uninst_Attr_Rec IN Proj_Item_Uninst_Attr_Rec_Type,
75 X_Return_Status OUT NOCOPY VARCHAR2,
76 X_Error_Message OUT NOCOPY VARCHAR2) IS
77
78 l_Api_Name CONSTANT VARCHAR2(30) :='CSE_PROJ_ITEM_UNINST_PKG';
79 l_api_version NUMBER DEFAULT 1.0;
80 l_Commit VARCHAR2(1);
81 l_Init_Msg_List VARCHAR2(1);
82 l_creatE_dest_inst_flag VARCHAR2(1);
83 l_resolve_id_columns VARCHAR2(1);
84 l_Active_Instance_Only VARCHAR2(1);
85 l_Validation_Level NUMBER;
86 l_Resolve_Id_Column VARCHAR2(1);
87 l_Instance_Header_Tbl_Out csi_datastructures_pub.instance_header_tbl;
88 l_Instance_Rec csi_datastructures_pub.Instance_Rec;
89 l_Dest_Instance_Rec csi_datastructures_pub.Instance_Rec;
90 l_Instance_Query_Rec csi_datastructures_pub.Instance_Query_Rec;
91 l_txn_rec csi_datastructures_pub.transaction_rec;
92 l_upd_txn_rec csi_datastructures_pub.transaction_rec;
93 l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
94 l_Party_Query_Rec csi_datastructures_pub.party_query_rec;
95 l_party_tbl csi_datastructures_pub.party_tbl;
96 l_account_tbl csi_datastructures_pub.party_account_tbl;
97 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
98 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
99 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
100 l_Instance_Id_Lst csi_datastructures_pub.Id_Tbl;
101 l_msg_index NUMBER;
102 i PLS_INTEGER;
103 l_S_object_version_number NUMBER :=1;
104 l_D_object_version_number NUMBER :=1;
105 l_object_version_number NUMBER :=1;
106 l_Msg_Count NUMBER;
107 l_Msg_Data VARCHAR2(2000);
108 l_Account_Query_Rec csi_datastructures_pub.party_account_query_rec;
109 l_Transaction_Id NUMBER;
110 l_Return_Status VARCHAR2(1);
111 l_Error_Message VARCHAR2(2000);
112 l_Hz_Location_Id NUMBER;
113 l_Location_Type_Code VARCHAR2(30) ;
114 l_Transaction_Type_Id NUMBER;
115 l_App_Short_Name CONSTANT VARCHAR2(10):='CSE';
116 l_Source_Quantity NUMBER;
117 l_Source_Instance_Id NUMBER;
118 l_Source_Transaction_Id NUMBER;
119 l_Dest_Transaction_Id NUMBER;
120 l_Inv_Organization_Id NUMBER;
121 l_Txn_Status VARCHAR2(20);
122 l_sysdate DATE:=sysdate;
123 l_Instance_Status VARCHAR2(30);
124 l_s_last_pa_project_id NUMBER;
125 l_s_last_pa_task_id NUMBER;
126 l_S_Inv_Master_Organization_Id NUMBER;
127 l_Source_Unit_Of_Measure VARCHAR2(3);
128 l_Txn_Status VARCHAR2(20);
129 l_Source_Transaction_Id NUMBER;
130 l_Destination_Transaction_Id NUMBER;
131 l_file VARCHAR2(500);
132 l_s_status_id NUMBER;
133 t_transaction_id NUMBER;
134 CURSOR inst_status_cur IS
135 SELECT instance_status_id
136 FROM csi_instance_statuses
137 WHERE upper(name) ='EXPIRED';
138
139 Update_Ib_Exp EXCEPTION;
140
141 BEGIN
142 l_Commit := FND_API.G_FALSE;
143 l_Init_Msg_List := FND_API.G_TRUE;
144 l_creatE_dest_inst_flag := FND_API.G_FALSE;
145 l_resolve_id_columns := FND_API.G_FALSE;
146 l_Active_Instance_Only := FND_API.G_FALSE;
147 l_Validation_Level := fnd_api.g_valid_level_full;
148 l_Resolve_Id_Column := FND_API.G_FALSE;
149 t_transaction_id := -1;
150 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
151 X_Error_Message := Null;
152
153 -- If the option is set turn on the debug log.
154
155 IF (l_debug = 'Y') THEN
156 cse_debug_pub.g_dir := nvl(FND_PROFILE.VALUE('CSE_DEBUG_LOG_DIRECTORY'), '/tmp');
157 cse_debug_pub.g_file := NULL;
158 l_file := cse_debug_pub.set_debug_file('cse' || to_char(l_sysdate, 'DD-MON-YYYY') || '.log');
159 cse_debug_pub.debug_on;
160 END IF;
161
162 -- l_Network_Location_Code := P_Proj_Item_Uninst_Attr_Rec.Network_Location_Code;
163 If P_Proj_Item_Uninst_Attr_Rec.Network_Location_id is not null then
164 l_hz_location_id :=P_Proj_Item_Uninst_Attr_Rec.Network_Location_id ;
165 l_location_type_code:='HZ_LOCATIONS';
166 elsif P_Proj_Item_Uninst_Attr_Rec.party_site_id is not null then
167 l_hz_location_id :=P_Proj_Item_Uninst_Attr_Rec.party_site_id ;
168 l_location_type_code:='HZ_PARTY_SITES';
169 end if;
170 l_Transaction_Type_Id := CSE_UTIL_PKG.Get_Txn_Type_ID
171 ('PROJECT_ITEM_UNINSTALLED',
172 l_App_Short_Name);
173
174 IF (l_debug = 'Y') THEN
175 CSE_DEBUG_PUB.ADD('**** PROJECT ITEM UNINSTALLED TRANSACTION ******');
176 CSE_DEBUG_PUB.ADD('-----------------------------------------------');
177 END IF;
178
179 --Step 2, Query for the existence of the source record
180
181 IF (l_debug = 'Y') THEN
182 CSE_DEBUG_PUB.ADD('Initailizing Query Record for the source item instance - step2');
183 END IF;
184
185 l_Instance_Query_Rec := CSE_UTIL_PKG.Init_Instance_Query_Rec;
186 l_Instance_Query_Rec.Inventory_Item_Id:= P_Proj_Item_Uninst_Attr_Rec.Item_Id;
187 l_Instance_Query_Rec.Inventory_Revision:= P_Proj_Item_Uninst_Attr_Rec.Revision;
188 l_Instance_Query_Rec.Lot_Number := P_Proj_Item_Uninst_Attr_Rec.Lot_Number;
189 l_Instance_Query_Rec.Serial_Number:= P_Proj_Item_Uninst_Attr_Rec.Serial_Number;
190 l_Instance_Query_Rec.Last_Pa_Project_Id:= P_Proj_Item_Uninst_Attr_Rec.project_id;
191 l_Instance_Query_Rec.Last_Pa_Task_Id := P_Proj_Item_Uninst_Attr_Rec.Task_Id;
192 l_Instance_Query_Rec.Location_Id := l_Hz_Location_Id;
193 l_Instance_Query_Rec.Location_Type_Code:= l_Location_Type_Code;
194 l_Instance_Query_Rec.Instance_Usage_Code:=CSE_DATASTRUCTURES_PUB.G_INSTALLED;
195
196 IF (l_debug = 'Y') THEN
197 CSE_DEBUG_PUB.ADD(l_Api_Name||' Before CSI_Item_Instance_Pub.Get_Item_Instance');
198 END IF;
199
200 CSI_Item_Instance_Pub.Get_Item_Instances(
201 p_api_version =>l_api_version,
202 p_commit =>l_Commit,
203 p_init_msg_list =>l_Init_Msg_List,
204 p_validation_level =>l_Validation_Level,
205 p_instance_Query_rec =>l_Instance_Query_Rec,
206 p_party_query_rec =>l_Party_Query_Rec,
207 p_account_query_rec =>l_account_query_rec,
208 p_active_instance_only =>l_Active_Instance_Only,
209 x_Instance_Header_Tbl =>l_Instance_Header_Tbl_Out,
210 p_transaction_id =>l_transaction_id,
211 p_resolve_id_columns =>l_resolve_id_columns,
212 x_return_status =>l_Return_Status,
213 x_msg_count =>l_Msg_Count,
214 x_msg_data =>l_Msg_Data );
215
216 -- get the error message from the stack if there is any error
217
218 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
219 l_msg_index := 1;
220 l_Error_Message:=l_Msg_Data;
221 WHILE l_msg_count > 0 LOOP
222 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
223 l_msg_index := l_msg_index + 1;
224 l_Msg_Count := l_Msg_Count - 1;
225 END LOOP;
226 RAISE Update_Ib_Exp;
227 END IF;
228
229 --raise exception if the source record is not found
230
231 IF(l_Instance_Header_Tbl_Out.COUNT=0) THEN
232 fnd_message.set_name('CSE','CSE_SRC_RECORD_NOTFOUND');
233 fnd_message.set_token('ITEM',P_Proj_Item_Uninst_Attr_Rec.Item_Id);
234 l_error_message := fnd_message.get;
235 l_Return_Status := FND_API.G_RET_STS_ERROR;
236 RAISE Update_Ib_Exp;
237 END IF;
238
239
240 -- check if there exist multiple source instances
241
242 IF(l_Instance_Header_Tbl_Out.COUNT>1) THEN
243 fnd_message.set_name('CSE','CSE_SRC_MULTIPLE_ITM_INSTANCES');
244 fnd_message.set_token('ITEM',P_Proj_Item_Uninst_Attr_Rec.Item_Id);
245 l_error_message := fnd_message.get;
246 l_Return_Status := FND_API.G_RET_STS_ERROR;
247 RAISE Update_Ib_Exp;
248 END IF;
249
250 -- continue if there exist only one source item instance
251
252 IF(l_Instance_Header_Tbl_Out.COUNT=1) THEN
253 i :=l_Instance_Header_Tbl_Out.FIRST;
254 l_Source_Quantity :=l_Instance_Header_Tbl_Out(i).Quantity;
255 l_source_unit_of_measure :=l_Instance_Header_Tbl_Out(i).Unit_Of_Measure;
256 l_Inv_Organization_Id :=l_Instance_Header_Tbl_Out(i).Inv_Organization_Id;
257 l_Source_Instance_id :=l_Instance_Header_Tbl_Out(i).Instance_Id;
258 l_S_Inv_Master_Organization_Id:=l_Instance_Header_Tbl_Out(i).Inv_Master_Organization_Id;
259 l_S_object_version_number :=l_Instance_Header_Tbl_Out(i).object_version_number;
260 l_s_status_id :=l_Instance_Header_Tbl_Out(i).instance_status_id;
261 l_s_last_pa_project_id :=l_Instance_Header_Tbl_Out(i).last_pa_project_id;
262 l_s_last_pa_task_id :=l_Instance_Header_Tbl_Out(i).last_pa_task_id;
263
264 -- Step 3,Check if the txn_qty is more than source item instance qty
265
266 IF(P_Proj_Item_Uninst_Attr_Rec.Quantity >
267 l_Instance_Header_Tbl_Out(i).Quantity) THEN
268 fnd_message.set_name('CSE','CSE_SRC_ITEM_QUANTITY');
269 fnd_message.set_token('ITEM',P_Proj_Item_Uninst_Attr_Rec.Item_Id);
270 l_error_message := fnd_message.get;
271 l_Return_Status := FND_API.G_RET_STS_ERROR;
272 RAISE Update_Ib_Exp;
273 END IF;
274
275
276 --Step 4,If the Item is serialized,
277
278 IF(P_Proj_Item_Uninst_Attr_Rec.Serial_Number IS NOT NULL) THEN
279
280 IF (l_debug = 'Y') THEN
281 CSE_DEBUG_PUB.ADD('Initailizing instance Record for update of serialized item inst- step4');
282 END IF;
283
284 l_Instance_Rec := CSE_UTIL_PKG.Init_Instance_Update_Rec;
285 l_Instance_Rec.Instance_Id :=l_Source_Instance_Id;
286 l_Instance_Rec.Instance_Usage_Code :=CSE_DATASTRUCTURES_PUB.G_IN_PROCESS;
287 l_Instance_Rec.Location_Type_Code :=CSE_UTIL_PKG.Get_Location_Type_Code('PROJECT');
288 l_Instance_Rec.Location_Id :=CSE_UTIL_PKG.Get_Dflt_Project_Location_Id;
289 l_Instance_Rec.Install_Location_Type_Code:=Null;
290 l_Instance_Rec.Install_Location_Id :=Null;
291 l_Instance_Rec.object_version_number :=l_S_object_version_number;
292 l_Instance_Rec.pa_project_id :=l_s_last_pa_project_id;
293 l_Instance_Rec.pa_project_task_id :=l_s_last_pa_task_id;
294 l_Instance_Rec.last_pa_project_id :=null;
295 l_Instance_Rec.last_pa_task_id :=null;
296 l_Txn_Rec :=CSE_UTIL_PKG.Init_Txn_Rec;
297 l_Txn_Rec.Transaction_Date :=l_sysdate;
298 l_Txn_Rec.Source_Transaction_Date :=P_Proj_Item_Uninst_Attr_Rec.Transaction_Date;
299 l_Txn_Rec.Transaction_Type_Id :=l_Transaction_Type_Id;
300 l_Txn_Rec.Source_Group_Ref := P_Proj_Item_Uninst_Attr_Rec.Work_Order_Number;
301 l_Txn_Rec.Transaction_Quantity := P_Proj_Item_Uninst_Attr_Rec.Quantity;
302 l_Txn_Rec.Transacted_By := P_Proj_Item_Uninst_Attr_Rec.Transacted_By;
303 l_Txn_Rec.Transaction_Status_Code := CSE_DATASTRUCTURES_PUB.G_PENDING;
304 l_Txn_Rec.Message_Id := P_Proj_Item_Uninst_Attr_Rec.Message_Id;
305
306 IF NOT t_transaction_id = -1 THEN
307 l_txn_rec.transaction_id := t_transaction_id;
308 END IF;
309 IF (l_debug = 'Y') THEN
310 CSE_DEBUG_PUB.ADD('Before update of serialized item instanace');
311 END IF;
312
313 CSI_Item_Instance_Pub.Update_Item_Instance(
314 p_api_version => l_api_version,
315 p_commit => l_Commit,
316 p_validation_level => l_Validation_Level,
317 p_init_msg_list =>l_Init_Msg_List,
318 p_instance_rec => l_Instance_Rec,
319 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
320 p_party_tbl => l_party_tbl,
321 p_account_tbl => l_account_tbl,
322 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
323 p_org_assignments_tbl => l_org_assignments_tbl,
324 p_txn_rec =>l_Txn_Rec,
325 p_asset_assignment_tbl => l_asset_assignment_tbl,
326 x_instance_id_lst => l_instance_id_lst,
327 x_return_status => l_Return_Status,
328 x_msg_count => l_Msg_Count,
329 x_msg_data => l_Msg_Data );
330
334 l_Error_Message:=l_msg_data;
331 -- get the error message from the stack if there is any error
332 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
333 l_msg_index := 1;
335 WHILE l_msg_count > 0 LOOP
336 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
337 l_msg_index := l_msg_index + 1;
338 l_Msg_Count := l_Msg_Count - 1;
339 END LOOP;
340 RAISE Update_Ib_Exp;
341 ELSIF l_Return_Status = FND_API.G_Ret_Sts_Success THEN
342 IF t_transaction_id =-1 THEN
343 t_transaction_id := l_txn_rec.transaction_id;
344 END IF;
345 END IF;
346 END IF;
347
348 -- Step 5 ,if the item is non serialized
349
350 IF(P_Proj_Item_Uninst_Attr_Rec.Serial_Number IS NULL) THEN
351
352 --Step 5.1 ,Check for the existence of the destination record
353
354 IF (l_debug = 'Y') THEN
355 CSE_DEBUG_PUB.ADD('Initailizing Query Record for the destination instance - step5.1');
356 END IF;
357
358 l_Instance_Query_Rec := CSE_UTIL_PKG.Init_Instance_Query_Rec;
359 l_Instance_Query_Rec.Inventory_Item_Id:= P_Proj_Item_Uninst_Attr_Rec.Item_Id;
360 l_Instance_Query_Rec.Inventory_Revision:= P_Proj_Item_Uninst_Attr_Rec.Revision;
361 l_Instance_Query_Rec.Lot_Number := P_Proj_Item_Uninst_Attr_Rec.Lot_Number;
362 l_Instance_Query_Rec.Serial_Number:= P_Proj_Item_Uninst_Attr_Rec.Serial_Number;
363 l_Instance_Query_Rec.Pa_Project_Id := P_Proj_Item_Uninst_Attr_Rec.project_id;
364 l_Instance_Query_Rec.Pa_Project_Task_Id:=P_Proj_Item_Uninst_Attr_Rec.Task_Id;
365 l_Instance_Query_Rec.Instance_Usage_Code:= CSE_DATASTRUCTURES_PUB.G_IN_PROCESS;
366
367 l_Active_Instance_Only := FND_API.G_FALSE;
368
369 IF (l_debug = 'Y') THEN
370 CSE_DEBUG_PUB.ADD('Before querying for the existence of destination record');
371 END IF;
372
373 CSI_Item_Instance_Pub.Get_Item_Instances(
374 p_api_version => l_api_version,
375 p_commit => l_Commit,
376 p_init_msg_list => l_Init_Msg_List,
377 p_validation_level => l_Validation_Level,
378 p_instance_Query_rec => l_Instance_Query_Rec,
379 p_party_query_rec => l_Party_Query_Rec,
380 p_account_query_rec => l_account_query_rec,
381 p_active_instance_only => l_Active_Instance_Only,
382 p_transaction_id => l_transaction_id,
383 p_resolve_id_columns => l_resolve_id_columns,
384 x_Instance_Header_Tbl => l_Instance_Header_Tbl_Out,
385 x_return_status => l_Return_Status,
386 x_msg_count => l_Msg_Count,
387 x_msg_data => l_Msg_Data );
388
389 -- get the error message from the stack if there is any error
390
391 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
392 l_msg_index := 1;
393 l_Error_Message:=l_Msg_Data;
394 WHILE l_msg_count > 0
395 LOOP
396 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
397 l_msg_index := l_msg_index + 1;
398 l_Msg_Count := l_Msg_Count - 1;
399 END LOOP;
400 RAISE Update_Ib_Exp;
401 END IF;
402
403 -- update source instance quantity
404
405
406 IF (l_debug = 'Y') THEN
407 CSE_DEBUG_PUB.ADD('Initailizing instance Record for update of source qty - step5.3');
408 END IF;
409
410 l_Instance_Rec := CSE_UTIL_PKG.Init_Instance_Update_Rec;
411 l_Instance_Rec.Instance_Id := l_Source_instance_id;
412 l_Instance_Rec.Quantity := (l_Source_Quantity -
413 P_Proj_Item_Uninst_Attr_Rec.Quantity);
414 l_Instance_Rec.Active_End_Date :=Null;
415 l_Instance_Rec.Object_version_number :=l_S_object_version_number;
416 l_Party_tbl.delete;
417 l_Txn_Rec := CSE_UTIL_PKG.Init_Txn_Rec;
418 l_Txn_Rec.Transaction_Date := l_sysdate;
419 l_Txn_Rec.Transaction_Type_Id := l_Transaction_Type_Id;
420 l_Txn_Rec.Source_Transaction_Date := P_Proj_Item_Uninst_Attr_Rec.Transaction_Date;
421 l_Txn_Rec.Source_Group_Ref := P_Proj_Item_Uninst_Attr_Rec.Work_Order_Number;
422 l_Txn_Rec.Transaction_Quantity := P_Proj_Item_Uninst_Attr_Rec.Quantity;
423 l_Txn_Rec.Transacted_By := P_Proj_Item_Uninst_Attr_Rec.Transacted_By;
424 l_Txn_Rec.Transaction_Status_Code := CSE_DATASTRUCTURES_PUB.G_PENDING;
425 l_Txn_Rec.Transaction_Action_Code := Null;
426 l_Txn_Rec.Message_Id := P_Proj_Item_Uninst_Attr_Rec.Message_Id;
427
428 IF NOT t_transaction_id = -1 THEN
429 l_txn_rec.transaction_id := t_transaction_id;
430 END IF;
431 IF (l_debug = 'Y') THEN
432 CSE_DEBUG_PUB.ADD('Before update of source instanace qty - step 5.3');
433 END IF;
434
435
436 CSI_Item_Instance_Pub.Update_Item_Instance(
437 p_api_version => l_api_version,
438 p_commit => l_Commit,
439 p_validation_level => l_Validation_Level,
440 p_init_msg_list => l_Init_Msg_List,
441 p_instance_rec => l_Instance_Rec,
442 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
443 p_party_tbl => l_party_tbl,
444 p_account_tbl => l_account_tbl,
445 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
449 x_instance_id_lst => l_instance_id_lst,
446 p_org_assignments_tbl => l_org_assignments_tbl,
447 p_txn_rec => l_Txn_Rec,
448 p_asset_assignment_tbl => l_asset_assignment_tbl,
450 x_return_status => l_Return_Status,
451 x_msg_count => l_Msg_Count,
452 x_msg_data => l_Msg_Data );
453
454 -- get the error message from the stack if there is any error
455
456 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
457 l_msg_index := 1;
458 l_Error_Message:=l_msg_data;
459 WHILE l_msg_count > 0 LOOP
460 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
461 l_msg_index := l_msg_index + 1;
462 l_Msg_Count := l_Msg_Count - 1;
463 END LOOP;
464 RAISE Update_Ib_Exp;
465 ELSIF l_Return_Status = FND_API.G_Ret_Sts_Success THEN
466 IF t_transaction_id =-1 THEN
467 t_transaction_id := l_txn_rec.transaction_id;
468 END IF;
469 END IF;
470
471 IF(l_Instance_Header_Tbl_Out.COUNT>0) THEN
472
473 cse_util_pkg.Get_Destination_Instance(
474 P_Dest_Instance_tbl => l_Instance_Header_Tbl_Out,
475 X_Instance_Rec => l_Dest_Instance_Rec,
476 X_Return_Status => l_Return_Status,
477 X_Error_Message => l_Error_Message);
478
479 IF NOT(l_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
480 RAISE Update_Ib_Exp;
481 END IF;
482
483
484 -- If destination item instance is found
485
486 IF l_Dest_Instance_Rec.Instance_Id IS NOT NULL THEN
487 l_D_object_version_number:=l_Dest_Instance_Rec.object_version_number;
488
489 IF (l_debug = 'Y') THEN
490 CSE_DEBUG_PUB.ADD('Initailizing instance Record for update Of dest inst - step5.1');
491 END IF;
492
493 l_Instance_Rec := CSE_UTIL_PKG.Init_Instance_Update_Rec;
494 l_Instance_Rec.Instance_Id := l_Dest_Instance_Rec.Instance_Id;
495 l_Instance_Rec.Quantity := (P_Proj_Item_Uninst_Attr_Rec.Quantity +
496 l_Dest_Instance_Rec.Quantity );
497 l_Instance_Rec.Active_End_Date := Null;
498 l_Instance_Rec.Object_version_number :=l_D_object_version_number;
499
500 -- check if destination instance is expired ,if expired then set the status same as source.
501
502 FOR inst_status_cur_rec in inst_status_cur
503 LOOP
504 IF (inst_status_cur_rec.instance_status_id=l_Dest_Instance_Rec.Instance_status_id)
505 THEN
506 l_Instance_Rec.instance_status_id :=l_s_status_id;
507 END IF;
508 END LOOP;
509 l_Txn_Rec := CSE_UTIL_PKG.Init_Txn_Rec;
510 l_Txn_Rec.Transaction_Date := l_sysdate;
511 l_Txn_Rec.Source_Transaction_Date:= P_Proj_Item_Uninst_Attr_Rec.Transaction_Date;
512 l_Txn_Rec.Transaction_Type_Id := L_Transaction_Type_Id;
513 l_Txn_Rec.Source_Group_Ref := P_Proj_Item_Uninst_Attr_Rec.Work_Order_Number;
514 l_Txn_Rec.Transaction_Quantity := P_Proj_Item_Uninst_Attr_Rec.Quantity;
515 l_Txn_Rec.Transacted_By := P_Proj_Item_Uninst_Attr_Rec.Transacted_By;
516 l_Txn_Rec.Transaction_Status_Code:= CSE_DATASTRUCTURES_PUB.G_PENDING;
517 l_Txn_Rec.Message_Id := P_Proj_Item_Uninst_Attr_Rec.Message_Id;
518
519 IF NOT t_transaction_id = -1 THEN
520 l_txn_rec.transaction_id := t_transaction_id;
521 END IF;
522 IF (l_debug = 'Y') THEN
523 CSE_DEBUG_PUB.ADD('Before update of destination instanace');
524 END IF;
525
526 CSI_Item_Instance_Pub.Update_Item_Instance(
527 p_api_version =>l_api_version,
528 p_commit => l_Commit,
529 p_validation_level => l_Validation_Level,
530 p_init_msg_list =>l_Init_Msg_List,
531 p_instance_rec => l_Instance_Rec,
532 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
533 p_party_tbl => l_party_tbl,
534 p_account_tbl => l_account_tbl,
535 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
536 p_org_assignments_tbl => l_org_assignments_tbl,
537 p_txn_rec =>l_Txn_Rec,
538 p_asset_assignment_tbl => l_asset_assignment_tbl,
539 x_instance_id_lst =>l_instance_id_lst,
540 x_return_status =>l_Return_Status,
541 x_msg_count =>l_Msg_Count,
542 x_msg_data =>l_Msg_Data );
543
544 -- get the error message from the stack if there is any error
545
546 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
547 l_msg_index := 1;
548 l_Error_Message:=l_msg_data;
549 WHILE l_msg_count > 0 LOOP
550 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
551 l_msg_index := l_msg_index + 1;
552 l_Msg_Count := l_Msg_Count - 1;
553 END LOOP;
554 RAISE Update_Ib_Exp;
555 ELSIF l_Return_Status = FND_API.G_Ret_Sts_Success THEN
556 IF t_transaction_id =-1 THEN
557 t_transaction_id := l_txn_rec.transaction_id;
558 END IF;
559 END IF;
560
561 ELSIF l_Dest_Instance_Rec.Instance_Id IS NULL THEN
562 fnd_message.set_name('CSE','CSE_NO_ACTIVE_DEST_INSTANCES');
563 fnd_message.set_token('ITEM',l_instance_header_tbl_out(i).inventory_item_id);
564 l_error_message := fnd_message.get;
565 l_Return_Status := FND_API.G_RET_STS_ERROR;
566 RAISE Update_Ib_Exp;
567 END IF;
568
569 ELSIF (l_Instance_Header_Tbl_Out.COUNT=0) THEN
570 fnd_message.set_name('CSE','CSE_DEST_RECORD_NOTFOUND');
571 fnd_message.set_token('ITEM',l_instance_header_tbl_out(i).inventory_item_id);
572 l_error_message := fnd_message.get;
573 l_Return_Status := FND_API.G_RET_STS_ERROR;
574 RAISE Update_Ib_Exp;
575 END IF;
576
577 END IF;
578 END IF;
579 IF (l_debug = 'Y') THEN
580 CSE_DEBUG_PUB.ADD('**** END OF PROJECT ITEM UNINSTALLED TRANSACTION ******');
581 CSE_DEBUG_PUB.ADD(' ');
582 END IF;
583 EXCEPTION
584 WHEN Update_Ib_Exp THEN
585 X_Return_Status := l_Return_Status;
586 x_Error_Message := l_Error_Message;
587
588 WHEN OTHERS THEN
589 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
590 fnd_message.set_token('ERR_MSG',l_Api_Name||'='|| SQLERRM);
591 x_error_message := fnd_message.get;
592 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
593
594 END Update_Ib_Repository;
595
596 PROCEDURE Update_eib_instances(
597 P_Proj_Item_Uninst_Attr_tbl IN Proj_Item_Uninst_Attr_tbl_Type,
598 X_Return_Status OUT NOCOPY VARCHAR2,
599 X_Error_Message OUT NOCOPY VARCHAR2) IS
600 BEGIN
601 IF NOT P_Proj_Item_Uninst_Attr_tbl.COUNT = 0 THEN
602 FOR i IN P_Proj_Item_Uninst_Attr_tbl.FIRST .. P_Proj_Item_Uninst_Attr_tbl.LAST LOOP
603 IF P_Proj_Item_Uninst_Attr_tbl.EXISTS(i) THEN
604 update_ib_repository( p_proj_item_uninst_attr_tbl(i),
605 x_return_status,
606 x_error_message);
607 END IF;
608 END LOOP;
609 END IF;
610
611 END update_eib_instances;
612 END CSE_PROJ_ITEM_UNINST_PKG;