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