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